Разное

Набор значений ограничен excel как убрать: набор значений которые могут быть введены в ячейку ограничен

14.04.1994

Содержание

набор значений которые могут быть введены в ячейку ограничен

Автор admin На чтение 6 мин Просмотров 6.1к. Опубликовано

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

Условие проверки

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

  1. Нажмите на любую клетку.
  2. Перейдите на вкладку «Данные».
  3. Кликните на выделенный инструмент.
  4. Выберите пункт «Проверка данных».

  1. После этого появится окно, в котором можно будет выбрать формат информации для проверки.

Рассмотрим эти форматы более внимательно.

Любое значение

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

Целое число

Выбрав этот формат, вы сможете отредактировать дополнительную настройку во втором поле.

Например, если выбрать вариант «между», то вы увидите, что в этом окне появятся два дополнительных поля: «Минимум» и «Максимум».

Если выбрать «больше» какой-то величины, то вам нужно будет ввести минимальный допустимый порог. Именно поэтому вы увидите только одно поле – «Минимум», поскольку «Максимум» неограничен.

Действительное

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

Список

Этот формат наиболее интересный.

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

  1. Заполните чем-нибудь несколько клеток. Неважно чем.

  1. Нажмите на какую-нибудь клетку. Перейдите на знакомую вам вкладку. Кликните на иконку «Работа с данными». Выберите выделенный инструмент.

  1. В поле «Тип данных» выберите вариант «Список». Кликните в графу «Источник». Затем выделите нужный диапазон клеток. Так намного удобнее, чем редактировать ссылку вручную. Для продолжения нажмите на «OK».

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

Дата

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

Время

Аналогично и тут. Только здесь указывается одно время (без даты).

Длина текста

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

Вариантов довольно много. Этот способ используется при заполнении каких-нибудь бланков или анкет.

Другой

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

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

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

  1. Повторите описанные выше шаги по вызову окна «Проверка вводимых значений».
  2. Выберите какой-нибудь тип данных. В качестве примера мы укажем, что необходимо вводить «Действительное» число, которое больше 10.

  1. Теперь переходим на вкладку «Сообщение об ошибке».
  2. Выбираем вид отображения:
    • «Останов»;
    • «Предупреждение»;
    • «Сообщение».
  3. Указываем заголовок и текст уведомления.
  4. Для сохранения проверки ввода значений нажмите на «OK»

В качестве примера мы укажем следующие настройки.

  1. Введите любое число. Например, что-нибудь меньше указанного значения. Нажмите на клавишу [knopka]Enter[/knopka]. И вы увидите уведомление о том, что была допущена ошибка ввода данных.

В данном случае в поле «Вид» был указан формат «Сообщение». В этом случае вы можете вводить любые числа. Но каждый раз будете видеть подобное уведомление.

Если сделать тип «Останов», то ввести неверное значение не удастся.

А теперь попробуйте убрать введенные настройки и оставить пустые поля.

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

Но обратите внимание на то, что текст сообщения отличается!

Отличие версий Microsoft Excel

Описанная выше инструкция подходит для современных редакторов 2010, 2013 и 2016 годов. По сравнению со старыми программами существуют некоторые отличия.

Откройте этот же файл в Экселе 2007. Введите неправильное значение. И вы увидите эту ошибку. То есть в современных редакторах текст сообщения по умолчанию был изменён. Привычный всем вид существовал до 2007 года.

В старом Excel 2003 ошибка точно такая же.

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

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

  1. Кликните на любую клетку, для которой вы хотите задать какие-нибудь правила.
  2. Откройте в меню раздел «Данные».
  3. Выберите инструмент «Работа с данными».
  4. Нажмите на иконку «Проверка данных».

  1. В появившемся окне перейдите на вкладку «Сообщение для ввода».
  2. Укажите любой текст в указанных полях.
  3. Для продолжения нажмите на «OK».

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

Как отключить эту ошибку

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

  1. Выберите ячейку, в которой вы не можете указать нужное вам значение.
  2. Перейдите на панели инструментов на вкладку «Данные».
  3. Нажмите на инструмент «Работа с данными».
  4. Кликните на иконку «Проверка данных».

  1. Для того чтобы убрать все настройки, достаточно нажать на кнопку «Очистить всё».
  2. Сохраняем изменения кликом на «OK».

  1. Теперь можно вносить любые данные, словно вы открыли пустой файл и никаких настроек там нет.

Примеры от компании Microsoft

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

Заключение

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

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

Видеоинструкция

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

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

Скачивание примеров

Скачать образец книги со всеми примерами проверки данных из этой статьи

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

Ограничение ввода данных

  1. Выделите ячейки, для которых нужно ограничить ввод данных.

  2. На вкладке Данные щелкните Проверка данных > Проверка данных.

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

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

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

Запрос для пользователей на ввод допустимых значений

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

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

  2. На вкладке Данные щелкните Проверка данных > Проверка данных.

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

  3. На вкладке Подсказка по вводу установите флажок Отображать подсказку, если ячейка является текущей.

  4. В поле Заголовок введите заголовок сообщения.

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

Отображение сообщения об ошибке при вводе недопустимых данных

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

  1. Выберите ячейки, в которых нужно отображать сообщение об ошибке.

  2. На вкладке Данные щелкните Проверка данных > Проверка данных.

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

  3. Н вкладке Сообщение об ошибке в поле Заголовок введите заголовок сообщения.

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

  5. Выполните одно из следующих действий.

    Задача

    В контекстном меню Вид выберите

    Требовать от пользователей исправления ошибки перед продолжением

    Остановка

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

    Предупреждение

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

    Сообщение

Как сделать ограничения в excel?

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

Условие проверки

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

  1. Нажмите на любую клетку.
  2. Перейдите на вкладку «Данные».
  3. Кликните на выделенный инструмент.
  4. Выберите пункт «Проверка данных».

  1. После этого появится окно, в котором можно будет выбрать формат информации для проверки.

Рассмотрим эти форматы более внимательно.

Любое значение

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

Целое число

Выбрав этот формат, вы сможете отредактировать дополнительную настройку во втором поле.

Например, если выбрать вариант «между», то вы увидите, что в этом окне появятся два дополнительных поля: «Минимум» и «Максимум».

Если выбрать «больше» какой-то величины, то вам нужно будет ввести минимальный допустимый порог. Именно поэтому вы увидите только одно поле – «Минимум», поскольку «Максимум» неограничен.

Действительное

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

Список

Этот формат наиболее интересный.

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

  1. Заполните чем-нибудь несколько клеток. Неважно чем.

  1. Нажмите на какую-нибудь клетку. Перейдите на знакомую вам вкладку. Кликните на иконку «Работа с данными». Выберите выделенный инструмент.

  1. В поле «Тип данных» выберите вариант «Список». Кликните в графу «Источник». Затем выделите нужный диапазон клеток. Так намного удобнее, чем редактировать ссылку вручную. Для продолжения нажмите на «OK».

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

Дата

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

Время

Аналогично и тут. Только здесь указывается одно время (без даты).

Длина текста

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

Вариантов довольно много. Этот способ используется при заполнении каких-нибудь бланков или анкет.

Другой

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

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

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

  1. Повторите описанные выше шаги по вызову окна «Проверка вводимых значений».
  2. Выберите какой-нибудь тип данных. В качестве примера мы укажем, что необходимо вводить «Действительное» число, которое больше 10.

  1. Теперь переходим на вкладку «Сообщение об ошибке».
  2. Выбираем вид отображения:
    • «Останов»;
    • «Предупреждение»;
    • «Сообщение».
  3. Указываем заголовок и текст уведомления.
  4. Для сохранения проверки ввода значений нажмите на «OK»

В качестве примера мы укажем следующие настройки.

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

В данном случае в поле «Вид» был указан формат «Сообщение». В этом случае вы можете вводить любые числа. Но каждый раз будете видеть подобное уведомление.

Если сделать тип «Останов», то ввести неверное значение не удастся.

А теперь попробуйте убрать введенные настройки и оставить пустые поля.

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

Но обратите внимание на то, что текст сообщения отличается!

Отличие версий Microsoft Excel

Описанная выше инструкция подходит для современных редакторов 2010, 2013 и 2016 годов. По сравнению со старыми программами существуют некоторые отличия.

Откройте этот же файл в Экселе 2007. Введите неправильное значение. И вы увидите эту ошибку. То есть в современных редакторах текст сообщения по умолчанию был изменён. Привычный всем вид существовал до 2007 года.

В старом Excel 2003 ошибка точно такая же.

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

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

  1. Кликните на любую клетку, для которой вы хотите задать какие-нибудь правила.
  2. Откройте в меню раздел «Данные».
  3. Выберите инструмент «Работа с данными».
  4. Нажмите на иконку «Проверка данных».
  1. В появившемся окне перейдите на вкладку «Сообщение для ввода».
  2. Укажите любой текст в указанных полях.
  3. Для продолжения нажмите на «OK».
  1. Теперь каждый раз, когда бы будете активировать эту ячейку, будете видеть подобную подсказку. Это значительно облегчит ситуацию другим пользователям, которые будут работать с созданными вами файлами.

Как отключить эту ошибку

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

  1. Выберите ячейку, в которой вы не можете указать нужное вам значение.
  2. Перейдите на панели инструментов на вкладку «Данные».
  3. Нажмите на инструмент «Работа с данными».
  4. Кликните на иконку «Проверка данных».
  1. Для того чтобы убрать все настройки, достаточно нажать на кнопку «Очистить всё».
  2. Сохраняем изменения кликом на «OK».
  1. Теперь можно вносить любые данные, словно вы открыли пустой файл и никаких настроек там нет.

Примеры от компании Microsoft

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

Заключение

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

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

Видеоинструкция

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

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

Рис. 1. Состав выпадающего списка зависит от содержания соседней ячейки

Скачать заметку в формате Word или pdf, примеры в формате Excel2007

Команда Проверка данных находится на вкладке Данные, область Работа с данными.

Примечание. Иногда команда Проверка данных может быть недоступна:

  • Возможно, в настоящее время вводятся данные. Во время ввода данных в ячейку команда Проверка данных недоступна. Чтобы завершить ввод данных, нажмите клавишу ВВОД или ESC.
  • Возможно, лист защищен или является общим. Если лист защищен или является общим, изменить параметры проверки данных невозможно. Снимите защиту или отмените режим «общий».
  • Возможно, таблица Excel связана с узлом SharePoint. Невозможно добавить проверку данных в таблицу Excel, которая связана с узлом SharePoint. Чтобы добавить проверку данных, необходимо удалить связь таблицы Excel или преобразовать ее в диапазон.

К сожалению, Excel в своем стандарте позволяет делать списки только на основе:

  • имени массива
  • диапазона ячеек
  • прямого перечисления элементов списка (рис. 2).

Примечание. Элементы списка вводите через стандартный разделитель элементов списка Microsoft Windows (в русском Excel по умолчанию это точка с запятой).

Рис. 2. Возможные источники списка: вверху – имя массива; посередине – диапазон ячеек; внизу – элементы списка

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

Рис. 3. Недопустимый источник списка – формула

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

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

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

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

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

И всё же в Excel есть одна функция непрямого действия. На английском языке у нее говорящее название – INDIRECT. На русском – название функции ни о чем – ДВССЫЛ… В чем же заключается непрямое действие? В отличие от других функций Excel, ДВССЫЛ возвращает не значение, хранящееся в ячейке, а ссылку, хранящуюся в ячейке. Непонятно? Сам «продирался» через это с трудом 🙂 Попробую пояснить. Вот что написано в справке Excel: ДВССЫЛ – возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого (рис. 4).

Рис. 4. Как работает функция ДВССЫЛ: вверху – формулы, внизу – значения

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

Итак, еще раз, ДВССЫЛ возвращает ссылку, а не значение, хранящееся в ячейке. Ссылка немедленно вычисляется, и выводится ее значение (или содержимое). Именно это свойство позволит нам ввести непрямую ссылку на соседнюю ячейку так, что вернется не значение, хранящееся в этой соседней ячейке (как на рис. 3), а ссылка, хранящаяся в ячейке, эта ссылка тут же вычисляется, и получается имя массива (рис. 5).

Рис. 5. Формирование списка, зависящего от значения в левой ячейке

Примечание. Ссылка в формуле =ДВССЫЛ(D2) должна быть относительной (D2), а не абсолютной ($D$2). Подробнее об этом см. раздел Тип ссылок на ячейки в формулах для проверки данных заметки Excel. Проверка данных.

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

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

  1. Выделите диапазон В2:В8.

Примечание: Ячейка В10 содержит функцию SUM (СУММ), которая вычисляет сумму ячеек диапазона B2:B8.

  1. На вкладке Data (Данные) нажмите кнопку Data Validation (Проверка данных).
  2. Выберите пункт Custom (Другой) из выпадающего списка Allow (Тип данных).
  3. Введите формулу, показанную ниже, в поле Formula (Формула) и нажмите ОК.

    =SUM($B$2:$B$8)

Как создать или удалить выпадающий список в Excel

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

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

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

Создание раскрывающихся списков

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

Техническая сторона вопроса

Перед тем, как сделать выпадающий список в Excel, сформируйте на листе в диапазоне ячеек необходимые варианты. Проследите за тем, чтобы в этом перечне не было пустых строк, иначе Эксель не сможет создать нужный объект на листе. Введённые значения в строках можно отсортировать по алфавиту. Для этого найдите в Ленте настроек вкладку данные и нажмите на «Сортировку». Когда работа с данными у вас закончится, выделите нужный диапазон. В нём не должно быть пустых строк, это важно! Программа не сможет создать список с пустым элементом внутри себя, потому что пустая строка не будет восприниматься в качестве данных для выбора. При этом перечень данных вы можете сформировать и на другом листе, не только на том, где будет располагаться поле ввода. Допустим, вы не хотите, чтобы они были доступны для редактирования другим пользователям. Тогда имеет смысл расположить их на скрытом листе.

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

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

Удаление списка

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

Заключение

Алгоритм создания таких объектов прост. Перед тем, как создать в Excel раскрывающийся список, сформируйте перечень значений, при необходимости отформатируйте так, как вам удобно. Обратите внимание на 2 нюанса. Первый: длина диапазона данных ограничена, пороговое значение 32767 элементов, второй: длина всплывающего окна будет определена длиной пунктов из перечня. Располагая этот объект на странице, вы упростите ввод данных от других пользователей. Их использование положительно влияет на скорость и точность работы, помогает упростить формулы, работающие в документе, и разрешает проблему неодинакового форматирования текстовых данных. Но если вы используете в книге Экселя Microsoft Share Point, создать выпадающий список будет невозможно, что связано с ограничениями в работе публикующей программы.

Как ограничить ввод данных в ячейку excel

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

Как часть ограничения ввода данных на число (которое может быть целым числом, десятичной, датой или временем), вы также указываете допустимые значения для этого типа номера (целое число от 10 до 100 или дата между 1 января 2012 года и 31 декабря 2012 года, например).

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

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

Чтобы использовать функцию проверки данных, поместите курсор ячейки в ячейку, где вы хотите ограничить тип ввода данных, который вы можете там сделать, а затем нажмите кнопку «Проверка данных» на вкладке «Данные» ленты (или нажмите Alt + AVV). Откроется диалоговое окно «Проверка данных» с выбранной вкладкой «Настройки».

Затем щелкните раскрывающуюся кнопку, прикрепленную к раскрывающемуся списку Разрешить, и выберите один из следующих элементов:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы найти ячейки, к которым была применена проверка данных, откройте диалоговое окно «Перейти» (Ctrl + G или F5), а затем нажмите кнопку «Специальная» и нажмите кнопку «Проверка данных» в диалоговом окне «Перейти к специальному».

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

Чтобы избавиться от настроек проверки данных, назначенных для определенного диапазона ячеек или ячеек, выберите ячейку или диапазон, откройте диалоговое окно «Проверка данных» (Alt + AVV), а затем нажмите кнопку «Очистить все», прежде чем нажимать «ОК».

Автор: rf-webmaestro · Опубликовано 11.05.2018 · Обновлено 08.02.2019

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

Условие проверки

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

  1. Нажмите на любую клетку.
  2. Перейдите на вкладку «Данные».
  3. Кликните на выделенный инструмент.
  4. Выберите пункт «Проверка данных».

  1. После этого появится окно, в котором можно будет выбрать формат информации для проверки.

Рассмотрим эти форматы более внимательно.

Любое значение

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

Целое число

Выбрав этот формат, вы сможете отредактировать дополнительную настройку во втором поле.

Например, если выбрать вариант «между», то вы увидите, что в этом окне появятся два дополнительных поля: «Минимум» и «Максимум».

Если выбрать «больше» какой-то величины, то вам нужно будет ввести минимальный допустимый порог. Именно поэтому вы увидите только одно поле – «Минимум», поскольку «Максимум» неограничен.

Действительное

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

Список

Этот формат наиболее интересный.

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

  1. Заполните чем-нибудь несколько клеток. Неважно чем.

  1. Нажмите на какую-нибудь клетку. Перейдите на знакомую вам вкладку. Кликните на иконку «Работа с данными». Выберите выделенный инструмент.

  1. В поле «Тип данных» выберите вариант «Список». Кликните в графу «Источник». Затем выделите нужный диапазон клеток. Так намного удобнее, чем редактировать ссылку вручную. Для продолжения нажмите на «OK».

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

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

Время

Аналогично и тут. Только здесь указывается одно время (без даты).

Длина текста

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

Вариантов довольно много. Этот способ используется при заполнении каких-нибудь бланков или анкет.

Другой

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

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

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

  1. Повторите описанные выше шаги по вызову окна «Проверка вводимых значений».
  2. Выберите какой-нибудь тип данных. В качестве примера мы укажем, что необходимо вводить «Действительное» число, которое больше 10.

  1. Теперь переходим на вкладку «Сообщение об ошибке».
  2. Выбираем вид отображения:
  3. «Останов»;
  4. «Предупреждение»;
  5. «Сообщение».
  6. Указываем заголовок и текст уведомления.
  7. Для сохранения проверки ввода значений нажмите на «OK»

В качестве примера мы укажем следующие настройки.

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

В данном случае в поле «Вид» был указан формат «Сообщение». В этом случае вы можете вводить любые числа. Но каждый раз будете видеть подобное уведомление.

Если сделать тип «Останов», то ввести неверное значение не удастся.

А теперь попробуйте убрать введенные настройки и оставить пустые поля.

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

Но обратите внимание на то, что текст сообщения отличается!

Отличие версий Microsoft Excel

Описанная выше инструкция подходит для современных редакторов 2010, 2013 и 2016 годов. По сравнению со старыми программами существуют некоторые отличия.

Откройте этот же файл в Экселе 2007. Введите неправильное значение. И вы увидите эту ошибку. То есть в современных редакторах текст сообщения по умолчанию был изменён. Привычный всем вид существовал до 2007 года.

