Как проверить формулу в excel: Поиск ошибок в формулах — Служба поддержки Майкрософт

Содержание

Руководство по проверке данных Excel

Содержание

Вступление
Контроль достоверности данных
Важное ограничение
Определение правил проверки данных
Параметры проверки данных
Простое выпадающее меню
Проверка данных с помощью пользовательской формулы
Формулы устранения неполадок
Примеры формул проверки данных
Проверка данных, чтобы обвести недействительные записи
Найти ячейки с проверкой данных
Скопировать данные проверки из одной ячейки в другую
Очистить все данные проверки

Вступление

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

значение является числом от 1 до 6
дата произойдет в следующие 30 дней
текстовая запись содержит менее 25 символов

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

Сообщение отображается автоматически при выборе ячейки

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

Пример сообщения об ошибке

Кроме того, проверка данных может использоваться для предоставления пользователю определенного выбора в раскрывающемся меню:

Пример раскрывающегося меню проверки данных

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

Контроль достоверности данных

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

Элементы управления проверкой данных на вкладке ДАННЫЕ

Важное ограничение

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

Определение правил проверки данных

Проверка данных определяется в окне с 3 вкладками: Параметры, Сообщение для ввода и Сообщение об ошибке:

Окно проверки данных имеет три основные вкладки

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

Пример вкладки настроек проверки данных

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

Вкладка настройки сообщения проверки данных

Вкладка «Сообщение об ошибке» определяет, как выполняется проверка. Например, когда вид установлен на «Останов», неверные данные вызывают окно с сообщением, и ввод не разрешен.

Вкладка предупреждения об ошибке проверки данных

Пользователь видит сообщение, подобное этому:

Пример сообщения об ошибке проверки данных

Когда в поле «Вид» установлено значение «Сообщение» или «Предупреждение», изменяется значок, отображаемый с пользовательским сообщением. Пользователь может игнорировать сообщение и вводить значения, которые не проходят проверку. Ниже обобщено поведение каждого вида предупреждения об ошибке.

Вид: Останов
Не позволяет пользователям вводить недопустимые данные в ячейку. Пользователи могут повторить попытку, но должны ввести значение, которое проходит проверку данных. В окне предупреждения «Останов» есть три опции: «Повторить», «Отмена» и «Справка».

Вид: Предупреждение
Предупреждает пользователей о том, что данные неверны. Предупреждение ничего не делает, чтобы остановить ввод неверных данных. В окне «Предупреждение» есть три параметра: «Да» (для принятия недействительных данных), «Нет» (для редактирования недействительных данных), «Отмена» (для удаления недействительных данных) и «Справка».

Вид: Сообщение
Сообщает пользователям, что данные являются недействительными. Это сообщение не делает ничего, чтобы остановить ввод неверных данных. Информационное окно имеет 3 кнопки: «ОК», чтобы принять недействительные данные, «Отмена», чтобы удалить их и «Справка».

Параметры проверки данных

При создании правила проверки данных доступно восемь параметров:

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

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

Действительное — работает как опция целого числа, но допускает десятичные значения. Например, если для параметра «Действительное» задано значение от 0 до 3, допустимы все значения, такие как 0,5 и 2,5.

Список — разрешены только значения из предварительно определенного списка. Значения представляются пользователю как выпадающее меню. Допустимые значения могут быть жестко заданы непосредственно на вкладке «Параметры» или указаны в виде диапазона на рабочем листе.

Дата — разрешены только даты. Например, вам может потребоваться дата между 1 января 2018 года и 31 декабря 2021 года или дата после 1 июня 2018 года.

Время — разрешено только время. Например, вы можете указать время между 9:00 и 17:00 или разрешить время только после 12:00.

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

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

На вкладке параметров также есть два флажка:

Игнорировать пустые ячейки — говорит Excel не проверять ячейки, которые не содержат значений. На практике этот параметр влияет только на команду «Обвести неверные данные». Когда эта опция включена, пустые ячейки не обведены, даже если они не прошли проверку.

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

Простое выпадающее меню

Вы можете предоставить пользователю раскрывающееся меню опций, жестко закодировав значения в поле настроек или выбрав диапазон на листе. Например, чтобы ограничить записи действиями «ПРИНЯТ», «В ОБРАБОТКЕ» или «ОТГРУЖЕН», вы можете ввести эти значения через точку с запятой:

Раскрывающееся меню проверки данных с жестко заданными значениями

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

