Excel - Avançado (Office 2010)
Conteúdo 01
Importação de dados de arquivo de Bloco de Notas com caractere delimitador.
Alguns sistemas exportam dados em formato .txt e, para que os dados sejam importados no Excel corretamente, é necessário realizar o procedimento que detalho nesse vídeo.
Conteúdo 02
O vídeo é sobre importação de arquivos de texto (.txt) para o Excel no formato de Largura Fixa. Se você ainda não viu a importação de texto com delimitador, acesse esse link https://youtu.be/lOSthZGOjAU !
Como realizar a importação? Acesse Dados - No bloco Obter Dados Externos - De Texto.
Acesse o vídeo abaixo e veja o passo-a-passo de como importar os dados com largura fixa no Office 2010!
Conteúdo 03
A importação de arquivos do tipo Access, permite importar dados que estão nas tabelas do Access e importá-las para o Excel de forma bem simples.
Assista o vídeo e veja o passo-a-passo!
Conteúdo 04
Neste vídeo, apresento a classificação simples, que é feita tendo com uma coluna como base.
Em seguida, apresento a classificação por níveis, onde mais de uma coluna é usada para a classificação. Isso faz com que uma coluna seja classificada conforme outra. Exemplo: Tenho uma planilha de cadastro de cliente e quero classificá-los conforme cidade em ordem alfabética (1ª classificação), dentro de cada cidade, quero classificar por bairro também em ordem alfabética (2ª classificação) e, por fim, dentro de cada bairro, quero classificar os clientes por ordem alfabética (3ª classificação)!
Conteúdo 05
O Excel permite classificar os dados não somente por ordem alfabética, mas também por uma ordem criada por nós. Exemplo: classificar uma planilha conforme o funcionário mais antigo da loja até o mais recém contratado. Ou classificar os produtos conforme ranking de vendas. Ou ainda classificar por cargo...
Tudo isso é possível com a Classificação Personalizada. Clique em Classificar e Filtrar na guia Página Inicial, depois Classificação Personalizada.
Ah, a partir do momento que a lista foi criada, é possível usá-la no recurso de autopreenchimento, quando se clica na alça de preenchimento (aquele quadradinho preto do lado direito inferior quando a célula está selecionada).
O filtro avançado é utilizado quando é necessário extrair dados simultaneamente de uma planilha baseados em critérios diferentes para cada grupo/tipo. Se fosse usar um Autofiltro (Filtro Simples), ao se definir o primeiro grupo de critérios, não haveria nenhum problema e os dados seriam resultados normalmente. Porém, ao se definir o segundo grupo dentro do mesmo processo de filtragem, esse seria baseado no resultado do filtro do primeiro grupo, não considerando, portanto, outros dados que estão na planilha e que não se enquadraram no primeiro grupo de critérios.
O Excel possui uma ferramenta chamada Subtotal, que calcula um "total" conforme grupos pré-configurados. Esse total não precisa ser necessariamente um somatório, mas também pode ser uma média, uma contagem, um maior ou menor valor, dentre outras possibilidades.
Para usar o Subtotal, clique na guia Dados, depois Subtotal.
Para copiar e colar apenas os subtotais gerados nesta ferramenta Subtotal, é necessário um procedimento um pouco diferente do habitual. Veja o vídeo.
Conteúdo 08
A função CONT.SE é utilizada para contar quantas células possuem determinado conteúdo. Exemplo: para saber quantos funcionários são do departamento de RH, basta contar todas as células em que esteja escrito RH na coluna de departamento.
O mesmo conceito se aplica para a função SOMASE, porém ela irá somar todos os valores que atendem a um determinado critério. Exemplo: para saber qual é a folha de pagamento total do departamento de Logística, basta somar todos os salários dos funcionários em que esteja escrito Logística na coluna de departamento.
As funções de Banco de Dados, permitem calcular com base em vários critérios podendo estar ou não em colunas diferentes!
Há várias funções que começam com BD: BDSOMA, BDMÉDIA, BDEXTRAIR... Todas são construídas da mesma maneira.
Conteúdo 10
Essas funções permitem realizar cálculos com variados critérios, porém obrigatoriamente precisam estar em colunas diferentes.
Para ficar mais fácil saber quando usar cada uma, elaborei este infográfico:
A validação de dados é ideal para evitar inconsistência de dados em nossa planilha. Assim, trava-se o formato do conteúdo a ser inserido em uma célula, bloqueando a inserção de qualquer outro tipo fora do padrão.
A opção de lista permitirá aparecer uma seta ao lado da célula que, ao ser pressionada, mostrará uma lista de itens para que seja escolhido um deles.
Neste vídeo, mostro as diversas outras opções, além da lista, de limitar o conteúdo de uma célula.
Conteúdo 12
A tabela dinâmica é um dos recursos mais utilizados atualmente pelo mundo corporativo e se faz essencial em qualquer currículo em áreas administrativas e afins.
A tabela dinâmica permite elaborar relatórios e consolidações de dados de forma prática, ajustando-se rapidamente para obtenção de novos resultados. Sendo assim, é possível obter total vendido por cada funcionário, quantidade de produtos vendidos, total de peças vendidas, porcentagem de gastos por setor etc. É uma ferramenta riquíssima!
Veja o vídeo e aprenda de forma fácil a construir uma tabela dinâmica.
Para ajudar na identificação dos campos, construí este infográfico:
Continuando com a tabela dinâmica, neste vídeo mostro como construir um gráfico dinâmico. Ele com a tabela dinâmica são vinculados, ou seja, uma alteração realizada na tabela reflete no gráfico e vice-versa.
A tabela dinâmica possui vários recursos para sua configuração e formatação. É possível:
- Apresentar resultados como porcentagem de um total;
- Contar, calcular média, encontrar o maior valor, dentre outras operações, ao invés de apenas somar como é o padrão;
- Preencher espaços vazios automaticamente com um conteúdo predefinido;
- Definir se os subtotais serão visíveis ou não e como;
Confira este vídeo e veja como sua tabela dinâmica pode ficar bem diferente do padrão apresentado no momento da sua construção.
- Definir a estrutura de apresentação da tabela;
- Modificar a cor;
- Formatar os valores adequadamente...
Enfim, é possível fazer muita coisa com a tabela dinâmica!
A segmentação de Dados é um recurso que permite filtrar a tabela a partir de dados que não necessariamente estejam sendo mostrados dentro da tabela dinâmica em si. Isso significa que é possível filtrar por data, por exemplo, mesmo que as datas não estejam visíveis na tabela. Porém, a coluna de data precisa existir na tabela /planilha que deu origem à tabela dinâmica.
Veja o vídeo e entenda melhor esse recurso, muito utilizado inclusive em dashboards.
Um recurso que muita gente desconhece é a geração automática de relatórios individuais da tabela dinâmica e muitas vezes se perde muito tempo construindo manualmente relatórios manuais.
Para geração de relatórios, use o campo Filtros de relatório da tabela dinâmica.
Veja o vídeo e confira como funciona!
Conteúdo 13
Quando o Excel trabalha com uma quantidade grande de dados e tem-se a necessidade de usar várias ferramentas de análise, ele pode acabar travando. No entanto, boa parte dos dados que estão na planilha, nesse momento podem não ser úteis e só ajudam a sobrecarregar o Excel.
Uma solução para este problema, é usar a ferramenta Microsoft Query e extrair para uma nova planilha somente as colunas essenciais. Além disso, já é possível realizar filtragens e classificações diretamente dentro do Microsoft Query, sendo exibido somente os dados que realmente interessam, aliviando drasticamente a sobrecarga no Excel.
Uma situação que pode acontecer ao usar a ferramenta é o Excel não ter referência para identificação das planilhas e colunas: Ele mostra uma tela de erro e logo em seguida não apresenta nenhuma planilha ou coluna na tela do Microsoft Query.
Neste vídeo, eu mostro como resolver esse erro, importar os dados necessários e ajustar a planilha final.
O Excel e o Access pertencem a mesma fabricante e, por isso, torna-se mais simples o processo de importação do Banco de dados Access para o Excel.
Da mesma forma que na importação de planilhas do Excel, com a ferramenta Microsoft Query é possível extrair para uma nova planilha somente as colunas essenciais das tabelas do Access. Além disso, já é possível realizar filtragens e classificações diretamente no Microsoft Query, sendo exibido somente os dados que realmente interessam.
Confira!
Conteúdo 15
A função SE é utilizada para que uma resposta seja apresentada de acordo com o teste realizado. Neste vídeo também ensino como fixar uma célula para que a mesma não seja incrementada ao se usar o autopreenchimento.
Confira o vídeo!
Conteúdo 16
A função SE na forma encadeada, permite obter mais respostas, além daquelas duas inicialmente que o SE na estrutura simples (comum) permite. Sendo assim, é possível realizar testes diferentes e obter as suas respectivas respostas caso os mesmos sejam atendidos / verdadeiros.
Veja essa diferença:
Exemplo 1 - SE Simples
=SE(B4="Bom";"Bonificação";"Inapto") - Considerando só haver as duas possibilidades de resposta.
Exemplo 2
=SE(B4="Bom";"Bonificação";SE(B4="Mediano";"Reavaliação";"Inapto")) - Considerando haver agora três possibilidades de resposta: Bom, Reavaliação e Inapto. Sendo que a última (Inapto) só será mostrada se as demais não forem atendidas. É o que sobrou.
Uma DICA: Sempre contem a quantidade de possibilidades de resposta. Se forem três, serão usadas duas estruturas de SE. Caso tenhamos dez possibilidades de resposta, serão usadas nove estruturas de SE. Ou seja, sempre será um Se a menos da quantidade de respostas.
Infográfico:
Conteúdo 17
A função SE juntamente com funções E ou OU, permite utilizar vários testes simultâneos para que uma resposta seja obtida.
Veja essa diferença:
Exemplo 1 - SE com a função E
=SE(E(A3="Ok";B3="Sim");"Classificado";"Desclassificado") - Nesse caso, para que seja mostrada a resposta "Classificado", é preciso que os dois testes sejam atendidos ao mesmo tempo.
Exemplo 1 - SE com a função OU
=SE(OU(A3="Ok";B3="Sim");"Classificado";"Desclassificado") - Já nessa situação, para que seja mostrada a resposta "Classificado", é preciso que pelo menos um dos testes seja atendido.
Infográfico:
Conteúdo 18
Vou postar alguns vídeos, para ajudar vocês a entender melhor como essa função é construída e como ela realmente funciona. Entendendo a dinâmica, a PROCV pode ser utilizada em variadas situações!
Como o nome diz: PROCV significa procura na vertical. Ela irá procurar um conteúdo determinado por nós, na planilha de base (que contém todas as informações já cadastradas) para assim, mostrar a informação que estamos precisando sobre o conteúdo que foi procurado.
Neste vídeo, serão preenchidas as informações do crachá a partir da digitação da matrícula no primeiro campo.
Assista ao vídeo!
Segue o segundo exemplo relacionado à função PROCV! Será preenchido um pequeno formulário na parte superior da planilha, que buscará informações sobre um determinado imóvel.
Segue o terceiro exemplo relacionado à função PROCV! Será preenchido um controle de vendas que, a partir do código informado, mostrará o nome do produto e o respectivo preço.
Segue o quarto exemplo relacionado à função PROCV! Será preenchido um relatório de pacientes que apresentará o IMC de cada um e a faixa a qual o paciente está incluído.
Infográfico:
Conteúdo 19
Nos vídeos anteriores, ensinei a função PROCV e, neste vídeo, eu apresento a função PROCH que significa procura na horizontal. Assim como na PROCV, a função PROCH irá procurar um conteúdo determinado por nós, na planilha de base (que contém todas as informações já cadastradas agora na horizontal) para assim, mostrar a informação que estamos precisando sobre o conteúdo que foi procurado.
Assista ao vídeo e veja que a PROCH é bem parecida com a função PROCV!
Conteúdo 20:
Para quem não tem a versão mais recente do Excel, o jeito é usar as funções ÍNDICE e CORRESP, também conhecidas como PROCV INVERTIDO, para resolver o problema!
Mas que problema é esse Izadora??!!
E como eu uso essas outras funções?
Assista ao vídeo, que eu explico direitinho quando o problema ocorre e como usar as funções ÍNDICE e CORRESP para resolver!
Mais conteúdos em breve!
Nenhum comentário :
Postar um comentário