Microsoft Excel Tips and Tricks: การหาค่าสุดท้ายที่ไม่เป็นค่าว่างที่ได้จากสูตร

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

ค่าว่างที่ได้จากสูตรที่พูดถึงในวรรคก่อนมีลักษณะเป็นดังนี้ครับ เช่น =If(A1=1,1,””) หาก A1 ไม่เท่ากับ 1 จะได้ผลลัพธ์เป็นช่องว่าง และหากเป็นช่องว่างแล้ว สูตรการหาค่าสุดท้ายที่เรากำลังกล่าวถึงจะไม่สนใจค่านี้

สมมุติ A1:A1000 คือขอบเขตที่ต้องการแสดงข้อมูลตัวสุดท้าย แต่ไม่รวมค่าว่างที่ได้จากสูตร

ลองคีย์ที่ Cell ใดๆ ตามนี้ครับ
=Lookup(2,1/(A1:A1000<>""),A1:A1000) แล้ว Enter

จากสูตร 

=Lookup(2,1/(A1:A1000<>""),A1:A1000)

แต่ละส่วนประกอบสูตรจะถูกแบ่งด้วยเครื่องหมายคอมม่า

ดังนั้น สิ่งที่เราต้องทราบมีดังนี้

1) 2 หมายถึงอะไร
2) 1/(A1:A1000<>"") หมายถึงอะไร
3) A1:A1000 หมายถึงอะไร

การจะทำความเข้าใจสูตรนี้ ต้องเข้าใจสูตร Lookup แบบเวกเตอร์ก่อนครับ

LOOKUP(lookup_value,lookup_vector,result_vector)

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

Lookup_value  ค่าที่ LOOKUP จะค้นหาในเวคเตอร์แรก หรือค่าที่จะค้นหาใน lookup_vector

Lookup_value อาจเป็นตัวเลข ข้อความ ค่าตรรกะ หรือชื่อหรือการอ้างอิงที่อ้างถึงค่า

Lookup_vector  ช่วงที่ประกอบด้วยแถวเพียงหนึ่งแถวหรือคอลัมน์เพียงหนึ่งคอลัมน์เท่านั้น ค่าใน lookup_vector อาจเป็นข้อความ ตัวเลขหรือค่าตรรกะ
สิ่งสำคัญ  ค่าใน lookup_vector ต้องอยู่ในลำดับจากน้อยไปหามาก: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE หากไม่เรียงในลักษณะนี้ ฟังก์ชั่น LOOKUP อาจไม่แสดงค่าที่ถูกต้อง ข้อความที่เป็นตัวพิมพ์ใหญ่และตัวพิมพ์เล็กนั้นเทียบเท่ากัน

Result_vector  ช่วงที่ประกอบด้วยแถวหรือแถวหรือคอลัมน์หนึ่งคอลัมน์เท่านั้น ช่วงนี้ต้องมีขนาดเดียวกับ lookup_vector

อันนี้สำคัญ 
หาก LOOKUP ไม่พบ lookup_value ฟังก์ชั่นนี้จะจับคู่ค่าที่ใหญ่ที่สุดใน lookup_vector ที่น้อยกว่าหรือเท่ากับ lookup_value
หาก lookup_value น้อยกว่าค่าที่น้อยที่สุดใน lookup_vector ฟังก์ชั่น LOOKUP จะให้ค่าข้อผิดพลาดเป็น #N/A

ดังนั้น ถ้าเทียบตามคำอธิบายข้างบน1. เลข 2 หมายถึง Lookup value หรือค่าที่ต้องการค้นหาในช่วงข้อมูลตามข้อ 2
2. 1/(A1:A1000<>"") หมายถึงช่วงข้อมูลที่ต้องการค่า Lookup_value ในข้อ 1
3. A1:A1000 หมายถึง ช่วงข้อมูลผลลัพธ์ที่ต้องการนำมาแสดง
หมายถึงว่า หากพบค่าข้อ 1 ในช่วงข้อมูลตามข้อ 2 อยู่ที่ตำแหน่งไหน ให้นำค่าในข้อ 3 ในตำแหน่งเดียวกันมาแสดง

ทีนี้มาแกะสูตรกัน เพื่อให้ง่าย ต้องสมมุติโจทย์ให้ง่ายก่อน 
สมมุติว่า A1:A5 ประกอบด้วย
A1=1
A2=5
A3=B
A4 คีย์สูตร=if(a2=5,"",0)
A5=8
ตามลำดับ

ที่ B1 เขียนสูตร

=LOOKUP(2,1/(A1:A5<>""),A1:A5)

ลองมาแกะสูตรกัน ต่อไปนี้จะทำที่ Formula bar หรือคลิก B1 แล้วกด F2 ก็สามารถทำใน Cell B1 ได้เลยเช่นกัน

ลากเมาส์คลุม (A1:A5<>"") แล้วกด F9 จะได้

=LOOKUP(2,1/{TRUE;TRUE;TRUE;FALSE;TRUE},A1:A5)

ลากเมาส์คลุม 1/{TRUE;TRUE;TRUE;FALSE;TRUE}แล้วกด F9 จะได้

=LOOKUP(2,{1;1;1;#DIV/0!;1},A1:A5)

ลากเมาส์คลุม A1:A5 แล้วกด F9 จะได้

=LOOKUP(2,{1;1;1;#DIV/0!;1},{1;5;"B";"";8})

จะเห็นว่าไม่สามารถค้นหาค่า 2 จากช่วง {1;1;1;#DIV/0!;1} ได้เพราะไม่มี แต่จากคำอธิบายข้างบนบอกว่า lookup_vector จะต้องเรียงกันจากน้อยไปหามาก และหากไม่มีค่าที่ตรงกันกับค่าที่ค้นหา จะใช้ค่าที่ใหญ่ที่สุดหรือค่าที่มากที่สุดที่เจอใน lookup_vector ที่น้อยกว่าหรือเท่ากับ lookup_value

จากความหมายข้างต้น ผลลัพธ์ที่ได้จะเป็นเลข 1 ตัวสุดท้ายเท่านั้น เพราะถือว่าเรียงจากน้อยไปหามาก และเป็นค่าใหญ่สุดแล้ว เมื่อได้ดังนี้แล้ว 1 ตัวสุดท้ายจะตรงกับตัวใดในช่วง result_vector

=LOOKUP(2,{1;1;1;#DIV/0!;1},{1;5;"B";"";8})

คำตอบที่ได้ก็คือ 8

ลองเขียนสูตรใหม่เหมือนตอนเริ่มต้น ลองลบเลข 8 ใน A5 ออกแล้วสังเกตดูผลลัพธ์ครับ