Как разделить столбец данных Excel на два столбца. Как разбирать данные в Excel.
Смотреть видео
- Первый метод Билла с использованием текста в столбцы (находится на вкладке «Данные»).
- На шаге 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&" ","")
Стенограмма видео
(Музыка)
Билл Джелен: Привет, с возвращением, пришло время для еще одного подкаста Dueling Excel. Я Билл Джелен из. Ко мне присоединится Майк Гирвин из Excel Is Fun. Это наш
Эпизод 182: Разделение данных из одной ячейки на две ячейки.
Хорошо, сегодня вопрос прислал Том. Есть ли способ легко разделить данные в одной ячейке, чтобы данные отображались в двух ячейках? Например, 123 Main Street, он хочет, чтобы 123 в одной камере и Main Street в другой камере; или, Говард и Ховард, а затем Энд. Я провел бесчисленное количество часов, разделяя такие данные. Буду признателен за известие от вашей компании, хотя существует множество различных способов сделать это.
Первое, что я собираюсь сделать, это выделить все данные, Ctrl + Shift + стрелка вниз, а затем данные, текст в столбцы. Текст в столбцы на шаге 1, данные разделены. Он разделен пробелом, а затем просто нажмите «Готово». Проблема с этим методом заключается в том, что если у вас 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. Итак, я перехожу к Вставке, Таблице или использую Ctrl + T. В моей таблице есть заголовки, кнопка ОК выделена, поэтому я могу щелкнуть ее мышью или просто нажать Enter. Теперь я хочу назвать эту таблицу, поэтому я собираюсь подойти сюда, OriginalData и Enter. Теперь это таблица Excel, мы можем перейти к Data, а там это From Table. Это перенесет его из Excel в редактор. Столбец выбран: вкладка «Домашняя лента», мы можем сказать «Разделить столбец по разделителю» или подойти и щелкнуть правой кнопкой мыши «Разделить столбец по разделителю». В раскрывающемся списке мы можем сказать: «Эй, используйте пробел» и посмотрите на этот крайний левый разделитель. Когда я нажимаю ОК, БУМ! Вот оно. Теперь я назову оба этих столбца: дважды щелкните "Часть 1" Enter, дважды щелкните "Часть 2" и "Enter". Сейчас же,Я могу подойти сюда или «Закрыть и загрузить», «Закрыть и загрузить в» и выбрать, куда это поместить. Я определенно хочу выгрузить его как таблицу, новый рабочий лист, существующий рабочий лист. Выделите это, нажмите кнопку свертывания. Я скажу D1, нажмите ОК, затем нажмите Загрузить. И вот, наш вывод Power Query.
Хорошо, вернись к.
Билл Джелен: О, Майк, Power Query - это круто! Да, это отличный способ. Вот еще один вариант, который может сработать, если у вас Excel 2013 или новее.
И что мы собираемся сделать, так это выйти сюда и сказать Первую часть, а затем Вторую часть. Убедитесь, что вы разместили эти заголовки: если вы не разместите эти заголовки, они не должны быть такими, но у них должны быть заголовки, иначе это не сработает. Я поставлю 123 и Мэйн-стрит, а потом поставим Говарда и Энд, вот так. Теперь, когда у нас есть красивый небольшой узор, перейдите сюда во вкладке Data и Flash Fill, который представляет собой Ctrl + E, нажмите Ctrl + E прямо здесь, а затем нажмите Ctrl + E прямо здесь. Прекрасно то, что нам не нужно объединять данные вместе, как в моем примере. Хорошо, Майк, вернемся к тебе.
Майк Гирвин: Динь-динь-динь. Это без сомнения победитель. Flash Fill - это то, что вам нужно. Обратите внимание, нам не нужно было преобразовывать его в таблицу или открывать какое-либо диалоговое окно; просто набрал несколько примеров, а затем Ctrl + E.
Хорошо, что ж, мы могли бы сделать это с помощью формул, хотя Flash Fill, вероятно, будет быстрее. Хорошо, посмотрите на это, шаблон, подобный этой ячейке списка, использованной во Flash Fill, - это все, что находится до первого пробела, а затем все после. Итак, я собираюсь использовать функцию LEFT, текст находится прямо там и сколько символов слева? Что ж, я собираюсь найти это место - 1 2 3 4, используя функцию ПОИСК, Найти текст, пробел и «» внутри него. Теперь обратите внимание, что поиск будет считать по пальцам 1 2 3 4, и это попадет в то пространство, которое я хочу, это пространство, поэтому я -1) Ctrl + Enter, дважды щелкните и отправьте его вниз. Итак, он всегда получает все до первого пробела.
Обратите внимание, что у нас уже есть текст, поэтому я могу использовать функцию ЗАМЕНА. Текст, который я собираюсь просмотреть, - это полные данные, запятая, старый текст, который я хочу найти, а затем ЗАМЕНА. Ничего почти не 1 2 3. Я действительно хочу добавить пробел, который я только что вынул в предыдущей формуле, обратно. Теперь он будет искать 1 2 3, пробел, а затем Ховард, пробел и так далее, запятую и затем новый текст, который я хочу заменить. Что ж, чтобы сообщить SUBSTITUTE, что вы хотите заменить его ничем, вы говорите «» без пробелов между ними, закрываете скобки, и это сработает. Ctrl + Enter, дважды щелкните и отправьте его вниз. Хорошо? Просто кинь обратно в.
Bill Jelen: Hey! Alright, Mike, both of your methods were awesome. Let's do a quick wrap-up here. My first method using Text to Columns: Step 1, choose Delimited; Step 2, choose a space, and then click Finish. The problem is that if you have multiple spaces it’s going to end up in multiple cells. I have to put those back together. Office 365 TEXTJOIN or the old B2&“ ”&C2 and so on.
Mike used Power Query, it's known as getting transforming Excel 2016 or in earlier versions 10 or 13, you download it and use the Power Query Tab. I even learned something here, but first you converted data using Ctrl+ T then from Table, Split Column, by Delimiter, choose Delimiter Space and then, at once, at the left-most delimiter. I didn't know you could rename a column by double- clicking. I've been right-clicking and renaming all this time and being a little annoyed of that. That will save me a lot of time. And then not Close & Load but Close & Load 2 and choose a new spot on the worksheet.
My second method was Flash Field. Now that is great if you have Excel 2013 or newer. Just type the headings, it won't work without the headings. Type a pattern for the first two rows. Go to the first blank cell and press Ctrl+E in each column.
And then, Mike's method. Well, sure that was longer. It is a must if you have something before Excel 2013 because you can't use Flash Fill. Maybe in 2010 you can just Power Query, just add some new columns over there at the LEFT of A2 and then SEARCH, look for the space, and -1 to get rid of that space.
For the second part, SUBSTITUTE, I was going to use equal mid or something like that but this is even better because you already know what you want to take out. You want to take out B2 and the Space and replace it with nothing. That was awesome.
Хорошо, я хочу поблагодарить всех, что заглянули. Увидимся в следующий раз на другом подкасте Dueling Excel от и Excel - это весело.
Скачать файл
Загрузите образец файла здесь: Duel182.xlsm
