Сводные таблицы в excel

Группировка с шагом

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

1. Создаем сводную таблицу, где в строках будут дни, а в значениях – продажи. Если Вы не знаете, как создать сводную таблицу – сначала прочтите предыдущий пост;
2. Кликните правой кнопкой мыши по любой из дат в сводной таблице и в контекстном меню выберите «Группировать». Откроется окно настройки группировки;

1. В полях «Начиная с» и «по» автоматически установятся минимальная и максимальная даты в списке. Можете, при необходимости, указать здесь более узкий период для группировки
2. В списке «С шагом» выберите эталонный интервал времени. У нас это «Месяцы». Можно выбрать сразу несколько пунктов в этом списке. Давайте попробуем построить по кварталам и месяцам, отмечаем их;
3. Жмем «Ок» и сразу же получаем результат. Взгляните, что получилось:

Таким же образом можно группировать обычные числовые данные. Например, мы хотим сгруппировать ежедневные продажи с шагом 1000 и узнать, в каком из интервалов было больше всего чеков. Делаем так:

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

1. Кликнем правой кнопкой в любо строке первого столбца (суммы продаж) и выберем «Группировать». В открывшемся окне задаем минимальное и максимальное числа для группировки, а так же, шаг. У нас это 1000. Вместо огромной таблицы, получаем компактную, из десяти строк. В каждой строке – интервал сумм и количество чеков в этом интервале.

1. Кликнем правой кнопкой по любой ячейке в столбце «Количество чеков» и выберем Сортировка – по убыванию;
2. Можно для наглядности выразить результат в процентах. Кликнем по той же ячейке и выберем Дополнительные вычисления — % от суммы по столбцу. Вот и все, задача решена, нам прекрасно видно в каких интервалах сумм было больше всего чеков.

Конечно, эта задача упрощенная, он отличается от тех, которые будете решать Вы. Но главное – понять механизм работы, тогда сможете применить его в своих расчетах.

Так же, можно группировать записи вручную. Для этого выделите нужные данные и нажмите Работа со сводными таблицами – Анализ – Группировать – Группировка по выделенному.

Кстати, чтобы отменить группировку – кликните правой кнопкой мыши по группированному столбцу и выберите «Разгруппировать».

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

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

А выглядит это вот так:

Вы видите несколько окон на рабочем листе с перечисленными в них данными, а так же, сводную таблицу, содержащую полный набор данных. Но что если кому-то нужно посмотреть продажи метизов у Романа 1 апреля 2016 года? Кликаем в окнах на кнопки:

1. В окне «Дата» ищем и выбираем 01.04.2016;
2. В окне «Менеджер» выбираем «Роман»;
3. В окне «Группа товара» кликаем «Метизы»

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

По-моему, отлично! Так вот, чтобы включить срезы в сводных таблицах – выделите любую ячейку этой таблицы и выполните на ленте Работа со сводными таблицами – Анализ – Фильтр – Вставить срез. На экране появится окошко, где нужно галочками отметить те поля сводной таблицы, по которым можно будет делать срезы. Каждому полю будет соответствовать свое окошко со списком. Выбирайте, жмите Ок и все, заработало!

Аналогично работает временная шкала. Этот инструмент очень похож на срезы, но управляет полями, в которых содержатся даты. Чтобы добавить временную шкалу – нажмите Работа со сводными таблицами – Анализ – Фильтр – Вставить временную шкалу. После простых настроек появится окно фильтрации дат, которое позволяет эффективно и быстро ограничивать периоды дат, выводимые в отчет.

Как обновлять данные в сводной таблице

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

13

Также аналогичную операцию можно провернуть через вкладку «Данные» на ленте. Для этого достаточно нажать на кнопку «Обновить все», выделенную красным прямоугольником на скриншоте.

14

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

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет – на всю Таблицу=Отчет – только на данные (без строки заголовка)=Отчет – только на первую строку заголовков=Отчет – на итоги=Отчет – на всю текущую строку (где вводится формула)=Отчет – на весь столбец «Продажи»=Отчет – на ячейку из текущей строки столбца «Продажи»

Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

=СУММ(D2:D8)

то она автоматически переделается в

=Отчет

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

Это значит, что диаграмма или сводная таблица, где в качестве источника указана Таблица Excel, автоматически будет подтягивать новые записи. 

А теперь о том, как Таблицы облегчают жизнь и работу.

Но не все так просто…

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

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

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

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

​Фактически — это суммирование​​ Просто на вкладке​​Если нужно обновить все​какого товара и на​ листа Листа1 первого​ Переносить все 20​ содержащихся в сводной​ выделяем первую ячейку​ в большинстве случаев,​ открывшемся окне переводим​ изменялась и сама​ может изменить на​ нескольких таблиц в​ списка диапазонов первый​​» укажите (через запятую)​ г., а затем​ Чтобы суммирование происходило​ всех ячеек B3​ макет выбираем «Не​ отчеты в книге​ какую сумму;​ файла, что-то типа​ файлов в один,​ таблице. Менять исходную​ столбца, который копируем.​ функционала первого варианта​ переключатель в позицию​​ таблица в левой​ любое удобное ему.​ одну. Подробнее, смотрите​ диапазон. Ставим галочку​ название тех листов,​ добавить данные из​ с учетом заголовков​​ на листах с​ показывать промежуточные суммы»:​​ Excel, выбираем кнопку​

​кто из продавцов постарался;​​ такого:​ но на разных​ информацию не имеет​ Ввод. «Размножаем» формулу,​ вполне достаточно для​ «Ко всем ячейкам».​ части окна.​ Просмотреть или изменить​ в статье «Суммирование​ у цифры «1»​ с которых будет​ таблицы 2012 г.​

​ столбцов и строк​​ 2001 по 2003,​

​Получим отчет без дополнительных​​ «Обновить все» (или​когда (число, месяц).​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=’C:\ВашаПапка\ВашаДругаяПапка\Лист1′!B9​ листах не хотелось​

​ смысла. В таких​​ протягивая вниз за​ выполнения поставленных задач.​​Теперь, наша сводная таблица​

​Получилась вот такая сводная​​ имя таблицы можно​ в Excel» здесь.​ — первое поле​ браться информация для​ и просуммировать?​

​ необходимо включить оба​​ т.е. количество листов,​​ сумм:​ комбинацию клавиш CTRL+ALT+F5).​Если это огромная сеть​Формулу копируете в​ бы, т.к. данные​​ ситуациях лучше добавить​ правый нижний угол​​ Сводные таблицы могут​ приобрела презентабельный вид.​ таблица. Над таблицей​​ во вкладке «Конструктор».​Можно сделать таблицу​ страницы сводной таблицы​ сводной.​Если нет, то​ флажка​​ по сути, может​​Огромные сводные таблицы, которые​Настройка автоматического обновления при​

​ магазинов и продажи​​ другие ячейки листа​ заполняют разные люди​ вычисляемое (пользовательское) поле.​

​ ячейки.​​ формировать данные в​Создать сводную таблицу можно,​ отображаются фильтры по​Для того, чтобы непосредственно​​ Excel с фильтрами​ станет активным. Здесь​Действительно имел ввиду​ какие есть варианты?​Использовать в качестве имен​ быть любым. Также​ составляются на основании​ изменении данных:​ идут, то в​ File1. Аналогично настраиваете​

​ на разных компьютерах.​​Это виртуальный столбец, создаваемый​По такому же принципу​ отчеты по практически​​ применив Мастер сводных​ полу и дате.​​ начать создание сводной​ из простого списка.​ пишем название параметра​ Excel 2013. А​​Заранее благодарю​​(Use labels)​ в будущем возможно​ «чужих» таблиц, периодически​На вкладке «Работа со​​ течение одного квартала​​ остальные листы -​​ Пример таблицы во​​ в результате вычислений.​ переносим другие данные.​ любым критериям, которые​ таблиц. Но, для​Но, как мы помним,​​ таблицы, переходим во​ Читайте в статье​ выбранного диапазона. В​

