Snasui Excel Forum

ต้องการคำนวณโดยมีเงื่อนไขที่มีตัวแปร 4 ตัว ต้องใช้สูตรลิงค์มาแบบไหนดีครับ รายละเอียดตามซีท "เงินเดือนใหม่"
จากสูตรนี้ต้องการให้เป็นค่าว่างต้องปรับแก้ตรงไหนครับ รายละเอียดตามซีท "เงินเดือน"
=IF(D5=4,G5,IF(D5=3.75,H5,IF(D5=3.5,I5,IF(D5=3.25,J5,IF(D5=3,K5,IF(D5=2.75,L5,IF(D5=2.5,M5,IF(D5=2.25,N5))))))))+IF(D5=2,O5,IF(D5=1.75,P5,IF(D5=1.5,Q5,IF(D5=1,R5,IF(D5=0,S5,IF(D5="",""))))))

ลองตามนี้ครับ

1. ชีทบัญชีเงินเดือนจะต้องเติม ประเภทตำแหน่ง และ ระดับ โดยไม่ให้มีค่าว่าง

2. ชีทเงินเดือนเซลล์ E5 คีย์สูตร

=IF(D5="","",INDEX($G5:$S5,MATCH(D5/100,$G$4:$S$4,-1)))

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

3. ชีทเงินเดือนใหม่เซลล์ F5 คีย์สูตร

=ROUNDUP(E5*INDEX(บัญชีเงินเดือน!$G$5:$G$26,MATCH(1,IF($B5=บัญชีเงินเดือน!$B$5:$B$26,IF($C5=บัญชีเงินเดือน!$C$5:$C$26,IF($D5>=บัญชีเงินเดือน!$D$5:$D$26,IF($D5<=บัญชีเงินเดือน!$E$5:$E$26,1)))),0))/100,-1)

Ctrl+Shift+Enter > Copy ลงด้านล่าง

ขอบคุณครับ...สูตรสั้นกระทัดรัดดีครับ
ทดลองคีย์ข้อมูลดูพบว่า ถ้าีคีย์ค่าของเปอร์เซ็นต์ที่ได้เลื่อนมีค่าต่างไปจากเรนจ์ที่กำหนด ค่ามันควรจะแสดงเป็นค่าว่างหรือแสดงข้อความเตือนอะไรสักอย่างให้ทราบ เช่น ที่ซีทเงินเดือน ถ้าคีย์ค่าที่ D5 ผิดไปเป็น D5=5 ค่าที่ได้ตรง E5 จะเป็น #N/A หรือคีย์ค่าที่ D5 =3.6 ค่าที่แสดง E5 = 1150 ซึ่งมีค่าไม่ถูกต้อง
ที่ซีท "เงินเดือนใหม่" ถ้าคีย์ค่าที่ E5 ผิดไป เช่น 4.6 ค่าที่ได้ตรง F5 ก็จะผิดไป คือว่า เปอร์เซนต์ที่เลื่อนได้ถูกกำหนดไว้เป็น 4%,3.75%,3.5%,3.25%,3.0%,2.75%,2.5%,2.25%,2.0%,1.75%,1.5%,1% ตามลำดับ

ใช้สูตร If ดักค่าที่ว่าได้ครับ เช่น

ที่ชีทเงินเดือนเซลล์ E5

=IF(ISNUMBER(MATCH(D5/100,$G$4:$S$4,0)),INDEX($G5:$S5,MATCH(D5/100,$G$4:$S$4,-1)),"")

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

ที่ชีทเงินเดือนใหม่เซลล์ F5 คีย์

=IF(ISNUMBER(MATCH(E5/100,เงินเดือน!$G$4:$S$4,0)),ROUNDUP(E5*INDEX(บัญชีเงินเดือน!$G$5:$G$26,MATCH(1,IF($B5=บัญชีเงินเดือน!$B$5:$B$26,IF($C5=บัญชีเงินเดือน!$C$5:$C$26,IF($D5>=บัญชีเงินเดือน!$D$5:$D$26,IF($D5<=บัญชีเงินเดือน!$E$5:$E$26,1)))),0))/100,-1),"")

Ctrl+Shift+Enter > Copy ลงด้านล่าง

ใช้สูตร If ดักค่าที่ว่าได้ครับ เช่น

ที่ชีทเงินเดือนเซลล์ E5

=IF(ISNUMBER(MATCH(D5/100,$G$4:$S$4,0)),INDEX($G5:$S5,MATCH(D5/100,$G$4:$S$4,-1)),"")

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

ที่ชีทเงินเดือนใหม่เซลล์ F5 คีย์

=IF(ISNUMBER(MATCH(E5/100, เงินเดือน!$G$4:$S$4,0)),ROUNDUP(E5*INDEX(บัญชีเงินเดือน !$G$5:$G$26,MATCH(1,IF($B5=บัญชีเงินเดือน!$B$5:$B$26,IF($C5=บัญชีเงิน เดือน!$C$5:$C$26,IF($D5>=บัญชีเงินเดือน!$D$5:$D$26,IF($D5<=บัญชี เงินเดือน!$E$5:$E$26,1)))),0))/100,-1),"")

Ctrl+Shift+Enter > Copy ลงด้านล่าง

ทดลองแล้วพบว่าถ้า E5 หรือ F5 ของทั้ง2 สูตรเป็นค่าว่างค่าที่ได้ไม่ยอมเป็นค่าว่างตามครับ

กรณีต้องการให้เป็นค่าว่างอาจจะกระทบกับการนำผลลัพธ์ไปคำนวณต่อ ซึ่งคิดว่าคุณ Joo สามารถปรับเองได้นะครับ

ลองตามนี้ครับ

1. ที่ชีท เงินเดือน เซลล์ E5 คีย์

=IF(OR(ISNA(MATCH(D5/100,$G$4:$S$4,0)),D5=""),"",INDEX($G5:$S5,MATCH(D5/100,$G$4:$S$4,-1)))

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

2. ที่ชีท เงินเดือนใหม่ เซลล์ F5 คีย์

=IF(OR(ISNA(MATCH(E5/100,เงินเดือน!$G$4:$S$4,0)),E5=""),"",ROUNDUP(E5*INDEX(บัญชีเงินเดือน!$G$5:$G$26,MATCH(1,IF($B5=บัญชีเงินเดือน!$B$5:$B$26,IF($C5=บัญชีเงินเดือน!$C$5:$C$26,IF($D5>=บัญชีเงินเดือน!$D$5:$D$26,IF($D5<=บัญชีเงินเดือน!$E$5:$E$26,1)))),0))/100,-1))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

ผมว่าลองใช้ Format มาช่วยดูน่าจะดีกว่านะครับ

เช่น ที่เซลล์ E5 ใช้ Format
#,##0_);[Red](#,##0);
คือถ้ามีค่าเป็น 0 ให้แสดงเป็นว่าง เพราะอาจจะมีผลต่อการนำค่าไปคำนวณต่อ

ขอบคุณครับ... มีเพิ่มครับที่ซีทเงินเดือน1ถ้าค่าที่ D5ไม่อยู่ในเรนจ์ แล้วต้องการให้ H5 แล้วแสดงเป็นค่าว่าง ต้องปรับแก้สูตรนี้อย่างไรดีครับ
=IF(D5="","",INDEX(บัญชีเงินเดือน!$G$5:$G$26,MATCH(1,IF($B5=บัญชีเงินเดือน!$B$5:$B$26,IF($C5=บัญชีเงินเดือน!$C$5:$C$26,IF($D5>=บัญชีเงินเดือน!$D$5:$D$26,IF($D5<=บัญชีเงินเดือน!$E$5:$E$26,1)))),0)))

ลองตามนี้ครับ

ชีทเงินเดือน1 เซลล์ H5 คีย์

=IF(SUM((D5>=บัญชีเงินเดือน!$D$5:$D$26)*(D5<=บัญชีเงินเดือน!$E$5:$E$26))=0,"",INDEX(บัญชีเงินเดือน!$G$5:$G$26,MATCH(1,IF($B5=บัญชีเงินเดือน!$B$5:$B$26,IF($C5=บัญชีเงินเดือน!$C$5:$C$26,IF($D5>=บัญชีเงินเดือน!$D$5:$D$26,IF($D5<=บัญชีเงินเดือน!$E$5:$E$26,1)))),0)))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

ทดลองแล้วพบว่าถ้าค่าที่ D5 อยู่นอกเรนจ์ ที่ H5 จะแสดงค่าเป็น #N/A ครับ

joo wrote:ทดลองแล้วพบว่าถ้าค่าที่ D5 อยู่นอกเรนจ์ ที่ H5 จะแสดงค่าเป็น #N/A ครับ

ที่ว่า D5 อยู่นอก Range แล้วเป็น #N/A ลองยกตัวอย่างมาสักค่าครับ

ที่เงื่อนไข B5 = AC,C5 = 2 ค่าของ D5 จะอยู่ในช่วง 12530 – 25180 และ 25190 – 36020(ดูจากซีทบัญชีเงินเดือน) ถ้าเราคีย์ค่า D5= 36640 ซึ่งอยู่นอกเรนจ์และไม่ตรงเงื่อนไข ค่าที่ H5 ก็จะเป็น #N/A แบบนี้ครับ

ลองตามนี้ครับ

ชีทเงินเดือน1 เซลล์ H5 คีย์

=IF(ISNA(MATCH(1,IF($B5=บัญชีเงินเดือน!$B$5:$B$26,IF($C5=บัญชีเงินเดือน!$C$5:$C$26,IF($D5>=บัญชีเงินเดือน!$D$5:$D$26,IF($D5<=บัญชีเงินเดือน!$E$5:$E$26,1)))))),"",INDEX(บัญชีเงินเดือน!$G$5:$G$26,MATCH(1,IF($B5=บัญชีเงินเดือน!$B$5:$B$26,IF($C5=บัญชีเงินเดือน!$C$5:$C$26,IF($D5>=บัญชีเงินเดือน!$D$5:$D$26,IF($D5<=บัญชีเงินเดือน!$E$5:$E$26,1)))),0)))

Ctrl+Shift+Enter

ทดลองแล้วใช้งานได้ดีครับ ขอบคุณครับ...

ในแต่ละตำแหน่งจะมีระดับสูงสุดอยู่ ถ้า B5 = AC,C5 = 1ถึง5 ค่าของ D5 อยู่ในเรนจ์ของแต่ละระดับ(ดูจากซีทบัญชีเงินเดือน) เช่น ถ้าเราคีย์ค่า D5= 36020 ซึ่งเป็นค่าที่ยังอยู่ในเรนจ์ของตำแหน่ง AC ที่ระดับ2 (ดูจากซีทบันชีเงินเดือน)ก็ให้คำนวณแบบปัดเศษขึ้นเต็มสิบ แต่ถ้า D5 อยู่ที่ค่าสูงสุดของเรนจ์ ใน ตำแหน่ง AC(B5=AC) ระดับ 5(C5=5) ก็ให้คำนวณแบบไม่ต้องปัดเศษ ต้องปรับสูตรที่ I5:U5 อย่างไรครับ
I5=IF(ISNA(MATCH(1,IF($B5=บัญชีเงินเดือน!$B$5:$B$34,IF($C5=บัญชีเงินเดือน!$C$5:$C$34,IF($D5>=บัญชีเงินเดือน!$D$5:$D$34,IF($D5>=บัญชีเงินเดือน!$E$5:$E$34,1)))))),ROUNDUP($H5*I$4,-1), $H5*I$4)

ลองตามนี้ครับ

ที่ I5 คีย์

=IF($C5=MAX(IF(บัญชีเงินเดือน!$B$5:$B$26=$B5,บัญชีเงินเดือน!$C$5:$C$26)),$H5*I$4,ROUNDUP($H5*I$4,-1))

Ctrl+Shift+Enter

ทดลองแล้วยังไม่ได้ครับ...เรียบเรียงเงื่อนไขใหม่พร้อมคำถามเพิ่มครับ ที่ซีทเงินเดือน1 ถ้า B5= AC ,C5 =2 ,แล้วD5 อยู่ใรเรนจ์ 125530-25180 และ 25190-36020 ก็ให้คำนวณที่ I5 แบบปัดเศษขึ้นเต็มสิบ แต่ถ้า D5= 36020 ซึ่งเป็นค่าสูงสุดของเรนจ์ ก็ให้ตัวอักษรที่ D5เปลี่ยนเป็นสีแดงและคำนวณที่ I5 แบบธรรมดาไม่ต้องปัดเศษครับ

ยังไม่ค่อยกระจ่างครับ จากข้อความ

-ถ้า C5=2,D5 = ค่าสูงสุดของเรนจ์ก็ให้ D5 เป็นตัวอักษรสีแดง
-ถ้า C5=2,D5 = ค่าสูงสุดของเรนจ์ก็ให้แสดง"เต็มขั้น"ที่เซลล์ J19

ทั้งสองเงื่อนไขไม่ต้องดูตำแหน่งว่าเป็น AC หรือ G หรือ ฯลฯ ใช่หรือไม่ครับ

ต้องขออภัยด้วยครับที่บอกเงื่อไขไม่หมด คือว่ายังคงดูตำแหน่งว่าเป็น AC หรือ G อยู่เหมือนเดิมครับ
เช่น ถ้าฺ B5=AC,C5=2,D5= ค่าสูงสุดของเรนจ์ก็ให้ D5 เป็นตัวอักษรสีแดง
ถ้า ฺB5= AC,C5=2,D5 = ค่าสูงสุดของเรนจ์ก็ให้แสดง"เต็มขั้น" สมมติไว้ที่เซลล์ J19(ส่วนมากตำแหน่ง G และ AC จะเต็มขั้นที่ระดับ2โอกาสที่จะไประดับ3,4,5 น้อยมาก)

ลองตามนี้ครับ

1. เซลล์ I5 คีย์สูตรเพื่อการปัดหรือไม่ปัดตามเงื่อนไข

=IF($D5=MAX(IF($B5=บัญชีเงินเดือน!$B$5:$B$26,IF($C5=บัญชีเงินเดือน!$C$5:$C$26,บัญชีเงินเดือน!$E$5:$E$26))), ROUNDUP($H5*I$4,-1),$H5*I$4)

Ctrl+Shift+Enter > Copy ไปทางขวาและลงด้านล่าง

2. เซลล์ W5 คีย์สูตรเพื่อหาค่า Max ของแต่ละบรรทัดตามเงื่อนไข

=MAX(IF($B5=บัญชีเงินเดือน!$B$5:$B$26,IF($C5=บัญชีเงินเดือน!$C$5:$C$26,บัญชีเงินเดือน!$E$5:$E$26)))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

3. เซลล์ J19 คีย์สูตรเพื่อแสดงเต็มขั้นหรือว่างเมื่อเข้าเงื่อนไข

=IF($D5=MAX(IF(บัญชีเงินเดือน!$B$5:$B$26=$B5,IF(บัญชีเงินเดือน!$C$5:$C$26=$C5,บัญชีเงินเดือน!$E$5:$E$26))),"เต็มขั้น","")

Ctrl+Shift+Enter

ดูไฟล์แนบประกอบครับ