Вложенный список в excel

Добавляем значения в выпадающий список – динамический список

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

Выделяем диапазон ячеек – D1:D8, затем на вкладке «Главная» нажимаем «Форматировать как таблицу» и выбираем любой стиль.

Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками».

Вверху пишем заголовок таблицы – «Сотрудники», и заполняем ее данными.

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

Теперь добавим новое имя сотрудника в наш список: Ира. В выпадающем списке оно появилось. Если мы удалим любое имя из таблицы, из списка оно тоже удалится.

Основные требования

Вам понадобятся Power Pivot и Power Query (получение и преобразование) для выполнения нескольких задач при создании модели данных Excel . Вот как вы можете получить эти функции в своей книге Excel:

Как получить Power Pivot

1. Excel 2010: вам необходимо загрузить надстройку Power Pivot от Microsoft, а затем установить ее для своей программы Excel на свой компьютер.

2. Excel 2013: выпуск Excel 2013 Office профессиональный плюс включает Power Pivot. Но вам необходимо активировать его перед первым использованием. Вот как:

1. Щелкните Файл на ленте книги Excel.
2. Затем щелкните Параметры, чтобы открыть Параметры Excel .
3. Теперь нажмите « Надстройки» .
4. Выберите надстройки COM , щелкнув раскрывающееся меню в поле « Управление» .
5. Нажмите « Перейти» и установите флажок для Microsoft Power Pivot для Excel .

3. Excel 2016 и более поздние версии: на ленте вы найдете меню Power Pivot.

Как получить Power Query (получить и преобразовать)

1. Excel 2010: вы можете загрузить надстройку Power Query от Microsoft . После установки Power Query появится на ленте .

2. Excel 2013: вам необходимо активировать Power Query, выполнив те же действия, которые вы только что сделали, чтобы сделать Power Pivot работоспособным в Excel 2013.

3. Excel 2016 и более поздние версии вы можете найти Power Query (получение и преобразование), перейдя на вкладку « Данные » на ленте Excel.

Как связать ячейки из разных файлов Excel

ПþÃÂõÃÂàÃÂòÃÂ÷ÃÂòðýøàüõöôàÃÂð÷ûøÃÂýÃÂüø ÃÂðùûðüø Excel (øûø ÃÂðñþÃÂøüø úýøóðüø) ÿÃÂðúÃÂøÃÂõÃÂúø ÃÂðúþù öõ, úðú ÃÂúð÷ðýþ òÃÂÃÂõ. ÃÂð øÃÂúûÃÂÃÂõýøõü ÃÂûÃÂÃÂðõò, úþóôð òàòÃÂÃÂðòûÃÂõÃÂõ ÃÂÃÂõùúø, òÃÂÃÂðòÃÂÃÂõ øàò ôÃÂÃÂóÃÂàÃÂûõúÃÂÃÂþýýÃÂàÃÂðñûøÃÂàòüõÃÂÃÂþ ôÃÂÃÂóþù òúûðôúø. ÃÂþàúðú ÃÂÃÂþ ÃÂôõûðÃÂàò 4 ÿÃÂþÃÂÃÂÃÂàÃÂðóð.

• ÃÂÃÂúÃÂþùÃÂõ þñð ôþúÃÂüõýÃÂð Excel.
• ÃÂþ òÃÂþÃÂþü ÃÂðùûõ (ÃÂðÃÂÃÂõàÃÂûÃÂöñàÿþôôõÃÂöúø), òÃÂñõÃÂøÃÂõ ÃÂÃÂõùúàø òòõôøÃÂõ ÃÂøüòþû ÃÂðòõýÃÂÃÂòð ÷ýðú ÃÂðòýþ
• ÃÂõÃÂõúûÃÂÃÂøÃÂÃÂÃÂàýð þÃÂøóøýðûÃÂýÃÂù ÃÂðùû (ÃÂýÃÂõÃÂýõàTech áþòõÃÂÃÂ) ø ýðöüøÃÂõ ýð ÃÂÃÂõùúÃÂ, ýð úþÃÂþÃÂÃÂàÃÂþÃÂøÃÂõ ÃÂþÃÂûðÃÂÃÂÃÂÃÂ.
• ÃÂðöüøÃÂõ ÃÂþùÃÂø ÷ðòõÃÂÃÂøÃÂàÃÂþÃÂüÃÂûÃÂ.

âõÿõÃÂàÃÂþÃÂüÃÂûð ôûàÃÂòÃÂ÷ðýýþù ÃÂÃÂõùúø ÃÂðúöõ øüõõàôÃÂÃÂóþõ øüàúýøóø ò úòðôÃÂðÃÂýÃÂàÃÂúþñúðÃÂ.

