Коллеги, привет! Прошу помочь решить задачу в экселе.
Клиенты, которые ранее звонили по рекламным объявлениям и интересовались теми или иными товарами, пришли в офис продаж и приобрели некий товар Нужно подтянуть последнее рекламное объявление, по которому они звонили и интересовались тем товаром, который в итоге купили. В Таблице 1 представлена история звонков клиентов В Таблицу 2 нужно подтянуть рекламное объявление и дату звонка То есть нужно подтянуть рекламное объявление по совпадению: номера телефона и названию товара и по ближайшей дате звонка к дате покупки. Файл прилагаю Заранее благодарю за помощь!
P.S. Я всегда пользовался связкой ИНДЕКС и ПОИСКПОЗ:
Но сейчас эта формула мою задачу не решает, так как я не знаю, как в неё внедрить условие по поиску ближайшей к дате покупки даты звонка.
И ещё большая просьба: желательно без макросов
Коллеги, привет! Прошу помочь решить задачу в экселе.
Клиенты, которые ранее звонили по рекламным объявлениям и интересовались теми или иными товарами, пришли в офис продаж и приобрели некий товар Нужно подтянуть последнее рекламное объявление, по которому они звонили и интересовались тем товаром, который в итоге купили. В Таблице 1 представлена история звонков клиентов В Таблицу 2 нужно подтянуть рекламное объявление и дату звонка То есть нужно подтянуть рекламное объявление по совпадению: номера телефона и названию товара и по ближайшей дате звонка к дате покупки. Файл прилагаю Заранее благодарю за помощь!
P.S. Я всегда пользовался связкой ИНДЕКС и ПОИСКПОЗ:
В Таблицу 2 надо подтянуть данные (вторая вкладка) В вашей формуле, к сожалению, не учитывается условие про ближайшую дату. Должно подтягиваться только то рекламное объявление, где есть совпадение номера телефона, купленного товара и где дата звонка самая ближайшая к дате покупки. Понятно, что дата покупки должна быть позже даты звонка, то есть все звонки после даты покупки - не считаются
В Таблицу 2 надо подтянуть данные (вторая вкладка) В вашей формуле, к сожалению, не учитывается условие про ближайшую дату. Должно подтягиваться только то рекламное объявление, где есть совпадение номера телефона, купленного товара и где дата звонка самая ближайшая к дате покупки. Понятно, что дата покупки должна быть позже даты звонка, то есть все звонки после даты покупки - не считаютсяdmitriyaleksandrovichni
Шлюхогон42
Сообщение отредактировал dmitriyaleksandrovichni - Среда, 28.06.2023, 18:07
Pelena, спасибо, работает! Но можете, пожалуйста, объяснить подробно формулу? Мне нужно её понять, чтобы использовать в дальнейшем в работе, в том числе и при условии, что подтянуть номер объявления нужно только по номеру телефона, без привязки к купленному товару. Её же можно и таким образом использовать? Буду вам очень признателен за детальное объяснение!
Pelena, спасибо, работает! Но можете, пожалуйста, объяснить подробно формулу? Мне нужно её понять, чтобы использовать в дальнейшем в работе, в том числе и при условии, что подтянуть номер объявления нужно только по номеру телефона, без привязки к купленному товару. Её же можно и таким образом использовать? Буду вам очень признателен за детальное объяснение!dmitriyaleksandrovichni
части ('Таблица 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 - т.к. нам нужно одно наибольшее значение Функция ЕСЛИОШИБКА позволяет обработать ситуацию, когда покупки не было Как-то так
части ('Таблица 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
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Pelena, ещё такой вопрос: если не важно, чтобы дата звонка была раньше даты покупки? А главное, чтобы отклонение от даты покупки позже или раньше во времени было минимальным? Тут по идее как-то модуль надо зашить. Можете подсказать, как это сделать?
Pelena, ещё такой вопрос: если не важно, чтобы дата звонка была раньше даты покупки? А главное, чтобы отклонение от даты покупки позже или раньше во времени было минимальным? Тут по идее как-то модуль надо зашить. Можете подсказать, как это сделать?dmitriyaleksandrovichni
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 Как же тогда работает условие НАИБОЛЬШИЙ?
Помогите, пожалуйста!
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 Как же тогда работает условие НАИБОЛЬШИЙ?
значит номер строки будет делиться либо на 1, либо на 0
всё верно. Когда номер строки делится на 1, остаётся номер строки. Когда номер строки делится на 0, получается ошибка. Среди оставшихся номеров строк, выбираем самое большое и подставляем в ИНДЕКС. Например, исходная дата у нас в 5-ой строке. Наше условие выдаёт нам #ДЕЛ/0!;2;3;#ДЕЛ/0!;#ДЕЛ/0!;... дальше все ошибки, т.к. даты превышают исходную. Наибольшее значение из этого массива 3, соответственно, функция ИНДЕКС возвращает значение из 3-ей строки диапазона
значит номер строки будет делиться либо на 1, либо на 0
всё верно. Когда номер строки делится на 1, остаётся номер строки. Когда номер строки делится на 0, получается ошибка. Среди оставшихся номеров строк, выбираем самое большое и подставляем в ИНДЕКС. Например, исходная дата у нас в 5-ой строке. Наше условие выдаёт нам #ДЕЛ/0!;2;3;#ДЕЛ/0!;#ДЕЛ/0!;... дальше все ошибки, т.к. даты превышают исходную. Наибольшее значение из этого массива 3, соответственно, функция ИНДЕКС возвращает значение из 3-ей строки диапазонаPelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Pelena, теперь понял, спасибо! НАИБОЛЬШИЙ - будет выбирать наибольший номер строки, а так как у нас значения в таблице 1 отсортированы по датам от старых к новым, то номер строки самой поздней даты (то есть самой ближней к интересующей нас дате), будет наибольшим. Ура!)
Pelena, теперь понял, спасибо! НАИБОЛЬШИЙ - будет выбирать наибольший номер строки, а так как у нас значения в таблице 1 отсортированы по датам от старых к новым, то номер строки самой поздней даты (то есть самой ближней к интересующей нас дате), будет наибольшим. Ура!)dmitriyaleksandrovichni
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
