2016年12月26日 星期一

Excel-表格的欄和列轉置(OFFSET,INT,MOD)

網友問到 Excel 的問題:如下圖,要將一個表格中的資料以公式做欄和列轉置的動作,該如何處理?
Excel-表格的欄和列轉置(OFFSET,INT,MOD)
【公式設計與解析】
儲存格J2:
=OFFSET($B$1,COLUMN(A:A)+INT((ROW(1:1)-1)/4)*3,MOD(ROW(1:1)-1,4)+1)
複製儲存格J2,往下各列貼上。
(1) COLUMN(A:A)+INT((ROW(1:1)-1)/4)*3
當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3。
當公式向下複製時,INT((ROW(1:1)-1)/4)產生:1, 1, 1, 1, 4, 4, 4, 4, 7, 7, 7, 7, …。
兩者合併時會產生:1,2,3→1,2,3→1,2,3→1,2,3→4,5,6→4,5,6→4,5,6→4,5,6→ …。
(2) MOD(ROW(1:1)-1,4)+1
當公式向右複製時,
會產生:1,1,1→2,2,2→3,3,3→4,4,4→1,1,1→2,2,2→3,3,3→4,4,4→ …。
參考下圖:
Excel-表格的欄和列轉置(OFFSET,INT,MOD)
將第(1)式和第(2)式代入 OFFSET 函數取得各個儲存格的內容,完成轉置的動作。

2016年12月24日 星期六

Excel-合併兩個資料不重覆的表格(MATCH,OFFSET)

回答網友關於 Excel 的問題:在下圖中有兩個表格,其中的內容沒有重覆,而且有編號可以相互對應。如何將兩個表格合併在一起?
參考下圖,如何如何將編號2,6,10,14,17,20,22的內容置入第一個表格中?
Excel-合併兩個資料不重覆的表格(MATCH,OFFSET)

【公式設計與解析】
1. 選取儲存格B2:B23。
2. 按 Ctrl+G 鍵,開啟[到]對話框。
Excel-合併兩個資料不重覆的表格(MATCH,OFFSET)
3. 按一下[特殊]按鈕,開啟[特殊目標]對話框。
4. 選取「空格」選項,按一下[確定]按鈕。(目前的狀態是選取了空白的儲存格)
Excel-合併兩個資料不重覆的表格(MATCH,OFFSET)
5. 在資料編輯列中輸入公式:=OFFSET($E$1,MATCH(A3,$D$2:$D$8,0),0)
MATCH(A3,$D$2:$D$8,0):找出儲存格A3在儲存格D2:D8陣列中的位置。
再透過 OFFSET 函數,根據 MATCH 函數傳回的位置,取得對應的E欄中的內容。
6. 按下 Ctrl+Enter 鍵。
Excel-合併兩個資料不重覆的表格(MATCH,OFFSET)
結果如下:
Excel-合併兩個資料不重覆的表格(MATCH,OFFSET)

Excel-合併兩個資料不重覆的表格2(MATCH,OFFSET,IFERROR)

稍作修改成另一種合併方式,如下圖,在右表中若編號在左表中有資料,則希望填入項目1~項目3的欄位中。
Excel-合併兩個資料不重覆的表格2(MATCH,OFFSET,IFERROR)
結果如下:
Excel-合併兩個資料不重覆的表格2(MATCH,OFFSET,IFERROR)
【公式設計解析】
儲存格I2:
=IFERROR(OFFSET($A$1,MATCH($F2,$A$2:$A$27,0),COLUMN(A:A)),"")
複製儲存格I2,則至儲存格I2:K17。
(1) MATCH($F2,$A$2:$A$27,0)
找出儲存格F2在儲存格A2:A27陣列中的位置。若儲存格F2內容不存在儲存格A2:A27陣列中,則會傳回錯誤訊息。
(2) OFFSET($A$1,第(1)式,COLUMN(A:A))
透過 OFFSET 函數,根據 MATCH 函數傳回的位置,取得對應的B欄至D欄中的內容。
(3) IFERROR(第(2)式,"")
若第(1)式傳回錯誤訊息,則轉換顯示為空白(空字串)。

2016年12月21日 星期三

Excel-日期格式轉換(西元年轉民國年)(TEXT,SUBSTITUTE)

網友問到一個 Excel 的問題:如何將日期格式例如:1975/01/01(西元年),改顯示為0640101(民國年)格式?
在下圖中,西元年格式以『/』做為年、月、日的分隔,轉換為民國年格式時,要去除『/』符號。
Excel-日期格式轉換(西元年轉民國年)(TEXT,SUBSTITUTE)

【公式設計與解析】
方法一:取出公式取出年、月、日來組合
儲存格C2:=RIGHT("0"&(YEAR(A2)-1911),3)&RIGHT("0"&MONTH(A2),2)&
RIGHT("0"&DAY(A2),2)
複製儲存格C2,貼至儲存格C2:C17。
(1) RIGHT("0"&(YEAR(A2)-1911),3)
利用 YEAR 函數取儲存格A2中日期的『年』數,再減 1911,可得民國年數。由於得到的民國年可能是 2 碼或是 3 碼,所以先將『0』字元串接民國年,再由字串右側取 3 碼,即可將 2 碼的民國年之前加上一碼『0』。
(2) RIGHT("0"&MONTH(A2),2)
利用 MONTH 函數取儲存格A2中日期的『月』數。由於得到的月數可能是 1 碼或是 2 碼,所以先將『0』字元串接月數,再由字串右側取 2 碼,即可將 1 碼的月數之前加上一碼『0』。
(3) RIGHT("0"&DAY(A2),2)
利用 DAY 函數取儲存格A2中日期的『日』數。由於得到的日數可能是 1 碼或是 2 碼,所以先將『0』字元串接日數,再由字串右側取 2 碼,即可將 1 碼的日數之前加上一碼『0』。
(4) 最後,將第(1),(2),(3)式以『&』串接。

方法二:利用文字格式化取得年月日
儲存格C2:=RIGHT("0"&SUBSTITUTE(TEXT(A2,"[$-404]e/mm/dd;@"),"/",""),7)
複製儲存格C2,貼至儲存格C2:C17。
(1) TEXT(A2,"[$-404]e/mm/dd;@")
利用 TEXT 函數將儲存格A2的日期設定格式『[$-404]e/mm/dd;@』,可得民國年格式,例如:1975/01/01→64/01/01。
Excel-日期格式轉換(西元年轉民國年)(TEXT,SUBSTITUTE)
(2) SUBSTITUTE(第(1)式,"/","")
利用 SUBSTITUTE 函數將第(1)式結果中的『/』去除。
(3) RIGHT("0"&第(2)式,7)
由於得到的民國年可能是 2 碼或是 3 碼,所以先將『0』字元串接民國年,再由字串右側取 3 碼,即可將 2 碼的民國年之前加上一碼『0』。

【補充說明】
如果你只是要將西元年格式改成民國年格式,也可以在數值格式設定中選取[日期]類別,然後在[行事曆類型]下拉式清單中選取「中華民國曆」選項,即可得到民國年格式的日期。
image

2016年12月17日 星期六

Excel-依年資計算特休天數(VLOOKUP,INT)

網友問到,在 Excel 中如何設計依年資算出特休天數?計算規則如下:(參考下圖)
(1) 6個月以上未滿1年:特休3天
(2) 1年以上未滿2年:特休7天
(3) 2年以上未滿3年:特休10天
(4) 3年以上未滿5年:特休14天
(5) 5年以上未滿10年:特休15天
(6) 滿(含)10年以上,每增一年加給1天,最多給至30天為止
Excel-依年資計算特休天數(VLOOKUP,INT)
【公式設計與解析】
首先,你要先建立如儲存格E1:F7的資料清單,目的是要給 VLOOKUP 函數查表之用。
Excel-依年資計算特休天數(VLOOKUP,INT)
接著,輸入公式:
儲存格C2:=VLOOKUP(B2,$E$2:$F$7,2,TRUE)+INT((B2>=10)*(B2<=25)*
(B2-10))+INT((B2>25)*15)
複製儲存格C2,貼至儲存格C2:C23。
(1) VLOOKUP(B2,$E$2:$F$7,2,TRUE)
在 VLOOKUP 函數中藉由查表,查出年資對應特休天數。
(2) INT((B2>=10)*(B2<=25)*(B2-10))
若年資在 10~25 之間,則每一年的特休加 1。(B2>=10)*(B2<=25)之中的『*』,乃相當於執行兩個條件(B2>=10 和 B2<=25)的邏輯 AND 運算,傳回 TRUE/FALSE 。當在執行『*(B2<=25)』的『*』,及在執行算術運算,會將 TRUE/FALSE 轉換為 1/0。
(3) INT((B2>25)*15)
若年資超過 15 年,則都以最高 30 年,即加15+15=30。

