Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Подтянуть значение по двум условиям и ближайшей дате - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подтянуть значение по двум условиям и ближайшей дате (Формулы/Formulas)
Подтянуть значение по двум условиям и ближайшей дате
dmitriyaleksandrovichni Дата: Среда, 28.06.2023, 17:41 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 40% ±

13
Коллеги, привет!
Прошу помочь решить задачу в экселе.

Клиенты, которые ранее звонили по рекламным объявлениям и интересовались теми или иными товарами, пришли в офис продаж и приобрели некий товар
Нужно подтянуть последнее рекламное объявление, по которому они звонили и интересовались тем товаром, который в итоге купили.
В Таблице 1 представлена история звонков клиентов
В Таблицу 2 нужно подтянуть рекламное объявление и дату звонка
То есть нужно подтянуть рекламное объявление по совпадению: номера телефона и названию товара и по ближайшей дате звонка к дате покупки.
Файл прилагаю
Заранее благодарю за помощь!

P.S. Я всегда пользовался связкой ИНДЕКС и ПОИСКПОЗ:
Код
=ИНДЕКС(Таблица1[Рекламное объявление];ПОИСКПОЗ(1;(Таблица1[Номер телефона]=[@[Номер телефона]])*(Таблица1[Интересующий товар]=[@[Купленный товар]]);0))

Но сейчас эта формула мою задачу не решает, так как я не знаю, как в неё внедрить условие по поиску ближайшей к дате покупки даты звонка.

И ещё большая просьба: желательно без макросов
К сообщению приложен файл: 3816529.xlsx (24.0 Kb)


Шлюхогон42

Сообщение отредактировал dmitriyaleksandrovichni - Среда, 28.06.2023, 17:59
 
Ответить
СообщениеКоллеги, привет!
Прошу помочь решить задачу в экселе.

Клиенты, которые ранее звонили по рекламным объявлениям и интересовались теми или иными товарами, пришли в офис продаж и приобрели некий товар
Нужно подтянуть последнее рекламное объявление, по которому они звонили и интересовались тем товаром, который в итоге купили.
В Таблице 1 представлена история звонков клиентов
В Таблицу 2 нужно подтянуть рекламное объявление и дату звонка
То есть нужно подтянуть рекламное объявление по совпадению: номера телефона и названию товара и по ближайшей дате звонка к дате покупки.
Файл прилагаю
Заранее благодарю за помощь!

P.S. Я всегда пользовался связкой ИНДЕКС и ПОИСКПОЗ:
Код
=ИНДЕКС(Таблица1[Рекламное объявление];ПОИСКПОЗ(1;(Таблица1[Номер телефона]=[@[Номер телефона]])*(Таблица1[Интересующий товар]=[@[Купленный товар]]);0))

Но сейчас эта формула мою задачу не решает, так как я не знаю, как в неё внедрить условие по поиску ближайшей к дате покупки даты звонка.

И ещё большая просьба: желательно без макросов

Автор - dmitriyaleksandrovichni
Дата добавления - 28.06.2023 в 17:41
Serge_007 Дата: Среда, 28.06.2023, 17:50 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Здравствуйте

Цитата dmitriyaleksandrovichni, 28.06.2023 в 17:41, в сообщении № 1 ()
по ближайшей дате звонка к дате покупки
В файле нет даты покупки

Посмотрите вложение, может угадал
К сообщению приложен файл: 20230628_dmitriyaleksandrovich.xls (53.0 Kb)


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЗдравствуйте

Цитата dmitriyaleksandrovichni, 28.06.2023 в 17:41, в сообщении № 1 ()
по ближайшей дате звонка к дате покупки
В файле нет даты покупки

Посмотрите вложение, может угадал

Автор - Serge_007
Дата добавления - 28.06.2023 в 17:50
dmitriyaleksandrovichni Дата: Среда, 28.06.2023, 18:01 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 40% ±

13
Сорри, исправил. Дата визита - это дата покупки, сейчас поправил


Шлюхогон42

Сообщение отредактировал dmitriyaleksandrovichni - Среда, 28.06.2023, 18:16
 
Ответить
СообщениеСорри, исправил. Дата визита - это дата покупки, сейчас поправил

Автор - dmitriyaleksandrovichni
Дата добавления - 28.06.2023 в 18:01
dmitriyaleksandrovichni Дата: Среда, 28.06.2023, 18:07 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 40% ±

13
В Таблицу 2 надо подтянуть данные (вторая вкладка)
В вашей формуле, к сожалению, не учитывается условие про ближайшую дату. Должно подтягиваться только то рекламное объявление, где есть совпадение номера телефона, купленного товара и где дата звонка самая ближайшая к дате покупки. Понятно, что дата покупки должна быть позже даты звонка, то есть все звонки после даты покупки - не считаются


Шлюхогон42

Сообщение отредактировал dmitriyaleksandrovichni - Среда, 28.06.2023, 18:07
 
Ответить
СообщениеВ Таблицу 2 надо подтянуть данные (вторая вкладка)
В вашей формуле, к сожалению, не учитывается условие про ближайшую дату. Должно подтягиваться только то рекламное объявление, где есть совпадение номера телефона, купленного товара и где дата звонка самая ближайшая к дате покупки. Понятно, что дата покупки должна быть позже даты звонка, то есть все звонки после даты покупки - не считаются

Автор - dmitriyaleksandrovichni
Дата добавления - 28.06.2023 в 18:07
Pelena Дата: Среда, 28.06.2023, 19:41 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 19179
Репутация: 4418 ±
Замечаний: ±

Excel 365 & Mac Excel
Вариант ИНДЕКС+АГРЕГАТ
К сообщению приложен файл: 0739199.xlsx (26.4 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВариант ИНДЕКС+АГРЕГАТ

Автор - Pelena
Дата добавления - 28.06.2023 в 19:41
Serge_007 Дата: Четверг, 29.06.2023, 09:39 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеКросс : https://forum.msexcel.ru/index.php?topic=12254.0

Автор - Serge_007
Дата добавления - 29.06.2023 в 09:39
dmitriyaleksandrovichni Дата: Четверг, 29.06.2023, 11:04 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 40% ±

13
Pelena, спасибо, работает!
Но можете, пожалуйста, объяснить подробно формулу? Мне нужно её понять, чтобы использовать в дальнейшем в работе, в том числе и при условии, что подтянуть номер объявления нужно только по номеру телефона, без привязки к купленному товару. Её же можно и таким образом использовать?
Буду вам очень признателен за детальное объяснение!


Шлюхогон42
 
Ответить
СообщениеPelena, спасибо, работает!
Но можете, пожалуйста, объяснить подробно формулу? Мне нужно её понять, чтобы использовать в дальнейшем в работе, в том числе и при условии, что подтянуть номер объявления нужно только по номеру телефона, без привязки к купленному товару. Её же можно и таким образом использовать?
Буду вам очень признателен за детальное объяснение!

Автор - dmitriyaleksandrovichni
Дата добавления - 29.06.2023 в 11:04
Pelena Дата: Четверг, 29.06.2023, 14:34 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 19179
Репутация: 4418 ±
Замечаний: ±

Excel 365 & Mac Excel
Код
=ЕСЛИОШИБКА(АГРЕГАТ(14;6;'Таблица 1'!$D$2:$D$500/('Таблица 1'!$B$2:$B$500=[@[Купленный товар]])/('Таблица 1'!$C$2:$C$500=[@[Номер телефона]])/('Таблица 1'!$D$2:$D$500<=[@[Дата покупки]]);1);"-")

части
('Таблица 1'!$B$2:$B$500=[@[Купленный товар]])
('Таблица 1'!$C$2:$C$500=[@[Номер телефона]])
('Таблица 1'!$D$2:$D$500<=[@[Дата покупки]])
представляют собой условия выборки. То есть если надо без привязки к купленному товару, то убираем фрагмент /('Таблица 1'!$B$2:$B$500=[@[Купленный товар]])
Каждая из этих частей даёт массив из элементов ИСТИНА и ЛОЖЬ. Это можно увидеть, выделив фрагмент и нажав клавишу F9 на клавиатуре.
Если ИСТИНА и ЛОЖЬ участвуют в арифметических операциях (в нашем случае деление), то они преобразуются в 1 и 0 соответственно. Если мы даты 'Таблица 1'!$D$2:$D$500, то есть по сути числа, разделим на массив из нулей и единиц, то получим, что там где условия соблюдаются (1), останется дата, а где не соблюдаются (0), будет ошибка #ДЕЛ/0!
Дальше в дело вступает функция АГРЕГАТ, которая имеет аргументы:
14 - наибольший, т.к. нам нужна наибольшая дата из подходящих под условия
6 - пропуск ошибок, т.к. нам нужно пропустить ошибки #ДЕЛ/0!
дальше наш массив, про который мы говорили выше
1 - т.к. нам нужно одно наибольшее значение
Функция ЕСЛИОШИБКА позволяет обработать ситуацию, когда покупки не было
Как-то так


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Код
=ЕСЛИОШИБКА(АГРЕГАТ(14;6;'Таблица 1'!$D$2:$D$500/('Таблица 1'!$B$2:$B$500=[@[Купленный товар]])/('Таблица 1'!$C$2:$C$500=[@[Номер телефона]])/('Таблица 1'!$D$2:$D$500<=[@[Дата покупки]]);1);"-")

части
('Таблица 1'!$B$2:$B$500=[@[Купленный товар]])
('Таблица 1'!$C$2:$C$500=[@[Номер телефона]])
('Таблица 1'!$D$2:$D$500<=[@[Дата покупки]])
представляют собой условия выборки. То есть если надо без привязки к купленному товару, то убираем фрагмент /('Таблица 1'!$B$2:$B$500=[@[Купленный товар]])
Каждая из этих частей даёт массив из элементов ИСТИНА и ЛОЖЬ. Это можно увидеть, выделив фрагмент и нажав клавишу F9 на клавиатуре.
Если ИСТИНА и ЛОЖЬ участвуют в арифметических операциях (в нашем случае деление), то они преобразуются в 1 и 0 соответственно. Если мы даты 'Таблица 1'!$D$2:$D$500, то есть по сути числа, разделим на массив из нулей и единиц, то получим, что там где условия соблюдаются (1), останется дата, а где не соблюдаются (0), будет ошибка #ДЕЛ/0!
Дальше в дело вступает функция АГРЕГАТ, которая имеет аргументы:
14 - наибольший, т.к. нам нужна наибольшая дата из подходящих под условия
6 - пропуск ошибок, т.к. нам нужно пропустить ошибки #ДЕЛ/0!
дальше наш массив, про который мы говорили выше
1 - т.к. нам нужно одно наибольшее значение
Функция ЕСЛИОШИБКА позволяет обработать ситуацию, когда покупки не было
Как-то так

Автор - Pelena
Дата добавления - 29.06.2023 в 14:34
dmitriyaleksandrovichni Дата: Четверг, 29.06.2023, 14:53 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 40% ±

13
Pelena, большое спасибо!
Уже активно пользуюсь вашей формулой)


Шлюхогон42
 
Ответить
СообщениеPelena, большое спасибо!
Уже активно пользуюсь вашей формулой)

Автор - dmitriyaleksandrovichni
Дата добавления - 29.06.2023 в 14:53
dmitriyaleksandrovichni Дата: Четверг, 29.06.2023, 15:29 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 40% ±

13
Pelena, ещё такой вопрос:
если не важно, чтобы дата звонка была раньше даты покупки? А главное, чтобы отклонение от даты покупки позже или раньше во времени было минимальным? Тут по идее как-то модуль надо зашить. Можете подсказать, как это сделать?


Шлюхогон42
 
Ответить
СообщениеPelena, ещё такой вопрос:
если не важно, чтобы дата звонка была раньше даты покупки? А главное, чтобы отклонение от даты покупки позже или раньше во времени было минимальным? Тут по идее как-то модуль надо зашить. Можете подсказать, как это сделать?

Автор - dmitriyaleksandrovichni
Дата добавления - 29.06.2023 в 15:29
Pelena Дата: Четверг, 29.06.2023, 15:45 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 19179
Репутация: 4418 ±
Замечаний: ±

Excel 365 & Mac Excel
Предыдущая формула считает ближайшую меньшую дату. Посчитайте по аналогии ближайшую бОльшую и сравните, какая из них ближе к заданной


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПредыдущая формула считает ближайшую меньшую дату. Посчитайте по аналогии ближайшую бОльшую и сравните, какая из них ближе к заданной

Автор - Pelena
Дата добавления - 29.06.2023 в 15:45
dmitriyaleksandrovichni Дата: Пятница, 30.06.2023, 11:13 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 40% ±

13
Pelena, я всё пытаюсь допереть, как работает эта формула - помогите разобраться до конца, пожалуйста.
То, что СТРОКА(Таблица1[Рекламное объявление])-1 - выдаёт номер строки в таблице - это понятно (как работает ИНДЕКС - я тоже знаю) Если б таблица начиналась не со 2-й строки, а с 3-й - было бы СТРОКА(Таблица1[Рекламное объявление])-2
('Таблица 1'!$B$2:$B$500=[@[Купленный товар]])
('Таблица 1'!$C$2:$C$500=[@[Номер телефона]])
тут тоже всё ясно - формула пройдёт все ячейки сверху донизу и при выполнении условия в ячейке выдаст 1, при невыполнении - 0
Далее номер строки делится на 1 или 0 - и тут понятно для чего нужна формула АГРЕГАТ, так как она способна пропускать ошибки типа ДЕЛ/0 (аргумент 6 в функции)

