เมื่อสูตร Excel มีชีวิตจิตใจ

อยากเก่งสูตร ต้องเดาสูตรให้เป็น

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

ยกตัวอย่างเช่น สูตร If มาจากคำว่า ถ้า

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

สูตร Excel จัดลำดับโครงสร้างภายในวงเล็บไว้เรียงตามลำดับความคิดของมนุษย์โดยใช้เครื่องหมาย comma , แบ่งแยกแต่ละลำดับออกจากกัน

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

สูตร If จึงมีลักษณะโครงสร้างสูตรเป็น

=If( เงื่อนไข, ผลกรณีเงื่อนไขเป็นจริง , ผลกรณีเงื่อนไขเป็นเท็จ )

ลองดูตัวอย่างใหม่ให้ยากขึ้นหน่อยกันดีกว่า ช่วยกันหาว่าสินค้าที่มีรหัส Id a003 มียอด Amount เท่ากับเท่าใด

ลองค่อยๆใช้ตาคุณมองตาของคุณดูว่า กว่าจะตอบออกมาได้ว่า Amount เท่ากับ 30 นั้น คุณใช้ตามองอะไรแล้วเหลือบไปมองอะไรทีละขั้น ตามนี้ใช่ไหม

ขั้นแรก ต้องรู้ก่อนว่าจะหา Id a003

ขั้นที่สอง เหลือบตาไปมองตาราง B3:D7 โดยมองเทียบรหัส a003 กับรหัส Id ในแนวแถวตั้งแรกก่อนว่าอยู่ตรงไหน

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

การมองตามแถวตั้งนี้ชื่อภาษาอังกฤษเรียกว่า Vertical Lookup ซึ่ง Excel ย่อชื่อสูตรออกมาเป็น VLookup แล้วโครงสร้างภายในสูตรจึงตามด้วย ("a003",B3:B7,3) ตามลำดับขั้นแรกถึงขั้นที่สามนั่นเอง

=VLookup("a003",B3:B7,3)

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

วิธีเลือกใช้สูตร

จากปัญหาเดิมที่ใช้ VLookup หา Id a003 ออกมาได้ว่ามี Amount 30 นั้น ถ้าเราไม่ใช้ VLookup แต่หันกลับไปใช้สูตร If แทน โดยสมมติว่าที่เซลล์ A1 เป็นรหัส a003 จะต้องใช้สูตร If ดังนี้

=If(A1="a001",10,If(A1="a002",20,If(A1="a003",30,If(A1="a004",40,50))))

แม้สูตร If ใช้หาคำตอบได้เช่นเดียวกับ VLookup ก็ตาม แต่มนุษย์เรามักชอบใช้ VLookup มากกว่าเพราะสั้นกว่า If มาก ส่วนในแง่ของ Excel นั้น สูตร If จะใช้เวลาคำนวณเร็วกว่า

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

แต่อย่างไรก็ตามเพื่อช่วยให้ผู้ที่ไม่เคยคุ้นเคยกับสูตร Excel มาก่อน พอจะได้แนวทางไปใช้ต่อไปได้เอง ดีกว่าจะเสียเวลาลองผิดลองถูก ผมจึงขอเสนอแนะวิธีเลือกใช้สูตร If, And, Or, Choose, VLookup, CountIf, SumIf ซึ่งเป็นสูตรจำเป็นต้องใช้ในงานเสมอ ดังนี้

• สูตร If ให้ใช้กับการตัดสินใจง่ายๆ โดยค่าที่ใช้เป็นเงื่อนไขจะเป็นเลขหรือตัวอักษรใดก็ได้ จะอ้างอิงมาจากเซลล์ใดก็ได้ ไม่จำกัดว่าต้องมาจากชีทเดียวกัน
• ถ้าต้องการเพิ่มเติมเงื่อนไขให้กับสูตร If ให้ใช้สูตร And หรือสูตร Or ช่วยกำหนดเงื่อนไขซ้อนเข้าไปในสูตร If
• แม้จะเพิ่ม And/Or เข้าไปในสูตร If แล้วก็ตามแต่อย่างไรก็ตามก็ยังจำกัดผลที่ต้องการแค่ 7 ค่าเท่านั้น เพราะสูตร If จะซ้อน If ต่อไปได้อีกแค่ 7 สูตรเท่านั้น
• ถ้าต้องการผลลัพธ์เกินกว่า 7 ค่า ให้ใช้สูตร Choose แทน If เพราะสูตร Choose สามารถเลือกคำตอบได้ถึง 29 ค่า แต่สูตร Choose ต้องใช้เลขลำดับจาก 1-29 เป็นตัวชี้ ต่างจาก If ที่สามารถใช้เลขหรือตัวอักษรเป็นเงื่อนไขก็ได้
• สูตร If และ Choose มีข้อดีเหมือนกันตรงที่สามารถกระจายค่าที่เกี่ยวข้องกับสูตรไว้ที่ใดก็ได้ ไม่ต้องนำค่ามาสร้างเป็นตารางเนื้อที่ติดกัน
• หากต้องการเลือกคำตอบเกินกว่า 29 ค่า ต้องนำค่าเหล่านั้นมาสร้างเป็นตารางติดต่อกันในชีทเดียวกันเพื่อใช้สูตร VLookup แทน
• VLookup มีจุดอ่อนที่สำคัญ คือ ต้องเป็นตารางติดต่อกัน และใช้แถวตั้งแรกสุดซ้ายมือเท่านั้นเก็บค่าหรือรหัสที่ใช้ค้นหา นอกจากนั้นยังต้องกำหนดเลขแถวตั้งที่เก็บค่าคำตอบให้กับสูตร VLookup อีกด้วย
• หากตารางไม่ติดต่อกันหรือแยกกันอยู่ต่างชีท ต้องใช้สูตร Match แล้วตามด้วย Index แทน
• VLookup กับ Match มีจุดอ่อนตรงที่ไม่สามารถใช้หาค่าที่มีรายการซ้ำได้
• หากมีรายการซ้ำต้องใช้สูตร CountIF เพื่อนับรายการซ้ำว่ามีกี่รายการ แล้วใช้สูตร SumIF เพื่อหายอดรวมทั้งหมดของรายการซ้ำ
• รายละเอียดของประเภทสูตรและข้อดีข้อเสียของสูตร SumIf ขอให้ดูจาก http://www.excelexperttraining.com/blogs/archives/z200-eLearning000263.php
• VLookup, Match, Index เป็นสูตรที่หาคำตอบตามแนวตั้งฉาก แต่ถ้าต้องการหาคำตอบตามแนวอื่นหรือใช้ค่าปัจจุบันย้อนหาค่าในอดีต ให้ใช้สูตร Offset แทน

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

• ถ้าค่าที่ต้องการหา มีค่าเดียว ให้ใช้สูตรในกลุ่ม Lookup
• ถ้าตารางมีโครงสร้างคงที่ เลือกใช้สูตร VLookup
• ถ้าตารางมีโครงสร้างไม่แน่นอน เลือกใช้สูตร Match+Index หาค่าในอนาคต เลือกใช้สูตร Offset หาค่าในอดีตอนาคตก็ได้
• ถ้าค่าที่ต้องการหา บันทึกซ้ำไว้หลายค่า ต้องใช้สูตร Array หายอดรวม


• ถ้าเงื่อนไขง่ายๆ ใช้สูตร SUMIF


• ถ้าเงื่อนไขยากขึ้น ซ้บซ้อนขึ้น ให้ดูต่อที่เงื่อนไข


• ถ้าเงื่อนไขมีทั้งใช่และไม่ใช่ ใช้สูตร Sum-IF-Array


• ถ้าเงื่อนไข ใช้แค่แบบใช่


• เลือกใช้สูตร SumArray จะแกะได้


• เลือกใช้สูตร SumProduct แบบ -- เพื่อแฟ้มเล็กแต่จะใช้วิธีกด F9 ในวงเล็บทั้งหมดเพื่อแกะไม่ได้


