Страницы: 1
RSS
Как переместить каждую вторую строку в столбец и удалить пустую ячейку + математические операции, Упрощение работы с СС от Youtube
 
Здравствуйте, я занимаюсь анализом речи во многих видео на ютубе (поиск определенных ключевых слов). Для этого я копирую "транскрипт" отдельно взятого видео в таблицу Эксель:
пример:
Входящие данные из ютуба:
Скрытый текст

Ключевые слова:
Скрытый текст

Вывод/цель:
Скрытый текст

Спасибо
 
Nikita Rocky, вариант в PQ. Наличие ключевых слов определяется без регистра (Music - ключевое слово, оставляем строки с music, Music, MUSIC и тд). Ищется точное совпадение слова (music - ключевое слово; строка, содержащая musics, не будет выводиться).
Код
let
  keywords = List.Buffer (
    Table.TransformColumns ( Excel.CurrentWorkbook(){[ Name = "keywords" ]}[Content], { "keywords", ( x ) => Text.Lower ( x ) } )[keywords]
  ),
  src = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
  split = List.Transform ( List.Split ( src[data], 2 ), ( x ) => { x{0} / 60 } & { x{1} } ),
  toTable = Table.FromRows ( split, type table [ timecode = time, text = text ] ),
  typed = Table.TransformColumnTypes ( toTable, { { "timecode", type time } } ),
  filter = Table.SelectRows (
    typed,
    each List.ContainsAny (
      Text.SplitAny ( Text.Lower ( [text] ), Text.Remove ( [text], { "0" .. "9", "A" .. "z", "А" .. "я", "[", "]" } ) ),
      keywords
    )
  )
in
  filter
 
Выделите диапазон с исходными данными. Запустите макрос FindKeywords.
В активной книге должен быть лист "Keywords".
Код
Option Explicit

Sub FindKeywords()
    Dim dicKeywords As Object
    Set dicKeywords = GetKeyword(Sheets("Keywords").Range("A1:A3"))
    If dicKeywords.Count > 0 Then
        Dim selection_usedrange As Range
        On Error Resume Next
        Set selection_usedrange = Intersect(Selection, ActiveSheet.UsedRange)
        On Error GoTo 0
        If Not selection_usedrange Is Nothing Then
            Dim arr As Variant
            If selection_usedrange.Cells.Count = 1 Then
                ReDim arr(1 To 1, 1 To 1)
                arr(1, 1) = selection_usedrange.Value
            Else
                arr = selection_usedrange
            End If
            
            Dim orr As Variant
            ReDim orr(1 To UBound(arr, 1), 1 To 2)
            Dim y As Long
            Dim u As Long
            For y = 1 To UBound(arr, 1) - 1
                If IsNumeric(arr(y, 1)) Then
                    If CheckKeywords(LCase(arr(y + 1, 1)), dicKeywords) Then
                        u = u + 1
                        orr(u, 1) = arr(y, 1) * 60 * 24
                        orr(u, 2) = arr(y + 1, 1)
                        y = y + 1
                    End If
                End If
            Next
            Erase arr
            Set dicKeywords = Nothing
            If u > 0 Then
                OutArr orr
            End If
        End If
    End If
End Sub

Sub OutArr(arr As Variant)
    With Workbooks.Add(1)
        With .Sheets(1).Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2))
            .Value = arr
        End With
        .Saved = True
    End With
End Sub

Function CheckKeywords(ByVal txt As String, dicKeywords As Object) As Boolean
    Dim v As Variant
    For Each v In dicKeywords
        If InStr(txt, v) > 0 Then
            CheckKeywords = True
            Exit For
        End If
    Next
End Function

Function GetKeyword(rn As Range) As Object
    Dim arr As Variant
    If rn.Cells.Count = 1 Then
        ReDim arr(1 To 1, 1 To 1)
        arr(1, 1) = rn.Value
    Else
        arr = rn
    End If
    
    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    'dic.CompareMode = 1
    
    Dim y As Long
    For y = 1 To UBound(arr, 1)
        If arr(y, 1) <> "" Then
            dic.Item(LCase(arr(y, 1))) = 0
        End If
    Next
    
    Set GetKeyword = dic
End Function
Изменено: МатросНаЗебре - 10.01.2022 14:14:00
 
поиск с листа Keywords + фильтр не пустых?
Изменено: andylu - 10.01.2022 14:26:00
 
Nikita Rocky,
Ещё вариант PQ
Код
let
    fn=(x)=>Excel.CurrentWorkbook(){[Name=x]}[Content],
    i = fn("Данные")[Столбец1],
    c = List.Distinct(List.RemoveNulls((Table.Column(Table.TransformColumns(fn("Keywords"),{{"Столбец1", Text.Upper, type text}}),"Столбец1")))),
    fn1 = (input) => List.First(List.Select(c, each Text.Contains(input,_))),
    r =  Table.FromRows(List.Split( i, 2 ) ),
    p = Table.TransformColumns(r, {{"Column1", each _ * 1440, type number}}),
    n = Table.AddColumn(p, "Наличие", each try fn1(Text.Upper([Column2]))otherwise null),
    f = Table.SelectRows(n, each [Наличие] <> null),
    u = Table.RemoveColumns(f,{"Наличие"})
in
    u
 
Дорогие surkenny, МатросНаЗебре, andylu, Msi2102 - пламенное Вам спасибо!
Завтра буду пробовать все варианты, очен очень Вам благодарен!
 
Цитата
написал:
Music - ключевое слово, оставляем строки с music, Music, MUSIC и тд). Ищется точное совпадение слова (music - ключевое слово; строка, содержащая musics, не будет выводиться).
Здравствуйте surkenny,

я приспособил Ваш вариант. Но есть вопрос как сделать так, чтобы поиск шел от части ключевого слова?
Например, сейчас он ищет точное совпадение игнорируя главные или заглавный буквы - пример:
Ключевое слово: "Беларус", поиск "" (нет совпадений)
Желаемый результат:
Ключевое слово: "Беларус", поиск "В Беларуси".... "Беларусь сегодня ..."

Я прикрепляю пример. Большое спасибо!
Изменено: Nikita Rocky - 20.01.2022 13:53:08
 
Nikita Rocky,
1. Уберите разрывы между строками в сообщении.
2. Можно так:
Код
let
  keywords = List.Buffer (
    Table.TransformColumns ( Excel.CurrentWorkbook(){[ Name = "keywords" ]}[Content], { "keywords", ( x ) => Text.Lower ( x ) } )[keywords]
  ),
  src = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
  split = List.Transform ( List.Split ( src[data], 2 ), ( x ) => { x{0} / 60 } & { x{1} } ),
  toTable = Table.FromRows ( split, type table [ timecode = time, text = text ] ),
  typed = Table.TransformColumnTypes ( toTable, { { "timecode", type time } } ),
  filter = Table.SelectRows (
    typed,
    each List.Accumulate ( keywords, 0, ( s, c ) => s + Number.From ( Text.Contains ( [text], c, Comparer.OrdinalIgnoreCase ) ) ) > 0
  )
in
  filter

Но будут "лишние" совпадения. К примеру, [.. Мда, чайник не вовремя ..] из-за того, что содержит время.
Изменено: surkenny - 20.01.2022 06:43:07
 
Цитата
написал:
Но будут "лишние" совпадения. К примеру, [.. Мда, чайник не вовремя ..] из-за того, что содержит время.
Да, я понимаю. Большое спасибо!

Кстати, раз такая пьянка пошла - можно ли область data автоматически выделять и получать данные из других листов? Например, указывая номер листа на главном рабочем листе (в данном примере 45 или 46)? Тогда мне не нужно будет постоянно копировать данные на главный "Лист1" (смотрите пример)

Благодарю заранее!  
Изменено: Nikita Rocky - 20.01.2022 13:59:11
 
Nikita Rocky, можно так. Но!!! Если добавляете листы или меняете на них данные, нужно сохранить файл перед обновлением запроса.
Код
let
  keywords = List.Buffer (
    Table.TransformColumns ( Excel.CurrentWorkbook(){[ Name = "keywords" ]}[Content], { "keywords", ( x ) => Text.Lower ( x ) } )[keywords]
  ),
  parameters = Record.FromList (
    Excel.CurrentWorkbook(){[ Name = "parameters" ]}[Content][Значение],
    Excel.CurrentWorkbook(){[ Name = "parameters" ]}[Content][Параметр]
  ),
  src = Excel.Workbook ( File.Contents ( parameters[Путь к файлу] ) ){[ Item = Text.From ( parameters[Лист] ), Kind = "Sheet" ]}[Data],
  split = List.Transform ( List.Split ( Table.Column ( src, Table.ColumnNames ( src ){0} ), 2 ), ( x ) => { Number.From ( x{0} ) / 60 } & { x{1} } ),
  toTable = Table.FromRows ( split, type table [ timecode = time, text = text ] ),
  typed = Table.TransformColumnTypes ( toTable, { { "timecode", type time } } ),
  filter = Table.SelectRows (
    typed,
    each List.ContainsAny (
      Text.SplitAny ( Text.Lower ( [text] ), Text.Remove ( [text], { "0" .. "9", "A" .. "z", "А" .. "я", "[", "]" } ) ),
      keywords
    )
  )
in
  filter
Изменено: surkenny - 20.01.2022 14:47:34
 
Nikita Rocky, объединил код:
Код
let
  keywords = List.Buffer (
    Table.TransformColumns ( Excel.CurrentWorkbook(){[ Name = "keywords" ]}[Content], { "keywords", ( x ) => Text.Lower ( x ) } )[keywords]
  ),
  parameters = Record.FromList (
    Excel.CurrentWorkbook(){[ Name = "parameters" ]}[Content][Значение],
    Excel.CurrentWorkbook(){[ Name = "parameters" ]}[Content][Параметр]
  ),
  src = Excel.Workbook ( File.Contents ( parameters[Путь к файлу] ) ){[ Item = Text.From ( parameters[Лист] ), Kind = "Sheet" ]}[Data],
  split = List.Transform (
    List.Split ( Table.Column ( src, Table.ColumnNames ( src ){0} ), 2 ),
    ( x ) => { Time.From ( Number.From ( x{0} ) / 60 ) } & { x{1} }
  ),
  toTable = Table.FromRows ( split, type table [ timecode = time, text = text ] ),
  filter = Table.SelectRows (
    toTable,
    each List.Accumulate ( keywords, 0, ( s, c ) => s + Number.From ( Text.Contains ( [text], c, Comparer.OrdinalIgnoreCase ) ) ) > 0
  )
in
  filter

Как открыть редактор PQ, можно прочитать ТУТ
 
Огромнейшее спасибо! К сожалению, что-то ни так со временем - в столбце timecode значения времен, которые вовсе не соответствуют тексту. Где загвоздка?  
 
Nikita Rocky, потому что новые данные в другом формате. Жирную часть замените
split = List.Transform ( List.Split ( Table.Column ( src, Table.ColumnNames ( src ){0} ), 2 ), ( x ) => { Time.From ( x{0} ) } & { x{1} } )
Код
let
  keywords = List.Buffer (
    Table.TransformColumns ( Excel.CurrentWorkbook(){[ Name = "keywords" ]}[Content], { "keywords", ( x ) => Text.Lower ( x ) } )[keywords]
  ),
  parameters = Record.FromList (
    Excel.CurrentWorkbook(){[ Name = "parameters" ]}[Content][Значение],
    Excel.CurrentWorkbook(){[ Name = "parameters" ]}[Content][Параметр]
  ),
  src = Excel.Workbook ( File.Contents ( parameters[Путь к файлу] ) ){[ Item = Text.From ( parameters[Лист] ), Kind = "Sheet" ]}[Data],
  split = List.Transform ( List.Split ( Table.Column ( src, Table.ColumnNames ( src ){0} ), 2 ), ( x ) => { Time.From ( x{0} ) } & { x{1} } ),
  toTable = Table.FromRows ( split, type table [ timecode = time, text = text ] ),
  filter = Table.SelectRows (
    toTable,
    each List.Accumulate ( keywords, 0, ( s, c ) => s + Number.From ( Text.Contains ( [text], c, Comparer.OrdinalIgnoreCase ) ) ) > 0
  )
in
  filter
Изменено: surkenny - 21.01.2022 13:49:50
 
Цитата
написал: Жирную часть замените
Уважаемый surkenny, заменить Time.From ( x{0}.. на Text.From ( x{0}.. ?

Я посмотрел на информацию, которую Вы мне прислали. Я пытаюсь понять в каком разделе я могу увидеть код, который мне стоит изменить, но так и не понимаю, где он находится. По этой ссылке я читал информацию, но тут показано как создать новое соединение, а не как посмотреть уже существующее.

Когда я нахожусь в Power Query Editor какие конкретные шаги мне нужно выполнить?

П.С. Я все-таки докопался до кода

В версии, где конверсия работала код был следующим:
Код
= List.Transform ( List.Split ( Table.Column ( src, Table.ColumnNames ( src ){0} ), 2 ), ( x ) => { Time.From ( x{0} ) } & { x{1} } )   

а как выглядит он сейчас:

Код
= List.Transform (    List.Split ( Table.Column ( src, Table.ColumnNames ( src ){0} ), 2 ),    ( x ) => { Time.From ( Number.From ( x{0} ) / 60 ) } & { x{1} }  )

В общем я колдую и скоро найду ответ. Век живи, век учись...

Изменено: Nikita Rocky - 21.01.2022 15:08:21
 
Nikita Rocky, нужно заменить Time.From ( Number.From ( x{0} ) / 60 ) на Time.From ( x{0} )

 
Да, понял, огромное спасибо. Я просмотрел несколько видео в ютубе, но все объясняли сложные, продвинутые операции.

Осталась следующая проблема:
При копировании транскрипта из ютуба и вставке в Excel он интерпретирует MM:SS как HH:MM:SS или даже (в перемешку с) 01/01/1900  00:00:00.
Таким образом существую следующие артефакты (смотрите скрины). Так как скопированных листов 1000+ менять формат данных мне представляется довольно проблематичным. Я пытался решить это простым делением на 60, но к сожалению с такими ячейками это не работает.

Есть ли оптимально простое решение решить такую проблему?

 
Как excel должен определить, что Вы неверно указали время. В ячейке hh:mm, а на самом деле это mm:ss? А бывает, что в ячейке mm:ss и менять ничего не нужно.
Вообще, с 99% точностью можно решить логически. Если во всем списке ss всегда равны 00, то формат перепутан и нужно разделить на 60.
Можно ещё проще - ввести параметр в таблице excel делить на 60 да/нет. Если видно, что в итоге ошибка - меняем параметр и обновляемой запрос.
Вот Вам и домашнее задание :)
Мне кажется, я уже достаточно помог в Вашем вопросе.
Изменено: surkenny - 22.01.2022 12:58:31
 
Да, более чем! Спасибо Вам за огромную работу. Снимаю перед Вами виртуальную шляпу.
Страницы: 1
Наверх