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

Вход

Регистрация

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

 

= Мир MS Excel/Выбор уникальных элементов по условию мин разницы дат - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбор уникальных элементов по условию мин разницы дат (Формулы/Formulas)
Выбор уникальных элементов по условию мин разницы дат
VadimVV Дата: Четверг, 23.06.2016, 18:37 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Добрый день.

Есть три поля: Дата получения, артикул и дата заявки.
Артикул может повторяться. Необходимо проверить задвоения артикула и в случае повторяющихся значений, исключить все значения, кроме ого, у которого дата получения была ближе к дате заявки (как в ту, так и в другую сторону).

Можно ли такое сделать формулами? Или исключительно макросами? Я хочу попробовать сделать формулами, т.к. как сделать неформулами я знаю (используя надстройки).

Прошу помочь. Пример прилагаю.
К сообщению приложен файл: 8949961.xlsx(8Kb)
 
Ответить
СообщениеДобрый день.

Есть три поля: Дата получения, артикул и дата заявки.
Артикул может повторяться. Необходимо проверить задвоения артикула и в случае повторяющихся значений, исключить все значения, кроме ого, у которого дата получения была ближе к дате заявки (как в ту, так и в другую сторону).

Можно ли такое сделать формулами? Или исключительно макросами? Я хочу попробовать сделать формулами, т.к. как сделать неформулами я знаю (используя надстройки).

Прошу помочь. Пример прилагаю.

Автор - VadimVV
Дата добавления - 23.06.2016 в 18:37
Nic70y Дата: Четверг, 23.06.2016, 18:56 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3476
Репутация: 722 ±
Замечаний: 0% ±

Excel 2013
массивные:
Код
=ЕСЛИОШИБКА(ИНДЕКС(A$2:A$12;ПОИСКПОЗ(МИН(ЕСЛИ(B22=B$2:B$12;ABS(A$2:A$12-C$2:C$12)));ЕСЛИ(B22=B$2:B$12;ABS(A$2:A$12-C$2:C$12));));"")
Код
=ЕСЛИОШИБКА(ИНДЕКС(B$2:B$12;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(B$2:B$12;B$2:B$12;)=СТРОКА(B$2:B$12)-1;СТРОКА(B$2:B$12)-1);СТРОКА(B1)));"")
и впр
Код
=ЕСЛИОШИБКА(ВПР(B22;B$2:C$12;2;);"")
К сообщению приложен файл: 2666960.xlsx(10Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)


Сообщение отредактировал Nic70y - Четверг, 23.06.2016, 18:57
 
Ответить
Сообщениемассивные:
Код
=ЕСЛИОШИБКА(ИНДЕКС(A$2:A$12;ПОИСКПОЗ(МИН(ЕСЛИ(B22=B$2:B$12;ABS(A$2:A$12-C$2:C$12)));ЕСЛИ(B22=B$2:B$12;ABS(A$2:A$12-C$2:C$12));));"")
Код
=ЕСЛИОШИБКА(ИНДЕКС(B$2:B$12;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(B$2:B$12;B$2:B$12;)=СТРОКА(B$2:B$12)-1;СТРОКА(B$2:B$12)-1);СТРОКА(B1)));"")
и впр
Код
=ЕСЛИОШИБКА(ВПР(B22;B$2:C$12;2;);"")

Автор - Nic70y
Дата добавления - 23.06.2016 в 18:56
VadimVV Дата: Пятница, 24.06.2016, 10:35 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Спасибо, работает. Но хотелось бы уточнить понять как работает:

Код
=ЕСЛИОШИБКА(ИНДЕКС(B$2:B$12;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(B$2:B$12;B$2:B$12;)=СТРОКА(B$2:B$12)-1;СТРОКА(B$2:B$12)-1);СТРОКА(B1)));"")
Здесь,я понимаю, что в столбце Артикул, мы с помощью Индекса ищем первый встречающийся уникальный номер, но как происходит поиск? И почему excel понимает, что второй раз не нужно этот номер отображать при воспроизведении результата. В частности, я не очень понимаю логику формулы отсюда:
Код
НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(B$2:B$12;B$2:B$12;)=СТРОКА(B$2:B$12)-1;СТРОКА(B$2:B$12)-1);СТРОКА(B1)))
. Вижу, что из всего столбца выбирается определенным образом значения, но как это происходит - не понимаю. Если не затруднит, поясните, пожалуйста. Я не гений в excel и многие операции делаю с помощью надстроек.

В поиске даты получения неясна эта часть:
Код
ПОИСКПОЗ(МИН(ЕСЛИ(B22=B$2:B$12;ABS(A$2:A$12-C$2:C$12)));ЕСЛИ(B22=B$2:B$12;ABS(A$2:A$12-C$2:C$12));));"")
Понимаю, что ищется минимальное значение, но опять же - не понятна логика :(

И здесь вот:
Код
=ЕСЛИОШИБКА(ВПР(B22;B$2:C$12;2;);"")
нет ли ошибки в том, что ВПР найдет первую попавшуюся дату, а не ту, которая будет соответствовать определенной строке?
 
Ответить
СообщениеСпасибо, работает. Но хотелось бы уточнить понять как работает:

Код
=ЕСЛИОШИБКА(ИНДЕКС(B$2:B$12;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(B$2:B$12;B$2:B$12;)=СТРОКА(B$2:B$12)-1;СТРОКА(B$2:B$12)-1);СТРОКА(B1)));"")
Здесь,я понимаю, что в столбце Артикул, мы с помощью Индекса ищем первый встречающийся уникальный номер, но как происходит поиск? И почему excel понимает, что второй раз не нужно этот номер отображать при воспроизведении результата. В частности, я не очень понимаю логику формулы отсюда:
Код
НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(B$2:B$12;B$2:B$12;)=СТРОКА(B$2:B$12)-1;СТРОКА(B$2:B$12)-1);СТРОКА(B1)))
. Вижу, что из всего столбца выбирается определенным образом значения, но как это происходит - не понимаю. Если не затруднит, поясните, пожалуйста. Я не гений в excel и многие операции делаю с помощью надстроек.

В поиске даты получения неясна эта часть:
Код
ПОИСКПОЗ(МИН(ЕСЛИ(B22=B$2:B$12;ABS(A$2:A$12-C$2:C$12)));ЕСЛИ(B22=B$2:B$12;ABS(A$2:A$12-C$2:C$12));));"")
Понимаю, что ищется минимальное значение, но опять же - не понятна логика :(

И здесь вот:
Код
=ЕСЛИОШИБКА(ВПР(B22;B$2:C$12;2;);"")
нет ли ошибки в том, что ВПР найдет первую попавшуюся дату, а не ту, которая будет соответствовать определенной строке?

Автор - VadimVV
Дата добавления - 24.06.2016 в 10:35
Nic70y Дата: Пятница, 24.06.2016, 11:15 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3476
Репутация: 722 ±
Замечаний: 0% ±

Excel 2013
столбец B:
выделяем часть формулы
Код
ПОИСКПОЗ(B$2:B$12;B$2:B$12;)
жмем F9, получаем{1:1:3:4:5:6:7:8:3:6:4}
Код
ЕСЛИ({1:1:3:4:5:6:7:8:3:6:4}=СТРОКА(B$2:B$12)-1;СТРОКА(B$2:B$12)-1)
соот. получаем ИСТИНУ только там, где =СТРОКА(B$2:B$12)-1, т.е {1:ЛОЖЬ:3:4:5:6:7:8:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}, с помощью НАИМЕНЬШИЙ извлекаем числа, в данном случае 1-я строка это 1, 2-я это 3 и т.д. так
Код
=ЕСЛИОШИБКА(ИНДЕКС(B$2:B$12;{1});"")
уже понятнее?
столбец A:
ищем позицию минимального числа в массиве, массив в данном случае
Код
ЕСЛИ(B22=B$2:B$12;ABS(A$2:A$12-C$2:C$12))
т.е. модуль разницы столбца A и C, при условии совпадения артикулов
/так же все можно проверить с помощью F9
столбец C:
нет ли ошибки в том, что ВПР найдет первую попавшуюся дату
у вас в примере даты заявки по артикулу одинаковы.


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)


Сообщение отредактировал Nic70y - Пятница, 24.06.2016, 11:27
 
Ответить
Сообщениестолбец B:
выделяем часть формулы
Код
ПОИСКПОЗ(B$2:B$12;B$2:B$12;)
жмем F9, получаем{1:1:3:4:5:6:7:8:3:6:4}
Код
ЕСЛИ({1:1:3:4:5:6:7:8:3:6:4}=СТРОКА(B$2:B$12)-1;СТРОКА(B$2:B$12)-1)
соот. получаем ИСТИНУ только там, где =СТРОКА(B$2:B$12)-1, т.е {1:ЛОЖЬ:3:4:5:6:7:8:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}, с помощью НАИМЕНЬШИЙ извлекаем числа, в данном случае 1-я строка это 1, 2-я это 3 и т.д. так
Код
=ЕСЛИОШИБКА(ИНДЕКС(B$2:B$12;{1});"")
уже понятнее?
столбец A:
ищем позицию минимального числа в массиве, массив в данном случае
Код
ЕСЛИ(B22=B$2:B$12;ABS(A$2:A$12-C$2:C$12))
т.е. модуль разницы столбца A и C, при условии совпадения артикулов
/так же все можно проверить с помощью F9
столбец C:
нет ли ошибки в том, что ВПР найдет первую попавшуюся дату
у вас в примере даты заявки по артикулу одинаковы.

Автор - Nic70y
Дата добавления - 24.06.2016 в 11:15
VadimVV Дата: Пятница, 24.06.2016, 11:52 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Да, алгоритм понятнее стал:
Цитата
жмем F9, получаем{1:1:3:4:5:6:7:8:3:6:4}
здесь идут по порядку значения, т.е. в первой ячейке значение 1, во второй - также значение 1 и т.д.. В последней ячейке значение 4 ячейки. Понятна логика.

Цитата
соот. получаем ИСТИНУ только там, где =СТРОКА(B$2:B$12)-1, т.е {1:ЛОЖЬ:3:4:5:6:7:8:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}, с помощью НАИМЕНЬШИЙ извлекаем числа, в данном случае 1-я строка это 1, 2-я это 3 и т.д. так
здесь тоже понятнее стало: СТРОКА ищет сопоставления, т.е. находя повторение элемента выдает ложь.

Спасибо за пояснения.

Цитата
у вас в примере даты заявки по артикулу одинаковы.
а как быть, если даты не одинаковы?
 
Ответить
СообщениеДа, алгоритм понятнее стал:
Цитата
жмем F9, получаем{1:1:3:4:5:6:7:8:3:6:4}
здесь идут по порядку значения, т.е. в первой ячейке значение 1, во второй - также значение 1 и т.д.. В последней ячейке значение 4 ячейки. Понятна логика.

Цитата
соот. получаем ИСТИНУ только там, где =СТРОКА(B$2:B$12)-1, т.е {1:ЛОЖЬ:3:4:5:6:7:8:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}, с помощью НАИМЕНЬШИЙ извлекаем числа, в данном случае 1-я строка это 1, 2-я это 3 и т.д. так
здесь тоже понятнее стало: СТРОКА ищет сопоставления, т.е. находя повторение элемента выдает ложь.

Спасибо за пояснения.

Цитата
у вас в примере даты заявки по артикулу одинаковы.
а как быть, если даты не одинаковы?

Автор - VadimVV
Дата добавления - 24.06.2016 в 11:52
Nic70y Дата: Пятница, 24.06.2016, 12:09 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3476
Репутация: 722 ±
Замечаний: 0% ±

Excel 2013
а как быть, если даты не одинаковы?
тогда не понятно что считать уникальными артикулами, если даты заявки разные, или какую из них брать.
составьте пример с разными датами и соот. результат


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
а как быть, если даты не одинаковы?
тогда не понятно что считать уникальными артикулами, если даты заявки разные, или какую из них брать.
составьте пример с разными датами и соот. результат

Автор - Nic70y
Дата добавления - 24.06.2016 в 12:09
VadimVV Дата: Пятница, 24.06.2016, 12:22 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Цитата
что считать уникальными артикулами, если даты заявки разные
Уникальный будет тот, у которого минимальная разница между датами, вне зависимости от того, какая дата раньше (т.е. дата получения может быть раньше даты заявки).

Например:
10.05.2016 12345 02.05.2016
15.05.2016 12345 18.05.2016

Получаем
15.05.2016 12345 18.05.2016, т.к. разница между датами 3 дня, что меньше разницы между датами в 7 дней в первом случае.

Пример приложил
К сообщению приложен файл: 7491325.xlsx(8Kb)
 
Ответить
Сообщение
Цитата
что считать уникальными артикулами, если даты заявки разные
Уникальный будет тот, у которого минимальная разница между датами, вне зависимости от того, какая дата раньше (т.е. дата получения может быть раньше даты заявки).

Например:
10.05.2016 12345 02.05.2016
15.05.2016 12345 18.05.2016

Получаем
15.05.2016 12345 18.05.2016, т.к. разница между датами 3 дня, что меньше разницы между датами в 7 дней в первом случае.

Пример приложил

Автор - VadimVV
Дата добавления - 24.06.2016 в 12:22
Nic70y Дата: Пятница, 24.06.2016, 12:41 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3476
Репутация: 722 ±
Замечаний: 0% ±

Excel 2013
ну это совсем другое дело.
принцип тот же.
К сообщению приложен файл: 6713954.xlsx(10Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщениену это совсем другое дело.
принцип тот же.

Автор - Nic70y
Дата добавления - 24.06.2016 в 12:41
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбор уникальных элементов по условию мин разницы дат (Формулы/Formulas)
Страница 1 из 11
Поиск:

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