Как сделать зависимые таблицы в excel?

Создание связи между двумя таблицами в Excel

Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.

Все таблицы в книге указываются в списках полей сводной таблицы и Power View.

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

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

Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.

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

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

Щелкните Данные> Отношения.

Если команда Отношения недоступна, значит книга содержит только одну таблицу.

В окне Управление связями нажмите кнопку Создать.

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

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

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

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

Нажмите кнопку ОК.

Дополнительные сведения о связях между таблицами в Excel

Примечания о связях

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

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

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

Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.

Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.

Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании

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

Нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.

В разделе Price (Цена) нажмите Free (Бесплатно).

В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).

Найдите DateStream и нажмите кнопку Subscribe (Подписаться).

Введите свои учетные данные Майкрософт и нажмите Sign in (Вход). Откроется окно предварительного просмотра данных.

Прокрутите вниз и нажмите Select Query (Запрос на выборку).

Нажмите кнопку Далее.

Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите кнопку Закрыть.

Чтобы импортировать второй набор данных, нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace.

В разделе Type (Тип) нажмите Data Данные).

В разделе Price (Цена) нажмите Free (Бесплатно).

Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).

Прокрутите вниз и нажмите Select Query (Запрос на выборку).

Нажмите кнопку Далее.

Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.

Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.

В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.

Читать еще:  Как сделать штриховку в excel?

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

Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.

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

В списке полей, в разделе «Могут потребоваться связи между таблицами» нажмите Создать.

В поле «Связанная таблица» выберите On_Time_Performance, а в поле «Связанный столбец (первичный ключ)» — FlightDate.

В поле «Таблица» выберитеBasicCalendarUS, а в поле «Столбец (чужой)» — DateKey. Нажмите ОК для создания связи.

Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.

В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.

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

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

Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.

В главной таблице нажмите Сортировка по столбцу.

В поле «Сортировать» выберите MonthInCalendar.

В поле «По» выберите MonthOfYear.

Сводная таблица теперь сортирует каждую комбинацию «месяц и год» (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.

«Могут потребоваться связи между таблицами»

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

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

Шаг 1. Определите, какие таблицы указать в связи

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

Примечание: Можно создавать неоднозначные связи, которые являются недопустимыми при использовании в сводной таблице или отчете Power View. Пусть все ваши таблицы связаны каким-то образом с другими таблицами в модели, но при попытке объединения полей из разных таблиц вы получите сообщение «Могут потребоваться связи между таблицами». Наиболее вероятной причиной является то, что вы столкнулись со связью «многие ко многим». Если вы будете следовать цепочке связей между таблицами, которые подключаются к необходимым для вас таблицам, то вы, вероятно, обнаружите наличие двух или более связей «один ко многим» между таблицами. Не существует простого обходного пути, который бы работал в любой ситуации, но вы можете попробоватьсоздать вычисляемые столбцы, чтобы консолидировать столбцы, которые вы хотите использовать в одной таблице.

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

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

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

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

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

Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.

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

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

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

Доброго времени на fast-wolker.ru! В процессе рутинной работы с таблицами всегда возникает желание ускорить ввод значений. Особенно это касается наименований позиций. Зачастую мы имеем один и тот же список, с которыми постоянно приходится работать, а новые добавляются постепенно.

Каждый раз заносить в таблицу одно и то же название утомительно; к тому же всегда есть возможность ошибки — поставить лишнюю точку, пробел и так далее. Сегодня расскажу о способах, который позволяет в ряде случаев ускорить работу. В самых простых примерах, может что-то подойдет и Вам. Итак…

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

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

Ставим курсор на следующую пустую ячейку под списком кликаем правой кнопкой мыши; из контекстного меню выбираем команду. Или используем сочетание клавиш «Alt+ Стрелка вниз». Такой способ выбора из списка использовать приходиться не слишком часто. Наименования ведь редко повторяются.

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

Как в эксель сделать выпадающий список с другого листа?

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

Выделяем нужную область и в верхней левом верхнем углу присваиваем ей имя «Список_Кондит». У нас есть теперь первый ИМЕНОВАННЫЙ диапазон в нашей книге эксель. Чтобы потом диапазонами управлять, нужно открыть вкладку «Формулы» и найти там «Диспетчер имен»:

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

Следующий шаг — идем обратно в наш лист где создана основная таблица. Выделяем в графе нужные строки. Идем во вкладку «Данные»- «Проверка данных». В строке «тип данных» выбираем «Список»….

А в строке «Источник» копируем адрес нашего диапазона из «диспетчера имен»…

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

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

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

Сначала сделаем в листе «база» наши будущие списки. У нас будет три категории товара — полуфабрикаты, рыба и хлебобулочные. И мы заполним ими три графы.

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

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

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

Ставим курсор в строку из «Полуфабрикаты, затем открываем вкладку «Главная» и жмем на кнопку «Форматировать как таблицу». Дизайн выбираем любой. Задаем по столбцу нужный вертикальный диапазон, включая только строки с наименованиями; ничего кроме диапазона не меняем.

В процессе этих действий нам стал доступен «конструктор таблиц». Обязательно присваиваем нашей( как бы созданной) таблице имя (в левом верхнем углу) «Полуфабрикаты».

Повторяем действо для граф «Рыба» и «Хлебобулочные».

Завешающий этап. Встаем в нужную строку таблицы, как обычно идем в «Данные»-«Проверка данных», выбираем тип «Список» и в качестве диапазона указываем на соседнюю ячейку , т.е на ранее созданный список, но…

… с использованием в «Источнике» функции ДВССЫЛ. «=ДВССЫЛ($Е$6)». Эта функция возвращает ссылку на наши диапазоны т.е. предыдущие списки, а не содержимое ячейки. Мы работаем с данными, в функцию просто их подставили. Наименование вводится вручную.

Обратите внимание на «источник». Указана абсолютная адресация столбца и ячейки (через знаки «$»). Чтобы распространить в таком виде на остальные строки, нужно будет указывать для каждой строки свой источник данных. Это долго. Можно аккуратно убрать абсолютную адресацию руками из «источника» и после скопировать ячейку на остальные строки ниже. Тогда «формула» распространится корректно.

Попробуйте добавить в базу новые позиции; проверьте — появляются ли они теперь автоматически в списке после указании категорий? Должно получиться.

Делаем выпадающий список в ячейке эксель с выбором по первой букве

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

В режиме конструктора вставим в наш документ элемент управления Active X «Поле со списком«:

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

  • ListFillRange — сюда вставляем наш именованный диапазон из «диспетчера имен»;
  • LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент — нужно, если данные ее будут использоваться в других таблицах, формулах и т.д;
  • ListRows — количество отображаемых строк в списке;
  • Font — выбираем шрифт, размер, стиль;
  • ForeColor и BackColor — цвет текста и фона;
  • ListRow — количество возвращаемых строк в списке;
  • PrintObject — определяет — выводить элемент управления на печать или нет (истина или ложь)

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

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

Выпадающие списки в Excel

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

Рад видеть в нашем тематическом сообществе интересные и полезные посты авторов @Petrov210217, @navferty , @zoodes, @FelixJanssen , @zoixnet, @kda2495 , @avvacat , продолжайте в том же духе))

Итак, по просьбе @LordTachanka выкладываю пост, как создать выпадающие списки разными способами.

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

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

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

Плюсом является также то, что можно задать уведомление об ошибке при вводе товара, отсутствующего в списке:

В результате будет выходить такое сообщение:

Удалить выпадающий список можно выбрав в «Тип данных» – «Любое значение».

С помощью макроса (с возможностью добавления новых элементов)

Допустим, что у нас есть список с именами и таблица, куда их нужно вносить.

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

Выделяем весь список (ячейки A1:A7) и нажимаем Ctrl+T или Главная — Форматировать как таблицу, чтобы создать «умную» таблицу:

Теперь создадим именованный диапазон, указывающий на заполненные именами ячейки в нашем списке. Для этого выделим в списке уже только имена без шапки (ячейки A2:A7) и в левой части строки формул (там будет имя таблицы) введём имя для нашего диапазона (например Легенды):

После переименования таблицы нажимаем Enter, в результате слово «Легенды» исчезнет, но диапазон будет создан.

Проверить диапазон можно на вкладке Формулы — Диспетчер имен:

Таким образом, при дописывании новых имен к списку будет расширяться наша «умная» Таблица3, а за ней и наш именованный диапазон Легенды.

Читать еще:  Как сделать товарную накладную в excel?

Теперь создаём выпадающий список в ячейке

Выделяем зелёные ячейки и жмём на вкладке «Данные» кнопку «Проверка данных».

Далее выбираем из выпадающего списка «Тип данных» позицию «Список» и вводим в строку» Источник» ссылку на созданный именованный диапазон =Легенды:

Чтобы Excel позволил нам в будущем ввести в список новые имена, снимем галочки на вкладках «Сообщение для ввода» и «Сообщение об ошибке» и жмём ОК. Выпадающий список готов!

Причем, если, например, вручную дописать новое имя в список в столбце А, то оно автоматически появится в выпадающем списке в любой из зелёных ячеек, поскольку имена берутся из динамического диапазона Легенды:

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

