Вопрос или проблема
У меня есть таблица с двумя столбцами:
счет | сумма
--------+-------
525 | 199.12
400 | 209.23
410 | 458.78
415 | 56.10
150 | 119.60
Эти данные извлекаются из SQL-сервера с помощью Power Query. Я хочу суммировать суммы, соответствующие определенным счетам. Я подумал, что это будет достаточно просто с помощью VLOOKUP. Поэтому я написал: =SUM(VLOOKUP({"400","410","411","412","413","414","450"},Query1!A:B,2,FALSE)). Это работает отлично, кроме случаев, когда одно из значений в массиве поиска отсутствует в таблице, что происходит, если в данный период времени не было записей для этого счета. В этом случае я получаю ошибку “Значение недоступно для формулы или функции”.
Мне кажется, это простое решение, но я не могу его найти.
Я разобрался. Функция IFNA существует для этой цели. Новый код: =SUM(IFNA(VLOOKUP({"400","410","411","412","413","414","450"},Query1!A:B,2,FALSE),0))
Ответ или решение
Чтобы использовать функцию VLOOKUP для поиска нескольких значений в Excel, особенно когда некоторые значения могут отсутствовать, следует учитывать некоторые важные моменты. В описанной вами ситуации у вас есть таблица с двумя столбцами, где хранятся учетные записи и соответствующие суммы. Задача заключается в том, чтобы суммировать суммы для определенных учетных записей, используя VLOOKUP.
Изначально вы правильно решили использовать VLOOKUP для получения значений. Однако когда одно из искомых значений отсутствует в таблице, новая версия VLOOKUP выдает ошибку "#N/A", что мешает правильному вычислению суммы.
Как решить проблему с отсутствующими значениями
Функция IFNA — это отличное решение для обработки ошибок, возникающих при использовании функции VLOOKUP. Ваша окончательная формула выглядит следующим образом:
=SUM(IFNA(VLOOKUP({"400","410","411","412","413","414","450"}, Query1!A:B, 2, FALSE), 0))Давайте разберем, как она работает:
-
VLOOKUP — это функция, которая ищет значения в первом столбце указанного диапазона и возвращает соответствующие значения из указанного столбца. В вашем случае вы ищете суммы для заданных учетных записей: 400, 410, 411, 412, 413, 414 и 450.
-
IFNA — эта функция проверяет, произошла ли ошибка
#N/Aпри выполнении функции VLOOKUP. Если ошибка возникает, IFNA возвращает значение по умолчанию, в вашем случае — 0, что позволяет избежать проблем с суммированием. -
SUM — эта функция складывает все возвращаемые значения. Благодаря использованию IFNA, если VLOOKUP не может найти значение, вместо ошибки будет добавляться 0, что не повлияет на итоговую сумму.
Примечания по использованию
- Обновление данных: Убедитесь, что ваши данные из SQL-сервера обновлены, чтобы VLOOKUP мог корректно работать с актуальными значениями.
- Форматирование учетных записей: Убедитесь, что формат учетных записей в вашем массиве (например, "400", "410") совпадает с форматом учетных записей в вашем диапазоне данных. Например, если учетные записи в таблице числовые, приведите массив к числовому формату:
{400, 410, 411, 412, 413, 414, 450}. - Альтернативные функции: В более сложных случаях вы можете рассмотреть использование других функций, таких как
SUMPRODUCTили создания сводных таблиц, которые могут предложить более гибкие способы работы с данными.
Заключение
Используя функцию IFNA вместе с VLOOKUP, вы можете значительно улучшить устойчивость ваших формул в Excel и избежать неприятных ошибок при их использовании. Это позволит вам эффективно суммировать значения даже в случае отсутствия некоторых учетных записей в вашем наборе данных, сохраняя при этом целостность расчетов.