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

Обновлено: 21.11.2024

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

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

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

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

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

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

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

Этот подход работает визуально и создает централизованное расположение с ключевыми выходными данными. И поскольку мы связали названия, темпы роста и маржу (всегда делайте это!), таблицы должны совпадать. Однако оставление «живой таблицы», встроенной в лист расчета, имеет свои недостатки:

Что, если бы мы захотели добавить еще одну строку или столбец в таблицу расчетов в месте, которое повлияет на «живую таблицу» (т. е. мы хотим добавить новую строку между EBITDA и маржой в нашем примере)? Это вызовет ошибку («Невозможно изменить часть таблицы данных»). Чтобы решить эту проблему, нам нужно будет удалить данные в «живой таблице», добавить строку, воссоздать таблицу данных и повторно связать «фиктивную таблицу» на выходном листе. Это неэффективно и чревато ошибками или упущениями.
При таком подходе создатели моделей пытаются скрыть «живую таблицу», либо переместив ее подальше в правую часть модели, где ее никто не увидит, либо изменив весь текст на белый и удалив все границы. Эта практика опасна. Он подвержен непреднамеренному удалению (вы не можете защитить то, что не видите) и усложняет проверку модели.

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

Фальшивые ячейки ввода

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

Создайте пустые ячейки ввода. Создайте две «фиктивные входные ячейки» на выходном листе, на которые может ссылаться таблица данных. Обратите внимание, что мы четко помечаем их как фиктивные ячейки, которые должны оставаться пустыми. Если это модель с большим количеством пользователей, мы рекомендуем защитить эти ячейки, чтобы избежать случайного ввода данных ничего не подозревающими пользователями (защищенные диапазоны — это тема для другого информационного бюллетеня).
Вырежьте и переместите входную ячейку. В реальной модели существует высокая вероятность того, что несколько местоположений в модели будут ссылаться на одну входную ячейку. По этой причине вам следует вырезать и вставить старые входные ячейки в новое место, чтобы другие ссылки в модели продолжали ссылаться на эту ячейку.
Скопируйте и вставьте введенное значение обратно в исходное место. Мы по-прежнему хотим, чтобы пользователи вводили свои предположения о росте доходов и марже в столбце D, поэтому мы копируем и вставляем значения обратно в их исходное местоположение. Здесь пользователи будут вводить входное значение для будущей модели.
Добавьте логику if, чтобы разрешить таблице данных изменять входные данные. Замените содержимое ячеек в столбце E следующими формулами:Для роста доходов (ячейка E6):
=IF('Выходной лист'!H14="",'Расчетный лист' !D6,'Выходной лист'!H14)
Для маржи EBITDA (ячейка E9):
=IF('Выходной лист'!H15="",'Расчетный лист'!D9,'Выходной лист'! Н15)
Отформатируйте и защитите новые ячейки ввода. Как и в случае с «фиктивными входными данными» на листе вывода, мы рекомендуем пометить и/или защитить эти новые ячейки (E6 и E9 на листе расчета), чтобы избежать случайного ввода данных.
Подключите таблицу данных к выходному листу. Таблица данных на выходном листе теперь может быть подключена, как обычно, с ячейкой ввода строки, относящейся к ячейке ввода «фиктивного» дохода, и ячейкой ввода столбца, относящейся к ячейке ввода «фиктивной» маржи.

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

Если вы хотите выполнить это упражнение или посмотреть пример, перейдите по этой ссылке

Часто при использовании Alteryx или в любой форме отчетности нам может понадобиться вывести разные разделы данных или разные результаты в несколько мест. Теперь любой, кто имеет опыт работы с Alteryx, может знать, что вы можете использовать инструмент вывода по умолчанию для вывода идентичных листов, используя «Взять имя файла/таблицы из поля» и «Изменение имени файла/таблицы».

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

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

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

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

Рабочий процесс, который у нас получится, будет состоять из 12 инструментов в 8 разных частях и выглядеть следующим образом:

Наш окончательный рабочий процесс

