การวิเคราะห์ความไวใน Excel (แผ่นข้อมูลตัวอย่าง)

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

รับผลลัพธ์หลายรายการด้วยตารางข้อมูล

ความสามารถของแผ่นข้อมูลเป็นองค์ประกอบของการวิเคราะห์แบบ What-if ซึ่งมักจะทำผ่าน Microsoft Excel นี่คือชื่อที่สองสำหรับการวิเคราะห์ความอ่อนไหว

ขององค์กร

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

พื้นฐานเกี่ยวกับตารางข้อมูล

ตารางข้อมูลมี XNUMX ประเภท โดยมีจำนวนส่วนประกอบต่างกัน คุณต้องรวบรวมตารางโดยเน้นที่จำนวนค่า uXNUMXbuXNUMXb ที่คุณต้องตรวจสอบด้วย

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

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

การสร้างตารางข้อมูลด้วยตัวแปรเดียว

พิจารณาวิธีการวิเคราะห์โดยใช้ตัวอย่างร้านหนังสือขนาดเล็กที่มีหนังสือในสต็อกเพียง 100 เล่ม บางส่วนสามารถขายได้แพงกว่า (50 เหรียญ) ส่วนที่เหลือจะทำให้ผู้ซื้อเสียค่าใช้จ่ายน้อยกว่า (20 เหรียญ) รายได้รวมจากการขายสินค้าทั้งหมดคำนวณ - เจ้าของตัดสินใจว่าจะขายหนังสือ 60% ในราคาสูง คุณต้องค้นหาว่ารายได้จะเพิ่มขึ้นอย่างไรหากคุณเพิ่มราคาสินค้าในปริมาณมาก – 70% เป็นต้น

เอาใจใส่! ต้องคำนวณรายได้ทั้งหมดโดยใช้สูตร มิฉะนั้น จะไม่สามารถรวบรวมตารางข้อมูลได้

1. เลือกเซลล์ว่างจากขอบของแผ่นงานแล้วเขียนสูตรลงไป: = เซลล์ของรายได้ทั้งหมด ตัวอย่างเช่น หากรายได้เขียนในเซลล์ C14 (ระบุการกำหนดแบบสุ่ม) คุณต้องเขียนสิ่งนี้: =S14.
2. เราเขียนเปอร์เซ็นต์ของปริมาณสินค้าในคอลัมน์ทางด้านซ้ายของเซลล์นี้ ไม่ใช่ด้านล่าง นี่เป็นสิ่งสำคัญมาก
3. เราเลือกช่วงของเซลล์ที่มีคอลัมน์เปอร์เซ็นต์และลิงก์ไปยังรายได้ทั้งหมด

4. เราพบบนแท็บ "ข้อมูล" รายการ "ถ้าการวิเคราะห์" และคลิกที่มัน - ในเมนูที่เปิดขึ้น เลือกตัวเลือก "ตารางข้อมูล"

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

หลังจากคลิกปุ่ม "ตกลง" ในหน้าต่างที่ป้อนข้อมูลสำหรับการรวบรวมตาราง ผลลัพธ์ของการคำนวณจะปรากฏในบรรทัด

การเพิ่มสูตรลงในตารางข้อมูลตัวแปรเดียว

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

1. เลือกช่วงของเซลล์อีกครั้ง แต่ตอนนี้ควรมีสูตรใหม่
2. เปิดเมนูการวิเคราะห์ "จะเกิดอะไรขึ้นถ้า" และเลือก "แผ่นข้อมูล"
3. เราเพิ่มสูตรใหม่ลงในฟิลด์ที่เกี่ยวข้องในแถวหรือคอลัมน์ ขึ้นอยู่กับการวางแนวของจาน

สร้างตารางข้อมูลที่มีสองตัวแปร

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

1. เขียนตัวเลือกราคาในบรรทัดเดียวพร้อมลิงก์ไปยังรายได้ – หนึ่งเซลล์สำหรับแต่ละราคา
2. เลือกช่วงของเซลล์

3. เปิดหน้าต่างตารางข้อมูล เช่นเดียวกับการคอมไพล์ตารางด้วยตัวแปรเดียว ผ่านแท็บ "ข้อมูล" บนแถบเครื่องมือ
4. แทนที่ในคอลัมน์ “แทนที่ค่าตามคอลัมน์ใน …” เซลล์ที่มีราคาสูงเริ่มต้น
5. เพิ่มเซลล์ที่มีเปอร์เซ็นต์เริ่มต้นของการขายหนังสือราคาแพงลงในคอลัมน์ “แทนที่ค่าตามแถวใน …” แล้วคลิก “ตกลง”

เป็นผลให้ทั้งตารางเต็มไปด้วยจำนวนรายได้ที่เป็นไปได้โดยมีเงื่อนไขต่างกันสำหรับการขายสินค้า

เร่งการคำนวณแผ่นงานที่มีตารางข้อมูล

ถ้าคุณต้องการการคำนวณอย่างรวดเร็วในตารางข้อมูลที่ไม่ทริกเกอร์การคำนวณใหม่ของเวิร์กบุ๊กทั้งหมด มีบางสิ่งที่คุณสามารถทำได้เพื่อเพิ่มความเร็วในกระบวนการ

1. เปิดหน้าต่างตัวเลือก เลือกรายการ "สูตร" ในเมนูด้านขวา
2. เลือกรายการ "อัตโนมัติ ยกเว้นตารางข้อมูล" ในส่วน "การคำนวณในเวิร์กบุ๊ก"

3. มาคำนวณผลลัพธ์ในตารางใหม่ด้วยตนเอง เมื่อต้องการทำสิ่งนี้ ให้เลือกสูตรแล้วกดปุ่ม F

เครื่องมืออื่นๆ สำหรับการวิเคราะห์ความไว

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

1. ฟังก์ชัน “การเลือกพารามิเตอร์” เหมาะสมหากทราบผลลัพธ์ที่ต้องการ และคุณจำเป็นต้องทราบค่าอินพุตของตัวแปรเพื่อให้ได้ผลลัพธ์ดังกล่าว.
2. “ค้นหาวิธีแก้ปัญหา” เป็นส่วนเสริมสำหรับการแก้ปัญหา จำเป็นต้องกำหนดขอบเขตและชี้ไปที่พวกมัน หลังจากนั้นระบบจะพบคำตอบ การแก้ปัญหาถูกกำหนดโดยการเปลี่ยนค่า
3. การวิเคราะห์ความไวสามารถทำได้โดยใช้ Scenario Manager พบเครื่องมือนี้ในเมนูการวิเคราะห์แบบ What-if ใต้แท็บข้อมูล มันแทนที่ค่าในหลาย ๆ เซลล์ – จำนวนสามารถเข้าถึง 32 ผู้มอบหมายงานเปรียบเทียบค่าเหล่านี้เพื่อให้ผู้ใช้ไม่ต้องเปลี่ยนด้วยตนเอง ตัวอย่างการใช้ตัวจัดการสคริปต์:

การวิเคราะห์ความไวของโครงการลงทุนใน Excel

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

วิธีวิเคราะห์ความอ่อนไหวในการลงทุน

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

1. มูลค่าปัจจุบันสุทธิ – คำนวณโดยการลบจำนวนเงินลงทุนออกจากจำนวนรายได้
2. อัตราผลตอบแทน / กำไรภายใน – ระบุว่าต้องได้รับกำไรเท่าใดจากการลงทุนในหนึ่งปี
3. อัตราส่วนคืนทุนคืออัตราส่วนของผลกำไรทั้งหมดต่อการลงทุนเริ่มแรก
4. ดัชนีกำไรลด – บ่งบอกถึงประสิทธิผลของการลงทุน

สูตร

ความไวในการฝังสามารถคำนวณได้โดยใช้สูตรนี้: เปลี่ยนพารามิเตอร์เอาต์พุตเป็น % / เปลี่ยนพารามิเตอร์อินพุตเป็น %

พารามิเตอร์เอาต์พุตและอินพุตสามารถเป็นค่าที่อธิบายไว้ก่อนหน้านี้

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

ตัวอย่างการวิเคราะห์ความอ่อนไหวของโครงการลงทุนใน Excel

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

1. กรอกข้อมูลในตารางเพื่อวิเคราะห์โครงการ

2. เราคำนวณกระแสเงินสดโดยใช้ฟังก์ชัน OFFSET ในระยะแรกกระแสจะเท่ากับการลงทุน ต่อไปเราใช้สูตร: =IF(OFFSET(Number,1;)=2;SUM(เข้า 1:ออก 1); SUM(เข้า 1:ออก 1)+$B$ 5)
   การกำหนดเซลล์ในสูตรอาจแตกต่างกันไปขึ้นอยู่กับเลย์เอาต์ของตาราง ในตอนท้าย ค่าจากข้อมูลเริ่มต้นจะถูกเพิ่ม – มูลค่าซาก

3. เรากำหนดระยะเวลาที่โครงการจะชำระ สำหรับช่วงแรกเราใช้สูตรนี้: = สรุป (G7: ก17;»<0″). ช่วงเซลล์คือคอลัมน์กระแสเงินสด สำหรับช่วงเวลาต่อไป เราใช้สูตรนี้: =ช่วงเริ่มต้น+IF(e.stream แรก>0; e.stream แรก;0) โครงการจะถึงจุดคุ้มทุนใน 4 ปี

4. เราสร้างคอลัมน์สำหรับตัวเลขของช่วงเวลาเหล่านั้นเมื่อโครงการจ่ายเงินออก

5. เราคำนวณผลตอบแทนจากการลงทุน จำเป็นต้องสร้างนิพจน์โดยที่ผลกำไรในช่วงเวลาหนึ่งๆ ถูกหารด้วยเงินลงทุนเริ่มแรก

6. เรากำหนดปัจจัยส่วนลดโดยใช้สูตรนี้: =1/(1+แผ่น%) ^หมายเลข.

7. เราคำนวณมูลค่าปัจจุบันโดยใช้การคูณ – กระแสเงินสดจะถูกคูณด้วยตัวคูณส่วนลด

8. มาคำนวณ PI (ดัชนีความสามารถในการทำกำไร) กัน มูลค่าปัจจุบันเมื่อเวลาผ่านไปหารด้วยเงินลงทุนเมื่อเริ่มโครงการ

9. มากำหนดอัตราผลตอบแทนภายในโดยใช้ฟังก์ชัน IRR: =IRR(ช่วงของกระแสเงินสด)

การวิเคราะห์ความไวในการลงทุนโดยใช้เอกสารข้อมูล

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

การวิเคราะห์ปัจจัยและการกระจายใน Excel พร้อมการคำนวณอัตโนมัติ

การวิเคราะห์ความอ่อนไหวอีกประเภทหนึ่งคือการวิเคราะห์ปัจจัยและการวิเคราะห์ความแปรปรวน ประเภทแรกกำหนดความสัมพันธ์ระหว่างตัวเลข ประเภทที่สองแสดงการพึ่งพาตัวแปรหนึ่งกับตัวแปรอื่น

ANOVA ใน Excel

วัตถุประสงค์ของการวิเคราะห์ดังกล่าวคือการแบ่งความแปรปรวนของค่าออกเป็นสามองค์ประกอบ:

1. ความแปรปรวนอันเป็นผลมาจากอิทธิพลของค่าอื่นๆ
2. การเปลี่ยนแปลงอันเนื่องมาจากความสัมพันธ์ของค่านิยมที่ส่งผลต่อมัน
3. การเปลี่ยนแปลงแบบสุ่ม

ลองทำการวิเคราะห์ความแปรปรวนผ่าน Add-in ของ Excel "การวิเคราะห์ข้อมูล" หากไม่ได้เปิดใช้งาน ก็สามารถเปิดใช้งานได้ในการตั้งค่า

ตารางเริ่มต้นต้องเป็นไปตามกฎสองข้อ: มีหนึ่งคอลัมน์สำหรับแต่ละค่า และข้อมูลในนั้นจะถูกจัดเรียงตามลำดับจากน้อยไปมากหรือจากมากไปน้อย จำเป็นต้องตรวจสอบอิทธิพลของระดับการศึกษาต่อพฤติกรรมที่มีความขัดแย้ง

1. ค้นหาเครื่องมือวิเคราะห์ข้อมูลในแท็บข้อมูลและเปิดหน้าต่าง ในรายการ คุณต้องเลือกการวิเคราะห์ความแปรปรวนทางเดียว

2. กรอกบรรทัดของกล่องโต้ตอบ ช่วงอินพุตคือเซลล์ทั้งหมด ไม่รวมส่วนหัวและตัวเลข จัดกลุ่มตามคอลัมน์ เราแสดงผลบนแผ่นงานใหม่

เนื่องจากค่าในเซลล์สีเหลืองมีค่ามากกว่าหนึ่ง สมมติฐานจึงถือว่าไม่ถูกต้อง - ไม่มีความสัมพันธ์ระหว่างการศึกษาและพฤติกรรมที่ขัดแย้งกัน

การวิเคราะห์ปัจจัยใน Excel: ตัวอย่าง

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

1. เราต้องค้นหาว่าความต้องการสินค้าใดเพิ่มขึ้นมากที่สุดในช่วงเดือนที่สอง เรากำลังรวบรวมตารางใหม่เพื่อกำหนดการเติบโตและความต้องการที่ลดลง การเจริญเติบโตคำนวณโดยใช้สูตรนี้: =IF((Demand 2-Demand 1)>0; Demand 2-Demand 1;0). ลดสูตร: =IF(การเติบโต=0; ความต้องการ 1- ความต้องการ 2;0)

2. คำนวณการเติบโตของความต้องการสินค้าเป็นเปอร์เซ็นต์: =IF(การเติบโต/ผลลัพธ์ 2 =0; ลดลง/ผลลัพธ์ 2; การเติบโต/ผลลัพธ์ 2)

3. มาสร้างแผนภูมิเพื่อความชัดเจนกัน เลือกช่วงของเซลล์และสร้างฮิสโตแกรมผ่านแท็บ "แทรก" ในการตั้งค่า คุณต้องลบการเติม ซึ่งสามารถทำได้ผ่านเครื่องมือ Format Data Series

การวิเคราะห์ความแปรปรวนแบบสองทางใน Excel

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

1. เราเปิด "การวิเคราะห์ข้อมูล" ในรายการ คุณต้องค้นหาการวิเคราะห์ความแปรปรวนแบบสองทางโดยไม่มีการซ้ำซ้อน
2. ช่วงเวลาอินพุต – เซลล์ที่มีข้อมูล (ไม่มีส่วนหัว) เราแสดงผลบนแผ่นงานใหม่และคลิก "ตกลง"

ค่า F มากกว่าค่าวิกฤต F ซึ่งหมายความว่าพื้นมีผลต่อความเร็วของการตอบสนองต่อเสียง

สรุป

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