ссылка на видимую часть выделения при написании формулы в Excell

Question

Я знаю, что есть много способов создания промежуточных таблиц, которые меняются в зависимости от изменения видимости ячеек - хотя мой вопрос может звучать похоже - это НЕ мой вопрос.

Предположим, у меня есть книга с 2 столбцами и 5 строками, например

.    A      B
1    Name   Val
2    A      1
3    A      3
4    B      5
5    B      2
6    A      6
7    A      
8    B      5
9    A      2

Я пытаюсь заполнить ячейку B7 средним значением некоторых из названных строк "A" над ней ... Поэтому я фильтрую таблицу по столбцу "Имя", используя "A", то, что я хочу получить, это формула суммы (B2, B3, B6) или (еще лучше) сумма (B2:B3, B6), т. Е. Сумма ячеек, которые видны при создании этой формулы.

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

Обратите внимание, что это значение должно оставаться постоянным относительно будущих изменений в видимости строк (мне даже не нужно его менять, если значения в A2, A3, A6 изменятся с A на B - но я ожидаю, что оно изменится, если B2, B3 или В6 изменены ....)

Я также знаю, что если я выберу диапазон B2:B6 (когда все еще отфильтрован, чтобы отображать только строки с именем "A") и нажмите ALT+; (окна) это изменит выбор, чтобы быть точно B2:B3, B6, но это, кажется, не работает во время создания формулы :(

Просто чтобы уточнить - реальные данные намного больше, поэтому выбор ячеек по очереди не возможен.

Любые идеи, как получить ссылку на видимую в настоящее время часть текущего выбранного диапазона?

Answer 1

Фильтрация и ввод в такие вещи, как это кажется большой работой. Почему бы просто не выбрать легкий путь?

=IF(B2<>"","",AVERAGEIF($A$2:A2,"A",$B$2:B2))

В столбце С и скопируйте его вниз. Рядом с пустыми ячейками будет помещено среднее значение всех "А" над ним. Вы можете расширить его до большего количества условий, используя AVERAGEIFS, и вы можете изменить условие с "A" на a2, если вы хотите, чтобы оно усредняло все наблюдения над ним для этого значения (чтобы оно брало B, C и т.д.).

Я знаю, что это не отвечает на ваш точный вопрос, но ваш реальный вопрос может потребовать много времени для реализации, и это действительно быстрое решение

После этого вы можете создать еще один столбец, который объединяет значения - принимает значение в B, если оно есть, в противном случае - значение в C. Вы также можете просто изменить формулу на:

=IF(B2<>"",B2,AVERAGEIF($A$2:A2,"A",$B$2:B2))

И просто сделайте все это в одном столбце.

Answer 2

Я надеялся получить лучшее решение, так что я ждал, но так как, кажется, что здесь нет ни одного, то, что я в итоге сделал:

Я отфильтровал и выбрал клетки, которые я хотел усреднить.

т.е. в примере, представленном после фильтрации по имени "А" для столбца AI получено:

.    A      B
1    Name   Val
2    A      1
3    A      3
6    A      6
7    A      
9    A      2

Честно говоря, я не уверен, что первоначально сделал, чтобы решить эту проблему, он включал выделение нужных ячеек, а затем копирование их (заставление выделения появляться вокруг каждой из видимых ячеек), а затем использование некоторого выражения на панели VBA Watches. получить список адресов всех выбранных ячеек.

однако, если я сейчас попробую это на примере, и я использовал мышь, чтобы выбрать от B2 до B6 и копирование. эффективно выбирая B2:B3, B6 (потому что B4 и B5 там, где они невидимы из-за фильтра). Я не могу воссоздать мою однострочную :(

Тем не менее, теперь я обнаружил, что могу использовать debug.print selection.SpecialCells(xlCellTypeVisible).address который даже не требует действия копирования, так что фактически он, вероятно, лучше, чем мое первоначальное решение.

Если вы намереваетесь использовать этот метод, вы должны знать, что существуют некоторые ограничения для метода SpecialCells в тех случаях, когда базовый диапазон имеет более 8192 областей - подробности об этом и способы преодоления этого см. Здесь: https://stackoverflow. ком / а / 1375508/25412