Excel intermediário (tratamento de dados e formatação visual, cenários, atingir metas e solver)

Prévia do material em texto

TRATAMENTO DE DADOS E FORMATAÇÃO VISUAL: 
1. ARRED( número ; casas_decimais_desejadas ): arredonda um numero até uma quantidade especificada de 
dígitos. Esta função, diferentemente da formatação para acréscimo ou redução de casas decimais na Página 
Inicial do Excel, realmente altera o valor da célula. A formatação por acréscimo e redução mencionada 
anteriormente é apenas uma alteração visual, no entanto, o valor da célula permanece constante. 
Analogamente, temos as funções ARRENDONDAR.PARA.BAIXO( número ; casas_decimais_desejadas ) e 
ARREDONDAR.PARA.CIMA( número ; casas_decimais_desejadas ). Temos também as funções INT( núm ), que 
arredonda um número para baixo até o inteiro mais próximo, e TRUNCAR( núm ; [núm_digitos] ), que trunca 
um número até um numero inteiro removendo a parte decimal ou fracionária do numero. 
2. AGREGAR( núm_função ; opção ; matriz ): retorna uma agregação em uma lista ou um banco de dados, Essa 
função é uma fórmula versátil que permite realizar operações simples como soma, média etc, excluindo valores 
que podem gerar erros, ou linhas ocultas, por exemplo. Núm_função é uma codificação interna para facilitar a 
sintaxe da função, opção é o que desejamos, desde ignorar erros a ignorar linhas/colunas ocultas, já matriz é o 
intervalo com os dados sob os quais deve ser feito a operação selecionada. 
3. ARRUMAR( texto ): remove os espaços de uma cadeia de texto, com exceção dos espaços simples entre 
palavras. Ao importar dados externos para o Excel, frequentemente encontramos formatações não padrão, 
como por exemplo, duplo espaço entre palavras, que atrapalham no uso das funções. Assim, em nosso banco 
de dados, podemos criar uma nova coluna/linha, utilizar a função ARRUMAR nela, transferir os dados corretos 
de volta a coluna original, selecionando o intervalo criado e já com as dados corrigidos, e apenas arrastando 
sobre o intervalo original (através da borda do intervalo novo) com o botão direito do mouse, e em seguida, 
selecionando a opção “copiar aqui somente valores”, para não manter a referência ou a fórmula, e então 
excluimos a coluna/linha auxiliar criada, voltando a formatação original da nossa planilha, no entanto, com os 
dados devidamente formatados. 
4. NUMERO.CARACTERES( texto ): retorna o número de caracteres em uma cadeia de texto (string). inclui espacos 
em branco, letras, simbolos e números. 
5. PROCURAR( texto_procurado; texto ): retorna a posição inicial de uma cadeia de texto encontrada em outra 
cadeia de texto, também pode ser utilizada para procurar caracteres, para um tratamento de dados específico. 
Essa função é case sesnsitive, para não diferenciar maiúscula e minúsculas, deve-se utilizar a função LOCALIZAR( 
texto_procurado ; texto ). 
6. EXT.TEXTO( texto ; num_inicial ; comprimento ): retorna os caracteres do meio de uma cadeia de texto, tendo 
a posição e o comprimento especificados. É utilizada para extrair texto como apenas o primeiro nome de uma 
pessoa. Para uma extração automatizada do primeiro nome, poe exemplo, o comprimento pode ser dado 
através da função LOCALIZAR(“ ”), significando fim do primeiro nome do indivíduo, menos um (LOCALIZAR 
retorna a posição do espaço, e não do último caracter de um nome). Para extrair o segundo nome da pessoa, 
pode-se utilizar novamente a função EXT.TEXTO e na posição inicial, utilizar a LOCALIZAR(“ ”) acrescido mais 
um. Já o comprimento é igual ao comprimento do nome inteiro subtraído do comprimento anterior ao início 
do segundo nome, ou seja LOCALIZAR(“ “ ; texto). 
Obs.: É possível também extrair texto de uma coluna de células através da ferramenta Texto para 
Colunas, onde um delimitador deve ser selecionado (como espaços ou tabulações). Assim, uma coluna com 
nome e sobrenome é dividida em duas colunas. Pode-se utilizar também o preenchimento relâmpago, no 
entanto, o preenchimento relâmpago necessita que você faca manualmente alguma(s) vezes o método, até o 
Excel compreender plenamente o padrão utilizado. Tem como atalho as teclas CTRL + E. 
Para adequar/ajustar a(o) largura/comprimento padrão das células do Excel, basta selecionar as 
colunas/linhas que devem receber nova formatação, e simplesmente ajustar uma única célula dentre as 
selecionadas, que tais padrões são expandidos as demais células. Analogamente, é possível alterar todas as 
células da planilha, bastando-se para isso, fazer a seleção no canto superior esquerdo, entre a divisão da 
coluna A e da linha 1 (que selecionará automaticamente todas as células) e então ajustar a largura e o 
comprimento. 
Obs.: Deve-se evitar a mesclagem de células, pois informações são perdidas e torna-se mais dificil o 
tratamento de dados. 
7. Para uma melhor visualização, ou mais clara dos dados, pode-se utilizar a formatação condicional de um 
conjunto de células. Podemos utilizar regras especiais, barras de dados, porcentagem, escalas de cor. Os dados 
podem ser comparados entre si (escala) ou entre valores preestabelecidos como metas, por exemplo. 
8. MINÚSCULA( texto ): converte todas as letras em uma cadeia de texto em minúscula. Analogamente temos a 
função MAIÚSCULA. Temos também PRI.MAIÚSCULA, que é a formatação que mais recomendada e utilizada, 
que é o primeiro caracter de cada palavra em maiúsculo. 
9. Para fazer a substituição de uma cadeia de caracteres por outro específico, podemos utilizar as funções MUDAR 
e SUBSTITUIR. SINTAXE: MUDAR( texto_antigo ; núm inicial de substituição ; num de caracteres a ser alterados 
; novo texto ) substitui parte de uma cadeia de texto por uma cadeia diferente. SUBSTITUIR(texto; texto_antigo; 
novo texto;[num da ocorrencia]) substitui um texto antigo por outro novo em uma cadeia de texto. 
 
FERRAMENTAS 
Hiperlinks: botões, formas, ícones e células de redirecionamento podem ser criadas com o recurso do 
Hiperlink. Tais links podem redirecionar a documentos externos, páginas da internet, ou até mesmo diferentes 
planilhas da sua pasta de trabalho ao ter a forma associada, selecionada. 
 Caso seja necessário um documento compacto (que ocupe reduzido espaço em memória) é possível 
compactar imagens (de catálogos, por exemplo) para reduzir sua qualidade, e assim, seu peso. Formatar → Compactar 
imagens. 
 Para realizar conferência das suas fórmulas complexas, pode-se utilizar a função FORMULA TEXTO, que 
imprime a fórmula utilizada naquela célula, permitindo que examinemos de forma mais criteriosa, não apenas através 
da barra de fórmulas. 
 Uma data é armazenada no excel como um número (de forma crescente contando a partir de 01/01/1900), 
dessa forma pode-se fazer operações entre datas de forma simplificada. Por exemplo, a quantidade de dias entre uma 
data e outra é encontrada unicamente pela subtração de tais datas. Uma data de interesse, como por exemplo, daqui 
a 180 dias, pode ser encontrada simplesmemte somando a data de hoje com 180. 
Obs.; Existem também funções específicas que permitem a extração de cada dado de uma data, como por exemplo as 
funções DIA(data), MÊS(data) e ANO(data). Com tais dados, quando for necessario, podemos retornar ao valor incial 
através da funcao DATA( ano ; mês ; dia ), onde dentro dela, podemos fazer operações como descobrir que dia é daqui 
a 45 dias, para pagamento de faturas por exemplo. Para visualização das iniciais correspondentes aos números dos 
meses (obtidos pela função MÊS(data)), pode-se realizar a conversao através da sintaxe a seguir da fórmula: =TEXTO( 
MÊS(data) ; “MMM” ). 
 Para levar em conta apenas dias úteis entre duas datas distintas, devemos utilizar a função DIATRABALHO( 
data_inicial ; dias ; [intervalo_feriados] ). Esta função, por sua vez, retorna uma data especificada após os dias úteis de 
trabalho (parâmetro dias), a partir da data inicial (parâmetro data_inicial). Caso não sejam especificados o parâmetro 
[feriado], ela não levará em conta apenas finais de semana. Assim, deve-se ter uma lista com os feriados da sua região, 
e passaresse intervalo como argumento para função, para um resultado com maior acurácia. Já a função 
DIATRABALHOTOTAL( data inicial ; data final ; [intervalo_feriados] ) retorna a quantidade de dias úteis entre tais datas. 
 A função DIA.DA.SEMANA recebe uma data como parâmetro, e retorna um número de 1 a 7 correspondendo 
aos dias da semana (1-domingo, 2-segunda, .... 7-sábado). 
 Em planilhas extensas, ou sobre as quais mais de uma pessoa opera, frequentemente podem ocorrer dados 
duplicados, que interferem no resultado, tornando-o inconsistente. No entanto, o próprio excel possui ferramenta 
que exclui valores duplicados. Dados → Remover duplicatas. Assim, pode-se selecionar qual critério deve ser utilizado 
como verificador de dualidade. Deve-se utilizar de preferência um valor único e característico, como CPF, RG, CNPJ 
etc. Caso se utilize planilhas apenas como fonte de dados ESTÁTICOS, ao final da inserção, pode-se ocultar esta panilha, 
para melhor visualização da pasta de trabalho, clicando sobre o nome da planilha com o botão direito, e então 
selecionando Ocultar. Para trazer de volta a visualização, basta clicar novamente com o botão direito sobre o nome 
das demais planilhas e então selecionar Exibir. 
FERRAMENTAS AVANÇADAS 
1. Segurança e distribuição: 
Você pode bloquar as alterações de fórmulas e dados na sua planilha selecionando a guia Revisão → Proteger 
Planilha. Para que os usuários possam alterar apenas um intervalo de células, ou conjunto de células em específico, 
pode-se desbloqueá-las antes de proteger a planilha (todas as células vêm bloqueadas por padrão). Para isso, selecione 
o intervalo/conjunto de células, precione CRTL + 1 (ou clique com botão direito → Formatar Células) e na subseção 
“proteção”, desmarque a caixa de seleção “bloqueada”. Todas as células que não estiverem bloqueadas, ao proteger 
a planilha, estas poderão ser modificadas. 
Pode-se também, proteger toda a pasta de trabalho, na guia Revisão → Proteger pasta de trabalho. Caso tenha 
alguma coluna/linha/planilha oculta, proteger a mesma impede que o usuário sem a senha exiba tais informações 
contidas nesses espaços. Já para proteger o arquivo inteiro, para antes mesmo de ser aberto requisitar senha, 
selecione a guia Arquivo → Salvar como → Procurar → Ferramentas (lista suspensa) → Opções gerais → Senha de 
proteção. 
Para que mais de uma pessoa possa alterar o mesmo documento de forma síncrona, deve-se selecionar a 
ferramenta “Compartilhar Pasta de Trabalho” na barra de ferramentas de acesso rápido, seleciona a opção “Use o 
antigo recurso de pastas de trabalho compartilhada ...”. 
2. Cenários: 
Na guia Dados → Testes de hipóteses, podemos criar diferentes cenários, para em um momento que formos 
comparar, não precisarmos alterar os valores de referência um por um, simplesmente alternamos o cenário para o 
desejado, e visualizamos os dados. (essa ferramenta é particularmente interessante durante uma apresentação). 
Após criada uma série de possíveis cenários, para uma melhor visão geral dos cenários, alternando um ou mais 
parâmetros, pode-se gerar um relatório contendo as principais informações dos diversos cenários, possibilitando 
assim, uma melhor decisão de qual rumo tomar, para por exemplo, obter um melhor faturamento. Para isso deve-se 
selecionar a guia Dados → Testes de hipóteses → Gerenciados de Cenários → Resumir. 
3. Atingir metas e solver: 
Novamente em Testes de hipóteses → Atingir Meta, podemos analisar um dado cenário necessário quando 
uma determinada meta for atingida, vendo assim todos os valores correspondentes para uma série de parâmetros. 
Pode ser interessante para obter informações que antes não seriam possíveis, como parâmetros de funções como 
PGTO, por exemplo, tendo outras variáveis estabelecidas (de forma a possibilitar o cálculo da variável restante) fixas, 
sendo possível encontrar quantos anos levaria para quitar a dívida pagando uma parcela X por mês, etc. 
Assim, como o objetivo da ferramenta Atingir Metas é variar de forma automatizada os parâmetros envolvidos 
para atingir tal meta estipulada, os campos/células selecionadas devem ser fórmulas, de forma que a ferramenta 
conheça os parâmetros envolvidos, relações entre eles, e suas limitações. 
Para problemas mais complexos, envolvendo mais de uma possível variável, deve-se utilizar a ferramenta Solver. 
GERENCIAMENTO E ANÁLISE DE DADOS 
A partir de um banco de dados, é possivel formatá-lo como uma tabela através de Página Inicial → Formatar como 
tabela. Com isso, novas ferramentas são desloqueadas ao usuário, como formatação padrão disponibilizada pelo Excel, 
o recurso de filtros, por exemplo, de forma automática, e a adição automática de novas colunas/linhas de dados ao 
corpo da tabela, permitindo que seus dados se mantenham constantemente atualizados. 
Obs.: A nomeclatura de algumas células variam ao utilizar o recurso de tabela. Dados correspondentes (mesma 
linha) recebem uma nomeclatura com o nome da tabela, seguido por um @ e o nome do campo (coluna) ao qual tal 
dado pertence, como quantidade disponivel de um produto, e seu valor unitário. No entanto, para valores de 
diferentes linhas, ou seja, dados que o excel não caracteriza como uma operação intuitiva, as referências tradicionais 
serão apresentadas para ressaltar ao usuário que tal operação pode estar usando um dado incorreto, proveniente de 
uma célula diferente da esperada. 
Obs.: O Excel utiliza o “@” como uma referência a a mesma linha cuja nova função está sendo escrita. 
Para o caso de uma soma de uma coluna inteira, podemos utilizar a propriedade desses dados estarem em 
uma tabela, para facilitar o cálculo. Assim, podemos fazer: =SOMA(nome_da_tabela[nome_da_coluna_desejada]). 
Uma das vantagens desse uso, é a atualização automática: assim que novos dados forem adicionados a tabela, a 
soma/média/contagem já é refeita, levando em consideração o dado recém inserido. 
Recomenda-se passar uma tabela (formatar o banco de dados para uma tabela) para funções, não o intervalo 
exato da tabela, e sim, as colunas desejadas, dessa forma, caso se acrescente novos itens (linhas) ao banco de dados, 
as funções de busca continuam funcionais. Assim, caso seja adicionada uma nova coluna (ou linha) em um banco de 
dado sob o qual existe uma tabela dinâmica, para fazer tal valor ser mostrado na tabela dinâmica, devemos apenas ir 
em ferramentas de tabela dinâmica → analisar → atualizar. 
É possivel classificar dados em ordem alfabética (texto), crescente (numérico), e do mais novo para o mais 
antigo (data) e vice-versa, através da guia Dados → Classificar. Para uma base de dados contendo uma coluna de 
estados e cidades é possivel realizar uma classificar em vários níveis, por exemplo classificar por ordem alfabética dos 
estados, e, mantendo essa classificação, classificar também as cidades. Para isso podemos criar uma classificação 
avançada/personalizada, e especificar a hierarquia entre as diversas classificações, bastando para isso ir em Página 
Inicial → Classificar e Filtrar. 
ERROS 
Ao alterar valores em sua planilha, é sempre recomendado rastrear dependentes e precedentes (guia 
Fórmulas), para evitar que sua alteração produza um erro, ou inconcistência. 
Erros comuns: 
#DIV/0! → Divisão por zero. Principais causas: modificação de fórmulas, resultando em uma divisão por uma célula 
que não existe mais, que foi movida, ou em branco. 
#NOME? → Erro na digitação do nome e/ou da sintaxe de fórmulas. 
#VALOR! → Normalmente relacionado a soma de células que não devem ser somadas, como por exemplo uma mistura 
de valores com texto. 
######## → Cerquilha. Falta de espaço na célula para visualização do dado. Para corrigir, basta adequar a largura da 
célula. Para ajustar a largura das células de sua planilha, mantendo-as com a mesma largura, seleciona (com auxílio 
do SHIFT e/ou CTRL) as colunas desejadas, e então ajusta manualmente. Tal ajuste é propagado sobre todas as colunasselecionadas. 
#REF! → Referência. Erro de referência a uma célula ou planilha indisponível, ou não existente. 
Recomenda-se que sempre ao terminar de trabalhar em uma planilha, o autor execute de forma automatizada 
uma busca por erros. Fórmulas → Verificação de Erros. 
IMPRESSÃO E VISUALIZAÇÃO 
Na guia Layout da Página, conseguimos acessar várias ferramentas para adequar a impressão da nossa planilha 
ao nosso desejo. Podemos, também, ter uma pré-visualização de como o Excel imprimirá nossa planliha utilizando o 
modo de visualização layout da página no canto inferior direito. Caso tenhamos uma planilha extensa, mas desejemos 
imprimi-la somente em uma página, podemos selecionar a opção Arquivo → Imprimir → Sem dimensionamento e 
substituir por ajustar planilha a uma página. 
Pode-se obter uma melhor visualização da impressão selecionando a opção de visualização de quebra de 
páginas, onde se tem uma visão geral de quantas páginas serão necessárias para imprimir o documento e seus limites. 
Nessa visualização, podemos arrastar esses limites para um ajuste manual de quais dados devem ser impressos onde, 
e fazer o ajuste de quantidade de páginas desejado. 
Por padrão, a grade auxiliar do Excel não é impressa. No entanto, para melhor visualização é possível 
selecionar o intervalo sob o qual se deseja a grade, e então selecionar na guia Exibir → Fonte →Bordas. Também em 
Arquivo → imprimir → configirar página, podemos optar por imprimir linhas de grade de toda a planilha. Caso possua 
uma coluna/linha que não desejamos imprimir, podemos selecioná-la, e ocultá-la. 
Deve-se congelar painéis permitindo uma melhor visualização em rolagens em grandes planilhas, indo na guia 
Exibir → Congelar Painéis. Caso já tenha selecionado alguma célula, o Excel automaticamente congela todas as linhas 
acima e todas as colunas á esquerda dessa célula. Caso seja criada uma tabela dinâmica com tais dados, e se deseje 
fazer o análogo a “Congelar painéis” ao imprimir, o usuário deve clicar com botão direito sobre a tabela dinâmica, 
Opções de tabela dinâmica → Impressão → Marcar “Definir Títulos de impressão” e “Repetir rótulos de linha em cada 
página impressa”. 
É possível compactar imagens (em um catálogo, por exemplo) para que o arquivo não se torne demasiado 
pesado, para isso, pode-se reduzir tamanho e qualidade das imagens presentes, em Formatar → Compactar imagens. 
É possível ocultar uma planilha auxiliar para que ela não atrapalhe na apresentação e simplicidade visual da 
sua pasta de trabalho, e no entanto, funcione como uma fonte de dados para diversas planilhas e fórmulas. Para isso, 
basta clicar com o botão direito sobre o nome da planilha em questão na pasta de trabalho, e selecionar a opção 
“ocultar”. Para trazer de volta á visualização, basta clicar com o botão direito novamente sobre o nome de alguma 
planilha, e selecionar “exibir”.