學不完．教不停．用不盡

延續前二篇：

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)來處理，公式較容易理解與使用。

有學校的老師問到：在 Excel 裡如何將某班的成績如下圖的呈現？

其中是依某一科目的高分至低分，列出人數和座位。

看起來，老師實在是太辛苦了！為了想要了解學生的狀況，要不斷的統計和分析。

【公式設計與解析】

1. 設計輔助欄位判斷每個分數由第一個儲存格起出現的次數

在 Google 試算表裡每次開放給學生共編以填寫內容，但是常常有學生反應被其他人更改而不勝其擾。有時也追查不出是被誰更改了，現在有解了！

Google 試算表現在新增了顯示儲存格編輯記錄的功能了。

可以看到該儲存格由那一個帳號在何時編輯過，並且可以透過向左、向右鍵頭圖示以切換顯示不同編輯者。這下子，學生會不會比較不會亂搞了呢？就再看下去才會知道了。

有老師問到：在 Excel 處理學期成績時，有時會自動標示不及格分數的學生，來提醒自己是否在分數上要做一些調整。但是反之，如果真的有調整分數時，如何看到那些分數是被調整的？

例如，在下圖中已有利用「設定格式化的條件」來讓不及格者的分數顯示為紅色。但是老師有時會設定一些加分或減分項目來微調，想要能看出那些最終分數是被調整過的。

例如：在學期成績欄位中的公式，有再針對K欄做了分數調整。

以 Chrome 或 Edge 為例，在瀏覽網頁時，看到一張圖片想要儲存，很直覺得的做法是：在圖片上按右鍵，再選取「另存圖片」，再選取資料夾和檔案名稱，即可完成一張圖片的存檔。

有人覺得這樣的步驟還是有點煩人，尤其是一個網頁中有幾張圖片要下載時。

或是你會使用一些下載軟體或是外掛，來幫助你下載多張圖片。如果你只是一時想要下載幾張圖片，你可以試試這樣，看看會不會心情好一下。

開啟檔案總管，切換至想要儲存圖片的資料夾，再將檔案總管和網頁瀏覽器並列。

接著，將網頁中想要存檔的圖片，直接以拖曳方式拖到檔案總管中，即可完成存檔。

現在正值期末，教師通常會做一些課程回饋的問卷調查，因為多個班級共用一個Google表單，所以只會得到一個試算表結果，如何將數據轉換成各個班級的各個題目平均分數？

如下圖，很多學生填答，在此只留下班級資料(刪附學生個人資料)，想要以此得到一個班級和題目的對照平均分數。

【公式設計與解析】

這個工作看起來很複雜，只要用對方法，公式處理起來還算簡單。

在此，將題目轉換成甲,乙,丙,丁,戊,己庚,辛,壬,癸，班級為201,202,203,204,205,206。

儲存格E2：=SUMIF($A$2:$A$17,D2,$B$2:$B$17)

當你在儲存格A18:B19中輸入資料時，公式結果不會改變，即新增資料不會併入計算。

如果先將儲存格範圍以名稱定義：

選取儲存格A1:B14，按 Ctrl+Shift+F3 鍵，勾選「頂端列」。定義名稱：人員、數量。

儲存格E2：=SUMIF(人員,D2,數量)

在 Excel 裡，直條圖和橫條圖是常用的統計圖表，在下圖裡如果要依總人數和已完成人數，並以完成百分比概念來呈現，該如何處理？

例如，下圖中要在橫條圖中呈現百分比的視覺效果。

【參考步驟】

1. 先建立輔助欄位。

延續上一篇文章：Excel-輪值表轉換不同呈現方式(OFFSET,MATCH)

下圖中，左半部的排班規劃表，如何轉換為下圖左側的清單？

如果只是要使用一次的話，只要用手拉一拉資料就好，如果要長期套用相同的排班表，則不妨把公式操作一遍。

【公式設計與解析】

在 Excel 裡如果製作了工作輪值的基本資料表，可以轉換成不同的呈現方式。

【形式１】

例如下圖左，標示每一天輪班人員的標示，轉換為下圖右將每人輪班的日期標示出來。

儲存格I2：=IF(C2="●",$A2,"")

複製儲存格I2，貼至儲存格I2:M27。

網友問到輸入信用卡號 16 個數字時，始終最後一碼都是 0，這是什麼原因？

在 Excel 的工作表裡，如果在預設的狀況下輸入像是信用卡號( 16 個數字)時，你會發現顯示的結果第 16 位都是顯示0。

這是因為 Excel 只能儲存 15 個有效位數，而且會將前面 15 個數字之後的位數變更為 0。例如，輸入：1234567890123456，在設定儲存格格式的對話框中會看到：

(1) 通用格式：1.23457E+15（以指數形式顯示）

(2) 數值格式：1234567890123450.00