Динамические выпадающие списки, ДВССЫЛ и Умные Таблицы - Функции Excel (7)
HTML-код
- Опубликовано: 20 ноя 2020
- Привет! В этом видео ты узнаешь, как в Excel можно создавать динамические выпадающие списки. Динамичность данных списков заключается в том, что при добавлении или удалении значений из источника данных значения в выпадающем списке также автоматически актуализируются. То есть ручное обновление источника данных при работе с подобными выпадающими списками не требуется.
Видео при этом добавляется в альбом о функциях и формулах в Excel, поскольку для динамических выпадающих списков требуется применение функции ДВССЫЛ, которую мы в этом видео наряду с умными таблицами рассмотрим.
Приятного просмотра! 😊
Файл из видео:
drive.google.com/file/d/1kxiv...
Хочешь поддержать мой канал? Это можно сделать с помощью функции «Спонсорство»:
/ @bilyalkhassenov
Это лучшие уроки по экзель которые я смотрел. Спасибо большое Билял.
Функция двссыл очень пригодилась в работе, спасибо вам огромное!
спасибо за урок! очень познавательно)
Спасибо большое за ваши уроки. Всё доступно и понятно
Я из-за вас уже получил повышение на работе Спасибо огромное!
Билалу магарыч будет на кошелек? :)
Автору огромное спасибо!!!!! Любовь и уважение!
Самые классные уроки. Коротко и ясно.👌
Спасибо, Билял, рад наслаждаться твоими уроками!!
Спасибо за полезную информацию
Огромное спасибо, по-братски! Материал, подача - огонь! И оно заработало:)))
Билял ты объяснил все доступно! Красава
большое спасибо за новое видео :)
Благодарю. Очень круто
Спасибо ! Класс!!
Спасибо)
Спасибо 🙏 большое
Привет! Было бы круто, если бы в описании под видео были ссылки на связанные с этим видео темы: там есть такая фраза, мол об этом расскажу в следующем видео. Спасибо!
Как всегда - класс 👍👍👍
Спасибо!
Спасибо большое ! А Класс, Цена ,Доступно подтягиваются по функциям следующего видео (я тоже сначала не поняла, как это сделать)
1000 лайков ! Ты молод и крут!)
спасибо!
Спасибо большое
Очень помогло с libreoffice calc
отличный ролик, только начинаю постигать функционал эксель.
Здравствуйте
Спасибо за видео, все работает. Именно то что было нужно. Можно сюда добавить функцию поиска на динамические выпадающие списки? Очень большой список, неудобно выбирать вручную.
Здравствуй, Билял. Подскажи, есть ли возможность сделать список, ссылающийся на умную таблицу но при этом чтобы в этом списке были только уникальные значения. И хотелось бы второй список, который был бы связан с первым списком и выводил бы только те значения, которые соответствуют значениям первого списка. Спасибо
Можно присвоить динамическому диапазону имя через диспетчер имён, имя прописывается в проверке значений для выпадающего списка напрямую, эффект будет то-же.
Куда так спешите мастер ??? Не поспеваю за "бегом" Ваших мыслей ... Придется на пониженной скорости смотреть.
Жаль, что только один раз, можно лайк поставить.
Классное видео! Можно ли сделать выпадающий список 2х уровневым? Ну т.е. что бы список на 2 уровне выбора зависел от того что мы выберем на 1 уровне? (Например: мы выбираем 1) сначала машину, а исходя из того какую машину мы выбрали нам предлагается выпадающий список водителей к этой машине? Есть видео о том, как собрать такой эксель файл?
Дякую!
Красава
пипец это гениально
Добрый день!
Подскажите как ускорить функцию ВПР при количестве более 200К+ записей?
здравствуйте, дело в том, что некоторые программы, которые формируют файлы для ексель или некоторые формы для отчетов из министерства, имеют не одну строку или столбец, а множество очень мелких объединенных (обычно столбцов) и на таких ячейках формулы не работают при протягивании. как быть в этой ситуации?
В 365 Office при вводе в обычную ячейку =Автопарк[Модель] ошибка #ЗНАЧ не появляется, вместо ошибки excel возвращает весь массив этого столбца умной таблицы👍 В новом 365 Office это называется переносом
Всё ясно, спасибо огромное! Но я изменила ваши формулы на Если и Впр и тоже всё работает. Почему вы используете Поискпоз, что это даёт?
Все это здорово, но не до конца :( Я вот о чем --> А где зависимость значения второго столбца (во втором столбце тоже выпадающий список) от выбранного значения в первом столбце? И т.д. 3-й, 4-й?
Константин, добрый день! Столкнулся с такой же проблемой, подскажите, пожалуйста, получилось ли найти решение?
@@pevoff выпадающий список связывается с другим выпадающим списком тоже с помощью двссыл. Только непонятно, в чем исходный вопрос был. Какие столбцы-то имелись в виду?
@@user-dr5mg3po7o у меня задача сделать порядка 15 связанных выпадающих списков не используя нейм менеджер, т.к. он убьет быстродействие документа. Решение я лично вижу в некой формуле впр (vlookup) которая выдает сразу выпадающим списком все уникальные значения по выданному критерию. По идее тут нужны хорошие знания vba. В интернете я находил впр который выдает строкой уникальные значения по заданному критерию, но из них нельзя быстро и стандартными средствами загнать в выпадающий список.
@@pevoff впр не будет вам искать список. Стандартный впр даст первое значение, соответствующее найденному. Либо там надо извернуться в край. Если вы не хотите использовать именованные диапазоны, то можно попробовать задать их формулой (я как -то крутилась и задала подобный диапазон через подсчет значений, насколько помню. Посмотрела - использовала подсчет, смещение и поискпозиции. Плюс то, что отдельно отобрала уникальные значения.). Либо vba - это будет validation. Там не так сложно и примеры легко гуглятся.
В каком видео вы рассказываете про авто ввод класса, цены за день и доступно. Дайте ссылку
на 7:50 при удалении строк таблицы у тебя таблица соответственно уменьшается, а у меня получаются пустые строки, которые так же отображаются в выпадающем списке, может надо как то по особенному удалять ? (Я с Ctrl выделяю всю строку и жму Del)
У меня формулы ниже, как на 1.10, автоматически не подтягиваются. Как прописать, чтобы при выборе значения из выпадающего списка в ячейке ниже подтягивались данные из таблицы?
Билял спасибо большое за урок. У меня только один вопрос, а как ты сделал, что при выборе модели в выпадающем списке подтягиваются и остальные данные?(цена, доступно). Я проделал всё как ты и говорил, но со своей таблицей. Выпадающий список удалось сделать, но остальные данные не подтягиваются. Спасибо за ответ!
присоединяюсь к вопросу
Именно это я и искал
Видел одну видео по этому тему там через индекс связали все остальные инфу.
У меня всё через впр подтягивается
Присоединяюсь к вопросу. Мне надо чтобы по выбору работы в первой ячейке строчки - вся строка заполнялась автоматически.
А где следующие урок, непонятно как данные класс, цена и доступно менялась?
Добрый день! Спасибо большое за ваш труд! Почему то у меня не получается создать выпадающий список, когда название умной таблицы отличается от названия столбца. А вот если они одинаковые, то получается. Почему так? (
Подскажите пожалуйста, я сделал динамический список, при выборе авто, остальное не подтягивается, в чем причина я не понимаю
Меня финансовым аналитиком на работу приняли, без опыта, хоть я и учусь на электрика, спасибо вам❤❤
Не выгнали еще ?
Самолёт)
А вот как сделать чтобы таблица сама уменьшалась? Или она по умолчанию должна это делать? А то я пробовал но таблица не уменьшается а остается такой какой была до удаления значений
Расскажите пожалуйста как сделать автозаполнение данных по выбранной модели авто.
Спасибо большое, и прошу помочь советом: после создания Умной таблицы, ссылки структурированные не появляются. Остаются как и прежде =С1, например. С чем это м.б. связано?
у меня тоже самое, нашли ответ на вопрос?
Как можно сделать чтобы динамический выпадающий список формировался по условию? Например: есть список 2 колонки: 1-категория, 2-наименование. На отдельном листе я в ячейке пишу слово из категории и в соседней ячейке появляется выпадающий список Наименований отсортированных по этой категории
Привет. Подскажи пожалуйста как в формулу добавить функцию копирования формулы в списке?
Как работает верхняя таблица? Как три нижнии строчки понимают какую информацию им подставлять?
Класс! Спасибо автор. Но есть проблема. Данные списка передаются как значение, а не ссылка. Если мы заполним ТАБЛИЦУ данными, например, "AUDY", а потом увидим ошибку, исправим "AUDY" на "AUDI", то уже заполненные старые данные с ошибкой не будут автоматом исправляться. Нужно будем мышкой выбрать повторно. Может знаете, как это побороть? Думаю идти в направлении неких текстовых ключей для каждой строки, которые будут дополнительным столбцом в "справочнике", они должны быть уникальными и не меняться в течение жизни строки. Но это теория, практики пока не получается 😢
Плз дайте ссылку на обещанное в начале видео с подтягиванием 2го и 3го столбца при выборе 1го.
Нашли это видео?
Нет
@BilyalKhassenov вопрос схожий - а КАК сделать АВТООБНОВЛЕНИЕ связанных раскрывающихся списков ?!
Например - когда я выбираю Категорию ==>> то в ячейке "Товары из Категории" СРАЗУ ЖЕ выбирался 1-й товар из списка ?!!!!!!!!!
а если одни и те же модели отличаются объемом двигателя которые прописаны в соседнем столбце?
Билял не совсем понятно с Cntl+Shift+Enter, что вы сделали таким действием, к тому же, у меня ничего не поменялось, когда нажал эти кнопки. И с листом тоже не прошло. Тест двссыл не появился, ссылка происходит на 1 лист в действиях, несмотря на существующий 2. приходится в ручном режиме в слове Лист1 менять на Лист2.
нужно нажимать Shift+Ctrl+Enter, обратите внимание на последовательность. СНАЧАЛА Shift
Как сделать так, чтобы у меня раскрывался список из конкретной ячейки? Пример есть таблица за доходами. В одной из строк доход , полученный за продажу фруктов - рядом записан Тотал. Нужно сделать так , чтобы из этой ячейки раскрывался список из чего состоит этот Тотал- яблоки = 5 рублей ; груши = 7 рублей и так далее.
Суть - таблица из множества значений , где по каждой записан финальный Тотал. Но каждое значение состоит из других значений , которые тоже надо отобразить.
как используя ДВССЫЛ при обращении к умной таблице для создания выпадающего списка ссылаться не на название шапки умной таблицы а номера столбца?
Сделала я таблицу как в начале видео,нажала ctrl+T, сделала Проверка данных по списку и выбрала столбец с моделями авто. И все. У меня на этом этапе УЖЕ новые модели в таблице сами подтягиваются. Без функции двссыл.
спасибо! а как связывались ячейки с3 с4 с5?
Скорее всего через "ВПР()"
Просто обыскался уже сегодня.... Можете подсказать:
Есть два столбца:
1. Фио (имеются дубляжи)
2.серия паспорта человека из п. 1
Надо найти те Фио для которых введены разные серии паспортов.
То есть когда для одного и того же Петрова введены разные серии паспортов.
Нашёл решение?
Здравствуйте!
Я, конечно, поздно отвечаю, но, наверно, лучше поздно, чем совсем не ответить 😊
Мне вот такое решение сразу в голову пришло:
1. Выделяете оба столбца
2. Во вкладке «Данные» нажимаете на «Удалить дубликаты»
3. В открывшемся окне «Удалить дубликаты», в поле «Колонны», ставим галочки напротив названия этих двух столбцов (ФИО и номер паспорта). Другие столбцы в этом окне галочкой не отмечаем! Нажимаем на ОК
4. Теперь дописываем еще один столбец с формулой СЧЁТЕСЛИ. В первом аргументе указываем закрепленный с помощью F4 диапазон с ФИО, а во втором аргументе отмечаем ФИО текущей строки.
5. Протягиваем формулу
6. Фильтруем значения в новом столбце: убираем единицы
В итоге так и получим лишь ФИО со всем вариациями приписанных им номеров паспорта.
Надеюсь, получится понять такое объяснение. В любом случае - я сниму на эту тему видео на следующей неделе. Спасибо Вам за идею для видео!
С уважением,
Билял
Не знаю, пришло Вам оповещение о моём сообщении с решением, которое я только что отослал. Поэтому коротко решил и Вам написать, чтобы Вы увидели :)
@@BilyalKhassenov спасибо :) вопрос решал очень долго через сводные таблицы.. Обязательно посмотрю ваше видео
Билял, Cntl+Shift+Enter у меня, не меняют вообще ничего. Что не так?
А почему у меня без этой формулы ДВССЫЛ динамический список обновляется в выпадающем списке?
Добрый день. Я пишу макрос на VBA, с помощью которого можно заполнять в таблицу учеников и информацию об их поступлении в университеты. Так вот, основная функция работает, но я хотел бы ещё сделать так, чтобы после создания и заполнения информации об ученике, напротив него появлялась кнопка, при нажатии на которую ячейки будут окрашиваться в зелёный цвет, означая то, что работа с учеником закончена.
Я нигде в интернете не смог найти, как с помощью кода в VBA создавать кнопку в ячейках и задавать ей функцию там же.
Я подумал может у вас есть нужный мне источник, где я смогу посмотреть как это делается.
Спасибо
Готов файлик выслать. Всё очень не сложно (gena10111@gmail.com). Или сделайте таблицу как у Биляла: один в один, а в новый модуль вставьте код:
Option Explicit
Sub a()
Dim btn As Button, i As Integer
Application.ScreenUpdating = False
ActiveSheet.Buttons.Delete
Dim t As Range, t2 As Range
For i = 10 To 13
Set t = ActiveSheet.Range(Cells(i, 6), Cells(i, 6))
Set t2 = ActiveSheet.Range(Cells(i, 5), Cells(i, 5))
t2.Interior.Pattern = xlNone
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.OnAction = "btnS"
.Caption = "E" & i
.Name = "E" & i
End With
Next i
Application.ScreenUpdating = True
End Sub
Sub btnS()
Dim strC As String
strC = Application.Caller
ThisWorkbook.Worksheets("Лист1").Range(strC).Select
If Selection.Interior.ThemeColor = xlThemeColorAccent6 Then
Selection.Interior.Pattern = xlNone
Else: Selection.Interior.ThemeColor = xlThemeColorAccent6
End If
End Sub
Потом правьте, как хотите)!
@@user-bm8yq2hh6h вы мне очень помогли, спасибо!)
@@lalapanda4216 Рад был помочь. А нашел я ответ не ресурсе stackoverflow.com/questions/4559094/how-to-add-a-button-programmatically-in-vba-next-to-some-sheet-cell-data )))! Удачи
Добрый день. Видео короткое и понятное для простых списков. Однако вы не раскрыли тему как быть с выпадающим списком если значения в столбце "Модель" повторяются, к примеру при создании таблицы по продажам. Тогда в выпадающий список будут попадать одинаковые модели автомобилей и использование примеров приведенных в этом ролике становится некорректным. Пробую сделать небольшую базу данных в эксель и сейчас как раз столкнулся с такой проблемой.
Здравствуйте! Вы нашли ответ на свой вопрос??? как сделать, что бы пропадали?
@@sergeyisaev7004 День добрый. Моих знаний и времени не хватило. Найденные в интернете примеры не помогли. База данных получилась только для склада (отслеживание движение товара). Добавить к получившейся базе деньги по покупке и продаже товара не удалось, а соответсвенно вычислить прибыль тоже. Хотелось бы у вас на канале увидеть поэтапные уроки по созданию БД в эксель. Спасибо за ответ.
Если у вас две одинаковые модели по разной цене нужна пометка для отличия одного от другого, например инвентарный номер или VIN машины. В противном случае два значения объединяются общей чертой и это уже группа. А для группы нужен второй уровень динамического списка. Проще говоря: выбираете модель и а в соседнюю ячейку подтягивается список инвентарного номера машины по которому, далее в соседнюю ячейку подтягивается стоимость авто.
@@Frosya937 Добрый день. А можете прислать короткий пример таких списков. Например продажа печенья.
Привет Билял. Как дела?
объясните пожалуйста формулы в ячейка с3 с4 с5, так как я хочу точно как у вас сделать но у меня почему то выходит только ячейка с2 а внизу ничего нету
как он сделал что при выборе модели, в других окнах автоматически появляются значения?
ВПР, скорее всего.
я делаю максимально проще: в качестве источника данных для проверки данных/список - применяю именованный диапозон = выбранные данные без заголовка нужного столбца. Таблица умная и расширяет именованный диапозон. Пользуйтесь! :)
Подскажите, профессионалы почему у меня не работают кнопки F? Когда я нажимаю F4 у меня вываливается громкость, а ячейки не закрепляются.
Нажми сочитание клавиш fn + esc наслождайся
@@user-ep7tg3sr6h спасибо! Вы мой герой))
@@alenazurabovna4155 рад был помочь, а что бы продолжить громкость регулировать надо теперь нажимать fn + f4 )))
@@user-ep7tg3sr6h Спасибо большое!!!
Сложновато. Но можно разобраться ,после нескольких попыток.
при вводе значения =двссыл("С2") выдает ошибку
В уроке отсутствует важная информация по функции ДВССЫЛ, у неё на самом деле 2 аргумента, второй опциональный он имеет значение ИСТИНА(по умолчанию)если у Вас в экселе ссылки на ячейки именуются по типу А1-А2 или ЛОЖЬ если они именуются по типу R1C1-R2C1(зависит от настроек экселя). Скорее всего при написании функции нужно указать верно второй аргумент и всё заработает.
Щиро дякую ! Пробував побудувати теж саме але дивився на каналі якогось рашистського коуча... Бляха муха.. не знаю який він коуч але він 20 хвилин це пояснював розказуючи що попало показуючи криву таблицю і основні параемтри дій НЕ ОЗВУЧУВАВ а мовчки тис.... Слава вищим силам знайшов адекватну людину . Особиста подяка за Аву та ОФОРМЛЕННЯ каналу! Однозначна підписка!
хм, я не очень понял зачем нужна функция ДВССЫЛ ,если вы пользуетесь умной таблицей? достаточно простой ссылки (=$B$10:$B$18)а диапазон для новых значений будет добавляться умной таблицей автоматически
но действительно, с помощью функции ДВССЫЛ можно и обойтись без умной таблицы прописав что-то вроде : =ДВССЫЛ("B10"&":"&"B"&10+СЧЁТЗ(B:B))
Как убрать повторы значений при выпадающем списке ?
Не подтягиваются данные в чем ошибка?
решение оказалось в следующем уроке.
2021
На другой лист не получается сослаться((((
Спасибо большое ❤️🇺🇦
Ребята! Я нашла видео как автозаполнение сделать!!! Ищите видео "ВПР vs. Индекс & Поискпоз "
Да, но для выпадающего списка можно обойтись и без умной таблицы и двссыл, просто указать весть столбец в качестве источника выпадающего списка. Так при добавлении новых строк данные в выпадающем списке будут добавляться
Блин , почему с середины процесса??
Главная проблема ДВССЫЛ, что когда вы нажмете на кнопку Влияющие ячейки, чтоб посмотреть источник данных, вы ничего не увидите и проверка расчёта превращается в ад.
так быстро говорите 🤦♂️
а в гугл таблицах кто ни будь пробовал?
Нечего не понял спасибо
Слишком быстро и непонятно
Автор не до конца раскрыл тему, упустил ключевые моменты, поторопился. Если взялся за дело, делай качественно.
Ну-ка расскажи, что там упущено? 😅
Почему на ты?