Как разделить таблицу в экселе

Обновлено: 07.07.2024

Как разделить ячейку в Экселе? Жмите на необходимую секцию, перейдите в раздел «Работа с таблицами» и в пункте «Макет» в группе «Объединение» жмите на «Разделить ячейки». Выберите число столбцов / строк для разделения по вертикали / горизонтали соответственно. Ниже подробно рассмотрим, как правильно выполнять деление в Excel. Разберем наиболее удобные варианты, которые пользуются наибольшим спросом у пользователей программы.

Как разбить

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

Ячейку

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

Жмите на нужную секцию, которую необходимо разделить в Экселе.
Перейдите в раздел «Работа с таблицами».
Войдите во вкладку «Макет».
В группе «Объединение» кликните «Разделить …».
Выберите в поле Число столбцов / строк для деления по вертикали / горизонтали соответственно.
Если нужно разделить участок в Экселе одновременно по вертикали и горизонтали, введите требуемое значение в графы с количеством столбцов / строк.

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

Перед тем, как разбить ячейки в Excel, убедитесь, что «Мгновенное заполнение» включено. Для этого войдите в «Файл». Здесь найдите пункт «Параметры», а далее «Дополнительно» и «Автоматически выполнять мгновенное …».
Вставьте новый столбец рядом со столбцом, содержащим исходные данные.
Введите желаемую часть текста в 1-ю ячейку.
Введите данные еще в пару ячеек.
Дождитесь, пока Эксель поймет, по какому шаблону вводятся данные. В дальнейшем он предложит таким методом заполнить остальные.
Если вам подходит предложенный вариант, жмите «Ввод».

Если система не предлагает разделить ячейку в Экселе на две, запустите инструмент вручную. Для этого войдите во вкладку «Данные» или жмите на комбинацию Ctrl+E.

Способ №3 (по диагонали):

На вкладке «Шрифт» определите применяемый шрифт и размер.

В разделе «Граница» выберите вид линии.
Жмите на диагональную линию в окне «Отдельные».

Красиво разместите текст, чтобы он находился красиво над / под линией.
В разделе «Выравнивание» и в окне «Ориентация» измените наклон текста.

Способ №4 (если ранее была объединена):

Поставьте курсор на ранее объединенный участок в Экселе.
Выберите «Отменить объединение».

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

Столбец

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

Выше мы рассматривали, как разделить ячейку в Экселе на несколько, и для разделения можно использовать указанный в способе №1 вариант. Главное выбрать деление по вертикали.

Но можно использовать еще один вариант:

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

Выберите переключатель «С разделителями», если установлен иной вариант, и кликните «Далее».

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

Жмите далее и в окне «Формат данных столбца» выберите необходимый формат. Если ничего не менять, он будет таким же, как в первоначальной ячейке.

Строку

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

Но можно использовать еще один вариант, как разделить строки в Excel с помощью инструмента транспонирования. Сделайте следующие шаги:

Выберите одну строчку в Экселе и копируйте ее с помощью Ctrl+C.
Станьте на 1-ю ячейку диапазона и кликните правой кнопкой мышки.
Жмите на «Транспонировать» под «Параметры вставки».
Проверьте, что одна строка ставится как один столбец, но с несколькими строчками.

Еще один вариант — применение специальной утилиты:

После этого одна строка в Экселе разбивается на несколько. Можно использовать и другой путь, который также подразумевает применение Kutools. После входа в панель инструментов выберите «Текст», а далее «Разделить клетки». Далее в окне разделения ячеек установите «Разделить на две строки» и в «Тип» укажите вариант деления.

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

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

Инструмент «Разбить таблицу» автоматически разнесёт данные одного листа по нескольким листам:

Разнесение данных таблицы или диапазона на отдельные листы Выбор метода разделения: по значениям столбца или по числу строк Сохранение заголовков и форматирования в таблицах результата Разделение объединённых ячеек с дублированием значений

Перед началом работы добавьте «Разбить таблицу» в Excel

«Разбить таблицу» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

– пробный период дает 14 дней полного доступа ко всем инструментам.

Как разбить таблицу на несколько листов на основе значений столбца

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

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

Отметьте флажком Таблица с заголовками , если это так.

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

Выберите разбить по Значениям в этом столбце в качестве метода разделения В выпадающем списке найдите и выберите ключевой столбец:

Если в таблице есть заголовок, найдите столбец по его названию в заголовке. Если в таблице нет заголовка, найдите столбец по его общему буквенному обозначению (A, B, C, т.д.)

Задайте способ именования листов результата:

Выберите Значение в столбце , чтобы вкладкам присваивались имена по значениям ключевого столбца. Внимание: если некоторые ячейки в вашем ключевом столбце пустые, пожалуйста, заполните пропуски или используйте другой способ именования листов. Или, выберите Числовой ряд , чтобы вкладкам назначались имена последовательными числами (1, 2, 3…) При необходимости, добавьте префикс или суффикс. Они будут повторяться в названии каждой вкладки. Совет: рекомендуем использовать содержательные префиксы и суффиксы — позже будет проще искать и переключаться между листами. Нажмите OK Готово. Обработка больших таблиц может занять некоторое время.

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

Как разбить таблицу на несколько листов по заданному числу строк

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

Отметьте флажком Таблица с заголовками , если это так.

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

Задайте способ именования листов результата:

Выберите Числовой ряд , чтобы вкладкам назначались имена последовательными числами (1, 2, 3…) При необходимости, добавьте префикс или суффикс. Они будут повторяться на каждой вкладке. Совет: рекомендуем использовать содержательные префиксы и суффиксы — позже будет проще искать и переключаться между листами. Нажмите OK Готово. Обработка больших таблиц может занять некоторое время.

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

Как образом данные копируются на новые листы

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

Формулы и ссылки на ячейки:

Чтобы избежать искажения данных, вместо ссылок на ячейки, функций или формул на исходном листе, в листы результата надстройка XLTools «Разбить таблицу» вставляет их значения.

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

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

Как сохранить листы результата отдельными файлами

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

Главный стол	Разделить таблицу на несколько таблиц по значению столбца	Разделить таблицу на несколько таблиц по количеству строк

Разделите большую таблицу на несколько таблиц на основе значения столбца с кодом VBA

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

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модульи вставьте следующий код в Модуль Окно.

Код VBA: разделите большую таблицу на несколько таблиц по ключевому столбцу:

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

4, Затем нажмите OK, и появится другое диалоговое окно, выберите данные столбца, на основе которых вы хотите разделить таблицу, см. снимок экрана:

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

Разделите большую таблицу на несколько таблиц на основе определенного количества строк с кодом VBA

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

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модульи вставьте следующий код в Модуль Окно.

Код VBA: разделите большую таблицу на несколько таблиц по количеству строк:

3, Затем нажмите F5 нажмите, в появившемся диалоговом окне выберите строку заголовка, см. снимок экрана:

4, Затем нажмите OKи во втором поле подсказки выберите диапазон данных, который вы хотите разделить по количеству строк, см. снимок экрана:

5. А затем продолжайте нажимать OK кнопку, в третьем поле запроса введите количество строк, которые вы хотите разделить, см. снимок экрана:

6, Затем нажмите OK Кнопка, основная таблица была разделена на несколько листов в зависимости от количества строк, как показано ниже:

Разделите большую таблицу на несколько таблиц на основе значения столбца или количества строк с удивительной функцией

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

Советы: Чтобы применить это Разделить данные функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон данных, который вы хотите разделить, а затем щелкните Kutools Plus > Разделить данные, см. снимок экрана:

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

(1.) Выберите Конкретный столбец or Фиксированные строки из Сплит на основе раздел по мере необходимости;

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

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

В Microsoft Excel есть много инструментов для сборки данных из нескольких таблиц (с разных листов или из разных файлов): прямые ссылки, функция ДВССЫЛ (INDIRECT) , надстройки Power Query и Power Pivot и т.д. С этой стороны баррикад всё выглядит неплохо.

Но если вы нарвётесь на обратную задачу - разнесения данных из одной таблицы на разные листы - то всё будет гораздо печальнее. На сегодняшний момент цивилизованных встроенных инструментов для такого разделения данных в арсенале Excel, к сожалению, нет. Так что придется задействовать макрос на Visual Basic, либо воспольоваться связкой макрорекордер+Power Query с небольшой "доработкой напильником" после.

Давайте подробно рассмотрим, как это можно реализовать.

Постановка задачи

Имеем в качестве исходных данных вот такую таблицу размером больше 5000 строк по продажам:

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

Подготовка

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

Во-первых, создадим отдельную таблицу-справочник, где в единственном столбце будут перечислены все города, для которых нужно создать отдельные листы. Само-собой, в этом справочнике могут быть не все города, присутствующие в исходных данных, а только те, по которым нам нужны отчеты. Проще всего создать такую таблицу, используя команду Данные - Удалить дубликаты (Data - Remove duplicates) для копии столбца Город или функцию УНИК (UNIQUE) - если у вас последняя версия Excel 365.

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

Во-вторых, преобразуем обе таблицы в динамические ("умные"), чтобы с ними было проще работать. Используем команду Главная - Форматировать как таблицу (Home - Format as Table) или сочетание клавиш Ctrl + T . На появившейся вкладке Конструктор (Design) назовём их таблПродажи и таблГорода, соответственно:

Способ 1. Макрос для деления по листам

На вкладке Разработчик (Developer) нажмите на кнопку Visual Basic или используйте сочетание клавиш Alt + F11 . В открывшемся окне редактора макросов вставьте новый пустой модуль через меню Insert - Module и скопируйте туда следующий код:

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

Запустить созданный макрос в Excel можно на вкладке Разработчик кнопкой Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 .

Способ 2. Создаем множественные запросы в Power Query

У предыдущего способа, при всей его компактности и простоте, есть существенный недостаток - созданные макросом листы не обновляются при изменениях в исходной таблице продаж. Если обновление "на лету" необходимо, то придется использовать связку VBA+Power Query, а точнее - создавать с помощью макроса не просто листы со статическими данными, а обновляемые запросы Power Query.

Макрос в этом случае частично похож на предыдущий (в нём тоже есть цикл For Each . Next для перебора городов в справочнике), но внутри цикла будет уже не фильтрация и копирование, а создание запроса Power Query и выгрузка его результатов на новый лист:

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

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

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