有網友問到:在 Excel 中有一個報名資料總表,如何讓報名結果依梯次顯示在不同工作表中?
如下圖的報名總表,其梯次有三種選項:一月、二月、三月。
如何將資料自動顯示在『一月、二月、三月』不同的工作表中?
有網友問到:在 Excel 中有一個報名資料總表,如何讓報名結果依梯次顯示在不同工作表中?
如下圖的報名總表,其梯次有三種選項:一月、二月、三月。
如何將資料自動顯示在『一月、二月、三月』不同的工作表中?
網友根據先前的這篇文章:Excel-多條件的查詢(INDEX+SMALL+陣列),發現他使用的 Excel 版本無法使用 IFERROR 函數,該如何修改公式?
在下圖中,其實是要根據三個條件(編號、外形、尺寸)求得對應的售價,而且當查無資料時以空白顯示,該如何處理?
【公式設計與說明】
選取儲存格A1:C19,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號、外形、尺寸。
在 Excel 中如果你有一些統計圖是經常性要產生且格式固定,你大概不會想要每次重覆多個步驟來設定,希望能有快速生成的方式。將圖表儲存成『範本』,是個不錯的選擇!
參考下圖,在本例中有三個表格要產生相同格式的統計圖。
當你取一個表格產生了一個統計圖,也都調整好所有的格式。
在統計圖的圖表區中,按一下右鍵,選取「另存為範本」。
網友問到:在 Excel 中如果要將一列多欄轉為多列多欄,該如何處理?
參考下圖,想要將同一列中的儲存格A1:R1,轉換為儲存格A5:D9(四欄)。
【公式設計與解析】
儲存格A5:=OFFSET($A$1,0,+(ROW(1:1)-1)*4+MOD(COLUMN(A:A)-1,4),1,1)
網友問到:參考下圖,在 Excel 中,如何將表格中的欄列互換?
在下圖中,如果你使用複製功能,再使用『轉置』貼上,即可得到欄列互換的結果。但是,如果要使用公式來設計,該如何處理?
【公式設計與解析】
透過 OFFSET 函數處理欄列互換很簡單!根據微軟的定義:
在 Word 中或許你使用過『樣式』功能,來加速文書排版工作,並且讓文件不同地方的版面效果趨近於一致,更可以加速修改格式時的速度。
而在 Excel 中也可以使用『樣式』功能!例如,在下圖中有三個表格,其中一個已經格式設定完成,如果要讓另外兩個表格,也是套用相同的表格格式。相信你的直覺做法是用『複製格式』,刷二下即可完成,當然,這是沒問題的。但是,若要修改其中的格式,就得重覆操作。
本例特別使用『樣式』功能來練習。
1. 選取本例中的儲存格A1:D1。(這些儲存格已經設定跨欄置中效果)
有老師問到:如下圖,在 Excel 中如何製作這個二週小考平均的比較表?
在下圖中,已有兩週的小考平均,如何在第2週比第1週的平均進步時顯示綠色正三角型圖示,在第2週比第1週的平均退步時顯示紅色倒三角型圖示?
【公式設計與解析】
1. 先在 K 欄中計算第2週和第1週的平均值的差。
網友根據這篇文章:Excel-表格的轉換(OFFSET,SUMPRODUCT,ROW),問到想要如下圖中由B表格查詢A表格,該如何處理?
【公式設計與解析析】
儲存格E3:{=IFERROR(OFFSET($A$1,SMALL(IF($B$3:$B$18=$D3,ROW
($B$3:$B$18),""),COLUMN(A:A))-1,0),"")}
網友想要在 Excel 中做資料處理,參考下圖中的數值欄位,要依規則取後2碼來調整「進位」,該如何處理?
規則:
(1) 若後2碼<=50,則調整為:50
(2) 若後2碼>50,則調整為:100
網友問到:如何在 Excel 中將一個矩陣資料內容轉換為一欄?參考下圖,儲存格A1:D5中的資料要轉換至儲存格A7:A26。
【公式設計與解析】
儲存格A7:=OFFSET($A$1,MOD(ROW(1:1)-1,5),INT((ROW(1:1)-1)/5),1,1)
複製儲存格A7,往下各列貼上。
參考下圖,在 Excel 中,如果要將儲存格內容「877/13/3214/6481/643/4486」的文字,依其分隔符號『/』取出『877、13、3214、6481、643、4486』,該如何處理?注意其分隔符號之間的內容的文字長度並不一致。
通常,我們會使用 Excel 中的「資料部析」工具來處理,手動操作也很方便,但是如果想要以公式來處理,或像下圖中要把取出的資料放在同一欄中。(資料剖析工具只能將資料部析結果放在同一列中)
【公式設計與解析】
參考上圖,假設:本例的資料中有五個分隔符號『/』,將資料分成六組。
網友想要在 Excel 的工作表中將大量資料的前幾碼刪除,該如何處理?
在下圖中的『資料』欄位中,欄位中的每筆資料都是一樣長度的,但是也可能會遇到資料的長度不一樣的狀況,所以使用的公式要有一點點彈性。
【公式設計與解析】
(1) 省略第1個0
儲存格B2:=MID(A2,2,99)
網友問到:如下圖的 Excel 資料清單中,如何能找出不同料號的最低價廠商?
下圖中的基本資料有:廠商名稱、料號和單價,而單價有可能不同廠商相同單價。在本例中如有相同單價的廠商,則僅列出第一個廠商。
【公式設計與解析】
(1) 為了便於說明,先設定儲存格範圍名稱:
網友問到一個關於用 Excel 來根據打卡清單計算工時的問題,該如何處理?
參考下圖,其條件如下:
8:00~8:14→8:00;8:15~8:30→8:30;8:30~8:44→8:30;8:45~9:00→9:00
【公式設計與解析】
網友很常問的問題:在 Excel 中如何轉換日期格式,通常會有國曆和西曆的轉換,或是和格式相關的轉換等。
例如:下圖中要將一個國曆的年月日格式轉換為西曆的年/月/日格式。其中國曆年為三碼,西月年為四碼,月和日都是二碼。
【公式設計與解析】
在 Excel 中的儲存格內容常會是數字和非數字混合,如何能找出某個儲存格中第一個非數字的位置呢?(參考下圖)
【公式設計與解析】
先假設儲存格內容不會超過 20 個字元組成。
儲存格B2:{=MATCH(FALSE,ISNUMBER(VALUE(MID(A2,ROW($1:$20),1))),0)}
有老師問到:如果成績中有些數字欄位呈現了文字或是錯誤訊息時,可否不要列入排序,公式可如何處理?
參考下圖,一個成績表中含有『轉學、休學、缺考』等文字,還有錯誤訊息,當在排序時要排除這三個,不要顯示排序結果。如果你使用 RANK 函數,會得到G欄的結果。
【公式設計與解析】
儲存格I2:=IFERROR(RANK(F2,IF(ISNUMBER($F$2:$F$26),F$2:F$26,FALSE)),"")
在 Excel 的工作表中,有時會用到要在資料清單中反推數值所在的儲存格。如下圖,要如何找出儲存格L1的內容對應儲存格A1:J10中相符者的儲存格位址?
【公式設計與解析】
假設儲存格A1:J10的內容具唯一性,不會重覆。
儲存格L4:{=ADDRESS(SUM((A1:J10=L1)*ROW(1:10)),SUM((A1:J10=L1)*
如果在 Excel 的工作表中有一個資料清單,其中每一個儲存格是由數值和單位所組成,如何能將數值和單位分離出來?
參考下圖,資料清單中的數值和單位的字元數都不相同,如何分離其中的數值和單位?
【公試設計與解析】
(1) 取出數值