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

Страницы: 1 2 След.
Округление до ближайшего из чисел с условием
 
#
11.02.2025 22:44:52
Всем огромное спасибо! Воспользовался решением на PQ!
 
Перейти    
Округление до ближайшего из чисел с условием
 
#
11.02.2025 19:44:47
Можно и на PQ, если что
 
Перейти    
Округление до ближайшего из чисел с условием
 
#
11.02.2025 19:30:32
Добрый день! Есть задача формулой округлить цены из выборки до ближайшего числа из ценовой политики.
Например, вот так выглядит ценовая политика:
Цена
290
750
950
1250
1500
1750
1950
2250
2500
2750
2950
3250
3500
3950
4500
4950
5500
7500
9500
12500
15000
Привило округления
округляем цену в большую сторону до ближайшего из чисел из ценовой политики
однако, если цена превышает лишь на 5%, то округялем до меньшего из числе из ценовой политики

например
299 больше 290 лишь на 3%, значит округляем до 290
601 округляется до 750, т.к. 610 больше 290 более чем на 5%

Цена Цена с округлением
299 290
601 750
627 750
654 750
1496 1500
1759 1750

Как можно сделать это формулами? Цены в ценовой политике могут и обновляться и добавляться.
Изменено: БМВ - 11.02.2025 20:06:48
 
Перейти    
Формула подсчета стоимости аренды
 
#
23.09.2024 15:55:37
Добрый день, всем!
Есть задача автоматизации подсчета цены аренды. В разное время и дни недели - стоимость аренды разная (задаются пользователем), поэтому не все так тривиально.
Пример расчета приложен, желтым отмечены ячейки, которые нужно подсчитать. Подсчет уже реализован через дополнительные столбцы, но хотелось бы найти более эффективное решение, желательно через 1 формулу, которая также будет работать и в google таблицах (будет миграция впоследствии), т.е. макросы не приветствуются.

Если решение есть - пришлите ориентир по стоимости, работа буде оплачена. Спасибо
Изменено: deffocus - 23.09.2024 16:01:15
 
Перейти    
PQ. Дробление строк по правилу, Дробление строк по правилу через PQ
 
#
05.08.2024 17:25:30
Спасибо! Работает!
 
Перейти    
PQ. Дробление строк по правилу, Дробление строк по правилу через PQ
 
#
05.08.2024 14:49:05
Добрый день!
Большая просьба помочь со следующей задачкой. Требуется подсчитать количество проектов в каждой ячейке, поделить сумму на это количество, раздробить строки для каждого проекта и поставить соответствующую сумму. Реализовать необходимо через PQ.

Исходные данные:
ПроектыСуммаДата
Проект1, Проект25000                        01.01.2024
Проект2450001.01.2024
Проект2, Проект4, Проект5                  900002.01.2024
Проект5100003.01.2024
Должно получиться:
ПроектыСуммаДата
Проект1                                                  2500                         01.01.2024
Проект2250001.01.2024
Проект2450001.01.2024
Проект2300002.01.2024
Проект4300002.01.2024
Проект5300002.01.2024
Проект5100002.01.2024
На скрине будет понятнее:



Спасибо!!!
Изменено: deffocus - 05.08.2024 14:59:31
 
Перейти    
Подсчет заполняемости саун, Подсчет заполняемости саун
 
#
22.04.2024 14:26:03
Исполнитель найден
 
Перейти    
Подсчет заполняемости саун, Подсчет заполняемости саун
 
#
22.04.2024 13:39:06
Добрый день!
Задача в следующем, есть таблица с информацией о брони в саунах (дата и время начала и конца).
Через PQ необходимо по выбранной сауне (или по всем) подсчитать, насколько часто встречался данный промежуток времени в брони. В таблице 2 - нужно тоже самое, но заполнить уже в % (например, 100% означат, что этот промежуток времени во всех днях был заполнен).
Просьба указать стоимость и срок. Спасибо!
Изменено: deffocus - 22.04.2024 13:40:18
 
Перейти    
Подсчет количества заполненных часов
 
#
22.04.2024 12:33:24
Всем огромное человеческое спасибо! Оба последних варианта подошли!!
 
Перейти    
Подсчет количества заполненных часов
 
