學不完．教不停．用不盡

網友問到：在 Excel 的資料清單中(如下圖)，有A、B、C三種狀態，要依下列條件評估狀態：

(1)三個中有一個『高負荷』，結果為『高負荷』；

(2)沒有『高負荷』時，三個中有一個『中負荷』，結果為『中負荷』；

(3)三個都為『低負荷』，結果為『低負荷』

網友問了一個問題：在 Excel 的工作表中想要根據一個矩陣表(雙條件)的資料來查詢對應的結果，該如何處理？

以下圖為例，在表<1>中是一個某疾病風險(三個數字區間)和工作負荷(三個文字項目)對應於發病風險的對照表(其中資料為虛擬)，如何以兩個條件查詢對應的發病風險。

【公式設計與解析】

因為有些網友是初學者，所以用比較複雜的公式內容來表示，讓網友能知悉公式和查表內容的對照關係。

有網友問到：在 Excel 中有一個如下圖的清單，其中的代碼欄位含有區域碼，如何根據代碼小計各區域的總和？

以下圖為例，每個人的代碼中含區域碼，如果在不另行取出區域碼的做法下，如何直接計算各區域的小計？

【公式設計與解析】

選取儲存格B1:C30，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：代碼、金額。

最近網友根據這篇文章：將PowerPoint簡報檔轉成影片，在操作上一直不能成功，所以我把操作步驟再重新描述一次。我以 PowerPoint 2013 為例。

1. 你在一個投影片的一張簡報中(以第一張投影片為例)，選取[插入／多媒體]功能表中選取「音訊／我個人電腦中的音訊」選項，插入一個音訊(以一個MP3檔為例)。

2. 接著，在[音訊工具／播放]功能表中的「音訊選項」區中，設定：

開始：自動；勾選「跨投影片撥放」；勾選「循循播放，直到停止」。(因為希望整個影片都有背景音樂)

同仁問到：常會看到下圖這種表示法，在 Excel 中如何模擬？

在下圖中，假設A欄為1~5的分數清單(本例共有10027個)。

【公式設計與解析】

網友根據這篇：

Excel-取含有文字的儲存格中計算數字總和(SUMPRODUCT,SUBSTITUTE)

想要將資料格以下圖格式呈現，該如何將『核』字消除，再計算小計。

【公式設計與解析】

網友問到 Excel 的問題：如下圖，依據訂單編號，想要隨機決定生產順序，所以要決定起始的生產時間，該如何處理？

在下圖中，每按一次 F9 鍵，即可隨機產生一個生產順序。而每一個訂單的生產起始時間，都是由先前訂單的生產時間累加而來。

【公式設計與解析】

儲存格H2：=SUMPRODUCT(($C$2:$C$11<C2)*$D$2:$D$11*$E$2:$E$11)

有網友問到在 Excel 中，跨工作表運算的問題。如果在公式中使用跨工作表位址參照，則須使用格式『工作表!儲存格』，但是要特別注意工作表名稱為純數字或是文字。

在下圖中，工作表名稱分別為：1、2、3、4、一、二、三、四。

參考上圖，特別注意：如果工作表範圍中包含數字，則在公式使必須使用『'  '』來含括工作表名稱。

1. 計算工作表1～工作4的儲存格A1:A13的和

儲存格J2：=SUM('1:4'!A2)

如果在 Excel 中要設計二層的下拉式選單，可以配合使用「資料驗證」功能來設計，當原始資料為橫式或是直式時，設計的方式並不相同。

一、原始資料為橫式

參考下圖右側，原始資料是橫式呈現。

參考以下步驟來設計：

1. 將儲存格D1:J1定義名稱：商品類別1。

網友根據前一篇文章(Excel-西元年和民國年互換表示(TEXT,MID,DATE,SUBSTITUTE)中，學到如何執行西元年和民國年的互換，例如：

2015/01/01→1040101→104/01/01 和 104/01/01→1040101→2015/01/01

想要進一步知道，若要執行：1040101→104/01/01→2015/01/01

該如何處理？(參考下圖)

有同仁問到：在 Excel 中，如果有一段連續列被隱藏，但是只想取消隱藏其中的某一列時該如何操作？

在下圖中，將第 4 列至第 13 列予以隱藏。

如果想要取消隱藏第 8 列，而保留其他隱藏列時，該如何處理？如果全部取消隱藏，再分二次將第4至第7列和第9列至13列予以隱藏，這是不明智的做法。

你可以這樣做！在名稱方塊中輸入：A8或是8:8，按下 Enter 鍵，即可選取儲存格A8或是第8列。

網友想要針對 Excel 中的數列清單，以一個千分位、二個千分位、三個千分位為單位來顯示數值，而不改變儲存格中所儲存格的數值內容，該如何處理？

參考下圖的C欄、D欄、E欄，分別以一個千分位、二個千分位、三個千分位為單位來顯示數值，以F欄為例，將E欄內容乘以 2，其結果和將 A欄乘以 2，結果相同，表示E欄內容和A欄內容相同。其中被省略的部分，會以四捨五入來呈現。

如果只是想要改變數值的顯示結果，不想改變儲存格內容，則不需使用函數，要使用數值格式設定，在[儲存格格式]對話框的「自訂」 類別中設定，例如：

C欄儲存格：設定『#.000,』(一個逗號，代表一個千分位)

D欄儲存格：設定『#.000,,』(二個逗號，代表二個千分位)

有網友問到：在取得的 Excel 報表的人員和時數清單中，其中時數部分的儲存格中含有文字和數字，若要取出數字部分來計算，該如何處理？(參考下圖)

在時數欄位中，想要將儲存格中的『核』字去除，留下數字以方便計算小計。

【公式設計與解析】

網友提供了個含有名稱、時間、數量的 Excel 清單，希望計算 08:00~20:00 和 20:00~08:00 的各類名稱小計(參考下圖)，該如何處理？

【公式設計與解析】

為了說明方便，選取儲存格A1:C19，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：名稱、時間、數量。

網友根據先前文章：Excel-取出清單中非空白儲存格內容重新排列(SMALL,IFERROR)，參考下圖，如果要將重新排列的結果依原順序排列該如何設計公式？

(1) 橫式轉直式依原順序重新排列

如果要依原順序重新排列，在此提供「陣列公式」的相關設計，在下圖中重新排列後產生的多個『0』，乃是原清單中沒有資料的儲存格。(為了說明方便，公式中並未將 0 消除)

有網友想要將一個數拆解為幾個100和100以下的數字，利用 Excel 該如何處理？以下圖為例，266 被拆解成 100, 100, 66。

【公式設計與解析】

儲存格B2：=(INT($A2/100)>=COLUMN(A:A))*100+(INT($A2/100)=
COLUMN(A:A)-1)*MOD($A2,100)

有網友想要根據 Excel 資料表中的一個含有色彩的樣式清單，挑選指定色彩的儲存格。參考下圖，挑選所有『紅色』的各種樣式。該如何處理？

【準備工作】

選取B欄中有資料的儲存格，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：樣式。

【公式設計與解析】

讀者想要在以下的 Excel 工作表，每間隔 5 個數即計算和，其餘儲存格均顯示空白，該如何處理？若分組數不是 5 個，而是一個變數，公式該如何設計？(參考下圖)

【公式設計與解析】

1. 分組數固定(本例為5)

儲存格B2：=IF(MOD(ROW(A2),5)=1,SUM(OFFSET(A2,0,0,-5,)),"")

在 Excel 檔案中用來計算與分析資料大多以表格和統計圖表呈現，如果有要用到呈現圖片，都需要靠手動方式來設定。這次來練習一個有趣的例子，如何才能做到像下圖中，輸入一個姓名，即能查詢這個人的照片？

在下圖中的例子，希望能選取一個人的姓名後，能自動顯示這個人的照片和計算其BMI值。

【公式設計與解析】

假設資料放在儲存格A1:E20。

同事在工作時要處理 Excel 檔案，卻常發現資料的呈現方向不是自己想要的方向，所以需要手動調整，透過公式來轉換。

1. 將同一欄的資料垂直翻轉或同一列的資料水平翻轉

垂直翻轉

儲存格C3：=OFFSET($A$3,10-ROW(1:1),)

10為資料個數，當公式向下複製時，ROW(1:1)=1→ROW(2:2)=2→