Bagaimana untuk membina carta air terjun

Semakin kerap saya bertemu dalam pelaporan syarikat yang berbeza dan mendengar permintaan daripada pelatih untuk menerangkan bagaimana gambarajah lata penyelewengan dibina - ia juga merupakan "air terjun", ia juga "air terjun", ia juga merupakan "jambatan". ”, ia juga merupakan “jambatan”, dsb. Ia kelihatan seperti ini:

Dari jauh, ia benar-benar kelihatan seperti lata air terjun di sungai gunung atau jambatan gantung – siapa yang nampak 🙂

Keistimewaan rajah sedemikian ialah:

• Kami melihat dengan jelas nilai awal dan akhir parameter (lajur pertama dan terakhir).
• Perubahan positif (pertumbuhan) dipaparkan dalam satu warna (biasanya hijau), dan yang negatif (menolak) kepada orang lain (biasanya merah).
  
• Kadangkala carta juga mungkin mengandungi lajur kecil (kelabumendarat pada lajur paksi-x).
  

Dalam kehidupan seharian, rajah tersebut biasanya digunakan dalam kes berikut:

• visual paparan dinamik sebarang proses dalam masa: aliran tunai (aliran tunai), pelaburan (kami melabur dalam projek dan mendapat keuntungan daripadanya).
• Visualisasi pelaksanaan rancangan (lajur paling kiri dalam rajah adalah fakta, lajur paling kanan ialah pelan, keseluruhan rajah menggambarkan proses kita bergerak ke arah hasil yang diinginkan)
• Apabila anda memerlukan visual menunjukkan faktoryang memberi kesan kepada parameter kami (analisis faktor keuntungan – kandungannya).
  

Terdapat beberapa cara untuk membina carta sedemikian - semuanya bergantung pada versi Microsoft Excel anda.

Kaedah 1: Paling mudah: Jenis terbina dalam dalam Excel 2016 dan lebih baharu

Jika anda mempunyai Excel 2016, 2019 atau lebih baru (atau Office 365), maka membina carta sedemikian tidak sukar – versi Excel ini sudah mempunyai jenis ini terbina dalam secara lalai. Ia hanya perlu memilih jadual dengan data dan pilih pada tab Memasukkan (Sisipkan) Perintah melata (Air Terjun):

Akibatnya, kami akan mendapat gambar rajah yang hampir siap:

Anda boleh segera menetapkan warna isian yang diingini untuk lajur positif dan negatif. Cara paling mudah untuk melakukan ini ialah memilih baris yang sesuai Meningkatkan и mengurangkan terus dalam legenda dan dengan mengklik kanan pada mereka, pilih arahan Mengisi (Isi):

Jika anda perlu menambah lajur dengan subjumlah atau jumlah lajur akhir pada carta, maka adalah paling mudah untuk melakukan ini menggunakan fungsi JUMLAH KECIL (JUMLAH KECIL) or UNIT (AGREGAT). Mereka akan mengira jumlah terkumpul dari permulaan jadual, sambil mengecualikan daripadanya jumlah serupa yang terdapat di atas:

Dalam kes ini, hujah pertama (9) ialah kod operasi penjumlahan matematik, dan kedua (0) menyebabkan fungsi mengabaikan jumlah yang telah dikira untuk suku sebelumnya dalam keputusan.

Selepas menambah baris dengan jumlah, ia kekal untuk memilih jumlah lajur yang telah muncul pada rajah (buat dua klik tunggal berturut-turut pada lajur) dan, dengan mengklik kanan pada tetikus, pilih arahan Tetapkan sebagai jumlah (Tetapkan sebagai jumlah):

Lajur yang dipilih akan mendarat pada paksi-x dan menukar warna secara automatik kepada kelabu.

Itu, sebenarnya, sahaja - gambar rajah air terjun sudah sedia:

Kaedah 2. Universal: lajur tidak kelihatan

Jika anda mempunyai Excel 2013 atau versi yang lebih lama (2010, 2007, dsb.), maka kaedah yang diterangkan di atas tidak akan berfungsi untuk anda. Anda perlu berkeliling dan memotong carta air terjun yang hilang daripada histogram bertindan biasa (menjumlahkan bar di atas satu sama lain).

Caranya di sini ialah menggunakan lajur prop telus untuk menaikkan baris data merah dan hijau kami ke ketinggian yang betul:

Untuk membina carta sedemikian, kita perlu menambah beberapa lagi lajur tambahan dengan formula pada data sumber:

• Pertama, kita perlu membahagikan lajur asal kita dengan memisahkan nilai positif dan negatif ke lajur berasingan menggunakan fungsi IF (JIKA).  
• Kedua, anda perlu menambah lajur di hadapan lajur perentas pacu, dengan nilai pertama ialah 0, dan bermula dari sel kedua, formula akan mengira ketinggian lajur sokongan yang sangat telus tersebut.
  

Selepas itu, ia kekal untuk memilih keseluruhan jadual kecuali lajur asal Aliran dan buat histogram bertindan biasa merentasi Inset — Histogram (Sisipkan — Carta Lajur):

Jika anda kini memilih lajur biru dan menjadikannya tidak kelihatan (klik kanan padanya - Format Baris – Isi – Tiada Isi), maka kita hanya mendapat apa yang kita perlukan. 

Kelebihan kaedah ini adalah kesederhanaan. Dalam minus - keperluan untuk mengira lajur tambahan.

Kaedah 3. Jika kita pergi ke merah, semuanya lebih sukar

Malangnya, kaedah sebelumnya hanya berfungsi secukupnya untuk nilai positif. Jika sekurang-kurangnya di beberapa kawasan air terjun kita masuk ke kawasan negatif, maka kerumitan tugas meningkat dengan ketara. Dalam kes ini, adalah perlu untuk mengira setiap baris (dummy, hijau dan merah) secara berasingan untuk bahagian negatif dan positif dengan formula:

Untuk tidak terlalu menderita dan tidak mencipta semula roda, templat siap sedia untuk kes sedemikian boleh dimuat turun dalam tajuk artikel ini.

Kaedah 4. Eksotik: jalur atas-bawah

Kaedah ini adalah berdasarkan penggunaan unsur khas carta rata yang kurang dikenali (histogram dan graf) – Band atas-bawah (Bar Atas-Bawah). Jalur ini menyambungkan titik dua graf secara berpasangan untuk menunjukkan dengan jelas mana antara dua titik yang lebih tinggi atau lebih rendah, yang digunakan secara aktif apabila menggambarkan fakta pelan:

Adalah mudah untuk mengetahui bahawa jika kita mengalih keluar garis carta dan meninggalkan hanya jalur naik ke bawah pada carta, maka kita akan mendapat "air terjun" yang sama.

Untuk pembinaan sedemikian, kami perlu menambah dua lagi lajur tambahan pada jadual kami dengan formula mudah yang akan mengira kedudukan dua graf halimunan yang diperlukan:

Untuk mencipta "air terjun", anda perlu memilih lajur dengan bulan (untuk tandatangan di sepanjang paksi X) dan dua lajur tambahan Jadual 1 и Jadual 2 dan bina graf biasa untuk permulaan menggunakan Sisipkan – Graf (Sisipkan — Baris Сhart):

Sekarang mari tambah jalur naik turun pada carta kami:

• Dalam Excel 2013 dan lebih baharu, ini mesti dipilih pada tab Pembina Perintah Tambah Elemen Carta — Jalur peningkatan-penurunan (Reka Bentuk — Tambah Elemen Carta — Bar Atas-Bawah)
• Dalam Excel 2007-2010 – pergi ke tab Susun Atur – Bar Pengurangan Terdahulu (Layout — Bar Atas-Bawah)

Carta kemudiannya akan kelihatan seperti ini:

Ia kekal untuk memilih graf dan menjadikannya telus dengan mengklik padanya secara bergilir dengan butang kanan tetikus dan memilih arahan Format siri data (Siri format). Begitu juga, anda boleh menukar warna jalur hitam dan putih standard yang agak lusuh kepada hijau dan merah untuk mendapatkan gambar yang lebih bagus pada akhirnya:

Dalam versi terkini Microsoft Excel, lebar bar boleh ditukar dengan mengklik pada salah satu graf lutsinar (bukan bar!) dengan butang kanan tetikus dan memilih arahan Format Siri Data – Pembersihan Sisi (Siri format — Lebar jurang).

Dalam versi Excel yang lebih lama, anda perlu menggunakan arahan Visual Basic untuk membetulkannya:

1. Serlahkan gambar rajah yang dibina
2. Tekan pintasan papan kekunci Alt+F11untuk masuk ke dalam Editor Visual Basic
3. Tekan pintasan papan kekunci Ctrl+Guntuk membuka input arahan langsung dan panel nyahpepijat Serta-merta (biasanya terletak di bahagian bawah).

4. Salin dan tampal arahan berikut di sana: ActiveChart.ChartGroups(1).GapWidth = 30 dan tekan Masukkan:

Anda boleh, sudah tentu, bermain-main dengan nilai parameter jika anda mahu. GapWidthuntuk mencapai pelepasan yang diingini:

• Cara membina carta bullet dalam Excel untuk menggambarkan KPI  
• Perkara Baharu dalam Carta dalam Excel 2013
• Cara membuat carta "langsung" interaktif dalam Excel