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

Подтверждаем правильность ввода галочкой.

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

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

=ЕСЛИ(логическое условие, галочка, знак_ошибки)

Все наши допустимые значения, которые могут быть записаны в ячейку при вводе, мы расположим в отдельном диапазоне данных. В нашем случае — в ячейках Е2:Е5. Чтобы было проще работать с этими условиями, давайте обозначим эти ячейки как именованный диапазон. Назовем его «значения». Теперь вместо координат ячеек достаточно будет ввести имя диапазона — «значения». Этот набор можно при необходимости расширить.

Формула в ячейке В2 выглядит следующим образом:

=ЕСЛИ(СЧЁТЕСЛИ(значения,A2),СИМВОЛ(252),СИМВОЛ(251))

Как работает эта формула в Excel?

Чтобы отобразить галочку, если введенное значение верное, вы можете использовать формулу на основе функции ЕСЛИ вместе с шрифтом символов, таким как Wingdings.

проверка правильности ввода Excel при помощи функции ЕСЛИ

Для проверки условия мы используем функцию СЧЕТЕСЛИ.

=СЧЁТЕСЛИ(значения,A2)

Здесь мы считаем, сколько раз значение из ячейки А2 встречается в диапазоне «значения». Если его там нет, то функция СЧЕТЕСЛИ возвратит 0. И этот ноль будет воспринят функцией ЕСЛИ как отрицательный ответ на поставленное условие. Если все введено верно и введенный текст был найден в контрольном списке, то будет возвращена 1, которая будет воспринята как «Да». Это прекрасно работает как логическое условие для функции ЕСЛИ.

Обратите внимание, что здесь используется одна важная особенность Excel: если в качестве аргумента функции ЕСЛИ используется какая-то функция, результатом вычисления которой будет число, то функция ЕСЛИ любое число, отличное от нуля, преобразует в «ИСТИНА», а ноль — в «ЛОЖЬ».

Кстати, и в обратном направлении тоже работает: если в математической формуле в качестве одного из аргументов использовать любую логическую функцию (в том числе и ЕСЛИ) и попытаться проделать с ним математические вычисления, то результат выполнения логической функции будет представлен в виде числа — 0 либо 1.

Чтобы вывести галочку в случае верного ввода, используем функцию СИМВОЛ, которая преобразует код в символ.

коды символов Excel

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

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

Выпадающий список — лучшая гарантия правильности ввода данных.

Ещё примеры использования функции ЕСЛИ и СЧЕТЕСЛИ:

ЕСЛИ + СОВПАД текст Функция ЕСЛИ: проверяем условия с текстом — Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст. СодержаниеПроверяем условие для полного совпадения текста.ЕСЛИ + СОВПАДИспользование функции ЕСЛИ с частичным совпадением текста.ЕСЛИ + ПОИСКЕСЛИ… функция если
Визуализация данных при помощи функции ЕСЛИ — Функцию ЕСЛИ можно использовать для вставки в таблицу символов, которые наглядно показывают происходящие с данными изменения. К примеру, мы хотим показать в отдельной колонке таблицы, происходит рост или снижение продаж.… ЕСЛИ с датами 3 примера, как функция ЕСЛИ работает с датами. — На первый взгляд может показаться, что функцию ЕСЛИ для работы с датами можно применять так же, как для числовых и текстовых значений, которые мы только что обсудили. К сожалению, это…

Защита листа и ячеек в Excel

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

Как поставить защиту в Excel на лист

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

  1. Выделите диапазон ячеек B2:B6 и вызовите окно «Формат ячеек» (CTRL+1). Перейдите на вкладку «Защита» и снимите галочку на против опции «Защищаемая ячейка». Нажмите ОК.
  2. Выберите инструмент «Рицензирование»-«Защитить лист».
  3. В появившемся диалоговом окне «Защита листа» установите галочки так как указано на рисунке. То есть 2 опции оставляем по умолчанию, которые разрешают всем пользователям выделять любые ячейки. А так же разрешаем их форматировать, поставив галочку напротив «форматирование ячеек». При необходимости укажите пароль на снятие защиты с листа.

Теперь проверим. Попробуйте вводить данные в любую ячейку вне диапазона B2:B6. В результате получаем сообщение: «Ячейка защищена от изменений». Но если мы захотим отформатировать любую ячейку на листе (например, изменить цвет фона) – нам это удастся без ограничений. Так же без ограничений мы можем делать любые изменения в диапазоне B2:B6. Как вводить данные, так и форматировать их.

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



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

Часто бывает так, что самое ценное на листе это формулы, которые могут быть достаточно сложными. Данный пример сохраняет формулы от случайного удаления, изменения или копирования. Но их можно просматривать. Если перейти в ячейку B7, то в строке формул мы увидим: «СУММ(B2:B6)» .

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

  1. Запретить выделять ячейки на листе.
  2. Включить скрытие содержимого ячейки.

Рассмотрим, как реализовать второй способ:

  1. Если лист защищенный снимите защиту выбрав инструмент: «Рецензирование»-«Снять защиту листа».
  2. Перейдите на ячейку B7 и снова вызываем окно «Формат ячеек» (CTRL+1). На закладке «Защита» отмечаем опцию «Скрыть формулы».
  3. Включите защиту с такими самыми параметрами окна «Защита листа» как в предыдущем примере.

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

Примечание. Закладка «Защита» доступна только при незащищенном листе.

Как скрыть лист в Excel

Допустим нам нужно скрыть закупочные цены и наценку в прайс-листе:

  1. Заполните «Лист1» так как показано на рисунке. Здесь у нас будут храниться закупочные цены.
  2. Скопируйте закупочный прайс на «Лист2», а в место цен в диапазоне B2:B4 проставьте формулы наценки 25%: =Лист1!B2*1,25.
  3. Щелкните правой кнопкой мышки по ярлычке листа «Лист1» и выберите опцию «Скрыть». Рядом же находится опция «Показать». Она будет активна, если книга содержит хотя бы 1 скрытый лист. Используйте ее, чтобы показать все скрытие листы в одном списке. Но существует способ, который позволяет даже скрыть лист в списке с помощью VBA-редактора (Alt+F11).
  4. Для блокировки опции «Показать» выберите инструмент «Рецензирование»-«Защитить книгу». В появившемся окне «Защита структуры и окон» поставьте галочку напротив опции «структуру».
  5. Выделите диапазон ячеек B2:B4, чтобы в формате ячеек установить параметр «Скрыть формулы» как описано выше. И включите защиту листа.

Внимание! Защита листа является наименее безопасной защитой в Excel. Получить пароль можно практически мгновенно с помощью программ для взлома. Например, таких как: Advanced Office Password Recovery – эта программа позволяет снять защиту листа Excel, макросов и т.п.

Полезный совет! Чтобы посмотреть скрытые листы Excel и узнать их истинное количество в защищенной книге, нужно открыть режим редактирования макросов (Alt+F11). В левом окне «VBAProject» будут отображаться все листы с их именами.

Но и здесь может быть закрыт доступ паролем. Для этого выбираем инструмент: «Tools»-«VBAProjectProperties»-«Protection» и в соответствующих полях вводим пароль. С другой стороны, если установленные пароли значит, книга скрывает часть данных от пользователя. А при большом желании пользователь рано или поздно найдет способ получить доступ этим к данным. Об этом следует помнить, когда Вы хотите показать только часть данных, а часть желаете скрыть! В данном случае следует правильно оценивать уровень секретности информации, которая предоставляется другим лицам. Ответственность за безопасность в первую очередь лежит на Вас.

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

Типичные ошибки при регистрации электронного кабинета – Освіта.UA