贊助廠商

在 Excel 2021 版之後,如果你的公式中有陣列的表示式,則公式會自動溢出至陣列範圍的儲存格。以下要用製作九九乘法表來當為範例,以比較之間的差異。

1. 非陣列公式,利用欄和列已輸入的數值 1~9。

Excel-以製作九九乘法表說明陣列和非陣列公式

儲存格B4:=$A4*B$3

複製儲存格B4,貼至儲存格B4:J12。

 

2. 陣列公式,利用 ROW 和 COLUMN 自動產生數值 1~9。

Excel-以製作九九乘法表說明陣列和非陣列公式

儲存格B4:=ROW(1:9)*COLUMN(A:I)

在儲存格B4輸入的公式會自動溢出至儲存格B4:J12。

在公式中「1:9」和「A:I」都是陣列表示。

 

3. 非陣列公式,利用欄和列已輸入的數值 1~9。

Excel-以製作九九乘法表說明陣列和非陣列公式

儲存格A3:=ROW(A1)&"X"&COLUMN(A1)&"="&ROW(A1)*COLUMN(A1)

複製儲存格B3,貼至儲存格B3:I11。

 

4. 陣列公式,利用 ROW 和 COLUMN 自動產生數值 1~9。

Excel-以製作九九乘法表說明陣列和非陣列公式

儲存格A3:=ROW(1:9)&"X"&COLUMN(A:I)&"="&ROW(1:9)*COLUMN(A:I)

在儲存格A3輸入公式會自動溢出至儲存格A3:I11。

在公式中「1:9」和「A:I」都是陣列表示。

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

本篇以研習報名表的Google表單填答結果來練習後續的資料處理。

參考下圖,除了姓名欄位以外,共有四場研習報名,各有一欄參加與否的資訊、一欄便當的資訊。

Google表單填答結果後續資料處理

【設計與解析】

選取儲存格A3:I28,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義各欄位名稱。

1.各課程參加人數

儲存格L4:=SUMPRODUCT((INDIRECT("課程"&K4)="參加")*1)

複製儲存格L4,貼至儲存格L4:L7。

(1) 利用 INDIRECT 函數將字串「"課程"&K4」(本例:課程A)轉換為儲存格範圍。

(2) INDIRECT("課程"&K4)="參加":條件判斷傳回 TRUE/FALSE 陣列。

(3) INDIRECT("課程"&K4)="參加")*1:將 TRUE/FALSE 陣列轉換為 1/0 陣列。

(4) 利用 SUMPRODUCT 函數將 1/0 陣列加總

同理:

(1) 參加者便當葷

儲存格M4:=SUMPRODUCT((INDIRECT("便當"&K4)="葷")*1)

(2) 參加者便當素

儲存格N4:=SUMPRODUCT((INDIRECT("便當"&K4)="素")*1)

2. 各課程參加者名單

儲存格L9:=FILTER(姓名,INDIRECT(K8&K9)="參加")

(1) 利用 INDIRECT 函數將字串「K8&K9」(本例:課程A)轉換為儲存格範圍。

(2) 利用 FILTER 函數依條件「INDIRECT(K8&K9)="參加")」篩選並列出符合的「姓名」。

同理,各課程參加者便當資訊:

儲存格M9:=FILTER(INDIRECT("便當"&K9),INDIRECT(K8&K9)="參加")

3. 各課程參加者共報名幾場次

儲存格N9:=SUMPRODUCT((姓名=L9)*($B$4:$I$28="參加"))

利用雙條件:

條件一:姓名=L9

條件二:$B$4:$I$28="參加"

SUMPRODUCT 函數將兩者都符合者的數量加總,即為所求。

 

【參考資料】

 FILTER 函數參考微軟提供的說明網頁:

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