Днес е луд въпрос. Имате колона с номера на части. В номера на частта има от 4 до 7 тирета. Искате да извлечете само частта от номера на частта след първото тире и до, но не и последното тире. Това е дуелен епизод на Excel.
Гледам видео
- Целта е да намерите първото и последното тире и да запазите всичко между тях
- Трудната част тук е намирането на последното тире
- Метод на фактура 1: Flash Fill
- Попълнете ръчно първите няколко (включително някои с различен брой тирета)
- Изберете празната клетка под нея
- Ctrl + E за Flash Fill
- Mike Метод 2:
- Използвайте Power Query
- В Excel 2016 Power Query е в групата Получаване и трансформиране в Excel 2016
- В Excel 2010 и 2013 изтеглете Power Query от Microsoft. Той създава нов раздел Power Query в лентата
- Преобразувайте данните си в таблица, използвайки Ctrl + T
- Използвайте разделяне на данните в Power Query - първо за разделяне в най-лявото тире, след това за разделяне в най-дясното тире
- Метод за сметка 3:
- Функция VBA, която итерира от края на клетката назад, за да намери последното тире
- Mike Метод 4:
- Използвайте SUBSTITUTE, за да намерите местоположението на N-тата тире
- ЗАМЕНИТЕЛ е единствената текстова функция, която ви позволява да посочите номер на екземпляр
- За да намерите кой номер на екземпляр, използвайте
=LEN(A2)-LEN(SUBSTITUTE)
Видео транскрипция
Бил: Хей. Добре дошъл обратно. Време е за поредния подкаст на дуел Excel. Аз съм Бил Джелен от MrExcel. (Към мен ще се присъедини Майк Гирвин от ExcelIsFun. Това е нашият - 00:03) епизод 185: извлечение от първия - до последния -.
Добре. Днешният въпрос е изпратен от Anvar в YouTube. Как мога да извлека всичко от първото - до последното - и да проверя тези данни, които той има тук. Има огромен брой тирета, някъде от 3, 5, 6, 7 тирета, нали?
И така, първата ми мисъл е, е, хей, наистина е лесно да се намери първата - нали? = ляво или = СРЕДА НА НАМЕРЯВАНЕТО на А2 и след това -, +1 добре, но за да стигна до последното -, това ще ме нарани главата, нали, е, добре, колко тирета имаме? Можем да вземем ЗАМЕНИТЕЛЯ на A2, замествайки тиретата, и да сравним дължината на тази, първоначалната дължина. Това ми казва броя на чертичките, но сега знам кое - да намеря, 2-ро, 3-то, 4-то, 5-то, но използвам ли FIND?
Бях готов да отида във VBA, нали? Това е моята реакция на коляно. Казах, изчакайте секунда. Казах, Anvar, в коя версия на Excel си? Той казва, аз съм в Excel 2016. Казах, това е красиво. Ако сте в Excel 2013 или по-нова, бихме могли да използваме тази страхотна нова функция, наречена flash fill. С флаш запълване просто трябва да му дадем шаблон и аз ще му дам достатъчно шаблон, така че не е просто да взема едно с две чертички и да го правя няколко пъти. Искам да се уверя, че имам няколко различни тирета по този начин. Чад от екипа на Excel знае какво търся. Чад е човекът, който е написал логиката за flash fill. И така, имам около 3 от тях там и след това CONTROL + E е пряк път за използване на ДАННИ и след това FLASH FILL и, разбира се, изглежда, че е направил правилното нещо. Добре, Майк.Да видим какво имате.
Майк: Благодаря, MrExcel. Да Flash fill печели. Тази функция точно там, flash fill, е един от модерните инструменти на Excel, който е просто невероятен. Ако става въпрос за еднократна сделка и имате последователен модел, хей, така бих го направил аз.
Ей, да преминем към следващия лист. Сега, вместо да използваме флаш запълване, всъщност можем да използваме заявка за захранване. Сега използвам Excel 2016, така че имам групата GET & TRANSFORM. Това е заявка за мощност. В по-ранните версии, 2013 (до 10 - 2:30), всъщност трябва да изтеглите безплатната добавка за заявка за захранване.
Сега, за да работи заявката за захранване, тя трябва да бъде преобразувана в таблица на Excel. Сега отново бих използвал flash fill, ако това беше еднократна сделка. Кога бихте използвали заявка за захранване? Е, ако сте имали наистина големи данни или идвате от външен източник, това би бил начинът, или може дори да ви хареса това по-добре, отколкото да се налага да пишете 3 или 4 примера за флаш попълване, защото с мощност заявка, ние можем конкретно кажете намери първото - и намерете последното -.
Сега ще преобразувам това в таблица на Excel. Имам избрана единична клетка, празни клетки докрай. Отивам на INSERT, TABLE, или вие използвате клавиатурата, CONTROL + T. Мога да натисна OK или ENTER. Искам да дам име на тази таблица, така че ще отида до TABLE TOOLS, DESIGN, нагоре в PROPERTIES. Ще се обадя на това STARTKEYTABLE и ENTER. Сега мога да се върна към ДАННИ, да го въведа в заявка за захранване с помощта на бутона ОТ ТАБЛИЦА. Там е моята рубрика. Там е името. Не искам да запазвам това име, защото изходът ще бъде експортиран в Excel и искам да му дам различно име. Така че, ще го нарека CLEANEDKEYTABLE. Нямам нужда от този ПРОМЕНЕН ТИП. Просто гледам източника. Сега мога да щракна върху колоната и точно в HOME има бутон SPLIT. Мога да кажа СПЛИТ, ПО ДЕЛИМЕТЪР. Изглежда, че вече се досеща. Азще кажа НАЛЯВО-НАЙ-ВЕЧЕ. Щракнете върху OK.
Сега, ако погледна тук, виждам ПРОМЕНЕН ТИП. Нямам нужда от това, така че ще се отърва от тази стъпка. Имам само РАЗДЕЛЕНА КОЛОНА ПО DELIMITER. Сега ще направя това отново, но вместо да използвам бутона SPLIT тук, щракнете с десния бутон на мишката до SPLIT COLUMN, BY DELIMITER и погледнете това. Можем да изберем да го разделим НАЙ-ДОБРИЯТ ДЕЛИМЕТЪР. Щракнете върху OK. Сега не ми трябват тези две колони, така че щраквам с десния бутон върху колоната, която искам да запазя, ПРЕМАХВАЙТЕ ДРУГИ КОЛОНИ. Всъщност отивам на X този ПРОМЕНЕН ТИП. Ще каже, СИГУРНИ ЛИ СТЕ ИЗТРИВАНЕ НА ТОВА? Ще кажа, да, ИЗТРИВАНЕ. Там са моите чисти данни.
Сега мога да стигна до ЗАТВАРЯНЕ И ТОВАР. ЗАТИРАЙТЕ И ТОВАРЕТЕ. Това е новият диалогов прозорец ИМПОРТ. По-рано се казваше ТОВА ДО, но аз искам да го заредя на маса в СЪЩЕСТВУВАЩИЯ РАБОТЕН ЛИСТ. Щракнете върху бутона за свиване. Отивам да избера C1, да откача, да натисна OK и ето. Запитване за захранване за почистване на нашите данни и получаване само на данните, които искаме. Добре. Ще го хвърля обратно.
Бил: Точно там е смисълът, НАДЯСНО-НАЙ-ДЕЛИМЕТЪРЪТ В КОЛОНАТА ЗА РАЗДЕЛЯНЕ ОТ ДЕЛИМЕТЪР, една от страхотните функции в заявката за захранване. Това е страхотно.
Добре. Моята реакция на коляно - VBA UDF (неразбираема - 05:34) наистина лесна за изпълнение VBA. Превключете на ALT + F11. ПОСТАВЕТЕ МОДУЛ. В този модул въведете този код. Ще (създам - 05:43) чисто нова функция, ще я нарека MIDPART и ще й предам някакъв текст и тогава това, което ще направя, е ще премине от последния знак в тази клетка от дължината на MYTEXT обратно до 1, СТЪПКА -1 и ще погледне този знак. И така, MID на MYTEXT, тази променлива i, ни казва кой знак търсим за дължина 1. Дали е -? Веднага щом намеря -, ще взема ЛЯВОТО от MYTEXT, започвайки от символ i - 1, така че се отървавам от всичко за това последно - докрай и след това, уверете се, че не отивам продължавайте да търсите още тирета, EXIT FOR ще ме измъкне от този (неразбираем - 06:17) цикъл,и от там е лесната част. Ние просто ще вземем MYTEXT, започнете от MID на MYTEXT, (където използвам - 06:26) използвайте функцията FIND, за да намерите първата -, отидете с 1 повече от това и върнете това обратно.
И така, нека се върнем назад, ALT + Q, за да се върнем към Excel. = Раздел MIDPART на това и изглежда, че работи. Копирайте това. Майк, имаш ли още един? (= MIDPart (A2))
Майк: Е, имам още една ,, но това ще бъде една дълга формула - не толкова кратка, колкото тази СДС. Добре, нека да преминем към следващия лист. Сега, ако ще направим формула и имаме някакъв текст и винаги има различен брой разделители, по някакъв начин трябва да получа позицията на последния разделител.
Сега това ще отнеме няколко стъпки, но ще започна с функцията ЗАМЕСТИТЕЛ. Ще разгледам този текст,, старият текст, който искам да намеря, е в ”, че -,, и какво искам да поставя на негово място или да го заместя? „“. Това няма да постави нищо. Сега, ако I) и CONTROL + ENTER, какво ще направя? (= ЗАМЕСТИТЕЛ (A2, “-”, “”))
Е, сега мога да взема дължината на това и да го извадя от дължината на този елемент. Това ще ми каже колко разделители има. F2 и точно в началото ще напиша дължината на това. Това ще ми даде цялата дължина - дължината на текста без тире,), CONTROL + ENTER, щракнете двукратно и го изпратете надолу. това ми казва колко разделители има за този текст. Има 6. (= LEN (A2) -LEN (ЗАМЕНИТЕЛ (A2, “-”, “”)))
Сега ще използвам тази шеста сега в заместителя, за да поставя различен знак точно в шестия списък на разделителя, F2, и ако напиша SUBSTITUTE, това, което искаме да забележим, е, че тази функция има номер на екземпляр. Ако разгледате други текстови функции като търсене и намиране, те нямат номер на екземпляр. Заместването е единственото, което се сещам, което всъщност ви позволява конкретно да кажете с кой екземпляр от разделител искате да се справите. Ето текста,,. Старият текст е в „а - и аз трябва да избера за новия текст някакъв знак, който никога няма да бъде в този текстов ринг. Ще избера, като, или нещо подобно, и там идва номерът на екземпляра,), CONTROL + ENTER и ето го. Ако щракна двукратно и го изпратя, това винаги поставя това в положението на последния разделител. (= ЗАМЕСТИТЕЛ (A2,“-”, “^”, LEN (A2) -LEN (ЗАМЕНИТЕЛ (A2, “-”, “”))))
Сега трябва да разбера във всяка една от тях в каква позиция се намира. F2. Ще използвам функцията SEARCH. ТЪРСЕНЕ. Пиша S и таб. Сега търсенето и намирането са еднакви, с изключение на това, че търсенето не е чувствително към регистъра. В този случай и двете биха били добре, защото търсеният от мен текст е в „, че ^,“, в рамките на този текст. Между другото, причината, поради която използвам търсене вместо търсене, е, че раздела S ми дава търсене, но раздела FI ще ме накара да намеря. Така че, това е като един знак по-малко, когато го пишете. CONTROL + ENTER, щракнете двукратно и го изпратете надолу и сега ми казва, че на 27-мо място е последният разделител. (= ТЪРСЕНЕ (“^”, ЗАМЕСТИТЕЛ (A2, “-”, “^”, LEN (A2) -LEN (ЗАМЕНИТЕЛ (A2, “-”, “”))))))
Сега ще възприема този подход за тези текстови елементи. Сега ще използвам лявата функция и ще получа всичко от самото начало чак до тази позиция. Това ще се отърве от последното малко. Сега всъщност търсенето ни казва 27, което е точно там, а ние искаме само да отидем до 26. И така, F2 и в края отивам на - 1, CONTROL + ENTER, щракнете двукратно и го изпратете надолу. Сега мога да използвам лявата функция. F2. НАЛЯВО. Ето го, останало от това,,. Това е колко символи. ), CONTROL + ENTER, щракнете двукратно и го изпратете надолу. И така, сега се отървахме от последното малко след последния разделител във всяка клетка. (= НАЛЯВО (A2, ТЪРСЕНЕ (“^”, ЗАМЕСТИТЕЛ (A2, “-”, “^”, LEN (A2) -LEN (ЗАМЕНИТЕЛ (A2, “-”, “”))))) - 1))
Сега всичко, което трябва да направя, е да заменя първите четири знака, първите четири знака, първите три знака. Сега мога да използвам функцията за търсене в оригиналния текст, защото той може да намери - което е три и ще кажа замени, моля, отидете от първия знак, три знака и го заменете с нищо, F2, и точно в началото, ще напиша REPLACE. Там е старият текст. Сега гледайте това. Искам да си дам малко повече място за дишане. Просто ще избера изкуствено интервал, ALT + ENTER. Това е нещо като в DAX. Сега просто имам повече място за дишане. Това е старият текст,,. Стартовият номер, трябва винаги да започвам от първата позиция, така че просто въвеждам 1, и трябва да намеря това първо - което представлява брой знаци. И така, раздел S, „-“, през … в този текст,че търсенето ще намери 4, 4, 3. Това ще работи. ) и след това, нов текст „”. Това няма да постави нищо в тези първи знаци. ). Имам маркирана цялата колона, за да мога да попълня тази редактирана формула с CONTROL + ENTER и готово. По целия път извличаме всичко между първото и последното -. (= ЗАМЕНЕТЕ (НАЛЯВО (A2, ТЪРСЕНЕ (“^”, ЗАМЕСТИТЕЛ (A2, “-”, “^”, LEN (A2) -LEN (ЗАМЕНИТЕЛ (A2, “-”, “”)))) - 1) , 1, ТЪРСЕНЕ (“-”, A2), “”))
Единствената причина, поради която искаме да сме луди по този начин с формулите, е ако искаме резултатът от формулата да се актуализира незабавно, когато сме променили нещо, така че ако напиша -00, веднага се актуализира. Запитването за захранване и флаш запълването няма да се актуализират автоматично, нали? Изпратете го обратно на.
Бил: Е, това беше един дявол от формула. Като заместител беше трикът. Бях използвал заместител в първата стъпка, но не видях, че има номера на екземпляра. Добре, така че днес имаме четири различни метода. Първият ми метод е flash fill. Изберете първите няколко, изберете празното поле под него и след това CONTROL + E, за да запълните мига. Методът на Майк, използвайте заявка за захранване. Обичам това, особено разделените данни, които ви позволяват да използвате най-лявата - и след това най-дясната -. Моите семинари на живо винаги говорят за тази една функция. Би трябвало да бъде финалист за Нобелова награда за най-добър отличителен филм. Няма да спечели, но би бил в един от първите пет, сигурен съм. Моят метод номер три, функция VBA, дефинирана от потребителя функция на UDF, която итерира от края на клетката, а след това, методът на Майк, страхотният метод на формулата.Използвайте заместител, за да намерите местоположението на n-та - и след това предайте този отговор обратно в заместител, който ви казва от кой номер на екземпляр да търсите. Брилянтно.
Е, ето ви. Искам да благодаря на всички, че се отбиха. Ще се видим следващия път за поредния подкаст на Дуел на Excel от и ExcelIsFun.
Свали файл
Изтеглете примерния файл тук: Duel185.xlsm
