Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/PQ извлечь строки после определенной строки - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » PQ извлечь строки после определенной строки (Формулы/Formulas)
PQ извлечь строки после определенной строки
Anis625 Дата: Воскресенье, 14.04.2019, 15:48 | Сообщение № 1
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
Добрый день, участникам форума!

Решая одну практическую задачку инструментами PQ (благодаря sboy можно сказать очень многому научился), но "уперся" в одну "стену".

Этим кодом почистил файл почти необходимого состояния:
[vba]
Код
let
    Источник = Excel.Workbook(File.Contents("F:\PQ\путь.xlsx"), null, true),
    #"Карта КПЭ_Sheet" = Источник{[Item="Карта КПЭ",Kind="Sheet"]}[Data],
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Карта КПЭ_Sheet",{"Column2"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Column2] <> null and [Column2] <> " " and [Column2] <> "Дата:" and [Column2] <> "Подпись Работника"))
in
    #"Строки с примененным фильтром"
[/vba]

Результатом кода получается столбце с данными (во вложении). Пытаюсь извлечь строки после строки "Ключевой проект" (это единственная строка название которой не меняется, но меняется ее местоположение при работе с другими файлами).

Дальше этот запрос будет обрабатывать папку с файлами в которых строки после строки "Ключевой проект" может отличаться.

Подскажите, пожалуйста, как покрутить этот кубик-рубика чтобы получать названия проектов после строки "Ключевой проект".
Привязываться к номерами не стоит, т.к. в некоторых файлах нет номеров
К сообщению приложен файл: --.xlsx (9.4 Kb)


Сообщение отредактировал Anis625 - Воскресенье, 14.04.2019, 15:48
 
Ответить
СообщениеДобрый день, участникам форума!

Решая одну практическую задачку инструментами PQ (благодаря sboy можно сказать очень многому научился), но "уперся" в одну "стену".

Этим кодом почистил файл почти необходимого состояния:
[vba]
Код
let
    Источник = Excel.Workbook(File.Contents("F:\PQ\путь.xlsx"), null, true),
    #"Карта КПЭ_Sheet" = Источник{[Item="Карта КПЭ",Kind="Sheet"]}[Data],
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Карта КПЭ_Sheet",{"Column2"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Column2] <> null and [Column2] <> " " and [Column2] <> "Дата:" and [Column2] <> "Подпись Работника"))
in
    #"Строки с примененным фильтром"
[/vba]

Результатом кода получается столбце с данными (во вложении). Пытаюсь извлечь строки после строки "Ключевой проект" (это единственная строка название которой не меняется, но меняется ее местоположение при работе с другими файлами).

Дальше этот запрос будет обрабатывать папку с файлами в которых строки после строки "Ключевой проект" может отличаться.

