ดึงข้อมูลข้ามตาราง Google Sheet

การทำงานโดยทั่วไป จะมีข้อมูลที่อยู่หลายที่ แต่อาจจะมี key บางอย่างที่เชื่อมโยงกัน เพื่อให้ดึงข้อมูลจากอีกตารางนึง ไปแสดงผลที่อีกตารางนึงได้ นั่นคือสิ่งที่เราเรียกกันว่าการ Join แต่ใน Google Sheet / Excel นั้น function ที่ชื่อว่า join กลับถูกใช้ในการเชื่อมคำ เราจะต้องใช้วิธีอื่นในการจัดการข้อมูลตาราง

วิธีที่ปัจจุบันเราใช้กันจะมีทั้งหมด 3 วิธี ซึ่งจริงๆ แล้วอาจจะมีวิธีอื่นที่สามารถทำได้เช่นกัน แต่เราจะเสนอเฉพาะวิธีที่สะดวก พร้อมบอกข้อดี ข้อพิจารณา และแสดงตัวอย่างการนำไปใช้งานให้เห็นกันทีละวิธีเลย

ก่อนเริ่มกันเรามาดูตัวอย่างข้อมูลกันก่อน

ข้อมูลของเราจะมีทั้งหมด 2 ชุด

1.ข้อมูลพนักงาน ประกอบไปด้วย เลขพนักงาน ชื่อพนักงาน และเลขตำแหน่ง
2.ข้อมูลตำแหน่งพนักงาน ประกอบไปด้วย เลขตำแหน่ง ชื่อตำแหน่ง และชื่อย่อตำแหน่ง

สังเกตได้ว่าจะมีจุดร่วมกันคือ column ที่เป็น Position_ID ที่จะสามารถดึง ตำแหน่ง และชื่อย่อของตำแหน่งไปแสดงผลได้

เกณฑ์การประเมินของเราจะขึ้นอยู่กับเงื่อนไขดังต่อไปนี้

1. วิธีดึงค่าแบบทั่วไป
2. การจัดการข้อความ Error
3. วิธีดึงค่าแบบหลาย column

INDEX + MATCH (เก่า แต่ยังใช้ได้ดี มีความซับซ้อนสูง)

ต้องบอกเลยว่าตั้งแต่ลืมตามา ก็เจอสูตรนี้ตั้งแต่เรียนวิชาคอมพิวเตอร์แล้ว อย่างไรก็ตามถึงจะรู้จักมันเป็นสูตรแรก ๆ แต่ผมเชื่อว่าหลายคนก็คงไม่ได้โปรดปรานสักเท่าไรนัก ด้วยความซับซ้อนของสูตรที่ต้องใช้ 2 function ในการทำงาน ทั้ง INDEX และ MATCH

1. การใช้ Function match

MATCH(search_key , range , [search_type])

ฟังก์ชั่นจะเอา search_key ของเรา ไปหาใน range แล้วส่งตำแหน่งของค่าที่เจอมาให้ โดยให้ใส่ [search_type] = 0 เพราะเราต้องการข้อมูลที่เหมือนกับ search_key เป๊ะ ๆ

=match(D6,$B$14:$B$16,0)

2.การใช้ Function INDEX

INDEX(reference , [row] , [column])

ฟังก์ชั่นนี้จะนำตาราง reference ไปค้นหาด้วย row , column

=INDEX($B$14:$D$16,F6,2)

ถ้าเราใส่เป็น row = 1 , column = 2 ผลลัพธ์ก็จะเหมือนภาพด้านล่าง

เมื่อเรารู้แล้วว่า INDEX จะต้องทำงานหลังจากที่ MATCH ทำงานเสร็จแล้ว เราก็จะรวมสูตรได้ตามนี้ครับ

ทีนี้ถ้าเราอยากได้ข้อมูลทุก column เราก็แค่ลบ 2 ออก

แล้วถ้าเราใช้ Arrayformula กับสูตรนี้ล่ะ

สาเหตุที่มันใช้ไม่ได้ตรง ๆ ก็เป็นเพราะว่า โดยปกติแล้ว INDEX จะเป็น function ที่คืนค่าแบบหลายตัวได้ก็จริง แต่เราใช้เงื่อนไข MATCH ควบคู่กันไปด้วย จะทำให้ INDEX ที่ค้นเจอจะมีผลลัพธ์แค่ค่าแรก เพียงค่าเดียวเท่านั้น (แม้ว่าจะใส่ search_key ใน MATCH หลายตัวก็ตาม)

แต่ก็ใช่ว่าจะไม่มีวิธีแก้นะ Google Sheet มี Function Map ที่สามารถทำงานแบบวนซ้ำได้ และมีการใช้ LAMBDA ในการจัดการ INDEX +MATCH อีกที

หลักการทำงานของสูตรก็คือ มันจะสร้าง LAMBDA function ขึ้นมา 1 ตัว โดยที่จะรับค่ามาจาก MAP() ด้านบน ซึ่งก็คือ D6:D10 เพื่อใช้ในการวนซ้ำ function INDEX+MATCH ให้มีผลลัพธ์ออกมาหลาย ๆ ค่าแทนที่จะส่งกลับมาเพียงแค่ค่าแรกที่ตรงกันเท่านั้น

ถ้ามี ERROR แก้ได้ไหม ?

ใช่ครับการเขียนสูตรที่ดีควรมีการ Handle Error เอาไว้ด้วย ให้มันแสดงผลแบบนี้ตรงๆ คนที่เอาไปใช้งานต่อก็อาจจะกำหมัดได้ครับ

ถ้าอยากได้ Result หลาย column ทำได้ไหม ? คำตอบคือได้นะ แต่จะมีความวุ่นวายเพิ่มขึ้นมานิดนึง

=IFERROR(MAP(D6:D10, LAMBDA(d, 
  HSTACK(
    INDEX(C14:C16, MATCH(d, B14:B16, 0)),
    INDEX(D14:D16, MATCH(d, B14:B16, 0))
  )
)),"-")

สังเกตได้ว่าพอทำไปทำมาแล้วมันยุ่งยากมากเลย โดยเฉพาะอย่างยิ่งถ้าเราไม่เข้าใจสูตร เราอาจจะทำมันพังได้ แม้ว่าเราไม่รู้ตัวด้วยซ้ำ คะแนนความยาก ผมให้ 10/10 เลย สำหรับ INDEX+MATCH

VLOOKUP (ง่ายสุด แต่ก็มีข้อจำกัด)

จริง ๆ แล้ว ในบรรดาสูตรที่เอาไว้เชื่อมข้อมูล ผมชอบใช้ Vlookup มากกว่าสูตรอื่น ๆ เนื่องจากการจัดการตารางโดยทั่วไป เรามักจะดึงข้อมูลแถวกับแถวชนกันอยู่แล้ว แต่ก็มีข้อพิจารณาเพียงเรื่องเดียวสำหรับ Function นี้ ก็คือ column ของตารางอ้างอิงจะต้องอยู่ด้านซ้ายเสมอ ซึ่งเป็นข้อจำกัดที่ใหญ่มาก ๆ

การเลือกใช้ Vlookup แปลว่าเราต้องมีการจัดระเบียบตารางให้เรียบร้อยตั้งแต่แรกอยู่แล้ว ซึ่งจากงานที่ผมทำมันมีขั้นตอนที่ต้อง Clean ,Transform Data อยู่แล้ว ผมเลยมองว่าไม่ใช่เรื่องใหญ่อะไร เมื่อเทียบกับสูตรที่เขียนง่ายมาก ๆ

ก่อนอื่นผมขออธิบายสูตร Vlookup ก่อน

=VLOOKUP(search_key , range , index , [is_sorted])

ฟังก์ชั่นนี้ จะนำ search_key ของเราไปค้นหาใน range ที่เราเลือกก่อนจะกำหนดว่าจะให้คืนค่าคอลัมน์ใดบ้าง และความพิเศษของมันคือ สามารถคือค่าหลาย column ได้โดยที่เลือกได้ว่าจะให้ column ไหนมาก่อนมาหลังด้วยครับ

ข้อพิจารณา

แต่อย่างที่เรารู้กันครับ search_key ที่ vlookup เอาไปค้นหาใน range จะตรวจสอบเฉพาะ column แรกของ range เท่านั้น ทำให้ตัว index ที่เราจะส่งค่า column มามันจะไม่สามารถส่งค่าติดลบได้ครับ นั่นเลยเป็นสาเหตุที่เราไม่สามารถจะดึงข้อมูลที่อยู่ด้านซ้ายของ column ออกมาได้ครับ

การเลือกหลาย column

สังเกตได้ว่าวิธีเขียน Vlookup ง่ายมาก ๆ เดี๋ยวเราจะเขียนเพื่อดึง column อื่น ในสูตรบรรทัดเดียว ใช่ครับ ทำได้ง่ายมาก ๆและสลับคอลัมน์ได้ด้วย ผมจะยกตัวอย่างให้มันขึ้นต้นด้วย Pos, Position_Name ตามลำดับ

สิ่งที่ผมทำเพิ่มก็แค่ แทนที่จะหยิบคอลัมน์ 2 มาเพียงค่าเดียว ผมหยิบ {3,2} ติดมือมาด้วย แล้วใช้สูตรเป็น ArrayFormula ด้วย ซึ่ง Arrayformula สำคัญมานะครับ ถ้าเปลี่ยนแค่คอลัมน์แต่ไม่ใช่ ArrayFormula สูตรจะไม่ติดครับ

ที่เหลือก็แค่เพิ่ม IFERROR เพื่อกันข้อมูลค้นหาแล้วไม่ตรงกับเงื่อนไขเท่านั้น เป็นอันจบสิ้น สูตรนี้น่าจะสั้นที่สุดเลย

XLOOKUP (Function ใหม่ มาแรง และยืดหยุ่น)

ผมสามารถพูดได้เลยว่า xlookup เป็น function ที่สร้างมาเพื่อกำจัดจุดอ่อนของ vlookup อย่างแท้จริง แต่ก็แลกมาด้วยความสามารถบางอย่างที่ต้องสูญเสียไปอย่างการดึงหลายคอลัมน์แบบง่ายๆ ซึ่งจริงๆ แล้วอาจจะมีคนส่วนน้อยที่ดึงหลาย ๆ คอลัมน์

ขออธิบายส่วน xlookup กันสักนิดนึง

XLOOKUP(search_key, lookup_range , result_range ,
[missing_value] , [match_mode], [search_mode])

สูตรนี้คือจะเอา search_key ไปค้นหาใน lookup_range แล้วจะคืนค่าผลลัพธ์ที่ตรงกันใน result_range โดย function นี้จะคำนวณจาก row, column ที่ตำแหน่งตรงกัน แปลว่า lookup_range , result_range จะต้องมีขนาดเท่ากัน และอยู่ในทิศทางเดียวกันเท่านั้น (คล้ายกับการใช้ Filter )

สูตรที่ผมเขียนเบื้องต้นจะใส่เฉพาะ Require Argument เท่านั้น คือจะนำ search_key ไปค้นหาใน Position_ID แล้วส่ง Position_Name กลับมาเป็นคำตอบ ทีนี้เราจะมาจัดการ Error กันก่อน ซึ่ง xlookup มีสิ่งที่เรียกว่า [missing_value] อยู่แล้ว ให้เราจัดการ Error ในส่วนนั้นแทนที่จะต้องเขียน IFERROR ครอบฟังก์ชั่นทั้งหมด แล้วเขียน ArrayFormula ครอบไปเลยครับ

ก็ดูเรียบร้อยดี เหมือนจะไม่มีปัญหานะ แต่ xlooup จริงๆ แล้วไม่ได้ support ArrayFormula เต็มที่หรอกนะครับ เพราะผลลัพธ์ของมันจะไม่สามารถส่งมากกว่า 1 คอลัมน์ออกมาได้

วิธีการแก้ไขให้ทำแบบนี้ครับ

ผมใช้วิธีการรวมผลลัพธ์กัน โดยให้เครื่องหมาย , เป็น delimeter คั่นแต่ละข้อความเพื่อเป็นตัวอย่าง จริงๆ ควรเช็คด้วยว่าข้อความเรามี delimeter ลักษณะนี้อยู่หรือเปล่า แล้วทำการให้ xlookup ส่งผลลัพธ์มาก่อน แล้วเรามากระจายคอลัมน์เอง ด้วย Function Split

มาถึงตรงนี้แล้วคุณก็คงจะพอเข้าใจผมบ้าง ไม่มากก็น้อย ว่าทำไมผมถึงชอบใช้ Vlookup นักหนา ส่วนนึงเพราะผมคิดว่าการขยับคอลัมน์ใน Google Sheet รวมถึง Excel อย่างน้อยการง่ายกว่าการต้องมาเขียนสูตรยาว ๆ แล้วจัดการปัญหาที่ไม่รู้จบ อีกส่วนนึงก็อาจจะเพราะผมเป็นคนขี้เกียจที่ชอบทำงานครั้งเดียวได้ผลลัพธ์หลายอัน (คอลัมน์) ถ้าคุณชอบ Vlookup ตอนนี้คุณเป็นหัวแถวแล้วครับ (อย่างน้อยก็ทำงานเสร็จเร็ว)

สามารถลองศึกษาเพิ่มเติมได้ที่นี่ หรือจะ Make a Copy ไปใช้ก็ได้ครับ

Speardsheet Join

Google Docs