Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины
Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно
Целевая аудитория данной статьи — граждане, которые плохо ли, хорошо ли, но работают в Excel, однако, боятся замахнуться на Вильяма нашего Шекспира сводные таблицы. Причин такой ситуации может быть несколько:
Кто-то вам сказал, что это нечто, сравнимое по сложности понимания с Общей теорией относительности А.Эйнштейна, и, что лучше, наверное, держаться от этого подальше, чтобы не подрывать свою «Великую веру в себя».
Вам наплевать на Excel вообще и на сводные таблицы в частности, но завтра у вас собеседование на хорошую позицию в хорошем месте и надо произвести неизгладимое впечатление своей осведомленностью по поводу этой сакральной возможности, которая почему-то так люба работодателям.
Вы лишь начинаете осваивать Excel и только-только созрели для этого нового чудного мира, который откроет перед вами знание сводных таблиц. Я так и знал, что это именно ваш пункт!
Наша задача
Учебная ситуация
Журнал продаж цветов
Скачать
5 шагов
Внимательно вглядитесь в журнал учёта продаж цветов.
3 из них содержат текстовую информацию: точка, имя продавца, тип цветов
1 столбец содержит числа — стоимость проданных цветов
Обратите внимание, что один из столбцов содержит числовую информацию, которую можно суммировать. Теперь следите за руками:
Шаг 1 . Скроем столбцы A:D , оставим только столбец E — Стоимость . В ячейку E1002 введём формулу суммы, а строки 2:1001 скроем. Получится вот это:
Таким образом мы узнали? что за весь год, всеми точками, всеми продавцами, всех видов цветов продано на 1063100 рублей. Мы пока не говорим ни о каких сводных таблицах, а просто пытаемся анализировать обычную таблицу.
Шаг 3 . А кто у нас передовик капиталистического труда? А вот кто:
Шаг 4 . А какие цветы приносят больше денег? Посмотрим:
Шаг 5 . А в каком месяце максимальные продажи? Странная картина, ну да ладно.
Утомительно, не правда ли? И заметьте, что действия ТИПОВЫЕ !
Так вот, для того, чтобы НИКОГДА БОЛЬШЕ не заниматься этими пятью позорными шагами, существуют сводные таблицы.
Измерения
Создаём сводную таблицу
Давайте создадим сводную таблицу, благо дело это крайне простое:
Встаньте на любую ячейку нашей таблицы на листе Журнал_продаж
На ленте ВСТАВКА нажмите крайнюю левую кнопку Сводная таблица
Нажмите в возникшем диалоговом окне OK . В этом диалоге можно изменить диапазон нашей таблицы, который Excel отлично определяет самостоятельно, а также можно указать, куда вставлять сводную таблицу — рекомендую выбирать всегда отдельный новый лист, что и соответствует настройке по умолчанию.
Момент истины
А теперь я при помощи сводной таблицы за 10 секунд повторю наши 5 шагов, на которые мы потратили минут 10.
Теперь по порядку.
Итак, что мы видим на главном экране управления сводной таблицой:
Секция Поля сводной таблицы — содержит не что иное, как заголовки столбцов нашей исходной таблицы. Поле или измерение — суть синонимы. Термин «поле» пришёл к нам из баз данных. В сводных таблицах операции с полями означают действия со всеми строками данного столбца, из чего, собственно, и проистекает вся мощь этого инструмента.
Раздел СТРОКИ — сюда перетаскивают поля, сгруппированные значения которых должны располагаться по вертикали. Пример:
Раздел КОЛОННЫ — сюда перетаскивают поля, значения которых должны располагаться горизонтально. Пример:
Раздел ЗНАЧЕНИЯ — сюда перетаскивают поля, которые можно суммировать. Суммировать можно только числовые поля, а вот подсчитать количество строк можно по любому полю, поэтому, если вы в данный раздел перетащите какое-либо поле измерения (например, Продавец ), то Excel автоматически поймёт, что его надо подсчитывать (count), а не суммировать (sum).
Раздел ФИЛЬТРЫ — сюда перетаскиваем поля, по которым не надо группировать значения столбцов и суммировать, но надо отфильтровать строки, чтобы в дальнейшей группировке и суммировании участвовали только строки, удовлетворяющие условию на значение фильтра. Посмотрите на примере:
Раз пошла такая пьянка.
А что это мы используем только одно измерение? Как насчёт двух?
А чего это у нас таблица такая блёклая? Добавим красок и ещё усложним:
Не хотите попробовать сделать такое формулами? А сводными таблицами это делается за 5 секунд!
А тут мы находим среднюю стоимость букета по продавщицам. Схема объясняет некоторые нюансы настройки.
Выводы
Сводные таблицы — самый мощный и востребованный инструмент в Excel.
Анализировать данные со сводными таблицами в десятки раз быстрее и проще, чем без оных.
Миф о сложности освоения этого инструмента — всего лишь миф.
Для первого раза и дальнейших самостоятельных экспериментов вы узнали достаточно! Не поленитесь — закрепите полученные знания на практике.
Дальнейшие нюансы работы со сводными таблицами будут обсуждаться в других статьях, которые будут опираться на эту. До встречи!
Как рассчитать процентное изменение с помощью сводных таблиц в Excel.
- Выведите на экран панель инструментов Сводные таблицы.
- Установите указатель мыши на любую ячейку сводной таблицы.
- Щелкните на кнопке Обновить данные панели инструментов Сводные таблицы.
- Чтобы установить обновление сводной таблицы при каждом открытии книги, откройте диалоговое окно Параметры сводной таблицы (см. рис.9.7). Для этого щелкните правой кнопкой мыши на любой ячейке уже созданной сводной таблицы и в появившемся контекстном меню выберите команду Параметры таблицы. Затем в группе Источник установите флажок обновить при открытии.
Чтобы сделать все это, мы сначала отформатируем наш диапазон значений в виде таблицы в Excel, а затем мы собираемся создать сводную таблицу, чтобы сделать и отобразить наши вычисления процентного изменения.
Как создать сводную таблицу в excel 2010
Аннотация: Цель работы: научиться создавать и применять сводные таблицы при работе с данными. Содержание работы: Мастер сводных таблиц. Построение макета сводной таблицы. Работа со сводной таблицей. Порядок выполнения работы: Изучить методические указания. Выполнить задания. Оформить отчет и ответить на контрольные вопросы.
- Общая сумма по столбцам и общая сумма по строкам, чтобы в сводной таблице были представлены итоги расчетов. Обычно эти флажки установлены по умолчанию. Если в подведении итогов нет необходимости, просто снимите соответствующий флажок.
- Автоформат, чтобы к сводной таблице был применен используемый по умолчанию автоформат.
НОУ ИНТУИТ | Лекция | Сводные таблицы
- использование списка (базы данных Excel );
- использование внешнего источника данных;
- использование нескольких диапазонов консолидации;
- использование данных из другой сводной таблицы.
Страница – поле, которому в отчете сводной таблице или сводной диаграммы назначена страничная ориентация, обеспечивает фильтрацию данных сводной таблицы по значениям выбранных полей, может содержать несколько полей списка; поле страницы
Функция | Результат |
---|---|
Отличие | Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках, поле и элемент |
Доля | Значения ячеек области данных отображаются в процентах к заданному элементу, указанному в списках поле и элементам. |
Приведенное отличие | Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в стеках поле и элемент, нормированной к значению этого элемента |
С нарастающим итогом в поле | Значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Следует выбрать поле, элементы которого будут отображаться в нарастающем итоге |
Доля от суммы по строке | Значения ячеек области данных отображаются в Процентах от итога строки |
Доля от суммы по столбцу | Значения ячеек области данных отображаются в Процентах от итога столбца |
Доля от общей суммы | Значения ячеек области данных отображаются в процентах от общего итога сводной таблицы |
Индекс | При определении значений ячеек области данных используется следующий алгоритм: ((Значение в ячейке) * (Общий итог))/((Итог строки) *(Итог столбца) |
10 наиболее полезных функций при анализе данных в Excel — ExcelGuide: Про Excel и не только
Чтобы сделать все это, мы сначала отформатируем наш диапазон значений в виде таблицы в Excel, а затем мы собираемся создать сводную таблицу, чтобы сделать и отобразить наши вычисления процентного изменения.
— Значение, если истина — устанавливаем указанное значение, если логическое выражение вернуло ИСТИНА
— Значение, если ложь — устанавливает указанное значение, если логическое выражение вернуло ЛОЖЬ.
ГПР
- выявления взаимосвязей в большом наборе данных;
- группировки данных по различным признакам и отслеживания тенденции изменений в группах;
- нахождения повторяющихся элементов, детализации и т.п.;
- создания удобных для чтения отчетов, что является самым главным.
Перетащите с помощью мыши кнопки с названиями столбцов (полей данных) из правой части диалогового окна в нужные области. Макет сводной таблицы, создаваемой на основе данных нашего примера, представлен на рис. 9.5.
СЦЕПИТЬ
Сводная таблица (или свод) – это таблица итогов различных видов, составленная по данным из нескольких таблиц. В качестве таблиц-источников могут выступать списки, другие сводные таблицы, базы данных внешние и внутренние, отдельные блоки данных электронной таблицы Excel и др. Сводная таблица обеспечивает различные способы агрегирования информации.
Изменение функции вычисления общих итогов
В правой половине окна создается панель основных инструментов управления — «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет». Отметьте необходимые пункты и отчет сводной таблицы с выбранными полями будет создан.
Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.