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

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

1. สามารถคำนวณหาคำตอบในปัญหาที่ไม่เคยคิดว่า Excel จะทำได้มาก่อน
2. สูตรสั้นลง และสามารถแก้ไขสูตรได้ง่ายและรวดเร็ว
3. สามารถตรวจสอบความถูกต้องของการคำนวณในทุกขั้นตอน
4. สามารถขยายขนาดหรือเคลื่อนย้ายตำแหน่งเซลล์ เพื่อนำตารางไปใช้ซ้ำในเซลล์อื่น ชีทอื่นหรือแฟ้มอื่นได้ทันที
5. สามารถนำตารางไปใช้ในงานคำนวณอื่นที่ใช้หลักการเดียวกันได้ทันที หรือหลังจากผ่านการปรับปรุงเพียงเล็กน้อย
6. สามารถนำตารางไปใช้งานร่วมกับเมนูคำสั่งของ Excel ได้ทุกเมื่อ แม้แต่การนำข้อมูลไปแสดงผลต่อในรูปกราฟ
7. แฟ้มคำนวณเร็วและมีขนาดแฟ้มเล็กลง แม้จะมีตารางขนาดใหญ่ก็ตาม

ตารางคำนวณคืออะไร

เพื่อทำให้เข้าใจตรงกัน จึงขอให้คำจำกัดความในความหมายของตารางคำนวณก่อนว่า ตารางคำนวณในบทความนี้มิได้หมายถึงพื้นที่ตารางว่างๆที่คุณจะเห็นทันทีบนจอเมื่อเปิดโปรแกรม Excel ขึ้นมาใช้งาน แต่ตารางคำนวณในที่นี้มีความหมายถึงตารางซึ่งคุณสร้างขึ้นมาเองโดยมีข้อมูลบันทึกไว้แล้ว อาจเป็นข้อมูลตัวเลข ตัวอักษร หรือสูตรใดๆก็ได้ ทั้งนี้มีเจตนาเพื่อใช้พื้นที่เซลล์หรือตารางนั้นในการคำนวณหาคำตอบที่คุณต้องการโดยเฉพาะ เช่น ตารางคำนวณค่าแรงที่ต้องจ่ายให้ลูกจ้างทำงานในแต่ละกะ ตารางคำนวณยอดต้นทุนขายแบบ Fist-in First-out ตารางคำนวณหาจำนวนสินค้าที่ต้องวางแผนสั่งผลิต (Material Requirements Planning) หรือตารางคำนวณเพื่อวางแผนการลงทุน (Feasibility Study) เป็นต้น

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

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

ข้อควรพิจารณาก่อนเริ่มสร้างตารางคำนวณ

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

1. กำหนดขอบเขตของความต้องการของคุณที่เป็นไปได้ว่ามีอะไรบ้าง เช่น ต้องการใช้ตารางในชีทหนึ่งเพื่อคำนวณค่าแรงของพนักงานคนหนึ่งซึ่งทำงานในวันหนึ่ง หรือต้องการใช้ตารางในชีทหนึ่งเพื่อคำนวณค่าแรงของพนักงานคนหนึ่งซึ่งทำงานในเดือนหนึ่งโดยแสดงเป็นรายวันต่อเนื่องกันไป หรือต้องการคำนวณค่าแรงของพนักงานทุกคนในวันหนึ่งๆ หรือต้องการคำนวณค่าแรงให้กับพนักงานทุกคนในเดือนหนึ่งโดยแสดงเป็นรายวันต่อเนื่องกันไป หรือมีความต้องการอื่นๆอีกบ้างไหม ทั้งนี้เพื่อหาทางสร้างตารางคำนวณเพียงตารางเดียวแต่ต้องมีความยืดหยุ่นสามารถนำไปใช้คำนวณได้ทุกกรณีตามที่คุณต้องการ(ทั้งที่เคยต้องการในอดีตและอาจจะมีมากขึ้นในอนาคต)
2. ค้นหาเงื่อนไขทั้งหมดที่เกี่ยวข้องในการคำนวณ ซึ่งโดยทั่วไปผู้ที่คลุกคลีกับการบริหารการจ้างแรงงานย่อมต้องมีเงื่อนไขที่ตนนำไปใช้ในงานอยู่แล้ว เช่น เงื่อนไขเกี่ยวกับช่วงเวลาหรือกะที่กำหนดให้ทำงานในแต่ละวันแบ่งเป็นกี่ช่วง แต่ละช่วงใช้เวลาทำงานมากน้อยเพียงไร แบ่งเป็นช่วงเวลาหยุดพักกี่ครั้ง แต่ละครั้งให้หยุดพักได้นานเท่าใด ลูกจ้างต้องตอกบัตรหรือบันทึกการเข้าทำงานไว้อย่างไร บริษัทมีเงื่อนไขในการจ่ายค่าจ้างแรงงานเป็นอย่างไร ซึ่งเงื่อนไขเหล่านี้จะต้องหาทางนำมาใช้ในการคำนวณ
3. ในอดีตจนถึงปัจจุบันใช้วิธีการคำนวณค่าแรงอย่างไร มีขั้นตอนการคำนวณที่ชัดเจนเป็นมาตรฐานที่ใช้กันได้ตลอดมาหรือไม่ หรือมีกรณีพิเศษใดบ้างที่ทำให้ต้องคำนวณวิธีอื่นต่างจากที่เป็นมาตรฐานนั้น ให้เก็บรวบรวมตัวเลขที่ใช้ในแต่ละช่วงแต่ละกรณีเอาไว้เพื่อนำมาใช้เป็นตัวอย่างสร้างตารางคำนวณใน Excel และเพื่อใช้เปรียบเทียบผลคำนวณที่เกิดขึ้นว่าตรงกันหรือไม่
4. ค้นหาความเป็นไปได้ทุกทางที่ลูกจ้างคนหนึ่งจะเข้างานในช่วงเวลาที่กำหนด ซึ่งในกรณีปัญหาการเข้างานนี้พบว่ามีโอกาสความเป็นไปได้ที่จะมีช่วงเวลาทำงานซึ่งสัมพันธ์กับช่วงเวลาที่กำหนดหรือกะหนึ่งๆถึง 7 กรณี เช่น ถ้าสมมติว่ากำหนดให้กะทำงานจาก 8:00 ถึง 10:00 น. ลูกจ้างสามารถมีช่วงทำงานตรงกับกะ(8:00-10:00) อยู่ภายในกะ(8:30-9:00) คร่อมกะทั้งก่อนและหลัง(7:00-12:00) คร่อมกะเพียงช่วงเริ่มงาน(7:00-9:00) คร่อมกะเพียงช่วงเลิกงาน(9:00-12:00) นอกกะช่วงก่อนกะ(6:00-7:00) และนอกกะช่วงหลังกะ(11:00-12:00) ซึ่งคุณจะต้องหาทางคิดสูตรเพียงสูตรเดียวให้สามารถคำนวณได้ครบทั้ง 7 กรณี
5. ขอให้ยึดหลักว่า อย่าพยายามคิดหาสูตรลัดเพื่อคำนวณหาคำตอบให้แล้วเสร็จโดยใช้เซลล์สูตรเพียงเซลล์เดียว เพราะนอกจากสูตรจะต้องยาวเหยียดซ้อนสูตรกันหลายชั้นจนแกะแทบไม่ออกแล้ว ยังอาจเป็นไปไม่ได้เลยที่จะคิดหาสูตรขึ้นมาใช้ได้ และคุณอาจป่วยเป็นโรคกระเพาะเพราะมัวแต่คิดสูตรอยู่ทั้งวันทั้งคืน
6. แทนที่จะคิดสูตรลัดสูตรเดียวเซลล์เดียว ขอให้แบ่งการคำนวณออกเป็นขั้นๆ แล้วหาทางใช้สูตรสำเร็จรูปที่ Excel มีอยู่ให้ได้ก่อน แต่หากไม่มีสูตรสำเร็จรูปที่หาคำตอบได้โดยตรง คุณต้องหาทางนำสูตรสำเร็จรูปที่มีมาใช้ร่วมกันโดยอาจนำมาซ้อนสูตรกันในเซลล์เดียว หรือใช้เซลล์แยกคำนวณทีละขั้น แล้วจึงนำผลลัพธ์ที่ได้จากเซลล์หนึ่งส่งไปคำนวณต่อในสูตรของอีกเซลล์หนึ่ง กลายเป็นที่มาของตารางคำนวณนั่นเอง
7. ลองค้นหาวิธีการคำนวณจากอินเตอร์เน็ต เช่น ค้นหาจาก Google เพื่อเรียนรู้จากตัวอย่างของคนอื่น คุณอาจโชคดีได้ตัวอย่างที่ตรงตามที่คุณต้องการ สามารถนำไปใช้ต่อได้เลย หรือแค่นำไปดัดแปลงเพียงเล็กน้อยก่อนก็ใช้กับงานของคุณได้แล้ว ซึ่งแม้ว่าจะได้ตัวอย่างไม่ตรงกับสิ่งที่ต้องการก็ตาม แต่ตัวอย่างเหล่านั้นน่าจะเป็นแนวทางให้คุณได้คิดพิจารณาและเกิดมุมมองที่กว้างขวางและชัดเจนกว่าเดิม

ขั้นตอนการสร้างตารางคำนวณ

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

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

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

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

รูปตารางนี้เป็นตัวอย่างการคำนวณสูตรคูณแม่ 2 ถึง 5 โดยกำหนดให้นำไปคูณเลข 1 ถึง 5

• ให้แยกเซลล์รับตัวแปรวางไว้ตามแนวคู่ขนานกับพื้นที่คำนวณโดยอาจวางไว้เป็นแนวหัวตารางด้านบน (C2:F2) หรือหัวตารางด้านข้าง (B3:B7) ซึ่งตัวแปรที่ว่านี้เป็นเซลล์รับตัวเลขที่คุณอาจมีความต้องการเปลี่ยนแปลงเป็นค่าอื่นในอนาคต 
• C3:F7 เป็นพื้นที่เซลล์ที่คุณต้องหาทางสร้างสูตรลงไปทุกเซลล์ โดยให้สร้างสูตรลงไปที่เซลล์ C3 ซึ่งเป็นตำแหน่งเซลล์หัวมุมซ้ายบนเพียงเซลล์เดียว ให้มีสูตร =C$2*$B3 จากนั้นให้ copy ไป paste ทุกเซลล์ในพื้นที่ C3:F7

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

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

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

ตัวอย่างตารางคำนวณหาระยะเวลาที่ใช้ในกะ

สมมติว่าลูกจ้างคนหนึ่งเข้างานตั้งแต่เวลา 7:00-9:00 น. ให้สร้างตารางคำนวณหาระยะเวลาที่เขาทำงานในกะที่กำหนดซึ่งเริ่มตั้งแต่เวลา 8:00-10:00 น.

จากคำถามข้างต้นนี้ คุณคงคิดคำตอบในใจได้ว่าต้องทำงานในกะเป็นเวลา 1 ชั่วโมงใช่ไหม เพราะลูกจ้างคนนี้ออกจากงานเวลา 9:00 น. จึงนับระยะเวลาตั้งแต่เริ่มกะตอน 8:00 น. ไปจนถึงเวลาที่เขาออกจากงาน ได้เป็นระยะเวลา 1 ชั่วโมง (8:00-9:00) ซึ่งคงหาทางสร้างสูตรคำนวณได้ไม่ยาก แต่ถ้าเขามีช่วงที่เข้าทำงานหลากหลายกรณีจะใช้สูตรอย่างไร

1. ตรงกับกะ (8:00-10:00)
2. อยู่ภายในกะ (8:30-9:00)
3. คร่อมกะ ทั้งก่อนและหลัง (7:00-12:00)
4. คร่อมกะ เพียงช่วงเริ่มงาน (7:00-9:00)
5. คร่อมกะเพียงช่วงเลิกงาน (9:00-12:00)
6. นอกกะ ช่วงก่อนกะ (6:00-7:00)
7. นอกกะ ช่วงหลังกะ (11:00-12:00)

สูตรคำนวณหาระยะเวลาทำงานที่ใช้ในกะ ในเซลล์ H4 = MAX(  0,  MIN(F4,D4)  -  MAX(E4,C4) ) โดยแยกอธิบายแต่ละส่วนในสูตรได้ ดังนี้

• MIN(F4,D4) เป็นการนำเวลาปลายกะมาเทียบกับเวลาปลาย job (เวลาออกจากงาน) เพื่อหาเวลาสุดท้ายที่เป็นไปได้ที่เกิดขึ้นก่อน
• MAX(E4,C4) เป็นการนำเวลาต้นกะมาเทียบกับเวลาต้น job (เวลาเริ่มเข้างาน) เพื่อหาเวลาสุดท้ายที่เป็นไปได้ที่เกิดขึ้นหลังสุด
• MIN(F4,D4)-MAX(E4,C4) คำนวณหาระยะเวลาทำงานที่ใช้ในกะ
• MAX(0,xxx) ปรับผลลัพธ์ที่คำนวณได้ให้ไม่มีทางต่ำกว่า 0

สูตร = MAX(0,MIN(F4,D4)-MAX(E4,C4)) นี้ เป็นสูตรสัั้นๆที่สามารถใช้แทนสูตร IF เพราะหากจะใช้ IF ในการคำนวณก็จะต้องนำ IF มาซ้อนกันถึง 7 ชั้น

ตารางคำนวณต้องมาก่อน ส่วนสูตรลัดมาทีหลัง

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

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

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

ยกตัวอย่างสูตรคำนวณหาชื่อแฟ้ม ซึ่งดูแล้วยากที่จะจำ และไม่มีทางรู้ว่ามีหลักการคิดมาได้อย่างไร

=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

สูตรนี้มีที่มาจากสูตร

=MID(ข้อความที่มีชื่อแฟ้ม, เลขตำแหน่งเครื่องหมาย [, เลขจำนวนตัวอักษรของชื่อแฟ้ม)

แยกคำนวณแต่ละส่วนได้ตามรูปต่อไปนี้

แทนที่จะใช้สูตรยาวจนจำแทบไม่ไหวในเซลล์ B7 ให้เริ่มจากการใช้สูตรหาค่าที่เกี่ยวข้องกับการแยกคำทีละขั้น ดังนี้

1. เซลล์ A1 ใช้สูตร =CELL("filename") เพื่อหาชื่อแฟ้ม โดยคำตอบที่ได้จะแสดงทั้งชื่อ drive ชื่อโฟลเดอร์ ชื่อแฟ้ม และชื่อชีท
2. เซลล์ B3 และ B4 ใช้สูตร =FIND("[",$A$1) และ =FIND("]",$A$1) เพื่อคำนวณหาตำแหน่งของเครื่องหมายวงเล็บ [ ] ตามลำดับ
3. เซลล์ B5 ใช้สูตร =MID(A1,B3+1,B4-B3-1) เพื่อแยกเฉพาะส่วนที่เป็นชื่อแฟ้มออกมาใช้งาน

การออกแบบโครงสร้างตารางคำนวณร่วมกับสูตรลัด

จากสูตร = MAX(0,MIN(F4,D4)-MAX(E4,C4)) ซึ่งใช้คำนวณหาระยะเวลาที่ใช้ในกะซึ่งได้อธิบายที่ไปที่มาของแต่ละส่วนในสูตรแล้วว่าเป็นอย่างไร จากนี้ขอให้คุณคิดต่อไปอีกว่าจะนำสูตรนี้ไปใช้ในการวางแผนโดยจำเป็นต้องนำสูตรนี้ไปใช้ร่วมกับตัวแปรอื่นๆ เช่น ช่วงเวลาของกะอื่นๆ ช่วงเวลาเข้างานในวันอื่นๆหรือเวลาเข้างานของลูกจ้างคนอื่น ตลอดจนหาทางคำนวณค่าแรงที่ต้องจ่ายออกเป็นตัวเงินนั้น ต้องออกแบบตารางคำนวณให้มีหน้าตาอย่างไรดี

ตัวอย่างนี้ขอสมมติว่าในกำหนดเวลาทำงานแต่ละวัน ตั้งแต่ 6:00-24:00 น.นั้น ถูกแบ่งออกเป็น 8 กะ แต่ละกะเริ่มจากช่วงเวลา 6:00, 8:00, 10:00, 12:00, 13:00, 17:00, 19:00, และ 22:00 โดยกำหนดค่าแรงในช่วงกะที่เริ่ม 8:00, 10:00, 13:00, และ 17:00 ให้ใช้อัตราค่าแรงปกติ ส่วนกะอื่นๆมีอัตราค่าแรง OT 1.5 เท่าของอัตราปกติ

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

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

ตัวเลขเวลาเริ่มกะที่บันทึกไว้ในเซลล์ E5:L5 นั้น ยังถูกนำไปกระจายแยกเป็นเวลาเริ่มกะ (From) และเวลาสิ้นสุดของกะ (To) ไว้ที่เซลล์ E2:L3 เพื่อแสดงให้เห็นชัดเจนขึ้นว่าแต่ละกะมีเวลาเริ่มต้นและสิ้นสุดอย่างไร และยังเผื่อไว้ใช้กับสูตรคำนวณหาระยะเวลาที่ใช้ในกะอีกด้วย จะได้ทำให้สูตรมีการใช้ตำแหน่งอ้างอิงในแนวเดียวกันไปตลอด ช่วยให้สร้างและแกะที่ไปที่มาได้ง่ายขึ้น 

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

กำหนดให้บันทึกเวลาเริ่มงาน (Start) และออกจากงาน (Stop) คู่ขนานกับเลขที่ Job ไว้ในเซลล์ตั้งแต่ C6:C10 และ D6:D10 ตามลำดับ

ตารางตั้งแต่เซลล์ E6:L10 ใช้สูตร =MAX(0,MIN( E$3, $D6 ) - MAX( E$2, $C6 )) เพื่อคำนวณหาระยะเวลาที่ทำงานในแต่ละกะ โดยสร้างสูตรนี้ลงไปในเซลล์ E6 เซลล์เดียวแล้ว copy ไป paste เพื่อใช้คำนวณทุกเซลล์ตั้งแต่ E6:L10

เพื่อแสดงให้เห็นเฉพาะตัวเลขเวลาที่คำนวณได้ ไม่ให้แสดงตัวเลขเวลาที่เท่ากับ 0 จึงเลือกใช้ Format แบบ hh:mm;; และใช้สีพื้นแยกแต่ละกะที่ใช้อัตราค่าแรงปกติออกจากพื้นตารางส่วนที่ใช้อัตราค่าแรงพิเศษ (OT)

จากนั้นสร้างสูตรรวมหายอดระยะเวลาที่ทำงานในช่วงอัตราปกติและช่วงอัตรา OT ไว้ในตารางด้านขวาตั้งแต่เซลล์ N6:O10 ซึ่งเมื่อนำไปคูณกับเลขอัตราในเซลล์ Q4 และ R4 แล้ว ทำให้คำนวณหาค่าแรงแยกประเภทของอัตราแต่ละตัวแสดงให้เห็นในตาราง Q6:R10

วิเคราะห์ลักษณะโครงสร้างตารางคำนวณที่ใช้

1. พื้นที่ตารางที่ใช้รับตัวแปร เช่น B6:B10, C6:C10, D6:D10, และ E5:L5 ถูกวางไว้เป็นแนวคู่ขนานกับแนวตารางสูตรคำนวณ E6:L10
2. สูตรค���นวณในพื้นที่ตาราง E6:L10 เกิดจากสูตรในเซลล์ E6 เพียงเซลล์เดียวที่นำไป paste ต่อให้เซลล์อื่นๆ ทำให้สะดวกในการแก้ไขสูตรไม่ต้องค้นหาและแก้ไขสูตรในเซลล์แต่ละเซลล์
3. ตารางกำหนดเวลาแต่ละช่วงกะ E2:L3 ช่วยชี้ชัดว่าแต่ละกะมีเวลาใด ช่วยให้สร้างสูตรคำนวณได้ง่ายขึ้นและยังทำให้มีความเข้าใจชัดเจนมากขึ้นสำหรับผู้ใช้งานทั่วไป ซึ่งในอนาคตหากไม่ต้องการแสดงพื้นที่ส่วนนี้ก็สามารถโยกย้ายไปซ่อนที่อื่น
4. จุดเด่นของโครงสร้างตารางซึ่งช่วยให้สามารถคำนวณค่าแรงในแต่ละอัตราได้ คือ การใช้ตารางแบ่งออกเป็นแต่ละกะเพื่อแยกคำนวณหาระยะเวลาที่ใช้ในกะนั้นๆก่อน และยังช่วยให้สามารถตรวจสอบความถูกต้องของการคำนวณได้ง่าย ชัดเจนกว่าการคิดสูตรยากๆยาวๆเพียงเพื่อคำนวณแยกแต่ละกะให้แล้วเสร็จในเซลล์เดียว
5. ในแง่ของเลขตัวเงินค่าแรงที่โดยทั่วไปถือเป็นเรื่องปกปิด ได้ถูกแยกคำนวณในตาราง Q6:R10 ช่วยให้สามารถใช้ตารางส่วนนี้ในการบริหารค่าแรงได้โดยตรงและสามารถโยกย้ายตารางไปแยกเก็บไว้เป็นเรื่องลับในชีทอื่นหรือแฟ้มอื่น
6. เลขที่ Job ในตาราง B6:B10 นั้น ในอนาคตสามารถบันทึกรหัสหรือชื่อลงไปแทน จากนั้นจึงนำสูตร Vlookup หาเวลาทำงานมาแสดงโดยอัตโนมัติในตาราง C6:D10 ได้ด้วย หรืออาจใช้คำสั่ง Data > Table เพื่อส่งตัวแปรลงไปแทนเลขที่ Job เพื่อคำนวณลัดหายอดค่าแรงได้ทันทีโดยไม่จำเป็นต้องย้อนกลับมาเพีมรายการใหม่
7. สามารถใช้กับการคำนวณหาค่าแรงได้หลายกรณี เช่น ค่าแรงของลูกจ้างคนหนึ่งในวันหนึ่ง หรือค่าแรงของลูกจ้างคนหนึ่งรายวัน หรือค่าแรงของลูกจ้างเรียงตัวไปทุกคนในแต่ละวัน ซึ่งคุณอาจใช้ชีทเดิมชีทเดียวคำนวณ หรือ copy เป็นชีทใหม่เพื่อคำนวณแยกตามรายบุคคล

หมายเหตุ :

1. การไล่ลำดับงานในตัวอย่างนี้เป็นการไล่จากบนมาล่างหรือจากซ้ายไปขวา ซึ่งในแง่การวางแผนกำหนดการณ์แล้ว เรียกว่า แบบ Push เพื่อหากำหนดเวลาที่งานเสร็จ
2. ถ้าไล่ลำดับงานจากล่างไปบนหรือจากขวามาซ้าย จะกลายเป็นแบบ Pull เพื่อหากำหนดเวลาที่ต้องเริ่มงาน
3. หากต้องการแทรกระยะเวลาหยุดพักของแต่ละ Job ซึ่งย่อมมีผลทำให้กำหนดเวลาเริ่มต้นของ Job ถัดไปช้าลง สามารถปรับเพิ่มได้ง่ายๆโดยสร้างสูตรหากำหนดเวลาเริ่มงานใน Job ถัดไปให้มีค่าเท่ากับ เวลาออกจากงานของ Job ก่อนบวกด้วยระยะเวลาที่หยุดพัก
4. ผู้สนใจตารางคำนวณหาระยะเวลาที่ใช้ในกะ สามารถเรียนรู้ได้จากการอบรมหลักสูตรประยุกต์ใช้ Excel เพื่อวางแผนความต้องการวัสดุ(MRP)และจัดตารางการผลิต หรือหลักสูตรฉลาดใช้สารพัดสูตร Excel อย่างมืออาชีพ หรือ download ตัวอย่างได้ฟรีจาก http://www.excelexperttraining.com/extreme/files/timeplan/timeconsume.xls