學不完．教不停．用不盡

在 Excel 中有一堆日期的數列，如果要分別依週別和星期統計次數，該如何處理？

(這類練習在本部落格中已出現多次，讀者可以參考其他幾篇文章。試著以搜尋方式來找到其他文章！)

【公式處理】

儲存格G2：=SUMPRODUCT((日期>=D2)*(日期<=F2))

有同事問到一個常見的問題：因為某種需要，必須將許多金額裝袋，所以要先準備多張鈔票及硬幣，如何知道要準備多少呢？其中紙鈔有：1000元、500元、100元，硬幣有50元、10元、5元、1元。(參考下圖)

儲存格B2：=INT($A2/B$1)

INT 函式可以取不大於該數的最大整數。由此取得共需幾張1000元鈔票。

儲存格C2：=INT(($A2-SUMPRODUCT($B$1:B$1,$B2:B2))/C$1)

藉由 SUMPRODUCT 函式取得前幾欄已換算好的金額，將原金額扣掉該金額，再透過 INT 函式計算剩下金額換算為各欄所需的數量。

開學了，同事對於學生姓名有興趣，想要知道姓名中包含某些字的人數有多少，在 Excel 中該如何處理？(參考下圖)

假資料範圍為：儲存格A1:B100。

先選取儲存格B1:B100，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：姓名。

【使用 PRODUCT 函數】

儲存格D1：=SUMPRODUCT(--(LEFT(姓名,1)="王"))

在 Excel 中有一個資料表(如下圖左)，分別由類別 A, B, C 組成的日期、時間、瀏覽量和留言量的數值資料。如果要建構如下圖右的資料表，該如何處理？

【準備工作】

先選取 A 欄到 E 欄中的有資料的儲存格，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，建立名稱：日期、時間、瀏覽量、留言量。

(1) 計算留言小計

有網友問到，在一個資料表中(如下圖左)，如何取出指定間隔列的資料(如下圖右)？

要做到這個動作的方法很多，這次使用 OFFSET 函數來處理：

儲存格D1：=OFFSET(A$2,(ROW(1:1)-1)*4,,)

(ROW(1:1)-1)*4：產生起始值為1，間隔 4 的連續數值。ROW(1:1)=1，往下複製時會變為ROW(2:2)=1、ROW(3:3)=3、…。

複製儲存格D1，貼至儲存格D2:E16。

有老師問到，如果一份試卷編輯好後，將答案置於一個表格中，如何切換答案要不要列印出來？

先描述一個用法：

在 Word、Excel 或 PowerPoint 中，如果增加了多個物件，你可以指定每個物件的可見度與層次。先選取[常用/選取]下拉式清單中的「選取窗格」選項。

[選取窗格]對話框：

有人問到：在 Excel 的資料表中有一串工作時間的數列，如果想要轉換為不同的時間呈現方式(參考下圖)，該如何處理？

(1)

儲存格B2：=INT(A2*24)&"時"&INT((A2*24-INT(A2*24))*60)&"分"

因為在 Excel 中，每一小時換算為數值：1/24，每一分鐘換算為數值：1/24/60。

INT(A2*24)：計算時的數值。

學校同仁問到，如果要將下圖左之儲存格中的資料(分為三行)，轉換為下圖右的資料型態(變為一行)，如行動作比較方便？

你可以試試以下的做法：

1. 開啟[尋找及取代]對話框。

2. 將插入點移至[尋找目標]方塊中，按著 Alt 鍵不放，然後使用 KeyPad 的數字鍵，輸入「0010」。

3. 在[取代成]文字方塊中輸入「/」。

在 Excel 中有一個資料表(參考下圖左)，如果第 1,2 碼為代號，第 4,5 碼為分組，根據這兩個資料來做小計，該如何處理較為簡單？藉用 SUMPRODUCT 函數，可以用最簡捷的公式計算出來。

首先，選取儲存格A1:A25，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：Data。

【輸入公式】

儲存格D2：=SUMPRODUCT((LEFT(Data,2)=$C2)*(MID(Data,4,2)=D$1)*RIGHT(Data,2))

複製儲存格D2，貼至儲存格D2:G5。

根據在 Excel 的一個資料表(如下圖左)，要產生如下圖右的兩種不同資料型態((一)和(二)互為轉置)，如何下公式？

首先，選取儲存格A1:E23，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：人員、東、南、西、北。

【輸入公式】

1. 小計(一)

儲存格H2：=SUMPRODUCT(--(INDIRECT(H$1)=$G2))