Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Подтянуть тарифы с учётом соответствия дат
 
В правую таблицу нужно подтянуть тарифы из левой таблицы с учётом соответствия дат. Во вложенном примере Январь 2018 соответствует диапазону из левой таблицы с 1 июля 2017 по 30 апреля 2018.
Левая таблица в оригинальной реализации лежит в другом файле и вставлена в этот файл через подключение .odc , она периодически обновляется.
Это я готовлю приход-расход для своего посёлка, уже голову сломал. Если не подскажете умное решение, сделаю выпадающий список с ограничением ввода данных со ссылкой на тарифы в Левой таблице и вставлю его в Правую таблицу.
Спасибо скину на телефон или карту.
 
Попробуйте так. Массивная

=IFERROR(INDEX(ТарифыЭлектро_1[Тариф Т1];MATCH(LARGE((ТарифыЭлектро_1[ДатаНачала]<=--("1 " &[@[За период (Месяц)]]&" " &[@[За период (Год)]]))*ТарифыЭлектро_1[ДатаНачала];1);ТарифыЭлектро_1[ДатаНачала];0));"")

или тоже массивная
=SUMPRODUCT(ТарифыЭлектро_1[Тариф Т1]*(ТарифыЭлектро_1[ДатаНачала]<=DATEVALUE("1 " &Членск[@[За период (Месяц)]]&" " &Членск[@[За период (Год)]]))*(IF(ТарифыЭлектро_1[ДатаКонца]>0;ТарифыЭлектро_1[ДатаКонца];9^9)>DATEVALUE("1 " &Членск[@[За период (Месяц)]]&" " &Членск[@[За период (Год)]])))

Если  первая таблица точно упорядочена, то не массивная

=LOOKUP(2;1/(ТарифыЭлектро_1[ДатаНачала]<=DATEVALUE("1 " &Членск[@[За период (Месяц)]]&" " &Членск[@[За период (Год)]]));ТарифыЭлектро_1[Тариф Т1])

Изменено: БМВ - 12 Апр 2018 18:30:46
 
Ваш файл пример работает. Спасибо БМВ! Не могли бы Вы объяснить логику формулы? Ибо если однажды что-то пойдёт не так, я смогу только заплакать))) Например для чего перед & стоит пробел ( "1 " &)? Как такое возможно? Это тайные знания тайного сообщества? Для чего в <=ДАТАЗНАЧ("1 " &    вставлена единица с пробелом и так далее.
Второй вариант выглядит интереснее.
Третий вариант отлично при одном уточнении: какой столбец должен быть правильно отсортирован? Если это только один столбец, то это сделать реально. Если несколько, то нет гарантии, что каждое следующее изменение тарифов Т1 и Т2 будет увеличено.
Изменено: olega-san1 - 12 Апр 2018 18:30:19
 
olega-san1, работают все варианты, просто первая формула внутри таблицы и запись
DATEVALUE("1 " &Членск[@[За период (Месяц)]]&" " &Членск[@[За период (Год)]]) короче
DATEVALUE("1 " &[@[За период (Месяц)]]&" " &[@[За период (Год)]])
собственно эта формула составляет текстовое  "1 Января 2018" которое преобразуется в формат даты. Можно по идее и вместо функции использовать
--("1 " &[@[За период (Месяц)]]&" " &[@[За период (Год)]])
Так же можно использовать вашу таблицу и сделать вот так
DATE([@[За период (Год)]];VLOOKUP([@[За период (Месяц)]];Месяцы;2;0);1)
или так
MATCH([@[За период (Месяц)]];Месяцы[Месяц];0)

В варианте 1 ищется наибольшее значение начала тарифного плана , которое меньше полученной даты и строка в которой это значение, хотя тут я поторопился надо было по другому немного а так как сейчас работает только при упорядоченных исходных. я заменил выше и файл и формулу.

Вариант 2 используется то, что искомая дата может лежать только между датами периода тарифного, разве что когда период не закрыт, то его подменяем на очень большое число . Сумма - просто дает сумму из одного искомого числа.

Вариант3  прием есть и он находит последнее значение которое удовлетворяет требованию, меньше искомой даты. таблица должна быть полностью отсортирована по началу периода. Вариант короткий, но получается уступает первым двум.
Изменено: БМВ - 12 Апр 2018 20:59:21
 
Цитата
БМВ написал:
Цитата
Вариант3  прием есть и он находит последнее значение которое удовлетворяет требованию, меньше искомой даты. таблица должна быть полностью отсортирована по началу периода. Вариант короткий, но получается уступает первым двум.
Не уступает - самое "оно"! Левая таблица всегда будет отсортирована по началу периода. Я это условие применю в файле подключения и все дела.
Пробовал ПОИСКПОЗ (MATCH) - длиннее получается, так что Ваш вариант  самый подходящий. Пробовал без пробелов "1 " & и &" " & тоже работает:
=ПРОСМОТР(2;1/(ТарифыЭлектро_1[ДатаНачала]<=ДАТАЗНАЧ("1"&Членск[@[За период (Месяц)]]&Членск[@[За период (Год)]]));ТарифыЭлектро_1[Тариф Т1])
Или это временная работа?
Ещё вопрос: Что значит 2 во фрагменте =ПРОСМОТР(2;1/( и как расшифровывается просматриваемый вектор 1/(ТарифыЭлектро_1[ДатаНачала]<=ДАТАЗНАЧ("1 " &[@[За период (Месяц)]]&" " &[@[За период (Год)]]))
Проверьте, пожалуйста, свою почту, прикреплённую к этому форуму.
Изменено: olega-san1 - 12 Апр 2018 22:20:02
 
=ПРОСМОТР(2;1/(логическое выражение)   -  
логическое выражение принимает значение True(1) /False(0). при делении получаем или 1 или ошибка. Lookup умеет отбрасывать ошибки, а при поиске числа заведомо больше 1, находит последнюю 1, ну и советующее ему значение . 2 , а не 1 все ставят только по тому, что не всегда 1/1=1, и если вдруг будет 1.0000000001 , то все сломается.

Почту проверил, спасибо, оценил, но эта ветка не предполагает оплаты, а формулы не тянут на заказ в ветке "Работа". Уверяю, даже тут решались более сложные задачи на безвозмездной основе.
 
БМВ, ...как-то я не сообразил, что здесь есть ветка "Работа", думаю у меня такой ступор не последний раз.
Ещё раз огромное спасибо. Примерно понял, завтра ещё почитаю Ваши ответы и запишу себе шпаргалку.
Страницы: 1
Читают тему (гостей: 1)