在 Excel 檔案中用來計算與分析資料大多以表格和統計圖表呈現,如果有要用到呈現圖片,都需要靠手動方式來設定。這次來練習一個有趣的例子,如何才能做到像下圖中,輸入一個姓名,即能查詢這個人的照片?
在下圖中的例子,希望能選取一個人的姓名後,能自動顯示這個人的照片和計算其BMI值。
【公式設計與解析】
假設資料放在儲存格A1:E20。
在 Excel 檔案中用來計算與分析資料大多以表格和統計圖表呈現,如果有要用到呈現圖片,都需要靠手動方式來設定。這次來練習一個有趣的例子,如何才能做到像下圖中,輸入一個姓名,即能查詢這個人的照片?
在下圖中的例子,希望能選取一個人的姓名後,能自動顯示這個人的照片和計算其BMI值。
【公式設計與解析】
假設資料放在儲存格A1:E20。
同事在工作時要處理 Excel 檔案,卻常發現資料的呈現方向不是自己想要的方向,所以需要手動調整,透過公式來轉換。
1. 將同一欄的資料垂直翻轉或同一列的資料水平翻轉
垂直翻轉
儲存格C3:=OFFSET($A$3,10-ROW(1:1),)
10為資料個數,當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→
大家在使用 PowerPoint 時,通常會喜歡使用母片來將整個簡報設定為風格一致的投影片。並且如果有需要修改每張投影片的相同設定時,只要在母片中修改,每張投影片都會跟著變動,省時又省力。
但是,當你在設定投影片母片後,有時會發現一些頑強抵抗不願意改變的投影片,該如何處理?這也常發生在你接收了別人的簡報檔,想要修改成自己要的風格,卻有些投影片無法順利被修改。
如下圖的投影片狀態:
當你在投影片母片修改了標題文字的色彩和大小:
網友問到:在 Excel 中,如何在同一欄自動產生指定數字範圍內的數列?
參考下圖,如果使用手動方式,可以先產生所要的數列,然後再不斷的複製。如果使用公式,則可以讓結果變成動態顯示,隨時更改周期,即可產生不同的數列。
1. 指定周期(數列由 1 開始)
儲存格B2:=MOD(ROW(A1)-1,B$1)+1
ROW(A1):取得儲存格A1的列號(=1),當公式往下複製時會產生ROW(A1)=1→
網友問到:在 Excel 有一個編號清單(如下圖左),其中每列的起始編號並不連續,但是每五個一組。想要將編號由一列轉多列呈現,該如何處理?
在下圖中,第一個編號:A000001-A000005,要轉成A000001、A000002、A000003、A000004、A000005,依此類推。
【公式設計與解析】
為了說明方便,先選取儲存格A1:A21,按 Ctrl+Shift+F3 鍵,定義名稱:編號。
網友想要將 Excel 資料表中的資料,由一列多欄轉多列一欄呈現(如下圖),該如何處理?
【公式說明與解析】
儲存格F2:=OFFSET($A$1,INT(ROW(1:1)/4)+1,MOD(ROW(1:1),4)-1)
複製儲存格F2,貼至儲存格F2:F4。
網友問到:在 Excel 中有些資料是間隔欄(列)呈現,例如在下圖中的屬性和數量分別有多欄,如何整合同一屬性計算數量?
一、間隔欄
【公式設計與解析】
列舉三種方式:
儲存格I2:=SUMIF($A2:$G2,I$1,$B2:$H2)
網友問到:如果在 Excel 的資料表中有一個『台斤』的數列,其中『ntm』表示『幾斤幾兩』。如何能將A欄的台斤表示轉換成B欄的數值表示法?且要如何將台斤轉換成公斤來表示?
【公式設計與解析】
參考上圖,輸入以下公式:
(1) 轉換台斤為數值
網友想要由 Excel 資料表中的日期+時間清單中,分離出日期和時間部分。分離出來的日期和時間,兩者都還是保持為日期格式。
在A欄中的內容都是由日期和時間組成。在 Excel 中每一天都是以數值『1』表示,所以每個日期都可以表示為:一個整數再加上小數。而每過一天,數值就會加 1。
(1) 日期/儲存格C2:=INT(A2)
使用 INT 函數取出不大於儲存格A2內容的最大整數,即可得到數數部分。
(2) 時間/儲存格E2:=MOD(A2,1)
使用 MOD 函數取出儲存格A2除以 1 的餘數,即可得到小數部分。
網友問到在 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 中建立一個圖檔的清單,並且能直接點選後開啟圖檔,該如何處理?(參考下圖)
這是個不錯的問題,因為圖檔的命名,不見得都是以有意義的名稱來命名,或許只是使用流水號或是一些不具任何意義的編號。在 Excel 中如果能建立圖片內容的名稱和檔案名稱的對照表,或許有助於加速開啟想要的檔案。
假設所有的圖檔都放在資料夾中,路徑『E:\Google Downloads\images\images』。
儲存格C2:=HYPERLINK("E:\Google Downloads\images\images\"&B2,B2)
相當於儲存格C2:=HYPERLINK("路徑"&B2,B2)
在 Excel 的資料表中,有網友想要為一些數值加上單位,例如『公斤』,該如何處理?參考下圖,又如何將公斤和公克分開給予單位?
1. 為數值加上單位
假設數值整數部分最多二位數,小數部分最多三位數。
選取要加上單位的儲存格,為其設定儲存格格式。自訂格式:#0.0##"公斤"。
上一篇文章:Excel-取出每列有數值最左(右)欄的內容及對應的欄位(INDEX,COLUMN)
其中提到了「取出每列有數值最左(右)欄的內容及對應的欄位」,有網友想要進一步,想要略過空白儲存格將資料依序向左集中。
如下圖,每一列中有部分儲存格是『空白』儲存格,現在要把非空白的儲存格向左依序集中,該如何處理?
【公式設計與解析】
網友有興趣想要找出在 Excel 中的資料表(如下圖)中,每列最左欄或最右欄的數值及其對應的欄位名稱,該如何處理?
一、取出每列有數值最左欄的內容及對應的欄位
1. 使用陣列公式
(1) 最左欄數值
儲存格G2:{=INDEX(A2:F2,MIN(IF(A2:F2<>"",COLUMN(A2:F2))))}
在學校中常會有一些報名表(如下圖),筆數可能上千筆,如何在 Excel 中使用『樞紐分析表』當為工具,建立一個摘要表呢(參考下圖)?
實務面上應該是要先有摘要表,才能在報名表中配置相關資料,但是現在的題目是同仁想要由報名表來反推摘要表。
假設如下圖的報名表(內容超過1000筆):
請你插入一個樞紐分析表,本例在「列」區域中插入了『科目、學期、教師、時數』四個欄位,結果出來的結果如下圖,是一個大網模式(階層)的呈現方式。要如何呈現為「表格」的模式?
網友問到:在 Excel 中,如何求兩個時間點之間相差幾時幾分?以下圖為例,時間點A和時間點B,相差不超過 24 小時,要求兩個時間點之間相差幾時幾分。
在 Excel 中每一個日期時間都代表一個數字,而以 1 代表一天,每小時為 1/24,每分鐘為 1/(24X60),每秒鐘為 1/(24X60X60)。所以兩個時間點只要直接相減,即可取得時間差。從這個時間差如何轉換為幾時幾分?
如果你只是想要呈現幾分幾秒的文字,透過 TEXT 是最簡單的方式。
儲存格D2:=TEXT(B2-A2,"hh"&"時"&"mm"&"分")
複製儲存格D2,往下各列貼上。
被網友問過最多次的 Excel 問題,可以說是『從完整清單中分列不同項目的清單』這樣的題目,像是下圖中「工作」欄位中有四種項目類別:清潔、收銀、樓面、廚房,想要由這個總表裡篩選各類項目的清單。
你可以使用排序工具,對「工作」欄位排序,即可將相同項目集合在一起,再分四次複製。
也可以手動使用篩選工具,分四次篩選各個項目,再複製出來使用。
網友問到一個問題:在 Excel 的工作表中,如果只給予儲存格的號碼,如何能計算加總?以下圖為例,如果給予 2 個號碼:22、27,要對照儲存格的位置(B22和B27),再予以加總(=B22+B27=48+47=95)。
【公式設計與解析】
若要將英文字和數字所組成的文字,轉換成一個儲存格位址,則必須靠 INDIRECT 函數來處理。
儲存格G12:=INDIRECT("B"&D12)+INDIRECT("B"&D13)
本範例是延續前四篇文章的研習講義:
1. Excel-SUMPRODUCT函數範例與說明(研習範例)
2. Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)
3. Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)
4. Excel-多人多項的金額統計(SUMPRODUCT練習)
這次來練習在 SUMPRODUCT 函數中搭配其他函數達成篩選並統計的功能。參考下圖,列有五種計算一二三年級各項比賽的人數。