Blog

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

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

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

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

เทคนิคการใช้ excel เรื่องโครงสร้าง

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

จัดระเบียบข้อมูล

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

สมมติเช่น รายชื่อ องค์กรปกครองส่วนท้องถิ่น ถ้าเราจะเก็บรายชื่อไว้ ก็จะมีแค่ ชื่อ อปท. ประเภท อปท.  อำเภอ จังหวัด รหัสหน่วยงาน จำนวนข้อมูลน่าจะประมาณ 8,000 บวกลบ แบบนี้โดย sense น่าจะเก็บไว้ใน sheet เดียวกันได้ ใช่หรือไม่ใช่?

ในเวอร์ชัน 2016 เป็นต้นมา เวิร์กชีต 1 งาน เก็บข้อมูลได้ 1,048,576 แถว (Row) ถ้าเป็นเวอร์ชัน 2007 ลงไป จะเก็บได้เพียง 65,536 แถว ดังนั้นถ้าจะเอาข้อมูล อปท. มาใส่ในเวิร์กชีตเดียวกันสวย ๆ ก็ทำได้สบายมาก

แต่ที่เคยเจอคือ บางท่าน จัดระเบียบโดยเอารายชื่อ อปท. แยกใส่เวิร์กชีตละ 1 จังหวัด เท่ากับเราจะมีแผ่นงานที่ต้องจัดการ 77 เวิร์กชีต! เคยเจอแม้แต่ แยก workbook และใส่แยกไว้ในแต่ละโฟลเดอร์อย่างสวยงาม หรือบางท่าน แบ่งประเภทอปท (เทศบาลเมือง เทศบาลตำบล ฯลฯ) ไว้คนละชีตอย่างสวยงาม

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

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

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

พวกโครงสร้างข้อมูลนี้เป็นสิ่งสำคัญในเทคนิคการใช้ excel

Formatting

นอกเหนือจากโครงสร้างตาราง การจัดรูปแบบ (format) ก็เป็นปัญหาเหมือนกัน คือความจริง การทำตารางควรจะเรียบง่าย แต่หลายคนชอบ “ตกแต่งเพื่อความสวยงาม” ซึ่งก็ไม่ผิด แต่บางทีมันตรวจยาก ยกตัวอย่าง เช่น

สี

จำไว้ว่า การตกแต่งรูปแบบอาจจะทำให้ท่านรู้สึกว่ามัน “สวย” แต่มันอาจจะทำให้คนอื่นปวดหัวได้ โดยเฉพาะเรื่องสี ไม่รู้ทำไม เจอพวกบ้าสีตลอด ทำสีมาแปดสี สิบสี มีทั้งสีตัวอักษร สีพื้นหลัง สีไฮไลต์ คือ พี่จ๋า มันลายตาเว้ยยยยยยย ถ้าไม่ติดอะไรมาก ใช้สองสามสีก็พอแล้วจ้า

จัดชิดซ้าย ชิดขวา ตรงกลาง

คนมักจะชอบจัดชิดซ้าย ขวา หรือ กลาง เพื่อความสวยงาม โดยเฉพาะเวลาที่ต้องพิมพ์เป็นรายงาน แต่ อยากให้เก็บเป็นขั้นตอนสุดท้ายจริง ๆ เพราะ ในระหว่างการทำงาน โดยพื้นฐานของ Excel นั้น ค่าที่เป็นตัวเลขจะปรับให้ชิดขวา ตัวอักษรหรืออักขระจะไปชิดซ้าย แบบนี้มันจะเช็คได้ง่ายว่า มีรูปแบบผิดหรือไม่ เพราะ ตัวเลขต้องนำไปคำนวณต่อจะต้องอยู่ในรูปแบบ (format) ตัวเลข – number เท่านั้น ถ้าเก็บเป็นข้อความจะใช้คำนวณไม่ได้ และอาจจะมีปัญหาเวลา lookup ขอให้ใช้เมื่อจำเป็นจริง ๆ เท่านั้น

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

 Merge Cells

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

ถ้าจะ merge cells ควรใช้ตัวเลือกอื่น เช่น ใน format cells ให้เลือก Center across selection แทนการ merge cells (ถ้าหาไม่เจอ ไปที่ริบบอน Home แล้วก็เลือก Alignment คลิกตรงล่างขวาที่เป็นลูกศรชี้ลง ใช้ drop-down เลือก Center Across จะดีกว่าอย่างหาที่เปรียบไม่ได้เทคนิคการใช้ excel เรื่อง Formula

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

Formular Tips

อย่างอ้างอิงเกินเหตุ

คนหลายคน ชอบเขียนสูตรที่คลุมทั้งคอลัมน์ ซึ่งมันทำให้ excel ต้องคำนวณยาวตามที่อ้างอิง แม้ว่าไม่จำเป็นต้องคำนวณในส่วนนั้นเลย

เช่น มีข้อมูลในคอลัมน์ A จนถึง H จำนวน 1,000 แถว แล้วท่านอาจจะทำ vlookup ข้อมูล แล้วเกรงว่าจะต้องมีข้อมูลเพิ่มในอนาคต เวลาอ้างอิงเลยอ้างอิง A:H นั่นหมายถึง A1:H65536 ซึ่งส่วนใหญ่มักจะใช้วิธีนี้กัน โดยให้เหตุผลว่ารองรับข้อมูลในอนาคต ทำเผื่อ ๆ ไว้ก่อน

อยากจะบอกเหลือเกินว่า นี่เป็นความเคยชินที่ไม่ค่อยดีเท่าไหร่ เลี่ยงได้ควรเลี่ยง ซึ่งแนะนำให้ใช้ dynamic named ranges ในการแก้ปัญหาการอ้างอิงแบบนี้จะดีกว่า

Manual Calculation Mode

มีอยุ่อย่างหนึ่งที่เห็นอาจารย์หลายท่านแนะนำ ในกรณีที่ข้อมูลมีปริมาณมาก มีการคำนวณมาก ถ้าต้องคำนวณตลอดจะทำให้การทำงานช้าลง นั่นคือ manual calculation mode หรือการให้ excel หยุดคำนวณอัตโนมัติ เปลี่ยนเป็นคำนวณเมื่อเราต้องการเท่านั้น

สาเหตุที่แนะนำกันอย่างนี้ เพราะการคำนวณซ้ำ ๆ โดยอัตโนมัติตลอดเวลาทำให้ทำงานช้างลง manual calculation mode จะช่วยลดภาระการทำงานของ excel ได้มากพอสมควร

ถ้าจะทำไปที่ Excel → Formulas  → Calculations Options เลือก Manual

ถ้าจะคำนวณค่อยกด F9

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

Array น่ะดี แต่เลี่ยงได้ก็ควรเลี่ยง

สูตรอาร์เรย์ (Array formulas) เป็นฟังก์ชันที่ดีและมีประโยชน์มาก แต่ขณะเดียวกันก็สร้างปัญหาได้เช่นกัน ถ้าต้องอ้างอิงช่วงข้อมูลขนาดใหญ่จะเป็นปัญหาในการทำงานพอควร และบางทีก็ต้องบังคับให้ใช้ manual calculation mode เพื่อเลี่ยงการทำงานหนักเกินเหตุของ excel

ดังนั้น ถ้าเลี่ยงอาร์เรย์ได้ก็ควรเลี่ยง แนะนำให้ลองอ่าน “Sum or Count Cells While Avoiding Error Values” เพื่อใช้การคำนวณอื่นที่ทดแทนกัน แต่ไม่ใช่ว่าไม่ควร Array นะ เพียงแต่ควรจะใช้เท่าที่จำเป็นต้องใช้

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