После ввода наших данных нам нужно будет разделить их на 3 потока, о которых мы говорили ранее:

По клиентам
По категориям
В целом

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

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

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

Они должны быть настроены следующим образом:

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

Теперь наш рабочий процесс должен выглядеть следующим образом:

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

После этого нам нужно будет объединить две верхние таблицы, чтобы создать 30 отдельных таблиц, так как данные, которые я использовал здесь, относятся к 15 различным европейским странам, а теперь есть по 2 для каждой страны; 1 содержит информацию о клиенте, а другой — категорию. Объединив здесь две стороны, мы перемещаем их в один и тот же поток данных, что позволит нам ввести еще один инструмент отчетности; инструмент «Макет».

Инструмент макета здесь будет настроен следующим образом:

Несколько моментов, на которые стоит обратить внимание –

Во-первых, мы выбрали режим компоновки Каждая группа записей, чтобы он оставался горизонтальной линией для каждой страны. Если выбрана каждая отдельная запись, Alteryx будет отображать все записи, поэтому по 1 каждой для клиента. и Категория, и, следовательно, ничего не ставить рядом. Если использовать All Records Combined, все 30 записей будут сведены вместе, а это не то, что нам нужно.
Во-вторых, убедитесь, что для параметра "Страна/регион" выбран параметр "Группировать по", иначе группировка не будет выполняться ни по чему, и вы получите тот же результат, что и для каждой отдельной записи.
Наконец, как и раньше, остальные параметры являются только эстетическими, поэтому они не изменят наш результат в будущем, а только то, как он будет выглядеть; не стесняйтесь редактировать их по своему усмотрению.

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

Подходя к концу рабочего процесса, мы имеем следующее:

Теперь нам нужно сделать два небольших шага, чтобы заставить Alteryx выводить несколько таблиц на один лист Excel. Для начала мы снова представим еще один инструмент «Макет», хотя на этот раз он будет настроен на вертикальную компоновку; это так, как мы хотим, чтобы наши таблицы (в данном случае объекты, поскольку 2 таблицы, которые были объединены, теперь являются 1 объектом), были расположены друг над другом. Однако ключ к тому, что мы здесь делаем, заключается в том, что это будет вертикальная ориентация с разрывами секций, а не просто обычная вертикальная ориентация. Это создает следующие «разрывы» в нашем макете:

Для файлов .xls и .xlsx разрыв раздела эквивалентен новому листу в книге.
Для файлов .pdf, .doc, .docx и .rft разрыв раздела эквивалентен новой странице.
Для файлов .html и .pcxml разрыв раздела не создается.

Поскольку мы используем файл .xlsx, это создаст отдельную страницу для каждого объекта, в результате чего у нас останется 30 страниц (лист 1 — это две сводные таблицы для Австрии, лист 2 — наша отдельная таблица для Австрии и т. д.), если выбрано Каждая отдельная запись. Если вместо этого мы выберем группу по стране/региону, это сделает каждую страну/регион своей собственной, сохраняя все 3 наши таблицы вместе. Наконец, мы должны убедиться, что имя раздела совпадает с названием нашей группы, так как оно определяет имена листов; в противном случае они будут просто листом 1, листом 2 и т. д.

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

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

Сначала нам нужно выбрать режим вывода. Инструмент Render позволит нам создавать временные объекты (они полезны для аналитических приложений), однако в нашем случае мы хотим создать постоянный файл, поэтому мы должны выбрать определенный выходной файл. Здесь нам нужно выбрать путь для нашего файла, сохранив его в определенном месте.Здесь мы также выбираем тип нашего файла, мы хотим убедиться, что это .xlsx. Последний важный элемент, который нам нужно изменить, - это поле данных, которое нам нужно определить как наши данные, содержащиеся в нашем поле макета, разделитель здесь не имеет большого значения, поскольку наши записи уже разделены по разделам, поэтому, если бы мы не выполнили это действие раньше, мы могли бы определить его здесь.

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

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

Точно так же вы можете вывести несколько таблиц на несколько листов в рабочей книге Excel. Этот метод также будет работать для PDF-файлов, размещая каждый набор диаграмм на странице, однако нужно немного больше внимания уделять эстетике PDF-файлов, чем книгам Excel. Если вы хотите изменить внешний вид таблиц, которые мы создали здесь, вы можете использовать технику Бена Мосса, чтобы добавить макросы VBA в ваш рабочий процесс Alteryx здесь.

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

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

Прежде чем начать

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

Как разделить таблицу на отдельные файлы

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

Запустить мастер разделения таблиц

Откройте книгу и щелкните значок Разделить таблицу в группе Преобразование на вкладке Инструменты Ablebits:

Шаг 1. Выберите таблицу

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

На первом шаге вы также выбираете способ разделения таблицы:

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

Нажмите "Далее", чтобы продолжить.

Шаг 2. Выберите ключевые столбцы

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

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

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

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

Нажмите "Далее", чтобы продолжить.

Шаг 3. Выберите пункт назначения

Этот шаг позволяет выбрать, где разместить разделенные таблицы:

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

Нажмите "Далее".

Шаг 4. Выберите дополнительные параметры

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

Как назвать новые листы

Инструмент может называть новые листы двумя способами:

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

Как скопировать заголовки и сохранить форматирование

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

Нажмите "Готово" и дайте инструменту пару секунд на обработку данных и создание новых таблиц:

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

Ultimate Suite для Excel

Этот инструмент является частью пакета Ablebits Ultimate Suite, который включает более 70 профессиональных инструментов и более 300 решений для повседневных задач.

Работать с MS Excel достаточно сложно, так как с одним рабочим листом. Но когда вы работаете с несколькими листами, сложность работы с этими данными возрастает в десять раз с каждым добавленным листом.

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

1. Просмотр нескольких листов одновременно

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

a) Откройте книгу в Excel.

b) Нажмите кнопку "Новое окно" для каждого листа, который вы хотите просмотреть в этой книге.

c) В каждом новом окне нажимайте на вкладку листа, который хотите просмотреть.

d) Когда рабочие листы отобразятся, нажмите кнопку «Упорядочить все» в окне «Просмотр».

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

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

2. Просмотр дополнительных вкладок рабочего листа

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

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

3. Группировка листов вместе

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

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

b) Затем нажмите + CTRL (для несмежных вкладок листа) или нажмите + Shift (для смежных листов) на других вкладках листа, чтобы добавить его в группу.

c) При необходимости измените ячейку. Нажмите «Ввод».

Чтобы разгруппировать листы, щелкните правой кнопкой мыши и выберите параметр "Разгруппировать листы". Это может показаться простым советом, но он очень полезен!

4. Копирование формул между рабочими листами

Один из полезных советов по работе с данными на листах — копируйте формулы, требующие повторяющихся вычислений. Мы уже рассматривали этот совет ранее в нашей статье «Работайте с данными Excel как профессионал с помощью 9 простых советов», но это отличный совет, о котором стоит упомянуть еще раз:

a) Когда рабочий лист, содержащий формулу, которую вы хотите скопировать, открыт, нажмите CTRL + щелкните вкладку рабочего листа, в который вы хотите ее скопировать.

b) Нажмите или перейдите к ячейке с нужной формулой или данными (в открытом листе).

c) Нажмите F2, чтобы активировать ячейку.

d) Нажмите Enter. Это приведет к повторному вводу формулы или данных, а также к той же соответствующей ячейке на другом выбранном листе.

5. Использование формул для связывания листов

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

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

6. Объединение данных из нескольких листов

Если вам нужно объединить данные из разных листов, этот учебник от Tuts+ Computer Skills покажет вам, как шаг за шагом объединить данные из нескольких листов в одну сводную таблицу.

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

7. Импорт нескольких листов Excel из нескольких книг Excel

Следующий прием от ExcellsFun немного сложнее, но он полезен для переноса данных Excel из разных листов и книг на отдельный лист с помощью PowerQuery.

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

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