Разделяне на клетки в Excel (пълно ръководство с подробни стъпки)
В Excel има различни причини, поради които може да се наложи да разделите клетъчни данни. Например необработените данни могат да съдържат множество части от информация, събрани в една клетка, като пълни имена или адреси. Разделянето на тези клетки ви позволява да отделяте различни видове информация, което прави данните по-лесни за почистване и анализ. Тази статия ще ви послужи като изчерпателно ръководство, показвайки различни начини за разделяне на клетки в редове или колони въз основа на конкретни разделители.
Разделете клетките в Excel на няколко колони
Както е показано на следващата екранна снимка, да предположим, че имате списък с пълни имена и искате да разделите всяко пълно име на отделни собствено и фамилно име и да поставите разделените данни в отделни колони. Този раздел ще демонстрира четири начина, които ще ви помогнат да изпълните тази задача.
Разделете клетките на множество колони със съветника за текст към колона
За да разделите клетки на множество колони въз основа на конкретен разделител, един често използван метод е Текст към колона съветник в Excel. Тук ще ви покажа стъпка по стъпка как да използвате този съветник, за да постигнете желания резултат.
Стъпка 1: Изберете клетките, които искате да разделите, и отворете съветника Текст към колони
В този случай аз избирам диапазона A2: A8, който съдържа пълни имена. След това отидете на Дата кликнете върху Текст към колони за да отворите Текст към колони съветника.
Стъпка 2: Конфигурирайте стъпките една по една в съветника
- в Стъпка 1 на 3 съветник, изберете ограничители и след това кликнете върху Напред бутон.
- в Стъпка 2 на 3 изберете разделителите за вашите данни и след това щракнете върху Напред бутона, за да продължите.
В този случай, тъй като трябва да разделя пълните имена на собствени и фамилни имена въз основа на интервали, избирам само Космос в квадратчето Разделители раздел.бележки:- Ако разделителят, от който се нуждаете, не е показан в този раздел, можете да изберете Други отметка и въведете свой собствен разделител в текстовото поле.
- За да разделите клетките по нов ред, можете да изберете Други отметка и натиснете Ctrl + J ключове заедно.
- В последния съветник трябва да конфигурирате, както следва:
1) В Дестинация изберете клетка, за да поставите разделените данни. Тук избирам клетка C2.2) Щракнете върху завършеност бутон.
Резултат
Пълните имена в избраните клетки са разделени на име и фамилия и са разположени в различни колони.
Удобно разделете клетките на множество колони с помощта на Kutools
Както можете да видите, Текст към колони съветникът изисква множество стъпки за изпълнение на задачата. Ако имате нужда от по-прост метод, Разделени клетки функция на Kutools за Excel е силно препоръчително. С тази функция можете удобно да разделяте клетки на множество колони или редове въз основа на конкретен разделител, като завършите настройките в един диалогов прозорец.
След инсталиране на Kutools за Excelизберете Kutools > Обединяване и разделяне > Разделени клетки за да отворите Разделени клетки диалогов прозорец.
- Изберете диапазона от клетки, съдържащи текста, който искате да разделите.
- Изберете Разделяне на колони опция.
- Изберете Космос (или всеки разделител, от който се нуждаете) и щракнете OK.
- Изберете целева клетка и щракнете OK за да получите всички разделени данни.
Разделете клетките на няколко колони с Flash Fill
Сега нека да преминем към третия метод, известен като Запълване със светкавица. Въведен в Excel 2013, Запълване със светкавица проектиран да попълва автоматично вашите данни, когато усети модел. В този раздел ще демонстрирам как да използвам функцията Flash Fill за разделяне на имената и фамилиите от пълните имена в една колона.
Стъпка 1: Въведете ръчно първите разделени данни в клетката, съседна на оригиналната колона
В този случай ще разделя пълните имена в колона А на отделни собствени и фамилни имена. Първото пълно име е в клетка A2, така че избирам съседната клетка B2 и въвеждам първото име. Вижте екранна снимка:
Стъпка 2: Приложете Flash Fill, за да попълните автоматично всички собствени имена
Започнете да въвеждате второто първо име в клетката под B2 (което е B3), тогава Excel ще разпознае шаблона и ще генерира предварителен преглед на останалите първи имена и трябва да натиснете Въведете за да приемете визуализацията.
Сега всички първи имена на пълни имена в колона A са разделени в колона B.
Стъпка 3: Вземете фамилиите на пълните имена в друга колона
Трябва да повторите горните стъпки 1 и 2, за да разделите фамилните имена от пълните имена в колона A в колоната до колоната с първо име.
Резултат
- Тази функция е налична само в Excel 2013 и по-нови версии.
- Можете също да получите достъп до Flash Fill с един от следните методи.
- Чрез пряк път
След като напишете първото име в клетка B2, изберете диапазона B2:B8, натиснете Ctrl + E клавиши за автоматично попълване на останалите първи имена - По опция панделка
След като напишете първото име в клетка B2, изберете диапазона B2:B8, отидете на щракване Напълнете > Запълване със светкавица под Начало раздела.
- Чрез пряк път
Разделете клетките на множество колони с формули
Горните методи не са динамични, което означава, че ако изходните данни се променят, тогава трябва да изпълним отново същия процес. Вземете същия пример като по-горе, за да разделите списъка с пълните имена в колона A на отделни собствени и фамилни имена и данните за разделяне да се актуализират автоматично с всякакви промени в изходните данни, моля, опитайте една от следните формули
Използвайте функциите TEXT, за да разделите клетките на колони по определен разделител
Формулите, предоставени в този раздел, са налични във всички версии на Excel. За да приложите формулите, направете следното.
Стъпка 1: Извлечете текста преди първия разделител (в този случай собствените имена)
- Изберете клетка (в този случай C2), за да изведете първото име, въведете следната формула и натиснете Въведете за да получите първото име в A2.
=LEFT(A2,SEARCH(" ",A2)-1)
- Изберете тази клетка с резултати и плъзнете манипулатора й за автоматично попълване надолу, за да получите останалите първи имена.
Стъпка 2: Извлечете текста след първия разделител (в този случай фамилните имена)
- Изберете клетка (в този случай D2), за да изведете фамилното име, въведете следната формула и натиснете Въведете за да получите фамилното име в A2.
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))
- Изберете тази клетка с резултати и плъзнете манипулатора й за автоматично попълване надолу, за да получите останалите фамилни имена.
- В горните формули:
- A2 е клетката, съдържаща пълното име, което искам да разделя.
- Пространство в кавички показва, че клетката ще бъде разделена с интервал. Можете да промените референтната клетка и разделителя според вашите нужди.
- Ако една клетка съдържа повече от два текста, разделени с интервали които трябва да бъдат разделени, втората формула, предоставена по-горе, ще върне неправилен резултат. Ще ви трябват допълнителни формули, за да разделите правилно втората, третата и до N-та стойност, разделени с интервали.
- Използвайте следната формула за върнете втората дума (напр. бащино име), разделени с интервали.
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ", 100)),100,100))
- Промени второто 100 да се 200 да се вземете третата дума (напр. фамилия), разделени с интервали.
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ", 100)),200,100))
- Чрез промяна на 200 на 300, 400, 500, и т.н., можете вземете четвъртата, петата, шестата и следващите думи.
- Използвайте следната формула за върнете втората дума (напр. бащино име), разделени с интервали.
Използвайте функцията TEXTSPLIT, за да разделите клетките на колони по конкретен разделител
Ако използвате Excel за Microsoft 365- Функция TEXTSPLIT е по-препоръчително. Моля, направете следното.
Стъпка 1: Изберете клетка за извеждане на резултата. Тук избирам клетка C2
Стъпка 2: Въведете формулата по-долу и натиснете Enter
=TEXTSPLIT(A2," ")
Можете да видите, че целият текст, разделен с интервали в A2, е разделен на различни колони.
Стъпка 3: Плъзнете формулата, за да получите всички резултати
Изберете клетките с резултати в същия ред, след което плъзнете манипулатора за автоматично попълване надолу, за да получите всички резултати.
- Тази функция е налична само в Excel за Microsoft 365.
- В тази формула
- A2 е клетката, съдържаща пълното име, което искам да разделя.
- Пространство в кавички показва, че клетката ще бъде разделена с интервал. Можете да промените референтната клетка и разделителя според вашите нужди.
Разделете клетките в Excel на няколко реда
Както е показано на екранната снимка по-долу, има списък с подробности за поръчката в диапазона A2:A4 и данните трябва да бъдат разделени с наклонена черта, за да се извлекат различни типове информация като артикул, количество, единична цена и дата. За да изпълните тази задача, този раздел демонстрира 3 метода.
Разделете клетките на няколко реда с функцията TEXTSPLIT
Ако използвате Excel за Microsoft 365, методът на функцията TEXTSPLIT може лесно да помогне. Моля, направете следното.
Стъпка 1: Изберете клетка за извеждане на резултата. Тук избирам клетка B6
Стъпка 2: Въведете формулата по-долу и натиснете Enter
=TEXTSPLIT(A2,,"/")
Целият текст в A2 е разделен на отделни редове въз основа на разделителя „наклонена черта“.
За да разделите данните в клетки A3 и A4 на отделни редове въз основа на наклонени черти, просто повторете стъпки 1 и 2 със съответните формули по-долу.
Формула в C6:
=TEXTSPLIT(A3,,"/")
Формула в D6:
=TEXTSPLIT(A4,,"/")
Резултат
- Тази функция е налична само в Excel за Microsoft 365.
- В горните формули можете да промените наклонената черта / в кавичките на всеки разделител според вашите данни.
Удобно разделете клетките на множество редове с помощта на Kutools
Въпреки че функцията TEXTSPLIT на Excel е много полезна, тя е ограничена до потребителите на Excel за Microsoft 365. Освен това, ако имате няколко клетки в колона за разделяне, ще трябва да приложите различни формули поотделно към всяка клетка, за да получите резултатите. За разлика, Kutools за Excel's Разделени клетки функцията работи във всички версии на Excel. Той предоставя лесно и ефективно решение за разделяне на клетки на множество редове или колони наведнъж само с няколко кликвания.
След инсталиране на Kutools за Excel, Щракнете върху Kutools > Обединяване и разделяне > Разделени клетки за да отворите Разделени клетки диалогов прозорец.
- Изберете диапазона от клетки, съдържащи текста, който искате да разделите.
- Изберете Разделяне на редове опция.
- Изберете разделител, от който се нуждаете (тук избирам Други опция и въведете наклонена черта), след което щракнете OK.
- Изберете целева клетка и щракнете OK за да получите всички разделени данни
Разделете клетките на множество редове с VBA код
Този раздел предоставя VBA код, за да можете лесно да разделяте клетки на няколко реда в Excel. Моля, направете следното.
Стъпка 1: Отворете прозореца на Microsoft Visual Basic за приложения
Натиснете Друг + F11 клавиши за отваряне на този прозорец.
Стъпка 2: Поставете модул и въведете VBA код
Кликнете Поставете > Модулии след това копирайте и поставете следния VBA код в Модул (код) прозорец.
VBA код: Разделете клетките на няколко реда в Excel
Option Explicit
Sub SplitCellsToRows()
'Updated by Extendoffice 20230727
Dim inputRng As Range
Dim outputRng As Range
Dim cell As Range
Dim splitValues() As String
Dim delimiter As String
Dim i As Long
Dim columnOffset As Long
On Error Resume Next
Set inputRng = Application.InputBox("Please select the input range", "Kutools for Excel", Type:=8) ' Ask user to select input range
If inputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
Set outputRng = Application.InputBox("Please select the output range", "Kutools for Excel", Type:=8) ' Ask user to select output range
If outputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
delimiter = Application.InputBox("Please enter the delimiter to split the cell contents", "Kutools for Excel", Type:=2) ' Ask user for delimiter
If delimiter = "" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
If delimiter = "" Or delimiter = "False" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
Application.ScreenUpdating = False
columnOffset = 0
For Each cell In inputRng
If InStr(cell.Value, delimiter) > 0 Then
splitValues = Split(cell.Value, delimiter)
For i = LBound(splitValues) To UBound(splitValues)
outputRng.Offset(i, columnOffset).Value = splitValues(i)
Next i
columnOffset = columnOffset + 1
Else
outputRng.Offset(0, columnOffset).Value = cell.Value
columnOffset = columnOffset + 1
End If
Next cell
Application.ScreenUpdating = True
End Sub
Стъпка 3: Стартирайте VBA кода
Натиснете F5 ключ за изпълнение на кода. След това трябва да направите следните конфигурации.
- Ще се появи диалогов прозорец, който ви подканва да изберете клетките с данните, които искате да разделите (тук избирам диапазона A2:A4). След като направите своя избор, щракнете OK.
- Във втория изскачащ диалогов прозорец трябва да изберете изходния диапазон (тук избирам клетка B6) и след това щракнете върху OK.
- В последния диалогов прозорец въведете разделителя, използван за разделяне на съдържанието на клетката (тук въвеждам наклонена черта) и след това щракнете върху OK бутон.
Резултат
Клетките в избрания диапазон се разделят на няколко реда едновременно.
Разделете клетките на няколко реда с Power Query
Друг метод за разделяне на клетки на няколко реда чрез определен разделител е използването Power Query, което също може да направи така, че разделените данни да се променят динамично с изходните данни. Недостатъкът на този метод е, че са необходими няколко стъпки за изпълнение. Нека се потопим, за да видим как работи.
Стъпка 1: Изберете клетките, които искате да разделите на няколко реда, изберете Данни > От таблица / диапазон
Стъпка 2: Преобразувайте избраните клетки в таблица
Ако избраните клетки не са във формат на таблица на Excel, a Създаване на таблица ще се появи диалогов прозорец. В този диалогов прозорец просто трябва да проверите дали Excel е избрал избрания диапазон от клетки правилно, да маркирате дали таблицата ви има заглавка и след това да щракнете върху OK бутон.
Ако избраните клетки са таблица на Excel, преминете към стъпка 3.
Стъпка 3: Изберете Разделяне на колона по разделител
A Маса - Power Query Редактор изскача прозорец, щракнете Разделена колона > Чрез разделител под Начало раздела.
Стъпка 4: Конфигурирайте диалоговия прозорец Разделяне на колона по разделител
- в Изберете или въведете разделителя раздел, задайте разделител за разделяне на текста (Тук аз избирам Custom и въведете наклонена черта / в текстовото поле).
- Разширете Разширени опции раздел (който е сгънат по подразбиране) и изберете Редове опция.
- в Цитат характер раздел, изберете None от падащия списък;
- Кликнете OK.
Стъпка 5: Запазете и заредете разделените данни
- В този случай, тъй като трябва да посоча персонализирана дестинация за моите разделени данни, щраквам Затваряне и зареждане > Затвори и зареди в.
тип: За да заредите разделените данни в нов работен лист, изберете Затваряне и зареждане опция. - в Импортиране на данни диалогов прозорец, изберете Съществуващ работен лист изберете клетка, за да намерите разделените данни, и след това щракнете OK.
Резултат
След това всички клетки в избрания диапазон се разделят на различни редове в рамките на една и съща колона чрез зададен разделител.
В заключение, тази статия изследва различни методи за разделяне на клетки в множество колони или редове в Excel. Без значение кой подход изберете, овладяването на тези техники може значително да подобри ефективността ви при работа с данни в Excel. Продължете да изследвате и ще намерите метода, който работи най-добре за вас.
Свързани статии
Разделяне на клетки по първи интервал в Excel
Този урок демонстрира две формули, които да ви помогнат да разделите клетките по първото пространство в Excel.
Разделяне на числа в колони в Excel
Ако имате списък с големи числа и искате да разделите числата във всяка клетка на отделни цифри и да ги поставите в различни колони, методите в този урок могат да ви помогнат.
Разделете клетка по диагонал в Excel
Обичайно е съдържанието на клетката да се разделя чрез интервал, запетая и т.н. в Excel. Но знаете ли как да разделите клетка по диагонал? Тази статия ще ви покаже решението.
Най-добрите инструменти за продуктивност в офиса
Усъвършенствайте уменията си за Excel с Kutools за Excel и изпитайте ефективност, както никога досега. Kutools за Excel предлага над 300 разширени функции за повишаване на производителността и спестяване на време. Щракнете тук, за да получите функцията, от която се нуждаете най-много...
Раздел Office Внася интерфейс с раздели в Office и прави работата ви много по-лесна
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
Съдържание
- Видео
- Разделете клетките в Excel на няколко колони
- Със съветника за текст към колона
- Лесно с Kutools
- С Flash Fill
- С формули
- Разделете клетките в Excel на няколко реда
- С функция TEXTSPLIT
- Лесно с Kutools
- С VBA код
- с Power Query
- Свързани статии
- Най-добрите инструменти за производителност в офиса
- Коментари