O que é o preenchimento relâmpago

O preenchimento relâmpago é um dos recursos mais curiosos e inovadores do Excel, já que ele é capaz de inferir um determinado resultado a partir de exemplos.

Normalmente no Excel precisamos a partir de um exemplo definir uma regra e construí-la usando funções. O preenchimento relâmpago precisa apenas dos exemplos que ele mesmo será capaz de inferir os próximos resultados.

Como sempre digo nos meus treinamentos: se você conhece a regra é capaz de achar uma solução. O flash fill “descobre” a regra.

A desvantagem é que ele não insere na célula a regra que ele utilizou, mas tirando isso ele é extremamente útil simplificando processos em que precisemos juntar, separar textos, alterar valores etc.

Mostraremos a seguir 6 exemplos de como usar o preenchimento relâmpago

Exemplo1 de como usar o preenchimento relâmpago

Aqui vamos usar o preenchimento relâmpago para trazer a 1ª palavras de uma frase. Veja:

Para usarmos o preenchimento relâmpago basta digitarmos nas primeiras células o que queremos trazer. NO exemplo acima vamos digitar CADA, CAIU e já veremos a sugestão:

Note que ele trará tudo já em maiúsculas, pois foi assim que colocamos nossos exemplos. Para aceitar a sugestão basta dar ENTER.

Se ele não mostrar a sugestão selecione as células de exemplo mais as células em branco, vá em DADOS e clique no botão do PREENCHIMENTO RELÂMPAGO (ou pressione CTRL E):

Além disso o Excel mostra na barra inferior esquerda a informação de quantas células foram alteradas pelo preenchimento relâmpago, e se você clicar na mensagem ele irá selecioná-las.

Exemplo 2: Juntar (concatenar células)

Vamos unir o texto1 com o texto 2, colocando entre elas uma vírgula:

Exemplo 3: Criar uma data

Abaixo vamos pegar o número do ano e transformar em uma data válida na forma 31/dezembro daquele ano. No meu caso ele não sugeriu as datas automaticamente. Mas não tem problema, basta selecionar as 2 células de exemplo e pressionar CTRL E:

E ele mostra o aviso:

Exemplo 4: Converter valores

Vamos usar o preenchimento relâmpago para converter os valores da coluna A para mostrá-los com o sinal de R$ e o M de milhares:

Importante: nesse caso a coluna b não tem mais valor, pois eles foram convertidos em texto. Use com cuidado pois os dados da coluna b não são mais números.

 Exemplo 5: Extrair somente números do texto

Queremos que ele traga somente os valores ditribuídos ao longo do texto:

Exemplo 6: Formatar números

 Abaixo vamos usar o preenchimento relâmpago para aparecer em formato de números de telefone:

Tem várias outras possibilidades. Como gosto de dizer: pague para ver e teste, antes de achar / dizer que lele não é capaz de fazer!

 

OUTROS ARTIGOS DE INTERESSE

Já são 40 anos de idade

O Excel 1.0 foi criado em 1982. São quase 40 anos de existência. Apesar de ter começado minha carreira usando o Excel 5.0 tenho certeza de que a função PROCV existia nela (pelo menos no 5.0 tinha!).

Então estamos hoje, no séc. XXI obcecados em aprender a usar uma função velha. Antiga. Desatualizada.

Você pode dizer que a função soma também tem 40 anos e não é por isso que deixamos de usá-la. Sim, mas veja que atualmente a usamos somente em situações bem simples. Qualquer soma “diferente” já partimos para o SOMASES, tabelas dinâmicas etc.

Vamos parar de lero-lero e se achegue. Mostrarei 4 motivos supimpas para vosmecê parar de usar o PROCV (o moribundo e cafona).

Motivo 1 para você parar de usar o PROCV

ELE SÓ OLHA PARA UM LADO

Você precisa ter sorte para usar o PROCV, porque só conseguirá pesquisar informações para a direita do chamado valor_procurado.

Se os itens estiverem à esquerda dançou. Vai precisar fazer a famosa gambiarra.

Veja o exemplo a seguir. Temos a lista de tripulantes da nave Nostromo, em Alien. Tirando a Ripley e o gato, todo mundo morreu e a ordem é exatamente a ordem em que morreram.

Se eu quiser pesquisar o nome completo do personagem a partir da ordem em que foi morto, OK. Meu procv ficaria assim:

 

Agora, se eu tiver o Nome e quiser saber o nome completo já não consigo, porque o PROCV só pesquisa colunas à direita da coluna de índice.

Para pesquisarmos isso usando o PROCV precisaríamos fazer alguma gambiarra. E convenhamos, se estamos falando de uma planilha PROFISSIONAL não dá para pensar nisso…

Motivo 2 para você parar de usar o PROCV

BUSCA DINÂMICA DE COLUNAS

No exemplo acima definimos que queremos a coluna de Nome Completo. Mas e se precisarmos buscar várias colunas? A tabela a seguir mostra o público total de filmes brasileiros por estado de origem do filme no ano de 2015.

Se quisermos buscar os dados de um determinado estado e de todas as datas o nosso PROCV não poderá ter uma coluna fixa dentro dele:

 

Veja que o argumento do número de coluna é 2 porque queremos o valor de janeiro. Mas e se quisermos copiar a fórmula para trazer todos os anos?

Cansei de ver profissional (sic) fazer isso:

 

O cidadão colocava os números de coluna numa célula e vinculava o PROCV a ele. Mas ficava pior: muitos pintavam a fonte de branco para ninguém ver…

Ou então coloca a função COL () dentro do PROCV. Faz gambiarra mas não consegue parar de usar o PROCV…

O COL () – 1 vai trazer a coluna de janeiro, já que a coluna onde a fórmula está é a coluna C (3). Como janeiro na tabela está na coluna B basta subtrair 1 da coluna onde a fórmula está.

Não é ERRADO, afinal de contas a resposta estará correta. Mas é preciso ter cuidado, porque se aparecer uma coluna a mais em algum lugar já não será mais 1 de subtração. Então o modelo terá um cuidado a mais, o de verificar a fórmula sempre que aparecer uma nova coluna. Em um modelo profissional o quanto menos de checagens que precisarmos fazer, melhor…

Motivo 3 para você parar de usar o PROCV

BUSCA NA VERTICAL

O “V” do PROCV significa “vertical”, isto é, ele pesquisa da linha superior para baixo. Mas não procura para o lado. Se quisermos buscar para o lado, precisaremos usar o PROCH.

No exemplo a seguir não queremos escolher o estado, queremos escolher a data. Já sabemos que os dados serão sempre de SP, só queremos alternar as datas. PROCV não funciona. 

 

 

Motivo 4 para você parar de usar o PROCV

“MINDSET”

Albert Maslow (o da pirâmide) cunhou uma frase “Se você é um martelo você tenderá a ver tudo como um prego”.

Na verdade, o GRANDE problema de se querer a todo custo aprender PROCV está no teu mindset. “Todo mundo” usa o PROCV.

Muitas planilhas usadas em empresas já existem há anos e são usadas por diversos profissionais. Muitas foram construídas há 5, 6 anos e por isso utilizam ferramentas daquela época.

Elas nunca passaram por um upgrade, porque afinal de contas elas funcionam bem. É como se fôssemos voltar à Lua usando o computador usado na Apollo XI. Bom, eles funcionavam e levaram o homem até lá, não é mesmo? Para que fazer upgrade?

Então muitos profissionais querem aprender a usar o PROCV para lidar com planilhas desatualizadas. E pior: quando criarem planilhas, como só conhecem as funções arcaicas eles colocarão nas novas planilhas…as funções antigas.

E se você acha que vai ser promovido, ganhar mais e ser reconhecido por conhecer PROCV lamento informar, mas você está se nivelando por baixo e está basicamente sendo um martelo. Como você quer se destacar fazendo exatamente o que “todo mundo” faz?

Há alguns anos o Excel dispõe de uma função MUITO MELHOR e que resolve todos os problemas apresentados nesse texto. Ela é o PROCX. E se você nunca ouviu falar, que bom! Provavelmente quase ninguém na sua empresa conhece.

Lembre sempre disto: não estamos falando de conhecer um monte de funções. É a técnica de modelagem, a forma de pensar a criação de planilhas!

OUTROS ARTIGOS DE INTERESSE

O que é ?

O gráfico de tornou-se popular através da empresa de consultoria McKinsey no final do séc XX em apresentações para seus clientes.

Sua utilidade é apresentar de forma visual a decomposição de um valor principal até o resultado final. Significa que em toda a situação onde queremos mostrar como um valor X se transformou em Y através de adições  / subtrações ao longo de itens ou tempo poderiam ser apresentadas em um Gráfico de Waterfall. Se você deseja comparar ítens então precisa de um gráfico de barras, não de Waterfall.

Como fazer um Gráfico de Waterfall no Excel

Primeiramente precisamos de uma tabela com os valores que apresente:

• O valor inicial
• Cada ítem de variação
• O valor final

Veja o exemplo a seguir. Temos uma receita (valor inicial) e os demais lançamentos. Note que não inserimos subtotais (lucro bruto, lucro operacional, etc.), somente o valor final:

Agora vamos selecionar o intervalo da tabela e ir em INSERIR > GRÁFICO e procurar o que o Excel chama de “Cascata”:

Ele apresentará esse formato:

Precisamos fazer alguns ajustes. Começaremos formatando a fonte do Eixo Vertical (de valores). Basa clicar sobre ele e ir na área de formatação de fonte:

Defina uma cor, tamanho e tipo de fonte.

Em seguida faremos o mesmo com o eixo de categorias (se o texto estiver muito grande, tente deixá-lo com algumas abreviações – se o leitor puder entendê-las).

Vamos formatar as cores. Note que o Gráfico de Waterfall mostra 2 cores diferentes, uma para positivo e outra para valores negativos. Não precisamos de cores diferentes, basta o tom. Então selecionamos o gráfico e vamos em DESIGN DO GRÁFICO > ALTERAR CORES e escolhemos alguma paleta monocromática:

Vamos formatar o eixo de valores. Clique sobre ele com o botão direito e vá em FORMATAR EIXO:

Na tela que surgir à direita (Opções do Eixo), fixe o mínimo em zero para não correr o risco de distorcer o tamanho das colunas).

Se quiser adicione marcas de escala junto ao eixo (são os riscos que aparecem junto aos valores do eixo).

Em seguida clicamos no ícone da lata de tinta (formataçã0) e caso queira, remova a linha (ela vai remover a marca de escala, se houver):

Clique no último valor e indique que ele é o total e não um componente da variação. Se você notar, o valor de 2.000 aparece como se fosse uma variação, mas na verdade ele é o valor final. Clique somente nele (clique uma vez e depois mais uma vez para selecionar o ponto). Em seguida vá no botão direito do mouse e selecione a opção DEFINIR COMO TOTAL.

Dessa forma seu Gráfico de Waterfall mostrará o último ponto como o valor final.

Vamos melhorar a largura das colunas. Clique sobre qualquer uma delas com o botão direito e vá em FORMATAR SÉRIE DE DADOS e reduza o espaçamento e remova a lninha de conexão.

Agora você vai decidir se mostrará todos os valores nas colunas ou não.

Caso opte por mostrar os valores, apague o eixo vertical para nao ficar repetitivo.

Se remover os valores, mantenha o eixo.

Lembre-se de que se há a necessidade de se LER os números é bem provável que você precise de uma tabela ao invés de um gráfico. Então cuidado para não transformar seu gráfico em uma tabela.

Veja as 2 situações a seguir:

OUTROS ARTIGOS DE INTERESSE

O que é um Bullet Chart

O Bullet Chart foi criado por Stephen Few por volta de 2005 com o objetivo de ser a solução PROFISISONAL para as aberrações chamadas de Gráficos de Velocímetro.

O Bullet Chart e capaz de apresentar de forma mais clara valores e suas relações com a meta / períodos anteriores, sem a decoração existente nos gráficos (sic) de velocímetro.

Como fazer um Bullet Chart no Excel

No link a seguir colocamos o tutorial para você aprender a criar um bullet chart no Excel.

E lembre-se de nos seguir no Youtube! 

OUTROS ARTIGOS DE INTERESSE

Função KEEPFILTERS

A função KEEPFILTERS é usada em combinação com a função CALCULATE de maneira a impedir que ela sobreponha um filtro existente.

Quando mostramos a função CALCULATE mostramos que ela “anula” o filtro existente e pode ser que não queiramos que ela faça isso.

 O exemplo que mostramos na função foi o cálculo da RECEITA da família Legumes, e tivemos esse resultado:

Nossa função aplicava um filtro de Família Legumes e como nosso visual estava usando o contexto de filtro de família então a função anulava esse contexto, por isso que aparecia o valor de Receita de Legumes para todas as famílias. A CALCULATE passou a ignorar essa informação.

O que faremos agora com a função KEEPFILTERS é manter esse filtro existente, isto é, a função CALCULATE irá considerar ambos os filtros.

Veja como ficará nossa função:

Quando colocamos em um visual será isso o que veremos:

A leitura da fórmula será:

1ª Linha: Filtro Frutas e Filtro Legumes. Não há nenhuma venda simultânea das 2 famílias.

2ª Linha: Filtro Legumes e Filtro Legumes: Aí sim temos venda de Legumes.

3ª Linha: Filtro Verduras e Filtro Legumes: Aqui também não dá para uma venda ser simultaneamente de Verduras e Legumes, por isso temos zero.

Se modificarmos nosso visual para incluir o nome do produto nós veremos isto:

Na função CALCULATE inicial vemos o valor de Legumes aparecendo nas 3 famílias.

Nos produtos somente temos valores para os produtos da família legumes porque a função está filtrando a família legumes, então somente teremos vendas dos produtos daquela família.

A função KEEPFILTERS é mais consistente, porque só aparece receita naquele item que tiver Legumes em sua família (quer seja no total dela ou nos produtos que compõem a família).

 

OUTROS ARTIGOS DE INTERESSE

Função SUMX

A função SUMX é uma função faz uma soma, mas ao contrário da função SUM ela soma valores a partir da leitura de cada uma das linhas de uma determinada tabela.

Depois que explicar vai ficar mais simples de entender 🙂

No exemplo a seguir queremos calcular o custo total de cada um dos produtos. Nós temos uma tabela com o cadastro de produtos e a indicação de seu custo unitário e outra tabela com a informação de venda desses produtos e portanto a quantidade de cada um deles.

Esta é a tabela de produtos:

Esta a de vendas. Note que elas se relacionam pela coluna de Código do Produto.

Se criarmos uma medida simplesmente multiplicando a coluna de Custo Unitário e de Quantidade teremos isto:

Note que Quantidade é uma medida que está SOMANDO a coluna de Volume de vendas. Já no caso do custo unitário precisamos fazer alguma “agregação” dele através de uma função. No caso estamos usando a função SUM.

O que essa função SUMX fará?

Lembremos sempre da questão do contexto. Se por exemplo colocarmos essa medida em um cartão nós teremos isto:

O contexto do cartão será TUDO, então a medida vai somar toda a quantidade (55.300) e vai multiplicar pela soma do custo unitário (5,83):

Óbvio que está errado, porque na verdade a função deveria ter pego o custo unitário para cada produto, multiplicado pelo volume de vendas de cada um deles e somente depois disto somado os valores.

Se colocarmos esta medida em uma tabela é isto o que vamos ver:

O custo de cada produto está correto, porque a medida está trabalhando naquele contexto de produto, então ela vai somar somente a quantidade daquele produto e vai “somar”  custo unitário dele.

 

Como só temos um custo para cada produto então ela vai fazer a conta corretamente.

Já no total da família temos um valor errado, pois a medida irá somar o volume de vendas de todos os produtos daquela família (está OK) . Em seguida vai multiplicar pela soma de todos os custos unitários dos produtos daquela família. Bom, já sabemos que não vai dar certo.

Temos esse problema porque a função SUM está fazendo o que se espera dela, que é trabalhar dentro do contexto de filtro, isto é, está somando os valores para aquele produto, cidade, estado, etc…

