SUMRIOUtilizando funes no Microsoft Excel.......................................................................................... 11 Uma Introduo s Funes Bsicas do Microsoft Excel ................................................................. 13

2

FRMULAS EM PLANILHAS DO EXCEL

FRMULAS NO EXCEL UMA INTRODUO: Como as frmulas calculam valores? Uma frmula uma equao que analisa e faz clculos com os dados em uma planilha. As frmulas efetuam operaes como adio, multiplicao e comparao em valores da planilha; alm disso, podem combinar valores. As frmulas podem referir-se a outras clulas na mesma planilha (por exemplo: A1, C25, Z34, etc), a clulas em outras planilhas da mesma pasta de trabalho ou a clulas em planilhas em outras pastas de trabalho. O exemplo a seguir adiciona o valor da clula B4 e 25 e divide o resultado pela soma das clulas D5, E5 e F5. Observe: neste exemplo, estamos utilizando a funo SOMA. Trataremos, em detalhes, sobre funes, nas demais lies deste Curso.

Sobre a sintaxe da frmula: As frmulas calculam valores em uma ordem especfica conhecida como sintaxe. A sintaxe da frmula descreve o processo do clculo. Uma frmula no Microsoft Excel comea com um sinal de igual (=), seguido do clculo da frmula. Por exemplo, a frmula a seguir subtrai 1 de 5. O resultado da frmula exibido na clula. =5-1 Obs: o sinal de menos (-) chamado de operador de subtrao. Na prxima lio falaremos mais sobre operadores. Sintaxe da frmula a estrutura ou ordem dos elementos em uma frmula. As frmulas no Microsoft Excel seguem uma sintaxe especfica que inclui um sinal de igual (=) seguido dos elementos a serem calculados (os operandos) e dos operadores de clculo. Cada operando pode ser um valor que no se altera (um valor constante), uma referncia de clula ou intervalo, um rtulo, um nome ou uma funo de planilha. Por padro, o Microsoft Excel calcula uma frmula da esquerda para a direita, iniciando com o sinal de igual (=). Voc pode controlar a maneira como os clculos so efetuados, alterando a sintaxe da frmula. Por exemplo, a frmula a seguir fornece 11 como resultado, pois o Microsoft Excel calcula a multiplicao antes da adio. A frmulaSENAC/INFORMTICA

5

multiplica 2 por 3 (tendo como resultado 6) e, em seguida, adiciona 5. =5+2*3 Por outro lado, se usar parnteses para alterar a sintaxe, voc pode adicionar primeiro 5 e 2 e, em seguida, multiplicar esse resultado por 3 para obter 21 como resultado. =(5+2)*3 Sobre as referncias da clula: Uma frmula pode referir-se a uma clula. Se voc desejar que uma clula contenha o mesmo valor que outra, insira um sinal de igual seguido da referncia da clula, por exemplo =A10; a clula onde voc inserir essa frmula ir conter o mesmo valor da clula A10. A clula que contm a frmula denominada dependente seu valor depende do valor de outra clula. Sempre que a clula qual a frmula fizer referncia for alterada, a clula que contiver a frmula ser atualizada. A frmula a seguir multiplica o valor na clula B15 por 5. A frmula ser recalculada sempre que o valor na clula B15 for alterado. =B15*5 As frmulas podem fazer referncia a clulas ou intervalos de clulas, ou a nomes ou rtulos que representem as clulas ou intervalos. Sobre as funes de planilha: O Microsoft Excel contm muitas frmulas predefinidas ou internas conhecidas como funes de planilha. As funes podem ser usadas para efetuar clculos simples ou complexos. A funo mais comum em planilhas a funo SOMA( ), que usada para somar os valores de um intervalo de clulas. Embora voc possa criar uma frmula para calcular o valor total de algumas clulas que contm valores, a funo de planilha SOMA() calcular diversos intervalos de clulas.

OPERADORES EM FRMULAS DO EXCELApresentao: nesta lio, vamos tratar sobre os principais operadores que podemos utilizar em frmulas do Excel. Os operadores especificam o tipo de clculo que voc deseja efetuar nos elementos de uma frmula. O Microsoft Excel inclui quatro tipos diferentes de operadores de clculo:

aritmticos de comparao de texto de referncia

Operadores aritmticos:6MICROSOFT EXCEL2000

Efetuam operaes matemticas bsicas como adio, subtrao ou multiplicao, combinam nmeros e produzem resultados numricos. Na tabela a seguir, temos uma descrio desses operadores utilizados em frmulas do Excel:

Operador + * / %

Descrio Adio Subtrao Multiplicao Diviso

Exemplo =B2+B3+B4 =C5-D5 =C5*2 =A20/B4

Porcentagem. Utilizado para especificar =A2*20% porcentagens. Por exemplo, para inserir o valor de ou cinco por cento em uma clula, digite o seguinte: 5% ou 0,05. =A2*0,2 Exponenciao. utilizado para elevar o primeiro =A2^B2 operando ao expoente definido pelo segundo operando. O seguinte exemplo eleva 2 no expoente 3: =2^3

^

Operadores de comparao: Comparam dois valores e produzem o valor lgico VERDADEIRO ou FALSO. Por exemplo, se utilizarmos a seguinte frmula: =F2 Maior do que Retorna verdadeiro quando o primeiro valor for maior do que o segundo. < Menor do que Retorna menor quando o primeiro valor for menor do que o segundo.SENAC/INFORMTICA

Exemplo =F2=F5

=F2>F5

=F2=

Maior ou igual a Retorna verdadeiro quando o primeiro valor for maior ou igual ao segundo.

=F2>=F5

resulta 9 =CONT.VALORES(valor1;valor2;intervalo1;...)

