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

Обновлено: 21.11.2024

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

Разделить ячейку на таблицу с помощью функции "Разделить ячейки"

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

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

<р>1. Выберите ячейки, которые вы хотите разделить на таблицу, и щелкните Данные > Текст в столбцы. Смотрите скриншот:

<р>2. В диалоговом окне «Текст в столбцы» отметьте один вариант, так как вам нужно разделить ячейку, если вы хотите разделить ячейки по разделителям, отметьте «Разделенные», если разделены по фиксированной ширине, отметьте «Фиксированная ширина». Смотрите скриншот:

<р>3. Нажмите «Далее», чтобы продолжить, и проверьте разделитель, который вы хотите разделить, на основе шага 2 из 3 мастера, см. снимок экрана:

Если вы установите флажок «Фиксированная ширина» на шаге 2, вы можете щелкнуть в том месте строки, где вы хотите разделить ее, в мастере шага 2 из 3. Смотрите скриншот:

<р>4. Нажмите «Далее» и укажите место назначения таблицы, которую вы хотите разместить, на шаге 3 из 3 мастера. Смотрите скриншот:

<р>5. Нажмите Готово. Затем ячейки были разбиты на таблицу.

Разделить ячейку на таблицу с помощью Split Cells

С функцией «Текст в столбцы» шаги сложны, если вы заинтересованы в добавлении, вы можете попробовать утилиту «Разделить ячейки» Kutools for Excel, которая может быстро разделить ячейку на таблицу.

После установки Kutools for Excel сделайте следующее: (Загрузите Kutools for Excel сейчас!)

<р>1. Выберите ячейки, которые вы хотите разделить, и нажмите Kutools > Объединить и разделить > Разделить ячейки. Смотрите скриншот:

<р>2. В диалоговом окне «Разделить ячейки» укажите нужные разделы «Тип» и «Разделить по», затем нажмите «ОК», чтобы выбрать ячейку для размещения выходной таблицы. Смотрите скриншот:

<р>3. Нажмите «ОК». И ячейки были разделены на таблицу.

Разбить на столбцы

Разбить на строки

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

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

Если в вашей таблице включен стандартный фильтр 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 решений для повседневных задач.

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

Надстройка «Разделить таблицу» автоматически разбивает данные на одном листе на несколько новых листов:

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

Разделить таблицу — это одна из более чем 20 функций надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, Office 365 для настольных ПК.

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

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

Проверьте, есть ли заголовки в моей таблице.

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

Определите, как назвать получившиеся рабочие листы:

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

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

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

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

Проверьте, есть ли заголовки в моей таблице.

Выберите, как назвать получившиеся рабочие листы:

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

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

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

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

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

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

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

Как сохранить полученные рабочие листы в виде отдельных файлов

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

robert huff Привет! Возможно, я упустил из виду ресурсы, но было бы полезно, если бы также были предоставлены образцы файлов Excel, иллюстрирующие каждую из функций XL Tools Pro. Спасибо. Боб

Мария Балобанова Привет, Боб! Файлов с примерами нет, но вы можете найти пошаговые руководства по каждой функции — все они перечислены на главной странице.

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

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

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

Скачать файл примера

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

Скачать файл: 0051 Разделить ячейки в Excel.xlsx

Посмотреть видео

Пример данных

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

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

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

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

Вот и все. Всего несколько кликов и готово 🙂

Теперь наш диапазон должен быть идеально разделен на несколько столбцов.

Примечание о тексте в столбцах

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

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

Разделить ячейки с помощью Flash Fill

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

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

Фоновое выполнение

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

Пока ничего не произойдет.< /li>
Начните вводить элемент текста, который мы хотим извлечь из второй ячейки. Теперь Flash Fill начинает действовать и дает рекомендации:
Нажмите клавишу Tab или Enter, чтобы принять рекомендуемую заливку.

Это дает нам первое имя.

Выполнение вручную

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

Примечание о быстрой заливке

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

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

Мгновенная заливка также доступна в разделе Данные (вкладка) > Мгновенная заливка

Если Excel не может понять шаблон, он вернет сообщение об ошибке.

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

Разделить ячейки с помощью Power Query

Еще один способ разделения нескольких ячеек в Excel — использование Power Query. Это изначально доступно с Excel 2016. До этого существовала надстройка для Excel 2010 и 2013.

Теперь преобразованные данные будут загружены обратно в ячейки Excel.

Примечание о Power Query

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

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

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

Power Query предоставляет множество расширенных инструментов для более сложных операций с данными. Если вы мало использовали Power Query, ознакомьтесь с моей серией «Введение в Power Query».

Разделить ячейки с помощью формул Excel

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

Полезные функции Excel для разделения ячеек

Возвращает указанное количество символов от начала текстовой строки.

Пример:

Результат: Дорис

Второй аргумент 5 указывает функции вернуть первые 5 символов текста «Дорис Грин».

ПРАВО

Возвращает указанное количество символов с конца текстовой строки.

Пример:

Результат: Запад

Второй аргумент 4 указывает функции вернуть последние 4 символа текста «Кэтрин Уэст».

MID возвращает символы из середины текстовой строки с заданной начальной позицией и длиной.

Пример:

Результат: Лью

Второй аргумент представляет собой n-й символ, с которого нужно начать, а последний аргумент — длину строки. В тексте «Энн Льюис» 3 символа, начинающиеся с позиции 5, — это «Лью».

Возвращает количество символов в текстовой строке.

Пример:

Результат: 13

В строке 13 символов (включая пробелы).

ПОИСК

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

Пример:

Результат: 8

Символ пробела сначала появляется на 8-й позиции строки «Чарльз Белл»; поэтому возвращаемое значение равно 8.

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

Примечание. НАЙТИ – это эквивалент функции ПОИСК с учетом регистра.

ЗАМЕНИТЬ

ПОДСТАВИТЬ заменяет все существующие экземпляры текстовой строки новой текстовой строкой.

Пример:

Результат: Хуан Уилсон

Текстовая строка Dix была заменена на Wils. Последний аргумент определяет, какой экземпляр заменить. В приведенной выше формуле он заменил только 1-й экземпляр. Если бы мы исключили этот аргумент, он заменил бы все экземпляры.

Пример сценария

Наш сценарий достаточно прост: чтобы разделить ячейку на несколько столбцов, мы можем использовать функции ВЛЕВО, ВПРАВО, ДЛСТР и ПОИСК. Мы можем извлечь имя и фамилию следующим образом:

ПОИСК находит позицию пробела, которая для первой записи в примерах ячеек «Амалия Аллен» является 8-й позицией.
Мы не хотим, чтобы в строку включался символ пробела, поэтому мы вычитаем из результата 1, чтобы получить значение 7.
LEFT затем извлекает первые 7 символов текстовой строки.

LEN находит длину текстовой строки, которая для «Амалия Аллен» равна 13.
ПОИСК находит позицию пробела, которая равна 8.
13 минус 8 дает нам количество символов после первого пробела.
Затем используется RIGHT для извлечения символов после пробела.

Эти две формулы возвращают нас к тому же результату, что и раньше; имя в столбце B и фамилия в столбце C:

Примечания об использовании формул

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

В зависимости от сложности разделяемой текстовой строки эти формулы могут стать довольно сложными.

Заключение

С помощью этих 4 методов вы научились разбивать ячейки в Excel. Каждый вариант добавлял все больше и больше возможностей для решения сложных задач. Какой вариант вы выберете, зависит от вашего конкретного сценария. Ознакомьтесь со всеми вариантами, и тогда вы сможете принимать наилучшие решения.
Получите БЕСПЛАТНУЮ электронную книгу VBA с 30 наиболее полезными макросами Excel VBA.

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

Не забывайте:

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

Вам нужна помощь в адаптации этого к вашим потребностям?

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

Читайте другие блоги или смотрите видео на YouTube по той же теме. Вы получите гораздо больше пользы, найдя собственные решения.
Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
Используйте Excel Rescue, моего партнера-консультанта. Они помогают решить небольшие проблемы с Excel.

Что дальше?
Пока не уходите, в Excel Off The Grid есть чему поучиться. Ознакомьтесь с последними сообщениями:

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