Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1
Пожелания по увеличению функциональности Plex
 
Николай, добрый вечер!

Было бы здорово добавить в Plex к функции "Разорвать все связи" или к "Очистка книги" дополнительную возможность удаления всех подключений из вкладки "Данные->Подключения" (Excel 2013), включая модель данных и удаления всех Queries созданых в PowerQuery, да и вообще удаление любых связей в книге с внешними источниками.

Это часто требуется, когда из книги нужно сделать вариант для пересылки и чтобы нигде не оставалось путей и ссылок, откуда взята информация.

Будем ждать такого в новых версиях!

Спасибо!
Привести данные из 1С в нужный вид для сводной в ексель
 
Раньше тоже мучился с группировками в 1С, а оказывается их просто в настройках отчета 1С нужно убрать. А те поля что там были выбрать в "Дополнительные поля", размещение - "в отдельнЫХ колонках" и не забыть поставить галку "Выводить детальные записи".
Группировок вообще не должно быть!!!
Приложил скрин из 1С окна настроек стандартного отчета. Чтобы увидеть закладку "Дополнительные поля", нужно поставить галку "Расширенная настройка".

Также после сохранения такого отчета в формате .xlsx, нужно данный файл открыть в Excel и, ничего не меняя, просто сохранить в самом Excel (лучше 2 раза). Можно даже увидеть, что размер файла изменится. У меня PQ без данной операции не проглатывает 1С-кие отчеты.
Округление вверх до значения 90 в формуле DAX и в Power Query
 
DAX - это язык надстройки PowerPivot и приложения PowerBI.

У PowerQuery язык "M". Тема не соответствует запросу.
Сложение нескольких строк в одну ячейку по условию в Power Query
 
Вот блин я заморочился:

Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],

// Группируем по Число и Коду и AllRows по значению

    GroupedRows = Table.Group(Source, {"Число", "Код"}, {{"Count", each _, type table}}), 

// добавляем внутри таблиц колонки Count Индекс от 0. Он нам понадобиться для создания динамичного листа названия колонок, 
// а также для шага PivotedColumn

    AddedCustom = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([Count], "Index", 0, 1)),
    RemovedColumns = Table.RemoveColumns(AddedCustom,{"Count"}),
    ExpandedCustom = Table.ExpandTableColumn(RemovedColumns, "Custom", {"Значение", "Index"}, {"Значение", "Index"}),

// Эти 3 строчки кода ниже нам создают динамичный лист названия колонок для объединения данных через запятную для последнего шага MergedColumns. 
// Проблема при объединении колонок - это то, что их названия прописываются как говорится Hardcoded. Т.е. если на текущий момент наш запрос сработает, 
// то завтра данные обновятся и колонок станет больше, но они в шаг MergedColumns уже бы не попали.

    ChangeFutureListToText = Table.TransformColumnTypes(ExpandedCustom,{{"Index", type text}}),    // важно чтобы Лист создавался из Text type
    CustomIndex = ChangeFutureListToText[Index],    // создание листа. Есть кнопка в интерфейсе
    List = List.Distinct(CustomIndex), // убираем дубликаты

// Выделяем колонку Index и делаем Pivot по колонке Значение и выбираем в Advance - Dont Aggregate
    PivotedColumn = Table.Pivot(Table.TransformColumnTypes(ExpandedCustom, {{"Index", type text}}, "ru-RU"), 
                       List.Distinct(Table.TransformColumnTypes(ExpandedCustom, {{"Index", type text}}, "ru-RU")[Index]), "Index", "Значение"),

// Выделяем колонки от 0 и дальше и соединяем их через ",". По умолчанию (по примеру), там где List в шаге MergedColumns , 
// было бы {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, т.е. жестко прописаны (Hardcoded) названия колонок, а если бы их стало больше,
// то они бы не попали в объединение. Но благодаря выше 3-м шагам мы создали динамичный лист названия колонок

    MergedColumns = Table.CombineColumns(PivotedColumn,  List ,Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
in
    MergedColumns


а можно было проще:

Цитата
Максим Зеленский написал:
Код ? 1= Table.Group(#"Changed Type", {"Столбец2"}, {{"Concatenated", each Text.Combine(List.Transform(_[Столбец3

ХА!  :D
Консолидация данных клиента
 
Очень просто делается в надстройке PowerQuery для Excel 2010/2013 (уже встроенная в Excel 2016 и зовется Get and Transform).
https://www.microsoft.com/en-us/download/details.aspx?id=39379

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

Потратьте время на узнавание как она работает. Для таких задач она просто создана и не надо никаких макросов.
На основе данных - разделить и создать ссылку
 
Пример PowerQuery. Там это тоже очень просто. Там 2 листа: вариант 1 и вариант 2. Можно хоть так, хоть так настраивать обработки.
Конструктор получения данных
 
Цитата
Дмитрий Щербаков написал: ну это дезинформация.
Не буду спорить, просто столкнулся на работе с тем, что сотруднику поставили надстройку, у него был Excel 2013 Standard, я же работаю в PRO. Мне нужно было ему показать какое-то действие в этой надстройке. И когда сел показывать увидел, что не все коннекторы были. Все кнопки не проверял, но показалось, что и не все кнопки преобразования были в интерфейсе.

Ну, значит ошибся.
Конструктор получения данных
 
Цитата
БМВ написал: PowerQuery - доступна и для стандарта, а вот PowerPivot только в про.
Это да, но функционал будет не полный, и на сколько я понял в плане именно интерфейса не все кнопки будут. А вот с точки зрения языка запросов M, ограничений думаю нет.

Возможно пользователям для уже настроенного запроса, где они будут только обновлять хватит и для стандарта. А вот Вам Ings как создателю запросов желательно запускать из Pro.
Изменено: VasiliePavlov - 10 апр 2018 21:37:41
Конструктор получения данных
 
Как вариант PowerQuery - очень крутая штука и доступна всем. Но нужна Pro версия Excel, чтобы все функции работали. Универсальна для 2010/2013/2016(встроена)/PowerBI(встроена).

https://www.youtube.com/watch?v=8IWrtwMb03s
C 1:57 по 5:20 как подключиться к SQL database. На английском, но там всё сопровождается видеорядом, что куда нажимать и вводить.

https://www.youtube.com/watch?v=gK2yBpiITvI&index=14&list=PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQd­iK&t=2s
Файл пример Start
Файл пример Finish

Тоже на английском, и то же всё наглядно. Как из с листа Ексель можно задать параметры, которые будет обрабатывать PowerQuery при создании запроса к базе данных. Т.е. данный параметр пользователь может задавать сам прямо в книге. Главное, чтобы вы настроили его обработку в PowerQuery.

Надеюсь разберетесь.
"Слетает" формула автоматического подсчета срок в умной таблице.
 
а если вообще через другую формулу?
Код
=ЕСЛИ([@ПОСТАВЩИК]<>"";СТРОКА()-2;"")

"-2" - чтобы учитывало количество строк от начала листа до строки с формулой.
Ваша же таблица не подразумевает пустых строк между данными?
объединение столбцов с повторяющимися названиями в PowerQuery
 
И кстати если зайти по ссылке от Максима и изучить именно 4-й способ (это где использование команды Record.FromList) можно играться и с 3 и 4 и т.д. повторяющимся группами столбцов. Например может повторяться (2) Дата - Количество, а может (3) Дата – Сумма - ДатаСерт, или (4) Дата – Сумма - ДатаСерт - СуммаРезерв и т.д.

Шаги в статье такие:

1.      Promote headers
2.      Unpivot al columns except common columns. Now our repeating columns becomes repeating groups of rows.
3.      Add “Index” column
4.      Transform “Index” column with use of Number.IntegerDivide function. This can be done manually or with UI: Select “Index” column, go to Transform – From Number – Standard – Divide (Integer). Our goal is to get whole number of “Index”/2 (as we have two repeating columns).

Если повторяется 3 названия то “Index”/3, если 4 - “Index”/4 и т.д.

5.      Now each pair of row has the same corresponding number.
6.      And here is some magic: now we’ll group rows by this modified “Index”, but as an aggregate function we’ll use non-standard Records.FromList function. It is not in list of functions available in dialog window, so it should be entered manually or by editing of other aggregate function code:

2 столбца:
Код
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Имя", "Заказ", "Index"}, {{"Значения", each Record.FromList([Value], {"Дата","Сумма"}), type record}}),

3 столбца:
Код
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Имя", "Заказ", "Index"}, {{"Значения", each Record.FromList([Value], {"Дата","Сумма","ДатаСерт"}), type record}}),

4 столбца:
Код
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Имя", "Заказ", "Index"}, {{"Значения", each Record.FromList([Value], {"Дата","Сумма","ДатаСерт","СуммаРезерв"}), type record}}),


Т.е. просто дописываются названия новых повторяющихся столбцов.

7.     Now we can remove “Index” and expand “Values” column by clicking on double arrow right to column name, and we got the table we need!
8.     Make sure all data has correct type assigned, close & load.


Прикрепил файл с примерами.
Изменено: VasiliePavlov - 2 фев 2017 13:47:12
Когда ждать версии на 64 бита?
 
На работе стоит Windows 7 Профессиональная 64-разрядная и офис Про+ 2013 тоже 64-разрядный. Plex работает, ошибки не выскакивают.
Учет заказов по нескольким маршрутам на ОДНУ дату в расходе по остатку на складе
 
СУММЕСЛИ, ё моё. Сам же применял и сам же не подумал, что и здесь её можно использовать.
Макрос бы точно сам не придумал.

Применил к реальным данным, всё вроде подхватило и заработало. Дополнительных лагов обработки файла вроде не видно.
Спасибо!
Учет заказов по нескольким маршрутам на ОДНУ дату в расходе по остатку на складе
 
Добрый день,

Создал для себя инструмент планирования остатков и маршрутов в зависимости от наличия остатков на складе на дату, прихода/расхода на дату, с учетом проставления дат по маршрутам по разным товарам по разным заказам. Всё это на основе 3-х таблиц.
Но не могу сделать, чтобы я мог проставить на одну дату несколько маршрутов и при этом в таблице учета остатков эти маршруты правильно отображались и суммировали расход на дату.
Конечно бы хотелось через стандартные функции это можно было сделать, но если предложите макрос - думаю разберусь.

Excel 2010/2013 Pro Plus.
Пример приложил.
Вычленить текст из ячейки
 
А может кто-то сбросить ссылку на хорошее описание параметров/значений ".pattern=" ?

Вот например нашел для себя вытаскивание подряд идущих шести любых цифр параметром "\d{6}", это нужно было, когда в середине текста содержался индекс, слева название компании, справа адрес. И нужно было всё это разнести по разным колонкам. Вот вытащив индекс, это стало легко:
Код
[/CODE]Sub ExtNumbers6()
' извлечение нужного количества подряд идущих цифр
Dim c As Range, x
On Error Resume Next
With CreateObject(&quot;vbscript.regexp&quot;)
    .Pattern = &quot;\d{6}&quot; '6 цифр
    For Each c In Selection.Cells
        c.Value = .Execute(c.Value)(0)
    Next
End With
End Sub

[CODE]
Изменено: VasiliePavlov - 1 дек 2015 11:03:28
Ошибка функции VLOOKUP2, символ "[" в тексте выдает ошибку
 
Век учись, век учись! Спасибо =)
Ошибка функции VLOOKUP2, символ "[" в тексте выдает ошибку
 
Понятно. Спасибо.
А ведь теперь, зная это, можно хоть так, хоть так =)

Ну может еще вдогонку скажете, чем же этот символ "[" такой особенный, что Like его не воспринимает? Или в принципе таких символов гораздо больше?
Ошибка функции VLOOKUP2, символ "[" в тексте выдает ошибку
 
ДА! Сработало! =) А если не секрет, что может собой повлечь изменение Like на = ? Т.е. при данном тексте - исправило, а при другом - наоборот? Или функция стала в целом универсальней?
Ошибка функции VLOOKUP2, символ "[" в тексте выдает ошибку
 
Ну так, между прочим, просто хотел узнать, получиться исправить это?
Ошибка функции VLOOKUP2, символ "[" в тексте выдает ошибку
 
Здравствуйте,

Только обрадовался функции VLOOKUP2, что может подхватить значения, где больше 255 символов, как тут же мне выдал ошибку если в тексте есть открывающаяся квадратная скобка "[". Файл прикрепил.

Выручайте! =) Очень нужна эта функция. Удалить кругом скобки не могу, т.к. работаю с разными отчетами и периодически они обновляются.

Прошу помочь.
Страницы: 1
Наверх