Бывают ситуации, когда в Excel приходится разбивать ячейки. Это может быть, когда вы получаете данные из базы данных, копируете их из Интернета или получаете от коллеги.
Простой пример, когда вам нужно разделить ячейки в Excel, — это когда у вас есть полные имена, и вы хотите разделить их на имя и фамилию.
Или вы получаете адрес »и хотите разделить адрес, чтобы можно было анализировать города или пин-код отдельно.
Как разделить ячейки в Excel
В этом руководстве вы узнаете, как разбивать ячейки в Excel, используя следующие методы:
- Использование функции Text to Column.
- Использование текстовых функций Excel.
- Использование Flash Fill (доступно в 2013 и 2016 годах).
Давай начнем!
Разделить ячейки в Excel с помощью текста в столбец
Ниже у меня есть список имен некоторых из моих любимых вымышленных персонажей, и я хочу разделить эти имена на отдельные ячейки:
Вот шаги, чтобы разделить эти имена на имя и фамилию:
- Выделите ячейки, в которых есть текст, который вы хотите разделить (в данном случае A2: A7).
- Перейдите на вкладку «Данные».
- В группе «Инструменты для работы с данными» нажмите «Текст в столбцы».
- В мастере преобразования текста в столбцы:
- Шаг 1 из 3 мастера преобразования текста в столбцы: Убедитесь, что выбран параметр «С разделителями» (это выбор по умолчанию). Это позволит вам разделить имя и фамилию на основе указанного разделителя (в данном случае пробел).
- Щелкните Далее.
- Шаг 2 из 3 Мастер преобразования текста в столбцы: Выберите пробел в качестве разделителя и отмените выбор всего остального. Вы можете увидеть, как будет выглядеть ваш результат, в разделе «Предварительный просмотр данных» диалогового окна.
- Щелкните Далее.
- Шаг 3 из 3 Мастер преобразования текста в столбцы: На этом шаге вы можете указать формат данных и желаемый результат. Я буду использовать общий формат данных, так как у меня есть текстовые данные для разделения. Назначением по умолчанию является A2, и если вы продолжите, он заменит исходный набор данных. Если вы хотите сохранить исходные данные без изменений, выберите другую ячейку в качестве места назначения. В этом случае выбирается B2.
- Щелкните Готово.
- Шаг 1 из 3 мастера преобразования текста в столбцы: Убедитесь, что выбран параметр «С разделителями» (это выбор по умолчанию). Это позволит вам разделить имя и фамилию на основе указанного разделителя (в данном случае пробел).
Это мгновенно разделит текст ячейки на два разных столбца.
Примечание:
- Функция Text to Column разделяет содержимое ячеек на основе разделителя. Хотя это хорошо работает, если вы хотите разделить имя и фамилию, в случае имени, отчества и фамилии он разделит их на три части.
- Результат, который вы получаете от использования функции Text to Column, является статическим. Это означает, что если есть какие-либо изменения в исходных данных, вам придется повторить процесс, чтобы получить обновленные результаты.
Разделение ячеек в Excel с помощью текстовых функций
Функции Excel Text отлично подходят, когда вы хотите нарезать текстовые строки кубиками.
В то время как функция Text to Column дает статический результат, результат, который вы получаете от использования функций, является динамическим и будет автоматически обновляться при изменении исходных данных.
Разделение имен, содержащих имя и фамилию
Предположим, у вас есть такие же данные, как показано ниже:
Извлечение имени
Чтобы получить имя из этого списка, используйте следующую формулу:
= ЛЕВЫЙ (A2; ПОИСК (""; A2) -1)
Эта формула обнаружит первый пробел, а затем вернет весь текст перед этим пробелом:
Эта формула использует функцию ПОИСК, чтобы получить позицию символа пробела. В случае с Брюсом Уэйном пробел находится на 6-й позиции. Затем он извлекает все символы слева от него с помощью функции LEFT.
Извлечение фамилии
Аналогично, чтобы получить фамилию, используйте следующую формулу:
= ВПРАВО (A2; LEN (A2) -ПОИСК (""; A2))
Эта формула использует функцию поиска, чтобы найти положение пробела с помощью функции ПОИСК. Затем он вычитает это число из общей длины имени (которая задается функцией LEN). Это дает количество символов в фамилии.
Эта фамилия затем извлекается с помощью функции ВПРАВО.
Примечание: Эти функции могут не работать, если в именах есть начальные, конечные или двойные пробелы. Щелкните здесь, чтобы узнать, как удалить начальные / конечные / двойные пробелы в Excel.
Разделение имен, содержащих имя, отчество и фамилию
Могут быть случаи, когда вы получаете комбинацию имен, в которой некоторые имена также имеют отчество.
Формула в таких случаях немного сложна.
Извлечение имени
Чтобы получить имя:
= ЛЕВЫЙ (A2; ПОИСК (""; A2) -1)
Это та же формула, которую мы использовали, когда не было второго имени. Он просто ищет первый пробел и возвращает все символы перед пробелом.
Извлечение отчества
Чтобы получить отчество:
= ЕСЛИОШИБКА (MID (A2; ПОИСК (""; A2) + 1; ПОИСК (""; A2, ПОИСК (""; A2) +1) -ПОИСК ("", A2)), "")
Функция MID начинается с первого символа пробела и извлекает отчество, используя разницу в положении первого и второго символа пробела.
В случаях, когда отчество отсутствует, функция MID возвращает ошибку. Чтобы избежать ошибки, он заключен в функцию ЕСЛИОШИБКА.
Извлечение фамилии
Чтобы получить фамилию, используйте следующую формулу:
= ЕСЛИ (LEN (A2) -LEN (ПОДСТАВИТЬ (A2; "", "")) = 1; RIGHT (A2; LEN (A2) -SEARCH ("", A2)), RIGHT (A2; LEN (A2) -ПОИСК ("", A2, ПОИСК ("", A2) +1)))
Эта формула проверяет, есть ли отчество (путем подсчета количества пробелов). Если есть только 1 пробел, он просто возвращает весь текст справа от пробела.
Но если их 2, то он определяет второй пробел и возвращает количество символов после второго пробела.
Примечание. Эта формула хорошо работает, когда у вас есть имена, содержащие только первое имя и фамилию, либо имя, отчество и фамилию. Однако, если у вас есть микс, в котором есть суффиксы или приветствия, вам придется дополнительно изменить формулы.
Разделение ячеек в Excel с помощью Flash Fill
Flash Fill — это новая функция, представленная в Excel 2013.
Это может быть очень удобно, когда у вас есть узор, и вы хотите быстро извлечь его часть.
Например, возьмем данные об имени и фамилии:
Флэш-заливка работает, идентифицируя шаблоны и реплицируя их для всех остальных ячеек.
Вот как вы можете извлечь первое имя из списка с помощью Flash Fill:
- В ячейке B2 введите имя Брюса Уэйна (т. Е. Брюс).
- Выделив ячейку, вы увидите небольшой квадрат в правом конце выделенной ячейки. Дважды щелкните по нему. Это заполнит одно и то же имя во всех ячейках.
- Когда ячейки будут заполнены, в правом нижнем углу вы увидите значок параметров автозаполнения. Нажмите здесь.
- Выберите из списка Flash Fill.
- Как только вы выберете Flash Fill, вы заметите, что все ячейки обновляются и теперь показывают имя для каждого имени.
Как работает Flash Fill?
Flash Fill ищет шаблоны в наборе данных и воспроизводит их.
Flash Fill — это удивительно умная функция, которая в большинстве случаев работает должным образом. Но в некоторых случаях это тоже не удается.
Например, если у меня есть список имен, в котором есть комбинация имен, у некоторых есть отчество, а у некоторых нет.
Если в таком случае я извлечу отчество, Flash Fill по ошибке вернет фамилию, если имя отсутствует.
Если честно, это все еще хорошее приближение к тенденции. Однако это не то, что я хотел.
Но это по-прежнему достаточно хороший инструмент, чтобы держать его в своем арсенале и использовать всякий раз, когда в этом возникает необходимость.
Вот еще один пример блестящей работы Flash Fill.
У меня есть набор адресов, из которых я хочу быстро извлечь город.
Чтобы быстро получить город, введите название города для первого адреса (в этом примере введите Лондон в ячейку B2) и используйте автозаполнение, чтобы заполнить все ячейки. Теперь используйте Flash Fill, и вы сразу же получите название города по каждому адресу.
Точно так же вы можете разделить адрес и извлечь любую часть адреса.
Обратите внимание, что для этого потребуется, чтобы адрес был однородным набором данных с одним и тем же разделителем (в данном случае запятая).
Если вы попытаетесь использовать Flash Fill при отсутствии шаблона, появится сообщение об ошибке, как показано ниже:
В этом руководстве я рассмотрел три различных способа разделить ячейки в Excel на несколько столбцов (с использованием текста в столбцы, формул и Flash-заливки).
Надеюсь, вы нашли это руководство по Excel полезным.
