Excel или в формулах: ИЛИ (функция ИЛИ) — Служба поддержки Майкрософт

Поиск и исправление ошибок в вычислениях Excel

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

Второй способ обнаружения ошибок – Excel отображает в левом верхнем углу ячейки зелёный треугольник (индикатор ошибки). При выборе такой ячейки появляется смарт-тег проверки ошибок.

Для проверки ошибок необходимо выполнить следующие шаги:

1. Выберите лист, который требуется проверить на наличие ошибок.

2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Проверка наличия ошибок. Откроется окно диалога Контроль ошибок.

3. В окне диалога Контроль ошибок просмотрите информацию о текущей ошибке в левой части окна.

4. Для просмотра более детального описания ошибки и возможных вариантов её исправления нажмите кнопку Справка по этой ошибке.

5. Нажмите кнопку Показать этапы вычисления. MS Excel откроет окно диалога Вычисление формулы, где вы сможете просмотреть значения различных частей вложенной формулы, вычисляемые в порядке расчёта формулы:

a) нажмите кнопку Вычислить, чтобы проверить значение подчёркнутой ссылки. Результат вычислений показан курсивом;

b) если подчёркнутая часть формулы является ссылкой на другую формулу, нажмите кнопку Шаг с заходом, чтобы отобразить другую формулу в поле Вычисление. Нажмите кнопку Шаг с выходом, чтобы вернуться в предыдущую ячейку и формулу;

c) Выполняйте указанные действия, пока не будет вычислена каждая часть формулы;

d) Чтобы снова увидеть вычисления, нажмите кнопку Заново;

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

6. Для изменения формулы в строке формул нажмите кнопку Изменить в строке формул.

7. Если ошибка является несущественной, вы можете нажать кнопку Пропустить ошибку. Помеченная ошибка при последующих проверках будет пропускаться.

8. Для перехода к следующей ошибке нажмите кнопку Далее. Для возврата к предыдущей – кнопку Назад.

9. Доведите до конца проверку ошибок и закройте окно диалога Контроль ошибок.

Трассировка связей между формулами и ячейками

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

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

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

Для отображения ячеек, в формулы которых входит какая-либо ячейка, её следует выделить и нажать кнопку Зависимые ячейки в группе Зависимости формул вкладки Формулы.

Один щелчок по кнопке Зависимые ячейки отображает связи с
ячейками, непосредственно зависящими от выделенной
ячейки. Если эти
ячейки также влияют на другие
ячейки, то следующий щелчок отображает связи с зависимыми
ячейками. И так далее.

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

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

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

В этом случае вашим помощником может выступать панель инструментов Окно контрольного значения.

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

Добавление ячеек в окно контрольных значений

Для добавления на панель контрольных значений выполните следующие действия:

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

Чтобы выделить все ячейки листа с формулами, на вкладке Главная в группе Правка нажмите кнопку Найти и выделить и выберите команду Формулы.

2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Окно контрольного значения.

.

4. В окне диалога Добавление контрольного значения проверьте правильность выбранного диапазона и нажмите кнопку Добавить.

Циклические ссылки

Циклические ссылки возникают, когда формула в какой-либо ячейке посредством других ячеек ссылается сама на себя.

Например, ячейка С4 = Е7, Е7 = С11, С11 = С4. В итоге С4 ссылается на С4.

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

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

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

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

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

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

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

Если циклическая ссылка – одна на листе, то в строке состояния будет выведено сообщение о наличии циклических ссылок с адресом ячейки.

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

Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.

На вкладке Формулы в группе Зависимости формул выберите элемент Поиск ошибок и в раскрывающемся списке пункт Циклические ссылки.

Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После её корректировки или удаления – со второй и т. д.

Итак, используя вышеперечисленные инструменты и приёмы, вы сможете облегчить поиск, исправление и предупреждение ошибок в рабочих книгах Excel.

Успехов!!!

Свидетельство о регистрации СМИ: Эл № ФС77-67462 от 18 октября 2016 г.
Контакты редакции: +7 (495) 784-73-75, smi@4dk. ru

Самые распространенные ошибки в Excel и как их исправить

Содержание

• Как исправить ошибку #ПЕРЕНОС! в Excel
• Как исправить ошибку #ЗНАЧ! в Excel
• Как исправить ошибку #ПУСТО! в Excel
• Как исправить ошибку #ИМЯ? в Excel

Как исправить ошибку #ПЕРЕНОС! в Excel

