Excel - ajuda

[m.elis]

Olá a todos,

Precisava que alguém me desse algumas informações para aprofundar os meus conhecimentos de Excel no que diz respeito às funções: Estatística e Financeiras.

Preciso de as usar para a resolução de um trabalho e nado a passo de tartaruga. Faço muitas consultas nas Ajudas que por lá aparecem, mas não é suficiente.

Alguém conhece algum livrinho, de fácil consulta, e para quem sabe pouco? Emprestaram-me uns manuais, mas a linguagem informática é "dura" para mim.

Aceito, de bom gosto, sugestões.

[JRibeiro]

Está aqui um tutorial que pode ser muito útil e dar acesso a outros: http://www.youtube.com/watch?v=iQ1XIj0ZQOM&feature=related

http://office.microsoft.com/pt-pt/excel/HA011465332070.aspx

http://office.microsoft.com/pt-pt/excel/HA100340222070.aspx

Se quiseres expor em concreto o que pretendes fazer, pode ser que possa, ou alguêm mais entendido, dar umas dicas!...

Bom trabalho! 

[m.elis]

Obrigada, Jribeiro. vou ver com atenção e assim que puder digo se foi suficiente.

[JRibeiro]

Aproveito este tópico para colocar a seguinte dúvida na construção de uma fórmula matemática no excel 2007:

Tabela com 3 colunas.

Coluna 1 - valores do menor, até ao maior sendo que entre estes há um intervalo de valores (ex. linha 1: até 5; Linha 2: 6 a 7; …; linha 5: acima de 40)

Coluna 2 - valores percentuais diferentes (ex. linha 1: 1,5%; linha 2: 2,5%;... linha 5: 6%)

Coluna 3 - valores distintos únicos (ex. linha 1: 10; linha 2: 20; ... linha 5: 50)

A fórmula é muito simples. Para o valor da coluna 1, basta multiplicar o valor da coluna 2 pelo valor da coluna 3 (ex. linha 1: escolho 4, então a conta a fazer é 1,5%*10)

O que pretendo, é algo do tipo "IF, THEN, ELSE", ou seja relacionar os valores todos da coluna 1, numa fórmula única, mediante o valor a atribuir numa outra célula (se por exemplo, escolher 35, gostaria de obter o valor correcto, que é só multiplicar o valor da linha 4 dado pelo produto da coluna 2 e da coluna 3).


Já soube fazer isto, mas estou um pouco "enferrujado"!... 

[pauloaguia]

O que pretendo, é algo do tipo "IF, THEN, ELSE", ou seja relacionar os valores todos da coluna 1, numa fórmula única, mediante o valor a atribuir numa outra célula (se por exemplo, escolher 35, gostaria de obter o valor correcto, que é só multiplicar o valor da linha 4 dado pelo produto da coluna 2 e da coluna 3).

Acho que não percebi bem o teu problema por isso vou fazer um enunciado parecido e dar a fórmula, pode ser que te ajude:
* Na coluna A tenho os limites inferiores de cada intervalo (no teu caso seria talvez 0, 6, ..., 40)
* Na coluna B as percentagens
* Na coluna C um valor distinto para cada linha
(Na linha 1 tenho cabeçalhos, por isso a range com valores é A2:C6)

Supondo que quero calcular o produto da percentagem pelo tal valor distinto correspondentes a um dado número (que se encontre, por exemplo, em F10) consoante o intervalo em que esse número se encontra na coluna A, a fórmula para esse resultado é
=VLOOKUP(F10;$A$2:$C$6;2)*VLOOKUP(F10;$A$2:$C$6;3)
(com a devida substituição do ; por , de acordo com os regional settings de cada um)

Atenção que para que isto resulte, a coluna A tem efectivamente que estar ordenada por ordem crescente.

Pensando bem não sei se simplifiquei muito mais o enunciado, mas espero que te ajude  

[JRibeiro]

O que pretendo, é algo do tipo "IF, THEN, ELSE", ou seja relacionar os valores todos da coluna 1, numa fórmula única, mediante o valor a atribuir numa outra célula (se por exemplo, escolher 35, gostaria de obter o valor correcto, que é só multiplicar o valor da linha 4 dado pelo produto da coluna 2 e da coluna 3).

Acho que não percebi bem o teu problema por isso vou fazer um enunciado parecido e dar a fórmula, pode ser que te ajude:
* Na coluna A tenho os limites inferiores de cada intervalo (no teu caso seria talvez 0, 6, ..., 40)
* Na coluna B as percentagens
* Na coluna C um valor distinto para cada linha
(Na linha 1 tenho cabeçalhos, por isso a range com valores é A2:C6)

Supondo que quero calcular o produto da percentagem pelo tal valor distinto correspondentes a um dado número (que se encontre, por exemplo, em F10) consoante o intervalo em que esse número se encontra na coluna A, a fórmula para esse resultado é
=VLOOKUP(F10;$A$2:$C$6;2)*VLOOKUP(F10;$A$2:$C$6;3)
(com a devida substituição do ; por , de acordo com os regional settings de cada um)

Atenção que para que isto resulte, a coluna A tem efectivamente que estar ordenada por ordem crescente.

Pensando bem não sei se simplifiquei muito mais o enunciado, mas espero que te ajude  

Deve ser isso, mas não conheço a função VLOOKUP e não percebo porque colocas ...;2 e ...;3.

Experimentei e não resultou. Se calhar não expliquei bem o que pretendo!...

Estava mais virado para a função SE, visto que no excel não há a instrução, IF... THEN... ELSE que é aquilo que pretendo. Se o valor inserido na célula X é, por exemplo, 4, então o resultado é (1,5%*10=)W; se o valor inserido na célula X é, 15, então...

Experimentei com a função SE para as duas primeiras linhas e funcionou. O problema agora é introduzir as restantes 3 linhas que faltam numa só instrução!... 

Bem, se não houver mais sugestões, vou tentar com aquilo que tenho e depois logo se vê se acerto com as ; " , etc!... 

[pauloaguia]

Deve ser isso, mas não conheço a função VLOOKUP e não percebo porque colocas ...;2 e ...;3.

Como com qualquer outra função no Excel, não há como carregar em F1 e perguntar
Pelo que percebi mais abaixo usas as funções em português, essas já não as sei, por isso não te sei dizer que nome deves usar para VLOOKUP. Mas se pesquisares na lista de funções do tipo referência, és capaz de chegar lá

A função VLOOKUP procura o primeiro parâmetro (o valor de F10) na primeira coluna da tabela dada no segundo parâmetro ($A$2:$C$6) e devolve o valor da mesma linha na coluna dada no terceiro parâmetro (ou seja, o primeiro VLOOKUP devolve o valor da 2ª coluna e o outro o valor da 3ª coluna).
A função aceita ainda um 4ª parâmetro, opcional. Quando ausente, a pesquisa na primeira coluna é feita por intervalo em vez de ser uma pesquisa por valor exacto - se procurares por 4 dá-te a primeira linha, se procurares por 100 dá-te a 5ª.

Estava mais virado para a função SE, visto que no excel não há a instrução, IF... THEN... ELSE que é aquilo que pretendo. Se o valor inserido na célula X é, por exemplo, 4, então o resultado é (1,5%*10=)W; se o valor inserido na célula X é, 15, então...

Algo deste género?
SE(X<=5;$B$2*$C$2;SE(X<=7;$B$3*$C$3;SE(X<=10;$B$4*$C$4;SE(X<=25;$B$5*$C$5;$B$6*$C$6))))

O único problema aqui é que só podes usar até 6 níveis de funções umas dentro das outras. Se quiseres ter mais do que 6 ou 7 linhas, deixa de funcionar e tens mesmo que recorrer a uma solução do primeiro tipo.

[JRibeiro]

Deve ser isso, mas não conheço a função VLOOKUP e não percebo porque colocas ...;2 e ...;3.

Como com qualquer outra função no Excel, não há como carregar em F1 e perguntar
Pelo que percebi mais abaixo usas as funções em português, essas já não as sei, por isso não te sei dizer que nome deves usar para VLOOKUP. Mas se pesquisares na lista de funções do tipo referência, és capaz de chegar lá

A função VLOOKUP procura o primeiro parâmetro (o valor de F10) na primeira coluna da tabela dada no segundo parâmetro ($A$2:$C$6) e devolve o valor da mesma linha na coluna dada no terceiro parâmetro (ou seja, o primeiro VLOOKUP devolve o valor da 2ª coluna e o outro o valor da 3ª coluna).
A função aceita ainda um 4ª parâmetro, opcional. Quando ausente, a pesquisa na primeira coluna é feita por intervalo em vez de ser uma pesquisa por valor exacto - se procurares por 4 dá-te a primeira linha, se procurares por 100 dá-te a 5ª.

Ainda hei-de voltar a esta função!...

O problema está em que na coluna 1 (que defines como sendo coluna A) tenho texto misturado, dai talvez ser essa a razão para a função VLOOKUP não funcionar!...
O texto também não é nada por ai alêm. Trata-se de definir intervalos de valores. Por exemplo, 6 a 7!...
Vou experimentar [6,7[, mas parece-me que o problema se vai manter!...

Estava mais virado para a função SE, visto que no excel não há a instrução, IF... THEN... ELSE que é aquilo que pretendo. Se o valor inserido na célula X é, por exemplo, 4, então o resultado é (1,5%*10=)W; se o valor inserido na célula X é, 15, então...

Algo deste género?
SE(X<=5;$B$2*$C$2;SE(X<=7;$B$3*$C$3;SE(X<=10;$B$4*$C$4;SE(X<=25;$B$5*$C$5;$B$6*$C$6))))

O único problema aqui é que só podes usar até 6 níveis de funções umas dentro das outras. Se quiseres ter mais do que 6 ou 7 linhas, deixa de funcionar e tens mesmo que recorrer a uma solução do primeiro tipo.

Bravo! Consegui adaptar as instruções que sugeriste ao meu problema e já percebi porque motivo não estava a resultar: ) (parenteses) a mais, quando deveria ter apenas colocado SE seguido do argumento!...  

Obrigado!  

Outra dúvida, mas não sei se a consigo explicar correctamente!...

Num determinado mês, não sei quantas vezes vou "consumir" determinado produto. Há alguma instrução que me permita adicionar automaticamente uma linha (e demais colunas referentes a esse produto) para esse mês? Caso não necessite de acrescentar mais linhas, a instrução deveria permitir avançar para o mês seguinte!...  
A instrução deve identificar o mês onde o "produto" foi consumido.

Bem sei que posso manualmente efectuar a operação, mas se a puder fazer com um simples click!...  

[pauloaguia]

O problema está em que na coluna 1 (que defines como sendo coluna A) tenho texto misturado, dai talvez ser essa a razão para a função VLOOKUP não funcionar!...
O texto também não é nada por ai alêm. Trata-se de definir intervalos de valores. Por exemplo, 6 a 7!...
Vou experimentar [6,7[, mas parece-me que o problema se vai manter!...

Para funcionar, tem que ser apenas um valor numérico. Daí eu ter dito que na primeira coluna tinha o limite inferior do intervalo. O Excel pode ser muito poderoso mas ainda não consegue interpretar o significado que se pretende dar a um bocado de texto

Num determinado mês, não sei quantas vezes vou "consumir" determinado produto. Há alguma instrução que me permita adicionar automaticamente uma linha (e demais colunas referentes a esse produto) para esse mês? Caso não necessite de acrescentar mais linhas, a instrução deveria permitir avançar para o mês seguinte!...  
A instrução deve identificar o mês onde o "produto" foi consumido.

Não estou por dentro do contexto, por isso não percebi nada O que é o mês seguinte?
Posso ver uma cópia da folha de Excel? Era capaz de ajudar...

Mas assim de repente, parece-me que precisas de macros para fazer isso, já não vai lá só com fórmulas.

[JRibeiro]

Num determinado mês, não sei quantas vezes vou "consumir" determinado produto. Há alguma instrução que me permita adicionar automaticamente uma linha (e demais colunas referentes a esse produto) para esse mês? Caso não necessite de acrescentar mais linhas, a instrução deveria permitir avançar para o mês seguinte!...  
A instrução deve identificar o mês onde o "produto" foi consumido.

Não estou por dentro do contexto, por isso não percebi nada O que é o mês seguinte?
Posso ver uma cópia da folha de Excel? Era capaz de ajudar...

Mas assim de repente, parece-me que precisas de macros para fazer isso, já não vai lá só com fórmulas.

Então irá manualmente e já vai muito bem. Só de ver a palavra "macros"!... 

Exemplo: Estamos em Maio. Fui n vezes ao hipermercado "Tal" comprar o produto X que me custou Y e que me fez poupar W em relação a XPTO. Em vez de estar a a inserir "manualmente" n linhas para o mês de Maio, preferia que houvesse uma instrução que a fizesse. Mas sendo macros, é preferivel deixar estar como está!... 

[JRibeiro]

Tenho uma fórmula do qual sei o início mas gostaria de a "estender" para n casos que não sei à partida quantos são.

A fórmula é do tipo:
b7+g7
b7+(g7+g8)
b7+(g7+g8+g9)
e por ai fora.

Como posso, no excel, estender a fórmula à casa n, que não sei qual é?

Suponho que devo ter duas colunas para o efeito!...

[pauloaguia]

Tenho uma fórmula do qual sei o início mas gostaria de a "estender" para n casos que não sei à partida quantos são.

A fórmula é do tipo:
b7+g7
b7+(g7+g8)
b7+(g7+g8+g9)
e por ai fora.

Suponho que o resultado das várias iterações seja colocado em células consecutivas...
Sendo o caso, na primeira podes colocar
        =$B$7+SUM($G$7:G7)
Ao copiar esta fórmula para as células seguintes, a parte absoluta (as referências que têm o símbolo $) mantêm-se, mas a parte relativa vai sendo actualizada - na célula 3 casas à direita da fórmula original, por exemplo, a fórmula será $B$7+SUM($G$7:J7)
Para copiar usa-se o tradicional Ctrl+C, Ctrl+V ou então seleccionando a célula e arrastando para a direita o pequeno quadrado do canto inferior direito da mesma.

[JRibeiro]

Creio que me expliquei mal e apresentei a fórmula incorrecta!... 

Na realidade a fórmula é para aplicar ao longo de uma coluna e é do tipo:
b7+g7
b8+(g7+g8)
b9+(g7+g8+g9)
e por ai fora.

Penso que na sugestão que deste, bastaria retirar os cifrões do B7 para ela começar a funcionar mas apareceu-me um erro de "#nome?" que não estou a descortinar solução!...

No entanto, a dúvida estende-se à existência de uma célula que me vai dizer quantas linhas devo usar para efectuar o cálculo que pretendo de acordo com a fórmula que apresentei.

Exemplo. Na célula, digo efectua o cálculo 12 vezes.
Manualmente, sei que na coluna X tenho o cálculo b7+g7, que devo somar ao cálculo b8+(g7+g8) e por ai fora!...
Ora deve haver uma forma mais expedita de fazer esta operação!...

[pauloaguia]

Creio que me expliquei mal e apresentei a fórmula incorrecta!... 

Na realidade a fórmula é para aplicar ao longo de uma coluna e é do tipo:
b7+g7
b8+(g7+g8)
b9+(g7+g8+g9)
e por ai fora.

Deixa lá, eu também me distraí e devia ter dito que era para arrastar para baixo e não para a direita

Penso que na sugestão que deste, bastaria retirar os cifrões do B7 para ela começar a funcionar mas apareceu-me um erro de "#nome?" que não estou a descortinar solução!...

Sim, basta retirar os $ do B7. Quanto ao #nome? basta substituir SUM por SOMA (eu trabalho com o Excel em inglês e esquece-mo que as fórmulas têm nomes diferentes)

No entanto, a dúvida estende-se à existência de uma célula que me vai dizer quantas linhas devo usar para efectuar o cálculo que pretendo de acordo com a fórmula que apresentei.

Exemplo. Na célula, digo efectua o cálculo 12 vezes.
Manualmente, sei que na coluna X tenho o cálculo b7+g7, que devo somar ao cálculo b8+(g7+g8) e por ai fora!...
Ora deve haver uma forma mais expedita de fazer esta operação!...

Esta parte acho que não percebi...
Por exemplo, SUM($G$7:G19) já vai somar as 13 parcelas - tudo o que está entre G7 e G19 (G7 + G8 + G9 + ... + G18 + G19). É a isto que te referes?

[JRibeiro]

Creio que me expliquei mal e apresentei a fórmula incorrecta!... 

Na realidade a fórmula é para aplicar ao longo de uma coluna e é do tipo:
b7+g7
b8+(g7+g8)
b9+(g7+g8+g9)
e por ai fora.

Deixa lá, eu também me distraí e devia ter dito que era para arrastar para baixo e não para a direita

Penso que na sugestão que deste, bastaria retirar os cifrões do B7 para ela começar a funcionar mas apareceu-me um erro de "#nome?" que não estou a descortinar solução!...

Sim, basta retirar os $ do B7. Quanto ao #nome? basta substituir SUM por SOMA (eu trabalho com o Excel em inglês e esquece-mo que as fórmulas têm nomes diferentes)

Esta parte já está resolvida!... 

No entanto, a dúvida estende-se à existência de uma célula que me vai dizer quantas linhas devo usar para efectuar o cálculo que pretendo de acordo com a fórmula que apresentei.

Exemplo. Na célula, digo efectua o cálculo 12 vezes.
Manualmente, sei que na coluna X tenho o cálculo b7+g7, que devo somar ao cálculo b8+(g7+g8) e por ai fora!...
Ora deve haver uma forma mais expedita de fazer esta operação!...

Esta parte acho que não percebi...
Por exemplo, SUM($G$7:G19) já vai somar as 13 parcelas - tudo o que está entre G7 e G19 (G7 + G8 + G9 + ... + G18 + G19). É a isto que te referes?

Não é bem isso, embora seja verdade o que dizes.

Aquilo que pretendo está explicado umas mensagens atrás (Maio 28, 2010, 18:01:42) e creio que a solução é a macros. O problema é que não faço ideia de como "instruir" a macros no excel!...

Dito de outra forma, este mês fui comprar 5 vezes o produto X. Mas poderia ter ido comprar só duas vezes.

Como posso instruir o excel, para construir as X linhas, que são variáveis de mês para mês?

[pauloaguia]

Aquilo que pretendo está explicado umas mensagens atrás (Maio 28, 2010, 18:01:42) e creio que a solução é a macros. O problema é que não faço ideia de como "instruir" a macros no excel!...

Dito de outra forma, este mês fui comprar 5 vezes o produto X. Mas poderia ter ido comprar só duas vezes.

Como posso instruir o excel, para construir as X linhas, que são variáveis de mês para mês?

Pois, e essa parte, já da outra vez eu não tinha percebido

O que queres dizer com "construir linhas"? Dá para ver um excerto do ficheiro? Dava para ficar com uma ideia melhor de como ajudar...

[JRibeiro]

Pretendo calcular a média de X células de uma coluna. A função a utilizar é do tipo: =MÉDIA(A5:A15).

Como é que posso calcular a média apenas das células preenchidas?

Exemplo: se das 15 células da coluna apenas usar 5, de acordo com a fórmula de cima, obtenho uma média que não corresponde à média pretendida porque estou a usar células vazias!...

Eu disse 5 mas podiam ser 2 ou 7 ou 14!...

[pauloaguia]

Pretendo calcular a média de X células de uma coluna. A função a utilizar é do tipo: =MÉDIA(A5:A15).

Como é que posso calcular a média apenas das células preenchidas?

A função MÉDIA (suponho que seja a AVERAGE em inglês) já ignora as células em branco... Talvez no teu caso elas não estejam mesmo em branco?
Há também uma outra função AVERAGEA (não tenho a certeza da correspondência em português... talvez MÉDIA.VAL, a julgar pelos nomes de outras funções do mesmo género) que entra em linha de conta com strings e booleanos... mas mesmo esta função ignora as células em branco.

Experimentei numa coluna colocar a seguinte sequência de valores {1, 2, 1, 2, , , , 1, 2, 1, 2, , , , 1, 2} (cuja média, ignorando as células em branco é claramente 1.5) e quer usando AVERAGE() quer AVERAGEA() obtive o resultado de 1.5. Mudei algumas das células para terem um espaço e AVERAGE continua a dar 1.5 pois só considera valores numéricos mas AVERAGEA já começa a baixar (pois considera o espaço como se fosse 0).