學不完．教不停．用不盡

根據上一篇文章：Excel-垂直資料轉換為矩陣形式(SUMPRODUCT,OFFSET,COLUMN)

如果想要調整為列出每日住院的病歷，該如何處理？

【公式設計與解析】

選取儲存格A1:D15，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：病歷號、入院日、出院日。

1. 計算每日人數

網友問到：在 Excel 工作表有一個如下圖的上方資料表，如何轉換為下方的資料表？

【公式設計與解析】

選取儲存格C1:C15，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：入院日。

在 Google 的試算表中，如果你想要製作下圖這樣的組織圖，該如何處理？

你必須要先熟悉其資料項目的排列方式。

（如下圖）資料項目分為上層和下層，仿照圖中的排列邏輯（對照紅色指引）來建構資料。

資料建構完成後，選取這些資料，再選取[插入]功能表中的「圖表」選項。

網友根據前一篇文章：Excel-找出一群數列之最大值／最小值的儲存格位址(SUMPRODUCT)，延伸問到如何找出一群數列之第1,23,大值的儲存格位址？

參考下圖，假設其中的數字均沒有重覆，使用三個不同色彩分別標示第 1,2,3 大的值。

【公式設計與解析】

1. 找出第 1 大值的儲存格位置

有人想要找出在 Excel 工作表中一群數列之最大值和最小值的儲存格位址，該如何處理？

在以下的圖表中，假設其中的數字均沒有重覆，要來出最大值和最小值的儲存格位址。

【公式設計與解析】

1. 找出最大值的儲存格位置

在 Excel 中，如果要對一群三個 0／1 一組的清單來計算共有幾個是每組有 2 個 1 的個數，該如何處理？

參考下圖，每一列中有 3 個數，由 0／1 組成，試著來計算每組有 2 個 1 的總數有幾個。

【公式設計與解析】

(1) 使用 SUMPRODUCT 函數

延續前一篇文章：Excel-將整年的資料清單分配到12個月的資料表中(OFFSET,MATCH)

如果你的原始資料清單中不是一個月中的每一天都有資料，而且呈現的時候只想要呈現有日期的資料，該如何處理？

以下是三個月的原始資料，有些日期沒有資料。

 

【公式設計與解析】

網友問到：在 Excel 中，有一個一整年份的資料清單，如何依其月份分配到12個月的資料表中？

在下圖的例子中，看到有三個月份的資料（其中有部分被隱藏），如何讓全年工作表中的資料能自動放到所屬的月份工作表中？

(1) 在「1月」工作表中

先建立一月份的日期清單，再輸入公式：

儲存格C2：=OFFSET(全年!$C1,MATCH(A2,全年!A2:A69,0),0)

在 Excel 中如果要將一些資料資單列入另一個清單中，做規則性的排列，如下圖和下下圖所示，該如何處理？

1. 手動操作

選取儲存格K2:P5，然後拖曳儲存格P5右下角的控制點，至儲存格P21的位置，即可自動週期性的填滿整個區域，其結果如同複製儲存格的功能。

在 Excel 的工作表中，如果你想在一個儲存格中輸入二列文字，則可以藉助使用快速鍵： Alt+Enter 鍵，加以分成二列。如果你想使用公式來將二個儲存格內的文字組合在一個儲存格，並且分列在一個儲存格中，該如何處理？

(1) 兩個儲存格內容合併在一個儲存格

儲存格D2：=A2&CHAR(10)&B2

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

在 Excel 中常見的時間表示方式：「hh:mm」，用以呈現時和分的數值。如何來轉換數值形式和中文形式的時間？

以下圖為例，來練習數值和中文的互換。

【公式設計與解析】

(1) 數字轉文字

有同仁問到：在 Windows 中為了使用上的方便，通常會把常用的「檔案捷徑」放在桌面上，但是如果你是要打開檔案所在的資料夾呢？如下圖中，

你可以這樣做：

1. 按著 Alt 鍵，在要開啟的檔案捷徑圖示上按二下，即會開啟檔案的「內容」對話框。

2. 再按一下[開啟檔案位置]按鈕。

網友問到的 Excel 問題：參考下圖，如何將上圖的資料格式轉換為下圖的資料格式。

【公式設計與解析】

儲存格B16：

=SUMPRODUCT(($A$2:$A$12=$A16)*($B$1:$G$1=B$15)*$B$2:$G$12)

網友問到：在日常的問卷調查中常會使用五等第「非常滿意、滿意、無意見、不滿意、非常不滿意」來做為問卷選項。如何在 Excel 中將所選項轉換為數字，或是將數字轉換為選項？

本例定義「非常不滿意：1；不滿意：2；無意見：3；滿意：4；非常滿意：5」

參考下圖，利用 VLOOKUP 函數將選項轉換數字，利用 CHOOSE 函數將數字轉選項。

【公式設計與解析】

網友問到：在 Excel 中有一個上百筆資料的日期清單，如何依所挑選的月份，用公式篩選出符合的清單。

當然，在 Excel 中，你可以使用「篩選」工具來手動挑出想要月份的日期清單。本篇文章是要利用公式來自動篩選想要月份的日期清單。以下分成二個部分來說明。

1. 月份以數字來篩選

月份以「1, 2, …, 12」的數字在下拉式清單中選取。(下拉式清單可以藉由「資料驗證」來製作)

選取日期的所有儲存格，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期。

網友問到：如下圖的 Excel 資料表中，假設想要由 12 項中取前 8 個較優的成績來平均，但是有些資料項不足 8 個，該如何處理？

參考下圖，其中不足 8 項的部分，應該只採計有數值的項目來平均。

【公式設計與解析】

(1) 公式：=IF(COUNT(A2:L2)<8,COUNT(A2:L2),8)

在 Excel 的工作表中，遇到的格式中，常會有左右相鄰兩欄要計算乘積和，或是上下相鄰兩列要計算乘積和，該如何處理。

在 Excel 中，若要計算『乘積和』，最常使用的函數是：SUMPRODUCT。

1. 左右相鄰兩欄

使用 SUM 函數也是可以輕鬆的計算得到結果：

儲存格N3：=SUM(B3*C3,D3*E3,H3*I3,F3*G3,J3*K3,L3*M3)

網友提問 Excel 的問題：在下圖上半部的資料清單，如何轉換為下半部的結果？

【公式設計與解析】

儲存格B13：=IFERROR(OFFSET($B$1,SUMPRODUCT(($A$2:$A$9=$A13)*
($C$2:$F$9=B$12)*ROW($C$2:$F$9))-1,0),"")

當你要設定圖案的格式時，開啟[格式化圖案]窗格，當你在設定數值大小時，有時想要知道設定值的範圍，以便更有效率的進行設定，該如何處理？

以下圖為例，只要將滑鼠移至方塊上方，按一下 Shift 鍵，即會出現範圍提示。

例如：

網友問到一個 Excel 的問題：參考下圖，如何將一個資料清單，以不使用公式的方式產生不重覆的項目？

如果你不想使用公式，想要利用一些操作就得到不重覆的項目清單，可以巧妙利用樞紐分析表來完成。

當你選取項目的內容（含標題），再建立樞紐分析表：

將唯一的欄位（本例為「項目」）加至「列」：