Используются жестко заданные значения
выпадающего меню проверки данных

Другой способ ввода значений в раскрывающееся меню — использование ссылки на рабочий лист. Например, с размерами (то есть маленький, средний и т.д.) в диапазоне F4:F6. Вы можете указать этот диапазон непосредственно в окне настроек проверки данных:

Значения выпадающего меню проверки
данных со ссылкой на диапазон

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

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

Вы также можете использовать именованные диапазоны для указания значений. Например, с именованным диапазоном под названием «размер» для F4:F6, вы можете ввести имя непосредственно в окне, начиная со знака равенства:

Значения выпадающего меню проверки
данных с именованным диапазоном

Именованные диапазоны автоматически являются абсолютными, поэтому они не изменятся.

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

Проверка данных с помощью пользовательской формулы

Формулы проверки данных должны быть логическими формулами, которые возвращают ИСТИНА, если ввод действителен, и ЛОЖЬ, если ввод недействителен. Например, чтобы разрешить ввод любого числа в ячейку A1, вы можете использовать функцию ЕЧИСЛО (ISNUMBER) в формуле, подобной этой:

= ЕЧИСЛО (А1)

Если пользователь вводит значение 10 в A1, ЕЧИСЛО (ISNUMBER) возвращает ИСТИНА, и проверка данных завершается успешно. Если вводится значение типа «яблоко» в A1, ЕЧИСЛО (ISNUMBER) возвращает ЛОЖЬ, и проверка данных завершается неудачно.

Чтобы включить проверку данных с помощью формулы, выберите «Другой» на вкладке «Параметры», затем введите формулу, начиная со знака равенства (=), как обычно.

Формулы устранения неполадок

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

Проверка достоверности данных
с помощью фиктивных формул

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

Примеры формул проверки данных

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

Чтобы разрешить только 5 символьных значений, начинающихся с «z», вы можете использовать:

= И (ЛЕВСИМВ (А1) = «z»; ДЛСТР (A1) = 5)

Эта формула возвращает ИСТИНА только тогда, когда код длиной 5 цифр и начинается с «z». Два значения в примере выше возвращают ЛОЖЬ с этой формулой.

Чтобы разрешить ввод даты в течение 30 дней с сегодняшнего дня:

= И (А1> СЕГОДНЯ (), А1 <= (СЕГОДНЯ () + 30))

Чтобы разрешить только уникальные значения:

= СЧЁТЕСЛИ (диапазон, А1) <2

Разрешить только адрес электронной почты

= ЕЧИСЛО (НАЙТИ ( «@», A1)

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

После проверки данных можно попросить Excel обвести ранее введенные недействительные значения. На вкладке «Данные» на ленте нажмите «Проверка данных» и выберите «Обвести неверные данные»:

Обведите недействительные значения с проверкой данных

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

= И (ЛЕВСИМВ (А1) = «z», ДЛСТР (A1) = 5)

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

Найти ячейки с проверкой данных

Чтобы найти ячейки с примененной проверкой данных, используйте диалоговое окно Переход — Выделить. Введите сочетание клавиш Ctrl + G, затем нажмите кнопку «Выделить». Когда появится окно, выберите «Проверка данных»:

Переход к кнопке Выделить Выберите проверку данных

Скопировать данные проверки из одной ячейки в другую

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

Использование специальной вставки
для копирования проверки данных

Примечание: вы можете использовать сочетание клавиш Ctrl + Alt + V, чтобы вызвать Специальную вставку без мыши.

Очистить все данные проверки

Чтобы очистить все данные проверки из диапазона ячеек:

выделите ячейки
нажмите кнопку «Проверка данных» на вкладке «Данные»
нажмите кнопку «Очистить все»

Очистить данные проверки

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

Как найти ошибку в таблице Excel по формуле

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

Поиск ошибок в Excel формулой

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

Для определения локализации первой ошибки на листе относительно строк следует использовать следующую формулу:

Данная формула должна выполняться в массиве, поэтом после ее ввода для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки, как на рисунке.

Таблица с большим объемом данных содержит ошибки, первая из которых находится в диапазоне третей строки листа 3:3.

Как получить адрес ячейки с ошибкой

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

Данная формула так же должна выполняться в массиве, поэтом после ее ввода снова для подтверждения жмем комбинацию клавиш CTRL+SHIFT+Enter.

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

Принцип действия формулы для поиска ошибок:

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

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

Скачать пример поиска ошибок в формулах Excel

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

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

Как только вы наберете формулу в Excel и нажмете Enter, она вернет вычисленный результат, а формула исчезнет.

Вот как это должно работать.

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

В этом руководстве по Excel я рассмотрю следующие темы:

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

Показать формулы в Excel вместо значений

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

Щелкните вкладку «Формулы» на ленте.
В группе «Аудит формул» щелкните параметр «Показать формулы».

Как только вы нажмете «Показать формулы», формулы на листе станут видимыми. Это кнопка-переключатель, поэтому вы можете нажать на нее еще раз, чтобы формулы были заменены ее вычисленным результатом.

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

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

Вы также можете использовать сочетание клавиш Excel – Control + ` (эта клавиша находится в верхней левой части клавиатуры под клавишей Escape).

Примечание. Это метод на уровне листа. Это означает, что при использовании параметра «Показать формулы» или ярлыка будут отображаться только формулы на активном листе. Все остальные рабочие листы не будут затронуты. Чтобы отобразить формулы на других листах, вам нужно перейти на этот лист и использовать этот ярлык (или кнопку на ленте).

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

Вот шаги, которые отобразят формулы на всех листах в Excel:

Перейдите на вкладку «Файл». Если вы используете Excel 2007, перейдите к кнопке Office.
Нажмите «Параметры».
На левой панели выберите Дополнительно.
Справа прокрутите вниз до раздела «Параметры отображения для этого рабочего листа».
В раскрывающемся списке выберите рабочий лист, на котором вы хотите отображать формулы вместо значений.
Установите флажок «Показывать формулы в ячейках вместо результатов их вычислений».
Нажмите OK.

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

Как печатать формулы в Excel

Вот шаги для печати формул в Excel:

Перейдите на вкладку Формула.
Нажмите кнопку «Показать формулу».
Выберите «Файл» -> «Печать».

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

Показывать формулы в Excel вместо значения только в выбранных ячейках

Описанные выше методы отображали все формулы на листе.

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

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

Вот шаги для отображения формул в Excel только в выбранных ячейках:

Выберите ячейку, в которой вы хотите показать формулу вместо значения.
Перейти на главную страницу -> Найти и выбрать -> Заменить (сочетание клавиш — Control + H).
В диалоговом окне «Найти и заменить» на вкладке замены введите = в поле «Найти» и «=» в поле «Заменить на».
Нажмите «Заменить все».

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

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

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

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

Есть несколько причин, по которым это может произойти:

Включен режим «Показать формулы» или вы случайно нажали сочетание клавиш Control + `. Чтобы отключить его, просто снова используйте ярлык или нажмите «Показать формулу» на вкладке «Формулы».
Это может быть связано с наличием пробела или апострофа перед знаком равенства в формуле. Наличие их перед знаком равенства делает формат ячейки текстовым, и формула отображается вместо значения. Чтобы справиться с этим, просто удалите их. Вы можете использовать найти и заменить, чтобы сделать это.
Если к ячейке применено форматирование «Текст», и вы вводите формулу и нажимаете «Ввод», она по-прежнему будет отображать формулу вместо вычисленного значения. Чтобы решить эту проблему, перейдите на вкладку «Главная» и в группе «Число» измените форматирование на «Общее».

Вам также могут понравиться следующие учебные пособия по Excel:

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

Проверка и оценка ошибок в формулах Excel 2010

Dummies

Исследовать книгу Купить на Amazon

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

 =ЕСЛИ(МАКС(Заказы!B2:B29)>200,МАКС(Заказы!B2:B29)*Доставка!C22,Доставка!C24)

Вычисление формул

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

Выберите ячейку, содержащую формулу, и нажмите кнопку «Вычислить формулу» на вкладке «Формулы» ленты.
Появится диалоговое окно «Вычислить формулу».
Использование диалогового окна «Вычислить формулу» для оценки формулы.
Нажмите кнопку Оценить один или несколько раз, чтобы оценить каждое выражение в формуле.
При каждом последующем нажатии кнопки Оценить в диалоговом окне Формула оценки отображаются промежуточные результаты. Кнопки Step In и Step Out активируются во время шагов, которые работают с прецедентами.

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

Проверка ошибок

Аналогичным средством поиска ошибок является диалоговое окно «Проверка ошибок». Чтобы использовать этот инструмент, выполните следующие действия:

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