Excel Avançado Quando leciono Excel avançado, e entro na parte da matéria que abordo sobre a função PROC (PROCV e PROCH), uma das maiores dúvidas/perguntas dos meus alunos é sobre comparar listas diferentes para achar valores que estejam numa listagem e em outra, vocês já devem ter ouvido falar, ou leram em várias apostilas, livros, artigos,… que em sua maioria deveriam estar com exemplos um tanto complicados, e que vocês acabam tentando encontrar o que almejam na mão mesmo. Para facilitar aos meus alunos eu montei este material neste post, vamos através de alguns passos realizar esta COMPARAÇÃO DE LISTAS. Passos: Fazer uma fórmula para comparar duas listas com dados (contábeis e valores), realizando assim uma conferência de valores entre uma lista e outra, e ainda informar as contas que não estão presentes na lista analisada. Usaremos, para este caso, a função PROCV (procura vertical). Ela irá buscar toda a lista 1 (Contabilidade ABC Simonsen) em busca de cada uma das contas contábeis que existem na lista 2 (Contabilidade ABC Simonsen – Hj). Ao encontrar, irá subtrair o valor da lista 2 (Contabilidade ABC Simonsen – Hj) com o valor da lista 1 (Contabilidade ABC Simonsen). Assim, se houver diferença entre os valores, ela irá mostrá-los. Na célula C3 iremos inserir, então, esta função. Clicamos em Inserir > Função (para quem gosta de tecla de atalho ela é a Ctrl+F2). Logo, escolhemos a categoria Planilha (lembrando mais uma vez que estou utilizando a versão do BrOffice 3.3.2). Posterior a essa escolha, vamos escolher a função, vamos preencher as seguintes informações: Orientações sobre o PROCV: Critério de pesquisa: é a conta que estamos procurando na lista 2 (Contabilidade ABC Simonsen – Hj). No exemplo, queremos procurar na lista 2 (Contabilidade ABC Simonsen – Hj) a conta 122599.01. A função PROCV procura somente um item por vez, e somente na primeira coluna da matriz / intervalo. Matriz: é o intervalo de dados onde a função vai procurar o valor informado no argumento critério de pesquisa. Note que a referência possui “$” nas células. Isso serve para “ancorar” a célula, pois, depois de pronta ela, será copiada para baixo e, caso não seja “ancorada”, ela vira E4:F14, E5:F15 na seguinte, etc. Fazendo da maneira indicada, isto não acontece. Índice: não tem nada a ver com a coluna do Excel. Na verdade, neste campo colocamos o número da coluna onde, na lista 2 (Contabilidade ABC Simonsen – Hj), está a informação que queremos trazer para a lista 1 (Contabilidade ABC Simonsen). Como queremos o valor, ele está na segunda coluna de nosso intervalo (lembre-se de que ele vai da coluna G – 1ª – até a H – 2ª coluna). Se fôssemos considerar as colunas do Excel, seria a 8 (imagine se a lista estivesse na coluna TZ– qual seria o número?) Ordem de classificação: este campo está em fonte normal, ao contrário dos de cima, que estão em negrito. Isso porque ele é um campo opcional. Se não pusermos nada nele o Excel vai assumir um valor padrão. É é aí onde o problema começa. No PROCV, o BrOffice.org Calc/Excel pode assumir a busca de dados de duas formas: por aproximação ou exatamente o que se digitou. Se não pusermos nada, ele assume a busca por aproximação. Isso é perigoso, principalmente se nossa lista estiver sem classificação. Então, para garantir que o Excel vá encontrar corretamente o que procuro, coloco a opção 0 (se quisesse uma procura aproximada bastaria colocar 1 – ou não informar nada). Depois disso, basta dar OK. Aparecerá o valor de R$300.000,00. Mas ele não é o valor da diferença. Então, para ficar isso, basta editar a fórmula (pressionando a tecla F2) e em seguida digitando ao final –B3. A fórmula ficará: =PROCV(A3;$E$3:$F$13;2;0)-B3 Agora, basta copiar até o final. Como observado abaixo: Mas veja o que aconteceu na célula C4: apareceu um erro#N/DISP Para quem é ou já foi o meu aluno, eu já disse diversas vezes que isso não é mensagem de erro, isso ocorre porque a fórmula não pôde encontrar na lista 2 (Contabilidade ABC Simonsen – Hj) a conta 1.01.01.02. Então, quando o PROCV não encontrar algum item, ele mostrará esta mensagem(#N/DISP). ATENÇÃO! Pode ser que a conta exista, mas esteja escrita com uma sintaxe errada, por exemplo, 122,599.02. Cuidado para verificar se os códigos são consistentes, para não ficar com a impressão de que a conta não existe. Podemos simplesmente deixar a Fórmula como está ou então corrigi-la para, em caso de não existir na lista 2 (Contabilidade ABC Simonsen – Hj), que apareça a mensagem informando. Podemos fazer isto usando outra função do BrOffice.org Calc/Excel, a chamada ÉERROS. Ela verifica se o resultado de uma fórmula dá erro. Veja como ela ficaria na célula C3: =SE(ÉERROS(PROCV(A3;$E$3:$F$13;2;0)-B3);”CHECAR”;PROCV(A3;$E$3:$F$13;2;0)-B3) No caso, ela foi associada à função SE para que, em caso de erros, ela faça uma coisa (no caso, mostrar o texto “Checar”) ou então simplesmente faça o cálculo. Se optar por usar a fórmula mencionada, ficará assim: Orientações sobre o ÉERROS: Ela retorna VERDADEIRO se o valor for um valor de erro. Valor: (obrigatório) Valor a ser testado. Outra maneira de visualizar a comparação: através de Cores Neste caso, também usamos a função PROCV e ÉERROS. Para tanto, marcamos o intervalo com as contas da lista 1 (Contabilidade ABC Simonsen), clicamos em Formatar > FormataçãoCondicional e colocamos as seguintes regras (para criar as demais, clique no botão adicionar): A 1ª fórmula procura a conta contábil da lista 1 (Contabilidade ABC Simonsen) na lista 2 (Contabilidade ABC Simonsen – Hj), e se os valores forem iguais, coloca em verde. A 2ª verifica se a conta da lista 1 (Contabilidade ABC Simonsen) está presente na lista 2 (Contabilidade ABC Simonsen – Hj). Se não estiver ele ficará amarela. E na 3ª, ele verifica se o valor da lista 1 (Contabilidade ABC Simonsen) é diferente da lista 2 (Contabilidade ABC Simonsen – Hj). Se for, fica em vermelho. Estes são as duas formas distintas para se realizar a mesma coisa. E não são os únicos. Há várias outras formas de se fazer este tipo de comparação. Dúvidas é só entrar em contato, segue os meus contatos abaixo e ao lado nas redes sociais. Abraços e até a próxima aula aos Domingos, ou próximo post por aqui. |