ทำไม Excel ไม่คำนวณผลลัพธ์

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

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

เราสามารถกำหนดให้ Excel คำนวณผลลัพธ์อัตโนมัติดังนี้ครับ

1. เข้าเมนู Tools
2. คลิก Options
3. ที่แถบ Calculation เลือก Automatic
4. OK

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

การ Activate สูตรใหม่ สามารถทำดังนี้ครับ

1. เข้าเมนู Edit
2. เลือก Replace
3. ช่อง Find what: คีย์เครื่องหมาย =
4. ช่อง Replace with: คีย์เครื่องหมาย =
5. คลิกปุ่ม Replace All

สาเหตุที่ 3 เนื่องจากการอ้างอิงเป็นวงกลมโดยไม่ตั้งใจ ซึ่งก็คือ การนำเซลล์ผลลัพธ์ไปใช้คำนวณด้วย ยกตัวอย่างเช่นเรากำลังคีย์สูตรในเซลล์ A2 เป็น =A2+A1 จะเห็นว่าเรานำ A2 ไปใช้คำนวณด้วยทั้ง ๆ ที่ปัจจุบันเรากำลังคีย์สูตรอยู่ในเซลล์ A2 โดยปกติเมื่อเราคีย์สูตรเช่นนี้ลงไป โปรแกรมจะฟ้องว่าเรามีการคำนวณอ้างอิงเป็นวงกลม และหากเราคลิกยอมรับ จะทำให้ไฟล์เราคำนวณ "วน" ไปได้ 1000 ครั้ง (ค่าตั้งต้น) จำนวนรอบการคำนวณสามารถไปกำหนดเองได้ว่าให้คำนวณวนได้กี่รอบ เมื่อครบรอบแล้วก็จะไม่สามารถคำนวณต่อได้ แม้เราทำการเปลี่ยนแปลงหรือแก้ไขสูตร โปรแกรมก็จะไม่แสดงการคำนวณให้อีก โดยปกติแล้วที่ Status bar จะมีการแจ้งผู้ใช้ว่า Circula: ตามด้วยเซลล์ที่เป็นต้นเหตุให้เกิดการอ้างอิงแบบวงกลม

แต่หากการคำนวณวนนั้นอยู่ใน Sheet อื่น หรืออยู่ในไฟล์อื่นที่เปิดขึ้นมาพร้อมกันที่ Status bar จะแสดงแค่ Circular เท่านั้นครับ การแก้ไขให้คลิกดูแต่ละ Sheet และแต่ละไฟล์เพื่อหาเซลล์ที่เป็นต้นเหตุของการอ้างอิงแบบวงกลมแล้วแก้ไขให้ถูกต้อง โดยปกติการคำนวณแบบอ้างอิงเป็นวงกลมควรจะเกิดจากการตั้งใจให้คำนวณเพื่องานบางลักษณะเช่นการทำ Solver ครับ

ลำดับการคำนวณใน Excel

ตัวดำเนินการในการคำนวณ

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

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

รูปแบบของตัวดำเนินการ

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

ตัวดำเนินการ	ความหมาย	ตัวอย่าง
+ (เครื่องหมายบวก)	การบวก	=3+3
– (เครื่องหมายลบ)	การลบ	=3–1, =–1
* (เครื่องหมายคูณ)	การคูณ	=3*3
/ (เครื่องหมายหาร)	การหาร	=-3/3
% (เครื่องหมายเปอร์เซ็นต์)	เปอร์เซ็นต์	=20% --> ความหมายคือ 20/100 หรือ 0.2
^ (เครื่องหมายยกกำลัง)	การยกกำลัง	=3^2

เครื่องหมายที่ใช้ในการเปรียบเทียบ สามารถเปรียบเทียบค่า 2 ค่าได้ตามเครื่องหมายด้านล่าง ซึ่งผลลัพธ์ที่ได้จะเป็นตรรกะในลักษณะใช่หรือไม่ใช่ หรือ True, False

ตัวดำเนินการเปรียบเทียบ	ความหมาย	ตัวอย่าง
= (เครื่องหมายเท่ากับ)	ความเท่ากับ	=A1=B1
> (เครื่องหมายมากกว่า)	ความมากกว่า	=A1>B1
< (เครื่องหมายน้อยกว่า)	ความน้อยกว่า	=A1< B1
>= (เครื่องหมายมากกว่าหรือเท่ากับ)	ความมากกว่าหรือเท่ากับ	=A1>=B1
<= (เครื่องหมายน้อยกว่าหรือเท่ากับ)	ความน้อยกว่าหรือเท่ากับ	=A1<=B1
<> (เครื่องหมายไม่เท่ากับ)	ความไม่เท่ากับ	=A1<>B1

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

