Многоуровневый заголовок средтвами Power Query, как собрать из многоуровневого заголовка нормальтную таблицу все знают - а что если обратно из таблицы сделать отчет с многоуровневой шапкой
Вопрос - у меня есть таблица с датой количеством и ценами, я хочу средствами PQ трансформировать ее в Таблицу с многоуровневым заголовком но возникает затруднение с названиями столбцов и трансформацией строки заголовка
буду благодарен за совет как это сделать в PQ
(про сводную понимаю, но хочется заморочитmся с PQ )
Добрый день, ранее на форуме обсуждали, как отвязать срез для скопированной таблицы от общего кэша, чтоб изменения на срезе 1 не влияли на срез 2. https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=128141&a.. я сейчас пытаюсь решить обратную задачу, чтоб получить для двух разных сводных таблиц с разным кэшем единый срез.
пока что дошел до того, что это будет макрос, который при изменении отбора на срезе 1 будет искать такие же значения на срезе 2 и ставить им отметку "True"
но в макросах я не силен, и настроить так чтоб макрос работал с двумя срезами я не сумел. прошу помощи в макросе или подсказку как можно решить этот задачу другим методом.
Power Query - определение функции обновляется при каждом изменении запроса, как настроить функцию в PQ, так чтобы она обновлялась при каждом изменении исходного запроса
Добрый день, В PQ, при загрузке файлов из папки, программа самостоятельно создает цепочку связанных запросов, параметр, пример обработки и функцию, которую будет применять к каждому загружаемому файлу. У этой функции по умолчанию есть очень интересный параметр - "определение этой функции обновляется при каждом изменении запроса". Иными словами эта функция связанна с примером обработки и, при каждом изменении примера обработки, аналогично изменится и наша функция.
Устанавливается этот параметр автоматически и последствии его можно отключить. Но вот, как наоборот его включить? Допустим связь между примером и функцией сбилась? Или я сам захотел связать этот пример с новой функцией? И, самый интересный вопрос, - а можно указывать пример обработки за пределами рабочей книги, например в отдельном файле?
Power Query открывает файлы источники без команды, сбои в работе запросов приводят к нарушению вычислений ETL и начинают самостоятельно открываться файлы источники
Добрый день, Проявилась неожиданная проблема при работе с запросами в Power Query. Суть проблемы - при вычислении запроса начинает тормозить компьютер.Нне может, ни просчитать обновление запроса, ни выгрузить результат. Далее, после появления сообщения об ошибке (предлагают послать нахмуренный смайлик), система начинает самостоятельно открывать файлы источники. При попытке их закрывать вручную, открытие повторяется. Открывает не все источники, а только относящиеся к последнему редактированному запросу - даже если в нем всего одна таблица в одну строку. Проблема плавающая причину вычислить пока не смог. Кто нибудь сталкивался с подобным? есть способы защиты от этой ошибки?
возник вопрос: существует ли какой либо способ измерить скорость обновления запроса в power query по аналогии с RangeTimer - макросом
сейчас все эмпирически происходит то есть написал - вроде работает потом добавил строчку начало обновляться дольше - хочется какой то инструмент для наглядного сравнения скорости вычислений
поделитесь опытом кто то что знает по данной теме )))))
Добрый день, модераторам и форумчанам. заметил при поиске интересное облако тегов над формой поиска [img]c:\YandexDisk\YandexDisk\%D0%A1%D0%BA%D1%80%D0%B8%D0%BD%D1%88%D0%BE%D1%82%D1%8B\2018-02-21_08-49-30.png[/img] вопрос оно там всегда было а я не пользовался? или это обновление удобное?
второй вопрос а принцип работы этих полей? отфильтровывает результат поиска по заданному тегу? а если по двум отфильтровать хочу можно как то это сделать?
начал настраивать под свой вариант, и сразу запнулся, дело в том что нарастающий итог то считает и даже отфильтровывает по признакам...но вот по датам считает все время с самого начала ряда - тоесть я отфильтровываю первую неделю от вычислений а сумма считается не от нуля а от накопленной величины к этому моменту.
самое близкое что я нашел это функция TOTALMTD() - соответсвенно нарастающий итог с начала месяца. или ее аналог:
при работе с массивами данных может потребоваться небольшая доработка исходных данных, в моем случае это защита от ввода различных вариантов (опечаток) при работе нескольких пользователей. суть доработки - замена части названий из исходного столбца на исправленные варианты написания (привел пример в файле) код для power query у меня вышел вот такой
Код
let
Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
#"Измененный тип" = Table.TransformColumnTypes(Источник,{{"№", Int64.Type}, {"Фирма", type text}, {"цифра", Int64.Type}}),
#"Объединенные запросы" = Table.NestedJoin(#"Измененный тип",{"Фирма"},Таблица3,{"исходное название"},"NewColumn",JoinKind.LeftOuter),
#"Развернутый элемент NewColumn" = Table.ExpandTableColumn(#"Объединенные запросы", "NewColumn", {"требуемое название"}, {"требуемое название"}),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Развернутый элемент NewColumn", "1", each if [требуемое название] = null then [Фирма] else [требуемое название]),
#"Переупорядоченные столбцы" = Table.ReorderColumns(#"Добавлен пользовательский объект",{"№", "1", "Фирма", "цифра", "требуемое название"}),
#"Переименованные столбцы" = Table.RenameColumns(#"Переупорядоченные столбцы",{{"1", "Фирма"}, {"Фирма", "удалить"}}),
#"Другие удаленные столбцы" = Table.SelectColumns(#"Переименованные столбцы",{"№", "Фирма", "цифра"})
in
#"Другие удаленные столбцы"
ИМХО, сложновато для такой простой операции вопрос можно ли эту же процедуру делать как быстрее или за меньшее число шагов?
VBA макрос для пересчета листа в зависимости от положения курсора на странице, для принудительного пересчета части книги в зависимости от действий оператора
Добрый день уважаемые кодеры, поддержите советом формулиста. есть желание сделать красивое решение но по VBA плаваю.
идея в том чтоб сделать небольшой помощник для заполнения данных человеком
суть работы как я представляю: при изменении позиции курсора на листе (только для этого листа) активируется макрос
макрос записывает положение курсора строка/столбец, передает переменные формулам и запускает пересчет для диапазона ячеек (а то файл большой компьютеры слабые ))) ) в результате получается область где динамически отфильтровываются нужные данные для нужного набора атрибутов/признаков
для ясности изложения приложил файл с примером как это может выглядеть.
Столкнулся с проблемой циклических ссылок в Power Pivot. суть проблемы расчет столбцов на начало и на конец - проблема в том что вычисления по одному столбцу за предыдущий период являются основой для расчета след периода, и PP выдает цикличную ссылку - не считает.
ВОПРОС: как можно перебороть цикличность? Исп другие формулы? Использовать другую цепочку вычислений? может кто то делал гибрид чтоб из PP приходили данные - вычислялись в Excell и передавались обратно в PP?
набросал легкий пример показывающий суть вычислений и возникающую проблему в PP
Заполнение пропусков в столбце с помощью DAX, запрос power query, для корректной работы сводных таблиц все данные должны иметь определенный набор признаков в столбцах, и в этих признаках не должно быть пропусков
добрый день уважаемые форумчане, в работе мы часто сталкиваемся с таблицами заполненными не полностью. например выгрузки 1С, или при использовании объединений в ячейках, в обрывках сводных таблиц и возникают вот такие ситуации с пропусками данных в столбце, когда все элементы расположенные ниже названия группы в столбце принадлежат одной группе но не имеют подписи.
в таблицах эксель это заполнить очень легко простой ф-лой = если(епусто(); ; )
но вот вопрос как реализовать эту же процедуру но на DAX чтоб Запрос power query научился этому приему проблема в том что запросы работают с данными столбца в целом и сослаться на предыдущие значение у меня не выходит (
кто нибудь знает решение данного вопроса? или хотя бы подскажите в каких местах искать ответ.
у меня следующая ситуация - есть книга эксель с 20 таблицами. эти таблицы собираются в общую модель данных с помощью запросов во внешней книге по модели данных строится сводная таблица если исходная книга эксель переименовывается или перемещается - то сбивается путь у запросов, соответственно не обновляется модель данных
для того чтобы изменить источник в конструкторе запросов есть простой механизм - но это занимает довольно много времени так как при изменении конструктор пытается пересчитать каждый запрос. в редакторе кода запроса можно менять источник целым блоком - но опять таки одна простая операция х20 повторений - долго , теряется смысл запросов
Вопрос кто то сталкивался с похожими проблемами? есть ли способы менять источник в запросах массово? или научить запрос автоматическому поиску файла в папке?
суть проблемы старая песня, при заполнении ячейки цифрами (например какой то код или номер)через точку или / эксель воспринимает ее как дату и форматирует при этом заменяя данные в ячейке на дату закодированную цифрой - это все знают
способ борьбы простой не сложный изменяем формат ячейки на текстовый или еще какой и радуемся ))
но 2013 решил не сдаваться так просто в моем случае я заменяю в большом массиве состоящем из примерно таких цифр 2_11_8, или 6_5_1 я заменяю "_" на точку "." эксель соответственно форматирует все как дату и меняет содержимое ячейки ( переформатировать ячейки перед заменой не спасает он все равно меняет все по своему. попробовал на 2010 все там в порядке если формат текстовый то эксель не своевольничает.
Добрый день Уважаемы Форумчане. нашел в 2013 Excell опцию сводной таблицы преобразовать в формулы OLAP которая в замен полей сводной таблицы автоматом настраивает формулы на массив данных то есть при нажатии вы имеете готовые формулы в таком виде: "=КУБЭЛЕМЕНТ("ThisWorkbookDataModel";"[Таблица4].[№].&[1]")" как я понимаю не смотря на весь внешний вид формул но это все же константа задается последним оператором "&[1]" вопрос можно ли это все до настроить до состояния умных таблиц и формул которые будут повторять действия сводной таблицы по группировке и фильтрации данных массива?
сейчас на ровном месте столкнулся с непонятной проблемой. коротко вся история: сделал выгрузку бюджетных данных по статьям более 20 тыс строк настроил итоговую таблицу с формулами "=суммеслимн" чтоб делать выборку прикрутил выбор месяца и региона для анализа через формы все работает, все красиво. отдал бухгалтерам появляются вопросы почему суммирует не те статьи? с меня на компе проблем нет все по прежнему считается. тогда попробовал перекинуть на соседний комп. и там в самом деле обнаружил такую проблему там где мой эксель уверенно показывает "0" у соседа вдруг выпадают какие то цифры. проверил выгрузку в таких статья цифр нет только нули а эксель упорно продолжает суммировать туда что то.
подскажите в чем может быть дело может какие ограничения формул или самого экселя? может еще какие то подводные камни?
столкнулся с такой проблемой: есть 1 большой расчетный файл с большим количеством листов и разнообразных расчетов. для введения входных данных широко применяются списки значений, все реализовано через именованные диапазоны, имена списков соответственно есть в диспетчере имен. теперь при вынесении какого либо листа в новую книгу вместе с листом переносятся и имена диапазонов, но выглядит это как то бессистемно переносятся не все имена существующие в книге, но и гораздо больше имен чем используется на листе. не знаете ли вы принцип по которому происходит выборка имен для новой книги? далее вопрос как побыстрее и поудобнее выяснить какие именно имена используются на данном листе? я пока нашел только команду /выделение группы ячеек - проверка данных , но так я вижу только где используется проверка данных но не вижу имен исп диапазонов, если диапазонов заданно много то это не удобно. также при обратном перемещение выдернутого листа все имена использованные в обеих книгах складываются и начинают дублировать друг друга, это легко лечится удалением дубликатов но хочется узнать вдруг есть способ получше.
с большой надеждой жду от вас советов и обмена личным опытом приемов работы в эксель
Добрый день, уважаемые форумчане. вот уже долгое время мучаюсь с одной формулой конечно всегда можно решить задачу с помощью доп вычислений но хочется научиться делать красиво в одну формулу.
итак есть исходная таблица данных в которой перечислены товары по одной номенклатуре, есть справочник в котором перечислены группы в которые входят товары. задача просуммировать исходную таблицу по признаку группы не вводя дополнительный столбец
Добрый день, есть условный двумерный массив по строкам этого массива производятся вычисления(любые - сложить, поделить, сцепить) и получается дополнительный столбец с данными затем данные из этого столбца разносятся по условию в итоговую таблицу результат.
вопрос как мне пропустить этап построения этого доп столбца и сделать все вычисления в виртуальном массиве?
начал копать форум на тему сабжа,информации нашел не очень много и довольно разрозненную, решил объединить для наглядности. [spoiler]
Цитата
"функции подразделяются на летучие и нелетучие. В подавляющем большинстве случаев движок пересчета Excel пытается считать исключительно то, что действительно нуждается в пересчете, экономя т.о. время затрачиваемое на расчеты. Excel ведет некий реестр, в котором помечаются ячейки/формулы, нуждающиеся в обновлении при ближайшем событии пересчета. Одним из исключений из этого правила являются формулы содержашие летучие функции. Ячейки содержашие такие формулы неизменно помечаются как нуждающиеся в обновлении при любых обстоятельствах. На сегодня (версии 2002-2007), летучими являются следующие функции: СЛЧИС() ТДАТА() СЕГОДНЯ() СМЕЩ() ЯЧЕЙКА() ДВССЫЛ() ИНФОРМ() Летучие функции делают летучими формулы их содержащие, а также от них зависящие (Dependencies). Само по себе понятие летучесть означает, что пересчет функции происходит при каждом событии пересчета на листе, независимо от того, влияют ли изменения вызвавшие данное событие, на результат формулы. Триггеров события пересчета достаточно, самые привычные из них следующие: - изменение значения или формулы в ячейке (в т.ч. при использовании Мастера Функций и клавиши F9) - открытие файла сохраненного в более старой версии - открытие файла с выбранной опцией пересчета при открытии - изменение критерия автофильтра - удаление и вставка строк и столбцов - добавление, изменение и удаление именованных формул (в т.ч. и именованных диапазонов как частного случая именованных формул) - изменение имен или порядка расположения рабочих листов - удаление рабочих листов (но не добавление) - скрытие и показ строк (в XL2003) - открытие файлов с расширением *.csv Все это и многое другое можно прочитать здесь: http://www.decisionmodels.com/calcsecretsi.htm http://www.decisionmodels.com/calcsecretsb.htm "
вот хороший макрос с счетчиком времени пересчета формул: <SPOILER>
Код
Private Declare Function getFrequency Lib "kernel32" _Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Function MicroTimer() As Double
'
' Returns seconds.
Dim cyTicks1 As Currency
Static cyFrequency As Currency
'
MicroTimer = 0
' Get frequency.
If cyFrequency = 0 Then getFrequency cyFrequency
' Get ticks.
getTickCount cyTicks1
' Seconds
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function
Sub RangeTimer()
DoCalcTimer 1
End Sub
Sub SheetTimer()
DoCalcTimer 2
End Sub
Sub RecalcTimer()
DoCalcTimer 3
End Sub
Sub FullcalcTimer()
DoCalcTimer 4
End Sub
Sub DoCalcTimer(jMethod As Long)
Dim dTime As Double
Dim dOvhd As Double
Dim oRng As Range
Dim oCell As Range
Dim oArrRange As Range
Dim sCalcType As String
Dim lCalcSave As Long
Dim bIterSave As Boolean
'
On Error GoTo Errhandl
' Initialize
dTime = MicroTimer
' Save calculation settings.
lCalcSave = Application.Calculation
bIterSave = Application.Iteration
If Application.Calculation <> xlCalculationManual Then
Application.Calculation = xlCalculationManual
End If
Select Case jMethod
Case 1
' Switch off iteration.
If Application.Iteration <> False Then
Application.Iteration = False
End If
' Max is used range.
If Selection.Count > 1000 Then
Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
Else
Set oRng = Selection
End If
' Include array cells outside selection.
For Each oCell In oRng
If oCell.HasArray Then
If oArrRange Is Nothing Then
Set oArrRange = oCell.CurrentArray
End If
If Intersect(oCell, oArrRange) Is Nothing Then
Set oArrRange = oCell.CurrentArray
Set oRng = Union(oRng, oArrRange)
End If
End If
Next oCell
sCalcType = "Calculate " & CStr(oRng.Count) & _
" Cell(s) in Selected Range: "
Case 2
sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
Case 3
sCalcType = "Recalculate open workbooks: "
Case 4
sCalcType = "Full Calculate open workbooks: "
End Select
' Get start time.
dTime = MicroTimer
Select Case jMethod
Case 1
If Val(Application.Version) >= 12 Then
oRng.CalculateRowMajorOrder
Else
oRng.Calculate
End If
Case 2
ActiveSheet.Calculate
Case 3
Application.Calculate
Case 4
Application.CalculateFull
End Select
' Calculate duration.
dTime = MicroTimer - dTime
On Error GoTo 0
dTime = Round(dTime, 5)
MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
vbOKOnly + vbInformation, "CalcTimer"
Finish:
' Restore calculation settings.
If Application.Calculation <> lCalcSave Then
Application.Calculation = lCalcSave
End If
If Application.Iteration <> bIterSave Then
Application.Calculation = bIterSave
End If
Exit Sub
Errhandl:
On Error GoTo 0
MsgBox "Unable to Calculate " & sCalcType, _
vbOKOnly + vbCritical, "CalcTimer"
GoTo Finish
End Sub
у меня есть одна формула которая производит вычисления в виртуальных массивах: {=МАКС(МУМНОЖ((СТРОКА(ИНДЕКС(A:A;МИН(Техника!$L$7:$M$1554)):ИНДЕКС(A:A;МАКС(Техника!$L$7:$M$1554)))>=ТРАНСП(Техника!$L$7:$L$1554))*(СТРОКА(ИНДЕКС(A:A;МИН(Техника!$L$7:$M$1554)):ИНДЕКС(A:A;МАКС(Техника!$L$7:$M$1554)))<=ТРАНСП(Техника!$M$7:$M$1554));(--(Техника!$AB$7:$AB$1554)*(Техника!$P$7:$P$1554=Проверка!C6))))+МАКС(МУМНОЖ((СТРОКА(ИНДЕКС(A:A;МИН(Техника!$L$7:$M$1554)):ИНДЕКС(A:A;МАКС(Техника!$L$7:$M$1554)))>=ТРАНСП(Техника!$L$7:$L$1554))*(СТРОКА(ИНДЕКС(A:A;МИН(Техника!$L$7:$M$1554)):ИНДЕКС(A:A;МАКС(Техника!$L$7:$M$1554)))<=ТРАНСП(Техника!$M$7:$M$1554));(--(Техника!$AB$7:$AB$1554)*(Техника!$R$7:$R$1554=Проверка!E6)*(Техника!$R$7:$R$1554<>0))))+МАКС(МУМНОЖ((СТРОКА(ИНДЕКС(A:A;МИН(Техника!$L$7:$M$1554)):ИНДЕКС(A:A;МАКС(Техника!$L$7:$M$1554)))>=ТРАНСП(Техника!$L$7:$L$1554))*(СТРОКА(ИНДЕКС(A:A;МИН(Техника!$L$7:$M$1554)):ИНДЕКС(A:A;МАКС(Техника!$L$7:$M$1554)))<=ТРАНСП(Техника!$M$7:$M$1554));(--(Техника!$AB$7:$AB$1554)*(Техника!$S$7:$S$1554=Проверка!E6)*(Техника!$S$7:$S$1554<>0))))}
формула работает,. но эксель тормозит и периодически пишет что не хватает ресурсов для расчета. в расчете есть повторяющийся массив СТРОКА(ИНДЕКС(A:A;МИН(Техника!$L$7:$M$1554)):ИНДЕКС(A:A;МАКС(Техника!$L$7:$M$1554)))>=ТРАНСП(Техника!$L$7:$L$1554))*(СТРОКА(ИНДЕКС(A:A;МИН(Техника!$L$7:$M$1554)):ИНДЕКС(A:A;МАКС(Техника!$L$7:$M$1554)))<=ТРАНСП(Техника!$M$7:$M$1554))
для ускорения пересчета я хотел бы вынести этот массив в отдельную ячейку/столбец/лист чтоб он вычислялся один раз и затем остальные ячейки ссылались на него.
подскажите как это сделать, пожалуйста. а то что у меня мысль по кругу ходит и пока безрезультатно
уважаемые форумчане подскажите как с точки зрения скорости пересчета книги эксель и "веса" файла лучше поступать: - использовать в одной ячейке сложную формулу которая сама строит массивы высчитывает средние величины и ограничения и совершает иные действия с этими массивами, - или же лучше упростить формулу введя вспомогательный столбец с промежуточными расчетами на который впоследствии будут ссылаться формулы?
P.s. а что скажете про использование имен в формулах? тормозят ли они вычисления? или может утяжеляют файл?
Есть даты начала и конца интервала, в табличном виде:
начало
конец
число дней
количество машин
1
11.09.2013
26.09.2013
15
11
2
22.07.2013
03.08.2013
12
7
3
12.09.2013
11.10.2013
29
9
4
20.09.2013
23.09.2013
3
14
5
01.10.2013
08.10.2013
7
9
6
12.10.2013
23.10.2013
11
11
7
09.10.2013
28.10.2013
19
15
8
13.10.2013
30.10.2013
17
9
интервалы разной протяженности и разной интенсивности нагрузки (количество машин) задача найти периоды максимальной нагрузки при помощи формул в файле мои соображения и подробности