Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Weter
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
Power Query. Суммирование значений в периоде дата и время по критерию
 
#
15.08.2019 12:03:10
Огромное спасибо, все как всегда проще чем кажется, а я голову ломал со списками...
 
Перейти    
Power Query. Суммирование значений в периоде дата и время по критерию
 
#
15.08.2019 10:02:46

Здравствуйте.

Пытаюсь решить в Power Query задачу, но что-то ничего путевого не приходит на ум.

Может кто-нибудь подскажет с решением?

Есть две таблицы, в первой: дата создания, код, дата и время начала и окончания периода, во второй: дата, время, код и сумма.

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

Ранее делал подобную задачу, но только с датами, там все было просто, вычитал продолжительность периода строил список дат и джойнил со второй таблицей по критерию код и дата создания.

А вот как сделать тоже самое с временем никак не пойму.

Пример во вложении
 
Перейти    
Нахождение оптимального маршрута при помощи Google API
 
#
16.08.2017 14:32:00
Решил через костыли )) Применил функцию сцепить для всех промежуточных точек и уже на эту ячейку ссылаюсь из макроса.
Обновил файл в первом сообщение, может кому будет интересно.
Кстати если промежуточных точек более 8-ми, то вам понадобится apikey от гугл. Он бесплатен, так что кому нужно тот найдет.
Код теперь такой:
Код
Public Function Маршрут(start As String, dest As String, wpoint As String, key As String)
    Dim firstVal As String, secondVal As String, wpointVal As String, lastVal As String
    firstVal = "https://maps.googleapis.com/maps/api/directions/json?origin="
    secondVal = "&destination="
    wpointVal = "&waypoints=optimize:true|"
    lastVal = "&avoid=ferries&mode=car&language=ru"
    keyVal = "&"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & wpointVal & Replace(wpoint, " ", "+") & lastVal & keyVal & Replace(key, " ", "+")
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """overview_polyline"" : {") = 0 Then GoTo ErrorHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """waypoint_order"".*?([\d\,\s]{2,150})": regex.Global = False
    Set matches = regex.Execute(objHTTP.responseText)
    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    Маршрут = tmpVal
    Exit Function
ErrorHandl:
    Маршрут = -1
End Function
 
Перейти    
Нахождение оптимального маршрута при помощи Google API
 
#
09.08.2017 15:44:51
Возможно и помогла бы, если бы хоть чуть-чуть разбирался в программировании и в частности в VBA, а так смотрю и ничего не понимаю.
Был бы благодарен, если бы на моём примере, мне кто-нибудь помог бы составить url.
Ну и не знаю второй ли это вопрос или нет, так как тема та же, статья эта мне точно не поможет составить правильный regex.Pattern =, чтобы из waypoint_order возвращалось значение 3, 1, 2, 4, 0  
 
Перейти    
Нахождение оптимального маршрута при помощи Google API
 
#
09.08.2017 08:21:46
Цитата
Андрей VG написал:
А почему бы ещё с  самим API  не познакомиться?Цитата Weter  написал:
у меня получилось добавить только одну промежуточную точку. Да, вроде всё предельно прозрачно  Промежуточные точки
Добрый день. С API я познакомился и как задаются промежуточные точки понял.
У меня вопрос немного в другом - как создать в VBA, url адрес с промежуточными точками.
У меня сейчас вот что получается:
Ячейка C4 с начальным пунктом в http://maps.googleapis.com/maps/api/directions/json?origin=
+ ячейка C5  с конечным пунктом в  &destination=
+ включаем в апи оптимизацию маршрута и добавляем ячейку C6 с промежуточным пунктом в &waypoints=optimize:true|
и в конце адреса добавляем ограничения и т.д. &avoid=ferries&mode=car&language=ru
Таким образом запрос выглядит следующим образом
http://maps.googleapis.com/maps/api/directions/json?origin=Москва&destination=Москва&waypoints=optimize:true|Тула&avoid=ferries&mode=car&language=ru
Моя же проблема заключается в следующем, как кроме Тулы C6 (как в моём примере), средствами VBA добавить в запрос еще промежуточные точки C7, C8, C9 с промежуточным знаком "|" между ними?
Естественно, если в самом коде написать запрос, то все работает
http://maps.googleapis.com/maps/api/directions/json?origin=Москва&destination=Москва&amp... Посад|Смоленск&avoid=ferries&mode=car&language=ru
вот этот запрос даёт ответ "waypoint_order" : [ 3, 1, 2, 4, 0 ].

Здесь же я столкнулся с еще одной проблемой по этой теме.
Как составить шаблон для regex.Pattern = """waypoint_order"".*?([0-9]+)" Пока как не кручу он возвращает только одно значение waypoint_order, т.е. либо 3, либо 1.
 
Перейти    
Нахождение оптимального маршрута при помощи Google API
 
#
08.08.2017 10:46:37

Здравствуйте.

Решаю для себя вот такую задачу.

Сервис гугл мапс, при помощи api directions, позволяет найти оптимальный маршрут до 23 промежуточных точек.

В сети, и на этом форуме я нашел подобное решение для api  distancematrix, которое находит дистанцию между двумя населенными пунктами. Доработал эту функцию под свою задачу, но так как в VBA вообще не бум-бум, то не понимаю, как сделать, чтобы в запрос можно было добавлять промежуточные точки. На данном этапе путем добавления еще одной переменной у меня получилось добавить только одну промежуточную точку.

А как сделать так, чтобы их, промежуточных точек,  можно было добавлять множество?

Ну и сам код:

Код
Public Function Маршрут(start As String, dest As String, wpoint As String)
    Dim firstVal As String, secondVal As String, wpointVal As String, lastVal As String
    firstVal = "http://maps.googleapis.com/maps/api/directions/json?origin="
    secondVal = "&destination="
    wpointVal = "&waypoints=optimize:true|"
    lastVal = "&avoid=ferries&mode=car&language=ru"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & wpointVal & Replace(wpoint, " ", "+") & lastVal
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """overview_polyline"" : {") = 0 Then GoTo ErrorHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """waypoint_order"".*?([0-9]+)": regex.Global = False
    Set matches = regex.Execute(objHTTP.responseText)
    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    Маршрут = CDbl(tmpVal)
    Exit Function
ErrorHandl:
    Маршрут = -1
End Function  
Изменено: Weter - 16.08.2017 14:27:33 (добавил решение)
 
Перейти    
Как из одной таблицы собрать другую
 
#
16.06.2017 14:18:58
Arturus и Андрей VG Большое спасибо, одно из двух мне точно подойдёт. Кстати про PQ я и позабыл совсем ))
 
Перейти    
Как из одной таблицы собрать другую
 
#
16.06.2017 12:35:15
Спасибо за отзыв. Сортировка это только пожелание. Подскажите как сделать без сортировки?
 
Перейти    
Как из одной таблицы собрать другую
 
#
16.06.2017 11:44:47
Здравствуйте. Помогите! Ни как не могу разобраться.
Для примера, есть некие персонажи, которые посещают некое заведение, название персонажа, а также  день недели и время посещения заносятся таблицу. Персонаж может посещать заведение несколько раз, но обычно не более трех.
Таблица заполняется следующим образом |Название|День недели|Время|
Мне же на выходе нужно получить следующую таблицу
|Название|День недели 1-го посещения|Время 1-го посещения|День недели 2-го посещения|Время 2-го посещения| и т.д.
Думаю что без макроса здесь не обойтись, но  в них я никак не силен.
Из примера я думаю будет понятнее, что я хотел бы получить.
 
Перейти    
Страницы: 1
Наверх