網友想要了解,如下圖,在 Excel 中有一個數值清單,部分儲存格產生了空白,想要排除這些空白儲存格,將非空白儲存格集合在一起,該如何處理呢?
【公式說明】
儲存格B3:{=OFFSET($A$1,0,SMALL(IF($B$1:$O$1<>"",COLUMN($B$1:$O$1),999),
COLUMN(A:A))-1,,)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,自動產生「{}」。
網友想要了解,如下圖,在 Excel 中有一個數值清單,部分儲存格產生了空白,想要排除這些空白儲存格,將非空白儲存格集合在一起,該如何處理呢?
【公式說明】
儲存格B3:{=OFFSET($A$1,0,SMALL(IF($B$1:$O$1<>"",COLUMN($B$1:$O$1),999),
COLUMN(A:A))-1,,)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,自動產生「{}」。
有網友問到:要如何在 Excel 中,由一個日期和項目組成的清單中,挑選相同日期者置於個別的工作表中?
參考下圖,先以資料查詢結果放在同一工作表來解說。
【方法一】
為了解說方便,先選取儲存格A1:A23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。
儲存格D2:{=IFERROR(OFFSET($B$1,SMALL(IF(日期=D$1,ROW(日期)-1,FALSE),
最近學校老師都在討論翻轉教室、線上學習,又是可汗學院,又是均一教育平台,又是MOOCs,一個一個不斷的出現。但是,老師們最關心的是:如何才能建立自己的線上教材呢?
要學校購置一個實用的平台並不容易,如何能實作練習呢?最近微軟推出了 Office Mix 工具,讓你可以將在 PowerPoint 2013 中編輯的簡報,轉換為線上學習教材。而老師可以將這些線上教材,有系統、有組織的架構於個人的網站或是部落格上,這樣就可以成就自己的線上學習平台了。基本上,平台的花費可以接近於 0,而且不用維護系統,教師只要專注於教材本身即可。就拿 Office Mix 來體驗一下吧!
Office Mix 可以將原來只有簡報投影片的原型,再疊加上錄製 Webcam 的畫面、收錄麥克風的聲音,及你在投影片上繪製的記號等,讓使用者可以宛如你親臨現場指導般的學習。即使投影片中含有超連結文字,也可以使用這些超連結。而且能對使用者的操作,做一些簡單的分析。
根據:https://mix.office.com/watch/1v4koz7wvbove的線上教學,提到了 Office Mix 的互動特性包含了:
Office Mix 的線上學習教材庫在:https://mix.office.com/Gallery,這些精挑細選的線上教材,是我們學習的典範,可以先拿來觀摩觀摩。
學校裡有一些老師和我討論到:想要以簡單的方式來讓學生來參與讀書會,而現在 YouTube 上的影片/短片十分豐富,如何讓學生能看完影片,打好心得,直接傳送讓老師能看到呢?
我想,Google 表單是一個不錯的呈現方式,參考以下的簡單操作範例:
1. 在 YouTube 上找到你要的影片,並複製影片的網址。
本例為:http://youtu.be/qfTPJld4RNo
2. 進入你的 Google 雲端硬碟,並且新增一個 Google 表單。
網友根據:Excel-各種成績計算(SUMPRODUCT,AVERAGE,LARGE,陣列公式)這篇文章中所列公式,建議要避免使用陣列公式(不要使用 Ctrl+Shift+Enter 鍵)。而是以陣列常數直接做為參數。
(1) 前6高分項平均成績
儲存格L2:=AVERAGE(LARGE(B4:K4,{1,2,3,4,5,6}))
原公式為{=AVERAGE(LARGE(B4:K4,ROW($1:$6)))}
這兩個公式是異曲同工,結果會相同。但是如果你要使用的項目是{1,2,3, ... , 98,99,100}時,在陣列公式中使用 ROW($1:$100) 或許會較方便。
有網友問到:根據這篇文章「Excel-搜尋多個字串(FIND、SEARCH)」,如果改成只要有一個關鍵被搜尋到即顯示「V」,只有在所有關鍵字都找不到時才顯示「X」。
【輸入公式】
根據題意只有在所有關鍵字都找不到時才顯示「X」,而使用 FIND 函數來找尋關鍵字,當找不到時會傳回 #VALUE!,所以只要藉助 ISERR 函數來判斷是否都傳回錯誤訊息即可。
儲存格B2:=IF(ISERR(FIND("感測器",A2))*ISERR(FIND("網路",A2)),"X","V")
網友問到如何計算一個日期為當年度第幾天的問題,這是常見的日期運算問題。
在 Excel 中日期是可以運算的,因為每個日期都被轉換為一個數值,例如:2014/1/1在儲存格格式中的通用格式,看到的是 41640,這就是其代表的數值。因為 Excel 是以 1900/1/1 當為 1,每增加一天數值加 1。
以 2014 年為例,指定一個日期要算出是 2014 年的第幾天,其 1/1 為第 1 天。(參考下圖)
以下提供三種作法來練習:
有網友問到:如果要使用 Excel 來計算年齡30~34=-1分、35~39=0分、40~44=1分、45~49=2分、50~54=3分、55~59=4分、60~64=5分、65~69=6分、70~74=7分,該如何寫公式?
參考下圖,通常必須先建立年齡區間和得分的對照表,而且年齡區間依排序好。以下分成二種作法來說明。
(1) 年齡區間使用三個欄位
儲存格B2:=VLOOKUP(A2,$D$2:$G$10,4,TRUE)
複製儲存格B2,貼至儲存各B2:B14。
新學期,老師陸續給學生們考試,產生了許多的小考成績,老師也問到了各種的成績採計方式,以下列舉使用 Excel 除了一般的平均(AVERAGE)之外,可能會用到的方式。
(1) 計算加權平均
儲存格L4:=SUMPRODUCT(B4:K4*$B$2:$K$2)/SUMPRODUCT($B$2:$K$2)
SUMPRODUCT(B4:K4*$B$2:$K$2):計算各次成績乘以加權數的和。
SUMPRODUCT($B$2:$K$2):計算各次加權數的和。
將以上二式相除,即為加權平均。複製儲存格L4,貼至儲存格L4:L19。
在 Excel 的各個版本中,一直都有提供 AND、OR、NOT 函數,而到了 Excel 2013 版後開始提供 XOR 函數。
XOR 稱為 Exclusive OR,其真值表如下,當有奇數個 TRUE 時,XOR 會傳回 TRUE。
【應用範例】