[Excel]如何逐列搜尋,並關聯對應值

身為開發者,不可能什麼事都自己寫程式解決,當有合適工具時,評估後確定適用,能節省不少時間的話,產出才是最重要的。筆者最近在工作上就遇到,需要將不同資料庫內的資料取出,交互比對分析後,產出結果。畢竟有時程壓力,殺雞焉用牛刀,趕緊查了一下Excel用法後,確定可以做到類似SQL Join的效果,就順利完成交卷了。

這裡我將遇到的案例改編一下,順便介紹用法,希望能幫助到需要的讀者。假設有三個班級的學生要領糖果,已經預訂好每一個學生要領的糖果類型,也有領取紀錄,需要將兩者關聯起來。找出以下資料

  • 該領取但未領取的人有哪些人
  • 不該領卻領的人有哪些人
  • 對應領取清冊,每一種糖果領的人數分布

預期結果如下圖對應的欄位,再利用標題篩選就可以快速找出需要的人

圖表來說,付費的Offic還是比較給力,不過Google的也算堪用,基本功能都有了

藍色框框是原本就有的資料,綠色則是經過設定對應出的資訊,主要使用 “VLOOKUP" 公式達成,Google Excel有簡單的說明,簡而言之就是搜尋區域內第一欄,找到相同的值後,對應同一列後面的欄位

更詳細的部分可以參考微軟Excel官網,還有影片講解,以我們的例子來講,領取清冊D2會設成

=VLOOKUP(A2,’實際領取名單’!$A$2:$C$10,2,FALSE)

如果顯示#N/A,表示找不到對應的值,這邊依序說明每個參數的意義

  1. 要搜尋的值,A2對應到 小明
  2. 搜尋的範圍,單引號+驚嘆號 ‘實際領取名單’! 是代表另一個工作表,後面接著$A$2:$C$10,代表A2到C10的範圍,$字號的意義,是固定不變化,因為在複製或延伸欄位時,Excel會自動幫你添加行數,但這樣範圍就會變化,對應不到原本的範圍了
  3. 找到值後,對應往右數的順序欄位,以本例來說,領取記錄找到小明在A7,第2欄就是B7
  4. False代表大約相同,這個參數官網和Google Excel說明不同,但如果設True完全相符時,會找不到對應的值

接著附上圖表的設定方式

Microsoft Office Excel 樞紐分析表
Google Excel圖表

範例文件皆有附在參考,希望能幫到被臨時指派任務的人~

參考

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Google photo

您的留言將使用 Google 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s

Create a website or blog at WordPress.com

向上 ↑

%d 位部落客按了讚: