Блог

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

 =AVERAGEIFS(Hoofdtab!AE2:AE19883;
    Hoofdtab!V2:V19883;"="amp;'Statistics Query'!F4;
    Hoofdtab!V2:V19883;"="amp;'Statistics Query'!F5;
    Hoofdtab!V2:V19883;"="amp;'Statistics Query'!F6;
    Hoofdtab!V2:V19883;"="amp;'Statistics Query'!F7;
    Hoofdtab!I2:I19883;">="amp;F8;
    Hoofdtab!I2:I19883;"<="amp;F9;
    Hoofdtab!X2:X19883;"="amp;F10)
 

Следующий снимок экрана должен внести некоторую ясность:

В справочном списке много записей «Продукт». Я хочу сделать так, чтобы я мог фильтровать по двум / трем / четырем (ячейка F5 / F6 / F7) продуктам одновременно. В настоящее время я использую звездочку как знак «все имеет значение». В примере я использую только один продукт, и это работает. Однако, как только я добавляю второй продукт в ячейку F5, я получаю #DIV / 0! ошибка. Кто-нибудь знает, как я могу решить эту проблему?

Я использую Excel 2019

С уважением,

VHes

Чтобы решить вашу проблему, вам необходимо вернуть массив, из которого вы можете возвращать соответствующие значения. AVERAGEIFS требуется, чтобы все условия были истинными. Но вы, похоже, хотите, чтобы одно ИЛИ любое из нескольких условий было истинным в сочетании с другой группой условий, где два из них всегда должны быть истинными.

Вот пример, который вы можете использовать для применения принципов к вашей реальной проблеме.

Учитывая следующие данные:

Предположим

• мы хотим выбрать любой из списка, который может содержать a, b, and/or c
• и мы хотим включить диапазон дат.
• мы хотим усреднить соответствующие значения в value столбце
• мы называем
  • столбец букв critRng1
  • столбец дат critRng2
  • столбец значений avgRng

У нас есть диапазон, в который мы включаем наши значения crit ( crit1 у нас есть диапазон с начальной датой диапазона и конечной датой ( DATE1 и DATE2 )

Для этого упражнения мы предположим, что

• критерий1 (обратите внимание на пустую ячейку)

и наши даты:

Формула: =1/(critRng1=TRANSPOSE(crit1))*1/(critRng2>=DATE1)*1/(critRng2<=DATE2)*avgRng

вернет массив значений

Обратите внимание, что желаемые значения, которые мы хотим усреднить, присутствуют вместе с кучей #DIV/0! ошибок.

Поскольку AVERAGE функция будет игнорировать текст, мы можем использовать IFERROR и AVERAGE для получения ответа:

 =AVERAGE(IFERROR(1/(critRng1=TRANSPOSE(crit1))*1/(critRng2>=DATE1)*1/(critRng2<=DATE2)*avgRng,""))
 

или немного короче:

 =AVERAGE(IFERROR(1/((critRng1=TRANSPOSE(crit1))*(critRng2>=DATE1)*(critRng2<=DATE2))*avgRng,""))
 

Для вышеуказанных условий формула вернет 4 (среднее значение 5, 4 and 3 )