CV上經常會提及到Microsoft office的技巧,不少人也會寫下Excel,但你又對Excel有多深認識?Excel就只是把資料好好排列的功能而已?Excel公式你懂多少?Freehunter為你整理Excel實用公式!
「VLOOKUP」中的「V」是指Vertical,表示垂直方向(在同一欄)查找資料。
=VLOOKUP(尋找的值,資料範圍, 欄數,False)
以下的例子詳細說明VLOOKUP的用法,Excel記錄了Freehunter用戶的資料(虛構),複製以下圖表,一起試試尋找下列資料。
(把「姓名」儲存格貼在A1)
姓名 | 編號 | 職業 | 年齡 |
Tom | 1 | 平面設計師 | 23 |
Amy | 2 | 畫家 | 35 |
John | 3 | 攝影師 | 27 |
Susan | 4 | 翻譯員 | 29 |
Maggie | 5 | 程式開發員 | 30 |
Yuki | 6 | 畫家 | 24 |
James | 7 | 平面設計師 | 27 |
Tim | 8 | 翻譯員 | 32 |
Henry | 9 | 平面設計師 | 38 |
Natalie | 10 | 攝影師 | 26 |
Mary | 11 | 攝影師 | 31 |
William | 12 | 攝影師 | 32 |
(把「姓名」儲存格貼在G6)
姓名 | 職業 |
Yuki | (公式1) |
Natalie | (公式2) |
William | (公式3) |
=VLOOKUP(尋找的值,資料範圍, 欄數,False)
希望尋找Yuki的職業,於是「Yuki」成為「尋找的值」;「資料範圍」是為整個表格,由儲存格A1至D13;「職業」欄數是第三欄,所以公式1:=VLOOKUP(G7,$A$1:$D$13,3,FALSE)。
「資料範圍」加上$,會把所選的儲存格範圍定在「絕對位置」,當公式套用到其他的儲存格時,「資料範圍」儲存格不會受影響。因此完成「Yuki」一格後,把表格下拉就可以自動完成其餘公式。
公式2:=VLOOKUP(G8,$A$1:$D$13,3,FALSE)
公式3:=VLOOKUP(G9,$A$1:$D$13,3,FALSE)
「HLOOKUP」中的 「H」是指Horizontal,表示水平方向(在同一列)查找資料。
=HLOOKUP(G7,$A$1:$C$13,3,FALSE)
為幫助大家更容易了解,以下資料與上部分的表格一樣,只是改變了資料排序。
(把「姓名」儲存格貼在A1)
姓名 | Tom | Amy | John | Susan | Maggie | Yuki | James | Tim | Henry | Natalie | Mary | William |
編號 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
職業 | 平面設計師 | 畫家 | 攝影師 | 翻譯員 | 程式開發員 | 畫家 | 平面設計師 | 翻譯員 | 平面設計師 | 攝影師 | 攝影師 | 攝影師 |
年齡 | 23 | 35 | 27 | 29 | 30 | 24 | 27 | 32 | 38 | 26 | 31 | 32 |
(把「姓名」儲存格貼在A7)
姓名 | Yuki | Natalie | William |
職業 | (公式1) | (公式2) | (公式3) |
和VLOOKUP的運作模式一樣,「Yuki」成為「尋找的值」;「資料範圍」是為整個表格,由儲存格A1至M4;但這次由水平方向尋找資料,「職業」列數是第三列,所以公式1:=HLOOKUP(B7,$A$1:$M$4,3,FALSE)。
公式2:=HLOOKUP(C7,$A$1:$M$4,3,FALSE)
公式3:=HLOOKUP(D7,$A$1:$M$4,3,FALSE)
1. SUM:計算所選儲存格中所有數值的總和
=SUMIF(數值範圍)
2. SUMIF:計算所選儲存格中,滿足一個指定條件數值的總和
=SUMIF(條件範圍,條件)
=SUMIF(條件範圍,條件,數值範圍)
3. SUMIFS:計算所選儲存格中,滿足所有指定條件數值的總和
=SUMIFS(數值範圍,條件範圍1,條件1,條件範圍2,條件2,…)
4. AVERAGE:計算所選儲存格中,滿足一個指定條件數值的平均值
=AVERAGE(數值範圍)
5. AVERAGEIF:計算所選儲存格中,滿足一個指定條件數值的平均值
=AVERAGEIF(條件範圍,條件)
=AVERAGEIF(條件範圍,條件,數值範圍)
6. AVERAGEIFS:計算所選儲存格中,滿足所有指定條件數值的平均值
=AVERAGEIFS(數值範圍,條件範圍1,條件1,條件範圍2,條件2,…)
7. COUNTIF:計算所選儲存格中,滿足一個指定條件數值的儲存格數目
=COUNTIF(統計範圍,條件)
Excel記錄了上半年商店每單交易的詳細資料,複製以下圖表,一起試試計算下列項目,看看你掌握多少!
(把「月份」儲存格貼在A1)
月份 | 類別 | 銷售額 |
一月 | 水果 | 2500 |
三月 | 水果 | 3750 |
二月 | 水果 | 2635 |
一月 | 水果 | 2940 |
四月 | 蔬菜 | 2395 |
二月 | 水果 | 2500 |
六月 | 蔬菜 | 1860 |
五月 | 蔬菜 | 2365 |
一月 | 蔬菜 | 2500 |
一月 | 蔬菜 | 1920 |
五月 | 水果 | 3940 |
五月 | 水果 | 2700 |
1. 上半年銷售總額
=SUM(C2:C13)
2. 上半年3000 元或以上的銷售總額
=SUMIF(C2:C13,”>=3000″)
3. 上半年水果的銷售總額
=SUMIF(B2:B13,”水果”,C2:C13)
4. 一月份蔬菜的銷售總額
=SUMIFS(C2:C13,A2:A13,”一月”,B2:B13,”蔬菜”)
5. 上半年每單交易的平均金額
=AVERAGE(C2:C13)
6. 上半年3000 元以上交易的平均金額
=AVERAGEIF(C2:C13,”>=3000″)
7. 上半年蔬菜交易的平均金額
=AVERAGEIF(B2:B13,”蔬菜”,C2:C13)
8. 一月份水果交易的平均金額
=AVERAGEIFS(C2:C13,A2:A13,”一月”,B2:B13,”水果”)
9. 上半年銷售額大於$2500交易數量
=COUNTIF(C2:C13,”>2500″)
10. 上半年蔬菜交易數量
=COUNTIF(B2:B13,”蔬菜”)
答案:
以上公式是EXCEL入門的基本,希望可以協助大家進行簡單的操作,讓大家在數據處理上更得心應手!如果需要進行更仔細的數據分析,可以邀請Freehunter的Freelancer協助!
Freehunter有超過30,000位Freelancer,超過19種行業!不少Freelancer都具備豐富經驗,趕快免費發佈工作以尋找獲取準確報價吧!
精選30,000+位Freelancer,涵蓋18種不同行業,
一個網站一應俱全。