Как да разделите колона от данни на Excel в две колони. Как да анализирате данните в Excel.
Гледам видео
- Първият метод на Бил, използващ Text to Columns (намира се в раздела Data).
- В стъпка 1 изберете разделител. В стъпка 2 изберете интервал. Пропуснете стъпка 3, като щракнете върху край.
- Текстът ще се раздели на всяко място, така че всичко с три думи ще се окаже в 3 клетки. Върнете ги обратно с
=TEXTJOIN(" ",True,B2:E2)или - с
=B2&" "&C2&" "&D2 - Първият метод на Майк използва Power Query. Power Query е Get & Transform през 2016 г. или безплатно изтегляне за 2010 или 2013 г.
- Първо, преобразувайте данните си в таблица, използвайки Ctrl + T. След това в Power Query от таблица. Разделена колона, от разделител. Изберете интервал и след това в най-левия разделител.
- Можете да преименувате колона чрез двукратно щракване!
- Затворете & Заредете за … и изберете ново място на работния лист.
- Вторият метод на Бил е да използва Flash Fill. Въведете нови заглавия в A, B & C. Flash Fill няма да работи, ако нямате заглавия! Въведете шаблон за първите два реда.
- Отидете до първата празна клетка в B и натиснете Ctrl + E. Повторете за колона C.
- Вторият метод на Майк е да използва следните формули:
- За първата част използвайте
=LEFT(A2,SEARCH(" ",A2)-1) - За втората част използвайте
=SUBSTITUTE(A2,B2&" ","")
Видео транскрипция
(Музика)
Бил Джелен: Хей, добре дошли, време е за поредния дуел на Excel Подкаст. Аз съм Бил Джелен от. Към мен ще се присъедини Майк Гирвин от Excel Is Fun. Това е наше
Епизод 182: Разделяне на данни от една клетка, за да се появи в две клетки.
Добре, днешният въпрос е изпратен от Том. Има ли начин за лесно разделяне на данните в една клетка, за да се покажат данните в две клетки? Например, 123 Main Street, той иска 123 в една клетка и Main Street в друга клетка; или, Хауърд и Хауърд и след това Край. Прекарах безброй часове, разделяйки този вид данни. Ще се радвам да чуете вашата компания, докато има много, много различни начини да направите това.
Първото нещо, което ще направя, е да избера всички данни, Ctrl + Shift + стрелка надолу и след това Data, Text to Columns. Текст към колони в стъпка 1, данните са разграничени. То се разграничава с интервал и след това просто щракнете върху Finish. Тук караницата с този метод е, че ако имате 123 Main Street, той ще се окаже в 3 клетки вместо в 2 клетки. О, Power Query ще направи това много по-лесно, но ето ни. Добре, така че това, което ще направя, е да изляза далеч вдясно от Данните, където знам, че отвъд всичко е изградено. Ако съм в Office 365, ще използвам TEXTJOIN. TEXTJOIN, това страхотно нещо, разделителят е пространство. Игнорирайте празни клетки True и след това клетките, които искам да обединим така, и просто копирам всички тези надолу, Ctrl + V. Ще копирам Ctrl + C и след това Home, Paste,Поставете като стойности и на този етап мога да изтрия тези 3 допълнителни колони.
А, но никой няма Office 365, нали? Така че, ако нямате Office 365, трябва да направите = това нещо & "" & онова, а след това, ако имаше още "" и това, и ако имаше повече, продължете. В този случай е безсмислено, защото в D няма нищо, но вие разбирате идеята. Ctrl + C, копирайте го до последния ред с данни, Ctrl + V и след това Ctrl + C, Alt + ESV, за да направите тези B стойности. И ето ни, добре. Майк, нека видим какво имаш.
Майк Гирвин: Благодаря ,. Хей, вие ме уловихте лесно тук, защото вече споменахте Get & Transform Power Query, старият текст към колони ви позволява да кажете интервал при всеки знак, нали? Е, ако използваме Power Query, можем да използваме този разделител и да кажем: „Хей, просто се раздели при първото появяване.“
Сега, за да получим тези данни в редактора на заявки, трябва да ги преобразуваме в таблица на Excel. Така че отивам до Insert, Table или използвам Ctrl + T. Моята таблица има заглавки, бутонът ОК е маркиран, за да мога да го щракна с мишката или просто да натисна Enter. Сега искам да кръстя тази таблица, така че ще дойда тук, OriginalData и Enter. Сега това е таблица на Excel, можем да стигнем до Данни и там е От таблица. Това ще го пренесе от Excel в редактора. Избрана е колоната: Раздел Home Ribbon, можем да кажем Разделяне на колона от разделител или елате тук и щракнете с десния бутон на мишката, Разделяне на колона по разделител. От падащото меню можем да кажем, хей, използвайте интервал и погледнете това в най-левия разделител. Когато щракна ОК, БУМ! Ето го. Сега ще назова и двете колони: щракнете двукратно върху Част 1 Enter, щракнете двукратно върху Част 2 и Въведете. Сега,Мога да изляза тук или да затворя и заредя, да затворя и да заредя и мога да избера къде да го поставя. Определено искам да го изхвърля като таблица, нов работен лист, съществуващ работен лист. Маркирайте това, щракнете върху бутона за свиване. Ще кажа D1, щракнете върху OK, след това щракнете върху Load. И ето го, нашата Power Query Output.
Добре, върни се към.
Бил Джелен: О, Майк, Power Query е страхотно! Да, това е чудесен начин. Ето още един, който може да работи, ако имате Excel 2013 или по-нов.
И това, което ще направим, е да излезем тук и да кажем Първа част и после Втора част. Уверете се, че сте поставили тези заглавия, че ако не поставите тези заглавия, те не трябва да са такива, но трябва да имат заглавия, или няма да работи. Ще сложа 123 и Main Street и след това ще сложим Хауърд и End, така. Сега, когато имаме хубав малък шаблон там, излезте тук в раздела с данни и Flash Fill, който е Ctrl + E, натиснете Ctrl + E точно там и след това натиснете Ctrl + E точно там. Красивото е, че не е нужно да обединяваме данни заедно, както в моя пример. Добре, Майк, обратно към теб.
Майк Гирвин: Ding-ding-ding. Това е победителят без съмнение. Flash Fill е начинът да отидете там. Забележете, не трябваше да го преобразуваме в таблица или да отваряме диалогов прозорец; просто напишете няколко примера и след това Ctrl + E.
Добре, добре, бихме могли да го направим с формули, въпреки че Flash Fill вероятно ще бъде по-бърз. Ами вижте това, моделът, точно като тази клетка от списъка, използвана във Flash Fill, е всичко преди първото пространство и след това всичко след. Хей, ще използвам функцията НАЛЯВО, Текстът е точно там и колко символа отляво? Е, ще потърся това пространство - 1 2 3 4, използвайки функцията SEARCH, Търсене на текст, интервал и “”, в рамките на това. Сега забележете, че Търсене ще брои на пръстите си 1 2 3 4 и това ще стигне до това пространство, което искам, това пространство, така че -1) Ctrl + Enter, щракнете двукратно и го изпратете надолу. Така че, това винаги получава всичко преди първото пространство.
Сега забележете, че вече имаме текста тук, за да мога да използвам функцията SUBSTITUTE. Текстът, който ще разгледам, е Пълните данни, запетая, Старият текст, който искам да търся и след това ЗАМЕСТЕТЕ. Нищо не е почти 1 2 3. Всъщност искам да добавя Space, което току-що извадих в предишната формула, обратно. Сега ще търси 1 2 3, Space и след това Howard, Space и така нататък, запетая и след това новия текст, който искам да заменя. Е, за да кажете на ЗАМЕСТИТЕЛЯ, че искате да го замените с нищо, казвате „” няма интервал между тях, Close Parenthesis и това ще работи. Ctrl + Enter, щракнете двукратно и го изпратете надолу. Добре? Просто го хвърлете обратно.
Бил Джелен: Хей! Добре, Майк, и двата ти метода бяха страхотни. Нека направим едно бързо приключване тук. Първият ми метод с използване на текст към колони: Стъпка 1, изберете Разграничено; Стъпка 2, изберете интервал и след това щракнете върху Готово. Проблемът е, че ако имате няколко интервала, той ще попадне в множество клетки. Трябва да ги събера отново. Office 365 TEXTJOIN или старите B2 & “” & C2 и т.н.
Майк използва Power Query, известен е като получаване на трансформиращ Excel 2016 или в по-ранни версии 10 или 13, изтегляте го и използвате раздела Power Query. Дори научих нещо тук, но първо преобразувахте данни с помощта на Ctrl + T, след това от Таблица, Разделена колона от Delimiter, изберете Delimiter Space и след това веднага в най-левия разделител. Не знаех, че можете да преименувате колона чрез двукратно щракване. Щракнах с десния бутон и преименувах през цялото това време и малко ме дразнеше това. Това ще ми спести много време. И след това не Close & Load, а Close & Load 2 и изберете ново място на работния лист.
Вторият ми метод беше Flash Field. Това е чудесно, ако имате Excel 2013 или по-нова версия. Просто напишете заглавията, няма да работи без заглавията. Въведете шаблон за първите два реда. Отидете до първата празна клетка и натиснете Ctrl + E във всяка колона.
И след това, методът на Майк. Е, със сигурност това беше по-дълго. Задължително е, ако имате нещо преди Excel 2013, защото не можете да използвате Flash Fill. Може би през 2010 г. можете просто Power Query, просто добавете няколко нови колони отляво на A2 и след това ТЪРСЕТЕ, потърсете пространството и -1, за да се отървете от това пространство.
За втората част, ЗАМЕСТИТЕЛ, щях да използвам еднаква средна или нещо подобно, но това е още по-добре, защото вече знаете какво искате да извадите. Искате да извадите B2 и Space и да ги замените с нищо. Това беше страхотно.
Добре, искам да благодаря на всички, че се отбиха. Ще се видим следващия път за поредния дуелиращ подкаст на Excel от и Excel е забавен.
Свали файл
Изтеглете примерния файл от тук: Duel182.xlsm
