贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201402 (27)

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

最近上課又教到了 AND 函數和 OR 函數,初學者大多不知如何看待這些函數,以下稍微對這兩個函數做一些分析。

一、AND 函數

儲存格C2:=AND(A2,B2)      【複製公式,往下各列貼上】

如果要將輸入 A、B 以 0、1 來表示,則輸出函數改為:

儲存格G2:=AND(E2,F2)*1  【複製公式,往下各列貼上】

記憶方式:有假(0)必為假(0);兩者為真(1)則為真(1)。

以下是關於 AND 函數的分析:(參考下圖)

(1) 當 A=FALSE,不管 B 為何,結果必為 FALSE。【與 B 無關】

(2) 當 A=TRUE,不管 B 為何,結果必為 B。【與 B 相同】

(3) 當 A 和 B 相反,結果為 FALSE。【與(1)同】

(4) 當 A=B=FALSE,結果為 FALSE;當 A=B=TRUE,結果為 TRUE。【與(2)同】


二、OR 函數

儲存格C14:=OR(A14,B14)      【複製公式,往下各列貼上】

如果要將輸入 A、B 以 0、1 來表示,則輸出函數改為:

儲存格G14:=OR(E14,F14)*1  【複製公式,往下各列貼上】

記憶方式:有真(1)必為真(1);兩者為假(0)則為假(0)。

以下是關於 OR 函數的分析:(參考下圖)

(1)當 A=FALSE,不管 B 為何,結果必為 B。【與 B 相同】

(2)當 A=TRUE,不管 B 為何,結果必為 TRUE。【與 B 無關】

(3) 當 A=B=FALSE,結果為 FALSE;當 A=B=TRUE,結果為 TRUE。【與(1)同】

(4) 當 A 和 B 相反,結果為 TRUE。【與(2)同】


三、綜合分析

(1) 當 A=B,AND(A,B)=OR(A,B)。

(2) 當 A<>B,AND(A,B)=FALSE,OR(A,B)=TRUE。【AND(A,B)和OR(A,B)相反】


【延伸學習】

當使用三個輸入變數時:

儲存格D2:=AND(B2,C2,C2)         【複製公式,往下各列貼上】

儲存格I2:=AND(F2,G2,H2)*1        【複製公式,往下各列貼上】

儲存格D12:=OR(A12,B12,C12)   【複製公式,往下各列貼上】

儲存格I12:=OR(F12,G12,H12)*1  【複製公式,往下各列貼上】

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

有網友問到:如下圖中的資料,欄為各種長度,列為各種高度,欄列的交集為數量,想要藉由輸入長度和寬度來求得數量,該如何處理?

【準備工作】

選取儲存格B1:H1,在名稱管理中新增一個名稱:長度。

選取儲存格A2:A11,在名稱管理中新增一個名稱:寬度。

 

【輸入公式】

以下列出三種作法:

(1) 儲存格K3:=INDEX(資料,MATCH(K2,寬度,0)+1,MATCH(K1,長度,0)+1)

MATCH(K2,寬度,0):查詢儲存格K2的內容,在寬度儲存格陣列中位於第幾個。

MATCH(K1,長度,0):查詢儲存格K1的內容,在寬度儲存格陣列中位於第幾個。

將以上兩個式子代入 INDEX 函數中,即可以在欄和列「交集」的位置查詢到想要的結果。

 

(2) 儲存格K3:=OFFSET(B2,MATCH(K2,寬度,0)-1,MATCH(K1,長度,0)-1)

OFFSET 函數中利用欄和列相對位置的觀念,求得想要的儲存格位置。

 

(3) 儲存格K3:=INDIRECT(ADDRESS(MATCH(K2,寬度,0)+1,MATCH(K1,長度,0)+1))

使用 ADDRESS 函數找到想要的儲存格位址,原理和 OFFSET 函數相同,再藉由 INDIRECT 函數求得該儲存格的內容。

 

【延伸學習】

如果你想要在長度和寬度的儲存格中以選單方式來選取內容,則可以藉由「資料驗證」方式來處理。

以寬度為例,因為已事先定義好名稱:寬度,則可以在[資料驗證]對話框中,設定資料驗證準則為:儲存格內允許:清單;來源:=寬度。長度的做法相同。

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

有網友問到一個非常實用的問題:一般在 Excel 的工作表中輸入資料時,可以利用「資料驗證」中的清單來產生一個選單,方便使用者以選取選項的方式來輸入資料。但是,如果選項內容是會變動的,或是內容會出現一些空白選項(參考下圖),將會造成一些困擾,該如何克服這些問題?

image

我們要的是一個會自動增加內容的選單,而且沒有多餘的空白項:

image

做法如下:

假設選取用來作為選項的內容為儲存格D2:D22。

先新增一個名稱:MENU,其內容參照為:=OFFSET(D2,,,COUNTA(D2:D22))

正確的寫法如下:(注意工作表名稱及絶對參照($))

內容參照為:=OFFSET(工作表1!$C$2,,,COUNTA(工作表1!$C$2:$C$22))

其中使用 COUNTA 函數來取得儲存格範圍中有多少個含有資料的儲存格數,再透過 OFFSET 函數取得一個動態儲存格範圍(有內容的儲存格範圍)。

接著,選取儲存格A2:A22,設定資料驗證:

儲存格內允許:「清單」;來源:=MENU

.

如此,只要在選項的儲存格範圍中新增一筆資料時,選單中也會同步增加一個選項,而且不會出現一些空白項。

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

有網友問到:如下圖左的資料,希望能在不同的工作表中以「區別」篩選出合乎的資料(分別將北區、西區、南區、東區篩選至不同工作表),參考下圖右,該如何處理?

【準備工作】

選取所有的資料範圍,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、區別、金額。

image image

以篩選「北區」為例來說明:

【輸入公式】

儲存格A2:{=IFERROR(INDEX(INDIRECT(A$1),SMALL(IF(區別="北區",ROW(區別),FALSE),ROW(1:1))-1,1),"")}

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

IF(區別="北區",ROW(區別),FALSE):找出「區別」中符合「北區」的 ROW() 陣列(例如 ROW(3:3)=3、ROW(4:4)=4),不符合者以 False 表示。

SMALL(IF(區別="北區",ROW(區別),FALSE),ROW(1:1)):在上述的陣列 ROW() 中,取出最小值 ROW(1:1)=1,如果往下複製公式時,可以取出第二小值 ROW(2:2)=2,依此類推得到符合北區的儲存格是第幾列的數值陣列。

本例可得陣列:{false,3,4,false,false7,false,…..}。

INDIRECT(A$1):利用 INDIRECT 函數將儲存格A1內容轉換為真實的位址。(儲存格內容已事先定義為名稱了)

接著使用 INDEX 函數,以查表方式找出上述符合北區的儲存格是第幾列的數值所對應的儲存格內容。例如:INDEX(北區,3-1,1)查到第一個北區者的姓名。

複製儲存格A2,貼至儲存格A2:C2。複製儲存格A2:C2,往下至列貼上。

由於往下複製公式時,可能會傳回錯誤訊息(找不到符合的內容),所以使用 IFERROR 函數,讓傳回錯誤訊息的儲存格顯示空白。

 

註:如果想要篩選「西區、南區、東區」時,只要將上述公式中的「北區」置換掉即可。

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

有網友問到:如果給一個起始的日期,要產生一個固定星期幾週期的日期,該如何處理?參考下圖,以產生每週星期一和星期四為例。

其中:星期一和星期四差三天,星期四和下個星期一相差四天。

在儲存格A2輸入一個起始日期,本例為 2014/2/17。

儲存格A3:=A2+IF(MOD(ROW(1:1),2),3,4)

ROW(1:1):取得第1列的數值(ROW(1:1)=1),當往下複製儲存格時會產生 ROW(2:2)=2、ROW(3:3)=3、…。

MOD(ROW(1:1),2):計算 ROW(1:1) 除以 2 的餘數(=1),當往下複製儲存格時會產生 1、0、1、0、1、0、…。

IF(MOD(ROW(1:1),2),3,4):如果餘數為 1,則傳回 3,餘數為 0 傳回 4。

將上個儲存格日期加上 3 或 4,即可產生週期性星期幾的日期了。

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

有網友根據之前一篇:Excel-標示週末假日的日期(WEEKDAY),想要知道如果日期資料放在不同欄(同一列)時,該如何處理?(參考下圖)

要標示特定日期,需要藉助 WEEKDAY 函式,並利用設定格式化的條件來處理。

1. 選取儲存格A2:I3。

2. 選取「設定格式化的條件」指令,新增一個規則。

image

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

4. 編輯規則,輸入公式:=WEEKDAY(A2,2)>5

其中 WEEKDAY 函數的參數使用「2」,表示數字1代表星期一、數字2代表星期二、…、數字6代表星期六、數字7代表星期日。(大於5即為星期六、日)

image

5. 設定格式:儲存格底色:淺黃色,文字色彩:紅色,粗體字。

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

有網友問到:在以下的資料表中,如何根據 Num 欄位的內容來傳回「大/中/小」文字?

規則:輸入1,2,3,傳回「小」;輸入4,5,6,傳回「中」;輸入7,8,9,傳回「大」。

以下提供二種不同作法:

(1) 儲存格B1:=VLOOKUP(A2,{1,"小";4,"中";7,"大"},2,TRUE)

這是利用陣列查表方法找出數字對應的文字。

(2) 儲存格B1:=IF(A2<4,"小",IF(A2<7,"中","大"))

這是利用 IF 的巢狀結構來判斷不同數字範圍傳回對應的文字。

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

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

在 Excel 中有一個資料表,其中有多個分組的資料(參考下圖),如何找出各組最大值和最小值呢?

你可能使用過 SUMIF、COUNTIF、AVERAGEIF 等條件式運算函數,但 Excel 中並沒有 MAXIF 或 MINIF 等函數,不過你可以使用陣列公式來取代。

【準備工作】

選取儲存格A1:B21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:組別、數值。

【建立公式】

(1) 求各組最大值

儲存格C2:{=IF(B2=MAX(IF(組別=A2,數值,FALSE)),"V","")}

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

{IF(組別=A2,數值,FALSE)}:找出和「組別」陣列中和儲存格A2符合的儲存格陣列(判斷式中若不符合者,則給予 False)。

{MAX(IF(組別=A2,數值,FALSE))}:利用 MAX 函數取出上式中的取大值。

 

(2) 求各組最小值

儲存格D2:{=IF(B2=MIN(IF(組別=A2,數值,FALSE)),"V","")}

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

原理同(1),將 MAX 函數改用 MIN 函數。

 

【延伸學習】

儲存格C2:{=IF(B2=MAX(IF(組別=A2,數值,FALSE)),"V","")},其中的 FALSE 如果以 0 或空白來取代,都會得到錯誤的結果,因為 0 或空白都會被視為 0,而 0 可能會誤成為各組中的最小值。

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

你是否也是 Gmail 的使用者,你知道有那些 Gmail 的快速鍵可以使用嗎?使用快速鍵可以加快操作的效能,你只要在 Gmail 介面中按一下「?」,即可出現鍵盤快速鍵的一覽表:

在以下的網址中,列出了 Gmail 可以使用的快速鍵:

網址:https://support.google.com/mail/answer/6594?hl=tw

有些快速鍵是預設為啟用的,例如:

有些快速鍵則必須由使用者啟用後才能使用,例如:(擷取部分,未完整列出)

你可以選取 Gmail 中的設定(齒輪將圖示),在[一般設定]標籤下,選取「開啟鍵盤快速鍵」選項,並按下[儲存格變更]按鈕,即可使用。

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

有網友問到(參考下圖),想要找尋資料中的每一列之最後一筆資料為何,該如何處理?

(1) 每列資料全為數字

儲存格K1:=OFFSET(B1,0,COUNT(B1:H1)-1)

複製儲存格K1,貼至儲存格K1:K10。

COUNT(B1:H1):計數儲存格B1:H1中共有幾個數字,再透過 OFFSET 函數取得後一個儲存格的位置。

 

(2) 每列資料全為文字

儲存格K1:=OFFSET(B1,0,COUNTA(B1:H1)-1)

複製儲存格K1,貼至儲存格K1:K10。

COUNTA(B1:H1):計數儲存格B1:H1中共有幾個文字,再透過 OFFSET 函數取得後一個儲存格的位置。

【註記】

以上公式有個缺點:如果各列資料中含有未輸入資料的空白儲存格,則結果會出錯

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

有位網友問到:參考下圖,每月有不固定的人員支領薪資,如何計算各個人員整年度支領的薪資?下圖中以四個月來當範例。

【準備工作】

選取儲存格B2:C25,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、薪資。


【輸入公式】

儲存格F2:=SUMPRODUCT((姓名=E2)*薪資)

(姓名=E2):判斷在姓名陣列中是否和儲存格E2(甲)相同,可以得到 {False,False,False,False,True,False,False,…}陣列。

將上式的陣列乘以薪資的陣列,運算過程中 True/False 陣列,會轉換為 1/0 的陣列,相乘後得到如下的結果:

image

透過 SUMPRODUCT 函數計算上式的總和,即為所求。

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


【補充資料】

關於 SUMPRODUCT 函數詳細說明,請參閱微軟網站:

http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

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

有網友想要知道一年中每個月份的星期一、星期二、…、星期日的數量,該如何處理?

參考下圖,以2014年為例,找出每個月星期日到星期六的日數:

【輸入公式】

儲存格C2:=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE($A2,$B2,1) & ":" & DATE($A2,$B2+1,0))),1)=COLUMN(A:A)))

DATE($A2,$B2,1):取得該月的第一天。

DATE($A2,$B2+1,0):取得該月的最後一天。【注意這個技巧,求下個月的 0 號日期,即為上個月的最後一天】

INDIRECT(DATE($A2,$B2,1) & ":" & DATE($A2,$B2+1,0)):將該月頭尾日期代入 INDIRECT 函數,轉換為一個儲存格範圍。例如一月為:$41640:$41670。

ROW(INDIRECT(DATE($A2,$B2,1) & ":" & DATE($A2,$B2+1,0))):將上式代入 ROW 函數,本例可得 ROW($41640:$41670)。

接著,將上式帶入 WEEKDAY 函數來取得一個星期幾的傳回值,其中參數「1」表示星期日 = 1、星期一 = 2、…、星期六 = 7。

利用上式來判斷是否等於 COLUMN(A:A),得到一個 True/False 陣列,WEEKDAY 函數前的「--」,可以將 True/False 陣列,經由運算轉換為 1/0 陣列。

最終,透過 SUMPRODUCT 函數,計算 1 的數量,即為所求。

接著,複製儲存格C3,貼至儲存格C2:I13。

至於數字(4、5)左側的符號,是設定格式化的條件,所選取的樣式而來的:

image

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

有網友問到:

如何將同一列上的資料,由左至右改成由右至左呈現(水平翻轉)?(參考下圖)

如何將同一列上的資料,由左至右改成由上至下呈現(90度轉置)?(參考下下圖)


【輸入公式】

通常這類問題可以藉由 OFFSET 函數,改變其 colsrows 參數即可。正好藉由這個例子來練習 OFFSET 使用函數。

(1) 將同一列上的資料,由左至右改成由右至左呈現(水平翻轉)

儲存格A5:=OFFSET($A$1,,COLUMN($L:$L)-COLUMN(A:A),,)

複製儲存格A5,貼至儲存格A5:L5。

COLUMN($L:$L)-COLUMN(A:A):COLUMN(A:A)=1、COLUMN(B:B)=2、…、COLUMN(L:L)=12。向右複製公式時可以產生 11,10,9, … ,1,0。


(2)將同一列上的資料,由左至右改成由上至下呈現(90度轉置)

儲存格N2:=OFFSET($O$1,,ROW(1:1)-1,,)

複製儲存格N2,貼至儲存格N5:L11。

ROW(1:1)-1:ROW(1:1)=1、ROW(1:1)=1、…、ROW(10:10)=10。向下複製時可以產生 0,1,2, … ,8,9。


【補充資料】

關於 OFFSET 函數的詳細說明,可參考微軟網站:

http://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

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

有位網友問到:在 Excel 中有一列數值資料,如何分別計算奇數欄位(A,C,E,…)和偶數欄位(B,D,F,…)的和?(參考下圖)

假設資料範圍:A1:Z1。

(1) 計算偶數欄位和

儲存格AB2:=SUMPRODUCT((A1:Z1)*(MOD(COLUMN(A:Z),2)=0))

COLUMN(A:Z):欄位A~Z,將要運算的資料範圍起迄欄位轉換為數字,欄位A=1、欄位B=2、欄位C=3、…。

MOD(COLUMN(A:Z),2)=0:計算欄位A~Z(=1,2,3,…,26)除以 2 的餘數,判斷是否等於 0(表示偶數欄),得到一個 True/False 的陣列(False,True,False,True,…)。

(A1:Z1)*(MOD(COLUMN(A:Z),2)=0):在運算過程中,True/False 陣列會轉換為 1/0 的陣列,再和陣列 A1:Z1 相乘。

透過 SUMPRODCUT 函數將上式的每個陣列相乘積加總,即為所求。

 

(2) 計算奇數欄位和

儲存格AB4:=SUMPRODUCT((A1:Z1)*(MOD(COLUMN(A:Z),2)=1))

同 (1) 說明,僅修改 (MOD(COLUMN(A:Z),2)=1),計算欄位A~Z(=1,2,3,…,26)除以 2 的餘數,判斷是否等於 1(表示奇數欄),得到一個 True/False 的陣列(True,False,True,False,…)。

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

在網路上看到有人在討論:在一個資料範圍中,要找出指定項目的最大值和最小值,而指定一個項目時,該如何處理?指定二個項目時,該如何處理?(參考下圖)

通常這種題目,都必須透過陣列公式來處理。可以用來練習、認識陣列公式的處理!

【準備工作】

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

 

【輸入公式】

以下公式全部是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

(1) 找出甲的最大值

儲存格E2:{=MAX((項目="甲")*內容)}

項目="甲":這是一個判斷式,會產生符合(True)和不符合(False)的陣列。

(項目="甲")*內容:上式乘以(*)內容時,True 會被視為 1,而 False 會被視為 0。得到的結果只有符合條件的內容會被留下,不符合者全會被視為「0」。

將上述透過 MAX 函數,即可找出符合條件者的最大值。

 

(2) 找出甲的最小值

儲存格F2:{=MIN((項目="甲")*內容)} (X)錯誤解答

當你在求最小值,如果仿(1)的作法,將會得到錯誤的結果。因為不符合條件者會被視為 0,因此 MIN 函數找到的最小值是 0,但是內容中的最小值並非 0。

更改成以下的公式:

儲存格F2:{=MIN(IF(項目="甲",內容,FALSE))}

將 (項目="甲")*內容) 修改成 IF(項目="甲",內容,FALSE),讓不符合者以 False 顯示,再透過 MIN 函數找尋最小值時,就不會找到 0 值了。

 

(3) 找出甲和乙的最大值

如果要找二種項目的最大值,參考以下的做法:

儲存格E8:{=MAX(((項目="甲")+(項目="乙"))*內容)}

公式中的「+」,乃執行邏輯 OR 運算,將該結果乘以內容,再取 MAX 函數的結果,即為甲和乙二個項目的最大值。

你也可以將式改成以下的寫法,結果是相同的:

儲存格F8:{=MAX((項目="甲")*內容,(項目="乙")*內容)}

 

(4) 找出甲和乙的最小值

參考(3)式,你應該會寫找出甲和乙的最小值了吧!參考下式:

儲存格E8:{=MIN(IF((項目="甲")+(項目="乙"),內容,FALSE))}

 

【延伸閱讀】

如果你想要了解陣列公式的處理過程,可以在[公式]功能表中選取「評估值公式」,然後再仔細觀察每個步驟的運算結果。

或是在公式中選取公式的片段,然後按一下 F9 鍵,即可看到部分公式的執行結果。

善用工具,將有助於增加對公式的理解!

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

有網友問到一個問題:在 Excel 中要如何怎麼設定,才能呈現以下的效果:

設定一基數,超過此基數時呈現負差值,字體變紅色;小於此基數時呈現正差值,字體變藍色。例如:(參考下圖)

如基數為10....對應數為15.....則呈現-5(文字轉紅)

如基數為10....對應數為7.......則呈現+3(文字轉藍)

(1) 顯示正負差值

1. 選取儲存格B3:B24。

2. 選取[儲存格格式/自訂]標籤。

3. 輸入自訂的類型:[>0] "+"?;[<0]"-"?;

(其中:大於 0 的數會顯示「+」,小於 0 的數會顯示「-」,等於 0 的數不顯示。)

image

 

(2) 顯示不同色彩

1. 選取儲存格B3:B24。

2. 選取[常用/樣式]功能表中的「設定格式化的條件」。

3. 新增一個規則:儲格格值<0,格式設定:文字色彩為紅色。

4. 新增一個規則:儲格格值>0,格式設定:文字色彩為藍色。

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

學校老師用 Excel 記錄學生小考成績時,要善用試算表的設定格式化的條件功能。製作動態效果的成績表,達到最大的提醒作用。

例如:將不及格的分數用紅色標示,並且將累計超過三次不及格者,以紅色底色顯示。

(1) 標示不及格分數