В старом Excel 2003 ошибка точно такая же.

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

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

  1. Кликните на любую клетку, для которой вы хотите задать какие-нибудь правила.
  2. Откройте в меню раздел «Данные».
  3. Выберите инструмент «Работа с данными».
  4. Нажмите на иконку «Проверка данных».

  1. В появившемся окне перейдите на вкладку «Сообщение для ввода».
  2. Укажите любой текст в указанных полях.
  3. Для продолжения нажмите на «OK».

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

Как отключить эту ошибку

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

  1. Выберите ячейку, в которой вы не можете указать нужное вам значение.
  2. Перейдите на панели инструментов на вкладку «Данные».
  3. Нажмите на инструмент «Работа с данными».
  4. Кликните на иконку «Проверка данных».

  1. Для того чтобы убрать все настройки, достаточно нажать на кнопку «Очистить всё».
  2. Сохраняем изменения кликом на «OK».

  1. Теперь можно вносить любые данные, словно вы открыли пустой файл и никаких настроек там нет.

Примеры от компании Microsoft

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

Заключение

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

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

Видеоинструкция

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

В этом курсе:

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

Скачивание примеров

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

Выберите данные >проверка данных.

На вкладке Параметры в списке Тип данных выберите подходящий вариант:

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

» Дес .», чтобы ограничить ячейки как допустимые только десятичными числами.

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

Дата, чтобы можно было ввести только дату.

Time (время ): Ограничьте ячейки, чтобы она принимала только время.

Длина текста, чтобы ограничить длину текста.

Другой, чтобы задать настраиваемую формулу.

В списке Значение выберите условие:

больше или равно

меньше или равно

На вкладке Параметры в списке Тип данных выберите подходящий вариант:

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

Установите флажок Игнорировать пустые ячейки, если нужно пропускать пустые ячейки.

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

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

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

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

  • Какую версию вы используете?
  • Более новые версии
  • Office 2011

Ограничение ввода данных

Выделите ячейки, для которых нужно ограничить ввод данных.

На вкладке данные нажмите кнопку Проверка данных > Проверка данных.

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

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

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

Запрос на ввод допустимых записей

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

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

На вкладке данные нажмите кнопку Проверка данных > Проверка данных.

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

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

В поле название введите название сообщения.

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

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

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

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

На вкладке данные нажмите кнопку Проверка данных > Проверка данных.

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

На вкладке сообщение об ошибке в поле название введите название сообщения.

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

Выполните одно из следующих действий:

На вкладке Style (стиль ) всплывающее меню выберите

Требовать от пользователей исправить ошибку перед продолжением

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

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

Ограничение ввода данных

Выделите ячейки, для которых нужно ограничить ввод данных.

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

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

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

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

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

Запрос на ввод допустимых записей

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

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

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

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

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

В поле название введите название сообщения.

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

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

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

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

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

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

На вкладке сообщение об ошибке в поле название введите название сообщения.

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

Выполните одно из следующих действий:

На вкладке Style (стиль ) всплывающее меню выберите

Требовать от пользователей исправить ошибку перед продолжением

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

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

Добавление проверки данных в ячейку или диапазон ячеек

Примечание: Первые два шага, описанные в этом разделе, предназначены для добавления проверки данных любого типа. Шаги 3-7 предназначены специально для создания раскрывающегося списка.

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

На вкладке Данные в группе Работа с данными нажмите кнопку Проверка данных.

На вкладке Параметры в разделе Разрешить нажмите кнопку Список.

В поле Источник введите значения, разделенные точкой с запятой. Например, введите » низкий», «средний», «высокий».

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

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

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

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

Удаление проверки данных — выделите ячейки, содержащие условия, которые вы хотите удалить, а затем перейдите к разделу данные > проверка данных и в диалоговом окне Проверка данных нажмите кнопку Очистить все , а затем нажмите кнопку ОК.

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

Разрешить вводить только целые числа из определенного диапазона

Выполните действия, описанные в 1-2 выше.

В списке Разрешить выберите значение Целое число.

В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон.

Введите минимальное, максимальное или определенное разрешенное значение.

Можно также ввести формулу, которая возвращает числовое значение.

Например, допустим, что вы проверяете значения в ячейке F1. Чтобы задать минимальный объем вычетов, равный значению этой ячейки, умноженному на 2, выберите пункт Больше или равно в поле Данные и введите формулу =2*F1 в поле Минимальное значение.

Разрешить вводить только десятичные числа из определенного диапазона

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите значение Десятичный.

В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон.

Введите минимальное, максимальное или определенное разрешенное значение.

Можно также ввести формулу, которая возвращает числовое значение. Например, для задания максимального значения комиссионных и премиальных в размере 6% от заработной платы продавца в ячейке E1 выберите пункт Меньше или равно в поле Данные и введите формулу =E1*6% в поле Максимальное значение.

Примечание: Чтобы предоставить пользователю возможность вводить процентные значения, например 20%, в поле Разрешить выберите пункт десятичные , выберите необходимый тип ограничения в поле данные , введите минимальное, максимальное или конкретное значение в десятичном формате, например . 2, а затем Выбери ячейку для проверки данных в процентах, выделяя ячейку и выбирая процентный формат в группе число на вкладке Главная .

Разрешить вводить только даты в заданном интервале времени

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите значение Дата.

В поле Данные выберите необходимый тип ограничения. Например, для разрешения даты после определенного дня выберите ограничение Больше.

Введите начальную, конечную или определенную разрешенную дату.

Вы также можете ввести формулу, которая возвращает дату. Например, чтобы задать интервал времени между текущей датой и датой через 3 дня после текущей, выберите пункт Между в поле Данные, потом введите =СЕГОДНЯ() в поле Дата начала и затем введите =СЕГОДНЯ()+3 в поле Дата завершения.

Разрешить вводить только время в заданном интервале

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите значение Время.

В поле Данные выберите необходимый тип ограничения. Например, для разрешения времени до определенного времени дня выберите ограничение меньше.

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

Например, если в ячейке E2 задано время начала (8:00), а в ячейке F2 — время окончания (17:00) и вы хотите ограничить собрания этим промежутком, выберите между в поле Данные, а затем введите =E2 в поле Время начала и =F2 в поле Время окончания.

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

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите значение Длина текста.

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

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

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

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите необходимый тип данных.

В поле Данные выберите необходимый тип ограничения.

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

Например, чтобы допустить ввод сведений для счета только тогда, когда итог не превышает бюджет в ячейке E1, выберите значение Число десятичных знаков в списке Разрешить, ограничение «Меньше или равно» в списке «Данные», а в поле Максимальное значение введите >= =E1.

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

Снимки экрана в этой статье взяты Excel 2016; но функциональность одинакова в Excel Online.

Значение в ячейке, содержащей код продукта (C2), всегда начинается со стандартного префикса «ID-» и имеет длину не менее 10 (более 9) знаков.

= И (LEFT (C2; 3) = «ID-«; ДЛСТР (C2) >9)

Ячейки, содержащие название товара (D2), содержат только текст.

Значение в ячейке, содержащей чью-то дату рождения (B6), было больше числа лет, указанного в ячейке B4.

Примечание: Необходимо сначала ввести формулу проверки данных в ячейку A2, а затем скопировать эту ячейку в ячейки A3:A10 так, чтобы второй аргумент СЧЁТЕСЛИ соответствовал текущей ячейке. Часть A2)=1 изменится на A3)=1, A4)=1 и т. д.

Адрес электронной почты в ячейке B4 содержал символ @.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

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

Как удалить выпадающий список в Excel

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

Содержание

Использование выпадающих списков


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

Процедура создания


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

  1. Выделяем столбец табличного массива, в ячейках которого планируется разместить выпадающий список. Передвигаемся во вкладку «Данные» и клацаем по кнопке «Проверка данных». Она локализирована на ленте в блоке «Работа с данными».
  2. Запускается окно инструмента «Проверка значений». Переходим в раздел «Параметры». В области «Тип данных» из перечня выбираем вариант «Список». После этого перемещаемся к полю «Источник». Тут нужно указать группу наименований, предназначенную для использования в списке. Эти названия можно внести вручную, а можно указать на них ссылку, если они уже размещены в документе Excel в другом месте.

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

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

    Ещё один вариант установки связи – это присвоение массиву с перечнем имени. Выделяем диапазон, в котором указаны значения данных. Слева от строки формул находится область имён. По умолчанию в нём при выделении диапазона отображается координаты первой выделенной ячейки. Мы же для наших целей просто вписываем туда наименование, которое считаем более подходящим. Главные требования к имени состоят в том, чтобы оно было уникальным в пределах книги, не имело пробелов и обязательно начиналось с буквы. Теперь именно по данному наименованию будет идентифицироваться тот диапазон, который мы до этого выделили.

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

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

    Для того, чтобы преобразовать диапазон в «умную» таблицу, выделяем его и передвигаемся во вкладку «Главная». Там клацаем по кнопке «Форматировать как таблицу», которая размещена на ленте в блоке «Стили». Открывается большая группа стилей. На функциональность таблицы выбор конкретного стиля никак не влияет, а поэтому выбираем любой из них.

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

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

    Если перечень размещен в другой книге, то для корректного его отражения требуется применить функцию ДВССЫЛ. Указанный оператор предназначен для того, чтобы формировать «суперабсолютные» ссылки на элементы листа в текстовом виде. Собственно при этом процедура будет выполняться практически точно такая же, как и в ранее описываемых случаях, только в области «Источник» после символа «=» следует указать наименование оператора – «ДВССЫЛ». После этого в скобках в качестве аргумента данной функции должен быть указан адрес диапазона, включая наименование книги и листа. Собственно, как показано на изображении ниже.

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

