Многоколоночный оператор if - Excel

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

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

=AVERAGE(IF($C$3:$C$10={"LA","SF","POR","SEA"},$D$3:$D$10))

Затем нажмите Ctrl + Shift + Enter.

Из-за того, как организованы мои данные, эта предыдущая формула — единственный способ определить (отчасти вручную), расположен ли город на восточном или западном побережье.

Теперь мне не хватает части, которая гласила бы: "А также, если в предыдущем столбце написано "север""

введите здесь описание изображения


if-statement excel average array-formulas
person gmorissette    schedule 15.12.2014    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Обновление с помощью функции массива

Первая попытка:

Итак, сначала я подумал, что вы можете просто добавить второе условие к функции массива следующим образом:

=AVERAGE(IF(AND($C$2:$C$9={"LA","SF","POR","SEA"},$B$2:$B$9={"North"}),$D$2:$D$9))

Там, где AND требовалось бы, чтобы каждый элемент был правдой, но это, похоже, не сработало... и функции массива - хитрые звери для отладки.

Вторая попытка:

Вместо этого вы можете расширить функцию IF вторым IF. Таким образом, в случае, если первое истинно, значение должно возвращать другой ЕСЛИ, который проверяет второй критерий. Только если он соответствует обоим критериям, верните оценку, иначе он все равно ничего не вернет, что не будет получено функцией среднего.

Таким образом, финальная вещь должна выглядеть так:

=AVERAGE(IF($C$2:$C$9={"LA","SF","POR","SEA"},IF($B$2:$B$9={"North"},$D$2:$D$9)))

Именованный массив

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

Просто перейдите на ленту формул> Раздел «Определенные имена»> «Диспетчер имен»> «Создать».

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

Именованное значение

Затем вы можете использовать его в формуле вместо массива следующим образом:

=AVERAGE(IF(C2:C9=WestCities,IF(B2:B9="North",D2:D9)))

Именованное использование

С сводной таблицей

Вот мое предложение:

Во-первых, добавьте столбец для Востока/Запада, чтобы не вводить данные в формулу.

Данные

Затем добавьте сводную таблицу, и она сделает все расчеты за вас.

Настройте его следующим образом (убедитесь, что агрегируете по среднему значению (а не по сумме):

Настройка сводки

И это будет выглядеть так:

Сводка

Если вы хотите сохранить исходное форматирование, вы можете просто запустить формулы из своей сводной таблицы следующим образом:

=GETPIVOTDATA("Score",Sheet4!$A$3,"E/W","West","S/N","North")

Основная формула

С западной колонной

Кроме того, если бы у вас была возможность добавить столбец для запада, вы могли бы легко проверить это с помощью СРЗНАЧЕСЛИМН с несколькими критериями, подобными этому:

=AVERAGEIFS(D2:D9,A2:A9,"West",B2:B9,"North")

Среднее значение

person KyleMit    schedule 15.12.2014