Примеры использования функции ДВССЫЛ (INDIRECT)
HTML-код
- Опубликовано: 13 апр 2017
- Разбор на примерах суперполезной функции ДВССЫЛ (INDIRECT). Скачать файл-пример www.planetaexcel.ru/techniques...
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru/books/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
Любой урок открываешь и восхищаешься тренером! Ни одного лишнего слова, слова паразиты вообще отсутствуют. Блеск! Спасибо Николай! Вы - лучший!
Просто я их на монтаже вырезаю :)
Лаконично, емко и полезно! Профессионал!
Николай, спасибо за «Пример 3. Выпадающий список по умной таблице»
Это, пожалуй, самое красивое решение для создания «расширяемого» выпадающего списка в Excel:
=ДВССЫЛ("Люди[Сотрудники]")
Еще вспомнил, в формулах для «Условного форматирования» функция ДВССЫЛ разрешает писать формулы в стиле «Умных таблиц», например:
=ДВССЫЛ("Люди[Сотрудники]")=$J10
=ДВССЫЛ("Люди["&A$1&"]")=A$6
Соответственно без ДВССЫЛ это не работает…
у меня не получается, делаю как показано на ролике- =ДВССЫЛ("Люди[Сотрудники]")
Николай Вы лучший тренер. Спасибо большое!!!
Отлично! А я использовал ДВССЫЛ в ВПР. В качестве ссылки на ячейку, в которой указывалось название диапазона поиска (в моем случае - умной таблицы), которое нужно было выбирать из выпадающего списка. Без ДВССЫЛ Exсel воспринимал ссылку (название умной таблицы) как обычный текст. Примерно так: =ВПР([что ищем];ДВССЫЛ(Лист1!$H$1 (поле со списком из названий умных таблиц);столбец поиска;ЛОЖЬ).
Спасибо! Как всегда все очень интересно, полезно и доступно
очень крутой урок, как и всегда! Спасибо, Николай!))))
Именно что искал! Автору респект
Спасибо большое, учусь у Вас не первый год!
И снова отлично. Спасибо!
Как сериал можно пересматривать, чтобы закрепить в голове какие-то моменты😀👍👍👍🔥
Николай, спасибо. Знал об этой функции, однако практически не использовал. Исправлюсь.
Долго ломал голову как написать формулу для поиска известных данных в таблице (т.е. если вообще в таблице искомое значение или нет), перебрал все комбинации ВПР, ГПР, ПОИСКПОЗ, ИНДЕКС и СМЕЩ на худой конец. Ваш урок сильно выручил! Даже не знал что можно ДВССЫЛ так использовать! Спасибо огромное!
Шикарно. Хоть сейчас мне это не нужно, вспоминаю моменты, когда так хотелось подобную функцию
Просто и шикарно!
Почему я вчера это не посмотрел?))) как раз нужно было, как на последнем примере из множества вкладок вытащить значения. Спасибо за урок!
spasibo za vashi starania, vashi video uroki vsegda polzeni i nujnie,
Спасибо за видеоурок.
Спасибо вам большое!
Большое спасибо!!!
Спасибо за труд
супер! просто и понятно. спасибо
Здр. очень благодарен за полезные видеокурсы
КРУТО!!! как всегда, впрочем
Отличная функция ДВССЫЛ. Но при создании выпадающего списка лучше использовать именованный диапазон.
Т.е. мы создаём умную таблицу, а после назначаем отдельное имя этой таблицы через диспетчер формул. В этом случае можно использовать именованный диапазон в проверке данных.
Плюс такого сложного решения в том, что при переименовании умной таблицы выпадающей список будет функционировать и дальше.
Николай, благодарю за познавательное видео. Не могли бы вы сделать видео о функции - Анализ "что если".
да, очень полезная функция, спасибо
Спасибо, очень помогло
Спасибо!
Спасибо огромное!
Пушка! Благодарю
Круто!!!
Вы крутой!
Большое спасибо, Николай. Очень полезное видео!
Возник вопрос: если я создаю связанные раскрывающиеся списки и они должны работать на много строк вниз, то как лучше написать формулу во втором связанном столбце, что бы ее можно было скопировать и она работала без правки вручную.
С переносом списка, для не возникновения #ССЫЛКА! можно использовать формулу ЕСЛИОШИБКА и аргументом замены поставить двойные кавычки, разве не тоже самое будет? (в конце примера, вы советуете сделать проверку на "0", с последующей заменой на пустую ячейку)
Интересует момент с выпадающим списком 10:43 , а можно что бы он был отсортирован по алфавиту? Не затрагивая основную таблицу? Может как то хитро создать рядом еще одну таблицу где будет автоматом все дописываться с первой и сортироваться?
Подскажите пжл. про последний способ применения двссыл, как быть если в каждом листе кол.во позиции в товаров состоит из более 70 строк их надо обьединить в общую в конце?
К статии: Если в примере с выпадающим списком не дописывать СОТРУДНИКИ , а просто оставить "люди" (т.к. мы именовали ОДИН столбец) - ВСЕ ВЕЛИКОЛЕПНО РАБОТАЕТ. Если в "умной таблице" не один столбец, то надо выделить нужный и присвоить ему имя и можете спокойно его использовать в ячейке с выпадающим списком....
точно...когда знаешь, все просто...
Это не баг и не фитча. Надо в списке имен создать именнованный диапазон столбце данной таблицу и вписать его название через равно в проверке данных и будет счастье.
Здравствуйте. В случае с выпадающим списком, список обновляется и без функции ДВССЫЛ. Т.е. я применил пункт меню "Проверка данных" к связанному списку с умной таблицей список обновился соответственно таблице (при этом программой использовались абсолютные ссылки).
посмотрел... пойду потранспонирую
Всех приветствую!
Подскажите пожалуйста, как, подобно примеру 2, выделять текст в зависимости от значений в других ячейках?
В данном примере, меняя значения конца и начала интервала, менялись и выделенные месяца.
Заранее благодарен за помощь!
Для сборки в одну таблицу, я бы не дописывал "B, C, D" и "1, 2, 3, 4", а использовал бы функцию ИНДЕКС, т.к. имена столбцов совпадают. Но то Я....
- Кстати: Это Я почерпнул из Ваших предыдущих видеоуроков!!!!
Николай, а каким инструментом воспользоваться, чтобы также ячейки выделялись при смене диапазона?
Николай, есть ли какое сочетание клавиш которое при протягивании ячейки поделит значение этой ячейки на количество протягиваемых ячеек? Например у меня есть в первой ячейке число 2500 я его протягиваю на 4 ячейки вправо и мне нужно что бы эти 2500 разделило на 4ячейки и вписало в каждую ячейку равную часть от этих 2500 По примеру как автозаполнение при протягивании но только мне нужно деление
👍👍👍🔥🔥🔥🔥🔥
Как использовать формулу УНИК( ) внутри ДВССЫЛ ( ) ? Что там в кавычках? Исходные данные в умной таблице.
Спасибо Николай. Как нужно сделать для того чтобы , когда открывалсья новый лист, имя листа автоматически попала в другой лист где хранятся все имена листов.
Очень ценный видеоурок.
Я использовала функцию INDIRECT в своей формуле, но, видимо, что-то не правильно.
Нужно чтобы в формуле неабсолютное значение ячейки изменялось (при копировании формулы) на следующие значения в ячейках по данной строке, но не получается.
ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН('Raw Data'!$L$2:$L$150;'Raw Data'!$B$2:$B$150;B9;'Raw Data'!$D$2:$D$150;(ДВССЫЛ("R8C"&СТРОКА();ЛОЖЬ);"n/a")
Блеск!
Добрый день. А не подскажите, какая формула была использована в условном форматировании для выручки на 7.47 минуте, там автоматически выделяется диапазон?
Блин это такая тема, кто в теме, тот поймет. Можно даже вба не изучать.
Спасибо! А как при помощи формулы двссыл или другой, транспортировать данные с другого листа которые нужно с вертикального в горизонтальное положение в несколько строк (в одну получается, но это слишком длинная стока, т.е. месяц по дням с разбивший по часам). Формула такая ДВССЫЛ(«листH”&столбец())
Тоже измучился.
а как быть если нужно транспонировать данные ,находящиеся не в ячейке А2,а например в ячейке А33,при вводе формулы -выдает значение=0????
Большое спасибо. Но подскажите. Как быть если есть листы д 1, Д 2 и тд. По количеству дней в месяце. И надо сделать сводку за месяц. Прямая ссылка работает. Но вот как правильно оформить ДВССЫЛ что бы после протянуть до конца месяца и оно автоматически меняло листы но ячейку оставляло ?
а подскажите как сделать , если у тебя есть несколько листов с сотрудниками (например 1 лист сотрудники с отдела А, второй лист сотрудников отдела Б), и есть третий лист, где мы отмечаем график работы всех сотрудников обоих отделов, как сделать выпадющий список, который будет создаваться из двух списков\листов
Боюсь, что без промежуточной таблицы, соединяющей оба списка в один, никак. Её можно сделать вручную или запросом в Power Query, если он у вас есть.
Подскажите, если с 1 столбца нужно сделать сумму на разные города в другой столбец, то какой функцией нужно воспользоваться ?
Спасибо большое за урок, подскажите, пожалуйста, если в названиях листов есть пробел, то ДВССЫЛ выдает ошибку. Как можно это обойти?
Интересно а как сделать сумму ячеек "ИТОГО" на всех имеющихся или будующих листах?
Николай, добрый день.При открытых книгах все ок, но стоит снова открыть файл без первоистоничка и все данные с ошибками "ССЫЛКА!". Как известно, ДВССЫЛ не работает с закрытыми книгами, как быть в таких случаях? Альтернативу функции пока не нашел.
Единственный вариант - заранее открывать книги, из которых ДВССЫЛ берет данные.
Здравствуйте, у меня (excel 2016) выпадающий список подхватывает новые/добавляемые строки без ДВССЫЛ, также и удаляет, что не нужно. То есть получается, что в умной таблице заданный диапазон уже не является фиксированным? Спасибо.
Если именованный диапазон вы сделали на умной таблице, то он тоже станет резиновым, да.
А как на 14:25 сделать также, только без цифр в столбце левее, как формула тогда будет выглядеть?
Николай, добрый день! Подскажите, как использовать функцию ДВССЫЛ если наименование листов состоит из двух и более слов?
Имя листа нужно заключить в апострофы (одинарные кавычки).
Здравствуйте. Можно вопрос? Какую функцию использовать в ексель 2010 для того, чтобы при простого выделения в списке ячейку А1 со значением "Иванов" в листе 1, вышло этот значение "Иванов" в ячейке В2 в листе 2? Заранее спасибо.
=Лист1!A1
@@planetaexcel простите но у меня будет огромный список. Этот вариант прокатит, когда в списке один Иванов только.
А как при выборе месяца в столбце меняется цвет?
Если в создании выпадающего списка используется "умная таблица", то достаточно указать первоначальный диапазон в виде "=$A$2:$A$5" без кавычек. В дальнейшем, с увеличением количества строк таблицы диапазон будет автоматически меняться. Проверьте
Но такая ссылка менее понятна. И попробуйте сделать так с таблицей на другом листе.
Если умная таблица и выпадающий список на одном листе - да, а если на разных - нет.
И это ограничение обойти легко. Нужно создать дополнительный именованный диапазон с областью видимости "книга" и связать его с нужным столбцом "умной таблицы". Например, имя "столбец1"=Таблица1[столбец1]. В этом случае значение диапазона для списка будет выглядеть "=столбец1"
------
To Evgen S
Эту ссылку руками вводить не нужно, достаточно навести мышкой на нужный столбец умной таблицы, причем так, чтобы выделился только диапазон с данными, при этом ссылка сформируется автоматически. По поводу разных листов ответил выше
С функцией ДВССЫЛ способ более универсален. Да и действий меньше.
Я не против функции, ее преимущества неоспоримы, и видео замечательное, как и все прочие уроки Николая. Я просто отметил неточность в утверждении (видео с 9:20 ->)
Николай, добрый день! Ещё вопрос, не могу разобраться, как использовать функцию ДВССЫЛ при подсчёте закрашенных ячеек?
Насколько я знаю, она такое не умеет :)
Здравствуйте, все круто, но вот почему-то никак не получается связать выпадающий список =ДВССЫЛ и резиновую Таблицу со значениями (и диапазон тоже) из другого файла Excel. Скорее всего тоже есть какая-то хитрость в кавычках или скобках, но не понятно в каком моменте!? Если не трудно, опишите пожалуйста как построить такую функцию и связь...
А вообще заодно никак нельзя-ли, чтобы список выпадал при вводе текста в ячейке списка, предлагая варианты по введенному ключевому слову? Заранее спасибо!
Мое почтение. Не получается привязать источник из другой книги excel. Книга донор открыта, находится в той же папке, имя диапазону присвоено. =indirect("[книга.xlsx]Лист1!Имя диапазона").
Пример таблицы.
Лист 1
Тут список товаров которые покупаются каждый мес, но некоторые иногда могут не покупаться (если есть остаток на начало мес).
Хлеб 5
Сыр 0
Масло 4
Соль 1
Нужно собрать в новую таблицу в листе 2 те строки, которые имеют значения больше ноля.
Пример:
Хлеб 5
Масло 4
Соль 1.
Обратите внимание:
Строка со значением "0" (тоесть строка Сыр) проигнорировалась.
Строка Сыр в ячейке В2 было значение ниже ноля...
Нужно собрать данные из строк опираясь на один из столбцов в ячейке которого есть данные в данном случае это столбец В
Друзья, если есть видео с таким примером, то дайте ссылку или расскажите как это реализовать?
Может есть функция какая нибудь типа "ССЫЛКА ЕСЛИ" 😞
А если таблица начинается не с А1 и есть другие таблицы на листе?
2022
Я когда ввожу это в источник =ДВССЫЛ("Люди[Сотрудники]")
Выдает:
"При вычислении "Источник" возникает ошибка.Продолжить?"
После чего у меня совсем нету выпадающего списка. Что делать?
либо название диапазона неверное, либо заголовок столбца неверный
Я ВВЁЛ:
=ДВССЫЛ('КЖ-04'!B3)
выбрасывает ошибку #ССЫЛКА
И делал как в последнем примере:
=ДВССЫЛ(B1&"!"&C4&B2)
но не работает, почему такое может быть? может быть такое, что теперь эта формула работает по-другому?
нашёл проблему! наличие дефиса или пробела в названии вкладки, на которую я хотел сослаться, выдавала ошибку! (нижнее подчёркивание не вызывает ошибку!)
двссыл можно сделать динамической примерно так =ЕСЛИОШИБКА(ДВССЫЛ("'[Баланс КЭС.xlsx]"&D$3&$B7-2000&"'!$"&СИМВОЛ(64+ПОИСКПОЗ("Пол. Отпуск";ДВССЫЛ("'[Баланс КЭС.xlsx]"&D$3&$B7-2000&"'!$4:$4");0))&"$"&ПОИСКПОЗ($A$1;ДВССЫЛ("'[Баланс КЭС.xlsx]"&D$3&$B7-2000&"'!$A:$A");0));
знак доллара как же?
а если не первая строка? в случае: из колонки в столбец...
=ДВССЫЛ("R2C"&СТРОКА();ЛОЖЬ)
как решить эти задачи docs.google.com/spreadsheets/d/1BsUk68KI-0VmDWtyKfMuoP7U1Bj41jRUVF4TgblEFYQ/edit#gid=588555730
Всё-так последней пример нуууу никак не дошёл до меня, эти доп столбцы окончательно запутали
Все, абсолютно все примеры на одном листе, а как этот двссыл сделать с другого листа? Я уже за*бался, пятый час сижу...
="['" & тут имя листа & "']!" & тут адрес ячейки
Дебильная функция с непонятным функционалом. Эти частные случаи запоминать что ли надо. Так в обычной практике и не догадаешься интуитивно как ее применять.