Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно

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

Кто-то вам сказал, что это нечто, сравнимое по сложности понимания с Общей теорией относительности А.Эйнштейна, и, что лучше, наверное, держаться от этого подальше, чтобы не подрывать свою «Великую веру в себя».

Вам наплевать на Excel вообще и на сводные таблицы в частности, но завтра у вас собеседование на хорошую позицию в хорошем месте и надо произвести неизгладимое впечатление своей осведомленностью по поводу этой сакральной возможности, которая почему-то так люба работодателям.

Вы лишь начинаете осваивать Excel и только-только созрели для этого нового чудного мира, который откроет перед вами знание сводных таблиц. Я так и знал, что это именно ваш пункт!

Наша задача

Учебная ситуация

Журнал продаж цветов

Скачать

5 шагов

Внимательно вглядитесь в журнал учёта продаж цветов.

3 из них содержат текстовую информацию: точка, имя продавца, тип цветов

1 столбец содержит числа — стоимость проданных цветов

Обратите внимание, что один из столбцов содержит числовую информацию, которую можно суммировать. Теперь следите за руками:

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

Шаг 1 . Скроем столбцы A:D , оставим только столбец E — Стоимость . В ячейку E1002 введём формулу суммы, а строки 2:1001 скроем. Получится вот это:

Таким образом мы узнали? что за весь год, всеми точками, всеми продавцами, всех видов цветов продано на 1063100 рублей. Мы пока не говорим ни о каких сводных таблицах, а просто пытаемся анализировать обычную таблицу.

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

Шаг 3 . А кто у нас передовик капиталистического труда? А вот кто:

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

Шаг 4 . А какие цветы приносят больше денег? Посмотрим:

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

Шаг 5 . А в каком месяце максимальные продажи? Странная картина, ну да ладно.

Утомительно, не правда ли? И заметьте, что действия ТИПОВЫЕ !
Так вот, для того, чтобы НИКОГДА БОЛЬШЕ не заниматься этими пятью позорными шагами, существуют сводные таблицы.

Измерения

Создаём сводную таблицу

Давайте создадим сводную таблицу, благо дело это крайне простое:

Встаньте на любую ячейку нашей таблицы на листе Журнал_продаж

На ленте ВСТАВКА нажмите крайнюю левую кнопку Сводная таблица

Нажмите в возникшем диалоговом окне OK . В этом диалоге можно изменить диапазон нашей таблицы, который Excel отлично определяет самостоятельно, а также можно указать, куда вставлять сводную таблицу — рекомендую выбирать всегда отдельный новый лист, что и соответствует настройке по умолчанию.

Момент истины

А теперь я при помощи сводной таблицы за 10 секунд повторю наши 5 шагов, на которые мы потратили минут 10.

Теперь по порядку.

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

Итак, что мы видим на главном экране управления сводной таблицой:

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

Секция Поля сводной таблицы — содержит не что иное, как заголовки столбцов нашей исходной таблицы. Поле или измерение — суть синонимы. Термин «поле» пришёл к нам из баз данных. В сводных таблицах операции с полями означают действия со всеми строками данного столбца, из чего, собственно, и проистекает вся мощь этого инструмента.

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

Раздел СТРОКИ — сюда перетаскивают поля, сгруппированные значения которых должны располагаться по вертикали. Пример:

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

Раздел КОЛОННЫ — сюда перетаскивают поля, значения которых должны располагаться горизонтально. Пример:

Раздел ЗНАЧЕНИЯ — сюда перетаскивают поля, которые можно суммировать. Суммировать можно только числовые поля, а вот подсчитать количество строк можно по любому полю, поэтому, если вы в данный раздел перетащите какое-либо поле измерения (например, Продавец ), то Excel автоматически поймёт, что его надо подсчитывать (count), а не суммировать (sum).

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

Раздел ФИЛЬТРЫ — сюда перетаскиваем поля, по которым не надо группировать значения столбцов и суммировать, но надо отфильтровать строки, чтобы в дальнейшей группировке и суммировании участвовали только строки, удовлетворяющие условию на значение фильтра. Посмотрите на примере:

Раз пошла такая пьянка.

А что это мы используем только одно измерение? Как насчёт двух?

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

А чего это у нас таблица такая блёклая? Добавим красок и ещё усложним:

Не хотите попробовать сделать такое формулами? А сводными таблицами это делается за 5 секунд!

А тут мы находим среднюю стоимость букета по продавщицам. Схема объясняет некоторые нюансы настройки.

Как в Сводной Таблице Excel Текстовые Данные Наиболее Быстро и Корректно • Момент истины

Выводы

Сводные таблицы — самый мощный и востребованный инструмент в Excel.

Анализировать данные со сводными таблицами в десятки раз быстрее и проще, чем без оных.

Миф о сложности освоения этого инструмента — всего лишь миф.

Для первого раза и дальнейших самостоятельных экспериментов вы узнали достаточно! Не поленитесь — закрепите полученные знания на практике.

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

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
До версии Excel 2013 включительно этот инструмент был в виде надстройки, которую можно было установить бесплатно с сайта Microsoft. Если же вы хотите что-то уточнить, я с радостью помогу!
Вы можете использовать эту технику для любых действий — практически везде, где вы хотите увидеть, как одно значение сравнивается с другим. В этой статье мы собираемся использовать простой пример расчета и отображения процента, на который ежемесячно изменяется общая стоимость продаж.

Как рассчитать процентное изменение с помощью сводных таблиц в Excel.

  1. Выведите на экран панель инструментов Сводные таблицы.
  2. Установите указатель мыши на любую ячейку сводной таблицы.
  3. Щелкните на кнопке Обновить данные панели инструментов Сводные таблицы.
  4. Чтобы установить обновление сводной таблицы при каждом открытии книги, откройте диалоговое окно Параметры сводной таблицы (см. рис.9.7). Для этого щелкните правой кнопкой мыши на любой ячейке уже созданной сводной таблицы и в появившемся контекстном меню выберите команду Параметры таблицы. Затем в группе Источник установите флажок обновить при открытии.

Чтобы сделать все это, мы сначала отформатируем наш диапазон значений в виде таблицы в Excel, а затем мы собираемся создать сводную таблицу, чтобы сделать и отобразить наши вычисления процентного изменения.

Microsoft Excel 2003
Строка — поле, которое в отчете сводной таблице ориентировано как строка, обеспечивают группирование строк списка для вычисления итогов Элементы, связанные с полем строки, отображаются как подписи строк.
Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Столбец поле, которое в отчете сводной таблице ориентировано как столбец, элементы, связанные с полем столбца, отображаются как подписи столбцов, обеспечивают группирование и столбцов списка для вычисления итогов, может содержать несколько полей списка;. Если же вы хотите что-то уточнить, я с радостью помогу!
Иногда сводная таблица — не лучший вариант визуализации данных. В таком случае можно создавать отчеты Power View. Как и Power Pivot, Power View — это надстройка Excel, которая по умолчанию включена в версии Pro Plus и выше (начиная с версии 2010).
иконка 3

Как создать сводную таблицу в excel 2010

Аннотация: Цель работы: научиться создавать и применять сводные таблицы при работе с данными. Содержание работы: Мастер сводных таблиц. Построение макета сводной таблицы. Работа со сводной таблицей. Порядок выполнения работы: Изучить методические указания. Выполнить задания. Оформить отчет и ответить на контрольные вопросы.

Введение в сводные таблицы - Сводные таблицы - Excel - Каталог статей - Perfect Excel
Независимо от того, как сгруппированы данные в исходных диапазонах, вы мо­жете самостоятельно создать группы элементов полей в сводной таблице. Процедура группировки данных зависит от типа данных: даты, числа, текст.
Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Раздел ФИЛЬТРЫ — сюда перетаскиваем поля, по которым не надо группировать значения столбцов и суммировать, но надо отфильтровать строки, чтобы в дальнейшей группировке и суммировании участвовали только строки, удовлетворяющие условию на значение фильтра. Если же вы хотите что-то уточнить, я с радостью помогу!
  • Общая сумма по столбцам и общая сумма по строкам, чтобы в сводной таблице были представ­лены итоги расчетов. Обычно эти флажки установлены по умолчанию. Если в подведении итогов нет необходимости, просто снимите соответствующий фла­жок.
  • Автоформат, чтобы к сводной таблице был применен используемый по умолчанию автоформат.

НОУ ИНТУИТ | Лекция | Сводные таблицы

  • использование списка (базы данных Excel );
  • использование внешнего источника данных;
  • использование нескольких диапазонов консолидации;
  • использование данных из другой сводной таблицы.

Страница – поле, которому в отчете сводной таблице или сводной диаграммы назначена страничная ориентация, обеспечивает фильтрацию данных сводной таблицы по значениям выбранных полей, может содержать несколько полей списка; поле страницы

Таблица 7.1. Виды дополнительных функций в полях области данных

Функция Результат
Отличие Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках, поле и элемент
Доля Значения ячеек области данных отображаются в процентах к заданному элементу, указанному в списках поле и элементам.
Приведенное отличие Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в стеках поле и элемент, нормированной к значению этого элемента
С нарастающим итогом в поле Значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Следует выбрать поле, элементы которого будут отображаться в нарастающем итоге
Доля от суммы по строке Значения ячеек области данных отображаются в Процентах от итога строки
Доля от суммы по столбцу Значения ячеек области данных отображаются в Процентах от итога столбца
Доля от общей суммы Значения ячеек области данных отображаются в процентах от общего итога сводной таблицы
Индекс При определении значений ячеек области данных используется следующий алгоритм: ((Значение в ячейке) * (Общий итог))/((Итог строки) *(Итог столбца)
Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Сводные таблицы отличный инструмент и один из самых простых способов отобразить процентное изменение значений с течением времени. Если же вы хотите что-то уточнить, я с радостью помогу!
Другие опции для редактирования отчетов доступны из меню «Работа со сводными таблицами» на вкладках «Параметры» и «Конструктор». Почти каждый из инструментов этих вкладок имеет массу настроек и дополнительных функций.

10 наиболее полезных функций при анализе данных в Excel — ExcelGuide: Про Excel и не только

Чтобы сделать все это, мы сначала отформатируем наш диапазон значений в виде таблицы в Excel, а затем мы собираемся создать сводную таблицу, чтобы сделать и отобразить наши вычисления процентного изменения.

Обработка больших массивов данных в Excel | LABA (ЛАБА)
В полях для ошибок отображать и для пустых ячеек отобра­жать введите значения, которые будут отображаться в ячейках в случае ошибки или отсутствия данных. Эти поля станут доступными только при наличии соответствующие флажков.
Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Выводит все данные в области дан ных при помощи того же метода, что и функция Отличие, но отражает разность как процент от базовых данных. Если же вы хотите что-то уточнить, я с радостью помогу!
— Логическое выражение — выражение, которое по итогу своего вычисления должно вырнуться значение ИСТИНА или ЛОЖЬ.
— Значение, если истина — устанавливаем указанное значение, если логическое выражение вернуло ИСТИНА
— Значение, если ложь — устанавливает указанное значение, если логическое выражение вернуло ЛОЖЬ.

ГПР

  • выявления взаимосвязей в большом наборе данных;
  • группировки данных по различным признакам и отслеживания тенденции изменений в группах;
  • нахождения повторяющихся элементов, детализации и т.п.;
  • создания удобных для чтения отчетов, что является самым главным.

Перетащите с помощью мыши кнопки с названиями столбцов (полей данных) из правой части диалогового окна в нужные области. Макет сводной таблицы, создаваемой на основе данных нашего примера, представлен на рис. 9.5.

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Вы можете использовать эту технику для любых действий практически везде, где вы хотите увидеть, как одно значение сравнивается с другим. Если же вы хотите что-то уточнить, я с радостью помогу!
Часто данные, которые мы получаем из внешних источников, имеют текстовый формат и мы не можем производить с ними математических действий (складывать, вычитать и т.п.). Нам требуется сначала преобразовать текст в число, для этого используйте функцию ЗНАЧЕН. Синтаксис у функции следующий:

СЦЕПИТЬ

Сводная таблица (или свод) – это таблица итогов различных видов, составленная по данным из нескольких таблиц. В качестве таблиц-источников могут выступать списки, другие сводные таблицы, базы данных внешние и внутренние, отдельные блоки данных электронной таблицы Excel и др. Сводная таблица обеспечивает различные способы агрегирования информации.

Скачать
Чтобы переместить поле данных, поместите указатель мыши на любую кнопку поля и перетащите ее в новую область. Таким образом, можно преобразовать поле строки в поле столбца и наоборот. Можно также создать поле страницы, перетащив кнопку соответствующего поля в область над макетом сводной таблицы.
Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Если предварительно курсор установлен в список, для которого строится сводная таблица, то интервал ячеек будет указан автоматически. Если же вы хотите что-то уточнить, я с радостью помогу!
Независимо от того, как сгруппированы данные в исходных диапазонах, вы мо­жете самостоятельно создать группы элементов полей в сводной таблице. Процедура группировки данных зависит от типа данных: даты, числа, текст.

Изменение функции вычисления общих итогов

В правой половине окна создается панель основных инструментов управления — «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет». Отметьте необходимые пункты и отчет сводной таблицы с выбранными полями будет создан.

Оставить отзыв

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