มาใช้ Excel ช่วยดำเนินการกับเมตริกซ์กัน ตอน 4 (How to use MS Excel to operate with matrix-4)

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

จากบทความเรื่องการ การหาเมตริกซ์ผกผันด้วยสูตร Excel เราได้ทราบวิธีการใช้ Excel หาเมตริกซ์ผกผันแล้ว กลับมาพิจารณาระบบสมการเชิงเส้น (Linear Equation System) โดยระบบสมการเชิงเส้นจำนวน n สมการสามารถเขียนอยู่ในรูปแบบของเมตริกซ์ได้ คือ

[A]{X} = {B}            (a)

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

[A]{X} = {B}

หากเมตริกซ์ A ไม่เป็น Singular Matrix เราสามารถนำเมตริกซ์ผกผันของเมตริกซ์ A คูณเข้าด้านซ้ายและขวาของสมการที่ a ได้ดังนี้

[A]^-1[A]{X} = [A]^-1{B}

[I]{X} = [A]^-1{B}

{X} = [A]^-1{B}

ดังนั้นคำตอบของระบบสมการเชิงเส้นจะเท่ากับผลคูณของเมตริกซ์ผกผันของเมตริกซ์ A กับเวกเตอร์ B นั่นเอง โดยในการใช้ Excel จะมีขั้นตอนดำเนินการดังนี้

1.     คำนวณหา เมตริกซ์ผกผันของเมตริกซ์ A โดยใช้สูตร Excel : MINVERSE

2.     หากเมตริกซ์ผกผันของเมตริกซ์ A สามารถหาค่าได้ ให้นำมาคูณกับเวกเตอร์ B โดยใช้สูตร Excel : MMULT

มาดูตัวอย่างกันครับ สมมุติมีระบบสมการ 3 ตัวแปร ดังนี้

4X -4Y      = 400          (1)

-X+4Y-2Z = 400          (2)

-2Y+4Z     = 400          (3)

จากระบบสมการดังกล่าว สามารถเขียนในรูประบบสมการ [A]{X} = {B} ได้ดังภาพที่ 1 เรามาเริ่มตามขั้นตอนกันเลยครับ

1. หาเมตริกซ์ผกผันของ [A] โดยเลือก F9:H11

2. พิมพ์สูตร Excel ดังนี้ =MINVERSE(B4:D6)

3. กดปุ่ม Ctrl + Shift + Enter เพื่อเป็นการคำนวณแบบ Array

4. จะได้ เมตริกซ์ผกผัน ในเซลล์ F9:H11 ดังแสดงในภาพที่ 1

5. หาคำตอบระบบสมการ โดยเลือก F14:F16

6. พิมพ์สูตร Excel ดังนี้ =MMULT(F9:H11,F4:F6)

7. กดปุ่ม Ctrl + Shift + Enter เพื่อเป็นการคำนวณแบบ Array

8. จะได้ เวกเตอร์ X  ในเซลล์ F14:F16 ซึ่งเป็นคำตอบของระบบสมการ ดังแสดงในภาพที่ 1

ภาพที่ 1 การใช้ excel ช่วยแก้ระบบสมการเชิงเส้น

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

หัวข้อที่เกี่ยวข้อง

การใช้ Excel หา Determinant ของเมตริกซ์

การใช้ Excel คูณระหว่างเมตริกซ์

         การใช้ Excel คำนวณหาเมตริกซ์ผกผัน

มาใช้ Excel ช่วยดำเนินการกับเมตริกซ์กัน ตอน 3 (How to use MS Excel to operate with matrix-3)

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

มาดูนิยามกันก่อนครับ 

           หากเมตริกซ์ A เป็นเมตริกซ์จัตุรัสและเป็นเมตริกซ์ไม่เอกฐาน (Nonsingular matrix) แล้ว 

I คือ เมตริกซ์เอกลักษณ์ (Identity Matix)

การคำนวณหาค่าเมตริกซ์ผกผัน โดยใช้ Excel จะใช้สูตร MINVERSE โดยมีรูปแบบการใช้สูตร Excel ดังนี้

MINVERSE (array)

โดย Excel จะคืนค่า #VALUE! เมื่อมีข้อผิดพลาด 2 รูปแบบดังนี้

1. สมาชิกใน array อย่างน้อยเป็นค่าว่าง หรือเป็นข้อความ

2. จำนวนหลักและจำนวนแถวของ array ไม่เท่ากัน  (ไม่เป็น เมตริกซ์จัตุรัส)

Excel จะคืนค่า #NUM! สำหรับเมตริกซ์ที่หาค่าเมตริกซ์ผกผันไม่ได้ (ค่า Determinant = 0) หรือที่เรียกว่าเมตริกซ์เอกฐาน (Singular matrix)

การใช้สูตร Excel ขอยกตัวอย่างเมตริกซ์ A ในหัวข้อการ การใช้สูตร Excel หา Determinant ของเมตริกซ์ ดังรูปที่ 1 ซึ่งมีค่า Determinant  = 32 จะสามารถดำเนินการหาเมตริกซ์ผกผันได้ดังนี้

1. เลือกช่วงข้อมูล D7:F9  (เท่ากับจำนวนสมาชิก  3x3)

2. พิมพ์สูตร excel ดังนี้ = MINVERSE (D2:F4)

3. กดปุ่ม Ctrl + Shift + Enter เพื่อเป็นการคำนวณแบบ Array

4. จะได้ เมตริกซ์ผกผัน ดังแสดงในภาพที่ 1

ภาพที่ 1 ผลการใช้สูตร Excel หาเมตริกซ์ผกผัน

การตรวจสอบค่าทำได้โดยใช้สูตร Excel : MMULT หาผลคูณระหว่าง A^-1*A จะได้ผลลัพธ์เป็นเมตริกซ์เอกลักษณ์ ดังแสดงในภาพที่ 1

หัวข้อที่เกี่ยวข้อง

การใช้ Excel หา Determinant ของเมตริกซ์

การใช้ Excel คูณระหว่างเมตริกซ์
การใช้ Excel แก้ระบบสมการเชิงเส้น 

มาใช้ Excel ช่วยดำเนินการกับเมตริกซ์กัน ตอน 2 (How to use MS Excel to operate with matrix-2)

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

        เมตริกซ์ที่สามารถคูณกันได้จะต้องมีเป็นไปตามเงื่อนไขดังนี้ครับ ถ้า A มีขนาด ixc และ B มีขนาด cxj แล้ว

จากนิยามอธิบายกันแบบง่ายๆครับว่า จำนวนหลักของเมตริกซ์ A จะต้องเท่ากับจำนวนแถวของเมตริกซ์ B จีงจะสามารถคูณระหว่างเมตริกซ์ได้ และที่สำคัญการคูณเมตริกซ์ไม่มีสมบัติการสลับตำแหน่ง นั่นคือ A*B ไม่เท่ากับ B * A

การคูณกันระหว่างเมตริกซ์ โดยใช้ Excel จะใช้สูตร MMULT โดยมีรูปแบบการใช้สูตร Excel ดังนี้

MMULT (array1,array2) โดย Excel จะคืนค่า #VALUE! เมื่อมีการกำหนด ARRAY ไม่ถูกต้อง 2 รูปแบบดังนี้

1. สมาชิกใน array1 และ array2 อย่างน้อยหนึ่งตัวเป็นค่าว่าง หรือเป็นข้อความ

2. จำนวนหลักของ array1 ไม่เท่ากับ จำนวนแถวของ array2

การใช้สูตร Excel : MMULT  ขอยกตัวอย่าง 2 รูปแบบ สมมุติต้องการหา ผลคูณระหว่าง A(2X3) และ B (3X2) ดังภาพที่ 1 โดยกำหนดให้สมาชิกของ A อยู่ในช่วง C4:E5 และ สมาชิกของ B อยู่ในช่วง G4:H6

คำนวณผลคูณ A*B 

สมมุติให้ C เป็นผลคูณ A และ B ดังนั้น C จะมีขนาด 2x2 การใช้ Excel คำนวณสามารถดำเนินการได้ดังนี้

1. เลือกช่วงข้อมูล C8:D9 (เท่ากับจำนวนสมาชิกของ C 2x2)

2. พิมพ์สูตร excel ดังนี้ =MMULT(C4:E5, G4:H6)

3. กดปุ่ม Ctrl + Shift + Enter เพื่อเป็นการคำนวณแบบ Array

4. ผลลัพธ์แสดงดังภาพที่ 1

คำนวณผลคูณ B*A 

สมมุติให้ C เป็นผลคูณ B และ A ดังนั้น C จะมีขนาด 3x3 การใช้ Excel คำนวณสามารถดำเนินการได้ดังนี้

1. เลือกช่วงข้อมูล C11:E13 (เท่ากับจำนวนสมาชิกของ C 3x3)

2. พิมพ์สูตร excel ดังนี้ =MMULT(G4:H6,C4:E5)

3. กดปุ่ม Ctrl + Shift + Enter เพื่อเป็นการคำนวณแบบ Array

4. ผลลัพธ์แสดงดังภาพที่ 1

ภาพที่ 1 ผลการใช้ Excel คำนวณผลคูณระหว่างเมตริกซ์

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

หัวข้อที่เกี่ยวของ

การใช้ Excel หา Determinant ของเมตริกซ์
โปรแกรมภาษา C สำหรับดำเนินการคูณเมตริกซ์
การใช้ Excel หาเมตริกซ์ผกผัน
การใช้ Excel แก้ระบบสมการเชิงเส้น

 

มาใช้ Excel ช่วยดำเนินการกับเมตริกซ์กัน (How to use MS Excel to operate with matrix)

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

การหา Determinant ของ Matrix

Determinant ของเมตริกซ์เป็นค่าที่แสดงคุณลักษณะเฉพาะของเมตริกซ์ใดๆ โดยเมตริกซ์ที่จะหา Determinant ได้ต้องเป็นเมตริกซ์ที่มีจำนวนแถวเท่ากับจำนวนหลัก (เมตริกซ์จัตตุรัส) ใน Excel จะใช้สูตร MDETERM โดยมีรูปแบบการใช้สูตร Excel ดังนี้

MDETERM(array) โดย Excel จะคืนค่า #VALUE! เมื่อมีการกำหนด array ไม่ถูกต้อง 2 รูปแบบดังนี้

1. สมาชิกใน array อย่างน้อยหนึ่งตัวเป็นค่าว่าง หรือเป็นข้อความ

2. array หรือ matrix ไม่เป็นเมตริกซ์จัตตุรัส

การใช้สูตร ขอยกตัวอย่าง 2 รูปแบบ สมมุติต้องการหา Determinant ของเมตริกซ์ 3x3 ทีมีสมาชิกดังนี้

4    -4    0

-1    4   -2

0    -2    4

1. สมมุติต้องการแสดงค่า Determinant ที่ เซลล์ B3 เราสามารถเขียนสูตร Excel ได้ดังนี้

B3 = MDETERM({4,-4,0;-1,4,-2;0,-2,4})

2 . กำหนดสมาชิกของเมตริกซ์ลงในเซลล์และใช้การเลือกเซลล์เมตริกซ์ที่ต้องการคำนวณ เช่น กำหนดสมาชิกเมตริกซ์ลงในเซลล์ D2:F4 ดังแสดงในภาพที่ 1 เราสามารถเขียนสูตร Excel ใน B3 ได้ดังนี้

B3 = MDETERM(D2:F4)

ผลลัพธ์แสดงได้ดังภาพที่ 1

ภาพที่ 1 ใช้สูตร Excel คำนวณ Determinant ของเมตริกซ์

เพิ่มเติม

1. หากค่า Determinant ของเมตริกซ์มีค่าเข้าใกล้ศูนย์มากๆ เราจะเรียกเมตริกนั้นว่า Singular Matrix ซึ่งจะแสดงถึงคุณลักษณะที่ไม่ปกติ อาจมีความผิดปกติของระบบที่กำลังศึกษาอยู่ก็เป็นได้

2. ก่อนที่จะดำเนินการใดๆกับเมตริกซ์ ควรหา Determinant ของเมตริกซ์นั้นๆก่อนเป็นลำดับแรกเพื่อตรวจสอบคุณลักษณะของเมตริกซ์นั้น

หัวข้อที่เกี่ยวข้อง
1. การใช้ Excel คำนวณผลคูณระหว่างเมตริกซ์
2. การใช้ Excel ช่วยหาเมตริกซ์ผกผัน
3. การใช้ Excel แก้ระบบสมการเชิงเส้น

การประยุกต์ใช้สูตร excel สำหรับวิเคราะห์การถดถอยแบบเชิงเส้น

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

Y = mX + c 

โดยที่ m คือ ความชันของเส้นตรง c คือ จุดตัดของเส้นตรงบนแกน Y เมื่อ X ตัวแปรอิสระ

การใช้สูตร excel : SLOPE  และ Excel : INTERCEPT สำหรับหาค่าความชันและจุดตัดแกน ได้เคยนำเสนอมาแล้วในบทความ เรื่อง การใช้ Excel สร้างกราฟเส้นตรง    ซึ่งการใช้สูตร Excel ดังกล่าวยังขาดผลวิเคราะห์ทางสถิติที่จะนำมาใช้วัดความแม่นยำของการใช้สมการเส้นตรงในการทำนายแนวโน้มจากข้อมูลที่ได้จากการทดลอง ดังนั้นสูตร Excel : LINEST ซึ่งใช้การวิเคราะห์การถดถอยเชิงเส้นจึงถูกนำมาใช้วิเคราะห์หาค่าความชันและจุดตัดแกน Y เนื่องจากสูตร Excel : LINEST สามารถคืนค่าทางสถิติที่ได้จากการวิเคราะห์การถดถอยเชิงเส้นมาให้ผู้ใช้อย่างเราพิจารณาความแม่นยำของแบบจำลองเชิงเส้นที่จะอธิบายแนวโน้มของข้อมูลที่วัดได้จริง ดังตัวอย่างในภาพที่ 1

