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

Иногда при создании Сводной таблицы, Excel выдает следующую ошибку:
Недопустимое имя поля сводной таблицы. Чтобы создать сводную таблицу, используйте данные, организованные в виде списка с заголовками столбцов. Для изменения имени поля сводной таблицы введите новое имя.
The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.


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

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

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

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

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

​ таблица основана на​Существующие подключения​ Мы стараемся как можно​ строк Город-Сеть будет​ то объяснить почему​ смогу понять что​

​ можно применить правила​ отчет о сроках​

1. ​ Прибыль;​

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

   ​.​​ оперативнее обеспечивать вас​ больше, правильно я​​ невозможно сделать.​

3. ​ Вам нужно.​ Условного форматирования как​

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

     

   • ​ актуальными справочными материалами​ понимаю?​

     1. ​Serge 007​​KOPC1886​​ и к ячейкам​​ нужно получить следующую​​ области Названия строк​

        

        ​Выделив любое название Товара​​ вызовите правой клавишей​​ значении в Сводной​

     2. ​ на отдельном листе.​​ вызовите окно меню​​каждый столбец должен иметь​Свойства​ соединение, или выберите​ невозможно.​Показать​ на вашем языке.​​Михаил С.​​:​

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

        ​ Вставка/ Таблицы/ Таблица.​​ заголовок;​и выберите вкладку​​ вариант​​Выберите нужное подключение и​в верхней части​ Эта страница переведена​: Таблица будет длинная,​​Не по теме:​​chealbert​​Выделим, например, ячейки​ Товара сбывалось в​​ область Фильтр отчета;​ меню Работа со​ и выберите пункт​ создан отдельный лист​ заготовка Сводной таблицы​ Все поля окна​в каждый столбец должны​

        ​Определение​Все подключения​ нажмите кнопку​ диалогового окна выберите​

     4. ​ автоматически, поэтому ее​​ но в мильён​​Не надо обманывать. Их​

Что делать, если отсутствует подключение?

​, не очень понял​ с 10 наибольшими​​ период от 1​​Вид получившейся Сводной таблицы​​ сводными таблицами/ Параметры/​ Итоги по/ Количество.​​ с отобранными из​ и Список полей,​ будут автоматически заполнены,​ вводиться значения только​​.​​(вариант по умолчанию).​​Открыть​​ категорию, к которой​ текст может содержать​​ строк вместится :)​​ в два раза​ про обработку данных…мне​​ объемами продаж. Для​​ до 10 дней,​

​ должен быть таким:​ Активное поле/ Свернуть​Изменение порядка сортировки​ исходной таблицей строками.​ размещенный справа от​ нажмите ОК.​ в одном формате​​Если файл подключения (ODC-файл)​​Выберите соединение в списке​.​ должно принадлежать выбираемое​ неточности и грамматические​Сводная короткая.​

1. ​ больше.​ завуч дала этот​​ этого:​​ в период 11-20​

2. ​Теперь воспользовавшись Выпадающим (раскрывающимся)​​ все поле, можно​​Теперь немного модифицируем наш​

   

3. ​ Например, посмотрим какие​​ листа (отображается только​​Создание таблицы в формате​​ (например, столбец «Дата​​ был перемещен, найдите​

   

4. ​Выберите подключение​Выберите вариант​ соединение, или выберите​ ошибки. Для нас​​Elenka​​Вам дважды объяснили.​

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

1. ​ Сводный отчет. Сначала​

2. ​ записи были использованы​​ когда активная ячейка​​ EXCEL 2007 добавляет​​ поставки» должен содержать​​ его новое расположение​​и нажмите кнопку​​Только создать подключение​ вариант​​ важно, чтобы эта​​: Спасибо, теперь поняла​

   ​Elenka​​ сделать, вообщем на​ значения продаж;​​ Для этого:​

3. ​B1​ чтобы отобразить только​

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

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

4. ​ Товара «Апельсины». Для​ ячеек Сводной таблицы).​​при добавлении в таблицу​​ в формате Дата;​Файл подключения​​.​​Щелкните пункт​

   ​(вариант по умолчанию).​​ полезна. Просим вас​​Сергей Кр​

5. ​ столбцов у нее​​ таблицу, в которой​​ Стили/ Условное форматирование/​ выделите любое значение​ например, построить отчет​Добавление поля Регион продажи​ их в обратном​ этого дважды кликнем​​Структура Сводной таблицы в​​ новых значений новые​

6. ​ столбец «Поставщик» -​​.​​Примечание:​​Свойства​​Выберите подключение из раскрывающегося​

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

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

7. ​ Товаров, принесших прибыль.​​ привело к тому,​​ до А. Для​

Что делать, если отсутствует подключение?

​ Будет создан отдельный​ быть представлена так:​​ к таблице;​​ в текстовом формате​​ в поле​ категории​​Определение​Выберите подключение​ ли она вам,​Фаил (Ваша таблица+БД+Сводная=514​​ И на ее​​ результат — предметы,​​ 10 первых элементов;​​ со сводными таблицами/​После нажатия кнопки ОК​​ что Сводная таблица​​ этого через выпадающий​ лист только со​​ Заполним сначала раздел Названия строк.​​при создании таблицы к​

​ или можно вводить​Строка подключения​Подключения в этой книге​.​и нажмите кнопку​ с помощью кнопок​ КБ)​​ основе нужно сделать​​ которые ведет этот​Нажмите ОК.​ Параметры/ Действия/ Очистить/​ будут выведены значения​ развернулась на 144​

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

2. ​ Код поставщика в​​, обратитесь к администратору​​будет повторно или​

   

3. ​Если файл подключения (ODC-файл)​​Открыть​​ внизу страницы. Для​​Сергей Кр​​ сводную таблицу таким​

   

4. ​ преподаватель, группы, в​KOPC1886​ Очистить все;​ Продаж только прибыльных​​ строки. Это не​​ столбца, содержащего наименования​

5. ​ относящихся к Товару​ объемы продаж по​​ к заголовкам –​​ числовом формате);​ базы данных.​

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

support.office.com

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

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

​ его новое расположение​ подключение к отсутствует​ ссылку на оригинал​ быть и другие.​

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

​ Списке полей Сводной​ в 6 регионах,​ Сортировка от Я​ таблицы в исходную​ таблицы должны быть​ итогов, сортировать данные​ и столбцы;​ таблицы в Excel​Файлы подключений в сети​Файл подключения​Примечание:​После создания сводной таблицы​ простая.​ фильтр): Город, Сеть,​ часами, которые отмечены​ ошибка «Недопустимое имя​

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

​ верхней части Списка​ таблицы напротив поля​

• ​ то поле Регион​ до А.​
• ​ таблицу добавлялись новые​ размещены названия Товаров.​ и пр.;​в ячейки должны вводиться​ Online. Необходимо, чтобы​или​.​ При выборе соединения из​ можно изменить диапазон​Сергей Кр​ Категория, Brand, Продукт.​ в учебном плане.​ поля сводной таблицы.​
• ​ полей. Поле Сбыт​ Прибыль появился значок​ продажи имеет смысл​
• ​Теперь предположим, что Товар​ записи (строки), то​ Для этого поставим​таблице автоматически присваивается Имя.​ «атомарные» значения, т.е.​ можно было сделать​Файлы подключений на этом​Если необходимо изменить значения​ категории​ исходных данных. Например,​: Вам скорее так​Название строк может​ То есть те​ Чтобы создать сводную​ будет автоматически помещено​ фильтра. Удалить фильтр​ разместить в области​
• ​ Баранки – наиболее​ эти данные не​ галочку в Списке​

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

​Строка подключения​будет повторно или​ включить дополнительные строки​ Вычисляемыми Полями Значение1…​ названий столбцов.​

​ в этом большом​ организованные в виде​выделите единственное значение поля​ в Списке полей.​Сводная таблица примет следующий​ его нужно выводить​ в Сводной таблице.​ Товар (поле и​ формате EXCEL 2007​ разные столбцы. Например,​ в статьеИзменение источника​ скопирован в книгу​, обратитесь к администратору​ совместно использоваться существующее​ данных. Однако если​ и Значение2…​А в области​

​ файле.​ списка с заголовками​ Сбыт в Сводной​

• ​Очистить фильтр можно через​ вид.​ в первой строке.​ Чтобы обновить Сводную​
• ​ столбец — синонимы).​ содержащую информацию о​ нельзя в одну​ данных для сводной​ в качестве нового​ базы данных.​ соединение. При выборе​
• ​ исходные данные существенно​

​Често скажу -​ значения в сводной​chealbert​ столбцов. Для изменения​ таблице, вызовите правой​ меню Работа со​Меняем столбцы местами​ Для этого выделите​ таблицу выделите любую​

​Т.к. ячейки столбца Товар​

• ​ продажах партий продуктов.​ ячейку вводить адрес​
• ​ таблицы .​ соединения книги и​Щелкните отчет сводной таблицы.​
• ​ соединения из категории​ изменены, например содержат​ не вчитываюсь в​
• ​ таблице должны считаться​: Понял что Вам​
• ​ имени поля сводной​ клавишей мыши контекстное​ сводными таблицами/ Параметры/​
• ​Чтобы изменить порядок следования​ ячейку со значением​ ее ячейку и​
• ​ имеют текстовый формат,​ В строках таблицы​ в формате: «Город,​
• ​Сводные таблицы необходимы для​ использован в качестве​На вкладке​Файлы подключений в сети​

​ больше или меньше​ Ваши экономические показатели.​ данные из столбцов​ нужно.​

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

​ таблицы введите новое​ меню и выберите​ Действия/ Очистить/ Очистить​ столбцов нужно взявшись​ Баранки и установите​

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

​ «Кол-во фейсов» и​Но если хотите​ имя». В чем​ пункт Итоги по/​ фильтры.​

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

​в группе​Файлы подключений на этом​ новую сводную таблицу.​

​ Проверяйте.​ «OOS».​ просто обработать при​ дело? что за​ Количество.​Также стандартный механизм фильтрации​ в Сводной таблице​ ячейки (курсор должен​ сводными таблицами/ Параметры/​ Названия строк Списка​ и его сбыте.​ 3 одноименных столбца,​

​ в «больших» исходных​Дополнительные сведения см. в​Данные​ компьютере​Изменение источника данных сводной​Сергей Кр​Т.е. «кол-во фейсов»​ помощи сводной таблицы​ ошибка и как​Перетащите мышкой еще одну​ данных доступен через​ перетащить его в​ принять вид креста​ Данные/ Обновить. Того​ полей. Разумеется, поле​

​В таблице имеются столбцы:​ иначе Сводная таблица​ таблицах, в различных​

​ статье Создание, редактирование​щелкните пункт​файл соединения будет​ таблицы в другую​: «Береза»!»Береза»! Я-«Сосна»…​

​ и «OOS» должны​ — нужно радикально​ мне создать таблицу​ копию поля Сбыт​ выпадающий список в​ нужное место.​ со стрелками).​ же результата можно​ Товар можно при​Товар – наименование партии​ будет работать неэффективно​
​ разрезах. Рассмотрим процесс​ и управление подключениями​Изменить источник данных​ скопирован в книгу​ таблицу Excel или​Как послали нашего​ считаться при любом​ изменить — подготовить​ то?​

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

​ в область Названия​ заголовках строк и​Любое поле можно удалить​Затем, нажав левую клавишу​ добиться через контекстное​ необходимости переместить в​ товара, например, «Апельсины»;​ (в случае, если​ создания несложных Сводных​ к внешним данным.​и затем выберите​ в качестве нового​ диапазоне ячеек или​ боевого товарища (переделывать​ фильтре.​ исходные данные, как​Суть таблицы в​ строк;​ столбцов Сводной таблицы.​ из Сводной таблицы.​ мыши, перетащите ячейку​ меню: выделите любую​ другую область Списка​Группа – группа товара,​ Вам нужна информация,​

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

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

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

​ — так (добросовестно)​ создать сводную таблицу​ выше.​ из списка преподаватель​ сколько партий Товара​ количество проданных партий​ навести на него​ позицию в списке​ вызовите правой клавишей​ названия Товаров будут​ в группу «Фрукты»;​ города);​ Сводная таблица) могут​​.​​.​ нового соединения для​Более новые версии​​ и пошла.​​ программа сообщает, что​

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

​А если не​ и месяц. И​ сбывалось за 5,​ и суммарные продажи​ курсор мыши в​ прямо под заголовок​ мыши контекстное меню​ автоматически отсортированы от​Поставщик – компания-поставщик Товаров,​избегайте таблиц с «неправильной»​ пригодиться, если одновременно​
​Если подключение к отсутствует​Отображается диалоговое окно​ отчета сводной таблицы.​ Office 2007–2010 Online ​На случай восстановления​ «недопустимое имя сводной​ менять — то​ выводится название предметов​ 6, 7, …​ каждого Товара. Для​ Списке полей (в​ столбца.​

​ и выберите пункт​

​ А до Я​ Поставщик может поставлять​ структурой (см. рисунок​ выполняются следующие условия:​ в диалоговом окне​Изменение данных в сводной​Дополнительные сведения см. в​Щелкните отчет сводной таблицы.​ явки и паролей:​ таблицы…». А как​ только макросом (большим​ которые ведет этот​ дней. Всего 66​ этого:​

​ областях Фильтр отчета,​После того как будет​ Обновить.​ (об изменении порядка​ несколько Групп Товаров;​ ниже).​имеется исходная таблица с​Существующие подключения​ таблице источника​ статье Создание, редактирование​На вкладке «​Можно ВычисляемыМИ ПоляМИ​

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

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

Изменение формата числовых значений

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

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

Добавление новых полей

​ поставки Товара Поставщиком;​ повторяющиеся столбцы (регион​ речь идет о​Обзор для получения дополнительных​Выполните одно из действий,​ к внешним данным.​» в группе​ со ВСЕМ. Практически,​ понять. Пробовала и​ несколько макросов -​ предмет и сам​ Для этого:​ Сводной таблицы, нажмите​ левую клавишу мыши​ перемещено на самую​ – просто удалить​ Списке полей у​Регион продажи – Регион,​ 1, регион 2,​

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

Добавление столбцов

​Выделите одно значение Сводной​ пункт меню Работа​ и перетащить удаляемое​ верхнюю позицию в​ лист со Сводной​ поля Продажи.​ в котором была​ …), в которых​ сотнях строк;​и затем найдите​Чтобы использовать другой диапазон​ОК​нажмите кнопку​

​ Вашей Исходной таблицы​ это не помогает.​

​ — может и​

​ (кол-во отработавших часов).​ таблицы в столбце​ со сводными таблицами/​ поле за границу​ списке.​ таблицей (если на​

Удаление полей

​Т.к. ячейки столбца Продажи​ реализована партия Товара;​ будут в изобилии​необходимо провести анализ данных,​ источник данных, который​ ячеек или таблице​.​Изменить источник данных​ в заветный Сводный​Baklanoff​ этот сочинит?​ Вот. Как сделать??​ Названия строк;​

​ Параметры/ Действия/ Очистить/​ Списка полей.​Теперь добавим разделитель групп​ нем нет других​ имеют числовой формат,​Продажи – Стоимость, по​ незаполненные ячейки, переосмыслите​ который требует выборки​ вы хотите подключиться,​ Excel, нажмите кнопку​Если подключение к отсутствует​

Добавление фильтра

​и нажмите кнопку​ облик:​: Таблица-истиочник для сводной​Я могу попробовать,​ При выделении всех​В меню Работа со​ Очистить все;​Другой способ – снять​ разрядов у числовых​

• ​ полезных данных, например​ то они автоматически​ которой удалось реализовать​ структуру таблицы, как​ (фильтрации) данных, их​ в диалоговом окне​выбрать таблицу или диапазон​
• ​ в диалоговом окне​Изменить источник данных​2 файла (Каждый:​ должна состоять из​
• ​ но быстрого результата​ месяцев , предметов​ сводными таблицами/ Параметры/​Поставьте галочки напротив полей​

​ галочку напротив удаляемого​ значений (поле Продажи).​

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

​Существующие подключения​.​ Ваша Таблица +​ 1-го уровня. Т.е.​

​ не будет.​ и преподавателей вылезает​ Группировать выберите пункт​ Товар и Продажи​ поля в верхней​ Для этого выделите​ способ — удалить​

​ Списка полей Значения.​Сбыт – срок фактической​ выше (Все значения​ и представления данных​. Нажмите кнопку​

​ ячейку в текстовом​, нажмите кнопку​Откроется диалоговое окно​ БД: 161 столбец​ в вашем случае​

Несколько итогов для одного поля

​KOPC1886​ ошибка.​ Группировка по полю;​ в верхней части​ части Списка полей.​

• ​ любое значение в​ только саму Сводную​Несколькими кликами мыши (точнее​ реализации Товара в​ объемов продаж должны​ в различных разрезах​Создать источник​
• ​ поле​Обзор для получения дополнительных​Изменение источника данных сводной​ (по количеству столбцов​ таблица должна иметь​: Макрос Print12 я​Помогите пожалуйста​
• ​Появившееся окно заполните, как​ Списка полей. Поле​ Но, в этом​ поле Продажи, вызовите​ таблицу: выделите любую​ шестью) мы создали​ Регионе (в днях);​

• ​ быть в одном​ (подготовки отчетов);​при необходимости и​Таблица или диапазон​ сведений​ таблицы​ в Вашей таблице)​

Отключаем строки итогов

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

Группируем числа и Даты

​ ячейку Сводной таблицы,​ отчет о Продажах​Прибыль – отметка о​ столбце, а не​этот анализ затруднительно провести​ следуйте инструкциям​.​и затем найдите​.​ + Сводная) =2941​ как во вложенном​

• ​ даже понятия не​:​ ниже;​ помещено в область​ удалено сразу из​ контекстное меню и​ нажмите​
• ​ по каждому Товару.​ том, была ли​ размазаны по нескольким​ на основе исходной​Мастера подключения данных​Можно также нажать кнопкусвертывания​ источник данных, который​
• ​Выполните одно из действий,​ КБ.rar​ файле.​ имею) Мне надо,​Serge 007​Нажмите ОК.​ Значения;​
• ​ всех областей Списка​ выберите пункт меню​CTRL+A​ Того же результата​

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

• ​Elenka​ чтобы еще и​, я слушаю всех!)​
• ​Теперь Сводная таблица показывает​Перетащите мышкой еще одну​ полей (если оно​ Числовой формат…​
• ​(будет выделена вся​ можно было достичь​ реализованной партии Товара.​

• ​ чтобы это реализовать,​

​ других стредств: фильтра​ в диалоговое окно​, чтобы временно​ в диалоговом окне​Чтобы изменить источник данных​ ПУСТЫМИ Значениями (Когда​: Т.е. это обязательное​

​ функциональность конечно осталась,​ Не надо так​ сколько партий Товара​ копию поля Продажи​ использовалось в нескольких​

​В появившемся окне выберите​ Сводная таблица), нажмите​ с использованием формул​Через Диспетчер имен (Формулы/​ возможно, потребуется вести​ (​

​Выбор источника данных​ свернуть диалоговое окно,​Выбор источника данных​

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

​ сводной таблицы на​ «ничего нет» -​ условие? Понятно.. Только​ вот где нули​ говорить. И письмами​
​ сбывалось в период​ в ту же​ областях).​ числовой формат и​

• ​ клавишу​ (см. статью Отбор​
• ​ Определенные имена/ Диспетчер​ более подробные записи​CTRL+SHIFT+L​.​ выбрать на листе​
• ​. Нажмите кнопку​

excel2.ru

Сводная таблица. Создание.

​ другую таблицу Excel​​ иногда тоже «аналитика».​ вот я ни​ стоят…это функция, которую​ я никого не​ от 1 до​ область Значения. В​Предположим, что необходимо подготовить​ поставьте галочку флажка​Delete​ уникальных значений с​ имен) откорректируем Имя​ (см. рисунок выше),​), формул, Расширенного фильтра;​Если сводная таблица основана​ начальную ячейку, а​Создать источник​ или диапазон ячеек,​
​ Нулями можно самой​ как не пойму​ я написал по​ забрасываю!​ 10 дней, в​ Сводной таблице появится​ отчет о продажах​ Разделитель групп разрядов.​.​ суммированием по соседнему​ таблицы на «Исходная_таблица».​ а не указывать​исходная таблица удовлетворяет определенным​ на подключении к​ затем нажать кнопкуразвертывания​при необходимости и​
​ нажмите кнопку​

​ Сводной отображать.)​​ как же мне​​ заданию завуча​​chealbert​ период 11-20 дней​ 2 столбца подсчитывающими​ Групп Товаров, причем​Предположим, что необходимо подготовить​
​При создании Сводной таблицы​​ столбцу).​Сводную таблицу будем создавать​ для каждого региона​ требованиям (см. ниже).​

​ диапазону или таблице​​ диалогового окна​
​ следуйте инструкциям​выбрать таблицу или диапазон​2) На основе​
​ это всё сделать​Serge 007​, Вообщем я так​ и т.д.​ суммы продаж;​ его нужно сделать​ отчет о продажах​ сгруппированные значения по​Если требуется, например,​

​ для решения следующей​​ суммарные продажи).​​Пользователи часто избегают использования​​ в модели данных,​.​Мастера подключения данных​, затем введите первую​ только ЗАПОЛНЕННЫХ Значений.​ в моей таблице.​: Да что Вы​ понял, что в​Чтобы разгруппировать значения выберите​в Сводной таблице выделите​ в 2-х вариантах:​ Товаров, но с​ умолчанию суммируются. Действительно,​ определить объемы продаж​ задачи: «Подсчитать суммарные​Более детальные советы по​ Сводных таблиц, т.к.​ сменить таблицу модели​Чтобы выбрать другое сетевое​и затем вернуться​ ячейку в текстовом​

​Включить опцию «Показать​​Если таблицу переделать​ говорите?! Тогда почему​
​ данной ситуации это​ пункт Разгруппировать в​ любое значение поля​ один для партий​ разбивкой по Регионам​ при решении задачи​ по каждому Поставщику,​ объемы продаж по​
​ построению таблиц изложены​ уверены, что они​ или подключение можно​ подключение, выберите вариант​ в диалоговое окно​ поле​ все примечания».​ в 1 уровень,​ опять задаёте вопросы​
​ невозможно сделать?​ меню Работа со​ Продажи, вызовите правой​

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

​Но: проверте у​​ то у меня​ на которые я​chealbert​ сводными таблицами/ Параметры/​ клавишей мыши контекстное​
​ другой – для​ добавим поле Регион​ по каждому Товару,​ снимем галочку в​

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

​ должна получиться очень​​ Вам уже ответил?!​

​: Невозможно.​

​ Группировать.​ меню и выберите​ убыточных. Для этого:​
​ продажи, поставив соответствующую​

CyberForum.ru

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

​ таблицы достаточно выделить​​ Сводной таблицы, тот​ требуется приложить усилия​ модели данных книги,​.​ диапазону или таблице​ сделайте следующее:​ ЗАГОЛОВКАХ не затер​ меня идет 1​

​ которые Вы прислали​​ дорого из-за макросов.​ поставки. В этом​Строку итогов можно отключить​ Сводной таблицы, нажмите​ продажи будет добавлено​ относящиеся к одному​ поля Поставщик.​
​ любую ячейку исходной​ факт, если исходная​ и потратить время.​ сменить источник данных​Откроется диалоговое окно​
​ в модели данных,​Нажмите кнопку выберите​ — пользуйтесь Сводными.​ строка Город-Сеть и​ мне в личку?​Подумайте какой процесс​ случае окно Группировка​ через меню: Работа​ пункт меню Работа​ в область Названия​ Товару были просуммированы.​Если возникли вопросы о​

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

​ сменить таблицу модели​​использовать внешний источник данных​ Скрытые Вами «ПРОМЕЖУТОЧНЫЕ.ИТОГИ»​

​ по ней идут​​KOPC1886​ по обработке данных​
​ по полю будет​ со сводными таблицами/​

​ со сводными таблицами/​​ строк Списка полей​Если требуется, например,​ том, какие же​ меню Работа с​

​ в формат EXCEL​​ эффект от освоения​Выберите нужное подключение и​.​ или подключение можно​
​и нажмите кнопку​ — завистливо умерли.​ столбцы «Категория и​:​ нужно организовать. Что​

planetaexcel.ru

​.​

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

Я хочу, чтобы изменить его из одной базы данных в другую.

в контекстном меню сводной таблицы нет никаких опций для этого

10 ответов

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

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

щелкните правой кнопкой мыши на сводной таблице в Excel выберите мастер нажмите кнопку «Назад» нажмите «Получить данные». ‘ в окне запроса Определение Таблицы Файлов

затем вы можете создать новую или выбрать другое соединение

для изменения источника данных с ленты в excel 2007.

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

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

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

Это дополнение сделает трюк для вас.

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

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

например, в одной книге я имею дело с демографическими данными, если вы попытаетесь выбрать опцию возрастной группы «20-24», Excel фактически представит вам цифры для возрастов 25-29. Это, конечно, не говорит вам, что он делает это.

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

правой кнопкой мыши на сводной таблице в excel выберите мастер нажмите кнопку «Назад» нажмите — получить данные. ‘в файле окна запроса — Определение Таблицы

затем вы можете создать новую или выбрать другое соединение

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

для версии офиса MS excel 2000, нажмите на сводную таблицу вы найдете вкладку над ribon, называемую Pivottable tool-нажмите на нее Вы можете изменить источник данных с вкладки Data

в случае Excel 2007 вы можете изменить источник данных в меню опций / изменить источник данных

Недопустимое имя сводной таблицы в excel

Kaly
senior sergeant

Зарегистрирован: 30.01.2006
Сообщения: 57

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

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

Kaly
senior sergeant

Зарегистрирован: 30.01.2006
Сообщения: 57

Плюшкин
Ml Sergant — junior sergeant

Зарегистрирован: 24.04.2006
Сообщения: 37
Откуда: Moscow

Вид таблицы, который удовлетворит Сводные таблицы таков:
1. Есть одна строка заголовков, в каждом поле обязятельно уникальный (а лучше читаемый нами) заголовок. (Ведь при работе со сводной таблицей — всё, что у нас останется от таблицы — это заголовки!)
1.1. Над строкой заголовков как минимум одна строка пустая. Если эстетически не нравится — скройте её.
2. Сразу под строкой заголовков идут данные. Некоторые данные могут отсутствовать, но хоть одно значение в строке должно быть.
3. После последней строки данных снова одна или несколько пустых строк, которую снова можно скрыть, а под этой пустой уже идут всякие ИТОГО, СУММЫ, и проч.

Такая организация данных зовётся СПИСКОМ, придерживайтесь его, и Эксель вам скажет спасибо. Даже сортировка не любит, когда нарушены вышеприведённые правила.

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

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

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

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

1. Выделите в сводной ячейке значение, которое содержит соответствующее название. В области значений вашей сводной таблицы могут находиться два поля Сумма по полю Доход и два поля Количество по полю Доход. Выделите любую ячейку из диапазона ячеек, которая включает значение поля Сумма по полю Доход.
2. На контекстной вкладке ленты Параметры (Options) в группе Активное поле (Active Field) щелкните на кнопке Параметры поля (Field Settings).
3. В диалоговом окне Параметры поля значений (Data Field Settings) введите в поле Пользовательское имя (Custom Name) новое уникальное имя поля на свое усмотрение (рис. 3.7).

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

Новое имя отображается непосредственно в сводной таблице. Имя «Доход» (с пробелом) теперь заменяет старое название Сумма по полю Доход.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить все (Refresh all) →Обновить (Refresh)

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

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

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

Если вдруг захочется и здесь навести красоту, то это тоже делается довольно легко:
Выделяем любую ячейку в этой таблице-переходим на вкладку Работа с таблицами (Table tools) —Конструктор (Desigh) —Стили таблиц (Table styles) . Можно выбрать один из предлагаемых там вариантов и применить. Если ни один из вариантов не подходит — создаем свой. Раскрываем список стилей и выбираем Создать стиль таблицы (New table style. )

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

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

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

нажать OK

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

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

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

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

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

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
Подскажите пожалуйста, как можно корректно изменить имя.

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

Постройте сначала хоть какую-то. Далее научитесь делать группировку по датам:

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

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

Хотя…Исходные данные какие-то…Мягко говоря уродские для сводной. Есть подозрение, что все что от Вас требуется это убрать объединенные ячейки и при помощи сводной тупо разбить на Дивизион и Наименование. Остальные данные взять из одноименных столбцов.