• Power Query: นำเข้า + ทำความสะอาดข้อมูล (Get & Transform) ให้พร้อมก่อนวิเคราะห์

    Bylungthi

    1. ข้อมูลมีความขาดตกบกพร่องคือต้นตอของปัญหาทุกอย่าง ต่อจากบทความ PivotTable ที่เราเรียนกันไป ผมเชื่อว่าหลายคนเจอสถานการณ์แบบนี้ — เปิดไฟล์ขายมาแล้วข้อมูลเต็มไปด้วยแถวว่าง ชื่อสาขาเขียนไม่เหมือนกัน บ้างเขียน “กรุงเทพ” บ้างเขียน “กทม” วันที่บางแถวเป็นข้อความ บางแถวเป็นตัวเลข คุณอยากทำ PivotTable สรุปยอดแต่พอสร้างออกมาแล้วตัวเลขเพี้ยน กลุ่มข้อมูลไม่รวมกันเป็นก้อน สิ่งนี้เรียกกันว่า ข้อมูลสกปรก (dirty data) ครับ และมันคือฝันร้ายของคนที่ต้องวิเคราะห์ข้อมูลทุกคน ไม่ว่าคุณจะเก่งสูตรหรือ PivotTable แค่ไหน ถ้าข้อมูลต้นทางไม่สะอาด ผลลัพธ์ที่ออกมาก็เชื่อถือไม่ได้ และที่แย่กว่านั้นคือเรามักรู้ตัวช้า ตอนตัวเลขโดนตั้งคำถามในการประชุมแล้ว ขอยกตัวอย่างปัญหาที่พบบ่อยครับ เช่น ชื่อสินค้าบางแถวพิมพ์เกินช่องว่าง บางแถวสะกดต่างกัน วันที่บางรายการเป็นตัวเลข บางรายการเป็นข้อความ มีแถวว่างคั่นกลางตารางหลายจุด ทำให้ PivotTable รวมกลุ่มข้อมูลไม่เข้า แล้วตัวเลขยอดรวมก็เพี้ยนไปอย่างไม่รู้ตัว ปัญหาแบบนี้เจอบ่อยในไฟล์ที่คนหลายคนช่วยกันกรอกข้อมูลครับ หลายคนแก้ปัญหาด้วยการเปิดไฟล์มานั่งลบแถวว่างทีละแถว ลบคอลัมน์ที่ไม่ใช้ ทุกครั้งที่ได้ไฟล์ใหม่ก็ต้องมานั่งทำซ้ำเดิม ๆ วนไปวนมา เสียเวลามหาศาล แถมพอทำมือแบบนี้บ่อยครั้งก็มีโอกาสพลาดสูง เช่น ลบแถวผิด หรือแก้หัวคอลัมน์แล้วส่งต่อข้อมูลเพี้ยน ขอจินตนาการภาพว่าคุณต้องเปิดไฟล์ขายสิบไฟล์แล้วคัดลอกต่อกันทุกสัปดาห์…

  • PivotChart + Sparklines — เห็นภาพจากข้อมูลให้จบในไฟล์เดียว

    Bylungthi

    ไม่มีใครอยากอ่านตารางตัวเลขหมื่นแถว สมมติว่าคุณเพิ่งทำ PivotTable สรุปยอดขายรายสาขาเสร็จเรียบร้อย ตัวเลขครบถ้วน แต่พอส่งให้หัวหน้าดู เจอคำถามแรกเลยว่า “สรุปให้เห็นภาพหน่อยได้ไหม” — เพราะสมองคนเราอ่านภาพได้เร็วกว่าตัวเลขเป็นกองๆ นี่เองครับ PivotTable ให้ตัวเลขที่แม่นยำ แต่ตัวเลขอย่างเดียวอาจยังสื่อสารไม่ชัด ถ้าคุณอยากให้รายงานดูง่ายขึ้นอีกขั้น สิ่งที่ต้องทำต่อคือเปลี่ยนตัวเลขเหล่านั้นให้เป็นภาพครับ ทั้ง PivotChart สำหรับกราฟใหญ่ และ Sparklines สำหรับกราฟจิ๋วในเซลล์ บทความนี้ผมจะพาคุณสร้างทั้งสองอย่างให้จบในไฟล์เดียว เริ่มจาก PivotChart ที่ผูกกับ PivotTable โดยตรง แล้วปิดท้ายด้วย Sparklines ที่ช่วยดูเทรนด์ทีละแถว แค่ทำตามก็เห็นภาพชัดขึ้นทันทีครับ เปิดไฟล์ตัวอย่าง และทำไปด้วยกันเลยนะครับ ผมเตรียมข้อมูลยอดขายรายเดือน รายหมวดไว้ให้เรียบร้อยแล้ว PivotChart คืออะไร ต่างจาก Chart ปกติยังไง PivotChart คือกราฟที่สร้างขึ้นจาก PivotTable โดยตรงครับ ต่างจาก Chart ทั่วไปตรงที่ข้อมูลในกราฟถูก “ผูก” กับ PivotTable เอาไว้ เมื่อไหร่ที่คุณกรองหรือแก้ Pivot กราฟจะเปลี่ยนตามอัตโนมัติ…

  • PivotTable ขั้นสูง — Group, Calculated Field, Slicer และ Timeline

    Bylungthi

    1. เมื่อ PivotTable พื้นฐานแล้วไม่พอ บทความก่อนหน้าเราได้สร้าง PivotTable ครั้งแรก ลากฟิลด์ไป Rows, Columns, Values, Filters จนได้รายงานสรุปยอดขายตามสาขาและสินค้าในไม่กี่คลิก แต่วันนี้ผมจะพาคุณไปอีกระดับครับ — ระดับที่ทำให้ PivotTable ไม่ได้เป็นแค่ตารางสรูปธรรมดา แตกลายเป็นเครื่องมือวิเคราะห์ที่ตอบคำถามธุรกิจลึกขึ้นได้ ลองนึกดูครับ ถ้าคุณมีข้อมูลขายรายวันตลอดปี คุณอยากดูแนวโน้มรายเดือนหรือรายไตรมาส จะนั่งเลื่อนดูทีละวันไหม? ถ้าอยากคำนวณกำไร = ยอดขาย − ต้นทุน โดยไม่ต้องเพิ่มคอลัมน์ในข้อมูลต้นทาง จะทำยังไง? ถ้าอยากให้เพื่อนร่วมงานกรองดูเฉพาะสาขาที่เขาสนใจด้วยตนเองแบบกดปุ่มเดียว ไม่ต้องมาขอคุณกรองให้ทุกครั้ง จะสะดวกมากไหม? คำตอบทั้งหมดอยู่ที่ 4 ฟีเจอร์นี้ครับ: Group, Calculated Field, Slicer, และ Timeline พร้อมกับการจัด Layout ให้อ่านง่าย เปิดไฟล์ตัวอย่างครับ แล้วทำไปด้วยกัน — ผมเตรียมข้อมูลขายร้านเครื่องดื่มครอบคลุมวันที่ สาขา สินค้า จำนวน ราคา ต้นทุน และยอดรวม…

  • PivotTable เบื้องต้น — สร้างและจัดวางฟิลด์ให้สรุปข้อมูลเป็นเรื่องง่าย

    Bylungthi

    1. เคยปวดหัวกับยอดขายหลายพันแถวไหม สมมติว่าคุณมีไฟล์ขายของร้านเครื่องดื่มที่บันทึกยอดขายรายวันตลอดหลายเดือน — มีทั้งสาขา สินค้า จำนวน ราคา ไล่ลงมาเป็นร้อยๆ แถว ลองนึกดูว่าถ้าอยากรู้ว่าสาขาไหนขายดีที่สุดหรือสินค้าไหนขายได้มากสุด คุณจะทำยังไง? ถ้าจะนั่งกรองทีละค่าแล้วรวมด้วย SUMIFS ก็พอไปได้ แต่พออยากเปลี่ยนมุมมองใหม่ เช่น เปลี่ยนจาก “ดูตามสาขา” เป็น “ดูตามสินค้า” หรืออยากเพิ่มเงื่อนไขอีกชั้นหนึ่ง คุณก็ต้องมานั่งแกะสูตร เปลี่ยนอาร์กิวเมนต์กันใหม่ทุกครั้ง — เสียเวลาไปเปล่าๆ อีกทั้งพอถึงเวลาส่งรายงานจริง หลายคนก็เจอว่าสูตรที่เคยเขียนไว้ใช้ได้อยู่ดี วันหนึ่งเพื่อนร่วมงานขอให้ดูอีกมุมหนึ่ง เช่น ขอว่ารวมตามเดือนด้วย ก็ต้องกลับไปแก้สูตรใหม่อีก round — วนเวียนแบบนี้แหละครับที่ทำให้งานสรุปข้อมูลดูน่าเบื่อและเสียเวลา วันนี้ผมจะพาคุณรู้จักเครื่องมือที่ตอบโจทย์นี้ได้ในไม่กี่คลิก นั่นคือ PivotTable ครับ เครื่องมือสรุปข้อมูลที่คนทำงานสายวิเคราะห์เกือบทุกคนต้องใช้เป็น เพราะมันเปลี่ยนข้อมูลดิบหลายพันแถวให้กลายเป็นสรุปที่อ่านง่ายในพริบตา โดยไม่ต้องเขียนสูตรยาวๆ เลย ยิ่งข้อมูลเยอะแค่ไหน ยิ่งเห็นคุณค่าของ PivotTable ชัดขึ้นเท่านั้นครับ เพราะผมมือวิเคราะห์หลายคนกว่าจะส่งรายงานแต่ละชุดก็ต้องคัดลอกข้อมูลมานั่งรวมกันทีละกลุ่ม ใช้เวลามหาศาล แต่พอมี PivotTable เรื่องพวกนี้กลายเป็นแค่เรื่องของลากวาง ลองนึกภาพว่าคุณเป็นผู้จัดการร้านกาแฟที่ต้องส่งรายงานยอดขายรายสัปดาห์ให้เจ้าของกิจการ ทุกสัปดาห์คุณต้องเปิดไฟล์ขาย คัดกรองสาขาทีละสาขา…

  • Named Ranges + ตรวจสอบสูตร — Trace, Evaluate, Watch

    Bylungthi

    1. ทำให้สูตรของคุณอ่านง่ายและดูแลได้แบบมือโปร ในกลุ่มบทความ “Excel สำหรับทำงาน” นี้ คุณคงได้สร้างสูตรที่ลึกและซับซ้อนขึ้นเรื่อยๆ ตั้งแต่ VLOOKUP, INDEX+MATCH ไปจนถึง INDIRECT และ ERROR handling ที่เพิ่งจบไป บทความนี้เป็นบทสุดท้ายในกลุ่มนี้ครับ ผมอยากพาคุณมาปิดท้ายด้วยทักษะที่คนใช้ Excel เก่งๆ ใช้เป็นประจำ แต่คนเริ่มต้นมักมองข้าม นั่นคือ การตั้งชื่อช่วง (Named Ranges) และ การตรวจสอบสูตร (Formula Auditing) ลองนึกภาพว่าคุณเปิดไฟล์ราคาแล้วเห็นสูตรแบบนี้ เทียบกับแบบนี้ สูตรไหนอ่านแล้วรู้เรื่องทันทีว่ากำลังคำนวณอะไร? คำตอบชัดเจนมากครับ การตั้งชื่อให้ช่วงข้อมูลไม่ใช่แค่เรื่องสวยงาม แต่ช่วยให้คุณและคนอื่นที่มาใช้ไฟล์ต่อเข้าใจที่มาที่ไปของสูตรได้โดยไม่ต้องมานั่งเดาว่า B2:B5 คืออะไร ส่วนการตรวจสอบสูตรคือเรื่องของความมั่นใจครับ สูตรยาวๆ หลายชั้น เวลาอยากรู้ว่ามันอ้างอิงไปที่ไหนบ้าง หรือผลลัพธ์มันเพี้ยนอยู่ตรงจุดไหน เรามีชุดเครื่องมือที่ฝังมากับ Excel อยู่แล้วให้ใช้ ไม่ต้องเขียนฟังก์ชันอะไรเพิ่มเลย วันนี้ผมจัดเตรียมไฟล์ตัวอย่างไว้ 3 ชีทคือ NAMED RANGES ตั้งชื่อช่วงและใช้ในสูตร, TRACE PRECEDENTS…

  • กันข้อผิดพลาด — IFERROR, IFNA, ISERROR + ตรวจสูตร

    Bylungthi

    1. อย่าให้ error มาทำลายรายงานของคุณ บทความที่แล้วเราสร้างสูตรอ้างอิงขั้นสูงแบบ INDIRECT, OFFSET, ADDRESS กันไป คราวนี้ผมอยากพาคุณมาดูอีกด้านที่คนใช้ Excel ปฏิเสธไม่ได้ว่าต้องเจอ นั่นคือ error หรือข้อผิดพลาด ครับ ไม่ว่าสูตรจะสวยแค่ไหน พอกลับมาเปิดไฟล์ทีไรเห็น #DIV/0! โผล่เป็นพรืด หรือ VLOOKUP ตอบ #N/A เพราะพิมพ์รหัสผิดแค่ตัวเดียว ความมั่นใจในไฟล์ก็วูบลงไปเลย ลองนึกภาพนะครับ ว่าคุณเป็นเจ้าของร้านค้าชุมชนที่ใช้ Excel คำนวณกำไรต่อหน่วยของสินค้ารายวัน สินค้าบางตัวเพิ่งสั่งมาวันนี้ยังไม่ได้ตั้งราคาขาย ต้นทุนโผล่ขึ้นมาแล้วตารางก็เด้ง #DIV/0! ขึ้นมาจนดูไม่เป็นระเบียบ หรือฝ่ายขายพิมพ์รหัสสินค้าผิดไปหนึ่งตัวแล้ว VLOOKUP ก็ตอบ #N/A จนลูกค้าสงสัยว่าไฟล์พังรึเปล่า ที่จริงแล้วมันไม่ใช่เรื่องผิดปกติเลยครับ แต่มันคือหน้าที่ของเราที่จะทำให้ไฟล์ทนทานต่อสถานการณ์แบบนี้ได้ ชุดฟังก์ชันที่เราจะใช้วันนี้มีดังนี้ครับ เปิดไฟล์ตัวอย่าง แล้วทำไปด้วยกันนะครับ ผมจัดไว้ 3 ชีท เริ่มจาก IFERROR DIV0 กัน error จากการหาร ข้ามไป IFNA…

  • การอ้างอิงขั้นสูง — INDIRECT, OFFSET, ADDRESS

    Bylungthi

    1. เมื่อสูตรต้องหาตำแหน่งเอง บทความก่อนหน้าเราจัดการกับ Dynamic Arrays กันไปแล้ว คราวนี้ผมจะพาคุณเข้าสู่อีกโลกหนึ่งของสูตรที่เรียกว่า การอ้างอิงขั้นสูง ครับ ฟังดูซับซ้อน แต่จริงๆ แล้วมันคือการทำให้สูตรรู้จัก “ย้าย” และ “มองหา” ตำแหน่งได้ด้วยตัวเอง โดยที่เราไม่ต้องแก้สูตรทุกครั้ง เคยไหมครับ ที่ต้องทำรายงานสรุปยอดและทุกเดือนก็ต้องมือเปล่าเข้าไปเปลี่ยนช่วงข้อมูลในสูตรทีละตัว เช่น เดือนนี้สรุป 3 เดือน พอเดือนหน้าต้องสรุป 4 เดือน ก็ต้องไปแก้สูตรใหม่ทุกครั้ง หรืออยากให้สูตรอ่านเซลล์ที่เราพิมพ์ชื่อไว้อีกที แล้วดึงข้อมูลจากตรงนั้นให้ เราจะมานั่งเขียนสูตรตายตัวกันไปไม่ได้แล้ว เพราะยิ่งไฟล์งานโตขึ้นเท่าไหร่ การแก้สูตรด้วยมือก็ยิ่งเสี่ยงพลาดมากเท่านั้นครับ วันนี้เราจะมาทำความรู้จักฟังก์ชันสามตัวที่ทำงานคู่กันสุดแกร่งครับ ทั้งสามตัวนี้เหมือนเป็นชุดเครื่องมือที่เปิดประตูให้สูตรของเราอ้างอิงแบบอัตโนมัติได้มากขึ้น โดยเฉพาะเวลาทำไฟล์รายงานที่ข้อมูลเปลี่ยนทุกเดือนหรือทุกสัปดาห์ เพราะแทนที่จะแก้สูตรซ้ำๆ เราสร้างกลไกให้มันขยับตามเอง แถมยังผูกเข้ากับ Data Validation และ Named Ranges ได้อย่างกลมกลืนด้วย ถ้ายังรู้สึกว่าแนวคิดนี้จับต้องยาก ให้ลองคิดง่ายๆ ว่า Excel ปกติแล้วเราพิมพ์ที่อยู่แบบตรงๆ อย่าง A1 หรือ B2 เข้าไปในสูตร แต่วิธีใหม่นี้เราสอนให้สูตร…

  • Dynamic Arrays — FILTER, SORT, UNIQUE, SEQUENCE

    Bylungthi

    1. เมื่อสูตรหนึ่งตอบได้หลายค่า บทความก่อนหน้าเราจัดการกับวันที่กันไปแล้ว คราวนี้ผมจะพาคุณมาถึงจุดพลิกโฉมของ Excel ในยุค Microsoft 365 ครับ นั่นคือ Dynamic Arrays หรือที่เรียกกันว่า สูตรแบบไดนามิก เคยไหมครับ เวลากรองข้อมูลก็ต้องเปิด Filter ทีละคอลัมน์ เวลาหาค่าที่ไม่ซ้ำก็ต้องคัดด้วยมือ หรือเวลาเรียงข้อมูลก็ต้องกด Sort แล้วเสี่ยงทำข้อมูลต้นฉบับเละไปกับมือ ถ้าฟังดูคุ้น นี่แหละคือปัญหาที่ Dynamic Arrays เกิดมาแก้ ก่อนหน้านี้ถ้าเราอยากให้สูตรตอบหลายค่าต้องกด Ctrl+Shift+Enter ให้เป็น Array Formula และผลลัพธ์ต้องวางไว้ให้พอดีล่วงหน้า ถ้าเผลอลากเยอะไปหรือหย่อนไปก็พังทิ้ง หรือถ้าเผลอไปกดตรงกลางก็แก้ยาก ยุคของ Dynamic Arrays ต่างออกไปสิ้นเชิง เพราะคุณแค่เขียนสูตรหนึ่งครั้ง แล้ว Excel จัดการเรื่องขนาดและตำแหน่งให้เองทั้งหมด หลักการมันง่ายมาก คือสูตรกลุ่มนี้ตอบ ได้หลายค่าพร้อมกัน และค่าที่ตอบมานั้น ไหลลงมา (spill) ครอบคลุมหลายเซลล์อัตโนมัติ โดยคุณไม่ต้องลากสูตรลงมาเองแม้แต่เซลล์เดียว แถมเมื่อข้อมูลต้นทางเปลี่ยน ผลลัพธ์ก็อัปเดตตามทันทีแบบเรียลไทม์ ชุดฟังก์ชันที่เราจะเล่นกันวันนี้มี 4…

  • ฟังก์ชันวันที่และเวลา — DATEDIF, EOMONTH, WORKDAY, NETWORKDAYS

    Bylungthi

    1. วันที่ใน Excel ไม่ใช่แค่ตัวเลขธรรมดา บทความที่แล้ว เราเพิ่งไขความลับของฟังก์ชันข้อความกันไป คราวนี้ผมจะพาคุณมาดูอีกมิติหนึ่งที่คนทำงานไทยเจอทุกวัน นั่นคือ วันที่และเวลา ครับ ลองนึกภาพดูนะครับ อยากรู้ว่าพนักงานคนนี้อายุงานกี่ปีแล้ว อยากรู้ว่าบิลครบกำหนดชำระตรงกับสิ้นเดือนพอดีหรือเปล่า หรืออยากรู้ว่างานนี้ถ้าเริ่มวันจันทร์แล้วต้องใช้เวลากี่วันทำการถึงจะเสร็จก่อนเสาร์-อาทิตย์ งานพวกนี้ล้วนแต่นับวันเป็นหลัก แต่ถ้าคุณนับด้วยมือหรือคิดในใจทีละแถว รับรองพลาดแน่ และยิ่งข้อมูลเยอะก็ยิ่งพัง ที่สำคัญคือ Excel ไม่ได้มองวันที่เป็นแค่ตัวหนังสือ แต่มันแปลงวันที่ทุกวันให้เป็น serial number — ตัวเลขที่ใช้คำนวณได้จริง เช่น 1 มกราคม 1900 คือเลข 1 และเพิ่มขึ้นวันละ 1 ตัวเลขนี้แหละที่ทำให้เราบวก ลบ เปรียบเทียบวันได้ดั่งตัวเลขทั่วๆ ไป ชุดฟังก์ชันที่เราจะใช้กันวันนี้มีดังนี้ครับ ให้เปิดไฟล์ตัวอย่าง แล้วทำตามไปด้วยกันจะดีมากเลยครับ ผมจัดไว้ 3 ชีท เริ่มจากวางแผนงานด้วย WORKDAY ไปนับอายุงานด้วย DATEDIF และปิดท้ายที่งวดบิลกับ EOMONTH ผมเตรียมตัวอย่างให้ครบทั้งการบวกวัน การนับวันระหว่างช่วง และการจัดการงวด เพื่อให้คุณเห็นภาพการประยุกต์ใช้งานจริงในแต่ละสายงาน 💡…