спасибо большое все супер, то что нужно..

приветствую. Есть таблица, см. пример.

Нужно написать формулу в VBA. в которой я задаю ячейку с условием (desc) и начальную ячейку для суммирования (quantity).Формула двигается вниз по таблице и суммирует ячейки в столбце quantity, у которых значение desc совпадает. Когда сумма по quantity будет равна 0, то выводится результат в виде кол-ва строк от текущей до последней ячейки.
Помогите пожалуйста. Спасибо. Формулами не получается так сделать..

Спасибо за помощь. Все получилось. Доработал код. Оставлю тут, вдруг кому пригодится...
В общем с помощью макроса в ексель добавляется новая формула, которая тянет данные с yahoo.finance по любым инструментам.

Формула:

=Фильтр_JSON(ссылка на JSON ; раздел который нужно вытащить ; конец раздела обычно обозначается запятой)
напр.:
=Фильтр_JSON("https://query2.finance.yahoo.com/v10/finance/quoteSummary/TSLA?modules=price";"regularMarketPrice:{raw:";",")

описание ссылок на JSON и разделов можно почитать тут: https://habr.com/ru/articles/505674/

код VBA:

Скрытый текст

Option Explicit Public crumb As String Public cookie As String Function Фильтр_JSON(ВЕБСЛУЖБА, ОТ$, ДО$) 'Функция возвращает значение, полученное в результате фильтрации JSON    Dim full_response 'переменная для хранения результата функции GetHTTPResponse, которая получает HTTP-ответ от веб-службы    Dim otvet 'переменная для выделения значения    full_response = Replace(GetHTTPResponse(ВЕБСЛУЖБА), Chr(34), "")    otvet = Split(Split(full_response, ОТ)(1), ДО)(0) 'Результат возвращаемый функцией 'Debug.Print otvet Фильтр_JSON = otvet End Function Function GetHTTPResponse(ByVal sURL As String) As String     Dim oXMLHTTP 'Проверяем наличие Crumb в кэше, если его нет, то запрашиваем 'Debug.Print "crumb= " & crumb    If crumb = "" Then        Call GetYahooCrumb    End If        'Добавляем crumb к адресу    sURL = sURL & "&crumb=" & crumb    On Error Resume Next    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")    With oXMLHTTP        .Open "GET", sURL, False        .setRequestHeader "Cache-Control", "max-age=0"        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2564.41 Safari/537.36 OPR/35.0.2066.10 (Edition beta)"        .setRequestHeader "Accept-Encoding", "deflate"        .setRequestHeader "Accept-Language", "ru-RU,ru;q=0.8,en-US;q=0.6,en;q=0.4"        .send         GetHTTPResponse = .responseText 'Проверка полученного текста JSON 'Debug.Print .responseText    End With    Set oXMLHTTP = Nothing End Function Static Function GetYahooCrumb() 'Проверка запуска получения крамба и куки 'Debug.Print "Запуск функции GetYahooCrumb"        ' Set User Agent    Dim user_agent_key As String    Dim user_agent_value As String    user_agent_key = "User-Agent"    user_agent_value = "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36"    ' Create HTTP Request    Dim req As Object    Set req = CreateObject("MSXML2.XMLHTTP")    req.Open "GET", "https://finance.yahoo.com/";, False    req.setRequestHeader user_agent_key, user_agent_value    req.send        ' Get Cookie    If req.Status = 200 Then        cookie = req.getResponseHeader("Set-Cookie")    Else        Exit Function    End If    ' Get Crumb    Set req = CreateObject("MSXML2.XMLHTTP")    req.Open "GET", "https://query1.finance.yahoo.com/v1/test/getcrumb";, False    req.setRequestHeader user_agent_key, user_agent_value    req.setRequestHeader "Cookie", cookie    req.send      ' Get Crumb Value    crumb = req.responseText 'Проверка полученного крамба 'Debug.Print "Crumb получен и = " & crumb End Function

Приветствую!
Раньше с yahoo.finance можно было забирать json и разбирать его в екселе с помощью запроса типа

https://query2.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=price

Но пару месяцев назад яху прикрыли эту лавочку. И теперь чтобы бесплатно получить данные нужно сначала получить ключ по запросу:

https://query2.finance.yahoo.com/v1/test/getcrumb

и затем присоединить этот ключ к запросу. И теперь запрос выглядит след.образом:

https://query2.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=price&crumb=ZEumtlLosgt

Ключ у каждого браузера свой. И вопрос: как получить этот ключ в екселе, чтобы подцеплять к запросам. Мыкался, но ничего не получается. Или ошибка, или пустота в ответ.... вот тут пишут, что нужно сделать: https://stackoverflow.com/a/76320367.... Помогите пжлста...

Вот так сейчас запрашиваю JSON и вытаскиваю из него нужный тег:

Скрытый текст

Option Explicit Function Фильтр_JSON(ВЕБСЛУЖБА, ОТ$, ДО$) 'Функция возвращает значение, полученное в результате фильтрации JSON    Dim ss 'переменная ss используется для хранения результата функции GetHTTPResponse, которая получает HTTP-ответ от веб-службы    Dim t 'переменная t используется для разделения строки ss на подстроки    ss = Replace(GetHTTPResponse(ВЕБСЛУЖБА), Chr(34), "")    t = Split(Split(ss, ОТ)(1), ДО)(0) Debug.Print t Фильтр_JSON = t End Function Private Function GetHTTPResponse(ByVal sURL As String) As String     Dim oXMLHTTP     On Error Resume Next     Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")     With oXMLHTTP        .Open "GET", sURL, False        .SetRequestHeader "Cache-Control", "max-age=0"        .SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2564.41 Safari/537.36 OPR/35.0.2066.10 (Edition beta)"        .SetRequestHeader "Accept-Encoding", "deflate"        .SetRequestHeader "Accept-Language", "ru-RU,ru;q=0.8,en-US;q=0.6,en;q=0.4"        .send         GetHTTPResponse = .responseText     End With     Set oXMLHTTP = Nothing End Function

Сам отвечу.... В документе остались старые запросы, которых нигде абсолютно нет. Удалось их победить так: открыл файл ексель архиватором, нашел там "xl\xmlMaps.xml" , в нем нашел ошибочные данные, которые вылезают в окне ошибок и удалил их... После чего все ошибки пропали....  

Да я тоже люблю троллить людей, которые, напр., путают Вольты и Ватты.... Ну применил не то определение, не мой профиль, но суть вопроса вроде понятно описал...

Андрей VG, думал над решением с помощью PQ но не подходит, тк много разных JSONов будет и они будут меняться, и нужно по паре параметров из каждого JSONа брать, и каждый через PQ заводить не оч удобно... Получится 100500 запросов.. Формулой удобнее, хотя предчувствую, что больше ресурсов потребуется на обработку формул, по сравнению с PQ...

Александр Моторин, вот спасибо, добрый человек! я сток промучался, а тут неск минут делов.... Для моей задачи пока хватит..Еще б разобраться с массивами, но это попозже напишу....

в итоге остановился на том, что итоговую таблицу из РР выгружаю на лист и с него уже забираю данные. В принципе все норм работает. Спасибо!

Подскажите, почему возникает такая ошибка при попытке обновления с помощью "Обновить все" или ctrl+alt+F5 (рисунок прикреплен)
Если захожу в PQ, и обновляю там, то все таблицы обновляются без ошибок (ссылка на файл)

Помогите решить задачу. Необходимо в VBA сделать пользовательскую функцию, чтобы с помощью формул вытаскивать из JSON нужные теги...
Напр.
Результат: PBF Energy Inc.

В параметрах функции задаем источник JSON и Xpath (тег) который нужно извлечь.

В инете самое близкое нашел вот это (https://medium.com/swlh/excel-vba-parse-json-easily-c2213f4d8e7a), но там не для вставки в формулы. Я подозреваю, что можно как-то этот код с работой в формулах связать, но мозгов не хватает...

спасибо за идеи!!! Все посмотрю, попробую и отпишусь)

да не сложно..прост тестирую..... но видимо не то.... в файле примере просто табличку с цифрами дал.. у меня так же формулы  там присутствуют... А PQ формулы все убивает.... Я что-то на этом не акцентировал внимание.... (
Скачал так же DAXстудио... но там не смог разобраться сразу... Видимо легких путей тут нет...
Думал, что логично написать запрос к модели данных и  получить значение, согласно условию запроса, но не тут-то было..  

пример

вот пример.
То что выделено цветом я сейчас через ВПР беру из выгруженной на отдельный лист, таблицы.
Можно эти значения напрямую из PQ или PP тянуть?

ну таблиц много на самом деле и в них постоянно что-то удаляю/добавляю.... прост упростил пример, для вопроса...

Эммм... В PQ выгружаются все инструменты Мосбиржи (пара тысяч строк), а моя табличка содержит 10 позиций. И нужно по этим 10 позициям данные подтянуть...  

Есть табличка в екселе, где я руками заполняю 1 и 2 стобец. И в эту табличку (в 3 столбец) нужно подтянуть значения из модели данных power pivot или из power query.

Приветствую!

Получаю данные из интернета в power query, обрабатываю их и направляю в модель данных. В Excel заполняю вручную таблицу сделок. Можно ли в нее вытащить некоторые данные из модели данных или из таблицы power query?

Пример:
Подтягиваю котировки с мосбиржи в модель данных. в екселе веду портфель, и нужно в ручную табличку напротив записей дописывать данные, полученные с мосбиржи.

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

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

В итоге мне нужно диаграмму построить по последнему столбцу "общий объем", а как это сделать не форматируя и не перестраивая саму таблицу я не понимаю....

по правому щелчку выпадает меню, в результате форматируется и таблица сводная... но ее формат мне не нужен... и тут как бэ логично очень, что бы эти кнопки отвечали за выбор данных для диаграммы. Они ведь так и называются "кнопки поля значения"
это поле влечет за собой переформатирование и самой сводной таблицы, а таблица нужна в неизменном виде. Т.е. диаграмма у меня всегда строится по первому столбцу сводной таблицы. Если первым столбцом сделать другой, то по нему и будет диаграмма. Как то это криво реализовано, если так и задумано....  

Dyroff, в первом посте написано "ПРИМЕР". По нему щелкаете и скачиваете.

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

У вас в этом файле кнопки активны? может что-то с самим екселем у меня?

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

Файл с примером выложил в облако. Сюда 300 кБ не крепится.

пример