Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Богдан
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
Заполнение дат по условию из родительской строки
 
#
02.03.2025 17:28:20
Цитата
написал:
обязательно? Вроде несложные формулы "ентосмогють":=ЕСЛИ(C2<>"";C2;ЕСЛИ(И(C2="";C1<>"");C1;КОНМЕСЯЦА(E1;0)+1))=ЕСЛИ(D2<>"";D2;МИН(КОНМЕСЯЦА(E2;0);ПРОСМОТР(7^8;D$1:[@GulBis])))Но ничего не имею против изучения языка "М" ;-)...было любопытно "сгенерить" таблицу одной формулой (2021+):дин.массив=LET(к1_;Tabelle1[Column1];к2_;Tabelle1[Column2];к3_;Tabelle1[GulAb];к4_;Tabelle1[GulBis];п_;ПОСЛЕД(ЧСТРОК(к1_));л_;п_/(к3_<>"");к3з_;ПРОСМОТР(п_;л_;к3_);к4з_;ПРОСМОТР(п_;л_;к4_);к3к_;КОНМЕСЯЦА(к3з_;п_-ПРОСМОТР(п_;л_)-2)+1;кк3_;(к3з_+к3к_+ABS(к3з_-к3к_))/2;к4к_;КОНМЕСЯЦА(кк3_;0);я_;ВЫБОР(ПОСЛЕД(;4);к1_;к2_;кк3_;ЕСЛИ(к4_="";(к4з_+к4к_-ABS(к4з_-к4к_))/2;к4_));я_)

Прикрепленные файлы
Example_.xlsx  (14.16 КБ)
В том-то и дело, что обязательно. Исходная таблица с пустыми строками под родительской строкой - это уже результат нескольких шагов обработки данных в PQ. В теории можно было бы использовать промежуточную таблицу с заполнением в Excel, но это не совсем удобно. Такие дела...
 
Перейти    
Заполнение дат по условию из родительской строки
 
#
02.03.2025 17:26:05
Цитата
написал:
Ma_Ri , здравствуйте. Спасибо за наблюдательность. И правда, косяк вышел  Исправляем...Ошибки надо не признавать - их надо смывать! Кровью!let    fx = (row) => {row} & ((names, dates) => List.Generate(        () => [s = dates{0}, e = List.Min({dates{1}, Date.EndOfMonth(s)})],        (x) => x   [s = Date.AddDays(x[e], 1), e = List.Min({dates{1}, Date.EndOfMonth(s)})],        (x) => names & Record.ToList(x)    ))(List.FirstN(row, 2), List.LastN(row, 2)),    before = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],    dts = Table.TransformColumnTypes(        Table.SelectRows(before, (x) => x[GulAb] <> null),         {{"GulAb", type date}, {"GulBis", type date}}    ),     the_list = Table.ToList(dts, fx),    after = Table.FromList(List.Combine(the_list), each _, Table.ColumnNames(before))in    afterКроме того, что вы перечислили, к странным решениям я бы еще отнес Table.Buffer, а также List.FirstN(lst{0},2) в генераторе (что будет вычисляться каждый раз - почему бы не сделать это один раз перед генератором?). Еще одно спорное решение - каждый раз создавать таблицу (в функции) и потом Table.Combine... В рез-те при возрастании объемов это все будет умирать в муках ((( Но с генератором вы справились - и это главное!   Цитата Ma_Ri  написал:Спасибо, что оставили свое решение. )а надо было убрать?  
И вам большое спасибо!)  
 
Перейти    
Заполнение дат по условию из родительской строки
 
#
02.03.2025 17:23:10
Цитата
написал:
Здравствуйте!Пробовала использовать List.Generate (опыта никакого), что уж получилось...  pqКодlet
   f=(x)=>[lst=Table.ToList(x,(y)=>y),
           from = lst{0}{2},
           to = lst{0}{3},
           gen=List.Generate(()=>[start = from, end =Date.EndOfMonth(start)],
                             (y)=>y[start]  [start =Date.StartOfMonth(Date.AddMonths(y[start],1)),
                                   end=if Date.EndOfMonth(start)>to then to else Date.EndOfMonth(start)],
                             (y)=>List.FirstN(lst{0},2)&{y[start],y[end]}),
           tbl=Table.FromList({lst{0}}&gen,(x)=>x,Table.ColumnNames(x)) ][tbl],

   from = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
   tr = Table.Buffer(Table.TransformColumnTypes(from,{{"GulAb",type date},{"GulBis",type date}})),
   gr=Table.Group(tr,List.FirstN(Table.ColumnNames(from),3),{"tmp",f},GroupKind.Local,(s,c)=>Number.From(c[GulAb]<>null)),
   to=Table.Combine(gr[tmp])
in
   toВсего доброго!

Прикрепленные файлы
Example.xlsx  (27.45 КБ)

Изменено: Ma_Ri  - 01.03.2025 18:48:54
Ma_Ri спасибо вам большое! Выглядит очень жизнеспособно!  :D  
 
Перейти    
Заполнение дат по условию из родительской строки
 
#
28.02.2025 17:31:25
Ребята, всем добрый день.
Прошу вас о помощи, потому как треклятый искуственный интелект подсовывает разные нерабочие пользовательские функции по моему вопросу, а сам я не настолько эксперт в M
Суть в том, что нужно в файле из примера получить из левой таблицы правую в Power Query.
Количество пустых строк в левой таблице равно количеству месяцев родительской строки между колонками GulAb и GulBis.
Дата GulBis первой пустой строки равна аналогичной дате родительской строки, а дата в колонке GulBis концу месяца даты этого столбца родительской строки. Все пустые строки вплоть до даты в родительском столбце GulBis - это первый и последний месяц каждего последующего месяца. Последняя строка в столбце GulAb дата первого дня месяца а GulBis - дата родительской строки.
Подскажите, как это можно сделать в Power Query и можно ли ?) Всем спасибо заранее за помощь и хорошего дня
 
Перейти    
Получение определенных текстовых значений из столбца в Power Query
 
#
23.12.2022 09:00:15
Цитата
написал:
Два шага:

1. Добавление столбца => Настраиваемый столбец
RColumn
= if Text.Start([Komponentennummer], 1)="R" then [Komponentennummer] else null

2. Преобразование => Заполнить => Вниз

Код:
Код
    [URL=#]?[/URL]       1  2      AddColumn = Table.AddColumn(Prev_Step,   "RColumn"  , each if Text.Start([Komponentennummer], 1)=  "R"   then [Komponentennummer] else null ),    FillDown = Table.FillDown(AddColumn,{  "RColumn"  })  
 
Как оказывается все просто! Вот так и знал, что должна быть подобная функция, но не смог найти! Большое спасибо за помощь :)  
 
Перейти    
Получение определенных текстовых значений из столбца в Power Query
 
#
22.12.2022 17:45:42
Коллеги, приветствую!
Поскольку в Power Query нет регулярных знаков, а следовательно и нет подстановочных знаков как * в Excel, столкнулся с проблемой не всех, а конкретных значений из соседнего текстового столбца по условию. Нужно получать эти значения именно в PQ, потому как там же планируется дальнейшая работа.
Во вложенном файле есть значения, которые начинаются либо с R, либо c A. Задача в том, чтобы в соседнем столбце (желаемый результат представлен в столбце B) напротив всех значений, начинающихся с А, проставить значение c R, которое стоит над ними. Соотевтственно, когда в столбце А доходим до следующего значения R*, проставляем его в соседнем столбце и так далее. Я пробовал дублировать столбцы и заменять значения, начинающиеся с А, на что-то иное, а потому заполнением вверх-вниз баловаться, но это ни к чему не привело, потому что сочетаний А????? может быть огромное количество, а подстановочных знаков нет...
Буду благодарен за совет, если кто сталкивался с подобной задачей
 
Перейти    
Последовательность обновления запросов с помощью VBA
 
#
06.12.2022 10:03:23
Цитата
webley написал:
Если не ошибаюсь, проблема решается отключением фонового обновления в запросах и потом задать в макросе порядок обновления (как Вы уже и сделали)

Благодарю тебя, добрый человек, это помогло!  :)  8)  
Изменено: Богдан - 06.12.2022 12:04:32
 
Перейти    
Последовательность обновления запросов с помощью VBA
 
