Источник списка должен быть списком с разделителями или ссылкой на одну – Связанные выпадающие списки “Источник ассортимента должен быть списком с разделителями или ссылкой на одну строку или столбец”

Содержание

Как обойти ограничение Excel и сделать выпадающий список зависимым

Недавно дочь обратилась с вопросом, нельзя ли в Excel выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, находящейся слева от ячейки со списком (рис. 1)? Я довольно давно не использовал в работе выпадающие списки, поэтому для начала решил освежить свои знания по вопросу проверки данных в Excel.

Рис. 1. Состав выпадающего списка зависит от содержания соседней ячейки

Скачать заметку в формате Word или pdf, примеры в формате Excel2007

Команда Проверка данных находится на вкладке Данные, область Работа с данными.

Примечание. Иногда команда Проверка данных может быть недоступна:

  • Возможно, в настоящее время вводятся данные. Во время ввода данных в ячейку команда Проверка данных недоступна. Чтобы завершить ввод данных, нажмите клавишу ВВОД или ESC.
  • Возможно, лист защищен или является общим. Если лист защищен или является общим, изменить параметры проверки данных невозможно. Снимите защиту или отмените режим «общий».
  • Возможно, таблица Excel связана с узлом SharePoint. Невозможно добавить проверку данных в таблицу Excel, которая связана с узлом SharePoint. Чтобы добавить проверку данных, необходимо удалить связь таблицы Excel или преобразовать ее в диапазон.

К сожалению, Excel в своем стандарте позволяет делать списки только на основе:

  • имени массива
  • диапазона ячеек
  • прямого перечисления элементов списка (рис. 2).

Примечание. Элементы списка вводите через стандартный разделитель элементов списка Microsoft Windows (в русском Excel по умолчанию это точка с запятой).

Рис. 2. Возможные источники списка: вверху – имя массива; посередине – диапазон ячеек; внизу – элементы списка

Попытка ввести формулу в поле Источник диалогового окна Проверка вводимых значений заканчивается неудачей (рис. 3). Видно, что Excel не воспринял значение ячейки D2 ("цвет"), как имя массива, и просто включил это значение в качестве единственного элемента списка.

Рис. 3. Недопустимый источник списка – формула

Примечания. Ширина раскрывающегося списка определяется шириной ячейки, для которой применяется проверка данных. Ширину ячейки можно настроить так, чтобы не обрезать допустимые записи, ширина которых больше ширины раскрывающегося списка.

Убедитесь, что установлен флажок Список допустимых значений. В противном случае рядом с ячейкой не будет отображена стрелка раскрывающегося списка. Хотя ограничение на ввод значений в ячейку работать будет.

Чтобы указать, как обрабатывать пустые (нулевые) значения, установите или снимите флажок Игнорировать пустые ячейки. При включенном флажке ячейку можно будет оставить пустой.

Если допустимые значения заданы именем диапазона ячеек, среди которых имеется пустая ячейка, установка флажка Игнорировать пустые ячейки позволит вводить в проверяемую ячейку любые значения.

После изменения процедуры проверки одной ячейки можно автоматически применить эти изменения ко всем остальным ячейкам, имеющим такие же параметры. Для этого откройте диалоговое окно Проверка данных и на вкладке Параметры установите флажок Распространить изменения на другие ячейки с тем же условием.

И всё же в Excel есть одна функция непрямого действия. На английском языке у нее говорящее название – INDIRECT. На русском – название функции ни о чем – ДВССЫЛ… В чем же заключается непрямое действие? В отличие от других функций Excel, ДВССЫЛ возвращает не значение, хранящееся в ячейке, а ссылку, хранящуюся в ячейке. Непонятно? Сам «продирался» через это с трудом 🙂 Попробую пояснить. Вот что написано в справке Excel: ДВССЫЛ – возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого (рис. 4).

Рис. 4. Как работает функция ДВССЫЛ: вверху – формулы, внизу – значения

Понимаю, что если вы впервые столкнулись с функцией ДВССЫЛ, то разобраться сложно. Пробуйте, экспериментируйте, и понимание со временем придет.

Итак, еще раз, ДВССЫЛ возвращает ссылку, а не значение, хранящееся в ячейке. Ссылка немедленно вычисляется, и выводится ее значение (или содержимое). Именно это свойство позволит нам ввести непрямую ссылку на соседнюю ячейку так, что вернется не значение, хранящееся в этой соседней ячейке (как на рис. 3), а ссылка, хранящаяся в ячейке, эта ссылка тут же вычисляется, и получается имя массива (рис. 5).

Рис. 5. Формирование списка, зависящего от значения в левой ячейке

Примечание. Ссылка в формуле =ДВССЫЛ(D2) должна быть относительной (D2), а не абсолютной ($D$2). Подробнее об этом см. раздел Тип ссылок на ячейки в формулах для проверки данных заметки Excel. Проверка данных.

Если настоящая заметка не подсказала вам путь к решению вашей проблемы, рекомендую почитать Связанные (зависимые) выпадающие списки (обратите также внимание на многочисленные комментарии к ней).

baguzin.ru

Создаем выпадающий список в Excel – Офисное пространство

Вам наверняка не раз приходилось заполнять таблицы с выпадающими списками. Но самостоятельно создать такой список в первый раз будет проблемой. Просто потому, что вы в жизни не догадаетесь, где эту функцию искать.

Итак, я создала табличку-заготовку, на той же странице вывожу диапазон данных (потом это можно скрыть).

Теперь захожу на вкладку Данные, и выбираю Проверка данных.

 

В открывшемся диалоговом окне выбираю Список, и ввожу диапазон значений.

Жму на Ок, и теперь в выделенной ячейке появился выпадающий список.

Готово! Вы создали свой первый выпадающий список в Excel!

blogs.technet.microsoft.com

Создание раскрывающихся списков и управление ими

Работу с листом можно сделать более эффективной с помощью раскрывающихся списков. Пользователю вашего листа достаточно будет щелкнуть стрелку и выбрать запись в списке. Пройдите этот курс, чтобы узнать больше о том, как использовать раскрывающиеся списки и управлять ими.

Содержание курса

Раскрывающиеся списки (1:01)
Вводить данные проще и быстрее, если набор допустимых записей ограничен с помощью раскрывающегося списка. В этом случае при выделении ячейки появляется направленная вниз стрелка списка, которую можно щелкнуть, чтобы выбрать один из элементов.

Элементы раскрывающегося списка

(4:37)
Чтобы добавить элементы в раскрывающийся список, можно использовать список с разделителями-запятыми, а также обычный или именованный диапазон ячеек. В этом видео показано, как использовать обычные и именованные диапазоны.

Подсказки при вводе и сообщения об ошибках (2:47)
Чтобы помочь пользователям выбрать элемент раскрывающегося списка или просто сообщить, что ячейка содержит раскрывающийся список, можно добавить подсказку, которая будет отображаться при выделении ячейки. Кроме того, можно указать сообщение об ошибке, отображаемое при неправильном выборе.

Управление раскрывающимися списками (6:20)


Чтобы предотвратить изменение данных раскрывающегося списка, можно скрыть столбцы, строки или весь лист с данными. При этом их легко отобразить снова, если вам потребуется что-нибудь исправить. Кроме того, можно заблокировать и защитить паролем отдельные ячейки или даже весь лист.

Сводка курса
Краткое описание ключевых моментов этого курса.

Дополнительные курсы см. на сайте Обучение работе с Microsoft Office.

support.office.com

Выпадающий список в Excel с помощью инструментов или макросов

Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.

Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.

Создание раскрывающегося списка

Путь: меню «Данные» – инструмент «Проверка данных» – вкладка «Параметры». Тип данных – «Список».

Ввести значения, из которых будет складываться выпадающий список, можно разными способами:

  1. Вручную через «точку-с-запятой» в поле «Источник».
  2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
  3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.



Выпадающий список в Excel с подстановкой данных

Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.

  1. Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
  2. Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
  3. Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.

  1. Сформируем именованный диапазон. Путь: «Формулы» – «Диспетчер имен» – «Создать». Вводим уникальное название диапазона – ОК.
  2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
  3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
  4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
  5. Private Sub Worksheet_Change(ByVal Target As Range)
     
    Dim lReply As Long
     
        If Target.Cells.Count > 1 Then Exit Sub
        If Target.Address = "$C$2" Then
         If IsEmpty(Target) Then Exit Sub
           If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then
              lReply = MsgBox("Добавить введенное имя " & _
                             Target & " в выпадающий список?", vbYesNo + vbQuestion)
              If lReply = vbYes Then
                  Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target
              End If
           End If
         End If
    End Sub
     
  6. Сохраняем, установив тип файла «с поддержкой макросов».
  7. Переходим на лист со списком. Вкладка «Разработчик» – «Код» – «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

  1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
  2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.

  1. Создадим первый выпадающий список, куда войдут названия диапазонов.
  2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
  3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.
  4. Выбор нескольких значений из выпадающего списка Excel

    Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.

    1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
    2. Private Sub Worksheet_Change(ByVal Target As Range)
          On Error Resume Next
          If Not Intersect(Target, Range("Е2:Е9")) Is Nothing And Target.Cells.Count = 1 Then
              Application.EnableEvents = False
              If Len(Target.Offset(0, 1)) = 0 Then
                  Target.Offset(0, 1) = Target
              Else
                  Target.End(xlToRight).Offset(0, 1) = Target
              End If
              Target.ClearContents
              Application.EnableEvents = True
          End If
      End Sub
       
    3. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
    4. Private Sub Worksheet_Change(ByVal Target As Range)
          On Error Resume Next
          If Not Intersect(Target, Range("Н2:К2")) Is Nothing And Target.Cells.Count = 1 Then
              Application.EnableEvents = False
              If Len(Target.Offset(1, 0)) = 0 Then
                  Target.Offset(1, 0) = Target
              Else
                  Target.End(xlDown).Offset(1, 0) = Target
              End If
              Target.ClearContents
              Application.EnableEvents = True
          End If
      End Sub
       
    5. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.

    6. Private Sub Worksheet_Change(ByVal Target As Range)
          On Error Resume Next
          If Not Intersect(Target, Range("C2:C5")) Is Nothing And Target.Cells.Count = 1 Then
              Application.EnableEvents = False
              newVal = Target
              Application.Undo
              oldval = Target
              If Len(oldval) <> 0 And oldval <> newVal Then
                  Target = Target & "," & newVal
              Else
                  Target = newVal
              End If
              If Len(newVal) = 0 Then Target.ClearContents
              Application.EnableEvents = True
          End If
      End Sub

    Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

    Выпадающий список с поиском

    1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
    2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
    3. Жмем «Свойства» – открывается перечень настроек.
    4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

    Скачать пример выпадающего списка

    При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.

exceltable.com

Как в экселе сделать выпадающий список

Для таблиц, которые используют постоянные и повторяющиеся данные (например фамилии сотрудников, номенклатура товара или процент скидки для клиента) чтобы не держать в голове и не ошибиться при наборе, существует возможность один раз создать стандартный список и при подстановке данных делать выборку из него. Данная статья позволит вам использовать 4 разных способа как в экселе сделать выпадающий список.

Способ 1 — горячие клавиши и раскрывающийся список в excel

Данный способ использования выпадающего списка по сути не является инструментом таблицы, который надо как либо настраивать или заполнять. Это встроенная функция (горячие клавиши) которая работает всегда. При заполнении какого либо столбца, вы можете нажать правой кнопкой мыши на пустой ячейке и в выпадающем списке выбрать пункт меню «Выбрать из раскрывающегося списка».

