贊助廠商

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

搜尋本部落格文章資料

網友根據下圖左的 Excel 資料表,想要列出如下圖右的摘要結果,該如何處理?

在下圖左的資料表中每個人員可以參加 3 個組別(沒有重覆),要如何列出每個組的人員清單(如下圖右)?

Excel-從多欄中列出符合者清單(OFFSET,SMALL,ROW,陣列公式)


【公式設計與解析】

儲存格E2:{=OFFSET($A$1,SMALL(IF(($B$2:$D$23=F$1),ROW($B$2:$D$23),
999),ROW(1:1))-1,0)}

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

複製儲存格E2,貼至儲存格E2:K23。

(1) IF(($B$2:$D$23=F$1),ROW($B$2:$D$23),999)

在陣列公式中,若資料範圍儲存格B2:D23和儲存格F1相同,則傳回相符者的列號陣列,否則傳回『999』(這只是一個很大的數值,必須超過所以資料的列號最大值。)。

(2) SMALL(第(1)式,ROW(1:1))

根據第(1)式傳回的列號陣列,利用 SMALL 函數找出其最小值(ROW(1:1)=1)。若公式向下複製,則ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...,可以依序找出第 1, 2, 3, ...較小者。

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

將第(2)式傳回的列號代入 OFFSET 函數找出對應的儲存格內容。當公式向下複製時,可以由最小列至最大列,依序列出符合者的儲存格內容。

摘要結果的「0」是公式運算的結果,表示已超出資料範圍的傳回值。

Posted by vincent at 痞客邦 PIXNET Guestbook(0) 人氣()

有網友問到:在 Excel 中的 SUMIF、SUMIFS、SUMPRODUCT 函數有其共通之處,在使用時如何互相取代?如果使用 SUM+IF+陣列公式,又是如何互相取代?

參考下圖的資料表,運用 SUMIF、SUMIFS、SUMPRODUCT 函數及使用 SUM+IF+陣列公式來計算特定條件下的數量小計和金額小計。

SUM+IF+陣列公式的互通

以下用四個例子來對照 SUMIF、SUMIFS 及 SUMPRODUCT 函數及使用 SUM+IF+陣列公式。

為方便說明,先選取B1:D23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:單價、數量、金額。

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


(1) 計算單價500以上者數量小計

儲存格F3:=SUMIF(單價,">500",數量)

儲存格F3:=SUMPRODUCT((單價>500)*數量)

儲存格F3:{=SUM(IF(單價>500,數量,))}


(2) 計算單價500以上者金額小計

儲存格F5:=SUMIF(單價,">500",金額)

儲存格F5:=SUMPRODUCT((單價>500)*金額)

儲存格F5:{=SUM(IF(單價>500,金額,))}


(3) 計算單價300~600者數量小計

儲存格F8:=SUMIFS(數量,單價,">=300",單價,"<=600")

儲存格F8:=SUMPRODUCT((單價>=300)*(單價<=600)*數量)

儲存格F8:{=SUM(IF((單價>=300)*(單價<=600),數量,))}


(4) 計算單價300~600者金額小計

儲存格F10:=SUMIFS(金額,單價,">=300",單價,"<=600")

儲存格F10:=SUMPRODUCT((單價>=300)*(單價<=600)*金額)

儲存格F10:{=SUM(IF((單價>=300)*(單價<=600),金額,))}

Posted by vincent at 痞客邦 PIXNET Guestbook(0) 人氣()

網友問到:在 Excel 中如下圖的資料表中,如何在挑選日期後自動計算每日的數量總和?

參考下圖,當指定一個項目時,希望能計算各日的總和;當指定一個日期時,希望能計算所有項目的總和,該如何處理?

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)


【公式設計與解析】

1. 為儲存格範圍定義名稱

選取儲存格A2:J11,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,一次定義每個項目的儲存格範圍。

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

結果如下:

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

2. 設計下拉式清單

利用「資料驗證」功能來設計下拉式清單功能。

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

(1) 在儲存格L3的資料驗證準則中設定:

儲存格內允許:清單;來源:=$A$2:$A$11

(2) 在儲存格L5的資料驗證準則中設定:

儲存格內允許:清單;來源:=$B$1:$J$1

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

3. 輸入公式

(1) 儲存格L2:=SUM(INDIRECT(L2))

INDIRECT(L2):將儲存格L2的內容(本例:己)利用 INDIRECT 函數轉換為位址。因先前已定義各個項目的儲存格範圍,本例結果為:儲存格B7:J7。

最後透過 SUM 函數將儲存格範圍予以加總。

(2)儲存格L5:=SUM(OFFSET(A1,1,MATCH(L4,B1:J1,0),10,1))

MATCH(L4,B1:J1,0):找尋儲存格L4的內容在儲存格B1:J1中的位置(本例傳回『6』)。

OFFSET(A1,1,MATCH(L4,B1:J1,0),10,1):利用 OFFSET 函數找出儲存格L4所屬的儲存格範圍(本例為:儲存格G2:G11)。

最後透過 SUM 函數將儲存格範圍予以加總。


【補充資料】

如何讓資料表中的欄和列儲對照存格L5和儲存格L2的內容以不同色彩文字顯示?

1. 選取儲存格A2:J11,進入設定格式化的條件編輯規則:

規則:=$A2=$L$2 (注意位址的相對參照和絶對參照)

格式:紅色文字。

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

2. 選取儲存格A2:J11,進入設定格式化的條件編輯規則:

規則:=B$1=$L$4 (注意位址的相對參照和絶對參照)

格式:紅色文字。

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

Posted by vincent at 痞客邦 PIXNET Guestbook(1) 人氣()

有網友問到在 Excel 中跨工作表使用SUMPRODUCT函數的問題。參考下圖:

假設在工作表3的 SUMPRODUCT 函數要使用工作表1和工作表2的內容來運算。

Excel-跨工作表使用SUMPRODUCT函數

SUMPRODUCT 函數中參照其他工作表的位址是可以的,只要儲存格範圍的對照是正確的即可。如下公式:

工作表3的儲存格A2:=SUMPRODUCT((工作表1!$B$2:$B$11>5)*工作表2!B2:B11)

如果你覺得公式太長不好處理,可以參考以下的做法:

定義工作表1的儲存格B1:B11之名稱:次數

定義工作表2的儲存格B1:B11之名稱:數量

最重要的是領域必須設定為「活頁簿」。

Excel-跨工作表使用SUMPRODUCT函數

輸入公式:

工作表3的儲存格A2:=SUMPRODUCT((次數>5)*數量)

Posted by vincent at 痞客邦 PIXNET Guestbook(1) 人氣()

網友問到:在下圖左的 Excel 工作表中有一些廠商和產品的需求量,其中有一些是重覆的項目,如何將其轉換為下圖右的摘要統計表?

在下圖中,轉換過程中,空白列也一併消除了。該如何建立這個報表?

Excel-使用樞紐析表建立工作表的摘要統計表

網友詢問該用何公式來處理?我也還沒想出來。但是使用樞紐分析表,幾分鐘即可以輕易做出結果。

參考以下的步驟:

1. 選取儲存格A1:C20,建立樞紐分析表。

Excel-使用樞紐析表建立工作表的摘要統計表

2. 將「列」加入『廠商』,再加入『產品』。

3. 在「值」加入『需求量』。

Excel-使用樞紐析表建立工作表的摘要統計表

4. 將「值」的需求量由計數改為加總。

Excel-使用樞紐析表建立工作表的摘要統計表

5. 選取儲存格A4,按一下「欄位設定」。

6. 在「小計與篩選」標籤下的「小計」區中選取『無』。

Excel-使用樞紐析表建立工作表的摘要統計表

7. 在[版面配置與列印]標籤下:

選取『以列表方式顯示項目標籤』,再勾選『重複項目標籤』。

Excel-使用樞紐析表建立工作表的摘要統計表

8. 選取儲存格A4:C15,貼至原來工作表。

Excel-使用樞紐析表建立工作表的摘要統計表

Posted by vincent at 痞客邦 PIXNET Guestbook(0) 人氣()

網友問到的 Excel 問題:如下圖左有一個人員的排班基本表,其中每個日期分為二列,分別是早班和晚班的人員輪值名單,現在要轉換為下圖右的人員和日期的彙整表,該如何處理?對照

Excel-資料表重組(SUMPRODUCT,OFFSET,MATCH)


【公式設計與解析】

選取儲存格A1:A21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

儲存格I2:=OFFSET($B$1,SUMPRODUCT((OFFSET($C$1,MATCH($H2,
日期,0),0,2,4)=I$1)*ROW(OFFSET($C$1,MATCH($H2,日期,0),0,2,4)))-1,0)

複製儲存格I2,貼至儲存格I2:P11。

(1) MATCH($H2,日期,0)

查詢儲存格H2在日期陣列中的位置(列號),因為每一個日期都有二個符合,但是只會傳回第1個(列號較小者)。本例傳回「2」。

Excel-資料表重組(SUMPRODUCT,OFFSET,MATCH)

(2) OFFSET($C$1,第(1)式,0,2,4)

利用第(1)式的傳回值找出符合該日期的儲存格範圍,例如:儲存格H2為『9/1』,會傳回儲存格C2:F3。

(3) ROW(第(2)式)

利用第(2)式的儲存格範圍,藉由 ROW 函數傳回每個儲存格的列號陣列。以儲存格C2:F3為例,在 SUMPRODUCT 函數中會傳回 {2,3}。

(4) SUMPRODUCT((第(2)式=I$1)*ROW(第(2)式)

SUMPRODUCT 函數中的條件:第(2)式=I$1,因為本例只會有一個符合,所以乘以ROW(第(2)式後,即可得符合者的列號。公式=SUMPRODUCT({0,0,0,0,0,0,0,3},最後傳回『3』,表示『甲』在『第3列』。

(5) OFFSET($B$1,第(4)式-1,0)

根據第(4)式的傳回值,代入 OFFSET 函數於B欄中找出對應的位置,即為所求。

Posted by vincent at 痞客邦 PIXNET Guestbook(0) 人氣()

網友問到實用且簡單的問題:在下圖的 Excel 工作表中,如何將報到日期欄位尚未輸入資料者,整列以不同底色標示?

Excel-將資料表中未輸入資料者整列顯示不同色彩

參考以下的步驟:

1. 選取所有的資料,本例為儲存格A2:D26。

2. 在「設定格式化的條件」中新增一個規則。

規則類型:使用公式來決定要格式化哪些儲存格

公式:=$D2=""  (注意:D欄要使用絶對參照,而2列要使用相對參照。)

格式:粉紅色儲存格底色

Excel-將資料表中未輸入資料者整列顯示不同色彩

如果你的資料很多,透過「自動篩選」工具,在其中一個欄位中選取「依色彩排序」,再選取一個色彩(本例為粉紅色)。

Excel-將工作表中未輸入資料者整列顯示不同色彩

未輸入資料的欄位即會集合在一起,以方便輸入資料。

Excel-將工作表中未輸入資料者整列顯示不同色彩

Posted by vincent at 痞客邦 PIXNET Guestbook(1) 人氣()

You haven’t logged in yet, please use guest status to leave message. You can also log in with above service account and leave message

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

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

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

Please input verification code on left:

Cannot understand, change to another image

請輸入驗證碼