Şelale grafiği nasıl oluşturulur

Gittikçe daha sık farklı şirketlerin raporlarında buluşuyorum ve kursiyerlerden kademeli bir sapma diyagramının nasıl oluşturulduğunu açıklama taleplerini duyuyorum - aynı zamanda bir “şelale”, aynı zamanda bir “şelale”, aynı zamanda bir “köprü”. ”, aynı zamanda bir “köprü” vb. Şuna benziyor:

Uzaktan bakıldığında, gerçekten bir dağ nehri üzerindeki şelaleler ya da asma bir köprü gibi görünüyor - kim ne görüyor 🙂

Böyle bir diyagramın özelliği şudur:

• Parametrenin ilk ve son değerini (ilk ve son sütunlar) açıkça görüyoruz.
• Olumlu değişiklikler (büyüme) tek renkte görüntülenir (genellikle yeşil) ve olumsuz olanlar (reddetme) başkalarına (genellikle kırmızı).
  
• Bazen grafik, ara toplam sütunları da içerebilir (grix ekseni sütunlarına indi).
  

Günlük yaşamda, bu tür diyagramlar genellikle aşağıdaki durumlarda kullanılır:

• Görsel dinamik ekran zaman içindeki herhangi bir süreç: nakit akışı (nakit akışı), yatırımlar (bir projeye yatırım yapar ve ondan kar elde ederiz).
• Görüntüleme uygulama planı (şemada en soldaki sütun bir gerçektir, en sağdaki sütun bir plandır, tüm diyagram istenen sonuca doğru ilerleme sürecimizi yansıtır)
• Görsel ihtiyacınız olduğunda faktörleri gösterparametremizi etkileyen faktörler (kârın faktör analizi - nelerden oluştuğu).
  

Böyle bir grafik oluşturmanın birkaç yolu vardır - hepsi Microsoft Excel sürümünüze bağlıdır.

Yöntem 1: En kolay: Excel 2016 ve daha yeni sürümlerde yerleşik tür

Excel 2016, 2019 veya daha yenisine (veya Office 365) sahipseniz, böyle bir grafik oluşturmak zor değildir - Excel'in bu sürümlerinde varsayılan olarak bu tür yerleşik olarak bulunur. Sadece veri içeren bir tablo seçmek ve sekmede seçmek gerekli olacaktır. Ekle (Ekle) Komuta Basamaklı (Şelale):

Sonuç olarak, neredeyse hazır bir şema elde edeceğiz:

Pozitif ve negatif sütunlar için istediğiniz dolgu renklerini hemen ayarlayabilirsiniz. Bunu yapmanın en kolay yolu uygun satırları seçmektir. Artırmak и azaltmak doğrudan göstergede ve üzerlerine sağ tıklayarak komutu seçin Doldurmak (Doldurmak):

Grafiğe alt toplamlı sütunlar veya son bir sütun toplamı eklemeniz gerekiyorsa, bunu işlevleri kullanarak yapmak en uygunudur. ARA TOPLAMLAR (ARA TOPLAMLAR) or ÜNİTESİ (AGREGA). Yukarıda bulunan benzer toplamları hariç tutarken, tablonun başından itibaren biriken tutarı hesaplayacaklar:

Bu durumda, ilk argüman (9) matematiksel toplama işleminin kodudur ve ikinci (0), fonksiyonun sonuçlarda önceki çeyrekler için önceden hesaplanmış toplamları yok saymasına neden olur.

Toplamları olan satırları ekledikten sonra, diyagramda görünen toplam sütunları seçmeye (sütun üzerinde iki ardışık tek tıklama yapın) kalır ve fareye sağ tıklayarak komutu seçin Toplam olarak ayarla (Toplam olarak ayarla):

Seçilen sütun x eksenine gelecek ve rengi otomatik olarak griye değiştirecektir.

Aslında hepsi bu kadar – şelale diyagramı hazır:

Yöntem 2. Evrensel: görünmez sütunlar

Excel 2013 veya daha eski sürümleriniz varsa (2010, 2007 vb.), yukarıda açıklanan yöntem sizin için çalışmayacaktır. Normal bir yığılmış histogramdan (çubukları üst üste toplayarak) dolaşıp eksik şelale grafiğini kesmeniz gerekecek.

Buradaki hile, kırmızı ve yeşil veri satırlarımızı doğru yüksekliğe yükseltmek için şeffaf destek sütunları kullanmaktır:

Böyle bir grafik oluşturmak için, kaynak verilere formüller içeren birkaç yardımcı sütun daha eklememiz gerekir:

• İlk olarak, fonksiyonu kullanarak pozitif ve negatif değerleri ayrı sütunlara ayırarak orijinal sütunumuzu bölmemiz gerekiyor. IF (EĞER).  
• İkinci olarak, sütunların önüne bir sütun eklemeniz gerekecek. emzikler, burada ilk değer 0 olacak ve ikinci hücreden başlayarak formül bu çok şeffaf destekleyici sütunların yüksekliğini hesaplayacaktır.
  

Bundan sonra, orijinal sütun hariç tüm tabloyu seçmek kalır. akış ve genelinde düzenli bir yığılmış histogram oluşturun İç Metin - Histogram (Ekle — Sütun Grafiği):

Şimdi mavi sütunları seçip görünmez yaparsanız (üzerlerine sağ tıklayın - Satır Formatı – Doldur – Doldurma Yok), o zaman ihtiyacımız olanı alırız. 

Bu yöntemin avantajı basitliğidir. Eksilerde - yardımcı sütunları sayma ihtiyacı.

Yöntem 3. Kırmızıya gidersek her şey daha zor

Ne yazık ki, önceki yöntem yalnızca pozitif değerler için yeterince çalışır. En azından bir alanda şelalemiz olumsuz bir alana girerse, görevin karmaşıklığı önemli ölçüde artar. Bu durumda, formüllerle negatif ve pozitif kısımlar için her satırı (kukla, yeşil ve kırmızı) ayrı ayrı hesaplamak gerekecektir:

Çok fazla acı çekmemek ve tekerleği yeniden icat etmemek için böyle bir durum için hazır bir şablon bu makalenin başlığından indirilebilir.

Yöntem 4. Egzotik: yukarı-aşağı bantlar

Bu yöntem, düz çizelgelerin (histogramlar ve grafikler) az bilinen özel bir öğesinin kullanımına dayanmaktadır. Yukarı-aşağı bantlar (Yukarı-Aşağı Çubuklar). Bu bantlar, plan gerçeğini görselleştirirken aktif olarak kullanılan iki noktadan hangisinin daha yüksek veya daha düşük olduğunu açıkça göstermek için iki grafiğin noktalarını çiftler halinde birleştirir:

Grafiklerin çizgilerini kaldırırsak ve grafikte sadece yukarı-aşağı bantları bırakırsak, aynı “şelaleyi” alacağımızı anlamak kolaydır.

Böyle bir yapı için, gerekli iki görünmez grafiğin konumunu hesaplayacak basit formüllerle tablomuza iki ek sütun daha eklememiz gerekiyor:

Bir "şelale" oluşturmak için ayları olan bir sütun (X ekseni boyunca imzalar için) ve iki ek sütun seçmeniz gerekir. Zamanlama 1 и Zamanlama 2 ve kullanarak yeni başlayanlar için düzenli bir grafik oluşturun Ekle – Grafik (Ekle — Satır Grafiği):

Şimdi grafiğimize yukarı-aşağı bantları ekleyelim:

• Excel 2013 ve daha yeni sürümlerde, bu sekmede seçilmelidir Inşaatçı Komuta Grafik Öğesi Ekle — Artış-azalma bantları (Tasarım — Grafik Öğesi Ekle — Yukarı-Aşağı Çubuklar)
• Excel 2007-2010'da - sekmeye gidin Düzen – İlerleme-Azaltma Çubukları (Düzen — Yukarı-Aşağı Çubuklar)

Grafik daha sonra şöyle görünecek:

Grafikleri seçmek ve sırayla farenin sağ tuşuyla tıklayarak ve komutu seçerek şeffaf hale getirmek için kalır. Veri serisi formatı (Biçim serisi). Benzer şekilde, sonunda daha güzel bir resim elde etmek için standart, oldukça eski püskü görünen siyah ve beyaz şerit renklerini yeşil ve kırmızı olarak değiştirebilirsiniz:

Microsoft Excel'in en son sürümlerinde, çubukların genişliği, saydam grafiklerden birine (çubuklar değil!) sağ fare tuşu ile tıklanarak ve komut seçilerek değiştirilebilir. Veri Serisi Formatı – Yan Boşluk (Biçim serisi — Boşluk genişliği).

Excel'in eski sürümlerinde bunu düzeltmek için Visual Basic komutunu kullanmanız gerekiyordu:

1. Yerleşik diyagramı vurgulayın
2. Klavye kısayoluna basın Ara Toplam+F11Visual Basic Düzenleyicisine girmek için
3. Klavye kısayoluna basın Ctrl+Gdoğrudan komut girişi ve hata ayıklama panelini açmak için Acil (genellikle altta bulunur).

4. Aşağıdaki komutu kopyalayıp oraya yapıştırın: ActiveChart.ChartGroups(1).GapWidth = 30 ve basın Keşfet:

Elbette, isterseniz parametre değeriyle oynayabilirsiniz. boşluk genişliğiİstenilen açıklığa ulaşmak için:

• KPI'yı görselleştirmek için Excel'de madde işareti grafiği nasıl oluşturulur  
• Excel 2013'teki Grafiklerdeki Yenilikler
• Excel'de etkileşimli bir "canlı" grafik nasıl oluşturulur