Newcomposers.ru

IT Мир
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Ютуб эксель сводные таблицы

Общие сведения о сводных таблицах в Excel

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

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

Использование сводных таблиц для ответа на вопросы

Предположим, мы хотим ответить на вопрос: “Какова общая сумма продаж каждого из продавцов?” применительно к данным на примере ниже. Поиск ответа на этот вопрос может оказаться затруднительным и занять очень много времени, поскольку данные по каждому продавцу разбросаны по многим строкам. Конечно же, можно воспользоваться командой Промежуточный итог, чтобы подвести итог отдельно по каждому продавцу, однако, нам все еще предстоит работать с большим объемом информации.

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

Создав сводную таблицу, Вы можете использовать её для ответа на различные вопросы с помощью перегруппировки или сведения данных. Например, если необходимо ответить на вопрос: “Какова общая сумма продаж по месяцам?”, мы можем придать нашей сводной таблице следующий вид:

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

  1. Выделите таблицу или ячейки (включая заголовки столбцов), содержащие данные, которые необходимо использовать.
  2. На вкладке Вставка щелкните команду Сводная таблица.
  3. В появившемся диалоговом окне Создание сводной таблицы введите необходимые настройки, а затем нажмите OK. В нашем примере мы воспользуемся Таблицей1 в качестве источника данных и поместим сводную таблицу на новом листе.
  4. На новом листе появится пустая сводная таблица и список полей.
  5. После создания сводной таблицы, Вам потребуется решить какие поля необходимо в нее добавить. Каждое поле является заголовком столбца источника данных. В списке полей сводной таблицы установите флажок для каждого поля, которое необходимо добавить. В нашем примере мы хотим подсчитать общую сумму продаж каждого из продавцов, поэтому установим флажки в полях Продавец и Сумма заказа.
  6. Выбранные поля будут добавлены в одну из четырех областей, которые расположены под списком полей. В нашем примере поле Продавец было добавлено в область Строки, в то время как Сумма заказа – в область Значения. Кроме этого, Вы можете щелкнуть по нужному полю и, удерживая левую кнопку мыши, перетащить его в требуемую область.
  7. Сводная таблица вычислит и подведет итоги для выбранных полей. В нашем примере сводная таблица подсчитала общую сумму продаж каждого из продавцов.

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

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

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

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

Итак, как мы уже писали ранее, для создания сводной таблицы Вам нужна простая базовая таблица. Вы можете создать её сами, или воспользоваться нашим примером, загрузив его по ссылке (простая таблица.xlsx).

Для создания сводной таблицы перейдите на вкладку Вставка, где в группе Таблицы выберите команду Сводная таблица.

Откроется следующее диалоговое окно:

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

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

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

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

Нажав кнопку Ок после настройки нужных нам условий, мы получаем следующий рабочий лист:

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

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

Читать еще:  Копирование роликов с ютуба

а) в верхней части окна настроек отмечаем все названия необходимых нам столбцов:

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

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

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

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

Настройка нашей таблицы должна выглядеть вот так:

Тогда наша сводная таблица будет иметь следующий вид:

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

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

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

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

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

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

В начало страницы

В начало страницы

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

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

Итак, как мы уже писали ранее, для создания сводной таблицы Вам нужна простая базовая таблица. Вы можете создать её сами, или воспользоваться нашим примером, загрузив его по ссылке (простая таблица.xlsx).

Для создания сводной таблицы перейдите на вкладку Вставка, где в группе Таблицы выберите команду Сводная таблица.

Откроется следующее диалоговое окно:

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

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

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

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

Нажав кнопку Ок после настройки нужных нам условий, мы получаем следующий рабочий лист:

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

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

а) в верхней части окна настроек отмечаем все названия необходимых нам столбцов:

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

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

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

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

Читать еще:  Музыка из нарезок ютуба

Настройка нашей таблицы должна выглядеть вот так:

Тогда наша сводная таблица будет иметь следующий вид:

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

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

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

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

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

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

В начало страницы

В начало страницы

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

Исходные данные

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

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

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

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

Рекомендуемые сводные таблицы

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

Выберите любую ячейку исходной таблицы.

Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].

Выберите команду Рекомендуемые сводные таблицы [Recommended PivotTables].