=CONT.SE( ) Essa funo conta de acordo com um critrio definido. Por exemplo, em uma planilha com dados sobre os funcionrios, podemos querer contar quantos funcionrios esto locados para o departamento de Contabilidade. Podemos usar a funo CONT.SE, para, a partir da coluna Seo, contar quantos funcionrios pertencem ao departamento de Contabilidade. Sintaxe: Exemplo: Se na faixa de B2 at B50 tivermos 10 vezes a palavra CONTAB, indicando que o funcionrio da Contabilidade, ento: =CONT.SE(B2:B50;"CONTAB") --> Retorna 10 NOTA: o critrio deve vir sempre entre aspas, mesmo que seja um teste numrico. Por exemplo, para contar quantos valores maiores do que 20 existem na faixa de A1 at A50, utilizamos a seguinte frmula: =CONT.SE(A1:A50;">20"). =SOMASE( ) Essa funo procura em uma coluna por determinados valores (por exemplo, procura em uma coluna pela Seo do funcionrio) e, caso encontre o valor procurado, utiliza os valores de outra coluna para ir somando. Por exemplo, em uma planilha com dados sobre os funcionrios, podemos querer somar o total de salrios para todos os funcionrios que esto locados para o departamento de Contabilidade. Podemos usar a funo SOMASE() para, a partir da coluna Seo, verificar os funcionrios que pertencem a Contabilidade (CONTAB) e somar os respectivos salrios na coluna de Salrios. Sintaxe: Exemplo: Se na faixa de B2 at B50 tivermos 10 vezes a palavra CONTAB, indicando que o funcionrio da Contabilidade, e na coluna F, de F2 at F50, tivermos as informaes16MICROSOFT EXCEL2000

=CONT.SE(FAIXA;Critrio)

=SOMASE(FAIXA_DE_TESTE;Critrio;FAIXA_VALORES_A_SOMAR)

sobre o salrio, ento: =SOMASE(B2:B50;"CONTAB";F2:F50) Retorna a soma dos salrios dos 10 funcionrios da Contabilidade. Em resumo, procura na faixa de B2:B50 pela palavra CONTAB; ao encontrar, desloca-se para a coluna F (onde est o valor dos salrios) e vai somando os valores dos salrios para os funcionrios do departamento de Contabilidade.

FUNES INTERMEDIRIAS DO EXCEL IIIApresentao: neste lio aprenderemos a utilizar as seguintes funes: ESQUERDA() DIREITA() OU() E() NO()

=ESQUERDA() Essa funo atua em valores do tipo texto. A funo esquerda retorna um determinado nmero de caracteres a partir da esquerda (incio) de uma String de Texto. Sintaxe: Exemplo: Se na clula B2 tivermos o texto "Curso Bsico de Excel 97", ento: =ESQUERDA(B2;7) --> Retorna Curso B =ESQUERDA("Todos devem Participar";4) Retorna Todo Observe que o espao em branco tambm conta como um caractere. =DIREITA() Essa funo atua em valores do tipo texto. A funo direita retorna um determinado nmero de caracteres a partir da direita (final) de uma String de Texto. Sintaxe: Exemplo: Se na clula B2 tivermos o texto Lies de Excel 2002, ento:SENAC/INFORMTICA

=ESQUERDA(String ou Endereo;Nmero de Caracteres)

=DIREITA(String ou Endereo;Nmero de Caracteres)

17

=DIREITA(B2;7) --> Retorna xcel 2002 =DIREITA("Todos Devem Participar";4) Retorna ipar Observe que o espao em branco tambm conta como um caractere. =E() Todos os argumentos devem ser verdadeiros, para que a funo retorne um valor verdadeiro.

Sintaxe: Exemplo:

=E(Argumentos)

=E(25)

--> Retorna Verdadeiro