ÃÂÃÂûø òà÷ðúÃÂþõÃÂõ øÃÂÃÂþôýÃÂù ÃÂðùû Excel ø ÃÂýþòð ÿþÃÂüþÃÂÃÂøÃÂõ ýð ÃÂþÃÂüÃÂûÃÂ, òàÃÂòøôøÃÂõ, ÃÂÃÂþ ÃÂõÿõÃÂàþý ÃÂðúöõ øüõõàüõÃÂÃÂþÿþûþöõýøõ òÃÂõóþ ôþúÃÂüõýÃÂð. íÃÂþ þ÷ýðÃÂðõÃÂ, ÃÂÃÂþ õÃÂûø òàÿõÃÂõüõÃÂÃÂøÃÂõ øÃÂÃÂþôýÃÂù ÃÂðùû, úþÃÂþÃÂÃÂù òàÃÂúð÷ðûø ò ôÃÂÃÂóþü üõÃÂÃÂõ, øûø ÿõÃÂõøüõýÃÂõÃÂõ õóþ, ÃÂÃÂÃÂûúø ÿõÃÂõÃÂÃÂðýÃÂàÃÂðñþÃÂðÃÂÃÂ. ÃÂþàÿþÃÂõüàýðôõöýõõ ÃÂÃÂðýøÃÂàòÃÂõ òðöýÃÂõ ôðýýÃÂõ ò þôýþü ÃÂðùûõ Excel.

Второй способ создания двухуровнего списка

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

ВАЖНО!
Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается

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

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

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

• Ссылка
  в нашем случае — $A$1
  — верхний левый угол исходной таблицы;
• Смещ_по_строкам
  — ПОИСКПОЗ(F3;$A$1:$A$67;0)-1
  — номер строки со значением искомой группы (в моем случае страны ячейка F3
  ) минус единица;
• Cмещ_по_столбцам
  — 1
  — так как нам необходим столбец с подгруппами (городами);
• — СЧЁТЕСЛИ($A$1:$A$67;F3)
  — количество подгрупп в искомой группе (количество городов в стране F3
  );
• — 1
  — так как это ширина нашего столбца с подгруппами.

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

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

Построение взаимосвязей между таблицами в модели данных

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

Функция представления диаграммы в Power Pivot позволяет перетаскивать эти поля для построения взаимосвязи. Выполните следующие действия, чтобы создать ссылки на таблицы в модели данных Excel:

1. На ленте книги Excel щелкните меню Power Pivot .

2. Теперь нажмите « Управление» в разделе « Модель данных ». Вы увидите редактор Power Pivot, как показано ниже:

3. Нажмите кнопку « Просмотр диаграммы» , расположенную в разделе « Просмотр » на вкладке « Главная страница Power Pivot». Вы увидите заголовки столбцов таблицы, сгруппированные по имени таблицы.

4. Теперь вы сможете перетаскивать уникальный идентификатор поля из одной таблицы в другую. Ниже представлена ​​схема отношений между четырьмя таблицами модели данных Excel:

Ниже описывается связь между таблицами:

• Стол студенты | Студенческий билет к таблице Оценки | Студенческий билет
• Таблица семестров | Идентификатор семестра в таблицу Оценки | Семестр
• Табличные классы | Номер класса в таблицу Оценки | ID класса

5. Вы можете создавать отношения, выбирая пару столбцов с уникальными значениями. Если есть дубликаты, вы увидите следующую ошибку:

6. Вы заметите звезду (*) с одной стороны и одну (1) с другой в виде диаграммы отношений. Он определяет, что между таблицами существует связь «один ко многим».

7. В редакторе Power Pivot щелкните вкладку « Дизайн » и выберите « Управление взаимосвязями», чтобы узнать, в каких полях выполняются подключения.

Создаем зависимые выпадающие списки

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

Первый называем «Имя», второй – «Фамилия», третий – «Отч».

Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники».

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

В поле «Тип данных» выберите «Список», в поле источник – или введите «=Сотрудники», или выделите диапазон ячеек, которому присвоено имя.

Первый выпадающий список создан. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя», во втором отобразится список имен, если выберем «Фамилия» – список фамилий.

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

По такому принципу можно делать зависимые выпадающие списки.

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

Теперь Вы знаете, как сделать раскрывающийся список в Excel.

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

Спасибо, всё получилось.

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

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

Создание модели данных путем импорта данных в книгу Excel

Для этого руководства вы можете получить предварительно отформатированные образцы данных от Microsoft:

Скачать : образец данных о студентах (только данные) | Пример данных о студентах (полная модель)

Вы можете импортировать базу данных с несколькими связанными таблицами из многих источников, таких как книги Excel, Microsoft Access, веб-сайты, SQL Server и т. Д. Затем вам нужно отформатировать набор данных, чтобы Excel мог его использовать. Вот шаги, которые вы можете попробовать:

1. В Excel 2016 и более поздних версиях щелкните вкладку « Данные » и выберите « Новый запрос» .

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

3. При использовании версии Excel 2013 щелкните Power Query на ленте, а затем выберите « Получить внешние данные», чтобы выбрать данные для импорта.

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

5. Щелкните Загрузить, чтобы завершить процесс импорта.

6. Excel создаст для вас модель данных, используя эти таблицы. Вы можете увидеть заголовки столбцов таблицы в списках полей сводной таблицы .

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

1. Отформатируйте данные в табличной модели, выделив все ячейки, содержащие данные, и нажмите Ctrl + T.

2. Теперь выберите всю таблицу и щелкните вкладку Power Pivot на ленте .

3. В разделе « Таблицы » нажмите « Добавить в модель данных» .

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

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