Надстройка для облегчения ввода значений в ячейку Excel
Автор: nerv
Last Update: 27/03/2012
Вам часто приходится заниматься заполнением электронных таблиц, долго и муторно выбирать варианты из выпадающих списков?
А, может, иметь дело с одними теми же, но не структурированными данными?
Раз так, то данная надстройка призвана облегчить Вам жизнь: сократить время, потраченное на нудную, однообразную работу, а вместе с тем повысить ее качество и эффективность.
Как это работает:
По нажатию Ctrl+Enter рядом с выделенной ячейкой появляется список, который позволяет не только выбирать, но и производить поиск по интересующим Вас данным.
Посмотрим, что он умеет:
- Не содержит повторов (уникальный). Легко выявить однотипные данные;
- Отсортирован по возрастанию. Возможность быстро найти то, что нужно;
- После вызова сразу готов к поиску/выбору из списка. Лишние движения ни к чему;
- Позволяет искать с использованием специальных подстановочных символов (*,?,~ и т.п.);
- Осуществлять быстрый поиск по "шаблону". Если ячейка, из которой был вызван список, содержит информацию, поиск будет произведен по ней;
- Появляется рядом с текущей/активной ячейкой и не "убегает" за пределы экрана;
- Навигация привычными стандартными клавишами: Up [Вверх], Down [Вниз], Page Up [На страницу Вверх ], Page Down [На страницу вниз];
- Корректная работа со всеми типами данных: строки, даты, числа;
- Обработка ошибок формул листа. Никаких пустых строк в списке;
- Обработка защиты ячеек листа. В защищенные ячейки ввод запрещен;
- Информация об общем количестве списка и найденных по запросу элементах;
- Быстрый вызов по нажатию Ctrl+Enter;
- Быстрое закрытие: клавиша Esc;
- Быстрый ввод клавишей Enter
Помимо всего вышеперечисленного, позволяет сэкономить на размере файла за счет формирования списка "на лету", который создается в разы быстрее, если данные упорядочены или частично упорядочены по возрастанию.
Отличия версии 1.6 от 1.5:
- новая, более мощная/быстрая процедура сортировки;
- переход после ввода на следующую ячейку (в зависимости от установок Excel);
Добавлены настройки:
- использования и формирования списка (подробнее во вложении "how to use");
- поиска с учетом регистра и без него;
- маски поиска;
- заголовков.
Комментарии
Автор этой надстройки - не я.
Тот, кто её написал, уже давно в комментах на сайте не отвечает, - а я не планирую поддерживать чужие решения.
Потому, комменты к этой статье я сейчас закрою.
На вопросы по этой надстройке больше отвечать некому.
Надстройка не поддерживается, не дорабатывается, - если хотите её использовать, то используйте в таком виде, в каком она сейчас есть.
Если нужно что-то аналогичное, - оформляйте заказ на сайте, сделаем «с нуля»
Уважаемый Игорь!
При работе у меня был сформирован список к примеру с 500 позиций в одном столбце, но уникальных элементов 100, возможно ли скопировать или экспортировать эти 100 уникальных элементов?
Спасибо за надстройку!!!
Люди добрые! Оч полезная надстройка! Но вот у меня одна проблема когда в двух соседних столбцах расположен "автопоиск" выдает ошибку (Unknown error). Допустим автопоиск настроен на ячейку А2 и Второй на В2. записан макрос на автозапуск
Sub va()
'
' va Макрос
'
'
Application.Run "nerv_DropDownList.DropDownListShow"
End Sub
и этот макрос запускается командой
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A2:A2000"), Target) Is Nothing Then
Application.Run "va"
End If
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("B2:B2000"), Target) Is Nothing Then
Application.Run "va"
End Sub
При переходе на ячейку А2 выходит окно поиска ввожу данные нажимаю enter, происходит переход на след ячейку B2 и выскакивает ошибка Unknown error!
Помогите советом что сделать !?
>Подскажите, как сделать так, чтобы можно было формировать запрос из другого файла
Тот же вопрос: можно ли в качестве "Шаблона Подстановки" использовать другую книгу?
Благодарю за шикарную надстройку.
ИНДЕКС() + ПОИСКПОЗ() Вам в помощь.
Саша, а зачем что-то привязывать...
Подключите файл как надстройку к Excel, - и он сам будет запускаться каждый раз вместе с Excel
Добрый день.
Макрос прекрасен!
Подскажите пожалуйста, как его можно привязать к personal.xls или рабочему файлу со списками, чтобы избежать запуска
Здравствуйте!
А сколько строк эта надстройка может обработать?
Можно ли сделать чтобы обрабатывала 200 000
Скажите, пожалуйста, как выбрать сразу несколько значений. Чтобы в одной ячейке было "Апельсин; Лайм". Это возможно?
Здравствуйте! Могли бы Вы обновить файл nerv_DropDownList_1.6.zip? Файл скачивается, запускается excel, но ничего не открывается и ошибки не выдает. Спасибо!
Подскажите, как сделать так, чтобы можно было формировать запрос из другого файла
Отличная штука, спасибо!
Есть небольшой глюк при использовании настройки - если на ячейки, куда автоподбором вставляются значения, установить проверку, то проверка не срабатывает когда значение вводится через окно надстройки.
>> /////и как обеспечить проверку вводимых данных (запрет на ввод данных не из списка).
>> Это легко можно организовать штатными методами екселя - /Данные/Проверка данных/список, указав предварительно созданный именованный динамический список из тогоже диапазона что и обсуждаемая надстройка.
Excel 2007 - не получается так задать, макрос обходит запрет
Супер. Спасибо Автору!
Сочетание Ctrl+Enter уже используется в EXCEL: "Выделите ячейки, даже несмежные, введите то, что Вам нужно и нажмите Ctrl + Enter. Такой подход работает с датами, строками, числами и даже формулами. Этот способ позволяет сэкономить время как при вводе новых данных, так и при замене уже существующих."
Добрый день. Хочу связаться с автором по поводу разработки такой же функциональности под Excel for Mac. Помогите, пожалуйста. (на оба почтовых ящика, указанных выше, отправлял письма, ответа не получил :( )
Надстройка просто супер но мне нужно чтобы работала она на excel 2003 а она не поддержуется 2003-м может кто-то посоветовать чтото подобное.
Макрос Работает! Хотел добавить что-бы ячейку с данными с лева захватывало и с права (например не просто название а "КОД" "НАЗВАНИЕ" "ЦЕНА", то есть макрос ищет товар по наименованию но при выборе нужно что бы подбирался код в ячейке левее.
Денег не просить но спрашивало пароль после удаления записей на странице "Ноутбуки" (уже не спрашивает)
Поковыряться понял что не получится )
Здравствуйте, Сергей.
Макрос работает? Работает
Денег с вас просит? Нет
с чего он вдруг платный-то стал?
а если хотите поковыряться в коде, - такой возможности нет
Зачем вам лазить в код надстройки?
Все, что вы описали, можно сделать макросами в вашем файле.
Отличная вещь, то что искал, я так понял макрос платный? Когда я хотел поуберать лишние надписи после сохранения, закрытия и открытия запросило пароль.
/////и как обеспечить проверку вводимых данных (запрет на ввод данных не из списка).
Это легко можно организовать штатными методами екселя - /Данные/Проверка данных/список, указав предварительно созданный именованный динамический список из тогоже диапазона что и обсуждаемая надстройка.
не могу понять как добавлять новые значения в список: только через лист DDLSettings? или все-таки можно как-то через форму поиска?
Спасибо за ответ, но через проверку изменений в столбце нельзя.
как сюда закинуть файл excel со всем содержимым? Для примера.
Дело в том что, ячейка с выбором регулярно сдвигается в столбце вниз, и проверка видит следующую измененную ячейку. Соответственно ставит новую дату по вашему примеру.
Это делается отдельным макросом (в вашем файле):
по событию изменения листа Worksheet_Change, проверяете номер столбца, в котором изменена ячейка,
и, если изменения произошли в том столбце, где выбор идет через выпадающий список, - то в ячейку справа заносите дату
при помощи строки кода
target.next = NowСпасибо, очень полезная надстройка.
Глобальная проблема для меня: как сделать чтобы после выбора из списка и нажатия Enter, в ячейке справа АВТОМАТОМ вводилась сегодняшняя дата???
Очень нужно!
к сожалению, в текущей реализации нельзя
А можно как-то изменить высоту списка, чтобы кол-во отображаемых строк в нем было больше? Спасибо.
Изменить ширину списка можно на листе натсроек
Очень удобно! Спасибо автору.
А возможно как-то изменить размер диалогового окна? сделать его больше?
Спасибо!
Неплохо было бы ввести возможность в выпадающем списке привязать еще одну колонку, связанную с поисковой (например, рядом с наименованием выводить цену из следующей колонки) и добавить в настроечный лист диапазон, который рядом выводить, а по наименованию оставлять без цены. Как в Access: там выпадающий список может содержать несколько колонок, а вносить в поле только одно. Тем более, что это реализуется не так сложно.
> Отличная надстройка. Спасибо автору. Если возможно изменить клавишу Быстрого ввода из клавиши Enter на клавишу Tab?
Пожалуйста. В данной реализации не возможно.
Отличная надстройка. Спасибо автору. Если возможно изменить клавишу Быстрого ввода из клавиши Enter на клавишу Tab?
> Добрый вечер, отличная настройка.
> Возник вопрос, можно ли доработать надстройку, что бы она работала как связанные выпадающие списки, выбор для ввода данных в конкретную ячейку, зависит от значения в другой ячейке?
Доброго времени суток
Можно, но у меня нет времени : ) Когда появится неизвестно...
Добрый вечер, отличная настройка.
Возник вопрос, можно ли доработать надстройку, что бы она работала как связанные выпадающие списки, выбор для ввода данных в конкретную ячейку, зависит от значения в другой ячейке?
Ну мы, вроде, сделали. Просто рекомендация к вашей реализации) Советую вам задуматься над объединением надстройки с файлом.
> Весьма неудобный функционал
никто не заставляет вас ей (надстройкой) пользоваться. Сделайте свою - удобную, хорошую и "без геморроя" )
----- примечание администратора сайта ---------
то же самое хотел написать, слово в слово, - но ждал, пока автор надстройки отзовётся)
Весьма неудобный функционал - макросы в отдельном файле оставляют желать лучшего. Зачем такой геморрой? Исправьте.
Вячеслав, ну написано же, как связаться...
Email автора надстройки указан в конце статьи
nerv, очень крутая надстройка!
У меня есть предложение, как с вами связаться?
Добрый день!
Это гениально. Особенно для тех, кто не очень смыслит в VBA)
Возник вопрос. Если я хочу передать свой файлик другому человеку для работы, мне также обязательно ему передавать и надстройку так?
> но могу предположить источник ошибки
скорее всего так и есть, спасибо
> Именно таким образом и было определено почему в тестовом файле всё отлично, а у меня хоть убей не работает)))
: )
>здраствуйте Nerv, подскажите пож-та, если этот механизм применить для связанного списка. что нужно сделать для того чтобы список выпадал только по критериям? спасибо
Здравствуйте, Ольга. В данной реализации скорее нет, чем да.
>Осталось одно - редактируемый лист и лист с базой сейчас должны быть в одной книге. Возможно ли разнести их по разным книгам?
в будущем, вероятно, да. В данной реализации, насколько помню, - нет.
Nerv, огромное спасибо за за Вашу работу. Надстройка прекрасно работает. Даже с базой более 8000 строк на стареньком 4-м Пентиуме. Без проблем заработали и именованные диапазоны и столбцы "умной" таблицы Excel 2010. Осталось одно - редактируемый лист и лист с базой сейчас должны быть в одной книге. Возможно ли разнести их по разным книгам? Задание имени листа в виде '[имя_книги]Лист1' не прошло.
здраствуйте Nerv, подскажите пож-та, если этот механизм применить для связанного списка. что нужно сделать для того чтобы список выпадал только по критериям? спасибо.
> В крайнем случае могу посоветовать переименовать лист
Именно таким образом и было определено почему в тестовом файле всё отлично, а у меня хоть убей не работает)))
> По идее, все должно быть хорошо
Nerv, я не смотрел твой код, но могу предположить источник ошибки.
Обычно, эта ошибка появляется,
если подставлять значение переменной без принудительного указания типа, в качестве параметра в функции Worksheet
Т.е. твой макрос ищет лист с ИНДЕКСОМ 2012, а не с НАЗВАНИЕМ 2012
(по умолчанию, функция worksheets ждёт параметр числового типа)
Например, если в текстбоксе написано 2012,
то в коде worksheets(textbox1) будет ссылаться на 2012-й лист,
а worksheets(cstr(textbox1)) - на лист с именем "2012"
> Возник небольшой вопрос. Если в имени листа "Где используется" использованы только цифры (у меня листы называются 2011, 2012 ... и т.д.), то макрос не работает. Или я что-то не так делаю?
сходу затрудняюсь ответить, т.к. писалось давно ) По идее, все должно быть хорошо. В крайнем случае могу посоветовать переименовать лист
> Сделайте, пожалуйста, возможность запуска макроса автоматически при открывании файла.
с какой целью? Что мешает нажать горячие клавиши? Если оно так необходимо, можете нажить их программно
>Ну так, что, я готов заплатить на яндекс-мани, залейте макрос в читабельном виде.
не имею желания иметь с вами дел. Сами знаете почему.
с какой целью? Что мешает нажать горячие клавиши? Если оно так необходимо, можете нажить их програмно
Спасибо огромное за надстройку. Возник небольшой вопрос. Если в имени листа "Где используется" использованы только цифры (у меня листы называются 2011, 2012 ... и т.д.), то макрос не работает. Или я что-то не так делаю?
Сделайте, пожалуйста, возможность запуска макроса автоматически при открывании файла.