學不完．教不停．用不盡

在 Excel 的表格中，常常會有需要對間隔列的內容做處理，該如何選取間隔列？

本篇內容提供四種做法：

1. 手動選取間隔列的方式

2. 利用輔助欄選取間隔列的方式

3. 利用公式篩選間隔列

4. 利用條件式格式設定

本篇要介紹如何將檔案總管裡一個資料夾的所有檔案清單列在 Excel 的工作表裡？

【參考作法】

1. 選取「資料／取得及轉換資料」功能表裡的「從檔案／從資料夾」。

2. 選取想要的資料夾。

電腦和行動裝置之間傳輸檔案和訊息一直都是重要的工作需求。目前的 Edge 瀏覽器提供了「drop」個實驗功能，可以讓不同的裝置透過Edge 和相同帳號來傳遞檔案和訊息。在操作練習前，先在不同裝置的 Edge 瀏覽器都必須登入相同的微軟帳號。

1. 在 Edge 瀏覽器的網址列中輸入：edge://flags/

2. 在搜尋框中輸入「drop」，進行搜尋。

3. 在 Enable Drop 項目中選取：Enabled。(啟用Drop實驗功能)

4. 按下「重新啟動」。(瀏覽器關閉後自動重新開啟)

在 Excel 裡根據原有的表格，如果要根據某一欄而隨機重排內容，該如何處理？

本篇將會學到：SORTBY函數、RANDARRAY函數

在下圖中有單一欄位和二個欄位的表格，如讓表格隨機重排。

【設計與解析】

RANDARRAY 函數可以產生亂數，而每按一次F9鍵，亂數會重新產生一次。

在 Excel 裡要產生各種等差數列，可以透過手動操作，現在有更方便的方式：利用 SEQUENCE 函數來產生。

以下列舉多種方式來產生各種的數字陣列。

【設計與解析】

(1) 儲存格G3：=SEQUENCE(B4,C4,D4,E4)

產生陣列：SEQUENCE(4,1,2,4)={2;6;10;14}，這是一維的多列(數值以「;」隔開)陣列。

本篇要練習：在 Excel 裡以多條件篩選資料並重新排列欄位，將會學到：FILTER、CHOOSEROWS函數。

【設計與解析】

1. 篩選男性且數值>40，列出「人員、數值、組別」

儲存格F5：=CHOOSECOLS(FILTER(A5:D26,(C5:C26="男")*(數值>40)),{1,2,4})

(1) FILTER(A5:D26,(C5:C26="男")*(數值>40))

本篇你將學到：如何將圖片內容轉換至Excel工作表中。

圖片來源可能是原始檔案輸出而成，也可以是一個PDF檔的擷圖，也可能是相機拍攝而來。

【例】在下圖中是一個PDF檔，如果選取並複製圖片中的兩個表格：

當貼至 Excel 的工作表中，會變成以下的狀態：(全部資料都貼在同一欄中)

在 Excel 裡，一般可以透過 VLOOKUP、XLOOKUP、INDEX等函數來執行表格中的查詢，但是結果都是只能查詢到一個結果。如果表格中有多個查詢結果時，該如何處理？

本篇會用到的函數：FILTER、UNIQUE、SORT。

在下圖中，要利用 FILTER 函數來取代 XLOOKUP 函數進行查詢，並且要練習動態陣列結合下拉式清單的操作。

【設計與解析】

1. 列出會員的不重覆清單

在 Excel 裡常會對表格做查詢，但是有時也會需要由查詢到的內容，反查欄名和列名，該如何處理？

假設表格中的數值全都是不會重覆。

【設計與解析】

為方便說明，先對儲存格範圍定義名稱：

編號：儲存格A7:A18

在上學期末時發現，很多網友查詢了擇優計算成績的做法，應該都一些辛苦的老師為一大堆的學生成績所苦。平時就很認真的記錄，期末要給學生最有利的成績。

通常，老師會在許多的成績中擇優或勾選或劃記不列入以計算平均。

