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

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

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

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

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

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

Выделите ячейку или целую область, которую Excel должен проверить при вводе данных. Теперь перейдите к ленте меню «Данные | Работа с данными | Проверка данных». В следующем окне установите условия проверки. В поле «Тип данных» выберите между такими опциями, как «Целое число», «Действительное», «Список», «Дата», «Время», «Длина текста» или «Другой».

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

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

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

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

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

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

Как создать таблицу в Word

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

Для представленного выше списка из 2-х учеников потребуется создать в редакторе MS Word таблицу из 3-х строк (учеников у нас в списке пока двое, а самую первую строку таблицы мы используем для заголовков столбцов), и из 5-и столбцов для ввода в них следующих данных:

• фамилия ученика
• имя ученика
• отчество ученика
• дата рождения ученика
• телефон ученика.

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

• Затем поставим курсор на вторую строку,
• откроем вкладку «Вставка» (1 на рис. 1),
• кликнем по иконке «Таблица» (2 на рис. 1) и
• выберем строку меню «Вставить таблицу» (3 на рис. 1).

Рис. 1 (кликните по рисунку для увеличения). Создание новой таблицы в редакторе Word.

В открывшемся служебном окне «Вставка таблицы»

• указываем «Число столбцов» 5 (1 на рис. 2) и
• «Число строк» 3 (2 на рис. 2) ,
• далее жмем на кнопку «ОК» (3 на рис. 2).

Рис. 2. Описание вставляемой таблицы (указание числа столбцов и строк) в редакторе Ворд.

Таблица успешно вставляется и имеет вид, как показано на рис. 3.

Рис. 3 (кликните по рисунку для увеличения). Вставленная пока еще пустая таблица из 3-х строк и 5-и столбцов в редакторе Microsoft Word.

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

В оставшиеся 2 строки таблицы заносим данные ученика по фамилии Иванов и ученицы по фамилии Петрова (рис. 4).

Рис. 4 (кликните по рисунку для увеличения). Заполненная таблица, сделанная в редакторе Ворд.

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

Как выделить и перенести часть таблицы в Ворде

Данные в таблице у нас теперь, как говорят, структурированы. А значит, их легко найти, выделить, куда-то перенести отдельно.

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

Рис. 5 (кликните по рисунку для увеличения). Выделение одной колонки таблицы в редакторе MS Word.

Затем копируем эти данные в буфер обмена (клик мышкой на кнопку «Копировать» — 1 на рис. 6).

Рис. 6 (кликните по рисунку для увеличения). Копирование выделенной части таблицы в буфер обмена в редакторе Microsoft Word.

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

Рис. 7 (кликните по рисунку для увеличения). Подготовка к вставке части таблицы в другое место текста в редакторе Word.

Часть таблицы, а именно – фамилии учеников – вставится ниже под основной таблицей (рис. 8).

Рис. 8 (кликните по рисунку для увеличения). Часть таблицы (фамилии учеников) воспроизведена в другом месте текста в редакторе Ворд.

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

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

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

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

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

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

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

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

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

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

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

Как сделать ВПР в Excel понятная инструкция

Чтобы понять, как пользоваться функцией ВПР в Excel, разберем ее синтаксис:

=ВПР(искомое значение; таблица; номер столбца; )

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

Таблица — ссылка на таблицу, в которой будет искать.

Номер столбца — порядковый номер столбца относительно самого левого столбца из выделенного диапазона поиска.Интервальный просмотр — указывается 0 (точный поиск) или 1 (приблизительный поиск)

Рассмотрим использование ВПР на примере:

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

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

Добавим в первую таблицу столбец Температура и напишем следующую формулу:

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

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

Запомним, что в абсолютном большинстве случаем Интервальный просмотр ставим = 0.

Кстати, если не указать интервальный просмотр совсем, то формула не выдаст ошибку, т.к. этот аргумент необязательный. Но результат будет неожиданным, поскольку в этом случае ВПР считает не указанный Интервальный просмотр равным 1 (а это приблизительный поиск).

