Apostila de Excel Avancado 2007

www.dashboardnoexcel.blogspot.com

- IEL

Excel Avanado

2007

- 2 -

NDICE

Contedo

Nota ao aluno(a) .....................................................................................................- 4 -

Introduo ao Excel Avanado .............................................................................- 5 -

Conhecendo a interface do Excel 2007 ...............................................................- 6 -

Guia Incio .........................................................................................................................- 6 -

Guia Inserir .......................................................................................................................- 6 -

Guia Layout da Pgina .......................................................................................................- 7 -

Guia Frmulas ...................................................................................................................- 7 -

Guia Dados........................................................................................................................- 7 -

Guia Reviso .....................................................................................................................- 8 -

Guia Exibio ....................................................................................................................- 8 -

Destaques no menu ..........................................................................................................- 8 -

Boto Office ......................................................................................................................- 9 -

Boto Opes do Excel .................................................................................................... - 10 -

Ajuda .............................................................................................................................. - 11 -

Conhecendo outras partes da rea de trabalho................................................ - 12 -

Caixa de Nome ................................................................................................................ - 12 -

Barra de Frmulas ........................................................................................................... - 12 -

Navegador de Planilhas ................................................................................................... - 13 -

Guias das Planilhas .......................................................................................................... - 13 -

Barra de Status .............................................................................................................. - 14 -

rea de Trabalho ........................................................................................................... - 15 -

Especificaes e limites de planilha e de pasta de trabalho ........................... - 16 -

Operadores disponveis no Excel 2007 ............................................................. - 18 -

Operadores aritmticos ................................................................................................. - 18 -

Operadores de comparao .......................................................................................... - 18 -

Operador de concatenao de texto .............................................................................. - 18 -

Operadores de referncia .............................................................................................. - 19 -

A ordem em que o Excel efetua operaes em frmulas ................................ - 20 -

Funes no Excel 2007 ....................................................................................... - 21 -

Anatomia de uma funo ............................................................................................... - 21 -

- 3 -

Funes................................................................................................................. - 23 -

Data e Hora .................................................................................................................... - 23 -

O que se pode fazer com datas? .................................................................................... - 23 -

E ..................................................................................................................................... - 24 -

Ou ................................................................................................................................... - 25 -

Se ................................................................................................................................... - 25 -

Seerro ............................................................................................................................ - 27 -

Cont.se ........................................................................................................................... - 27 -

Somase ........................................................................................................................... - 28 -

Informao .................................................................................................................... - 29 -

cel.vazia ....................................................................................................................... - 30 -

ndice ............................................................................................................................. - 31 -

Procv .............................................................................................................................. - 31 -

Concatenao de textos ...................................................................................... - 32 -

& (E comercial) ........................................................................................................... - 32 -

Gerenciamento de Dados ................................................................................... - 34 -

Classificao de Dados ................................................................................................... - 34 -

Filtrar ............................................................................................................................. - 34 -

Filtro Avanado ............................................................................................................. - 35 -

Subtotal .......................................................................................................................... - 35 -

Ferramentas.......................................................................................................... - 35 -

Formatao Condicional ................................................................................................ - 35 -

Validao de dados ........................................................................................................ - 38 -

Remover Duplicatas ...................................................................................................... - 39 -

Cenrios ......................................................................................................................... - 40 -

Atingir Meta ................................................................................................................... - 41 -

Solver ............................................................................................................................. - 41 -

Controles de Formulrio ............................................................................................... - 42 -

Relatrio de Tabela e Grfico dinmico ........................................................................ - 42 -

Macros ........................................................................................................................... - 45 -

- 4 -

Nota ao aluno(a)

Essa apostila no contempla todos os recursos do Office 2007, assim dentro da possibilidade e do tempo disponvel, acompanhe novas atualizaes no blog www.dashboardnoexcel.blogspot.com. Destaques para:

Personalizar a barra de ferramentas de Acesso Rpido

Suplementos

Colar Especial

Estilos de clulas

Formatar como tabela

Ir para Especial

Inserir ilustraes

Inserir objeto

Temas

Auditoria de frmulas

Obter dados externos

Consolidar

Proteger planilha

Compartilhar pasta de trabalho

Controlar alteraes

Salvar espao de trabalho

- 5 -

Introduo ao Excel Avanado

Qual a utilidade do Excel? O Excel certamente a planilha eletrnica mais conhecida no mundo e a mais utilizada nos escritrios e em residncias. A sua aplicao destinada a:

Anlise financeira e/ou estatstica da sua empresa.

Criao de grficos baseados nas tabelas.

Listas organizadas, similares a banco de dados.

Tabelas dinmicas.

Acessar dados externos.

Automatizar rotinas macros. Exportar para outros aplicativos.

- 6 -

Conhecendo a interface do Excel 2007 A grande diferena no Excel 2007 a interface grfica. Onde antes estvamos acostumados a barras de menus, hoje apresentado como Guias e respectivas Faixas de Opes.

Ao carregar o programa Excel 2007 disponibilizada a figura abaixo que apresenta como padro as seguintes Guias: Incio, Inserir, Layout da Pgina,

Frmulas, Dados, Reviso, Exibio.

Guia Incio

Disponibiliza as seguintes faixas de opes:

rea de Transferncia

Fonte

Alinhamento

Nmero

Estilo

Clulas

Edio

Guia Inserir


Tabelas

Ilustraes

Grficos

Links

Texto

- 7 -

Guia Layout da Pgina


Temas

Configurar Pgina

Dimensionar para Ajustar

Opes de Planilha

Organizar

Guia Frmulas


Biblioteca de Funes

Nomes Definidos

Auditoria de Frmulas

Clculo

Guia Dados


Obter Dados Externos

Conexes

Classificar e Filtrar

Ferramentas de Dados

Estrutura de Tpicos

- 8 -

Guia Reviso


Reviso de Texto

Comentrios

Alteraes

Guia Exibio


Modos de Exibio de Pasta de Trabalho

Mostrar/Ocultar

Zoom

Janela

Macros

Destaques no menu

Nome da pasta e o aplicativo

em uso.

Boto Office Barra de Ferramentas de

Acesso Rpido

- 9 -

Boto Office

O boto Office acessa submenus escondidos. O nvel principal disponibiliza:

Novo o Criar uma nova pasta de trabalho em branco

Abrir o Carregar uma pasta de trabalho existente

Salvar o Salvar as alteraes da paste em uso

Salvar Como o Salvar a pasta de trabalho do Excel em outros formatos ou

simplesmente uma cpia do documento.

Imprimir o Visualizar e imprimir o documento

Preparar o Preparar o documento para distribuio

Enviar o Enviar uma cpia de documento para outras pessoas

Publicar o Distribuir documentos para outras pessoas

Fechar o Fechar a pasta de trabalho

Quando houver na faixa de opes o recurso apontado

pela seta vermelha, quer dizer que possvel acessar

diretamente sua caixa de dilogo correspondente. Basta

dar um clique duplo para abrir a respectiva caixa de

dilogo.

- 10 -

Boto Opes do Excel

Ao clicar neste boto, surge uma caixa de dilogo que permite a personalizao do ambiente do Excel 2007 (Figura 10).

- 11 -

Disponibilizado as seguintes opes da caixa de dilogo:

Mais Usados o Destinado as opes mais populares do Excel 2007

Frmulas o Altera as opes de clculo de frmulas , desempenho e

tratamento de erros do Excel 2007

Reviso de Texto o Altera a maneira como o Excel corrige e formata o texto

Salvar o Personaliza a maneira como as pastas sero salvas

Avanado o Opes avanadas para o trabalho com o Excel

Personalizar o Personaliza a barra de ferramentas de acesso rpido

Suplementos o Exibe e gerencia suplementos no Excel

Central de Confiabilidade o Ajuda a manter a segurana e a integridade do computador e dos

documentos

Recursos o Recursos online e confiabilidade do pacote Office.

Ajuda

Discretamente inserido est a Ajuda do aplicativo. Ao clicarmos neste boto apresentada a imagem abaixo. Outra alternativa para acessar esta caixa de dilogo pressionarmos a tecla F1.

- 12 -

Conhecendo outras partes da rea de trabalho

Caixa de Nome

A caixa de nome pode ser redimensionada. Para que isso ocorra, basta arrastar a barra de frmula para a direita.

Barra de Frmulas

A barra de frmulas tambm apresenta um recurso especial. Ela expande sua rea de atuao para apresentar melhor frmulas extensas. Para que isto ocorra, clique na seta para baixo. Recompor com a seta para cima.

- 13 -

Navegador de Planilhas

Na parte inferior da rea de trabalho encontramos o navegador de planilhas que funciona da seguinte forma:

Ao clicarmos neste boto, faremos um deslocamento para a primeira planilha existente esquerda, mesmo estando em uma planilha distante.

Ao clicarmos neste boto, faremos um deslocamento para a esquerda entre planilhas existentes, uma a uma.

Ao clicarmos neste boto, faremos um deslocamento para a direita entre as planilhas existentes, uma a uma

Ao clicarmos neste boto, faremos um deslocamento para a ltima planilha existente direita, mesmo estando em uma planilha distante. Outra alternativa importante que se por acaso voc tiver mais que 15 planilhas e desejar se deslocar para uma planilha especfica, dever ento, clicar sobre qualquer boto do navegador e com o boto direito do mouse, escolher entre as disponibilizadas no menu pop up. Se a planilha desejada no estiver aparecendo, clique em Mais planilhas e uma caixa de dilogo com

barra de rolamento disponibilizada para percorrer at encontrar a planilha.

Guias das Planilhas Ao lado do navegador, destacam-se as guias das planilhas. A configurao padro apresenta 3 guias, mas se desejarmos alterar esse valor deve-se levar em considerao seu limite mximo de 255 pastas.

- 14 -

Acesse as opes do Excel e configure de acordo com a sua necessidade. Nota: O bom profissional tem que saber o seguinte: planilhas vazias ocupam

espao em kbytes. Se voc s precisa de uma guia, exclua as duas restantes e economize espao de armazenamento. Lembre-se: uma planilha armazenada no seu disco rgido com planilhas vazias pode no fazer diferena, mas 1000, faz muita diferena.

Barra de Status

a barra inferior que disponibiliza as seguintes opes:

Informao do status

Boto de gravao de macros

Modos de exibio: Normal, Layout da Pgina e Visualizao de Quebra de Pgina

Nvel de Zoom (percentual) com uma barra deslizante

- 15 -

rea de Trabalho

A rea de trabalho permaneceu sem alteraes. Uma folha quadriculada preenche todo o espao com novas dimenses. Quem reclamava das dimenses das planilhas do Excel 2003 vai ter espao suficiente na nova verso. O nmero de colunas saltou de 256 para 16.384, e o de linhas passou de 65.536 para 1.048.576. A largura da coluna comporta 255 caracteres, e a altura da linha vai a 409 pontos. As quebras de pgina possveis somam 1.026 na horizontal e na vertical, e cada clula pode conter um total de 32.767 caracteres. Conheam na ntegra os novos limites do Excel 2007 na tabela abaixo.

- 16 -

Especificaes e limites de planilha e de pasta de

trabalho

Recurso Limite mximo

Pastas de trabalho abertas Limitado pela memria disponvel e pelos recursos do sistema

Tamanho da planilha 1.048.576 linhas por 16.384 colunas

Largura da coluna 255 caracteres

Altura da linha 409 pontos

Quebras de pgina 1.026 na horizontal e na vertical

Nmero total de caracteres que uma clula pode conter

32.767 caracteres

Caracteres em um cabealho ou rodap 255

Planilhas em uma pasta de trabalho

Limitado pela memria disponvel e pelos recursos do sistema (o padro 3 folhas).

Cores em uma pasta de trabalho

16 milhes de cores (32 bits com acesso total ao espectro de cores de 24 bits)

Formatos/estilos de clula exclusivos 64.000

Estilos de preenchimento 32

Espessura e estilos de linha 16

Tipos exclusivos de fonte 1.024 fontes globais disponveis para uso; 512 por pasta de trabalho

Formatos de nmero em uma pasta de trabalho Entre 200 e 250, dependendo da verso do idioma do Excel instalado

Nomes em uma pasta de trabalho Limitado pela memria disponvel

Janelas em uma pasta de trabalho Limitado pela memria disponvel

- 17 -

Recurso Limite mximo

Painis em uma janela 4

Planilhas vinculadas Limitado pela memria disponvel

Cenrios (cenrio: um conjunto nomeado de valores de entrada que voc pode substituir em um modelo de planilha.)

Limitado pela memria disponvel; um relatrio resumido mostra somente os primeiro 251 cenrios.

Clulas variveis em um cenrio 32

Clulas ajustveis no suplemento Solver 200

Funes personalizadas Limitado pela memria disponvel

Intervalo de zoom 10 por cento a 400 por cento

Relatrios Limitado pela memria disponvel

Referncias classificadas

64 em uma nica classificao; ilimitado quando usar classificaes seqenciais

Nveis de desfazer 100

Campos em um formulrio de dados 32

Parmetros de pasta de trabalho 255 parmetros por pasta de trabalho

Listas suspensas de filtro 10.000

- 18 -

Operadores disponveis no Excel 2007

Operadores aritmticos

Operador aritmtico Significado Exemplo

+ (sinal de mais) Adio 3+3

(sinal de menos) Subtrao Negao

31 1

* (asterisco) Multiplicao 3*3

/ (sinal de diviso) Diviso 3/3

% (sinal de porcentagem) Porcentagem 20%

^ (acento circunflexo) Exponenciao 3^2

Operadores de comparao

Operador de comparao Significado Exemplo

= (sinal de igual) Igual a A1=B1

> (sinal de maior que) Maior que A1>B1

< (sinal de menor que) Menor que A1= (sinal de maior ou igual a) Maior ou igual a A1>B1

- 19 -

contnuo

Operadores de referncia

Operador de referncia Significado Exemplo

: (dois-pontos)

Operador de intervalo, que produz uma referncia para todas as clulas entre duas referncias, incluindo as duas referncias

B5:B15

, (vrgula) Operador de unio, que combina diversas referncias em uma referncia

SOMA(B5:B15,D5:D15)

(espao) Operador de interseo, que produz uma referncia a clulas comuns a duas referncias

B7:D7 C6:C8

- 20 -

A ordem em que o Excel efetua operaes em frmulas

Precedncia de operadores

Quando houver operadores iguais em um mesma sentena, o Excel avaliar os

operadores da esquerda para a direita.

Operador Descrio

: (dois-pontos)

(espao simples)

, (vrgula)

Operadores de referncia

Negao (como em 1)

% Porcentagem

^ Exponenciao

* e / Multiplicao e diviso

+ e Adio e subtrao

& Conecta duas seqncias de texto (concatenao)

= < > =

Comparao

- 21 -

Funes no Excel 2007 O Excel 2007 oferece 340 funes distribudas por categorias conforme abaixo:

Financeira (53)

Data e Hora (20)

Matemtica e Trigonometria (60)

Estatstica (83)

Procura e Referncia (18)

Banco de Dados (12)

Texto (24)

Lgica (7)

Informaes (17)

Engenharia (39)

Cubo (7)

Anatomia de uma funo A estrutura de uma funo dever ter:

- 22 -

Os argumentos variam de funo para funo. Um argumento pode ser:

Nmeros

Textos

Valores Lgicos

Valores de erro

Referncias

Matrizes Para fazer referncia a faixa de clulas (intervalos) utilize os dois pontos para informar o incio e fim do intervalo de dados. Ex: Cont.valores(A1:A1000) Quando uma funo usar mais de um argumento, devemos recorrer ao ponto e vrgula. Ex: Soma(A1:A10; B1:B10)

- 23 -

Funes

Data e Hora Para o Excel, datas e horas so consideradas valores numricos. Voc dispe de diversos formatos para digit-los, conforme mostra a Tabela a seguir.

Se voc entrar com os dados O Excel adotar o formato

3/4/95 d/m/aa

4-Mar-95 d-mmm-aa

4-Mar d-mmm

Mar-95 mmm-aa

8:50 PM h:mm AM/PM

8:50:35 PM h:mm:ss AM/PM

20:50 h:mm

3/4/95 20:50 m/d/yy h:mm

O que se pode fazer com datas? No exemplo abaixo, demonstramos a possibilidade de gerar um relatrio especfico apenas sobre as datas existentes na tabela.

Funes utilizadas no relatrio. =DIA.DA.SEMANA(B2;2) =DIA(B2) =MS(B2) =ANO(B2) =NMSEMANA(B2) =DIATRABALHOTOTAL(B2;C2) =DIAS360(B2;C2) =DIA.DA.SEMANA(B2;2) Formatado como dddd

- 24 -

Como calcular os dias trabalhados utilizando o recurso com datas?

E F G H

10 COLABORADOR DATA ADM. DATA DEM. DIAS TRAB.

11 CARLA 15/6/2006 29/6/2006

12 CLAUDIA 5/6/2006 10/7/2006

13 LUCIENE 10/6/2006 8/7/2006

E Verifica se todos os argumentos so verdadeiros e retorna verdadeiro. Caso algum argumento seja falso, retorna falso.

Sintaxe: E(lgico1;lgico2;...)

Logical1,logical2,... so de uma a 255 condies que voc deseja testar e que

podem resultar em VERDADEIRO ou FALSO.

Como verificar se a descrio na clula A15 Notebook e se a quantidade vendida na clula C15 maior que 10. =E(A15=NOTEBOOK;C15>10) O retorno VERDADEIRO devido as duas condies serem verdadeiras.

- 25 -

Ou Verifica se algum argumento verdadeiro e retorna verdadeiro. Retorna falso se todos os argumentos forem falsos.

Sintaxe: OU(lgico1;lgico2;...)

Logical1,logical2,... so de uma a 255 condies que voc deseja testar e que

podem resultar em VERDADEIRO ou FALSO. Como verificar se a descrio na clula A15 Notebook e se na clula C15 a quantidade vendida igual a 10. =OU(A15=NOTEBOOK;C15=10) O retorno VERDADEIRO devido a primeira condio ser verdadeira.

Se Efetua um teste lgico em valores e frmulas e retorna como verdadeiro ou falso. Sintaxe: SE(teste_lgico; valor_se_verdadeiro; valor_se_falso)

Teste_lgico, qualquer valor ou expresso que pode ser avaliada como

VERDADEIRO ou FALSO.

Usando o exerccio abaixo possvel aplicar uma frmula na clula A18 para avaliar a situao das mquinas. Nesse caso, utilizamos a funo Se que verifica a quantidade de erros encontrados e caso seja maior ou igual a 5 apresenta a informao Manuteno na mquina. Caso contrrio, permanece sem informao.

- 26 -

=SE(A3>=5;"Manuteno na mquina";"") A funo SE permite apenas 7 encadeamentos, porm, existe uma tcnica para burlar esta condio: Dever ser preenchido na ltima condio um deslocamento para uma nova srie de encadeamentos. Assim, no h mais limite e pode ser aplicado em banco de dados com altos volumes de informaes.

- 27 -

Seerro Retorna valor_se_erro se a expresso for um erro. Caso contrrio, retorna o valor da expresso. Sintaxe: SEERRO(valor,valor_se_erro)

Valor o argumento verificado quanto ao erro.

Valor_se_erro o valor a ser retornado se a frmula gerar um erro. Os

seguintes tipos de erro so avaliados #N/D, #VALOR!, #REF!, #DIV/0!, #NM!,

#NOME? ou #NULO!).

No exemplo acima, foi aplicado a frmula =SEERRO(B8/B7;0) na clula B9 para evitar que apresentasse um erro como resultado.

Cont.se Calcula o nmero de clulas no vazias em um intervalo que corresponde a determinados critrios. Sintaxe: CONT.SE(intervalo; critrios)

Intervalo o intervalo de clulas no qual se deseja contar, incluindo nmeros

ou nomes, matrizes ou referncias que contm nmeros. Os campos em

branco e valores de texto so ignorados.

Critrios o critrio na forma de um nmero, expresso ou texto que define

quais clulas sero contadas.

Usando o exerccio anterior, aplicamos a funo Cont.Se na clula A2 para verificar a quantidade de erros no intervalo B8:C16 usando o critrio determinado na clula A5.

- 28 -

=CONT.SE(B8:C16;A5) O Excel 2007 disponibiliza tambm a alternativa Cont.ses para solucionar situaes que exija mais critrios. Pea ao seu professor que faa simulaes com essa nova funo.

Somase Adiciona as clulas especificadas por um determinado critrio. Sintaxe: SOMASE(intervalo;critrios;intervalo_soma) Intervalo o intervalo de clulas que se deseja calcular por critrios. As

clulas em cada intervalo devero ser nmeros e nomes, matrizes ou

referncias que contm nmeros. Os espaos em branco e os valores de texto

so ignorados.

Critrios so os critrios na forma de um nmero, expresso ou texto que

define quais clulas sero adicionadas.

Intervalo_soma so as clulas reais a serem adicionadas se as clulas

correspondentes no intervalo coincidirem com os critrios. Se intervalo_soma

for omitido, as clulas no intervalo sero avaliadas pelos critrios e adicionadas

se corresponderem aos mesmos.

- 29 -

=SOMASE(B3:B5;">30000";C3:C5) O Excel 2007 disponibiliza tambm a alternativa Somases para solucionar situaes que exija mais critrios. Pea ao seu professor que faa simulaes com essa nova funo.

Informao Retorna informaes sobre o ambiente operacional atual Sintaxe: INFORMAO(tipo_texto)

Tipo_texto o texto que especifica o tipo de informao a ser retornado.

Pouco aproveitada por profissionais, a funo Informao tem grande importncia para o gerenciamento de planilhas, principalmente, quando se trata de manipular vrias planilhas ao mesmo tempo. Usando a sintaxe abaixo possvel detectar a quantidade de planilhas abertas. =INFORMAO("nmarquivo")

Se algumas forem fechadas enquanto est trabalhando com a planilha principal onde est armazenada a funo, ser preciso recalcular novamente usando a tecla F9. Uma variao pode ser acrescentada para informar o local de armazenagem desta planilha, use ento: =INFORMAO("diretrio")

- 30 -

cel.vazia Verifica se uma condio foi satisfeita e retorna um valor se for verdadeiro ou outro valor se for falso. Sintaxe: SE(teste_lgico;valor_se_verdadeiro;valor_se_falso) Teste_lgico qualquer valor ou expresso que possa ser avaliado como VERDADEIRO ou FALSO. Valor_se_verdadeiro o valor retornado se teste_lgico for VERDADEIRO. Valor_se_falso o valor retornado se teste_lgico for FALSO. Combinada com a funo SE a sua aplicao fica mais interessante. No exemplo abaixo, existe uma tela de cadastro a ser preenchida. Enquanto no for digitada nenhuma informao na lacuna nome a mensagem de alerta no ser removida.

Foi usada a frmula: =SE(CL.VAZIA(D8);"Digite seu nome";"")

- 31 -

ndice Retorna um valor ou a referncia para um valor de dentro de uma tabela ou intervalo. Sintaxe: NDICE(matriz,nm_linha,nm_coluna)

Matriz um intervalo de clulas ou uma constante de matriz. Se a matriz contiver apenas uma linha ou coluna, o argumento nm_linha ou nm_coluna correspondente opcional. Se a matriz tiver mais de uma linha e mais de uma coluna e apenas nm_linha ou nm_coluna for usado, NDICE retornar uma matriz referente linha ou coluna inteira da matriz. Nm_linha seleciona a linha na matriz a partir da qual um valor dever ser retornado. Se nm_linha for omitido, nm_coluna obrigatrio. Nm_coluna seleciona a coluna na matriz a partir da qual um valor dever ser retornado. Se nm_coluna for omitido, nm_linha obrigatrio.

Qual o produto na clula A6? =NDICE(A5:A8;2;1) Vamos abordar melhor esta funo em conjunto com controles de formulrios.

Procv Localiza um valor na primeira coluna de uma matriz de tabela e retorna um valor na mesma linha de outra coluna na matriz da tabela. Sintaxe: PROCV(valor_procurado;matriz_tabela;nm_ndice_coluna;procurar_intervalo) Valor_procurado: O valor a ser procurado na primeira coluna da matriz da tabela. O Valor_procurado pode ser um valor ou uma referncia. Matriz_tabela: Duas ou mais colunas de dados. Use uma referncia para um intervalo ou um nome de intervalo. O uso do Procv fica mais interessante se combinado a uma validao tipo lista, conforme exemplo abaixo.

- 32 -

Na clula C4 do Relatrio h uma validao de dados tipo lista para resgatar informaes da primeira coluna da tabela Dados dos Clientes.

Para preencher os campos subseqentes (Nome do cliente, Endereo, Cidade, Telefone) aplicamos o PROCV. =PROCV(C4;J5:N11;2;FALSO) A cada mudana do cdigo do cliente a informao correspondente apresentada nos campos subseqentes acima mencionados. O Excel 2007 disponibiliza tambm a alternativa Proch para solucionar situaes nas tabelas no sentido horizontal. Pea ao seu professor que faa simulaes com essa funo.

Concatenao de textos

& (E comercial) A concatenao de textos com esse smbolo tem muita utilidade para solucionar situaes adversas. Combinada ento com a funo PROCV o resultado dinmico.

- 33 -

Problema:

Como elaborar uma frmula que apresente como resultado a quantidade e medida correspondente de cada produto. Incluir nessa frmula uma condio para quando a quantidade for maior que uma unidade mostrar a medida no plural.

A B

2

3 cx Caixa4 dz Dzia5 kg Quilo6 lt Lata7 pct Pacote8 sc Saco9 un Unidade

A B C D

12 Qtd Unidade ProdutoQuantidade

e Medida

13 1 kg Arroz 1 Quilo14 2 cx Leite 2 Caixas15 3 sc Farinha 3 Sacos16 4 pct Macarro 4 Pacotes

Unidades de Medida

Soluo: =B13&" "&PROCV(C13;$B$3:$C$9;2;FALSO)&SE(B13>1;"s";"")

- 34 -

Gerenciamento de Dados O Excel oferece algumas ferramentas essenciais para gerenciamento de dados.

Classificao de Dados Organiza a ordem dos dados da tabela com base nos critrios definidos pelo usurio. Por exemplo, temos a tabela abaixo e precisamos classific-la pela Diria simples a completa.

Selecione qualquer clula da tabela e clique na guia Dados, faixa de opo Classificar e Filtrar. Clique na opo Classificar. Na caixa de dilogo, opte por Classificar por Diria. Na ordem, Z-A.