Но вот чего я никак не могу понять, так это как работает НАИБОЛЬШЕЕ в связке с последним условием:
('Таблица 1'!$D$2:$D$500<=[@[Дата покупки]])
Ведь результатом вычисления этого условия будет тоже 1 или 0, а значит номер строки будет делиться либо на 1, либо на 0
Как же тогда работает условие НАИБОЛЬШИЙ?


Помогите, пожалуйста!


Шлюхогон42
 
Ответить
СообщениеPelena, я всё пытаюсь допереть, как работает эта формула - помогите разобраться до конца, пожалуйста.
То, что СТРОКА(Таблица1[Рекламное объявление])-1 - выдаёт номер строки в таблице - это понятно (как работает ИНДЕКС - я тоже знаю) Если б таблица начиналась не со 2-й строки, а с 3-й - было бы СТРОКА(Таблица1[Рекламное объявление])-2
('Таблица 1'!$B$2:$B$500=[@[Купленный товар]])
('Таблица 1'!$C$2:$C$500=[@[Номер телефона]])
тут тоже всё ясно - формула пройдёт все ячейки сверху донизу и при выполнении условия в ячейке выдаст 1, при невыполнении - 0
Далее номер строки делится на 1 или 0 - и тут понятно для чего нужна формула АГРЕГАТ, так как она способна пропускать ошибки типа ДЕЛ/0 (аргумент 6 в функции)

Но вот чего я никак не могу понять, так это как работает НАИБОЛЬШЕЕ в связке с последним условием:
('Таблица 1'!$D$2:$D$500<=[@[Дата покупки]])
Ведь результатом вычисления этого условия будет тоже 1 или 0, а значит номер строки будет делиться либо на 1, либо на 0
Как же тогда работает условие НАИБОЛЬШИЙ?


Помогите, пожалуйста!

Автор - dmitriyaleksandrovichni
Дата добавления - 30.06.2023 в 11:13
Pelena Дата: Пятница, 30.06.2023, 14:13 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 19179
Репутация: 4418 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата dmitriyaleksandrovichni, 30.06.2023 в 11:13, в сообщении № 12 ()
значит номер строки будет делиться либо на 1, либо на 0

всё верно.
Когда номер строки делится на 1, остаётся номер строки. Когда номер строки делится на 0, получается ошибка. Среди оставшихся номеров строк, выбираем самое большое и подставляем в ИНДЕКС.
Например, исходная дата у нас в 5-ой строке. Наше условие выдаёт нам #ДЕЛ/0!;2;3;#ДЕЛ/0!;#ДЕЛ/0!;... дальше все ошибки, т.к. даты превышают исходную. Наибольшее значение из этого массива 3, соответственно, функция ИНДЕКС возвращает значение из 3-ей строки диапазона


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата dmitriyaleksandrovichni, 30.06.2023 в 11:13, в сообщении № 12 ()
значит номер строки будет делиться либо на 1, либо на 0

всё верно.
Когда номер строки делится на 1, остаётся номер строки. Когда номер строки делится на 0, получается ошибка. Среди оставшихся номеров строк, выбираем самое большое и подставляем в ИНДЕКС.
Например, исходная дата у нас в 5-ой строке. Наше условие выдаёт нам #ДЕЛ/0!;2;3;#ДЕЛ/0!;#ДЕЛ/0!;... дальше все ошибки, т.к. даты превышают исходную. Наибольшее значение из этого массива 3, соответственно, функция ИНДЕКС возвращает значение из 3-ей строки диапазона

Автор - Pelena
Дата добавления - 30.06.2023 в 14:13
dmitriyaleksandrovichni Дата: Пятница, 30.06.2023, 14:32 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 40% ±

13
Pelena, теперь понял, спасибо!
НАИБОЛЬШИЙ - будет выбирать наибольший номер строки, а так как у нас значения в таблице 1 отсортированы по датам от старых к новым, то номер строки самой поздней даты (то есть самой ближней к интересующей нас дате), будет наибольшим. Ура!)


Шлюхогон42
 
Ответить
СообщениеPelena, теперь понял, спасибо!
НАИБОЛЬШИЙ - будет выбирать наибольший номер строки, а так как у нас значения в таблице 1 отсортированы по датам от старых к новым, то номер строки самой поздней даты (то есть самой ближней к интересующей нас дате), будет наибольшим. Ура!)

Автор - dmitriyaleksandrovichni
Дата добавления - 30.06.2023 в 14:32
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подтянуть значение по двум условиям и ближайшей дате (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!