Excel函式進階應用-(一) HLOOKUP、IF、COUNTIF

更新於 2019/06/08發佈於 2019/06/08閱讀時間約 6 分鐘

首先我要介紹的這個案例式我前天剛拿到的，熱騰騰的端午節玩家儲值活動優惠贈送
(內容數字已做調整，與原先完全不同，此只為一起研究Excel)

1.當日儲值滿300，並消費900元寶，贈送188元寶
2.當日儲值滿500，並消費1200元寶，贈送388元寶
3.當日儲值滿1000，並消費4000元寶，贈送588元寶
4.當日儲值滿2000，並消費8000元寶，贈送888元寶
5.當日儲值滿5000，並消費16000元寶，贈送1888元寶
6.當日儲值滿10000，並消費30000元寶，贈送2888元寶
7.當日儲值滿20000，並消費50000元寶，贈送3888元寶
8.當日儲值滿50000，並消費100000元寶，贈送5888元寶

光看這些文字真的讓人覺得很疲憊，雖然他們的排列還是有一定規律在，但當派送了10筆20筆30筆......100筆的優惠時，真的讓人很頭痛，甚至是看錯行、派發錯誤獎勵
另外，若是玩家達成了第一個儲值的條件，但第二個於遊戲內消費的金額條件未達到，那麼他便會降一階拿到獎勵，或是拿不到。

這個檔案我用到的函數只有3個『HLOOKUP、IF、COUNTIF』

首先，最一開始我會先於A1將上列資訊表格化

將資訊表格化

接著於表格下方建立需要填入項目的表頭，我個人習慣是會將表頭用一個深色底白色字，這樣在看表格時會更加得清楚。
並將一開始建立的表格，移到與下方表格相同的相對位置。
(這皆為個人習慣，各位可以不用這樣用)

建立所要使用的表格表頭

接著我們就要開始進入正題了! 要來寫公式啦!

先來直接給大家看一下我的完成公式，公式是放在E11
=HLOOKUP($E$2,$E$2:$E$9,IF(COUNTIF($D$2:$D$9,"<="&D:D)>COUNTIF($C$2:$C$9,"<="&C:C),COUNTIF($C$2:$C$9,"<="&C:C),COUNTIF($D$2:$D$9,"<="&D:D)),0)

公式看起來好像沒有很複雜對吧~但是他卻消耗了我超級多的時間......
那麼，我們開始來拆解公式吧!

HLOOKUP ( ___1___ , ___2___ , ___3___ , ___4___ ) 這是HLOOKUP的基本構成
首先說到HLOOKUP就會說到VLOOKUP
而HLOOKUP與VLOOKUP的差異性就在於直的與橫的
HLOOKUP為要向下尋找你所要的目標
VLOOKUP為要向右尋找你所要的目標

那麼我們來說說這1234格分別是什麼吧：
第1格是你要找什麼東西，而他一定會在「第2格」的範圍裡最上方那一列當中
第2格是你所需要的資訊來源是在哪一個範圍當中
第3格是當你找到目標後，從最上往下數來第幾個
第4格很簡單，他就只是純粹問你你要找部分一樣的資訊，還是要找完全一樣
　#若你要找部份一樣，此格請輸入FALSE，或是1
　#若你要找完全一樣，此格請輸入TRUE，或是0
　#此格可省略，但省略便會自動判定為1

=HLOOKUP($E$2,$E$2:$E$9,___3___,0) 中間的IF先砍掉，等等在來說
第一格 $E$2 ，其實我只是當他是裝飾品，因為他是必須要填寫的欄位，所以我就直接用了我想要的範圍的第一格
第二格 $E$2:$E$9 這是我最終想要找到的東西，我希望他能為我找出我需要派發多少給玩家，而因為表單我們會一直接下去往下填，但是表格是不會動的，因此還需要用「$」將範圍固定住
第四格 0 這是我的個人習慣，因我一律都是找一模一樣的，所以我固定輸入0
IF ( ___1___ , ___2___ , ___3___ ) 這是IF的基本構成
IF的適用範圍相當的大，很多地方都可以用，是一個超級隨處可見的傢伙
一樣我們也來說說他的用法吧!
第1格為你需要判斷什麼
第2格為若判斷為「是」，將會呈現什麼
第3格為若判斷為「否」，將會呈現什麼
COUNTIF ( ___1___ , ___2___ ) 這是COUNTIF的基本構成
COUNTIF是COUNT的衍伸，COUNT為計算指定範圍內的數字有幾個
而COUNTIF多加了IF便是將他多出了條件化的功能，讓你可以一次框選大範圍並將不需要的不要計算近來

一樣來看一下他的用法
第1格為你要從哪一個範圍中找出符合條件的數量
第2格為你的條件是什麼

接著我們一一來看公式裡出現的每一個COUNTIF

COUNTIF($D$2:$D$9,"<="&D:D)
這是我們的公式中第一個出現的COUNTIF，他的意思為計算【在D2:D9中大於等於D欄的有幾格】
(D欄為玩家於遊戲內消費的遊戲幣金額)

COUNTIF($C$2:$C$9,"<="&C:C)
這意思為計算【在C2:C9中大於等於C欄的有幾格】
(C欄為玩家的儲值金額)

最後我們來結合HLOOKUP和IF，看一下整段的公式
=HLOOKUP($E$2,$E$2:$E$9,IF(COUNTIF($D$2:$D$9,"<="&D:D)>COUNTIF($C$2:$C$9,"<="&C:C),COUNTIF($C$2:$C$9,"<="&C:C),COUNTIF($D$2:$D$9,"<="&D:D)),0)
這意思為：
假設我計算出玩家儲值金額已達3級距，但他於遊戲內的消費金額僅達2級距，那麼我就派發2級距的獎金

表單完成

第一次寫文章，以上說明可能還不是很好，如果有任何的問題，歡迎留言給我讓我們一起討論吧!

1會員

3內容數

留言0

查看全部

發表第一個留言支持創作者！