贊助廠商

///超過3500篇文章列表///

目前分類:講義資料 (2299)

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

有網友問到在以下的資料清單中(如下圖左),其數字雖然由小到大排列,但是有幾個重覆,要如何給予重覆的數字加以編號呢?(如下圖右)

【輸入公式】

儲存格B2:

=IF(COUNTIF($A$2:A2,A2)>1,A2&"-"&COUNTIF($A$2:A2,A2)-1,A2)

COUNTIF($A$2:A2,A2)>1:判斷由第一個儲存格(A2)至目前儲存格中,和本身(儲存格A2)相同的數量是否大於 1。若成立代表數字已重覆出現,若不成立代表數字尚未出現過。

A2&"-"&COUNTIF($A$2:A2,A2)-1:給予新的編號「-號碼」,該號碼即為儲存格(A2)內容出次數再減 1。(此排序法為題目要求)

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

這個公式對於原始的數列中,即使沒有經過排序也是適用!

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

有網友問到:在 Excel 中,想要在某一資料清單中(下圖左),插入多列空白列(下圖右),該如何處理?

image image

 

假設我們要在 10 列資料中,每列下方插入 4 列空白列。

1. 首先,新增一欄,如下圖的欄A。

2. 在欄A中為已有資料的各列,依序填入流水號,本例為 1 ~ 10。(參考下圖)

3. 在儲存格A11中輸入公式:=1+(ROW(A11)-10)/4-0.001。

其中 ROW(A11)-10 是因為公式從儲存格A11起始,而「/4」乃為要插入 4 列,若要插入 n 列,則為「/n」。而「-0.001」則適用於插入 1000 列以下的動作。

4. 複製儲存格A11,往下多列貼上。(參考下圖)

5. 選取欄A。

6. 按一下[排序與篩選]功能表中的「從最小到最大排序」。

image

系統如果出現[排序警告]對話框,則選取「將選取範圍擴大」選項。

image

得到如下圖的結果,最後再將欄A刪除即可。

image

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

我們常在一個 Excel 活頁簿中的多個工作表,分別放置了相同格式但不同內容的資料,並且希望取用這些工作表內容加以計算。

例如:老師們將不同班級同學的成績記錄在多個格式一樣的工作表中,而要取出各個工作表的資料來計算各班的不及格人數,或是計算多班的不及格人數總和。該如何處理這類的問題呢?(參考下圖)

過去網友也問到:COUNTIF 函數無法跨工作表使用的相關問題,在此也會遇到,所以要用不同的方式來計算。

 

(1) 計算各班不及格人數

儲存格E2:=COUNTIF(INDIRECT(D2&"!"&"B2:B21"),"<60")

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

透過 INDIRECT 函數將「D2&"!"&"B2:B21"」字串轉換成「位址」(本例為:301!B2:B21)。再透過 COUNTIF 函數來計算小於 60 的個數。

 

(2) 計算全部班級不及格人數總和

儲存格H2:={=SUM((N(INDIRECT(ADDRESS(ROW(A$2:A$21),2,1,1,
TRANSPOSE(D2:D11))))<60)*1)}

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

在陣列公式中,ROW(A$2:A$21) 代表第2, 3, 4, ..., 21。使用 TRANSPOSE 函數將班級名稱轉置(列的排列轉為欄的排列),在 ADDRESS 函數中即可取得表列文字所代表的每一個工作表相同位置的內容。

利用 INDIRECT 函數將 ADDRESS 函數取得的字串轉換成「位址」,而 N 函數將儲存格內容轉換為數字。公式 N(公式)<60)*1,目的為找出小於 60 的 TRUE/FALSE 陣列,「*1」的作用為將TRUE/FALSE 陣列轉換為 1/0 的陣列。再送至 SUM 函數計算總和,即為所求。

 

(3) 計算部分班級不及格人數總和

儲存格E5:={=SUM((N(INDIRECT(ADDRESS(ROW(A$2:A$21),2,1,1,
TRANSPOSE(G5:G9))))<60)*1)}

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

原理同 (2)。藉助班級清單,便可找出任意「不連續」工作表的不及格人數總和。

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