【設計與解析】

1. 擇優N次計算平均成績(本例:10)

儲存格Q5 公式：=AVERAGE(LARGE(B5:P5,ROW(1:10)))

本篇來比較在 Excel 中，FILTER 函數和 XLOOKUP 函數如何執行雙條件查詢。

藉由不同運算子的巧妙運用，達到相同運算效果。

【設計與解析】

1. FLTER 函數

公式：=FILTER(數值,(A組=E4)*(B組=F4))

在 Excel 中使用 COUNTIF 函數來計算符合條件的個數，一般都是在一個表格中處理。如果要使用在多個表格中，該如何處理？

如下圖的A表、B表、C表是三個不連續的表格，要計算三個表格中「甲」的個數。

【設計與解析】

首先，為A表、B表、C表的人員的儲存格範圍命名：

本篇要練習如何計算姓名清單中姓名字數為2、3、4的人數，並且要練習函數：LEN、COUNTIF、SUMPRODUCT、FILTER、COUNTA等運用。

【設計與解析】

選取儲存格B3:B33，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：姓名。

公式一：=SUM((LEN(姓名)=2)*1)

公式中的「2」分別改成「3、4」，即可計算姓名 3 個字和 4 個字的人數。

網友問到：在 Excel 裡，如何在一個 8X5 的矩陣中產生不重覆的目數？

在 8X5 的矩陣中需要 40 個不重覆的數字(1～40)。

【設計與解析】

在設計公式前，必須先在選項的「公式」中設定：

勾選：啟用反覆運算，並設定最高次數：1000。

有老師問到：分組教學的二個老師成績如何合併成一個成績表？

如下圖，分組有二組(A組和B組)，人員都不相同，要將A組和B組的成績表合併成一個成績表。

【設計與解析】

一個式子就可以搞定：

儲存格E5：=SORT(VSTACK(A5:C14,A18:C27))

在 Excel 裡是以欄名列號來表達一個儲存格位址，而 ROW 函數可以傳回列號、COLUMN 函數可以傳回欄號。本篇要來說明如何將欄名轉換為數值和數值轉換為欄名。

【設計與解析】

1. 欄名轉換為數值

儲存各B4：=COLUMN(INDIRECT(A4&1))

利用 INDIRECT 函數將文字轉換為位址(傳回A1、Z1、AZ1、…)，再利用 COLUMN 函數傳回欄的號碼。

在 Excel 裡可以使用 IMAGE 函數來顯示網際網路上的圖片，我們也可以取用來做圖片檢視器。

【設計與解析】

首先，在網際網路上搜集到數個球類的圖片，並複製圖片的網址，將球類名稱和網址置於儲存格A7:B14。

接著，在儲存格B3中設定「資料驗證」，準則：

儲存格內允許：清單／來源：$A$7:$A$14。

在 Excel 裡可以用來依分隔符號分割字串的函數可以使用：TEXTSPLIT、TEXTBEFORE、TEXTAFTER 等函數。

以下的例子要來看看這幾個函數之間的交互運用。

【設計與解析】

1. 取出數字1

儲存格E8：=TEXTBEFORE(A7,{"+","-","X","/"},1)

在 Excel 裡常會利用「資料剖析」工具來分割字串，而 TEXTSPLIT 函數可以利用公式來完成這個手動的操作。如果在字串中含有多個分隔字元，該如何設計公式才能一氣合成？

【設計與解析】

(1) 字串：甲,乙,丙,丁,戊,己,庚,辛,壬,癸

公式=TEXTSPLIT(A3,",")

分隔符號「,」。

在 Excel 裡如何將一個表格依單數列、偶數列分成二個表格？

參考下圖，一個20週的清單裡分成二個組別(A群、B群)，如何依單數週、偶數週分成二個表格？

1. 將一個表格依單數列、偶數列分成二個表格

(1) 單數週

儲存格F5：=CHOOSEROWS(A5:D24,SEQUENCE(10)*2-1)