Excel Avançado - bibliotecaonlineead.com.brbibliotecaonlineead.com.br/logsys/cursos/apostilas/excel-avancado... · seguido de um parêntese de abertura, os argumentos da função

Excel Avançado

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 Refere-se às células como posições fixas neste caso não há

$A$1

Absoluta posições fixas neste caso não há alteração ao copiar a célula.

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 SOMA(B5:B15;D5:D15); (ponto-e-vírgula) Operador de união, que combina diversas referências em uma referência

SOMA(B5:B15;D5:D15)

Operadores AritméticosOperador aritmético Significado

+ (sinal de adição) Adição

– (sinal de subtração) SubtraçãoNegaçã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 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 valoresAs 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 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.• 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 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.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 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

• ExemplosSe 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(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:valor. A função possui três argumentos:

1. A condição a ser verificada, 2. O valor a ser retornado se a condição for verdadeira 3. 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


Valor_se_verdadeiro é o valor retornado se teste_lógico for VERDADEIRO.

• Por exemplo, se esse argumento for a seqüência de caracteres • 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


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 "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, 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: 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 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);"")

ERetorna 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 • 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 VERDADEIROE(VERDADEIRO; FALSO) é igual a FALSOE(VERDADEIRO; FALSO) é igual a FALSOE(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. 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 • 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

• ExemplosOU(VERDADEIRO) é igual a VERDADEIRO

OU(1+1=1;2+2=5) é igual a FALSO

Se o intervalo A1:A3 contiver os valoresVERDADEIRO, 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,002 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).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 B C

31 Vendedor Total de Pedidos Total de Vendas

32 Manuel 13 =SOMASE(A2:A5,A32,B2:B5)32

33 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.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.– 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.ERRORetorna 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, 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

• ExemploA 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 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),"Ranges do not intersect","The divisor is zero"))

Val_erroSe val_erro for TIPO.ERRO retornará

#NULO! 1

#DIV/0! 2

#VALOR 3#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 à 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)

PROCV1 a Jan

2 b Fev

3 c Mar

4 d Abr

5 e Mai5 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ê 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;procurar_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

Jan Fev Mar Abr Maio Jun Jul Ago Set

ARRED

Arredonda um número até uma quantidade especificada de dígitos.

• Sintaxe

• ARRED(núm;núm_dígitos)• 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 Núm será arredondado para o número

específicado de casas decimais.

=0

Núm será arredondado para o inteiro mais próximo

<0 Núm será arredondado para esquerda da

=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

<0 Núm será arredondado para esquerda da vírgula decimal

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 • 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 Núm será arredondado para baixo pelo

número específicado de casas decimais.

=0

Núm será arredondado para baixo até o inteiro mais próximo.

<0 Núm será arredondado para baixo à

Exemplos=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

<0 Núm será arredondado para baixo à esquerda da vírgula decimal

ARREDONDAR.PARA.CIMA

Arredonda um número para cima afastando-o de zero.

SintaxeARREDONDAR.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 Núm será arredondado para cima pelo

número específicado de casas decimais.

=0

Núm será arredondado para cima até o inteiro mais próximo.

<0 Núm será arredondado para cima à esquerda

Exemplos=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

<0 Núm será arredondado para cima à esquerda da vírgula decimal

INT

Arredonda um número para baixo até o número inteiro mais próximo.

• Sintaxe

• INT(núm)• 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.– 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.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.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.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 FunctionEnd 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)NF=NO+(3*ND)

End Function

Funções

• Onde definir?

– No Editor de VBA, no excel escolha no Menu Ferramentas/Macro/Editor de VBA.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


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 precisão simples) 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.

VBATipo de dados Tamanho de

armazenamentoIntervalo

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,9228162514264337593543950335 com 28 casas decimais à direita; o menor número diferente de zero é +/-0,0000000000000000000000000001.

VBA


Intervalo