ภาพที่ 1 ชุดข้อมูล

รูปแบบการใช้สูตร Linest มีดังนี้

LINEST(known_y's, [known_x's], [const], [stats])   สูตรนี้จะคืนค่ากลับเป็น Array

เมื่อ

known_y's คือข้อมูลตัวแปรตามที่ทราบ

[known_x's] คือ ข้อมูลอาเรย์ x ตัวแปรอิสระ หากไม่ใส่ Excel จะกำหนดให้เป็นค่า 1,2,3,… จนเท่ากับจำนวนของตัวแปรตาม

[const] คือ ตรรกะที่ใช้ระบุให้ Excel คำนวณค่าจุดตัดแกน (c )หรือไม่ หากเป็น TRUE จะคำนวณตามปกติ หากเป็น FALSE จะกำหนดให้ c = 0

[stats] คือ ตรรกะที่ใช้ระบุให้ Excel ส่งกลับผลคำนวณค่าทางสถิติหรือไม่ โดย หากเป็น TRUE  จะส่งกลับหากเป็น FALSE จะไม่ส่งกลับ

เนื่องจากสูตร Excel : Linest จะคืนค่าผลการคำนวณในรูปแบบ Array ดังนั้นจะต้องใช้สูตร Excel:Index ช่วยเพื่อตำแหน่งของตัวแปรที่ต้องการแสดงผล

ให้พิมพ์ข้อความใน E2 E3 และ E4 ดังนี้ m , c , r^2

พิมพ์สูตร excel ใน F2 เป็น  =INDEX(LINEST(C$2:C$9,B$2:B$9,TRUE,TRUE),1,1)  จากนั้นกดปุ่ม Ctrl + Shift+ENTER  เพื่อเป็นการคำนวณแบบ Array

พิมพ์สูตร excel ใน F3 เป็น  =INDEX(LINEST(C$2:C$9,B$2:B$9,TRUE,TRUE),1,2)  จากนั้นกดปุ่ม Ctrl + Shift+ENTER  เพื่อเป็นการคำนวณแบบ Array

พิมพ์สูตร excel ใน F4 เป็น  =INDEX(LINEST(C$2:C$9,B$2:B$9,TRUE,TRUE),3,1)  จากนั้นกดปุ่ม Ctrl + Shift+ENTER  เพื่อเป็นการคำนวณแบบ Array

จากผลการใช้สูตร Excel :Linest จะได้แบบจำลองสมการเชิงเส้นที่มี m = 2.037, c = -0.096, r^2 = 0.99 ค่า r^2 เป็นค่าทางสถิติที่ได้จากการวิเคราะห์การถดถอยของ Excel โดยพบว่าเข้าใกล้ 1 มากๆ แสดงถึงแนวโน้มความสัมพันธ์ระหว่างตัวแปรตาม y และตัวแปรอิสระ x ของข้อมูลชุดนี้มีความเป็นเชิงเส้นมาก ดังแสดงในภาพที่ 2 จากแบบจำลองสมการเชิงเส้นที่วิเคราะห์ได้ทำให้ผู้ใช้สามารถประมาณค่าภายในช่วง (Interpolation) ของชุดข้อมูลดังกล่าวได้เมื่อทราบตัวแปรอิสระ

ภาพที่ 2 ผลการใช้สูตร Excel

ซึ่งจากผลการประยุกต์ใช้สูตร Excel : Linest จะทำให้ผู้ใช้มีความมั่นใจที่จะเลือกใช้แบบจำลองสมการเชิงเส้นสำหรับอธิบายความสัมพันธ์ของข้อมูลที่มีอยู่ ในขณะเดียวกันหากค่า r^2 ของแบบจำลองสมการเชิงเส้น มีค่าน้อยกว่า 1 มากๆ จะแสดงให้ทราบว่าความสัมพันธ์ของชุดข้อมูลดังกล่าวไม่เป็นเชิงเส้น อาจจะเป็นแบบพหุนาม หรือ exponential ก็เป็นได้ ในทำนองเดียวกันเราสามารถประยุกต์ใช้สูตร Excel : Linest กับแบบจำลองเชิงเส้นแบบหลายเชิง แบบจำลองพหุนาม (โพลิโนเมียล) รวมถึง สมการแบบยกกำลังและแบบ log ได้ดังจะนำเสนอในครั้งต่อๆไป