Excel подсказки при вводе
Обновлено: 07.07.2024
Функция автозавершения Excel позволяет легко вводить один и тот же текст в несколько ячеек. Используя автозавершение, введите несколько первых букв вашей записи в ячейку, и Excel автоматически завершит ее на основе других записей, которые вы уже сделали в столбце.
В дополнение к уменьшению количества текста, который необходимо набирать, эта функция гарантирует, что ваши записи будут внесены правильно и согласованно.
Рассмотрим, как это работает. Предположим, вы вводите информацию о продукте в столбец. Один продукт называется «сахар-песок». Первый раз, когда вы вводите Сахар-песок в ячейку, Excel запоминает эту запись. Позже, когда вы начнете набирать слово Сахар в этом столбце, Excel распознает слово по нескольким первым буквам и завершит его ввод за вас. Просто нажмите Enter, и все готово.
Автозавершение также изменяет регистр букв автоматически. Если начать ввод со слова сахар (со строчной буквой «с») во второй записи, Excel сделает букву «с» прописной в соответствии с предыдущей записью в столбце.
Если столбец содержит несколько записей, которые соответствуют первым нескольким символам, Excel не отобразит предложение по автоматическому завершению, пока новая запись не будет соответствовать одной из старых однозначным образом. Например, если столбец также содержит продукт под названием «сахар-рафинад», автозавершение не будет реализовано, пока вы не введете либо первую букву слова «песок», либо первую букву слова «рафинад».
Вы также можете получить доступ к ориентированной на использование мыши версии автозавершения, щелкнув правой кнопкой мыши на ячейке и выбрав в контекстном меню пункт Выбрать из раскрывающегося списка. Excel отобразит список, в котором содержатся все записи в текущем столбце. Остается просто выбрать ту, которую вы хотите (рис. 32.1).
Рис. 32.1. Выбор пункта контекстного меню Выбрать из раскрывающегося списка позволяет отображать список записей в столбце
Вы даже можете получить доступ к списку с клавиатуры: нажмите Shift+F10 для отображения контекстного меню, а затем нажмите клавишу К (горячую клавишу для команды Выбрать из раскрывающегося списка). Используйте кнопки со стрелками, чтобы сделать свой выбор, и нажмите Enter.
Имейте в виду, что функция автозавершения работает только в пределах смежных столбцов ячеек. Если у вас есть, например, пустая строка, автозавершение будет анализировать только содержимое ячеек под пустой строкой. Если функция автозавершения покажется вам отвлекающей, ее можно отключить в разделе Дополнительно диалогового окна Параметры Excel (выберите Файл ► Параметры). Флажок Автозавершение значений ячеек находится в области Параметры правки.
Вот и моя демонстрационная excel-таблица. Как её заполнять?
Подсказки в MS Excel
Первый способ вставки подсказки
Самый проcтой способ вставить примечание в лист MS Excel
Удалить примечания можно только из контекстного меню правой кнопки мыши
Второй способ вставки подсказки
Ещё один способ ввести подсказку интересен нам только с точки зрения того, о чем пойдет разговор дальше (фильтрации ввода), тем не менее знать о нем полезно.
Примечания в Excel с помощью проверки данных
Проверка по числу введенных символов
Проверка по числу введенных символов в MS Excel
Слишком длинный текст! Excel стоит на страже и не пропустит такой очевидной ошибки
Проверка по числу
Ещё один хороший способ фильтрации который пригодится без сомнения: фильтрация по числу. Установите фильтр подобный указанному на рисунке и навсегда избавьтесь от проблем с тем, что в поле цена появляются буквы, пробелы и лишние символы.
Проверка по числу в Excel
Фильтрация на ввод чисел работает ничуть не хуже
Выбор из имеющегося списка значений
А теперь, сделаем проверку по выпадающему списку в MS Excel. Данные введем вручную, через точку с запятой.
Выбор из списка на листе MS Excel. Других данных ввести не удастся.
Классический выпадающий список в ячейке листа Excel, сделанный через Данные - Проверка (Data - Validation) - простая и удобная штука, которую ежедневно применяют очень многие пользователи. Однако, у этого списка есть один весьма серьезный недостаток - в нём нет быстрого поиска по первым символам, т.е. фильтрации (отбора) только тех значений, куда введённый фрагмент входит как подстрока. Это серьезно ухудшает удобство пользования даже если в списке всего пара-тройка десятков позиций, а при нескольких сотнях убивает юзабилити напрочь.
Давайте рассмотрим как всё же реализовать подобный трюк. В качестве подопытного кролика возьмём список 250 лучших фильмов по версии IMDb:
Конечная цель - создать выпадающий список (ячейка G3), в котором можно будет быстро находить нужные фильмы, введя только жанр, год или фрагмент названия, например "гамп".
Шаг 1. Определяем, кто нам нужен
Сначала нам нужно понять, какие из исходных ячеек нужно показывать в списке, т.е. определить содержится ли введённый в выпадающем списке текст (например, жанр "детектив") в названии фильма. Для этого добавим слева от исходных данных еще один столбец с функцией ПОИСК (SEARCH ) , которая ищет заданную подстроку в тексте и выдает либо порядковый номер символа, где он был обнаружен, либо ошибку, если его там нет:
Теперь завернем нашу формулу в функцию проверки ЕЧИСЛО (ISNUMBER) , которая превратит числа в логическую ИСТИНУ (TRUE) , а ошибки - в ЛОЖЬ (FALSE) :
Теперь сделаем так, чтобы ЛОЖЬ превратилась в 0, а вместо ИСТИНА в столбце появились последовательно возрастающие индексы-числа 1,2,3. и т.д. Это можно сделать с помощью добавления к нашей же формуле ещё парочки функций:
Здесь функция ЕСЛИ (IF) проверяет что мы имеем (ИСТИНУ или ЛОЖЬ), и
- если была ИСТИНА, то выводит максимальное значение из всех вышестоящих чисел + 1
- если была ЛОЖЬ, то выводит 0
Шаг 2. Отбираем в отдельный список
Дальше - проще. Теперь банальной функцией ВПР (VLOOKUP) просто выведём все найденные названия (я добавил столбец с порядковыми номерами для удобства):
После этого можно поиграться, вводя в жёлтую ячейку G2 разные слова и фразы и понаблюдать за тем, как наши формулы отбирают только подходящие фильмы:
Шаг 3. Создаем именованный диапазон
Теперь создадим именованный диапазон, который будет ссылаться на отобранные фильмы. Для этого выбрем на вкладке Формулы команды Диспетчер имен - Создать (Formulas - Name Manager - Create) :
Имя диапазона может быть любым (например, Фильмы), а самое главное - это функция СМЕЩ (OFFSET) , которая и делает всю работу. Напомню её синтаксис, если вы подзабыли:
=СМЕЩ( начальная_ячейка ; сдвиг_вниз ; сдвиг_вправо ; высота ; ширина )
- В качестве начальной ячейки задаём первую ячейку списка отобранных элементов (E2).
- Сдвиги вниз и вправо у нас отсутствуют, т.е. равны нулю.
- Высота диапазона у нас соответствует максимальному значению индекса из столбца А.
- Ширина диапазона - 1 столбец.
Осталось сделать выпадающий список.
Шаг 4. Создаем выпадающий список
Выделим жёлтую ячейку (G2) и выберем на вкладке Данные команду Проверка данных (Data - Validation) . В открывшемся окне выбрем Список (List) в поле Тип данных (Allow) , а в качестве источника введем имя нашего созданного диапазона со знаком равно перед ним:
Вот и всё. Можно жать на ОК и наслаждаться результатом:
Для пущего удобства при вводе с клавиатуры можно использовать Ctrl + Enter вместо Enter после ввода текста (так активная ячейка не уходит вниз) и сочетание клавиш Alt + стрелка вниз , чтобы развернуть выпадающий список без мыши.
В принципе, можно было бы и не продолжать, но недавно Microsoft выкатила обновление вычислительного движка Excel, который теперь поддерживает динамические массивы и имеет специальные функции для работы с ними. Большинству пользователей они станут доступны в ближайшие месяцы, но даже если пока этих возможностей в вашем Excel нет - грех не показать как элементарно с их помощью решается наша задача.
Всё, что мы делали на Шагах 1-3 заменяется одной(!) формулой, где новая функция ФИЛЬТР (FILTER) отбирает из исходного диапазона A2:A251 только те фильмы, которые содержат заданную подстроку.
И всё. Никаких именованных диапазонов и медленных СМЕЩ, никаких танцев с дополнительными столбцами и формулами. Песня!
Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.
На вкладке Параметры в поле Тип данных выберите пункт Список.
Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе "Города" в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
Не знаете, какой параметр выбрать в поле Вид?
Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант Остановка.
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей.
Если список элементов для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов см. в статье Блокировка ячеек.
Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка.
Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.
Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.
Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.
Выделите ячейки, для которых нужно ограничить ввод данных.
На вкладке Данные в группе Инструменты нажмите кнопку Проверка данных или Проверить.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.
Щелкните поле Источник и выделите на листе список допустимых элементов.
Диалоговое окно свернется, чтобы было видно весь лист.
Нажмите клавишу ВВОД или кнопку Развернуть , чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.
Значения также можно ввести непосредственно в поле Источник через запятую.
Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле Источник.
См. также
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
На вкладке Параметры в поле Разрешить выберите пункт Список.
Если вы уже создали таблицу с элементами раскрывающегося списка, щелкните поле Источник и выделите ячейки, содержащие эти элементы. Однако не включайте в него ячейку заголовка. Добавьте только ячейки, которые должны отображаться в раскрывающемся списке. Список элементов также можно ввести непосредственно в поле Источник через запятую. Например:
Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей. Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка. Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Читайте также: