Como aplico fórmulas a linhas de subtotal?

Question

Eu tenho uma planilha com muitos subtotais e um número variável de linhas entre eles. Se eu mostrar apenas as linhas do subtotal, adicionar uma fórmula e arrastá-la para baixo, o Excel adicionará a fórmula a todas as linhas e não apenas às linhas do subtotal: -

antes do subtotal:

prod    qty   price
1       3      55
1       4      66
2       5      77
2       6      88
2       7      99
2       8     110
2       9     121
3      10     132
3      11     143
3      12     154

depois de um sub total compactado:

prod           qty   price
1 Total          7    121
2 Total         35    495
3 Total         33    429
Grand Total     75   1045

adicionou média aos subtotais:

prod           qty    price     weighted average
1 Total          7     121      17.28571429
2 Total         35     495      14.14285714
3 Total         33     429      13
Grand Total     75    1045

arrastada para baixo da fórmula e descompactada

prod           qty    price     weighted average
1               3      55   
1               4      66   
1 Total         7     121       17.28571429
2               5      77       15.4
2               6      88       14.66666667
2               7      99       14.14285714
2               8     110       13.75
2               9     121       13.44444444
2 Total        35     495       14.14285714
3              10     132       13.2
3              11     143       13
3              12     154       12.83333333
3 Total        33     429       13
Grand Total    75    1045   

Por favor, não se concentre neste exemplo e como isso pode ser feito de forma diferente. A planilha atual é muito complexa e precisamos usar os subtotais nas fórmulas.

Eu preciso aplicar fórmulas apenas aos subtotais. Como eu faço isso?

    

Answer 1

As linhas de subtotal são geralmente identificáveis (por exemplo, com Average ou Count - ou mais frequentemente e como no seu caso com Total ). Filtrar no 'Em cada alteração na coluna' contém "Tot" para acessar apenas as linhas de subtotais.

Observe que, embora filtrados, os resultados podem não parecer corretos, mas devem ser ajustados adequadamente quando o filtro for removido.

    

Answer 2

Eu tive que fazer uma coisa parecida para uma matriz de decisão ponderada com Categorias que eram individualmente ponderadas. A planilha é algo parecido com isto:

emqueo"#NAME?" coisa aparece são várias fórmulas que eu vou descrever. A planilha depende de macros - assim que estiverem ativadas, a opção "#NAME?" coisa vai embora e o número correto aparece.

Cada categoria tem vários critérios com pesos. Abaixo dos critérios, os totais da categoria são calculados, mas na linha à esquerda dos totais está uma célula com a palavra "Categoria". As pontuações da categoria usam essa fórmula:

=ROUND(SUMPRODUCT(range_up($C83),range_up(D83))/SUM(range_up($C83)),1)

A fórmula acima estaria correta se fosse inserida na célula D84 e o peso da categoria estivesse em $ C84.

As pontuações finais usam essa fórmula:

=sum_categories($B4:$B98,1,2)

onde "$ B4: $ B98" é o intervalo que contém a palavra "Categoria", o "1" é quantas colunas à direita da coluna "Categoria" os pesos aparecem, e o "2" é quantos colunas à direita da coluna "Categoria" aparecem as pontuações (ou seja, esta deve ser a coluna na qual a fórmula está).

As fórmulas acima usam duas funções, range_up e sum_categories, fornecidas abaixo:

Function range_up(r As Range) As Range
  Dim t As Range, b As Range

  Application.Volatile

  Set b = r.Cells(1, 1) 'make sure it's only one cell

  If IsEmpty(b.Value) Then 'if cell is empty, start one cell up
    Set b = b.offset(-1)
  End If

  'end(xlup) has strange behaviour if cell above is blank, so fix it manually
  If IsEmpty(b.offset(-1)) Then
    Set t = b
  Else
    Set t = b.End(xlUp)
  End If
  Set range_up = t.Resize(b.Row - t.Row + 1)

End Function

Function sum_categories(r As Range, offset1 As Integer, offset2 As Integer) As Variant
  Dim sum As Variant
  Dim c As Range

  Application.Volatile

  sum = 0
  For Each c In r.Cells
    If c.Value = "Category" Then
      sum = sum + c.offset(0, offset1).Value * c.offset(0, offset2).Value
    End If
  Next c

  sum_categories = sum
End Function

Por fim, se você quiser recalcular manualmente, inclua um botão em seu formulário que chame essa função:

Sub force_recalc()
  Application.CalculateFullRebuild
End Sub

    

Answer 3

O problema é apenas em apresentação? Em caso afirmativo, você poderia usar uma condicional simples para ocultar a saída indesejada. =IF(RIGHT(B10,5)="Total",INDEX(Summary,A10),"") (Substitua o ÍNDICE por qualquer fórmula que você esteja realmente usando)

    

Answer 4

Se você recolher todas as linhas que não deseja substituir, cole a fórmula da seguinte forma:

1. Copie a fórmula
2. Realce a área para a qual você deseja aplicar a fórmula
3. Ir para "Ir para especial" (canto superior direito da faixa de opções da casa ou CtrlSG e depois Alt + S)
4. Selecione apenas as células visíveis, clique em ok
5. Cole a fórmula

Espero que isso ajude