0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как всегда показывать стрелку списка проверки данных в Excel?

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

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

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

В этой статье мы создадим выпадающий список с помощью Data / Data Management / Validation с типом данных List (Список).

Существует множество способов форматирования выпадающего списка.

А. Простейший выпадающий список — ввод элементов списка непосредственно в поле Источник

Самый простой способ создать выпадающий список — ввести элементы списка непосредственно в поле Source (Источник) инструмента проверки данных.

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

Если в поле Source указаны единицы измерения pcs;kg;sq.m;cub.m, то выбор будет ограничен этими четырьмя измерениями.

Теперь посмотрите, что у нас есть. Выделите ячейку B1. Когда ячейка выделена, справа от нее появляется квадратная кнопка со стрелкой, позволяющая выбирать элементы из выпадающего списка.

Элементы списка легко потерять (например, удалив строку или столбец, содержащий ячейку B1); вводить большое количество элементов неудобно. Для небольших (3-5 значений) неизменяемых списков этот подход подходит. Он позволяет быстро создать список.

Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

Элементы раскрывающегося списка можно поместить в диапазон в Excel, а затем в поле «Источник» инструмента проверки данных можно сделать ссылку на диапазон.

Предположим, что элементы списка pcs;kg;m²;m²;cubic meters введены в ячейки в диапазоне A1:A4 , поле Source будет содержать =list1!$A$1:$A$4

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

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

Читайте так же:
Как в Excel отсортировать даты по кварталам?

Во-первых, давайте устраним второй недостаток, разместив список выпадающих элементов на другом листе.

B. Ввод элементов списка в диапазон (на любом листе)

Ни правила проверки данных, ни условное форматирование не позволят вам напрямую ссылаться на диапазоны другого листа (см. Пример файла):

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

И поместите диапазон со списком элементов на другой лист (на лист List в файле примера).

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

Использование именованного диапазона Создайте диапазон с именем List_Items, содержащий список элементов выпадающего списка (ячейки A1:A4 на листе List). Для этого

  • Выберите A1:A4 ,
  • Нажмите Формулы/Неопределенные имена/Присвоить имя
  • В поле Имя введите Список_элементов, в поле Область выберите Книга ;

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

  • Вызовите Управление данными ;
  • В поле Источник введите ссылку на созданное имя: =Список элементов .

Учитывая, что список элементов будет дополняться, можно сразу выбрать больший диапазон, например A1:A10. Однако в этом случае выпадающий список может содержать пустые строки.

Вы можете использовать динамический диапазон, чтобы избавиться от пустых строк и учесть новые элементы списка. Для этого при создании имени элемента списка в поле Диапазон необходимо ввести формулу = MESSAGE(List!$A$1;;;MESSAGE(List!$A:$A)).

R EAD() предполагает, что диапазон ( A:A ), в котором находятся элементы, будет заполнен без пропуска строк (см. файл примера, лист Dynamic Range).

Использует функцию DWSSYL().

Используйте функцию DVSSRL() для ссылки на список элементов на другом рабочем листе. Выберите диапазон ячеек, в котором будет находиться раскрывающийся список на листе Пример, затем вызовите функцию Проверка данных и укажите =DBCRL(«list!A1:A4») в Источнике.

Формула перестает работать, если вы переименовываете лист. Подробную информацию о том, как частично обойти эту проблему, см. в статье Определение имени листа.

Ввод элементов списка в диапазоне ячеек, находящемся в другой книге

Например, при желании можно переместить элементы раскрывающегося списка в книгу Source.xlsx:

  • В рабочей книге Source.xlsx создайте необходимый список элементов;
  • В рабочей книге Source.xlsx назовите диапазон ячеек, содержащих список элементов, например, ListInside;
  • Откройте рабочую книгу, в которой вы хотите разместить ячейки с выпадающим списком;
  • Выделите необходимый диапазон ячеек, вызовите контроллер данных и в поле Source укажите = DVSYL(«[Source.xlsx]sheet1!ListInside»);
Читайте так же:
Как быстро преобразовать файл XLSX в файл XLS или PDF?

При работе со списком элементов, расположенных в другой рабочей книге, в меню Источник . Файл xlsx должен быть открыт и находиться в той же папке, в противном случае необходимо указать полный путь к файлу. В целом, лучше избегать ссылок на другие листы или использовать Personal.xlsx или Add-ons .

