Snasui Excel Forum

ขอคำแนะนำจากพี่ ๆ ทั้งหลาย ในการใช้สูตรในการคำนวณ ค่าครองชีพ ค่ะ ในช่อง D4 ถึง D7 โดยใช้ข้อมูลจากเงินเดือนของแต่ละคน ตามเงื่อนไขทั้ง 2 กรณี ค่ะ

1. กรณีเงินเดือนไม่ถึง 11,700 บาท ให้เพิ่มค่าครองชีพเดือนละ 1,500 บาท เมื่อรวมกันแล้ว ต้องไม่เกิน 11,700 บาท
ตามตัวอย่างลำดับที่ 2 , 3 และ 4

2. กรณีรับเงินดังกล่าวรวมกันแล้วไม่ถึงเดือนละ 8,200 บาท ให้เพิ่มค่าครองชีพจนถึง 8,200 บาท
ตามตัวอย่างลำดับที่ 1


ขอบคุณล่วงหน้าค่ะ

โจทย์คือให้หา ค่าครองชีพ ในคอลัมน์ E4 เป็นต้นไป
จาก 2 เงื่อนไข จะมีความเป็นไปได้ว่า
1. จะต้องรู้เงินเดือนขั้นต่ำ ที่จะสามารถ + ได้มากกว่า 1500 นั่นก็คือ
8200 - 1500 = 6700
เขียนได้เป็น =IF(D4<6700,8200-D4)
2. จะต้องรู้เงินเดือนสูงสุด ที่ทำให้ + ได้เต็มที่ 1500 นั่นก็คือ
11700 - 1500 = 10200
เขียนได้เป็น =IF(D4>10200,11700-D4)
3. ที่เหลือ ให้ค่าครองชีพ = 1500
รวมสูตรทั้งหมดเขียนใน E4
=IF(D4<6700,8200-D4,IF(D4>10200,11700-D4,1500))

ส่วนช่อง F4 ก็แค่เอา D4 + E4

เสร็จแล้วก็ลากสูตรทั้งสองช่องลงมาครับ

แถมอีกสูตรครับ

เซลล์ E4 คีย์

=LOOKUP(D4,{0,8201},{8200,11700})-MIN(11700,D4)

Enter > Copy ลงด้านล่าง

สำหรับกรณีที่เงินเดือนมากกว่าหรือเท่ากับ 11700 จะไม่ได้ค่าครองชีพ

ได้แล้วค่ะ
ดีใจมากเลย.... สามารถเอาสูตรที่ได้ นำไปดัดแปลง ใช้งานต่าง ๆ ได้ด้วย ค่ะ
ขอขอบพระ คุณพี่ ChoBkuN และ คุณพี่ snasui

ถามสูตร Lookup ครับว่า
=LOOKUP(D4,{0,8201},{8200,11700})-MIN(11700,D4)
ผมไม่แม่นสูตร array โดยเฉพาะ lookup
lookup_vector {0,8201} หมายความว่าอย่างไรครับ
lookup_result {8200,11700} คือ ยังก็จะประมวลผลออกมาแค่ 2 ค่าคือ 8200 กับ 11700 ใช่หรือเปล่าครับ

จากสูตร

=LOOKUP(D4,{0,8201},{8200,11700})-MIN(11700,D4)

หมายความว่า ให้หาค่าที่น้อยกว่าหรือเท่ากับ D4 ในช่วงข้อมูล {0,8201} แล้วนำค่าที่ตรงกันในช่วงข้อมูล {8200,11700} มาแสดง หลังจากนั้น ให้นำค่าที่น้อยกว่าระหว่าง 11700 และ D4 มาหักออกไปอีกที

ค่าที่เขียนอยู่ในเครื่องหมายปีกกาเป็นค่าคงที่ Array ซึ่งใช้แทนการเขียนลงไปในช่วงเซลล์แล้วอ้างอิงมาใช้ครับ

สมมุติเรานำค่าคงที่ Array มาเขียนใน F1:G2 แล้วอ้างอิงมาใช้ สูตรจะอยู่ในรูปแบบด้านล่างครับ

=Lookup(D4,$F$1:$F$2,$G$1:$G$2)-Min(11700,D4)

ดังนั้น ตามที่ถามมา

ChoBkuN wrote:lookup_vector {0,8201} หมายความว่าอย่างไรครับ

หมายถึง ช่วงข้อมูลที่จะทำการ Lookup โดยนำ D4 มา Lookup เพื่อหาว่ามีค่าใดน้อยกว่าหรือเท่ากับ D4 หรือไม่

ChoBkuN wrote:lookup_result {8200,11700} คือ ยังก็จะประมวลผลออกมาแค่ 2 ค่าคือ 8200 กับ 11700 ใช่หรือเปล่าครับ

คำตอบคือ ถูกต้องครับ

ขอโทษครับ แต่ยังไม่เข้าใจ

snasui wrote:หมายความว่า ให้หาค่าที่น้อยกว่าหรือเท่ากับ D4 ในช่วงข้อมูล {0,8201}

Lookup คือ มันมีการทำงานอย่างไรครับ ถึงมองค่าที่ น้อยกว่าหรือเท่ากับ D4

อย่างใน Case คือ เงินเดือน 5000 บาท

Lookup จะมองค่าระห่าง 0 - 8201 บาท ซึ่ง 5000 อยู่ในช่วงข้อมูลนี้

แล้วมันเชื่อมโยงอย่างไรถึงจะป้อนค่า 8200 หรือ 11700 ใน vector_result

ตอนนี้เข้าใจว่าถ้าค่านั้น อยู่ในช่วงดังกล่าว (0-8201)จะซะท้อนค่า 8200

แต่ถ้าไม่อยู่ในช่วงดังกล่าว จะสะท้อนค่า 11700

ถ้าเป็นตามนี้จริงเราใช้ IF เขียนในรูปแบบนี้ได้ไหมครับ

=IF(and(D4>=0,D4<=8201),8200,11700)-MIN(11700,D4)

ChoBkuN wrote:Lookup คือ มันมีการทำงานอย่างไรครับ ถึงมองค่าที่ น้อยกว่าหรือเท่ากับ D4 อย่างใน Case คือ เงินเดือน 5000 บาท

จาก {0,8201} ค่าที่น้อยกว่าหรือเท่ากับ 5000 คือ 0 ครับ

หากกรณี D4 เป็น 12345678 ดังนั้นค่าที่น้อยกว่าหรือเท่ากับ 12345678 คือ 8201 ครับ

ผลลัพธ์คือค่าใด ๆ ในช่วง {8200,11700} ที่อยู่ในตำแหน่งเดียวกัน 0 หรือ ตำแหน่งเดียวกันกับ 8201

ChoBkuN wrote:ถ้าเป็นตามนี้จริงเราใช้ IF เขียนในรูปแบบนี้ได้ไหมครับ=IF(and(D4>=0,D4<=8201),8200,11700)-MIN(11700,D4)

ควรจะเป็น

=IF(and(D4>=0,D4<=8200),8200,11700)-MIN(11700,D4)

ขอบคุณครับ
สรุปก็คือ ธรรมชาติของ Lookup
จะมองค่าที่ต่ำกว่า ตัวที่เราอ้างอิง ในกรณีที่ไม่มีตัวเหมือนตรงๆ
คล้ายกับ Vlookup ที่เปิดท้าย เอาไว้ ใช้กรณีหาเกรดตามคะแนนที่อ้างอิง (A,B,C,D,F)
ใช่ไหมครับ

ใช่แล้วครับ