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

Как вернуть значение, если данное значение существует в определенном диапазоне в Excel?

Какие существуют обозначения ошибок и способы их исправления?

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

##### — Что обозначает и как исправить?

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

  • Проверить формулу, вычисляет ли она количество дней между двумя датами;
  • Если в формуле нет ошибок, следует изменить формат ячейки, например, с «Дата и время» на «Общий» или «Числовой».

#ЗНАЧ! — Что обозначает и как исправить?

Эти сообщение об использовании текста вместо числа или ло гического значения (ИСТИНА или ЛОЖЬ). То есть Excel такой плейбой и не может преобразовать данный текст в ячейке в правильный тип данных.
Необходимо убедиться, что формула или функция ссылается на те ячейки, которые содержат действительные значения.
Например, если в ячейке A2 содержится число, а в ячейке A3 содержится текст, то в ячейке А1 с формулой =A2+A3 будет ото бражаться #ЗНАЧ! .

#ДЕЛ/0! — Что обозначает и как исправить?

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

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

#ИМЯ? — Что обозначает и как исправить?

Эти символы указывают на то, что в формуле используется несуществующее имя или неправильный оператор.

1 вариант

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

  • В окне открытого листа перейдите на вкладку «Формулы» и нажмите на кнопку «Менеджер имен» в группе «Определенные имена». В окне Менеджер имен проверьте, есть ли имя в списке.
Читайте так же:
Как включить (включить / выключить) защиту листа в Excel?

Если это имя отсутствует, добавьте его, используя инструкции «Как дать имя ячейке или диапазону ячеек? «.

2 вариант

Если в написании имени допущена ошибка, необходимо проверить его написание.

  • В окне открытого листа нажмите клавишу F3. В окне «Вставить имя» выберите нужное имя из списка имен и нажмите кнопку «OK».
  • Внесите исправления (при необходимости) в формулу, отображаемую в соответствующей ячейке.
  • Нажмите кнопку «Enter», чтобы зафиксировать его.
3 вариант

Если в формуле используется функция с ошибкой в написании.
Например, СУМ(A1:А10) вместо СУММ(A1:А10) .

  • В окне открытого рабочего листа выберите ячейку, в которой произошла ошибка в описании функции.
  • Рядом с ячейкой выберите кнопку «Источник ошибок».
  • Выберите команду «Редактировать в строке формул» в списке команд.
  • В панели формул в поле Имя появляется правильное имя формулы, и вы соответствующим образом изменяете неправильное написание формулы.
  • Зафиксируйте результат, нажав Enter.
4 вариант

Если в формулу введен текст, который не заключен в двойные кавычки, то необходимо проверить все текстовые записи в форму ле и заключить их в двойные кавычки. Иначе Excel будет пытаться распознать данный текст как имя диапазона ячеек, хотя это и не предполагалось.
Например, СУММ(A1 А10) вместо СУММ(A1:А10) .

5 вариант

Если в ссылке на диапазон ячеек пропущено двоеточие, то для исправления необходимо в формуле во всех подобных ссылках про верить знак двоеточия и исправить по мере необходимости.
Например, СУММ(A1 А10) вместо СУММ(A1:А10) .

6 вариант

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

#Н/Д — Что обозначает и как исправить?

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

1 вариант

Для формул, требующих недостающих данных и #H/D или ND(), недостающие данные должны быть введены в #H/D.

2 вариант

Если в функции RUN, SCAN, SEARCH или ERR указано неправильное значение аргумента «искомое значение» (например, ссылка на диапазон ячеек, что недопустимо), необходимо обратиться только к правильной ячейке соответственно.

3 вариант

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

4 вариант

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

Читайте так же:
Как быстро посчитать, равны ли ячейки x или y в Excel?
5 вариант

Если для просмотра значений в несортированной таблице ис пользуются функции ВПР , ГПР или ПОИСКПОЗ , для которых по умолчанию сведения для просмотра таблиц должны располагаться в возрастающем порядке.
В функциях ВПР и ГПР содержится аргумент «интерваль ный _просмотр», позволяющий искать определенное значение и в несортированной таблице. Но при этом, чтобы отыскать определен ное значение, аргумент «интервальный_просмотр» должен иметь значение ЛОЖЬ .
В функции ПОИСКПОЗ содержится аргумент «тип_сопостав ления», позволяющий сортировать данные для поиска. Если же со ответствующее значение отыскать невозможно, то рекомендуется задать аргумент «тип_сопоставления» равный 0.

6 вариант

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

7 вариант

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

#ССЫЛКА! — Что обозначает и как исправить?

Эти символы сообщают о неправильной ссылке на ячейку.

1 вариант

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

2 вариант

Если вы используете функцию OLE, которая связана с незапущенным приложением, запустите нужное приложение.

3 вариант

Ссылка на недоступный объект DDE (Dynamic Data Exchange), например «system», требует проверки того, что используется корректный раздел DDE.

4 вариант

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

#ЧИСЛО! — Что обозначает и как исправить?

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

1 вариант

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

2 вариант

