Як створити діаграму водоспаду в Excel

Цей підручник демонструє, як створити діаграму з водоспадом у всіх версіях Excel: 2007, 2010, 2013, 2016 та 2022 роки.

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

Зазвичай перший і останній стовпці представляють початкові та кінцеві значення, розділені проміжними коригуваннями, які зображують послідовність кроків, необхідних для проходження від точки А до точки В.

Цей тип діаграми широко використовується професіоналами у всіх основних галузях для проведення кількісного аналізу-від розшифровки фінансової звітності до моніторингу товарних запасів.

Містові графіки були представлені як вбудований тип діаграми лише в Excel 2016, що означає, що користувачі попередніх версій Excel не мають іншого вибору, крім як створити діаграму з нуля.

З цієї причини в цьому підручнику ви дізнаєтесь, як створити цю діаграму Маріо як вручну з нуля, так і звернувшись до рідного типу діаграми, який виконує всю брудну роботу за вас.

Починаємо

Прямо за воротами, нам потрібно кинути деякі необроблені дані в картинку. Отже, для ілюстрації припустимо, що ви збираєтесь аналізувати щоквартальний чистий грошовий потік якоїсь фіктивної компанії.

Враховуючи все це, врахуйте такі дані:

Щоб допомогти вам налаштувати діаграму під власні потреби, поговоримо про таблицю докладніше:

• Період: Цей стовпець представляє послідовність позитивних і негативних значень, які мають бути нанесені на діаграму.
• Чистий грошовий потік: Цей стовпець складається з відповідних значень. Перший (В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, ця таблиця може здатися абсолютно лякаючою, але насправді кожен може впоратися з цим завданням, дотримуючись детальних інструкцій, викладених нижче.

Почнемо з додавання трьох допоміжних стовпців з назвою “Невидимий” (стовпець В), “Збільшувати” (стовпець С) і "Зменшити” (стовпець 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. Перейдіть до Заповнити & Лінія вкладка Заповнити у старих версіях Excel.
2. Під "Заповнити,"Виберіть"Без заповнення.”
3. Під "Кордон,"Натисніть"Жодного рядка.”

Крок №4: Налаштуйте колірну схему.

У той же самий Форматувати серію даних панелі завдань, перефарбуйте кожну серію даних, виконавши ідентичні кроки, показані вище (Опції серії > Заповнити > Тверда заливка > Колір), так що це можна зрозуміти інтуїтивно з першого погляду:

1. За Серія "Зменшення" підбирати червоний.
2. За Серія "Збільшення" підбирати зелений.
3. Крім того, двічі клацніть на стовпцях "Баланс перенесення" та "Поточний баланс" і перефарбувати їх синій.

Ось що ви повинні закінчити в цей момент:

Крок 5: Змініть ширину зазору на "20%".

Тепер збільште ширину стовпців для кращого візуального сприйняття.

1. Відкрийте файл Форматувати серію даних панель завдань (клацніть правою кнопкою миші один із рядів даних і виберіть Форматувати серію даних) і перейдіть до Опції серії вкладка.
2. Змінити "Ширина зазору"До"20%.”

Крок #6: Налаштуйте діапазони вертикальних осей.

Знову ж таки, масштаб осі слід змінити, щоб більше наблизити дані. Щоб швидко повторити процес для тих, хто пропустив перший розділ цього підручника, ось як це зробити:

1. Клацніть правою кнопкою миші по вертикальній осі і виберіть "Формат осі.”
2. На панелі завдань відкрийте файл Параметри осі вкладка.
3. Під "Границі, "Змінити"Мінімум"До"50,000”Та“Максимум"До"110,000.”

Крок #7: Додайте та розташуйте спеціальні мітки даних.

Ці спеціальні мітки даних чекали занадто довго, тому давайте нарешті використаємо їх. Клацніть правою кнопкою миші будь-який стовпець і виберіть "Додати мітки даних.”

Відразу до діаграми будуть додані мітки даних за замовчуванням, прив'язані до допоміжних значень:

Але це не зовсім те, що ми шукаємо. Щоб вирішити цю проблему, вручну замініть мітки за замовчуванням на спеціальні значення, які ви підготували заздалегідь.

1. Двічі клацніть ярлик даних, який потрібно змінити.
2. Введіть “=" в Панель формул.
3. Виділіть відповідну клітинку в стовпці Мітки.

Замініть решту міток даних, повторивши той самий процес для кожного плаваючого стовпця.

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

Після того, як ви успішно впоралися з мітками, ваша діаграма Маріо повинна перетворитися на щось на зразок цього:

Крок #8: Очистіть область діаграми.

Нарешті, видаліть легенду діаграми та лінії сітки, які нічого не приносять у таблицю (Клацніть правою кнопкою миші > Видалити). Змініть назву діаграми, і ось ваша діаграма водоспадів!