贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201601 (30)

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

有網友問到:在 Excel 中如何計算各類茶飲的去冰或少冰數量?如下圖中,有多種飲品(紅茶、綠茶、奶茶、清茶),特調為少冰或去冰,如何計算各種項目特調後的數量?

Excel-計算各類茶飲的去冰或沙冰數量(SUMPRODUCT)


【公式設計與解析】

儲存格F2:=SUMPRODUCT(($B$2:$B$27=E2)*($C$2:$C$27=""))

有的讀者對於使用 SUMPRODUCT 函數並不熟悉,平常都是使用 SUM 函數或是 SUMIF 函數。公式中的「*」,在運算時相當於執行邏輯 AND 運算,兩個關係運算會傳回 TRUE/FALSE 陣列。在執行「*」運算時,會將 TRUE/FALSE 陣列轉換為 1/0 陣列,在 SUMPRODUCT 函數中執行『乘積和』的動作。

儲存格G2:=SUMPRODUCT(($B$2:$B$27=E2)*($C$2:$C$27="少冰"))

儲存格H2:=SUMPRODUCT(($B$2:$B$27=E2)*($C$2:$C$27="去冰"))

複製儲存格F2:H2,貼至儲存格F2:H5。

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

有網友問到:如何在 Excel 中,當要設計二層下拉式清單時,可以跨工作表使用?例如:在下圖中是一些類別:『季別、月份、星期、天干、地支』的不同項目:

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)

當我們在另一個工作表中要設計一個下拉式選單來根據類別,再選取項目,該如何處理?

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)

先前的另一篇文章:Excel-製作二層的下拉式清單來輸入資料(INDIRECT,資料驗證),做法是將二層選單內容和選單放在同一個工作表中,讀者可以自行參考。

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)

如果要跨工作表使用二層下拉式選單,則可以調整如下的做法:

1. 選取儲存格A1:E1,定義名稱:類別。

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)

這個名稱的範圍,記得要指定為:活頁簿。(如此才能跨工作表)

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)


2. 將各個類別依其標題設定名稱

(1) 選取儲存格A1:E13。

(2) 按 Ctrl+G 鍵,開啟[到]對話框。

(3) 按一下[特殊]按鈕,開啟[特殊目標]對話框。

(4) 選取[常數]選項,按一下[確定]按鈕。

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)

目前,被選取的儲存格如下圖:

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)

(5) 按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:季別、月份、星期、天干、地支。

(Excel 自動設定這幾個名稱的範圍都是『活頁簿』)

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)


3. 設計下拉式清單

(1) 在儲存格B1中設定資料驗證

儲存格內允許:清單;來源:=類別。(類別為先前已設定儲存格範圍的名稱)

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)

(2) 在儲存格B2中設定資料驗證

儲存格內允許:清單;來源:=INDIRECT($B$1)。(利用 INDIRECT 函數將儲存各B1的人內容轉換為儲存格範圍的名稱)

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)


如此,便完成了『項目』會隨『類別』的改變而改變的二層下拉式選單的設計。

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)

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

有網友問到:如何在 Excel 中,利用一個多類別的折扣對照表來查詢各種數量的折扣。例如下圖中,分別有陸運、海運、空運三類的不同數量/折扣的對照表,要如何設計公式,只要挑選運別,輸入數量後,自動產生折扣值?

先前的一篇文章:Excel-在兩個資料表中查詢(VLOOKUP),已有一些說明。本文用不一樣的方式來操作。在下圖中,以陸運為例:數量超過200,折扣為95%;數量超過300,折扣為92%。

Excel-在多個類別中查詢(VLOOKUP,INDIRECT)


【公式設計與解析】

1. 為各種類別命名儲存格範圍。

(1) 選取儲存格E2:F9。

(2) 選取[公式/已定義名稱]功能表中的「定義名稱」選項。

Excel-在多個類別中查詢(VLOOKUP,INDIRECT)

(3) 在[名稱]方塊中,已自動產生「陸運」,按下[確定]按鈕即可。

(如果沒有自動產生,請自行輸入「陸運」)。

Excel-在多個類別中查詢(VLOOKUP,INDIRECT)

(4) 依步驟(3),再設定「海運」和「空運」。


2. 設計下拉式選單

(1) 選取儲存格A2:A18。

(2) 選取[資料/資料工具]功能表中的「資料驗證/資料驗證」選項。

(3) 設定資料驗證:

儲存格內允許:『清單』;來源:『陸運,海運,空運』。

Excel-在多個類別中查詢(VLOOKUP,INDIRECT)


3. 設計公式

(1) 儲存格C2:=VLOOKUP(B2,INDIRECT(A2),2,TRUE)

INDIRECT(A2):將儲存格A2中的文字轉換為一個儲存格名稱,該名稱在步驟1中已先行定義好名稱了。

再透過 VLOOKUP 函數利用查表方式找出數量對應的折扣,注意:要選取參數:TRUE

(2) 複製儲存格C2,貼至儲存格C2:C18。

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

有網友問到一個 Excel 的問題:如果要將一個數值分配給指定的項目數,如何設計公式能自動分配,並使其總和與原數值相同?

參考下圖中的例子,數值24833要分配給7個項目,其中有幾個會比其他數值多 1。分配後的總和與原數值相同。

Excel-將一個數值分配給多個項目,且總和與原數值相同(INT,MOD,ROW)

(1)

儲存格A4:=ROW(1:1)

儲存格B4:=INT($B$1/$D$1)+(ROW(1:1)<=MOD($B$1,$D$1))

INT($B$1/$D$1):取得『數值/分配數』的商數。

ROW(1:1)<=MOD($B$1,$D$1):判斷是否要『加 1』。該判斷結果和上式相加時,若判斷結果若為 FALSE,則視為 0,若判斷結果若為 TRUE,則視為 1。

複製儲存格A4:B4,貼至儲存格A4:B19。


(2)

儲存格D4:=IF(ROW(1:1)<=$D$1,ROW(1:1),"")

儲存格E4:
=IF(ROW(1:1)<=$D$1,INT($B$1/$D$1)+(ROW(1:1)<=MOD($B$1,$D$1)),"")

判斷項目個數如果超過分配數,則儲存格顯示空白。

複製儲存格D4:E4,貼至儲存格D4:E19。

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

網友問到:如何在 Excel 的工作表中設計間隔列使用不同色彩?如下圖,分別呈現間隔1~8列的不同色彩。一般都是使用『設定格式化的條件』來設定即可完成。

Excel-間隔列套用不同色彩(MOD,ROW)

在以下的示意圖中,假設原始儲存格底色為『淺綠色』,我們只要讓顯示 TRUE 的儲存格顯示不同色彩(例如『淺藍色』)即可。

Excel-間隔列套用不同色彩(MOD,ROW)

如果要間隔1列顯示不同儲存格底色,則參考以下步驟:

1. 選取儲存格B2:B24。

2. 在設定格式化的條件中編輯格式規則:

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

規則:=MOD(ROW()-2,2)<1

儲存格B2的ROW()為2,儲存格B3的ROW()為3,依此類推。

ROW()-2:因為資料由第 2 列開始。使用 MOD 函數來求取列數減 2 後再除以 2 的餘數,如果小於 1 則傳回 TRUE;如果等於 1 則傳回 FALSE

設定格式:淺藍色。(上式傳回 TRUE 者,回套用格式『淺藍色』)

Excel-間隔列套用不同色彩(MOD,ROW)

如果要修改為間隔 2~8 列,則參考:

間隔1列,規則:=MOD(ROW()-2,2)<1

修改為:

間隔2列,規則:=MOD(ROW()-2,4)<2

間隔3列,規則:=MOD(ROW()-2,6)<3

間隔4列,規則:=MOD(ROW()-2,8)<4

間隔5列,規則:=MOD(ROW()-2,10)<5

間隔6列,規則:=MOD(ROW()-2,12)<6

間隔7列,規則:=MOD(ROW()-2,14)<7

間隔8列,規則:=MOD(ROW()-2,16)<8

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

最近孩子問到:在 Word 文件中如何將一篇文章排版成放在稿紙中的樣子?這是非常容易的,因為 Word 已內建有稿紙功能。你只要點選[版面配置/稿紙]選單中的「稿紙設定」功能。

Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)

在[稿紙設定]對話框中挑選格線的格式、列數x欄數、格線色彩等,再選取頁面方向和設定頁首/頁尾等。

Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)

Word 會自動將文字置入你設定的稿紙格式中。(本例有勾選「允許標點符號益出邊界」)

Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)

如果你在 Excel 的活頁簿中,也想要達到這種效果呢?

在下圖中,一段文字已經置於儲存格A1中,假設每列要顯示 20 個字。

儲存格A3:=MID($A$1,(ROW(1:1)-1)*20+COLUMN(A:A),1)

複製儲存格A3,貼至儲存格A3:T14。

如果要調整每列顯示的字數,只要改變『20』的數值。

Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)

在下圖中,一段文字已經置於儲存格A1中,假設每欄要顯示 20 個字。

儲存格L3:=MID($A$1,ROW(1:1)+(12-COLUMN(L:L))*20,1)

