Умные таблицы excel

Управление списком полей таблицы

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

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

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

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

Месяц Дата Кол-во Курс Изменение
Январь 10.01.2013 1 30,4215 0,0488
Январь 11.01.2013 1 30,3650 -0,0565
Январь 12.01.2013 1 30,2537 -0,1113
Сентябрь 28.09.2013 1 32,3451 0,1715

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

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

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

Таблица из нескольких листов

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

Работа выполняется в несколько этапов:

  1. Необходимо создать новый файл, позже именно в нем появятся значения сводной таблицы.
  2. Перейти в шестую вкладку на верхней панели, где проходит анализ и сборка данных. Там появится вкладка с бесплатной надстройкой. Потребуется создать запрос (как показано на картинке), чтобы указать там файл с таблицами.
  3. Откроется окно, где программа предложит выбрать один из листов. Какой именно, значения не имеет. Необходимо нажать на любой файл, а потом нажать на кнопку для его изменения.
  4. После этого откроется окно редактора, пользователю придется изменить в его правой части параметры запроса. В Power Query обычно сразу прописываются все шаги, необходимо удалить все, кроме изначального — источника.
  5. Выбрать листы с данными, где находится информация. Там откроется общий список, поэтому из него стоит выделить только подходящие значения. Легче всего найти их через фильтр, который находится сверху.
  6. Убрать все столбцы кроме Data. Для этого достаточно кликнуть правой кнопкой мыши на строчке и нажать на соответствующую кнопку.
  7. Щелкнуть на стрелочки в разные стороны, чтобы посмотреть содержимое.
  8. После этого появится содержимое всех таблиц, на этом этапе необходимо проверить, правильно ли указана информация.
  9. Поднять первую строку в шапке и использовать ее в качестве заголовка. Эта функция доступна на вкладке с главными настройками. Повторяющиеся значения необходимо убрать через фильтр.
  10. Для сохранения информации пользователю нужно нажать кнопку для закрытия и загрузки, а в появившемся окне поставить галочку напротив строки для создания подключения. После этого остается только настроить сводную таблицу.
  11. Кликнуть в верхней части программы на вставку и перейти в раздел с таблицами. Там нажать на функцию для использования внешнего источника, дальше выбрать подключение и сформировавшийся отчет. Потом пользователь точно также перетаскивает свободные поля, как при создании новой таблицы.

1. Срезы

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

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

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

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

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

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

Разные значения против уникальных значений

Кажется, что это одно и то же, но это не так.

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

Разница между уникальным и разными значениями

Уникальные значения / имена — это те, которые встречаются только один раз. Это означает, что все имена, которые повторяются и имеют дубликаты, не являются уникальными. Уникальные имена перечислены в столбце D вышеупомянутого набора данных.

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

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

Проверка правильности выставленных коммунальных счетов

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

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

Для примера мы сделали сводную табличку тарифов для Москвы:

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

Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:

= тариф * количество человек / показания счетчика / площадь

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

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

Добавить свой первый срез в Excel

Чтобы начать работу со срезами, начните, кликнув внутри сводной таблицы. На ленте Excel найдите раздел Работа со сводными таблицами и нажмите Параметры.

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

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

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

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

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

Вот так! Вы вставили срез полей. Теперь вы можете выбрать и кликнуть, чтобы отфильтровать ваши данные. Давайте узнаем больше о том, как использовать и настраивать фильтры.

Повторяйте и продолжайте обучение (с ещё бо́льшими уроками по Excel)

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

Эти уроки вам продвинуть ваши навыки с Excel и сводным таблицам на следующий уровень. Проверь их:

  • ExcelZoo имеет большой обзор приёмов сводной таблицы в их статье, 10 уроков для освоения сводных таблиц (на английском).
  • Мы в Envato Tuts+ рассмотрели сводные таблицы с помощью урока для новичков Как создать свою первую сводную таблицу в Microsoft Excel.
  • Для более простого введения в Microsoft Excel ознакомьтесь с нашей учебной серией Как сделать и использовать формулы в Excel (Учебный лагерь для начинающих).

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

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

13

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

14

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

Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010

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

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

Шаг 1.

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

Рисунок 1. Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010

Шаг 2.

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

Рисунок 2. Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010

Шаг 3.

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

Рисунок 3. Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010

Изменение набора полей сводной таблицы

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

  1. Щелкните на произвольно выбранной ячейке сводной таблицы.

Excel добавит на ленту набор контекстных вкладок Работа со сводными таблицами с собственными контекстными вкладками Анализ и Конструктор.

  1. Щелкните на контекстной вкладке Анализ, чтобы отобразить на ленте ее кнопки.
  2. Щелкните на кнопке Список полей, находящейся в группе Показать.

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

