Содержание
Microsoft Excel: выпадающие списки
При работе в программе Microsoft Excel в таблицах с повторяющимися данными, очень удобно использовать выпадающий список. С его помощью можно просто выбирать нужные параметры из сформированного меню. Давайте выясним, как сделать раскрывающийся список различными способами.
Создание дополнительного списка
Самым удобным, и одновременно наиболее функциональным способом создания выпадающего списка, является метод, основанный на построении отдельного списка данных.
Прежде всего, делаем таблицу-заготовку, где собираемся использовать выпадающее меню, а также делаем отдельным списком данные, которые в будущем включим в это меню. Эти данные можно размещать как на этом же листе документа, так и на другом, если вы не хотите, чтобы обе таблице располагались визуально вместе.
Выделяем данные, которые планируем занести в раскрывающийся список. Кликаем правой кнопкой мыши, и в контекстном меню выбираем пункт «Присвоить имя…».
Открывается форма создания имени. В поле «Имя» вписываем любое удобное наименование, по которому будем узнавать данный список. Но, это наименование должно начинаться обязательно с буквы. Можно также вписать примечание, но это не обязательно. Жмем на кнопку «OK».
Переходим во вкладку «Данные» программы Microsoft Excel. Выделяем область таблицы, где собираемся применять выпадающий список. Жмем на кнопку «Проверка данных», расположенную на Ленте.
Открывается окно проверки вводимых значений. Во вкладке «Параметры» в поле «Тип данных» выбираем параметр «Список». В поле «Источник» ставим знак равно, и сразу без пробелов пишем имя списка, которое присвоили ему выше. Жмем на кнопку «OK».
Выпадающий список готов. Теперь, при нажатии на кнопку у каждой ячейки указанного диапазона будет появляться список параметров, среди которых можно выбрать любой для добавления в ячейку.
Создание выпадающего списка с помощью инструментов разработчика
Второй способ предполагает создание выпадающего списка с помощью инструментов разработчика, а именно с использованием ActiveX. По умолчанию, функции инструментов разработчика отсутствуют, поэтому нам, прежде всего, нужно будет их включить. Для этого, переходим во вкладку «Файл» программы Excel, а затем кликаем по надписи «Параметры».
В открывшемся окне переходим в подраздел «Настройка ленты», и ставим флажок напротив значения «Разработчик». Жмем на кнопку «OK».
После этого, на ленте появляется вкладка с названием «Разработчик», куда мы и перемещаемся. Чертим в Microsoft Excel список, который должен стать выпадающим меню. Затем, кликаем на Ленте на значок «Вставить», и среди появившихся элементов в группе «Элемент ActiveX» выбираем «Поле со списком».
Кликаем по месту, где должна быть ячейка со списком. Как видите, форма списка появилась.
Затем мы перемещаемся в «Режим конструктора». Жмем на кнопку «Свойства элемента управления».
Открывается окно свойств элемента управления. В графе «ListFillRange» вручную через двоеточие прописываем диапазон ячеек таблицы, данные которой будут формировать пункты выпадающего списка.
Далее, кликаем по ячейке, и в контекстном меню последовательно переходим по пунктам «Объект ComboBox» и «Edit».
Выпадающий список в Microsoft Excel готов.
Чтобы сделать и другие ячейки с выпадающим списком, просто становимся на нижний правый край готовой ячейки, нажимаем кнопку мыши, и протягиваем вниз.
Связанные списки
Также, в программе Excel можно создавать связанные выпадающие списки. Это такие списки, когда при выборе одного значения из списка, в другой графе предлагается выбрать соответствующие ему параметры. Например, при выборе в списке продуктов картофеля, предлагается выбрать как меры измерения килограммы и граммы, а при выборе масла растительного – литры и миллилитры.
Прежде всего, подготовим таблицу, где будут располагаться выпадающие списки, и отдельно сделаем списки с наименованием продуктов и мер измерения.
Присваиваем каждому из списков именованный диапазон, как это мы уже делали ранее с обычными выпадающими списками.
В первой ячейке создаём список точно таким же образом, как делали это ранее, через проверку данных.
Во второй ячейке тоже запускаем окно проверки данных, но в графе «Источник» вводим функцию «=ДВССЫЛ» и адрес первой ячейки. Например, =ДВССЫЛ($B3).
Как видим, список создан.
Теперь, чтобы и нижние ячейки приобрели те же свойства, как и в предыдущий раз, выделяем верхние ячейки, и при нажатой клавише мышки «протаскиваем» вниз.
Всё, таблица создана.
Мы разобрались, как сделать выпадающий список в Экселе. В программе можно создавать, как простые выпадающие списки, так и зависимые. При этом, можно использовать различные методы создания. Выбор зависит от конкретного предназначения списка, целей его создания, области применения, и т.д.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 11905 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Drop-down List
Drop-down lists in Excel are helpful if you want to be sure that users select an item from a list, instead of typing their own values.
Create a Drop-down List
To create a drop-down list in Excel, execute the following steps.
1. On the second sheet, type the items you want to appear in the drop-down list.
Note: if you don’t want users to access the items on Sheet2, you can hide Sheet2. To achieve this, right click on the sheet tab of Sheet2 and click on Hide.
2. On the first sheet, select cell B1.
3. On the Data tab, in the Data Tools group, click Data Validation.
The ‘Data Validation’ dialog box appears.
4. In the Allow box, click List.
5. Click in the Source box and select the range A1:A3 on Sheet2.
Note: to copy/paste a drop-down list, select the cell with the drop-down list and press CTRL + c, select another cell and press CTRL + v.
7. You can also type the items directly into the Source box, instead of using a range reference.
Note: this makes your drop-down list case sensitive. For example, if a user types yes, an error alert will be displayed.
Allow Other Entries
You can also create a drop-down list in Excel that allows other entries.
1. First, if you type a value that is not in the list, Excel shows an error alert.
To allow other entries, execute the following steps.
2. On the Data tab, in the Data Tools group, click Data Validation.
The ‘Data Validation’ dialog box appears.
3. On the Error Alert tab, uncheck ‘Show error alert after invalid data is entered’.
5. You can now enter a value that is not in the list.
Add/Remove Items
You can add or remove items from a drop-down list in Excel without opening the ‘Data Validation’ dialog box and changing the range reference. This saves time.
1. To add an item to a drop-down list, go to the items and select an item.
2. Right click, and then click Insert.
3. Select "Shift cells down" and click OK.
Note: Excel automatically changed the range reference from Sheet2!$A$1:$A$3 to Sheet2!$A$1:$A$4. You can check this by opening the ‘Data Validation’ dialog box.
4. Type a new item.
5. To remove an item from a drop-down list, at step 2, click Delete, select "Shift cells up" and click OK.
Dynamic Drop-down List
You can also use a formula that updates your drop-down list automatically when you add an item to the end of the list.
1. On the first sheet, select cell B1.
2. On the Data tab, in the Data Tools group, click Data Validation.
The ‘Data Validation’ dialog box appears.
3. In the Allow box, click List.
4. Click in the Source box and enter the formula: =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
Explanation: the OFFSET function takes 5 arguments. Reference: Sheet2!$A$1, rows to offset: 0, columns to offset: 0, height: COUNTA(Sheet2!$A:$A) and width: 1. COUNTA(Sheet2!$A:$A) counts the number of values in column A on Sheet2 that are not empty. When you add an item to the list on Sheet2, COUNTA(Sheet2!$A:$A) increases. As a result, the range returned by the OFFSET function expands and the drop-down list will be updated.
6. On the second sheet, simply add a new item to the end of the list.
Remove a Drop-down List
To remove a drop-down list in Excel, execute the following steps.
1. Select the cell with the drop-down list.
2. On the Data tab, in the Data Tools group, click Data Validation.
The ‘Data Validation’ dialog box appears.
3. Click Clear All.
Note: to remove all other drop-down lists with the same settings, check "Apply these changes to all other cells with the same settings" before you click on Clear All.
Dependent Drop-down Lists
Want to learn even more about drop-down lists in Excel? Learn how to create dependent drop-down lists.
1. For example, if the user selects Pizza from a first drop-down list.
2. A second drop-down list contains the Pizza items.
3. But if the user selects Chinese from the first drop-down list, the second drop-down list contains the Chinese dishes.
Table Magic
You can also store your items in an Excel table to create a dynamic drop-down list.
1. On the second sheet, select a list item.
2. On the Insert tab, in the Tables group, click Table.
3. Excel automatically selects the data for you. Click OK.
4. If you select the list, Excel reveals the structured reference.
5. Use this structured reference to create a dynamic drop-down list.
Explanation: the INDIRECT function in Excel converts a text string into a valid reference.
6. On the second sheet, simply add a new item to the end of the list.
Note: try it yourself. Download the Excel file and create this drop-down list.
7. When using tables, use the UNIQUE function in Excel 365 to extract unique list items.
Note: this dynamic array function, entered into cell F1, fills multiple cells. Wow! This behavior in Excel 365 is called spilling.
8. Use this spill range to create a magic drop-down list.
Explanation: always use the first cell (F1) and a hash character to refer to a spill range.
Note: when you add new records, the UNIQUE function automatically extracts new unique list items and Excel automatically updates the drop-down list.
Загрузка...
