Любой урок открываешь и восхищаешься тренером! Ни одного лишнего слова, слова паразиты вообще отсутствуют. Блеск! Спасибо Николай! Вы - лучший!

@planetaexcel

4 жыл бұрын

Просто я их на монтаже вырезаю :)

Лаконично, емко и полезно! Профессионал!

Николай Вы лучший тренер. Спасибо большое!!!

Николай, спасибо за «Пример 3. Выпадающий список по умной таблице» Это, пожалуй, самое красивое решение для создания «расширяемого» выпадающего списка в Excel: =ДВССЫЛ("Люди[Сотрудники]")

@biweb13

7 жыл бұрын

Еще вспомнил, в формулах для «Условного форматирования» функция ДВССЫЛ разрешает писать формулы в стиле «Умных таблиц», например: =ДВССЫЛ("Люди[Сотрудники]")=$J10 =ДВССЫЛ("Люди["&A$1&"]")=A$6 Соответственно без ДВССЫЛ это не работает…

@0107195513081960

7 жыл бұрын

у меня не получается, делаю как показано на ролике- =ДВССЫЛ("Люди[Сотрудники]")

Спасибо большое, учусь у Вас не первый год!

spasibo za vashi starania, vashi video uroki vsegda polzeni i nujnie,

Как сериал можно пересматривать, чтобы закрепить в голове какие-то моменты😀👍👍👍🔥

очень крутой урок, как и всегда! Спасибо, Николай!))))

Почему я вчера это не посмотрел?))) как раз нужно было, как на последнем примере из множества вкладок вытащить значения. Спасибо за урок!

Именно что искал! Автору респект

Долго ломал голову как написать формулу для поиска известных данных в таблице (т.е. если вообще в таблице искомое значение или нет), перебрал все комбинации ВПР, ГПР, ПОИСКПОЗ, ИНДЕКС и СМЕЩ на худой конец. Ваш урок сильно выручил! Даже не знал что можно ДВССЫЛ так использовать! Спасибо огромное!

Спасибо! Как всегда все очень интересно, полезно и доступно

Отлично! А я использовал ДВССЫЛ в ВПР. В качестве ссылки на ячейку, в которой указывалось название диапазона поиска (в моем случае - умной таблицы), которое нужно было выбирать из выпадающего списка. Без ДВССЫЛ Exсel воспринимал ссылку (название умной таблицы) как обычный текст. Примерно так: =ВПР([что ищем];ДВССЫЛ(Лист1!$H$1 (поле со списком из названий умных таблиц);столбец поиска;ЛОЖЬ).

И снова отлично. Спасибо!

Николай, спасибо. Знал об этой функции, однако практически не использовал. Исправлюсь.

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

Просто и шикарно!

КРУТО!!! как всегда, впрочем

Спасибо за видеоурок.

Спасибо вам большое!

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

Николай, благодарю за познавательное видео. Не могли бы вы сделать видео о функции - Анализ "что если".

Большое спасибо!!!

супер! просто и понятно. спасибо

Здр. очень благодарен за полезные видеокурсы

Спасибо за труд

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

Пушка! Благодарю

да, очень полезная функция, спасибо

Спасибо, очень помогло

Спасибо!

Круто!!!

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

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

С переносом списка, для не возникновения #ССЫЛКА! можно использовать формулу ЕСЛИОШИБКА и аргументом замены поставить двойные кавычки, разве не тоже самое будет? (в конце примера, вы советуете сделать проверку на "0", с последующей заменой на пустую ячейку)

Спасибо огромное!

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

Вы крутой!

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

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

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

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

Интересует момент с выпадающим списком 10:43 , а можно что бы он был отсортирован по алфавиту? Не затрагивая основную таблицу? Может как то хитро создать рядом еще одну таблицу где будет автоматом все дописываться с первой и сортироваться?

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

а как быть если нужно транспонировать данные ,находящиеся не в ячейке А2,а например в ячейке А33,при вводе формулы -выдает значение=0????

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

Подскажите, если с 1 столбца нужно сделать сумму на разные города в другой столбец, то какой функцией нужно воспользоваться ?

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

@planetaexcel

6 жыл бұрын

Единственный вариант - заранее открывать книги, из которых ДВССЫЛ берет данные.

Спасибо! А как при помощи формулы двссыл или другой, транспортировать данные с другого листа которые нужно с вертикального в горизонтальное положение в несколько строк (в одну получается, но это слишком длинная стока, т.е. месяц по дням с разбивший по часам). Формула такая ДВССЫЛ(«листH”&столбец())

@borat--sagdiyev

3 жыл бұрын

Тоже измучился.

а подскажите как сделать , если у тебя есть несколько листов с сотрудниками (например 1 лист сотрудники с отдела А, второй лист сотрудников отдела Б), и есть третий лист, где мы отмечаем график работы всех сотрудников обоих отделов, как сделать выпадющий список, который будет создаваться из двух списков\листов

@planetaexcel

4 жыл бұрын

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

Николай, добрый день! Подскажите, как использовать функцию ДВССЫЛ если наименование листов состоит из двух и более слов?

@planetaexcel

Жыл бұрын

Имя листа нужно заключить в апострофы (одинарные кавычки).

👍👍👍🔥🔥🔥🔥🔥

Здравствуйте, у меня (excel 2016) выпадающий список подхватывает новые/добавляемые строки без ДВССЫЛ, также и удаляет, что не нужно. То есть получается, что в умной таблице заданный диапазон уже не является фиксированным? Спасибо.

@planetaexcel

7 ай бұрын

Если именованный диапазон вы сделали на умной таблице, то он тоже станет резиновым, да.

точно...когда знаешь, все просто...

Николай, добрый день! Ещё вопрос, не могу разобраться, как использовать функцию ДВССЫЛ при подсчёте закрашенных ячеек?

@planetaexcel

Жыл бұрын

Насколько я знаю, она такое не умеет :)

Блеск!

К статии: Если в примере с выпадающим списком не дописывать СОТРУДНИКИ , а просто оставить "люди" (т.к. мы именовали ОДИН столбец) - ВСЕ ВЕЛИКОЛЕПНО РАБОТАЕТ. Если в "умной таблице" не один столбец, то надо выделить нужный и присвоить ему имя и можете спокойно его использовать в ячейке с выпадающим списком....

Здравствуйте. Можно вопрос? Какую функцию использовать в ексель 2010 для того, чтобы при простого выделения в списке ячейку А1 со значением "Иванов" в листе 1, вышло этот значение "Иванов" в ячейке В2 в листе 2? Заранее спасибо.

@planetaexcel

3 жыл бұрын

=Лист1!A1

@user-hh8pf9lc1l

3 жыл бұрын

@@planetaexcel простите но у меня будет огромный список. Этот вариант прокатит, когда в списке один Иванов только.

Мое почтение. Не получается привязать источник из другой книги excel. Книга донор открыта, находится в той же папке, имя диапазону присвоено. =indirect("[книга.xlsx]Лист1!Имя диапазона").

А как на 14:25 сделать также, только без цифр в столбце левее, как формула тогда будет выглядеть?

Блин это такая тема, кто в теме, тот поймет. Можно даже вба не изучать.

Для сборки в одну таблицу, я бы не дописывал "B, C, D" и "1, 2, 3, 4", а использовал бы функцию ИНДЕКС, т.к. имена столбцов совпадают. Но то Я.... - Кстати: Это Я почерпнул из Ваших предыдущих видеоуроков!!!!

Интересно а как сделать сумму ячеек "ИТОГО" на всех имеющихся или будующих листах?

Здравствуйте, все круто, но вот почему-то никак не получается связать выпадающий список =ДВССЫЛ и резиновую Таблицу со значениями (и диапазон тоже) из другого файла Excel. Скорее всего тоже есть какая-то хитрость в кавычках или скобках, но не понятно в каком моменте!? Если не трудно, опишите пожалуйста как построить такую функцию и связь...

@doVita86

6 жыл бұрын

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

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

Очень ценный видеоурок. Я использовала функцию INDIRECT в своей формуле, но, видимо, что-то не правильно. Нужно чтобы в формуле неабсолютное значение ячейки изменялось (при копировании формулы) на следующие значения в ячейках по данной строке, но не получается. ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН('Raw Data'!$L$2:$L$150;'Raw Data'!$B$2:$B$150;B9;'Raw Data'!$D$2:$D$150;(ДВССЫЛ("R8C"&СТРОКА();ЛОЖЬ);"n/a")

Как использовать формулу УНИК( ) внутри ДВССЫЛ ( ) ? Что там в кавычках? Исходные данные в умной таблице.

посмотрел... пойду потранспонирую

А как при выборе месяца в столбце меняется цвет?

Я когда ввожу это в источник =ДВССЫЛ("Люди[Сотрудники]") Выдает: "При вычислении "Источник" возникает ошибка.Продолжить?" После чего у меня совсем нету выпадающего списка. Что делать?

@evgendenisov4093

4 жыл бұрын

либо название диапазона неверное, либо заголовок столбца неверный

А если таблица начинается не с А1 и есть другие таблицы на листе?

Я ВВЁЛ: =ДВССЫЛ('КЖ-04'!B3) выбрасывает ошибку #ССЫЛКА И делал как в последнем примере: =ДВССЫЛ(B1&"!"&C4&B2) но не работает, почему такое может быть? может быть такое, что теперь эта формула работает по-другому?

@user-st7hj3pv2x

3 жыл бұрын

нашёл проблему! наличие дефиса или пробела в названии вкладки, на которую я хотел сослаться, выдавала ошибку! (нижнее подчёркивание не вызывает ошибку!)

знак доллара как же?

Пример таблицы. Лист 1 Тут список товаров которые покупаются каждый мес, но некоторые иногда могут не покупаться (если есть остаток на начало мес). Хлеб 5 Сыр 0 Масло 4 Соль 1 Нужно собрать в новую таблицу в листе 2 те строки, которые имеют значения больше ноля. Пример: Хлеб 5 Масло 4 Соль 1. Обратите внимание: Строка со значением "0" (тоесть строка Сыр) проигнорировалась. Строка Сыр в ячейке В2 было значение ниже ноля... Нужно собрать данные из строк опираясь на один из столбцов в ячейке которого есть данные в данном случае это столбец В Друзья, если есть видео с таким примером, то дайте ссылку или расскажите как это реализовать? Может есть функция какая нибудь типа "ССЫЛКА ЕСЛИ" 😞

2022

Если в создании выпадающего списка используется "умная таблица", то достаточно указать первоначальный диапазон в виде "=$A$2:$A$5" без кавычек. В дальнейшем, с увеличением количества строк таблицы диапазон будет автоматически меняться. Проверьте

@evgens7003

7 жыл бұрын

Но такая ссылка менее понятна. И попробуйте сделать так с таблицей на другом листе.

@planetaexcel

7 жыл бұрын

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

@Arkadi_P

7 жыл бұрын

И это ограничение обойти легко. Нужно создать дополнительный именованный диапазон с областью видимости "книга" и связать его с нужным столбцом "умной таблицы". Например, имя "столбец1"=Таблица1[столбец1]. В этом случае значение диапазона для списка будет выглядеть "=столбец1" ------ To Evgen S Эту ссылку руками вводить не нужно, достаточно навести мышкой на нужный столбец умной таблицы, причем так, чтобы выделился только диапазон с данными, при этом ссылка сформируется автоматически. По поводу разных листов ответил выше

@3855298

7 жыл бұрын

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

@Arkadi_P

7 жыл бұрын

Я не против функции, ее преимущества неоспоримы, и видео замечательное, как и все прочие уроки Николая. Я просто отметил неточность в утверждении (видео с 9:20 ->)

а если не первая строка? в случае: из колонки в столбец...

@user-xl4xp5bx8m

4 жыл бұрын

=ДВССЫЛ("R2C"&СТРОКА();ЛОЖЬ)

двссыл можно сделать динамической примерно так =ЕСЛИОШИБКА(ДВССЫЛ("'[Баланс КЭС.xlsx]"&D$3&$B7-2000&"'!$"&СИМВОЛ(64+ПОИСКПОЗ("Пол. Отпуск";ДВССЫЛ("'[Баланс КЭС.xlsx]"&D$3&$B7-2000&"'!$4:$4");0))&"$"&ПОИСКПОЗ($A$1;ДВССЫЛ("'[Баланс КЭС.xlsx]"&D$3&$B7-2000&"'!$A:$A");0));

как решить эти задачи docs.google.com/spreadsheets/d/1BsUk68KI-0VmDWtyKfMuoP7U1Bj41jRUVF4TgblEFYQ/edit#gid=588555730

Всё-так последней пример нуууу никак не дошёл до меня, эти доп столбцы окончательно запутали

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

@planetaexcel

3 жыл бұрын

="['" & тут имя листа & "']!" & тут адрес ячейки