參考下圖,有網友想要知道在一個 Excel 的項目清單中,如何根據每一個項目,在同列中列出相關的內容?

【準備工作】

選取儲存格A1:B16,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、內容。

【輸入公式】

儲存格E2:{=IFERROR(OFFSET($B$1,SMALL(IF($D2=項目,ROW(內容),FALSE),
COLUMN(A:A))-1,,,),"")}

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

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

(1) IF($D2=項目,ROW(內容),FALSE)

在陣列公式中判斷儲存格D2的內容和「項目」陣列中的每一個項目是否相同,若是,則傳回該儲存格的列號;若否,則傳回 FALSE

本例傳回陣列:{2; 3; FALSE; FALSE; FALSE; FALSE; 8; ... }

(2) SMALL(IF($D2=項目,ROW(內容),FALSE),COLUMN(A:A))

根據 (1) 的結果代入 (2) 式中,當 COLUMN(A:A)=1,往右複製時會產生COLUMN(B:B)=2、COLUMN(C:C)=3、...。

SMALL 函數中,本例會傳回結果 2(第一個最小值)。往右複製後,會傳回 2, 3, 8, ...。

(3) OFFSET($B$1,SMALL(IF($D2=項目,ROW(內容),FALSE),COLUMN(A:A))-1,,,)

將 (2) 的結果代入 OFFSET 函數,在「內容」陣列中取得對應的內容。

(4) 最後藉由 IFERROR 函數將儲存格傳回錯誤訊息時(查詢不到對應值時),改設定為空白。

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

有網友問到在 Word 應用程式中如果要將多個 Word 文件組合成一個文件時,當使用將在檔案總管中的多個檔案拖曳至新增的 Word 文件中(參考下圖):

每個檔案會將一個圖片來表示,當在圖片上按二下時,可以編輯該文件:(每個檔案被視為一個物件被插入)

這並不是讀者所要的組合方式,有沒有其他方式可以達到目的呢?參考以下的做法:

先選取[插入/文字]功能表中的「文字檔」指令:

接著選取你想要插入的所有 Word 檔(多個檔案),按一下[插入]按鈕。

這多個檔案即會依序插入該空白文件中,而且是可以編輯的狀態,並且一個檔接續另一個檔案,所以可能在一個頁面中含有來自兩個不同檔案的內容:

如此便可以順利的將多個 Word 文件檔串接在一個 Word 檔中了!

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

有網友問到在 Excel 中有一個資料清單,如何在格式化條件設定中使用多條件的問題。

請問,如果我格式化的條件是:

需格式化欄位(F1),條件為:(F1)值<-0.2,且(E1)<3。

需格式化欄位(F2),條件為:(F2)值<-0.2,且(E2)<3。

需格式化欄位(F3),條件為:(F3)值<-0.2,且(E3)<3。

以此類推,該如何處理?(參考下圖)

【參考做法】

1. 選取想要設定格式的儲存格。

2. 選取[新增/設定格式化的條件]功能表中選取[新增規則]指令。

3. 在[編輯格式化規則]對話框中,設定如下:

(1) 選取規則類型設定為:使用公式來決定要格式化哪些儲存格。

(2) 規則設定為:=($F1<-0.2)*($E1<3)

其中的「*」運算子,用以執行邏輯 AND 的運算。

注意到:欄F和欄E使用了絶對參照,而列1則使用相對參照。因為這個公式的設定是以第一個儲存格(E1)來設定,這個公式會自動複製到其他原先被選取的儲存格中。

(3) 設定文字格式為:粗體紅色字。

image

如此,便完成了兩個條件的規則設定:

如果你的條件有多個,則公式定為:

(條件一)*(條件二)*(條件三)* … *(條件N)

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

有網友問到:想要在 Excel 的一個日期/金額清單中,如何計算一個日期範圍內的金額小計?例如:下圖中合於起始日期和結束日期之間的金額要加以小計。

【輸入公式】

儲存格E3:=SUMPRODUCT((A2:A23>=E1)*(A2:A23<=E2)*B2:B23)

(1) (A2:A23>=E1):找出日期陣列中大於或等於儲存格E1日期的 TRUE/FALSE 陣列。

