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

Вход

Регистрация

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

 

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

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

Добрый день!
Помогите решить задачу пожалуйста? Есть заданная дата, нужно в таблице с данными найти соответствующее этой дате значение, удовлетворяющее определенному условию. Если же в заданную дату этого значения нет, то тогда использовать значение соответствующее ближайшей дате от заданной.

Вот здесь пример: https://docs.google.com/spreads....t#gid=0
Необходимо найти цену заказа товара ('Заказы (исх.)'!H:H) на вкладке "Заказы (исх.)", соответствующую заданной дате в строке "2:2" на вкладке "Лист1" и если выполняется условие B3='Заказы (исх.)'!N:N
Если же в заданную дату это условие не выполняется (B3='Заказы (исх.)'!N:N), то нужно найти ближайшую дату, где оно выполняется, и взять значение оттуда.


Сообщение отредактировал dminicab - Четверг, 27.10.2022, 23:07
 
Ответить
СообщениеДобрый день!
Помогите решить задачу пожалуйста? Есть заданная дата, нужно в таблице с данными найти соответствующее этой дате значение, удовлетворяющее определенному условию. Если же в заданную дату этого значения нет, то тогда использовать значение соответствующее ближайшей дате от заданной.

Вот здесь пример: https://docs.google.com/spreads....t#gid=0
Необходимо найти цену заказа товара ('Заказы (исх.)'!H:H) на вкладке "Заказы (исх.)", соответствующую заданной дате в строке "2:2" на вкладке "Лист1" и если выполняется условие B3='Заказы (исх.)'!N:N
Если же в заданную дату это условие не выполняется (B3='Заказы (исх.)'!N:N), то нужно найти ближайшую дату, где оно выполняется, и взять значение оттуда.

Автор - dminicab
Дата добавления - 27.10.2022 в 21:35
Serge_007 Дата: Пятница, 28.10.2022, 08:58 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 15538
Репутация: 2558 ±
Замечаний: ±

Excel 2016
К сообщению приложен файл: 9169541.png(52.3 Kb)


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение

Автор - Serge_007
Дата добавления - 28.10.2022 в 08:58
Gustav Дата: Пятница, 28.10.2022, 08:59 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Если же в заданную дату этого значения нет, то тогда использовать значение соответствующее ближайшей дате от заданной.

"Ближайшей" - ДО или ПОСЛЕ заданной?


Нет доступа к примеру. Дайте хотя бы на просмотр.


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Если же в заданную дату этого значения нет, то тогда использовать значение соответствующее ближайшей дате от заданной.

"Ближайшей" - ДО или ПОСЛЕ заданной?


Нет доступа к примеру. Дайте хотя бы на просмотр.

Автор - Gustav
Дата добавления - 28.10.2022 в 08:59
dminicab Дата: Пятница, 28.10.2022, 22:42 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 20% ±

"Ближайшей" - ДО или ПОСЛЕ заданной?

До заданной даты
Прошу прощения за доступ. Не проверил
Открыл
 
Ответить
Сообщение
"Ближайшей" - ДО или ПОСЛЕ заданной?

До заданной даты
Прошу прощения за доступ. Не проверил
Открыл

Автор - dminicab
Дата добавления - 28.10.2022 в 22:42
dminicab Дата: Пятница, 28.10.2022, 22:43 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 20% ±

Открыл доступ. Прошу прощения!
 
Ответить
СообщениеОткрыл доступ. Прошу прощения!

Автор - dminicab
Дата добавления - 28.10.2022 в 22:43
Gustav Дата: Суббота, 29.10.2022, 01:26 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Насколько я понял, при наличии нескольких времен внутри одной даты Вас интересует значение цены, соответствующее максимальному (т.е. наиболее позднему) из этих времен. Фактически это означает поиск цены для последней секунды "текущих" суток, т.е. для момента времени Дата + 23:59:59. При отсутствии в таблице записи, точно соответствующей этому моменту времени, берется ближайшее меньшее время, например, Дата + 22:00. Т.е. поведение алгоритма соответствует поведению функции ВПР (VLOOKUP) c четвертым параметром, равным 1.

С учетом вышесказанного формулу предлагаю такую (для ячейки Q3):
[vba]
Код
=INDEX( SORT( FILTER(
    'Заказы (исх.)'!$H:$H;
    'Заказы (исх.)'!$N:$N = --$B3;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2-7; Q$2+1; 0; 0)
); 1; 0); 1)
[/vba]Функция FILTER здесь отбирает строки с датами не раньше недели (это я так решил) до "текущей" даты (Q$2-7) и все строки "текущих" суток (Q$2+1), не включая крайние моменты времени этого восьмидневного интервала (0; 0). Мой диапазон "7 дней в прошлое" можно сократить до приемлемого меньшего количества дней - главное, чтобы до момента времени Дата + 23:59:59 функция FILTER гарантированно возвращала хотя бы одну строку, иначе возникнет ошибка. Задайте это кол-во дней сами, основываясь на собственном опыте.

Если же вдруг всё наоборот и интересует самое ранее время внутри текущих суток (в идеале на момент времени Дата + 00:00:00), то формуле можно придать следующий вид (с подстраховкой - обработкой возможного отсутствия строк с текущей датой в принципе):
[vba]
Код
=IFERROR(
INDEX( SORT( FILTER(
    'Заказы (исх.)'!$H:$H;
    'Заказы (исх.)'!$N:$N = --$B3;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2; Q$2+1; 1; 0)
); 1; 1); 1);
INDEX( SORT( FILTER(
    'Заказы (исх.)'!$H:$H;
    'Заказы (исх.)'!$N:$N = --$B3;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2-7; Q$2+1; 0; 0)
); 1; 0); 1)
)
[/vba]Как видно, первая формула стала второй ("ошибочной") частью второй формулы ("найти хоть что-то в прошлом"), а первая часть второй формулы при этом ищет (если найдёт) самое ранее время внутри текущих суток.

Если же интересует цена на какое-то специфическое время внутри текущих суток, например, в полдень 12:00, то в первой части второй формулы условие для дат нужно записать одним из способов ниже:
[vba]
Код
ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2 + TIMEVALUE("12:00"); Q$2+1; 1; 0)

ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2 + "12:00"; Q$2+1; 1; 0)

ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2 + 12/24; Q$2+1; 1; 0)

ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2 + 0,5; Q$2+1; 1; 0)
[/vba]
[p.s.]Есть важное замечание - см. ниже моё сообщение №9[/p.s.]
Вместо 'Заказы (исх.)'!$H:$H надо {'Заказы (исх.)'!$B:$B\ 'Заказы (исх.)'!$H:$H - 'Заказы (исх.)'!$I:$I}
и вместо INDEX(...; 1) надо INDEX(...; 1; 2)


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Суббота, 29.10.2022, 18:21
 
Ответить
СообщениеНасколько я понял, при наличии нескольких времен внутри одной даты Вас интересует значение цены, соответствующее максимальному (т.е. наиболее позднему) из этих времен. Фактически это означает поиск цены для последней секунды "текущих" суток, т.е. для момента времени Дата + 23:59:59. При отсутствии в таблице записи, точно соответствующей этому моменту времени, берется ближайшее меньшее время, например, Дата + 22:00. Т.е. поведение алгоритма соответствует поведению функции ВПР (VLOOKUP) c четвертым параметром, равным 1.

С учетом вышесказанного формулу предлагаю такую (для ячейки Q3):
[vba]
Код
=INDEX( SORT( FILTER(
    'Заказы (исх.)'!$H:$H;
    'Заказы (исх.)'!$N:$N = --$B3;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2-7; Q$2+1; 0; 0)
); 1; 0); 1)
[/vba]Функция FILTER здесь отбирает строки с датами не раньше недели (это я так решил) до "текущей" даты (Q$2-7) и все строки "текущих" суток (Q$2+1), не включая крайние моменты времени этого восьмидневного интервала (0; 0). Мой диапазон "7 дней в прошлое" можно сократить до приемлемого меньшего количества дней - главное, чтобы до момента времени Дата + 23:59:59 функция FILTER гарантированно возвращала хотя бы одну строку, иначе возникнет ошибка. Задайте это кол-во дней сами, основываясь на собственном опыте.

Если же вдруг всё наоборот и интересует самое ранее время внутри текущих суток (в идеале на момент времени Дата + 00:00:00), то формуле можно придать следующий вид (с подстраховкой - обработкой возможного отсутствия строк с текущей датой в принципе):
[vba]
Код
=IFERROR(
INDEX( SORT( FILTER(
    'Заказы (исх.)'!$H:$H;
    'Заказы (исх.)'!$N:$N = --$B3;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2; Q$2+1; 1; 0)
); 1; 1); 1);
INDEX( SORT( FILTER(
    'Заказы (исх.)'!$H:$H;
    'Заказы (исх.)'!$N:$N = --$B3;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2-7; Q$2+1; 0; 0)
); 1; 0); 1)
)
[/vba]Как видно, первая формула стала второй ("ошибочной") частью второй формулы ("найти хоть что-то в прошлом"), а первая часть второй формулы при этом ищет (если найдёт) самое ранее время внутри текущих суток.

Если же интересует цена на какое-то специфическое время внутри текущих суток, например, в полдень 12:00, то в первой части второй формулы условие для дат нужно записать одним из способов ниже:
[vba]
Код
ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2 + TIMEVALUE("12:00"); Q$2+1; 1; 0)

ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2 + "12:00"; Q$2+1; 1; 0)

ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2 + 12/24; Q$2+1; 1; 0)

ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2 + 0,5; Q$2+1; 1; 0)
[/vba]
[p.s.]Есть важное замечание - см. ниже моё сообщение №9[/p.s.]
Вместо 'Заказы (исх.)'!$H:$H надо {'Заказы (исх.)'!$B:$B\ 'Заказы (исх.)'!$H:$H - 'Заказы (исх.)'!$I:$I}
и вместо INDEX(...; 1) надо INDEX(...; 1; 2)

Автор - Gustav
Дата добавления - 29.10.2022 в 01:26
dminicab Дата: Суббота, 29.10.2022, 16:39 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 20% ±

Насколько я понял, при наличии нескольких времен внутри одной даты Вас интересует значение цены, соответствующее максимальному (т.е. наиболее позднему) из этих времен.

Не совсем :angel:

По времени мне не нужно. Мне нужно, чтобы функция возвращала цену " 'Заказы (исх.)'!$H:$H ", в заданную дату " Q$2 ". С условием соответствия " 'Заказы (исх.)'!$N:$N = --$B3 ". Если же функция не находит цену в заданную дату, нужно, чтобы она (функция) брала цену с ближайшей даты до заданной " 2:Q$2 ".
Т.е. если в заданную дату в таблице " 'Заказы (исх.) " значение цены отсутствует, нужно чтобы она (функция) искала цену в ближайшую дату до заданной даты, и возвращала цену за ту дату, в которой она (цена) есть.

А вообще мне нужно, чтобы возвращалась цена (" 'Заказы (исх.)'!$H:$H ") минус скидка (" 'Заказы (исх.)'!$I:$I "). Но я в принципе потом смогу это сам высчитать.
 
Ответить
Сообщение
Насколько я понял, при наличии нескольких времен внутри одной даты Вас интересует значение цены, соответствующее максимальному (т.е. наиболее позднему) из этих времен.

Не совсем :angel:

По времени мне не нужно. Мне нужно, чтобы функция возвращала цену " 'Заказы (исх.)'!$H:$H ", в заданную дату " Q$2 ". С условием соответствия " 'Заказы (исх.)'!$N:$N = --$B3 ". Если же функция не находит цену в заданную дату, нужно, чтобы она (функция) брала цену с ближайшей даты до заданной " 2:Q$2 ".
Т.е. если в заданную дату в таблице " 'Заказы (исх.) " значение цены отсутствует, нужно чтобы она (функция) искала цену в ближайшую дату до заданной даты, и возвращала цену за ту дату, в которой она (цена) есть.

А вообще мне нужно, чтобы возвращалась цена (" 'Заказы (исх.)'!$H:$H ") минус скидка (" 'Заказы (исх.)'!$I:$I "). Но я в принципе потом смогу это сам высчитать.

Автор - dminicab
Дата добавления - 29.10.2022 в 16:39
Gustav Дата: Суббота, 29.10.2022, 16:48 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Мне нужно, чтобы функция возвращала цену " 'Заказы (исх.)'!$H:$H ", в заданную дату " Q$2 ". С условием соответствия " 'Заказы (исх.)'!$N:$N = --$B3 ". Если же функция не находит цену в заданную дату

А если находит НЕСКОЛЬКО цен в заданной дате? Какую брать? Самую раннюю из них? А если самая ранняя имеет время 19:00, то брать её или всё же последнюю цену ДО текущей даты? Т.е. фактически всегда искать цену на момент времени 00:00 текущей даты (т.е. даты без "хвоста" времени), так?

