Progress-servis55.ru

Новости из мира ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Енд в excel

Обходим ошибки с помощью функций Excel

Здравствуйте, друзья. В предыдущей статье я рассказывал, как найти ошибку в расчетах и исправить ее. А вот что делать, если появление ошибки допускается? Например, функция поиска ВПР возвращает «#Н/Д», если в таблицу еще не внесено нужное значение. Или у Вас возникает деление на ноль, которое не является синтаксической ошибкой, просто именно сейчас мы имеем такие исходные данные. Как же сделать так, чтобы «ошибка» в одной формуле не тянула за собой целый ряд ошибок в зависимых формулах? Очень просто, с помощью функций Эксель!

В программе есть несколько функций для разных типов ошибок:

  1. ЕНД(значение) – проверяет аргумент «значение» на ошибку #Н/Д. Если ошибка – возвращает «ИСТИНА», нет ошибки «ЛОЖЬ». Я использую такую функцию в комбинации с функциями поиска и другими логическими функциями. Например, так: ЕСЛИ(ЕНД(А1);0;А1) . В итоге, если в ячейке А1 ошибка #Н/Д – функция вернет ноль, в противном случае – значение в ячейке А1.
  2. ЕОШ(значение) – проверяет значение на ошибки, кроме #Н/Д. То есть, она вернет «ИСТИНА», если в ячейке ошибки: «#ЗНАЧ!», «#ССЫЛКА!», «#ДЕЛ/0!», «#ЧИСЛО!», «#ИМЯ?» , «#ПУСТО!».
  3. ЕОШИБКА(значение) – проверка на наличие любой из ошибок, перечисленных в пунктах 1-2. Аналогично, вернет «ИСТИНА», когда есть ошибка, и «ЛОЖЬ», когда ее нет.
  4. ЕСНД(значение; значение если #Н/Д) – похожа на функцию из пункта 1, но при обнаружении ошибки возвращает не «ИСТИНА», а значение аргумента «значение если #Н/Д». Очень удобная функция для таблиц, заполняемых в реальном времени, для которых применяется консолидация.
  5. ЕСЛИОШИБКА(значение; значение если ошибка) – функция похожа на предыдущую, только ищет все виды ошибок. При нахождении возвращает значение аргумента «значение если ошибка».

Иногда нужно предусмотреть реакцию формулы на любой из видов ошибок, какой-то определенный. Для этого можно использовать функцию =ТИП.ОШИБКИ(значение ошибки) . Функция вернет код, соответствующий ошибке:

ОшибкаКод
#ПУСТО!1
#ДЕЛ/0!2
#ЗНАЧ!3
#ССЫЛКА!4
#ИМЯ?5
#ЧИСЛО!6
#Н/Д7
#ОЖИДАНИЕ_ДАННЫХ8
Другая ошибка, или ошибки нет#Н/Д

Например, запишем такую формулу: =ЕСЛИ(ТИП.ОШИБКИ(А1)=4;«Внешняя ссылка нарушена»;А1) . Если в ячейке А1 будет ошибка «#ССЫЛКА!», формула выведет надпись: ;«Внешняя ссылка нарушена». Иначе – выведет значение ячейки А1.

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

Если же не справились – задавайте свои вопросы в комментариях к этому посту!

Следующая статья будет посвящена сводным таблицам. Вот, где Вы научитесь экономить свое время!

Жду Вас на страницах своего блога и с радостью отвечу на Ваши вопросы.

Функции проверки типа

На этой страничке описаны девять функций рабочего листа, которые используются для проверки типа значения или ссылки.

Каждая из этих функций проверяет тип значения и возвращает значение ИСТИНА или ЛОЖЬ в зависимости от типа. Например, функция ЕПУСТО возвращает логическое значение ИСТИНА, если проверяемое значение является ссылкой на пустую ячейку; в противном случае возвращается логическое значение ЛОЖЬ.

Читать еще:  Формула подсчета ячеек в excel

ЕПУСТО(значение)
ЕОШ(значение)
ЕОШИБКА(значение)
ЕЛОГИЧ(значение)
ЕНД(значение)
ЕНЕТЕКСТ(значение)
ЕЧИСЛО(значение)
ЕССЫЛКА(значение)
ЕТЕКСТ(значение)

Значение — это проверяемое значение. Значение может быть пустой ячейкой, значением ошибки, логическим значением, текстом, числом, ссылкой или именем объекта любого из перечисленных типов.

ФункцияВозвращает ИСТИНА, если
ЕЛОГИЧЗначение ссылается на логическое значение.
ЕНДЗначение ссылается на значение ошибки #Н/Д (значение недоступно).
ЕНЕТЕКСТЗначение ссылается на любой элемент, который не является текстом. (Заметим, что функция возвращает ИСТИНА, если значение ссылается на пустую ячейку).
ЕОШЗначение ссылается на любое значение ошибки, кроме #Н/Д.
ЕОШИБКАЗначение ссылается на любое значение ошибки (#Н/Д, #ЗНАЧ! , #ССЫЛ! , #ДЕЛ/0! , #ЧИСЛО! , #ИМЯ? или #ПУСТО!).
ЕПУСТОЗначение ссылается на пустую ячейку.
ЕССЫЛКАЗначение ссылается на ссылку.
ЕТЕКСТЗначение ссылается на текст.
ЕЧИСЛОЗначение ссылается на число.

Аргумент этих функций не преобразуется. Для большинства других функций, например, если в качестве аргумента требуется число, то текстовое значение «19» преобразуется в число 19. Однако в формуле ЕЧИСЛО(«19») «19» не преобразуется из текста в число, и функция ЕЧИСЛО возвращает значение ЛОЖЬ.

Эти функции полезны в формулах и макросах для проверки результатов вычислений. Комбинируя эти функции с функцией ЕСЛИ, можно локализовать ошибки в формулах (см. следующие примеры).

ФормулаОписание (результат)
=ЕЛОГИЧ(ИСТИНА)Проверяет, является ли значение ИСТИНА логическим значением (ИСТИНА)
=ЕЛОГИЧ(«ИСТИНА»)Проверяет, является ли значение «ИСТИНА» логическим значением (ЛОЖЬ)
=ЕЧИСЛО(4)Проверяет, является ли значение 4 числом (ИСТИНА)
ABC
1ДанныеФормулаОписание (результат)
2Золото=ЕПУСТО(A2)Проверяет, является ли ячейка A2 пустой (ЛОЖЬ)
3Регион1=ЕОШИБКА(A4)Проверяет, является ли #ССЫЛ! сообщением об ошибке (ИСТИНА)
4#ССЫЛ!=ЕНД(A4)Проверяет, является ли #ССЫЛ! сообщением об ошибке #Н/Д (ЛОЖЬ)
5330,92=ЕНД(A6)Проверяет является ли #Н/Д сообщением об ошибке #Н/Д (ИСТИНА)
6#Н/Д=ЕОШ(A6)Проверяет, является ли #Н/Д сообщением об ошибке (ЛОЖЬ)
7=ЕЧИСЛО(A5)Проверяет, является ли значение 330,92 числом (ИСТИНА)
8=ЕТЕКСТ(A3)Проверяет, является ли значение «Регион1» текстом (ИСТИНА)

Наиболее часто мне приходится применять функцию ЕНД() в комбинации с функцией ЕСЛИ() в случае если функция ВПР() выдает ошибку типа #Н/Д. Пример такого применения можно посмотреть здесь.

Е (функции Е)

Описание

Каждая из функций Епроверяет указанное значение и возвращает в зависимости от результата значение ИСТИНА или ЛОЖЬ. Например, функция ЕПУСТО возвращает логическое значение ИСТИНА, если проверяемое значение является ссылкой на пустую ячейку; в противном случае возвращается логическое значение ЛОЖЬ.

Функции Е используются для получения сведений о значении перед выполнением с ним вычисления или другого действия. Например, для выполнения другого действия при возникновении ошибки можно использовать функцию ЕОШИБКА в сочетании с функцией ЕСЛИ:

= ЕСЛИ( ЕОШИБКА(A1); «Произошла ошибка.»; A1 * 2)

Эта формула проверяет наличие ошибки в ячейке A1. При возникновении ошибки функция ЕСЛИ возвращает сообщение «Произошла ошибка.» Если ошибки отсутствуют, функция ЕСЛИ вычисляет произведение A1*2.

Синтаксис

Аргумент функции Е описаны ниже.

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

Читать еще:  Excel пользовательский формат даты

Возвращает значение ИСТИНА, если

Аргумент «значение» ссылается на пустую ячейку

Аргумент «значение» ссылается на любое значение ошибки, кроме #Н/Д

Аргумент «значение» ссылается на любое значение ошибки (#Н/Д, #ЗНАЧ!, #ССЫЛ!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО!)

Аргумент «значение» ссылается на логическое значение

Аргумент «значение» ссылается на значение ошибки #Н/Д (значение недоступно)

Аргумент «значение» ссылается на любой элемент, который не является текстом. (Обратите внимание, что функция возвращает значение ИСТИНА, если аргумент ссылается на пустую ячейку.)

Аргумент «значение» ссылается на число

Аргумент «значение» ссылается на ссылку

Аргумент «значение» ссылается на текст

Замечания

Аргументы в функциях Е не преобразуются. Любые числа, заключенные в кавычки, воспринимаются как текст. Например, в большинстве других функций, требующих числового аргумента, текстовое значение «19» преобразуется в число 19. Однако в формуле ЕЧИСЛО( «19») это значение не преобразуется из текста в число, и функция ЕЧИСЛО возвращает значение ЛОЖЬ.

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

Примеры

Пример 1

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Проверяет, является ли значение ИСТИНА логическим

Проверяет, является ли значение «ИСТИНА» логическим

Проверяет, является ли значение 4 числом

Проверяет, является ли значение G8 допустимой ссылкой

Проверяет, является ли значение XYZ1 допустимой ссылкой

Скопируйте образец данных из приведенной ниже таблицы и вставьте его в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Проверяет, является ли ячейка C2 пустой

Проверяет, является ли значение в ячейке A4 (#ССЫЛ!) значением ошибки

Проверяет, является ли значение в ячейке A4 (#ССЫЛ!) значением ошибки #Н/Д

Проверяет, является ли значение в ячейке A6 (#Н/Д) значением ошибки #Н/Д

Проверяет, является ли значение в ячейке A6 (#Н/Д) значением ошибки

Проверяет, является ли значение в ячейке A5 (330,92) числом

Проверяет, является ли значение в ячейке A3 («Регион1») текстом

Функция ЕНД как скрыть ошибку #Н/Д для ВПР и ПРОСМОТР в Excel

Функция ЕНД в Excel используется для проверки ячеек или выражений, передаваемых в качестве аргумента, и возвращает логическое значение ИСТИНА. Например, если ячейка содержит код ошибки #Н/Д или результатом вычисления выражения, переданного в качестве аргумента, является код ошибки #Н/Д. В противном случае результатом выполнения данной функции является логическое ЛОЖЬ.

Суммирование количества ошибок в ячейках Excel

Примеры использования функции ЕНД в Excel. Данная функция принадлежит к категории «Проверка свойств и значений» – функций Excel (нелогические функции для проверки условий). Она удобна при проведении сложных расчетов с ветвлением логики. Например, при отсутствии ошибки будет выполнено действие_1, в противном случае – действие_2.

Читать еще:  Использование счетесли в excel

Пример 1. В таблице содержатся данные о товарах и их количестве. Данные были получены из СУБД, если количество единиц товаров равно нулю, в таблице Excel данная информация отобразилась в виде кода ошибки #Н/Д. Определить число наименований товаров, которых нет в наличии.

Вид таблицы данных:

Для расчета используем следующую запись (формула массива CTRL+SHIFT+Enter):

Функция ЕНД принимает сразу диапазон ячеек B3:B13 в качестве аргумента, поскольку используется формула массива. Двойное отрицание «—» необходимо для явного преобразования логических значений к числовым данным (ИСТИНА – 1, ЛОЖЬ – 0). Функция СУММ суммирует элементы полученного массива из нулей и единиц. В результате получаем:

В результате мы получили число равно количеству ошибок #Н/Д в столбце B.

Как получить первое значение ячейки вместо ошибки Н/Д в Excel

Пример 2. В таблице содержится диапазон ячеек со случайными числами, отсортированными в порядке возрастания. Найти ближайшее число из данного диапазона заданному с помощью функции ПРОСМОТР. Известно, если искомое число меньше первого значения в диапазоне, будет выведен код ошибки #Н/Д. Обработать данную ситуацию так, чтобы вместо кода ошибки выводился первый элемент массива.

Вид таблицы данных:

Для поиска числа 1 используем следующую запись:

Функция ЕНД анализирует результат выполнения функции ПРОСМОТР. Если в качестве первого аргумента ПРОСМОТР передано числовое значение, которое меньше значения первого элемента просматриваемого диапазона, будет сгенерирован код ошибки #Н/Д и будет выполнено выражение, переданное в качестве аргумента значение_если_истина функции ЕСЛИ. В противном случае (искомое число находится в диапазоне массива или превышает значение его последнего элемента), выполнится выражение, переданное в качестве аргумента значение_если_ложь .

Как видно, ошибка #Н/Д не выводится, а вместо нее первое значение ячейки просматриваемого столбца.

Описание синтаксиса и параметров функции ЕНД в Excel

Функция ЕНД имеет следующую синтаксическую запись:

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

  1. Преобразование типов данных для значений, переданных в качестве аргумента функции ЕНД, не выполняется. Например, число «99» указанное в кавычках, будет рассматриваться как текстовые данные. Если в качестве аргумента была передана строка «#Н/Д», ЕНД вернет значение ЛОЖЬ. Если ячейка содержит код ошибки #Н/Д, функция ЕНД, получившая ссылку на данную ячейку в качестве аргумента, вернет – ИСТИНА.
  2. Данная функция обычно используется в комбинации с ЕСЛИ и другими функциями для проверки выражения для своевременной проверки результатов вычислений и перехвата возможной ошибки.
  3. Код ошибки #Н/Д генерируют функции в случаях, когда в формулах используются недопустимые значения. Распространенные примеры:
  • при использовании функций для поиска данных (ПОИСКОП, ВПР и других), если в качестве аргумента «искомое_значение» было введено несуществующее;
  • при использовании формул массивов, если длина массива результатов превышает длину исходных массивов;
  • если при использовании функции не были указаны один либо нескольких аргументов, обязательных для заполнения.
Ссылка на основную публикацию
Adblock
detector