Не забываем добавить абсолютные ссылки (значки доллара $) к диапазону, иначе диапазон “съедет” при копировании формулы.

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

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

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

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

Первое, что может оказаться полезным, это проверка по длине введенного текста
. Выбираем пункт «Длина текста
» и указываем любой диапазон ограничений. К примеру, я хочу чтобы текст в поле «Сообщение» был как можно более кратким, но в то же время ячейка не могла оставаться пустой. Значения от 3-х до 25 введенных символов, этого вполне хватит, чтоб вписать что-то вроде «Напоминание об оплате» (21 символ), а при попытке ввести более длинный текст, MS Excel выдаст окно-предупреждение и не даст завершить ввод.

Как определить сложность вопросов

Викторина состояла из 15 вопросов, но все они представляли разную сложность для участников. С помощью функций COUNT (СЧЁТ) и COUNTIF (СЧЁТЕСЛИ) можно очень быстро определить самые легкие и сложные вопросы.

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

Для этого я использовал формулу

=(COUNTIF(D3:FY3,B3))/(COUNT(D3:FY3))

В этой формуле:

• В числителе подсчитывается, сколько ячеек в диапазоне между D3 и FY3 (взято с запасом) содержат такое же значение, как и ячейка B3, т.е. правильный ответ.
• В знаменателе подсчитывается, сколько ячеек в диапазоне между D3 и FY3 содержат значения. Если ячейка пуста, она не учитывается.
• Значение дроби – это доля правильных ответов на вопрос (например, 0.20 означает 20%).

Я скопировал формулу в остальные строки и сразу увидел, что проще всего был вопрос о том, с какой ИТ-компанией сотрудничает OSZone (93% правильных ответов). Самым сложным оказался вопрос о поисковом операторе (лишь 18% участников ответили верно). Это промежуточные данные, но в итоге мало что изменилось.

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

В EXCEL 2007 в Проверке данных
, как и в нельзя впрямую указать ссылку на диапазоны другого листа, например, так =Лист2!$A$1
. Позволяют обойти это ограничение использование .

Если в Проверке данных
нужно сделать, например, ссылку на ячейку А1

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

В Excel 2010, напротив, можно использовать правила проверки данных, ссылающиеся на значения на других листах. В Excel 2007 и Excel 97-2003 проверка данных этого типа не поддерживается и не отображается на листе. Однако все правила проверки данных остаются доступными в книге и применяются при повторном открытии книги в Excel 2010, если они не были изменены в Excel 2007 или Excel 97-2003.

Проверка электронной почты с помощью Regex

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

Шаблон: ^+@+*+\.{2,24}$

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

А теперь укажите критерии проверки, выполнив уже знакомые шаги:

Введите приведенное выше регулярное выражение в B2.
Выберите ячейку A1 и определите имя, называемое Подтвердить адрес электронной почты что относится к: =RegExpMatch(Лист1!A1, Лист1!$B$2)

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

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

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

Если адрес электронной почты, который вы вводите в проверенную ячейку, не соответствует шаблону регулярного выражения, появится следующее предупреждение:

Выделение цветом всей строки

Необходимо выделить цветом те строки таблицы, в которых в столбце «В» значения больше 5000.

Для этого:

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

2. Выберите в меню Формат – Условное форматирование

3. В открывшемся Окне задайте условие:

• Выберите – «формула»
• введите формулу =$B2>5000

  В2 – адрес первой ячейки столбца, в котором проверяются значения

4. Выберите необходимый формат для выделения строки

5. Нажмите ОК

Теперь зеленым цветом будут выделены все строки, в которых в столбце «В» стоит значение большее 5000.

Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе – он фиксирует столбец, оставляянезафиксированной ссылку на строку- проверяемые значения берутся из столбца указанного в формуле, по очереди из каждой последующей строки.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Проверка по числу введенных символов в MS Excel

Первое, что может оказаться полезным, это проверка по длине введенного текста. Выбираем пункт «Длина текста» и указываем любой диапазон ограничений. К примеру, я хочу чтобы текст в поле «Сообщение» был как можно более кратким, но в то же время ячейка не могла оставаться пустой. Значения от 3-х до 25 введенных символов, этого вполне хватит, чтоб вписать что-то вроде «Напоминание об оплате» (21 символ), а при попытке ввести более длинный текст, MS Excel выдаст окно-предупреждение и не даст завершить ввод.

Слишком длинный текст! Excel стоит на страже и не пропустит такой очевидной ошибки

Что такое проверка данных в Excel?

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

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

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

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

Если значения вставляются через Буфер обмена
(Вставить значения
) или с использованием сочетания клавиш CTRL
+
D
(копирование значения из ячейки сверху) или копируются сверху вниз, то проверка в явном виде не осуществляется. Кроме того, при копировании значений можно вообще случайно удалить правила Проверки данных
, например если в ячейке источнике не определена Проверка данных
, а данные из нее вставляются через Буфер обмен
а с использованием комбинации клавиш CTRL+V
.

Поясним на примере. Предположим, к ячейке А1

применена Проверка данных
с условием проверки Другой
, где в поле формула введено =СТРОКА(A1)=1
, т.е. для всех ячеек из первой строки условие Проверки данных
будет принимать значение ИСТИНА, для других строк — ЛОЖЬ вне зависимости от содержания ячейки.

Теперь выделим ячейку А2

и нажмем CTRL+D
. Значение из А1

скопируется в А2

вместе с условием Проверки данных
. Несмотря на то, что теперь условие Проверки данных
будет принимать значение ЛОЖЬ, никакого предупреждающего сообщения выведено не будет. Чтобы убедиться, что данные в ячейках соответствуют условиям определенным в Проверке данных
, нужно вызвать команду меню Обвести неверные данные
(). Ячейки с неверными данными будут обведены красными овалами. Теперь опять выделим ячеку А2

и нажмем клавишу F2
(войдем в режим Правки), затем нажмем ENTER
— появится окно с сообщением, что введенное значение неверно.

Есть еще один способ обхода проверки данных. Предположим, ввод в ячейку ограничен значениями от 1 до 3. Теперь в любую другую ячейку без Проверки данных
введем значение 4. Выделим эту ячейку, в Строке формул
выделим значение 4 и скопируем его в Буфер обмена
. Теперь выделим ячейку с Проверкой данных
и нажмем CTRL+V
. Значение вставилось в ячейку! Кроме того, Проверка данных
осталась нетронутой в отличие от случая, когда через Буфер обмена
, например, вставляется значение из WORD. Чтобы убедиться, что данные в ячейке не соответствуют условиям определенным в Проверке данных
, нужно вызвать команду меню Обвести неверные данные
(Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные
).

Функция ИНФОРМ

Синтаксис:

ИНФОРМ(тип_инфо) 

Результат: Информация о текущей операционной среде.

Аргументы:

тип_инфо — текст, задающий тип возвращаемой информации.

Значение аргумента	Возвращаемое значение
«directory»	Путь текущего каталога
«memavail»	Количество свободной памяти в байтах
«memused»	Количество памяти, используемой для данных
«numfile»	Количество активных рабочих листов
«origin»	Абсолютная ссылка в стиле А1 в виде текста
«osversion»	Текущая версия операционной системы в виде текста
«recalc»	Текущий режим перевычисления; возвращается «Автоматически» или «Вручную»
«release»	Номер версии Microsoft Excel в текстовом виде
«system»	Название операционной среды: Macintosh — «mac»; Windows — «pcdos»
«totmem»	Общее количество доступной памяти в байтах, включая уже используемую

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

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

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

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

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

Я, таким образом, иногда подстраховываю и себя самого, когда создаю таблицы данных. Думаю, «Проверка данных» будет и для Вас полезным помощником!

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

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

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

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

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

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

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

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

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

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

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

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