贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友提問:在 Excel 中,如何透過欄(列)標題和內容來反求列(欄)標題?

【經由列標題和內容求得欄標題】

本例:透過列標題『丁』和內容『R』,要求得欄標題『寅』。

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

1. 定義儲存格名稱

選取儲存格A2:F6,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:甲、乙、丙、丁、戊。

2. 選取儲存格I2,設定「資料驗證」。設定準則:

儲存格內允許:清單。來源:=INDIRECT(H2)。

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

結果如下:(當儲存格H2改變時,儲存格I2可以選取的清單也隨之變動。)

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

3. 設定公式。

儲存格H3:=OFFSET(A1,0,SUMPRODUCT((B2:F6=I2)*COLUMN(B2:F6))-1)

(1) SUMPRODUCT((B2:F6=I2)*COLUMN(B2:F6))

利用 SUMPRODUCT 函數求得儲存格I2的欄號。

(2) OFFSET(A1,0,第(1)式-1)

OFFSET 函數中根據第(1)式傳回的列號來求得對應的儲存格內容。


【經由欄標題和內容求得列標題】

本例:透過欄標題『卯』和內容『S』,要求得列標題『丁』。

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

1. 定義儲存格名稱

選取儲存格B1:F6,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:子、丑、寅、卯、辰。

2. 選取儲存格I2,設定「資料驗證」。設定準則:

儲存格內允許:清單。來源:=INDIRECT(H2)。

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

結果如下:(當儲存格H2改變時,儲存格I2可以選取的清單也隨之變動。)

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

3. 設定公式。

儲存格H3:=OFFSET(A1,SUMPRODUCT((B2:F6=I2)*ROW(A2:A6))-1,0)

(1) SUMPRODUCT((B2:F6=I2)*ROW(A2:A6)).

利用 SUMPRODUCT 函數求得儲存格I2的欄號。

(2) OFFSET(A1,第(1)式-1,0)

OFFSET 函數中根據第(1)式傳回的列號來求得對應的儲存格內容。

文章標籤

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

新同學進入新的校園,註冊組長又面臨「分班」的問題了!如果你已經由某一個規則將學生予以排序完成,如何能以 S 型分班的概念來分班呢?Excel 如何幫助你?以下分別以『手動』和『自動』二種方式來說明。

參考下圖,左側是有數百名學生已排序後清單,右側是自動以 S 型來分班的結果。(本例沒有考慮男女生等因素)

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)


【手動處理】

本例假設要分7班。

(1) 在分班欄位依序輸入「1,2,3,4,5,6,7,6,5,4,3,2,1」。

(2) 複製上述的儲存格範圍,往下各列貼上。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

(3) 進入自動篩選,篩選「1」項。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN) Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

(4) 複製篩選「1」的儲存格,貼至101班。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

(5) 重覆步驟(3)和(4),分別篩選「1,2,3,4,5,6,7」對應貼至「101,102,103,104,105,106,107」。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)


【公式自動處理】

如果你想試試以公式來自動分班,則可以輸入以下公式。

1. 產生分班欄位

儲存格C2:=IF(MOD(INT((ROW(1:1)-1)/$D$1),2),$D$1-MOD(ROW(1:1)-1,
$D$1),MOD(ROW(1:1)-1,$D$1)+1)

複製儲存格C2,往下各列貼上。

(1) MOD(INT((ROW(1:1)-1)/$D$1),2)

(2) $D$1-MOD(ROW(1:1)-1,$D$1)

(3) MOD(ROW(1:1)-1,$D$1)+1

第(1)式、第(2)式、第(3)式產生的結果如下圖:

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

(4) IF(第(1)式,第(2),第(3))

當第(1)式傳回『1』時,相當於邏輯 TRUE,會顯示第(2)式的結果。

當第(1)式傳回『0』時,相當於邏輯 FALSE,會顯示第(3)式的結果。


2. 產生分班結果

先選取C欄中有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:分班。

儲存格F2:{=OFFSET($B$1,SMALL(IF(分班=COLUMN(A:A),ROW(分班),),
ROW(1:1)+COUNTIF(分班,"<>"&COLUMN(A:A)))-1,0)}

這是陣列公式,輸入完成,要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

複製儲存格F2,貼至儲存格F2:L41。

(1) IF(分班=COLUMN(A:A),ROW(分班),)

在陣列公式中,判斷分班的儲存格陣列中和 COLUMN(A:A) 相同者,傳回其列號。COLUMN(A:A)=1,向右複製後,得到:COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。