Выберите подходящую таблицу в списке слева. После выборы в окне справа вы увидите макет будущей таблицы.

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

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

Выберите любую ячейку исходной таблицы.

Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].

Выберите команду Сводная таблица [PivotTable].

В диалоговом окне Создание сводной таблицы [Create PivotTable] убедитесь в правильности диапазона данных, на основе которого будет строиться отчет. Если диапазон некорректный, его нужно поменять в поле Таблица и диапазон [Table/Range].

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

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

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

После формирования сводной таблицы в выбранном месте появиться область с ее названием. По умолчанию отчет называется СводнаяТаблица1 [PivotTable 1]. Для начала работы необходимо щелкнуть левой клавшей мыши по данной области. В результате в правой части листа откроется макет сводной таблицы.

Макет сводной таблицы состоит из списка полей сводной таблицы, в котором перечислены все заголовки исходной таблицы и четырех областей: ФИЛЬТРЫ, КОЛОННЫ, СТРОКИ и ЗНАЧЕНИЯ.

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

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

Данную операцию можно сделать еще 2 способами:

  • отметить флажок напротив поля Группа;
  • щелкнуть по полу Группа правой кнопкой мыши и выбрать Добавить в названия строк [Add to Row Labels].

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

Теперь осталось добавить сумму по полю Рыночная стоимость. Для этого перенесем поле Рыночная стоимость в область значений. Желаемая таблица получена.

Делаем сводную таблицу в Excel

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

Сводные таблицы в Эксель

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

Стандартный способ создания сводной таблицы

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

Читать еще:  Ютуб режим пк

Для примера рассматривается таблица со сформированным объемом заработной платы сотрудникам предприятия. Из информации известны ФИО, категория, дата начисления средств, пол работников и общая сумма к выплате. Необходимо будет распределить всю информацию в одну общую таблицу. Обратите внимание, расчеты будут вестись только за 3 квартал года.

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

  1. Наведите курсор мыши на любую ячейку, которая имеется в таблице.
  2. Перейдите в раздел «Стили» и разверните строку «Форматировать как таблицу». Выберите любой стиль и установите его.
  3. В отобразившемся окне впишите место расположения таблицы, после чего кликните на пункт «Ок». Вы можете выбирать абсолютно любые удобные координаты.
  4. После выполнения этих действий таблица станет динамичной и приобретет функцию автоматического растягивания.
  5. На этом же этапе проекту присваивается название, которое при необходимости можно изменить в разделе «Конструктор».
  6. Теперь можно приступать непосредственно к созданию самой суммарной таблицы. Перейдите в раздел «Вставка» и нажмите пункт «Сводная таблица».

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

    Параметры таблицы

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

    1. Нажмите на кнопку, которая находится возле пункта «Дата».
    2. В отобразившемся окне поставьте флажок возле значения «Выделить несколько…».
    3. Уберите флажки, которые расположены напротив всех дат, не относящихся к третьему кварталу, и кликните на клавишу «Ок».

    По такому же принципу можно выбрать определенный пол для отчета.

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

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

    1. Перейдите во вкладку «Файл» и в появившемся списке выберите строку «Параметры».
    2. Перед вами появится новая вкладка, в которой слева необходимо отметить пункт «Панель быстрого доступа».
    3. Из левого столбца в перечне выберите «Мастер сводных таблиц и диаграмм», и нажмите на пункт «Добавить». Для окончания переноса кликните на клавишу «Ок».

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

    1. В «Шаге 1» определите, где находятся данные, которые будут применяться во время формирования таблицы, и определите вид отчета, после чего кликните на пункт «Далее».
    2. На втором этапе можно указать диапазон исходных данных, но в описанном в статье примере эти действия предпринимать нет необходимости, поэтому просто нажмите «Далее».
    3. В последнем шаге необходимо выбрать место расположения создаваемой таблицы, затем подтвердите свои действия кнопкой «Готово».
    4. После выполнения этих шагов откроется точно такой же документ, который был описан в предыдущем способе.
    5. Для заполнения суммарной таблицы выполните такие же действия, которые описывались в стандартном способе создания таблицы.

    Заключение

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

    Ссылка на основную публикацию
    Adblock
    detector