EXCEL Tips: การดึงข้อมูลในโดยอ้างอิงจากหลายตัวแปร

Excel - Multi-lookup

 

เมื่อนานมาแล้ว ผมเขียนถึงฟังก์ชั่นสารพัดประโยชน์ที่มนุษย์เงินเดือนควรรู้จักใน Microsoft Excel คือ VLOOKUP() ที่เอาไว้ เพราะมันสามารถใช้ดึงข้อมูลจากชุดข้อมูลในตาราง Excel โดยอ้างอิงจากข้อมูลใน Cell (ภาษาไทยเรียก สดมภ์ แต่ผมว่ายิ่งเรียกยิ่งงง ฉะนั้นผมขอเรียก Cell นะ) เช่น คุณมีรายชื่อของเซลส์ แล้วก็มียอดขายของเขาในปีนึง เวลาจะอ้างอิงไปใช้ใน Sheet อื่นในไฟล์ หรือ ณ บริเวณอื่นของ Sheet ถ้าเรารู้จักฟังก์ชั่น VLOOKUP() มันจะช่วยให้สะดวกมากครับ เช่น หากเราอยากจะให้พิมพ์ชื่อเซลส์ไปใน Cell E2 แล้วแสดงยอดขายของเซลส์คนนั้นใน Cell F2 ถ้าใส่สูตร =VLOOK(E2,A2:C5, 3, FALSE) ลงไป เราก็จะได้ตามที่ต้องการ

 

Excel - VLOOKUP

 

แต่ถ้าวันดีคืนดีข้อมูลมันเกิดซับซ้อนมากกว่านั้นล่ะ (ซึ่งส่วนใหญ่มักจะเป็นแบบนั้น) เช่น ไม่ได้มีแค่ชื่อกับยอดขาย แต่มีเดือนและปีที่ขายด้วย แล้วเวลาเราต้องการค้นหาข้อมูล เราก็อยากจะแค่พิมพ์ชื่อเซลส์ลงไป ระบุเดือน และปีที่ต้องการ ก็จะได้ยอดขายออกมาเลยแบบในรูปด้านล่างนี่จะทำยังไง?!?

 

Excel - Multi-lookup Sample

 

วิธีนึงที่ทำได้คือ ต้องสร้าง Column ใหม่ แล้วทำการเอาข้อมูลใน Column B, C และ D มาต่อกัน จากนั้นก็เอาข้อมูลใน Cell J4, J5 และ J6 มาต่อกัน แล้วเทียบด้วย VLOOKUP() ตามปกติ แต่นั่นมันวุ่นวายกับการสร้าง Column และ Row เพิ่ม ซึ่งอาจทำให้เสียโครงสร้างของตารางไปได้ … ฉะนั้น วิธีที่ดีที่สุดคือ การใช้ Array Formula ครับ โดยเอาฟังก์ชั่น INDEX() และ MATCH() มาใช้คู่กัน เพื่อทำหน้าที่แทน VLOOKUP() ที่ไม่สามารถรับมือกับสถานการณ์นี้ได้

 

ฟังก์ชั่น INDEX()

จะให้ค่าคืนมาเป็นข้อมูลในเซลล์ใน Array โดยเราระบุเป็นตำแหน่ง (Row, Column) ครับ เช่น อย่างในกรณีรูปด้านล่าง ตรง Cell A4 ผมใส่สูตรว่า =INDEX(A1:D3, 2, 3) หมายความว่า ให้แสดงข้อมูลของแถวที่ 2 คอลัมน์ที่ 3 ของ Array ตั้งแต่ Cell A1 ถึง D3 ซึ่งผลที่ได้ก็คือ 5 นั่นเอง (แถวที่ 2 คอลัมน์ที่ 3 ในที่นี้ก็คือ Cell C2 ครับ)

 

Excen - Index

 

ฟังก์ชั่น MATCH()

ฟังก์ชั่น MATCH() นี่เอาไว้ใช้หาตำแหน่งแถวที่ของข้อมูล โดยระบุไปว่าจะค้นหาอะไร จากช่วงของ Array ที่ต้องการ … เชน ในรูปตัวอย่างด้านล่าง ผมใส่สูตรไปว่า =MATCH(40, A1:A9, 0) หมายความว่า ผมต้องการหาข้อมูลที่เท่ากับ 40 จาก Array ที่ A1 ถึง A9 (การระบุ Array ต้องเป็นแนวตั้ง หรือเป็น Column เท่านั้น) ฉะนั้นค่าที่ได้กลับมาคือ 4 เพราะ ตัวเลข 40 อยุ่แถวที่ 4 ของ Array A1 ถึง A9 นั่นเอง

 

