เชี่ยวชาญ Excel Equation Solver: 5 ตัวอย่างปฏิบัติจริง

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

วิธีแก้สมการใน Excel

ก่อนเริ่มแก้สมการใน Excel เรามาดูกันก่อนว่าสมการประเภทไหนจะแก้ได้ด้วยวิธีใด

ประเภทของสมการที่แก้ได้ใน Excel:

มีสมการหลายประเภท แต่ไม่ใช่ทุกประเภทที่แก้ได้ใน Excel ในบทความนี้เราจะแก้สมการประเภทต่อไปนี้:

• สมการกำลังสาม (Cubic Equation)
• สมการกำลังสอง (Quadratic Equation)
• สมการเชิงเส้น (Linear Equation)
• สมการเลขยกกำลัง (Exponential Equation)
• สมการดิฟเฟอเลนเชียล (Differential Equation)
• สมการไม่เชิงเส้น (Non-linear Equation)

เครื่องมือ Excel สำหรับแก้สมการ:

มีเครื่องมือเฉพาะสำหรับแก้สมการใน Excel เช่น Excel Solver Add-in และฟีเจอร์ Goal Seek นอกจากนี้ยังสามารถแก้สมการด้วยวิธีตัวเลข/ด้วยตนเอง ระบบเมทริกซ์ และวิธีอื่นๆ ได้อีกด้วย

5 ตัวอย่างการแก้สมการใน Excel

1. การแก้สมการพหุนามใน Excel

สมการพหุนาม (Polynomial) เป็นการรวมของตัวแปรและสัมประสิทธิ์ด้วยการดำเนินการเลขคณิต ในส่วนนี้เราจะลองแก้สมการพหุนามต่างๆ เช่น สมการกำลังสาม กำลังสอง เชิงเส้น ฯลฯ

1.1 การแก้สมการกำลังสาม (Cubic Equation)

สมการพหุนามที่มี次สูงสุดเป็น 3 เรียกว่าสมการพหุนามกำลังสาม ที่นี่เราจะแสดง 2 วิธีในการแก้สมการกำลังสามใน Excel

i. ใช้ Goal Seek

เราจะใช้ฟีเจอร์ Goal Seek ของ Excel เพื่อแก้สมการกำลังสามนี้

สมมติเรามีสมการ:

Y = 5X³ - 2X² + 3X - 6

เราต้องแก้สมการนี้และหาค่า X

📌 ขั้นตอน:

• แรกเริ่ม แยกสัมประสิทธิ์ออกเป็น 4 เซลล์

• เราต้องการหาค่า X สมมติค่าเริ่มต้นของ X เป็น ศูนย์ (0) และใส่ 0 ในเซลล์ที่สอดคล้องกัน

• สร้างสูตรสมการที่กำหนดในเซลล์ของ Y
• กด Enter เพื่อได้ค่า Y

=C5*C7^3+D5*C7^2+E5*C7+F5

• กด Enter อีกครั้งเพื่อได้ค่า Y

ตอนนี้เราจะใช้ฟีเจอร์ Goal Seek

• คลิกที่แท็บ Data
• เลือก Goal Seek จากส่วน What-If Analysis

• กล่องโต้ตอบ Goal Seek จะปรากฏขึ้น

เราต้องใส่การอ้างอิงเซลล์และค่าในที่นี้

• เลือก เซลล์ H5 เป็น Set cell เซลล์นี้มีสมการ
• เลือก เซลล์ C7 เป็น By changing cell ซึ่งเป็นตัวแปร ค่าของตัวแปรนี้จะเปลี่ยนหลังการดำเนินการ

• ใส่ 20 ในกล่อง To value ซึ่งเป็นค่าที่สมมติสำหรับสมการ

• สุดท้าย กดปุ่ม OK

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

• กด OK อีกครั้ง

นี่คือค่าสุดท้ายของ X

ii. ใช้ Solver Add-In

Solver เป็น Add-in ในส่วนนี้เราจะใช้ Add-in นี้เพื่อแก้สมการที่กำหนดและได้ค่าตัวแปร

Solver Add-in ไม่มีใน Excel โดยค่าเริ่มต้น เราต้องเพิ่ม Add-in นี้ก่อน

