Funções

Utilização avançada do Microsoft Excel
Apontamentos sobre as funcionalidades avançadas mais
importantes para a manipulação e tratamento de listas
Introdução ...................................................................................................................... 2
Funções .......................................................................................................................... 2
Funções estatísticas no Excel ..................................................................................... 2
Função IF ................................................................................................................... 3
Funções de análise de erro ......................................................................................... 3
Funções matemáticas ................................................................................................. 3
Funções de pesquisa ................................................................................................... 4
Funções de bases de dados ......................................................................................... 5
Auditing ..................................................................................................................... 5
Pivot Table ..................................................................................................................... 6
Ligação a outros ficheiros e folhas ................................................................................ 8
Programação simples ..................................................................................................... 9
Introdução
O Microsoft Excel é uma ferramenta bastante poderosa que permite a manipulação de
dados e que permite tirar proveito da grande capacidade cálculo dos computadores.
Quando usado em combinação com o SAP permite manipular e combinar os dados
extraídos do SAP de uma forma bastante fléxivel o que facilita a obtenção dos valores
pretendidos. Além disso o Excel é muito mais flexível, intuitivo e poderoso no que diz
respeito à criação de gráficos do que o SAP.
Estes apontamentos sucintos visam utilizadores de Excel que conhecem as suas
funcionalidades básicas e que pretendem tirar proveito das funcionalidades avançadas
por forma a tirar estatísticas diversas com base em listagens no Excel. São indicadas
as funções mais úteis mas o utilizador terá que consultar a ajuda do próprio Excel para
saber quais os parâmetros necessários para cada função. (Ao escrever uma fórmula se
carregar no = existente à esquerda vai ter uma ajuda na introdução da fórmula).
Funções
Funções estatísticas no Excel
As fórmulas em Excel começam sempre por um sinal + ou por um sinal de =. A
seguir à indicação da função deve ser indicado o conjunto de células sobre o qual se
pretendem efectuar os cálculos. O conjunto de células pode ser continuo ou
descontinuo. Por exemplo SUM(A1:A10;C10:C14). Para se indicar conjuntos
descontinuos com o rato usar a tecla CTRL ao mesmo tempo.
Para qualquer conjunto de valores existem algumas funções básicas no Excel.
SUM
Calcula a soma das células
AVERAGE
Calcula a média dos valores das células
COUNT
O número de células que têm valores
COUNTA
O número de células não vazias
MAX
O valor máximo no conjunto de células
MIN
O valor mínimo no conjunto de células
STDEV
O desvio padrão do conjunto de células
2
Função IF
A função IF permite efectuar cálculos de uma forma diferente de acordo com uma
expressão.
A sua forma é IF( condição; valor se verdadeira; valor se falsa).
Funções de análise de erro
Estas funções permitem com a função IF verificar se existem valores incorrectos e
colocar novos valores. Por exemplo =+IF( ISBLANK(A1);0;A1)
ISBLANK
Verdadeiro se a célula estiver vazia
ISERROR
Verdadeiro se erro (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!,
#NAME?, or #NULL!).
ISNONTEXT
Verdadeiro se não for texto
ISNUMBER
Verdadeiro se for um número
ISTEXT
Verdadeiro se for texto
Funções matemáticas
ABS
Valor absoluto
COUNTIF
Conta o número de células que obedeçem a uma condição
INT
Devolva a parte inteira de um número
SIGN
Retorna –1, 0 ou +1 de acordo com o sinal
SUMIF
Soma os valores das células que obedeçam a uma condição
SUMPRODUCT
Dá o valor da soma do produto de 2 colunas
Para arredondamento de números existem ainda as funções CEILING, FLOOR,
ROUND, ROUNDDOWN, ROUNDUP, TRUNC
3
Funções de pesquisa
As funções de pesquisa são essenciais no relacionamento de valores de uma lista com
outra lista.
CHOOSE
Permite escolher um valor de uma lista
COLUMN
Indica o número da coluna
HLOOKUP
Pesquisa na horizontal um valor e retorna o valor que se encontra n
linhas abaixo.
INDEX
Permite indicar dentro de uma lista qual a linha e coluna da qual se
pretende obter o valor
INDIRECT
Serve para fazer uma referência indirecta a uma célula. Se forem
apagadas ou deslocadas células a referência mantêm-se
LOOKUP
Igual ao HLOOKUP e VLOOKUP mas em vez de procurar um
valor específico retorna o valor correspondente ao valor mais
próximo
MATCH
Funciona de forma semelhante ao LOOKUP mas retorna a posição
do item
ROW
Retorna o número da linha
VLOOKUP
Pesquisa na vertical um valor e retorna o valor que se encontra n
colunas à direita
4
Funções de bases de dados
As funções de bases de dados requerem 3 partes:

Indicação do bloco correpondente à base de dados. No cimo de cada bloco é
necessário que esteja indicado o nome da coluna

Indicação do campo sobre o qual se pretende efectuar a operação

Indicação do critério de pesquisa
As funções correspondem as funções matemáticas existentes anteriormente.
Os critérios são definidos através da utilização de células com a indicação do nome do
campo por cima e do critério por baixo.
DAVERAGE
Returns the average of selected database entries
DCOUNT
Counts the cells that contain numbers in a database
DCOUNTA
Counts nonblank cells in a database
DMAX
Returns the maximum value from selected database entries
DMIN
Returns the minimum value from selected database entries
DSTDEV
Estimates the standard deviation based on a sample of selected
database entries
DSUM
Adds the numbers in the field column of records in the database that
match the criteria
Auditing
Quando começa a encher uma folha de cálculo de fórmulas complexas por vezes
torna-se complicado ver de onde é que resulta cada valor. Nesses casos deverá usar a
função TOOLS AUDITING. Com esta função consegue ver através de setas com que
células está uma célula relacionada.
5
Pivot Table
A funcionalidade Pivot Table é das funcionalidades mais potentes do Excel e permite
efectuar um tratamento de dados potente e multidimensional. Mesmo a nível de bases
de dados como o Access não tem esta capacidade.
Seleccionando a tabela faz-se DATA PIVOT TABLE REPORT.
Carregando Next neste ecrã e no seguinte onde se definem os dados que estão a ser
tidos em conta tem-se:
6
Aqui pode-se escolher o que se pretende ter nas linhas e o que se pretende ter nas
colunas. Em Data será os dados que se pretende. Para tal arrasta-se o título das
colunas para os locais correspondentes e no caso dos valores um duplo clic permite
indicar a função de agragação pretendida.
A Page permite gerar uma página por cada valor do campo aí colocado.
Carregando em NEXT 2 vezes vai-se obter a página seguinte. Nesta imagem é
possível ver os totais agregados de acordo com o que foi especificado.
Através da Toolbar do Pivot Table é possível indicar o critério de ordenação que se
pretende, os valores que se pretendem esconder e ainda se pode fazer agrupamentos
diversos dos valores. É ainda possível rever os campos e valores que se pretendem
visualizar.
É ainda possível acrescentar campos e valores calculados a estas tabelas.
7
O ponto de exclamação permite fazer o refrescamento e faz com que a tabela seja
recalculada de acordo com as alterações que tenham sido efectuadas no sistema de
base.
Ligação a outros ficheiros e folhas
No Excel pode estabelecer ligação a outros ficheiros. Para tal a forma mais fácil é de
abrir os 2 ficheiros entre os quais pretende estabelecer ligação e depois criar a fórmula
num ficheiro com referência ao outro ficheiro.
A fórmula ficará do tipo +[Book1]Sheet1!$E$7 onde Book1 é o nome do ficheiro e
Sheet é o nome da folha.
No caso de várias folhas ficará só +Sheet1!$E$7.
Para mudar entre vários ficheiros no Excel poderá usar o CTRL + F6 ou o menu
Window.
Para mudar entre várias páginas pode usar CTRL+PG DN ou CTRL+ PG UP ou o
rato.
8
Programação simples
O Excel permite efectuar programação em Visual Basic para automatizar uma série de
tarefas.
No entanto para quem começa a forma mais fácil é usar o gravador de macros e a
partir daí fazer a programação que pretenda.
Para gravar uma macro o que tem a fazer é:
1. TOOLS – MACROS – RECORD NEW MACRO.
2. Indicar o nome da macro a gravar
3. Executar os passos que se pretendem tornar automáticos
4. Carregar em TOOLS – MACROS – STOP RECORDING ou no botão de parar
A partir deste ponto tem já criado um ficheiro com um programa em Visual Basic que
pode em seguida editar fazendo TOOLS – MACROS – MACRO… - EDIT.
Para editar programas assim criados é conveniente ter instalado no seu computador o
Help do Excel sobre programação.
O exemplo abaixo foi gerado automaticamente e copia o bloco de células A1:C13
para E1 e coloca na célula H3 o valor 3.
Sub Macro1()
Range("A1:C13").Select
Selection.Copy
Range("E1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("H3").Select
ActiveCell.FormulaR1C1 = "3"
End Sub
Pode igualmente criar macros para abrir outros ficheiros de Excel e copiar os dados
para outra folha ou gerar gráficos automaticamente.
9