Данас је лудо питање. Имате колону бројева делова. У делу броја има од 4 до 7 цртица. Желите да извучете само део броја дела након прве цртице и до, али не укључујући последњу цртицу. Ово је двобојна Екцел епизода.
Погледајте видео
- Циљ је пронаћи прву и последњу цртицу и задржати све између
- Овде је најтеже пронаћи последњу цртицу
- Метод рачуна 1: Фласх Филл
- Ручно попуните првих неколико (укључујући неке са различитим бројем цртица)
- Изаберите празну ћелију испод тога
- Цтрл + Е за Фласх Филл
- Мике Метход 2:
- Користите Повер Куери
- У програму Екцел 2016, Повер Куери је у групи Преузми и трансформиши у програму Екцел 2016
- У програму Екцел 2010 и 2013 преузмите Повер Куери од Мицрософта. На траци креира нову картицу Повер Куери
- Претворите податке у табелу користећи Цтрл + Т
- Користите Поделите податке у Повер Куери-у - прво да бисте поделили на крајњој левој цртици, а затим да бисте поделили на сасвим десној цртици
- Метод рачуна 3:
- ВБА функција која се понавља од краја ћелије уназад да би пронашла последњу цртицу
- Мајк Метод 4:
- Користите ЗАМЕНУ да бисте пронашли локацију Н-те цртице
- ЗАМЕНА је једина текстуална функција која вам омогућава да одредите број инстанце
- Да бисте пронашли број инстанце, користите
=LEN(A2)-LEN(SUBSTITUTE)
Видео транскрипт
Билл: Хеј. Добродошли назад. Време је за још један Дуцаст Екцел подцаст. Ја сам Билл Јелен из МрЕкцела. (Придружиће ми се Мике Гирвин из ЕкцелИсФун. Ово је наша - 00:03) епизода 185: извод из прве - последње -.
У реду. Данашње питање шаље Анвар на ИоуТубе-у. Како могу да издвојим све од првог - до последњег - и проверим ове податке које он овде има. Постоји огроман број цртица, било од 3, 5, 6, 7 цртица, у реду?
Дакле, моја прва мисао је, па, хеј, заиста је лако пронаћи прву - зар не? = лево или = СРЕДИНА ПРОНАЂЕЊА А2, а затим -, +1 у реду, али да бих дошао до последњег - од тога ће ме болети глава, зар не, јер, колико цртица имамо? Могли бисмо узети ЗАМЕНУ А2, замењујући цртице, и упоредити дужину те, оригиналне дужине. То ми говори о броју цртица, али сада знам коју - да пронађем, 2., 3., 4., 5., али да ли користим ФИНД?
Био сам спреман да одем на ВБА, зар не? То је моја реакција трзаја у колену. Рекао сам, чекај мало. Рекао сам, Анвар, у којој си верзији Екцела? Каже, ја сам у програму Екцел 2016. Рекао сам, то је прелепо. Ако користите Екцел 2013 или новију верзију, могли бисмо да користимо ову сјајну нову функцију звану Фласх филл. Са фласх филл-ом, морамо му дати образац, а ја ћу му дати довољно узорка, тако да није само да узмем један са две цртице и то неколико пута. Желим да се уверим да на тај начин имам неколико различитих цртица. Чад из Екцел-овог тима зна шта тражим. Цхад је момак који је написао логику за фласх филл. Дакле, уђем тамо отприлике 3, а затим је ЦОНТРОЛ + Е пречица за коришћење ДАТА, а затим ФЛАСХ ФИЛЛ и, сасвим сигурно, изгледа да је поступио исправно. У реду, Мике.Да видимо шта имате.
Мике: Хвала, МрЕкцел. -Да. Фласх филл побеђује. Та функција управо тамо, фласх филл, један је од модерних Екцел алата који је једноставно невероватан. Ако је то једнократна погодба и ако имате доследан образац, хеј, то бих ја на тај начин урадио.
Хеј, идемо на следећи лист. Сада, уместо да користимо флеш попуњавање, заправо можемо да користимо упит напајања. Сада користим Екцел 2016, тако да имам групу ГЕТ & ТРАНСФОРМ. То је питање снаге. У ранијим верзијама, 2013. (до 10 - 2.30), заправо морате да преузмете бесплатни додатак за упите напајања.
Сада, да би упит за напајање функционисао, ово треба претворити у Екцел табелу. Сада бих опет користио фласх филл ако је ово једнократна погодба. Када бисте користили упит напајања? Па, ако сте имали заиста велике податке или сте долазили из спољног извора, ово би био пут или би вам се ово можда чак и више свидело него да морате укуцати 3 или 4 примера за флеш попуњавање, јер, уз упит напајања, можемо конкретно реците пронађи прво - и пронађи последње -.
Сада ћу ово претворити у Екцел табелу. Изабрао сам једну ћелију, празне ћелије до краја. Идем на ИНСЕРТ, ТАБЛЕ, или ви користите тастатуру, ЦОНТРОЛ + Т. Могу да кликнем на ОК или ЕНТЕР. Желим да дам назив овој табели, па ћу прећи на ТАБЛЕ АЛАТИ, ДИЗАЈН, горе у ОСОБИНЕ. Назваћу ово СТАРТКЕИТАБЛЕ и ЕНТЕР. Сада се могу вратити на ДАТА, унети га у упит напајања помоћу дугмета ФРОМ ТАБЛЕ. Ено моје колумне. Ето имена. Не желим да задржим ово име јер ће се излаз извести у Екцел и желим му дати друго име. Дакле, назваћу га ЧИСТИ КЉУЧНИ СТОЛ. Не треба ми тај ПРОМЕЊЕНИ ТИП. Само гледам извор. Сада могу да кликнем на колону и, тачно горе у ХОМЕ, налази се дугме СПЛИТ. Могу рећи ПОДЕЛА, ДЕЛИМЕРОМ. Изгледа да је већ претпостављено. Ја 'Рећи ћу НАЈВЕЋЕ. Кликните ОК.
Сада, ако погледам овамо, видим ПРОМЕЊЕНИ ТИП. То ми не треба, па ћу се решити тог корака. Имам само ПОДЕЉЕНУ КОЛОНУ ДО ДЕЛИМИТЕРА. Сада ћу то поновити, али, уместо да овде користим дугме СПЛИТ, кликните десним тастером миша на СПЛИТ ЦОЛУМН, БИ ДЕЛИМИТЕР, и погледајте то. Можемо да га поделимо ДЕСНИМ НАЈБОЉИМ ДЕЛИМИТЕРОМ. Кликните ОК. Сада ми нису потребне ове две колоне, па ћу кликнути десним тастером миша на колону коју желим да задржим, УКЛОНИ ДРУГЕ КОЛОНЕ. Заправо идем на Кс овај ПРОМЕЊЕН ТИП. Рећи ће ДА ЛИ СТЕ СИГУРНИ ДА ЖЕЛИТЕ ОВО ИЗБРИСАТИ? Рећи ћу, да, ИЗБРИШИ. Ту су моји чисти подаци.
Сада могу доћи до ЗАТВОРИ И УТОВАРИ. ЗАТВОРИ И УТОВАРИ. Ово је нови дијалошки оквир ИМПОРТ. Раније се говорило УТОВАРИ, али желим да га учитам на сто, на ПОСТОЈЕЋЕМ РАДНОМ ЛИСТУ. Кликните на дугме за сажимање. Идем да изаберем Ц1, опозовем, кликнем у реду и ето. Упит за напајање да очистимо наше податке и добијемо само оне податке које желимо. У реду. Вратићу га назад.
Билл: Ту је поента, тачно-НАЈБОЉЕ ДЕЛИМИТЕР у ПОДЕЛИ КОЛОНУ ПО ДЕЛИМИТЕР, једна од сјајних карактеристика у упиту напајања. То је феноменално.
У реду. Моја реакција трзаја у колену - ВБА УДФ (неразумљиво - 05:34) заиста је лако урадити ВБА. Пребаците се на АЛТ + Ф11. УМЕСТИТЕ МОДУЛ. У тај модул откуцајте овај код. Ја ћу (створити - 05:43) потпуно нову функцију, назваћу је МИДПАРТ и проследићу јој неки текст, а онда ћу урадити прећи ће са последњег знака у тој ћелији са дужине МИТЕКСТ натраг на 1, КОРАК -1 и погледати тај знак. Дакле, СРЕДИНА МИТЕКСТ-а, та променљива и, говори нам који карактер тражимо у дужини од 1. Да ли је -? Чим нађем -, узећу ЛЕВО ОД МИТЕКСТ-а почевши од знака и - 1, па се ослободим свега за то последње - скроз, а затим, уверите се да не идем и даље тражите још цртица, ЕКСИТ ФОР ће ме извући из ове (неразумљиве - 06:17) петље,а одатле је лак део. Само ћемо узети МИТЕКСТ, почети са СРЕДИНОМ МИТЕКСТ-а, (где користим - 06:26) помоћу функције ФИНД пронаћи прво -, прећи 1 више од тога и вратити то назад.
Дакле, вратимо се АЛТ + К да се вратимо у Екцел. = МИДПАРТ картица и чини се да ради. Запиши то. Мике, имаш ли још једног? (= МИДдео (А2))
Мике: Па, имам још једну, али то ће бити једна дугачка формула - не тако кратка као тај УДФ. У реду, идемо на следећи лист. Сад, ако ћемо да радимо формулу и имамо неки текст и увек постоји различит број граничника, некако морам да добијем положај тог последњег граничника.
Сада ће ово требати неколико корака, али почећу са функцијом ЗАМЕНА. Прегледаћу тај текст,, стари текст који желим да нађем ”, тај -,, и шта желим да поставим на његово место или да га заменим? “”. То ништа неће убацити. Сад, ако ја) и ЦОНТРОЛ + ЕНТЕР, шта ће то да се уради? (= ЗАМЕНА (А2, “-”, “”))
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.
Па, ето ти. Желим да се захвалим свима што су навратили. Видимо се следећи пут за још један Дуцаст Екцел подцаст од и ЕкцелИсФун.
Скини докуменат
Преузмите датотеку узорка овде: Дуел185.клсм
