Як розділити стовпець даних Excel на два стовпці. Як проаналізувати дані в Excel.
Переглянути відео
- Перший метод Білла з використанням тексту в стовпці (знаходиться на вкладці Дані).
- На кроці 1 виберіть розділений. На кроці 2 виберіть пробіл. Пропустіть крок 3, натиснувши кнопку Готово.
- Текст буде розділений на кожен пробіл, тому все, що має три слова, опиниться в 3 клітинках. Покладіть їх назад разом з
=TEXTJOIN(" ",True,B2:E2)або - з
=B2&" "&C2&" "&D2 - Перший метод Майка використовує Power Query. Power Query - це «Отримати та перетворити» у 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 + Стрілка вниз, а потім Дані, Текст у стовпці. Текст у стовпці на кроці 1, дані розділені. Він розділений пробілом, а потім просто натисніть кнопку Готово. Зараз ось клопоти з цим методом полягають у тому, що якщо у вас 123 Main Street, то він опиниться в 3 клітинах замість 2 клітин. О, Power Query зробить це набагато простішим, але ось ми тут. Добре, то, що я збираюся зробити, це я вийду далеко праворуч від Даних, де я знаю, що там, де все побудовано. Якщо я працюю в Office 365, я збираюся використовувати TEXTJOIN. TEXTJOIN, ця дивовижна річ, роздільник - це простір. Ігнорувати порожні клітинки True, а потім клітинки, які я хочу об'єднати таким чином, і я просто копіюю всі ці вниз, Ctrl + V. Я скопіюю Ctrl + C, а потім Home, Paste,Вставте як значення, і на цьому етапі я можу видалити ці 3 додаткові стовпці.
Ага, але ні в кого немає Office 365, так? Отже, якщо у вас немає Office 365, вам потрібно зробити = this thing & “” & that, а потім, якщо було більше “” & that, а якщо їх було більше, продовжуйте. У цьому випадку це безглуздо, тому що в D нічого немає, але ви розумієте. Ctrl + C, скопіюйте його до останнього рядка даних, Ctrl + V, а потім Ctrl + C, Alt + ESV, щоб зробити ці значення B. І ось ми там, добре. Майк, давайте подивимось, що у вас є.
Майк Гірвін: Дякую. Ей, ти мені тут простий, тому що ти вже згадав Get & Transform Power Query, старий Текст у стовпці дозволяє лише вимовляти пробіл біля кожного символу, так? Що ж, якщо ми використовуємо Power Query, ми можемо використати цей роздільник і сказати: "Гей, просто розділи з першого разу".
Тепер, щоб отримати ці дані у редакторі запитів, ми повинні перетворити їх у таблицю Excel. Тож я піднімаюся до Insert, Table або використовую Ctrl + T. У моїй таблиці є заголовки, кнопка OK виділена, щоб я міг клацнути мишею або просто натиснути Enter. Тепер я хочу назвати цю таблицю, тому я збираюся підійти сюди, OriginalData та Enter. Тепер це таблиця Excel, ми можемо підійти до даних, і ось вона з таблиці. Це перенесе його з Excel в редактор. Вибрано стовпець: вкладка Домашня стрічка, ми можемо сказати Розділити стовпець роздільником або перейти сюди та клацнути правою кнопкою миші, Розділити стовпець роздільником. З випадаючого списку ми можемо сказати, привіт, використовуйте пробіл і подивіться на цей крайній лівий роздільник. Коли я натискаю кнопку ОК, БУМ! Там. Тепер я збираюся назвати обидва ці стовпці: двічі клацніть Part 1 Enter, двічі клацніть Part 2 та Enter. Зараз,Я можу підійти сюди або Закрити & Завантажити, Закрити & Завантажити, і я можу вибрати, куди це помістити. Я точно хочу скинути його як Таблицю, Новий аркуш, Існуючий аркуш. Виділіть це, натисніть кнопку згортання. Я збираюся сказати D1, натисніть кнопку OK, а потім натисніть кнопку Завантажити. І ось, наш вихідний сигнал Power Query.
Добре, повертайся до.
Білл Джелен: О, Майк, Power Query - це чудово! Так, це чудовий шлях. Ось ще один, який може працювати, якщо у вас Excel 2013 або новішої версії.
І що ми будемо робити, це вийти сюди і сказати Першу частину, а потім Другу частину. Обов’язково поставте ці заголовки: якщо ви не ставите ці заголовки, вони не повинні бути такими, але вони повинні мати заголовки, інакше це не спрацює. Я покладу 123 і Мейн-стріт, а потім ми покладемо Говарда та Енда, ось так. Тепер, коли у нас є приємний маленький шаблон, вийдіть сюди на вкладку даних і заливку Flash, яка є 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, пробіл, а потім Говард, пробіл і так далі, кома, а потім новий текст, який я хочу замінити. Ну, щоб сказати ЗАМІННИКУ, що ви хочете замінити його нічим, ви кажете “” немає пробілу між ними, закрийте дужки, і це буде працювати. 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.
Добре, я хочу подякувати усім, що завітали. Ми побачимо вас наступного разу для чергового дуельного підкасту Excel від і Excel є цікавим.
Завантажити файл
Завантажте зразок файлу тут: Duel182.xlsm