#
22.04.2024 12:08:44
Спасибо за ваше время. Получается, что единственный вариант, это 1000 строчек переводить в столбцы, верно?
Изменено: deffocus - 22.04.2024 12:09:11
 
Перейти    
Подсчет количества заполненных часов
 
#
22.04.2024 09:58:49
Спасибо за то, что откликнулись! Дело в том, что строчек по брони будет около 1000. Тогда, предложенной формулой их не подсчитать.
Это я просто для пример только сделал 2 строчки, чтобы было наглядно и можно было проверить.

Дополнил пример комментариями.
Изменено: deffocus - 22.04.2024 10:00:36
 
Перейти    
Подсчет количества заполненных часов
 
#
21.04.2024 23:36:31
Добрый день!
Обращаюсь с задачкой, которую мне не удалось решить. Прошу помощи.
Есть время начало брони гостиницы и ее конца, например, бронь открылась в 15 часов, а закрылась в 20. Есть посложнее пример, началась бронь в 22 часа, а закончилась уже на следующий день в 3 часа ночи.
Нам необходимо выявить самые заполненные часы, т.е. подсчитать количество часовых промежутков, которые встречались в брони. Пример во вложении
 
Перейти    
Фильтрация (настраиваемый столбец, вложенная таблица) в PQ
 
#
13.03.2023 16:09:44
Всем огромное спасибо, оба решения хоть и не тривиальные, но более-менее понятные :)
И главное рабочие!
 
Перейти    
Фильтрация (настраиваемый столбец, вложенная таблица) в PQ
 
#
13.03.2023 15:05:06
Ну вот как вы делаете эту Магию?? ))
Я уже несколько книг прочитал, но кроме как добавления нового столбца с кодом по типу "= ( r ) => Table.SelectRows ( r[ТаблицаСделки], each [Код] = r[Код]) не могу дальше двигаться.
Спасибо огромное, это то что нужно!
 
Перейти    
Фильтрация (настраиваемый столбец, вложенная таблица) в PQ
 
#
13.03.2023 14:36:25
Поймали :) в любом случае спасибо Вам.
На самом деле я ждал решения через фильтрацию вложенной таблицы в столбец, она уже была начата в моем варианте.
В вашем решении придется добавлять много новых столбцов в исходные таблицы. Добавил Number.Abs, что то не полегчало..
Изменено: deffocus - 13.03.2023 14:39:43
 
Перейти    
Фильтрация (настраиваемый столбец, вложенная таблица) в PQ
 
#
13.03.2023 14:07:58
evgeniygeo,

разница же в абсолюте должна быть :) между ними 12 дней, что больше 6
 
Перейти    
Фильтрация (настраиваемый столбец, вложенная таблица) в PQ
 
#
13.03.2023 13:26:17
evgeniygeo,

В обоих таблицах есть совпадающие коды товаров, по ним и происходит объединение.
В исходной таблице в доп.столбце надо поставить "да", в том случае, если разница даты из исходной таблицы (для первой строки это 14.01.23) и какой либо найденной датой из доп.таблицы (для этого товара) меньше или равна 6.

Я поставил в приложенном файле формулами, чтобы было понятнее.
К сожалению, в предложенном вами варианте, стоит "да" в строке с датой 11.02.23 для товара CCHH, что неверно...т.к. разница с датами из доп.таблицы для товара CCHH ни в одном случае не дает меньше или равно 6
Изменено: deffocus - 13.03.2023 13:33:11
 
Перейти    
Фильтрация (настраиваемый столбец, вложенная таблица) в PQ
 
#
13.03.2023 12:45:37
Добрый день!

Пытаюсь решить следующую задачку. Есть исходная таблица с датой сделки и кодом товара:
Дата   сделкиКод товараСумма
14.01.2023HHFF5000
15.02.2023HHFF6000
01.03.2023HHFF4000
12.01.2023CCHH3500
11.02.2023CCHH6500
02.03.2023CCHH4500
и доп.таблица с датой и кодом товара:
ДатаКод товара
20.01.2023HHFF
25.02.2023HHFF
31.12.2022CCHH
11.01.2023CCHH
15.03.2023CCHH
30.04.2023CCHH
Обе таблицы надо сопоставить по коду товара и вычислить разницу между датами (в исходной таблице и доп.таблицей). Если разница дат меньше или равна 6, то в исходной таблице надо в новом столбце сделать пометку для соответствующей строки. Выглядеть это будет вот так:
Дата сделкиКод товараСуммаДоп.столбец
14.01.2023HHFF5000да (т.к. разница между 20.01.23 и 14.01.23 меньше или равна 6)
15.02.2023HHFF6000
01.03.2023HHFF4000да
12.01.2023CCHH3500да
11.02.2023CCHH6500
02.03.2023CCHH4500
Я пытался реализовать это через настраиваемый столбец и вложенной таблицей в PowerQuery, но пока не получилось. Просьба, помочь.
Очень подробный пример приложен.
Изменено: deffocus - 13.03.2023 12:48:09
 
Перейти    
Выгрузка из Microsoft SQL Server через вызов процедуры exec с помощью VBA Excel
 
#
16.01.2023 20:09:42
Добрый день!
Есть Microsoft SQL Server, где хранится база данных.
Для загрузки данных в самом SQL Server сначала определяются переменные, затем запускается процедура (exec), после этого следуюет сам Select. Пример кода как это работает в SQL Server Managment:
Код
declare @startDate date = '2022-01-01',@endDate date = '2022-12-31',@name1 varChar(150) = 'Наименование1',@type nvarChar(3) = 'Параметр1';
exec [base].[sp_upLoadXLS] @startDate ,@endDate ,@nameFund ,@type ;
select * from [base].[npo_1];
Т.е. если все запустить, то SQL Server выполнит процедуру и только после этого выгрузит Select.

Теперь требуется все тоже самое запустить и выгрузить на лист через средства VBA Excel.
Я написал следующий код:
Код
Sub LoadData()

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection
Set rst = New ADODB.Recordset

cn.ConnectionString = "DRIVER={SQL Server};SERVER=MMM1;DATABASE=MMM2" 'Параметры строки подключения
cn.Open


rst.Open "declare @startDate date = '2022-01-01',@endDate date = '2022-12-31',@nameFund varChar(150) = 'Наименование1',@type nvarChar(3) = 'Параметр1'; exec [base].[sp_upLoadXLS] @startDate ,@endDate ,@nameFund ,@type; select * from [base].[npo_1]; "

ActiveSheet.Range("A1").CopyFromRecordset rst 'Извлекаем данные на лист

rst.Close
cn.Close

Set rst = Nothing
Set cn = Nothing

End Sub
Проблема: я не знаю как передать в rst.Open все три исходных строчки (включая declare, exec и select). Прошерстил ветки форума, но не нашел нужное.
И как я понимаю, сначала выполняется процедура, после ее завершения идет обращение через Select, т.е. важно чтобы сначала завершилась процедура.
Помогите, пожалуйста, с реализацией макроса. Спасибо!
Изменено: deffocus - 16.01.2023 20:14:36
 
Перейти    
Power Query Фильтрация дат, оставляем максимальную в месяце
 
#
10.01.2023 14:50:41
"Приручи данные" заказал еще до НГ :) большое спасибо и за другие источники!
 
Перейти    
Power Query Фильтрация дат, оставляем максимальную в месяце
 
#
10.01.2023 12:07:49
Большое спасибо!
Элегантное решение surkenny уже в работе, все отлично!
surkenny, я логически понимаю ваше решение. Но вот как такое писать самому :) возможно вы подскажете какие то ресурсы или книгу, с помощью которых можно научиться писать такой код?
 
Перейти    
Power Query Фильтрация дат, оставляем максимальную в месяце
 
#
09.01.2023 17:36:28
Добрый день!

Могут ли коллеги помочь со следующей задачкой?
Есть исходные данные с разными датами. Также на листе есть фильтр, если значение его 0 - то нужно через PQ вывести из исходной таблицы все строки с датой максимальной в каждом месяце. Если же фильтр 1, то просто вывести все строки. Пример ниже + файл
ИСХОДНАЯ
ДатаСвойство1Свойство2
31.12.2021219389
12.01.2022337116
19.01.202246468
26.01.2022290471
31.01.202249145
09.02.202289226
16.02.202220040
18.02.2022417186
25.02.2022482313
28.02.2022433228
10.03.20223377
16.03.202251183
23.03.2022345210
31.03.202220423
11.05.2022473322
18.05.202250218
25.05.202221305
31.05.2022109231
15.12.202276283
Если фильтр 0, то
ДатаСвойство1Свойство2
31.12.2021219389
31.01.202249145
28.02.2022433228
31.03.202220423
31.05.2022109231
15.12.202276283
Если фильтр 1, то выводим всю исходную таблицу.
Большое спасибо!
Изменено: deffocus - 09.01.2023 18:02:36
 
Перейти    
Power Query отсечение лишних строк
 
#
26.09.2022 17:54:53
surkenny, работаю, но не на таком уровне как Вы. Большое спасибо, очень помогли - все работает!
 
Перейти    
Power Query отсечение лишних строк
 
#
26.09.2022 11:55:15
Михаил Л, surkenny, большое спасибо за помощь!
Проверил оба варианта.
При какой комбинации они не работают: если вообще не найдено значения "не Call", в таком случае все данные должны попасть :)

Прикрепляю пример с этими данными.
 
Перейти    
Power Query отсечение лишних строк
 
#
23.09.2022 16:15:10
Добры день, всем!
Помогите с решением нетривиальной задачки с помощью PowerQuery отсечь ненужные строки в этой таблице:
ДатаПараметр1Параметр2Параметр3Параметр4
21.03.2023СинийШирокийCall2158
19.09.2023СинийШирокийCall8496
19.03.2024СинийШирокийCall1487
17.09.2024СинийШирокийCall3214
18.03.2025СинийШирокийCall9841
16.09.2025СинийШирокийCall3217
17.03.2026СинийШирокийне Call1898
17.03.2026СинийШирокийCall1478
15.09.2026СинийШирокийCall1456
16.03.2027СинийШирокийCall3259
14.09.2027СинийШирокийCall9165
14.03.2028СинийШирокийCall28
12.09.2028СинийШирокийCall5059
13.03.2029СинийШирокийCall7645
11.09.2029СинийШирокийCall6328
12.03.2030СинийШирокийCall7976
10.09.2030СинийШирокийCall6045
11.03.2031СинийШирокийCall3765
11.03.2031СинийШирокийне Call4007
30.09.2022ЖелтыйШирокийне Call2158
30.09.2022ЖелтыйШирокийCall8496
30.12.2022ЖелтыйШирокийCall1487
31.03.2023ЖелтыйШирокийCall3214
30.06.2023ЖелтыйШирокийCall9841
29.09.2023ЖелтыйШирокийCall3217
29.12.2023ЖелтыйШирокийне Call1898
29.03.2024ЖелтыйШирокийCall1478
28.06.2024ЖелтыйШирокийCall1456
27.09.2024ЖелтыйШирокийCall3259
27.09.2024ЖелтыйШирокийCall9165
Правило отсечения: Как только  в столбце "Параметр3" появляется значение "не Call", то убираем все значения с теми же "Параметр1" и "Параметр2" дата которых больше, чем дата когда появилось значение "не Call".

В результате получится следующая таблица:
ДатаПараметр1Параметр2Параметр3Параметр4
21.03.2023СинийШирокийCall2158
19.09.2023СинийШирокийCall8496
19.03.2024СинийШирокийCall1487
17.09.2024СинийШирокийCall3214
18.03.2025СинийШирокийCall9841
16.09.2025СинийШирокийCall3217
17.03.2026СинийШирокийне Call1898
17.03.2026СинийШирокийCall1478
30.09.2022ЖелтыйШирокийне Call2158
30.09.2022ЖелтыйШирокийCall8496
Прикладываю пример в Excel. Буду очень рад помощи, спасибо!!!
Изменено: deffocus - 23.09.2022 16:16:43
 
Перейти    
Power Query получить csv из веб-запроса
 
#
06.07.2022 11:32:16
evgeniygeo, спасибо
Похоже на то, что это пока единственный рабочий вариант решения вопроса.
 
Перейти    
Power Query получить csv из веб-запроса
 
#
05.07.2022 17:50:49
Добрый день!

У меня не получилось подключиться средствами PQ к csv файлу из следующего веб-запроса:
https://sdw-wsrest.ecb.europa.eu/service/data/YC/B.U2.EUR.4F.G_N_A+G_N_C.SV_C_YM.?lastNObservations=...
То есть через браузер  сразу скачивается csv файл без регистраций.

