Tutorial: Buat Waterfall Bagan di Excel

Pelajari cara membuat grafik air terjun di Excel. Kami telah memberikan langkah-demi-langkah tutorial untuk membuat grafik seperti di Excel 2007, 2010, 2013 dan 2016.

A+ A-

Sebuah grafik terjun digunakan untuk menunjukkan efek keseluruhan dengan memperkenalkan langkah-langkah positif atau negatif. Grafik terjun visualisasi perjalanan dari titik awal ke titik akhir dengan pergi melalui pasang surut. Misalnya, jumlah item dalam perubahan persediaan selama periode waktu. Kadang-kadang persediaan keseluruhan item naik (karena baru dibeli, diperbaiki atau dikembalikan saham) dan kadang-kadang turun (karena penjualan dan rusak saham). Sebuah grafik air terjun akan cocok untuk menunjukkan bagaimana persediaan barang yang berfluktuasi selama berbagai bulan dari tahun.

Cara Membuat Air terjun Bagan di Excel

Membuat grafik terjun di Excel mudah. Hal ini membutuhkan beberapa langkah tambahan untuk mempersiapkan data Anda sebelum merencanakan. Sayangnya, versi Excel sebelumnya tidak memberikan pilihan langsung untuk membuat bagan tersebut. Tapi sudahlah, itu masih ada ilmu roket! Kami akan mengajarkan Anda bagaimana untuk pergi tentang itu.

Dalam tutorial ini, kita akan menggunakan sampel Excel datasheet untuk menunjukkan berbagai langkah menciptakan grafik air terjun. Sampel datasheet berisi data pendapatan perusahaan untuk tahun tertentu. Perusahaan dimulai dengan sejumlah uang di kas dan kemudian ada keuntungan atau kerugian angka untuk setiap bulan. Anda dapat men-download air terjun ini lembar data grafik .

Jika Anda adalah untuk membuat grafik terjun dari data tersebut, Anda mungkin akan memiliki data seperti yang ditunjukkan di bawah ini. Ini hanya berisi nama bulan dan laporan laba rugi selama bulan itu. Start dan poin End menunjukkan jumlah uang yang Anda miliki di awal dan di akhir tahun.

Datasheet untuk grafik terjun di Excel

Sekarang, sebelum memulai tutorial, mari kita tunjukkan grafik terjun jadi yang kita akan buat dari data ini.

Contoh grafik terjun di Excel

Ini disebut grafik air terjun. Grafik tersebut dipopulerkan oleh McKinsey & Company . Dalam grafik ini setiap langkah dimulai dari tingkat langkah sebelumnya. Langkah bisa naik atau turun tergantung pada nilai langkah itu. Secara keseluruhan, grafik memberikan kesan jalan melangkah yang jatuh dan naik dari waktu ke waktu.

Sekarang, mari kita belajar cara membuat jenis grafik. Meskipun Excel 2016 telah memperkenalkan grafik terjun dasar tetapi, untuk membuat grafik seperti menunjukkan di atas, Anda akan perlu untuk membuat beberapa tambahan data Anda dan kemudian memodifikasi ditumpuk kolom Char t untuk mencapai hasil yang ditunjukkan di atas.

Langkah 1: Tambahkan kolom

Pertama-tama, Anda harus menambahkan kolom berikut di datasheet Anda. Kolom ini diperlukan untuk penciptaan grafik terjun:

  1. Mendasarkan
  2. Akhir
  3. Meningkat
  4. Mengurangi
  5. Mulai

Kolom dasar akan berisi jumlah total uang yang Anda miliki setelah menyesuaikan laba atau rugi bulan sebelumnya. Angka dasar akan bertindak sebagai dasar untuk menciptakan bulan depan bar chart. End kolom akan berisi hanya satu angka yang akan menunjukkan jumlah uang yang Anda miliki di akhir tahun. Meningkatkan dan Penurunan kolom akan memiliki nomor atau nol tergantung pada apakah laba atau rugi yang timbul selama bulan itu. Mulai kolom juga akan memiliki figur tunggal yang menunjukkan jumlah uang yang Anda miliki di awal tahun.

Langkah 2: Gunakan rumus untuk menghitung data yang dibutuhkan

Sekarang Anda akan perlu mengisi nilai dalam kolom baru. Anda dapat dengan mudah mengisi kolom ini menggunakan rumus.

Formula di F2

= IF (G2> 0, G2,0)

  • Formula ini bertanya berapa banyak uang yang Anda harus mulai dengan.

Formula di E3

= IF (G3 <= 0, -G3,0)

  • Formula ini hanya melihat nilai di kolom Laba / Rugi dan jika itu adalah kerugian, itu menambah nilai yang sama di E3 dan jika itu adalah keuntungan formula menambahkan 0 di kolom Penurunan.
  • Auto mengisi rumus ini dari E4 ke E14.

Formula di D3

= IF (G3> 0, G3,0)

  • Formula ini memasuki laba di kolom Meningkatkan jika ada keuntungan; selain itu memasuki 0.
  • Auto mengisi rumus ini dari D4 ke D14.

Formula di C3

= (B2 + F2) -E3

  • Formula ini menghitung jumlah dasar dengan mengambil di akun laba / rugi pada akhir bulan.
  • Auto mengisi rumus ini dari C4 ke C14.

Formula di C15

= B14 + D14 + E14

  • Formula ini menghitung jumlah bersih secara keseluruhan di tangan Anda pada akhir tahun.

Itu saja! datasheet Excel Anda siap untuk dikonversi menjadi grafik terjun! Mari kita lihat bagaimana datasheet akan muncul sekarang:

Pengolahan datasheet untuk membuat grafik terjun di Excel - langkah 2

Langkah 3: Buat Kolom Bagan Bertumpuk

  1. Pilih semua kolom kecuali kolom terakhir (laba / rugi kolom)
  2. Memasukkan grafik dengan pergi ke Insert> Charts> Kolom> Tumpukan Kolom

Memasukkan grafik kolom ditumpuk untuk membuat grafik terjun

Sebuah grafik ditumpuk sederhana akan dimasukkan dalam datasheet Anda. Dalam grafik ditumpuk, setiap batang berisi lebih dari satu buah yang menggambarkan nilai data. Potongan-potongan ini ditumpuk di atas satu sama lain. Dalam grafik kami nilai kolom dasar datang pertama dan di atas itu datang kenaikan atau penurunan dari bulan tersebut. Berikut adalah grafik kolom ditumpuk:

Pengolahan ditumpuk grafik untuk membuat grafik terjun di Excel - langkah 1

Sekarang, pada dasarnya, kita perlu memformat grafik ini untuk membuat grafik air terjun. Pada bagian berikut kita akan menjelaskan modifikasi apa yang diperlukan dan bagaimana melakukannya.

Langkah 4: Memodifikasi grafik kolom ditumpuk untuk mengubahnya menjadi grafik terjun

Pertama-tama kita akan membuat perlu membuat bagian dasar dari bar menghilang. Seperti yang Anda lihat, jika kita bisa menghapus bagian dasar warna biru setiap kolom, maka apa yang akan ditinggalkan dengan dasarnya grafik air terjun!

  • Ambil klik kanan pada salah satu bar biru untuk memilih seri biru seluruh
  • Pilih Format Data Series ... dari pop up menu

Pengolahan ditumpuk grafik untuk membuat grafik terjun di Excel - langkah 2

Anda akan mendapatkan kotak dialog Format Data Series. Di sini Anda dapat mengubah warna isi, warna border, border gaya dll untuk seri data yang telah Anda pilih. Membuat perubahan berikut dalam kotak dialog ini:

  • Isi: set ke No fill
  • Border Color: set ke Tidak ada garis
  • Seri Pilihan: mengatur Gap Lebar ke suatu tempat sekitar 20% (ini akan membuat kolom bar yang lebih luas dengan mengurangi kesenjangan antara mereka

Pengolahan ditumpuk grafik untuk membuat grafik terjun di Excel - langkah 3

Setelah melakukan perubahan tersebut, klik tombol Close untuk menutup kotak dialog. Anda harus mendapatkan grafik air terjun seperti yang ditunjukkan di bawah ini. Perlu dicatat bahwa bagian dasar biru belum dihapus sama sekali. Kami baru saja membuat terlihat dengan menghapus warna fill dan warna garis.

Pengolahan ditumpuk grafik untuk membuat grafik terjun di Excel - langkah 4

Anda bekerja dilakukan! Hanya beberapa penyesuaian kecil dilakukan untuk membuat grafik air terjun ini terlihat benar-benar profesional.

  • Akan lebih bermakna jika Awal dan Akhir kolom dengan warna yang sama. Ambil klik kanan pada kolom End berwarna merah dan pilih Format data Series. Kemudian di bawah opsi Fill, pilih Solid fill dan mengisi setiap warna yang Anda inginkan pada kolom End.
  • Demikian pula, Anda juga dapat mengubah warna Meningkatkan dan Penurunan grafik kolom jika Anda tidak menyukai warna default.
  • Sekarang kita telah membuat porsi Basis tak terlihat, tidak ada gunanya menunjukkan Base di legenda. Klik dua kali pada legenda Base dan pilih Hapus untuk menghapus Basis dari legenda.
  • Jika Anda ingin, Anda sama dapat menghapus Start dan End juga dari daftar legenda.
  • Anda juga mungkin ingin memindahkan semua legenda dari sisi kanan ke bagian bawah grafik. Hal ini akan memberikan lebih banyak ruang untuk grafik kolom melebar. Untuk ini mengambil klik kanan pada legenda dan pilih Format Legenda ... pilihan. Sebuah kotak dialog akan muncul. Dari Legenda Options, pilih Bawah sebagai posisi legenda.
  • Anda dapat menambahkan nilai dalam Meningkatkan dan Penurunan grafik kolom untuk membuat grafik lebih mudah dibaca. Ambil klik kanan pada salah satu kolom Meningkatkan grafik dan pilih Tambah Data Label. cara yang sama Anda dapat menambahkan nilai untuk kolom Penurunan.

Itu saja! grafik air terjun Anda siap!

grafik terjun selesai

Charts Waterfall sederhana

Metode di atas dapat membuat serangkaian data yang divisualisasikan sebagai grafik air terjun. Tapi grafik air terjun juga bisa dibuat dengan data sederhana. Dan tentu saja grafik tersebut lebih sederhana untuk membuat juga. Excel 2016 juga memperkenalkan pilihan untuk membuat grafik air terjun sederhana. Mari kita lihat contoh yang diambil dari Wikipedia.

Berikut ini adalah tokoh-tokoh dari pendapatan dan beban perusahaan:

  • Pendapatan produk: $ 420K
  • Pendapatan jasa: $ 210K
  • Biaya tetap: $ -170K
  • Biaya variabel: $ -140K
  • Total: $ 320K

Data ini dapat diplot dalam bentuk grafik terjun seperti yang ditunjukkan di bawah ini:

Excel air terjun grafik pola pendapatan dan beban

Microsoft Office 2016 sudah termasuk pilihan untuk membuat grafik air terjun sederhana seperti.

Klik Insert> Insert Waterfall atau grafik Stock> Waterfall.

pilihan grafik terjun di Excel 2016

Kami berharap bahwa artikel ini tentang cara membuat grafik terjun di Microsoft Excel berguna untuk Anda. Jika Anda memiliki pertanyaan terkait dengan topik ini, jangan ragu untuk bertanya di bagian komentar. Kami akan mencoba yang terbaik untuk membantu Anda. Terima kasih telah menggunakan TechWelkin!