複製儲存格L3,貼至儲存格A3:L22。

其中L欄即為第12欄,所以如果要改變欄數,只要改變『12』和『L:L』數值。

Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)

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

在 Excel 中通常會使用 ROUND 函數來調整小數和整數的四捨五入動作,在此,要以 INT 函數來模擬 ROUND 函數的動作,並分小數和整數二個部分來處理。

1. 小數部分

在下圖中,分別呈現取小數 1 位至小數 6 位的結果。

Excel-利用INT函數模擬整數和小數的四捨五入(ROUND)

(1) 取小數 3 位四捨五入

儲存格B4:=ROUND(B$1,3)

觀察函數中的參數『3』。

儲存格B4:=INT(B$1*10^3+0.5)/10^3

觀察函數中的參數『3』和運算符號『*、/』。

(2) 取小數 6 位四捨五入

儲存格B7:=ROUND(B$1,6)

儲存格B7:=INT(B$1*10^6+0.5)/10^6

觀察函數中的參數『6』和運算符號『*、/』。


2. 整數部分

Excel-利用INT函數模擬整數和小數的四捨五入(ROUND)

(1) 取整數 3 位四捨五入

儲存格B4:=ROUND(B$1,-3)

觀察函數中的參數『-3』。

儲存格B4:=INT(B$1/10^3+0.5)*10^3

觀察函數中的參數『3』和運算符號『*、/』。

(2) 取整數 6 位四捨五入

儲存格B7:=ROUND(B$1,-6)

儲存格B7:=INT(B$1/10^6+0.5)*10^6

觀察函數中的參數『6』和運算符號『*、/』。


【綜合比較】

小數:儲存格B7:=ROUND(B$1,6)

整數:儲存格B7:=ROUND(B$1,-6)

要取小數位數 6 位,則參數為『6』;要取整數位數 6 位,則參數為『-6』。

小數:儲存格B7:=INT(B$1*10^6+0.5)/10^6

整數:儲存格B7:=INT(B$1/10^6+0.5)*10^6

要取小數和整數 6 位,則參數都是『10^6』;要取小數位數時『先乘再除』、要取整數位數時『先除再乘』。

依此類推...。

(本文其實是為了給學生參加統一入學測驗計算機概論的考試準備時,方便記憶用的。)

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

學校同仁問到:在 Word 文件中,如果想要將文件中的某些文字置換成一個圖示,該如何處理較為快速且方便。例如:在下圖中的文件中有許多個 Google Chrome 字樣,想要快速置換成 Chrome 的圖示(在文件第一列),該如何處理?

Word-在文件中將文字置換成圖片

參考以下步驟:

1. 將複製這個圖示。(在剪貼簿中可以看到這個圖示)

Word-在文件中將文字置換成圖片

2. 在[常用/編輯]功能表中選取「取代」功能。

3. 在[尋找與取代]對話框的[取代]標籤下,按一下[更多]按鈕。

4. 在[尋找目標]文字方塊中輸入:Google Chrome;在[取代為]文字方塊中輸入『^c』。

(或是將插入點移至[取代為]文字方塊中,按一下[指定方式]按鈕,再選取「[剪貼簿]內容」,也會自動產生『^c』。)

Word-在文件中將文字置換成圖片

5. 按下[全部取代]按鈕。

結果即會將所有的 Google Chrome 置換成指定的圖示。

Word-在文件中將文字置換成圖片

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

同仁問:有時候想要利用手邊的照片,做一個自動播放照片的1分鐘的影片,並且希望加上音樂,很簡單的一個小動作,不知取用何種軟體來製作,才能省時又省事。我的做法是利用手邊辦公室電腦裡就有的軟體『PowerPoint』即可,不需再另外找任何軟體。

本例以 PowerPoint 2013 來介紹。

1. 先將照片和標題放在 12 張投影片中(預定每張播放5秒)。

使用PowerPoint做為照片轉影片工具

2. 在第一張投影片中插入一個音訊(例如:MP3檔)。

3. 選取這個音訊圖示,在[播放]功能表中,設定播放的動作:

(1) 按一下「音訊模式/在背景播放」。

(2) 在「音訊選項」中勾選:『放映時隱藏』。

(3) 在「音訊選項」中設定開始:自動。

(4) 在「音訊選項」中勾選:『跨投影片播放』。

(5) 在「音訊選項」中勾選:『循環播放,直到停止』。

使用PowerPoint做為照片轉影片工具

4. 切換至「投影片瀏覽」模式,並選取所有投影片。

5. 在[切換至此投影片]區中設定一個切換效果。

6. 在[切換]功能表的「預存時間」中設定:

(1) 勾選:每隔,輸入秒數(本例為:4.0秒)

(2) 在「期間」輸入秒數(本例為:1.5秒)

使用PowerPoint做為照片轉影片工具

7. 選取[檔案/匯出]功能表,點選「建立視訊」。

8. 選取投影片要用在何種媒體(本例:電腦與HD顯示器)及是否使用錄製的時間和旁白。

9. 設定秒數(本例為5秒)。

10. 按一下[建立視訊]按鈕。

使用PowerPoint做為照片轉影片工具

最後產生一個 MP4 影片檔。

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

根據前一篇文章:Excel-14個樞紐分析表應用練習,本篇文章要以公式模擬這些樞紐分析的動作。

資料來源是在一個 Excel 工作作的銷售記錄的資料清單中,含有欄位:日期、店名、業務員、產品代碼、機型、單價、數量、銷售額(如下圖)。現在,取用這個資料清單來練習樞分析表的操作,以下使用 Excel 2013 為例,資料來源有 700 筆以上。

【準備工作】

選取資料表中所有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、店名、業務員、產品、代碼、機型、單價、數量、銷售額。

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

在另一個工作表中含有產品及業務員的基本資料。也是先定義名稱:代碼清單、產品清單、機型清單、單價清單、店名清單。

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)


1. 計算各店的銷售總額

儲存格E4:=SUMPRODUCT((店名=D4)*銷售額)

複製儲存格E4,貼至儲存格E4:E10。

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)


2. 計算各分店每個業務員的銷售總額

儲存格G3:=SUMPRODUCT((店名=E4)*(業務員=F4)*銷售額)

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

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)


3. 可篩選各店中各種機型的銷售總額

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

儲存格E4:=SUMPRODUCT((機型=D4)*(店名=$E$1)*銷售額)

複製儲存格E4,貼至儲存格E4:E18。

在儲存格E1設定資料驗證,其中的準則為:

儲存格內允許:清單

來源:=店名清單(相當於『仁愛店,民生店,民族店,民權店,和平店,忠孝店,信義店』)

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)


4. 依各產品篩選每個分店各種機型的銷售總額(以矩陣表格顯示)

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

在儲存格B14中設計產品清單的下拉選單,在資料驗證的準則中設定:

儲存格內允許:清單

來源:=產品清單

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

儲存格B18:=IF(COLUMN(A:A)<=COUNTIF(基本資料!$B2:$B16,$B$14),OFFSET
(基本資料!$C$1,MATCH($B$14,基本資料!$B2:$B16,0)+COLUMN(A:A)-1,),"")

產品資料置於「基本資料!$B2:$B16」。當選取儲存格B14,則儲存格B17:E17的內容隨之變動。

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

儲存格18:=SUMPRODUCT((店名=$A18)*(機型=B$17)*銷售額)

複製儲存格B18,貼至儲存格B18:E24。

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

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

在一個 Excel 工作作的銷售記錄的資料清單中,含有欄位:日期、店名、業務員、產品代碼、機型、單價、數量、銷售額。取用這個資料清單來練習樞分析表的操作。

以下使用 Excel 2013 為例,資料來源有 700 筆以上。

Excel-14個樞紐分析表應用練習


1. 計算各店的銷售總額

在樞紐分析表欄位中,設定:

列:店名;值:銷售總額。並且更改儲存格A3和儲存格B3的標籤名稱。

Excel-14個樞紐分析表應用練習


2. 計算各分店每個業務員的銷售總額(大綱模式顯示)

列:店名(1)、業務員(2);值:銷售總額。

Excel-14個樞紐分析表應用練習


3. 計算各分店每個業務員的銷售總額(表格模式顯示)

列:店名(1)、業務員(2);值:銷售總額。

Excel-14個樞紐分析表應用練習

選取一個店名的儲存格,在『店名』欄位中設定。

Excel-14個樞紐分析表應用練習

在[小計與篩選]標籤下,『小計』設定:無。

Excel-14個樞紐分析表應用練習

在[版面配置與列印]標籤下,設定:

選取『以列表方式顯示項目標籤』;勾選『重覆項目標籤』。

Excel-14個樞紐分析表應用練習


4. 篩選銷售總額『前三名』店家

Excel-14個樞紐分析表應用練習

選取『店名』欄位,再選取[值篩選/前10項]選項:

Excel-14個樞紐分析表應用練習

設定:『最前』+『3』+『項』。

Excel-14個樞紐分析表應用練習


5. 依各店篩選業務員銷售前三名,並依銷售總額遞減排序(表格模式顯示)

Excel-14個樞紐分析表應用練習

選取『業務員』欄位,再選取[值篩選/前10項]選項,設定:『最前』+『3』+『項』。

然後,選取『業務員』欄位,選取「更多排序選項」選項。

Excel-14個樞紐分析表應用練習

選取:遞減,再選取「銷售總額」欄位。

Excel-14個樞紐分析表應用練習


6. 依各產品篩選業務員銷售前三名,並依銷售總額遞減排序(表格模式顯示)

選取『業務員』欄位,再選取[值篩選/前10項]選項,設定:『最前』+『3』+『項』。

選取『業務員』欄位,選取「更多排序選項」選項。

在[排序]對話框中,選取:遞減,再選取「銷售總額」欄位。

Excel-14個樞紐分析表應用練習


7. 依各產品的各種機型計算銷售總額,並依銷售總額遞增排序(表格模式顯示)

選取『產品』欄位,選取「更多排序選項」選項。

在[排序]對話框中,選取:遞增,再選取「銷售總額」欄位。

Excel-14個樞紐分析表應用練習


8. 可篩選各店中各種機型的銷售總額

在樞紐分析表欄位中,設定:

篩選:店名;列:機型;值:銷售總額。

Excel-14個樞紐分析表應用練習

只要選取某一家店,即可篩選該店的資料。(可單選/複選)

Excel-14個樞紐分析表應用練習


9. 依各產品篩選每個分店各種機型的銷售總額(表格模式顯示)

在樞紐分析表欄位中,設定:

篩選:產品;列:店名(1)、機型(2);值:銷售總額。

Excel-14個樞紐分析表應用練習

只要選取某一產品,即可篩選該產品的資料。(可單選/複選)

Excel-14個樞紐分析表應用練習


10. 依各產品篩選每個分店各種機型的銷售總額(以矩陣表格顯示)

在樞紐分析表欄位中,設定:

篩選:產品;列:店名;欄:機型;值:銷售總額。

Excel-14個樞紐分析表應用練習

Excel-14個樞紐分析表應用練習


11. 依各業務員列出每個產品的各種機型的銷售總額(以矩陣表格顯示)

(自行練習)

Excel-14個樞紐分析表應用練習


12. 取得一家店中的一種產品銷售清單

如果產生了一個商店和產品的銷售總額表(如下圖),如果想要知道一家店中的一種產品銷售清單,該如何處理?其實很簡單,例如,要知仁愛店的冰箱的銷售清單,則只要在儲存格B5上按二下即可。

Excel-14個樞紐分析表應用練習

Excel 會在一個新的工作表列出該銷售清單:

Excel-14個樞紐分析表應用練習


13. 使用交叉分析篩選器來輔助篩選

當使用樞紐分析表工具建立了店名、代碼和產品計算銷售總額的分析表,如下圖:

Excel-14個樞紐分析表應用練習

在[樞紐分析工具]功能表中選取「插入交叉分析篩選單」,選取「店名」。

Excel-14個樞紐分析表應用練習

使用這個篩選器也可以快速篩選想要的內容:

Excel-14個樞紐分析表應用練習

自行練習以下的樞紐分析結果:

Excel-14個樞紐分析表應用練習


14. 使用時間表來篩選日期

建立一個含有日期的樞紐分析表(如下圖),你可以在[樞紐分析工具]功能表中選取「插入時間表」,選取「日期」。

你可以在日期區間(年、季、月、天)中使用捲軸來指定要篩選的日期範圍。

Excel-14個樞紐分析表應用練習

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

下圖右是一個 Excel 的資料表,是一個矩陣式的資料清單,要如何篩選出同類型的資料?例如:選取『戊』開頭的資料內容。

Excel-在資料矩陣中篩選資料(SUBSTITUTE,OFFSET,INT,MOD)


【公式設計與解析】

(1)

儲存格A2:{=SMALL(IF((SUBSTITUTE($D$1:$H$10,$B$1,"")<>$D$1:$H$10),
(ROW($D$1:$H$10)-1)*5+COLUMN($D$1:$H$10),999),ROW(1:1))-3}

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

SUBSTITUTE($D$1:$H$10,$B$1,"")<>$D$1:$H$10:利用 SUBSTITUTE 函數判斷在儲存格陣列中以儲存格B1文字置換為空白,其結果如果和原儲存格陣列內容不相同者,表示該儲存格內容含有儲存格B1的文字。

(ROW($D$1:$H$10)-1)*5+COLUMN($D$1:$H$10):將資料矩陣的位置轉換一個數值。

參數 999,是隨意假設一個較大的數值。

在陣列公式中以 SMALL 函數配合 ROW 函數,依序取出第 1, 2, 3, 4, ...較小的數值。


(2)

儲存格B2:=OFFSET($D$1,INT((A2-1)/5),MOD(A2-1,5))

INT((A2-1)/5):藉由儲存格A2的數值,計算資料位於第幾列。

MOD(A2-1,5):藉由儲存格A2的數值,計算資料位於第幾欄。

Excel-在資料矩陣中篩選資料(SUBSTITUTE,OFFSET,INT,MOD)

複製儲存格A2:B2,貼至儲存格A2:B19。

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

網友問到:在 Excel 的工作表中有一個資料清單(如下圖左),想要依『日期』欄位來統計每個人員在各個月份的時數小計(如下圖右),該如何處理?

Excel-依日期中的月份對個人小計(SUMPRODUCT)


【公式設計與解析】

選取儲存格A1:C26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、日期、時數。

表<1>

儲存格F2:
=SUMPRODUCT((人員=$E2)*(VALUE(LEFT(日期,2))=COLUMN(A:A))*時數)

LEFT(日期,2):取出『日期』陣列每個儲存格左邊 2 個字(表示月份),取出的資料為『文字』型態。

VALUE(LEFT(日期,2)):利用 VALUE 函數將上式的文字轉換為『數字』型態。

COLUMN(A:A):向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:CA)=3→...。

表<2>

儲存格F15:
=SUMPRODUCT((人員=F$14)*(VALUE(LEFT(日期,2))=ROW(1:1))*時數)

ROW(1:1):向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。


【延伸學習】

公式=SUMPRODUCT((人員=F$14)*(VALUE(LEFT(日期,2))=ROW(1:1))*時數)

公式中的運算子『*』,會在運算過程中將 TRUE/FALSE 陣列藉由運算轉換為 1/0 陣列。

相當於:

公式=SUMPRODUCT(--(人員=F$14),--((VALUE(LEFT(日期,2))=ROW(1:1))),時數)

--(人員=F$14):將 TRUE/FALSE 陣列藉由運算轉換為 1/0 陣列。

--((VALUE(LEFT(日期,2))=ROW(1:1))):原理同上式。

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

網友問到一個有趣的問題:如何使用 Excel 來計算將一張大紙最多可剪裁成幾張小紙?如下圖,一張大紙在剪裁成小紙時,不論長或寬不見得都能剛好剪成是小紙的倍數,可能會有一些剩餘的部分。如何計算能剪裁的最多數量?

Excel-求一張大紙最多可剪裁成幾張小紙(INT,MAX)

如果要以 Excel 的公式來處理,可以試試:

儲存格D2:=MAX(INT(B2/B7)*INT(B3/B6),INT(B2/B6)*INT(B3/B7))

Excel-求一張大紙最多可剪裁成幾張小紙(INT,MAX)

有二種計算方式:

(1) (大紙的長/小紙的長)*(大紙的寬/小紙的寬)

公式:=INT(B2/B7)*INT(B3/B6),本例為 330。

INT 函數作用在於取兩數相除的整數商。

(2) (大紙的長/小紙的寬)*(大紙的寬/小紙的長)

公式:=INT(B2/B6)*INT(B3/B7),本例為 294。

再取第(1)和第(2)的最大值,本例為 330。

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

網友問到:在 Excel 的資料清單中(如下圖),有A、B、C三種狀態,要依下列條件評估狀態:

(1)三個中有一個『高負荷』,結果為『高負荷』;

(2)沒有『高負荷』時,三個中有一個『中負荷』,結果為『中負荷』;

(3)三個都為『低負荷』,結果為『低負荷』

Excel-利用巢狀IF結構判斷三種狀態(IF,COUNTIF)v


【公式設計與解析】

儲存格D2:=IF(COUNTIF(A2:C2,"高負荷"),"高負荷",IF(COUNTIF(A2:C2,
"中負荷"),"中負荷","低負荷"))

COUNTIF(A2:C2,"高負荷"):找出三個儲存格中符合『高負荷』者有幾個。

COUNTIF(A2:C2,"中負荷"):找出三個儲存格中符合『中負荷』者有幾個。

IF 函數的條件判斷式中,等於 0 的數視為 FALSE,0 以外的數視為 TRUE。

利用二個 IF 函數形成巢狀結構,可以判斷三種狀態。

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

網友問了一個問題:在 Excel 的工作表中想要根據一個矩陣表(雙條件)的資料來查詢對應的結果,該如何處理?

以下圖為例,在表<1>中是一個某疾病風險(三個數字區間)和工作負荷(三個文字項目)對應於發病風險的對照表(其中資料為虛擬),如何以兩個條件查詢對應的發病風險。

Excel-雙條件查詢(INDEX,OFFSET,VLOOKUP,MATCH)


【公式設計與解析】

因為有些網友是初學者,所以用比較複雜的公式內容來表示,讓網友能知悉公式和查表內容的對照關係。


(1) 查表內容以常數表示

{"低度風險","中度風險","中度風險";"中度風險","中度風險","高度風險";"中度風險","高度風險","高度風險"}:查表的資料內容,相當於儲存格D5:F7。(注意其中的『,』和『;』,同一欄的不同資料以『,』分隔,不同欄的資料以『;』隔開。)

MATCH(B2,{"低負荷","中負荷","高負荷"},0):依儲存格B2內容查詢位於 {"低負荷","中負荷","高負荷"} 陣列中的第 n 個。(其中參數 0,表示每個字都要符合才行。)

VLOOKUP(A2,{0,1;0.1,2;0.2,3},2,TRUE):依儲存格A2內容查詢在 {0,1;0.1,2;0.2,3} 陣列中對應第 2 欄的第 m 個。(其中參數 TRUE,表示數字取大約接近即可。)

Excel-雙條件查詢(INDEX,OFFSET,VLOOKUP,MATCH)

儲存格C2:=INDEX({"低度風險","中度風險","中度風險";"中度風險","中度風險",
"高度風險";"中度風險","高度風險","高度風險"},MATCH(B2,{"低負荷","中負荷",
"高負荷"},0),VLOOKUP(A2,{0,1;0.1,2;0.2,3},2,TRUE))

公式相當於:=INDEX(資料內容,n,m)


(2) 查表內容轉換為分數矩陣

關察表中,如果將疾病風險和工作負荷的數值相加,可以得到一個分數矩陣。其中:

0代表:低度風險;1和2代表:低度風險;3和4代表:低度風險。

Excel-雙條件查詢(INDEX,OFFSET,VLOOKUP,MATCH)

儲存格C2:=VLOOKUP((MATCH(B2,{"低負荷","中負荷","高負荷"},0)-1)+VLOOKUP
(A2,{0,0;0.1,1;0.2,2},2,TRUE),{0,"低度風險";1,"中度風險";3,"高度風險"},2,TRUE)

MATCH(B2,{"低負荷","中負荷","高負荷"},0)-1:依儲存格B2內容查詢位於 {"低負荷","中負荷","高負荷"} 陣列中的第幾個,再減 1。在此假設為 n。

VLOOKUP(A2,{0,0;0.1,1;0.2,2},2,TRUE):依儲存格A2內容查詢在 {0,0;0.1,2;0.2,2} 陣列中對應第 2 欄的第幾個。在此假設為 m。

公式相當於:=VLOOKUP(n+m,{0,"低度風險";1,"中度風險";3,"高度風險"},2,TRUE)


(3) 查表內容以變數表示

Excel-雙條件查詢(INDEX,OFFSET,VLOOKUP,MATCH)

如果你的資料是位於表<2>,則公式可以簡化:

儲存格C2:=INDEX($F$9:$H$11,MATCH(B2,$F$8:$H$8,0),VLOOKUP(A2,
{0,1;0.1,2;0.2,3},2,TRUE))


(4) 將 INDEX 換成 OFFSET

你也可以試試 OFFSET 函數來設計。

儲存格C2:=OFFSET($F$2,VLOOKUP(A2,{0,1;0.1,2;0.2,3},2,TRUE)-1,
MATCH(B2,$F$8:$H$8,0)-1)

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

有網友問到:在 Excel 中有一個如下圖的清單,其中的代碼欄位含有區域碼,如何根據代碼小計各區域的總和?

以下圖為例,每個人的代碼中含區域碼,如果在不另行取出區域碼的做法下,如何直接計算各區域的小計?

image


【公式設計與解析】

選取儲存格B1:C30,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:代碼、金額。


1. 區域碼在相同位置

以上圖為例,區域碼都位於代碼的前二碼。

儲存格F2:=SUMPRODUCT((LEFT(代碼,2)=E2)*金額)

LEFT(代碼,2)=E2:利用 LEFT 函數取出代碼中的前二碼,再和儲存格E2比對是否相同,傳回 TRUE/FALSE 陣列。(相同傳回 TRUE,不相同傳回 FALSE)

(LEFT(代碼,2)=E2)*金額:在 SUMPRODUCT 函數中,『*』運算過程中會將 TRUE/FALSE 陣列轉換為 1/0 陣列。再計算『乘積和』。


2. 區域代碼在不同位置

如果區域碼位於代碼中的不固定位置,則公式修改如下:

儲存格F2:=SUMPRODUCT((SUBSTITUTE(代碼,E2,"")<>代碼)*金額)

SUBSTITUTE(代碼,E2,"")<>代碼:利用 SUBSTITUTE 函數將代碼中的字串以空字串取代儲存格E2的內容,再和儲存格E2比對是否相同,傳回 TRUE/FALSE 陣列。(不相同傳回 TRUE,表示代碼中含有儲存格E2的內容;相同傳回 FALSE,表示代碼中沒有儲存格E2的內容。)

((SUBSTITUTE(代碼,E2,"")<>代碼)*金額:在 SUMPRODUCT 函數中,『*』運算過程中會將 TRUE/FALSE 陣列轉換為 1/0 陣列。再計算『乘積和』。

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

最近網友根據這篇文章:將PowerPoint簡報檔轉成影片,在操作上一直不能成功,所以我把操作步驟再重新描述一次。我以 PowerPoint 2013 為例。

1. 你在一個投影片的一張簡報中(以第一張投影片為例),選取[插入/多媒體]功能表中選取「音訊/我個人電腦中的音訊」選項,插入一個音訊(以一個MP3檔為例)。

PowerPoint-在投影片中插入音樂並將簡報檔轉成影片

2. 接著,在[音訊工具/播放]功能表中的「音訊選項」區中,設定:

開始:自動;勾選「跨投影片撥放」;勾選「循循播放,直到停止」。(因為希望整個影片都有背景音樂)

PowerPoint-在投影片中插入音樂並將簡報檔轉成影片

3. 再來,選取[檔案/匯出]選單,按一下「建立視訊」。

PowerPoint-在投影片中插入音樂並將簡報檔轉成影片

4. 在[建立視訊區]中設定視訊要用於那種裝置(電腦、HD顯示器、網際網路、DVD、可攜式裝置等)

5. 視需要決定要不要使用錄製的時間和旁白,如果不使用則設定每張投影片的播放秒數(本例為5秒)。

6. 按一下「建立視訊」按鈕。

PowerPoint-在投影片中插入音樂並將簡報檔轉成影片

7. 你可以選取「MP4」或「WMV」格式。

8.最後,你可以看到建立視訊的過程與進度。

PowerPoint-在投影片中插入音樂並將簡報檔轉成影片

試播一下,整個影片都有背景音樂,而且每 5 秒自動切換投影片,直到投影片最後一張播完。

PowerPoint-在投影片中插入音樂並將簡報檔轉成影片

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

同仁問到:常會看到下圖這種表示法,在 Excel 中如何模擬?

Excel-由分數清單計算加權平均並在儲存格中繪製橫條圖

在下圖中,假設A欄為1~5的分數清單(本例共有10027個)。

Excel-由分數清單計算加權平均並在儲存格中繪製橫條圖


【公式設計與解析】

先選取A欄中所有的分數,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:分數。

(1) 計算評分次數

儲存格C4:="評分次數:"&COUNT(分數)

(2) 計算加權平均分數

儲存格C3:=SUMPRODUCT((分數={5,4,3,2,1})*{5,4,3,2,1})/COUNT(分數)

(3) 計算各個分數的數量

儲存格E3:=SUMPRODUCT(--(分數=5))

儲存格E4:=SUMPRODUCT(--(分數=4))

儲存格E5:=SUMPRODUCT(--(分數=3))

儲存格E6:=SUMPRODUCT(--(分數=2))

儲存格E7:=SUMPRODUCT(--(分數=1))

(4) 設定樣式

接著,選取儲存格E3:E7,設定[常用/設定格式化的條件]功能表中的「資料橫條」,再選取一種樣式。

Excel-由分數清單計算加權平均並在儲存格中繪製橫條圖

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

網友根據這篇:

Excel-取含有文字的儲存格中計算數字總和(SUMPRODUCT,SUBSTITUTE)

想要將資料格以下圖格式呈現,該如何將『核』字消除,再計算小計。

Excel-取含有文字的儲存格中計算數字總和(SUMPRODUCT,SUBSTITUTE)


【公式設計與解析】

儲存格K2:=SUMPRODUCT(1*(SUBSTITUTE(B2:J2,"核","")))

SUMPRODUCT 函數中,儲存格B2:J2被視為陣列儲存格。

SUBSTITUTE(B2:J2,"核",""):將儲存格範圍的每個儲存格中的『核』字消除,留下數字,傳回數字的陣列。不過,這個數字其實是文字格式。

其中的『*1』,可以將上式中的文字格式轉換為數值格式。

再透過 SUMPRODUCT 函數予以數字加總

複製儲存格K2,貼至儲存格K2:K11。

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

1 2
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