學不完．教不停．用不盡

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

如下圖，分組有二組(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)

在 Excel 中有一個清單是依日期的起始和結束來分組，如何給予一個日期，來查詢其分組？

【設計與解析】

先為儲存格範圍命名，選取儲存格A3:c13，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：組別、起始、結束。

根據儲存格E4的日期來反查組別。

1. 使用 XMATCH 函數

關於 GUID 可以參考：https://zh.wikipedia.org/zh-tw/全局唯一標識符

本篇要利用亂數產生函數(RANDBETWEEN)和10進制轉換16進制函數(DEC2HEX)，來產生隨機的 GUID。

GUID 以 8-4-4-4-12 碼的組合顯示，以亂數產生的 GUID，只要按 F9 鍵即可產生新的一組。

【設計與解析】

(1) 產生8碼16進制亂數：

在 Excel 裡要計算算階層可以使用 FACT 函數、計算雙階層可以使用 FACTDOUBLE 函數。以下各例舉 4 種方式來運算階層。

【例一】計算階層 5!=5*4*3*2*1

(1) 公式：=5*4*3*2*1

(2) 公式：=FACT(5)

網友問到如何在 Excel 裡產生 QR Code 圖片？

由於 Excel 無法自動產生 QR Code 圖片，但是可以藉助 Google API 來協助，再加上 Excel 裡的 IMAGE 函數。

Google API 參考說明：

https://developers.google.com/chart/interactive/docs/gallery?hl=zh-tw

根網址：https://chart.googleapis.com/chart?