ЕСЛИ и ISBLANK в массиве

Question

Я нуб, когда дело доходит до продвинутого Excel. В настоящее время я использую эту формулу, которую я нашел в Интернете и изменил в своей таблице:

=IF(ROWS(G$7:G7)>$D$2,"",
    INDEX(Test!G$2:G$999999,
          SMALL(IF((Test!$A$2:$A$999999>=$B$2)*(Test!$A$2:$A$999999<=$B$3),
          ROW(Test!$A$2:$A$999999)-ROW(Test!$A$2)+1),ROWS(G$7:G7))))

По сути, я извлекаю информацию из другого листа, соответствующего определенному диапазону дат. Я оставил диапазон ячеек как row999999, поэтому мне не нужно обновлять формулу при вводе новых данных за месяцы / годы.

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

Я попытался поместить IF или ISBLANK впереди, но это заставляет все ячейки (даже те, у которых есть данные) очищаться.

Answer 1

Вы должны быть в состоянии поместить IF(ISBLANK(...)) вокруг вашей функции INDEX . Что-то вроде этого:

=IF(ROWS(G$7:G7)>$D$2,"",
    IF(ISBLANK(INDEX(Test!G$2:G$999999,
                     SMALL(IF((Test!$A$2:$A$999999>=$B$2)*(Test!$A$2:$A$999999<=$B$3),
                     ROW(Test!$A$2:$A$999999)-ROW(Test!$A$2)+1),ROWS(G$7:G7)))),"",
       INDEX(Test!G$2:G$999999,
             SMALL(IF((Test!$A$2:$A$999999>=$B$2)*(Test!$A$2:$A$999999<=$B$3),
             ROW(Test!$A$2:$A$999999)-ROW(Test!$A$2)+1),ROWS(G$7:G7)))))

Answer 2

« Я оставил диапазон ячеек как row999999, так что мне не нужно обновлять формулу при вводе новых данных за месяцы / годы », боюсь, это ужасная идея.

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

В отличии от COUNTIF SUMIF COUNTIFS или SUMIFS например, формулы массива вычислить по всем ячейкам , переданных им, технически ли за последние используемые клетки в этих диапазонах или нет.

Так, если, например, у вас есть данные, простирающиеся только до строки 1000, то, ссылаясь на 999 999 строк, вы фактически заставляете Excel вычислять почти на миллион больше ячеек, чем фактически необходимо, что приводит к поразительно ресурсоемкой формуле. И это только для одного экземпляра этой формулы.

С некоторыми функциями вы можете уйти с привязкой целых столбцов без ущерба для производительности, хотя и не с функциями , которые работают над массивами, такими как AGGREGATE SUMPRODUCT и любое строительство требует CSE CTRL+SHIFT+ENTER

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

С уважением