Выпадающий список excel двссыл в excel: Двухуровневый выпадающий список в Excel

Как сделать выпадающий список в Excel

18 ноября 2022 Ликбез Технологии

Лайфхакер собрал семь способов: от простых к сложным.

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

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

Поставьте курсор в следующую пустую ячейку диапазона. В нашем примере это B7.

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

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

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

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

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

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

Нажмите OK, и в заданной ячейке появится выпадающий список с нужными значениями.

Как сделать выпадающий список в Excel из именованного диапазона

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

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

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

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

Выпадающий список с элементами из именованного диапазона готов.

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

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

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

Выберите «Список», в качестве источника укажите такую формулу: =ДВССЫЛ("[имя_файла]имя_листа!диапазон") То есть внутри кавычек должно быть название файла с данными для списка в квадратных скобках, потом без пробела название листа, а затем так же без пробела восклицательный знак и обозначение диапазона. В нашем примере будет так: =ДВССЫЛ("[Книга1]Лист1!A3:A9")

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

Как сделать выпадающий список в Excel из таблицы

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

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

Выберите любой вариант дизайна, отметьте в открывшемся окошке опцию «Таблица с заголовками» и нажмите OK.

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

Кликните по ячейке, в которой должен быть список, и нажмите «Проверка данных» на вкладке «Данные».

Укажите «Список» как тип данных, а для источника введите такую формулу: =ДВССЫЛ("имя_таблицы") В нашем примере будет так: =ДВССЫЛ("Двери")

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

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

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

Если вкладка «Разработчик» не отображается, для начала следует включить её. Для этого раскройте меню и выберите «Другие команды…».

Кликните «Настроить ленту», затем включите пункт «Разработчик» и нажмите OK.

На вкладке «Разработчик» кликните «Вставить» и выберите «Поле со списком» из элементов ActiveX.

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

Найдите строку ListFillRange, введите в неё диапазон значений для списка и нажмите Enter. В нашем примере это B6:B9.

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

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

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

Подготовьте столбцы с данными: в шапках пункты первого, родительского списка, а под ними пункты связанного с ним второго. Выделите первый столбец с шапкой и на вкладке «Главная» кликните «Форматировать как таблицу».

Выберите любой из стилей, а затем в появившемся окошке включите опцию «Таблица с заголовками» и нажмите OK.

Кликните по любой из ячеек таблицы, а затем присвойте ей имя, вписав его в поле «Имя таблицы» и нажав Enter. Это будет пункт первого списка, в нашем примере — «Компьютеры». Таким же образом создайте таблицы для остальных столбцов и переименуйте их.

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

Укажите в качестве типа «Список», а в качестве источника выделите шапки столбцов с категориями, которые станут пунктами родительского перечня.

Далее установите курсор в ячейку, в которой будет второй список, кликните «Проверка данных», выберите вариант «Список», а в поле «Источник» впишите =ДВССЫЛ и адрес ячейки родительского списка в скобках. То есть =ДВССЫЛ(h4) для нашего примера.

Теперь при выборе пункта родительского списка в связанном с ним будут отображаться только соответствующие варианты. Например, если нажать на строку «Ноутбуки», то Excel покажет только модели лэптопов.

А если указать «Наушники» — будут отображены соответствующие названия аксессуаров из одноимённого столбца.

Читайте также 🧐

• 10 формул в Excel, которые облегчат вам жизнь
• Как сделать диаграмму в Excel
• Как сделать или убрать разрыв страницы в Word и Excel
• Как в Excel закрепить область при прокрутке
• 12 простых приёмов для ускоренной работы в Excel

Как строить выпадающие списки в Excel

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

Есть много способов, чтобы создавать выпадающие списки в Excel. Рассмотрим некоторые вместе с аналитиком Laba Александром Галабурдой.

Как работает выпадающий список в Excel

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

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

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

Окно делится на 3 вкладки:

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

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

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

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

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

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

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

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

#1. Стандартный

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

Если вам нужно ввести подсказки и настроить вывод сообщения об ошибке, переходим в соответствующие разделы и прописываем необходимые свойства. Затем нажимаем «ОК».

Как видим, при выделении ячейки в диапазоне A2:A25 во вкладке «Проект», у нас появился список значений.

#2. Список с подстановкой данных

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

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

Настроим проверку данных иначе. Для автодополнения списков рассмотрим 2 варианта:

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

Подробнее о том, что такое  «Умные таблицы» и как с ними работать — на наших курсах. 

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

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

В строке с источником прописываем ссылку на столбец таблицы с использованием функции ДВССЫЛ: =ДВССЫЛ(«Товары[Товар]»). Далее отмечаем «Распространить изменения на другие ячейки с тем же условием», и нажимаем «ОК».

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

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

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

Называем будущий список, при необходимости корректируем диапазон значений.

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

Общие рекомендации

• Присваивайте источникам с данными имена. Это позволит пополнять списки новыми данными без изменения формулы в проверке данных.
• Если в вашем отчете содержится несколько списков, выносите источники на отдельный лист. Это практично и не загромождает главную страницу отчета лишними данными.
• Сортируйте список по удобному для вас параметру — в больших массивах это позволит быстро найти нужную позицию.
• Не дублируйте в источниках значения выпадающего списка, иначе у вас будет несколько одинаковых значений.
• По умолчанию в выпадающем списке отображается всего 8 первых значений. Если нужно отобразить больше или реализовать функцию быстрого поиска, используйте элементы управления VBA или ActiveX.

Весь бизнес-контент в удобном формате. Интервью, кейсы, лайфхаки корп. мира — в нашем телеграм-канале. Присоединяйтесь!

Последние материалы

Статья

Чем весенний банкопад в США грозит Украине и миру

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

Читать

Статья

Клиент всегда прав. Или нет?

Когда не стоит уступать клиенту и как тактично указать на его ошибку.

Читать

Статья

«Я хотел системно взглянуть на управление IT-проектами. Выбирал из 10 курсов и остановился на Laba»

Чему научились выпускники курса «Проджект-менеджмент в IT» в Laba.

Читать

Создание раскрывающегося фильтра для извлечения данных на основе выбора

Смотреть видео – Извлечение данных с помощью раскрывающегося списка в Excel

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

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

Обратите внимание: как только я выбираю Индию из раскрывающегося фильтра, извлекаются все записи для Индии.

Загрузите файл примера

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

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

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

Создание уникального списка элементов

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

В приведенном выше примере первым шагом является получение уникального списка всех стран.

Вот шаги для получения уникального списка:

1. Выберите все страны и вставьте его в другую часть рабочего листа.
2. Перейдите в Данные -> Удалить дубликаты.
3. В диалоговом окне «Удалить дубликаты» выберите столбец со списком стран. Это даст вам уникальный список, как показано ниже.

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

См. также : Полное руководство по поиску и удалению дубликатов в Excel.

Создание раскрывающегося фильтра

Вот шаги для создания раскрывающегося списка в ячейке:

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

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

Для этого нам понадобятся вспомогательные столбцы и формулы.

Создать вспомогательные столбцы для извлечения записей для выбранного элемента

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

Это можно сделать с помощью трех вспомогательных столбцов.

Вот шаги для создания вспомогательных столбцов:

• Вспомогательный столбец №1 —  Введите порядковый номер для всех записей (в данном случае 20, для этого можно использовать функцию ROWS()).
• Вспомогательный столбец № 2 — Используйте эту простую функцию функции ЕСЛИ: = ЕСЛИ (D4 = $ H $ 2, E4, «»)
  • Эта формула проверяет, соответствует ли страна в первой строке стране в раскрывающемся меню. Поэтому, если я выбираю Индию, он проверяет, есть ли в первой строке Индия в качестве страны или нет. Если это правда, он возвращает этот номер строки, иначе он возвращает пустой («»). Теперь, когда мы выбираем любую страну, отображаются только те номера строк (во втором вспомогательном столбце), в которых есть выбранная страна. (Например, если выбрана Индия, то это будет выглядеть как на картинке ниже).

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

• . Третий вспомогательный столбец  – Используйте следующую комбинацию функций ЕСЛИОШИБКА и МАЛЕНЬКИЙ:
  =ЕСЛИОШИБКА(МАЛЕНЬКИЙ($F$4:$F$23,E4)»,»)
  

Это даст нам то, что показано на рисунке ниже:

Теперь, когда у нас есть число вместе, нам просто нужно извлечь данные из этого числа. Это можно легко сделать с помощью функции ИНДЕКС (используйте эту формулу в ячейках, где вам нужно извлечь результат):
=ЕСЛИОШИБКА(ИНДЕКС($B$4:$D$23,$G4,СТОЛБЦЫ($J$3:J3)) «»)

Эта формула состоит из 2 частей:
ИНДЕКС  – извлекает данные на основе номера строки
ЕСЛИОШИБКА  – эта функция возвращает пустое значение при отсутствии данных вы наконец получите:

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

Продолжайте. используйте эту технику и произведите впечатление на своего босса и коллег (немного хвастовства никогда не бывает плохо).

Загрузить файл примера

Понравился урок? Дайте мне знать ваши мысли в разделе комментариев.

Вы также можете найти следующие полезные уроки:

• Динамический фильтр Excel — извлечение данных по мере их ввода.
• Динамический поиск в Excel с использованием условного форматирования.
• Создать динамический раскрывающийся список с поисковыми предложениями.
• Как извлечь подстроку в Excel с помощью формул.
• Как отфильтровать ячейки с жирным шрифтом в Excel.

Функция ДВССЫЛ в Excel (объяснение с примерами + видео)

Функция ДВССЫЛ в Excel — обзор

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

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

В этом уроке по Excel я покажу вам, как использовать косвенную функцию в Excel, используя несколько практических примеров.

Но прежде чем я перейду к примерам, давайте сначала посмотрим на его синтаксис.

INDIRECT FUNCTION Синтаксис

 = INDIRECT(ref_text, [a1]) 

Входные аргументы

• ref_text – Текстовая строка, содержащая ссылку на ячейку или именованный диапазон. Это должна быть допустимая ссылка на ячейку, иначе функция вернет ошибку #ССЫЛКА! ошибка
• [a1] – Логическое значение, указывающее, какой тип ссылки использовать для ref text . Это может быть либо ИСТИНА (указывающая ссылку на стиль A1), либо ЛОЖЬ (указывающая ссылку на стиль R1C1). Если опущено, по умолчанию это TRUE.

Дополнительные примечания

• ДВССЫЛ — это изменчивая функция. Это означает, что он пересчитывает каждый раз, когда открывается рабочая книга Excel или когда на листе запускается расчет. Это увеличивает время обработки и замедляет вашу книгу. Хотя вы можете использовать косвенную формулу с небольшими наборами данных, практически не влияя на скорость, вы можете увидеть, что она замедляет работу вашей книги при использовании ее с большими наборами данных
• Текст ссылки (ref_text) может быть:
  • Ссылка на ячейку, которая, в свою очередь, содержит ссылку в формате ссылки в стиле A1 или R1C1.
  • Ссылка на ячейку в двойных кавычках.
  • Именованный диапазон, возвращающий ссылку

Примеры использования косвенной функции в Excel

Теперь давайте углубимся и рассмотрим несколько примеров использования функции ДВССЫЛ в Excel.

Пример 1. Использование ссылки на ячейку для получения значения

Он принимает ссылку на ячейку в виде текстовой строки в качестве входных данных и возвращает значение в этой ссылке (как показано в примере ниже):

Формула в ячейке C1:

 =ДВССЫЛ("A1") 

Приведенная выше формула принимает ссылку на ячейку A1 в качестве входного аргумента (в двойных кавычках в виде текстовой строки) и возвращает значение в этой ячейке, равное 123.

Теперь, если вы думаете, почему бы мне просто не использовать =A1 вместо использования функции ДВССЫЛ у вас правильный вопрос.

Вот почему…

Когда вы используете =A1 или =$A$1 , вы получите тот же результат. Но когда вы вставите строку над первой строкой, вы заметите, что ссылки на ячейки автоматически изменятся для учета новой строки.

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

Пример 2. Использование ссылки на ячейку в ячейке для получения значения

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

В приведенном выше примере ячейка A1 имеет значение 123.

Ячейка C1 содержит ссылку на ячейку A1 (в виде текстовой строки).

Теперь, когда вы используете функцию ДВССЫЛ и используете C1 в качестве аргумента (который, в свою очередь, содержит адрес ячейки в виде текстовой строки), он преобразует значение в ячейке A1 в допустимую ссылку на ячейку.

Это, в свою очередь, означает, что функция будет обращаться к ячейке A1 и возвращать в ней значение.

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

Кроме того, если текстовая строка в ячейке C1 не является допустимой ссылкой на ячейку, косвенная функция вернет ошибку #ССЫЛКА! ошибка.

Пример 3. Создание ссылки с использованием значения в ячейке

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

Например, если ячейка C1 содержит число 2, а вы используете формулу = ДВССЫЛ («A» & C1) , тогда это будет ссылаться на ячейку A2.

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

Если текстовая строка, которую вы используете в формуле, дает ссылку, которую Excel не понимает, она вернет ошибку ссылки (#ССЫЛКА!).

Пример 4. Вычисление суммы диапазона ячеек

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

Например, =ДВССЫЛ(«A1:A5») будет относиться к диапазону A1:A5.

Затем вы можете использовать функцию СУММ, чтобы найти общее значение, или функцию НАИБОЛЬШИЙ/МАЛЕНЬКИЙ/МИН/МАКС, чтобы выполнить другие вычисления.

Как и в случае с функцией СУММ, вы также можете использовать такие функции, как НАИБОЛЬШИЙ, МАКС/МИН, СЧЕТ и т. д.

Пример 5. Создание ссылки на лист с помощью функции ДВССЫЛ

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

Вот что вам нужно знать о ссылках на другие листы:

• Допустим, у вас есть рабочий лист с именем Лист1, и на листе в ячейке A1 у вас есть значение 123. Если вы перейдете на другой лист (скажем, Лист2) и обратитесь к ячейке A1 в Лист1, формула будет выглядеть так:  =Лист1!A1

Но..

• Если у вас есть рабочий лист, содержащий два или более слов (с пробелом между ними), и вы ссылаетесь на ячейку A1 на этом листе с другого листа, формула будет выглядеть так:  = «Набор данных»! A1

В случае нескольких слов Excel автоматически вставляет одинарные кавычки в начале и в конце имени листа.

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

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

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

 =ДВССЫЛ("'Набор данных'!A1") 

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

Если у вас есть имя рабочего листа в ячейке (скажем, A1), вы можете использовать следующую формулу: имя рабочего листа в ячейке A1 и адрес ячейки в ячейке A2, тогда формула будет выглядеть так:

 = ДВССЫЛ ("'"&A1&"'!"&A2) 

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

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

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

Пример 6. Ссылка на именованный диапазон с помощью формулы ДВССЫЛ

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

Например, предположим, что у вас есть оценки 5 учащихся по трем предметам, как показано ниже:

В этом примере давайте назовем ячейки:

• B2:B6: Math
• C2:C6: Физика
• D2:D6: Химия

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

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

 =ДВССЫЛ("Именованный диапазон") 

Например, если вы хотите узнать среднее значение оценок по математике, используйте формулу:

 =СРЗНАЧ(ДВССЫЛ("Математика")) 

Если в ячейке есть именованный диапазон (клавиша F2 в приведенном ниже примере имеет имя «Математика»), вы можете использовать его непосредственно в формуле.

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

Пример 7. Создание зависимого выпадающего списка с помощью функции Excel INDIRECT

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

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

Теперь, чтобы создать зависимый раскрывающийся список, вам нужно создать два именованных диапазона, A2 :A5 с названием США и B2:B5 с названием Индия.

Теперь выберите ячейку D2 и создайте раскрывающийся список для Индии и США. Это будет первый раскрывающийся список, в котором пользователь может выбрать страну.

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

• Выберите ячейку E2 (ячейка, в которой вы хотите получить зависимый раскрывающийся список).
• Перейдите на вкладку «Данные»
• Щелкните Проверка данных.
• Выберите список в качестве критериев проверки и используйте следующую формулу в исходном поле: =ДВССЫЛ($D$2)
• Нажмите кнопку ОК.

Теперь, когда вы вводите США в ячейку D2, раскрывающийся список в ячейке E2 покажет штаты в США.

И когда вы вводите Индию в ячейку D2, раскрывающийся список в ячейке E2 покажет штаты в Индии.

Вот несколько примеров использования функции ДВССЫЛ в Excel. Эти примеры будут работать во всех версиях Excel (Office 365, Excel 2019/2016/2013/2013)

Надеюсь, этот учебник был вам полезен.

Связанные функции Microsoft Excel:

• Функция ВПР Excel.