Построение динамично меняющейся диаграммы на основе сводной таблицы.
В работе с программой «Excel» часто сталкиваюсь с тем, что для качественной аналитики больших объемов данных недостаточно обычного статичного графика или диаграммы, требуется использовать динамичный изменяющийся график с фильтрами и отборами информации.
Такой график или диаграмму можно реализовать в программе «Excel» версии 2010 и выше при помощи сводных таблиц.
Умные Таблицы Excel – секреты эффективной работы.
Для графика с двумя и большим количеством рядов данных стоит оставить «легенду» с подписями. Она позволит разобраться, каким цветом, оттенком или типом линии отмечен нужный показатель. Однако название диаграммы в этом случае придется вносить вручную — или указать в настройках, в какой ячейке таблицы расположен нужный текст.
| Город | Страна | Артикул | Товар | Категория | Наличие на складе |
| Самара | Россия | 2333 | Икра лососевая | Пища | Есть |
| Харьков | Украина | 443 | Мышь оптоволоконная | Комплектующие | Нет |
| Ереван | Армения | 255 | Кран вентильный | Комплектующие | В пути |
| Ижевск | Россия | 455 | Ведро эмалерованное | Тара | Есть |
| Кострома | Россия | 23345 | Сливки (флакон) | Пища | Нет |
| Коломна | Россия | 444 | ПО Free Excel (1 шт) | Комплектующие | В пути |
| Алма -Аты | Казахстан | 3345 | Коробка картонная | Тара | Есть |
| Таганрог | Россия | 556 | Пакет 3*4 (100шт.) | Тара | Нет |
| Самара | Россия | 235456 | Мышь оптоволоконная | Комплектующие | В пути |
| Харьков | Украина | 3455 | Кран вентильный | Комплектующие | Есть |
| Ереван | Армения | 344 | Сетка 20 метров | Комплектующие | Нет |
| Ижевск | Россия | 3444 | Сливки (флакон) | Пища | В пути |
| Кострома | Россия | 789 | ПО Free Excel (1 шт) | Комплектующие | Есть |
| Коломна | Россия | 456 | Яблоки, кг. | Пища | Нет |
| Алма -Аты | Казахстан | 56456 | Яблоки красные (1 ящик) | Пища | В пути |
| Таганрог | Россия | 678 | Сельдь | Пища | Есть |
| Самара | Россия | 3456 | Слива (1 уп.) | Пища | Нет |
| Харьков | Украина | 36 | Алыча(1 шт.) | Пища | В пути |
| Ереван | Армения | 345 | Кран вентильный | Комплектующие | Есть |
| Ижевск | Россия | 568 | Таз | Тара | Нет |
| Кострома | Россия | 788 | Батончик шоколад | Пища | В пути |
| Коломна | Россия | 345 | ПО Free Excel (1 шт) | Комплектующие | Есть |
| Алма -Аты | Казахстан | 345 | Диван раскладной | Мебель | Нет |
| Таганрог | Россия | 777 | Кресло | Мебель | В пути |
Обработка динамических рядов и прогноз динамики вMs Excel.
ВпрограммеMS Excel имеется целый ряд возможностей углубленной статистической обработки динамических рядов.
В качестве примера создадим в Excel таблицу «Динамика случаев заболеваний с временной утратой трудоспособности» (Рисунок 113).
После запуска Excel введите в ячейку A2 слово: Год и нажмите клавишу [Enter]. В ячейки A3-A9 введите года: 1985..1991.
Чтобы завершить создание таблицы запишите в ячейки B1-G2 названия столбцов, а также введите в ячейки B3-B9 числовые данные.(Рисунок 113)
Рисунок 113. Таблица динамики заболеваемости с временной утратой способности (ЗВУТ)
Заполните пустые столбцы таблицы. В графу «Абсолютный прирост» занесите разность между последующим и предыдущим уровнями. Для этого введите в ячейку С4 формулу: =В4-В3
В графу «Темп роста» заносится отношение (в %) каждого последующего уровня к предыдущему. Для этого введите в ячейку D4 формулу: =В4/В3*100
Заполните графу «Абс.значение 1% прироста». Для этого введите в ячейку F4 формулу: =B3/100
В графу «Показатель наглядности» заносится отношение (в %) каждого уровня к исходному уровню на 1985 г. Для этого введите в ячейку G3 формулу: = В4/$B$3*100. Знак $ включается в формулу, что бы адрес ячейки В3 не изменялся, как обычно при копировании.
Скопируйте формулы из ячеек C3:G3 в ячейки C4:G9 с помощью команды:
Как уже отмечалось, математическое выражение закономерностей динамики данных можно получить с помощью функции экспоненциального сглаживания.
Например: Необходимо провести сглаживание числового ряда, отражающего динамику травматизма за 1982-1993 гг.
Предварительно в таблице MS Excel необходимо разместить исходные данные. (Рисунок 114).
Затем строим линейный график с помощью функции «Добавить диаграмму. На диаграмме, для удобства счета, по оси Х вместо значения года выставляем номер года (1,2,3 и т.д.). Для повышения изобразительности диаграммы по вертикальной оси в качестве точки отсчета выбираем не 0, а 70.
Щелкнув правой кнопкой мыши по линии графика вызываем всплывающее меню, из которого выбираем функцию Добавить линию тренда.
Затем последовательно в открывшейся закладке выбираем Тип линии тренда Линейная (Рисунок 117). Затем, переключив ярлык закладки на Параметры, устанавливаем параметры Прогноз вперед на 1 период, устанавливаем флажки: Показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации R2 (Рисунок 118).
Рисунок 119 Диаграмма выравнивания динамического ряда с помощью линейной функции Y=-0,7867х +90,864.
С помощью этого коэффициента можно подбирать функцию наиболее полно аппроксимирующую ту или иную тенденцию. Например, при анализе этих же исходных данных полином 3 степени дает более полную аппроксимацию, соответственно, R 2 будет равен 0,56.
С помощью рассмотренной функции мастера диаграмм можно производить прогнозирование значений Y: в данном случае, когда задавался период прогноза на 1 единицу, на экране изображение диаграммы приводится с отображением точки прогноза (13 по оси Х).
Как построить график в Excel по данным таблицы: пошаговая инструкция |
Роман, я выбрал ось дат в качестве примера. В качестве оси Х может использоваться и время, и числа, и деньги… То есть, трюк не связан с каким-то конкретным типом данных. Если проблемы у Вас остались, пришлите в личку файл Excel с описанием, чего хотите добиться. Попробую помочь.
