segunda-feira, 6 de agosto de 2012

EXCEL 2007 - GANTT TEMPLATE com Listview (with a Listview)


Este gantt template tem um formulário que aparece automaticamente quando abrimos o ficheiro e que permite ver, mediante a escolha na combo, as tarefas que estão concluídas, expiradas ou em curso. Penso que é útil para quem tem de construir um plano com muitas tarefas.
This gantt template has a form that automatically appears when you open the file and you can view, by selection in the combobox, the tasks that are completed, expired or current. I think it is useful for anyone who has to build a plan with many tasks.







Além desta característica, este modelo tem outras possibilidades:
In addition to this feature, this model has other possibilities:



Permite ver, em simultâneo, os cenários de programação e de execução;
A legenda altera-se automaticamente, em função da escolha no tipo de gráfico;
Permite escolher a escala da linha de tempo;
Assinala no gráfico a data actual, sempre que a mesma coincide com as datas da linha de tempo.
Permite escolher a representação gráfica da percentagem de execução;
A criação deste modelo foi inspirada nos exemplos encontrados aqui e aqui.
Compare programming and implementation;The subtitle changes automatically, depending on the choice of the type graph;Choose timeline scale;Highlight present date in timeline;Choose graphic representation of % completed.This template was inspired in these examples here and here.
O ficheiro é constituído por dois separadores: "Gantt" e "Folha1".
O separador "Gantt" é a folha de trabalho. A "Folha1" é preenchida automaticamente e é a origem da informação da listview.
The file has two sheets: "Gantt" and "Folha1"."Gantt" is where we set all data. The "Sheet1" is automatically populated from "Gantt" and is the source of information that is shown in listview.
Descarregar Modelo / Download Template
Informar se não conseguir baixar
Please report if download link not work


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.

quinta-feira, 16 de fevereiro de 2012

EXCEL 2007 - FILTRAR UMA BASE DE DADOS COM UMA FUNÇÃO UDF (How to filter a database with a UDF Function)

O utilizador comum poderá pensar que filtrar ou analisar informação de uma pequena base de dados ou de uma tabela é algo para pessoas mais experimentadas em Excel. Ora bem, nem sempre é tão complicado assim, já que o poderemos fazer com recurso a uma função UDF (User Defined Function). Há algum tempo atrás, usei o modelo em anexo para filtrar dados de pequenas tabelas. A função utilizada permite listar os itens por ordem alfabética e acrescentar automaticamente outros itens, caso a base de dados ou a tabela seja modificada.
Common users may think that filtering or analyzing information from a small database or a data table is for people more experienced in Excel. Well, it's not always so complicated, since we can make use of a UDF (User Defined Function). For quite some time ago, I have used the template attached to filter data from small tables. This UDF example allows to list the items alphabetically and automatically add other items if the
database or table is modified.


Folha1 - Tabela de dados Sheet1 - Database Table

Poder-se-á argumentar que a ferramenta Filtro Avançado executa o mesmo procedimento. De facto, é verdade, mas só se a tabela ou a base de dados de origem não for modificada, caso contrário teremos de executar essa ferramenta sempre que se proceder a uma adenda à tabela (por exemplo, acrescentar um novo item). Além disso, não ordena os itens alfabeticamente. Por outro lado, podíamos igualmente recorrer às tabelas dinâmicas, mas a ideia é apresentar uma alternativa que simplifique o processo para utilizadores menos experimentados.
It could be argued that the Advanced Filter tool performs the same procedure. In fact, it is true, but only if the original table / database is not changed, otherwise we will have to perform this tool whenever you make an addendum to the table (eg,  adding a new item). Beyond addition, it doesn't sort the items alphabetically. On the other hand, we could also use the dynamic tables, but the idea is to present an alternative that simplifies the process for users with lower experience.

Tomemos como exemplo uma oficina de reparação de automóveis em que estão listados, numa pequena base de dados, os automóveis reparados por cada mecânico, a data da reparação e o respectivo orçamento.
Take for example a garage car service where we list on a small base data every car repaired by each mechanic, the repair date and budget.

quarta-feira, 4 de janeiro de 2012

EXCEL 2007 - DUAS ALTERNATIVAS À FUNÇÃO SE (Two Alternatives to IF Function)

Aninhar uma função é usar a sua sintaxe como um dos argumentos de outra função.
Veja-se o exemplo:
= SE (A1 = "Maçãs"; 1; SE (A1 = "Laranjas"; 2; SE (A1 = "Pêras"; 3; SE (A1="Bananas"; 4))))
O Excel 2007 possibilita aninhar a função SE até 64 níveis. Uma enorme melhoria relativamente à versão anterior (Excel 2003) que só permitia 7 níveis.
No entanto, uma fórmula com 64 condições torna-se muito extensa morosa na sua construção e a possibilidade de erro na sua elaboração exige atenção e constante verificação.
Uma das alternativas para evitar esta situação é a utilização da função PROCV.
Consideremos a tabela abaixo:
To nest a IF function is to use its syntax as an argument of another function.
Consider the example:
= IF (A1 = "Apples", 1, IF (A1 = "Oranges", 2, IF (A1 = "Pears", 3, IF (A1 = "Bananas", 4))))
In Excel 2007 its possible to nest the IF function up to 64 levels. A huge improvement over the previous version (Excel 2003) that only allowed seven levels.
However, a formula with 64 conditions is very extensive in its construction and the possibility of error in their development requires constant attention and verification.
An alternative to avoid this is to use the VLOOKUP function.
Consider the table below:


A tabela (azul) contém informação sobre o local , ano de realização e número de equipas dos Campeonatos do Mundo de Futebol desde 1978.

O exemplo "A" contém uma fórmula aninhada nas colunas B e C:
=SE(A12=A2;B2;SE(A12=A3;B2;SE(A12=A4;B4;SE(A12=A5;B5;SE(A12=A6;B6;SE(A12=A7;B7;SE(A12=A8;B8;SE(A12=A9;B9;SE(A12=A10;A10))))))))) - Fórmula em B12

O exemplo "B" contém um fórmula com a função PROCV nas colunas B e C:
=PROCV(A14;A2:C10;2;FALSO) - Fórmula em B14
Os argumentos desta função são os seguintes: A14 é o valor a procurar na primeira coluna da tabela (EUA), A2:C10 é a tabela onde será feita a pesquisa, 2 é a coluna onde queremos encontrar o resultado pretendido (2002), FALSO retorna um valor exactamente igual ao da tabela (se substituirmos por VERDADEIRO, retorna um valor aproximado - mas não se aplica a este exemplo).
Como se pode comprovar, torna-se mais simples e rápido utilizar a função PROCV em vez da função SE aninhada.

O exemplo "C" foi acrescentado como curiosidade. Trata-se de uma simples multiplicação dos argumentos cujo resultado é igual a UM ou ZERO, consoante a correspondência é VERDADEIRA OU FALSA. Em termos de extensão não é muito diferente de uma fórmula aninhada mas torna-se mais simples de construir e o seu cálculo é mais rápido.
=(A16=A2)*B2+(A16=A3)*B3+(A16=A4)*B4+(A16=A5)*B5+(A16=A6)*B6+(A16=A7)*B7+(A16=A8)*B8+(A16=A9)*B9+(A16=A10)*B10 - Fórmula em B16

The table (blue) contains information on the location, year of the event and number of teams of FIFA Soccer World Championships since 1978.

Example "A" contains a nested formula in columns B and C:
=IF(A12=A2,B2,IF(A12=A3,B2,IF(A12=A4,B4,IF(A12=A5,B5,IF(A12=A6,B6,
IF(A12=A7,B7,IF(A12=A8,B8,IF(A12=A9,B9,IF(A12=A10,A10)))))))))
- Formula in B12

Example "B" contains a formula with the VLOOKUP function in columns B and C:
= VLOOKUP (A14, A2: C10, 2, FALSE) 
- Formula in B14

The arguments of this function are: A14 is the value to find in the first column of the table (EUA), A2: C10 is the table where the research will be done, 2 is the column where you want to find the desired result (2002), FALSE returns an exactly value equal to the table (TRUE returns an approximate value - but does not apply to this example).
As can be seen, it is simpler and faster to use the VLOOKUP instead of nested IF function
 
The example "C" was added as a curiosity. It is a simple multiplication of the arguments which result is equal to one or zero, depending on the match is TRUE OR FALSE. In terms of extension is not very different from a nested formula but it is simpler to build and its calculation is faster.
=(A16=A2)*B2+(A16=A3)*B3+(A16=A4)*B4+(A16=A5)*B5+(A16=A6)*B6+(A16=A7)*B7+(A16=A8)*B8+(A16=A9)*B9+(A16=A10)*B10 
- Formula in B16