Fórmulas e funções do Excel Parte 9: fórmulas de matriz

1. Introdução: fórmulas de matriz: Muito bem, bem-vindos à Seção 9. Estamos na reta final agora, e esta seção vai falar sobre uma nova raça de fórmula chamada fórmula array . E para aqueles de vocês que nunca trabalharam com as fórmulas de ray, eles operam um pouco diferente das fórmulas não-árias que eles têm uma sintaxe ligeiramente incomum e uso que leva algum tempo para se acostumar. Então há um pouco de uma curva de aprendizagem lá. Mas uma vez que você se sentir confortável e aprender a usá-los, você vai começar a ver que eles podem abrir a porta para fazer algumas coisas que você realmente não pode fazer outra maneira. Então vamos começar a seção com as regras de fórmulas de array, como usá-las e quais são alguns dos prós e contras comuns das fórmulas de array. Vamos falar sobre constantes verticais, horizontais e bidimensionais de matriz. E, em seguida, como usar essas constantes fórmulas como usar chamado um raise. Então vamos para um monte de demos e nos destacamos. Comece com transposto. Vou falar sobre a ligação de dados entre folhas. Vou mostrar-lhe como usar a abordagem não Array versus array. Comparar e contrastar com isso. Vamos falar sobre como retornar os X maiores valores de organizar em uma única etapa. Vamos usar a função Len que contam caracteres em um intervalo de células e por último, mas não menos importante, irá criar um máximo personalizado se função, como de costume, para arquivos para baixar. Excel para analistas, fórmulas de matriz que pdf e uma Seção nove ou um arquivo de fórmulas Excel. Então vá em frente e abra isso e vamos acertar. 2. Regras de funções de matriz: Ok, então o que diabos está certo? Funções? Basicamente, eles são projetados para executar vários cálculos em um ou mais itens em um array. O que os torna muito diferentes das funções normais das células é que as funções de raio podem assumir a forma de uma fórmula de célula única, caso em que elas existem dentro de uma célula ou uma fórmula multi-célula, caso em que é a mesma fórmula aplicado através de um número de células. Nota muito importante é que você deve pressionar controle, deslocamento, deslocamento, entrar dedo do pé, entrar, editar ou excluir qualquer fórmula de matriz. Se você não pressionar o controle, Shift enter. Se você apenas pressionar enter como você está acostumado, estes não vai funcionar tão vale a pena repetir novamente. Pressione Shift de controle. Insira toda vez que você inserir, editar ou excluir uma fórmula de matriz. Quando você fizer isso, você verá que sobressai automaticamente entre parênteses em torno de sua fórmula, começando antes do sinal de igual e indo até o final da sua fórmula, que indica que a função se aplica a uma matriz. Então vamos dar uma olhada em um exemplo. Se eu fosse selecionar o intervalo de D a três d cinco aqui e digitar a fórmula ser através de cinco vezes C dois a C cinco e apertar Enter. A fórmula só será aplicada para vender D para Se eu selecionar esse mesmo tipo de intervalo exatamente a mesma fórmula e pressionar o deslocamento de controle entrar. Eu fui criado fórmula Honore e isso se aplica a todas as células na faixa que eu tinha destacado. Como podem ver, os resultados são preenchidos em todas as quatro linhas dentro do meu raio. Então, regras de funções de matriz infelizmente, há alguns em você deve obedecer a todos eles, a fim de usá-los corretamente. O primeiro, como eu mencionei várias vezes agora é que você deve pressionar o controle shift enter como às vezes abreviado como C S E. Algumas pessoas chamam de funções de raio. Funções CSE por esse motivo para editar ou inserir André Formula Regra dois Para funções de matriz de várias células , você deve selecionar o intervalo de células antes de inserir a fórmula. Então, o primeiro passo é entender o intervalo de células que você deseja que sua fórmula seja aplicada e , em seguida, o passo dois é realmente escrever a fórmula. Regra número três. Esta é uma importante. Não é possível alterar o conteúdo de qualquer célula individual que faça parte de uma fórmula de matriz. É um pouco rigoroso e que assim que você escreve em uma fórmula de raio que é específica para um array, você não pode inserir Rose. Você não pode, uh, apagar valores. Você não pode mover células se elas fizessem parte dessa matriz. Regra quatro. Você pode mover ou excluir uma fórmula de array inteira, mas não uma parte dela. Muitas vezes. Se sua matriz precisa mudar, você tem que excluir sua fórmula de array inteira e reconstruir do zero e, em seguida, por último, mas não menos importante número cinco. Não é possível inserir células em branco ou excluir células de uma fórmula de matriz de várias células, então pressione a linha inferior Shift enter. Selecione o intervalo de células antes de inserir a fórmula. E certifique-se de que seu array não vai mudar uma vez que você escreveu a fórmula. Então essas são as regras de um raio funções. Em poucas palavras. 3. Prós e cones de Funções de matriz: Tudo bem, então você já pode começar. A sensação de que as funções de Ray, embora possam ser incrivelmente poderosas, também pode ser um desastre total para trabalhar, especialmente como você primeiro se familiarizar com elas. Eles tendem a ser um pouco mais rígidos e rígidos do que as funções não-array, então a curva de aprendizagem é um pouco mais íngreme aqui. Independentemente disso, vamos dar uma olhada em alguns dos prós e contras comentários no lado pro. Primeiro, elas são realmente ótimas ferramentas para condensar vários cálculos em uma fórmula, que tem o benefício adicional de reduzir frequentemente os tamanhos dos arquivos. Em segundo lugar, ele pode executar algumas funções complexas que não fórmulas de matriz não podem. Vou mostrar-lhe alguns exemplos que demonstram isso, e em terceiro lugar, eles ajudaram a reduzir o risco de erro humano, como excluir acidentalmente partes de um aumento ou perda de fórmulas de digitação no lado. Pode ser muito difícil modificar ou excluir fórmulas de array existentes. Pode ser meio desajeitado para trabalhar. Você tem visibilidade limitada na função de fórmulas, especialmente para usuários que não estão familiarizados com os raios. Então, ele só faz um pouco mais complicado, toa que maneira de solucionar problemas e identificar como combinações de funções podem estar funcionando. Em terceiro lugar , elimina a opção de modificar as células contidas em um raise. Então, se você tem um conjunto de dados que tende a mudar um pouco, são um funções pode ser muito difícil de trabalhar com. E por último, mas não menos importante, eles podem realmente reduzir a velocidade de processamento se várias funções de matriz são usadas tão pessoalmente no trabalho que eu faço, eu me encontro trabalhando com um dado muito dinâmico que está constantemente mudando e atualizando, Nesse caso, há muitos casos que são funções de raio não são o ajuste certo para mim. Mas isso dito, há certamente oportunidades onde eu usei funções de array porque não há outra opção que poderia ter feito a mesma coisa. Então, no final do dia, é realmente apenas uma função de quais dados você está trabalhando e o que você está tentando realizar quando se trata de usar funções de array versus funções não-array. Então, com isso, vamos falar sobre os diferentes tipos de taxa e aumento nomeado, e então vamos saltar direto para o excel 4. Constantes de matriz vertical, horizontal e 2-Dimensional: Ok, então as constantes de raio são um aumento que são criadas inserindo manualmente uma lista de itens diretamente na barra de fórmulas e cercando essa lista com colchetes. Então, há três tipos. Há horizontal ou uma constante, que cria matriz contida dentro de uma única corda, aqueles delimitados por vírgulas. Então o que você faz é selecionar a matriz horizontal de células que você deseja criar neste caso , um 1 31 Se quiséssemos que essa matriz para preencher com os números 123 e quatro nós éramos tipo iguais. Abra o suporte uma vírgula, duas vírgulas, três vírgula quatro. Feche o colchete e pressione Shift controlado Enter. É importante notar que você tem que digitar manualmente este conjunto de colchetes e, quando você pressiona control shift, enter. Excel irá então adicionar um segundo par de colchetes em torno de toda a coisa. Então essa é a horizontal ou uma matriz vertical constante. Constance funciona da mesma forma que você apenas seleciona sua matriz vertical de células, e então você delimita seus valores por vírgulas em vez de vírgulas. Então, se fôssemos preencher uma matriz de um a um quatro com os valores 1234 nós éramos tipo igual entre colchete aberto um ponto-e-vírgula para ponto-e-vírgula, três ponto-e-vírgula quatro. Feche o suporte e, em seguida, pressione a tecla Control Shift. Digite E por último, mas não menos importante, você pode combinar constantes de matriz horizontal e vertical para criar constantes de taxa bidimensional . Então, para escrever uma constante de matriz bidimensional para criar uma do zero, você basicamente insere seus valores da mesma maneira que você leria um livro começando no canto superior esquerdo cada linha movendo-se da esquerda para a direita. Então você lista os valores sequenciais de colunas auxiliar B para C para D, separados por vírgulas e, em seguida, usar um ponto semi dois-pontos para indicar um salto para a próxima linha. Então, neste caso, temos os valores de um a oito e desde 123 e quatro fora, todos na mesma linha. Aqueles ar separados com vírgulas e, em seguida, o semi cólon indica que o cinco está começando na segunda linha de volta e coluna a então novamente pressione a mudança de controle. Digite e sua matriz será preenchida. Então vamos saltar para o Excel. Vou abrir um trabalho em branco. Ela só tem um pouco de prática trabalhando com isso. Então vamos selecionar um 1 31 ir para a barra de fórmulas e tomará igual suporte aberto uma vírgula , duas vírgulas três comuns para fechar o colchete. E se eu pressionar Enter agora, você verá que o valor é preenchido e venderá um. O número um mas B um C um e d um permanecem inalterados. O problema é que eu não apertei o controle Shift, enter. E quando eu faço isso agora você pode ver que ele é adicionado o segundo par de colchetes em torno da minha função, e ele é preenchido o array que eu tinha selecionado com os valores que eu tinha entrada aqui. Então é assim que se cria uma matriz horizontal básica. Vamos dar uma olhada em como editar e excluir que organizar. Então o desafio é, você sabe, agora que eu tenho minha função de matriz escrita, se eu quiser mudar o Ford de cinco, eu não posso simplesmente mudar o valor da célula porque é parte deste array. Então pressione cancelar. , Da mesma forma, não posso entrar na barra de fórmulas e mudá-la lá pela mesma razão. Então o que eu preciso fazer é basicamente selecionar a matriz inteira, ir para a fórmula, mudar o Ford de cinco e, em seguida, pressionar control shift enter novamente para atualizar a matriz quando se trata de excluir a matriz. Um caso semelhante. Não posso apagar células individuais. O que tem a fazer é selecionar a matriz inteira e excluir os valores dessa forma, caso em que as funções desapareceram. Ou vou desfazer isso. Ele pode selecionar a matriz, ir para a barra de fórmulas, excluir a fórmula. E agora aqui está a pegadinha. Mesmo que não haja fórmula escrita lá pela imprensa enter, nada acontece. Então eu posso continuar batendo. Digite mais e mais e mais, e nada vai acontecer depois de pressionar controle, shift, enter mesmo quando eu exclui a função para limpar completamente a matriz que eu acabei de criar. Então, novamente, curva de aprendizagem é um pouco íngreme. Uma vez que você começa a jogar com isso, ele começa a se tornar um pouco mais natural, mas novamente, definitivamente muito mais rígida e rígida do que as fórmulas celulares tradicionais. Então vamos criar uma matriz vertical. Você seleciona um 134 indo para barra de fórmulas é igual a colchete aberto. Um. vírgula para ponto e vírgula Três semi-cone. Quatro. Lembra-se dos dois pontos, ou o quê? Indique ou delimite diferentes valores que caem em rosa individual. Então feche a mudança de controle do suporte Enter e lá vai você. É preenchido Mireille em células 134 Então novamente para apagar isso, apague a função, pressione controle, shift, enter e ele se foi. Então vamos fazer mais um exemplo. Vamos fazer uma matriz bidimensional de, Ah, Ah, um até Be for So, neste caso, digite parêntese igual. E lembre-se, é como se estivesse lendo um livro. Então ele vai começar com qualquer valor que eu quiser em um seguido de B um de A a B para um três B três um quatro antes. Então por que não fazemos números sequenciais de novo? Então um e, em seguida, comentário leva-me para o próximo Valor Colin, que é para semi cólon salta para baixo para a próxima linha. Três. Comum para ponto e vírgula 56 vem perto. O deslocamento de controle do suporte entra e o que é necessário? R. Eu precisava de mais um par de números para isso preencher o array completo que eu tinha selecionado, então este é um bom exemplo. Quando nós apenas voltar para a função, adicionar mais um semi cólon sete comum oito deslocamento de controle, entrar e lá vai você. Essa é a matriz adequada. Então horizontal, vertical e bidimensional corrida 5. Como usar Constantes de matriz em fórmulas: então duas coisas importantes a observar sobre uma taxa constante. A primeira é que eles não têm apenas que conter valores como eu tenho mostrado a vocês. Eles também podem conter texto entre aspas ou até mesmo valores lógicos ou valores de erro em segundo lugar, vez de serem usados apenas para preencher células lá mais frequentemente usadas como parte de fórmulas de raio . Então, olhando para este exemplo aqui, como você pode dizer, há uma constante de matriz aninhada em uma fórmula de raio maior. E na verdade são quatro coisas acontecendo aqui. Temos a nossa função uma matriz armazenada, que é apenas a referência a um um a um cinco, o operador de multiplicação e, finalmente, nossa constante de raios cercados colchetes. E porque esses números são valores são delimitados por dois pontos e vírgula. Eu sei que isto é uma constante de matriz vertical. Então, tudo o que esta função está fazendo é pegar cada valor na matriz de um a um cinco e multiplicá-lo contra seu valor correspondente na constante de matriz que nós aninhamos aqui. Então, basicamente, a função alguma é apenas adicionar ou agregar todos esses produtos, então cinco vezes um oito vezes 22 vezes 37 vezes quatro e três vezes cinco para chegar em um total de 70 dicas eso aqui. Como mencionei, esses colchetes em torno da constante de taxa são adicionados manualmente. Os colchetes externos são adicionados uma vez que você pressionar control shift enter para converter isso em uma função de matriz. Certo, então vamos pular para o Excel, praticar um pouco com isso. Primeiro, vou criar uma matriz vertical. Constante. Quero dizer, tipo é igual a colchete. 12345 Feche a mudança de controle do suporte, Enter. E agora eu vou selecionar B um para B cinco e eu vou criar um problema de multiplicação . Isso vai ser apenas os valores de um a um cinco. Essa é a minha história. Tempos de matriz. E agora eu vou aninhar uma nova matriz vertical dentro desta fórmula em vez de criar uma separadamente e, em seguida, referindo-se às células que ela é preenchida. Só vou trabalhar na fórmula. Então 10 ponto-e-vírgula. 20 30 40 50. Tenha em mente que se eu colocar comentários aqui, isso iria estragar tudo porque ele vai estar tentando multiplicar valores movendo-se para baixo, linha por linha por valores movendo-se sobre chamar minha coluna e as coisas não se alinham da maneira que eu quero . Então agora estou criando uma matriz com as mesmas dimensões de um a um cinco. Eu sou para que eu tenha um tipo limpo de relacionamento 1 para 1. Feche a mudança de controle do suporte, digite para terminar isso. E como você pode ver, isso parece funcionar corretamente. Basicamente, o que está fazendo é dizer um um vezes 10 que é 10 um dois vezes 22 vezes vinte, 40 três vezes, trinta, 94 vezes, quarenta, 160 finalmente, cinco vezes 50 é para 50. Então, como você pode ver, eu salvei um passo apenas inserindo esta constante na função array. Então, na próxima seção, vamos falar sobre como realmente nomear esses arrays eso que eu não tenho que escrever 10 2030 40 50 do zero. Se eu vou usá-lo uma e outra vez, eu posso dar-lhe um nome e, em seguida, apenas digite o nome na fórmula da mesma forma que usamos chamado um aumento em algumas das seções anteriores. 6. Constantes de matriz nomeada: Tudo bem, assim como intervalos de células normais, você pode atribuir um nome a uma determinada constante de matriz. A única diferença é que na caixa refere-se a, você escreve-a exatamente como você faria se você estiver tentando preencher células, então você adiciona os colchetes e delimita por dois pontos e dois pontos. Se for uma matriz vertical, vírgulas se for uma matriz horizontal ou uma combinação de ambas. Se for um raio bidimensional. Então tudo que você precisa fazer é apenas ir em fórmulas, nome, gerente ou definir nome. Criar um nome para o seu uma constante e, em seguida, digitá-lo exatamente como você faria normalmente na caixa refere-se a e saber que você não tem que pressionar shift control enter aqui. Você apenas digitá-lo com os colchetes e pressione OK que irá armazenar a constante de matriz com nome para que você possa preencher células apenas digitando a matriz nomeada. Ou você pode substituir a constância da matriz com o nome Array. Se ele vive dentro de uma fórmula. Eso, mesmo quando você está apenas digitando o nome Furnari Constant, você ainda tem que pressionar Control Shift Enter para que ele preencha, então ele está pulando para se destacar e praticar um pouco com isso, hum, novamente, Só estou trabalhando com a pasta de trabalho em branco por enquanto. Hum, por que não começamos criando, você sabe, matriz vertical padrão de um a cinco é igual a colchete. 12345 Deve ser muito familiar até agora. Deslocamento de controle entrar. Então essa é uma maneira de adicionar o array. E como você pode ver, ele é armazenado como uma constante de matriz com cada um dos cinco valores. Agora o que eu poderia fazer é ir em fórmulas para encontrar o nome, dar-lhe um nome chamado Numbers e, em seguida, aqui, onde ele diz refere-se a excluir tudo após o sinal de igual, digite o colchete e, em seguida, exatamente a mesma coisa. Um. Ponto-e-vírgula 2345 Feche o suporte pressionado. OK, e agora, se eu selecionar ser ganho através de ser cinco e no tipo de barra de fórmulas é igual a números, você pode ver a excelência sinalizada porque ele sabe que há um nome para Ray chamado números. Eu poderia pressionar control shift enter e ele vai aplicar esse array para o meu intervalo selecionado. Então vamos fazer a mesma coisa. Exceto que em vez de usar números, vamos criar uma matriz nomeada com texto. Alguém chama este texto, e ele vai se referir a outra matriz vertical que vai estar entre aspas. Um semi cólon para semi cólon três semi cólon. Desculpe, bloqueando você por química e cinco. Então, apenas certifique-se de que seu torno de cada um dos seus valores cita que você está cercando a coisa toda com colchetes. Então pressione OK. Ele salvou que chamado Array como texto, Agora em C um a C cinco, Há tipo igual a texto poderia ver que é reconhecido o controle. Turno. Digite e ele irá aplicar a matriz de texto lá. Então agora, apenas para usar aqueles armazenados ou nomeados raise como parte de uma função, ele realmente funciona da mesma maneira. Então vamos dizer que temos apenas cinco números aleatórios em uma matriz vertical de um sete a um 11 . Hum, o que eu poderia fazer B sétimo ou ser 11 se eu quiser fazer um problema de multiplicação semelhante que fizemos na última palestra, mas usar ou se referir a matriz sem nome em vez de uma matriz manual embutida , ele poderia fazer isso então digite é igual a 73 11 vezes. E então aqui é onde nós teríamos feito você Ninguém ponto-e-vírgula 2345 Então é assim que nós costumávamos fazer isso. Agora é editar que, em vez de colocar a matriz manual lá, vamos apenas números corretos. Então um 7 a 11 vezes os números são uma mudança de controle. Entre e teremos a mesma resposta. Portanto, há apenas alguns exemplos de como usar matrizes nomeadas com constantes de taxa para tornar sua vida um pouco mais fácil. A seguir estavam prestes a mergulhar no Excel e andar lá um monte de demonstrações diferentes. 7. A função de transposição: Tudo bem. Então a primeira ou uma fórmula demo que vamos fazer chama-se transpor. E basicamente, a função transposta só permite que você altere a orientação de uma determinada matriz de dados. Então, por exemplo, se você tem uma matriz que tem cinco rosa de altura, mas duas colunas de largura, transpondo isso mudaria para uma matriz de duas linhas de altura para cinco colunas de largura. Uma coisa a notar que torna isso um pouco complicado é que o intervalo em que você entra função transposta deve ser as dimensões exatas dos dados transpostos. Então a sintaxe sábia não poderia ser mais simples. É apenas transposto. E, em seguida, a referência à sua matriz original novamente. Uma vez que isto vai ser uma mudança de controle de função de matriz, entrar no dedo do pé, trancá-lo em uma pequena ponta. Aqui há várias maneiras de transpor dados, e se você quiser transpor o conjunto de dados que você vai querer editar mais tarde, você pode transpô-lo como apenas valores em oposição a uma fórmula usando pay especial transposta, que é o altar H v O atalho que mostrei na primeira seção. Então vamos saltar para o Excel desta vez. Vamos abrir nossas fórmulas de matriz de pasta de trabalho do Excel Seção 9. É uma pasta de trabalho bem simples. Temos apenas uma guia para trabalhar com os dados de vendas chamados. Como você pode ver, eu tenho uma coluna de ano aqui. Ah, lista de lojas, incluindo Boston, Nova York e Detroit e, em seguida, alguns números hipotéticos para receita gastar lucro e estão vivos. E nós vamos apenas trabalhar com este estado que ray enquanto praticamos funções resumidas. Então eu vou mostrar duas maneiras de transpor esses dados da primeira maneira, que é a rota A simples. Se você só precisa dos valores a serem transpostos e é isso, é apenas para pegar a matriz de um a F 10. Controla C para copiar. Eu seleciono qualquer célula onde você quer soltar seus dados de transposição que atingiu Ault H V T. Isso é Colar Especial transposto. E como você pode ver, ele mudou de uma matriz de seis por 10 para uma matriz de 10 por seis, e é basicamente apenas moveu os valores para uma nova orientação Aqui. A segunda maneira é usar uma função de matriz. Então lembre-se, eu preciso selecionar as dimensões perfeitas dos meus dados de transposição antes de escrever esta função e eu vou realmente mostrar-lhe a maneira errada de fazê-lo primeiro, para que você tenha uma noção do que acontece se você não seguir esses dados regras. Eu sou então eu vou apenas escolher um array aleatório como este e na barra de fórmulas tipo é igual transposto. E novamente, meu aumento do conjunto de dados original. Feche o parêntese, e estou pronto para pressionar a tecla Control Shift Enter para transpor. Então, o que a função de transposição fará é preencher o array que eu selecionei e qualquer célula onde não possa encontrar o valor apropriado do direito original? Vai cair com um N A. Então vou apagar o controle para me livrar dele. E agora sei que quero 10 por seis. Então, há 10 colunas de largura 23456 E vamos começar de novo e digitar transpor mais uma vez novamente. Você levanta um a f 10 fechá-lo turno de controle, entrar. E lá vai você. Se eu quiser dar a isso a mesma formatação, isso é tipo do que é bom sobre todo th e v t é que ele preservou a formatação para que eu pudesse apenas pegar este pedaço no dedo do pé da casa tinha pintor formato de imprensa cozinhar o canto superior aqui e vai aplicar a mesma formatação. Então, como você pode ver agora, a mesma função transposta aparecerá em qualquer lugar. Eu clico dentro disso. Tudo bem, , então lá vai você para diferentes maneiras de transpor dados. E novamente, depende do que você está tentando fazer aqui. Para determinar qual abordagem tomar se você precisa manter um link para o seu array original não transposto que a função de raio é provavelmente a melhor aposta. Se você está procurando apenas reorganizar seus dados e, em seguida, editar ou modificar esses dados mais tarde, então o antigo atalho H V T é provavelmente a sua melhor aposta. De qualquer forma, isso é função de transposição. 8. Como ligar dados entre folhas : matriz vs.: Tudo bem. Demonstração número dois é sobre a vinculação de dados entre folhas. Então, vamos voltar à nossa guia de dados de vendas. Na verdade, vou apagar as colunas que criámos a partir do exercício de transposição . Agora, o que eu quero fazer é apenas duplicar os valores em um um o ref 10 apenas ligando-os a uma nova folha. Então eu vou mostrar a vocês duas abordagens a abordagem não-array primeiro, seguida pela abordagem array. Então eu vou criar uma nova guia. Chamar este duplicado, e eu vou fazer é escrever uma fórmula auto e vender 81 igual a dados de vendas. Um pressione Enter. E uma vez que esta é uma referência relativa, eu posso apenas arrastá-lo para baixo para a linha 10 e ele irá duplicar, uh, o array na guia de dados de vendas. Como você pode ver, cada célula tem uma função diferente porque está vinculando a uma célula diferente na guia de dados de vendas . E novamente, se eu quiser ser particular sobre minha formatação, ele também pode usar o pintor de formatos para aplicar para muitas entre células. Então eu tenho dados duplicados em duas guias. Vamos criar 1/3 guia chamado duplicado, também. E neste caso, eu vou selecionar todo o array que ele irá preencher e, em seguida, pressionar igual a dados de vendas de um a F 10. A única diferença é que eu vou apertar o controle shift enter como uma função de matriz. E lá vai você. Foi recolhido todos os dados correctos da cidade de dados de vendas outra vez. Vou copiar a formatação e colá-la lá. E então lá vão você três versões do mesmo conjunto de dados. A diferença agora, quando você olha para essas células é que na versão duplicada uma, eu tenho um total de 60 fórmulas aqui concedidas suas fórmulas simples. Mas cada célula tem uma fórmula diferente na versão ray em duplicado, também. Em qualquer lugar em que você clicar, você verá que é apenas uma função porque está sendo aplicada a uma matriz inteira em uma única etapa. Então isso é apenas uma demonstração de como o Ray funciona pode ajudá-lo a consolidar fórmulas em um caso como este. E assim, você sabe, mesmo se eu fosse mudar os valores aqui, então 2015 Boston gastar foi realmente 1000 que vai realmente atualizar corretamente tanto na matriz e, em seguida, versões Honore. Então lá vai você, ligando dados entre folhas usando funções de matriz 9. Retornando os maiores valores em um intervalo: não são assim. Agora que você tem uma noção de como essas fórmulas funcionam, vamos começar a integrar algumas das ferramentas e funções que aprendemos em palestras anteriores e ver como essas podem ser usadas como parte de uma função de raio. Então, o primeiro exemplo que eu quero mostrar a vocês é, Ah, digamos que eu quero retirar os três primeiros anos de receita da coluna C. Então, em cada um, eu vou ficar no top três e, ah, a primeira maneira que eu pensaria em fazê-lo. Mas não ou uma versão que já aprendemos seria usada. A função grande assim é igual a grande raio C de dois a C 10. Pressione isso para corrigir isso e, em seguida, para o número um maior que apenas digite mas um para o valor K. Feche e bata. Entrar. Eu poderia apenas copiar isso, colá-lo mais duas vezes e apenas mudar o valor K para um dois para o segundo maior e 23 para o terceiro maior. Então essa é a versão não array. Quero formatar da mesma maneira. Ela é a pintora de formatos, então são três fórmulas separadas para retornar os três maiores valores da coluna C. Um, vamos fazer a mesma coisa aqui, Top 3. Desta vez eu vou usar uma função de matriz para fazer a mesma coisa. Então lembre-se, eu vou começar fazendo minha seleção de todas as três células I para através I para e tipo de barra de fórmula é igual a grande raio que eu estou procurando. Os maiores valores ainda é C dois a C 10. A única diferença aqui é que para o valor k, porque eu estou procurando por todos os três maiores valores. Não se pode colocar um, dois ou três. Preciso colocar uma constante de taxa aqui. Vai abrir o suporte e fazer uma constante de matriz vertical de um a três. Desde que eu estou procurando os três maiores valores e a matriz que eu selecionei é uma matriz vertical de três linhas de altura. Então eu só preciso fechar a mudança de controle de parênteses entre e aí vai você. Assim, a mesma formatação que você pode ver obtendo exatamente os mesmos três valores principais. A única diferença é que eu tenho três fórmulas aqui na idade da coluna, apenas uma única fórmula array e coluna I Agora e se eu quisesse mudar isso dos três primeiros para os cinco primeiros. , Mais uma vez, eu poderia copiar isso. Colado mais duas vezes. Altere o 3 para 4 e um 4 para 5 para a versão do array. O que eu poderia fazer. É selecionar uma matriz vertical com cinco rosas nele. Eu poderia adicionar um semi cólon quatro, semi cólon cinco e turno de controle. Entrar. E lá vai você de novo. Basta atualizar a formatação Aqui, mudar isso para, uh, top 5 e novamente duas maneiras de realizar a mesma coisa. 10. Contando personagens em células: Certo, então a próxima demonstração que quero compartilhar com vocês é sobre contar personagens em várias células, e esta não é particularmente difícil. Na verdade, é uma função de matriz bastante simples, mas é um bom exemplo de uma tarefa que você não pode realizar em uma única etapa a menos que você use teoria uma fórmula. Eu sou assim, se o nosso objetivo, por qualquer motivo, é contar o número de caracteres em toda esta gama de lojas. B dois até Beat 10. Se eu fosse tomar a abordagem não-array quando eu tenho que fazer é o tipo de Siris de funções Len que se relacionam com a coluna B e apenas aplicá-lo para baixo. Isso me dá o número de caracteres em cada um desses nomes de cidades, incluindo espaços. E então eu teria uma grande função de soma aqui para me dar o total. Então 63 caracteres em todo esse intervalo. Eu também posso fazer a mesma coisa com uma função de matriz em um único passo, e eu poderia começar com a função de comprimento e aplicá-lo para ser 23 b 10. Mas o problema é, mesmo que eu entre com a mudança de controle. Entra, eu só tenho seis porque é no Lee calculando o comprimento da primeira célula na referência. Então o que eu preciso fazer é apenas inserir uma alguma função aqui também. Então vai ser alguns dos comprimentos de cada uma das cordas em cada fileira do meu raio. E agora, uma vez que eu apertar a mudança de controle, entra. Como você pode ver, eu obtenho o mesmo total em um único passo, ao contrário de cerca de uma dúzia então um exemplo de como você pode ser capaz de trabalhar de forma mais eficiente usando a abordagem de raios versus a abordagem tradicional da fórmula celular. 11. Como criar uma fórmula de matriz "MAX IF": Tudo bem, então para a demonstração final vou mostrar a você como construir uma função máxima se personalizada. Este é um ótimo exemplo de onde as funções de matriz podem entrar em jogo quando você não está array ou fórmulas de célula tradicionais podem ficar aquém. Então, se você se lembra na seção de função estatística, falamos sobre alguns “se “, contagem “se “, média “se”. Infelizmente, não há fórmula equivalente se você quiser calcular o valor máximo em uma coluna com base em alguns critérios. Portanto, não há função máxima se fora da caixa que podemos usar. Felizmente, as funções de matriz podem fazer o trabalho. Então o que eu quero fazer aqui é retornar o valor máximo para a receita e coluna C com base em um nome específico da loja Então um desses três nomes de cidades. Então, a primeira coisa que eu quero fazer apenas para mostrar um exemplo. É só nomear esta cidade de matriz, e eu não estou fazendo ah array constante. Tudo o que eu estou fazendo é apenas nomear o intervalo de B a três b 10 apenas para que nós lembremos que essa é uma opção para ajudar a economizar algum tempo ao escrever esta função para que arrays agora chamado cidade O que eu vou fazer é escrever um dos nomes da cidade aqui em H oito, pegue Boston e, em seguida, em h nove. E é aqui que vou digitar meu máximo. Se funcionar assim vai ser uma função relativamente simples vamos começar com iguais , Max, Max, abrir a princesa se e agora o teste lógico vai ser cidade. Você vê, é meio que acionado na matriz porque ele o reconheceu como um chamado Ray, e cidade é igual a qualquer valor em h 8. Agora é Boston. E se eu mudar esse valor, você poderia puxar o máximo para Nova York ou Detroit, por exemplo, em então o valor de verdade será apenas o array de C 2 a C 10 que é o meu campo de receita. É aí que eu estou tentando extrair o valor máximo de tão perto para parênteses e controle shift enter. E lá vai você. Então pegue a formatação de aparecer na guia inicial. Boston, 1726 dólares. Poderíamos fazer uma verificação rápida. Confirme se esse é o valor máximo para Boston. E depois de mudar isso para Nova York, por exemplo, em 1944. Ela também está correta. E Detroit, 17 43. Lá vai você, tão ótima maneira de criar nossa própria função personalizada onde não tínhamos a opção. Usando nossas fórmulas estatísticas tradicionais que encerram a seção de fórmulas de matriz. Esta é realmente apenas uma pequena amostra dos tipos de análises que você poderia fazer com a corrida . Por isso encorajo-te. Depois de se sentir confortável, basta começar a explorar como e onde você pode usar o de em outras áreas. Então, em seguida, temos uma seção final cobrindo algumas funções bônus ou fazer algumas demonstrações realmente interessantes lá, então fique atento para o trecho final. 12. Bônus: "MAX IF" com vários critérios: Tudo bem, então na verdade mentiu. Eu tenho algumas demonstrações adicionais de função de array que eu quero compartilhar com. Vocês que vieram acima com desde que o curso foi originalmente publicado realmente ajudaram a demonstrar o quão versátil e poderoso funções de matriz poderia ser. Então, nesta palestra, eu quero realmente pegar no máximo se função que acabamos de completar e torná-lo um pouco mais complicado. Então, em vez de apenas basear o máximo, se em um critério em que City é igual a Detroit ou Boston ou Nova York, eu quero atualizar esse Max. Se função de matriz para contabilizar vários critérios especificamente tanto local ou cidade eo ano para saltar de volta para a pasta de trabalho, a primeira coisa que eu vou fazer é apenas selecionar um dois a 8 10 e nomear esse intervalo de ano apenas para torná-lo um um pouco mais fácil de trabalhar e aqui fora. E eu comi. Vamos colocar um ano pode ser qualquer coisa. Vou colocar 2014 aqui, e eu venderia nove. É aqui que quero entrar no meu novo máximo. Se função, eu vou realmente apenas arrastar isso para me dar um bom ponto de partida, Eu posso editar as referências Cidade ainda precisa igual H oito, e os valores estavam procurando o máximo dentro. Não é D para três d 10. Ele ainda é ver o Recibo 10 o campo de receita. Então, se eu pressionar a tecla Control Shift enter, você verá que eu tenho o mesmo ponto de partida de antes. E agora eu poderia apenas adicionar esse segundo critério aqui então eu quero fazer é realmente cercar meu primeiro critério. A cidade é igual a H oito entre parênteses. Multiplique isso pelo meu segundo critério, que é o ano. Você pode ver que identifica o intervalo de nomes igual a que eu comi perto desse parêntese. Então eu tenho dois critérios, cada um cercado por parênteses, separados por um multiplicador. Vou te mostrar o porquê em um minuto. Então, se eu controlar turno, entrar agora ele retorna 16 09 que é o valor máximo Se Cidade é igual a Detroit e ano é igual 2014. Então Detroit 2014 16 09 Agora, obviamente, é o único valor que existe para Detroit 2014. Mas para dar a isso um teste, podemos mudar o 2013 para 2014. E agora, como você pode ver, o valor máximo muda de 16 09 para 17 43. Então, desfaça essa mudança de volta para 16 09 Agora, vamos cavar um pouco, tentando entender exatamente como isso está operando. A melhor maneira de fazer isso é usar a ferramenta de fórmula de avaliação. Então, com esta célula selecionada, eu posso ir em fórmulas, avaliar fórmula, e isso vai me guiar através de cada passo da função e como ela é avaliada. Então vamos começar com a cidade sublinhada, e quando avaliarmos a cidade chamada intervalo, ela traduzi-la na matriz de nove linhas diferentes de dados. Boston, Boston, Boston, Nova Iorque, Nova Iorque, , Detroit, Detroit, Detroit. Então estamos agora procurando casos em que os valores dentro dessa matriz são iguais ao valor em oito anos , que avalia para Detroit e como o Excel percorre isso, ele vai traduzir essa declaração em uma matriz de trégua e false é tão para cada linha onde essa condição é verdadeira ou a cidade é igual a Detroit, ele retornará um verdadeiro e para cada linha onde esse critério não é verdadeiro, ele retornará um falso para que o primeiro critério cidade é igual a oito anos se traduza em falso, falso, falso, falso, falso, falso verdadeiro. Verdadeiro, Verdadeiro, uma vez que apenas as três últimas linhas nesta matriz atendem a esses critérios. A seguir, vamos fazer a mesma coisa com os critérios do ano. Então ano avalia para uma matriz contendo todas as nossas datas nestas nove linhas e colunas A. E estamos procurando casos em que os valores nessa matriz são iguais ao valor e eu oito, que agora é 2014. Portanto, isto irá avaliar de uma forma muito semelhante que irá traduzir esta afirmação em outra. Siris de verdadeiro e falso é neste caso, nós temos falso, verdadeiro, falso, falso, verdadeiro, falso, falso, verdadeiro, falso. E como você pode ver, isso faz sentido porque a cada três anos, apenas um é 2014. Então agora aqui está a chave que permite que esta função para retornar o valor adequado que criamos para um aumento. Baseado em nossos dois critérios. A primeira matriz contém valores verdadeiros e falsos com base em linhas que correspondem à cidade local. A segunda matriz contém valores verdadeiros e falsos baseados em rosa que atendem o ano. Então, quando multiplicamos esses dois juntos, o resultado é uma única matriz de zeros e uns que podem ser interpretados exatamente da mesma maneira que você interpretaria trégua e False é onde verdadeiro é igual a um falso é igual a zero. Então o que temos aqui agora é uma matriz que se parece com 000000010 Então Onley um valor nessa matriz preenchido como verdadeiro. E isso é porque qualquer caso em que um critério ou o outro não foi atendido. Quando esses dois valores são multiplicados, um dos quais tem que ser zero, isso resultará em um zero na matriz final. Então um indica a linha dentro da nossa matriz onde todos os critérios foram atendidos, e neste caso é a segunda a última linha. E se você apenas olhar para nossos dados aqui, a segunda a última linha linha nove é a estrada que contém tanto o ano Nós nos preocupamos com 2014 ea cidade que nós nos importamos com Detroit agora o último pedaço desta função. A instrução if essencialmente apenas atribui um valor de dentro do intervalo de C dois a C 10 para qualquer valor em nosso raio que não é igual a zero. Então, em outras palavras, vamos retirar o valor do segundo dedo do pé última venda na faixa de C 2 a C 10 desde o segundo dedo. Último lugar em nossas matrizes, o único valor diferente de zero. E como você pode ver, resulta 16 09, uma vez que esse é o segundo ao último valor em nossa matriz de C dois a C 10 e agora estamos apenas à esquerda com uma função máxima simples. Então, qual é o máximo de falso ou 000000 16 090 É 16 09 E esse é o valor que é devolvido . Então, como você pode ver um raio, funções são muito, muito diferentes formas, funções celulares normais em que eles tipo de ciclo através desses processos todos dentro da mesma fórmula dentro da mesma célula, e Isso é o que os torna tão poderosos. 13. Bônus: como usar o Operador Unary duplo ("--"): Certo, vamos dar mais um exemplo. Apenas para mostrar como as fórmulas de array podem ser usadas às vezes para fazer coisas que vendem fórmulas simplesmente não conseguem realizar. Então, neste caso, aderindo ao nosso exemplo de dados, Digamos que alguém lhe pergunta, quantos anos o lucro excede gastar neste conjunto de dados? Se você fosse realizar isso usando fórmulas celulares, você poderia fazer algo como uma declaração condicional que diz, Se ele é maior que D, então retornou uma. Caso contrário zero e você poderia aplicar isso para baixo e, em seguida, tomar o alguns dessa coluna e lá você pode ver bem lá quatro anos ou quatro linhas neste conjunto de dados em que o lucro excedeu gastar. Mas isso levou dois passos. Usei 10 fórmulas. As funções totais do array poderiam fazer isso com muito mais elegância e com muito menos poder de processamento para fazer isso, digamos apenas, você sabe, lucro. É maior do que gastar indo linha de menu para casa que fazer itálico e aqui é onde eu quero retornar o mesmo valor de quatro. Então eu vou realmente usar uma função de matriz usando alguns, então eu vou dizer igual alguns e agora, em vez de apenas um único número ou um único intervalo tradicional de números aqui que pode realmente inserir intervalos de dados aqui de uma só vez. Então eu posso dizer E para através e 10 é maior que D para três d 10 e fechar o parêntese. Então, em teoria, o que isso deve fazer como uma função de matriz, é percorrer cada linha dentro de um raise e tally up um verdadeiro ou um em qualquer caso onde o critério é verdadeiro ou onde coluna é grande e coluna d e um zero caso contrário, e, em seguida, tomar o sol e retornar de quatro. Então, se controlarmos a mudança entrar, você verá que temos um zero, o que obviamente não é o que queremos. Então ele vai para fórmulas, avaliar fórmula e ver o que está acontecendo aqui. Então, nesta declaração maior do que é avaliada, isso resulta na matriz que esperaríamos falso, falso, verdadeiro, , falso verdadeiro, falso, verdadeiro, , verdadeiro,verdadeiro, verdadeiro, falso, falso e, como você pode ver, portanto, valores verdadeiros nas posições 34 seis e sete. E se você olhar para essa rosa específica dentro de nossa linha de dados, 1239 68 é ótimo e em 7 58 49 anos 80, ótimo nos 9 64 6 e 7. Então, essas são as posições corretas ou as instâncias corretas de linhas que correspondem a esse critério maior do que. O problema é que, quando tentamos somar essa afirmação, ela é avaliada como zero. E a razão é que mesmo que em muitos casos o Excel irá tratar false está em zeros e True's como um com alguma instrução, ele não sabe como lidar com esses valores. Então o Excel realmente tem uma pequena ferramenta muito útil chamada W Nery e que essencialmente converte valores booleanos não numéricos true e false em reais e zeros. Então, se mergulharmos de volta na função dela aqui logo após o primeiro parêntese indo para fazer dois traços, esse é o duplo você Nery. Não há necessidade de abrir o parêntese novamente e fechar o segundo no final. Agora, se eu controlar shift enter, ele retorna o valor adequado de quatro. Então, sempre que você tem uma função de matriz onde você precisa converter uma matriz específica de valores booleanos em uns e zeros que W Nery deve fazer o truque 14. HOME: ordene funções: Tudo bem. Hora de dar um tapinha nas costas. Acabou de terminar a seção de corrida. Coisas loucas, definitivamente um pouco complicado para se acostumar e muito, muito diferente das funções celulares tradicionais que temos falado agora novamente . Como eu disse antes, isso é realmente apenas riscar a superfície e dar-lhe alguns exemplos selecionados de como nossas fórmulas A podem ser usadas no Excel. Dito isto, uma vez que você domina os fundamentos de como as fórmulas do Ray funcionam, você pode rapidamente começar a aplicá-las e maneiras mais complicadas ou mais avançadas. Então tempo de lição de casa para qualquer um interessado em cavar um pouco mais fundo e obter um pouco mais prática com estes no arquivo de exercícios de casa que está disponível no curso. O recurso é que temos duas abas com exercícios de matriz. A 1ª 1 é uma função de raio, e aqui vamos praticar algumas coisas como Max. Se Max se com vários critérios usando esse duplo você Nery para converter valores booleanos em números e, em seguida, por último, mas não menos importante, temos esta guia de função matriz de bônus, que é onde as coisas começam a ficar realmente peludo. Essencialmente, o que vamos tentar fazer aqui é usar fórmula Honore para retornar um subconjunto deste intervalo de três d 14 com base em critérios específicos. Então, queremos retornar esse subconjunto aqui em I três a l 14, mas Onley para Rose que atendem a data de início, data término e critérios de receita mínima atribuídos aqui na coluna G. Então, eu vou realmente dar-lhe uma pequena espreitadela no arquivo de resposta porque o que eu fiz é definir uma explicação passo a passo em minhas próprias palavras de como esta coisa está realmente operando. E se você der uma olhada na função, você pode ver que é uma espécie de besta. Dito isto, esta provavelmente não é a única maneira de fazer isso. Tenha em mente que esta é a abordagem que eu escolhi para realizar isso, mas pode muito bem haver outros. Então, como sempre, postagens para o quadro de discussão. Se você precisar de ajuda, me envie uma mensagem diretamente, ou se você só quiser dizer oi, sempre aqui para ajudar. Obrigado, pessoal. Boa sorte