Excel - Analisando dados em um modelo

Question

Não tenho certeza se isso é possível no Excel.

Eu tenho uma planilha que parece:

+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
|               |              August            |            September          |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Week Starting | 04/08 | 11/08  | 18/08 | 25/08 | 01/09 | 08/09 | 15/09 | 22/09 |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Monday        |       |        |       |       |       |       |       |       |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Tuesday       |       |        |       |       |       |       |       |       |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Wednesday     |       |        |       |       |       |       |       |       |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Thursday      |       |        |       |       |       |       |       |       |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Friday        |       |        |       |       |       |       |       |       |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Week Extra    |       |        |       |       |       |       |       |       |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Week Total    |       |        |       |       |       |       |       |       |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+

(A folha real tem todos os meses.)

E dados brutos semelhantes a:

+------+------+----------+
| DDMM | Year | Quantity |
+------+------+----------+
| 0408 | 2014 |        2 |
| 0708 | 2014 |        7 |
| 0509 | 2014 |       12 |
| 0508 | 0000 |        6 |
+------+------+----------+

Eu preciso analisar os dados brutos e adicioná-los à tabela do calendário. A primeira linha de dados adicionaria 2 à segunda-feira sob "04/08".

Se o ano for 0000, preciso adicionar o número ao "Week Extra" para a semana correta.

Isso é possível?

Obrigado

    

Answer 1

A melhor maneira é converter as datas em sua tabela bruta em datas reais do Excel. A partir da data real do Excel, você pode extrair o texto para o dia da semana, o texto do mês e a data de início da semana, que pode ser convertida novamente em texto para corresponder à sua tabela. As fórmulas para fazer isso estão na imagem abaixo (porque é muito detalhado para digitar).

Paracolocá-losnasuatabeladeagendas,vocêpodefazeralgumascoisas.Umatabeladinâmica,daqualvocêpodeusar=getpivotdata()paraextrairvaloresparacadacélulanocalendário,éummétodo.Euprefiro,emvezdisso,usar=sumproduct(),poistemmenossobrecarga.Fórmulasestãonaimagemabaixoeumaexplicaçãorápidasegue.

=sumproduct() pode testar várias condições para verdadeiro / falso, em um intervalo de valores (neste caso, testando as condições de cada linha) e, em seguida, somar os resultados de outro intervalo quando todas as condições forem verdadeiras para a linha . Aqui estamos testando que E2: E5 contém o DD / MM em J2, que F2: F5 contém o Texto do Mês em J1, e que G2: G5 contém o texto do WorkDay em I3. Se todas as condições forem verdadeiras, então ele pega e soma o valor em C2: C5. Uma palavra de cautela com esta fórmula, os intervalos que você está testando devem SEMPRE ter o mesmo tamanho. Você não pode testar E2: E5 para DD / MM e F2: F20 para o mês. O Sumproduct lançará um erro.