Подскажите, пожалуйста, как покрутить этот кубик-рубика чтобы получать названия проектов после строки "Ключевой проект".
Привязываться к номерами не стоит, т.к. в некоторых файлах нет номеров
Автор - Anis625
Дата добавления - 14.04.2019 в 15:48
Anis625 Дата: Воскресенье, 14.04.2019, 15:58 | Сообщение № 2
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
Как вариант можно попробовать добавить пользовательский столбец с каким-нибудь критерием:
[vba]
Код
Table.AddColumn(#"Строки с примененным фильтром", "Пользовательская", each if [Column2] = "Ключевой проект:" then "КП" else 0)
[/vba]
А потом удалить строки с 0

Но он применяет этот код только к одной строке. А как можно добавить, например, слово "КП" на все строки ниже?
К сообщению приложен файл: 8162298.jpg (14.2 Kb)


Сообщение отредактировал Anis625 - Воскресенье, 14.04.2019, 16:02
 
Ответить
СообщениеКак вариант можно попробовать добавить пользовательский столбец с каким-нибудь критерием:
[vba]
Код
Table.AddColumn(#"Строки с примененным фильтром", "Пользовательская", each if [Column2] = "Ключевой проект:" then "КП" else 0)
[/vba]
А потом удалить строки с 0

Но он применяет этот код только к одной строке. А как можно добавить, например, слово "КП" на все строки ниже?
Автор - Anis625
Дата добавления - 14.04.2019 в 15:58
Anis625 Дата: Воскресенье, 14.04.2019, 16:08 | Сообщение № 3
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
В Excel я бы сделал:

[vba]
Код
ЕСЛИ(ИЛИ(A2="Ключевой проект:";B1="Ключевой проект:");"КП";0)
[/vba]

Но PQ ссылается на весь столбец и пока не понимаю как можно сделать также в PQ как в EXCEL
 
Ответить
СообщениеВ Excel я бы сделал:

[vba]
Код
ЕСЛИ(ИЛИ(A2="Ключевой проект:";B1="Ключевой проект:");"КП";0)
[/vba]

Но PQ ссылается на весь столбец и пока не понимаю как можно сделать также в PQ как в EXCEL
Автор - Anis625
Дата добавления - 14.04.2019 в 16:08
krosav4ig Дата: Воскресенье, 14.04.2019, 17:32 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Anis625, ниже списка проектов могут идти какие-либо другие данные?

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
СообщениеAnis625, ниже списка проектов могут идти какие-либо другие данные?
Автор - krosav4ig
Дата добавления - 14.04.2019 в 17:32
Anis625 Дата: Воскресенье, 14.04.2019, 17:34 | Сообщение № 5
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
krosav4ig,

уже нет, написал код так, что дальше ничего нет - заканчивается проектами которые нужно извлечь.

Вот только не написал, что может быть случай когда не может быть строки "Ключевой проект:" и соответственно нечего будет извлекать.
 
Ответить
Сообщениеkrosav4ig,

уже нет, написал код так, что дальше ничего нет - заканчивается проектами которые нужно извлечь.

Вот только не написал, что может быть случай когда не может быть строки "Ключевой проект:" и соответственно нечего будет извлекать.
Автор - Anis625
Дата добавления - 14.04.2019 в 17:34
krosav4ig Дата: Воскресенье, 14.04.2019, 17:37 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
тогда можно как-то так
[vba]
Код
let
    Источник = Excel.Workbook(File.Contents("F:\PQ\путь.xlsx"), null, true),
    #"Карта КПЭ_Sheet" = Источник{[Item="Карта КПЭ",Kind="Sheet"]}[Data],
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Карта КПЭ_Sheet",{"Column2"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Column2] <> null and [Column2] <> " " and [Column2] <> "Дата:" and [Column2] <> "Подпись Работника")),
    Пользовательская = ((t)=>Table.Skip(t,List.PositionOf(Table.Column(t,"Column2"),"Ключевой проект:")+1))(#"Строки с примененным фильтром")
in
    Пользовательская
[/vba]

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениетогда можно как-то так
[vba]
Код
let
    Источник = Excel.Workbook(File.Contents("F:\PQ\путь.xlsx"), null, true),
    #"Карта КПЭ_Sheet" = Источник{[Item="Карта КПЭ",Kind="Sheet"]}[Data],
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Карта КПЭ_Sheet",{"Column2"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Column2] <> null and [Column2] <> " " and [Column2] <> "Дата:" and [Column2] <> "Подпись Работника")),
    Пользовательская = ((t)=>Table.Skip(t,List.PositionOf(Table.Column(t,"Column2"),"Ключевой проект:")+1))(#"Строки с примененным фильтром")
in
    Пользовательская
[/vba]
Автор - krosav4ig
Дата добавления - 14.04.2019 в 17:37
krosav4ig Дата: Воскресенье, 14.04.2019, 17:42 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
или так [vba]
Код
let
    Источник = Excel.Workbook(File.Contents("F:\PQ\путь.xlsx"), null, true),
    #"Карта КПЭ_Sheet" = Источник{[Item="Карта КПЭ",Kind="Sheet"]}[Data],
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Карта КПЭ_Sheet",{"Column2"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Column2] <> null and [Column2] <> " " and [Column2] <> "Дата:" and [Column2] <> "Подпись Работника")),
    #"Added Conditional Column" = Table.AddColumn(#"Строки с примененным фильтром", "Пользовательская", each if [Column2] = "Ключевой проект:" then 1 else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Пользовательская"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Пользовательская] = 1)),
    #"Removed Top Rows" = Table.Skip(#"Filtered Rows",1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Пользовательская"})
in
#"Removed Columns"
[/vba]

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Воскресенье, 14.04.2019, 17:43
 
Ответить
Сообщениеили так [vba]
Код
let
    Источник = Excel.Workbook(File.Contents("F:\PQ\путь.xlsx"), null, true),
    #"Карта КПЭ_Sheet" = Источник{[Item="Карта КПЭ",Kind="Sheet"]}[Data],
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Карта КПЭ_Sheet",{"Column2"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Column2] <> null and [Column2] <> " " and [Column2] <> "Дата:" and [Column2] <> "Подпись Работника")),
    #"Added Conditional Column" = Table.AddColumn(#"Строки с примененным фильтром", "Пользовательская", each if [Column2] = "Ключевой проект:" then 1 else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Пользовательская"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Пользовательская] = 1)),
    #"Removed Top Rows" = Table.Skip(#"Filtered Rows",1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Пользовательская"})
in
#"Removed Columns"
[/vba]
Автор - krosav4ig
Дата добавления - 14.04.2019 в 17:42
krosav4ig Дата: Воскресенье, 14.04.2019, 17:45 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
или так [vba]
Код
let
    Источник = Excel.Workbook(File.Contents("F:\PQ\путь.xlsx"), null, true),
    #"Карта КПЭ_Sheet" = Источник{[Item="Карта КПЭ",Kind="Sheet"]}[Data],
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Карта КПЭ_Sheet",{"Column2"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Column2] <> null and [Column2] <> " " and [Column2] <> "Дата:" and [Column2] <> "Подпись Работника")),
    #"Added Conditional Column" = Table.AddColumn(#"Строки с примененным фильтром", "Пользовательская", each if [Column2] = "Ключевой проект:" then 1 else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Пользовательская"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Пользовательская] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Пользовательская"})
in
    #"Removed Columns"
[/vba]

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениеили так [vba]
Код
let
    Источник = Excel.Workbook(File.Contents("F:\PQ\путь.xlsx"), null, true),
    #"Карта КПЭ_Sheet" = Источник{[Item="Карта КПЭ",Kind="Sheet"]}[Data],
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Карта КПЭ_Sheet",{"Column2"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Column2] <> null and [Column2] <> " " and [Column2] <> "Дата:" and [Column2] <> "Подпись Работника")),
    #"Added Conditional Column" = Table.AddColumn(#"Строки с примененным фильтром", "Пользовательская", each if [Column2] = "Ключевой проект:" then 1 else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Пользовательская"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Пользовательская] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Пользовательская"})
in
    #"Removed Columns"
[/vba]
Автор - krosav4ig
Дата добавления - 14.04.2019 в 17:45
Anis625 Дата: Воскресенье, 14.04.2019, 18:03 | Сообщение № 9
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
krosav4ig,

Крутяяяяяк, почти как надо получилось.

Не срабатывает только в двух случаях:
1-й мой косяк - оказывается в разных файлах написано по разному: "Ключевой проект:", "Ключевые проекты:" могут быть с пробелами. Попробовал сделать так:
[vba]
Код
Пользовательская = ((t)=>Table.Skip(t,List.PositionOf(Table.Column(t,"Column2"),"Ключев*")+1))(#"Строки с примененным фильтром")
[/vba]
Не срабатывает =( похоже нужно проверку на текст содержит.

2-й случай когда нет вообще такого слова - отображает всю таблицу целиком =)
 
Ответить
Сообщениеkrosav4ig,

Крутяяяяяк, почти как надо получилось.

Не срабатывает только в двух случаях:
1-й мой косяк - оказывается в разных файлах написано по разному: "Ключевой проект:", "Ключевые проекты:" могут быть с пробелами. Попробовал сделать так:
[vba]
Код
Пользовательская = ((t)=>Table.Skip(t,List.PositionOf(Table.Column(t,"Column2"),"Ключев*")+1))(#"Строки с примененным фильтром")
[/vba]
Не срабатывает =( похоже нужно проверку на текст содержит.

2-й случай когда нет вообще такого слова - отображает всю таблицу целиком =)
Автор - Anis625
Дата добавления - 14.04.2019 в 18:03
Anis625 Дата: Воскресенье, 14.04.2019, 18:09 | Сообщение № 10
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
хотя возможно текст содержит


Сообщение отредактировал Anis625 - Воскресенье, 14.04.2019, 18:13
 
Ответить
Сообщениехотя возможно текст содержит
Автор - Anis625
Дата добавления - 14.04.2019 в 18:09
krosav4ig Дата: Воскресенье, 14.04.2019, 18:15 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
[vba]
Код
let
    Источник = Excel.Workbook(File.Contents("F:\PQ\путь.xlsx"), null, true),
    #"Карта КПЭ_Sheet" = Источник{[Item="Карта КПЭ",Kind="Sheet"]}[Data],
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Карта КПЭ_Sheet",{"Column2"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Column2] <> null and [Column2] <> " " and [Column2] <> "Дата:" and [Column2] <> "Подпись Работника")),
    #"Added Custom" = Table.AddColumn(#"Строки с примененным фильтром", "Пользовательская", each if Text.Contains([Column2],"Ключев") and Text.Contains([Column2],"проект") then 1 else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Пользовательская"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Пользовательская] = 1)),
    #"Removed Top Rows" = Table.Skip(#"Filtered Rows",1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Пользовательская"})
in
    #"Removed Columns"
[/vba][p.s.]Table.FillDown - это Преобразование -> Заполнить -> Вниз

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Воскресенье, 14.04.2019, 18:17
 
Ответить
Сообщение[vba]
Код
let
    Источник = Excel.Workbook(File.Contents("F:\PQ\путь.xlsx"), null, true),
    #"Карта КПЭ_Sheet" = Источник{[Item="Карта КПЭ",Kind="Sheet"]}[Data],
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Карта КПЭ_Sheet",{"Column2"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Column2] <> null and [Column2] <> " " and [Column2] <> "Дата:" and [Column2] <> "Подпись Работника")),
    #"Added Custom" = Table.AddColumn(#"Строки с примененным фильтром", "Пользовательская", each if Text.Contains([Column2],"Ключев") and Text.Contains([Column2],"проект") then 1 else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Пользовательская"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Пользовательская] = 1)),
    #"Removed Top Rows" = Table.Skip(#"Filtered Rows",1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Пользовательская"})
in
    #"Removed Columns"
[/vba][p.s.]Table.FillDown - это Преобразование -> Заполнить -> Вниз
Автор - krosav4ig
Дата добавления - 14.04.2019 в 18:15
Anis625 Дата: Воскресенье, 14.04.2019, 19:03 | Сообщение № 12
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
krosav4ig,

Мдаааааааа, с заполнение вниз не догадался =)

Спасибо Вам ОГРОМНОЕ. PQ это настоящий кубик-рубика =)
 
Ответить
Сообщениеkrosav4ig,

Мдаааааааа, с заполнение вниз не догадался =)

Спасибо Вам ОГРОМНОЕ. PQ это настоящий кубик-рубика =)
Автор - Anis625
Дата добавления - 14.04.2019 в 19:03
krosav4ig Дата: Воскресенье, 14.04.2019, 19:37 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Anis625, Не, кубик-рубика - это DAX :)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
СообщениеAnis625, Не, кубик-рубика - это DAX :)
Автор - krosav4ig
Дата добавления - 14.04.2019 в 19:37
Мир MS Excel » Вопросы и решения » Вопросы по Excel » PQ извлечь строки после определенной строки (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!