เรื่องอะไรเอ่ย ยากที่สุดในการใช้ Excel

หลักการสร้างงานที่ดีใน Excel ให้แบ่งพื้นที่ใช้งานเป็น 3 ส่วน ซึ่งผมเรียกย่อๆว่า I C O ได้แก่

1. ตาราง Input ใช้สำหรับจัดเก็บฐานข้อมูล
2. ตาราง Calculate ใช้สำหรับสร้างสูตรคำนวณ
3. ตาราง Output เป็นหน้ารายงานที่ต้องการ

ตาราง Input มีข้อกำหนดแน่นอนว่า หัวตารางต้องใช้พื้นที่เพียง 1 row ถัดจากนั้นต้องติดกับรายการข้อมูลต่อกันไปเรื่อยๆ ห้ามมี row เว้นแบบขาดช่วงเด็ดขาด และพื้นที่รอบข้างของตารางฐานข้อมูลต้องห่างจากเซลล์เรื่องที่ไม่เกี่ยวข้องอย่างน้อย 1 เซลล์รอบข้าง

ซึ่งผมจะสอนให้จำง่ายๆว่า หัวตาราง 1 row ห้ามเว้น และห้ามติด หากผิดจากกฎ 3 ข้อนี้แล้วเมนู Data จะทำงานผิดเพี้ยนไปเลย หรืออาจใช้สร้าง Pivot Table ไม่ได้ด้วยซ้ำ

ตารางรายงาน Output จะออกแบบหน้าตาแบบใดก็ได้ ขอให้ตอบสนองต่อความต้องการของผู้ใช้ได้ก็พอแล้ว

ส่วนตาราง Calculate หรือตารางที่ใช้คำนวณนี่แหละ เป็นตารางที่ชี้เป็นชี้ตายให้กับการใช้ Excel เลยทีเดียว ก่อนอื่นขอแนะนำให้อ่านรายละเอียดเรื่องตารางคำนวณได้จาก

https://excelexperttraining.com/home/online/manuals/calculation-module

อย่างไรก็ตามเนื้อหาที่ผมได้อธิบายไว้นั้นยังไม่สมบูรณ์นัก จึงขออธิบายเพิ่มดังนี้

ระดับความเก่งมีกี่ขั้น

เมื่อก่อนผมคิดว่าคนที่จะเก่ง Excel ได้ ต้องผ่านขั้นตอน 4 ขั้นตามนี้

1. สามารถใช้เมนู
2. สามารถใช้สูตร
3. สามารถใช้ VBA
4. สามารถใช้สูตร Array

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

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

ตารางคำนวณที่ยืดหยุ่นต้องใช้ $ และไม่ต้องใส่ $

เครื่องหมาย $ ที่ใส่กำกับตำแหน่งอ้างอิงไว้จะช่วยทำให้เมื่อ copy สูตรไปใช้ที่อื่น สูตรจะปรับตำแหน่งอ้างอิงให้เอง ผมเชื่อว่าทุกคนที่ใช้ Excel สร้างสูตรมานานต้องทราบเรื่องนี้กันดีอยู่แล้ว แต่เครื่องหมาย $ จะกลายเป็นปัญหาใหญ่เมื่อ copy ตารางทั้งตารางไปใช้ที่อื่น เช่นเครื่องหมาย $ ที่ใส่ไว้หน้า row 4 พอ copy ไปวางที่อื่นยังไงๆก็ยังลิงก์ค่าจาก row 4

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

ตารางคำนวณที่ยืดหยุ่นต้องไม่ใช้สูตรที่กำหนดขอบเขตตาราง

สูตร VLookup, Match, Index เป็นสูตรที่จะทำงานได้ในพื้นที่ซึ่งอ้างอิงไว้ในสูตรเท่านั้นใช่ไหม เช่น =Index(B2:Z200,2,3) เป็นสูตรที่จะหาค่าจากขอบเขตตารางช่วงตั้งแต่ B2:Z200 เท่านั้น ซึ่งหากนำสูตรที่กำหนดขอบเขตพื้นที่มาใช้ในตารางคำนวณ จะเกิดอะไรขึ้นหากจำเป็นต้องขยายพื้นที่ตารางออกไป

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

แทนที่จะใช้สูตร VLookup Match Index ในการดึงค่าจากตารางคำนวณที่มีการขยายตัว ผมจะเลือกใช้สูตร Offset หรือ Indirect+Address แทน เพราะสองสูตรนี้จะหาค่าจากเซลล์ใดก็ได้ในตารางเพียงแค่ขอให้ทราบตำแหน่งของเซลล์นั้นว่าอยู่ที่ row ใด column ใดก่อน เช่น

=Offset(A1,1000000,10000) จะดึงค่าจากเซลล์ที่อยู่ใน row ที่ 1 ล้านและ column ที่ 1 หมื่นนับถัดจากเซลล์ A1 หรือเทียบได้กับสูตร Indirect(address(1000001,10001))

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

หมายเหตุ

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

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