=E(2>3;5>4)) --> Retorna Falso Tambm podemos utilizar referncia a Clulas. Por exemplo, se na Clula A5 tivermos o valor 10, teremos o seguinte: =E(A5 Retorna Verdadeiro =E(A53) =OU() Pelo menos um dos argumentos testados devem ser verdadeiros, para que a funo retorne um valor verdadeiro. A funo somente retorna falso, quando todos os argumentos testados forem falsos. Sintaxe: Exemplo: =OU(25) --> Retorna Verdadeiro =OU(Argumentos) --> Retorna Falso, pois A53;5>4)) --> Retorna Verdadeiro =OU(2>3;5 Retorna Falso

Tambm posso utilizar referncia a Clulas. Por exemplo, se na Clula A5 tivermos o valor 10, teremos o seguinte: =OU(A5 Retorna Verdadeiro =OU(A53) -> Retorna Verdadeiro =NO(3>2) -> Retorna Falso =NO(Argumento)

FUNES INTERMEDIRIAS DO EXCEL EXEMPLOS PRTICOSApresentao: a partir desta lio veremos uma srie de exemplos prticos que utilizam as funes bsicas do Excel, apresentadas nas lies anteriores. Aqui teremos um exemplo que utiliza as seguintes funes: SOMA() MDIA() MXIMO() MNIMO()

Exemplo: Vamos criar uma planilha simples, que se chamar Mdulo2.xls, na qual efetuaremos alguns clculos, e ser salva na pasta C:\Meus documentos. Para criar a planilha Mdulo 2.xls, faa o seguinte: 1. Abra o Excel (Iniciar -> Programas -> Microsoft Excel).

SENAC/INFORMTICA

19

2. Clique no boto Salvar. 3. Na clula B12 iremos retornar o valor da maior diria. Os valores das dirias esto na faixa de D5 a D10. Para determinar o maior valor dessa faixa, utilizaremos a funo Mximo(), passando essa faixa como parmetro. Para isso, na clula B12, digite a seguinte frmula: =Mximo(D5:D10) Com essa frmula estamos pedindo para o Excel que seja retornado o maior valor na faixa de clulas de D5 a D10. Importante: o nome da funo deve ser escrito com o acento; caso contrrio, ser gerado um erro. 4. Na clula B13, iremos retornar o valor da menor diria. Os valores das dirias esto na faixa de D5 a D10. Para determinar o menor valor dessa faixa, utilizaremos a funo Mnimo(), passando aquela como parmetro. Para isso, na clula B13, digite a seguinte frmula: =Mnimo(D5:D10) Com essa frmula estamos pedindo para o Excel que seja retornado o menor valor na faixa de clulas de D5 a D10. Importante: o nome da funo deve ser escrito com o acento; caso contrrio, ser gerado um erro. 5. Na clula B14, iremos retornar a soma de todas as dirias. Os valores das dirias esto na faixa de D5 a D10. Para determinar a soma dessa faixa, utilizaremos a funo Soma(), passando aquela como parmetro. Para isso, na clula B14, digite a seguinte frmula:20MICROSOFT EXCEL2000

=Soma(D5:D10) Com esta, estamos pedindo para o Excel que seja retornada a soma dos valores, na faixa de clulas de D5 a D10. 6. Na clula B15, iremos retornar a mdia aritmtica do valor das dirias. Os valores das dirias esto na faixa de D5 a D10. Para determinar a mdia aritmtica dessa faixa, utilizaremos a funo Mdia(), passando aquela como parmetro. Para isso, na clula B15, digite a seguinte frmula: =Mdia(D5:D10) Com esta, estamos pedindo para o Excel que seja retornada a mdia aritmtica dos valores, na faixa de clulas de D5 a D10.

EXERCCIO: Importante: o nome da funo deve ser escrito com o acento; caso contrrio, ser gerado um erro. 1. Efetue os seguintes clculos: 1.1) Na coluna C, calcule o valor do INSS como sendo 10% do salrio bruto (coluna B), independentemente do valor do salrio. 1.2) Na coluna D, calcule o valor do desconto para o plano de sade do funcionrio. Esse valor ser baseado no valor do salrio bruto. Para funcionrios com salrio maior ou igual a R$ 650,00, o desconto para o plano de sade ser de 15% do salrio bruto. Para salrios abaixo desse valor, o desconto ser de 10% do salrio bruto. Dica: Para efetuar esses clculos automaticamente, utilize a funo SE().

1.3) Na coluna E, calcule o valor do salrio lquido. Para isso, subtraia, do salrio bruto, os valores do desconto para o INSS e do desconto para o plano de sade. 1.4) Na clula B12, determine o valor do maior salrio lquido. Utilize a funo Mximo( ). 1.5) Na clula B13, determine o valor do menor salrio lquido. Utilize a funo Mnimo( ). 1.6) Na clula B14, determine a soma de todos os salrios lquidos. Utilize a funo Soma( ). 1.7) Na clula B15, determine a mdia aritmtica dos salrios lquidos. Utilize a funo Mdia( ). 2.Voc deve obter os resultados indicados na figura a seguir:

SENAC/INFORMTICA

21

3.Clique no boto Salvar (

).

NOTA: com base nos conceitos apresentados nas lies anteriores, tente resolver este exerccio. Na prxima lio veremos a resoluo comentada.

FUNES INTERMEDIRIAS DO EXCEL RESOLUO DE EXEMPLOSRESOLUO DO EXEMPLO 2 Exemplo 2 - Resoluo Apresentao: nesta lio, apresentaremos a resoluo do exemplo 2, enviado na lio anterior. Resoluo: 1. Na coluna C, para calcular o valor do INSS, digite a seguinte frmula:

22

MICROSOFT EXCEL2000

=B5*0,1 Estamos multiplicando o salrio bruto (coluna B) por 10 por cento (0,1). Estenda essa frmula para as demais clulas, at a linha 10. =SE(B5>=650;B5*15%;B5*10%) Observe que utilizamos a funo SE para efetuar um desconto de 15% se o salrio bruto for maior ou igual a 650, e 10%, caso contrrio. Estenda essa frmula para as demais clulas, at a linha 10. Para maiores informaes sobre a funo SE( ). Nota: para maiores informaes sobre como estender uma frmula para uma faixa de clulas 3. Na coluna E, para calcular o valor do salrio lquido, digite a seguinte frmula: =B5-C5-D5 Estenda essa frmula para as demais clulas, at a linha 10. 4. Efetue os seguintes clculos: 4.1) Na clula B12, determine o valor do maior salrio lquido. Utilize a funo Mximo. Utilize a seguinte frmula: =MXIMO(E5:E10). 4.2) Na clula B13, determine o valor do menor salrio lquido. Utilize a funo Mnimo. Utilize a seguinte frmula: =MNIMO(E5:E10). 4.3) Na clula B14, determine o valor da soma de todos os salrios lquidos. Utilize a funo Soma. Utilize a seguinte frmula: =SOMA(E5:E10). 4.4) Na clula B12, determine o valor da mdia dos salrios lquidos. Utilize a funo Mdia. Utilize a seguinte frmula: =MDIA(E5:E10). 5. Voc deve obter os resultados indicados na figura a seguir:

SENAC/INFORMTICA

23

6. Clique no boto Salvar (

).

NOTA: observe que a novidade neste exemplo a utilizao da funo SE(). Com a utilizao desta, foi possvel aplicar diferentes percentuais ao desconto do plano de sade, com base no valor do salrio bruto. Nos exemplos das prximas lies utilizaremos bastante a funo SE( ).

FUNES INTERMEDIRIAS DO EXCEL EXEMPLO DE ENDEREOS ABSOLUTOSApresentao: nesta lio, veremos mais um exemplo prtico, com a utilizao de frmulas. Tambm aprenderemos o conceito de endereo absoluto, o qual de fundamental importncia para a criao de planilhas no Excel. O exemplo proposto: vamos supor que voc esteja preparando uma planilha para calcular o valor do salrio bruto para os funcionrios da Empresa ABC Ltda. O salrio calculado com base no nmero de horas trabalhadas. O valor para horas extras diferente do valor para a hora normal. Nesta lio, criaremos a planilha Mdulo 2.xls e a salvaremos na pasta C:\Meus documentos. Para criar a planilha Mdulo 2.xls, faa o seguinte: 1. Abra o Excel (Iniciar -> Programas -> Microsoft Excel).24MICROSOFT EXCEL2000

2. Ser aberta uma pasta de trabalho em branco (Pasta1.xls). 3. Digite as informaes indicadas na figura a seguir:

MUITA ATENO PARA O CONCEITO DE ENDEREO ABSOLUTO: Para calcular o valor do salrio bruto, devemos multiplicar o nmero de horas normais pelo valor da hora normal e somar esse resultado com o resultado obtido a partir da multiplicao do nmero de horas extras pelo valor da hora extra. Para o funcionrio "Jos da Silva", que est na linha 8, utilizaramos a seguinte frmula: =B8*B4+C8*B5. B8 contm o nmero de horas normais e B4 o valor da hora normal. C8 contm o nmero de horas extras e B5 o valor da hora extra. Com essa frmula obteremos, para o funcionrio Jos da Silva, o valor 728,75. Se tentarmos copiar essa frmula para as demais linhas, iremos obter resultados incorretos, conforme indicado na figura a seguir:

SENAC/INFORMTICA

25

POR QUE ISSO ACONTECE?????? Estamos utilizando, para a linha 8, a seguinte frmula: =B8*B4+C8*B5. Ao copiarmos essa frmula para as demais linhas, a frmula passa a ser adaptada, conforme indicado na tabela a seguir:

Para a linha: 9 10 11 12 13

A frmula ser adaptada para: =B9*B5+C9*B6. =B10*B6+C10*B7. =B11*B7+C11*B8. =B12*B8+C12*B9. =B13*B9+C13*B10.

26

MICROSOFT EXCEL2000

Observe que, na medida em que vamos descendo uma linha, os nmeros das linhas vo sendo incrementados. Esse o comportamento padro do Excel quando copiamos uma frmula para uma faixa de clulas. Para o nmero de horas (colunas B e C), esse o comportamento desejado, porm, para o valor da hora extra e da hora normal, esse no o comportamento desejado. Uma vez que o valor da hora normal est fixo na clula B4, devemos sempre multiplicar o valor da coluna B (nmero de horas normais) pelo valor da clula B4. Uma vez que o valor da hora extra est fixo na clula B5, devemos sempre multiplicar o valor da coluna C (nmero de horas extras) pelo valor da clula B5. Para que os clculos fossem feitos corretamente, deveramos utilizar as frmulas indicadas na tabela a seguir:

Para a linha: 8 9 10 11 12 13

A frmula correta : =B8*B4+C8*B5 =B9*B4+C9*B5 =B10*B4+C10*B5 =B11*B4+C11*B5 =B12*B4+C12*B5 =B13*B4+C13*B5

ENTO, NESTE CASO, TEREI QUE DIGITAR AS FRMULAS UMA A UMA?????? De maneira alguma! Para evitar que isso acontea utilizamos os endereos absolutos. Quando precisamos fixar um endereo, de tal forma que, ao copiar uma frmula, o endereo da clula no seja adaptado, precisamos torn-lo um endereo absoluto. Esse o caso com os endereos das clulas B4 e B5, os quais devem ficar fixos, isto , no devem ser adaptados medida que a frmula copiada para outras clulas. Para tornar um endereo absoluto, basta colocar um sinal $ antes da letra da coluna e antes do nmero da linha. Por exemplo, para tornar B4 e B5 endereos absolutos na frmula da linha 8, s utilizar a seguinte frmula: =B8*$B$5+C8*$B$6 Feito isso, voc pode estender a frmula para as demais clulas, que os endereos absolutos no sero adaptados, conforme indicado na tabela a seguir:

Para a linha: 8 9 10

A frmula com endereo absoluto fica: =B8*$B$4+C8*$B$5 =B9*$B$4+C9*$B$5 =B10*$B$4+C10*$B$5SENAC/INFORMTICA

27

11 12 13

=B11*$B$4+C11*$B$5 =B12*$B$4+C12*$B$5 =B13*$B$4+C13*$B$5

Observe que os endereos que no so absolutos vo sendo adaptados, j os endereos absolutos se mantm inalterados medida que a frmula vai sendo copiada para as demais clulas. Por isso, para calcular o valor do salrio bruto, digite a seguinte frmula na clula D8: =B8*$B$4+C8*$B$5 Depois, s estend-la para as demais linhas. 1. Voc deve obter os resultados indicados na figura a seguir:

2. Clique no boto Salvar (

).

28

MICROSOFT EXCEL2000

Dica: Para copiar uma frmula para outra clula ou planilha, faa as seguintes coordenadas: selecione a frmula na barra de frmula com o mouse, clique com o boto direito do mouse para copiar, v clula em que deseja colar a frmula, d um clique sobre ela, agora aperte a tecla Esc, em seguida clique com o boto direito do mouse e cole. Pronto! Est colada a sua frmula... Se voc arrastasse o mouse, selecionaria todas as clulas abaixo, e no isto que voc quer?!

FUNES SE ANINHADASUtilizao Avanada da Funo SE =SE(teste;valor_verdadeiro;valor_falso) Essa funo retorna o valor definido no parmetro "valor_se_verdadeiro" se o resultado do teste for verdadeiro ou o valor definido no parmetro "valor_se_falso" se o resultado do teste for falso. Utilize a funo =SE() para fazer testes condicionais de frmulas e valores de clulas. Sintaxe: =SE(teste;valor_se_verdadeiro;valor_se_falso)

Se omitido valor_se_falso ser retornado falso. O resultado do teste determinar o valor devolvido pela funo =SE(). Os argumentos valor_se_verdadeiro e valor_se_falso podero ser qualquer valor ou teste lgico. Podem ser encadeadas at sete funes =SE() como argumentos valor_se_verdadeiro e valor_se_falso para construir testes mais elaborados. Veremos essa tcnica mais adiante. Se voc deseja emitir uma mensagem no resultado da condio, a mensagem deve ser colocada entre aspas; assim, ao invs de ser executada uma determinada frmula, ser exibida uma mensagem para o usurio. Exemplos: =SE(VERDADEIRO;1;2) retorna 1 =SE(FALSO;1;2) retorna 2 =SE(A1=650;B5*15%;B5*10%)SENAC/INFORMTICA

29

Neste exemplo, estamos testando o valor da clula B5. Se este for maior ou igual a 650, aplicamos um percentual de 15%; caso contrrio, aplicamos um percentual de 10%. Porm, nem todas as situaes so assim, to simples. Neste exemplo, temos apenas um teste: B5>=650. Existem situaes mais complexas, nas quais precisamos efetuar uma srie de testes. Um exemplo tpico seria o clculo do Imposto de Renda, o qual baseado em uma srie de faixas. Para entendermos como funciona a utilizao de funes "SE Aninhadas", vamos a um exemplo prtico. Considere a planilha indicada na figura a seguir:

O valor do desconto, para o plano de sade, ser baseado na faixa salarial, conforme descrito na tabela a seguir: Faixa salarial < 500,00 >=500 E 1000 Valor do desconto (em R$) 50 75 100

Veja que, nesta situao, no temos apenas um teste. Temos que testar trs possibilidades: salrio menor do que 500 (=500 E 1000). Na verdade, precisamos fazer apenas dois testes. Testamos se menor do que 500, depois se est entre 500 e 1000. Se nenhum dos dois testes for verdadeiro, significa que o salrio maior do que 1000 e no30MICROSOFT EXCEL2000

precisamos fazer o terceiro teste. Esta uma regra geral: "O nmero de testes igual ao nmero de faixas menos um, ou seja: trs faixas = dois testes". No nosso caso temos trs faixas, com isso teremos apenas dois testes. Outro detalhe importante a ser observado quando temos que testar uma faixa, como por exemplo: >=500 E =1000). Esta frmula serve para a clula que pede que conte o salrio entre 500 e 100. 8. Nas clulas C18, C19 e C20, utilize a funo SOMASE() para determinar a soma dos salrios para cada uma das faixas salariais. Por exemplo, na clula C18 vamos determinar a soma dos salrios de todos os funcionrios que ganham menos do que R$ 500,00. Utilizando a frmula para a soma de valores entre 500e e 100, =SOMASE(B4:B15;>500)-SOMASE(B4:B15;>=1000). 9. Feito isso, voc deve obter os resultados indicados na figura a seguir:

34

MICROSOFT EXCEL2000

10. Agora vamos salvar a planilha.(

)

11. Selecione o comando Arquivo -> Salvar como. Surge a janela Salvar como. 12. Utilize a lista Salvar em para navegar at a pasta C:\Meus documento 13. No campo Nome do arquivo:, digite Modulo 2.xls. Sua janela deve estar conforme indicado na figura a seguir:

SENAC/INFORMTICA

35

14. Clique no boto Salvar ( 15. Feche o Microsoft Excel.

):

EXEMPLO 6

Objetivo: nesta lio iremos propor um exemplo que utiliza o conceito de endereos absolutos e as seguintes funes: SOMA() MDIA() MXIMO() MNIMO() CONT.SE() SOMASE() Funes "SE Aninhadas" E()

Nesta lio criaremos a planilha Modulo 2.xls, na qual efetuaremos alguns clculos e salvaremo-la na pasta C:\Meus documentos. Para criar a planilha Modulo 2.xls, faa o seguinte: 1. Abra o Excel (Iniciar -> Programas -> Microsoft Excel).36MICROSOFT EXCEL2000

2. Ser aberta uma pasta de trabalho em branco (Pasta1.xls). 3. Digite as informaes indicadas na figura a seguir:

4. Na coluna E, vamos calcular o valor do salrio bruto. Na clula E9, digite a seguinte frmula: =C9*$B$5+D9*$B$6 Observe: a utilizao de endereos absolutos para as clulas $B$5 (valor da hora normal) e $B$6 (valor da hora extra). Estenda essa frmula para as demais linhas. 5. Utilize funes SE Aninhadas para determinar o valor do desconto para o INSS, na coluna F, de acordo com os critrios da tabela a seguir:

SENAC/INFORMTICA

37

Salrio Bruto < 500 >=500 E 1000 Na clula F9, digite a seguinte frmula:

Desconto do INSS (em R$) 35 65 90

=SE(E9=500;E9 Salvar como. Surgir a janela indicada na figura a seguir:

SENAC/INFORMTICA

59

3. D um clique no boto Opes. Na janela que surge (conforme indicado na figura a seguir), voc pode definir uma senha para leitura (senha de proteo) e tambm uma senha para gravao/alterao (senha de gravao). Se for definida apenas a senha de proteo, a senha ser solicitada na abertura da planilha. Se voc fornecer uma incorreta, a planilha no ser carregada. Se voc definir apenas a de gravao, a senha ser solicitada no momento da abertura da planilha. Se voc no fornecer a senha de gravao ou fornecer uma incorreta, a planilha ser carregada, porm, no poder ser alterada. Na figura a seguir, definiremos uma senha de proteo e tambm uma de gravao:Marcando aqui sempre criar um Backup automtico

No poder salvar as mudanas, s se mudar o nome do arquivo.

4. Aps digitar as senhas, d um clique no boto OK. Ser exibida uma janela pedindo confirmao para a senha de proteo. Digite a senha novamente e d um clique em OK. Surgir uma janela pedindo a confirmao da senha da gravao. Digite-a novamente e d um clique em OK. Voc estar de volta janela Salvar como. D um clique no boto Salvar. Voc estar de volta planilha.60MICROSOFT EXCEL2000

Nota: as senhas de gravao e proteo no precisam ser iguais. 5. Feche a planilha.

6. Abra a planilha novamente e observe que, em primeiro lugar, ser solicitada a senha de proteo. Digite-a, conforme indicado na figura a seguir, e d um clique no boto OK:

Se voc no digitar a senha ou digitar uma incorreta, a planilha no ser aberta. 7. Em seguida, ser solicitada a senha de gravao. Digite-a, conforme indicado na figura a seguir, e d um clique no boto OK:

Se voc no souber a senha, pode clicar no boto Somente leitura. A planilha ser aberta, porm, no podero ser feitas alteraes. 8. Se voc no quiser mais utilizar senhas em uma planilha, utilize o comando Arquivo -> Salvar como. Na janela que surge, d um clique no boto Opes e defina as duas senhas em branco. Salve a planilha. Na prxima vez que a planilha for aberta, no sero mais solicitadas as senhas de proteo e gravao.

MACROS E OUTRAS OPERAES DO EXCEL 2000SENAC/INFORMTICA

61

Macro um programa gerado pelo usurio, cujo contedo so instrues armazenadas exclusivamente para aes a serem feitas. Com o Macro, o usurio pode executar vrias aes ao mesmo tempo, simplesmente clicando em botes ou executando um comando, diminuindo consideravelmente seu trabalho. Nossa primeira macro tem como funo inserir uma nova planilha e formatar determinadas clulas com cores de procedimentos a seguir: 1. Abra o menu Ferramentas, posicione o apontador em Macro e clique sobre o comando Gravar Nova Macro; 2. Na caixa exibida, voc pode: 3. Nomear a macro, digitando seu nome na caixa de entrada Nome da Macro. Para o exemplo, foi digitado Macro_Teste; 4. Definir um atalho de teclas para macro, comeando por CTRL. Apenas digite o caractere que, em conjunto com essa tecla, far a macro agir; 5. Definir o local de armazenamento da macro. 6. Clique no boto OK. A rea de trabalho do Excel ser visualizada, juntamente com a presena de uma pequena barra denominada Parar macro. Ela ser necessria para concluir as aes que comporo as instrues da macro:

Barra de Gravao

Dica Quando voc abrir uma pasta de trabalho que contenha uma macro, aparecer uma mensagem de aviso, pedindo-lhe que ative ou desative todas as macros da pasta de trabalho. Desativar as macros o ajudar a reduzir o risco de que um vrus de macro, que pode estar, por exemplo, em uma pasta de trabalho de uma rede ou site da Web inseguros, danifique seus arquivos ou computador. Como o Excel no determina de fato se uma macro na pasta de trabalho contm um vrus, certifique-se de que a fonte da pasta de trabalho seja confivel, ou passe um antivrus. 1. Abra o menu Inserir e clique no comando Planilha;62MICROSOFT EXCEL2000

2. Escolha aleatoriamente clulas da planilha e aplique diferentes cores de fundo para eles; 3. Selecione o intervalo A1:D10, formate-o como Moeda; 4. Clique no boto Gravao na Barra de Gravao, concluindo a gravao da macro. Executando a macro Para executar a macro vamos seguir estes passos: Abra o menu Ferramentas e, no submenu Macro, clique no comando Macros; Na caixa exibida, selecione o nome a ser executado e, para isso, clique no boto :

Caixa de edio e manipulao de macros

Os cdigos da macro podem ser no s visualizados como tambm alterados pelo usurio. Execute os procedimentos a seguir para entender melhor: Tecle ATL F8, abrindo a caixa de dilogos Macro; Selecione o nome da macro desejada e clique no boto Editar. Esse boto faz com que seja aberto o Editor do Visual Basic, linguagem de programao da qual se constituem as macros do Excel:

SENAC/INFORMTICA

63

Janela do Visual Basic

Por essa janela, o usurio com experincia e conhecimento em Visual Basic poder alterar quaisquer caractersticas e aes da macro atravs desse editor. Pode-se criar um boto de atalho que execute a macro desejada. Na verdade, esse boto tambm constitudo por um conjunto de instrues em Visual Basic e est vinculado diretamente macro. Acione a barra de ferramentas Formulrios. Essa barra possui botes e outros elementos que podem ser inseridos na planilha, com funes especficas; esses elementos vo desde botes de atalho, passando por menus desdobrveis, at botes de rdio e caixas de verificao:

Barra de ferramentas Formulrios

Clique na ferramenta de boto representada na barra pelo cone . O apontador do mouse toma a forma de uma pequena cruz. Pressione e arraste o mouse na diagonal, mais ou menos no local onde deseja que o boto fique posicionado; Aps a colocao do boto na planilha, a caixa de dilogos Atribuir macro se torna64MICROSOFT EXCEL2000

disponvel. Pode-se atribuir um nome ou aproveitar outro j existente; Ao retornar planilha, clique em qualquer outra clula, a fim de tirar a seleo do objeto:

Boto de Macro

Exerccio 1. Crie uma planilha de Rendimento de Cliente, no menu Ferramentas e d um clique em Personalizar. Aparecer a caixa de dilogo Personalizar; 2. Na guia Barra de ferramentas, na lista Barras de ferramentas, role para baixo e desmarque a caixa de verificao Macros. D um clique em Fechar; 3. Certifique-se de que selecionada pelo menos uma clula da planilha Rendimento de Cliente; 4. No menu Ferramentas, aponte para Macro e d um clique em Gravar nova macro. Aparecer a caixa de dilogo Gravar macro; 5. Na caixa Nome da macro, digite Aut_Open e d um clique em OK. Aparecer a barra de ferramentas Parar gravao; 6. No menu Ferramentas, d um clique em Personalizar e d um clique na guia Barras de ferramentas, se for necessrio; 7. Na caixa Barras de ferramentas, certifique-se de que so selecionadas as caixas Padro, Formatao, Parar gravao e Barra de menus da planilha. Selecione a caixa de verificao Macro e d um clique em Fechar; 8. Na Barra de ferramentas Parar gravao d um clique no boto Parar gravao. A macro Auto_Open ser registrada.

CONFIGURANDO BOTES DA MACRO

SENAC/INFORMTICA

65

Como vimos anteriormente, podemos inserir botes de macro em nossa planilha. Agora iremos inserir botes que nos permita ir de uma planilha para outra sem necessitar utilizar as guias de planilha para alternar entre elas.

1. Clique na opo Boto que se encontra na Barra de Formulrio. 2. V com o cursor do mouse para qualquer lugar da planilha e d um clique com o boto direito do mouse. Automaticamente aparecer esta tela.

3. Clique no boto Novo Automaticamente aparecer a tela do Microsoft Visual Basic.

66

MICROSOFT EXCEL2000

4. Esta tela a do Visual Basic, onde podemos notar que existe um cursos piscando entre dois comando Sub Boto1_Clique ( ) e End Sub escreva a seguinte palavra Plan2, no final coloque um ponto, aparecer esta tela.

5. D um duplo clique na Opo , esta tela desaparecer e este boto ter a funo de ir para a 2 planilha de sua planilha principal. Este comando fez com que este boto ficasse ativo e quando pressionada fossemos enviados para a planilha 2.

TRABALHANDO COM BOTO DE OPONa Barra de Formulrios podemos tambm inserir botes de seleo. Com ele, voc pode selecionar apenas uma opo como verdadeira, mostrando assim um resultado que o esperado.

1. Clique no boto boto de Opo que esta na barra de Formulrios, v com o curso do mouse para a planilha e d um clique em qualquer lugar. 2. Clique com o boto direito do mouse no boto que voc acabou de criar e aparecer uma janela igual a que esta na figura abaixo.

SENAC/INFORMTICA

67

3. Na opo Valor coloque Desmarcado, na opo Vnculo da Clula, coloque a clula B3. No trmino clique em OK. 4. Repita esta operao colocando outros botes e verifique que a cada boto adicionado a um nmero correspondente.

Criaremos uma tabela vinculada a uma base dados access, e faremos uma consulta utilizando os comandos do Excel. Importao de dados: Para importar dados de um banco de dados externo basta seguir os seguintes passos: 1. passo: Clique no menu dados e escolha a opo obter dados externos: Escolha a opo Criar nova consulta como na figura abaixo:

68

MICROSOFT EXCEL2000

Temos a opo Executar consulta salva : essa opo s servir se j tivssemos criado a consulta, a consulta na realidade servi para indicar de qual base de dados e tabela que os dados sero importados . Na opo Nova consulta Web : tem como funo permitir a transferncia de dados armazenado em um servido na net. Importar arquivo de texto: o Excel capaz de transferir informaes de arquivos TXT para dentro de suas planilhas. Vai surgir as seguinte tela ao clicar em criar nova consulta:

Aqui voc poder escolher o banco de dados a qual a sua tabela que voc deseja importar, caso no esteja relacionada nos itens acima clique na primeira opo e OK. Com exemplo vamos escolher a opo Banco de dados MS Access: Vai aparecer a seguinte tela abaixo:

SENAC/INFORMTICA

69

Aqui o usurio escolhe a arquivo .MDB Aps a escolha feita , teremos que indicar a tabela o os campos os quais sero importados para o Excel de acordo com a figura abaixo:

Mostra os campos da tabela

Passa os campos para a janela da direita informando assim que ele ser importado

Aps a escolha dos campos clique em avanar Vai surgir a seguinte tela:70MICROSOFT EXCEL2000

Aqui voc poder filtrar os campos , atravs de condies:

Clique novamente em avanar: Surgira a tela abaixo:

Nesta tela o usurio pode definir o tipo de classificao (Crescente ou Decrescente) de um determinado campo. Ao termino escolha avanar Surgira:

SENAC/INFORMTICA

71

A primeira opo o aplicativo de consulta de base de dados ser fechado e os dados sero retornado ao Excel. Na segunda opo ele abrir o aplicativo Ms-query para podermos manipular os dados. Vamos ficar com a segunda opo. Aparecera a seguinte tela:

O MS Query tem como finalidade de servir como elo de ligao entre o aplicativo e a base dados, atravs possvel sempre importar , exportar, manipular os dados, fazer consultas e outras funes. nele que aprenderemos a consultar atravs dos comandos de sql. Clique no boto72MICROSOFT EXCEL2000

SQL na barra de ferramenta.

COMANDOS SQLSELECT inclui o programa principal do banco de dados para retornar a informao como um conjunto de registro:Sintaxe: SELECT [predicado {* |tabela.* |[tablea.campo as alias ....,]}]

FROM [TABELA] Especifica a tabela o qual os campos especificados no commando select pertencem WHERE - Serve para determinar condies para que os dados possam ser mostrados ORDER BY Classifica os dados Exemplo: Mostre os campos Cdigo, Funcionrio , salrio da tabela Pessoal. Select cdigo,funcionrio, salrio From pessoal Exemplo2: Mostre todos os campos da tabela pessoal cujo o cargo seja Diretor e o salrio maior do R$ 1.000,000 e classifique em ordem de nome crescente Select * From pessoal Where cargo=Diretor and salrio=1000 Order by nome Exemplo3: Esse exemplo mostra qual seria o salrio se cada funcionrio recebesse um aumento de 10%. No altera o original do salrio. Select cdigo,nome,salrio, salrio*10/100 as abono , ((salrio*10)/100+salrio) as Total From pessoal

SENAC/INFORMTICA

73

Exemplo4 Mostra aumento de 10% no salrio das pessoas que possuam JOS em nome: Select nome, salrio as atual , salrio*1.1 as Corrigido From pessoal Where nome LIKE %+jose+%

Obs: Os comandos aqui representados so comandos unicamente de consultas Aps filtrar os dados de acordo com as nossas necessidades clique no menu arquivo e escolha a opo retornar dados ao Excel, ir aparecer a seguinte tela:

Escolha a clula que deseja inserir os dados e clique em ok Aparecera a seguinte tela:

Permite o acesso a consulta MS-Query

Atualiza os dados da base de dados

Selecione a rea e daremos um novo nome chamando-a de INTERVALO como mostra a figura74MICROSOFT EXCEL2000

abaixo:

Colocamos abriremos uma nova planilha e tiraremos a sua grade com os seguintes passos: 1. Clique em EXIBIR 2. escolha a opo Barra de ferramenta 3. D um clique em FORMULARIO 4. D um clique no seguinte boto

Faremos a seguinte tela:

Foram utilizados os botes Caixa de Combinao, caixa de grupo, boto de comando e boto deSENAC/INFORMTICA

75

opo: Daremos funo a cada um deles: Primeiro a caixa de combinao : Clique com o boto direito sobre ela: Escolha a ultima opo Formatar Controle.

A seguinte tela ira surgir:Escolha o intervalo de entrada, ou seja, Plan1!B2:B9 Vamos vincular a clula cdigo Plan2!$b3 Nmero de items a ser mostrado na lista, caso ultrapasse ser exibida uma barra de rolagem

76

MICROSOFT EXCEL2000

Para mostrar o salrio do item selecionado vamos utilizar a funo PROCV PROCV( ) : Funo de procurar um valor em um intervalo especificado PROCV( valor, intervalo, coluna, val. Lgico) Valor: O valor a ser procurado Intervalo: Local onde o valor deve ser localizado Coluna: Coluna de retorno dos dados Val. Lgico: So dois valores VERDADEIRO ou FALSO Digitaremos a seguinte formula no campo salrio: =Procv(b3,intervalo, 3,verdadeiro)onde b3 e o valor do cdigo a ser digitado na planilha 2 , intervalo criamos ele logo no inicio da apostila, quando selecionamos a rea e criamos o nome intervalo , o numero 3 indica a terceira coluna da planilha 1, ou seja, que ira retornar com o salrio . Lembrando que deveremos fazer isso com uma tabela onde tenha os campos cdigo, funcionrio e salrio que no e o nosso caso, pois o exemplo citado acima na importao de dados tem campos totalmente diferentes nesse caso, voc ter que realizar o processo de importao novamente com uma tabela com os campos citados. Caso no haja, teremos que criar essa tabela l no Access.

Vamos colocar a funo no boto, ele ter finalidade de retornar a planilha 1. D um clique no boto com o boto direito do mouse e escolha a opo ATRIBUIR MACRO. Ir aparecer a seguinte tela:

Nome da macro

D um clique no boto novo. E surgira a seguinte tela:SENAC/INFORMTICA

77

Aqui a rea de programao , onde a linguagem utilizada ser visual basic

Digite Plan1. e aparecer a seguinte tela

No final desta operao o Boto ir retornar para a Plan1.

Escolha a opo Activate e feche tudo.

78

MICROSOFT EXCEL2000

REFERNCIAS BIBLIOGRFICASCITRANGULO, Marcelo Rosin. Livro Passo a Passo Microsoft Excel 2000. So Paulo: Makron Books, 2000. SENAC/DN. Rogrio Massaro Suriani. Excel 2000. So Paulo: Senac, 2000.

SITES VISITADOS1000Ways Excel & cia www.1000ways.com.br/excel Ecurso Cursos de Excel XP www.ecurso.com.br Excel www.geocities.com/capecanaveral/8906/ Excel 2000 Intermedirio Store.learn2.com/basket Excel: Microsoft Press www.microsoft.com/brasil/mspress/subjects/subjectal_pl.html IDG Now Descoberta nova falha no Excel idgnow.terra.com.Br/idgnow/pcnews/2002/ 05/0055 Interao Tecnolgica Curso www.itct.org/apostilas/excel/excel.html TIC no ensino A Estatstica no Excel 2000 www.ticensino.com www.ekuka.kit.net/apostilas.htm

SENAC/INFORMTICA

79