1:48 Способ 1. Именованные диапазоны и функция ДВССЫЛ 5:11 Способ 2. Умные таблицы и функция ДВССЫЛ 9:12 Способ 3. Автоматическое связывание функциями СМЕЩ и ПОИСКПОЗ
Нарвался на этот контент! Шарахнул инсайт! Отходил мин. 20! Николай ты реально крут! Подписался! Отправил ссылку Бухгалтеру! Огромное человеческое спасибо!
Николай, спасибо вам за все без исключения видео. Искала решение одной проблемы и нашла его в одном из ваших роликов. И такое началось... :-) Мне так понравилась подача материала и приемы, которые не описывают в книгах, что я стала смотреть все видео подряд. Очень познавательно, но еще больше поучительно. Каюсь, без ваших подсказок я раньше делала некоторые расчеты часами. Теперь опасаюсь, работодатель может подумать, что мне нечем заняться на рабочем месте :-)))
Я посмотрел данное видео и потратил 16 минут личного времени, и понимаю что теперь могу сэкономить своё и других людей, время обработки служебной информации. Вы один из лучших мастеров, в работе с данным инструментом!!!
Николай, благодарю за подробное описание. Очень полезная информация. Прошу описать как продлить (скопировать) в 3 способе ячейку "товар" так чтобы она не ссылалась на материнскую ячейку "Категория"
Николай, благодарю за ценную информацию! Очень помог третий способ. В него я ещё интегрировал нюанс второго способа, т.е. списки библиотеки перевести в таблицу, чтобы при поступлении новых данных (с последующей сортировкой) - они добавлялясь в "умный диапазон" и не приходилось делать корректировки
Spasibo vam bolshoe, vashi vidoe uroki ochen iasnie, legkie dlia ponimania i vsio chetko oboznacheno. vsega polchaiu bolshuiu pozu !!!!! Super video ot Super Prepodovatelia!!!
Спасибо Николай, всё очень понятно. Не могли бы вы объяснить как продлить лист выпадающих свянных списков на другие строки с теми же данными.... На данный момент приходиться вручную вводить ссылки...
Отличные объяснения: четко, по делу, без воды, не скороговоркой. Был бы признателен, если бы появился видеоурок по следующей проблеме. Есть некий список, скажем: без ворот, откатные ворота, распашные ворота, складчатые ворота, роллет. Ниже есть выбор количества в штуках. Мне бы хотелоь, если выбор сделан "без ворот", то в графе количество появляется 0, во всех остальных случаях чтобы можно было ввести любое число.
1/2. «Сказ о том, как решить проблему в способах №.№ 1 и 2, когда значения в столбце Категория (первый выпадающий список) представляют собой набор слов, разделенных пробелом» В ролике этой информации нет, а в комментариях Николай дал решение этой проблемы, но как бы вскользь и между прочем. Мне же эта информация кажется достаточно важной и интересной, чтобы обратить на неё более пристальное внимание. Ну и вот, спешу порадовать таких же жирафов, до которых (как и до меня) не сразу дошло как ЭТО делать. Чтобы сразу, на конкретном образце, посмотреть как эта штука фунциклирует, скачайте файл-пример Николая и на листе «Умные таблицы» проделайте следующие манипуляции: 1. В ячейке H11 замените «Фрукты» на «Мои ну очень очень любимые фрукты ягоды», получив ту самую проблемную категорию товара, состоящую из нескольких слов и разделенную пробелами (пока только ими). Название столбца «Фрукты» в ячейке A3 не трогаем, так как оно нигде не используется и ни на что не влияет, но нам нужно изменить название данной умной таблицы, записав его сплошняком без пробелов или через нижнее подчеркивание: «МоиНуОченьОченьЛюбимыеФруктыЯгоды». или «Мои_ну_очень_очень_любимые_фрукты_ягоды». Первый вариант является более универсальным, поэтому настоятельно рекомендую использовать именно его. В чем заключается универсальность расскажу чуть ниже. Наконец, на последнем шаге выделяем ячейку K5, в которой расположен связанный выпадающий список, вызываем окно «Проверка вводимых значений» и в поле «Источник» вместо =ДВССЫЛ($H$5) пишем =ДВССЫЛ(ПОДСТАВИТЬ($H$5;" ";"")). Пустота в третьем аргументе функции ПОДСТАВИТЬ как раз и убирает все пробелы из ячейки с нашими любимыми фруктами. И даже если вы случайно поставили лишний пробел в начале ячейки, в конце ячейки или поставили два пробела между словами, то всё равно всё будет работать корректно. Усё! Можно пользовать. Когда знаешь - всё просто! (Вроде кто-то из великих любит так приговаривать))) Да, пока далеко не ушли от этой темы, надо бы еще раз подчеркнуть, что ссылаться на ячейку H5 целесообразней все-таки не в виде абсолютной ссылки $H$5, а в виде $H5. В этом случае связанный выпадающий список будет без проблем привычным нам образом протягиваться вниз по столбцу. 2. Теперь самую малость, буквально капелюшечку, усложним формат написания категории товара и отразим его в ячейке H11 следующим образом: «Мои ну, очень-очень любимые фрукты/ягоды». Кроме пробела у нас появились дополнительные три символа, которые нужно зачистить: запятая, тире и слэш. Название умной таблицы остается прежним, как в предыдущем пункте, а вот метод зачистки будет чуток усложнен. необходимо использовать несколько функций ПОДСТАВИТЬ, вложенных друг в друга: =ДВССЫЛ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ($H5;"/";"");"-";"");",";"");" ";"")). Таким вот нехитрым способом вы можете избавиться от любых неуставных символов в ваших категориях, при этом последовательность замен значения не имеет. Предполагаю, что поиск и удаление символов звездочка (*), вопросительный знак (?) (если, конечно, функция ПОДСТАВИТЬ вообще допускает использование подстановочных знаков), а также кавычек и других спец. символов в диалоговом окне «Проверка вводимых значений» осуществляется по тем же правилам, как если бы мы это делали просто в обычной ячейке на листе. Проверять как-то лениво, так что сами, сами. 3. Если же вы не воспользовались моим мудрым советом и записали имя таблицы через нижнее подчеркивание, то я вам не завидую. Возможные варианты развития событий: Вариант 1. Поскольку между словами «ну» и «очень» стоит запятая и пробел, то можно написать имя таблицы через два нижних подчеркивания: «Мои_ну__очень_очень_любимые_фрукты_ягоды». В этом случае мы просто без дополнительных премудростей прописываем нижнее подчеркивание в третьем аргументе функции ПОДСТАВИТЬ. ...
2/2. Продолжаем разговор. Вариант 2. В имени таблицы все слова разделяем одним нижним подчеркиванием, но в одной из вложенных функций ПОДСТАВИТЬ заменяем не просто одиночную запятую или одиночный пробел, а именно сочетание запятой с пробелом на нижнее подчеркивание: ", " на "_". При этом, нужно обязательно следить за последовательностью замен: сначала должна замениться запятая с пробелом, а только потом просто запятая или просто пробел. В противном случае работать не будет. Плюс сохраняется опасность того, что в ячейке с категорией товара случайно окажется лишний пробел. Опять же фурыкать не будет. Ну, или для подстраховки на всякий случай оберните все ваши многочисленные ПОДСТАВИТЬ в СЖПРОБЕЛЫ. Тогда шансы на success резко возрастут. В общем, вероятность негативного влияния антропогенного фактора на конечный результат на столько велика, что я даже не стану приводить пример такой формулы, дабы не вводить в соблазн слабых духом. Пишите имя таблицы сплошняком без подчеркиваний - это самый надежный вариант. А самое грустное во всей этой истории заключается в том, что все мои разглагольствования смысла не имеют и никому не нужны, так как существует волшебный и замечательный способ № 4 (лист «Без сортировки»), который делает всё то же самое, что и первые 2, но лишен их недостатка в виде ручного создания и поддержания в актуальном состоянии именованных диапазонов под каждую категорию. Если вдруг вы начали продавать вместо овощей овощебазы, то достаточно в ячейке E2 изменить название категории и можно струячить дальше, никаких дополнительных телодвижений больше не понадобится. Опять же, 4-му способу пофиг из скольких слов состоит название категории и какими символами они разделены. Плюс справочники легко масштабируются как по строкам, так и по столбцам. Короче, не Зависимые списки, а непрерывный праздник какой-то! Коротенечко я бы так сформулировал основные правила и рекомендации при работе по 4-му способу: - при создании справочника не используйте умные таблицы и автофильтр. И то, и другое скорее будет мешать, чем приносить пользу; - сортируйте столбцы справочника стандартными средствами: Данные> Сортировка и фильтр. Это даст возможность упорядочить каждую подкатегорию независимо от соседних столбцов; - если вы не очень четко представляете как работают глупые именованные и неименованные диапазоны и не знаете как подпихнуть в имеющийся справочник новую категорию = новый столбец, то сразу еще на этапе его (справочника) создания добавьте справа запасной столбец. Собственно можете его так и обозвать «Запасной столбец». Правда тогда в первичном выпадающем списке в самом низу всегда будет вылезать пункт «Запасной столбец», да и хрен бы с ним. Ну, или сотрите заголовок столбца - будет вылезать пустой пункт. Главное, что вы будете добавлять в справочник новые столбцы всегда перед этим запасным, а, следовательно, новая категория и подкатегории гарантированно будут попадать в основной и зависимый списки; - во второй функции СМЕЩ предусмотрите адекватный запас для СЧЁТЗ. Если вы предполагаете, что максимально возможное количество позиций в самой большой подкатегории будет не больше 1000, то вместо 10 пропишите цифирьку 2000, не жадничайте. Сколько-нибудь существенно Excel это не загрузит, зато вы совершенно точно будете избавлены от головной боли в части расширения справочника по строкам; - забудьте про способы № 1 и 2))). THE END. Текст, конечно, получился длинноватый, ну, да что вы хотите, уважаемые бухгалтеры и прочие аналитики, от 100%-ного гуманитария? Против природы не попрёшь!)))
Спасибо Николай. Очень классно дается материал. И практично. Единственное но. Третий вариант сложный для работы, т.к. при добавлении новых позиций каждый раз придется пересортировывать таблицу
Николай, все круто, но еще бы юзал Ctrl-C /Ctrl-V - для переименования Таблиц и другие приемчики с коментами (которые обычно импользуешь в работе) - и было бы совсем ок! Еще желательно снизу видео писать названия функций на англ! и русском (кому - нужно) - чтобы видео можно было проще искать и вспоминать! Спасибо (не первый раз натыкаюсь на видео - пришлось Подписаться)!!! =)
Для тех кто пробовал вариант с умной таблицей: При работе с умными таблицами не обязательно создавать несколько таблиц. Для списка первого уровня: =ДВССЫЛ("ИМЯ_ТАБЛИЦЫ[#Заголовки]") - формируем первый выпадающий список по заголовкам таблицы. Для списка второго уровня: =ДВССЫЛ("ИМЯ_ТАБЛИЦЫ["&ссылка на ячейку первого выпадающего списка&"]") - формируем второй выпадающий список по столбцам таблицы. Достоинства - использование преимущества умных таблиц как в отношении добавления как строк так и столбцов. Возможность масштабирования, ведь значением второго выпадающего списка может оказаться имя следующей таблицы . Недостатки - Не желательно использование выпадающих списков и таблицы на одном листе.
Добрый день! Присоединяюсь к вопросу: Как нужно делать способ 3, если "желтое" поле выбора категорий и "зеленое" поле выбора товаров - расположено на одном листе, а таблица с исходными данными - на другом? Подскажите, пожалуйста.
Спасибо! Очень интересно! Искала, как работать со списками, и нашла Ваше видео. Очень помогло, все получилось. Но, к сожалению, так и не получается создать список из нескольких столбцов. Т.е. когда от первого столбца зависит содержимое второго, а от второго зависит содержимое третьего. В третьем столбце список не работает.
Очень подробные уроки, хотя такое редко встретишь. А как провернуть такое с элементами Active x? Чтобы следующее поле со списком выдавало в зависимости от выбора предыдущего
Николай спасибо, понятное видео. У меня все получилось. Но есть вопрос. Как сделать третий способ если данные расположены на другом листе, не получается. Подскажите.
Вопрос ) про "способ 3" как сделать или скопировать "категории товар" ( допустим нужно ещё связанные выпадающие списки "категори товар" такие же самые просто клонировать или копировать ещё раз 20 30 что бы не проделывать процедуру с диспетчером имени снова и снова овер 30 раз 😕
Здравствуйте Николай! Подписавшись на Вас, не пожалела, одним словом волшебник. Вопрос такого характера, как сделать связанный выпадающий список с автоматическим заполнением других ячеек. Например: есть справочник с названием тр/средств, есть справочник летняя и зимняя норма списания, есть справочник значений к летней и зимней норме для каждого авто, как сделать и связать выпадающие списки норм списания к авто, и при выборе летней или зимней нормы автоматически вставлялось значение к определенному авто.Спасибо
А как сделать чтобы при выборе значения из списка соседние(или выбранные) ячейки заполнялись автоматически ??? Допустим выбрал какую-то еду из списка, а калории /жиры /белки/углеводы заполнились автоматически по заранее составленной таблице. Подскажите плиз
Эти важнейшие функции и способы могут заменяться альтернативными функциями ВПР и Индекс, (как кому удобно ) а самое замечательное их прикладное значение - они успешно РЕШАЮТ ТРУДОЕМКИЕ ЗАДАЧИ не только в бухгалтерском учете и менеджменте, они - легко используются при построении предложений в английском языке, где строгий порядок слов в предложении ... а списки - могут быть и на отдельных файлах ! Почему то коучи - скромничают и не делятся этими знаниями с русскоязычной аудиторией.
Николай, можно усовершенствовать второй способ и обойтись единственной таблицей вместо трёх - использовать в ДВССЫЛ ссылки на заголовки и на колонки таблицы. Такой способ будет максимально расширяемым и удобным. Пример - dl.dropboxusercontent.com/u/6795309/excel/excel-nested-lists.xlsx
+Алексей Марьин Не всегда одна таблица вместо трех удобна. При этом способе если у вас в одном столбце 20 наименований, а в соседнем 3, то в выпадающем списке с тремя наименованиями будут: три наименования и 17 пустых позиций. И иногда эти пустые ячейки будут прямо в середине выпадающего списка (в зависимости от того, как пользователь добавит новую категорию).
Все три примера работают, если жёлтая ячейка (H5) фиксированная. На практике категория и товар вводятся в таблицу в хронологическом порядке (вместе с другими данными, не имеющими отношения к данному примеру). Допустим, категория вводится в столбец C, товар в столбец D). Разумеется, никакой сортировки при вводе нет. Если для выпадающего списка Категория можно сделать отдельную табличку в сторонке (как у Вас Н11-Н13), то с товаром сложнее. Нужно чтобы выпадающий список товаров формировался не из фиксированной ячейки (у Вас Н5), а на основе только что введённой категории (допустим, в ячейку C465). Как сформировать правильный выпадающий список в ячейке D465 для этой категории?
Добрый день) Спасибо за видео. Подскажите, а есть возможность сделать вывод списка на основе двух параметров ( а не на одном, как показано видео. Например, есть группы "Фрукты" и "Овощи" . в "Фруктах" в ней категории "цитрусы" и "яблоки, груши". А в "цитрусы" список всех кто подходит (апельсины, мандарины и т.д.)
Спасибо, все понятно. Но есть вопрос. У нас в основной таблице поменялось наименование ячейки. Как сделать так, чтобы она изменилась и в других ячейках когда мы её выбирали из выпадающего списка?
Николай, спасибо вам доступное объяснение. Хотелось бы узнать возможно ли на одном листе собрать информацию о тарифах курьерской службы (допустим на доставку чего-либо на разные расстояния) двух или трех компаний, анализа? Заранее благодарен.
Здравствуйте! Есть проблема при создании именного диапазона на 1 части методики 3:00 минута. Когда выделяю все ячейки и после введение название в адрес ячейки, выходит такое предупреждение: "Необходимо ввести допустимую ссылку для перехода или допустимое имя для выделения". Хотя на первую колонку сделал без никаких проблем, а данная проблема выявляется на вторую колонку. Спасибо за внимание!
Добрый день. При добавлении какого-либо товара, получается, нужно " засовывать " в середину нужного списка, чтобы в именной формуле не менять диапазоны. А если новая категория, диапазоны в формуле надо все-таки подвинуть согласно новым границам, верно?
Николай, скажите пожалуйста как решить такую задачу: Мне нужно чтобы при выборе данных из списка (срок изготовления на выбор) подставлялась скидка (наценка) от общей суммы из ячейки. Есть ли видео как это можно сделать?
Николай @planetaexcel здравствуйте! вот СВЯЗАННЫЕ списки - у меня СРАЗУ получились , благодарю 😀👍 Возник вопрос = 8:33 = а *можно ЛИ* сделать так, чтобы при смене значения в ОРАНЖЕВОЙ ячейке (с фруктов -> на зелень), в Зелёной ячейке *СРАЗУ БЫ менялись и продукты из первой строки каждого столбца ?!* 🙋 У меня таблица 9х9, и когда я в ней меняю все 81 ячейку с "фрукты" на другие категории, то ВРУЧНУЮ менять значения из ЗЕЛЁНОЙ ячейки, с "Арбузов" -> на "Петрушку" ===== ОЧЕНЬ УТОМИТЕЛЬНО (!) ...... позже, даа - придётся ручками менять все "Арбузы" на "Петрушки, или на "Картошки" , но ПЕРВИЧНО =>> всё же хочется чтобы значения в зелёной ячейке АВТОМАТОМ менялись на 1-е значение соответствующего столбца при смене Категории в "Оранжевой ячейке" (!!!) Можно ЛИ как-то это сделать ??!
Подскажите пожалуйста Хочу сделать таблицу. Создать список Месяцев, и чтобы при выборе допустим Декабря, в строке с 1 по 31 ячейку отображалась дата. И при смене месяца, чтобы количество заполненых ячеек менялось в зависимости от количества дней в месяце
Добрый день, спасибо все подробно. Но у меня вопрос решение которого не нашел. Я научился делать зависимый выпадающий список в одной ячейке. Но мне нужно "протянуть" этот список вниз. Как это сделать? Категория - Товар Категория - Товар Категория - Товар
Здравствуйте! А возможно применить автозаполнение к ячейке с выпадающим списком? Например, у меня в выпадающем списке 500 фамилий и если набирать начало фамилии, происходило бы автозаполнение
Спасибо, очень доходчиво и весьма понятно, но вопрос все же остался. Типография: К примеру, есть 2 уровня цен для клиентов (постоянные и нет). Список материалов и цена на какую-либо позицию зависит от разрешения печати (360, 720, 1440...). как сделать чтобы в зависимости от выбора категории клиента, материала, разрешения в какой либо ячейке подтягивалась соответствующая цена. Заранее СПАСИБО!
Приветствую, Николай! Хочу поднять правую руку и спросить следующее: "Данный вопрос касается второго способа. Когда вы приводите в пример, категории товаров, то обязательно чтобы между ними был зазор в один столбец"?
🎉🎉🎉 А можно ли сделать в первом выбор допустим "труба", а дальше три выборные ячейки из таблицы длинна высота толщина существующие под этот тип трубы? 🎉🎉🎉
Как всегда все доходчиво) Николай, а работает ли все это внутри умной таблицы? У меня вот такая ситуация таблицы с нужными списками на вспомогательном листе (всегда делаю его)...на нем если делать связанные списки все работает, НО как только пытаюсь сделать все тоже самое на другом листе внутри умной таблицы...ничего не работает....Есть столбец например Поставщик и вот на основе его выбора надо выбирать Товар и потом ВПРом цена подтягивается
Хотя спасибо)) Уже сам решил проблему...В строке где прописывал Источник списка в ДВССЫЛ изменил тип диапазона с абсолютного на смешанный (вместо $A$4:$A10, поставил $A4:$A10)
Николай, а про гугл таблицы такое можете сделать? Мне в облаке надо, многопользовательское. Я в обычном экселе самостоятельно реализовал что-то похожее на одно из ваших решений (там это просто, на мой взгляд), но в гугле подобный функционал сделан как-то тупее. Но вдруг я чего-то упустил.
Николай, как сделать что бы в зелёной ячейке появлялся не выпадающий список, а полный список, например в жёлтом окне выбираем "овощи" и на зелёном поле видим все товары расписанные на 8 строк получается, 8 зелёных строк с наименованием. Спасибо за ответ.
Последний, третий способ. сказано "самый универсальный из всех", но ведь в нём поиск сугубо по определённым ячейкам, а новые фрукты/овощи/зелень не войдут во второй список (без корректировки формулы каждый раз)? См. 12:50, в функции ПОИСКПОЗ указаны определённые ячейки, а не имя диапазона. 3й. способ уступает второму способу в универсальности, т.к. во 2 способе ссылки на именованные умные таблицы, автоматически включающие новые данные.
Третий способ универсальнее. Формулу менять не надо, просто вставляешь строчку внутрь талбицы, протягиваешь название первого столбца, например "овощ", и пишешь что за овощ во втором, и новый овощ появится в зависмом выпадающем списке. Воторй способ хорош, когда мало категорий, как в примере (фрукты, овощи и зелень - всего три), но когда категорий много, третий способ предпочтительнее. Например в моем случае 23 химических продукта, каждый имеет от одного до 5 растворителей...да я помру делать 23 отдельные умные таблицы.
1:48 Способ 1. Именованные диапазоны и функция ДВССЫЛ
5:11 Способ 2. Умные таблицы и функция ДВССЫЛ
9:12 Способ 3. Автоматическое связывание функциями СМЕЩ и ПОИСКПОЗ
Спасибо, Вадим :)
@@planetaexcel Здравствуйте, сделал все так как и вы, но выходит при вычислении "Источник" возникает ошибка, что делать? может где то ошибся?
16 Минут данного видео, сохранят мне уйму времени, полагаю не только мне. Большое, человеческое спасибо!
Нарвался на этот контент! Шарахнул инсайт! Отходил мин. 20! Николай ты реально крут! Подписался! Отправил ссылку Бухгалтеру! Огромное человеческое спасибо!
Ой, так легко доносите информацию! ОГРОМНОЕ ВАМ СПАСИБО! ЖЕЛАЮ ВСЕХ БЛАГ!
Николай, спасибо вам за все без исключения видео. Искала решение одной проблемы и нашла его в одном из ваших роликов. И такое началось... :-) Мне так понравилась подача материала и приемы, которые не описывают в книгах, что я стала смотреть все видео подряд. Очень познавательно, но еще больше поучительно. Каюсь, без ваших подсказок я раньше делала некоторые расчеты часами. Теперь опасаюсь, работодатель может подумать, что мне нечем заняться на рабочем месте :-)))
:)
Большое спасибо! Благодарности нет границ! Только у вас нашла нужную информацию!
Я посмотрел данное видео и потратил 16 минут личного времени, и понимаю что теперь могу сэкономить своё и других людей, время обработки служебной информации. Вы один из лучших мастеров, в работе с данным инструментом!!!
Николай, благодарю за подробное описание. Очень полезная информация.
Прошу описать как продлить (скопировать) в 3 способе ячейку "товар" так чтобы она не ссылалась на материнскую ячейку "Категория"
Подача просто супер! Всё ясно и понятно, без лишних слов!!! 100℅ лайк и подписка!
Николай, благодарю за ценную информацию! Очень помог третий способ. В него я ещё интегрировал нюанс второго способа, т.е. списки библиотеки перевести в таблицу, чтобы при поступлении новых данных (с последующей сортировкой) - они добавлялясь в "умный диапазон" и не приходилось делать корректировки
Огpомное спасибо за ваши труды!
Удачи, здоровья и благополучия Вам!
Огромное спасибо. Очень ценный урок. Воспользовался информацией из этого видео для решения своих задач. Очень помогло.
Обожаю Ваш сайт и подсказки! Спасибо, что Вы есть!
Инструкции у всех примерно одинаковые, но ваш голос приятнее. Ищу только ваши лайфхаки по экселю)
Спасибо огромное!! Несколько вечеров над задачей мучился!! С Вашим роликом за час все сделал. Удачи Вам!!
Spasibo vam bolshoe, vashi vidoe uroki ochen iasnie, legkie dlia ponimania i vsio chetko oboznacheno. vsega polchaiu bolshuiu pozu !!!!! Super video ot Super Prepodovatelia!!!
Большое спасибо за видео! Всё предельно понятно изложено!
Гениально. Спасибо, Николай.
Николай, большое Вам спасибо. Вы мне очень помогли. Без видео разобраться не получалось.
Vse vashi video ochen poleznie i aktualnie, spasibo za vashi starania
Спасибо!!! Очень хорошо вы объясняете, просто и доходчиво!
Спасибо вам большое, вы очень облегчаете мою аналитическую работу)
ОГРОМНЕЙШЕЕ СПАСИБО!!! ИСКАЛА ИМЕННО ЭТУ ПОДСКАЗКУ!!! 🙏🏼🙏🏼🙏🏼
Шикарно! В который раз убеждаюсь в полезности функции "ДВССЫЛ" ;)
Спасибо Николай, всё очень понятно.
Не могли бы вы объяснить как продлить лист выпадающих свянных списков на другие строки с теми же данными....
На данный момент приходиться вручную вводить ссылки...
Здравствуйте Николай , хотел спросить. Не могли бы вы объяснить как сделать для продажа одежды такой же программа ?Спасибо большое
Офигенно!! 👍 желаю добиться больших высот 🤟
Спасибо большое! Просто, наглядно и очень полезно!
Отличные объяснения: четко, по делу, без воды, не скороговоркой. Был бы признателен, если бы появился видеоурок по следующей проблеме. Есть некий список, скажем: без ворот, откатные ворота, распашные ворота, складчатые ворота, роллет. Ниже есть выбор количества в штуках. Мне бы хотелоь, если выбор сделан "без ворот", то в графе количество появляется 0, во всех остальных случаях чтобы можно было ввести любое число.
Шикарно. 3й способ прям хорош. Люблю такие замороченные вещи.
Спасибо, очень полезная информация.
1/2.
«Сказ о том, как решить проблему в способах №.№ 1 и 2, когда значения в столбце Категория (первый выпадающий список) представляют собой набор слов, разделенных пробелом»
В ролике этой информации нет, а в комментариях Николай дал решение этой проблемы, но как бы вскользь и между прочем. Мне же эта информация кажется достаточно важной и интересной, чтобы обратить на неё более пристальное внимание. Ну и вот, спешу порадовать таких же жирафов, до которых (как и до меня) не сразу дошло как ЭТО делать.
Чтобы сразу, на конкретном образце, посмотреть как эта штука фунциклирует, скачайте файл-пример Николая и на листе «Умные таблицы» проделайте следующие манипуляции:
1. В ячейке H11 замените «Фрукты» на «Мои ну очень очень любимые фрукты ягоды», получив ту самую проблемную категорию товара, состоящую из нескольких слов и разделенную пробелами (пока только ими).
Название столбца «Фрукты» в ячейке A3 не трогаем, так как оно нигде не используется и ни на что не влияет, но нам нужно изменить название данной умной таблицы, записав его сплошняком без пробелов или через нижнее подчеркивание: «МоиНуОченьОченьЛюбимыеФруктыЯгоды». или «Мои_ну_очень_очень_любимые_фрукты_ягоды». Первый вариант является более универсальным, поэтому настоятельно рекомендую использовать именно его. В чем заключается универсальность расскажу чуть ниже.
Наконец, на последнем шаге выделяем ячейку K5, в которой расположен связанный выпадающий список, вызываем окно «Проверка вводимых значений» и в поле «Источник» вместо =ДВССЫЛ($H$5) пишем =ДВССЫЛ(ПОДСТАВИТЬ($H$5;" ";"")). Пустота в третьем аргументе функции ПОДСТАВИТЬ как раз и убирает все пробелы из ячейки с нашими любимыми фруктами. И даже если вы случайно поставили лишний пробел в начале ячейки, в конце ячейки или поставили два пробела между словами, то всё равно всё будет работать корректно.
Усё! Можно пользовать. Когда знаешь - всё просто! (Вроде кто-то из великих любит так приговаривать)))
Да, пока далеко не ушли от этой темы, надо бы еще раз подчеркнуть, что ссылаться на ячейку H5 целесообразней все-таки не в виде абсолютной ссылки $H$5, а в виде $H5. В этом случае связанный выпадающий список будет без проблем привычным нам образом протягиваться вниз по столбцу.
2. Теперь самую малость, буквально капелюшечку, усложним формат написания категории товара и отразим его в ячейке H11 следующим образом: «Мои ну, очень-очень любимые фрукты/ягоды». Кроме пробела у нас появились дополнительные три символа, которые нужно зачистить: запятая, тире и слэш. Название умной таблицы остается прежним, как в предыдущем пункте, а вот метод зачистки будет чуток усложнен. необходимо использовать несколько функций ПОДСТАВИТЬ, вложенных друг в друга: =ДВССЫЛ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ($H5;"/";"");"-";"");",";"");" ";"")). Таким вот нехитрым способом вы можете избавиться от любых неуставных символов в ваших категориях, при этом последовательность замен значения не имеет.
Предполагаю, что поиск и удаление символов звездочка (*), вопросительный знак (?) (если, конечно, функция ПОДСТАВИТЬ вообще допускает использование подстановочных знаков), а также кавычек и других спец. символов в диалоговом окне «Проверка вводимых значений» осуществляется по тем же правилам, как если бы мы это делали просто в обычной ячейке на листе. Проверять как-то лениво, так что сами, сами.
3. Если же вы не воспользовались моим мудрым советом и записали имя таблицы через нижнее подчеркивание, то я вам не завидую.
Возможные варианты развития событий:
Вариант 1. Поскольку между словами «ну» и «очень» стоит запятая и пробел, то можно написать имя таблицы через два нижних подчеркивания: «Мои_ну__очень_очень_любимые_фрукты_ягоды». В этом случае мы просто без дополнительных премудростей прописываем нижнее подчеркивание в третьем аргументе функции ПОДСТАВИТЬ.
...
2/2.
Продолжаем разговор.
Вариант 2. В имени таблицы все слова разделяем одним нижним подчеркиванием, но в одной из вложенных функций ПОДСТАВИТЬ заменяем не просто одиночную запятую или одиночный пробел, а именно сочетание запятой с пробелом на нижнее подчеркивание: ", " на "_". При этом, нужно обязательно следить за последовательностью замен: сначала должна замениться запятая с пробелом, а только потом просто запятая или просто пробел. В противном случае работать не будет. Плюс сохраняется опасность того, что в ячейке с категорией товара случайно окажется лишний пробел. Опять же фурыкать не будет. Ну, или для подстраховки на всякий случай оберните все ваши многочисленные ПОДСТАВИТЬ в СЖПРОБЕЛЫ. Тогда шансы на success резко возрастут.
В общем, вероятность негативного влияния антропогенного фактора на конечный результат на столько велика, что я даже не стану приводить пример такой формулы, дабы не вводить в соблазн слабых духом. Пишите имя таблицы сплошняком без подчеркиваний - это самый надежный вариант.
А самое грустное во всей этой истории заключается в том, что все мои разглагольствования смысла не имеют и никому не нужны, так как существует волшебный и замечательный способ № 4 (лист «Без сортировки»), который делает всё то же самое, что и первые 2, но лишен их недостатка в виде ручного создания и поддержания в актуальном состоянии именованных диапазонов под каждую категорию. Если вдруг вы начали продавать вместо овощей овощебазы, то достаточно в ячейке E2 изменить название категории и можно струячить дальше, никаких дополнительных телодвижений больше не понадобится. Опять же, 4-му способу пофиг из скольких слов состоит название категории и какими символами они разделены. Плюс справочники легко масштабируются как по строкам, так и по столбцам. Короче, не Зависимые списки, а непрерывный праздник какой-то!
Коротенечко я бы так сформулировал основные правила и рекомендации при работе по 4-му способу:
- при создании справочника не используйте умные таблицы и автофильтр. И то, и другое скорее будет мешать, чем приносить пользу;
- сортируйте столбцы справочника стандартными средствами: Данные> Сортировка и фильтр. Это даст возможность упорядочить каждую подкатегорию независимо от соседних столбцов;
- если вы не очень четко представляете как работают глупые именованные и неименованные диапазоны и не знаете как подпихнуть в имеющийся справочник новую категорию = новый столбец, то сразу еще на этапе его (справочника) создания добавьте справа запасной столбец. Собственно можете его так и обозвать «Запасной столбец». Правда тогда в первичном выпадающем списке в самом низу всегда будет вылезать пункт «Запасной столбец», да и хрен бы с ним. Ну, или сотрите заголовок столбца - будет вылезать пустой пункт. Главное, что вы будете добавлять в справочник новые столбцы всегда перед этим запасным, а, следовательно, новая категория и подкатегории гарантированно будут попадать в основной и зависимый списки;
- во второй функции СМЕЩ предусмотрите адекватный запас для СЧЁТЗ. Если вы предполагаете, что максимально возможное количество позиций в самой большой подкатегории будет не больше 1000, то вместо 10 пропишите цифирьку 2000, не жадничайте. Сколько-нибудь существенно Excel это не загрузит, зато вы совершенно точно будете избавлены от головной боли в части расширения справочника по строкам;
- забудьте про способы № 1 и 2))).
THE END.
Текст, конечно, получился длинноватый, ну, да что вы хотите, уважаемые бухгалтеры и прочие аналитики, от 100%-ного гуманитария? Против природы не попрёшь!)))
@@VladislavMikhailov609 спасибо вам огромное!!!!!
Николай спасибо большое. Очень полезная информация.
Подписался, учусь. Пожалуйста не прекращайте помогать людям умнеть! Спасибо
Спасибо огромное, мы Вас любим))
Очень полезные уроки. Спасибо Вам большое.
Благодарю. Очень ценная информация.
Спасибо. Очень полезное видео. Я подписался.
Спасибо Николай. Очень классно дается материал. И практично. Единственное но. Третий вариант сложный для работы, т.к. при добавлении новых позиций каждый раз придется пересортировывать таблицу
Сделай макрос: при вводе - нажатии ентер - таблица автоматом пересортируется. И забудь.
@@Farinaga Тогда уже стоит идти до четвертого метода, при котором данные не требуют сортировки )))
Николай, все круто, но еще бы юзал Ctrl-C /Ctrl-V - для переименования Таблиц и другие приемчики с коментами (которые обычно импользуешь в работе) - и было бы совсем ок!
Еще желательно снизу видео писать названия функций на англ! и русском (кому - нужно) - чтобы видео можно было проще искать и вспоминать!
Спасибо (не первый раз натыкаюсь на видео - пришлось Подписаться)!!! =)
Супер наконец-то понял как это работает
Спасибо. Очень полезное информация!
Подписка и спасибо за помощь❤
Очень полезно. Спасибо.
Это просто супер!
Для тех кто пробовал вариант с умной таблицей:
При работе с умными таблицами не обязательно создавать несколько таблиц.
Для списка первого уровня: =ДВССЫЛ("ИМЯ_ТАБЛИЦЫ[#Заголовки]") - формируем первый выпадающий список по заголовкам таблицы.
Для списка второго уровня: =ДВССЫЛ("ИМЯ_ТАБЛИЦЫ["&ссылка на ячейку первого выпадающего списка&"]") - формируем второй выпадающий список по столбцам таблицы.
Достоинства - использование преимущества умных таблиц как в отношении добавления как строк так и столбцов. Возможность масштабирования, ведь значением второго выпадающего списка может оказаться имя следующей таблицы .
Недостатки - Не желательно использование выпадающих списков и таблицы на одном листе.
Спасибо, всё получилось!!
Добрый день! Присоединяюсь к вопросу: Как нужно делать способ 3, если "желтое" поле выбора категорий и "зеленое" поле выбора товаров - расположено на одном листе, а таблица с исходными данными - на другом? Подскажите, пожалуйста.
Вижу Павла- лайк неглядя)
Шикарно! всё получилось.
Спасибо! Очень интересно! Искала, как работать со списками, и нашла Ваше видео. Очень помогло, все получилось. Но, к сожалению, так и не получается создать список из нескольких столбцов. Т.е. когда от первого столбца зависит содержимое второго, а от второго зависит содержимое третьего. В третьем столбце список не работает.
Используйте сцепку, сегодня получилось)
Очень подробные уроки, хотя такое редко встретишь. А как провернуть такое с элементами Active x? Чтобы следующее поле со списком выдавало в зависимости от выбора предыдущего
Николай спасибо, понятное видео. У меня все получилось. Но есть вопрос. Как сделать третий способ если данные расположены на другом листе, не получается. Подскажите.
Не знаю, актуально ли, но у меня имена всех листов только английскими буквами без пробелов. Если не английские, то выдает ошибку
Вопрос ) про "способ 3" как сделать или скопировать "категории товар" ( допустим нужно ещё связанные выпадающие списки "категори товар" такие же самые просто клонировать или копировать ещё раз 20 30 что бы не проделывать процедуру с диспетчером имени снова и снова овер 30 раз 😕
Вы не узнали как это делается ,?
Большое спасибо.
Волшебно
Аааа! Обыскался! Спасибо!
Именно то, что искал!!!
Здравствуйте Николай! Подписавшись на Вас, не пожалела, одним словом волшебник. Вопрос такого характера, как сделать связанный выпадающий список с автоматическим заполнением других ячеек. Например: есть справочник с названием тр/средств, есть справочник летняя и зимняя норма списания, есть справочник значений к летней и зимней норме для каждого авто, как сделать и связать выпадающие списки норм списания к авто, и при выборе летней или зимней нормы автоматически вставлялось значение к определенному авто.Спасибо
Елена, вам поможет функция ВПР (VLOOKUP) - погуглите ;)
ПРОСТО СУПЕР... ТЫ МАГИСТР
А как сделать чтобы при выборе значения из списка соседние(или выбранные) ячейки заполнялись автоматически ???
Допустим выбрал какую-то еду из списка, а калории /жиры /белки/углеводы заполнились автоматически по заранее составленной таблице. Подскажите плиз
тут поможет функция впр
Луиза Нестерова спасибо
Спасибо, милчеловкек, удружил.
Эти важнейшие функции и способы могут заменяться альтернативными функциями ВПР и Индекс, (как кому удобно ) а самое замечательное их прикладное значение - они успешно РЕШАЮТ ТРУДОЕМКИЕ ЗАДАЧИ не только в бухгалтерском учете и менеджменте, они - легко используются при построении предложений в английском языке, где строгий порядок слов в предложении ... а списки - могут быть и на отдельных файлах ! Почему то коучи - скромничают и не делятся этими знаниями с русскоязычной аудиторией.
Добрый день!
Подскажите пжл можно ли добавлять Категории/Товары (как во 2ом примере с умными табл) используя 3ий вариант ?
Николай, можно усовершенствовать второй способ и обойтись единственной таблицей вместо трёх - использовать в ДВССЫЛ ссылки на заголовки и на колонки таблицы.
Такой способ будет максимально расширяемым и удобным.
Пример - dl.dropboxusercontent.com/u/6795309/excel/excel-nested-lists.xlsx
+Алексей Марьин Не всегда одна таблица вместо трех удобна. При этом способе если у вас в одном столбце 20 наименований, а в соседнем 3, то в выпадающем списке с тремя наименованиями будут: три наименования и 17 пустых позиций. И иногда эти пустые ячейки будут прямо в середине выпадающего списка (в зависимости от того, как пользователь добавит новую категорию).
Николай, подскажите, можно ли сделать многоуровневые связанные выпадающие списки? 3, 5, 10 уровней.
Заранее спасибо!
я сделал на 1 уровень больше, чем в видео
просто необходимо повторить процедуру дважды
VBA хорошо, но у большинства работа макросов запрещена. Вот и мучаемся обычными средствами.
все хорошо пока не появляются одинаковые значения :(
Все три примера работают, если жёлтая ячейка (H5) фиксированная. На практике категория и товар вводятся в таблицу в хронологическом порядке (вместе с другими данными, не имеющими отношения к данному примеру). Допустим, категория вводится в столбец C, товар в столбец D). Разумеется, никакой сортировки при вводе нет. Если для выпадающего списка Категория можно сделать отдельную табличку в сторонке (как у Вас Н11-Н13), то с товаром сложнее. Нужно чтобы выпадающий список товаров формировался не из фиксированной ячейки (у Вас Н5), а на основе только что введённой категории (допустим, в ячейку C465). Как сформировать правильный выпадающий список в ячейке D465 для этой категории?
Отлично! Помогло. Было бы прикольно все функции дублировать на американском языке)
Спасибо! Все понятно)
Блестяще! Спасибо!
Спасибо огромное, добрый человек. Сколько же часов я потратил на поиски этой функции
Большое спасибо!
Добрый день)
Спасибо за видео. Подскажите, а есть возможность сделать вывод списка на основе двух параметров ( а не на одном, как показано видео. Например, есть группы "Фрукты" и "Овощи" . в "Фруктах" в ней категории "цитрусы" и "яблоки, груши". А в "цитрусы" список всех кто подходит (апельсины, мандарины и т.д.)
ОГРОМНОЕ СПАСИБО!
Сначала лайк потом просмотр.
наверное лучшие специалисты по ексель это сами разработчики ексель)))
При этом БГейтс не лучший специалист по виндовсам )))
Первыми двумя способами пользуюсь уже давно, про третий не знал, но мне больше все-таки второй нравится. Есть возможность добавления новых данных…
Третий тоже не плохой. Надо написать макрос о сортировке общего списка через кнопку.
помог, очень сильно) спасибо большое
Спасибо То что искал :) BIG LIKE
Доброго времени суток, существуют ли способы примнения способа к динамической таблице?
Спасибо, все понятно. Но есть вопрос. У нас в основной таблице поменялось наименование ячейки. Как сделать так, чтобы она изменилась и в других ячейках когда мы её выбирали из выпадающего списка?
Вообще крутяк!!!
Есть ли возможность повторить способ 3, если списки категорий и товаров - на одном листе, а выпадающий список необходимо сделать на другом?
Николай, спасибо вам доступное объяснение. Хотелось бы узнать возможно ли на одном листе собрать информацию о тарифах курьерской службы (допустим на доставку чего-либо на разные расстояния) двух или трех компаний, анализа? Заранее благодарен.
Здравствуйте! Есть проблема при создании именного диапазона на 1 части методики 3:00 минута. Когда выделяю все ячейки и после введение название в адрес ячейки, выходит такое предупреждение: "Необходимо ввести допустимую ссылку для перехода или допустимое имя для выделения".
Хотя на первую колонку сделал без никаких проблем, а данная проблема выявляется на вторую колонку.
Спасибо за внимание!
Добрый день. При добавлении какого-либо товара, получается, нужно " засовывать " в середину нужного списка, чтобы в именной формуле не менять диапазоны. А если новая категория, диапазоны в формуле надо все-таки подвинуть согласно новым границам, верно?
Николай, скажите пожалуйста как решить такую задачу: Мне нужно чтобы при выборе данных из списка (срок изготовления на выбор) подставлялась скидка (наценка) от общей суммы из ячейки. Есть ли видео как это можно сделать?
Николай @planetaexcel здравствуйте!
вот СВЯЗАННЫЕ списки - у меня СРАЗУ получились , благодарю 😀👍
Возник вопрос = 8:33 = а *можно ЛИ* сделать так, чтобы при смене значения в ОРАНЖЕВОЙ ячейке (с фруктов -> на зелень), в Зелёной ячейке *СРАЗУ БЫ менялись и продукты из первой строки каждого столбца ?!* 🙋
У меня таблица 9х9, и когда я в ней меняю все 81 ячейку с "фрукты" на другие категории, то ВРУЧНУЮ менять значения из ЗЕЛЁНОЙ ячейки, с "Арбузов" -> на "Петрушку" ===== ОЧЕНЬ УТОМИТЕЛЬНО (!) ...... позже, даа - придётся ручками менять все "Арбузы" на "Петрушки, или на "Картошки" , но ПЕРВИЧНО =>> всё же хочется чтобы значения в зелёной ячейке АВТОМАТОМ менялись на 1-е значение соответствующего столбца при смене Категории в "Оранжевой ячейке" (!!!)
Можно ЛИ как-то это сделать ??!
Спасибо за видео
Подскажите пожалуйста
Хочу сделать таблицу. Создать список Месяцев, и чтобы при выборе допустим Декабря, в строке с 1 по 31 ячейку отображалась дата. И при смене месяца, чтобы количество заполненых ячеек менялось в зависимости от количества дней в месяце
Очень хорошо!
Добрый день, спасибо все подробно. Но у меня вопрос решение которого не нашел. Я научился делать зависимый выпадающий список в одной ячейке.
Но мне нужно "протянуть" этот список вниз. Как это сделать?
Категория - Товар
Категория - Товар
Категория - Товар
Здравствуйте! А возможно применить автозаполнение к ячейке с выпадающим списком? Например, у меня в выпадающем списке 500 фамилий и если набирать начало фамилии, происходило бы автозаполнение
Спасибо, очень доходчиво и весьма понятно, но вопрос все же остался. Типография: К примеру, есть 2 уровня цен для клиентов (постоянные и нет). Список материалов и цена на какую-либо позицию зависит от разрешения печати (360, 720, 1440...). как сделать чтобы в зависимости от выбора категории клиента, материала, разрешения в какой либо ячейке подтягивалась соответствующая цена. Заранее СПАСИБО!
Тут нужен ВПР по трем измерениям (товар - тип клиента - разрешение). Гляньте мою статью на эту тему www.planetaexcel.ru/techniques/2/231/
Спасибо.
Приветствую, Николай! Хочу поднять правую руку и спросить следующее: "Данный вопрос касается второго способа. Когда вы приводите в пример, категории товаров, то обязательно чтобы между ними был зазор в один столбец"?
🎉🎉🎉 А можно ли сделать в первом выбор допустим "труба", а дальше три выборные ячейки из таблицы длинна высота толщина существующие под этот тип трубы? 🎉🎉🎉
Как всегда все доходчиво)
Николай, а работает ли все это внутри умной таблицы? У меня вот такая ситуация таблицы с нужными списками на вспомогательном листе (всегда делаю его)...на нем если делать связанные списки все работает, НО как только пытаюсь сделать все тоже самое на другом листе внутри умной таблицы...ничего не работает....Есть столбец например Поставщик и вот на основе его выбора надо выбирать Товар и потом ВПРом цена подтягивается
Хотя спасибо)) Уже сам решил проблему...В строке где прописывал Источник списка в ДВССЫЛ изменил тип диапазона с абсолютного на смешанный (вместо $A$4:$A10, поставил $A4:$A10)
Николай, а про гугл таблицы такое можете сделать? Мне в облаке надо, многопользовательское. Я в обычном экселе самостоятельно реализовал что-то похожее на одно из ваших решений (там это просто, на мой взгляд), но в гугле подобный функционал сделан как-то тупее. Но вдруг я чего-то упустил.
Николай, как сделать что бы в зелёной ячейке появлялся не выпадающий список, а полный список, например в жёлтом окне выбираем "овощи" и на зелёном поле видим все товары расписанные на 8 строк получается, 8 зелёных строк с наименованием. Спасибо за ответ.
Последний, третий способ. сказано "самый универсальный из всех", но ведь в нём поиск сугубо по определённым ячейкам, а новые фрукты/овощи/зелень не войдут во второй список (без корректировки формулы каждый раз)? См. 12:50, в функции ПОИСКПОЗ указаны определённые ячейки, а не имя диапазона. 3й. способ уступает второму способу в универсальности, т.к. во 2 способе ссылки на именованные умные таблицы, автоматически включающие новые данные.
Третий способ универсальнее. Формулу менять не надо, просто вставляешь строчку внутрь талбицы, протягиваешь название первого столбца, например "овощ", и пишешь что за овощ во втором, и новый овощ появится в зависмом выпадающем списке. Воторй способ хорош, когда мало категорий, как в примере (фрукты, овощи и зелень - всего три), но когда категорий много, третий способ предпочтительнее. Например в моем случае 23 химических продукта, каждый имеет от одного до 5 растворителей...да я помру делать 23 отдельные умные таблицы.