ในบรรดาสูตรสำเร็จรูปของ Excel หรือ Excel Function ที่มีหลายร้อยสูตรนั้น มีเพียงไม่กี่สูตรที่ต้องฝึกใช้ให้เป็นและทำความเข้าใจให้ดีว่าจะเลือกใช้สูตรไหน เมื่อไหร่ และเพราะอะไร ซึ่งสูตรเหล่านี้ผมนำมาอธิบายไว้ทั้งบนเว็บและฟอรัมบ่อยครั้ง ใครที่ขยันติดตามอ่านก็แทบไม่ต้องซื้อตำราหรือไปเข้าอบรมที่ไหนเสียด้วยซ้ำ
สูตรที่ติดไม้ติดมือไว้ตลอด ได้แก่ IF IsError And OR Choose Vlookup Match Index CountIF SumIF SumArray SumProduct และ Offset
การเลือกใช้สูตรต้องไม่เอาแต่ใจตัวเองหรือเลือกใช้เพราะเป็นสูตรที่ตัวเองถนัด แต่ต้องเลือกใช้โดยมีเหตุผล ซึ่งเหตุผลของแต่ละคนอาจเหมือนกันหรือแตกต่างกันก็ได้แล้วแต่สถานการณ์และสภาพแวดล้อม
ถ้าสร้างขึ้นมาเพื่อใช้งานครั้งเดียวแล้วทิ้ง จะเลือกใช้สูตรใดก็ได้ที่หาคำตอบได้เหมือนกัน แต่ถ้าแฟ้มนั้นต้องนำมาใช้บ่อยก็จำเป็นต้องเลือกสูตรที่ใช้ให้ต่างไปจากแฟ้มที่สร้างขึ้นมาเพื่อใช้นานๆครั้งก็เป็นได้ เพราะพอไม่ได้ใช้แฟ้มใดเป็นประจำ ในไม่นานก็จะลืมไปแล้วว่าอะไรเป็นอะไรในแฟ้มที่มีเยอะแยะไปหมดใช่ไหม
หากเลือกสร้างสูตรที่ต้องซ้อนสูตรกันยาวเหยียดก็ย่อมยากที่จะจำได้ว่าทำไมจึงต้องเอาสูตรนั้นมาซ้อนกับสูตรนี้ด้วย กว่าจะแกะสูตรออกต้องเสียเวลานานมาก
ในบรรดาสูตรติดไม้ติดมือนั้น เริ่มแรกให้คิดถึงสูตร IF ก่อน ถ้าจำเป็นต้องใช้สูตร IF มาซ้อนกันก็ขอให้ซ้อนกันเพียงไม่กี่ชั้น แต่ถ้าต้องซ้อนกันหลายชั้นจึงจะหาคำตอบได้ ควรหนีสูตร IF ไปใช้สูตรอื่นกันดีกว่า
สูตร IF กับ Choose เหมาะกับข้อมูลที่ต้องนำมาเป็นเงื่อนไขและหาคำตอบเป็นค่าที่กระจายกัน ไม่ได้อยู่ติดกันเป็นตารางที่เดียว
สูตร VLookup Match Index เป็นสูตรที่ช่วยทำให้ทำงานได้ง่ายขึ้น แกะ แก้ไข ตรวจสอบความถูกต้องก็ง่ายกว่าค่าที่กระจาย โดยใช้กับข้อมูลในแบบตารางที่นำเงื่อนไขและคำตอบมาไว้ในตารางเดียวกัน ซึ่ง Index ยืดหยุ่นกว่า VLookup เพราะ Index สามารถใช้กับตารางที่แยกกันอยู่ต่างชีทหรือต่างแฟ้มกันก็ได้โดยต้องใช้ Match หาเลขตำแหน่งรายการให้ได้ก่อน ส่วน VLookup เหมาะกับการค้นหาข้อมูลจากตารางที่มีโครงสร้างเป็นมาตรฐานซึ่งมีตำแหน่งของ column แต่ละเรื่องคงเดิม (หาก insert column แทรกจะทำให้สูตร VLookup หาคำตอบเพี้ยนผิดพลาดได้ทันที)
สูตร VLookup Match Index สามารถใช้หาคำตอบที่เป็นได้ทั้งตัวเลขและตัวอักษร แต่จุดอ่อนของสูตร VLookup Match Index จะหาค่าคำตอบได้ถูกต้องต่อเมื่อในตารางนั้นไม่มีค่าที่ซ้ำกันเลย หากมีรายการที่ซ้ำกันก็จะหาคำตอบได้เฉพาะรายการแรก ซึ่งสูตรที่จะทำหน้าที่ตรวจสอบว่ามีข้อมูลซ้ำกันหรือไม่ก็คือสูตร CountIF
ถ้าต้องการหารายการข้อมูลที่ซ้ำกันแต่ละรายการมาแสดงต้องพึ่งสูตร Multiple Match ซึ่งทำงานแบบ Array เพื่อหาเลขที่ตำแหน่งรายการมาแสดงก่อนจากนั้นจึงใช้ Index หารายละเอียดแต่ละรายการ
หากข้อมูลซ้ำกันและมีค่าเป็นตัวเลข ก็ให้ใช้สูตรหายอดรวมแยกประเภทของตัวเลขที่มีรายการซ้ำกัน เช่น ชื่อลูกค้าซ้ำ เลขที่บัญชีซ้ำ วันที่ซ้ำ ว่ามียอดขายเป็นเท่าไร โดยใช้สูตร SumIF, Sum-IF Array, Sum Array, หรือ SumProduct ซึ่งจะนำมาอธิบายต่อในภายหลัง
แผนผังต่อไปนี้เดิมทีผมเขียนไว้ด้วยมือ ลูกศิษย์ช่วยปรับแต่งมาให้เห็นชัดเจนขึ้น
