ซ่อมคอมพิวเตอร์นอกสถานที่ บางกะปิ รามคำแหง

วันพุธที่ 18 กรกฎาคม พ.ศ. 2555

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(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 ได้เลยเช่นกัน


วิธีการมีดังนี้


  1. ลากเมาส์คลุม (A1:A5<>"") แล้วกด F9 จะได้
    =LOOKUP(2,1/{TRUE;TRUE;TRUE;FALSE;TRUE},A1:A5)

  2. ลากเมาส์คลุม 1/{TRUE;TRUE;TRUE;FALSE;TRUE}แล้วกด F9 จะได้
    =LOOKUP(2,{1;1;1;#DIV/0!;1},A1:A5)

  3. ลากเมาส์คลุม 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 ออกแล้วสังเกตดูผลลัพธ์ครับ

0 ความคิดเห็น:

แสดงความคิดเห็น