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

Обновлено: 05.07.2024

Но вы не одиноки: даже самые продвинутые пользователи Excel время от времени сталкиваются с этими ошибками. По этой причине мы собрали несколько советов, призванных сэкономить вам несколько минут (или часов) при устранении неприятных ошибок Excel. Ознакомьтесь с ними ниже.

Рекомендации, которые помогут уменьшить количество ошибок Excel

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

• Начинайте каждую формулу со знака равенства.
• Для умножения чисел используйте символ *, а не X.
• Сопоставьте все открывающие и закрывающие скобки, чтобы они были парами.
• Используйте кавычки вокруг текста в формулах.

8 сообщений об ошибках Excel, которые вам надоело видеть (и как их исправить)

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

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

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

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

Как решить эту ошибку:

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

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

Это немного более сложная ошибка Excel. Вот краткое описание того, почему это может появиться в ячейке, в которой вы работаете:

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

Как устранить эту ошибку

Если формула написана правильно, а ваша электронная таблица по-прежнему возвращает ошибку, Excel, вероятно, запутался в одной из ваших записей внутри формулы. Простой способ исправить это — заставить Excel вставить формулу за вас. Выделите ячейку, в которой вы хотите запустить формулу, затем нажмите вкладку «Формулы» в верхней панели навигации. Выберите «Вставить функцию» (если вы используете Microsoft Excel 2017, этот параметр будет в крайнем левом углу панели навигации «Формулы»).

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

Как устранить эту ошибку

Нажмите на правую границу заголовка столбца и увеличьте ширину столбца.

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

Как устранить эту ошибку

Эту ошибку довольно легко устранить. Просто измените значение ячейки на значение, не равное 0, или добавьте значение, если ваша ячейка была пустой. Вот пример:

Что на самом деле означает эта ошибка? Это означает, что вы могли случайно удалить или вставить ячейку, которая использовалась в вашей формуле. Например, предположим, что столбец "Результат" ниже ссылается на формулу: =СУММ(A2,B2,C2).

Если бы мы случайно удалили столбец "Номер 2", мы бы увидели эту ошибку:

Как устранить эту ошибку

Прежде чем вставить набор ячеек, убедитесь, что нет формул, которые ссылаются (отсюда "ССЫЛКА") на удаляемые ячейки. Кроме того, при удалении ячеек важно еще раз проверить, на какие формулы ссылаются эти ячейки.

Совет для профессионалов: если вы случайно удалили несколько ячеек, вы можете нажать кнопку "Отменить" на панели быстрого доступа (или нажать CTRL+Z для ПК или Command + Z для Mac), чтобы восстановить их.

Чтобы дать вам дополнительный контекст, вот как работают операторы ссылок Excel:

• Оператор диапазона (точка с запятой): определяет ссылку на диапазон ячеек.
• Оператор объединения (запятая): объединяет две ссылки в одну ссылку.
• Оператор пересечения (пробел): возвращает ссылку на пересечение двух диапазонов.

Как устранить эту ошибку

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

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

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

Как устранить эту ошибку

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

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

Например, когда вы вводите формулу Excel, убедитесь, что вы не включаете такие значения, как 1000 долларов США в денежном формате. Вместо этого введите 1000, а затем отформатируйте ячейку с валютой и запятыми после расчета формулы.

Как устранить эту ошибку

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

Вот как можно отформатировать числа после удаления запятых и валюты из формулы:

(Чтобы настроить валюту, щелкните маленький треугольник справа от значка, чтобы выбрать параметр в раскрывающемся меню.)

Хотите больше советов по Excel? Прочтите этот пост о простых советах, приемах и сочетаниях клавиш в Excel.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Исправить ошибку для определенной функции

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

Проблемы с вычитанием

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

Вычесть ссылку на ячейку из другой

Введите два значения в две отдельные ячейки. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере в ячейке D2 указана запланированная сумма, а в ячейке E2 — фактическая сумма. F2 имеет формулу =D2-E2.

Или используйте СУММ с положительными и отрицательными числами

Введите положительное значение в одну ячейку и отрицательное значение в другую. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки вместе. В этом примере ячейка D6 содержит бюджетную сумму, а ячейка E6 имеет фактическую сумму в виде отрицательного числа.F6 имеет формулу =СУММ(D6,E6).