1. 選取儲存格C2:H26。

2. 在設定格式化的條件中,新增一個規則:

類型:只格式化包含下列的儲存格,規則:儲存格值小於60。

格式:字型色彩「紅色」、粗體。

 

(2) 標示累計超過三次不及格者

1. 選取儲存格A2:H26。

2. 在設定格式化的條件中,新增一個規則:

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

規則:=COUNTIF($C2:$J2,"<60")>=3  【注意$位置】

格式:儲存格底色「粉紅色」。

image

往後,繼續輸入各次小考成績,不及格次數會累計,以達動態標示的效果。

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

參考下圖,有網友想要將 VIP 滿一年(到期日)者,在到期前二個月(60天)內給予警示,例如以紅色字顯示,而已經過期者以灰色顯示,該如何處理?一般這種案例都是以「設定格式化條件」來操作即可。

(1) 只設定日期欄位給予不同色彩

1. 選取儲存格E2:E22。

2. 設定格式化的條件為:使用公式來決定要格式化哪些儲存格。

3. 輸入公式:=E2<TODAY()

4. 設定前景色彩為:灰色。

image

5. 新增一個規則,並設定格式化的條件為:使用公式來決定要格式化哪些儲存格。

6. 輸入公式:=TODAY()+60>E2

7. 設定前景色彩為:紅色。

 

(2) 設定每個欄位都給予不同色彩

做法和 (1) 雷同:

1. 選取儲存格A2:E22。

2. 設定格式化的條件為:使用公式來決定要格式化哪些儲存格。

3. 輸入公式:=$E2<TODAY()  【注意$的位置】

4. 設定前景色彩為:灰色。

5. 新增一個規則,並設定格式化的條件為:使用公式來決定要格式化哪些儲存格。

6. 輸入公式:=TODAY()+60>$E2  【注意$的位置】

7. 設定前景色彩為:紅色。

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

前言:先前有一篇將民國年轉西元年的文章,因為只考慮民國年只有二位數所寫出的公式,在三位數的民國年會產生錯誤,特別重新撰寫了新的公式。

在如下的民國年欄位中(年月日以「.」分隔),想要產生西元年的表示法(年月日以「/」分隔),該如何處理?

可行的做法:先把第一個和第二個「.」的位置找出來,再分別取出年月日的值,將其以「/」串接。

(1) 第一個「.」的位置

儲存格B2:=FIND(".",A2)

(2) 第二個「.」的位置

儲存格C2:=FIND(".",A2,FIND(".",A2)+1)

(3) 轉換成西元年

儲存格D2:

=1911+LEFT(A2,B2)&"/"&MID(A2,B2+1,C2-B2-1)&"/"&RIGHT(A2,LEN(A2)-C2)

其中:

1911+LEFT(A2,B2):取出年的文字

MID(A2,B2+1,C2-B2-1):取出月的文字

RIGHT(A2,LEN(A2)-C2):取出日的文字

你也可以不透過輔助欄位(B欄和C欄),直接以一個公式表示:

儲存格D2:=1911+LEFT(A2,FIND(".",A2)-1)&"/"&MID(A2,FIND(".",A2)+1,FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1)&"/"&RIGHT(A2,LEN(A2)-FIND(".",A2,FIND(".",A2)+1))

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

有網友問到:如果要將某個數值依間隔值分割,依序列出,最後列出不足數,該如何處理?(參考下圖)

【輸入公式】

儲存格B3:=IF(COLUMN(A:A)>INT($A3/$B$1)+1,"",IF(COLUMN(A:A)<=INT($A3/$B$1),$B$1,MOD($A3,$B$1)))

INT($A3/$B$1):計算儲存格A3內容共含有幾個間隔值。

COLUMN(A:A)<=INT($A3/$B$1):判斷儲存格所在欄是否小於間隔值數。

根據上式結果,如果是,則顯示間隔值,如果否,則顯示儲存格A3除以間隔值的餘數。

COLUMN(A:A)>INT($A3/$B$1)+1:用以判斷如果儲存格所在欄超過間隔值數量(加1),則讓儲存格顯示空白。

複製儲存格B3,貼至儲存格B3:G3。


【延伸閱讀】

你可以試試練習將一個金額轉換為各種鈔票及硬幣的有趣問題:

Excel-將金額轉換為各種面額鈔票及硬幣(SUMPRODUCT)

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

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