Этот же пункт меню можно запустить сочетанием клавиш Alt+»Стрелка вниз» и программа автоматически предложит в выпадающем списке значения ячеек, которые вы ранее заполняли данными. На изображении ниже программа предложила 4 варианта заполнения (дублирующиеся данные Excel не показывает). Единственное условие работы данного инструмента — это между ячейкой, в которую вы вводите данные из списка и самим списком не должно быть пустых ячеек.

Использование горячих клавиш для раскрытия выпадающего списка данных

При чем список для заполнения таким способом работает как в ячейке снизу, так и в ячейке сверху. Для верхней ячейки программа возьмет содержание списка из нижних значений. И опять же не должно быть пустой ячейки между данными и ячейкой для ввода.

Выпадающий список может работать и в верхней части с данными, которые ниже ячейки

Способ 2 — самый удобный, простой и наиболее гибкий

Данный способ предполагает создание отдельных данных для списка. При чем данные могут находится как на листе с таблицей, так и на другом листе файла Excel.

  1. Сперва необходимо создать список данных, который будет  источником данных для подстановки в выпадающий список в excel. Выделите данные и нажмите правой кнопкой мыши. В выпадающем списке выберите пункт «Присвоить имя…».

    Создание набора данных для списка

  2. В окне «Создание имени» задайте имя для вашего списка (это имя дальше будет использоваться в формуле подстановки). Имя должно быть без пробелов и начинаться с буквы.

    Введите имя для набора данных

  3. Выделите ячейки (можно сразу несколько ячеек), в которых планируется создать выпадающий список. Во вкладке «ДАННЫЕ» вверху документа нажмите на «Проверка данных».

    Создать выпадающий список можно сразу для нескольких ячеек

  4. В окне проверка вводимых значение в качестве типа данных задайте «Список». В строке «Источник:» введите знак равно и имя для ранее созданного списка. Данная формула позволит ввести значения только из списка, т.е. произведет проверку введенного значения и предложит варианты. Эти варианты и будут выпадающим списком.

Для создания проверки вводимых значений введите имя ранее созданного списка

При попытке ввести значение, которого нет в заданном списке, эксель выдаст ошибку.

Кроме списка можно вводить данные вручную. Если введенные данные не совпадут с одним из данных — программа выдаст ошибку

А при нажатии на кнопку выпадающего списка в ячейке вы увидите перечень значений из созданного ранее.

Способ 3 — как в excel сделать выпадающий список с использованием ActiveX

Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:

  1. Нажмите на «Файл» в левом верхнем углу приложения.
  2. Выберите пункт «Параметры» и нажмите на него.
  3. В окне настройки параметров Excel во вкладке «Настроить ленту» поставьте галочку напротив вкладки «Разработчик».

Включение вкладки «РАЗРАБОТЧИК»

Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.

Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.

Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).

С открытым окном свойств нажмите на ранее созданный элемент «Поле со списком». В списке свойств очень много параметров для настройки и вы сможете изучив их, настроить очень много начиная от отображения списка до специальных свойств данного объекта.

Но нас на этапе создания интересуют только три основных:

  1. ListFillRange — указывает диапазон ячеек, из которых будут браться значения для выпадающего списка. В моем примере я указал два столбца (A2:B7 — дальше покажу как это использовать). Если необходимо только одни значения указывается A2:A7.
  2. ListRows — количество данных в выпадающем списке. Элемент ActiveX отличается от первого способа тем, что можно указать большое количество данных.
  3. ColumnCount — указывает сколько столбцов данных указывать в выпадающем списке.

В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:

Как видите получился выпадающий список в excel с подстановкой данных из второго столбца с данными «Поставщик».

Поделиться “3 способа как в экселе сделать выпадающий список”

sprosivideo.com

Excel. Проверка данных

Недавно дочь обратилась с вопросом, нельзя ли в Excel выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, находящейся слева от ячейки со списком? Я довольно давно не использовал в работе выпадающие списки, поэтому для начала решил освежить свои знания по вопросу проверки данных в Excel. Собственно, ответ на вопрос дочери см. Как обойти ограничение Excel, и сделать выпадающий список зависимым.

Средство проверки данных

Excel позволяет задать определенные правила, по которым будет определяться, какие данные могут содержаться в ячейке. [1] Например, необходимо, чтобы число, содержащееся в ячейке, принадлежало диапазону от 1 до 12. В случае если пользователь введет неправильное значение, программа выведет соответствующее сообщение (рис. 1).

Рис. 1. Вывод сообщения о неправильном вводе данных

Скачать заметку в формате Word или pdf, примеры в формате Excel2007

Excel позволяет легко создавать критерии для проверки данных, в том числе с использованием формул для создания более сложных критериев.

Средство проверки вводимых данных имеет принципиальный недостаток — если пользователь скопирует ячейку, а потом вставит ее в ячейку, в которой производится проверка вводимых данных, то все определенные для проверки формулы исчезнут.

Определение критерия проверки

Для определения типа данных, которые могут содержаться в ячейке или диапазоне ячеек, необходимо выполнить следующие действия.

1. Выделите ячейку или диапазон ячеек.

2. Выберите вкладку Данные, область Работа с даннымиПроверка данных. Excel отобразит диалоговое окно Проверка вводимых значений.

3. Щелкните на вкладке Параметры (рис. 2).

Рис. 2. Вкладка Параметры диалогового окна Проверка вводимых значений

4. Выберите одну из опций из раскрывающегося списка Тип данных. В зависимости от выбранной опции может измениться внешний вид вкладки Параметры путем добавления или удаления дополнительных элементов управления (для определения формулы нужно выбрать опцию Другой).

5. С помощью имеющихся на этой вкладке элементов управления задайте критерий проверки данных. Доступные элементы управления зависят от выбора, сделанного на предыдущем шаге.

6. (Опционально) Щелкните на вкладке Сообщение для ввода и введите сообщение, которое должно появиться на экране при выделении указанной ячейки. Это сообщение выводится для того, чтобы сообщить пользователю, какие данные можно вводить. Если пропустить этот шаг, то при выделении ячейки не появится никакого сообщения.

7. (Опционально) Щелкните на вкладке Сообщение об ошибке и введите сообщение, которое должно появляться в случае, когда пользователь введет недопустимое значение. Выбор вида сообщения в списке Вид определит, какой выбор будет у пользователя при внесении неверных данных. Для предотвращения ввода неправильных значений нужно выбрать вид сообщения Останов. Если пропустить этот шаг, то при возникновении ошибки будет появляться стандартное сообщение об ошибке.

8. Щелкните ОК.

После выполнения этих действий в выделенной ячейке или диапазоне будет производиться проверка вводимых данных.

Типы проверяемых данных

Вкладка Параметры диалогового окна Проверка вводимых значений предоставляет большой выбор типов данных для условий проверки. Выбрать тип допустимых данных можно в раскрывающемся списке Тип данных (как уже отмечалось, остальные элементы управления этой вкладки изменяются в зависимости от выбранного типа данных). Можно выбрать следующие типы данных.

  • Любое значение. Выбор этой опции удаляет условие проверки данных. Однако сообщение для ввода все равно будет выводиться, если не снять флажок Выводить сообщение об ошибке во вкладке Сообщение для ввода.
  • Целое число. Пользователь должен ввести целое число. С помощью раскрывающегося списка Значение можно определить допустимый диапазон значений. Например, можно определить, что вводимое значение должно быть целым числом и большим или равным 100.
  • Действительное. Пользователь должен ввести действительное число. Диапазон допустимых значений можно определить с помощью раскрывающегося списка Значение. Например, можно определить, что вводимое число должно быть больше или равно 0 и меньше или равно 1.
  • Список. Пользователь должен выбрать значение из предложенного списка значений. Подробнее см. ниже раздел Создание раскрывающегося списка.
  • Дата. Пользователь должен ввести дату. С помощью раскрывающегося списка Значение можно определить допустимый диапазон дат. Например, можно определить, что вводимая дата должна быть больше или равна 1 января 2012 года и меньше или равна 31 декабря 2012 года.
  • Время. Пользователь должен ввести значение времени. С помощью раскрывающегося списка Значение можно определить допустимый диапазон значений. Например, вводимое значение времени должно быть больше чем 12:00.
  • Длина текста. Ограничивается длина вводимой строки (количество символов). С помощью раскрывающегося списка Значение можно определить допустимую длину строки. Например, можно определить, что длина вводимой строки должна равняться 1 (один символ).
  • Другой. Логическая формула, которая определяет правильность вводимых пользователем данных. Формулу можно занести непосредственно в поле Формула (которое появляется при выборе этого типа) или определить ссылку на ячейку с формулой. Ниже приводятся примере нескольких полезных формул.

Во вкладке Параметры диалогового окна Проверка вводимых значений содержатся две опции.

  • Игнорировать пустые ячейки. Если установлен флажок этой опции, то допускаются пустые записи в ячейках, для которых определена проверка данных. Этот флажок действует немного странно. Если флажок снять и определить в ячейке какое-нибудь правило проверки данных, то флажок позволит оставить ячейку пустой. Но как только вы попытаетесь в ячейке ввести что-то недопустимое, и после этого попробуете оставить ячейку пустой, то это не получится.
  • Распространить изменения на другие ячейки с тем же условием. Если флажок этой опции установлен, то все внесенные изменения будут применяться также и к другим ячейкам, которые содержат исходные условия проверки данных. Если вы определили некое правило в одной или нескольких ячейках, а затем выбрали диапазон, включающий эти ячейки и некоторые другие, и кликнули в меню Проверка данных, то появится сообщение: «Выделенная область содержит ячейки без условий на значения. Распространить условия на эти ячейки?». Если нажмете «Да», то появится вкладка Параметры диалогового окна Проверка вводимых значений. Так вот сейчас флажок Распространить изменения на другие ячейки с тем же условием ставить не нужно.

Важно помнить, что даже после того, как была определена проверка вводимых данных, пользователь все равно может внести неправильные значения. Даже если в раскрывающемся списке Вид вкладки Сообщение об ошибке выбран вид сообщения Останов, вероятность внесения неверных данных все еще существует. Также стоит обратить внимание на то, что проверка вводимых данных не реагирует на результаты вычисления формул. Другими словами, применение проверки данных к ячейке, содержащей формулу, не даст никакого результата.

В Excel имеется команда ДанныеРабота с даннымиПроверка данныхОбвести неверные данные, после выбора которой все неверные значения будут обведены красным кружком (рис. 3).

Рис. 3. Ячейки с неверными значениями (значения которых больше 100) обведены кружками

Создание раскрывающегося списка

Возможно, проверка вводимых данных чаще всего используется для создания раскрывающегося списка значений. На рис. 4 приведен пример, в котором имена месяцев, содержащиеся в диапазоне А1:А12, используются для создания раскрывающегося списка.

Рис. 4. Список, созданный с помощью средства проверки данных

Чтобы создать такой список:

1. Введите список значений в один столбец или строку. Эти значения будут использоваться в раскрывающемся списке.

2. Выберите ячейку, которая должна содержать раскрывающийся список (в нашем примере – D3).

3. Во вкладке Параметры диалогового окна Проверка вводимых данных выберите тип данных Список и в поле Источник укажите диапазон, который содержит список значений (в нашем примере – $А$1:$А$12).

4. Удостоверьтесь, что установлен флажок Список допустимых значений.

5. Сделайте другие установки в диалоговом окне Проверка вводимых данных, как описано в предыдущем разделе.

После выполнения этих действий при активизации ячейки справа от нее будет появляться кнопка раскрывающегося списка. Щелкните на этой кнопке, чтобы развернуть список и выбрать необходимое значение.

Если список должен содержать небольшое количество значений, то их можно ввести непосредственно в поле Источник во вкладке Параметры диалогового окна Проверка вводимых значений (это поле появится, если выбрать из раскрывающегося списка Тип данных тип Список). Между вводимыми значениями нужно вставить разделитель, определенный в соответствии с региональными настройками (для России – это точка с запятой).

Если для задания раскрывающегося списка используется диапазон, то он обязательно должен находиться на том же листе, что и активизированная ячейка. В случае если список должен использовать значения из диапазона, который находится на другим листе, ему можно присвоить имя и затем использовать это имя в поле Источник (после символа равно). Например, если список содержит значения из списка МойСписок, нужно ввести следующую формулу: =МойСписок

Проверка данных с использованием формул

Создавать простые проверки данных с помощью средства проверки вводимых данных достаточно просто. Но настоящую мощь этого свойства можно ощутить только в том случае, если работать с формулами проверки данных.

Формула, определенная для проверки данных, должна возвращать логическое значение ИСТИНА или ЛОЖЬ. Если она вернет значение ИСТИНА, то введенное значение будет записано в ячейку. Если же результат вычисления формулы будет равен значению ЛОЖЬ, то появится сообщение об ошибке, в котором будет содержаться предупреждение, определенное во вкладке Сообщение об ошибке диалогового окна Проверка вводимых значений.

Определить формулу можно в диалоговом окне Проверка вводимых значений. Для этого из раскрывающегося списка Тип данных вкладки Параметры выберите тип Другой. Формулу можно ввести непосредственно в поле Формула или же вставить ссылку на ячейку, содержащую эту формулу. Поле Формула появляется во вкладке Параметры только в том случае, если выбран тип Другой.

Тип ссылок на ячейки в формулах для проверки данных

Если формула, введенная в диалоговое окно Проверка вводимых значений, содержит ссылку на ячейку, то она будет рассматриваться как относительная ссылка по отношению к верхней левой ячейке выделенного диапазона.

Следующий пример пояснит, почему именно относительные ссылки надо использовать в формулах для проверки данных. Предположим, что в диапазон В2:В10 необходимо только нечетные числа. В диалоговом окне Проверка вводимых значений нет такого типа данных, поэтому для создания критерия проверки данных надо использовать формулу. Для создания такого критерия проверки данных выполните следующие действия.

1. Выделите диапазон В2:В10 таким образом, чтобы ячейка В2 стала активизированной.

2. Выберите команду ДанныеРабота с даннымиПроверка данных, чтобы открыть диалоговое окно Проверка вводимых значений.

3. Перейдите на вкладку Параметры и в списке Тип данных выберите Другой.

4. Введите следующую формулу в поле Формула (рис. 5) =ЕНЕЧЁТ(В2). В этой формуле применена функция ЕНЕЧЁТ, которая возвращает значение ИСТИНА, если ее аргумент является нечетным числом.

5. Перейдите на вкладку Сообщение об ошибке и выберите вид сообщения Останов. Также введите текст сообщения «Разрешается ввод только нечетных чисел».

6. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Проверка вводимых значений.

Рис. 5. Ввод формулы в диалоговое окно Проверка вводимых значений

Заметьте, что введенная формула содержит ссылку на верхнюю левую ячейку выделенного диапазона. Эта формула должна применяться ко всему диапазону ячеек, поэтому следует ожидать, что каждая ячейка этого диапазона содержит такую же формулу. Поскольку в формуле ссыпка на ячейку относительная, то эта формула изменяется для каждой отдельной ячейки диапазона В2:В10. Чтобы в этом удостовериться, поставьте курсор, например, в ячейку В5, и откройте диалоговое окно Проверка вводимых значений. В этом окне вы должны увидеть формулу =ЕНЕЧЁТ(В5)

В общем случае, когда вводится формула для проверки данных в диапазон ячеек, следует использовать относительную ссылку на активизированную ячейку, которой, как правило, является верхняя левая ячейка выделенного диапазона. Исключение составляют ситуации, когда надо сделать ссылку на некоторую конкретную ячейку. Например, вы хотите, чтобы в диапазон А1:В10 вводились только такие значения, которые превышают значение в ячейке С1. Для этого используется формула =А1>$С$1

В таком случае ссылка на ячейку С1 делается абсолютной и поэтому данная ссылка не меняется во всех ячейках выделенного диапазона.

Примеры формул для проверки данных

Здесь приведено несколько примеров, в которых для проверки вводимых данных используются формулы. Все формулы были введены непосредственно в поле Формула во вкладке Параметры диалогового окна Проверка вводимых значений.

Ввод только текста. Для того чтобы разрешить ввод только текста (и запретить ввод числовых значений) в ячейку или диапазон, используется следующая формула: =ЕТЕКСТ(А1). Здесь предполагается, что А1 является активизированной ячейкой выделенного диапазона.

Ввод значений, больших, чем в предыдущей ячейке. Следующая формула проверки данных позволяет ввести число в ячейку только в том случае, если оно больше, чем значение в предыдущей ячейке: =А2>А1. В формуле предполагается, что активизированной ячейкой выделенного диапазона является ячейка А2. Заметьте, что эту формулу нельзя использовать в первой строке рабочего листа.

Ввод только уникальных значений. Следующая формула проверки вводимых данных не позволит пользователю ввести в диапазоне А1:С20 повторяющиеся значения: =СЧЁТЕСЛИ($А$1:$С$20;А1)=1. Здесь предполагается, что А1 является активизированной ячейкой выделенного диапазона. Обратите внимание на то, что в качестве первого аргумента функции СЧЁТЕСЛИ ($А$1:$С$20) используется абсолютная ссылка. Вторым аргументом (А1) является относительная ссылка, которая меняется для каждой ячейки выделенного диапазона. На рис. 6 показано, как работает эта формула. Здесь сделана попытка ввести в ячейку А5 значение 2, которое уже есть в диапазоне А1:С20.

Рис. 6. Использование средства проверки данных для предотвращения ввода дублирующихся значений

Ввод текста, начинающегося с буквы А. В следующей формуле используется прием, который позволяет проводить проверку по заданному символу. В данном случае формула вернет значение ИСТИНА, если ввести в ячейку строку, которая будет начинаться с буквы А (независимо от регистра): =ЛЕВСИМВ(А1)="а". В этой формуле предполагается, что активизированной ячейкой выделенного диапазона является ячейка А1.

Ниже приведена немного модифицированная формула проверки данных. С помощью этой формулы можно организовать ввод строки, которая состоит из пяти букв и начинается с буквы А:
=СЧЁТЕСЛИ (А1; "А????") =1

Возможно, вас также заинтересует Проверка формул в Excel, или что означает зеленый треугольник


[1] Цитируется по книге Джон Уокенбах. Microsoft Excel 2007. Библия пользователя. – М: ООО «И.Д. Вильямс», 2008. – С. 482–489.

baguzin.ru

Выпадающий список с поиском в Экселе.

Привет!

В Одессе сильная жара, и меня ждет море. Море работы. Я разработал такой вот выпадающий список с поиском — как в Гугле — для Экселя.

Если ты не знаешь, что такое выпадающие списки, не беда. В Экселе их можно найти по адресу:
Данные → Проверка данных… Выбор значений из списка.

Ты получишь следующий результат. В ячейке с проверкой данных появится стрелка, при нажатии на которую выпадет список возможных значений.

Чего склонен ожидать пользователь от такого списка?

  1. При вводе любого символа с клавиатуры список сокращается до тех значений, которые отвечают введенному критерию-символу. Это сделать нельзя.
  2. Ну хотя бы при вводе первого символа, чтобы список прокручивался до слов, начинающихся с той буквы, которую ты ввел. Нет, так сделать тоже нельзя.
Предлагаю решение, которое поможет тебе справляться с большими списками. Решение использует VBA с использованием пользовательской формы. Далее я пошагово расскажу, как у себя настроить то же. Но сперва предлагаю скачать пример с готовым фильтром, на базе которого ты можешь настроить свой шаблон.

Немного кода…


Работаем в редакторе VBA. Если ты раньше там не бывал, не бойся. В этом нет ничего страшного!

Нажми [Alt] + [F11] для открытия редактора кода. Интерфейс его весьма приятный, он не менялся, наверное, уже лет 20. Слева нажми правой кнопкой мыши по элементам текущей книги и нажми Insert > Module.

Итак, в обычный модуль добавь следующий код:


Option Explicit
Const minW = 170
Const minH = 15
Public NamedRange       As String
Public LinkedCell       As Range
Public List             As Range
Public L                As Long
Public T                As Long
Public W                As Long
Public H                As Long
Public CodeChange       As Boolean


Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long) As Long
 
Private Declare Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" (ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
 
Private Declare Function DrawMenuBar Lib "user32" ( _
ByVal hwnd As Long) As Long

Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hDC As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long
Private Const HWND_DESKTOP As Long = 0
Private Const LOGPIXELSY As Long = 90
Private Const LOGPIXELSX As Long = 88

Const TWIPSPERINCH = 1440

Private Declare Function SystemParametersInfo Lib "user32" _
      Alias "SystemParametersInfoA" _
    (ByVal uAction As Long, _
      ByVal uParam As Long, _
      lpvParam As Any, _
      ByVal fuWinIni As Long) As Long

Sub ShowForm()
    If UserForm1.Caption = "UserForm1" Then _
    UserForm1.Show ' show the form
End Sub

Sub ButtonLaunch()
' Change Range(...) to yours
Dim Target      As Range
On Error Resume Next
Set Target = ActiveCell
If Intersect(Target, Range("B4:B500")) _
    Is Nothing Or Target.Cells.Count > 1 Then
    Exit Sub
End If
SetList
Call DetectDimentions(Target)
Call SetLinkedCell(Target)
ShowForm
End Sub

Sub DetectDimentions(Ran As Range)
Dim S       As Shape
Dim C       As Range
Dim TwX     As Long
Dim TwY     As Long
Dim CorrX   As Long
Dim CorrY   As Long
Dim ScrRow  As Long
Dim ScrCol  As Long
Dim Cls     As Range
Dim Ros     As Range
Dim Zoom    As Integer

TwX = TwipsPerPixelX
TwY = TwipsPerPixelY
Set C = Cells(1, 1)
Set S = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 0, 0, 1, 1)
S.Top = C.Top
S.Left = C.Left

' find correction
Zoom = ActiveWindow.Zoom
ActiveWindow.Zoom = 100
ScrRow = ActiveWindow.ScrollRow
ScrCol = ActiveWindow.ScrollColumn
If ActiveWindow.ScrollRow > 1 Then
Set Cls = Range(Cells(1, 1), Cells(ScrRow - 1, 1))
CorrY = Cls.Height * 20 / TwX
End If
If ActiveWindow.ScrollColumn > 1 Then
Set Ros = Range(Cells(1, 1), Cells(1, ScrCol - 1))
CorrX = Ros.Width * 20 / TwX
End If

With ActiveWindow
    L = .PointsToScreenPixelsX(S.Left) + CorrX
    T = .PointsToScreenPixelsY(S.Top) + CorrY
End With
ActiveWindow.Zoom = Zoom
L = L * TwX / 20
T = T * TwY / 20

With Ran
    W = .Width
    If W < minW Then W = minW
    H = .Height
    If H < minH Then H = minH
End With

S.Delete

End Sub

Sub SetLinkedCell(Target As Range)
    Set LinkedCell = Target
End Sub

Sub SetList()
NamedRange = "Regions"
On Error Resume Next
    Set List = Range(NamedRange)
If Err.Number <> 0 Then
    MsgBox "Диапазона [" & NamedRange & "] не существует =("
    End
End If
End Sub

Public Function InList(Find As String, List As Range)
    InList = Application.Match(Find, List, 0)
    If IsError(InList) Then InList = False
End Function


Sub PopulateList(CB As MSForms.ListBox, List As Range, CBval As String)
    Dim Cel     As Range
    CB.Clear
    If CBval = "" Or InList(CBval, List) Then
        For Each Cel In List
            CB.AddItem Cel
        Next Cel
    Else
        CBval = UCase(CBval)
        For Each Cel In List
            If InStr(1, UCase(Cel), CBval) Then _
                CB.AddItem Cel
        Next Cel
    End If
End Sub

Sub RemoveCaption(objForm As Object)
     
    Dim lStyle          As Long
    Dim hMenu           As Long
    Dim mhWndForm       As Long
     
    If Val(Application.Version) < 9 Then
        mhWndForm = FindWindow("ThunderXFrame", objForm.Caption) 'XL97
    Else
        mhWndForm = FindWindow("ThunderDFrame", objForm.Caption) 'XL2000+
    End If
    lStyle = GetWindowLong(mhWndForm, -16)
    lStyle = lStyle And Not &HC00000
    SetWindowLong mhWndForm, -16, lStyle
    DrawMenuBar mhWndForm
     
End Sub

Public Function TwipsPerPixelX()
  Dim lngDC As Long
 
  lngDC = GetDC(HWND_DESKTOP)
  TwipsPerPixelX = TWIPSPERINCH / GetDeviceCaps(lngDC, LOGPIXELSX)
  ReleaseDC HWND_DESKTOP, lngDC
End Function
 
Public Function TwipsPerPixelY()
  Dim lngDC As Long
 
  lngDC = GetDC(HWND_DESKTOP)
  TwipsPerPixelY = TWIPSPERINCH / GetDeviceCaps(lngDC, LOGPIXELSY)
  ReleaseDC HWND_DESKTOP, lngDC
End Function

В этом коде найди строку NamedRange = “Regions” и слово Regions замени на свой диапазон. Это может быть адрес диапазона, например “Лист1!A1:A200”, это может быть имя диапазона, как в моем случае.
Так же в строке If Intersect(Target, Range(“B4:B500”)) тебе нужно поставить свой диапазон, в котором требуется создать выпадающие списки. Для этого замени B4:B500 на адрес своего диапазона. Этот адрес может не содержать имени листа, т.к. всегда используется текущий лист.

Дальше нужно вставить форму с двумя элементами управления: TestBox и ListBox.

Там же в редакторе кода вставляешь форму: Insert > UserForm. И на нее простыми кликами мыши переносишь TestBox и ListBox. Ты их можешь разместить как угодно. При запуске формы мы их разместим правильно.

Теперь пора вставлять код формы. Выдели саму форму и нажми [F7]. Это перенесет тебя в окно с кодом самой формы. В это окно вставь код:


Const ListBoxH = 100
Option Explicit
Private Sub ListBox1_Change()
If ListBox1.ListIndex > -1 Then
    If ListBox1.Value <> TextBox1.Value Then
        CodeChange = True
        TextBox1.Value = ListBox1.Value
    End If
End If
End Sub
Sub EndForm()
    LinkedCell = ListBox1.Value
    Unload Me
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    EndForm
End Sub

Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 13
    EndForm
Case 38
    If ListBox1.ListIndex = 0 Then TextBox1.SetFocus
End Select
End Sub

Private Sub TextBox1_Change()
If CodeChange Then
    CodeChange = False
Else
    LinkedCell.Value = TextBox1.Value
    Call PopulateList(UserForm1.ListBox1, List, TextBox1.Value)
End If
End Sub

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 18
    ListBox1.Value = ""
Case 40
    If ListBox1.ListCount > 0 Then
        ListBox1.ListIndex = 0
        ListBox1.SetFocus
    End If
End Select
End Sub

Private Sub UserForm_Terminate()
    If InList(LinkedCell.Value, List) = False Then
        LinkedCell.Value = ""
    End If
End Sub
Private Sub UserForm_Initialize()
    Call RemoveCaption(Me)
End Sub
Private Sub UserForm_Activate()
    With UserForm1
        .Top = T
        .Left = L
        .Width = W + 4
        .Height = H + ListBoxH - 4
    End With
    With ListBox1
        .Top = H
        .Left = 0
        .Width = W
        .Height = ListBoxH
    End With
    With TextBox1
        .Top = 0
        .Left = 0
        .Width = W
        .Height = H + 5
        .Value = LinkedCell.Value
    End With
    If LinkedCell.Value = "" Then _
    Call PopulateList(UserForm1.ListBox1, List, TextBox1.Value)
End Sub   

Дальше ты почти готов к использованию списков с поиском. Тебе нужно создать сам список, из которого будут выпадать значения. Теперь стань в любую ячейку, которая находится внутри целевого диапазона и нажми [Alt] + [F8]  и в списке макросов найди и запусти макрос: ButtonLaunch.

И еще для удобства пользования ты можешь привязать макрос, который запускает код к кнопке или картинке.

И еще ты можешь заставить код срабатывать автоматически. Для этого в код целевого листа вставь следующее:


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Change Range(...) to yours
If Target.Columns.Count > 1 Then Exit Sub
If Target.Rows.Count > 1 Then Exit Sub
If Intersect(Target, Range("B4:B500")) Is Nothing Then
    Exit Sub
End If
SetList
Call DetectDimentions(Target)
Call SetLinkedCell(Target)
ShowForm
End Sub
 

В этом последнем коде так же замени диапазон. В строке If Intersect(Target, Range(“B4:B500”)) тебе нужно поставить свой диапазон, в котором требуется создать выпадающие списки. Для этого замени B4:B500 на адрес своего диапазона.

***
Сложно мне было достичь такого результата… Что я только не перепробовал, пока не пришел к такому решению. И оно, разумеется, не идеально…

Привет разработчикам

В Экселе, оказывается, не все так просто, как на самом деле. При разработке данного проекта я пробовал использовать следующие средства:

Элементы ActiveX. 
Результат: работают они красивее, чем формы, к тому же они самостоятельны (не привязаны к оболочке) и являются  частью рабочего пространства т.е. ячеек Экселя. Поэтому связанные списки у меня появлялись прямо в ячейках. Но при работе с ActiveX, оказалось, что они сплошь глючные, иногда неадекватно показывают событие выпадения самого списка. И когда, преодолев все сложности, я дописал код, оказалось, что он работает нестабильно. На более слабой машине, чем моя, Эксель пока последней 2013 версии вылетал каждый раз при попытке создания списка.

Элементы управления формы.
Они, как я узнал на одном из форумов, более приятны в обращении, но оказалось, что выпадающий список форм не обладает всем функционалом, который мне был необходим. Так что это пока отпадает.

Выпадающий список в форме.
Это уже ближе. С формой у меня есть отдельная боль — ее очень нелегко разместить в нужном месте экрана, на что ушло процентов 80 всего кода и столько же усилий. Но главное, что выпадающий список в стандартной форме Экселя тоже работал глючно. А именно, при попытке заставить его выпадать, он часто выдавал непредсказуемый результат на экран.

Сочетание элементов управления в форме.
Наконец, я пришел к двум связанным элементам формы, которые симулируют один выпадающий список, а именно это простое текстовое поле (TestBox) и простой список(ListBox). Именно этот вариант привел меня к решению.

Решение это мне не нравится до сих пор: уж слишком оно громоздкое и неизящное. Слишком много усилий ради такой простой функции. Что ж, будем искать еще варианты. Но, тем не менее, приведенное выше решение работает стабильно и не приводит к перезагрузке программы (надеюсь, что это так). Поэтому предлагаю тебе адаптировать его.

Идея!

И тут, при написании этой статьи, ко мне пришла идея: а что если использовать смешанную технику для нахождения более изящного решения.

В примере выше, который я как раз закончил делать, ты видишь динамический список, который появляется непосредственно на месте ячейки, которую мы форматируем. Замечу несколько моментов:

  1. Во-первых, качай пример себе и тестируй, адаптируй, развивай.
  2. Второе. Как его адаптировать. В коде листа есть обработчик событий, и в нем нужно заменить во второй строке Const DropDownRange = “B4:B500” на свой диапазон. С этим проблем не должно возникнуть. Дальше в модуле есть код, который обрабатывает большинство вещей. В нем ты тоже замени кое-что, а именно в строке Public Const NamedRange = “Regions” поменяй “Regions” на полный адрес диапазона со значениями для проверки. Тут удобнее использовать именованный диапазон, как в моем примере, но можно и прописать полный адрес, например Лист1!А1:А25
  3. Третье. Данным списком удобно пользоваться. Он работает шустро. Он отвечает на некоторые события: после редактирования текста меняется список, это само собой. Но еще ты можешь после ввода чего-нибудь нажать на стрелки Вниз – Вверх, при этом выделятся значения внутри списка, и потом нажать [Tab] или [Enter] ввести выбранное значение. Это, по-моему, совпадает с ожидаемым поведением элементов управления. Тут пока есть небольшая недоработка, которую ты легко заметишь, протестировав пример. А именно если в текстовое поле ничего не введено, то оно не реагирует на нажатие стрелок. Но с этим можно жить, а к тому же, думаю, пытливый ум найдет решение.
  4. Данный список не проверяет жестко введенный текст в ячейку, но с этим можно легко справиться, например, дополнительно сделав проверку данных по диапазону.
Это решение, по-моему, максимально отвечает поставленной задаче. Тут есть еще простор для улучшений, но начало задано. Смысл работы основывается на нескольких идеях:

Идея первая. Сделай качественную подделку.
Можно подделать элементы управления, если их создавать прямо сверху ячейки, которую выделил пользователь.

Идея вторая. Использовать элементы ActiveX и элементы формы
Ввиду множества проделанных неудачных опытов, я пришел к выводу, что напрямую добиться результата нельзя. Но можно отдельно использовать элемент со списком ListBox, и элемент поле TextBox, и заставить из совместно работать как ComboBox — выпадающий список. Повторюсь, что напрямую это сделать я не смог — уж очень много глюков обнаружилось. И вот я в решении соединил 2 элемента управления, причем один из них — ActiveX (поле), а второй — элемент формы (список). Это дало более стабильный результат, но при этом изящный и более легковесный, чем решение с пользовательскими формами. К тому же мне повезло найти пример с обработкой нажатия пользователем кнопок (KeyCode) при изменении Поля TextBox. Это дало возможность сделать список более дружелюбным к действиям пользователя.

Идея третья. Что еще можно сделать
Еще можно сделать так, чтобы пользователь смог вручную отключать опцию выпадения списка. Дело в том, что это может быть слишком навязчиво и неудобно при форматировании таблицы в целом. Тут оставляю пробел и специально не дорабатываю код: это довольно просто сделать и способов множество, так что оставляю эту работу за тобой.

И еще, разумеется, можно повысить дружелюбность списков, и добавить жесткую проверку данных, которая не даст ошибиться при вводе. Но это потом.

***
Скоро будут выходные, и я хочу все-таки пойти на море. Поэтому закругляюсь.

www.doconomist.net