Excel Avançado
Ronado Calixto
Vinicius Santana
Tipos de Referência
Referência
Relativa
Quando o contéudo das células é
copiado há alteração tanto na
linha quanto na coluna.
A1
Referência
Absoluta
Refere-se às células como
posições fixas neste caso não há
alteração ao copiar a célula.
$A$1
Referência
Mista
A referência tem parte relativa e
parte absoluta
$A1 ou
A$1
Operador de referência
: (dois-pontos)
Operador de intervalo, que
produz uma referência a todas as
células entre duas referências,
incluindo as duas referências
B5:B15
; (ponto-e-vírgula)
Operador de união, que combina
diversas referências em uma
referência
SOMA(B5:B15;D5:D15)
Operadores Aritméticos
Operador aritmético
Significado
+ (sinal de adição)
Adição
– (sinal de subtração)
Subtração
Negação
* (sinal de multiplicação)
Multiplicação
/ (sinal de divisão)
divisão
%
porcentagem
^ (sinal de
exponenciação)
Exponenciação
Operador de comparação
= (sinal de igual)
Igual a
A1=B1
> (sinal de maior do que)
Maior do que
A1>B1
< (sinal de menor do que)
Menor do que
A1<B1
>= (sinal de maior ou igual a)
Maior ou igual a
A1>=B1
<= (sinal de menor ou igual a)
Menor ou igual a
A1<=B1
<> (sinal de diferente)
Diferente
A1<>B1
Operador de texto
Operador de texto
Significado
Exemplo
& (E comercial)
Conecta ou concatena dois
valores para produzir um valor de
texto contínuo
north" & "wind" produz
"Northwind"
Funções
Uso de funções para calcular valores
As funções são fórmulas predefinidas 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, ou referências 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 vírgulas 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.
Funções
Inserindo uma fórmula que contenha uma função.
=SOMA(A10,B5:B10;50,37)
= Sinal de igual (Se a função estiver no início de uma formula).
, Uma vírgula separa cada argumento.
() Parentêses envolvem os argumentos.
Soma
•
•
•
•
SOMA(núm1;núm2; ...)
Núm1, núm2,... são argumentos de 1 a 30 que se deseja somar.
Os números, valores lógicos e representações em forma de texto de
números digitados diretamente na lista de argumentos são contados.
Consulte o primeiro e o segundo exemplos seguintes.
=SOMA(3; 2) é igual a 5
=SOMA("3"; 2; VERDADEIRO) é igual a 6
Porque os valores de texto são traduzidos em números e o valor lógico
VERDADEIRO é traduzido no número 1.
Soma
•
Se um argumento for uma matriz ou referência, apenas os números nesta
matriz ou referência serão contados. Células vazias, valores lógicos, texto ou
valores de erro na matriz ou referência são ignorados. Consulte o terceiro
exemplo a seguir.
Os argumentos que são valores de erro ou texto que não podem ser traduzidos
em números geram erros.
• Diferentemente do exemplo anterior, se A1 contiver "3" e B1 contiver
VERDADEIRO, então:
=SOMA(A1; B1; 2) é igual a 2 porque as referências a valores não-numéricos nas
referências não são traduzidas.
Se a célula A2:E2 contiver 5, 15, 30, 40 e 50:
=SOMA(A2:C2) é igual a 50
=SOMA(B2:E2; 15) é igual a 150
Media
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 você deseja obter a média.
• Comentários
• Os argumentos devem ser números ou nomes, matrizes ou
referências que contenham números.
Media
Se uma matriz ou argumento de referência contiver
texto, valores lógicos ou células vazias, estes valores
serão ignorados; no entanto, células com o valor nulo
serão incluídos.
Media
Dica Ao calcular a média das células, lembre-se da diferença entre as células
vazias e as que contêm o valor nulo, sobretudo se você tiver desmarcado a
caixa de seleção Valores zero na guia Exibir comando Opções, menu
Ferramentas. As células vazias não são contadas, mas aquelas que contêm
valores nulos são.
Media
• Exemplos
Se A1:A5 se chamar Pontos e contiver os números 10, 7, 9, 27 e 2,
então:
=MÉDIA(A1:A5) é igual a 11
=MÉDIA(Pontos) é igual a 11
=MÉDIA(A1:A5; 5) é igual a 10
MÉDIA(A1:A5) é igual a SOMA(A1:A5)/CONT.NÚM(A1:A5) é igual
a 11
Se C1:C3 se chamar OutrosPontos e contiver os números 4, 18 e
7, então:
MÉDIA(Pontos; OutrosPontos) é igual a 10,5
Se
• Comentários
• Até sete funções SE podem ser aninhadas como argumentos
valor_se_verdadeiro e valor_se_falso para construir testes mais
elaborados. Consulte o último exemplo a seguir.
• Quando os argumentos valor_se_verdadeiro e valor_se_falso
são avaliados, SE retorna o valor que foi retornado por estas
instruções.
• Se qualquer um dos argumentos de SE forem matrizes, cada
elemento da matriz será avaliado quando a instrução SE for
executada.
Se
A função de planilha SE verifica uma condição que pode ser
verdadeira ou falsa. Se a condição for verdadeira, a função
retornará um valor; se for falsa, a função retornará outro
valor. A função possui três argumentos:
1.
2.
3.
A condição a ser verificada,
O valor a ser retornado se a condição for verdadeira
E o valor a ser retornado se a condição for falsa.
=SE(teste_lógico,valor_se_verdadeiro,valor_se_falso)
Se
=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.
Se
=SE(teste_lógico,valor_se_verdadeiro,valor_se_falso)
Valor_se_verdadeiro é o valor retornado se teste_lógico for
VERDADEIRO.
• Por exemplo, se esse argumento for a 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).
Para exibir a palavra VERDADEIRO, use o valor lógico
VERDADEIRO para esse argumento. Valor_se_verdadeiro
pode ser outra fórmula.
Se
=SE(teste_lógico,valor_se_verdadeiro,valor_se_falso)
Valor_se_falso é o valor retornado se teste_lógico for FALSO.
Por exemplo, se esse argumento for a 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". Se teste_lógico for FALSO e
valor_se_falso for omitido (ou seja, se não houver vírgula após
valor_se_verdadeiro), o valor lógico FALSO será retornado.
Se teste_lógico for FALSO e valor_se_falso for vazio (ou seja, se houver
uma vírgula após valor_se_verdadeiro seguida do parênteses de fechamento),
o valor 0 (zero) será retornado. Valor_se_falso pode ser outra fórmula.
Se
• Exemplos
Em uma planilha de orçamento, a célula A10 contém uma
fórmula para calcular o orçamento atual. Se o resultado da
fórmula contida na célula A10 for menor que ou igual a 100, a
função seguinte exibirá "Dentro do orçamento". Caso contrário,
a função exibirá "Acima do orçamento".
SE(A10<=100,"Dentro do orçamento","Acima do orçamento")
Se
Suponha que uma planilha de despesa contenha os seguintes
dados em B2:B4 em relação às "Despesas reais" para janeiro,
fevereiro e março: 1500, 500, 500. C2:C4 contém os seguintes
dados para "Despesas previstas" para os mesmos períodos:
900, 900, 925.
Você poderia escrever uma fórmula para verificar se está acima
do orçamento ou não com as seguintes fórmulas:
SE(B2>C2;"Acima do orçamento";"OK")
é igual a "Acima do orçamento"
SE(B3>C3;"Acima do orçamento";"OK")
é igual a "OK"
Se
No exemplo a seguir, se o valor na célula A10 for
100, então teste_lógico será VERDADEIRO e o valor
total para o intervalo B5:B15 será calculado.
Caso contrário, se teste_lógico for FALSO e texto
vazio ("") for retornado a célula que contém a função
SE será esvaziada.
SE(A10=100;SOMA(B5:B15);"")
E
•
•
•
•
•
Retorna VERDADEIRO se todos os argumentos forem
VERDADEIRO; retorna FALSO se um ou mais argumentos
forem FALSO.
Sintaxe
E(lógico1;lógico2; ...)
Lógico1; lógico2;... são de 1 a 30 condições que você deseja
testar e que podem ser VERDADEIRO ou FALSO.
Os argumentos devem ser valores lógicos, matrizes ou
referências que contêm valores lógicos.
Se uma matriz ou argumento de referência contiver texto ou
células vazias, estes valores serão ignorados.
• Se o intervalo especificado não contiver valores lógicos, E
retornará o valor de erro #VALOR!.
E
• Exemplos
E(VERDADEIRO; VERDADEIRO) é igual a VERDADEIRO
E(VERDADEIRO; FALSO) é igual a FALSO
E(2+2=4; 2+3=5) é igual a VERDADEIRO
Se B1:B3 contiver os valores VERDADEIRO, FALSO e
VERDADEIRO, então:
E(B1:B3) será igual a FALSO
E
Se B4 contiver um número entre 1 e 100, então:
E(1<B4; B4<100) será igual a VERDADEIRO
Suponha que você deseja exibir B4 se esta contiver um número
entre 1 e 100, e que você deseja exibir uma mensagem se ela
não contiver.
Se B4 contiver 104, então:
SE(E(1<B4; B4<100); B4; "O valor está fora do intervalo.")
será igual a "O valor está fora do intervalo".
Se B4 contiver 50, então:
SE(E(1<B4; B4<100); B4; "O valor está fora do intervalo.")
será igual a 50
OU
• Retorna VERDADEIRO se qualquer argumento for
VERDADEIRO; retorna FALSO se todos os argumentos forem
FALSOS.
• Sintaxe
• OU(lógico1;lógico2;...)
• Lógico1;lógico2,... são de uma a 30 condições que você deseja
testar e que podem resultar em VERDADEIRO ou FALSO.
• Os argumentos devem ser avaliados como valores lógicos do
tipo VERDADEIRO ou FALSO ou como matrizes ou referências
que contêm valores lógicos.
• Se um argumento de uma matriz ou referência contiver texto ou
células vazias, esses valores serão ignorados.
• Se o intervalo especificado não contiver valores lógicos, OU
retornará o valor de erro #VALOR!.
OU
• Exemplos
OU(VERDADEIRO) é igual a VERDADEIRO
OU(1+1=1;2+2=5) é igual a FALSO
Se o intervalo A1:A3 contiver os valores
VERDADEIRO, FALSO e VERDADEIRO:
OU(A1:A3) será igual a VERDADEIRO
Cont.Se
• CONT.SE possui dois argumentos: o intervalo a ser verificado e
o valor a ser usado no intervalo (o critério).
=CONT.SE(intervalo;critério)
Intervalo:
Intervalo de Células no qual se deseja contar células não vazias.
Critério:
É o critério na forma de um número, expressão ou texto que define
quais células serão contadas.
Cont.Se
• Exemplo:
• Suponha que de C9:C16 contenha
“F”, “M”,”M”,”F”,”M”,”M”,”F”,”M” respectivamente.
=cont.se(c9:c16;”M”) é igual a 5.
=cont.se(c9:c16;”F”) é igual a 3.
Exemplo(SomaSe)
A
B
1 Vendedor
Renda Total
2
Manuel
R$15.000,00
3
Manuel
R$9.000,00
4
Gabriel
R$8.000,00
5
Gabriel
R$20.000,00
SomaSe
• A função de planilha SOMASE calculará a quantia total faturada
por cada vendedor.
SOMASE possui três argumentos:
• O intervalo a ser verificado
• O valor a ser usado no intervalo (o critério)
• O intervalo que contém os valores a serem somados.
=SOMASE(intervalo;critério;intervalo_soma)
SomaSe
A fórmula verifica:
• O texto na célula A32 (o argumento critério).
• Na lista Vendedor (A2:A5, o argumento intervalo) .
• E a soma as quantias correspondentes da coluna
Renda total (B2:B5, o argumento intervalo_soma).
Para Manuel, a função (na célula C32) tem a seguinte aparência:
=SOMASE(A2:A5,A32,B2:B5)
ou
=SOMASE(A2:A5,”Manuel”,B2:B5)
SomaSe
A
31
32
33
B
Vendedor Total de Pedidos
C
Total de Vendas
Manuel
13
=SOMASE(A2:A5,A32,B2:B5)
Gabriel
7
=SOMASE(A2:A5,A33,B2:B5)
Erro
• O que significa o erro #DIV/0!?
– O valor de erro #DIV/0! ocorre quando uma fórmula divide
por 0 (zero).
• O que significa o erro #####?
– O valor numérico inserido em uma célula é muito grande
para ser exibido.
• O que significa o erro #VALOR!?
– Ocorre quando o tipo de argumento ou operando errado é
usado.
• O que significa o erro #NOME?
– Ocorre quando não é reconhecido o texto em uma fórmula.
Erro
• O que significa o erro #ND?
– Ocorre quando um valor não se encontra disponível para
uma função ou fórmula.
• O que significa o erro #REF!?
– Ocorre quando uma referência da célula não é válida.
• O que significa o erro #NUM!?
– Ocorre quando existe um problema com um número em
uma fórmula ou função.
• O que significa o erro #NULO?
– Ocorre quando é especificado uma interseção entre duas
áreas que não tem interseção.
Tipo.Erro
• TIPO.ERRO
Retorna um número que corresponde a um dos valores de erro
do Microsoft Excel ou retorna o erro #N/D se não houver erro.
Você pode usar TIPO.ERRO em uma função SE para testar um
valor de erro e retornar uma seqüência de caracteres de texto,
como uma mensagem, em vez de um valor de erro.
• Sintaxe
• TIPO.ERRO(val_erro)
• Val_erro é o valor de erro cujo número de identificação você
deseja localizar. Apesar de val_erro poder ser o valor de erro
real, ele será normalmente uma referência a uma célula que
contenha uma fórmula que se deseje testar.
Tipo.Erro
• Exemplo
A fórmula a seguir verifica a célula E50 para ver se contém o valor de erro
#NULO! ou o valor de erro #DIV/0!.
Se retornar, o número do valor de erro será usado na função de planilha
ESCOLHER para exibir uma entre duas mensagens; caso contrário, o
valor de erro #N/A será retornado.
• IF(ERROR.TYPE(E50)<3,CHOOSE(ERROR.TYPE(E50),"Rang
es do not intersect","The divisor is zero"))
Val_erro
Se val_erro for
TIPO.ERRO retornará
#NULO!
1
#DIV/0!
2
#VALOR
3
#REF!
4
#NOME?
5
#NÚM!
6
#N/D
6
Outros
#N/D
ÉERRO-ÉERROS
ÉERRO
Retorna verdadeiro se ‘Valor’ for qualquer valor de erro exceto
#N/D.
ÉERROS
Retorna verdadeiro se ‘Valor’ for qualquer valor de erro (#N/D,
#VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!).
Exemplo:
=SE(ÉERROS(MÉDIA(INTERVALO));"";MÉDIA(INTERVALO))
PROCV
Localiza um valor na primeira coluna à esquerda de uma tabela
e retorna um valor na mesma linha de uma coluna especificada
na tabela. Use PROCV em vez de PROCH quando os valores
da comparação estiverem posicionados em uma coluna à
esquerda ou à direita dos dados que você deseja procurar.
• Sintaxe
• PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;
procurar_intervalo)
PROCV
1
a
Jan
2
b
Fev
3
c
Mar
4
d
Abr
5
e
Mai
6
f
Jun
7
g
Jul
8
h
Ago
9
i
Set
=PROCV(3;A1:C9;2) -> c
=PROCV(3;A1:C9;3) -> mar
PROCH
• Localiza um valor específico na linha superior de uma tabela ou
matriz de valores e retorna o valor na mesma coluna de uma
linha especificada na tabela ou matriz. Use PROCH quando
seus valores de comparação estiverem localizados em uma
linha ao longo da parte superior de uma tabela de dados e você
deseja observar um número específico de linhas mais abaixo.
Use PROCV quando os valores de comparação estiverem em
uma coluna à esquerda ou à direita dos dados que você deseja
localizar.
• Sintaxe
• PROCH(valor_procurado;matriz_tabela;núm_índice_lin;proc
urar_intervalo)
PROCH
1
2
3
4
5
6
7
8
9
a
b
c
d
e
f
g
h
i
Jan
Fev
Mar
Abr
Maio
Jun
Jul
Ago
Set
=PROCH(5;A1:I3;2) -> e
=PROCH(5;A1:I3;3) -> maio
ARRED
Arredonda um número até uma quantidade especificada de dígitos.
• Sintaxe
• ARRED(núm;núm_dígitos)
• Núm é o número que você deseja arredondar.
• Núm_dígitos especifica o número de dígitos para o qual você
deseja arredondar núm.
ARRED
>0
=0
<0
Núm será arredondado para o número
específicado de casas decimais.
Núm será arredondado para o inteiro mais
próximo
Núm será arredondado para esquerda da
vírgula decimal
=ARRED(2,15; 1) é igual a 2,2
=ARRED(2,149; 1) é igual a 2,1
=ARRED(-1,475; 2) é igual a -1,48
=ARRED(21,5; -1) é igual a 20
ARREDONDAR.PARA.BAIXO
ARREDONDAR.PARA.BAIXO(núm;núm_dígitos)
• Núm é qualquer número real que se deseja arredondar.
• Núm_dígitos é o número de dígitos para o qual se deseja
arredondar núm.
• Comentários
• ARREDONDAR.PARA.BAIXO funciona como ARRED, com a
diferença de sempre arredondar um número para baixo.
ARREDONDAR.PARA.BAIXO
>0
=0
<0
Exemplos
Núm será arredondado para baixo pelo
número específicado de casas decimais.
Núm será arredondado para baixo até o
inteiro mais próximo.
Núm será arredondado para baixo à
esquerda da vírgula decimal
=ARREDONDAR.PARA.BAIXO(3,2; 0) é igual a 3
=ARREDONDAR.PARA.BAIXO(76,9; 0) é igual a 76
=ARREDONDAR.PARA.BAIXO(3,14159; 3) é igual a 3,141
=ARREDONDAR.PARA.BAIXO(-3,14159; 1) é igual a -3,1
=ARREDONDAR.PARA.BAIXO(31415,92654; -2) é igual a 31.400
ARREDONDAR.PARA.CIMA
Arredonda um número para cima afastando-o de zero.
Sintaxe
ARREDONDAR.PARA.CIMA(núm;núm_dígitos)
Núm é qualquer número real que se deseja arredondar.
Núm_dígitos é o número de dígitos para o qual se deseja
arredondar núm.
Comentários
• ARREDONDAR.PARA.CIMA funciona como ARRED, com a
diferença de sempre arredondar um número para cima.
ARREDONDAR.PARA.CIMA
>0
=0
<0
Exemplos
Núm será arredondado para cima pelo
número específicado de casas decimais.
Núm será arredondado para cima até o
inteiro mais próximo.
Núm será arredondado para cima à esquerda
da vírgula decimal
=ARREDONDAR.PARA.CIMA(3,2;0) é igual a 4
=ARREDONDAR.PARA.CIMA(76,9;0) é igual a 77
=ARREDONDAR.PARA.CIMA(3,14159; 3) é igual a 3,142
=ARREDONDAR.PARA.CIMA(-3,14159; 1) é igual a -3,2
=ARREDONDAR.PARA.CIMA(31415,92654; -2) é igual a 31.500
INT
Arredonda um número para baixo até o número inteiro mais
próximo.
• Sintaxe
• INT(núm)
• Núm é o número real que se deseja arredondar para baixo até
um inteiro.
• Exemplos
=INT(8,9) é igual a 8
=INT(-8,9) é igual a -9
Truncar e Int
• Comentários:
– Truncar e Int são semelhantes, pois os dois retornam
inteiros.
– Truncar remove a parte fracionária do número.
– Int arredonda para menos até o número inteiro mais
próximo, de acordo com a parte fracionária do número.
– Truncar e Int são diferentes apenas quando usam números
negativos.
=Truncar(-4,3) retorna –4
=Int(-4,3) retorna -5
Vínculos
• São planilhas que fazem referências a células de outras
planilhas ou pastas.
• A melhor maneira de estabelecer uma fórmula com referências
tridimensionais é utilizar a técnica de apontar com o mouse
para endereços ou intervalos.
• Exemplo:
=Soma([Pasta1]Plan1!A1:A5)
• Obs: Soma os valores da células de A1 até A5 na planilha Plan1
que está na Pasta1.
Criando Funções
• Conjunto de Rotinas que têm como objetivo executar
um conjunto de instruções e produzir um valor final.
Funções
• São definidas por:
– Nome da Função
– Parâmetros
• Valores enviados entre parênteses e que constituem o
input da função.
• Sobre este input que serão executados os respectivos
cálculos.
– Resultado(Output)
• Valor produzido pela aplicação da função aos
Parâmetros.
Funções
•
Como criar Funções?
1. Defina o objetivo da Função, os parâmetros e
resultado.
2. Escreva um algoritmo.
3. Traduza o seu algoritmo em VBA.
(Visual Basic for Application).
Funções
•
Sintaxe do VBA:
Function <NOME DA FUNÇÃO>(<PARAMETRO1>,<PARAMETRO2>,…)
….
End Function
Funções
• Exemplo:
• Problema:Fazer uma função que calcule a nota final de um
candidato em um concurso.
• Parâmetros:Nota da Prova Objetiva,Nota da Prova Discursiva
• Passos a executar:
– Multiplicar Nota da Prova Discursiva por 3.
– Somar a Nota da Prova Objetiva com o resultado anterior.
– Atribuir a uma variável.
Funções
• Em VBA:
Function Nota(NO,ND)
NF=NO+(3*ND)
End Function
Funções
• Onde definir?
– No Editor de VBA, no excel escolha no Menu
Ferramentas/Macro/Editor de VBA.
– No editor insira um Módulo
– Digite o seu código.
Funções
Funções
• Como Executar?
• Selecionar na Planiha no Menu Inserir /Função ou na Barra de
Ferramentas no botão Fx.
VBA
• Sete Objetos mais Importantes:
–
–
–
–
–
–
–
Application(Aplicativo)
Range(Faixa)
WorksheetFunction(Função de Planilha)
Woorkbook(Pasta de Trabalho)
WoorkSheet(Planilha)
PivotTable(Tabela-Pivô)
Chart(Gráfico)
VBA
Tipo de dados
Tamanho de
armazenamento
Intervalo
Byte
1 byte
de 0 a 255
Boolean
2 bytes
True ou False
Integer
2 bytes
de -32.768 a 32.767
Long
(número inteiro longo)
4 bytes
de -2.147.483.648 a
2.147.483.647
VBA
Tipo de dados
Tamanho de
armazenamento
Intervalo
Single
(vírgula flutuante de
precisão simples)
4 bytes
de –3,402823E38 a 1,401298E-45 para valores
negativos; de 1,401298E-45
a 3,402823E38 para valores
positivos
Double
(vírgula flutuante de dupla
precisão)
8 bytes
de –1,79769313486231E308
a
-4,94065645841247E-324
para valores negativos; de
4,94065645841247E-324 a
1,79769313486232E308 para
valores positivos.
VBA
Tipo de dados
Tamanho de
armazenamento
Intervalo
Currency
(número inteiro em escala)
8 bytes
de 922.337.203.685.477,5808 a
922.337.203.685.477,5807
Decimal
14 bytes
+/79.228.162.514.264.337.593.
543.950.335 sem vírgula
decimal;
+/7,9228162514264337593543
950335 com 28 casas
decimais à direita; o menor
número diferente de zero é
+/0,0000000000000000000000
000001.
VBA
Tipo de dados
Tamanho de
armazenamento
Intervalo
Date
8 bytes
De 1 de janeiro de 100
a 31 de dezembro de
9999
Objeto
4 bytes
Qualquer referência
Object
String
(comprimento variável
Variant
(com números)
10 bytes + comprimento
da seqüência
De 0 a aproximadamente 2
bilhões
16 bytes
Qualquer valor numérico
até o intervalo de um
Double
VBA
Tipo de dados
Variant
(com caracteres)
Tamanho de
armazenamento
22 bytes +
comprimento da
seqüência
Definido pelo usuário Número requerido
(usando Type)
por elementos
Intervalo
O mesmo intervalo
de String de
comprimento
variável
O intervalo de cada
elemento é igual ao
intervalo do seu tipo
de dados.
VBA
• DeclararVariável
– Exemplos:
• Dim Name as String
• Dim Holiday as Date
Dim:Palavra chave que indica uma declaração de
variáveis(abreviatura de dimensão)
Número:Nome da variável a utilizar.
As:Palavra chave utilizada para separar o nome da
variável do tipo de dados.
Integer:Tipo de dados atribuído à variável.
VBA Control
If-then-Else
Testa uma condição e executa um determinado conjunto de
instruções
For-next
Executa uma deterninada tarefa um determinado número de
vezes.
While-Wend
Executa uma determinada tarefa enquanto que um condição
permaneça verdadeira, com valor TRUE.
Do loop
Executa uma determinada tarefa enquanto que um condição
permaneça TRUE, ou então até que seja TRUE.
Select-Case
Seleciona um dos segmentos de código a processar mediante a
avaliação consecutiva de condições.
For-Each-Next
Realiza uma determinada tarefa repetitiva em cada objeto de
uma coleção ou cada item de um array.
VBA Control
• Exemplo:
If(<condição>,<se condição verdadeira>,<se condição falsa>)
Function É_Par(numero)
Dim resto As Double
resto=Numero Mod 2
If resto =0 then
É_Par=True
else
É_Par=False
End Function
VBA Control (exercício)
• Aplicação Prática 1:
• Crie a função MOD para usar nas células do
Excel.
• Elementos de Análise:
• Nome:Resto_Divisao
• Parâmentros:Dividendo e Divisor
• Output:Resto da divisão do Dividendo pelo
divisor.
VBA Control(exercício)
• Passos a Executar:
– Calcular o quociente da
divisão(Dividendo/Divisor) na forma inteira.
– Subtrair ao dividendo a multiplicação do
quociente pelo Divisor, obtendo assim o resto.
– Escreva uma função em VBA.
VBA Control
• Select Case<Expressão a ser avalidada>
[Case<Valor da Expressão>[Instrução a realizar]]
…[Case Else[Instruções a realizar na situação
residual]]
End Select
VBA Control
Function C_Etaria(Idade)
Select Case Idade
Case faixa<3
C_etaria=“Bebê”
Case faixa<13
C_etaria=“Criança”
Case faixa<20
C_etaria=“Adolescente”
Case faixa<26
C_etaria=“Jovem”
Case faixa<66
C_etaria=“Adulto”
Case Else
C_etaria=“Idoso”
End Select
End Function
VBA Control
• For –Next
Permite a execução repetida de uma tarefa durante um
determinado número de vezes.
For<Inicialização do Contador>To<Valor>[Step<Valor a Incrementar>]
<Instruções a realizar em cada iteração>
Next
VBA Control
• Exemplo
• Elementos de Análise:
– Nome: Calc_Potência
– Parâmetros:Base, Potência
– Output:Base elevada à potência.
• Passos a Executar:
– Multiplicar sucessivas vezes(número indicado pela
potência) à base pelo resultado acumulado das
sucessivas operações.
VBA Control
Function Calc_Potencia(Base,Potencia)
Dim i As Integer
Dim acumulado as Long
acumulado=1
For i=1 to Potencia Step 1
acumulado= acumulado*Base
Next
Calc_potencia=acumulado
End Function
VBA Control
While –Wend
While<Condição>
...
Wend
VBA Control
• Exemplo
• Elementos de Análise:
– Nome: Fator
– Parâmetros:Número
– Output:Fatorial do Número Produzido.
• Passos a Executar:
– Verificar se o número é positivo,se for multiplicar
sucessivas vezes um número pelo resultado acumulado
das sucessivas multiplicações do números inteiros que o
antecedem.
VBA Control
Function Fatorial(numero)
Dim i As Integer
Dim acumulado As Long
If numero >=0 then
acumulado=1
I=1
While I<numero
acumulado=acumulado*1
I=I+1
Wend
Fatorial=acumulado
Else
Fatorial=“ERRO”
End if
End Function
VBA(objetos-modelo)
• Propriedade
– É uma característica de um objeto ou atributo.
Ex. Objeto Worksheet tem 55 propriedades.
• Método
– É uma ação que pode ser realizada no objeto.
Ex. O objeto Worksheet tem um método.
• Objetos-Coleção
– Representam a coleção de objetos.
VBA(MsgBox)
• MsgBox
– Msgbox é usada para exibir uma mensagem e
aguardar que um usuário responda um botão.
• Msgbox(prompt[,buttons][,title])
– Prompt: Parâmetro da String
– Buttons: O botão
– Title:Titulo da barra de Titulo
VBA(MsgBox)
Constante
Valor
Descrição
VbOKOnly
0
Exibe somente o
botão OK.
VbOKCancel
1
Exibe os botões OK e
Cancelar.
VbAbortRetryIgnore
2
Exibe os botões
Abortar, Repetir e
Ignorar.
VbYesNoCancel
3
Exibe os botões Sim,
Não e Cancelar.
VBA(MsgBox)
Constante
Valor
Descrição
VbYesNo
4
Exibe os botões Sim e Não.
VbRetryCancel
5
Exibe os botões Repetir e
Cancelar.
vbCritical
16
Exibe o ícone Mensagem
crítica.
vbExclamation
48
Exibe o ícone Mensagem de
aviso.
VBA(MsgBox)
(valores retornados)
Constante
Valor
Descrição
vbOK
1
OK
vbCancel
2
Cancelar
vbAbort
3
Abortar
vbRetry
4
Repetir
vbIgnore
5
Ignorar
vbYes
6
Sim
vbNo
7
Não
VBA(MsgBox)
Exemplo:
Sub negrt()
For Each n In Plan1.Range("area")
If n.Font.Bold Then
MsgBox "Linha " & n.Row & " Coluna " & n.Column & vbCrLf _
& n.Value
Else
End If
Next n
End Sub
VBA(MsgBox)
• Exemplo:
Sub caixa()
MsgBox “Continua?", vbQuestion + vbYesNo
End Sub
VBA(InputBox)
• InputBox
– A função InputBox é projetada para entrada do
usuário.
• InputBox(prompt[,title][,default])
– Prompt:É a mensagem na caixa de entrada.
– Title:É o título da caixa de entrada(Barra de Título).
– Default:É o valor padrão que é exibido na caixa do texto.
VBA
• Exemplo:
sNome=InputBox(“Digite o seu nome.”,”Nome”,”Vinicius”)
VBA(string)
Função
Exemplo
Retorna
Len
Len(“January Invoice”)
15
Ucase
Lcase
MsgboxUcase(“Donna”)
MsgboxLcase(“Donna”)
DONNA
donna
Str
Val
Str(123)
Val(“4.5”)
“123”
4.5
Left
Rigth
Left(Olá Mundo, 7)
Rigth(Olá Mundo, 6)
“Olá Mun”
“ Mundo”
Trim
RTrim
LTrim
Trim(“ extra “)
Espaços finais
Espaços na frente
extra
VBA
• Sintaxe de Objeto-Modelo
• ObjVar.Aproperty
– ObjVar->Variável do objeto que se refere aum objeto
em particular.
– Aproperty->É uma propriedade desse objeto.
• Exemplo:
Dim rgn As Range
Set rgn=ActiveSheet.Rows(i)
Rgn.Font.Name=“Arial”
VBA
• Variáveis-Objeto
– Alternadamente, podemos designar uma variável-objeto
elas poupam tempo de execução.
– Exemplo:
Dim fnt As Font
Set fnt=ActiveSheet.Rows(I).Font
Fnt.bold=true
Fnt.Italic=true
Fnt.Underline=true
Fnt.Size=true
Fnt.Name=“Arial”
VBA
Objeto Application
Representa todo o aplicativo Microsoft Excel.
O objeto Application contém:
•
Definições e opções para o aplicativo como um
todo (muitas das opções da caixa de diálogo
Opções (menu Ferramentas), por exemplo).
•
Métodos que retornem objetos do nível mais alto,
como ActiveCell, ActiveSheet e assim por diante.
VBA
• Propriedade ActiveCell
Retorna um objeto Range representando a célula
ativa da janela ativa (a janela visível) ou da janela
especificada.
Se a janela não estiver exibindo uma planilha, essa
propriedade falhará. Somente leitura.
ActiveCell Application.ActiveCell
ActiveWindow.ActiveCell
Application.ActiveWindow.ActiveCell
VBA
•
•
Objeto Range
Representa uma célula, uma linha, uma coluna, uma seleção
de células contendo um ou mais blocos contíguos de células .
Usar o objeto Range
As seguintes propriedades e métodos para retornar um
objeto Range são descritas nesta seção:
Propriedade Range
Propriedade Cells
Range e Cells
Propriedade Offset
Método Union
VBA(Objeto Range )
• Propriedade Range
Use Range(argumento), onde argumento nomeia o intervalo, para
retornar um objeto Range representando uma única célula ou um
intervalo de células.
O exemplo seguinte coloca o valor da célula A1 na célula A5.
Worksheets("Sheet1").Range("A5").Value=_
Worksheets("Sheet1").Range("A1").Value
VBA(Objeto Range )
• O exemplo seguinte preenche o intervalo A1:H8 com
números randômicos definindo a fórmula para cada célula
do intervalo.
• Quando usada sem um qualificador de objeto (um objeto à
esquerda do ponto), a propriedade Range retorna um
intervalo da planilha ativa.
• Se a planilha ativa não for uma planilha de trabalho, o
método falhará.
• Use o método Activate para ativar uma planilha antes de
usar a propriedade Range sem um qualificador de objeto
explícito.
VBA(Objeto Range )
• Propriedade Cells
Use Cells(linha, coluna) onde linha é o índice da linha e coluna é o
índice da coluna, para retornar uma única célula.
O exemplo seguinte define o valor da célula A1 como 24.
Worksheets(1).Cells(1, 1).Value = 24
O exemplo seguinte define a fórmula para a célula A2.
ActiveSheet.Cells(2, 1).Formula = "=sum(B1:B5)"
VBA(Objeto Range )
• Range e Cells
Use Range(célula1, célula2), onde célula1 e célula2
são objetos Range que especificam as células inicial
e final, para retornar um objeto Range.
O exemplo seguinte define o estilo da linha da borda
das células J1:J10.
With Worksheets(1)
.Range(.Cells(1, 1), _
.Cells(10, 10)).Borders.LineStyle = xlThick
End With
VBA
• Observe o ponto na frente de cada ocorrência da
propriedade Cells.
• O ponto será obrigatório se o resultado da instrução
With anterior for aplicado à propriedade Cells —
nesse caso, para indicar que as células estão na
planilha um (sem o ponto, a propriedade Cells
retornaria as células da planilha ativa).
VBA(Objeto Range )
• Método Activate
Ativa o objeto
Sintaxe
expressão.Activate
Exemplo do método Activate
Este exemplo ativa Plan1 .
Worksheets(" Plan1 ").Activate
VBA(Objeto Range )
Este exemplo seleciona as células A1:C3
de Plan1 e, em seguida, faz de B2 a
célula ativa.
Worksheets(" Plan1 ").Activate
Range("A1:C3").Select
Range("B2").Activate
VBA(Objeto Range )
Este exemplo ativa BOOK4.xls.
Se BOOK4.xls tiver várias janelas, o exemplo ativará a
primeira janela, BOOK4.xls:1.
Workbooks("BOOK4.XLS").Activate
VBA(Objeto Range )
Propriedade Offset
Use Offset(linha, coluna), onde linha e coluna são os deslocamentos
de linha e coluna, para retornar um intervalo em um deslocamento
especificado de um outro intervalo.
O exemplo seguinte seleciona a célula três linhas abaixo e uma coluna
à esquerda da célula do canto superior esquerdo da seleção atual.Você
não pode selecionar uma célula que não esteja na planilha ativa,
portanto, você precisa ativar primeiro a planilha.
Worksheets("sheet1").Activate
Selection.Offset(3, 1).Range("A1").Select
VBA(Objeto Range )
Método Union
Use Union(intervalo1, intervalo2, ...) para retornar intervalos de
várias áreas — isto é, intervalos compostos de dois ou mais
blocos contíguos de células.
O exemplo seguinte cria um objeto definido como a união de
intervalos A1:B2 e C3:D4 e, em seguida, seleciona o intervalo
definido.
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Worksheets("sheet1").Activate
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.Select
VBA(Objeto WorkBook)
• Representa uma pasta de trabalho do Microsoft
Excel.
• O objeto Workbook é um membro da coleção
Workbooks.
• A coleção Workbooks contém todos os objetos
Workbook atualmente abertos no Microsoft Excel.
VBA(Objeto WorkBook)
Método Add
(adiciona uma nova pasta de trabalho vazia ao Microsoft Excel.)
WorkBookObjeto.Add(template)
Método Close
(O exemplo seguinte fecha todas as pastas de trabalho abertas.)
WorkBookObjeto.Close
VBA(Objeto WorkBook)
Método Open
(O exemplo seguinte abre o arquivo Array.xls como
uma pasta de trabalho somente leitura.)
Workbooks.Open fileName:="array.xls", readOnly:=True
VBA(Objeto WorkBook)
• Propriedade ActiveWorkbook
A propriedade ActiveWorkbook retorna a
pasta de trabalho que está ativa no
momento. O exemplo seguinte define o nome
do autor da pasta de trabalho ativa.
• ActiveWorkbook.Author = "Jean Selva"
VBA(Objeto WorkBook)
• Propriedade ThisWorkbook
A propriedade ThisWorkbook retorna a pasta de trabalho
onde há código do Visual Basic sendo executado.
Na maioria dos casos, esta é a própria pasta de trabalho
ativa. Entretanto, se o código do Visual Basic for parte de um
suplemento, a propriedade ThisWorkbook não retornará a
pasta de trabalho ativa.
Nesse caso, a pasta de trabalho ativa é a pasta de
trabalho que está chamando o suplemento, enquanto que a
propriedade ThisWorkbook retorna a pasta de trabalho do
suplemento.
Se você estiver criando um suplemento a partir de seu
código do Visual Basic, você deverá usar a propriedade
ThisWorkbook para qualificar qualquer instrução que precise
ser executada na pasta de trabalho que você compila em um
suplemento.
VBA(Objeto WorkBook)
Por exemplo, use ocódigo tal como o
seguinte para ativar uma folha de caixa de
diálogo armazenada em sua pasta de trabalho
do suplemento.
ThisWorkbook.DialogSheets(1).Show
Organização de Dados em
Listas
• Uma lista é essencialmente uma coleção
organizada de informações.
• O Microsoft Excel oferece uma série de
recursos que facilitam o gerenciamento e a
análise de dados em uma lista.
Diretrizes para Organização de
Dados em Listas
• Inserir rótulos descritivos (um para cada coluna) na
primeira linha da lista;
• Colocar itens semelhantes em uma mesma coluna;
• Manter a lista separada;
• Posicionar dados essenciais acima ou abaixo da lista;
• Mostrar linhas e colunas;
• Usar somente uma lista por planilha.
Formato de Lista
• Usar rótulos de coluna formatados;
• Usar bordas de célula;
• Evitar linhas e colunas em branco;
• Não digitar espaços à esquerda ou precedentes;
• Estender fórmulas e formatos de listas
(Ferramentas/Opções/Editar/Estender
fórmulas e formatos de lista).
Operações em uma Lista
• Entrar com dados;
• Filtrar dados;
• Ordenar dados;
• Calcular subtotais.
Entrar com Dados em uma
Lista
• Diretamente nas células:
– AutoConclusão (Ferramentas/Opções/Editar/Ativar
AutoConclusão para valores de células)
– Mouse (menu de atalho/Lista de opções...)
• Usando um formulário (Dados/Formulário)
Entrar com Dados em uma
Lista
• Janela do formulário:
Entrar com Dados em uma
Lista
• Características do formulário:
– Cada campo apresenta uma caixa de edição;
– Exceção: campos com fórmula não apresentam uma
caixa de edição;
– Consolidar edição de dados e incluir novo registro
(botão Novo);
– Excluir registro (botão Excluir);
– Desconsiderar edição de dados (botão Restaurar);
– Navegar pelos registros (botões Localizar anterior e
Localizar próxima);
Entrar com Dados em uma
Lista
• Características do formulário (continuação):
– Especificar critérios para filtrar registros (botão
Critérios);
– Apagar todos os critérios (botão Limpar);
– Consolidar edição de critérios e visualizar registros
(botão Formulário);
– Consolidar edição de dados e fechar formulário (botão
Fechar).
– Restrição: se o número de campos exceder a 32, o
formulário não será exibido.
Entrar com Dados em uma
Lista
• Operadores utilizados em critérios:
– Operadores de comparação: =, <>, >, >=, <, <=
– Operadores curinga: * e ?
Localizar Dados em uma Lista
• Filtrar uma lista para ver apenas as linhas
que se encaixem em critérios específicos.
– AutoFiltro: para critérios simples;
– Filtro avançado: para critérios mais
complexos.
Filtrar Dados em uma Lista
• Filtrar é uma maneira rápida e fácil de localizar e
trabalhar com um subconjunto de dados em uma
lista.
• Uma lista filtrada exibe somente as linhas que
atendem aos critérios especificados para uma
coluna.
• Só é possível aplicar filtros a apenas uma lista de
uma planilha de cada vez.
Filtrar Dados com AutoFiltro
1.
2.
3.
4.
5.
Posicionar o cursor de seleção em uma célula da lista
que se deseja filtrar;
No menu Dados, clicar em Filtrar e, em seguida, clicar
em AutoFiltro;
Para exibir somente as linhas que contêm um valor
específico, clicar na seta da coluna que contém os dados
que se deseja exibir;
Clicar em um dos valores da lista apresentada;
Para aplicar uma condição adicional baseada em um
valor de outra coluna, repetir as etapas 3 e 4 na outra
coluna.
Filtrar Dados com AutoFiltro
• Observações:
– A barra de status mostra a quantidade de linhas
selecionadas;
– A seta da coluna muda de cor indicando que a lista está
filtrada por um valor daquela coluna;
– Os cabeçalhos de linha dos registros filtrados mudam
de cor;
– Restrição: apenas os primeiros 250 diferentes itens
aparecem na lista;
– Dados/Filtrar/AutoFiltro encerra o modo AutoFiltro.
Filtrar Dados com AutoFiltro
• A lista de valores apresentada pela seta da
coluna inclui as opções:
– (tudo): mostra todos os itens (retira condição);
– (10 Primeiros...): n primeiros ou n últimos;
– (Personalizar...): permite especificar até duas
condições para uma mesma coluna;
– (Vazias): mostra itens não preenchidos;
– (NãoVazias): mostra itens preenchidos.
Filtrar Dados com AutoFiltro
• Pode ser usada qualquer combinação de
valores de colunas diferentes, selecionandose um valor em cada coluna;
• Para utilizar mais uma condição em uma
coluna, deve-se utilizar a opção
(Personalizar...).
Filtrar Dados com AutoFiltro
• Janela Personalizar AutoFiltro:
Filtrar Dados com AutoFiltro
• Características da janela Personalizar AutoFiltro:
– A opção (Personalizar...) permite filtrar os dados de
várias maneiras:
• valores acima ou abaixo de um valor específico
(opção Ou);
• valores em um intervalo (opção E);
• dois valores específicos (opção Ou);
• valores aproximados (caracteres curinga: * e ?).
Filtrar Dados com AutoFiltro
• Janela AutoFiltro – 10 primeiros:
Filtrar Dados com AutoFiltro
• Características da janela AutoFiltro – 10
primeiros:
– A janela só é mostrada quando os dados são numéricos;
– A opção (10 Primeiros...) permite filtrar:
• Os primeiros (maiores) n itens;
• Os últimos (menores) n itens;
• Os primeiros (maiores) n por cento;
• Os últimos (menores) n por cento.
Filtrar Dados com Filtro
Avançado
• O Filtro Avançado permite:
– Especificar critérios mais complexos de
filtragem;
– Especificar critério de filtragem computado;
– Extrair uma cópia dos registros que satisfazem
ao critério para um outro local.
Filtrar Dados com Filtro
Avançado
• Inicialmente deve-se definir um intervalo de critérios, que
consiste de pelo menos duas linhas:
– A primeira deve conter alguns ou todos os nomes de
colunas da lista;
– A segunda deve conter os critérios de filtragem, os
quais serão conjugados com o operador AND.
• Um intervalo de critérios pode ter qualquer número de
linhas, as quais serão conjugadas com o operador OR.
Filtrar Dados com Filtro
Avançado
Mês
Vendedor
Janeiro
Tipo
Total
novo
Fevereiro
>1000
(Mês = “Janeiro”) AND (Tipo = “novo”)
OR
(Mês = “Fevereiro”) AND (Total > 1000)
Filtrar Dados com Filtro
Avançado
• Observações:
=“=Janeiro”
>R
< >R
Ro*
r*r
R?r
Filtrar Dados com Filtro
Avançado
1. Posicionar o cursor de seleção em uma célula da
lista que se deseja filtrar;
2. No menu Dados, clicar em Filtrar e, em
seguida, clicar em Filtro avançado…;
3. Clicar no campo Intervalo de critérios da
janela Filtro avançado;
4. Selecionar o intervalo de células que compõem o
critério;
5. Clicar no botão OK.
Filtrar Dados com Filtro
Avançado
• Janela Filtro avançado:
Filtrar Dados com Filtro
Avançado
• Ao utilizar a opção Copiar para outro local da
janela Filtro avançado:
– Os registros que satisfizerem ao critério de
filtragem serão copiados para o local indicado
no campo Copiar para;
– A própria lista não é filtrada.
Filtrar Dados com Filtro
Avançado
• Ao utilizar a opção Somente registros exclusivos
da janela Filtro avançado:
– Os registros que satisfizerem ao critério de
filtragem serão mostrados sem duplicatas;
– Não preenchendo o campo Intervalo de
critérios, todas as linhas duplicadas da lista
serão escondidas.
Filtrar Dados com Filtro
Avançado
• Observações:
– A barra de status mostra a quantidade de linhas
selecionadas;
– Os cabeçalhos de linha dos registros filtrados
mudam de cor;
– Dados/Filtrar/Mostrar todos encerra o modo
Filtro avançado.
Filtrar Dados com Filtro
Avançado
• Critérios computados:
– Filtram uma lista baseados em um ou mais
cálculos;
– Não utilizam cabeçalhos de coluna da lista, mas
sim novos;
– Essencialmente criam novos campos para a
lista.
Filtrar Dados com Filtro
Avançado
Duração
TRUE
Projeto
Início
Término
= C5 – B5 + 1 >= 30
Recursos
Filtrar Dados com Filtro
Avançado
• Ao utilizar critério computado:
– Criar um novo nome de campo ou deixar a célula vazia;
– Criar fórmulas utilizando referências relativas às células
do primeiro registro da lista;
– Não se preocupar com os valores retornados pelas
fórmulas do intervalo de critérios;
– Ao utilizar referências a células fora da lista em
fórmulas, utilizar referência absoluta;
– Utilizar quantos critérios computados forem
necessários, mesclando-os com critérios não
computados, se necessário.
Filtrar Dados em uma Lista
• Observações:
– Gráficos: apenas os dados visíveis são considerados no
gráfico (Ferramentas/Opções.../Plotar apenas células
visíveis);
– Funções: todos os dados, visíveis ou não, são
considerados no cálculo (exceção para as funções da
categoria Banco de dados).
Filtrar Dados em uma Lista
• Formato geral das funções de banco de dados:
BDfunção(banco_de_dados;campo;critérios)
– banco_de_dados: é o intervalo de células que constitui
a lista ou o banco de dados;
– campo: indica a coluna que será usada na função. O
campo pode ser dado como texto com o rótulo de
coluna entre aspas, ou como um número que represente
a posição da coluna na lista;
– critérios: é uma referência a um intervalo de células que
especifica as condições para a função.
Filtrar Dados em uma Lista
• BDCONTAR: total de células que contêm
números;
• BDCONTARA: total de células não vazias;
• BDEXTRAIR: extrai um único registro que
corresponde a um critério específico;
• BDMÁX: máximo (maior) dos valores
selecionados;
Filtrar Dados em uma Lista
• BDMÉDIA: média dos valores selecionados;
• BDMÍN: mínimo (menor) dos valores
selecionados;
• BDMULTIPL: produto dos valores selecionados;
• BDSOMA: soma dos valores selecionados.
Calcular Subtotais em uma
Lista
• O Microsoft Excel pode resumir dados
automaticamente calculando valores de subtotais e
totais gerais em uma lista.
• Quando se opta por 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 ao final.
Calcular Subtotais em uma
Lista
• Subtotais: calculados através de uma função de
resumo, como Soma ou Média;
• Total geral: derivado de dados de detalhe, não dos
valores das linhas de subtotais;
• Recálculo automático: os valores de subtotais e
total geral são automaticamente recalculados à
medida que se edita os dados de detalhe.
Calcular Subtotais em uma
Lista
1.
2.
3.
4.
5.
6.
Classificar a lista pela coluna para a qual se deseja
calcular subtotais.
Clicar em uma célula da lista.
No menu Dados, clicar em Subtotais.
Na caixa A cada alteração em, clicar na coluna que
contém os grupos para os quais se deseja subtotais. Esta
deve ser a mesma coluna pela qual a lista foi classificada
na etapa 1.
Na caixa Usar função, clicar na função que se deseja
usar para calcular os subtotais.
Na caixa Adicionar subtotal a, marcar a caixa de
seleção das colunas que contêm os valores para os quais
se deseja subtotais.
Calcular Subtotais em uma
Lista
• Janela Subtotais:
Calcular Subtotais em uma
Lista
• A opção Usar função lista as funções:
– Soma: a soma dos valores em uma lista. Esta é a função
padrão para dados numéricos;
– Contar: o número de itens em uma lista. Esta é a função
padrão para dados não numéricos;
– Média: a média dos valores em uma lista;
– Máx: o maior valor em uma lista;
– Mín: o menor valor em uma lista;
– Produto: o resultado da multiplicação de todos os
valores em uma lista;
Calcular Subtotais em uma
Lista
• A opção Usar função lista as funções:
– Cont.Núms: o número de registros ou linhas em uma
lista que contém dados numéricos;
– DesvPad: uma estimativa do desvio padrão de uma
população, onde a lista é a amostra;
– DesvPadp: o desvio padrão de uma população onde a
lista é a população inteira;
– Var: uma estimativa da variância de uma população
onde a lista é a amostra;
– Varp: a variância de uma população onde a lista é a
população inteira;
Calcular Subtotais em uma
Lista
• A opção Substituir subtotais atuais remove todos os
subtotais existentes;
• A opção Quebra de página entre grupos insere
quebras de página após cada subtotal;
• A opção Resumir abaixo dos dados posiciona os
subtotais abaixo dos dados;
• O botão Remover todos encerra o modo Subtotais.
Calcular Subtotais em uma
Lista
• Observações:
– É mostrada uma estrutura de tópicos ao lado
esquerdo da coluna de cabeçalhos de linha;
– São adicionadas linhas de subtotais após cada
grupo de registros;
– É adicionada uma linha de total após a última
linha de subtotal.
Calcular Subtotais Aninhados
em uma Lista
1.
2.
3.
4.
5.
6.
7.
8.
Classificar a lista por duas ou mais colunas;
Insirir subtotais automáticos para a primeira coluna para a qual se
deseja subtotais. Esta coluna deve ser a coluna especificada na
caixa Classificar por quando a lista foi classificada;
Após ter exibido subtotais automáticos para a primeira coluna,
repetir o processo para a próxima coluna;
Posicionar o cursor de seleção em uma célula da lista;
No menu Dados, clicar em Subtotais;
Na caixa A cada alteração em, selecionar a próxima coluna que
se deseja subtotalizar;
Desmarcar a caixa de seleção Substituir subtotais atuais e clicar
em OK.
Repetir as etapas de 4 a 7 para cada coluna para a qual se deseja
calcular subtotais.
Bibliografia
• Microsoft Excel 2000 – (help)
• Desenvolvendo Macro no Excel
Steven Roman-Ed.O’Reilly.
• Excel Bible
Jonh Walkenbach-IDG Books.