(2) SMALL(第(1)式,ROW(1:1)+COUNTIF(分班,"<>"&COLUMN(A:A))

利用 SMALL 函數,由小到大依序取出不為 0 的列號。

COUNTIF(分班,"<>"&COLUMN(A:A):計算在分班陣列中,不為「1」的個數。

(3) OFFSET($B$1,第(2)式-1,0)

最後,透過 OFFSET 函數,代入第(2)式的傳回值,得到對應的儲存格內容。

3.使用公式的好處是:不管要分幾班都可以適用。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

文章標籤

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

在 Excel 中有一個數值清單(如下圖B欄),如何計算累計至指定第幾個?

在下圖中,C欄為計算每一個累計的結果,如何在沒有C欄輔助時計算指定個數的累計?

Excel-指定數值清單中累計至第幾個(SUBTOTAL,OFFSET,INDEX)

【公式設計與解析】

方法一:儲存格C2:=SUM($B$2:B2)

方法二:儲存格C2:=SUM(OFFSET($B$2,0,0,ROW(1:1),1))

方法三:儲存格C2:=SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:1),1))

複製儲存格C2,貼至儲存格C21。

以上三種方式都可以得到每個數值的累計結果。

現在,根據儲存格E2的指定個數,要計算累計結果。

儲存格E4:=INDEX(SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:20),1)),E2,1)

(1) OFFSET($B$2,0,0,ROW(1:20),1))

利用 OFFSET 函數取得B欄中要計算累計的儲存格區間。

(2) SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:20),1))

透過 SUBTOTAL 函數指定參數 9,用以指定執行 SUM 功能。

Excel-指定數值清單中累計至第幾個(SUBTOTAL,OFFSET,INDEX)

使用 SUBTOTAL 函數的用意,是可以利用 ROW(1:20) 產生 1~20  的陣列。

image

在執行 SUM 運算時得到累計的結果。

image

(3) INDEX(SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:20),1)),E2,1)

最後,再利用 INDEX  函數取出指定(儲存格E2)的累計結果。

文章標籤

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

網友想要在 Excel 中找出每一年各個月的第一個星期六是那一天,該如何處理?

參考以下的二個範例,基本上要使用 WEEKDAY 函數。


1. 每個月第1個星期六的日期

Excel-找出一年中每個月的第一個星期六(WEEKDAY,DATE)

【公式設計與解析】

儲存格B3=8-WEEKDAY(DATE($A3,COLUMN(A:A),1),17)

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

先來看看 WEEKDAY 函數的傳回值:

儲存格B4=WEEKDAY(DATE($A3,COLUMN(A:A),1),17)

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

在 WEEKDAY 函數中使用參數『17』,表示傳回的數字1~7對應星期日~星期六。

將 8 減掉 WEEKDAY 的傳回值,即為所求。


2. 求每個月第1個星期三的日期

Excel-找出一年中每個月的第一個星期六(WEEKDAY,DATE)

【公式設計與解析】

儲存格B3=8-WEEKDAY(DATE($A3,COLUMN(A:A),1),14)

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

儲存格B4=WEEKDAY(DATE($A3,COLUMN(A:A),1),14)

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

如果要找每個月的第一個星期三,則只要將 WEEKDAY 的參數指定為『14』,即表示傳回的數字1~7對應星期四~星期三。將 8 減掉 WEEKDAY 的傳回值,即為所求。


根據以上兩個例子,不難發現,要求每個月第一個星期幾和參數的對應關係:

星期日:11

星期一:12

星期二:13

星期三:14

星期四:15

星期五:16

星期六:17

文章標籤

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

網友問到:在 Excel 中的一個資料表,想要求得每一欄中,不為 0 的第 1, 2 個並且予以加總。該如何處理?

參考下圖,每一欄都有數個連續內容為 0 的儲存格,如何求得不為 0 的第 1, 2 個並且予以加總?

Excel-找出連續0之後的2個數予以加總(SUMPRODUCT,OFFSET)


【公式設計與解析】

儲存格A19:

=SUM(OFFSET(A1,SUMPRODUCT(MAX((A1:A18=0)*ROW(A1:A18))),0,2,1))

複製儲存格A19,貼至儲存格A19:G19。

(1) (A1:A18=0)*ROW(A1:A18)

SUMPRODUCT 函數中傳回符合條件 A1:A18=0 者的列號。ROW 函數可以傳回儲存格列號。

(2) MAX((A1:A18=0)*ROW(A1:A18))

利用 MAX 函數取得不為 0 者儲存格列號中的最大值。

(3) SUMPRODUCT(MAX((A1:A18=0)*ROW(A1:A18)))

利用 SUMPRODUCT 函數可以使用陣列運算。

(4) OFFSET(A1,SUMPRODUCT(MAX((A1:A18=0)*ROW(A1:A18))),0,2,1)

透過 OFFSET 函數,以儲存格A1為起點,位移至不為 0 的第 1 個儲存格,再取高度為 2、寬度為 1 的儲存格範圍。本例傳回儲存格A15:A16。

(5) 最後再透過 SUM 函數予以加總,即為所求。

Excel-找出連續0之後的2個數予以加總(SUMPRODUCT,OFFSET)

文章標籤

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

找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