(2) (A2:A23<=E2):找出日期陣列中小於或等於儲存格E2日期的 TRUE/FALSE 陣列。

把 (1) 和 (2) 和儲存格B2:B23加以相乘,即為所求。其中「*」運算相當於執行邏輯 AND 運算,所以在運算過程中 TRUE/FALSE 陣列會被轉換為 1/0 陣列。

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

在 Excel 中,如果給予一個起始日期,如何產生一個固定區間(例如:7天)的日期序列?參考下圖的範例,是由 2014/07/01 開始,在連續儲存格中產生每七天一組的日期序列。

儲存格D2:

=TEXT($B$1+(ROW(1:1)-1)*7,"mm/dd")&"~"&TEXT($B$1+(ROW(1:1)-1)*7+6,"mm/dd")

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

(1) TEXT($B$1+(ROW(1:1)-1)*7,"mm/dd")

ROW(1:1)=1,往下複製後產生 ROW(2:2)=2、ROW(3:3)=3、…。

(ROW(1:1)-1)*7=0,往下複製後產生 (ROW(2:2)-1)*7=7, (ROW(3:3)-1)*7=14, …。

$B$1+(ROW(1:1)-1)*7 =2014/7/1,往下複製後產生 2014/7/8、2014/7/15、…。

TEXT($B$1+(ROW(1:1)-1)*7,"mm/dd"):將上式產生的日期格式化為月二碼、日二碼,例如:2014/7/1 取為 07/01。

(2) TEXT($B$1+(ROW(1:1)-1)*7+6,"mm/dd")

原理同 (1) 的說明,只是每個日期再加 6 。

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

有網友在一個 Excel 的工作表中建立了一個資料清單,想要使用下拉式清單選取資料的方式來查表得到結果,該如何處理?(參考下圖)

參考以下的做法:

1. 選取儲存格A2,在[資料/資料驗證]中,執行以下的設定:

儲存格內允許:清單,來源:=$D$2:$D$9

2. 選取儲存格B2,輸入以下公式:

儲存格B2:=VLOOKUP(A2,D2:E10,2)

原本讀者想要以 IF 的語法來執行公式,可是 IF 函數只能使用 7 層的巢狀結構,所以改用 VLOOKUP 函數來執行。

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

學校同仁想要分析一份在 Excel 試算表的簽到單中(如下圖中,其中簽名無任何次序性),根據完整的名冊(如下圖左),想要找出未簽到的人(如下圖右),該如何處理?

 

【公式輸入】

儲存格H2:{=IFERROR(OFFSET($B$2,SMALL(IF(COUNTIF(D$2:D$25, $B$2:$B$25)=0,ROW($B$2:$B$25)-2,FALSE),ROW(1:1)),,,),"")}

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

複製儲存格H2,貼至儲存格H2:J25。

 

公式有點長,慢慢來解析:

(1) COUNTIF(D$2:D$25,$B$2:$B$25):

利用 COUNTIF 函數,計算在名字清冊中的名字陣列出現在1月6日的簽到名單中次數(若為 1 表示有簽到,若為 0 表示未簽到),形成一個 1/0 的陣列集合。本例結果為:0, 0, 1, 0, 1, …。

(2) IF(COUNTIF(D$2:D$25,$B$2:$B$25)=0,ROW($B$2:$B$25)-2,FALSE):

根據(1)的傳回值,判斷是否為0(若為 1 表示有簽到,若為 0 表示未簽到),若是,則給予對應的一個數值:ROW($B$2:$B$25)-2,其中 ROW($B$2:$B$25) 為在名單清冊的第幾列。若否,則設定為 FALSE。參考下圖:

(3) SMALL(IF(COUNTIF(D$2:D$25,$B$2:$B$25)=0,ROW($B$2:$B$25)
-2,FALSE),ROW(1:1))

當公式往下各列複製後,可以利用 SMALL 函數,依序取出第 1, 2, 3, … 小值的數,本例為:0, 1, 3, 5, 7, 9, 11, 21。

(4) OFFSET($B$2,SMALL(IF(COUNTIF(D$2:D$25,$B$2:$B$25)=0,
ROW($B$2:$B$25)-2,"#NA"),ROW(1:1)),,,)

根據(3)的傳回值,代入 OFFSET 函數取得人員名冊上的一個對應姓名,其中如果查不到對應名字時(代表已查不到未簽到的名字了),則會傳回錯誤訊息:#NUM!

(5) 將(4)的傳回值透過 IFERROR 函數將傳回上述錯誤訊息的儲存格顯示為空白。

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

有同事在問:在 PowerPoint 中製作簡報時,將一投影片中的一個物件設定了動畫,如何能讓這個動畫持續動作,不受時間的限制呢?即能夠讓播放的人可以控制何時停止動畫播放。

例如:以下的投影片中的圖片物件要讓它持續旋轉,不要給予動畫的時間限制,就讓它一直旋轉下去,直到播放者將它停止。

你只要在設定該物件的動畫時,先設定一個動畫。然後在[動畫]窗格上選取這個圖形物片物件(本例為Picture 2),按一下右鍵,選取「效果選項」:

image

接著在這個動畫的設定對話框中選取[預存時間]標籤,在[重複]下拉式清單中,除了一些固定的時間之外,還提供了「直到下一次按滑鼠」或是「直到最後一張投影片」選項,而這兩個選項,就可由播放者自行決定何時停止動畫效果或是動畫持續到簡報結束。

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

有網友問到使用 VLOOKUP 函數和巢狀 IF 函數那一個較好,以其提供的範例來觀察。

某藥物:(範例)
● 12 歲以上吃 1 顆
● 6~12 歲吃 1/2 顆
● 2~5 歲吃 1/4~1/2 顆
● 6 個月~2 歲吃 1/4 顆

參考下圖,如果使用巢狀 IF 函數來看,公式如下:

儲存格B2:=IF(A2<0.5,"0",IF(A2<2,"1/4",IF(A2<6,"1/4~1/2",IF(A2<=12,"1/2","1"))))

複製儲存格B2,往下各列則上。

巢狀 IF 函數會受到七層的限制,如果改以 VLOOKUP 函數來查表,則公式如下:

儲存格B2:=VLOOKUP(A2,$D$2:$E$6,2,TRUE)

其中的 TRUE 參數,表示查詢時只要「大致符合」即可。

複製儲存格B2,往下各列則上。

使用 VLOOKUP 函數看似公式可以簡化,但必須建立一個對照表,不過不會受到個數的限制。在本例中,還用到一個小技巧,在年紀欄位中,顯示的 0.5, 2, 6, 12,其實是 0.49999, 1.99999, 5.99999, 12.00001,但是在儲存格格式中設定小數點位數為 0 的結果。這是為了某些數,例如:2,到底要算在 0.5 ~ 2 或是 2 ~ 5 的那一個區間中所做的調整。

不過 VLOOKUP 函數的寫法可以有其他各種變化!到底使用 VLOOKUP 函數和巢狀 IF 函數那一個較好?能執行正確結果的公式都是好的公式!

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

有網友問到這類的問題:某藥物年紀 6 個月以下禁用,12 歲以上用固定劑量每日兩顆。年紀介於其中的小朋友每天每公斤劑量為0.4mg,該如何使用 Excel 顯示藥的劑量?(參考下圖的範例,其中體重為隨機產生。)

image

依照其問題的描述,可以發現其條件有三個部分,分別給予不同藥的劑量:

(1) 6 個月以下;(2) 12 歲以上;(3) 介於前二項。

所以,只要使用巢狀的 IF 函數(二個 IF)即可滿足三個條件的判斷:

儲存格D3:=IF((A3=0)*(B3<=6),"禁  用",IF(A3>=12,"2顆",TEXT(C3*0.4,"0.0")&"mg"))

(A3=0)*(B3<=6):其中的「*」運算子為執行 AND 邏輯運算,即年 = 0 和月 <= 6。

TEXT(C3*0.4,"0.0")&"mg"):C3*0.4 為劑量計算,透過 TEXT 函數讓運算結果,都取小數點 1 位元,而「&"mg"」是將運算結果串接「mg」字串。

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

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

有老師問到在如下圖的 PowerPoint 投影中,如果想要在一個物件(本例為一個按鈕圖示)上按一下,才顯示一段文字內容,該如何處理?

例如:老師顯示了一個題目來問學生,按一下[按我看答案]按鈕,才會顯示答案的文字。一般將文字設定動畫之後,只能依照預設的順序,以「循序」的方式顯示各段的文字,所以無法控制何時顯示答案的文字。

參考以下的步驟來改變文字的顯示為「互動功能」:

1. 先為答案的文字,設定一個動畫效果:

2. 在[動畫]窗格中,選取這個動畫效果,按一下右鍵,選取[效果選項]:

3. 在[出現]對話框中,點選[預存時間]標籤,再點選[觸發程序]按鈕:

4. 在[按一下就開始效果]下拉式清單中,選取一個物件,本例為:按鈕形 3:按我看答案。

在[動畫]窗格中,原來的文字效果會顥示為互動功能…。

image

如此,只有在按鈕物件上按一下,才能看到答案的文字。

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

有網友問到:在 Excel 中有一些已命名的工作表,如何根據工作表名稱取得固定儲存格內容?參考下圖,每個工作表的格式是相同。

  

要存取不同工作表的某個儲存格,其公式為:工作表名稱!儲存格。

我們要透過 INDIRECT 函數將工作表名稱字串轉換為位址,所以在使用時要設定為:INDIRECT("工作表名稱!儲存格")

【橫式】

儲存格B2:=INDIRECT("'"& $A2 & "'!B" & COLUMN(B:B))

因為 INDIRECT 函數公式中,「&」運算子要串接的字串,必須頭尾使用「"」含括字串。所以字串中若有「"」符號,則必須先串接「'」。

INDIRECT("'"& $A2 & "'!B" & COLUMN(B:B))

==> INDIRECT("XXXXXXXX" & COLUMN(B:B))

==> XXXXXXXX = '"& $A2 & "'!B

其中「"'"」為「"」串接「'」,再串接「"」所組成;而「"'」為「"」串接「'」所組成。

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

【直式】

儲存格K2:=INDIRECT("'"& K$1 & "'!B" & ROW(2:2))

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

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

有網友問到:如下圖右的兩個資料表,如果想要在選擇「項目」後,能由輸入的「數值」在不同的資料表中查詢到「百分比」,該如何處理?

【輸入公式】

本例要使用 VLOOKUP 函數來執行查詢的工作,如上圖,資料的安排非常重要,這決定了公式如何撰寫。(注意:在I欄中使用「- 350、- 550、…」,只是資料呈現方式,不會影響查詢。)

儲存格C2:=IF(A2="空運",VLOOKUP(B2,E2:G11,3,TRUE),VLOOKUP(B2,H2:J16,3,TRUE))

VLOOKUP(B2,E2:G11,3,TRUE):在儲存格E2:G11中,查詢儲存格B2內容對應的資料。

VLOOKUP(B2,H2:J16,3,TRUE):在儲存格H2:J16中,查詢儲存格B2內容對應的資料。

VLOOKUP 函數中的參數 TRUE,表示搜尋資料只要大致相符即可,而對照表已經是「由小到大」排序好了,所以查詢結果會傳回:大於且最接近的數值。

其中項目的選擇,可以使用「資料驗證」方式來處理。

註:本例若輸入的數值,不在查詢範圍內,會出現「#N/A」的錯誤訊息。

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

最近有同事因為要重新安裝電腦,問到要如何將平常累積建立的「自動校正」內容,在新安裝電腦時可以繼續使用?

關於自動校正的使用,可以參考以下兩篇文章:

Word-使用自動校正功能加速文字輸入

善用自訂校正,加速資料輸入

在 Microsoft Office 中的 Word/Excel/PowerPoint 等中會使用到自動校正功能,而這個自動校正功能是共用、互通的。使用者可能在安裝新電腦時,要移轉 Office 的自動校正內容,或是安裝新版本或是要將一部電腦中的自動校正內容移轉至另一部電腦中使用,來看看如何操作,讓自動校正內容可以移轉。

