✅ Как да разделяме клетки в Excel (разделени в множество колони)

Съдържание

Как да разделя клетки в Excel

Може да има ситуации, когато трябва да разделяте клетки в Excel. Това може да е, когато получите данните от база данни или ги копирате от интернет или ги получите от колега.

Прост пример, при който трябва да разделите клетки в Excel, е когато имате пълни имена и искате да ги разделите на собствено и фамилно име.

Или получавате адрес “и искате да разделите адреса, така че да можете да анализирате градовете или пин кода поотделно.

Как да разделя клетки в Excel

В този урок ще научите как да разделяте клетки в Excel, като използвате следните техники:

Използване на функцията Текст в колона.
Използване на текстови функции на Excel.
Използване на Flash Fill (налично през 2013 и 2016 г.).

Нека да започнем!

Разделяне на клетки в Excel чрез използване на текст в колона

По -долу имам списък с имена на някои от любимите ми измислени герои и искам да разделя тези имена на отделни клетки .:

Ето стъпките за разделяне на тези имена на име и фамилия:

Изберете клетките, в които имате текста, който искате да разделите (в този случай A2: A7).
Щракнете върху раздела Данни
В групата „Инструменти за данни“ кликнете върху „Текст в колони“.
В съветника за преобразуване на текст в колони:
- Стъпка 1 от 3 на съветника за текст към колони: Уверете се, че е избрано Delimited (това е избор по подразбиране). Това ще ви позволи да разделите първото име и фамилията въз основа на определен разделител (интервал в този случай).
- Щракнете върху Напред.
- Стъпка 2 от 3 Съветник за текст към колони: Изберете Space като разделител и премахнете избора на всичко останало. Можете да видите как ще изглежда резултатът ви в секцията Предварителен преглед на данни в диалоговия прозорец.
- Щракнете върху Напред.
- Стъпка 3 от 3 Съветник за текст към колони: В тази стъпка можете да посочите формата на данните и къде искате резултата. Ще запазя формата на данните като Общ, тъй като имам текстови данни за разделяне. Дестинацията по подразбиране е A2 и ако продължите с това, тя ще замени оригиналния набор от данни. Ако искате да запазите оригиналните данни непокътнати, изберете друга клетка като дестинация. В този случай се избира B2.
- Щракнете върху Край.

Това незабавно ще раздели текста на клетката в две различни колони.

Забележка:

Функцията Текст в колона разделя съдържанието на клетките въз основа на разделителя. Въпреки че това работи добре, ако искате да разделите името и фамилията, в случай на име, бащино и фамилно име ще го раздели на три части.
Резултатът, който получавате от използването на функцията Текст в колона, е статичен. Това означава, че ако има промени в първоначалните данни, ще трябва да повторите процеса, за да получите актуализирани резултати.

Разделяне на клетки в Excel с помощта на текстови функции

Функциите на Excel Text са чудесни, когато искате да нарежете и нарежете текстови низове.

Докато функцията Текст в колона дава статичен резултат, резултатът, който получавате от използването на функции, е динамичен и автоматично ще се актуализира, когато промените оригиналните данни.

Разделяне на имена с име и фамилия

Да предположим, че имате същите данни, както е показано по -долу:

Извличане на първото име

За да получите първото име от този списък, използвайте следната формула:

= НАЛЯВО (A2, ТЪРСЕНЕ ("", A2) -1)

Тази формула ще открие първия знак за интервал и след това ще върне целия текст преди този знак за интервал:

Тази формула използва функцията SEARCH, за да получи позицията на интервала. В случая с Брус Уейн, космическият герой е на 6 -та позиция. След това извлича всички знаци вляво от него, като използва функцията НАЛЯВО.

Извличане на фамилията

По същия начин, за да получите фамилното име, използвайте следната формула:

= НАДЯСНО (A2, LEN (A2) -SEARCH ("", A2))

Тази формула използва функцията за търсене, за да намери позицията на интервала с помощта на функцията SEARCH. След това изважда това число от общата дължина на името (което е дадено от функцията LEN). Това дава броя на знаците в фамилното име.

След това това фамилно име се извлича с помощта на функцията НАДЯСНО.

Забележка: Тези функции може да не работят добре, ако имате водещи, последни или двойни интервали в имената. Щракнете тук, за да научите как да премахнете водещите/задните/двойните интервали в Excel.

Разделяне на имена с име, бащино име и фамилия

Възможно е да има случаи, когато получавате комбинация от имена, при които някои имена имат и средно име.

Формулата в такива случаи е малко сложна.

Извличане на първото име

За да получите първото име:

= НАЛЯВО (A2, ТЪРСЕНЕ ("", A2) -1)

Това е същата формула, която използвахме, когато нямаше средно име. Той просто търси първия знак за интервал и връща всички знаци преди интервала.

Извличане на средното име

За да получите средното име:

= IFERROR (MID (A2, SEARCH ("", A2)+1, SEARCH ("", A2, SEARCH ("", A2) +1) -SEARCH ("", A2)), "")

MID функцията започва от първия знак за интервал и извлича средното име, като използва разликата в позицията на първия и втория знак за интервал.

В случай, че няма средно име, функцията MID връща грешка. За да се избегне грешката, тя е опакована във функцията IFERROR.

Извличане на фамилията

За да получите фамилното име, използвайте следната формула:

= АКО (LEN (A2) -LEN (ЗАМЕСТНИК (A2, "", "")) = 1, НАДЯСНО (A2, LEN (A2) -ТЪРСЕНЕ ("", A2)), НАДЯСНО (A2, LEN (A2) -SEARCH ("", A2, SEARCH ("", A2) +1)))

Тази формула проверява дали има средно име или не (чрез преброяване на броя на знаците за интервал). Ако има само 1 интервал, той просто връща целия текст вдясно от интервала.

Но ако има 2, тогава той забелязва втория знак за интервал и връща броя на знаците след втория интервал.

Забележка: Тази формула работи добре, когато имате имена, които имат или само името на юмрука и фамилията, или името, бащиното и фамилното име. Ако обаче имате микс, в който имате наставки или поздрави, тогава ще трябва да промените формулите допълнително.

Разделяне на клетки в Excel с помощта на Flash Fill

Flash Fill е нова функция, въведена в Excel 2013.

Може да бъде наистина удобно, когато имате модел и искате бързо да извлечете част от него.

Например, нека вземем данните за името и фамилията:

Флаш запълването работи, като идентифицира модели и го копира за всички останали клетки.

Ето как можете да извлечете първото име от списъка с помощта на Flash Fill:

В клетка В2 въведете първото име на Брус Уейн (т.е. Брус).
С избраната клетка ще забележите малък квадрат в десния край на избора на клетка. Щракнете двукратно върху него. Това ще запълни едно и също име във всички клетки.
Когато клетките се запълнят, в долния десен ъгъл ще видите иконата за опции за автоматично попълване. Щракнете върху него.
Изберете Flash Fill от списъка.
Веднага щом изберете Flash Fill, ще забележите, че всички клетки се актуализират и сега показват първото име за всяко име.

Как работи Flash Fill?

Flash Fill търси шаблоните в набора от данни и копира шаблона.

Flash Fill е изненадващо умна функция и работи според очакванията в повечето случаи. Но също така се проваля в някои случаи.

Например, ако имам списък с имена, който има комбинация от имена, като някои имат средно име, а други не.

Ако извлека средното име в такъв случай, Flash Fill погрешно ще върне фамилията в случай, че няма собствено име.

Честно казано, това все още е добро приближение на тенденцията. Това обаче не е това, което исках.

Но все пак това е достатъчно добър инструмент, който да държите във вашия арсенал и да го използвате винаги, когато възникне необходимост.

Ето още един пример, при който Flash Fill работи блестящо.

Имам набор от адреси, от които искам бързо да извлека града.

За да получите бързо града, въведете името на града за първия адрес (въведете Лондон в клетка В2 в този пример) и използвайте автоматичното попълване, за да попълните всички клетки. Сега използвайте Flash Fill и незабавно ще ви даде името на града от всеки адрес.

По същия начин можете да разделите адреса и да извлечете всяка част от адреса.

Имайте предвид, че това ще изисква адресът да бъде хомогенен набор от данни със същия разделител (в този случай запетая).

В случай, че се опитате да използвате Flash Fill, когато няма шаблон, той ще ви покаже грешка, както е показано по -долу: