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

Страницы: 1
Power Pivot мера расходы/сумму по дате
 
#
14.11.2024 23:20:40
Здравствуйте. Совсем нулевые знания DAX. Кое как через GPT пробовал скормить задачу и выдаёт решение такое, но оно работает с ошибкой в строках, ничего не вычисляет.
Почему Мера выдаёт верно в Итогах, но ничего не считает там где хотелось бы?

Пробовал гуглить ещё использовать CALCULATE(SUM(таблица);Календарь) эффект ровно тот же, что есть и нет Итог считает, но не данные в сводной

В желтых строках должен быть ДРР расход/оборот вычисляемый и снизу Итог должен быть таким какой сейчас

:oops:  файлик не влазил по весу https://disk.yandex.ru/i/xqcDW7uTzb4Pqw

Доп. вопрос. Нормально что при загрузке из pq таких маленьких данных он на 1-2 секунду задумывается? Когда нажимаю обновить сводную или все запросы.
Есть ли рекомендация что можно оптимизировать? Полагаю Тип лучше в PQ прикрутить столбец, а не в самом PP?




И меры пробовал в значения выносить - эффект тот же
Изменено: jiumohoo - 15.11.2024 00:19:40
 
Перейти    
Динамическая формула повторять предыдущую ячейку
 
#
06.09.2024 11:25:26
Здравствуйте.
Требуется формула для поиска ячейки с датой и повторением её в динамическом массиве пока не встретится следующая дата и так далее.


Строка2 - это пример другой формулы, где указав массив она на него и растягивается и неважно что внутри я с колонками делаю. Точно также хочу и дату повторять

Изменено: jiumohoo - 06.09.2024 11:27:45
 
Перейти    
Перебор %скидки до нужного значения, ПРОСМОТР()
 
#
15.03.2024 11:25:25
Здравствуйте.
Обращался ранее по текущему вопросу и рад ответам.

Понадобилось увеличить количество переменных зависящих от цены.
Добавив новые переменные в новой ситуации по той же логике по формуле от MCH на выходе не получался нужный результат.
Почему-то требуется дополнительно к расчёту угадывать число с приплюсовыванием для получения правильной скидки. Методом тыка понял, что надо -3 дописать.
Вопрос: почему что-то надо дописывать?
БМВ в своём ответе дописывал +8 к формуле, как пример.



---------------------------
Касаемо обращения.

Почему в моей расчётной формуле основанной на примере от МСН выходная скидка по большинству случаю не совсем близка к условию?


Для примера поправил 3 строки вручную и получаю нужный мне % из условия. Но вот формула косячит.




Просьба помочь подправить. Конечно можно ещё обернуть в LET() для простоты чтения, но сам факт её неверного расчёта не понимаю как исправить.
Код
=ПРОСМОТР($V$6;
((U8-U8*(90-СТРОКА($10:$899))%)-T8-
(((U8-U8*(90-СТРОКА($10:$899))%)*$J$6)+
((U8-U8*(90-СТРОКА($10:$899))%)*I8%)+
L8+
(ЕСЛИ(ИЛИ(E8>=25;H8>=200);500;ЕСЛИ((U8-U8*(90-СТРОКА($10:$899))%)*5,5%<13;13;МИН((U8-U8*(90-СТРОКА($10:$899))%)*5,5%;300))))+
((U8-U8*(90-СТРОКА($10:$899))%)*$Q$6)+
((U8-U8*(90-СТРОКА($10:$899))%)*R8)))
/(U8-U8*(90-СТРОКА($10:$899))%);(90-1-СТРОКА($10:$899))%*100)-3


Колонка V с этой формулой считает нужную скидку по условию % маржи из заголовка.
Новый перебор.xlsx (113.2 КБ)
Изменено: jiumohoo - 15.03.2024 11:47:55
 
Перейти    
Повернуть текст в ячейке не меняя высоту строки
 
#
18.10.2023 19:51:12
Здравствуйте. Не удаётся найти решение казалось бы на банальный вопрос.
То ли майкрософт не продумал вариант и проблема с первого дня, как появился продукт.


В горизонтальном положении если текст длинней размера ячейки - вылазит за неё.
В вертикальном положении - увеличивается высота строки из-за смены формата ячеек.
Ну не может же так быть непродуманно.

Без макросов штатным методом такое возможно? Попробовал формулу, а она массивом выдаёт наоборот результат продолжая его вниз под изначальный текст. Должен уходить вверх. Если есть вариант, то мою формулу в листе реверсивно перевернуть попробовать...
Изменено: jiumohoo - 18.10.2023 19:51:30
 
Перейти    
Сводная таблица фильтр по подписи не формат даты, а текстовый промежуток дат
 
#
18.07.2023 10:28:06
Здравствуйте.
Не хочется добавлять в источник данных дополнительный столбец для такого вопроса с вычленением даты. И в модели данных аналогично.
Строки подписаны таким образом
03.07.2023-09.07.2023
10.07.2023-16.07.2023
К примеру надо отфильтровать всё в большую сторону от ??.05.2023 (от первой даты в периоде)
Пытался с символами играться в поле используя */? - результат не получаю нужный. Там кажется ещё формулы кушаются, но также не получилось

Задумка просто обновлять сводную таблицу и чтобы новые данные добавлялись от заданной границы. А когда захочется подправить новый период - зайти в фильтр и поменять границу

Есть ли решение?
Файл весит больше 300 кб... Даже до 1000 строк урезав - не смог приложить пример
https://disk.yandex.ru/d/qfqa8a47vNlwiw
Изменено: jiumohoo - 18.07.2023 10:32:22
 
Перейти    
Сослаться на ячейку в которой логический оператор и число, Сослаться на ячейку в которой логистический оператор и число Формула
 
#
30.05.2023 13:55:39
Здравствуйте.
Задача закомментировать условия, которые будут использоваться в расчётах.

Править формулу если что-то изменится в <>= это трудоёмко.

Простой пример
=100 >= 200
=A1>=A2
И вот как бы оператор прикрутить в одну формулу)))
=A1&operator&A2 - ошибка :(
Вроде как формула "И(" превращает в логическое выражение, но тоже не получается. ДВССЫЛ аналогично, +0 тоже

Наверно есть простейшее решение, но не вижу. Громоздкие через Суммпроизв или Выбор - не то.  
Изменено: jiumohoo - 31.05.2023 09:50:18
 
Перейти    
PQ. Их накопительных данных выводить данные из последнего файла, если их нет брать из последнего
 
#
23.05.2023 11:33:14
Здравствуйте.
Периодично происходит парсинг данных с сайта, но проблема апи запроса в том, что при пагинации всё равно имеет свойство дублировать товар на любой из другой страниц. Это побороть не получается и не сильно горит, потому что вполне устраивает накопление данных, ибо хоть когда-то все товары соберутся. Задача получать из накапливаемых данных информацию из последнего, ибо она свежая и тянуть все отсутствующие артикула из других файлов, а также брать данные из как раз-таки по последней дате файла, как можно свежей.

csv имеет такие колонки
АртикулSKUРейтингКол-во отзывовОценок покупателя
Пример на артикуле: CH-AN-030-000004(тут цвет неважен в примере, ибо цифры в идентичны между ними)
xlg_tmp_2023_04_07T13_14_25.csv (рейтинг 0, кол-во отзывов 1, оценок 0)
xlg_tmp_2023_04_27T13_14_25.csv (рейтинг 3,71, кол-во отзывов 14, оценок 17)
xlg_tmp_2023_05_23T10_35_43.csv (рейтинг 4,13, кол-во отзывов 39, оценок 45)

На выходе нужно получить
CH-AN-030-000004  (рейтинг 4,13, кол-во отзывов 39, оценок 45)

А вот другая ситуация с ухудшением по причине того, где не понял, как бороться.
Снова пример неважно какой артикул такие там есть.
xlg_tmp_2023_04_07T13_14_25.csv (рейтинг 5, кол-во отзывов 5, оценок 5)
xlg_tmp_2023_04_27T13_14_25.csv (рейтинг 1, кол-во отзывов 22, оценок 22)
xlg_tmp_2023_05_23T10_35_43.csv (рейтинг 1, кол-во отзывов 39, оценок 45)
То бишь, Table.Group макс по дате и взять то и сё поле на выходе мне даёт совсем не те значения.
На выходе нужно получить: (рейтинг 1, кол-во отзывов 39, оценок 45)

Артикул собранный из всех файлов / дата наименования файла откуда взято, ибо свежей данных не встретилось / сами данные



Нужно аккумулировать артикула из всех файлов и к ним взять данные как можно свежей по дате наименования файлов.
Я тестово пробовал в приложенном файле через Table.Group и прочие комбинации, но на выходе неверно, скил слабый. Намесил грязи.
Изменено: jiumohoo - 23.05.2023 11:51:30
 
Перейти    
PQ. Подготовить POST запрос русские буквы из каши обратно в русские
 
#
11.02.2023 22:58:22
Здравствуйте.
Необходимо подготовить тело для запроса.
Облазил кодировки и все перетестил https://learn.microsoft.com/en-us/powerquery-m/textencoding-type
в Json.FromValue и Text.FromBinary

Проблема


На выходе в запросе toJson должно получиться
["CH-BP-008-000003желтый","CH-BP-POPIN-000002бордовый"]


Лечение в гугле совсем не нашёл. В основном обратный принцип, как из ответа получить адекватные буквы (юзать локаль или 1251 вроде как).

Русские буквы.XLSX (15.99 КБ)

:(
 
Перейти    
PQ. Рекурсивный запроc API не получается pagetoken пустить по кругу
 
#
08.02.2023 21:50:19
Здравствуйте.
Схожая тема с решением. Делаю идентично, но не без прикола
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=140847&a...

Попробовал такой подход и не крутит по кругу, отадёт только 200 строк, как в запросе
Код
let
    baseQuery = "https://api.partner.market.yandex.ru/v2/campaigns/"&Camp&"/offer-mapping-entries.json?limit=200&page_token=",
    Headers = [#"Authorization"="Bearer "&Token],
    
     generate = List.Generate(() => 
     [response = Json.Document(Web.Contents(baseQuery,[Headers=Headers]))[result]],
each [response][paging][nextPageToken]? <> null,
each [response = Json.Document(Web.Contents(baseQuery & Text.From([response][paging][nextPageToken]),[Headers=Headers]))],
each [response][offerMappingEntries])
in
    generate

Другая попытка
Код
let

    baseQuery = "https://api.partner.market.yandex.ru/v2/campaigns/"&Camp&"/offer-mapping-entries.json?limit=200&page_token=",
    Headers = [#"Authorization"="Bearer "&Token],
    
    Source = List.Generate(() => 
    [response = Json.Document(Web.Contents(baseQuery,[Headers=Headers]))[result], page_token = ""],
each [response][paging][nextPageToken]? <> null,
each [page_token = [response][paging][nextPageToken], response = Json.Document(Web.Contents(baseQuery&Text.From([page_token]),[Headers=Headers]))],
each [response][offerMappingEntries]),
Source1 = Source{0}
in
Source1

----
Так выглядит голый запрос с &page_token=



Так выглядит проверка условия, когда докрутилось до последнего page_token и в ответе нет nextPageToken



В обоих случаях парсит только 1 раз. Не улавливаю, что не туда с этим рекурсивом -.-




Как правильно передавать токен в третье условие функции?  
Изменено: jiumohoo - 08.02.2023 22:43:17
 
Перейти    
Найти текст и позицию его значения в столбце с повторяющимися похожим текстом, но то, которое по совпадению крайнее в строке
 
#
08.12.2022 22:02:56
Здравствуйте
Требуется отыскать категорию товара в большом справочнике, название которой повторяться может неоднократно, а находиться должно самым крайним в строке
Соответствие категории приходится на последнюю её иерархию

Пример:

Нужно найти тариф на категорию "Сумки" и вытащить 6%
Формулу, что написал тащит первое вхождение = 10%, что категорически неверно
Как её переделать, чтобы искала последнее значение по последнему вхождению на каждой строке не представляю
Код
=ИНДЕКС(C:C;ПОИСКПОЗ(ЛОЖЬ;ЕОШИБКА(ПОИСК(F3;B:B));0))
КатегорияТариф
Спорт и отдых-Туризм и отдых   на природе-Сумки-холодильники10%
Спорт и отдых-Зимние виды   спорта-Защита и экипировка-Сумки   и чехлы10%
Спорт и отдых-Охота и рыбалка-Сумки и ящики10%
Одежда, обувь и   аксессуары-Аксессуары-Сумки и   чемоданы-Сумки6%
Строительство и   ремонт-Инструменты-Расходные материалы и оснастка-Для цепных пил-Сумки и чехлы10%
Power Query тоже можно попробовать, но в связке с возвратом к артикулу, которому ищется

Книга1_123.xlsx (120.14 КБ)
Изменено: jiumohoo - 08.12.2022 22:07:56
 
Перейти    
Суммировать числа внутри ячейки, которые прописаны через слеш, Пример: текст в ячейке 5/5/1 и как получить в соседней ячейке их сумму 11?
 
#
06.12.2022 23:14:34
Отыскал только решение макросом, но мне оно не нужно. Формулой хоть как-то можно?
https://www.mrexcel.com/board/threads/sum-all-digits-in-single-cell.640823/page-2#post-5051651

Значения в ячейках по типу: 60.5/90/60.5
Преобразую через Подставить заменив точку на запятую и слеш на плюс, результат 60,5+90+60,5 = никак не могу сложить

pl ex.xlsx (22.7 КБ)

В гугле есть пример с весом в КГ, который убирается тем же Подставить и суммируют диапазон преобразованных ячеек и это совсем не то)

Самое дикое решение вижу это вычленение каждого числа по разделителю и после сложить. Формула на выходе будет трешевой
Изменено: jiumohoo - 06.12.2022 23:30:51
 
Перейти    
Варианты перебора подставления значений вместо нескольких десятков ЕСЛИ
 
#
07.09.2022 11:28:34
Здравствуйте.
Необходимо рассчитать процент скидки до маржи на определенный процент (ссылка на ячейку к примеру 15%).
Я сделал колхозный вариант в 60 вложений ЕСЛИ с подстановкой в каждое выражение скидки.

1) упёрся в ограничение 8192 знаков;
2) сократив формулу до 8000 знаков попал на непонятный баг, что с 100 строки формула визуально и технически ломается. Пропадает формула ЕСЛИ, но вроде как она считает, только книгу как .xlsx сохранить нельзя. Сильно напрягает такой вариант поведения формулы.
Код
=ЕСЛИОШИБКА(ЕСЛИ(((B5-(B5*60/100)-((((B5-(B5*60/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*60/100)-((((B5-(B5*60/100))*G5%)+E5+F5)+J5))>$K$4;60;
ЕСЛИ(((B5-(B5*59/100)-((((B5-(B5*59/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*59/100)-((((B5-(B5*59/100))*G5%)+E5+F5)+J5))>$K$4;59;
ЕСЛИ(((B5-(B5*58/100)-((((B5-(B5*58/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*58/100)-((((B5-(B5*58/100))*G5%)+E5+F5)+J5))>$K$4;58;
ЕСЛИ(((B5-(B5*57/100)-((((B5-(B5*57/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*57/100)-((((B5-(B5*57/100))*G5%)+E5+F5)+J5))>$K$4;57;
ЕСЛИ(((B5-(B5*56/100)-((((B5-(B5*56/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*56/100)-((((B5-(B5*56/100))*G5%)+E5+F5)+J5))>$K$4;56;
ЕСЛИ(((B5-(B5*55/100)-((((B5-(B5*55/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*55/100)-((((B5-(B5*55/100))*G5%)+E5+F5)+J5))>$K$4;55;
ЕСЛИ(((B5-(B5*54/100)-((((B5-(B5*54/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*54/100)-((((B5-(B5*54/100))*G5%)+E5+F5)+J5))>$K$4;54;
ЕСЛИ(((B5-(B5*53/100)-((((B5-(B5*53/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*53/100)-((((B5-(B5*53/100))*G5%)+E5+F5)+J5))>$K$4;53;
ЕСЛИ(((B5-(B5*52/100)-((((B5-(B5*52/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*52/100)-((((B5-(B5*52/100))*G5%)+E5+F5)+J5))>$K$4;52;
ЕСЛИ(((B5-(B5*51/100)-((((B5-(B5*51/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*51/100)-((((B5-(B5*51/100))*G5%)+E5+F5)+J5))>$K$4;51;
ЕСЛИ(((B5-(B5*50/100)-((((B5-(B5*50/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*50/100)-((((B5-(B5*50/100))*G5%)+E5+F5)+J5))>$K$4;50;
ЕСЛИ(((B5-(B5*49/100)-((((B5-(B5*49/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*49/100)-((((B5-(B5*49/100))*G5%)+E5+F5)+J5))>$K$4;49;
ЕСЛИ(((B5-(B5*48/100)-((((B5-(B5*48/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*48/100)-((((B5-(B5*48/100))*G5%)+E5+F5)+J5))>$K$4;48;
ЕСЛИ(((B5-(B5*47/100)-((((B5-(B5*47/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*47/100)-((((B5-(B5*47/100))*G5%)+E5+F5)+J5))>$K$4;47;
ЕСЛИ(((B5-(B5*46/100)-((((B5-(B5*46/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*46/100)-((((B5-(B5*46/100))*G5%)+E5+F5)+J5))>$K$4;46;
ЕСЛИ(((B5-(B5*45/100)-((((B5-(B5*45/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*45/100)-((((B5-(B5*45/100))*G5%)+E5+F5)+J5))>$K$4;45;
ЕСЛИ(((B5-(B5*44/100)-((((B5-(B5*44/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*44/100)-((((B5-(B5*44/100))*G5%)+E5+F5)+J5))>$K$4;44;
ЕСЛИ(((B5-(B5*43/100)-((((B5-(B5*43/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*43/100)-((((B5-(B5*43/100))*G5%)+E5+F5)+J5))>$K$4;43;
ЕСЛИ(((B5-(B5*42/100)-((((B5-(B5*42/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*42/100)-((((B5-(B5*42/100))*G5%)+E5+F5)+J5))>$K$4;42;
ЕСЛИ(((B5-(B5*41/100)-((((B5-(B5*41/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*41/100)-((((B5-(B5*41/100))*G5%)+E5+F5)+J5))>$K$4;41;
ЕСЛИ(((B5-(B5*40/100)-((((B5-(B5*40/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*40/100)-((((B5-(B5*40/100))*G5%)+E5+F5)+J5))>$K$4;40;
ЕСЛИ(((B5-(B5*39/100)-((((B5-(B5*39/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*39/100)-((((B5-(B5*39/100))*G5%)+E5+F5)+J5))>$K$4;39;
ЕСЛИ(((B5-(B5*38/100)-((((B5-(B5*38/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*38/100)-((((B5-(B5*38/100))*G5%)+E5+F5)+J5))>$K$4;38;
ЕСЛИ(((B5-(B5*37/100)-((((B5-(B5*37/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*37/100)-((((B5-(B5*37/100))*G5%)+E5+F5)+J5))>$K$4;37;
ЕСЛИ(((B5-(B5*36/100)-((((B5-(B5*36/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*36/100)-((((B5-(B5*36/100))*G5%)+E5+F5)+J5))>$K$4;36;
ЕСЛИ(((B5-(B5*35/100)-((((B5-(B5*35/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*35/100)-((((B5-(B5*35/100))*G5%)+E5+F5)+J5))>$K$4;35;
ЕСЛИ(((B5-(B5*34/100)-((((B5-(B5*34/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*34/100)-((((B5-(B5*34/100))*G5%)+E5+F5)+J5))>$K$4;34;
ЕСЛИ(((B5-(B5*33/100)-((((B5-(B5*33/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*33/100)-((((B5-(B5*33/100))*G5%)+E5+F5)+J5))>$K$4;33;
ЕСЛИ(((B5-(B5*32/100)-((((B5-(B5*32/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*32/100)-((((B5-(B5*32/100))*G5%)+E5+F5)+J5))>$K$4;32;
ЕСЛИ(((B5-(B5*31/100)-((((B5-(B5*31/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*31/100)-((((B5-(B5*31/100))*G5%)+E5+F5)+J5))>$K$4;31;
ЕСЛИ(((B5-(B5*30/100)-((((B5-(B5*30/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*30/100)-((((B5-(B5*30/100))*G5%)+E5+F5)+J5))>$K$4;30;
ЕСЛИ(((B5-(B5*29/100)-((((B5-(B5*29/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*29/100)-((((B5-(B5*29/100))*G5%)+E5+F5)+J5))>$K$4;29;
ЕСЛИ(((B5-(B5*28/100)-((((B5-(B5*28/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*28/100)-((((B5-(B5*28/100))*G5%)+E5+F5)+J5))>$K$4;28;
ЕСЛИ(((B5-(B5*27/100)-((((B5-(B5*27/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*27/100)-((((B5-(B5*27/100))*G5%)+E5+F5)+J5))>$K$4;27;
ЕСЛИ(((B5-(B5*26/100)-((((B5-(B5*26/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*26/100)-((((B5-(B5*26/100))*G5%)+E5+F5)+J5))>$K$4;26;
ЕСЛИ(((B5-(B5*25/100)-((((B5-(B5*25/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*25/100)-((((B5-(B5*25/100))*G5%)+E5+F5)+J5))>$K$4;25;
ЕСЛИ(((B5-(B5*24/100)-((((B5-(B5*24/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*24/100)-((((B5-(B5*24/100))*G5%)+E5+F5)+J5))>$K$4;24;
ЕСЛИ(((B5-(B5*23/100)-((((B5-(B5*23/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*23/100)-((((B5-(B5*23/100))*G5%)+E5+F5)+J5))>$K$4;23;
ЕСЛИ(((B5-(B5*22/100)-((((B5-(B5*22/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*22/100)-((((B5-(B5*22/100))*G5%)+E5+F5)+J5))>$K$4;22;
ЕСЛИ(((B5-(B5*21/100)-((((B5-(B5*21/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*21/100)-((((B5-(B5*21/100))*G5%)+E5+F5)+J5))>$K$4;21;
ЕСЛИ(((B5-(B5*20/100)-((((B5-(B5*20/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*20/100)-((((B5-(B5*20/100))*G5%)+E5+F5)+J5))>$K$4;20;
ЕСЛИ(((B5-(B5*19/100)-((((B5-(B5*19/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*19/100)-((((B5-(B5*19/100))*G5%)+E5+F5)+J5))>$K$4;19;
ЕСЛИ(((B5-(B5*18/100)-((((B5-(B5*18/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*18/100)-((((B5-(B5*18/100))*G5%)+E5+F5)+J5))>$K$4;18;
ЕСЛИ(((B5-(B5*17/100)-((((B5-(B5*17/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*17/100)-((((B5-(B5*17/100))*G5%)+E5+F5)+J5))>$K$4;17;
ЕСЛИ(((B5-(B5*16/100)-((((B5-(B5*16/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*16/100)-((((B5-(B5*16/100))*G5%)+E5+F5)+J5))>$K$4;16;
ЕСЛИ(((B5-(B5*15/100)-((((B5-(B5*15/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*15/100)-((((B5-(B5*15/100))*G5%)+E5+F5)+J5))>$K$4;15;
ЕСЛИ(((B5-(B5*14/100)-((((B5-(B5*14/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*14/100)-((((B5-(B5*14/100))*G5%)+E5+F5)+J5))>$K$4;14;
ЕСЛИ(((B5-(B5*13/100)-((((B5-(B5*13/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*13/100)-((((B5-(B5*13/100))*G5%)+E5+F5)+J5))>$K$4;13;
ЕСЛИ(((B5-(B5*12/100)-((((B5-(B5*12/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*12/100)-((((B5-(B5*12/100))*G5%)+E5+F5)+J5))>$K$4;12;
ЕСЛИ(((B5-(B5*11/100)-((((B5-(B5*11/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*11/100)-((((B5-(B5*11/100))*G5%)+E5+F5)+J5))>$K$4;11;
ЕСЛИ(((B5-(B5*10/100)-((((B5-(B5*10/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*10/100)-((((B5-(B5*10/100))*G5%)+E5+F5)+J5))>$K$4;10;
ЕСЛИ(((B5-(B5*9/100)-((((B5-(B5*9/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*9/100)-((((B5-(B5*9/100))*G5%)+E5+F5)+J5))>$K$4;9;
"Меньше 9"))))))))))))))))))))))))))))))))))))))))))))))))))));"")
Ломается




Просьба помочь, как ещё можно добиться варианта перебора значений. То ли как-то формулы массива применить и в них не особо понимаю.
Перебор значений вместо ЕСЛИ.xlsx (57.15 КБ)
Изменено: jiumohoo - 07.09.2022 12:00:56
 
Перейти    
VBA пересохранить каждый файл в папке в порядке возрастания с сортировки на листе
 
#
13.07.2022 11:48:13
Здравствуйте. Просьба помочь макросом пересохранить каждый файл в папке в последовательности возрастания
Вот такое безобразие в папке (300 файлов +/- хаотично сохранены)


Выгрузил из PQ на лист Folder Path и Name. Name разбил на доп. столбцы, чтобы выставить сортировку по дате.
На основании порядка строк в таблице в той же последовательности пересохранить каждый файл. Вес файлов разный от 10 кб до 5000 кб, по этому наверно ещё задержку надо прописать символическую (наверно, но не уверен, что это нужно).

Пересохранить.xlsx (28.45 КБ)Отсортировал в колонке D. В колонке Е обратно сцепил путь к файлам. И по колонке Е последовательно строкам запустить макрос
Изменено: jiumohoo - 13.07.2022 12:56:34
 
Перейти    
Гиперссылка. Не удается открыть: *ссылка*. Не удается скачать нужные данные. (Озон категорически не открывается)
 
#
31.05.2022 15:53:55
Потыкался по форуму и кажись проблема локальная, но крайне странно, что именно конкретный сайт отказывается открываться через эксель.

В файле ссылки на разные карточки товаров по разным маркетплейсам.

Озон 3 вида ссылок:
ФАК (база знаний) https://seller-edu.ozon.ru/ - открывается
API документация https://docs.ozon.ru/api/seller/ - открывается
Любая карточка товара или раздел сайта - не хочет

Абсурд какой-то.
Можно ли как-то вычленить хотя бы косвенно, что не нравится? Браузер по умолчанию на эксплорер менялся.
Ссылки Https на Http заменял.
Пару недель назад всё работало, сейчас такая вот проблема с озоном...
Изменено: jiumohoo - 31.05.2022 15:54:57
 
Перейти    
PQ. Транспортировать раскрываемые столбцы из List => Records в строчку среди других столбцов оставив только 1 строку со всеми значениями
 
#
21.05.2022 20:19:36
Здравствуйте.
При запросе на разный товар поле options (List) содержит разное количество строк в ответе.
Дополнительная проблема состоит в том, что в последствии обернув в функцию запрос будет отдавать разное количество столбцов Name с их характеристиками по каждому айдишнику (вопрос кажется решил, добавил список AllHeaders, который нужно будет подставить при раскрытии).



В файле имеется:
функция nmid - это как сейчас работает, без высасывания содержимого в options.
Table- айдишки берутся с таблицы + кастомный столбец функция nmid.

Будет функцией (Форум) - тот же самый nmid, только не функция. На котором можно играться пытавшись развернуть options.

Колонка Name должна стать заголовками, а value и measure сцепил кастомной колонкой "пользовательская".  Развернуть эти колонки в строчку, оставив лишь 1 строку со всеми значениями.




А другого айдишника иное количество колонок Name из-за чего не представляю, как потом срастить одну таблицу.


Пример


Поскольку у "категории" свои наименования характеристик, то создал Список со всеми возможными заголовками из options по категориям. Проблем свести в одну таблицу разные категории не будет.

После того, если всё-таки возможно распарсить мою задачу, просьба обернуть сразу в функцию и попробовать на Table

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

parse.xlsx (111.29 КБ)


С радостью оплачу решение задачи, если она не тянет на бесплатную помощь. Сообщите, перенесу в платную ветку.
Также нет проблем, если имеется совершенно иное решение, а не моё из топика.

Пытался это провернуть, но жаловалось на тип Текст в колонке Name. Однако там чуть иное
https://stackoverflow.com/questions/64478411/transpose-single-column-in-dax-or-power-query
Изменено: jiumohoo - 22.05.2022 17:43:34
 
Перейти    
Убивать процесс excel от каждой книги после закрытия (vba?)
 
#
12.05.2022 22:53:06
Здравствуйте.
На работе после перехода офиса с 2016 на 2019 эксель стал работать отвратительно (все смирились, но не я).
Поскольку всё дело на серваке, то айтишник крайне нежелательно вообще что-либо делает. Кажется даже пакеты обновлений windows не делаются, либо максимально поздно (мб через полгода) или просто скачать какой-нибудь хотфикс офиса - даже близко не делается, якобы что-то может где-то полететь. Как по мне бред. Увы и ах, ситуация такова, что работайте как есть... Всевозможные варианты перегуглил, надстройки повырубать, другие кнопки офиса потыкать из гайдов, безопасный режим excel - бесполезняк.

Когда процессов excel нет - открывает бодрячком.



Закрываю книги - процессы висят.



Открытых книг нет - процессы прошлых висят - запускаю новые файлы = понеслась...
3 старых закрытых книги висят + 3 новых запущенных, но открываются до жути долго (5-20 секунд, может и дольше). Разницы нет даже если в примере 1 книгу открывал бы - эффект одинаков.


Старые процессы пропадают примерно спустя 2-3 минуты. Шара по времени. Если работать в открытых книгах Х время и закрыть, то следующие также быстро откроются. Никто не работает весь день в 1-2 книгах, чтобы не испытывать проблем с лютейшей задержкой открытия книг.

Возможно ли написать макрос, который будет убивать процесс от каждой книги после закрытия? (В надстройку запихать).
В данный момент использую батник, чтобы убить весь эксель разом. Не лучшее решение, но по крайней мере не надо ждать.
taskkill /f /im excel.exe
Изменено: jiumohoo - 12.05.2022 22:56:51
 
Перейти    
Автоматический запуск переключения ссылок r1c1 на a1 из надстройки (не по кнопке)
 
#
12.05.2022 21:25:24
Здравствуйте. Кнопочных решений полно в инете и пользуюсь по сей момент. К сожалению, устал, много файлов с R1C1 ссылками...

Рабочие примеры для кнопки:
1.
Код
If Application.ReferenceStyle = xlA1 Then
Application.ReferenceStyle = xlR1C1
Else
Application.ReferenceStyle = xlA1
End If


2.
Код
Application.ReferenceStyle = xlA1 + xlR1C1 - Application.ReferenceStyle


Как сделать автоматическое изменение ссылок при запуске любого нового файла?
Пытался дописать ActiveWorkbook к .Application увы тщетно.
Ведь при запуске любого файла вторым делом запускается надстройка и ActiveWorkbook - ошибка




В надстройке имеется Sub Auto_Open()
Только как правильно вписать, чтобы действие происходило не в файле надстройки
 
Перейти    
PQ. Можно ли скрыть группы запросов по умолчанию?
 
#
24.03.2022 14:45:45
При каждом открытии файла "запросы и подключения" в развёрнутом виде. Запросы разбил по группам, которые должны вместе обновляться.
Неприятно то, что нужно скролить список запросов... А будь список из 50+ запросов.... и сделать группировку, то они всё равно развёрнуты окажутся. Какой смысл от группировки? Помимо того, что обновить можно запросы именно в этой группе. То ли не продумано, то ли я что-то пропустил?
Сие список, чтобы всегда был скрыт по группам.

Изменено: jiumohoo - 24.03.2022 14:48:11
 
Перейти    
PQ. Разделить таблицу на две по значению в колонке
 
#
23.03.2022 12:43:11
Здравствуйте.
Данные выгружаются длинной простынёй, как по значению в колонке разбить на две?


На выходе получить так
Изменено: jiumohoo - 23.03.2022 12:43:35
 
Перейти    
PQ. Зациклить Веб запрос с задержкой до изменения статуса в ответе
 
#
12.03.2022 19:20:31
Здравствуйте. Погуглил способ, как зациклить веб запрос для ожидания статуса ответа и нашёл 2 варианта, но сил не хватает осилить их использование.
List.Generate и Value.WaitFor

Есть 2 запроса API.
В первом запрашивается информация - отдаётся ключ для отчёта.


В этот момент сайт начал формировать отчёт и уйти на это может от 1-й секунды до Х секунд(минут).

Во втором запросе отправляется ключ полученный из первого запроса для получения отчёта.
Поскольку запросы уходят почти мгновенно, то статус 2-го запроса выглядит таким образом, то есть всё ещё формируется processing.


Я игрался с задержкой при помощи
Код
Function.InvokeAfter(()=> Json.Document(Web.Contents(SecondURL,[Content = Text.ToBinary(SecondBody), Headers = Headers])),#duration(0,0,0,15))[result]


Меня это не очень устраивает, поскольку как выше написал формироваться отчёт может непонятное время. Ставить 15 секунд, а может 2 минуты - это угадайка.

Как зациклить запрос вместе с задержкой при помощи Power Query?
Видится возможность ориентироваться на статус.
status = processing, повторить запрос через 5 секунд  и так по кругу при получении processing.


status = success, продолжить извлекать информацию оставшись в том же запросе
Json.Document(Web.Contents(SecondURL,[Content = Text.ToBinary(SecondBody), Headers = Headers]))[result]




Вспомогательная ссылка в которой статус processing/success
Test = Json.Document(Web.Contents(SecondURL,[Content = Text.ToBinary(SecondBody), Headers = Headers]))[result][status]

Как я понял принцип List.Generate, но не получилось, если это вообще хотя бы примерно правильно... Сильно напомнило формулу Если :C
List.Generate(()=> "success",
Test = "processing",
Test)
Изменено: jiumohoo - 13.03.2022 21:43:13
 
Перейти    
PQ. При Web запросе подставить переменную с листа в Content = Text.ToBinary (где уже есть данные)
 
#
09.03.2022 21:36:20
Здравствуйте.

Есть запрос на seller.ozon
Документация https://docs.ozon.ru/api/seller/#operation/AnalyticsAPI_AnalyticsItemTurnoverDataV3

Код
let
      URL = "https://api-seller.ozon.ru/v1/analytics/item_turnover", 
      ID = "ключ", 
      API = "ключ", 
Date_from = Excel.CurrentWorkbook(){[Name="Date_from"]}[Content]{0}[Column1],  - эту дату запихнуть в Body вместо ручной даты в коде
      Headers = [#"Content-Type" = "application/json", #"Client-Id" = ID, #"Api-Key" = API], 
Body = "{""date_from"":""2022-03-01""}", 
      Response = Json.Document(Web.Contents(URL, [Content = Text.ToBinary(Body), Headers = Headers]))
in
      Response


Аналогичная тема есть у другого юзера, где решение самого запроса было иным через Json.FromValue, но у меня почему-то этот вариант отказывается работать. И может быть там можно сделать то, что я хочу...

В основе всех запросов желание брать переменные с листа на примере даты 2022-03-01.
По принципу
Excel.CurrentWorkbook(){[Name="date_from"]}[Content]{0}[Column1]




Незадача в том, что не нашёл способ, как подпихивать в двухкавычный json вставку значений с листа.
Подскажите пожалуйста, как провернуть :(

По сути Body должен получится идентичным текущему




Файл с примером: turnover v1.xlsx (22.92 КБ)
Изменено: jiumohoo - 09.03.2022 21:40:49
 
Перейти    
PQ. Закрепить комментарий к ID вопроса, чтобы при обновлении таблицы комментарий не уезжал
 
#
27.01.2022 00:06:36
Название темы сложно подобрать под суть вопроса. Модераторы, отредактируйте пожалуйста, если более внятно получится понять.

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

Имеется 2 запроса на сайт с отзывами
1 запрос на архив (отвеченные вопросы)
2 запрос на вопросы

В приложенном файле:
2 листа "Вопрос на сайте хранится" и "Архив на сайте хранится" - сделал их для имитации хранения данных на сайте.
2 листа "Парсинг архива" и "Парсинг вопросов" - обращаются к двум листам выше опять же для имитации запроса.
1 лист "Объединение запросов" через Table.Combine объединено в общую таблицу.

Лист "Объединение запросов" - рабочая зона.
На листе добавлена колонка J вручную (не через PQ). В которой пишется комментарий напротив конкретного вопроса с пометкой для себя. Мол, если вопрос требует ответа с особыми знаниями по товару, то комментарий пишется "Вопрос передан производителю" ну и всякое такое.

Проблема в том, что при обновлении запросов эта таблица движется и текст из колонки J попадает на другой вопрос (айди).

Как можно красиво решить вопрос зафиксировав указанный комментарий по требуемому вопросу (айди)?

Один из вариантов, что могу придумать это вести вторую таблицу, куда копировать надо айди вопроса и сам комментарий, а дальше через PQ сращивать или просто ВПР. (Ну это работа ради работы, чтобы делать дубликаты... Мне категорически не нравится).

Чтобы создать движение таблицы "Объединение запросов" для имитации работы:
надо на листах "Вопрос на сайте хранится" перекинуть любой вопрос в "Архив на сайте хранится", мол ответил на вопрос и тот улетел в архив. Либо просто докинуть в лист "Вопрос на сайте хранится" самодельную строчку вопроса. И обновить запросы. Строчка комментария сместится на совершенно другой айди... :(
 
Перейти    
Отсутствует возможность использовать конструктор в умной таблице
 
#
14.12.2021 20:12:53
Недавно заметил, что не могу открыть конструктор в умных таблицах. Год назад колупал настройки эксель (может где выключил, но сейчас несколько раз пролазил - безуспешно).

Нет конструктора

Окей, включил кнопку в свойствах

У сводных таблиц всё замечательно с конструктором

Вопрос, что пошло не так?  :(  
 
Перейти    
PQ парсинг цен из списка ссылок (WB, нетабличные данные)
 
#
13.12.2021 10:46:42
Здравствуйте. Посмотрел парсинг от Николая https://www.planetaexcel.ru/techniques/13/16185/

PQ классная штука, но видимо надо язык М учить, чтобы делать нечто большее.
Посмотрел заморские видео, как парсить из списка, но там пример на BI и немного иное.

Имеется ли способ хоть как, даже колхозно, спарсить цены в ячейку? Или спарсить сам Url+цена и в отдельную таблицу.
Итоговое количество ссылок, которое понадобится парсить штук 30 максимум. Думал сделать 30 отдельных запросов на каждую ссылку, но мне сказали эксель умрёт и это тупо. Ко всему этому как бы не пытался не смог значения срастить ссылка+цена.  
 
Перейти    
Вставка вертикальных картинок улетает из ячейки в сторону (vba)
 
#
22.09.2021 22:37:18
Здравствуйте. Имеется скрипт, который веб ссылки на картинки и преобразует в картинки. Всё бы ничего, но вот такая беда с вертикальными фотками, что улетают в сторону. Помогите обуздать сие проблему, фиксануть скрипт. Пусть уж лучше будет плоско выглядеть, чем так. Или как вариант поворачивать на 90 градусов, тогда по сути должно красиво растягиваться в ячейке.

Скрытый текст
Код
Public Sub Add_Images_To_Cells()

    Dim lastRow As Long
    Dim URLs As Range, URL As Range
    Dim pic As Picture
    Dim urlColumn As String
    
    With ActiveSheet
        urlColumn = "H"
        lastRow = .Cells(Rows.Count, urlColumn).End(xlUp).Row
        Set URLs = .Range(urlColumn & "2:" & urlColumn & lastRow)
    End With

    For Each URL In URLs
        If InStr(URL.Value, "http") > 0 Then
            URL.Offset(0, 0).Select
            Set pic = URL.Parent.Pictures.Insert(URL.Value)
            With pic.ShapeRange
                .LockAspectRatio = msoFalse
                .Height = URL.Offset(0, 0).Height - 1
                .Width = URL.Offset(0, 0).Width - 1
                .LockAspectRatio = msoTrue
            End With
            
            DoEvents
        End If
    Next
    
End Sub
Изменено: jiumohoo - 23.09.2021 00:01:23
 
Перейти    
Сохранить csv в xlsx в ту же директорию
 
#
13.04.2021 21:21:05
.del
Изменено: jiumohoo - 22.09.2021 22:54:36
 
Перейти    
Сцепить значения в строках по количеству в ячейке
 
#
29.01.2021 22:09:31
.del
Изменено: jiumohoo - 22.09.2021 22:53:57
 
Перейти    
Размножить строки согласно кратности коробов
 
#
06.12.2020 21:36:03
.del
Изменено: jiumohoo - 22.09.2021 22:51:02
 
Перейти    
Страницы: 1
Наверх