Как сделать динамику в excel по годам

Корпорация "Центр"

Динамическая диаграмма в Excel

Динамическая диаграмма в Excel

Добрый день, уважаемые читатели! Сегодня мы рассмотрим вопрос, который поступил от одного из читателей блога — как построить динамическую диаграмму (график)? То есть, чтобы график сам перестраивался в зависимости от выбранных условий и без удаления данных.

Как говорится — хороший вопрос! Приступим.

Для начала построим таблицу с любыми данными, динамику которых нужно отслеживать.

Далее создадим выпадающий список выбора (магазинов). Для этого перейдём на вкладку "Данные", в блоке кнопок "Работа с данными" нажмём кнопку "Проверка данных", выберем тип "Список", а затем укажем диапазон (источник) $A$2:$A$5 (в моём случае).

Подробнее о том как строить выпадающие списки смотрим ЗДЕСЬ.

Получим вот такую картину.

Теперь нам нужен график (диаграмма) пока только по одному магазину. Пусть это будет Ручеек.

Выделяем ячейки с A1:I2 поскольку пока нам будет нужен только он, переходим на вкладку "Вставка", в блоке кнопок "Диаграммы" жмём по треугольнику после кнопки "График" и выбираем "График с маркерами и накоплением" (для большей наглядности). Получим наш график. Как строить диаграммы смотрим ЗДЕСЬ.

динамическая диаграмма в Excel

И вот теперь мы немного отойдём от привычного построения диаграмм. Для построения динамической диаграммы в Excel нам придётся создать новую переменную — именованный диапазон. Переходим на вкладку "Формулы", в блоке кнопок "Определённые имена" нажмём кнопку "Диспетчер имён".

динамическая диаграмма в Excel

Перед нами появится следующее окно.

динамическая диаграмма в Excel

Нажимаем кнопку "Создать", задаём имя для нашего диапазона (я задам _chart), поле "Область" оставим "Книга", если что-то хочется написать в поле "Примечание" — смело пишем. Мы подобрались к самому интересному — полю "Диапазон". Сюда мы напишем следующую формулу:

Поясню что есть что. Функция СМЕЩ (смещение) будет обновлять наши данные по магазинам (так как мы построили график только для магазина Ручеек).

Далее в скобках будут показаны пределы данных времени (месяцы) (у мня это от ячейки B1 до ячейки I1). Их обязательно нужно жёстко закрепить (символами $) иначе будем получать неверную информацию.

Функция ПОИСКПОЗ поможет нам найти выбранный в списке магазин, т.е. если я выбираю в ячейке L1 другой магазин формула будет искать в диапазоне от A2 до A5 точное совпадение названия.

Подробнее о функции ПОИСКПОЗ — ВИДЕО С НАШЕГО КАНАЛА.

динамическая диаграмма в Excel

Нажимаем "ОК", затем мы увидим, что в списке диспетчера имён появился наш диапазон _chart.

динамическая диаграмма в Excel

Нажимаем "Закрыть" и возвращаемся к нашему графику. По нему щёлкаем правой кнопкой мышки и берём пункт "Выбрать данные".

Корпорация "Центр"

динамическая диаграмма в Excel

Где находится поле с названием нашего ряда (Ручеек) кликаем кнопку "Изменить". Имя ряда мы менять не будем (там будут меняться наши магазины), а вот в значениях напишем =Лист2!_chart (можно вообще написать в кавычках имя файла, так как поле области мы оставляли Книга и после восклицательного знака написать имя нашего диапазона).

динамическая диаграмма в Excel

Нажимаем ОК и проверяем — выбираем из списка другие магазины и смотрим за изменениями графика!

Пишите комментарии если что-то было непонятно!

Диаграмма темпов роста в Excel

диаграммы, excel, красивые, интересные

Как в Excel создать диаграмму с динамикой темпов роста, где изменения показателей показаны стрелками?
Все просто — рисуем столбцы и добавляем к ним полосы повышения и понижения. Плюс рисуем графики с накоплением — первый для роста, второй график — уровень подписи.


1. Исходные данные

Предположим, нам нужно показать динамику темпов роста выручки:

Добавим в таблицу строку «подписи» — с суммой немного больше исходного значения. И строку «рост», где будет рассчитан прирост выручки к предыдущему периоду. В первой колонке проставляем #Н/Д для того, чтобы не значения этого столбца не выводились в диаграмме.


2. Вставляем гистограмму с накоплением

Выделяем таблицу с выручкой и новыми строками. Добавляем гистограмму с накоплением: меню Вставка → Гистограмма → Гистограмма с накоплением.


3. Рост и подписи превращаем в график с накоплением

Выделяем гистограмму мышкой, переходим в меню Конструктор → Изменить тип диаграммы → выбираем вид диаграммы Комбинированная, для данных «подписи» и «рост» выбираем тип диаграммы — график с накоплением. Благодаря этому график с малыми значениями «наложится» на график с большими значениями.


4. Добавляем подписи линии роста

Добавляем подписи для линии роста: выделяем на диаграмме линию роста, в меню переходим на вкладку Конструктор → Добавить элемент диаграммы → Метки данных → выбираем Слева.

Делаем линию роста на диаграмме невидимой: выделяем линию правой кнопкой мышки, нажимаем Формат ряда данных, назначаем тип линии = Нет линий.


5. Добавляем линии ряда данных, удаляем легенду

Выделяем столбцы гистограммы, переходим в меню Конструктор → Добавить элемент диаграммы → Линии → Линии ряда данных (если такая линия не появилась, проверьте, правильный ли у вас тип диаграммы — должна быть Гистограмма с накоплением).
Удаляем легенду.


6. Задаем тип стрелки

Задаем тип стрелки — выделяем линию правой кнопкой мышки → Формат линий ряда → задаем тип стрелки.
Готово! Подписи и эффекты добавлять по вкусу.

Корпорация "Центр"

Ссылка на основную публикацию