Filtrar Apresenta somente as informaes desejadas com base em critrios definidos pelo usurio. No nosso exemplo, filtraremos as informaes que apresente somente hotis de cinco estrelas. Clique na guia Dados, na faixa de opes Classificar e Filtrar e na opo Filtro. Clique na caixa de listagem do campo N de estrelas e marque apenas

a opo 5.

NOME DO HOTEL ENDEREO CIDADE ESTADO N DE ESTRELASN DE

QUARTOS

PREO

MNIMO

PREO

MXIMODIRIA

Hotel Jundia Av. Carlos Guimares, 4689 Jundia SP 5 157 22,00 135,00 Completa

La Bell Veronique Av. Pajuara, 345 Macei AL 5 100 10,00 100,00 Completa

Othon Palace Hotel Av. Ipanema, 2093 Rio de Janeiro RJ 5 102 44,00 96,00 Completa

Pousada Tia Maria Rua Heitor Garcia, 349 Belo Horizonte MG 5 175 24,00 88,00 Completa

Para retornar todos os dados da tabela, repita o processo anterior e opte por Selecionar tudo. Para remover o filtro, clique na opo Filtro da faixa de opes Classificar e Filtrar.

- 35 -

Filtro Avanado Um pouco mais complexo, exige pelo menos a definio de 2 campos cruciais: Intervalo da Lista e Intervalo de Critrios. Selecione os ttulos da tabela e copie para qualquer clula vazia. Insira um critrio abaixo do campo Diria, como por exemplo, Simples. Selecione qualquer clula da tabela e clique na guia Dados. Use a faixa de opes Classificar e Filtrar e clique em Avanado. Com o intervalo da lista j

definido, defina o intervalo de critrios, selecionando a cpia dos ttulos e a linha logo abaixo. Para retornar todas as informaes da tabela, clique em Limpar na faixa de opes Classificar e Filtrar.

Subtotal Apresenta um resumo de campos definido pelo usurio. Esse resumo pode ser configurado com as funes:

Soma,

Contnm,

Mdia,

Mx,

Mnimo,

Produto. Aproveitando o exerccio anterior, selecione qualquer clula na tabela e clique na guia Dados, faixa de opes Estrutura de tpicos, opo Subtotal. Configure com: A cada alterao em Nome do Hotel, usar a funo Contnm, adicionar subtotal a Diria.

Ferramentas O Excel oferece algumas ferramentas essenciais para restrio de clulas, formataes condicionais, entre outros. Essas ferramentas quando aplicadas em combinao com as funes e frmulas, fornece solues para problemas complexos.

Formatao Condicional A verso 2007 apresenta opes para: - Realar regras das clulas; - Regras de primeiros/ltimos; - Barras de dados; - Escala de cores; - Conjunto de cones.

- 36 -

Realar clulas Criar uma formatao condicional que apresente o preenchimento da clula azul quando encontrar o aluno Joo. Selecione os alunos e clique na guia Incio, use a faixa de opes Estilo e a opo Formatao Condicional. Escolha Realar regra das clulas \ Texto que contm e preencha com Joo. Em Formato Personalizado e na aba Preenchimento escolha qualquer tonalidade de azul e Ok.

Aluno Joo

Aluno Departamento Nota

Joo Cincias 7,5

Paulo Matemtica 8,0

Luiz Lnguas 4,5

Joo Histria 9,0

Laura Literatura 6,5

Karla Geografia 10,0

Simone Matemtica 9,0

Pedro Lnguas 7,5

Joo Literatura 8,0

Vera Histria 5,0

Criar uma formatao condicional que apresente o preenchimento da clula azul quando encontrar

o aluno Joo.

Regras de primeiros/ltimos Criar uma formatao condicional que apresente o preenchimento da clula vermelho para as trs maiores notas da lista. Selecione as notas e clique na guia Incio, use a faixa de opes Estilo e a opo Formatao Condicional. Escolha Regras de primeiros / ltimos e clique em Mais regras. Na descrio da regra altere o valor de 10 para 3. Clique no boto Formatar e na aba Preenchimento escolha qualquer tonalidade de vermelho e Ok. Aluno Departamento Nota

Joo Cincias 7,5

Paulo Matemtica 8,0

Luiz Lnguas 4,5

Joo Histria 9,0

Laura Literatura 6,5

Karla Geografia 10,0

Simone Matemtica 9,0

Pedro Lnguas 7,5

Joo Literatura 8,0

Vera Histria 5,0

Criar uma formatao condicional que apresente o preenchimento da clula vermelho para as trs

maiores notas da lista.

- 37 -

Barra de dados Criar uma formatao condicional que simule grficos de barra nas clulas, onde os maiores valores devero apresentar barras maiores e os menores valores barras menores. Selecione as unidades e clique na guia Incio, use a faixa de opes Estilo e a opo Formatao Condicional. Escolha Barra de Dados e escolha o

primeiro modelo de barras em azul.

Nome do Produto Unidades Total (R$)Northwind Traders Almonds 20 200,00

Northwind Traders Beer 487 6.818,00

Northwind Traders Boysenberry Spread 100 2.500,00

Northwind Traders Cajun Seasoning 40 880,00

Northwind Traders Chai 40 720,00

Northwind Traders Chocolate 200 2.550,00

Northwind Traders Chocolate Biscuits Mix 85 782,00

Northwind Traders Clam Chowder 290 2.798,50

Northwind Traders Coffee 650 29.900,00

Northwind Traders Crab Meat 120 2.208,00

Northwind Traders Curry Sauce 65 2.600,00

Northwind Traders Dried Apples 40 2.120,00

Northwind Traders Dried Pears 40 1.200,00

Northwind Traders Dried Plums 75 262,50

Northwind Traders Fruit Cocktail 40 1.560,00

Northwind Traders Gnocchi 10 380,00

Northwind Traders Green Tea 275 822,25

Northwind Traders Long Grain Rice 40 280,00

Northwind Traders Marmalade 40 3.240,00

Northwind Traders Mozzarella 90 3.132,00

Northwind Traders Olive Oil 25 533,75

Northwind Traders Ravioli 100 1.950,00

Northwind Traders Scones 20 200,00

Northwind Traders Syrup 50 500,00

Solicite ao seu professor que demonstre as outras opes de formataes condicionais.

- 38 -

Validao de dados Permite restringir clulas conforme configurao personalizada. Selecione a clula a sofrer a restrio e na guia Dados, faixa de opes Ferramenta de Dados e escolha a opo Validao de Dados. Na aba Configuraes preencha conforme imagem abaixo. No nosso exemplo, a

lacuna nome a ser preenchida no poder ultrapassar o limite mximo de 50 caracteres.

Na aba Alerta de erro preencha com a mensagem Clula restrita somente a 50 caracteres. Se a regra for quebrada uma mensagem dever alertar o usurio, conforme imagem abaixo.

A validao tambm pode permitir uma lista de opes para uma clula. Continuando nosso exemplo acima, selecionamos a clula a ser preenchida com o sexo e aplicamos a validao tipo lista. A fonte pode estar na mesma planilha ou no. A nossa fonte localizada no intervalo N12:N13 informada. O resultado na clula:

- 39 -

Remover Duplicatas Novidade na verso 2007, essa ferramenta altamente destrutiva. Quando voc desejar remover informaes em duplicidade dentro de uma tabela use-a com cautela. Na tabela abaixo, precisamos remover duplicidades existentes. Observe que existe apenas dois registros semelhantes no quarto e nono registros.

Vendedor Produto

Denise Mouse PS/2

Douglas Notebook

Eduardo Cooler

Eliane HD

Flvio Cooler

Denise Memria

Douglas Pendrive

Eduardo Monitor

Eliane HD

Flvio Memria

Denise Scanner

Douglas Impressora

Eduardo Impressora

Eliane Computador

Flvio Computador

Denise Pendrive Selecione a tabela e na guia Dados, na faixa de opes Ferramentas de dados use a opo Remover Duplicatas. As colunas vendedor e produtos sero automaticamente selecionadas na caixa de dilogo. Pressione o boto Ok e um dos registros ser removido.

- 40 -

Cenrios um conjunto de valores que o Excel salva e pode substituir automaticamente na planilha. Os cenrios so usados para prever resultados de um modelo de planilha, onde voc pode criar e salvar diferentes grupos de valores. Depois de criados possvel alternar para qualquer desses valores e exibir como relatrios finais. Para gerar os cenrios necessrio carregar o Gerenciador de cenrios, que o administrador desta ferramenta. Com um exemplo fica mais fcil de explicar esta ferramenta. Temos duas situaes provveis que poder ocorrer neste ano. A primeira a situao normal e a segunda a situao acima do esperado com aumentos exorbitantes em algumas reas.

Clique na guia Dados, na faixa de opes Teste de Hipteses e escolha Gerenciador de Cenrios. Na caixa de dilogo, clique em Adicionar e digite o nome do cenrio como Normal.Em clulas variveis, selecione o intervalo $D$3:$D$6 e pressione o boto Ok. Aceite os valores apresentado e pressione Adicionar. No novo cenrio digite Exagerado. Substitua os valores por 25; 2,5; 35 e 5 e clique em OK. Para ver a projeo com os valores exagerados, selecione o cenrio Exagerado e clique em Mostrar. Apresentamos o aumento do Exagerado sobre o Normal.

Normal 19.250

Exagerado 31.500

Dif % 163,64

- 41 -

Atingir Meta Trata-se de uma ferramenta que auxilia a obteno de solues de problemas matemticos numa planilha, baseadas em equaes ou inequaes lineares ou no, resolvendo problemas que possuam mais de uma resposta. Uma empresa est oferecendo um veculo por R$23.000 em 36 parcelas fixas de R$661,25 com juros mensais de 3,50 a.m. Um comprador fz seus clculos e infelizmente s poder dispor de uma mensalidade de apenas R$500,00. Qual o valor do bem que ele pode adquirir com o mesmo nmero de parcelas e juros mensais?

Para encontrar o valor do veculo que poder ser financiado, clique na guia Dados, faixa de opes Teste de Hiptese e Atingir Meta. Na caixa de dilogo configure definir clula (mensalidade), Para o valor (500,00), Alternando clula (Valor do veculo). O valor final ser de R$17.391,00.

Solver uma ferramenta semelhante ao Atingir Meta, porm mais poderosa. Vejamos um exemplo:

Um cliente adquiriu um computador Pentium IV dos Estados Unidos no valor unitrio de R$5.000,00. Revende o equipamento com 15% sobre o valor adquirido. Qual ser o lucro se revender o equipamento por R$6.500,00?

Na guia Dados escolha a faixa de opes Anlise. Clique sobre Solver e