Прежде чем рассмотреть ошибку #ПЕРЕНОС! (#SPILL), рассмотрим, что такое перенос. В Excel это означает, что формула возвращает несколько значений (массив), и они автоматически переносятся в соседние ячейки.

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

Ошибка #ПЕРЕНОС! возникает, когда формула возвращает несколько значений, но Excel не может вывести один или несколько результатов.

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

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

Рассмотрим решения, которые подойдут для наиболее распространенных вариаций этой ошибки.

Диапазон для переноса содержит одну или более ячеек с значениями

Решение: очистить диапазон для переноса.

Диапазон для переноса находится внутри таблицы

Решение 1: преобразовать таблицу в диапазон данных.

Для этого выполните следующие шаги:

1. нажмите на любую ячейку в таблице,
2. в меню в верхней части окна выберите «Конструктор»,
3. выберите команду «Преобразовать в диапазон».

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

Решение 2: переместить формулу за границы таблицы.

Диапазон для переноса содержит объединенные ячейки

Решение: разделить ячейки внутри диапазона.

Как исправить ошибку #ЗНАЧ! в Excel

Ошибка #ЗНАЧ! (#VALUE) возникает в следующих случаях:

• что-то не так с ячейкой (ячейками), на которую ссылается формула,
• что-то не так с самой формулой.

Иногда найти источник проблемы не просто. Ниже — о самых распространенных случаях и как и их исправить.

Математическая формула ссылается на текст

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

Решение: использовать вместо формулы (уравнения, составленного пользователем) функцию (формулу, заранее заданную Excel). Функции по умолчанию игнорируют большую часть текстовых значений и производят расчеты лишь с числами.

Например, на примере ниже функция =СУММ(B2,B11) будет игнорировать текст в ячейке B11.

Скриншот: Zapier.com

Стоит помнить, что эта функция не оповещает автоматически о пропусках ячеек, и ее результат может вводить в заблуждение.

Одна или более ячеек содержат пробелы

В этих случаях ячейка выглядит пустой.

Решение: найти и заменить пробелы. Вот как это сделать:

1. выделите диапазон ячеек, к которому обращается формула;
2. нажмите на иконку с биноклем, нажмите «Найти и выделить»;
3. в окне «Найти и заменить» выберите «Заменить»;
4. в поле «Найти» вставьте пробелы;
5. поле «Заменить на» оставьте пустым;
6. нажмите «Заменить все».

Как исправить ошибку #ПУСТО! в Excel

Ошибка #ПУСТО! (#REF) возникает, когда формулы ссылается на ячейку, которая уже не существует. Разберем наиболее распространенные причины возникновения ошибки и как их исправить.

Ячейка, на которую ссылается формула, удалена

Формула использует прямые ссылки на ячейки (каждая ячейка отделена запятой), но одна или несколько ячеек удалены. Это основная причина, почему Excel не рекомендует использовать прямые ссылки на ячейки.

Решение 1: Если ячейки удалены случайно, отмените это действие.

Решение 2: Обновите формулу, чтобы она ссылалась на диапазон ячеек. В этом случае Excel сможет произвести вычисления, даже если одна из ячеек была удалена.

Формула содержит относительные ссылки

Относительная ссылка означает, что используемые данные привязаны к ячейке, в которую вставлена формула. Например, если формулу =СУММ(B2:E2) скопировать из ячейки G2 в G3, Excel предположит, что должен суммировать все ячейки из колонок B-E в ряду 3.

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

Например, если формулу =СУММ(G2:G7) перенести в ячейку I4, Excel предположит, что пользователю требуется сложить шесть ячеек над клеткой I4. В данном случае это невозможно, так как доступны лишь три ячейки.

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

Например, формула =СУММ(G2:G7) с абсолютными ссылками будет выглядеть как: =СУММ($G$2:$G$7).

Как исправить ошибку #ИМЯ? в Excel

Ошибка #ИМЯ? (#NAME) возникает, если название формулы неверно написано. Рассмотрим основные решения проблемы.

Название формулы содержит опечатку

Решение: обновить название формулы. Лучший способ избегать опечаток — использовать встроенный редактор формул Excel. Когда пользователь начинает печатать название формулы, программа автоматически предложит список названий, содержащих те же буквы.

Неверное название формулы

Иногда пользователи вводят название несуществующей формулы или формулы, которая называется иначе (например, ПЛЮС вместо реальной функции СУММ). В этом случае формула вернет ошибку #ИМЯ?.

Решение: найти правильное имя формулы и обновить ее.

Вот как это сделать:

1. выберите в меню раздел «Формулы»;
2. нажмите на «Вставить функцию» в левой части панели инструментов, в открывшемся окне «Вставка функции» просмотрите недавно использовавшиеся функции или полный список всех доступных функций;
3. выберите и вставьте нужную функцию;
4. в редакторе формул обновите ячейки, к которым будет отсылаться формула (поля зависят от выбранной функции).

Эти советы применимы и к ошибкам в Google Таблицах (за исключением #ПЕРЕНОС!, которая часто отображается как #REF).

Источник.

Фото на обложке: Aihr.com

Подписывайтесь на наш Telegram-канал, чтобы быть в курсе последних новостей и событий!

Excel Функция ИЛИ | GoSkills

Что такое функция ИЛИ?

Функция Excel ИЛИ — это логическая функция, которая определяет, верно ли хотя бы одно условие из нескольких критериев. Даже если выполняется только одно условие, это значение проходит тест.

Синтаксис или формат функции ИЛИ: 

  =ИЛИ(логический1, [логический2],...)  

Функция ИЛИ может обрабатывать до 255 аргументов, каждый из которых является логической проверкой. Требуется только один аргумент. Однако, если вы используете функцию ИЛИ, скорее всего, у вас их будет как минимум две.

Как использовать функцию ИЛИ в Excel

Загрузите файл с бесплатными упражнениями

Используйте этот бесплатный файл Excel, чтобы следовать учебному пособию по функции ИЛИ.

Введите адрес электронной почты

Пример 1

Вот практический пример использования функции ИЛИ. Отдел продаж хочет вознаграждать всех сотрудников команды North за выдающиеся общие результаты, а также тех, у кого более 30 продаж из любой другой команды. Любое из этих двух условий давало бы работнику право на это вознаграждение.

Для определения строк, удовлетворяющих одному или обоим критериям, требуются два оператора. В первом утверждении будет сказано, что значение в ячейке B3 больше 30, а во втором утверждении будет сказано, что значение в ячейке C3 равно слову «Север». Формула ИЛИ будет выглядеть следующим образом:

  =ИЛИ(B3>30,C3=«Север») 
 

Если оба утверждения верны, отображается результат ИСТИНА. Если хотя бы одно утверждение истинно, отображается результат ИСТИНА.

Если ни одно из утверждений не является истинным, Excel возвращает значение ЛОЖЬ.

Использование с функцией фильтрации

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

На вкладке «Данные» на ленте щелкните значок «Фильтр» и используйте стрелки фильтра в верхней части набора данных, чтобы выбрать только строки со значениями «ИСТИНА» в столбце D. Нажмите «ОК», чтобы применить фильтр.

Строки, которые не удовлетворяют ни одному из указанных критериев, отфильтровываются с экрана, на что указывают синие номера строк.

Использование с функцией ЕСЛИ

Есть и другие способы проявить творческий подход с помощью функции ИЛИ.

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

Поскольку первый аргумент функции ЕСЛИ вызывает логическую проверку, мы можем сделать логической проверкой саму функцию ИЛИ.

Введите:

  =ЕСЛИ(ИЛИ(B3>30,C3=«Север»),  

Аргумент после =ЕСЛИ( выше, который раньше был полной формулой ИЛИ, теперь становится первым аргументом функции ЕСЛИ. 

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

Можно ввести третий аргумент для функции IF value_if_false . Если третий аргумент не введен, результат FALSE будет отображаться для строк, которые не удовлетворяют обоим критериям.

Однако ввод пустой пары двойных кавычек в качестве третьего аргумента для value_if_false приведет к пустым ячейкам для строк, которые не удовлетворяют всем установленным критериям.

Логическое утверждение…

«Если B3 больше 30, или если C3 равно «Север», то вывести слово «Бонус». В противном случае отображать пустую ячейку»,

… будет представлено в Excel следующим образом:

  = ЕСЛИ (ИЛИ (B3> 30, C3 = «Север»), «Бонус», «») 
 

Пример 2

Вот пример использования комбинации ЕСЛИ/ИЛИ с математическим расчетом для value_if_true . В следующей электронной таблице показаны посещения веб-сайта компании за текущий 30-дневный период и предыдущий 30-дневный период.

Если в столбце B или столбце C значение больше 500, это место считается местом с интенсивным движением.

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

Как и в предыдущем примере, функции ЕСЛИ и ИЛИ будут вложены для Аргумента 1 следующим образом:

  =ЕСЛИ(ИЛИ(B2>500,C2>500)  

Второй аргумент будет иметь вид функции СРЗНАЧ:

  AVERAGE(B2:C2)  

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

Полная формула будет выглядеть так:

  = ЕСЛИ (ИЛИ (B2> 500, C2> 500), СРЗНАЧ (B2: C2))  

Результат рабочего листа, когда эта формула скопирована во все соответствующие ячейки в столбце D, будет:

Использование с сортировкой

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

Сводка

Функция Excel ИЛИ полезна для выполнения тестов в рамках одной функции для определения ячеек, которые соответствуют одному из нескольких критериев.

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

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

Изучайте Excel бесплатно

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

Начать бесплатный курс

Понравилось? Подпишитесь и присоединитесь к 400 962 другим пользователям.

Получайте наш последний контент раньше всех. Отписаться когда угодно.

Ваш электронный адрес

Клаудия Бакли

Клаудия — менеджер проектов и инструктор по бизнес-навыкам в GoSkills. В свободное время она читает детективные романы и занимается генеалогическими исследованиями.

Памятка по формулам Excel (примеры)

Памятка по формулам Excel (Содержание)

• Шпаргалка по формулам Excel
• Текстовые функции в Excel
• СТАТИСТИЧЕСКИЕ Функции в Excel
• Функции ДАТЫ И ВРЕМЕНИ в Excel
• МАТЕМАТИЧЕСКИЕ Функции в Excel

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

Расчеты, используемые в шпаргалке по формулам Excel

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

Функции ТЕКСТА в Excel

• Перейдите на вкладку ФОРМУЛЫ.
• Нажмите на функции ТЕКСТ. Откроется раскрывающийся список. Обратитесь к скриншоту ниже.

Как мы видим здесь, перечислены несколько строковых функций. Наиболее часто используемые текстовые функции: ВЛЕВО, ВПРАВО, СРЕДНЯЯ, СЦЕПИТЬ, НИЖЕ, ДЛСТР и т. д.

Как работает функция ТЕКСТ?

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

Вы можете скачать этот шаблон формул для шпаргалок Excel здесь – Шаблон формул для шпаргалок Excel

Пример №1

Мы берем некоторые имена и применим к ним текстовую функцию.

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

СТАТИСТИЧЕСКИЕ Функции в Excel

• Перейдите на вкладку ФОРМУЛА.
• Нажмите на опцию «Дополнительные функции». Выберите категорию Статистические функции.

• Откроется раскрывающийся список функций.

Наиболее часто используемыми статистическими функциями являются MIN, MAX, COUNT, AVERAGE, MEDIAN и т. д.

Пример #2

Ниже приведены некоторые числовые значения.

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

ДАТА И ВРЕМЯ Функции в Excel

• Перейдите на вкладку ФОРМУЛЫ.
• Щелкните категорию функций даты и времени. Откроется раскрывающийся список функций, как показано на скриншоте ниже.

Наиболее часто используемые функции ДАТА И ВРЕМЯ: СЕЙЧАС, ДАТА, ДЕНЬ НЕД, СЕГОДНЯ, РАБДЕНЬ и т. д.

Давайте разберемся с этим на некоторых примерах на снимке экрана ниже.

Пример #3

Ниже приведены некоторые данные.

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

МАТЕМАТИЧЕСКИЕ Функции в Excel

• Перейдите на вкладку ФОРМУЛЫ.
• Щелкните категорию функций Math & Trig. Откроется раскрывающийся список функций, как показано на скриншоте ниже.

Наиболее часто используемыми математическими функциями являются СУММ, ПРОМЕЖУТОЧНЫЙ ИТОГ, СЛУЧМЕЖДУ, ПРОИЗВЕДЕНИЕ, КОРЕНЬ-КВОРОТ и т. д.

Рассмотрим работу этой функции на примере.

Пример #4

Возьмем ниже числовую серию.

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

Что нужно помнить о Шпаргалке по формулам Excel 

• Если вы пропустили или забыли синтаксис какой-либо функции, перейдите на вкладку ФОРМУЛЫ и нажмите кнопку «Вставить функцию» под снимком экрана или нажмите клавиши SHIFT+F3.