CyberForum.ru>

​ что смущает?​

• Excel сравнение двух таблиц из разных файлов
• Как в excel создать сводную таблицу
• Excel несколько файлов объединить в один
• Как в excel построить сводную таблицу
• Как сделать гистограмму в excel по данным таблицы
• Что такое сводные таблицы в excel примеры
• Как в excel сделать сводную таблицу
• Excel объединение нескольких таблиц в одну
• Как сделать доступ к файлу excel для нескольких пользователей
• Как в excel в ячейке сделать несколько строк
• Как в excel работать со сводными таблицами
• Excel обновить сводную таблицу в excel

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

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

Из примера видно, что сводная таблица представляет древовидную структуру, если используется более 1 поля. Корнем являются значения столбца, который в списке области «Названия строк» идет первым. Все последующие поля вкладываются в него и в друг друга, согласно своей очередности в списке, изменить которую можно простым перетаскиванием мыши. Каждую отдельную ветвь подобного дерева можно сворачивать и раскрывать. Данное свойство так же применимо к области названий столбцов.По умолчанию эксель задает сводным таблицам макет в сжатом виде. Его можно изменить через параметры (клик правой кнопкой мыши по области таблицы -> параметры сводной таблицы -> Вывод -> Классический макет) либо через конструктор:

Применение макета табличной формы позволяет расположить каждое поле в отдельном столбце и дополнительно вывести по нему промежуточные итоги.Если подводить дополнительно итог не требуется, то его нужно удалить, чтобы облегчить чтение таблицы. Достаточно правого клика мыши по нему и в списке снять галочку с соответствующего пункта. Для избавления от всех итогов кроме основных, на вкладке конструктор в разделе макет выберите «Промежуточные итоги» -> «Не показывать промежуточные суммы».

Так как сводная таблица представляет древовидную структуру, то название строки отображается только один раз. В Microsoft Excel, начиная с версии 2010, можно дополнительно применить к макету повторение подписей элементов.

Теперь законченная сводная таблица выглядит так на листе Excel:

Помимо рассмотренных свойств через параметры таблицы можно установить:

1. Имя сводной таблицы;
2. Объединение и выравнивание подписей;
3. Вывод значений для пустых ячеек;
4. Автоматическое изменение ширины столбцов;
5. Отображение общих итогов по строкам и столбцам;
6. Сортировку;
7. Печать;
8. Обновление и др.

Теперь Вы умеете пользоваться сводными таблицами Excel. Полученные здесь знания позволят Вам далее самостоятельно экспериментировать с ними и повышать свой навык.

Вперёд >

Новые статьи:

• Критерий Манна-Уитни

• Подключение MySQL в Excel

• Подключение Excel к SQL Server

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Заполнение

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

Таблица может содержать любое количество данных. Отмечать желаемые пункты следует в графе «Поля для добавления».

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

В рассматриваемой конструкции можно делать различные вычисления. Для этого в графе «Значения» необходимо выбрать один из вариантов и кликнуть по строке «Параметры полей».

В появившемся окне можно выбрать желаемый тип операции.

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

Точное или приближенное совпадение в функции ВПР

И, наконец, давайте рассмотрим поподробнее последний аргумент, который указывается для функции ВПР – range_lookup (интервальный_просмотр). Как уже упоминалось в начале урока, этот аргумент очень важен. Вы можете получить абсолютно разные результаты в одной и той же формуле при его значении TRUE (ПРАВДА) или FALSE (ЛОЖЬ).

Для начала давайте выясним, что в Microsoft Excel понимается под точным и приближенным совпадением.

