• เกริ่น

ผู้เขียนสอน Google Sheets ตั้งแต่ขั้นต้นไปจนถึงขั้นสูง และสิ่งที่สอนเป็นประจำก็คือ การวิเคราะห์ข้อมูล โดยใช้เครื่องมือต่างๆ เช่น ตัวกรอง, PIVOT Table และ ฟังก์ชั่นต่างๆ เช่น SUMIF(S), SUMPRODUCT, COUNTIF(S), SORT, FILTER, QUERY, VLOOKUP, XLOOKUP, ArrayFormula, LAMBDA, MAP, ... เป็นต้น ดูรายละเอียดเพิ่มเติม

เครื่องมือต่างๆข้างต้น มีข้อดีที่ ใช้สะดวก ทำงานรวดเร็ว(หากข้อมูลไม่มาก) โดยเฉพาะสูตรแบบอาเรย์ที่ทำงานได้ยอดเยี่ยมมาก

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

โปรเจกต์นี้ ผู้เขียนได้รวบรวมเทคนิคการวิเคราห์ะข้อมูล ด้วย Google Apps Script ซึ่งไม่ว่าจะวิเคราะห์ด้วยเครื่องมืออะไร สิ่งที่เราต้องทำก็จะซ้ำๆ เช่น การกรอง, การเรียงลำดับ, การหาผลรวม, การคำนวณเปอร์เซ็นต์, การหาผลรวมเป็นหมวดหมู่, การสร้างตาราง Pivot Table เป็นต้น

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

โปรดดูรายละเอียดโปรเจกต์ P0004 เว็บแอพรวมเทคนิควิเคราห์ะข้อมูล ประกอบการเพื่อตัดสินใจ

• ภาพรวม

• ระบบพัฒนาโดยใช้ Google Apps Script
• เก็บข้อมูลใน Google Sheets

• ข้อมูลตัวอย่าง

ข้อมูลตัวอย่างในโปรเจกต์นี้ เป็นข้อมูลการขายสินค้า

เมื่อขายสินค้าได้ 1 ออเดอร์ ข้อมูลจถูกบันทึกแยกลงใน 2 ชี้ท ก็คือ OMain และ ODetail ตามภาพ

OMain จะบันทึกข้อมูลสรุปของออเดอร์ เช่น รหัสลูกค้า, รหัสพนักงานขาย และ ยอดซื้อของออเดอร์ นั้นๆ

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

ชี้ท P เป็นข้อมูลของสินค้า สินค้ามี 2 ประเภทก็คือ แบบ single และแบบ set โดยแบบ set ประกอบด้วยสินค้าแบบ single หลายตัว แต่ละตัวมีได้หลายชิ้น

ชี้ท S เป็นข้อมูลของสินค้าแบบเซ็ต กรณีสินค้าในชี้ท P เป็นแบบ set สามารถมาดูได้ว่า สินค้าประกอบ หรือ สินค้าแบบ single ที่อยู่ในเซ็ตมีอะไรบ้าง มีจำนวนเท่าไร

ชี้ท E เป็นข้อมูลของพนักงาน เวลาบันทึกการขาย จะบันทึกรหัสพนักงานด้วย

ชี้ท Home ใช้สำหรับแสดงผลลัพธ์จากการวิเคราะห์ข้อมูล

ชี้ท C ใช้เก็บข้อมูลลูกค้า แต่ไม่ได้ใช้ในโปรเจกต์นี้ และเผื่อไว้ให้ทดสอบวิเคราะห์กันเอง

• ไลเซนส์

License : CC4.0 BY (https://creativecommons.org/licenses/by/4.0/deed.en)

หากนำไปพัฒนาต่อแล้วเผยแพร่สู่สาธารณะ จะต้องระบุที่มา

• ทดสอบใช้งานแบบขึ้นระบบเอง

ไฟล์ Google Sheets

เมื่อก๊อปปี้ไฟล์ออกไป โค้ดจะติดไปกับไฟล์ด้วย

โค้ดบางส่วนจะถูกเข้ารหัส ระบบจะใช้งานได้ แต่จะแก้ไขโค้ดได้ยาก

สำหรับท่านที่สั่งซื้อโปรเจกต์นี้ไป จะได้โค้ด Apps Script ที่ไม่มีการเข้ารหัส

• ตัวอย่างเมนูสำหรับการวิเคราะห์ข้อมูล

• เมนู 1. ค้นหาออเดอร์ (โปรดระบุเลขที่ออเดอร์)

• ข้อมูลออเดอร์นในชี้ท OMain และ ODetail ที่ตรงกับการค้นหาจะถูกนำมาแสดงที่ชี้ท Home
• เทคนิคที่ใช้ไม่ใช่การกรอง เพราะถ้าข้อมูลมีเป็นจำนวนมาก การกรองจะทำงานช้าหรือทำไม่ได้เลย เพราะติดข้อจำกัด
• เทคนิคที่ใช้คือการ Lookup ค้นหาที่คอลัมน์ "เลขที่ออเดอร์" ถ้าตรงกันก็ยกเฉพาะแถวที่พบมาซึ่ง ในชี้ท ODetail อาจพบได้หลายแถว แทคนิคนี้จะทำงานกับข้อมูลที่น้อยกว่าการกรองมหาศาล

• เมนู 2. กรองยอดขายในชี้ท OMain ที่มากกว่าหรือเท่ากับ (โปรดระบุตัวเลข)

• ใช้เทคนิคการกรองธรรมดา ก็คือ จับข้อมูลจากชี้ท OMain ทั้งหมดมา แล้วก็กรองตามเงื่อนไข

• เมนู 3. ยอดขายในชี้ท OMain รายวัน

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

• เมนู 4. ยอดขายในชี้ท OMain รายเดือน

• เหมือนกับข้อ 3. แต่เป็นการหาผลรวมยอดขาย รายเดือน

• เมนู 5. ยอดขายในชี้ท ODetail จำแนกตามเลขที่ออเดอร์

• เมนูนี้ มีไว้สำหรับการตรวจสอบข้อมูล ก็คือ ยอดรวมในแต่ละออเดอร์ในชี้ท ODetail จะต้องเท่ากับใน OMain
• คำนวณแบบ SUMIF โดยหาผลรวมยอดขาย ในแต่ละออเดอร์ตามที่บันทึกในชี้ท ODetail

• เมนู 6. กรองรหัสพนักงานที่ไม่ซ้ำจากชี้ท OMain

• เหมือนกับการใช้ฟังก์ชั่น UNIQUE เพื่อกรองตัวซ้ำให้เหลือแต่ตัวไม่ซ้ำ

• เมนู 7. กรองรหัสพนักงานที่ไม่ซ้ำจากชี้ท OMain และค้นหาข้อมูลพนักงานทั้งหมด

• เป็นขั้นตอนที่ต่อจากข้อ 6. หลังกรองรหัสพนักงานที่ไม่ซ้ำได้แล้ว จากนั้นจะส่งรหัสพนักงานไป Lookupหรือค้นหาข้อมูลพนักงานในชี้ท E แล้วส่งข้อมูลที่ตรงกันกลับมา

• เมนู 8. ค้นหาพนักงาน ตามที่ใช้เม้าส์เลือกรหัสพนักงาน

• คล้ายกับข้อ 7. แต่ครั้งนี้ เราใช้เม้าส์เลือกรหัสพนักงานก่อน จากนั้นจึงใช้คำสั่ง Lookup เพื่อตีกรอบ Lookup เฉพาะสิ่งที่เราต้องการเท่านั้น

• เมนู 9. ค้นหาพนักงาน ตามที่ใช้เม้าส์เลือกรหัสพนักงาน แบบระบุคอลัมน์

• คล้ายกับข้อ 8. เราใช้เม้าส์เลือกรหัสพนักงานก่อนเหมือนเดิม จากนั้นจึงใช้คำสั่ง Lookup ซึ่งจะมีหน้าต่างให้เรากรอกว่า ต้องการข้อมูลในคอลัมน์ใดบ้าง ซึ่งไม่จำเป็นต้องเอามาทั้งหมด

• เมนู 10. กรองรหัสสินค้า ในชี้ท ODetail (โปรดระบุรหัสสินค้า เช่น A001)

• เป็นการกรองข้อมูลในชี้ท ODetail ตามรหัสสินค้า ใช้เทคนิคการกรองธรรมดา

• เมนู 11. กรองรหัสสินค้า ในชี้ท ODetail (เลือกจาก Modal Dialog)

• คล้ายกับข้อ 10. เป็นการกรองข้อมูลในชี้ท ODetail ตามรหัสสินค้า แต่ครั้งนี้เราจะใช้การเลือกจาก Modal Dialog ไม่ต้องพิมพ์รหัสสินค้าเอง

• เมนู 12. กรองรหัสสินค้า ในชี้ท ODetail (เลือกจาก Sidebar)

• คล้ายกับข้อ 11. เป็นการกรองข้อมูลในชี้ท ODetail ตามรหัสสินค้า แต่ครั้งนี้เราจะใช้การเลือกจาก Sidebar ไม่ต้องพิมพ์รหัสสินค้าเอง

• เมนู 14. คำนวณยอดขายสินค้าในชี้ท ODetail จำแนกตามรหัสสินค้า

• เมนูนี้คำนวน2อย่าง ก็คือ จำนวนขาย และ ยอดขาย จำแนกตามรหัสสินค้า

• เมนู 15. คำนวณยอดขายสินค้าในชี้ท ODetail จำแนกตามรหัสสินค้า และค้นหาสินค้า

• คล้ายกับข้อ 14. คำนวณ2อย่าง จำนวนขาย และ ยอดขาย จำแนกตามรหัสสินค้า แต่เพิ่มการ Lookup เพื่อดูว่ารหัสสินค้าแต่ละตัว มีราละเอียดอะไรบ้าง

• เมนู 16. สร้างตาราง Pivot Table จากชี้ท ODetail หาจำนวนขาย จำแนกตาม วันที่-รหัสสินค้า

• สร้างตาราง PIVOT table เพื่อคำนวณจำนวนขาย(ชิ้น/อัน) จำแนกตามวันที่-รหัสสินค้า ซึ่งมีการคำนวณแถวผลรวม คอลัมน์ผลรวม และแถวเปอร์เซ็นให้ด้วย
• เทคนิคการสร้างตาราง PIVOT table คล้ายกับการใช้ เช่นใช้ฟังก์ชั่น UNIQUE และ SUMIFS หัวตารางสร้างจาก UNIQUE ภายในตารางที่มีการคำนวณ คำนวณด้วย SUMIFS
• การเชียน Apps Script เพื่อสร้าง PIVOT table อาจจะยากสักหน่อย แต่ว่ายากเพียงครั้งเดียว เมื่อเราเขียนได้แล้ว เราก็รียูสโค้ดมาใช้ได้ตลอด

• เมนู 17. สร้างตาราง Pivot Table จากชี้ท ODetail หายอดขาย จำแนกตาม วันที่-รหัสสินค้า

• คลายกับข้อ 16. แต่ครั้งนี้ สร้างตาราง PIVOT table เพื่อคำนวณยอดขาย(บาท) จำแนกตามวันที่-รหัสสินค้า

• เมนู 18. สร้างตาราง Pivot Table จากชี้ท ODetail หาจำนวนขาย จำแนกตาม เดือน-รหัสสินค้า

• คลายกับข้อ 16. แต่ครั้งนี้ สร้างตาราง PIVOT table เพื่อคำนวณจำนวน(ชิ้น/อัน) รวมกลุ่มตามเดือน-รหัสสินค้า

• เมนู 19. สร้างตาราง Pivot Table จากชี้ท ODetail หายอดขาย จำแนกตาม เดือน-รหัสสินค้า

• คลายกับข้อ 17. แต่ครั้งนี้ สร้างตาราง PIVOT table เพื่อคำนวณยอดขาย(บาท) รวมกลุ่มตามเดือน-รหัสสินค้า

• เมนู 20. สร้างตาราง Pivot Table จากชี้ท ODetail หาจำนวนขายและยอดขาย จำแนกตาม เดือน-รหัสสินค้า

• สร้างตาราง PIVOT table เพื่อคำนวณจำนวนขาย(ชิ้น/อัน) และคำนวณยอดขาย(บาท) โดยรวมกลุ่มตามเดือน-รหัสสินค้า พิเศษกว่าข้อก่อนหน้าก็คือ เมนูนี้คำนวณ 2 อย่างในครั้งเดียว แต่ตารางอาจจะดูยากสักหน่อย

• เมนู 21. สร้างตาราง Pivot Table จากชี้ท ODetail หาจำนวนขายแบบแตกสินค้าเซ็ต จำแนกตาม เดือน-รหัสสินค้า

• เมนูมีการสร้างตาราง PIVOT table ที่ซับซ้อน ก็คือ ในกรณีที่เป็นสินค้าเซ็ตอย่างเช่น X001 ซึ่งประกอบไปด้วยสินค้าเดียว(single) A001(1ชิ้น), D001(10ชิ้น), P001(1ชิ้น), A002(2ชิ้น) การคำนวณจะต้องแตกสินค้าเซ็ตออกมาก เพื่อดูว่า ประกอบไปด้วยสินค้าเดี่ยว(single)อะไรบ้าง จากนั้นจึงทำการนับจำนวณสินค้าเดี่ยวในเซ็นนั้นๆ เพราะเราสินใจว่าสินค้าเดียวถูกขายไปเป็นจำนวนเท่าไร
• หากท่านใดจัดสินค้าเป็นเซ็ตแล้วขาย การคำนวณแบบนี้ถือเป็นสิ่งจำเป็น

• เมนู About