Страницы: 1
RSS
Подстановка загруженного курса по условию даты, Чтобы подставлялся курс ЕСЛИ в такой-то ячейке =СЕГОДНЯ() или СУББОТА. Курс загружается из ЦБ по Power Query.
 
Всем доброго дня! 👋

Сообщение в уже существующей Теме наверное малозаметно или его не видно 😊Полагаю, лучше всегда создавать новую свою Тему, и не писать в какой-то очень "древней" Теме. 😊

Прошу помощи или совета.🙏

На вкладке "Лист1" есть ячейка, которая показывает сегодняшнюю дату по оператору "=СЕГОДНЯ()" в ячейке D14.

Ниже в D15-16 по формуле вычисляется сумма в РУБ (из usd) через формулу перемножения суммы в usd (С15) на курс (K15).

На отдельной вкладке "Курс" по ссылке с сайта ЦБ загружается таблица с курсами на сегодня и завтра, или вчера и сегодня, если это вс/пн/вт. Скриншот Power Query.

Требуется чтобы при открытии файла, курс в ячейке K15 подставлялся по условию даты: ЕСЛИ дата из ячейки B1 вкладки "Курс" равна D14 → то подставлять курс от соответствующей даты (B2), ИНАЧЕ → подставлять курс, который принадлежит дате рядом (С2).

Также чтобы в ячейку L15 выводилась дата того курса, который был выведен/использован/применен в K15.

Заранее благодарю!🙏

 
так можете получить курс доллара зависящий от даты без Power Query - далее подстройте под вашу задачу
Код
=ФИЛЬТР.XML(
ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1="&
ТЕКСТ(D14;"ДД.ММ.ГГГГ")&
"&date_req2="&
ТЕКСТ(D14;"ДД.ММ.ГГГГ")&
"&VAL_NM_RQ=R01235");
"//Value")
так можете на период (неделя) назад получить массив курсов:
=ФИЛЬТР.XML(
ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1="&
ТЕКСТ(D14-7;"ДД.ММ.ГГГГ")&
"&date_req2="&
ТЕКСТ(D14;"ДД.ММ.ГГГГ")&
"&VAL_NM_RQ=R01235");
"//Value")
Изменено: Тимофеев - 26.01.2023 19:45:55
 
Тимофеев, большое спасибо за отклик и помощь! 🙏

Правильно ли я понимаю, что когда файл будет открываться в воскресенье, понедельник и вторник - то этот массив (который первый) будет показывать крайний опубликованный курс - т.е. с субботы?
 
Не знаю специфику сайта - что он показывает на странице в эти дни
Можно использовать формулы для подстройки дальнейшей
Например
=ДЕНЬНЕД(СЕГОДНЯ();2) выдаст 4, т.е. Четверг. В неделе 7 дней и тут уже с помощью если или выбор настраивайте условие какая дата должна попасть в формулу.
Т.е. Вам остается настроить логику в D14 какая дата должна отобразиться в зависимости от дня недели
А вообще выходные не за горами - посмотрите что будет при открытии )
Если хотите видеть рядом с массивом на 7 дней даты курсов. то:
Код
=ФИЛЬТР.XML(
ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1="&
ТЕКСТ(D14-7;"ДД.ММ.ГГГГ")&"&date_req2="&
ТЕКСТ(D14;"ДД.ММ.ГГГГ")&
"&VAL_NM_RQ=R01235");
"//Record//@Date")
Изменено: Тимофеев - 26.01.2023 20:05:26
 
Тимофеев, да, подожду понедельника/вторника, посмотрю что будет получаться. 👍

А логика из D14 - она должна быть всегда одной и той же 👉 =СЕГОДНЯ()
Т.е. когда файл открывают - дата того дня и прописывается. Но как известно, курс с субботы применяется и на вс и пн (со вторником - это я переборщил вроде, в пн должен появиться новый курс на вт), соответственно, при открытии файла в вс/пн дата то будет сегодняшняя, а вот курс должен быть, получается, субботним.
 
Ну напишете если что думаю показывать будет что надо
пропишите ф формуле не минус 7 а минус 20 и посмотрите что даёт в предыдущие субботы
Изменено: Тимофеев - 26.01.2023 20:13:12
 
Хорошо, посмотрю как будет получаться! 👍🙏
Обязательно отпишусь!
 
Для курса можно вот так написать и все нормально тогда
Код
=СУММПРОИЗВ(курс!B2:C2*(--курс!B1:C1=D14))
Скажи мне, кудесник, любимец ба’гов...
 
Поглядел чуть внимательнее:
Не дает данные за Вс и Пн, а также за официальные нерабочие дни (например с 1 по 9 января)
Следовательно удлиним формулу с небольшим запасом, учитывая что нерабочих 31 дней подряд не будет
В I2 дописал формулу чуть длиннее, но на любом системном разделителе отработает
Код
=ПРОСМОТР(СЕГОДНЯ();
ФИЛЬТР.XML(ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(СЕГОДНЯ()-31;"ДД.ММ.ГГГГ")&"&date_req2="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")&"&VAL_NM_RQ=R01235");
"//Record//@Date");
ФИЛЬТР.XML(ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(СЕГОДНЯ()-31;"ДД.ММ.ГГГГ")&"&date_req2="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")&"&VAL_NM_RQ=R01235");
"//Value"))
Изменено: Тимофеев - 27.01.2023 16:43:13
 
_Boroda_, Приветствую!

Благодарю за отклик! 🙏

Сообщаю обратную связь.

P.S.: Я таким похожим образом тоже пробовал, где просто указывал, что если B1 = D14 → выводит курс B2, иначе С2
но у меня это также превращалось в похожую ошибку "#ССЫЛКА!"

 
Тимофеев, приветствую!

Так, открыл сегодня (вс, 29.01) - всё вроде как good! 😊👍Немного правда смущает крутость и объемность формул... 😊Это наверное с непривычки, т.к. никогда такие "полотнища" формул не использовал...

Есть уточнения:
  1. Так много дат и курсов не требуется в моём файле. Даже если перенести это на отдельную вкладку - со временем эти данные будут накапливаться (если я правильно понял), а хотелось бы чтоб всё было завязано только вокруг актуальной даты.
    Какую формулу мне нужно скопировать свой файл (в L15), чтобы отображалась либо сегодняшняя дата, либо дата субботы (если файл открывается в вс/пн)?

  2. Правильно ли я понимаю, что для ячейки своего файла, где требуется отображение актуального курса (К15), мне нужно скопировать формулу или из H2 или из I2?

  3. В чём отличия между формулами H2 и I2?
Заранее благодарю! 🙏
Изменено: Aleksei - 29.01.2023 10:55:55
 
п.3: в H2  формула для системного разделителя "," - если открыть этот файл на компьютере где установлен системный разделитель "." расчеты не произведутся, потому что возвратится текст. В I2 - формула которая определит системный разделитель и подставит "." или ","
п.2 Лучше брать формулу из I2
п.1 Даты не будут накапливаться - они будут пересчитываться всегда на период 31 день назад от =сегодня(). Для даты формула по тому же принципу с Просмотром - вставил в ваш первый файл
Код
=ПРОСМОТР(СЕГОДНЯ();
ФИЛЬТР.XML(
ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(СЕГОДНЯ()-31;"ДД.ММ.ГГГГ")&"
&date_req2="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")&"&VAL_NM_RQ=R01235");"//Record//@Date");
ФИЛЬТР.XML(ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(СЕГОДНЯ()-31;"ДД.ММ.ГГГГ")&"
&date_req2="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")&"&VAL_NM_RQ=R01235");"//Record//@Date"))
Изменено: Тимофеев - 30.01.2023 08:57:40
 
Тимофеев, приветствую!

