Cara membuat bagan air terjun

Semakin sering saya bertemu dalam pelaporan perusahaan yang berbeda dan mendengar permintaan dari peserta pelatihan untuk menjelaskan bagaimana diagram kaskade penyimpangan dibangun - itu juga "air terjun", itu juga "air terjun", itu juga "jembatan". ”, itu juga merupakan “jembatan”, dll. . Ini terlihat seperti ini:

Dari kejauhan, itu benar-benar terlihat seperti air terjun di sungai gunung atau jembatan gantung – siapa yang melihat apa

Keunikan diagram semacam itu adalah:

• Kami dengan jelas melihat nilai awal dan akhir dari parameter (kolom pertama dan terakhir).
• Perubahan positif (pertumbuhan) ditampilkan dalam satu warna (biasanya hijau), dan yang negatif (penurunan) kepada orang lain (biasanya merah).
  
• Terkadang bagan juga dapat berisi kolom subtotal (abu-abumendarat di kolom sumbu x).
  

Dalam kehidupan sehari-hari, diagram seperti itu biasanya digunakan dalam kasus-kasus berikut:

• visual tampilan dinamis setiap proses dalam waktu: arus kas (arus kas), investasi (kami berinvestasi dalam sebuah proyek dan mendapatkan keuntungan darinya).
• Visualisasi rencana implementasi (kolom paling kiri dalam diagram adalah fakta, kolom paling kanan adalah rencana, seluruh diagram mencerminkan proses kami bergerak menuju hasil yang diinginkan)
• Ketika Anda membutuhkan visual menunjukkan faktoryang mempengaruhi parameter kami (analisis faktorial keuntungan – terdiri dari apa).
  

Ada beberapa cara untuk membuat bagan seperti itu – semuanya tergantung pada versi Microsoft Excel Anda.

Metode 1: Termudah: Tipe bawaan di Excel 2016 dan yang lebih baru

Jika Anda memiliki Excel 2016, 2019 atau yang lebih baru (atau Office 365), maka membuat bagan seperti itu tidak sulit – versi Excel ini sudah memiliki tipe bawaan ini secara default. Anda hanya perlu memilih tabel dengan data dan memilih pada tab Menyisipkan (Memasukkan) perintah Cascading (Air terjun):

Akibatnya, kita akan mendapatkan diagram yang hampir jadi:

Anda dapat segera mengatur warna isian yang diinginkan untuk kolom positif dan negatif. Cara termudah untuk melakukannya adalah dengan memilih baris yang sesuai Meningkatkan и mengurangi langsung di legenda dan dengan mengklik kanan pada mereka, pilih perintah Mengisi (Mengisi):

Jika Anda perlu menambahkan kolom dengan subtotal atau total kolom akhir ke bagan, maka akan lebih mudah untuk melakukannya menggunakan fungsi SUBTOTAL (SUBTOTAL) or UNIT (AGREGAT). Mereka akan menghitung jumlah yang terakumulasi dari awal tabel, sementara mengecualikan total serupa yang terletak di atas:

Dalam kasus ini, argumen pertama (9) adalah kode operasi penjumlahan matematika, dan argumen kedua (0) menyebabkan fungsi mengabaikan total yang sudah dihitung untuk kuartal sebelumnya dalam hasil.

Setelah menambahkan baris dengan total, tetap memilih kolom total yang muncul pada diagram (buat dua klik tunggal berturut-turut pada kolom) dan, dengan mengklik kanan mouse, pilih perintah Tetapkan sebagai total (Tetapkan sebagai total):

Kolom yang dipilih akan mendarat di sumbu x dan otomatis berubah warna menjadi abu-abu.

Sebenarnya, itu saja – diagram air terjun sudah siap:

Metode 2. Universal: kolom tak terlihat

Jika Anda memiliki Excel 2013 atau versi yang lebih lama (2010, 2007, dll.), maka metode yang dijelaskan di atas tidak akan berfungsi untuk Anda. Anda harus berkeliling dan memotong bagan air terjun yang hilang dari histogram bertumpuk biasa (menjumlahkan batang di atas satu sama lain).

Triknya di sini adalah menggunakan kolom prop transparan untuk menaikkan baris data merah dan hijau ke ketinggian yang benar:

Untuk membuat bagan seperti itu, kita perlu menambahkan beberapa kolom tambahan dengan rumus ke data sumber:

• Pertama, kita perlu membagi kolom asli kita dengan memisahkan nilai positif dan negatif menjadi kolom terpisah menggunakan fungsi IF (JIKA).  
• Kedua, Anda perlu menambahkan kolom di depan kolom dot, di mana nilai pertama adalah 0, dan mulai dari sel kedua, rumus akan menghitung tinggi kolom pendukung yang sangat transparan tersebut.
  

Setelah itu, tinggal memilih seluruh tabel kecuali kolom aslinya Aliran dan buat histogram bertumpuk reguler di seluruh Sisipan — Histogram (Sisipkan — Bagan Kolom):

Jika sekarang Anda memilih kolom biru dan membuatnya tidak terlihat (klik kanan pada kolom tersebut – Format Baris – Isi – Tanpa Isi), maka kita hanya mendapatkan apa yang kita butuhkan. 

Keuntungan dari metode ini adalah kesederhanaan. Dalam minus - kebutuhan untuk menghitung kolom tambahan.

Metode 3. Jika kita masuk ke merah, semuanya lebih sulit

Sayangnya, metode sebelumnya hanya berfungsi secara memadai untuk nilai positif. Jika setidaknya di beberapa area air terjun kami masuk ke area negatif, maka kompleksitas tugas meningkat secara signifikan. Dalam hal ini, perlu untuk menghitung setiap baris (dummy, hijau dan merah) secara terpisah untuk bagian negatif dan positif dengan rumus:

Agar tidak terlalu menderita dan tidak menemukan kembali roda, templat yang sudah jadi untuk kasus seperti itu dapat diunduh di judul artikel ini.

Metode 4. Eksotis: band naik-turun

Metode ini didasarkan pada penggunaan elemen grafik datar (histogram dan grafik) yang tidak banyak diketahui secara khusus – Band atas-bawah (Bilah Atas-Bawah). Pita-pita ini menghubungkan titik-titik dari dua grafik berpasangan untuk menunjukkan dengan jelas mana dari dua titik yang lebih tinggi atau lebih rendah, yang secara aktif digunakan saat memvisualisasikan fakta rencana:

Sangat mudah untuk mengetahui bahwa jika kita menghapus garis grafik dan hanya menyisakan pita naik-turun pada grafik, maka kita akan mendapatkan "air terjun" yang sama.

Untuk konstruksi seperti itu, kita perlu menambahkan dua kolom tambahan ke tabel kita dengan rumus sederhana yang akan menghitung posisi dua grafik tak terlihat yang diperlukan:

Untuk membuat "air terjun", Anda harus memilih kolom dengan bulan (untuk tanda tangan di sepanjang sumbu X) dan dua kolom tambahan Jadwalkan 1 и Jadwalkan 2 dan buat grafik biasa sebagai permulaan menggunakan Sisipkan – Grafik (Sisipkan — Baris hart):

Sekarang mari tambahkan band naik-turun ke bagan kita:

• Di Excel 2013 dan yang lebih baru, ini harus dipilih pada tab Pembina perintah Tambahkan Elemen Bagan — Pita kenaikan-penurunan (Desain — Tambahkan Elemen Bagan — Batang Atas-Bawah)
• Di Excel 2007-2010 – buka tab Tata Letak – Bilah Penurunan Lanjut (Tata Letak — Bilah Atas-Bawah)

Bagan kemudian akan terlihat seperti ini:

Tetap memilih grafik dan membuatnya transparan dengan mengkliknya secara bergantian dengan tombol kanan mouse dan memilih perintah Format seri data (Format seri). Demikian pula, Anda dapat mengubah warna garis hitam dan putih standar yang terlihat lusuh menjadi hijau dan merah untuk mendapatkan gambar yang lebih bagus pada akhirnya:

Dalam versi terbaru Microsoft Excel, lebar batang dapat diubah dengan mengklik salah satu grafik transparan (bukan batang!) dengan tombol kanan mouse dan memilih perintah Format Seri Data – Jarak Samping (Format seri — Lebar celah).

Di versi Excel yang lebih lama, Anda harus menggunakan perintah Visual Basic untuk memperbaikinya:

1. Sorot diagram yang dibangun
2. Tekan pintasan keyboard lain+F11untuk masuk ke Editor Visual Basic
3. Tekan pintasan keyboard Ctrl+Guntuk membuka input perintah langsung dan panel debug Segera (biasanya terletak di bagian bawah).

4. Salin dan tempel perintah berikut di sana: ActiveChart.ChartGroups(1).GapWidth = 30 dan tekan Enter:

Anda dapat, tentu saja, bermain-main dengan nilai parameter jika Anda mau. lebar celahuntuk mencapai izin yang diinginkan:

• Cara membuat bagan peluru di Excel untuk memvisualisasikan KPI  
• Apa yang Baru di Bagan di Excel 2013
• Cara membuat bagan "langsung" interaktif di Excel