В Windows откройте панель управления регионом.

Windows 10: нажмите "Пуск", введите "Регион" и нажмите панель управления "Регион".

Windows 8: на начальном экране введите «Регион», нажмите «Настройки», а затем нажмите «Регион».

Windows 7: нажмите "Пуск", введите "Регион", а затем нажмите "Регион и язык".

На вкладке "Форматы" нажмите "Дополнительные настройки".

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

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

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

Вычесть ссылку на ячейку из другой

Введите две даты в две отдельные ячейки. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере в ячейке D10 указана дата начала, а в ячейке E10 — дата окончания. F10 имеет формулу =E10-D10.

Или используйте функцию РАЗНДАТ

Введите две даты в две отдельные ячейки. В третьей ячейке используйте функцию DATEDIF, чтобы найти разницу в датах. Дополнительные сведения о функции РАЗНДАТ см. в разделе Расчет разницы между двумя датами.

Расширьте столбец даты. Если ваша дата выровнена по правому краю, то это дата. Но если он выровнен по левому краю, это означает, что дата на самом деле не является датой. Это текст. И Excel не распознает текст как дату. Вот несколько решений, которые могут решить эту проблему.

Проверить начальные пробелы

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

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

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

Выберите столбец, содержащий дату, щелкнув его заголовок.

Нажмите Данные > Текст в столбцы.

Дважды нажмите "Далее".

На шаге 3 из 3 мастера в разделе Формат данных столбца нажмите Дата.

Выберите формат даты и нажмите "Готово".

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

Проверьте настройки даты вашего компьютера

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

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

Есть два решения этой проблемы. Вы можете изменить систему дат, которую использует ваш компьютер, чтобы она соответствовала системе дат, которую вы хотите ввести в Excel. Или в Excel вы можете создать новый столбец и использовать функцию ДАТА для создания истинной даты на основе даты, сохраненной в виде текста. Вот как это сделать, если система дат вашего компьютера — мм/дд/гггг, а ваша текстовая дата — 31/12/2017 в ячейке A1:

Создайте следующую формулу: =ДАТА(ПРАВО(A1,4),СРЕДИНА(A1,4,2),ЛЕВО(A1,2))

Результатом будет 31 декабря 2017 г.

Если вы хотите, чтобы формат отображался как дд/мм/гг, нажмите CTRL+1 (или + 1 на Mac).

Выберите другой язык, в котором используется формат дд/мм/гг, например английский (Великобритания). Когда вы закончите применять формат, результатом будет 31/12/2017, и это будет настоящая дата, а не текстовая дата.

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

Проблемы с пробелами и текстом

1. Выберите ячейки, на которые ссылаются

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

2. Найти и заменить

На вкладке "Главная" нажмите "Найти и выбрать" > "Заменить".

3. Замените пробелы ничем

В поле Найти введите один пробел. Затем в поле Заменить на удалите все, что там может быть.

4. Заменить или Заменить все

5. Включите фильтр

Иногда существуют скрытые символы, отличные от пробелов, из-за которых ячейка кажется пустой, хотя она на самом деле не пуста. Одиночные апострофы внутри ячейки могут сделать это. Чтобы избавиться от этих символов в столбце, включите фильтр, выбрав Главная > Сортировка и фильтр > Фильтр.

6. Установите фильтр

Нажмите стрелку фильтра и снимите флажок "Выбрать все". Затем установите флажок «Пробелы».

7. Установите любые неназванные флажки

Установите флажки, рядом с которыми ничего нет, например этот.

8. Выберите пустые ячейки и удалите

Когда Excel вернет пустые ячейки, выделите их. Затем нажмите клавишу Удалить. Это удалит все скрытые символы в ячейках.

9. Очистить фильтр

Нажмите на стрелку фильтра и выберите Очистить фильтр от. чтобы все ячейки были видны.

10. Результат

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

Тот же пример с ISTEXT

Здесь функция ISTEXT была добавлена ​​в столбец F. Все ячейки в порядке, кроме одной со значением TRUE. Это означает, что в ячейке E2 есть текст. Чтобы решить эту проблему, вы можете удалить содержимое ячейки и повторно ввести значение 1865,00. Или вы также можете использовать функцию CLEAN для очистки символов или использовать функцию REPLACE для замены специальных символов другими значениями.

После использования CLEAN или REPLACE вы захотите скопировать результат и использовать Главная > Вставить > Специальная вставка > Значения. Возможно, вам также придется преобразовать числа, сохраненные в виде текста, в числа.

Используйте функцию ЕСЛИ в Excel, когда вы хотите что-то проверить, и покажите один результат, если результат теста равен True, и другой результат, если результат теста False. В Excel 365 также есть функция IFS, которая может заменить несколько новых функций ЕСЛИ.

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

Введение — функция ЕСЛИ

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

Хронология видео

• 0:00 Введение
• 0:13 Проведите тест
• 0:39 Форма заказа
• 0:55 Простая формула ЕСЛИ
• 1:39 Проверить формулу

Как работает функция ЕСЛИ в Excel

ЕСЛИ — это одна из логических функций в Microsoft Excel, и синтаксис функции ЕСЛИ состоит из трех частей (аргументов):

1. logical_test: ПРОВЕРКА чего-либо, например значения в ячейке.
2. value_if_true: Укажите, что должно произойти, если результат проверки равен TRUE .
3. value_if_false: Укажите, что должно произойти, если результат теста FALSE .

Пример 1. Проверка на пустую ячейку

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

Это 3 части (аргументы) функции ЕСЛИ для этого примера:

1. логический тест: ПРОВЕРКА ячейки D7, чтобы убедиться, что она пуста ( D7="" ).
2. значение, если оно истинно: если D7 пусто (ИСТИНА), в ячейке с формулой Итого ничего не будет отображаться ("" ).
3. значение, если false: если D7 не пусто (FALSE), ячейка с формулой "Итого" будет умножать цену на количество ( C7*D7 )

Завершенная формула: =ЕСЛИ( D7="" , "" , C7*D7 )

На приведенном ниже снимке экрана ячейка D7 пуста (результат TRUE), поэтому формула ЕСЛИ в ячейке E7 также выглядит пустой.

Если ячейка D7 не пуста (результат FALSE), расчет ячейки Total показывает значение Цена x Количество.

Пример 2. Вложенные функции ЕСЛИ

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

Один логический тест

В примере 1 формула ЕСЛИ содержала один логический тест: пуста ли ячейка Qty (D7)?

Два логических теста

Чтобы проверить ячейки "Цена" и "Количество", к существующей формуле добавляется еще одна функция ЕСЛИ.

Это создает второй логический тест: пуста ли ячейка "Цена" (C7)?

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

1. В строке 7 ячейка "Цена" пуста, поэтому в ячейке "Итого" есть пустая строка ("")
2. В строке 8 ячейка "Количество" пуста, поэтому в ячейке "Итого" есть пустая строка ("")
3. В строке 9 ни одна ячейка не пуста, поэтому рассчитывается общая цена (цена x количество)

Порядок вложенных функций ЕСЛИ

В предыдущем примере с вложенной формулой ЕСЛИ было 2 логических теста:

• Ячейка "Цена" пуста?
• Ячейка "Количество" пуста?

Эти тесты были одинаково сложными.

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

Насколько сложны тесты?

В некоторых случаях логические тесты во вложенной формуле ЕСЛИ имеют разные уровни сложности.

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

Оценка в ячейке B2 равна 86, что соответствует первому логическому тесту, поэтому результат "Хорошо".

Неверный результат: сначала проверить проще

Однако, если критерий "Среднее" стоит первым в формуле, результат будет неверным.

Оценка в ячейке B2 равна 86, что соответствует первому логическому тесту, поэтому результат "Средний".

Примечания о вложенных формулах ЕСЛИ

Вот несколько важных моментов, о которых следует помнить при построении вложенной формулы ЕСЛИ:

1. При построении вложенных формул ЕСЛИ может иметь значение порядок перечисления тестов. См. раздел ниже: самые сложные испытания должны быть первыми.
2. Не добавляйте в формулу слишком много функций ЕСЛИ, иначе формулу будет сложно построить правильно и сложно поддерживать с течением времени.
3. Многие вложенные формулы ЕСЛИ можно заменить другими решениями, такими как формула ВПР или ИНДЕКС и ПОИСКПОЗ, для большей гибкости. См. пример в разделе «Является ли функция IF лучшим решением?» раздел ниже.