Para termos o cálculo correto precisamos da função SUMX, porque é ela quem “anda” por todas as linhas da tabela antes de fazer a soma (a isto chamamos de contexto de linha).

Precisamos dizer que é para a função ir de linha em linha na tabela de PRODUTOS fazendo o cálculo de cada um dos produtos, e somente após isto ela somá-los.

Sintaxe da função SUMX

 

A sintaxe dela será esta:

O 1º argumento é a tabela onde a função vai “andar” (em inglês chama-se iteration, mas resolvi abrasileirar para “andar”). Significa que ela irá ler linha por linha da tabela de produtos.

O 2º argumento indica que cálculo ela deve fazer ao andar linha por linha. Bom, queremos pegar o volume de vendas DAQUELE PRODUTO e multiplicar pelo seu CUSTO UNITÁRIO.

Veja que aqui não estamos somando a coluna de Custo Unitário, estamos apenas indicando a coluna, pois é assim que a função irá multiplicar cada linha.

Depois de fazer essa multiplicação ela vai somar o seu resultado.

Nossa tabela ficará assim:

Veja que agora o cálculo está correto, mesmo nos totais da famílias, porque a função está sempre lendo linha a linha da tabela de produtos e somando somente o resultado final na família.

No caso do exemplo com a SUM ela somava o custo unitário daquela família.

 E se usássemos a tabela de Vendas?

Ela também funcionaria, pois ele somente iria somar os valores depois de fazer a multiplicação em cada uma das linhas, o único cuidado seria para trazer o valor do custo unitário, pois precisaríamos usar a função RELATED para trazer o valor individual de cada produto para a medida:

 

A função vai “andar” por todas as linhas da tabela de Vendas fazendo a multiplicação. Em seguida despejará seu resultado de acordo com o contexto de filtro que tivermos em nosso visual:

 

 

OUTROS ARTIGOS DE INTERESSE

Função FILTER

A função FILTER filtra tabelas com base em determinados critérios e posteriormente as medias utilizam esse filtro.

Quer dizer que essa função não funciona “sozinha”, ela precisa de uma outra função. Normalmente a função CALCULATE ou as funções “X” a utilizam, já que elas atuam diretamente no chamado contexto de linha, isto é diretamente nas linhas da tabela.

A função FILTER irá trabalhar no contexto de linha, “filtrando” a tabela para que a seguir esses dados filtrados sejam mensurados em alguma medida.

No exemplo a seguir criamos uma medida para somar a receita somente da família “Frutas”.

A função FILTER tem 2 argumentos: a tabela a ser filtrada e o critério para aquele filtro.

Exemplo da função

Veja como ficará para nosso exemplo:

A medida base será a função CALCULATE, uma vez que queremos calcular a RECEITA para aquela família.

O 2º argumento da CALCULATE será o critério. No nosso caso será a família FRUTAS.

No 1º argumento da nossa função informamos a tabela, que é a de Cadastro de Produtos. O 2º argumento será o nosso critério, que corresponde à Família Frutas.

Depois de formatar nossa matriz ficará assim:

As demais famílias estão em branco porque na nossa medida o 1º cálculo é filtrar nossa tabela de Produtos para a família Frutas.

Em termos visuais a medida enxerga somente isso:

Então é como se as demais famílias não existissem.

Daí quando colocamos essa medida em um visual o contexto de filtro passa a ser aplicado (em termos bem gerais, o contexto de linha refere-se às linhas da tabela e o contexto de filtro refere-se ao que vemos nos visuais).

Quando a medida passa para a Matriz ela vai aplicar o contexto de filtro, então na linha de Frutas a função vai olhar a tabela FILTRADA e somar os valores da Receita da Família Frutas.

Quando estivermos na linha da Família Legumes ela fará o mesmo, mas como a tabela filtrada tem somente Frutas, ele vai dar zero, porque na tabela de Frutas não tem família legumes.

O novo layout…

Uma tabela de Produtos mostrará isso:

A função FILTER filtra a tabela para Frutas e a função CALCULATE “anda” pelo visual com os produtos para somar suas receitas.  Na tabela filtrada não tem abóbora, porque é um legume, então ele retorna zero, porque ele está lendo primeiramente o filtro aplicado.

