A grande vantagem em utilizar o formato de banco de dados para criar planilhas é que os valores podem ser retornados de forma mais prática. Num banco de dados, cada coluna contém um campo com uma lista de valores.
Excel - Dados organizados em forma de banco de dados
Os valores da mesma linha formam um registro, ou seja, são informações relacionadas.
Excel - Linha/Registro do banco de dados
Assim, o Excel pode retornar valores de um registro simplesmente se deslocando horizontal e verticalmente.
Excel - Retornando um valor do banco de dados
No exemplo abaixo, precisamos calcular a média de preço obtida por cada vendedor. Para isto, teremos que utilizar uma FÓRMULA MATRICIAL.
Excel - planilha para cálculo da média de preço de cada vendedor
As fórmulas matriciais realizam cálculos usando argumentos matriciais, que são conjuntos de células equivalentes e que possuem o mesmo número de linhas e colunas. Para o exemplo, utilizaremos como argumentos matriciais o intervalo de células da coluna “A” e o intervalo com o mesmo número de células da coluna “D”.
Excel - argumentos matriciais A2:A12 e D2:D12
Vamos selecionar a célula onde será fornecido o valor. Para calcular a média, escolhemos a função estatística “MÉDIA”.
Excel - função estatística "MÉDIA"
Como argumento, vamos utilizar a função “SE”, que verificará quais as células do primeiro argumento matricial (A2:A12) contém o valor “James” e retornará os respectivos valores do segundo argumento (D2:D12) para que seja calculada a média.
Excel - onde houver "James" na matriz A2:A13, retornar o valor na mesma linha da matriz D2:D12
Para que a fórmula matricial funcione, temos que selecioná-la e acionar as teclas CTRL + SHIFT + ENTER. Após este comando, a fórmula passa a ser exibida entre dois colchetes que indicam o seu comportamento matricial. A fórmula final ficará então com a seguinte sintaxe: {=MÉDIA(SE(A2:A12=”James”;D2:D12))}
Excel - fórmula matricial
Para que a fórmula possa ser estendida para as células ao lado através da alça de preenchimento, vamos utilizar referências absolutas (“$”) na fórmula. Assim, os argumentos matriciais não serão modificados. Lembre-se: sempre que você alterar uma fórmula matricial, as teclas CTRL + SHIFT + ENTER devem ser novamente pressionadas. Para saber mais sobre referências absolutas, veja ESTE TUTORIAL.
Excel - fórmula com referências absolutas "$"
Vamos estender a fórmula. Para saber mais sobre a alça de preenchimento, acesse ESTE TUTORIAL.
Excel - estendendo a fórmula com a alça de preenchimento
Agora basta alterar os nomes para que as fórmulas copiadas forneçam os resultados desejados, mas lembre-se novamente: sempre que você alterar uma fórmula matricial, as teclas CTRL + SHIFT + ENTER devem ser novamente pressionadas.
Excel - adaptando o critério das fórmulas para cada vendedor
Veja o resultado:
Excel - planilha com fórmulas matriciais
Popularity: 15%
