前一篇文章:Excel-常用指令SUMPRODUCT函數,接著來看看 SUMIF 函數的使用。
語法:SUMIF(判斷的儲存格範圍,判斷準則(條件), 計算的儲存格範圍)
參考下圖,如果要計算超過3000者的數量加總。
利用 SUMIF 函數,公式:=SUMIF(B3:B10,">3000")
前一篇文章:Excel-常用指令SUMPRODUCT函數,接著來看看 SUMIF 函數的使用。
語法:SUMIF(判斷的儲存格範圍,判斷準則(條件), 計算的儲存格範圍)
參考下圖,如果要計算超過3000者的數量加總。
利用 SUMIF 函數,公式:=SUMIF(B3:B10,">3000")
Excel 裡的函數那麼多,依照 20/80 理論,大概只要使用其中的 20% 函數,就可以完成 80% 所有的工作。那麼,總有一些重要的函數要優先學習,其中,SUMPRODUCT 函數應該就是其中非常重要的函數之一了。
SUMPRODUCT 是在計算陣列的乘積和。其語法為:
SUMPRODUCT (array1, [array2], [array3], ...)
即在執行array1、array2、…的乘積和。
例如下圖所示,要執行甲欄和乙欄的乘積和,其中四種公式的寫法都在表達相同的概念,
最近 Google 將「雲端硬碟檔案串流」更名為 Google 雲端硬碟電腦版,並整合了「備份與同步」的功能。以下是以49.0.8.0版來說明。
新版的功能:
(1) 將電腦上的資料夾同步到 Google 雲端硬碟,也可以備份到 Google 相簿。
(2) 會將 Google 雲端硬碟中的檔案和資料夾串流或鏡射到你的電腦,在電腦上可以存取 Google 雲端硬碟檔案。
(3) 同時連結四個 Google 帳戶並存取當中的檔案。
在 Excel 裡常用來搜尋的函數只有幾個,不外乎是 MATCH、INDEX、VLOOKUP、OFFSET 等。其使用上都要依照函數的邏輯來操作,一般使用者可能沒有理解函數的運作模式,所以導致不會設計公式。
本篇再次來試試說明這幾個函數在查詢資料上的使用。
首先,要先弄對誰是欄、誰是列。一般在學生記不住時,我都會指引其依照中文字「欄」和「列」的文字結構來記。(如下圖)
其中,垂直者為「欄」(COLUMN),水平者為「列」(ROW)。
網友問到:在 Excel 裡如果要跨工作表計算指定項目的個數,該如何處理?
如下圖,想要在四個工作表裡相同位置儲存格範圍裡,計算指定項目的個數。
因為如果使用 COUNTIF 函數無法活跨工作表使用,如果為每工作表的計算使用一個 COUNTIF 函數,當引用的工作表愈多,公式總長度就會愈長,該如何處理?
最近又有學校的行政人員問到一個使用頻率非常高的問題,輸入一個日期要月和日為2碼,並且匯出至其他系統時也要保留2碼。
如上圖,可分為四種狀況。
1) 因為儲存格預設為數值格式,07/08會變為7/8
當輸入「2021/07/08」,會顯示為「2021/7/8」。
匯出至其他系統時會匯入「2021/7/8」。
網友問到:如何在 Excel 的一個工作表裡,藉由點選一個儲存格內容以顯示另一個工作表中儲存格的內容?
例如:點選儲存格B2中的超連結,想要工作表DATA裡的儲存格B2內容。
【公式設計與解析】
儲存格B2公式:
=HYPERLINK("[link.xlsx]DATA!"&ADDRESS(ROW(A2),COLUMN(B2)),$A2&"."&B$1)
有老師問到這樣的需求:想要依指定的次數來列出學生座號的清單,該如何處理?
如下圖,座號要顯示2次、座號15要顯示、…。
【公式設計與解析】
若是藉助一個輔助欄位(圖中的欄A)來處理,公式較容易理解與使用。
有學校的老師問到:在 Excel 裡如何將某班的成績如下圖的呈現?
其中是依某一科目的高分至低分,列出人數和座位。
看起來,老師實在是太辛苦了!為了想要了解學生的狀況,要不斷的統計和分析。
【公式設計與解析】
1. 設計輔助欄位判斷每個分數由第一個儲存格起出現的次數