Date 8 bytes De 1 de janeiro de 100 a 31 de dezembro de 99999999

Objeto 4 bytes Qualquer referência Object

String(comprimento variável

10 bytes + comprimento da seqüência

De 0 a aproximadamente 2 bilhões

Variant(com números)

16 bytes Qualquer valor numérico até o intervalo de um Double

VBA


Intervalo

Variant(com caracteres)

22 bytes + comprimento da

O mesmo intervalo de String de (com caracteres) comprimento da

seqüênciade String de comprimento variável

Definido pelo usuário (usando Type)

Número requerido por elementos

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 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 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)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.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.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 …[Case Else[Instruções a realizar na situação residual]]

End Select

VBA ControlFunction C_Etaria(Idade)

Select Case Idade

Case faixa<3

C_etaria=“Bebê”

Case faixa<13

C_etaria=“Criança”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=1acumulado=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=1I=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.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.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 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 CancelarvbCancel 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 + vbYesNoMsgBox “Continua?", vbQuestion + vbYesNo

End Sub

VBA(InputBox)

• InputBox

– A função InputBox é projetada para entrada do usuário.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”)sNome=InputBox(“Digite o seu nome.”,”Nome”,”Vinicius”)

VBA(string)Função Exemplo Retorna

Len Len(“January Invoice”) 15

Ucase MsgboxUcase(“Donna”) DONNA

Lcase MsgboxLcase(“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:– 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 ApplicationRepresenta 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 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 RangeRepresenta 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 RangeAs seguintes propriedades e métodos para retornar um As seguintes propriedades e métodos para retornar um objeto Range são descritas nesta seção:

Propriedade RangePropriedade CellsRange e CellsPropriedade OffsetMé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.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élulado intervalo.

• Quando usada sem um qualificador de objeto (um objeto à • 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 objetoexplí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 CellsUse 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 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 —With anterior for aplicado à propriedade Cells —nesse caso, para indicar que as células estão na planilha um (sem o ponto, a propriedade Cellsretornaria as células da planilha ativa).

VBA(Objeto Range )

• Método ActivateAtiva o objetoSintaxeSintaxeexpressão.ActivateExemplo 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.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.primeira janela, BOOK4.xls:1.

Workbooks("BOOK4.XLS").Activate

VBA(Objeto Range )

Propriedade OffsetUse 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ê à 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 UnionUse 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 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çãoworkbooks. workbooks.

• A coleção Workbooks contém todos os objetosWorkbook atualmente abertos no Microsoft Excel.


Método Add

(adiciona uma nova pasta de trabalho vazia ao Microsoft Excel.)

WorkBookObjeto.Add(template)WorkBookObjeto.Add(template)

Método Close

(O exemplo seguinte fecha todas as pastas de trabalho abertas.)

WorkBookObjeto.Close


Método Open

(O exemplo seguinte abre o arquivo Array.xls como uma pasta de trabalho somente leitura.)

Workbooks.Open fileName:="array.xls", readOnly:=TrueWorkbooks.Open fileName:="array.xls", readOnly:=True


• Propriedade ActiveWorkbook

A propriedade ActiveWorkbook retorna a pasta de trabalho que está ativa no 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"


• Propriedade ThisWorkbookA 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 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.


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.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;• 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)AutoConclusão para valores de células)

– Mouse (menu de atalho/Lista de opções...)

• Usando um formulário (Dados/Formulário)


• Janela do formulário:


• 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;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);


• 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);– 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.


• 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. 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 AutoFiltro;

3. 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;

4. Clicar em um dos valores da lista apresentada;

5. Para aplicar uma condição adicional baseada em um valor de outra coluna, repetir as etapas 3 e 4 na outra coluna.


• Observações:– A barra de status mostra a quantidade de linhas

selecionadas;

– A seta da coluna muda de cor indicando que a lista está – 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.


• A lista de valores apresentada pela seta da coluna inclui as opções:

– (tudo): mostra todos os itens (retira condição);– (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.


• Pode ser usada qualquer combinação de valores de colunas diferentes, selecionando-se um valor em cada coluna;se um valor em cada coluna;

• Para utilizar mais uma condição em uma coluna, deve-se utilizar a opção (Personalizar...).


• Janela Personalizar AutoFiltro:


• Características da janela Personalizar AutoFiltro:

– A opção (Personalizar...) permite filtrar os dados de várias maneiras: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 ?).


• Janela AutoFiltro – 10 primeiros:


• 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:– 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;filtragem;

– Especificar critério de filtragem computado;

– Extrair uma cópia dos registros que satisfazem ao critério para um outro local.


• 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;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.


Mês Vendedor Tipo Total

Janeiro novo

Fevereiro >1000Fevereiro >1000

(Mês = “Janeiro”) AND (Tipo = “novo”)

OR

(Mês = “Fevereiro”) AND (Total > 1000)


• Observações:

=“=Janeiro”

>R>R

< >R

Ro*

r*r

R?r


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…; 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.


• Janela 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.


• Ao utilizar a opção Somente registros exclusivosda janela Filtro avançado:

– Os registros que satisfizerem ao critério de – 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.


• Observações:

– A barra de status mostra a quantidade de linhas selecionadas;selecionadas;

– Os cabeçalhos de linha dos registros filtrados mudam de cor;

– Dados/Filtrar/Mostrar todos encerra o modo Filtro avançado.


• Critérios computados:

– Filtram uma lista baseados em um ou mais cálculos;cálculos;

– Não utilizam cabeçalhos de coluna da lista, mas sim novos;

– Essencialmente criam novos campos para a lista.


Duração

TRUE

Projeto Início Término Recursos

= C5 – B5 + 1 >= 30


• 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;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.


• Observações:

– Gráficos: apenas os dados visíveis são considerados no gráfico (Ferramentas/Opções.../Plotar apenas células 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).


• 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 – 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.


• BDCONTAR: total de células que contêm números;

• BDCONTARA: total de células não vazias;• 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;


• BDMÉDIA: média dos valores selecionados;

• BDMÍN: mínimo (menor) dos valores selecionados;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.


• 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 • 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.


1. Classificar a lista pela coluna para a qual se deseja calcular subtotais.

2. Clicar em uma célula da lista.

3. No menu Dados, clicar em Subtotais.3. No menu Dados, clicar em Subtotais.

4. 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.

5. Na caixa Usar função, clicar na função que se deseja usar para calcular os subtotais.

6. Na caixa Adicionar subtotal a, marcar a caixa de seleção das colunas que contêm os valores para os quais se deseja subtotais.


• Janela Subtotais:


• 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 – 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;


• 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 – 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;


• A opção Substituir subtotais atuais remove todos os subtotais existentes;

• A opção Quebra de página entre grupos insere • 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.


• Observações:

– É mostrada uma estrutura de tópicos ao lado esquerdo da coluna de cabeçalhos de linha;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. Classificar a lista por duas ou mais colunas;2. 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;

3. Após ter exibido subtotais automáticos para a primeira coluna, 3. Após ter exibido subtotais automáticos para a primeira coluna, repetir o processo para a próxima coluna;

4. Posicionar o cursor de seleção em uma célula da lista;5. No menu Dados, clicar em Subtotais;6. Na caixa A cada alteração em, selecionar a próxima coluna que

se deseja subtotalizar;7. Desmarcar a caixa de seleção Substituir subtotais atuais e clicar

em OK.8. Repetir as etapas de 4 a 7 para cada coluna para a qual se deseja

calcular subtotais.

Documents

Excel Avançado - bibliotecaonlineead.com.brbibliotecaonlineead.com.br/logsys/cursos/apostilas/excel-avancado... · seguido de um parêntese de abertura, os argumentos da função