【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函數只能在單行列中運行