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

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

Более подробно описал в примере.
Лист 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])),
    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
Универсальный фильтр на PQ
 
Добрый день. Попробовал реализовать универсальный фильтр на power query. Но сталкнулся с такой проблемой.
Фильтр работает по принципу больше меньше. (Больше первой строки и меньше второй строки фильтра).
Проблема в том что если одно из полей пустое результат тоже пустой.
И еще в идеале наверное можно сделать что бы не прописывать правило для каждого  столбца. Но это наверное через функции нужно реализовавыть а мне до этого совсем далеко.
Изменено: kolyaya - 30.08.2021 15:59:14
Накопление столбца power query
 
Добрый вечер. Столкнулся с такой задачей. Нужно добавить в таблице столбец, который содержать сумму всех  предыдущих ячеек соседнего столбца. Для наглядности изобразил в виде талицы.
Пробовал дабавить столбец с исходной таблицей, фильтрацией по индексу и последующей группировкой. Но так и не дождался результата в течении 5 минут на исходной таблице (
Может есть методы попроще. Не через ..опу ((
Отправка таблицы в 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
Выбор инструментов для расчета данных.
 
Здравствуйте. Вопрос теоретический:
Какие инструменты лучше использовать?
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
 
Здравствуйте, что то никак не могу найти функцию объединения строк (если она есть).
Получаю код 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 с заменой переменной
 
Добрый день. Помогите пожалуйста написать код для решения такой задачи.
Есть таблица с данными, таблица с переменными, и код группировки с расчетом и подстановкой переменной.
Нужно сделать группировку со всеми возможными вариантами из таблицы с переменными.

Попробовал изобразить вручную для первых 3 переменных, надеюсь будет все понятно.
Изменено: kolyaya - 05.11.2020 16:13:38
Обработка запросов PQ по списку url-адресов
 
Здравствуйте. Помогите пожалуйста с таким вопросом.
Есть список url-адресов с которых импортируются данные, этот список динамический.
Возможно ли сделать что бы запрос обрабатывающий адрес проходил по всему списку адресов, или необходимо создавать запрос под каждый адрес?
Сбор данных при помощи power query
 
Здравствуйте. Подскажите как можно сделать (если можно) что бы при выполнении запроса Power Query данные в таблице не обновлялись, а добавлялись в конец таблицы.
Пробовал играться со свойствами, но как я понял свойства относится именно к обновлению.
Изменено: kolyaya - 14.10.2020 16:13:12
Объединение таблиц при помощи PQ при условии что некоторые столбцы могут иметь любое значение
 
Доброго времени суток.
Столкнулся с такой задачей. Есть 2 таблицы.
В одной перечислены значения для группы характеристик, в другой характеристики для которых нужно подобрать значения из первой таблицы.
Проблема в том, что нужно подобрать группами характеристик (изобразил в примере).
В исходе характеристик в районе 10, поэтому просто Join и Combine не подошло, так как приходится объединять много вариаций.  
Подсчет количества строк и суммы для комбинаций характеристик.
 
Добрый вечер. Помогите пожалуйста с такой задачей.
Есть таблица условно у товара ряд характеристик указаных в отдельных столбцах.
Нужно рассчитать количество строк и сумму с различных комбинаций. Характеристик в исходном файле более 20 поэтому ручной перебор не помог ((, пробовал через PQ но сообразил только для частных случаев при помощи группировки. А как можно перебрать все возможные комбинации характеристик. В примере попытался изобразить то что нужно. Как null указал столбец когда для подсчета характеристика не используется. Например подсчет строк только наименованию. (надеюсь будет понятно).  
Запуск макроса, если книга открывается в заданный промежуток времени
 
Доброго времени. Помогите написать vba  макрос который выполняет действие в зависимости от времени запуска.
В чем суть? Макрос выполняется при открытии файла, нужно если он открывается в промежуток времени от 0-00 до 6-00 - выполняется макрос 1
если он открывается в промежуток времени от 6-00 до 12-00 - выполняется макрос 2.
Помогите пожалуйста.
Макрос копирования значения ячейки на другой лист по двойному клику
 
Добрый день. Помогите пожалуйста. Нужно написать макрос который при двойном нажатии на ячейку в столбце id на листе data копировал значение ячейки на лист Card в A2 (или в таблицу ID) и после копирования запускал Макрос1 ().
Постоение график по оси Х со значением типа DateTime
 
Добрый день. Подскажите пожалуйста как правильно выставить опции при построении графика, если по оси Х значения в формате DateTime.
У меня отображается только по датам (( Подскажите что не так.
VBA. Запуск макроса в зависимости от времени.
 
Добрый день. В файле 4 запроса PQ. Их нужно обновить 4 раза в день. В 6, 12, 18 и 24 часа.
Макрос обновления запроса я нашел.
Код
Sub Refresh_One_Query()

ThisWorkbook.Connections("Запрос — Заказы1").Refresh

Workbooks.Application.DisplayAlerts = False
Excel.ActiveWorkbook.Save
Application.Quit

End Sub

Запуск в определенное время тоже нашел
Код
Application.OnTime TimeValue("06:01:00"), "Refresh_One_Query"  
Но это немного не то. Если файл запустился в 6-05 запрос не обновляется.

Помогите пожалуйста сделать чтобы макрос запускал макрос в зависимости от времени. Если время с 00-00 до 6-00 запускается обновление Запрос — Заказы1
если время с 6-00 до 12-00 запускается обновление Запрос — Заказы2 и т..д.
Изменено: kolyaya - 11.07.2020 18:58:51
Организация запросов power query
 
Подскажите пожалуйста  как правильно организовать запросы пока я в начале пути дров не наломал.
Есть одна большая таблица по 30 категориям.
Производится многощаговая обработка категорий, что бы избежать нехватки ресурсов в таком порядке:

1. Из общей таблицы выделяется категория и выгружается на лист.
2. Далее путем обработки 5 запросов формируется итоговая таблица.
3. Данные из всех 30 тоговых таблиц объеденяются в итоговый отчет.

Сейчас создал 30 книг, но столкнулся с проблемой если возникает необходимость изменений в запросе, нужно менять во всех 30 книгах ((
Если организовать все в одной книге как не запутаться в таком количестве запросов и как их обновлять?

Буду рад любым советам?
Обновление запросов PowerQuery по очереди.
 
Добрый день.
Есть ряд зависимых запросов (Запрос1, Запрос2, Запрос3)
Нужно что бы запросы обновлялись по очереди.
Запустился Запрос1, дождаться окончания обновления, обновить Запрос2, дождаться окончания обновления, обновить Запрос3
Помогите пожалуйста.
Динамический минимум на графике, Установка минимума в графике по формуле.
 
Еще раз здравствуйте.
Опять гугл мне не хочет помогать (( Такой вопрос:
На основании данных строится график. Минимум всегда больше 1, но часто удобно смотреть что бы минимум был на 0,5 меньше минимума в данных.
Есть ли возможность задать минимум формулой. При установке автоматически минимум выставляется в 0, а в ручную менять каждый раз не лучший вариант.
Подксажите пожалуйста.
Изменено: kolyaya - 01.07.2020 22:55:52
VBA макрос для перехода к следующему элементу списка
 
Помогите пожалуйста создать кнопку для переходу к следующему элементу списка.
Отфильтровать строки по id на отдельный лист
 
Здравствуйте.
Есть таблица с набором данных. Подскажите или помогите реализовать возможность отфильтровать строки по id на отдельный лист, что бы на основании отфильтрованных данных строился график и производились вычисления. И по возможности кнопку перехода на следующий ID. Что бы можно было листать как карточки.
Постарался отразить все в примере.
Расчет изменений цены для большой таблицы.
 
Здравствуйте.
Есть большая таблица более 1 млн.строк. поэтому работал в PQ
Попробовал высчитать изменение цены исключая колебания (если цена изменилась и вернулась обратно такое движение исключаем) при помощи объединения таблиц со сдвинутым индексом. Но получается не то что нужно (Съедается часть строк, а это потеря информации + ооооочень долго + на выходе все равно не те цифры (( ).
Решил попробовать перейти в Power Pivot (первый раз, до этого с ним еще не работал, поэтому сильно не ругайте).
Нашел там интересную функцию SUBSTITUTEWITHINDEX
Но не разобрался в синтексисе, а русскоязычные сайты примеров использования не дали.
Прошу вашей помощи.
Автоматическое создание отчетов с изменением внутри запроса Power Query
 
Здравствуйте. Есть отчет, который необходимо сформировать в Power Query для разных складов.
Для изменения склада приходиться заходить в запрос "склад" и менять настройки фильтра. После формировать отчет, копировать на новый лист и так для множества складов.
Для каждого отчета склада свой лист.
Можно ли как нибудь автоматизировать данный цикл?
Изменено: kolyaya - 01.05.2020 22:39:50
Расчет по датам при наличии динамических данных
 
Добрый день. Помогите пожалуйста. Есть таблица

ДатаМенеджерСуммаto.Колвоto.Сумма
01.01.2020Вася10000110000
01.01.2020Петя200003170000
03.01.2020Петя600003170000
02.01.2020Петя 30000130000
02.01.2020Юля400003170000
03.01.2020Юля500003170000
03.01.2020Коля50000270000
04.01.2020Коля20000270000
04.01.2020Петя900003170000
04.01.2020Юля800003170000
04.01.2020Ваня50000150000
04.01.2020Слава60000160000
Данные в столбце "to.Колво" и  "to.Сумма"расчитываются при запросе Power Query (динамические). Нужны актуальные данные суммы именно на дату анализа (В столбце дата)
Это можно сделать при помощи Power Query?
Или как я вижу при помощи VBA Перенести данные с последней даты на другой лист, обновить данные и так по кругу.
Буду рад любому варианту. Так как сам к сожалению не соображу ((
Группировка в PowerQuery последних значений
 
Здравствуйте, подскажите как можно сгруппировать не все строки, а определенное количество, например последние 10.  
Итоги с перебором значений
 
Добрый день. Возможно ли автоматизировать расчет по таблице для разных переменных, что бы не подставлять каждый раз переменную, а так что бы сразу считало для каждой выборки?
В примере в цене добавляем процент1 и процент2 которые могут изменяться, нужно получить сумму при каждом варианте.
Надеюсь понятно объяснил.
Разбить файл на отдельные для каждой даты
 
Привет всем. Есть таблица с данными  Дата - Данные. Нужно разбить файл что бы каждый день был в отдельном файле. Помогите пожалуйста. Я так понимаю только макросом такое можно сделать?
Фильтр в Power Query по списку
 
Добрый день, подскажите возможно ли сделать фильтр в power query по списку.
Я понимаю что можно прописать множество условий, но в задаче 100 +- позиций. И задач таких несколько :(
Подставить год к дате (в идеале в Power Query)
 
Помогите пожалуйста.
Есть 2 колонки в одной период в формате 18\19 год
В другой дата 01.02
Как по подрядку проставить сначала до 31.12 - 18 год, а потом 19.
Возможно ли в формате даты использовать дату без года, только число и месяц? (У меня только в текстовом формате записало :( )
Оставить первый и последний столбец Power query
 
Помогите пожалуйста оставить первый и последний столбец Power query, не трогая индекс (по факту второй, четвертый). Если с даннами только один столбец, продублировать его в последний см. строка 2.
В исходной таблице более 40 столбцов с разным количеством столбцов.
Страницы: 1 2 След.
Наверх