Excel список адресов ячеек - Word и Excel - помощь в работе с программами

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

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

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

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

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

Вручную через «точку-с-запятой» в поле «Источник».

Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.

Назначить имя для диапазона значений и в поле источник вписать это имя.

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

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

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

Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».

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

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

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

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

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

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

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

Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.

Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.

Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).

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

Сохраняем, установив тип файла «с поддержкой макросов».

Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

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

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

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

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

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

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

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

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

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

Создадим первый выпадающий список, куда войдут названия диапазонов.

Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.

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

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

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

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

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

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

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

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

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

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

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

На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).

Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.

Жмем «Свойства» – открывается перечень настроек.

Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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

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

Источник

Download Article

If you’re wondering how to create a multiple-line list in a single cell in Microsoft Excel, you’ve come to the right place. Whether you want a cell to contain a bulleted list with line breaks, a numbered list, or a drop-down list, inserting a list is easy once you know where to look. This wikiHow will teach you three helpful ways to insert any type of list to one cell in Excel.

1

Double-click the cell you want to edit. If you want to create a bullet or numerical list in a single cell with each item on its own line, start by double-clicking the cell into which you want to type the list.
2
Insert a bullet point (optional). If you want to preface each list item with a bullet rather than a number or other character, you can use a key shortcut to insert the bullet symbol. Here’s how:
- Mac: Press Option + 8.
- Windows:
  - If you have a numeric keypad on the side of your keyboard, hold down the Alt key while pressing 7 on the keypad.^[1]
  - If not, click the Insert menu, select Symbol, type 2022 into the «Character code» box at the bottom, and then click Insert.
  - If 2022 didn’t bring up a bullet point, select the Wingdings font instead, and then enter 159 as the character code. You can then click Insert to add the bullet point.
Advertisement
3
Type your first list item. Don’t press Enter or Return after typing.
- If you want your list to be numbered, preface the first list item with 1. or 1).
4

Press Alt+↵ Enter (PC) or Control+⌥ Option+⏎ Return on a Mac. This adds a line break so you can start typing on the next line of the same cell.^[2]
5

Type the remaining list items. To continue your list, just enter another bullet point on the second line, type the list item, and press Alt + Enter or Control + Option + Return to open a new line. When you’re finished, you can click anywhere else on your sheet to exit the cell.

1
Create your list in another app. If you’re trying to paste a bullet list (or other type of list) into a single cell rather than have it spread across multiple cells, there’s a trick to pasting the list. Start by creating your list in an app like Word, TextEdit, or Notepad.
- If you create a bulleted list in Word, the bullets will copy over to your cell when pasted into Excel. Bullets may not copy from other apps.
2

Copy the list. To do this, just highlight the list, right-click the highlighted area, and then select Copy.
3

Double-click a cell in Excel. Double-clicking the cell before pasting makes it so the list items will all appear in the same cell.
4

Right-click the cell. The context menu will expand.
5

Click the clipboard icon under «Paste Options.» The icon has a clipboard and a black rectangle. This pastes the list into the cell you double-clicked. Each list item will appear on its own line within the same cell.

1

Open the workbook in which you want to create a drop-down list. If you want to be able to click a cell to view and select from a drop-down list, you can create a list with Excel’s data validation tool.^[3]
2
Create a new worksheet in the workbook. You can do this by clicking the + next to the existing workbook sheets at the bottom of Excel. This worksheet is where you’ll enter the items that you want to appear in your drop-down list.
- After you create the list on a separate sheet and add it to a table, you’ll be able to create a drop-down list containing the list data in any cell you want.
3
Type each list item into a single column. Enter every possible list choice into its own separate cell. The items you type will all be available in the drop-down list.
- If you plan to make a lot of drop-down menus and want to use this same sheet to create all of them, add a header to the top of the list. For example, if you’re making a list of cities, you could type City into the first cell. This header won’t actually appear on the drop-down list you create—it’s just for organization on this sheet that contains list data.
4

Highlight the entire table and press Ctrl+T. Include the header at the top of the list when highlighting. This opens the Create Table dialog.
5
Choose a header option and click OK. If you added a header to the top of your list, check the box next to «My table has headers.» If not, make sure there is no checkmark there before clicking OK.
- Now that your list is in a table, you can make changes to it after creating your drop-down list, and your drop-down list will update automatically.
6

Sort the list alphabetically. This will keep your list organized once you add it to your sheet. To do this, just click the arrow next to your header cell and select Sort A to Z.
7

Click the cell on the worksheet in which you want to add the list. This can be any cell on any worksheet in the workbook.
8

Type a name for the list into the cell. This is the cell where the list will appear, so give it a name that indicates the type of option you should choose from that list. For example, if you made a list of cities, you could type City here.
9

Click the Data tab and select Data Validation. Make sure the cell is selected before doing this. If you don’t see Data Validation in the toolbar, click the icon in the «Data Tools» section that has two black rectangles with a green checkmark and a red circle with a line through it. This opens the Data Validation window.
10

Click the «Allow» menu and select List. Additional options will expand.
11

Click the up-arrow in the «Source» field. This minimizes the Data Validation window so you can select your list data.
12

Select the list (without the header) and press ↵ Enter or ⏎ Return. Click back over to the tab that has your list data and drag the mouse cursor over just the list items. Pressing Enter or Return will add the range to the «Source» field.
13

Click OK. The selected cell now has a drop-down list. If you need to add or remove items from the list, you can simply make those changes on your new worksheet and they’ll automatically propagate to the list.

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Thanks for submitting a tip for review!

About This Article

Article SummaryX

1. Double-click the cell.
2. Press Alt + 7 or Option + 8 to add a bullet point.
3. Type a list item.
4. Press Alt + Enter (PC) or Control + Option + Return (Mac) to go to the next line.
5. Repeat until your list is finished.

Did this summary help you?

Thanks to all authors for creating a page that has been read 62,222 times.

Is this article up to date?

Источник

Содержание

1 Как нам это может пригодиться?
2 Как работает выпадающий список в Excel
- 2.1 #1. Параметры
- 2.2 #2. Подсказка по вводу
- 2.3 #3. Сообщение об ошибке
3 Выпадающий список через контекстное меню
4 Создание списка с применением инструментов разработчика
5 1 — Самый быстрый способ.
- 5.1 2 — Используем меню.
- 5.2 3 — Создаем элемент управления.
- 5.3 4 — Элемент ActiveX
6 Как сделать в Экселе выпадающий список: 3 способа
- 6.1 1 Применение комбинации клавиш
- 6.2 2 Использование меню
- 6.3 3 Создание нового элемента управления
  - 6.3.1 Excel 2007-2013
  - 6.3.2 Excel Online
  - 6.3.3 Excel для Mac 2011
7 Как убрать (удалить) выпадающий список в Excel
8 Пробелы в названии при создании связанного выпадающего списка
9 Пример выпадающего списка в Excel
10 Зависимые раскрывающиеся списки
11 Создание дополнительного списка
12 Связанный список

Как нам это может пригодиться?

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

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

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

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

Чтобы создать такой выпадающий список, перейдите в раздел «Данные» на панели инструментов, в группе «Работа с данными» выберите пункт «Проверка данных».

Далее всплывает окно «Проверка вводимых значений».

#1. Параметры

Здесь задаются основные параметры выпадающего списка в Excel:

Тип данных. Можно выбрать тип данных, который будет содержать список: диапазон целых или действительных чисел, текстовые выражения, даты и время. Можно задать ограничения по длине текста и различные формулы.
Игнорировать пустые значения — данный пункт означает, что Excel не будет проверять на правильность ячейки, в которых содержатся пустые значения.
Список допустимых значений. Этот флажок отображается только в том случае, если выбран тип данных «Список». Если убрать флажок, в ячейке будет происходить проверка на соответствие значений списку, но раскрывающее поле с выпадающими значениями будет отсутствовать.
Значение. Работает только с теми типами данных, в которых можно задать ограничения по числам или датам.
Источник. Здесь перечисляются значения для проверки данных или задается формула.
Распространить изменения на другие ячейки с тем же условием. Excel здесь находит все ячейки в книге, которые ссылаются на идентичное по свойствам условие и изменяет их согласно новых параметров. В случае, если флажок не будет установлен, условие будет изменено только для выделенных ячеек в таблице.
Очистить все — удаляет установленную проверку данных с выделенных ячеек.

#2. Подсказка по вводу

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

#3. Сообщение об ошибке

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

Выпадающий список через контекстное меню

Смотрите также: “Как из Экселя перенести таблицу в Ворд”

Самым простой и понятный метод, при котором нужно предварительно создать список в другом месте документа. Можно разместить его рядом с таблицей, либо создать новый лист и составить перечень там, чтобы не “засорять” исходный документ лишними элементами и данными.

Во вспомогательной таблице пишем перечень всех наименований – каждый с новой строки в отдельной ячейке. В итоге должен получиться один столбец с заполненными данными.
Затем отмечаем все эти ячейки, нажимаем в любом месте отмеченного диапазона правой кнопкой мыши и в открывшемся списке кликаем по функции “Присвоить имя..”.
На экране появится окно “Создание имени”. Называем список так, как хочется, но с условием – первым символом должна быть буква, также не допускается использование определенных символов. Здесь же предусмотрена возможность добавления списку примечания в соответствующем текстовом поле. По готовности нажимаем OK.
Переключаемся во вкладку “Данные” в основном окне программы. Отмечаем группу ячеек, для которых хотим задать выбор из нашего списка и нажимаем на значок “Проверка данных” в подразделе “Работа с данными”.
На экране появится окно “Проверка вводимых значений”. Находясь во вкладке “Параметры” в типе данных останавливаемся на опции “Список”. В текстовом поле “Источник” пишем знак “равно” (“=”) и название только что созданного списка. В нашем случае – “=Наименование”. Нажимаем OK.
Все готово. Справа от каждой ячейки выбранного диапазона появится небольшой значок со стрелкой вниз, нажав на которую можно открыть перечень наименований, который мы заранее составили. Щелкнув по нужному варианту из списка, он сразу же будет вставлен в ячейку. Кроме того, значение в ячейке теперь может соответствовать только наименованию из списка, что исключит любые возможные опечатки.

Создание списка с применением инструментов разработчика

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

В первую очередь, эти инструменты нужно найти и активировать, так как по умолчанию они выключены. Переходим в меню “Файл”.
В перечне слева находим в самом низу пункт “Параметры” и щелкаем по нему.
Переходим в раздел “Настроить ленту” и в области “Основные вкладки” ставим галочку напротив пункта “Разработчик”. Инструменты разработчика будут добавлены на ленту программы. Кликаем OK, чтобы сохранить настройки.
Теперь в программе есть новая вкладка под названием “Разработчик”. Через нее мы и будем работать. Сначала создаем столбец с элементами, которые будут источниками значений для нашего выпадающего списка.
Переключаемся во вкладу “Разработчик”. В подразделе “Элементы управления” нажимаем на кнопку “Вставить”. В открывшемся перечне в блоке функций “Элементы ActiveX” кликаем по значку “Поле со списком”.
Далее нажимаем на нужную ячейку, после чего появится окно со списком. Настраиваем его размеры по границам ячейки. Если список выделен мышкой, на панели инструментов будет активен “Режим конструктора”. Нажимаем на кнопку “Свойства”, чтобы продолжить настройку списка.
В открывшихся параметрах находим строку “ListFillRange”. В столбце рядом через двоеточие пишем координаты диапазона ячеек, составляющих наш ранее созданный список. Закрываем окно с параметрами, щелкнув на крестик.
Затем кликаем правой кнопкой мыши по окну списка, далее – по пункту “Объект ComboBox” и выбираем “Edit”.
В результате мы получаем выпадающий список с заранее определенным перечнем.
Чтобы вставить его в несколько ячеек, наводим курсор на правый нижний угол ячейки со списком, и как только он поменяет вид на крестик, зажимаем левую кнопку мыши и тянем вниз до самой нижней строки, в которой нам нужен подобный список.

1 — Самый быстрый способ.

Как проще всего добавить выпадающий список? Всего один щелчок правой кнопкой мыши по пустой клетке под столбцом с данными, затем команда контекстного меню «Выберите из раскрывающегося списка» (Choose from drop-down list). А можно просто стать в нужное место и нажать сочетание клавиш Alt+стрелка вниз. Появится отсортированный перечень уникальных ранее введенных значений.
Способ не работает, если нашу ячейку и столбец с записями отделяет хотя бы одна пустая строка или вы хотите ввести то, что еще не вводилось выше. На нашем примере это хорошо видно.

2 — Используем меню.

Давайте рассмотрим небольшой пример, в котором нам нужно постоянно вводить в таблицу одни и те же наименования товаров. Выпишите в столбик данные, которые мы будем использовать (например, названия товаров). В нашем примере — в диапазон G2:G7.

Выделите ячейку таблицы (можно сразу несколько), в которых хотите использовать ввод из заранее определенного перечня. Далее в главном меню выберите на вкладке Данные – Проверка… (Data – Validation). Далее нажмите пункт Тип данных (Allow) и выберите вариант Список (List). Поставьте курсор в поле Источник (Source) и впишите в него адреса с эталонными значениями элементов — в нашем случае G2:G7. Рекомендуется также использовать здесь абсолютные ссылки (для их установки нажмите клавишу F4).

