Snasui Excel Forum

เรียนอาจารย์และเพื่อนสมาชิก

สรุปคะแนนพนักงานในแต่ละปีและแต่ละครั้งที่ตามที่เลือก
สิ่งที่ต้องการ ขออธิบาย ดังนี้

อธิบาย
1.ในช่อง b2 ถึง e2 จะกรอกใส่เข้าไปเอง เพื่อหาคะแนนของพนักงานว่าคนนั้นๆ ได้คะแนนเท่าไรในแต่ละปี และปีละ 2 ครั้ง
2.ใน column A เป็นจำนวนปี ให้แสดงจำนวนปี ให้ตรงกันกับพนักงานงานที่เราเลือกใน ในช่อง b2 ถึง e2 โดยพิจารณาจากข้อมูลใน column K ถึง Q
แล้วใน คะแนนใน column P , Q มาใส่ไว้ตรงตามปี ตามครั้ง 1 และ ครั้ง 2 แยกเป็น 3 ช่วงคะแนนให้ถูกต้อง
ยกตัวอย่าง เช่น
นาย AAAA ABCDEF 1
มีข้อมูลใน column K ถึง Q อยู่ 12 ปี ให้นำแสดงไว้ 12 ปี ใน column A ให้สามารถเปลี่ยนแปลงไปตามที่เราเลือกพนักงานใน ในช่อง b2 ถึง e2
จากนั้นให้นำคะแนนครั้งที 1 และ ครั้งที่ 2 มาใส่ในรายการใน column B ถึง G ให้ถูกต้อง
ข้อสังเกตุ ที่ระบายสีไว้ ข้อมูลบางคนมีปีซ้ำกันได้แล้วแยกเป็น 2 รายการ ดูใน column K ถึง Q
จากตัวอย่างยกมา 2 คน

ขอบคุณครับ

...

B6:Dxx

=IFERROR((LOOKUP(AGGREGATE(15,6,$P$5:$P$21/($E$2=$N$5:$N$21)/($A6=$O$5:$O$21),1),{0,60,90},{3,2,1})=COLUMNS($B6:B6))*AGGREGATE(15,6,$P$5:$P$21/($E$2=$N$5:$N$21)/($A6=$O$5:$O$21),1),"")


E6:Gxx

=IFERROR((LOOKUP(AGGREGATE(15,6,$Q$5:$Q$21/($E$2=$N$5:$N$21)/($A6=$O$5:$O$21),1),{0,60,90},{3,2,1})=COLUMNS($E6:E6))*AGGREGATE(15,6,$Q$5:$Q$21/($E$2=$N$5:$N$21)/($A6=$O$5:$O$21),1),"")

Norkaz

สูตรได้ตามต้องการ
ขอเพิ่มเติมใน column A จำนวนปี ให้ปรับเปลี่ยนไปตามพนักงานในช่อง B2 ถึง E1 คือ เมื่อต้องการหาพนักงาน นาย AAAA นามสกุล ABCDEF เลขที่ 1 จำนวนปี ใน column A จะได้จำนวนตาม columm O ที่ 2006 - 2016 ทั้งหมด 12 ปี
หากเปลี่ยนเป็น พนักงาน นาย BBBB นามสกุล CCCCCC เลขที่ 10 จำนวนปีก็จะปรับเปลี่ยนไปเป็น 2 ปี คือ ปี 2020 และ 2021 เป็นต้น

ขอบคุณครับ

Totem wrote: Fri Mar 11, 2022 10:29 am สูตรได้ตามต้องการ
ขอเพิ่มเติมใน column A จำนวนปี ให้ปรับเปลี่ยนไปตามพนักงานในช่อง B2 ถึง E1 คือ เมื่อต้องการหาพนักงาน นาย AAAA นามสกุล ABCDEF เลขที่ 1 จำนวนปี ใน column A จะได้จำนวนตาม columm O ที่ 2006 - 2016 ทั้งหมด 12 ปี
หากเปลี่ยนเป็น พนักงาน นาย BBBB นามสกุล CCCCCC เลขที่ 10 จำนวนปีก็จะปรับเปลี่ยนไปเป็น 2 ปี คือ ปี 2020 และ 2021 เป็นต้น

ขอบคุณครับ


ใส่คะแนนให้ตรงช่อง.xlsx

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

..

โจทย์ ที่ถามมา ครั้งนี้ กับ โจทย์ ครั้งแรก ที่ถามมา ไม่สามารถ ใช้ แทนกันได้ หลักการเขียนสูตรก็ คนละแแบบกัน

ช่วยทำตัวอย่าง มา 2 ชีท

1. ก่อน เป็นแบบนี้
2. หลัง เมื่อคีย์ เลขที่พนักงานแล้ว ต้องการเป็นแบบใด

** หากพนักงาน 1คน มี เลขที่พนักงาน ไม่ซ้ำกับใคร ซึ่งตามหลักการก็ควรเป็นเช่นนั้น ใน

บริษัท หรืองค์กรใดๆ ไม่ควรมีเลขที่พนักงานเดียว ใช้ ร่วมกัน มากกว่า หนึ่งคน

ดังนั้นชื่อ นามสกุลไม่ต้องใช้ ให้ใช้ เลขที่พนักงานก็พอ

Norkaz

norkaz wrote: Fri Mar 11, 2022 12:06 pm ..

โจทย์ ที่ถามมา ครั้งนี้ กับ โจทย์ ครั้งแรก ที่ถามมา ไม่สามารถ ใช้ แทนกันได้ หลักการเขียนสูตรก็ คนละแแบบกัน

ช่วยทำตัวอย่าง มา 2 ชีท

1. ก่อน เป็นแบบนี้
2. หลัง เมื่อคีย์ เลขที่พนักงานแล้ว ต้องการเป็นแบบใด

** หากพนักงาน 1คน มี เลขที่พนักงาน ไม่ซ้ำกับใคร ซึ่งตามหลักการก็ควรเป็นเช่นนั้น ใน

บริษัท หรืองค์กรใดๆ ไม่ควรมีเลขที่พนักงานเดียว ใช้ ร่วมกัน มากกว่า หนึ่งคน

ดังนั้นชื่อ นามสกุลไม่ต้องใช้ ให้ใช้ เลขที่พนักงานก็พอ

Norkaz

อธิบายเพิ่มเติม ดังนี้
หาข้อมูลพนักงานตาม คำนำหน้า ชื่อ นามสกุล และเลขที่ ณ ช่วงวัน เดือน ปี นั้นๆ
พนักงาน 1 คน มี เลขที่พนักงาน ได้เพียงเลขเดียว แต่มีกรณีที่พนักงานคนนั้นๆ เปลี่ยนแผนก ทำให้ต้องเปลี่ยนเลขที่พนักงานให้ไปอยู่ในแผนกนั้นๆ ทำให้เลขที่เดิมที่ใช้อยู่ว่าง และเมื่อมีคนมาใหม่ก็จะแทนเลขที่พนักงานคนเก่าที่ย้ายแผนกไป
ดังนั้น ข้อมูลตาม column k ถึง q ยังเป็นเลขพนักงานเดิม ซึ่งเป็นช่วงวัน เดือน ปี ที่ได้คะแนน ณ เวลานั้น ได้คะแนนที่อยูในแผนกเดิม เมื่อย้ายเลขพนักงาน ก็จะไปหาช่วงวัน เดือน ปี อื่นๆ ไม่ต้องแสดงคะแนนออกมา และ column A ช่วงวัน เดือน ปี เปลี่ยนไปตามข้อมูล column k ถึง q ที่เป็นพนักงานคนนั้นๆ
ต้องการตามตัวอย่างที่แนบไว้ครับ
ขอบคุณครับ

..

ลองแบบนี้ ดู ถ้ายังไม่ใช่ ช่วยทำตัวอย่าง Before - After มาประกอบครับ


Excel 365

A6
=YEAR(FILTER($O$5:$O$99,$E$2=$N$5:$N$99))

B6:Dxx
=IFERROR(1/(LOOKUP(FILTER($P$5:$P$99,$E$2=$N$5:$N$99),{0,60,90},{3,2,1})=COLUMNS($B6:B6))*FILTER($P$5:$P$99,$E$2=$N$5:$N$99),"")

E6:Gxx
=IFERROR(1/(LOOKUP(FILTER($Q$5:$Q$99,$E$2=$N$5:$N$99),{0,60,90},{3,2,1})=COLUMNS($E6:E6))*FILTER($Q$5:$Q$99,$E$2=$N$5:$N$99),"")

Norkaz

norkaz wrote: Fri Mar 11, 2022 3:51 pm ..

ลองแบบนี้ ดู ถ้ายังไม่ใช่ ช่วยทำตัวอย่าง Before - After มาประกอบครับ


Excel 365

A6
=YEAR(FILTER($O$5:$O$99,$E$2=$N$5:$N$99))

B6:Dxx
=IFERROR(1/(LOOKUP(FILTER($P$5:$P$99,$E$2=$N$5:$N$99),{0,60,90},{3,2,1})=COLUMNS($B6:B6))*FILTER($P$5:$P$99,$E$2=$N$5:$N$99),"")

E6:Gxx
=IFERROR(1/(LOOKUP(FILTER($Q$5:$Q$99,$E$2=$N$5:$N$99),{0,60,90},{3,2,1})=COLUMNS($E6:E6))*FILTER($Q$5:$Q$99,$E$2=$N$5:$N$99),"")

Norkaz

ยังไม่ได้ตามต้องการ ขออธิบายและยกตัวอย่าง ตารางที่ 1 สูตรด้านบน กับตารางที่ 2 ที่ต้องการ ดังนี้

จาก column O ที่ O16 , O17 วัน เดือน ปี เดียวกันให้แสดงรายการอยู่ในแถวเดียวกันตามตารางที่ 2
ถ้า column O ที่ O16 , O17 วัน เดือน ปี คนละเดือน ให้แสดงรายการอยู่คนละแถวตามตารางที่ 2

ถ้าอิงตามคำนำหน้าชื่อ ชื่อ นามสกุล เลขที่ ใน columm B2 ถึง E2 ด้วย โดยไม่อิงแต่เลขที่อย่างเดียวจะดีมากครับ

ขอบคุณครับ

...

A6
=IFERROR(YEAR(INDEX($O:$O,AGGREGATE(15,6,ROW($K$5:$K$22)/(MATCH($B$2&$C$2&$D$2&$E$2&$O$5:$O$22,$K$5:$K$22&$L$5:$L$22&$M$5:$M$22&$N$5:$N$22&$O$5:$O$22,0)=ROW($K$5:$K$22)-ROW($K$5)+1),ROWS(A$6:A6)))),"")
Copy ลงไป


H6:Ixx

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


=SUMIFS(P$5:P$22,$K$5:$K$22,$B$2,$L$5:$L$22,$C$2,$M$5:$M$22,$D$2,$N$5:$N$22,$E$2,$O$5:$O$22,IFERROR((INDEX($O:$O,AGGREGATE(15,6,ROW($K$5:$K$22)/(MATCH($B$2&$C$2&$D$2&$E$2&$O$5:$O$22,$K$5:$K$22&$L$5:$L$22&$M$5:$M$22&$N$5:$N$22&$O$5:$O$22,0)=ROW($K$5:$K$22)-ROW($K$5)+1),ROWS(H$6:H6)))),""))


B6:Dxx
=IF($H6=0,"",IF(LOOKUP($H6,{0,60,90},{3,2,1})=COLUMNS($B6:B6),$H6,""))


E6:Gxx
=IF($I6=0,"",IF(LOOKUP($I6,{0,60,90},{3,2,1})=COLUMNS($E6:E6),$I6,""))

Norkaz

norkaz wrote: Mon Mar 14, 2022 8:09 pm ...

A6
=IFERROR(YEAR(INDEX($O:$O,AGGREGATE(15,6,ROW($K$5:$K$22)/(MATCH($B$2&$C$2&$D$2&$E$2&$O$5:$O$22,$K$5:$K$22&$L$5:$L$22&$M$5:$M$22&$N$5:$N$22&$O$5:$O$22,0)=ROW($K$5:$K$22)-ROW($K$5)+1),ROWS(A$6:A6)))),"")
Copy ลงไป


H6:Ixx

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


=SUMIFS(P$5:P$22,$K$5:$K$22,$B$2,$L$5:$L$22,$C$2,$M$5:$M$22,$D$2,$N$5:$N$22,$E$2,$O$5:$O$22,IFERROR((INDEX($O:$O,AGGREGATE(15,6,ROW($K$5:$K$22)/(MATCH($B$2&$C$2&$D$2&$E$2&$O$5:$O$22,$K$5:$K$22&$L$5:$L$22&$M$5:$M$22&$N$5:$N$22&$O$5:$O$22,0)=ROW($K$5:$K$22)-ROW($K$5)+1),ROWS(H$6:H6)))),""))


B6:Dxx
=IF($H6=0,"",IF(LOOKUP($H6,{0,60,90},{3,2,1})=COLUMNS($B6:B6),$H6,""))


E6:Gxx
=IF($I6=0,"",IF(LOOKUP($I6,{0,60,90},{3,2,1})=COLUMNS($E6:E6),$I6,""))

Norkaz

ได้ตามต้องการครับ ขอบคุณครับ

จากสูตรถ้าได้

=IFERROR(YEAR(INDEX($O:$O,AGGREGATE(15,6,ROW($K$5:$K$22)/(MATCH($B$2&$C$2&$D$2&$E$2&$O$5:$O$22,$K$5:$K$22&$L$5:$L$22&$M$5:$M$22&$N$5:$N$22&$O$5:$O$22,0)=ROW($K$5:$K$22)-ROW($K$5)+1),ROWS(A$6:A6)))),"")

=SUMIFS(P$5:P$22,$K$5:$K$22,$B$2,$L$5:$L$22,$C$2,$M$5:$M$22,$D$2,$N$5:$N$22,$E$2,$O$5:$O$22,IFERROR((INDEX($O:$O,AGGREGATE(15,6,ROW($K$5:$K$22)/(MATCH($B$2&$C$2&$D$2&$E$2&$O$5:$O$22,$K$5:$K$22&$L$5:$L$22&$M$5:$M$22&$N$5:$N$22&$O$5:$O$22,0)=ROW($K$5:$K$22)-ROW($K$5)+1),ROWS(H$6:H6)))),""))

INDEX($O:$O, กำหนดช่วงข้อมูลได้หรือไม่ครับ ขอบคุณครับ

...

กำหนดช่วงได้ ขึ้นกับจะกำหนด ณ. จุดใด

ถ้าแบบเดิม ก็เริ่มที่ O1
INDEX(O1:Oxx)

์Norkaz

norkaz wrote: Wed Mar 16, 2022 5:01 pm ...

กำหนดช่วงได้ ขึ้นกับจะกำหนด ณ. จุดใด

ถ้าแบบเดิม ก็เริ่มที่ O1
INDEX(O1:Oxx)

Norkaz

ขอบคุณครับ