Вопрос, как к нему можно подключится через PQ напрямую по ссылке?

Запрос вида
let
   Источник = Csv.Document(Web.Contents("https://sdw-wsrest.ecb.europa.eu/service/data/YC/B.U2.EUR.4F.G_N_A+G_N_C.SV_C_YM.?lastNObservations=...) )
in
   Источник

выдает ошибку (400)
Файл прикрепил.  
Изменено: deffocus - 05.07.2022 18:22:05
 
Перейти    
Power Query. Вложенная группировка
 
#
10.06.2022 20:13:17
Супер! Действительно работает.
Можно ли попросить еще одну "нестандартную" группировку сделать?
Нужно переместить некоторые столбцы в строки.
Исходная таблица:
Категория 1Категория 2Категория 3Сумма1Сумма2
Инф1Флт2Дрмб15090
Инф2Флт2Дрмб2100180
Инф3Флт3Дрмб32036
Получить нужно вот такую:
Категория 1Категория 2Дрмб3ВидСуммыСумма
Инф1Флт2Дрмб1Сумма150
Инф2Флт2Дрмб2Сумма1100
Инф3Флт3Дрмб3Сумма120
Инф1Флт2Дрмб1Сумма290
Инф2Флт2Дрмб2Сумма2180
Инф3Флт3Дрмб3Сумма236
Как такой трюк можно осуществить? Пример в приложении. Заранее спасибо!
Изменено: deffocus - 10.06.2022 20:13:43
 
Перейти    
Power Query. Вложенная группировка
 
#
10.06.2022 12:49:37
Цитата
написал:
deffocus, PQ не для этого создан  Это легко делается в PP. Зачем в PQ  это вычислять?
Но, раз уж надо:
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16      let        src = Excel.CurrentWorkbook(){[ Name =   "Таблица1"   ]}[Content],        typed = Table.TransformColumnTypes (          src,          { {   "Категория 1"  , type text }, {   "Категория 2"  , type text }, {   "Категория 3"  , type text }, {   "Сумма"  , type number } }        ),        group = Table.Group (          typed,          {   "Категория 1"  ,   "Категория 2"   },          {            {   "Дрмб3"  , ( t ) => List.Sum ( Table.SelectRows ( t, ( r ) => r[Категория 3] =   "Дрмб3"   )[Сумма] ), type number },            {   "Общая  сумма"  , each List.Sum ( [Сумма] ), type number }          }        )    in        group  
 
PP - это не Power Bi? Пока огромное удовольствие изучать и делать работу на PQ :)
 
Перейти    
Power Query. Вложенная группировка
 
#
10.06.2022 12:48:04
Цитата
написал:
Код
    [URL=#]?[/URL]       1  2  3  4  5  6      let          Источник = Excel.CurrentWorkbook(){[Name=  "Таблица1"  ]}[Content],          #  "Измененный тип"   = Table.TransformColumnTypes(Источник,{{  "Категория 1"  , type text}, {  "Категория 2"  , type text}, {  "Категория 3"  , type text}, {  "Сумма"  , Int64.Type}}),          #  "Сгруппированные строки"   = Table.Group(#  "Измененный тип"  , {  "Категория 1"  ,   "Категория 2"  }, {{  "Сумма по критерию Дрмб3"  , each List.Sum(Table.SelectRows(_, each [Категория 3] =   "Дрмб3"  )[Сумма])},{  "Общая сумма"  , each List.Sum([Сумма]), type number}})    in          #  "Сгруппированные строки"  
 
Большое спасибо! Просто огромное!
Если требуется дописать условие в категории, то это будет выглядеть так? Вроде не работает в таком виде:
#"Сгруппированные строки" = Table.Group(#"Измененный тип", {"Категория 1", "Категория 2"}, {{"Сумма по критерию Дрмб3", each List.Sum(Table.SelectRows(_, each [Категория 3] = "Дрмб3" or [Категория 3] = "Дрмб1")[Сумма])},{"Общая сумма", each List.Sum([Сумма]), type number}})
 
Перейти    
Страницы: 1 2 След.
Наверх