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

กรณีค่าหาค่าสุดท้าย สามารถดูได้ที่ การหาค่าสุดท้าย ซึ่งมีทั้งหาค่าสุดท้ายที่เป็นตัวเลข, เป็นอักขระ และค่าสุดท้ายโดยไม่สนใจว่าเป็นตัวเลขหรืออักขระ

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

ค่าว่างที่ได้จากสูตรที่พูดถึงในวรรคก่อนมีลักษณะเป็นดังนี้ครับ เช่น

=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_vector และจะนำค่าของ result_vector ในตำแหน่งเดียวกันมาแสดง

lookup_value  คือค่าที่ต้องการจะค้นหาในเวคเตอร์แรก โดยจะนำไปค้นหาใน lookup_vector ซึ่ง Lookup_value อาจเป็นตัวเลข ข้อความ ค่าตรรกะ หรือชื่อหรือการอ้างอิงค่ามาจากเซลล์ใด ๆ ก็ได้

lookup_vector  คือช่วงข้อมูล ซึ่งมีได้ 2 รูปแบบ คือ

1. ช่วงข้อมูลเพียงหนึ่งแถว
2. ช่วงข้อมูลเพียงหนึ่งคอลัมน์

ค่าใน lookup_vector อาจเป็นข้อความ ตัวเลขหรือค่าตรรกะก็ได้

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

result_vector  ช่วงข้อมูลที่ต้องการนำมาแสดงผล ช่วงนี้ต้องมีขนาดเดียวกับ lookup_vector

สิ่งสำคัญที่จะต้องทราบ

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

ดังนั้น ถ้าเทียบตามคำอธิบายข้างบน

1. เลข 2 หมายถึง Lookup value หรือค่าที่ต้องการค้นหาในช่วงข้อมูลตามข้อ 2
2. 1/(A1:A1000<>””) หมายถึงช่วงข้อมูลที่ต้องการค้นหาเลข 2 ในข้อ 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} ได้เพราะไม่มีค่าใดเท่ากับ 2 แต่จากคำอธิบายข้างบนบอกว่า 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 ออกแล้วสังเกตดูผลลัพธ์ครับ

Revised: January 28, 2017 at 11:49