Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск ячеек, которые (не)соответствуют определенным условиям. VBA Excel.
 
#1
06.04.2021 19:09:11
Добрый день!

Есть таблица в Эксель. 9 столбцов, примерно 8 тыс. строк, в потенциале 20 тыс. Каждый столбец должен заполняться по определенным правилам. Задача – проанализировать таблицу и скопировать на другой лист все строки, в которых есть ошибки с указанием ошибки или списка ошибок, если в одной строке их несколько. Из 9 столбцов – только 5 подлежат проверке, в остальных содержаться формулы.

Пока планирую методом перебора каждой строки в цикле. Примерно так. Это набросок, а не реальный код.

Код
If Cells(i, “a”) <> Cells(i-1,”b”) Then CodeErr = "Значение А не равно предыдущему значению В"
ErrNar = СodeErr

If Cells(i, “с”) <> 1330 Then CodeErr = "Неправильное значение С"
    If ErrNar <> 0 Then
    ErrNar = ErrNar + ", " + СodeErr
    Else
    ErrNar = CodeErr
    End If

If Cells(i, “d”) = 0 Then CodeErr = "Значение D равно нулю"
    If ErrNar <> 0 Then
    ErrNar = ErrNar + ", " + CodeErr
    Else
    ErrNar = CodeErr
    End If

If ErrNar <> 0 Then
MsgBox "Список ошибок в строке " & i & “: “& ErrNar
Else
MsgBox "Нет ошибок в строке "& i
End If

CodeErr=0
ErrNar = 0


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

Заранее большое спасибо.
 
 
 
#2
06.04.2021 19:33:35
Работа с объктами листа медленная. Работайте с массивами.
Проверять только нужные столбцы.
Передавать управление функции, которая будет возвращать True, если в строке есть ошибки. После отработки функции записывать номер строки (или данные строки) в массив ошибок.

Код
    aData = Range(...).Value
    aColumns = Array(1, 3, 5, 6, 8)
    
    For i = 1 To UBound(aData)
        For j = 0 To 4
            If fChecking (aData, i, aColumns(j))= True Then
                ' заполняем масив ошибок
            End If
        Next j
    Next i
'  ..................
End Sub

Function fChecking(aData(), i As Long, ByVal p As Long) As Boolean
    Dim bErr As Boolean
    
    Select Case p
    Case 1: ' проверка1
    Case 2: ' проверка2
    ......
    End Select
    
    fChecking = bErr
End Function

Массив ошибок выгрузить на лист
Не забывайте объявлять переменные
 
 
 
#3
06.04.2021 19:56:58
avbook, а покажите файл и 10-20 строк что есть и что должно быть на выходе)...у меня есть такое примерно на работе заполняется 5 столбцов на каждый месяц и получается у меня для проверки идут 240 столбцов и 1500 строк т.е. 360 тысяч ячеек, все они проверяются в моем случае на правильность заполнения (формата данных)...и есл ошибка у меня на отдельный лист выводится список ячеек с ошибками и ссылку для бысттрого перехода к ней...
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#4
06.04.2021 20:50:52
vikttur, спасибо за совет.

Одно уточнение, пожалуйста. Мне следовало бы сразу сказать обо всех деталях. Извините, не хотел заранее «грузить».
Проверка ячеек идет не только по числам, но и по тексту: ищем ячейки с пробелами в начале или в конце, и еще ищем пустые ячейки. А также для двух числовых столбцов выполняется проверка, что число из нее входит в массив допустимых значений. Я выложил файл, где есть выдержка из таблицы. В следующем сообщении.

Это как-то меняет подход к решению задачи?

Mershik, В моем случае – это можно назвать TimeSheet. Пример и пояснения в файле. Я веду его уже год с небольшим. Сейчас стал анализировать (графики, сводные таблицы) и обнаружил ошибки. Конечно, можно все поправить руками. Но мне стало интересно – как решить эту задачу макросом. Появилась мотивация начать изучать макросы. За год в реальном файле уже 7975 строк.
 
 
 
#5
06.04.2021 21:42:03
avbook, описание классное) много задач тое понятно - а в каком виде все это в результате должно быть? закрасить цветом? пока для раздумий
Код
Sub mrshkei()
Dim arr, i As Long, j As Long
arr = Range("A4:I21")

For j = LBound(arr) To UBound(arr)
    'Что делал
    If Left(arr(j, 6), 1) = " " Or Right(arr(j, 6), 1) = " " Or InStr(arr(j, 6), "  ") > 0 Then
        Cells(j + 3, 6).Interior.ColorIndex = 3 ' пока просто закрашивание
    End If
    'Группа работ
    If Left(arr(j, 7), 1) = " " Or Right(arr(j, 7), 1) = " " Or InStr(arr(j, 7), "  ") > 0 Or arr(j, 7) = Empty Then
        Cells(j + 3, 7).Interior.ColorIndex = 3
    End If
    '
Next j

End Sub
Изменено: Mershik - 06.04.2021 21:56:32
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#6
06.04.2021 22:08:29
спасибо за комплимент,

Цитата
Mershik написал: а в каком виде все это в результате должно быть?
В моих планах было сделать следующее:
1) Все строки с ошибками скопировать на отдельный лист, в каждой строке справа добавить ячейку с тестовым описанием ошибок. Пример: «Длительность равна 0:00, Лишние пробелы в Поле «Группа работ».
2) В исходном листе заливкой указать все ячейки(!) с ошибками.
3) В исходном листе выбрать Автофильтр по Столбцу «Строка», т.е показать все ошибки. Установить курсор в начало такой отфильтрованной таблицы. Примечание: Автофильтр можно установить по номерам строк. Номер строки планировал взять из соответствующего столбца, т.е. не адрес ячейки.
3.1) При каждом запуске макроса чистить вышеуказанную заливку. И устанавливать Автофильтр на исходную таблицу в режиме «Показать Все».
4) Если ошибок нет – лист ошибок не создавать. Установить курсор в последнюю строку исходной таблицы.

Здесь, Вы можете заметить, есть дубляж: и отдельно ошибки на листе, и еще Автофильтр строк с ошибками в исходной таблице.
 
 
 
#7
06.04.2021 22:09:45
Цитата
Mershik написал:
пока для раздумий
и спасибо за пример.

PS Конечная цель, проанализировать, как я использую свое время. Сколько в % времени уходит на тот или иной вид работ, как эти %% зависят от дня недели и\или времени суток, и др. В планах сделать что-то типа Dashbord: сводные таблицы, графики. Возможно, в дальнейшем – визуализация в BI…
Изменено: avbook - 06.04.2021 22:29:21
 
 
 
#8
06.04.2021 23:54:56
Цитата
avbook написал: Это как-то меняет подход к решению задачи?
Совершенно не меняет. Меняет только типы провкрок (в функции, если говорить о варианте, предложенном мной)

Цитата
Mershik написал: много задач
А в этом разделе "один вопрос - одна тема". Вы спросили, как  в общем построить алгоритм. ответ Вы получили. Может, по структуре или построению кода еще ответы будут.
Но решать конкретные задачи - или в отдельных темах, или, если комплексно - в разделе Работа
 
 
 
#9
07.04.2021 00:14:50
Цитата
vikttur написал:
Совершенно не меняет.
Ок. Спасибо.

Цитата
vikttur написал:
А в этом разделе "один вопрос - одна тема".
Понятно.
 
 
 
Страницы: 1
Наверх