你可以按一下 WinKey+R 鍵,在文字框中輸入:%appdata%\Microsoft\Office,點選一個資料夾:

Windows 會開啟一資料夾,這個資料夾的位址是:

C:\Users\你的帳號\AppData\Roaming\Microsoft\Office

複製其中的「MSO1033.acl」檔案,並將其貼至另一部電腦或是新版本的相同資料夾之下即可。你也可以定期備份這個檔案,即可備份「自動校正」內容。

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

有網友問到一個日常生活的問題:如何依員工薪水查詢健保費的投保級距和顧主負擔(參考下圖)?

(註:下圖數據僅供練習,與相關規定不一定相符。為了說明方便,已隱藏多列資料。)

【公式說明】

因為有一個完整的資料對照表,所以很適合使用查詢公式來檢索。本例使用 VLOOKUP 函數來練習。(參考下圖)

經網友指正,特別再列出本公式所適用之範圍如下:(和政府所列級距認定不一致,在此僅提供公式練習。)

【公式說明】

儲存格M2:=VLOOKUP(L2,I2:J53,1,TRUE)

儲存格N2:=VLOOKUP(L2,I2:J53,2,TRUE)

其中資料範圍為儲存格I2:J53,意義為查詢儲存格L2的內容,在資料表I2:J53的第 1 欄中找尋最接近者,並傳回對應的第 1 欄和第 2 欄的內容。

VLOOKUP 函數中的參數 TRUE,表示搜尋資料只要大致相符即可,而對照表已經是「由小到大」排序好了,所以查詢結果會傳回:大於且最接近的數值。

(建議薪水總額可以使用「資料驗證」功能,由資料表中的「應領總額」建立清單。)

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

在 Windows 7 以上版本的檔案總管裡,可以在點選檔案時,於狀態列上看到比較多的檔案資訊,不過每次只能顯示一個檔案的資訊:

在狀態列中,除了可以檢視資訊,還可以在此修改資訊的內容:

如果需要在多個檔案中互相比較或是加以排序這些檔案資訊時,該如何處理呢?

你只要在檔案檢視的「欄位名稱」上按一下右鍵,再選取「其他」:

然後點選想要顯示的資訊名稱:(上移/下移是用來安排這些檔案資訊顯示的位置)

如此,便可以用來比較多個檔案的資訊內容(也可以用來排序):

你可以用來不一需打開 Word 檔,即可知道每個檔案的「作者」、「頁數」和「總共的編輯時間」等:

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

有網友問到,如下圖的 Excel 資料表,如何根據下圖左的出差記錄(只顯示部分資料),摘要依假別分年/月統計次數?(參考下圖右)

【準備工作】

選取A欄至D欄中有資料的區域,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:日期、早班、午班、晚班。

【輸入公式】

儲存格H2:
=SUMPRODUCT((YEAR(日期)=$F2)*(MONTH(日期)=$G2)*(早班=H$1))+
  SUMPRODUCT((YEAR(日期)=$F2)*(MONTH(日期)=$G2)*(午班=H$1))+
  SUMPRODUCT((YEAR(日期)=$F2)*(MONTH(日期)=$G2)*(晚班=H$1))

其中:

條件(1)  YEAR(日期)=$F2:判斷在日期陣列的年份(2014年)是否和儲存格F2(出差)相同,傳回 TRUE/FALSE 陣列。

條件(2)  MONTH(日期)=$G2:判斷在日期陣列的月份(1份)是否和儲存格F2(出差)相同,傳回 TRUE/FALSE 陣列。

條件(3)  早班=H$1:判斷在早班陣列中的內容是否和儲存格F2(出差)相同,傳回 TRUE/FALSE 陣列。

公式中的「*」會在運算時將 TRUE/FALSE 陣列轉換為 1/0 陣列。

午班=H$1:判斷在午班陣列中的內容是否和儲存格F2(午班)相同,傳回 TRUE/FALSE 陣列。

晚班=H$1:判斷在晚班陣列中的內容是否和儲存格F2(晚班)相同,傳回 TRUE/FALSE 陣列。

複製儲存格H2,貼至儲存格H2:J25。

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