As fórmulas de matriz do Excel são muito poderosas. Você pode substituir milhares de fórmulas por uma única fórmula depois de aprender o truque Ctrl + Shift + Enter. Hoje, uma única fórmula de matriz faz 86.000 cálculos.
Triskaidekaphobia é o medo da sexta-feira 13. Este tópico não curará nada, mas mostrará uma fórmula absolutamente incrível que substitui 110.268 fórmulas. Na vida real, nunca preciso contar quantas sextas-feiras 13 ocorreram na minha vida, mas o poder e a beleza dessa fórmula ilustram o poder do Excel.
Digamos que você tenha um amigo supersticioso sobre a sexta-feira 13. Você quer ilustrar quantas sextas-feiras, dia 13, seu amigo já viveu.
Configure a planilha simples abaixo, com a data de nascimento em B1 e =TODAY()em B2. Em seguida, uma fórmula selvagem em B6 avalia todos os dias que seu amigo esteve vivo para descobrir quantos desses dias eram sexta-feira e caíram no dia 13 do mês. Para mim, o número é 86. Nada a temer.
A propósito, 17/02/1965 realmente é meu aniversário. Mas não quero que você me envie um cartão de aniversário. Em vez disso, no meu aniversário, quero que me deixe explicar como funciona essa fórmula incrível, um pequeno passo de cada vez.
Você já usou a função INDIRETO? Quando você solicitar =INDIRECT("C3"), o Excel irá para C3 e retornará o que quer que esteja naquela célula. Mas INDIRETO é mais poderoso quando você calcula a referência da célula em tempo real. Você pode configurar uma roda de prêmios onde alguém escolhe uma letra entre A e C e, em seguida, escolhe um número entre 1 e 3. Ao concatenar as duas respostas, você terá um endereço de célula, e o que quer que esteja naquele endereço de célula é o prêmio . Parece que ganhei um álbum de fotos em vez da estadia no resort.
Você sabe como o Excel armazena datas? Quando o Excel mostra 17/02/1965, ele está armazenando 23790 na célula, porque 17/02/1965 foi o 23790º dia do século XX. No centro da fórmula está uma concatenação que une a data de início, dois pontos e a data de término. O Excel não usa a data formatada. Em vez disso, ele usa o número de série nos bastidores. Então B3&":"&B4se torna 23790: 42167. Acredite ou não, essa é uma referência de célula válida. Se quiser somar tudo nas linhas 3 a 5, você pode usar =SUM(3:5). Portanto, quando você passa 23790: 42167 para a função INDIRETO, ele aponta para todas as linhas.
A próxima coisa que a fórmula do assassino faz é pedir o ROW(23790:42167). Normalmente, você passa uma única célula: =ROW(D17)é 17. Mas, neste caso, você está passando por milhares de células. Quando você pede ROW(23790:42167)e termina a fórmula com Ctrl + Shift + Enter, o Excel realmente retorna todos os números de 23790, 23791, 23792 e assim por diante até 42167.
Esta etapa é a etapa incrível. Nesta etapa, partimos de dois números e “destacamos” um array de 18.378 números. Agora, temos que fazer algo com esse conjunto de respostas. A célula B9 da figura anterior apenas conta quantas respostas obtivemos, o que é enfadonho, mas prova que ROW(23790:42167)está retornando 18.378 respostas.
Vamos simplificar drasticamente a pergunta original para que você possa ver o que está acontecendo. Nesse caso, encontraremos o número de sextas-feiras em julho de 2015. A fórmula mostrada abaixo em B7 fornece a resposta correta em B6.
No centro da fórmula está ROW(INDIRECT(B3&":"&B4)). Isso retornará as 31 datas em julho de 2015. Mas a fórmula então passa essas 31 datas para a WEEKDAY(,2)função. Esta função retornará 1 para segunda-feira, 5 para sexta-feira e assim por diante. Portanto, a grande questão é quantas dessas 31 datas retornam 5 quando passadas para a WEEKDAY(,2)função.
Você pode observar o cálculo da fórmula em câmera lenta usando o comando Avaliar Fórmula na guia Fórmula da faixa de opções.
Isso ocorre depois que INDIRECT converte as datas em uma referência de linha.
Na próxima etapa, o Excel está prestes a passar 31 números para a função WEEKDAY. Agora, na fórmula matadora, ele passaria 18.378 números em vez de 31.
Aqui estão os resultados das 31 funções WEEKDAY. Lembre-se, queremos contar quantos são 5.
Verificar se o array anterior é 5 retorna um monte de valores True / False. Existem 5 valores verdadeiros, um para cada sexta-feira.
Não posso mostrar o que acontece a seguir, mas posso explicar. O Excel não pode somar um monte de valores True e False. É contra as regras. Mas se você multiplicar esses valores True e False por 1 ou se usar a função duplo-negativa ou N (), você converterá os valores True para 1 e os valores False para 0. Envie-os para SUM ou SUMPRODUCT, e você irá obter a contagem dos valores verdadeiros.
Aqui está um exemplo semelhante para contar quantos meses têm um dia 13. É trivial pensar nisso: todo mês tem um 13, então a resposta para um ano inteiro é melhor 12. O Excel está fazendo as contas, gerando 365 datas, enviando todas para a função DAY () e descobrindo quantas terminam até no dia 13 do mês. A resposta, como esperado, é 12.
A próxima figura é uma planilha que faz toda a lógica da fórmula one killer mostrada no início deste tópico. Criei uma linha para cada dia em que estou vivo. Na coluna B, obtenho o DIA () dessa data. Na coluna C, obtenho o WEEKDAY () da data. Na coluna D, B é igual a 13? Na coluna E, C = 5? Em seguida, multiplico D * E para converter True / False em 1/0.
Eu escondi muitas linhas, mas mostro três dias aleatórios no meio, que por acaso são uma sexta e o dia 13.
O total em F18381 é o mesmo 86 que minha fórmula original retornou. Um ótimo sinal. Mas esta planilha tem 110.268 fórmulas. Minha fórmula matadora original faz toda a lógica dessas 110.268 fórmulas em uma única fórmula.
Esperar. Eu quero esclarecer. Não há nada mágico na fórmula original que torna mais inteligente e encurta a lógica. Essa fórmula original realmente está executando 110.268 passos, provavelmente até mais, porque a fórmula original tem que calcular o array ROW () duas vezes.
Encontre uma maneira de usar isso ROW(INDIRECT(Date:Date))na vida real e envie para mim por e-mail (pub at ponto com). Vou mandar um prêmio para as primeiras 100 pessoas que responderem. Provavelmente não é uma estadia no resort. Mais provavelmente um Big Mac. Mas é assim que acontece com os prêmios. Muitos Big Macs e poucas estadias em resorts.
Vi essa fórmula pela primeira vez postada no Quadro de Mensagens em 2003 por Ekim. O crédito foi dado a Harlan Grove. A fórmula também apareceu no livro de Bob Umlas This Isn't Excel, It's Magic. Mike Delaney, Meni Porat e Tim Sheets sugeriram o truque menos / menos. SUMPRODUCT foi sugerido por Audrey Lynn e Steven White. Obrigado a todos.
Assistir vídeo
- Há uma classe secreta de fórmulas chamada Fórmulas de matriz.
- Uma fórmula de matriz pode fazer milhares de cálculos intermediários.
- Eles geralmente exigem que você pressione Ctrl + Shift + Enter, mas nem sempre.
- O melhor livro sobre fórmulas de matriz é Ctrl + Shift + Enter de Mike Girvin.
- INDIRETO permite usar concatenação para construir algo que se parece com uma referência de célula.
- As datas são bem formatadas, mas são armazenadas como um número de dias desde 1º de janeiro de 1900.
- A concatenação de duas datas apontará para um intervalo de linhas no Excel.
- Perguntar pelo
ROW(INDIRECT(Date1:Date2))"aparecerá" uma matriz de muitos números consecutivos - Usando a função WEEKDAY para descobrir se uma data é sexta-feira.
- Quantas sextas-feiras ocorrem neste julho?
- Para assistir ao cálculo de uma fórmula em câmera lenta, use a ferramenta Avaliar fórmula
- Quantos 13 dias ocorrem este ano?
- Quantas sexta-feira 13 ocorreram entre duas datas?
- Verifique cada data para ver se o WEEKDAY é sexta-feira
- Verifique cada data para ver se o DIA é 13
- Multiplique esses resultados usando SUMPRODUCT
- Use - para converter Verdadeiro / Falso em 1/0
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2026 - Minha fórmula favorita em todo o Excel!
Para fazer o podcast de todo o livro, clique no “i” no canto superior direito para acessar a lista de reprodução!
Certo, era o 30º tópico do livro, estávamos meio que no final da seção de fórmulas, ou no meio da seção de fórmulas, e eu disse que tinha que incluir minha fórmula favorita de todos os tempos. Esta é uma fórmula incrível, quer você tenha que contar o número Friday the 13th ou não, ela abre um mundo em toda uma área secreta do Excel chamada Array Formulas! Insira uma data de início, insira uma data de término e esta fórmula calcula o número de sexta-feira 13 que ocorreu entre essas duas datas. Na verdade, ele está fazendo cinco cálculos em cada dia entre essas duas datas, 91895 cálculos + SUM, 91896 cálculos acontecendo dentro desta pequena fórmula, certo. Agora, no final deste episódio, você ficará muito intrigado com as fórmulas de array. Eu quero apontar,meu amigo Mike Girvin tem o melhor livro sobre fórmulas de array chamado “Ctrl + Shift” Enter ”, esta é uma impressão recente com a capa azul, costumava ser uma capa amarela e verde. Agora, qualquer que você conseguir, é um grande livro, com o mesmo conteúdo tanto no amarelo quanto no verde.
Tudo bem, vamos começar por dentro disso, com uma fórmula que você pode não ter ouvido falar chamada INDIRETA. INDIRETO nos permite concatenar, ou de alguma forma construir um pedaço de texto que se parece com uma referência de célula. Tudo bem então, digamos que temos uma roda de prêmio aqui, e eu apenas pedi para você escolher entre A, B e C. Tudo bem, então você escolhe este e escolhe C e, em seguida, escolha este e escolha 3, tudo bem, e seu prêmio é uma estadia em resort, porque é isso que está armazenado no C3. E a fórmula aqui é concatenada com qualquer coisa desde C5 e qualquer coisa em C6 usando o & e depois passando para o INDIRETO. Então = INDIRETO (C5 & C6), neste caso, é C3, que tem que ser uma referência balanceada. INDIRETO diz "Ei, vamos para C3 e retornar a resposta a partir disso, certo?" No Lotus 1-2-3, era chamada de função @@,no Excel, eles o renomearam como INDIRETO. Tudo bem, então você tem no INDIRETO, agora aqui está a coisa incrível que está acontecendo lá dentro.
Temos duas datas, como o Excel armazena datas, 17/02/1965, isso é apenas formatação. Se olharmos para o número real por trás disso, é 23790, o que significa que são 23790 dias desde 01/01/1900 e 42167 dias desde 01/01/1980. Em um Mac, será desde 01/01/1904, portanto, as datas serão cerca de 3000. Certo, é assim que o Excel armazena, mas está nos mostrando, graças a esse formato de número como uma data, mas se concatenássemos B3 e a: e B4, na verdade nos daria os números armazenados nos bastidores. Portanto, = B3 & ”:” & B4, e se passarmos isso para INDIRETO, na verdade vai apontar para todas as linhas de 23790 a 42167.
Então tem o INDIRETO do B6, eu pedi o ROW disso, isso vai me dar um monte de respostas, e descobrir quantas respostas eu usei conta, certo. E para fazer isso funcionar, se eu apenas pressionar Enter, não funciona, tenho que segurar Ctrl e Shift e pressionar Enter, e veja, isso adiciona o () ao redor da fórmula aqui. Diz ao Excel para ir para o modo de super fórmula, modo de fórmula de matriz, e fazer toda a matemática para tudo que saiu dessa matriz, 18378, certo. Então, esse é um truque incrível, indireto de data1: data2, passe para a função ROW, e aqui está um pequeno exemplo.
Então, queremos apenas descobrir quantas sextas-feiras ocorreram neste mês de julho, aqui está uma data de início, aqui está uma data de término e, para cada uma dessas linhas, vou pedir o WEEKDAY. WEEKDAY nos diz em que dia da semana é, e aqui no, 2 argumento Sextas-feiras terá um valor de 5. Portanto, estou procurando a resposta e vamos escolher esta fórmula, vá para Fórmulas, and Evaluate Formula, and Evaluate Formula é uma ótima maneira de ver uma fórmula sendo calculada em câmera lenta. Portanto, há o B3, o primeiro de julho, e você vê que muda para um número, e então juntamos os dois pontos, certo, há o B4, que vai mudar para um número, e agora temos o texto, 42186: 42216 Neste ponto, passamos isso para o ROW, e aquela pequena expressão simples vai se transformar em 31 valores bem aqui.
Agora, no exemplo onde eu tinha tudo de 1965 a 2015, apareceria 86.000 valores, certo, e você não quer fazer isso e avaliar a fórmula, porque seria meio insano, certo? Mas você pode ver o que está acontecendo aqui com o 31, e agora estou passando esses 31 dias para a função WEEKDAY e temos 3-4-5. Portanto, 3 significa que foi uma quarta-feira, 4 significa que foi uma quinta-feira e 5 significa que foi uma sexta-feira. Pegue todos esses 31 valores e veja se eles são = 5, que é uma sexta-feira, e teremos um monte de FALSOS e VERDADEIROS, então quarta, quinta, sexta e 7 células depois seria o próximo VERDADEIRO, impressionante!
Tudo bem, então neste caso temos 5 TRUEs e 26 FALSEs. Para adicioná-los, preciso converter o FALSE em 0 e os TRUEs em 1, e uma maneira muito comum de fazer isso é usar - . Tudo bem, infelizmente não mostrou a resposta ali, onde vimos um monte de 1s e 0s, mas isso realmente é o que acontece, e então SUMPRODUCT adiciona e nos leva ao 5. Aqui, se quisermos descobrir quantos dias do dia 13 do mês houve este ano, desta data de início até a data de término, um processo muito semelhante. Embora tenhamos 365, passe para a função DAY e verifique quantos são 13, certo. Para o exemplo de 92000 linhas, você sabe, estamos obtendo o dia, estamos obtendo o dia da semana, verificando se o, DAY = 13, verificando se o WEEKDAY = FALSE, multiplicando isso * isto,e somente nos casos em que é uma sexta-feira 13 isso acaba sendo VERDADEIRO. O SUMPRODUCT então diz “Some tudo isso”, e é assim que obtemos os 86, literalmente 91895 cálculos + SUM, 91896 acontecendo dentro desta fórmula, é muito poderoso! Vá comprar o livro do Mike, é um livro incrível, vai abrir um mundo inteiro de fórmulas do Excel para você e, na verdade, você deve apenas comprar os dois livros. Compre meu livro, compre o livro de Mike, e você terá uma coleção incrível que o ajudará pelo resto do ano.ele abrirá um mundo inteiro de fórmulas do Excel para você e, na verdade, você deve apenas comprar os dois livros. Compre meu livro, compre o livro de Mike, e você terá uma coleção incrível que o ajudará pelo resto do ano.ele abrirá um mundo inteiro de fórmulas do Excel para você e, na verdade, você deve apenas comprar os dois livros. Compre meu livro, compre o livro de Mike, e você terá uma coleção incrível que o ajudará pelo resto do ano.
Tudo bem, recapitule: há uma classe secreta de fórmulas chamada fórmulas de matriz, e a fórmula de matriz pode fazer milhares de cálculos intermediários. Eles geralmente exigem que você pressione Ctrl + Shift + Enter, mas nem sempre, e o melhor livro sobre fórmulas de matriz é o livro “Ctrl + Shift + Enter” de Mike Girvin. Tudo bem, então INDIRETO permite usar concatenação para construir algo que se parece com uma referência de célula e, em seguida, INDIRETO vai para essa referência de célula. Concatenar duas datas com dois pontos apontará para um intervalo de linhas no Excel e, em seguida, solicitar a LINHA do INDIRETO de data1: data2 aparecerá uma matriz de muitos números consecutivos, talvez 31, talvez 365 ou talvez 85000. Marque cada data para ver se WEEKDAY = Friday, verifique cada dia para ver se DAY = 13, multiplique essas duas matrizes de TRUEs e FALSEs usando o SUMPRODUCT. Em muitos casos, nós 'Vou usar - para converter TRUE / FALSE em 1's e 0's para permitir que o SUMPRODUCT funcione. É uma fórmula incrível, não fui eu que a criei, encontrei-a no quadro de mensagens, enquanto trabalhava nela, e fiquei tipo "Uau, isso é muito legal!"
Certo, quero agradecer a sua visita, nos vemos na próxima vez para outro netcast do!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2026.xlsx