📌 ขั้นตอน:

• ตั้งค่าตัวแปรเป็น ศูนย์ (0) ในชุดข้อมูล

• ไปที่ File >> Options
• หน้าต่าง Excel Options จะปรากฏ
• เลือก Add-ins ด้านซ้าย
• เลือก Excel Add-ins และคลิกปุ่ม Go

• หน้าต่าง Add-ins จะปรากฏ
• ติ๊กถูก Solver Add-in และคลิก OK

• เราจะเห็น Solver Add-in ในแท็บ Data
• คลิกที่ Solver

• หน้าต่าง Solver Parameters จะปรากฏ

• ใส่การอ้างอิงเซลล์ของสมการในกล่อง Set Objective
• ติ๊กถูก Value of และใส่ 20 ในกล่องที่สอดคล้อง
• ใส่การอ้างอิงเซลล์ของตัวแปร
• สุดท้าย คลิก Solve

• เลือก Keep Solver Solution แล้วกด OK

• ดูชุดข้อมูล

เราจะเห็นว่าค่าตัวแปรได้เปลี่ยนไปแล้ว

1.2 การแก้สมการกำลังสอง (Quadratic Equation)

สมการพหุนามที่มีชั้นสูงสุดเป็น 2 เรียกว่าสมการพหุนามกำลังสอง ที่นี่เราจะแสดง 2 วิธีในการแก้สมการกำลังสองใน Excel

เราจะแก้สมการกำลังสองต่อไปนี้:

Y = 3X² + 6X - 5

i. แก้ด้วยฟีเจอร์ Goal Seek

เราจะแก้สมการกำลังสองนี้โดยใช้ฟีเจอร์ Goal Seek มาดูส่วนด้านล่าง

📌 ขั้นตอน:

• แรกเริ่ม แยกสัมประสิทธิ์ของตัวแปร

• ตั้งค่าเริ่มต้นของ X เป็นศูนย์ (0)
• ใส่สมการที่กำหนดโดยใช้อ้างอิงเซลล์ใน เซลล์ G5

=C5*C7^2+D5*C7+E5

• กดปุ่ม Enter ตอนนี้

เราได้ค่า Y โดยพิจารณาว่า X เป็นศูนย์

ตอนนี้เราจะใช้ฟีเจอร์ Goal Seek เพื่อได้ค่า X เราแสดงวิธีเปิดใช้งาน Goal Seek ไปแล้ว

• ใส่การอ้างอิงเซลล์ของตัวแปรและสมการในกล่องโต้ตอบ Goal Seek
• สมมติค่าสมการเป็น 18 และใส่ในกล่องส่วน To value

• สุดท้าย กด OK

เราได้ค่าสุดท้ายของตัวแปร X

ii. ใช้ Solver Add-In

เราแสดงวิธีเพิ่ม Solver Add-in ใน Excel ไปแล้ว ในส่วนนี้เราจะใช้ Solver นี้เพื่อแก้สมการต่อไปนี้

📌 ขั้นตอน:

• ใส่ ศูนย์ (0) ใน เซลล์ C7 เป็นค่าเริ่มต้นของ X
• ใส่สูตรต่อไปนี้ใน เซลล์ G5

• กดปุ่ม Enter

• เข้าสู่ Solver Add-in ตามที่แสดงไว้ก่อนหน้า
• เลือกการอ้างอิงเซลล์ของสมการเป็นวัตถุประสงค์ (Objective)
• ใส่การอ้างอิงเซลล์ของตัวแปร
• ตั้งค่าสมการเป็น 18
• สุดท้าย คลิกตัวเลือก Solve

• ติ๊กถูก Keep Solver Solution จากหน้าต่าง Solver Results

• สุดท้าย คลิกปุ่ม OK

2. การแก้สมการเชิงเส้น (Linear Equations)

สมการที่มีตัวแปรใดๆ ที่มีชั้นสูงสุดเป็น 1 เรียกว่าสมการเชิงเส้น

2.1 ใช้ระบบเมทริกซ์ (Matrix System)

ฟังก์ชัน MINVERSE ส่งคืนเมทริกซ์ผกผันสำหรับเมทริกซ์ที่เก็บในอาเรย์

ฟังก์ชัน MMULT ส่งคืนผลคูณเมทริกซ์ของอาเรย์สองอาเรย์ อาเรย์ที่มีจำนวนแถวเท่ากับ array1 และคอลัมน์เท่ากับ array2

วิธีนี้จะใช้ระบบเมทริกซ์เพื่อแก้สมการเชิงเส้น ที่นี่มี 3 สมการเชิงเส้น กับ 3 ตัวแปร x, y, และ z สมการคือ:

3x + 2y + z = 8

11x - 9y + 23z = 27

8x - 5y = 10

เราจะใช้ฟังก์ชัน MINVERSE และ MMULT เพื่อแก้สมการที่กำหนด

📌 ขั้นตอน:

• แรกเริ่ม แยกสัมประสิทธิ์ตัวแปรในเซลล์ต่างๆ และจัดรูปแบบเป็นเมทริกซ์
• เราสร้าง 2 เมทริกซ์ หนึ่งเป็นสัมประสิทธิ์ของตัวแปร อีกหนึ่งเป็นค่าคงที่

• เพิ่มอีก 2 เมทริกซ์สำหรับการคำนวณ

• หาเมทริกซ์ผกผันของ A โดยใช้ฟังก์ชัน MINVERSE
• ใส่สูตรต่อไปนี้ใน เซลล์ C7

=MINVERSE(C5:E7)

นี่คือสูตรอาเรย์ (Array Formula)

• กดปุ่ม Enter

เมทริกซ์ผกผันถูกสร้างขึ้นสำเร็จ

• นำสูตรที่อิงจากฟังก์ชัน MMULT ไปใช้ใน เซลล์ H9

=MMULT(C9:E11,H5:H7)

เราใช้เมทริกซ์สองอันขนาด 3x3 และ 3x1 ในสูตร และเมทริกซ์ผลลัพธ์มีขนาด 3x1

• กดปุ่ม Enter อีกครั้ง

นี่คือคำตอบของตัวแปรที่ใช้ในสมการเชิงเส้น

2.2 ใช้ Solver Add-In

เราจะใช้ Solver Add-in เพื่อแก้ 3 สมการกับ 3 ตัวแปร

📌 ขั้นตอน:

• แรกเริ่ม แยกสัมประสิทธิ์ตามที่แสดงก่อนหน้านี้

• เพิ่ม 2 ส่วนสำหรับค่าตัวแปรและใส่สมการ
• ตั้งค่าเริ่มต้นของตัวแปรเป็น ศูนย์ (0)

• ใส่สมการ 3 สมการต่อไปนี้ในเซลล์ E10 ถึง E12

=C5*C10+D5*C11+E5*C12

=C6*C10+D6*C11+E6*C12

=C7*C10+D7*C11+E7*C12

• ไปที่ฟีเจอร์ Solver
• ตั้งเซลล์อ้างอิงของสมการแรกเป็นวัตถุประสงค์ (Objective)
• ตั้งค่าสมการเป็น 8
• ใส่ช่วงของตัวแปรในกล่องที่ทำเครื่องหมาย
• คลิกปุ่ม Add

• หน้าต่าง Add Constraint จะปรากฏ
• ใส่การอ้างอิงเซลล์และค่าตามที่ทำเครื่องหมายในภาพด้านล่าง

• ใส่เงื่อนไขที่สอง
• สุดท้าย กด OK

• เพิ่มเงื่อนไขแล้ว กดปุ่ม Solve

• ดูชุดข้อมูล

เราเห็นว่าค่าตัวแปรได้เปลี่ยนไปแล้ว

2.3 ใช้กฎของครามเมอร์ (Cramer's Rule) แก้สมการพร้อมกัน 3 ตัวแปรใน Excel

เมื่อสมการเชิงเส้นสองสมการขึ้นไป มีตัวแปรเหมือนกันและสามารถแก้พร้อมกันได้ เรียกว่าสมการพร้อมกัน (Simultaneous Equations) เราจะแก้สมการพร้อมกันโดยใช้ กฎของครามเมอร์ (Cramer's Rule) ฟังก์ชัน MDETERM จะใช้หา determinant

ฟังก์ชัน MDETERM ส่งคืน determinant ของเมทริกซ์ในอาเรย์

📌 ขั้นตอน:

• แยกสัมประสิทธิ์เป็น LHS (Left Hand Side) และ RHS (Right Hand Side)

• เพิ่ม 4 ส่วนเพื่อสร้างเมทริกซ์จากข้อมูลที่มี

• ใช้ข้อมูล LHS สร้าง Matrix D

• สร้าง Matrix Dx โดยแทนสัมประสิทธิ์ของ X ด้วย RHS

• เช่นเดียวกัน สร้างเมทริกซ์ Dy และ Dz

• ใส่สูตรต่อไปนี้ใน เซลล์ F11 เพื่อหา determinant ของ Matrix D

=MDETERM(C10:E12)

• กดปุ่ม Enter

• หา determinant ของ Dx, Dy, Dz โดยใช้สูตรต่อไปนี้

=MDETERM(C14:E16)

=MDETERM(C18:E20)

=MDETERM(C22:E24)

• ไปที่ เซลล์ I6
• หาร determinant ของ Dx ด้วย D เพื่อคำนวณค่า X

=F15/F11

• กด Enter เพื่อได้ผลลัพธ์

• เช่นเดียวกัน หาค่า Y และ Z ด้วยสูตร:

=F19/F11

=F23/F11

สุดท้าย เราแก้สมการพร้อมกันและได้ค่าตัวแปร 3 ตัวแล้ว

3. การแก้สมการไม่เชิงเส้น (Nonlinear Equations) ใน Excel

สมการที่มีชั้นเป็น 2 หรือมากกว่า 2 และไม่สร้างเส้นตรง เรียกว่า สมการไม่เชิงเส้น (Non-linear Equation)

ในวิธีนี้ เราจะแก้สมการไม่เชิงเส้นใน Excel โดยใช้ฟีเจอร์ Solver ของ Excel

เรามีสมการไม่เชิงเส้น 2 สมการ:

📌 ขั้นตอน:

• ใส่สมการและตัวแปรในชุดข้อมูล

• พิจารณาค่าตัวแปรเป็น ศูนย์ (0) และใส่ในชุดข้อมูล

• ใส่ 2 สมการใน เซลล์ C5 และ C6 เพื่อได้ค่า RHS

=C9^2+C10^2-25

=C9-C10^2

• เพิ่มแถวใหม่ในชุดข้อมูลสำหรับผลรวม (Sum)
• ใส่สมการต่อไปนี้ใน เซลล์ C12

=SUM(C5:C6)

• กด Enter ได้ผลรวมของ RHS ของทั้งสองสมการ

• ใช้ฟีเจอร์ Solver ของ Excel
• ใส่การอ้างอิงเซลล์ในกล่องที่ทำเครื่องหมาย
• ตั้ง Value of 0
• คลิก Add เพื่อเพิ่มเงื่อนไข (Constraints)

• เพิ่ม เงื่อนไขที่ 1 ตามภาพ
• กด Add อีกครั้งสำหรับ เงื่อนไขที่ 2

• ใส่การอ้างอิงเซลล์และค่า
• สุดท้าย กด OK

• เห็นว่าเงื่อนไขถูกเพิ่มใน Solver แล้ว
• คลิกปุ่ม Solve

• ติ๊กถูก Keep Solver Solution แล้วคลิก OK

• ดูชุดข้อมูลตอนนี้

เราได้ค่า X และ Y สำเร็จแล้ว

4. การแก้สมการเลขยกกำลัง (Exponential Equation)

สมการเลขยกกำลัง (Exponential Equation) เป็นสมการที่มีตัวแปรและค่าคงอยู่ ในสมการเลขยกกำลัง ตัวแปรถูกพิจารณาว่าเป็นกำลังหรือชั้นของฐานหรือค่าคงอยู่

ในวิธีนี้ เราจะแสดงวิธีแก้สมการเลขยกกำลังโดยใช้ฟังก์ชัน EXP

ฟังก์ชัน EXP ส่งคืนค่า e ยกกำลังตัวเลขที่กำหนด

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

ที่นี่:

Po = ประชากรปัจจุบันหรือเริ่มต้น

R = อัตราการเติบโต

T = เวลา

P = ประชากรประมาณการในอนาคต

สมการนี้มีส่วนเลขยกกำลัง ซึ่งเราจะใช้ฟังก์ชัน EXP

📌 ขั้นตอน:

• ที่นี่ มีประชากรปัจจุบัน อัตราการเติบโตเป้าหมาย และจำนวนปีในชุดข้อมูล เราจะคำนวณประชากรอนาคตโดยใช้ค่าเหล่านี้

• ใส่สูตรต่อไปนี้ที่อิงจากฟังก์ชัน EXP ใน เซลล์ C7

=ROUND(C4*EXP(C5*C6),0)

เราใช้ฟังก์ชัน ROUND เนื่องจากประชากรต้องเป็นจำนวนเต็ม

• กดปุ่ม Enter เพื่อได้ผลลัพธ์

นี่คือประชากรในอนาคตหลัง 10 ปี ตามอัตราการเติบโตที่สมมติ

5. การแก้สมการดิฟเฟอเลนเชียล (Differential Equations) ใน Excel

สมการที่มีอนุพันธ์อย่างน้อยหนึ่งของฟังก์ชันที่ไม่ทราบ เรียกว่า สมการดิฟเฟอเลนเชียล (Differential Equation) อนุพันธ์อาจเป็นธรรมดาหรือเป็นอนุพันธ์บางส่วน

ที่นี่เราจะแสดงวิธีแก้สมการดิฟเฟอเลนเชียลใน Excel เราต้องหา dy/dt อนุพันธ์ของ y เทียบกับ t เราบันทึกข้อมูลทั้งหมดในชุดข้อมูล

📌 ขั้นตอน:

• ตั้งค่าเริ่มต้นของ n, t, และ y จากข้อมูลที่กำหนด

• ใส่สูตรต่อไปนี้ใน เซลล์ C6 สำหรับ t

=C5+$G$5

สูตรนี้สร้างจาก t(n-1)

• กดปุ่ม Enter

• ใส่อีกสูตรใน เซลล์ D6 สำหรับ y

=D5+(C5-D5)*$G$5

สูตรนี้สร้างจากสมการของ y(n+1)

• กด Enter อีกครั้ง

• ขยายค่าจนถึงค่าสูงสุดของ t ซึ่งคือ 1.2

เราต้องการวาดกราฟโดยใช้ค่า t และ y

• ไปที่แท็บ Insert
• เลือกกราฟจากกลุ่ม Charts

• ดูกราฟ

นี่คือกราฟ y เทียบ t

• ดับเบิลคลิกที่กราฟ และปรับค่าต่ำสุด/สูงสุดของแกนกราฟ ปรับแกนแนวนอน

• ปรับแกนแนวตั้ง

• หลังปรับแกนแล้ว กราฟจะเป็นดังนี้

ตอนนี้เราจะหาสมการดิฟเฟอเลนเชียล

• คำนวณสมการดิฟเฟอเลนเชียลด้วยตนเองและใส่ในชุดข้อมูล

• สร้างสูตรจากสมการนี้และใส่ใน เซลล์ E5

=-1+C5+1.5*EXP(-C5)

• กด Enter และลากไอคอน Fill Handle

• กลับไปที่กราฟ คลิกขวาเมาส์
• เลือก Select Data จากเมนูบริบท (Context Menu)

• เลือก Add จากหน้าต่าง Select Data Source

• เลือกเซลล์คอลัมน์ t เป็นค่า X และเซลล์คอลัมน์ y_exact เป็นค่า Y ในหน้าต่าง Edit Series

• ดูกราฟอีกครั้ง

สรุป

ในบทความนี้ เราอธิบายวิธีแก้สมการประเภทต่างๆ หวังว่าจะตอบสนองความต้องการของคุณในการแก้สมการหลายประเภทใน Excel โปรดเยี่ยมชมเว็บไซต์ Exceldemy.com และให้คำแนะนำในกล่องความคิดเห็น