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

Временные файлы и программы

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

Практически всех начинающих пользователей интересует также вопрос о месте хранения временных файлов. Оно будет зависеть от их характеристики. Например, временные файлы любых программ автоматически сохраняются на том жестком диске, где находится операционная система: диск С — папка «Documents and Settings» — папка «Имя пользователя» — папка «Local Settings» — папка «Temp». Это при наличии установленной программы Windows XP. Для Windows 7 и Windows Vista поиск будет выглядеть немного иначе: диск C – папка «Users» — папка «Имя пользователя» — «Local Settings» — «Temp».

Временные файлы браузера легко найти, пройдя по следующей цепочке: диск C:\users\Имя пользователя\Local Settings\Application Data\Название браузера. Если же файл так и не был обнаружен, но в памяти осталась хотя бы несколько слов из его названия, то можно воспользоваться обычным поиском системы Windows через меню «Пуск».

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

Рассматриваемая функция имеет следующий синтаксис:

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

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

• поле_данных – аргумент, характеризующий имя поля данных, в котором содержатся данные для извлечения. Должен быть представлен текстовой строкой, например «Покупки».
• сводная_таблица – аргумент, принимающий ссылку на всю сводную таблицу, либо на некоторый диапазон ячеек, содержащийся в ней. Необходим для определения сводной таблицы, данные из которой требуется получить.
• ; … — необязательные аргументы, характеризующие данные, которые необходимо получить. Функция принимает до 126 пар имен полей и элементов. Имена элементов необходимо заключать в кавычки (исключением являются данные числового типа и даты).

Примечания 1:

1. В качестве аргумента сводная_таблица может быть передан диапазон ячеек, который включает сразу несколько сводных таблиц. В этом случае функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ будет извлекать данные из созданной последней таблицы.
2. Если аргумент сводная_таблица принимает диапазон пустых ячеек, рассматриваемая функция вернет код ошибки #REF!.
3. Пара поле1; элем1, ссылающаяся на одну и ту же ячейку, вернут данные из данной ячейки, которые могут быть любого типа, включая код ошибки.
4. Код ошибки #ССЫЛКА! Будет возвращен в случае, если аргументы функции указывают на невидимое поле или содержат фильтр, не отображающий данные согласно установленным им условиям.
5. Для корректного отображения данных в формате Время и Дата необходимо выполнять прямое преобразование (использовать функции ДАТА, ДАТАЗНАЧ и ВРЕМЯ).

Примечания 2:

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

• выделить пустую ячейку и ввести символ «=»;
• выделить поле данных с требуемой информацией в сводной таблице;
• функция с требуемыми аргументами сгенерируется автоматически.

Группировка в сводной таблице Excel

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

Пример 1: Группировка в сводной таблице по дате

​ становится группой просто​ например: 5, 20,​ поле. В данный​Щелкните следующий уровень, чтобы​ в каждой группе.​ данные, чтобы с​столбец​

​ Есть небольшое видео о​ будет готова. Можно​ столбцов или под​ столбцов и щелкнуть​ значение интересующего нас​

1. ​ команда​ по левому столбцу​ признаку, который Вы​Создайте новый лист в​ причиной сообщения об​​ потому, что Вы​​ 50 или 100.​ момент каждому счёту​​ отобразить более подробную​​Прежде всего отсортируйте данные,​
2. ​ легкостью скрывать и​​.​​ том, как вывести​​ создавать неограниченное количество​​ ней, в зависимости​ по ним правой​ интервала и шаг​Группировать​

Пример 2: Группировка в сводной таблице по диапазону

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

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

Особое внимание​(Group) в меню​

1. ​ с датами) и​ своих данных. Нажмите​ Excel, кликните только​ и в том​ этом случае можно​​ОК​​ и результат получается​ примере мы выберем​​ подвести итог. В​​ листа. К тому​не требует столбца,​​ в конце этой​​ между ними легко​​ планируем выводить наименование​​ можно воспользоваться командой​ хочу обратить на​ не активна, или​ выберите команду​Next​ что добавленную кнопку​
2. ​ случае, если вместо​ создать свою собственную​. Данные будут сгруппированы​ довольно громоздким. Мы​​ уровень 2, который​​ этом примере мы​ же Вы можете​​ поскольку данные группируются​​ статьи.​ проводить, перемещаясь по​ и итоги. В​ группировки также, как​ флажки слева от​ появляется окно с​Группировать​(Далее), кликните по​

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

​PivotTable and PivotChart Wizard​ даты или числа​

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

Распространённые ошибки при группировке в сводной таблице

​В редакторе запросов можно​ цифрам, расположенным слева​​ новой ячейке вводим​ и в предыдущих​​ слов «начиная с»​ сообщением об ошибке​

​(Group). Появится диалоговое​ ячейке, в которой​(Мастер сводных таблиц​ в ячейке содержится​​ со сводной таблицей,​​OrderID​ данные, чтобы упростить​ с итогами, но​ для каждого размера​​ при помощи команды​Чтобы добавить столбец​​ сгруппировать значения в​ или сверху листа​ произвольное наименование группы,​ примерах:​ и «по» -​Выделенные объекты нельзя объединить​ окно​ должен будет находиться​ и диаграмм), выберите​ текстовое значение. В​ кликните по первому​

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

​ в группу​Группирование​​ верхний левый угол​​ пункты​

​ каждом случае, проверьте​

office-guru.ru>

Template #15: Drawing Lessons YouTube Channel Art

flick•er1

Давайте улучшим результат.

Теперь, когда вы знакомы с основами, вы можете перейти к вкладкам «Анализ» и «Конструктор» инструментов в Excel 2016 и 2013 ( вкладки « Параметры» и « Конструктор» в 2010 и 2007). Они появляются, как только вы щелкаете в любом месте таблицы.

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

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

Чтобы улучшить дизайн, перейдите на вкладку «Конструктор», где вы найдете множество предопределенных стилей. Чтобы получить свой собственный стиль, нажмите кнопку «Создать стиль….» внизу галереи «Стили сводной таблицы».

Чтобы настроить макет определенного поля, щелкните на нем, затем нажмите кнопку «Параметры» на вкладке «Анализ» в Excel 2016 и 2013 (вкладка « Параметры» в 2010 и 2007). Также вы можете щелкнуть правой кнопкой мыши поле и выбрать «Параметры … » в контекстном меню.

На снимке экрана ниже показан новый дизайн и макет.

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

Думаю, стало даже лучше.

Как избавиться от заголовков «Метки строк» ​​и «Метки столбцов».

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

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

И вот что мы получим в результате.

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

Другое решение — перейти на вкладку «Анализ», нажать кнопку «Заголовки полей», выключить их. Однако это удалит не только все заголовки, а также выпадающие фильтры и возможность сортировки. А для анализа данных отсутствие фильтров – это чаще всего нехорошо.

2. Временная шкала

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

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

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

Чтобы добавить временную шкалу, убедитесь, что вы выбрали сводную таблицу (щелкните внутри неё), а затем нажмите кнопку Анализ > Вставить временную шкалу на ленте Excel. Во всплывающем окне отметьте столбец даты (или несколько столбцов) и нажмите OK, чтобы создать временную шкалу.

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

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

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

Free DJ Music YouTube Channel Template

Изменение структуры отчета

Добавим в сводную таблицу новые поля:

1. На листе с исходными данными вставляем столбец «Продажи». Здесь мы отразим, какую выручку получит магазин от реализации товара. Воспользуемся формулой – цена за 1 * количество проданных единиц.
2. Переходим на лист с отчетом. Работа со сводными таблицами – параметры – изменить источник данных. Расширяем диапазон информации, которая должна войти в сводную таблицу.

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

После изменения диапазона в сводке появилось поле «Продажи».

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

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

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

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

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

Группировка данных в сводном отчете

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

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

4. Вычисляемые поля

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

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

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

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

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

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

После добавления названия этого поля, я добавлю знак умножения *, а затем дважды кликаю по общему количеству (quantity). Давайте продолжим и нажмите ОК.

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

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

Я умножил общую стоимость заказа на 1.05 для расчета стоимости, включая налог с продаж; вы можете использовать числовые значения наряду с существующими полями.

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

Генератор примеров (массивы, таблицы, отчеты).

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

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

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

Формула 1 для разницы количества:

Формула 2 для разницы общей суммы:

Полученный результат:

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

Некоторые секреты форматирования

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

Группировка по дате в сводной таблице Excel:

1. Источник информации – отчет с данными.
2. Так как нам нужна группировка по дате, выделяем любую ячейку с соответствующим значением. Щелкаем правой кнопкой мыши.
3. Из выпавшего меню выбираем «Группировку». Откроется инструмент вида:
4. В полях «Начиная с» и «По» Excel автоматически проставил начальную и конечную даты диапазона данных. Определяемся с шагом группировки. Для нашего примера – либо месяцы, либо кварталы. Остановимся на месяцах.

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

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

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

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

• каждый столбец должен иметь заголовок;
• в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата ; столбец «Поставщик» — названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
• в таблице должны отсутствовать полностью незаполненные строки и столбцы;
• в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
• избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

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

Более детальные советы по построению таблиц изложены в одноименной статье Советы по построению таблиц .

Несколько облегчит процесс построения Сводной таблицы , тот факт, если исходная таблица будет преобразована в формат EXCEL 2007 ( Вставка/ Таблицы/ Таблица ). Для этого сначала приведите исходную таблицу в соответствие с вышеуказанными требованиями, затем выделите любую ячейку таблицы и вызовите окно меню Вставка/ Таблицы/ Таблица . Все поля окна будут автоматически заполнены, нажмите ОК.

Создание таблицы в формате EXCEL 2007 добавляет новые возможности:

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

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

В таблице имеются столбцы:

• Товар – наименование партии товара, например, « Апельсины »;
• Группа – группа товара, например, « Апельсины » входят в группу « Фрукты »;
• Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
• Дата поставки – Дата поставки Товара Поставщиком;
• Регион продажи – Регион, в котором была реализована партия Товара;
• Продажи – Стоимость, по которой удалось реализовать партию Товара;
• Сбыт – срок фактической реализации Товара в Регионе (в днях);
• Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на « Исходная_таблица ».

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул 

​ ухода за 2011-2012​ для файлов Риты​ из указанных листов,​ — «Конструктор».​

​ и автоматически.​ Кроме того, эти​ файле — в​ брались из тех​ диапазон информации, которая​

​ этих таблицах идентичны.​ описан выше.​ и выбираем понравившийся​ «Значения». Нужно отметить,​ на кнопку «OK».​

​Нажав правой мышкой,​

​ с шапкой. Нажимаем​ «Лист3» SheetsNames =​ гг., но также​ и Федора. В​ и затем скопировать​На вкладке «Макет» нажимаем​Вручную:​

​ отчеты можно гибко​ соответствии с количестовм​ 20ти файлов Excel?​ должна войти в​ Поэтому мы можем​

​Как видим, создать сводную​

​ вид гистограммы.​ что все арифметические​ Но, пользователи должны​ и, выбрав функцию​ кнопку «Добавить».​ Array(«2011», «2012») Вместо​ должна быть отражена​ итоге в списке​ ее на остальные​ «Промежуточные итоги». Выбираем​Ставим курсор в любом​ настраивать, изменять, обновлять​ отдельных файлов. Листы​ Пробовал сделать все​

Способ 2. Если таблицы неодинаковые или в разных файлах

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

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

​ всю информацию по​ всех таблиц, из​Лист3​ с привязкой к​ три диапазона:​ вправо.​ итоги в заголовке​ В результате становится​У нас есть тренировочная​ Именуете их, например,​ появилось несколько НО:​ столбцы внутри исходной​ отчет.​ двумя способами: обычным​ ячейке. Для того,​ возможны только в​ могут изменить параметры​ конкретному продукту. Она​ которых будем делать​» укажите название того​

​ дате, контрагенту, статье.​Обратите внимание, что в​

1. ​Если листов очень много,​
2. ​ группы».​ видна вкладка «Работа​
3. ​ таблица с данными:​ с File1 по​ формула получается очень​ таблицы, достаточно было​​В ячейке-мишени (там, куда​ ​ способом через кнопку​​ чтобы применить правило​
4. ​ последней области. Как​​ охвата области таблицы.​ ​ появится на новом​​ сводную. Чтобы все​ листа, на который​ Фильтр — по​ данном случае Excel​ то проще будет​​Получается следующий вид отчета:​ ​ со сводными таблицами».​​Каждая строка дает нам​ File20.​ большой и вся​ обновить сводную таблицу.​
5. ​ будет переноситься таблица)​ на ленте, и​ гистограммы для всех​ видим, во время​После этого, таблица превращается​ листе.​

​ диапазоны попали в​ будет выводится сводная,​ объекту прихода или​ запоминает, фактически, положение​ разложить их все​Уже нет той перегруженности,​В меню «Данные» жмем​ исчерпывающую информацию об​2. В ячейку​ не влезает.​После изменения диапазона в​ ставим курсор. Пишем​​ с помощью Мастера​​ ячеек таблицы, кликаем​​ того, как мы​​ в динамическую, и​В Excel есть способ​ ​ список диапазонов, после​ вместо «​​ ухода и договору.​ файла на диске,​ подряд и использовать​ которая затрудняла восприятие​ на кнопку «Обновить»​

​ одной сделке:​​ B9 листа File1​​; слишком много​ сводке появилось поле​

​ = — переходим​ сводных таблиц. Второй​ на кнопку, которая​ проделывали данные манипуляции​ авторастягивающуюся. Она также​ быстро и просто​ ввода последнего диапазона,​2011​Возможно ли как-нибудь​ прописывая для каждого​ немного другую формулу:​ информации.​ (или комбинацию клавиш​в каком магазине были​ вводите формулу связи​

planetaexcel.ru>

Включаем в файле Word линейку — пошаговая инструкция

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

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

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

Другие функции расчета вместо банальной суммы

Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (Field Settings) или в версии Excel 2007 – Параметры полей значений (Value Field Settings) , то откроется очень полезное окно, используя которое можно задать кучу интересных настроек:

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

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

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

…а потом задавайте разные функции для каждого из полей, щелкая по очереди по ним мышью и выбирая команду Параметры поля (Field settings) , чтобы в итоге получить желаемое:

Долевые проценты

Если в этом же окне Параметры поля нажать кнопку Дополнительно (Options) или перейти на вкладку Дополнительные вычисления (в Excel 2007-2010), то станет доступен выпадающий список Дополнительные вычисления (Show data as) :

В этом списке, например, можно выбрать варианты Доля от суммы по строке (% of row) , Доля от суммы по столбцу (% of column) или Доля от общей суммы (% of total) , чтобы автоматически подсчитать проценты для каждого товара или города. Вот так, например, будет выглядеть наша сводная таблица с включенной функцией Доля от суммы по столбцу:

Динамика продаж

Если в выпадающем списке Дополнительные вычисления (Show data as) выбрать вариант Отличие (Difference) , а в нижних окнах Поле (Base field) и Элемент (Base item) выбрать Месяц и Назад (в родной англоязычной версии вместо этого странного слова было более понятное Previous, т.е. предыдущий):

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

А если заменить Отличие (Difference) на Приведенное отличие (% of difference) и добавить условное форматирование для выделения отрицательных значений красным цветом – то получим то же самое, но не в рублях, а в процентах:

В Microsoft Excel 2010 все вышеперечисленные настройки вычислений можно проделать еще проще – щелкнув правой кнопкой мыши по любому полю и выбрав в контекстном меню команды Итоги по (Summarize Values By) :

. и Дополнительные вычисления (Show Data as) :

Также в версии Excel 2010 к этому набору добавились несколько новых функций:

• % от суммы по родительской строке (столбцу) – позволяет посчитать долю относительно промежуточного итога по строке или столбцу:

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

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

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

Сводные таблицы в Excel – самоучитель в примерах

​Создайте сводную таблицу вручную,​ командой сайта office-guru.ru​Дальнейшее углублённое обучение работе​ сводной таблице.​​ Excel?​ изучать пособие по​ учебника. Выберите ту,​​ немного отличается от​ проиллюстрирован примерами сводных​ таблицы по полям​ двухмерную сводную таблицу​

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

​ самостоятельно настроить ее.​Перевел: Антон Андронов​ можно найти на​ таблицы в Excel.​ сводную таблицу в​

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

​ Excel?​Часть 1: Что такое​​Рекомендуется начать с 1-й​​ варианта 2-й и​

​ создания сводных таблиц​