Как применить формулы к строкам промежуточных итогов?

Question

У меня есть электронная таблица с множеством промежуточных итогов и переменным числом строк между ними. Если я показываю только строки промежуточных итогов, добавляю формулу и перетаскиваю ее вниз, Excel добавляет формулу ко всем строкам, а не только к строкам промежуточных итогов:

до промежуточного итога:

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

после того, как промежуточный итог сжат:

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

добавлено среднее к промежуточным итогам:

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

перетащил формулу и без сжатия

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   

Пожалуйста, не концентрируйтесь на этом примере и на том, как это можно сделать по-другому. Фактический лист очень сложен, и нам нужно использовать промежуточные итоги в формулах.

Мне нужно применять формулы только для промежуточных итогов. Как я могу это сделать?

Answer 1

Подытог строки , как правило , идентифицируется (например , со Average или Count - или наиболее часто и , как в вашем случае с Total Фильтр "При каждом изменении в столбце" содержит "Tot" для доступа только к строкам промежуточных итогов.

Обратите внимание, что при фильтрации результаты могут выглядеть некорректно, но должны корректироваться при удалении фильтра.

Answer 2

Мне пришлось сделать аналогичную вещь для матрицы взвешенных решений с категориями, которые были индивидуально взвешены. Электронная таблица выглядит примерно так:

Где "#NAME?"Появляется несколько формул, которые я опишу. Электронная таблица основана на макросах - после их включения "#NAME?""вещь уходит, и появляется правильный номер.

Каждая категория имеет несколько критериев с весами. Под критериями вычисляются итоги по категориям, но в строке слева от итогов находится ячейка со словом "Категория". Оценки категории используют эту формулу:

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

Приведенная выше формула была бы правильной, если бы она была введена в ячейку D84, а вес категории был в $ C84.

Окончательные оценки используют эту формулу:

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

где «$ B4:$ B98» - это диапазон, в котором содержится слово "Категория", "1" - это количество столбцов справа от столбца "Категория", в которых отображаются веса, а "2" - количество столбцов в справа от столбца "Категория" отображаются оценки (т. е. это должен быть столбец, в котором находится формула).

В приведенных выше формулах используются две функции range_up и sum_categories, представленные ниже:

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

Наконец, если вы хотите пересчитать вручную, включите в форму кнопку, которая вызывает эту функцию:

Sub force_recalc()
  Application.CalculateFullRebuild
End Sub

Answer 3

Проблема только в презентации? Если это так, то вы можете использовать простое условное выражение, чтобы скрыть нежелательный вывод. =IF(RIGHT(B10,5)="Total",INDEX(Summary,A10),"") (замените INDEX той формулой, которую вы фактически используете)

Answer 4

Если вы свернете все строки, которые не хотите перезаписывать, вы можете вставить формулу, выполнив следующие действия:

1. Скопируйте формулу
2. Выделите область, к которой вы хотите применить формулу
3. Перейти к "Перейти к специальным" (в правом верхнем углу домашней ленты, или CtrlSG, затем Alt+S)
4. Выберите только видимые ячейки, нажмите ОК
5. Вставить формулу

Надеюсь это поможет