Excel para a tomada de decisões

além de fórmulas, outra maneira de conduzir computações matemáticas no Excel é através de funções. Funções estatísticas aplicam um processo matemático a um grupo de células em uma planilha. Por exemplo, a função SUM é usada para adicionar os valores contidos em um intervalo de células. Uma lista de funções estatísticas comumente usadas é mostrada no quadro 2.4. As funções são mais eficientes do que as fórmulas quando você está aplicando um processo matemático a um grupo de células. Se você usar uma fórmula para adicionar os valores em uma gama de células, você teria que adicionar cada localização da célula para a fórmula um de cada vez. Isso pode ser muito demorado se você tiver que adicionar os valores em algumas centenas de locais de células. No entanto, quando você usa uma função, você pode destacar todas as células que contêm valores que você deseja somar em apenas um passo. Esta seção demonstra uma variedade de funções estatísticas que vamos adicionar ao manual de orçamento pessoal. Além de demonstrar Funções, Esta seção também analisa por cento dos cálculos totais e o uso de referências absolutas.

Quadro 2.4, Comumente Usado Funções Estatísticas

A função SOMA é usado quando você precisa para calcular totais para um intervalo de células ou um grupo de células selecionadas em uma planilha. No que diz respeito à folha de cálculo de detalhes do orçamento, utilizaremos a função SUM para calcular os totais na linha 12. É importante notar que existem vários métodos para adicionar uma função a uma folha de trabalho, que serão demonstrados ao longo do restante deste capítulo. O seguinte ilustra como uma função pode ser adicionada a uma folha de trabalho, digitando-a em um local de célula:

1. carregue na página de detalhes do orçamento para abrir a folha de trabalho.
2. clique na célula C12.
3. tipo um sinal igual =.
4. Digite a soma do nome da função.
5. tipo um parêntesis aberto (.
6. clique na célula C3 e arraste para a célula C11. Isto coloca o intervalo C3: C11 na função.
7. Tipo A parêntesis de Fecho ).
8. pressione a tecla ENTER. A função calcula o total para a coluna de gastos mensais, que é $1,496.

a figura 2.11 mostra a aparência da função SOMA adicionada à folha de cálculo de detalhes do orçamento antes de carregar na tecla ENTER.

como mostrado na figura 2.11, a função de soma foi adicionada à célula C12. No entanto, esta função também é necessária para calcular os totais nas colunas de gastos anuais e gastos LY. A função pode ser copiada e colada nestas localizações de células por causa da referenciação relativa. Referenciação relativa serve o mesmo propósito para funções como faz para fórmulas. O seguinte demonstra como a linha total é concluída:

1. clique na célula C12 na folha de cálculo de detalhes do orçamento.Carregue no botão Copiar na página inicial da fita.
2. células de realce D12 e E12.Carregue no botão Colar na página inicial da fita . Isto cola a função SOMA nas células D12 e E12 e calcula os totais para estas colunas.
3. clique na célula F11.Carregue no botão Copiar na página inicial da fita.
4. carregue na célula F12 e depois carregue no botão Colar na página inicial da fita. Como agora temos totais na linha 12, podemos colar a fórmula de alteração percentual nesta linha.

a figura 2.12 mostra a saída da função SOMA que foi adicionada às células C12, D12 e E12. Além disso, a fórmula de Mudança percentual foi copiada e colada na célula F12. Note – se que esta versão do orçamento está a planear uma diminuição de 1,7% nas despesas em comparação com o ano passado.

referências absolutas (calculando percentagem dos totais)

ficheiro de dados: continuar com o orçamento pessoal CH2.

uma vez que os totais foram adicionados à linha 12 da folha de cálculo de detalhes do orçamento, um por cento do cálculo total pode ser adicionado à coluna B começando na célula B3. A percentagem do cálculo total mostra a percentagem para cada valor na coluna de gastos anuais em relação ao total na célula D12. No entanto, após a fórmula ser criada, será necessário desligar a funcionalidade de referenciação relativa do Excel antes de copiar e colar a fórmula para o resto das localizações de células na coluna. Desligar o recurso relativo de referência do Excel é realizado através de uma referência absoluta. Os seguintes passos explicam como isso é feito:

1. carregue na célula B3 na folha de cálculo dos detalhes do orçamento.
2. tipo um sinal igual =.
3. clique na célula D3.
4. Type a forward slash /.
5. clique na célula D12.
6. pressione a tecla ENTER. Os Serviços Domésticos representam 16,7% do orçamento anual de despesas (ver Figura 2.14).

Figura 2.14 mostra a fórmula completa que está calculando a porcentagem que o gasto anual dos Serviços Domésticos representa para o gasto anual total para o orçamento (ver célula B3). Normalmente, copiaríamos esta fórmula e a colaríamos no intervalo B4:B11. No entanto, por causa da referenciação relativa, ambas as referências de células aumentarão em uma linha como a fórmula é colada nas células abaixo de B3. Isto é bom para a primeira referência da célula na fórmula (D3), mas não para a segunda referência da célula (D12). A figura 2.15 ilustra o que acontece se colarmos a fórmula no intervalo B4:B12 em seu estado atual. Observe que o Excel produz o código #DIV / 0 de erro. Isso significa que o Excel está tentando dividir um número por zero, o que é impossível. Olhando para a fórmula na célula B4, você vê que a primeira referência da célula foi alterada de D3 para D4. Isso é bom, porque agora queremos dividir a despesa anual para o seguro pelo total da despesa anual na célula D12. No entanto, o Excel também mudou a referência da célula D12 para D13. Como a localização da célula D13 está em branco, a fórmula produz o código #DIV/0 de erro.

To eliminate the divide-by-zero error shown in Figure 2.15 we must add an absolute reference to cell D12 in the formula. Uma referência absoluta impede a referenciação relativa de mudar uma referência de célula em uma fórmula. Isto também é referido como bloquear uma célula. O seguinte explica como isso é realizado:

1. duplo clique na célula B3.
2. coloque o cursor do rato na frente do D12 e clique. O cursor intermitente deve estar à frente do D na célula de referência D12.
3. pressione a tecla F4. Você verá um sinal de Dólar ( $ ) adicionado na frente da letra da coluna D e da linha número 12. Você também pode digitar os sinais do dólar na frente da letra da coluna e número da linha.
4. pressione a tecla ENTER.
5. clique na célula B3.Carregue no botão Copiar na página inicial da fita.
6. realçar o intervalo B4: B11.Carregue no botão Colar na página inicial da fita .

a figura 2.16 mostra a percentagem da fórmula total com uma referência absoluta adicionada a D12. Observe que na célula B4, a referência da célula permanece D12 em vez de mudar para D13, como mostrado na figura 2.15. Além disso, você verá que as porcentagens estão sendo calculadas no resto das células da coluna, e o erro de dividir por zero é agora eliminado.

a função de contagem

ficheiro de dados: Continue com o orçamento pessoal CH2.

a próxima função que vamos adicionar à folha de cálculo de detalhes do orçamento é a função de contagem. A função de contagem é usada para determinar quantas células em um intervalo contêm um item numérico. A função de contagem não funcionará para contar texto ou outros itens não-numéricos. Para a folha de cálculo de detalhes do orçamento, usaremos a função de contagem para contar o número de itens que estão planejados na coluna de gastos anuais (coluna D). O seguinte explica como a função de contagem é adicionada à planilha usando a lista de funções:

1. clique na célula D13 na folha de cálculo de detalhes do orçamento.
2. tipo um sinal igual =.
3. Digite a letra C.
4. clique na seta para baixo na barra de rolagem da lista de funções (ver Figura 2.17) e encontre a contagem de palavras.
5. faça duplo-click na contagem de palavras da lista de funções.
6. realçar o intervalo D3: D11.
7. você pode digitar um parêntesis de fecho ) e, em seguida, pressione a tecla ENTER, ou simplesmente pressione a tecla ENTER e o Excel irá fechar a função para você. A função produz uma saída de 9, uma vez que existem 9 itens planejados na planilha.

Figura 2.17 mostra a lista de funções que aparece após completar os passos 2 e 3 para a função de contagem. A lista de funções fornece um método alternativo para adicionar uma função a uma planilha.

figura 2.18 mostra a saída da função de contagem após pressionar a tecla ENTER. A função conta o número de células no intervalo D3:D11 que contêm um valor numérico. O resultado de 9 indica que existem 9 categorias previstas para este orçamento.

a função MÉDIA

a próxima função que adicionaremos à folha de cálculo de detalhes do orçamento é a função MÉDIA. Esta função é usada para calcular a média aritmética para um grupo de números. Para a folha de cálculo de detalhes do orçamento, vamos usar a função para calcular a média dos valores na coluna de gastos anuais. Vamos adicionar isso à planilha usando a biblioteca de funções. Os seguintes passos explicam como isso é realizado:

1. clique na célula D14 na folha de cálculo de detalhes do orçamento.
2. clique na página fórmulas da fita.
3. carregue no botão Mais funções no grupo de comandos da biblioteca de funções.
4. coloque o cursor do rato sobre a opção estatística a partir da lista de opções.
5. clique no nome médio da função na lista de funções que aparecem no menu (Ver figura 2.19). Isto abre a janela de argumentos das funções.
6. carregue na janela de colapso na janela de argumentos da Função (ver Figura 2.20).
7. realçar o intervalo D3: D11.
8. carregue no botão Expandir a janela na janela argumentos da Função (ver Figura 2.21). Você também pode pressionar a tecla ENTER para obter o mesmo resultado.
9. carregue no botão OK na janela argumentos da função. Isto adiciona a função MÉDIA à planilha.

a figura 2.19 ilustra como uma função é seleccionada da biblioteca de funções na página fórmulas da fita.

a figura 2.20 mostra a janela de argumentos da função. Isto aparece depois que uma função é selecionada da biblioteca de funções. O botão da janela de colapso é usado para esconder a janela, para que uma série de células possa ser realçada na folha de trabalho e depois adicionada à função.

figura 2.21 mostra como uma gama de células pode ser seleccionada a partir da janela argumentos da função, uma vez que tenha sido colapsada.

Figura 2.22 mostra a janela de argumentos da função, a seguir à definição do intervalo de células para a função MÉDIA. A janela mostra o resultado da função antes de ser adicionada à localização da célula. Isso permite que você avalie o resultado da função para determinar se faz sentido antes de adicioná-lo à planilha.

a figura 2.23, mostra a função MÉDIA completa na folha de cálculo de detalhes do orçamento. A saída da função mostra que, em média, esperamos gastar $1,994 para cada uma das categorias listadas na Coluna a do orçamento. Este cálculo médio de gastos por categoria pode ser usado como um indicador para determinar quais categorias estão custando mais ou menos do que o gasto médio orçamentado Dólares.

as funções MAX E MIN

ficheiro de dados: continuar com o orçamento pessoal CH2.

as duas funções estatísticas finais que vamos adicionar à folha de cálculo de detalhes do orçamento são as funções MAX E MIN. Estas funções identificam os valores mais altos e mais baixos numa gama de células. As seguintes etapas explicam como adicionar estas funções à folha de cálculo de detalhes do orçamento:

1. clique na célula D15 na folha de cálculo de detalhes do orçamento.
2. tipo um sinal igual =.
3. Digite a palavra MIN.
4. tipo um parêntesis aberto (.
5. realçar o intervalo D3: D11.
6. digite um parêntesis de fecho ) e pressione a tecla ENTER, ou simplesmente pressione a tecla ENTER e o Excel irá fechar a função para você. A função MIN produz uma saída de US $1.200, que é o menor valor na coluna de gastos anuais (ver Figura 2.24).
7. clique na célula D16.
8. tipo um sinal igual =.
9. Digite a palavra MAX.
10. tipo um parêntesis aberto (.
11. realçar o intervalo D3: D11.
12. digite um parêntesis de fecho ) e pressione a tecla ENTER, ou simplesmente pressione a tecla ENTER e o Excel irá fechar a função para você. A função MAX produz uma saída de $3,500. Este é o valor mais elevado da coluna de gastos anuais (ver Figura 2.25).

copiar e colar fórmulas (colagem sem formatos)

ficheiro de dados: continuar com o orçamento pessoal do CH2.

como mostrado na figura 2.25, as funções contagem, média, MIN e MAX estão resumindo os dados na coluna de gastos anuais. Você também vai notar que há espaço para copiar e colar essas funções sob a coluna de gastos de LY. Isto permite-nos comparar o que gastámos no ano passado e o que planeamos gastar este ano. Normalmente, nós simplesmente copiaríamos e colaríamos essas funções no intervalo E13: E16. No entanto, você pode ter notado a fronteira de estilo de linha dupla que foi usado em torno do perímetro da faixa B13:E16. Se usássemos o comando regular Paste, a linha dupla do lado direito do intervalo E13:E16 seria substituída por uma única linha. Portanto, vamos usar um dos comandos especiais Paste para colar apenas as funções sem nenhum dos tratamentos de formatação. Isto é conseguido através dos seguintes passos:

1. destaque o intervalo D13: D16 na ficha de trabalho de detalhes do orçamento.Carregue no botão Copiar na página inicial da fita.
2. clique na célula E13.Carregue na seta para baixo por baixo do botão Colar na página inicial da fita.
3. carregue na opção fórmulas da lista de botões (ver Figura 2.26).

a Figura 2. 26 mostra a lista de botões que aparecem quando carrega na seta para baixo por baixo do botão Colar na página pessoal da fita. Uma coisa a notar sobre estas opções é que você pode visualizá-las antes de fazer uma seleção, arrastando o cursor do mouse sobre as opções. Como mostrado na figura, quando o cursor do rato é colocado sobre o botão fórmulas, você pode ver como as funções irão aparecer antes de fazer uma seleção. Observe que o contorno de linha dupla não muda quando esta opção é pré-visualizada. É por isso que esta seleção é feita em vez da opção Colar regular.