Список в ячейке Excel

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

Самый элементарный метод

Самый простой способ — щелкнуть левой кнопкой мыши ячейку, которая не содержит никаких значений, а затем перейти в контекстное меню, где нажать «Выбрать из раскрывающегося списка». Также можно использовать комбинацию Alt + стрелка вниз. Однако этот метод имеет ограничение. Это выражается в том, что список не работает, если ячейка, в которой планируется список, отделена от ячеек, из каких извлекается информация. Кроме того, этот метод связан с очень небольшим количеством дополнительных возможностей.

Но для основных задач результат будет нормальным.

Стандартный метод

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

Далее нужно кликнуть по кнопке «ОК», и ячейка превратится в список, появляющийся при нажатии по ней.

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

Списки этого типа могут связываться с другими.

Элемент управления

Сей метод предусматривает создание нового элемента – поля со списком и с дальнейшей привязкой к нему нужных ячеек. Для реализации этой задачи выполняйте следующую инструкцию:

1. Если используются последние версии Excel (от  2007), то следует открыть вкладку «Разработчик». Если же версия более старая, нужно перейти на панель «Формы», доступ к которой можно получить через меню Вид – Панели инструментов. Если у вас эта вкладка не отображается, то нужно найти кнопку «Офис» (есть только в версиях 2007 и новее) и нажать по ней. Далее – открыть «Параметры Excel», и там поставить галочку возле пункта «Отображать вкладку «Разработчик» на ленте».
2. Найти символ выпадающего списка на верхней панели (не Active X, а обычный). Чтобы найти правильный элемент, нужно навести на каждый из них мышью и подождать несколько секунд до тех пор, пока не появится всплывающая подсказка, как показано на рисунке. 3.gif
3. Кликнуть по этому значку.
4. Нарисовать элемент, после чего вызвать его контекстное меню.

После всех этих операций появится контекстное меню, где следует выбрать пункт «Формат объекта» и выставить следующие параметры:

1. Формировать список по диапазону. Затем нужно будет выделить данные, на основе которых будет формироваться будущий перечень. 
2. Связь с ячейкой. Тут необходимо выбрать ячейку, в какую станут выводиться данные, указанные вами.
3. Количество строк списка. Здесь нужно указать, сколько строк должен выводить список. Предустановлено 8 пунктов, но можно сделать перечень больше. Предыдущий пункт таких возможностей не имеет.

Далее нажмите «ОК» и список готов к использованию.

Может быть сделано так, что вместо номера элемента отображается имя элемента. Вы должны использовать функцию ИНДЕКС, чтобы реализовать эту задачу.

Элемент Active X

Этот метод аналогичен предыдущему, с той лишь разницей, что используется элемент Active X «Поле со списком», расположенный в том же меню, в котором он вызывается путем нажатия кнопки «Вставить» на вкладке «Разработчик». У него есть несколько преимуществ по сравнению с предыдущим.

В целом, механизм его добавления такой же, как и у обычного элемента: нужно его выбрать и нарисовать. После этого начинаются уже принципиальные отличия. Дело в том, что элемент Active X имеет два режима: отладки и ввода. В первом варианте возможна его гибкая настройка, редактирование параметров, внешнего вида и расположения на листе. Если же выбран режим ввода, то им можно пользоваться лишь по назначению: выбирать любой из представленных в нем пунктов.

Чтобы переключаться между режимами отладки и ввода, нужно нажать на кнопку «Режим конструктора», расположенную на той же вкладке «Разработчик».

При нажатии этой кнопки можно редактировать параметры списка, доступ к которым можно получить через кнопку «Свойства», расположенную справа от кнопки «Режим конструктора».

Самые полезные параметры следующие:

1. ListFillRange – диапазон, поставляющий элементы перечня. При этом нет возможности самостоятельно выделить диапазон, поэтому придется его вписывать вручную. Например, так: «Лист2!A1:A5».
2. LinkedCell – связанная ячейка, в какой Excel будет записывать элемент.
3. ListRows – количество строк.
4. Font – принципиально новый элемент, позволяющий изменять шрифт (за исключением его цвета).
5. ForeColor и BackColor – еще два новых параметра, позволяющих настроить цвет текста и фона.

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

Если используется этот способ составления списков, то можно указывать и многомерные диапазоны. Например, диапазон, состоящий из двух колонок, дополнительно указав на необходимость выведения двух столбцов (для этого надо настроить параметр ColumnCount=2). Тогда можно добиться весьма интересных результатов, которые с лихвой окупят все усилия и время, потраченные на настройку элемента Active X «Поле со списком».

Как создать связанный список Excel?

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

Это можно сделать разными методами.

Метод 1: Функция ДВССЫЛ

В первом случае надо применить функцию ДВССЫЛ, способную преобразовывать в ссылку любой текст. Например, если написано A1, то функция вернет ссылку на ячейку, имеющую такой же адрес. Если же в ячейке написан текст «Маша», то эта формула сможет сделать линк на именованный диапазон с таким названием. Проще говоря, она может менять ссылку и название местами.

Предположим, нам надо составить такой перечень моделей марок Toyota, Ford, Nissan.

Модельный ряд Toyota нужно выделить, начиная ячейкой A2 вплоть до конца перечня и назвать его аналогично. Если используется древняя версия Excel, эта функция реализуется через меню «Вставка – Имя – Присвоить». Если версия Excel не самая старая (начиная с 2007), то нужно перейти во вкладку «Формулы», где отыскать «Диспетчер имен». Аналогичная операция проводится и с перечнем модельного ряда других производителей автомобилей.

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

Теперь нужно сгенерировать перечень автомобилей. Нужно нажать мышью (левой кнопкой) по пустой ячейке и открыть меню Данные – Проверка для Excel 2003 версии и кнопку «Проверка данных» на вкладке «Данные» для Excel 2007 версии и более современных. Затем в перечне «Тип данных» нужно выбрать Список, и в поле «Источник» написать ячейки с названиями марок.

Теперь следует сформировать второй список, где будут перечисляться машины из модельного ряда. Последовательность действий такая же, но с тем лишь исключением, что в поле Источник нужно указать формулу =ДВССЫЛ(F3). Естественно, в скобках нужно указать адрес подходящей именно в вашем случае ячейки. Все, после того, как вы нажмете ОК, формирование второго перечня будет осуществляться, исходя из данных в первом.

Но такой метод имеет ряд существенных недостатков:

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

Использование функции СМЕЩ и ПОИСКПОЗ

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

Для формирования первого списка, который будет влиять на содержимое второго, подойдет и стандартный способ, описанный выше, то есть:

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

А вот для второго списка, на который будет влиять первый, придется выполнять другие действия, а именно создать именованный диапазон с функцией =СМЕЩ, создающей ссылку на ячейку, где указаны исключительно модели определенной марки. А для этого осуществляются следующие действия:

1. Надо нажать комбинацию Ctrl + F3. Также можно открыть диспетчер имен и присвоить имя соответствующему диапазону.
2. Создать новый диапазон вместе с функцией СМЕЩ в ссылке и присвоить ему имя. Оно может быть каким-угодно, например, «Модели».

В нашей ситуации формула будет следующей:

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

Важно указывать только абсолютные ссылки. То есть, в них надо указать знак $. После нажатия клавиши Enter, Excel автоматически добавит имена листов. Это не должно вас пугать.

Результат будет следующий:

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

1. Выделить ячейку G8.
2. Выбрать команду «Проверка данных» на вкладке «Данные».
3. Там, где источник списка, указать имя диапазона: =Модели.

И все!

Выводы

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

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

Также важно помнить про ограничение первых двух методов – возможность использовать не более 8 элементов в одном списке. Два последних лишены этого недостатка.