ตัวดำเนินการเกี่ยวกับอักขระ	ความหมาย	ตัวอย่าง
& (ampersand)	การเชื่อมคำ, เป็นการเชื่อมคำสองคำหรือมากกว่าให้เป็นข้อความเดียวกัน	="ผม"&" รักคุณ"

ตัวดำเนินการในการอ้างอิง เป็นการรวมช่วงของข้อมูล ดังเครื่องหมายด้านล่าง

ตัวดำเนินการอ้างอิง	ความหมาย	ตัวอย่าง
: (เครื่อง หมายโคลอน, หรือเครื่องหมายทวิภาค)	เป็นเครื่องหมายใช้แทนช่วงข้อมูล ซึ่งเป็นการอ้างอิงถึงช่วงแทนการอ้างอิงแบบเซลล์ต่อเซลล์	B5:B15 เป็นการอ้างอิงเซลล์จาก B5 จนถึง B15
, (เครื่องหมายคอมม่า หรือเครื่องหมายจุลภาค)	เป็นเครื่องหมายใช้ในการรวมช่วงเซลล์	=SUM(B5:B15,D5:D15) เป็นการรวมค่าในเซลล์ B5 ถึง B15 และ D5 ถึง D15 ซึ่งเป็น 2 ช่วงเซลล์
(ช่อง ว่าง) เครื่องหมายอินเตอร์เซกชัน	เป็นการอ้างอิงถึง 2 ช่วงเซลล์ซึ่งมีพื้นที่เหลื่อมกัน	=SUM(B7:D7 C6:C8) ความหมายคือ SUM(C6:D7)

ลำดับ (ศักดิ์) ในการคำนวณของตัวดำเนินการ

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

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

ลำดับที่	ตัวดำเนินการ	ความหมาย	ตัวอย่าง
1.	:	โคลอน	=Sum(A1:B10)
2.		ช่องว่างเดี่ยว	=Sum(A1:C10 B1:D10)
3.	,	คอมม่า	=Sum(A1,B1)
4.	–	ลบ	–1
5.	%	เปอร์เซ็นต์	25%
6.	^	ยกกำลัง	=2^3
7.	* และ /	คูณและหาร	=2*3/4
8.	+ และ –	บวกและหัก	=2-3+1
9.	&	เชื่อมข้อความเป็นข้อความเดียว	="ผม"&" รักคุณ"
10.	= < > <= >= <>	เครื่องหมายเปรียบเทียบ	=A1<C2

การใช้วงเล็บ

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

=5+2*3 คำตอบจะได้ 11 เนื่องจากเครื่องหมาย * มีศักดิ์สูงกว่าเครื่องหมาย + จึงมีการดำเนินการก่อน แต่หาก กำหนดเป็น
=(5+2)*3 คำตอบจะได้ 21 เนื่องจากกำหนดให้ 5 กับ 2 ทำการบวกกันก่อน โดยใช้เครื่องหมายวงเล็บ จากนั้นนำผลลัพธ์ในเครื่องหมายวงเล็บไปดำเนินการต่อไป

จาก ตัวอย่างด้านล่าง เป็นการนำ B4 บวกกับ 25 ก่อน แล้วค่อยนำ SUM(D5:F5) มาหาร ไม่เช่นนั้นโปรแกรมจะนำ 25 หารด้วย SUM(D5:F5) ก่อนแล้วค่อยบวกกับ B4

=(B4+25)/SUM(D5:F5)

การสั่งให้คำนวณแผ่นงานหรือสมุดงาน

ในการสั่งให้ Excel ทำการคำนวณสามารถใช้ Shortcut ดังข้างล่างนี้

1. กด F9 จะทำการคำนวณสูตรที่มีการเปลี่ยนแปลงหลังจากการคำนวณครั้งสุดท้ายในสมุดงานที่เปิดอยู่ทั้งหมด ถ้าสมุดงานถูกตั้งค่าเป็นการคำนวณอัตโนมัติ ก็ไม่จำเป็นต้องกดเป็น F9 เพื่อคำนวณ
2. กด SHIFT+F9 จะทำการคำนวณสูตรที่มีการเปลี่ยนแปลงหลังจากการคำนวณครั้งสุดท้ายบนแผ่นงานปัจจุบัน
3. กด CTRL+ALT+F9 จะทำการคำนวณสูตรทั้งหมดในสมุดงานทั้งหมดทุกสมุดงานที่กำลังทำงาน โดยไม่สนใจว่าจะมีการเปลี่ยนแปลงจากครั้งสุดท้ายที่มีการคำนวณหรือไม่
4. กด CTRL+SHIFT+ALT+F9 จะทำการตรวจสอบสูตรที่อ้างถึงอีกครั้ง จากนั้นจะทำการคำนวณสูตรทั้งหมดในสมุดงานที่เปิดอยู่ทั้งหมด โดยไม่คำนึงถึงว่ามีเปลี่ยนมาจากครั้งที่แล้วหรือไม่