Урок: Как сделать выпадающий список в Экселе

Выполнение операций


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

  1. Если мы установим курсор на любой элемент листа, к которому был применен ниспадающий список, то увидим информационное сообщение, введенное нами ранее в окне проверки данных. Кроме того, справа от ячейки появится пиктограмма в виде треугольника. Именно она служит для доступа к выбору списочных элементов. Клацаем по данному треугольнику.
  2. После клика по нему меню из списочных объектов будет открыто. В нем расположены все элементы, которые были ранее внесены через окно проверки данных. Выбираем тот вариант, который считаем нужным.
  3. Выбранный вариант отобразится в ячейке.
  4. Если же мы попытаемся ввести в ячейку любое значение, которое отсутствует в перечне, то данное действие заблокируется. При этом, если вы вносили сообщение предупреждения в окно проверки данных, то оно отобразиться на экране. Нужно в окне предупреждения нажать на кнопку «Отмена» и со следующей попытки ввести корректные данные.

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

Добавление нового элемента


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

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

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

  2. Запускается окошко, где следует произвести выбор объекта вставки. Выбираем вариант «Строку» и жмем на кнопку «OK».
  3. Итак, пустая строка добавлена.
  4. Вписываем в неё то значение, которое желаем, чтобы отобразилось в ниспадающем списке.
  5. После этого возвращаемся к тому табличному массиву, в котором размещен раскрывающийся перечень. Кликнув по треугольнику, справа от любой ячейки массива, мы видим, что к уже имеющимся списочным элементам было добавлено нужное нам значение. Теперь при желании его можно выбрать, чтобы вставить в элемент таблицы.

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

  1. Выделяем весь табличный диапазон, в элементах которого размещен выпадающий перечень. Переходим во вкладку «Данные» и снова кликаем по кнопке «Проверка данных» в группе «Работа с данными».
  2. Запускается окно проверки вводимых данных. Перемещаемся в раздел «Параметры». Как видим, все настройки тут точно такие же, как мы их выставили ранее. Нас в данном случае будет интересовать область «Источник». Добавляем туда к уже имеющему перечню через точку с запятой (;) то значение или значения, которые хотим увидеть в выпадающем списке. После добавления клацаем по «OK».
  3. Теперь, если мы откроем выпадающий перечень в табличном массиве, то увидим там добавленное значение.

Удаление элемента


Удаление списочного элемента проводится по точно такому же алгоритму, что и добавление.

  1. Если данные подтягиваются из табличного массива, то тогда переходим к этой таблице и клацаем правой кнопкой мыши по ячейке, где расположено значение, которое следует удалить. В контекстном меню останавливаем выбор на варианте «Удалить…».
  2. Открывается окно удаления ячеек практически аналогичное тому, которое мы видели при их добавлении. Тут снова устанавливаем переключатель в позицию «Строку» и клацаем по «OK».
  3. Строка из табличного массива, как видим, удалена.
  4. Теперь возвращаемся к той таблице, где находятся ячейки с выпадающим перечнем. Клацаем по треугольнику справа от любой ячейки. В раскрывшемся списке видим, что удаленный элемент отсутствует.

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

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

Полное удаление


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

  1. Выделяем весь массив, где расположен раскрывающийся перечень. Передвигаемся во вкладку «Главная». Щелкаем по иконке «Очистить», которая размещается на ленте в блоке «Редактирование». В открывшемся меню выбираем позицию «Очистить все».
  2. При выборе данного действия в выделенных элементах листа будут удалены все значения, очищено форматирование, а кроме того, достигнута главная цель поставленной задачи: раскрывающийся перечень будет удален и теперь в ячейки можно вводить любые значения вручную.

Кроме того, если пользователю не нужно сохранять введенные данные, то существует ещё один вариант удалить ниспадающий перечень.

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

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

    Ещё проще сразу после выделения применить набор кнопок Ctrl+C.

  2. После этого выделяем тот фрагмент табличного массива, где располагаются выпадающие элементы. Жмем на кнопку «Вставить», локализированную на ленте во вкладке «Главная» в разделе «Буфер обмена».

    Второй вариант действий – это щелкнуть по выделению правой кнопкой мышки и остановить выбор на варианте «Вставить» в группе «Параметры вставки».

    Наконец, есть возможность просто обозначить нужные ячейки и набрать сочетание кнопок Ctrl+V.

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

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

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

  1. Выделяем весь фрагмент, в котором расположены элементы с раскрывающимся перечнем. Передвигаемся во вкладку «Данные» и клацаем по значку «Проверка данных», который, как мы помним, размещен на ленте в группе «Работа с данными».
  2. Открывается уже хорошо знакомое нам окно проверки вводимых данных. Находясь в любом разделе указанного инструмента, нам нужно совершить единственное действие — нажать на кнопку «Очистить всё». Она располагается в нижнем левом углу окна.
  3. После этого окно проверки данных можно закрывать, нажав на стандартную кнопку закрытия в его верхнем правом углу в виде крестика или на кнопку «OK» в нижней части окна.
  4. Затем выделяем любую из ячеек, в которой раньше был размещен раскрывающийся перечень. Как видим, теперь нет ни подсказки при выделении элемента, ни треугольника для вызова списка справа от ячейки. Но при этом нетронутым осталось форматирование и все до этого введенные значения с помощью списка. Это означает, что с поставленной задачей мы справились успешно: инструмент, который больше нам не нужен, удален, но результаты его работы остались целыми.

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

Проверка данных в Excel — ОфисГуру

Автор Антон Андронов На чтение 2 мин Опубликовано

Используйте проверку данных в 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. Введите сообщение об ошибке.

  4. Нажмите ОК.

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

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

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

    Результат:

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

Оцените качество статьи. Нам важно ваше мнение:

Применить проверку данных к ячейкам

Загрузите наши примеры

Загрузите пример книги со всеми примерами проверки данных в этой статье.

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

Ограничить ввод данных

  1. Выберите ячейки, в которых вы хотите ограничить ввод данных.

  2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

  3. В поле Разрешить выберите тип данных, которые вы хотите разрешить, и заполните ограничивающие критерии и значения.

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

Запрашивать у пользователей действительные записи

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

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

  2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

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

  4. В поле Заголовок введите заголовок сообщения.

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

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

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

  1. Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.

  2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

  3. На вкладке Оповещение об ошибке в поле Заголовок введите заголовок сообщения.

  4. В поле Сообщение об ошибке введите сообщение, которое должно отображаться при вводе неверных данных.

  5. Выполните одно из следующих действий:

    от до

    На Стиль всплывающее меню , выберите

    Потребовать от пользователей исправить ошибку перед продолжением

    Стоп

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

    Предупреждение

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

    Важно

Удалить раскрывающийся список

  1. Выберите ячейку из раскрывающегося списка.

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

  2. Щелкните Данные > Проверка данных .

  3. На вкладке Настройки щелкните Очистить все .

  4. Нажмите ОК

Если вам нужно удалить все проверки данных с рабочего листа, включая раскрывающиеся списки, но вы не знаете, где они находятся, вы можете использовать диалоговое окно Перейти к специальному . Нажмите Ctrl+G > Special , затем Data Validation > All или Same и повторите описанные выше шаги.

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

  1. Выберите ячейку из раскрывающегося списка.

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

  2. Щелкните Данные > Проверка данных .

  3. На вкладке Настройки щелкните Очистить все .

  4. Нажмите ОК

Если вам нужно удалить все проверки данных с рабочего листа, включая раскрывающиеся списки, но вы не знаете, где они находятся, вы можете использовать диалоговое окно Перейти к специальному .Нажмите Ctrl+G > Special , затем Data Validation > All или Same и повторите описанные выше шаги.

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

  1. Выберите ячейки из раскрывающегося списка.

  2. Щелкните Данные > Проверка данных .

  3. На вкладке Настройки щелкните Очистить все .

  4. Нажмите OK .

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

Как исправить #ЗНАЧ! ошибка

Сделайте столбец даты шире. Если ваша дата выровнена по правому краю, то это дата. Но если он выровнен по левому краю, это означает, что дата на самом деле не является датой. Это текст. И Excel не распознает текст как дату. Вот несколько решений, которые могут решить эту проблему.

Проверка на начальные пробелы

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

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

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

  3. Выберите столбец, содержащий дату, щелкнув его заголовок.

  4. Щелкните Данные > Текст в столбцы .

  5. Дважды щелкните Далее .

  6. На шаге 3 из 3 мастера в разделе Формат данных столбца щелкните Дата .

  7. Выберите формат даты и нажмите Готово .

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

Проверьте настройки даты вашего компьютера

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

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

У этой проблемы есть два решения: Вы можете изменить систему дат, которую использует ваш компьютер, чтобы она соответствовала системе дат, которую вы хотите ввести в Excel.Или в Excel вы можете создать новый столбец и использовать функцию ДАТА для создания истинной даты на основе даты, сохраненной в виде текста. Вот как это сделать, если система дат вашего компьютера — мм/дд/гггг, а ваша текстовая дата — 31/12/2017 в ячейке A1:

.
  1. Создайте следующую формулу: =ДАТА(ПРАВО(A1,4),СРЕДИНА(A1,4,2),ЛЕВО(A1,2))

  2. Результат будет 31.12.2017 .

  3. Если вы хотите, чтобы формат отображался как дд/мм/гг, нажмите CTRL+1 (или + 1 на Mac).

  4. Выберите другую локаль, использующую формат дд/мм/гг, например, Английский (Великобритания) . Когда вы закончите применять формат, результатом будет 31/12/2017 , и это будет настоящая дата, а не текстовая дата.

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

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

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

