สูตร Google Sheets(Excel) มันยาก หรือเราทำให้ลำบากเอง

Cover image

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

วิธีสำรวจวิธีออกแบบตารางในวันนี้ก็ไม่ได้ซับซ้อนอะไรมากมายเลย มีแค่ไม่กี่ข้อเท่านั้นเองเดี๋ยวเรามาลองดูกันครับ

คุณเคยทำแบบนี้หรือไม่?

1. วางชุดข้อมูลต่อกัน column แทนที่จะวางต่อกันใน row

คุณทำแบบนี้หรือไม่ 1

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

2. แยกตารางโดยไม่จำเป็น

คุณทำแบบนี้หรือไม่ 2

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

3. รวมข้อมูลที่จำเป็นต้องคิดแยกกันใน cell เดียวกัน

คุณทำแบบนี้หรือไม่ 3

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

4. ใส่ข้อมูลคนละประเภทในชุดข้อมูลเดียวกัน

คุณทำแบบนี้หรือไม่ 4

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

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

ตัวอย่างที่ 1 ร้านค้า สาขา และยอดขาย

ตัวอย่างที่ 1-1

นี่คือตารางที่พนักงานคนนึงสร้างไว้ เนื่องจากร้านมีสินค้าไม่เยอะมาก และมีสาขาแค่ 4 สาขา พนักงานคนนี้เลยทำตารางเล็กๆมารวมกันไว้ใน sheet เดียว โดยหวังว่าการที่ดูง่ายแบบนี้คงทำให่้จัดการอะไรๆง่ายหน่อย หลังจากนั้นเขาก็เสนอให้ผู้บริหารแล้วได้รับ feedback ว่าให้เพิ่มเติมข้อมูลดังนี้

สิ่งที่ผู้บริหารอยากเห็น

  • สินค้าขายดีเรียงจากมากไปหาน้อย
  • สาขาที่ขายดีเรียงจากมากไปหาน้อย
  • ยอดขายรวมทุกสาขาในแต่ละเดือน

สูตรที่ใช้เพื่อให้ได้ข้อมูลที่ผู้บริหารต้องการ

  • สินค้าขายดีเรียงจากมากไปหาน้อย ก็ใช้ SUM() ยอดของแต่ละสาขา แล้วก็เอามาบวกกัน หลังจากนั้นก็ยัดลง {} แล้วสั่ง SORT() ก็จบแล้วยากตรงไหน (หรอ?)
=SORT({
    B5,SUM(C5:E5)+SUM(H5:J5)+SUM(C14:E14)+SUM(H14:J14);
    B6,SUM(C6:E6)+SUM(H6:J6)+SUM(C15:E15)+SUM(H15:J15);
    B7,SUM(C7:E7)+SUM(H7:J7)+SUM(C16:E16)+SUM(H16:J16);
    B8,SUM(C8:E8)+SUM(H8:J8)+SUM(C17:E17)+SUM(H17:J17);
    B9,SUM(C9:E9)+SUM(H9:J9)+SUM(C18:E18)+SUM(H18:J18)
},2,false)

ตัวอย่างที่ 1-2

  • สาขาที่ขายดีเรียงจากมากไปหาน้อย อันนี้ก็ง่ายขึ้นมาอีก SUM() ตาราง 4 ตารางแล้วยัดเข้า {} จัด SORT() ก็จบโธ่เอ้ย มันลำบากตรงไหน?
=SORT({
    B2,SUM(C5:E9);
    B11,SUM(C14:E18);
    G2,SUM(H5:J9);
    G11,SUM(H14:J18)
},2,false)

ตัวอย่างที่ 1-3

  • ยอดขายรวมทุกสาขาในแต่ละเดือน คล้ายๆข้อแรกแหละ แค่เปลี่ยนมาจิ้มเดือนแทนที่จะเป็นสินค้า มีแค่ 3 เดือนไม่ทันตาลายหรอก
=SORT({
    C4,SUM(C5:C9)+SUM(C14:C18)+SUM(H5:H9)+SUM(H14:H18);
    D4,SUM(D5:D9)+SUM(D14:D18)+SUM(I5:I9)+SUM(I14:I18);
    E4,SUM(E5:E9)+SUM(E14:E18)+SUM(J5:J9)+SUM(J14:J18)
},2,false)

ตัวอย่างที่ 1-4

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

จะดีกว่าไหมถ้าเปลี่ยนมาเก็บข้อมูลแบบนี้

ตัวอย่างที่ 1-5

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

มาดูสูตรที่ใช้กับตารางนี้กัน

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

  • สินค้าขายดีเรียงจากมากไปหาน้อย ผมสั่งเลือก column B ซึ่งก็คือ "สินค้า" และ SUM(C) คือ "ผลรวมของยอดขาย" มาแสดงโดยจัดกลุ่มมันด้วยชื่อสินค้า group by B แล้วก็เรียงจากมากไปหาน้อย order by SUM(C) จากช่วงข้อมูล A1:D เห็นไหมว่ามันสั้นลงเยอะ
=QUERY(A1:D,"select B, SUM(C) group by B order by SUM(C) desc")
  • สาขาที่ขายดีเรียงจากมากไปหาน้อย คล้ายกับสินค้าขายดีเลยครับ แค่เปลี่ยนมาแสดง A และ group by A เพราะเราพิจารณาเป็นสาขา ก็จะได้คำตอบแล้ว
=QUERY(A1:D,"select A, SUM(C) group by A order by SUM(C) desc")
  • ยอดขายรวมทุกสาขาในแต่ละเดือน เช่นกันกับ 2 ข้อที่ผ่านมาครับ เปลี่ยนมาพิจารณา D ซึ่งคือเดือนนั่นเอง
