【Excel 進階公式運用

接續上一堂課的Excel基本操作課程,這禮拜Willy要繼續為大家帶來一些在實務上常見的Excel進階技巧,幫助大家在Excel的操作上更加進化!


  • 經過這堂課你將會學到

  • Index X Match組合技

  • If和Ifna的邏輯

  • Filter的使用

  • Arrayformula的操作

  • Excel邏輯運算



Index X Match組合技


Index

在一個矩陣當中,如果你已經知道你想要回傳的數值的位置,這時候便可以使用Index()函數進行操作,使用時你會需要告訴Excel


=Index(資料範圍, 列, 欄)


舉例來說

Case1:請回傳蛋餅


Case2:請回傳4/29的早餐金額


Match

當你在一列或一行的資料當中,希望可以知道某一個資料的在該列或該行的位置時,就可以透過Match()來達成。


=Match(搜尋對象, 資料範圍, 選擇精確或是模糊查找)


Case1:請找出87分是排在第幾位

因此我們可以發現,透過Match回傳位置以及Index知道位置後會回傳資料的特性,我們可以將兩個函數合併使用,組合出一個可以回傳資料的公式。


Case1:請製作出可以選定日期和餐別後,回傳吃了什麼的資料查找表


首先我們製作日期的下拉選單:


在日期格按右鍵選取資料驗證


選取日期範圍


同樣的方法我們可以完成餐別的下拉選單。


接著我們來處理要回傳的欄位的公式:


透過Match去比對下拉選單中選取的日期位在第幾列,選取的餐別在第幾欄,之後便可以透過Index回傳出該餐是吃了什麼。


我們不難看出來,其實Index X Match的組合技使用出來的效果和上周所提到的Vlookup十分相似,但Vlookup在使用上有一些先天上的限制

  • 要回傳的資訊一定要在查找值的右邊,因此當原始資料沒辦法符合這個條件而還是要使用Vlookup時,你可能要花額外時間整理資料

  • 當你需要比對兩項條件時,如範例中你需要比對”日期”和”餐別”,此時Vlookup便無法達成你的要求

  • 使用Vlookup時,你需要自己去手動計算回傳值的位置


因此當你遇到上述狀況時,就可以使用Index X Match的組合技囉!



If和Ifna的邏輯


在Excel中,我們可以使用If和Ifna來讓Excel執行程式完成簡單的指令。


=If(條件, 條件成立的指令, 條件不成立的指令)

=Ifna(條件, 如果條件為na時的指令)


Case1:在選定日期後,回傳出指定日期的餐費,未選定日期則顯示”未選定日期”


Case2:在選定日期和餐別後,回傳該餐花費,若未選定餐別則為傳整天的餐費,未選定日期則顯示”未選定日期”



Filter的使用

當你想要篩選資料時,就可以使用filter函數,設定好要的條件就可以輕鬆地得到結果囉!


=Filter(你要回傳的資料範圍, 條件1, 條件2…..)


Case1:找出英文和物理都及格的學生


Case2:找出英文、物理和化學都及格的學生



Arrayformula的操作

當你在複製公式時,時常是使用欄位右下角點兩下快速複製公式到整欄


但使用這種方法時,我們時常會遇到一個問題,就是當我們插入新的一欄在資料表當中時,新插入的欄位並無法被套用到公式


這時候在Google sheet中,我們就可以使用Arrayformula來解決我們遭遇到的問題。Arrayformul會自動將公式套到你所指定的範圍,不用擔心資料插入後沒套用到公式,將一整行需要展開的公式簡化成一個儲存格。


同時在Arrayformula中,除非是刪除到填寫公式的欄位,我們大部分情況都可以避免資料誤刪的問題



Excel邏輯運算

在Excel中,我們也可以運用邏輯運算來套用到我們的函數當中。


True=1,在Excel中,如果你的邏輯運算成立,則Excel會回傳1

False=0,在Excel中,如果你的邏輯運算不成立,則Excel會回傳0


同時我們也會需要用到像前面介紹的If()、Ifna(),以及And()、Or()、Not()等邏輯運算子來撰寫我們的式子。


=And(條件1, 條件2, ...)

=Or(條件1, 條件2, ...)

=Not(條件)

*不使用Arrayformula的原因是因為And函數只能在單行列中運行

*Regexmatch非本堂教學內容,有興趣同學可以參考REGEXMATCH - Google 文件編輯器說明



只要把上次和這次的課程內容熟記,絕大多數的商業數據處理就能迎刃而解囉!熟記之後更重要的是大家勤加練習,讓這些式子用起來變成跟呼吸一樣自然,你就朝著Excel大師更進一步了!下一堂課Willy將帶著我們複習內容,並且帶大家實際演練一次喔,我們下次見!