Когда полезна проверка данных?

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

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

  • Ограничить числа за пределами указанного диапазона — Например, вы можете указать максимальный процент ввода для ежегодного повышения заслуг сотрудника, скажем, 3%, или разрешить только целое число от 1 до 100.

  • Ограничить даты вне определенного периода времени — Например, в запросе на отгул сотрудника вы можете запретить кому-либо выбирать дату до сегодняшней даты.

  • Ограничить время за пределами определенного периода времени — Например, вы можете указать расписание собрания с 8:00 до 17:00.

  • Ограничить количество текстовых символов — Например, вы можете ограничить разрешенный текст в ячейке до 10 или менее символов.

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

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

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

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

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

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

Вы можете выбрать один из трех типов предупреждений об ошибках:

Значок

Тип

Использовать до

Стоп

Запретить пользователям вводить неверные данные в ячейку.

Предупреждающее сообщение Stop имеет две опции: Повторить или Отмена .

Предупреждение

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

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

Информация

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

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

Советы по работе с проверкой данных

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

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

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

  • Если вы планируете защитить лист или книгу, защитите их после того, как закончите указывать все параметры проверки. Убедитесь, что вы разблокировали все проверенные ячейки, прежде чем защищать лист. В противном случае пользователи не смогут вводить какие-либо данные в ячейки. См. Защита рабочего листа.

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

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

    Чтобы применить круги, выберите ячейки, которые вы хотите оценить, и перейдите к Данные > Инструменты для работы с данными > Проверка данных > Обведите неверные данные .

  • Чтобы быстро удалить проверку данных для ячейки, выберите ее, а затем перейдите к Данные > Инструменты данных > Проверка данных > Настройки > Очистить все .

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

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

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

  • Если проверка данных не работает, убедитесь, что:

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

    • Ручной пересчет отключен. — Если ручной пересчет включен, невычисленные ячейки могут помешать правильной проверке данных. Чтобы отключить ручной пересчет, перейдите на вкладку Формулы > группу Расчет > Параметры расчета > щелкните Автоматически .

    • Формулы не содержат ошибок — Убедитесь, что формулы в проверенных ячейках не вызывают ошибок, таких как #ССЫЛКА! или #ДЕЛ/0!.Excel игнорирует проверку данных, пока вы не исправите ошибку.

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

    • Таблица Excel может быть связана с сайтом SharePoint — Вы не можете добавить проверку данных в таблицу Excel, связанную с сайтом SharePoint.Чтобы добавить проверку данных, необходимо отменить связь таблицы Excel или преобразовать таблицу Excel в диапазон.

    • Возможно, вы сейчас вводите данные — Команда проверки данных недоступна, пока вы вводите данные в ячейку. Чтобы завершить ввод данных, нажмите Enter или ESC для выхода.

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

Как обновить или удалить проверку данных в унаследованной книге

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

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

Нужна дополнительная помощь?

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

Спецификации и ограничения Excel

Спецификации и ограничения рабочих листов и рабочих книг

Функция

Максимальный предел

Открытые книги

Ограничен доступной памятью и системными ресурсами

Общее количество строк и столбцов на листе

1 048 576 строк по 16 384 столбца

Ширина столбца

255 символов

Высота строки

409 баллов

Разрывы страниц

1026 горизонтальный и вертикальный

Общее количество символов, которое может содержать ячейка

32 767 символов

символов в верхнем или нижнем колонтитуле

255

Максимальное количество переводов строки на ячейку

253

листов в книге

Ограничен доступной памятью (по умолчанию 1 лист)

Цвета в книге

16 миллионов цветов (32 бита с полным доступом к 24-битному цветовому спектру)

Именованные представления в книге

Ограничен доступной памятью

Уникальные форматы/стили ячеек

65 490

Стили заливки

256

Толщина линий и стили

256

Уникальные типы шрифтов

1024 глобальных шрифта, доступных для использования; 512 на книгу

Числовые форматы в книге

От 200 до 250, в зависимости от установленной языковой версии Excel

Имена в книге

Ограничен доступной памятью

Окна в книге

Ограничен доступной памятью

Гиперссылки на листе

65 530

Стекла в окне

4

Связанные листы

Ограничен доступной памятью

Сценарии

Ограничен доступной памятью; в сводном отчете показаны только первые 251 сценарий

Изменение ячеек в сценарии

32

Настраиваемые ячейки в Solver

200

Пользовательские функции

Ограничен доступной памятью

Диапазон увеличения

от 10 до 400 процентов

Отчеты

Ограничен доступной памятью

Сортировать ссылки

64 в одном сорте; неограниченно при использовании последовательных сортировок

Уровни отмены

100

Поля в форме данных

32

Параметры рабочей книги

255 параметров на рабочую книгу

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

10 000

Несмежные ячейки, которые можно выбрать

2 147 483 648 ячеек

Максимальные ограничения на объем памяти и размер файла для рабочих книг модели данных

32-разрядная среда зависит от 2 гигабайт (ГБ) виртуального адресного пространства, совместно используемого Excel, книгой и надстройками, которые выполняются в одном процессе.Доля модели данных в адресном пространстве может достигать 500–700 мегабайт (МБ), но может быть меньше, если загружены другие модели данных и надстройки.

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

Начиная с Excel 2016, функция Large Address Aware позволяет 32-разрядному Excel потреблять в два раза больше памяти, когда пользователи работают в 64-разрядной операционной системе Windows.Дополнительные сведения см. в статье Изменение возможности поддержки больших адресов для Excel.

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

Дополнительные сведения см. в разделе Спецификация и ограничения модели данных.

Ядра процессора

64

Длина имени файла

218 символов — включая путь к файлу.Например, C:\Username\Documents\FileName.xlsx.

Расчетные спецификации и пределы

Функция

Максимальный предел

Точность числа

15 цифр

Наименьшее допустимое отрицательное число

-2.2251Э-308

Наименьшее допустимое положительное число

2.2251Е-308

Наибольшее допустимое положительное число

9.99999999999999E+307

Наибольшее допустимое отрицательное число

-9,99999999999999E+307

Наибольшее допустимое положительное число по формуле

1.7976

8623158e+308

Наибольшее допустимое отрицательное число по формуле

-1,7976

8623158e+308

Длина содержания формулы

8 192 символа

Внутренняя длина формулы

16 384 байта

Итерации

32 767

Массивы рабочих листов

Ограничен доступной памятью

Выбранные диапазоны

2 048

Аргументы в функции

255

Вложенные уровни функций

64

Определенные пользователем категории функций

255

Количество доступных функций рабочего листа

341

Размер стека операндов

1 024

Зависимость между рабочими листами

64 000 рабочих листов, которые могут ссылаться на другие листы

Зависимость формулы массива между рабочими листами

Ограничен доступной памятью

Зависимость от области

Ограничен доступной памятью

Зависимость области на рабочий лист

Ограничен доступной памятью

Зависимость от одной ячейки

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

Длина содержимого связанной ячейки из закрытых книг

32 767

Самая ранняя дата, разрешенная для расчета

1 января 1900 г. (1 января 1904 г., если используется система дат 1904 г.)

Последняя дата, разрешенная для расчета

31 декабря 9999

Максимальное количество времени, которое можно ввести

9999:59:59

Технические характеристики и ограничения

Функция

Максимальный предел

Диаграммы, связанные с рабочим листом

Ограничен доступной памятью

Рабочие листы, на которые ссылается диаграмма

255

Серия данных на одной диаграмме

255

Точки данных в серии данных для двумерных диаграмм

Ограничен доступной памятью

Точки данных в серии данных для трехмерных диаграмм

Ограничен доступной памятью

Точки данных для всех рядов данных на одной диаграмме

Ограничен доступной памятью

Спецификации и ограничения сводных таблиц и сводных диаграмм

Функция

Максимальный предел

Отчеты сводной таблицы на листе

Ограничен доступной памятью

Уникальных элементов на поле

1 048 576

Поля строки или столбца в отчете сводной таблицы

Ограничен доступной памятью

Фильтры отчетов в отчете сводной таблицы

256 (может быть ограничено доступной памятью)

Поля значений в отчете сводной таблицы

256

Формулы вычисляемых элементов в отчете сводной таблицы

Ограничен доступной памятью

Фильтры отчетов в отчете сводной диаграммы

256 (может быть ограничено доступной памятью)

Поля значений в отчете сводной диаграммы

256

Формулы вычисляемых элементов в отчете сводной диаграммы

Ограничен доступной памятью

Длина имени MDX для элемента сводной таблицы

32 767

Длина строки реляционной сводной таблицы

32 767

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

10 000

Книги с параметром «Разрешить внесение изменений более чем одним пользователем…» настройка включена

Если для книги включен параметр Разрешить изменения более чем одному пользователю… , применяется следующая информация. Этот параметр доступен, если щелкнуть вкладку Review > Share Workbook . Обратите внимание, что в более новых версиях Excel кнопка «Поделиться книгой » была скрыта. Чтобы отобразить его, нажмите Файл > Параметры > Панель быстрого доступа .Откройте список под Выберите команды из и выберите Все команды . Прокрутите список вниз, пока не увидите Share Workbook (Legacy) . Выберите этот элемент и нажмите Добавить . Нажмите OK . Кнопка «Поделиться книгой» теперь находится в верхней части окна Excel.

Функция

Максимальный предел

Пользователи, которые могут открывать файл одновременно

256

Личные представления в рабочей книге

Ограничен доступной памятью

Дней, в течение которых ведется история изменений

32 767 (по умолчанию 30 дней)

Книги, которые можно объединить одновременно

Ограничен доступной памятью

Ячейки, которые можно выделить

32 767

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

32 (каждый пользователь обозначен отдельным цветом; изменения, сделанные текущим пользователем, выделены темно-синим цветом)

Таблицы Excel в книге

0 (ноль)

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

Спецификации и ограничения рабочих листов и рабочих книг

Функция

Максимальный предел

Открытые книги

Ограничен доступной памятью и системными ресурсами

Общее количество строк и столбцов на листе

1 048 576 строк по 16 384 столбца

Ширина столбца

255 символов

Высота строки

409 баллов

Разрывы страниц

1026 горизонтальный и вертикальный

Общее количество символов, которое может содержать ячейка

32 767 символов

символов в верхнем или нижнем колонтитуле

255

Максимальное количество переводов строки на ячейку

253

листов в книге

Ограничен доступной памятью (по умолчанию 3 листа)

Цвета в книге

16 миллионов цветов (32 бита с полным доступом к 24-битному цветовому спектру)

Именованные представления в книге

Ограничен доступной памятью

Уникальные форматы/стили ячеек

65 490

Стили заливки

256

Толщина линий и стили

256

Уникальные типы шрифтов

1024 глобальных шрифта, доступных для использования; 512 на книгу

Числовые форматы в книге

От 200 до 250, в зависимости от установленной языковой версии Excel

Имена в книге

Ограничен доступной памятью

Окна в книге

Ограничен доступной памятью

Гиперссылки на листе

65 530 гиперссылок

Стекла в окне

4

Связанные листы

Ограничен доступной памятью

Сценарии

Ограничен доступной памятью; в сводном отчете показаны только первые 251 сценарий

Изменение ячеек в сценарии

32

Настраиваемые ячейки в Solver

200

Пользовательские функции

Ограничен доступной памятью

Диапазон увеличения

от 10 до 400 процентов

Отчеты

Ограничен доступной памятью

Сортировать ссылки

64 в одном сорте; неограниченно при использовании последовательных сортировок

Уровни отмены

100

Поля в форме данных

32

Параметры рабочей книги

255 параметров на рабочую книгу

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

10 000

Несмежные ячейки, которые можно выбрать

2 147 483 648 ячеек

Ядра процессора

64

Расчетные спецификации и пределы

Функция

Максимальный предел

Точность числа

15 цифр

Наименьшее допустимое отрицательное число

-2.2251Э-308

Наименьшее допустимое положительное число

2.2251Е-308

Наибольшее допустимое положительное число

9.99999999999999E+307

Наибольшее допустимое отрицательное число

-9,99999999999999E+307

Наибольшее допустимое положительное число по формуле

1.7976

8623158e+308

Наибольшее допустимое отрицательное число по формуле

-1,7976

8623158e+308

Длина содержания формулы

8 192 символа

Внутренняя длина формулы

16 384 байта

Итерации

32 767

Массивы рабочих листов

Ограничен доступной памятью

Выбранные диапазоны

2 048

Аргументы в функции

255

Вложенные уровни функций

64

Определенные пользователем категории функций

255

Количество доступных функций рабочего листа

341

Размер стека операндов

1 024

Зависимость между рабочими листами

64 000 рабочих листов, которые могут ссылаться на другие листы

Зависимость формулы массива между рабочими листами

Ограничен доступной памятью

Зависимость от области

Ограничен доступной памятью

Зависимость области на рабочий лист

Ограничен доступной памятью

Зависимость от одной ячейки

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

Длина содержимого связанной ячейки из закрытых книг

32 767

Самая ранняя дата, разрешенная для расчета

1 января 1900 г. (1 января 1904 г., если используется система дат 1904 г.)

Последняя дата, разрешенная для расчета

31 декабря 9999

Максимальное количество времени, которое можно ввести

9999:59:59

Технические характеристики и ограничения

Функция

Максимальный предел

Диаграммы, связанные с рабочим листом

Ограничен доступной памятью

Рабочие листы, на которые ссылается диаграмма

255

Серия данных на одной диаграмме

255

Точки данных в серии данных для двумерных диаграмм

Ограничен доступной памятью

Точки данных в серии данных для трехмерных диаграмм

Ограничен доступной памятью

Точки данных для всех рядов данных на одной диаграмме

Ограничен доступной памятью

Спецификации и ограничения сводных таблиц и сводных диаграмм

Функция

Максимальный предел

Отчеты сводной таблицы на листе

Ограничен доступной памятью

Уникальных элементов на поле

1 048 576

Поля строки или столбца в отчете сводной таблицы

Ограничен доступной памятью

Фильтры отчетов в отчете сводной таблицы

256 (может быть ограничено доступной памятью)

Поля значений в отчете сводной таблицы

256

Формулы вычисляемых элементов в отчете сводной таблицы

Ограничен доступной памятью

Фильтры отчетов в отчете сводной диаграммы

256 (может быть ограничено доступной памятью)

Поля значений в отчете сводной диаграммы

256

Формулы вычисляемых элементов в отчете сводной диаграммы

Ограничен доступной памятью

Длина имени MDX для элемента сводной таблицы

32 767

Длина строки реляционной сводной таблицы

32 767

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

10 000

Книги с параметром «Разрешить внесение изменений более чем одним пользователем…» настройка включена

Если для книги включен параметр Разрешить изменения более чем одному пользователю… , применяется следующая информация. Этот параметр доступен, если щелкнуть вкладку Review > Share Workbook .

Функция

Максимальный предел

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

256

Личные представления в рабочей книге

Ограничен доступной памятью

Дней, в течение которых ведется история изменений

32 767 (по умолчанию 30 дней)

Книги, которые можно объединить одновременно

Ограничен доступной памятью

Ячейки, которые можно выделить в книге

32 767

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

32 (каждый пользователь обозначен отдельным цветом; изменения, сделанные текущим пользователем, выделены темно-синим цветом)

Таблицы Excel в книге

0 (ноль)

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

Спецификации и ограничения рабочих листов и рабочих книг

Функция

Максимальный предел

Открытые книги

Ограничен доступной памятью и системными ресурсами

Общее количество строк и столбцов на листе

1 048 576 строк по 16 384 столбца

Ширина столбца

255 символов

Высота строки

409 баллов

Разрывы страниц

1026 горизонтальный и вертикальный

Общее количество символов, которое может содержать ячейка

32 767 символов

символов в верхнем или нижнем колонтитуле

255

Максимальное количество переводов строки на ячейку

253

листов в книге

Ограничен доступной памятью (по умолчанию 3 листа)

Цвета в книге

16 миллионов цветов (32 бита с полным доступом к 24-битному цветовому спектру)

Именованные представления в книге

Ограничен доступной памятью

Уникальные форматы/стили ячеек

65 490

Стили заливки

256

Толщина линий и стили

256

Уникальные типы шрифтов

1024 глобальных шрифта, доступных для использования; 512 на книгу

Числовые форматы в книге

От 200 до 250, в зависимости от установленной языковой версии Excel

Имена в книге

Ограничен доступной памятью

Окна в книге

Ограничен доступной памятью

Гиперссылки на листе

65 530 гиперссылок

Стекла в окне

4

Связанные листы

Ограничен доступной памятью

Сценарии

Ограничен доступной памятью; в сводном отчете показаны только первые 251 сценарий

Изменение ячеек в сценарии

32

Настраиваемые ячейки в Solver

200

Пользовательские функции

Ограничен доступной памятью

Диапазон увеличения

от 10 до 400 процентов

Отчеты

Ограничен доступной памятью

Сортировать ссылки

64 в одном сорте; неограниченно при использовании последовательных сортировок

Уровни отмены

100

Поля в форме данных

32

Параметры рабочей книги

255 параметров на рабочую книгу

Фильтр раскрывающихся списков

10 000

Расчетные спецификации и пределы

Функция

Максимальный предел

Точность числа

15 цифр

Наименьшее допустимое отрицательное число

-2.2251Э-308

Наименьшее допустимое положительное число

2.2251Е-308

Наибольшее допустимое положительное число

9.99999999999999E+307

Наибольшее допустимое отрицательное число

-9,99999999999999E+307

Наибольшее допустимое положительное число по формуле

1.7976

8623158e+308

Наибольшее допустимое отрицательное число по формуле

-1,7976

8623158e+308

Длина содержания формулы

8 192 символа

Внутренняя длина формулы

16 384 байта

Итерации

32 767

Массивы рабочих листов

Ограничен доступной памятью

Выбранные диапазоны

2 048

Аргументы в функции

255

Вложенные уровни функций

64

Определенные пользователем категории функций

255

Количество доступных функций рабочего листа

341

Размер стека операндов

1 024

Зависимость между рабочими листами

64 000 рабочих листов, которые могут ссылаться на другие листы

Зависимость формулы массива между рабочими листами

Ограничен доступной памятью

Зависимость от области

Ограничен доступной памятью

Зависимость области на рабочий лист

Ограничен доступной памятью

Зависимость от одной ячейки

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

Длина содержимого связанной ячейки из закрытых книг

32 767

Самая ранняя дата, разрешенная для расчета

1 января 1900 г. (1 января 1904 г., если используется система дат 1904 г.)

Последняя дата, разрешенная для расчета

31 декабря 9999

Максимальное количество времени, которое можно ввести

9999:59:59

Технические характеристики и ограничения

Функция

Максимальный предел

Диаграммы, связанные с рабочим листом

Ограничен доступной памятью

Рабочие листы, на которые ссылается диаграмма

255

Серия данных на одной диаграмме

255

Точки данных в серии данных для двумерных диаграмм

32 000

Точки данных в серии данных для трехмерных диаграмм

4000

Точки данных для всех рядов данных на одной диаграмме

256 000

Спецификации и ограничения сводных таблиц и сводных диаграмм

Функция

Максимальный предел

Отчеты сводной таблицы на листе

Ограничен доступной памятью

Уникальных элементов на поле

1 048 576

Поля строки или столбца в отчете сводной таблицы

Ограничен доступной памятью

Фильтры отчетов в отчете сводной таблицы

256 (может быть ограничено доступной памятью)

Поля значений в отчете сводной таблицы

256

Формулы вычисляемых элементов в отчете сводной таблицы

Ограничен доступной памятью

Фильтры отчетов в отчете сводной диаграммы

256 (может быть ограничено доступной памятью)

Поля значений в отчете сводной диаграммы

256

Формулы вычисляемых элементов в отчете сводной диаграммы

Ограничен доступной памятью

Длина имени MDX для элемента сводной таблицы

32 767

Длина строки реляционной сводной таблицы

32 767

Книги с параметром «Разрешить внесение изменений более чем одним пользователем…» настройка включена

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

Функция

Максимальный предел

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

256

Личные представления в рабочей книге

Ограничен доступной памятью

Дней, в течение которых ведется история изменений

32 767 (по умолчанию 30 дней)

Книги, которые можно объединить одновременно

Ограничен доступной памятью

Ячейки, которые можно выделить

32 767

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

32 (каждый пользователь обозначен отдельным цветом; изменения, сделанные текущим пользователем, выделены темно-синим цветом)

Таблицы Excel в книге

0 (ноль)

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

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

В этом руководстве объясняется, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверки данных в другие ячейки, находить недопустимые записи, исправлять и удалять проверки данных.

При настройке рабочей книги для пользователей часто может потребоваться контролировать ввод информации в определенные ячейки, чтобы убедиться, что все введенные данные точны и непротиворечивы.Среди прочего, вы можете захотеть разрешить в ячейке только определенный тип данных, например числа или даты, или ограничить числа определенным диапазоном, а текст — заданной длиной. Возможно, вы даже захотите предоставить заранее определенный список допустимых записей, чтобы исключить возможные ошибки. Проверка данных Excel позволяет выполнять все эти действия во всех версиях Microsoft Excel 365, 2019, 2016, 20013, 2010 и более ранних версиях.

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

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

Вот лишь несколько примеров того, что может сделать проверка данных в Excel:

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

Например, вы можете настроить правило, ограничивающее ввод данных 4-значными числами от 1000 до 9999.Если пользователь введет что-то другое, Excel покажет предупреждение об ошибке, объясняющее, что он сделал неправильно:

.

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

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

1. Откройте диалоговое окно проверки данных

Выберите одну или несколько ячеек для проверки, перейдите на вкладку Данные > группу Инструменты данных и нажмите кнопку Проверка данных .

Вы также можете открыть диалоговое окно проверки данных, нажав Alt > D > L, при этом каждая клавиша нажимается отдельно.

2. Создайте правило проверки Excel

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

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

В качестве примера создадим правило, запрещающее пользователям вводить целое число от 1000 до 9999:

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

3. Добавьте входное сообщение (необязательно)

Если вы хотите отобразить сообщение, объясняющее пользователю, какие данные разрешены в данной ячейке, откройте вкладку Входное сообщение и выполните следующие действия:

  • Убедитесь, что установлен флажок Показывать входное сообщение при выборе ячейки .
  • Введите заголовок и текст сообщения в соответствующие поля.
  • Щелкните OK , чтобы закрыть диалоговое окно.

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

4. Отображение предупреждения об ошибке (необязательно)

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

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

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

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

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

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

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

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

  • Установите флажок Показывать предупреждение об ошибке после ввода неверных данных (обычно установлен по умолчанию).
  • В поле Стиль выберите нужный тип оповещения.
  • Введите заголовок и текст сообщения об ошибке в соответствующие поля.
  • Нажмите OK .

Теперь, если пользователь введет недопустимые данные, Excel отобразит специальное предупреждение с объяснением ошибки (как показано в начале этого руководства).

Примечание. Если вы не введете свое собственное сообщение, появится стоп-оповещение по умолчанию со следующим текстом: Это значение не соответствует ограничениям проверки данных, определенным для этой ячейки .

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

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

Как вы уже знаете, критерии проверки определяются на вкладке Settings диалогового окна Data Validation ( вкладка Data > Data Validation ).

Целые и десятичные числа

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

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

Например, вот как вы создаете правило проверки Excel, которое разрешает любое целое число больше 0:

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

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

Аналогичным образом, чтобы проверить время, выберите Время в поле Разрешить , а затем определите необходимые критерии.

Например, чтобы разрешить только даты между Дата начала в B1 и Дата окончания в B2, примените это правило проверки даты Excel:

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

Длина текста

Чтобы разрешить ввод данных определенной длины, выберите Длина текста в поле Разрешить и выберите критерии проверки в соответствии с вашей бизнес-логикой.

Например, чтобы ограничить ввод 10 символами, создайте это правило:

Примечание. Опция Длина текста ограничивает количество символов, но не тип данных, что означает, что указанное выше правило разрешает как текст, так и числа длиной до 10 символов или 10 цифр соответственно.

Список проверки данных Excel (раскрывающийся)

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

  1. Откройте диалоговое окно Data Validation ( вкладка Data > Data Validation ).
  2. На вкладке Параметры выберите Список в поле Разрешить .
  3. В поле Источник введите элементы списка проверки Excel, разделенные запятыми. Например, чтобы ограничить пользовательский ввод тремя вариантами, введите Да, Нет, Н/Д .
  4. Убедитесь, что раскрывающийся список в ячейке установлен, чтобы стрелка раскрывающегося списка отображалась рядом с ячейкой.
  5. Нажмите OK .

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

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

Другие способы создания списка проверки данных в Excel

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

.

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

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

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

Как изменить проверку данных в Excel

Чтобы изменить правило проверки Excel, выполните следующие действия:

  1. Выберите любую из проверенных ячеек.
  2. Откройте диалоговое окно Data Validation ( вкладка Data > Data Validation ).
  3. Внесите необходимые изменения.
  4. Установите флажок Применить эти изменения ко всем другим ячейкам с теми же параметрами , чтобы скопировать сделанные вами изменения во все другие ячейки с исходными критериями проверки.
  5. Нажмите OK , чтобы сохранить изменения.

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

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

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

Чтобы скопировать правило проверки в Excel, выполните следующие 4 быстрых шага:

  1. Выберите ячейку, к которой применяется правило проверки, и нажмите Ctrl + C, чтобы скопировать ее.
  2. Выберите другие ячейки, которые вы хотите проверить. Чтобы выбрать несмежные ячейки, нажмите и удерживайте клавишу Ctrl при выборе ячеек.
  3. Щелкните выделенное правой кнопкой мыши, выберите Специальная вставка и выберите параметр Проверка .

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

  4. Нажмите OK .

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

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

Чтобы быстро найти все проверенные ячейки на текущем листе, перейдите на вкладку Главная > Редактирование группы и нажмите Найти и выбрать > Проверка данных :

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

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

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

Метод 1: Обычный способ удаления проверки данных

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

  1. Выберите ячейку (ячейки) с проверкой данных.
  2. На вкладке Data нажмите кнопку Data Validation .
  3. На вкладке Настройки нажмите кнопку Очистить все , а затем нажмите OK .

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

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

Метод 2: Вставить Special для удаления правил проверки данных

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

  1. Выберите пустую ячейку без проверки данных и нажмите Ctrl + C, чтобы скопировать ее.
  2. Выберите ячейки, из которых вы хотите удалить проверку данных.
  3. Нажмите Ctrl + Alt + V, затем N, что является ярлыком для Специальная вставка > Проверка данных .
  4. Нажмите Enter. Сделанный!

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

Теперь, когда вы знакомы с основами проверки данных в Excel, позвольте мне поделиться несколькими советами, которые могут значительно повысить эффективность ваших правил.

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

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

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

Например, чтобы разрешить любое целое число, кроме числа в A1, выберите критерий не равно в поле Данные и введите =$A$1 в поле Значение :

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

Например, чтобы запретить пользователям вводить даты после сегодняшней даты, введите формулу =СЕГОДНЯ() в какую-нибудь ячейку, скажем, B1, а затем настройте правило проверки даты на основе этой ячейки:

Или вы можете ввести формулу =СЕГОДНЯ() непосредственно в поле Дата начала , что даст тот же эффект.

Правила проверки на основе формул

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

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

= МИН($A$1:$A$10)

=МАКС($A$1:$A$10)

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

Как найти неверные данные на листе

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

Чтобы найти недействительные данные, которые попали в ваши рабочие листы до того, как вы добавили проверку данных, перейдите на вкладку Данные и щелкните Проверка данных > Обведите неверные данные .

Это выделит все ячейки, которые не соответствуют критериям проверки:

Как только вы исправите неверную запись, круг автоматически исчезнет. Чтобы удалить все круги, перейдите на вкладку Данные и щелкните Проверка данных > Очистить круги проверки .

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

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

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

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

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

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

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

