IF e ISBLANK em uma matriz

Question

Eu sou um noob quando se trata de Excel avançado. No momento, estou usando essa fórmula que encontrei on-line e alterei para minha planilha:

=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))))

Basicamente, estou puxando informações de uma planilha diferente, correspondendo a um período específico. Eu deixei o intervalo de células como row999999, para que eu não precise atualizar a fórmula ao inserir novos dados ao longo dos meses / anos.

Eu gostaria de deixar as células em branco se elas estiverem sendo extraídas de uma célula em branco. No momento, elas estão chegando como zero.

Eu tentei colocar um IF ou ISBLANK na frente, mas isso faz com que todas as células (mesmo aquelas com dados) fiquem em branco.

    

Answer 1

Você deve conseguir colocar uma IF(ISBLANK(...)) em torno da sua função INDEX . Algo parecido com isto:

=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

Re " Eu deixei o intervalo de células como row999999 para que eu não precise atualizar a fórmula quando eu inserir novos dados nos meses / anos " isto é, eu tenho medo uma ideia terrível.

Eu sei que você acha que está "protegendo suas apostas" definindo uma referência superior, mas na verdade você está fazendo muito mais mal do que bem.

Ao contrário de COUNTIF , SUMIF , COUNTIFS ou SUMIFS , por exemplo, as fórmulas de matriz calculam todas as células transmitidas a elas, seja tecnicamente além das últimas células usadas nesses intervalos ou não.

Portanto, se, por exemplo, você só tiver dados estendidos até a linha 1000, então, fazendo referência a 999.999 linhas, estará forçando o Excel a calcular quase um milhão de células a mais do que é realmente necessário, resultando em uma capacidade surpreendentemente fórmula pesada. E isso é apenas para uma instância dessa fórmula.

Com algumas funções, você pode fazer referência a colunas inteiras sem prejudicar o desempenho, mas não com funções que operam em matrizes, como AGGREGATE , SUMPRODUCT e qualquer construção que exija CSE ( CTRL+SHIFT+ENTER ).

Assim, você deve escolher um limite superior adequado, embora suficiente, para a linha final que está sendo referenciada ou, melhor ainda, tornar seus intervalos dinâmicos, de modo que eles se ajustem automaticamente conforme seus dados se expandem / contratos. >

Atenciosamente