Adelitusn.ru

ПК и Техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Разбор функции ДВССЫЛ (INDIRECT) на примерах

Разбор функции ДВССЫЛ (INDIRECT) на примерах

Excel – просто! Ищем значение на пересечении

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

1. ИНДЕКС (INDEX)

Данная функция позволяет извлечь значение из диапазона ячеек по номеру строки и столбца.

Аргументы функции выглядят следующим образом:

Excel – просто. Ищем значение на пересечении

Массив – ссылка на диапазон ячеек, в котором нам нужно осуществить поиск;

Номер_строки – строка, из которой нужно извлечь значения;

Номер_столбца – столбец, из которого нужно извлечь значение.

Функция ИНДЕКС также может извлекать значение из разных выделенных областей, но в данной статье я не буду останавливаться на этой возможности.

Схематически работу функции можно изобразить так:

Excel – просто. Ищем значение на пересечении

Для автоматического поиска строки/столбца функцию ИНДЕКС, как правило, дополняют двумя функциями ПОИСКПОЗ (MATCH).

В таком случае формула выглядит примерно так:

=ИНДЕКС($C$7:$F$9;ПОИСКПОЗ($I$5;$B$7:$B$9;0);ПОИСКПОЗ($I$6;$C$6:$F$6;0))

Excel – просто. Ищем значение на пересечении

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

2. СУММПРОИЗВ (SUMPRODUCT)

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

Excel – просто. Ищем значение на пересечении

Для решения нашей задачи формула будет выглядеть так:

=СУММПРОИЗВ((C6:F6=I6)*(B7:B9=I5)*(C7:F9))

Простыми словами синтаксис функции можно представить так:

=СУММПРОИЗВ((условие_1)*(условие_2)*(что_суммировать))

Excel – просто. Ищем значение на пересечении

Сумма умножений 0, 1 и значений таблицы извлечёт нам искомое значение.

3. ДВССЫЛ (INDIRECT)

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

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

Excel – просто. Ищем значение на пересечении

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

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

Для начала нам необходимо создать диапазоны для строк и столбцов.

Выделяем столбцы с заголовками и нажимаем комбинацию клавиш Ctrl+Shift+F3

Excel – просто. Ищем значение на пересечении

Аналогичную операцию проделываем и для строк.

Читайте так же:
Как извлечь страницы из ПДФ-файла и сохранить их

Excel – просто. Ищем значение на пересечении

Сама формула будет иметь такой вид:

=ДВССЫЛ(I5) ДВССЫЛ(I6)

Обратите внимание что функции разделены пробелом.

Excel – просто. Ищем значение на пересечении

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

Выпадающий список с данными из другого файла

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

ДВССЫЛ (INDIRECT),

чтобы сформировать правильную ссылку на внешний файл.

Например, если необходимо создать выпадающий список с содержимым ячеек А1:А10 с листа Список из файла Toeapbi.xls, нужно открыть окно проверки данных через меню или вкладку Данные — Проверка (Data — Validation) данных и в поле Источник (Source) ввести следующую конструкцию:

=ДВССЫЛ(«[Товары.х18]Список!$А$1:$А$10»)

Двойная ссылка

Рис. 18. Двойная ссылка

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

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

=ДВССЫЛ(«’С:ТЕМР[Товары.х18]Список’!$А$1:$А$10»)

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

Минус всей этой системы только один — выпадающий список будет корректно работать только в том случае, если файл Toeapbi.xls открыт.

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

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

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

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

Читайте так же:
Как получить стикеры в ВК

Ari st о ____________

Camry 1 ‘m mi riant

Рис. 19. Заготовка для списка

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

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

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

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

=ДВССЫЛ(ЕЗ),

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

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

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

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

Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. То есть если в нем есть текст с пробелами, то придется их заменять на подчеркивания с помощью функции ПОДСТАВИТЬ), т. е. формула будет выглядеть как =ДВССЫЛ(ПОДСТАВИТЬ(РЗ;» «;»_»)).

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

Способ 2. Список соответствий и функции

Читайте так же:
HP LaserJet M1522nf MFP driver

СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)

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

Трюк №25. Как в Excel при проверке данных заставить Excel использовать список на другом рабочем листе

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

Заставить Excel при проверке данных ссылаться на список на другом рабочем листе можно двумя способами — при помощи именованных диапазонов и функции ДВССЫЛ (INDIRECT).

Способ 1. Именованные диапазоны

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

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

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

Предположим, список находится на листе Sheet1 в диапазоне $А$1:$А$10 . Щелкните любую ячейку на другом рабочем листе, где должен будет появиться этот список проверки. Затем выберите команду Данные → Проверка (Data → Validation) и в поле Тип данных (Allow) выберите пункт Список (List). В поле Источник (Source) введите следующую функцию: =INDIRECT(«Sheet1!$А$1:$А$10») , в русской версии Excel: =ДВССЫЛ(«Sheet1!$А$1:$А$10») . Убедитесь, что флажок Список допустимых значений (In-Cell) установлен, и щелкните на кнопке ОК. Список на листе Sheetl окажется в вашем раскрывающемся списке проверки.

Если имя рабочего листа, на котором расположен список, содержит пробелы, функцию ДВССЫЛ (INDIRECT) нужно записать так: =INDIRECT(«‘Sheet 1’!$А$1:$А$10») , в русской версии Excel: =ДВССЫЛ(«Sheet 1!$А$1:$А$10») . Различие заключается в том, что здесь после первой кавычки стоит один апостроф, а второй апостроф находится перед восклицательным знаком. Апострофы ограничивают название листа.

Читайте так же:
Как настроить и зайти в роутер Apple: настройка интернета и Wi-Fi

Преимущества и недостатки обоих методов

У именованных диапазонов и функции ДВССЫЛ (INDIRECT) при использовании их для связи со списком на другом рабочем листе есть преимущества и недостатки.

Преимущество использования именованного диапазона в данном сценарии заключается в том, что изменение названия листа не повлияет на список проверки. nЭто подчеркивает недостаток функции ДВССЫЛ (INDIRECT) — а именно, любое изменение названия листа не будет автоматически отражаться в функции ДВССЫЛ (INDIRECT), поэтому придется вручную изменить функцию, указав новое название листа.

Преимущество функции ДВССЫЛ (INDIRECT): если из именованного диапазона будет удалена первая ячейка или строка либо последняя ячейка или строка, то именованный диапазон вернет ошибку #REF! . В этом недостаток именованных диапазонов — если удалить ячейки или строки из именованного диапазона, эти изменения не повлияют на список проверки.

Связанные (зависимые) выпадающие списки в MS Excel. Описание и примеры.

Рассмотрим, что такое связанные (зависимые) выпадающие списки в MS Excel, на примере. У нас есть Таблица №2. В ней есть три столбца с названием видов товаров: Конфеты, Печенья и Торты. В каждом из этих столбцов, есть непосредственно перечень самих этих товаров.

Пример

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

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

Сначала Задаем имя диапазону ячеек, в которых находятся название столбцов. В нашем примере это ячейки С4;В4;Е4 (Конфеты, Печенья и Торты). Назовём данный диапазон: Товары.

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

Читайте так же:
PDF24 Creator для Windows

В закладке Данные, находим кнопку Проверка данных.

Проверка данных

Нажимаем на нее. Появляется диалоговое окно Проверка вводимых значений, в которое вносим данные:

Тип данных. Выбираем вариант Список

Источник. Здесь после знака равно «=» пишем наше Заданное имя. В нашем примере: =Товары.

Связанные выпадающие списки в Excel

Нажимаем ОК. У нас появляется выпадающий список с названием видом товаров.

Связанные выпадающие списки в Excel

Теперь создадим второй выпадающий список.

Сначала присвоим Заданное имя ячейкам, которые содержат название товаров. Нам нужно Задать имя диапазону С5:С14 (Конфеты А, Конфеты Б …), D5:D14 (Печень А, Печенье Б …), Е5:Е14 (Торт А, Торт Б …). Заданные имена должны соответствовать названию столбцов из первого выпадающего списка. Заданное имя для диапазон С5:С14 (Конфеты А, Конфеты Б …) должно быть Конфеты, для диапазона D5:D14 (Печень А, Печенье Б …) – Печенье, для диапазона Е5:Е14 (Торт А, Торт Б …) – Торты.

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

Вызываем функцию выпадающий список и в диалоговом окне Проверка вводимых значений вносим данные:

Тип данных. Выбираем вариант Список

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

Выглядит это вот так: =ДВССЫЛ(G4)

Связанные выпадающие списки в Excel

Связанные выпадающие списки в Excel

Нажимаем ОК. Появляется второй выпадающий список. Перечень названий, в котором будет зависеть от того, что выбрано в первом выпадающем списке. У нас получились связанные (зависимые) выпадающие списки в MS Excel.

Данный алгоритм так же будет работать, если исходные данные (Таблица №2) и выпадающие списки расположены на разных листах книги.

Обратите внимание.

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

Информацию о том, как работает функция Задать имя в Excel, Вы можете найти в этой статье: Функция Задать имя в Excel. Описание и примеры.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector