การใช้สูตร Countif เพื่อสรุปข้อมูลในสถิติการมาทำงานค่ะ

[catch]

รบกวนทุก ๆ ท่านค่ะ
จากตารางที่แนบ
1. ต้องการนำข้อมูลใน Sheet สถิติการมาทำงาน ไปสรุปจำนวนตามเงื่อนไขที่กำหนดเป็นเดือนๆ ลงใน Sheet "สรุปสถิติประจำเดือน" โดยอ้างอิงข้อมูลจากรหัสพนักงานค่ะ เหตุผลที่ต้องอ้างอิงจากพนักงานเพราะจะมีคนเข้า-ออก อาจลบ หรือแทรกแถวเกิดขึ้นใน Sheet สถิติการมาทำงาน ค่ะ
เช่น ในช่อง B4 ต้องการค่า "/" เป็นผลรวมของรหัสพนักงาน 43263 ค่ะ
ในช่อง C4 ต้องการค่า "/O" เป็นผลรวมของรหัสพนักงาน 43263 ค่ะ
2. กรณีคล้ายกับข้อ 1 แต่หาเป็นช่วงวันที่ ตามคอลัมน์ที่ B2 กำหนดไว้ค่ะ

***Sheet สถิติการมาทำงาน เป็นเหมือนฐานข้อมูลที่ต้องกรอกทุกวันค่ะ แล้วให้ส่งค่ากลับอัตโนมัติไปที่ Sheet สรุปสถิติประจำเดือน และ Sheet สรุปสิถิติประจำงวด

[XDoll]

ลองดูไฟล์ตัวอย่างครับ
หากตรงความต้องการ จะมาอธิบายภายหลังครับ

[XDoll]

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

ที่ B4 =COUNTIFS(OFFSET(สถิติการมาทำงาน!$A$3,MATCH(สรุปสถิติประจำงวด!$A4,สถิติการมาทำงาน!$A$4:$A$87,0),1,,375),IF(B$3="",">0",B$3),สถิติการมาทำงาน!$B$3:$NL$3,">="&สรุปสถิติประจำงวด!$B$2,สถิติการมาทำงาน!$B$3:$NL$3,"<="&สรุปสถิติประจำงวด!$D$2) คอปปี้ไปถึง column AE และล่งล่างครับ

ที่ AG4 =COUNTIFS(OFFSET(สถิติการมาทำงาน!$A$3,MATCH(สรุปสถิติประจำงวด!$A4,สถิติการมาทำงาน!$A$4:$A$87,0),1,,375),IF(AG$3="",">0",AG$3),สถิติการมาทำงาน!$B$3:$NL$3,">="&สรุปสถิติประจำงวด!$AG$2,สถิติการมาทำงาน!$B$3:$NL$3,"<="&สรุปสถิติประจำงวด!$AI$2) คอปปี้ไปถึง column BJ และล่งล่างครับ

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

[catch]

สุดยอดเลยค่ะ
ซึ้งน้ำตาไหล ขอบคุณมาก ๆ ค่ะ ใช้อย่างที่ต้องการค่ะ
ไม่ค่อยมีความรู้ค่ะ ทำได้แต่สูตรง่าย ๆ ที่ไม่ซับซ้อน
แล้วกรณีไม่มีค่าในสัญลักษณ์นั้น ๆ ไม่ให้แสดงเป็น 0 แต่ให้เป็น Blank ต้องใส่เพิ่มตรงไหนค่ะ

คำถามส่วนที่สงสัยเพิ่มเติมค่ะ ค่าที่ฉันทำเป็นสีแดง มีความหมายว่าอย่างไรค่ะ
=COUNTIFS(OFFSET(สถิติการมาทำงาน!$A$3,MATCH(สรุปสถิติประจำงวด!$A4,สถิติการมาทำงาน!$A$4:$A$87,0),1,,375),B$3,สถิติการมาทำงาน!$B$3:$NL$3,">="&สรุปสถิติประจำงวด!$B$2,สถิติการมาทำงาน!$B$3:$NL$3,"<="&สรุปสถิติประจำงวด!$D$2

ขอขอบพระคุณอย่างสูงค่ะ ที่ให้ความช่วยเหลือ และยังให้ความรู้เพิ่มเติมด้วยค่ะ

[catch]

ใช่ค่ะ ต้องขอโทษด้วยค่ะ
ทำสูตรให้แล้ว ยังรอบคอบตรวจสอบเพิ่มเติมให้อีกด้วยนะคะ ใจดีจริง ๆ ค่ะ
ใส่เป็นตัวเลขตลอดค่ะ
ตอนแรกว่าจะใช้ Sumif ค่ะ

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

[XDoll]

สุดยอดเลยค่ะ
ซึ้งน้ำตาไหล ขอบคุณมาก ๆ ค่ะ ใช้อย่างที่ต้องการค่ะ
ไม่ค่อยมีความรู้ค่ะ ทำได้แต่สูตรง่าย ๆ ที่ไม่ซับซ้อน
แล้วกรณีไม่มีค่าในสัญลักษณ์นั้น ๆ ไม่ให้แสดงเป็น 0 แต่ให้เป็น Blank ต้องใส่เพิ่มตรงไหนค่ะ

คลุม ช่วงที่ต้องการ (เช่น B4:AE87) คลิ๊กขวา เลือก Format > Custom > ช่อง Type ใส่ เครื่องหมาย #

คำถามส่วนที่สงสัยเพิ่มเติมค่ะ ค่าที่ฉันทำเป็นสีแดง มีความหมายว่าอย่างไรค่ะ
=COUNTIFS(OFFSET(สถิติการมาทำงาน!$A$3,MATCH(สรุปสถิติประจำงวด!$A4,สถิติการมาทำงาน!$A$4:$A$87,0),1,,375),B$3,สถิติการมาทำงาน!$B$3:$NL$3,">="&สรุปสถิติประจำงวด!$B$2,สถิติการมาทำงาน!$B$3:$NL$3,"<="&สรุปสถิติประจำงวด!$D$2

ขอขอบพระคุณอย่างสูงค่ะ ที่ให้ความช่วยเหลือ และยังให้ความรู้เพิ่มเติมด้วยค่ะ

ค่า 0 เป็น Match type ส่วนหนึ่งของสูตร Match สูตรนี้จะให้ระบุค่าใกล้เคียง หรือ ตรงตัว หากให้หาค่าตรงตัวต้องระบุ 0 ค่าข้างเคียงระบุ1

ค่า 1 เป็นส่วนหนึ่งของสูตร offset ให้เริ่ม Column ถัดไป 1 Column จาก Cell Reference จากสูตรคือ Cell A4(ดูสูตรประกอบไปด้วยนะครับ)

ค่า 375 ได้มากจากการนับ Column จาก B ถึง NL ครับ ค่านี้มีความสัมพันธ์กับ $B$3:$NL$3 หากมีการเปลี่ยนแปลงตรงนี้ ค่า 375 ต้องเปลี่ยนไปด้วยเช่นกัน ยกตัวอย่างหาก แก้ไขสูตรนี้จาก ช่วง $B$3:$NL$3 เป็น $B$3:$D$3 ค่า 375 ต้องแก้เป็น เลข 3 พอจะนึกออกนะครับ

* พยายามเรียบเรียงคำพูดแล้วครับ หากอธิบายไม่เข้าใจ ต้องขออภัยด้วยครับ

[XDoll]

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

สีม่วง ใช่จำนวนนาทีใช่ไหมครับ

[catch]

OH My God!
สุดยอดมากจริง ๆ ค่ะ

อธิบายดีมากค่ะ เข้าใจค่ะ คือใช้คำแบบที่อ่านแล้วไม่สับสน ซับซ้อนเลยค่ะ
เพิ่มเติมจุด ,0),1,,375) ทำไม่ต้องมี ,, 2 อันด้วยค่ะ

[catch]

ใช่ค่ะ เป็นจำนวน นาทีค่ะ

[XDoll]

เดี๋ยวมาเพิ่มเติมให้นะครับ

[XDoll]

อธิบายดีมากค่ะ เข้าใจค่ะ คือใช้คำแบบที่อ่านแล้วไม่สับสน ซับซ้อนเลยค่ะ
เพิ่มเติมจุด ,0),1,,375) ทำไม่ต้องมี ,, 2 อันด้วยค่ะ

ไวยากรณ์ของ OFFSET(reference, rows, cols, [height], [width])
ระหว่าง ,, จริงๆแล้วหากไม่ละ จะเป็น ,1, แต่เนื่องด้วย สูตรนี้สามารถละได้ก็เลยไม่ใสไปครับ
ส่วนนี้ก็คือ [height] คือความกว้างของ ROW ในที่นี้เราต้องการแค่แถวเดียวครับ

*ผิดพลาดประการใดขอโทษด้วยนะครับ เป็นความเข้าใจส่วนตัว รออาจารย์หรือสมาชิกท่านอื่นมาเสริมนะครับ
ลองอ่านเพิ่มเติม https://support.office.com/th-TH/articl ... d906d11b66

[XDoll]

ให้คำนวณเป็นวันออกมาค่ะ หากไม่ถึงวัน ก็เป็นชั่วโมงย่อย ๆ ลงมาค่ะ วัน:ชั่วโมง:นาที พอทำได้ไหมค่ะ ต้องเพิ่มเติมแก้ไขฟอร์มไหมค่ะ

ลองสร้างรูปแบบ ระบุคำตอบที่ต้องการ และแนบไฟล์มาดูครับ

[catch]

ขอบคุณมาก ๆ นะคะ รบกวนมากจริง ๆ ค่ะ

[catch]

คือ ในไฟล์ Excel เดิมค่ะ ที่ยังไม่ได้หาผลรวมสายค่ะ
อ้างอิงตัวนี้ค่ะ

24-Mar-2016 11-54-45.png

ให้คำนวณเป็นวันออกมาค่ะ วัน:ชั่วโมง:นาที พอทำได้ไหมค่ะ ต้องเพิ่มเติมแก้ไขฟอร์มไหมค่ะ

ใส่สรุปลง Sheet สรุปสถิตประจำงวด ค่ะ

24-Mar-2016 11-51-19.png

รบกวนด้วยนะคะ
ลองพยายามเองแล้วจนปัญญาความสามารถค่ะ

[catch]

ขอโทษนะคะ รูปภาพสลับกันค่ะ

[XDoll]

ที่ B4 แก้สูตร เพื่อดักให้มีการรวมเวลาสาย ในช่วงเวลาที่กำหนดครับ

Code: Select all

=IF(B$3="",SUMPRODUCT(--(สถิติการมาทำงาน!$B$3:$NL$3<=สรุปสถิติประจำงวด!$D$2),--(สถิติการมาทำงาน!$B$3:$NL$3>=สรุปสถิติประจำงวด!$B$2),OFFSET(สถิติการมาทำงาน!$A$3,MATCH(สรุปสถิติประจำงวด!$A4,สถิติการมาทำงาน!$A$4:$A$87,0),1,,375)),COUNTIFS(OFFSET(สถิติการมาทำงาน!$A$3,MATCH(สรุปสถิติประจำงวด!$A4,สถิติการมาทำงาน!$A$4:$A$87,0),1,,375),B$3,สถิติการมาทำงาน!$B$3:$NL$3,">="&สรุปสถิติประจำงวด!$B$2,สถิติการมาทำงาน!$B$3:$NL$3,"<="&สรุปสถิติประจำงวด!$D$2))

คอปปี้ไปขวาและลงล่างครับ

[catch]

ขอบคุณค่ะ ได้แก้ไขตามที่ส่งมาให้แล้วค่ะ

แต่ว่าพอใส่แล้วในช่อง H4 มันเป็นผลรวมของ Count ไม่ทราบว่า รวมเป็นนาที และให้ผลรวมเป็นนาทีรวมเป็นจำนวน วัน:ชั่วโมง:นาทีอย่างไรค่ะ
รบกวนด้วยนะคะ

[XDoll]

ดูการแสดงผลในไฟล์แนบนะครับ
Sheet "เงื่อนไข" Column C แปลงเงื่อนไขการลาออกมาเป็นชม. DD hh:mm:ss (ยกเว้นสาย จะเป็นนาที) แต่เนื่องด้วยต้องแสดงผลเป็นวันเข้าไปใน Cell เดียวกัน จำเป็นต้องใช้ 24 ชม เป็นฐานการตัดรอบ 1 วัน (ในความเป็นจริง ทำงานแค่ 8 ชม/ว)

ลองดูครับพอจะใช้ได้ไหม

[catch]

ขอบคุณอีกครั้ง และอีกครั้งค่ะ กราบงาม ๆ ในความช่วยเหลือจนถึงที่สุดค่ะ

ใช้งานได้ค่ะ
แต่ใช้แค่แถว D เท่านั้นค่ะ ส่วนแถวอื่น ๆ ใช้กับสูตรเดิมๆ ที่คุณสร้างให้ก่อนหน้านี้ค่ะ

มีคำถามอยู่ 3 ข้อค่ะ
1. สูตร *INDEX(เงื่อนไข!$C$1:$C$34,MATCH(D$3,เงื่อนไข!$A$1:$A$34,0))) ในกรณีใช้เฉพาะแถว D ไม่ต้องกำหนดใช่ไหมค่ะ

2. สูตรใหม่ที่ทำให้ รบกวนอีกนิดได้ไหมค่ะ หากสรุปเหลือให้เป็นชั่วโมง:นาที ก็ได้ค่ะ เพราะมีผลกระทบตอนเอามารวมทั้งปีค่ะ (รายละเอียดเพิ่มเติมในข้อ 3ค่ะ) ขอโทษด้วยจริง ๆ นะคะ เพราะตามที่คุณชี้แจงว่า จำเป็นต้องใช้ 24 ชั่วโมง เป็นฐานการตัดรอบ 1 วัน แต่หลักต้องนับ 8 ชั่วโมงค่ะ

3. ได้แนบไฟล์ ที่นำสูตรของคุณไปใส่ในตาราง ตามค่าที่ต้องการเรียบร้อยแล้วค่ะ แต่มีส่วนเพิ่มเติมกรณีรวบรวมข้อมูลทั้งปีใน Sheet สรุปสถิติประจำงวด แถว BN ตอนนี้ในช่อง BN83 มีค่าให้ตรวจสอบ อยากให้ค่าเป็นตามรูปค่ะ

25-Mar-2016 13-48-22.png

ท้ายนี้ขอบคุณมาก ๆ เลยที่สละเวลาให้ค่ะ

[catch]

ขอโทษค่ะ อีกข้อค่ะ
เกรงใจจริง ๆ ค่ะ
กรณีแทรก Column หรือ Row มีผลอะไรกับสูตรไหมค่ะ