2015年12月31日 星期四

Excel-將數字分解(INT,MOD,COLUMN)

有網友想要將一個數拆解為幾個100和100以下的數字,利用 Excel 該如何處理?以下圖為例,266 被拆解成 100, 100, 66。
Excel-將數字分解(INT,MOD,COLUMN)

【公式設計與解析】
儲存格B2:=(INT($A2/100)>=COLUMN(A:A))*100+(INT($A2/100)=
COLUMN(A:A)-1)*MOD($A2,100)
複製儲存格B2,貼至儲存格B2:K18。
INT($A2/100):取得百位數。
(INT($A2/100)>=COLUMN(A:A))*100:判斷百位數是否大於或等於各欄的編號(1~9),傳回 TRUR/FALSE。COLUMN(A:A)=1,向右複製後產生COLUMN(A:A)=1→COLUMN(B:B)=2→ … →COLUMN(I:I)=9。
INT($A2/100)=COLUMN(A:A)-1:判斷百位數是否等於各欄的編號減1。
MOD($A2,100):取得不足100的數值。

2015年12月30日 星期三

Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)

有網友想要根據 Excel 資料表中的一個含有色彩的樣式清單,挑選指定色彩的儲存格。參考下圖,挑選所有『紅色』的各種樣式。該如何處理?
image
【準備工作】
選取B欄中有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:樣式。

【公式設計與解析】
儲存格D3:{=IFERROR(OFFSET($A$1,LARGE(IF((LEFT(樣式,2)=E$1),
ROW(樣式),FALSE),ROW(1:1))-1,),"")}
儲存格E3:{=IFERROR(OFFSET($B$1,LARGE(IF((LEFT(樣式,2)=E$1),
ROW(樣式),FALSE),ROW(1:1))-1,),"")}
以上二式是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
LEFT(樣式,2)=E$1:利用 LEFT 函數篩選所有樣式儲存格中的前二碼(代表色彩),再和儲存格E1來比對。
IF((LEFT(樣式,2)=E$1),ROW(樣式),FALSE):比對結果若相符,則傳回『列號』;若不相符,則傳回『FALSE』。
LARGE(IF((LEFT(樣式,2)=E$1),ROW(樣式),FALSE),ROW(1:1)):利用 LARGE 函數依序找出較大列號第 1, 2, 3, ... 個。
將上式代入 OFFSET 函數求得相對於儲存格B1的儲存格,即為所求。
最後利用 IFERROR 函數將傳回的錯誤訊息顯示為空白。
複製儲存格D3:E3,往下各列貼上。
注意:該公式的結果會依編號的相反順序呈現。

【延伸練習】
1. 依色彩文字內容顯示不同色彩
如果想要將儲存格文字色彩依儲存格內容標示的色彩而變,該如何處理?
選取「樣式」儲存格範圍,設定格式化的條件。
Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)
本例設定七個相同的規則類型:使用公式來決定要格式化哪些儲存格。
分別設定不同規則:
(1) 規則:=FIND($E$1,$B2);格式:儲存格填滿較深藍色
如果儲存格E1中指定哪種色,則該色彩所在的儲存格即可變為較深藍色的底色。
Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)
(2) 規則:=LEFT($B2,2)="紫色";格式:文字色彩為『紫色』。
觀察『樣式』中的前二碼均為色彩名稱。
Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)
(3) 規則:=LEFT($B2,2)="橙色";格式:文字色彩為『橙色』。
(4) 規則:=LEFT($B2,2)="黑色";格式:文字色彩為『黑色』。
(5) 規則:=LEFT($B2,2)="綠色";格式:文字色彩為『綠色』。
(6) 規則:=LEFT($B2,2)="藍色";格式:文字色彩為『藍色』。
(7) 規則:=LEFT($B2,2)="紅色";格式:文字色彩為『紅色』。
依照此規則,即可依樣式中的色彩,文字自動變化為該色彩。
Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)

2. 篩選相同色彩
如果你啟用「自動篩選」,則可以在B欄中使用『依色彩篩選』:
Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)
利用篩選功能即可手動篩選相同色彩的儲存格:
Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)

3.使用下拉式清單挑選色彩
如果要在儲存格E1中使用下拉式清單來挑選一個色彩,可以透過「資料驗證」工具來處理。先選取儲存格E1,再選取[資料/資料工具]功能表中的「資料驗證」。
Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)
在[資料驗證]對話框中,設定:
儲存格內允許:清單
來源:橙色,灰色,紅色,紫色,黑色,綠色
Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)

2015年12月28日 星期一

Excel-取固定數量小計(OFFSET,ROW,MOD)

讀者想要在以下的 Excel 工作表,每間隔 5 個數即計算和,其餘儲存格均顯示空白,該如何處理?若分組數不是 5 個,而是一個變數,公式該如何設計?(參考下圖)
Excel-取固定數量小計(OFFSET,ROW,MOD)

【公式設計與解析】
1. 分組數固定(本例為5)
儲存格B2:=IF(MOD(ROW(A2),5)=1,SUM(OFFSET(A2,0,0,-5,)),"")
複製儲存格B2:往下各列貼上。
MOD(ROW(A2),5)=2;MOD(ROW(A3),5)=2;MOD(ROW(A4),5)=4;
MOD(ROW(A5),5)=0;MOD(ROW(A6),5)=1;MOD(ROW(A7),5)=2;
...
OFFSET(A2,0,0,-5,):以某個儲存格為準,傳回向上5個儲存格範圍。(因為要5個一組)

2. 分組數不固定(在儲存格D2控制)
如果將分組數置於儲存格D2,則公式將上式中的 5 置換為儲存格D2即可。
儲存格B2:=IF(MOD(ROW(D2),$D$2)=1,SUM(OFFSET(A2,0,0,-$D$2,)),"")
複製儲存格B2:往下各列貼上。
在儲存格D2輸入分組數,即可依這個數量顯示小計。
Excel-取固定數量小計(OFFSET,ROW,MOD)

【加碼演出】
如果要依分組數,如何在小計欄位自動顯示較深的色彩格式?
先選取A欄和B欄中有資料的儲存格,設定格式化的條件如下:
規則類型:使用公式來決定要格式化哪些儲存格。
規則:=MOD(ROW(A2),$D$2)=1
Excel-取固定數量小計(OFFSET,ROW,MOD)
如此,便可以在小計欄位自動顯示較深底色。

2015年12月25日 星期五

Excel-利用下拉式選單挑選名字後自動顯示照片(OFFSET,MATCH,使用名稱)

在 Excel 檔案中用來計算與分析資料大多以表格和統計圖表呈現,如果有要用到呈現圖片,都需要靠手動方式來設定。這次來練習一個有趣的例子,如何才能做到像下圖中,輸入一個姓名,即能查詢這個人的照片?
在下圖中的例子,希望能選取一個人的姓名後,能自動顯示這個人的照片和計算其BMI值。
Excel-利用下拉式選單挑選名字後自動顯示照片(OFFSET,MATCH,使用名稱)

【公式設計與解析】
假設資料放在儲存格A1:E20。
1. 選取儲存格B1:B20,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名。
2. 選取儲存格G1,選取[資料/資料驗證]功能表中的「資料驗證」。
3. 設定資料驗證準則,儲存格內允許:清單;來源:=姓名。(姓名為先前已定義的名稱)
Excel-利用下拉式選單挑選名字後自動顯示照片(OFFSET,MATCH,使用名稱)
4. 在[公式/已定義名稱]功能表中選取「名稱管理員」,新增「image」名稱,其參照:
=OFFSET(工作表1!$C$1,MATCH(工作表1!$G$1,姓名,0),)
利用儲存格G1,在姓名範圍內比對,傳回所在位置。再透過 OFFSET 函數,找到這個姓名在相片欄位的儲存格位置。
Excel-利用下拉式選單挑選名字後自動顯示照片(OFFSET,MATCH,使用名稱)
5. 將儲存格C2的照片複製到儲存格G2中。
6. 選取儲存格G2中的照片,在公式編輯列中輸入「=image」。(在步驟定義的名稱)
Excel-利用下拉式選單挑選名字後自動顯示照片(OFFSET,MATCH,使用名稱)
7. 在儲存格H2中輸入求BMI的公式:
儲存格H2:=ROUND(OFFSET(E1,MATCH(G1,姓名,0),)/(OFFSET(D1,
MATCH(G1,姓名,0),)/100)^2,1)
其中,
MATCH(G1,姓名,0):由儲存格G1求得在姓名欄位中的位置。
(1) OFFSET(E1,MATCH(G1,姓名,0),):透過 OFFSET 函數以相對位置求得體重。
(2) (OFFSET(D1,MATCH(G1,姓名,0),)/100)^2:透過 OFFSET 函數以相對位置求得身高。再將身高除以 100,再取平方數。
(3) BMI = (1)式/(2)式
最後使用 ROUND 函數將 BMI 設定顯示 2 位的小數點位數。
透過下拉式選單,你可以依姓名查詢其照片和BMI值。

2015年12月24日 星期四

Excel-資料呈現水平翻轉和垂直翻轉(OFFSET,ROW,COLUMN)

同事在工作時要處理 Excel 檔案,卻常發現資料的呈現方向不是自己想要的方向,所以需要手動調整,透過公式來轉換。
1. 將同一欄的資料垂直翻轉或同一列的資料水平翻轉
Excel-資料呈現水平翻轉和垂直翻轉(OFFSET,ROW,COLUMN)
垂直翻轉
儲存格C3:=OFFSET($A$3,10-ROW(1:1),)
10為資料個數,當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→
ROW(3:3)=3→…。
複製儲存格C3,貼至儲存格C3:C12。
水平翻轉
儲存格E7:=OFFSET($E$3,,10-COLUMN(A:A))
當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→…。
複製儲存格E7,貼至儲存格E7:N7。

2. 同時將同一欄的資料垂直翻轉和同一列的資料水平翻轉
Excel-資料呈現水平翻轉和垂直翻轉(OFFSET,ROW,COLUMN)
儲存格J1:=OFFSET($A$1,8-ROW(1:1),8-COLUMN(A:A))
資料個數水平和垂直均為8個。
複製儲存格J1,貼至儲存格J1:Q8。

PowerPoint-解決設定投影片母片後,有些投影片未被套用的問題

大家在使用 PowerPoint 時,通常會喜歡使用母片來將整個簡報設定為風格一致的投影片。並且如果有需要修改每張投影片的相同設定時,只要在母片中修改,每張投影片都會跟著變動,省時又省力。
但是,當你在設定投影片母片後,有時會發現一些頑強抵抗不願意改變的投影片,該如何處理?這也常發生在你接收了別人的簡報檔,想要修改成自己要的風格,卻有些投影片無法順利被修改。
如下圖的投影片狀態:
PowerPoint-解決設定投影片母片後,有些投影片未被套用的問題
當你在投影片母片修改了標題文字的色彩和大小:
PowerPoint-解決設定投影片母片後,有些投影片未被套用的問題
套用這個母片的設定後,發現有一張投影片仍文風不動:
PowerPoint-解決設定投影片母片後,有些投影片未被套用的問題
會發生這個現象是因為在這張投影片中,你曾經做過一些手動的設定,當 PowerPoint 套用母片時,並不會套用到手動的部分。所以,你可以切換到這張投影片,然後按一下[常用/投影片]功能表中的「重設」按鈕。
PowerPoint-解決設定投影片母片後,有些投影片未被套用的問題
原先投影片的手動設定會被移除,並且套用母片現在的設定。問題就解決了!
PowerPoint-解決設定投影片母片後,有些投影片未被套用的問題
所以,當你拿到一個別人給簡報或是先前自己設計的簡報,都可以透過「重設」的動作,再重新重頭的設計,變成是自己想要的樣式。

2015年12月23日 星期三

Excel-同一欄自動產生指定數字範圍內的數列(INT,MOD,ROW)

網友問到:在 Excel 中,如何在同一欄自動產生指定數字範圍內的數列?
參考下圖,如果使用手動方式,可以先產生所要的數列,然後再不斷的複製。如果使用公式,則可以讓結果變成動態顯示,隨時更改周期,即可產生不同的數列。
1. 指定周期(數列由 1 開始)
Excel-同一欄自動產生指定數字範圍內的數列(INT,MOD,ROW)
儲存格B2:=MOD(ROW(A1)-1,B$1)+1
ROW(A1):取得儲存格A1的列號(=1),當公式往下複製時會產生ROW(A1)=1→
ROW(A2)=2→ROW(A3)=3→...。
利用 MOD 函數求得除以周期的餘數。
公式相當於
儲存格B2:=MOD(ROW(A1)-1,周期)+1
複製儲存格B2,貼至儲存格B2:G2。複製儲存格B2:G2,往下各列貼上。

2. 指定起始值和終止值
Excel-同一欄自動產生指定數字範圍內的數列(INT,MOD,ROW)
儲存格B3:=B$1+MOD(ROW(A1)-1,(B$2-B$1+1))
公式相當於
儲存格B3:=起始值+MOD(ROW(A1)-1,(終止值-起始值+1))
複製儲存格B3,貼至儲存格B3:G3。複製儲存格B3:G3,往下各列貼上。

2015年12月21日 星期一

Excel-根據不連續編號將一列轉多列(INDEX,ROW,INT,MOD)

網友問到:在 Excel 有一個編號清單(如下圖左),其中每列的起始編號並不連續,但是每五個一組。想要將編號由一列轉多列呈現,該如何處理?
在下圖中,第一個編號:A000001-A000005,要轉成A000001、A000002、A000003、A000004、A000005,依此類推。
根據不連續編號將一列轉多列(INDEX,ROW,INT,MOD)

【公式設計與解析】
為了說明方便,先選取儲存格A1:A21,按 Ctrl+Shift+F3 鍵,定義名稱:編號。
儲存格C2:="A"&RIGHT("000000"&(INDEX(MID(編號,2,6),INT((ROW(1:1)-1)/5)
+1,1))+MOD(ROW(1:1)-1,5),6)
複製儲存格C2,往下各列貼上。
MID(編號,2,6):取得A欄中每一列不含「A」的數字(例如:000001、000002等)
其中 INT((ROW(1:1)-1)/5)+1 和 MOD(ROW(1:1)-1,5) 會產生如下圖的數列。
將以上二式代入 INDEX 函數依序取得 1→2→3→4→5→86→87→88→89→90→…。
RIGHT("000000"&(INDEX函數,6)):將INDEX函數的左側串接 6 個 0,再由右側取出 6 個字元,即可得到 000001→000002→000003→000004→000005→
000086→000087→000088→000089→000090→…。
最後,在上式的左側串接「A」,即大功告成。
根據不連續編號將一列轉多列(INDEX,ROW,INT,MOD)

【思考題】
如果每一列的編號個數不是固定為5,則公式如何設計。(目前我也還沒有完整的解決方案,待續囉…)

2015年12月18日 星期五

Excel-一列多欄轉多列一欄(OFFSET,INT,MOD,ROW)

網友想要將 Excel 資料表中的資料,由一列多欄轉多列一欄呈現(如下圖),該如何處理?
Excel-一列轉多列(OFFSET,INT,MOD,ROW)

【公式說明與解析】
儲存格F2:=OFFSET($A$1,INT(ROW(1:1)/4)+1,MOD(ROW(1:1),4)-1)
複製儲存格F2,貼至儲存格F2:F4。
INT(ROW(1:1)/4)+1:公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3。INT(ROW(1:1)/4)+1會傳回 1→1→1。
MOD(ROW(1:1),4)-1:公式向下複製時,會傳回 0→1→2。
在 OFFSET 函數中由儲存格A1當起點,取出相對位置,即可將一列中的三欄(A2:C2)轉為一欄中的三列(F2:F4)。相當於 OFFSET(A1,1,0)→OFFSET(A1,1,1)→OFFSET(A1,1,2)。
複製儲存格F1:F4,貼至儲存格F1:F24。
儲存格F7:F9:公式相當於 OFFSET(A1,2,0)→OFFSET(A1,2,1)→OFFSET(A1,2,2)。
儲存格F10:F13:公式相當於 OFFSET(A1,3,0)→OFFSET(A1,3,1)→OFFSET(A1,3,2)。
...
如此便可以將將一列中的三欄轉為一欄中的三列。
同理:
儲存格H2:=OFFSET($A$1,INT(ROW(1:1)/4)+7,MOD(ROW(1:1),4)-1)
(試著比較儲存格F2和H2的差異)
複製儲存格H2,貼至儲存格H2:H4。
複製儲存格H1:H4,貼至儲存格H1:H24。

2015年12月17日 星期四

Excel-符合條件之間隔欄(列)加總(SUMIF,SUMPRODUCT,陣列公式)

網友問到:在 Excel 中有些資料是間隔欄(列)呈現,例如在下圖中的屬性和數量分別有多欄,如何整合同一屬性計算數量?
一、間隔欄
image
【公式設計與解析】
列舉三種方式:
儲存格I2:=SUMIF($A2:$G2,I$1,$B2:$H2)
儲存格I2:=SUMPRODUCT(--($A2:$G2=M$1),$B2:$H2)
儲存格I2:{=SUM(IF($A2:$G2=I$1,$B2:$H2,FALSE))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
複製儲存格I2,貼至儲存格I2:K10。

二、間隔列
image
【公式設計與解析】
列舉三種方式:
儲存格B9:=SUMIF(B$1:B$7,$A9,B$2:B$8)
儲存格B9:=SUMPRODUCT(--(B$1:B$7=$A9),B$2:B$8)
儲存格B9:{=SUM(IF(B$1:B$7=$A9,B$2:B$8,FALSE))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
複製儲存格B9,貼至儲存格B9:J11。

Excel-台斤和公斤表示方式轉換(FIND,TEXT,INT,MOD)

網友問到:如果在 Excel 的資料表中有一個『台斤』的數列,其中『ntm』表示『幾斤幾兩』。如何能將A欄的台斤表示轉換成B欄的數值表示法?且要如何將台斤轉換成公斤來表示?
image

【公式設計與解析】
參考上圖,輸入以下公式:
(1) 轉換台斤為數值
儲存格B2:=LEFT(A2,FIND("t",A2)-1)+RIGHT(A2,LEN(A2)-FIND("t",A2))/16
FIND("t",A2)-1):利用 FIND 函數找出『t』的位置。
LEFT(A2,FIND("t",A2)-1):利用 LEFT 函數取出『t』左邊的文字。
RIGHT(A2,LEN(A2)-FIND("t",A2)):利用 RIGHT 函數取出『t』右邊的文字。

(2) 轉換台斤為公斤(數值)
儲存格D2:=ROUND(B2*0.6,3)
取出儲存格D2乘以0.6後的『小數』部分,並取小數三位。

(3) 轉換公斤表示法
儲存格E2:=TEXT(INT(D2),"?0")&"K"&TEXT(MOD(D2,1)*1000,"000")
INT(D2):取出儲存格D2中的『整數』部分。
TEXT(INT(D2),"?0"):設定整數的顯示格式為『?0』,以方便對齊。
MOD(D2,1)*1000:取出儲存格D2的小數部分,再乘以1000。
TEXT(MOD(D2,1)*1000,"000"):將上式的結果以三位數字表示。

2015年12月15日 星期二

Excel-將日期+時間的數列清單分別取出日期和時間(INT,MOD)

網友想要由 Excel 資料表中的日期+時間清單中,分離出日期和時間部分。分離出來的日期和時間,兩者都還是保持為日期格式。
在A欄中的內容都是由日期和時間組成。在 Excel 中每一天都是以數值『1』表示,所以每個日期都可以表示為:一個整數再加上小數。而每過一天,數值就會加 1。
(1) 日期/儲存格C2:=INT(A2)
使用 INT 函數取出不大於儲存格A2內容的最大整數,即可得到數數部分。
(2) 時間/儲存格E2:=MOD(A2,1)
使用 MOD 函數取出儲存格A2除以 1 的餘數,即可得到小數部分。
image

Excel-西元年和民國年互換表示(TEXT,MID,DATE,SUBSTITUTE)

網友問到在 Excel 中有一個『西元年』的清單,如何轉換為『民國年』的表示方式?
儲存格C2:=TEXT(A2,"yyyy")-1911&TEXT(A2,"mmdd")
儲存格E2:=TEXT(A2,"yyyy")-1911&"/"&TEXT(A2,"mm/dd")
注意:儲存格A1為數值,儲存格C2和儲存格E2會轉換為文字。
關於日期格式的設定,可以參考:http://isvincent.pixnet.net/blog/post/42385024
Excel-西元年和民國年互換表示(TEXT,MID,DATE,SUBSTITUTE)

另外,如果你要將民國年轉換為西元年的表示方式,則公式如下:
儲存格C2:=SUBSTITUTE(A2,"/","")
儲存格E2:=DATE(MID(A2,1,3)+1911,MID(A2,5,2),MID(A2,8,2))
注意:儲存格A2和儲存格C2為文字,儲存格E2為數字。
Excel-西元年和民國年互換表示(TEXT,MID,DATE,SUBSTITUTE)

2015年12月14日 星期一

Excel-建立圖檔超連結方便點選後開啟(HYPERLINK)

有網友想要在 Excel 中建立一個圖檔的清單,並且能直接點選後開啟圖檔,該如何處理?(參考下圖)
這是個不錯的問題,因為圖檔的命名,不見得都是以有意義的名稱來命名,或許只是使用流水號或是一些不具任何意義的編號。在 Excel 中如果能建立圖片內容的名稱和檔案名稱的對照表,或許有助於加速開啟想要的檔案。
image
假設所有的圖檔都放在資料夾中,路徑『E:\Google Downloads\images\images』。
儲存格C2:=HYPERLINK("E:\Google Downloads\images\images\"&B2,B2)
相當於儲存格C2:=HYPERLINK("路徑"&B2,B2)
當點選儲存格C2的超連結時,Excel 會跳出警示訊息,按下「是」按鈕,就能在預設的看圖軟體開啟。
image

2015年12月13日 星期日

Excel-為數值加上單位(數值格式,TEXT)

在 Excel 的資料表中,有網友想要為一些數值加上單位,例如『公斤』,該如何處理?參考下圖,又如何將公斤和公克分開給予單位?
Excel-為數值加上單位(數值格式,TEXT)

1. 為數值加上單位
假設數值整數部分最多二位數,小數部分最多三位數。
選取要加上單位的儲存格,為其設定儲存格格式。自訂格式:#0.0##"公斤"。
設定結果可以參考上圖中的 B 欄。
注意:設定儲存格自訂數值格式的方式,儲存格內容保持為『數值』。
image

2. 利用 TEXT 函數
儲存格E2:
=TEXT(INT(A2),"?0")&"公斤"&TEXT((A2-INT(A2))*1000,"000")&"公克"
TEXT(INT(A2),"?0")&"公斤":取出整數部分,並且串接文字:公斤。INT(A2)可將儲存格A2中的小數去除,並且利用 TEXT 函數設定數值格式:?0。
TEXT((A2-INT(A2))*1000,"000")&"公克":(A2-INT(A2))*1000用以取出小數部分,利用 TEXT 函數設定數值格式:000,並且串接文字:公克。
注意:使用 TEXT 函數設定儲存格數值格式的方式,儲存格內容轉變為『文字』。

Excel-略過空白儲存格將資料依序向左集中(OFFSET,COLUMN,PHONETIC)

其中提到了「取出每列有數值最左(右)欄的內容及對應的欄位」,有網友想要進一步,想要略過空白儲存格將資料依序向左集中。
如下圖,每一列中有部分儲存格是『空白』儲存格,現在要把非空白的儲存格向左依序集中,該如何處理?
Excel-略過空白儲存格將資料依序向左集中(OFFSET,SMALL,COLUMN,PHONETIC)

【公式設計與解析】
儲存格A13:{=OFFSET($A1,0,SMALL(IF(($A1:$M1<>""),COLUMN($A1:$M1),
999),COLUMN(A:A))-1)}
這是陣列公式,輸入完成按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
SMALL(IF(($A1:$M1<>""),COLUMN($A1:$M1),999):在陣列公式中,判斷儲存格A1:M1的內容是否不為空白,如果成立則傳回欄的編號,如果不成立則傳回999。(999參數只是隨意假設一個很大的數)
再透過 OFFSET 函數,以儲存格A1為起點,運用上式的結果來查詢相對應的欄內容。
複製儲存格A13,貼至儲存格A13:M21。
觀察下圖,其中空白儲存格會傳回『0』。
Excel-略過空白儲存格將資料依序向左集中(OFFSET,SMALL,COLUMN,PHONETIC)
如果要將儲存格中的 0,顯示為空白,則修改公式:
儲存格A13:{=IF(SMALL(IF(($A1:$M1<>""),COLUMN($A1:$M1),999),
COLUMN(A:A))=999,"",OFFSET($A1,0,SMALL(IF(($A1:$M1<>""),COLUMN
($A1:$M1),999),COLUMN(A:A))-1))}

【延伸練習】
如果你的儲存格內容中不包含公式,且每一個儲存格內容最多只 1 個字,則改用下列更簡單的公式。
儲存格A13:=MID(PHONETIC($A1:$M1),COLUMN(C:C),1)
使用 PHONETIC 函數將多個儲存格內容串接在一起,如果儲存格內容包含公式,則這個儲存格會被視為空白。
複製儲存格A13,貼至儲存格A13:M21。

2015年12月11日 星期五

Excel-取出每列有數值最左(右)欄的內容及對應的欄位(INDEX,COLUMN)

網友有興趣想要找出在 Excel 中的資料表(如下圖)中,每列最左欄或最右欄的數值及其對應的欄位名稱,該如何處理?
一、取出每列有數值最左欄的內容及對應的欄位
image
1. 使用陣列公式
(1) 最左欄數值
儲存格G2:{=INDEX(A2:F2,MIN(IF(A2:F2<>"",COLUMN(A2:F2))))}
或 儲存格G2:{=INDEX(A2:F2,MIN((A2:F2<>"")*COLUMN(A2:F2)))}
輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。
(2) 對應的欄位
儲存格H2:{=INDEX($A$1:$F$1,MIN(IF(A2:F2<>"",COLUMN(A2:F2))))}
或 儲存格H2:{=INDEX($A$1:$F$1,MIN((A2:F2<>"")*COLUMN(A2:F2)))}
輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。
複製儲存格G2:H2,往下各列貼上。

2. 使用 SUMPRODUCT 函數
(1) 最左欄數值
儲存格G2:=INDEX(A2:F2,SUMPRODUCT(SMALL((A2:F2<>"")*COLUMN(A2:F2),
COLUMN(F3)-SUM(1*(A2:F2<>""))+1)))
(2) 對應的欄位
儲存格H2:=INDEX(A1:F1,SUMPRODUCT(SMALL((A2:F2<>"")*COLUMN(A2:F2),
COLUMN(F3)-SUM(1*(A2:F2<>""))+1)))

二、取出每列有數值最右欄的內容及對應的欄位
image
1. 使用陣列公式
(1) 最右欄數值
儲存格G2:{=INDEX(A2:F2,MAX(IF(A2:F2<>"",COLUMN(A2:F2))))}
或 儲存格G2:{=INDEX(A2:F2,MAX((A2:F2<>"")*COLUMN(A2:F2)))}
輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。
(2) 對應的欄位
儲存格H2:{=INDEX($A$1:$F$1,MAX(IF(A2:F2<>"",COLUMN(A2:F2))))}
或 儲存格H2:{=INDEX($A$1:$F$1,MAX((A2:F2<>"")*COLUMN(A2:F2)))}
輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。

2. 使用 SUMPRODUCT 函數
(1) 最右欄數值
儲存格G2:=INDEX(A2:F2,SUMPRODUCT(MAX((A2:F2<>"")*COLUMN(A2:F2))))
(2) 對應的欄位
儲存格H2:=INDEX($A$1:$F$1,SUMPRODUCT(MAX((A2:F2<>"")*
COLUMN(A2:F2))))

Excel-計算兩個時間相差幾時幾分(TEXT)

網友問到:在 Excel 中,如何求兩個時間點之間相差幾時幾分?以下圖為例,時間點A和時間點B,相差不超過 24 小時,要求兩個時間點之間相差幾時幾分。
Excel-計算兩個時間相差幾時幾分(TEXT)
在 Excel 中每一個日期時間都代表一個數字,而以 1 代表一天,每小時為 1/24,每分鐘為 1/(24X60),每秒鐘為 1/(24X60X60)。所以兩個時間點只要直接相減,即可取得時間差。從這個時間差如何轉換為幾時幾分?
如果你只是想要呈現幾分幾秒的文字,透過 TEXT 是最簡單的方式。
儲存格D2:=TEXT(B2-A2,"hh"&"時"&"mm"&"分")
複製儲存格D2,往下各列貼上。
如果你要維持結果是個數字,則儲存格D2:=B2-A2。
然後設定儲存格D2的數值格式為『自訂格式』,類型填入:hh"時"mm"分"
hh"時"mm"分"

【補充資料】
在 TEXT 函數中,關於時間的參數:
h
將小時顯示為數字,前面不補零。
[h]
以小時顯示經過的時間。若使用的公式傳回的時間中時數超過 24,請使用類似 [h]:mm:ss 的數字格式。
hh
將小時顯示為數字,適當時前面會補零。 如果格式包含 AM 或 PM,則會依照 12 小時制來顯示小時。 否則小時將以 24 小時制表示。
m
將分鐘顯示為數字,前面不補零。
[m] 
以分鐘顯示經過的時間。若使用傳回時間的公式,其中分鐘數超過 60,請使用類似 [mm]:ss 的數字格式。
mm 
將分鐘顯示為數字,適當時前面會補零。
s  
將秒鐘顯示為數字,前面不補零。
[s]
以秒鐘顯示經過的時間。若使用傳回時間的公式,其中秒數超過 60,請使用類似 [ss] 的數字格式。
  ss 
將秒鐘顯示為數字,適當時前面會補零。如果您要顯示秒鐘的分數,請使用類似 h:mm:ss.00 的數字格式。
AM/PM, am/pm, A/P, a/p
使用 12 小時制來顯示小時。 如果是午夜到中午的時間,Excel 會顯示 AM、am、A 或 a;如果是中午到午夜的時間,則會顯示 PM、pm、P 或 p。
您或許對這些文章有興趣:

Excel-使用樞分析表建立表格式摘要結果

在學校中常會有一些報名表(如下圖),筆數可能上千筆,如何在 Excel 中使用『樞紐分析表』當為工具,建立一個摘要表呢(參考下圖)?
實務面上應該是要先有摘要表,才能在報名表中配置相關資料,但是現在的題目是同仁想要由報名表來反推摘要表。
使用樞分析表建立表格式摘要結果
假設如下圖的報名表(內容超過1000筆):
使用樞分析表建立表格式摘要結果
請你插入一個樞紐分析表,本例在「列」區域中插入了『科目、學期、教師、時數』四個欄位,結果出來的結果如下圖,是一個大網模式(階層)的呈現方式。要如何呈現為「表格」的模式? 
使用樞分析表建立表格式摘要結果
先選取一個欄位(本例為:科目),按一下[樞紐分析表工具/作用中欄位]功能表單中的「欄位設定」,開啟[欄位設定]對話框。(參考下圖)
使用樞分析表建立表格式摘要結果
接著,在[小計與篩選]標籤下,將「小計」設定為『無』。
再來,在[版面配置與列印]標籤下,在「版面配置」設定為『以列表方式顯示項目標籤』。
使用樞分析表建立表格式摘要結果
重覆上述步驟,將四個欄位都做相同設定,得到如下圖的結果。其中,有部分欄位的標籤內容是空白的,如何將這些空白填入標籤呢?
使用樞分析表建立表格式摘要結果
你可以在這個欄位的[欄位設定]對話框中的[版面配置與列印]標籤下,勾選:重覆項目標籤。
使用樞分析表建立表格式摘要結果
結果如下:
使用樞分析表建立表格式摘要結果
將內容複製到其他工作表使用:
使用樞分析表建立表格式摘要結果

好康東東