Pivot table excel 2013 как сделать

Pivot table excel 2013 как сделать

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

На вкладке Вставка (Insert) появилась кнопка Рекомендуемые сводные таблицы (Recommended Pivot Tables), предлагающая несколько подходящих сводных таблиц разного типа на выбор в зависимости от вида исходных данных:

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

Фильтрация с помощью Временной Шкалы

К возможности фильтровать сводные таблицы срезами (slicers), появившейся в Excel 2010, добавили Временную шкалу (Timeline) — интерактивную графическую ось времени, которую можно вставить на вкладке Анализ (Analysis) . Выделив любой временной интервал на оси, мы получим в привязанной к ней сводной результаты именно за выбранный период:

Новый механизм расчета сводных таблиц (Data Object Model)

В Excel 2013 впервые реализована возможность строить сводные таблицы не на основе классического механизма кэша, как во всех прошлых версиях до этого. Теперь при построении сводной таблицы можно установить в нижней части диалога неприметную галочку Добавить эти данные в модель данных (Add to Data Object Model) и, тем самым, использовать для построения сводной «движок» Power Pivot, т.е. по-сути полноценную базу данных, встроенную в Excel:

Именно объектная модель позволяет сделать один из самых впечатляющих трюков этой версии — построить сводную сразу по нескольким диапазонам данных.

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

На вкладке Данные (Data) появилась кнопка Отношения (Relationships), позволяющая связать нескольких таблиц по ключевым полям (столбцам) с помощью вот такого диалога:

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

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

Функция подсчета количества уникальных элементов

Если вы строите сводную с использованием Data Object Model, то в списке функций производимых над данными (правой кнопкой мыши по полю — Параметры поля) добавится еще одна новая — Число различных элементов (Distinct Count). Она вычислит не общее количество непустых элементов, как обычная функция Счет (Count), а количество неповторяющихся представителей.

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

Экспресс-просмотр

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

Логическим продолжением и развитием этой идеи стала новая функция Экспресс-просмотр (Quick Explore) в сводных таблицах Excel 2013.

При выделении в сводной любой ячейки с числовым результатом рядом появляется смарт-тег Экспресс-просмотра. Нажав на него, можно выбрать нужную нам таблицу (если сводная строится по нескольким таблицам) и интересующее нас поле для детализации. Так, например, если я выделю ячейку с результатами продаж менеджера Иванова и выберу поле Заказчик для детализации:

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

Недостатки

Из замеченного в процессе работы:

  • В сводных таблицах построенных на основе Объектной модели не работает группировка. Это хоть и не критично, но печально. Будем надеяться, что исправят.
  • Иногда названия столбцов в списке полей не соответствуют реальности, т.е. берутся не из ячеек шапки, а непонятно откуда. Помогает предварительное форматирование исходных диапазонов как «умных» таблиц.
  • Для выгрузки исходных данных из Объектной модели приходится использовать команду Данные — Подключения (Data — Connections).

This Excel tutorial explains how to create a pivot table in Excel 2013 (with screenshots and step-by-step instructions).

Question: How do I create a pivot table in Microsoft Excel 2013?

Answer: In this example, the data for the pivot table resides on Sheet1.

Highlight the cell where you’d like to see the pivot table. In this example, we’ve selected cell A1 on Sheet2.

Next, select the INSERT tab from the toolbar at the top of the screen. In the Tables group, click on the Tables button and select PivotTable from the popup menu.

A Create PivotTable window should appear. Select the range of data for the pivot table and click on the OK button. In this example, we’ve chosen cells A1 to F16 in Sheet1.

Your pivot table should now appear as follows:

Next, choose the fields to add to the report. In this example, we’ve selected the checkboxes next to the Order ID and Quantity fields.

Next in the VALUES section, click on the «Sum of Order ID» and drag it to the ROWS section.

Finally, we want the title in cell A1 to show as «Order ID» instead of «Row Labels». To do this, select cell A1 and type Order ID.

Your pivot table should now display the total quantity for each Order ID as follows:

Last updated: January 25, 2017

Learning how to make a pivot table in Excel 2013 will give you a new tool in your Excel utility belt that can make sorting and analyzing data much simpler. A pivot table in Excel 2013 offers a variety of options for comparing data in columns, and grouping similar data in a way that would otherwise be difficult to do manually. When you create a pivot table in Excel 2013, you are taking data from your spreadsheet and inserting it into, by default, a new format in another sheet of your Excel workbook. Once the data has been reconfigured for the pivot table, you can adjust the way that it is organized and displayed to help you better understand the information that the spreadsheet contains. So read below to learn how to make a pivot table in Excel 2013.

If you are planning to install Office 2013 on a second computer, consider getting an Office 2013 subscription instead. This allows you to install your copy of Office on up to five computers, with the ability to remove and add those licenses if you get a new computer, or want to switch a license to a different computer.

How to Make a Pivot Table in Excel 2013

If you find yourself asking the question “what is a pivot table,” then know you are not alone. According to Wikipedia, a pivot table is “a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, a pivot table can automatically sort, count, total or average the data stored in one table or spreadsheet, displaying the results in a second table showing the summarized data”.

The way that I use pivot tables the most often it to quickly total amounts that are associated with different rows. For example, I have created a very simple spreadsheet below that lists sales amounts for three different members of a sales team. There is only a small amount of data in this table in an effort to keep this as simple as possible, but this same process can easily be expanded to handle much larger amounts of data, and prevent you from needing to write any formulas or macros to get the information that you want. We are going to create a simple pivot table that takes these 8 sales and combines them into a three-row report that shows the name of the salesperson and their total sales. So read below to learn how to create this type of pivot table in Excel 2013.

Step 1: Open the Excel 2013 spreadsheet that contains the data that you want to put into a pivot table.

Step 2: Use your mouse to highlight all of the data that you want to include in the pivot table.

Highlight the data you want to include in the pivot table

Step 3: Click the Insert tab at the top of the window, then click the PivotTable icon in the Tables section of the ribbon. This opens a Create PivotTable window.

Click Insert, then PivotTable

Step 4: I am going to leave these settings at their defaults, which will create a new worksheet for the pivot table. Click the OK button to continue.

Click the OK button

Step 5: Check the box to the left of each of your column names in the PivotTable Fields column at the right side of the window.

Check the box to the left of each column to include in the PivotTable

Step 6: View your pivot table in the spreadsheet. You will see that the initial data from my spreadsheet has been combined into easy to read totals that give me the totals that I wanted from my data.

Now that your data is in the pivot table, you can manipulate it by double-clicking a column heading, then changing a setting on that screen. For example, if I wanted to view the number of sales instead of the total sales amount, I could change the option under Summarize value field by to Count instead of Sum.

Change your PivotTable options

Now that you have seen how a basic pivot table works, take some time to experiment with the feature to see what different types of data you can produce from your spreadsheets. Pivot tables are incredibly helpful for reducing the amount of manual addition that anyone should have to do with Excel data that can’t be easily totaled with a formula, so it can be a real time-saver. If you need more help with Excel 2013 pivot tables, visit Microsoft’s Excel 2013 help site, where they provide a very comprehensive view of the capabilities of pivot tables in Excel 2013.

Some helpful tips for working with pivot tables in Excel 2013

  • The order that you click the boxes in step 5 can matter. If Excel 2013 isn’t displaying your data the way that you would want, try clicking the boxes next to each column in a different order.
  • You can format your pivot table cells in a similar manner to how you would a regular cell. Simply right-click the cell, then choose the Format Cells option.
  • There is an option on the Design tab under PivotTable Tools call Banded Rows. If you check that option Excel will automatically alternate the fill color for each row in your pivot table. This can make the data a little easier to read.
  • Additionally, there are options on that Design tab for Subtotals, Grand Totals, Report Layout and Blank Rows. Once you have your data showing correctly in the pivot table, these options can help you create well-formatted reports that you can share with your colleagues.

For more information on Excel 2013, read our article about changing the default save location in Excel 2013. You can choose to save your spreadsheets to your computer by default, instead of the SkyDrive option that many Office 2013 installations will do by default.

Читайте также  Печать документа в Excel

Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.

Источник:
http://word-office.ru/pivot-table-excel-2013-kak-sdelat.html

Как сделать сводную таблицу в Excel: пошаговая инструкция

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

Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.

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

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

Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

Рассмотрим, как создать сводную таблицу в Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

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

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

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

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

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

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

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

4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.

В целом требований немного, но их следует знать.

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

Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши

или
через команду во вкладке Данные – Обновить все.

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

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

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

Источник:
http://statanaliz.info/excel/svodnye-tablitsy/kak-v-excel-sdelat-svodnuyu-tablitsu/

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

В этом курсе:

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

Сводные таблицы работают немного по-разному в зависимости от того, какая платформа используется для запуска Excel.

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

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

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

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

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

В поле Таблица или диапазон проверьте диапазон ячеек.

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

Нажмите кнопку ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На вкладке Вставка нажмите кнопку Рекомендуемые сводные таблицы.

«Рекомендуемые сводные таблицы» для автоматического создания сводной таблицы» />

Excel проанализирует данные и предоставит несколько вариантов, как в этом примере:

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

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

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

Если вы используете Excel для Mac 2011 или более ранней версии, кнопка «Сводная таблица» находится на вкладке Данные в группе Анализ.

Читайте также  Как столбцы сделать буквами в excel?

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

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

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

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

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

Соответствующие поля в сводной таблице

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

Затем измените функцию в разделе Суммировать по. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, «Сумма по полю имя_поля»), но вы можете изменить это имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Число. .

Совет: Так как при изменении способа вычисления в разделе Суммировать по обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно выбрать пункт Найти (в меню «Изменить»), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.

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

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

Отображение значения как результата вычисления и как процента

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

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

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

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

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

Выполните одно из следующих действий:

Чтобы создать сводную таблицу вручную, выберите создать собственную сводную таблицу.

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

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

Изменение исходных данных и конечной ячейки

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

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

Выполните одно из следующих действий:

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

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

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

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

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

Соответствующие поля в сводной таблице

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

Затем измените функцию в разделе Операция. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, «Сумма по полю имя_поля»), но вы можете изменить имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Числовой формат.

Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.

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

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

диалоговое окно «Дополнительные вычисления»» />

Отображение значения как результата вычисления и как процента

Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.

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

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

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

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

Источник:
http://support.microsoft.com/ru-ru/office/%D1%81%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%B8%D0%B5-%D1%81%D0%B2%D0%BE%D0%B4%D0%BD%D0%BE%D0%B9-%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D1%8B-%D0%B4%D0%BB%D1%8F-%D0%B0%D0%BD%D0%B0%D0%BB%D0%B8%D0%B7%D0%B0-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85-%D0%BB%D0%B8%D1%81%D1%82%D0%B0-a9a84538-bfe9-40a9-a8e9-f99134456576

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

Постановка задачи

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

Прежде, чем начать давайте уточним пару моментов. Априори я полагаю, что в наших данных выполняются следующие условия:

  • Таблицы могут иметь любое количество строк с любыми данными, но обязательно — одинаковую шапку.
  • На листах с исходными таблицами не должно быть лишних данных. Один лист — одна таблица. Для контроля советую использовать сочетание клавиш Ctrl + End , которое перемещает вас на последнюю использованную ячейку листа. В идеале — это должна быть последняя ячейка таблицы с данными. Если при нажатии на Ctrl + End выделяется какая-либо пустая ячейка правее или ниже таблицы — удалите после таблицы эти пустые столбцы справа или строки снизу и сохраните файл.

Способ 1. Сборка таблиц для сводной с помощью Power Query

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

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

Затем на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) выберем команду Создать запрос — Из файла — Excel (Get Data — From file — Excel) и укажем исходный файл с таблицами, которые надо собрать:

В появившемся окне выберем любой лист (не принципиально какой именно) и внизу жмем кнопку Изменить (Edit) :

Поверх Excel должно открыться окно редактора запросов Power Query. В правой части окна на панели Параметры запроса удалим все автоматически созданные шаги кроме первого — Источник (Source) :

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

Удалим все столбцы, кроме колонки Data, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns) :

Затем можно развернуть содержимое собранных таблиц, щелкнув по двойной стрелке в верхней части столбца (флажок Использовать исходное имя столбца как префикс можно при этом отключить):

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

Осталось поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) и удалить попавшие в данные повторяющиеся шапки таблиц с помощью фильтра:

Сохраним всё проделанное с помощью команды Закрыть и загрузить — Закрыть и загрузить в. (Close & Load — Close & Load to. ) на вкладке Главная (Home) , а в открывшемся окне выберем опцию Только подключение (Connection Only) :

Всё. Осталось только построить сводную. Для этого идём на вкладку Вставка — Сводная таблица (Insert — Pivot Table) , выбирыем опцию Использовать внешний источник данных (Use external data source) , а затем, нажав кнопку Выбрать подключение, наш запрос. Дальнейшее создание и настройка сводной происходит совершенно стандартным образом путем перетаскивания нужных нам полей в области строк, столбцов и значений:

Читайте также  График производства работ (образец)

Если в будущем изменятся исходные данные или добавится еще несколько листов-магазинов, то достаточно будет обновить запрос и нашу сводную с помощью команды Обновить все на вкладке Данные (Data — Refresh All) .

Способ 2. Объединяем таблицы SQL-командой UNION в макросе

Еще одно решение нашей задачи представлено вот таким макросом, который создает набор данных (cache) для сводной таблицы, используя команду UNION языка запросов SQL. Эта команда объединяет таблицы со всех указанных в массиве SheetNames листов книги в единую таблицу данных. То есть вместо физического копирования-вставки диапазонов с разных листов на один мы делаем то же самое в оперативной памяти компьютера. Потом макрос добавляет новый лист с заданным именем (переменная ResultSheetName) и создает на нем полноценную(!) сводную на основе собранного кэша.

Чтобы воспользоваться макросом используйте кнопку Visual Basic на вкладке Разработчик (Developer) или сочетание клавиш Alt + F11 . Затем вставляем новый пустой модуль через меню Insert — Module и копируем туда следующий код:

Готовый макрос потом можно запустить сочетанием клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer — Macros) .

Минусы такого подхода:

  • Данные не обновляются, т.к. кэш не имеет связи с исходными таблицами. При изменении исходных данных надо запустить макрос еще раз и построить сводную заново.
  • При изменении количества листов необходимо правки в код макроса (массив SheetNames).

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

Техническое замечание: если при запуске макроса вы получаете сообщение об ошибке вида «Provider not registered», то скорее всего у вас 64-битная версия Excel или установлена не полная версия Office (нет Access). Чтобы исправить ситуацию замените в коде макроса фрагмент:

И скачайте и установите бесплатный движок обработки данных из Access с сайта Microsoft — Microsoft Access Database Engine 2010 Redistributable

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

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

В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому начиная с 2007 года Microsoft эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц. Теперь эта возможность доступна только через настраиваемую кнопку Мастер сводных таблиц (Pivot Table Wizard) , которую при желании можно добавить на панель быстрого доступа через Файл — Параметры — Настройка панели быстрого доступа — Все команды (File — Options — Customize Quick Access Toolbar — All Commands) :

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

А затем в следующем окне выделить по очереди каждый диапазон и добавить его в общий список:

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

Источник:
http://www.planetaexcel.ru/techniques/8/133/

Microsoft Excel

трюки • приёмы • решения

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

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

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

Рис. 1. Реестр рисков проекта Grant St. Move

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

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

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

Теперь, когда данные представлены в табличном формате, вы можете создать сводную таблицу, не забудьте купить клавиатуру для компьютера. Для начала установите табличный курсор в любой ячейке этой таблицы, затем щелкните на значке PivotTable (Сводная таблица) вкладки Insert (Вставка) и в появившемся меню выберите команду PivotTable (Сводная таблица).

Рис. 3. Значок PivotTable расположен в крайней части вкладки Insert (Вставка)

На экране появится диалоговое окно Create Pivot Table (Создание сводной таблицы), показанное на рис. 4. В этом диалоговом окне следует указать программе, на основе каких данных будет построена сводная таблица — рабочего листа текущей рабочей книги или внешних данных (например, SQL Server). Обратите внимание, что в поле Table/Range (Таблица или диапазон) мы оставили заданное по умолчанию значение — Table1 (Таблица1).

Рис. 4. Диалоговое окно Create Pivot Table (Создание сводной таблицы)

Щелкните на кнопке ОК. Программа немедленно создаст макет сводной таблицы па новом рабочем листе (рис. 5), в правой части которого расположена панель Pivot Table Field List (Список полей сводной таблицы). Обратите внимание, что в верхней части этой панели перечислены названия всех полей созданной нами таблицы реестра рисков.

Рис. 5. Рабочий лист, на котором расположен макет сводной таблицы и панель Pivot Table Field List (Список полей сводной таблицы)

Названия полей, перечисленные на панели Pivot Table Field List, представляют собой названия заголовков столбцов, взятые из нашей таблицы. Области макета сводной таблицы предназначены для различного отображения данных. Их можно представлять как некую трехаспектную палитру. Допустим, нам требуется узнать количество рисков по каждой категории. Например, сколько внешних рисков у нашего проекта? Начните с перетаскивания поля Risk Category (Категория риска), как показано на рис. 6, в область Drop Row Fields Here (Перетащите сюда поля строк). (Местоположение этой области показано на рис. 5).

Рис. 6. Результат перетаскивания поля Risk Category (Категория риска) в область Drop Row Fields Here (Перетащите сюда поля строк)

Как видите, название поля Risk Category (Категория риска) появилось в области Row Labels (Названия строк), которая расположена в нижней части панели Pivot Table Field List (Список полей сводной таблицы). Теперь перетащите поле Risk Name (Название риска) в область макета сводной таблицы Drop Data Items Here (Перетащите сюда элементы данных), как показано на рис. 7.

Рис. 7. Результат перетаскивания поля Risk Name (Название риска) в область Drop Data /terns Here (Перетащите сюда элементы данных)

Обратите внимание, что в нижней части созданной нами сводной таблицы программа Excel автоматически добавила строку с заголовком Grand Total (Общий итог), в которой отображено общее количество названий рисков по отдельным категориям. Теперь нетрудно заметить, что, например, категория «Связанные с решением кадровых вопросов» (поле Organizational) содержит два риска, категория «Технические» (поле Technical) — четыре и т.д. В последней строке — Grand Total (Общий итог) — указано общее количество рисков (11) по всем категориям.

Как вы, должно быть, заметили, в области Values (Значения), расположенной в нижней части панели Pivot Table Field List (Список полей сводной таблицы), появился элемент Count of Risk Name (Количество по полю Risk Name), т.е. суммарное количество рисков (см. рис. 7). Существует множество способов отображения, представления и подсчета данных в сводных таблицах Excel. Если хотите увидеть результаты и подсчеты, которые сделает для вас Excel, поэкспериментируйте с перемещением полей из списка панели Pivot Table Field List (Список полей сводной таблицы) в разные области макета сводной таблицы.

Источник:
http://excelexpert.ru/kak-ispolzovat-svodnuyu-tablicu-excel-dlya-analiza-sostoyaniya-proekta

Создание отчетов с помощью Сводных таблиц (Pivot Tables)

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

В ней каждая отдельная строка содержит полную информацию об одной кредитной сделке:

  • название заемщика
  • номер сделки
  • дата выдачи и дата погашения по кредиту
  • тип кредита
  • сумма кредита

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

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

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

Выделяем всю нашу таблицу с данными и жмем в меню Вставка — Сводная таблица ( Insert — PivotTable ).

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

По умолчанию выбрано поместить сводную таблицу на новый лист (New Worksheet) , однако мы можем, при необходимости, вручную выбрать текущий или любой другой лист книги (Existing Worksheet). Лучше для этого выбирать новый лист — тогда нет риска что сводная таблица «перекроется» с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку OK и переходим к самому интересному — этапа конструирования нашего отчета.

Работа с макетом

То, что Вы увидите, нажав кнопку OK называется макет (layout) сводной таблицы:

Работать с ним несложно — надо перетаскивать мышью названия столбцов (полей) из окна Список полей сводной таблицы (PivotTable Field List) в области строк (Row Labels), столбцов (Column Lables), фильтров (Report Filter) и данных макета (Values). Единственный нюанс — делайте это точнее, не промахнитесь! Поехали.

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

И получаем следующий вид:

Не так уж это все и сложно, не так ли?

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

Источник:
http://moonexcel.com.ua/tip_pivot_tables_ru