Tipos de Referência. Referência Relativa $A$1. Referência Absoluta. Referência Mista. $A1 ou A$1

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

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

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

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

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.

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

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

A

B

C

31

32

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;proc urar_intervalo)

PROCV

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

=PROCH(5;A1:I3;2) -> e =PROCH(5;A1:I3;3) -> maio

1 2 3 4 5 6 7 8 9

a b c d e f g h i

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>,…)

….

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

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

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

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 Repetição

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

VBA Repetição

• 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

VBA Repetição

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

VBA Repetição Condicional

While –Wend

While<Condição> ...

VBA Repetição Condicional

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

• 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

Mensagens

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,

Mensagens

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

Mensagens (Retorno)

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

Mensagem - 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

Mensagem

Sub caixa()

MsgBox

“Continua?", vbQuestion + vbYesNo

End Sub

Caixa de Entrada

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

VBA

Sub pedidoNome() Dim nome As String

nome = InputBox("Digite o seu nome.", "Nome", "Natália") End Sub

Funções de Texto

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

VBA – Célula Ativa

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

VBA – Faixa

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 .

Exemplo

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

Células

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

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.

Objeto WorkBook

Método Open

(O exemplo seguinte abre o arquivo Array.xls como

uma pasta de trabalho somente leitura.)