ต้องการ Match ข้อมูลระหว่าง 2 ชีท และกระจายข้อมูลตามช่วงเงิน

[niwat2811]

เงื่อนไขที่ต้องการคือ
ต้องการ match ข้อมูลที่ชีท 1 คอลัมภน์ C โดยถ้าข้อมูลแต่ละแถว
ตรงกับข้อมูลในชีท 2 คอลัมภน์ B ให้นำจำนวนเงินคงเหลือของชีท 1 คอลัมภน์ F
มากระจายตามช่วงเงินในคอลัมภน์ G โดยดูข้อมูลตามช่วงเงินได้จากชีท 2 คอลัมภน์ C
และให้นำเรทการคิดค่าธรรมเนียมตามช่วงเงินที่ชีท 2 คอลัมภน์ D มาไว้ที่ชีท 1 คอลัมภน์ H
แต่ถ้าข้อมูลไม่ match กันก็ให้นำจำนวนเงินของชีท 1 คอลัมภน์ F มาวางไว้ที่คอลัมภน์ G
ไม่ทราบว่าควรใช้สูตรอย่างไรดีครับ
ตัวอย่างคำตอบที่ต้องการอยู่ในชีท 1 คอลัมภน์ P กับ Q ครับ

[DhitiBank]

คิดจนเมื่อยหัวเลยครับ ลองแบบนี้ครับ

1. ที่ Sheet2 เตรียมขั้นบันไดใหม่ เพื่อให้เขียนสูตรอ้างอิงได้ง่ายขึ้นหน่อย
1.1 E1 คีย์
=IF(C1="-",0,RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"=",REPT(" ",20)),"-",REPT(" ",20)),"<",REPT(" ",20)),20)-IF(ISNUMBER(SEARCH("<",C1)),0.0001,0))
Enter -> คัดลอกลงล่าง

1.2 F1 คีย์
=E1

1.3 F2 คีย์
=IF(ISNUMBER(SEARCH(">=",C2)),99999999999999,IF(COUNTIF(B$2:B2,B2)=1,E2,E2-E1))
Enter -> คัดลอกลงล่าง

2. ที่ Sheet1
2.1 G3 คีย์
=IF(COUNTIF(Sheet2!$B$1:$B$24,LOOKUP(CHAR(255),$C$3:$C3))=0,LOOKUP(9.9999999999E+307,$F$3:$F3),
ROUND(MIN(LOOKUP(9.9999999999E+307,$F$3:$F3)-SUM(OFFSET(G$2,LOOKUP(CHAR(255),$C$3:$C3,ROW($C$3:$C3)-ROW($C$3)),,
ROWS($C$3:$C3)-LOOKUP(CHAR(255),$C$3:$C3,ROW($C$3:$C3)-ROW($C$3))))
+SUM(OFFSET(G$2,LOOKUP(CHAR(255),$C$3:$C3,ROW($C$3:$C3)-ROW($C$3)),)),
INDEX(Sheet2!$F$1:$F$24,MATCH(LOOKUP(CHAR(255),$C$3:$C3),Sheet2!$B$1:$B$24,0)+MIN(ROWS($C$3:$C3)-LOOKUP(CHAR(255),$C$3:$C3,ROW($C$3:$C3)-ROW($C$3))-1,LOOKUP(2,1/(Sheet2!$B$1:$B$24=LOOKUP(CHAR(255),$C$3:$C3)),ROW(Sheet2!$B$1:$B$24)-ROW(Sheet2!$B$1)+1)-MATCH(LOOKUP(CHAR(255),$C$3:$C3),Sheet2!$B$1:$B$24,0)))),4))
Enter -> คัดลอกลงล่าง

2.2 H3 คีย์
=IF(G3=0,"",INDEX(Sheet2!$D$1:$D$24,MATCH(LOOKUP(CHAR(255),$C$3:$C3),Sheet2!$B$1:$B$24,0)+MIN(ROWS($C$3:$C3)-LOOKUP(CHAR(255),$C$3:$C3,ROW($C$3:$C3)-ROW($C$3))-1,LOOKUP(2,1/(Sheet2!$B$1:$B$24=LOOKUP(CHAR(255),$C$3:$C3)),ROW(Sheet2!$B$1:$B$24)-ROW(Sheet2!$B$1)+1)-MATCH(LOOKUP(CHAR(255),$C$3:$C3),Sheet2!$B$1:$B$24,0))))
Enter -> คัดลอกลงล่างครับ

สำหรับสูตรในข้อ 2.2 บางรายการจะติด #N/A เนื่องจากไม่พบในฐานข้อมูล Sheet2 นะครับ

[niwat2811]

ต้องขออภัยคุณ DhitiBank ด้วยครับที่เข้ามาตอบช้า
ได้ลองนำสูตรที่ให้มาไปลองใช้แล้วพบว่าติดปัญหา
ตรงบรรทัดที่ 25 คือยังดึงข้อมูลมาไม่ถูกต้อง ตามไฟล์แนบ
ไม่ทราบว่าต้องแก้ไขตรงไหนเพิ่มอย่างไรครับ
ขอบคุณมากครับ

[DhitiBank]

หากยังไม่มีใครตอบ เดี๋ยวผมดูให้คืนนี้นะครับ ตอนนี้ขับรถครับ

[DhitiBank]

สอบถามหน่อยครับ
จากรูปที่ 1 ใน Sheet2 เซลล์ C11 จำนวนเงินเป็น "-" หมายความว่าอย่างไรครับ ผมไม่แน่ใจเลยใส่สูตรให้แปลงเป็นขั้นบันไดแล้วได้ 0 (ดังที่แสดงเซลล์ E11 และ F11) เวลาดึงข้อมูลมา สูตรพบว่า 0 มีค่าน้อยที่สุดเลยแสดง 0 แทนที่จะแสดงจำนวนเงินในเซลล์ G25 ของ Sheet1

Pict1.png

แต่หากผมสมมติจำนวนเงินให้มากเกินจริงใน Sheet2 เซลล์ C11 สูตรจะสามารถแสดงผลได้ถูกต้องครับ ดังแสดงในรูปที่ 2

Pict2.png

ไม่ทราบว่าพอจะแก้ไขที่ขั้นบันไดใน Sheet2 แทนได้ไหมครับ (ที่จริงแก้สูตรก็ได้ แต่ผมลืมแนวคิดไปหมดแล้ว หากต้องปรับสูตรใหม่ ก็ยาว...ครับ )

[menem]

ลองดูนะครับ ผมใช้วิธีแปลงตารางเป็นแบบขั้นบรรได (เหมือนคิดภาษี)
น่าจะทำให้การคำนวณง่ายขึ้นครับ

** มีบางรายการไม่ตรงกับคำตอบที่ให้ไว้ เนื่องจากไม่พบรายการนั้น ๆ ในตารางเงื่อนไขนะครับ

[niwat2811]

ขอบคุณทั้งสองท่านมากครับ ได้ลองทำตามคำแนะนำแล้ว สามารถใช้งานได้ถูกต้องครับ