Referenciando uma célula em branco - deve agir como uma célula em branco

Question

Eu tenho uma planilha que faz referência a uma célula em branco em outra guia. Eu uso o iif (isblank (thatcell), "", thatcell). Isso funciona bem, até eu usar essa célula para calcular. Quando eu incluir essa célula para calcular o desvio padrão (ou mesmo counta (), ele trata a célula em branco como zero. Gostaria de saber se há outra maneira de contornar isso. Será melhor se o Excel puder criar uma função blankcell () no lugar de "" Alguém pode me ajudar?

    

Answer 1

Os espaços em branco são zeros para qualquer fórmula, a menos que exclua especificamente os espaços em branco. Sua melhor aposta seria usar uma função definida pelo usuário dentro do VBA para o seu projeto. Algo como:

Sub SumNonBlanks()
 For each c in range("A:A")
  If c.value <> "" then
  'Enter them into an array
  End if
 Next
'Do stuff to array
End Sub

Uma solução alternativa é excluir zeros em sua fórmula, algo semelhante a =SUM(if(A1:A10 <> 0,A1:A10)) inserido como uma fórmula de matriz.

As fórmulas das matrizes são digitadas digitando-as e pressionando ctrl deslocamento enter .

A parte disputada : mas se você precisar incluir zeros, precisará ser mais complexo, como =SUM(IF(Not(Isblank(... =sumif(not(isblank(... como uma fórmula de matriz.

    

Answer 2

Uma solução alternativa, que não envolve a gravação de funções personalizadas, seria usar a função AGGREGATE para calcular o desvio padrão. A vantagem dessa função é que, ao contrário de SUM ou STDEV , ela pode ignorar erros.

Para que isso funcione, você precisa fazer com que sua primeira célula retorne um erro em vez de uma cadeia de texto nula. Na sua primeira célula, digite

=IF(ISBLANK(ThatCell),NA(),H23)

A função NA simplesmente retorna um erro # N / A.

Em seguida, em vez de usar STDEV para calcular o desvio padrão, use

=AGGREGATE(7,6,Range)

A primeira opção na função AGGREGATE , neste caso, 7 diz para executar um cálculo STDEV.S (para usar STDEV.P, use 8 ). A segunda opção, neste caso 6 , informa a função para ignorar erros. Como você disse a IF para retornar um erro # N / A no lugar de uma célula em branco, essa célula será ignorada.

função AGREGADA

Função NA