XLookup เป็นฟังก์ชันใหม่ใน Excel ที่ทำหน้าที่คล้าย VLookup รวมกับ HLookup ถ้าใครยังใช้ Excel เวอร์ชันเก่า ๆ อยู่จะยังใช้ฟังก์ชันนี้ไม่ได้ โดยฟังก์ชันนี้ได้ทำลายข้อจำกัดของ VLookup หลายประการ
สมัยที่ใช้ Vlookup จะมีปัญหาว่า เราหาข้อมูลจากบนลงล่างเท่านั้น ถ้าหาจากซ้ายไปขวาต้องใช้ HLookup และค่าที่ใช้เป็นตัว lookup หรือ ค้นหา จะต้องอยู่ทางซ้ายมือเท่านั้น ทำให้มีการใช้ index & match มาช่วยแก้ปัญหา ซึ่งบางคนก็มองว่า index & match นี่มันซ้อนฟังก์ชันซับซ้อนทำให้สับสนเกินไป ซึ่งทางไมโครซอฟต์คงได้ยินคำบ่นเรื่องนี้มากก็เลยทำฟังก์ชันใหม่มาช่วยแก้ปัญหา ลองอ่านรายละเอียดวิธีการใช้จากเว็บไซต์ไมโครซอฟต์ดูได้ จะเห็นว่าการใช้งานสะดวกมาก
เนื้อหาโดยรวม :)
XLookup ฉบับ Basic
ถ้าจะให้สรุปข้อดีเทียบกับ Vlookup ในตอนนี้ก็คือ
- หาข้อมูลจากขวาไปซ้ายได้ ไม่จำกัดแค่จากซ้ายไปขวาอย่าง Vlookup
- กำหนดให้แสดงข้อมูลจากด้านล่างก่อน
- แสดงผลลัพธ์ได้มากกว่า 1 ข้อมูล
- หาได้ทั้งแนวตั้งแนวนอน
- แสดงผลลัพธ์ได้ทั้งแนวตั้งและแนวนอน
- ค่าตั้งต้นคือ “หาค่าที่ตรงกัน” ในขณะที่ Vlookup ใช้ “หาค่าใกล้เคียง” เป็นค่าตั้งต้น
โครงสร้างฟังก์ชันก็ง่ายมาก คือ
(ค่าที่ต้องการค้นหา, ช่วงที่ให้ดูค่าที่ค้นหา, ช่วงของค่าที่ต้องการให้แสดงผล, ถ้าไม่พบ, ประเภทการจับคู่, แบบการค้นหา)
เท่านี้เอง
ค่าที่ต้องการค้นหา จะสัมพันธ์กับ ช่วงที่ให้ดูค่าที่ค้นหา ช่วงของค่าที่ต้องการให้แสดงผล คือสิ่งที่ต้องการให้แสดงผล อันนี้เหมือนกับ VLookup HLookup ที่คุ้นเคยกันดีอยู่แล้ว
ยกตัวอย่างวิธีการใช้ง่าย ๆ สมมติว่าเราจะเอามาใช้แทน VLookup ในการหาว่า ลำดับที่บันทึกนี้ มีราคาซื้อขายกันเท่าไหร่ จะทำอย่างไร
โดยจะใส่ค่าลำดับที่ต้องการค้นหาลงใน Cell H2 โดยให้ค้นหาลำดับที่บันทึก ซึ่งอยู่ในคอลัมน์ A (A2 – A57) แสดงผลลัพธ์ที่ต้องการคือ ราคาซื้อขาย อยู่ในคอลัมน์ E (E2 – E57)
ก็จะได้สูตรออกมาแบบนี้
=XLOOKUP(H2,A2:A57,E2:E57)
อันนี้จะมีค่าปริยายว่า เราไม่ได้กำหนดว่าถ้าขึ้นค่า error จะให้แสดงผลว่าอะไร แสดงเฉพาะค่าที่ตรงกันเท่านั้น และ การค้นหาจะค้นหาจากบนลงล่าง
เอาใหม่ ลองดูสิ่งที่เคยทำไม่ได้ใน VLookup เช่นการค้นหาจากขวามาซ้าย ลองดูว่าถ้าเราจะหาว่า ราคาซื้อขายเท่านี้ เป็นของห้องใด ซึ่งเดิม ถ้าจะใช้ VLookup เราต้องย้ายคอลัมน์ E (ราคาซื้อขาย) ให้มาอยู่ทางด้านซ้ายของคอลัมน์ C (เลขที่ห้อง) หรือไม่ก็ต้องเปลี่ยนไปใช้ Index & Match แต่ตอนนี้ทำได้ง่าย ๆ สบายมาก
ก็จะได้สูตรออกมาแบบนี้
=XLOOKUP(H2,E2:E57,C2:C57)
ความสะดวกแบบนี้ทำให้ทำงานง่ายกว่าตอนใช้ VLookup มาก และไม่ต้องใช้ Index & Match มาช่วยอย่างเมื่อก่อน
สิ่งใหม่ที่ต่างจาก lookup เดิม
เมื่อก่อน สมัยใช้ VLookup HLookup ถ้าไม่พบจะขึ้น #N/A ซึ่งถ้าต้องการให้แสดงผลว่าอะไร เป็นตัวเลือกเพิ่มสำหรับใส่เฉพาะเจาะจงว่าถ้าหาไม่เจอจะให้แสดงผลเป็นอะไรหรือเปล่า ซึ่งเดิมจะใช้ IFERROR ครอบอีกครั้งว่าจะให้แสดงผลอะไรในกรณีที่มีการผิดพลาด (หาค่าที่จะจับกันไม่ได้) เช่น “ไม่พบ” “ไม่มี” “ยู้ฮู” ฯลฯ เราแค่ใส่ คำนั้นตามท้ายไป อย่างเช่น
=XLOOKUP(H2,A2:A57,E2:E57,”ไม่พบจ้ะ”)
เลือกให้แสดงผลตรงกันหรือใกล้เคียงกัน
ประเภทการจับคู่จะมีรายละเอียดปลีกย่อยต่างไป คือถ้าเป็น 0 (หรือไม่ใส่ค่า) จะแสดงผลเฉพาะที่ตรงกันเท่านั้น ถ้าเป็น -1 จะแสดงผลที่ตรงกันหรือถ้าไม่มีตรงกัน จะแสดงผลใกล้เคียงที่ต่ำกว่า ถ้าเป็น 1 จะแสดงผลที่ตรงกันหรือถ้าไม่มีตรงกัน จะแสดงผลใกล้เคียงที่สูงกว่า ถ้าเป็น 2 จะเป็นอย่างไรก็ได้
อย่างเช่น จากตัวอย่างที่แล้ว ถ้าไม่พบก็จะแสดงคำว่า “ไม่พบจ้ะ” แต่คราวนี้ถ้าเราเปลี่ยนสูตร โดยกำกับรูปแบบการค้นหาเพิ่มเข้าไป เช่น -1 นั่นคือ ถ้าไม่พบข้อมูลที่ตรงกัน ให้แสดงข้อมูลที่ใกล้เคียงที่ต่ำกว่า
=XLOOKUP(H2,A2:A57,E2:E57,"ไม่พบจ้ะ",-1)
ถ้ามีตัวที่ตรงกันกับที่ค้นหา ก็จะแสดงผลที่ค้นหา แต่ถ้าไม่มีตรงกันมันจะไม่แสดง “ไม่พบจ้ะ” จะแสดงค่าที่น้อยกว่าขึ้นมาแทน ในตัวอย่างนี้ มันหาค่าลำดับ 24 ไม่พบ ก็จะแสดงค่าของลำดับ 23 คือตัวที่ใกล้เคียงที่สุดแต่มีค่าน้อยกว่า มาแทน
ค้นจากบนลงล่าง หรือจากล่างขึ้นบน
และที่น่าสนใจอีกแบบคือ เลือกว่าจะค้นหาจาก บนลงล่าง (ค่ามาตรฐาน) หรือจะย้อนจากล่างขึ้นบนก็ได้ อันนี้ก็เป็นตัวเลือกที่น่าสนใจ คือ 1 เลือกจากซ้ายไปขวา หรือ บนลงล่าง -1 จะเป็นจากขวาไปซ้าย หรือจากล่างขึ้นบน 2 binary search ascending -2 = binary search descending การใช้งานไบนารีจะเร็วกว่าจริง แต่ข้อมูลจะต้องจัดการให้ดีก่อน คือต้องเรียงลำดับให้ดีก่อน
สมมติเช่น เราจะค้นหาว่าห้องขนาด 44.93 ตารางเมตร มีราคาขายเท่าไหร่ ก็จะใช้สูตร
=XLOOKUP(H2,D2:D57,E2:E57,"ไม่พบจ้ะ")
ในที่นี้คือ ใส่เนื้อที่ที่ต้องการค้นหาที่ H2 ข้อมูลเนื้อที่อยู่ที่คอลัมน์ D (D2:D57) และราคาซื้อขายอยู่ที่คอลัมน์ E (E2:E57)
จะได้คำตอบว่า 4,225,470 ซึ่งอยู่ใน Cell E6 ซึ่งเป็น Cell แรกที่มีค่าเนื้อที่ 44.93 ตารางเมตร แต่ถ้าเราสั่งการค้นหา ให้ค้นจากข้างล่างขึ้นบน ด้วยสูตรนี้ คือใส่ -1
=XLOOKUP(H2,D2:D57,E2:E57,”ไม่พบจ้ะ”,,-1)
(การไม่ใส่ค่าประเภทการจับคู่ จะมีค่าเท่ากับใส่ค่าประเภทการจับคู่เป็น 0 คือ จับเฉพาะข้อมูลที่ตรงกันเท่านั้น)
ค่าที่ได้จะเป็น 2,352,835 ใน Cell E14 ซึ่งเป็น Cell แรก จากด้านล่างขึ้นมาข้างบน
มาดูสิ่งที่ดีงามสร้างความสบายในการทำงานที่ VLookup ทำไม่ได้อีกอย่างก็คือ การแสดงผลลัพธ์ที่มากกว่า 1 อย่างเช่น ตัวอย่างเดิม แต่เราอยากให้แสดงทั้ง ค่าของ วันที่ (แถวฺ B) และเลขที่ห้อง (แถว C) และราคา (คอลัมน์ D) เราสามารถให้แสดงผลได้ในคำสั่งเดียว โดยใช้ช่วงของแถวที่ต้องการให้แสดงผลลัพธ์ได้เลย คือ โดยช่วยที่ให้แสดงผลลัพธ์ใส่รวบทั้ง 3 คอลัมน์พร้อมกัน
=XLOOKUP(H2,A2:A57,B2:D57,”ไม่พบจ้ะ”)
จากตัวอย่าง สูตรนี้เขียนที่ Cell I2 เพียง Cell เดียว แต่แสดงผลที่ Cell J2 และ K2 ด้วย เมื่อลองคลิกไปดูใน K2 จะเห็นสูตรแบบเดียวกับ I2 แต่เป็นตัวสีเทา อันนี้คือ ถ้าเราให้แสดงผลลัพธ์ 3 Cell (หรือมากกว่า) จะแสดงใน Cell ทางด้านขวาที่ยังเป็น Cell ว่างโดยอัตโนมัติ นี่คือการใช้งานแบบพื้น ๆ ซึ่งต่อไปจะเขียนถึงการนำไปใช้แบบซับซ้อนมากขึ้น เช่นค้นหาแบบ 2 เงื่อนไข หรือ หลายเงื่อนไขซ้อนกัน
ถ้าใครย้อนกลับไปดูที่ ลิงก์ของไมโครซอฟต์ ก็จะเห็นว่า ในเว็บของไมโครซอฟต์ให้ตัวอย่างการใช้งานในแบบค้นหาสองทาง ทั้งแนวตั้ง แนวนอน และการใช้งานแบบอื่น ๆ อีก ซึ่งจะเอามาอธิบายในครั้งนี้
ค้นหาแบบ 2 เงื่อนไข
อย่างแรกคือการค้นหาแบบสองทาง หรือการหาแบบ Crosstab สมมติว่าเรามีตารางหนึ่ง ที่จะบอกว่า ในรอบ 4 ปี มีรายได้จากภาษีอะไรบ้าง การค้นหาจะมีเงื่อนไขว่า จะหาจากประเภทรายได้ และ ปีพ.ศ. เพื่อให้แสดงผลลัพธ์เป็นจำนวนรายได้ แบบนี้คือเราจะป้อนค่าเข้าไปสองค่า ค่าแรกคือจะหา ประเภทรายได้ และอีกค่าคือจะหา ปีพ.ศ.
เพื่อให้เข้าใจง่าย จะเขียนสูตรออกมาแบบนี้
=XLOOKUP(H1, “ประเภทรายได้”,XLOOKUP(H2, “ปีพ.ศ.”, “เขตข้อมูล))
ในนี้คือ ชุดแรกจะค้นหาตามแนวตั้ง ส่วนชุดที่ 2 จะค้นหาตามแนวนอน
ให้ H1 เป็น Cell ที่จะใส่ข้อมูลประเภทรายได้ “ประเภทรายได้” คือค่าของประเภทรายได้ที่จะไปหา (ในที่นี้คือ A2-A18) และให้ H2 เป็น Cell ที่จะใส่ข้อมูลปีพ.ศ. ที่ต้องการ “ปีพ.ศ.” คือค่าของปีพ.ศ.ที่จะค้นหา (ในที่นี้คือ B1-E1) และเขตข้อมูลคือสิ่งที่ต้องการให้แสดงค่าจากพื้นที่นี้ ในที่นี้คือ คอลัมน์ B – E แถวที่ 2 – 18
เขียนมาเป็นสูตรได้ดังนี้
=XLOOKUP(H1,A2:A18,XLOOKUP(H2, B1:E1, B2:E18))
อธิบายฟังก์ชัน
อัถ้ามาเจาะสูตรการเขียนนี้ จะเห็นว่ามันคือการใช้อาร์เรย์ในการทำงาน แต่เราเขียนเป็นสูตรออกมาง่าย ๆ
XLOOKUP(H2, “ปีพ.ศ.”, “เขตข้อมูล))
ให้ H2 เป็น Cell ที่จะใส่ข้อมูลปีพ.ศ. ที่ต้องการ สมมติเราต้องการหาข้อมูลของปี 2559 ตรง “ปีพ.ศ.” ก็จะเป็นขอบเขต B1 – E1 (คือหัวตาราง พ.ศ. 2559 – 2562) ค่าที่เราใส่ไปใน H2 จะวิ่งมาหาข้อมูลที่ตรงกันจากในขอบเขตนี้ จากนั้นจะส่งกลับค่าใน “เขตข้อมูล” กลับมา ซึ่งข้อมูลในพื้นที่ก็จะเป็นในคอลัมน์ B ถึง E ตั้งแต่แถวแรกถึงแถวสุดท้ายที่ต้องการ
ซึ่งในตัวอย่างเราใส่ค่าปีพ.ศ. เป็น 2559 ค่าที่ส่งกลับมาในที่นี้จะเป็นคอลัมน์ B ก็จะเป็น
1635.750318
21.01442585
96.86156853
เรื่อยไปจนถึง 92.94231484
ถ้าเขียนเป็นอาร์เรย์ก็คือ {1635.750318, 21.01442585, 96.86156853,…,92.94231484}
ซึ่งตรงนี้ก็จะกลายเป็น ค่าที่ต้องการแสดงในสูตรอีกชุดหนึ่ง
=XLOOKUP(H1, ประเภทรายได้, {อาร์เรย์ที่ได้จากสูตรชุดที่สอง})
คิดว่าน่าจะเข้าใจคอนเซ็ปต์การทำงาน
ค้นหาแบบหลายเงื่อนไข
หลังจากที่ทำงานกับแบบสองเงื่อนไขแล้ว เรามาลองขยายขอบเขตเป็นแบบ 3 เงื่อนไข (หรือมากกว่า) กันบ้าง
สมมติว่าเรามีตาราง ซึ่งจะระบุว่า แบบบ้านนี้ เนื้อที่ดินเท่านี้ เนื้อที่ใช้สอยเท่านี้ ราคาเท่าไหร่ นั่นหมายถึงเราจะหาผลลัพธ์จากเงื่อนไขถึง 3 อย่าง ซึ่งเราสามารถใช้ & ในการผนวกค่าที่ต้องการค้นหาได้ แนวคิดการสร้างสูตรก็คือ
=XLOOKUP (ค่าที่หา1&ค่าที่หา2&ค่าที่หา3,ช่วงที่หา1&rช่วงที่หา2&ช่วงที่หา3,ช่วงที่แสดงผลลัพธ์)
คือคิดง่าย ๆ ค่าที่หามี 3 ตัว กับ ช่วงที่หา 3 ช่วง เสมือนเป็น 1 ค่า ในที่นี้ก็จะได้
=XLOOKUP (I2&I3&I4,B2:B13&C2:C13&D2:D13,F2:F13)
การหา I2&I3&I4 จะเป็นการหา B70300 โปรดสังเกตว่า ค่าอาร์เรย์ต่าง ๆ ที่เคยใช้งานปรกติจะใส่ { } ประกบไว้ แต่ฟัง์กชันใหม่นี่ออกแบบมาให้ทำงานกับอาร์เรย์ได้โดยตรง ตรงนี้คือความง่ายที่ต่างจาก VLookup และช่วยให้การทำงานง่ายขึ้นมาก