Динамические выпадающие списки, ДВССЫЛ и Умные Таблицы - Функции Excel (7)
ฝัง
- เผยแพร่เมื่อ 20 พ.ย. 2020
- Привет! В этом видео ты узнаешь, как в Excel можно создавать динамические выпадающие списки. Динамичность данных списков заключается в том, что при добавлении или удалении значений из источника данных значения в выпадающем списке также автоматически актуализируются. То есть ручное обновление источника данных при работе с подобными выпадающими списками не требуется.
Видео при этом добавляется в альбом о функциях и формулах в Excel, поскольку для динамических выпадающих списков требуется применение функции ДВССЫЛ, которую мы в этом видео наряду с умными таблицами рассмотрим.
Приятного просмотра! 😊
Файл из видео:
drive.google.com/file/d/1kxiv...
Хочешь поддержать мой канал? Это можно сделать с помощью функции «Спонсорство»:
/ @bilyalkhassenov
Автору огромное спасибо!!!!! Любовь и уважение!
Это лучшие уроки по экзель которые я смотрел. Спасибо большое Билял.
Я из-за вас уже получил повышение на работе Спасибо огромное!
Билалу магарыч будет на кошелек? :)
Спасибо большое за ваши уроки. Всё доступно и понятно
Самые классные уроки. Коротко и ясно.👌
Функция двссыл очень пригодилась в работе, спасибо вам огромное!
Спасибо за полезную информацию
спасибо за урок! очень познавательно)
Спасибо, Билял, рад наслаждаться твоими уроками!!
Жаль, что только один раз, можно лайк поставить.
Огромное спасибо, по-братски! Материал, подача - огонь! И оно заработало:)))
Билял ты объяснил все доступно! Красава
большое спасибо за новое видео :)
Спасибо!
Благодарю. Очень круто
Куда так спешите мастер ??? Не поспеваю за "бегом" Ваших мыслей ... Придется на пониженной скорости смотреть.
Спасибо 🙏 большое
Спасибо ! Класс!!
Спасибо большое
Очень помогло с libreoffice calc
Как всегда - класс 👍👍👍
отличный ролик, только начинаю постигать функционал эксель.
Спасибо)
1000 лайков ! Ты молод и крут!)
Привет! Было бы круто, если бы в описании под видео были ссылки на связанные с этим видео темы: там есть такая фраза, мол об этом расскажу в следующем видео. Спасибо!
Спасибо большое ! А Класс, Цена ,Доступно подтягиваются по функциям следующего видео (я тоже сначала не поняла, как это сделать)
пипец это гениально
Здравствуй, Билял. Подскажи, есть ли возможность сделать список, ссылающийся на умную таблицу но при этом чтобы в этом списке были только уникальные значения. И хотелось бы второй список, который был бы связан с первым списком и выводил бы только те значения, которые соответствуют значениям первого списка. Спасибо
спасибо!
Здравствуйте
Спасибо за видео, все работает. Именно то что было нужно. Можно сюда добавить функцию поиска на динамические выпадающие списки? Очень большой список, неудобно выбирать вручную.
Добрый день!
Подскажите как ускорить функцию ВПР при количестве более 200К+ записей?
Классное видео! Можно ли сделать выпадающий список 2х уровневым? Ну т.е. что бы список на 2 уровне выбора зависел от того что мы выберем на 1 уровне? (Например: мы выбираем 1) сначала машину, а исходя из того какую машину мы выбрали нам предлагается выпадающий список водителей к этой машине? Есть видео о том, как собрать такой эксель файл?
В 365 Office при вводе в обычную ячейку =Автопарк[Модель] ошибка #ЗНАЧ не появляется, вместо ошибки excel возвращает весь массив этого столбца умной таблицы👍 В новом 365 Office это называется переносом
Меня финансовым аналитиком на работу приняли, без опыта, хоть я и учусь на электрика, спасибо вам❤❤
Не выгнали еще ?
Можно присвоить динамическому диапазону имя через диспетчер имён, имя прописывается в проверке значений для выпадающего списка напрямую, эффект будет то-же.
В каком видео вы рассказываете про авто ввод класса, цены за день и доступно. Дайте ссылку
Всё ясно, спасибо огромное! Но я изменила ваши формулы на Если и Впр и тоже всё работает. Почему вы используете Поискпоз, что это даёт?
Дякую!
Красава
здравствуйте, дело в том, что некоторые программы, которые формируют файлы для ексель или некоторые формы для отчетов из министерства, имеют не одну строку или столбец, а множество очень мелких объединенных (обычно столбцов) и на таких ячейках формулы не работают при протягивании. как быть в этой ситуации?
Все это здорово, но не до конца :( Я вот о чем --> А где зависимость значения второго столбца (во втором столбце тоже выпадающий список) от выбранного значения в первом столбце? И т.д. 3-й, 4-й?
Константин, добрый день! Столкнулся с такой же проблемой, подскажите, пожалуйста, получилось ли найти решение?
@@pevoff выпадающий список связывается с другим выпадающим списком тоже с помощью двссыл. Только непонятно, в чем исходный вопрос был. Какие столбцы-то имелись в виду?
@@user-dr5mg3po7o у меня задача сделать порядка 15 связанных выпадающих списков не используя нейм менеджер, т.к. он убьет быстродействие документа. Решение я лично вижу в некой формуле впр (vlookup) которая выдает сразу выпадающим списком все уникальные значения по выданному критерию. По идее тут нужны хорошие знания vba. В интернете я находил впр который выдает строкой уникальные значения по заданному критерию, но из них нельзя быстро и стандартными средствами загнать в выпадающий список.
@@pevoff впр не будет вам искать список. Стандартный впр даст первое значение, соответствующее найденному. Либо там надо извернуться в край. Если вы не хотите использовать именованные диапазоны, то можно попробовать задать их формулой (я как -то крутилась и задала подобный диапазон через подсчет значений, насколько помню. Посмотрела - использовала подсчет, смещение и поискпозиции. Плюс то, что отдельно отобрала уникальные значения.). Либо vba - это будет validation. Там не так сложно и примеры легко гуглятся.
Самолёт)
А где следующие урок, непонятно как данные класс, цена и доступно менялась?
У меня формулы ниже, как на 1.10, автоматически не подтягиваются. Как прописать, чтобы при выборе значения из выпадающего списка в ячейке ниже подтягивались данные из таблицы?
Расскажите пожалуйста как сделать автозаполнение данных по выбранной модели авто.
Билял спасибо большое за урок. У меня только один вопрос, а как ты сделал, что при выборе модели в выпадающем списке подтягиваются и остальные данные?(цена, доступно). Я проделал всё как ты и говорил, но со своей таблицей. Выпадающий список удалось сделать, но остальные данные не подтягиваются. Спасибо за ответ!
присоединяюсь к вопросу
Именно это я и искал
Видел одну видео по этому тему там через индекс связали все остальные инфу.
У меня всё через впр подтягивается
Присоединяюсь к вопросу. Мне надо чтобы по выбору работы в первой ячейке строчки - вся строка заполнялась автоматически.
на 7:50 при удалении строк таблицы у тебя таблица соответственно уменьшается, а у меня получаются пустые строки, которые так же отображаются в выпадающем списке, может надо как то по особенному удалять ? (Я с Ctrl выделяю всю строку и жму Del)
Подскажите пожалуйста, я сделал динамический список, при выборе авто, остальное не подтягивается, в чем причина я не понимаю
Добрый день! Спасибо большое за ваш труд! Почему то у меня не получается создать выпадающий список, когда название умной таблицы отличается от названия столбца. А вот если они одинаковые, то получается. Почему так? (
Спасибо большое, и прошу помочь советом: после создания Умной таблицы, ссылки структурированные не появляются. Остаются как и прежде =С1, например. С чем это м.б. связано?
у меня тоже самое, нашли ответ на вопрос?
А вот как сделать чтобы таблица сама уменьшалась? Или она по умолчанию должна это делать? А то я пробовал но таблица не уменьшается а остается такой какой была до удаления значений
Плз дайте ссылку на обещанное в начале видео с подтягиванием 2го и 3го столбца при выборе 1го.
Нашли это видео?
Нет
Привет. Подскажи пожалуйста как в формулу добавить функцию копирования формулы в списке?
Как можно сделать чтобы динамический выпадающий список формировался по условию? Например: есть список 2 колонки: 1-категория, 2-наименование. На отдельном листе я в ячейке пишу слово из категории и в соседней ячейке появляется выпадающий список Наименований отсортированных по этой категории
Как работает верхняя таблица? Как три нижнии строчки понимают какую информацию им подставлять?
Класс! Спасибо автор. Но есть проблема. Данные списка передаются как значение, а не ссылка. Если мы заполним ТАБЛИЦУ данными, например, "AUDY", а потом увидим ошибку, исправим "AUDY" на "AUDI", то уже заполненные старые данные с ошибкой не будут автоматом исправляться. Нужно будем мышкой выбрать повторно. Может знаете, как это побороть? Думаю идти в направлении неких текстовых ключей для каждой строки, которые будут дополнительным столбцом в "справочнике", они должны быть уникальными и не меняться в течение жизни строки. Но это теория, практики пока не получается 😢
@BilyalKhassenov вопрос схожий - а КАК сделать АВТООБНОВЛЕНИЕ связанных раскрывающихся списков ?!
Например - когда я выбираю Категорию ==>> то в ячейке "Товары из Категории" СРАЗУ ЖЕ выбирался 1-й товар из списка ?!!!!!!!!!
Просто обыскался уже сегодня.... Можете подсказать:
Есть два столбца:
1. Фио (имеются дубляжи)
2.серия паспорта человека из п. 1
Надо найти те Фио для которых введены разные серии паспортов.
То есть когда для одного и того же Петрова введены разные серии паспортов.
Нашёл решение?
Здравствуйте!
Я, конечно, поздно отвечаю, но, наверно, лучше поздно, чем совсем не ответить 😊
Мне вот такое решение сразу в голову пришло:
1. Выделяете оба столбца
2. Во вкладке «Данные» нажимаете на «Удалить дубликаты»
3. В открывшемся окне «Удалить дубликаты», в поле «Колонны», ставим галочки напротив названия этих двух столбцов (ФИО и номер паспорта). Другие столбцы в этом окне галочкой не отмечаем! Нажимаем на ОК
4. Теперь дописываем еще один столбец с формулой СЧЁТЕСЛИ. В первом аргументе указываем закрепленный с помощью F4 диапазон с ФИО, а во втором аргументе отмечаем ФИО текущей строки.
5. Протягиваем формулу
6. Фильтруем значения в новом столбце: убираем единицы
В итоге так и получим лишь ФИО со всем вариациями приписанных им номеров паспорта.
Надеюсь, получится понять такое объяснение. В любом случае - я сниму на эту тему видео на следующей неделе. Спасибо Вам за идею для видео!
С уважением,
Билял
Не знаю, пришло Вам оповещение о моём сообщении с решением, которое я только что отослал. Поэтому коротко решил и Вам написать, чтобы Вы увидели :)
@@BilyalKhassenov спасибо :) вопрос решал очень долго через сводные таблицы.. Обязательно посмотрю ваше видео
Добрый день. Я пишу макрос на 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 )))! Удачи
А почему у меня без этой формулы ДВССЫЛ динамический список обновляется в выпадающем списке?
а если одни и те же модели отличаются объемом двигателя которые прописаны в соседнем столбце?
Билял не совсем понятно с Cntl+Shift+Enter, что вы сделали таким действием, к тому же, у меня ничего не поменялось, когда нажал эти кнопки. И с листом тоже не прошло. Тест двссыл не появился, ссылка происходит на 1 лист в действиях, несмотря на существующий 2. приходится в ручном режиме в слове Лист1 менять на Лист2.
нужно нажимать Shift+Ctrl+Enter, обратите внимание на последовательность. СНАЧАЛА Shift
спасибо! а как связывались ячейки с3 с4 с5?
Скорее всего через "ВПР()"
объясните пожалуйста формулы в ячейка с3 с4 с5, так как я хочу точно как у вас сделать но у меня почему то выходит только ячейка с2 а внизу ничего нету
как используя ДВССЫЛ при обращении к умной таблице для создания выпадающего списка ссылаться не на название шапки умной таблицы а номера столбца?
Как сделать так, чтобы у меня раскрывался список из конкретной ячейки? Пример есть таблица за доходами. В одной из строк доход , полученный за продажу фруктов - рядом записан Тотал. Нужно сделать так , чтобы из этой ячейки раскрывался список из чего состоит этот Тотал- яблоки = 5 рублей ; груши = 7 рублей и так далее.
Суть - таблица из множества значений , где по каждой записан финальный Тотал. Но каждое значение состоит из других значений , которые тоже надо отобразить.
Сделала я таблицу как в начале видео,нажала ctrl+T, сделала Проверка данных по списку и выбрала столбец с моделями авто. И все. У меня на этом этапе УЖЕ новые модели в таблице сами подтягиваются. Без функции двссыл.
Ребята! Я нашла видео как автозаполнение сделать!!! Ищите видео "ВПР vs. Индекс & Поискпоз "
Щиро дякую ! Пробував побудувати теж саме але дивився на каналі якогось рашистського коуча... Бляха муха.. не знаю який він коуч але він 20 хвилин це пояснював розказуючи що попало показуючи криву таблицю і основні параемтри дій НЕ ОЗВУЧУВАВ а мовчки тис.... Слава вищим силам знайшов адекватну людину . Особиста подяка за Аву та ОФОРМЛЕННЯ каналу! Однозначна підписка!
Билял, Cntl+Shift+Enter у меня, не меняют вообще ничего. Что не так?
я делаю максимально проще: в качестве источника данных для проверки данных/список - применяю именованный диапозон = выбранные данные без заголовка нужного столбца. Таблица умная и расширяет именованный диапозон. Пользуйтесь! :)
как он сделал что при выборе модели, в других окнах автоматически появляются значения?
ВПР, скорее всего.
Сложновато. Но можно разобраться ,после нескольких попыток.
Привет Билял. Как дела?
решение оказалось в следующем уроке.
Добрый день. Видео короткое и понятное для простых списков. Однако вы не раскрыли тему как быть с выпадающим списком если значения в столбце "Модель" повторяются, к примеру при создании таблицы по продажам. Тогда в выпадающий список будут попадать одинаковые модели автомобилей и использование примеров приведенных в этом ролике становится некорректным. Пробую сделать небольшую базу данных в эксель и сейчас как раз столкнулся с такой проблемой.
Здравствуйте! Вы нашли ответ на свой вопрос??? как сделать, что бы пропадали?
@@sergeyisaev7004 День добрый. Моих знаний и времени не хватило. Найденные в интернете примеры не помогли. База данных получилась только для склада (отслеживание движение товара). Добавить к получившейся базе деньги по покупке и продаже товара не удалось, а соответсвенно вычислить прибыль тоже. Хотелось бы у вас на канале увидеть поэтапные уроки по созданию БД в эксель. Спасибо за ответ.
Если у вас две одинаковые модели по разной цене нужна пометка для отличия одного от другого, например инвентарный номер или VIN машины. В противном случае два значения объединяются общей чертой и это уже группа. А для группы нужен второй уровень динамического списка. Проще говоря: выбираете модель и а в соседнюю ячейку подтягивается список инвентарного номера машины по которому, далее в соседнюю ячейку подтягивается стоимость авто.
@@Frosya937 Добрый день. А можете прислать короткий пример таких списков. Например продажа печенья.
Подскажите, профессионалы почему у меня не работают кнопки F? Когда я нажимаю F4 у меня вываливается громкость, а ячейки не закрепляются.
Нажми сочитание клавиш fn + esc наслождайся
@@user-ep7tg3sr6h спасибо! Вы мой герой))
@@alenazurabovna4155 рад был помочь, а что бы продолжить громкость регулировать надо теперь нажимать fn + f4 )))
@@user-ep7tg3sr6h Спасибо большое!!!
при вводе значения =двссыл("С2") выдает ошибку
В уроке отсутствует важная информация по функции ДВССЫЛ, у неё на самом деле 2 аргумента, второй опциональный он имеет значение ИСТИНА(по умолчанию)если у Вас в экселе ссылки на ячейки именуются по типу А1-А2 или ЛОЖЬ если они именуются по типу R1C1-R2C1(зависит от настроек экселя). Скорее всего при написании функции нужно указать верно второй аргумент и всё заработает.
2021
Не подтягиваются данные в чем ошибка?
хм, я не очень понял зачем нужна функция ДВССЫЛ ,если вы пользуетесь умной таблицей? достаточно простой ссылки (=$B$10:$B$18)а диапазон для новых значений будет добавляться умной таблицей автоматически
но действительно, с помощью функции ДВССЫЛ можно и обойтись без умной таблицы прописав что-то вроде : =ДВССЫЛ("B10"&":"&"B"&10+СЧЁТЗ(B:B))
На другой лист не получается сослаться((((
Как убрать повторы значений при выпадающем списке ?
Спасибо большое ❤️🇺🇦
Да, но для выпадающего списка можно обойтись и без умной таблицы и двссыл, просто указать весть столбец в качестве источника выпадающего списка. Так при добавлении новых строк данные в выпадающем списке будут добавляться
Главная проблема ДВССЫЛ, что когда вы нажмете на кнопку Влияющие ячейки, чтоб посмотреть источник данных, вы ничего не увидите и проверка расчёта превращается в ад.
так быстро говорите 🤦♂️
а в гугл таблицах кто ни будь пробовал?
Нечего не понял спасибо
Слишком быстро и непонятно
Автор не до конца раскрыл тему, упустил ключевые моменты, поторопился. Если взялся за дело, делай качественно.
Ну-ка расскажи, что там упущено? 😅
Почему на ты?