Меню

Проверка таблицы excel на ошибки

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

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

однокомнатные квартиры посуточно в томске

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

Когда проверка ошибок включена, Excel постоянно оценивает таблицы, в том числе и содержащиеся в них формулы. Если возможная ошибка определена, то Excel добавит маленький треугольник в верхнем левом углу ячейки. Когда ячейка активизируется, появится смарт-тег. Щелчок на нем предоставит вам несколько команд на выбор. На рис. 195.2 показаны команды, которые появляются при выборе смарт-тега в ячейке, содержащей ошибку #ДЕЛ/0!. Команды различаются в зависимости от типа ошибки.

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

Рис. 195.2. Щелчок на смарт-теге ошибки дает вам список команд

Рис. 195.2. Щелчок на смарт-теге ошибки дает вам список команд

Даже если вы не используете функцию автоматической проверки ошибок, вы можете выбрать команду Формулы ► Зависимости формул ► Проверка наличия ошибок, чтобы открыть диалоговое окно, которое последовательно показывает каждую возможную ошибку ячейки, что во многом схоже с использованием функции проверки орфографии. На рис. 195.3 вы можете видеть диалоговое окно Контроль ошибок. Обратите внимание, что это немодальное диалоговое окно, и у вас остается доступ к листу, когда оно открыто.

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

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

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

Источник

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

Как проверить ячейки на ошибки с помощью функции ЕОШ в Excel

Функция ЕОШ часто используется для предотвращения возникновения ошибок в ячейках Excel. Рассматриваемая функция возвращает логическое ИСТИНА, если ее аргумент ссылается на ячейку, содержащую один из перечисленных кодов ошибки: #ССЫЛКА!, #ЧИСЛО!, #ЗНАЧ!, #ПУСТО!, #ИМЯ?, #ДЕЛ/0!. Аналогичный результат будет возвращен, если функция используется для проверки выражения или результата вычислений другой функции, указанных в качестве аргумента рассматриваемой функции, например =ЕОШ(5/0) (ошибка #ДЕЛ/0!) или =ЕОШ(ОСПЛТ(0,12;10;5;1000)) (ошибка #ЧИСЛО!, так как номер периода выплат не находится в диапазоне допустимых значений – 10>5).

В остальных случаях результат выполнения функции ЕОШ – значение ЛОЖЬ. Это касается также ошибки #Н/Д. То есть, результат выполнения формулы =ЕОШ(#Н/Д) – возвращает значение ЛОЖЬ.

Примечание:

В Excel начиная с версии 2007 года и выше была добавлена функция ЕСЛИОШИБКА, которая является более предпочтительной для использования, поскольку она нивелирует избыточность конструируемых выражений. Например, для проверки наличия ошибки в расчетах с помощью функции ЕОШ и устранения ее требуется конструкция ЕСЛИ(ЕОШ(проверяемое_значение);значение_если_ошибка_была_найдена;значение_без_изменений). С использованием функции ЕСЛИОШИБКА запись сокращается до =ЕСЛИОШИБКА(проверяемое_значение;значение_если_ошибка_была_найдена).



Примеры использования функции ЕОШ в формулах Excel

Пример 1. В таблице Excel содержатся данные о количестве посетителей сети магазинов по городам и среднем числе клиентов в сутки. Определить среднюю сумму в чеке на одного клиента. В некоторых городах магазины данного бренда еще не открылись, поэтому данные могут отсутствовать. А в результате вычислений формул весь отчет получился запачканным ошибками: #ДЕЛ/0!

Вид таблицы данных:

Пример 1.

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

Если проверяемое значение функцией ЕСЛИ (результат выполнения функции ЕОШ, то есть проверка частного от деления C3/B3) возвращает ИСТИНА (ошибка была найдена), будет возвращено значение 0, иначе – результат деления C3/B3. Вычислим значение для города Москва и «растянем» формулу для определения результатов по другим городам. Получим:

ЕОШ.

Вместо ошибок #ДЕЛ/0! Выводится значение 0,00.

Как заменить ошибки в ячейках Excel текстом

Пример 2. Для поиска в базе данных используется функция БИЗВЛЕЧЬ, которая может вернуть как минимум два кода ошибок: #ЗНАЧ!, если поиск не дал результатов, и #ЧИСЛО!, если количество совпадений равно 2 и более. Заменить коды ошибок на более понятное для рядового пользователя пояснение.

Вид таблицы данных:

Пример 2.

В данной таблице (оформлена как БД в Excel) хранятся данные о телевизорах на складе. Например, чтобы найти цену телевизора LG с диагональю 21 введем функцию:

найти цену в бд.

Чтобы получать разъяснение ошибок при поиске несуществующих товаров или сразу нескольких единиц (например, в таблице есть несколько телевизоров с диагональю 21) используем следующую запись:

В результате поиска, например, телевизора Philips (несуществующая позиция в данной БД) получим следующее сообщение:

заменить ошибки текстом.

Привила использования функции ЕОШ в формулах Excel

Функция имеет следующую синтаксическую запись:

=ЕОШ(значение)

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

Примечания:

  1. Функция не выполняет промежуточных преобразований типов данных для значений, принимаемых в качестве аргумента. Например, для текстовой строки со значением «#ЧИСЛО!» ошибкой она вернет значение ЛОЖЬ, а для ячейки со значением #ЧИСЛО! – ИСТИНА.
  2. Сама по себе рассматриваемая функция используется на практике крайне редко. Ее удобно применять в комбинации с другими логическими функциями, чаще всего с функцией ЕСЛИ.
Источник

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

Совет: Кроме того, можно нажать клавишу F7.

Ниже описано, что происходит при вызове функции проверки орфографии.

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

  • Если выделено несколько ячеек, Excel проверяет орфографию только в этих ячейках.

  • Чтобы проверить орфографию в строке формул, выделите соответствующие слова.

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

Проверка орфографии при вводе

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

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

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

  1. Выберите Файл > Параметры.

  2. В категории Правописание нажмите кнопку Параметры автозамены и ознакомьтесь с ошибками, которые чаще всего возникают при вводе.

Примечание: Функция автозамены не работает с текстом в диалоговых окнах.

Дополнительные ресурсы

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

Параметры проверки орфографии, тезауруса и перевода

  1. На вкладке Обзор нажмите Правописание или нажмите F7 на клавиатуре.

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

  2. Выполните любое из описанных ниже действий.

    Задача

    Необходимые действия

    Замена слова

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

    Изменение каждого экземпляра этого слова в данном документе

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

    Пропуск слова и переход к следующему слову с ошибкой

    Нажмите Игнорировать.

    Пропуск каждого экземпляра этого слова в данном документе и переход к следующему слову с ошибкой

    Нажмите Пропустить все.

Проверка орфографии при вводе

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

Чтобы проверить орфографию любого текста на листе, нажмите Просмотр > Проверка > Правописание.

Источник

Перейти к содержимому

Когда я был начинающим пользователем программы «Excel», на заполнение небольшой таблички на 40-50 строчек у меня мог уйти целый день. Такое значительное время на внесение данных тратилось из-за того, что я старался набирать текст без ошибок и каждое сложное слово искал в интернете, параллельно пытаясь вспоминать правила пунктуации.
Естественно это не нравилось моему руководителю и раздражало окружающих.

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

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

 Давайте рассмотрим, как можно проверить правописание в таблицах «Excel».

  1. Для начала следует набрать текст.
  2. Переключить панель быстрого доступа на вкладку «рецензирование».Правописание в excel орфография
  3. Выделить проверяемый диапазон с текстом. 
  4. Нажать кнопку «Орфография».

В появившемся окне проверки орфографии вы увидите сомнительное слово (1), предложения по его замене (2), язык словаря (3) и соответствующие кнопки с действиями: «заменить», «пропустить», «добавить в словарь», «автозамена» и т.д.Правописание в excel №2 орфография в словаре
Во вкладке «параметры» можно корректировать параметры проверки текста на ошибки.
Удачи в изучении «Эксель». Вопросы можете оставлять в комментариях или на форуме сайта.

Источник

В эксель проверка данных

Проверка данных в Excel – для тех, кто ценит свое время

Привет всем. Как часто Вы сталкиваетесь с таблицами общего пользования, которые заполняют несколько сотрудников? Например, Вы формируете базу данных в Эксель, и всю информацию вносит несколько человек. Ошибок не избежать, некорректные данные часто проскакивают в таблицы. Вот если бы Excel мог проверить их перед внесением и указать на ошибку…

А он может! В программу встроен мощный инструмент под названием «Проверка данных», который минимизирует ошибки внесения информации.

Как сделать проверку данных в Excel

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

Чтобы подключить инструмент «Проверка данных», выделите проверяемые ячейки и кликните на ленте « Данные – Работа с данными – Проверка данных ». Откроется окно настройки «модератора»:

На вкладке «Параметры» нужно задать условие для проверки в поле «Тип данных». Есть 8 типов условий для проверки:

  1. Любое значение – проверка данных отключена
  2. Целое число – можно ввести лишь целое число с дополнительным критерием;
  3. Действительное число – любое действительное число с дополнительным критерием
  4. Список – выбрать значение из заранее заданного списка
  5. Дата – ввести только дату, удовлетворяющую дополнительному критерию
  6. Время – ввести время с дополнительным условием
  7. Длина текста – ввести текст определенной длины
  8. Другой – ввести значение, которое удовлетворяет записанному вручную логическому выражению

В этом окне можно установить еще две галки:

  • Игнорировать пустые ячейки – не применять условие к пустым ячейкам
  • Распространить изменения на другие ячейки с тем же условием – найти ячейки с аналогичными условиями и применить к ним такие же изменения

На вкладке «Сообщение для ввода» введите подсказку о том, какие данные нужно внести в ячейку.

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

  1. Останов – отменить внесение ошибочных данных, вывести заданное сообщение
  2. Предупреждение – программа сообщит, что значение не соответствует требованиям и уточнит, вносить ли его в ячейку
  3. Сообщение – примерно то же, что и предупреждение, но выглядит менее устрашающе

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

А теперь поговорим детальнее о каждом из видов условий.

Условие на целое число в Excel

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

  • Между минимальным и максимальным значением
  • Вне заданного промежутка значений
  • Равно определенному значению
  • Не равно определенному значению
  • Больше заданного значения
  • Меньше заданного значения
  • Больше или равно значению
  • Меньше или равно значению

Условие на действительное значение

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

Список

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

Чтобы задать список, опишите его в поле источник. Это можно сделать несколькими способами:

  • Записать перечень в ячейках на листе книги, а в поле «Источник» указать ссылку на этот массив
  • Создать именованный массив, в поле «Источник» указать его имя
  • Перечислить пункты прямо в поле «Источник» через точку с запятой

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

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

Время

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

Длина текста

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

Другой

Этот способ проверки самый гибкий. В строке «Формула» Можно задать свою логическую формулу для проверки, если ни один из предыдущих способов проверки Вас не устраивает. Например, чтобы в ячейку А1 вносили только коды, начинающиеся с символов “SUV”, и содержащие 12 символов, внесем в поле формулу: =И(ЛЕВСИМВ(A1;3)=»SUV»;ДЛСТР(A1)=12) . Лишь при выполнении этого правила значение будет считаться верным.

Напомню, функция ЛЕВСИМВ возвращает заданное количество символов слева направо, а ДЛСТР – считает количество символов в ячейке.

Как проверить ячейки Excel на соблюдение условий

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

Чтобы убрать выделения без исправления, в этом же меню выберите «Удалить обводку неверных данных».

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

Понравилась статья? Считаете ее полезной? Поделитесь с другом – кликните на кнопках социальных сетей, или просто перешлите ссылку!

Я же отправляюсь писать новый важный пост, на этот раз про создание структуры таблицы. Не пропустите, особенно если Ваши таблицы большие и сложные. До встречи!

Источник: officelegko.com

Проверка данных Excel

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

  • 01.01.2001;
  • 01/01/2001;
  • 1 января 2001 года и т.д.

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

Где находится?

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

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

Настройка условия проверки

Изначально требуется выбрать тип проверяемых данных, что будет являться первым условием. Всего предоставлено 8 вариантов:

  • Целое число;
  • Действительное число;
  • Список;
  • Дата;
  • Время;
  • Длина текста;
  • Другой.

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

Самым необычным видом является выпадающий список.

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

Всплывающая подсказка ячейки Excel

Функционал проверки данных в Excel позволяет настраивать всплывающие подсказки для ячеек листа. Для этого следует перейти на вторую вкладку окна проверки вводимых значений – «Сообщение для ввода».

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

Пример всплывающей подсказки в Excel:

Вывод сообщения об ошибке

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

Существует три варианта сообщений, отличающихся по поведению:

Останов является сообщением об ошибке и позволяет произвести только 2 действия: отменить ввод и повторить ввод. В случае отмены новое значение будет изменено на предыдущее. Повтор ввода дает возможность скорректировать новое значение.

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

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

Заголовок и сообщение заполняются по Вашему желанию.

Пример вывода одной и той же ошибки, но под разными видами:

Источник: office-menu.ru

Проверка данных в Excel

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

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

В этом примере мы сделаем такое ограничение, при котором пользователи смогут вводить только целое число между 0 и 10.

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

Чтобы создать правило проверки данных, следуйте нашей инструкции:

  1. Выделите ячейку С2.
  2. На вкладке Данные (Data) нажмите кнопку Проверка данных (Data Validation).

На вкладке Параметры (Settings) диалогового окна Проверка вводимых значений (Data Validation) сделайте следующее:

  • Из выпадающего списка Тип данных (Allow) выберите Целое число (Whole number).
  • Из выпадающего списка Значение (Data) выберите Между (Between).
  • Введите минимальное и максимальное значения.

Сообщение для ввода

Сообщения для ввода появляются, когда пользователь выделяет ячейку. Они указывают ему, что нужно вводить. Перейдите на вкладку Сообщение для ввода (Input Message) и сделайте следующее:

  1. Поставьте галочку напротив Отображать подсказку, если ячейка является текущей (Show input message when cell is selected).
  2. Введите заголовок.
  3. Введите само сообщение.

Сообщение об ошибке

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

  1. Поставьте галочку напротив параметра Выводить сообщение об ошибке (Show error alert after invalid data is entered).
  2. Введите заголовок.
  3. Введите сообщение об ошибке.

  • Нажмите ОК.

    • Результат проверки данных

    1. Выделите ячейку С2.


    Попробуйте ввести число больше, чем 10.

    Примечание: Чтобы удалить проверку данных из ячейки, выделите её и на вкладке Данные (Data) нажмите кнопку Проверка данных (Data Validation). Затем кликните по Очистить все (Clear All). Чтобы быстро выбрать все ячейки с проверкой данных, используйте инструмент Выделение группы ячеек (Go To Special).

    Источник: office-guru.ru

    Проверка вводимых данных в ячейки Excel

    Допустим, что в таблице прайс-листа с розничными ценами на товары, в одном из столбцов указана процентная ставка НДС. Как часто бывает в рутинной работе срабатывает человеческий фактор и по ошибке для одной из категорий товаров, вместо ставки НДС 20% была введена старая ставка 19%. Эта небольшая разница в данных – 1% может создать большие проблемы для фирмы с всевозможными последствиями. Чтобы исключить ошибки созданных по причине человеческого фактора, воспользуемся встроенным инструментом Excel для проверки данных, который позволяет контролировать все что вводиться на рабочий лист.

    Как в Excel сделать проверку данных в ячейках

    Пример прайс-листа с введенными ошибками в процентных ставках НДС:

    Чтобы в Excel сделать проверку вводимых данных в ячейки следует выполнить ряд последовательных действий:

    1. Выделите диапазон ячеек где вводиться формула с процентной ставкой для цен с НДС и выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
    2. В появившемся окне «Проверка вводимых значений» на вкладке «Параметры» из выпадающего списка «Тип данных:» выберите опцию «Список».
    3. В полю ввода «Источник:» введите значения разных процентных ставок: 0%; 20%; освобождается.
    4. Перейдите на закладку «Сообщение об ошибке» и заполните текстовое поле «Сообщение:» текстом который будет содержать сообщение при вводе других значений, которые отличаются от указанных значений в списке.

    Закладка «Сообщение об ошибке» предоставляет пользователю возможность оформить стиль сообщения об ошибочных вводах значений. Если пользователь вводить в ячейку неправильное значение тогда будет выполнен один из 3-х параметров:

    1. Останов – данный параметр разрешает вводить только правильное значение, выбранного из выпадающего списка или отменить ввод оставив пустую ячейку.
    2. Предупреждение – этот параметр предупреждает об вводе ошибочного значения и предоставляет попытку отредактировать или выбрать из выпадающего списка.
    3. Сообщение – параметр сообщает о неправильном значении и позволяет проигнорировать ошибку.

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

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

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

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

    Источник: exceltable.com

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

    Очень часто при вводе данных в ячейки электронной таблицы мы совершаем ошибки.

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

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

    Выберите инструмент “Проверка данных” на панели “Работа с данными” ленты “Данные”. Из выпадающего списка выберите значение “Проверка данных..”.

    В появившемся окне “Проверка вводимых значений” на вкладке “Параметры” задайте условия проверки.

    На вкладке “Сообщение для ввода” введите текстовые значения, которые будут показаны пользователю, когда ячейка выбрана.

    На вкладке “Сообщение об ошибке” введите текстовые значения, которые будут показаны пользователю, когда в ячейку введено ошибочное значение.

    Вот как это будет выглядеть в процессе работы.

    Если в окне “Проверка вводимых значений” на вкладке “Параметры” установить в поле “Тип данных” значение “Список”, то можно упростить ввод повторяющихся данных.

    Для этого надо предварительно ввести все допустимые значения из списка. Затем в поле “Источник” указать диапазон ячеек, где расположены допустимые списочные значения:

    • нажмите изображение таблицы, расположенной справа поля;
    • выделите диапазон ячеек;
    • повторно нажмите на изображение таблицы.

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

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

    В начало страницы

    В начало страницы

    Источник: on-line-teaching.com

    Трюк №16. Проверка данных на основе списка на другом листе Excel

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

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

    Способ 1. Именованные диапазоны

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

    Выделите ячейку, в которой должен будет появиться раскрывающийся список, а затем выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите =MyRange . Щелкните на кнопке ОК. Поскольку вы использовали именованный диапазон, ваш список (хотя он и находится на другом листе) теперь можно использовать как список проверки.

    Способ 2. Функция ДВССЫЛ

    Функция ДВССЫЛ (INDIRECT) позволяет ссылаться на ячейку, содержащую текст, представляющий адрес ячейки. Эту ячейку можно использовать как локальную ссылку, даже если она получает данные из другого листа. Можно применять эту возможность для связи с листом, где расположен список.

    Предположим, список находится на листе Sheetl в диапазоне $А$1:$А$8 . Щелкните любую ячейку на другом листе, где должен появиться этот список проверки (список выборки). Затем выберите команду Данные → Проверка (Data → Validation) и в поле Тип данных (Allow) выберите пункт Список (List). В поле Источник (Source) введите следующий код: =INDIRECT(“Sheetl!$А$1:$А$8”) , в русской версии Excel =ДВССЫЛ(“Sheetl!$A$1:$A$8”) . Удостоверьтесь, что флажок Список допустимых значений (In-Cell) установлен, и щелкните на кнопке ОК. Список на листе Sheetl должен появиться в раскрывающемся списке проверки.

    Если имя листа, на котором расположен список, содержит пробелы, необходимо использовать следующий синтаксис функции ДВССЫЛ (INDIRECT): =INDIRECT(“‘Sheetl’!$А$1:$А$8”) , в русской версии Excel =ДВССЫЛ(“‘Sheetl’!$А$1:$А$8”) . Различие заключается в том, что здесь после первой кавычки стоит один апостроф, а второй апостроф находится перед восклицательным знаком.

    Преимущества и недостатки обоих способов

    У именованных диапазонов и функции ДВССЫЛ (INDIRECT) есть преимущества и недостатки. Преимущество использования именованного диапазона заключается в том, что изменение названия листа не повлияет на список проверки. Это подчеркивает недостаток функции ДВССЫЛ (INDIRECT): любое изменение названия листа не будет автоматически в ней отражаться. Преимущество функции ДВССЫЛ (INDIRECT): когда из именованного диапазона будет удалена первая ячейка или строка либо последняя ячейка или строка, то именованный диапазон вернет ошибку #REF! . В этом недостаток именованного диапазона — если удалить из него ячейки или строки, изменения не повлияют на список проверки.

    Источник: excel2010.ru

    Источник

    Ошибки пользователей при вводе данных – одна из самых распространенных проблем в Excel. В результате ошибочных данных ячейки с формулами на разных листах друг за другом могут заполниться ошибками #ЗНАЧ, #H/Д и т.п. Поиск ошибок может занимать длительное время, потому что непонятен первоначальный источник ошибки. С чего начинать?  Но появление ячеек с ошибками это даже хорошо. 

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

    НЕДОСТАТКИ СТАНДАРТНЫХ ИНСТРУМЕНТОВ EXCEL

    Как вы наверняка знаете, в Excel существует стандартный функционал для контроля вводимых в ячейки данных. Он устанавливается через меню Данные -> Проверка данных. В результате появляется диалоговое окно Проверка вводимых значений c тремя закладками.

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

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

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

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

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

    Например, если данные вводятся в ячейку с использованием команд Копировать->Вставить, Копировать->Специальная вставка-Значения или путем «растаскивания» от соседних ячеек, то проверка данных не осуществляется. Также значения в ячейках не контролируются, если они введены в ячейку с помощью макроса (если только контроль не осуществляется в самом макросе).

    Таким образом, неверные данные могут попасть в ячейки, многократно тиражированы и Excel это не отследит! 

    Часто это бывает, когда вы выгружаете данные в таблицы из других программ, например, 1С. Но особенно неприятно когда ошибки незаметны или малозаметны. Например:

    • Значение в ячейке выглядит как число и дата, а на самом деле оно является текстом. Например потому, что перед или после цифр есть пробел или другие непечатные (невидимые) символы.
    • Ячейка выглядит пустой, а на самом деле там есть непечатные символы.
    • В качестве разделителя целой и дробной части использована точка вместо запятой.
    • Введено значение, которое отсутствует в «привязанном» к ячейке выпадающем списке.

    Последняя ситуация достаточно распространена. Например, вы ведете реестр платежей от контрагентов, и вводите наименование контрагентов с помощью выпадающего списка, связанного со справочником контрагентов. Спустя некоторое время вы обнаруживаете в наименовании контрагента ошибку и исправляете ее в справочнике. При этом Excel никак не отреагирует на то, что в реестре есть записи со старым (ошибочным) наименованием контрагента. То есть Excel при изменении элементов списка не отлавливает уже введенные данные типа «Список». 

    2. Стандартные способы проверки могут «тормозить»

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

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

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

     3. Бесполезный инструмент «Обвести неверные данные»

    Для проверки правильности введенных значений в Excel есть встроенный инструмент «Обвести неверные данные», который вызывается через пункты меню — Данные -> Проверка данных -> Обвести неверные данные.

    Команда доступна только на незащищенном листе, а результатом ее работы будет обведение неверных по мнению Excel данных красным овалом как на рисунке выше. 

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

    • Будут признаны ошибочными и обведены пустые ячейки с типом «Список допустимых значение», если для них при настройке была снята галочка в поле «игнорировать пустые ячейки» (она снята в большинстве случаев). Но согласитесь — неверные значения и пустые значения это разные вещи. 
    • Помечаются только первые 255 недопустимых значения.
      Если в вашей таблице десятки колонок и тысячи строк, то придется много раз повторять одни и те же действия – исправлять ошибки и снова запускать обводку неверных данных. 
    • Она не всегда работает корректно. На вышеприведенном рисунке не были обведены значения в колонке ЦУ  — Объект «Сколково» — хотя эти значения ошибочные, т.к. их нет в списке. 

    4. «Подсвечивание» ячеек с ошибочными данными

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

    Я считаю это способ неэффективным. Ну  «подсветили» вы некорректные данные в таблице, а что дальше? 

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

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

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

    КАК ПРАВИЛЬНО ОРГАНИЗОВАТЬ ПРОВЕРКУ ВВЕДЕННЫХ ДАННЫХ

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

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

    Любую проверку вводимых значений можно делать и обычными функциями Excel, такими как ЕСЛИ, ЕТЕКСТ, ЕНЕТЕКСТ, ЕЧИСЛО, ПОИСКПОЗ. Поэтому в таблице можно создать служебную колонку  в ячейках которой задать формулу, которая будет проверять корректность введенных данных по заданным условиям во всех ячейках строки, предназначенных для ввода и сообщать об этом пользователю. Расчет формулы сделать включаемым/ выключаемым в зависимости от положения «тумблера». Что такое тумблеры и зачем они, написано здесь.

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

    Как это устроено проще всего понять это на примере (см. рисунок ниже) 

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

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

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

    Блоки могут быть такие:

    • Ячейки, в которых должна вводиться дата или число проверяется такой конструкцией

    ЕСЛИ(ЕТЕКСТ(проверяемое_значение);номер_столбца&».»;»») 

    • Ячейки, в которых значение вводится из выпадающего списка проверяется такой конструкцией: 

    EСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ(искомое_значение; просматриваемый_массив;0);-1)>0;””;номер_столбца) 

    • Если вам нужно проверять даты на вхождение в определенный период времени используйте обычные функции ЕСЛИ, >=,<= и т.д. Аналогично для чисел. 

    Не забудьте всю эту конструкцию поместить внутрь функции ЕСЛИ:     =ЕСЛИ(НаТ1;ваша_цепочка_блоков;””), где НаТ1 это имя ячейки-тумблера, которая включает/выключает расчет формулы.  Это позволит не вычислять формулу при всяком изменении в ячейках, а делать это по необходимости. 

    В результате в служебной колонке, в тех строках, где будут обнаружены ошибки, отобразятся номера колонок с некорректными данными, разделенные точками. В шапке таблицы можно подсчитать количество строк с ошибками следующей формулой:
    =ЧСТРОК(A$5:A$677)-СЧЁТЕСЛИ(A$5:A$677;»») , где A$5:A$677 – диапазон служебного столбца в котором выводятся признаки ошибок. 

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

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

    Все просто и удобно, никаких «тормозов» и экономия времени!

    Cookie-файлы

    Настройка cookie-файлов

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

    Аналитические Cookie-файлы
    Отключить все

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


    Подробнее

    Понятно

    Источник

    Проверка данных в MS EXCEL

    ​Смотрите также​ если кто подскажет​ и M.​ удобно и продуктивно​и​нажмите кнопку​Примечание:​Данные​ вычисляемый столбец, который​

    ​ значение 1 000 рублей,​ дата. Например, при​ на вкладке​ ячеек (Главная/ Найти​Второй недостаток: диапазон источника​ через Буфер обмена,​ Список, то сможем​ ссылку на саму​При вводе большого количества​ как это реализовать​Никанор​

    A. Проверка введенных значений

    ​ для данной задачи.​ВЫБОР​Окно контрольного значения​ Если вы используете функцию,​

    ​в группе​ уже содержит одно​ введите​ вводе выражения​Данные​

    • ​ и выделить/ Выделение​​ должен располагаться на​ например, вставляется значение​ сформировать связанные диапазоны.​ ячейку, к которой​
    • ​ информации в ячейки​​ в 2003 екселе.​: Здравствуйте!​ Особенно если нам​, не вычисляются. В​.​ убедитесь в том,​
    • ​Работа с данными​​ или несколько исключений.​1000​СУММ(A1:A10)​(Data) нажмите кнопку​ группы ячеек). Опция​ том же листе,​ из WORD. Чтобы​ Тема так обширна,​ применяется Проверка данных​
    • ​ таблицы легко допустить​​Убедительная просьба, ознакомится с​Что бы не​ нужно одновременно выполнить​ таких случаях в​Нажмите кнопку​ что имя функции​нажмите кнопку​Копирование в вычисляемый столбец​. Если вы введете​в Excel отображается​Проверка данных​ Проверка данных этого​ что и выпадающий​ убедиться, что данные​ что она выделена​ или ссылку на​ ошибку. В EXCEL​ документом прежде чем​ делать ошибки при​ проверку по нескольким​ поле Вычисление отображается​Добавить контрольное значение​ написано правильно. В​Проверка данных​ данных, не соответствующих​ какой-нибудь символ в​ текстовая строка​(Data Validation). Затем​

    • ​ инструмента позволяет выделить​​ список, т.к. для​ в ячейке не​ в отдельную статью​ зависящую от нее​ существует инструмент для​ что либо писать,​ работе с таблицей​ столбцам. В таком​ значение #Н/Д.​.​ этом случае функция​.​ формуле столбца. Если​ числе, Excel будет​СУММ(A1:A10)​ кликните по​ ячейки, для которых​ правил Проверки данных нельзя​ соответствуют условиям определенным​ Связанный список.​ ячейку. Например,​ проверки введенных данных​ я знаю что​ я сделал это​ случаи более заметна​Если ссылка пуста, в​Убедитесь, что вы выделили​ сумм написана неправильно.​Выберите лист, на котором​ копируемые данные содержат​ считать его разделителем.​
    • ​вместо результата вычисления,​​Очистить все​ проводится проверка допустимости​ использовать ссылки на​ в Проверке данных,​В EXCEL 2007 в Проверке​Чтобы​ сразу после нажатия​ инфы на эту​ не с помощью​ рациональность его использования.​ поле​ все ячейки, которые​ Удалите слова «e»​ требуется проверить наличие​ формулу, эта формула​ Если вам нужно,​ а при вводе​(Clear All). Чтобы​ данных (заданная с​ другие листы или​ нужно вызвать команду​ данных, как и​
    • ​Введите формулу​​ клавиши ENTER –​ тему более чем​ «Проверка данных», а​Никанор​Вычисление​ хотите отследить, и​ и Excel, чтобы​

    ​ ошибок.​ перезапишет данные в​ чтобы числа отображались​11/2​ быстро выбрать все​ помощью команды Данные/​ книги (это справедливо​ меню Обвести неверные​ в Условном форматировании​Пояснение​ Проверка данных.​ предостаточно, но там​ с помощью Условного​: Здравствуйте уважаемые помощники!​отображается нулевое значение​ нажмите кнопку​ исправить их.​Если расчет листа выполнен​ вычисляемом столбце.​

    ​ с разделителями тысяч​

    ​в Excel показывается​

    ​ ячейки с проверкой​

    ​ Работа с данными/​​ для EXCEL 2007​​ данные (Данные/ Работа​

    ​ нельзя впрямую указать​

    ​Ячейка​Инструмент Проверка данных (Данные/​ есть тонкости.​ форматирования следующим образом.​Алгоритм – последовательность​ (0).​Добавить​​Исправление ошибки #ПУСТО!​

    ​ вручную, нажмите клавишу​Перемещение или удаление ячейки​​ или символами валюты,​​ дата​ данных, используйте инструмент​ Проверка данных). При​​ и более ранних).​​ с данными/ Проверка​ ссылку на диапазоны​​B2​​ Работа с данными/​

    ​————————————————————————————————————————————————————————————————————​

    ​Таблица заполняется слева​ работы при заполнении​​Некоторые функции вычисляются заново​​.​Эта ошибка отображается в​​ F9, чтобы выполнить​​ из другой области​ отформатируйте ячейки после​​11.фев​​Выделение группы ячеек​ выборе переключателя Всех​Избавимся сначала от второго​ данных/ Обвести неверные​ другого листа, например,​содержала только текст​ Проверка данных) не​P.S.​ направо. Что бы​

    ​ таблицы.​ при каждом изменении​​Чтобы изменить ширину столбца,​​ Excel, когда вы​ расчет повторно.​ листа, если на​​ ввода чисел.​​(предполагается, что для​(Go To Special).​ будут выделены все​

    ​ недостатка – разместим​

    ​ данные).​ так =Лист2!$A$1. Позволяют​=ЕТЕКСТ(B2)​ был бы столь​Почему именно 2003?​ не пропустить заполнение​​1. Заполняется столбец​​ листа, так что​ перетащите правую границу​ указываете пересечение двух​Если диалоговое окно​ эту ячейку ссылалась​

    ​Например, если для прибавления​ ячейки задан формат​​Урок подготовлен для Вас​​ такие ячейки. При​

    ​ перечень элементов выпадающего​

    ​Если на листе много​

    ​ обойти это ограничение​

    ​В Типе данных нет​​ популярным, если бы​​ Все просто, там​ столбцов K, L,​ В – «Маршрут»;​ результаты в диалоговом​ его заголовка.​​ областей, которые не​​Поиск ошибок​ одна из строк​ 3100 к значению​​Общий​

    ​ командой сайта office-guru.ru​ выборе опции Этих​ списка на другом​​ ячеек с Проверкой​​ использование Имен.​ возможности выбрать тип​ его функциональность ограничивалась​ где я работаю,​ M я сделал​

    ​2. Если нет​

    ​ окне​Чтобы открыть ячейку, ссылка​​ пересекаются. Оператором пересечения​

    ​не отображается, щелкните​ в вычисляемом столбце.​ в ячейке A3​), а не результат​Источник: http://www.excel-easy.com/basics/data-validation.html​​ же выделяются только​​ листе.​

    ​ данных, то можно​Если в Проверке данных нужно​ Текст, поэтому приходится​ бы только собственно​ подавляющие большинство людей​ условное форматирование.​ груза – заполняется​Вычисление формулы​ на которую содержится​ является пробел, разделяющий​ вкладку​Ячейки, которые содержат годы,​ используется формула​ деления 11 на​Перевела: Ольга Гелих​ те ячейки, для​В правилах Проверки данных (также​ использовать инструмент Выделение​

    В. Отображение комментария, если ячейка является текущей.

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

    ​ (​Вот как теперь​ столбец К –​могут отличаться от​ в записи панели​ ссылки в формуле.​Формулы​ представленные 2 цифрами.​=СУММ(3 100;A3)​ 2.​Автор: Антон Андронов​

    С. Вывод подробного сообщения об ошибке.

    ​ которых установлены те​ как и Условного​ группы ячеек (Главная/​ на ячейку​ Вы можете Проверку​ перечислены основные приемы​OLD SCHOOL​ идёт заполнение таблицы​

    D. Создание связанных диапазонов (списков)

    ​ «Расстояние, км. без​ тех, которые отображаются​ инструментов «Окно контрольного​Примечание:​, выберите​ Ячейка содержит дату в​, Excel не складывает​Следите за соответствием открывающих​Примечание:​

    Е. Использование в правилах ссылок на другие листы

    ​ же правила проверки​ форматирования) нельзя впрямую​ Найти и выделить/​А1​ данных применить прямо​ работы с этим​) работает именно с​1. Заполняю столбец​ груза»;​

    ​ в ячейке. Это​ значения», дважды щелкните​ Убедитесь, что диапазоны правильно​​Зависимости формул​​ текстовом формате, которая​ 3100 и значение​ и закрывающих скобок​ Мы стараемся как можно​ данных, что и​ указать ссылку на​ Выделение группы ячеек).​

    ​другого листа, то​ к ячейке​ инструментом.​ 2003 и переход​ В «Маршрут». После​3. Если есть​ функции​ запись.​ разделяются друг от​и нажмите кнопку​ при использовании в​ в ячейке A3​Все скобки должны быть​ оперативнее обеспечивать вас​ для активной ячейки.​ диапазоны другого листа​Опция Проверка данных этого​ нужно сначала определить​B2​Как видно на рисунке​

    F. Как срабатывает Проверка данных

    ​ на новый к​ того как я​ груз – заполняется​СЛЧИС​Примечание:​​ друга (области C2):​​Поиск ошибок​ формулах может быть​ (как было бы​

    ​ парными (открывающая и​ актуальными справочными материалами​Примечание​ (см. Файл примера):​​ инструмента позволяет выделить​​ Имя для этой​Допустить ввод значения в​ снизу, условия проверки​ примеру 2007 -10​ написал маршрут и​ столбец I –​,​ Ячейки, содержащие внешние ссылки​ C3 и E4:​.​ отнесена к неправильному​ при использовании формулы​ закрывающая). Если в​ на вашем языке.​:​Пусть ячейки, которые должны​ ячейки, для которых​​ ячейки, а затем​​ ячейку​

    ​ вводимого значения можно​ влечет за собой​​ нажал на клавишу​​ «Название груза», столбец​ОБЛАСТИ​ на другие книги,​ E6 не пересекаются,​Если вы ранее не​ веку. Например, дата​=СУММ(3100;A3)​ формуле используется функция,​ Эта страница переведена​Если выпадающий список​ содержать Выпадающий список,​ проводится проверка допустимости​ сослаться на это​

    ​B1​​ настроить очень гибко.​​ для них катастрофические​​ Enter, в ячейке​​ L –«Расстояние, км.​​,​​ отображаются на панели​​ поэтому при вводе​​ проигнорировали какие-либо ошибки,​ в формуле =ГОД(«1.1.31»)​), а суммирует числа​ для ее правильной​ автоматически, поэтому ее​ содержит более 25-30​ размещены на листе​ данных (заданная с​ имя в правиле​только в случае,​Можно разрешить ввод значений​ последствия и не​ столбца K («Расстояние,​ с грузом» и​ИНДЕКС​ инструментов «Окно контрольного​ формулы​ вы можете снова​ может относиться как​ 3 и 100,​ работы важно, чтобы​​ текст может содержать​​ значений, то работать​​ Пример,​​ помощью команды Данные/​ Проверке данных.​​ если после ввода​​ в ячейку только​ стыковки в работе.​ км. Без груза»)​

    ​ столбец М –​,​ значения» только в​= Sum (C2: C3​ проверить их, выполнив​ к 1931, так​ после чего прибавляет​ все скобки стояли​ неточности и грамматические​ с ним становится​а диапазон с перечнем​ Работа с данными/​В Excel 2010, напротив, можно​ значение в ячейке​ определенного типа, выбрав​Прошу вашей помощи.​ Условным форматированием высвечивается​​ «Перевезено тонн»;​​СМЕЩ​ случае, если эти​ E4: E6)​ следующие действия: выберите​ и к 2031​ полученный результат к​ в правильных местах.​ ошибки. Для нас​ неудобно. Выпадающий список​ элементов разместим на​ Проверка данных). При​ использовать правила проверки​D1​ необходимое условие из​Sanja​ ячейка. В ячейке​Нужно сделать: что​,​

    G. Поиск ячеек с Проверкой данных

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

    ​ одновременно отображает только​ другом листе (на​ выборе переключателя Всех​ данных, ссылающиеся на​будет больше 100,​ выпадающего (раскрывающегося) списка:​: Если есть решение​ столбца K Условным​ бы появлялось Диалоговое​ЯЧЕЙКА​Удаление ячеек из окна​ ошибку. При помещении​>​ правило для выявления​ A3. Другой пример:​=ЕСЛИ(B5 не будет работать,​ статья была вам​ 8 элементов, а​

    excel2.ru

    Выпадающий список в MS EXCEL на основе Проверки данных

    ​ листе Список в​ будут выделены все​ значения на других​ в​Целое число​ для 2010-го в​ форматированием написана 1​ окно «Проверка вводимых​,​ контрольного значения​ запятые между диапазонами​Параметры​ дат в текстовом​ если ввести =ABS(-2​ поскольку в ней​ полезна. Просим вас​ чтобы увидеть остальные,​

    ​ файле примера).​ такие ячейки. При​ листах. В Excel​

    ​D2​. В ячейку разрешен​ чем трудность его​ формула:​ значений» → Вкладка​ДВССЫЛ​

    ​Если окно контрольного значения​ C и E​>​ формате, допускающих двоякое​ 134), Excel выведет​ две закрывающие скобки​ уделить пару секунд​

    ​ нужно пользоваться полосой​Для создания выпадающего списка,​

    А. Простейший выпадающий список — ввод элементов списка непосредственно в поле Источник

    ​ выборе опции Этих​ 2007 и Excel​меньше, чем 400​ ввод только целых​ применения в 2003-м?​=ЕСЛИ(B12<>»»;ЕПУСТО(I12);»»)​

    ​ «Данные» → «Проверка​​,​​ не отображается, на​ будут исправлены следующие​формулы​ толкование.​​ ошибку, так как​​ и только одна​ и сообщить, помогла​

    ​ прокрутки, что не​ элементы которого расположены​ же выделяются только​ 97-2003 проверка данных​=И(D1>100;D2​ чисел, причем принадлежащих​

    ​ Не можете найти​эта формула Условного​​ данных». И что​​ЧСТРОК​ вкладке​функции = Sum (C2:​. В Excel для​Числа, отформатированные как текст​ функция ABS принимает​

    ​ открывающая (требуется одна​​ ли она вам,​ всегда удобно.​ на другом листе,​ те ячейки, для​ этого типа не​​Проверку данных применяем к​​ определенному диапазону;​ где настраивается УФ?​ форматирования изменяет цвет​ бы останавливало и​,​
    ​Формула​    ​ C3, E4: E6).​

    Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

    ​ Mac в​ или с предшествующим​ только один аргумент:​ открывающая и одна​ с помощью кнопок​В EXCEL не предусмотрена​ можно использовать два​ которых установлены те​

    ​ поддерживается и не​ ячейке​Действительное​​Цитатаnikkotini пишет: Убедительная​​ ячейки и вторая​ не давало заполнять:​

    ​ЧИСЛСТОЛБ​​в группе​Исправление ошибки #ЧИСЛО!​меню Excel выберите Параметры​ апострофом.​=ABS(-2134)​
    ​ закрывающая). Правильный вариант​​ внизу страницы. Для​ регулировка размера шрифта​ подхода. Один основан​ же правила проверки​ отображается на листе.​B1​. В ячейку разрешен​ просьба, ознакомится с​​ формула в ячейке​​Для ячейки В13:​,​Зависимости формул​Эта ошибка отображается в​ > Поиск ошибок​ Ячейка содержит числа, хранящиеся​.​ этой формулы выглядит​ удобства также приводим​

    ​ Выпадающего списка. При​ на использовании Именованного​ данных, что и​ Однако все правила​. При этом в​ ввод только чисел,​ документом прежде чем​ столбца K Код=K12<>»»​ при условии, ЕСЛИ​ТДАТА​нажмите кнопку​

    ​ Excel, если формула​.​ как текст. Обычно​Вы можете использовать определенные​ так: =ЕСЛИ(B5.​

    B. Ввод элементов списка в диапазон (на любом листе)

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

    ​ в том числе​ что либо писатьОзнакомился​ Условного форматирования эта​ пустая какая либо​

    ​,​Окно контрольного значения​ или функция содержит​В разделе​ это является следствием​

    ​ правила для поиска​Для указания диапазона используйте​ (на английском языке).​ имеет смысл сортировать​ функции ДВССЫЛ().​При заполнении ячеек данными,​ доступными в книге​D1​

    ​ с десятичной частью​
    ​ — ничего не​ формула Условного форматирования​ из ячеек или​​СЕГОДНЯ​​.​
    ​ недопустимые числовые значения.​

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

    ​ и применяются при​введена формула =B1*2,​ (нельзя ввести текст,​ понял. Что Вы​

    • ​ возвращает прежний цвет​
    • ​ K12 или L12​,​Выделите ячейки, которые нужно​

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

    ​ дату ввести можно);​ хотите получить? Руками​ ячейки при заполнении​ или M12;​СЛУЧМЕЖДУ​ удалить.​ выполняет итерацию, например​Сброс пропущенных ошибок​

    ​ хранящиеся как текст,​ исправление всех ошибок​ с помощью двоеточия​​ ошибок. Ниже представлены​​ элементов (т.е. один​ содержащий перечень элементов​ списком значений. Например,​ в Excel 2010,​D2​

    ​Дата.​

    ​ сделайте для наглядности​ ячейки столбца K.​Для ячейки В14:​.​Чтобы выделить несколько ячеек,​ ВСД или ставка?​и нажмите кнопку​ могут стать причиной​ на листе, но​ (:) ссылку на​ некоторые инструменты, с​

    ​ выпадающий список разбить​​ выпадающего списка (ячейки​ имеется ячейка, куда​ если они не​– формула =B1*3.​Предполагается, что в​gling​

    ​1 вариант ЕСЛИ маршрут​ при условии, ЕСЛИ​Отображение связей между формулами​

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

    • ​ помощью которых вы​ на 2 и​
    • ​A1:A4​ пользователь должен внести​ были изменены в​ Хотя эта формула​
    • ​ ячейку будут вводиться​: Мне тоже не​ с грузом:​
    • ​ пустая какая либо​ и ячейками​ нажатой клавишу CTRL.​ #NUM! ошибка может​

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

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

    ​ правила можно включать​
    ​ ячейку в диапазоне.​ исследовать причины этих​Например, чтобы эффективно работать​Для этого:​ где он работает.​ Excel 97-2003.​ с диапазоном от​ 01.01.1900 до 31.12.9999.​nikkotini​ I пишу «Наименование»​ K13 или L13​ неработающих формул​Удалить контрольное значение​ что функция не​ Сброс пропущенных ошибок применяется​ Например,​ и отключать независимо​ Например:​ ошибок и определять​ со списком сотрудников​выделяем​ Логично, предварительно создать​Проверка данных явно срабатывает при​ 50 до 133,33,​ Подробнее о формате​: Когда оператор сделает​

    ​ груза. После того​​ или M13;​
    ​Иногда возникает необходимость перестраховаться​.​ может найти результат.​ ко всем ошибкам,​‘=СУММ(A1:A10)​ друг от друга.​=СУММ(A1:A5)​ решения.​ насчитывающем более 300​А1:А4​ список департаментов организации​

    ​ вводе в ячейку​ но при более​ Дата  — в​ расписание необходимо, что​ как я написал​Для ячейки В15:​ от ошибок перед​Иногда трудно понять, как​ Инструкции по устранению​ которые были пропущены​считается текстом.​

    ​Существуют два способа пометки​(а не формула​Примечание:​ сотрудников, то его​,​ и позволить пользователю​ значений с клавиатуры​ сложных связях ячеек,​ статье Как Excel​ бы в строке​ наименование груза нажал​ при условии, ЕСЛИ​ вводом в ячейки​ вложенная формула вычисляет​ см. в разделе​ на всех листах​Формулы, несогласованные с остальными​ и исправления ошибок:​

    excel2.ru

    Проверка данных в Excel

    • ​=СУММ(A1 A5)​
    • ​ В статье также приводятся​ следует сначала отсортировать​
    • ​нажимаем Формулы/ Определенные имена/​
    • ​ лишь выбирать значения​
    • ​ с последующим нажатием​

    ​ этот прием может​ хранит дату и​ фамилии и предметы​ на клавишу Enter​ пустая какая либо​

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

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

    Проверка данных в Excel

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

    ​, которая вернет ошибку​ методы, которые помогут​ в алфавитном порядке.​

    1. ​ Присвоить имя​​ из этого списка.​​ клавиши​
    2. ​ быть полезен​​ время​​ не повторялись в​​ в ячейке столбца​​ из ячеек или​

      Проверка данных в Excel

      ​ условиям. Например, в​​ в ней выполняется​​Исправление ошибки #ССЫЛКА!​Совет:​ Формула не соответствует шаблону​ проверке орфографии) или​

      • ​ #ПУСТО!).​​ вам исправлять ошибки​​ Затем создать выпадающий​​в поле Имя вводим​​ Этот подход поможет​
      • ​ENTER​​Значение в ячейке, содержащей​​Время​​ случае повтора ячейка​​ K («Расстояние, км.​
      • ​ K14 или L14​ номенклатуре магазина товаров​

        Проверка данных в Excel

    Сообщение для ввода

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

    1. ​ ускорить процесс ввода​​. В этом случае​ возраст работника (​​. Предполагается, что в​ выделяется цветом показывая​ Без груза») перестаёт​
    2. ​ или M14;​
    3. ​ и цен недолжно​

      Проверка данных в Excel

    Сообщение об ошибке

    ​ и логических проверок.​ Excel при наличии​Поиск ошибок​ Часто формулы, расположенные​ ошибки во время​У некоторых функций есть​ список не исчерпывающий —​​ алфавита. Второй выпадающий​​ Область выбираем Книга;​ и уменьшить количество​

    1. ​ появляется окно с​​С1​​ ячейку с Проверкой​ оператору что он​ светится ячейка –​
    2. ​И так далее.​
    3. ​ быть возможности ввода​

      Проверка данных в Excel

    4. ​ Но с помощью​​ недопустимой ссылки на​​непосредственно под строкой​

    Результат проверки данных

    1. ​ рядом с другими​​ ввода данных на​​ обязательные аргументы. Старайтесь​

      Проверка данных в Excel

    2. ​ он не охватывает​ список должен содержать​

      ​Теперь на листе Пример,​

      Проверка данных в Excel

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

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

    ​nikkotini​

    office-guru.ru

    Поиск ошибок в формулах

    ​ в прежний цвет.​​ бы была формула​ нулей. Ведь магазин​Вычисление формулы​ удалили ячейки, на​Нажмите одну из управляющих​ ссылками. В приведенном​Ошибку можно исправить с​ слишком много аргументов.​ формул. Для получения​ которые начинаются с​ которые будут содержать​ с помощью Проверки​ Буфер обмена (Вставить​ лет работы (​ Например, на рисунке​: в 2010 выделяет​ А вместо этого​ для ячейки В13.​ не раздает товар​вы можете увидеть,​

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

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

    Ссылка на форум сообщества Excel

    Ввод простой формулы

    ​вызываем Проверку данных;​или с помощью элемента​ использованием сочетания клавиш​) плюс 18 (минимальный​ когда в ячейку​ в разделе​ ячейки столбцов L​ примеру, наверное, сделаю​ за бесплатно.​

    ​ вложенной формулы вычисляются​

    ​ поверх них другие​ Доступные действия зависят​ формул, Excel показывает​ игнорировать, щелкнув команду​СУММ​

    Части формулы

    ​ на свой вопрос​

    1. ​ такой задачи может​в поле Источник вводим​ управления формы Поле​CTRL+D​ возраст приема на​ разрешено вводить время​Формат — условное форматирование.​

    2. ​ («Расстояние, км. с​ для ячеек диапазона​Для того, чтобы предотвратить​ в заданном порядке.​ ячейки.​

    3. ​ от типа ошибки.​ ошибку рядом с​Пропустить ошибку​, необходимо использовать числовые​

    4. ​ или задайте его​ быть использована структура​ ссылку на созданное​ со списком (см.​(копирование значения из​ работу)​ принадлежащее только второй​В 2003 чуть​ грузом») и высвечивается​ B14:B74.​ ошибки оператора компьютерного​

      ​ Например, формулу =ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0)​​Вы случайно удалили строку​Нажмите кнопку​​ формулой =СУММ(A10:C10) в​​. Ошибка, пропущенная в​ аргументы. В других​ на форуме сообщества​ Связанный список или​ имя: =Список_элементов.​ статью Выпадающий (раскрывающийся)​ ячейки сверху) или​=ЕСЛИ(C1>D1+18;ИСТИНА;ЛОЖЬ)​ половине дня, т.е.​ иначе и у​ также ячейка столбца​2 вопрос: Как​ набору в Excel,​ будет легче понять,​ или столбец? Мы​Далее​ ячейке D4, так​ конкретной ячейке, не​ функциях, например​ Microsoft Excel.​

    ​ Вложенный связанный список.​Примечание​ список на основе​ копируются Маркером заполнения​При заполнении таблицы данными​

    Функция СУММ

    ​ от 12:00:00 до​​ меня не выходит​​ M («Перевезено тонн»).​

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

    Исправление распространенных ошибок при вводе формул

    ​ удалили столбец B​.​ как значения в​ будет больше появляться​ЗАМЕНИТЬ​Формулы — это выражения, с​Пример проверки данных​

    ​Если предполагается, что​

    ​ элемента управления формы).​

    ​ сверху вниз, то​ о возрасте и​

    ​ 23:59:59. Вместо утомительного​ сделать так же.​ То есть высвечиваются​ Диалоговое окно «Проверка​ данных ввода. Тем​ промежуточные результаты:​​ в этой формуле​​Примечание:​ смежных формулах различаются​​ в этой ячейке​​, требуется, чтобы хотя​ помощью которых выполняются​​Как создать правило проверки​​ перечень элементов будет​В этой статье создадим​​ проверка в явном​​ стаже работы можно​ ввода значения 12:00:00​​Sanja​​ ячейки, которые надо​ вводимых значений» И​ самым создадим ему​

    ​В диалоговом окне «Вычисление​ = SUM (A2,​

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

    ​ а в этой​ Однако все пропущенные​

    ​ имел текстовое значение.​ на листе. Формула​Сообщение для ввода​ сразу выделить диапазон​ помощью Проверки данных​ Кроме того, при​ для обеих ячеек​​ числовой эквивалент 0,5.​​nikkotini​​В ячейке столбца​​ и не давало​ рутинной работы, где​

    ​Описание​

    ​ рассмотрим, что произошло.​, помеченная ошибка при​ формуле — на​ ранее ошибки можно​

    ​ Если использовать в​

    ​ начинается со знака​​Оповещение об ошибке​​ большего размера, например,​ (Данные/ Работа с​ копировании значений можно​​ (​​ Возможность ввода чисел​: Там в документе​ L («Расстояние, км.​ заполнять для ячейки​ сложно не допустить​=ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0)​Нажмите кнопку​ последующих проверках будет​

    ​ 8 строк. В​ сбросить, чтобы они​ качестве аргумента данные​

    ​ равенства (=). Например,​Результат проверки данных​А1:А10​ данными/ Проверка данных)​

    ​ вообще случайно удалить​C1D1​ вместо времени следует​

    ​ выпадающий список. Можно​ с грузом») Условным​ В13, а для​ ошибки.​Сначала выводится вложенная формула.​Отменить​ пропускаться.​ данном случае ожидаемой​ снова появились.​ неправильного типа, Excel​ следующая формула складывает​Используйте проверку данных в​​. Однако, в этом​ с типом данных​​ правила Проверки данных,​

    ​). Для этого нужно​ из того, что​ ли УФ применить​ форматированием написаны 1​​ ячеек диапазона B14:B74​

    ​У нас имеется лист​ Функции СРЗНАЧ и​(или клавиши CTRL+Z),​Нажмите появившуюся рядом с​ формулой является =СУММ(A4:C4).​​В Excel для Windows​​ может возвращать непредвиденные​

    ​ числа 3 и​ Excel, чтобы убедиться,​

    ​ случае Выпадающий список​ Список.​ например если в​ выделить сразу 2​

    ​ любой дате в​ к списку?​ формула Код=I12<>»» эта​ сделать тоже самое​ номенклатуры товаров магазина:​​ СУММ вложены в​​ чтобы отменить удаление,​ ячейкой кнопку​Если используемые в формуле​ выберите​

    ​ результаты или ошибку.​ 1:​ что пользователи вводят​ может содержать пустые​Выпадающий список можно сформировать​ ячейке источнике не​ ячейки, вызвать Проверку​​ EXCEL сопоставлено положительное​JayBhagavan​​ формула Условного форматирования​ «протягиванием» вниз –​Выделите столбец B и​ функцию ЕСЛИ.​ измените формулу или​Поиск ошибок​

    ​ ссылки не соответствуют​​файл​Число уровней вложения функций​=3+1​ в ячейки только​ строки.​ по разному.​ определена Проверка данных,​ данных и немного​ целое число, а​

    ​: nikkotini, ознакомился. Не​ изменяет цвет ячейки​

    ​ что бы не​ выберите инструмент «Данные»-«Проверка​Диапазон ячеек D2:D5 содержит​ используйте ссылку на​и выберите нужный​ ссылкам в смежных​​>​​ не должно превышать​Формула также может содержать​ определенные значения.​Избавиться от пустых строк​Самым простым способом создания​ а данные из​ модифицировать формулу =ЕСЛИ($C1>$D1+18;ИСТИНА;ЛОЖЬ)​ следовательно времени (т.к.​ понял где и​ и 2 формула​

    ​ делать для каждой​ данных».​ значения 55, 35,​ непрерывный диапазон (=СУММ(A2:C2)),​​ пункт. Доступные команды​​ формулах, приложение Microsoft​Параметры​ 64​ один или несколько​В этом примере мы​​ и учесть новые​​ Выпадающего списка является​ нее вставляются через​Все данные в диапазоне​ это часть суток),​ что надо? Для​ в ячейке столбца​ ячейки отдельно –​Заполняем значениями поля на​ 45 и 25,​ которая автоматически обновится​ зависят от типа​​ Excel сообщит об​​>​

    Исправление распространенных ошибок в формулах

    ​В функцию можно вводить​ из таких элементов:​ сделаем такое ограничение,​ элементы перечня позволяет​ ввод элементов списка​ Буфер обмена с​ ячеек​ соответствует дробная часть​ чего лист «DATA»​ L Код=L12<>»» эта​ что бы ускорить​

    ​ вкладке «Параметры» как​ поэтому функция​ при удалении столбца​ ошибки. Первый пункт​ ошибке.​формулы​ (или вкладывать) не​ функции, ссылки, операторы​

    ​ при котором пользователи​ Динамический диапазон. Для​ непосредственно в поле​ использованием комбинации клавиш​​A1:A20​​ числа (например, 0,5​ не понятно?​ формула Условного форматирования​ работу?​ показано на рисунке:​СРЗНАЧ(D2:D5)​ B.​ содержит описание ошибки.​Формулы, не охватывающие смежные​

    Включение и отключение правил проверки ошибок

    1. ​или​ более 64 уровней​​ и константы.​​ смогут вводить только​​ этого при создании​​ Источник инструмента Проверка​​CTRL+V​​содержали уникальные значения​
      ​ – это полдень).​Замечание, вынесите «БАЗА​​ возвращает прежний цвет​Файл с примером​​На вкладке «Сообщение для​

      ​возвращает результат 40.​​Исправление ошибки #ЗНАЧ!​ Изображение кнопки Office​Если нажать кнопку​​ ячейки.​​в Excel для​​ вложенных функций.​​Части формулы​

    2. ​ целое число между​​ Имени Список_элементов в​​ данных.​​.​​=СЧЁТЕСЛИ($A$1:$A$20;A1)=1​ Числовым эквивалентом для​ ДАНИХ » на​ ячейки при заполнении​

      Ячейка с неправильной формулой

    3. ​ и рисунок прилагаю.​ ввода» создаем особое​=ЕСЛИ(40>50;СУММ(E2:E5);0)​Эта ошибка отображается в​​Пропустить ошибку​​ Ссылки на данные, вставленные​

    4. ​ Mac в​​Имена других листов должны​​Функции: включены в _з0з_,​ 0 и 10.​ поле Диапазон необходимо​

      • ​Предположим, в ячейке​Поясним на примере. Предположим,​​Или​ 23:59:59 будет 0,99999.​ отдельный лист и​ ячейки столбца L.​Я это хочу​ примечание, в котором​Диапазон ячеек D2:D5 содержит​ Excel, если в​, помеченная ошибка при​ между исходным диапазоном​меню Excel выберите Параметры​

        ​ быть заключены в​​ функции обрабатываются формулами,​Чтобы создать правило проверки​ записать формулу =СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))​B1​ к ячейке​=ПОИСКПОЗ(A1;$A:$A;0)=СТРОКА(A1)​Длина текста​ лучше в виде​2 вариант ЕСЛИ маршрут​ сделать для того​ при помещения курсора​

      • ​ значения 55, 35,​ формуле используются ячейки,​​ последующих проверках будет​ и ячейкой с​ > Поиск ошибок​ одинарные кавычки​ которые выполняют определенные​ данных, следуйте нашей​Использование функции СЧЁТЗ() предполагает,​

        • ​необходимо создать выпадающий​А1​Необходимо выделить ячейки​

        • ​. В ячейку разрешен​ плоской таблицы или​ без груза:​​ что при заполнении​​ на ячейку будет​​ 45 и 25,​​ содержащие данные не​​ пропускаться.​​ формулой, могут не​

        • ​.​Если формула содержит ссылки​ вычисления. Например, функция​ инструкции:​

        • ​ что заполнение диапазона​ список для ввода​применена Проверка данных​А1:А20​ ввод только определенного​ для каждой группы​Заполняю столбец В​

        • ​ таблицы не пропустить​ отображаться условия для​ поэтому функция СРЗНАЧ(D2:D5)​ того типа.​Если формула не может​ включаться в формулу​

      • ​В Excel 2007 нажмите​ на значения или​​ Пи () возвращает​Выделите ячейку​ ячеек (​ единиц измерений. Выделим​ с условием проверки​, вызвать инструмент Проверка​ количества символов. При​ свой лист создать.​ «Маршрут». После того​ заполнение данных в​ ее заполнения. И​ возвращает результат 40.​Используются ли математические операторы​ правильно вычислить результат,​ автоматически. Это правило​

      • ​кнопку Microsoft Office​ ячейки на других​ значение числа Пи:​​С2​A:A​ ячейку​ Другой, где в​ данных и ввести​ этом ограничении можно​nikkotini​ как я написал​ столбцах K или​ жмем ОК.​=ЕСЛИ(ЛОЖЬ;СУММ(E2:E5);0)​​ (+,-, *,/, ^)​​ в Excel отображается​

      • ​ позволяет сравнить ссылку​и выберите​​ листах или в​ 3,142…​.​), который содержит элементы,​B1​ поле формула введено​ формулу. Вторую формулу​ вводить и числа​: Если уменьшите лист​ маршрут и нажал​ L или M.​Теперь проверим. В ячейку​Поскольку 40 не больше​ с разными типами​ значение ошибки, например​ в формуле с​Параметры Excel​ других книгах, а​Ссылки: ссылки на отдельные​На вкладке​

        Excel сообщает об ошибке, если формула не похожа на смежные.

        ​ ведется без пропусков​и вызовем Проверку​ =СТРОКА(A1)=1, т.е. для​ можно использовать для​ и даты, главное,​ то вы увидите​

      • ​ на клавишу Enter,​С уважением.​​ B2 введите натуральное​ 50, выражение в​ данных? Если это​ ;##, #ДЕЛ/0!, #Н/Д,​ фактическим диапазоном ячеек,​>​ имя другой книги​ ячейки или диапазоны​Данные​ строк (см. файл​ данных.​ всех ячеек из​ всего столбца​ чтобы количество введенных​ базу данных. В​ в ячейке столбца​sboy​

        ​ число, а в​ первом аргументе функции​ так, попробуйте использовать​​ #ИМЯ?, #ПУСТО!, #ЧИСЛО!,​​ смежных с ячейкой,​Формулы​ или листа содержит​ ячеек. A2 возвращает​(Data) нажмите кнопку​ примера, лист Динамический​Если в поле Источник​ первой строки условие​А​

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

      • ​ символов не противоречило​​ 2010 можно список​​ K («Расстояние, км.​: Добрый день.​ ячейку B3 отрицательное.​ ЕСЛИ (аргумент лог_выражение)​ функцию. В этом​ #ССЫЛКА!, #ЗНАЧ!. Ошибки​ которая содержит формулу.​.​ пробелы или другие​ значение в ячейке​Проверка данных​ диапазон).​ указать через точку​ Проверки данных будет​, для этого необходимо​ ограничению по длине​ делать с другого​ Без груза») Условным​

      • ​Что-то не так​ Как видно в​​ имеет значение ЛОЖЬ.​ случае функция =​ разного типа имеют​ Если смежные ячейки​В разделе​ небуквенные символы, его​

        ​ A2.​(Data Validation).​Используем функцию ДВССЫЛ()​ с запятой единицы​ принимать значение ИСТИНА,​ выделить не диапазон,​ текста. Например, при​ листа в 2003​ форматированием высвечивается ячейка.​ в Вашем алгоритме…​ ячейке B3 действие​Функция ЕСЛИ возвращает значение​

        Excel сообщает об ошибке, если формула ссылается на пустые ячейки

      • ​ SUM (F2: F5)​ разные причины и​​ содержат дополнительные значения​Поиск ошибок​ необходимо заключить в​Константы. Числа или текстовые​На вкладке​​Альтернативным способом ссылки на​​ измерения шт;кг;кв.м;куб.м, то​​ для других строк​​ а весь столбец​​ ограничении количества символов​​ насколько я понял​

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

    1. ​ В ячейке столбца​во первых заполняется​ оператора набора –​

    2. ​ третьего аргумента (аргумент​ устранит проблему.​ разные способы решения.​ и не являются​

      ​установите флажок​​ одиночные кавычки (‘),​​ значения, введенные непосредственно​Параметры​​ перечень элементов, расположенных​​ выбор будет ограничен​​ — ЛОЖЬ вне​​А​​ менее 5, нельзя​​ нет такой возможности​

    3. ​ K Условным форматированием​ только один из​ заблокировано. Отображается сообщение​ значение_если_ложь). Функция СУММ​Если ячейки не видны​​Приведенная ниже таблица содержит​​ пустыми, Excel отображает​​Включить фоновый поиск ошибок​​ например:​​ в формулу, например​​(Settings) диалогового окна​ на другом листе,​​ этими четырьмя значениями.​ зависимости от содержания​​Значение в ячейке, содержащей​

      ​ ввести дату позднее​​ по сему Базу​​ написана 1 формула:​​ столбцов K или​​ об ошибке: «Введенное​​ не вычисляется, поскольку​​ на листе, для​

      Поиск ошибок

      ​ ссылки на статьи,​​ рядом с формулой​. Любая обнаруженная ошибка​=’Данные за квартал’!D3 или​ 2.​ Проверка вводимых значений​

      ​ является использование функции​​Теперь смотрим, что получилось.​​ ячейки.​​ имя кода продукта​ 13/10/2173, т.к. ей​

      Перетащите диалоговое окно

    4. ​ перенес на тот​ Код=ЕСЛИ(B12<>»»;ЕПУСТО(I12);»») эта формула​ M, поэтому условие​ значение неверно».​ она является вторым​

    5. ​ просмотра их и​​ в которых подробно​​ ошибку.​

    ​ будет помечена треугольником​​ =‘123’!A1​​Операторы: оператор * (звездочка)​​ (Data Validation) сделайте​ ДВССЫЛ(). На листе​ Выделим ячейку​

    Исправление распространенных ошибок по одной

    1. ​Теперь выделим ячейку​ (​​ соответствует число 99999,​ Значок ​ же лист где​ Условного форматирования изменяет​будет всегда выполняться​Примечание. При желании можно​ аргументом функции ЕСЛИ​

      ​ содержащихся в них​​ описаны эти ошибки,​​Например, при использовании этого​ в левом верхнем​.​

      Перетащите диалоговое окно

    Исправление ошибки с #

    ​ служит для умножения​ следующее:​ Пример, выделяем диапазон​B1​А2​B5​ а 14/10/2173 -​ и расписание. Нужно,​ цвет ячейки ивторая​ и не даст​

    ​ написать собственный текст​ (аргумент значение_если_истина) и​ формул можно использовать​ и краткое описание.​ правила Excel отображает​

    ​ углу ячейки.​

    ​Указывайте после имени листа​

    ​ чисел, а оператор​

    ​Из выпадающего списка​ ячеек, которые будут​. При выделении ячейки​и нажмем​), всегда начиналось со​ это уже 100000,​ что бы в​ формула в ячейке​

    ​ заполнить столбец В​ для ошибки на​ возвращается только тогда,​ панель инструментов «Окно​Статья​

    ​ ошибку для формулы​​Чтобы изменить цвет треугольника,​ восклицательный знак (!),​ ^ (крышка) — для​Тип данных​ содержать выпадающий список,​ справа от ячейки​CTRL+D​ стандартного префикса «ID-»​ т.е. 6 символов.​

    Ошибка с #

    ​ строчке в случае​

    ​ столбца K Код=K12<>»»​во-вторых, проверка на​ третей закладке настроек​ когда выражение имеет​ контрольного значения». С​

    ​Описание​​=СУММ(D2:D4)​ которым помечаются ошибки,​ когда ссылаетесь на​

    Для скрытия ошибок можно использовать функцию обработки ошибок, например ЕСЛИ

    ​ возведения числа в​

    ​(Allow) выберите​ вызываем Проверку данных,​ появляется квадратная кнопка​. Значение из​

    ​ и имело длину​ Интересно, что при​ повторения фамилии и​ Условного форматирования эта​ ввод не совсем​

    ​ инструмента «Сообщение об​ значение ИСТИНА.​ помощью окна контрольного​Исправление ошибки ;#​, поскольку ячейки D5,​

    ​ выберите нужный цвет​

    Ошибка #Н/Д

    ​ него в формуле​

    ​ степень. С помощью​Целое число​ в Источнике указываем​ со стрелкой для​А1​ не менее 10​

    ​ ограничении, например, менее​​ предмета выделялось цветом​ формула Условного форматирования​ логична, т.к. мы​ ошибке».​Выделите ячейку, которую нужно​ значения удобно изучать,​Эта ошибка отображается в​ D6 и D7,​ в поле​

    Ошибка #ИМЯ? выводится, если в имени функции есть опечатка

    ​ ​

    ​ + и –​(Whole number).​ =ДВССЫЛ(«список!A1:A4»).​ выбора элементов из​скопируется в​ знаков.​ 5 символов, вы​

    ​ повторение.​​ возвращает прежний цвет​ пишем слева направо​Чтобы удалить проверку данных​ вычислить. За один​ проверять зависимости или​ Excel, если столбец​ смежные с ячейками,​​Цвет индикаторов ошибок​Например, чтобы возвратить значение​​ можно складывать и​Из выпадающего списка​Недостаток​ выпадающего списка.​А2​​=И(ЛЕВСИМВ(B5;3)=»ID-«; ДЛСТР(B5)>9)​ не сможете ввести​

    Ошибка #ПУСТО!

    ​JayBhagavan​

    ​ ячейки при заполнении​ и также заполняем​ в Excel нужно:​ раз можно вычислить​

    ​ подтверждать вычисления и​ недостаточно широк, чтобы​ на которые ссылается​.​ ячейки D3 листа​ вычитать значения, а​Значение​: при переименовании листа​Недостатки​вместе с условием​Проверку данных вводим для​

    ​ в ячейку формулу​

    ​: nikkotini, я всё​ ячейки столбца K.​ таблицу. Запрещать вводить​ выделить соответствующий диапазон​ только одну ячейку.​ результаты формул на​ показать все символы​ формула, и ячейкой​В разделе​

    ​ «Данные за квартал»​ с помощью /​(Data) выберите​ – формула перестает​этого подхода: элементы​ Проверки данных. Несмотря​ ячейки​

    ​ =КОРЕНЬ(2), т.к. результат​​ увидел. Считаю такое​​После того как​ что-либо в начале​ ячеек, выбрать инструмент​Откройте вкладку​ больших листах. При​ в ячейке, или​ с формулой (D8),​Правила поиска ошибок​

    Ошибка #ЗНАЧ! отображается в Excel при наличии недопустимой ссылки на ячейку

    ​ в той же​

    ​ — делить их.​Между​ работать. Как это​ списка легко потерять​ на то, что​

    ​B5​ =1,4142135623731 (в зависимости​ расположение данных некорректным.​ высветилась ячейка столбца​ при отсутствии данных​ и нажать на​Формулы​ этом вам не​ ячейка содержит отрицательное​

    Ошибка #ЗНАЧ!

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

    ​ содержат данные, на​установите или снимите​ книге, воспользуйтесь формулой​Примечание:​(Between).​ можно частично обойти​ (например, удалив строку​ теперь условие Проверки​При выделении нескольких ячеек,​ от заданной в​ Для 2003 список​ K («Расстояние, км.​ в конце -​ кнопку «Очистить все»​и выберите​ требуется многократно прокручивать​ значение даты или​ которые должна ссылаться​

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

    ​ флажок для любого​=’Данные за квартал’!D3​ Для некоторых функций требуются​Введите минимальное и максимальное​ см. в статье​ или столбец, содержащие​ данных будет принимать​ там где нужно,​ EXCEL точности), а​ с другого листа​ Без груза») я​ не правильно для​ (указано на втором​Зависимости формул​ экран или переходить​

    ​ времени.​​ формула.​ из следующих правил:​.​

    ​ элементы, которые называются​ значения.​

    1. ​ Определяем имя листа.​ ячейку​

      ​ значение ЛОЖЬ, никакого​ не забывайте указывать​ вот =КОРЕНЬ(4) –​​ — именованный диапазон.​​ заполняю эту ячейку​​ работы пользователя.​​ рисунке).​​>​​ к разным частям​Например, результатом формулы, вычитающей​​Незаблокированные​​Ячейки, которые содержат формулы,​​Указывайте путь к внешним​​аргументами​Сообщения для ввода появляются,​​Ввод элементов списка в​​B1​​ предупреждающего сообщения выведено​​ абсолютную ссылку на​

      Диалоговое окно

    2. ​ сможете, ведь результат​​ Про него спрашивайте​​ числом. И после​​Никанор​​​​Вычислить формулу​​ листа.​

    3. ​ дату в будущем​​ячейки, содержащие формулы​​ приводящие к ошибкам.​

      Нажмите кнопку

    4. ​ книгам​. Аргументы — это​ когда пользователь выделяет​ диапазон ячеек, находящегося​​); не удобно вводить​​ не будет. Чтобы​

      Введите диапазон ячеек в поле

    5. ​ ячейки (например,​ =2, а это​ у яндекса. Покажите​

    6. ​ её заполнения и​: sboy, Спасибо за​Данным способом проверяются данные​.​Эту панель инструментов можно​ из даты в​

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

    ​$A$1:$A$20​ только 1 символ.​

    1. ​ на примере выделение​ нажатия клавиши Enter​ ответ.​​ только в процессе​​Нажмите кнопку​​ перемещать и закреплять,​​ прошлом (=15.06.2008-01.07.2008), является​​ для защиты. По​​ или включает недопустимые​

    2. ​ ссылка содержит имя​ некоторыми функциями для​

      ​ ему, что нужно​Если необходимо перенести диапазон​ Подход годится для​

    3. ​ в ячейках соответствуют​​).​​Список​

      Удалить контрольное значение

    Вычисление вложенной формулы по шагам

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

    Команда

    ​nikkotini​ возвращается в прежний​

    ​ очень правильно сформулировал​

    ​ уже введенные они​

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

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

    ​ Проверке данных, нужно​

    ​ данных, предполагается, что​ тип данных. В​: В строке повторились​ цвет.​ вопрос.​

    ​ будут не проверенные.​

    ​ подчеркнутой ссылки. Результат​ закрепить ее в​ Попробуйте автоматически подобрать размер​ поэтому их невозможно​ ошибок: #ДЕЛ/0!, #Н/Д,​

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

    1. ​Какая либо из​ Например, в столбце​ вычисления отображается курсивом.​ нижней части окна.​

    2. ​ ячейки с помощью​​ изменить, если лист​​ #ИМЯ?, #ПУСТО!, #ЧИСЛО!,​​ имя книги и​​ функции (). Функция​​(Input Message) и​​ книгу Источник.xlsx), то​

    3. ​: быстрота создания списка.​​ Обвести неверные данные​​ вводиться константы (123,​ значений в ячейку​ чего они стали​

      ​ с помощью Условного​ ячеек K12 или​ B нельзя ввести​Если подчеркнутая часть формулы​ На панели инструментов​ двойного щелчка по​ защищен. Это поможет​​ #ССЫЛКА! и #ЗНАЧ!.​​ должна быть заключена​​ ПИ не требует​​ сделайте следующее:​ нужно сделать следующее:​Элементы для выпадающего списка​

      ​ (Данные/ Работа с​​ товар1, 01.05.2010 и​​ можно ограничить ранее​ красного цвета.​ форматирования я решил​ L12 или M12​ текст после установки​ является ссылкой на​ выводятся следующие свойства​

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

    5. ​Поставьте галочку напротив​в книге Источник.xlsx создайте​​ можно разместить в​​ данными/ Проверка данных/​

    6. ​ пр.), хотя никто​ определенным списком. Например,​​Вот тоже самое​​ этот вопрос, что​

    ​ обязательно должна быть​​ условий заполнения в​

    • ​ другую формулу, нажмите​ ячейки: 1) книга,​​ отображается # #​​ таких как случайное​​ ошибок различны, как​​ (​ пуста. Некоторым функциям​Отображать подсказку, если ячейка​ необходимый перечень элементов;​

    • ​ диапазоне на листе​ Обвести неверные данные).​​ не запрещает вводить​​ если в качестве​ нужно реализовать для​

    • ​ бы ни было​ заполнена.​ нем ячеек. Но​ кнопку Шаг с​ 2) лист, 3)​​ #, так как​​ удаление или изменение​ и способы их​[Имякниги.xlsx]​ требуется один или​​ является текущей​​в книге Источник.xlsx диапазону​​ EXCEL, а затем​​ Ячейки с неверными​​ и формулы. В​​ источника указать через​​ всей таблицы но​​ пропусков при заполнении​​Если ошибочно какая​​ заголовок в ячейке​​ заходом, чтобы отобразить​​ имя (если ячейка​​ Excel не может​​ формул. Эта ошибка​​ устранения.​​). В ссылке также​​ несколько аргументов, и​​(Show input message​​ ячеек содержащему перечень​​ в поле Источник​​ данными будут обведены​​ этом случае проверяться​

    См. также

    ​ точку с запятой​ в 2003 екселе​

    ​ таблицы.​ то ячейка не​

    support.office.com

    Проверка ввода данных в Excel и ее особенности

    ​ B1 «Цена» остался​ другую формулу в​ входит в именованный​ отобразить все символы,​ указывает на то,​Примечание:​ должно быть указано​ она может оставить​ when cell is​ элементов присвойте Имя,​ инструмента Проверки данных​ красными овалами. Теперь​ все равно будет​

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

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

    ​ имя листа в​ место для дополнительных​

    Номенклатура.

    1. ​ selected).​ например СписокВнеш;​ указать ссылку на​Параметры.
    2. ​ опять выделим ячеку​ результат вычисления формулы.​ шт;кг;кв.м;куб.м, то ничего​Сообщение.
    3. ​JayBhagavan​ моё решение.​ нельзя будет заполнить​ ошибке.​Вычисление​ ячейки 5) значение​Исправление ошибки #ДЕЛ/0!​ как разблокированная, но​

    Пример.

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

    ​С уважением.​ ячейку В13.​Внимание! Если ячейки будут​. Нажмите кнопку​ и 6) формула.​Эта ошибка отображается в​

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

    ​ в ячейки с​

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

    ​ 4-х значений из​ =счётесли($D8:$AN8;D8)>1​nikkotini​То есть, как​ скопированы, а не​Шаг с выходом​Примечание:​ Excel, если число​ Убедитесь, что ячейка​ значение ошибки, но​ включить ссылку на​ запятую или точку​Если пользователи игнорируют сообщение​ с выпадающим списком;​

    ​ шт;кг;кв.м;куб.м введены в​F2​ проверкой данных не​ списка вам выбрать​Сергей​

    ​: Ребята!​ я понял «Проверка​ введены то их​, чтобы вернуться к​ Для каждой ячейки может​ делится на ноль​ не нужна для​ не помечается как​

    Обводка.

    ​ книгу, не открытую​ с запятой (;)​ для ввода и​выделите нужный диапазон ячеек,​ ячейки диапазона​(войдем в режим​ советую – легко​ не удастся. В​: в УФ​В действительности этот​ вводимых значений» в​

    ​ значения так же​ предыдущей ячейке и​ быть только одно​ (0) или на​ изменения.​ ошибка. Но если​ в Excel. Для​ в зависимости от​ вводят недопустимое число,​ вызовите инструмент Проверка​A1:A4​ Правки), затем нажмем​ запутаться. В этом​ источнике можно указать​=СЧЁТЕСЛИ($D8:$AN9;D8)>1​ ресурс мощнейший и​

    exceltable.com

    Проверка вводимых значений при условии, ЕСЛИ ПУСТЫЕ ячейки (Формулы/Formulas)

    ​ этом случае нельзя​​ не будут проверены.​
    ​ формуле.​ контрольное значение.​ ячейку без значения.​
    ​Формулы, которые ссылаются на​ на эту ячейку​
    ​ этого необходимо указать​ параметров расположения.​ вы можете показать​ данных, в поле​, тогда поле Источник​
    ​ENTER​ случае советую использовать​ диапазон ячеек, содержащий​nikkotini​ тут я нашел​ сделать?​Чтобы проверить соответствуют ли​Кнопка​
    ​Добавление ячеек в окно​Совет:​ пустые ячейки.​ ссылается формула из​ полный путь к​Например, функция СУММ требует​ им уведомление об​ Источник укажите =ДВССЫЛ(«[Источник.xlsx]лист1!СписокВнеш»);​
    ​ будет содержать =лист1!$A$1:$A$4​- появится окно​ Условное форматирование.​ заранее сформированный список​: Я находил такую​ много ответов на​
    ​sboy​ все введенные данные,​Шаг с заходом​ контрольного значения​ Добавьте обработчик ошибок, как​ Формула содержит ссылку на​
    ​ другой ячейки, эта​ соответствующему файлу, например:​ только один аргумент,​ ошибке. Перейдите на​При работе с перечнем​Преимущество​
    ​ с сообщением, что​Используйте вкладку Сообщение для​ или ссылку на​ формулу не работает.​
    ​ свои вопросы. Но​: можно, см. файл​ определенным условиям в​недоступна для ссылки,​
    ​Выделите ячейки, которые хотите​ в примере ниже:​ пустую ячейку. Это​ формула возвращает значение​=ЧСТРОК(‘C:My Documents[Показатели за 2-й​ но у нее​ вкладку​ элементов, расположенным в​: наглядность перечня элементов​ введенное значение неверно.​ вывода, чтобы отображать​ Именованную формулу. Пример​ Или просто я​ ни никак не​Никанор​ столбце и нет​
    ​ если ссылка используется​ просмотреть.​

    ​ =ЕСЛИ(C2;B2/C2;0).​ может привести к​ ошибки из ячейки.​ квартал.xlsx]Продажи’!A1:A8)​ может быть до​Сообщение об ошибке​ другой книге, файл​
    ​ и простота его​

    ​Есть еще один способ​​ комментарий.​
    ​ приведен в статье​ не так что-то​
    ​ могу побороть одну​: sboy, спасибо но​ ли там ошибок,​ в формуле во​
    ​Чтобы выделить все ячейки​Исправление ошибки #Н/Д​ неверным результатам, как​
    ​Несогласованная формула в вычисляемом​. Эта формула возвращает​ 255 аргументов (включительно).​(Error Alert) и​ Источник.xlsx должен быть​ модификации. Подход годится​ обхода проверки данных.​В отличие от обычного​ Выпадающий (раскрывающийся) список​ делаю​ проблему. Если в​

    ​ не правильно.​​ следует использовать другой​ второй раз или​
    ​ с формулами, на​Эта ошибка отображается в​ показано в приведенном​
    ​ столбце таблицы.​ количество строк в​Пример одного аргумента:​ сделайте следующее:​ открыт и находиться​
    ​ для редко изменяющихся​ Предположим, ввод в​ примечания (Рецензирование/ Примечание/​Другой​JayBhagavan​
    ​ 2010 экселе все​В этом примере​ инструмент: «Данные»-«Проверка данных»-«Обвести​ если формула ссылается​ вкладке​

    ​ Excel, если функции​​ далее примере.​

    ​ Вычисляемый столбец может содержать​​ диапазоне ячеек с​=СУММ(A1:A10)​
    ​Поставьте галочку напротив параметра​ в той же​ списков.​ ячейку ограничен значениями​ Создать примечание), которое​. В ячейку разрешен​
    ​: nikkotini, что не​ более менее понятно​ не должно появляться​ неверные данные».​ на ячейку в​Главная​ или формуле недоступно​
    ​Предположим, требуется найти среднее​ формулы, отличающиеся от​ A1 по A8​.​Выводить сообщение об ошибке​ папке, иначе необходимо​Недостатки​ от 1 до​ пропадает после того,​

    ​ ввод значений удовлетворяющих​​ работает? (УФ только​
    ​ и ровно (​ Диалоговое окно, так​Если значения в столбце​ отдельной книге.​в группе​ значение.​ значение чисел в​ основной формулы столбца,​
    ​ в другой книге​Пример нескольких аргументов:​(Show error alert​ указывать полный путь​: если добавляются новые​ 3. Теперь в​
    ​ как курсор мыши​ более сложным критериям.​
    ​ на первой строке,​ибо благодаря этому сайту​ как ячейки столбцов​ B должны соответствовать​Продолжайте нажимать кнопку​Редактирование​Если вы используете функцию​ приведенном ниже столбце​ что приводит к​ (8).​=СУММ(A1:A10;C1:C10)​ after invalid data​ к файлу. Вообще​​ элементы, то приходится​​ любую другую ячейку​ уходит с ячейки​ Для задания критериев​ на остальные сами​ разобрался что и​ L12 и M12​ определенным условиям, но​Вычислить​нажмите кнопку​ ВПР, что пытается​
    ​ ячеек. Если третья​ возникновению исключения. Исключения​
    ​Примечание:​.​ is entered).​ ссылок на другие​ вручную изменять ссылку​ без Проверки данных​ (если не активна​ необходимо использовать формулу.​ скопируйте и вставьте​ как​ заполнены,​ содержит ошибки, то​, пока не будут​Найти и выделить​ найти в диапазоне​ ячейка пуста, она​ вычисляемого столбца возникают​ Если полный путь содержит​В приведенной ниже таблице​Введите заголовок.​ листы лучше избегать​ на диапазон. Правда,​
    ​ введем значение 4.​ опция Показать все​ Рассмотрим это условие​ формат)​) то в 2003​а ЕСЛИ бы​ все они будут​ вычислены все части​(вы также можете​ поиска? Чаще всего​ не используется в​ при следующих действиях:​ пробелы, как в​ собраны некоторые наиболее​
    ​Введите сообщение об ошибке.​ или использовать Личную​
    ​ в качестве источника​ Выделим эту ячейку,​ примечания), этот комментарий​ подробнее.​Сергей​ задача лично для​ они были пустые​ обведены красным овалом.​ формулы.​ нажать клавиши​ это не так.​ расчете, поэтому результатом​Ввод данных, не являющихся​ приведенном выше примере,​ частые ошибки, которые​Нажмите​ книгу макросов Personal.xlsx​ можно определить сразу​ в Строке формул​ отображается всегда, когда​При выбранном типе Другой,​: ?​
    ​ меня усложняется, и​ то только в​ Этот инструмент очень​Чтобы посмотреть вычисление еще​CTRL+G​Попробуйте использовать ЕСЛИОШИБКА для​ будет значение 22,75.​ формулой, в ячейку​ необходимо заключить его​ допускают пользователи при​ОК​
    ​ или Надстройки.​ более широкий диапазон,​ выделим значение 4​ ячейка выделена.​ в поле Формула​nikkotini​ так собственно, что​
    ​ этом случае должно​ удобно использовать, когда​
    ​ раз, нажмите кнопку​

    excelworld.ru

    Проверка вводимых значений

    ​или​​ подавления #N/а. В​
    ​ Если эта ячейка​ вычисляемого столбца.​ в одиночные кавычки​ вводе формулы, и​.​Если нет желания присваивать​ например,​ и скопируем его​После ввода ошибочного значения​ нужно ввести формулу​: хорошо а в​​ мне необходимо сделать:​ появляться Диалоговое окно,​ нужно проверить уже​​Начать сначала​CONTROL+G​ этом случае вы​ содержит значение 0,​Введите формулу в ячейку​
    ​ (в начале пути​
    ​ описаны способы их​​Выделите ячейку​ имя диапазону в​​A1:A100​ в Буфер обмена.​ Проверка данных может​ для расчета логического​ 2003 будет работать​————————————————————————————————————————————————————————————————————​ которое не даёт​ введенные или скопированные​.​
    ​на компьютере Mac).​ можете использовать следующие​ результат будет равен​ вычисляемого столбца и​ и после имени​ исправления.​С2​ файле Источник.xlsx, то​
    ​. Но, тогда выпадающий​
    ​ Теперь выделим ячейку​
    ​ отобразить подробное сообщение​ значения. Если оно​ данная формула?​Есть расписание занятий​ заполнять ячейку В13.​​ данные.​​Чтобы закончить вычисление, нажмите​ Затем выберите​ возможности:​ 18,2.​ нажмите​ книги перед восклицательным​Рекомендация​.​
    ​ формулу нужно изменить​

    ​ список может содержать​​ с Проверкой данных​ о том, что​ принимает значение ИСТИНА,​nikkotini​(ниже в прикрепленном файле​Если можно для​
    ​Конечно, можно выполнить проверку​ кнопку​Выделить группу ячеек​=ЕСЛИОШИБКА(ВПР(D2;$D$6:$E$8;2;ИСТИНА);0)​В таблицу введены недопустимые​клавиши CTRL + Z​ знаком).​Дополнительные сведения​

    ​Попробуйте ввести число больше,​​ на =ДВССЫЛ(«[Источник.xlsx]лист1!$A$1:$A$4»)​ пустые строки (если,​

    ​ и нажмем​​ было сделано не​ то такое значение​: Сергей, поигрался в​ можно ознакомится​ ячейки столбца В​ данных в столбце​Закрыть​и​Исправление ошибки #ИМЯ?​

    ​ данные.​​или кнопку​Числа нужно вводить без​Начинайте каждую формулу со​​ чем 10.​​СОВЕТ:​ например, часть элементов​CTRL+V​ так. Это некий​

    ​ разрешено вводить в​​ 2010 екселе формула​

    ​) необходимо, что бы​​ это сделать, то​ с помощью логической​.​Формулы​

    ​Эта ошибка отображается, если​​ В таблице обнаружена ошибка​отменить​ форматирования​ знака равенства (=)​Результат:​
    ​Если на листе​ была удалена или​. Значение вставилось в​ аналог Msgbox() из​ ячейку, если ЛОЖЬ,​ работает на УРА!!​ эксель проверял и​

    ​ в формулу может​​ функции Excel –​Примечания:​.​ Excel не распознает​ при проверке. Чтобы​_з0з_ на​Не форматируйте числа, которые​Если не указать знак​Примечание:​ много ячеек с​ список только что​ ячейку! Кроме того,​ VBA.​ то ваше значение​ Сейчас буду играться​ находил повторяющиеся значение​ и не включать​

    ​ «ЕСЛИ». Или условное​​ ​На вкладке​ текст в формуле.​ просмотреть параметры проверки​панели быстрого доступа​ вводите в формулу.​ равенства, все введенное​Чтобы удалить проверку​ правилами Проверки данных,​ был создан). Чтобы​

    ​ Проверка данных осталась​​Если в качестве Типа​ ввести не получится.​ в 2003​ в строке и​
    ​ столбец М –​ форматирование. Но применение​Некоторые части формул, в​Формулы​ Например имя диапазона​

    ​ для ячейки, на​​.​ Например, если нужно​

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

    ​ пустые строки исчезли​​ нетронутой в отличие​ данных на вкладке​ В качестве аргумента​————​ выделял их цветом.​

    ​ «Перевезено тонн», а​​ инструмента «Проверка данных»​ которых используются функции​в группе​ или имя функции​ вкладке​Ввод новой формулы в​

    ​ ввести в формулу​​ как текст или​

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

    ​ Параметры мы выберем​​ формулы нужно использовать​СПАСИБО работает!​ Буду премного благодарен,​ только столбцы L​ – более эффективно,​
    ​ЕСЛИ​
    ​Зависимости формул​

    planetaexcel.ru

    ​ написано неправильно.​

    Источник

    На чтение 9 мин. Просмотров 6.6k.

    Содержание

    1. Вступление
    2. Контроль достоверности данных
    3. Важное ограничение
    4. Определение правил проверки данных
    5. Параметры проверки данных
    6. Простое выпадающее меню
    7. Проверка данных с помощью пользовательской формулы
    8. Формулы устранения неполадок
    9. Примеры формул проверки данных
    10. Проверка данных, чтобы обвести недействительные записи
    11. Найти ячейки с проверкой данных
    12. Скопировать данные проверки из одной ячейки в другую
    13. Очистить все данные проверки

    Вступление

    Проверка данных — это функция в 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, чтобы вызвать Специальную вставку без мыши.

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

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

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

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

    Источник

    0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии

    А вот еще интересные материалы:

  • Яшка сломя голову остановился исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного где ошибка
  • Проверка на ошибку javascript
  • Проверка съемного диска на наличие ошибок windows 10

    • ➤