學不完．教不停．用不盡

本篇要來介紹一個常見而簡單又好用的函數：MOD。

MOD 函數主要是傳回兩數相除後的餘數（餘數和除數具有相同的正負號）。

MOD 函數如果使用 INT 函數來表示：MOD(n, d) = n-d*INT(n/d)。

本篇要來介紹一個常見而簡單又好用的函數：INT。

INT 函數主要是將數字無條件捨去至整數，觀察下圖中的四種數值取 INT 函數的結果。

【例】利用 INT 函數應用在求得兩數相除的整數商。

在下圖中是一個由多個2欄一組(代碼、班級)所組成的資料表，如何查詢班級的代碼？

如果你要使用 VLOOKUP 函數來查表時，要查詢的內容必存在於第一欄中，但此例是內容分散在多個欄位中，所以不適合使用 VLOOKUP 函數。

改用 INDEX 函數來試試。

【公式設計與解析】

在 Excel 裡儲存格顯示的內容可以和儲存格的內容不一樣，這是因為相同數值，可以設定不同的格式，靠的是「數值格式」的設定。

下圖中，產生了基於起始日期的日期列表。

而其中的日期是以「年/月/日」的形式顯示，這也是預設顯示的格式。

如果你在數值格式設定中，選取了自訂格式：mm/dd，則顯示的結果是月二碼/日二碼。

根據這篇：Excel-設計一式製作萬年月曆，再來變化成另一種萬年月曆的形式。

1. 儲存格A2：輸入年份

2. 儲存格G2：輸入月份

3. 產生各月第一週的各天日期

如何在 Excel 的工作表裡，對月曆自動設定簡易區隔的格式？

當你建好了如下圖這樣的一個連續日期產生的月曆表，以後只要在第一個儲存格B3，輸入星期一的日期，就可以自動產生連續的日期。

網友再次詢問到在 Excel 中，萬年月曆的做法。

在下圖中，只要在儲存格A3中輸入西元年，即可產生該年裡12個月的月曆，每個日期可以分別對照到星期幾。

【公式設計與解析】

儲存格B4：=DATE($A$3,COLUMN(A1),1)-WEEKDAY(DATE($A$3,COLUMN(A3),1),2)+ROW(1:1)

常常我們在上網時或閱讀某些文件時，看到一些人物、物件等圖片，很想知道那是什麼，要 Google 時也不知道要用什麼關鍵字。在手機上可以使用例如 Google lens 的智慧鏡頭功能，只要對著螢幕拍照，就可以幫你搜尋。

現在，微軟的 Windows 10 的工作列上，也可以讓你擷取畫面直接來搜尋了。

只要選取工作列上的搜尋框，點選圖示：以螢幕擷取畫面搜尋。

會自行啟動內建的「剪取與繪圖」程式，你可以用來擷取畫面。

在 Excel 裡，如果要計算今天起的日期區間中，某個星期幾的數量，該如何處理？

例如，下圖中要計算今天起的90天裡星期五的數量。

如果利用輔助欄位來協助，先利用C欄取得星期幾的資料，再利用D欄判斷是否為星期五。最後計算D欄中的「V」數量，即可求得結果（本例為13）。

如果你是想在一個儲存格裡解決這麼複雜的過程，你必須用到 SUMPRODUCT、WEEKDAY、INDIRECT、ROW、TODAY 等函數。

【公式設計與解析】

在 Excel 裡有一個數據的清單，每一個儲存格中包含了數值和單位，如何來計算這些包含了單位的數值總和？

1. 如果你想手動來處理

(1) 選取儲存格B4:B11，進行資料剖析處理，將數值和單位分成二欄。

長期在學校中擔任行政工作，每每看到其他同仁（例如：註冊組）在取得的資料中要查詢想要的資料時總是失敗。細究其原因，原來都是格式惹的禍！

因為要處理的資料，來源總是五花八門。其中由數字組成的資料，例如：學號，在 Excel 中可能被視為數值，也可能被視為文字。當在以 VLOOKUP 函數查詢時，如果以文字學號在數值學號中查詢或是以數值學號在文字學號中查詢，往往查不到任何資料。

如何來解決這個常見的問題呢？其實很簡單喔！

1. 如何以文字學號在數值學號中查詢？

儲存格B3：=VLOOKUP(A3*1,$E$3:$F$22,2,FALSE)

關於 Excel，最近接連介紹 SUMPRODUCT 函數應用。

Excel-常用指令SUMPRODUCT函數

Excel-互通陣列公式、SUM+IF、SUMIF、SUMIFS、SUMPRODUCT

Excel-使用SUMPRODUCT函數執行陣列資料運算

網友剛好也問到：如下圖的資料表單，如何設計對照基本資料的統計分析？

因為位置有放置項目和數量的限制，所以要統計是否有超出。

在前幾篇文章中提及了 SUMPROCUT 函數的使用，你有了解了嗎？

Excel-常用指令SUMPRODUCT函數

Excel-互通陣列公式、SUM+IF、SUMIF、SUMIFS、SUMPRODUCT

SUMPRODUCT 函數對於處理陣列資料的功能性很強，本篇再來讓初學者有一些練習，以增強理解與應用。

【使用 SUMPRODUCT 函數】

本篇要利用產生天干和地支的排列組合，來練習查表的操作。

會使用到函數：INDEX、ROW、INT、MOD。

首先，定義儲存格範圍的名稱。

選取儲存格A2:A12，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：天干。

選取儲存格B2:B14，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：地支。

在 Excel 中，除了拿來做為試算功能之外，更可以用來做資料的處理。

例如在下圖中，有一個表格是由星期、時段和人員組成的表格。如何從左圖轉換為右圖？

（本例中每一天，每一個人員只會出現一次。）

【公式設計與解析】

儲存格G3：=IFERROR(INDEX($B$2:$D$2,MATCH(G$2,$B3:$D3,0)),"")

前一篇文章：Excel-如何不使用陣列公式列出符合條件的清單，要幫助不會使用陣列公式的人也能做到複雜的公式相同結果。本篇也是來看看網友常問的問題中「列出不重覆的清單」，如何不使用陣列公式達到相同結果。

在下圖中，如果要使用陣列公式來根據G欄的原始資料，列出不重覆的資料清單。

如果以陣列公式來處理，儲存格H3：

{=INDEX($G$3:$G$30,SMALL(IF($G$4:$G$31=$G$3:$G$30,"",
ROW(G$3:$G$30)),ROW(1:1))-2)}

在解答網友問題的過程中，常會發現網友對於陣列公式的接受度低，因為不易理解。而且一個儲存格中包含過多的函數，也讓人在理解之路困難重重。

例如，以下的範例要在姓名的清單中，依指定的文字找出姓名中含有該文字的清單。

如果以陣列公式來處理，可以在一個儲存格中即可求得解答。

儲存格C3公式：

{=IFERROR(OFFSET(A$2,SMALL(IF(SUBSTITUTE(姓名,$D$1,"")<>姓名,

網友問到：在 Excel 中如果使用的版本沒有提供 CONCAT 函數，如果要做到如下圖要串接被勾選的對應儲存格內容，該如何處理？

儲存格L2：{=CONCAT(IF(B2:K2="V",B$1:K$1,""))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。

在 Excel 2019 版以上就有 CONCAT 函數，使用上相當方便。

但是不同版本裡，沒有 CONCAT 函數時，只好使用數個輔助欄位來協助。（下圖中的Ｍ欄至Ｖ欄）

在 Excel 裡，有許多公式中都需要用到邏輯判斷，例如函數：NOT、AND、OR、XOR等，可以直接做邏輯運算，而函數：IF、IFS、SWITCH、IFERROR、IFNA等在參數中也都含有運算結果 TRUE／FALSE 的判斷。

邏輯運算有其規則，使用時要先知道其邏輯概念，必要時背下來，使用上不容易出錯。

有些初學者要理解這些相關的邏輯判斷的確不容易，藉助其他工具來幫助理解吧！

網友常會問到在 Excel 中要將儲存格內容串接的問題，實在不容易做到。

但是仍然可以使用 PHONETIC 函數和 CONCAT 函數能做到的部分來練習。

1. 串接有「V」對應的項目

儲存格L2：{=CONCAT(IF(B2:K2="V",B$1:K$1,""))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。