Excel Avançado, Notas de estudo de Engenharia Química

Baixe Excel Avançado e outras Notas de estudo em PDF para Engenharia Química, somente na Docsity!

Licenciamento de Uso

Este documento é propriedade intelectual © 2002 do Centro de Computação da Unicamp e distribuído sob os seguintes termos:

1. As apostilas publicadas pelo Centro de Computação da Unicamp podem ser reproduzidas e distribuídas no todo ou em parte, em qualquer meio físico ou eletrônico, desde que os termos desta licença sejam obedecidos, e que esta licença ou referência a ela seja exibida na reprodução.
2. Qualquer publicação na forma impressa deve obrigatoriamente citar, nas páginas externas, sua origem e atribuições de direito autoral (o Centro de Computação da Unicamp e seu(s) autor(es)).
3. Todas as traduções e trabalhos derivados ou agregados incorporando qualquer informação contida neste documento devem ser regidas por estas mesmas normas de distribuição e direitos autorais. Ou seja, não é permitido produzir um trabalho derivado desta obra e impor restrições à sua distribuição. O Centro de Computação da Unicamp deve obrigatoriamente ser notificado ([email protected]) de tais trabalhos com vista ao aperfeiçoamento e incorporação de melhorias aos originais.

Adicionalmente, devem ser observadas as seguintes restrições:

• A versão modificada deve ser identificada como tal
• O responsável pelas modificações deve ser identificado e as modificações datadas
• Reconhecimento da fonte original do documento
• A localização do documento original deve ser citada
• Versões modificadas não contam com o endosso dos autores originais a menos que autorização para tal seja fornecida por escrito.

A licença de uso e redistribuição deste material é oferecida sem nenhuma garantia de qualquer tipo, expressa ou implícita, quanto a sua adequação a qualquer finalidade. O Centro de Computação da Unicamp não assume qualquer responsabilidade sobre o uso das informações contidas neste material.

Descrição

Mostrar alguns recursos avançados como por exemplo: Funções Gerais, Funções Financeiras e Lógicas, Consolidar Dados e Subtotais, Proteger de Planilhas, Criar e usar Macros e Compartilhamento de planilhas.

Operadores de cálculos em fórmulas

Quatro tipos de operadores são símbolos que identificam o tipo de cálculo.

Para efetuar operações matemáticas básicas. Operadores aritméticos

Operador aritmético Significado

+ Adição

- Subtração

• Multiplicação

/ Divisão

% Percentagem

^ Exponenciação

Para comparar 2 valores e obter como resultado os valores lógicos de verdadeiro ou falsos Operadores de comparação Operador de comparação Significado

= Igual a

Maior do que

< Menor do que

= Maior ou igual a

<= Menor ou igual a

<> Diferente

Use o símbolo & concatenar 2 seqüências de caracteres Operador de concatenação de texto

Operador de texto Significado

& Une dois valores para produzir um valor de texto contínuo

Combine intervalos de células para cálculos com os seguintes operadores. Operadores de referência

Operador de referência Significado

: Operador de intervalo, que produz uma referência a todas as células entre duas referências, incluindo as duas referências

; Operador de união, que combina diversas referências em uma referência

Usar funções Gerais

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

Sintaxe: ARRED(núm;núm_dígitos) Num é o número que se deseja arredondar. Num_digitos especifica o número de dígitos para o qual você deseja arredondar núm.

0 núm será arredondado para o número especificado de casas decimais. =0 núm será arredondado para o inteiro mais próximo <0 núm será arredondado para a esquerda da vírgula decimal

Exemplos ARRED(2,15; 1) é igual a 2, ARRED(2,149; 1) é igual a 2, ARRED(-1,475; 2) é igual a -1, ARRED(21,5; -1) é igual a 20

Arredondar – ARRED

Calcula o número de células não vazias em um intervalo que corresponde a determinados critérios.

Sintaxe : CONT.SE (intervalo;critérios)

Intervalo é o intervalo de células no qual se deseja contar células não vazias. Critérios é o critério na forma de um número, expressão ou texto que define quais células serão contadas.

Exemplos Suponha que A3:A6 contenha "maçãs", "laranjas", "pêras", "maçãs", respectivamente. CONT.SE (A3:A6;"maçãs") é igual a 2 Suponha que B3:B6 contenha 32, 54, 75, 86, respectivamente. CONT.SE(B3:B6;">55") é igual a 2

Condicional – SE

Retorna um valor se uma condição que você especificou avaliar como VERDADEIRO e um outro valor se for avaliado como FALSO.

Sintaxe: 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. Valor_se_verdadeiro é o valor retornado se teste_lógico for VERDADEIRO. Valor_se_verdadeiro pode ser outra fórmula Valor_se_falso é o valor retornado se teste_lógico for FALSO. Valor_se_falso pode ser outra fórmula.

• Até sete funções SE podem ser aninhadas como argumentos valor_se_verdadeiro e valor_se_falso para construir testes mais elaborados.
• Quando os argumentos valor_se_verdadeiro e valor_se_falso são avaliados, SE retorna o valor que foi retornado por estas instruções.

Exemplos: Suponha que em A1 tenho a média de um aluno. Se o valor da média for menor do que 5,0 a célula da fórmula recebe Aprovado, caso contrário Reprovado. SE(A1>5;"Aprovado";"Reprovado")

Condicional – SOMASE

Adiciona as células especificadas por um determinado critério. Sintaxe :SOMASE(intervalo;critérios;intervalo_soma) Intervalo é o intervalo de células que se deseja calcular. Critérios São os critérios na forma de um número, expressão ou texto, que define quais células serão adicionadas. Intervalo_soma São as células que serão realmente somadas. As células em intervalo_soma são somadas somente se suas células correspondentes em intervalo coincidirem com os critérios estipulados. Se intervalo_soma for omitido, as células em intervalo serão somadas.

Exemplo: Suponha que A1:A4 contém os seguintes valores de propriedade para quatro casas: R$ 100.000, R$ 200.000, R$ 300.000, R$ 400.000, respectivamente. B1:B4 conterá as seguintes comissões de vendas em cada um dos valores de propriedade correspondentes: R$ 7.000, R$ 14.000, R$ 21.000, R$ 28.000. SOMASE(A1:A4;">160000";B1:B4) é igual a R$ 63. Comen

Localiza um valor na primeira coluna à esquerda de uma tabela e retorna um valor na mesma linha de uma coluna especificada na tabela.

Sintaxe PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;procurar_intervalo) Valor_procurado é o valor a ser procurado na primeira coluna da matriz. Valor_procurado pode ser um valor, uma referência ou uma seqüência de caracteres de texto. é a tabela de informações em que os dados são procurados. Use uma referência para um intervalo ou nome de intervalo, tal como Banco de dados ou Lista.

• Se procurar_intervalo for VERDADEIRO, os valores na primeira coluna de matriz_tabela deverão ser colocados em ordem ascendente: ..., -2, -1, 0, 1, 2, ... , A-Z, FALSO, VERDADEIRO; caso contrário, PROCV pode não retornar o valor correto. Se procurar_intervalo for FALSO, matriz_tabela não precisará ser ordenada.
• Você pode colocar os valores em ordem ascendente escolhendo o comando Classificar no menu Dados e selecionando Crescente.

Matriz_tabela

• Os valores na primeira coluna de matriz_tabela podem ser texto, números ou valores lógicos. e Textos em maiúsculas e minúsculas são equivalentes. Núm_índice_coluna é o número da coluna em matriz_tabela a partir do qual o valor correspondente deve ser retornado. Um núm_índice_coluna de 1 retornará o valor na primeira coluna em matriz_tabela; um núm_índice_coluna de 2 retornará o valor na segunda coluna em matriz_tabela, e assim por diante. Se núm_índice_coluna for menor do que 1, PROCV retornará o valor de erro #VALOR!; se núm_índice_coluna for maior do que o número de colunas em matriz_tabela, PROCV retornará o valor de erro #REF!. Procurar_intervalo é um valor lógico que especifica se você quer que PROCV encontre a correspondência exata ou uma correspondência aproximada. Se VERDADEIRO ou omitida, uma correspondência aproximada é retornada; em outras palavras, se uma correspondência exata não for encontrada, o valor maior mais próximo que é menor que o valor_procurado é retornado. Se FALSO, PROCV encontrará uma correspondência exata. Se nenhuma correspondência for encontrada, o valor de erro #N/D é retornado.

Se PROCV não localizar valor_procurado e procurar_intervalo for VERDADEIRO, ela usará o maior valor que for menor do que ou igual a valor_procurado.

Procurar um valor específico em uma lista de valores - PROCV

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.

Sintaxe PROCH(valor_procurado;matriz_tabela;núm_índice_lin;procurar_intervalo) Valor_procurado é o valor a ser localizado na primeira linha da tabela. Valor_procurado pode ser um valor, uma referência ou uma seqüência de caracteres de texto. é uma tabela de informações onde os dados devem ser procurados. Use uma referência para um intervalo ou um nome de intervalo.

• Os valores na primeira linha de matriz_tabela podem ser texto, números ou valores lógicos.
• Se procurar_intervalo for VERDADEIRO, os valores na primeira linha de matriz_tabela deverão ser colocados em ordem ascendente: ...-2, -1, 0, 1, 2,... , A-Z, FALSO, VERDADEIRO, caso contrário, PROCH pode não retornar o valor correto. Se procurar_intervalo for FALSO, matriz_tabela não precisará ser ordenada.
• Textos com maiúsculas ou minúsculas são equivalentes.

Matriz_tabela

• Você pode colocar valores em ordem ascendente, da esquerda para a direita, selecionando os valores, escolhendo o comando Classificar no menu Dados. Clique em Opções , clique em Classificar da esquerda para a direita e, em seguida, em OK. Em Classificar por , clique na linha da lista e depois em Crescente. Núm_índice_lin é o número da linha em matriz_tabela de onde o valor correspondente deve ser retirado. Um núm_índice_lin equivalente a 1 retorna o valor da primeira linha na matriz_tabela, um núm_índice_lin equivalente a 2 retorna o valor da segunda linha na matriz_tabela, e assim por diante. Se núm_índice_lin for menor do que 1, PROCH retornará o valor de erro #VALOR!; se núm_índice_lin for maior do que o número de linhas na matriz_tabela, PROCH retornará o valor de erro #REF!. Procurar_intervalo é um valor lógico que especifica se você quer que PROCH localize uma correspondência exata ou aproximada. Se VERDADEIRO ou omitido, uma correspondência aproximada é retornada. Em outras palavras, se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor_procurado é retornado. Se FALSO, PROCH encontrará uma correspondência exata. Se nenhuma correspondência for localizada, o valor de erro #N/D será retornado.
• Se PROCH não localizar valor_procurado, e procurar_intervalo for VERDADEIRO, ela usará o maior valor que é menor do que o valor_procurado.
• Se o valor_procurado for menor do que o menor valor na primeira linha de matriz_tabela, PROCH retornará o valor de erro #N/D.

Exemplos: Suponha que você tenha uma planilha de inventário de peças de automóveis. A1:A4 contém "Eixos", 4, 5, 6. B1:B4 contêm "Rolamentos", 4, 7, 8. C1:C4 contêm "Parafusos", 9, 10, 11.

PROCH("Eixos"; A1:C4;2;VERDADEIRO) é igual a 4 PROCH("Rolamentos"; A1:C4;3;FALSO) é igual a 7 PROCH("Rolamentos";A1:C4;3;VERDADEIRO) é igual a 7 PROCH("Parafusos";A1:C4;4;) é igual a 11

PROCH

Usar funções Financeiras e lógicas

Para especificar as taxas e o NPER ser consistente quanto às unidades usadas. Se fizer pagamentos mensais por um empréstimo de quatro anos com juros de 12% ao ano, utilizar taxa=12%/12 e Nper=4*12. Se fizer pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para nper.

Retorna o valor presente de um investimento. O valor presente é o valor total correspondente ao valor atual de uma série de pagamentos futuros. Por exemplo, quando você pede dinheiro emprestado, o valor do empréstimo é o valor presente para quem empresta.

Sintaxe : VP(taxa;nper;pgto;vf;tipo) Taxa é a taxa de juros por período. Por exemplo, se você obtiver um empréstimo para um carro com uma taxa de juros de 10% ao ano e fizer pagamentos mensais, a sua taxa de juros mensal será 10%/12, ou 0,83%. Você deve inserir 10%/12, ou 0,83%, ou 0,0083, na fórmula como taxa. Nper é o número total de períodos de pagamento de uma anuidade. Pgto é o pagamento feito a cada período e não pode mudar durante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros, e não há outras tarifas ou taxas. Por exemplo, os pagamentos mensais por um empréstimo para o carro de R$ 10.000 de quatro anos a 12% são R$ 263,33. Você deve inserir -263,33 na fórmula como pgto. Se pgto for omitido, você deverá incluir o argumento vf. Vf é o valor futuro, ou um saldo de caixa, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, é 0). Por exemplo, se quiser economizar R$ 50.000 para pagar um projeto especial em 18 anos, então R$ 50.000 é o valor futuro. Você pode então calcular a taxa de juros e determinar quanto deverá economizar a cada mês. Se vf for omitido, você deverá incluir o argumento pgto. tipo Tipo é o número 0 ou 1 e indica as datas de vencimento. Definir tipo para Se os vencimentos forem 0 ou omitido No final do período 1 No início do período

Calcular o valor presente de um investimento - VP

Retorna o valor futuro de um investimento de acordo com os pagamentos periódicos e constantes e com uma taxa de juros constante.

Taxa é a taxa de juros por período. Nper é o número total de períodos de pagamento em uma anuidade

Pgto é o pagamento feito em cada período; não pode mudar durante a vigência da anuidade.Geralmente, pgto contém o capital e os juros, mas nenhuma outra tarifa ou taxas. Se pgto for

omitido, você deverá incluir o argumento vp. Vp é o valor presente ou a soma total correspondente ao valor presente de uma série de pagamentos futuros. Se vp for omitido, será considerado 0 (zero) e a inclusão do argumento pgto será obrigatória Vf é o valor futuro, ou saldo, que você deseja obter após o último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). Tipo Tipo é o número 0 ou 1 e indica as datas de vencimento. Definir tipo para Se os vencimentos forem 0 ou omitido No final do período 1 No início do período

Sintaxe : VF(taxa;nper;pgto;vp;tipo) Todos os argumentos, pagamentos feitos, como depósitos em poupança, são representados por números negativos; depósitos recebidos, como cheques de dividendos, são representados por números positivos.

Exemplos VF(0,5%; 10; -200; -500; 1) é igual a $2.581, VF(1%; 12; -1000) é igual a $12.682, VF(11%/12; 35; -2000; 1) é igual a $82.846,

Calcular o valor futuro de um investimento VF

Retorna o valor absoluto de um número. O valor absoluto de um número é o próprio número sem o respectivo sinal. Sintaxe : ABS(núm) Núm é o número real do qual você deseja obter o valor absoluto.

Exemplos:

ABS(2) igual a 2 ABS(-2) igual a 2

Retorna o número de períodos para investimento de acordo com pagamentos constantes e periódicos e uma taxa de juros constante.

Exibir o valor absoluto de um número - ABS

Calcular o número total de um investimento - NPER

Calcular a taxa de juros de uma parcela – Taxa(rate)

Retorna a taxa de juros por período de uma anuidade. TAXA é calculada por iteração e pode ter zero ou mais soluções. Se os resultados sucessivos de TAXA não convergirem para 0,0000001 depois de 20 iterações, TAXA retornará o valor de erro #NÚM!.

Sintaxe: TAXA(nper;pgto;vp;vf;tipo;estimativa) Nper (^) é o número total de períodos de pagamento em uma anuidade. Pgto é o pagamento feito em cada período e não pode mudar durante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros e nenhuma outra taxa ou tributo. Se pgto for omitido, você deverá incluir o argumento vf. Vp (^) é o valor presente — o valor total correspondente ao valor atual de uma série de pagamentos futuros. Vf (^) é o valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). Tipo Tipo é o número 0 ou 1 e indica as datas de vencimento. Definir tipo para Se os vencimentos forem 0 ou omitido No final do período 1 No início do período

Estimativa é a sua estimativa para a taxa.

• Se você omitir estimativa, este argumento será considerado 10%.
• Se TAXA não convergir, atribua valores diferentes para estimativa. Em geral, TAXA converge se estimativa estiver entre 0 e 1.

Exemplo: Para calcular a taxa de um empréstimo de quatro anos de $8.000 com pagamentos mensais de $200: TAXA(15; -12100; 150000) é igual a 2,48% esta taxa é mensal. A taxa anual é 2,48%*12, que é igual a 30%.

Calcular o valor de uma prestação inicial PGTO

Retorna o pagamento periódico de uma anuidade de acordo com pagamentos constantes e com uma taxa de juros constante.

Taxa é a taxa de juros por período. Nper (^) é o número total de pagamentos pelo empréstimo. Vp (^) é o valor presente—o valor total presente de uma série de pagamentos futuros. Vf (^) é o valor futuro, ou o saldo de caixa, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, 0). Tipo Tipo é o número 0 ou 1 e indica as datas de vencimento. Definir tipo para

Se os vencimentos forem 0 ou omitido No final do período 1 No início do período

Sintaxe : PGTO(taxa;nper;vp;vf;tipo)

• O pagamento retornado por PGTO inclui o principal e os juros e não inclui taxas, pagamentos de reserva ou tarifas, às vezes associados a empréstimos.

Modos de exibição e Cenários

1. No menu Exibir , clique em Personalizar modos de exibição.
2. Na caixa Modos de exibição , clique no nome da exibição desejada.
3. Clique em Mostrar.

Exibição personalizada Modos de exibição personalizados alteram a maneira como pastas de trabalho, planilhas, objetos e janelas são exibidas. Pode-se definir conjuntos específicos de configurações de impressão e exibição e salvá-los como modos de exibição. Em seguida, você pode alternar para qualquer um dos modos de exibição sempre que desejar exibir ou imprimir a pasta de trabalho de maneira diferente. As configurações armazenadas incluem larguras das colunas, opções de exibição, tamanho e posição da janela na tela, divisões da janela ou painéis congelados, a planilha que está ativa e as células que são selecionadas no momento em que o modo de exibição é criado.Pode-se também salvar opcionalmente linhas e colunas ocultas, configurações de filtro e de impressão. O modo de exibição personalizado inclui a pasta de trabalho inteira. Se você ocultar uma planilha antes de adicionar uma exibição, o Microsoft Excel ocultará a planilha sempre que você mostrar o modo de exibição.

Antes de criar um modo de exibição Configure a pasta de trabalho como você deseja que ela seja exibida e impressa. Se você incluir configurações de impressão em uma exibição, a exibição incluirá a área de impressão definida ou a planilha inteira se esta não tiver uma área de impressão definida.

Excluir uma exibição

1. No menu Exibir , clique em Personalizar modos de exibição.
2. Na caixa Modos de exibição , clique no nome da exibição.
3. Clique em Excluir.

Modos de Exibição

Um cenário é um conjunto de valores que o Microsoft Excel salva e pode substituir automaticamente na sua planilha. Você pode usar cenários para prever o resultado de um modelo de planilha. Pode-se criar e salvar diferentes grupos de valores em uma planilha e alternar para qualquer um desses novos cenários para exibir resultados diferentes.

Criar um cenário

1. No menu Ferramentas , clique em Cenários.
2. Clique em Adicionar.
3. Na caixa Nome do cenário , digite um nome para o cenário.
4. Na caixa Células variáveis , insira as referências para as células que você deseja alterar.
5. Em Proteção , selecione as opções desejadas.
6. Clique em OK.
7. Na caixa de diálogo Valores de cenário , digite os valores desejados para as células variáveis.
8. Para criar o cenário, clique em OK.

Para criar cenários adicionais, clique em Adicionar e, em seguida, repita as etapas 3-7. Quando acabar de criar cenários, clique em OK e, em seguida, clique em Fechar na caixa de diálogo Gerenciador de cenários.

Dica Para preservar os valores originais das células variáveis, crie um cenário que utilize os valores das células originais antes de criar cenários que alterem os valores.

Criar um relatório de resumo de cenário No menu Ferramentas , clique em Cenários. Clique em Resumir. Clique em Resumo do cenário ou Tabela dinâmica do cenário. Na caixa Células de resultado , insira as referências para as células que fazem referência a células cujos valores são alterados pelos cenários. Separe as diversas referências por vírgulas.

Cenários