• เลือกใช้สูตร SumProduct แบบ * แฟ้มใหญ่กว่าหน่อย แต่แกะสูตรได้
• ถ้าวัดความเร็ว สูตร Lookup เร็วกว่าสูตร Array


• SumIF เร็วกว่า Sum-IF-Array เร็วกว่า SumProduct เร็วกว่า SumArray
• ถ้าดูจากขนาดแฟ้ม จากใหญ่กว่าไปเล็กกว่า Sum-IF-Array > SumArray > SumProduct
• แต่ถ้าพิจารณาจากความชอบ ผมชอบใช้ Index+Match, SumArray, SumProduct แบบ * เพราะยืดหยุ่น โครงสร้างง่าย และแกะสูตรได้ทุกขั้นตอนของการคำนวณ
• แนวทางข้างต้นนี้ อาจเห็นแตกต่างกัน แล้วแต่จะมีมุมมอง และยังต้องดูสถานการณ์อื่นๆประกอบอีกด้วย

เมื่อใดควรหรือไม่ควรใช้สูตร Array

• เนื่องจากสูตร Array จะทำให้แฟ้มคำนวณช้าลง ดังนั้นหากไม่จำเป็นแล้วควรหาทางใช้สูตรสำเร็จรูปอื่นๆที่มีอยู่แล้วให้ได้ก่อน แต่ถ้าไม่ทีทางเลือกอื่นแล้วก็จำเป็นต้องใช้สูตร Array
• เมื่อใช้สูตร Array แล้วควรหาทางปรับโครงสร้างตารางให้ใช้จำนวนเซลล์ลดลง เพราะแฟ้มจะได้คำนวณเร็วขึ้น
• ถ้าแฟ้มใดมีผู้ใช้งานร่วมกันหลายคน ควรแนะนำให้ผู้ใช้ทุกคนสังเกตสูตรใดก็ตามอยู่ในระหว่างเครื่องหมาย { } ว่าเป็นสูตร Array ซึ่งต้องกดปุ่ม Ctrl+Shift+Enter ในการสร้างหรือแก้ไขสูตร
• หลีกเลี่ยงการสร้างสูตร Array ที่ต้องกดปุ่ม Ctrl+Shift+Enter โดยขอให้ใช้สูตร SumIF หรือ SumProduct ที่สามารถทำงานแบบ Array และไม่จำเป็นต้องใช้วิธีกดปุ่ม Ctrl+Shift+Enter ในการสร้างสูตรแต่อย่างใด
• ถ้าผู้ใช้งานร่วมกันไม่มีความสามารถในการแกะสูตร ขอแนะนำให้หลีกเลี่ยงการใช้สูตร Array หรือถ้าต้องการใช้ก็ควรพยายามสร้างสูตรสั้นๆที่ง่ายต่อความเข้าใจ อย่าสร้างสูตรซ้อนกันยาวจนแกะไม่ไหว โดยขอให้สร้างสูตรคำนวณแยกเป็นคำตอบทีละขั้นดีกว่าที่จะใช้สูตรยากๆยาวๆสูตรเดียวลัดขั้นการคำนวณ
• หลีกเลี่ยงการสร้างสูตร Array ซ้ำกันลงไปในเซลล์หลายเซลล์ แต่ควรหาทางสร้างสูตร Array สูตรเดียวลงไปในตารางหลายเซลล์พร้อมกันทีเดียวทั้งตาราง เพราะสูตรที่สร้างพร้อมกันทีเดียวสูตรเดียวนั้น ย่อมเสียเวลาคำนวณเพียงครั้งเดียวเท่านั้น
• ควรใช้สูตร Function VBA ทีทำงานแบบ Array ที่ต้องคำนวณซับซ้อนหลายขั้นดีกว่าที่จะใช้สูตร Array ซ้อนต่อกันหลายชั้น
• ผู้ใช้สูตร Array ต้องมีความเข้าใจในที่ไปที่มาและสามารถสร้างสูตรขึ้นมาใช้เอง อย่าลอกสูตรผู้อื่นมาใช้

โจทย์สำคัญกว่าตัวสูตร

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

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