На чтение 6 мин Просмотров 21.7к. Опубликовано 11.05.2018
Иногда при работе с таблицами в редакторе Excel появляется уведомление, что «набор значений, которые могут быть введены в ячейку, ограничен». Самая главная проблема в том, что из-за этого вы не можете ввести нужную вам информацию. На самом деле ничего страшного тут нет. Это ситуацию можно исправить в несколько шагов. В этой статье мы подробно рассмотрим, из-за чего именно появляется это окно и что можно сделать в этой ситуации.
Содержание
- Условие проверки
- Любое значение
- Целое число
- Действительное
- Список
- Дата
- Время
- Длина текста
- Другой
- Сообщение об ошибке
- Отличие версий Microsoft Excel
- Сообщение для ввода данных
- Как отключить эту ошибку
- Примеры от компании Microsoft
- Заключение
- Видеоинструкция
Условие проверки
В редакторе Excel существует возможность ограничить ввод информации по каким-нибудь критериям. Процесс происходит следующим образом.
- Нажмите на любую клетку.
- Перейдите на вкладку «Данные».
- Кликните на выделенный инструмент.
- Выберите пункт «Проверка данных».
- После этого появится окно, в котором можно будет выбрать формат информации для проверки.
Рассмотрим эти форматы более внимательно.
Любое значение
В этом случае в ячейку можно будет вводить что угодно. Параметр используется по умолчанию для любой клетки.
Целое число
Выбрав этот формат, вы сможете отредактировать дополнительную настройку во втором поле.
Например, если выбрать вариант «между», то вы увидите, что в этом окне появятся два дополнительных поля: «Минимум» и «Максимум».
Если выбрать «больше» какой-то величины, то вам нужно будет ввести минимальный допустимый порог. Именно поэтому вы увидите только одно поле – «Минимум», поскольку «Максимум» неограничен.
Действительное
В этом случае принцип работы точно такой же, как и с целыми числами. Разница в том, что на этот раз вы можете использовать любые величины. В том числе и дробные. Для тех, кто не знает, что такое действительные числа, более подробно о них можно прочитать в «Википедии».
Список
Этот формат наиболее интересный.
Для того чтобы понять его возможности, нужно выполнить несколько простых действий.
- Заполните чем-нибудь несколько клеток. Неважно чем.
- Нажмите на какую-нибудь клетку. Перейдите на знакомую вам вкладку. Кликните на иконку «Работа с данными». Выберите выделенный инструмент.
- В поле «Тип данных» выберите вариант «Список». Кликните в графу «Источник». Затем выделите нужный диапазон клеток. Так намного удобнее, чем редактировать ссылку вручную. Для продолжения нажмите на «OK».
- Благодаря этому теперь в этой ячейке можно выбирать нужное слово из выпадающего списка. Это намного удобнее, чем изменять текст вручную.
Дата
Данный формат подходит для тех случаев, когда ячейка должна содержать только дату. При этом доступны точно такие же дополнительные условия, как с целыми и действительными числами.
Время
Аналогично и тут. Только здесь указывается одно время (без даты).
Длина текста
Иногда бывают ситуации, когда необходимо ограничить не формат клетки, а длину содержимого выражения. То есть, благодаря этой настройке, вы можете сделать так, чтобы в определенных полях можно было указать текст не больше нужного количества символов. Например, в графе номер телефона или название города.
Вариантов довольно много. Этот способ используется при заполнении каких-нибудь бланков или анкет.
Другой
Данный формат отличается от всех остальных. Здесь вы можете указать какую-нибудь формулу для проверки соответствия информации какому-либо условию.
Сообщение об ошибке
При желании вы можете добавить какое-нибудь уведомление о допущенной ошибке при вводе данных в ячейку. Для это вам нужно выполнить несколько простых операций.
- Повторите описанные выше шаги по вызову окна «Проверка вводимых значений».
- Выберите какой-нибудь тип данных. В качестве примера мы укажем, что необходимо вводить «Действительное» число, которое больше 10.
- Теперь переходим на вкладку «Сообщение об ошибке».
- Выбираем вид отображения:
- «Останов»;
- «Предупреждение»;
- «Сообщение».
- Указываем заголовок и текст уведомления.
- Для сохранения проверки ввода значений нажмите на «OK»
В качестве примера мы укажем следующие настройки.
- Введите любое число. Например, что-нибудь меньше указанного значения. Нажмите на клавишу [knopka]Enter[/knopka]. И вы увидите уведомление о том, что была допущена ошибка ввода данных.
В данном случае в поле «Вид» был указан формат «Сообщение». В этом случае вы можете вводить любые числа. Но каждый раз будете видеть подобное уведомление.
Если сделать тип «Останов», то ввести неверное значение не удастся.
А теперь попробуйте убрать введенные настройки и оставить пустые поля.
В результате этого вы и увидите указанную ранее ошибку. То есть, если ничего не указывать вручную, редактор выведет предупреждение по умолчанию.
Но обратите внимание на то, что текст сообщения отличается!
Отличие версий Microsoft Excel
Описанная выше инструкция подходит для современных редакторов 2010, 2013 и 2016 годов. По сравнению со старыми программами существуют некоторые отличия.
Откройте этот же файл в Экселе 2007. Введите неправильное значение. И вы увидите эту ошибку. То есть в современных редакторах текст сообщения по умолчанию был изменён. Привычный всем вид существовал до 2007 года.
В старом Excel 2003 ошибка точно такая же.
Сообщение для ввода данных
Чтобы у пользователей после ввода информации подобных ошибок не возникало, необходимо указывать такие подсказки заранее. Делается это очень просто.
- Кликните на любую клетку, для которой вы хотите задать какие-нибудь правила.
- Откройте в меню раздел «Данные».
- Выберите инструмент «Работа с данными».
- Нажмите на иконку «Проверка данных».
- В появившемся окне перейдите на вкладку «Сообщение для ввода».
- Укажите любой текст в указанных полях.
- Для продолжения нажмите на «OK».
- Теперь каждый раз, когда бы будете активировать эту ячейку, будете видеть подобную подсказку. Это значительно облегчит ситуацию другим пользователям, которые будут работать с созданными вами файлами.
Как отключить эту ошибку
Если вы открыли чужую таблицу и вам нужно сделать какие-нибудь изменения, но при этом видите подобную ошибку при вводе данных, то не нужно отчаиваться. Исправить ситуацию довольно просто.
- Выберите ячейку, в которой вы не можете указать нужное вам значение.
- Перейдите на панели инструментов на вкладку «Данные».
- Нажмите на инструмент «Работа с данными».
- Кликните на иконку «Проверка данных».
- Для того чтобы убрать все настройки, достаточно нажать на кнопку «Очистить всё».
- Сохраняем изменения кликом на «OK».
- Теперь можно вносить любые данные, словно вы открыли пустой файл и никаких настроек там нет.
Примеры от компании Microsoft
Если вы не совсем понимаете, каким образом нужно указывать условия для ввода значений, рекомендуется открыть официальный сайт компании Майкрософт. Там вы сможете найти файл примеров. В нём указаны абсолютно все типы данных и различные случаи составления документов и отчётов.
Заключение
В данной статье мы рассмотрели, в каких случаях может появляться ошибка ввода значений в таблицах Excel. Кроме этого, особое внимание было уделено настройкам различных условий для определения допустимого набора величин.
Если у вас что-то не получается, значит, вы что-то упускаете из виду или выбираете не тот формат данных. Попробуйте повторить эти действия более внимательно.
Видеоинструкция
Если вдруг вы не смогли понять данную инструкцию, ниже прилагается видеоролик, в котором все шаги описываются еще более детально с дополнительными пояснениями.
Содержимое ячейки можно редактировать непосредственно в ячейке. Вы также можете изменить содержимое ячейки, введя строка формул.
При редактировании содержимого ячейки Excel режиме правки. Некоторые Excel работают иначе или недоступны в режиме правки.
Если Excel режиме редактирования, в левом нижнем углу окна программы Excel отображается слово Изменить, как показано на рисунке ниже.
Как Excel в режиме правки?
В режиме правки многие команды недоступны. Например, если Excel находится в режиме правки, применить условное форматирование или изменить выравнивание содержимого ячейки невозможно.
Кроме того, клавиши со стрелками немного по-Excel в режиме правки. Вместо того чтобы перемещать курсор из ячейки в ячейку, в режиме правки клавиши со стрелками перемещают курсор в ячейке.
Включить или отключить режим правки
Если вы пытаетесь использовать режим правки, но ничего не происходит, возможно, он отключен. Вы можете включить или отключить режим правки, изменив параметр Excel редактирования.
-
Щелкните Файл > Параметры > Дополнительно.
-ИЛИ-
Только Excel 2007: нажмите кнопку Microsoft Office
, Excel параметры ,а затем выберите категорию Дополнительные параметры. -
В разделе Параметры правки выполните требуемое действие.
-
Чтобы включить режим правки, выберите элемент Разрешить редактирование непосредственно в ячейках.
-
Чтобы отключить режим правки, сключим поле Разрешить редактирование непосредственно в ячейках.
-
, Excel параметры ,а затем выберите категорию Дополнительные параметры.Перейдите в режим правки
Чтобы начать работу в режиме правки, сделайте следующее:
-
Дважды щелкните ячейку с данными, которые вы хотите изменить.
При этом запускается режим правки, и курсор находится в ячейке, которую вы дважды щелкнули. Содержимое ячейки также отображается в панели формул.
-
Щелкните ячейку с данными, которые вы хотите изменить, а затем щелкните в любом месте в области формул.
При этом в режиме правки курсор будет расположен в области формул в том месте, где вы щелкнули.
-
Щелкните ячейку с данными, которые вы хотите изменить, и нажмите F2.
В режиме правки курсор будет курсором в конце содержимого ячейки.
Вставка, удаление и замена содержимого ячеок
-
Чтобы вставить символы, щелкните ячейку, в которой их нужно вставить, и введите новые символы.
-
Чтобы удалить символы, щелкните ячейку, в которой их нужно удалить, а затем нажмите кнопку BACKSPACE или выберите символы, а затем нажмите кнопку DELETE.
-
Чтобы заменить определенные символы, вы выберите их и введите новые символы.
-
Чтобы включить режим замены, чтобы существующие символы заменялись новыми при вводе, нажмите кнопку ВСТАВИТЬ.
Примечание: Режим перепечатки можно использовать или отключать только в режиме правки. Если включен режим вставки, знак справа от точки вставки выделяется в панели формул и перезаписывается при вводе.
-
Чтобы начать новую строку текста в определенной точке ячейки, щелкните в том месте, где нужно ввести разрыв строки, а затем нажмите ALT+ВВОД.
Отмена и отмена изменений
Перед нажатием клавиши ВВОД или TAB, а также до или после нажатием клавиши F2 можно нажать клавишу ESC, чтобы отменить любые изменения содержимого ячейки.
После нажатия вводов или tab можно отменить изменения, нажав CTRL+Z или нажав кнопку Отменить 
на панели быстрого доступа.
Настройка способа отображения содержимого ячейки
После редактирования содержимого ячейки может потребоваться настроить их отображение.
-
Иногда в ячейке может отображаться строка вида #####. Это может происходить, если ячейка содержит число или дату, а ширина столбца не позволяет отобразить все необходимые символы. Предположим, например, что ячейка с форматом даты «дд.мм.дд.yyy» содержит дату 31.12.2007 г. Однако ширины столбца хватает только для показа шести символов. В ячейке будет отображаться #####. Чтобы увидеть все содержимое ячейки с текущим форматом, необходимо увеличить ширину столбца.
Изменение ширины столбца
-
Щелкните ячейку, для которой требуется изменить ширину столбца.
-
На вкладке Главная в группе Ячейки нажмите кнопку Формат.
-
Выделите пункт Размер ячейки и выполните одно из следующих действий.
-
Чтобы подогнать размер ячейки под текст, выберите команду Автоподбор ширины столбца.
-
Чтобы указать большую ширину столбца, щелкните Ширина столбца и введите нужное число в поле Ширина столбца.
Примечание: Также ширину столбца можно увеличить, изменив формат столбца или отдельной ячейки. Например, можно изменить формат даты, чтобы отображался только день и месяц (формат «дд.мм»), например 31.12, или представить число в экспоненциальном формате, например 4E+08.
-
-
-
Если в ячейке несколько строк текста, часть текста может отображаться не так, как нужно. Путем переноса текста в ячейке можно отобразить несколько строк текста.
Примечание: Если Excel режиме редактирования, изменить способ переноса текста невозможно.
Перенос текста в ячейке
-
Щелкните ячейку, в которой требуется выполнить перенос текста.
-
На вкладке Главная в группе Выравнивание выберите пункт Переносить текст.
Примечание: Если текст является одним длинным словом, символы не будут переносимы; вы можете расширить столбец или уменьшить размер шрифта, чтобы увидеть весь текст. Если после переноса текста в ячейке отображается не весь текст, может потребоваться изменить высоту строки.
На вкладке Главная в группе Ячейки нажмите кнопку Формат и выберите в разделе Размер ячейки команду Автоподбор высоты строки.
-
Выход из режима правки
Чтобы остановить работу в режиме правки, сделайте следующее:
-
Нажмите клавишу ВВОД.
Excel выйти из режима правки и выбрать ячейку непосредственно под текущей ячейкой.
Вы также можете указать, что при нажатии нажатия ввод вы можете выбрать другую смежную ячейку.
-
Щелкните Файл > Параметры > Дополнительно.
-ИЛИ-
Только Excel 2007: нажмите кнопку Microsoft Office
, Excel параметры ,а затем выберите категорию Дополнительные параметры. -
В разделе Параметры правки выполните требуемое действие.
-
Чтобы выделение оставалось в ячейке, которую вы редактируете, сделайте так, чтобы после нажатия нажатия ввод переместили выделение.
-
Чтобы управлять направлением выделения, выберите после нажатия клавиши ВВОД, переместите выделение, щелкните стрелку рядом с кнопкой Направление ивыберите направление из списка.
-
-
-
Нажмите клавишу TAB.
При этом режим правки перестанет быть изменен и выберет ячейку справа от текущей ячейки. При нажатии shift+TAB ячейка будет выбрана слева.
-
Щелкните другую ячейку.
Excel выйти из режима правки и выбрать выбранную ячейку.
-
Нажмите F2.
Excel выйти из режима правки и выйти из курсора.
Создание раскрывающегося списка
Смотрите также Is Nothing And список.Сформируем именованный диапазон. Путь: Можно выбрать конкретное. ячейку, ограничен. Как
данных, то можно принимать значение ИСТИНА,
-
нельзя впрямую указать данных и ввести или ссылку на Числовым эквивалентом дляПрименение проверки данных кНа вкладкевыберите пункт запретить пользователям егоИгнорировать пустые ячейкиЧтобы упростить работу пользователей Target.Cells.Count = 1Открываем параметры проверки данных. «Формулы» — «ДиспетчерОчень удобный инструмент Excel
исправить? использовать инструмент Выделение
-
для других строк ссылку на диапазоны формулу. Вторую формулу зависящую от нее 23:59:59 будет 0,99999. ячейкамПараметрыСписок просмотр и изменение,.
-
с листом, добавьте Then Application.EnableEvents = В поле «Источник» имен» — «Создать». для проверки введенных
-
-
Евгений токарев группы ячеек (Главная/ — ЛОЖЬ вне
-
другого листа, например, можно использовать для ячейку. Например,Длина текстаФункции Excel (по категориям)
в поле. скройте и защититеУстановите флажок в ячейки раскрывающиеся False If Len(Target.Offset(1, вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”). Вводим уникальное название данных. Повысить комфорт: Встаньте в ячейке, Найти и выделить/
-
зависимости от содержания так =Лист2!$A$1. Позволяют всего столбцаЧтобы. В ячейку разрешенПри вводе большого количестваРазрешить
-
Щелкните поле этот лист. ПодробнееСписок допустимых значений списки, в которых 0)) = 0Имя файла, из которого диапазона – ОК. работы с данными затем Меню - Выделение группы ячеек). ячейки. обойти это ограничениеАВведите формулу
-
ввод только определенного информации в ячейкивыберите пунктИсточник
-
о защите листовОткройте вкладку
-
можно выбрать нужный Then Target.Offset(1, 0) берется информация для
-
Создаем раскрывающийся список в позволяют возможности выпадающих Данные — Проверка.Опция Проверка данных этогоТеперь выделим ячейку использование Имен., для этого необходимоПояснение количества символов. При таблицы легко допуститьСписоки выделите на см. в статье
-
-
Подсказка по вводу элемент. = Target Else
-
списка, заключено в любой ячейке. Как списков: подстановка данных, Тип данных - инструмента позволяет выделитьА2Если в Проверке данных нужно выделить не диапазон,Ячейка этом ограничении можно ошибку. В EXCEL. листе список допустимых Блокировка ячеек.
-
-
.Windows macOS Online Target.End(xlDown).Offset(1, 0) = квадратные скобки. Этот
-
это сделать, уже отображение данных другого любое значение. ячейки, для которыхи нажмем сделать, например, ссылку а весь столбецB2 вводить и числа существует инструмент для
Если вы уже создали элементов.
Если вы решили изменить -
Если вы хотите, чтобы Target End If файл должен быть известно. Источник – листа или файла,
Вообще, удобная штука проводится проверка допустимостиCTRL+D на ячейкуАсодержала только текст и даты, главное, проверки введенных данных таблицу с элементамиДиалоговое окно свернется, чтобы
-
Если вы уже создали элементов.
Если вы решили изменитьРабота с раскрывающимся списком
элементы раскрывающегося списка, при выборе ячейкиНа новом листе введите Target.ClearContents Application.EnableEvents = открыт. Если книга имя диапазона: =деревья. наличие функции поиска
на самом деле. данных (заданная с. Значение изА1Значение в ячейке, содержащей=ЕТЕКСТ(B2) чтобы количество введенных сразу после нажатия раскрывающегося списка, щелкните было видно весь см. статью Добавление
появлялось всплывающее сообщение, данные, которые должны True End If с нужными значениямиСнимаем галочки на вкладках
и зависимости. Можно делать списки помощью команды Данные/
Скачивание примеров
А1другого листа, то имя кода продуктаВ Типе данных нет символов не противоречило клавиши ENTER – поле лист. и удаление элементов установите флажок
отображаться в раскрывающемся End Sub находится в другой «Сообщение для ввода»,Путь: меню «Данные» -
выбора, можно проверять Работа с данными/скопируется в нужно сначала определить ( возможности выбрать тип ограничению по длине Проверка данных.ИсточникНажмите клавишу ВВОД или раскрывающегося списка.Отображать подсказку, если ячейка списке. Желательно, чтобыЧтобы выбираемые значения отображались папке, нужно указывать
-
«Сообщение об ошибке». инструмент «Проверка данных» значения ввода пользователей Проверка данных). ПриА2 Имя для этойB5
-
Текст, поэтому приходится текста. Например, приИнструмент Проверка данных (Данные/
-
и выделите ячейки, кнопкуЧтобы удалить раскрывающийся список, является текущей элементы списка содержались в одной ячейке, путь полностью. Если этого не — вкладка «Параметры».
с выводом всяких выборе переключателя Всехвместе с условием ячейки, а затем), всегда начиналось со этого добиваться косвенно. ограничении количества символов Работа с данными/ содержащие эти элементы.Развернуть см. статью Удалениеи введите заголовок
-
в таблице Excel. разделенные любым знакомВозьмем три именованных диапазона: сделать, Excel не Тип данных – сообщений и т. будут выделены все Проверки данных. Несмотря
-
сослаться на это стандартного префикса «ID-» Вы можете Проверку менее 5, нельзя Проверка данных) не
Однако не включайте, чтобы развернуть раскрывающегося списка.
-
и сообщение в Если это не препинания, применим такой
Это обязательное условие. Выше позволит нам вводить «Список». п. такие ячейки. При на то, что имя в правиле
-
и имело длину данных применить прямо ввести дату позднее был бы столь
-
в него ячейку диалоговое окно, аПредлагаем скачать образец книги соответствующие поля (до так, список можно модуль. описано, как сделать
-
новые значения.Ввести значения, из которыхIdoktop»petrograd» выборе опции Этих теперь условие Проверки Проверке данных. не менее 10 к ячейке 13/10/2173, т.к. ей популярным, если бы заголовка. Добавьте только затем нажмите кнопку с несколькими примерами 225 символов). Если
-
Это обязательное условие. Выше позволит нам вводить «Список». п. такие ячейки. ПриСм. также
быстро преобразовать вPrivate Sub Worksheet_Change(ByVal
обычный список именованнымВызываем редактор Visual Basic.
-
будет складываться выпадающий: Формат ячеек посмотри. же выделяются только данных будет приниматьВ Excel 2010, напротив, можно знаков.
B2 соответствует число 99999,
-
его функциональность ограничивалась ячейки, которые должныОК проверки данных, аналогичными вы не хотите, таблицу, выделив любую Target As Range) диапазоном (с помощью Для этого щелкаем список, можно разными
-
Миша куць те ячейки, для значение ЛОЖЬ, никакого использовать правила проверки=И(ЛЕВСИМВ(B5;3)=»ID-«; ДЛСТР(B5)>9)
-
-
Допустить ввод значения в а 14/10/2173 - бы только собственно
-
отображаться в раскрывающемся. примеру в этой чтобы сообщение отображалось, ячейку диапазона и
-
On Error Resume «Диспетчера имен»). Помним, правой кнопкой мыши способами:: Поставте тип поля которых установлены те предупреждающего сообщения выведено
-
данных, ссылающиеся наПроверку данных вводим для ячейку это уже 100000, проверкой. Ниже кратко списке. Список элементовСоветы: статье. Вы можете снимите этот флажок. нажав клавиши Next что имя не по названию листаВручную через «точку-с-запятой» в текстовый! же правила проверки не будет. Чтобы
значения на других
-
ячейкиB1 т.е. 6 символов. перечислены основные приемы
-
также можно ввести
-
воспользоваться ими илиОткройте вкладкуCTRL+T
-
If Not Intersect(Target, может содержать пробелов и переходим по поле «Источник».Claire данных, что и убедиться, что данные листах. В ExcelB5только в случае, Интересно, что при работы с этим
-
-
непосредственно в полеЗначения также можно ввести создать собственные сценарии
-
Сообщение об ошибке. Range(«C2:C5»)) Is Nothing и знаков препинания. вкладке «Исходный текст».Ввести значения заранее. А: ну уж не для активной ячейки. в ячейках соответствуют 2007 и ExcelПри выделении нескольких ячеек, если после ввода ограничении, например, менее инструментом.
-
-
Источник непосредственно в поле проверки данных. Скачать
.Примечания: And Target.Cells.Count =Создадим первый выпадающий список, Либо одновременно нажимаем в качестве источника текстовый. числовой!Получили форму от вышестоящей условиям определенным в 97-2003 проверка данных там где нужно, значение в ячейке 5 символов, выКак видно на рисункечерез запятую. Например:
См. также
Источник примеры проверки данных
Если вы хотите, чтобы
support.office.com
Проверка данных в MS EXCEL
1 Then куда войдут названия клавиши Alt + указать диапазон ячеекAnalitik организации для заполнения Проверке данных, нужно этого типа не
не забывайте указыватьD1 не сможете ввести снизу, условия проверкиФрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусычерез запятую. Excel при вводе значения,Почему данные следует поместитьApplication.EnableEvents = False диапазонов.
A. Проверка введенных значений
F11. Копируем код со списком.: Странно. У меня отчетности. Но заполнить
вызвать команду меню поддерживается и не абсолютную ссылку набудет больше 100, в ячейку формулу
- вводимого значения можноЕсли можно оставить ячейкуЧтобы изменить список допустимыхВвод данных станет быстрее которого нет в
- в таблицу? ПотомуnewVal = TargetКогда поставили курсор в (только вставьте своиНазначить имя для диапазона в общем формате её не можем!
- Обвести неверные данные отображается на листе. ячейки (например, в =КОРЕНЬ(2), т.к. результат настроить очень гибко. пустой, установите флажок элементов, просто измените и точнее, если списке, появлялось всплывающее
- что в этомApplication.Undo поле «Источник», переходим параметры).Private Sub Worksheet_Change(ByVal значений и в ячеек спокойно жуётПостоянно, даже после (Данные/ Работа с Однако все правила$A$1:$A$20D2 =1,4142135623731 (в зависимостиМожно разрешить ввод значенийИгнорировать пустые ячейки значения в списке-источнике ограничить значения в сообщение, установите флажок случае при добавленииoldval = Target на лист и Target As Range) поле источник вписать отрицательные числа. ввода одного любого данными/ Проверка данных/ проверки данных остаются).меньше, чем 400 от заданной в
- в ячейку только. или диапазон в ячейке вариантами изВыводить сообщение об ошибке и удалении элементовIf Len(oldval) <> выделяем попеременно нужные Dim lReply As это имя.Анюта символа выдает сообщение: Обвести неверные данные). доступными в книгеПри использовании инструмента Проверка=И(D1>100;D2 EXCEL точности), а определенного типа, выбравУстановите флажок поле раскрывающегося списка., выберите параметр в все раскрывающиеся списки, 0 And oldval ячейки. Long If Target.Cells.CountЛюбой из вариантов даст: Доброго времени суток. «ВВЕДЕННОЕ ЗНАЧЕНИЕ НЕ Ячейки с неверными и применяются при данных, предполагается, что
- Проверку данных применяем к вот =КОРЕНЬ(4) – необходимое условие изСписок допустимых значенийИсточникСначала создайте на листе поле созданные на основе <> newVal ThenТеперь создадим второй раскрывающийся > 1 Then такой результат. Подскажите,пожалуйста, работает функция ВЕРНО. НАБОР ЗНАЧЕНИЙ, данными будут обведены повторном открытии книги в ячейку будут ячейке сможете, ведь результат выпадающего (раскрывающегося) списка:Откройте вкладку. список допустимых элементов,
- Вид этой таблицы, будутTarget = Target список. В нем Exit Sub If ВПР вместе с КОТОРЫЕ МОГУТ БЫТЬ
красными овалами. Теперь в Excel 2010, вводиться константы (123,B1 =2, а этоЦелое числоСообщение для вводаМожно указать собственное сообщение а затем выполнитеи введите заголовок обновляться автоматически. Дополнительные & «,» & должны отражаться те Target.Address = «$C$2″Необходимо сделать раскрывающийся список ЕНД, но вместо ВВЕДЕНЫ В ЯЧЕЙКУ опять выделим ячеку если они не
| товар1, 01.05.2010 и |
. При этом в |
только 1 символ. |
|
. В ячейку разрешен. об ошибке, которое |
сортировку или расположите |
и сообщение. Если действия не требуются. newVal слова, которые соответствуют Then If IsEmpty(Target) со значениями из нулевого значения просто ОГРАНИЧЕН» |
|
А2 были изменены в пр.), хотя никто ячейкеСписок ввод только целыхЕсли вы хотите, чтобы будет отображаться при их в нужном вы не хотите,Теперь следует отсортировать данные |
Else |
выбранному в первом Then Exit Sub динамического диапазона. Если чистая ячейка, аПричем ввести символыи нажмем клавишу Excel 2007 или не запрещает вводитьD1. Наверное, самый интересный чисел, причем принадлежащих при выборе ячейки вводе недопустимых данных. порядке. В дальнейшем чтобы сообщение отображалось, в том порядке,Target = newVal списке названию. Если |
|
If WorksheetFunction.CountIf(Range(«Деревья»), Target) вносятся изменения в нужен именно 0( (буковки или циферки)F2 Excel 97-2003. и формулы. Ввведена формула =B1*2, тип данных. В определенному диапазону; |
появлялось всплывающее сообщение, |
На вкладке эти элементы могут снимите этот флажок. в котором ониEnd If «Деревья», то «граб», = 0 Then имеющийся диапазон (добавляются так фин.отчет). Что можно только в(войдем в режимПроверка данных явно срабатывает при |
|
этом случае проверяться а в этом случае вводДействительное |
установите флажок Данные служить источником для |
Не знаете, какой параметр должны отображаться вIf Len(newVal) = «дуб» и т.д. lReply = MsgBox(«Добавить или удаляются данные), надо сделать для одну ячейку в Правки), затем нажмем вводе в ячейку все равно будетD2 |
|
значений в ячейку. В ячейку разрешенПоказывать сообщениянажмите кнопку раскрывающегося списка данных. выбрать в поле раскрывающемся списке. 0 Then Target.ClearContents Вводим в поле |
введенное имя « |
они автоматически отражаются этого? этой строке (после |
ENTER значений с клавиатуры результат вычисления формулы.– формула =B1*3. можно ограничить ранее ввод только чисел,и введите заголовок
Проверка данных Если список небольшой,ВидВыделите на листе ячейку,Application.EnableEvents = True «Источник» функцию вида & _ Target в раскрывающемся списке.Лузер™ цифры «1» по- появится окно с последующим нажатием Вообще вводить формулы Хотя эта формула определенным списком. Например, в том числе и сообщение вили
В. Отображение комментария, если ячейка является текущей.
на него можно? в которую требуется
End If =ДВССЫЛ(E3). E3 – & » вВыделяем диапазон для выпадающего: если(енд(впр());0;впр()) горизонтали) , а с сообщением, что клавиши в ячейки с эквивалентна ограничению Действительное если в качестве
С. Вывод подробного сообщения об ошибке.
с десятичной частью соответствующие поля (доПроверить легко ссылаться иЧтобы отобразить сообщение, не поместить раскрывающийся список.End Sub ячейка с именем
D. Создание связанных диапазонов (списков)
выпадающий список?», vbYesNo списка. В главномGuest не вводятся символы введенное значение неверно.ENTER проверкой данных не с диапазоном от источника указать через
Е. Использование в правилах ссылок на другие листы
(нельзя ввести текст, 225 символов). Если, а затем откройте вводить элементы прямо препятствующее вводу данных,На ленте откройте вкладкуНе забываем менять диапазоны первого диапазона. + vbQuestion) If
меню находим инструмент: СПАСИБО!!! только в однуЕсть еще один способ. В этом случае советую – легко 50 до 133,33, точку с запятой дату ввести можно); вы не хотите, вкладку
в средстве проверки которые не содержатсяДанные на «свои». СпискиБывает, когда из раскрывающегося lReply = vbYes «Форматировать как таблицу».Алексей-74 строку, которая под обхода проверки данных. появляется окно с запутаться. В этом но при более единицы измерения товараДата. чтобы сообщение отображалось,Сообщение об ошибке данных. в раскрывающемся списке,и нажмите кнопку
F. Как срабатывает Проверка данных
создаем классическим способом. списка необходимо выбрать Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +Откроются стили. Выбираем любой.: Есть обратная задача: нумерацией (см. скриншот) Предположим, ввод в описанием ошибки. случае советую использовать
сложных связях ячеек, шт;кг;кв.м;куб.м, то ничегоПредполагается, что в снимите этот флажок..Создайте список допустимых элементов выберите вариантПроверка данных А всю остальную сразу несколько элементов. 1, 1) = Для решения нашей надо чтобы была . ячейку ограничен значениямиЕсли значения вставляются через Условное форматирование. этот прием может другого, кроме этих ячейку будут вводитьсяОткройте вкладкуПрименение проверки данных к для раскрывающегося списка.Сообщение
. работу будут делать Рассмотрим пути реализации Target End If задачи дизайн не пустая ячейка. ПробовалВышестоящая организация говорит, от 1 до Буфер обмена (ВставитьИспользуйте вкладку Сообщение для быть полезен 4-х значений из даты начиная отСообщение об ошибке ячейкам Для этого введите
или «Предупреждение». ЕслиПримечание: макросы. задачи. End If End имеет значения. Наличие вместо 0 ставить что у них 3. Теперь в значения) или с вывода, чтобы отображатьЗначение в ячейке, содержащей списка вам выбрать 01.01.1900 до 31.12.9999..Упрощение ввода данных с элементы на листе выбрать вариант «Сообщение», Если кнопкаНа вкладке «Разработчик» находимСоздаем стандартный список с If End Sub заголовка (шапки) важно. «», » « всё работает, мол любую другую ячейку использованием сочетания клавиш комментарий. возраст работника ( не удастся. В Подробнее о форматеЕсли вы хотите, чтобы помощью формы данных в одном столбце сообщение будет отображатьсяПроверка данных инструмент «Вставить» – помощью инструмента «Проверка
Сохраняем, установив тип файла В нашем примере ничего не получается. это у Вас без Проверки данныхCTRL+DВ отличие от обычногоС1 источнике можно указать Дата — в при вводе значения,На новом листе введите или строке без со значкомнедоступна, возможно, лист «ActiveX». Здесь нам данных». Добавляем в «с поддержкой макросов». это ячейка А1 Подскажите, что изменить. что-то с компьютерами! введем значение 4.(копирование значения из примечания (Рецензирование/ Примечание/), всегда должно быть диапазон ячеек, содержащий статье Как Excel которого нет в данные, которые должны пустых ячеек., а если защищен или является нужна кнопка «Поле исходный код листаПереходим на лист со со словом «Деревья».
G. Поиск ячеек с Проверкой данных
Igor67Но не со Выделим эту ячейку, ячейки сверху) или Создать примечание), которое больше числа полных заранее сформированный список
хранит дату и списке, появлялось всплывающее отображаться в раскрывающемсяВыделите ячейки, для которых «Предупреждение» — со значком общим. Разблокируйте определенные со списком» (ориентируемся готовый макрос. Как списком. Вкладка «Разработчик» То есть нужно: И не получится, всеми же, включая в Строке формул копируются Маркером заполнения пропадает после того, лет работы ( или ссылку на время
excel2.ru
MS Exel
сообщение, установите флажок списке. Желательно, чтобы нужно ограничить ввод.
области защищенной книги на всплывающие подсказки). это делать, описано — «Код» - выбрать стиль таблицы так как Ехс и домашние компы выделим значение 4
сверху вниз, то как курсор мышиD1 Именованную формулу. ПримерВремяПоказывать оповещения элементы списка содержались данных.Чтобы заблокировать пользователям ввод или отмените общийЩелкаем по значку – выше. С его
«Макросы». Сочетание клавиш со строкой заголовка. считает что результатом сотрудников! и скопируем его
проверка в явном уходит с ячейки) плюс 18 (минимальный приведен в статье
. Предполагается, что в, выберите параметр в
в таблице Excel.На вкладке данных, которые не доступ к листу, становится активным «Режим помощью справа от для быстрого вызова Получаем следующий вид формулы является строкаПОДСКАЖИТЕ КАК ИСПРАВИТЬ
в Буфер обмена.
виде не осуществляется. (если не активна
возраст приема на Выпадающий (раскрывающийся) список ячейку с Проверкой поле
Excel
Примечания:Данные содержатся в раскрывающемся а затем повторите конструктора». Рисуем курсором выпадающего списка будут – Alt + диапазона:
нулевой длины. Но ЭТУ БЕДУ? Теперь выделим ячейку Кроме того, при опция Показать все работу)
Другой данный этого типаТип в группе списке, выберите вариант шаг 3. (он становится «крестиком»)
добавляться выбранные значения.Private F8. Выбираем нужное
Ставим курсор в ячейку, на экране ничегоMakfromkz
с Проверкой данных копировании значений можно примечания), этот комментарий
=ЕСЛИ(C1>D1+18;ИСТИНА;ЛОЖЬ). В ячейку разрешен будет вводиться время.и введите заголовокПочему данные следует поместить
ВПР(как чтобы проставлялся 0)
ИнструментыОстановкаНа вкладке небольшой прямоугольник – Sub Worksheet_Change(ByVal Target имя. Нажимаем «Выполнить». где будет находиться не должно отображаться…: на ячейке с и нажмем вообще случайно удалить
отображается всегда, когдаПри заполнении таблицы данными
ввод значений удовлетворяющих Например, на рисунке
и сообщение. Если в таблицу? Потомунажмите кнопку.Параметры место будущего списка. As Range) OnКогда мы введем в
выпадающий список. ОткрываемGuest этой бедой войдитеCTRL+V правила Проверки данных, ячейка выделена. о возрасте и более сложным критериям.
ниже приведено условие, вы не хотите, что в этомПроверка данныхПримечание:в полеЖмем «Свойства» – открывается Error Resume Next пустую ячейку выпадающего
параметры инструмента «Проверка: Если для возврата в меню ДАННЫЕ—ПРОВЕРКА. Значение вставилось в например если в
После ввода ошибочного значения стаже работы можно
planetaexcel.ru
Выпадающий список в Excel с помощью инструментов или макросов
Для задания критериев когда в ячейку чтобы сообщение отображалось, случае при добавленииили Если вы не добавилиТип данных
перечень настроек. If Not Intersect(Target, списка новое наименование, данных» (выше описан значения определяетсья 0, и выберите любое ячейку! Кроме того, ячейке источнике не Проверка данных может поставить эту проверку
Создание раскрывающегося списка
необходимо использовать формулу. разрешено вводить время снимите этот флажок. и удалении элементовПроверить
заголовок и текст,выберите пунктВписываем диапазон в строку Range(«Е2:Е9»)) Is Nothing
- появится сообщение: «Добавить путь). В поле
- формула не вводится. значение или проанализируйте Проверка данных осталась определена Проверка данных,
- отобразить подробное сообщение для обеих ячеек Рассмотрим это условие принадлежащее только второй
Нажмите кнопку все раскрывающиеся списки,
.
Выпадающий список в Excel с подстановкой данных
по умолчанию выводитсяСписок ListFillRange (руками). Ячейку, And Target.Cells.Count = введенное имя баобаб «Источник» прописываем такую Пишет «Введенное значение какие данные хотят
- нетронутой в отличие а данные из о том, что (
- подробнее. половине дня, т.е.ОК созданные на основеПримечание: заголовок «Microsoft Excel». куда будет выводиться 1 Then Application.EnableEvents в выпадающий список?». функцию: неверно». Набор значений, видеть разработчики вашей
- от случая, когда нее вставляются через было сделано неC1D1При выбранном типе Другой, от 12:00:00 до. этой таблицы, будут
Если команда проверки недоступна, и сообщение «ВведенноеЩелкните поле
выбранное значение – = False If
Нажмем «Да» и добавиться
Протестируем. Вот наша таблица которые могут быть таблицы в проверяемых
через Буфер обмена, Буфер обмена с так. Это некий). Для этого нужно в поле Формула 23:59:59. Вместо утомительногоПосле создания раскрывающегося списка
- обновляться автоматически. Дополнительные возможно, лист защищен значение неверно. НаборИсточник в строку LinkedCell.
- Len(Target.Offset(0, 1)) = еще одна строка со списком на введены в ячейку ячейках
- например, вставляется значение использованием комбинации клавиш аналог Msgbox() из выделить сразу 2 нужно ввести формулу ввода значения 12:00:00 убедитесь, что он
- действия не требуются. или книга является значений, которые могути выделите диапазон Для изменения шрифта 0 Then Target.Offset(0, со значением «баобаб». одном листе: ограничен.»Увеличить из WORD. ЧтобыCTRL+V VBA. ячейки, вызвать Проверку для расчета логического можно использовать его работает правильно. Например,Теперь следует отсортировать данные общей. Если книга быть введены в списка. В примере и размера – 1) = TargetКогда значения для выпадающегоДобавим в таблицу новоеЮрий МLoony убедиться, что данные.Если в качестве Типа данных и немного значения. Если оно числовой эквивалент 0,5.
- можно проверить, достаточно в том порядке,
- является общей или ячейку, ограничен». данные находятся на Font. Else Target.End(xlToRight).Offset(0, 1) списка расположены на значение «елка».: Если нужно только
: http://office.microsoft.com/ru-ru/excel-help/HP010072600.aspx в ячейке неПоясним на примере. Предположим, данных на вкладке модифицировать формулу =ЕСЛИ($C1>$D1+18;ИСТИНА;ЛОЖЬ) принимает значение ИСТИНА,
Возможность ввода чисел ли ширины ячеек в котором они
Выпадающий список в Excel с данными с другого листа/файла
лист защищен, изменитьПосле создания раскрывающегося списка листе «Города» вСкачать пример выпадающего списка = Target End другом листе илиТеперь удалим значение «береза». визуально, то вДмитрий диденко соответствуют условиям определенным к ячейке
- Параметры мы выберемВсе данные в диапазоне то такое значение
- вместо времени следует для отображения всех должны отображаться в
параметры проверки данных убедитесь, что он диапазоне A2:A9. ОбратитеПри вводе первых букв If Target.ClearContents Application.EnableEvents в другой книге,Осуществить задуманное нам помогла параметрах убираем «Отображать: перешлите мне я в Проверке данных,
Как сделать зависимые выпадающие списки
А1
Список, то сможем ячеек разрешено вводить в из того, что элементов. Если вы раскрывающемся списке. невозможно. Дополнительные сведения работает правильно. Например,
- внимание на то, с клавиатуры высвечиваются = True End
- стандартный способ не «умная таблица», которая нулевыые значения» сделаю или скажу нужно вызвать команду
- применена Проверка данных сформировать связанные диапазоны.A1:A20 ячейку, если ЛОЖЬ, любой дате в решили изменить элементыВыделите на листе ячейку, о защите книги можно проверить, достаточно что строка заголовков подходящие элементы. И If End Sub работает. Решить задачу
Выбор нескольких значений из выпадающего списка Excel
легка «расширяется», меняется.Guest что делать. бесплатно меню Обвести неверные с условием проверки
- Тема так обширна,содержали уникальные значения то ваше значение EXCEL сопоставлено положительное раскрывающегося списка, см. в которую требуется см. в статье ли ширины ячеек отсутствует в диапазоне, это далеко неЧтобы выбранные значения показывались можно с помощьюТеперь сделаем так, чтобы: Супер! спасибо большое.Хочу поставить в ячейку данные (Данные/ Работа Другой, где в что она выделена=СЧЁТЕСЛИ($A$1:$A$20;A1)=1 ввести не получится. целое число, а статью Добавление и поместить раскрывающийся список. Защита книги. для отображения всех так как она
- все приятные моменты снизу, вставляем другой функции ДВССЫЛ: она можно было вводитьПод выпадающим списком понимается отрицательное значение -1 с данными/ Проверка поле формула введено в отдельную статьюИли В качестве аргумента следовательно времени (т.к. удаление элементов раскрывающегосяНа ленте откройте вкладкуОткройте вкладку элементов. не является одним данного инструмента. Здесь код обработчика.Private Sub
- сформирует правильную ссылку новые значения прямо содержание в одной мне выдает Введеное данных/ Обвести неверные
=СТРОКА(A1)=1, т.е. для Связанный список.
=ПОИСКПОЗ(A1;$A:$A;0)=СТРОКА(A1) формулы нужно использовать
это часть суток), списка. Чтобы удалитьДанныеПараметры
Если список элементов для
из вариантов, доступных
можно настраивать визуальное
Worksheet_Change(ByVal Target As
на внешний источник в ячейку с ячейке нескольких значений.
значение неверно. Набор данные). всех ячеек из
В EXCEL 2007 в Проверке
Необходимо выделить ячейки
ссылку на саму
соответствует дробная часть раскрывающийся список, см.
и нажмите кнопку
и во всплывающем
раскрывающегося списка находится
для выбора. представление информации, указывать Range) On Error информации. этим списком. И Когда пользователь щелкает
Выпадающий список с поиском
- значений, которые могутЕсли на листе много первой строки условие данных, как иА1:А20 ячейку, к которой
- числа (например, 0,5 статью Удаление раскрывающегосяПроверка данных меню на другом листеЕсли можно оставить ячейку
- в качестве источника Resume Next If
- Делаем активной ячейку, куда данные автоматически добавлялись по стрелочке справа, быть введены в ячеек с Проверкой Проверки данных будет в Условном форматировании, вызвать инструмент Проверка
применяется Проверка данных
– это полдень). списка..Разрешить и вы хотите пустой, установите флажок сразу два столбца. Not Intersect(Target, Range(«Н2:К2»)) хотим поместить раскрывающийся в диапазон.
exceltable.com
появляется определенный перечень.
Возможно, вас интересует: функция ЗАМЕНИТЬ в Excel.
- «Найти и заменить» в Excel
- Горячие клавиши
- Процедура «Найти и заменить» не работает
- Подстановочные знаки, или как найти “звёздочку”
- Замена нескольких значений на несколько
- Массовая замена с помощью функции “ПОДСТАВИТЬ”
- Файл-шаблон с формулой множественной замены
- Заменить несколько значений на одно
- С помощью функции «ПОДСТАВИТЬ»
- С помощью регулярных выражений
- Массовая замена в !SEMTools
- Пример: замена символов по вхождению
- Пример: замена списка слов на другой список слов
Процедура поиска и замены данных — одна из самых востребованных в Excel. Базовая процедура позволяет заменить за один заход только одно значение, но зато множеством способов. Рассмотрим, как эффективно работать с ней.
Горячие клавиши
Сочетания клавиш ниже заметно ускорят работу с инструментом:
- Для запуска диалогового окна поиска — Ctrl + F.
- Для запуска окна поиска и замены — Ctrl + H.
- Для выделения всех найденных ячеек (после нажатия кнопки «найти всё») — Ctrl + A.
- Для очистки всех найденных ячеек — Ctrl + Delete.
- Для ввода одних и тех же данных во все найденные ячейки — Ввод текста, Ctrl + Enter.
Смотрите gif-пример: здесь я произвожу поиск ячеек с дальнейшим их редактированием. В отличие от замены, редактирование найденных ячеек позволяет быстро менять их содержимое целиком.
Процедура «Найти и заменить» не работает
Я сам когда-то неоднократно впадал в ступор в этой ситуации. Уверен и видишь своими глазами, что искомый паттерн в данных есть, но Excel при выполнении процедуры поиска сообщает:
или при замене:
Так вот, совет нажать кнопку “Параметры” в обоих этих сообщениях действительно полезен — там наверняка активен чекбокс “Учитывать регистр” или “Ячейка целиком”, которые мешают Excel найти искомое. Excel, в свою очередь, сохраняет конфигурацию последнего поиска.
Подстановочные знаки, или как найти “звёздочку”
Сухая официальная справка по Excel сообщает:
— что можно использовать подстановочные символы “*” и “?”;
— что “*” и “?” означают несколько символов, включая их отсутствие, и один любой символ;
— что их можно использовать для соответствующих процедур поиска.
Чего не говорит справка, так это того, что в комбинации с опцией “ячейка целиком” эти символы позволяют, не прибегая к помощи расширенного фильтра и процедуры поиска группы ячеек:
- находить ячейки, заканчивающиеся на определенный символ, слово или текст,
- находить ячейки, начинающиеся с определенного символа, слова или текста,
- находить непустые ячейки.
Если хотите поподробнее познакомиться с этими и другими специальными символами, читайте соответствующую статью.
В примере ниже мы находим все двузначные числа, затем числа, заканчивающиеся и начинающиеся на 7, и, наконец, все непустые ячейки. Напомню, выделить все результаты поиска помогает горячее сочетание клавиш Ctrl + A.
Так а как найти звёздочку?
Действительно, забыл. Чтобы найти «звёздочку», нужно в окошке поиска ставить перед ней знак ~ (тильда), он находится обычно под клавишей Esc. Это позволяет экранировать “звездочку”, как и вопросительный знак, и не воспринимать их как служебные символы.
Замена нескольких значений на несколько
Массовая замена в Excel — довольно частая потребность. Очень часто нужно массово и при этом быстро заменить несколько символов, слов и т.д. на другие. При этом на текущий момент простого инструмента в стандартном функционале Excel нет.
Тем не менее, если очень нужно, любую задачу можно решить. В зависимости от того, на что вы хотите заменить, могут помочь комбинации функций, регулярные выражения, а в самых сложных случаях — надстройка !SEMTools.
Эта задача более сложная, чем замена на одно значение. Как ни странно, функция «ЗАМЕНИТЬ» здесь не подходит — она требует явного указания позиции заменяемого текста. Зато может помочь функция «ПОДСТАВИТЬ».
Массовая замена с помощью функции “ПОДСТАВИТЬ”
Используя несколько условий в сложной формуле, можно производить одновременную замену нескольких значений. Excel позволяет использовать до 64 уровней вложенности — свобода действий высока. Например, вот так можно перевести кириллицу в латиницу:
При этом, если использовать в качестве подставляемого фрагмента пустоту, можно использовать функцию для удаления нескольких символов, — смотрите, как удалить цифры из ячейки этим способом.
Но у решения есть и свои недостатки:
- Функция ПОДСТАВИТЬ регистрозависимая, что заставляет при замене одного символа использовать два его варианта — в верхнем и нижнем регистрах. Хотя в некоторых случаях, как, например, на картинке выше, это и преимущество.
- Максимум 64 замены — хоть и много, но все же ограничение.
- Формально процедура замены таким способом будет происходить массово и моментально, однако длительность написания таких формул сводит на нет это преимущество. За исключением случаев, когда они будут использоваться многократно.
Файл-шаблон с формулой множественной замены
Вместо явного прописывания заменяемых паттернов в формуле существует вариант использовать внутри нее ссылки на ячейки, значения в которых можно прописывать на свое усмотрение. Это сократит время, так как не требует редактирования сложной формулы.
Файл доступен по ссылке, но можно и не скачивать его, а просто скопировать текст формулы ниже и вставить ее в любую ячейку, кроме диапазона A1:B64. Формула заменяет в ячейке C1 значения в столбце A стоящими напротив в столбце B.
А вот и она сама (тройной клик по любой части текста = выделить всю формулу): обращается к ячейке D1, делая 64 замены по правилам, указанным в ячейках A1-B64. При этом в столбцах можно удалять значения — это не нарушит её работу.
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(D1;$A$1;$B$1);$A$2;$B$2);$A$3;$B$3);$A$4;$B$4);$A$5;$B$5);$A$6;$B$6);$A$7;$B$7);$A$8;$B$8);$A$9;$B$9);$A$10;$B$10);$A$11;$B$11);$A$12;$B$12);$A$13;$B$13);$A$14;$B$14);$A$15;$B$15);$A$16;$B$16);$A$17;$B$17);$A$18;$B$18);$A$19;$B$19);$A$20;$B$20);$A$21;$B$21);$A$22;$B$22);$A$23;$B$23);$A$24;$B$24);$A$25;$B$25);$A$26;$B$26);$A$27;$B$27);$A$28;$B$28);$A$29;$B$29);$A$30;$B$30);$A$31;$B$31);$A$32;$B$32);$A$33;$B$33);$A$34;$B$34);$A$35;$B$35);$A$36;$B$36);$A$37;$B$37);$A$38;$B$38);$A$39;$B$39);$A$40;$B$40);$A$41;$B$41);$A$42;$B$42);$A$43;$B$43);$A$44;$B$44);$A$45;$B$45);$A$46;$B$46);$A$47;$B$47);$A$48;$B$48);$A$49;$B$49);$A$50;$B$50);$A$51;$B$51);$A$52;$B$52);$A$53;$B$53);$A$54;$B$54);$A$55;$B$55);$A$56;$B$56);$A$57;$B$57);$A$58;$B$58);$A$59;$B$59);$A$60;$B$60);$A$61;$B$61);$A$62;$B$62);$A$63;$B$63);$A$64;$B$64)
Заменить несколько значений на одно
С помощью функции «ПОДСТАВИТЬ»
При замене нескольких значений на одно и то же механика работы формул на основе нескольких уровней вложенности не будет отличаться от замены нескольких на несколько. Просто третий аргумент (на что заменить) на всех уровнях вложенности будет один и тот же. Кстати, если оставить его пустым (кавычки без символов между ними), то это позволит удалить определенные символы. Пример: удалить цифры из ячейки путем замены на пустоту:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"1";"");"2";"");"3";"");"4";"");"5";"");"6";"");"7";"");"8";"");"9";"");"0";"")
С помощью регулярных выражений
Важно: регулярные выражения не поставляются в Excel “из коробки”, но формулы ниже доступны бесплатно, если установить надстройку !SEMTools.
Регулярные выражения (RegEx, регулярки) — наиболее удобное решение, когда нужно заменить несколько символов на один. Все эти несколько символов обычным способом безо всяких разделителей нужно перечислить внутри квадратных скобок. Примеры формул:
=regexreplace(A1;"d";"#")
=regexreplace(A1;"w";"#")
=regexreplace(A1;"а-яА-Я";"#")
=regexreplace(A1;"s";"_")
Первая заменяет на символ “#” все цифры, вторая — все английские буквы, а третья — все кириллические символы в верхнем и нижнем регистре. Четвёртая заменяет любые пробелы, в том числе табуляцию и переносы строк, на нижнее подчеркивание.
Если же нужно заменять не символы, а несколько значений, состоящих в свою очередь из нескольких букв, цифр или знаков, синтаксис предполагает уже использование круглых скобок и вертикальной черты “|” в качестве разделителя.
Массовая замена в !SEMTools
Надстройка для Excel !SEMTools позволяет в пару кликов производить замены на всех уровнях:
- символов и их сочетаний,
- паттернов регулярных выражений,
- слов,
- целых ячеек (в некоторой степени аналог ВПР).
При этом процедуры изменяют исходный диапазон, что экономит время. Все что нужно —предварительно выделить его, определиться с задачей, вызвать нужную процедуру и выделить два столбца сопоставления заменяемых и замещающих значений (предполагается, что если вы знаете, что на что менять, то и такие списки есть).
Пример: замена символов по вхождению
Аналог обычной процедуры замены без учета регистра заменяемых символов, по вхождению. С одним отличием: здесь замена массовая и можно выбрать сколько угодно строк с парами «заменяемое-заменяющее» значение.
Ниже пример с единичными символами, но паттерны могут быть какими угодно в зависимости от вашей задачи.
Пример: замена списка слов на другой список слов
На этом примере — замена списка слов на другой список, в данном случае на одно и то же слово. Здесь решается задача типизации разнородных фраз путем замены слов, содержащих латиницу и цифры, на одно слово. Далее после этой операции можно будет посчитать уникальные значения в столбце, чтобы выявить наиболее популярные сочетания.
С версии !SEMTools 9.18.18 появилась опция: при замене списка слов не учитывать пунктуацию в исходных предложениях, а регистр слов теперь сохраняется.
Инструменты находятся в группе макросов «ИЗМЕНИТЬ» в отдельном меню и для удобства продублированы в меню «Изменить символы», «Изменить слова» и «Изменить ячейки».
Скачивайте надстройку !SEMTools и делайте массовую замену символов, слов или целых списков в Excel!
Смотрите также по теме поиска и замены данных в Excel:
- Найти заглавные/строчные буквы в ячейке;
- Найти латиницу или кириллицу в тексте;
- Найти числа в текстовых ячейках;
- Обнаружить текстовые символы;
- Функция НАЙТИ в Excel;
- Функция ПОИСК в Excel;
- Функция ЗАМЕНИТЬ в Excel;
- Найти определенные символы в ячейках Excel.
Skip to content
Одной из наиболее полезных функций при вводе данных является возможность использовать выпадающий список в Excel. Он позволяет выбирать значение из предварительно определенного перечня и разрешает вводить только те данные, которые соответствуют вашим требованиям. Мы предложим вам несколько простых способов, как создавать выпадающие списки в Excel. Более сложные способы, основанные на динамических диапазонах и использовании данных из других таблиц, мы также рассмотрим.
Зачем нужен выпадающий список?
Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся значения. К примеру, фамилии сотрудников, названия товаров или делать выбор в ячейке Excel вида «да – нет». Что может случиться? Конечно, в первую очередь будут ошибки при вводе. Человеческий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем сотрудников. Далее придётся искать ошибки, исправлять их и вновь повторять расчет.
Ну и конечно же, все время руками вводить одни и те же слова – просто бессмысленная работа и потеря времени. Вот здесь-то выпадающие списки нам и пригодятся. При нажатии выпадает перечень заранее определённых значений, из которых просто необходимо указать какое-то одно.
Важно то, что вы теперь будете не писать, а выбирать их с помощью мыши или клавиатуры. Это значительно ускоряет работу, а также гарантирует защиту от случайных ошибок. Проверка того, что мы записали в таблицу, теперь уже не нужна.
1 — Самый быстрый способ создать выпадающий список.
Как проще всего добавить выпадающий список в таблицу Excel? Всего один щелчок правой кнопкой мыши по пустой клетке под столбцом с данными, затем команда контекстного меню «Выберите из раскрывающегося списка» (Choose from drop-down list). А можно просто стать в нужное место и нажать сочетание клавиш Alt+стрелка вниз. Появится отсортированный перечень уникальных ранее введенных значений.
Способ не работает, если нашу ячейку и столбец с записями отделяет хотя бы одна пустая строка или вы хотите ввести то, что еще не вводилось выше. На нашем примере это хорошо видно.
2 — Используем меню.
Давайте рассмотрим небольшой пример, в котором нам нужно постоянно вводить в таблицу одни и те же наименования товаров. Выпишите в столбик данные, которые мы будем использовать (например, названия товаров). В нашем примере — в диапазон G2:G7.
Выделите ячейку таблицы (можно сразу несколько), в которых хотите использовать ввод из заранее определенного перечня. Там мы разместим наш выпадающий список.
Далее в главном меню выберите на вкладке Данные – Проверка… (Data – Validation). Затем укажите пункт Тип данных (Allow) и выберите вариант Список (List). Поставьте курсор в поле Источник (Source) и впишите в него адреса с эталонными значениями элементов — в нашем случае G2:G7. Рекомендуется также использовать здесь абсолютные ссылки (для их установки нажмите клавишу F4).
Бонусом здесь идет возможность задать подсказку и сообщение об ошибке, если автоматически вставленное значение вы захотите изменить вручную. Для этого существуют вкладки Подсказка по вводу (Input Message) и Сообщение об ошибке (Error Alert).
В качестве источника значений для выпадающего списка в Excel можно использовать также и именованный диапазон.
К примеру, диапазону I2:I13, содержащему названия месяцев, можно присвоить наименование «месяцы». Затем имя можно ввести в поле «Источник».
Кроме того, и источник и в виде обычного диапазона ячеек, и именованный диапазон могут находиться на других листах вашей рабочей книги.
Но вы можете и не использовать диапазоны или ссылки, а просто определить возможные варианты прямо в поле «Источник». К примеру, чтобы реализовать в таблице Excel простейший выбор «да – нет», вы можете вписать туда –
Да;Нет
Используйте для разделения значений точку с запятой, запятую, либо другой символ, установленный у вас в качестве разделителя элементов. (Смотрите Панель управления — Часы и регион — Форматы — Дополнительно — Числа.)
3 — Создаем элемент управления.
Вставим на лист новый объект – элемент управления «Поле со списком» с последующей привязкой его к данным на листе Excel. Делаем:
- Откройте вкладку Разработчик (Developer). Если её не видно, то в Excel 2007 нужно нажать кнопку Офис – Параметры – флажок Отображать вкладку Разработчик на ленте (Office Button – Options – Show Developer Tab in the Ribbon) или в версии 2010–2013 и выше щелкните правой кнопкой мыши по ленте, выберите команду Настройка ленты (Customize Ribbon) и включите отображение вкладки Разработчик (Developer) с помощью флажка.
- Найдите нужный значок среди элементов управления (см.рисунок ниже).
Вставив элемент управления на рабочий лист, щелкните по нему правой кнопкой мышки и выберите в появившемся меню пункт «Формат объекта». Далее указываем диапазон ячеек, в котором записаны допустимые значения для ввода. В поле «Связь с ячейкой» укажем, куда именно поместить результат. Важно учитывать, что этим результатом будет не само значение из указанного нами диапазона, а только его порядковый номер.
Но нам ведь нужен не этот номер, а соответствующее ему слово. Используем функцию ИНДЕКС (INDEX в английском варианте). Она позволяет найти в списке значений одно из них соответственно его порядковому номеру. В качестве аргументов ИНДЕКС укажите диапазон ячеек (F5:F11) и адрес с полученным порядковым номером (F2).
Формулу в F3 запишем, как показано на рисунке:
=ИНДЕКС(F5:F11;F2)
Как и в предыдущем способе, здесь возможны ссылки на другие листы, на именованные диапазоны.
Обратите также внимание, что здесь мы не привязаны ни к какой конкретному месту таблицы. Таким списком Excel удобно пользоваться, поскольку его можно свободно «перетаскивать» мышкой в любое удобное место. Для этого на вкладке «Разработчик» нужно активизировать режим конструктора.
4 — Элемент ActiveX
Действуем аналогично предыдущему способу, но выбираем иконку чуть ниже — из раздела «Элементы ActiveX».
Определяем перечень допустимых значений (1). Обратите внимание, что здесь для показа можно выбирать сразу несколько колонок. Затем выбираем адрес, по которому будет вставлена нужная позиция из перечня (2).Указываем количество столбцов, которые будут использованы как исходные данные (3), и номер столбца, из которого будет происходить выбор для вставки на лист (4). Если укажете номер столбца 2, то в А5 будет вставлена не фамилия, а должность. Можно также указать количество строк, которое будет выведено в перечне. По умолчанию — 8. Остальные можно прокручивать мышкой (5).
Этот способ сложнее предыдущего, но зато возвращает сразу значение, а не его номер. Поэтому необходимость в промежуточной ячейке и обработке ее при помощи ИНДЕКС — отпадает. Думаю, таким списком пользоваться гораздо удобнее.
5 — Выпадающий список в Excel с автозаполнением
Задача: Создать перечень, в который будут автоматически добавляться значения из заданного динамического диапазона. Если в любую ячейку этого диапазона будут внесены изменения, то сразу же изменится и набор предлагаемых к выбору значений. Никакие формулы и настройки здесь корректировать не нужно.
Вот как автозаполнение может выглядеть на простом примере:
Способ 1. Укажите заведомо большой источник значений для списка.
Самая простая и несложная хитрость. В начале действуем по обычному алгоритму действий: в меню выбираем на вкладке Данные – Проверка … (Data – Validation). Из перечня Тип данных (Allow) выберите вариант Список (List). Поставьте курсор в поле Источник (Source). Зарезервируем в списке набор с большим запасом: например, до 55-й строки, хотя занято у нас только 7. Обязательно не забудьте поставить галочку в чекбоксе «Игнорировать пустые …». Тогда ваш «резерв» из пустых значений не будет вам мешать.
Действительно самый простой способ, но не слишком удобный. Ведь зарезервированное место может и закончиться…
Конечно, в качестве источника можно указать и весь столбец:
=$A:$A
Но обработка такого большого количества ячеек может несколько замедлить вычисления. Особенно в больших таблицах Excel.
Способ 2. Применяем именованный диапазон.
Именованный диапазон отличается от обычного тем, что ему присвоено определенное наименование. С ним гораздо проще работать, так как не нужно вводить ссылку, а достаточно просто указать его имя. Давайте рассмотрим небольшой пример.
В столбце А находятся имена сотрудников, которые мы будем вводить. Перечень может быть сколь угодно длинным. Нам необходимо, чтобы каждая новая запись включалась в раскрывающийся список без всяких дополнительных действий с нашей стороны.
Выделим имеющийся в нашем распоряжении перечень имен A2:A10. Затем присвоим ему название, заполнив поле «Имя», находящееся левее строки формул.
Так вы присвоите какое-то имя этому диапазону Excel.
Создадим в С2 перечень значений. В качестве источника для него укажем выражение
=имя
Недостатком работы с таким выпадающим списком в Excel является то, что новые значения нельзя просто дописывать в конец используемого перечня. Они останутся за пределами именованного диапазона. Если что-то нужно добавить, то их придется вставлять внутрь диапазона, использовав вставку пустой строки.
Перечень ещё можно отсортировать, чтобы удобно было пользоваться.
Главное неудобство пользования таким списком заключается в том, что используемый нами диапазон — статический. Автоматически его размеры измениться не могут. Согласитесь, не слишком удобный и технологичный способ. Слишком много ручных операций.
А теперь давайте пойдем дальше и посмотрим, как можно работать с динамическим диапазоном, который автоматически подстраивается под вводимые значения.
Способ 3. Выпадающий список на основе «умной» таблицы Excel.
Начиная с 2007 года таблица для Excel — уже не просто набор строк и столбцов. Если вы просто расположите показатели с привычном для нас табличном виде, то он не будет считать их таблицей. Существует специальное форматирование, после чего диапазон начинает вести себя как единое целое, приобретая целый ряд интересных свойств. В частности, он начинает сам отслеживать свои размеры, динамически изменяясь при корректировке данных.
Любой набор значений в таблице может быть таким образом преобразован. Например, A1:A8. Выделите их мышкой. Затем преобразуйте в таблицу, используя меню Главная — Форматировать как таблицу (Home — Format as Table). Укажите, что в первой строке у вас находится название столбца. Это будет «шапка» вашей таблицы. Внешний вид может быть любым: это не более чем внешнее оформление и ни на что больше оно не влияет.
Как уже было сказано выше, «умная» таблица хороша для нас тем, что динамически меняет свои размеры при добавлении в нее информации. Если в строку ниже нее вписать что-либо, то она тут же присоединит к себе её. Таким образом, новые значения можно просто дописывать. К примеру, впишите в A9 слово «кокос», и таблица тут же расширится до 9 строк.
Следовательно, автоматическое обновление набора используемой информации в списке можно организовать, если использовать содержимое какого-либо столбца «умной» таблицы.
Осталось только обозначить ее как источник. Проблема заключается в том, что программа в качестве источника в списке не понимает выражение вида
=Таблица1[Столбец1]
и не считает его формулой. Хотя в обычных выражениях на листе вашей рабочей книги это вполне будет работать. Эта конструкция обозначает ссылку на первый столбец. Но в поле «Источник» она почему-то игнорируется.
Чтобы использовать «умную таблицу» как источник, нам придется пойти на небольшую хитрость и воспользоваться функцией ДВССЫЛ (INDIRECT в английском варианте). Эта функция преобразует текстовую переменную в обычную ссылку.
Формула теперь будет выглядеть следующим образом:
=ДВССЫЛ(«Таблица5[Продукт]»)
Таблица5 — имя, автоматически присвоенное «умной таблице». У вас оно может быть другим. На вкладке меню Конструктор (Design) можно изменить стандартное имя на свое (но без пробелов!). По нему мы сможем потом адресоваться к нашей таблице на любом листе книги.
«Продукт» — название нашего первого и единственного столбца, присвоено по его заголовку.
Не забудьте также заключить все выражение в кавычки, чтобы обозначить его как текстовую переменную.
Теперь если в A9 вы допишете еще один фрукт (например, кокос), то он тут же автоматически появится и в нашем перечне. Аналогично будет, если мы что-то удалим. Задача автоматического увеличения выпадающего списка значений решена.
Надеемся, вы сможете теперь с помощью списков без ошибок вводить часто повторяющиеся данные в таблицу Excel при помощи выпадающего списка.
А вот еще полезная для вас информация:
Как сделать зависимый выпадающий список в Excel? — Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах,…