После открытия панели списка полей можно выполнить следующие изменения.

Чтобы удалить поле, перетащите его имя из области, в которой оно находится в текущий момент (ФИЛЬТРЫ, СТРОКИ, СТОЛБЦЫ или ЗНАЧЕНИЯ), в любое другое место. Как только указатель мыши примет вид крестика, отпустите кнопку мыши или просто снимите флажок около этого поля в списке полей.

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

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

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

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

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

Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

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

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

На ее построение потребовалось буквально 5-10 секунд.

Добавить данные в модель данных и суммировать, используя «Число различных элементов»

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

В случае, если вы используете предыдущую версию, вы не сможете использовать этот метод (используйте метод, описанный выше).

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

Ниже приведены шаги для получения количества разных сотрудников в сводной таблице:

  • Выберите любую ячейку в таблице.
  • Нажмите вкладку «Вставка».

Нажмите на кнопку Сводная таблица.

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

Нажмите ОК.

Приведенные выше шаги вставят новый лист с новой сводной таблицей.

Перетащите регион в область «Строки» и «Сотрудник» в область «Значения». Вы получите такую сводную таблицу:

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

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

  • Щелкните правой кнопкой мыши по любой ячейке в «Число элементов в столбце Сотрудник»
  • Нажмите на «Параметры полей значений».

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

Нажмите ОК.

Обратите внимание, что название столбца изменится с «Число элементов в столбце Сотрудник» на «Число разных элементов в столбце Сотрудник». Вы можете изменить его

Некоторые вещи, которые нужно знать, добавляя свои данные в модель данных:

  • Если вы сохраните свои данные в модели данных, а затем откроете в более старой версии Excel, появится предупреждение: «Некоторые функции сводной таблицы не будут сохранены».
  • Когда вы добавляете свои данные в модель данных и создаете сводную таблицу, в ней не отображаются параметры добавления вычисляемых полей и вычисляемых столбцов.

Создание таблицы Excel

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

Чтобы превратить диапазон в таблицу, можно воспользоваться двумя способами. Первый – это перейти на вкладку «Вставка», после чего нажать на кнопку «Таблица».

2

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

3

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

Перед тем, как разбираться в особенностях работы с таблицами, необходимо разобраться, как она устроена в Excel.

Пример использования ВПР

Взглянем, как работает функция ВПР на конкретном примере.

У нас имеется две таблицы. Первая из них представляет собой таблицу закупок, в которой размещены наименования продуктов питания. В следующей колонке после наименования расположено значение количества товара, который требуется закупить. Далее следует цена. И в последней колонке – общая стоимость закупки конкретного наименования товара, которая рассчитывается по вбитой уже в ячейку формуле умножения количества на цену. А вот цену нам как раз и придется подтянуть с помощью функции ВПР из соседней таблицы, которая представляет собой прайс-лист.

  1. Кликаем по верхней ячейке (C3) в столбце «Цена» в первой таблице. Затем, жмем на значок «Вставить функцию», который расположен перед строкой формул.

В открывшемся окне мастера функций выбираем категорию «Ссылки и массивы». Затем, из представленного набора функций выбираем «ВПР». Жмем на кнопку «OK».

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

Так как у нас искомое значение для ячейки C3, это «Картофель», то и выделяем соответствующее значение. Возвращаемся к окну аргументов функции.

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

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

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

В следующей графе «Номер столбца» нам нужно указать номер того столбца, откуда будем выводить значения. Этот столбец располагается в выделенной выше области таблицы. Так как таблица состоит из двух столбцов, а столбец с ценами является вторым, то ставим номер «2».
В последней графе «Интервальный просмотр» нам нужно указать значение «0» (ЛОЖЬ) или «1» (ИСТИНА). В первом случае, будут выводиться только точные совпадения, а во втором — наиболее приближенные. Так как наименование продуктов – это текстовые данные, то они не могут быть приближенными, в отличие от числовых данных, поэтому нам нужно поставить значение «0». Далее, жмем на кнопку «OK».

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

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

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

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Как построить сводную таблицу в Excel.

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

Появляется диалоговое окно Создание сводной таблицы.  

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

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

Нажимаем ОК.

Открылся новый лист в правой части которого появился блок настройки Сводной таблицы — Поля сводной таблицы. 

Он содержит в себе следующие элементы:

  • Поля для добавления в отчет. Здесь можно выбрать элементы исходной таблицы (диапазона данных), которые будут отображаться в Сводной таблицы. Чтобы выбрать нужный элемент, напротив него необходимо поставить галочку.
  • Фильтры. Здесь находятся элементы, которые будут фильтровать данные, отображаемые в  Сводной таблицы.
  • Столбцы. Здесь находятся элементы, которые будут отображаться в Сводной таблице в качестве столбцов.
  • Строки. Здесь находятся элементы, которые будут отображаться в Сводной таблице в качестве строк.
  • Значения. Здесь находятся элементы, которые будут отображаться в Сводной таблице в качестве числовых данных.

После того, как выбраны (поставлены галочки)  поля для добавления в отчет, они отобразятся в блоках (полях) Фильтры, Столбцы, Строки и Значения. Далее, их необходимо перетащить в соответствующие поля исходя их того, какую Сводную таблицу необходимо получить в результате.

Пункт Отложить обновление макета, позволяет настраивать обновление Сводной таблицы. Если галочка не стоит, настройки сделанные в блоке Поля сводной таблицы отобразятся в Сводной таблице сразу. Если галочка стоит, то только после того, как нажата кнопка Обновить.

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

Если мы поставим галочку, которая подтверждает выделение сразу нескольких объектов, то сможем обрабатывать данные сразу по нескольким продавцам. Применение фильтра возможно для столбцов и строк. Поставив галочку на одной из разновидностей товара, можно узнать, сколько его реализовано одним или несколькими продавцами. Отдельно настраиваются и параметры поля. На примере мы видим, что определенный продавец Рома в конкретном месяце продал рубашек на конкретную сумму. Нажатием мышки мы в строке «Сумма по полю…» вызываем меню и выбираем «Параметры полей значений». Далее для сведения данных в поле выбираем «Количество». Подтверждаем выбор. Посмотрите на таблицу. По ней четко видно, что в один из месяцев продавец продал рубашки в количестве 2-х штук. Теперь меняем таблицу и делаем так, чтобы фильтр срабатывал по месяцам. Поле «Дата» мы переносим в «Фильтр отчета», а там где «Названия столбцов», будет «Продавец». Таблица  отображает весь период продаж или за конкретный месяц.

Выделение ячеек в сводной таблице приведет к появлению такой вкладки как «Работа со сводными таблицами», а в ней будут еще две вкладки «Параметры» и «Конструктор». На самом деле рассказывать о настройках сводных таблиц можно еще очень долго. Проводите изменения под свой вкус, добиваясь удобного для вас пользования. Не бойтесь нажимать и экспериментировать. Любое действие вы всегда сможете изменить нажатием сочетания клавиш Ctrl+Z.

Надеюсь, вы усвоили весь материал, и теперь знаете, как сделать сводную таблицу в excel.

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

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

Настройки Таблицы

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное – это создание срезов.

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

и срез готов. В нем показаны все уникальные значения выбранного столбца.

Для фильтрации Таблицы следует выбрать интересующую категорию.

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

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.

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

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

Чтобы создать временную шкалу для сводной таблицы, выберите ячейку сводной таблицы и щелкните на кнопке Вставить временную шкалу. Эта кнопка находится в группе Фильтр контекстной вкладки Анализ, относящейся к группе контекстных вкладок Работа со сводными таблицами. На экране появится диалоговое окно Вставка временных шкал, включающее список полей сводной таблицы, на основе которых может создаваться временная шкала. Установите флажок, соответствующий полю типа “дата”, которое будет использовано для создания временных шкал, и щелкните на кнопке ОК.

В результате выполнения соответствующих действий Excel создает “плавающую” временную шкалу Дата, разделенную на годы и месяцы, и полосу, соответствующую выбранному периоду времени. По умолчанию в качестве единиц измерения временной шкалы используются месяцы, хотя можно выбрать годы, кварталы или даже дни. Чтобы изменить единицу измерения времени, щелкните на кнопке раскрывающегося списка МЕСЯЦЫ и выберите требуемую единицу измерения.

С помощью временной шкалы можно выбрать период, для которого отображаются данные сводной таблицы. Как показано на скриншоте, данные сводной таблицы были отфильтрованы таким образом, чтобы отображать расходы и доходы на 1-2 января 2019 года. Чтобы выполнить подобную фильтрацию, перетащите ползунок временной шкалы Дата таким образом, чтобы охватить период от 1 января 2019 года по 2 января 2019 года включительно. Чтобы изменить выбранный ранее период, выберите для него другие начало и конец, перетаскивая ползунок Дата.

Изменение функции итогов

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

Изменение порядка сортировки

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

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

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

После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Adblock
detector