configure a caixa de dilogo com: - Definir clula de destino = B11; - Valor de = 6500; - Clulas variveis = B10; - Submeter s restries = B11

- 42 -

Controles de Formulrio

So recursos especiais para montar formulrios eletrnicos.

Para ter esses recursos, primeiro necessrio ativar a guia Desenvolvedor. Clique no boto Office e Opes do Excel. Em Mais usados marque a opo Mostrar guia desenvolvedor na faixa de opes.

Cargo

Cargo Sexo Estado

Sexo Assistente M Amazonas

Secretria F So Paulo

Estado Rio de Janeiro

Selecione a lacuna a ser preenchida pelo cargo e clique na guia Desenvolvedor. Na faixa de opes Controles, clique em Inserir. Clique sobre o segundo boto (da esquerda para a direita) Caixa de combinao e desenhe um retngulo sobre a lacuna. Com o boto direito do mouse sobre o controle escolha a opo Formatar controle. Na aba Controle preencha o intervalo de entrada selecionando as opes que devero compor o controle. No vnculo da clula escolha uma clula vazia e Ok.

Selecione a lacuna a ser preenchida pelo sexo e clique na guia Desenvolvedor. Na faixa de opes Controles, clique em Inserir. Clique sobre o sexto boto (da esquerda para a direita) Boto de opo e desenhe um retngulo sobre a lacuna. Repita o processo e cria outra vez o mesmo boto. Com o boto direito do mouse sobre o controle escolha a opo Formatar controle. Na aba Controle preencha o vnculo da clula escolhendo uma clula vazia e Ok. Clique sobre a moldura do boto e ative a edio. Substitua o texto por M. Faa o mesmo com o segundo boto e substitua por F.

Relatrio de Tabela e Grfico dinmico

Tabelas Dinmicas so nada mais do que resumos de dados de uma determinada lista. dinmica porque pode ser modificada rapidamente, e facilmente recalculada quando os dados de origem so modificados.

Grfico Dinmico um complemento da tabela dinmica em forma visual dos dados, que em apenas alguns cliques gerado automaticamente.

As tabelas dinmicas possuem campos que se originam das colunas na lista ou tabela original. possvel tambm, criar uma tabela dinmica de uma lista ou banco de dados do Excel, ou de um banco de dados externo.

Restries das Tabelas Dinmicas

Para que uma tabela dinmica funcione corretamente devemos atender aos seguintes conselhos e restries:

- 43 -

Devemos identificar cada uma das colunas com um nome (label) porque o Excel ir fazer com que cada label seja o nome de campo na Tabela Dinmica,

Porque a Tabela Dinmica cria os necessrios totais baseados nos campos que temos na tabela,

Devemos remover quaisquer totais automticos da lista de dados,

O Excel usa toda a lista, incluindo clulas escondidas. Se no queremos que os dados escondidos no constem da Tabela Dinmica, devemos filtrar a lista de dados usando o Filtro Avanado,

O Excel permite criar uma Tabela Dinmica que contenha um mximo de 8.000 itens. S podemos ter 256 campos na rea da Pgina e 256 campos na rea de Dados. As outras reas esto somente limitadas aos limites prprios da(s) folha(s) de clculo.

A Tabela Dinmica e os seus campos

Adicionamos campos importados para a Tabela Dinmica arrastando-os para uma das quatro diferentes reas: Filtros de Relatrio, Rtulos de Coluna, Rtulos de Linha e Valores. Apesar de podermos adicionar um campo a qualquer uma das reas da Tabela Dinmica, o posicionamento determina o layout da Tabela.

Uma das caractersticas interessantes das Tabelas Dinmicas que o Excel insere automaticamente a linha e a coluna permitindo indicar o total para os valores nas linhas e colunas correspondentes da Tabela Dinmica.

Vamos criar uma tabela dinmica com base na tabela abaixo:

Selecione qualquer clula da tabela e clique na guia Inserir, faixa de opes Tabelas e clique na opo Tabela Dinmica. Na caixa de dilogo apenas clique em Ok.

- 44 -

Clique sobre o campo Funcionrio e arraste-o para Filtro de Relatrio. Arraste o Cargo para Rtulos de Linha e Salrio Base para Valores.

O resultado do layout ser:

Para montar o grfico dinmico com base na tabela dinmica, selecione qualquer clula da tabela dinmica, clique na guia Inserir e na faixa de opes Grficos escolha o grfico de Colunas (Colunas 2D \ Colunas agrupadas).

- 45 -

Macros

uma ao ou um conjunto de aes gravadas em um mdulo VBA. Aps gravar uma macro possvel atribuir a um boto e quando necessrio ativ-la a um clique deste boto.

Macro para remover as linhas de grade e cabealhos (ttulos) de linhas e coluna do Excel 2007.

Para criar essa macro, basta clicar no atalho disponibilizado na barra de status. Na caixa de dilogo, digite Desativar e pressione Ok. Clique na guia Exibio e na faixa de opo Mostrar/ocultar desmarque as opes Linhas de grade e Ttulos. Clique sobre o boto Parar gravao localizado na barra de status.

Use a combinao Alt F8 para exibir a caixa de dilogo de macros. Selecione a macro Desativar e clique no boto Editar. Nesse momento voc direcionado ao ambiente VBA. A rotina da macro apresentada.

Sub Desativar() ' ' Desativar Macro ' ' ActiveWindow.DisplayGridlines = False ActiveWindow.DisplayHeadings = False End Sub

Documents

Apostila de Excel Avancado 2007