สร้างสูตรการคำนวณอัตราเงินเดือน

[sarat]

ขออภัย

มีเรื่ีองรบกวนต้องการที่จะสร้างสูตรในการคำนวณอัตราเงินเดือน พยามที่จะสร้างด้วยตนเองแล้วแต่คิดไม่ออก
จึงอยากรบกวนอาจารย์ช่วยสร้างให้ผมหน่อย เพื่อเป็นการลดภาระในการปฏิบัติงานให้ไวขึ้น จุดประสงค์ตามไฟล์ที่แนบครับ
ขอขอบพระคุณมา ณ โอกาสนี้

[snasui]

ช่วยลำดับว่าเซลล์ I6 ชีต อบต.ห้วยทราย มีค่าเป็น 558,120 มีวิธีการคิดอย่างไรมาอีกรอบครับ

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

[sarat]

ขออภัย ต้องมาตอบที่ทำงานครับ

[sarat]

ขออภัย พิมพ์ข้อความผิด ต้องเป็นลบด้วย ช่อง E (5) 706 แล้วคูณ 6

[snasui]

ขออภัย พิมพ์ข้อความผิด ต้องเป็นลบด้วย ช่อง E (5) 706 แล้วคูณ 6

ค่อย ๆ ถามตอบกันไปนะครับ

จากที่อธิบายมาในไฟล์แนบ

558120 มาจากการมอง D6 กับ H6 กับชีตฐานเงินเดือนบัญชี 5 คูณด้วย 12 เดือน บวก (เงินประจำตำแหน่ง คูณ ด้วย 12 เดือน)

เป็นการมองระดับตำแหน่งถ้าเป็นตำแหน่งบริหารท้องถิ่นระดับกลาง กับเงินเดือน โดยจะผันแปรไปตามตำแหน่ง และเงินเดือน ซึ่งในชีตฐานเงินเดือนจะเป็นตัวกำหนดเมื่อมองหา
เช่น บริหารท้องถิ่นระดับกลาง32510 จะไปตรงกับชีตฐานเงินเดือนบัญชี 5 (A706)ก็ให้นำเงินเดือน D (4) 706 คูณด้วย 12 บวก (7000+7000 คูณ 12)
สำหรับ Q6 3780 มาจากการมอง D6 กับ H6 เหมือนกันก็จะไปตรงกับชีตฐานเงินเดือนบัญชี5 ใน (A706) ก็ให้นำเงินเดือนช่อง F (6) 706 ลบด้วย ช่อง F (5) 706 แล้วคูณ 6
[/quot]

ที่ผมระบายสีไว้นั้นได้ค่ามาจากไหน และจะทราบได้อย่างไรว่าเป็น 7,000+7,000 ครับ

จากความต้องการที่แจ้่งมา

สิ่งที่การทำ
ช่อง I Q R T U มีสูตรอยู่

ถ้าต้องการทำใหม่
เมื่อคีย์ช่อง H (เงินเดือน) ต้องการให้สูตรในชีตสูตรระบบแท่งมาแสดงในช่อง I (รวมเงินเดือน) โดยที่ผันแปรไปตามช่อง D กับ ช่อง H

ตัวอย่าง เช่น ปลัด (บริหารท้องถิ่นระดับกลาง) เงินเดือน 32510
เมื่อเราคีย์ข้อมูลจากช่อง A ถึง ช่อง H ช่อง I จะดึงข้อมูลในชีตสูตรช่อง I (เหมือนกัน)มาคำนวณ ช่อง Q R T U เหมือนกัน
หากช่อง D เป็นปฏิบัติการ ก็จะดึงข้อมูลในชีพสูตร ที่เป็นปฏิบัติการ มาคำนวณ

เหตุผลเนื่องจะได้มีเสียเวลาเพราะสูตรในแต่ละระดับตำแหน่งไม่เหมือนกันในชีพสูตรระบบแท่ง หากคีย์แค่ ช่อง D กับช่อง H จะดึงข้อมูลมาได้หมดจะได้ลดระยะเวลาและไม่ผิดพลาด

จากด้านบนจะเห็นว่า ต้องการให้นำค่ามาแสดงที่คอลัมน์ I ดังนั้น จำเป็นต้องทราบทุกค่าที่เป็นที่มาของคอลัมน์ I ช่วยอธิบายมาเฉพาะ 7,000+7,000 มาอีกรอบครับ

[sarat]

ขออภัย ลืมคิดไปว่าในชีตสูตรระบบแท่ง ช่อง D จะมีบริหารท้องถิ่นระดับกลางอยู่ 4 รายการ แต่รายการที่มีปัญหาคือ ลำดับที่ D7 กับ D9 จะต่างกันตรงที่ในสูตรจะบวกเงินประจำตำแหน่ง (7000+7000) กับ (5600+5600)
(7000+7000) เป็นตำแหน่่งของ ปลัด อปท.
(5600+5600) เป็นตำแหน่งของ รองปลัด อปท.
โดยในช่อง C จะเป็นตัวกำหนดว่า ตำแหน่งนี้ ได้เงินประจำตำแหน่ง (ปจน.) เท่าใด หากจะกำหนดในช่อง C ด้วยไม่สามารถกระทำได้เพราะ อปท.แต่ละแห่งมีการเรียกตำแหน่งที่ต่างกัน จึงเป็นการเข้าใจเอาเองว่าหากเป็นตำแหน่งน้ีให้นำคอลัมน์นี้ บรรทัดนี้ไปใช้
สำหรับตำแหน่งว่างนั้น ได้กำหนดให้มีเครื่องหมาย - ต่อท้ายจึงสามารถแยกแยะได้ ถ้าเป็นเช่นนี้จะดำเนินการได้หรือไม่ ขอบคุณมากครับ

[snasui]

คำถามเพิ่มครับ

สูตรในคอลัมน์ I ของชีต อบต.ห้วยทราย บางเซลล์ คูณ ด้วย 12 บางเซลล์ไม่คูณ มีหลักการพิจารณาอย่างไรครับ

[sarat]

ขอโทษ ไปประชุมเลยไม่ได้เข้ามาดู
ช่อง I ถ้าเป็นตำแหน่งที่มีคนครอง (มีชื่อคนในช่อง V) ก็จะเป็นมากำหนดให้มองช่อง D และ ช่อง H ซึ่งจะมีชีตฐานเงินเดือน (ที่ตรงกัน) เป็นตัวกำหนดว่าต้องใช้คอลัมม์ใดในการคำนวณ และกำหนดให้คูณทั้งปี (12 เดือน) หากเป็นตำแหน่งที่ไม่มีคนครอง (ว่าง ในช่อง V) ก็กำหนดให้มองช่อง D เพียงอย่างเดียว ซึ่งในชีตฐานเงินเดือน(ที่ตรงกัน) ก็จะเป็นตัวกำหนดว่าต้องใช้คอลัมม์ใดในการคำนวณ โดยในกรณีนี้ในชีตฐานเงินเดือนจะคำนวณการคูณด้วย 12 ไว้แล้ว

[logic]

หลายขั้นตอนอยู่เหมือนกันครับ

แผ่นงาน สูตรระบบแท่ง
1. w6 =IF(LEFT(C6,2)="ตำ",C6,W5)
2. x6:x40 เป็นต้นไปกรอกยอดรวมเงินประจำตำแหน่ง
3. y6 =IF(W6="ตำแหน่งมีคนครอง",X6*12,X6)
คัดลอกสูตรข้อ 1, 2 ลงไปถึงบรรทัด 40

แผ่นงาน อบต.ห้วยทราย
i6 =IF(H6="",VLOOKUP(D6,'ฐานเงินเดือน บัญชี 5'!$A$2:$K$880,4,0),VLOOKUP(D6&H6,'ฐานเงินเดือน บัญชี 5'!$A$2:$K$880,4,0)*12)+IFERROR(INDEX(สูตรระบบแท่ง!$Y$7:$Y$40,MATCH(1,1/((สูตรระบบแท่ง!$D$7:$D$40=D6)*(สูตรระบบแท่ง!$H$7:$H$40>=H6)),0)),0)

อันนี้เป็นสูตรแบบอาร์เรย์ คัดลอกสูตรไปวางแล้วแป้นฟังก์ชั่น f2 ตามมาด้วย ctrl+shift ค้างไว้ แล้ว enter เสร็จแล้วค่อยคัดลอกสูตรไปวางในเซลล์อื่น

[sarat]

ขอโทษ รบกวนอีกครั้ง
เน่ืองจากสูตรที่กำหนดให้ ไม่สามารถคำนวณได้ตามวัตถุประสงค์ โดยมีรายละเอียดตามเอกสารที่แนบ
จึงขอรบกวนอีกสักครั้ง

[snasui]

ในชีตระบบแท่ง คอลัมน์ H แก้ไขเครื่องหมาย - ให้เป็น 0

เนื่องจากมีการใช้คอลัมน์ H เสมือนเป็น Number แต่เครื่องหมาย - เป็น Text ซึ่งจะมีค่ามากกว่า Number เสมอ ทำให้สูตรแสดงผลไม่ถูกต้อง

จากนั้นเปลี่ยนสูตรที่ H7 เป็น

=IF(H6="",VLOOKUP(D6,'ฐานเงินเดือน บัญชี 5'!$A$2:$K$880,4,0),VLOOKUP(D6&H6,'ฐานเงินเดือน บัญชี 5'!$A$2:$K$880,4,0)*12)+IFERROR(INDEX(สูตรระบบแท่ง!$Y$7:$Y$40,MATCH(1,1/((สูตรระบบแท่ง!$D$7:$D$40=D6)*(สูตรระบบแท่ง!$H$7:$H$40>=H6)*(สูตรระบบแท่ง!$H$7:$H$40>0)),0)),0)

Ctrl+Shift+Enter > Copy ไปยังเซลล์ที่เกี่ยวข้อง

[sarat]

ขอบคุณมาก มาก ครับ

[sarat]

รบกวนอีกสักคำถาม รายละเอียดตามแนบครับ

[snasui]

ถามคร่าว ๆ พอสังเขปมาในฟอรัมด้วย จะได้เป็นประโยชน์ในการค้นหาข้อมูล ระบบค้นหาไม่สามารถเข้าไปถึงข้อมูลในไฟล์แนบครับ

[sarat]

ต้องการแยกชาย หญิง ในแต่ละประเภท เนื่ีองจากสูตรเดิมที่กำหนดไว้ได้กำหนดให้นับรวมทั้งหมดทุกประเภท
ตัวอย่างเช่น ข้าราชการ มีจำนวนคน 14 คน ข้าราชการครู มีจำนวน 2 คน พนักงานจ้างตามภารกิจ มีจำนวน 11 คนเมื่ีอรวมแล้วจะมีทั้งหมด 27 คน แต่ไม่สามารถที่จะแยกออกมาเป็นข้าราชการที่เป็นชายกี่คน ข้าราชการที่เป็นหญิงกี่คน ในแต่ละประเภท
ขอบคุณครับ

[sarat]

ต้องการแยกชาย หญิง ในแต่ละประเภท เนื่ีองจากสูตรเดิมที่กำหนดไว้ได้กำหนดให้นับรวมทั้งหมดทุกประเภท
ตัวอย่างเช่น ข้าราชการ มีจำนวนคน 14 คน ข้าราชการครู มีจำนวน 2 คน พนักงานจ้างตามภารกิจ มีจำนวน 11 คนเมื่ีอรวมแล้วจะมีทั้งหมด 27 คน แต่ไม่สามารถที่จะแยกออกมาเป็นข้าราชการที่เป็นชายกี่คน ข้าราชการที่เป็นหญิงกี่คน ในแต่ละประเภท
ขอบคุณครับ

[snasui]

กรณีมียศนำหน้าชื่อโปรแกรมจะทราบได้อย่างไรว่าเป็นชายหรือหญิงครับ

ควรมีคอลัมน์ที่ระบุเพศมาช่วยจะดีกว่าแยกเพศด้วยคำนำหน้าชื่อครับ

[sarat]

รบกวน
เนื่ีองจากสูตรที่กำหนดให้ ปรากฎว่าในส่วนของตำแหน่ง ที่เป็นตำแหน่งว่าง
ปรากฎว่าไม่สามารถคำนวณให้ได้ตามข้อเท็จจริง กล่าวคือในชีตสูตรระบบแท่งหากเป็นตำแหน่งว่าง
ตำแหน่งรองปลัดองค์การบริหารส่วนตำบล ประเภท บริหารท้องถิ่นระดับต้น- ช่องเงินเดือนรวมจะเป็น 443940 บาท
แต่เมื่อคีย์ข้อมูลในลำดับที่ 3 ในชีต อบต.ห้วยทราย ปรากฎว่าช่องเงินเดือนรวมจะเป็น 447440 บาท

[snasui]

ให้อ้างอิงชื่อเซลล์ประกอบด้วยครับ การอ้างอิงเฉพาะค่าในเซลล์จะไม่สะดวกในการเข้าถึงข้อมูล

ตามด้านล่างนี้คือเซลล์ไหนครับ

เนื่องจากสูตรที่กำหนดให้ ปรากฎว่าในส่วนของตำแหน่ง ที่เป็นตำแหน่งว่าง
ปรากฎว่าไม่สามารถคำนวณให้ได้ตามข้อเท็จจริง

ด้านล่างนี้คือเซลล์ไหนครับ

ในชีตสูตรระบบแท่งหากเป็นตำแหน่งว่าง
ตำแหน่งรองปลัดองค์การบริหารส่วนตำบล ประเภท บริหารท้องถิ่นระดับต้น-

[sarat]

ขออภัยครับ
ในชีต อบต.ห้วยทราย เซลล์ I8