Как да създадете диаграма с водопад в Excel

Този урок ще покаже как да създадете диаграма с водопад във всички версии на Excel: 2007, 2010, 2013, 2016 и 2022.

Диаграма с водопад (наричана още мостова диаграма, диаграма с летящи тухли, каскадна диаграма или диаграма Марио) е графика, която визуално разбива кумулативния ефект, който поредица от последователни положителни или отрицателни стойности са допринесли за крайния резултат.

Обикновено първата и последната колони представляват началните и крайните стойности, разделени от междинни корекции, които изобразяват последователността от стъпки, необходими за преминаване от точка А до точка Б.

Този тип диаграма е широко използван от професионалисти във всички големи индустрии за провеждане на количествен анализ-от декодиране на финансови отчети до мониторинг на запасите в магазините.

Мостовите графики бяха въведени като вграден тип диаграми само в Excel 2016, което означава, че потребителите на по-ранните версии на Excel нямат друг избор, освен да създадат диаграмата от нулата.

Поради тази причина в този урок ще научите как да създадете тази диаграма Mario както ръчно от нулата, така и като се обърнете към родния тип диаграма, който върши цялата мръсна работа вместо вас.

Приготвяме се да започнем

Точно от портата трябва да хвърлим някои необработени данни в картината. Така че, за илюстрация, нека приемем, че сте тръгнали да анализирате тримесечния нетен паричен поток на някоя измислена компания.

Имайки предвид всичко това, вземете предвид следните данни:

За да ви помогнем да настроите диаграмата според вашите собствени нужди, нека поговорим за таблицата по -подробно:

• Месечен цикъл: Тази колона представлява последователност от положителни и отрицателни стойности, които да бъдат нанесени на диаграмата.
• Нетни парични потоци: Тази колона се състои от съответните стойности. Първият (В2) и последно (В13) клетките съдържат началните и крайните стойности, докато останалите числа между тях означават количествените промени във времето.

Сега, след като знаете основните неща на диаграмата, можем да преминем към петна.

Как да създадете диаграма с водопад в Excel 2016+

Първо, нека ви покажа решение за тези, които търсят пътя на най -малкото съпротивление.

Използвайки вградения, готов шаблон, при условие че използвате Excel 2016 или 2022-не трябва да ви отнема повече от няколко прости стъпки, за да настроите диаграма с водопад.

Всичко това обаче идва с цената на ограничените възможности за персонализиране и потенциалните проблеми със съвместимостта; по -старите версии на Excel просто не могат да обработват по -нови типове диаграми.

В крайна сметка: винаги, когато ще изпращате диаграма, винаги е по -безопасно да я изградите ръчно.

Стъпка 1: Начертайте диаграма с водопад.

За начало създайте стандартна диаграма с водопад въз основа на вашите действителни данни. Красотата на този метод е, че не е нужно да прескачате никакви обръчи:

1. Маркирайте всички данни (A1: B13).
2. Отидете на Вмъкване раздел.
3. Изберете „Поставете диаграма на водопад, фуния, запас, повърхност или радар”Бутон.
4. Избирам „Водопад“.

Незабавно вашата диаграма с летящи тухли ще изникне:

Стъпка 2: Задайте колоните с междинна сума и обща сума.

Следващата ни стъпка е да отделим началните и крайните стойности от „летящите тухли“.

Щракнете двукратно и след това щракнете с десния бутон върху колоните, представляващи точките с данни „Баланс за пренасяне" и "Текущ баланс“И изберете„Задайте като общо.”

Технически може да искате да спрете точно там и да оставите диаграмата такава, каквато е. Но тази каскадна графика не ни казва нищо за нанесените върху нея данни.

Защо да се задоволявате с по -малко? Вместо това можете значително да подобрите новосъздадената си диаграма Марио, като промените няколко неща тук и там.

Стъпка #3: Променете цветовата схема.

Един ефективен начин да подобрите диаграмата си е да оставите цветовата схема да работи за вас, а не обратното. Преоцветете поредицата от данни по начин, който помага да се предаде какво представлява диаграмата.

1. Щракнете двукратно и след това щракнете с десния бутон върху елемента легенда, представляващ Серия „Намаляване“.
2. Изберете „Попълнете”Бутон.
3. Изберете червен от цветовата палитра.

По същия начин пребоядисвайте Серия „Увеличаване“ към зелено и Серия „Общо“ до синьо. Веднъж там, вашата каскадна диаграма трябва да изглежда така:

Стъпка #4: Приспособете диапазоните на вертикалните оси към вашите действителни данни.

За да увеличите плаващите колони за повече подробности, променете скалата на вертикалната ос. Щракнете с десния бутон върху основната вертикална ос и щракнете върху „Форматиране на оста.”

В Форматиране на оста панел със задачи, изпълнете следните прости стъпки:

1. Превключете към Опции на оста раздел.
2. Нагласи Минимални граници да се "50,000.”
3. Нагласи Максимални граници да се "110,000.”

Стъпка #5: Фина настройка на детайлите.

Здравият разум е, че колкото повече елементи съдържат диаграмата, толкова по -меко изглежда тя. Така че нека се отървем от ненужните елементи, като премахнем легендата и линиите на мрежата.

Щракнете с десния бутон върху легендата на диаграмата и изберете „Изтрий”От изскачащото меню. Повторете същия процес за линиите на мрежата.

Накрая променете заглавието на диаграмата и можете да я наречете ден!

Как да създадете диаграма с водопад в Excel 2007, 2010 и 2013

Този урок ще завърши точно тук, ако показаният по -горе метод е съвместим с всички версии на Excel. За съжаление това не е така.

Поради тази причина може да помислите за ръчно изчертаване на диаграмата от нулата, дори ако използвате Excel 2016 или 2022.

Очевидно отнема повече време, за да се състави графиката по този начин, но изплащането си заслужава усилията.

До края на този раздел ще научите как да създадете тази персонализирана, динамична мостова диаграма, съвместима с всички версии на Excel:

Без повече шум, нека топката се търкаля.

Стъпка 1: Подгответе данните от диаграмата.

Честно казано, тази стъпка е най -трудната.

Просто казано, нашата персонализирана графика на водопада ще бъде създадена въз основа на подредена колонна диаграма. Но за да се справим, трябва да добавим някои допълнителни помощни данни към уравнението.

За ваше удобство разгледайте бързо как трябва да бъдат организирани данните ви в диаграмата до края на подготвителната фаза.

За тези, които правят първите си бебешки стъпки по пътя към овладяването на Excel, тази таблица може да изглежда направо обезсърчаваща, но в действителност всеки може да се справи със задачата, като следва подробните инструкции, описани по -долу.

Нека започнем, като добавим три помощни колони, наречени „Невидим” (колона Б), “Нараства” (колона C), и "Намаляване” (колона D), които ще съдържат фиктивни стойности за позициониране на вашите действителни данни в бъдещата диаграма.

И ако колони Увеличете и Намаляване са по-скоро обясними, с какво става въпрос колона Невидим? По принцип тези стойности ще формират невидимата основа на бъдещата подредена колонна диаграма, за да поддържат действителните стойности до подходящата височина.

Нека се справим колона Увеличаване първо: Въведете следната формула в клетката C2 и го плъзнете надолу с помощта на дръжката за запълване, за да го изпълните за останалите клетки (C3: C13):

1	= IF (E2> 0, E2, 0)

Сега въведете огледалната формула в първата клетка на колона Намаляване (колона D) и го копирайте също:

1	= IF (E2 <= 0, -E2, 0)

Двама надолу, един за тръгване. След като сте намерили необходимите данни от диаграмата, вече можете да настроите колона Невидим за точно начертаване на колебанията на паричните потоци, като въведете следната формула във втората клетка на колоната (В3) и го копирате до В12:

1	= B2+C2-D3

ЗАБЕЛЕЖКА: Абсолютно важно е да напуснете първата и последната клетки (В2 и В13) от колоната празна!

Не забравяйте да форматирате всички данни на графиката като валута (Начало> Група номера> Валута).

В този момент ето как трябва да изглежда разширената ви таблица:

Като една последна корекция, настройте още една колона, наречена „Етикети”, Който ще съдържа фантастични персонализирани етикети с данни.

След като настроите тази колона, въведете тази формула във втората клетка на колона Етикети (F3) и го копирайте, оставяйки първата клетка (F2) и последната клетка (F13) празно:

1	= IF (E3 <= 0, TEXT (-E3, "$#, ##") & CHAR (10) & TEXT (-E3/SUM (B3: D3), "(#% ↓)"), TEXT (E3, "$#, ##") & CHAR (10) & TEXT (E3/SUM (B3: D3), "(#% ↑)"))

Тези, които работят с други формати на числа, трябва да прилагат тази формула вместо това:

1	= IF (E3 <= 0, TEXT (-E3, "#, ##") & CHAR (10) & TEXT (-E3/SUM (B3: D3), "(#% ↓)"), TEXT (E3, " #, ## ") & CHAR (10) & TEXT (E3/SUM (B3: D3)," (#% ↑) "))

Стъпка 2: Изградете подредена колонна диаграма.

Успяхме да преодолеем гърбицата, така че най -накрая дойде времето за изграждане на нашата диаграма на водопада.

1. Изберете всички данни в колони Период, невидим, увеличаване, и Намаляване (A1: D13).
2. Отидете на Вмъкване раздел.
3. Щракнете върху „Вмъкване на колона или стълбовидна диаграма.”
4. Избирам "Подредена колона.”

Excel ще събере тази проста графика, която в крайна сметка ще бъде трансформирана в зашеметяваща диаграма на водопада:

Стъпка #3: Скрийте серията „Невидими“.

Преди да преминем към останалата част от диаграмата, скрийте основните серии от данни, като натиснете плаващите колони до върха. Щракнете с десния бутон върху някоя от сините колони, представляващи Серия "Невидим" и отворете Форматиране на серия от данни панел със задачи.

Веднъж там, направете помощните колони невидими:

1. Отидете на Fill & Line раздел-the Попълнете в по -старите версии на Excel.
2. Под „Попълнете,"Изберете"Без запълване.”
3. Под „Граница,"Щракнете върху"Няма ред.”

Стъпка #4: Регулирайте цветовата схема.

В същото Форматиране на серия от данни панел със задачи, преоцветете всяка серия от данни, като следвате идентичните стъпки, показани по -горе (Опции за серия > Попълнете > Твърдо пълнене > Цвят), така че да може да се разбере интуитивно с един поглед:

1. За Серия „Намаляване“ избирам червен.
2. За Серия „Увеличаване“ избирам зелено.
3. Освен това щракнете двукратно върху колоните „Баланс за пренасяне“ и "Текущ баланс" и ги оцвети отново син.

Ето какво трябва да получите в този момент:

Стъпка #5: Променете ширината на празнината на „20%“.

Сега увеличете ширината на колоните за по -добро визуално възприятие.

1. Отвори Форматиране на серия от данни панел със задачи (щракнете с десния бутон върху една от сериите данни и изберете Форматиране на серия от данни) и превключете към Опции за серия раздел.
2. Промяна “Ширина на пролуката" да се "20%.”

Стъпка #6: Регулирайте диапазоните на вертикалните оси.

Отново скалата на оста трябва да бъде променена, за да се увеличи по -отблизо данните. За да повторите бързо процеса за тези, които са пропуснали първия раздел на този урок, ето как го правите:

1. Щракнете с десния бутон върху вертикалната ос и изберете „Форматиране на оста.”
2. В прозореца със задачи отворете Опции на оста раздел.
3. Под „Граници, „Промяна“Минимум" да се "50,000" и "Максимум" да се "110,000.”

Стъпка #7: Добавете и позиционирайте персонализираните етикети с данни.

Тези персонализирани етикети за данни чакат твърде дълго, така че нека ги използваме най-накрая. Щракнете с десния бутон върху която и да е колона и изберете „Добавяне на етикети на данни.”

Незабавно етикетите с данни по подразбиране, обвързани с помощните стойности, ще бъдат добавени към диаграмата:

Но това не е точно това, което търсим. За да заобиколите проблема, ръчно заменете етикетите по подразбиране с персонализираните стойности, които сте подготвили предварително.

1. Щракнете двукратно върху етикета с данни, който искате да промените.
2. Въведете „=" в Лента с формули.
3. Маркирайте съответната клетка в колона Етикети.

Заменете останалите етикети с данни, като повторите същия процес за всяка плаваща колона.

Веднъж там, ще трябва да плъзнете етикетите до съответните им позиции: всички положителни стойности трябва да бъдат поставени над съответните зелени колони, докато отрицателните трябва да бъдат преместени под червените колони.

След като успешно се справите с етикетите, вашата диаграма Mario трябва да се трансформира в нещо подобно:

Стъпка #8: Почистете областта на диаграмата.

И накрая, премахнете легендата на диаграмата и линиите на мрежата, които не носят нищо на масата (Кликнете с десния бутон > Изтрий). Променете заглавието на диаграмата и там имате вашата диаграма с водопада!