Някои от въпросите, които се появяват, са доста трудни. Днес имаме колона от клетки. Всяка клетка има няколко думи, след това дата, след това още няколко думи. Целта е да издърпате датата на този текст в нова колона. Това е дуелен епизод с идеи на Бил и Майк.
Гледам видео
- Супер широк подход на Бил:
- Поставете всички 12 месеца в отделни колони
- Използвайте функцията FIND, за да видите дали този месец е в оригиналния текст
- За да намерите минималната начална позиция, използвайте = AGGREGATE (5,6,…
- Няколко допълнителни формули за търсене на номер 2 или 3 позиции преди месеца
- Подходът на Майк:
- Използвайте ТЪРСЕНЕ вместо НАМЕРЕТЕ. Намирането е чувствително към малки и големи букви, а търсенето не.
- Създайте операция с масив от аргументи на функция, като посочите B13: B24 като Find_Text.
- Формулата връща #VALUE! Грешка, но ако натиснете F2, F9, ще видите, че връща масив.
- Първите 13 функции в AGGREGATE не могат да обработват масив, но функции 14-19 могат да обработват масив.
- 5 = MIN и 15 = SMALL (, 1) са подобни, но SMALL (, 1) ще работи с масив.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX и AGGREGATE могат да обработват аргументи от масив на функции без Ctrl + Shift + Enter
- Майк беше по-умен, като погледна дали 2 знака преди старта е число и след това взе 3 знака преди. Допълнителното пространство се елиминира от TRIM ()
- За да получите заглавието, използвайте функцията SUBSTITUTE, за да се отървете от текста Date в колона C
Видео транскрипция
Бил Джелен: Хей, добре дошла. Време е за поредния дуелинг подкаст на Excel. Аз съм Бил Джелен от. Към мен ще се присъедини Майк Гирвин от Excel Is Fun.
Това е нашият дуел # 170: Намиране на дати
Хей, добре дошли на всички. Имах толкова страхотен въпрос тук и не можах да го реша. Поне не можах да го разреша лесно, затова излязох при Майк Гирвин и му казах: „Майк, хей, имаш ли начин да направиш това?“ Той каза: „Да, имам начин да го направя. Да направим дуел “.
Така че някой в YouTube е изпратил тези данни и като цяло всяка отделна клетка има нещо като заглавие на документ, последвано от дата. Те искаха да разбият тези данни в заглавието на документа: какво е това, какво е това и след това каква е датата. Но датите са напълно зли. Както тук, това е 20 януари; но тук долу има неща, при които датата може да е след клетката, 9 април. Добре и без значение по какъв начин искаме да го намерим. И понякога има две дати и това е просто напълно ужасно и че това е просто смесена ситуация на дати и колкото е възможно, дори няма дата да се показва, добре. И така, ето моя опит. От дясната страна ще сложа нещата, които търся. Това, което наистина харесвам тук, е, че никога не са съкращавали името на месеца. Аз наистина,наистина оценявам това. Така че напишете през януари и ще изтегля тук до декември така и за всяка клетка, която искам да знам, можем ли да намерим = НАМЕРЯ този януари. Така че ще натисна F4 един, два пъти, за да го заключа само до един ред, в текста там в колона А, така. Ще натисна F4 един, два, три пъти, за да го заключа до колоната, добре. И тук ни казва, че януари се намира в позиция 32, а през останалите 11 месеца ще ни каже, че изобщо не е намерен. С други думи, сега получаваме грешка в стойността. Това, което трябва да направя там, е, че трябва да намеря, трябва да намеря минималната стойност, като се игнорират всички грешки в стойността. Така че, разкрийте тази малка формула тук = АГРЕГАТ и нека да я изградим от нулата, = АГРЕГАТ, това, което искаме е МИН, така че това е числото 5,и след това игнорирайте стойностите за грешка номер 6 запетая и след това всички тези клетки от януари до декември. И това, което ще ни каже, е, че ще ни каже къде се случва месецът. И в този случай ще получим 0, да кажем, че месецът изобщо не се случва.
Добре сега, нека разкрием останалата част от това. И така, за да се справим със ситуацията, когато тук имаме 20 януари или 1 ноември, казах, че първото нещо, което ще направя, е да погледна откъде започва този месец и да се върна две клетки, две клетки, два знака , два знака. И вижте дали това е число, не е така. Това е моята колона тук, наречена Adjust2. Настройте 2. И ето какво ще направим. Ще кажа, вземете MID на A2, стартирайки го къде в G2-2 за дължина 1, добавете 0 към него и попитайте, това число ли е или не? Добре, това е число. И тогава ще потърсим и ситуацията, когато е двуцифрена дата, така че 20 януари. Така че това се нарича Adjust3, върнете се 3 символа от къде. Така че има Къде, върнете се три символа с дължина 1, добавете 0 към него и вижте дали това 'sa номер, добре? След това ще се приспособим и Adjusted Where казва IF. АКО този странен случай е 0, просто ще поставим наистина голяма стойност 999; в противен случай ще се върнем от G2 и ще се върнем назад 3, ако Adjust3 е True или ще се върнем 2, ако Adjust2 е True, или ако нито едно от тях не е True, Къде ще бъде мястото, където започва месецът. Добре, сега, след като знаем, че коригираното Къде, ще щракнем двукратно, за да копираме това. Е, сега е наистина лесно. Просто ще направим - за заглавието ще кажем вземете вляво от А2, колко символа искаме. Искаме D2-1, защото това -1 е да се освободим от пространството в края. Въпреки че предполагам, че TRIM също се отървава от пространството в края.АКО този странен случай беше 0, просто ще поставим наистина голяма стойност 999; в противен случай ще се върнем от G2 и ще се върнем назад 3, ако Adjust3 е True или ще се върнем 2, ако Adjust2 е True, или ако нито едно от тях не е True, Къде ще бъде мястото, където започва месецът. Добре, сега, след като знаем, че коригираното Къде, ще щракнем двукратно, за да копираме това. Е, сега е наистина лесно. Просто ще направим - за заглавието ще кажем вземете вляво от А2, колко символа искаме. Искаме D2-1, защото това -1 е да се освободим от пространството в края. Въпреки че предполагам, че TRIM също се отървава от пространството в края.АКО този странен случай е 0, просто ще поставим наистина голяма стойност 999; в противен случай ще се върнем от G2 и ще се върнем назад 3, ако Adjust3 е True или ще се върнем 2, ако Adjust2 е True, или ако нито едно от тях не е True, Къде ще бъде мястото, където започва месецът. Добре, сега, след като знаем, че коригираното Къде, ще щракнем двукратно, за да копираме това. Е, хей сега, наистина е лесно. Ние просто ще - за заглавието ще кажем вземете вляво от А2, колко знака искаме. Искаме D2-1, защото това -1 е да се освободим от пространството в края. Въпреки че предполагам, че TRIM също се отървава от пространството в края.или ако нито едно от тях не е Вярно, Къде ще бъде мястото, където започва месецът. Добре, сега, след като знаем, че коригираното Къде, ще щракнем двукратно, за да копираме това. Е, сега е наистина лесно. Просто ще направим - за заглавието ще кажем вземете вляво от А2, колко символа искаме. Искаме D2-1, защото това -1 е да се освободим от пространството в края. Въпреки че предполагам, че TRIM също се отървава от пространството в края.или ако нито едно от тях не е Вярно, Къде ще бъде мястото, където започва месецът. Добре, сега, след като знаем, че коригираното Къде, ще щракнем двукратно, за да копираме това. Е, сега е наистина лесно. Просто ще направим - за заглавието ще кажем вземете вляво от А2, колко символа искаме. Искаме D2-1, защото това -1 е да се освободим от пространството в края. Въпреки че предполагам, че TRIM също се отървава от пространството в края.s -1 е да се отървете от пространството в края. Въпреки че предполагам, че TRIM също се отървава от пространството в края.s -1 е да се отървете от пространството в края. Въпреки че предполагам, че TRIM също се отървава от пространството в края.
И тогава за датата ще използваме MID. MID for - MID на A2, започвайки от Коригираното Къде в D2 и излиза 50 или каквото и да е заедно, което смятате, че може да бъде, и след това функцията TRIM и ще щракнем два пъти, за да копираме това.
Добре сега, причината да се обърна към Майк е, че казах, чудя се дали има начин да заменя тези 12 колони с един формуляр, всъщност тези 13 колони с един формуляр. Има ли някакъв начин да направя това, използвайки формула на масив? И Майк, разбира се, написа онази страхотна книга, Ctrl + Shift + Enter, за Array формули. И опитах няколко различни неща и в съзнанието ми нямаше как да стане. Добре, но знаете ли, нека отидем да попитаме експерта. Така че Майк, да видим какво имаш.
Майк Гирвин: Благодаря ,. Хей, и като говорим за експерт, това беше доста експертно направено. Използвахте FIND, AGGREGATE, ISNUMBER (MID. Сега, когато ми изпратихте този въпрос, аз продължих и го реших и е невероятно колко подобно е моето решение на вашето.
Добре, ще отида на този лист тук. Ще започна с това да разбера къде е началната позиция в този текстов низ за всеки конкретен месец. Сега начинът, по който ще го направя, е, хей, да използвам тази функция ТЪРСЕНЕ. Сега, вие сте използвали FIND, аз използвам SEARCH. Всъщност вероятно FIND е по-добре в тази ситуация, защото FIND е чувствителен към малки и големи букви, SEARCH не. Сега обикновено това, което правим или с FIND, или с SEARCH, казвам, хей, отидете FIND, януари, запетая в този по-голям текстов низ, така обикновено използваме SEARCH Ctrl + Enter и той брои на пръста си: едно, две, три , четири пет. Той казва, че 32-ият знак е мястото, където е намерил януари.
Сега, вместо да го правя в много клетки в колоните, ще натисна F2, ела тук и FIND_TEXT. Забележете, че му дадохме 1 елемент, SEARCH ни даде 1 отговор. Но ако подчертая цялата колона с имена на месеци, сега вместо един елемент поставям много елементи там. Това е аргумент за функция. Поставяме масив от елементи и това означава, че правим операция Array Argument Function. Всеки път, когато правите това, казвате на функцията, хей, дайте ми 12 отговора, по 1 за всеки месец. Сега това ще достави масив, така че ако се опитам да въведа това и да копирам, няма да работи.
Е, нека да слезем до всяка конкретна клетка, F2 и след това F9, за да видим, че да, всъщност тя доставя масив, и погледнете това. Изглежда, че аз F2 тук, забравих да го заключа. Така че ще щракна върху това и F4, Ctrl + Enter, щракнете двукратно и ще го изпратите надолу, F2, F9. Ето, това е този масив. Има точно 12 отговора и има 34 и 55. Сега, от този масив, тъй като ние винаги искаме действителния първи месец, а не втория месец, ние искаме каквото и да е MIN, тъй като това са броят на символите отляво. Така че ще щракна Escape, изкачи се на върха F2. Ще използвам функцията AGGREGATE. Ей, бихме искали да използваме AGREGATE 5, но колкото и да се опитвате, ако имате операция с масив и ние правим тук, ако се опитате да поставите която и да е функция от 1 до 13, тя просто не работи. Но няма проблем,имаме МАЛКИ, така че номер 15 запетая. Всяка една от тези функции от 14 до 19, те разбират операции с масиви. И след като изберете 14 или по-висока, това е екранният съвет, който работите, а не този най-долният с препратките. Добре, запетая.
Вторите опции тук искаме да игнорираме грешки, запетая. Тогава номер 6 ще каже на AGGREGATE да погледне тук и да игнорира грешките. Ще види само цифрите. И това е една от петте функции в Excel: РАЗГЛЕЖДАНЕ на някакъв продукт, ИЗПИТВАНЕ НА КВАДРАТЕН КВАДРАТ, АГРЕГАТ и ИНДЕКС, които всъщност имат специален аргумент, който може да обработва операции с масиви, без да прави специален щрих на клавиша. Така че има масив, запетая и след това за K просто поставяме A1. Това е нашият начин да ги вкараме. Затворете скоби, Ctrl + Enter, щракнете двукратно и го изпратете надолу. И така, това ни казва позицията, в която е намерила името на първия месец от този списък.
Сега ще се справим с грешката NUM в самия край на нашата окончателна формула. Сега ще трябва да ги вземем и да забележим, че понякога има число преди месеца, а понякога, като тук през декември, няма. Така че ще направя същото нещо. Ще се върна с два символа назад и ще проверя дали е буква или в този случай число = MID, има текст, запетая, начална позиция. Е, искам да започна от 32 в този случай и -2, за да се върна към и запетая. Получавам точно един герой. Сега, ако затворя скоби СРЕД НАЛЯВО НАДЯСНО, всички те доставят текст, щракнете двукратно и го изпратете надолу и искаме да проверим дали е число. Така че гледайте това, цялата колона е подчертана. Активна клетка отгоре, ще ударя F2. Можем да направим всяка математическа операция за преобразуване на текстови числа обратно в число, така че аз 'm ще добавите 0, Ctrl + Enter, за да попълните тази редактирана формула надолу през колоната. Ctrl + Enter. Сега можем да зададем въпроса: Върнатият артикул номер ли ли е? F2. Така че сега казвам ISNUMBER, затворете скоби, Ctrl + Enter. Така че сега имаме модел на истински и неверни. Сега, не забравяйте, че трябва да получим началната позиция и за 32 определено ще трябва да извадим 3 и да започнем от тази 20, но забележете тук, не искаме да изваждаме нито една. Така че нашият логически тест, ако ударя F2, той просто ще бъде включен в аргумента за логически тест IF. Ако това излезе True запетая, тогава искам да отскоча 3 запетая. В противен случай искам да отскоча назад 0, да затворя скоби, Ctrl + Enter, за да попълня това докрай. Сега можем да вземем това число и да извадим числото тук, за да ни даде изходната позиция. Активна клетка в горната част F2, I 'поставям това вътре в MID. Там е текстът, запетая. И можете ли да повярвате? Всичко това, за да получите началния номер. Така че ще щракна върху B2 и ще извадя нашата IF, стигна до запетая в края и просто ще сложа голямо число тук, 100, някакво голямо число, достатъчно голямо, за да стигна чак до края, затворете скоби и Ctrl + Enter, за да попълните това докрай. Изглежда, че имаме допълнителни пространства и това има смисъл, защото точно тук се върнахме три назад, така че няма проблем. Активна клетка отгоре, F2, ще използвам функцията за подстригване, диетичната функция. Не, функцията TRIM за премахване на излишни интервали, с изключение на единични интервали между думите. Стигнете до края, затворете скоби, Ctrl + Enter, за да попълните това докрай.И можете ли да повярвате? Всичко това, за да получите стартовия номер. Така че ще щракна върху B2 и ще извадя нашата IF, стигна до запетая в края и просто ще сложа голямо число тук, 100, някакво голямо число, достатъчно голямо, за да стигна чак до края, затворете скоби и Ctrl + Enter, за да попълните това докрай. Изглежда, че имаме допълнителни пространства и това има смисъл, защото точно тук се върнахме три назад, така че няма проблем. Активна клетка отгоре, F2, ще използвам функцията за подстригване, диетичната функция. Не, функцията TRIM за премахване на излишни интервали, с изключение на единични интервали между думите. Стигнете до края, затворете скоби, Ctrl + Enter, за да попълните това докрай.И можете ли да повярвате? Всичко това, за да получите началния номер. Така че ще щракна върху B2 и ще извадя нашата IF, стигна до запетая в края и просто ще сложа голямо число тук, 100, някакво голямо число, достатъчно голямо, за да стигна чак до края, затворете скоби и Ctrl + Enter, за да попълните това докрай. Изглежда, че имаме допълнителни пространства и това има смисъл, защото точно тук се върнахме три назад, така че няма проблем. Активна клетка отгоре, F2, ще използвам функцията за подстригване, диетичната функция. Не, функцията TRIM за премахване на излишни интервали, с изключение на единични интервали между думите. Стигнете до края, затворете скоби, Ctrl + Enter, за да попълните това докрай.някакво голямо число, достатъчно голямо, за да стигнете чак до края, затворете скоби и Ctrl + Enter, за да го попълните докрай. Изглежда, че имаме допълнителни пространства и това има смисъл, защото точно тук се върнахме три назад, така че няма проблем. Активна клетка отгоре, F2, ще използвам функцията за подстригване, диетичната функция. Не, функцията TRIM за премахване на излишни интервали, с изключение на единични интервали между думите. Стигнете до края, затворете скоби, Ctrl + Enter, за да попълните това докрай.някакво голямо число, достатъчно голямо, за да стигнете чак до края, затворете скоби и Ctrl + Enter, за да го попълните докрай. Изглежда, че имаме допълнителни пространства и това има смисъл, защото точно тук се върнахме три назад, така че няма проблем. Активна клетка отгоре, F2, ще използвам функцията за подстригване, диетичната функция. Не, функцията TRIM за премахване на излишни интервали, с изключение на единични интервали между думите. Стигнете до края, затворете скоби, Ctrl + Enter, за да попълните това докрай.функцията TRIM за премахване на излишни интервали с изключение на единични интервали между думи. Стигнете до края, затворете скоби, Ctrl + Enter, за да попълните това докрай.функцията TRIM за премахване на излишни интервали с изключение на единични интервали между думи. Стигнете до края, затворете скоби, Ctrl + Enter, за да попълните това докрай.
Сега имам датата, о, с изключение на NUM. Сега бих могъл да стигна до върха и да използвам IF грешка, но тогава тя ще изпълни всички тези плюс тази клетка точно там, а за малък набор от данни това изобщо няма значение; но с цел ефективност ще кажа IF (ISNUMBER и щраквам върху тази клетка, по този начин затваряме скоби, запетая. Спусъкът за това дали изпълняваме формулата се базира само на това вместо на цялата формула. Ако това излезе True, искаме да изпълним формулата, запетая. В противен случай двойна кавичка двойна кавичка. Този текстов низ с нулева връзка няма да показва нищо. Ctrl + Enter, щракнете двукратно и го изпратете трябва да получим заглавието. Е, вече имам текста, който не искам тук, така че ще използвам функцията ЗАМЕСТИТЕЛ. ЗАМЕСТИТЕЛ, там е текстът, запетая. Старият текст,това е точно там, запетая, новият текст. Ей, искам да го взема и да ЗАМЕНЯ в нищо. Там е нашият текстов низ с нулева връзка, Ctrl + Enter, щракнете двукратно и го изпратете надолу.
Сега ще отида тук до колона Б, щракнете с десния бутон, Скрий и ето. Добре, върни го обратно.
Бил Джелен: Хей, Майк, това е брилянтно и знам точно, къде точно сбърках. Точно тук в ред 12, когато формулата върна грешката #VALUE, натиснахте F2, F9, за да видите, че връща масив. Когато получих грешка #VALUE, просто се заклех малко и казах, защо това не работи? Никога не съм мислил да натискам F2, F9, добре. Също така, разбира се, MIN и SMALL (, 1) са еднакви, но разликата е SMALL (, 1) ще работи с масив във функцията AGGREGATE. Това беше красив, красив трик. И тогава преминах през цялата тази кавга, за да разгледам 2 знака преди и 3 знака преди. Бяхте достатъчно умни, за да кажете: „Хей, ще отидем 2 знака преди и ако да, върнем се 3 знака.“ В най-лошия случай получавате място за това допълнително пространство и елиминирано от TRIM. И после черешата отгоре,използване на функцията SUBSTITUTE, за да се отървете от текста на датата в колона C. Какъв брилянтен, брилянтен начин да отидете, добре
И така, искам да благодаря на всички, че се отбиха. Ще се видим следващия път за поредния дуел на Excel Podcast от и Excel е забавно.
Свали файл
Изтеглете примерния файл тук: Duel180.xlsm
