В ячейку Excel не введены данные

Обновлено: 21.11.2024

Этот вопрос возникает довольно часто: вы хотите ввести данные в ячейку, но Excel почему-то не позволяет!

Есть несколько причин, по которым это может произойти, и мы рассмотрим их все в этой статье.

Возможно, рабочий лист был защищен

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

Вы можете отключить защиту, нажав: Обзор > Защитить > Снять защиту с листа

Обычно для отключения защиты требуется пароль.

Защита подробно описана в наших книгах для экспертов и электронных книгах.

Ячейки могли быть объединены

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

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

Главная > Выравнивание > Объединить и центрировать

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

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

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

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

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

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

Вы можете изменить и удалить правила проверки, щелкнув Данные > Инструменты данных > Проверка данных.

Правила проверки полностью объясняются в наших книгах по навыкам для экспертов и электронных книгах.

Текст может быть невидим

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

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

Главная > Стили > Обычный

Стили и форматирование подробно описаны в наших книгах по основным навыкам и электронных книгах.

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

Поскольку C7 не имеет значения на экране выше, формула не показывает результата. На приведенном ниже экране C7 содержит число, и отображается сумма:

Цель этого примера — проверить ввод перед вычислением результата. Важно понимать, что можно заменить любую допустимую формулу. Функция СУММ используется только в качестве примера. Логика также может быть скорректирована разными способами в зависимости от ситуации.

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

Если диапазон содержит три числа (т. е. все 3 ячейки не пусты), результатом будет ИСТИНА, и ЕСЛИ запустит функцию СУММ. Если нет, результатом является ЛОЖЬ, и ЕСЛИ возвращает пустую строку (""). Поскольку C7 не имеет значения на экране выше, формула не показывает результата.

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

СО СЧИТАТЬПУСТОТЫ

Функция СЧИТАТЬПУСТОТЫ подсчитывает пустые ячейки в диапазоне, поэтому мы можем написать немного более компактную формулу, подобную этой:

Если СЧИТАТЬПУСТОТЫ возвращает любое число, кроме нуля, функция ЕСЛИ оценивается как ИСТИНА и ничего не возвращает (""). Если СЧИТАТЬПУСТОТЫ возвращает ноль, ЕСЛИ оценивается как ЛОЖЬ и возвращает сумму.

Пустой

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

В этом примере используется буквальный подход с функцией IПУСТО. Поскольку мы хотим проверить все три ячейки одновременно, нам нужно использовать IПУСТО три раза внутри функции ИЛИ. Это логический тест внутри ЕСЛИ:

Когда оператор ИЛИ возвращает значение ИСТИНА (хотя бы одна ячейка пуста), ЕСЛИ возвращает пустую строку (""). Когда ИЛИ возвращает ЛОЖЬ (пустых ячеек нет), ЕСЛИ запускает функцию СУММ и возвращает результат:

С логическими операторами

Функция ЕПУСТО может быть заменена стандартными логическими операторами, например:

Кроме того, мы можем комбинировать оператор не равно (<>) с функцией AND следующим образом:

Обратите внимание, что функция СУММ была перемещена к результату ИСТИНА. Он будет работать, только если C5, C6 и C5 не пусты.

С СЧЕТЧИКОМ

Наконец, вы можете использовать функцию СЧЕТЧИК для проверки числового или текстового ввода:

Если диапазон C5:C5 содержит три значения (числа или текст), результатом будет TRUE и будет запущена функция SUM. На самом деле это не имеет смысла для показанного примера (который требует числового ввода), но его можно использовать в других ситуациях.

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

Excel Да, нет раскрывающегося списка

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

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

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

Когда закончите, нажмите "ОК".

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

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

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

Отображение сообщения об ошибке

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

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

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

Подсчет и анализ данных

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

Для этого используйте формулу Excel СЧЁТЕСЛИ, чтобы найти ячейки в заданном диапазоне, которые соответствуют определенным критериям, например содержат определенную подстроку. Например, =СЧЁТЕСЛИ(B2:B12, "ДА") вернёт количество ячеек в диапазоне от B2 до B12, содержащих подстроку "ДА." Используя подстроки "да" и "нет", вы можете быстро подсчитать количество ответов "да" и "нет".

Сортировка столбца

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

Для этого нажмите вкладку "Данные" в меню ленты. Затем в группе «Сортировка и фильтр» нажмите кнопку «Сортировка». Выберите нужный столбец в раскрывающемся списке «Сортировать по». В разделе "Порядок" выберите "От А до Я" или "От Я до А", чтобы выбрать нужный порядок.

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

Разделы:

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

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

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

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

Типы проверки данных и способы их использования

Самое сложное в проверке данных — понять, что вводить в поле "Источник", особенно для пользовательского типа.

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

Любое значение — разрешить ввод любого значения в ячейку

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

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

Целое число – разрешить ввод в ячейку только целых чисел

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

Вы можете ограничить ввод в ячейку числом от 1 до 5 или от 500 до 1000 или любым другим значением, если это целое число.

Поместите наименьшее число, которое вы хотите разрешить, в поле "Минимум" и наибольшее число, которое вы хотите разрешить, в поле "Максимум" следующим образом:

Это говорит о том, что наименьшее число, которое вы можете поместить в ячейку, равно 1, а самое большое — 5.

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

Десятичный: разрешить ввод в ячейку только чисел, включая числа с десятичными знаками

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

Например, теперь мы можем разрешить пользователю вводить в ячейку такие числа, как 1,5, 3,33 или 100,23434, но по-прежнему ограничивать наименьшее и наибольшее числа, которые разрешено вводить в ячейку.

Теперь давайте введем 1,5 в поле "Минимум" и 5,5 в поле "Максимум".

После того как мы нажмем OK, мы сможем ввести в ячейку любое число от 1,5 до 5,5.

Это означает, что мы можем сделать что-то вроде этого:

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

Список — ограничение ввода в ячейку предопределенным списком значений (включая список раскрывающегося меню)

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

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

Вот экран, используемый для создания списка:

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

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

Вы можете ввести диапазон ячеек, содержащих список значений:

Или вы можете просто ввести список значений непосредственно в поле Источник:

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

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

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

Дата – ограничить ввод в ячейку датой

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

Заполните поля Дата начала и Дата окончания датой в формате, понятном Excel, или ссылкой на ячейку с датой и все:

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

Время — ограничить ввод ячейки временем

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

Введите время в поле Время начала и Время окончания:

Обратите внимание, что мой Excel использует AM и PM, но 24-часовой формат также будет работать без проблем.

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

Длина текста. Ограничьте ввод в ячейку текстом и контролируйте его длину

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

Давайте поставим 5 для минимума и 10 для максимума:

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

Обратите внимание, что пробелы учитываются при подсчете символов.

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

Пользовательский — ограничить ввод в ячейку любыми данными в Excel

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

Здесь у вас есть только одно поле для работы, поле Формула.

В это поле необходимо ввести формулу или функцию, которая возвращает значение ИСТИНА или ЛОЖЬ. То есть формула должна быть логической формулой, которая всегда будет возвращать только ИСТИНА или ЛОЖЬ.

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

Это говорит о том, что Excel разрешает ввод в ячейку, ТОЛЬКО если ячейка D2 содержит число больше 1, а ячейка D3 содержит число больше 5.

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

Ссылки на ячейки при проверке данных

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

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

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

Чтобы удалить проверку данных, просто выберите нужную ячейку или ячейки, перейдите в окно проверки данных (вкладка "Данные" > "Проверка данных") и нажмите кнопку "Очистить все" в нижнем левом углу окна:

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

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

Перейдите в окно "Проверка данных", а затем на вкладку "Входящее сообщение":

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

Вот пример для нашего примера проверки пользовательских данных выше:

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

Пользовательские сообщения об ошибках

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

Вот сообщение об ошибке по умолчанию:

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

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

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

Затем вы можете ввести заголовок и сообщение:

Теперь давайте посмотрим на наше сообщение, когда мы пытаемся ввести неверное значение:

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

Опция «Информация» также позволяет нам сохранить неверное значение после того, как оно всплыло:

Единственный способ, который фактически остановит ввод неверных значений, — это стиль Stop:

Примечания

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

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

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

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

Объедините это с защитой ячеек и рабочих листов, и вы сможете создавать надежные файлы Excel.

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

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков
50+ часов видео
200+ руководств по Excel

Читайте также: