Algumas das perguntas que surgem são bastante difíceis. Hoje, temos uma coluna de células. Cada célula tem algumas palavras, depois uma data e depois mais algumas palavras. O objetivo é puxar a parte da data desse texto para uma nova coluna. Este é um episódio de duelo com ideias de Bill e Mike.
Assistir vídeo
- Abordagem super ampla de Bill:
- Coloque todos os 12 meses em colunas separadas
- Use a função FIND para ver se este mês está no texto original
- Para encontrar a posição inicial mínima, use = AGGREGATE (5,6,…
- Algumas fórmulas extras para procurar um número 2 ou 3 posições antes do mês
- Abordagem de Mike:
- Use SEARCH em vez de FIND. Localizar diferencia maiúsculas de minúsculas, a Pesquisa não.
- Crie uma operação de matriz de argumento de função especificando B13: B24 como Find_Text.
- A fórmula retorna #VALUE! Erro, mas se você pressionar F2, F9, verá que ele está retornando um array.
- As primeiras 13 funções em AGGREGATE não podem lidar com uma matriz, mas as funções 14-19 podem lidar com uma matriz.
- 5 = MIN e 15 = SMALL (, 1) são semelhantes, mas SMALL (, 1) funcionará com uma matriz.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX e AGGREGATE podem manipular argumentos de matriz de função sem Ctrl + Shift + Enter
- Mike foi mais esperto ao ver se 2 caracteres antes do início são um número e, em seguida, pegou os 3 caracteres antes. O espaço extra é eliminado pelo TRIM ()
- Para obter o Título, use a função SUBSTITUTE para se livrar do texto da Data na coluna C
Transcrição de vídeo
Bill Jelen: Ei, bem-vindo de volta. É hora de outro Podcast de duelo do Excel. Sou Bill Jelen de. Terei a companhia de Mike Girvin, do Excel Is Fun.
Este é nosso Duelo # 170: Encontrando Datas
Ei, bem-vindos de volta a todos. Eu tinha uma pergunta tão boa aqui e não consegui resolvê-la. Pelo menos não consegui resolver isso facilmente, então procurei Mike Girvin e disse: "Mike, ei, você tem uma maneira de fazer isso?" Ele disse: “Sim, eu tenho uma maneira de fazer isso. Vamos fazer um duelo. ”
Então, alguém no YouTube enviou esses dados e cada célula em geral tem algo como o título de um documento seguido por uma data. Eles queriam dividir esses dados no título do documento: o que é, o que é a coisa e então qual é a data. Mas as datas são completamente más. Como aqui, é 20 de janeiro; mas aqui embaixo, há coisas em que a data pode estar após a célula, 9 de abril. Tudo bem, e não importa de que maneira queremos encontrá-lo. E às vezes há duas datas e isso é completamente horrível e é uma situação tão confusa de datas e, como possível, nem mesmo tem uma data marcada, tudo bem. Então, aqui está minha tentativa. No lado direito, colocarei as coisas que procuro. O que eu realmente gosto aqui é que eles nunca abreviavam o nome do mês. Eu realmente,realmente aprecio isso. Portanto, digite janeiro e arrastarei aqui até dezembro assim, e para cada célula que desejo saber podemos encontrar = ENCONTRAR naquele janeiro. Vou pressionar F4 uma, duas vezes para travar em apenas uma linha, no texto ali na Coluna A, assim. Vou pressionar F4 uma, duas, três vezes para travar na coluna, certo. E aqui, está nos dizendo que janeiro é encontrado na posição 32 e pelos outros 11 meses, vai nos dizer que não foi encontrado. Em outras palavras, estamos recebendo o erro de valor agora. O que preciso fazer é encontrar, preciso encontrar o valor mínimo ignorando todos os erros de valor. Então, mostre esta pequena fórmula aqui = AGREGADO e vamos construir do zero, = AGREGADO, o que queremos é o MIN, então esse é o número 5,e, em seguida, Ignore os valores de erro, número 6, vírgula e todas essas células de janeiro a dezembro. E o que isso vai nos dizer é onde o mês acontece. E, neste caso, obteremos 0, digamos que o mês não aconteça.
Tudo bem agora, vamos revelar o resto disso. Então, para lidar com a situação em que temos 20 de janeiro ou 1 de novembro, eu disse que a primeira coisa que vou fazer é ver onde esse mês começa e voltar duas células, duas células, dois caracteres , dois personagens. E veja se isso é um número, não é. Essa é a minha coluna aqui chamada, Adjust2. Adjust2. E aqui está o que vamos fazer. Eu vou dizer, pegue o MID de A2 iniciando em onde em G2-2 para um comprimento de 1, adicione 0 a ele e pergunte, isso é um número ou não? Tudo bem, isso é um número. E então, também procuraremos a situação em que é uma data de 2 dígitos, portanto, 20 de janeiro. Então isso é chamado de Adjust3, volte 3 caracteres de onde. Então há o Where, volte três caracteres para o comprimento de 1, adicione 0 a ele e veja se isso 'um número, certo? Então, vamos ajustar e o Onde Ajustado diz SE. SE este caso estranho for 0, vamos colocar um valor muito grande 999; caso contrário, iremos de G2 e voltaremos 3, se Adjust3 for True ou voltaremos 2 se Adjust2 for True, ou se nenhum desses for True, o Where vai ser onde o mês começa. Tudo bem, agora que sabemos que foi ajustado para onde, clicaremos duas vezes para copiar. Bem, ei agora, é realmente fácil. Vamos apenas - para o Título, vamos dizer vá para a esquerda de A2, quantos caracteres queremos. Queremos D2-1 porque esse é o -1 para eliminar o espaço no final. Embora eu ache que o TRIM também está se livrando do espaço no final.SE este caso estranho for 0, vamos colocar um valor muito grande 999; caso contrário, iremos de G2 e voltaremos 3, se Adjust3 for True ou voltaremos 2 se Adjust2 for True, ou se nenhum desses for True, o Where vai ser onde o mês começa. Tudo bem, agora que sabemos que foi ajustado para onde, clicaremos duas vezes para copiar. Bem, ei agora, é realmente fácil. Nós vamos apenas - para o Título, vamos dizer vá para a esquerda de A2, quantos caracteres queremos. Queremos D2-1 porque esse é o -1 para eliminar o espaço no final. Embora eu ache que o TRIM também está se livrando do espaço no final.SE este caso estranho for 0, vamos colocar um valor muito grande 999; caso contrário, iremos de G2 e voltaremos 3, se Adjust3 for True ou voltaremos 2 se Adjust2 for True, ou se nenhum desses for True, o Where vai ser onde o mês começa. Tudo bem, agora que sabemos que foi ajustado para onde, clicaremos duas vezes para copiar. Bem, ei agora, é realmente fácil. Nós vamos apenas - para o Título, vamos dizer vá para a esquerda de A2, quantos caracteres queremos. Queremos D2-1 porque esse é o -1 para eliminar o espaço no final. Embora eu ache que o TRIM também está se livrando do espaço no final.ou se nenhum desses for True, o Onde vai ser onde o mês começa. Tudo bem, agora que sabemos que foi ajustado para onde, clicaremos duas vezes para copiar. Bem, ei agora, é realmente fácil. Nós vamos apenas - para o Título, vamos dizer vá para a esquerda de A2, quantos caracteres queremos. Queremos D2-1 porque esse é o -1 para eliminar o espaço no final. Embora eu ache que o TRIM também está se livrando do espaço no final.ou se nenhum desses for True, o Onde vai ser onde o mês começa. Tudo bem, agora que sabemos que foi ajustado para onde, clicaremos duas vezes para copiar. Bem, ei agora, é realmente fácil. Nós vamos apenas - para o Título, vamos dizer vá para a esquerda de A2, quantos caracteres queremos. Queremos D2-1 porque esse é o -1 para eliminar o espaço no final. Embora eu ache que o TRIM também está se livrando do espaço no final.s o -1 é para se livrar do espaço no final. Embora eu ache que o TRIM também está se livrando do espaço no final.s o -1 é para se livrar do espaço no final. Embora eu ache que o TRIM também está se livrando do espaço no final.
E então, para a data, vamos usar o MID. MID para MID de A2 começando no Where Ajustado em D2 e saindo 50 ou o que quer que você ache que possa ser, e então a função TRIM, e clicaremos duas vezes para copiar.
Muito bem, procurei o Mike porque disse, gostaria de saber se há uma forma de substituir estas 12 colunas por um único formulário, na verdade estas 13 colunas por um único formulário. Existe alguma maneira de fazer isso usando uma fórmula de matriz? E Mike, é claro, escreveu aquele ótimo livro, Ctrl + Shift + Enter, sobre fórmulas de matriz. E eu tentei algumas coisas diferentes e na minha mente, não havia como fazer. Tudo bem, mas você sabe, vamos perguntar ao especialista. Então Mike, vamos ver o que você tem.
Mike Girvin: Obrigado. E por falar em especialista, isso foi feito com bastante habilidade. Você usou FIND, AGGREGATE, ISNUMBER (MID. Agora, quando você enviou essa pergunta para mim, eu fui em frente e resolvi e é incrível como minha solução é semelhante à sua.
Tudo bem, vou passar para esta folha aqui. Vou começar descobrindo onde está a posição inicial nesta string de texto para cada mês específico. Agora, o que vou fazer é, ei, usar esta função SEARCH. Agora, você usou FIND, eu uso SEARCH. Na verdade, provavelmente FIND é melhor nesta situação porque FIND faz distinção entre maiúsculas e minúsculas, SEARCH não. Agora, normalmente o que fazemos com FIND ou SEARCH, eu digo, ei, vá FIND, janeiro, vírgula dentro desta string de texto maior, é assim que normalmente usamos SEARCH Ctrl + Enter, e conta no dedo: um, dois, três , quatro cinco. Diz que o 32º personagem foi encontrado em janeiro.
Now, instead of doing it in many cells across the columns, I'm going to hit F2, come up here and the FIND_TEXT. Notice we gave it 1 item, SEARCH gave us 1 answer. But if I highlight the entire column of month names, now instead of a single item I put many items in there. This is a Function Argument. We're putting an array of items in and so that means we're doing a Function Argument Array operation. Any time you do that, you tell the function, hey, give me 12 answers, 1 for each month. Now this will deliver an array so if I try to Enter this and copy down it's not going to work.
Well, let's go down to any particular cell, F2 and then F9 to look that yes, in fact, it is delivering an array, and look at that. It looks like I F2 up here, forgot to lock it. So I'm going to click on that and F4, Ctrl+Enter, double click and send it down, F2, F9. There we go, that's that array. There's exactly 12 answers and there's the 34 and the 55. Now, from this array, since we always want the actual first month, not the second month, we want whatever the MIN is because those are number of characters in from the left. So I'm going to click Escape, come up to the top F2. I'm going to use the AGGREGATE function. Hey, we would like to use AGGREGATE 5 but no matter how hard you try if you have an array operation and we do here, if you try to put any function 1 to 13, it just doesn't work. But no problem, we have SMALL, so number 15 comma. Any one of those functions 14 to 19, they understand array operations. And once you select 14 or above, this is the screen tip you're working off, not this bottom one with the references. Alright, comma.
The second options here we want to Ignore errors, comma. That number 6 will then tell AGGREGATE to look through here and ignore the errors. It will only see the numbers. And this is one of five functions in Excel: LOOKUP some product, CHI SQUARE TEST, AGGREGATE, and INDEX that actually have a special argument that can handle Array operations without doing any special key stroke. So there is the Array, comma, and then for K we simply put A1. That's our way of getting them in. Close parentheses, Ctrl+Enter, double click and send it down. And so that tells us the position where it found the first month name from this list.
Now, we'll deal with the NUM error at the very end in our final formula. Now, we are going to have to take these and notice that sometimes there's a number before the month and sometimes, like down here in December, there is not. So I'm going to do the same thing did. I'm going to go back two characters and check whether it is a letter or in this case a number =MID, there’s the text, comma, the starting position. Well, I want to start at 32 in this case and -2 to go back to and comma. I get exactly one character. Now, if I close parenthesis MID LEFT RIGHT they all deliver text, double click and send it down and we want to check if it's a number. So watch this, the whole column is highlighted. Active cell at the top, I'm going to hit F2. We could do any Math operation to convert text numbers back to number so I'm going to add 0, Ctrl+Enter to populate this edited formula down through the column. Ctrl+Enter. Now, we can ask the question: Is the returned item a number? F2. So now I say ISNUMBER, close parenthesis, Ctrl+Enter. So now we have a pattern of Trues and Falses. Now, remember we need to get the starting position and for 32 we're definitely going to have to subtract 3 and start at that 20 but notice down here, we don't want to subtract any. So our logical test if I hit F2, that will simply be put into the IF Logical Test Argument. If that comes out True comma then I want to jump back 3 comma. Otherwise I want to jump back 0, close parenthesis, Ctrl+Enter to populate that all the way down. Now we can take this number and subtract the number over here to give us our starting position. Active cell at the top F2, I'm putting this inside of MID. There's the text, comma. And can you believe it? All of this to get the start number. So I'm going to click on that B2 and subtract our IF, come to the end comma and I'm just going to put a big number in here, 100, some big number big enough to get all the way to the end, close parenthesis and Ctrl+Enter to populate that all the way down. It looks like we have some extra spaces and that makes sense because right here we went back three, so no problem. Active cell at the top, F2, I'm going to use the haircut function, the diet function. No, the TRIM function to remove extra spaces except for single spaces between words. Come to the end, close parenthesis, Ctrl+Enter to populate that all the way down.
Now, I have the date, oh, except for the NUM. Now, I could come to the top and use IF error but then it would run all of these plus that cell right there and for a small data set, it doesn't matter at all; but, with the goal of efficiency, I'm going to say IF(ISNUMBER and I'm going to click on that cell, that way close parenthesis, comma. The trigger for whether we run the formula is only based on that instead of the entire formula. If that comes out True, we want to run the formula, comma. Otherwise, double quote double quote. That zero link text string will show nothing. Ctrl+Enter, double click and send it down. And now, all we need to do is get the Title. Well, I already have the text that I don't want in here so I'm going to use the SUBSTITUTE function. SUBSTITUTE, there's the text, comma. The old text, it's that right there, comma, the new text. Hey, I want to take that and SUBSTITUTE in nothing. There's our zero link text string, Ctrl+Enter, double click and send it down.
Now, I'm going to come over here to column B, right click, Hide and there we go. Alright, throw it back to.
Bill Jelen: Hey, Mike, that is brilliant and I know exactly, exactly where I went wrong. Right here in row 12 when the formula returned the #VALUE error, you pressed F2, F9 to see that it's returning an array. When I got the #VALUE error, I just swore a little bit and said, why isn't this working? Never thought of pressing F2, F9, alright. Also, like that, of course, MIN and SMALL(,1) are the same but the difference is SMALL(,1) will work with an array in the AGGREGATE function. That was a beautiful, beautiful trick. And then, I went through that whole hassle to look at 2 characters before and 3 characters before. You were smart enough to say, “Hey, we're going to go 2 characters before and if so, go back 3 characters.” Worst case you get a space for that extra space and eliminated by the TRIM. And then the cherry on top, using SUBSTITUTE function to get rid of the Date text in column C. What a brilliant, brilliant way to go, alright.
Então, quero agradecer a todos pela visita. Nos vemos na próxima vez para outro Dueling Excel Podcast do e Excel Is Fun.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Duel180.xlsm
