Функция ЕСЛИОШИБКА в Excel возвращает результат формулы, а если формула выдала ошибку (#Н/Д, #ДЕЛ/0!, #ЗНАЧ! и другие) — указанное вами значение: =ЕСЛИОШИБКА(A2/B2;0) вернёт 0 вместо #ДЕЛ/0!, =ЕСЛИОШИБКА(ВПР(E2;A2:B100;2;0);"нет в списке") — подпись вместо #Н/Д, а =ЕСЛИОШИБКА(ВПР(E2;A2:B100;2;0);"") — пустую ячейку. Первый аргумент — проверяемая формула, второй — что показать при ошибке. На английском функция называется IFERROR.

Что делает ЕСЛИОШИБКА?

Часто при использовании формул, если результат возвращает ошибку, нужно обрабатывать ее, а если нет - возвращать результат вычисления.

Именно эту задачу и решает функция ЕСЛИОШИБКА.

Функция проверяет входящее значение/вычисление на ошибочность, если ошибки нет, возвращает его само.

Оцениваются следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!

Эквивалентным сочетанием была бы формула на основе комбинации функции ЕСЛИ и ЕОШ.

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

ЕСЛИ(ЕОШ(вычисление);"результат-если-ошибка";вычисление)

Отличие ЕСЛИОШИБКА в том, что она производит вычисление однократно и экономит ресурсы.

Также использование функции упрощает синтаксис формул.

Синтаксис функции ЕСЛИОШИБКА

Синтаксис функции ЕСЛИОШИБКА предполагает всего два аргумента, оба - обязательные:

=ЕСЛИОШИБКА(Значение-Или-Вычисление;Значение-если-ошибка)

Пример 1: ЕСЛИОШИБКА + ВПР

Наиболее характерный пример использования - в паре с функцией ВПР при поиске данных в больших таблицах.

Вариант "если ошибка, то своё значение":

=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"ошибка")

Вариант "Если ошибка, то пусто":

=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"")

Поскольку ВПР может изрядно загрузить процессор, функция ЕСЛИОШИБКА здесь весьма кстати.

Пример 2: ЕСЛИОШИБКА + деление на ноль

Задача маркетолога - произвести оценку эффективности рекламных кампаний. Один из ключевых показателей - стоимость привлечения клиента. Рассчитывается он довольно просто - расходы по рекламным кампаниям делятся на количество приведенных ими клиентов.

Что делать, когда кампания не привела ни одного? Вычисление выдаст ошибку

#ДЕЛ/0!

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

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

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

Пример 3: ЕСЛИОШИБКА в формулах массива

Проблема функций МИН и МАКС при работе с массивами в том, что, если в массиве присутствует хотя бы одна ошибка, они также возвращают ошибку.

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

Формула для поиска позиции первого символа латиницы:

={МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1);""))}

Механика ее работы такова:

• Функция СИМВОЛ создает массив букв английского алфавита;
• Функция ПОИСК ищет позицию каждой буквы в строке;
• Если буква не найдена, функция вернет ошибку #ЗНАЧ!;
• Функция ЕСЛИОШИБКА возвращает пустую строку в таких случаях, а числа оставляет как они есть;
• Функция МИН пропускает пустые строки и возвращает минимальное число;
• Если весь массив будет состоять из пустых строк, функция МИН вернет 0.

А такая формула использует массив констант и ищет позицию первой цифры:

={МИН(ЕСЛИОШИБКА(ПОИСК({1:2:3:4:5:6:7:8:9:0};A1);""))}

Читайте подробнее в статье про формулы массива.

ЕСЛИОШИБКА или ЕСНД

ЕСЛИОШИБКА перехватывает любые ошибки, в том числе те, о которых лучше знать: опечатку в имени (#ИМЯ?) или битую ссылку (#ССЫЛКА!). Если нужно обработать только «не найдено» от ВПР и ПОИСКПОЗ, используйте ЕСНД — она реагирует только на #Н/Д.

Другие логические функции

ЕСЛИ, И, ИЛИ, НЕ

Почему ЕСЛИОШИБКА не работает — частые ошибки

• Excel пишет «В формуле обнаружена ошибка». Не хватает скобки или точки с запятой между аргументами: =ЕСЛИОШИБКА(формула;значение).
• Пустая строка "" мешает расчётам. Ячейка с "" не пустая: СЧЁТЗ её считает, а сложение с ней даёт #ЗНАЧ!. Если результат будет использоваться в вычислениях, возвращайте 0.
• Ошибка спрятана, а данные неверные. ЕСЛИОШИБКА скрывает и опечатки в формуле. При отладке временно уберите её или используйте ЕСНД.
• Формула медленная. ЕСЛИОШИБКА не ускоряет ВПР сама по себе — на больших таблицах сократите диапазоны поиска.

ЕСЛИОШИБКА — частые вопросы

Что значит ЕСЛИОШИБКА в Excel?

Это функция «если ошибка, то»: она возвращает результат формулы, а при ошибке — значение из второго аргумента.

Как сделать, чтобы вместо ошибки была пустая ячейка?

Укажите вторым аргументом пустые кавычки: =ЕСЛИОШИБКА(A2/B2;"").

Как использовать ЕСЛИОШИБКА с ВПР?

Оберните ВПР целиком: =ЕСЛИОШИБКА(ВПР(E2;A2:B100;2;0);"нет").

Как называется ЕСЛИОШИБКА на английском?

IFERROR. Синтаксис тот же: =IFERROR(value,value_if_error).

Чем ЕСЛИОШИБКА отличается от ЕСНД?

ЕСЛИОШИБКА перехватывает все ошибки, ЕСНД — только #Н/Д.

Итоги: функция ЕСЛИОШИБКА в Excel

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

Часто возникает задача быстро вывести все буквы алфавита и разместить их на разных строках или в разных столбцах по порядку. A, B, C… или А, Б, В… в зависимости от нужного алфавита.

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

• Заполнением двух ячеек вручную и протягиванием курсором
• Опцией «Автозаполнение»
• Формулой, ссылающейся на предыдущую ячейку с нужным шагом (например, A1+1)

В случае с буквами алфавита автозаполнение не работает. Протянуть алфавит без формулы тоже не получится.

Но можно создать алфавит функциями, и есть даже несколько вариантов.

Сделать алфавит обычной функцией

Наиболее простой вариант решить задачу — функция СИМВОЛ.

Она возвращает по коду ANSI знак, который ему соответствует.

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

Буквы кириллицы можно создать аналогичным способом, разница только в диапазоне чисел. Для русского алфавита зарезервированы числа от 192 до 255. Подробнее можно почитать в статье про функцию СИМВОЛ в Excel.

Как протянуть алфавит формулой

Можно ли без создания столбца прибавить к букве число и получить следующую? Да, такая формула тоже возможна.

Чтобы получить одну букву из другой математически (вычитанием для предыдущей и сложением для следующей), поможет комбинация функций СИМВОЛ и КОДСИМВ. КОДСИМВ производит обратную операцию — возвращает код символа из самого символа.

А вот текстовое представление формулы:

=СИМВОЛ(КОДСИМВ(A1)+1)

Создать алфавит в Excel формулами массива

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

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

Еще одно их преимущество — достаточно ввести формулу 1 раз, предварительно выделив область, в которую будет выведен массив, и он будет заполнен разом и полностью.

Важно: фигурные скобки в формулах массива не нужно вводить с клавиатуры, они появляются автоматически при вводе функций сочетанием клавиш Ctrl + Shift + Enter.

Способ №1

Чтобы создать массив чисел, воспользуемся функцией СТРОКА, которая будет обращаться к диапазону строк с нужными нам адресами и возвращать массив чисел, который будет брать на вход уже функция СИМВОЛ.

Такая формула массива потребуется, чтобы создать диапазон из 26 английских букв:

{=СИМВОЛ(СТРОКА(A65:A90))}

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

Более надежно будет создать его функцией ДВССЫЛ:

{=СИМВОЛ(СТРОКА(ДВССЫЛ("65:90")))}

Аналогичные формулы для создания русского алфавита:

{=СИМВОЛ(СТРОКА(A192:A223))}

{=СИМВОЛ(СТРОКА(ДВССЫЛ("192:223")))}

Обратите внимание, буква Ё не входит в сплошной диапазон символов таблицы ANSI. Её придется ввести позже самостоятельно.

Способ №2

Еще один способ получить символы английского алфавита — получить их из адресов ячеек первых 26 столбцов.

Помогут в этом функция АДРЕС, которая выведет адреса ячеек текстом по их координатам, и функция ЛЕВСИМВ, которая извлечет из них первую букву.

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

{=ЛЕВСИМВ(АДРЕС(1;СТОЛБЕЦ(A1:Z1);4;1);1)}

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

А если нужно в вертикальном, формула выше обрабатывается дополнительно функцией ТРАНСП:

{=ТРАНСП(ЛЕВСИМВ(АДРЕС(1;СТОЛБЕЦ(A1:Z1);4;1);1))}

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

Создать алфавит в новых версиях Excel

В Excel 365, 2021 и более поздних версиях можно использовать функцию ПОСЛЕД (задаёт массив с последовательностью чисел):

=СИМВОЛ(ПОСЛЕД(26;;65))

Более того, все формулы, перечисленные выше, уже не требуют Ctrl + Shift + Enter. Массивы автоматически заполняют подходящие им по размерам диапазоны ячеек.

Русский алфавит в Excel

Если язык вашей системы — русский, формула ниже позволит вывести 32 буквы русского алфавита, кроме буквы Ё. Формулу можно вставлять в любую ячейку первой строки таблицы:

=СИМВОЛ(СТРОКА(191:191)+1)

Это не формула массива, а обычная формула. Она вернёт в первой ячейке «А», а дальше её нужно просто «протянуть» вниз.

Формула массива для русского алфавита (Excel 365, 2021 и позднее):

=СИМВОЛ(ПОСЛЕД(32;;192))

Формулы алфавита в других задачах Excel

Формулы массива могут пригодиться не только для банального создания списка букв алфавита. Сами эти буквы могут быть нужны в других задачах. Примеры таких — по ссылкам ниже:

Найти английские буквы в русском тексте

Найти первую букву в ячейке

Следующая статья по формулам массива:

Учимся формулам массива — 2/4

Подсчет количества символов в диапазоне Excel

Формулы массива — одна из самых мощных, но часто неправильно понятых возможностей Excel. Если раньше они были чем-то загадочным и «магическим», то с выходом Excel 365 всё изменилось. Новые версии Excel упростили работу с массивами, сделав их интуитивно понятными и удобными.

В этой статье вы:

• разберётесь, что такое формула массива и как она работает;
• узнаете, чем отличаются формулы в старых и новых версиях Excel;
• увидите практические примеры и типовые ошибки;
• научитесь применять их в повседневной работе.

Что такое формула массива

Формула массива — это формула, которая работает не с отдельным значением, а с набором значений одновременно. Такие формулы могут:

• получать массив на вход (например, диапазон из нескольких ячеек);
• создавать массив внутри формулы;
• возвращать массив как результат.

В старых версиях Excel (до 2019 включительно) формулы массива требовали особого способа ввода — через комбинацию Ctrl + Shift + Enter. Поэтому их ещё называют CSE-формулами. Excel обрамлял такие формулы фигурными скобками { }, показывая, что это массив.

В новых версиях Excel (Microsoft 365 и Excel 2021) появился механизм динамических массивов. Теперь достаточно просто нажать Enter — и если формула возвращает массив, Excel сам «прольёт» его в соседние ячейки. Управлять этим можно с помощью новых функций, например SEQUENCE, FILTER, UNIQUE, SORT.

Примеры формул массива

Пример 1. Классическая формула массива (старый Excel):

=СУММ(ЕСЛИ(A1:A10>100; 1; 0))

Чтобы она сработала, нужно было нажимать Ctrl + Shift + Enter. Она считает количество значений больше 100 в диапазоне.

В Excel 365 эта же формула работает без специальных клавиш:

=СУММ((A1:A10>100)*1)

Пример 2. Формула, возвращающая массив значений (новый Excel):

=ПОСЛЕД(5)

Результат: числа от 1 до 5 в вертикальном столбце. Формула занимает не одну ячейку, а сразу пять — это и есть динамический массив.

Пример 3. Извлечение уникальных значений из столбца:

=УНИК(A2:A100)

Массив и диапазон: в чём разница

Массив — это внутреннее представление набора значений, которое Excel использует в формулах. Порядок и длина фиксированы. Excel воспринимает массив как цельную структуру: нельзя изменить его часть, вставить строку в середину или отсортировать.

Диапазон — это просто адрес ячеек на листе. Его можно сортировать, расширять, вставлять строки и столбцы. Формула массива может ссылаться на диапазон, но работает с ним иначе: как с последовательностью значений, а не как с набором адресов.

Типовая ошибка: нельзя изменить часть массива

Если вы попытаетесь удалить или отредактировать ячейку, которая входит в диапазон, заполненный формулой массива, Excel выдаст ошибку:

Нельзя изменить часть массива.

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

Как понять, что формула — это массив?

Если:

• вводили формулу через Ctrl + Shift + Enter — это массив (в старом Excel);
• формула «проливается» вниз или вбок — это динамический массив (в новом Excel);
• возникает ошибка при редактировании одной ячейки — это тоже признак массива.

Размерность массивов

Как массивы, так и диапазоны в Excel могут быть одномерными (вектор), двумерными (таблица) и трехмерными (многослойная таблица).

Одномерные массивы (векторы) в свою очередь могут быть вертикальными и горизонтальными.

Трехмерные массивы — довольно редко используемая на практике сущность.

Виды массивов

По типам элементов в них:
По типу данных —

• числовой массив (массив чисел)
• строковый массив (текстовые значения)
• булевый массив (значения ИСТИНА-ЛОЖЬ)
• смешанного типа

По изменяемости элементов —

• массив констант,
• вычисляемый массив

Как создать массив в Excel

Создать массив в Excel можно множеством способов. Ниже — подробнее с примерами. Все перечисленные выражения в них являются формулами массива, даже если не содержат в себе функций.

Массив констант, созданный вручную

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

При этом разделителем между элементами слева направо является точка с запятой, а разделителем строк — двоеточие.

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

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

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

Массив цифр как в примере ниже, поможет быстро найти цифры в текстовых ячейках.

Это позволит больше не вводить его вручную, а обращаться к нему по его имени. Более того, Microsoft Excel помогает при их вводе всплывающими подсказками.

С помощью функций

Указанные выше методы создания массивов довольно редки на практике. Чаще всего массивы создаются автоматически при обращении различных функций к диапазонам в Excel.

Единственное условие для создания массива с помощью функции — функция не должна обладать агрегирующим свойством. Например, СУММ, СРЗНАЧ не создадут массив при обращении к диапазону.

На этом сайте есть статья — как дописать символ, слово или текст к нескольким ячейкам. Там рассматриваются несколько способов, в том числе без создания дополнительного столбца. Но вот еще один — с помощью функции СЦЕПИТЬ и формулы массива:

Кавычки-ёлочки в этой формуле нам помогает создать функция СИМВОЛ.

С помощью математических операторов

Достаточно применить любую из математических операций к диапазону — и будет создан массив, аналогичный по размерам.

С помощью операторов сравнения

Аналогично математическим операторам, массивы создаются при сравнении диапазонов с константой или значением ячейки. Результатом операции сравнения являются значения ИСТИНА или ЛОЖЬ.

С помощью других массивов

В двух предыдущих примерах массивы создавались на основе взаимодействия диапазона ячеек и некой константы.

Но можно создать массив и по принципу наоборот — на основе одной ячейки, произведя ее взаимодействие (операторами или функциями) с массивом констант.

Размер результирующего массива в таком случае будет аналогичен размеру массива констант.

Еще раз обратите внимание на разделители строк и столбцов в массивах. Столбцы разделяются точкой с запятой, строки — двоеточием.

С помощью комбинаций указанных методов

Как вы уже догадались, возможности фантазии безграничны — можно строить какие угодно комбинации перечисленных выше методов, производя операции над массивами и диапазонами.

Вывод результата формулы массива на лист

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

Ctrl + Shift + Enter

Если выделить недостаточное количество ячеек — будут выведены не все элементы массива, а только те, что соответствуют по порядку.
Если выделить избыточное количество — лишние ячейки выдадут ошибку #Н/Д.

На примере результирующий массив должен быть размером 5*5, но перед вводом формулы массива был выделен диапазон 6*6.

Динамические массивы в последних версиях Excel

С появлением динамических массивов в Excel 365 Microsoft совершила настоящую революцию, которая кардинально изменила подход к работе с формулами.

Раньше для работы с массивами данных пользователям приходилось использовать специальный режим ввода формул с помощью комбинации Ctrl+Shift+Enter, что создавало дополнительные сложности и часто вызывало ошибки у новичков.

Динамические массивы полностью устранили необходимость в ручном управлении размерами выходных диапазонов. Теперь достаточно ввести формулу в одну ячейку, и Excel автоматически «разольет» результаты в соседние ячейки, самостоятельно определяя необходимый размер выходного диапазона.

Отмена Ctrl+Shift+Enter в новых версиях Excel

Переход на динамические массивы упростил синтаксис формул. Больше не нужно помнить о волшебной комбинации клавиш Ctrl + Shift + Enter — теперь любая формула, возвращающая массив, автоматически становится динамической.

Это особенно полезно при работе с изменяющимися данными, так как размер результирующего массива автоматически подстраивается под объем исходных данных. Ошибка #ПЕРЕНОС! заменила собой множество старых ошибок, связанных с перекрытием диапазонов, делая процесс отладки более понятным и предсказуемым.

Взаимодействие функций Excel с массивами

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

Однако, есть ряд функций, называемых агрегирующими. Если им на вход подается диапазон или массив, они возвращают единственное результирующее значение. Самая популярная — функция СУММ. Но есть и множество других.

Из раздела математических функций к таковым также относятся ПРОИЗВЕД, СУММПРОИЗВ, СУММЕСЛИ, СУММЕСЛИМН.

Практически все статистические функции по природе берут на вход диапазон или массив и возвращают одно число. Наиболее популярные из них — СРЗНАЧ, МИН, МАКС, СЧЁТ, СЧЁТЗ, СЧЁТЕСЛИ.

Среди логических функций агрегирующим свойством обладают функция И и функция ИЛИ.

Формулы массива — примеры формул

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

Приступаем к практическим шагам:

Учимся формулам массива 1/4:

Как создать алфавит в Excel

Модуль числа в Excel возвращает функция ABS: =ABS(A2) превратит −15 в 15, а положительные числа оставит без изменений. Чтобы поставить модуль в формуле, оберните в ABS всё выражение: =ABS(B2-A2) — разница без учёта знака, =СУММПРОИЗВ(ABS(B2:B10)) — сумма модулей диапазона. Отдельного знака модуля (|x|) в Excel нет — только функция ABS.

См. также, и не путать: ABC-анализ в Excel

Что делает функция ABS

Функция ABS возвращает модуль числа. Модуль числа по определению всегда положительное число. Модуль отрицательного числа равен его произведению на -1.

Синтаксис функции ABS

=ABS(ЯчейкаИлиЗначение)

Примеры формул с функцией ABS

Пример 1

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

Задача менеджера - минимизировать затрачиваемые человеческие ресурсы, при этом сохраняя максимальную скорость ответа на входящие звонки.

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

В столбце D используется обычное вычитание значения предыдущего часа.

Руководитель использует функцию ABS, чтобы определить часы, когда разница максимальна, чтобы изменить рабочее расписание сотрудников и "сгладить" график.

Условное форматирование помогает быстро выявить, что стоит задерживать часть сотрудников на один час в 16:00 в этот день, а также выводить больше сотрудников в самом начале смены.

Пример 2.

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

Как происходит расчет в формуле? Сначала для каждого значения массива высчитывается его абсолютное значение функцией ABS, а далее уже эти абсолютные значения суммируются.

Как и все формулы массива, такая формула вводится без фигурных скобок, но ввод в ячейку производится сочетанием клавиш Ctrl + Shift + Enter.

Формула высчитывает кумулятивное значение "проблемных" часов в течение каждого дня и позволяет руководителю выявить дни с самым несбалансированным графиком.

Как поставить модуль в формуле Excel

Знака |x| в Excel нет, поэтому модуль всегда записывают функцией ABS вокруг нужного выражения. Её можно вкладывать в любые формулы: в разницу, в условия, в поиск максимального отклонения.

Другие математические функции

ЗНАК, МУМНОЖ, НЕЧЁТ, НОД, НОК, ОКРВВЕРХ, ОКРВНИЗ, ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ, ОКРУГЛТ, ОСТАТ, ОТБР, ПИ, ПРОИЗВЕД, РИМСКОЕ, СЛУЧМЕЖДУ, СЛЧИС, СТЕПЕНЬ, СУММ, СУММЕСЛИ, СУММПРОИЗВ, ФАКТР, ЦЕЛОЕ, ЧАСТНОЕ, ЧЁТН, ЧИСЛКОМБ

Частые ошибки с ABS

• Минус остался. ABS применили не ко всей разнице: =ABS(B2)-A2 вместо =ABS(B2-A2).
• #ЗНАЧ!. В аргументе текст, который нельзя превратить в число, например «−15» со знаком минус-тире вместо дефиса. Замените символ ПОДСТАВИТЬ.
• Сумма модулей посчитана неверно в Excel 2019. =СУММ(ABS(…)) по диапазону нужно вводить через Ctrl+Shift+Enter — или используйте СУММПРОИЗВ.

Модуль в Excel — частые вопросы

Как сделать модуль в Excel?

Функцией =ABS(A2) — она возвращает число без знака.

Как поставить модуль в формуле?

Оберните выражение в ABS: =ABS(B2-A2). Знака |x| в Excel нет.

Что значит ABS в Excel?

ABS — от английского absolute, «абсолютная величина», то есть модуль числа. В русском Excel функция называется так же.

Как посчитать сумму модулей?

Формулой =СУММПРОИЗВ(ABS(A2:A10)).

Итоги: модуль числа в Excel

Надеюсь, на этой странице вы нашли то, что искали. Модуль в Excel — это всегда ABS вокруг нужного выражения.

Функция ЕОШ в Excel возвращает ИСТИНА, если в ячейке или формуле ошибка, — любая, кроме #Н/Д: =ЕОШ(A2/B2) даст ИСТИНА при делении на ноль. Обычно её используют в проверках: =ЕСЛИ(ЕОШ(A2/B2);0;A2/B2) или =НЕ(ЕОШ(ПОИСК("@";A2))) — «есть ли @ в ячейке». Все ошибки, включая #Н/Д, проверяет ЕОШИБКА, только #Н/Д — ЕНД. На английском функция называется ISERR.

Что делает функция ЕОШ

Эта функция проверяет ячейку и показывает ИСТИНА, если в ячейке есть значение ошибки.

Она будет показывать ЛОЖЬ, если содержимое ячейки вычисляется без ошибки, или если это ошибка #Н/Д.

Синтаксис функции ЕОШ

У функции ЕОШ один аргумент:

=ЕОШ(ячейка-или-вычисление)

Примеры с функцией ЕОШ

Другие функции "Информация"

ЕЛОГИЧ, ЕОШИБКА, ЕПУСТО, ЕСЛИОШИБКА, ЕЧЁТН, ЕНД, ЕНЕТЕКСТ, ЕНЕЧЁТ, ЕССЫЛКА, ЕТЕКСТ, ЕЧИСЛО, ИНФОРМ, НД, СЧИТАТЬПУСТОТЫ, ТИП.ОШИБКИ, Ч, ЯЧЕЙКА

Функция ЕОШ — частые вопросы

Что делает функция ЕОШ?

Проверяет, является ли значение ошибкой, кроме #Н/Д, и возвращает ИСТИНА или ЛОЖЬ.

Чем ЕОШ отличается от ЕОШИБКА?

ЕОШИБКА считает ошибкой и #Н/Д, ЕОШ — нет.

Как посчитать ячейки с ошибками?

Формулой =СУММПРОИЗВ(--ЕОШИБКА(A2:A100)) — все ошибки, или с ЕОШ — все, кроме #Н/Д.

Как называется ЕОШ на английском?

ISERR. ЕОШИБКА — ISERROR, ЕНД — ISNA.

Итоги: функция ЕОШ в Excel

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

Функция ЕНД в Excel проверяет, содержит ли ячейка ошибку #Н/Д («нет данных»), и возвращает ИСТИНА или ЛОЖЬ: =ЕНД(ВПР(E2;A:B;2;0)) даст ИСТИНА, если ВПР не нашла значение. Чтобы вместо ошибки вывести подпись, ЕНД оборачивают в ЕСЛИ: =ЕСЛИ(ЕНД(ВПР(E2;A:B;2;0));"не найдено";ВПР(E2;A:B;2;0)). В Excel 2013 и новее то же короче делает =ЕСНД(ВПР(E2;A:B;2;0);"не найдено"). Другие ошибки (#ДЕЛ/0!, #ЗНАЧ!) ЕНД не замечает.

Синтаксис ЕНД

Синтаксис функции ЕНД:

=ЕНД(значение)

Результат функции всегда ИСТИНА или ЛОЖЬ.

Что делает функция ЕНД?

Эта функция проверяет ячейку, чтобы определить, содержит ли она ошибку НеДоступно (#Н/Д).

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

ЕНД обычно используется в комбинации с другими, например, с функцией ЕСЛИ. Это позволяет обрабатывать ошибки и выводить при необходимости другие значения.

Последние формулы с ЕНД (выделены красным):

=ЕНД(ВПР(C12;A:B;2;0))

=ЕСЛИ(ЕНД(ВПР(C13;A:B;2;0));"не найдено";ВПР(C13;A:B;2;0))

Обратите внимание - поскольку формула при обработке возвращает только ИСТИНА и ЛОЖЬ, то в случаях, когда ошибки нет, приходится вставлять исходную проверяемую функцией формулу повторно. Это удлиняет формулы и удваивает производимые вычисления в таких случаях, поэтому многие предпочитают использовать не такую связку, а функцию ЕСЛИОШИБКА.

Что значит #Н/Д в Excel

#Н/Д — «нет данных» (в английском Excel #N/A). Excel показывает её, когда формула не нашла то, что искала. Самые частые причины:

• ВПР, ГПР, ПОИСКПОЗ или ПРОСМОТРX не нашли искомое значение в таблице.
• Значения выглядят одинаково, но различаются: лишний пробел, число в одном столбце и текст в другом.
• Функция НД() специально вставлена, чтобы точка не попадала на график.
• Диапазоны в формуле массива разного размера.

Чтобы посчитать, сколько значений одного списка нет в другом, ЕНД удобно сочетать с ПОИСКПОЗ:

Другие функции "Информация"

ЕЛОГИЧ, ЕОШ, ЕОШИБКА, ЕПУСТО, ЕЧЁТН, ЕНЕТЕКСТ, ЕНЕЧЁТ, ЕССЫЛКА, ЕТЕКСТ, ЕЧИСЛО, ИНФОРМ, НД, СЧИТАТЬПУСТОТЫ, ТИП.ОШИБКИ, Ч, ЯЧЕЙКА

Почему ЕНД не помогает — частые ошибки

• Ошибка осталась. В ячейке не #Н/Д, а другая ошибка (#ЗНАЧ!, #ССЫЛКА!) — ЕНД её не видит. Используйте ЕОШИБКА или ЕСЛИОШИБКА.
• ВПР возвращает #Н/Д, хотя значение есть. Лишние пробелы или число, сохранённое как текст. Очистите данные СЖПРОБЕЛЫ или приведите к одному типу.
• Формула громоздкая. ЕСЛИ(ЕНД(ВПР(…));…;ВПР(…)) дважды считает ВПР — замените на ЕСНД.

Функция ЕНД — частые вопросы

Что значит #Н/Д в Excel?

«Нет данных»: формула поиска не нашла значение. Чаще всего это результат ВПР или ПОИСКПОЗ.

Что делает функция ЕНД?

Возвращает ИСТИНА, если значение — ошибка #Н/Д, и ЛОЖЬ во всех остальных случаях.

Как убрать #Н/Д из ВПР?

Формулой =ЕСНД(ВПР(E2;A:B;2;0);"") — вместо ошибки будет пустая ячейка.

Чем ЕНД отличается от ЕОШИБКА?

ЕНД проверяет только #Н/Д, ЕОШИБКА — любые ошибки.

Как называется ЕНД на английском?

ISNA.

Итоги: функция ЕНД в Excel

Надеюсь, на этой странице вы нашли то, что искали. ЕНД нужна для проверки «найдено или нет», а для замены ошибки удобнее ЕСНД.

Узнать код символа в Excel можно функцией КОДСИМВ: =КОДСИМВ(A2) вернёт код первого символа ячейки в кодировке Windows (для «А» — 192, для латинской «A» — 65). Код по Юникоду, одинаковый на всех компьютерах, возвращает =UNICODE(A2). Код любого символа в середине строки получают через ПСТР: =КОДСИМВ(ПСТР(A2;5;1)). Обратная функция — СИМВОЛ: она по коду возвращает символ.

Что делает функция КОДСИМВ?

Эта функция возвращает значение ANSI первого символа подаваемой на вход строки. Простыми словами - превращает букву в число (которое ей соответствует в текущей системной кодировке).

Набор символов ANSI используется Windows для идентификации каждого символа клавиатуры с помощью уникального номера.

В наборе ANSI 255 символов. Набор зависит от текущего языка системы, поэтому одни и те же формулы на разных компьютерах могут отдавать разные значения.

КОДСИМВ: формат результата и исходных данных

Аналогично всем текстовым функциям, функция КОДСИМВ преобразует входящее значение в текстовое. При таком преобразовании символы ячеек в числовых форматах могут измениться. В частности, это касается дат, времени, процентного формата.

Синтаксис функции КОДСИМВ

=КОДСИМВ(Ячейка_или_значение)

Функция регистрозависима — буквы в разных регистрах имеют разные коды.

Для символов, отсутствующих в текущей таблице символов системы, возвращается 63, которое при обратном преобразовании функцией СИМВОЛ вернет вопросительный знак.

Если на вход функции подается пустая ячейка или строка нулевой длины, функция возвращает ошибку #ЗНАЧ!

Коды символов Windows на русском языке

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

Примеры формул с функцией КОДСИМВ

Один из ярких примеров я рассмотрел в отдельной статье — Как создать алфавит в Excel. Статья рассказывает, как прибавить к букве единичку, чтобы получить следующую. Конечно, прибавляем мы не к букве, а к её коду. В общем, смотрите статью.

Почему КОДСИМВ возвращает не то — частые ошибки

• 63 вместо кода. Символа нет в кодировке Windows (эмодзи, ₽, многие математические знаки). Используйте UNICODE.
• На другом компьютере другой код. Коды 128–255 зависят от языка системы. UNICODE от него не зависит.
• #ЗНАЧ!. Ячейка пустая или в ней строка нулевой длины.
• Код числа вместо кода цифры. Для числа 2026 функция вернёт код «2» — первого символа.

Функция КОДСИМВ — частые вопросы

Как узнать код символа в Excel?

Формулой =КОДСИМВ(A2) — код в кодировке Windows, или =UNICODE(A2) — номер в Юникоде.

Какая функция возвращает ANSI-код первого символа?

КОДСИМВ (на английском CODE).

Какая функция возвращает символ по коду ANSI?

СИМВОЛ (CHAR). Для кодов Юникода — ЮНИСИМВ (UNICHAR).

Как найти неразрывный пробел?

Проверьте код подозрительного символа: =КОДСИМВ(ПСТР(A2;5;1)). Неразрывный пробел — 160, обычный — 32.

Итоги: функция КОДСИМВ в Excel

Надеюсь, на этой странице вы нашли то, что искали. КОДСИМВ помогает разобраться с невидимыми и похожими символами; для надёжности на разных компьютерах берите UNICODE.