Работа со сводными таблицами в Microsoft Excel

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

При написании данной статьи был использован Excel 2010. Концепция сводных таблиц почти не изменялась долгие годы, но способ их создания немного различен в каждой новой версии Excel. Если у Вас версия Excel не 2010 года, то будьте готовы, что снимки экранов в данной статье будут отличаться от того, что Вы увидите на своём экране.

Немного истории

• Во-первых, компания Lotus решила, что эта новомодная GUI-платформа с названием Windows — это всего лишь мимолётное увлечение, которое долго не протянет. Они отказались создавать версию Lotus 1-2-3 для Windows (впрочем, только несколько лет), предсказывая, что DOS-версия их программного обеспечения — это всё, что когда-либо будет нужно потребителям. Microsoft, естественно, разработала Excel специально под Windows.
• Во-вторых, Microsoft разработала в Excel такой инструмент, как сводные таблицы, которого не было в Lotus 1-2-3. Сводные таблицы, эксклюзивная для Excel вещь, оказалась так ошеломительно полезна, что люди были склонны осваивать новый программный пакет Excel, а не продолжать работать в Lotus 1-2-3, в котором их не было.

Сводные таблицы вместе с недооценкой успеха Windows в целом, сыграли похоронный марш для Lotus 1-2-3 и положили начало успеху Microsoft Excel.

Что такое сводные таблицы?

Итак, как же лучше охарактеризовать, что собой представляют сводные таблицы?

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

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

Если вернуться к исходному списку продаж, то он выглядел бы примерно так:

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

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

Для начала убедитесь, что у Вас есть какие-то исходные данные на листе Excel. Перечень финансовых операций – самое типичное, что встречается. На самом деле, это может быть перечень чего угодно: контактные данные сотрудников, коллекция компакт-дисков или данные о расходе топлива Вашей компании.

После того, как мы открыли этот список в Excel, можем приступить к созданию сводной таблицы.

Затем на вкладке Insert (Вставка) выберите команду PivotTable (Сводная таблица):

Появится диалоговое окно Create PivotTable (Создание сводной таблицы) с двумя вопросами для Вас:

Excel создаст новый лист и разместит на нем пустую сводную таблицу:

Обратите внимание, что заголовок Amount теперь отмечен галочкой, а в области Values (Значения) появилась запись Sum of Amount (Сумма по полю Amount), указывающая на то, что столбец Amount просуммирован.

Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы.

Итак, наша первая сводная таблица создана! Удобно, но не особо впечатляет. Вероятно, мы хотим получить больше информации о наших данных, чем есть сейчас.

Становится интересней! Наша сводная таблица начинает обретать форму…

Видите преимущества? За пару кликов мы создали таблицу, которую вручную пришлось бы создавать очень долго.

Что ещё мы можем сделать? Ну, в определённом смысле, наша сводная таблица уже готова. Мы создали полезную сводку по исходным данным. Уже получена важная информация! В оставшейся части статьи мы разберём некоторые способы создания более сложных сводных таблиц, а также узнаем, как их настраивать.

Настройка сводной таблицы

Во-первых, мы можем создать двумерную сводную таблицу. Сделаем это, используя заголовок столбца Payment Method (Способ оплаты). Просто перетащите заголовок Payment Method в область Column Labels (Колонны):

Теперь сделаем трёхмерную таблицу. Как может выглядеть такая таблица? Давайте посмотрим…

Перетащите заголовок Package (Комплекс) в область Report Filter (Фильтры):

Это даёт нам возможность отфильтровать отчёт по признаку «Какой комплекс отдыха был оплачен». Например, мы можем видеть разбивку по продавцам и по способам оплаты для всех комплексов или за пару щелчков мышью изменить вид сводной таблицы и показать такую же разбивку только для заказавших комплекс Sunseekers.

Итак, если Вы правильно это понимаете, то нашу сводную таблицу можно назвать трёхмерной. Продолжим настраивать…

