Microsoft Excel - это мощное и универсальное приложение для работы с электронными таблицами, которое отлично подходит для отслеживания и управления всем, начиная от инвентаря предприятия, заканчивая бюджетами для малого бизнеса и личной пригодностью. Одним из преимуществ Excel является то, что вы можете заранее настроить формулы, которые будут автоматически обновляться при вводе новых данных. К сожалению, некоторые формулы математически невозможны без необходимых данных, что приводит к ошибкам в вашей таблице, таким как # DIV / 0 !, #VALUE !, #REF !, и #NAME ?. Хотя эти ошибки не обязательно вредны, они будут отображаться в вашей электронной таблице до тех пор, пока не будут исправлены или пока не будут введены необходимые данные, что может сделать общую таблицу менее привлекательной и более трудной для понимания. К счастью, по крайней мере, в случае отсутствия данных, вы можете скрыть ошибки Excel с некоторой помощью от функций IF и ISERROR. Вот как это сделать.
Мы используем небольшую электронную таблицу отслеживания потери веса в качестве примера таблицы, которая может привести к ошибке вычисления (вычисление процента потери веса) при ожидании новых данных (последующие взвешивания).
В нашем примере электронная таблица ожидает ввода в столбце « Вес», а затем автоматически обновляет все остальные столбцы на основе новых данных. Проблема заключается в том, что в столбце Процент потерянных используется значение Изменить, которое не обновлялось в течение недель, в которые вес еще не был введен, что приводит к # DIV / 0! ошибка, которая указывает, что формула пытается разделить на ноль. Мы можем решить эту ошибку тремя способами:
- Мы можем удалить формулу из недель, в которые не был введен вес, а затем вручную добавить ее обратно в каждую неделю. Это будет работать в нашем примере, потому что электронная таблица относительно мала, но не будет идеальной для больших и более сложных электронных таблиц.
- Мы можем рассчитать процент потери, используя другую формулу, которая не делится на ноль. Опять же, это возможно в нашем примере, но не всегда зависит от электронной таблицы и набора данных.
- Мы можем использовать функцию ISERROR, которая в сочетании с оператором IF позволяет нам определить альтернативное значение или вычисление, если первоначальный результат возвращает ошибку. Это решение мы покажем вам сегодня.
Функция ISERROR
ISERROR сам по себе проверяет обозначенную ячейку или формулу и возвращает «true», если результатом вычисления или значением ячейки является ошибка, и «false», если это не так. Вы можете использовать ISERROR, просто введя расчет или ячейку в скобках после функции. Например:
ЕОШИБКА ((B5-B4) / С5)
Если вычисление (B5-B4) / C5 возвращает ошибку, то ISERROR вернет «true» в сочетании с условной формулой. Хотя это можно использовать по-разному, его, пожалуй, наиболее полезная роль заключается в сочетании с функцией IF.
Функция ЕСЛИ
Функция IF используется путем помещения трех тестов или значений в скобки, разделенных запятыми: IF (значение, которое будет проверено, значение, если true, значение, если false). Например:
IF (B5> 100, 0, B5)
В приведенном выше примере, если значение в ячейке B5 больше 100 (что означает, что тест верен), то в качестве значения ячейки будет показан ноль. Но если B5 меньше или равно 100 (что означает, что тест ложен), будет показано фактическое значение B5.
ЕСЛИ И ISERROR объединены
Мы комбинируем функции IF и ISERROR, используя ISERROR в качестве теста для оператора IF. Давайте обратимся к нашей таблице потери веса в качестве примера. Причина, по которой ячейка E6 возвращает # DIV / 0! ошибка в том, что его формула пытается разделить общий вес, потерянный на вес предыдущей недели, который еще не доступен для всех недель и который фактически действует как попытка делить на ноль.
Но если мы используем комбинацию IF и ISERROR, мы можем сказать Excel игнорировать ошибки и просто ввести 0% (или любое желаемое значение), или просто завершить вычисление, если ошибок нет. В нашем примере это можно сделать с помощью следующей формулы:
ЕСЛИ (ЕОШИБКА (Д6 / В5), 0, (D6 / D5))
С помощью этой функции вы можете скопировать ее во все оставшиеся ячейки, и любые ошибки будут заменены нулями. Однако при вводе новых данных в будущем затронутые ячейки автоматически обновятся до правильных значений, поскольку условие ошибки больше не будет истинным.
Помните, что при попытке скрыть ошибки Excel вы можете использовать практически любое значение или формулу для всех трех переменных в выражении IF; это не должно быть ноль или целое число, как в нашем примере. Альтернативные варианты включают ссылку на совершенно отдельную формулу или вставку пробела с использованием двух кавычек («») в качестве «истинного» значения. Для иллюстрации, следующая формула будет отображать пробел в случае ошибки вместо нуля:
ЕСЛИ (ЕОШИБКА (Д6 / В5), "", (D6 / D5))
Просто помните, что оператор IF может быстро стать длинным и сложным, особенно в паре с ISERROR, и в таких ситуациях легко не ставить круглые скобки или запятую. Последние версии формул цветового кода Excel при их вводе помогают отслеживать значения ячеек и скобки.
