Excel函式進階應用-(一) HLOOKUP、IF、COUNTIF
首先我要介紹的這個案例式我前天剛拿到的，熱騰騰的端午節玩家儲值活動優惠贈送
(內容數字已做調整，與原先完全不同，此只為一起研究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級距的獎金
第一次寫文章，以上說明可能還不是很好，如果有任何的問題，歡迎留言給我讓我們一起討論吧!
分享至
成為作者繼續創作的動力吧！
© 2024 vocus All rights reserved.