gototopgototop

Материалы Excel

Сейчас на сайте

Сейчас 103 гостей онлайн
Вставить в таблицу Excel. Проверка данных в Excel.

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

      Можно настроить проверку данных в Excel, чтобы все заполняли таблицу, бланк, форму правильно. Настроить не только проверку вводимых данных в Excel, но и указать количество вводимых символов, цифр, длину текста, писать целые числа или с дробью, разрешить вводить данные в ячейку только, если будут заполнены другие ячейки, написать правильно код, данные в определенном формате (например, АО-123456), т.д. Как настроить ячейки Excel для ввода различных кодов, читайте в статье "Проверка вводимых данных в Excel".
      Внимание!
Проверка данных не работает, если в ячейку вставляют скопированные данные. Чтобы запретить вставлять скопированные данные сделаем так. Нажимаем кнопку «Office» в левом верхнем углу окна Excel, заходим в «Параметры Excel».  В разделе «Дополнительно» убираем галочку у слов «Разрешить маркеры заполнения и перетаскивание ячеек».
      Если в ячейке написана формула с ошибкой (#ССЫЛКА!  или #ДЕЛО/0!), то проверка данных работать не будет.
      Не работает «Проверка данных», если включен ручной пересчет данных таблицы. Чтобы отключить ручной пересчет данных, заходим на закладке «Формулы» в разделе Вычисление» нажимаем на кнопку «Параметры вычисления». Нажимаем в появившемся списке на слово «Автоматически».
      Если установлен общий доступ к таблице, то сначала его нужно убрать. Установить проверку данных в нужных ячейках, затем установить общий доступ к таблице, бланку. Как установить общий доступ к таблице, чтобы с ней могли работать на нескольких компьютерах, читайте в статье «Общий доступ к файлу Excel» тут.
      В Excel функция «Проверка данных» расположена на закладке «Данные» в разделе «Работа с данными». Нажимаем на кнопку «Проверка данных» и появляется такое окно.
Проверка данных в Excel.На закладке «Параметры» нажимаем на стрелку «Тип данных:». Рассмотрим появившейся список.
      «Любое значение» - это значит, что в ячейку можно написать всё: цифры, буквы, знаки, т.д. Эта функция пригодится, чтобы убрать из ячейки выпадающий список, т.д. – чтобы убрать любую настройку из этого списка. Сделать ячейку обыкновенной.
      Не забудьте изменить формат ячейки. Например, в ячейке стояло ограничение по дате. Мы убрали ограничение по дате, поставив в этой ячейке тип данных «любое значение». Но формат «Дата» в ячейке остался. Поставим формат ячейки «Общий». О форматах ячейки смотрите в статье «Число Excel. Формат».
      «Целое число» -  устанавливает параметры вводимого в ячейку числа. Например, значение «Между» - указываем максимальное и минимальное число, числа между которыми можно написать в ячейке. Можно ограничить цифры года рождения в анкете (от 1930 до 2100, например).
      Значение «Вне» - наоборот, можно написать числа, которые больше указанного максимума и меньше указанного минимума (указано от 10 до 100, значит можно написать любое число, кроме чисел в диапазоне 10-100).      «Действительное число» - можно ввести только число - целое или дробное. Нельзя ввести текст, т.д.
      «Список» - чтобы сделать раскрывающийся список. Подробнее об этом, смотрите в статье «Выпадающий список в Excel» здесь.
      «Дата» - можно установить, чтобы писали конкретную дату (значение - «равно»), дату в определенном диапазоне (значение - «между»), т.д.
      В условиях проверки данных Excel можно написать формулу, ссылаясь на другую ячейку. По значению указанной ячейке будет проверяться ячейка, в которой установили проверку. Например, в ячейку В1 установили текущую дату. А в ячейку В2 установили проверку данных по ячейке В1. Заполнили диалоговое окно проверки данных так.Проверяем, введем в ячейку В2 другую дату. Вышло такое окно.«Время» - устанавливаем время, которое можно писать в ячейке. Например.При таком настрое проверки можно написать время такое: 12:05:00, 12:05:01, … 12:05:59, 12:06. Всё. 12:06:01 – не введется, выйдет сообщение об ошибке.
      Время в диалоговом окне проверки данных пишем через двоеточие – это формат времени.
      Можно установить время формулой. Например, ставим «Время начала» =А1 (в ячейке А1 стоит время). А «Время конца» ставим =А1+''6:00''. Это значит, что можно будет ввести значение данных в ячейку, в которой установили эту проверку данных, с времени из ячейки А1 плюс шесть часов (например, с 12:00 до 12:00+6:00=18:00).
      «Длина текста» - указываем, какое количество символов можно написать в ячейке. Например, номер счета, документа, т.д. Можно установить конкретное число символов (например,7) или диапазон символов, букв, цифр (от 7 до 9).
       «Другой» -  здесь пишем формулу для расчета условий проверки в строке «Формула» диалогового окна «Проверка вводимых значений». Например, установим в ячейке А1 такую формулу условия проверки. =ЕТЕКСТ(А1) Пояснение - в проверке данных функция «ТЕКСТ» не работает, п.э. пишем функцию ЕТЕКСТ.
Мы поставили в ячейке А1 условие, при котором в ячейку можно писать только текст. Цифры не напишутся.
      Еще один пример. У нас такая таблица.
В ячейку В1 установим правило заполнения ячейки, только когда будут заполнены ячейки А1 и А2. Выделяем ячейку В1. Заполним диалоговое окно «Проверка вводимых значений» так.
Формулой мы написали, что, если в ячейке А1 будет стоять число больше 10, а в ячейке А2 будет стоять число меньше 20, то разрешается ввести данные в ячейку В1. Иначе, выйдет сообщение об ошибке. В ячейки А1 и А2 можно вставить формулы других расчетов данных таблицы. Например, в ячейках столбца А идет расчет прихода. А в столбце В1 введем проверкой данных контроль расхода.
      В условия проверки данных в Excel можно вводить логические формулы. 
Например, можно настроить формат ячеек в Excel. Нужно установить в ячейку А1 проверку ввода кода товара. Код всегда должен начинаться с буквы и имеет 6 цифр. Выделяем ячейку А1.  Диалоговое окно «Проверка вводимых данных» заполняем так.
Формулой проверки мы говорим – данные в ячейке А1 должны начинаться и буквы АО и тире, затем количество цифр, не менее 5. Получилось так.
      Если нужно установить проверку данных не в одной ячейке, а в нескольких (в диапазоне, в столбце), то выделяем сразу весь нужный диапазон ячеек. Вызываем диалоговое окно «Проверка данных» и указываем условия проверки, пишем формулы, т.д.
      Если нужно в проверке данных в формуле сделать ссылку на ячейку другого листа, то, сначала, нужно присвоить имя этой ячейке. Как это сделать, смотрите в статье «Присвоить имя в Excel ячейке, диапазону, формуле». Обыкновенную ссылку на ячейку другого листа проверка данных не распознает.
      Если в ячейки с установленной проверкой данных введены неверные значения, то после нажатия клавиши «Enter» выходит стандартное окно предупреждения об ошибке. Но можно настроить окно предупреждения с описанием ошибки.  Для этого в диалоговом оке «Проверка вводимых значений» переходим на закладку «Сообщение об ошибке».      Чтобы сразу ввели данные в ячейку верно, можно написать сообщение (подсказку) в окне «Проверка вводимых значений» в разделе «Сообщение для ввода». Например, так.
      Все функции можно совместить. Например, в ячейке анкеты мы установили, что нужно писать целое число. В сообщении для ввода (в подсказке) объясним, что нужно писать полное количество лет. И в сообщении об ошибке ещё раз объясняем, что они написали не полное количество лет одной цифрой.
      Чтобы найти ячейки, в которые установлена проверка данных, нажимаем на закладке «Главная» в разделе «Редактирование» на кнопку «Найти и выделить». Ячейки с проверкой данных выделятся.
      Чтобы очистить ячейку Excel от настроек проверки данных, нажимаем в диалоговом окне на кнопку «Очистить все» или устанавливаем «Тип данных» - «Любое значение».
      Как настроить проверку данных, чтобы в ячейках писать только рабочие даты, без выходных, смотрите в статье "Проверка даты в Excel".
      В Excel можно проводить анализ данных с помощью диаграмм. Как проанализировать эффективность своей деятельности, провести анализ продаж, выявить причины медленного развития бизнеса, т.д, читайте в статье "Диаграмма Парето в Excel".