[решено] Подскажите по формуле в EXCEL

[another_user]

Ребят, подскажите как написать формулу или отправьте куда нужно, где есть эксперты по эксель.

Для общего понимания: идет расчет начальной (максимальной) цены контракта с использованием этого их реестра контрактов. Есть две значимые позиции, текущая дата и дата заключения контракта. Нужно, чтобы считался коэффициент для пересчета цен прошлых периодов к текущему уровню цен. Одна ячейка - цена из контракта, следующая за ней ячейка должна содержать формулу, считающую коэффициент исходя из нижеследующего. Если между текущей датой и датой заключения контракта больше 180 дней, то нужно... (и вот тут самое сложное для меня)... нужно чтобы формула определила месяц текущей даты и месяц даты заключения контракта (допустим текущая декабрь и дата заключения контракта - июнь), потом сходила в другой в лист в такую табличку (http://www.gks.ru/bgd/free/b00_24/IssWWW.exe/Stg/d000/I000030R.HTM), там нашла эти месяцы и сделала (100 + (100,7 октябрь - 100) + (100,6 сентябрь - 100) + (100,4 - 100) + (100,8 - 100) + (100,2 - 100))/100= 1,027 и далее этот коэффициент умножить на цену контракта из первой ячейки.

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

[okshef]

another_user, попробуйте изобразить ваш вопрос в файле, как вы его понимаете...
Таблица, на которую вы дали ссылку, легко копируется в Excel.

[another_user]

легко копируется в Excel. »

Ну, попробуйте, скопируйте)

131730

Вот как бы файлик. Сначала идет цена из источника, а ровно в следующей ячейке я хочу, чтобы считался коэффициент и умножался в полностью автоматическом режиме в зависимости от текущей даты и даты заключения контракта. Я склоняюсь к тому, что либо надо писать ну очень длинные формулы, либо это невозможно в принципе. Ваши мысли?

[okshef]

Ну, попробуйте, скопируйте) »
вы же скопировали :) Есть способ проще: Импорт данных в Excel 2010 из интернета (http://office-guru.ru/excel/import-dannyh-v-excel-2010-iz-interneta-85.html)
Очень плохо, что в вашем файле объединение ячеек - это существенно усложняет вычисления. Теперь по логике расчетов.
В ячейке H19 вижу дату заключения контракта, в ячейке K19 - цена за единицу. Как вы видите связь данных первого листа с данными со второго, что должно быть?

[another_user]

объединение ячеек - это существенно усложняет вычисления. »

Почему? Просто за адресом нужно внимательней последить и все, по моему.

Теперь по логике расчетов. »

Вот смотрите, значит...

Ячейка D5 содержит указание на текущую дату. Смотрим источник ценовой информации № 3, где мы с вами видим, что дата заключения контракта 26 января 2015. К37 содержит цену товара, которая указана в контракте. В ячейке N37 в зависимости от текущей даты и даты заключения контракта должен полностью автоматически делаться расчет коэффициента для пересчета цен прошлых периодов к текущему уровню цен по логике, описанной в первом посте. Обратите, кстати, внимание, N37 содержит формулу и считает как мне нужно. Но проблема в том, что эта формула применима только если сейчас декабрь, а контракт заключен в январе. Эту формулу я писал специально под эти месяцы, а я хочу, чтобы формула сама определяла какой месяц сегодня и каким месяцем заключен контракт, а потом шла на лист GKS и автоматически выбирала оттуда данные по формуле (100+(х-100))/100, где х это каждый месяц в периоде между месяцем заключения контракта и текущим месяцем. Я надеюсь вы понимаете. ))

Я все-таки думаю, что Excel способен на это. Разве что мой мозг пока не способен.

[okshef]

Если между текущей датой и датой заключения контракта больше 180 дней »
а если меньше?
Я так понял, что цена не меняется.

Так?

P.S. Думаю, у вас возникнут вопросы. Буду рад ответить.

[another_user]

а если меньше?
Я так понял, что цена не меняется. »

Так.

Думаю, у вас возникнут вопросы. »

Еще бы! Как вы это сделали? Единственное что надо коэффициент округлять, а то если брать апрель, то он считается 1,036, а должен быть 1,04.

[okshef]

а то если брать апрель »
поясните. Апреля в задаче нет. Думаю, с округлением (https://support.office.com/ru-ru/article/Округление-числа-ba199237-27f0-4bc1-8185-5c2cdb4b45f6) вы и сами справитесь.


Я убрал объединение ячеек, уж простите...
На листе 2 та же информация, что и на листе GKS, только убраны квартальные промежуточные расчеты. Введены именованные диапазоны (http://www.excel-vba.ru/chto-umeet-excel/imenovannye-diapazony/) (так красивее формула получается :)): в столбце В диапазону Лист2!$B$6:$B$27 присвоено имя "Месяцы", в столбце С диапазону Лист2!$C$6:$C$27 - "Коэффициенты"
В столбце В Лист2 названия месяцев заменены числовыми значениями 1.01.2014, 1.02.2014 и т.д. и применено форматирование "[$-ru-RU]ММММ ГГГГ;@"

Теперь к формуле. Пояснения для ячейки Е13
=ЕСЛИ(РАЗНДАТ(C13;$B$1;"d")>179;D13*(100+СУММПРОИЗВ((Коэффициенты-100)*(Месяцы>ДАТАМЕС(C13;-1))))/100;D13)
РАЗНДАТ(C13;$B$1;"d") - недокументированная функция, но справка по ней есть (https://support.office.com/ru-ru/article/Функция-РАЗНДАТ-bd549d1c-f829-4691-a77d-4a1e3d42bc1a). Одно замечание: вместо "Д", "М", "Г", и т.д. нужно использовать "d', "m", "y", и т.д.
Считает количество дней от текущей даты до даты в ячейке С13 в днях.
Поскольку один из дней входит в диапазон дат, пришлось в условии использовать не 180, а 179
Теперь непосредственно о вычислении
ваше желание: (100+(х-100))/100, где х это каждый месяц в периоде между...
исполнено в виде
(100+СУММПРОИЗВ((Коэффициенты-100)*(Месяцы>ДАТАМЕС(C13;-1))))/100
(Коэффициенты-100) - вычитает 100 от каждого значение в диапазоне "Коэффициенты", это понятно. Результат: диапазон уменьшенных на 100 коэффициентов инфляции.
(Месяцы>ДАТАМЕС(C13;-1)) - отсекает месяцы, меньшие даты заключения контракта. Т.к. в диапазоне "Месяцы" месяц начинается с 1-го числа, нужно значение месяца в ячейке С13 уменьшить на 1 месяц, для этого нужна функция "ДАТАМЕС (https://support.office.com/ru-ru/article/ДАТАМЕС-функция-ДАТАМЕС-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5)". Результат: диапазон логических 0 и 1. 0 - месяц меньше месяца закл. контракта, 1 - больше или равен.
Дальше функция СУММПРОИЗВ суммирует произведения двух диапазонов: уменьшенных коэффициентов и 0 и 1 согласно выбранным месяцам.
Остальное - чистая арифметика.
Надеюсь, понятно объяснил :)

[another_user]

Надеюсь, понятно объяснил »

Большое человеческое спасибо!