贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201403 (19)

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

在日常生活中,人的記憶對於某一天可能較有印象,但是對於經過幾天的概念比較不容易建構,因為還要經過計算才能得到,尤其每年的日數還不是固定的。

所以,你會用到和老婆結婚幾天、和女兒認識幾天等這些數字以製造一些驚喜。很好用喔,你懂的!

當然,你也可能計算離退休還有幾天、距離大考還有幾天等倒數的天數,以激勵自己。

因為 Excel 已經將每一天都數字化了,所以你只要利用加/減運算即可。

(1) 計算經過天數:儲存格B2:=B2-B1+1

(2) 計算預定日期:儲存格B7:=B5+B6+1

(3) 指定今天日期:儲存格B9:=TODAY();計算剩下天數:儲存格B2:=B10-B9

(4) 計算累計天數:儲存格E2:=DATE(YEAR(B1)+ROW(1:1),MONTH(B1),DAY(B1))-B1

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

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

有網友詢問:如果在 Excel 的工作表中產生了錯誤訊息(參考下圖),想要讓這些錯誤訊息不要顯示出來,或是列印的時候不列印出來,該如何處理?

如果你只是想要將工作表中的錯誤訊不要列印出來,則可以在「版面設定」中的[工作表]標籤中,在[儲存格錯誤為]下拉式清單中選取「空白」。

預覽列印時即可發現錯誤訊息的儲存格都以空白顯示了:

image

如果你是要在工作表中就不要顯示這些錯誤訊息,則可以藉助「設定格式化條件」來處理。

1. 首先選取你要的儲存格,在[設定格式化的條件]中新增一個規則。

2. 選取「使用公式來決定要格式化哪些儲存格」。

3. 輸入公式:=ISERROR(B2)。(本例第一個儲存格為B2)

4. 設定格式為:儲存格前景色和背景色彩相同。

在工作表中即可不顯示錯誤的訊息了:

如果你是想要解決單一儲存格可能發生錯誤訊息,你還可以透過 IFERROR 函數。

公式:IFERROR(原公式,"")

例如: =IFERROR(A2/0,"") 的結果會顯示空白。

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

有網友問到,其資料表中(參考下圖)如果使用 SEARCH 函數要取出第1個數字時,遇到困難,先觀察其公式:

儲存格B2:=MID(A2,SEARCH("W",A2)+1,SEARCH("*",A2)-SEARCH("W",A2)-1)

得到錯誤訊息:#VALUE!

這是因為公式中的 SEARCH 函數中要搜尋的字元「*」為萬用字元,因為在搜尋該字元時,應使用「~*」,因為公式改為:

儲存格B2:=MID(A2,SEARCH("W",A2)+1,SEARCH("~*",A2)-SEARCH("W",A2)-1)

另外,如果要搜尋「?」和「~」這兩個符號,也要使用「~?」和「~~」來處理。

儲存格B3:=MID(A3,SEARCH("W",A3)+1,SEARCH("~?",A3)-SEARCH("W",A3)-1)

儲存格B4:=MID(A4,SEARCH("W",A4)+1,SEARCH("~~",A4)-SEARCH("W",A4)-1)

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

有網友問到類似以下的問題:參考下圖右的一個資料表,其中人員的排列是隨機的,每人有三個分數,比重分別為1,3,5,如何在下圖左中查詢人員名稱以對應計算出小計。

【準備工作】

選取儲存格D2:G11,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、分數1、分數2、分數3。

選取儲存格D1:G1,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:比重。

【輸入公式】

小計部分其實可以先算好(=分數1X1+分數2X3+分數3X5),再由查表方式來取得結果。本次來練習比重可以隨時改變,並且用 SUMPRODUCT 函數來獲取結果。

儲存格B3:=SUMPRODUCT((OFFSET($E$3,MATCH(A3,人員,0)-1,,,3))*比重)

MATCH(A3,人員,0):找出儲存格A3的人名(a1)在人員陣列中的列數,例如a1在第7列。

OFFSET($E$3,MATCH(A3,人員,0)-1,,,3)):找出儲存格A3的人名(a1)所對應的分數儲存格,例如a1對應到儲存格E9:G9。

最後,使用 SUMPRODUCT 函數將上式中的儲存格陣列儲存格E9:G9)和比重陣列取相乘積,即為所求。

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

很多人像我一樣,平常不只使用一種瀏覽器來上網,例如我常用 Google Chrome 和 Internet Explorer 這兩種瀏覽器。稍微研究一下,發現這兩種瀏覽器有些快速鍵是設定為相同的,這讓我方便不少。例如:

(1) 開啟搜尋框:Ctrl + F

如果要關閉搜尋框,則使用 Esc 鍵。

(2) 顯示/隱藏我的最愛列:Ctrl + Shift + B

(3) 清除瀏覽資料:Ctrl + Shift + Delete

清除瀏覽資料是資訊安全和保護個人隱私的重要工作,使用快速鍵,養成隨手刪除的好習慣。方便才會好做!如果要關閉清除瀏覽資料對話框,則使用 Esc 鍵。

Google Chrome

Internet Explorer

(4) 檢視下載:Ctrl + J

Google Chrome

Internet Explorer

(5) 切換全螢幕:F11

(6) 開啟新的索引標籤(分頁):Ctrl + T

(7) 關閉目前索引標籤(分頁):Ctrl + W

(8) 在多個分頁標籤中切換:Ctrl + Tab

(9) 回到上一頁/下一頁:Backspace/Shift + Backspace

(10) 顯示瀏覽的歷史記錄:Ctrl + H

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

在 Excel 中有一個資料表(如下圖左),略過空白儲存格將資料重整,重新組合如下圖右,該如何處理?

【準備工作】

選取儲存格A2:A26,按下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。

【輸入公式】

儲存格C2:{=OFFSET($A$1,SMALL(IF(資料<>"",ROW(資料),999),ROW(1:1))-1,,,)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

IF(資料<>"",ROW(資料),9999):判斷資料陣列中是否不為空白儲存格,成立則取得列號陣列,不成立則給予一個很大的值(本例為9999)。

SMALL(IF(資料<>"",ROW(資料),999),ROW(1:1)):根據上式取得的陣列,取出最小的一個列號(ROW(1:1)=1),當向下複製公式時,可以取得第 2 小、第 3 小的值、…。

接著,以 OFFSET 函數藉由列號陣列分別查詢「資料」陣列中的內容,如此便可列出不包含空白儲存格的內容。

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

解答網友問題:在下圖中的 Excel 資料表,如何由「人員」求得「單位」?

這類的查表方式,在本部落格文章中時常被使用,通常有下列數種公式類型:

儲存格E2:=INDEX(A2:B20,MATCH(D2,A2:A20,0),2)

儲存格E2:=OFFSET(B1,MATCH(D2,A2:A20,0),)

儲存格E2:=VLOOKUP(D2,A2:B20,2,FALSE)

儲存格E2:=INDIRECT(ADDRESS(MATCH(D2,A2:A20,0)+1,2))

各種詳細說明,可以透過網頁上的搜尋框,以搜尋方式取得部落框中其他文章的說明。

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

有網友想要在 Excel 中,隨機產生特定一組文字/數字的其中一個,該如何處理呢?

參考下圖,我們來練習在一組數字和一組文字中,隨機產生其中的一個。

數字範例:3、6、7、10、13、15、17、19、20、25

文字範例:甲、乙、丙、丁、戊、己、庚、辛、壬、癸

【產生公式】

(1) 隨機數字

=CHOOSE(INT(RAND()*10+1),3,6,7,10,13,15,17,19,20,25)

(2) 隨機文字

=CHOOSE(INT(RAND()*10+1),"甲","乙","丙","丁","戊","己","庚","辛","壬","癸")

其中 RAND() 函數可以產生介於 0 和 1 之間的亂數值(可能為 0,但不會為 1),INT 函數則可以取一個數之不大於的最大整數。透過 CHOOSE 函數來根據亂數值,對應陣列中的其中一個。

再來試試 INDEX 函數:

(1) 隨機數字

=INDEX({3;6;7;10;13;15;17;19;20;25},INT(RAND()*10+1),)

(2) 隨機文字

=INDEX({"甲";"乙";"丙";"丁";"戊";"己";"庚";"辛";"壬";"癸"},INT(RAND()*10+1),)

注意陣列由 { 和 } 含括,每個元素以「;」分隔。

如果你的數字已經排序過,也可以使用 SMALL 函數來執行:

=SMALL({3;6;7;10;13;15;17;19;20;25},INT(RAND()*10+1))

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

一直有網友有這種需求:在 Excel 的資料表,想要將一欄中的資料篩選為多欄後依順序列出(參考下圖),該如何處理呢?

Excel 其實提供了多種的工具可以手動來完成這個工作,但是,因為資料會變動,所以還是以公式來處理比較好。

【準備工作】

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

【輸入公式】

(1) 儲存格E2

{=IFERROR(OFFSET($B$1,SMALL(IF(人員="甲",ROW(人員),10^10),ROW(1:1))-1,,,),"")}

這是陣列公式,輸入完成,要按 Ctrl+Shift+Enter 鍵。

IF(人員="甲",ROW(人員),10^10):取得人員為「甲」的「列號陣列」,如果不為「甲」者,給予一個很大的數,本例為 10 的 10 次方。

SMALL(IF(人員="甲",ROW(人員),10^10),ROW(1:1)):利用上式取得的列號陣列,在 SMALL 函數中取得第 1 小值(ROW(1:1)=1)的列號,如果該公式向下複製時,即可取得第 2 小、第 3 小、…的列號。

將上式取得的列號,帶入 OFFSET 函數以取得對照的儲存格內容(即為「假別」)。

再以 IFFERROR 函數將查不到資料而傳回錯誤訊息的儲存格顯示為空白。

(2) 儲存格F2

{=IFERROR(OFFSET($C$1,SMALL(IF(人員="甲",ROW(人員),10^10),ROW(1:1))-1,,,),"")}

這是陣列公式,輸入完成,要按 Ctrl+Shift+Enter 鍵。

仿 (1) 儲存格E2的原理,只需將起始儲存格B1改為儲存格C1即可。如此,可以取得「時數」的內容。

複製儲存格E2:F2,往下各列貼上。

試著自行練習產生G2:J2的公式,只要將上述公式中的「甲」改為「乙」、「丙」即可。

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

有網友問到:如下的 Excel 資料表,如何找出各個品項中,日期最大者對應的數量?

【準備工作】

選取儲存格A1:C50,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、品項、數量。

【輸入公式】

儲存格F2:{=OFFSET($C$2,MATCH(MAX(IF((品項=E2),日期,FALSE)),日期)-1,)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

IF((品項=E2),日期,FALSE):核對品項陣列中符合儲存格E2者,找出所對應的日期陣列。

MAX(IF((品項=E2),日期,FALSE)):找出上式日期陣列中的最大值。

MATCH(MAX(IF((品項=E2),日期,FALSE)),日期):找出日期陣列中的最大值為資料中的第幾列。

最後,透過 OFFSET 函數,找出欄C中的對應數量。

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

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

有網友問到:根據一個銷售額清單的 Excel 資料表,如何根據金額區間來計算總額?參考下圖,以每 500 為區間來計算總額。

【準備工作】

選取儲存格B2:B26,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:銷售額。

【輸入公式】

儲存格E2:

=SUMPRODUCT((銷售額>(ROW(1:1)-1)*500)*(銷售額<=ROW(1:1)*500)*銷售額)

(銷售額>(ROW(1:1)-1)*500):當往下複製儲存格時,ROW(1:1)=1 會變為 ROW(2:2)=2、ROW(3:3)=3、…。計算結果會依序產生 0、500、1000、1500、2000、…。

本例運算結果產生陣列 {True,True,True,True,True,True,True,True,True,True,True,…}。

(銷售額<=ROW(1:1)*500):當往下複製儲存格時,計算結果會依序產生 500、1000、1500、2000、2500、…。

本例運算結果產生陣列 {False,False,False,False,False,True,False,False,False,False,True,…}。

(銷售額>(ROW(1:1)-1)*500)*(銷售額<=ROW(1:1)*500):其中的「*」相當於執行 AND 運算,因此只有第 6、11、… 的結果為 True

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

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

網友問到:要如何統計儲存格中不同儲存格色彩的個數,其中每二個儲存格已合併,該如何處理?先請參考之前的文章:

Excel-取得儲存格的色彩(http://isvincent.pixnet.net/blog/post/34018213)

作法參考下圖:先取得色彩代表的數值,再測試資料欄位的色彩數值,進一步統計個數。

【參考步驟】

1. 定義二個名稱:

color:=GET.CELL(63,工作表1!$D2)

clolorData:=GET.CELL(63,工作表1!$A2)

image

2. 找出色彩代表的數值

儲存格E2:=color

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

3. 找出儲存格的色彩數值

儲存格B2:=colorData

複製儲存格B2,貼至儲存格B2:B23。

4. 計算儲存格的色彩的個數

儲存格F2:=COUNTIF($B$2:$B$27,E2)/2

因為每二個儲存格為合併狀態,所以利用 COUNTIF 函數來計算各個色彩的個數時要再除以2。

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

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

有網友問到一個常見的 Excel 問題:如何合併兩個相同格式但不同內容的表格(參考下圖)?

建議作法:

1. 選取儲存格B2:F13。(其中一個表格的資料區)

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

3. 在[到]對話框中按一下[特殊]按鈕。

4. 在[特殊目標]對話框中,選取「空格」選項,按一下[確定]按鈕。

目前在第一個表格中的所有空白儲存格都被選取了。

5. 在第一個空白儲存格(本例為儲存格B3),輸入「=I3」(第二個表格的相同位置)。

6. 按一下 Ctrl+Enter 鍵。

7. 第二個表格的內容就被複製到第一個表格中了。

注意:目前為止,以上複製的內容之儲存格包含了公式,所以應該進一步處理將公式去除,留下內容,才會和原來表格中的內容一致。

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

有網友問到:在 Excel 中,如果要從多個欄位中列出資料重覆的清單(資料交集),該如何處理?(參考下圖)

【準備工作】

選取儲存格A1:A26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:A資料。

選取儲存格A2:E26,定義名稱:全部資料。

【輸入公式】

儲存格G2:=IFERROR(OFFSET($A$1,SMALL(IF(COUNTIF(全部資料,A資料)=5,ROW(A資料),FALSE),ROW(1:1))-1,),"")

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

【公式說明】

參考下圖,對照相關說明。

(1) COUNTIF(全部資料,A資料)

在陣列公式中找出每一個A資料在全部資料中的個數。

(2) IF(COUNTIF(全部資料,A資料)=5,ROW(A資料),FALSE)

在陣列公式中找出A資料在全部資料中個數為5的列號陣列,例如:

{Fasle,Fasle,Fasle,Fasle,5,Fasle,Fasle,Fasle,9,Fasle,Fasle,12,Fasle,…}

(3) SMALL(IF(COUNTIF(全部資料,A資料)=5,ROW(A資料),FALSE),ROW(1:1))

在陣列公式中將(2)的結果由最小至最大列出,參考下圖中的欄G。

(4) OFFSET($A$1,SMALL(IF(COUNTIF(全部資料,A資料)=5,ROW(A資料),FALSE),ROW(1:1))-1,)

使用 OFFSET 函數,在陣列公式中將(3)的結果對應出儲存格內容,參考下圖中的欄G。

(5) 利用 IFERROR 函數,將出現錯誤訊息 #NUM! 的儲存格改以空白顯示。

image

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

有網友想要解決一個問題:在一個加班的資料表中,希望能分別計算平日和假日的加班時數。而上班情形又分為二種狀況,一種是正常的星期六日為假日加班日,另一種是輪班之星期二四為加班日。

【準備工作】

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

一、星期六日為假日加班日

(1) 計算平日加班時數

儲存格D2:=SUM((WEEKDAY(A2,2)<6)*C2)

使用 WEEKDAY 函數來取得日期為星期幾,參數「2」表示傳回值1代表星期一、傳回值 2 代表星期二、…、回值 7 代表星期日。

WEEKDAY(A2,2)<6:表示日期為星期一到星期五(為平日)。

image

(2) 計算假日加班時數

儲存格E2:=SUM(NOT((WEEKDAY(A2,2)<6))*C2)

選取儲存格D2:E2,往下各列貼上。

(3) 計算平日加班時數總和

儲存格H2:=SUMPRODUCT((WEEKDAY(日期,2)<6)*時數)

透過 SUMPRODUCT 函數將平日加班的時數陣列加總。

(4) 計算假日加班時數總和

儲存格H3:=SUMPRODUCT(NOT((WEEKDAY(日期,2)<6))*時數)

透過 SUMPRODUCT 函數將假日加班的時數陣列加總。其中 NOT 函數可以將條件結果 TRUE 轉成 FALSE,將 FALSE 轉成 TRUE


二、星期二四為假日加班日

原理說明與一之(1)(2)(3)(4)相同。

(1) 計算假日加班時數

儲存格E2:=SUM(OR(WEEKDAY(A2,2)=2,WEEKDAY(A2,2)=4)*C2)

找出日期的星期幾「二或(OR)四」者,乘以時數,再予以加總。

(2) 計算平日加班時數

儲存格D2:=SUM(NOT(OR(WEEKDAY(A2,2)=2,WEEKDAY(A2,2)=4))*C2)

找出日期的星期幾不是「二或(OR)四」者,乘以時數,再予以加總。(使用 NOT 函數)

選取儲存格D2:E2,往下各列貼上。

(3) 計算假日加班時數總和

儲存格H3:=SUMPRODUCT((WEEKDAY(日期,2)=2)*時數)+SUMPRODUCT((WEEKDAY(日期,2)=4)*時數)

透過 SUMPRODUCT 函數將假日(星期二四)加班的時數陣列分別計算後加總。

(4) 計算平日加班時數總和

儲存格H2:=SUM(時數)-(SUMPRODUCT((WEEKDAY(日期,2)=2)*時數)+SUMPRODUCT((WEEKDAY(日期,2)=4)*時數))

計算加班的總和來減掉假日(星期二四)加班時數總和,即為平日加班時數總和。

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

開學三週了,有老師問到如何讓課程中的小老師,協助老師利用網路輸入班上同學的小考成績?我想使用 Google 試算表是一個不錯的選擇!

首先,你和小老師都先要具有 Gmail 的帳號。

1. 連線至 http://google.com/drive,進入 Google 雲端硬碟。

2. 按一下「建立」,再選取「試算表」。

3. 建立如下圖的成績表:

4. 建立二個計算平均的公式:

(1) 計算目前成績的平均

儲存格O2:=average(C2:N2)

(2) 計算目前成績最佳 5 次的平均

儲存格P2:=arrayformula(average(large(C2:N2,{1,2,3,4,5})))

其中 arrayformul 為陣列公式的函數,在 large 函數中使用參數 {1,2,3,4,5},表示要取前 1,2,3,4,5 個數值。

5. 選取儲存格O2:P2,使用右下角的填滿控制點,將公式往下複製。

6. 要設定不及格者顯示紅色字,選取[格式/條件式格式設定]選項。

7. 在條件式格式設定中,指定「小於 60」,勾選「文字」,設定色彩為紅色。

8. 接著要設定小老師可以使用該文件,按一下文件右上角的「共用」。

9. 在「共用設定」中,輸入小老師的 Gmail 地址,勾選「可以編輯」,按一下「傳送」。

10. 小老師將會收到一封 Email,點選試算表檔,即可以進入輸入成績。

11. 在你的共用設定中的共用連結,即為該試算表的超連結,也可以看到已經與誰共用。

12. 小老師可以開始輸入成績,二個計算平均的公式,也會立即更新為最新的結果。

13. 同時間,你也可以看到小老師輸入的狀況。

註:你也可以將原先已製好的 Excel 檔匯入 Google 試算表中,讓小老師輸入成績。

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

網友問到一個常見的問題:參考下圖,想要藉由下拉式清單來篩選不同類別的資料,該如何處理?

處理原則是下拉式清單可以使用「資料驗證」功能,而篩選資料通常會用到陣列公式。

【準備工作】

選取「級數」欄位中有資料的儲存格範圍,按一下 Ctrl+Shift+F3 鍵,定義名稱:級數。

【輸入公式】

儲存格G2:{=IFERROR(OFFSET($B$1,SMALL(IF(級數=$F$1,ROW(級數),FALSE),ROW(1:1))-1,COLUMN(A:A)-1,,),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

複製儲存格G2,貼至儲存格G2:H2。複製儲存格G2:H2,往下各列貼上。

參考下圖,對公式加以分析:

IF(級數=$F$1,ROW(級數),FALSE):在陣列公式中,判斷陣列值是否和儲存格F1的內容相同,如果是則傳回測試列對應的 ROW 函數值(參考欄B和欄C的內容),否則傳回 FALSE

SMALL(IF(級數=$F$1,ROW(級數),FALSE),ROW(1:1)):使用 SMALL 函數找尋上式所對應的列號之第1,2,3,…個(參考欄D的內容)。例如:第一個的列號為1,第二個的列號為7,…。

將上式取得的第1,2,3,…個列號,代入 OFFSET 函數中,取得對應的儲存格內容。

OFFSET($B$1,SMALL(IF(級數=$F$1,ROW(級數),FALSE),ROW(1:1))-1,COLUMN(A:A)-1,,)

其中 COLUMN(A:A)=1,如果向複製時會變成 COLUMN(B:B)=2、COLUMN(C:C)=3、…。

可以控制在 OFFSET 函數中傳那一「欄」的位置。

這個公式無論幾欄的資料,只要向右複製,即可傳回對應的儲存格內容。

最後,再透過 IFERROR 函數,將沒有傳回儲存格內容的值(錯誤訊息#NUM!)改以空白顯示。

至於儲存格的下拉式選單,則在「資料驗證」中設定儲存格內允許:清單;來源:一、二、三、四、五、六。(參考下圖)

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

有網友想要在如下的資料表中,統計特定起始字元的資料個數,該如何處理?

儲存格D2:=SUMPRODUCT(--COUNTIF($A$2:$A$26,C2&"*"))

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

這個問題的公式主要是要在 COUNTIF 函數中使用萬用字元「*」。「C2&"*"」表示以儲存格C2為開頭的資料。

使用 SUMPRODUCT 函數即可以一個公式來計算整個資料陣列的和,相當方便好用。

註:所謂起始字元並不限定一個字元,也可以是多個字元,例如:SD、ABD等。

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

在 Excel 的資料表中含有 A 組和 B 組資料,如果想要判斷各組的奇/偶數個數,及二者和之奇/偶數個數,該如何處理。

【準備工作】

選取儲存格A1:B22,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:A組、B組。

【基礎知識】

(1) MOD 函數用以餘數,所以可以使用 MOD 函數來找出一個數除以 2 的餘數,用以判斷該數為奇數或是偶數。例如:

MOD(A2,2) = 1,表示儲存格A2的內容為奇數。

MOD(A2,2) = 0,表示儲存格A2的內容為偶數。

(2) MOD(A2,2)+MOD(B2,2) 會有三種可能:

MOD(A2,2)+MOD(B2,2) = 0 :表示 儲存格A2、儲存格B2 皆為偶數。

MOD(A2,2)+MOD(B2,2) = 2 :表示 儲存格A2、儲存格B2 皆為奇數。

MOD(A2,2)+MOD(B2,2) = 1 :表示 儲存格A2、儲存格B2 一奇一偶。

(3) 在公式數如果取 TRUE/FALSE 來運算,則 TRUE 視為 1,FALSE 視為 0。

(4) NOT 邏輯運算為「相反」,所以:

NOT(TRUE) = FALSE,NOT(FALSE) = TRUE。

NOT(1) = FALSE = 0,NOT(0) = TRUE = 1。

(5) 使用 SUMPRODUCT 函數來計算陣列中符合條件的乘積和。其公式中常會使用「--」運算來將 TRUE/FALSE 陣列轉換為 1/0 陣列,其和「*1」和「+0」等是相同意思的。


【輸入公式】

(1) A組和B組有2個偶數的個數

a.儲存格C2:=IF(MOD(A2,2)+MOD(B2,2)=0,"V","")

b.儲存格C2:=IF(MOD(A2,2)=0,IF(MOD(B2,2)=0,"V",""),"")

該巢狀 IF 函數結構,表示符合 MOD(A2,2)=0 且符合 MOD(B2,2)=0 者為二者皆為偶數。

(2) A組和B組有2個奇數的個數

a.儲存格D2:=IF(MOD(A2,2)+MOD(B2,2)=0,"V","")

b.儲存格D2:=IF(MOD(A2,2)=1,IF(MOD(B2,2)=1,"V",""),"")

該巢狀 IF 函數結構,表示符合 MOD(A2,2)=1 且符合 MOD(B2,2)=1 者為二者皆為奇數。

(3) A組和B組有1偶1奇的個數

儲存格E2:=IF(MOD(A2,2)+MOD(B2,2)=0,"V","")

(4) A組和B組者皆偶數的總數

a.儲存格H2:=SUMPRODUCT(--((MOD(A組,2)+MOD(B組,2))=0))

b.儲存格H2:=SUMPRODUCT(NOT(MOD(A組,2))*NOT(MOD(B組,2)))

c.儲存格H2:=SUMPRODUCT((MOD(A組,2)-1)*(MOD(B組,2)-1))

比較以上三式,其結果相同。

(5) A組和B組二者皆奇數的總數

a.儲存格H3:=SUMPRODUCT(--((MOD(A組,2)+MOD(B組,2))=2))

b.儲存格H3:=SUMPRODUCT(MOD(A組,2)*MOD(B組,2))

比較以上二式,其結果相同。

(6) A組和B組二者1偶1奇的總數

儲存格H4:=SUMPRODUCT(--((MOD(A組,2)+MOD(B組,2))=1))

(7) A組和B組二者和為奇數的總數

儲存格H5:=SUMPRODUCT(--NOT(MOD(A組+B組,2)))

(8) A組和B組二者和為偶數的總數

儲存格H6:=SUMPRODUCT(--MOD(A組+B組,2))

試著比較(7)和(8)二式。

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

找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