Пример 3: функция ЕСЛИ с флажком

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

• Для местных заказов установите флажок, чтобы добавить галочку, и в связанной ячейке будет указано ИСТИНА.
• Если галочка снята, связанная ячейка показывает FALSE

Формула ЕСЛИ в ячейке E12 проверяет связанную ячейку (G11)l на наличие в ней значения "ЛОЖЬ". Если это так, сумма налога равна нулю.

Если ячейка G11 не содержит "ЛОЖЬ", промежуточная сумма в ячейке E10 умножается на налоговую ставку в ячейке D12, чтобы показать сумму налога.

=ЕСЛИ(G11=ЛОЖЬ,0,E10*D12)

Функция IFS — как она работает

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

• Вы можете проверить несколько условий, чтобы убедиться, что они ИСТИННЫ.
• Формула возвращает результат для первого условия ИСТИНА.

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

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

1. logical_test1: ПРОВЕРКА чего-либо, например значения в ячейке.
2. value_if_true1: укажите, что должно произойти, если результат проверки равен TRUE .
3. логический_тест2. логическая проверка127: (необязательно) ПРОВЕРКА чего-либо, например значения в ячейке.
4. значение_если_истина2. value_if_true127: (необязательно) Укажите, что должно произойти, если результат проверки равен TRUE .

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

Пример 1 IFS: проверка на наличие пустых ячеек

В этом примере вместо вложенной формулы ЕСЛИ используется формула ЕСЛИ.

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

• =IFS(COUNTA(B9:D9)=3, C9*D9, B9="","", C9="","нет цены", D9="","нет количества")

Вот как работает эта формула IFS:

1. Сначала функция СЧЕТЧИК подсчитывает значения в ячейках B9:D9. Этот счет равен 3?
   • Если значение TRUE, умножьте значение ячейки цены (C9) на количество (D9), чтобы рассчитать общую цену.
2. Затем проверяется ячейка B9, чтобы убедиться, что она пуста. Две двойные кавычки представляют пустую строку
   • Если TRUE, вернуть пустую строку
3. Затем проверяется ячейка C9, чтобы убедиться, что она пуста
   • Если TRUE, вернуть текстовую строку "нет цены"
4. Наконец, проверяется ячейка D9, чтобы убедиться, что она пуста
   • Если TRUE, вернуть текстовую строку "нет цены"

Ниже вы можете увидеть результаты этой формулы в ячейках E9:E12

• В ячейке E9 результатом является общая цена, поскольку все 3 ячейки заполнены.
• В ячейке E10 функция возвращает пустую строку "", поскольку название продукта отсутствует.
• В ячейке E11 указано "нет цены", поскольку ячейка с ценой пуста.

IFS Ex2: нет аргумента value_if_false

В Excel 365 вы можете использовать функцию ЕСЛИ вместо нескольких вложенных функций ЕСЛИ:

• проверить несколько условий, чтобы убедиться, что они ИСТИННЫ
• возвращает результат для первого условия TRUE

Однако, в отличие от функции ЕСЛИ, функция ЕСЛИ не имеет конечного аргумента для значения_если_ложь

Как формулы IFS могут выполнять следующие действия без этого аргумента?

Окончательный тест и ценность

В качестве обходного пути для имитации аргумента value_if_false функции ЕСЛИ:

• Используйте TRUE в качестве окончательного логического теста.
• Используйте "значение для всего остального" в качестве последнего аргумента "значение_если_истина"

Например, эта формула имеет 2 логических теста: хороший и средний.

Любые другие оценки, не прошедшие первые два теста, получат оценку "Плохо", поскольку они проходят последний тест

=IFS(
C9>=80,"Хорошо",
C9>=60,"Средне",
ИСТИНА,"Плохо"
)

Является ли функция ЕСЛИ лучшим решением?

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

Ответ: Нет, обычно нет. Если вам нужно сделать что-то большее, чем простой тест на истинность или ложность, другие функции Excel обеспечат более эффективное решение проблемы.

Пример: рейтинговая система

Вот один пример, где функцию ВПР проще настроить и поддерживать по сравнению с формулой вложенной функции ЕСЛИ или формулой функции ЕСЛИ (в Excel 365).

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

Решения функции IF и функции IFS

Эта формула ЕСЛИ в ячейке D2 может вычислять рейтинг:

Или, в Excel 365, эта формула IFS может рассчитать рейтинг:

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

• Вам нужно будет найти все эти формулы и изменить все числа уровней рейтинга.
• Или, если бы было добавлено больше уровней рейтинга, формулы пришлось бы переписать.

Решение функции ВПР

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

Затем используйте эту формулу ВПР в ячейке D2, чтобы вычислить рейтинг:

В будущем, если уровни рейтинга изменятся, просто измените информацию в таблице RatingLookup.

• Вам НЕ НУЖНО искать все эти формулы и менять все номера рейтинговых уровней.
• Или, если бы было добавлено больше уровней рейтинга, формулы НЕ нужно было бы переписывать.

Еще примеры

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

1. Преобразование оценок учащихся из цифр в буквы – используйте функцию ВПР или ИНДЕКС/ПОИСКПОЗ.
2. Выберите процентную ставку на основе даты транзакции — используйте ГПР
3. Показывать конкретное значение вместо ошибки — используйте ЕСЛИОШИБКА (Excel 2007 и более поздние версии)

Формулы в именованной таблице Excel

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

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

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

Обычная ссылка на ячейку и структурированная ссылка

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

• Обычная ссылка на ячейку содержит адрес ячейки, например C7
  • Ссылка может содержать один или два знака $, например $C7, C$7, $C$7
  • Знак $ создает абсолютную ссылку на строку или столбец (не изменится, если формула будет скопирована в другое место).
  • Без знака $ ссылка на строку или столбец является относительной (может измениться, если формула скопирована в другое место).
  • Структурированные ссылки могут также включать имя таблицы или символ @, например, Таблица1[@Total]

  Получить образец файла

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

  Дополнительную информацию о типах планов и включенных возможностях см. на странице планов Smartsheet.

  Формула вернет ошибку, если Smartsheet ожидает от формулы элементов, отличных от предоставленных. Ниже приведен список сообщений об ошибках формулы, их причины и решения, которые вы можете применить для исправления ошибок.

  Причина

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

  Разрешение

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

  Причина

  Формула находится в столбце определенного типа (дата, число, символ), а возвращаемое значение имеет другой тип.

  Разрешение

  Либо переместите формулу в другой столбец, либо преобразуйте результат в соответствующий тип.Результаты формулы могут быть преобразованы в текстовые значения путем добавления пустой строки. Например, =TODAY() + "" позволит вам ввести сегодняшнюю дату в текстовую колонку.

  Причины

  Машина формул все еще выполняет вычисления на серверной части.

  • Часто это может означать, что вы столкнулись с ошибкой медленности из-за сетевых условий или интенсивного использования браузера.
  • Это также может указывать на то, что вы используете большую формулу и/или формула ссылается на ячейку, которая ссылается на другую ячейку посредством формулы или ссылки на ячейку.

  Разрешение

  Часто эта ошибка исправляется после завершения расчета.

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

  • Скорость браузера. Скорость браузера может быть одним из основных факторов замедления при загрузке, навигации и сохранении таблицы. В наших тестах мы обнаружили, что Smartsheet лучше всего работает с Google Chrome. Если это вариант или при устранении неполадок, попробуйте использовать этот браузер, чтобы увидеть, улучшит ли он время отклика.
  • Расширенные функциональные возможности. Широкое использование формул, связывание ячеек и условное форматирование могут способствовать замедлению загрузки. Если ваш лист начинает работать медленнее, попробуйте отключить правила условного форматирования, чтобы узнать, не влияет ли это на проблему. Кроме того, рассмотрите возможность избавления от столбцов формул или ссылок на ячейки, которые могут больше не понадобиться.
  • Размер листа: по мере увеличения листа производительность может снижаться. При необходимости переместите строки (подробнее о перемещении строк) на лист архива.

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

  Причина

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

  Разрешение

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

  Причина

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

  Разрешение

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

  Причина

  Формула находится в столбце определенного типа (дата, число, символ), а возвращаемое значение имеет другой тип.

  Разрешение

  Либо переместите формулу в другой столбец, либо преобразуйте результат в соответствующий тип. Результаты формулы могут быть преобразованы в текстовые значения путем добавления пустой строки. Например, =TODAY() + "" позволит вам ввести сегодняшнюю дату в текстовую колонку.

  Причина

  Ваша формула пытается разделить сумму на ноль (например, 4/0 дает ошибку деления на ноль).

  Разрешение

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

  =ЕСЛИОШИБКА(Значение4 <> 0, 100/Значение4, "")

  Причина

  Скобки в вашей формуле не совпадают. Количество открытых скобок не совпадает с количеством закрытых скобок.

  Разрешение

  Проверьте формулу на наличие лишних скобок — добавьте дополнительные скобки или удалите их по мере необходимости.

  Причина

  Эта ошибка появляется при следующих обстоятельствах:

  1. Для функций, которые принимают два диапазона: размеры диапазонов не соответствуют функции.
  2. У функции отсутствует аргумент.
  3. В аргументе есть дополнительная функция.

  Разрешение

  Исправьте размер диапазона или аргументы, добавив или удалив аргументы в формуле.

  Причина

  Формула содержит или ссылается на тип данных, несовместимый с типом столбца, в который она вставлена. Например, эта формула MAX помещается в столбец «Текст/Число» и ссылается на другие значения в столбце «Дата».

  
  =MAX([Срок выполнения]1, [Срок выполнения]52)

  <р>
  . Если столбец, содержащий формулу, не относится к типу столбца «Дата», самая поздняя дата в диапазоне не может быть возвращена. (Дополнительную информацию о работе функции MAX см. в справочной статье функции MAX.)

  Разрешение

  Внесите в лист одно из следующих изменений:

  Причина

  Формула содержит или ссылается на несовместимый тип данных, например =INT("Hello")

  Разрешение

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

  Причина

  Имя ссылки на другой лист (в фигурных скобках) не существует как ссылка на другой лист.

  Разрешение

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

  Дополнительную информацию об этом процессе см. в разделе Формулы: справочные данные из других таблиц.

  Причина

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

  =IF(Quantity1 = = (больше или равно) комбинации операторов в неправильном порядке.

  Причина

  Формула содержит число вне диапазона, ожидаемого аргументом функции. Например, эта формула НАЙТИ, которая находит символ "H" в текстовой строке "Hello", имеет начальную позицию 100 символов.

  =НАЙТИ("H", "Привет", 100)

  <р>. Поскольку строка «Hello» состоит из 5 символов, функция FIND не может начать поиск символа «H» со 100 символов. Принимаются только числа 1–5.

  Разрешение

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

  Причина

  Вы вложили критерии в функцию СУММЕСЛИ или СЧЁТЕСЛИ.

  Разрешение

  Вместо этого используйте СУММЕСЛИМН или СЧЁТЕСЛИМН, так как вы можете включить несколько критериев без вложения.

  Причины

  • Функция ВПР не нашла результата.
  • В диапазоне нет числа, большего или равного значению search_value в функции ВПР.

  Разрешение

  Настройте search_value или lookup_table функции ВПР так, чтобы она находила результат. (Подробнее о ВПР.)

  Причина

  Формула находится в столбце определенного типа (дата, число, символ), а возвращаемое значение имеет другой тип.

  Разрешение

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

  =TODAY() + "" позволит вам ввести сегодняшнюю дату в текстовую колонку.

  Причина

  Формула обнаружила или вычислила значение, превышающее поддерживаемый числовой диапазон. Для числовых значений диапазон составляет от -9007199254740992 до 9007199254740992. Для расчетов РАБДЕНЬ максимальное количество дней составляет один миллион.

  Разрешение

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

  Причина

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

  Разрешение

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

  Причина

  Формула ссылается на несуществующую ячейку, возможно, из-за удаления строки или столбца.

  Разрешение

  Ваши формулы необходимо обновить вручную.

  Причина

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

  Разрешение

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

  Причина

  Функции CHILDREN, PARENT или ANCESTORS ссылаются на данные из другого листа (в фигурных скобках), что не поддерживается.

  Разрешение

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

  Читайте также:

    
  • Как убрать блики в фотошопе
    
  • Как удалить модуль в vba excel
    
  • Как изменить позицию в Outlook
    
  • Как сделать кнопку очистки в Excel
    
  • Бесплатные ру программы для windows 7