Как сделать всплывающую подсказку в excel
Проверка данных Excel
Во время работы с большими объемами информации, особенно когда ее обработка осуществляется формулами или макросами, важно, чтобы данные не имели ошибок, которые способны повлиять на итог. Сложности в получении корректных исходников появляются, когда они поступают из неструктурированных источников (плохо спроектированные базы данных, ручное заполнение таблиц и форм незаинтересованными в результате людьми). Особенно трудно добиться правильного оформления по определенной маске (шаблону). Например, дату в виде строки можно записать несколькими способами:
- 01.01.2001;
- 01/01/2001;
- 1 января 2001 года и т.д.
В статье описывается функционал, позволяющий упростить процесс сбора и проверки данных в Excel.
Где находится?
Для настройки параметров проверки вводимых значений необходимо на вкладке «Данные» в области «Работа с данными» кликнуть по иконке «Проверка данных» либо выбрать аналогичный пункт из раскрывающегося меню:
На экране появиться окно с настройками по умолчанию, где в качестве типа данных может быть использовано любое значение:
Настройка условия проверки
Изначально требуется выбрать тип проверяемых данных, что будет являться первым условием. Всего предоставлено 8 вариантов:
- Целое число;
- Действительное число;
- Список;
- Дата;
- Время;
- Длина текста;
- Другой.
В соответствии с выбранным пунктом, появляется возможность выбора дополнительных условий. Для чисел, дат, времени и длины текста возможно указать ограничения: меньше, больше, диапазон и т.п. К списку обязательно привязывается источник, а пункт «другой» предлагает ввести формулу, возвращающую логическое значение.
Самым необычным видом является выпадающий список.
Он позволяет выбирать значения, указанные в качестве источника и ограничивать допустимые значения им же. Также возможно создавать динамические выпадающие списки.
Всплывающая подсказка ячейки Excel
Функционал проверки данных в Excel позволяет настраивать всплывающие подсказки для ячеек листа. Для этого следует перейти на вторую вкладку окна проверки вводимых значений – «Сообщение для ввода».
На изображении показан пример возможного сообщения для ячейки, в которую вносятся целые числа от 1 до 31 (настройки вкладки «Параметры»). Заголовок и сообщения указываются по Вашему усмотрению, каких-либо правил к их оформлению нет. Не забудьте установить галочку на поле «Отображать подсказку, если ячейка является текущей», иначе сообщение будет деактивировано.
Пример всплывающей подсказки в Excel:
Вывод сообщения об ошибке
Последняя вкладка окна проверки данных позволяет настроить поведение и вывод сообщений при обнаружении ошибочного значения.
Существует три варианта сообщений, отличающихся по поведению:
Останов является сообщением об ошибке и позволяет произвести только 2 действия: отменить ввод и повторить ввод. В случае отмены новое значение будет изменено на предыдущее. Повтор ввода дает возможность скорректировать новое значение.
Предупреждение более лояльно в сравнении с остановом, так как позволяет оставлять значение, не соответствующее условиям проверки, после подтверждения ввода пользователем.
Сообщение выводить ошибку в виде простой информации и дает возможность отменить последнее действие.
Заголовок и сообщение заполняются по Вашему желанию.
Пример вывода одной и той же ошибки, но под разными видами:
Всплывающая подсказка в ячейке
Сегодня хочу поделиться одним маленьким приемом, который поможет сделать удобные и красивые подсказки к ячейкам:
На создание такой подсказки меня навел однажды вопрос на одном из форумов — как можно сделать удобный перевод текста из ячейки, чтобы он показывался при наведении на ячейку мышью? Не выделении — именно наведении. Можно использовать примечания(вкладка Рецензирование -Создать примечание). Но в случае с примечаниями есть несколько не очень удобных моментов: размеры примечания придется подгонять для каждой подсказки отдельно; примечания как правило будут показываться справа от ячейки, а не чуть ниже; при большом количестве примечаний файл может значительно увеличиться в размерах и некоторые возможности будут мешать их корректному отображению(например, если закрепить области, то примечание может и съехать и «срезаться»). Первое, что пришло в голову — а что если сделать как в гиперссылках (Что такое гиперссылка?)? Если точнее — что если именно эти подсказки использовать? Но как же тогда делать гиперссылку? Куда? Все просто — я решил, что можно создать гиперссылку на ячейку, в которой сама гиперссылка. Тогда даже при нажатии на ячейку с гиперссылкой нас никуда не перекинет. Я покажу как это можно сделать вручную и как это можно сделать при помощи кода VBA.
Для начала немного об исходных данных. У нас есть лист с ячейками, в которых надо создать подсказки и есть лист «справочник», в котором указано для каких значений какие должны быть подсказки.
РУЧНОЕ СОЗДАНИЕ ПОДСКАЗКИ
Выделяем ячейку, подсказку для которой хотим создать -правый клик мыши -Гиперссылка (Hyperlink). Откроется окно добавления гиперссылки.
Выбираем местом в документе
Введите адрес ячейки — указываем адрес той ячейки, в которой хотим создать подсказку
Или выберите место в документе — указываем тот лист, на котором наша ячейка, в которой создаем подсказку.
Далее жмем кнопку Подсказка и в появившемся окне вписываем текст всплывающей подсказки -Ок. Так же в основном окне жмем Ок. Подсказка создана.
Ниже небольшой ролик, в котором показано как это все делается пошагово:
В этом способе все хорошо, кроме двух вещей:
- Если подсказок много, но создавать их весьма непростое занятие. Необходимость листа «справочник» при ручном методе так же ставится под сомнение
- При создании гиперссылки формат ячейки автоматом изменяется. А для нашей цели это в большинстве случаев не надо. Это можно обойти, создавая подсказки вторым способом — при помощи кодов VBA.
СОЗДАНИЕ ПОДСКАЗКИ АВТОМАТИЧЕСКИ КОДОМ VBA
Здесь особо рассказывать нечего — лучше сначала скачать файл, приложенный к статье. Там есть лист «справочник», в котором забита пара значений для подсказок и лист, в котором подсказки создаются.
Как это работает. Выделяются ячейки для создания подсказок(в примере это D15:D16 ). И кнопкой запускается код, который в выделенных ячейках создаст всплывающие подсказки. Сам принцип: код просматривает каждую выделенную ячейку и если она не пустая запоминает её значение. Ищет это значение в первом столбце листа «справочник» и если находит — создает гиперссылку и значение из второго столбца листа «справочник»(в строке с найденным значением) вставляет в качестве подсказки к гиперссылке.
Но помимо этого код перед созданием гиперссылки запоминает большую часть форматирования текста в ячейке и после создания гиперссылки возвращает его. Хотя и здесь не без ложки дегтя — код не сможет корректно обработать и вернуть смешанное форматирование (например, разный цвет шрифта в одной ячейке, разный стиль и т.п.). Но подсказки при этом все равно будут созданы.
Что следует знать: код ищет каждое значение ячейки полностью, а не каждое слово отдельно. Это значит, что если в ячейке будет записано «аннуитентный платеж», а не просто «аннуитентный» как в примере — то подсказка для такой ячейки не будет создана.
Если добавить или изменить значения в листе «справочник», то для того, чтобы подсказки обновились необходимо запустить код заново. Внесение изменений в сам код при этом не требуется.
Созданные подсказки останутся в файле даже если удалить сам код. Т.е. если создать подсказки в файле, а затем файл переслать другому человеку — он сможет использовать подсказки без дополнительных действий.
Всплывающая подсказка в ячейке (59,0 KiB, 1 962 скачиваний)
Если кому-то лень качать сам пример — привожу так же код: