การประมาณใน Excel: 5 วิธีง่ายๆ

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

การดำเนินการของการประมาณ

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

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

แต่สามารถสร้างได้โดยประมาณหนึ่งในห้าประเภทของการประมาณ:

• เชิงเส้น;
• เลขชี้กำลัง;
• ลอการิทึม;
• พหุนาม;
• พลัง.

พิจารณาตัวเลือกแต่ละตัวในรายละเอียดเพิ่มเติมแยกต่างหาก

บทเรียน: วิธีการสร้างเส้นแนวโน้มใน Excel

วิธีที่ 1: การปรับให้เรียบเชิงเส้น

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

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

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



2. ตารางถูกสร้างขึ้น



3. ในการเพิ่มบรรทัดเทรนด์ให้เลือกด้วยปุ่มเมาส์ขวา เมนูบริบทปรากฏขึ้น เลือกไอเท็ม "เพิ่มเส้นแนวโน้ม ... "

   

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



4. หากคุณยังคงเลือกตัวเลือกแรกของการดำเนินการด้วยการเพิ่มผ่านเมนูบริบทหน้าต่างรูปแบบจะเปิดขึ้น

   ในบล็อกพารามิเตอร์ "สร้างเส้นแนวโน้ม (การประมาณและการปรับให้เรียบ)" เราตั้งสวิตช์เป็นตำแหน่ง "เชิงเส้น"

   หากคุณต้องการคุณสามารถติดตั้งเห็บใกล้ตำแหน่ง "แสดงสมการบนแผนภาพ" หลังจากนั้นแผนภาพจะแสดงสมการฟังก์ชั่นที่เรียบ

   นอกจากนี้ในกรณีของเราเป็นสิ่งสำคัญสำหรับการเปรียบเทียบตัวเลือกที่แตกต่างกันสำหรับการประมาณเพื่อสร้างรายการเกี่ยวกับรายการ "วางค่าของการประมาณที่ถูกต้องบนไดอะแกรม (r ^ 2)" ตัวบ่งชี้นี้อาจแตกต่างกันตั้งแต่ 0 ถึง 1 เท่าใดที่สูงขึ้นการประมาณนั้นดีกว่า (สำคัญ) เป็นที่เชื่อกันว่าด้วยขนาดของตัวบ่งชี้นี้ 0.85 และข้างต้นการปรับให้เรียบสามารถถือว่าน่าเชื่อถือและหากตัวบ่งชี้ต่ำกว่านั้นไม่มี

   หลังจากใช้การตั้งค่าทั้งหมดข้างต้นทั้งหมด คลิกที่ปุ่ม "ปิด" วางไว้ที่ด้านล่างของหน้าต่าง



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

การปรับให้เรียบซึ่งใช้ในกรณีนี้อธิบายโดยสูตรต่อไปนี้:

y = ax + b

โดยเฉพาะสูตรของเราใช้รูปแบบนี้:

y = -0.1156x + 72,255

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

วิธีที่ 2: การประมาณเลขชี้กำลัง

ตอนนี้เรามาพิจารณาประเภทการประมาณแบบเลขชี้กำลังใน Excel

1. เพื่อที่จะเปลี่ยนประเภทของเส้นแนวโน้มให้เลือกด้วยปุ่มเมาส์ขวาและเลือก "รูปแบบบรรทัดเทรนด์ ... " ในเมนูเปิด



2. หลังจากนั้นหน้าต่างรูปแบบที่คุ้นเคยกับเราแล้ว ในหน่วยการเลือกประเภทประมาณเราตั้งสวิตช์เป็นตำแหน่ง "เลขชี้กำลัง" การตั้งค่าที่เหลือจะปล่อยให้เหมือนกันในกรณีแรก คลิกที่ปุ่ม "ปิด"



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

มุมมองทั่วไปของฟังก์ชั่นการปรับให้เรียบเป็นเช่นนี้:

y = เป็น ^ x

โดยที่ e เป็นพื้นฐานของลอการิทึมธรรมชาติ

โดยเฉพาะสูตรของเราได้ทำแบบฟอร์มต่อไปนี้:

y = 6282.7 * e ^ (- 0,012 * x)

วิธีที่ 3: ลอการิทึมการปรับสภาพลอการิทึม

ตอนนี้มันเป็นเลี้ยวเพื่อพิจารณาวิธีการประมาณลอการิทึม

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



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

โดยทั่วไปสูตรการปรับให้เรียบมีลักษณะดังนี้:

y = a * ln (x) + b

ที่ LN เป็นขนาดของลอการิทึมธรรมชาติ ดังนั้นชื่อของวิธีการ

ในกรณีของเราสูตรจะใช้แบบฟอร์มต่อไปนี้:

y = -62,81LN (x) +404.96

วิธีที่ 4: การปรับให้เรียบพหุนาม

มันมาพิจารณาวิธีการทำให้ราบรื่นของพหุนาม

1. ไปที่หน้าต่างรูปแบบเส้นแนวโน้มตามที่ทำไปแล้วมากกว่าหนึ่งครั้ง ในบล็อก "Build Line Build" เราตั้งสวิตช์ไปที่ตำแหน่ง "พหุนาม" ด้านขวาของรายการนี้คือฟิลด์ "องศา" เมื่อเลือกค่า "พหุนาม" มันจะเปิดใช้งาน ที่นี่คุณสามารถระบุค่าพลังงานใด ๆ จาก 2 (ติดตั้งตามค่าเริ่มต้น) เป็น 6 ตัวบ่งชี้นี้กำหนดจำนวนของฟังก์ชั่น Maxima และ Minima เมื่อติดตั้งพหุนามของระดับที่สองมีการอธิบายเพียงหนึ่งเดียวเท่านั้นและเมื่อมีการติดตั้งพหุนามหกสูงถึงห้า Maxima สามารถอธิบายได้ เพื่อเริ่มต้นด้วยการตั้งค่าเริ่มต้นนั่นคือเราจะระบุระดับที่สอง การตั้งค่าที่เหลือทิ้งไว้เหมือนกับที่เราวางไว้ในวิธีการก่อนหน้า คลิกที่ปุ่ม "ปิด"



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

   

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

   y = a1 + a1 * x + a2 * x ^ 2 + ... + an * x ^ n

   ในกรณีของเราสูตรนี้ใช้รูปแบบนี้:

   y = 0.0015 * x ^ 2-1,7202 * x + 507.01

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



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

สูตรที่อธิบายถึงการปรับให้เรียบประเภทนี้ใช้แบบฟอร์มต่อไปนี้:

Y = 8E-08X ^ 6-0.0003x ^ 5 + 0.3725X ^ 4-269,33x ^ 3 + 109525X ^ 2-2E + 07X + 2E + 09

วิธีที่ 5: การปรับสีให้เรียบ

ในการเสร็จสิ้นให้พิจารณาวิธีการประมาณพลังงานใน Excel

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



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

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

สูตรทั่วไปที่อธิบายถึงวิธีนี้มีประเภทนี้:

y = bx ^ n

โดยเฉพาะสำหรับกรณีของเราดูเหมือนว่า:

Y = 6E + 18x ^ (- 6,512)

อย่างที่เราสามารถดูได้โดยใช้ข้อมูลเฉพาะที่เราใช้เช่นระดับความน่าเชื่อถือที่ใหญ่ที่สุดแสดงให้เห็นถึงวิธีการประมาณพหุนามกับพหุนามในระดับที่หก (0.9844), ระดับที่เล็กที่สุดของความน่าเชื่อถือในวิธีการเชิงเส้น (0.9418) แต่นี่ไม่ได้หมายความว่าแนวโน้มเดียวกันจะใช้ตัวอย่างอื่น ๆ ไม่ระดับของประสิทธิภาพในวิธีการข้างต้นอาจแตกต่างกันอย่างมีนัยสำคัญขึ้นอยู่กับฟังก์ชั่นประเภทเฉพาะที่จะสร้างเส้นแนวโน้ม ดังนั้นหากฟังก์ชั่นนี้วิธีการที่เลือกมีประสิทธิภาพมากที่สุดก็ไม่ได้หมายความว่าจะดีที่สุดในสถานการณ์อื่น

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