Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 3 4 5 6 След.
Копирование диапазона на другой лист по двойному щелчку.
 
Цитата
написал:
Не знаю правильно понял или нет. Попробуйте так
Супер. Спасибо огромное.
Копирование диапазона на другой лист по двойному щелчку.
 
Добрый день. Как то приходилось пользоваться макросом по копированию ячейки на другой лист двойным щелчком мыши.
Помогите пожалуйста переделать макрос что бы он копировал диапозон на другой лист по тому же адресу.

Более подробно описал в примере.
Лист Test исходные данные. Лист Total конечные данные.
Вернуть последние 3 строки таблицы для разных вариантов фильтрации диапазона
 
Ура. Получилось вот так :
Подскажите такой вариант по скорости оптимальный или есть более вменяемые варианты.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    list = {0 .. 7},
    tfl = Table.FromList(list, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ren = Table.RenameColumns(tfl,{{"Column1", "over"}}),
    tmp = Table.AddColumn(ren, "tmp", each ren),
    ex = Table.ExpandTableColumn(tmp, "tmp", {"over"}, {"under"}),
    diap = Table.SelectRows(ex, each ([over] < [under])),
    a = Table.ToRecords(diap),
    trans = List.Transform (a, each let o =_[over], u =_[under], b = Table.SelectRows(Источник, each [Price] > o and [Price] < u) in Table.LastN(b,3)),
    comb = Table.Combine(trans)
in
    comb
PS. (( Пол дня проковырялся, а этот запрос работает в 2 раза медленнее чем первоначальный

ЗЫЫЫ. Вот так наковырял. Попытался ускорить запрос - взял в буфер присоеденяемую таблицу Table.Buffer (Источник), обработка  с 35 до 15 секунд ускорилась на образце.
Но когда Table.Buffer (Источник) добавил в изначальный запрос скорость возросла с 20 до 5 секунд.  
Изменено: kolyaya - 05.10.2021 22:19:19
Вернуть последние 3 строки таблицы для разных вариантов фильтрации диапазона
 
Начал пробовать реализацию через List.Accumulate.
Но как я понял по итогу выводит результат фильтра последнего условия. Подскажите пожалуйста как сделать что бы все строки собирались в одну таблицу
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    list = {0 .. 7},
    tfl = Table.FromList(list, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ren = Table.RenameColumns(tfl,{{"Column1", "over"}}),
    tmp = Table.AddColumn(ren, "tmp", each ren),
    ex = Table.ExpandTableColumn(tmp, "tmp", {"over"}, {"under"}),
    diap = Table.SelectRows(ex, each ([over] < [under])),

    filtert = List.Buffer(Table.ToRecords(diap)),
    td = List.Accumulate(
                filtert,
                Источник,
                (tbl,flt)=> let a = Table.SelectRows(tbl, each [Price] > flt[over] and [Price] < flt[under]) in a)
in
    td
Вернуть последние 3 строки таблицы для разных вариантов фильтрации диапазона
 
Доброго времени суток. Помогите пожалуйста оптимизировать запрос. Нужно подобрать оптимальные варианты для таблицы. Для этого добавляю таблицу к строке диапазона разворачиваю ее, и после группирую с фильтром и обработкой. Чувствую что это не правильно, да и работает медленно (.
Буду рад разным вариантам, особенно более быстрым. ;)
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    list = {0 .. 7},
    tfl = Table.FromList(list, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ren = Table.RenameColumns(tfl,{{"Column1", "over"}}),
    tmp = Table.AddColumn(ren, "tmp", each ren),
    ex = Table.ExpandTableColumn(tmp, "tmp", {"over"}, {"under"}),
    diap = Table.SelectRows(ex, each ([over] < [under])),
    data = Table.AddColumn(diap, "tmp", each Источник),
    exdata = Table.ExpandTableColumn(data, "tmp", {"Name", "Price"}, {"Name", "Price"}),
    gr = Table.Group(exdata, {"over", "under"}, {{"tmp", each Table.LastN(Table.SelectRows(_, each [Price] > [over] and [Price] < [under]),3), type table}}),
    sel = Table.SelectColumns(gr,{"tmp"}),
    exsel = Table.ExpandTableColumn(sel, "tmp", {"over", "under", "Name", "Price"}, {"over", "under", "Name", "Price"})
in
    exsel
Изменено: vikttur - 04.10.2021 22:15:37
Фильтр в Power Query по списку
 
Полчить данные из файла > книги

или
Код
Excel.Workbook(File.Contents("путь\имя файла.xlsm"), null, true){[Item="Имя таблицы",Kind="Table"]}[Data]
Изменено: kolyaya - 01.09.2021 15:38:51
Универсальный фильтр на PQ
 
Круто, то что нужно. Огромное спасибо.
Универсальный фильтр на PQ
 
Через опу получилось вот так реализовать. Может кто-нибудь помочь реализовать это для 30 колонок. Или тут только ручками.
Пока данный вариант работает как надо ))  
Изменено: kolyaya - 31.08.2021 16:58:32
Универсальный фильтр на PQ
 
Цитата
Антон написал:
почему бы не применить обычный if then:
Пробовал так сделать, но в  некоторых колонках отсутствуют значения, и при такой реализации отфильтровываются строки с пустыми ячейками.
Цитата
Александр написал:
Создать параметр и ссылаться на него в коде. У Вас может изменится столбец "Цена", я правильно понял?
В исходном файле фильтр для 30+ колонок. Пока написал фильтр под каждую колонку, но при изменении колонок придктся каждый раз лезть в код. ((
Универсальный фильтр на PQ
 
Спасибо, работает, но для большого количества колонок сильно грамоздкий код получится.
А может кто-нибудь помочь кодом? Что бы не лазить в код при изменении имени стобца.
Универсальный фильтр на PQ
 
Добрый день. Попробовал реализовать универсальный фильтр на power query. Но сталкнулся с такой проблемой.
Фильтр работает по принципу больше меньше. (Больше первой строки и меньше второй строки фильтра).
Проблема в том что если одно из полей пустое результат тоже пустой.
И еще в идеале наверное можно сделать что бы не прописывать правило для каждого  столбца. Но это наверное через функции нужно реализовавыть а мне до этого совсем далеко.
Изменено: kolyaya - 30.08.2021 15:59:14
Накопление столбца power query
 
Цитата
buchlotnik написал:
это что ж за зверь у вас под капотом?
Огромное спасибо. ноут обычный ай5 5300у )) Может из-за 77 колонок задумывается  
Накопление столбца power query
 
Спасибо. 300 строк за 2.5 минуты просчитало.
Правда формулу неправильно задал изначально  :oops:
Можете подправить?
Накопление столбца power query
 
Добрый вечер. Столкнулся с такой задачей. Нужно добавить в таблице столбец, который содержать сумму всех  предыдущих ячеек соседнего столбца. Для наглядности изобразил в виде талицы.
Пробовал дабавить столбец с исходной таблицей, фильтрацией по индексу и последующей группировкой. Но так и не дождался результата в течении 5 минут на исходной таблице (
Может есть методы попроще. Не через ..опу ((
Отправка таблицы в Telegram
 
Супер. все работает. Огромное спасибо.
Отправка таблицы в Telegram
 
Спасибо, но ругается на End if without block if. Так и не смог сам разобратся, вроде If есть ((
Отправка таблицы в Telegram
 
Данные в принципе объеденил через power query (что-то сразу не додумался)
Получается нужно написать цикл для отправки поочереди значений 9 столбца  (или колонки send из умной таблицы)
Но вот с циклами я вообще не дружу. Помогите пожалуйста.
Отправка таблицы в Telegram
 
Содержимое ячейки A1 отправляет. А вот со строками беда.
Добавил пример в 1 сообщении.
Отправка таблицы в Telegram
 
Добрый вечер. Самому мознов не хватает поэтому прошу помощи.

После обновления таблица запросом Power query данные в ней могут появиться но она так же может остаться пустая.

Нужно если таблица не пустая нужно отправить содержимое каждой строки отдельным сообщением соеденив ячейки столбцов через разделитель (например -) в Telegram.

На данный момент наковырял вот такой код:
Код
Sub Send_to_Telegram()
Dim oHttp As Object
Dim sURI As String

sData = Cells(1, 1) 'ActiveSheet.ListObjects("NowS").DataBodyRange.Select
sURI = "https://api.telegram.org/bot***/sendmessage?chat_id=***&text=" & sData
 
'MsgBox sURI, vbInformation, "запрос"
On Error Resume Next
Set oHttp = CreateObject("MSXML2.XMLHTTP")
If Err.Number <> 0 Then
Set oHttp = CreateObject("MSXML.XMLHTTPRequest")
End If
On Error GoTo 0
If oHttp Is Nothing Then Exit Sub
oHttp.Open "GET", sURI, False
oHttp.Send
'MsgBox oHttp.ResponseText, vbInformation, "ответ"
Set oHttp = Nothing
End Sub

Sub RemoveTableBodyData()

Dim tbl As ListObject

Set tbl = ActiveSheet.ListObjects("NowS")
  If tbl.ListRows.Count >= 1 Then
Call Send_to_Telegram
  End If
  
End Sub
Изменено: kolyaya - 21.12.2020 19:30:26
Выбор инструментов для расчета данных.
 
Цитата
Михаил Л написал:
Настораживает это: около 60 гб в месяц
Пока после отсеивания ненужных данных из этого массива остается нужных около 500 мб, все остальное уходит в архив. В архиве занимает 2-3 гб и ждет своего времени (вдруг понадобиться перепроверить что-то или достать что-то новое).
Цитата
Михаил Л написал:
есть возможность импортировать их напрямую в бд mysql
Так импортируйте, только тут же пишите:
В программе по сбору данных есть возможность прямого импорта в MySql. Сам MySql сервер есть на хостинге веб сайта. Так же сейчас запустил локальный для тестов.
Цитата
Михаил Л написал:
а это, раз знаний в sql мало, уже можно в PQ делать
Прослушал сегодня несколько курсов по MySql в youtube. Похоже что все задачи которые выполняет (в моих запросах) PQ можно реализовать на MySql. Буду пробовать.

Всем откликнувшимся спасибо. Буду рад услышать еще дополнительную критику и напутствия.  ;)  
Выбор инструментов для расчета данных.
 
Цитата
buchlotnik написал:
похоже это была массовая спам-рассылка
Ну что же вы так сразу - массовая. Только избранным 5 пользователям написал, которые чаще помогали и на мой взгляд более квалифицированные. Просто не у кого совета попросить ((
Выбор инструментов для расчета данных.
 
Здравствуйте. Вопрос теоретический:
Какие инструменты лучше использовать?
1. На данный момент изначально данные поступают в текстовом виде (csv)(около 60 гб в месяц), так же есть возможность импортировать их напрямую в бд mysql.
2. В отдельном excel файле происходит фильтрация данных путем запросов PQ каждые 3 часа, что бы влезть в 1 млн. строк. соответственно в ежедневном файле по 8 листов данных.
3. Далее  в следующем excel файле каждые 3 часа путем запросов PQ происходит сравнение строк данных на предмет изменения цены, а также расчет величины изменения.
4. В следующем файле данные за день объеденяются и перепроверяются.
В итоге есть структура папок с excel и csv (исходные данные)  структурированные по датам.
В конечном итоге Выбирается товар из отдельной структуры и по ID выискиваются все данные по изменениям цены за последние 15 дней.
На данный момент все работает, медленно и уверенно (хотя есть предчувствие что через одно место). Но возникают идеи о расширении количества пользователей данных (на данный момент использование только локально). И есть идея о переходе от структуры по датам в структуру по ID в виду очень долгого поиска.
1. Есть ли смысл переходить в формат БД? (Например Mysql, знания на уровне установки  CMS на хостинг и небольшие изменения структуры БД)
2. Какие варианты есть для обработки (расчетов, групировки) данных. Какой софт для этого можно использовать? Стоит изучить какие либо языки программирования? Возможно ли производить расчеты аналогичные запросам  PQ  путем sql-запросов?
3. Это не извращение хранить данные в бд, импортировать в excel обрабатывать с помощью запросов pq и экспортировать обратно?

Заранее благодарен.
 
Объединение строк Power query
 
PooHkrd, Дмитрий(The_Prist) Щербаков, Спасибо за оперативность, то что нужно.
Объединение строк Power query
 
Здравствуйте, что то никак не могу найти функцию объединения строк (если она есть).
Получаю код html страницы в виде txt файла, так как явную таблицу не находит. Дальше путем транспонирования и объединения столбцов сливал все в одну ячейку.
Но иногда количество строк разное, соответственно и по количеству столбцов ошибка выскакивает.
Код
let
    Источник = Lines.FromBinary(Web.Contents(_)),
    ListToT = Table.FromList(Источник, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Trans = Table.Transpose(ListToT),
    Comb = Table.CombineColumns(Trans,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"tmp")
in Comb
   
Группировка в power query с заменой переменной
 
Цитата
PooHkrd написал:
З.Ы. так что ли надо?
Код работает, но столкнался с проблемой, может получится в рамках данной темы решить.
Не хватает мозгов в ноуте что бы обработать весь объем (
Возможно ли обработка кода по очереди для каждой строки таблицы tmp. Как я понимаю это как-то через пользовательскую функцию можно реализовать но что-то никак не могу понять с какой стороны подойти.
Код
let    Источник = Table.Join( Excel.CurrentWorkbook(){[Name="Data"]}[Content], {}, Excel.CurrentWorkbook(){[Name="tmp"]}[Content], {} ),
    AddedZ = Table.AddColumn(Источник, "z", each [x]+[y]*[tmp]),
    GroupZ = Table.Group(AddedZ, {"x", "tmp"}, {{"count", each Table.RowCount(_), type number}, {"z", each List.Sum([z]), type number}})
in
    GroupZ
т.е. в коде в первой строке происходит объединение всех строк со всеми, а нужно что бы добавилась только первая строка потом проиходил расчет и группировка.
И так для каждой строки tmp, и в конце объединить результаты для каждой строки.

P.S. Пока получилось вот так реализовать:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Group = Table.Group(Источник, {"x", "y"}, {{"Data", each 

let
    Join = Table.Join(_, {}, Excel.CurrentWorkbook(){[Name="tmp"]}[Content], {} ),
    AddedZ = Table.AddColumn(Join, "z", each [x]+[y]*[tmp]),
    GroupZ = Table.Group(AddedZ, {"x", "tmp"}, {{"count", each Table.RowCount(_), type number}, {"z", each List.Sum([z]), type number}})
in GroupZ, type table}}),

    Select = Table.SelectColumns(Group,{"Data"}),
    ExData = Table.ExpandTableColumn(Select, "Data", {"x", "tmp", "count", "z"}, {"x", "tmp", "count", "z"})
in
    ExData
Результат отличается от файла в примере, но по факту считает как надо. Только есть ощущение что данный тип реализации через одно место ((
Изменено: kolyaya - 07.11.2020 22:31:03
Группировка в power query с заменой переменной
 
PooHkrd, Спасибо большое, то что нужно.  
Группировка в power query с заменой переменной
 
Добрый день. Помогите пожалуйста написать код для решения такой задачи.
Есть таблица с данными, таблица с переменными, и код группировки с расчетом и подстановкой переменной.
Нужно сделать группировку со всеми возможными вариантами из таблицы с переменными.

Попробовал изобразить вручную для первых 3 переменных, надеюсь будет все понятно.
Изменено: kolyaya - 05.11.2020 16:13:38
Подстановка кода из другой таблицы по неполному условию, только Power Query или DAX
 
Ну вы уже сильно не злитесь на меня. Вот так как вы сделали самое то  ;) . Уже опробовал все работает. Огромное спасибо.
Подстановка кода из другой таблицы по неполному условию, только Power Query или DAX
 
Не сочтите за наглость  :oops:
Вроде получилось код Андрея адаптировать по свои данные (точнее данные под код)
Осталось одно НО:
Цитата
Максим Зеленский написал:
Подвох:В Данных столбцы проверки заполнены всегда, пустых значений в них нет.
В Справочнике не в каждой строке заполнены значения в столбцах Продукт, Тип, Канал. Например, может быть не заполнен столбец Тип, что означает "любой Тип". Или столбец Канал, что означает "любой Канал". Может быть не заполнено несколько столбцов. В конце концов, встречаются условия только по одному столбцу.
Логика подстановки должна быть такой, что сначала проверяются все полные соответствия, затем частично неполные (например, только отсутствующий тип), затем все остальные. В справочнике данные могут быть расставлены по приоритету, если это необходимо.
По задаче Максима - сначала проверяются все полные соответствия, затем частично неполные (например, только отсутствующий тип), затем все остальные. В справочнике данные могут быть расставлены по приоритету, если это необходимо.
А мне необходимо проверять по порядку. (построчно по справочнику от первой сроки к последней).
Помогите пожалуйста с справиться с этой жуткой задачей.
Объединение таблиц при помощи PQ при условии что некоторые столбцы могут иметь любое значение
 
Балбес я (( все равно не правильно задачу поставил.
Прошу прощения за потраченое время. :oops:

P.S. По факту подошел вариант в вышеуказаной теме Максима Зеленского.
Изменено: kolyaya - 16.10.2020 17:47:25
Страницы: 1 2 3 4 5 6 След.
Наверх