Щёлкаем правой кнопкой мыши по ярлыку нашего листа и выбираем «Просмотреть код». В модуле листа в редакторе Visual Basic, скопируйте код (не забудьте заменить название диапазона и ячейки ввода, также можно изменить вопрос при добавлении:

Private Sub Worksheet_Change(ByVal Target As Range)

Set p = Range(«Легенды»)

If Target.Cells.Count > 1 Then Exit Sub

If IsEmpty(Target) Then Exit Sub

If Not Intersect(Target, Range(«D2:D11»)) Is Nothing Then

If WorksheetFunction.CountIf(p, Target) = 0 Then

r = MsgBox(«Добавить новое имя в справочник?», vbYesNo)

If r = vbYes Then p.Cells(p.Rows.Count + 1) = Target

Теперь при попытке ввести новое имя в любую из зелёных ячеек Excel будет спрашивать:

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

Связанные (зависимые) выпадающие списки

Способ 1. Функция ДВССЫЛ (INDIRECT)

Этот фокус основан на применении функции ДВССЫЛ (INDIRECT), которая умеет делать одну простую вещь — преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. То есть, если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Маша», то функция выдаст ссылку на именованный диапазон с именем Маша и т.д. Такой, своего рода, «перевод стрелок» 😉

Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и Nissan:

Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota. В Excel 2003 и старше — это можно сделать в меню Вставка — Имя — Присвоить (Insert — Name — Define). В Excel 2007 и новее — на вкладке Формулы (Formulas) с помощью Диспетчера имен (Name Manager). Затем повторим то же самое со списками Форд и Ниссан, задав соответственно имена диапазонам Ford и Nissan.

При задании имен помните о том, что имена диапазонов в Excel не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например Ssang Yong), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т.е. Ssang_Yong).

Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку и откройте меню Данные — Проверка (Data — Validation) или нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data) если у вас Excel 2007 или новее. Затем из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и в поле Источник (Source) выделите ячейки с названиями марок (желтые ячейки в нашем примере). После нажатия на ОК первый выпадающий список готов:

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

где F3 — адрес ячейки с первым выпадающим списком (замените на свой).

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

Минусы такого способа:

  • В качестве вторичных (зависимых) диапазонов не могут выступать динамические диапазоны задаваемые формулами типа СМЕЩ (OFFSET). Для первичного (независимого) списка их использовать можно, а вот вторичный список должен быть определен жестко, без формул. Однако, это ограничение можно обойти, создав отсортированный список соответствий марка-модель (см. Способ 2).
  • Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нем есть текст с пробелами, то придется их заменять на подчеркивания с помощью функции ПОДСТАВИТЬ (SUBSTITUTE), т.е. формула будет выглядеть как =ДВССЫЛ(ПОДСТАВИТЬ(F3;» «;»_»))
  • Надо руками создавать много именованных диапазонов (если у нас много марок автомобилей).

Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)

Этот способ требует наличия отсортированного списка соответствий марка-модель вот такого вида:

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

  • дать имя диапазону D1:D3 (например Марки) с помощью Диспетчера имен (Name Manager) с вкладки Формулы (Formulas) или в старых версиях Excel — через меню Вставка — Имя — Присвоить (Insert — Name — Define)
  • выбрать на вкладке Данные (Data) команду Проверка данных (Data validation)
  • выбрать из выпадающего списка вариант проверки Список (List) и указать в качестве Источника (Source)=Марки или просто выделить ячейки D1:D3 (если они на том же листе, где список).

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

  • Нажмите Ctrl+F3 или воспользуйтесь кнопкой Диспетчер имен (Name manager) на вкладке Формулы (Formulas). В версиях до 2003 это была команда меню Вставка — Имя — Присвоить (Insert — Name — Define)
  • Создайте новый именованный диапазон с любым именем (например Модели) и в поле Ссылка (Reference) в нижней части окна введите руками следующую формулу:

Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов — не пугайтесь 🙂

Функция СМЕЩ (OFFSET) умеет выдавать ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. В более понятном варианте синтаксис этой функции таков:

=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках; размер_диапазона_в_столбцах)

  • начальная ячейка — берем первую ячейку нашего списка, т.е. А1
  • сдвиг_вниз — нам считает функция ПОИСКПОЗ (MATCH), которая, попросту говоря, выдает порядковый номер ячейки с выбранной маркой (G7) в заданном диапазоне (столбце А)
  • сдвиг_вправо = 1, т.к. мы хотим сослаться на модели в соседнем столбце (В)
  • размер_диапазона_в_строках — вычисляем с помощью функции СЧЕТЕСЛИ (COUNTIF), которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений — марок авто (G7)
  • размер_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с моделями

В итоге должно получиться что-то вроде этого:

Осталось добавить выпадающий список на основе созданной формулы к ячейке G8. Для этого:

  • выделяем ячейку G8
  • выбираем на вкладке Данные (Data) команду Проверка данных (Data validation) или в меню Данные — Проверка (Data — Validation)
  • из выпадающего списка выбираем вариант проверки Список (List) и вводим в качестве Источника (Source) знак равно и имя нашего диапазона, т.е. =Модели
Ссылка на основную публикацию
Adblock
detector