使用SUM函數或SUMPRODUCT函數或者MATCH和INDEX的組合
再接再厲,SUM函數配合EXACT函數也可以達到前兩篇文章所討論的範例,進行大小寫必須相符的查詢比對喔!例如:使用相同的範例,只要輸入以下的函數:
=SUM(EXACT($H$2,品號)*庫存)
以陣列的角度完成,將查詢比對所產生的邏輯值陣列(True 或 False)與庫存範圍進行相乘,若有查找到資料時,邏輯值陣列僅會有一個True,而與相對應的庫存範圍值進行相乘後,所產生的乘積陣列再透過SUM函數加總,也就是所尋獲的庫存值了!
也記得,輸入完此公式,要按下Ctrl+Shift+Enter按鍵喔!
這種陣列相乘再累加的原理,當然也適用於人如其名的SUMPRODUCT函數,因此,若是寫成:
=SUMPRODUCT(EXACT($H$2,品號)*庫存)
也可以查詢到指定品號的庫存量:
不過,可以使用SUM函數或是SUMPRODUCT函數,乃是因為要查找並傳回的是數值性資料的[庫存量],若是輸入指定的品號後,想要查找並傳回該品號的[生產線]、[負責人]或[分類]等字串性的資料,這時候可就是INDEX與MATCH函數大顯身手的時候了!例如:
可查找到指定品號的負責人。
可查找到指定品號的分類。
可查找到指定品號的生產線。
再加個工,讓這個實作範例更視覺化一點。選取儲存格範圍A3:E14,透過,設定格式化條件的操作,進行醒目資料的標示。
設定使用公式來決定要格式化哪些儲存格,公式為:
=EXACT($C3,$H$2)
設定為淺黃色的底色與藍色粗體字格式:
如此,只要找查找到資料後,整列的資料便會具有醒目提示的效果,例如:查找到「nr-019」後,整列「nr-019」資料便呈現為黃底藍字:
例如:查找到「bc-234」後,整列「bc-234」資料便呈現為黃底藍字:
這一系列的學習,相信您對於查找資料的各種技巧應該有更進一步的體驗了吧!