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

Страницы: 1 2 3 4 5 6 7 8 След.
Тестовое задание по Excel на должность аналитика
 
Думаю, тесты на должность аналитика обязательно должны включать тесты на знание PowerQuery, PowerBi и немного по части БД.

Так же имхо, тест должен быть профильным - пусть из реального массива данных получит что вам нужно.
Изменено: Alexey_Spb - 18 Апр 2019 08:59:00
Как изменить формулу чтоб добавить букву Q в расчет квартала в Power query
 
Цитата
Salta-301 написал:
Это формула вытаскивания кварталов в расчетном столбце:
P.S. Оригинальную формулу не анализировал, просто на основании предыдущего поста вы можетие получить желаемое так:
Код
= "Q" & Text.From(Date.From("1-" & [Attribute] & Text.From([Year]), "ru-RU"))
P.S. Только еще тип создаваемого столбца проверьте, а то мало ли (должно быть text)
Изменено: Alexey_Spb - 4 Апр 2019 10:39:16
Макрос сортировки по дате в виде чч:мм:гг
 
Вариант на PQ - быстро и удобно (Data -> Refresh all)
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Types = Table.TransformColumnTypes(Source,{{"Time", type time}}),
    Grp = Table.Group(Types, {"ID"}, {{"Maximum", each List.Max([Time]), type time}})
in
    Grp
Не вошедшие значения в ВПР
 
Надо использовать PQ
Выпадающий список зависимый от значения ячейки
 
Меня, наверное, закидают тапками, но я бы сначала через VLOOKUP (ВПР) обратился бы к таблице, в первом столбце которой - ключи "ДуXX", а во второй - диапазоны данных в виде текста, например "A100:A110". А уже эти диапазоны преобразовывать в список через INDIRECT (ДВССЫЛ).
Поиск нечетких совпадений в ячейке
 
Я бы делал такое на PQ и не мучался.
Условное форматирование в умной таблице. Сохранять при добалении строки
 
Умная таблица - это попытка Microsoft хотя бы немного приструнить разгулявшихся пользователей и убрать дурные вещи, откровенно нарушающие принцип 1NF, как, например, УФ или объединение ячеек. Учитесь вместо УФ пользоваться фильтрами.
Изменено: Alexey_Spb - 25 Мар 2019 18:14:52
Поиск нечетких совпадений в ячейке
 
Я ничего не понял.. Можно привести пример что есть на входе и что должно получиться в результате, а так же алгорим преобразования?
Поиск необходимых данных в огромном массиве и отображение результата в виде таблицы
 
Проверил на такой таблице из миллиона строк. Выполнение основного запроса заняло 50 секунд.
Код
let
    Source = List.Transform({0..10}, each "Магазин " & Text.From(_)),
    Max = List.Count(Source) - 1,
    Tt = Table.FromRows(List.Transform(List.Random(1000000), each {Number.Round(_, 3) * 10000000, Source{Number.Round(Number.RandomBetween(0, Max), 0)}}), {"Код товара", "Магазин"})
in
   Tt

Сводная на миллионе строк сводная обновилась за 10 секунд :)  Ну очевидно же что схожие операции сводная выполнит быстрее.

Правда у сводной будет две проблемы - похабный вид данных (в ячейках суммы). Наверное, решается через Custom-столбец, но не проверял сколько времени в этом случае займет обновление. Ну и второе - из 10 файлов данные тянуть.
Изменено: Alexey_Spb - 22 Мар 2019 17:57:56
Поиск необходимых данных в огромном массиве и отображение результата в виде таблицы
 
Предполагаю что это один из самых быстрых способов.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    // Группируем таблицу по коду товара
    Grp = Table.Group
           (
            // На таких объемах данных буферизация - наше все имхо
            Table.Buffer(Source), 
            {"Код товара"}, 
            {
             // Добавляя столбец Data, в ячейках которого будут содержаться таблицы
             // с колонками, соответствующими именам магазинов где есть данный товар 
             // и единственной строкой, содержащей во всех колонках "x".
             {
 
              "Data", 
              each Table.Buffer
                    (
                     // Нестандартное использование Pivot - вместо агрегации данных 
                     // ставим значение "x" во все колонки (имена магазинов, в которых
                     // есть группируемый товар.
                     Table.Pivot(_, List.Distinct([Магазин]), "Магазин", "Код товара", each "x")
                    )
             }
            }
           ),

    // Это получение уникального сортированного списка всех магазинов для использования в качестве 
    // имен колонок в готовой таблице.
    // Я создаю его, преобразовывая столбец Data, каждая ячейка которого является таблицей,
    // в столбец, содержащий список имен колонок таблицы в ячейке. А дальше путем слияния
    // списка списков (мутировавший столбец [Data] я получаю все возможные варианты имен магазинов.
    List = List.Sort
            (
             List.Distinct
              (

               // Как вариант - Вместо List.Combine использовать List.Buffer(Source[Магазин]).   
               // То есть тупо вычислить уникальные комбинации из колонки Магазин исходной таблицы
               // Вполне может быть быстрее..

               List.Combine
                (
                 Table.TransformColumns(Grp, {{"Data", each Table.ColumnNames(_)}})[Data]
                )

              ), 
             Order.Ascending
            ),

    // А дальше просто разворачиваем столбец Data по полному списку магазинов.
    // В тех строках, где встречаются имена колонок, которые есть в в List,
    // выставляется значение "x", которое там хранится. Если нет (то есть товара
    // не было в магазине - то null)
    Expanded = Table.ExpandTableColumn(Grp, "Data", List)

in
    // В итоге получаем результат.
    Expanded

   
Изменено: Alexey_Spb - 22 Мар 2019 17:17:09
Расчет вместимости паллет в фуру, Сколькой войдет паллет разного размера
 
Имхо, автору тему надо создать в платных заказах. Но есть подозрение что он это не сделает  :)  
Поиск необходимых данных в огромном массиве и отображение результата в виде таблицы
 
Цитата
Dark1589 написал:
но фильтровать артикулы лучше всё-равно заранее

В данном случае не думаю что PQ будет иметь преимущества по скорости перед сводной. Так же считаю что вариант Dark1589 не самый оптимальный с точки зрения скорости.
Изменено: Alexey_Spb - 22 Мар 2019 16:23:15
Поиск необходимых данных в огромном массиве и отображение результата в виде таблицы
 
Цитата
Stics написал:
Alexey_Spb , по 10 млн строк?

Пришлите пример с 10 млн. строк на a l е x е y _ b h (собако) майл.руъ
Изменено: Alexey_Spb - 22 Мар 2019 16:21:27
Поиск необходимых данных в огромном массиве и отображение результата в виде таблицы
 
Имхо, тут PQ не нужен.. задача элементарно решается сводной таблицей.
Сопоставить одинаковые значения из двух перемешанных столбцов
 
Цитата
alex_bro написал: Скрин из эксель -  http://prntscr.com/n1dy3s
В PowerQuery алгоритм решения составляется за минуты.
Каков вопрос (без примера), таков и ответ.
Power Query объединение запросов с условием промежутка между датами
 
Цитата
vinmax написал:
но не понимаю как это сделать.
Приведите нормальный рабочий пример.

P.S. Я бы копал в сторону этого:
Код
Table.Join(
           table1 as table, 
           key1 as any, 
           table2 as table, 
           key2 as any, 
           optional joinKind as nullable number, 
           optional joinAlgorithm as nullable number, 
           optional keyEqualityComparers as nullable list // <--- Есть подозрение что это список по числу ключевых колонок и сюда можно засобачить самодельный Comparer 
          ) as table
К сожалению ,у майкрософта документацию на эту тему никакой, гугл тоже ничего особо не находит.
Изменено: Alexey_Spb - 22 Мар 2019 15:37:13
Power Query объединение запросов с условием промежутка между датами
 
Вы бы пример нормальный привели, а не фуфло.

1. SAPCode двух таблиц имеют какую-либо связь?
2. Что будет если одна Date попадает сразу в несколько интервалов?
PQ значения из одного столбца перенести в три столбца согласно индексу
 
Цитата
Андрей Лящук написал: Еще вариант
Мне это чем-то напомнило https://www.ioccc.org  :)
Код
char O,o[];main(l){for(;~l;O||puts(o))O=(O[o]=
~(l=getchar())?4<(4^l>>5)?l:46:0)?-~O&printf("%02x ",l)*5:!O;}

Ну или вот:
Цитата
В 2004 году победителем конкурса стала графическая многозадачная операционная система с поддержкой клавиатуры, мыши, примитивной оконной подсистемой, поддержкой файловой системы, возможностью запускать ELF-программы. В поставку системы входит просмотрщик текстовых файлов, а также рудиментарный командный интерпретатор. Исходный код системы вместе с приложениями имеет размер около 3,5 килобайт.
http://www0.us.ioccc.org/2004/gavin.c
Изменено: Alexey_Spb - 21 Мар 2019 23:32:47
Как запретить форматирование ячеек, строк и столбцов в power querry?
 
Есть вариант спрятать лист с выходной таблицей PQ, а на нужный лист подтягивать данные формулами. На этом листе, видном пользователю, уже запретить форматирование.

Способ не подходит для больших объемов выходных данных (тысячи строк и более). В этом случае можно делать обновлени PQ через VBA с копированием на нужный лист.
Пересечение диапазонов дат в пределах строки, Пересечение дат в строке
 
Мучаем List.Intersect на PQ.. Но, наверное формулами в данном случае проще  :)

Скрипт очень медленный из-за наличия TransformColumns и генерации списков с List.Intersect.  На моем ноуте скорость ~200 строк в секунду для 100 тыс. записей по 5 пар колонок. Но он и не рассчитан на большие объемы данных, скорее было интересно придумать что-нибудь этакое.


Код
    // Готовые списки чтобы каждый раз руками не забивать.
    Unpf = {"УНПФ"}, RegL = {"регистрация", "ликвидация"},

    // Исходная таблица
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    // Убираем УПНФ из списка заголовков
    List = List.Difference(Table.ColumnNames(Source), Unpf), 

    // И для этого списка столбцов преобразуем колонки в числовое представление дат.
    // установить для колонки тип number в данном случае - бесполезно, не переварит.
    Type = Table.TransformColumns(Source, List.Transform(List, (Item) => {Item, each Number.From(Date.From(_))})),

    // Таблица создается путем группировки по трем основным колонкам (упнф, региистрация, ликвидация)
    // Добавляемые столбцы генерируются путем трансформации половинного списка индексов (так как колонки прием/увольнение идут всегда парами)
    Grp = Table.Group
           (
            Type, 
            Unpf & RegL, 
            List.Transform
             (

              // Список индексов 0.. до половины длины списка доп. колонок
              {0..Number.RoundDown(List.Count(List.Difference(List, RegL)) / 2) - 1}, 

               // В качестве каждого элемента будущего списка будет списочек вида {имя столбца, функция_генерации_значений_столбца}
              (Id) => {
                       // Имя стобца
                       "Диапазон " & Text.From(Id + 1), 
                       // Функция генерации значений
                       each 
                        let
                          // Так как в таблице-аргументе всегда одна строка в данном случае.
                          Row = _{0}
                        in
                          // Нужно для отлова null в исходных данных
                          try
                            // Признак - пересечение списков дат не пустое.
                            not List.IsEmpty
                                 (
                                  List.Intersect
                                   (
                                    {
                                     {Row[регистрация].. Row[ликвидация]}, 
                                      // Обращение к столбцу по его имени через индекс в том 
                                      // самом "половинном" списке индексов.
                                     {Record.Field(Row, List{Id*2})..Record.Field(Row, List{Id*2 + 1})}
                                    }
                                   )
                                 )
                          otherwise null
                      }
             )
           ),
    // Приведение чисел в даты для двух основных столбцов
    CType = Table.TransformColumnTypes(Grp,{{"регистрация", type date}, {"ликвидация", type date}})
in
    CType
Изменено: Alexey_Spb - 19 Мар 2019 16:34:00
PQ: Формат даты при загрузке в таблицу на лист
 
Есть такая тема.. Можно в PQ преобразовывать дату в строку, это имхо лучше постоянных проблем с форматом ячеек.
Посчитать сколько сотрудников выполнили план по нескольким продуктам
 
Цитата
belogorskayata написал:
но я думала немного в другом виде, в сводном
Рад бы вам помочь, да у вас в файле гомосяцкая форма представления данных (таблица с обьединенными ячейками).

За такое надо больно бить по рукам! Сделайте нормальную таблицу (1NF-подобную), просто лень тратить кучу усилий на ее приведение в приличный вид.
Разбить текст на строки указанной длины без разрыва слов
 
Вариант функции на PQ. Была такая в моем конвертере таблиц в автокадовские спецификации по ГОСТ (кто знает тот поймет какой это трэш).

Алгоритм тупой так как надо разбивать не по числу символов, а по суммарным их весам (ввиду того что не для моноширинных шрифтов разные символы имеют разный размер). В связи с тем что уже давно не работаю с ACad, новая версия генератора спецификаций по ГОСТ осталась недопиленной.

Код
   Text.CrLfSymbols = {" ",",",":",";","-","/","*","\","!","?","[","]","{","}","~","'","|"},

   Text.SplitSmart = 
    (
     InputText as nullable text,    // Входной текст
     SegmentMin as number,          // Минимальное число символов, функция не разбивает строку до этого числа символов
     SegmentMax as number           // Максимальное число символов. Если функция не найдет символа для переноса от SegmentMin до SegMentMax, перенос будет произведен по SegmentMax.
    ) as list =>            
       let
        Len = if InputText = null then 0 else Text.Length(InputText),
        Out = if Len > SegmentMax then
         let
          Pos_A = Text.PositionOfAny(Text.Range(InputText, SegmentMin, SegmentMax - SegmentMin), Text.CrLfSymbols, Occurrence.Last),
          Pos_B = if Pos_A < 0 then SegmentMax else SegmentMin + Pos_A + 1,
          Txt_A = Text.Start(InputText, Pos_B),
          Txt_B = Text.End(InputText, Len - Pos_B)
         in List.Combine({{Txt_A}, @Text.SplitSmart(Txt_B, SegmentMin, SegmentMax)}) 
        else
         {InputText}
       in Out,
Изменено: Alexey_Spb - 19 Мар 2019 09:20:12
PQ преобразование txt файла
 
Новая, все должно быть в порядке.

Но я тоже сталкивался с тем что не все функции есть и не все в таком виде как написано в мануале.  
Изменено: Alexey_Spb - 19 Мар 2019 07:50:36
Посчитать сколько человек сделало покупку по 2 условиям
 
Цитата
Processor написал:
Alexey_Spb , если есть цифра то учитывать, если 0 - то нет.  
Учитывать как рекламу в онлайне, то есть если один раз клиент ее увидел (в произвольной записи), значит все записи по данному клиенту валидны.

Или же анализ проводить только по тем записям, где имеется участие менеджера, а где нет - игнорировать.
Объединить взаимосвязанные данные (Дата, Сумма, Номер) из нескольких столбцов в один
 
Цитата
Prostaya написал:
Чтобы данные могли обновляться.  
Они и так обновляются, зайдите в Data и нажмите кнопку Refresh All.
Посчитать сколько человек сделало покупку по 2 условиям
 
Код
let
    Src = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grp = Table.Group
           (
            Src, 
            {"ID клента"}, 
            {
             {
              "Tmp", 
              each
               let
                  // Перед выбором уникальных комбинаций фильтруем наборы по каждому клиенту
                  Tt = Table.SelectRows(_, each [Реклама] = "online" and [Участие менеджера] <> null and [Участие менеджера] > 0)
                in
                 // И считаем уникальные значения тлько по способу покупки (избавляемся от участия менеджеров в уникальных комбинациях)
                 Table.RowCount(Table.Distinct(Tt, "Способ покупки"))
             }
            }
           ),
    Out = Table.SelectColumns(Table.SelectRows(Grp, each [Tmp] > 1), {"ID клента"})
in 
    Out

P.S. Алгоритм неверный.. надо переделать.

Прошу уточнить насчет участия менеджера - его как учитывать?
Изменено: Alexey_Spb - 18 Мар 2019 15:13:15
Посчитать сколько человек сделало покупку по 2 условиям
 
Цитата
Processor написал:
у вас только 2 условия, а выше больше условий
Я делал по этому посту..

Код
Необходимо посчитать сколько человек сделало покупку по 2 условиям:

1. Клиент совершил покупку более двух раз. (больше или равно 2)
2. Клиент совершил покупку двумя разными способами.  
Приведите цитату как надо.
Изменено: Alexey_Spb - 18 Мар 2019 14:45:41
Посчитать сколько человек сделало покупку по 2 условиям
 
Вариант на PQ. Если у вас большая таблица, так будет значительно быстрее.

PQ - это дальнейшее развитие формул в Excel.

Код
let
    // Загружаем данные из умной таблицы Table1
    Src = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // Объединяем таблицу по ID клиента:
    //   - В столбце Tmp: число уникальных комбинаций клиент-способ покупки для данного клиента.
    Grp = Table.Group(Src, {"ID клента"}, {{"Tmp", each Table.RowCount(Table.Distinct(_))}}),
    // Выбираем все строки, где число уникальных комбинаций больше одного (очевидно что число покупок тоже будет больше одного).
    // После выбираем столбец "Id Клиента"
    Out = Table.SelectColumns(Table.SelectRows(Grp, each [Tmp] > 1), {"ID клента"})
in
    // Возвращаем эту таблицу как результат
    Out
Изменено: Alexey_Spb - 18 Мар 2019 14:45:03
Объединение файлов в Power Query, Не отображается диалоговое окно
 
Цитата
Eugene Autos написал:
Посоветуете какую-то сборку?
Не знаю что вам ответит PoohKrd, но я вам советую сборку под назвавнием Advanced Editor - сразу уйдет зависимость от всяких окон.
Изменено: Alexey_Spb - 18 Мар 2019 14:24:09
Страницы: 1 2 3 4 5 6 7 8 След.
Наверх