Як зробити діаграму «водоспад» в excel.

Діаграми в Excel можна побудувати за різними даними. Можна в діаграмі показати поточні дані, а можна показати тільки різницю даних за періодами. Діаграма водоспад в Excel допоможе відобразити тільки зміна даних за періодами, як позитивних, так і негативних. допоможе провести аналіз в Excel будь-якої діяльності.
Для того, щоб побачити і порівняти зростання і зниження даних на графіку, будуємо діаграму «Водоспад».
Є кілька варіантів, як побудувати діаграму Водоспад.
Як працювати з діаграмою, графіком, де розташовані функції, дивіться в статті «Як зробити діаграму в Excel».
Перший спосіб.
Як зробити діаграму водоспад в Excel, коли всі дані в таблиці позитивні.
Наприклад, нам потрібно подивитися, як рухається прибуток по місяцях. Побудуємо таблицю.
У стовпці A - найменування (товар, прибуток, період, тощо), будь-які дані, зміна за якими хочемо побачити на графіку.
У стовпці C пишемо цифри зростання (прибутку, товару, тощо) У стовпці D пишемо цифри зниження.
У стовпці B пишемо в верхньому осередку число, від якого будемо відштовхуватися. Наприклад, залишок на початок місяця, якщо хочемо побачити різницю по місяцях, року - якщо хочемо подивитися різницю по роках, тощо
В осередку B3 пишемо формулу, яка буде вважати залишок на початок наступного періоду (місяця, року). = В2 + С3-D3 Пояснення формули - залишок на січень плюс прихід за лютий, мінус витрата за лютий, виходить залишок на лютий і на початок березня. Копіюємо формулу по стовпчику. У нас вийшла така таблиця.
Тепер виділяємо всю таблицю з шапкою. На закладці «Вставка» в розділі «Діаграми» натискаємо на кнопку «Гістограма». Вибираємо - «Гістограма з накопиченням».
Вийшов такий графік.
Тепер потрібно змінити кольори стовпців діаграми. Синій колір прибрати, зробити ці стовпці прозорими. Натискаємо на синій стовпчик на діаграмі. Стовпці виділилися. Натискаємо на синій стовпчик правою кнопкою миші, вибираємо - «Формат ряду даних» -gt; "Параметри ряду" -gt; «Заливка» -gt; «Ні заливки».
Як працювати з діаграмою, на яких закладках знайти функції, як змінити колір, ширину стовпця діаграми, тощо, читайте в статті «Як зробити графік в Excel».
Тепер встановимо зелений колір для даних приходу (підвищення) і червоний колір для даних витрат (зниження). Вийшло так.Можна зробити стовпці ширше. Натискаємо правою кнопкою миші на стовпець діаграми і вибираємо з контекстного меню функцію «Формат ряду даних». У розділі «Параметри ряду» зменшуємо «Зазор». Ми поставили 20%.
Вийшло так.
Другий спосіб.
Як зробити діаграму водоспад в Excel, коли дані в таблиці позитивні і негативні.
У нас така таблиця по руху фінансових коштів по місяцях.
Нам потрібно побудувати діаграму так, щоб позитивні дані на діаграмі відклалися вище нульової осі, а негативні числа - нижче нульової осі. Для цього потрібно розрахувати кілька додаткових даних. Формулами це зробити швидко. Вся таблиця вийде така.
У стовпці C порахували дані наростаючим підсумком - до даних попереднього місяця додали дані поточного місяця. Наприклад, до значення за січень додали значення за лютий.
В осередку С4 напишемо таку формулу. = C3 + B4 Вийшло накопичення наростаючим підсумком за лютий. (3 + (-2,5) = 0,5). У лютому відбулося зниження, але підсумкове значення залишилося позитивним. Ці дані відкладуть на діаграмі над нульовою віссю. А в квітні підсумкове значення негативне. Воно відкладеться під нульовою лінією.
Для цих цілей розіб`ємо всі дані по окремим колонкам, щоб діаграма вишикувалася правильно.
Отже, формули такі. Всі формули пишемо в рядку 4, потім копіюємо вниз по стовпчику.
В осередку E4 формула буде така. = ЕСЛИ (И (C4lt; 0-C3lt; 0) -макс (C3: C4) -0)
В осередку F4. = ЕСЛИ (И (B4gt; 0-C4gt; 0) -мінні (B4-C4) -0)
В осередку G4. = ЕСЛИ (И (B4gt; 0-C3lt; 0) -макс (-B4-C3) -0)
В осередку H4. = ЕСЛИ (И (C4gt; 0-C3gt; 0) -мінні (C3: C4) -0)
В осередку I4. = ЕСЛИ (И (B4lt; 0-C4lt; 0) -макс (B4-C4) -0)
В осередку J4. = ЕСЛИ (И (B4lt; 0-C3gt; 0) -мінні (-B4-C3) -0)
У таблиці назву стовпців забарвлене тим же кольором, що і будемо розфарбовувати стовпці в діаграмі. Чи не пофарбовані стовпчики в діаграмі зробимо прозорими.
Будуємо діаграму Водоспад в Excel.
Виділяємо стовпець A з назвою стовпця. Натискаємо кнопку «Ctrl», утримуючи її натиснутою, виділяємо стовпці з D до J. Виходить, ми не виділили тільки два стовпці - В і С.
На закладці «Вставка» в розділі «Діаграми» натискаємо на кнопку «Гістограма». Вибираємо - «Гістограма з накопиченням». Вийшла така діаграма.
Натискаємо на діаграму. На закладці «Конструктор» в розділі «Дані» натискаємо на кнопку функції «Строка / Стовпець». Міняємо місцями в діаграмі рядки і стовпці. Легенду на діаграмі розташували справа. Вийшло так.Тепер залишилося розібратися з квітами стовпців. Натискаємо на стовпець в діаграмі правою кнопкою миші. З контекстного меню вибираємо функцію «Формат ряду даних». Встановлюємо потрібний колір.
Стовпець січня пофарбували в сірий колір - це точка відліку.
Додаткові стовпці - ставимо - «Ні заливки», вони стануть прозорими.
Стовпці приходу офарблюємо в зелений колір. Стовпці витрати офарблюємо в червоний колір. Вийшло так.За цим графіком видно, що тільки в червні вийшли в плюс, хоч зростання було і в травні.
Третій спосіб.
Ще один спосіб побудови діаграми «Водоспад» в Excel за допомогою графіка. У нас така таблиць
В осередку C3 коштує така формула. = D2В осередку D3 коштує така формула. = D2 + B3
Формули копіюємо вниз по стовпцях. Виділяємо стовпці A, C, D (крім шпальти У) за допомогою клавіші «Ctrl» (дивіться другий спосіб) .На закладці «Вставка» натискаємо кнопку функції «Графік». Вийшов такий графік.Тепер потрібно додати смуги підвищення і зниження. В Excel 2013 натискаємо на плюсик праворуч від діаграми. Або на закладці «Конструктор» натискаємо на кнопку функції «Додати елемент діаграми», Вибираємо функцію «Смуги підвищення і пониження». В Excel 2007 на закладці «Макет» в розділі «Аналіз» натискаємо на кнопку «Смуги підвищення / пониження». Вийшло так.Залишилося змінити колір стовпців в діаграмі, прибрати колір у ліній графіка. Вийшло так. В Excel можна виділити повторювані дані кольором, але і словами, числами, знаками, формулою, тощо Дивіться про таких прийомах в статті "Як знайти повторювані значення в Excel".

Відео: Створення проектної діаграми Ганта в Excel