Arquivo da categoria: Excel

Dicas sobre Excel e utilização desta planilha eletrônica, que é a mais utilizada no mundo

SuperDicas – Usando as Fórmulas Matriciais

Originalmente publicado neste endereço: http://www.superdicas.com.br/excel/excel21.asp

Explico o motivo da replicação no fim do artigo.

O Que é uma Fórmula Matricial

No Excel uma fórmula matricial executa cálculos múltiplos e, em seguida, produz um resultado único, ou resultados múltiplos. Fórmulas matriciais agem sobre um ou mais conjuntos de valores, que são conhecidos como argumentos de matriz. Cada argumento de matriz deve ser retangular e cada argumento precisa ter o mesmo número de linhas e/ou o mesmo número de colunas que os outros argumentos.

Para produzir resultados múltiplos, a fórmula deve ser inserida em células múltiplas.

No Excel para se inserir uma fórmula matricial, deve-se pressionar as teclas [CTRL]+[SHIFT]+[ENTER] simultaneamente. O Excel coloca fórmulas matriciais entre chaves { e }.
Atentar para que essas chaves NUNCA sejam digitadas, pois assim não funcionará.

Recurso Pouco Utilizado

Além de pouco utilizado, por parecer muito complexo, é um recurso que quase ninguém conhece. Para facilitar a compreensão daremos abaixo dois exemplos de sua utilização prática. O primeiro exemplo ilustra a utilização da fórmula matricial como auxiliar na contagem de repetições de números. O segundo exemplo ilustra a utilização da mesma na elaboração de uma lista de repetições de nomes. Para facilitar, ainda mais, disponibilizamos uma planilha com estes exemplos, completos, e que você pode baixar para checar e adaptar às suas necessidades. O link para o download está no final desta página.

Exemplo 1: Contando o Número de Repetições numa Faixa de Células

Imaginemos que exista uma faixa de células onde são guardados diversos números, números esses que podem, ou não, se repetir dentro da faixa de entrada dos dados. Queremos ter a condição de saber quantas vezes um determinado número aparece dentro da faixa de entrada.

Cenário:

Nas células de A3 até A15 são colocados os números, que podem ter, ou não, repetições. Na célula B3 se digita um número – aquele que se quer conhecer o número de vezes que aparece na faixa de dados. Finalmente o resultado – o número de vezes que um dado valor aparece na faixa de entrada dos dados – deve aparecer na célula C3.

Aplicando a Fórmula Matricial:

Com o cursor na célula C3, digita-se a seguinte fórmula:
=SOMA(SE(A3:A15=B3;1;0)) – só que ao invés de pressionar a tecla [ENTER], como se faz em todas as demais situações, para inserir uma fórmula matricial se pressionam, simultaneamente, as teclas [CTRL]+[SHIFT]+[ENTER].

O Excel irá representar a fórmula entre chaves, como mostrado abaixo:

{=SOMA(SE(A3:A15=B3;1;0))}

A B C
1

Demonstração de Fórmula Matriz # 1

2 Entradas Checa este Número No. De Repetições
3 10

10

3

4 12  Na célula acima digita-se o número que se deseja pesquisar as repetições Na célula acima aparece o número de repetições do valor digitado
5 10
6 15
7 16
8 15
9 15
10 12
11 10
12 19
13 18
14 18
15 17

Como Funciona:

O Excel, percebendo se tratar de uma fórmula matricial, passa por todas as células da faixa demarcada, comparando-as com o valor digitado na célula B3 – SE(A3:A15=B3). Quando uma das células contiver um número idêntico a fórmula retorna o valor 1, senão retorna o valor 0. Ao final da faixa ser totalmente checada o Excel totaliza os zeros e uns (através da função =SOMA) encontrados em cada comparação. Essa soma é exatamente igual ao número de vezes que tal número aparece na faixa de entrada.

Exemplo 2: Mostrando Nomes Repetidos Apenas Uma Única Vez

Imaginemos que exista uma faixa de células onde são guardados diversos nomes de visitantes de nosso estante de arte, por exemplo. Esses visitantes podem, ou não, retornar e assim podem se repetir dentro da faixa de entrada dos dados. Queremos ter condição de destacar, por exemplo na coluna ao lado apenas a primeira vez em que algum nome aparece dentro da faixa de entrada.

