การดึงค่า lookup แบบมีเงื่อนไข

[indyzbinary]

ผมกำลังหาวิธีดึงค่าแบบมีเงื่อนไข โดยการออกบิลค่าส่งพัสดุ

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

billtest.xlsx

[snasui]

ช่วยยกตัวอย่างมาให้เห็นว่า "น้อยกว่าหรือเท่ากับน้ำหนัก" ที่ว่านั้นเช่นค่าใด และคำตอบที่ถูกต้องคือค่าใด ค่าที่เป็นคำตอบนั้นนำมาจากเซลล์ใด จะได้เข้าใจตรงกันครับ

[indyzbinary]

สมมุติว่า ผมใส่ post ที่ B6 อ้างจาก J33 เพื่อกำหนด Group ข้อมูลของประเภทพัสดุ
และ ใส่น้ำหนักที่ D6 = 900 อ้างจาก L33 แต่ค่าในเซลล์เป็น 1000
ถ้าเราต้องการให้ใส่ค่าในช่วง (0-1000) = 30 ในเซล M33 แสดงออกมาในช่อง F6 ครับ

[snasui]

เซลล์ F6 ปรับสูตรเป็นด้านล่างครับ

=INDEX($M$33:$M$90,MATCH(1,IF($K$33:$K$90=B6,IF(D6<=$L$33:$L$90,1)),0))

Ctrl+Shift+Enter

!

Note: Ctrl+Shift+Enter หมายถึง กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl+Shift ค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน จากนั้นกดแป้น Ctrl+Shift ค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้คีย์เข้าไปเองไม่ได้ ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

[indyzbinary]

=INDEX($M$33:$M$90,MATCH(1,IF($K$33:$K$90=B6,IF(D6<=$L$33:$L$90,1)),0))

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

[snasui]

จากสูตร =INDEX($M$33:$M$90,MATCH(1,IF($K$33:$K$90=B6,IF(D6<=$L$33:$L$90,1)),0))

หมายถึง จากช่วง $M$33:$M$90 ให้นำลำดับที่เป็นผลลัพธ์ของ MATCH(1,IF($K$33:$K$90=B6,IF(D6<=$L$33:$L$90,1)),0) มาแสดง หากผลลัพธ์ได้เป็น 10 หมายถึง จากช่วง $M$33:$M$90 ให้นำลำดับที่ 10 มาแสดง เช่นนี้เป็นต้น

จากสูตร MATCH(1,IF($K$33:$K$90=B6,IF(D6<=$L$33:$L$90,1)),0)

หมายถึง ให้หาเลข 1 ว่าอยู่ในลำดับที่เท่าไรของช่วงข้อมูลจากสูตร IF($K$33:$K$90=B6,IF(D6<=$L$33:$L$90,1)) หากเจอในลำดับที่ 8 จะแสดงค่าเป็นเลข 8 หากไม่เจอจะแสดง #N/A

จากสูตร IF($K$33:$K$90=B6,IF(D6<=$L$33:$L$90,1))

หมายถึง หาก $K$33:$K$90=B6 เป็นจริง ให้แสดงผลลัพธ์ของ IF(D6<=$L$33:$L$90,1) หากไม่เป็นจริงให้แสดงผลลัพธ์เป็น False

จากสูตร IF(D6<=$L$33:$L$90,1)

หมายถึง หาก D6<=$L$33:$L$90 เป็นจริง ให้แสดงผลลัพธ์เป็น 1 หากไม่เป็นจริงให้แสดงค่า False

สำหรับการแทนค่าสูตรให้แทนค่าจากคำอธิบายด้านล่างขึ้นด้านบน ส่วนการแกะสูตร วิธีหนึ่งคือ ให้คลุมค่า หรือ ช่วงเซลล์ในสูตรแล้วกดแป้น F9 เพื่อดูผลลัพธ์ หากต้องการยกเลิกให้กดแป้น Ctrl+Z ครับ

[indyzbinary]

ขอบคุณมากๆครับอาจารย์ excel นี่ทำอะไรได้หลายอย่างจริงๆ เลยครับ

[indyzbinary]

อาจารย์ครับแบบนี้ใน การอ้าง index ในแถว H33 มีผลไหมครับ

[snasui]

ลองได้เลยครับ หากไม่ได้คำตอบช่วยอธิบายมาอย่างละเอียดว่าต้องการคำตอบเป็นเท่าใด ด้วยเงื่อนไขใดจะได้เข้าใจตรงกันครับ

[indyzbinary]

ลองแล้วครับ ตาราง index ไม่มีผลใดๆ ตอนนี้ผม ย้าย DATA ไปไว้อีก ชีท นึงแล้วเพื่อความเป็นระเบียบ
=INDEX(DATA!$E$2:DATA!$E$59,MATCH(1,IF(DATA!$C$2:DATA!$C$59=B6,IF(D6<=DATA!$D$2:DATA!$D$59,1)),0))

และก็ได้เพิ่มตัวเลือก ประเภท พัสดุ โดยใช้ Data Validation เข้ามาช่วย

พอมีอาจารย์ชี้แนะ ก็เริ่มสนุกขึ้นมาและครับ