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

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

Вынудить Excel при проверке данных ссылаться на перечень на другом рабочем листе можно 2-мя методами — с помощью именованных диапазонов и функции ДВССЫЛ (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») . Различие заключается в том, что тут опосля первой кавычки стоит один апостроф, а 2-ой апостроф находится перед восклицательным знаком. Апострофы ограничивают заглавие листа.

Достоинства и недочеты обоих способов

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

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

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

Функция ДВССЫЛ в Excel с примерами использования

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

Синтаксис функции с описанием

Ссылка на текст. Неотклонимый аргумент, содержащий ссылку на ячейку в формате текстовой строчки. К примеру, =ДВССЫЛ («А1») либо =ДВССЫЛ («Лист 2!А3»).
А1 – логическое значение для определения типа ссылки. Необязательный аргумент. Если имеет значение ИСТИНА либо опущен, то ссылка на текст воспринимается как ссылка типа А1 (адресок ячейки указан очевидно). Значение ЛОЖЬ – как ссылка в стиле R1C1 (ссылка на ячейку с формулой, отсылающей к иной ячейке).

Почему при работе функции ДВССЫЛ появляются ошибки:

Значение аргумента «ссылка на ячейку» является недопустимой ссылкой – ошибка #ССЫЛКА!.
Неотклонимый аргумент ссылается на другую книжку (является наружной ссылкой), которая недосягаема (закрыта) – ошибка #ССЫЛКА!. При разработке схожих формул наружный источник данных должен быть открыт.
Значение неотклонимого аргумента ссылается на превосходящий предел спектр – ошибка #ССЫЛКА!. Функция может обработать до 1048576 строк либо 16384 столбцов.

Описание

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

Что возвращает функция

Функция возвращает ссылку, заданную текстовой строчкой.

Microsoft Excel

трюки • приёмы • решения

Функция ГПР

Горизонтальное 1-ое равенство. Отыскивает совпадение по ключу в первой строке определенного спектра и возвращает значение из обозначенной строчки этого спектра в совпавшем с ключом столбце.

Синтаксис: =ГПР(ключ; спектр; номер_строки; [интервальный_просмотр]).

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

Функция ДВССЫЛ в Excel: примеры

Начнем с хрестоматийного примера, чтоб осознать механизм работы функции.

Имеется таблица с данными:

Примеры функции ДВССЫЛ:

Разглядим практическое применение функции. На листах 1, 2, 3, 4 и 5 в одних и тех же ячейках размещены однотипные данные (информация о образовании служащих конторы за крайние 5 лет).

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

Пишем формулу в ячейке В4 и копируем ее на всю таблицу (вниз и на право). Данные с 5 разных листов собираются в итоговую таблицу.

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

Чтоб получить лишь нечетные записи, используем формулу:

Для выведения четных строк:

Примечание. Функция СИМВОЛ возвращает знак по данному коду. Код 65 выводит английскую буковку A. 66 – B. 67 – С.

Допустим, у юзера есть некоторое количество источников данных (в нашем примере – несколько отчетов). Необходимо вывести количество служащих, основываясь на 2-ух аспектах: «Год» и «Образование». Для поиска определенного значения в базе данных подступает функция ВПР.

Чтоб функция сработала, все отчеты поместим на один лист.

Интересно почитать: Что такое в excel функция впр

Но ВПР информацию в таком виде не сумеет переработать. Потому любому отчету мы дали имя (сделали именованные спектры). Раздельно сделали выпадающие списки: «Год», «Образование». В перечне «Год» – наименования именованных диапазонов.

Задачка: при выбирании года и образования в столбце «Количество» обязано появляться число служащих.

Если мы используем лишь функцию ВПР, покажется ошибка:

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

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

Синтаксис

Аргументы функции ДВССЫЛ описаны ниже.

Ссылка_на_текст — неотклонимый аргумент. Ссылка на ячейку, которая содержит ссылку в стиле А1 либо R1C1, имя, определенное как ссылка, либо ссылку на ячейку в виде текстовой строчки. Если значение аргумента “ссылка_на_текст” не является допустимой ссылкой, функция ДВССЫЛ возвращает значение ошибки #ССЫЛКА!.

Если значение аргумента “ссылка_на_ячейку” является ссылкой на другую книжку (наружной ссылкой), иная книжка обязана быть открыта. В неприятном случае функция ДВССЫЛ возвращает значение ошибки #ССЫЛКА!. Примечание.Поддержка наружных ссылок в Excel Web App отсутствует.

Если значение аргумента “ссылка_на_текст” является ссылкой на спектр ячеек, превосходящий предельное число строк 1 048 576 либо столбцов 16 384 (XFD), функция ДВССЫЛ возвращает значение ошибки #ССЫЛКА!.

Примечание. Этот итог различается от результата, получаемого в наиболее ранешних версиях приложения Excel, чем Microsoft Office Excel 2007, в которых превышение предела игнорируется и ворачивается значение.

A1 — необязательный аргумент. Логическое значение, определяющее тип ссылки, содержащейся в ячейке “ссылка_на_текст”.

Если аргумент “a1” имеет значение ИСТИНА либо опущен, “ссылка_на_ячейку” интерпретируется как ссылка в стиле A1.

Если аргумент “a1” имеет значение ЛОЖЬ, “ссылка_на_ячейку” интерпретируется как ссылка в стиле R1C1.

2. Транспонирование данных при помощи функции ДВССЫЛ (INDIRECT)

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

ДВССЫЛ в данном случае не является традиционным решением задачки, но тоже с ней управится. Для этого нам необходимо прописать такую формулу, которая при горизонтальном протягивании будет всякий раз ссылаться на ячейку ниже. Можно было бы прописывать руками номер строчки, но в примере ниже мы воспользуемся функцией СТОЛБЕЦ (COLUMN), которая возвратит номер столбца текущей ячейки. В моем примере они совпадут – данные размещены комфортно, чтоб не перегружать формулу излишними вычислениями для вашей наглядности (исходник начинается со 2й строчки, транспонирую в спектр, который начинается со 2го столбца). На практике может потребоваться скорректировать приобретенный итог фунции СТОЛБЕЦ, к примеру, вычитанием различия.

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

Присваивание объекта переменной

В Excel VBA объект быть может присвоен переменной с помощью главного слова Set:

Активный объект

В хоть какой момент времени в Excel есть активный объект Workbook – это рабочая книжка, открытая в этот момент. Буквально так же существует активный объект Worksheet, активный объект Range и так дальше.

Сослаться на активный объект Workbook либо Sheet в коде VBA можно как на ActiveWorkbook либо ActiveSheet, а на активный объект Range – как на Selection.

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

Таковым образом, чтоб сослаться на спектр A1:B10 на активном рабочем листе активной книжки, можно записать просто:

Смена активного объекта

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

Способы объектов, в том числе использованные лишь что способы Activate либо Select, дальше будут рассмотрены наиболее тщательно.

Примеры

Информация о студентах, вынесенная на отдельный лист в таблице.

Возвращает содержимое по ссылке (как для ячейки, так и для спектра).

Эта информация оказалась полезной?

Как можно сделать лучше эту статью?

Создаем не сдвигающуюся исходную ссылку

В первом примере в столбцах C и E находятся схожие числа, их суммы, посчитанные с помощью функции SUM (СУММ), тоже схожи. Тем не наименее, формулы незначительно различаются. В ячейке C8 формула вот таковая:

В ячейке E8 функция INDIRECT (ДВССЫЛ) создаёт ссылку на исходную ячейку E2:

Если вверху листа вставить строчку и добавить значение для января (Jan), то сумма в столбце C не поменяется. Поменяется формула, отреагировав на прибавление строчки:

Но, функция INDIRECT (ДВССЫЛ) фиксирует E2 как исходную ячейку, потому значение января автоматом врубается в подсчёт суммы по столбцу E. Конечная ячейка поменялась, но на исходную это не воздействовало.

Функция ДВССЫЛ в Excel с примерами использования

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

Синтаксис функции с описанием

Ссылка на текст. Неотклонимый аргумент, содержащий ссылку на ячейку в формате текстовой строчки. К примеру, =ДВССЫЛ («А1») либо =ДВССЫЛ («Лист 2!А3»).
А1 – логическое значение для определения типа ссылки. Необязательный аргумент. Если имеет значение ИСТИНА либо опущен, то ссылка на текст воспринимается как ссылка типа А1 (адресок ячейки указан очевидно). Значение ЛОЖЬ – как ссылка в стиле R1C1 (ссылка на ячейку с формулой, отсылающей к иной ячейке).

Почему при работе функции ДВССЫЛ появляются ошибки:

Значение аргумента «ссылка на ячейку» является недопустимой ссылкой – ошибка #ССЫЛКА!.
Неотклонимый аргумент ссылается на другую книжку (является наружной ссылкой), которая недосягаема (закрыта) – ошибка #ССЫЛКА!. При разработке схожих формул наружный источник данных должен быть открыт.
Значение неотклонимого аргумента ссылается на превосходящий предел спектр – ошибка #ССЫЛКА!. Функция может обработать до 1048576 строк либо 16384 столбцов.

Описание

1. Традиционный метод внедрения функции ДВССЫЛ (INDIRECT)

Имеем таблицу с наименованием городов и численностью их населения. Задачка: сослаться на ячейку с численностью – пусть это будет ячейка. B2. Наши деяния:

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

Интересно почитать: Функция в excel наименьший

Excel. Пример прямого использования функции ДВССЫЛ (INDIRECT)

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

Функция 30: INDIRECT (ДВССЫЛ)

Функция INDIRECT (ДВССЫЛ) возвращает ссылку, заданную текстовой строчкой.

Как можно применять функцию INDIRECT (ДВССЫЛ)?

Так как функция INDIRECT (ДВССЫЛ) возвращает ссылку, заданную текстовой строчкой, то с её помощью Вы можете:

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

Синтаксис INDIRECT (ДВССЫЛ)

Функция INDIRECT (ДВССЫЛ) имеет вот таковой синтаксис:

ref_text (ссылка_на_ячейку) – это текст ссылки.
a1 – если равен TRUE (ИСТИНА) либо не указан, то будет применен стиль ссылки A1; а если FALSE (ЛОЖЬ), то стиль R1C1.

Ловушки INDIRECT (ДВССЫЛ)

Функция INDIRECT (ДВССЫЛ) пересчитывается при любом изменении значений на листе Excel. Это может очень замедлить работу Вашей книжки, если функция употребляется в почти всех формулах.
Если функция INDIRECT (ДВССЫЛ) создаёт ссылку на другую книжку Excel, то эта книжка обязана быть открыта, по другому формула скажет о ошибке #REF! (#ССЫЛКА!).
Если функция INDIRECT (ДВССЫЛ) создаёт ссылку на спектр, превосходящий предельное число строк и столбцов, то формула скажет о ошибке #REF! (#ССЫЛКА!).
Функция INDIRECT (ДВССЫЛ) не может сделать ссылку на динамический именованный спектр.

Функция ВПР

Вертикальное 1-ое равенство. Отыскивает совпадение по ключу в первом столбце определенного спектра и возвращает значение из обозначенного столбца этого спектра в совпавшей с ключом строке.

Синтаксис: =ВПР(ключ; спектр; номер_столбца; [интервальный_просмотр]), где

ключ – неотклонимый аргумент. Разыскиваемое значение, для которого нужно возвратить значение.
спектр – неотклонимый аргумент. Таблица, в которой нужно отыскать значение по ключу. 1-ый столбец таблицы (спектра) должен содержать значение совпадающее с ключом, по другому будет возвращена ошибка #Н/Д.
номер_столбца – неотклонимый аргумент. Порядковый номер столбца в обозначенном спектре из которого нужно вернуть значение в случае совпадения ключа.
интервальный_просмотр – необязательный аргумент. Логическое значение указывающее тип просмотра:
- ЛОЖЬ – функция отыскивает четкое совпадение по первому столбцу таблицы. Если может быть несколько совпадений, то возвращено будет самое 1-ое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
- ИСТИНА – функция отыскивает ориентировочное совпадение. Является значением по дефлоту. Ориентировочное совпадение значит, если не было найдено ни 1-го совпадения, то функция возвратит значение предшествующего ключа. При всем этом предшествующим будет считаться тот ключ, который идет перед разыскиваемым согласно сортировке от наименьшего к большему или от А до Я. Потому, перед применением функции с данным интервальным просмотром, за ранее отсортируйте 1-ый столбец таблицы по возрастанию, потому что, если это не создать, функция может возвратить неверный итог. Когда найдено несколько совпадений, ворачивается крайнее из их.
Принципиально не путать, что номер столбца указывается не по индексу на листе, а по порядку в обозначенном спектре.

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

Для цены нужно применять функцию ВПР с четким совпадением (интервальный просмотр ЛОЖЬ), потому что данный параметр определен для всех продуктов и не предугадывает внедрение цены другого продукта, если вдруг она по случайности еще не определена.

Для определения расположения продукта употребляется ВПР с ориентировочным совпадением (интервальный просмотр ИСТИНА), потому что распределение продукта осуществляется по категориям. Из-за того, что наименование продукта состоит из наименования группы плюс доп текст, по условиям сортировки от А до Я наименования продуктов будут идти сходу опосля наименования группы, потому когда в таблице не находится совпадений с ключом подтягивается 1-ое значение сверху.

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

Он подобного эффекта можно избавиться методом определения группы из наименования продукта используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(” “;C11)-1), которые возвратят все знаки до первого пробела, также поменять интервальный просмотр на четкий.

Кроме всего описанного, функция ВПР дозволяет использовать для текстовых значений подстановочные знаки – * (звездочка – хоть какое количество всех знаков) и ? (один хоть какой знак). К примеру, для искомого значения “*” & “иван” & “*” могут подойти строчки Иван, Иванов, диванчик и т.д.

Также данная функция может находить значения в массивах – =ВПР(1;<2;”Два”:1;”Один”>;2;ЛОЖЬ) – итог выполнения строчка «Два».

Microsoft Excel

трюки • приёмы • решения

Функция ГПР

Горизонтальное 1-ое равенство. Отыскивает совпадение по ключу в первой строке определенного спектра и возвращает значение из обозначенной строчки этого спектра в совпавшем с ключом столбце.

Синтаксис: =ГПР(ключ; спектр; номер_строки; [интервальный_просмотр]).

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

Синтаксис

=INDIRECT(ref_text, [a1]) – британская версия

=ДВССЫЛ(ссылка_на_текст;[a1]) – российская версия

Функция ДВССЫЛ в Excel: примеры

Начнем с хрестоматийного примера, чтоб осознать механизм работы функции.

Имеется таблица с данными:

Примеры функции ДВССЫЛ:

Разглядим практическое применение функции. На листах 1, 2, 3, 4 и 5 в одних и тех же ячейках размещены однотипные данные (информация о образовании служащих конторы за крайние 5 лет).

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

Пишем формулу в ячейке В4 и копируем ее на всю таблицу (вниз и на право). Данные с 5 разных листов собираются в итоговую таблицу.

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

Чтоб получить лишь нечетные записи, используем формулу:

Для выведения четных строк:

Примечание. Функция СИМВОЛ возвращает знак по данному коду. Код 65 выводит английскую буковку A. 66 – B. 67 – С.

Допустим, у юзера есть некоторое количество источников данных (в нашем примере – несколько отчетов). Необходимо вывести количество служащих, основываясь на 2-ух аспектах: «Год» и «Образование». Для поиска определенного значения в базе данных подступает функция ВПР.

Чтоб функция сработала, все отчеты поместим на один лист.

Но ВПР информацию в таком виде не сумеет переработать. Потому любому отчету мы дали имя (сделали именованные спектры). Раздельно сделали выпадающие списки: «Год», «Образование». В перечне «Год» – наименования именованных диапазонов.

Задачка: при выбирании года и образования в столбце «Количество» обязано появляться число служащих.

Если мы используем лишь функцию ВПР, покажется ошибка:

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

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

2. Транспонирование данных при помощи функции ДВССЫЛ (INDIRECT)

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

ДВССЫЛ в данном случае не является традиционным решением задачки, но тоже с ней управится. Для этого нам необходимо прописать такую формулу, которая при горизонтальном протягивании будет всякий раз ссылаться на ячейку ниже. Можно было бы прописывать руками номер строчки, но в примере ниже мы воспользуемся функцией СТОЛБЕЦ (COLUMN), которая возвратит номер столбца текущей ячейки. В моем примере они совпадут – данные размещены комфортно, чтоб не перегружать формулу излишними вычислениями для вашей наглядности (исходник начинается со 2й строчки, транспонирую в спектр, который начинается со 2го столбца). На практике может потребоваться скорректировать приобретенный итог фунции СТОЛБЕЦ, к примеру, вычитанием различия.

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

Пример

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

Функция ДВССЫЛ в Excel — как применять функцию ДВССЫЛ и примеры использования функции ДВССЫЛ

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

КОСВЕННОЕ описание функции

Функция НАПРЯМУЮ делает ссылку на спектр данных либо спектр данных в Excel. Используйте функцию ДВССЫЛ, если вы желаете сослаться на ячейку в формуле без конфигурации формулы.

Синтаксис функции ДВССЫЛ

КОСВЕННЫЙ (ref_text; [a1])

— ref_text: ссылка на спектр данных либо спектр имен. Эта ссылка быть может или ссылочным типом A1, или ссылкой R1C1 (два метода вызова адреса ячейки), определенным именованием в качестве ссылки либо ссылкой на ячейки. в виде строкового текста.

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

+ a1 = Правильно либо игнорировать -> Справочный текст — это ссылка типа A1, которая является столбцом A, строчкой 1.

+ a1 = False -> Справочный текст — это справочный тип. R1C1 — это строчка 1 и столбец 1.

Внимание при использовании функции КОСВЕННО

— В случае ссылки на область данных ref_text на другое рабочее окно нужно, чтоб это окно было открыто, по другому функция возвратит #REF! Значение ошибки.

— Если спектр ссылок превосходит количество строк (больше 1 048 576) либо превосходит количество столбцов (больше 16 348 столбцов) => функция возвращает # ССЫЛКА! Значение ошибки

Пример функции КОСВЕННЫЙ

Пример 1: Обычный пример, знакомящий вас с адресами ячеек, который поможет для вас найти тип ссылки.

К примеру, есть значения в столбце B, используя функцию для получения значений в столбце B, начиная с 5-й строчки с различными ссылочными типами. В ячейках для вычисления введите формулу: = КОСВЕННО (E5; ИСТИНА)

Нажмите Enter, чтоб получить результаты. Для ссылочного типа R1C1 введите ту же формулу заместо значения a1 = False:

Итог возвращает правильное значение. То же самое и с иными ценностями.

Пример 2:

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

Шаг 1: Назовите каждую область данных типа, к примеру, с электрическими элементами темные данные из B15: C17 перейдите в адресную строчку, чтоб ввести имя области данных, обращая внимание на то, чтоб имя области данных совпадает с name Типы строк в таблице данных выше:

Шаг 2: Аналогично наименованию области данных для продуктов народного употребления, швейных изделий. Опосля ввода в ячейку нужно получить продажную стоимость формулы: = ВПР (D5, КОСВЕННО (C5), 2,0)

Шаг 3: Нажмите Enter, вы получили продажную стоимость в подтаблице в главный таблице данных:

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

Кроме использования косвенной функции, вы сможете применять функцию Vlookup в сочетании с функцией if:

Итог возвращает то же значение:

Но, когда функция vlookup соединяет воединыжды оператор if, этот оператор будет длиннее, чем функция Vlookup, объединенная с косвенной функцией.

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