สูตร Excel ที่ต้องติดไม้ติดมือไว้ตลอด

ในบรรดาสูตรสำเร็จรูปของ 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 ซึ่งจะนำมาอธิบายต่อในภายหลัง

แผนผังต่อไปนี้เดิมทีผมเขียนไว้ด้วยมือ ลูกศิษย์ช่วยปรับแต่งมาให้เห็นชัดเจนขึ้น

ExpertGuide

 

 

 

 

 

Related Articles

© Copyright 1999

สงวนลิขสิทธิ์ตามกฎหมาย

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

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

ลิงก์เว็บ Excel Expert Training

เว็บสำหรับ เรียนออนไลน์

เว็บสำหรับ เรียนแบบกลุ่ม-ส่วนตัว

ติดตามข่าวสารได้จาก facebook

ถามปัญหาได้ที่ กลุ่มคนรัก Excel

และไลน์กลุ่ม Excel Expert Group

ที่อยู่และการติดต่อ

สมเกียรติ ฟุ้งเกียรติ 7/1 รามคำแหง ซอย 35 หัวหมาก บางกะปิ กทม 10240 โทร 097-140-5555, 02-718-9331

Excel@ExcelExpertTraining.com

sfk234x234