#
05.12.2022 17:53:42
Коллеги, добрый день. Вопрос такой. Есть 5 листов. На втором листе данные я получаю запросом в PQ c внешнего файла. Начиная с третьего листа данные на лист получаются запросом к таблице предыдущего листа. И на первом листе есть кнопка, при нажатии на которую все таблицы/соединения должны обновится.
Проблема в том, что первым обновляется таблица на последнем листе, которая самая маленькая, видимо из-за этого она первой и обновляется.
А это для меня плохо, потому как она должна обновляться последней, на ней выводятся итоговые данные. В идеале должно обновится соединение второго листа, потом третьего, потом четвертого и в конце пятого. Попытка вот таким кодом вручную задать порядок листов к успеху не привела, все-равно первой обновляется самая маленькая таблица.

Sub refresh_all()
ThisWorkbook.Worksheets(2).ListObjects("MasterData").Refresh
ThisWorkbook.Worksheets(3).ListObjects("Anzahl_Equipment_Data").Refresh
ThisWorkbook.Worksheets(4).ListObjects("Anzahl_Materialien").Refresh
ThisWorkbook.Worksheets(5).ListObjects("Liste_fьr_Arno").Refresh
End Sub

Подскажите пожалуйста, есть ли способ указать порядок обновления соединений, если их больше 1 в книге ?
 
Перейти    
Разделение значений одного столбца на несколько
 
#
23.09.2022 08:19:08
Благодарю вас , добрые люди ! :oops:  :D  
 
Перейти    
Разделение значений одного столбца на несколько
 
#
22.09.2022 16:20:59
Добрый день, уважаемые друзья.
Возможно для кого-то моя задача покажется простой, но я за день "гугления" как-то не нашел ее решения, а сам, естественно, не знаю как можно ее решить.
Суть в том, что есть 3 столбца. В первом наименование материала и по два дублирующихся значения для каждого материала, потому что в столбце "Qty" для каждого материала сначало идет количество, потом стоимость. Задача эти значения из столбца"Qty" разбить на 2 столбца и избавиться от дублей, в показано в табличке рядом...Чувствую, что это можно решить с помощью PQ, но не понимаб как, потому как там для обработки строк не такой большой набор встроенных действий...
 
Перейти    
Сведение столбцов с одним названием одной таблицы
 
#
21.07.2022 16:30:19
Цитата
написал:
Богдан, у Вас же даже запрос PQ в файле свой.
По сути - из именованной ячейки в на листе Path (там формулой Excel вычисляется путь к этому файлу) берем путь. Далее по шагам гляньте, как обрабатываем.
Немного даже упростил, думаю Text.Trim - излишняя операция (и даже вредная, если " " считать значением  ):
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28      let        path = Table.FirstValue ( Excel.CurrentWorkbook(){[ Name =   "path"   ]}[Content] ),        wb = Excel.Workbook ( File.Contents ( path ) ),        sh = wb{[ Item =   "Main single Table"  , Kind =   "Sheet"   ]}[Data],        filter = Table.SelectRows ( sh, ( r ) => List.NonNullCount ( Record.FieldValues ( r ) ) > 0 ),        transpose = Table.Transpose ( filter ),        group = Table.Group (          transpose,          {   "Column1"   },          {            {              "clmns"  ,              ( t ) =>                [                  columns = List.Skip ( Table.ToColumns ( t ), 1 ),                  transform = List.TransformMany (                    columns,                    ( x ) => { List.  Select   ( x, ( z ) => z <>   ""   ) },                    ( x, y ) => Text.Combine ( y,   ";#(lf)"   )                  )                ][transform],              type {text}            }          }        ),        toTable = Table.FromColumns ( group[clmns], group[Column1] )    in        toTable  
 
Я тот запрос формировал методом научного тыка))) В любом случае большое человеческое спасибо! Вы мне огромное подспорье дали, дальше ума хватит разобраться. Надеюсь :D  
 
Перейти    
Сведение столбцов с одним названием одной таблицы
 
#
21.07.2022 15:54:04
Цитата
написал:
Богдан, такой вариант в PQ:
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28      let        path = Table.FirstValue ( Excel.CurrentWorkbook(){[ Name =   "path"   ]}[Content] ),        wb = Excel.Workbook ( File.Contents ( path ) ),        sh = wb{[ Item =   "Main single Table"  , Kind =   "Sheet"   ]}[Data],        filter = Table.SelectRows ( sh, ( r ) => List.NonNullCount ( Record.FieldValues ( r ) ) > 0 ),        transpose = Table.Transpose ( filter ),        group = Table.Group (          transpose,          {   "Column1"   },          {            {              "clmns"  ,              ( t ) =>                [                  columns = List.Skip ( Table.ToColumns ( t ), 1 ),                  transform = List.TransformMany (                    columns,                    ( x ) => { List.  Select   ( List.Transform ( x, Text.Trim ), ( z ) => z <>   ""   ) },                    ( x, y ) => Text.Combine ( y,   ";#(lf)"   )                  )                ][transform],              type table            }          }        ),        toTable = Table.FromColumns ( group[clmns], group[Column1] )    in        toTable  
 
Или с удалением столбцов, по которым вообще данных нет:
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29      let        path = Table.FirstValue ( Excel.CurrentWorkbook(){[ Name =   "path"   ]}[Content] ),        wb = Excel.Workbook ( File.Contents ( path ) ),        sh = wb{[ Item =   "Main single Table"  , Kind =   "Sheet"   ]}[Data],        filter = Table.SelectRows ( sh, ( r ) => List.NonNullCount ( Record.FieldValues ( r ) ) > 0 ),        transpose = Table.Transpose ( filter ),        group = Table.Group (          transpose,          {   "Column1"   },          {            {              "clmns"  ,              ( t ) =>                [                  columns = List.Skip ( Table.ToColumns ( t ), 1 ),                  transform = List.TransformMany (                    columns,                    ( x ) => { List.  Select   ( List.Transform ( x, Text.Trim ), ( z ) => z <>   ""   ) },                    ( x, y ) => Text.Combine ( y,   ";#(lf)"   )                  )                ][transform],              type table            }          }        ),        filter2 = Table.SelectRows ( group, each List.Count ( List.  Select   ( [clmns], ( x ) => x <>   ""   ) ) > 0 ),        toTable = Table.FromColumns ( filter2[clmns], filter2[Column1] )    in        toTable  
 
Ниче не понятно, но очень интересно)))
Осталось разобраться как этот код в Power Query правильно использовать, потому что моя нога туда почти не ступала)
Большое спасибо ! :oops:  :)  
 
Перейти    
Сведение столбцов с одним названием одной таблицы
 
#
21.07.2022 15:05:30
Добрый день, друзья. Есть у меня такая задача, которая не поддается правильном решению со стороны моего скрипучего мозга)
Есть таблица. В таблице в столбцах значатся характеристики определенных агрегатов, а в строках, соотвественно, значения этих характеристик.
Проблема в том, что столбцов относительно много и они могут дублироваться. Почему они дублируются-отдельный разговор, пока просто примем это за аксиому. Более того, дублей может быть более одного. Задача состоит в том, чтобы дублирующиеся столбцы свети в один, заполнив значения с дублей в единый основной столбец, а потом ненужные дублирующиеся столбцы поудалять. Хоть столбцы и дублируются, но во всех дублирующихся столбцах будет только одно заполненное значение, которое нужно оставить в едином свободном столбце. Столбцы, которые не дублируются, трогать не нужно, а оставить как есть.

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

*-логика примерно следующая:
1. Посчитать количество заполненных столбцов и поместить в переменную(пусть это будет n)
2. Создать переменную, в которой будет накапливаться количество дублированных столбцов(пусть это будет z)
3. Создать цикл с 1 по n-z, в котором обходить все столбцы и проверять есть ли дубли и сколько их(дублей, то есть поместить это количество в переменную z)
4. Когда такой столбец найден, запустить для него цикл с 1 по z, который будет обходить все строки столбца и проверять их на пустоту. Если пустота обнаружена, нужно тянуть значение строки с этим же номером из первого дублированного столбца. Если и он пуст, тянем со следуюй строки с этим номером со следующего дублированного столбца. Если нашли, выходим из второго цикла и идем на следующую итерацию основного цикла. Если ничего не найдено, то идем на следуюшую итерацию второго цикла (вложенного цикла) пока не обойдем все дублированные столбцы. Важно: после выхода с второго цикла(вложенного) нужно удалить все дублированные столбцы, кроме основного-того, в который мы переносили данные, если они были

Голову сломал, если честно...
 
Перейти    
Получение значений столбцов с нескольких листов
 
#
30.06.2022 10:11:37
Цитата
написал:
Если у Вас офис 2019 и выше то можете собрать уникальные такой формулой:
Код
    [URL=#]?[/URL]       1      =УНИК(ФИЛЬТР.XML(  "<t><s>"  &ПОДСТАВИТЬ(ОБЪЕДИНИТЬ(  ";"  ;1;  '7180301:6180310'!1:1);";";"</s><s>")&"</s></t>";"//s"))  
 
Но я бы на Вашем месте смотрел в сторону Power Query. Например можно почитать  ТУТ , ещё может пригодится  ЭТО  и  ЭТО  и выглядит примерно как в файле
Код
    [URL=#]?[/URL]       1  2  3  4  5      let          f=(x)=>Excel.CurrentWorkbook(){[Name=x]}[Content],          Запрос = Table.Combine({f(  "Таблица1"  ), f(  "Таблица2"  ), f(  "Таблица3"  ), f(  "Таблица4"  ), f(  "Таблица5"  ), f(  "Таблица6"  ), f(  "Таблица7"  ), f(  "Таблица8"  )})    in          Запрос  
 
PS И ещё, в  ПРАВИЛАХ  форума (п.2.6), сказано: 1 вопрос 1 тема.
Извиняюсь за 2 вопроса. Просто они взаимосвязаны, поэтому решил не разделять для лучшего понимания.
Спасибо большое за помощь, буду изучать инструменты, которыми не владею))
 
Перейти    
Получение значений столбцов с нескольких листов
 
#
30.06.2022 08:28:27
Цитата
написал:
Богдан, ну, с первой задачей - собрать все заголовки со всех листов я могу помочь.
Скачайте файл, откройте его, нажмите Alt+F8 - Выполнить - макрос соберёт все уникальные заголовки со всех листов и вставит их на новый лист.
P.S. А по второй задаче - созданию сводной - если никто вам не поможет бесплатно... могу написать макрос за деньги, который соберёт вашу сводную
Большое спасибо Вам за макрос! Понимаете в чем дело, я все же надеялся, что это решается без написания макрос на VBA, почему-то казалось, что вторую задачу можно решить функциями. Как мне казалось, там всего-то и надо что перебирать столбцы по имени и возвращать все содержимое столбца, если заголовок найден. И так по каждому листу. Видимо без "продвинутого" уровня владением макросами, которые я только начал познавать, не обойдется...
В любом случае спасибо за помощь!
 
Перейти    
Получение значений столбцов с нескольких листов
 
#
29.06.2022 16:16:21
Загрузил :oops:  
 
Перейти    
Получение значений столбцов с нескольких листов
 
#
29.06.2022 15:28:25
Ребятки, добрый день.
Помогите, пожалуйста автоматизировать 2 задачи, которые сейчас делаю ручками. Чувствую, это можно сделать, но пока не нашел ответа как :(
Первая задача
Есть несколько листов в одной книге. Мне нужно на новый лист со всех остальных листов получить значения с первой строки всех столбцов, где эти значения заполнены(пересечения столбца и строки не пустое). То есть иными словами мне надо получить все значения первой строки заполненных столбцов в массиве листов.
Я могу подсчитать и получить номера столбцов с помощью функции, а потом подставить этот массив в функцию Индекс для получения значений первой строки этих столбцов, но проблема в том, что на каждом листе разное количество столбцов...И поэтому в функции получения массива номеров столбцов диапазон каждого листа нужно менять вручную :cry: Можно как-то получить массив номеров заполненных столбцов листа ?
Здесь же вопрос-первым аргументом функции ИНДЕКС идет массив откуда брать данные. Какой будет синтаксис, чтобы в качестве этого аргумента указать массив листов? Чтобы не для каждого листа отдельно форматировать формулу, а получить данные одной формулой?
Если немного намудрил с описанием, напишу на человеческом для чего мне это нужно - есть N количество листов, на каждом листе есть заголовки(характеристики агрегатов). Количество заголовков-характеристик на каждом листе разное. Большинство статусов по листам совпадают, но есть и те, которые индивидуальны для каждого листа. Мне нужно на одном листе собрать все возможные заголовки-статусы и сделать список неповторяющихся, чтобы в будущем делать сводный лист по всем листам. Отсюда вытекает...
Вторая задача
Есть сводный лист со всеми возможными характеристиками агрегатов. Мне нужно по названию заголовка-характеристики получить все данные из столбцов других листов... Получается только в ручном режиме через сочитание функций ПОИСКПОПОЗ и ИНДЕКС, меняя аргументы для каждого листа. Но это очень много времени забирает, может умные люди подскажут как это правильнее сделать...? :oops:  
Изменено: Богдан - 29.06.2022 16:16:01
 
Перейти    
Страницы: 1
Наверх