Excel -MATCH

 

และเมื่อเอา INDEX() กับ MATCH() มารวมร่างกัน

เราก็จะสามารถใช้ MATCH() ในการหาว่าข้อมูลที่ต้องการอยู่ทีแถวที่เท่าไหร่ เพื่อให้ INDEX ไปดึงข้อมูลอีกชุด ตามจำนวนแถวที่ระบุเอาไว้ได้ … ซึ่งหากใช้งานแบบมิติเดียว เช่น ดูว่าค่าใน Column B เป็นเท่าไหร่ เมื่อค่าใน Column A เป็นตัว “C” มันก็จะคล้ายๆ กับการใช้ VLOOKUP() เลยแบบนี้

 

Excel - VLOOKUP vs INDEX and MATCH

แต่ถ้าจะเอามาใช้ทำแบบนี้ ใช้ VLOOKUP() ไปเลย ง่ายกว่าเยอะครับ

 

เราใช้ INDEX() คู่กับ MATCH ในแบบ Array Formula เพื่อดึงข้อมูลแบบอ้างอิงหลายตัวแปร

กลับมาที่โจทย์มหาหินของเรา … เรามีชื่อเซลส์ เรามีเดือน เรามีปี ที่เซลส์แต่ละคนทำยอดขาย แล้วเราก็มียอดขายของแต่ละเดือนแต่ละปี เก็บไว้ในแบบ 1 แถวหมายถึง ยอดขาย ณ เดือน & ปี ของเซลส์คนนั้น ถ้าเราอยากให้แสดงข้อมูลยอดขายออกมา โดยพิมพ์แค่ ชื่อ เดือน และปี แล้วให้ Excel ไปดึงข้อมูลมาเลยจะทำยังไ

 

Excel - Multi-lookup

 

ก็ใช้ MATCH() แบบ Array Formula ให้ดูข้อมูลจากหลายๆ Array พร้อมๆ กันสิครับ … สูตรอาจจะดูยุ่งยากขึ้น เพราะเราดูข้อมูลจากสองชุด เช่น ผมอยากดูว่าข้อมูลที่ Column A มีค่าเป็นตัว “C” และข้อมูล Column B มีค่าเป็นตัว “ฃ” เนี่ย มันอยู่แถวที่เท่าไหร่ ผมก็ใส่สูตรแบบนี้ =MATCH(“C”&”ฃ”, A1:A7&B1:B7, 0)

 

Excel - MATCH Array Formula

 

 

อธิบายสูตรง่ายๆ คือ

  • ให้ดูข้อมูลที่เป็น ตัว C และ ฃ (หมายถึง Cฃ)
  • โดยเทียบกับการเอาข้อมูลใน Array A1:A7 กับ Array B1:B7 มาเรียงติดกันทีละแถว เช่น A1B1 ก็คือ Aก, A2B2 ก็คือ Bข เป็นต้น
  • ถ้าแถวไหนมีค่าตรงตามที่ต้องการเป๊ะๆ ให้บอกมาว่าอยู่แถวที่เท่าไหร่

แต่ถ้าสังเกตดีๆ สูตรของผมจริงๆ มันเป็น {=MATCH(“C”&”ฃ”, A1:A7&B1:B7, 0)} ครับ แล้ว { กะ } นี่มันมายังไง … ไม่ใช่แค่พิมพ์เข้าไปเฉยๆ นะครับ อันนี้เป็นสัญลักษณ์ที่จะบอกให้รู้ว่า สูตรที่อยู่ข้างในเครื่องหมายปีกกานี้ เป็น Array Formula ครับ … เมื่อเราพิมพ์สูตร =MATCH(“C”&”ฃ”, A1:A7&B1:B7, 0) เสร็จ แทนที่จะกด Enter เฉยๆ เพื่อบอกว่าพิมพ์เสร็จแล้ว เราต้องกด Ctrl + Shift + Enter แทน เพื่อบอกให้ Excel รู้ว่า สูตรที่เราพิมพ์ไปนี้ เป็น Array Formula ครับ ไม่งั้นผลลัพธ์ของสูตรที่ได้จะเป็น #VALUE! นะครับ

ฉะนั้น กับสถานการณ์ที่ผมสมมติมา เมื่อจะนำมาเขียนเป็นสูตร มันก็จะออกมาแบบนี้ครับ

 

{=INDEX(E5:E40,MATCH(J4&J5&J6,B5:B40&C5:C40&D5:D40,0))}

 

หรือก็คือพิจารณาจากข้อมูลใน Cell J4, J5 และ J6 เอามาเรียงต่อกันแล้วเอาไปเทียบกับข้อมูลที่เอา Array B5:B40 (ข้อมูลชื่อ), C5:C40 (ข้อมูลเดือน) และ D5:D40 (ข้อมูลปี) มาเรียงต่อกัน อันไหนตรงก็บอกมาว่าอยู่แถวที่เท่าไหร่ แล้วไปดึงข้อมูลยอดขายมาจาก Array E5:E40 นั่นเอง

เช่น ผมพิมพ์ไปว่าชือคือ James เดือนคือ Apr และปีคือ 2009 ฉะนั้น J4&J5&J6 ก็จะมีค่าเป็น “JamesApr2009” ครับ ฉะนั้นใน Array B5:B40, C5:C40, D5:D40 เมื่อเอามาต่อกันแล้ว อันไหนมีค่าเท่านี้ ก็ให้ตอบมาว่าอยู่แถวที่เท่าไหร่ ซึ่งเมื่อนับแล้วก็อยู่แถวที่ 22 ครับ ฟังก์ชั่น INDEX() ก็จะไปดู Array E5:E40 ว่า แถวที่ 22 นั้นคืออะไร ซึ่งก็คือ Cell E26 มีค่าเท่ากับ 432,396.00 นั่นเอง

 

ประโยชน์ของการใช้ Array Formula กับฟังก์ชั่น INDEX() และ MATCH()

บางคนอาจจะยังนึกสถานการณ์ที่จะต้องใช้สูตรประเภทนี้ไม่ออก ขอบอกว่าส่วนใหญ่มันจะมาในรูปแบบสถานการณ์ที่เราต้องทำข้อมูลสรุปจากข้อมูลดิบให้มาเป็นข้อมูลแนว Dashboard ให้ผู้บริหารสามารถเข้าถึงได้สะดวกๆ ในกรณีที่การทำ Pivot Table นั้นยังให้ข้อมูลในรูปแบบที่ต้องการไม้ได้เต็มที่ หรือ ต้องการเอาข้อมูลที่ดึงมานำเสนอนี้ไปใส่สูตรต่ออีกน่ะครับ

ของแบบนี้ต้องลองใช้ในหลายๆ สถานการณ์ เพราะบางทีแค่ VLOOKUP ก็พอแล้ว บางทีก็ใช้ Pivot Tablet สะดวกกว่า หรือบางที ก็ต้องใช้ Array Formula นี่แหละ … หลายๆ อย่างจริงๆ Excel สามารถทำได้นะ แต่เพราะใช้ฟังก์ชั่นระดับ Advanced ไม่ค่อยเป็น หลายๆ คนเลยนึกว่า Excel ไม่สามารถทำได้น่ะ

The following two tabs change content below.
บล็อกเกอร์สายรีวิวที่มีความสนใจในด้านเทคโนโลยี จิตวิทยา และทรัพยากรมนุษย์ และเนื่องจากได้ร่ำเรียนปริญญาโทภาควิชาจิตวิทยาอุตสาหกรรมและองค์การ (แต่ไม่จบเพราะมัวแต่เอาความรู้มาใช้จริง จนลืมทำวิทยานิพนธ์) จึงคิดว่าจะเป็นการดีที่จะนำความรู้ด้านทรัพยากรบุคคล และจิตวิทยาที่ได้ มาเผยแพร่เพื่อคนอื่นๆ ต่อ ... ปัจจุบัน เป็นวิทยากรรับเชิญในด้านต่างๆ อาทิ เทคโนโลยีคอมพิวเตอร์ อุปกรณ์พกพา โซเชียลมีเดีย ดิจิตอลมาเก็ตติ้ง และเป็นพาร์ทเนอร์กับกลุ่มบริษัท Adecco Thailand ในด้านเนื้อหากับโซเชียลมีเดีย

You may also like...

Leave a Reply

%d bloggers like this: