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

bekon33 Пользователь Сообщений: 19 Регистрация: 06.12.2017	Добрый день! При обновлении сводной таблицы возникает ошибка “Перекрытие сводных таблиц не допускается”. Ошибка понятная, если бы на листе было несколько сводных таблиц, но на листе только одна сводная таблица. Больше ничего на листе нет. Подскажите, пожалуйста, в чем причина, как исправить. Заранее благодарен!

bekon33 Пользователь Сообщений: 19 Регистрация: 06.12.2017	Нашел, в чем была проблема. На другом листе были сводные таблицы, которые пересекались при обновлении. Но, не совсем понятно, я обновлял лист, где размещена только одна таблица, а вместе с ней обновлялись все таблицы. Хотя я отжимал обновить, а не обновить Все. Но, теперь буду знать.

Дмитрий(The_Prist) Щербаков Пользователь Сообщений: 14249 Регистрация: 15.09.2012 Профессиональная разработка приложений для MS Office	#3 29.08.2022 12:50:08 Цитата написал: на листе только одна сводная таблица без примера можно только гадать, но по опыту: возможно, те другие таблицы построены на том же кеше, что и обновляемая сводная. И тогда по сути при нажать Обновить для одной сводной, будут обновлены все сводные, построенные на этом же кеше. Возможно, это Ваш случай. Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

1. НЕДОПУСТИМОЕ ИМЯ ПОЛЯ СВОДНОЙ ТАБЛИЦЫ.
Чтобы создать сводную таблицу, используйте данные, организованные в виде списка с заголовками столбцов.

РЕШЕНИЕ:
Уберите объединенные ячейки. Их в заголовках быть не должно. Заголовок, это одна строка, все ячейки которой заполнены уникальными значениями и среди которых нет пустых ячеек.

2. ПЕРЕКРЫТИЕ ОТЧЁТОВ СВОДНЫХ ТАБЛИЦ НЕ ДОПУСКАЕТСЯ

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

РЕШЕНИЕ:
Ошибка возникла из-за того, что таблицы расположены очень близко к друг другу.  Эту особенность необходимо заранее просчитывать, когда создаете несколько сводных таблиц на одном листе.
Эту особенность необходимо заранее просчитывать, когда создаете несколько сводных таблиц на одном листе.

3. ПРИ ОБНОВЛЕНИИ СЛЕТАЕТ УСЛОВНОЕ ФОРМАТИРОВАИЕ

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

РЕШЕНИЕ – см. фото ниже
После того, как вы выбрали условное форматирование, нужно щелкнуть по значку в правом нижнем углу выделение и выбрать нижний или средний переключатель.

4. НЕВОЗМОЖНО ДОБАВИТЬ ВЫЧИСЛЯЕМОЕ ПОЛЕ (ЭЛЕМЕНТ) В СВОДНУЮ ТАБЛИЦУ
Команды недоступны на ленте

5. Выделенные объекты нельзя объединить в группу

РЕШЕНИЕ 4-5:

Если у вас возникают эти ошибки, то при построении Сводной таблицы вы включли галочку ДОБАВИТЬ ЭТИ ДАННЫЕ В МОДЕЛЬ ДАННЫХ, если вы сделали это случайно, удалите сводную таблицу и постройте заново без включения галочки.

🎓 Всех желающих научиться правильно строить и анализировать данные с помощью сводных таблиц приглашаю на свой курс по СВОДНЫМ ТАБЛИЦАМ. Начать обучение можно будет уже на новогодних каникулах со 2 января!

🔴С 29 декабря я открываю продажи по НОВОГОДНИМ ЦЕНАМ на курс СВОДНЫЕ ТАБЛИЦЫ + ДАШБОРДЫ+ PIVOT, где вы изучите все новые возможности на практике.
Пишите в комментариях кто хочет пройти данный курс и получить ссылку на оплату по новогодней цене. Вы первыми получите ссылку на курс по самой выгодной цене. Количество мест по акции ограниченно!

#ExcelEliseenko

Обновлено: 20.05.2023

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

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

У меня есть рабочая книга с несколькими листами, и на одном конкретном листе есть несколько сводных таблиц. теперь эти развороты отслеживают объем по месяцам. поэтому ЕДИНСТВЕННЫЙ СПОСОБ «расти»/изменить размер — добавить новый столбец для нового месяца. Строки фильтруются, поэтому никогда не будут/должны меняться. кроме того, есть несколько рядов, разделяющих каждый стержень. Итак, на этом листе у меня есть набор опор слева и набор справа (примерно 10 столбцов между ними). я могу обновить все опорные точки с левой стороны без ошибок. но когда я иду, чтобы обновить тот, что слева, я получаю сообщение об ошибке «сводные таблицы не могут перекрывать другой отчет сводной таблицы». ОДНАКО, слева нет других сводных таблиц. на самом деле, я просто удалил 10 или около того столбцов слева каждой сводки, чтобы убедиться, что ничего не скрыто. нет скрытых строк или столбцов.
Странная находка: на обновляющихся сводках я могу включать и выключать промежуточные итоги. но те, которые дают мне эту ошибку никогда не показывать итоговую строку. по-прежнему нет скрытых столбцов/строк.

бонус: у меня есть еще один лист, содержащий ТОЛЬКО ОДНУ сводную таблицу. когда я щелкаю и обновляю, я получаю то же сообщение.
Редактировать: я только что изменил свой фильтр, чтобы добавить новый столбец, и он добавил без проблем. но все равно выдает ошибку при обновлении

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

Член

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

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

нет смысла

Член

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

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

Член

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

поэтому я просмотрел их все и обновил

Доктор. Пап

Новый участник

СэрJB7

Ронин в Excel

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

Вы когда-нибудь сталкивались с такой ситуацией, когда после обновления источника данных сводной таблицы при обновлении таблицы вы получаете сообщение об ошибке “Отчет сводной таблицы не может перекрывать другой отчет сводной таблицы”?

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

Если ваш ответ “Нет”, просмотрите этот пост, чтобы получить ответы на все эти вопросы.

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

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

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

“Отчет сводной таблицы не может перекрывать другой отчет сводной таблицы”.

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

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

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

Для извлечения данных из поврежденного файла Excel мы рекомендуем этот инструмент:

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

1. Загрузите средство восстановления файлов Excel с оценкой отлично от Softpedia, Softonic и CNET.
2. Выберите поврежденный файл Excel (XLS, XLSX) и нажмите «Восстановить», чтобы начать процесс восстановления.
3. Просмотрите восстановленные файлы и нажмите “Сохранить файл”, чтобы сохранить файлы в нужном месте.

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

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

Вот как это сделать:

• Нажмите в любом месте сводной таблицы, чтобы отобразить инструменты сводной таблицы на ленте Excel.
• Нажмите кнопку “Анализ”, а затем кнопку “Параметры”.

• На вкладке “Данные” на ленте Excel установите флажок “Обновлять данные при открытии файла”.

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

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

• Щелкните правой кнопкой мыши в любом месте сводной таблицы.
• Перейдите на вкладку “Макет и формат”.
• Снимите флажок “Автоподбор ширины столбца” на вкладке “Обновление”.
• Нажмите кнопку “ОК”.

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

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

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

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

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

• Нажмите в любом месте сводной таблицы.
• В группе данных перейдите на вкладку контекстного анализа. Выберите “Свойства подключения” в раскрывающемся списке “Изменить источник данных”.
• В открывшемся диалоговом окне выберите параметр «Обновлять каждые» в разделе «Управление обновлением». Справа нажмите на интервал в минутах.
• Нажмите кнопку “ОК”.
• Установите временной интервал в минутах.
• Такие настройки очень полезны для «внешних источников данных».

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

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

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

Этапы использования средства восстановления Excel:

Как определить, какая сводная таблица перекрывается?

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

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

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

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

Подведение итогов:

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

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

Маргрет

Маргрет Артур – предприниматель и эксперт по контент-маркетингу. Она ведет технические блоги и делится знаниями по MS Office, Excel и другим техническим дисциплинам. Ее самобытное искусство представления технической информации простым для понимания языком очень впечатляет. Когда она не пишет, она любит незапланированные путешествия.

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

Обновить сводную таблицу

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

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

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

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

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

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

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

Во-первых, код подсчитывает листы с 2 или более сводными таблицами.

• Если листы с 2 или более сводными таблицами не найдены, макрос завершается.

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

На этом новом листе макрос создает список сводных таблиц со следующей информацией:

• Название листа
• Количество сводных таблиц на листе
• Название сводной таблицы
• Количество столбцов и строк в сводной таблице
• Адрес сводной таблицы (с гиперссылкой для удобства проверки)
  • Гиперссылка работает, если лист виден

  Список сводных таблиц — макрос

  Вот код макроса ListWbPTsMulti. Скопируйте его отсюда или получите код из файла примера (ссылка в следующем разделе).

  Затем вставьте этот код макроса ListWbPTsMulti в обычный модуль кода в своей книге.

  Получить рабочую тетрадь

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

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

  Доска обсуждений для совместной работы над Qlik NPrinting.

  • Сообщество Qlik
  • :
  • Форумы
  • :
  • Аналитика данных
  • :
  • Qlik NPrinting
  • :
  • Обсуждения Qlik NPrinting
  • :
  • Перекрывающиеся сводные таблицы на листе Excel

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

  разочарован

  • Отметить как новое
  • Добавить в закладки
  • Подписаться
  • Отключить звук
  • Отправить сообщение другу

  У меня есть две сводные точки в Npritning, но при предварительном просмотре они перекрываются?

  Есть ли какое-либо решение проблемы, с которой я столкнулся?

  Лех_Мишкевич z

  • Отметить как новое
  • Добавить в закладки
  • Подписаться
  • Отключить звук
  • Отправить сообщение другу

  Возможно, вам следует задать этот вопрос на форуме Microsoft. Если вы имеете в виду собственные сводные таблицы Excel, то они ограничены функциями Excel, поэтому вопрос на самом деле не связан с NPrinting, верно?

  С другой стороны, если вы имеете в виду что-то другое, сообщите подробности, чтобы мы могли воспроизвести проблему с нашей стороны и дать вам более точный совет.

  приветствую, Лех! Если применимо, пожалуйста, отметьте правильные/соответствующие ответы как “решение” (вы можете отметить до 3 “решений”. Ставьте НРАВИТСЯ темам, если предложенное решение помогает решить проблему.

  разочарован

  • Отметить как новое
  • Добавить в закладки
  • Подписаться
  • Отключить звук
  • Отправить сообщение другу

  Я использую нативные сводные таблицы. И я не думаю, что у форума Microsoft есть возможность или способность понимать nprint, поскольку данные таблицы являются динамическими, а не фиксированным числом строк.

  Я бы определенно обратился к форуму Microsoft, если бы он содержал фиксированное количество строк и все еще перекрывался, чего не происходит.

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

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

  При расширении данные перекрываются.

  Предложите обходной путь. Благодарим вас за помощь.

  Лех_Мишкевич z

  • Отметить как новое
  • Добавить в закладки
  • Подписаться
  • Отключить звук
  • Отправить сообщение другу

  Это вопрос Mocrosoft, и я не согласен с утверждением, что это как-то связано с nprinting.
  Вы получите точно такую ​​же проблему, если создадите 2 сводные таблицы и не оставите между ними достаточно места для всех необходимых данных. Как только вы поместите больше записей и обновите сводные таблицы, они будут перекрываться, если делать это непосредственно в Excel.

  все, что вы делаете в nprinting, — это поиск данных, которые затем передаются в сводные таблицы.

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

  и еще раз – это стандартное поведение Excel (не печать).

  приветствую, Лех! Если применимо, пожалуйста, отметьте правильные/соответствующие ответы как “решение” (вы можете отметить до 3 “решений”. Ставьте НРАВИТСЯ темам, если предложенное решение помогает решить проблему.

  Лех_Мишкевич z

  • Отметить как новое
  • Добавить в закладки
  • Подписаться
  • Отключить звук
  • Отправить сообщение другу

  Подробнее о том, что это строго вопрос Microsoft.

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

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

  приветствую, Лех! Если применимо, пожалуйста, отметьте правильные/соответствующие ответы как “решение” (вы можете отметить до 3 “решений”. Ставьте НРАВИТСЯ темам, если предложенное решение помогает решить проблему.

  разочарован

  • Отметить как новое
  • Добавить в закладки
  • Подписаться
  • Отключить звук
  • Отправить сообщение другу

  Спасибо, Лех, за решение.

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

  Иногда 30, а иногда 50.

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

  Лех_Мишкевич z

  • Отметить как новое
  • Добавить в закладки
  • Подписаться
  • Отключить звук
  • Отправить сообщение другу

  Это очевидно, не так ли?

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

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

  Теперь я понимаю, что количество строк является динамическим и зависит от комбинаций значений в измерениях и мерах. Обходной путь для этого будет заключаться в создании объекта/таблицы в Qlik Sense/QlikView (независимо от того, что вы используете), который будет возвращать необходимое количество строк для эффективного заполнения данных ниже верхней сводной таблицы, что приведет к перемещению нижней сводной таблицы ( тег примечания в моем случае). Кроме того, вам нужно будет включить в сводных таблицах Excel параметр [ДАННЫЕ] –> [Обновлять данные при открытии файла], чтобы обновить сводную таблицу, чтобы на нее не влияли записи, заполненные ниже. Вы можете сделать эти дополнительные данные очень маленькими (скажем, размер шрифта 1) и использовать тот же цвет шрифта, что и цвет фона, чтобы они не влияли на конечный результат.

  приветствую, Лех! Если применимо, пожалуйста, отметьте правильные/соответствующие ответы как “решение” (вы можете отметить до 3 “решений”. Ставьте НРАВИТСЯ темам, если предложенное решение помогает решить проблему.

  Читайте также:

    

  • Настройки браузера iPhone

    

  • Excel вырезает текст между символами

    

  • Шаблон схемы Исикавы для построения в Word

    

  • Как проверить файл Excel на наличие ошибок

    

  • Программа голосового набора для компьютера

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

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

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

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

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

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

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

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

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

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

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

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

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

Sub Refresh_All()

ThisWorkbook.RefreshAll

End Sub

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

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

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

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

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

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

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

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

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

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

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

Совет 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): $# ##0; 0,0%

Рис. 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»

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

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs _

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

Sheets(«Tempsheet»).Delete

Application.DisplayAlerts = True

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

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

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

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

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

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

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

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

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

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

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

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



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



4. На третьем шаге Excel предлагает выбрать, куда поместить сводную таблицу. Жмем «Готово» и открывается макет.



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Способ 1: прямое связывание таблиц формулой

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

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

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

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

1. На первом листе выделяем первую ячейку столбца «Ставка». Ставим в ней знак «=». Далее кликаем по ярлычку «Лист 2», который размещается в левой части интерфейса Excel над строкой состояния.
2. Происходит перемещения во вторую область документа. Щелкаем по первой ячейке в столбце «Ставка». Затем кликаем по кнопке Enter на клавиатуре, чтобы произвести ввод данных в ячейку, в которой ранее установили знак «равно».
3. Затем происходит автоматический переход на первый лист. Как видим, в соответствующую ячейку подтягивается величина ставки первого сотрудника из второй таблицы. Установив курсор на ячейку, содержащую ставку, видим, что для вывода данных на экран применяется обычная формула. Но перед координатами ячейки, откуда выводятся данные, стоит выражение «Лист2!», которое указывает наименование области документа, где они расположены. Общая формула в нашем случае выглядит так:

   =Лист2!B2

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

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

5. Все данные из аналогичного столбца на Листе 2 были подтянуты в таблицу на Листе 1. При изменении данных на Листе 2 они автоматически будут изменяться и на первом.

Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ

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

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

   =ИНДЕКС(массив;номер_строки;)

   «Массив» — аргумент, содержащий адрес диапазона, из которого мы будем извлекать информацию по номеру указанной строки.

   «Номер строки» — аргумент, являющийся номером этой самой строчки. При этом важно знать, что номер строки следует указывать не относительно всего документа, а только относительно выделенного массива.

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

   Ставим курсор в поле «Массив». После этого переходим на Лист 2 и, зажав левую кнопку мыши, выделяем все содержимое столбца «Ставка».

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

   =ПОИСКПОЗ(искомое_значение;просматриваемый_массив;)

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

   «Просматриваемый массив» — аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его позиции. У нас эту роль будет исполнять адрес столбца «Имя» на Листе 2.

   «Тип сопоставления» — аргумент, являющийся необязательным, но, в отличие от предыдущего оператора, этот необязательный аргумент нам будет нужен. Он указывает на то, как будет сопоставлять оператор искомое значение с массивом. Этот аргумент может иметь одно из трех значений: -1; 0; 1. Для неупорядоченных массивов следует выбрать вариант «0». Именно данный вариант подойдет для нашего случая.

   Итак, приступим к заполнению полей окна аргументов. Ставим курсор в поле «Искомое значение», кликаем по первой ячейке столбца «Имя» на Листе 1.

8. После того, как координаты отобразились, устанавливаем курсор в поле «Просматриваемый массив» и переходим по ярлыку «Лист 2», который размещен внизу окна Excel над строкой состояния. Зажимаем левую кнопку мыши и выделяем курсором все ячейки столбца «Имя».
9. После того, как их координаты отобразились в поле «Просматриваемый массив», переходим к полю «Тип сопоставления» и с клавиатуры устанавливаем там число «0». После этого опять возвращаемся к полю «Просматриваемый массив». Дело в том, что мы будем выполнять копирование формулы, как мы это делали в предыдущем способе. Будет происходить смещение адресов, но вот координаты просматриваемого массива нам нужно закрепить. Он не должен смещаться. Выделяем координаты курсором и жмем на функциональную клавишу F4. Как видим, перед координатами появился знак доллара, что означает то, что ссылка из относительной превратилась в абсолютную. Затем жмем на кнопку «OK».
10. Результат выведен на экран в первую ячейку столбца «Ставка». Но перед тем, как производить копирование, нам нужно закрепить ещё одну область, а именно первый аргумент функции ИНДЕКС. Для этого выделяем элемент колонки, который содержит формулу, и перемещаемся в строку формул. Выделяем первый аргумент оператора ИНДЕКС (B2:B7) и щелкаем по кнопке F4. Как видим, знак доллара появился около выбранных координат. Щелкаем по клавише Enter. В целом формула приняла следующий вид:

    =ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))

11. Теперь можно произвести копирование с помощью маркера заполнения. Вызываем его тем же способом, о котором мы говорили ранее, и протягиваем до конца табличного диапазона.
12. Как видим, несмотря на то, что порядок строк у двух связанных таблиц не совпадает, тем не менее, все значения подтягиваются соответственно фамилиям работников. Этого удалось достичь благодаря применению сочетания операторов ИНДЕКС—ПОИСКПОЗ.

Функция ИНДЕКС в ЭкселеФункция ПОИСКПОЗ в Экселе

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

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

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

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

   =СУММ(число1;число2;…)

   Поля в окне соответствуют аргументам указанной функции. Хотя их число может достигать 255 штук, но для нашей цели достаточно будет всего одного. Ставим курсор в поле «Число1». Кликаем по ярлыку «Лист 2» над строкой состояния.

4. После того, как мы переместились в нужный раздел книги, выделяем столбец, который следует просуммировать. Делаем это курсором, зажав левую кнопку мыши. Как видим, координаты выделенной области тут же отображаются в поле окна аргументов. Затем щелкаем по кнопке «OK».
5. После этого мы автоматически перемещаемся на Лист 1. Как видим, общая сумма размера ставок работников уже отображается в соответствующем элементе.
6. Но это ещё не все. Как мы помним, зарплата вычисляется путем умножения величины ставки на коэффициент. Поэтому снова выделяем ячейку, в которой находится суммированная величина. После этого переходим к строке формул. Дописываем к имеющейся в ней формуле знак умножения (*), а затем щелкаем по элементу, в котором располагается показатель коэффициента. Для выполнения вычисления щелкаем по клавише Enter на клавиатуре. Как видим, программа рассчитала общую заработную плату по предприятию.
7. Возвращаемся на Лист 2 и изменяем размер ставки любого работника.
8. После этого опять перемещаемся на страницу с общей суммой. Как видим, из-за изменений в связанной таблице результат общей заработной платы был автоматически пересчитан.

Способ 4: специальная вставка

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

1. Выделяем значения, которые нужно будет «затянуть» в другую таблицу. В нашем случае это диапазон столбца «Ставка» на Листе 2. Кликаем по выделенному фрагменту правой кнопкой мыши. В открывшемся списке выбираем пункт «Копировать». Альтернативной комбинацией является сочетание клавиш Ctrl+C. После этого перемещаемся на Лист 1.
2. Переместившись в нужную нам область книги, выделяем ячейки, в которые нужно будет подтягивать значения. В нашем случае это столбец «Ставка». Щелкаем по выделенному фрагменту правой кнопкой мыши. В контекстном меню в блоке инструментов «Параметры вставки» щелкаем по пиктограмме «Вставить связь».

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

3. После этого открывается окно специальной вставки. Жмем на кнопку «Вставить связь» в нижнем левом углу ячейки.
4. Какой бы вариант вы не выбрали, значения из одного табличного массива будут вставлены в другой. При изменении данных в исходнике они также автоматически будут изменяться и во вставленном диапазоне.

Урок: Специальная вставка в Экселе

Способ 5: связь между таблицами в нескольких книгах

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

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

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

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

Разрыв связи между таблицами

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

Способ 1: разрыв связи между книгами

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

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

Способ 2: вставка значений

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

1. Выделяем диапазон, в котором желаем удалить связь с другой таблицей. Щелкаем по нему правой кнопкой мыши. В раскрывшемся меню выбираем пункт «Копировать». Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl+C.
2. Далее, не снимая выделения с того же фрагмента, опять кликаем по нему правой кнопкой мыши. На этот раз в списке действий щелкаем по иконке «Значения», которая размещена в группе инструментов «Параметры вставки».
3. После этого все ссылки в выделенном диапазоне будут заменены на статические значения.

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Помогла ли вам эта статья?

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

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

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

Создание связей между рабочими книгами

1. Открываем обе рабочие книги в Excel
2. В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+С)
3. Переходим в конечную книгу, щелкаем правой кнопкой мыши по ячейке, куда мы хотим поместить связь. Из выпадающего меню выбираем Специальная вставка
4. В появившемся диалоговом окне Специальная вставка выбираем Вставить связь.

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

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

Прежде чем создавать связи между таблицами

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

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

Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.

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

Обновление связей

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

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

Разорвать связи в книгах Excel

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

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

Если вы ещё не знакомы со сводными таблицами, то начните с этой статьи.

Создание модели данных

Создадим сводную таблицу на основе любой из имеющихся таблиц.

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

2. В появившейся панеле Поля сводной таблицы вы видите надпись ДРУГИЕ ТАБЛИЦЫ…

3. Нажмём её. Появится такой вопрос:

4. Отвечаем Да и видим, что в список полей добавились все наши таблицы:

5. Если вы начнёте выбирать поля, то через некоторое время в списке полей появится кнопка СОЗДАТЬ…

6. Нажмём её и создадим связи между нашими таблицами. Так создаётся связь между таблицей Orders и OrderLines. Обратите внимание, что Excel умеет создавать связь типа «один к одному» или «один ко многим». Причём первой надо указывать таблицу, где «много», в противном случае Excel ругается и предлагает поменять их местами.

7. Аналогично создаём другие связи.

8. В диалоговое окно Управление связями можно попасть через ленту АНАЛИЗ команда Отношения

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

10. Результат будет таким:

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

Просто и удобно!

>
Финансы в Excel

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

Подробности Создано 06 Май 2014

Вложения:

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

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

1. Фиксированный диапазон
2. «Умная таблица» Excel 2007
3. Динамический именованный диапазон

Фиксированный диапазон

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

В зависимости от практической задачи может потребоваться добавить новые данные в источник сводного отчета. Обычно добавляются новые строки. Это может происходить как при ручном вводе или копировании, так и при автоматизированном получении данных из внешних систем. После простого добавления данных вниз исходного диапазона Excel не включит эту новую строку в источник сводной таблицы. Обновление отчета не приведет к изменениям. Действительно, сводная таблица основана на заранее определенном фиксированном диапазоне данных, не включающих новые строки. Пример такой ситуации показан в файле-примере на листах Data и ReportData – строки 11 и 12 в отчет не попали:

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

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

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

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

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

Начиная с версии Excel 2007, в интерфейсе рабочего листа стала доступна работа с так называемой «умной таблицей» (лента Вставка Таблица). Такая таблица имеет расширенные возможности по дизайну, собственную нотацию формул и может быть опубликована на сервере SharePoint для совместного доступа нескольким пользователям. В нашем случае важно, что «умная таблица» Excel может быть указана в качестве источника данных сводного отчета – можно указать ее по имени или выделить мышью (листы Table, ReportTable):

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

Динамический именованный диапазон

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

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

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

Пример такого использования имени с формулой на листах Name и ReportName.

Формула имени source выглядит следующим образом:

=OFFSET(Name!$A$1;0;0;COUNT(Name!$A:$A)+1;COUNTIF(Name!$1:$1;»*»))

Функции COUNT() и COUNTIF() используются для подсчета непустых значений в диапазоне, COUNT() предназначена для числовых ячеек, COUNTIF() – для текстовых. В формуле вычисляется количество непустых строк для поля типа дата (столбец A) и количество столбцов по заголовкам полей (строка 1). К количеству столбцов добавляется единица, так как в диапазон входит также строка заголовков. Результирующая функция OFFSET() выдает прямоугольный диапазон с относительным смещением от левого верхнего угла рабочего листа (Name!$A$1). Если количество полей исходных данных не меняется, то вместо последнего параметра функции лучше явно указать это число:

=OFFSET(Name!$A$1;0;0;COUNT(Name!$A:$A)+1;4)

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

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

Смотри также

» Обработка больших объемов данных. Часть 3. Сводные таблицы

Третья статья, посвященная обработке больших объемов данных с помощью Excel, описывает преимущества использования сводных таблиц….

» Сводная таблица Excelfin.ru

Надстройка предназначена для создания сводных таблиц на основе нескольких диапазонов данных файла Excel. Пользовательский интерфейс в…

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

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

» Сводный отчет на основе нескольких таблиц Excel

В стандартном режиме Excel позволяет строить сводные отчеты на основе диапазона ячеек, расположенного на одном рабочем листе. Собрать…

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

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