贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201512 (24)

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

有網友想要將一個數拆解為幾個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的數值。

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

有網友想要根據 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)

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

讀者想要在以下的 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)

如此,便可以在小計欄位自動顯示較深底色。

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

在 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值。

image

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

同事在工作時要處理 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。

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

大家在使用 PowerPoint 時,通常會喜歡使用母片來將整個簡報設定為風格一致的投影片。並且如果有需要修改每張投影片的相同設定時,只要在母片中修改,每張投影片都會跟著變動,省時又省力。

但是,當你在設定投影片母片後,有時會發現一些頑強抵抗不願意改變的投影片,該如何處理?這也常發生在你接收了別人的簡報檔,想要修改成自己要的風格,卻有些投影片無法順利被修改。

如下圖的投影片狀態:

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

當你在投影片母片修改了標題文字的色彩和大小:

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

套用這個母片的設定後,發現有一張投影片仍文風不動:

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

會發生這個現象是因為在這張投影片中,你曾經做過一些手動的設定,當 PowerPoint 套用母片時,並不會套用到手動的部分。所以,你可以切換到這張投影片,然後按一下[常用/投影片]功能表中的「重設」按鈕。

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

原先投影片的手動設定會被移除,並且套用母片現在的設定。問題就解決了!

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

所以,當你拿到一個別人給簡報或是先前自己設計的簡報,都可以透過「重設」的動作,再重新重頭的設計,變成是自己想要的樣式。

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

網友問到:在 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,往下各列貼上。

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

網友問到:在 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,則公式如何設計。(目前我也還沒有完整的解決方案,待續囉…)

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

網友想要將 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。

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

網友問到:在 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。

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

網友問到:如果在 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"):將上式的結果以三位數字表示。

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

網友想要由 Excel 資料表中的日期+時間清單中,分離出日期和時間部分。分離出來的日期和時間,兩者都還是保持為日期格式。

在A欄中的內容都是由日期和時間組成。在 Excel 中每一天都是以數值『1』表示,所以每個日期都可以表示為:一個整數再加上小數。而每過一天,數值就會加 1。

(1) 日期/儲存格C2:=INT(A2)

使用 INT 函數取出不大於儲存格A2內容的最大整數,即可得到數數部分。

(2) 時間/儲存格E2:=MOD(A2,1)

使用 MOD 函數取出儲存格A2除以 1 的餘數,即可得到小數部分。

image

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

網友問到在 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)

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

有網友想要在 Excel 中建立一個圖檔的清單,並且能直接點選後開啟圖檔,該如何處理?(參考下圖)

這是個不錯的問題,因為圖檔的命名,不見得都是以有意義的名稱來命名,或許只是使用流水號或是一些不具任何意義的編號。在 Excel 中如果能建立圖片內容的名稱和檔案名稱的對照表,或許有助於加速開啟想要的檔案。

image

假設所有的圖檔都放在資料夾中,路徑『E:\Google Downloads\images\images』。

儲存格C2:=HYPERLINK("E:\Google Downloads\images\images\"&B2,B2)

相當於儲存格C2:=HYPERLINK("路徑"&B2,B2)

當點選儲存格C2的超連結時,Excel 會跳出警示訊息,按下「是」按鈕,就能在預設的看圖軟體開啟。

image

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

在 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 函數設定儲存格數值格式的方式,儲存格內容轉變為『文字』。

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

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

其中提到了「取出每列有數值最左(右)欄的內容及對應的欄位」,有網友想要進一步,想要略過空白儲存格將資料依序向左集中。

如下圖,每一列中有部分儲存格是『空白』儲存格,現在要把非空白的儲存格向左依序集中,該如何處理?

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。

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

網友有興趣想要找出在 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))))

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

在學校中常會有一些報名表(如下圖),筆數可能上千筆,如何在 Excel 中使用『樞紐分析表』當為工具,建立一個摘要表呢(參考下圖)?

實務面上應該是要先有摘要表,才能在報名表中配置相關資料,但是現在的題目是同仁想要由報名表來反推摘要表。

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

假設如下圖的報名表(內容超過1000筆):

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

請你插入一個樞紐分析表,本例在「列」區域中插入了『科目、學期、教師、時數』四個欄位,結果出來的結果如下圖,是一個大網模式(階層)的呈現方式。要如何呈現為「表格」的模式? 

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

先選取一個欄位(本例為:科目),按一下[樞紐分析表工具/作用中欄位]功能表單中的「欄位設定」,開啟[欄位設定]對話框。(參考下圖)

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

接著,在[小計與篩選]標籤下,將「小計」設定為『無』。

再來,在[版面配置與列印]標籤下,在「版面配置」設定為『以列表方式顯示項目標籤』。

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

重覆上述步驟,將四個欄位都做相同設定,得到如下圖的結果。其中,有部分欄位的標籤內容是空白的,如何將這些空白填入標籤呢?

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

你可以在這個欄位的[欄位設定]對話框中的[版面配置與列印]標籤下,勾選:重覆項目標籤。

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

結果如下:

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

將內容複製到其他工作表使用:

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

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

網友問到:在 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]
以秒鐘顯示經過的時間。若使用傳回時間的公式,其中秒數超過 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。

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

被網友問過最多次的 Excel 問題,可以說是『從完整清單中分列不同項目的清單』這樣的題目,像是下圖中「工作」欄位中有四種項目類別:清潔、收銀、樓面、廚房,想要由這個總表裡篩選各類項目的清單。

Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)

你可以使用排序工具,對「工作」欄位排序,即可將相同項目集合在一起,再分四次複製。

Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)

也可以手動使用篩選工具,分四次篩選各個項目,再複製出來使用。

Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)

如果你使用『樞紐分析』工具,可以將姓名置於『列』,將工作置於『篩選』,也是可以執行篩選動作。

Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)


【使用公式】

除了上述手動方式,如果你想使用公式來處理,可以參考以下三種不同的方式。

首先,選取B欄中所有含有資料的儲存格,定義名稱:工作。


(1) 使用 SUMPRODUCT+LARGE

使用這個公式會將原始資料後出現者,重新排列時會先列出。(參考下圖)

Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)

儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(LARGE((工作=D$1)*
ROW(工作),ROW(1:1)))-1,),"")

SUMPRODUCT(LARGE((工作=D$1)*ROW(工作),ROW(1:1))):找出『工作』陣列中所有和儲存格D1相同者的列號,再由 LARGE 函數找出最大第 1 個值。

ROW(1:1)=1,向下複製公式時,會產生 ROW(2:2)=2→ROW(3:3)=3→ROW(4:4)=4→…。因此可以由 LARGE 函數找出最大第 2、3、4、… 個值。

透過 OFFSET 函數代入上式的列號,找到相對於儲存格A1的位置。

IFERROR 函數用以將 OFFSET 函數查詢得到錯誤訊息者,修正為空白。

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


(2) 使用 SUMPRODUCT+SMALL+陣列公式

使用這個公式會將原始資料先出現者,重新排列時會先列出。(參考下圖)

Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)

儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(工作=D$1,ROW(工作),
9999),ROW(1:1))-1,),"")}

輸入完成,要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。

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


(3) 使用 SUMPRODUCT+SMALL+不使用陣列公式

使用這個公式會將原始資料先出現者,重新排列時會先列出。(參考上圖)

儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((工作=D$1)*
ROW(工作),ROW(1:1)+COUNTA(工作)-SUM(1*(工作=D$1))))-1,),"")

這個公式比(2)複雜,但是不使用陣列公式。

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

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

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