Ссылка на пустую ячейку - должна действовать как пустая ячейка

Question

У меня есть таблица, которая ссылается на пустую ячейку на другой вкладке. Я использую iif(isblank(thatcell), "", thatcell). Это прекрасно работает, пока я не использую эту ячейку для расчета. Когда я включаю эту ячейку для вычисления стандартного отклонения (или даже countta ()), она обрабатывает пустую ячейку как ноль. Интересно, есть ли другой способ обойти это? Будет лучше, если Excel сможет создать функцию blankcell () вместо "". Кто-нибудь может мне помочь?

Answer 1

Пробелы - это нули для любой формулы, если это не исключает пробелы. Лучше всего будет использовать пользовательскую функцию в VBA для вашего проекта. Что-то вроде:

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

---

Одним из обходных путей является исключение нулей в вашей формуле, что-то вроде =SUM(if(A1:A10 <> 0,A1:A10)) введенного в качестве формулы массива.

Формулы массива вводятся, набирая его, а затем нажимая клавишу Ctrl Shift, введите.

Спорная часть: Но если вам нужно включить нули, вам нужно сделать более сложным, например =SUM(IF(Not(Isblank(... =sumif(not(isblank(... как формула массива).

Answer 2

Альтернативный обходной путь, который не включает в себя написание пользовательских функций, заключается в использовании функции AGGREGATE для вычисления стандартного отклонения. Преимущество этой функции в том, что, в отличие от SUM или STDEV , она может игнорировать ошибки.

Чтобы это работало, вам нужно, чтобы ваша первая ячейка возвращала ошибку вместо пустой текстовой строки. В первой ячейке введите

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

Функция NA просто возвращает ошибку # N/A.

Затем вместо STDEV для расчета стандартного отклонения используйте

=AGGREGATE(7,6,Range)

Первая опция в функции AGGREGATE , в этом случае 7 говорит ей выполнить вычисление STDEV.S (чтобы использовать STDEV.P, используйте 8). Второй вариант, в данном случае 6 , говорит функции игнорировать ошибки. Поскольку вы указали, что IF возвращает ошибку # N/A вместо пустой ячейки, эта ячейка будет игнорироваться.

Агрегатная функция

Функция NA