• Если аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ), формула ищет точное совпадение, т.е. точно такое же значение, что задано в аргументе lookup_value (искомое_значение). Если в первом столбце диапазона table_array (таблица) встречается два или более значений, совпадающих с аргументом lookup_value (искомое_значение), то выбрано будет первое из них. Если совпадения не найдены, функция сообщит об ошибке #N/A (#Н/Д).Например, следующая формула сообщит об ошибке #N/A (#Н/Д), если в диапазоне A2:A15 нет значения 4:

• Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА), формула ищет приблизительное совпадение. Точнее, сначала функция ВПР ищет точное совпадение, а если такое не найдено, выбирает приблизительное. Приблизительное совпадение – это наибольшее значение, не превышающее заданного в аргументе lookup_value (искомое_значение).

Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, то значения в первом столбце диапазона должны быть отсортированы по возрастанию, то есть от меньшего к большему. Иначе функция ВПР может вернуть ошибочный результат.

Чтобы лучше понять важность выбора TRUE (ИСТИНА) или FALSE (ЛОЖЬ), давайте разберём ещё несколько формул с функцией ВПР и посмотрим на результаты

Пример 1: Поиск точного совпадения при помощи ВПР

Как Вы помните, для поиска точного совпадения, четвёртый аргумент функции ВПР должен иметь значение FALSE (ЛОЖЬ).

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

Обратите внимание, что наш диапазон поиска (столбец A) содержит два значения 50 – в ячейках A5 и A6. Формула возвращает значение из ячейки B5

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

Пример 2: Используем ВПР для поиска приблизительного совпадения

Когда Вы используете функцию ВПР для поиска приблизительного совпадения, т.е. когда аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или пропущен, первое, что Вы должны сделать, – выполнить сортировку диапазона по первому столбцу в порядке возрастания.

Это очень важно, поскольку функция ВПР возвращает следующее наибольшее значение после заданного, а затем поиск останавливается. Если Вы пренебрежете правильной сортировкой, дело закончится тем, что Вы получите очень странные результаты или сообщение об ошибке #N/A (#Н/Д)

Вот теперь можно использовать одну из следующих формул:

Как видите, я хочу выяснить, у какого из животных скорость ближе всего к 69 милям в час. И вот какой результат мне вернула функция ВПР:

Как видите, формула возвратила результат Антилопа (Antelope), скорость которой 61 миля в час, хотя в списке есть также Гепард (Cheetah), который бежит со скоростью 70 миль в час, а 70 ближе к 69, чем 61, не так ли? Почему так происходит? Потому что функция ВПР при поиске приблизительного совпадения возвращает наибольшее значение, не превышающее искомое.

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

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

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

Нажмите здесь, чтобы загрузить файл примера и делать все вместе со мной:

razlichnye-znacheniya-v-svodnyh-tablitsahСкачать

С этим набором данных вам нужно найти ответ на следующие вопросы:

• Сколько сотрудников в каждом регионе (а это не что иное, как количество разных сотрудников в каждом регионе)?
• Сколько сотрудников продали принтер в 2019 году?

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

В этой статье рассматриваются следующие методы:

• Добавление вспомогательного столбца в исходный набор данных для подсчета разных значений (работает во всех версиях).
• Добавление данных в модель данных и использование параметра «Число различных элементов» (доступно в Excel 2013 и последующих версиях).

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

Давайте начнем!

Применение умных таблиц

Давайте вернемся к исходной таблице с данными, встанем в любую ячейки таблицы и в панели вкладок перейдем на Главная -> Стили -> Форматировать как таблицу:

Среди стилей оформления выбираем любой понравившийся (внешний вид на функциональности не влияет), далее выбираем диапазон для таблицы (по умолчанию Excel автоматически его определяет) и нажимаем OK:

Полученная умная таблица обладает большим спектром удобных свойств, но в данном случае нам интересны 2:

• Умная таблица получает имя (в примере выше Таблица1, его можно легко поменять), которое можно использовать для определения диапазона в качестве источника для сводной таблицы;
• Умная таблица автоматически изменяет размер при добавлении или удалении новых строк или столбцов.

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

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

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

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

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

Итак, запускаем Excel… и загружаем такой список…

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

Выделите любую ячейку из этого списка:

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

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

• Какие данные использовать для создания новой сводной таблицы?
• Куда поместить сводную таблицу?

Так как на предыдущем шаге мы уже выбрали одну из ячеек списка, то для создания сводной таблицы будет выделен весь список автоматически. Заметьте, что мы можем выбрать другой диапазон, другую таблицу и даже какой-нибудь внешний источник данных, например, таблицу базы данных Access или MS-SQL. К тому же нам необходимо выбрать, где разместить новую сводную таблицу: на новом листе или на одном из существующих. В данном примере мы выберем вариант – New Worksheet (На новый лист):

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

Как только мы кликнем по любой ячейке в сводной таблице, появится ещё одно диалоговое окно: PivotTable Field List (Поля сводной таблицы).

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

Область Values (Значения), вероятно, самая важная из четырёх. То, какой заголовок помещён в эту область, определяет, по каким данным будут подводиться итоги (сумма, среднее, максимум, минимум и т.д.) Это, почти всегда, численные значения. Отличный кандидат на место в этой области – данные под заголовком Amount (Стоимость) нашей исходной таблицы. Перетащим этот заголовок в область Values (Значения):

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

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

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

Обратимся к исходным данным и попробуем определить один или несколько столбцов, которые можно использовать, чтобы раздробить эту сумму. Например, мы можем сформировать нашу сводную таблицу таким образом, чтобы итоговая сумма продаж подсчитывалась для каждого продавца по отдельности. Т.е. в нашу сводную таблицу добавятся строки с именем каждого продавца компании и его итоговой суммой продаж. Чтобы достичь такого результата, достаточно перетащить заголовок Salesperson (Торговый представитель) в область Row Labels (Строки):

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

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

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

Сортировка значений

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

Для этого нужно сделать следующее.

1. Кликните на треугольник около нужного поля.
2. В результате этого вы увидите следующее меню. Здесь вы можете выбрать нужный вариант сортировки («от А до Я» или «от Я до А»).

Если стандартного варианта недостаточно, вы можете в этом же меню кликнуть на пункт «Дополнительные параметры сортировки».

В результате этого вы увидите следующее окно. Для более детальной настройки нужно нажать на кнопку «Дополнительно».

Здесь всё настроено в автоматическом режиме. Если вы уберете эту галочку, то сможете указать необходимый вам ключ.

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

​ изменении данных, делаем​​ помощь.​ с диапазоном таблицы​ с помощью простого​ области:​ конкретном примере.​Когда нажимаем на​ на лист с​сводные из нескольких​ 2003.​Для того, чтобы выполнить​ или дата. Мы​ Поэкспериментируем и установим​ «в списке или​ нет то вносить​Можно так же​ и сумме.​ по инструкции:​

​ базе данных Microsoft​​ новую запись и​​ воспользоваться макросами, коих​​Щелкаем правой кнопкой мыши​​Курсор стоит в любом​

​ отчет на основе​​ при желании можно​Теперь, таблица имеет совсем​​Названия столбцов;​ таблицу в динамическую.​ появляется дополнительная закладка​​ на кнопку «Мастер​Busine2012​: Скажите, а как​Заранее откройте исходные файлы​Устанавливаем фильтр по значению.​​ Результат – сводная​​ Excel».​​ тоже считать их​​ навалом в инете,​ по любой дате.​​ месте отчета. Работа​ данных в нескольких​ изменить, но нам​ другой вид. Столбцы​Значения;​ Это нужно для​ «Работа со сводными​ сводных таблиц и​

​: ошиблись или действительно​​ это сделать в​

​Создайте новую пустую книгу​​ Исключим из отчета​ таблица вида:​На втором шаге определяем​ массу.​ по сбору инфы​​ Выбираем команду «Группировать».​​ со сводными таблицами​ диапазонах консолидации.​ этого делать не​ делятся по полам,​Фильтр отчёта.​ того, чтобы в​ таблицами». В ней​ диаграмм». В появившемся​ имели ввиду Excel​ Excel 2010? Хорошо​​ (Ctrl + N)​ информацию по односпальным​Если фамилия продавцов для​ диапазон данных, на​В общем должен​ с разных файлов​В открывшемся диалоге задаем​ – Параметры –​Гораздо сложнее сделать сводную​ нужно. Просто жмем​ в строках появилась​Просто, перетаскиваем мышкой нужные​ случае добавления строк​ два раздела. С​ диалоговом окне указываем​ 2013?​​ бы еще так,​​Установите в нее активную​ кроватям – уберем​ анализа деятельности сети​ основании которых будет​ получиться результат, как​

​ в один, но​​ параметры группировки. Начальная​ Сводная таблица.​​ таблицу на основе​ на кнопку «Далее».​ разбивка по месяцам,​ нам поля таблицы​ и других данных,​ их помощью можно​ «в нескольких диапазонах​Katerina2130​ чтоб красиво.​​ ячейку и выберите​ флажок напротив названия​ магазинов не важна,​ строиться отчет. Так​ на вкладке Средняя​ в идеале, конечно,​ и конечная дата​​ разных по структуре​Затем, Мастер сводных таблиц​ а фильтровать таблицу​ в соответствующие нашим​ они автоматически подтягивались​ изменять все подписи​​ консолидации». Указываем –​: Если вам не​Serge_007​ на вкладке (в​ товара.​ можем сформировать отчет​ как у нас​ масса профилей (2)​ нужно отказаться от​ диапазона выводятся автоматически.​В открывшемся диалоге –​ исходных таблиц. Например,​​ предлагает выбрать место,​​ теперь можно по​ потребностям области. Тут​ в сводную таблицу.​

​ фильтров, параметры таблицы.​​ «сводная таблица».​ сложно, ответьте, пожалуйста,​:​ меню)​Жмем ОК – сводная​​ с поквартальной прибылью.​ стоит курсор в​AlexM​ разбиения исходных данных​ Выбираем шаг –​ Данные – Обновить​ таких:​​ где будет размещаться​ категории персонала.​ не существует какого-либо​ Для этого, становимся​Например, нажав на кнопку​Нажимаем «Далее».​ что именно вы​Katerina2130​​Данные — Консолидация​ таблица меняется.​Чтобы убрать результаты группировки,​ таблице, диапазон обозначится​: Вам надо СОЗДАТЬ​ на разные файлы/листы​ «Годы».​ при открытии файла​Первая таблица – приход​ новая таблица на​Если же в списке​ четкого установленного правила,​ курсором на любую​ «Заголовки полей», можно​​На втором шаге​ подкорректировали (какие места)?​: Добрый день!​(Data — Consolidate)​

​Имеем несколько однотипных таблиц​​ необходимо щелкнуть по​ автоматически.​ ТЕМУ под ваш​ и хранить их​

excelworld.ru>

Таблицы Excel

Лучше всего данные хранить в таблице Excel, это самый удобный и распространенный источник для Power Query. На ленте даже есть специальная кнопка.

Чтобы загрузить таблицу в редактор Power Query, достаточно выделить любую ее ячейку и нажать Данные → Получить и преобразовать данные → Из таблицы/диапазона.

Примечание. В вашей версии Excel расположение кнопок и их названия могут отличаться.

Если то же самое проделать с обычным диапазоном, то Excel вначале преобразует диапазон в таблицу Excel, а потом запустит Power Query.

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

Данные находятся в Power Query. Новые значения, внесенные в исходную таблицу, автоматически попадут в запрос после его обновления. Далее в редакторе Power Query делают обработку данных и выгружают либо в виде таблицы Excel, либо оставляют в памяти Excel в виде подключения.