Existe uma maneira de adicionar um número arbitrário de células com base em um critério específico no Excel?

Question

Portanto, tenho uma planilha com vários minigráficos, como visto abaixo. Os números em azul são inseridos pelo usuário e todos são adicionados juntos no gráfico com as células cinzas no canto superior esquerdo.

Agora, faço isso com uma fórmula de soma básica. A planilha real é muito maior, mas no exemplo C3 seria = soma (J3 + C13 + J13), D3 teria = soma (K3 + D13 + K13) e assim por diante. Como você pode imaginar, é uma PITA adicionar novos gráficos ou remover os existentes.

O que eu gostaria seria uma maneira de fazer isso acontecer automaticamente sem precisar adicionar células individuais, para que eu pudesse adicionar ou remover quantos gráficos eu quisesse enquanto ainda tinha os números adicionados.

Assim, a célula E5 contaria todas as células em uma planilha onde um número é três células abaixo de III e três células à direita de N / A. Ou algo que realiza a mesma coisa.

Existe uma maneira de realizar isso sem alterar o layout da minha planilha?

(ignore o N / As, acabei de copiá-lo e colá-lo para criar uma imagem de exemplo mais simples sem modificar as fórmulas que quebraram quando fiz isso).

    

Answer 1

Em suma, não há como fazer isso sem modificar seus layouts de gráficos que não são melhores do que especificar muito mais células do que você jamais usaria. No meu exemplo acima, termino em cell J500003 . Suponha que seus gráficos terminem na linha 470. Isso não é um problema. Células vazias contam como 0 e não afetam a soma. Se você fizer a fórmula arbitrariamente alta para começar, nunca precisará modificar a fórmula novamente.

Se você estiver aberto a uma pequena adição ao layout dos gráficos, a fórmula poderá ser mais curta, mas ainda acabará especificando um grande número de células não usadas. Se você adicionar "Header" (ou "Status" ou qualquer outra coisa) na célula acima de Open, então você pode tratar essas colunas como um banco de dados e usar DSUM

Por exemplo, aqui está o novo layout:

    A       B       C   D   E   F
12 Header           I   II  II  Total
13 Open             1   75  21  97
14 Closed           21  312 39  372
15 N/A              2   47  7   56
16 Unreviewed                   
17 Total possible                   
18 Total Reviewed   24  434 67  525
19 Percent Complete                 
20
21                  
22                  I   II  II  Total
23 Open             1   75  21  97
24 Closed           21  312 39  372
25 N/A              2   47  7   56
26 Unreviewed                   
27 Total possible                   
28 Total Reviewed   24  434 67  525
29 Percent Complete                 

(Você realmente só precisa adicionar sua seqüência de texto nas células A12 e H2. Eu usei "Header" no meu layout de exemplo.)

Agora, em A1, especifique a coluna desejada e, em A2, especifique o valor a ser correspondido nessa coluna. Por exemplo, eu quero adicionar todos os "I" que são "Aberto", então A1 é "Cabeçalho" (porque esse é o nome que eu dei à coluna de rótulos) e A2 será "Aberto" e a fórmula então ser =DSUM(A12:F140000,"I",A1:A2)+DSUM(H2:M140000,"I",A1:A2) Faz uma fórmula muito mais curta, mas não é mais7 ou mais eficiente e ainda faz referência a células potencialmente não usadas.

Se você quiser evitar tocar as células não utilizadas por completo e não quiser adicionar texto a duas células, terá que recorrer ao loop com o VBA.

    

Answer 2

A fórmula a seguir funcionará, mas somente se você alterar alguma formatação.

Formatando as alterações necessárias:

1. As somas não podem acontecer dentro do intervalo de células que você deseja calcular. Então você pode mudar sua formatação para algo como isto

Depoisdeterseusresumosforadocorpoprincipal,afórmulaaseguirfuncionarábem.SeriaemC4edepoiscopiadoecoladonasoutrascélulas.(AfórmulaéumafórmuladematrizedeveserdigitadacomCTRL-SHIFT-ENTER)

{=SUM(IF(COLUMN($A$12:$M$25)=(IF($A$12:$M$12=C$3,COLUMN($A$12:$M$12))),IF(ROW($A$12:$M$25)=IF($A$12:$A$25=$A4,ROW($A$12:$A$25)),$A$12:$M$25)))}

Expanda$A$12:$M$25paraoarraymaiorquevocêdesejacobrireverifiquese$A$12:$M$12éaprimeiralinhainteiradoarraye$A$12:$A$25éocomprimentointeirodoarray.

Estafórmulafuncionaráapenasenquantovocêmantiveropadrãodegrade,porqueelesóolhaparaoI,II,IIInaprimeiralinhaeo"open" "closed" and "N/A" na primeira coluna e, em seguida, calcula as interseções.

Para explicar a fórmula um pouco mais:

1. As instruções if incorporadas: o IF($A$12:$M$12=C$3 localiza as colunas apropriadas e a instrução IF($A$12:$A$25=$A4 localiza a linha apropriada.

2. As declarações if externas: IF(COLUMN($A$12:$M$25) e IF(ROW($A$12:$M$25) retornam quais células atendem aos dois critérios