Проверка данных в Excel предназначена для запрета ввода неверных данных непосредственно в ячейку, но не может помешать пользователям копировать неверные данные.Хотя нет способа отключить ярлыки копирования/вставки (кроме использования VBA), вы можете, по крайней мере, предотвратить копирование данных путем перетаскивания ячеек. Для этого перейдите к Файл > Параметры > Расширенные > Параметры редактирования и снимите флажок Включить маркер заполнения и перетаскивание ячеек .

Проверка данных Excel недоступна в режиме редактирования ячейки

Команда проверки данных недоступна (выделена серым цветом), если вы вводите или изменяете данные в ячейке.Закончив редактирование ячейки, нажмите Enter или Esc, чтобы выйти из режима редактирования, а затем выполните проверку данных.

Проверка данных не может быть применена к защищенной или общей книге

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

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

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

  • Формула проверки не возвращает ошибок.
  • Формула не ссылается на пустые ячейки.
  • Используются соответствующие ссылки на ячейки.

Дополнительные сведения см. в разделе Пользовательское правило проверки данных не работает.

Включен ручной пересчет

Если в Excel включен режим ручного расчета, невычисленные формулы могут помешать правильной проверке данных. Чтобы снова изменить параметр расчета Excel на автоматический, перейдите на вкладку Формулы > группу Расчет , нажмите кнопку Параметры расчета , а затем щелкните Автоматический .

Дополнительные сведения см. в разделе Автоматический расчет и расчет вручную.

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

Вас также может заинтересовать

Промежуточные итоги

в Excel: как вставить, использовать и удалить

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

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

Что такое промежуточный итог в Excel?

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

В Microsoft Excel функция «Промежуточный итог» не ограничивается только суммированием подмножеств значений в наборе данных. Это позволяет вам группировать и суммировать ваши данные, используя СУММ, СЧЁТ, СРЕДНЕЕ, МИН., МАКС. и другие функции. Кроме того, он создает иерархию групп, известную как схема, которая позволяет отображать или скрывать сведения о каждом промежуточном итоге или просматривать только сводку промежуточных и общих итогов.

Например, промежуточные итоги Excel могут выглядеть так:

Как вставить промежуточные итоги в Excel

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

1. Организация исходных данных

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

Итак, прежде чем добавлять промежуточные итоги, обязательно отсортируйте столбец, по которому вы хотите сгруппировать данные. Самый простой способ сделать это — нажать кнопку Фильтр на вкладке Данные , затем щелкнуть стрелку фильтра и выбрать сортировку от А до Я или от Я до А:

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

2. Добавить промежуточные итоги

Выберите любую ячейку в наборе данных, перейдите на вкладку Данные > группу Структура и нажмите Промежуточный итог .

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

3. Определите параметры промежуточного итога

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

  • В поле При каждом изменении в поле выберите столбец, содержащий данные, по которым вы хотите сгруппировать.
  • В поле Use function выберите одну из следующих функций:
    • Сумма — сложить числа.
    • Подсчет — подсчет непустых ячеек (при этом будут вставлены формулы промежуточных итогов с помощью функции СЧЁТ).
    • Среднее — вычислить среднее число.
    • Max — вернуть наибольшее значение.
    • Min — вернуть наименьшее значение.
    • Произведение — вычислить произведение ячеек.
    • Подсчет чисел — подсчет ячеек, содержащих числа (при этом будут вставлены формулы промежуточных итогов с помощью функции СЧЁТ).
    • StdDev — расчет стандартного отклонения совокупности на основе выборки чисел.
    • StdDevp — возвращает стандартное отклонение на основе всей совокупности чисел.
    • Var — оценка дисперсии генеральной совокупности на основе выборки чисел.
    • Varp — оценка дисперсии совокупности на основе всей совокупности чисел.
  • В разделе Добавить промежуточный итог к установите флажок для каждого столбца, для которого вы хотите получить промежуточный итог.

В этом примере мы группируем данные по столбцу Регион и используем функцию СУММ для суммирования чисел в столбцах Продажи и Прибыль .

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

  • Чтобы вставить автоматический разрыв страницы после каждой промежуточной суммы, установите флажок Разрыв страницы между группами .
  • Чтобы отобразить строку сводки над строкой сведений, снимите флажок Сводка под данными .Чтобы отобразить строку сводки под строкой сведений, установите этот флажок (обычно установлен по умолчанию).
  • Чтобы перезаписать любые существующие промежуточные итоги, оставьте установленным флажок Заменить текущие промежуточные итоги , в противном случае снимите этот флажок.

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

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

Наконечник. Если промежуточные итоги и общий итог не пересчитываются, обязательно настройте рабочую книгу на автоматический расчет формул ( Файл > Параметры > Формулы > Параметры расчета > Расчет рабочей книги > Автоматически ).

3 вещи, которые вы должны знать о функции промежуточных итогов Excel

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

1. Промежуточные итоги учитываются только видимые строки

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

Применение функции «Промежуточный итог» в Excel автоматически создает формулы ПРОМЕЖУТОЧНЫХ ИТОГОВ, которые выполняют определенный тип расчета, такой как сумма, количество, среднее и т. д. Функция определяется числом в первом аргументе (номер_функции), который принадлежит к одному из следующих наборов:

  • 1–11 игнорировать отфильтрованные ячейки, но включать скрытые вручную строки.
  • 101 — 111 игнорировать все скрытые строки (отфильтрованы и скрыты вручную).

Функция промежуточного итога Excel вставляет формулы с номерами функций 1–11.

В приведенном выше примере вставка промежуточных итогов с помощью функции «Сумма» создает следующую формулу: ПРОМЕЖУТОЧНЫЙ ИТОГ(9, C2:C5) . Где 9 представляет собой функцию СУММ, а C2:C5 — это первая группа ячеек для промежуточного итога.

Если вы отфильтруете, скажем, Лимонов и Апельсинов , они будут автоматически удалены из промежуточных итогов. Однако если вы скроете эти строки вручную, они будут включены в промежуточные итоги.Изображение ниже иллюстрирует разницу:

Чтобы исключить вручную скрытые строки , чтобы рассчитывались только видимые ячейки, измените формулу промежуточного итога, заменив номер функции 1-11 на соответствующий номер 101-111.

В нашем примере, чтобы просуммировать только видимые ячейки, за исключением строк, скрытых вручную, измените ПРОМЕЖУТОЧНЫЕ ИТОГОВ( 9 ,C2:C5) на ПРОМЕЖУТОЧНЫЕИТОГИ( 109 ,C2:C5):

Дополнительные сведения об использовании формул промежуточных итогов в Excel см. в учебнике по функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

2. Общие итоги рассчитываются на основе исходных данных

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

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

3. Промежуточные итоги недоступны в таблицах Excel

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

Как добавить несколько промежуточных итогов в Excel (вложенные промежуточные итоги)

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

1. Сортировать данные по нескольким столбцам

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

Подробные инструкции см. в разделе Как сортировать по нескольким столбцам.

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

2.Вставьте первый уровень промежуточных итогов

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

3. Вставка вложенных уровней промежуточных итогов

После установки внешних промежуточных итогов снова щелкните Данные > Промежуточные итоги , чтобы добавить внутренний уровень промежуточных итогов:

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

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

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

В этом примере внутренний уровень промежуточных итогов будет группировать данные по столбцу Товар и суммировать значения в столбцах Продажи и Прибыль :

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

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

Добавить разные промежуточные итоги для одного и того же столбца

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

Например, в нашем образце таблицы в дополнение к итоговым значениям по регионам мы можем отобразить среднее значение для столбцов Продажи и Прибыль :

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

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

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

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

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

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

В нашем образце рабочего листа щелкните цифру 2, чтобы отобразить первую группировку по Региону :

Или нажмите цифру 3, чтобы отобразить вложенные промежуточные итоги по Item :

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

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

Копировать только строки промежуточных итогов

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

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

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

  1. Отобразите только те строки промежуточных итогов, которые вы хотите скопировать, используя нумерацию или символы плюса и минуса.
  2. Выберите любую ячейку промежуточного итога, а затем нажмите Ctrl+A, чтобы выбрать все ячейки.
  3. Выбрав промежуточные итоги, перейдите на вкладку Главная > Редактирование группы и нажмите Найти и выбрать > Перейти к специальному…
  4. В диалоговом окне Перейти к специальному выберите Только видимые ячейки и нажмите кнопку ОК.

    Наконечник. Вместо использования функции Перейти к специальному вы можете нажать Alt + ; чтобы выбрать только видимые ячейки.

  5. На текущем рабочем листе нажмите Ctrl+C, чтобы скопировать выбранные ячейки промежуточных итогов.
  6. Откройте другой лист или книгу и нажмите Ctrl+V, чтобы вставить промежуточные итоги.

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

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

Как изменить промежуточные итоги

Чтобы быстро изменить существующие промежуточные итоги, выполните следующие действия:

  1. Выберите любую ячейку промежуточного итога.
  2. Перейдите на вкладку Данные и щелкните Итого .
  3. В диалоговом окне Промежуточный итог внесите необходимые изменения, относящиеся к ключевому столбцу, суммирующей функции и значениям для промежуточного итога.
  4. Убедитесь, что установлен флажок Заменить текущие промежуточные итоги .
  5. Нажмите OK.

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

Как удалить промежуточные итоги в Excel

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

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

Это разгруппирует ваши данные и удалит все существующие промежуточные итоги.

Помимо функции промежуточных итогов Excel, которая автоматически вставляет промежуточные итоги, существует «ручной» способ добавления промежуточных итогов в Excel — с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Это обеспечивает еще большую универсальность, и я покажу вам пару полезных приемов в нашем следующем уроке.

Вас также может заинтересовать

.

Добавить комментарий

Ваш адрес email не будет опубликован.