Introdução ao Excel 2

Microsoft Office Avançado

1 Funções Parte 2

Função SOMASE

A função SOMASE é uma junção de duas funções já estudadas aqui, a função SOMA e SE, onde buscaremos somar valores desde que atenda a uma condição especificada:

Sintaxe

SOMASE(intervalo analisado; critério; intervalo a ser somado).Onde:

Intervalo analisado (obrigatório): Intervalo em que a função vai analisar o critério.

Critério (obrigatório): Valor ou Texto a ser procurado no intervalo a ser analisado.

Intervalo a ser somado (opcional): Caso o critério seja atendido é efetuado a soma da referida célula analisada. Não pode conter texto neste intervalo.

No exemplo: Vamos calcular a somas das vendas dos vendedores por Gênero Observando a planilha acima, na célula C9 digitaremos a função =SOMASE(B2:B7;"M";C2:C7) para obter a soma dos vendedores. Faça o mesmo na célula c10 para as vendedoras, porém o critério será F.

Função CONT.SE

Esta função conta quantas células se atender ao critério solicitado. Ela pede apenas dois argumentos, o intervalo a ser analisado e o critério para ser verificado.

Sintaxe

CONT.SE(intervalo analisado; critério) .Onde:

Intervalo analisado (obrigatório): Intervalo em que a função vai analisar o critério.

Critério (obrigatório): Valor ou Texto a ser procurado no intervalo a ser analisado.

Aproveitando o mesmo exemplo da função anterior, podemos contar a quantidade de homens e mulheres.

Na planilha ao lado, na célula C9 digitaremos a função =CONT.SE(B2:B7;"M") para obter a quantidade de vendedores. Faça o mesmo na célula c10 para as vendedoras, porém o critério será F.

Função CONT.NÚM

Esta função é bem parecida com a anterior, porém conta apenas às células que contem apenas números. É solicitado apenas o intervalo como argumento.

Sintaxe

CONT.NÚM (intervalo analisado) Onde: Intervalo analisado (obrigatório): Intervalo em que a função vai contar se o conteúdo for número.

Função CONT.VALORES

Esta função conta todas as células que contem valores ou textos, com exceção das vazias. É solicitado apenas o intervalo como argumento.

Sintaxe

CONT.VALORES (intervalo analisado) Onde: Intervalo analisado (obrigatório): Intervalo em que a função vai contar se o conteúdo com exceção das células vazias.

Função CONTAR.VAZIO

Esta função conta todas as células vazias. É solicitado apenas o intervalo como argumento.

Sintaxe

CONTAR.VAZIO (intervalo analisado) Onde: Intervalo analisado (obrigatório): Intervalo em que a função vai contar apenas as células vazias.

Funções de Data e Hora

Antes de detalhar as funções de Data e Hora, precisamos entender melhor o formato data e hora no MS Excel. As datas são manipuladas pelo MS Excel como qualquer outro número, sendo que os dias são inteiros e as horas, frações decimais. Assim, são estabelecidos números de série, a partir do número 1, que representa a data 01/01/1900 às 12:00. Para exemplificar melhor basta digitarmos uma data e depois mudar o seu formato para número e veremos que número a data representa. A data 01/01/2015 é igual ao número 42.125, assim podemos somar ou subtrair datas como números comuns.

Formato de Data

Quando digitamos as datas em uma planilha do Excel, devemos atentar para a forma certa de fazê-lo, pois nem todos os formatos serão automaticamente convertidos. O correto é fazer a separação por meio de barras (29/08/2014) ou hifens (29-08-2014). Datas separadas por pontos (29.08.2014) não são reconhecidas pelo MS Excel. Na digitação, podem ser informados apenas os dois últimos dígitos dos anos (15/05/14), com um detalhe a observar: o MS Excel, por padrão, assumirá o número 20 à frente dos números de 00 a 29 (21/05/10, por exemplo, seria convertido para 21/05/2010). À frente dos números de 30 a 99 é acrescido o número 19 (25-12-99 passaria a ser 25/12/1999). Esse padrão pode ser alterado no painel de controle do sistema operacional.

Principais funções e combinações

=HOJE( ) Retorna a data atual. Não precisa de argumentos

=MÊS(HOJE()) Retorna o mês atual

=ANO(HOJE()) Retorna o ano atual

=HORA(AGORA()) Retorna à hora atual

=MINUTO(AGORA()) Retorna o minuto atual

=AGORA( ) Retorna a data e a hora. Não precisa de argumento

=DIAS360( data inicial; data final) Calcula o número de dias que há entre uma data inicial e uma data final.

=NÚMSEMANA(num serie). Retorna número da semana a qual pertence à data.

Além dessas temos muitas outras, basta consultar na Guia Fórmulas- Biblioteca de Funções-Data e Hora.

2 Funções

Conceito e Estrutura

Funções são fórmulas predefinidas que efetuam cálculos usando valores específicos, denominados argumentos, em uma determinada ordem ou estrutura. As funções podem ser usadas para executar cálculos simples ou complexos.

Assim como as fórmulas, as funções também possuem uma estrutura(sintaxe), conforme ilustrado abaixo:

Nome da função: Todas as funções que o Excel permite usar em suas células tem um nome exclusivo. Para obter uma lista das funções disponíveis, clique em uma célula e pressione SHIFT+F3.

Argumentos: Os argumentos podem ser números, texto, valores lógicos, como verdadeiro ou falso, matrizes, valores de erro como #N/D ou referências de célula. O argumento que você atribuir deve produzir um valor válido para esse argumento. Os argumentos também podem ser constantes, fórmulas ou outras funções.

Outro detalhe interessante numa função é a Dica de ferramenta Argumentos. Trata-se de uma dica de ferramenta com a sintaxe e argumentos que é automaticamente exibida à medida que você digita a função. Por exemplo, ao começar a digitar =SE( numa célula, você verá:

Note que o EXCEL mostra a dica com a sintaxe completa da função e os argumentos que podem ser inseridos dentro dela.

Grupo de funções

No MS Excel, as funções são organizadas por grupos de acordo com a área especifica como Banco de Dados, Data e Hora, Engenharia, Financeira, Informações, Lógica, Pesquisa e referência, Matemática e Trigonometria, Estatística, Texto e Dados.

A variedade de funções no Excel é realmente muito grande. Explicar todas as funções é praticamente impossível, pois a grande maioria delas é direcionada para tarefas específicas e exige conhecimentos técnicos e matemáticos em diversas áreas, como trigonometria, engenharia, estatística, etc.

Mesmo assim, isso não quer dizer que um usuário iniciante não possa aplicar funções práticas para auxiliá- los em algumas tarefas na planilha. A seguir, veremos como utilizar as funções mais comuns.

Função SOMA

Esta função soma todos os números que você especifica como argumentos. Cada argumento pode ser um intervalo, uma referência de célula, uma matriz, uma constante, uma fórmula ou o resultado de outra função. Por exemplo, SOMA(A1:A5) soma todos os números contidos nas células de A1 a A5. Outro exemplo: SOMA(A1;A3; A5) soma os números contidos nas células A1, A3 e A5.

Sintaxe

SOMA(núm1;núm2,...) Onde:

• número 1 (Obrigatório). O primeiro argumento numérico que você deseja somar.

• número 2,,...(Opcional). Argumentos de número de 2 a 255 que você deseja somar.

Função MÉDIA

Esta função calcula a média aritmética de uma determinada faixa de células contendo números. Para tal, efetua o cálculo somando os conteúdos dessas células e dividindo pela quantidade de células que foram somadas.

Sintaxe

MÉDIA(núm1;núm2, ...)

Onde: Número 1 (Obrigatório). O primeiro número, referência de célula ou intervalo para o qual você deseja a média. Número 2, ... (Opcionais). Números adicionais, referências de célula ou intervalos para os quais você deseja a média, até no máximo 255.

Função MÁXIMO e MÍNIMO

Essas funções dado um intervalo de células retorna o maior e menor número respectivamente.

Sintaxe

MÁXIMO(núm1; núm2;...) MÍNIMO(núm1; núm2;...)

Onde: Número 1 e Número 2,... Número 1 é obrigatório, números subsequentes são opcionais. De 1 a 255 números cujo valor máximo ou mínimo você deseja saber.

Função SE

A função SE é uma função do grupo de lógica, onde temos que tomar uma decisão baseada na lógica do problema. A função SE verifica uma condição que pode ser Verdadeira ou Falsa, diante de um teste lógico.

Sintaxe

SE(teste lógico; valor se verdadeiro; valor se falso).Onde:

Teste Lógico (Obrigatório). Qualquer valor ou expressão que possa ser avaliado como VERDADEIRO ou FALSO. Por exemplo, A10=100 é uma expressão lógica; se o valor da célula A10 for igual a 100, a expressão será considerada VERDADEIRO. Caso contrário, a expressão será considerada FALSO. Esse argumento pode usar qualquer operador de cálculo de comparação.

Valor se Verdadeiro (Opcional). O valor que você deseja que seja retornado se o argumento teste_lógico for considerado VERDADEIRO.

Valor se Falso (Opcional). O valor que você deseja que seja retornado se o argumento teste_lógico for considerado FALSO.

exemplo

Na Planilha ao lado, como saber se o número é negativo, temos que verificar se ele é menor que zero.

Na célula B2 digitaremos a seguinte formula:

Observação: Usamos aspas duplas se as respostas forem texto.

3 Funções Parte 3

Funções de Banco de Dados

O MS Excel inclui funções que analisam os dados armazenados em listas ou bancos de dados. Essas funções possuem três argumentos padrões: banco de dados, campo e critérios. Esses argumentos se referem aos intervalos de planilha usados pela função.

Uma base de dados é uma lista de dados relacionados em que as linhas de informação relacionada são registos e as colunas de dados são campos. A primeira linha da lista contém rótulos para cada coluna. A referência pode ser introduzida como um intervalo de células ou como um nome que representa o intervalo que contém a lista.

Vamos ver um exemplo de aplicação das funções de banco de dados: Um banco de dados de funcionários de determinada empresa.

Sintaxe:

Função BD (base de dados; campo; critérios)

• Banco de dados: o banco de dados é a tabela com os registros que desejamos analisar. A região em questão deve conter uma linha de cabeçalho, com o nome de cada coluna;

• Campo: o campo é uma célula com o nome de uma das colunas no banco de dados acima. Na tabela exemplo, os campos possíveis são Nome, Idade, Departamento, Salário e Telefone.

• Critérios: a tabela de critérios sempre tem duas linhas. A primeira linha contém os campos do banco de dados em uso, e a segunda, os valores de filtro associados a cada campo.

- Os nomes do campos precisam ser iguais ao Banco de Dados

Neste exemplo, desejamos obter a média salarial dos funcionários do departamento de vendas com pelo menos 25 anos. Isto é traduzido através das condições >=25 na coluna Idade, e do valor Vendas na coluna Departamento. Observe a montagem da tabela para a extração destes dados:

Na tabela acima, o banco de dados está na região C7:G17 (note que o cabeçalho é incluso na área), a tabela de critérios está em C23:G24 e o campo desejado é o valor de C27. Segue as funções resolvidas:

Média de Salários =BDMEDIA(C7:G17;C27; C23:G24)

Menor Salário = BDMIN(C7:G17;C27; C23:G24)

Maior Salário = BDMAX(C7:G17;C27; C23:G24)

As funções BDMÉDIA, BDMÁX e BDMÍN devolvem, de forma rápida e elegante, a média, o máximo e o mínimo dos salários de funcionários do departamento de vendas com pelo menos 25 anos. Além dessas 3 funções existem outras 9:

Comando Inserir Função

O comando Inserir Função exibe uma lista de funções e seus formatos e permite que você defina valores para os argumentos, ou seja, ao invés de digitar toda a sintaxe da função, você só precisaria escolher a função numa lista e depois informar seus argumentos.

Para ativar o recurso, vá à guia Fórmulas e clique no botão Inserir Função. Verá uma caixa de diálogo:

Procure por uma função: Neste campo você pode digitar uma descrição que você está tentando fazer, ou seja, o tipo de cálculo que está querendo executar. É útil quando você não sabe exatamente qual função tem que usar. Selecione uma categoria: Clique nesta lista para visualizar as categorias de função.

Auto Cálculo

Você pode usar o recurso de Auto Cálculo para visualizar rapidamente resultados para uma determinada faixa de células selecionada.

Por exemplo, ao selecionar um intervalo de células que contém números, você poderá observar na Barra de Status o resultado imediato da soma do conteúdo delas:

Além da soma, você pode obter outros resultados clicando com o botão direito do mouse sobre esse local da Barra de Status e escolher uma das opções disponíveis.

O Auto Cálculo apenas exibe o resultado. Ele não executa a função dentro de uma célula como quando usamos uma função ou fórmula.

Função Auto Soma

Utilizando o comando Auto Soma cujo botão fica na guia Fórmulas ou na guia Página Inicial, o MS Excel identifica a faixa de valores e insere automaticamente a fórmula, com o intervalo de células de referência.

Esse intervalo pode ser reconhecido facilmente por você porque ele fica destacado por uma borda preta pontilhada, em movimento.

4 Organização de dados

Validação de Dados

A validação de dados é um recurso do Excel que você pode usar para definir restrições em quais dados podem ou devem ser inseridos em uma célula. Você pode configurar a validação de dados para impedir que os usuários insiram dados inválidos. Se preferir, pode permitir que os usuários insiram dados inválidos, mas avisá- los quando tentarem digitar esse tipo de dados na célula. Também pode fornecer mensagens para definir a entrada esperada para a célula, além de instruções para ajudar os usuários a corrigir erros.

Por exemplo, em uma pasta de trabalho de marketing, é possível configurar uma célula para permitir apenas números de conta com exatamente três caracteres. Quando os usuários selecionarem a célula, você pode exibir uma mensagem como esta:

Se os usuários ignorarem a mensagem e digitarem dados inválidos na célula, como um número de dois ou cinco dígitos, você poderá mostrar uma mensagem de erro real.

As opções de validação de dados estão localizadas na guia Dados, no grupo Ferramentas de Dados.

A validação de dados é configurada na caixa de diálogo Validação de Dados.

Restringir a entrada de dados a valores em uma lista suspensa

1. Selecione uma ou mais células a serem validadas.

2. Na guia Dados, no grupo Ferramentas de Dados, clique em Validação de Dados.

3. Na caixa de diálogo Validação de Dados, clique na guia Configurações.

4. Na caixa Permitir, selecione Lista.

5. Clique na caixa Origem e, em seguida, digite os valores da lista separados pelo caractere separador de listas do Microsoft Windows (vírgulas por padrão).

6. Verifique se a caixa de seleção Menu suspenso na célula está marcada. Caso contrário, não será possível ver a seta suspensa ao lado da célula.

7. Para especificar como você deseja lidar com valores em branco (nulos), marque ou desmarque a caixa de seleção Ignorar em branco.

8. Opcionalmente, exiba uma mensagem de entrada quando a célula for clicada.

9. Especifique como o Excel deve responder quando forem inseridos dados inválidos.

10. Teste a validação de dados para assegurar-se de que ela esteja funcionando corretamente. Tente digitar dados válidos e inválidos nas células para ter certeza de que suas configurações estejam funcionando como você pretende e de que suas mensagens estejam aparecendo quando você espera.

Dica: No item 5 ao invés de digitamos os valores diretamente, podemos criar uma lista em outra planilha e nomeá-la utilizando a caixa de nome. Logo após basta inserir o sinal de igual e o nome da lista criada.

Restringir a entrada de dados a um número inteiro em limites

1. Selecione uma ou mais células a serem validadas.

2. Na guia Dados, no grupo Ferramentas de Dados, clique em Validação de Dados.

3. Na caixa de diálogo Validação de Dados, clique na guia Configurações.

4. Na caixa Permitir, selecione Número inteiro.

5. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para definir limites superior e inferior, selecione entre.

6. Insira o valor mínimo, máximo ou específico a ser permitido. Você também pode inserir uma fórmula que retorne um valor numérico.

7. Para especificar como você deseja lidar com valores em branco (nulos), marque ou desmarque a caixa de seleção Ignorar em branco.

8. Opcionalmente, exiba uma mensagem de entrada quando a célula for clicada.

9. Especifique como o Excel deve responder quando forem inseridos dados inválidos.

10. Teste a validação de dados para assegurar-se de que ela esteja funcionando corretamente.

11. Tente digitar dados válidos e inválidos nas células para ter certeza de que suas configurações estejam funcionando como você pretende e de que suas mensagens estejam aparecendo quando você espera.

5 Organização de dados Parte 2

Outras restrições

Assim como demonstramos essas duas restrições existem outros tipos de restrições similares com algumas diferenças, porém segue a mesma lógica. São elas:

• Restrição à entrada de dados a um número decimal em limites

• Restrição à entrada de dados a uma data em um período

• Restrição à entrada de dados a um horário em um período

• Restrição à entrada de dados a um texto com comprimento especificado

Guia Mensagem de Entrada

A mensagem exibe a informação especificada no momento da entrada de dados. Seu objetivo é dar uma orientação a quem está operando a planilha, informando-o sobre os critérios existentes.

Você poderá ver a Mensagem de Alerta sendo exibida a qualquer momento que posicionar seleção sobre qualquer uma das células onde foram aplicados os critérios.

Guia Alerta de Erro

Um aviso personalizado será exibido quando alguém digitar dados que não atendam aos critérios que você estabeleceu. Existem três estilos de alertas:

• Parar: Onde não deixa o usuário prosseguir.

• Aviso: Onde permite o usuário de parar ou continuar a digitação.

• Informações: Apenas avisa ao usuário sobre a digitação.

Podemos personalizar o Título da Mensagem e a Mensagem de Erro.

O alerta de erro, por sua vez, será mostrado quando alguém tentar inserir um dado que não se adéqua a condição.

Classificar

O comando Classificar organiza as informações nas linhas selecionadas ou lista as informações em ordem alfabética, por número ou por data.

Para classificar os dados, o MS Excel segue alguns critérios de prioridade e organização. Numa Classificação do tipo Crescente, por exemplo, a ordem é a seguinte:

• Números: Os números são classificados do menor número negativo, ao maior número positivo.

• Classificação alfanumérica: Quando você classifica texto alfanumérico, o Excel classifica da esquerda para a direita, caractere por caractere. Por exemplo, se uma célula contém o texto B100, o Excel coloca a célula depois de uma célula que contenha a entrada B1, e antes de uma célula que contenha a entrada B11.

• Apóstrofos (') e hifens (-): São ignorados, com uma exceção - se duas sequências de caracteres de texto forem iguais exceto pelo hífen, o texto com hífen é classificado por último.

• Valores lógicos: Em valores lógicos, “FALSO” é colocado antes de “VERDADEIRO”.

• Vazias: As células em branco são sempre classificadas por último.

Em uma Classificação do tipo Decrescente esta ordem de classificação é invertida, exceto para as células em branco, que serão sempre colocadas por último.

Classificação Rápida

Para obter uma classificação rápida selecione as células que deseja ordenar e clique num dos botões de classificação - Crescente ou Decrescente - que ficam na guia Dados, ou acessível através do botão Classificar e Filtrar na guia Página Inicial.

Quando efetuar esse processo em planilhas cujas células selecionadas contêm dados adjacentes, pode acontecer do MS Excel emitir uma mensagem de alerta.

O MS Excel "suspeita" que as células adjacentes complementam os dados das células selecionadas. Isso pode ser verdade, ou não, as células serem totalmente independentes.

No caso da planilha que está sendo classificados realmente os dados próximos à seleção devem "acompanhar" as informações que você tentou ordenar.

Portanto, quando isso acontecer, fique atento a cada situação e decida se deve Expandir a Seleção, ou apenas classificar os dados selecionados optando por Continuar com a seleção atual.

Classificação Personalizada

A classificação rápida permite classificar apenas uma coluna por vez, mas em alguns casos, precisaremos usar várias colunas onde será preciso estabelecer uma classificação baseada em níveis de prioridade, de acordo com cada coluna.

Por exemplo, você pode classificar os dados numa planilha começando primeiro pelos nomes dos funcionários, em seguida, pelos setores que eles trabalham, e por último, pelo seu código de identificação, definindo assim, níveis de prioridade para cada coluna de classificação.

• Para obter uma classificação deste tipo, você deve selecionar os dados que deseja ordenar e escolher o comando Classificar na guia Dados ou o comando Personalizar classificação na guia página inicial. Será aberta uma caixa de diálogo:

6 Organização de dados Parte 3

Você pode classificar utilizando colunas de prioridade. Para cada uma você pode estabelecer se a ordem será Crescente ou Decrescente. Na parte superior da caixa, há uma opção para que você informe ao Excel se, na seleção que foi feita, existe ou não uma Linha de Cabeçalho.

• Marque a opção Meus dados contêm cabeçalhos para excluir a primeira linha da classificação, se a lista tiver rótulos de coluna na primeira linha (por exemplo, se na seleção você tiver destacado também os títulos de cada coluna NOME, CÓDIGO e SETOR). Isso vai evitar que as linhas de cabeçalho também sejam classificadas e, assim, venham a ser retiradas de seu local original, perdendo a função de identificação.

• Desmarque a opção Meus dados contêm cabeçalhos para incluir a primeira linha na classificação se a lista não tiver rótulos de coluna na primeira linha.

Auto Filtro

O Auto Filtro é um recurso muito útil em planilhas extensas. Com ele, além de classificar poderemos fazer com que somente dados que obedeçam a determinados critérios sejam exibidos, ou seja, uma ferramenta para limitar a exibição de dados em sua planilha.

A tabela deve possuir rótulos (cabeçalho na primeira linha) para garantir o filtro de informações conforme a figura abaixo:

Selecionamos os rótulos e clicamos no comando filtro que está localizado na guia página inicial -grupo edição -Filtro. Aparecer o botão filtrar em cada rótulo da coluna.

Vamos agora mostrar um exemplo e ver como funciona o Auto Filtro.

Classificar Crescente ou Decrescente

Você pode facilmente classificar crescente ou decrescente. O filtro agora ajuda da seguinte maneira: Se os seus dados forem números, ele mostrará: Do menor para o maior ou Do maior para o menor, se for texto: Classificar de A - Z ou Classificar de Z - A. Intuitivo não? No exemplo, clicando em Situação, veremos o seguinte filtro:

Classificar por Cor

Permite-nos escolher qual a ordem que devemos colocar para os dados, a partir de suas cores de célula ou do texto.

Filtros por Cor

Já os filtros por cores são para exibir apenas aqueles itens com a cor que você escolher. Ele ocultará os valores diferentes do item que você escolheu:

No exemplo foram filtrados todos os produtos que estão com situação Normal. Para isso foi usado filtrar por cor, e escolhido Amarelo. Observe que, como foi usado filtro, as linhas referentes aos outros dados ficaram ocultas, e os cabeçalhos das linhas ficaram em azul, mostrando que há dados filtrados na planilha. Outra forma de visualizar isso é observando que na coluna Situação aparece um funil no lugar da setinha do filtro.

Filtros de Texto

Os filtros de texto dão a flexibilidade de usar critérios para determinar o seu filtro. Você pode exibir todos que começam com A, todos que contém Vendido, etc. Ele é bom para aqueles casos onde você tem os dados digitados uma parte no masculino e outra parte no feminino, como em Estagiário/Estagiária. Neste caso bastaria escolher Contém… e digitar Estagiá e pressionar OK e pronto, ele filtraria tanto os meninos quanto as meninas.

Filtros com caixas de seleção

Permitem selecionar mais de dois itens em uma coluna. O máximo de itens que era possível filtrarem era dois, e isso quando você usava o filtro personalizado e sabia trabalhar com os critérios. Agora, basta você marcar e desmarcar as caixas de seleção e pronto. Uma dica legal é, se você tem muitas caixas para selecionar e precisa de poucas marcações, basta clicar em (Selecionar Tudo) e pronto, o Excel desmarcará automaticamente todas as caixas para vocês. Então, você marca as caixas que deseja ver o resultado e pressiona OK para aplicar o filtro.

Remover Filtro

Para remover um filtro de uma coluna é simples. Basta ir à coluna onde você tem dados filtrados e clicar em “Limpar Filtro de… “. Todos os dados aparecerão normalmente.

Subtotais

O MS Excel pode calcular automaticamente valores de subtotais e totais gerais em uma lista. Quando você insere subtotais automáticos, o Excel organiza a lista em tópicos para que você possa exibir e ocultar as linhas de detalhes de cada subtotal.

Para inserir subtotais, você primeiro classifica a lista para agrupar as linhas das quais deseja calcular o subtotal. Em seguida, você pode calcular subtotais de qualquer coluna que contenha números.

Como são calculados os Subtotais?

Subtotais: O Excel calcula valores de subtotal com uma função de resumo, como Soma ou Média. Você pode exibir subtotais em uma lista com mais de um tipo de cálculo de cada vez.

Totais gerais: Os valores de totais gerais são derivados de dados de detalhe, não dos valores das linhas de subtotal. Por exemplo, se você usa a função de resumo Média, a linha de total geral exibirá uma média de todas as linhas de detalhes da lista, não uma média dos valores das linhas de subtotal.

Recálculo automático: O Excel recalcula os valores de subtotal e total geral automaticamente à medida que você edita os dados de detalhe.

Antes de qualquer coisa, verifique se os dados a serem subtotalizados estão no formato a seguir: cada coluna tem um rótulo na primeira linha e contém dados semelhantes e não existem linhas ou colunas em branco dentro do intervalo.

• Clique em uma célula na coluna a subtotalizar. Esta coluna deverá estar com seus dados devidamente classificados e ordenados de forma Crescente ou Decrescente. Na planilha a seguir, por exemplo, o objetivo é criar os Subtotais vendidos para cada Fabricante, e, portando, essa coluna deve estar devidamente classificada.

• Na guia Dados, clique em Subtotal .

• Na caixa A cada alteração em, clique na próxima coluna a ser subtotalizada.

• Na caixa Usar função, clique na função que você deseja usar para calcular os subtotais.

• Na caixa Adicionar subtotal a, marque a caixa de seleção de cada coluna que contenha valores a serem subtotalizados.

• Se você desejar uma quebra de página automática após cada subtotal, marque a caixa de seleção Quebra de página entre grupos. Se desejar que os subtotais apareçam acima, e não abaixo, das linhas subtotalizadas, desmarque a caixa de seleção Resumir abaixo dos dados.

Quando fizer a confirmação, verifique o resultado na planilha.

Ainda podemos observar no exemplo que foram criadas estruturas de tópicos, na qual podemos suprimir os subgrupos.

7 Organização de dados Parte 4

Remover subtotais de uma lista

• Clique em uma célula na lista que contém subtotais.

• Na guia Dados, clique em Subtotal .

• Na caixa de diálogo, clique no botão Remover todos.

Quando você remove subtotais de uma lista, o MS Excel também remove a estrutura de tópicos e todas as quebras de página que foram aplicadas na lista quando você os inseriu. Você pode confirmar isso observando a planilha:

8 Gráficos

Conceito

Números e estatísticas nem sempre são fáceis de serem interpretados, principalmente quando queremos apresenta-los. Uma estratégia muito utilizada para organizar visualmente informações numéricas e valores estatísticos é através da criação de Gráficos. Gráficos têm apelo visual e facilitam, para os usuários, a visualização de comparações, padrões e tendências nos dados. Por exemplo, em vez de ter de analisar várias colunas de números de planilha, você pode ver de relance se as vendas estão caindo ou subindo a cada trimestre, ou como as vendas reais se comparam às vendas estimadas. Você pode criar um gráfico como planilha individual, ou como um objeto incorporado a uma planilha.

Tipos de Gráficos

O MS Excel oferece suporte para vários tipos de gráficos com a finalidade de ajudá-lo a exibir dados de maneira que sejam significativas para o seu propósito. Ao criar um gráfico ou modificar um gráfico existente, você pode escolher entre uma grande variedade de tipos de modelos (como colunas ou de pizza) e seus subtipos (como colunas empilhadas ou de pizza em 3D).

Estrutura de um Gráfico

No MS Excel, um gráfico é vinculado aos dados da planilha em que foi criado e é atualizado automaticamente quando você altera os dados da planilha. Um gráfico possui vários elementos. Alguns deles são exibidos por padrão, enquanto outros podem ser adicionados conforme necessário. É possível alterar a exibição dos elementos do gráfico movendo-os para outros locais no gráfico, redimensionando-os ou alterando seu formato. Também é possível remover os elementos que você não deseja exibir.

1. A área do gráfico.

2. A área de plotagem do gráfico.

3. Os pontos de dados da série de dados que são plotados no gráfico.

4. O eixo horizontal (categoria) e o eixo vertical (valor) ao longo dos quais os dados são plotados no gráfico.

5. A legenda do gráfico.

6. Um título de gráfico e eixo que você pode utilizar no gráfico.

7. Um rótulo de dados que você pode usar para identificar os detalhes de um ponto de dados em uma série de dados.

Gráficos incorporados

Um gráfico incorporado é considerado um objeto gráfico e é salvo como parte da planilha em que foi criado. Use gráficos incorporados quando quiser exibir ou imprimir um ou mais gráficos com seus dados da planilha.

Planilha de gráfico

Uma planilha de gráfico é uma planilha separada dentro de uma pasta de trabalho que tem um nome próprio. Use uma planilha de gráfico quando quiser exibir ou editar gráficos extensos ou complexos separados dos dados da planilha ou quando desejar preservar espaço na tela enquanto trabalha na planilha.

Criar Gráfico

No MS Excel, você pode criar um gráfico em apenas uma etapa. O MS Excel usa um tipo de gráfico e formatação padrão, que poderão ser alterados no futuro.

• Para criar uma planilha de gráfico que usa o tipo de gráfico padrão, primeiro selecione os dados que irão originá-lo.

• Em seguida, pressione F11. O gráfico será automaticamente incluído em uma nova planilha, da pasta de trabalho ativa.

Para criar rapidamente um gráfico incorporado, ou seja, inserido junto com a planilha que contém os dados de origem, faça o seguinte:

O gráfico será inserido junto com sua planilha de origem.

Os gráficos inseridos por este método rápido podem ser posteriormente, personalizados.

Formatar Layout e Estilo de um Gráfico

Depois de criar um gráfico, é possível alterar instantaneamente a sua aparência. Em vez de adicionar ou alterar manualmente os elementos ou a formatação do gráfico, é possível aplicar rapidamente um layout e um estilo predefinidos ao gráfico. O Excel fornece uma variedade de layouts e estilos úteis e predefinidos (ou layouts e estilos rápidos) que você pode selecionar.

Para aplicar um layout de gráfico predefinido, faça o seguinte:

• Clique em qualquer local do gráfico que você deseja formatar usando um layout de gráfico predefinido. Assim, será exibido as Ferramentas de Gráfico, adicionando as guias Design, Layout e Formatar.

• Na guia Design, no grupo Layouts de Gráfico, clique no layout de gráfico que deseja usar.

Para aplicar um estilo de gráfico predefinido, faça o seguinte:

• Clique em qualquer local do gráfico que você deseja formatar usando um estilo de gráfico predefinido. Isso exibirá as guias Design, Layout e Formatar.

• Na guia Design, no grupo Estilos de Gráfico, clique no estilo de gráfico a ser usado.

Para formatar manualmente o layout dos elementos do gráfico, clique no elemento de gráfico cujo layout você deseja alterar, ou siga os seguintes passos:

• Clique em qualquer local do gráfico para exibir as Ferramentas de Gráfico.

• Na guia Formatar, no grupo Seleção Atual clique na seta na caixa Elementos de Gráfico e selecione o elemento de gráfico desejado.

• Na guia Layout, no grupo Rótulos, Eixos ou Plano de Fundo, clique no botão do elemento de gráfico que corresponde ao elemento do gráfico que você selecionou e clique na opção de layout desejada.

9 Gráficos Parte 2

As opções de layout selecionadas são aplicadas ao elemento de gráfico selecionado. Por exemplo, se o gráfico inteiro estiver selecionado, rótulos de dados serão aplicados a todas as séries de dados. Se houver um único ponto selecionado, os rótulos de dados serão aplicados somente às séries de dados selecionadas ou ao ponto de dados. S

Para alterar manualmente o formato dos elementos do gráfico, clique no elemento de gráfico cujo estilo você deseja alterar, ou siga os seguintes passos:

• Clique em qualquer local do gráfico para exibir as Ferramentas de Gráfico.

• Na guia Formatar, no grupo Seleção Atual clique na seta na caixa Elementos de Gráfico e selecione o elemento de gráfico desejado.

Na guia Formato, para formatar qualquer elemento do gráfico selecionado, no grupo Seleção Atual, clique em Seleção de Formato e, em seguida, selecione as opções de formato que deseja.

• Para formatar a forma de um elemento do gráfico selecionado, no grupo Estilos de Forma, clique no estilo que deseja ou clique em Preenchimento de Forma, Contorno da Forma ou Efeitos de Forma e, em seguida, selecione as opções de formato que deseja.

• Para formatar o texto de um elemento do gráfico selecionado utilizando o Wordart, no grupo Estilos de Wordart, clique em um estilo. Também é possível clicar em Preenchimento do Texto, Contorno do Texto ou Efeitos de Texto e selecionar as opções de formato que desejar.

Adicionar ou Remover Títulos ou Rótulos de Dados

Para facilitar o entendimento de um gráfico, é possível adicionar títulos, como um título de gráfico e títulos de eixo. Os títulos de eixo estão geralmente disponíveis para todos os eixos que podem ser exibidos em um gráfico, incluindo eixos de profundidade (série) em gráficos 3D. Alguns tipos de gráfico (como os gráficos de radar) possuem eixos, mas não podem exibir títulos de eixos. Os tipos de gráfico que não possuem eixos (como gráficos de pizza e de roscas) também não exibem títulos de eixo.

Também é possível vincular o título do gráfico e os títulos de eixos ao texto correspondente nas células da planilha criando uma referência a essas células. Os títulos vinculados são automaticamente atualizados no gráfico quando você altera o texto correspondente na planilha.

Para identificar rapidamente uma série de dados em um gráfico, adicione rótulos de dados aos pontos de dados desse gráfico. Por padrão, os rótulos de dados são vinculados a valores na planilha e são atualizados automaticamente quando são feitas alterações nesses valores.

Adicionar um título de gráfico

Clique em qualquer lugar do gráfico em que você deseja adicionar um título, e siga os seguintes passos:

• Na guia Layout, no grupo Rótulos, clique em Título do Gráfico.

• Clique em Título de Sobreposição Centralizado ou Acima do Gráfico.

Adicionar títulos de eixo

Clique em qualquer lugar do gráfico em que você deseja adicionar títulos de eixo, e siga os seguintes passos:

• Na guia Layout, no grupo Rótulos, clique em Títulos dos Eixos.

• Para adicionar um título a um eixo horizontal (categoria) principal, clique em Título do Eixo Horizontal Principal e selecione a opção desejada. Se o gráfico tiver um eixo horizontal secundário, você também poderá clicar em Título do Eixo Horizontal Secundário.

• Para adicionar um título ao eixo vertical principal (valor), clique em Título do Eixo Vertical Principal e selecione a opção desejada. Se o gráfico tiver um eixo vertical secundário, você também poderá clicar em Título do Eixo Vertical Secundário.

• Para adicionar um título a um eixo de profundidade (série), clique em Título do Eixo de Profundidade e selecione a opção desejada.

• Na caixa de texto Título do Eixo exibida no gráfico, digite o texto desejado.

• Para formatar o texto, selecione-o e clique nas opções de formatação desejadas na Minibarra de ferramentas.

Adicionar rótulos de dados

Clique na área do gráfico, e siga os seguintes passos:

• Para adicionar um rótulo de dados a todos os pontos de uma série de dados, clique em qualquer local da série de dados que você deseja rotular.

• Para adicionar um rótulo de dados a um único ponto em uma série de dados, clique na série de dados que contém o ponto que você deseja rotular e clique nesse ponto.

• Na guia Layout, no grupo Rótulos, clique em Rótulos de Dados e, em seguida, clique na opção de exibição desejada.

Remover títulos ou rótulos de dados de um gráfico

Clique na área do gráfico, e siga os seguintes passos:

• Na guia Layout, no grupo Rótulos, para remover um título de gráfico, clique em Título de Gráfico e em Nenhum.

• Na guia Layout, no grupo Rótulos, para remover um eixo, clique em Título do Eixo, clique no tipo de eixo que você deseja remover e clique em Nenhum.

• Na guia Layout, no grupo Rótulos, para remover rótulos de dados, clique em Rótulos de Dados e em Nenhum.

Ou, para remover rapidamente um título ou rótulo de gráfico, clique nele e pressione DELETE.

10 Gráficos Parte 3

Mover Gráfico

Um gráfico que é inserido junto de uma planilha, ele fica localizado à frente da mesma, ou seja, ele é um gráfico incorporado como já estudado.

• Para mover o gráfico para outro ponto da planilha, clique em sua área (na parte em branco), segure o botão do mouse e arraste para outro local. Uma borda tracejada acompanha o trajeto.

Também podemos mover o gráfico incorporado para outra planilha, ou ainda modificar para uma planilha de gráfico. Basta selecionar gráfico ir na guia design e clicar no botão mover, onde teremos as duas opões mencionadas.

Redimensionar Gráfico

Caso você precise aumentar ou reduzir o tamanho do gráfico, use as alças de redimensionamento localizadas ao redor dele. Essas alças ficam visíveis quando o gráfico está selecionado. A seleção de um gráfico é feita com um clique na Área do Gráfico. Outra maneira é, na guia Formato, no grupo Tamanho, digite o tamanho nas caixas Altura da Forma e Largura da Forma.

Remover um Gráfico

Para remover um gráfico que está junto da planilha de origem, deixe sua área selecionada e tecle DELETE. Ele será excluído.

Para remover um gráfico que está numa planilha separada, devemos excluir a planilha inteira onde ele está. Para isso, clique com o botão direito na alça de identificação da planilha na parte inferior e escolha Excluir.

Salvar um Gráfico como Modelo

Para criar outro gráfico como o recém-criado, salve o gráfico como um modelo que pode ser usado como base para outros gráficos a seu gosto.

• Clique no gráfico que deseja salvar como um modelo.

• Na guia Design, no grupo Tipo, clique em Salvar como Modelo.

• Na caixa Nome do arquivo, digite um nome para o modelo.

A menos que uma pasta diferente seja especificada, o arquivo modelo (.crtx) será salvo na pasta Gráficos e o modelo ficará disponível em Modelos na caixa de diálogo .

Inserir Gráfico (na guia Inserir, grupo Gráficos, Iniciador de Caixa de Diálogo ) e na caixa de diálogo Alterar Tipo de Gráfico (guia Design, grupo Tipo, Alterar Tipo de Gráfico).

Um modelo de gráfico contém a formatação do gráfico e armazena as cores que estão em uso quando o gráfico é salvo como modelo. Quando você usa um modelo para criar um gráfico em outra pasta de trabalho, o gráfico novo usa as cores do modelo, não as cores do tema de documento atualmente aplicadas à pasta de trabalho. Para usar as cores do tema de documento em vez das cores do modelo de gráfico, clique com o botão direito na área do gráfico e, em seguida, clique em Redefinir para Coincidir Estilo.

11 Recursos adicionais

Comentários

Um comentário é uma anotação que você anexa a uma célula, separado do restante do conteúdo da célula. Os comentários são úteis como lembretes para você mesmo, como anotar como funciona uma fórmula, ou para fornecer observações a outros usuários.

Os comentários são identificados pelo nome do usuário para que você possa saber quem inseriu cada um deles em uma pasta de trabalho compartilhada para diversos usuários.

Para adicionar um comentário numa Célula:

• Clique na célula sobre a qual você deseja comentar.

• Na guia Revisão, clique em Novo Comentário.

• Na caixa, digite o texto do comentário.

• Se você não desejar que seja mostrado o nome do usuário no comentário, selecione e exclua este nome.

• Ao terminar de digitar o texto, clique fora da caixa de comentário.

Outra forma de inserir um comentário é clicar na célula com o botão de opção do mouse e no menu interativo clicar na opção inserir comentário.

Exibindo os comentários

As células com comentários têm triângulos vermelhos indicadores no canto superior direito. Se você posicionar o ponteiro sobre uma célula com um indicador, seu comentário será exibido.

Editando um Comentário

Para editar o texto de um comentário, posicione a seleção na célula que o contém e escolha na guia Revisão, o botão Editar Comentário. O comentário entra em modo de edição.

Outra forma de editar um comentário é clicar na célula com o botão de opção do mouse e no menu interativo clicar na opção editar comentário.

Remover Comentário

Para remover o comentário de uma célula inteiramente (não confunda com apagar o texto da caixa de comentário), posicione-se na célula onde ele está. Então, escolha na guia Revisão, o botão Excluir.

Outra forma de remover um comentário é clicar na célula com o botão de opção do mouse e no menu interativo clicar na opção excluir comentário.

Verificação Ortográfica

No MS Word podemos ver os sublinhados de alerta indicando as palavras com ortografia errada, porém não vemos com a mesma facilidade no MS Excel, mesmo assim é possível realizar a Verificação Ortográfica:

• Para verificar a ortografia de sua planilha, clique na tecla F7, ou;

• Clique no botão Verificar Ortografia , na guia Revisão.

Caso o MS Excel encontre possíveis falhas ortográficas, você verá a caixa de diálogo, semelhante ao MS Word. Nela você encontra opções para Adicionar, Ignorar e corrigir o erro.

Auto Conclusão

A Auto Conclusão oferece sugestões quando você está digitando dados em uma célula, caso esses termos tenham pelo menos o início parecido com outros termos que o EXCEL identifica em células adjacentes. Você percebe isso quando começa a digitar:

Você não precisa aceitar essa sugestão obrigatoriamente. Se ela realmente corresponder ao que está querendo digitar, tecle ENTER para aceitar. Caso o contrário, continue a digitar o que queria e a sugestão será desativada.

Auditoria de Fórmulas

Auditoria consiste em um exame cuidadoso, sistemático e independente, cujo objetivo seja averiguar se as atividades desenvolvidas estão em conformidade com as regras.

A Auditoria de Fórmulas do MS Excel ajuda a rastrear problemas em suas planilhas. Por exemplo, o valor que você vê em uma célula pode ser o resultado de uma fórmula, ou pode ser usado por uma fórmula que produz um resultado incorreto. Os comandos de auditoria exibem graficamente, ou rastreiam, as relações entre células e fórmulas usando setas rastreadoras. Estas setas apontam o relacionamento entre a célula selecionada e as células relacionadas a ela.

Rastrear Precedentes

Este comando desenha setas rastreadoras nas células que fornecem valores diretamente para a fórmula na célula ativa. Ou seja, se a célula selecionada contém uma fórmula, você pode localizar todas as células que fornecem dados para ela, conhecidos como precedentes.

• Para rastrear precedentes, deixe a seleção na célula que contém o resultado da fórmula.

• Na guia Fórmulas, no grupo Auditoria de Fórmulas e clique em Rastrear Precedentes. Você verá a planilha com a aparência a seguir:

Para rastrear as células que fornecem valores indiretamente para a fórmula na célula ativa, clique novamente em Rastrear Precedentes.

Rastrear Dependentes

Este comando desenha uma seta rastreadora para a célula ativa a partir das fórmulas que dependem do valor na célula ativa.

• Para rastrear dependentes, posicione a seleção na célula de partida e escolha Rastrear Dependentes no grupo Auditoria de Fórmulas da guia Fórmulas.

Pra adicionar outros níveis de dependentes indiretos, clique de novo em Rastrear Dependentes.

12 Recursos adicionais Parte 2

Rastrear Erros

Quando a célula ativa tem um valor de erro como #VALOR ou #DIV/0, este comando desenha setas rastreadoras para a célula ativa a partir das células que causam o valor de erro.

• Para usar esta opção, posicione-se na célula que contém o valor de erro.

• Clique em Verificação de Erros, no grupo Auditoria de Fórmulas da guia Fórmulas e escolha Rastrear erro.

Para remover as setas rastreadoras existentes em uma planilha, selecione Remover Setas no grupo Auditoria de Fórmulas da guia Fórmulas.

Comparar Pastas Lado a Lado

Com o MS Excel você pode comparar pastas de trabalho colocando-as uma ao lado da outra.

• Abra as pastas de trabalho que deseja Exibir lado a lado.

• Na guia Exibição, grupo janela, clique em Exibir Lado a Lado.

Automaticamente ao utilizar a barra de rolagem, as duas planilhas rolam ao mesmo tempo, para desativar basta desativar o comando Rolagem Sincronizada.

Para redefinir as janelas da planilha para as posições em que se encontravam antes da comparação, clique em Redefinir Posição da Janela.

Para cancelar a exibição Lado a Lado, clicar no comando Exibir Lado a Lado novamente.

13 Impressão de planilhas

Visualizando Planilhas

A visualização da impressão é um comando que dá uma noção real de como os dados da planilha ficarão dispostos na página, de acordo com as configurações do MS Excel.

• Clique na planilha ou selecione as planilhas que você deseja visualizar.

• Clique em Arquivo e depois clique em Imprimir ou pressione CTRL+P. A janela de visualização será exibida em preto e branco, quer a(s) planilha(s) inclua(am) cores ou não, a menos que a configuração esteja definida para impressão em uma impressora colorida.

• Para visualizar as páginas anteriores e seguintes, na parte inferior da janela Visualizar Impressão, clique em Próxima Página e Página Anterior. Próxima Página e Página Anterior estão disponíveis apenas quando você seleciona mais de uma planilha ou quando uma planilha contém mais de uma página de dados. Para exibir várias planilhas, em Configurações, clique em Imprimir Toda a Pasta de Trabalho.

Configurar Página

Este comando pode ser ativado clicando em Arquivo e depois clique em Imprimir ou pressione CTRL+P. Com a tela de Visualização de Impressão ativa faça:

• Para alterar a impressora, clique na caixa suspensa em Impressora e selecione a impressora desejada.

• Para fazer alterações na configuração da página, incluindo orientação, tamanho do papel e margens, selecione as opções desejadas em Configurações.

• Para dimensionar a planilha inteira a fim de ajustá-la a uma única página impressa, em Configurações, clique na opção desejada na caixa suspensa de opções de escala.

Em Configurações, é possível clicar na opção Configurar Página. Isso fará surgir a caixa de diálogo Configurar Página distribui suas opções em 4 guias. A seguir, a explicação sobre cada uma delas.

Guia Página

Orientação: Nesta seção, você especifica a orientação da página impressa. Você pode optar por Retrato (papel em pé), ou Paisagem (papel deitado). Só em mudar essa orientação, pode ocorrer do número de páginas que seriam geradas para imprimir a planilha, seja modificado.

Dimensionar: Marque a caixa de seleção Ajustar para e insira um número percentual na caixa % do tamanho normal. Você pode reduzir a planilha a 10% do tamanho normal ou aumentá-la a 400% do tamanho normal. Para reduzir a planilha ou a seleção quando você imprime para que ela caiba no número de páginas especificado, marque a outra caixa de seleção Ajustar para, insira um número na caixa página(s) de largura por e digite um número na caixa de altura. Para preencher a largura do papel e usar quantas páginas forem necessárias, digite 1 na caixa página(s) de largura por e deixe a caixa de altura em branco.

Tamanho do papel: Clique em Carta, Ofício ou outra opção de tamanho para indicar o tamanho no qual deseja imprimir seu documento ou envelope.

Qualidade da impressão: Clique na resolução desejada para especificar a qualidade de impressão da planilha ativa. A resolução é o número de pontos por polegada (dpi) que aparecem na página impressa. Quanto maior a resolução melhor a qualidade da impressão em impressoras que fornecem suporte à impressão de alta resolução.

Número da primeira página: Insira Automático para iniciar a numeração de páginas em 1 (se for a primeira página do trabalho de impressão) ou no número sequencial seguinte (se não for a primeira página do trabalho de impressão). Insira um número para especificar um número de página inicial que não seja “1.

Guia Margens

Margens: Insira configurações de margem e veja os resultados na caixa de visualização. Ajuste as medidas Superior, Inferior, Esquerda e Direita para especificar a distância entre os dados e a borda da página impressa.

Centralizar na página: Centraliza os dados na página entre as margens, marcando a caixa de seleção Vertical, a caixa de seleção Horizontal ou ambas.

Guia Cabeçalho e Rodapé

Cabeçalho: Clique em um cabeçalho interno nesta caixa ou clique no botão Personalizar cabeçalho para criar um cabeçalho personalizado para a planilha. O cabeçalho interno é copiado para a caixa de diálogo Cabeçalho, onde você poderá formatar ou editar o cabeçalho selecionado.

Ao personalizar, você vê a tela representada a seguir, onde pode especificar em qual das três seções o Cabeçalho vai ficar, ou até mesmo inserir conteúdos em todas elas.

Rodapé: Clique em um rodapé interno na caixa Rodapé e clique em Personalizar Rodapé para criar um rodapé personalizado para a planilha. Da mesma forma do cabeçalho, o rodapé interno é copiado para a caixa de diálogo Rodapé, onde você poderá formatar ou editar o rodapé selecionado.

Guia Planilha

Área de Impressão: Clique na caixa Área de impressão para selecionar o intervalo da planilha que será impresso e arraste pelas áreas da planilha que você deseja imprimir.

O botão Recolher caixa de diálogo , no canto direito dessa caixa move temporariamente a caixa de diálogo para que você insira o intervalo selecionando as células na planilha. Quando você terminar, poderá clicar novamente no botão para exibir a caixa de diálogo inteira.

Imprimir títulos: Nesta seção, selecione uma opção para imprimir as mesmas colunas ou linhas como títulos em cada página de uma planilha impressa. Selecione Linhas a repetir na parte superior se desejar linhas específicas como títulos horizontais em cada página. Selecione Colunas a repetir à esquerda se desejar títulos verticais em cada página. Em seguida, na planilha, selecione uma célula ou células nas colunas ou linhas de título desejadas. Você pode usar o botão Recolher caixa de diálogo no canto direito para mover temporariamente a caixa de diálogo para que você insira o intervalo selecionando as células na planilha.

Imprimir: Especifica que parte da planilha será impressa, se a impressão será colorida ou em preto e branco e define a qualidade de impressão. Marque a caixa Linhas de grade para imprimir linhas de grade de célula horizontais e verticais nas planilhas. Selecione a caixa de seleção Preto e branco se tiver formatado dados com cores, mas estiver utilizando uma impressora em preto e branco. Marque a caixa de seleção Qualidade rascunho para reduzir o tempo de impressão. Quando esta opção é selecionada, o MS Excel não imprime nem as linhas de grade nem a maior parte dos elementos gráficos. Clique em Títulos de linha e coluna para imprimir números de linha e letras de coluna em um estilo de referência A1.

Ordem da Página: Clique em Abaixo e acima ou Acima e abaixo para controlar a ordem na qual os dados serão numerados e impressos quando não couberem em uma página. A figura de exemplo ao lado das opções mostra a direção na qual o documento será impresso quando você selecionar uma delas.

Definir Área de Impressão

Se você imprime frequentemente uma seleção específica da planilha, defina uma área de impressão que inclua apenas essa seleção. Uma área de impressão corresponde a um ou mais intervalos de células que você seleciona para imprimir quando não deseja imprimir a planilha inteira. Quando a planilha for impressa após a definição de uma área de impressão, somente essa área será impressa. Você pode adicionar células para expandir a área de impressão quando necessário e limpar a área de impressão para imprimir toda a planilha.

Uma planilha pode ter várias áreas de impressão. Cada área de impressão será impressa como uma página separada. Essa área de impressão pode ser visualizada mudando-se o modo de exibição na Barra de Status para Visualização de Quebra de Página.

Para definir uma ou mais áreas de impressão, siga os seguintes passos:

• Na planilha, selecione as células que você deseja definir como área de impressão. É possível criar várias áreas de impressão mantendo a tecla CTRL pressionada e clicando nas áreas que você deseja imprimir.

• Na guia Layout da Página, no grupo Configurar Página, clique em Área de Impressão e, em seguida, clique em Definir Área de Impressão.

14 Impressão de planilhas Parte 2

A área de impressão definida é salva quando você salva a pasta de trabalho. Porém, você pode adicionar células a uma área de impressão, na visualização de quebra de página. Se as células que você deseja adicionar são adjacentes à área de impressão atual, posicione o mouse na a borda azul tracejada e quando o ponteiro ficar no formato de seta de duas pontas, arraste para incluí-las.

Limpar Área de Impressão

Para remover uma área de impressão definida (não é remover o conteúdo das células), faça o seguinte:

• Clique em qualquer lugar da planilha na qual você deseja limpar a área de impressão.

• Na guia Layout da Página, no grupo Configurar Página, clique em Limpar Área de Impressão.

Se a sua planilha contiver várias áreas de impressão, limpar uma área de impressão removerá todas as áreas de impressão na planilha.

Impressão

Para imprimir dados é necessário que exista uma impressora conectada ao seu computador, ou compartilhada entre vários computadores através de uma rede, e devidamente instalada. Atendendo esses requisitos, siga os seguintes passos:

• Clique na planilha ou selecione as planilhas que você deseja imprimir.

• Clique em Arquivo e depois clique em Imprimir ou pressione CTRL+P.

As opções desta caixa são:

Imprimir: Inicia a impressão da planilha.

Cópias: Para determinar o número de cópias a ser impresso.

Impressora: Nesta caixa, Selecione uma impressora. As informações que aparecem abaixo da caixa Nome (Status, Tipo, Onde, Comentário) aplicam-se à impressora selecionada.

Propriedades da Impressora: Clique neste botão para alterar as opções para a impressora selecionada. As configurações especificadas aplicam-se ao documento ativo e também podem afetar as configurações de impressão de outros aplicativos em uso.

15 Recursos avançados

Proteção de Planilhas e Pasta de Trabalho

Podemos proteger toda ou parte de uma planilha. Por padrão o MS Excel tem todas as células bloqueadas, porém o bloqueio só funciona quando colocamos senhas nas planilhas. Vejamos então como proceder:

• Selecione um conjunto de células;

• Clique com o botão direito e escolha a opção: Formatar células;

• Na janela que aparecer vá à guia Proteção;

• Desmarque a opção Bloqueada, assim estas células estarão livres do bloqueio que aplicaremos a seguir;

• Clique em OK;

• Selecione a guia Revisão;

• Clique no botão Proteger Planilha do grupo Alterações;

• Na janela que aparecer coloque uma Senha e, logo abaixo, marque ou não as opções que você deseja liberar para outros usuários. Geralmente deixamos marcadas somente as duas primeiras opções. Veja a figura abaixo:

• Pronto, clique em OK redigite sua senha e OK de novo.

Para desprotegê-la, clique na guia Revisão, no botão Desbloquear planilha e, na janela que aparecer, digite a senha, clique em OK. Podemos proteger sem senha, apenas clicamos no OK, mas qualquer pessoa poderá desbloquear.

Tabela Dinâmica

É uma ferramenta interativa e muito poderosa para análise de dados e tomada de decisões, cruzando informações de maneira rápida e flexível. Através dela você pode resumir centenas de informações de forma clara e objetiva e assim facilitar a análise dessas informações, gerando rapidamente novas tabelas onde serão analisadas de várias formas diferentes.

Podemos dizer que o recurso Tabela dinâmica, como o próprio nome diz, torna o trabalho de análise de dados muito mais dinâmico e prático.

Criando tabelas dinâmicas

O primeiro passo é abrir o arquivo com a tabela que você deseja transformar em tabela dinâmica.

Selecione o campo da tabela com o mouse.

Agora, clique na guia Inserir e depois em Tabela Dinâmica e então surgirá uma nova janela em sua tela. Nela, você poderá alterar os campos que farão parte de sua nova tabela e também definir o local aonde ela será criada (na mesma ou em uma nova planilha). Do lado direito aparece à lista de campos da tabela dinâmica. Selecione na lista de campos (à direita) os itens que aparecerão na tabela dinâmica.

Ainda na barra do lado direito, você poderá mover os dados entre os campos: Filtro de Relatórios, Rótulos de Coluna, Rótulos de Linha e Valores.

Rótulos de Linhas: Arrastando o campo para a área de rótulo de linha, a tabela dinâmica vai buscar todos os valores, pegar apenas os valores únicos e listar eles em linhas.

Rótulos de Colunas: O mesmo procedimento para o rotulo de colunas podemos escolher qualquer campo e jogar para a área de rótulos de colunas.

Valores: Como você deve estar percebendo, a tabela dinâmica já tomou forma, mas ainda faltam os valores. Nesse caso, é importante que a tabela base você tenha o valor que procura.

Podemos usar várias operações com as operações matemáticas básicas para serem executadas na tabela dinâmica, basta selecionar a tabela dinâmica e ir à guia opções e grupo configurações do campo ou o grupo cálculos.

Filtro de Relatório: Podemos ainda aplicar um filtro onde vai aparecer uma caixinha de filtro acima da sua tabela dinâmica que, ao clicar lá, você pode editar e escolher as datas que quiser.

Gráfico Dinâmico

A criação de um Gráfico Dinâmico é muito simples. Segue os mesmos passos da Tabela Dinâmica, no grupo Tabelas, clicar na opção Tabela Dinâmica, escolhe-se a opção: Gráfico Dinâmico.

Uma vez determinado a fonte de dados do gráfico e se o gráfico será inserido na mesma planilha ou em uma nova, surgirá a seguinte tela para escolha do tipo e dos campos que irão compor o gráfico.

Outra forma de fazer um gráfico dinâmico já com uma tabela dinâmica, basta clicar na tabela dinâmica e apertar a tecla F11.

A diferença do gráfico tradicional é que contem filtros, na qual podemos modificar a qualquer instante.

Compartilhando Planilhas do Excel

Podemos criar uma pasta de trabalho compartilhada e disponibilizá-la em um local de rede para que várias pessoas possam editar o conteúdo simultaneamente. Por exemplo, se cada pessoa do seu grupo de trabalho lidar com vários projetos e precisar saber o status dos projetos dos outros membros, o grupo poderá usar uma pasta de trabalho compartilhada para acompanhar o status dos projetos e atualizar as informações.

Como proprietário da pasta de trabalho compartilhada, você poderá gerenciá-la controlando o acesso do usuário a ela e resolvendo alterações conflitantes. Após incorporar todas as alterações, você poderá interromper o compartilhamento da pasta de trabalho.

16 Recursos avançados Parte 2

Vamos ver um exemplo:

• Crie a planilha que deve ser compartilhada ou abra o arquivo que você deseja compartilhar.

• Na guia Revisão, no grupo Alterações, clique no botão Compartilhar Pasta de Trabalho.

• Clique na caixa de seleção “Permitir alterações por mais de um usuário ao mesmo tempo”.

• Clique em OK.

• O arquivo compartilhado ao ser aberto apresentará na barra de títulos à descrição [Compartilhado].

A partir deste ponto, os usuários poderão alterar simultaneamente o arquivo. As alterações aparecerão somente quando o arquivo for salvo. Para alterar atributos do compartilhamento, clique na Guia Avançadas antes de confirmar o compartilhamento do arquivo.

Após o salvamento, as células alteradas apresentarão uma caixa de comentário, indicada por uma seta no canto superior esquerdo da célula. Quando o mouse é posicionado sobre a célula.

Dica: Se o conteúdo da mesma célula for alterado pelos 2 usuários, prevalecerá a última alteração salva.

Função PROCV

Você pode usar a função PROCV (Procura Vertical) para pesquisar a primeira coluna de um intervalo de células e, em seguida, retornar um valor de qualquer célula na mesma linha do intervalo. Por exemplo, suponhamos que você tenha uma lista de funcionários contida no intervalo A2:C10. Os números de identificação dos funcionários são armazenados na primeira coluna do intervalo, como mostrado na ilustração a seguir.

Se souber o número de identificação do funcionário, você poderá usar a função PROCV para retornar o departamento ou o nome desse funcionário. Para obter o nome do funcionário número 38, você pode usar a fórmula =PROCV(38, A2:C10, 3, FALSO). Essa fórmula procura o valor 38 na primeira coluna do intervalo A2:C10 e, em seguida, retorna o valor contido na terceira coluna do intervalo e na mesma linha do valor procurado ("Nuno Farinha") ("Nuno Farinha").

Sintaxe

PROCV(valor_procurado, matriz_tabela, núm_índice_coluna, [procurar_intervalo]).Onde:

Valor_procurado (Obrigatório). O valor a ser procurado na primeira coluna da tabela ou intervalo. O argumento valor_procurado pode ser um valor ou uma referência. Se o valor que você fornecer para o argumento valor_procurado for menor do que o menor valor da primeira coluna do argumento matriz_tabela, PROCV retornará o valor de erro #N/D.

Matriz_tabela (Obrigatório). O intervalo de células que contém os dados. Você pode usar uma referência a um intervalo (por exemplo, A2:D8) ou um nome de intervalo. Os valores na primeira coluna de matriz_tabela são os valores procurados por valor_procurado. Os valores podem ser texto, números ou valores lógicos. Textos em maiúsculas e minúsculas são equivalentes.

Núm_índice_coluna (Obrigatório). O número da coluna no argumento matriz_tabela do qual o valor correspondente deve ser retornado. Um argumento núm_índice_coluna de 1 retorna o valor na primeira coluna em matriz_tabela; um núm_índice_coluna de 2 retorna o valor na segunda coluna em matriz_tabela e assim por diante.

Se o argumento núm_índice_coluna for:

• Menor que 1, PROCV retornará o valor de erro #VALOR!.

• Maior do que o número de colunas em matriz_tabela, PROCV retornará o valor de erro #REF!.

Procurar_intervalo (Opcional). Um valor lógico que especifica se você quer que PROCV localize uma correspondência exata ou aproximada.

• Se procurar_intervalo for VERDADEIRO, ou for omitida, uma correspondência exata ou aproximada será retornada. Se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor_procurado será retornado.

Observação:

Se procurar_intervalo for VERDADEIRO, ou for omitido, os valores na primeira coluna de matriz_tabela deverão ser colocados em ordem ascendente; caso contrário, PROCV poderá não retornar o valor correto.

exemplo

Na primeira planilha digitaremos conforme dados abaixo :

Depois renomeamos a planilha para consulta:

Passando para outra planilha vamos montar a seguinte planilha. Depois renomearemos para Tabela de preços.

Agora aplicaremos a seguinte função na célula B3 da planilha consulta =PROCV(B2;'TABELA DE PREÇO'!A2:C5;2;FALSO). Logo em seguida faremos o mesmo para a célula B4 colocando a seguinte função =PROCV(B2;'TABELA DE PREÇO'!A2:C5;3;FALSO). Agora vamos testar digitando, por exemplo, o código 2 na célula B2 da planilha 2 de consulta.

Função PROCH

Esta função localiza um valor na linha superior de uma tabela ou matriz de valores e retorna um valor na mesma coluna de uma linha especificada na tabela ou matriz.

Sintaxe

PROCH(valor_procurado, matriz_tabela, núm_índice_lin, [procurar_intervalo]) Onde:

Valor_procurado (Obrigatório). O valor a ser localizado na primeira linha da tabela. Valor_procurado pode ser um valor, uma referência ou uma cadeia de texto.

Matriz_tabela (Obrigatório). Uma tabela de informações onde os dados devem ser procurados. Use uma referência para um intervalo ou um nome de intervalo.

17 Recursos avançados Parte 3

• Os valores na primeira linha de matriz_tabela podem ser texto, números ou valores lógicos.

• Se procurar_intervalo for VERDADEIRO, os valores na primeira linha de matriz_tabela deverão ser colocados em ordem ascendente: ...-2, -1, 0, 1, 2,... , A-Z, FALSO, VERDADEIRO, caso contrário, PROCH pode não retornar o valor correto. Se procurar_intervalo for FALSO, matriz_tabela não precisará ser ordenada.

• Textos em maiúsculas e minúsculas são equivalentes.

• Classifique os valores em ordem crescente, da esquerda para a direita.

Núm_índice_lin (Obrigatório). O número da linha em matriz_tabela de onde o valor correspondente deve ser retirado. Um núm_índice_lin equivalente a 1 retorna o valor da primeira linha na matriz_tabela, um núm_índice_lin equivalente a 2 retorna o valor da segunda linha na matriz_tabela e assim por diante. Se núm_índice_lin for menor do que 1, PROCH retornará o valor de erro #VALOR!; se núm_índice_lin for maior do que o número de linhas na matriz_tabela, PROCH retornará o valor de erro #REF!.

Procurar_intervalo (Opcional). Um valor lógico que especifica se você quer que PROCH localize uma correspondência exata ou aproximada. Se VERDADEIRO ou omitido, uma correspondência aproximada é retornada. Em outras palavras, se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor_procurado é retornado. Se FALSO, PROCH encontrará uma correspondência exata. Se nenhuma correspondência for localizada, o valor de erro #N/D será retornado. Exemplo:

Digite na célula A4 a seguinte função: =PROCH (5;A1:L2;2;0).

Funções Aninhadas

O objetivo aqui é simples no qual se resume a usar as funções aninhadas sempre que uma única função não for capaz de suprir a necessidade do seu uso, assim podemos fazer o uso da combinação de duas ou mais funções.

Em determinados casos, é possível você precise usar uma função como um dos argumentos de outra função.

Por exemplo, a fórmula a seguir usa uma função aninhada MÉDIA e compara o resultado com o valor 50. É possível aninhar até 64 níveis de funções em uma fórmula.

Retornos válidos

Quando uma função aninhada é usada como argumento, ela deve retornar o mesmo tipo de valor utilizado pelo argumento. Por exemplo, se o argumento retornar um valor VERDADEIRO ou FALSO, a função aninhada deverá retornar VERDADEIRO ou FALSO. Se não retornar, o MS Excel exibirá um valor de erro #VALOR! Vejamos agora algumas aplicações:

Função aninhada SE dentro de SE

A função SE serve para retornar um valor de acordo com uma condição que você estabelece. Agora, podemos utilizar múltiplas respostas de acordo com mais de uma condição, pode fazer a função SE aninhada a ela mesma. Deste modo, basta inserir uma condição seguida da outra, e a função retornará o que for verdadeiro. Veja que no exemplo a seguir uma tabela com a altura de cada atleta. Note que é necessário classificar cada um com relação a sua altura. Deste modo, iremos adotar o seguinte padrão:

Altura igual ou acima de 1,90m = Alto

Altura igual ou acima de 1,60m = Médio

Altura abaixo de 1,60m = Baixo

Vamos inserir a seguinte função na célula C2: =SE(B2>=1,9; “Alto”; SE(B2>=1,6; “Médio”; “Baixo”)). Vemos que a função representa realmente o que é desejado, ou seja, se a altura for maior ou igual a 1,90, então retornará a palavra “Alta”, se essa condição não for verdadeira, a lógica do Excel irá para a próxima função SE até encontrar o valor correspondente.

Função aninhada SE com E ou OU

Podemos combinar a função SE, colocando a função E dentro dela. Dessa forma, a condição que deverá ser respeitada para retornar um valor terá mais de um critério para ser verdadeiro para que ela seja válida. Isso ocorre porque a função E traz um valor verdadeiro quando todos os argumentos forem verdadeiros. Deste modo, se houver um único argumento falso, a função E trará o resultado Falso. Vamos ver um exemplo para melhor compreensão:

Na tabela a seguir para geração de cupons de descontos em uma determinada loja. Observe que para o cliente ganhar o cupom, este deve realizar pelo menos 3 compras no valor de R$50,00.

Assim você pode usar a função SE com E para determinar se o seu cliente terá ou não direito ao cupom. Na célula E2, a função deverá ficar assim: =SE(E(B2>=$F$2;C2>=$F$2;D2>=$F$2;"Ganha";"Não Ganha")

Se usarmos o OU invés do E, basta qualquer comprar para que o cliente tenha direito ao cupom.

Função aninhada SE com HOJE

Outro exemplo de funções aninhadas é a combinação do SE com a função HOJE para determinar status de prazos de acordo com a diferença entre uma data base (de vencimento de fatura ou de último contato, por exemplo) e um valor que seja o seu padrão ou o de mercado. Dessa forma podemos conseguir fazer comparações com datas.

Temos uma tabela com contas a serem pagas e suas respectivas datas de vencimento. Neste caso, queremos saber se a conta está vencida ou ainda irá vencer com base na data de hoje. Insira datas de vencimento antes e depois da data atual que você esta realizando este exemplo.

A função que devemos usar é: = SE(HOJE()<=B2;"A Vencer";"Vencida".

Note que a função está dizendo que se a data de hoje for menor ou igual à data de vencimento.

Função aninhada PROCV com MAIOR/MENOR

Como já estudado, o PROCV serve para fazer a busca por um termo específico, que definimos e, depois disso, retorna um valor correspondente. Vamos buscar o valor correspondente ao maior ou menor número de uma lista.

Temos uma lista de pessoas e suas respectivas idades. Se quiséssemos obter o nome da pessoa com maior ou menor idade dessa lista de maneira automática, o melhor caminho seria usando a função PROCV em conjunto com as funções MAIOR ou MENOR.

Usaremos a função: =PROCV(MAIOR(A2:A9;1);A2:B9;2;0) na célula E2, para determinar a pessoa mais velha e =PROCV(MENOR(A2:A9;1);A2:B9;2;0) na célula E4 para pessoa mais nova.

Funções ÉERROS e SEERRO

Existem 7 tipos de erros no MS Excel, mostraremos uma solução para esconder esses erros.

Função ÉERROS

A função ÉERROS verifica se um determinado valor seja ele uma célula ou fórmula contém algum tipo de erro. O resultado da função é um tipo lógico, podendo ser VERDADEIRO ou FALSO. Quando o valor analisado contiver algum tipo de erro, a fórmula retornará VERDADEIRO, caso contrário, retornará FALSO, o que significa que o valor não possui nenhuma inconsistência.

18 Recursos avançados Parte 4

A função ÉERROS estende a função EÉRRO, permitindo qualquer tipo de erro daqueles que são disponibilizados pelo Excel, (#N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!).

A função ÉERROS é útil para prever possíveis problemas em fórmulas construídas que utilizam valores externos ou referenciados. Isso significa que, em algum momento é possível que algum usuário entre com um dado inválido para sua fórmula, gerando uma expressão de erro como resultado.

Casos comuns pode ser a realização de cálculos aritméticos utilizando células que contenham valores de texto, quando acontece uma operação divisão por zero ou quando se busca um valor inexistente ou inválido.

Sintaxe da função ÉERROS

=ÉERROS(valor)

O uso desta função combinada com a função SE, ou seja, função aninhada pode evitar que as planilhas gerem resultados indesejados.

Veja um exemplo: =SE(ÉERROS(A1/B1);"";A1/B1)) O erro #DIV/0! , surge da operação divisão por 0. Para evitar que ele apareça podemos utilizar a função ÉERROS. Ou seja, Se a divisão por 0 trouxer como valor algum erro, apareça "nada", se não faça a operação de divisão.

Função SEERRO

A função SEERROS, surgiu a partir da versão 2007, para facilitar o uso do exemplo acima:

Sintaxe da função SEERO:

=SEERRO(valor; valor_se_erro). Valor: Pode se referir a uma célula ou fórmula que será testada. Valor_se_erro: Valor ou expressão a ser retornada se o argumento inicial retornar um erro.

No exemplo acima ficaria assim: =SEERRO(A1/B1;“”), bem mais simples.

Macros

Uma macro é uma sequência de comandos e funções armazenados em um módulo de VBA (linguagem de programação usada pelo MS Excel e pode ser usada sempre que você precisar executar a tarefa, funcionando, portanto, como uma espécie de atalho. É usada, muitas vezes, para tarefas repetitivas, visando menor perda de tempo em etapas demoradas, automatizando-as. A macro gravada no MS Excel, armazena suas informações referente a cada etapa realizada à medida que você vai executando uma série de comandos.

Existem, contudo, duas maneiras de se criar uma macro: através do uso de uma VBA, ou, usando o 'gravador de macro', no menu do Excel. Portanto, as macros são programações, mas para usá-las, você não precisa ser um desenvolvedor e nem mesmo ter conhecimento de programação, pois sua linguagem é bem acessível. Ao criar uma macro, você escolhe uma tecla que servirá de atalho para seu comando, facilitando sua execução.

Antes de gravar uma macro, planeje as etapas e os comandos que você deseja que a macro execute. Se cometer um erro durante a gravação, as correções feitas também são gravadas.

Por que devemos usá-Las?

As macros, acima de tudo, economizam tempo, pois com as tarefas já automatizadas, não haverá a necessidade de se realizar o processo todo manualmente. Estas podem ser usadas em tarefas repetitivas de produção de documentos, simplificar tarefas cansativas ou criar soluções, como automatizar a criação de documentos que você e seus colegas de trabalho usam regularmente.

Além disso, no que tange a segurança, a Microsoft Excel fornece uma boa proteção contra vírus que podem ser transmitidos através das macros. Se caso você compartilhar macros com outros usuários, podes certificá- las com uma assinatura digital de forma que os outros usuários possam verificar que as macros são de origem confiável. Portanto, sempre ao abrir uma pasta de trabalho que contenha macros, verifique a origem das mesmas antes de ativá-las.

Para criar uma macro precisamos ter atenção em três coisas:

1. Antes de gravar uma macro

Verifique se a guia Desenvolvedor está visível na faixa de opções. Por padrão, a guia Desenvolvedor não permanece visível; sendo assim, proceda da seguinte forma:

• Clique na guia Arquivo, clique em Opções e depois clique na categoria Personalizar Faixa de Opções.

• Em Personalizar a Faixa de Opções, na lista Guias Principais, clique em Desenvolvedor e em OK.

2. Gravar uma macro

No grupo Código da guia Desenvolvedor, clique em Gravar Macro e em OK para iniciar a gravação.

Execute algumas ações em sua planilha, como digitar um texto, selecionar colunas ou linhas, ou preencher alguns dados. No grupo Código da guia Desenvolvedor, clique em Parar Gravação.

3. Examine melhor a macro e faça o teste

Você pode aprender um pouco sobre a linguagem de programação Visual Basic editando uma macro gravada.

Para editar uma macro, no grupo Código da guia Desenvolvedor, clique em Macros, selecione o nome da macro gravada e clique em Editar. Isso inicia o Editor do Visual Basic. Examine o código e veja como as ações que você gravou aparecem codificadas. Alguns códigos provavelmente serão claros para você, enquanto outros não.

Teste o código, feche o Editor do Visual Basic e execute a macro novamente. Desta vez, veja se algo diferente acontece!

19 Teste de Hipóteses Parte 1

Teste de hipótese é o processo de alterar os valores em células para ver como essas alterações afetam o resultado das fórmulas na planilha.

Três tipos de ferramentas de testes de hipóteses são fornecidos com o Excel: cenários, tabelas de dados e Atingir Meta. Cenários e tabelas de dados usam conjuntos de valores de entrada e determinam os resultados possíveis. Uma tabela de dados funciona somente com uma ou duas variáveis, mas pode aceitar vários valores diferentes para essas variáveis. Um cenário pode ter diversas variáveis, mas pode acomodar apenas até 32 valores. Atingir Meta funciona de maneira diferente em cenários e tabelas de dados, uma vez que usa um resultado e determina os possíveis valores de entrada que geram esse resultado.

Além dessas três ferramentas, você pode instalar suplementos que o ajudem a realizar testes de hipóteses, como o suplemento Solver, que é semelhante a Atingir Meta, mas pode acomodar mais variáveis.

Cenários

Um cenário é um conjunto de valores que o Excel salva e pode substituir automaticamente em células de uma planilha. Você pode criar e salvar diferentes grupos de valores em uma planilha e alternar para qualquer um desses novos cenários para exibir resultados diferentes.

Por exemplo, suponha que existam dois cenários de orçamento: um no pior dos casos e outro no melhor dos casos. É possível usar o recurso Gerenciador de Cenários para criar ambos os cenários na mesma planilha e então alternar entre eles. Para cada cenário, especifique as células que são alteradas e os valores a serem usados para esse cenário. Quando você alternar entre os cenários, a célula de resultado será modificada para refletir os diferentes valores das células variáveis.

Vamos montar nossa planilha:

Criar um cenário

Antes de criar um cenário, você já deve ter um conjunto inicial de valores na planilha. Para facilitar a leitura dos relatórios de resumo de cenários, considere também a nomeação das células que você planeja usar nos cenários.

1. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, clique em Gerenciador de Cenários.

2. Clique em Adicionar.

3. Na caixa Nome do cenário, digite um nome para o cenário.

4. Na caixa Células variáveis, insira as referências para as células que você deseja especificar no seu cenário. Por exemplo, se você quiser ver como a alteração dos valores das células B1 e B2 irá afetar o resultado de uma fórmula com base nessas células, insira B1,B2.

5. Em Proteção, selecione as opções desejadas.

• Selecione Evitar Alterações para impedir a edição do cenário quando a planilha estiver protegida.

• Selecione Oculto para impedir a exibição do cenário quando a planilha estiver protegida.

6. Clique em OK.

7. Na caixa de diálogo Valores de Cenário, digite os valores que você deseja usar nas células variáveis desse cenário.

8. Para criar o cenário, clique em OK.

9. Se quiser criar cenários adicionais, repita as etapas de 2 a 8. Quando concluir a criação de cenários, clique em OK e em Fechar na caixa de diálogo Gerenciador de Cenários.

Mesclar cenários

1. Selecione a planilha na qual armazenar os resultados de cenários mesclados.

2. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, clique em Gerenciador de Cenários.

3. Clique em Mesclar.

4. Na caixa de diálogo Mesclar Cenários, clique na seta ao lado de Pasta e selecione uma pasta de trabalho que contêm cenários a serem mesclados nos seus resultados.

5. Na caixa Planilha, clique no nome da planilha que contém os cenários que você deseja mesclar.

6. Clique em OK para mesclar os cenários da planilha selecionada na planilha atual.

A caixa de diálogo Mesclar Cenários é fechada, e os cenários mesclados passarão a ser exibidos na caixa de diálogo Gerenciador de Cenários.

7. Repita as quatro etapas anteriores conforme necessário até mesclar todos os cenários desejados. Quando terminar, os cenários que você mesclou farão parte da planilha atual. Você pode fechar a caixa de diálogo Gerenciador de Cenários ou pode deixá-la aberta para continuar a análise.

Criar um relatório de resumo do cenário

1. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, clique em Gerenciador de Cenários.

2. Clique em Resumo.

20 Teste de Hipóteses Parte 2

3. Clique em Resumo do cenário ou Relatório de tabela dinâmica do cenário.

4. Na caixa Células de resultado, insira as referências para as células que fazem referência a células cujos valores são alterados pelos cenários. Separe as diversas referências por vírgulas.

Atingir Meta

O recurso Atingir Meta no Excel tem por finalidade alterar o valor de uma célula para que uma fórmula atinja determinado valor. Por exemplo, suponha que você precise de algum dinheiro emprestado. Você sabe o quanto precisa, por quanto tempo deseja pagar o empréstimo e quanto pode pagar por mês.

Vamos demonstrar através de um exemplo, mas antes precisamos preparar uma planilha.

1. Abra uma nova planilha em branco.

2. Primeiro, adicione alguns rótulos à primeira coluna para facilitar a leitura da planilha.

• Na célula A1, digite Valor do Empréstimo.

• Na célula A2, digite Prazo em Meses.

• Na célula A3, digite Taxa de Juros.

• Na célula A4, digite Pagamento.

3. Em seguida, adicione os valores que você conhece.

• Na célula B1, digite 100000. Esse é o valor que você deseja pedir emprestado.

• Na célula B2, digite 180. Esse é o número de meses no qual você deseja liquidar o empréstimo.

4. Em seguida, adicione a fórmula para a qual você tem uma meta. No exemplo, use a função PGTO:

 Na célula B4, digite =PGTO(B3/12;B2:B1). Essa fórmula calcula o valor do pagamento. Nesse exemplo, você deseja pagar R$ 900 por mês, mas não insere esse valor porque pretende usar

Atingir Meta para determinar a taxa de juros e Atingir Meta requer que você comece com uma fórmula.

A fórmula faz referência às células B1 e B2, que contêm valores que você especificou nas etapas anteriores. A fórmula também faz referência à célula B3, que é onde você irá especificar que Atingir Meta deve inserir a taxa de juros. A fórmula divide o valor em B3 por 12 porque você especificou um pagamento mensal e a função PGTO assume uma taxa de juros anual.

Como não há nenhum valor na célula B3, o Excel assume uma taxa de juros de 0% e, usando os valores no exemplo, retorna um pagamento de R$ 555,56. Você pode ignorar esse valor por enquanto.

Use Atingir Meta para determinar a taxa de juros

1. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, clique em Atingir Meta.

2. Na caixa Definir célula, insira a referência para a célula que contém a fórmula que você deseja resolver. No exemplo, essa referência é a célula B4.

3. Na caixa Para valor, digite o resultado da fórmula desejado. No exemplo, esse valor é -900. Observe que o número é negativo porque representa um pagamento.

4. Na caixa Alternando célula, insira a referência para a célula que contém o valor que você deseja ajustar.

No exemplo, essa referência é a célula B3.

Observação: A célula alterada por Atingir Meta deve ser referenciada pela fórmula na célula que você especificou na caixa Definir célula.

Clique em OK

6. O recurso Atingir Meta é executado e gera um resultado, como mostra a ilustração a seguir.

Por fim, formate a célula de destino (B3) de forma que ela exiba o resultado como uma porcentagem.

• Na guia Página Inicial, no grupo Número, clique em Porcentagem.

• Clique em Aumentar Casas Decimais ou Diminuir Casas Decimais para definir o número de casas decimais.

Tabela de Dados

Se você tiver uma fórmula que usa uma ou duas variáveis ou várias fórmulas que usam uma variável comum, poderá usar uma tabela de dados para ver todos os resultados em um só local. O uso de tabelas de dados facilita o exame geral de diversas possibilidades. Como você se concentra em apenas uma ou duas variáveis, os resultados podem ser lidos e compartilhados facilmente em formato tabular. Se o recálculo automático estiver habilitado para a pasta de trabalho, os dados nas tabelas de dados serão imediatamente recalculados e, consequentemente, você sempre terá dados atuais.

21 Teste de Hipóteses Parte 3

Uma tabela de dados não pode acomodar mais do que duas variáveis. Para analisar mais de duas variáveis, é possível usar cenários. Embora limitada a apenas uma ou duas variáveis, uma tabela de dados pode usar quantos valores de variáveis diferentes você quiser. Um cenário pode ter no máximo 32 valores diferentes, mas você pode criar quantos cenários desejar.

Vejamos o exemplo a seguir:

Suponha uma projeção de valores para uma compra de estoque com os preços que são pagos habitualmente. O comprador quer saber se compensaria comprar mais unidades, levando em conta uma possível variação de custos e de quantidade. Para isso observaremos a seguinte planilha.

Manualmente, precisaríamos digitar algumas fórmulas para calcular todas as multiplicações, mas utilizando o comando tabela de dados faremos com poucos passos.

Primeiramente selecionaremos a área de projeção, no exemplo, será o intervalo C6:K17. Note que você precisa selecionar, também, a célula onde está o 'Custo Total', conforme Figura 165. Após selecionar, vá à guia Dados, depois vá em 'Teste de Hipóteses' e 'Tabela de Dados'.

Na janela que se abrirá, o MS Excel irá pedir as referências para ele usar como parâmetro para criar a tabela a ser projetada. Para isso será usado os valores que tínhamos inicialmente. No campo Célula de entrada de linha precisaremos selecionar a célula onde há inicialmente a quantidade (C4), pois os valores a serem projetados estão dispostos em uma linha, note que no exemplo é a linha 6. Em Célula de entrada de coluna repetiremos o mesmo processo, você terá de selecionar a célula que corresponde ao custo unitário (C5), pois veja que para a projeção que faremos Variação do preço unitário está disposto em coluna (coluna B). Depois pode clicar em OK.

Solver

O Solver é uma ferramenta poderosa do MS Excel que permite fazer vários tipos de simulações na sua planilha, sendo utilizado principalmente para análise de sensibilidade com mais de uma variável e com restrições de parâmetros.

Quando encontramos mais de uma variável em um problema, com necessidade de limites e restrições, o Atingir Meta não poderá solucioná-lo, pois tem limites de parâmetros para simulação. Para isso, devemos utilizar o recurso Solver.

Ativando o comando Solver

Para ativar o Solver na sua planilha e liberar a utilização, você deverá ativá-lo em Suplementos.

• Na guia arquivo clique em Opções.

• Na janela de Opções do Excel clique em Suplementos. Perceba que o Solver está entre os Suplementos de Aplicativos Inativos.

• Para ativá-lo, basta clicar em Ir... onde iremos gerenciar os suplementos do Excel.

• Na próxima janela, marque a opção Solver e confirme.

• Será remetida uma mensagem a qual dirá que é necessário instalá-lo, aceite e aguarde a instalação.

• Depois de concluída, a instalação reiniciará o documento Excel para a ativação do suplemento.

• Na guia Dados será criado um novo grupo chamado de Análise e nele estará o comando Solver.

Detalhando o comando Solver

Com o Solver, você pode localizar um resultado ideal para uma fórmula em uma célula na sua planilha, chamada de célula de destino, com as seguintes possibilidades:

• Maximizar valores;

• Minimizar valores;

• Atingir uma meta de valor específico.

Ele trabalha com um grupo de células relacionadas direta ou indiretamente com a fórmula na célula de destino. Ou seja, todas as células que influenciam no resultado da célula destino poderão ser alteradas pelo próprio Excel, desde que sejam fórmulas inter-relacionadas e atinjam a meta desejada, avaliando todas as restrições e atingindo o resultado mais próximo possível.

O Solver ajusta simultaneamente as variáveis nas células que você especificar, chamadas de células ajustáveis, para atingir o resultado esperado por você através da célula de destino, a qual nunca pode ser uma fórmula e sim um input para que o Solver possa ser executado.

Importante: As células variáveis são sempre informações imputados que podem alterar o resultado das células destino. Portanto as células variáveis só podem ser input, caso contrário o Excel irá retornar um erro de consistência.

Exemplo:

Um empresário decide reduzir o seu preço unitário de venda em 20% para que ele possa se igualar ao principal concorrente em termos de preço. Porém esse mesmo empresário não quer que o seu lucro estimado de R$24.500 seja reduzido. Mas, se o preço unitário for reduzido em 20%, conforme planejado, o Lucro Líquido cairá para $13.860,00.

Considerando as prováveis variáveis, pergunta-se:

• Qual o percentual de aumento do volume de vendas para compensar a redução do preço?

• Qual o percentual possível de redução do custo variável?

• Qual o percentual possível de redução do custo fixo?

Veja a planilha abaixo com os resultados projetados originalmente pela empresa, antes de efetuar a redução dos preços:

22 Teste de Hipóteses Parte 4

Qual a melhor solução se eu quiser maximizar o meu resultado considerando as células variáveis todas em conjunto e simultâneas? Vejamos como isso pode ser feito no Solver.

1º Passo – Especificar a célula de destino que se deseja minimizar, maximizar ou ajustar para um determinado valor. Neste caso $C$13:

• Acesse o comandando Solver;

• Em Definir Objetivo informe $C$13;

• Em Igual a selecione Máx.

2º Passo – Especificar as células variáveis a serem ajustadas até uma solução ser encontrada:

• Em Células variáveis informe $C$3:$C$5, que são as células que irão sofrer alterações para que o Lucro Líquido possa ser maximizado.

3º Passo – Especificar as células de restrição que devem ficar dentro de determinados limites ou satisfazer os valores de destino. Vejamos:

• O volume de vendas não pode ser superior a quantidade em estoque no período. Sendo assim, $C$3 não pode ser superior a 230 unidades;

• O custo variável unitário não pode ser inferior ao que poder ser negociado com o fornecedor, principalmente visando manter a qualidade do produto final a ser vendido. Então nesse caso $C$4 não pode ser inferior a R$ 175, que foi o melhor nível negociado com o fornecedor;

• O custo fixo total não pode ser inferior a uma estrutura mínima necessária para que a empresa possa funcionar adequadamente. Nesse caso, o valor mínimo em $C$5 é atingir uma redução de no máximo 5% dos custos fixos atuais, passando então de R$ 5.000 para atingir um valor mínimo de até R$ 4.750.

• Podemos submeter a restrições as células ajustáveis (variáveis), a célula de destino ou outras células direta ou indiretamente relacionadas com a célula de destino incluindo na estrutura Solver abaixo:

Os operadores abaixo podem ser usados em restrições:

• <= Menor que ou igual a.

• >= Maior que ou igual a.

• = Igual a.

• núm Inteiro (aplica-se somente a células ajustáveis).

• bin Binário (aplica-se somente a células ajustáveis).

Veja como podemos incluir as restrições acima descritas do nosso exemplo no Solver:

• Clique no botão Adicionar e você verá a estrutura para incluir a primeira restrição, onde $C$3 (volume de vendas) não poderá ser superior a 230 (quantidade máxima em estoque por período);

• Clique novamente no botão Adicionar da tela de restrições para incluir mais o limite de redução dos custos variáveis unitários, onde $C$4 não poderá ser inferior a 175;

• Clique mais uma vez em Adicionar para incluir a última restrição no nosso exemplo, onde só poderemos reduzir o custo fixo total em, no máximo, 5%, o que significa que a célula $C$5 deverá ser maior ou igual a 4.750;

• Agora clique em OK para finalizar as restrições.

4º Passo – Solicitar que o problema seja resolvido pelo Solver do Excel, considerando todos os parâmetros e restrições. Vejamos:

• Clique em Resolver e você verá a seguinte tela:

Importante: Se o Solver conseguir resolver o problema considerando todos os parâmetros e restrições apresentados ele apresentará uma tela como a demonstrada acima. Se "estourar" o número de interações de cálculo ele irá informar que não será possível resolver, a não ser que os parâmetros e restrições sejam revistos.

Nessa tela você terá as seguintes opções:

• Manter solução do Solver: para manter os resultados que foram atingidos pela ferramenta Solver;

• Restaurar valores originais: para restaurar os valores originais;

23 Teste de Hipóteses Parte 5

• Relatórios: para ter acesso aos relatórios comparativos sobre as modificações executadas na planilha.

• Salvar cenário: No botão Salvar cenário será possível salvar a solução atual do Solver como um cenário (opcional);

• Para finalizar, clique em OK para manter os novos valores estimados pelo Solver, siga o resultado abaixo:

Conclusão: o máximo que o modelo pode apresentar com os parâmetros e restrições incluídas foi um Lucro Líquido de R$17.444.