คำนวณค่าแรงพนักงาน ลูกจ้างรายวัน พร้อมบันทึกและทำสรุปใน Google Sheets

Table of Contents

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

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

วางโครงสร้างข้อมูล #

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

ตั้งค่าเวลา #

เก็บการวิธีการนับเวลาของเราครับ เช่น ของผม 1 วัน เริ่มงาน 8:00 ถึง 17:00 น. พักตอนเที่ยง จะคิดเวลาเป็น 8 ชั่วโมงหรือ 480 นาที ส่วนล่วงเวลา เริ่มตั้งแต่ 18:00 ถึง 22:00 น. เป็น 4 ชั่วโมงหรือ 240 นาทีครับ

รายชื่อพนักงาน #

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

=ARRAYFORMULA(IF(A2:A<>"",C2:C&" "&D2:D,""))

ประวัติเวลาทำงาน #

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

ค่าแรงปกติ = (หน่วยแรงงาน x ค่าแรงปกติ)

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

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

ส่วนรับและแสดงผลข้อมูล #

ได้โครงสร้างข้อมูลเรียบร้อยแล้ว ต่อไปก็ต้องเตรียมการรับ input และแสดงผลหรือสรุปข้อมูลกัน มี 3 sheets ดังนี้ครับ

ตารางคำนวณ #

sheet นี้จะเต็มไปด้วย column ถี่ยิบๆและสูตรหลายจุด แต่ว่าไม่ต้องกังวลครับเดี๋ยวผมจะแยก sheet นี้มาอธิบายทีละส่วนกันอีกที

ข้อมูลลูกจ้าง #

cell A1 เราจะเอาไว้เลือกลูกจ้างคนที่เราจะคำนวณและบันทึกข้อมูลครับ เราก็จะไปดึงรายชื่อจาก sheet “รายชื่อลูกจ้าง” มา โดยคลิกขวาที่ A1 เลือก Data validation แล้วเลือก Criteria เป็น “List from a range” แล้วเลือกเอา column G จาก “รายชื่อลูกจ้าง” มาทั้งหมดครับ หรือจะใช้คำสั่งนี้เลยก็ได้ 'รายชื่อลูกจ้าง'!G2:G เพียงเท่านี้เราก็จะได้ dropdown รายชื่อลูกจ้างแล้วครับ

cell A2 เราจะแสดงหมายเลขพนักงานครับ ตัวนี้มีความสำคัญในการใช้บันทึก ผมก็จะใช้คำสั่ง =QUERY() ดึง “หมายเลขพนักงาน” ที่อยู่ column A ใน “รายชื่อลูกจ้าง” มาแสดงครับ โดยให้เงื่อนไขว่า column G ต้องตรงกับ cell A1 ของเรา

=QUERY('รายชื่อลูกจ้าง'!A2:G, "select A where G = '"&A1&"'")

cell C1 ค่าแรงของลูกจ้างที่เลือก ใช้ =QUERY() เหมือนเดิมครับแต่รอบนี้ค่าแรงอยู่ column E ก็เปลี่ยนสูตรข้างบนแค่ตัวเดียว

=QUERY('รายชื่อลูกจ้าง'!A2:G, "select E where G = '"&A1&"'")

cell C2 ค่าแรงของลูกจ้างในช่วงล่วงเวลา อันนี้แล้วแต่เลยครับว่าจะกำหนดแบบไหน อย่างในตัวอย่างนี้ผมก็จะใช้เป็น 1.5 เท่าของค่าแรงปกติครับ

=C1*1.5

input เวลา #

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

={
    "วันในสัปดาห์";
    ARRAYFORMULA(
        IF(
            A6:A36<>"",
            TEXT(A6:A36,"ddd"),
            ""
        )
    )
}

อธิบายสูตรด้านบนนะครับ #

วงเล็บปีกกา ครอบคือการสั่งให้ 1 cell สามารถสร้างข้อมูลได้หลาย row หรือ column นะครับ เช่นถ้าเราเขียนว่า ={1,2,3;4,5,6;7,8,9} คำสั่งนี้จะเป็นการบอกให้ sheet สร้างข้อมูลออกมา 3 rows, 3 columns โดยแต่ละแถวจะถูกขั้นด้วย ; ดูผลลัพธ์ในภาพได้เลยครับ เพื่อความเข้าใจ

=ARRAYFORMULA() ฟังก์ชันนี้ช่วยให้เราเขียนแค่แถวแรกแถวเดียว แต่ได้ผลลัพธ์ลงไปกี่แถวก็ได้ตามต้องการ สะดวกมากเราไม่ต้องมาคอยลากไปทีละ cell เพื่อให้สูตรมันตามไป เช่น ถ้าเราเขียนคำสั่ง =ARRAYFORMULA(A1:A3+B1:B3) ไว้ที่ C1 เราจะได้ค่าของการบวกกันของ A1 + B1 ที่ C1 แล้วก็ไล่ไปตามลำดับจน C3 เป็นต้น และในบทความนี้ของเราจะมี IF(A6:A<>"",TEXT(A6:A36,"ddd"),"") อยู่ด้านใน

=IF() อันนี้ใครใช้ MS Excel มาคงเคยผ่านหูผ่านตามาบ้าง มันคือการกำหนดเงื่อนไขในการใส่ค่าต่างๆของ cell นั้นๆ เช่น ถ้า =IF(A1>0, "ผ่าน", "ไม่ผ่าน") แปลว่า A1 มากกว่า 0 ให้ใส่แสดงคำว่า “ผ่าน” ถ้าไม่ใช่ก็แสดงคำว่า “ไม่ผ่าน” เมื่อรวมร่างกับ ARRAYFORMULA มันก็จะทำให้เราเขียน IF แค่บรรทัดเดียว แล้วคลุมไปทั้ง Sheet ก็ยังได้ เช่น =ARRAYFORMULA(IF(A1:A > 0,"ผ่าน","ไม่ผ่าน")) ในคำสั่งของผมจะเห็นว่าเงื่อนไขผมคือ A6:A<>"" อันนี้แปลว่า column A ต้องไม่ว่างเปล่านะ ต้องมีค่านะ ถึงจะใส่ค่าลงไป
=TEXT() คำสั่งจัด format ของ text ใน cell บทความนี้จะเป็น TEXT(A6:A36,"ddd") ก็คือแสดงชื่อวันแบบย่อๆครับ ประโยชน์คือตอนที่บันทึกเราจะได้ไม่ต้องมาเขียนคำสั่งซับซ้อน

เรียบร้อยแล้ว column B ของเราก็จะเป็นแบบนี้ครับ

คำนวณเวลา #

ตั้งแต่ column I ถึง N คือการพยายามหาเวลาทำงานในแต่ละกะที่กรอกไปใน A ถึง H โดยจะได้ออกมาเป็น “ชั่วโมง:นาที” ก่อนแล้วจึงแปลงเป็น “นาที” ล้วนๆ วิธีหาผมก็จะใช้สูตรดังนี้ครับ

ชั่วโมง:นาที ที่ cell I5 ใต้คำว่า “เช้า” ผมจะใช้สูตร

={
    "ชั่วโมง:นาที";
    ARRAYFORMULA(
        IF(
            A6:A36<>"",
            D6:D36-C6:C36,
            ""
        )
    )
}

คล้ายกับสูตรด้านบนครับต่างกันตรงที่ใน IF() การแสดงผลรอบนี้ผมใช้ D6:D36-C6:C36 ใครดูตามที่ตารางจะเห็นว่า column D ก็คือเวลาออก ส่วน C ก็คือเวลาเข้าของกะเช้าครับ เอามาลบกันก็จะได้

เวลาทำงานกะเช้า = เวลาที่ออก - เวลาที่เข้า

นาที ที่ cell J5 ใต่คำว่าเช้า ผมก็จะใช้สูตร

={
    "นาที";
    ARRAYFORMULA(
        IF(
            A6:A36<>"",
            (HOUR(I6:I36)*60)+MINUTE(I6:I36),
            ""
        )
    )
}

คำสั่ง HOUR() คือการดึงเอาเฉพาะชั่วโมงของ column I ซึ่งเป็น “ชั่วโมงและนาที” ออกมาครับ ซึ่งผมอยากได้เป็นนาที ผมก็เอาไปคูณ 60 นาที = ชั่วโมง x 60 หลังจากนั้นผมก็จะดึงเอานาทีจาก I มาบวกตรงๆด้วย MINUTE() สุดท้ายก็จะได้สูตร (HOUR(I6:I36)*60)+MINUTE(I6:I36) ที่อยู่ใน IF() นั่นเอง

ที่เห็นคือเฉพาะกะเช้านะครับ กะบ่าย และล่วงเวลาก็ใช้สูตรเดิมครับเปลี่ยนแค่ column ที่จะเอามาบวกหรือคูณกันเท่านั้นเอง ถ้าเรียบร้อยแล้วก็จะได้หน้าตาข้อมูลประมาณนี้

ใครที่ได้ตัวเลขแปลกๆ ให้ลองครอบแต่ละ column เอาไว้แล้ว ไปที่เมนู Format > Number > Duration ก็จะได้หน้าตาข้อมูลที่ถูกต้องนะครับ

คำนวณหน่วยของแรง #

ตั้งแต่ column O ถึง P คือการดึงเอานาทีที่ได้มาคำนวณหาหน่วยของแรงที่ได้ใน 1 วันนั้น ซึ่งจะมาหารกด้วยจำนวนนาทีที่ตั้งเอาไว้ใน “ตั้งค่าเวลา” เช่น 1 วันคือ 480 นาที ดังนั้นถ้าตามตารางของผมคือ

หน่วยของแรงปกติ = (นาทีกะเช้า + นาทีกะบ่าย)/480
หน่วยของแรงล่วงเวลา = นาทีล่วงเวลา/240

ดังนั้นสูตรที่ cell O5 คือ

={
    "หน่วยปกติ";
    ARRAYFORMULA(
        IF(
            A6:A36<>"",
            (J6:J36+L6:L36)/'ตั้งค่าเวลา'!B3,
            ""
        )
    )
}

และ cell P5 คือ

={
    "หน่วย่วงเวลา";
    ARRAYFORMULA(
        IF(
            A6:A36<>"",
            N6:N36/'ตั้งค่าเวลา'!B4,
            ""
        )
    )
}

เรียบร้อยจะได้ตามภาพครับ เพื่อความสวยงามอาจจะปรับตัวทศนิยมได้ที่เมนูนะครับ

คำนวณค่าแรง #

column Q คือการรวมเอาค่าแรงธรรมดาคูณด้วยหน่วยของเวลาปกติ มาบวกกับค่าแรงล่วงเวลาที่คูณกับหน่วยล่วงเวลา จะทำให้ได้ค่าแรงของวันนั้นๆ โดยใช้คำสั่งนี้ที่ Q5

={
    "เป็นเงิน";
    ARRAYFORMULA(
        IF(
            A6:A36<>"",
            ROUNDUP(O6:O36*$C$1 + P6:P36*$C$2),
            ""
        )
    )
}

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

ด้านบนก็จะมีผลรวมทั้งหมดให้เช่นกัน ก็ใช้คำสั่งง่ายๆอย่าง =SUM(Q6:Q36) เพื่อความค่าแรงในตารางออกมานั่นเอง

รอบันทึก #

เนื่องจากเราพยายามเลี่ยงการเขียน script หรือ code ผมก็จะแยกตารางออกมาจากหน้าคำนวณเป็นชุดข้อมูลที่เตรียมจะบันทึกลงในประวัติ ตรงนี้ใช้คำสั่ง =QUERY() ธรรมดาๆ เลือกแถวที่ต้องการมาแสดงได้เลย ส่วนด้านหน้าสุดก็จะดึงเอาหมายเลขพนักงานจากด้านบนของตารางคำนวณมาใช้

=QUERY(
    'ตารางคำนวณ'!A6:Q36,
    "select A,B,J,L,N,O,P,Q"
)

column A,B,J,L,N,O,P,Q ก็คือข้อมูลที่เราต้องการบันทึกจาก ตารางคำนวณ ครับ ซึ่งมันก็คือ วันที่, วันในสัปดาห์, นาทีเช้า, นาทีบ่าย, นาทีล่วงเวลา, หน่วยปกติ, หน่วยล่วงเวลา, ค่าแรง

ส่วนช่องแรกด้านหน้าคือ หมายเลขพนักงาน เราก็ดึงมาจาก cell A2 ของ ตารางคำนวณ ได้เลย แต่จะดึงมาแบบทีเดียวก็ต้องพึ่ง ARRAYFORMULA() แบบนี้ครับ

=ARRAYFORMULA(
    IF(
        B2:B<>"",
        'ตารางคำนวณ'!A2,
        ""
    )
)

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

การบันทึกข้อมูล #

จริงๆจะ copy แล้วเอาไปวางเองก็ได้นะครับ เพื่อความสะดวกขึ้นมาอีกนิดเราจะใช้ macro มาช่วยเรา copy ข้อมูลที่ รอบันทึก ไปวางที่ ประวัติเวลาทำงาน จริงๆจะให้สมบูรณ์วิธีนี้ต้องเขียน code ครับ แต่ Google Sheets เองก็มีเครื่องมือมาช่วยให้เราไม่ต้องไปเขียนมันทั้งหมดก็ได้นั่นก็คือ Macro ทำตามผมดังนี้นะครับ

ไปที่เมนู Tool > Macro > Record Macro เสร็จแล้วจะมีหน้าต่างเล็กๆขึ้นมาด้านล่าง

เลือกเป็น “Use relative reference” นะครับ เพราะเราต้องการให้ผลลัพธ์มันวางลงไปใน cell ที่เราเลือก ซึ่งปกติก็เป็นการวางต่อท้ายไปเรื่อยๆนั่นเอง

เปิด sheet รอบันทึก แล้วคลิกที่ A2 แล้วคลุมยาวไปจนเต็มทั้งตาราง

กลับมาคลิกที่ cell A2 ของ ประวัติเวลาทำงาน หนึ่งครั้งพร้อมกับ คลิกขวาเลือก Paste special > Paste values only เพราะเราต้องการเอาแต่ค่าสำเร็จจากตาราง รอบันทึก มาวางเลย ไม่ต้องการให้มีสูตรติดมาด้วย

กด Save ที่หน้าต่างตัว record ด้านล่าง จะมี Popup ขึ้นมาบอกให้เราตั้งชื่อ Macro นี้ ใครอยากตั้ง shortcut key ก็ทำได้นะครับ กด Save อีกครั้งเพื่อเป็นการเสร็จสิ้น

วิธีการใช้งาน macro ก็คือหลังจากเรากรอกข้อมูลเวลาของลูกจ้างใน ตารางคำนวณ เสร็จแล้ว ได้ค่าแรงทุกอย่างถูกต้องแล้ว เราก็จะมาเปิดที่ ประวัติเวลาทำงาน คลิกเลือก column A ที่เป็นแถวเปล่าต่อจากข้อมูลชุดสุดท้าย แล้วเปิดเมนู Tool > Macro > “ชื่อที่คุณตั้ง” ไม่กี่อึดใจข้อมูลชุดใหม่ก็จะถูกนำมาวางตรงที่ๆคุณเลือกไว้อย่างเรียบร้อยและสวยงาม

สรุป #

เมื่อบันทึกแล้วข้อมูลเรียบร้อยแล้ว ไม่ว่าจะใช้ macro หรือจะ copy มาวางเอง ต่อไปนี้เราก็สามารถจะ query ค่ามาแสดงได้สะดวกเลยครับ อย่างในตัวอย่างผมกำหนดช่วงเวลาเอาไว้เป็นเดือนสิงหาคมนะครับ เริ่มต้นที่ cell B1 แล้วสิ้นสุดที่ C1 หลังจากนั้นก็ให้คำสั่ง =QUERY() ทำการ SUM(I) จาก ประวัติเวลาทำงาน โดยมีเงื่อนไขว่า column B ของ ประวัติเวลาทำงาน อยู่ในช่วงเวลาที่กำหนด

where B >= date '2019-08-01' and B <= date '2019-08-31'

โดยหลังจาก SUM() แล้วให้จัดกลุ่มข้อมูลออกมาตาม หมายเลขพนักงาน

group by A

แล้วมันก็จะรวมเป็นสูตรหน้าตาแบบนี้ที่ cell A3 ครับ

=QUERY(
    'ประวัติเวลาทำงาน'!A1:I,
    "select A,SUM(I) where B >= date '"&TEXT(B1, "YYYY-MM-DD")&"' and B <= date '"&TEXT(C1, "YYYY-MM-DD")&"' group by A"
)

จากคำสั่งเมื่อกี้เราจะได้แค่ หมายเลขพนักงาน และ ค่าแรงรวมในช่วงเวลาที่กำหนด ถ้าเราอยากรู้ชื่อของพนักงานด้วยก็ใช้ VLOOKUP() ต่อได้ครับที่ cell C3 ครับ

={
    "ชื่อพนักงาน";
    ARRAYFORMULA(
        IF(
            A4:A<>"",
            VLOOKUP(A4:A,'รายชื่อลูกจ้าง'!A2:G,7,false),
            ""
        )
    )
}

แล้วหน้าตาก็จะออกมาประมาณนี้ครับ

ใครอยากเห็นของจริงดูที่ตัวอย่าง sheet ได้เลยครับผมแนบ link ไว้ให้แล้ว สามารถเข้าไปดูสูตรหรือจะ Make a copy เพื่อไปดัดแปลงหรือใช้ต่อเลยก็ได้เช่นกันนะครับ

ตัวอย่าง Sheets #

คำนวณค่าแรงรายวัน - Google Sheets

ถึงบทความนี้จะมีรายละเอียดใน sheet ค่อนข้างเยอะ แต่ผมเชื่อว่ามันจะคุ้มค่ากับเวลาที่เราจะได้กลับคืนมาจากการที่ต้องคอยคิดค่าแรงด้วยมือและเครื่องคิดเลขในทุกๆสัปดาห์หรือทุกๆเดือน ใครมีคำถามเพิ่มเติมหรือทำไปแล้วติดปัญหา Inbox เข้ามาได้ใน Facebook Page Peerasak เลยนะครับ จะพยายามตอบให้ทุกคำถามครับแต่ถ้าของใครมีรายละเอียดเยอะ ผมอาจจะต้องขอยกมาเขียนเป็นบทความอีกทีหนึ่งนะครับ