贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201610 (21)

瀏覽方式: 標題列表 簡短摘要

網友問到:在 Excel 的資料清單中,如何用公式篩選符合條件者?

參考下圖左,是一個『日期、編號、評語』的清單,現在要根據一個『編號』值,篩選出符合該編號的資料內容(日期和評語),該如何處理?

Excel-用公式篩選符合條件者(OFFSET,ROW,陣列公式)

【公式設計與解析】

選取儲存格B1:B26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號。

儲存格E3:

{=OFFSET($A$1,SMALL(IF(編號=$F$1,ROW(編號),999),ROW(1:1))-1,0)}

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

複製儲存格EE3,貼至儲存格E3:E14。

(1) IF(編號=$F$1,ROW(編號),999)

在陣列公式中判斷若是編號範圍的儲存格內容和儲存格F1相同者,傳回其列號陣列,若不是,則傳回「999」(這只是很大的一個數)。

(2) SMALL(IF(編號=$F$1,ROW(編號),999),ROW(1:1))

利用 SMALL 函數依序取出列號陣列中的第1, 2, 3, ... 較小值。當公式向下複製時,其中 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

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

將列號代入 OFFSET 函數,即可查詢到對應的儲存格內容。


同理,儲存格F3:

{=OFFSET($A$1,SMALL(IF(編號=$F$1,ROW(編號),999),ROW(2:2))-1,0)}

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

複製儲存格F3,貼至儲存格F3:F14。

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

讀者提問:下圖是 Excel 的資料表,如果在綠色區域中的S1~S8欄位中,根據藍色區域中的 S 對照 T 來列出橙色區域中的value。

例如:第2列中的S8位在T3欄位,查表得到T3=0.8,將其填入儲存格E2。

Excel-由兩個表格中查詢對應的結果(MATCH,OFFSET,VLOOKUP)

儲存格E2:=IFERROR(OFFSET($B$12,MATCH(E$1,$A2:$D2,0)-1,0),"")

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

(1) MATCH(E$1,$A2:$D2,0)

找出儲存格E1位於儲存格A2:D2範圍中的那個位置。

(2) OFFSET($B$12,MATCH(E$1,$A2:$D2,0)-1,0)

根據第(1)傳回的位置利用 OFFSET 函數傳回對應的儲存格內容。

(3) IFERROR(第(2)式,"")

若第(2)式的傳回值是錯誤訊息,則顯示空白。


若是原始資料如下安排:(儲存格A1:D1的內容與上圖不同)

Excel-由兩個表格中查詢對應的結果(MATCH,OFFSET,VLOOKUP)

公式調整如下:

儲存格E2:=IFERROR(OFFSET($A$1,0,MATCH(E$2,$A3:$D3,0)-1),"")

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


若是原始資料按排如下:(儲存格A1:D1的內容做了調整)

Excel-由兩個表格中查詢對應的結果(MATCH,OFFSET,VLOOKUP)

儲存格E2:=IFERROR(VLOOKUP(OFFSET($A$1,0,MATCH(E$1,$A2:$D2,0)-1),
$A$12:$B$15,2,FALSE),"")

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

(1) OFFSET($A$1,0,MATCH(E$1,$A2:$D2,0)-1)

找出儲存格E1內容所對應T1~T4中的那一個。

(2) VLOOKUP(第(1)式,$A$12:$B$15,2,FALSE)

根據第(1)式傳回的結果,查詢紅色區域中所對照的value。

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

網友問到一個很實用的問題:在 Excel 的資料表中,有部分欄位缺漏資料,如何挑出這些缺漏的記錄,以方便後續處理?

在下圖左的資料清單中含有四個欄位,其中姓名沒有缺漏,而性別、生日、餐食等有部分缺漏,在此要以「進階篩選工具」來挑出含有空白內容的記錄。

Excel-如何列出資料清單中任一個欄位有空白者(進階篩選)

做法很簡單,參考下圖左儲存格F1:I4的內容,請先輸入:

儲存格G2:『<=""』;儲存格H3:『<=""』;儲存格I4:『<=""』。

再進入「進階篩選」(選取[資料/排序與篩選]功能表區中的「篩選」),設定:

資料範圍:$A$1:$D$23;準則範圍:$F$1:$I$4;複製到:$F$7:$I$7。

Excel-如何列出資料清單中任一個欄位有空白者(進階篩選)

如果你有興趣使用公式來執行這個操作,請


儲存格F2:{=OFFSET($A$1,SMALL(IF(($B$2:$B$23="")+($C$2:$C$23="")+
($D$2:$D$23=""),ROW($A$2:$A$23),999)-1,ROW(1:1)),COLUMN(A:A)-1)}

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

公式中的『+』運算,相當於執行邏輯 OR 運算。

Excel-如何列出資料清單中任一個欄位有空白者(進階篩選)

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

在 Excel 中,在下圖中有類別和項目的清單,要如何才能產生不重覆的排列組合結果(參考下圖右)?

在下圖左中,有類別:甲、乙、丙、丁,項目:忠、孝、仁、愛,要產生其不重覆的排列組合結果,該如何處理?本篇將利用二種方法來處理。

Excel-不重覆的排列組合(公式,樞紐分析表


1. 使用公式

(1) 類別欄位

儲存格D2:=OFFSET($A$2,INT((ROW(1:1)-1)/4),0)

INT((ROW(1:1):當公式向下複製時產生「0,0,0,0,1,1,1,1,2,2,2,2,3,3,3,3」。

(2) 項目欄位

儲存格E2:=OFFSET($B$2,MOD(ROW(1:1)-1,4),0)

MOD(ROW(1:1)-1,4):當公式向下複製時產生「0,1,2,3,0,1,2,3,0,1,2,3,0,1,2,3」

複製儲存格D2:E2,貼至儲存格D2:E18。


2. 使用樞紐分析表工具

如果你不喜歡使用公式來處理,也可以透過「樞紐分析表」工具來自動產生。

你可以將類別清單和項目清單,放在相同或不同的工作表中。

Excel-不重覆的排列組合(公式,樞紐分析表 image

並選取[檔案/選項]功能,在[進階]標籤下找到「編輯自訂清單」按鈕,按一下這個按鈕以新增自訂清單。

Excel-不重覆的排列組合(公式,樞紐分析表

在自訂清單中,新增:忠、孝、仁、愛。

Excel-不重覆的排列組合(公式,樞紐分析表

回到類別清單中,建立樞紐分析表,勾選:新增此資料至資料模型。

Excel-不重覆的排列組合(公式,樞紐分析表

接著,對項目清單執行上述的動作。

Excel-不重覆的排列組合(公式,樞紐分析表

在樞紐分析表的「欄位清單」方塊中切換到「所有」標籤下,將兩個範圍都勾選,並且將「類別」插入「列」中,再將「項目」插入「列」中。(注意:項目要在類別之下)

Excel-不重覆的排列組合(公式,樞紐分析表

接著,選取列標籤中的一個儲存格(使其成為作用中欄位),按一下功能表中的「欄位設定」。然後在[欄位設定]對話框中的「版面配置與列」標籤下,勾選:以列表方式顯示項目標籤,並勾選:重複項目標籤。

Excel-不重覆的排列組合(公式,樞紐分析表

再切到「小計與篩選」標籤下,在[小計]區中勾選「無」。

Excel-不重覆的排列組合(公式,樞紐分析表

目前結果如下圖:(類別和項目目前尚未排序)。接著要執行正確排序的動作。

先在「列標籤」下拉式清單中選取「更多排序選項」:

Excel-不重覆的排列組合(公式,樞紐分析表

在[排序(類別)]對話框中,選取排序選項:遞增(類別),然後按一下「更多選項」按鈕。

Excel-不重覆的排列組合(公式,樞紐分析表

選取自訂排序順序:甲,乙,丙,列, ...。

Excel-不重覆的排列組合(公式,樞紐分析表

接著選取[項目]欄位中的一個儲存格,再選取[資料/排序],並在[排序(項目)]對話框中選取「遞增:項目」,並按一下「更多選項」按鈕。

Excel-不重覆的排列組合(公式,樞紐分析表

選取自訂排序順序:忠,孝,仁,愛。

Excel-不重覆的排列組合(公式,樞紐分析表

結果如下,即為所求。可以複製到其他位置使用了。

Excel-不重覆的排列組合(公式,樞紐分析表


【延伸練習】

你能運用上述的公式或是樞紐分析表工具,產生三個變項的所有排列組合?(如下圖)

Excel-不重覆的排列組合(公式,樞紐分析表

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

網友提問:在下圖中的 Excel 資料表,如何根據下圖右的『程度/標準』對照表,在下圖左中依據『耗時』欄位判斷是否合於標準?

如下圖,當耗時小於標準值時,以『V』標示。

Excel-查表時依條件顯示是否合於標準(VLOOKUP)


【公式設計與解析】

儲存格D2:=IF(C2<VLOOKUP(B2,{"易",30;"中",120;"難",240},2,FALSE),"V","")

用陣列:{"易",30;"中",120;"難",240} 來代替下表:(注意其『,』和『;』之差異)

image

公式也可改寫如下:

儲存格D2:=IF(C2<VLOOKUP(B2,$G$2:$H$4,2,FALSE),"V","")

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

網友問到一個 Excel 問題,雖然是不難,但是邏輯判斷工作對某些人而言,卻是會造成一些困擾,而不知如何下公式。例如:

網友原題目:

總共有A, B, C三個欄位,如果在A欄位key入『Y』或者『N』,如果是『N』的話,C欄位直接顯示『-』 ,如果是『Y』的話,C欄位會判讀B欄位有無輸入任何的符號或數值,如果有的會顯示『1』,沒有的話顯示『2』。

我將其翻譯為:

1. 若 A 是『N』,則 C 是『-』。

2. 若 A 是『Y』,則:

(1) 若 B 不是空白,則 C 是『1』。

(2) 若 B 是空白,則 C 是『2』。

如此,是否有助於你轉換為公式?

Excel-巢狀IF函數練習


【公式設計與解析】

儲存格C2:=IF(A2="N","-",IF(A2="Y",IF(NOT(ISBLANK(B2)),1,2),"輸入錯誤"))

複製存格C2,貼至儲存格C2:C7。

公式利用 3 個 IF 函數形成巢狀結構,而其中 ISBLANK(B2) 用以判斷儲存格B2是否為空白,並傳回 TRUE/FALSE 值。透過 NOT 函數將傳回值 TRUE/FALSE 值轉換為 FALSE/TRUE 值。

不論你輸入的是『Y、y』,Excel 視為二者相同。同理,『N、n』亦是。

你也可以將公式簡化為:

儲存格C2:=IF(A2="N","-",IF(A2="Y",IF(NOT(B2=""),1,2),"輸入錯誤"))

利用『""』(空字串),代替 ISBLANK 函數。

再簡化為:

儲存格C2:=IF(A2="N","-",IF(A2="Y",IF(B2<>"",1,2),"輸入錯誤"))

利用『<>』(不等於),來取代 NOT 函數的作用。

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

網友問到的 Excel 問題:如下圖A欄的日期是非數值的日期,無法以數值格式設定方式來調整格式(例如:dd/mm/yyyy→mm/dd/yyyy),該如何才能調整?

下圖中的A欄格式:dd/mm/yyyy,想要調整為C欄:mm/dd/yyyy,該如何處理?

Excel-調整非數值的日期格式(MID)

【公式設計與解析】

該例中的日期格式是固定的,均為:日2碼/月2碼/年4碼,所以可以直接用 MID 函數取出想要的部分。

儲存格C2:=MID(A2,4,3)&MID(A2,1,3)&MID(A2,7,4)

(1) MID(A2,4,3):取出儲存格C2中的1至3碼。

(2) MID(A2,1,3):取出儲存格C2中的4至6碼。

(3) MID(A2,7,4):取出儲存格C2中的7至10碼。

將(1)(2)(3)式之間以『&』運算子串接即可。

相同公式亦可套用在A欄格式:mm/dd/yyyy,調整為C欄:dd/mm/yyyy。

Excel-調整非數值的日期格式(MID)

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

先前的文章:

用Goole表單來設計線上測驗卷並且評分,還可以獲得測驗結果的統計分析

參考下圖,已經可以由回應結果中看到有一個欄位是『分數』欄位,不用再自行計算分數。但是,如果你「修改了正確的答案,或是修改了各題的得分」,該如何修正分數?

本篇文章要來練習如果要自行判斷得分,該如何處理?

取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)

1. 先下載回應結果為試算表格式。

取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)

2. 開啟這個 Excel 檔,並複製作答結果(儲存格C2:G9)。(參考下圖)

取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)

3. 在一個新的 Excel 檔案中貼上,位置安排如下圖(儲存格B5:F12)。

取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)

4. 在儲存格B2:G2,分別填入各題的標準答案。在儲存格C2:G2,填入各題的占分。

(可以在開放給他人填寫表單時,先行輸入一次正確答案)

5. 輸入公式:

儲存格G5:=SUMPRODUCT((B5:F5=$B$2:$F$2)*$B$3:$F$3)

複製儲存格G5,貼至儲存格G5:G12。

如果你的分數想要以比例來表示,參考下圖,公式也要調整:

取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)

儲存格G5:=SUMPRODUCT((B5:F5=$B$2:$F$2)*$B$3:$F$3*100)

公式中的參數『100』,乃指滿分為 100分。

最後,如果你想要「修改正確的答案,或是修改各題的得分」,只要在儲存格B2:F3中修改,得分隨即改變。

取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)

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

每隔一段時間,就有老師會問到在實務上會遇到的問題:如何運用 Excel 來產生隨機座位表?

例如下圖中,每按一次 F9 鍵,就可以產生一個隨機座位表,該如何處理?

Excel-產生隨機座位表(RAND,OFFSET,MATCH,ROW,COLUMN)


【公式設計與解析】

觀察I欄、J欄、K欄,除了座號和姓名之外,在I欄中多了一個亂數欄位,其儲存格內容:『=RAND()』。

假設學生有42個,選取儲存格I1:I43,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:亂數。

儲存格A4:=OFFSET($J$1,MATCH(SMALL(亂數,INT(((ROW(A4)-4)/3)*7)+
COLUMN(A4)),亂數,0),0)

複製儲存格A4,貼至儲存格A4:G4。複製儲存格A4:G4,再貼至下方對應的各列位置。

(1) INT(((ROW(A4)-4)/3)*7)

●式子中的參數『4』,乃因為儲存格A4是第 4 列。

●式子中的參數『7』,乃因為每一列有 7 個座位。

●式子中的參數『3』,乃因為每 3 列一組。

當公式向下複製時會產生儲存格A4=1、儲存格A7=8、儲存格A10=15、...。

(2) INT(((ROW(A4)-4)/3)*7)+COLUMN(A4)

當公式向右複製時,COLUMN(A4)=1→COLUMN(B4)2→ ... →COLUMN(G4)=7。

當公式向右複製時,第(2)式會產生 1, 2, 3, 4, 5, 6, 7。

當公式向下複製時,第 7 列會依序產生 8, 9, 10, 11, 12, 13, 14。

當公式向下複製時,第 10 列會依序產生 15, 16, 17, 18, 19, 20, 14。

(3) SMALL(亂數,INT(((ROW(A4)-4)/3)*7)+COLUMN(A4))

SMALL 函數中利用第(2)式的傳回值取得亂數陣列中的第 1, 2,3, ..., 43 個較小值。

(4) MATCH(SMALL(亂數,INT(((ROW(A4)-4)/3)*7)+COLUMN(A4)),亂數,0)

利用第(3)式傳回的亂數值,透過 MATCH 函數找到位於亂數欄位(I欄)的位置(傳回列號)。

(5) OFFSET($J$1,第(4)式,0)

由第(4)傳回的列號,代入 OFFSET 函數找到對應的J欄中的儲存格內容。

同理:

儲存格A5:=OFFSET($K$1,MATCH(SMALL(亂數,INT(((ROW(A4)-4)/3)*7)+
COLUMN(A4)),亂數,0),0)

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

網友問到的 Excel 的問題:如下圖,有一個日期和服務的清單列表,其中是三種服務的記錄,如何根據這個服務清單(下圖左),轉換為個別三個服務的日期清單(下圖右)

Excel-資料清單轉換(OFFSET,INDIRECT,ROW,陣列公式)

【公式設計與解析】

首先,選取儲存格A1:D27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、A服務、B服務、C服務。

接著輸入公式,儲存格F2:

{=OFFSET($A$1,SMALL(IF(INDIRECT(F$1)="V",ROW(日期),999),ROW(1:1))-1,0)}

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

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

(1) INDIRECT(F$1)

利用 INDIRECT 函數將儲存格F1的內容轉換為儲存格位址。例如:儲存格F1(「A服務」),轉換為儲存格B2:B27。(先前已定義名稱範圍)

(2) IF(INDIRECT(F$1)="V",ROW(日期),999)

在陣列公式中,判斷在儲存格範圍內的儲存格內容是否為「V」,若是,則傳回日期陣列的列號(利用 ROW 函數),若否,則傳回『999』。(這只是一個很大的數字,只要比儲存格範圍最大值大即可。)

(3) SMALL(IF(INDIRECT(F$1)="V",ROW(日期),999),ROW(1:1))

利用第(2)式所傳回的日期陣列,利用 SMALL 函數由小到大,依序取出日期對應的列號。(當公式向下複製時,會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。)

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

根據第(3)式取得的日期列號,代入 OFFSET 函數,即可找出對應的A欄內容(日期)。


或許,你的資料清單長成下圖這樣:

Excel-資料清單轉換(OFFSET,INDIRECT,ROW,陣列公式)

【公式設計與解析】

首先,選取儲存格A1:B27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、服務。

接著輸入公式,儲存格D2:

{=OFFSET($A$1,SMALL(IF(服務=D$1,ROW(日期),999),ROW(1:1))-1,0)}

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

複製儲存格D2,貼至儲存格D2:F22。

公式原理同上。

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

網友問到 Excel 的問題:如下圖,如何找出每個項目最高價的公司名稱?

觀察下圖,不同項目都有三個公司的標價,其中F欄標示出最高價,要從這二個資訊反推最高價的公司,該如何處理?

Excel-查表反推欄列標題(OFFSET,MATCH)

【公式設計與解析】

這個問題的概念像是要由表格內容反推欄或列的標題。

儲存格G2:=OFFSET($C$1,0,MATCH(F2,C2:E2,0)-1)

(1) MATCH(F2,C2:E2,0)

先由儲存格F2內容透過 MATCH 函數查到儲存格F2是在儲存格C2:E2中的那個位置(傳回第幾個)。本例傳回『3』(35在32,27,35中的第3個)。

(2) OFFSET($C$1,0,MATCH(F2,C2:E2,0)-1)

將第(1)代入 OFFSET 函數找出由儲存格C1開始的第n個位置的內容。本例將3代入,得到『丙公司』。(=OFFSET(C1,0,2))

複製儲存格G2,貼至儲存格G2:G11。

如果,你想略F欄的「最高價」(參考下圖),也可以修改公式如下:

儲存格F2:=OFFSET($C$1,0,MATCH(MAX(C2:E2),C2:E2,0)-1)

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

image

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

網友問到:在 Excel 的工作表中,如果已寫好了公式,如何因應可能增加資料而造成公式要跟著調整?

例如:下圖中要計算B欄清單中的數量總和,目前有16筆資料。如果增加一筆時,如何能不用修改公式,即可正確運算?

Excel-增加資料時不用修改公式(INDIRECT)

如果你使用公式:

儲存格E2:=SUM(B2:B17)

當新增一筆資料時,公式仍維持『=SUM(B2:B17)』,當然結果也不會有所調整。

因此,改用下列公式:

儲存格E5:=SUM(INDIRECT("B2:B"& COUNTA(B:B)))

COUNTA(B:B):計算B欄中有數值的儲存格個數。

INDIRECT("B2:B"& COUNTA(B:B)):利用 INDIRECT 函數將字串轉換為儲存格位址。如此,儲存格範圍即隨有資料的儲存格個數而變了。

Excel-增加資料時不用修改公式(INDIRECT)

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

網友想問:在 Excel 中如果有兩個固定間隔時間的清單,該如何找出兩者之間所有時間重疊者?

在下圖中:

條件A:在 9:00~13:00 中每間隔 3 分鐘的時間清單。

條件B:在 9:00~13:00 中每間隔 5 分鐘的時間清單。

想要找出條件A和條件B時間重疊者,如下圖右(D欄)。

Excel-找出兩個時間清單中重疊者(OFFSET,SMALL,ROW,陣列公式)


【公式設計與解析】

1. 產生由 9:00 開始間隔 3 分鐘的時間清單

儲存格A3:=9*1/24

儲存格A4:=A3+3/(24*60)

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


2. 產生由 9:00 開始間隔 5 分鐘的時間清單

儲存格B3:=9*1/24

儲存格B4:=B3+5/(24*60)

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


3. 產生重疊時間的清單

選取儲存格A2:B83(有資料的儲存格),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:條件A、條件B。

儲存格D3:{=OFFSET($B$3,SMALL(IF(COUNTIF(條件A,條件B),ROW(條件B),
999),ROW(1:1))-3,0)}

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

(1) COUNTIF(條件A,條件B)

在陣列公式中,計算條件B中每一項在條件A清單中的數量。(如果傳回 1,表示重疊;如果傳回 0,表示沒有重疊。)

(2) IF(COUNTIF(條件A,條件B),ROW(條件B),999)

在陣列公式中,若第(1)式的傳回值為 1(表示重疊),則傳回重疊者儲存格的列號;若第(1)式的傳回值為 0(表示沒有重疊),則傳回「999」(這只是一個任意很大的數值)。

(3) SMALL(IF(COUNTIF(條件A,條件B),ROW(條件B),999),ROW(1:1))

在陣列公式中,利用 SMALL 函數利用 ROW(1:1)=1,找出傳回的列號中的最小值。若公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...,即可依序找出列號中第 1, 2, 3, ... 小值的列號。

(4) OFFSET($B$3,第(3)式-3,0)

在陣列公式中,利用第 (3) 式的傳回值,代入第(4)式的 OFFSET 函數,即可找出對應的儲存格內容。

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

最近開始鼓勵學校同事在處理各類報表時能善用 Excel 的樞紐分析工具,因此提供以下的多個練習範例。

基本表:

Excel-樞紐分析報表格式變化練習


《練習一》

試練習呈現下列表格格式,該如何設定?

Excel-樞紐分析報表格式變化練習

在各區或中置入欄位名稱:

「欄」區域:『產品』,「列」區:『機型』,「值」區域:『銷售額』。

Excel-樞紐分析報表格式變化練習

點選儲存格A3、儲存格B3、儲存格A4,可以直接修改三個名稱:

Excel-樞紐分析報表格式變化練習


《練習二》

試練習呈現下列表格格式,該如何設定?

Excel-樞紐分析報表格式變化練習

在各區或中置入欄位名稱:

「列」區:先置入『產品』,再置入『機型』,「值」區域:『銷售額』。

Excel-樞紐分析報表格式變化練習

在儲存格A3上按一下,將『列標籤』修改為『產品機型』;

在儲存格B3上按二下,在自訂名稱中修改『加總-銷售額』為『銷售總額』。

Excel-樞紐分析報表格式變化練習

修改如下:

Excel-樞紐分析報表格式變化練習

選取儲存格A4(或任一產品名稱),再按一下[分析/作用中欄位]功能表區中的「欄位設定」。

在[小計與篩選]標籤下,將小計勾選為『無』:

Excel-樞紐分析報表格式變化練習

在[版面配置與列印]標籤下,選取『以列表方式顯示標籤』,勾選『重覆項目標籤』。

Excel-樞紐分析報表格式變化練習

結果如下:

Excel-樞紐分析報表格式變化練習


《練習三》

試練習呈現下列表格格式,該如何設定?

Excel-樞紐分析報表格式變化練習


《練習四》

試練習呈現下列表格格式,該如何設定?(每個業務員依各產品類別予以銷售額小計,並依每個業務員的銷售額由大到小排列)

Excel-樞紐分析報表格式變化練習

先產生如下的表格格式,在一個儲存格上按右鍵,選取[值的顯示方式/總計百分比]選項。

Excel-樞紐分析報表格式變化練習

結果如下:

Excel-樞紐分析報表格式變化練習

在[總計]欄位的任一儲存格上按右鍵,選取[排序/從最大到最小排序]選項:

Excel-樞紐分析報表格式變化練習

結果如下:

Excel-樞紐分析報表格式變化練習


《練習五》

試練習呈現下列表格格式,該如何設定?(分季/分月統計銷售額)

Excel-樞紐分析報表格式變化練習

在各區或中置入欄位名稱:

「列」區:先置入『日期』,再置入『店名』,「值」區域:『銷售額』(標籤改名:銷售總額)。

Excel-樞紐分析報表格式變化練習

結果如下:

Excel-樞紐分析報表格式變化練習

選取任一個銷售日期,在儲存格上按一下右鍵,選取「組成群組」:

Excel-樞紐分析報表格式變化練習

分別選取「月」和「季」:

Excel-樞紐分析報表格式變化練習

結果如下:

Excel-樞紐分析報表格式變化練習

最後,選取儲存格A5(季別中的一個),按一下[分析/作用中欄位]功能表區「欄位設定」。在[小計與篩選]標籤下的[小計]區中選取「自動」。

Excel-樞紐分析報表格式變化練習

結果如下:

Excel-樞紐分析報表格式變化練習


《練習六》

試練習呈現下列表格格式,該如何設定?(第二季的銷售前三名各月統計表,分店依銷售額由大到小排序)

Excel-樞紐分析報表格式變化練習

延續《練習五》的結果,按一下儲存格B3(分店)的下拉式清單,再選取「值篩選/前10項」選項:

Excel-樞紐分析報表格式變化練習

調整為「最前 3 項」(藉由銷售總額):

Excel-樞紐分析報表格式變化練習

結果如下:

Excel-樞紐分析報表格式變化練習

按一下儲存格A4(銷售日期)的下拉式清單,再選取「日期篩選/週期中的所有日期」選項,再選取「第二季」:

Excel-樞紐分析報表格式變化練習

結果如下:

Excel-樞紐分析報表格式變化練習

按一下儲存格B3(分店)的下拉式清單,選取「更多排序選項」:

Excel-樞紐分析報表格式變化練習

選取「遞減(Z到A)方式」,再選取下拉式清單中的「銷售總額」:

Excel-樞紐分析報表格式變化練習

結果如下:

Excel-樞紐分析報表格式變化練習


《練習七》

(1) 試自行練習呈現下列表格格式:店名和產品的樞紐分析,店名和產品分別依銷售數量由大至小排列。

Excel-樞紐分析報表格式變化練習

(2) 試自行練習呈現下列表格格式:機型和月份的樞紐分析,機型和月份分別依銷售數量由大至小排列。

Excel-樞紐分析報表格式變化練習

(3) 試自行練習呈現下列表格格式:產品和店名的樞紐分析,分別顯示各季的銷售數量,且產品和分店分別依銷售數量由大至小排列。

Excel-樞紐分析報表格式變化練習


《練習八》

試練習呈現下列表格格式:

報表版面配罝/壓縮模式:

Excel-樞紐分析報表格式變化練習

報表版面配罝/大綱模式:

Excel-樞紐分析報表格式變化練習

報表版面配罝/列表模式:

Excel-樞紐分析報表格式變化練習

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

網友問到這類的 Excel 應用問題:參考下圖,在加法、減法、乘法前面以『V』代表勾選,如果依勾選結果列出 X 和 Y 的運算式。

下圖中的例子為勾選『乘法』後,運算式為『X*Y=23*4=92』,該如何撰寫公式?

Excel-挑選不同運算子執行不同運算式(CHOOSE,SUMPRODUCT)


【公式設計與解析】

1. 使用輔助儲存格M1

儲存格M1:=(SUMPRODUCT((A1:F1="V")*COLUMN(A1:F1))+1)/2

在儲存格A1:F1中判斷那一個儲存格含有『V』,並傳回一個數字:

儲存格A1為『V』傳回 1;儲存格C1為『V』傳回 2;儲存格E1為『V』傳回 3。

儲存格D4:="X" & CHOOSE(M1,"+","-","*") & "Y=" & H1 & CHOOSE(M1,"+",
"-","*") & J1 & "=" & CHOOSE(M1,H1+J1,H1-J1,H1*J1)

CHOOSE(M1,"+","-","*"):根據儲存格M1的傳回值決定顯示那一個運算子『+、-、*』。

CHOOSE(M1,H1+J1,H1-J1,H1*J1)根據儲存格M1的傳回值決定執行那一個運算式。


2. 不使用輔助儲存格

如果你在工作表上不想顯示輔助儲存格,則可以改用定義名稱的方式來解決。

如下圖,定義名稱『OP』:

Excel-挑選不同運算子執行不同運算式(CHOOSE,SUMPRODUCT)

參照:=(SUMPRODUCT((A1:F1="V")*COLUMN(A1:F1))+1)/2 (與儲存格M1相同)

公式調整為:

儲存格D4:="X" & CHOOSE(OP,"+","-","*") & "Y=" & H1 & CHOOSE(OP,"+",
"-","*") & J1 & "=" & CHOOSE(OP,H1+J1,H1-J1,H1*J1)


【延伸練習】

如果設計為下拉式選單來挑選想要的運算,該如何設計?

Excel-挑選不同運算子執行不同運算式(CHOOSE,SUMPRODUCT)

儲存格I1:=VLOOKUP(A1,{"加法",1;"減法",2;"乘法",3},2,FALSE)

儲存格B4:="X" & CHOOSE(I1,"+","-","*") & "Y=" & D1 & CHOOSE(I1,"+",
"-","*") & F1 & "=" & CHOOSE(I1,D1+F1,D1-F1,D1*F1)

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

在 Excel 中如下圖左的資料表共有 1000 筆,其中包含了類別、項目和數量的資料。其中:類別內容為『甲/乙/丙/丁/戊/己』,項目內容為『子/丑/寅/卯/辰/巳』。要如何產出「各個類別中各個項目前三名數量總和的前四名」(如下圖右)?並且希望類別和項目都依由大到小遞減排序該如何處理?

例如:在下圖右中各類別的前四名是:甲→戊→丙→己。而甲的前三名是:卯→丑→子。

Excel-樞紐分析表應用與練習

這時候『樞紐分析表』工具就可以派上用場了!參考以下的步驟來練習:

1. 選取資料清單中的任一個儲存格,點選[插入/表格]功能表中的「樞紐分析表」。

Excel-樞紐分析表應用與練習

2. 在[建立樞紐分析表]對話框中確認分析的資料範圍後,接著顯示樞紐分析表欄位選項。

Excel-樞紐分析表應用與練習

3. 將「類別」欄位新增至「列」中,接著將「項目」欄位新增至「列」中。(注意:要將「項目」欄位置於「類別」欄位之下)。再將「數量」欄位新增至「值」中。

Excel-樞紐分析表應用與練習

4. 將「加總 - 數量」修改名稱為「小計」。

Excel-樞紐分析表應用與練習

Excel-樞紐分析表應用與練習

5. 點選 「類別」欄位的下拉式清單中,選取「值篩選/前10項」。

Excel-樞紐分析表應用與練習

6. 設定「類別」欄位要藉由「小計」欄位篩選最前 4 項(前四名)。

Excel-樞紐分析表應用與練習

7. 仿照步驟5和步驟6的做法,將「項目」欄位設定要藉由「小計」欄位篩選最前 3 項(前三名)。結果如下圖:

Excel-樞紐分析表應用與練習

8. 將儲存格A3中的「列標籤」修改為「類別」(即修改欄位名稱為「類別」)。

9. 選取儲存格A4(其中一個類別),然後點選「分析/作用中欄位」功能表區中的「欄位設定」。

10. 在[版面配置與列印]標籤下選取「以列表方式顯示項目標籤」,並勾選「重複項目標籤」。

Excel-樞紐分析表應用與練習

結果如下:

Excel-樞紐分析表應用與練習

11. 選取「小計」欄位中的任一個儲存格(本例:儲存格C5),再選取[編輯/排序與篩選]功能的「從最大到最小排序」。

Excel-樞紐分析表應用與練習

結果如下:

Excel-樞紐分析表應用與練習

12. 點選「類別」欄位中的篩選圖示,再選取「更多排序選項」。

Excel-樞紐分析表應用與練習

13. 選取「遞減(Z到A)方式」,再選取「小計」。(即以「小計」欄位由大到小排序)

Excel-樞紐分析表應用與練習

結果如下:

Excel-樞紐分析表應用與練習

複製樞紐分析結果,即為所求。

如果你不想要顯示每個類別的小計,則可以在[欄位設定]對話框中的「小計與篩選」標籤下,指定小計:無。

Excel-樞紐分析表應用與練習

最後,附上所有類別和項目的小計結果供檢驗樞紐分析結果是否正確。

Excel-樞紐分析表應用與練習

這樣的範例,你可以用在例如「業績報表」,像是業務員:甲、乙、丙、丁、戊、己,產品:子、丑、寅、卯、辰、巳。由業績前四名業務員來看其前三名的產品銷售業績。(參考下圖)

Excel-樞紐分析表應用與練習

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

網友根據前一篇文章:Excel-計算多欄多列交集的小計(SMPRODUCT),如下圖,如果想要列入小計的欄列交集處包含了文字(例如:A),則原公式會發生錯誤,該如何調整?

例如:人員『寅』和項目『乙』資料的交集是『A,A,59,A,79,A,A,78,21,69』,其中包含了 3 個A是非數,該如何計算數字和?

Excel-計算多欄多列交集的小計(SUM,IFERROR,陣列公式)


【公式設計與解析】

儲存格M3:{=SUM(IFERROR(B2:J18*(B1:J1=M2)*(A2:A18=M1),0))}

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

原先的公式:

儲存格M3:=SUMPRODUCT(B2:J18*(B1:J1=M2)*(A2:A18=M1))

當公式中執行『*』運算時,會因文字(A)傳回 #VALUE,而發生錯誤。

新的公式改用陣列公式,並且利用 IFERROR 函數將錯誤訊息轉換為『0』,再自行利用 SUM 函數予以加總,即為所求。

如果你將 SUM 函數以 SUMPRODUCT 函數置換結果會一樣。

儲存格M3:{=SUMPRODUCT(IFERROR(B2:J18*(B1:J1=M2)*(A2:A18=M1),0))}

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

情境:

日前出差到台南開會,一時間在我的 Inbox 中收到好朋友傳來了一封郵件,簡單的一句話:煩請取出圖片中的文字。仔細一看,原來是附件中的有一個圖檔(文字掃描結果),朋友想要取出其中的文字。

如果在電腦在身旁,應該是兩三下就解決。問題是身邊只有『手機』隨侍在側啊!該如何處理?

用手機中的App取出郵件附件中圖片檔裡的文字 用手機中的App取出郵件附件中圖片檔裡的文字

圖片中的文字如下圖所示,幸好其中的文字是標準字體組成。

用手機中的App取出郵件附件中圖片檔裡的文字

稍微想了一下,我把腦筋動到你我手機大概都有安裝的 App:Google Translate。

推想:因為 Google Translate App 可以取用圖片來翻譯,圖片來源又分為立即拍張照片,或是取用已有圖片檔。如果取用照片來翻譯,則必須先取得其中的文字。所以,我推定其會先對圖片執行「字元辨識」,而辨識後一定能取得其中的中文字。

參考以下的步驟:

1. 開啟 Google Translate App。

2. (重要)將待翻譯文字選取『中文』,將翻譯成文字選取「英文」(其實任何語言都行)。

3. 按一下照相機圖示(參考下圖左)。

4. 按一下圖片圖示(參考下圖右)。

用手機中的App取出郵件附件中圖片檔裡的文字 用手機中的App取出郵件附件中圖片檔裡的文字

5. 選取待翻譯的圖片,Google Translate 隨即展開文字掃描。

6. 掃描後的結果(藍色框)是其辨識後一組一組的詞。

7. 按一下「全選」,可以一次辨識全部文字。

用手機中的App取出郵件附件中圖片檔裡的文字 用手機中的App取出郵件附件中圖片檔裡的文字

8. 在辨識後的文字上點一下,即可點選「全部選取」。

用手機中的App取出郵件附件中圖片檔裡的文字 2016-10-05 01.27.20_resize

如此,你便取得所有圖片中的文字了,複製到其他地方,即可任何應用了!

(該範例圖片的辨識結果看來還不錯)

用手機中的App取出郵件附件中圖片檔裡的文字

這個實作範例看來還蠻有趣的,可以用手機來取得圖片中的文字,方便又實用。讓我在緊急時解決了一個不好處理的問題。

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

有網友想問:在 Excel 的工作表中有如下圖的資料表,如果給予人員和項目的內容,想要查詢交集資料的小計(總和),該如何處理?

例如:人員『卯』和項目『丙』資料的交集是『96,34,12,13』,其小計(總和)為155。該如何設計公式。

Excel-計算多欄多列交集的小計(SMPRODUCT)

【公式設計與解析】

儲存格M3:=SUMPRODUCT(B2:J18*(B1:J1=M2)*(A2:A18=M1))

公式中的『*』運算子相當於執行邏輯 AND 運算。

該公式除了可以計算多欄和多列的交集之小計(總和),相同公式適用以下的狀況(一欄多列的交集):

Excel-計算多欄多列交集的小計(SMPRODUCT)

相同公式適用以下的狀況(多欄一列的交集):

Excel-計算多欄多列交集的小計(SMPRODUCT)

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

最近因著 Pokémon Go 遊戲的流行,大家開始注意到:VR、AR、MR。上課時也試讓學生了解一般人對這三個關鍵字的關切狀況,在此藉助了 Google Trends 網站來了解大家的搜尋趨勢。

Google Trends:https://www.google.com.tw/trends

利用Google Trends來了解大家關注之關鍵字的熱度

你由功能選單可以由「探索」進入比較搜尋:

利用Google Trends來了解大家關注之關鍵字的熱度

你可以像以下這樣的比較:

利用Google Trends來了解大家關注之關鍵字的熱度

你可以輸入多個不同關鍵字來比較,並且選取地點(全球、國家、城市),再選取時間(過去5年、過去12個月、過去7天、過去1天、過去4小時、過去1小時等,或是自訂時間)

首先,你輸入一個關鍵字(本例為:VR),再按一下『比較』:

利用Google Trends來了解大家關注之關鍵字的熱度

接著輸入第二個關鍵字『AR』,再新增一個關鍵字『MR』,即可得三個的比較圖。

以VR/AR/MR比較,地點:全球/時間:過去5年。(熱門度:VR>AR>MR)

利用Google Trends來了解大家關注之關鍵字的熱度

全球不同區域的偏好:

利用Google Trends來了解大家關注之關鍵字的熱度

若將地點改成以『台灣』為範圍,和全球有不一樣的結果:

利用Google Trends來了解大家關注之關鍵字的熱度

改成以『台北』為範圍:

利用Google Trends來了解大家關注之關鍵字的熱度

若使用Virtual Reality(VR)、Augmented Reality(AR)、Mixed Reality(MR)比較。Augmented Reality(AR)在過去一年開始熱門了。

利用Google Trends來了解大家關注之關鍵字的熱度

改成以『台灣』為範圍:

利用Google Trends來了解大家關注之關鍵字的熱度

改成以『台北』為範圍:

利用Google Trends來了解大家關注之關鍵字的熱度

若改以虛擬實境、擴充實境、混合實境來比較。全球過去一年:

利用Google Trends來了解大家關注之關鍵字的熱度

台灣過去一年:

利用Google Trends來了解大家關注之關鍵字的熱度

台北過去一年:

利用Google Trends來了解大家關注之關鍵字的熱度

如果你設定在台北市由2016/7/1開始可以看到虛擬實境、擴充實境、混合實境的比較。

很明顯的2016/8/6這天,「擴增實境」的搜尋度大增,那天是 Pokémon Go 遊戲開放台灣下載的日子。

利用Google Trends來了解大家關注之關鍵字的熱度

改成英文關鍵字來比較更明顯:

利用Google Trends來了解大家關注之關鍵字的熱度

已建立的篩選器可以進行修改執行不同比較:

利用Google Trends來了解大家關注之關鍵字的熱度

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

1 2

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