Если ошибка libreoffice calc - Не ошибается лишь тот, кто ничего не делает!

Общая информация о ЕСЛИ (IF)

Функция ЕСЛИ – это одна из самых популярных в Excel функций. В англоязычном Excel, а также в Google Sheets, LibreOffice, OpenOffice, эта функция называется IF. ЕСЛИ (IF) относится к логическим функциям.

Уровень сложности по шкале BRP ADVICE – 2 из 7. Каждая вложенная ЕСЛИ (IF) увеличивает сложность формулы вдвое.

ЕСЛИ (IF) позволяет построить дерево решений, то есть при выполнении условия выполнять одно действие, а при невыполнении – другое. При этом условие должно быть вопросом, имеющим варианты ответа «да / нет» или «верно / неверно» (в терминах Excel, Google Sheets, LibreOffice, OpenOffice это «ИСТИНА / ЛОЖЬ» («TRUE / FALSE»).

Чтобы разобраться c функцией ЕСЛИ (IF), сначала надо разобраться с тем, что такое логические функции.

Что такое логические функции

В Excel, Google Sheets, LibreOffice, OpenOffice и других табличных документах работа логических функций основана на существовании логических параметров. Логических параметров два: первый – ИСТИНА (TRUE), второй – ЛОЖЬ (FALSE).

На основе использования этих логических параметров можно построить дерево решений. В простейшем варианте этого дерева будет задан вопрос, ответом на который может быть ИСТИНА (TRUE) или ЛОЖЬ (FALSE), и дано указание, что делать в каждом из этих двух случаев. Схематически такое дерево решений изображено на рисунке ниже.

Рисунок. Простейшее дерево решений

Логические функции позволяют либо построить такое дерево решений, либо задавать вопрос и получать логический параметр. К первым относятся, например, ЕСЛИ (IF), ЕСЛИОШИБКА (IFERROR). Ко вторым – ЕЧИСЛО (ISNUMBER), И (AND), ИЛИ (OR).

Excel, Google Sheets, LibreOffice, OpenOffice и большинство других программных продуктов позволяет использовать логические параметры ИСТИНА (TRUE) и ЛОЖЬ (FALSE) при выполнении математических операций. Чаще всего, ИСТИНА (TRUE) принимает значение 1, ЛОЖЬ (FALSE) принимает значение 0. Хотя иногда ИСТИНА (TRUE) и ЛОЖЬ (FALSE) принимают другие значения, например, при программировании в VBA ИСТИНА (TRUE) – это -1, а не 1.

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

Функция ЕСЛИ (IF)

Итак, функция ЕСЛИ (IF) позволяет построить дерево решений. У этого дерева решений есть один вопрос на входе и два варианта действий. Вопрос обязательно имеет два варианта ответа: да / нет, верно / неверно или в терминах логических параметров ИСТИНА (TRUE) / ЛОЖЬ (FALSE).

Вопрос и два варианта действий – это и есть три аргумента функции ЕСЛИ (IF).

Первый аргумент функции ЕСЛИ (IF) – логический вопрос. В Excel он называется «лог_выражение». Excel, Google Sheets, LibreOffice, OpenOffice автоматически находят ответ на этот вопрос, и этот ответ должен принять значение ИСТИНА (TRUE) / ЛОЖЬ (FALSE). Что же может дать такой ответ? Самые простые варианты – это классические равенства и неравенства. Например, выражение 12=12 вернет логический параметр ИСТИНА (TRUE), а неравенство 12>40 вернет логический параметр ЛОЖЬ (FALSE).

В логическом вопросе можно использовать равенства (левая и правая часть сравниваются при помощи знака «=»), неравенства (больше – «>», меньше – «<», больше или равно – «>=», меньше или равно «<=»), а также просто не равно – «<>».

Более сложные логические вопросы можно задать с помощью вложенных функций. В результате вычисления таких вложенных функций должен получиться тот самый логический параметр ИСТИНА (TRUE) или ЛОЖЬ (FALSE). К таким функциям относятся, например, ЕЧИСЛО (ISNUMBER), ЕТЕКСТ (ISTEXT), ЕНД (ISNA), И (AND), ИЛИ (OR), в сложных случаях – еще одна ЕСЛИ (IF).

Второй и третий аргумент — это функция ЕСЛИ (IF) должна сделать, когда ответ на вопрос ИСТИНА (TRUE), а когда ЛОЖЬ (FALSE). Функция ЕСЛИ (IF) вычисляет либо только второй аргумент (если ИСТИНА (TRUE)), либо только третий аргумент (если ЛОЖЬ (FALSE)).

Рассмотрим примеры применения функции ЕСЛИ (IF) с одним или несколькими условиями.

Применение ЕСЛИ (IF) с одним условием

Файл-пример №1 вы можете скачать по этой ссылке.

Предположим, в компании установлен план по продажам: каждый менеджер должен продать не менее чем на 1 миллион рублей в месяц. Оклад менеджера по продажам составляет 20 тысяч рублей. При выполнении плана менеджер получает оклад и премию 5% от фактического объема продаж. При невыполнении плана продаж – только оклад.

В конце каждого месяца формируется таблица, содержащая информацию о продажах каждого менеджера. Эта таблица может выглядеть, например, как на рисунке ниже.

Рисунок. Продажи в разрезе менеджеров по продажам за отчетный месяц

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

Рисунок. Отчет по результатам работы менеджеров по продажам

Для того чтобы автоматически заполнять столбец «Выполнение плана» и «Зарплата за месяц, руб.» (столбцы E и F соответственно), можно использовать функцию ЕСЛИ (IF).

Пример 1.1 – подстановка текста при помощи ЕСЛИ (IF)

Файл-пример №1 вы можете скачатьпо этой ссылке.

В столбце «Выполнение плана» в ячейке E4 используем вот такую формулу:

=ЕСЛИ(D4>=1000000;»Молодец!»;»План не выполнен:(«)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

=IF(D4>=1000000;»Молодец!»;»План не выполнен:(«) .

Кстати, в некоторых версиях Excel, вместо «;» должна использоваться «,».

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

Что значат все аргументы ЕСЛИ (IF)?

1. Лог_выражение: D4>=1000000. В нашем примере логический вопрос – это сравнение фактического результата и плана продаж. D4 – это ссылка на ячейку с фактическими продажами этого менеджера. Excel, Google Sheets, LibreOffice, OpenOffice подставляют вместо D4 значение из этой ячейки и проверяют, верно ли указанное неравенство. В результате проверки в формуле получается промежуточный результат, он используется для выбора нужной ветки в дереве решений.

2. Значение_если_истина. На наших схемах это левая ветка дерева решений. В текущем примере значение аргумента – «Молодец!». Этот аргумент показывает, что должна сделать функция ЕСЛИ (IF), когда в результате вычисления первого аргумента получается ИСТИНА (TRUE). В текущем примере необходимо просто написать текст «Молодец!».

Кстати, «Молодец!» у нас написано в кавычках, потому что любой текст внутри формулы должен быть написан в кавычках. Исключением являются только названия функций и именованных диапазонов. В остальных случаях всегда ставьте текст в кавычки.

3. Значение_если_ложь. На наших схемах это правая ветка дерева решений. В текущем примере значение аргумента – «План не выполнен:(«. Этот аргумент показывает, что должна сделать функция ЕСЛИ (IF), когда в результате вычисления первого аргумента получается ЛОЖЬ (FALSE). В текущем примере необходимо просто написать текст «План не выполнен:(».

Здесь мы также указали текст в кавычках, потому что, если не заключать текст внутри формулы в кавычки, возникнет ошибка #ИМЯ? (#NAME?). Исключение – только названия функций и именованных диапазонов.

Что именно делает функция ЕСЛИ (IF) в этом примере?

Во-первых, функция ЕСЛИ (IF) отвечает на логический вопрос (вычисляет первый аргумент). Во-вторых, идет к соответствующей ветке дерева решений. По Александрову П.Ф. получается так:

1. D4>=1000000, следовательно проверяем 1000329>=1000000, выражение верно, значит логический параметр – это ИСТИНА (TRUE).

2. Идем в аргумент Значение_если_истина. Нужно просто подставить текст «Молодец!». Указываем текст в ячейке. Конец расчетов.

Аргумент Значение_если_ложь в этом случае функция ЕСЛИ (IF) игнорирует.

Схематически расчеты выглядят, как на рисунке ниже.

Рисунок. Как работает функция ЕСЛИ (IF), когда логическое выражение возвращает ИСТИНА (TRUE)

По Ильину М.А. получается так:

1. D5>=1000000, следовательно проверяем 848880>=1000000, выражение не верно, значит логический параметр – ЛОЖЬ (FALSE).

2. Идем в аргумент Значение_если_ложь. Нужно просто подставить текст «План не выполнен:(». Указываем текст в ячейке. Конец расчетов.

Аргумент Значение_если_истина в этом случае функция ЕСЛИ (IF) игнорирует.

Схематически расчеты выглядят, как на рисунке ниже.

Рисунок. Как работает функция ЕСЛИ (IF), когда логическое выражение возвращает ЛОЖЬ (FALSE)

А вот этот текст — это ссылка на скачивание примера в Excel 2010-2013. Хотите решить пример онлайн? Оставьте заявку, мы уже работаем над этим.

Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой «Начать заново» на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.

Пример 1.2 – вычисление разных формул при помощи ЕСЛИ (IF)

Файл-пример №1 вы можете скачать по этой ссылке.

В столбце «Зарплата за месяц, руб.» в ячейке F4 используем вот такую формулу:

=ЕСЛИ(D4>=1000000;20000+D4*5/100;20000)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

=IF(D4>=1000000;20000+D4*5/100;20000) .

Не забывайте, в некоторых версиях Excel, вместо «;» должна использоваться «,».

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

Что именно делает функция ЕСЛИ (IF) в этом примере?

Функция ЕСЛИ (IF) отвечает на логический вопрос (вычисляет первый аргумент) и переходит к соответствующей ветке дерева решений. По Александрову П.Ф. получается так:

1. D4>=1000000, следовательно проверяем 1000329>=1000000, выражение верно, значит логический параметр – это ИСТИНА (TRUE).

2. Идем в аргумент Значение_если_истина. Нужно вычислить 20000+D4*5/100 (то есть оклад 20 тысяч и та самая премия 5% от продаж). Получаем 70016, указываем это значение в ячейке. Конец расчетов.

Аргумент Значение_если_ложь в этом случае функция ЕСЛИ (IF) игнорирует.

По Ильину М.А. получается так:

1. D5>=1000000, следовательно проверяем 848880>=1000000, выражение не верно, значит логический параметр – ЛОЖЬ (FALSE).

2. Идем в аргумент Значение_если_ложь. Нужно просто поставить 20000. Указываем число в ячейке. Конец расчетов.

Аргумент Значение_если_истина в этом случае функция ЕСЛИ (IF) игнорирует.

Применение ЕСЛИ (IF) с несколькими условиями

Пример 2 – разные условия в логическом выражении

Файл-пример №2 вы можете скачать по этой ссылке.

В прошлом примере и у менеджеров, и у старших менеджеров был одинаковый план продаж на месяц. Усложним задачу: установим повышенный план старшим менеджерам – 1 миллион 200 тысяч в месяц. Отчет тогда будет выглядеть, как на рисунке ниже.

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

В этом случае в столбце «Выполнение плана» в ячейке E4 используем вот такую формулу:

=ЕСЛИ(ЕСЛИ(C4=»Старший менеджер»;D4>=1200000;D4>=1000000);»Молодец!»;»План не выполнен:(«)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

=IF(IF(C4=»Старший менеджер»;D4>=1200000;D4>=1000000);»Молодец!»;»План не выполнен:(«) .

Не забудьте, в некоторых версиях Excel, вместо «;» должна использоваться «,».

Что именно делает функция ЕСЛИ (IF) в этом примере?

По Александрову П.Ф. получается так:

1. Функция ЕСЛИ (IF) начинает расчет с логического выражения и видит там вложенную функцию ЕСЛИ (IF). Excel, Google Sheets, LibreOffice, OpenOffice сначала считает вложенную функцию.

2. Вложенная функция ЕСЛИ (IF) проверяет логическое выражение: должность менеджера старший менеджер или нет. Александров П.Ф. – это не старший менеджер. Поэтому логическое выражение во вложенной ЕСЛИ (IF) возвращает ЛОЖЬ (FALSE).

3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_ложь и сравнивает фактические продажи с планом менеджера по продажам. 1 000 329 больше 1 000 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ИСТИНА (TRUE).

4. Результат вычисления вложенной функции ЕСЛИ (IF) передается в основную функцию. Основная функция ЕСЛИ (IF) видит логический параметр ИСТИНА (TRUE) и переходит к своему (а не к вложенному) аргументу Значение_если_истина. Этот аргумент – просто текст «Молодец!».

Формула с несколькими условиями, то есть с вложенными функциями ЕСЛИ (IF), возвращает в ячейку текст «Молодец!».

Посмотрите построенное дерево решений на схеме ниже.

Рисунок. Дерево решений для функции ЕСЛИ (IF) с несколькими условиями

По Ильину М.А. получается так:

2. Вложенная функция ЕСЛИ (IF) проверяет логическое выражение: должность менеджера старший менеджер или нет. Ильин М.А. – это не старший менеджер. Поэтому логическое выражение во вложенной ЕСЛИ (IF) возвращает ЛОЖЬ (FALSE).

3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_ложь и сравнивает фактические продажи с планом менеджера по продажам. 848 880 меньше 1 000 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ЛОЖЬ (FALSE).

4. Результат вычисления вложенной функции ЕСЛИ (IF) передается в основную функцию. Основная функция ЕСЛИ (IF) видит логический параметр ЛОЖЬ (FALSE) и переходит к своему (а не к вложенному) аргументу Значение_если_ложь. Этот аргумент – просто текст «План не выполнен:(».

Формула с несколькими условиями, то есть с вложенными функциями ЕСЛИ (IF), возвращает в ячейку текст «План не выполнен:(».

Посмотрите построенное дерево решений на схеме ниже.

Рисунок. Дерево решений для функции ЕСЛИ (IF) с несколькими условиями

По Незенецеву А.А. получается так:

2. Вложенная функция ЕСЛИ (IF) проверяет логическое выражение: должность менеджера старший менеджер или нет. Незенецев А.А. – это старший менеджер. Поэтому логическое выражение во вложенной ЕСЛИ (IF) возвращает ИСТИНА (TRUE).

3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_истина и сравнивает фактические продажи с планом старшего менеджера по продажам. 1 204 346 больше 1 200 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ИСТИНА (TRUE).

Посмотрите построенное дерево решений на схеме ниже.

Рисунок. Дерево решений для функции ЕСЛИ (IF) с несколькими условиями

По Соколовой Н.И. получается так:

2. Вложенная функция ЕСЛИ (IF) проверяет логическое выражение: должность менеджера старший менеджер или нет. Соколова Н.И. – это старший менеджер. Поэтому логическое выражение во вложенной ЕСЛИ (IF) возвращает ИСТИНА (TRUE).

3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_истина и сравнивает фактические продажи с планом старшего менеджера по продажам. 1 046 625 меньше 1 200 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ЛОЖЬ (FALSE).

Посмотрите построенное дерево решений на схеме ниже.

Рисунок. Дерево решений для функции ЕСЛИ (IF) с несколькими условиями

Формула для расчета заработной платы в примере 3

В столбце «Зарплата за месяц, руб.» в ячейке F4 используем вот такую формулу:

=ЕСЛИ(ЕСЛИ(C4=»Старший менеджер»;D4>=1200000;D4>=1000000);20000+D4*5/100;20000)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

=IF(IF(C4=»Старший менеджер»;D4>=1200000;D4>=1000000);20000+D4*5/100;20000) .

Не забудьте, в некоторых версиях Excel, вместо «;» должна использоваться «,».

В этом случае функция ЕСЛИ (IF) работает точно так же, как и в ячейке E4.

Пример 4 – разные условия и в логическом выражении, и в ветках дерева решений

Файл-пример №3 вы можете скачатьпо этой ссылке.

Итак, у нас есть менеджеры, есть старшие менеджеры. У старших менеджеров план выше, чем у обычных менеджеров. Для того чтобы такая модель работала, часто необходимо дополнительное стимулирование для старших менеджеров. Например, премия старшего менеджера повышается до 6%. То есть у нас сразу несколько условий:

1. Премия выплачивается только если выполнен план.

2. Если должность старший менеджер, план – 1 миллион 200 тысяч, иначе – 1 миллион.

3. Если должность старший менеджер, премия – 6%, иначе – 5%.

В итоге получается отчет, как на рисунке ниже.

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

Как решить такую задачу при помощи функции ЕСЛИ (IF)?

В ячейке F4 можно написать такую формулу:

=ЕСЛИ(

ЕСЛИ(C4=»Старший менеджер»;D4>=1200000;D4>=1000000);

20000+D4*ЕСЛИ(C4=»Старший менеджер»;6;5)/100;

20000)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

=IF(

IF(C4=»Старший менеджер»;D4>=1200000;D4>=1000000);

20000+D4*IF(C4=»Старший менеджер»;6;5)/100;

20000)

Не забывайте, в некоторых версиях Excel, вместо «;» должна использоваться «,».

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

На рисунке ниже схематически изображено построенное дерево решений.

Рисунок. Пример дерева решений с несколькими условиями и в логическом выражении, и в других аргументах функции ЕСЛИ (IF)

Частые ошибки при работе с функцией ЕСЛИ (IF)

1. Для функции ЕСЛИ (IF) всегда должен быть указан первый аргумент – логическое выражение и второй аргумент – значение если истина. Третий аргумент необязательный. Пользователи часто забывают указать третий аргумент особенно при работе со сложными формулами, из-за этого в некоторых случаях вместо нужного результата в ячейке появляется логический параметр ЛОЖЬ (FALSE).

2. Сложность формулы очень быстро растет при использовании вложенных ЕСЛИ (IF). Из-за этого очень часто пользователи забывают закрыть скобки вложенных вычислений, не ставят разделитель аргументов («;» или «,»). В зависимости от ошибки формулу в ячейку либо не удается записать, либо она считается неправильно.

3. В сложных формулах с ЕСЛИ (IF) очень тяжело отслеживать правильность расчетов: каждая вложенная функция ЕСЛИ (IF) добавляет в ваше дерево решений один вопрос и минимум две ветки. В среднем человек в уме держит до 7 объектов, получается, что при трех вложенных ЕСЛИ (IF) в уме нужно держать 3 вопроса и 6 веток дерева решений. Контролируемость и надежность формулы стремительно снижается.

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

Совет: работа со сложными формулами

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

1. Определите конечную цель ваших расчетов: какой результат вы должны получить в итоге.

2. Определите функцию, которая позволяет это сделать.

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

4. Если аргумент простой (число, ссылка на ячейку), поставьте его и переходите к следующему аргументу.

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

6. Всегда следите за скобками: как только вы закончили описание функции, закрывайте скобки.

И помните, если формула слишком сложная, лучше сделать промежуточный расчет в соседней ячейке.

Чем дополнить и заменить функцию ЕСЛИ (IF)

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

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

Функция ЕСЛИ (IF) иногда может быть заменена на функцию ВПР (VLOOKUP), ГПР (HLOOKUP), ПРОСМОТР(LOOKUP), ЕСЛИОШИБКА (IFERROR), СУММЕСЛИ (SUMIF) или СЧЁТЕСЛИ (COUNTIF).

Быстрые ссылки

Файл-пример №1 «Применение функции ЕСЛИ (IF) с одним условием» вы можете скачать по этой ссылке.

Файл-пример №2 «Применение функции ЕСЛИ (IF) с несколькими условиями» вы можете скачать по этой ссылке.

Файл-пример №3 «Применение функции ЕСЛИ (IF) с несколькими условиями в разных аргументах» вы можете скачать по этой ссылке.

Остались вопросы? Пишите нам в форму обратной связи и записывайтесь на интенсив по Excel или курс по функциям Excel.

Понравилась статья? Узнайте больше раньше других: заходите на нашу страницу в ВКонтакте и подписывайтесь на новости.

Желаем вам успешной работы!

Ваш Виктор Рыбцев

и команда Учебного центра BRP ADVICE.

Источник

From The Document Foundation Wiki

Wiki Home
Development
Design
QA
Events
Documentation
Website
Localization
Accessibility
Marketing
Diversity
Wiki Help

Documentation Home
Official Publications
Other Resources
FAQ
HowTo
Macros
Docs Development

Function name:

IFERROR

Category:

Logical

Summary:

Evaluates one argument and if no error is found, the value of that argument is returned. If an error is found, then a second argument is evaluated, and its value is returned.

Syntax:

IFERROR(Value; Alternative Value)

Returns:

Returns a value whose type depends on the argument selected as the return value.

Arguments:

Value is an expression, or a reference to a cell containing an expression, that is evaluated and returned if no error is found.

Alternative Value is an expression, or a reference to a cell containing an expression, that is evaluated and returned only if an error was found during the evaluation of Value.

The IFERROR function does not evaluate Otherwise Value if the evaluation of Then Value proceeds without an error. For example, the formula =IFERROR(23; 1/0) returns 23. While 1/0 might be expected to generate a #DIV0! error, the division is never attempted in this case.

Additional details:

Details specific to IFERROR function

=IFERROR(X; Y) is semantically equivalent to =IF(ISERROR(X); Y; X), except that the former case is more efficient because X is only computed once.

General information about Calc’s logical functions

Note:
For convenience, the information in this subsection is repeated on all pages describing Calc’s logical functions.

Calc uses the numbers 0 (FALSE) and 1 (TRUE) to represent logical values. For example, enter 1 in a cell that has the default general number format. Then select ▸ on the Menu bar to display the Format Cells dialog, select the Boolean Value option in the Category area of the Numbers tab, and click the OK button. Notice that the number 1 is now displayed as TRUE. To revert to displaying the number 1, repeat the process but select Number instead of Boolean Value.

To enter a logical value into a cell, simply type either TRUE or FALSE (case-insensitive) into the cell. Calc will recognize the logical value, display it in uppercase letters, and change the cell’s format to that appropriate to Boolean values.

Calc functions that produce a logical result return a number, either 0 or 1. In the case of a formula that simply calls such a logical function and is in a cell that has the default general number format, the cell is switched to the Boolean value format and the returned value is displayed as FALSE or TRUE. For example =TRUE() returns the value 1, which is displayed as TRUE; if you then change the format to a numerical format, it is displayed as the number 1.

Calc functions that test for a logical result always evaluate the expression to be tested and check if it is equal to 0. The value 0 is taken as FALSE and any other value is taken as TRUE. For example, =NOT(0) returns TRUE and =NOT(57.89) returns FALSE.

In contrast to Calc, Microsoft Excel has a separate type for logical values — they are not numbers but are sometimes converted to numbers. Take care therefore if you need compatibility.

Examples:

Formula	Description	Returns
=IFERROR(2; 5)	Here the first argument contains no error and so the function returns 2.	2
=IFERROR(SQRT(−2); 23)	Here the first argument contains an invalid argument error (Err:502) and so the function returns the value of the second argument.	23
=IFERROR(ASIN(45); 3/0)	Here the first argument contains a #NUM! error and so the function returns the value of the second argument. However, the second argument contains a division by 0 error.	#DIV/0!
=IFERROR(C8; C9) where cell C8 contains =1/0 and cell C9 contains the text «Error found».	Here the first argument contains a #DIV/0! error and so the function returns the value of the second argument.	«Error found»
=IFERROR(C8; C9) where cell C8 contains =13 and cell C9 contains the text «Error found».	Here the first argument contains no error and so the function returns its value.	13

AND

FALSE

IFNA

IFS

ISERROR

NOT

SWITCH

TRUE

XOR

ODF standard:

Section 6.15.5, part 2

Equivalent Excel functions:

IFERROR

Источник

Блог про LibreOffice

Советы, трюки, хитрости, инструкции, руководства

Страницы

2 октября 2014 г.

LibreOffice Calc: Поиск и возврат значений (функции VLOOKUP и IFERROR)

С Хабра:
«Как известно, знание функции VLOOKUP в MS EXCEL, достаточно чтобы в Москве стать средним аналитиком. Если человек знаком еще и с PIVOT или например знает как убрать дубликаты из списка — все двери в счастливый офисный мир перед ним настежь раскрыты.»

Эта статья является продолжением статьи «Импорт таблицы с сайта». Для диплома мне нужно вычислить ряд показателей. Некоторые из них считаются простым сложением счетов из оборотной ведомости. Но тут две проблемы:

Счетов очень много;
Мне нужно проделать это для пяти банков и в динамике за три года, итого 15 раз.

Не буду приводить все свои расчеты, покажу основную суть на небольшом примере.

Постановка задачи

Для расчета мне нужны данные из оборотной ведомости по счетам бухгалтерского учета кредитной организации (пример). Из нее мне нужны только левый столбец с номерами счетов и крайний правый с итоговыми суммами.

Допустим, один из требуемых показателей рассчитывается как сумма счетов: 20210 + 20305 + 30210 + 30233. На деле формула может включать десятки счетов.

Таким образом, мне нужно найти номера этих счетов и узнать сумму на них, а дальше сложить их.

Подготовка данных

Поиск и возврат значения (VLOOKUP)

Итак, чтобы не искать вручную номера счетов и их значения, я воспользовался функцией VLOOKUP. Она выполняет поиск в вертикальном порядке со ссылкой на соседние ячейки справа. Эта функция проверяет, содержится ли определенное значение в первом столбце массива. Функция возвращает значение в той же строке в соседнем столбце. Функцию можно найти в Мастере функций, но я буду вводить её прямо в ячейке.

Синтаксис функции следующий:
=VLOOKUP(Критерий поиска; Массив; Индекс; Порядок сортировки)

Критерий поиска — значение (число, текст или логическое значение), которое надо найти в первом столбце.

Массив — это массив данных, который должен содержать как минимум 2 столбца.

Индекс — номер столбца в массиве, содержащего значения, которые должны быть возвращены. Первый столбец имеет номер 1.

Порядок сортировки — необязательный параметр, который указывает порядок сортировки. Может принимать значение 0 и 1.

В моем случае формула будет иметь следующий вид:
=VLOOKUP(E16;A16:B142;2;0)

В столбце E у меня указаны номера счетов, которые нужно просуммировать для нахождения требуемого показателя. Значения в нём являются критерием поиска. Сам поиск производится в массиве состоящим из столбца А и В. Но поиск идет в столбце А, в котором находятся номера счетов оборотной ведомости.

В качестве индекса у меня указано число 2, это значит, что значения будут браться из второго столбца массива, т.е. из столбца В.

Обработка ошибок (IFERROR)

Проблема решается обертыванием предыдущей формулы в функцию IFERROR, которая проверяет наличие ошибок. В случае их отсутствия она возвращает полученное значения, в противном случае — то значение, которое мы укажем.
=IFERROR(значение; другое значение)

Обертывание выглядит следующим образом:
=IFERROR(VLOOKUP(E16;A16:B142;2;0);0)

Т.е. в случае успеха IFERROR вернет значение, которое найдет VLOOKUP. В случае ошибки вместо #Н/Д будет подставлен 0.

Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

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

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

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

Даже если вы годами используете функцию ВПР, то с высокой долей вероятности эта статья будет вам полезна и не оставит равнодушным. Я, например, будучи IT-специалистом, а потом и руководителем в IT, пользовался VLOOKUP 15 лет, но разобраться со всеми нюансами довелось только сейчас, когда я на профессиональной основе стал обучать людей Excel.

ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.

Синтаксис

Функция ВПР имеет четыре параметра:

=ВПР( ; ; [; ] ), тут:

— искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

— ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра ;

— номер столбца в диапазоне, из которого будет возвращено значение;

— это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона . В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).

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

Как же конкретно работает формула ВПР

Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром , в противном случае возвращается код ошибки #Н/Д (#N/A).

Схемы работы формул

ВПР тип I

ВПР тип II

Следствия для формул вида I

Формулы можно использовать для распределения значений по диапазонам.
Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.

Следствия для формул вида II

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

Производительность работы функции ВПР

Вы добрались до кульминационного места статьи. Казалось бы, ну какая разница, укажу ли я в качестве последнего параметра ноль или единицу? В основном все указывают, конечно же, ноль, так как это довольно практично: не надо заботиться о сортировке первого столбца массива, сразу видно, найдено значение или нет. Но если у вас на листе несколько тысяч формул ВПР (VLOOKUP), то вы заметите, что ВПР вида II работает медленно. При этом обычно все начинают думать:

мне нужен более мощный компьютер;
мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.

И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.

Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

Недостатки формулы

Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце указанного массива, а во-вторых, только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).

Некоторые аспекты применения формулы в реальной жизни

Диапазонный поиск

Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

Поиск текстовых строк

Безусловно, ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть два символа подстановки: «?» — заменяет один любой символ в текстовой строке, «*» — заменяет любое количество любых символов.

Борьба с пробелами

Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ (TRIM).

Разный формат данных

Если первый параметр функции ВПР ссылается на ячейку, которая содержит число, но которое хранится в ячейке в текстовом виде, а первый столбец массива содержит числа в правильном формате, то поиск будет неудачным. Возможна и обратная ситуация. Проблема легко решается переводом параметра 1 в необходимый формат:

=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;

=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.

Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

Двойное отрицание —D7.
Умножение на единицу D7*1.
Сложение с нулём D7+0.
Возведение в первую степень D7^1.

Перевод числа в текст производится через сцепку с пустой строкой, которая заставляет Excel преобразовать тип данных.

Как подавить выдачу #Н/Д

Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).

Если ВПР вернёт код ошибки #Н/Д, то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строка), а если ошибки не произошло, то эта функция сделает вид, что её вообще нет, а есть только ВПР, вернувший нормальный результат.

Массив

Часто забывают ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.

Хорошей идеей является размещение справочного массива на отдельном листе рабочей книги. Не путается под ногами, да и сохраннее будет.

Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.

Ну и на грани гениальности — оформить массив в виде умной таблицы.

Использование функции СТОЛБЕЦ для указания колонки извлечения

Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.

Создание составного ключа через &»|»&

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

Это моя первая статья для Лайфхакера. Если вам понравилось, то приглашаю вас посетить мой сайт, а также с удовольствием прочту в комментариях о ваших секретах использования функции ВПР и ей подобных. Спасибо. 🙂

Блог про LibreOffice

Советы, трюки, хитрости, инструкции, руководства

Страницы

2 октября 2014 г.