Se pusermos outro campo, o Estado por exemplo, teremos somente os valores daquele estado da Receita de…Frutas:

Isso ocorre porque relacionamos nossas tabelas e o filtro se “propaga” para aquela tabela.

Se você se lembra da função CALCULATE talvez se lembre que nela nós usamos um critério também. Então qual a diferença?

Num visual nossa diferença aparece assim:

Isso porque a função CALCULATE trabalha no contexto de filtro. Como estamos usando um critério de Famílias esse critério passa a valer sobre as linhas do visual, como se não existissem.

Por isso o valor repetido, pois a função está ignorando a informação do visual.

Se fôssemos usar a função FILTER para simular o que esse exemplo da CALCULATE está fazendo nós teríamos isso:

 

OUTROS ARTIGOS DE INTERESSE

Função COALESCE

A Microsoft periodicamente atualiza o PowerBI não só com novos visuais mas também com novas funções. A função COALESCE é uma função recente, tendo sido lançada no 1º semestre de 2020.

Basicamente é uma função condicional mas que avalia somente se determinado item é vazio, daí ela procura a próxima condição até encontrar a 1ª condição que não seja vazia.

No exemplo a seguir temos um slicer mostrando ANO e MÊS, e queremos que o cartão mostre qual item está selecionado. Se for um ano, que mostre aquele ano, se for um mês, que mostre aquele mês.

Para que isso aconteça vamos usar a função COALESCE para verificar se há um ano selecionado, se não tiver ano selecionado ela trará o mês selecionado.

Usaremos em conjunto a função SELECTEDVALUE . 

Vamos em PÁGINA INICIAL > NOVA MEDIDA para inserir a medida:

Nossa medida ficará assim. Em seguida vamos explicá-la.

 

As 2 funções SELECTEDVALUE verificam, em 1º, se há um mês selecionado e em seguida se há um ano. Na checagem do ano usamos o 2º argumento da função, para o caso de não haver um ano selecionado.

A função COALESCE retorna o 1º item não vazio, e por isso colocamos os itens em ordem crescente, porque se escolhemos um mês nós já escolheremos “por tabela” um não, o que faria a função sempre retornar o ano se esse fosse seu 1º argumento.

Então a função vai verificar nessa ordem:

Por fim temos a função FORMAT, para formatar o resultado em formato de texto, caso contrário quando tivermos um ano ele mostrará 2,01 ao invés de 2010.

 

 

 

 

 

 

OUTROS ARTIGOS DE INTERESSE

Função ALLSELECTED

A função ALLSELECTED é uma função que serve para criarmos medidas nas quais queremos ver o total de ítens que estejam sendo filtrados naquele visual, eliminando os ítens que estão ocultos, o que a função ALL não é capaz de fazer.

Na tabela abaixo queremos calcular o % da receita total dos produtos:

Fizemos isso combinando as funções DIVIDE, CALCULATE e ALL:

A priori está tudo OK, mas o problema ocorre quando filtramos algum produto. Se isso ocorrer, veremos isto:

Mas na verdade nós queremos ver o % do total dos produtos que estiverem visíveis naquele momento. No exemplo acima queremos que a soma dos 2 produtos dê 100%.

Para que isso ocorra nós usaremos a função ALLSELECTED, que cria uma lista de todos os ítens selecionados.

Veja como ela ficará:

 

Basicamente substituímos a função ALL usada no exemplo anterior pela ALLSELECTED.

Depois de formatarmos teremos esse resultado:

Dessa forma nossa medida mostrará sempre o % dos ítens que estiverem selecionados.

OUTROS ARTIGOS DE INTERESSE

Função DISTINCT

A função DISTINCT gera uma lista de valores distintos, isto é, sem repetições, podendo ser incluído tanto em uma medida quanto em uma tabela.

No exemplo a seguir vamos criar uma tabela com os nomes das Famílias de produtos.

Para inserir uma nova tabela vamos em PÁGINA INICIAL > NOVA TABELA:

Em seguida damos um nome para nossa nova tabela e inserimos a função DISTINCT:

E o resultado será este:

 

OUTROS ARTIGOS DE INTERESSE