Excel 2010 - É necessário somar o intervalo dinâmico com base nos 3 meses finais

Question

Estou tentando somar um intervalo dinâmico para uma lista de entradas 3 meses antes e 3 meses após um mês específico. Acho que preciso usar uma matriz para obter isso, mas não consigo encontrar o que preciso fazer.

Veja o que eu tenho atualmente no formato Matrix:

ROW  |  COLUMN A  |  COLUMN B  |  COLUMN C  |  COLUMN D  |  COLUMN E
---  |  ------------------------------------------------------------
1    |  Username  |  StartMo   |  January   |  February  |  March
     |  ____________________________________________________________
2    |  User 1    |  March     |  1,000     |  1,500     |  1,000
3    |  User 2    |  April     |  2,000     |  1,000     |  1,500
4    |  User 3    |  April     |  1,000     |  1,750     |  1,000

• Usuários 1-10 na coluna A
• Início do mês na coluna B
• Dados mensais do ano passado nas colunas C-M
• Cabeçalhos informando o mês dos dados na Linha 1

O que eu preciso fazer é somar se a coluna A tiver o usuário que eu quero, mas somente se a linha 1 estiver dentro de 3 meses do mês de início.

Pensamentos? Eu adoraria postar uma foto dos dados, mas desde que eu sou novo, não vai me deixar.

    

Answer 1

Para cada linha, você deseja somar um intervalo que seja um subconjunto dessa linha, determinado pelo “mês inicial” na coluna B. Vamos começar pegando sua descrição textual do intervalo e expressando-a de maneira fórmula. Para fazer isso, precisamos converter os nomes dos meses em números de 1 a 12. Não sei como fazer isso diretamente, mas é fácil extrair o número do mês de uma data , e podemos transformar um nome de mês em uma data, acrescentando um número (dia do mês). Por exemplo,

• se B2 for March , então
• B2 & "1" é March1 e
• MONTH(B2 & "1") é 3 .
• Se isso não funcionar na sua versão do Excel, tente MONTH(DATEVALUE(B2 & "1")) .

OK, a pergunta pede para somar os meses número do mês −3 até número do mês +3. Por exemplo, para o usuário 2 (linha 3), onde B3 é “abril” (ou seja, 4), queremos a soma de janeiro (1) até julho (7), já que 4−3 = 1 e 4 + 3 = 7. Mas há um problema: nem todos os sete meses necessários estão garantidos na folha. Para o usuário 1 (linha 2), B2 é “março”, então queremos dezembro (do ano anterior) até junho. Os dados do ano anterior e do ano seguinte não estão disponíveis então truncamos o nosso intervalo para janeiro a junho. Fazemos isso forçando o mês inicial a não ser menor que 1 e o mês final a não mais de 12.

Se fizermos isso com uma fórmula monolítica, será praticamente ilegível. É muito mais claro usar "colunas auxiliares" para armazenar valores intermediários. Então, defina

• P2 - número do mês correspondente a B2 :% =MONTH(B2 & "1")
• Q2 - início do mês do intervalo:
  =MAX(P2-3, 1)
• R2 - mês final do intervalo: =MIN(P2+3, 12)

Destacar as células que selecionamos é um truque e pode nos ajudar a verificar se estamos fazendo o que queremos fazer. Definir um formato condicional com uma fórmula de

=AND((COLUMN())-2>=$Q2, (COLUMN()-2)<=$R2)

na nossa matriz de dados, temos isto:

^{(Aimageméumlinkparaumacópiamaiordesimesma.)}

UmaboaferramentaparagerarintervalosdinâmicosnoExceléo OFFSET função:

OFFSET(reference, rows, cols, [height], [width])

que identifica uma regi rectangular de culas (possivelmente uma cula ica; isto é, um retângulo 1x1; possivelmente maior) pela sua posição em relação a alguma outra célula. Por exemplo, OFFSET(B2, 0, 1, , 6) identifica o intervalo C2:H2 , porque

• Da célula B2 , descendo 0 linhas e a coluna 1 direita leva você para C2 ,
• A altura é padronizada como 1, portanto, o intervalo é totalmente na linha 2 e
• A largura é 6: C2 , D2 , E2 , F2 , G2 e H2 , ou C2:H2 para resumir, são seis colunas.

Bem, a largura do intervalo é simplesmente o número da coluna final menos o número da coluna inicial mais 1. Então o resultado que você quer é simplesmente

=SUM(OFFSET(B2, 0, Q2, , R2-Q2+1))

E podemos colocar isso na coluna O :

Aquiestáotextoacima,emformadetexto,paraquevocêpossacopiá-loecolá-lo:

_{UsernameStartMoJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTheAnswerAndyMarch102202402100220024002100022000240002100002200002400002=SUM(OFFSET(B2,0,Q2,,R2-Q2+1))=MONTH(B2&"1") =MAX(P2-3,1) =MIN(P2+3,12) Bob April 103 203 403 1003 2003 4003 10003 20003 40003 100003 200003 400003 =SUM(OFFSET(B3,0,Q3,,R3-Q3+1)) =MONTH(B3&"1") =MAX(P3-3,1) =MIN(P3+3,12) Charlie April 104 204 404 1004 2004 4004 10004 20004 40004 100004 200004 400004 =SUM(OFFSET(B4,0,Q4,,R4-Q4+1)) =MONTH(B4&"1") =MAX(P4-3,1) =MIN(P4+3,12)}

Você pode precisar colá-lo no Word como texto e copiá-lo e colá-lo no Excel.