2022/08/28

製作下拉選單並使用INDEX+MATCH顯示對應資料

      , , ,      No comments   

如果有一天，妳被很臨時的交辦查詢數量很龐大的資料，還要列出對應的資訊，例如：某些客戶上次下單的時間、某些訂單的內容項目、交期等等。也許那本來不是妳負責的範圍，所以對它們還不是那麼熟識、了解，沒辦法很快的找到資料的位置，因此需要慢慢核對。

這時候，妳可以土法煉鋼的用眼睛找，進階一點可以使用Ctrl + F搜尋，但兩個方式都有個不方便的地方，就是如果下次換找另一個項目，就要重新再來一次同樣的流程，這很浪費時間以及考驗眼力呢！使用本篇介紹的下拉選單加上 INDEX以及MATCH的函數公式，就可以解決上述的問題，也能減少出錯的機率呢，快來試試吧。

若未輸入資料時，會顯示#N/A的錯誤代碼，可以參考：

    基本說明

    Microsoft Excel 與 Google Sheet下拉選單設定的位置有一點不同，但邏輯是一樣的，下面兩個都有附上手把手的操作步驟，開始前要確認一下有沒有看錯摟。
    而 INDEX 函數與 MATCH 函數在 Microsoft Excel 電腦版、網頁版和 Google Sheet 中皆可使用，且用法相同，此篇使用 Google Sheet 作為範例圖。為方便辨識，儲存格標示為  黃底  的是帶有函式資料的儲存格；儲存格標示為  綠底  的為可修改資料，且會影響公式結果的儲存格；標示為  淺灰底  的為黃底儲存格的公式文字。



    建立下拉選單 --- Micorsoft Excel

    1. 資料建立好後，選擇要顯示下拉選單的儲存格，選擇後會有綠色框線，範例選擇儲存格D2。

    2. 點選上方第一排灰底的資料，選擇資料工具中的資料驗證。


    3. 跳出資料驗證視窗，允許項目選擇清單，點選來源輸入格，直接拉取要做為下拉選單的資料，其他項目在此篇不需要修改，選擇確定。


    4. 完成。




    建立下拉選單 --- Google Sheet

    1. 資料建立好後，選擇要顯示下拉選單的儲存格，選擇後會有藍色框線，範例選擇儲存格D2。


    2. 選擇上排選單中的資料，找到並點選資料驗證。



    3. 跳出驗證資料視窗，看到第一個儲存格範圍，因為在前一個步驟已經選擇了，所以可以略過。第二個是條件，預設為範圍內的清單，在這篇不需要調整，可以直接點選右側「輸入範圍或公式」的格子，選擇後會出現藍色框。


    4. 直接拉取要做為下拉選單的資料，這時會跳出另一個選取資料範圍的視窗，選取完成後點選確定。


    5. 回到驗證資料視窗，如果下拉選單不讓人輸入其他內容，僅能選取，可以找到針對無效資料的選項，選擇「拒絕輸入內容」再儲存，如果不需要的話直接儲存就可以了。


    6. 完成。


    7. 如果有設定第5點的拒絕輸入內容，這時輸入其他資料會跳出警示視窗，就不能任意的更改資料，若沒有設定還是能夠輸入，僅會在儲存格右上角顯示紅色的警示三角型。

    函數INDEX

    傳回指定儲存格的值，或是傳回以列、欄偏移方式指定的儲存格內容。
    函數公式：INDEX( 選擇範圍 , 列[選用] , 欄[選用] )  

    使用方式：
    帶入要參照的儲存格，後方選用的不填，表示不偏移列與欄。
    例如：INDEX( A2 )  

    帶入要參照的儲存格範圍，並加入要偏移的列與欄數。
    例如：INDEX( A2:B10 , 2 , 2 ) (找出範圍內往下2列、往右2欄的值)



    函數MATCH

    搜尋與指定儲存格相同的項目，並傳回該項目在範圍中的相對位置。
    函數公式：MATCH( 尋找的值 , 尋找的範圍 , 搜尋方式[選用] )  

    使用方式：
    尋找的值可輸入儲存格、數字、文字、日期與函式等。
    例如：MATCH( A2 , 尋找的範圍 , 搜尋方式[選用] )
    　　　MATCH( 13579, 尋找的範圍 , 搜尋方式[選用] )
    　　　MATCH("蘋果", 尋找的範圍 , 搜尋方式[選用] )  
    　　　MATCH( "2022/7/7" , 尋找的範圍 , 搜尋方式[選用] )  
    　　　MATCH( date(2022,7,7) , 尋找的範圍 , 搜尋方式[選用] )  

    尋找的範圍需要為單一列或單欄。
    例如：MATCH( 尋找的值 , A1:A25  搜尋方式[選用] )  
    　　　MATCH( 尋找的值 , 8:8 , 搜尋方式[選用] )  

    搜尋方式若不填寫，預設值為1
    • 填寫數字1：資料須以遞增次序排列，會尋找大於或等於尋找值的最大值。
    • 填寫數字0：資料可以亂數排列，會尋找出完全相等的第一個值。
    • 填寫數字-1：資料須以遞減次序排列，會尋找大於或等於尋找值的值。
    簡單來說，就是以資料的順序排列去選擇要用那一個搜尋方式，建議填寫 0 結果會比較準確。



    函數實際操作

    此篇下拉選單、函數公式結果顯示如下(  綠底 、黃底  內容)：

    小提醒：這邊跳過下拉選單的步驟，還沒完成可以回上方看製作步驟噢。
      《 步驟 》
    1. 使用MATCH函數：
      在要顯示結果的儲存格內，輸入MATCH函數公式，範例中要查找的值為E3，查找範圍為B3至B11，搜尋方式選用 0 ，若以西瓜為例，得出列數為「5」。
      =MATCH(E3,B3:B11,0)  

    2. 合併INDEX+MATCH函數公式：
      繼續編輯儲存格，使用INDEX函數，參照儲存格範圍，為全部表格內容B3:C11，偏移的列數剛剛使用MATCH找出來了，公式為MATCH(E3,B3:B11,0)，最後加上要尋找的欄位，B欄為1、C欄為2，因此填入數字2。
      =INDEX(B3:C11, MATCH(E3,B3:B11,0), 2)  

    3. 完成~~~~~

    EXCEL小工具

    使用方式
    1. 填寫資料範圍，例如：A:B、A1:B10、B1:C5... ...
    2. 輸入下拉選單的儲存格位置，例如：A1、B2、D2... ...
    3. 輸入要尋找的範圍，須為包含下拉選單資料的範圍，例如：A:A、B1:B10... ...
    4. 輸入尋找的欄位數字，例如：尋找範圍A~C，A=1、B=2、C=3；尋找範圍B~D，B=1、C=2、D=3，以此類推
    5. 按下 Enter 按鈕
    6. 點擊輸出的函數公式就可以直接複製
    7. 將它貼在想要顯示的儲存格內即可
    篩選資料範圍
    下拉選單位置
    尋找範圍
    尋找欄位數



    Copied!

    0 Comments:

    張貼留言