Проверил. Выглядит всё как и требовалось. 😉👍
Интересно как будет в вс и пн 😊

Есть какое-то лайфхаки, чтобы:
  • не вылетало вот такое сообщение, вылетающее при открытии файла:
    ПРЕДУПРЕЖДЕНИЕ СИСТЕМЫ БЕЗОПАСНОСТИ Функции WEBSERVICE отключены. [Включить содержимое]
  • а также чтобы конкретный файл всегда открывался с теми настройками границ, которые для Принтера "Microsoft Print to PDF" ? Скриншот ниже.
 
Включение и отключение оповещений системы безопасности и политики Советы панели сообщений для Office - Служба поддержки Майкрософт (microsoft.com)
Как отключить Извещение системы безопасности Microsoft Excel (planetaexcel.ru)
Включите страничный режим и рамкой синей сделайте область печати какую нужно
Изменено: Тимофеев - 01.02.2023 11:24:20
 
Цитата
Тимофеевнаписал:
Включите страничный режим и рамкой синей сделайте область печати какую нужно
Имел в виду чтобы такая настройка сохранилась вне зависимости на каком ПК открывают файл. Типа зафиксировать это. 😊 По моим наблюдениям это всё "слетает", когда файл открывают на другом ПК.
Да даже когда я его открываю на своем ПК - видимо по умолчанию подставляется первый принтер из списка, и соответственно применяются настройки полей этого принтера.
 
Если открыть файл WinRar, то папочка с настройкой принтера есть в нем
и принтер написан
Т.е. настройки печати в файле сидят
 
Коллеги, а к чему такой геммор?
Почему не транспонировать таблицу курсов запросе (в столбцах валюты, в строках даты) и отсортировать по датам?
И тогда простейшая формула =ВПР(D14;rate;2;1)

Или чуть сложнее (вдруг, валют больше будет) =ВПР(D14;rate;ПОИСКПОЗ("Доллар США";rate[#Заголовки];0);1) и с очисткой текста в запросе.
Изменено: surkenny - 01.02.2023 15:07:24
 
Просто предложил формульный вариант - мало примеров с вебслужбой
 
surkenny, приветствую!

Большое спасибо за отклик! 🙏

Позвольте уточнить:
  • правильно ли я понимаю, что если открыть документ в воскресенье или понедельник, то в К15 будет курс субботы?
  • подскажите пожалуйста, как сделать, чтобы в L15 выводилась дата того курса, который был выведен/использован/применен в K15,
    или по условию: если дата из [=курс!A2] равна дате D14 - то выводить её, иначе - дату субботы?
 
surkenny, приветствую!

Сможете уточнить эти два момента?

Заранее благодарен! 🙏
 
Aleksei, приветствую!
Вы же видите формулу:
Код
=ВПР(D14;rate;ПОИСКПОЗ("Доллар США";rate[#Заголовки];0);1)

То есть результат зависит от даты в ячейке D14. Если на эту дату нет курса (выходной/праздничный день), то выведется курс на ближайшую дату с имеющимся курсом до. Корректно будет работать, так как результат запроса PQ 100% будет отсортирован по датам.
Цитата
Aleksei написал:
правильно ли я понимаю, что если открыть документ в воскресенье или понедельник, то в К15 будет курс субботы?
Ну да.
Цитата
Aleksei написал:
как сделать, чтобы в L15 выводилась дата того курса, который был выведен/использован/применен в K15
Ну так по аналогии с курсом в K15 почему не сделать?
Код
=ВПР(D14;rate;ПОИСКПОЗ("Дата";rate[#Заголовки];0);1)
 
Друзья, доброго всем дня!
Подскажите пожалуйста, как быть
Перестал работать код из формулы в 2024 году

https://docs.google.com/spreadsheets/d/1uY_eMm5H9hlVHH7sVBBq5-ocfS_8ebNq6Awh6aQWnro/edit?usp=sharing

Может что-то ЦБ поменял у себя?
Страницы: 1
Наверх