Что означает перекрытие отчетов сводных таблиц не допускается
Как использовать несколько сводных таблиц на одном листе без ошибки перекрытия?
У меня есть несколько сводных таблиц на одном листе. Поскольку каждый из них имеет зависимый размер из-за данных, это вызывает ошибку:
сводная таблица не может перекрывать другую сводную таблицу.
есть ли умный способ обойти это? К сожалению, мне нужно, чтобы они все были на одном листе.
3 ответов
Добро пожаловать в Superuser.
вы получаете эту ошибку, так как таблицы пытаются развернуться для отображения данных, удовлетворяющих критериям сводной таблицы. Таблицы должны расти, чтобы сделать это.
то, что вы просите, кажется, вопрос, заданный в течение нескольких лет на многих форумах. К сожалению, окончательного ответа нет. Кажется, что функция сводной таблицы не была разработана, чтобы хорошо играть с другими сводными таблицами на одном листе.
Если ваши сводные таблицы должны быть на одном листе, я делаю следующее предложение; поместите их бок о бок. Вам нужно будет иметь представление о том, сколько столбцов потребуется для каждой таблицы по мере ее расширения. После этого установьте зазор между таблицами которые могут учитывать расширение таблицы.
У меня есть этот же вопрос часто, поэтому я создал следующую работу вокруг. Во-первых, вставьте строки между вашими сводными таблицами, которые позволят им расширяться до максимума, в котором они вам нужны. 1) вставить столбец слева от таблицы.ex столбец A 2) Введите следующую формулу в 1-ю ячейку (A1) столбца. = if (B1<>«», 1, 2) 3) скопируйте и вставьте эту формулу в последнюю возможную ячейку столбца. Каждая ячейка должна содержать формулу в столбце A. Она должна пройти весь путь вниз последнюю строку сводной таблицы. 4) выберите строку 1 и вставьте пустую строку. 5) примените фильтр к каждому столбцу на листе (Data-Filter) при фильтрации столбца A можно выбрать 1 или 2. 1 покажет только строки, содержащие данные (ваши сводные таблицы) и 2 покажет все строки.
Что означает перекрытие отчетов сводных таблиц не допускается
Добрый вечер, участникам форума.
Сводными таблицами очень редко пользуюсь и настал этот час Х.
Освежил в памяти свои знания и даже сделал набросок. Но оказывается перекрытие сводных таблиц не допускается (не знал). Делать со смещением вправо не вариант, т.к. пытаюсь сделать компактный отчет на 1 листе со срезами.
Если нет вариантов обойти перекрытие сводных таблиц можно ли каким-то образом зашить информацию в одну сводную таблицу? Пример приложил.
Помнится как-то в старом виде можно делать сводную таблицу чтобы часть списка отображалась справа, но не помню как это делать.
Буду признателей за любой совет.
Добрый вечер, участникам форума.
Сводными таблицами очень редко пользуюсь и настал этот час Х.
Освежил в памяти свои знания и даже сделал набросок. Но оказывается перекрытие сводных таблиц не допускается (не знал). Делать со смещением вправо не вариант, т.к. пытаюсь сделать компактный отчет на 1 листе со срезами.
Если нет вариантов обойти перекрытие сводных таблиц можно ли каким-то образом зашить информацию в одну сводную таблицу? Пример приложил.
Помнится как-то в старом виде можно делать сводную таблицу чтобы часть списка отображалась справа, но не помню как это делать.
Буду признателей за любой совет. Anis625
Сообщение Добрый вечер, участникам форума.
Сводными таблицами очень редко пользуюсь и настал этот час Х.
Освежил в памяти свои знания и даже сделал набросок. Но оказывается перекрытие сводных таблиц не допускается (не знал). Делать со смещением вправо не вариант, т.к. пытаюсь сделать компактный отчет на 1 листе со срезами.
Если нет вариантов обойти перекрытие сводных таблиц можно ли каким-то образом зашить информацию в одну сводную таблицу? Пример приложил.
Помнится как-то в старом виде можно делать сводную таблицу чтобы часть списка отображалась справа, но не помню как это делать.
Archie Goodwin
Авторизация
Рубрики блога
Рекомендуем
Последние комментарии
Облако тегов
Устами великих
Реклама
Сводная таблица удобный инструмент для формирования динамических отчетов (многоразовых). Более того, гибкости таким отчетам добавляют срезы, которые можно добавлять к сводным.
Но минус в том, что когда отпускаешь все фильтры и сводная растягивается максимально по строкам, она не добавляет новые строки на листе экселя, а движется поверх них замещая все что там есть.
Это вообще не страшно, когда у вас всего одна сводная, но становится проблемой, когда вы на одном листе хотите разместить несколько сводных одна по другой.
Так вот, когда верхняя сводная попытается залезть на територию нижней (загрузлось больше данных или отпустили все фильтры), то excel выдаст ошибку что-то типа «запрещено перекрытие отчетов сводных», то есть две сводные не могут размещаться на одних и тех же ячейках.
Во вложении решение этой проблемы через макрос.
После первой сводной есть большой пропуск строк – запас в 1000 строк, которые используются как буфер. Макрос отслеживает любое изменение сводной на листе и мгновенно пересматривает кол-во используемых строк сводной в заданном диапазоне, после чего прячет неиспользуемые строки. Так визуально две сводные находятся рядом возле друг друга и при этом решается проблема того, что одна сводная может залезть на другую.
Сам макрос с максимально детализированными комментариями: Пример работы, напоминаю, можно посмотреть во вложении.
О сайте
Правда, помимо общих тематик, которые есть почти на каждом личном блоге, стоит выделить специализированные рубрики блога, которые будут полезны и интересны вебмастерам, программистам, дизайнерам, офисным работникам и пользователям ПК, желающим повысить свои навыки и уровень знаний. Подробнее о спецрубриках
Записки вебмастера – рубрика, которая призвана собрать коллекцию полезных скриптов и авторских решений, интересных особенностей и стандартов верстки, решение вопросов юзабилити и функционала, полезных ресурсов и программ.
Вопрос дизайна – это актуальные тренды, пошаговые и видео-уроки в фотошопе, необходимые плагины для фоторедакторов, векторные и PSD исходники, PNG иконки и GIF анимации, кириллические шрифты с засечками и без засечек, заливки (паттерны) и градиенты.
Мой ПК – каждая статья в этой рубрике направлена на то, чтобы узнать свой компьютер лучше. Здесь можно будет почитать о системных процессах и редактировании системного реестра, о способах защитить личные данные и компьютер в целом, о настройке локальной сети и подключениях к сети интернет, обзор ряда программ, которые делают работу за компьютером удобнее, быстрее и приятнее.
Прочие офисные программы – рубрика о программах для ведения учета (конфигурации, платформы, внешние отчеты для 1C), сдачи отчетности (MeDoc, БестЗвіт) и статистического анализа данных (SPSS), также здесь можно найти обзоры программного обеспечения для работы с периферийными устройствами. Свернуть
Независимая группировка сводных таблиц
Проблема
Если вы строите несколько сводных таблиц на основе одного источника, то, возможно, уже сталкивались с одним мелким, но неприятным моментом: Excel не позволяет независимо группировать данные в таких сводных.
Допустим, мы построили две отдельных сводных таблицы для анализа выручки и объемов продаж:
И наоборот, если попытаться аналогичным способом сгруппировать даты во второй сводной по кварталам, то перегруппируется и первая сводная, теряя текущую группировку по месяцам.
С одной стороны, это правильно: данные не дублируются, файл меньше весит и быстрее считается. С другой, мы имеем ту самую проблему с группировкой, изменение которой в одной из сводных влияет на все остальные. Что же делать?
Способ 1. Строим сводную правильно
Теперь можно строить сводную. Ставим активную ячейку в таблицу с данными или выделяем ее, жмем на добавленную кнопку и проходим последовательно шаги Мастера, выбрав на первом создание именно независимого отчета на собственном кэше:
Все, теперь группировка этой сводной не будет влиять на соседнюю и обе таблицы можно сгруппировать по-разному:
Способ 2. Отвязываем уже созданную сводную от общего кэша
Если у вас уже есть готовая сводная, и создавать ее заново с помощью Мастера вы не хотите, то можно отвязать ее от общего кэша, используя тактическую хитрость:
Такой бессмысленный, на первый взгляд, «финт ушами» заставляет Excel создать для сводной отдельную копию кэша, т.е. делает ее независимой.
Если поставить активную ячейку в сводную и запустить этот макрос с помощью кнопки Макросы на вкладке Разработчик или сочетания клавиш Alt+F8, то текущая сводная таблица будет отвязана от общего кэша, и вы сможете группировать ее независимо.
Финансы в Excel
Редактирование сводной таблицы
Сводные таблицы Excel является мощным средством анализа данных. При этом исходные данные могут храниться как в электронных таблицах, так и в базах данных. Одним из популярных замечаний к интерфейсу сводных таблиц, высказываемым пользователями Excel, является невозможность редактирования данных. Это замечание, с нашей точки зрения, некорректно, так как сводная таблица – это отчетная форма для анализа различных источников информации и разработка универсального интерфейса редактирования просто невозможна. Однако, в каждом конкретном случае, с помощью программных методов задача, в принципе, решаема. В примере к статье реализовано средство доступа к исходной информации сводной таблицы, основанной на простой таблице Excel.
Подключение макросов
Для работы примера требуется подключение макросов VBA. В Excel 2002-2003 может потребоваться предварительно изменить безопасность макросов до среднего или низкого уровня (Сервис \ Макросы \ Безопасность). В Excel 2007 щелкните на строку сообщения под лентой, а затем подтвердите операцию.
Без подключенных макросов пример будет работать в стандартном режиме отображения деталей (drill-down) при двойном клике в области данных сводной таблицы.
Функциональность
Программный код перехватывает двойной клик в области данных сводной таблицы и отображает исходные данные в виде отфильтрованных строк в отдельном окне. После редактирования и возврата в окно сводной таблицы, вторичное окно исходных данных автоматически закрывается, а данные в отчете обновляются.
В примере исходные данные размещены на листе «Data», а сводная таблица на листе «Отчет». В сводной таблице имеются 2 поля данных: «Объем», «Выручка» и вычисляемое поле «Средняя цена». Двойной клик доступен на обычных полях, при выборе вычисляемого поля никаких действий не производится, так как в этом случае, очевидно, нечего редактировать.
В новом окне редактирования выделяется ячейки столбца по выбранным в сводной таблице данным:
Обратите внимание, что в двух окнах находится одна и та же рабочая книга. Это видно по ярлыкам листов. Там же при первом открытии примера вы заметите появление нового листа «Filter». Этот лист используется для организации фильтра на листе исходных данных.
После редактирования, вернитесь на начальное окно со сводной таблицей. При этом служебное окно с исходными данными должно закрыться автоматически:
Программный код примера работает с любыми сводными таблицами, основанными на простых данных Excel. Для практического применения на реальном примере сводной таблицы несложно организовать редактирование в диалоговом окне вместо клонирования окна Excel.
Общий принцип поиска ключевой информации сводной таблицы может помочь создать более сложный интерфейс для выборки и редактирования информации из базы данных.
Алгоритм работы
Программный код
Небольшие комментарии к наиболее интересным частям программного кода VBA.
События рабочей книги (ThisWorkbook):
FlagCheckActiveWindow – публичное свойство для включения/отключения проверки активизации окна.