Cenário:

Nas células de A3 até A15 são colocados os nomes, que podem ter, ou não, repetições. Finalmente o resultado – uma coluna onde cada nome só apareça uma única vez – deve aparecer na célula C3.

Aplicando a Fórmula Matricial:

Com o cursor na célula B3, digita-se a seguinte fórmula:
=SE(SOMA(A3=$B$2:B2)*1)=0;A3;””) – só que ao invés de pressionar a tecla [ENTER], como se faz em todas as demais situações, para inserir uma fórmula matricial se pressionam, simultaneamente, as teclas [CTRL]+[SHIFT]+[ENTER].
O Excel irá representar a fórmula entre chaves, como mostrado abaixo:

{=SE(SOMA(A3=$B$2:B2)*1)=0;A3;””)}

Em seguida deve-se copiar a fórmula em B3 para o restante da faixa, no caso entre B4 e B15.

A B
1

Demonstração de
Fórmula Matriz # 2

2 Visitantes Visitantes Únicos
3 André André
4 Colombina Colombina
5 Matilde Matilde
6 Alfredo Alfredo
7 Antonio Antonio
8 Matilde
9 Colombina
10 André
11 Sérgio Sérgio
12 Matilde
13 André
14 Célia Célia
15 Antonio

Como Funciona:

O Excel, percebendo se tratar de uma fórmula matricial, passa por todas as células da faixa demarcada, comparando o valor em A3 com todos os valores já definidos pela fórmula desde a posição B2 até a célula da coluna B na linha atual (B2 está vazia naturalmente, ou contém apenas um título, que não representa um nome). Quando uma das células  da coluna A contiver um novo nome a fórmula retorna o valor 1, ou 2 ou de quantas vezes se repetiu essa nome até esse momento, se ele não apareceu nenhuma vez (o valor é 0) a fórmula SE dá resultado verdadeiro e o Excel coloca o nome encontrado na mesma linha na coluna A, senão (o resultado de SE é falso) o Excel apenas coloca um valor vazio (“”) na célula.

Experimente um pouco mais com este recurso pouco explorado, mas muito útil em diversos momentos de nosso trabalho com números e/ou nomes.

Declaração

Este post é uma homenagem.

Além de ser um recurso acima do média, fórmulas matriciais são um mistério para a maioria dos usuários de Excel, mesmo depois de anos de seu lançamento. No momento da publicação deste artigo, blogs, mídias sociais, canais de vídeos de todo tipo e até cursos são dados aos montes.

No entanto, anos atrás quando a internet eram blogs e geocities, raros eram os bons conteúdos publicados. Este em específico, era minha salva guarda, já que era um dos poucos que conseguiu explicar de maneira sucinta algo que level anos para entender, e gostaria de dividí-lo aqui, com vocês.

Repito, esta é uma homenagem, já que o site que hospedava a página não está mais no ar no link original ou em outro que pareça ser de mesma autoria. Se o autor do artigo se sentir de alguma forma lesado com a publicação deste, por favor, entre em contato para a devida remoção.

Excel – BDSOMA, BDMULTIPL, BD… e as funções do excel que você deveria conhecer

20 anos usando o Excel e ele ainda me surpreende. E sabe o que é mais curioso? Ele te surpreende com recursos que sempre estiveram lá, há anos, muitos, mas você nunca notou!

Este é o caso das funções de Banco de Dados, ou BD*.

Eu tive contato com estas fórmulas no começo da minha vida no Excel, mas, seja pela forma como ela foi explicada ou minha falta de experiência, acabei não sabendo o que fazer com elas e acabei deixando pra lá.

Não vou abordar todas aqui, primeiro, porque uma vez que você aprenda a usar uma, as outras seguem o mesmo raciocínio de uso, tornando fácil a adaptação, segundo, a lista é longa, terceiro… já esqueci, mas vamos ao que interessa.

No fim do artigo tem uma lista de TODAS as funções de banco de dados do próprio site da Microsoft para você conferir.

É hora do show!

Vou usar aqui o BDSOMA. Ele vai resumir o espetáculo que utilizar as funções de banco de dados. Vou aplicar o conceito máximo do reaproveitamento e utilizar o próprio exemplo da Microsoft.

Considere a tabela de dados abaixo:

ABCDE
1ÁrvoreAlturaIdadeRendimentoLucro
2Maçã182014$105
3Pera121210$96
4Cereja13149$105
5Maçã141510$75
6Pera988$77
7Maçã896$45

Uma lista de simples de pés-de-alguma fruta, coisa que todo morador de interior já teve no quintal de casa.

Para o exemplo, se quisermos extrair a SOMA dos rendimentos, fica fácil, se quisermos extrair a SOMASE a árvore for de Maçã, também fica fácil. Mas, se quisermos extrair a soma se a árvore for Maçã e os redimentos forem maiores que 10 e menores do que 16, precisamos pensar um pouco.

Se você for um pouco letrado no Excel, sabe que isso se resolve com um SOMASES com a seguinte fórmula:

=SOMASES(D2:D7;B2:B7;”>10″;A2:A7;”=Maçã”;B2:B7;”<16″)

Mas, se complicarmos um pouco mais, gerando critérios múltiplos para nossa soma, você precisa ou elaborar muito o SOMASES ou utilizar mais de um. Por exemplo, se eu quiser incluir a soma de tudo o que for Pera, a fórmula fica assim:

=SOMASES(D2:D7;B2:B7;”>10″;A2:A7;”=Maçã”;B2:B7;”<16″)+SOMASES(D2:D7;A2:A7;”=Pera”)

Resolve, mas é “feio”.

Nada contra o SOMASES, o problema é que, se você precisa repetir uma fórmula, a probabilidade de existir uma solução mais simples é inegável. Outra reclamação pessoal que tenho é que, para entender o que está acontecendo, eu basicamente tenho que decifrar a fórmula, ou seja, entrar na célula e lembrar o que ela está fazendo. Para fórmulas simples, isso não é problema, mas para coisas medonhas que requerem quebras de linha, não é tão simples.

Isso não acontece quando você utiliza o BDSOMA. Vamos aplicar o mesmo critério de soma detalhado acima usando-a. Abaixo, a planilha um pouco transformada:


ABCDEF
1ÁrvoreAlturaIdadeRendimentoLucroAltura
2=Maçã>10<16
3=Pera
4
5ÁrvoreAlturaIdadeRendimentoLucro
6Maçã182014$105
7Pera121210$96
8Cereja13149$105
9Maçã141510$75
10Pera988$77
11Maçã896$45

Eu já gosto mais do que estou vendo. Em resumo, a tabela principal foi empurrada algumas linhas para baixo e deu espaço ao que vou chamar de área de filtros ou critérios. Ou seja:

A5:E11 – São os dados que queremos trabalhar

A1:F3 – Os filtros a serem aplicados

Usando o BDSOMA, o mesmo resultado do SOMASES pode ser conseguido com a seguinte fórmula:

=BDSOMA(A5:E11;”Rendimento”;A1:F3)

Explicando a fórmula acima:

  • A5:E11 é obviamente a área de dados a ser avaliada
  • “Rendimento” é a coluna que queremos somar. Sim, você não precisa mencionar índice ou coisa parecida. Basta o nome
  • A1:F3 são os filtros, que podem ser repetidos, unidos e cruzados! Veja que temos duas linhas para dizer que queremos árvores de Maçã OU Pera, e no caso da Maçã, com altura >10 (célula B2) e <16 (célula F2)

Não sei para você leitor, mas para mim, isso é mágico! Os filtros estão à mostra e sei EXATAMENTE o que está sendo considerado na soma! E posso mudar à vontade a área de filtros para ver o resultado mudar, sem precisar mexer na fórmula! Experimente!

Sim, você pode chegar num resultado parecido com o SOMASES, mas dá mais trabalho, já que não é natural.

O formato utilizado é só uma convenção, sendo que os filtros podem ser colocar em qualquer lugar do seu arquivo.

“Mas se eu quiser o PRODUTO? Ou a MÉDIA?”. Oh meu caro padawan, divirta-se com a lista abaixo que utiliza a mesma estrutura do BDSOMA:

Função Descrição
BDMÉDIARetorna a média das entradas selecionadas de um banco de dados
BDCONTARConta as células que contêm números em um banco de dados
BDCONTARAConta células não vazias em um banco de dados
BDEXTRAIRExtrai de um banco de dados um único registro que corresponde a um critério específico
BDMÁXRetorna o valor máximo de entradas selecionadas de um banco de dados
BDMÍNRetorna o valor mínimo de entradas selecionadas de um banco de dados
BDMULTIPLMultiplica os valores em um campo específico de registros que correspondem ao critério em um banco de dados
BDESTEstima o desvio padrão com base em uma amostra de entradas selecionadas de um banco de dados
BDDESVPACalcula o desvio padrão com base na população inteira de entradas selecionadas de um banco de dados
BDSOMASoma os números na coluna de campos de registros do banco de dados que correspondem ao critério
BDVARESTEstima a variação com base em uma amostra de entradas selecionadas de um banco de dados
BDVARPCalcula a variação com base na população inteira de entradas selecionadas de um banco de dados

Você sabe o que é Power BI? Nem eu…

Faz tempo que não escrevo um artigo de opinião. Já era hora de desenferrujar e nada melhor do que polemizar para tal.

Não custa ressaltar que o que escreverei aqui é apenas MINHA OPINIÃO e não necessariamente reflete a verdade ou o que a comunidade pensa. E, por mais que o Power BI esteja mencionado no título, eu não falarei dele.

Sabendo disso, vamos em frente.

A bola da vez?

O assunto da vez, pelo menos no que tange o espectro de curiosos e interessados do Excel, é o Power BI e com ele, os Dashboards.

Não vou entrar no mérito do segundo por ele ser mais abrangente. Isso tem mais ou menos uns 2 a 3 anos da data deste post. Só se fala nele, só se dá curso sobre ele, masterclass, livros, etc. Isso é bom, aquece o mercado, alimenta famílias, engrandece o currículo.

MAS (em caixa alta mesmo) , se você sofre um mínimo do complexo de vira-lata, o que é o meu caso, provavelmente se sentiu “atrasado” em saber que nada sabia sobre o assunto. Como tudo curioso frustrado, fui lá tentar saber o que era. Para minha surpresa, o que encontrei foi isso…

O Power BI não é para mim, e talvez não seja para você

O subtítulo acima foi feito para incomodar, principalmente aos adoradores no Power BI. Entendo, mas há algo que precisa ficar claro aqui.

O Excel é um software com uma quantidade insana de funcionalidades. Nem mesmo os mais experientes conseguiriam enumerar todas, e eles admitem isso (vou deixar para você o trabalho de pesquisar isso. Minha dica é gastar um tempo no Quora vendo respostas dos MVPs).

Diante disso, toda vez que algum recurso revolucinário vem à tona, a primeira coisa que me vem a cabeça é ceticismo, e isso eu aprendi com o João Benito Savastano. Depois de quase 20 anos conhecendo e trabalhando com a ferramenta, achar que falta algo é “meio que” trair a confiança dela.

Não me leve a mal, eu gosto de novidades, tanto que faço questão de sempre ter a última versão do Office instalada no meu Windows. Mas, para ser sincero, depois do SOMASES, CONCAT e UNIRTEXTO, pouca coisa fez diferença para mim, e o Power BI é uma delas. Não é porque o Power BI é ruim. Os colegas da comunidade estão fazendo trabalhos Excelentes divulgando a ferramenta, escrevendo artigos, ministrando cursos e escrevendo livros.

Só que, bem, o Power BI não é para mim, e por alguns motivos simples:

  • ele não tornará meu trabalho melhor
  • ele não tornará meu trabalho mais rápido
  • meus projetos não se beneficiarão dele
  • meus clientes não se beneficiarão dele
  • eu gosto de Dashboards, mas nem meus clientes e nem eu precisamos deles

Isso é mais do que suficiente para eu não me preocupar com ele por enquanto, ou pelo menos até ele ser necessário para mim.

Coitado do Power BI

Aqui, um mea culpa. Eu basicamente massacrei o Power BI sem mesmo tê-lo conhecido. A verdade é que ele acabou sendo o bode expiatório de um problema maior que tentei elaborar acima.

Por isso, para qualquer outro recurso maluco que você acha que deveria conhecer, mas não conhece, pense que, se você é capaz de fazer seu trabalho com produtividade com os recursos que você já sabe, isso é tudo o que você precisa e você pode tranquilamente viver sem nada saber da “nova funcionalidade no pedaço”.

Conhecer coisas novas é tarefa obrigatória de todo profissional da atualidade, mas, com o tempo escasso e o mercado exigente, ser seletivo no que você vai utilizar é o melhor caminho a seguir.

Desculpe Power BI. Quem sabe no futuro…

Excel – Retornando mais de uma coluna com PROCV

Ela de novo, a função PROCV. Seu uso no Excel, se contado, deve ser equivalente ao número de instalações do próprio aplicativo.

Filosofias a parte, tanto quanto o uso do PROCV, fala-se muito das suas limitações. O substituto imediato é a combinação do das funções ÍNDICE e CORRESP, entre outras.

Porém, com uma pequena ajuda de fórmulas matriciais (veja o que são aqui: https://www.tomasvasquez.com.br/blog/microsoft-office/excel-formulas-matriciais), o PROCV por ir além, por exemplo, fazendo retornar mais de coluna ao mesmo tempo.

O problema

Para começar, precisamos de dados. No exemplo utilizado, tenho a base de munícios do Brasil e seus estados.

Fonte: https://pt.wikipedia.org/wiki/Lista_de_munic%C3%ADpios_do_Brasil_por_popula%C3%A7%C3%A3o

Com base nisso, vamos construir um PROCV bem simples (na célula H2):

=PROCV($H$1;$A$2:$E$5571;3;FALSO)

PROCV básico para obter o nome do município pela posição

Até aqui tudo bem, mas, o Brasil tem várias cidades de mesmo nome e saber também o estado ajudaria. Temos algumas formas de fazer isso, sendo a mais óbvia adicionar mais um PROCV ao lado do primeiro. Isso resolve bem para alguns casos, mas, e se precisar fazer isso 3,4 ou mesmo 10 vezes? Há situações em que isso é necessário e a fórmula pode chegar a ficar insanamente comprida. A solução mais elegante é usar PROCV com fórmulas matriciais.

O truque

Estou assumindo que você entende de fórmulas matriciais, com já foi referenciada no link acima.

Uma das coisas mais interessantes que as fórmulas matriciais é sua capacidade de iteração, seja via preenchimento, seja via arrays dentro da su função. Sim! Arrays! Você leu direito!

Começamos com uma pequena modificação no primeiro PROCV escrito, que deve agora parecer com isso:

=PROCV($H$1;$A$2:$E$5571;{3,4};FALSO)

O que mudou foi que, ao invés de mencionar a coluna 3, que refere ao nome do município, temos 2 valores numéricos entre chaves {} e separados por ponto-e-vírgula (separador padrão do Excel em português). O interessante é que quando você confirma a alteração, o Excel não reclama, apesar de ainda não saber o que fazer com aquilo, assumindo apenas o primeiro valor.

Para fazer nosso PROCV retornar os dois valores, há dois passos a serem seguidos:

  • Transformar o PROCV acima em matricial
  • Fazer algo com o resultado

A primeira parte é fácil. Basta entrar na célula e digitar o atalho Ctrl+Shift+Enter. Isso adicionará chaves em volta da fórmula provando que ela está no formato matricial. Mas, após isso, nada muda:

Matricial agora, mas não completo

O detalhe é que, a fórmula toda agora está retornando vários valores, mas o mecanismo padrão do Excel só interpreta um. Para pegar todos os resultados, precisamos de funções que recebem vários valores e fazem algo com eles, por exemplo, SOMA, MÉDIA, MÁX e, no nosso caso, CONCAT!

A CONCAT nada mais, nada mesmo que une os valores de texto que lhe são passados. Adicionando ela ao mix, temos o seguinte:

{=CONCAT(PROCV($H$1;$A$2:$E$5571;{3;4};FALSO))}

Agora concatenando tudo!

Um exemplo mais maluco ainda, agora usando UNIRTEXTO:

{=UNIRTEXTO(” – “;VERDADEIRO;PROCV($H$1;$A$2:$E$5571;{2;3;4;5};FALSO))}

PROCV com UNIRTEXTO com Fórmulas Matriciais. Não dá pra ser melhor!

Com isso, você não deve precisar mais precisar colocar vários PROCVs seguidos quando mais de uma coluna tiver que ser retornada.

Agora é com você Exceleiro de plantão! Caso tenha mais alguma maluca que possa fazer uso deste curso, coloque aí nos comentários!

Bom proveito!