У меня есть довольно простой лист Excel 2007 с тремя столбцами; имя (столбец A), значение1 (столбец B) и значение2 (столбец C). Этот лист часто автоматически фильтруется по столбцу «Имя».
Внизу 2-го столбца я хочу выполнить условный ПРОМЕЖУТОЧНЫЙ ИТОГ столбца B с учетом любых фильтров со следующим правилом: если для данной строки столбец B равен 0, используйте значение для этой строки из столбца C, в противном случае столбец C следует игнорировать.
A B C
1 Name Value1 Value2
2 Bob 100 6 <-- use 100, ignore 6 in Col C because B is non-zero
3 Bob 200 <-- use 200
4 Bob 0 50 <-- Col B=0, use C, thus 50 (and so on)
5 Bob 300
6 Ralph 10
7 Ralph 20 1 <-- use 20 from col B, ignore col 6.
8 Ralph 0 60
9 Ralph 50
10 Mary 1000
11 Mary 1200
12 Mary 0 250
13 Mary 1040
14 Subtotal 4280
Теперь я могу получить общее значение, которое хочу, с помощью формулы:
= СУММПРОИЗВ (- (B2: B13 = 0), C2: C13) + ПРОМЕЖУТОЧНЫЙ ИТОГ (9; B2: B13)
Но использование SUMPRODUCT не позволяет ему соблюдать требование скрытых / отфильтрованных ячеек, например, фильтрация Мэри не приводит к уменьшению итога на 3690. И SUBTOTAL не может использовать ссылку на массив (B2: B13 = 0). Так что значительная часть моего мозга пытается сказать мне, что я могу подойти близко, но на самом деле не могу этого сделать. Но я (тоже?) Упрям, чтобы так быстро сдаться :)
Часть меня пытается сказать, что это невозможно сделать напрямую, не углубляясь в VBA, чего я бы предпочел избежать, если это возможно для этого решения. Я подумал, что стоит привлечь внимание других людей к проблеме, чтобы указать на решение (вероятно, очень простое), которого я просто не вижу.
РЕДАКТИРОВАТЬ 1: Одно очевидное решение, на которое я должен был указать, - это просто добавить четвертый столбец с простым выражением IF, а затем SUBTOTAL. Это, безусловно, работает, но я не могу адаптировать это к данной ситуации. Приведенная здесь таблица является всего лишь репрезентативным отрывком из гораздо большего, хорошо структурированного рабочего листа, поэтому добавление произвольного столбца не является вариантом.
РЕДАКТИРОВАТЬ 2: предоставленные мной образцы данных позволили сделать простой вывод, что прямой ПРОМЕЖУТОЧНЫЙ ИТОГ всех столбцов решит проблему, но реальные данные, из которых были извлечены эти данные, могут включать значения для столбца C. даже если столбец B не равен нулю. В этом случае столбец C должен быть проигнорирован - столбец B, если он есть, всегда имеет приоритет. Я соответствующим образом изменил исходную таблицу данных.
=IF(B2=0,C2,B2)
и вместо этого вычислить промежуточный итог? - person Peter Albert   schedule 28.01.2013