При использовании функции с итерацией (подбором параметров), например «VSD» или «STAVKA», следует попробовать другое начальное приближение или изменить количество итераций.

3 вариант

Excel не может отобразить слишком большое или слишком маленькое число, поэтому необходимо настроить формулу. Получите результат между 1*10307 и 1*10307.

Читайте так же:
Как вернуть имя файла без расширения в ячейку в Excel?

#ПУСТО! — Что обозначает и как исправить?

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

1 вариант

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

  • Двоеточие (:), разделяющее начальную и конечную ячейки диапазона, используется для указания ссылки на непрерывный диапазон ячеек. Например: SUMP(C1:C20) .
  • Использует оператор конкатенации с точкой с запятой (;) для указания ссылки на два диапазона, которые не пересекаются. Например: SUMP(C1:C20;D1:D20) .
2 вариант

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

Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Excel является одной из самых популярных функций. Он возвращает результат (значение или другую формулу) в зависимости от условия.

Функция ЕСЛИ в Excel

Ниже приведен синтаксис этой функции.

I F(log_expression; значение_если_истинно; [значение_если_ложно]).

Выражение log_expression является проверяемым условием. Например, A2<100. Если значение в ячейке A2 действительно меньше 100, в памяти формируется ответ TRUE и функция возвращает значение, указанное в следующем поле. При отсутствии этого условия в памяти формируется ответ FALSE и возвращается значение последнего поля.

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

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

Очень простой пример. Он должен проверить, превышают ли продажи отдельных товаров 30 наименований или нет. Если они превышены, формула должна вернуть «Ok», в противном случае — «Delete». Расчет и результат показаны ниже.

Функция Excel ЕСЛИ с одним условием

Продажи первого товара равны 75, то есть условие, что они больше 30, выполнено. Следовательно, функция возвращает то, что указано в следующем поле, «Ok». Поскольку продаж второго товара было меньше 30, условие (>30) не выполняется, и возвращается альтернативное значение, указанное в третьем поле. Именно эта функция определяет, является ли утверждение истинным или ложным. Мы можем вычислить результаты для каждого продукта, потянув вычисление вниз.

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

Читайте так же:
Быстрое создание положительно отрицательной гистограммы в Excel

Прогноз остатков

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

Функция ЕСЛИ для задания условия в формуле

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

Формулы ЕСЛИ в Excel также широко используются в матричных формулах. Мы не будем продолжать. Я рекомендую интересующимся прочитать статью о том, как рассчитать минимальное и максимальное значение для каждого условия. Однако расчет, представленный в той статье, уже не актуален, поскольку в Excel 2016 появились функции МИНИМАЛЬНО и МАКСИМАЛЬНО. Но очень полезно иметь пример, так как он пригодится в другой ситуации.

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто существует более 2 возможных условий (проверенное и альтернативное), иногда даже 3 или 4. В этом случае вы можете использовать функцию IF, но она должна быть вложена внутрь самой себя, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Читайте так же:
Как вернуть значение ячейки на основе нескольких критериев в Excel?

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

Если какое-либо из указанных условий выполнено, то следующие условия также не проверяются. Важно, чтобы они были указаны в правильном порядке. Excel, если бы мы начали проверку с B2*1, не заметил бы эти условия, поскольку они включены в B2*1. B2*1 — это первая проверка, и если значение меньше 0,9, то оно также меньше 1. Тогда у нас будет только два варианта: меньше 1 и альтернатива, т.е. 1 или больше.

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

Подсказка функции

Обязательно закройте все скобки в конце, иначе Excel выдаст ошибку

Ошибка из-за нехватки скобки

Функция Excel ЕСЛИМН

I F в Excel в целом является надежным исполнителем. Но вариант, когда нужно написать длинную цепочку условий, не очень приятен, потому что, во-первых, написать с первого раза не всегда получается (если условие неверное, то скобка не закрывается); во-вторых, разобраться при необходимости в такой формуле бывает сложно, особенно когда условий много, а сами вычисления сложные.

В Microsoft Excel 2016 появилась функция ELIMIN, о которой и написана эта статья. IF заточена специально для проверки нескольких условий. Больше нет необходимости писать ЕСЛИ сто раз и считать скобки. Условия нужно только перечислить, а скобки закрыть.

Это работает следующим образом. Теперь мы можем применить новую формулу ELIMIN к приведенному выше примеру.

Функция Excel ЕСЛИМН

Как видите, формульная нотация выглядит гораздо проще и понятнее.

Вот некоторые моменты, которые вам следует учесть. Несмотря на изменение порядка, условия по-прежнему перечислены правильно, так что диапазоны не пересекаются. Как и в обычном IF, последнее альтернативное условие также должно быть указано. В IF указывается только альтернативное значение, которое имеет место, если ни одно из условий не выполняется. В данном случае условием является B2 > 1. В результате этого можно избежать, записав в поле условия TRUE, что указывает на то, что если ни одно из ранее перечисленных условий не выполняется, произойдет TRUE и будет возвращено последнее альтернативное значение.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector