Страницы: 1
RSS
Удаление строки по двум условиям
 
Добрый день,

в результате обработки выгрузки из 1С в PQ я столкнулся с проблемой - на выходе появились задвоившиеся города в первом столбце. То есть, например есть "город1" в столбце А,  и в столбце В есть настоящая итоговая сумма по нему за период (месяц).  И ниже строкой идет тот же "город1", но сумма в столбце В уже меньше итоговой.

Я в целом прикинул как, должен работать такой макрос на VBA, но реализовать его не могу(( (если значение ячейки = значению вышестоящей ячейки И сумма по этой же строке меньше чем в вышестоящей, то строку удалить )
средствами PQ тоже не могу убрать лишние строки.

Просьба помочь написать такой макрос. Файл прилагаю
 
Yum, попробуйте так
Код
Sub udalit_stroki()
' (если значение ячейки = значению вышестоящей ячейки
'И сумма по этой же строке меньше чем в вышестоящей, то строку удалить )
Dim ilastrow As Integer
Dim i As Integer
ilastrow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = ilastrow To 2 Step -1
        If Cells(i, 1) = Cells(i - 1, 1) And Cells(i, 2) < Cells(i - 1, 2) Then
        Rows(i).Delete
        End If
    Next i
End Sub
Не бойтесь совершенства. Вам его не достичь.
 
Mershik, по моему, все работает прекрасно! большое спасибо


как же хочется подтянуть VBA для такого :)  
 
Цитата
Mershik написал:
As Integer
ох, как это не практично...А если строк вдруг будет больше 32767? Такие переменные лучше объявлять As Long изначально.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
А можно вообще об Integer забыть :)
 
Дмитрий(The_Prist) Щербаков, vikttur, в следующий раз double буду))) и тогда удаление строк по одной так же не очень практично).

ЗЫ я только учусь...спасибо за практические советы
Код
Sub udalit_stroki()
' (если значение ячейки = значению вышестоящей ячейки
'И сумма по этой же строке меньше чем в вышестоящей, то строку удалить )
Dim ilastrow As Double
Dim i As Double
Dim rr As Range
Dim arr

ilastrow = Cells(Rows.Count, 1).End(xlUp).Row
arr = Range(Cells(2, 1), Cells(ilastrow, 1))
    For i = ilastrow To 2 Step -1
        If Cells(i, 1) = Cells(i - 1, 1) And Cells(i, 2) < Cells(i - 1, 2) Then
        If rr Is Nothing Then
                Set rr = Cells(i, 1)
            Else
                Set rr = Union(rr, Cells(i, 1))
            End If
        End If
    Next i
    If Not rr Is Nothing Then rr.EntireRow.Delete
End Sub
Изменено: Mershik - 16.02.2020 18:25:00
Не бойтесь совершенства. Вам его не достичь.
 
Mershik, спасибо за улучшение кода  :)  но можете прокомментировать смысл каждой новой строки ?
 
Цитата
Yum написал:
из 1С в PQ
А не проще ли все, в т. ч. удаление дубликатов, в PQ сделать?
 
Yum, в старом коде идет удаление каждой строки, т.е. если совпало условие удаляем строку и идем дальше проверять выше, снова выполнились условия и снова удалили... каждое удаление строки кушает ресурсы т.е. нужно нужно екселю внести изменения в номера строк так как они изменились и т.д. и т.п.

а эта часть кода позволяет так сказать аккумулировать все строки и после  за 1 раз удалить их т.е. когда будет у вас много строк для удаление 1 макрос будет выполнятся на порядок дольше...можно проверить)

Ради интереса проверил, размножил вашу таблицу до 25 тысяч строк...
1 макрос работал 266 секунд
2 макрос 22 ...
 
Код
If rr Is Nothing Then  Set rr = Cells(i, 1)
Else
Set rr = Union(rr, Cells(i, 1))
End If
Изменено: Mershik - 18.02.2020 14:15:39
Не бойтесь совершенства. Вам его не достичь.
 
Murderface_, в PQ удаление по двум условиям я еще не научился делать. А Вы сможете написать алгоритм для такой же операции в PQ ?
 
Mershik, еще раз спасибо, буду пробовать!
 
Yum, приложите небольшой пример с вашим запросом PQ.
 
PQ  ;)  :
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], 
    to = Table.Group(from, {"Column1"}, {{"Column5", each List.Max([Column5]), type number}})
in
    to
Соблюдение правил форума не освобождает от модераторского произвола
 
Mershik, зачем задавать arr, если нигде потом не используете? А если использовать - можно получить еще больший прирост в скорости:
Код
Sub udalit_stroki()
' (если значение ячейки = значению вышестоящей ячейки
'И сумма по этой же строке меньше чем в вышестоящей, то строку удалить )

'и все же лучше Lond, т.к. тип Row по умолчанию имеет именно этот тип, а не Double
'в итоге каждое обращение с таким типом приводит к необходимости доп.преобразований в нужный тип
Dim ilastrow As Long
Dim i As Long
Dim rr As Range
Dim arr
 
ilastrow = Cells(Rows.Count, 1).End(xlUp).Row
arr = Cells(1, 1).Resize(ilastrow, 2).Value
    For i = ilastrow To 2 Step -1
        If arr(i, 1) = arr(i - 1, 1) And arr(i, 2) < arr(i - 1, 2) Then
            If rr Is Nothing Then
                Set rr = Cells(i, 1)
            Else
                Set rr = Union(rr, Cells(i, 1))
            End If
        End If
    Next i
    If Not rr Is Nothing Then rr.EntireRow.Delete
End Sub
P.S. а вообще очень знакомо все выглядит, особенно имена переменных  :D  
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
buchlotnik, точно уверены в результате? Ведь нужна проверка с отсылкой на предыдущую строку. Не уверен, что нужны именно максимальные значения(вдруг нужно оставить строки, если наименование одинаковое, но строкой ниже идет сумма БОЛЬШЕ) - ваш запрос удалить первое и оставит второе. Хотя автору виднее, правильно ли это. На всякий случай вариант именно по работе с предыдущими строками:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Column1", type text}, {"Column5", type number}}),
    indx = Table.AddIndexColumn(#"Измененный тип", "indx", 0, 1),
    cond = Table.AddColumn(indx, "cond", each 
        if [indx] > 0 then
        if [Column1]=Источник{[indx]-1}[Column1] and [Column5]<Источник{[indx]-1}[Column5] then 
            1 
            else 0
        else 0),
    filter = Table.SelectRows(cond, each ([cond] = 0)),
    res = Table.RemoveColumns(filter,{"cond", "indx"})
in
    res
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков, Ваше решение очень подходит, спасибо!
Страницы: 1
Наверх