Надстройка для облегчения ввода значений в ячейку 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");
- поиска с учетом регистра и без него;
- маски поиска;
- заголовков.
Комментарии
Ну так, что, я готов заплатить на яндекс-мани, залейте макрос в читабельном виде.
>(я делаю файл подбора оборудования, необходимо, чтоб все макросы были в одном файле)
вы можете положить файл с надстройкой на сетевой диск и подключить ее оттуда
> Подскажите пож, как увеличить размер шрифта в окне выбора параметров (в настройку нельзя вынести??)
можно. Возможно, будет реализовано в следующих версиях.
> и как обеспечить проверку вводимых данных (запрет на ввод данных не из списка).
Возможно, будет реализовано в следующих версиях.
>вы можете пользоваться им бесплатно : )
зачем тогда сделали макрос невидимым?
(я делаю файл подбора оборудования, необходимо, чтоб все макросы были в одном файле)
> могу ли я купить этот макрос?
вы можете пользоваться им бесплатно : )
могу ли я купить этот макрос?
в принципе назначить можно любую клавишу предварительно установив надстройку. Для этого необходимо записать новый макрос с нужными клавишами и в код макроса вставить следующий код :
Sub клавиши()
' клавиши Макрос
Range("C3").Select
Application.Run "nerv_DropDownList.DropDownListShow"
End Sub
Если вы хотите что бы поле появлялось при выделении ячейки тогда вставьте в нужный лист исходный код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("C3"), Target) Is Nothing Then
Application.Run "клавиши"
End If
End Sub
в результате на том листе где вы вставили код при нажатие на ячейку "С3" ,будет вызываться надстройка
Подскажите пож, как увеличить размер шрифта в окне выбора параметров (в настройку нельзя вынести??)
и как обеспечить проверку вводимых данных (запрет на ввод данных не из списка).
Спасибо
И хорошо бы добавить возможность регулировки высоты (числа записей) отображаемых в выпадающем списке..
Есть ли возможность встроить надстройку в книгу (интегрировать макрос в файл)? И будет ли работать макрос в 2003ем excel'e?
Отлично! Прошу простить за невнимательное прочтение инструкции. Нашел, поправил, все работает. Но..) Максимальную ширину, которую не сбрасывает на 210 удалось установить только 605.. А в списке остались значения, которые увы даже при такой ширине отображаются только на половину.. Хорошо бы добавить возможность настройки шрифта (тип, размер, жирность). Какой нибудь нежирный ArialNarrov для моего случая наверное был бы самое то)
Здравствуйте. Спасибо. Ширину списка можно изменить в настройках на "листе настроек".
Идея супер, реализация отличная. Все наглядно просто и удобно! Чуть подсовременнить дизайн.. Об этом позже. И заплатить готов! Но, есть одно критически важное для моего случая неудобство. Которое мне нужно очень срочно устранить.. Выбор из списка длинных наименований (около 200 символов) как бы туда добавить авто настройку по ширине? Или залезть в макрос и там просто растянуть форму?
Еще раз спасибо нашел там было 210 но хочу попросить чтобы ты продолжил и улучшил настройку с добавлением в нее функцию охвата смежных трех столбцов
здравствуй nerv я очень благодарен тебе с твоей настройкой очень удобно работать наверно ты помог многим опять же извиняюсь но не могу найти лист "настройки"
см. лист "настройки"
Консолидируете с помощью формул данные в одном столбце это можно тогда как увеличить ширину вашей формы
Консолидируете с помощью формул данные в одном столбце - бесплатно
хочу заказать эту же надстройку только работываюшей с выбором данных по трем столбцам и сколько это будет стоить
> а по первому важно, чтобы данные брались с нескольких диапазонов подстановки
сведите к одному с помощью формул
> и новый вопрос: отбирая уникальные значения, фильтр пропускает ряд значений по непонятному алгоритму, могу прислать образец книги
книгу не надо. Приведите фрагмент данных и опишите, что на Ваш взгляд является ошибочным
спасибо, по второму вопросу: действительно срабатывает
а по первому важно, чтобы данные брались с нескольких диапазонов подстановки
и новый вопрос: отбирая уникальные значения, фильтр пропускает ряд значений по непонятному алгоритму, могу прислать образец книги
Само починилось (увидело больше). Что произошло так и не понял.
Было бы неплохо не надстройку, а макрос в файл, т.к. файл в сети и народу много юзает.
А так - спасибо, хорошая идея и реализация.
значит, Вы что-то делаете не так
Не видит списка более 100
Я разобрался. Новые листы, с которых собирается информация, должны вставляться вначале списка (слева). А новый лист, на котором нужно получить выпадающий список, за листом "Ноутбук" (справа). Тогда все работает.
На данный момент реализация надстройки такова, что изменить сочетание клавиш можно только путем изменения их в коде программы.
Вы написали, что можно "сменить комбинацию клавиш для вызова программы (по нажатию Enter, или по двойному щелчку)" каким образом это можно осуществить??
>Почему данные собираются только с одного листа, хотя я внёс в столбцы листа DDLSettings ряд значений для шаблона подстановки?
точно не помню, но скорее всего данные берутся с первого диапазона подстановки
>Почему при совпадении листа шаблона и где используется, программа не срабатывает?
срабатывает
Добрый вечер
Почему данные собираются только с одного листа, хотя я внёс в столбцы листа DDLSettings ряд значений для шаблона подстановки? Ведь очевидно, там предусмотрены несколько строк.
Почему при совпадении листа шаблона и где используется, программа не срабатывает?
:-)
>Не подскажете, как именно можно изменить этот пример под задачу облегчения ввода значений только из заранее заданного списка (несколько тысяч значений)?
смотрите пример внимательней : )
>Если пользователь отказывается искать по предлагаемому списку, то введенное с клавиатуры значение должно удаляться.
Надо править в коде. Если очень нужно, пишите мне на мыло (но будет не бесплатно)
>Еще один вопрос сводится к возможности категорирования списка значений. Скажем, если список состоит из 1000 значений, разделенных на 10 групп по 100 и рядом с каждым значением можно выводить номер группы.
В данной реализации это затруднительно. Можете "повесить" на определенные ячейки нужны Вам списки.
Огромное спасибо за содержательный пример!
Не подскажете, как именно можно изменить этот пример под задачу облегчения ввода значений только из заранее заданного списка (несколько тысяч значений)?
То-есть то, что ввел пользователь в ячейку не должно быть сохранено, а по нажатию Enter должно открываться окно из примера с подставленной строкой поиска. Если пользователь отказывается искать по предлагаемому списку, то введенное с клавиатуры значение должно удаляться.
Осноная цель - автоматический подсчет введенных значений в сводной таблице. Если пользователь сможет вводить свои произвольные значения, их невозможно будет подсчитать в дальнейшем, но облегчить ему ввод как-то хочется.
Еще один вопрос сводится к возможности категорирования списка значений. Скажем, если список состоит из 1000 значений, разделенных на 10 групп по 100 и рядом с каждым значением можно выводить номер группы.
Еще раз спасибо за пример!
Леонид, диапазонов сколько угодно, их имена латиницей.
Спасибо и за текущий вариант надстройки.
Вопрос: попытался указать несколько диапазонов - пишет ошибку.
Т.е. можно указывать только 1 диаппазон?
Действительно, было бы очень интересно.
Нет уж буду пользоваться версией 1.5, с DDLSettings слишком мудрено, что его в каждую книгу пихать надо где хочешь воспользоваться надстройкой? Ради интереса решил выставить свои настройки в DDLSettings ничего не вышло (видимо идею не понял). был бы признателен за версию 1.5 со всеми улучшениями версии 1.6 но без использования вкладки DDLSettings. Спасибо!
Доступна новая версия надстройки. Отличия от предыдущей смотрим в статье.
Спасибо за надстройку. Пару пожеланий на будущее: Заголовок на форме у меня в виде крякозябров (MSO 2003 Eng, регион.уст: EST) и менять нельзя. Может быть Unicode для видимого текста? Вывод/выбор даты происходит в америк.формате (2/18/2012) даже если Cell.NumberFormat="dd.mm.yyyy". Пока всё. Ждём версию 1.6 :)
Огромное спасибо Вам за облегчение рабочего процесса! )
Спасибо, и Вас!)
>или работал быстрее с такой таблицеей (44000 заполненых строк) - сильный вариант.
Можно осуществить, но не бесплатно. Думаю, этот объем (разумеется, длину строк я не знаю), будет обрабатывать ~ за 0,3-0,7 сек.
Напишите мне на мыло, оно указано в статье. Только сейчас я заняться Вами не смогу, сами понимаете Новый Год)
Так же хочется поздравить уважаемого администратора сайта(EF) с Новым Годом : )
С наступающим Вас.
чудесная идея.
но как можно сделать макрос более быстрым?
у меня табличка с 44000 строк.
(сам файл весит 14 мегабайтов)
так поиск увидеть не уалось - ехель завис на долго...
можно как нибудь указать чтоб макрос просматривал последнии 1000 строк - 2000 строк (слабый вариант)
или работал быстрее с такой таблицеей (44000 заполненых строк) - сильный вариант.
возможно следует сделать сохранение и поплнения индекса (сохранить в файл)?
если это можно сделать только на комерческой основе сколько это может стоить?
Николай, это тоже "Поле со списком", только расположенное на форме. К томе же зачем так делать, не совсем понятно. Или вы планируете создать в каждой ячейке Excel такое поле со списком?)
Как вариант, могу предложить вызывать выпадающий список после частичного ввода данных, например: ввели в ячейку "ап", нажали Ctrl+Enter и поиск будет произведен по этому "ап".
В Excel есть такой элемент управления как поле со списком (ActiveX). И там есть быстрый поиск, но не такой продвинутый, как сделали вы. Можно ли реализовать ваш вариант в этом "поле со списком" ? Там поиск как раз начинается сразу при вводе данных...
Увы, так не получится.
Как только вы начали вводить данные в ячейку (ячейка перешла в режим редактирования), Excel перестаёт реагировать на внешние раздражители, и все макросы приостанавливаются.
Что можно сделать:
1) сменить комбинацию клавиш для вызова программы (по нажатию Enter, или по двойному щелчку)
2) запускать макрос после ввода данных в ячейку (макрос сам будет запускаться ПОСЛЕ изменения ячейки)
т.е. вы ввели в ячейку пару букв, нажали Enter для завершения ввода, - и автоматом запустился макрос, используя введённые буквы в качестве фильтра.
А возможно ли чтобы эта надстройка работала без Ctrl+Enter - а просто при вводе данных в ячейку ?