Бонусом здесь идет возможность задать подсказку и сообщение об ошибке, если автоматически вставленное значение вы захотите изменить вручную. Для этого существуют вкладки Подсказка по вводу (Input Message) и Сообщение об ошибке (Error Alert).

В качестве источника можно использовать также и именованный диапазон.

К примеру, диапазону I2:I13, содержащему названия месяцев, можно присвоить наименование «месяцы». Затем имя можно ввести в поле «Источник».

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

Но вы можете и не использовать диапазоны или ссылки, а просто определить возможные варианты прямо в поле «Источник».

3 — Создаем элемент управления.

Вставим на лист новый объект – элемент управления «Поле со списком» с последующей привязкой его к данным на листе Excel. Делаем:

Откройте вкладку Разработчик (Developer). Если её не видно, то в Excel 2007 нужно нажать кнопку Офис – Параметры – флажок Отображать вкладку Разработчик на ленте (Office Button – Options – Show Developer Tab in the Ribbon) или в версии 2010–2013 щелкните правой кнопкой мыши по ленте, выберите команду Настройка ленты (Customize Ribbon) и включите отображение вкладки Разработчик (Developer) с помощью флажка.
Найдите нужный значок среди элементов управления (см.рисунок ниже).

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

Но нам ведь нужен не этот номер, а соответствующее ему слово. Используем функцию ИНДЕКС (INDEX в английском варианте). Она позволяет найти в списке значений одно из них соответственно его порядковому номеру. В качестве аргументов ИНДЕКС укажите диапазон ячеек (F5:F11) и адрес с полученным порядковым номером (F2).

Формулу в F3 запишем, как показано на рисунке:

=ИНДЕКС(F5:F11;F2)

Как и в предыдущем способе, здесь возможны ссылки на другие листы, на именованные диапазоны.

Обратите также внимание, что здесь мы не привязаны ни к какой конкретному месту таблицы. Таким списком удобно пользоваться, поскольку его можно свободно «перетаскивать» мышкой в любое удобное место. Для этого на вкладке «Разработчик» нужно активизировать режим конструктора.

4 — Элемент ActiveX

Действуем аналогично предыдущему способу, но выбираем иконку чуть ниже — из раздела «Элементы ActiveX».

Определяем перечень допустимых значений (1). Обратите внимание, что здесь для показа можно выбирать сразу несколько колонок. Затем выбираем адрес, по которому будет вставлена нужная позиция из перечня (2).Указываем количество столбцов, которые будут использованы как исходные данные (3), и номер столбца, из которого будет происходить выбор для вставки на лист (4). Если укажете номер столбца 2, то в А5 будет вставлена не фамилия, а должность. Можно также указать количество строк, которое будет выведено в перечне. По умолчанию — 8. Остальные можно прокручивать мышкой (5).

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

Как сделать в Экселе выпадающий список: 3 способа

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

1 Применение комбинации клавиш

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

Выбрать ячейку под списком, в котором содержатся его значения.
Нажать сочетание клавиш «Alt» + «стрелка вниз».
Выбрать нужное значение.

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

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

2 Использование меню

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

Выписать в столбик данные, которые будут использоваться в списке. Это может быть, например, количество ядер и потоков для процессора.
Выбрать ячейку, в которой будет находиться нужный объект.
Перейти на вкладку «Данные».
Найти и выбрать функцию «Проверка данных».
Выбрать в поле типа данных пункт «Список».
Указать в источнике адреса уже введенных значений. Или ввести их самостоятельно, через запятую. В этом случае экономится место в таблице, но повышается риск сделать ошибку еще на этапе создания списка. И изменять значения будет сложнее.

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

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

Автоматическое заполнение, скорее всего, будет некорректным. Но при выборе значений вручную они будут выбираться из нужного диапазона — это становится возможным благодаря знакам доллара перед каждой частью адреса источника ($I$6: $I$8).

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

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

3 Создание нового элемента управления

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

Включить режим разработчика. Для это следует кликнуть правой кнопкой мыши по ленте Excel, открыть настройки и поставить галочку напротив соответствующего пункта.
Перейти к появившейся новой вкладке. В меню «Вставить» выбрать поле со списком.
Кликнуть по любому месту таблицы. После того, как там появилось нужное поле, открыть его контекстное меню и выбрать «Формат объекта». Здесь следует указать связь с ячейкой, в которой будет находиться номер значения в списке (48/96 — второй, поэтому в ячейке G2 находится «2»). А еще — диапазон, откуда берутся данные.

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

Способ является сравнительно сложным, и его не стоит рассматривать в качестве основного. Зато после создания готового выпадающего списка в Экселе его можно будет расположить в любом месте таблицы. Для перетаскивания придется включить режим «Конструктора» на вкладке «Разработчик» и использовать правую кнопку мыши.

Excel 2007-2013

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

Выберите ячейки, в которой должен отображаться список.
На ленте на вкладке «Данные» щелкните «Проверка данных».
На вкладке «Параметры» в поле «Тип данных» выберите пункт «Список».
Щелкните в поле «Источник» и введите текст или числа (разделенные запятыми), которые должны появиться в списке.
Чтобы закрыть диалоговое окно, в щелкните «ОК».

Excel Online

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

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

Excel для Mac 2011

Выберите ячейки, в которой должен отображаться список.
На вкладке «Данные» в разделе «Инструменты» щелкните «Проверить».
Щелкните вкладку «Параметры», а затем во всплывающем меню «Разрешить» выберите пункт «Список».
Щелкните в поле «Источник» и введите текст или числа (разделенные запятыми), которые должны появиться в списке.
Чтобы закрыть диалоговое окно, в щелкните «ОК».

Как убрать (удалить) выпадающий список в Excel

Откройте окно настройки выпадающего списка и выберите «Любое значение» в разделе «Тип данных».

Ненужный элемент исчезнет.

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

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

Именуя диапазон значений, важно использовать вместо пробела нижнее подчеркивание «_». Например, «Мясные_блюда». Однако есть вероятность упустить данное правило в одном из названий, и в результате будет ошибка при выполнении операции. Можно в самих названиях использовать обычный пробел, а в момент подстановки в список, поменять его на подчеркивание, используя формулу ПОДСТАВИТЬ:

=ПОДСТАВИТЬ(F3;» «;»_») без кавычек.

Итоговая формула выглядит так:

=ДВССЫЛ(ПОДСТАВИТЬ($F$3;» «;»_»)) без кавычек.

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

=ДВССЫЛ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($F$3);» «;»_»)) без кавычек.

Пример выпадающего списка в Excel

В этом случае у Источника в значении стоит небольшая кнопка «Вниз», на которую можно кликнуть.

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

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

Зависимые раскрывающиеся списки

Чтобы создать зависимые выпадающие списки, следуйте дальнейшей пошаговой инструкции:

Для начала вам нужно создать именованный диапазон. Перейдите во вкладку «Формулы», затем выберите «Диспетчер имён» и «Создать».
Выделяем диапазон ячеек со значением, открываем вкладку «Формулы», нажимаем «Диспетчер имен»

Нажимаем «Создать»
Введите имя и диапазон, затем нажмите «ОК». Если вы перед этим выделяли нужные ячейки мышкой, то диапазон будет указан автоматически. Также примите к сведению, что имя диапазона должно быть уникальным и не должно содержать знаков препинания с пробелами.

Пишем имя «Деревья», нажимаем «ОК»
По такой же методике сделайте столько именованных диапазонов, сколько логических зависимостей хотите создать. В данном примере это ещё два диапазона: «Кустарники» и «Травы».

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

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

Результат выпадающего связанного списка

Теперь вам известны несколько способов, как создать выпадающее меню в Excel-таблице. Это может пригодиться вам в ряде случаев, а особенно – в проверке данных. Выбор правильного способа должен осуществляться в зависимости от того, с каким типом таблицы вы работаете. Если это «одноразовая» таблица, то подойдёт первый способ – он быстрый и лёгкий. Если же таблица требует постоянных изменений, то лучше всего будет воспользоваться тем способом, который включает в себя создание «умных таблиц» и именованных диапазонов.

Создание дополнительного списка

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

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

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

Открывается форма создания имени. В поле «Имя» вписываем любое удобное наименование, по которому будем узнавать данный список. Но, это наименование должно начинаться обязательно с буквы. Можно также вписать примечание, но это не обязательно. Жмем на кнопку «OK».

Переходим во вкладку «Данные» программы Microsoft Excel. Выделяем область таблицы, где собираемся применять выпадающий список. Жмем на кнопку «Проверка данных», расположенную на Ленте.

Открывается окно проверки вводимых значений. Во вкладке «Параметры» в поле «Тип данных» выбираем параметр «Список». В поле «Источник» ставим знак равно, и сразу без пробелов пишем имя списка, которое присвоили ему выше. Жмем на кнопку «OK».

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

Связанный список

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

Для этого нужно подготовить как минимум три столбца. В первом будут заполнены наименования товаров, а во втором и третьем – их возможные единицы измерения. Столбцов с возможными вариациями единиц измерения может быть и больше.
Сначала создаем один общий список для всех наименований продуктов, выделив все строки столбца “Наименование”, через контекстное меню выделенного диапазона.
Задаем ему имя, например, “Питание”.
Затем таким же образом формируем отдельные списки для каждого продукта с соответствующими единицами измерения. Для большей наглядности возьмем в качестве примера первую позицию – “Лук”. Отмечаем ячейки, содержащие все единицы измерения для этого продукта, через контекстное меню присваиваем имя, которое полностью должно совпадать с наименованием.
Таким же образом создаем отдельные списки для всех остальных продуктов в нашем перечне.
После этого вставляем общий список с продуктами в верхнюю ячейку первого столбца основной таблицы – как и в описанном выше примере, через кнопку “Проверка данных” (вкладка “Данные”).
В качестве источника указываем “=Питание” (согласно нашему названию).
Затем кликаем по верхней ячейке столбца с единицами измерения, также заходим в окно проверки данных и в источнике указываем формулу “=ДВССЫЛ(A2)“, где A2 – номер ячейки с соответствующим продуктом.
Списки готовы. Осталось его только растянуть их все строки таблицы, как для столбца A, так и для столбца B.

Источники

https://mister-office.ru/funktsii-excel/dropdown-list-auto-excel.html
https://l-a-b-a.com/blog/1715-kak-stroit-vypadayushchie-spiski-v-excel
https://MicroExcel.ru/vypadayushhie-spiski/
https://ichip.ru/sovety/ekspluataciya/kak-sdelat-v-eksele-vypadayushchij-spisok-3-sposoba-762462
https://support.microsoft.com/ru-ru/office/%D1%81%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%B8%D0%B5-%D1%80%D0%B0%D1%81%D0%BA%D1%80%D1%8B%D0%B2%D0%B0%D1%8E%D1%89%D0%B5%D0%B3%D0%BE%D1%81%D1%8F-%D1%81%D0%BF%D0%B8%D1%81%D0%BA%D0%B0-%D0%B2-excel-0a5b17bf-a950-e676-d759-131d9c9de9a4
https://WindowsTips.ru/vypadayushhij-spisok-v-excel
https://sysadmin-note.ru/article/kak-v-excel-sdelat-vypadayushhij-spisok/
https://SeoPulses.ru/kak-sdelat-vipadayushiy-spisok-excel/
https://pc-consultant.ru/rabota-na-komp-yutere/vypadajushhij-spisok-v-excel/
https://lumpics.ru/how-to-make-a-dropdown-list-in-excel/

Источник

Выпадающий список уникальных значений. Автоматическое обновление выпадающего списка

Введение

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

Рассмотрим особенности создания выпадающих списков на примере:

Исходные данные:

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

Задача:

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

Мы будем двигаться поэтапно, уделяя внимание всем возможностям данного инструмента.

Скачать файлы из этой статьи

Рабочие файлы

Обзорное видео о работе с выпадающими списками в Excel и Google таблицах смотрите ниже. Приятного просмотра!

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

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

Выпадающий список готов!

Такой способ позволяет представить обычный диапазон в виде выпадающего списка. Повторы данных остались в списке (в диапазоне A2:A16 названия городов повторяются и в выпадающем списке они также повторяются). Это, конечно, не удобно. О том, как сделать выпадающий список уникальных значений в Excel мы поговорим далее, пока остановимся на этом варианте.

Как создать зависимый выпадающий список в Excel?

Существует несколько вариантов. Один из них, это сочетание именованных диапазонов и функции ДВССЫЛ.

Именованный диапазон в Excel – это ячейка (или диапазон ячеек), которой присвоено имя.

Функция ДВССЫЛ в Excel преобразовывает текст в ссылку.

Способ 1: именованные диапазоны + функция `ДВССЫЛ`

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

Алгоритм создания именованного диапазона: выделяем диапазон, далее «Формулы» – «Задать имя».

У нас получится 5 именованных диапазона: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

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

Поэтому, вместо дефисов в названии города Ростов-на-Дону мы укажем допустимый символ – нижнее подчеркивание.

Именованные диапазоны готовы.

Теперь выбираем ячейку для второго выпадающего списка, того, который будет зависимым. Переходим к инструменту «Проверка данных», тип данных – «Список». В поле «Источник» указываем функцию: =ДВССЫЛ(D2), где D2 – это адрес ячейки с первым выпадающим списком городов.

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

Зависимый выпадающий список адресов готов.

Меняя значения в ячейке D2, меняются списки в ячейке E2. За исключением города Ростов-на-Дону. В выпадающем списке городов (ячейка D2), в названии используется дефис, а в именованном диапазоне – нижнее подчеркивание.

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

Функция ПОДСТАВИТЬ заменяет определенный текст в текстовой строке на новое значение. Вместо: =ДВССЫЛ(D2) укажем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;"-";"_"))

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

Теперь зависимый выпадающий список работает и для города, содержащего в названии дефисы – Ростов-на-Дону. Вернемся к выпадающему списку городов.

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

Для начала создадим из диапазона данных «умную» таблицу Excel. Сделать это можно сочетанием клавиш Ctrl+T.

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

Как сделать выпадающий список уникальных значений в Excel?

Надоело смотреть на повторяющиеся названия городов в выпадающем списке. Реализуем выпадающий список так, чтобы названия городов в нем не повторялись. Для этого, добавим слева вспомогательный столбец. Мы дали ему название – «Уникальные».

И включим новый столбец в диапазон «умной» таблицы. «Конструктор» – «Размер таблицы». Вместо =$B$1:$C$17 указываем: =$A$1:$C$17

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

В ячейку А2 добавим формулу массива, которая будет формировать список уникальных городов:

=ЕСЛИОШИБКА(ИНДЕКС([Город];ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$1:A1; [Город]);0));"")

Чтобы Excel воспринял нашу формулу, как формулу массива, жмем Ctrl + Shift + Enter.

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

Из списка уникальных городов создадим именованный диапазон (мы назвали его — «Уникальные»), который затем используем в качестве источника для выпадающего списка городов.

«Проверка данных» – «Список». В источнике данных, вместо предыдущего диапазона с названиями городов =$B$2:$B$18, задаем имя – =Уникальные

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

Чтобы их убрать, доработаем именованный диапазон «Уникальные». В диспетчере имен, вместо диапазона =Таблица1[Уникальные] используем: =СМЕЩ(Лист1!$A$2;0;0;СЧЁТЗ(Таблица1[Уникальные])-СЧИТАТЬПУСТОТЫ(Таблица1[Уникальные]))

где: Лист1!$A$2 – ячейка со значением первого пункта списка уникальных значений

Таблица1[Уникальные] – столбец с перечнем всех пунктов списка

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

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

Как сделать автоматически обновляемый зависимый список? Способ 2: `СМЕЩ+ПОИСКПОЗ+СЧЁТЕСЛИ`

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

В ячейку F2 (зависимый выпадающий список адресов) вместо: =ДВССЫЛ(ПОДСТАВИТЬ(E2;"-";"_")) вставляем: =СМЕЩ($B$2;ПОИСКПОЗ(E2;$B$2:$B$18;0)-1;1;СЧЁТЕСЛИ($B$2:$B$18;E2);1)

Для корректной работы этого способа, данные в столбце с городом должны быть отсортированы. Функция СМЕЩ будет динамически ссылаться только на ячейки адресов определенного города.

Аргументы функции:

Ссылка – берем первую ячейку нашего списка, т.е. $B$2

Смещение по строкам – считает функция ПОИСКПОЗ, которая выдает порядковый номер ячейки с выбранным городом (E2) в заданном диапазоне ($B$2:$B$18)

Смещение по столбцам = 1, т.к. мы хотим сослаться на адреса в соседнем столбце (С)

Высота – вычисляем с помощью функции СЧЁТЕСЛИ, которая подсчитывает количество встретившихся в диапазоне ($B$2:$B$18) нужных нам значений – названий городов (E2)

Ширина = 1, т.к. нам нужен один столбец с адресами

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

С выпадающими списками в Google таблицах все немного иначе.

Выпадающий список в Google таблицах

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

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

«Данные» – «Настроить проверку данных» – «Значение из диапазона»

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

Зависимый выпадающий список в Google таблицах

Возвращаемся к двум основным способам, которые мы рассмотрели в Excel.

Способ 1: именованные диапазоны + `ДВССЫЛ`

Создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

Выделяем ячейки – «Данные» – «Настроить именованные диапазоны»

Указываем имя и жмем готово. У нас получится 5 именованных диапазонов: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

Также, как и в Excel, в Google таблицах к именам диапазонов есть список требований.

Поэтому, вместо дефисов в названии города Ростов-на-Дону укажем допустимый символ – нижнее подчеркивание.

В Google таблицах мы не сможем подобно Excel задать функцию ДВССЫЛ в инструменте «Проверка данных». Поэтому, разместим результат функции ДВССЫЛ в пустых ячейках правее. Не забываем добавить обработку значений от дефисов функцией ПОДСТАВИТЬ. Подробнее о том, для чего это нужно, мы говорили ранее в примере Excel.

В ячейке F1 введем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;"_";"-"))

Последний штрих в создании зависимого выпадающего списка, в разделе «Настроить проверку данных», в качестве диапазона указываем список из столбца F:F.

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

Как автоматически обновить выпадающий список в Google таблицах при добавлении новых данных?

В выпадающем списке городов, достаточно расширить диапазон и вместо =$A$2:$A$16 указать: =$A$2:$A. Теперь при добавлении нового города он автоматически появляется в выпадающем списке.

Как автоматически обновить зависимый выпадающий список в Google таблицах при добавлении новых данных?

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

В ячейке G6 укажем:

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

Важно: для корректной работы этого способа, данные в столбце с городом должны быть отсортированы от А до Я, или от Я до А. Подробнее о том, как в данном случае работает функция СМЕЩ читайте выше в примере с Excel.

Заключительным этапом поместим результат функции СМЕЩ в диапазон выпадающего списка.

Скроем вспомогательные столбцы для удобства.

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

Заключение

Теперь Вам известны несколько способов, как создать выпадающие списки в Excel и Google таблицах. Смотрите примеры и создавайте нужные Вам выпадающие списки.

Изучить работу в программе Excel Вы можете на наших курсах: бесплатные онлайн-курсы по Excel

Пройдите бесплатный тест на нашем сайте, чтобы объективно оценить свой уровень владения инструментами и функциями программы Excel: пройти бесплатный тест

У нас Вы можете заказать выполнение задач по MS Excel и Google таблицам

Источник

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

Пошаговая инструкция, как сделать список внутри ячейки

Перед формированием перечислений в окошках таблицы необходимо ее правильно отформатировать. С этого и начнем алгоритм.

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

Затем в появившемся окне выбираем формат будущего списка. Во вкладке «Число» в списке «Числовые форматы» выбираем «Текстовый». Чтобы подтвердить действия, нажимаем на кнопку «ОК».

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

Далее набираем текст в формате списка. Для этого начинаем запись со знака маркировки или порядкового номера. Когда заполнили первую строчку, зажмите клавишу Alt и Enter. Это поможет перенестись на следующую строку в этой же ячейке. В противном случае для ввода следующей строки активируется новая ячейка.

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

Важно!

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

Здесь же предоставляется возможность удобной настройки для корректного отображения текста на печати.

Тест по теме «Как в Microsoft Excel сделать в одной ячейке»

Источник

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

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

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

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

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

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

About This Article

Is this article up to date?

Как нам это может пригодиться?

#1. Параметры

#2. Подсказка по вводу

#3. Сообщение об ошибке

Выпадающий список через контекстное меню

Создание списка с применением инструментов разработчика

1 — Самый быстрый способ.

2 — Используем меню.

3 — Создаем элемент управления.

4 — Элемент ActiveX

Как сделать в Экселе выпадающий список: 3 способа

1 Применение комбинации клавиш

2 Использование меню

3 Создание нового элемента управления

Excel 2007-2013

Excel Online

Excel для Mac 2011

Как убрать (удалить) выпадающий список в Excel

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

Пример выпадающего списка в Excel

Зависимые раскрывающиеся списки

Создание дополнительного списка

Связанный список

Выпадающий список уникальных значений. Автоматическое обновление выпадающего списка

Введение

Скачать файлы из этой статьи

Как создать зависимый выпадающий список в Excel?

Способ 1: именованные диапазоны + функция ДВССЫЛ

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

Как сделать выпадающий список уникальных значений в Excel?

Как сделать автоматически обновляемый зависимый список? Способ 2: СМЕЩ+ПОИСКПОЗ+СЧЁТЕСЛИ

Аргументы функции:

Выпадающий список в Google таблицах

Зависимый выпадающий список в Google таблицах

Способ 1: именованные диапазоны + ДВССЫЛ

Как автоматически обновить выпадающий список в Google таблицах при добавлении новых данных?

Как автоматически обновить зависимый выпадающий список в Google таблицах при добавлении новых данных?

Заключение

У нас Вы можете заказать выполнение задач по MS Excel и Google таблицам

Пошаговая инструкция, как сделать список внутри ячейки

Тест по теме «Как в Microsoft Excel сделать в одной ячейке»

А вот еще интересные статьи:

Способ 1: именованные диапазоны + функция `ДВССЫЛ`

Как сделать автоматически обновляемый зависимый список? Способ 2: `СМЕЩ+ПОИСКПОЗ+СЧЁТЕСЛИ`

Способ 1: именованные диапазоны + `ДВССЫЛ`