วิธีสร้างแผนภูมิน้ำตก

ฉันพบบ่อยขึ้นเรื่อยๆ ในการรายงานของบริษัทต่างๆ และได้ยินคำขอจากผู้เข้ารับการฝึกอบรมให้อธิบายว่าไดอะแกรมน้ำตกของการเบี่ยงเบนถูกสร้างขึ้นอย่างไร - ยังเป็น "น้ำตก" ยังเป็น "น้ำตก" ยังเป็น "สะพาน" ” ก็ยังเป็น “สะพาน” เป็นต้น . ดูเหมือนว่านี้:

จากระยะไกลดูเหมือนน้ำตกบนแม่น้ำภูเขาหรือสะพานแขวน - ใครเห็นบ้าง 🙂

ลักษณะเฉพาะของไดอะแกรมดังกล่าวคือ:

• เราเห็นค่าเริ่มต้นและค่าสุดท้ายของพารามิเตอร์อย่างชัดเจน (คอลัมน์แรกและคอลัมน์สุดท้าย)
• การเปลี่ยนแปลงในเชิงบวก (การเติบโต) จะแสดงเป็นสีเดียว (โดยปกติ สีเขียว) และด้านลบ (ปฏิเสธ) ให้กับผู้อื่น (โดยปกติ สีแดง).
  
• บางครั้งแผนภูมิอาจมีคอลัมน์ผลรวมย่อย (สีเทาลงบนเสาแกน x)
  

ในชีวิตประจำวันมักใช้ไดอะแกรมดังกล่าวในกรณีต่อไปนี้:

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

มีหลายวิธีในการสร้างแผนภูมิดังกล่าว ขึ้นอยู่กับเวอร์ชันของ Microsoft Excel

วิธีที่ 1: ง่ายที่สุด: ชนิดในตัวใน Excel 2016 และใหม่กว่า

หากคุณมี Excel 2016, 2019 หรือใหม่กว่า (หรือ Office 365) การสร้างแผนภูมิดังกล่าวไม่ใช่เรื่องยาก – Excel เวอร์ชันเหล่านี้มีประเภทนี้อยู่แล้วตามค่าเริ่มต้น จำเป็นต้องเลือกตารางที่มีข้อมูลและเลือกบนแท็บเท่านั้น สิ่งที่ใส่เข้าไป (แทรก) คำสั่ง ซ้อน (น้ำตก):

เป็นผลให้เราได้ไดอะแกรมสำเร็จรูป:

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

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

ในกรณีนี้ อาร์กิวเมนต์แรก (9) คือรหัสของการดำเนินการบวกทางคณิตศาสตร์ และข้อที่สอง (0) ทำให้ฟังก์ชันละเว้นผลรวมที่คำนวณแล้วสำหรับไตรมาสก่อนหน้าในผลลัพธ์

หลังจากเพิ่มแถวด้วยผลรวมแล้ว จะยังคงเลือกคอลัมน์ทั้งหมดที่ปรากฏบนไดอะแกรม (คลิกสองครั้งติดต่อกันบนคอลัมน์) และด้วยการคลิกขวาบนเมาส์ ให้เลือกคำสั่ง กำหนดเป็นผลรวม (ตั้งเป็นยอดรวม):

คอลัมน์ที่เลือกจะลงจอดบนแกน x และเปลี่ยนสีเป็นสีเทาโดยอัตโนมัติ

อันที่จริงแล้วนั่นคือทั้งหมด – แผนภาพน้ำตกพร้อมแล้ว:

วิธีที่ 2 สากล: คอลัมน์ที่มองไม่เห็น

หากคุณมี Excel 2013 หรือเวอร์ชันเก่ากว่า (2010, 2007 เป็นต้น) วิธีการที่อธิบายไว้ข้างต้นจะไม่เหมาะกับคุณ คุณจะต้องเดินไปรอบๆ และตัดแผนภูมิน้ำตกที่ขาดหายไปออกจากฮิสโตแกรมแบบเรียงซ้อนแบบปกติ

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

ในการสร้างแผนภูมิดังกล่าว เราจำเป็นต้องเพิ่มคอลัมน์เสริมอีกสองสามคอลัมน์ที่มีสูตรลงในข้อมูลต้นฉบับ:

• อันดับแรก เราต้องแยกคอลัมน์เดิมโดยแยกค่าบวกและค่าลบออกเป็นคอลัมน์แยกกันโดยใช้ฟังก์ชัน IF (ถ้า).  
• ประการที่สอง คุณจะต้องเพิ่มคอลัมน์หน้าคอลัมน์ จุกนมหลอกโดยที่ค่าแรกจะเป็น 0 และเริ่มจากเซลล์ที่สอง สูตรจะคำนวณความสูงของคอลัมน์สนับสนุนที่โปร่งใสมากเหล่านั้น
  

หลังจากนั้นก็ยังคงเลือกทั้งตารางยกเว้นคอลัมน์เดิม ไหล และสร้างฮิสโตแกรมแบบเรียงซ้อนทั่วๆ ไป สิ่งที่ใส่เข้าไป — ฮิสโตแกรม (แทรก — แผนภูมิคอลัมน์):

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

ข้อดีของวิธีนี้คือความเรียบง่าย ใน minuses – ความจำเป็นในการนับคอลัมน์เสริม

วิธีที่ 3 ถ้าเราตกแดงทุกอย่างจะยากขึ้น

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

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

วิธีที่ 4. แปลกใหม่: แถบขึ้น-ลง

วิธีนี้ใช้องค์ประกอบพิเศษที่ไม่ค่อยมีใครรู้จักของแผนภูมิแบบแบน (ฮิสโตแกรมและกราฟ) – วงขึ้น-ลง (แถบขึ้น-ลง). แถบเหล่านี้เชื่อมต่อจุดของกราฟสองกราฟเป็นคู่เพื่อแสดงให้เห็นอย่างชัดเจนว่าจุดใดในสองจุดที่สูงกว่าหรือต่ำกว่า ซึ่งถูกใช้อย่างแข็งขันเมื่อเห็นภาพข้อเท็จจริงของแผน:

มันง่ายที่จะคิดออกว่าถ้าเราลบเส้นของแผนภูมิและเหลือเฉพาะแถบขึ้น-ลงบนแผนภูมิ เราก็จะได้ "น้ำตก" แบบเดียวกัน

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

ในการสร้าง "น้ำตก" คุณต้องเลือกคอลัมน์ที่มีเดือน (สำหรับลายเซ็นตามแกน X) และคอลัมน์เพิ่มเติมสองคอลัมน์ กำหนดการ 1 и กำหนดการ 2 และสร้างกราฟปกติสำหรับผู้เริ่มต้นโดยใช้ แทรก – กราฟ (แทรก — แผนภูมิบรรทัด):

ตอนนี้ มาเพิ่มวงดนตรีขึ้น-ลงในแผนภูมิของเรา:

• ใน Excel 2013 และใหม่กว่า ต้องเลือกสิ่งนี้บนแท็บ นวกรรมิก คำสั่ง เพิ่มองค์ประกอบแผนภูมิ — วงเพิ่มขึ้น-ลดลง (การออกแบบ — เพิ่มองค์ประกอบแผนภูมิ — แถบขึ้น-ลง)
• ใน Excel 2007-2010 – ไปที่ tab เค้าโครง – แถบลดขั้นสูง (เค้าโครง — แถบขึ้น-ลง)

แผนภูมิจะมีลักษณะดังนี้:

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

ใน Microsoft Excel เวอร์ชันล่าสุด ความกว้างของแท่งกราฟสามารถเปลี่ยนแปลงได้ด้วยการคลิกบนกราฟโปร่งใสอันใดอันหนึ่ง (ไม่ใช่แท่ง) ด้วยปุ่มเมาส์ขวาและเลือกคำสั่ง รูปแบบชุดข้อมูล – ระยะห่างด้านข้าง (รูปแบบชุด — ความกว้างช่องว่าง).

ใน Excel เวอร์ชันเก่า คุณต้องใช้คำสั่ง Visual Basic เพื่อแก้ไขปัญหานี้:

1. เน้นไดอะแกรมที่สร้างขึ้น
2. กดแป้นพิมพ์ลัด อื่น ๆ+F11เพื่อเข้าสู่ Visual Basic Editor
3. กดแป้นพิมพ์ลัด Ctrl+Gเพื่อเปิดอินพุตคำสั่งโดยตรงและแผงแก้ไขข้อบกพร่อง ทันที (ปกติจะอยู่ด้านล่างสุด)

4. คัดลอกและวางคำสั่งต่อไปนี้ที่นั่น: ActiveChart.ChartGroups(1).GapWidth = 30 และกด เข้าสู่:

แน่นอน คุณสามารถเล่นกับค่าพารามิเตอร์ได้หากต้องการ ความกว้างของช่องว่างเพื่อให้บรรลุการกวาดล้างที่ต้องการ:

• วิธีสร้างแผนภูมิแสดงหัวข้อย่อยใน Excel เพื่อให้เห็นภาพ KPI  
• มีอะไรใหม่ในแผนภูมิใน Excel 2013
• วิธีสร้างแผนภูมิ "สด" แบบโต้ตอบใน Excel