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

Страницы: 1 2 3 4 5 6 7 8 След.
Каскадные выпадающие списки. Список в Power Query, Нужно настроить сложную связку выпадающих списков у двух книг или через запрос создать именованный диапазон.
 
А файл-пример где?

Power Query не может создавать именованные диапазоны, за этим вам к VBS. Но связанные выпадающие списки на PQ решаются другим способом - делаете на PQ таблицы, по одной для каждого уровня вложенных списков. Все они вида: "Имя параметра в выпадающем списке" - "Диапазон значений, соответствующих данному параметру, в таблице следущего уровня":

Значения в списке уровня 1: =INDIRECT(INDEX(Table_Level_1;;1)

Table_Level_1:
A: Выбранное в списке 1 значениеB: Ссылка для уровня 2
Группа товаров 1C2:C3
Группа товаров 2C4:C5
Значения в списке уровня 2: =INDIRECT(VLOOKUP(Table_Level_1;(введенное пользователем значение на уровне 1);2))

Table_Level_2:
C:  Выбранное в списке 2 значение D: Ссылка для уровня 3
Товар A в группе 1 E1:E2
Товар B в группе 1 E3:E3
Товар C в группе 2 E4:E4
Товар D в группе 2 E5:E8
Значения в списке уровня 3: =INDIRECT(VLOOKUP(Table_Level_2;(введенное пользователем значение на уровне 2);2))

Table_Level_3:
E: КомпонентыF: Не используется
Компонент 1 товара A
Компонент 2 товара А
Компонент товара B
Компонент товара C
Компонент 1 товара D
Компонент 2 товара D
Компонент 3 товара D
Компонент 4 товара D
Идея в том что диапазон из первой таблицы ссылается на диапазон во второй таблице, в выпадающем списке вы используете его через ДВССЫЛ(). В этом диапазоне как раз будут данные из выпадающего списка. Может, гуру экселя вам подскажут более изящный способ. PQ в данном случае используется чтобы построить данные справочные таблицы со ссылками и значениям
Изменено: Alexey_Spb - 21.05.2019 12:07:04
Аналог формулы FILTER из Google Sheet в Excel
 
argyman, попробуйте Power Query - имхо, не захотите больше возвращаться на поделие от гугл никогда больше. Ну только если вам не надо постоянно правит одну и ту же маленькую таблицу сотне человек одновременно.
PQ: Как свернуть номера в диапазоны по условию, Список номеров свернуть в диапазоны при двух условиях
 
Автор, некогда сделать алгоритм... Идея такая.

Добавляете столбец, значение которого увеличивается на единицу каждый раз когда при переходе на новую строку у вас есть изменение в полях, которые вам надо отслеживать. Делается через List.Accumulate со скармливанием ему всех строк таблицы, либо хитрым способом с индексными колонками (одна начинается с нуля, другая с единицы, потом их ждойните, получив в одной строке значения столбцов на этой строке и на предыдущей, а потом сравниваете изменения). Но имхо, аккумулятор тут лучше.

Далее группируете по этому столбцу.

Aleksey_Zhigulin - не так, смотрите мой пример (я сначала так же подумал, как и вы).
PQ: Как свернуть номера в диапазоны по условию, Список номеров свернуть в диапазоны при двух условиях
 
Группируете одновременно по полям Код - Статус (так можно). В один столбец - минимум из списка номеров, в другой - максимум. Это одно действие. Все.

Доработайте по вкусу.

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

Либо у вас ошибка в ИД - нужны ваши пояснения:
4712200052
4712200083
4712200092
4712200122
Изменено: Alexey_Spb - 08.05.2019 12:44:41
Расчет стоимости бумаги исходя из данных приведенных в таблице
 
Это лучше сделать при помощи Power Query. Так как нет примера - то нет и ответа.
Тестовое задание по Excel на должность аналитика
 
Думаю, тесты на должность аналитика обязательно должны включать тесты на знание PowerQuery, PowerBi и немного по части БД.

Так же имхо, тест должен быть профильным - пусть из реального массива данных получит что вам нужно.
Изменено: Alexey_Spb - 18.04.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 - 04.04.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.03.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.03.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.03.2019 17:17:09
Расчет вместимости паллет в фуру, Сколькой войдет паллет разного размера
 
Имхо, автору тему надо создать в платных заказах. Но есть подозрение что он это не сделает  :)  
Поиск необходимых данных в огромном массиве и отображение результата в виде таблицы
 
Цитата
Dark1589 написал:
но фильтровать артикулы лучше всё-равно заранее

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

Пришлите пример с 10 млн. строк на a l е x е y _ b h (собако) майл.руъ
Изменено: Alexey_Spb - 22.03.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.03.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.03.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.03.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.03.2019 09:20:12
PQ преобразование txt файла
 
Новая, все должно быть в порядке.

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

Или же анализ проводить только по тем записям, где имеется участие менеджера, а где нет - игнорировать.
Страницы: 1 2 3 4 5 6 7 8 След.
Наверх