С помощью Проверки данных [Data Validation] можно организовать ввод данных путем выбора из предлагаемого списка, значения которого зависят от другого списка. Причем, используя функцию СМЕЩ [OFFSET] можно создать вариант, когда добавленные исходные значения будут отображаться в списках для выбора нужных значений. Если значения списка зависят от выбранного значения из другого списка, то можно создать связанный (зависимый) список нужных значений. Это позволит в значительной степени избежать не корректных комбинаций вводимых значений.
Например, в поле Европа происходит выбор одного из двух значений: Западная или Восточная, после этого в поле Страна предлагается список с соответствующими значениями.
Последовательность создания:
Выделить ячейку F2, где будет выбираться Европа.
На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника выделить ячейки B2 и C2
- Ячейкам значений стран (данные в столбцах B и C) необходимо присвоить имена – Западная и Восточная, с возможностью автоматического определения диапазона ячеек по мере изменения количества значений в соответствующих столбцах:
На вкладке Формулы [Formulas] выбрать Диспетчер имен [Name Manager] или нажать клавиши Ctrl+F3.
Создать имена с использованием функции СМЕЩ:
- Выделить ячейку F3, где будет выбираться Страна.
На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника ввести формулу:
=ЕСЛИ($F$2=”Западная”;Западная;Восточная)
[=IF ($F$2=”Западная”;Западная;Восточная)], где F2 – ячейка, которая содержит значение, выбираемого из первого списка.
При добавлении новых данных, они будут сразу показаны в выпадающем списке:
Примечание: подразумевается, что элементы списка вводятся последовательно, т.е. элемент списка не содержит пустые ячейки.
Порой нужно осуществить замену одних данных другими. Например, многократно было введено значение с опечаткой или произошла смена одного названия на другое и т.д. Причем данные могут быть как на одном листе, так и на многих.
Делать такие замены вручную слишком долго. Используя диалог Найти и Заменить – это можно сделать быстро:
- На вкладке Главная [Home] в группе Редактирование [Edit] раскрыть список на кнопке Найти и выделить [Find & Select] и выбрать команду Заменить [Replace] или нажать на клавиатуре клавиши Ctrl+H.
- В поле Найти [Find what] ввести данные, которую необходимо найти.
- В поле Заменить на [Replace with] ввести данные, которыми надо заменить искомые значения.
- Нажать кнопку Параметры>> [Options>>], чтобы задать дополнительные параметры поиска:
Искать [Within] – на листе [Sheet] или в книге [Workbook].
Просматривать [Search] – по строкам [By Rows] или по столбцам [By Columns].
Область поиска [Look in] – формулы [Formulas], значения [Values] или примечания [Comments].
Учитывать регистр [Match case] – если флажок не установлен, то происходит поиск указанных данных без учета регистра.
Ячейка целиком [Match entire cell contents] – точность поиска введенных данных, если флажок не установлен, но идет поиск типа “содержит”.
Формат [Format] – уточнение формата искомой информации, например, параметры шрифта или цветовое оформление.
- Выполнить замену данных:
Заменить все [Replace All] – замена всех найденных данных
Вручную: сперва нажать кнопку Найти далее [Find Next] – будет выделена ячейка с указанными данными для поиска. Чтобы произвести замену – нажать кнопку Заменить [Replace], чтобы перейти к поиску следующего нужного значения нажать кнопку Найти далее [Find Next] и т.д.
- Нажать кнопку Закрыть [Close] для закрытия окна поиска или клавишу Esc.
Вероятно, Вы сталкивались с желанием ограничить перемещения по листу. Или, к примеру, разрешить ввод только в определенные ячейки (области).
Эту задачу легко решить, используя защиту ячеек листа.
- Первым действием, необходимо настроить атрибуты ячеек:
Выделить ячейки.
Щелкнуть правой кнопкой мыши по выделению и выбрать Формат ячеек [Format cells] или нажать клавиши Ctrl+1.
Открыть вкладку Защита [Protection].
Снять атрибут Защищаемая ячейка [Locked] с ячеек, которые после установки защиты листа должны изменяться.
- Вторым действием установить защиту ячеек листа:
На вкладке Рецензирование [Review], в группе Изменения [Changes], выбрать Защитить лист [Protect Sheet].
Флажок Защитить лист и содержимое защищаемых ячеек [Protect worksheet and contents of locked cells] должен быть установлен.
В списке Разрешить всем пользователям этого листа [Allow all users of this worksheet to] снять флажок Выделение заблокированных ячеек [Select Locked Cells].
Установить пароль в поле Пароль для отключения защиты листа [Password to unprotect sheet], иначе защита легко будет снята другим пользователем.
Пароль может содержать только буквы a–z, цифры 0–9 и специальные знаки, например !, @, #, $, %, ^, &, *, (, ), +, =
