terça-feira, 13 de março de 2012

EXCEL 2007 - Como utilizar a função FREQUÊNCIA (How to use FREQUENCY Function)

Como utilizar a função FREQUÊNCIA
Para se calcular o número de ocorrências de valores num conjunto de intervalos, usamos a função FREQUÊNCIA, que é uma função matriz. Como todas as funções deste tipo, A FREQUÊNCIA opera a partir de uma matriz de dados. Esta função, porém, precisa de outra variável: uma matriz de bins.
A matriz de dados é uma tabela de dados onde listamos os resultados pretendidos (ver Fig. A - DATABASE). A matriz de bins é a tabela de categorias ou de intervalos aos quais os resultados obtidos são associados (ver Fig. A - tabelas PESO e ALTURA).


Fig A

A sua aplicação é feita do seguinte modo:
  1. Construimos a tabela de dados (DATABASE) - matriz de dados.
  2. Criamos depois a matriz de bins (ver tabelas PESO e ALTURA). Para a tabela PESO criamos a coluna kg onde representamos os intervalos desejados. Do mesmo modo, criamos para a tabela ALTURA a coluna cm.
  3. A seguir seleccionamos o intervalo de células como mostra a Fig. B e escrevemos na barra de fórmulas "=FREQUÊNCIA($C$4:$C$28;F4:F9)". Premimos CTRL+SHIFT+ENTER para que a fórmula seja de matriz (a fórmula terá o seguinte aspecto "{=FREQUÊNCIA($C$4:$C$28;F4:F9)}". Repetimos o procedimento na tabela ALTURA, seleccionando as células à direita da coluna cm e introduzindo a fórmula "=FREQUÊNCIA($D$4:$D$28;H4:H9)" que, após premirmos CTRL+SHIFT+ENTER, será "{=FREQUÊNCIA($D$4:$D$28;H4:H9)}".
 
    Fig B

A fórmula calcula automaticamente as ocorrências em cada intervalo e que serão distribuídas conforme mostrado na Fig. C.


Fig C

Using the FREQUENCY function
To calculate the number of values occurrences in a set of ranges, we use FREQUENCY, which is an array function. This function, however, needs another variable: a bins array.
The array data is a table of data where we list the desired results (see Fig A - DATABASE). The bins array is the table of the categories or ranges which are associated with the results obtained (Fig. A - PESO and ALTURA tables).
We may do as follows:
  1. Build the data table (DATABASE) - data array.
  2. Then, build the bins array (see PESO and ALTURA tables). In PESO table column, we create kg column which represent the desired intervals. Similarly, we create  cm column in ALTURA table.
  3. Then we select the cell range as shown in Fig B and write in formula bar "= FREQUENCY ($C$4:$C$28,F4:F9)". Press Ctrl + SHIFT + ENTER to which the formula is array (the formula will look like "{= FREQUENCY ($C$4:$C$28,F4:F9)}". We repeat the procedure in ALTURA table by selecting the range of cells in the right column next to cm column and entering the formula "= FREQUENCY ($D$4:$D$28;H4:H9)" that after pressing CTRL + SHIFT + ENTER, will be "{=FREQUENCY($D$4:$D$28;H4:H9)}".
Formula automatically calculates for each range, and events which are distributed as shown in Fig C.

segunda-feira, 5 de março de 2012

EXCEL 2007 - SOMAR VALORES COM CRITÉRIOS MÚLTIPLOS (SUMIF WITH MULTIPLE CRITERIA)


Na mensagem anterior apresentei um modelo de filtragem de dados com recurso a uma função UDF. Nesse mesmo modelo utilizei uma função para somar os valores constantes da tabela de acordo com vários critérios. Para executar este procedimento, existem várias alternativas utilizando as funções SOMARPRODUTO ou SOMA.SE.S:
  1. A célula com fundo rosa tem a seguinte fórmula: "=SOMARPRODUTO((BaseDados!$A$2:$A$5000=C$1)*(BaseDados!$C$2:$C$5000=$A4)*(BaseDados!$E$2:$E$5000))"
  2. A célula com fundo verde tem a seguinte fórmula: "=SOMARPRODUTO(--(BaseDados!$A$2:$A$5000=D$1);--(BaseDados!$C$2:$C$5000=$A5);--(BaseDados!$E$2:$E$5000))"
  3. Por fim, a célula com fundo laranja tem a fórmula: "=SOMA.SE.S(BaseDados!$E$2:$E$5000;BaseDados!$A$2:$A$5000;E$1;BaseDados!$C$2:$C$5000;$A6)".
Todas estas possibilidades permitem chegar ao mesmo resultado. A diferença está apenas na rapidez de cálculo que o Excel executa. Ora, se em pequenas tabelas essa diferença é praticamente inexistente, já quando se trata de um grande número de dados (por exemplo, uma tabela com milhares de linhas), a diferença de rapidez é consideravelmente notória.

Até à versão Excel 2003, só era possível executar somas baseadas em dois ou mais critérios recorrendo à função SOMARPRODUTO (ver as opções 1 e 2). O Excel 2007 introduziu a função SOMA.SE.S (que é um desenvolvimento da anterior SOMA.SE, permitindo mais do que um critério). Com esta possibilidade, há um ganho considerável na rapidez de cálculo.


In previous post I 've shown a template of data filtering using a UDF function. In the same template I used a function to sum ​​the values ​​listed in the table according to multiple criteria. To perform this procedure, there are several alternatives using the SUMPRODUCT function or SUMIFS:
  1.  The cell with pink background has the following formula: "=SUMPRODUCT ((BaseDados!$A$2:$A$5000=C$1)*(BaseDados!$C$2:$C$5000=$A4)*(BaseDados!$E$2:$E$5000))"
  2.  The cell with a green background has the following formula: "=SUMPRODUCT(--(BaseDados!$A$2:$A$5000=D$1),--(BaseDados!$C$2:$C$5000=$A5), --(BaseDados!$E$2:$E$5000))"
  3.  Finally, the cell with orange background has the formula: "=SUMIFS(BaseDados!$E$2:$E$5000,BaseDados!$A$2:$A$5000,E$1,BaseDados!$C$2:$C$ 5000,$A6)"
All these possibilities allow to reach the same result. The only difference is the speed of calculation that Excel runs. However, in small tables like the example we can't see no difference is the speed calculation. But in the case of a large number of data (eg, a table with thousands of rows), a faster proccedure is very important.

In Excel 2003 version, we could only run sums based on two or more criteria using the SUMPRODUCT function (see options 1 and 2). Excel 2007 introduced SUMIFS function (which is a development of the previous SUMIF, allowing more than one criteria). With this possibility, there is a considerable gain in speed of calculation.