Если так, то всё, что нужно сделать - убрать в моей самой первой формуле "+1" в третьем параметре функции ISBETWEEN, а пятый заменить на "1":
[vba]
Код
=INDEX( SORT( FILTER(
    'Заказы (исх.)'!$H:$H;
    'Заказы (исх.)'!$N:$N = --$B3;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2-7; Q$2; 0; 1)
); 1; 0); 1)
[/vba]
[p.s.]Есть важное замечание - см. ниже моё сообщение №9[/p.s.]
Вместо 'Заказы (исх.)'!$H:$H надо {'Заказы (исх.)'!$B:$B\ 'Заказы (исх.)'!$H:$H - 'Заказы (исх.)'!$I:$I}
и вместо INDEX(...; 1) надо INDEX(...; 1; 2)


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Суббота, 29.10.2022, 18:22
 
Ответить
Сообщение
Мне нужно, чтобы функция возвращала цену " 'Заказы (исх.)'!$H:$H ", в заданную дату " Q$2 ". С условием соответствия " 'Заказы (исх.)'!$N:$N = --$B3 ". Если же функция не находит цену в заданную дату

А если находит НЕСКОЛЬКО цен в заданной дате? Какую брать? Самую раннюю из них? А если самая ранняя имеет время 19:00, то брать её или всё же последнюю цену ДО текущей даты? Т.е. фактически всегда искать цену на момент времени 00:00 текущей даты (т.е. даты без "хвоста" времени), так?

Если так, то всё, что нужно сделать - убрать в моей самой первой формуле "+1" в третьем параметре функции ISBETWEEN, а пятый заменить на "1":
[vba]
Код
=INDEX( SORT( FILTER(
    'Заказы (исх.)'!$H:$H;
    'Заказы (исх.)'!$N:$N = --$B3;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2-7; Q$2; 0; 1)
); 1; 0); 1)
[/vba]
[p.s.]Есть важное замечание - см. ниже моё сообщение №9[/p.s.]
Вместо 'Заказы (исх.)'!$H:$H надо {'Заказы (исх.)'!$B:$B\ 'Заказы (исх.)'!$H:$H - 'Заказы (исх.)'!$I:$I}
и вместо INDEX(...; 1) надо INDEX(...; 1; 2)

Автор - Gustav
Дата добавления - 29.10.2022 в 16:48
Gustav Дата: Суббота, 29.10.2022, 18:11 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
ОЙ-ОЙ-ОЙ! Только щас заметил - сортирую-то я не даты, а цены!! Как-то удалил столбец дат в процессе манипуляций... :o Скорее-скорее всё исправляем! И с учетом вот этого:
нужно, чтобы возвращалась цена (" 'Заказы (исх.)'!$H:$H ") минус скидка (" 'Заказы (исх.)'!$I:$I ")
последнюю формулу записываем так:
[vba]
Код
=INDEX( SORT( FILTER(
    {'Заказы (исх.)'!$B:$B\ 'Заказы (исх.)'!$H:$H - 'Заказы (исх.)'!$I:$I};
    'Заказы (исх.)'!$N:$N = --$B3;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2-7; Q$2; 0; 1)
); 1; 0); 1; 2)
[/vba]


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеОЙ-ОЙ-ОЙ! Только щас заметил - сортирую-то я не даты, а цены!! Как-то удалил столбец дат в процессе манипуляций... :o Скорее-скорее всё исправляем! И с учетом вот этого:
нужно, чтобы возвращалась цена (" 'Заказы (исх.)'!$H:$H ") минус скидка (" 'Заказы (исх.)'!$I:$I ")
последнюю формулу записываем так:
[vba]
Код
=INDEX( SORT( FILTER(
    {'Заказы (исх.)'!$B:$B\ 'Заказы (исх.)'!$H:$H - 'Заказы (исх.)'!$I:$I};
    'Заказы (исх.)'!$N:$N = --$B3;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2-7; Q$2; 0; 1)
); 1; 0); 1; 2)
[/vba]

Автор - Gustav
Дата добавления - 29.10.2022 в 18:11
dminicab Дата: Воскресенье, 30.10.2022, 01:44 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 20% ±

Если так, то всё, что нужно сделать - убрать в моей самой первой формуле "+1" в третьем параметре функции ISBETWEEN, а пятый заменить на "1":

=INDEX( SORT( FILTER(
    'Заказы (исх.)'!$H:$H;
    'Заказы (исх.)'!$N:$N = --$B3;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2-7; Q$2; 0; 1)
); 1; 0); 1)

Я подставил указанную формулу, и она почему-то не возвращает корректное значение. Формула возвращает значение "16490" или "16990", фактическое же значение в таблице "11920"
С чем это может быть связано?
 
Ответить
Сообщение
Если так, то всё, что нужно сделать - убрать в моей самой первой формуле "+1" в третьем параметре функции ISBETWEEN, а пятый заменить на "1":

=INDEX( SORT( FILTER(
    'Заказы (исх.)'!$H:$H;
    'Заказы (исх.)'!$N:$N = --$B3;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2-7; Q$2; 0; 1)
); 1; 0); 1)

Я подставил указанную формулу, и она почему-то не возвращает корректное значение. Формула возвращает значение "16490" или "16990", фактическое же значение в таблице "11920"
С чем это может быть связано?

Автор - dminicab
Дата добавления - 30.10.2022 в 01:44
Gustav Дата: Воскресенье, 30.10.2022, 03:13 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
С чем это может быть связано?

А и правда с чем? Может быть, с тем, что Вы в своем сообщении №10 цитируете мое №8, не прочитав более позднее моё №9 ? Так читайте его скорее - там всё написано и исправлено, даже с учётом Ваших последних пожеланий про скидку.


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
С чем это может быть связано?

А и правда с чем? Может быть, с тем, что Вы в своем сообщении №10 цитируете мое №8, не прочитав более позднее моё №9 ? Так читайте его скорее - там всё написано и исправлено, даже с учётом Ваших последних пожеланий про скидку.

Автор - Gustav
Дата добавления - 30.10.2022 в 03:13
dminicab Дата: Воскресенье, 30.10.2022, 08:56 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 20% ±

А и правда с чем? Может быть, с тем, что Вы в своем сообщении №10 цитируете мое №8, не прочитав более позднее моё №9 ? Так читайте его скорее - там всё написано и исправлено, даже с учётом Ваших последних пожеланий про скидку.

Увидел, спасибо! Но все равно возвращает не корректные данные(

Я ее немного подправил с учетом того, что скидка в столбце " 'Заказы (исх.)'!$I:$I " указана в процентах.
Код
=ИНДЕКС( SORT( FILTER(
    {'Заказы (исх.)'!$B:$B\ 'Заказы (исх.)'!$H:$H - ('Заказы (исх.)'!$I:$I*'Заказы (исх.)'!$H:$H/100)};
    'Заказы (исх.)'!$N:$N = --$B4;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2-7; Q$2; 0; 1)
); 1; 0); 1; 2)

Но и все равно функция дает результат "5947", а проверка показывает, что результат должен быть "4581,5". Причем при одних и тех же вводных, она почему-то 12.10 дает результат "5772", а в остальные даты "5947".
Может быть я что-то не так делаю?
 
Ответить
Сообщение
А и правда с чем? Может быть, с тем, что Вы в своем сообщении №10 цитируете мое №8, не прочитав более позднее моё №9 ? Так читайте его скорее - там всё написано и исправлено, даже с учётом Ваших последних пожеланий про скидку.

Увидел, спасибо! Но все равно возвращает не корректные данные(

Я ее немного подправил с учетом того, что скидка в столбце " 'Заказы (исх.)'!$I:$I " указана в процентах.
Код
=ИНДЕКС( SORT( FILTER(
    {'Заказы (исх.)'!$B:$B\ 'Заказы (исх.)'!$H:$H - ('Заказы (исх.)'!$I:$I*'Заказы (исх.)'!$H:$H/100)};
    'Заказы (исх.)'!$N:$N = --$B4;
    ISBETWEEN('Заказы (исх.)'!$B:$B; Q$2-7; Q$2; 0; 1)
); 1; 0); 1; 2)

Но и все равно функция дает результат "5947", а проверка показывает, что результат должен быть "4581,5". Причем при одних и тех же вводных, она почему-то 12.10 дает результат "5772", а в остальные даты "5947".
Может быть я что-то не так делаю?

Автор - dminicab
Дата добавления - 30.10.2022 в 08:56
Gustav Дата: Воскресенье, 30.10.2022, 10:09 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
проверка показывает, что результат должен быть "4581,5"

Это для какой даты и для какого артикула такой результат получается? Я посчитал "сумму - скидку" для всей таблицы "Заказы (исх.)" - ни одна ее строка не возвращает такого значения. Ближайшее к этому значению снизу - "4558", а потом сразу идёт значение "4598", которое уже больше указанного Вами.

И у Вас моя формула, предназначенная для ячейки Q3 (о чем написано в сообщении №6), т.е. для даты 10.10, была введена без корректировки адресов в условии по датам в ячейку S3, т.е. для даты 12.10. Т.е. у Вас сейчас в таблице напротив даты 12.10 стоит формула, считающая для даты 10.10.

На картинке - все записи для трех артикулов за период с 08.10 по 17.10 (отсортированы по убыванию даты/времени). Ну, и где что неправильно?

К сообщению приложен файл: 4638793.png(12.3 Kb)


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Воскресенье, 30.10.2022, 10:50
 
Ответить
Сообщение
проверка показывает, что результат должен быть "4581,5"

Это для какой даты и для какого артикула такой результат получается? Я посчитал "сумму - скидку" для всей таблицы "Заказы (исх.)" - ни одна ее строка не возвращает такого значения. Ближайшее к этому значению снизу - "4558", а потом сразу идёт значение "4598", которое уже больше указанного Вами.

И у Вас моя формула, предназначенная для ячейки Q3 (о чем написано в сообщении №6), т.е. для даты 10.10, была введена без корректировки адресов в условии по датам в ячейку S3, т.е. для даты 12.10. Т.е. у Вас сейчас в таблице напротив даты 12.10 стоит формула, считающая для даты 10.10.

На картинке - все записи для трех артикулов за период с 08.10 по 17.10 (отсортированы по убыванию даты/времени). Ну, и где что неправильно?


Автор - Gustav
Дата добавления - 30.10.2022 в 10:09
dminicab Дата: Воскресенье, 30.10.2022, 10:35 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 20% ±

Это для какой даты и для какого артикула такой результат получается?

Ну вот к примеру дата: 12.08.22 (в прошлом примере ошибся с датой), артикул: 39786769
Я беру общую цену " 'Заказы (исх.)'!$H6 " = 11920 и вычитаю из нее скидку " 'Заказы (исх.)'!$I6 " = 65%, которая в денежном выражении будет составлять => 11920*65% = 7338,5. И затем размер этой скидки я вычитаю из общей цены, чтобы получить фактическую рыночную цену 11920-7338,5 = 4581,5
Как-то так у меня получается

И тут для расчетов даты нужно использовать столбец " 'Заказы (исх.)'!$C:$C ", а не " 'Заказы (исх.)'!$B:$B "

Все, разобрался. Вернулся к первоначально использованным датам (октябрь). Да цену вычисляются корректно, только вот почему-то у меня не происходит изменение цены в нужную дату. Например артикул "39786769". У него до 10.10 цена 5772, а потом с 11.10 вырастает до 5947. Но у меня почему-то этот рост не с 11.10, а с 13.10


Сообщение отредактировал dminicab - Воскресенье, 30.10.2022, 11:09
 
Ответить
Сообщение
Это для какой даты и для какого артикула такой результат получается?

Ну вот к примеру дата: 12.08.22 (в прошлом примере ошибся с датой), артикул: 39786769
Я беру общую цену " 'Заказы (исх.)'!$H6 " = 11920 и вычитаю из нее скидку " 'Заказы (исх.)'!$I6 " = 65%, которая в денежном выражении будет составлять => 11920*65% = 7338,5. И затем размер этой скидки я вычитаю из общей цены, чтобы получить фактическую рыночную цену 11920-7338,5 = 4581,5
Как-то так у меня получается

И тут для расчетов даты нужно использовать столбец " 'Заказы (исх.)'!$C:$C ", а не " 'Заказы (исх.)'!$B:$B "

Все, разобрался. Вернулся к первоначально использованным датам (октябрь). Да цену вычисляются корректно, только вот почему-то у меня не происходит изменение цены в нужную дату. Например артикул "39786769". У него до 10.10 цена 5772, а потом с 11.10 вырастает до 5947. Но у меня почему-то этот рост не с 11.10, а с 13.10

Автор - dminicab
Дата добавления - 30.10.2022 в 10:35
Gustav Дата: Воскресенье, 30.10.2022, 11:05 | Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
11920*65% = 7338,5. ... Как-то так у меня получается

Аха, у меня получается 11920*65% = 7748. Что делать будем? Голосование организуем?
И тут для расчетов даты нужно использовать столбец " 'Заказы (исх.)'!$C:$C ", а не " 'Заказы (исх.)'!$B:$B "

А вот об этом Вы бы еще через год сказали. Знаете, что-то я устал этому топику помогать, пойду, пожалуй. Материала уже и так предостаточно выдано.


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Воскресенье, 30.10.2022, 11:11
 
Ответить
Сообщение
11920*65% = 7338,5. ... Как-то так у меня получается

Аха, у меня получается 11920*65% = 7748. Что делать будем? Голосование организуем?
И тут для расчетов даты нужно использовать столбец " 'Заказы (исх.)'!$C:$C ", а не " 'Заказы (исх.)'!$B:$B "

А вот об этом Вы бы еще через год сказали. Знаете, что-то я устал этому топику помогать, пойду, пожалуй. Материала уже и так предостаточно выдано.

Автор - Gustav
Дата добавления - 30.10.2022 в 11:05
dminicab Дата: Воскресенье, 30.10.2022, 11:11 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 20% ±

Аха, у меня получается 11920*65% = 7748. Что делать будем? Голосование организуем?

Ну я там с датами немного напутал взятых для примера. Выше ответил:)

Все, разобрался. Вернулся к первоначально использованным датам (октябрь). Да цену вычисляются корректно, только вот почему-то у меня не происходит изменение цены в нужную дату. Например артикул "39786769". У него до 10.10 цена 5772, а потом с 11.10 вырастает до 5947. Но у меня почему-то этот рост не с 11.10, а с 13.10
 
Ответить
Сообщение
Аха, у меня получается 11920*65% = 7748. Что делать будем? Голосование организуем?

Ну я там с датами немного напутал взятых для примера. Выше ответил:)

Все, разобрался. Вернулся к первоначально использованным датам (октябрь). Да цену вычисляются корректно, только вот почему-то у меня не происходит изменение цены в нужную дату. Например артикул "39786769". У него до 10.10 цена 5772, а потом с 11.10 вырастает до 5947. Но у меня почему-то этот рост не с 11.10, а с 13.10

Автор - dminicab
Дата добавления - 30.10.2022 в 11:11
Gustav Дата: Воскресенье, 30.10.2022, 11:37 | Сообщение № 17
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
только вот почему-то у меня не происходит изменение цены в нужную дату. Например артикул "39786769". У него до 10.10 цена 5772, а потом с 11.10 вырастает до 5947. Но у меня почему-то этот рост не с 11.10, а с 13.10


И у Вас моя формула, предназначенная для ячейки Q3 (о чем написано в сообщении №6), т.е. для даты 10.10, была введена без корректировки адресов в условии по датам в ячейку S3, т.е. для даты 12.10. Т.е. у Вас сейчас в таблице напротив даты 12.10 стоит формула, считающая для даты 10.10.


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
только вот почему-то у меня не происходит изменение цены в нужную дату. Например артикул "39786769". У него до 10.10 цена 5772, а потом с 11.10 вырастает до 5947. Но у меня почему-то этот рост не с 11.10, а с 13.10


И у Вас моя формула, предназначенная для ячейки Q3 (о чем написано в сообщении №6), т.е. для даты 10.10, была введена без корректировки адресов в условии по датам в ячейку S3, т.е. для даты 12.10. Т.е. у Вас сейчас в таблице напротив даты 12.10 стоит формула, считающая для даты 10.10.

Автор - Gustav
Дата добавления - 30.10.2022 в 11:37
dminicab Дата: Воскресенье, 30.10.2022, 11:51 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 20% ±

Да, так и есть. Недосмотрел, простите.
Все работает, спасибо огромное! Отблагодарил в tip box ^_^
 
Ответить
СообщениеДа, так и есть. Недосмотрел, простите.
Все работает, спасибо огромное! Отблагодарил в tip box ^_^

Автор - dminicab
Дата добавления - 30.10.2022 в 11:51
Gustav Дата: Воскресенье, 30.10.2022, 14:29 | Сообщение № 19
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Отблагодарил в tip box

Спасибо, приятно, выпью пивка за бесперебойную работу Вашей таблицы! beer


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Отблагодарил в tip box

Спасибо, приятно, выпью пивка за бесперебойную работу Вашей таблицы! beer

Автор - Gustav
Дата добавления - 30.10.2022 в 14:29
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Поиск ближайшего значения к заданной дате по условию (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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