Excel avançado 2007 nova versão

Excel

Avançado Orientador:

Ivair Cláudio Ferrari

Ivair Claudio Ferrari – 8839-8183 – ivairferrari@gmail.com

2

Videira (SC), December de 2013

Ivair Claudio Ferrari – 8839-8183 – ivairferrari@gmail.com

Sumรกrio

Capítulo 1 1.1. Introdução O Excel é considerado um dos mais importantes aplicativos de planilhas eletrônicas para uso em microcomputadores. Uma planilha eletrônica é na realidade uma folha de cálculo disposta em forma de tabela, onde poderão ser efetuados com extrema rapidez, vários tipos de cálculos matemáticos, sejam estes simples ou complexos.

O Excel é um poderoso aplicativo que permite a manipulação de planilhas de cálculo bem como a incorporação de gráficos e mapas dentro das mesmas. É possível criar planilhas de cálculo para orçamento, previsões e planejamentos para investimentos futuros, tabelas variadas, controle de gastos pessoais ou da empresa, controle de caixa, entre outras planilhas que envolvam cálculos.

Além disso, o Excel permite formatar as planilhas da maneira que desejar, ou seja, aplicar tipos de letras, tamanhos, cores e uma série de características especiais que podem ainda ser aplicados.

O objetivo deste material é orientá-lo para que possa extrair o máximo deste poderoso aplicativo e ser bem sucedido em seus trabalhos quer na escola, faculdade, em seu trabalho ou até mesmo em casa.

Capítulo 2 2.1.

Opções especiais do Excel

2.1.1.

A Diferença entre: Referências Relativas, Absolutas e Mistas

2.1.1.1.

Referências Relativas

Uma referência relativa em uma fórmula, como A1, é baseada na posição relativa da célula que contém a fórmula e da célula à qual a referência se refere.

Se a posição da célula que contém a fórmula se alterar, a referência será alterada.

Se você copiar ou preencher a fórmula ao longo de linhas ou de colunas, a referência se ajustará automaticamente. Por padrão, novas fórmulas usam referências relativas.

Por exemplo, se você copiar ou preencher uma referência relativa da célula B2 para a B3, ela se ajustará automaticamente de =A1 para =A2.

Fórmula copiada com referência relativa.

2.1.1.2.

Referências Absolutas

Uma referência absoluta de célula em uma fórmula, como $A$1, sempre se refere a uma célula em um local específico.

Se a posição da célula que contém a fórmula se alterar, a referência absoluta permanecerá a mesma.

Se você copiar ou preencher a fórmula ao longo de linhas ou colunas, a referência absoluta não se ajustará.

Por padrão, novas fórmulas usam referências relativas, e talvez você precise trocá-las por referências absolutas.

Por exemplo, se você copiar ou preencher uma referência absoluta da célula B2 para a célula B3, ela permanecerá a mesma em ambas as células =$A$1.

Fórmula copiada com referência Absoluta.

2.1.1.3.

Referências Mistas

Uma referência mista tem uma coluna absoluta e uma linha relativa, ou uma linha absoluta e uma coluna relativa.

Uma referência de coluna absoluta tem o formato $A1, $B1 e assim por diante. Uma referência de linha absoluta tem o formato A$1, B$1 e assim por diante.

Se a posição da célula que contém a fórmula se alterar, a referência relativa será alterada e a referência absoluta não se alterará.

Se você copiar ou preencher a fórmula ao longo de linhas ou colunas, a referência relativa se ajustará automaticamente e a referência absoluta não se ajustará.

Por exemplo, se você copiar ou preencher uma referência mista da célula A2 para B3, ela se ajustará de =A$1 para =B$1.

Fórmula copiada com referência mista.

2.1.2.

Caixa Nome

2.1.2.1.

Deslocamento

Com esta opção, podemos ir rapidamente para determinada célula, digitando a referência da célula na caixa nome.

2.1.2.2.

Campos Nomeados

Através da Caixa Nome podemos também nomear intervalos e utilizá-los em fórmulas em vez de utilizarmos os endereços das células. No menu Inserir/Nome encontramos duas opções que nos ajudam a fazer isto: Definir e Criar.

2.1.2.2.1.

Definir nome:

Podemos definir o nome de uma célula ou um intervalo de células.

Para dar um nome a uma célula ou a um intervalo de células, devemos preencher a caixa “Nomes na pasta de trabalho” e em seguida, colocar o endereço a que este nome se refere na caixa “Refere-se a:”.

Em nosso exemplo, definimos que as células da coluna ‘C2’ até ‘C17’ serão chamadas pelo seguinte nome: ‘Data’

2.1.2.2.2.

Criar nome automaticamente:

Cria nomes utilizando rótulos em um intervalo selecionado.

Quando selecionamos um intervalo de células em uma planilha e selecionamos a opção Criar nomes, que se encontra no menu Inserir Nomes, o Excel analisa os rótulos (ou cabeçalhos) da(s) linha(s) e ou da(s) coluna(s) para então, criar nomes relacionados a estes rótulos.

Em nosso exemplo, criamos nomes às colunas de acordo com seus rótulos da linha superior. Com isso, todas as colunas do intervalo selecionado, foram nomeadas com o nome que está na primeira linha de cada coluna.

Em nosso exemplo, percebemos que o intervalo selecionado, das células da coluna ‘C’, a partir da célula dois, recebeu o nome de ‘Data’.Agora, para nos referirmos ao endereço ‘C2’ até ‘C17’ poderemos fazer isso utilizando apenas o campo nomeado ‘Data’.

Veja em outro exemplo, como fica a fórmula, quando utilizamos o endereço convencional e o endereço nomeado.

Na primeira planilha estamos utilizando uma fórmula com endereço convencional (padrão: “=Soma(B2:B5)”) na célula “B6”. Utilizando a opção “Aplicar nomes”, substituímos a referência convencional pelo nome dado ao intervalo: •

“=Soma(B2:B5)”  “=Soma(Quantidade)”

2.1.3.

Comando “Ir Para”

Comando Ir para (menu Editar): uma das maneiras de movimentar o cursor para uma determinada célula da planilha é utilizando a opção do menu Editar, Ir Para. Ative o comando no menu ou pressione as teclas de atalho F5 ou Ctrl+Y. Na Caixa Referência, digite a referência da célula para onde deseja posicionar o cursor.

Na opção “Ir Para” encontramos o botão Especial que ativa a opção “Ir para especial”.

Ao ativarmos a opção Ir para / Especial..., poderemos selecionar: -

-

Comentários: seleciona todos os comentários da planilha ativa; Constantes: seleciona todas as células cujos valores não começam com um sinal de igual nem contêm uma fórmula. As caixas de seleção nesta opção definem o tipo de constante que se deseja utilizar (números, texto, lógicas, erros); Fórmulas: seleciona todas as células que contêm fórmulas. As caixas de seleção nesta opção definem o tipo de fórmula que desejamos utilizar; Em branco: seleciona todas as células vazias até a última célula da planilha que contém dado ou formatação.

Região atual: seleciona um intervalo de célula retangular ao redor da célula ativa. O Excel seleciona um intervalo no limite da combinação entre linhas e colunas vazias.

Matriz atual: seleciona uma matriz inteira (se a célula ativa estiver numa matriz).

Objetos: seleciona todos os objetos gráficos, incluindo os gráficos na planilha e em caixas de texto.

-

-

-

-

-

2.1.4.

Diferenças por linha: seleciona as células cujos conteúdos são diferentes da célula de comparação em cada linha. Para cada linha, a célula de comparação está na mesma coluna que a célula ativa.

Diferenças por coluna: seleciona as células cujos conteúdos são diferentes da célula de comparação em cada coluna. Para cada coluna, a célula de comparação está na mesma linha que a célula ativa.

Precedentes: seleciona as células às quais a fórmula faz referência na célula ativa.

Dependentes: seleciona células com fórmulas que fazem referência à célula ativa.

- Precedentes / Dependentes – Somente diretos: seleciona apenas as células às quais as fórmulas fazem referência direta na seleção (precedentes) ou seleciona apenas as células com fórmulas que fazem referência direta à célula ativa (dependentes). Esta opção só ficará disponível depois que clicarmos em Precedentes ou Dependentes.

- Precedentes / Dependentes – Todos os níveis: depois de clicar em Precedentes, seleciona todas as células às quais as células fazem referências diretas ou indiretas na seleção. Se clicarmos em Dependentes, todas as células que fazem referências diretas ou indiretas à célula ativa são selecionadas. Esta opção é útil para selecionar toda a rede de célula que afeta determinada célula em uma planilha complexa, ou para rastrear a lógica de um modelo, uma etapa por vez. Última célula: seleciona a última célula que contém dado ou formatação.

Somente células visíveis: seleciona células visíveis em uma planilha para que as alterações feitas afetem apenas células visíveis não afetando as ocultas.

Formatos condicionais: seleciona apenas as células com formatação condicional aplicada. Clique em Tudo para selecionar todas as células na planilha que possuem formatação condicional. Clique em Mesmos para selecionar as células que possuem a mesma formatação condicional que a célula selecionada no momento.

Validação de dados. Seleciona apenas as células com regras de validação de dados aplicados. Clique em Tudo para selecionar todas as células da planilha que têm a validação de dados aplicada. Clique em Mesmos para selecionar as células que possuem a mesma validação que a célula selecionada no momento.

Colar Especial

Colar especial...: esta opção cola (vinculando ou não) o conteúdo que foi copiado (que está na área de transferência) no local desejado.

Opções em Colar especial: a) Colar...: - Tudo: cola tudo que estiver em uma ou mais células no local desejado; - Fórmulas: cola somente fórmulas que estiverem contidas em uma ou mais células; - Valores: cola somente os valores. Se a célula copiada tiver fórmulas ou formatações, só os valores serão colados;

a

b

c

d

- Formatos: cola somente formatos existentes na célula sem copiar valores e fórmulas; - Comentários: cola somente o comentário que estiver na célula que foi copiada; - Validação: cola na área desejada, somente as opções de validação (se as células copiadas possuírem validação); - Tudo, exceto bordas: cola na área desejada, tudo com exceção das bordas.

- Larguras da coluna: cola a largura de uma coluna em outra coluna. b) Operação: pode-se também, colar a célula copiada em forma de operação: Adição, Subtração, Multiplicação e Divisão.

Ex: neste exemplo usaremos a célula “A1” com o valor 10 e “A2” com o valor 15: Se você copiar a célula “A1” e usar a opção colar especial/subtração na célula “A2” o resultado será 5, pois você estará invertendo o valor “10” para “–10”, resultando numa operação de (-10+15) que é igual a 5. c) Ignorar em branco: para evitar que células em branco copiadas substituam dados existentes, marque a caixa de seleção “Ignorar em branco”. Ao copiar um intervalo que inclui células em branco usando este procedimento, você evitará a colagem de células em branco sobre dados existentes. d) Transpor: alterna linhas de células para colunas ou colunas para linhas. Os dados na linha superior da área de cópia são exibidos na coluna esquerda da área de colagem, e os dados na coluna esquerda são exibidos na linha superior.

Selecione as células que deseja alternar e copie. Marque a caixa de seleção Transpor e clique em OK. (a área de colagem deve estar fora da área de cópia).

No exemplo, os dados encontrados no intervalo “B2:D4” foram copiados para o intervalo “B6:D8”. Além de copiados, os dados foram transpostos (os dados das linhas passaram para colunas e vice-versa).

2.1.5.

Atalhos para Preenchimento de Dados

2.1.5.1.Alça de Preenchimento Alça de Preenchimento

A alça de preenchimento é um pequeno quadrado situado no canto inferior direito da célula selecionada.

Quando posicionamos o ponteiro sobre a alça de preenchimento, o ponteiro se transforma em uma cruz preta. Podemos arrastar a alça de preenchimento para

copiar o conteúdo para células adjacentes, ou para preencher uma seqüência como, por exemplo: datas ou números.

Podemos também dar dois cliques sobre a alça de preenchimento, ao invés de arrastá-la, para que as células abaixo sejam preenchidas com os dados, sejam eles iguais ou seqüenciais. Para isso, porém, é preciso que uma coluna à direita ou à esquerda contenha dados.

2.1.5.2.Arrastar com: Botão Direito do Mouse Se você selecionar as células e, então, arrastar o retângulo de seleção utilizando o botão direito do mouse, um menu de atalho será exibido (quando o botão do mouse for liberado).

O comando Mover aqui move as células de seu local original para as células de destino.

O comando Copiar aqui copia as células selecionadas para as células de destino.

O comando Copiar aqui somente como valores, copia somente o conteúdo.

O comando Copiar aqui somente como formatos, copia todos os formatos.

Os 04 comandos Deslocar executam deslocar para baixo ou direita e copiar ou deslocar para baixo ou direita e mover.

2.1.5.3.Arrastar a Alça de Preenchimento com: Botão Direito do Mouse

Ao utilizar o botão direito do mouse para preencher um intervalo, um menu de atalho é exibido. O comando Copiar células copia as células selecionadas.

Podemos preencher uma série ou seqüência de dados, e também, preencher com ou sem formatação.

Se as células contêm datas, os comandos Preencher dias,

Preencher dias da semana (ignorando os sábados e domingos), Preencher meses ou Preencher anos ficam disponíveis, permitindo estender uma seqüência em que somente as datas são incrementadas.

Dica: Para copiar dados de uma planilha para outra, na mesma pasta de trabalho, com o auxilio do mouse, pressione as teclas “Ctrl + Alt”, arraste com o mouse até a planilha e célula desejada e então, solte o botão do mouse e a tecla “Alt”. Para mover os dados em vez de copiá-los, utilize somente a tecla “Alt”.

2.1.6.

Trabalhando com Pastas de Trabalho Vinculadas Referência a planilhas dentro da mesma pasta de trabalho: Para se referir às células em outras planilhas dentro da mesma pasta de trabalho é tão fácil quanto se referir às células na mesma planilha. Por exemplo, para se inserir uma referência à célula A9 em Plan2 na célula B10 em Plan1 devemos proceder da seguinte forma: 1. Selecione a célula B10 em Plan1 e digite um sinal de igual (=); 2. Dê um clique na guia de Plan2; 3. Dê um clique em A9 e, então, pressione a tecla Enter.

Após pressionar a tecla Enter, a planilha Plan1 é reativada e na célula B10 teremos a seguinte fórmula: “=Plan2!A9” Observe que a parte que diz respeito à planilha da referência é separada da parte da célula por um ponto de exclamação (!).

Referência a planilhas em outras pastas de trabalho (vínculos): Da mesma forma que nos referimos às células em outras planilhas dentro da mesma pasta de trabalho, também podemos nos referir a planilhas localizadas em pastas de trabalho diferentes. Por exemplo, para inserir uma referência à célula A2 em Plan2 da Pasta2 na célula A1 em Plan1 da Pasta1 devemos proceder da seguinte forma: 1. Crie uma nova pasta de trabalho (Pasta2); 2. Selecione o comando organizar no menu Janela e então selecione Vertical (ambas as pastas de trabalho aparecem, lado a lado, na sua tela); 3. Selecione a célula A1 em Plan1 da Pasta1 e digite o sinal de igual (=); 4. Dê um clique em qualquer lugar da janela da Pasta2 para ativar a pasta de trabalho; 5. Dê um clique na guia Plan2; 6. Dê um clique na célula A2 e, então, pressione a tecla Enter.

Observe que a referência da pasta de trabalho aparece primeiro na fórmula e é fechada entre colchetes e a parte que diz respeito à planilha da referência é separada da parte da célula por um ponto de exclamação (!).

Capítulo 3 3.1. Formatar Células Está opção aplica formatos às células selecionadas. Para ativar Essas opções, aponte e clique sobre o menu formatar/células ou use a tecla de atalho “Ctrl+1”.

3.1.1.

Guia Número

Na “guia Número” podemos controlar a exibição de valores numéricos e modificar a exibição de entradas de textos.

Formato Geral: aplica o formato de número padrão para a(s) célula(s) selecionada(s).

Formato número: exibe números nos formatos: inteiro, decimal fixo e com separador de milhar. Números negativos podem aparecer com um sinal de menos ou entre parênteses.

Formato moeda: usado para quantias monetárias.

Podemos escolher entre vários símbolos que representam a moeda de determinado País.

Formato contábil: alinha símbolos de moeda e vírgula decimais em uma coluna.

Formato data: aplica formatos de datas á(s) célula(s) selecionada(s).

Formato hora: aplica formatos de horas á(s) célula(s) selecionada(s).

Formato porcentagem: exibe o resultado com um símbolo de porcentagem com o número de casas decimais desejadas.

Formato fração: exibe quantias fracionárias como frações em vez valores decimais.

Formato científico: formatos científicos exibem números em anotação exponencial.

Por exemplo, 2198987654 é exibido como 9,88E+10.

Formato texto: esta categoria exibe células com formato de texto mesmo quando houver um número na célula. A célula e exibida exatamente como digitada.

Formato especial: os formatos de especial são úteis para rastrear valores de dados e listas. (CEP, telefone, CIC).

Formato personalizado Para criar um formato de número personalizado, selecionamos as células a serem formatadas. Na “guia número”, selecionamos na caixa categoria, o formato interno que se pareça com o formato que desejamos. Na caixa categoria, clique em personalizado. Na caixa Tipo, edite os códigos de formatação de número para criar a formatação que você deseja. A edição de um formato interno não causa a sua remoção.

Personalizar códigos de formato de hora, número e data: se um formato de número interno não estiver disponível para exibir os dados da maneira que desejamos, podemos criar um formato de número personalizado. Criamos formatos de números personalizados especificando os códigos de formatação que descrevam como exibir números, datas, horas ou textos.

Podemos especificar até quatro seções de códigos de formatação. As seções, separadas por ponto-e-vírgula, definem os formatos para números positivos, números negativos, valores nulos e texto, nessa ordem. Se especificarmos apenas duas seções, a primeira será usada para números positivos e nulos e a segunda, para números negativos.

Se especificarmos uma seção, todos os números usarão esse formato.

Formato de número personalizado com 04 seções: números positivos; números negativos; zeros (nulos) e; texto.

Obs. (Limite de 15 dígitos): independente do número de dígitos exibido, o Excel armazena números com até 15 dígitos de precisão. Se um número contiver mais de 15 dígitos significativos, o Excel converterá os dígitos adicionais para zeros (0).

Códigos de formatação de números básicos - “ # ”: exibe apenas os dígitos significativos e não exibe zeros não significativos.

- “ 0 ” (zero): exibe zeros não significativos se um número tiver menos dígitos do que o número de zeros no formato.

- “ ? ”: adiciona espaços para zeros não significativos em ambas as extremidades da vírgula decimal, para que as vírgulas decimais fiquem alinhadas. Além disso, use este símbolo para frações que tenham números de dígitos variáveis.

Para exibir 1234,59 como 1234,6 8,9 como 8,900 .631 como 0,6 12 como 12,0 e 1234,568 como 1234,57 44,398, 102,65, e 2,8 com vírgulas decimais alinhadas 5,25 como 5 1/4 e 5,3 como 5 3/10, com símbolos de divisão 12000como 12.000 12000 como 12

Use este código ####,# #,000 0,# #,0# ???,??? # ???/??? #.### #.

12200000 como 12,2 0,0,, Para definir a cor de uma seção do formato, digite o nome de uma das oito cores a seguir entre colchetes na seção. O código de cor deve ser o primeiro item da seção: [Preto]; [Azul]; [Ciano]; [Verde]; [Magenta]; [Vermelho]; [Branco]; [Amarelo].

Para definir os formatos de número que serão aplicados apenas se um número atender a uma condição especificada inclua a condição entre colchetes. A condição consiste em um operador de comparação e um valor. Por exemplo, o formato a seguir exibe números menores ou iguais a 100 em uma fonte vermelha e números maiores que 100 em uma fonte azul.

[Vermelho][<=100];[Azul][>100] Formato de número para datas e horas Para exibir dias, meses e anos: Para exibir Meses como 1–12 Meses como 01–12 Meses como Jan–Dez Meses como Janeiro–Dezembro Meses como a primeira letra do mês Dias como 1–31 Dias como 01–31 Dias como Dom–Sáb Dias como Domingo–Sábado Anos como 00–99 Anos como 1900–9999

Use este código m mm mmm mmmm mmmmm d dd ddd dddd aa aaaa

Para exibir horas, minutos e segundos: Para exibir Horas como 0–23 Horas como 00–23 Minutos como 0–59 Minutos como 00–59 Segundos como 0–59 Segundos como 00–59 Horas como 4 AM Hora como 4:36 PM Hora como 4:36:03 P Tempo decorrido em horas; por exemplo, 25.02 Tempo transcorrido em minutos; por exemplo, 63:46 Tempo transcorrido em segundos Frações de um segundo

Use este código h hh m mm s ss h AM/PM h:mm AM/PM h:mm:ss A/P [h]:mm [mm]:ss [ss] h:mm:ss,00

Formato de número: Porcentagem Para exibir números como uma porcentagem, inclua o sinal de porcentagem (%) no formato do número. Por exemplo, o número 0,08 aparece como 8%; 2,8 aparece como 280%.

Formato de número para texto e espaçamento Para exibir tanto caracteres de texto como números em uma célula, coloque os caracteres de texto entre aspas (" "), ou preceda um único caractere de uma barra invertida (\).

Uma seção de formato de texto, se incluída, é sempre a última seção do formato.

Podemos incluir um sinal de arroba (@) na seção em que desejamos exibir o texto inserido na célula. Se o caractere @ for omitido na seção de texto, o texto inserido não será exibido.

Se desejarmos exibir sempre os caracteres de texto específicos com o texto inserido, colocamos o texto adicional entre aspas duplas (" ") - por exemplo, "receita bruta para "@.

Se o formato não incluir uma seção de texto, o texto inserido não será afetado pelo formato.

Para criar um espaço da largura de um caractere em um formato de número, incluímos um sublinhado (_) seguido de um caractere. Por exemplo, quando colocamos um parêntese esquerdo depois de um sublinhado (_)), os números positivos são alinhados corretamente com os números negativos colocados entre parênteses.

Para repetir o próximo caractere no formato para preencher a largura da coluna, utilizamos um asterisco (*) no formato de número.

Exemplos de Formatações Personalizadas: Ex.1: [Azul]#.###,00_);[Verde](#.###,00);[Preto]0,00;[Ciano]”Receita bruta para: “@ Este exemplo formata o número digitado da seguinte maneira: - Se digitarmos o número 12 (doze) na célula formatada terá como resultado: 12,00 (na cor azul); - Se digitarmos o número -12 (menos doze) na célula formatada terá como resultado: (12,00) (entre parênteses na cor verde); - Se digitarmos o número 0 (zero) na célula formatada terá como resultado: 0,00 (na cor preta); - Se digitarmos uma palavra qualquer na célula formatada, como por exemplo Brasil: Receita bruta para: Brasil (na cor ciano).

Ex.2 (Data): "Hoje é dia "dd(dddd)," mês de "mmmm" do ano de "aaaa Este exemplo formata uma simples data (11/05/2002) e trás o seguinte resultado: - Hoje é dia 11(Sábado), mês de Maio do ano de 2002.

Ex.3 (Nº de telefone): (0##) 0###-#### - Digite o número: 04935661234 e será exibido: (049) 3566-1234.

Ex.4 (CPF): 0##\.###\.###-## - Digite o número: 74518255666 e será exibido: 745.182.556-66.

Ex.5 (CNPJ): 00\.0##\.###\/####-## - Digite o número: 82821111000172 e será exibido: 82.821.111/0001-72.

3.1.2.

Guia Alinhamento

Nesta guia, podemos configurar o alinhamento do texto digitado nas células na horizontal e na vertical.

Recuo: esta opção recua o texto das células a partir do lado esquerdo das células. Cada incremento na caixa Recuo equivale à largura de um caractere.

Orientação: girar o texto em qualquer ângulo em uma célula.

Com isso, você pode reduzir a quantidade de espaço horizontal exigido por itens de texto longos como, por exemplo, cabeçalhos, deixando mais espaço para os detalhes de seus dados.

Controle de texto: nas opções de controle de texto encontramos: - Retorno automático de texto: quebra o texto em múltiplas linhas em uma célula; - Reduzir para ajustar: reduz o tamanho aparente dos caracteres de fonte de maneira que todos os dados em uma célula selecionada caibam na coluna. O tamanho dos caracteres é ajustado automaticamente se a largura da coluna é alterada.

- Mesclar células: combina (junta) duas ou mais células selecionadas em uma única célula. A referência de célula para uma célula mesclada é a célula superior esquerda.

3.1.3.

Guia Fonte

Através da guia fonte, podemos formatar textos ou números com tipo, estilo ou tamanho de fonte. Podemos aplicar também os formatos: sublinhado, cor, tachado, sobrescrito ou subscrito ás fontes de textos ou números.

3.1.4.

Guia Borda

Nesta guia, podemos aplicar bordas às células selecionadas.

Podemos aplicar vários estilos de linhas (pontilhadas, simples, entre outras) para formar as bordas que podem ser interna (horizontal, vertical e diagonal) e externa (direita, esquerda, superior e inferior).

Para isso, selecionado clique nos diagramas de predefinições ou nos botões de borda. Pode-se também aplicar cores a estas bordas.

3.1.5.

Guia Padrões

Para aplicar um sombreado, ou seja, uma cor ao fundo das células selecionadas, basta escolhermos uma das cores.

3.1.6.

Guia Proteção

Na guia proteção, pode-se travar ou destravar as células selecionadas e ocultar ou não fórmulas.

Para que as opções travar e ocultar células funcionem, é preciso que a planilha esteja protegida.

3.2. Formatação Condicional Esta opção aplica formatos às células selecionadas que atendem a critérios específicos baseados em valores ou fórmulas que você especificar. Para aplicar esta formatação, aponte e clique no menu Formatar/Formatação Condicional.

Na formatação condicional, podemos optar em aplicar até três condições a uma ou mais células. Existem várias condições que você pode especificar e ainda, escolher um formato diferente para cada condição.

No exemplo a cima, aplicamos 03 (três) condições: - A primeira condição especifica que se o número digitado for menor do que 10, o formato aplicado à célula será cor vermelha, negrito e itálico; - A segunda condição especifica que se o número digitado for um valor entre 10 e 15, o formato aplicado à célula será cor azul, negrito e itálico; - A terceira condição especifica que se o número digitado for maior do que 15, o formato aplicado à célula, será cor verde, negrito e itálico.

Aplicar uma Formatação Condicional utilizando fórmulas.

Exemplo 1: Podemos aplicar formatação condicional através critérios estabelecidos por fórmulas.

No exemplo a seguir, da linha 04 até a linha 09, aplicamos um formato através de fórmulas que deixam as linhas em cores diferentes.

Sempre que for inserida uma linha em seu intervalo, as formas farão o trabalho de deixar uma linha na cor verde e uma na cor cinza.

Para aplicar este formato, siga as orientações a seguir:

No exemplo, utilizamos duas formas para que a formatação fosse aplicada: MOD: Retorna o resto da divisão após um número ser dividido por um divisor, no nosso exemplo, o resto da divisão por 2.

LIN: Retorna o número da linha de uma referência.

Quando o resto da divisão for igual a 0 (zero), a formatação será a “Condição 1”. Se o resto da divisão for diferente de 0 (zero), a formatação será a da “Condição 2”.

Exemplo 2: Existem muitas maneiras de utilizarmos a formatação condicional. No exemplo a seguir, temos uma planilha com dados.

Digamos que precisamos formatar as linhas de dados para que as linhas da data atual fiquem com formato diferente (destacadas).

Para isso, selecione as linhas de dados e ative a opção de Formatação Condicional.

Digite a seguinte fórmula: =SE($B8=$B$2;VERDADEIRO;FALS O)

Em seguida, clique no botão formatar a escolha o formato desejado. Clique em OK para confirmar.

Este será o resultado da formatação: A linha com data igual a data atual, será destacada com o formato escolhido.

Exemplo 3: Neste exemplo, desejamos formatar com um formato diferenciado, todos os dias que são feriados.

Nas células “H2:H8” digitamos as datas que são feriados. Em seguida nomeamos este intervalo com o nome “Feriados”.

Selecione as células “A2:F24” e ative a opção de Formatação Condicional. Digite a seguinte fórmula: =NÃO(É.NÃO.DISP(PROCV(A2;Feriados;1;FALSO))) Em seguida, clique no botão formatar a escolha o formato desejado. Clique em OK para confirmar.

Veja o resultado na figura abaixo.

Podemos aplicar outras fórmulas definindo assim, a formatação que será aplicada a(s) célula(s) selecionada(s).

Capítulo 4 4.1.

Analisando Dados

4.1.1.

Atingir Metas

Caminho: Ferramentas / Atingir Metas ...

Ajusta um valor em uma célula especifica ate que uma fórmula dependente dessa célula alcance o valor desejado.

Exemplo: Estamos à procura de uma casa e podemos pagar até R$ 1.200,00 por mês de prestação, sendo que temos R$ 25.000,00 para dar de entrada. Conseguimos obter um empréstimo a uma taxa de juros de 2,5 % a.m em 36 parcelas. A pergunta é: qual é o valor máximo que poderemos pagar pela casa?

Para responder a esta pergunta podemos utilizar a opção “Atingir Metas”. O Excel ira abrir uma caixa de diálogo que deverá preenchida: -

Definir Célula: é a referencia para identificar a célula que contém a fórmula para a qual desejamos encontrar a solução. Em nosso exemplo é a célula “B7” (Parcela Mensal);

-

Para o Valor: é o valor que desejamos alcançar.

Em nosso exemplo é o valor da parcela que podemos pagar: R$ 1.200,00;

-

Variando Célula: é a célula onde será alterado o número para chegar ao resultado esperado. Em nosso exemplo é a célula “B1” (Preço de Compra).

Depois de preencher os dados, clique em “OK”. Aparecera uma caixa de diálogo mostrando que foi “encontrada uma solução” (pode ocorrer de o calculo não chegar a 100% do valor desejado).

A figura a baixo mostra o nosso calculo resolvido e a caixa de diálogo exibindo o resultado obtido. No nosso exemplo poderíamos comprar uma casa no valor de R$ 53.268.

4.1.2.

Solver

Caminho: Ferramentas/Solver Importante: caso não apareça esta opção no Excel, vá até Ferramentas/Suplementos e o Excel exibirá uma caixa de diálogo Suplementos. Selecione na lista de suplementos e clique no item denominado “Solver”. Marque esta opção e clique no botão OK. O Excel irá instalar o suplemento e a parir de agora o comando Ferramentas/Solver estará disponível.

Com a ferramenta avançada “Solver” podemos: -

Especificar várias células ajustáveis.

-

Especificar restrições para valores que as células ajustáveis podem conter.

-

Gerar uma solução que maximize ou minimize determinada valor.

-

Podemos gerar várias soluções para um problema.

Os problemas para o “Solver” envolvem situações que satisfaçam os seguintes critérios: -

Uma célula de destino depende de outras células e fórmulas. Podemos maximizar ou minimizar o valor dessa célula ou defini-la igual a determinado valor.

-

A célula de destino depende de um grupo de célula (células variáveis) que podem ser ajustadas de modo que afetem a célula de destino.

-

A solução tem de estar de acordo com determinadas limitações ou restrições.

Depois que a planilha estiver configurada de forma adequada, poderemos usar o Solver para ajustar as células variáveis e produzir o resultado esperando na célula destino e ao mesmo tempo, atender algumas restrições que tiverem sido definidas.

Para exemplificar, vamos calcular o lucro de três produtos em uma empresa. Em uma planilha, na coluna “B”, digitamos o número de unidades de cada produto; na coluna “C”, digitamos o lucro unitário e na coluna “D” as fórmulas que calculam o lucro total de cada produto.

Não precisamos ser especialistas para ver qual produto é mais lucrativo. Se tudo fosse tão simples assim não necessitaríamos do Solver. Como acontece na maioria das situações, essa empresa apresenta algumas restrições que devem ser obedecidas. São elas: -

A capacidade de produção combinada é de 300 unidades dia;

-

A empresa precisa de 50 unidades do produto “A” para atender um pedido existente;

-

A empresa precisa de 40 unidades do produto “B” para atender a um pedido antecipado;

-

Como o mercado do produto “C” é relativamente limitado, não produzam mais de 40 unidades dia.

Essas quatro restrições tornaram o problema mais difícil de ser resolvido:

Nesse exemplo, a célula de destino é “D5” (lucro total). Digite a referência no campo “Definir Célula de Destino”.

Como o objetivo é maximizar o lucro, marque a opção igual a: “Máx”.

Especifique as células variáveis (no nosso exemplo o intervalo é “B2:B4”).

A próxima etapa é especificar as restrições do problema. Estas restrições são incluídas uma por vez e aparecem em uma caixa chamada “Submeter às Restrições”.

Para incluir as restrições clique no botão “Adicionar” ao lado da caixa “Submeter às Restrições”. Aparecera uma caixa chamada de diálogo a qual deve ser preenchida para adicionar as seguintes restrições: -

A capacidade é de 300 unidades: B5= 300.

Produzir pelo menos 50 unidades do produto “A”:B2 >= 50.

Produzir pelo menos 40 unidades do produto “B”:B3 >= 40.

Não produzir mais de 40 unidades do produto “C”:B4 <= 40.

Após ter inserido a ultima restrição clique em OK. Aparecerá a caixa de diálogo Parâmetros do Solver. Neste momento, o Solver já tem as informações necessárias. Clique em “Resolver” para dar inicio ao processo de resolução do problema. O Excel exibirá a caixa de diálogo “Resultado do Solver”. Podemos então: -

Substituir os valores das células variáveis originais pelos valores encontrados pelo Solver.

Restaurar os valores das células variáveis originais.

Criar um dos três, ou todos, os relatórios que descrevem o processo de resolução do problema realizado pelo Solver (pressione Shift para selecionar vários relatórios dessa lista).

Clicar no botão Salvar Cenário para gravar a solução como um cenário de modo que possa ser usado pelo gerenciador de cenários.

Se optarmos por qualquer opção de relatório (podem ser selecionados os três relatórios ao mesmo tempo) o Excel criara cada relatório em uma nova planilha.

Exemplo complexo do Solver:

Vamos supor que queremos encontrar alternativas para transportar materiais e, ao mesmo tempo, manter o custo total do frete o menor possível.

Uma empresa possui depósitos em Manaus, Rio de Janeiro e Foz do Iguaçu. As pontas de estoque de varejo do país fazem pedidos, que são enviados para um desses três depósitos.

Objetivo: atender as necessidades de produtos das seis pontas de estoque com os produtos disponíveis em estoque nos depósitos e, ao mesmo tempo, manter os custos totais do frete no menor valor possível.

Na tabela Custo do frete por Produto localiza-se o custo do frete por unidade de cada depósito ate a ponta de estoque do varejo.

Necessidade do produto de cada loja: Informação incluída no intervalo “C12:C17”.

Quantidade para enviar: o intervalo sombreado “D12:F17” contém as células ajustáveis (variáveis) que o Solver irá variar (definimos todas as variáveis com 25 para que o Solver tivesse um ponto de partida), a coluna “G” contém fórmulas que totalizam o número de unidades a serem enviadas para cada ponta de estoque.

Estoque Inicial do depósito: A linha “20” contém a quantidade de produtos em estoque de cada depósito. A linha 21 contém as fórmulas que subtraem a quantidade retirada do estoque (linha 18) para ser enviada.

Custo do Frete Calculado: A linha 24 contém fórmulas que calculam o frete.

• Célula D24  “=SOMARPRODUTO (D3:D8;D12:D17)”.

• Célula E24  “=SOMARPRODUTO (E3:E8;E12:E17)”.

• Célula F24  “=SOMARPRODUTO (F3:F8;F12:F17)”.

Esta fórmula calcula o custo total do frete a partir de cada depósito.

O Solver preenche os valores do intervalo “D12:F17” para que cada ponta de estoque de varejo receba o número desejado de unidades e o custo total do frete seja minimizado.

A quantidade de unidades necessárias para cada ponta de estoque de varejo deve ser igual à quantidade enviada (ou seja, todos os pedidos têm de ser atendidos).

Vejamos as restrições: 1ª Restrição: As quantidades necessárias têm que ser igual às quantidades enviadas (todos os pedidos devem ser atendidos). Portanto: C12:C17 = G12:G17.

2ª Restrição: O estoque final não pode ser negativo. Portanto: D21:F21 >= 0.

3ªRestrição: Quantidades enviadas não podem ser negativas. Portanto: D12:F17 >= 0.

Configurar o problema é à parte mais difícil quando todos os parâmetros do Solver estiverem preenchidos no botão “Resolver”.

Conforme podemos observar, na figura ao lado, o custo total ficou em: • $ 55.515,00.

4.1.3.

Cenários

O gerenciador de cenários facilita a automação dos modelos hipotéticos. Podemos armazenar conjuntos distintos de valores de entrada (células variáveis) e atribuir um nome a cada conjunto. A partir de então poderemos selecionar o conjunto de valores pelo nome. O Excel irá exibir a planilha usando aqueles valores.

Para incluir um cenário selecione Ferramentas/Cenários. Clique em “Adicionar” e será exibido a caixa de diálogo “Adicionar Cenários”.

Esta caixa é composta por quatro partes: - Nome do Cenário: Define um nome para o cenário.

- Células variáveis: Células de entrada do cenário. O número máximo de células variáveis que um cenário pode usar é 32.

Depois de definir todos os cenários e retornar a caixa de diálogo Gerenciador de Cenários, os nomes de todos os cenários definidos serão exibidos na lista. Selecione um desses cenários e clique no botão “Mostrar”. O Excel irá inserir os valores correspondentes nas células variáveis.

Clicando no botão “Editar”, podemos fazer modificações. Observe que o Excel atualiza automaticamente a caixa “Comentários” com o novo texto que indica quando o cenário foi modificado.

Com o botão “Excluir” podemos eliminar um ou mais cenários da lista.

4.1.4.

Ferramentas de Auditoria

O Excel fornece um conjunto de ferramentas de auditoria que podem ser extremamente úteis. Este conjunto de ferramentas se encontra no menu Ferramentas/Auditoria. Podemos também exibir a barra de ferramentas Auditoria. (Exibir / Barras de Ferramentas / Personalizar? Barra de Ferramentas / marque a opção Auditoria).

1. Rastrear Precedentes: Desenha setas que indicam os precedentes de células da fórmula. Para visualizar níveis adicionais de precedentes, clique várias vezes nessa ferramenta.

2. Remover Setas Precedentes: Remove o último conjunto de setas precedentes posicionado.

3. Rastrear Dependentes: Desenha setas que indicam os dependentes de célula da fórmula. Para visualizar níveis adicionais de dependentes, clique várias vezes nessa ferramenta.

4. Remover Setas Dependentes: Remove o último conjunto de setas dependentes posicionado.

5. Remover Todas as Setas: Remove todas as setas Precedentes e Dependentes numa vez só.

6. Rastrear Erro: Desenha setas até chegar à célula causadora do erro.

7. Adicionar Comentário: Exibe uma caixa de diálogo para anotar observações.

8. Circular Dados Inválidos: Usando o recurso Validação de Dados, podemos limitar dados aceitáveis inseridos em célula. Podemos fazer com que o Excel circule dados inválidos para mostrar as células com valores que não estão de acordo com as regras de validação aplicadas a elas.

9. Limpar Círculos de Validação: Para retirar o círculo de uma única célula, devemos inserir dados válidos na célula. Caso deseje limpar TODOS os círculos é só clicar no botão limpar círculos de validação.

Em nosso exemplo, uma seta rastreadora mostra que as células D9 até D11 são precedentes da célula B5, que contém a fórmula “=SOMA(D9:D11)”.

As outras setas rastreadoras mostram que as células C9, C10 e C11 são precedentes da B6, que contém a fórmula “=C9+C10+C11”. Observe que, por ser um intervalo na fórmula, “D9:D11” é rastreado como um grupo.

4.1.5.

Referências Circulares

Quando uma fórmula fizer referência à sua própria célula (direta ou indiretamente) ocorrerá uma referência circular. Em geral isso acontece devido a um erro e uma caixa de mensagem será exibida.

Uma referência circular direta é fácil de ser resolvida porque o problema está na mesma fórmula que está usando a célula resultado.

Uma referência circular indireta é mais difícil de ser resolvida, pois muitas vezes ocorre se for uma fórmula que usa outra fórmula, que pega outra fórmula e assim por diante. Neste caso a utilização da barra de ferramentas Auditoria ajudará. A célula que está provocando a referência circula é mostrada na barra de status do Excel precedida da palavra “circula”.

Caso seja intencional fazer uma fórmula circular (exemplo: uma empresa que doa 1% de seu lucro líquido, sendo que esse percentual compõe o cálculo do resultado) neste caso, deve ser mantido o botão Interação selecionado (caminho: Ferramentas / Opções / Cálculo / Marcar botão de Interação).

Na coluna “B” existe uma referência circular intencional. Na célula “B5”, temos uma fórmula que subtrai Despesas, Outros e Doações da Receita Bruta. A célula “B4” (Doações) possui uma fórmula que multiplica a célula “B5” pela célula “C4” (=1,0%). A referência circular ocorre entre as células “C4” e “C5”, pois uma depende da outra para chegar ao resultado. Com a opção Iteração selecionada, o Excel calcule a fórmula circular até chegar ao percentual mais próximo do desejado

4.1.6.

Classificar Dados em uma Tabela

Para classificar dados, selecione as células que contém os dados.

Resultado

No menu Dados clique em Classificar. Nas caixas “Classificar por”, “Em seguida por” e “E depois por”, clique nas colunas que deseja classificar.

Classificar por mais de três colunas: primeiro classifique pelas colunas menos importantes. Por exemplo, se a lista contém informações sobre funcionários e precisamos organizá-la por Departamento, Cargo, Sobrenome e Nome, podemos classificar a lista duas vezes.

4.1.7.

Filtrando Dados em uma Tabela

Esta opção exibe um subconjunto de linhas em uma lista usando filtros. Este é o meio mais rápido de selecionar apenas os itens que desejamos exibir em uma lista. Podemos aplicar filtros a apenas uma lista de uma planilha de cada vez.

Para fazer isto, clique em uma célula da lista que deseja filtrar. No menu Dados, aponte apara Filtrar e clique em Auto Filtro. Para exibir somente as linhas que contém um valor específico, clique na seta da coluna que contém os dados que deseja exibir. Clique no valor.

Para filtrar a lista por dois valores na mesma coluna, ou para aplicar outros operadores de comparação diferentes de “é igual”, clique na seta da coluna e em personalizar. Quando aplicamos um filtro a uma coluna, os únicos filtros disponíveis para outras colunas são os valores visíveis na lista filtrada. Podemos aplicar até duas condições a uma coluna com o Auto filtro.

O exemplo a seguir explica como filtrar os dados desejados e ocultar os demais.

Primeiramente aplique a opção Auto Filtro que se encontra no menu Dados.

Personalize a coluna valor. Para fazer isso, clique na seta da coluna que contém os dados que deseja exibir. Clique em personalizar.

Na caixa de diálogo que seguir, selecione é “maior do que” na opção Valor e ao lado digite o valor 130. Clique em OK.

Os dados serão personalizados e serão exibidos os valores maiores do que 130.

4.1.8.

Subtotais

O Excel pode resumir os dados automaticamente calculando valores de subtotais e totais gerais em uma lista. Para usar subtotais automáticos, a lista deve conter colunas rotuladas e deve estar classificada nas colunas para as quais desejamos os subtotais.

Quando inserimos subtotais automáticos, o Excel organiza a lista em tópicos agrupando linhas de detalhes com cada linha de subtotais associada e agrupando linhas de subtotais com a linha de totais gerais.

No exemplo ao lado, classificamos a coluna mês para que estes ficassem agrupados. Em seguida podemos aplicar subtotais à coluna VALOR a cada alteração de MÊS. Podemos escolher entre algumas funções (operações) que o Excel pode executar. Em nosso exemplo, escolhemos a função SOMA.

O Excel inclui um subtotal a cada alteração em MÊS e um total geral no final dos dados.

Pra excluir os subtotais inseridos, utilize a opção “Remover todos” na opção Subtotais.

4.1.9.

Aplicar Validação ás Células

Define os dados válidos para células individuais ou intervalo de células; restringe a entrada de dados a um tipo específico como números inteiros, números decimais ou textos; e define limites nas entradas válidas.

Na caixa Validação de dados, na opção “Permitir” escolha entre algumas opções de entrada de dados.

As células aceitam qualquer valor digitado. Podemos, porém, validar as células para permitir que sejam digitados somente alguns valores desejados.

Podemos permitir que apenas uma Lista pré-definida de dados possa ser digitada.

Podemos permitir que sejam digitados, somente alguns números inteiros, números decimais, um intervalo de datas ou horas.

Para validar um intervalo de células permitindo que sejam informadas apenas datas maiores ao dia atual e apenas datas referentes ao ano corrente, podemos proceder conforme a figura ao lado.

Se tentarmos digitar uma data que esteja fora dos limites estabelecidos, uma mensagem de erro será exibida não permitindo esta entrada.

Outra opção de validação é o “Comprimento do texto”. Um exemplo disto seria permitir que fossem digitadas apenas duas letras no caso de uma célula referente a um Estado.

Ex: SC; RS; PR ....

A opção Personalizada permite a validação de células com a utilização das fórmulas do Excel. Vejamos um exemplo: Em nosso exemplo, na coluna “C” temos os valores orçados. Na coluna “D” podemos digitar os valores realizados.

Na célula “D2” podemos aplicar a validação tipo de permissão: “Lista”. No campo “Fonte” digite alguns departamentos separados por ponto e vírgula (;) ou selecione algumas células em uma coluna que contenha os nomes dos departamentos. Nesta célula, pode-se apenas selecionar ou digitar os departamentos prédefinidos.

Vejamos um exemplo de validação de dados no tipo de permissão Personalizada: Validamos para que os gastos não ultrapassem os valores orçados de cada Item. Para esta validação, digitamos o seguinte no campo “Fórmula”: =SE($D$9<300000;VERDADEIRO;FALSO)

Obs.: Nas opções “Mensagem de entrada” e “Alerta de erro” podemos personalizar as mensagens exibidas de acordo com a validação de dados.

4.1.10.

Texto para Colunas

Para separarmos o texto de uma coluna de células para mais de uma coluna utilizamos o Assistente de conversão de texto para colunas. Este Assistente nos ajuda a dividir o texto em colunas usando um separador ou delimitador como tabulações ou vírgulas.

Para ativar esta opção selecione o menu Dados e clique em Texto para colunas.

No exemplo a seguir, temos na coluna “A” cinco linhas com número e texto digitados.

Veremos como separar esta coluna em outras três colunas.

Na etapa 1 escolha o tipo de campo que melhor descreva seus dados: -

Delimitados: Caracteres como vírgula ou tabulação que separa cada campo.

-

Largura fixa: Campos são alinhados em colunas com espaços entre cada campo.

Em nosso exemplo, escolhemos a segunda opção.

Na etapa 2 definimos as larguras dos campos. Para criar uma quebra de linha, clique na posição desejada. Para excluir a quebra de linha, clique duas vezes na linha ou arraste a seta sobre uma outra. Para mover uma quebra de linha clique na linha e arraste-a.

Na última etapa (etapa 3), podemos escolher o formato que a coluna terá (Geral, Texto, Data ou não importar coluna) e o Destino destas novas colunas.

Este será o resultado final e o texto que estava somente na coluna “A”, apresenta-se agora, nas colunas “A”, “B” e “C”.

Capítulo 5 5.1. Tabelas Dinâmicas No menu “Dados” encontramos a opção Relatório da tabela.

Uma tabela dinâmica é um tipo especial de tabela que resume informações de determinados campos em um banco de dados. Quando criamos uma tabela dinâmica, utilizamos o comando “Relatório de tabela”. O assistente da tabela dinâmica solicitará que sejam especificados quais são os campos desejados, como a tabela deve ser organizada e quais são os tipos de cálculos que desejamos que a tabela execute. Após construir a tabela, podemos reorganizá-la para visualizar seus dados. Esta habilidade de “dinamizar” as dimensões de sua tabela – por exemplo, transpor os cabeçalhos das colunas para as posições de linhas – torna a tabela dinâmica uma poderosa ferramenta analítica.

As tabelas dinâmicas são vinculadas aos dados dos quais elas derivam. Quando estes dados de origem mudam, a tabela não é automaticamente recalculada, mas podemos atualizála a qualquer momento, clicando em um botão (Atualizar dados).

5.1.1.

Criando uma Tabela Dinâmica

Podemos criar uma tabela dinâmica a partir de quatro tipos de dados: um banco de dados ou lista do Excel, uma fonte de dados externos, vários intervalos de consolidação ou outra tabela dinâmica ou gráfico dinâmico.

Podemos definir também, que tipo de relatório desejamos criar: Tabela dinâmica ou um Gráfico dinâmico (com tabela dinâmica).

5.1.1.1.

Etapa 1: Especificando o Tipo de Origem de Dados

Na etapa 1, especificamos o tipo de origem de dados no qual a tabela será baseada.

Escolhemos a opção que informa o Excel, onde estão os dados a serem analisados e o tipo de relatório a ser criado.

5.1.1.2.

Etapa 2: Indicando o local de origens

Na etapa, selecionamos o intervalo que contem os dados a serem utilizados.

Por padrão, o Excel seleciona a área em que o curso se encontra.

Recolher caixa de diálogo

Para inserir um intervalo na caixa, digitamos uma referencia ou selecionamos o intervalo diretamente da planilha. Se precisarmos mover temporariamente a caixa de diálogo para fora do caminho, selecionamos a opção “Recolher caixa de diálogo”.

5.1.1.3.

Etapa 3: Onde Colocas a Tabela Dinâmica

Nesta etapa, especifique onde o relatório deve ser colocado.

Se desejar colocar a tabela dinâmica ou gráfico dinâmico em uma planilha existente, digite ou selecione a referência de célula apenas para a célula superior esquerda do intervalo onde deseja que o relatório apareça. Caso queira que a tabela dinâmica ou gráfico dinâmico seja colocado em outra planilha, escolha a opção “Nova planilha”.

O layout do relatório pode ser criado diretamente na planilha.

Antes de clicar em “Concluir”, podemos utilizar o botão “Layout” para montar o relatório.

Arraste os nomes de campos para a área de Linha, Coluna, Dados ou Página para criar o layout de seu relatório.

O uso do campo “Dados” é o único dos campos que é obrigatório e nele devem ser colocados os itens a serem calculados. O uso dos demais campos é opcional.

Na caixa de diálogo layout podemos especificar como a tabela dinâmica será apresentada e qual tipo de informações serão exibidas no corpo da tabela. Nesta caixa de diálogo fornecemos um layout inicial à tabela. Posteriormente podemos reorganizar a tabela da forma que acharmos melhor.

No exemplo, arrastamos os nomes dos campos para as áreas desejadas: - Página: Nº item; - Linha: Data; - Coluna: Canal; - Dados: Unidades e Valor Total.

Ao retornar ao assistente na etapa 3, podemos concluir e a tabela dinâmica será exibida.

Podemos alterar a tabela dinâmica da forma que desejarmos, desde inclusão ou exclusão de campos, formatação e alteração de layout.

2 4

6 8 10 12

1 3 5 7 9 13 11

5.1.2.

Alterações na Tabela Dinâmica

Para fazermos alterações na tabela dinâmica, podemos clicar com o botão direito do mouse em qualquer área da tabela dinâmica e um menu suspenso será exibido. A partir deste menu, podemos selecionar entre as várias opções disponíveis.

1 – Formatar células: Aplica formatos às células selecionadas.

2 – Formatar relatório (Autoformatação): aplica uma combinação interna de formatos a um relatório de tabela dinâmica.

3 – Gráfico dinâmico: Cria um relatório dinâmico que reflete o modo de exibição do relatório de tabela dinâmica ativo. Quando criamos um gráfico dinâmico, o Excel cria automaticamente um relatório de gráfico dinâmico de coluna empilhada em uma nova folha de gráfico, utilizando o layout da tabela dinâmica para determinar o posicionamento dos campos.

4 – Ocultar: Exclui da tabela as células selecionadas ou a célula que contém o ponto de inserção.

5 – Assistente: Inicia o Assistente de tabela e gráfico dinâmico, permitindo modificações no relatório de tabela dinâmica ativo.

6 – Atualizar dados: atualiza os dados de uma tabela dinâmica quando os dados de origem são alterados.

7 – Selecionar: - Selecionar rótulo: seleciona apenas os rótulos quando clicamos em um rótulo de item ou campo da tabela dinâmica.

- Selecionar dados: seleciona apenas os dados associados quando clicamos em um campo ou em um rótulo de item da tabela dinâmica.

- Selecionar rótulos e dados: seleciona os dados associados junto com os rótulos quando clicamos em um rótulo de item ou campo da tabela dinâmica.

- Tabela inteira: seleciona a tabela dinâmica inteira, incluindo campos de páginas.

- Ativar seleção: ativa ou desativa a seleção estruturada da tabela dinâmica.

Quando “Ativar seleção” estiver pressionada, podemos selecionar elementos da tabela dinâmica para formatar ou aperfeiçoar fórmulas de itens calculados.

8 – Organizar estrutura de tópicos: - Ocultar detalhe: oculta os dados de detalhe exibidos.

- Mostrar detalhe: exibe todos os dados de detalhe da célula selecionada.

- Agrupar: agrupa itens em um campo para criar um único item a partir de diversos itens, por exemplo, podemos agrupar dias, semanas, meses ou outras datas.

- Desagrupar: este comando separa cada ocorrência de um grupo nos itens contidos nesse grupo, por exemplo, ele separa os trimestres nas datas individuais originais.

9 – Fórmulas: - Campo calculado: exibe uma caixa de diálogo inserir campo calculado, na qual podemos inserir um campo calculado em um relatório de tabela dinâmica ou gráfico dinâmico.

Podemos inserir fórmulas para efetuar diversos tipos de cálculos utilizando os campos da tabela.

- Item calculado: exibe uma caixa de diálogo inserir item calculado no relatório de tabela dinâmica ou gráfico dinâmico selecionado no momento.

- Ordem de resolução: exibe a ordem de resolução de cálculo atual, ou a ordem de resolução dos itens calculados para que possamos alterar a ordem de resolução.

- Listar fórmulas: exibe uma lista de fórmulas em um relatório de tabela dinâmica ou gráfico dinâmico em uma planilha separada.

10 – Ordem: - Mover para o início: move o campo de dados selecionado para o início da coluna. Esta opção está disponível somente de arrastarmos mais de um campo de dados para o diagrama da tabela dinâmica.

- Mover para cima: move o campo de dados selecionado uma posição para cima.

Esta opção está disponível somente de arrastarmos mais de um campo de dados para o diagrama da tabela dinâmica.

- Mover para baixo: move o campo de dados selecionado uma posição para baixo.

Esta opção está disponível somente de arrastarmos mais de um campo de dados para o diagrama da tabela dinâmica.

- Mover para o fim: move o campo de dados selecionado para o fim da coluna.

Esta opção está disponível somente de arrastarmos mais de um campo de dados para o diagrama da tabela dinâmica.

11 – Configuração de campo: permite alterar configurações, como subtotais, opções de classificação e layout, para o campo selecionado no relatório de tabela dinâmica ou gráfico dinâmico.

Podemos resumir dados em uma tabela dinâmica utilizando as funções: Soma, Cont.Valores, Média, Máximo, Mínimo, Mult, Cont.Núm, DesvPad, DesvPadp, Var e Varp.

Podemos também, configurar o layout dos campos, classificar por ordem crescente ou decrescente e exibir todos os itens ou parte deles na tabela dinâmica.

12 – Opções de tabela: exibe a caixa de diálogo “Opções de tabela dinâmica”, onde definimos opções avançadas de formatação, layout e gerenciamento de dados externos.

13 – Mostrar páginas: exibe cada página de um campo de página da tabela dinâmica um uma nova planilha na mesma pasta de trabalho.

Capítulo 6 6.1. Como as Funções Funcionam As funções são fórmulas pré-definidas que efetuam cálculos usando valores específicos, denominados argumentos, em uma determinada ordem ou estrutura.

Argumentos: Os argumentos podem ser números, texto, valores lógicos como VERDADEIRO ou FALSO, matrizes, valores de erro como #N/D, referencias de célula. O argumento atribuído deve produzir um valor válido para este argumento. Os argumentos também podem ser constantes, fórmulas ou outras funções.

Estrutura: a estrutura de uma função começa com o nome da função, seguido de um parêntese de abertura, os argumentos da função separados por ponto e vírgula (;) e um parêntese de fechamento. Se a função iniciar uma fórmula, digite um sinal de igual (=) antes do nome da função.

6.1.1.

Financeira

a. Função PGTO Retorna o pagamento periódico de uma anuidade de acordo com pagamentos constantes e com taxa de juros constante.

Sintaxe: PGTO(taxa;nper;vp;vf;tipo) Taxa: taxa de juros por período.

Nper: número total de pagamentos pelo empréstimo.

Vp: valor presente: o valor total presente de uma série de pagamentos futuros.

Vf: valor futuro ou saldo de caixa que se deseja obter depois do último pagamento.

Se Vf for omitido, será considerado 0 (zero).

Tipo: número 0 ou 1 indica as datas de vencimento.

Exemplo: a fórmula a seguir retorna o pagamento mensal por um empréstimo de R$ 10.000 a uma taxa anual de 8% que deve ser pago em 10 meses. =PGTO(8%/12;10;10000)  é igual a -R$1.037,03.

Para o mesmo empréstimo, se os vencimentos forem no início do período, o pagamento será: =PGTO(8%/12;10;10000;0;1)  é igual a -R$1.030,16.

b. Função TAXA

Retorna a taxa de juros de uma anuidade.

Sintaxe: TAXA(nper;pgto;vp;vf;tipo;estimativa) Nper: número total de períodos de pagamento em uma anuidade.

Pgto: pagamento feito em cada período que não pode mudar durante a vigência da anuidade. Se Pgto for omitido, deverá ser incluído o argumento Vf. Vp: valor presente: o valor total correspondente ao valor atual de uma série de pagamentos futuros.

Vf: valor futuro ou o saldo que se deseja obter depois do último pagamento. Se Vf for omitido, será considerado 0 (zero).

Tipo: pode ser 0 ou 1 e indica quando o pagamento deve ser efetuado. Utilize 0 se o prazo para o pagamento expirar no fim do período e 1 se expirar no início deste.

Estimativa: é a sua estimativa para a taxa.

Exemplo: Para calcular a taxa de um empréstimo de quatro anos de R$ 8.000 com pagamentos mensais de R$ 200. =TAXA(48;-200;8000)  é igual a 0,77%.

Esta é a taxa mensal, porque o período é anual. A taxa anual é 0,77%*12 que é igual a 9,24%. c. Função VF Retorna o valor futuro de um investimento de acordo com os pagamentos periódicos e constantes e com uma taxa de juros constante.

Sintaxe: VF(taxa;nper;pgto;vp;tipo) Taxa: taxa de juros por período.

Nper: número total de períodos de pagamento em uma anuidade.

Pgto: pagamento feito a cada período que não pode mudar durante a vigência da anuidade. Se Pgto for omitido, deverá ser incluído o argumento Vp. Vp: valor presente ou valor total correspondente ao valor presente de uma série de pagamentos futuros. Se Vp for omitido, será considerado 0 (zero) e a inclusão do argumento Pgto será obrigatória.

Tipo: pode ser 0 ou 1 e indica quando o pagamento deve ser efetuado. Utilize 0 se o prazo para o pagamento expirar no fim do período e 1 se expirar no início deste.

Exemplo: Suponha que deseja economizar dinheiro para um projeto que ocorrerá daqui um ano. Será depositado R$ 1.000 em uma conta de poupança que rende 6% de juros ao ano composto mensalmente (Juros mensais de 6%/12, ou 0,5%) é planejado um depósito de R$ no início de cada mês pelos próximos 12 meses. Quanto dinheiro teremos na conta no final dos 12 meses? =VF(6%/12;12;-100;-1000;1)  é igual a R$ 2.301,40. d. Função VP

Retorna o valor presente de um investimento. O valor presente é o valor total correspondente ao valor atual de uma série de pagamentos futuros. Quando pedimos dinheiro emprestado, o valor do empréstimo é o valor presente para quem empresta.

Sintaxe: VP(taxa;nper;pgto;vf;tipo) Taxa: taxa de juros por período.

Nper: número total de períodos de pagamento em uma anuidade.

Pgto: pagamento feito a cada período que não pode mudar durante a vigência da anuidade. Se Pgto for omitido, deverá ser incluído o argumento Vp. Vf: valor futuro ou um saldo de caixa que desejamos obter depois do último pagamento. Se Vf for omitido, será considerado 0 (zero) e a inclusão do argumento Pgto será obrigatória.

Tipo: pode ser 0 ou 1 e indica quando o pagamento deve ser efetuado. Utilize 0 se o prazo para o pagamento expirar no fim do período e 1 se expirar no início deste.

Exemplo: Suponha que desejamos comprar uma anuidade de seguros onde será pago R$ 500 ao final de cada mês pelos próximos 20 anos. O custo total será de R$ 60.000 e teremos um ganho de 9% ao ano. Queremos saber se este seria um bom investimento. =VP(0,09/12;12*20;500;;0)  é igual a -R$ 55.572,48.

O resultado é negativo porque representa o dinheiro a ser pago – um fluxo de caixa de saída. O valor presente da anuidade -R$ 55.572,48 é inferior ao que vamos pagar, que é de R$ 60.000. Portanto, concluímos que este não seria um bom investimento. e. Função NPER Retorna o número de períodos para investimento de acordo com pagamentos constantes e periódicos a uma taxa de juros constante.

Sintaxe: NPER(taxa;pgto;vp;vf;tipo) Taxa: taxa de juros por período.

Pgto: pagamento feito a cada período que não pode mudar durante a vigência da anuidade.

Vp: é o valor presente ou atual de uma série de pagamentos futuros.

Vf: valor futuro ou um saldo de caixa que desejamos obter depois do último pagamento. Se Vf for omitido, será considerado 0 (zero).

Tipo: pode ser 0 ou 1 e indica as datas de vencimento dos pagamentos.

Exemplo: =NPER(12%/12;-100;-1000;10065;1)  é igual a 60. =NPER(1%;-100;-1000;10065;1)  é igual a 60. =NPER(1%;-100;1037)  é igual a 11.

6.1.2.

Data e Hora

a. Função Agora

Retorna a data e a hora atual que está registrada no computador.

Sintaxe: Agora() b. Função Hoje Retorna o número de série da data atual.

Sintaxe: Hoje() c. Função Dia Retorna o dia do mês que corresponde o núm_série e varia de 1 a 31.

Sintaxe: Dara(núm_série) Núm_série: é o código de data-hora usado pelo Excel para os cálculos de data e hora. Podemos retornar o núm_série em forma de texto como “15-4-93” ou “15-abr93” em vez de número.

Exemplo: =Dia(“15-Abr-1993”)  é igual a 15. d. Função Mês Retorna o mês correspondente ao núm_série. O mês é fornecido com um inteiro, variando de 1 (janeiro) a 12 (dezembro).

Sintaxe: Mês(núm_série) Núm_série: idem função Dia. Exemplo: =Mês(366)  é igual a 12 (dezembro). =Mês(367)  é igual a 1 (janeiro). e. Função Ano Retorna o ano correspondente a uma data. O ano é retornado como um inteiro no intervalo de 1900-9999.

Sintaxe: Ano(núm_série) Núm_série: idem função Dia. Retornar_tipo: é um número que determina o tipo de valor de retorno.

Exemplo: =Ano(“5/7/1998”)  é igual a 1998. =Ano(“2005/05/01”)  é igual a 2005. f. Função Dia.da.semana Retorna o dia da semana correspondente ao núm_série. O dia é retornado como um inteiro variando entre 1 (domingo) e 7 (sábado).

Sintaxe: Dia.da.semana(núm_série) Núm_série: idem função Dia. Retornar_tipo: é um número que determina o tipo de valor de retorno.

Exemplo: =Dia.da.semana(“14/2/90”)  é igual a 4 (quarta-feira). g. Função DataM Retorna um número de série de data que é o número de meses indicado antes ou depois de data_inicial. Use DataM para calcular datas de quitação ou datas de vencimento que caem no mesmo dia do mês da data de emissão.

Sintaxe: DataM(data_inicial;meses) Data_inicial: é uma data que representa a data inicial.

Meses: é o número de meses antes ou depois de data_inicial. Um valor positivo para meses produz uma data futura; um valor negativo produz uma data passada.

Exemplo: =DataM(“10/08/2007”;2)  é igual a “10/10/2007”. =DataM(“10/08/2007”;-2)  é igual a “10/06/2007”.

Obs. Se esta função não estiver disponível, execute o Programa de Instalação para instalar as Ferramentas de análise. Após instalar a Ferramentas de análise, é necessário ativálas selecionando o comando Suplementos no menu Ferramentas. h. Função FimMês Retorna o número de série para o último dia do mês que é o número indicado de meses antes ou depois de data_inicial. Use FimMês para calcular as datas de vencimento que caem no último dia do mês.

Sintaxe: FimMês(data_inicial;meses) Data_inicial: é uma data que representa a data inicial.

Meses: é o número de meses antes ou depois de data_inicial. Um valor positivo para meses produz uma data futura; um valor negativo produz uma data passada.

Exemplo: =FimMês(“10/08/2007”;2)  é igual a “31/10/2007”. = FimMês(“10/08/2007”;-2)  é igual a “30/06/2007”.

Obs. Se esta função não estiver disponível, execute o Programa de Instalação para instalar as Ferramentas de análise. Após instalar a Ferramentas de análise, é necessário ativálas selecionando o comando Suplementos no menu Ferramentas.

6.1.3.

Matemática

a. Função Soma Retorna a soma de todos os números da lista de argumentos.

Sintaxe: Soma(núm1;núm2;...)

Núm1;núm2;...: são argumentos de 1 a 30 que se deseja somar.

Exemplo: =Soma(3;2)  é igual a 5.

Se o intervalo de células “A2:E2” contiver 5, 15, 30, 40 e 50 respectivamente, então: =Soma(A2:E2;15)  é igual a 155. b. Função Somase Soma as células especificadas por um determinado critério.

Sintaxe: Somase(intervalo;critérios;intervalo_soma) Intervalo: é o intervalo de células que se deseja calcular.

Critérios: são os critérios na forma de número, expressão ou texto que define quais células serão somadas.

Intervalo_soma: são as células que serão realmente somadas. As células em intervalo_soma são somadas somente se suas células correspondentes em intervalo coincidirem com os critérios estipulados.

Exemplo: suponha que o intervalo “A1:A4” contenha os seguintes valores de propriedade para 4 casas: R$100.000, R$200.000, R$300.000 e R$400.000, respectivamente. O intervalo “B1:B4” conterá as seguintes comissões de vendas em cada um dos valores de propriedade correspondentes: R$7.000, R$14.000, R$ 21.000 e R$28.000. =Somase(A1:A4;“>160000”;B1:B4)  é igual a R$63.000. c. Função SubTotal Retorna um subtotal em uma lista ou banco de dados.

Sintaxe: Subtotal(núm_função;ref1; ...)

Núm_função: é um número de 1 a 11 que determina a função a ser utilizada para o cálculo dos subtotais de uma listagem. (1: Média; 2: Cont.Núm; 3: Cont.Valores; 4: Máximo; 5: Mínimo; 6: Mult; 7: Desvpad; 8: Desvpadp; 9: Soma; 10: Var; 11 Varp).

Ref1, ...: representa de 1 a 29 intervalos ou referências cujo subtotal se deseja obter.

Se houver outros subtotais na coluna que esta sendo contada, estes serão ignorados para evitar dupla contagem. Subtotal ignora quaisquer linhas ocultas. Isto é importante quando desejamos subtotalizar apenas dados visíveis numa lista filtrada.

Exemplo: =Subtotal(9;“C3:C5”)  Gera um subtotal das células “C3:C5” usando a função Soma (núm_função = 9). d. Função Abs Retorna o valor absoluto de um número. O valor absoluto de um número é o próprio número sem o respectivo sinal.

Sintaxe: Abs(núm) Núm: é o número real do qual desejamos obter o valor absoluto.

Exemplo: =Abs(2)  é igual a 2. =Abs(-2)  é igual a 2. =Raiz(-16)  é igual a #NÚM!. =Raiz(Abs(-16))  é igual a 4. e. Função Aleatório Retorna um número aleatório maior ou igual a 0 (zero) e menor que 1 distribuído igualmente. Um novo número aleatório é retornado toda vez que a planilha for calculada.

Sintaxe: Aleatório() Exemplo: =Aleatório()  é igual a um número maior ou igual a 0 (zero) e menor que 1.

Para gerar um número aleatório entre A e B (números quaisquer): =Aleatório()*(b-a)+a Para gerar um número aleatório maior ou igual a 0 (zero) e menor que 100: =Aleatório()*100 f. Função Arred Retorna um número com uma quantidade especificada de dígitos.

Sintaxe: Arred(núm;núm_dígitos) Núm: é o número que desejamos arredondar.

Núm_dígitos: especifica o número de dígitos para o qual desejamos arredondar.

Exemplo: =Arred(2,15;1)  é igual a 2,2. =Arred(2,149;1)  é igual a 2,1. =Arred(21,5;-1)  é igual a 20. =Arred(100/3;-1)  é igual a 30. =Arred(100/3;0)  é igual a 33. =Arred(100/3;2)  é igual a 33,33.

Obs.: arredonda os número de acordo com a regra matemática. g. Função Arredondar.para.baixo Arredonda um número para baixo até zero.

Sintaxe: Arredondar.Para.Baixo(núm;núm_dígitos) Núm: é o número que desejamos arredondar.

Núm_dígitos: especifica o número de dígitos para o qual desejamos arredondar.

Exemplo:

=Arredondar.para.baixo(3,2;0)  é igual a 3. =Arredondar.para.baixo(76,9;0)  é igual a 76. =Arredondar.para.baixo(-3,1495;2)  é igual a -3,14.

Obs.: funciona como o Arred, com a diferença de arredondar para baixo. h. Função Arredondar.para.cima Arredonda um número para cima afastando de zero.

Sintaxe: Arredondar.Para.Cima(núm;núm_dígitos) Núm: é o número que desejamos arredondar.

Núm_dígitos: especifica o número de dígitos para o qual desejamos arredondar.

Exemplo: =Arredondar.para.cima(3,2;0)  é igual a 4. =Arredondar.para.cima(76,9;0)  é igual a 77. =Arredondar.para.cima(-3,1495;2)  é igual a -3,15.

Obs.: funciona como o Arred, com a diferença de arredondar para cima. i. Função Potência Fornece o resultado de um número elevado a uma potência.

Sintaxe: Potência(núm;potência) Núm: é o número base.

Potência: é o expoente para o qual a base é elevada.

Exemplo: =Potência(5;2)  é igual a 25. =Potência(9;1/2)  é igual a 3.

Obs.: O operador “^” pode substituir a função “Potência”. =5^2  é igual a 25. =9^(1/2)  é igual a 3.

6.1.4.

Estatística

a. Função Média Retorna a média aritmética dos argumentos.

Sintaxe: Média(núm1;núm2;...)

Núm1;núm2;...: são de 1 a 30 argumentos numéricos para os quais desejamos obter a média aritmética.

Exemplo: Se “A1:A5” se chamar Pontos e tiver os números 10, 7, 9, 27 e 2 respectivamente, então: =Média(A1:A5)  é igual a 11. =Média(Pontos)  é igual a 11. =Média(Pontos;5)  é igual a 10.

Se o intervalo de células “C1:C3” se chamar Pt-Pontos e contiver os números 4, 18 e 7 respectivamente, então:: =Média(Pontos;Pt-Pontos)  é igual a 10,5. b. Função Cont.Núm Calcula as células não vazias e os valores na lista de argumentos. Podemos utilizar esta função para obter o número de células quem contêm dados em um intervalo ou matriz.

Sintaxe: Cont.Núm(valor1;valor2;...)

Valor1;valor2;...: são de 1 a 30 argumentos que contêm ou se referem a uma variedade diferente de tipos de dados, mas somente os números são contados.

Os argumentos que são números, datas ou representações de texto de número são calculados, os argumentos que são valores de erro ou texto que não podem ser traduzidos em números são ignorados. Células vazias, valores lógicos, texto ou valores de erro nesta matriz ou referência são ignorados.

Exemplo: =Cont.Núm(A1:A7)  é igual a 3. =Cont.Núm(A4:A7)  é igual a 2. =Cont.Núm(A1:A7;2)  é igual a 4. c. Função Cont.Se Calcula o número de células não vazias em um intervalo que corresponde a determinados critérios.

Sintaxe: Cont.Se(intervalo;critérios) Intervalo: é o intervalo de células no qual se deseja contar células não vazias.

Critérios: é o critério na forma de um número, expressão ou texto que define quais células serão contadas. Por exemplo, os critérios podem ser expressos como 32, "32", ">32", "maçãs".

Exemplo: Suponha que A3:A6 contenha "maçãs", "laranjas", "pêras", "maçãs", respectivamente. =Cont.Se(A3:A6;“maçãs”)  é igual a 2.

Suponha que B3:B6 contenha 32, 54, 75, 86, respectivamente. =Cont.Se(B3:B6; “>55”)  é igual a 2. d. Função Cont.Valores

Calcula o número de células não vazias e os valores na lista de argumentos. Use Cont.Valores para calcular o número de células com dados em um intervalo ou matriz.

Sintaxe: Cont.Valores(valor1;valor2;...)

Valor1; valor2;...: são argumentos de 1 a 30 que representam os valores que você deseja calcular.

Exemplo: =Cont.Valores(A1:A7)  é igual a 6. =Cont.Valores(A4:A7)  é igual a 4. =Cont.Valores(A1:A7;2)  é igual a 7. e. Função Contar.Vazio Conta o número de células vazias no intervalo especificado.

Sintaxe: Contar.Vazio(intervalo) Intervalo: é o intervalo no qual se deseja contar as células em branco. f. Função Máximo Retorna o valor máximo de um conjunto de valores.

Sintaxe: Máximo(núm1;núm2;...)

Núm1;núm2;...: são 1 a 30 números cujo valor máximo desejamos localizar. g. Função Mínimo Retorna o valor mínimo de um conjunto de valores.

Sintaxe: Mínimo(núm1;núm2;...)

Núm1;núm2;...: são 1 a 30 números cujo valor mínimo desejamos localizar.

h. Função Maior Retorna o maior valor k-ésimo de um conjunto de dados. Exemplo: 5º maior número.

Sintaxe: Maior(Matriz;K) Matriz: Intervalo de dados cujo maior valor k-ésimo você deseja determinar.

K: Posição (começando do maior) no intervalo de células do valor a ser retornado. i. Função Menor Retorna o menor valor k-ésimo de um conjunto de dados. Exemplo: 5º menor número.

Sintaxe: Menor(Matriz;K)

Matriz: Intervalo de dados cujo menor valor k-ésimo você deseja determinar.

K: Posição (começando do menor) no intervalo de células do valor a ser retornado. j. Função Ordem Retorna a posição de um número em uma lista de números. A ordem de um número é seu tamanho em relação a outros valores de uma lista. (Se você fosse classificar a lista, a ordem do número seria a sua posição).

Sintaxe: Ordem(núm;ref;ordem) Núm: é o número cuja posição se deseja encontrar.

Ref: é uma matriz ou referência a uma lista de números. Valores não numéricos em ref são ignorados.

Ordem: é um número que especifica como posicionar um número em uma ordem.

Exemplo: Se “A1:A5” contiver os números 7, 4, 3, 1 e 2, respectivamente, então: =Ordem(A2:A1:A5;1)  é igual a 4. =Ordem(A1:A1:A5;1)  é igual a 5.

Exemplo: Na coluna “A” temos uma lista de produtos e na coluna “B” a quantidade vendida de cada produto. Na coluna “C” colocamos a função Ordem para mostrar a posição de cada item de acordo com a quantidade vendida.

6.1.5.

Procura e Referência

a. Função Procv Localiza um valor na primeira coluna à esquerda de uma tabela e retorna um valor na mesma linha de uma coluna especificada na tabela.

Sintaxe: Procv(valor...procurado;matriz_tabela:num_índice_coluna;procurar_intervalo) Valor_procurado: é o valor a ser procurado na primeira coluna na matriz.

Matriz_tabela: é a tabela de informações em que os dados são procurados. Use uma referência para um intervalo ou nome de intervalo, tal como Lista.

Núm_índice_coluna: é o nº da coluna em matriz_tabela a partir do qual o valor correspondente deve ser retornado. Um núm_índice_coluna de 1 retornará o valor na 1ª coluna; um núm_índice_coluna de 2 retornará o valor na 2ª coluna, e assim por diante.

Procurar_intervalo: é um valor lógico que especifica se você quer que PROCV encontre a correspondência exata ou uma correspondência aproximada. Se VERDADEIRO ou omitida, uma correspondência aproximada é retornada; em outras palavras, se uma correspondência exata não for encontrada, o valor maior mais próximo que é menor que o valor_procurado é retornado. Se FALSO, PROCV encontrará uma correspondência exata. Se nenhuma correspondência for encontrada, o valor de erro #N/D é retornado.

Exemplo: a planilha a seguir possui alguns dados. Com base nesta planilha, precisamos buscar alguns valores de acordo com a primeira coluna.

Neste outro exemplo precisamos buscar os dados de determinado funcionário. Para fazer isto, digitamos o número do funcionário e a função Procv se encarregará de trazer os dados (Nome; Cidade e Estado).

b. Função Proch Corresponde à mesma lógica da função Procv, com a diferença de que a pesquisa é feita com base nas células de cada coluna e as informações são provenientes das linhas. c. Função Col Retorna o número de coluna da referência especificada.

Sintaxe: Col(ref) Ref: é a célula ou intervalo de células cujo número da coluna desejamos obter.

Exemplos: Col(A3)  é igual a 1 (Coluna 1). d. Função Lin Retorna o número de linha da referência especificada.

Sintaxe: Lin(ref) Ref: é a célula ou intervalo de células cujo número da linha desejamos obter.

Exemplos: Lin(A3)  é igual a 3 (Linha 3).

6.1.6.

Texto

a. Função Arrumar Remove todos os espaços do texto exceto os espaços únicos entre palavras.

Sintaxe: Arrumar(texto) Texto: é o texto do qual se deseja remover espaços.

Exemplos: Arrumar(“

Primeiro Trim.

Ganhos

")  “Primeiro Trim. Ganhos”.

b. Função Concatenar Agrupa várias seqüências de caracteres de texto em uma única seqüência de caracteres.

Sintaxe: Concatenar(texto1;texto2; ...)

Texto1; texto2;...: são itens de texto a serem agrupados em um único item de texto.

O operador “&” pode ser usado no lugar de Concatenar para agrupar itens de texto.

Exemplos:

Concatenar(“Total”;“Valor")  é igual a “Valor total”. c. Função Direita Retorna uma seqüência de caracteres de tamanho especificado, extraída de outra seqüência de caracteres a partir da primeira posição à direita.

Sintaxe: Direita(texto;núm_caract) Texto: Seqüência de caracteres de texto que contém os caracteres a serem extraídos.

Núm_Caract: Especifica quantos caracteres se deseja extrair e deve ser maior ou igual a zero.

Exemplos: Direita(“Preço de Venda;5)  é igual a “Venda”.

Direita(“Número de estoque)  é igual a “e”. d. Função Esquerda Retorna uma seqüência de caracteres de tamanho especificado, extraída de outra seqüência de caracteres a partir da primeira posição à esquerda.

Sintaxe: Esquerda(texto;núm_caract) Texto: Seqüência de caracteres de texto que contém os caracteres a serem extraídos.

Núm_Caract: Especifica quantos caracteres se deseja extrair.

Exemplos: Esquerda(“Preço de Venda;5)  é igual a “Preço”. e. Função Ext.Texto Retorna caracteres de uma seqüência de caracteres, começando na posição especificada.

Sintaxe: Ext.Texto(texto;núm_inicial;núm_caract) Texto: Seqüência de caracteres de texto que contém os caracteres a serem extraídos.

Núm_inicial: Posição do primeiro caractere que desejamos extrair.

Núm_Caract: Especifica quantos caracteres se deseja extrair.

Exemplos: Ext.Texto(“Preço de Venda;1;5)  é igual a “Preço”. f. Função Núm.Caract Retorna o número de caracteres de uma seqüência de caracteres de texto.

Sintaxe: Núm.Caract(texto) Texto: texto cujo tamanho desejamos determinar. Os espaços também são contados.

Exemplos: Núm.Caract(“Rio de Janeiro, RJ)  é igual a 18.

g. Função Localizar Retorna o número do caractere no qual um caractere específico ou uma seqüência de caracteres de texto é encontrado(a) primeiro, começando com núm_inicial.

Sintaxe: Localizar(texto_procurado;no_texto;núm_inicial) Texto_procurado: texto s ser localizado. Pode-se usar caracteres curinga, ponto de interrogação e asterisco (*) em texto_procurado. Ponto de interrogação coincide com qualquer caractere único; asterisco coincide com qualquer seqüência de caracteres.

No_Texto: é o texto em que se deseja localizar o texto_procurado..

Núm_imicial: é o nº do caractere em “no_texto” em que se deseja iniciar a pesquisa.

Exemplos: Localizar(“ ”; “Rio de Janeiro”;1)  é igual a 4. =Esquerda(“São Paulo";Localizar(“ ”;“São Paulo";1))  é igual a “São”.

6.1.7.

Lógica

a. Função E Retorna VERDADEIRO se todos os argumentos forem verdadeiros e FALSO se todos os argumentos forem falsos.

Sintaxe: E(lógico1;lógico2;...) lógico1;lógico2;...: são de 1 a 30 condições que desejamos testar e que podem ser VERDADEIRO ou FALSO. Exemplos: E(2+2=4;2+3=5)  é igual a VERDADEIRO. E(30<20;20<25)  é igual a FALSO. b. Função Ou Retorna VERDADEIRO se qualquer argumentos for verdadeiro e FALSO se todos os argumentos forem falsos.

Sintaxe: OU(lógico1;lógico2;...) lógico1;lógico2;...: são de 1 a 30 condições que desejamos testar e que podem ser VERDADEIRO ou FALSO. Exemplos: E(1+1=1;2+2=2)  é igual a FALSO. E(30<20;20<25)  é igual a VERDADEIRO.

c. Função É.não.Disp Retorna o valor de erro #N/D (Não disponível).

Sintaxe: É.Não.Disp(Valor) Valor: é o valor que se deseja testar. Pode se referir a uma célula ou uma fórmula ou uma referência a uma célula.

Exemplos: É.não.Disp(#N/D)  é igual a VERDADEIRO. É.não.Disp(A1)  é igual a FALSO.

d. Função Se Retorna um valor se uma condição que foi especificada avaliar como VERDADEIRO e um outro valor se for avaliado como FALSO. Sintaxe: SE(teste_lógico;valor_se_verdadeiro;valor_se_falso) Teste_lógico: é 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 VERDADEIRA. Caso contrário, a expressão será considerada FALSA. Esse argumento pode usar qualquer operador de cálculo de comparação.

Valor_se_verdadeiro: é o valor retornado se teste_lógico for VERDADEIRO. Por exemplo, se esse argumento for à seqüência de caracteres de texto “Dentro do orçamento” e o argumento teste_lógico for considerado VERDADEIRO, a função SE exibirá o texto “Dentro do orçamento”. Se teste_lógico for VERDADEIRO e valor_se_verdadeiro for vazio, o argumento retornará 0 (zero).

Valor_se_falso: é o valor retornado se teste_lógico for FALSO. Por exemplo, se esse argumento for à seqüência de caracteres de texto “Acima do orçamento” e o argumento teste_lógico for considerado FALSO, a função SE exibirá o texto “Acima do orçamento”.

Exemplos: No exemplo a seguir, as fórmulas apresentadas na coluna “F” são as fórmulas que estão em uso na coluna “D”, que avalia se orçamento foi ultrapassado ou não.

6.1.8.

Banco de Dados

a. Função BdSoma Retorna a soma dos números no campo (coluna) de registros no banco de dados que atendam às condições especificadas.

Sintaxe: BDSOMA(banco_dados;campo;critérios) Banco_dados: é o intervalo de células que constitui a lista ou banco de dados.

Campo: é o rótulo da coluna entre aspas ou o número que representa a posição da coluna na lista.

Critérios: é o intervalo de células que contem as condições especificadas. O intervalo inclui um rótulo de coluna e uma célula abaixo do rótulo para a condição.

Obs. As demais funções de Banco de Dados possuem sintaxe idêntica à função BdSoma. Basta alterar o nome da função deixando o restante igual.

Exemplo: Nas colunas “A”, “B” e “C”, encontramos uma tabela de dados. Nas células F5 à F10, encontramos algumas funções de banco de dados que retornam um valor de acordo com sua função. Por exemplo, na célula F5, que contém a função BDSOMA, encontramos a soma dos valores da coluna “C” que resulta em 1.500.

Quando especificamos critérios, as funções de banco de dados recalculam os valores e retornam os novos resultados de acordo com estes critérios.

6.2. Fórmulas Matriciais A compreensão desse tipo de fórmula poderá abrir um novo mundo de capacidade analítica. O trabalho com matrizes (em vez de células individuais) requer um tipo diferente de abordagem.

Matriz: Coleção de células ou valores operados como grupo. A matriz pode ser armazenada nas células ou ser uma constante nomeada.

Fórmula Matricial: Fórmula que utiliza uma ou mais matrizes diretamente ou como argumento em uma função. A fórmula matricial pode ocupar uma ou mais células.

Uma matriz é uma coleção de itens. As matrizes do Excel podem ser unidimensionais ou bidimensionais. Essas dimensões referem-se a linhas e colunas.

No exemplo a seguir, o intervalo de células “C1:C5” armazena a mesma fórmula: {=A1:A5*B1:B5}. O resultado ocupa cinco células e multiplica cada elemento.

Para inserir uma fórmula de matriz, primeiro devemos selecionar o intervalo que desejamos incluir as fórmulas (ex.:”C1:C5”). Em nosso exemplo, na célula ativa “C1” será inserido a fórmula “=A1:A5*B1:B5”. Depois de criar a fórmula utilizamos a seguinte seqüência de teclas: Ctrl+Shift+ENTER (sem está seqüência o Excel não considerará uma forma matricial). A fórmula digitada agora ficará entre chaves “{ }”. Todas as células selecionadas serão preenchidas.

Se desejarmos alterar a fórmula, devemos selecionar toda a matriz e teclar “F2” ou clique com o mouse dentro da fórmula. Em uma matriz, não é possível excluir, mover ou mudar células individuais.

Embora não seja permitido alterar apenas uma parte da fórmula matricial, podemos aplicar uma formatação especifica na matriz inteira ou apenas em algumas partes dela.

As fórmulas matriciais têm as seguintes vantagens: - São mais eficientes de se trabalhar.

- Eliminam a necessidade de fórmulas intermediárias.

- Permitem a execução de operações de que de outra forma seriam mais difíceis ou quase impossíveis de serem realizadas.

- Utilizam menos memória.

As desvantagens das fórmulas matriciais: - Algumas matrizes grandes podem retardar o tempo de recálculo da planilha.

- Podem dificultar a compreensão de planilhas.

- Devemos nos lembrar de utilizar a seqüência de teclas: (Ctrl+Shift+Enter).

Exemplo 1: A planilha abaixo, à esquerda, utiliza fórmula padrão para calcular a diferença entre as notas de dois testes. A planilha à direita também calcula essas diferenças, mas utiliza uma fórmula matricial. Essa fórmula matricial reside apenas em uma célula porque o resultado é um valor único. Como vemos, não foi necessário incluir uma coluna adicional para calcular.

A fórmula usada na “Pasta 2” (à direita do exemplo) na célula “C11” é: {=média(C3:C9-B3:B9)}. Essa fórmula matricial opera em duas matrizes, subtraindo o intervalo da coluna C com o intervalo na coluna B e produz (na memória) uma nova matriz de sete elementos que armazena o resultado.

Exemplo 2: Neste exemplo, desejamos contar quantas letras possuem todos os nomes digitados na coluna “A”. Para isso, utilizamos a função “Num.Caract”, na coluna “B”, para cada nome e no final (célula “B7”), com a função soma, calculamos o total de letras dos nomes digitados.

Note que utilizamos 6 células com fórmulas para chegarmos ao resultado desejado.

Quando utilizamos matrizes, podemos simplificar isto e utilizar menos células do que utilizaríamos do modo convencional.

Note que em nosso exemplo, na célula “A7”, utilizamos 1 ao invés de 6 fórmulas e o resultado obtido foi o mesmo (utilizamos uma matriz).

Exemplo 3: Neste exemplo, encontramos dados nas colunas “B” à “G”. Para podermos fazer uma análise destes valores, podemos utilizar fórmulas matriciais como vemos a parti da coluna “J”.

Nosso objetivo é somar da base de dados (colunas “B” à “G”), os valores para cada categoria, separado ainda por mês. A fórmula matricial efetua o cálculo da seguinte forma: {=SOMA(SE($I4&J$3=$B$4:$B$20&TEXTO($C$4:$C$20;"MMM");$E$4:$E$20))}

• Esta fórmula se encontra na célula “J4”:  Item 1: O conteúdo da célula “I4” concatenado (agrupado) com conteúdo da célula “J3” que resulta em “AJAN”;  Item 2: O conteúdo das células “B4 à B20” concatenado (agrupado) com o conteúdos células “C4:C20” que resulta em “CAT + MÊS”; • Observação: Na coluna “C”, encontramos datas completas e na célula “J3” apenas o mês (JAN). Para que o Excel possa entender e comparar duas coisas iguais, ou seja, mês com mês, utilizamos a função Texto para que possa extrair da coluna “C” apenas o mês com três (3) letras (“MMM”). Assim, toda vez que o Excel encontrar nas colunas “B” concatenada com a coluna “C” (Item 2) o mesmo valor encontrado conforme Item 1, ele somará o valor da coluna “E”.  Por fim, colocamos as células (“E4 à E20”) que somam de acordo com a comparação entre o Item 1 e o Item 2.