=QUERY(A1:D,"select D, SUM(C) group by D order by SUM(C) desc")

ตัวอย่างที่ 1-6

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

ตัวอย่างที่ 2 พนักงาน วันที่และเวลาเข้างาน

ตัวอย่างที่ 2-1

นายจ้างคนหนึ่งกรอกเวลาเข้า-ออกงานในแต่ละกะของลูกจ้างจำนวน 5 คน เป็นเวลา 6 วัน โดยหวังว่าจะนำข้อมูลที่ได้มาหาตัวเลขดังนี้

ข้อมูลที่นายจ้างอยากได้

  • เวลาของแต่ละคนในแต่ละวัน
  • พนักงานคนไหนขาดงานบ้างในสัปดาห์นี้
  • พนักงานแต่ละคนทำงานกี่วันในสัปดาห์นี้
  • แต่ละวันมีคนมาทำงานกี่คน

สูตรที่ใช้เพื่อให้ได้ข้อมูลที่นายจ้างต้องการ

  • เวลาของแต่ละคนในแต่ละวัน เนื่องจากการเก็บข้อมูลเวลาเข้าออกในแต่ละกะไว้ cell เดียวกันต้องแยกกันออกมาด้วยคำสั่ง =SPLIT(B2, " - ") โดยใช้ขีดคั่นเวลาเป็นตัวแบ่ง แล้วค่อยเอาเวลาที่ แยกได้มาลบกันแล้วค่อยเอาผลต่างกะ "เช้า" และ "บ่าย" มารวมกันเพื่อให้ได้เวลาทำงานทั้งวัน แล้วก็ทำอย่างนี้วนไปเรื่อยๆทีละวัน แปลว่าเราต้องเพิ่มพื้นที่ column ออกไปเรื่อยๆเห็นท่าไม่ดี ผมเลยแยกเป็นตารางเล็กๆออกมาดีกว่า
=ARRAYFORMULA({SPLIT(B4:B8," - "),SPLIT(C4:C8," - ")})

ตัวอย่างที่ 2-2

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

จะดีกว่าไหมถ้าทำแบบนี้

ตัวอย่างที่ 2-3

  • ย้ายวันที่จากที่อยู่หัว column มาอยู่ใน row แทน
  • แยกเวลาเข้าออกของแต่ละกะไว้คนละ cell
  • วันไหนขาดก็เว้นว่างไว้เลยไม่ต้องเติมคำว่า "ขาด" เพราะข้อมูลชุดนี้ใส่แค่เวลาอย่างเดียวเวลาคำนวณจะได้ง่าย

สูตรที่ใช้เพื่อให้ได้ข้อมูลที่นายจ้างต้องการ

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

=ARRAYFORMULA((D2:D-C2:C)+(F2:F-E2:E))

ตัวอย่างที่ 2-4

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

=ARRAYFORMULA({HOUR(G2:G),MINUTE(G2:G)})

ตัวอย่างที่ 2-5

หลังจากนั้นก็เอา ชั่วโมง คูณ 60 แล้วบวกกับ นาที ก็จะได้เวลารวมในแต่ละวันพร้อมใช้งาน จะเห็นว่าคนที่ขาดงานเวลาก็จะเป็น 0 เองอยู่แล้ว

=ARRAYFORMULA((H2:H*60)+I2:I)

ตัวอย่างที่ 2-6

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

หลังจากได้เวลาสุทธิมาแล้วที่เหลือสูตร =QUERY ตัวเดียวก็เอาอยู่แล้วครับ

  • เวลาของแต่ละคนในแต่ละวัน อยากได้เวลารวมเราก็ SUM(J) แล้ว group by A เพราะเราต้องการมองเป็นรายบุคคลนั่นเองครับ
=QUERY(A1:J31, "select A, SUM(J) group by A order by SUM(J) desc label SUM(J) 'เวลารวม(นาที)'")

ตัวอย่างที่ 2-7

  • พนักงานคนไหนขาดงานบ้างในสัปดาห์นี้ ใครขาดงานดูง่ายๆด้วยการ นับ COUNT เอาแถวที่ J = 0 นั่นเองครับ เราดูเป็นรายบุคคลดังนั้นอย่าลืม group by A
=QUERY(A1:J31, "select A, COUNT(J) where J = 0 group by A label COUNT(J) 'วันขาดงาน'")

ตัวอย่างที่ 2-8

  • พนักงานแต่ละคนทำงานกี่วันในสัปดาห์นี้ เมื่อกี้ขาดงาน J = 0 ถ้ามาทำงานก็ต้อง J > 0 ใช่ไหมครับ
=QUERY(A1:J31, "select A, COUNT(J) where J > 0 group by A label COUNT(J) 'วันมาทำงาน'")

ตัวอย่างที่ 2-9

  • แต่ละวันมีคนมาทำงานกี่คน นับแถวที่ J > 0 โดยเปลี่ยนมุมมองจากบุคคลมาเป็นรายวันก็ group by B แทนครับ
=QUERY(A1:J31, "select B, COUNT(A) where J > 0 group by B label COUNT(A) 'จำนวนคนเข้างาน'")

ตัวอย่างที่ 2-10

สรุปง่ายๆว่า

ตารางเก็บข้อมูลที่ดีควรเก็บลงไปในแนวดิ่ง โดยมีคุณสมบัติของชุดข้อมูลถูกวางในแนวขวาง ดังรูปครับ

ตัวอย่างที่ 2-11

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

ตัวอย่าง Sheets จากบทความ

Google Sheets - สูตรมันยากหรือทำลำบากเอง

บทความใกล้เคียง