【補充說明】
若要在B欄中輸入年和月的資料,可以將儲存格的數值格式定為:?0 ??/12。
這是分數的格式,其中整數部分對齊 2 位數,而小數部分則以 12 為分母。輸入資料時,整數和分數之間要有一個空白鍵。
Excel-依年資計算特休天數(VLOOKUP,INT)

2016年12月15日 星期四

Excel-找出指定日期後的特定日數之下個星期一(WEEKDAY)

網友的提問很實用:在 Excel 中,如果想要找出指定日期後的特定日數之下個星期一,該如何處理?
在下圖中,指定日期是2016/12/27,11天後是2017/1/7(六),下個星期一是2017/1/9。
Excel-找出指定日期後的特定日數之下個星期一(WEEKDAY)
【公式設計與解析】
(1) 下個星期一
儲存格D2:=($B$1+A3)+(7-WEEKDAY($B$1+A3,2))+1
$B$1+A3:找出儲存格B1在n天(儲存格A3)後的日期。
WEEKDAY($B$1+A3,2):傳回儲存格B1在n天後是星期幾,WEEKDAY的參數『2』,代表傳回數字1~7為星期一~星期日。
(7-WEEKDAY($B$1+A3,2))+1:傳回下個星期一和n天後所相差的天數。
image
(2) 下個星期三
儲存格E2:=($B$1+A3)+(7-WEEKDAY($B$1+A3,13))+1
WEEKDAY的參數改為『13』,代表傳回數字1~7為星期三~星期二。。
(3) 下個星期六
儲存格F2:=($B$1+A3)+(7-WEEKDAY($B$1+A3,16))+1
WEEKDAY的參數改為『16』,代表傳回數字1~7為星期六~星期五。

2016年12月14日 星期三

Excel-名次不重覆且數值小者排名小(SUMPRODUCT,COUNTIF)

讀者問到 Excel 關於排序的問題:在下圖中有一個成績表,想要製作排名,規則如下:
1. 根據淨桿排名次。
2. 數值小者,名次小。
3. 數值相同者,名次相同。
4. 無資料者不列入排序。
該如何設計公式?
Excel-名次不重覆且數值小者排名小(SUMPRODUCT,COUNTIF)

【公式設計與解析】
儲存格F2:=IF(E2<>"",SUMPRODUCT((E$2:E$11E$2:E$11)))+1,"")
複製儲存格F2,貼至儲存格F2:F11。

(1) 1/COUNTIF(E$2:E$11,E$2:E$11)
在 SUMPRODUCT 函數中計算陣列(E2:E11)中每個數值的重覆次數之倒數。如下圖。
Excel-名次不重覆且數值小者排名小(SUMPRODUCT,COUNTIF)

(2) SUMPRODUCT((E$2:E$11
因題意是要『數值小者,名次小』,因此在 SUMPRODUCT 函數中加入一個條件:E$2:E$11

(3) IF(E2<>"",第(2)式,"")
因題意要『無資料者不列入排序』,因此只把非空白的儲存格列入計算。即若E2<>"",再執行 SUMPRODUCT 函數運算。

Excel-取出合於條件的資料(OFFSET,ROW,SMALL,陣列公式)

回答網友提問:在下圖中左有一個 Excel 的資料表,如何取出指定日期的所有資料?
在下圖中的「預製日」欄位是一個日期清單,現在我們要在儲存格G1中輸入一個日期,在下圖右列出預製日是該日期的所有資料清單,該如何處理?
這類題目,已是我接觸的問題中,最常被問及的。可見需求很高,所以再次不厭其煩的解說,希望能對網友有幫助。
Excel-取出合於條件的資料(OFFSET,ROW,SMALL,陣列公式)
【公式設計與解析】
首先,為了讓公式易讀好懂,先選取儲存格B1:B31,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:預製日。
接著,輸入公式,儲存格H2:
{=IFERROR(OFFSET($A$1,SMALL(IF(預製日=$G$1,ROW(預製日),""),ROW(1:1))-1,COLUMN(A:A)),"")}
這是一個陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格H2,貼至儲存格H2:K2,複製儲存格H2:K2,並往下各列貼上。
(1) IF(預製日=$G$1,ROW(預製日),"")
在陣列公式中,判斷預製日陣列如果和儲存格G1相同者,傳回其儲存格的列號,否則傳回空字串("")。ROW 函數可以傳回儲存格的列號,而當條件不成立時傳回空字串的用意是為了讓之後的公式傳回一個錯誤訊息。
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函數由小到大取出第 1, 2, 3, ... 較小值。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3。當公式向下複製時,若 SMALL 函數發生錯誤時,會傳回錯誤訊息。
(3) OFFSET($A$1,第(2)式-1,COLUMN(A:A))
利用第(2)式的傳回值,代入 OFFSET 函數,以儲存格A1為起點,傳回對應的儲存格內容。公式中的「第(2)式-1」是因為 OFFSET 函數中的 row 參數是以 0 為起始。
其中 COLUMN(A:A)=1,當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→COLUMN(D:D)=4。
(4) IFERROR(第(3)式,"")
若公式有傳回錯誤息時,利用 IFERROR 函數將其顯示為空字串。

2016年12月12日 星期一

Excel-格式轉換(一列轉三列)(OFFSET,INT,ROW)

網友問到 Excel 的問題:如下圖,當資料超過 10000 筆時,想要將一列轉換為三列時,該如何處理?
參考下圖,資料由一列轉三列時,依其色彩放在不同的位置上。
Excel-格式轉換(一列轉三列)(OFFSET,INT,ROW)
參考以下的公式:
Excel-格式轉換(一列轉三列)(OFFSET,INT,ROW)
【公式設計與解析】
善加利用 INT(ROW(1:1)/3) 公式,當公式向下複製時:
ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→ROW(4:4)=4→...。
INT(ROW(1:1)/3)=0→INT(ROW(2:2)/3)=0→INT(ROW(3:3)/3)=0→INT(ROW(4:4)/3)=1→INT(ROW(5:5)/3)=1→INT(ROW(6:6)/3)=1→...。
如此,便可以將一列轉三列。
再透過 OFFSET 函數,藉著調整起起始儲存格位置,即可定義新的儲存格位置。每個儲存格的公式如下:
儲存格B7:=OFFSET($B$1,INT(ROW(1:1)/3),0)
儲存格C7:=OFFSET($C$1,INT(ROW(1:1)/3),0)
儲存格D7:=OFFSET($E$1,INT(ROW(1:1)/3),0)
儲存格E7:=OFFSET($E$1,INT(ROW(1:1)/3),0)
儲存格F7:=OFFSET($J$1,INT(ROW(1:1)/3),0)
儲存格G7:=OFFSET($K$1,INT(ROW(1:1)/3),0)
儲存格H7:=OFFSET($L$1,INT(ROW(1:1)/3),0)
儲存格I7:=OFFSET($M$1,INT(ROW(1:1)/3),0)
儲存格D8:=OFFSET($F$1,INT(ROW(1:1)/3),0)
儲存格E8:=OFFSET($G$1,INT(ROW(1:1)/3),0)
儲存格H8:=OFFSET($N$1,INT(ROW(1:1)/3),0)
儲存格I8:=OFFSET($O$1,INT(ROW(1:1)/3),0)
儲存格D9:=OFFSET($H$1,INT(ROW(1:1)/3),0)
儲存格E9:=OFFSET($I$1,INT(ROW(1:1)/3),0)
儲存格H9:=OFFSET($P$1,INT(ROW(1:1)/3),0)
儲存格I9:=OFFSET($Q$1,INT(ROW(1:1)/3),0)
選取儲存格B7:I9,往下各列貼上。當資料超過 10000 筆時,透過公式來轉換,還是比較方便一些。

2016年12月11日 星期日

Excel-多選一輸出(VLOOKUP,IFERROR)

網友問到在 Excel 中,如果想要根據輸入的一個數值,顯示對應的結果,該如何處理?
參考下圖,輸入和輸出對照如下:
●1:Test Passed
●2:Test Failed
●3:Test Not Applicable
這是一個多選一的輸出結果,並且為輸入錯誤給予錯誤訊息。
Excel-多選一輸出(VLOOKUP,IFERROR)
【公式設與解析】
1. 使用定數
儲存格B2:=IFERROR(VLOOKUP(A2,{1,"Test Passed";2,"Test Failed";3,"Test
Not Applicable"},2,FALSE),"輸入錯誤")
複製儲存格B2,貼至儲存格B2:B5。
(1) {1,"Test Passed";2,"Test Failed";3,"Test Not Applicable"}
在 VLOOKUUP 函數中使用固定內容的二維陣列,第1欄的內容為輸入,第2欄的內容為輸出。
(2) VLOOKUP(A2,二維陣列,2,FALSE)
利用 VLOOKUP 函數,根據儲存格A2來查詢第2欄的結果。若儲存格A2的內容不是 1, 2, 3 其中一個,則會傳回錯誤訊息。
(3) IFERROR(第(2)式,"輸入錯誤")
若傳回錯誤訊息時,利用 IFERROR 函數將其顯示為空白。

2. 使用變數
儲存格B12:=IFERROR(VLOOKUP(A12,$D$12:$E$14,2,FALSE),"輸入錯誤")
複製儲存格B12,貼至儲存格B12:B15。
在 VLOOKUUP 函數中使用不固定內容的二維陣列(儲存格D12:E14)。將原先定數內容置於儲存格範圍中,隨時可以修改輸出結果。
Excel-多選一輸出(VLOOKUP,IFERROR)

2016年12月8日 星期四

Excel-計算日期區間中各個項目的小計(SUMPRODUCT)

同時有二個讀者問到類似的問題。在下圖左是一個 Excel 清單,如何計算日期區間中各個項目的數量小計(如下圖右)?
Excel-計算日期區間中各個項目的小計(SUMPRODUCT)

【公式設計與解析】
選取儲存格A1:C29,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、項目、數量。
儲存格F5:=SUMPRODUCT((日期>=$F$1)*(日期<=$F$2)*(項目=E5)*數量)
條件一:(日期>=$F$1)*(日期<=$F$2)
設定日期區間條件,其中『*』運算子相當於執行邏輯 AND 運算,兩個條件都成立時才會傳回 TRUE,否則傳回 FALSE。即日期介於儲存格F1和儲存格F2之間者才會傳回 TRUE
條件二:(項目=E5)
項目陣列中和儲存格E5相同者傳回 TRUE,否則傳回 FALSE
在 SUMPRODUCT 函數中執行『*』運算,會將 TRUE/FALSE 轉換為 1/0
參考以下的示意圖:
Excel-計算日期區間中各個項目的小計(SUMPRODUCT)

2016年12月4日 星期日

Word-如何取出文件中的大網標題

在下圖的 Word 文件中,若是已經有對章、節、小節等標題結構設定了樣式,並且有文字內容,要如何取出文件中的大綱標題?
如果你開啟了『功能窗格』,便可以清楚看到該文件的標題結構:
Word-如何取出文件中的大網標題
先查詢一下內容文字的樣式(此例為「內文」樣式)。
Word-如何取出文件中的大網標題
查詢得知「內文」樣式的大網階層為『本文』。其他(章、節、小節)使用大網階層1, 2, 3。
Word-如何取出文件中的大網標題
在此,若要直接取出大綱內容,目前我沒有特別的做法。但是若將本文樣式的內容全部以空白取代,結果相當於將內文全數刪除。參考以下的做法:
1. 開啟[尋找及取代]對話,切換至[取代]標籤。
2. 點選[尋找目標]文字方塊。
3. 點選[格式]按鈕,再選取「段落」選項。
Word-如何取出文件中的大網標題
4. 在[尋找段落]對話框的[縮排與行距]標籤下,在大網階層的下拉式清單中選取「本文」。
Word-如何取出文件中的大網標題
5. 在[取代為]文字方塊中保持空白(沒有任何字元)。
6. 按一下[全部取代]按鈕。
image
結果如下,所有的內文字均以刪除。
Word-如何取出文件中的大網標題
你也可以在[格式]按鈕中,選取「樣式」,再尋找「內文」樣式,並由空白取代。
Word-如何取出文件中的大網標題
其他做法:
你也可以在 PowerPoint 中新增投影片時,選取[從大綱插入投影片]選項:
Word-如何取出文件中的大網標題
如此即取得所有大綱標題,但也發現自訂的多層清單無法顯示。
Word-如何取出文件中的大網標題

檢視其他文章

好康東東