學不完．教不停．用不盡

延續前二篇：

Excel-常用指令SUMIF,SUMIFS函數

Excel-常用指令SUMPRODUCT函數

本篇要來練習如何互通陣列公式、SUMIF、SUMPRODUCT等函數的使用。

在下圖左的資料清單裡，如果要計算所以A*B的總和，先把每一組A*B算出後(C欄)，再予以加總。但是，如果使用陣列公式，則公式顯的簡捷。

​​ ​  ​

前一篇文章：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 函數，當引用的工作表愈多，公式總長度就會愈長，該如何處理？

網友問到，在 Excel 中，要依以下的規則調整個位數：

個位數0：維持原數

個位數1~8：調整為0

個位數9：進位至10位數

【公式設計與解析】

最近又有學校的行政人員問到一個使用頻率非常高的問題，輸入一個日期要月和日為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)來處理，公式較容易理解與使用。