Трюк Excel 17. Множественный ВПР в одной ячейке с помощью функции ОБЪЕДИНИТЬ
Vložit
- čas přidán 19. 06. 2017
- Подстановка данных по ключевому полю (критерию) и объединение результата в одну ячейку - множественный ВПР через запятую. Другие трюки Excel и файл с примером можно скачать здесь statanaliz.info/tryuki-ms-excel/
► Корпоративный тренинг "Продвинутый уровень MS Excel":
statanaliz.info/trening-prodv...
► Онлайн курс "Продвинутый уровень MS Excel":
statanaliz.info/kurs-prodvinu...
**************************
** Мой сайт об Excel и статистике **
statanaliz.info/
** Странички и аккаунты в соцсетях **
Facebook: / statanaliz.info
Вконтакте: id_statanaliz_info
Твиттер: / statanaliz_info
Telegram: t.me/statanaliz
За 4 минуты я понял, что мне надо с осторожностью писать в резюме о своих знаниях MS Office.
Понимание ограниченности своих знаний - это правильно. Дает стимул к обучению.
@@statanaliz Я 5 лет занимался корректировкой полей для нестандартных значений... теперь я переделаю таблицу. Спасибо большое!
@@alexk240 Надеюсь, этот урок облегчит Вашу работу. Удачи!
На одном из собеседований, лет 5 назад, я тоже опрометчиво обозначил, что неплохо знаю MS Office. После той встречи серьезно начал изучать прикладные инструменты.
Велике, дякую. Ця формула скоротила мені час в разів 10! Це неймовірно. 💜
Я дня три это искал и нашёл гораздо более удобную реализацию. Долгих лет жизни вам!
В гугл таблицах тоже пашет, но там где нужно указать пропуск пустых ячеек, указываем True.
Как же это круто, спасибо за бесценную информацию...Лучший!!!
Спасибо большое, что делитесь своими знаниями!
Пожалуйста )
Большое спасибо за урок! Очень полезное видео.
И вам спасибо за отзыв. Удачного применения Excel.
Век добра Вам человек. Вы мне очень помогли
Спастбо большое!!! Это решает несколько моих задач, над которыми думаю уже несколько дней!!!!
Значит правильно сделали, что не стали изобретать велосипед по второму разу, а нашли готовое решение. Спасибо за комментарий.
Большое спасибо за урок!
Пожалуйста!
Спасибо, отличный трюк!
Спасибо за комментарий!
Большое человеческое СПАСИБО!
Миллион благодарностей! А то всю голову сломала как сделать!
Нужно подписываться, что я и сделал. Толковый канал. Ещё раз спасибо Дмитрию.
Большое спасибо за подписку да еще и рекомендацию другим зрителям )).
То что искал, спасибо!
Спасибо! Очень полезно.
Пожалуйста, очень рад. Спасибо за комментарий.
Очень полезно. Спасибо
Рад, если помог. Спасибо за комментарий.
Круто, спасибо!!!
Слишком просто даже, но гениально. С первого раза залетела
Формула для уникальных! :
{=ЕСЛИОШИБКА(ИНДЕКС(Отчет[Автомобиль]; ПОИСКПОЗ( 0;СЧЁТЕСЛИ($D$1:D1;Отчет[Автомобиль])+(Отчет[Автомобиль]="");0));"")}
(Вставляем в ячейку D2 и протягиваем с запасом)
пользуйтесь кому надо ;)
...и лайк за видео
Для чайников:
вместо "Отчет[Автомобиль]" вставляем ваш диапазон значений (закрепленный), а вместо ячейки D1 в "$D$1:D1" ячейку на позицию выше выводииого диапазона (т.е. если вставляете формулу в ячейку F3, тогда - "$F$2:F2", ну и Ctrl+Shift+Enter соответственно)
спасибо огромное ❤
Вообще супер
Бомба!!!
Рад, если понравилось, спасибо )
Спасибо, очень помогли. А вот ещё один вопрос. Скажите как сделать, чтобы при добавление строк с наименованием товара одной таблицы (т.е один столбец), также одновременно добавлялось в другую таблицу (также на одном столбце)? При этом не затрагивая другие столбцы. На других столбцах будет вводиться разные данные.
Ты просто гений. Я в аксесе мучался сливал, удалял, вручную копировал
Кто же сейчас эксесом пользуется? Если нужна БД, то выбор велик, при этом куча бесплатных решений. Кстати, не всегда эксель может заменить БД, особенно когда данных очень много. PostgreSQL+CRUD на любом языке - нет проблем!
@@phat80 я работаю в гос органе, офис 2019 хом и бизнесс без аксесса - лицензия, другие базы данных тоже запрещено ставить
@@user-xm9uf8ln7k интересно, чем это продиктовано. Понимаю, когда запрещено ставить пиратский софт, так как это может грозить огромными штрафами (хотя кто пойдет это проверять в госконтору?). Но почему нельзя поставить официальные бесплатные решения, я никогда не пойму.
Это просто!) Прописать формулу для одной ячейки не сложно.
Другое дело, как данную формулу динамически размножить? Например через Arreyformula?
Буду признателен, если кто знает ответ!
Есть возможность такое реализовать, но только без функции ОБЪЕДИНИТЬ?
Спасибо! А есть способ вставить два условия? Если под IF просписать AND(условие1;условие2) не получается
Должно получиться. Пришлите файл с примером. Адрес в описании канала.
Чет все равно остаются пробелы. Сделал ровно также как в видео. В чем может быть проблема?
Спасибо, а если нужно таким образом просуммировать..если бы вместо имён были суммы, то сложил бы их Эксель , если вначале этой формулы добавить сумм
СУММЕСЛИ специальная функция для суммирования по критерию
@@statanaliz Здравствуйте Дмитрий.
Помогите пожалуйста создать таблицу где например
План 3, факт 3, то итог 300 (3*100) или План 3, факт 4, то итог 40 (4*10) или План 3, факт 2, то итог -300 (3/-100)
Все расчёты в одной ячейке.
Всего ячеек ПЛАН ФАКТ ИТОГ
Спасибо.
@@statanaliz Вроде как понимаю как это должно работать но не могу формулы подобрать.
Вот например.
Ячейки А1 План, А2 Факт, А3 Итог.
А3=А1=А2 тоА1*100 если А1>А2 тоА1×-100 если А1
Какую комбинацию клавиш вы нажали на 4.08 минуте? Что бы формула отобразила значения
F9 после выделения фрагмента формулы для пересчета.
@@statanaliz Спасибо!
хороша формула, ох как хороша. Но использовать её смогут не многие. Мало у кого работодатели закупают новые пакеты.
Это точно. Многие до сих пор в 2010 сидят. Ну, хоть на домашнем компьютере можно сделать.
Ну я лично в государственной финансовой организации работаю и у нас 2010 и power query ставить нельзя и флэшки нельзя и вообще ппц и в ручную всё делать это есть true.
лично я бы сам себе даже оплатил лицензию, но нельзя =)))) чем богаты, тому и рады
Владислав, далеко не в каждой коммерческой конторе стоит свежий office. Потратить 5-10 тыс. долларов на корпоративную попойку - это пожалуйста, а поставить лицензионный оффис - дорого. Таковы реалии, к сожалению.
В гугл таблицах работает всё.
у меня не получается по вашему примеру ... выпадает только 1 значение....=CONCATENATE(", ";;IF(Table1[Автомобиль]=P6;Table1[Менеджер];"")) и получается : , вася
Дмитрий, это гениально! Один вопрос, я с помощью этой функции объединяю периоды (09.2020, 10.2020 и т.д.). В итоге получаю в объединенной ячейке: 44013, 44044. Как быстро преобразовать эти данные в периоды, чтоб не мучаться с функцией "текст по столбцам" и "сцепка".
Заранее благодарю!
Последнюю ссылку в формуле (на столбец, из которого объединяются ячейки) заверните в функцию ТЕКСТ и придайте нужный формат даты. Если бы в моем примере были даты, а не имена, то формула была бы такая =ОБЪЕДИНИТЬ(", ";;ЕСЛИ(Отчет[Автомобиль]=D2;ТЕКСТ(Отчет[Продавец];"ДД.ММ.ГГ");""))
@@statanaliz это гениально! Благодарю)
@@user-lp2oz1kh5h Пожалуйста )
Как с языка сняли! Большое спасибо!) нашла ответ на интересующий вопрос) у меня тоже массивы из дат)
А как можно вывести только уникальные значения? Может быть кто то подскажет?
Почему при выборе ячеек у меня в формуле не пишется "Отчёт с квадратными скобками и наименованием столбца", а просто массив ячеек через двоеточие? Понять не могу. Может при выборе ячеек вы зажимаете комбинацию кнопок на клавиатуре?
Неееет, не в этом дело, у пеня тоже просто массив ячеек, все работает.
Умная таблица же ж!!
Формула отличная, но у меня постоянно выходит ошибка. С чем может быть связана? Когда, смотрю этапы вычисления, видно, что верно, но последний этап все равно ошибка
По комментарию трудно найти ошибку. Пришлите Ваш файл с ошибкой на почту - адрес в описании канала. Либо скачайте файл с примером (ссылка под роликом) и сравните свое решение с моим.
Здравствуйте. А как прописать в ЕСЛИ несколько условий? Например у нас есть еще столбец " Страна сборки" (столбец С) и мы хотим вытащить только тех кто продал авто собранные в России? =ОБЪЕДИНИТЬ("; ";;ЕСЛИ(И(A2:A12=D2;C2:C12="Россия");B2:B12;"-")) почему то не работает.
Попробуйте ЕСЛИМН
В Excel 2007 это не работает?
А как сделать ВПР из двух таблиц? Или трёх.. и т.д.
Можно написать свою формулу на VBA, то есть вообще любую и использовать ее потом в свое удовольствие.
Подскажите, почему в excel нет такой функции - объединить. Не находит (
Старая версия. Недавно добавили. Обновите excel.
3:42 как интервал (критерии) по названию таблицы записать?
Если источником является таблица Excel, то название таблицы и столбца ставятся автоматически после выделения.
@@statanaliz не выходит автоматически, в том то и дилемма
это с какой версии работает? может в этом дело
@@MultiFloofy Таблица Excel появилась примерно в Excel 2010. Какая у Вас версия?
@@statanaliz 2010 и использую
я установил Офис 2016 и функции =Объединить у меня нет (((( как мне ее настроить ?
Обновите офис до последней версии. В 2016 функция ОБЪЕДИНИТЬ появилась не в первом релизе. Лучше сразу ставьте 2019 или подписку 365.
Вместо раширенного фильтра нужно подставить функцию уник и будет всё автаматический обновляться
А как быть, если один продавец несколько раз продал одинаковые автомобили?
Чтобы не было "Мерседес: Петя, Таня, Петя, Петя,..."
@Езепов Дмитрий, Дмитрий, присоединяюсь к вопросу Bam DeGran. Подскажите пожалуйста.
Можно было просто сделать фильтр по машинам.
И как этот фильтр объединит разные значения в ячейку?