Если вдруг выясняется, что в сводной таблице должны выводится только оплата чеком и кредитной картой (то есть безналичный расчёт), то мы можем отключить вывод заголовка Cash (Наличными). Для этого рядом с Column Labels нажмите стрелку вниз и в выпадающем меню снимите галочку с пункта Cash:

Давайте посмотрим, на что теперь похожа наша сводная таблица. Как видите, столбец Cash исчез из нее.

Форматирование сводных таблиц в Excel

Очевидно, что сводные таблицы – это очень мощный инструмент, но до сих пор результаты выглядят как-то незамысловато и скучно. Например, цифры, которые мы суммируем, не похожи на суммы в долларах – это просто какие-то цифры. Давайте это исправим.

Нажмите кнопку Number Format (Числовой формат), откроется диалоговое окно Format Cells (Формат ячеек):

Из списка Category (Числовые форматы) выберите Accounting (Финансовый) и число десятичных знаков установите равным нулю. Теперь несколько раз нажмите ОК, чтобы вернуться назад к нашей сводной таблице.

Как видите, числа оказались отформатированы как суммы в долларах.

Раз уж мы занялись форматированием, давайте настроим формат для всей сводной таблицы. Есть несколько способов сделать это. Используем тот, что попроще…

Далее разверните меню нажатием на стрелочку в нижнем правом углу раздела PivotTable Styles (Стили сводной таблицы), чтобы увидеть обширную коллекцию встроенных стилей:

Выберите любой подходящий стиль и посмотрите на результат в своей сводной таблице:

Прочие настройки сводных таблиц в Excel

Иногда приходится фильтровать данные по датам. Например, в нашем списке торговых операций присутствует много-много дат. Excel предоставляет инструмент для группировки данных по дням, месяцам, годам и т.д. Давайте посмотрим, как это делается.

Для начала уберите запись Payment Method из области Column Labels (Колонны). Для этого перетащите его обратно к списку заголовков, а на его место переместите заголовок Date Booked (Дата бронирования):

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

Чтобы исправить это, кликните правой кнопкой мыши по любой дате и выберите из контекстного меню пункт Group (Группировать):

Появится диалоговое окно группировки. Мы выбираем Months (Месяцы) и жмём ОК:

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

Есть еще один очень важный момент, который необходимо знать! Вы можете создать не один, а несколько уровней заголовков строк (или столбцов):

То же самое можно проделать с заголовками столбцов (или даже с фильтрами).

Вернёмся к исходному виду таблицы и посмотрим, как вывести средние значения вместо сумм.

Заодно, пока мы здесь, давайте изменим Custom Name (Пользовательское имя) с Average of Amount (Количество по полю Amount) на что-нибудь покороче. Введите в этом поле что-нибудь вроде Avg:

Нажмите ОК и посмотрите, что получилось. Обратите внимание, все значения изменились с итоговых сумм на средние значения, а заголовок таблицы (в левой верхней ячейке) поменялся на Avg:

Если захотеть, то можно получить сразу сумму, среднее и количество (продаж), размещённые в одной сводной таблице.

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

1. Перетащите заголовок Salesperson (Торговый представитель) в область Column Labels (Колонны).
2. Трижды перетащите заголовок Amount (Стоимость) в область Values (Значения).
3. Для первого поля Amount измените название на Total (Сумма), а формат чисел в этом поле на Accounting (Финансовый). Количество десятичных знаков равно нулю.
4. Второе поле Amount назовите Average, операцию для него установите Average (Среднее) и формат чисел в этом поле тоже измените на Accounting (Финансовый) с числом десятичных знаков равным нулю.
5. Для третьего поля Amount установите название Count и операцию для него – Count (Количество)
6. В области Column Labels (Колонны) автоматически создано поле Σ Values (Σ Значения) – перетащите его в область Row Labels (Строки)

Общая сумма, среднее значение и количество продаж – всё в одной сводной таблице!

Заключение

[expert_bq id=»1570″]Если вносятся изменения в имеющийся диапазон добавляются или удаляются данные , они автоматически отражаются в раскрывающемся списке. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если вы хотите, чтобы при вводе значения, которого нет в списке, появлялось всплывающее сообщение, установите флажок Показывать оповещения, выберите параметр в поле Тип и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.

Создание сводной таблицы Excel из нескольких листов

• Во-первых, компания Lotus решила, что эта новомодная GUI-платформа с названием Windows — это всего лишь мимолётное увлечение, которое долго не протянет. Они отказались создавать версию Lotus 1-2-3 для Windows (впрочем, только несколько лет), предсказывая, что DOS-версия их программного обеспечения — это всё, что когда-либо будет нужно потребителям. Microsoft, естественно, разработала Excel специально под Windows.
• Во-вторых, Microsoft разработала в Excel такой инструмент, как сводные таблицы, которого не было в Lotus 1-2-3. Сводные таблицы, эксклюзивная для Excel вещь, оказалась так ошеломительно полезна, что люди были склонны осваивать новый программный пакет Excel, а не продолжать работать в Lotus 1-2-3, в котором их не было.

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

Сводная таблица с текстовыми полями в области значений

Данная статья содержит информацию о том, как с помощью надстройки Power Pivot в Excel добавлять текстовые поля в область значений сводной таблицы. Это может быть полезно, когда необходимо перечислить сущности, не добавляя их в группировку сводной таблицы. Такими сущностями могут быть номера договоров, перечень ассортимента, различные комментарии и другие характеристики.

ИНСТРУКЦИЯ ПО ДОБАВЛЕНИЮ ТЕКСТОВЫХ ПОЛЕЙ В СВОДНУЮ ТАБЛИЦУ

Чтобы с помощью надстройки Power Pivot добавить текст в сводную таблицу, нужно выполнить последовательно следующие действия:

Шаг 1. Включаем надстройку Power Pivot.

Надстройка Power Pivot входит в стандартный комплект Excel 2013, 2016 и Excel 365 для Windows. Она подключается одной галочкой в окне надстроек:

Файл → Параметры → Надстройки → Надстройки COM → Microsoft Power Pivot.

Шаг 2. Загружаем исходную таблицу Exсel в модель данных Power Pivot и создаем сводную таблицу с подключением к модели. В качестве примера возьмем таблицу (рис. 1), в которой ведутся остатки ассортимента в магазине обуви.

Исходную таблицу с информацией об остатках обуви загружаем в модель данных Power Pivot, как показано на рис. 2.

Выполнив загрузку, выходим из окна Power Pivot, создаем новый лист, через команду «Вставка» строим сводную таблицу.

Важный момент: в случае с Power Pivot для создания сводной таблицы выделять исходную таблицу не нужно.

Шаг 3. Добавим в область строк сводной таблицы следующие поля: Тип обуви, Полное наименование, Размеры, Цвет (рис. 3).

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

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

Чтобы создать меру, нужно на вкладке Power Pivot выбрать «Меры» и «Создать меру» (см. рис. 3).

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

Функция VALUES() создает таблицу из одного столбца с уникальными значениями (в нашем случае это «коричневый», «синий», «черный»).

Функция COUNTROWS() подсчитывает в таблице количество строк.

К мере применяются фильтры сводной таблицы (учитываются ее разрезы).

В группировке Ботильонов и следующего наименования обуви указано 3 — максимальное количество уникальных цветов. Все три цвета повторяются в первом по порядку размере 38, поэтому у данного размера тоже 3. В размере 39 только один цвет, поэтому стоит 1 (рис. 5).

Если функция VALUES() имеет единственное значение, то в значения сводной таблицы его можно добавить текстом, а не только рассчитать количество таких значений, чем ограничивается стандартный Excel.

Предположим, нами создана мера, которая выглядит следующим образом:

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

Чтобы обойти данную ошибку, нужно определить, когда VALUES() возвращает одно или несколько значений. Для этого воспользуемся функцией HASONEVALUE(). Она возвращает 1 (ИСТИНА), если в ней столбец с одним значением, и 0 (ЛОЖЬ), если столбец с несколькими значениями.

Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 10, 2018.

[expert_bq id=»1570″]После этого Excel откроет панель со списком полей сводной таблицы, на которой будет указано, к каким областям они принадлежат в текущий момент. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] На экране появится диалоговое окно Рекомендуемые сводные таблицы, показанное на скриншоте ниже. В левой его части приведены примеры сводных таблиц, которые Excel может создавать на основе данных, выбранных в списке.

Почему в excel не работает выпадающий список

Обратите внимание, что заголовок Amount теперь отмечен галочкой, а в области Values (Значения) появилась запись Sum of Amount (Сумма по полю Amount), указывающая на то, что столбец Amount просуммирован.

Формула получить данные сводной таблицы excel

Функция GetPivotData Возвращает видимые данные из сводной таблицы.

В этом примере = GETPIVOTDATA («продажи»; a3) возвращает общую сумму продаж из сводной таблицы.

Синтаксис

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных; сводная_таблица; [поле1; элем1; поле2; элем2]; …)

Аргументы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ описаны ниже.

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

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

От 1 до 126 пар имен полей и элементов, описывающих данные, которые необходимо извлечь. Они могут следовать друг за другом в произвольном порядке. Имена полей и имена для элементов, отличных от даты и чисел, должны быть заключены в кавычки.

Для сводных таблиц OLAPэлементы могут содержать исходное имя измерения, а также исходное имя элемента. Пара «поле-элемент» для сводной таблицы OLAP может выглядеть следующим образом:

Можно быстро ввести простую формулу ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, введя = (знак равенства) в ячейке, в которой должно быть возвращено значение, и затем щелкнув ячейку в сводной таблице, содержащей необходимые данные.

Вы можете отключить эту функцию, выбрав любую ячейку в существующей сводной таблице, а затем перейдите на вкладку анализ сводной таблицы > Параметры > сводной таблицы > снимите флажок Создать GetPivotData .

Вычисляемые поля, элементы и пользовательские вычисления можно включить в расчеты в GETPIVOTDATA.

Если аргумент pivot_table является диапазоном, содержащим две или несколько сводных таблиц, данные будут извлекаться из какой-либо сводной таблицы, созданной последними.

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

Если аргумент pivot_table не является диапазоном, в котором найдена Сводная таблица, функция GETPIVOTDATA возвращает #REF!.

Если аргументы не описывают видимое поле или содержат фильтр отчета, в котором не отображаются отфильтрованные данные, функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает значение ошибки #ССЫЛКА!. значение ошибки.

Примеры

В формулах в приведенном ниже примере показаны различные методы для считывания данных из сводной таблицы.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Get expert help now

Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel предназначена для получения доступа к полям данных сводных таблиц и возвращает данные в соответствии с запросом (формируется на основе переданных в данную функцию аргументов).

Пример как использовать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel

Пример 1. В таблице Excel содержатся данные о поступлениях бытовой техники различного типа и от разных производителей на склад интернет-магазина по номеру дня. Создать сводную таблицу на основе существующей, получить данные о количестве полученных ноутбуках фирмы Samsung и их общей стоимости с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.

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

Для получения искомых данных можно вручную ввести следующую функцию:

• «Количество» – поле сводной таблицы, данные из которого требуется получить;
• A4:C15 – диапазон ячеек, в которых находится сводная таблица;
• «Наименование»;»Ноутбук»;»Фирма»;»Samsung» – характеристика получаемых данных, на основании которой производится поиск требуемых данных.

Как видно, результат совпадает со значением, хранящимся в сводной таблице. Для получения значения поля «Сумма» воспользуемся более удобным способом получения данных, когда рассматриваемая функция генерируется автоматически. Для этого выделим ячейку G8, вставим символ «=» и выделим ячейку B7:

Данная формула была сгенерирована автоматически. Полученный результат:

С помощью данной формулы мы имеем возможность получить доступ к любым значениям полей сводной таблицы Excel.

Выборка данных из сводной таблицы с помощью формулы Excel

Пример 2. На основании данных из первого примера необходимо определить, насколько количество привезенных на склад телевизоров Samsung превышает количество телевизоров LG, а также разницу их общей стоимости.

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

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

Формула для анализа данных полученных из сводной таблицы Excel

Пример 3. В таблицу Excel выгружены статистические данные сайта в виде таблицы с полями «День», «Логин», «Страна» и «Время активности». Определить среднее время активности на сайте за исследуемый период для пользователей из каждой страны, вычислить наибольшее среди полученных значений.

Создадим сводную таблицу на новом листе и добавим поля для отображения среднего значения времени активности на сайте:

Произведем расчет для пользователя из GB (Великобритания):

Формула выглядит очень громоздкой, однако ее аргументы генерируются автоматически при выборе соответствующих ячеек. Итоговые результаты расчета:

Определим наибольшее значение с помощью соответствующей формулы:

Как видно, на сайте в среднем больше времени проводили пользователи из UA (Украины).

Особенности использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( поле_данных; сводная_таблица ; [поле1; элем1; поле2; элем2]; …)

Описание аргументов (первые два являются обязательными для заполнения):

Предположим, что из вот такой базы данных по продажам:

. вы создали небольшую, но симпатичную сводную таблицу:

Но ваш руководитель хочет не совсем ее, а что-то похожее на:

• Исходный внешний вид сводной таблицы не подходит — дизайн отчета должен соответствовать корпоративным стандартам (цвета, логотипы, спарклайны, стрелки и т.д.). «Дорабатывать напильником» дизайн сводной — долгий и мучительный процесс. И не факт, что красота не слетит после пересчета и обновления.
• Из всей сводной для отчета вам нужны не все данные, а только конкретные модели Ford по Питеру — придется руками фильтровать.
• Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зеленых ячейках, но среднее по месяцу в итогах — сводная так не умеет.
• Полученные в сводной результаты — еще не конец, нам необходимо произвести с ними какие-то дополнительные вычисления: пересчитать выручку в тысячах, добавить прогноз на апрель, сравнить этот год с прошлым. Многое из перечисленного в сводных или невозможно в принципе (особенно для сводных на основе OLAP-кубов) или делается, но «через одно место» с помощью вычисляемых полей и объектов.
• Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).

Изящным решением всех этих проблем может стать функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA) , которая умеет извлекать нужные нам данные из сводной, чтобы использовать их в других таблицах или расчетах.

Сделаем на отдельном от сводной листе заготовку отчета:

Выделите первую ячейку зеленого диапазона, введите знак «равно» и щелкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки а-ля «морской бой» Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:

• Первый ее аргумент («Выручка») — это имя извлекаемого поля.
• Второй (Лист1!$A$4) — это адрес первой ячейки сводной таблицы, откуда мы берем данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать из какой именно нужно вытащить число.
• Все остальные аргументы начиная с третьего — это попарно название поля и его значение, т.е., в нашем случае, это имя модели (Наименование=»Fiesta») и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто «январь».

Аккуратно замените в формуле «Fiesta» на $С7, а единичку на D$5 и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зеленые ячейки.

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

Если исходные данные поменяются, то нужно будет лишь обновить сводную (правой кнопкой мыши — Обновить). Даже если завтра изменится количество моделей или, скажем, Fiesta будет уже не третьей по счету машиной в сводной, наша функция все равно ее найдет и правильно извлечет соответствующий ей результат для нашего отчета.

[expert_bq id=»1570″]Если аргументы поля и элемента описывают одну ячейку, возвращается значение этой ячейки независимо от того, является ли она строкой, числом, ошибкой или пустой ячейкой. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Пример 1. В таблице Excel содержатся данные о поступлениях бытовой техники различного типа и от разных производителей на склад интернет-магазина по номеру дня. Создать сводную таблицу на основе существующей, получить данные о количестве полученных ноутбуках фирмы Samsung и их общей стоимости с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.

Сводная таблица с текстовыми полями в области значений

• «Количество» – поле сводной таблицы, данные из которого требуется получить;
• A4:C15 – диапазон ячеек, в которых находится сводная таблица;
• «Наименование»;»Ноутбук»;»Фирма»;»Samsung» – характеристика получаемых данных, на основании которой производится поиск требуемых данных.

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