Excel e Outras Coisas (Excel Things)

Problema Resolvido - DETERMINAR A DURAÇÃO DE UMA TAREFA (Solved Problem - How to determine an activity duration)

2016-08-12T12:22:00.001+01:00

Um leitor (Obrigado Denis) precisou de ajuda para determinar a data de conclusão da montagem de uma máquina, sabendo que:

A duração da montagem é de 30 horas de trabalho.
O turno regular é de 8:48 horas, das 8:00 às 17:48, incluindo o intervalo de 1:00 para almoço.
A data de início da montagem é 21/09/2014 10:00.

A distribuição das 30 horas seria efectuada da seguinte forma:

A blog reader (Thanks Denis) asked me for help in determining the date of completion of the assembly of a machine, knowing that:
The duration of the assembling is 30 working hours.

Regular shift is 8:48 hours, from 8:00 to 17:48, including the 1:00 for lunch.

The start date of the assembly is 21/09/2014 10:00.

The 30 hours distribution would be performed as follows:


Início Turno	30:00	Almoço	Fim Turno
21-09-2014 10:00	06:48	01:00	21-09-2014 17:48
22-09-2014 08:00	08:48	01:00	22-09-2014 17:48
23-09-2014 08:00	08:48	01:00	23-09-2014 17:48
24-09-2014 08:00	05:36		24-09-2014 13:36

A montagem da máquina terminaria no dia 24 de Setembro de 2014, às 13:36.
Método de cálculo:
Em primeiro lugar, vamos converter “8:48” (duração do turno) em valor decimal, aplicando uma rega de três – Se “8:00” é igual a 8, “8:48” será igual a “x”
Fórmula - =”8:48” / 8 x “8:00” – a que corresponde o resultado de 8,8
Construindo a fórmula:

Fórmula em C1 - =B1+(INT(A1*24/8,8)+RESTO(A1*24;8,8)/24)

The machine assembly end on September 24, 2014, at 13:36.

Method of calculation:

First, we convert "8:48" (shift duration) in decimal value by applying a simple rule of three - If "8:00" is equal to 8 then "8:48" is equal to "x"

Formula - = "8:48" / 8 x "8:00" - which corresponds to the result of 8.8

Building formula:

Formula em C1 - =B1+(INT(A1*24/8.8)+MOD(A1*24,8.8)/24)

	A	B	C
1	30:00	21 set 2014 10:00	24 set 2014 13:36
2
3

CALCULAR TEMPO ENTRE LOTES DE PRODUÇÃO (Calculating Time Between Production Batches)

2016-08-01T16:24:00.000+01:00

Este problema foi apresentado por um leitor e seguidor (Obrigado Maxwell).
Trata-se de calcular o atraso entre lotes de produção, tendo em consideração um valor tempo definido entre lotes, e realçando os valores tempo entre lotes superiores ao valor pré-definido (neste exemplo - 02:35)

A fórmula em C6: =B6+$E$3
A fórmula em E6: =SE(D6>C6;RESTO(D6-C6;1);RESTO(C6-D6;1))

This problem was presented by a reader and follower (Thanks Maxwell)

This is to calculate the delay between production batches , considering a set time value between lots, and highlighting the time values between batches greater than the default value (in this example - 02:35)

Formula in C6: =B6+$E$3
Formula in E6: =IF(D6>C6,MOD(D6-C6,1),MOD(C6-D6,1))

Como sempre, estou disponível para sugestões de melhoria e para ajudar nas questões que eventualmente estejam omissas, através dos comentários no blog ou por mail blog.excelcoisas@gmail.com

Espero que seja útil.

As always, I am available to suggestions for improvement and to help answer questions that are possibly missing through comments on the blog or via email blog.excelcoisas@gmail.com.
Hope that is useful.

CONVERTER HORAS EM DIAS DE TRABALHO (Convert Hours to Workdays)

2014-07-20T19:58:00.001+01:00

Converter horas em dias, segundos em minutos, dias em meses ou anos, etc. é muito simples.
Basta mudarmos a formatação do valor que pretendemos.
Por exemplo, se queremos converter 33:00 Horas em dias, só temos que alterar a formatação para dd hh mm.

Convert hours in days, seconds in minutes, days in months or years, etc.. is very simple.
You just have to format the cells as wish.
For example, if you want to convert 33:00 hours on days you only have to change the format to dd hh mm.

Converter horas em dias de trabalho já é um bocadinho mais complexo.
Tendo como exemplo o mesmo valor acima, e tendo por base um dia de trabalho de 8 horas, a conversão teria de resultar em 2 dias e 7 horas.
Aqui se demonstra várias formas de chegar ao mesmo resultado.

Convert hours in working days is a bit more complicated.
Taking the above example, and based on a 8 hrs working day, the conversion would result in 2 days and 7 hours.
Here is some ways to get same result.

Há, porém, um pequeno problema quando se trata de calcular tempo no EXCEL:
O EXCEL utiliza dois sistemas de datas - 1900 e 1904.
O primeiro é o mais utilizado e é também o pré-definido na maior parte dos sistemas operativos. O sistema de 1904 está normalmente associado às versões de EXCEL para APPLE.
Acontece que, quando trabalhamos num ficheiro que nos foi enviado por outrém, podemos ter a surpresa de o cálculo das fórmulas de tempo desse ficheiro não corresponderem aos resultados esperados.
Devemos por isso verificar sempre qual o sistema de datas ativo no sistema de datas que nos é enviado. Se o sistema é diferente do nosso, temos de proceder à correspondente alteração nas opções do EXCEL ou adaptar as fórmulas de tempo (data e hora) incluídas nesse ficheiro.

There is however a small problem when it comes to calculating time in EXCEL:
EXCEL uses two systems dates - 1900 and 1904.
The first is the most widely used and is also the default one in most operating systems. The 1904 system is usually associated with versions of EXCEL for APPLE.
Happens that when you work on a workbook that was sent to you by someone else, you may have the surprise of the calculation formulas of time that file do not match the expected results.
you should therefore always verify which date system is of activatedin the received workbook. If the system is different from yours, you have to make the corresponding change in EXCEL options or adapt date and time formulas included in that workbook.

Para saber qual o sistema ativo no seu computador, abra um documento NOVO no Excel, vá depois a opções do Excel > Avançadas > Ao calcular este livro > Verifique se a opção "Utilizar sistema de datas de 1904". Se estiver marcada, então está a usar o sistema de 1904. Se não estiver marcada, então está a utilizar o sistema de 1900 que é o mais utilizado.

To find the active system on your computer, open a NEW workbook in Excel, then go to Excel Options> Advanced> When calculating this workbook> Make sure the "Use 1904 date system". If checked, then you are using the 1904 Date System. If not checked, then the system in using is 1900 Date System, which is the most used.

Compare aqui as diferenças nas fórmulas usadas para cada um dos sitemas de datas, para convertermos horas em dias de trabalho.

Here is a comparision of the differences in the formulas used for each date system to convert hours in working days.

Como sempre, estou disponível para sugestões de melhoria e para ajudar nas questões que eventualmente estejam omissas, através dos comentários no blog ou por mail blog.excelcoisas@gmail.com
Espero que seja útil.

As always, I am available to suggestions for improvement and to help answer questions that are possibly missing through comments on the blog or via email blog.excelcoisas@gmail.com.
Hope that is useful.

CALCULAR HORAS EXTRA, TURNO NOCTURNO, HORAS NEGATIVAS, ETC - Versão 2 (Overtime Calculation, Night Shift, Negative Hours, Etc. - Version 2)

2014-05-22T12:40:00.000+01:00

Em resposta a algumas sugestões, apresento um novo modelo com alterações relativas ao cálculo de horas extra, tendo particularmente em consideração o cálculo utilizado em Portugal:
A 1ª hora extra calculada a 125%
As horas extra seguintes a 137.5%

Mais uma vez, o modelo é completamente editável e facilmente adaptável à maioria das situações relacionadas profissionais.
Como sempre, estou disponível para sugestões de melhoria e para ajudar nas questões que eventualmente estejam omissas, através dos comentários no blog ou por mail blog.excelcoisas@gmail.com
Espero que seja útil.

In response to some suggestions, I present a new model with changes regarding the calculation of overtime, taking into particular account the calculation used in Portugal: The 1st overtime calculated at 125% The following extra hours at 137.5%
Again, the template is fully editable and readily adaptable to most business situations. As always, I am available to suggestions for improvement and to help answer questions that are possibly missing through comments on the blog or via email blog.excelcoisas@gmail.com.

Hope that is useful.

Descarregar Modelo Download Template

INSERIR CARIMBO DE TEMPO (Insert a Timestamp)

2013-11-24T22:28:00.001+00:00

Em virtude de ter recebido várias solicitações relacionadas com carimbos temporais, achei pertinente a publicação de um artigo sobre o assunto.

Because having received several requests related to timestamps, I found relevant to submit a post with this the subject.

Todos os dias temos necessidade de registar a data e/ou a hora actuais de um evento (agendamentos, registo de despesas, etc.). A estes registos chamam-se carimbos de tempo.

Every day we need to record the date and/or time of a current event (scheduling, expense registration, etc.). To these records we call timestamps.

Todavia, se tivermos de repetir o processo múltiplas vezes, torna-se aborrecido ter de reescrever constantemente a data e a hora.

However, if we repeat the process multiple times, it becomes boring and time consuming having to constantly rewrite the date and time.

Um modo de substituir a introdução manual data e/ou a hora actuais é utilizar as fórmulas HOJE() e AGORA().

A method of replacing the manual input of the current date and/or time is to use existing formulas TODAY() and NOW().

A primeira insere automaticamente a data actual. A segunda, além da data, insere também a hora.

The first automatically inserts the current date. The second enters the current date and time.

Exemplo:

Em A1, inserimos =HOJE(), obteremos a data actual (Ex. 24/11/2013 - podemos formatar a data como quisermos).

Em A2, inserimos =AGORA(), obteremos a data e hora actual (Ex. 24/11/2103 1:55 PM)

Example:

In A1, insert =TODAY(), we get the current date (eg. 11/24/2013 - format date as we wish).

In A2, insert =NOW(), we get the current date and time (eg. 11/24/2103 1:55 PM)

O problema da utilização destas fórmulas é que elas estão constantemente a actualizar o resultado. Ou seja, se no dia seguinte, às 10:00, abrirmos o ficheiro, A1 apresentará a data de 25/11/2013 e A2 terá o valor 25/11/2013 10:00 AM. Não servem por isso para criar registos de tempo que permitam uma análise posterior dos dados.

The problem of the use of these formulas is that they are constantly updating the result. That is, if the next day, at 10:00, we open the file, A1 will display the date of 11/25/2013 and A2 will have the value 11/25/2013 10:00 AM. Therefore they do not serve to create timestamps to allow further analysis of the data.

Para obtermos realmente um carimbo de tempo, temos de utilizar os seguintes atalhos de teclado:

1. CTRL+SHIFT+; - Insere automaticamente a data actual

2. CTRL+SHIFT+: - Insere automaticamente a hora actual

3. CTRL+SHIFT+; ESPAÇO CTRL+SHIFT+: - Insere automaticamente a data e a hora actuais

To obtain a timestamp, we use the following keyboard shortcuts:

1. CTRL+SHIFT+; - Automatically inserts the current date

2. CTRL+SHIFT+: - Automatically inserts the current time

3. CTRL+SHIFT+; SPACE CTRL+SHIFT+: - Automatically inserts the current date and time

Uma forma ainda mais rápida de escrever a data e a hora é substituir o atalho 3 por um atalho de teclado personalizado (ex: CTRL+t) associando-lhe a seguinte macro:

An even faster way to write the date and time is to replace the shortcut 3 for a custom keyboard shortcut (eg CTRL+t) and link the following macro:

Sub CarimboTempo()

Dim CT

'Atalho de Teclado (Keyboard Shortcut): Ctrl+t

CT = Format(Now, "dd/mm/yyyy hh:mm:ss AM/PM") ‘formatar como desejar (format as you wish)

ActiveCell.Select

Selection.NumberFormat = "dd/mm/yyyy hh:mm AM/PM" ‘formatar como desejar (format as you wish)

ActiveCell.Value = CT

End Sub

Onde escrever a macro

1. Copiar o código acima

2. Escolher no Friso Programador (*) -> Visual Basic ->

3. Duplo click na Folha em que pretendemos correr a macro

4. Colar o código

Como associar a macro a um atalho

5. Escolher no Friso Programador (ou Ver) -> Macros -> Opções -> inserir CTRL+t

Where to place the macro

1. Copy the code above

2. In the Ribbon, choose Developer (*) > Visual Basic

3. Double click on the sheet in which we intend to run the macro

4. Paste the code

How to link the macro to a shortcut

5. In the Ribbon, choose Developer (or View) > Macros > Options > Write CTRL+t

OUTRO EXEMPLO DE CÁLCULO DE HORAS NEGATIVAS (Another Example for Calculating Negative Hours)

2013-07-28T12:34:00.000+01:00

Outra questão que me foi colocada recentemente relacionava-se com o cálculo de atrasos numa escala de produção. Neste caso específico, o atraso implicava que o resultado fosse negativo. E o Excel não reconhece directamente valores negativos quando se trata cálculo com unidades de tempo.
Another question I was asked recently was related to the calculation of delays on a production scale. In this particular case, the delay meant that the result was negative. And Excel does not directly recognize negative values when it comes to calculation units of time.

A solução apresentada foi a seguinte:
My solution was as follows:

Fórmula da Célula E6 e seguintes:
= SE(D6>C6;RESTO(D6-C6;1);RESTO(C6-D6;1))
Formula in E6 and below:
= IF(D6>C6,MOD(D6-C6,1);MOD(C6-D6,1))
As células a vermelho realçam os casos em que o resultado é negativo e, por isso, existe um atraso na escala.
Para que essas células apareçam com fundo vermelho, recorre-se à formatação condicional:
The red cells to enhance the cases in which the result is negative and, therefore, there is a delay in scale.

For these cells appear with red background, makes use of the conditional formatting:

Formatação Condicional / Conditional Format

CALCULAR HORAS ENTRE DUAS DATAS COM UMA CONDIÇÃO (Calculate Hours Between Two Dates with a Condition)

2013-07-27T23:18:00.000+01:00

O cálculo com unidades de tempo (horas, dias, etc.) tem sido um dos temas mais recorrentes aqui no blog.

Há alguns dias atrás, puseram-me uma questão interessante. Tratava-se de calcular o número de horas entre duas datas, subtraindo um determinado conjunto de horas (16) a cada dia do intervalo, caso esse intervalo fosse superior a 24 horas.

Apresentei duas soluções para chegar ao mesmo resultado:

Na primeira, utilizei a função SE para criar um conjunto de condições (Tabela 1).
Na segunda, utilizei as funções DATADIF (que já foi objecto de um post - http://exceleoutrascoisas.blogspot.pt/search/label/DATADIF) e TEMPO.

Fórmulas da Tabela 1:
D4 =SE((C4-B4)*24<16;(C4-B4)*24;SE(E((C4-B4)*24>=16;(C4-B4)*24<=24);(C4-B4)*24-16;SE((C4-B4)*24>24;(C4-B4)*24-(ARRED((C4-B4)*24/24;0)*16)))))
- A formatação da célula é 0,00
E4 =(SE((C4-B4)*24<16;(C4-B4)*24;SE(E((C4-B4)*24>=16;(C4-B4)*24<=24);(C4-B4)*24-16;SE((C4-B4)*24>24;(C4-B4)*24-(ARRED((C4-B4)*24/24;0)*16)))))/24
- A formatação da célula é [hh]:mm

Fórmula da Tabela 2:
D12 =C12-B12-DATADIF(B12;C12;"d")*TEMPO(16;0;0)
- A formatação da célula é [hh]:mm

Time units calculation (hours, days, etc.) has been one of the most frequently issues here in the blog.

A few days ago, someone put me an interesting question. It was abour calculating the number of hours between two dates by subtracting a set of hours (16) in each day in the range if that range was longer than 24 hours.

I have presented two solutions to reach the same result:

In the first one, I used the IF function to create a set of conditions (Table 1).
In the second, I used DATEDIF function (which has been the subject of a post - http://exceleoutrascoisas.blogspot.pt/search/label/DATADIF) and TIME function.

Formulas in Table 1:
D4 =IF((C4-B4)*24<16,(C4-B4)*24,IF(AND((C4-B4)*24>=16,(C4-B4)*24<=24),(C4-B4)*24-16,IF((C4-B4)*24>24,(C4-B4)*24-(ROUND((C4-B4)*24/24,0)*16)))))
- Cell format is 0.00
E4 =(IF((C4-B4)*24<16,(C4-B4)*24,IF(AND((C4-B4)*24>=16,(C4-B4)*24<=24),(C4-B4)*24-16,IF((C4-B4)*24>24,(C4-B4)*24-(ROUND((C4-B4)*24/24,0)*16)))))/24
- Cell format is [hh]: mm

Formula in Table 2:
D12 = C12-B12-DATEDIF (B12, C12, "d") * TIME (16, 0, 0)
- Cell format is [hh]: mm

CALCULAR HORAS EXTRA, TURNO NOCTURNO, HORAS NEGATIVAS, ETC (Overtime Calculation, Night Shift, Negative Hours, Etc.)

2013-06-15T00:31:00.000+01:00

Devido às inúmeras solicitações de ajuda, quer aqui no blog quer no meu dia-a-dia, em questões com cálculo de horas, criei um pequeno modelo de folha de ponto, focada no cálculo de horas extra e de turnos específicos.
É um modelo editável e facilmente adaptável à maioria das situações relacionadas profissionais.
Como sempre, estou disponível para sugestões de melhoria e para ajudar nas questões que eventualmente estejam omissas.
Espero que seja útil.

Due to numerous requests for help, either here on the blog or in my day-to-day issues in the calculation of hours, I have created a small timesheet template, focused on the calculation of overtime and specific shifts .It is an editable template and easily adaptable to most business situations.As always, I am available to suggestions for improvement and to help answer questions that may be missing.I hope you find it useful.

Baixar Modelo

Download Template

Informar se não conseguir baixar

Please report if download link not work

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

2012-08-06T16:44:00.000+01:00

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

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

2012-03-13T23:59:00.000+00:00

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:

Construimos a tabela de dados (DATABASE) - matriz de dados.
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.
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:

Build the data table (DATABASE) - data array.
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.
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.

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

2012-03-05T23:20:00.000+00:00

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:

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))"
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))"
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:

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))"
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))"
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.

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

2012-02-16T11:07:00.000+00:00

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.

Suponhamos agora que queríamos apurar a produção mensal de cada mecânico. Para isso, criamos outra tabela noutra folha, onde vamos aplicar a Função UDF (à qual vamos chamar "ListaUnica").
Now suppose we wanted to determine the monthly production of each mechanic. For this, we create a new table in another sheet, where we apply the UDF function (which we name "ListaUnica").

Antes, porém, vamos nomear o intervalo na coluna C ("Mecânicos") para simplificar a fórmula.
But first, let's name the range in column C ("Mecânicos") to simplify the formula.

Para introduzirmos a Função UDF, clicamos sobre o separador da folha com o botão direito e escolhemos "Ver Código". A seguir, na caixa à esquerda (VBA Project) clicar novamente com o botão direito, escolher Inserir - Módulo. Copiar depois o código seguinte para dentro do Módulo.
To introduce the UDF function, click on the tab sheet with the right mouse button and choose "View Code". Then in the left box (VBA Project) click again with the right button, choose Insert - Module. Then Copy the following code into the module.

O passo seguinte é criar a tabela com os elementos que queremos filtrar:
The next step is to create the table with the elements that we want to filter:

Numa outra folha, seleccionamos um intervalo (por exemplo, A2:A30) e na barra de fórmulas escrevemos o seguinte: "=ListaUnica(Mecânicos)". A seguir premimos "Ctrl + Shift + Enter" para que a fórmula seja uma fórmula matriz.
In another sheet, we select an interval (eg, A2:A30) and write "= ListaUnica (Mecânicos)." in the formula bar. Then press "Ctrl + Shift + Enter" to make it a matrix formula.

O aspecto final da fórmula em todas as células do intervalo seleccionado será: "{=ListaUnica(Mecânicos)}".
The formula final aspect in every cell of the selected range will be: "{=ListaUnica (Mecânicos)}."

Para apurar os valores correspondentes a cada mês e a cada mecânico, basta introduzir uma fórmula SOMA.SE.S, tal como é mostrado no modelo.
To determine the values corresponding to each month and each mechanic, just enter a formula SUMIFS as shown in the template.

Como se pode verificar, este modelo pode ser desenvolvido à medida de cada caso.
As can be seen, this template can be developed according to each case.

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

2012-01-04T14:08:00.002+00:00

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

EXCEL - CALCULAR FERIADOS MÓVEIS (Calculate Religious Holidays)

2011-12-18T20:39:00.000+00:00

Agora que se fala cada vez mais na supressão de alguns feriados em Portugal, bem como das "abençoadas pontes" que proporcionam fins de semana prolongados ou autênticas mini-férias, deixo aqui um modelo de cálculo dos feriados móveis e das "pontes" para os próximos 20 anos. A sua utilidade será discutível mas serve sempre como curiosidade de cultura geral.

Neste modelo foi considerado que, de um modo geral, os feriados móveis são religiosos e estão indexados ao evento da Páscoa.

Assim, o Domingo de Páscoa é por definição o primeiro Domingo de lua cheia após o equinócio de Março (ou da Primavera). No Hemisfério Norte, o equinócio ocorre no dia 20 ou 21 de Março. Deste modo, o Domingo de Páscoa só pode acontecer entre os dias 22 de Março e 25 de Abril. A Terça-feira de Carnaval acontece 47 dias antes do Domingo de Páscoa e o feriado de Corpo de Deus 60 dias após o Domingo de Páscoa e calha sempre numa quinta-feira. O Senhor de Matosinhos é sempre numa terça-feira e ocorre 51 dias após o Domingo de Páscoa.

Estes são os feriados móveis que conheço e praticados na minha região. Haverá com certeza outras festividades locais cuja comemoração seja igualmente volante. Mas a sua data estará inevitavelmente ligada ao evento da Páscoa.

Now that they're talking about the suppression of some holidays in Portugal, as well as the "blessed Monday off or Friday off" that provide long weekends or real mini-vacation, here is a template to calculate of religious holidays and "bridges" for next 20 years. Its usefulness is doubtful but it always serves as curiosity.
In this model it was considered that, in general, religious holidays are always indexed to the Easter event.
So, Easter Sunday is by definition the first full moon Sunday after the in March (or spring) equinox. In the Northern Hemisphere, the equinox occurs on March 20 and 21. Thus, Easter Sunday can only be between March 22 and April 25. Carnival day (always on a Tuesday) takes place 47 days before Easter Sunday and Corpus Christi 60 days after Easter Sunday and always in a Thursday. Lord of Matosinhos is always on a Tuesday and occurs 51 days after Easter Sunday.
These are the mobile holidays I know for my location. There are certainly other places where festivities are celebrated on a variável date. But that date will inevitably be linked to the Easter event.

EXCEL 2007 - FUNÇÕES PARA CALCULAR HORAS (Functions to Calculate Hours)

2011-12-05T12:46:00.001+00:00

Uma das dificuldades recorrentes no utilizador comum de Excel prende-se com o cálculo de tempo, particularmente nas situações que envolvem horas e minutos.

Eis aqui algumas sugestões para a resolução desses problemas, nomeadamente somar horas, diferença entre horas, arredondamento de valores hora e cálculo de minutos entre datas:

One of the recurring difficulties in common Excel users relates to the calculation of time, particularly in situations involving hours and minutes.
Here are some suggestions for solving these problems, including adding time values, difference between hours, hours rounding up and calculation of minutes between dates:

1. SOMAR HORAS (Summing Hours)

Existem duas possibilidades para somar valores de tempo - somar parcelas de tempo para determinar um valor tempo (Coluna A) ou calcular a soma aritmética de um conjunto de valores (Coluna C).

Para realizar correctamente estas operações, temos primeiro de formatar as células da seguinte forma:

No caso da Coluna A, pretende-se saber a que tempo do dia corresponde a adição das células A1+(A2+A3+A4+A5). Assim, em A6 introduz-se a fórmula "=SOMA(A1:A5)" e formata-se todas as células com "hh:mm" (Seleccionar Intervalo, Botão Direito do Rato, Formatar Células, Personalizado, escolher "hh:mm").

No caso da Coluna C, queremos obter a soma aritmética de várias parcelas de tempo. Então, em C6 introduz-se a fórmula "=SOMA(C1:C5)"e formata-se as células com "[hh]:mm" (Seleccionar Intervalo, Botão Direito do Rato, Formatar Células, Personalizado, escolher "[hh]:mm").

There are two possibilities to add time values - adding portions of time to determine a time value (Column A) or calculate the arithmetic sum of a set of values (Column C).
To perform these operations correctly, first we must format cells as follows:
In the case of Column A, the aim is to know what time of day is the sum of cells A1 + (A2 + A3 + A4 + A5). Thus, in A6 enter the formula "= SUM(A1: A5)" and format all cells with "hh:mm" (Select Range, Right Mouse Button, Format Cells, Customize, choose "hh:mm").
For Column C, we get the arithmetic sum of several parcels of time. Then, in C6 introduces the formula "= SUM(C1: C5)" and format cells to "[hh]:mm" (Select Range, Right Mouse Button, Format Cells, Customize, choose "[hh]:mm").

2. CALCULAR A DIFERENÇA ENTRE HORAS (Calculate the difference between hours)

Para calcular a diferença entre valores hora, é necessário utilizar a seguinte fórmula:

Em D1, por exemplo, introduzimos "=C1-B1+SE(B1>C3;1)" e formata-se todas as células com "hh:mm" (Seleccionar Intervalo, Botão Direito do Rato, Formatar Células, Personalizado, escolher "hh:mm").

To calculate the difference between hour values, you must use the following formula:
In D1, for example, enter "= B1-C1 + IF (B1> C3, 1)" and format all cells with "hh:mm" (Select Range, Right Mouse Button, Format Cells, Custom, choose "hh:mm").

3. ARREDONDAR VALORES HORA (Rounding up hours)

Para obtermos um valor de hora inteiro resultante da diferença entre valores tempo, introduzimos em D2, por exemplo, a seguinte fórmula: "=ARRED.EXCESSO((C2-B2)/(1/24);1)". Formata-se todas as células das colunas B e C com "hh:mm" e (Seleccionar Intervalo, Botão Direito do Rato, Formatar Células, Personalizado, escolher "hh:mm"), e da coluna D com "0"(Seleccionar Intervalo, Botão Direito do Rato, Formatar Células, Personalizado, escolher "0").

To obtain an integer hour value from the difference between time values, we use in D2, for example, the following formula: "=CEILING((C2-B2)/(1/24),1)". Formats are all cells in columns B and C to "hh:mm" and (Range Select, Right Mouse Button, Format Cells, Custom, choose "hh:mm"), and column D with "0" (Select range, Right Mouse Button, Format Cells, Custom, choose "0").

4. CALCULAR O NÚMERO DE MINUTOS ENTRE DATAS (Calculate number of minutes between dates)

Para determinar o número de minutos da diferença entre duas datas, introduzimos em D2, por exemplo, a fórmula "=(C2-B2)*1440". O valor "1440" corresponde a 24 horas x 60 minutos. A formatação das colunas B e C é "dd-mm-aaaa hh:mm" (Seleccionar Intervalo, Botão Direito do Rato, Formatar Células, Personalizado, escolher "dd-mm-aaa hh:mm"), e da coluna D com "0"(Seleccionar Intervalo, Botão Direito do Rato, Formatar Células, Personalizado, escolher "0").

To determine the difference between two dates in minutes, we shall use in D2, for example, the formula "=(C2-B2)*1440". The value "1440" corresponds to 24 hours x 60 minutes. The formatting of columns B and C is "dd-mm-yyyy hh:mm" (Select Range, Right Mouse Button, Format Cells, Custom, choose "dd-mm-yyyy hh:mm"), and column D with "0" (Select Range , Right Mouse Button, Format Cells, Custom, choose "0").

EXCEL 2007 - A função DATADIF (DATEDIF function)

2011-11-28T22:42:00.001+00:00

Para calcular a diferença entre duas datas, o Excel disponibiliza as funções ANO (para se apurar o número de anos), MÊS (para meses), ou simplesmente uma operação aritmética simples (A2-A1, por exemplo) se quisermos o resultado em dias.

Porém, existe uma outra função que simplifica este tipo de operações, sobretudo quando se trata de calcular o número de meses entre anos diferentes. Designa-se por DATADIF e não se encontra listada nos manuais ou na ajuda do Excel. Na figura abaixo podemos verificar como se torna mais simples o cálculo da diferença entre duas datas.

To calculate the difference between two dates, Excel provides functions YEAR (to determine the number of years), MONTH (for months), or just a simple arithmetic operation (A2-A1, for example) if we want the result in days .
But there is another function that simplifies this type of operation, especially when it comes to calculating the number of months from different years. That function is called DATEDIF and is not listed in Excel help. In the pictures below we can see how it makes it easier to calculate the difference between two dates.

EXCEL 2007 - ENCONTRAR VALORES DUPLICADOS (Find Duplicate Values)

2011-11-22T22:26:00.001+00:00

Eis um exemplo de como encontrar e identificar valores duplicados numa tabela.

Here's a template of how to find and identify duplicate values in a table.

Na célula "G4" inserir a formula "=E4&F4"

Copiar depois para as restantes células da coluna G

Na célula "H4" inserir a fórmula "SE(CONTAR.SE($G$4:G4;G4)>1; "VALOR DUPLICADO";"Valor ÚNICO")

Copiar depois para as restantes células da coluna H

Podemos ainda usar a formatação condicional para realçar os valores duplicados

In cell "G18" insert formula "=E18&F18"

Then copy to the cells below in column G

In cell "H18" insert formula "IF(COUNTIF($G$18:G18,G18)>1, "DUPLICATE VALUE","Unique Value")

Then copy to the cells below in column H

Also, we can use conditional formatting to highliht duplicate values

EXCEL 2007 - VALIDAÇÃO DE DADOS NA MESMA CÉLULA COM QUEBRA DE LINHA (Data Validation in Same Cell with Line Break)

2011-11-13T18:26:00.001+00:00

EXCEL 2007 - CRIAR PARÁGRAFO OU QUEBRA DE LINHA NA MESMA CÉLULA (Create PARAGRAPH or LINEBREAK in same cell

2011-11-06T12:29:00.000+00:00

Para criarmos parágrafo no WORD, premimos ENTER. No EXCEL existe uma forma simples de criar uma quebra de linha ou parágrafo na mesma célula. Basta para isso premir, ao mesmo tempo, as teclas ALT + ENTER.

To create paragraph or linebreak in WORD, we simply press ENTER key. In EXCEL, to do that in the same cell, just press ALT + ENTER

EXCEL 2007 VALIDAR DADOS EM COLUNAS E LINHAS SEPARADAS (Data Validation in Separate Columns ans Rows)

2011-10-19T22:54:00.000+01:00

Validar Dados em Colunas Separadas

Data Validation in Separate Columns

Validar Dados em Linhas Separadas

Data Validation in Separate Rows

EXCEL 2007 VALIDAR DADOS NA MESMA CÉLULA (Data Validation in same cell)

2011-10-10T17:16:00.000+01:00

Existem inúmeras formas de criar listas de validação. De modo a tornar os "posts" mais leves vou passar a publicar um conjunto de possibilidades da cada vez.

There are numerous ways to create data validation lists. In order to make the "posts" lighter I'm going to publish a set of possibilities at a time.

Adicionar Itens na mesma célula da lista

Add items in the same cell of the dropdown list

Adicionar itens na mesma célula de outra coluna

Add itens in the same cell in another column

Adicionar Itens na mesma célula da lista

Add items in the same cell of the dropdown list

Na lista da esquerda vemos uma lista de validação simples, tal como é usada frequentemente. Na lista da direita, podemos acrescentar itens à mesma célula.

In the dropdown list on the left, you see a validation list, as is often used. In dropdown list on the right, we can add items to the same cell.

Para inserir o código abaixo, premir com o botão direito do rato sobre o separador da folha, escolher ver código e copiar para dentro da janela de VBA.

To add the code below, right-click mouse button over the sheet tab, choose View code and paste it inside the VBA window.

Adicionar Itens na mesma célula de outra coluna

Add itens in the same cell in another column

Para inserir o código abaixo, premir com o botão direito do rato sobre o separador da folha, escolher ver código e copiar para dentro da janela de VBA.

To add the code below, right-click mouse button over the sheet tab, choose View code and paste it inside the VBA window.

EXCEL 2007 TRABALHAR COM IMAGENS (WORKING WITH IMAGES) #2

2011-09-05T23:07:00.000+01:00

MODELOS COM MACROS (VBA)
Templates with Macros (VBA )

Alguns exemplos de modelos de inserção de imagens, pequenas bases de dados ou outras aplicações.

Some templates to insert images, small image databases and other things that may suit your needs.

MODELO / Template # 2.1

MODELO / Template # 2.2

Apresentar uma imagem de cada vez:

Posicionar todas as imagens na mesma célula

Sobre o Separador da Folha1, clicar com o botão do lado direito do rato, escolher Ver Código

Copiar o seguinte código para a janela VBA

Show an image at a time:

Place all images in the same cell

On Sheet1 tab, right-click mouse button, choose View Code
Copy the following code into the VBA window

MODELO / Template # 2.3

EXCEL 2007 TRABALHAR COM IMAGENS (Working with Images) #1

2011-09-05T15:32:00.000+01:00

MODELO SÓ COM FÓRMULAS

Template With Formulas Only

Alguns exemplos de modelos de inserção de imagens, pequenas bases de dados ou outras aplicações.

Some templates to insert images, small image databases and other things that may suit your needs

Fig 01

Fig 02

Fig 03

EXCEL 2007 FUNÇÕES CONTAR (Count Functions)

2011-08-25T16:54:00.000+01:00

Existem várias formas de contar valores ou dados. Para executar esta tarefa, podemos utilizar várias funções além da função contar.

There are several ways to count values or data. To perform this task, we can use various functions besides the function count.

Função CONTAR / COUNT Function

Conta o número de células que contêm números

Count the number of cells that contain numbers

Fórmula em F2: =CONTAR(A2:E2) / =COUNT(A2:E2)

Função CONTAR.VAL / COUNTA Function

Conta o número de células que contêm valores (não em branco)

Count the number of cells with values (not blank)

Fórmula em F2: =CONTAR.VAL(A2:E2) / =COUNTA(A2:E2)

Função CONTAR.VAZIO / COUNTBLANK Function

Conta o número de células em branco

Count the number of blank cells

Fórmula em F2: =CONTAR.VAZIO(A2:E2) / =COUNTBLANK(A2:E2)

Função CONTAR.SE / COUNTIF Function

Conta o número de células que respeitam uma dada condição

Count the number of cells that meet a certain condition

Fórmula em F2: =CONTAR.SE(A2:E2;"BBB") / COUNTIF (A2:E2;"BBB")

Em alternativa, podemos usar a Função SOMARPRODUTO

As alternative, we can use SUMPRODUCT Function

Fórmula em F2:=SOMARPRODUTO(--(F1=A2:E2)) / =SUMPRODUCT(--(F1=A2:E2))

Função CONTAR.SE.S / COUNTIFS Function

Aplica critérios a células através de múltiplos intervalos e conta o número de vezes que todos os critérios são satisfeitos

Applies criteria to cells across multiple ranges and counts the number of times all criteria are met

=CONTAR.SE.S(B2:G5;"4") / =COUNTIFS(B2:G5;"4")

Conta quantas vezes o nº 4 existe na tabela
Counts how many cells with nº 4 in the table
=CONTAR.SE.S(B2:B5;"4";E2:E5;"7") / =COUNTIFS(B2:B5;"4";E2:E5;"7")
Conta quantas vezes os nº 4 e 7 são comuns em Z1 e Z4 na mesma linha
Counts how many cells, from Z1 and Z4, with nº 4 and nº 7 in the same table row
=CONTAR.SE.S(A2:G2;"<5";A2:G2;">2") / =COUNTIFS(A2:G2;"<5";A2:G2;">2")
Conta quantos números maiores que 2 e menores que 5 existem na linha A
Counts how many cells in row A with numbers <5 and >2

Técnicas de CONTAR com outras funções

COUNT Techniques using other fuctions

{=SOMA(SE(FREQUÊNCIA(CONTAR.SE(Zon2; "<"&Zon2);CONTAR.SE(Zon2; "<"&Zon2))>0;SE(CONTAR.SE(Zon1;Zon2)>0; 1; 0); ""))}
Conta os valores comuns às duas listas (Zon1 e Zon2), excluindo os valores repetidos

{=SUM(IF(FREQUENCY(COUNTIF(Zon2; "<"&Zon2);COUNTIF(Zon2; "<"&Zon2))>0;IF(COUNTIF(Zon1;Zon2)>0; 1; 0); ""))}

Count common UNIQUE DISTINCT cell values between Zon2 and Zon1 (repeated values excluded)

{=SOMA(SE(CONTAR.SE(Zon1;Zon2)>0; 1; 0))}
Conta TODOS os valores comuns em Zon2 entre as duas listas (Zon1 e Zon2)

{=SUM(IF(COUNTIF(Zon1;Zon2)>0; 1; 0))}

Count ALL common cell values between Zon2 and Zon1

NOTA:
Para ser mais rápido e mais fácil escrever a fórmula, devemos nomear cada intervalo de dados.
Por exemplo, para Zon1: Seleccionar o intervalo, Clicar o botão direito, escolher Nomear Intervalo, escrever Zon1
As fórmulas utilizadas são FÓRMULAS DE MATRIZ. Para accionar uma Fórmula de Matriz deve executar o seguinte:
Escrever a fórmula =SOMA(SE(CONTAR.SE(Zon1;Zon2)>0; 1; 0)) Premir Ctrl+Shift+Enter

NOTE:
To write formulas in a faster and easier way, you should name each data range.
For example, Zon1: Select the range, right click mouse botton, Choose Name a Range, Write Zon1
These are ARRAY FORMULAS. To execute na Array Formula you must:
Write =SUM(IF(COUNTIF(Zon1;Zon2)>0; 1; 0)) Press Ctrl+Shift+Enter

=SOMARPRODUTO(--("Europe"=A2:A8))
Conta as células que contêm Europe no intervalo A2:A8

=SOMARPRODUTO(--("Europe"=A2:A8))

Count cells in range C8:C14 containing Europe

=SOMARPRODUTO(--(A2:A8="America");--(B2:B8="Ca"))
Conta as linhas cujas células das colunas Client e Prod contenham os critérios pretendidos

=SOMARPRODUTO(--(A2:A8="America");--(B2:B8="Ca"))

Count rows which cell values contain defined criteria (America in Client Range and Ca in Prod Range)

=SOMARPRODUTO(--(A2:A8="Europe");--(B2:B8="Ri");--(C2:C8>130);--(C2:C8<190))
Conta as linhas cujas células das colunas Client e Prod contenham os critérios pretendidos e com valores entre 130 e 190

=SOMARPRODUTO(--(A2:A8="Europe");--(B2:B8="Ri");--(C2:C8>130);--(C2:C8<190))

Count rows which cell values contain defined criteria (Europe in Client Range and Ri in Prod Range), and values between 130 and 190

NOTA:
Para ser mais rápido e mais fácil escrever a fórmula, devemos nomear cada intervalo de dados.
Por exemplo, para Client: Seleccionar o intervalo, Clicar o botão direito, escolher Nomear Intervalo, escrever Client
As fórmulas ficariam assim
=SOMARPRODUTO(--("Europe"=Client))
=SOMARPRODUTO(--(Client="America");--(Prod="Ca"))
=SOMARPRODUTO(--(Client="Europe");--(Prod="Ri");--(Val>130);--(Val<190))
NOTE:
To write formulas in a faster and easier way, you should name each data range.
For example, Client: Select the range, right click mouse botton, Choose Name a Range, Write Client
Formulas would be writen like this
=SUMPRODUCT(--("Europe"=Client))
=SUMPRODUCT(--(Client="America");--(Prod="Ca"))
=SUMPRODUCT(--(Client="Europe");--(Prod="Ri");--(Val>130);--(Val<190))

EXCEL 2007 CRIAR UMA TABELA (Create a Table)

2011-08-21T23:45:00.000+01:00

Com o Excel 2007, é muito simples criar uma tabela personalizada. A maioria dos utilizadores comuns desconhece esta ferramenta ou simplesmente não a usa porque pensa que é complicada. Nada de mais errado.

With Excel 2007, it's very simple to create a custom table. Most ordinary users don't know about this tool or simply do not use it because they think it is very complicated. Nothing much wrong.

Exemplo:
Abrimos um novo livro ou um livro existente
Seleccionamos um intervalo de células, por exemplo, C5:K25
No Friso, seleccionamos o separador INSERIR e premimos TABELA
Na janela que aparece, apenas temos de confirmar o intervalo seleccionado ou escolher um outro adequado ao que pretendemos. Podemos também definir se desejamos acrescentar uma linha de cabeçalhos à nossa tabela.

Ao criar uma tabela, um novo separador é adicionado ao Friso - Ferramentas da Tabela, Estrutura - no qual podemos incrementar a nossa tabela com um conjunto variado de funcionalidades tais como, formatar de forma diferente a primeira ou a última coluna, apresentar Linha de Total, Listar colunas ou linhas, inserir ou eliminar linhas ou colunas, etc.

Podemos igualmente alterar o aspecto da tabela, bastando para isso escolher uma das inúmeras opções que aparecem no friso. Se mesmo assim estas não agradarem pode ainda formatar a tabela recorrendo aos procedimentos tradicionais (Formatar Células), este é contudo um processo muito mais moroso.

Um dos aspectos que comprova a versatilidade desta ferramenta: se numa determinada célula da tabela introduzirmos uma fórmula, a mesma é automaticamente copiada para todas as células da mesma coluna.

Mesmo se quisermos imprimir apenas a tabela, basta seleccionarmos uma célula da mesma, premir imprimir e na caixa de diálogo que aparece escolhemos Tabela.

Como podemos comprovar, o processo é simples, rápido e intuitivo.

Take this example:
Open a new worbookbook or an existing workbook
Select a range of cells, eg, C5:K25
On the Ribbon, select the tab INSERT and press TABLE
In the window that appears, just have to confirm the selected range or choose another appropriate to what you want. You can also define if you want to add a headers to the table.

When creating a table, a new tab is added to the Ribbon - Table Tools, Structure - which appears whenever a table cell is active, in which you can grow your table with a variety of features such as formatting differently the first or last column, provide Total Row, List columns or rows, insert or delete rows or columns, etc..

You can also change the look of the table, by simply choosing one of the many options that appear on the ribbon. If they still do not suit your preferences, you can format the table using the traditional procedures (Format Cells), however this is a process much slower.

One aspect that demonstrates the versatility of this tool: if you enter a formula in a particular table active cell, that is automatically copied to all cells in the same column.

Even if you want to print only the table, simply select a cell of the table range, press print and in dialog box that appears choose Table. No need to set the print area.

As you see, the process is simple, fast and intuitive.