Сегодня безумный вопрос. У вас есть столбец с номерами деталей. В номере детали от 4 до 7 тире. Вы хотите извлечь только часть номера детали после первого тире и до последнего тире, но не включая его. Это дуэльный эпизод в Excel.
Смотреть видео
- Цель - найти первый и последний рывок и сохранить все, что находится между ними.
- Самое сложное здесь - найти последний рывок
- Билл Метод 1: мгновенное заполнение
- Вручную заполните первые несколько (в том числе некоторые с другим количеством тире)
- Выберите пустую ячейку под ней
- Ctrl + E для Flash Fill
- Майк Метод 2:
- Используйте Power Query
- В Excel 2016 Power Query находится в группе «Получить и преобразовать» в Excel 2016.
- В Excel 2010 и 2013 загрузите Power Query от Microsoft. Он создает новую вкладку Power Query на ленте.
- Преобразуйте ваши данные в таблицу с помощью Ctrl + T
- Используйте разделение данных в Power Query - сначала для разделения в крайнем левом тире, затем для разделения в крайнем правом тире
- Билл Метод 3:
- Функция VBA, которая выполняет итерацию от конца ячейки назад, чтобы найти последнюю черту
- Майк Метод 4:
- Используйте ЗАМЕНУ, чтобы найти N-й тире
- ПОДСТАВИТЬ - единственная текстовая функция, которая позволяет указать номер экземпляра
- Чтобы узнать номер экземпляра, используйте
=LEN(A2)-LEN(SUBSTITUTE)
Стенограмма видео
Билл: Привет. С возвращением. Пришло время для еще одного подкаста Dueling Excel. Я Билл Джелен из MrExcel. (Ко мне присоединится Майк Гирвин из ExcelIsFun. Это наш - 00:03) эпизод 185: отрывок от первого до последнего.
Хорошо. Сегодняшний вопрос прислал Анвар на YouTube. Как мне извлечь все, от первого до последнего, и проверить эти данные, которые у него есть здесь. Здесь огромное количество тире, от 3, 5, 6, 7 тире, понятно?
Итак, моя первая мысль: ну, эй, действительно легко найти первую - верно? = left или = MID в FIND A2, а затем -, +1 хорошо, но чтобы добраться до последнего -, у меня заболела голова, верно, потому что, ну, сколько у нас тире? Мы могли бы взять ЗАМЕНУ A2, заменив тире, и сравнить его длину с исходной длиной. Это говорит мне количество тире, но теперь я знаю, какое - найти, 2-е, 3-е, 4-е, 5-е, но могу ли я использовать FIND?
Я был готов перейти на VBA, верно? Это моя коленная реакция. Я сказал, подожди секунду. Я сказал: Анвар, какая у тебя версия Excel? Он говорит, я в Excel 2016. Я сказал, это красиво. Если вы используете Excel 2013 или новее, мы могли бы использовать эту замечательную новую функцию, называемую флэш-заливкой. С флэш-заливкой нам просто нужно придать ему узор, и я собираюсь дать ему достаточно узора, так что дело не только в том, что я беру один с двумя черточками и делаю это пару раз. Я хочу убедиться, что у меня есть несколько разных штрихов. Чад из команды Excel знает, что я ищу. Чад - парень, который написал логику для заполнения флэш-памяти. Итак, у меня их около трех, а затем CONTROL + E - это ярлык для использования DATA, а затем FLASH FILL, и, конечно же, похоже, что он сделал правильно. Хорошо, Майк.Посмотрим, что у вас есть.
Майк: Спасибо, MrExcel. Да уж. Побеждает флэш-заливка. Эта функция, флэш-заливка, - один из современных инструментов Excel, который просто потрясающий. Если это разовая сделка, и у вас есть постоянная схема, эй, я бы так поступил.
Эй, давай перейдем к следующему листу. Теперь вместо использования флэш-заливки мы можем использовать запрос мощности. Теперь я использую Excel 2016, поэтому у меня есть группа GET & TRANSFORM. Это силовой запрос. В более ранних версиях 2013 года (до 10 - 2:30) вам действительно нужно было загрузить бесплатную надстройку запроса мощности.
Теперь, чтобы заставить запрос мощности работать, его нужно преобразовать в таблицу Excel. Опять же, я бы использовал флэш-заливку, если бы это было разовое предложение. Когда бы вы использовали запрос мощности? Что ж, если у вас действительно большие данные или вы поступали из внешнего источника, это было бы подходящим вариантом, или вам может даже понравиться это больше, чем необходимость набирать 3 или 4 примера для заполнения флэш-памяти, потому что с помощью запроса питания мы можем конкретно говорят: найди первое - и найди последнее -.
Теперь я собираюсь преобразовать это в таблицу Excel. У меня выделена одна ячейка, все ячейки пустые. Я перехожу к INSERT, TABLE, или вы используете клавиатуру, CONTROL + T. Я могу нажать ОК или ВВОД. Я хочу назвать эту таблицу, поэтому я перейду к ИНСТРУМЕНТЫ ТАБЛИЦ, ДИЗАЙН, наверх СВОЙСТВА. Я назову его STARTKEYTABLE и ENTER. Теперь я могу вернуться к ДАННЫМ и включить их в запрос мощности с помощью кнопки ИЗ ТАБЛИЦЫ. Вот моя колонка. Это имя. Я не хочу оставлять это имя, потому что результат будет экспортирован в Excel, и я хочу дать ему другое имя. Итак, я назову его CLEANEDKEYTABLE. Мне не нужен этот ИЗМЕНЕННЫЙ ТИП. Я просто смотрю на источник. Теперь я могу щелкнуть по столбцу, и прямо в HOME есть кнопка SPLIT. Я могу сказать РАЗДЕЛЕНИЕ ПО РАЗДЕЛИТЕЛЯМ. Похоже, уже догадались. Я'Я скажу ЛЕВЫЙ-САМЫЙ. Щелкните ОК.
Теперь, если я посмотрю сюда, я вижу ИЗМЕНЕННЫЙ ТИП. Мне это не нужно, поэтому я избавлюсь от этого шага. У меня есть только РАЗДЕЛЕНИЕ КОЛОНКИ НА РАЗДЕЛЕНИЕ. Теперь я собираюсь сделать это снова, но вместо того, чтобы использовать кнопку SPLIT здесь, щелкните правой кнопкой мыши вниз, чтобы SPLIT COLUMN, BY DELIMITER, и посмотрите на это. Мы можем разделить его с помощью ПРАВОГО РАЗДЕЛИТЕЛЯ. Щелкните ОК. Теперь мне не нужны эти два столбца, поэтому я собираюсь щелкнуть правой кнопкой мыши по столбцу, который я хочу сохранить, УДАЛИТЬ ДРУГИЕ КОЛОННЫ. Я действительно собираюсь X ИЗМЕНИТЬ ТИП. Будет ли вы уверены, что хотите УДАЛИТЬ ЭТО? Я скажу да, УДАЛИТЬ. Вот мои чистые данные.
Теперь я могу подойти к ЗАКРЫТЬ И ЗАГРУЗИТЬ. ЗАКРЫТЬ И ЗАГРУЗИТЬ. Это новое диалоговое окно ИМПОРТ. Раньше было написано ЗАГРУЗИТЬ В, но я хочу загрузить его в таблицу на СУЩЕСТВУЮЩЕЙ РАБОЧЕЙ ЛИСТЕ. Щелкните кнопку свертывания. Я собираюсь выбрать C1, развернуть, щелкнуть OK, и поехали. Запрос Power для очистки наших данных и получения именно тех данных, которые нам нужны. Хорошо. Закину обратно в.
Билл: Вот в чем суть, ПРАВОЙ РАЗДЕЛИТЕЛЬ в РАЗДЕЛЕННОЙ КОЛОНКЕ ПО РАЗДЕЛИТЕЛЕМ, одной из замечательных функций в запросе мощности. Это потрясающе.
Хорошо. Моя реакция коленного рефлекса - VBA UDF (неразборчиво - 05:34) действительно легко сделать VBA. Переключитесь на ALT + F11. ВСТАВЬТЕ МОДУЛЬ. В этом модуле введите этот код. Я собираюсь (создать - 05:43) новую функцию, я назову ее MIDPART, и я собираюсь передать ей текст, а затем то, что я собираюсь сделать, это я собирается перейти от последнего символа в этой ячейке от длины MYTEXT обратно к 1, STEP -1 и посмотреть на этот символ. Итак, MID в MYTEXT, эта переменная i, сообщает нам, какой символ мы ищем для длины 1. Это -? Как только я найду -, я возьму ЛЕВУЮ часть MYTEXT, начиная с символа i - 1, поэтому я избавлюсь от всего этого последнего - полностью, а затем убедитесь, что я не пойду продолжайте искать больше тире, EXIT FOR вытащит меня из этого (неразборчиво - 06:17) цикла,и отсюда самая легкая часть. Мы просто возьмем MYTEXT, начнем с MID в MYTEXT (где я использую - 06:26), используем функцию FIND, чтобы найти первый -, пройдите еще на 1 и верните это обратно.
So, let's go back, ALT+Q, to return to Excel. = MIDPART tab of that, and it looks like it's working. Copy that down. Mike, do you have another one? (=MIDPart(A2))
Mike: Well, I do have another one,, but it's going to be one long formula -- not as short as that UDF. Alright, let's go over to the next sheet. Now, if we're going to do a formula and we have some text and there are always a different number of delimiters, somehow, I need to get the position of that last delimiter.
Now, this is going to take a few steps but I'm going to start with the SUBSTITUTE function. I'm going to look through that text, , the old text I want to find is in ”, that -, , and what do I want to put in its place or substitute? “”. That will put nothing in. Now, if I ) and CONTROL+ENTER, what is that going to do? (=SUBSTITUTE(A2,“-”,“”))
Well, now I can take the length of this and subtract it from the length of this item. That will tell me how many delimiters there are. F2, and right at the beginning, I'm going to type the length of that. That will give me the full length - the length of that dashless text, ), CONTROL+ENTER, double click, and send it down. that tells me how many delimiters there are for this text. There are 6. (=LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))
Now I'm going to use that sixth now inside of substitute to put a different character right at the sixth listing of the delimiter, F2, and if I type SUBSTITUTE, what we want to notice is this function has an instance number. If you look at other text functions like search and find, they don't have an instance number. Substitute is the only one I can think of that actually lets you specifically say which instance of a delimiter you want to deal with. Here's the text, ,. Old text is in “ a -, and I need to pick for the new text some character that will never be in this text ring. I'm going to choose, like, or something like that, , and that's where instance number comes in, ), CONTROL+ENTER, and there it is. If I double click and send it down, it's always putting that in the position of the last delimiter. (=SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))
Now I need to figure out, in each one of these, what position it is in. F2. I'm going to use the SEARCH function. SEARCH. I type S and tab. Now, search and find are the same except for search is not case-sensitive. In this case, either one would be fine because the text I'm looking for is in “, that ^, ”, , within that text. By the way, the reason that I use search instead of find is because S tab gets me search but F I tab will get me find. So, it's like one character less when typing it out. CONTROL+ENTER, double click and send it down, and now it tells me, in the 27th position is that last delimiter. (=SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))))
Now, I'm going to take this approach for these text items. I'm now going to use the left function and get everything from the very beginning all the way up to that position. That will get rid of that last little bit. Now, actually, search tells us 27 which is right there and we only want to go to 26. So, F2, and, at the end, I'm going to - 1, CONTROL+ENTER, double click and send it down. Now, I can use the left function. F2. LEFT. There it is, left of that, ,. That's how many characters. ), CONTROL+ENTER, double click and send it down. So, now, we have gotten rid of the last little bit after the last delimiter in every cell. (=LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1))
Now all I need to do is replace the first four characters, first four characters, first three characters. Now, I can use the search function on the original text because it can find the - which is three and I'll tell replace, please go, from the first character, three characters in and replace it with nothing, F2, and right at the beginning, I'm going to type REPLACE. There's the old text. Now watch this. I want to give myself a little bit more breathing room. I'm just going to artificially pick a space, ALT+ENTER. That's kind of like we do in DAX. Now I just have more breathing room. That's the old text, ,. The starting number, I need to always start at the first position so I simply type 1, , and I need to find that first - which represents number of characters. So, S tab, “-” , through… within that text, that search will find 4, 4, 3. That will work. ) and then , new text “”. That will put nothing in those first characters. ). I have the entire column highlighted so I can populate this edited formula with CONTROL+ENTER, and there we go. All the way down, we’re extracting everything between the first and the last -. (=REPLACE(LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1),1,SEARCH(“-”,A2),“”))
Now, the only reason we want to be crazy like that with formulas is if we wanted the formula result to instantly update whenever we changed anything, so if I type -00, instantly it updates. Power query and flash fill will not automatically update, alright? Send it back to.
Bill: Well, that was one heck of a formula. Like, substitute was the trick. I had used substitute in the first step but didn't see that it had the instance number. Alright, so, we have four different methods here today. My first method is flash fill. Select first few, select the blank box below that, and then CONTROL+E to flash fill. Mike's method, use power query. I love that, especially the split data letting you use the leftmost - and then the rightmost -. My live seminars always talk about this one feature. Should be a finalist for the Nobel Prize for the best excel feature. It wouldn't win but it would be in one of the top five, I'm sure. My method number three, VBA function, a UDF user-defined function, that iterates from the end of the cell, and then, Mike's method, the awesome formula method. Use substitute to find the location of the nth - and then pass that answer back into substitute that tells you which instance number to look from. Brilliant.
Ну вот и все. Я хочу поблагодарить всех, что заглянули. Увидимся в следующий раз на другом подкасте Dueling Excel от и ExcelIsFun.
Скачать файл
Загрузите образец файла здесь: Duel185.xlsm