Чтобы не называть диапазон в файле Source.xlsx, измените формулу на = DVSYL(«[Source.xlsx]sheet1!$A$1:$A$4»)

СОВЕТ: Если лист содержит много ячеек с правилами проверки данных, можно воспользоваться инструментом Выделить группу ячеек (Главная / Найти и выбрать / Выделить группу ячеек). Опция Проверка данных этого инструмента позволяет выбрать ячейки, для которых выполняется проверка данных (определяется с помощью команды Данные / Работа с данными / Проверка данных). Если выбрана опция Все, все эти ячейки будут выделены. При выборе «Эти» также выбираются только те ячейки, для которых определены те же правила проверки данных, что и для активной ячейки.

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

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

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

Как всегда показывать стрелку списка проверки данных в Excel?

На днях я получил от бывшего коллеги вопрос о том, как реализовать в Excel каскадные выпадающие списки, чтобы то, что вы выбираете в одном списке, диктовало, что вы можете выбрать в другом, и так далее. Тема выпадающих списков так давно и так густо затронута популяризаторами Excel, что я ожидал легко найти нужный рецепт, но ошибся. Рецепт я нашел, но он мне не очень понравился из-за своей громоздкости, и повторить его пользователю со средними навыками будет довольно сложно. Метод достоин восхищения, поскольку он реализуется только с помощью формул рабочего листа. Тем не менее, я придумал другой способ, который мы сейчас и разберем. Я приведу пример, который вы можете легко адаптировать под свои нужды.

Читайте так же:
Как быстро вставить текущую дату с определенным форматом даты в Excel?

Как это выглядит

Для тех, кто умеет работать с проверкой данных в Excel, не раз создавал выпадающие списки, но плохо разбирается в макросах, просмотр этих списков станет серьезным умственным испытанием, особенно если вы уже настроили этот файл под свои задачи. Они проведут полдня, задаваясь вопросом: «Как, черт возьми, ты это сделал»? 🙂

Функционал

  1. Создавайте каскадные выпадающие списки. Количество каскадов не ограничено, но не стоит слишком увлекаться этим, так как это может повлиять на производительность Excel при работе с ячейками списка. 2-4 каскада более чем достаточно для выполнения типичных задач.
  2. В цепочке списков начальный список является таковым, потому что он ни от кого не зависит, а конечный список является таковым, потому что от него никто не зависит. Промежуточные списки всегда зависят от кого-то и всегда превосходят чьи-то списки. Вышестоящий список передает свое значение своему преемнику, а тот отображает список следующего шага, который уже фильтрует возможные варианты в соответствии со значениями в вышестоящем списке.
  3. При изменении значения ячейки списка на верхнем уровне очищаются (зависимые) ячейки на нижнем уровне.
  4. При переходе к ячейке, содержащей список, он открывается автоматически, что облегчает и ускоряет выбор значений. Если выбор не требуется, нажмите Esc.

Скачать пример

Как это настроить для вашего файла с нуля?

Шаг 1

Используя Excel, создайте книгу, которая поддерживает макросы (*. XLSM)

  1. Первый — это место, где будут располагаться каскадные полосы. Я называю этот лист FRONT, но вы можете дать ему любое название, которое вам нравится
  2. Второй лист — это место для справочной таблицы, в колонках которой будут отображаться списки. Я буду называть этот лист REF . Опять же, вы можете дать листу другое имя, но немного проще сохранить это имя.
  3. Третий лист является вспомогательным листом, и мы называем его BACK . Я не рекомендую менять имя, потому что нам все равно нужно сделать этот лист скрытым.

Шаг 2

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

  1. Перейдите к любой ячейке справочника,
  2. Выберите Формат как таблица на вкладке Главная на ленте,
  3. Выберите нужный шаблон таблицы, укажите, есть ли строка заголовка, нажмите OK
  4. На ленте на вкладке КОНСТРУКТОР (курсор должен находиться в ячейке таблицы) перейдите к полю ввода Имя таблицы и дайте нашей таблице имя, например Справочник.
Читайте так же:
Как быстро выполнить ранжирование цветов в Excel?

Шаг 3

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

  1. В ленте выберите вкладку ФОРМУЛЫ , пункт Диспетчер имен
  2. Нажмите кнопку Создать.
  3. Дайте имя rngRef , Область оставьте Книга , в качестве Диапазона укажите =Reference[#Данные] (=Reference[#Data])
  4. Нажмите OK

Шаг 4

Отметьте, где вы будете размещать списки выбора на ПЕРЕДНЕМ листе. Не используйте для этого ячейку A1.

Шаг 5

  1. В ячейку A1 листа BACK внесите имя созданного вами именованного диапазона, то есть rngRef
  2. В те же ячейке на листе FRONT , где у вас будут выпадающие списки, но только на листе BACK необходимо занести данные в определенном формате
  3. Данные в каждой ячейке будут состоять из трёх частей, разделенных точкой с запятой, например » 2 ; A2 ; C2 » или » 1 ;; B3 «
  4. Первая часть (до первого символа «;») означает номер столбца в вашем справочнике, который (столбец) будет использован для формирования значений списка.
  5. Вторая часть (между двумя символами «;») означает адрес родительской ячейки. У списков, являющихся первыми в каскаде, на этом месте всегда пустота, так как у них нет родительских ячеек.
  6. Третья часть (справа от второго символа «;») означает адрес ячейки, которая зависит от текущей. У списков, которые замыкают каскад, на этой позиции всегда пустота, например, » 3 ; B4 ;»
  7. Скройте лист BACK (правой кнопкой мыши по ярлыку листа, далее выбрать пункт Скрыть )

Шаг 6 (последний)

  1. Откройте редактор Visual Basic, используя комбинацию клавиш Alt+F11
  2. Слева, в окне с названием Project — VBAProject, найдите ПЕРЕДНИЙ лист вашей рабочей книги (помните, Дважды щелкните на имени этого листа
  3. Скопируйте этот код с сайта в редактор кода Visual Basic (правая часть экрана)
  4. Если вы назвали лист REF по-другому, найдите строку Const con_wsRef = «REF» и исправьте имя стандартного листа в кавычках на свое собственное (находится в строке 8).

Как это работает?

В Excel стандартные объекты (листы, ячейки), с которыми мы работаем, имеют встроенные пользовательские обработчики событий, которые программист VBA может легко использовать в своих целях. Поэтому мы назвали событие рабочего листа FRONT SelectionChange . Это означает, что каждый раз при изменении текущей ячейки на рабочем листе вызывается процедура Worksheet_SelectionChange, в которую мы помещаем наш код. Код ничего не делает, пока пользователь не встанет на ячейку, помеченную определенным образом на листе BACK.

Читайте так же:
Как быстро вставить пустой столбец между каждым другим столбцом в Excel?

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

Несмотря на то, что вы можете найти код макроса выше, я рекомендую изучить его в редакторе VBA в Excel, так как на сайте код не имеет правильного отступа. Эта статья должна была вас позабавить. Крутые каскадные выпадающие списки в ваших файлах произведут впечатление на ваших коллег! 🙂

Обновление!

Все, кто пытался прикрепить эту реализацию каскадных списков к более или менее обширным данным, сталкивались с одной печальной ситуацией: вроде бы все работает, но после повторного открытия файла Excel с сожалением сообщает, что файл поврежден, и предлагает восстановить его, что он успешно и делает. Для реализации выпадающего списка мы добавили правило проверки данных, которое ограничивало количество символов в строке. Excel не жалуется, когда мы создаем этот список программно (по сравнению со стандартным диалоговым окном). Файл сохраняется без проблем, но при открытии программа жалуется на это. Вот почему у меня возникла идея никогда не хранить правила валидации в ячейке. Другими словами, мы формируем выпадающий список для события SelectionChange на лету и удаляем этот список для события Change, поскольку пользователь уже выбрал интересующее его значение. Тем не менее, если пользователь не выберет значение из списка, событие Change не произойдет, а правило управления данными останется, что опять же чревато «порчей» файла. Поэтому мне приходится прибегать к такому извращенному приему, как сканирование каждого правила управления данными на рабочем листе и удаление «наших» (тех, которые по своему расположению соотносятся с рабочими листами BACK). На данный момент я привязал это к событиям активации/деактивации листа. Если у вас на листе много ячеек, которые используют правила проверки данных, но не коррелируют с каскадными выпадающими списками, я бы, вероятно, НЕ рекомендовал вам это решение.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector