Как обновить отчет excel - Word и Excel - помощь в работе с программами

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

О целесообразности и возможности сводных таблиц

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

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

Требования к исходной таблице:

у каждого столбца есть заголовок (первый вариант – неправильный; второй – правильный);

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

Нерациональная организация информации:

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

Лучше значения ввести следующим таким образом.

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

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

Подразумевается, что данная таблица состоит из сотен строк. Создадим сводный отчет для выведения списка книг из конкретной категории или определенного года. Задача сформулирована – перейдем к реализации.

Активизируем любую ячейку в исходном диапазоне – щелкаем мышкой. Переходим на вкладку «Вставка» — «Таблица». Нажимаем кнопку «Сводная таблица».

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

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

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

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

Теперь ставим птичку напротив поля «Год выпуска».

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

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

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

Вручную:

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

Активизируем нужное поле сводного отчета – становится доступен инструмент «Работа со сводными таблицами». Открываем вкладку «Параметры». В группе «Данные» нажимаем кнопку «Обновить».

Выделить сводную таблицу или отдельное поле, нажать сочетание клавиш Alt + F5.

Как настроить автоматическое обновление сводной таблицы в Excel:

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

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

Закрыть окно, нажав кнопку ОК.

Еще один вариант:

Открыть лист со сводным отчетом. На вкладке «Разработчик» нажать кнопку «Запись макроса».
Выполнить вручную обновление сводной таблицы – остановить запись. Нажать на кнопку «Макросы». Выбрать из доступных макросов записанный – «выполнить».

Теперь сводный отчет при открытии будет обновляться программно.

Источник

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2013 Еще…Меньше

Вы можете в любой момент нажать кнопку Обновить, чтобы обновить данные в сводных таблицах в книге. Вы можете обновить данные для сводных таблиц, импортированных из Power Query, таких как база данных (SQL Server, Oracle, Access и т. д.), куб служб Analysis Services, веб-канал данных и многие другие источники. Можно также обновить данные из таблицы Excel,которая автоматически включает все изменения внешнего источника данных.

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

Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ выберите команду Обновить или нажмите ALT+F5.

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

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

Чтобы отменить обновление, нажмите Отменить обновление.

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

На вкладке Анализ нажмите кнопку Параметры.
На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.

Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ нажмите кнопку Параметры.
На вкладке « Данные» проверьте данные обновления при открытии файла .

Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ выберите команду Обновить или нажмите ALT+F5.

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

Чтобы отменить обновление, нажмите Отменить обновление.

На вкладке Анализ нажмите кнопку Параметры.
На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.

Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ нажмите кнопку Параметры.
На вкладке « Данные» проверьте данные обновления при открытии файла .

Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ выберите команду Обновить или нажмите ALT+F5.

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

Чтобы отменить обновление, нажмите Отменить обновление.

На вкладке Анализ нажмите кнопку Параметры.
На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.

Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ нажмите кнопку Параметры.
На вкладке « Данные» проверьте данные обновления при открытии файла .

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Нужна дополнительная помощь?

Источник

Как в офисе.

В настоящей заметке представлена коллекция простых и изящных инструментов работы со сводными таблицами в Excel. То, что по-английски называется tips & tricks. Выделите время и ознакомьтесь с приводимыми здесь советами. [1] Кто знает, может быть, вы наконец-то найдете ответ на долго мучивший вас вопрос?

Совет 1. Автоматическое обновление сводных таблиц

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

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

Рис. 1. Включите опцию Обновить при открытии файла

Флажок Обновить при открытии файла следует устанавливать для каждой сводной таблицы отдельно.

Скачать заметку в формате Word или pdf, примеры в формате Excel (файл содержит код VBA).

Совет 2. Одновременное обновление всех сводных таблиц книги

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

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

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

Способ 3. Воспользуйтесь кодом VBA для обновления всех сводных таблиц в рабочей книге по требованию. Данный подход предусматривает использование метода RefreshAll объекта Workbook. Для использования этой методики создайте новый модуль и введите следующий код:

Учтите, что метод RefreshAll наравне со сводными таблицами обновляет все внешние диапазоны данных. Если рабочая книга содержит данные из внешних источников, например, базы данных или внешние файлы, все они будут обновлены вместе со сводными таблицами (подробнее о записи кода VBA см. VBA в сводных таблицах).

Совет 3. Сортировка элементов данных в произвольном порядке

На рис. 2 показан заданный по умолчанию порядок отображения регионов в сводной таблице. Регионы отсортированы в алфавитном порядке: Запад, Север, Средний Запад и Юг. Если ваши корпоративные правила требуют, чтобы сначала отображался регион Запад, а затем — регионы Средний Запад, Север и Юг, выполните ручную сортировку. Просто введите Средний Запад в ячейку С4 и нажмите клавишу Enter. Порядок сортировки регионов изменится.

Рис. 2. Регионы отображаются в алфавитном порядке

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

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

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

Выделите копируемые данные сводной таблицы, щелкните правой кнопкой мыши и в контекстном меню выберите пункт Копировать (или наберите на клавиатуре Ctrl+C).
Щелкните правой кнопкой мыши в произвольном месте рабочего листа и в контекстном меню выберите команду Вставить (или наберите Ctrl+V).

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

Выделите всю сводную таблицу, щелкните правой кнопкой мыши и в контекстном меню выберите пункт Копировать. Если сводная не содержит область ФИЛЬТРЫ, то для выделения области сводной таблицы можно воспользоваться клавиатурным сокращением Ctrl+Shift+*.
Щелкните правой кнопкой мыши в произвольном месте листа и в контекстном меню выберите параметр Специальная вставка.
Выберите параметр Значения и щелкните ОК.

Перед преобразованием сводной таблицы целесообразно удалить промежуточные итоги, поскольку они не слишком нужны в автономном наборе данных. Чтобы удалить все промежуточные итоги пройдите по меню Конструктор -> Промежуточные итоги -> Не показывать промежуточные итоги. Для удаления конкретных промежуточных итогов щелкните правой кнопкой мыши на ячейке, в которой эти итоги вычисляются. Выберите в контекстном меню пункт Параметры поля и в диалоговом окне Параметры поля в разделе Итоги выберите переключатель Нет. После щелчка на кнопке ОК промежуточные итоги будут удалены.

Совет 5. Заполнение пустых ячеек в полях СТРОКИ

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

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

Обратите внимание на то, что поля Регион и Рынок сбыта сохраняет ту же структуру строк, которая присуща при нахождении этих данных в области СТРОКИ сводной таблицы. В Excel 2013 существует быстрый способ заполнения ячеек в области СТРОКИ значениями. Кликните в области сводной таблицы, после чего пройдите по меню Конструктор -> Макет отчета -> Повторять все подписи элементов (рис. 4). После этого можно преобразовать сводную таблицу в значения, в результате чего вы получите таблицу данных без пробелов.

Рис. 4. После применения команды Повторять все подписи элементов заполняются все пустые ячейки

Совет 6. Ранжирование числовых полей сводной таблицы

В процессе сортировки и ранжирования полей, содержащих большое количество элементов данных, не всегда легко определить числовой ранг анализируемого элемента данных. Более того, если сводная таблица будет преобразована в значения, назначенный каждому элементу данных числовой ранг, отображенный в целочисленном поле, значительно облегчит анализ созданного набора данных. Откройте сводную таблицу, подобную показанной на рис. 5. Обратите внимание на то, что один и тот же показатель — Сумма по полю Объем продаж — отображается дважды. Щелкните правой кнопкой мыши на втором экземпляре показателя и в контекстном меню выберите команду Дополнительные вычисления -> Сортировка от максимального к минимальному (рис. 6.)

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

Рис. 5. Создайте сводную таблицу, в которой объем продаж в области ЗНАЧЕНИЯ выводится дважды

Рис. 6. Сортировка от максимальных значений к минимальным с помощью дополнительных вычислений

Рис. 7. Перед вами завершенный ранжированный отчет

Совет 7. Уменьшение размера отчета сводной таблицы

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

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

Совет 8. Создание автоматически развертываемого диапазона данных

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

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

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

Рис. 8. Преобразование исходных данных в таблицу

Совет 9. Сравнение обычных таблиц с помощью сводной таблицы

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

Рис. 9. Вам предстоит сравнить эти две таблицы

В процессе сравнения создается одна таблица, на основе которой создается сводная таблица. Убедитесь в том, что у вас имеется способ пометить данные, относящиеся к этим таблицам. В рассматриваемом примере для этого используется столбец Фискальный год (рис. 10). После объединения двух таблиц воспользуйтесь полученным комбинированным набором данных для создания новой сводной таблицы. Отформатируйте сводную таблицу таким образом, чтобы в качестве тега таблицы (идентификатор, указывающий на происхождение таблицы) использовалась область столбцов сводной таблицы. Как показано на рис. 11, годы находятся в области столбцов, а сведения о заказчиках — в области строк. В области данных содержатся объемы продаж для каждого заказчика.

Рис. 10. На основе двух исходных таблиц создается одна результирующая

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

Совет 10. Автоматическая фильтрация сводной таблицы

Как известно, в сводных таблицах нельзя применять автофильтры. Тем не менее существует трюк, позволяющий включить автофильтры в сводную таблицу. Принцип использования этой методики заключается в том, чтобы поместить указатель мыши справа от последнего заголовка сводной таблицы (ячейка D3 на рис. 12), а затем перейдите на ленту и выбрать команду Данные -> Фильтр. Начиная с этого момента в вашей сводной таблице появляется автофильтр! Например, вы сможете выбрать всех заказчиков с уровнем транзакций выше среднего. С помощью автофильтров в сводную таблицу добавляется дополнительный уровень аналитики.

Рис. 12. Трюк по использованию автофильтра в сводной таблице

Совет 11. Преобразование наборов данных, отображаемых в сводных таблицах

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

Рис. 13. Эту таблицу в матричном стиле следует преобразовать в табличный набор данных

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

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

Рис. 14. Результат конкатенации столбцов Рынок сбыта и Описание услуги

После создания конкатенированного столбца преобразуйте формулы в значения. Для этого выделите только что созданный столбец, нажмите Ctrl+C, после чего выполните команду Вставить -> Специальная вставка -> Значения. Теперь можно удалить столбцы Рынок сбыта и Описание услуги (рис. 15).

Рис. 15. Удалены столбцы Рынок сбыта и Описание услуги

Шаг 2. Создание сводной таблицы с несколькими диапазонами консолидации. Теперь нужно вызвать знакомый многим пользователям по предыдущим версиям Excel мастер сводных таблиц и диаграмм. Для вызова этого мастера нажмите комбинацию клавиш Alt+D+P. К сожалению, эта комбинация клавиш предназначена для англоязычной версии Excel 2013. В русскоязычной версии ей соответствует комбинация клавиш Alt+Д+Н. Но она по неизвестным мне причинам не работает. Тем не менее, можно вывести старый добрый мастер сводных таблиц на панель быстрого доступа, см. Использование мастера сводных таблиц. После запуска мастера установите переключатель В нескольких диапазонах консолидации. Кликните Далее. Установите переключатель Создать поля страницы и щелкните Далее. Определите рабочий диапазон и кликните Готово (подробнее см. Сводная таблица на основе нескольких листов или диапазонов консолидации). Вы создадите сводную таблицу (рис. 16).

Рис. 16. Сводная на основе нескольких диапазонов консолидации

Шаг 3. Дважды щелкните на пересечении строки и столбца в строке общих итогов. На этом этапе в вашем распоряжении окажется сводная таблица (рис. 16), включающая несколько диапазонов консолидации, которая является практически бесполезной. Выберите ячейку, находящуюся на пересечении строки и столбца общих итогов, и дважды щелкните на ней (в нашем примере это ячейка N88). Вы получите новый лист, структура которого напоминает структуру, показанную на рис. 17. Фактически этот лист представляет собой транспонированную версию исходных данных.

Рис. 17. Исходный набор данных был транспонирован

Шаг 4. Разбиение столбца Строка на отдельные поля. Осталось разбить столбец Строка на отдельные поля (вернуться к изначальной структуре). Добавьте один пустой столбец сразу же после столбца Строка. Выделите столбец А, а затем перейдите на вкладку ленты Данные и щелкните на кнопке Текст по столбцам. На экране появится диалоговое окно Мастер распределения текстов по столбцам. На первом шаге выберите переключатель С разделителями и щелкните на кнопке Далее. В следующем шаге выберите переключатель точка с запятой и щелкните Готово. Отформатируйте текст, добавьте заголовок и превратите исходные данные в таблицу путем нажатия Ctrl+T (рис. 18).

Рис. 18. Этот набор данных идеально подходит для создания сводной таблицы (сравните с рис. 13)

Совет 12. Включение двух числовых форматов в сводную таблицу

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

Рис. 19. Эта таблица включает несколько типов данных для одного поля из области значений

Несмотря на то что эта таблица может служить примером неплохого форматирования, не все так хорошо. Обратите внимание на то, что одни показатели должны отображаться в числовом формате, а другие — в процентном. Но в исходной базе данных поле Значение имеет тип Double. При создании сводной таблицы на основе набора данных невозможно присвоить два разных числовых формата одному полю Значение. Здесь действует простое правило: одно поле соответствует одному числовому формату. Попытка назначить числовой формат полю, которому был присвоен процентный формат, приведет к тому, что процентные значения превратятся в обычные числа, которые завершаются знаком процента (рис. 20).

Рис. 20. Каждому показателю можно присвоить только один числовой формат

Для решения этой проблемы применяется пользовательский числовой формат, который любое значение, большее 1,5, форматирует как число. Если же значение меньше 1,5, оно форматируется как процент. В диалоговом окне Формат ячеек выберите вкладку (все форматы) и в поле Тип введите следующую форматирующую строку (рис. 21): [>=1,5]$# ##0; [ » Рынок сбыта » ‘ » A4 » ‘ » PivotTable1 » ) ‘ » TempSheet «

‘Копирование данных в новую книгу и удаление временного листа

Filename:=ThisWorkbook.Path & » » & PvtItem.Name & » .xlsx «

Sheets( » Tempsheet » ).Delete

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

Const strFieldName. Имя поля, используемого для разделения данных. Другими словами, это поле, которое помещается в область фильтра/страниц сводной таблицы.
Const strTriggerRange. Ячейка триггера, в котором хранится единственное число из области данных сводной таблицы. В нашем случае ячейкой триггера является А4 (см. рис. 31).

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

[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 14.

Источник

Содержание

1 Создание отчета с помощью мастера сводных таблиц
2 Как обновить данные в сводной таблице Excel?
3 Некоторые секреты форматирования
4 Работа с итогами
5 Детализация информации
- 5.1 Видео
- 5.2 Лирическое вступление или мотивация
- 5.3 Если у вас Excel 2003 или старше
  - 5.3.1 Шаг 1. Откуда данные и что надо на выходе?
  - 5.3.2 Шаг 2. Выделите исходные данные, если нужно
  - 5.3.3 Шаг 3. Куда поместить сводную таблицу?
  - 5.3.4 Работа с макетом
- 5.4 Если у вас Excel 2007 или новее
- 5.5 P.S.
- 5.6 Ссылки по теме

Дата: 13 марта 2017 Категория: Excel Поделиться, добавить в закладки или статью

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

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

Правда же, эта таблица мало информативна и в таком виде не представляет пользы? А вот сводная таблица, сформированная из этих данных:

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

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

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

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

Чтобы сделать сводную таблицу на основании своих данных – выполните такую последовательность действий:

Установите курсор в любую ячейку таблицы
Нажмите на ленте: Вставка – Сводная таблица
Укажите расположение будущей сводной таблицы. Чтобы поместить ее на новый лист – установите галку «На новый лист». Чтобы выбрать расположение на существующих листах – выберите «На существующий лист» и в поле «Диапазон» укажите расположение верней левой ячейки сводной таблицы;

Нажмите Ок

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

«Выберите поля для добавления в отчет» — это заголовки всех столбцов, которые есть в таблице. Этими данными мы будем заполнять следующие 4 блока
Фильтры – список полей, по которым будет применяться фильтр. Эти поля появляются над сводной таблицей
Колонны – область, где задается, что будет содержаться в столбцах
Строки – область, где указывается, что будет содержаться в строках
Значения – задаем то, что будет отображаться или рассчитываться на пересечении строк или столбцов. То есть, основное тело таблицы

Области 2-5 заполняются данными перетягиванием заголовков из п.1. Например, нужно узнать, какая сумма продаж за год у менеджеров всех регионов. Значит, в строках у нас будут регионы и менеджеры, а в значениях – сумма продаж. Перетаскиваем соответствующие наименования столбцов из первой области меню компоновки в «Строки» и «Значения». Вот что получится:

Если теперь мы захотим, чтобы в столбцах данные были разбиты по группам товаров. Перетянем поле «Группа товара» в «Колонны», получаем результат:

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

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

А теперь нам, к примеру, захотелось узнать, кто из менеджеров продает больше всего. Снимем все фильтры, уберем галку «Регион» из строк. Получаем список менеджеров и их продажи. Кликнем правой кнопкой мыши в любой из строк «продажи» колонки «Общий итог», в контекстном меню выбираем Сортировка – Сортировка по убыванию. Естественно, сверху будет менеджер с наибольшими продажами, снизу – с наименьшими.

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

Вы можете настраивать макет сводной таблицы в части логики построения. Выделите любую ее ячейку и найдите на ленте Конструктор – Макет. Здесь можно сделать настройки по четырем пунктам:

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

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

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

Настраивать форматы данных
Изменять внешний вид ячеек, применять стили
Применять условное форматирование

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

Чтобы еще детальнее настроить внешний вид – кликните правой кнопкой мыши на любой ячейке сводной таблицы и в контекстном меню выберите «Параметры сводной таблицы». Здесь собрано несколько полезных настроек. Например, задайте что отображать вместо кодов ошибок, или настройте детализацию вывода на печать сводной таблицы.

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

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

Как всегда, жду Ваших вопросов и комментариев, будем становиться профессионалами вместе!

Поделиться, добавить в закладки или статью

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

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

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

У нас есть тренировочная таблица с данными:

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

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

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

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

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

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

Ставим курсор в любом месте таблицы с данными. Вызываем мастер сводных таблиц, нажимая на соответствующий инструмент, который теперь уже расположенный напанели быстрого доступа.
На первом шаге выбираем источник данных для формирования сводной таблицы. Нажимаем «Далее». Чтобы собрать информацию в сводный отчет из нескольких листов, выбираем: «в списке или базе данных Microsoft Excel».
На втором шаге определяем диапазон данных, на основании которых будет строиться отчет. Так как у нас стоит курсор в таблице, диапазон обозначится автоматически.
На третьем шаге Excel предлагает выбрать, куда поместить сводную таблицу. Жмем «Готово» и открывается макет.
Нужно обозначить поля для отображения в отчете. Допустим, мы хотим узнать суммы продаж по каждому продавцу. Ставим галочки – получаем:

Готовый отчет можно форматировать, изменять.

Это можно сделать вручную и автоматически.

Вручную:

Ставим курсор в любом месте сводной таблице. В результате становится видна вкладка «Работа со сводными таблицами».
В меню «Данные» жмем на кнопку «Обновить» (или комбинацию клавиш ALT+F5).
Если нужно обновить все отчеты в книге Excel, выбираем кнопку «Обновить все» (или комбинацию клавиш CTRL+ALT+F5).

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

На вкладке «Работа со сводными таблицами» (необходимо щелкнуть по отчету) выбираем меню «Параметры».
Открываем «Дополнительные параметры сводной таблицы». Открывается мастер.
В разделе «Данные» устанавливаем галочку напротив пункта «Обновить при открытии файла».

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

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

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

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

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

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

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

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

Работа с итогами

У нас есть сводный отчет такого вида:

Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.

Как в сводной таблице сделать итоги сверху:

«Работа со сводными таблицами» — «Конструктор».
На вкладке «Макет» нажимаем «Промежуточные итоги». Выбираем «Показывать все промежуточные итоги в заголовке группы».
Получается следующий вид отчета:

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

Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:

Получим отчет без дополнительных сумм:

Детализация информации

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

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

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

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

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

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

Жмем ОК – сводная таблица меняется.

Видео

Лирическое вступление или мотивация

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

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

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

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

Сколько и каких товаров продали в каждом месяце? Какова сезонность продаж?
Кто из менеджеров сколько заказов заключил и на какую сумму? Кому из менеджеров сколько премиальных полагается?
Кто входит в пятерку наших самых крупных заказчиков?

… и т.д.

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

Поехали…

Если у вас Excel 2003 или старше

Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные — Сводная таблица (Data — PivotTable and PivotChartReport). Запускается трехшаговый Мастер сводных таблиц (Pivot Table Wizard). Пройдем по его шагам с помощью кнопок Далее (Next) и Назад (Back) и в конце получим желаемое.

Шаг 1. Откуда данные и что надо на выходе?

На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего — «в списке или базе данных Microsoft Excel». Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel «понимает» практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант В нескольких диапазонах консолидации (Multiple consolidation ranges) применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант «в другой сводной таблице…» нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз.

Вид отчета — на Ваш вкус — только таблица или таблица сразу с диаграммой.

Шаг 2. Выделите исходные данные, если нужно

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

Шаг 3. Куда поместить сводную таблицу?

На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист — тогда нет риска что сводная таблица «перехлестнется» с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово (Finish) и переходим к самому интересному — этапу конструирования нашего отчета.

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

То, что Вы увидите далее, называется макетом (layout) сводной таблицы. Работать с ним несложно — надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы (Pivot Table Field List) в области строк (Rows), столбцов (Columns), страниц (Pages) и данных (Data Items) макета. Единственный нюанс — делайте это поточнее, не промахнитесь! В процессе перетаскивания сводная таблица у Вас на глазах начнет менять вид, отображая те данные, которые Вам необходимы. Перебросив все пять нужных нам полей из списка, Вы должны получить практически готовый отчет.

Останется его только достойно отформатировать:

Если у вас Excel 2007 или новее

В последних версиях Microsoft Excel 2007-2010 процедура построения сводной таблицы заметно упростилась. Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table) на вкладке Вставка (Insert). Вместо 3-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:

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

Названия строк (Row labels)
Названия столбцов (Column labels)
Значения (Values) — раньше это была область элементов данных — тут происходят вычисления.
Фильтр отчета (Report Filter) — раньше она называлась Страницы (Pages), смысл тот же.

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

P.S.

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

Ссылки по теме

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

Источник

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

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

свои вопросы при открытии книги в списке полей знак плюс или

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

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

В этой статье

В этой книге содержатся данным не будут

Правила условного форматирования, примененные можно оперативнее обеспечивать

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

Если фамилия продавцов для	формирования сводной таблицы.
сохранил себе спасибо. подтягиваются формулами в: Зависит от исходника, таблицы. алфавитном порядке по развернуть или свернуть каждый раз выполнить языке) .Макет отчета таблицы, содержащий именованный сократить прокрутку, недоступна срезы, которые можно сохранены. к ячейкам в	вас актуальными справочными анализа деятельности сети Нажимаем «Далее». ЧтобыThe_Prist СВОД — это я с вамиПримечание: возрастанию. строк или названия операцию над своднойДля управления различные параметры, команда набор свойств, которые в Excel 97–2003, использовать для фильтрацииЧто это означает. свернутых строках и материалами на вашем магазинов не важна, собрать информацию в: Там сначала надо Дата/Продавец/Коробки/Штуки полностью согласен, но Этот параметр недоступен дляПримечание:
столбцов. Может потребоваться таблицы. Снимите флажок, сводной таблицы сНе повторять подписи элементов были изменены и и поля отображаются сводных таблиц и Некоторые сводную таблицу или столбцах, будут потеряны языке. Эта страница можем сформировать отчет	сводный отчет из обновить источники данныхсводная таблица служит если на листе источника данных OLAP. Этот параметр недоступен для скрыть знак плюс чтобы не сохранять помощью диалогового окна). внесите необходимые изменения в табличной форме. функций для работы данные сервера параметры в Excel 97–2003. переведена автоматически, поэтому с поквартальной прибылью. нескольких листов, выбираем: — запишите код
контролем ИТОГО по только таблица сСохранять элементы, удаленные из источника данных OLAP. или минус кнопок	макет сводной таблицыПараметры сводной таблицыКнига содержит сводную таблицу, в диалоговом окнеЧто необходимо сделать. с аналитическими кубами подключения, которые недоступныЧто необходимо сделать. ее текст можетЧтобы убрать результаты группировки, «в списке или
обновления источника макрорекордером каждому продавцу в данными по которой раздела источника данныхСортировка в порядке источника при печати сводной	и формат и. к которой примененПараметры поля В средстве проверки совместимости в книге. Срезы в Excel 97 В средстве проверки совместимости содержать неточности и необходимо щелкнуть по базе данных Microsoft и добавьте в СВОДе при этом построена сводная иЧисло элементов для возвращения данных таблицы или отобразить прибегать к макетуИмя замещающий текст. Замещающий. Щелкните правой кнопкой нажмите кнопку
не будут сохранены. – 2007, будут щелкните ссылку грамматические ошибки. Для ячейке с данными Excel». приведенный в статье. визуальным просмотром данных	нужно обновлять определенную одного поля Выберите, чтобы отсортировать поля сводную таблицу только по умолчанию и Отображает имя сводной таблицы. текст для сводных мыши любой элементНайти Формулы, ссылающиеся на потеряны. Найти нас важно, чтобы правой кнопкой мышиНа втором шаге определяемViper25 по дням… сводную на другом Чтобы указать количество элементов в списке полей для просмотра. формат каждый раз, Чтобы изменить имя, таблиц будет удален
именованный набор и, чтобы найти сводные срезы, будут возвращатьЧто необходимо сделать., чтобы обнаружить свернутые эта статья была и нажать разгруппировать. диапазон данных, на: The_Prist, в файлеДанные обновляются Дата/Продавец/Коробки/Штуки, листе… Не вижу	для каждого поля, сводной таблицы, вПоказывать контекстные всплывающие подсказки что при выполнении щелкните текстовое поле в версиях, предшествующих нажмите кнопку таблицы с полями ошибку #ИМЯ?. Проверьте, совместимы ли используемые строки или столбцы, вам полезна. Просим Либо выбрать данный основании которых будет две исходные таблицы. автоматом обновляются в причин, что бы чтобы временно кэша том порядке, определяемый Выберите для отображения подсказок, операции в сводную и измените имя. Excel 2007.
Параметры поля в сжатой форме,Что это означает. параметры сервера с содержащие правила условного вас уделить пару параметр в меню строиться отчет. ТакС помощью Power	СВОДе, а вот это не использовать с книгой, выберите во внешнем источнике значение, строку или таблицу.Разметка и форматЧто это означает.. На вкладке а затем измените Среды не поддерживаются в более ранними версиями форматирования, а затем секунд и сообщить, «Структура». как у нас Query их объединяю, сводную приходится обновлятьand_evg одно из следующих данных. столбец сведения отображалисьИтоги и фильтрыРаздел макета Замещающий текст не поддерживаетсяРазметка и печать их формат на более ранних версиях Excel, и внесите разверните их, прежде
помогла ли онаУ нас есть сводный стоит курсор в	и на основании вручную…: Тогда лучше повесить действий:Примечание: на значение поляРаздел общие итоги Объединить и выровнять по в Excel 97убедитесь в том, структурную или табличную Excel. Они не необходимые изменения (
чем сохранять книгу вам, с помощью отчет такого вида: таблице, диапазон обозначится этой модели данныхПоэтому и встал данный макрос наАвтоматическое Этот параметр недоступен для или данных. Снимите	Показывать общие итоги для центру ячейки с – 2007 и что флажок форму, сняв флажок могут быть отображеныРабота со сводными таблицами в формате файлов кнопок внизу страницы.Видны итоги по месяцам автоматически. строится сводная.В в вопрос.
обработчик активации листа Количество уникальных элементов источника данных OLAP. флажок, чтобы скрыть строк подписями не отображается вОтображать элементы разных уровнейОбъединить и выровнять по и будут утеряны., вкладка	более ранней версии Для удобства также (сделано «Группировкой») иНа третьем шаге Excel модуль листа вставил_Boroda_ Shurf для каждого поляПечать всплывающие подсказки, значение, Установите или снимите флажок, Выберите для объединения ячеек этих версиях Excel. в разных полях центру ячейки с

При обновлении подключений с

Проблемы, которые приводят к небольшой потере точности

Анализ	Excel.
приводим ссылку на по наименованиям товаров. предлагает выбрать, куда Ваш код для: Данные подтягиваются формулами: по умолчанию.	Развернуть или свернуть кнопки строку или столбец чтобы отобразить или для внешней строкиЧто необходимо сделать.не установлен (по подписями функциями OLAP, ссылающимися, группаЭта книга содержит именованные оригинал (на английском Сделаем отчет более поместить сводную таблицу. автообновления. в СВОД, следовательно,and_evgНет Печать при отображаются сведения отображались на скрыть столбце
и столбца элементов В окне средства проверки умолчанию), и установлен( на срезы, вместоДанные наборы, не связанные языке) . удобным для изучения.	Жмем «Готово» иВ листе1 меняю эти данные вносятся, Ну да, а Нет уникальных элементов для в сводной таблице значение поля или Общий итог таким образом, можно совместимости щелкните ссылку флажоксводной таблицы В группе ожидаемых результатов отображаются, кнопка со сводной таблицей.Средство проверки совместимости обнаружилоКак в сводной таблице открывается макет. данные. на другие листы, если на том каждого поля. Установите или снимите флажок,
данных.рядом с последнего выровнять по центруНайтиавтоматически упорядочивать и удаление Работа с значения ошибки #ИМЯ?.	Источник данных Эти именованные наборы одну или несколько сделать итоги сверху:Нужно обозначить поля дляПодскажите, как получить а для этого же, то добивать.Макс чтобы отобразить илиПоказать свойства во всплывающих столбца. элементы по горизонтали , чтобы обнаружить сводную повторений из набора, «Что необходимо сделать., команда не будут сохранены. проблем совместимости, связанных«Работа со сводными таблицами» отображения в отчете. код обновления источника внесения нужно наApplication.EnableEvents = False Максимальное число уникальных скрыть разворачивать и подсказкахПоказывать общие итоги для и вертикали. Снимите таблицу, содержащую замещающийВыбранные (значение поАнализ Вместо срезов используйте дляСвойства подключенияЧто это означает. в сводные таблицы. — «Конструктор». Допустим, мы хотим
данных и куда те листы перейти.Application.ScreenUpdating = False элементов для каждого сворачивать кнопок при Установите или снимите флажок, столбцов	флажок, чтобы левому текст. Чтобы вывести умолчанию).», группа « фильтрации данных фильтры). Именованные наборы, не связанныеВажно:На вкладке «Макет» нажимаем узнать суммы продаж его вписать, чтобы А потом перейти’код обновления поля. Вы можете
печати сводной таблицы. чтобы отобразить или Установите или снимите флажок, краю элементов в замещающий текст вПодключение в этой книге	Сводная таблица сводных таблиц.Сводная таблица в данной со сводной таблицей Прежде чем продолжить сохранение «Промежуточные итоги». Выбираем по каждому продавцу. автообновление сводной работало. на лист СВОД.Application.ScreenUpdating = True указать до 1 Этот флажок недоступен скрыть всплывающие подсказки, чтобы отобразить или внешних полей строк более ранней версии содержит один или», команда «К началу страницы книге содержит данные, в Excel 97 книги в формате «Показывать все промежуточные Ставим галочки –Работать со сводными таблицами Тогда спокойно вешаем
Application.EnableEvents = True 048 576 элементов. в вкладке отображать сведения о скрыть и столбцов в	Excel, можно скопировать несколько именованных наборовПараметрыПроблема представленные с помощью – 2007 исчезает. файла более ранней итоги в заголовке получаем: Excel приходится в макрос на событиепротупил, согласен. ХотяПримечание:экран свойствах для элемента.Общий итог верхней части группы его в пустую или вычисляемых элементов.», вкладкаРешение функции «Дополнительные вычисления».Что необходимо сделать. версии, необходимо устранить группы».Готовый отчет можно форматировать, разных сферах. Можно активации листа это в любом Этот параметр недоступен дляэто диалоговое окноПримечание:строки в нижней элементов. ячейку на листе Возможно, некоторые свойстваРазметка и форматКнига содержит сводную таблицу,
Эти настраиваемые выходные Чтобы избежать возникновения этойпроблемы, которые приводят кПолучается следующий вид отчета: изменять. быстро обрабатывать большиеPrivate Sub Worksheet_Activate() случае не будет	источника данных OLAP. снят флажок Этот параметр доступен только части сводной таблицы.Когда в сжатой форме или добавить его этих наборов или). к которой применен данные не будут проблемы, убедитесь в существенной потере функциональностиУже нет той перегруженности,** объемы информации, сравнивать,Me.PivotTables(1).PivotCache.Refresh** лишнимDgerri
Показывать кнопки детализации для источника данныхФильтры раздела отступ для названий в примечание. элементов не будут	Сводная таблица в этой стиль сводной таблицы. сохранены и будут том, что сводная, чтобы предотвратить безвозвратную которая затрудняла восприятие Это можно сделать вручную группировать данные. ЭтоEnd Sub_Boroda_: Здравствуйте уважаемые Гуру!. OLAP.Промежуточный итог фильтром элементам строкЩелкните правой кнопкой мыши сохранены. книге содержит параметры,
В более ранних заменены исходными значениями таблица создается с потерю данных или информации. и автоматически. значительно облегчает труд	Dgerri: Давайте поищем.Прошу помощи, возобновитьПовторение подписей строк наПоказывать названия полей и страницы Чтобы задать отступ в любом месте Что это означает. не существующие в версиях Excel стили из источника данных. использованием подключения. неправильную работу функций.Как удалить промежуточные итоги?Вручную: менеджеров, продавцов, руководителей,: вау…1. Если сводная в памяти, как каждой печатной странице раскрывающимся фильтра Установите или снимите флажок, строк в область сводной таблицы и Именованные наборы или вычисляемые
более ранних версиях сводных таблиц неЧто это означает.Книга содержит сводную таблицуПроблемы, которые приводят к Просто на вкладкеСтавим курсор в любом	маркетологов, социологов ина примере работает, на другом листе. заставить сводную таблицу Установите или снимите флажок, Установите или снимите флажок, чтобы включить или метки строк при выберите элементы обладают свойствами, Excel. Некоторые параметры поддерживаются. Функция не поддерживается в с включенным анализом несущественной потере точности макет выбираем «Не месте сводной таблице. т.д. осталось прописать макрос Вы в исходной автоматически обновлять в чтобы повторять подписи чтобы отобразить или исключить отфильтрованные отчета сводной таблицы вПараметры сводной таблицы которые не поддерживаются сводной таблицы неЧто это означает. Excel 97 – «что если». Неопубликованные, не всегда обязательно показывать промежуточные суммы»:
В результате становитсяСводные таблицы позволяют быстро на все остальные таблице вручную поменяли ней данные после	элементов текущей области скрыть подписи сводной элементы при вычислении компактный формат, выберите. На вкладке в Excel 2007 будут сохранены. Стили сводных таблиц, основанные 2007 и пользовательское изменения «что если» устранять перед сохранением:Получим отчет без дополнительных видна вкладка «Работа сформировать различные отчеты 7 листов или добавили 100 ввода дополнительных, новых метки строк на таблицы в верхней промежуточных итогов. уровень отступа отЗамещающий текст и Excel 97–2003.Что это означает. на темах, не значение, которые не будут потеряны в данные и функциональность сумм: со сводными таблицами». по одним ибудем пробовать

ячеек. У Вас

support.office.com

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

 данных… каждой странице печати части сводной таблицыПримечание: 0 до 127.в поле Возможно, эти свойства Некоторые параметры сводной таблицы доступны и не отображаются данные, которые предыдущих версиях Excel. не теряются, ноОгромные сводные таблицы, которыеВ меню «Данные» жмем тем же данным.Спасибки большое 100 раз обновиласьили я что сводной таблицы. и отфильтровать стрелок Источник данных OLAP долженОтобразить поля в области

Описание не будут сохранены. в Excel 97 отображаются в Excel вы ввели (например,

Что это означает. книга при ее составляются на основании на кнопку «Обновить» Кроме того, эти

Enka

сводная. Не лучше

 то путаю, такоеПечать заголовков раскрывающегося списка на поддерживать подзапросом выборки фильтра отчетавыделите замещающий текстЧто необходимо сделать. – 2007 не 97–2003.% от родительской строки Изменения анализа «что если», открытии в более «чужих» таблиц, периодически (или комбинацию клавиш отчеты можно гибко: Добрый день!

 ли повесить обновление не возможно? Установите или снимите флажок, заголовки строк и синтаксису выражений MDX. Выберите и нажмите сочетание Щелкните правой кнопкой поддерживаются. Эти параметрыЧто необходимо сделать.

 суммы не опубликованные на ранней версии Microsoft нуждаются в детализации. ALT+F5). настраивать, изменять, обновлятьПодскажите, пожалуйста, как сводной на активациюЗаранее спасибо чтобы включить или столбцов.Помечать итоги *вниз, затем вправо клавиш CTRL+C, чтобы мыши любой элемент будут потеряны. В средстве проверки совместимости, сервере, не отображаются

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

скопировать его.

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

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

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

 находится? В этомDgerri заголовков строк и чтобы включить или скрыть звездочка рядом сверху вниз, как к которой примененПараметры поля щелкните ссылку, чтобы найти сводныеилиЧто необходимо сделать.Проблемы, которые приводят к Excel. Но можно «Обновить все» (илиКаждая строка дает нам буду добавлять периодически случае могут быть

, что то в

столбцов элемента на

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

 Прежде чем открывать книгу существенной потере функциональности это выяснить, если комбинацию клавиш CTRL+ALT+F5). исчерпывающую информацию об новую информацию. Например нюансы — например, таком роде не

каждой печатной странице

 на и удалить «звездочка» показывает, видимые нему, перед выполнением текст не отображаетсяПараметры поля значений, чтобы найти сводные применен стиль сводной

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

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

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

 добавила данные за с данными ссылаютсяPrivate Sub Worksheet_Change(ByVal TargetПримечание:Показывать элементы без данных и, которые используются ВыделитеЧто это означает. изменения. параметрами, и внесите стиль сводной таблицы В средстве проверки совместимости изменения анализа «что

ПроблемаВ марте продано двуспальныхНа вкладке «Работа со продажи;

06.01.17, но в

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

 если» (

Решение

 кроватей на сумму сводными таблицами» (необходимокакого товара и на сводной таблице они сводной (такое, своегоSheets(1).PivotTables(«СводнаяТаблица1»).PivotCache.Refresh по-прежнему необходимо ввести Установите или снимите флажок, сумму не являютсяотображение поля в в Excel 97 стиль среза, неОпределенные кнопки полей сводной применить форматирование своднойНайтиРабота со сводными таблицамиКнига содержит сводную таблицу, 23 780 у.е. щелкнуть по отчету) какую сумму;

 не появились. Заранее рода, итеративное вычисление).End Sub значения в полях чтобы отобразить или только значения, которые области фильтра отчета – 2007 и поддерживаемый в более диаграммы в этой таблицы, который поддерживается, чтобы найти сводные

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

Номер листа(на которомсквозные верхней строки скрыть элементы строк, используются в вычислениях.

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

Александр файл. находятся данные сили которые содержат нетПримечание:

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

 в предыдущих версиях с данной суммой таблицы». Открывается мастер.Если это огромная сеть

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

 В окне средства проверки не будет сохранен. полей будут сохраненыСводная таблица в этой

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

 и название своднойв разделеПримечание: в том случае, еще одну строку

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

 галочку напротив пункта идут, то в данные руками «Умной равно нужно смотреть

подправьте под свои

Печатать заголовки

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

 кнопкой мыши поле,Средства OLAP при сохранении в

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

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

 потерян при сохранении

 Кнопки полей, не отображаемые предшествующих Excel 2007. выберите, формате более раннейНа новом листе откроется файла». размер таблицы станет делайте на основе2. Если сводная: Возможно, но макросом.лист OLAP. подзапросом выборки синтаксису полей фильтра отчета

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

Теперь каждый раз при ужасающим. Проанализировать данные умной таблицы. на этом листе.Если им (макросом)» диалогового окнаПоказывать элементы без данных выражений MDX. Введите или выберите

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

 работать только сводные

Без вычислений

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

 пример Вашего файла.. (На вкладке

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

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

 умную Таблицу добавлять Worksheet_Change, что приведет Желательно с реальным

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

Книга содержит пользовательские внедренные данных, на которой в режиме ограниченной

новый лист, выбрав сводной таблицы.

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

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

Параметры страницы которые содержат нет скрытые фильтром, когда

support.office.com

Автоматическое обновление сводной таблицы (Формулы/Formulas)

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

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

 как это устранить================
на произвольные) данныхПечатать на каждой страницеПримечание: вычисляет промежуточных и фильтра отчета в примечание. можно сделать, чтобы после сохранения ее более поздней версии, Внедренные пользовательских данных, например
сводная таблица не
более поздних версий таблицу помещается абсолютно и принятия каких-то
Создам отчет с помощью ?
Добавлено и реальной сводной. Этот параметр доступен только
общих итогов. Снимите.Щелкните правой кнопкой мыши
сохранить настраиваемый стиль в формате более
невозможно обновить в Power Pivot данных будет сохранена. сводной таблицы поддерживает
вся информация из
решения может понадобиться
мастера сводных таблиц.Vladimir ChebykinПока я писал, по этим данным.Данные для источника данных флажок, чтобы включить«Формат» в любом месте среза. В более ранней версии Excel

 Excel 97 – не поддерживается вЧто это означает. 1 048 576 того столбца, который группировка. Допустим, нам В новых версиях: не верно, добавив Вы уже иИ сразу вопросы:Раздел данных сводной таблицы OLAP. только элементы, отображаемые

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

 2003. Excel 97 – В Excel 2010 и уникальных элементов для мы добавляем в нужно увидеть итоги
Excel он почему-то новый столбец (как свой пост поправили, В файле одна Сохранить источник данных с
Отображение подписей элементов при при промежуточных и Установите этот флажок, выберите

 можно использовать для и отключить кнопки
Что необходимо сделать. 2007. Данные будут более поздних версий каждого поля, но отчет. за месяц или спрятано глубоко в и строку) он и сами все сводная таблица или файлом отсутствии полей в общих итогов в а затем введитеПараметры сводной таблицы фильтрации данных фильтры полей, чтобы настроить В средстве проверки совместимости потеряны. можно воссоздать мер в Excel 97В нашем примере –
квартал. настройках: появится в сводной поняли
несколько? Установите или снимите флажок, области значений вычислениях. текст, например «Недействительна»,. На вкладке сводных таблиц. отображение нужных кнопок
нажмите кнопку
Что необходимо сделать.
в сводной таблице, – 2003 поддерживается ВСЕ товары, ВСЕГруппировка по дате вВыберите «Файл»-«Параметры»-«Панель быстрого доступа».

 после обновления данных.ShurfНа листе со чтобы сохранить или Установите или снимите флажок,Примечание: который вы хотитеЗамещающий текстСводная таблица в этой (Найти Удалите данные Power Pivot подключенной к источнику

 только 32 500 даты, ВСЕ суммы сводной таблице Excel:В выпадающем списке левойThe_Prist: Так-то, тогда надо сводной одна сводная?

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

 будет использоваться в обработки (OLAP). Не поля. пользователю не нужны с данными. из» укажите «Все здесь я приводил какой таблицы строится несколько сводных, то в книге. когда нет полей не OLAP. ошибке. Снимите этот
выделите замещающий текст с повторяющимися подписями. Анализ

 создана в текущем более ранних версиях hands
удается отобразить своднуюЧто необходимо сделать. некоторые элементы. ОниТак как нам нужна
команды».
пример создания полностью

excelworld.ru

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

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

 группировка по дате,В левой колонке найдите обновляемой сводной: смарт таблице, то одну или все? Примечание:

Использовать настраиваемые списки при сообщение об ошибке. клавиш CTRL+C, чтобы таблицы эти подписиПоказать или скрыть в формате ExcelВ этой книге содержатся 97 – 2007.

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

Есть ли вЭтот параметр не должны Этот флажок только в сортировкеПоказывать пустые ячейки
скопировать его. будут потеряны., кнопка 97 – 2003 срезы, которые можноЧто необходимо сделать.Найти

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

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

 до Microsoft Office чтобы включить или а затем введите
Примечание: Повторяющиеся подписи не поддерживаются). сводной таблицы в сводных таблиц и нажмите кнопку таблицы, размер которыхНажимаем на стрелочку у
мыши. диаграмм». Нажмите на
после изменения/добавления данных. новых записей своднаяКак часто будутКроме того этот параметр Excel 2007.

planetaexcel.ru

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

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

Из выпавшего меню выбираем кнопку между колонками: Жать Обновить уже хоть и обновиться, меняться данные? недоступен для источникаПоказывать вычисленные компоненты с списки при Excel

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

который вы хотите можно оперативнее обеспечивать

– 2007 и книге содержит один открыть в Excel

с аналитическими кубами, чтобы найти сводные
Сохранение книги в будем корректировать количество
«Группировку». Откроется инструмент
«Добавить» чтобы инструмент

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

формате Excel 97 информации. вида: переместился в правую это очень удобно. добавит.

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

Александр L_Boroda_ том, где данные Установите или снимите флажок,

флажок, чтобы отобразить при сортировке большихАвтоматически изменять ширину столбцов языке. Эта страница таблицы в более свойства именованных наборовКнига содержит сводную таблицу в более ранних
отображается один и повторное создание сводной название поля. В и «По» Excel ОК.: А вот если: Это не совсем для нее? чтобы включить детализация или скрыть вычисляемые
объемов данных также при обновлении переведена автоматически, поэтому ранних версиях Excel. не будут сохранены. с полями в версиях Excel. тот же показатель
таблицы в режиме нашем примере – автоматически проставил начальнуюТеперь инструмент находится в у меня база
так. Если ей=============== подробных данных из элементы измерения. Этот может повысить производительность. Выберите, чтобы отрегулировать ее текст может

Что необходимо сделать.Что это означает.

сжатой форме. В

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

Что это означает. несколько и удалите

совместимости.

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

Срезы будут недоступны в все повторяющиеся меры,

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

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

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

Excel 97 – чтобы остается только книге содержит правила остановимся на названии. с шагом группировки. под рукой. в Excel то прекрасно будет добавляться посте выше написано,

на новый лист.Примечание:

Развернуть или свернуть кнопки размер широкой текст
нас важно, чтобыНайти свойств, отличные от изменится на табличную 2007, но останутся одну меру.
условного форматирования, которыеУстанавливаем фильтр по значению. Для нашего примера
Ставим курсор в любом что то не автоматически я бы использоватьПримечание: Этот параметр доступен только Показать или число. Снимите эта статья была, чтобы найти сводную

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

месте таблицы с срабатывает код, всеDgerri не стал - Этот параметр недоступен для

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

Работа с итогами

к данным в в свернутых строках

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

сводная будет обновляться источника данных OLAP.

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

этой книге содержат или столбцах. Чтобы кроватям – уберем

на месяцах. сводных таблиц, нажимаяВозможно этот код помощь…

на любое изменениеОбновление данных при открытии

Детализация информации

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

Получаем отчет, в котором на соответствующий инструмент, только для умнойВыкладываю конкретику… листа. Боюсь, что файлаДва варианта являются взаимоисключающими. чтобы развернуть илиСохранять форматирование ячеек при
помогла ли онаРабота со сводными таблицами Excel 97–2003.

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

четко видны суммы который теперь уже таблицы и несам файл содержит Excelю это не Установите или снимите флажок,

Сортировка от А до свернуть строк или обновлении вам, с помощью, вкладкаЧто необходимо сделать. формами), которая препятствует В более ранних версиях ранних версиях Excel. более ранних версиях

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

Excel, разверните эти таблица меняется.

exceltable.com

Поэкспериментируем и установим

Источник

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

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

Кнопка “Обновить все” на панели инструментов

Кнопка “Обновить все” – самый простой способ обновить все сводные таблицы в файле одновременно. Она находится на панели инструментов в разделе “Данные”:

Все что требуется сделать, это перейти во вкладку “Данные” => раздел “Запросы и подключения” = > “Обновить все”.

Автоматическое обновление всех сводных таблиц при открытии файла

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

Кликните правой кнопкой мыши на любой сводной таблице файла
В выпадающем меню выберите пункт “Параметры сводной таблицы”:

В появившемся диалоговом окне “Параметры сводной таблицы” перейдите на вкладку “Данные” и отметьте галочкой пункт “Обновить при открытии файла”:

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

Овладеть всеми навыками работы со сводными таблицами вы можете на моем курсе “Сводные таблицы в Excel“!

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

Вы можете в любой момент нажать кнопку Обновить, чтобы обновить данные в сводных таблицах в книге. Можно обновлять значения в сводных таблицах, подключенных к внешним данным, таким как базы данных (SQL Server, Oracle, Access и другие), куб служб Analysis Services, веб-каналы данных и многие другие источники. Вы также можете обновлять данные из исходной таблицы в той же или другой книге. Кроме того, можно настроить автоматическое обновление данных сводной таблицы при открытии книги.

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

Обновление вручную

Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.

На вкладке Анализ выберите команду Обновить или нажмите ALT+F5.

Совет: Чтобы одновременно обновить все сводные таблицы в книге, нажмите кнопку анализ > Обновить все.

Чтобы отменить обновление, нажмите Отменить обновление.

Блокировка изменения ширины столбцов и форматирования ячеек

На вкладке Анализ нажмите кнопку Параметры.

На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.

Автоматическое обновление данных при открытии книги

Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.

На вкладке Анализ нажмите кнопку Параметры.

На вкладке » данные » установите флажок Обновить данные при открытии файла .

Обновление вручную

Щелкните в любом месте сводной таблицы.

Откроется окно » работасо сводными таблицами» с дополнительными параметрами и вкладкой » конструктор «.

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

Чтобы обновить данные в соответствии с источником данных, нажмите кнопку Обновить или нажмите клавиши Alt + F5.

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

Чтобы обновить все сводные таблицы в книге, нажмите кнопку » Обновить «, а затем выберите команду » Обновить все«.

Если обновление занимает больше времени, чем ожидалось, нажмите кнопку параметры > Обновить > Обновить состояние , чтобы проверить состояние обновления.

Чтобы отменить обновление, нажмите Отменить обновление.

Блокировка изменения ширины столбцов и форматирования ячеек

Нажмите кнопку параметры > Параметры.

Автоматическое обновление данных сводной таблицы при открытии книги

Щелкните в любом месте сводной таблицы.

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

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

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

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

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

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

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

О целесообразности и возможности сводных таблиц

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

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

Требования к исходной таблице:

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

Нерациональная организация информации:

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

Лучше значения ввести следующим таким образом.

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

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

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

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

Когда данные внесены в исходную таблицу, переходим на лист со сводным отчетом и щелкаем в любом его месте правой кнопкой мыши. В открывшемся меню выбираем «Обновить».
Активизируем нужное поле сводного отчета – становится доступен инструмент «Работа со сводными таблицами». Открываем вкладку «Параметры». В группе «Данные» нажимаем кнопку «Обновить».
Выделить сводную таблицу или отдельное поле, нажать сочетание клавиш Alt + F5.

Как настроить автоматическое обновление сводной таблицы в Excel:

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

Закрыть окно, нажав кнопку ОК.

Еще один вариант:

Открыть лист со сводным отчетом. На вкладке «Разработчик» нажать кнопку «Запись макроса».
Выполнить вручную обновление сводной таблицы – остановить запись. Нажать на кнопку «Макросы». Выбрать из доступных макросов записанный – «выполнить».

Теперь сводный отчет при открытии будет обновляться программно.

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

Сводные таблицы и диаграммы не обновляются автоматически.

Если вы изменили исходные данные, сводную таблицу необходимо обновить:

1. Выделите ячейку внутри Сводной таблицы.

2. В разделе Работа со сводными таблицами на вкладке Параметры в группе Данные нажмите кнопку Обновить.

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

1. Выделите ячейку внутри Сводной таблицы.

2. В разделе Работа со сводными таблицами на вкладке Параметры в группе Данные нажмите кнопку Источник данных.

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

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

Более подробную информацию вы можете найти в книгах:

1. Иванов И.И. Microsoft Excel 2010 для квалифицированного пользователя. — СПб.: 2011. — С. 153.

2. Джелен Б., Александр М. Сводные таблицы в Microsoft Excel 2010.: Пер. с англ. – М.: 2011. – С. 58.

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

При необходимости Вы можете изменить оформление сводной таблицы. Для этого:

1. Выделите ячейку внутри Сводной таблицы.

2. В разделе Работа со сводными таблицами перейдите на вкладку Конструктор.

3. В группе Стили сводной таблицы выберите подходящий стиль оформления.

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

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

Более подробную информацию вы можете найти в книгах:

1. Иванов И.И. Microsoft Excel 2010 для квалифицированного пользователя. — СПб.: 2011. — С. 154.

2. Джелен Б., Александр М. Сводные таблицы в Microsoft Excel 2010.: Пер. с англ. – М.: 2011. – С. 66.

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

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

1. Выберите отчет сводной таблицы. На ленте появится панель Работа со сводными таблицами с дополнительными вкладками Параметры и Конструктор

2. На вкладке Параметры в группе Сервис нажмите кнопку Сводная диаграмма.

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

4. Нажмите кнопку ОК. На текущий лист будет добавлена сводная диаграмма.

Более подробную информацию вы можете найти в книгах:

1. Иванов И.И. Microsoft Excel 2010 для квалифицированного пользователя. — СПб.: 2011. — С. 155.

2. Джелен Б., Александр М. Сводные таблицы в Microsoft Excel 2010.: Пер. с англ. – М.: 2011. – С. 69.

93.79.221.197 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.

Отключите adBlock!
и обновите страницу (F5)
очень нужно

Автообновляемая сводная таблица

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

Правая кнопка мыши→Обновить (Refresh)

Данные (Data) →Обновить все (Refresh all) →Обновить (Refresh)

Однако, если в конец исходных данных добавить строку(или несколько), то с большой долей вероятности даже обновление сводной таблицы не поможет — добавленная строка не появится в сводной. И чтобы её увидеть необходимо будет изменить источник данных для сводной таблицы, включив новую строку в диапазон. Не очень удобно, не правда ли? Чтобы добиться расширения диапазона исходных данных автоматически вместе с добавлением туда данных, лучше позаботиться об этом до создания сводной таблицы.

Недоавтообновление
Почему «недо» — жать кнопку Обновить все же придется. Но не отчаивайтесь — читайте до конца и мы научимся обновлять все автоматом.
Для счастливых обладателей Excel 2007 и старше есть простой способ без лишних телодвижений. Это встроенный инструмент Таблица (Table) . Его еще иначе называют «умная таблица» и я тоже буду применять этот термин, чтобы не было путаницы.
Умная таблица — это специальный объект, который представляет собой правильную таблицу с заголовками, которая расширяется по мере добавления в неё данных. В ней много еще чего полезного, но нас интересует сейчас именно то, что она расширяется сама по мере добавления данных и что на её основе можно создать сводную таблицу. В нашем случае она будет играть роль динамического именованного диапазона(стандартный именованный диапазон не может быть источником данных для сводной таблицы, поэтому и приходится идти другими путями). Чтобы создать такую таблицу необходимо:

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

Работа с таблицами (Table tools)

Конструктор (Desigh)

Стили таблиц (Table styles)

Создать стиль таблицы (New table style. )

А дальше все как привыкли:

Выделить любую ячейку исходной таблицы(теперь уже «умной»)
Вкладка Вставка (Insert) -группа Таблица (Table) -Сводная таблица (PivotTable)
В диалоговом окне Создание сводной таблицы (Create PivotTable) в пункте Выбрать таблицу или диапазон (Select a table or range) в поле Таблица или диапазон (Range/Table) будет автоматически указан не адрес какого-то диапазона, а имя созданной умной таблицы:

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

На новый лист (New Worksheet)
На существующий лист (Existing Worksheet)

нажать OK

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

Если необходимо настроить на авторасширение уже созданную сводную , то порядок почти такой же, только сводную таблицу создавать не надо. Преобразуем исходные данные в умную таблицу, переходим на лист со сводной таблицей. Выделяем любую ячейку в сводной таблице, переходим на динамическую вкладку Работа со сводными таблицами (PivotTable Tools) —Параметры (Options) -группа кнопок Даныне (Data) —Источник данных (Change data Source) . В появившемся окне в поле Таблица или диапазон (Table/Range) указываем либо ссылку на всю умную таблицу, либо имя нашей умной таблицы(если знаете где его подсмотреть). На что здесь следует обратить внимание: если указывался диапазон, то если он указан верно — в поле вместо адреса ячеек будет отображено имя умной таблицы:

Если же после указания видите именно диапазон — значит что-то указано неверно или таблица не является умной(возможно, форматирование от умной таблицы, но сама умная таблица была удалена).

Полное автообновление
Для полного счастья можно подключить работу макросов. Что я хочу? Я хочу, чтобы как только я изменил/добавил данные в исходные данные — сводная тут же обновилась. Для этого надо сделать следующее:

убеждаемся, что макросы разрешены(Почему не работает макрос?, Что такое макрос и где его искать?)
перейти на лист исходных данных(в моем случае лист так и называется — Исходные данные)
жмем на ярлычке этого листа правой кнопкой мыши —Исходный текст (View code) :
вставляем туда следующий код:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) ‘проверяем — изменения внутри умной таблицы или нет If Not Intersect(Target, Target.Parent.ListObjects(1).Range) Is Nothing Then ‘если внутри таблицы, то обновляем сводную таблицу на листе «Автообновляемая сводная» Sheets(«Автообновляемая сводная»).PivotTables(1).RefreshTable ‘для всех сводных на листе ‘ Dim pt As PivotTable ‘ For Each pt In Sheets(«Автообновляемая сводная»).PivotTables ‘ pt.RefreshTable ‘ Next End If End Sub

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

Sheets(«Автообновляемая сводная») — здесь Автообновляемая сводная это имя листа, на котором расположена сводная таблица. Это очень важно. Если будет указано неверное имя листа — код выдаст ошибку и никакого обновления, конечно же, не произойдет
код сделан таким образом, что на листе исходных данных должна быть только одна умная таблица. Нет, их может быть несколько, но код будет ориентироваться исключительно на первую. И если вы плохо знакомы с принципами создания объектов — то лучше не рисковать. Хотя и здесь можно выйти из положения. Если вы знаете имя своей умной таблицы(его можно подсмотреть на вкладке Конструктор -группа Свойства), то можно изменить код так:
вместо строки
If Not Intersect(Target, Target.Parent.ListObjects( 1 ).Range) Is Nothing Then
записать такую
If Not Intersect(Target, Target.Parent.ListObjects( «Таблица1» ).Range) Is Nothing Then
где Таблица1 — имя вашей умной таблицы, на основании которой создана сводная.
тот же нюанс с листом самой сводной — код ориентирован так, что обновляет только первую сводную на листе Автообновляемая сводная . Здесь так же можно заменить цифру 1 на имя сводной(например .PivotTables(«СводнаяТаблица1»).RefreshTable ), либо же сделать обновление всех сводных. Для этого надо раскомментировать блок ‘для всех сводных на листе (убрать апострофы перед строками) и убрать строку с именем листа, чтобы получилось так:

Private Sub Worksheet_Change(ByVal Target As Range) ‘проверяем — изменения внутри умной таблицы или нет If Not Intersect(Target, Target.Parent.ListObjects(1).Range) Is Nothing Then ‘если внутри таблицы, то обновляем все сводные на листе «Автообновляемая сводная» Dim pt As PivotTable For Each pt In Sheets(«Автообновляемая сводная»).PivotTables pt.RefreshTable Next End If End Sub

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

P.S. Так же можно использовать и иной подход — вставить в модуль листа Автообновляемая сводная такой код:

Private Sub Worksheet_Activate() Me.PivotTables(1).RefreshTable End Sub

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

Скачать файл с автообновляемой сводной таблицей:

Tips_PT_AutoRefreshPT.xlsm (46,5 KiB, 1 388 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Здравствуйте! Я в Excel мало что понимаю, прошу просто помочь.Это вроде моя тема.Книга,в ней 2 листа. Надо сделать так чтобы при изменение(обновление) листа 1, в лист 2(АВТОМАТИЧЕСКИ) копировались данные из определённой колонки(ячейки) листа 1.

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

Добрый вечер. Попыталась использовать ваш код для автообновления сводной, но на листе исходных данных у меня уже есть иной код с именем
Private Sub Worksheet_Change(ByVal Target As Range), и макрос выдает ошибку:
Compile error:
Аmbiguous name detected:Worksheet_Chenge
Подскажите пожалуйста, как можно корректно изменить имя.

Спасибо,разобралась сама) Все отлично работает

Источник

Хитрости »

21 Август 2016 62095 просмотров

Автообновляемая сводная таблица

Правая кнопка мыши→Обновить(Refresh)

Данные(Data) →Обновить все(Refresh all) →Обновить(Refresh)

Недоавтообновление
Почему «недо» — жать кнопку Обновить все же придется…Но не отчаивайтесь — читайте до конца и мы научимся обновлять все автоматом.

Для счастливых обладателей Excel 2007 и старше есть простой способ без лишних телодвижений. Это встроенный инструмент Таблица(Table). Его еще иначе называют «умная таблица» и я тоже буду применять этот термин, чтобы не было путаницы.

Умная таблица

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

Работа с таблицами(Table tools)

Конструктор(Desigh)

Стили таблиц(Table styles)

Создать стиль таблицы(New table style…)

А дальше все как привыкли:

Выделить любую ячейку исходной таблицы(теперь уже «умной»)
Вкладка Вставка(Insert) -группа Таблица(Table) -Сводная таблица(PivotTable)
В диалоговом окне Создание сводной таблицы(Create PivotTable) в пункте Выбрать таблицу или диапазон(Select a table or range) в поле Таблица или диапазон(Range/Table) будет автоматически указан не адрес какого-то диапазона, а имя созданной умной таблицы:

Далее надо определить место размещения Сводной таблицы:
- На новый лист (New Worksheet)
- На существующий лист (Existing Worksheet)
нажать OK

Если необходимо

настроить на авторасширение уже созданную сводную

, то порядок почти такой же, только сводную таблицу создавать не надо. Преобразуем исходные данные в умную таблицу, переходим на лист со сводной таблицей. Выделяем любую ячейку в сводной таблице, переходим на динамическую вкладку Работа со сводными таблицами(PivotTable Tools) —Параметры(Options) -группа кнопок Даныне(Data) —Источник данных(Change data Source). В появившемся окне в поле Таблица или диапазон(Table/Range) указываем либо ссылку на всю умную таблицу, либо имя нашей умной таблицы(если знаете где его подсмотреть).

На что здесь следует обратить внимание:

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

Если же после указания видите именно диапазон — значит что-то указано неверно или таблица не является умной(возможно, форматирование от умной таблицы, но сама умная таблица была удалена).

Полное автообновление

Для полного счастья можно подключить работу макросов. Что я хочу? Я хочу, чтобы как только я изменил/добавил данные в исходные данные — сводная тут же обновилась. Для этого надо сделать следующее:

убеждаемся, что макросы разрешены(Почему не работает макрос?, Что такое макрос и где его искать?)
перейти на лист исходных данных(в моем случае лист так и называется — Исходные данные)
жмем на ярлычке этого листа правой кнопкой мыши —Исходный текст(View code):

вставляем туда следующий код:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    'проверяем - изменения внутри умной таблицы или нет
    If Not Intersect(Target, Target.Parent.ListObjects(1).Range) Is Nothing Then
        'если внутри таблицы, то обновляем сводную таблицу на листе "Автообновляемая сводная"
        Sheets("Автообновляемая сводная").PivotTables(1).RefreshTable
        'для всех сводных на листе
'        Dim pt As PivotTable
'        For Each pt In Sheets("Автообновляемая сводная").PivotTables
'            pt.RefreshTable
'        Next
    End If
End Sub

Сохраняем файл(это опционально :))

Sheets(«Автообновляемая сводная») — здесь Автообновляемая сводная это имя листа, на котором расположена сводная таблица. Это очень важно. Если будет указано неверное имя листа — код выдаст ошибку и никакого обновления, конечно же, не произойдет
код сделан таким образом, что на листе исходных данных должна быть только одна умная таблица. Нет, их может быть несколько, но код будет ориентироваться исключительно на первую. И если вы плохо знакомы с принципами создания объектов — то лучше не рисковать. Хотя и здесь можно выйти из положения. Если вы знаете имя своей умной таблицы(его можно подсмотреть на вкладке Конструктор -группа Свойства), то можно изменить код так:
вместо строки
If Not Intersect(Target, Target.Parent.ListObjects(1).Range) Is Nothing Then
записать такую
If Not Intersect(Target, Target.Parent.ListObjects(«Таблица1»).Range) Is Nothing Then
где Таблица1 — имя вашей умной таблицы, на основании которой создана сводная.
тот же нюанс с листом самой сводной — код ориентирован так, что обновляет только первую сводную на листе Автообновляемая сводная. Здесь так же можно заменить цифру 1 на имя сводной(например .PivotTables(«СводнаяТаблица1»).RefreshTable), либо же сделать обновление всех сводных. Для этого надо раскомментировать блок ‘для всех сводных на листе(убрать апострофы перед строками) и убрать строку с именем листа, чтобы получилось так:
```
Private Sub Worksheet_Change(ByVal Target As Range)
    'проверяем - изменения внутри умной таблицы или нет
    If Not Intersect(Target, Target.Parent.ListObjects(1).Range) Is Nothing Then
        'если внутри таблицы, то обновляем все сводные на листе "Автообновляемая сводная"
        Dim pt As PivotTable
        For Each pt In Sheets("Автообновляемая сводная").PivotTables
            pt.RefreshTable
        Next
    End If
End Sub
```
но этот вариант удобен лишь в том случае, если на данных одной умной таблицы созданы различные сводные таблицы для отображения некоей динамики в различных проекциях.

P.S. Так же можно использовать и иной подход — вставить в модуль листа Автообновляемая сводная такой код:

Private Sub Worksheet_Activate()
    Me.PivotTables(1).RefreshTable
End Sub

Скачать файл с автообновляемой сводной таблицей:

Tips_PT_AutoRefreshPT.xlsm (46,5 KiB, 3 041 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Видеоуроки

Поиск по меткам

Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Источник

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

31.07.2011 12313

Как задать автоматическое обновление отчета сводной таблицы?

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

В окне открытого листа выделите любую ячейку сводной таблицы.
Перейдите к вкладке «Параметры» и в группе «Сводная таблица» раскройте меню кнопки «Параметры».
В списке команд выберите пункт «Параметры» (рис. 5.66).

Рис. 5.66. Вкладка «Параметры». Меню кнопки «Параметры»

В окне «Параметры сводной таблицы» на вкладке «Данные» активируйте пункт «Обновить при открытии файла» (рис. 5.67).

Рис. 5.67. Окно «Параметры сводной таблицы». Вкладка «Данные». Пункт «Обновить при открытии файла»

Закройте окно кнопкой «ОК».

Источник

Совет 1. Автоматическое обновление сводных таблиц

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

Рис. 1. Включите опцию Обновить при открытии файла

Флажок Обновить при открытии файла следует устанавливать для каждой сводной таблицы отдельно.

Скачать заметку в формате Word или pdf, примеры в формате Excel (файл содержит код VBA).

Совет 2. Одновременное обновление всех сводных таблиц книги

Sub Refresh_All()

ThisWorkbook.RefreshAll

End Sub

Совет 3. Сортировка элементов данных в произвольном порядке

Рис. 2. Регионы отображаются в алфавитном порядке

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

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

Выделите копируемые данные сводной таблицы, щелкните правой кнопкой мыши и в контекстном меню выберите пункт Копировать (или наберите на клавиатуре Ctrl+C).
Щелкните правой кнопкой мыши в произвольном месте рабочего листа и в контекстном меню выберите команду Вставить (или наберите Ctrl+V).

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

Выделите всю сводную таблицу, щелкните правой кнопкой мыши и в контекстном меню выберите пункт Копировать. Если сводная не содержит область ФИЛЬТРЫ, то для выделения области сводной таблицы можно воспользоваться клавиатурным сокращением Ctrl+Shift+*.
Щелкните правой кнопкой мыши в произвольном месте листа и в контекстном меню выберите параметр Специальная вставка.
Выберите параметр Значения и щелкните ОК.

Совет 5. Заполнение пустых ячеек в полях СТРОКИ

Рис. 4. После применения команды Повторять все подписи элементов заполняются все пустые ячейки

Совет 6. Ранжирование числовых полей сводной таблицы

Рис. 5. Создайте сводную таблицу, в которой объем продаж в области ЗНАЧЕНИЯ выводится дважды

Рис. 6. Сортировка от максимальных значений к минимальным с помощью дополнительных вычислений

Рис. 7. Перед вами завершенный ранжированный отчет

Совет 7. Уменьшение размера отчета сводной таблицы

Совет 8. Создание автоматически развертываемого диапазона данных

Рис. 8. Преобразование исходных данных в таблицу

Совет 9. Сравнение обычных таблиц с помощью сводной таблицы

Рис. 9. Вам предстоит сравнить эти две таблицы

Рис. 10. На основе двух исходных таблиц создается одна результирующая

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

Совет 10. Автоматическая фильтрация сводной таблицы

Рис. 12. Трюк по использованию автофильтра в сводной таблице

Совет 11. Преобразование наборов данных, отображаемых в сводных таблицах

Рис. 13. Эту таблицу в матричном стиле следует преобразовать в табличный набор данных

Рис. 14. Результат конкатенации столбцов Рынок сбыта и Описание услуги

Рис. 15. Удалены столбцы Рынок сбыта и Описание услуги

Рис. 16. Сводная на основе нескольких диапазонов консолидации

Рис. 17. Исходный набор данных был транспонирован

Рис. 18. Этот набор данных идеально подходит для создания сводной таблицы (сравните с рис. 13)

Совет 12. Включение двух числовых форматов в сводную таблицу

Рис. 19. Эта таблица включает несколько типов данных для одного поля из области значений

Рис. 20. Каждому показателю можно присвоить только один числовой формат

Рис. 21. Примените пользовательский числовой формат, в котором любые числа, меньшие 1,5, форматируются как проценты

Полученный результат показан на рис. 22. Как видите, теперь каждый показатель отформатирован корректно. Конечно, приведенный в этом совете рецепт не универсален. Скорее, он указывает направление, в котором стоит экспериментировать.

Рис. 22. Два числовых формата в одном поле данных!

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

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

Рис. 23. Поместите данные в область строк

Щелкните правой кнопкой мыши на любом значении в области строк и в контекстном меню выберите параметр Группировать. В диалоговом окне Группирование (рис. 24) определите значения параметров, определяющих начало, конец и шаг частотного распределения. Щелкните ОК.

Рис. 24. В диалоговом окне Группирование настройте параметры частотного распределения

Если в сводную таблицу добавить поле Заказчик (рис. 25), получим частотное распределение транзакций заказчиков относительно размера заказов (в долларах).

Рис. 25. Теперь в вашем распоряжении оказалось распределение транзакций заказчиков в соответствии с размерами заказов (в долларах)

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

Рис. 26. Сводная диаграмма частотного распределения

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

Аналитикам часто приходится создавать различные отчеты сводных таблиц для каждого региона, рынка сбыта, менеджера и т.п. Выполнение этой задачи обычно подразумевает длительный процесс копирования сводной таблицы на новый лист и последующее изменение поля фильтра с учетом соответствующего региона и менеджера. Этот процесс выполняется вручную и повторяется для каждого вида анализа. Но вообще-то создание отдельных сводных таблиц можно поручить Excel. В результате применения параметра Отобразить страницы фильтра отчета автоматически создается отдельная сводная таблица для каждого элемента, находящегося в области полей фильтра. Для использования этой функции просто создайте сводную таблицу, включающую поле фильтра (рис. 27). Поместите курсор в любом месте сводной таблицы и на вкладке Анализ в группе команд Сводная таблица щелкните на раскрывающемся списке Параметры (рис. 28). Затем щелкните на кнопке Отобразить страницы фильтра отчета.

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

Рис. 28. Щелкните на кнопке Отобразить страницы фильтра отчета

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

Рис. 29. Диалоговое окно Отображение страниц фильтра отчета

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

Рис. 30. Отдельная сводная таблица для каждого рынка сбыта

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

В совете 14 мы воспользовались специальной опцией для разделения сводных таблиц по рынкам сбыта на разных листах рабочей книги. Если же вам нужно разделить исходные данные по разным рынкам сбыта в отдельных книгах, можно воспользоваться небольшим кодом VBA. Для начала поместите поле, на основе которого будет выполняться фильтрация, в область полей фильтра. Поместите поле Объем продаж в область значений (рис. 31). Предлагаемый код VBA поочередно выбирает каждый элемент ФИЛЬТРА и вызывает функцию Показать детали, создавая новый лист с данными. Затем этот лист сохраняется в новой рабочей книге

Рис. 31. Исходная сводная таблица

Код VBA.

Sub ExplodeTable()

Dim PvtItem As PivotItem

Dim PvtTable As PivotTable

Dim strfield As PivotField

‘Изменение переменных в соответствии со сценарием

ConststrFieldName = "Рынок сбыта" ‘<—Изменение имени поля

Const strTriggerRange = "A4" ‘<—Изменение диапазона триггера

‘Изменение названия сводной таблицы (при необходимости)

SetPvtTable = ActiveSheet.PivotTables("PivotTable1") ‘<—Изменение названия сводной

‘Циклический обход каждого элемента выделенного поля

For Each PvtItem In PvtTable.PivotFields(strFieldName).PivotItems

PvtTable.PivotFields(strFieldName).CurrentPage = PvtItem.Name

Range(strTriggerRange).ShowDetail = True

‘Присваивание имени временному листу

ActiveSheet.Name = "TempSheet"

‘Копирование данных в новую книгу и удаление временного листа

ActiveSheet.Cells.Copy

Workbooks.Add

ActiveSheet.Paste

Cells.EntireColumn.AutoFit

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs _

Filename:=ThisWorkbook.Path & "" & PvtItem.Name & ".xlsx"

ActiveWorkbook.Close

Sheets("Tempsheet").Delete

Application.DisplayAlerts = True

NextPvtItem

EndSub

Const strFieldName. Имя поля, используемого для разделения данных. Другими словами, это поле, которое помещается в область фильтра/страниц сводной таблицы.
Const strTriggerRange. Ячейка триггера, в котором хранится единственное число из области данных сводной таблицы. В нашем случае ячейкой триггера является А4 (см. рис. 31).

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

[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 14.

Источник

Содержание

Автоматическое обновление всех сводных таблиц при открытии файла
Горячие клавиши
Поддерживаемые форматы файлов и расширения
Минимальные системные требования
Как установить Excel 2010
Если проблема в скачивании:
Выбор способа вставки данных
Настройка способа обновления связей и работа с диспетчером связей
Создание сводной таблицы в Excel
Источник данных сводной таблицы Excel
Обновляем Microsoft Excel 2007 на компьютере
Обновление через интерфейс программы
Ручная загрузка исправлений
Более поздние версии Office
Office в Microsoft Store
Office 2013
Возможности Microsoft Excel 2019
Изменение исходных данных сводной таблицы
Переход на внешний источник данных

Автоматическое обновление всех сводных таблиц при открытии файла

Кликните правой кнопкой мыши на любой сводной таблице файла
В выпадающем меню выберите пункт “Параметры сводной таблицы”:

В появившемся диалоговом окне “Параметры сводной таблицы” перейдите на вкладку “Данные” и отметьте галочкой пункт “Обновить при открытии файла”:

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

Овладеть всеми навыками работы со сводными таблицами вы можете на моем курсе “Сводные таблицы в Excel“!

Горячие клавиши

ALT+N – Открытие вкладки “Вставка” и вставка сводных таблиц, диаграмм, надстроек, спарклайнов, рисунков, фигур, заголовков или надписей.
CTRL+L – Вывод диалогового окна “Создание таблицы”.
CTRL+SHIFT+звездочка (*) – Выбор текущей области вокруг активной ячейки или выбор всего отчета сводной таблицы.
CTRL+SHIFT+U – Развертывание или свертывание строки формул.
CTRL+END – Перемещение курсора в конец текста в строке формул.
CTRL+S – Сохранение книги
CTRL+C – Копирование
CTRL+V – Вставка
DELETE – Удаление содержимого ячеек
ALT+P – Переход на вкладку Разметка страницы
ALT+Л – Переход на вкладку Формула
CTRL+9 – Скрытие выделенных строк
CTRL+0 – Скрытие выделенных столбцов

Поддерживаемые форматы файлов и расширения

XLSX – Книга Excel
XLSM – Книга Excel с поддержкой макросов
XLSB – Двоичная книга Excel
XLTX – Шаблон
XLTM – Шаблон (код)
XLS – Книга Excel 97—2003
XLT – Шаблон Excel 97—2003
XLS – Формат двоичных файлов Excel 5.0/95 (BIFF5).
XML – Формат файлов XML-таблиц 2003 (XMLSS).
XML – Формат данных XML.
CSV – Формат с разделителями-запятыми
XLS – Форматы файлов Microsoft Excel

Минимальные системные требования

Системные требования для запуска и установки Microsoft Excel для операционных систем Windows

Программа	Процессор	ОЗУ	HDD
Microsoft Excel 2016	1 ГГц	от 1 ГБ	от 3 ГБ
Microsoft Excel 2013	1 ГГц	1 – 2 ГБ	от 3 ГБ
Microsoft Excel 2010	500 МГц	от 256 Мб	от 1.5 ГБ
Microsoft Excel 2007	500 МГц	от 256 Мб	от 1 ГБ
Microsoft Excel 2003	400 МГц	от 64 Мб	от 200 МБ

* Microsoft Excel корректно работает со всеми версиями Windows

Как установить Excel 2010

Запустите программу установки нажмите «Настройка»

Пометьте в списке не устанавливать все кроме Excel и средства office

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

Если проблема в скачивании:

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

Выбор способа вставки данных

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

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

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

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

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

Вставленные связи можно обновлять несколькими способами. Например, если на вставленном объекте кликнуть правой кнопкой мыши, то в меню будет кнопка “Обновить связь”, нажатие которой приведет к обновлению выбранной связи.

Можно задать параметры обновления сразу для всех объектов. Для этого нужно вызвать диспетчер связей документа. Проходим по пути “Файл”-“Сведения”-“Изменить связи с файлами” (данная команда доступна, когда в документе есть как минимум одна связь, а сам документ сохранен).

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

В этом же диспетчере можно разорвать связи, открыть или изменить источник.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обновляем Microsoft Excel 2007 на компьютере

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

Обновление через интерфейс программы

Данный метод все еще нормально функционирует в операционной системе Windows 7, но последующих версиях им пользоваться нельзя. Если же вы обладатель упомянутой выше ОС и хотите загрузить обновление к Excel 2007, сделать это можно так:

«Меню»

«Параметры Excel»

В разделе «Ресурсы» выберите пункт «Проверить наличие обновлений».

Дождитесь окончания сканирования и инсталляции, если это требуется.

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

Ручная загрузка исправлений

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

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

Кликните на соответствующую кнопку для начала загрузки.

Откройте автоматический инсталлятор.

Ознакомьтесь с лицензионным соглашением, подтвердите его и нажмите на «Продолжить».

Подождите, пока закончится обнаружение и установка.

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

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

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

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

— Не так давно мы думали, что электронные таблицы Excel были совершенны, но с развитием технологий, Microsoft Excel показал, что он может зделать программу лучше и удобнее, делая ее проще в использовании и более визуально приятной. Эта концепция входит в (2019) 16.0.11001.20074 году издание, благодаря которому работа с цифрами и хранением информации стала более простой и продуктивной.

Более поздние версии Office

1. Откройте любое приложение Office, например Word, и создайте новый документ.

2. Перейдите в раздел учетНая запись файла _гт_ (или учетная запись Office , если вы открыли Outlook).

3. В разделе Сведения о продукте выберите элементы Параметры обновления > Обновить.

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

4. После того как Office завершит поиск и установку обновлений, закройте окно с сообщением ” У вас все в порядке”.

В меню кнопки Параметры обновления также можно выбрать пункт Отключить обновления или Просмотреть обновления (ранее установленные).

Office в Microsoft Store

Если вы приобрели Оффицефром приложения Microsoft Store на устройстве с ОС Windows, вы можете обновить Office на одном и том же месте!

Закройте все приложения Office.

Откройте приложение Microsoft Store. Для этого введите в поле поиска на панели задач запрос “Microsoft Store” и нажмите клавишу ВВОД.

Щелкните значок и убедитесь, что вы вошли в учетную запись Майкрософт, связанную с вашей лицензией на Office.

Щелкните значок и выберите пункт Загрузки и обновления.

Нажмите кнопку Получить обновления.

Примечание: Если после нажатия кнопки Получить обновления появится сообщение “Вы полностью готовы”, значит, у вас уже установлены все последние обновления.

Office 2013

1. Откройте любое приложение Office 2013, например Word, и создайте документ.

2. Выберите Файл > Учетная запись (или Учетная запись Office в Outlook 2013).

3. В разделе Сведения о продукте нажмите кнопку Параметры обновления.

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

4. Нажмите Включить обновления, если этот параметр доступен.

5. Чтобы вручную проверить наличие обновлений Office и установить их, выберите вариант Обновить сейчас.

6. После того как Office завершит поиск и установку обновлений, закройте окно с сообщением ” У вас все в порядке”.

Возможности Microsoft Excel 2019

Поддержка Windows Server;
Тонкая настройка внешнего вида таблицы;
Наличие мессенджера Skype для бизнеса;
Отправка материалов по электронной почте;
Встроенное средство проверки орфографии;
Присутствуют новые формулы и диаграммы;
Хранение информации на облачном сервисе;
Позволяет работать со структурой базы данных;
Возможность автоматически настраивать сетку для таблиц;
Интеграция между приложениями Word, Excel, PowerPoint, Microsoft Access, OneNote, Microsoft Publisher, Project Visio Pro и т.д.

Преимущества

Наличие функций Morph и Zoom;
Открытие форматов SVG и PDF;
Встроенный календарь на 2019 год;
Общий доступ к заметкам и другим данным;
Поддержка дисплеев с высоким разрешением;
Дружественный интерфейс, приятное управление;
Качественный подход к безопасности личной информации;
Официальный сайт автоматически обновляет программное обеспечение;
Использование современных технологий анализа данных, имеющихся в документе;
Совместимость новой версии Excel и Office 2019 с Mac OS X и ОС Windows 10 (x86, x64).

Недостатки

Высокие системные требования;
Редкие сбои при импорте карточки контактов с мобильных устройств;
Больше функций в полной версии Microsoft Office 2019 Professional Plus.

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

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

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

Нажмите на сводную таблицу. НА ЛЕНТЕ появляются СВОБОДНЫЕ ИНСТРУМЕНТЫ .
Нажмите на вкладку АНАЛИЗ.
Нажмите Изменить источник данных в группе данных.

Нажмите на сводную таблицу. НА ЛЕНТЕ появляются СВОБОДНЫЕ ИНСТРУМЕНТЫ .

Нажмите на вкладку АНАЛИЗ.

Нажмите Изменить источник данных в группе данных.

Выберите Изменить источник данных в раскрывающемся списке.

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

Выберите таблицу или диапазон, который хотите включить в поле «Таблица / диапазон» в разделе «Выбор таблицы или диапазона». Нажмите ОК.

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

Переход на внешний источник данных

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

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

Нажмите на сводную таблицу.

Нажмите вкладку ANALYZE на ленте.

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

Нажмите кнопку « Выбрать соединение» .

Откроется диалоговое окно « Существующие подключения ».

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

Выберите Все соединения в поле Показать. Все Соединения в вашей Рабочей книге будут отображены.

Нажмите кнопку Обзор для более .

Откроется окно выбора источника данных .

Нажмите на кнопку «Новый источник».
Пройдите шаги мастера подключения к данным.

Если ваш источник данных находится в другой книге Excel, выполните следующие действия:

Нажмите на поле Имя файла.
Выберите имя файла книги.

Источники

https://excelhack.ru/kak-obnovit-neskolko-svodnyh-tablic-v-excel-faile/
https://microffice.net/excel.html
https://windowsfree.ru/office/excel-2010/
https://zen.yandex.ru/media/id/59affb7afd96b11e8eadd771/59c379a5a867312e013d1a8f
https://statanaliz.info/excel/svodnye-tablitsy/kak-v-excel-sdelat-svodnuyu-tablitsu/
https://k-skupka.ru/info/kak-obnovit-excel-do-poslednej-versii/
https://softcatalog.info/ru/programmy/microsoft-excel-2019
https://coderlessons.com/tutorials/bolshie-dannye-i-analitika/izuchite-svodnye-tablitsy-excel/svodnye-tablitsy-excel-obnovlenie-dannykh
https://my-excel.ru/voprosy/kak-obnovit-jeksel.html

Источник

О целесообразности и возможности сводных таблиц

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

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

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

Нужна дополнительная помощь?

Как в офисе.

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

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

Работа с итогами

Детализация информации

Видео

Лирическое вступление или мотивация

Если у вас Excel 2003 или старше

Шаг 1. Откуда данные и что надо на выходе?

Шаг 2. Выделите исходные данные, если нужно

Шаг 3. Куда поместить сводную таблицу?

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

Если у вас Excel 2007 или новее

P.S.

Ссылки по теме

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

В этой статье

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

Проблемы, которые приводят к небольшой потере точности

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

Автоматическое обновление сводной таблицы (Формулы/Formulas)

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

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

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

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

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

Работа с итогами

Детализация информации

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

Кнопка “Обновить все” на панели инструментов

Автоматическое обновление всех сводных таблиц при открытии файла

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

Обновление вручную

Блокировка изменения ширины столбцов и форматирования ячеек

Автоматическое обновление данных при открытии книги

Обновление вручную

Блокировка изменения ширины столбцов и форматирования ячеек

Автоматическое обновление данных сводной таблицы при открытии книги

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

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

О целесообразности и возможности сводных таблиц

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

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

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

Автообновляемая сводная таблица

Поиск по меткам

Автообновляемая сводная таблица

Поиск по меткам

Как задать автоматическое обновление отчета сводной таблицы?

Автоматическое обновление всех сводных таблиц при открытии файла

Горячие клавиши

Поддерживаемые форматы файлов и расширения

Минимальные системные требования

Как установить Excel 2010

Если проблема в скачивании:

Выбор способа вставки данных

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

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

Обновляем Microsoft Excel 2007 на компьютере

Обновление через интерфейс программы

Ручная загрузка исправлений

Более поздние версии Office

Office в Microsoft Store

Office 2013

Возможности Microsoft Excel 2019

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

Переход на внешний источник данных

А вот еще интересные статьи: