Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1 2 След.
RSS
Подсчет строк в книге определенной заливки и определенного содержания
 
Приветствую всех!
Обращаюсь к вам за помощью в написании макроса или формулы для подсчета строк определенной заливки и содержания в книге excel.
Есть таблица, которая содержит заголовки и наименования. Более подробно и наглядно можно посмотреть в прикрепленном примере.
Количество заголовков всегда одинаково, но количество наименований под ними в каждом файле разное.
Справа от таблицы, в ячейках, я поместил примерный вид содержимого после работы макроса.
Необходимо, чтобы считались только те строки:
1. Которые не содержат ячеек с заголовком.
2. Которые окрашены в красный цвет (тот красный, который в примере).

Выражаясь проще, необходимо чтобы для каждого заголовка (ключ), мы получали количество красных строк (значение).
Как написать такой "проход", который начинает считать первую же строку под заголовком и останавливается, когда достигает следующего заголовка - ума не приложу.
 
строки раскрашиваются от фонаря или применяется что то более логическое
Лень двигатель прогресса, доказано!!!
 
Цитата
Как написать такой "проход", который начинает считать первую же строку под заголовком
Ищете слово Заголовок в столбцах A:G это будет начало диапазона, затем ищете следующее слово - это будет конец диапазона.
В этом диапазоне подсчитываете количество красных строк
 
Сергей, данные таблицы импортируются из 1С. В 1С данные берутся из базы, доступа к которой у меня нет.
Окрашиваются они по определенной логике.
Допустим, красная строка, это та позиция которой нет на складе, а желтая - та которая в пути на склад.
Изменено: mddg - 7 Дек 2018 12:25:28
 
Kuzmich, логику понимаю. Не хватает навыка в написании таких макросов.
Из программирования знаю только Java, но хотелось бы все сделать средствами Excel, без костылей.
 
mddg, дайте таблицу более близкую к реальности с наполнением что бы увидеть каким признаком определяется отсутствие или наличие на складе
Лень двигатель прогресса, доказано!!!
 
Сергей, определяется значением в базе данных. В самой таблице нет данных, которые разделяют желтые и красные строки кроме как цветом, к сожалению.
 
значит это точно не формулой засим откланиваюсь
Лень двигатель прогресса, доказано!!!
 
Есть еще у кого-нибудь идеи?)
 
можно конечно заморочиться с макрофункцией GET.CELL(28 и  GET.CELL(63 , но будет проще макрос, тем более что и в том и другом случае потребуется разрешить макросы.
Не стал строить вторую таблицу ограничился сводной.
 
две UDF
Формула (на основе UDF) для, например, O8:
=jjj_countif_by_color_and_indent_lvl(ИНДЕКС($A:$A;СТРОКА()+1):$A$29;jjj_GetInteriorColor($A8);2)
Считает ячейки ТОЛЬКО из первого столбца указанного диапазона с целевым цветом заливки и до первой ячейки с НЕ целевым отступом.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Реализация алгоритма, предложенного в сообщении #3
Код
Sub iЗаголовок()
Dim iLastRow As Long
Dim iText As String
Dim FRow As Integer
Dim ERow As Integer
Dim FoundCell As Range
Dim FAdr As String
Dim i As Integer
Dim n As Integer
    iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("N8:N" & iLastRow).ClearContents   'очищает столбец с результатом
  Set FoundCell = Columns("A:G").Find("Заголовок", , xlValues, xlPart)
   If Not FoundCell Is Nothing Then
      FAdr = FoundCell.Address            'адрес первого вхождения Заголовка
    Do
      iText = FoundCell.Text              'Заголовок с номером
      FRow = FoundCell.Row + 1            'первая строка после Заголовка
        Set FoundCell = Columns("A:G").Find("Заголовок", After:=FoundCell)
        If FoundCell.Address = FAdr Then
          ERow = iLastRow                 'последняя строка в диапазоне между Заголовками
        Else
          ERow = FoundCell.Row - 1
        End If
          n = 0
        For i = FRow To ERow              'цикл в диапазоне для подсчета количества окрашенных строк
          If Cells(i, "A").Interior.ColorIndex = Range("A8").Interior.ColorIndex Then n = n + 1
        Next
        Cells(FRow - 1, "N") = iText & " кол-во красных строк: " & n    'вывод результата в столбец N
    Loop While FoundCell.Address <> FAdr
   End If
End Sub
Результат в столбце N
 
Всем добрый вечер! Тоже заинтересовался этой задачкой, НО:
решил диапазон между заголовками определять по объединенным ячейкам (заголовки находятся в объединенных). Прописал цикл, в него - вложенный (от верхнего к нижнего заголовку) - и нифига не заработало, ничего понять не могу - не определяет объединенные и все, идет по всем строкам подряд и на объединенных не останавливается. Потом решил проверить: стер в редакторе весь код, скопировал на новый чистый лист значения и ширины столбцов, объединил и закрасил ячейки  в строках с заголовками, прописал простой цикл - и оказалось, что не работает именно на листе с примером от ТС, а на новом листе все работает. Лист с примером косячный? Или в чем еще может дело быть? Прикладываю пример (для обоих листов одинаковый макрос) но: на листе "TDSheet" (лист ТС-а) - этот макрос идет по всем строкам поочередно (не определяет MergeCells), а на листе "Пример" - все нормально, определяет. Конечно, может я не правильно с этим MergeCells обращаюсь, а если нет? В чем может быть причина?
Если в Excel такое возможно, так это ж получается можно неожиданно на очень большие неприятности нарваться  8-0
У меня Win 7  Excel 2007
 
  Игорь!
В примере от ТС Заголовок - объединенная ячейка из 7 ячеек, а Наименование - объединенная ячейка из 3 ячеек
Поэтому в цикле по столбцу А все ячейки объединенные.
На листе Пример Наименование - одна ячейка
 
Похоже задача интересна всем кроме ТС :-)
 
Kuzmich, у меня нет слов! Не знаю, наверное это природное - моя невнимательность и поспешность  :)  И сидел ведь, голову ломал! Логично  если заголовки - в объединенных, а далее - в обычных (но это по моей логике, не говорю вообще про "об.яч. - зло" и так уже много по этому поводу сказано).  Спасибо!!! А то уже хотел начать на Excel обижаться  :) А по объединенным  решил побегать, т.к. заголовки могут неизвестного содержания быть (навряд ли 1С выдает в отчетах заголовки типа "Заголовок 1", "Заголовок 2" и т.д).
БМВ, ну так это же хорошо! Значит мы живем и что-то делаем! Гораздо хуже, когда уже совсем ничего не интересно... А у ТС-а просто в данный момент времени другие интересы  :)
hardcoremaddog, просто для расширения моего кругозора: 1С при выгрузке в Excel умеет ячейки красить разноцветными красками?  
 
Цитата
_Igor_61 написал:
А по объединенным  решил побегать, т.к. заголовки могут неизвестного содержания быть
Можно цикл сделать по количеству (MergeArea.Count) объединенных ячеек, там 7, там 3
 
_Igor_61, Kuzmich, Для конкретного случая можно , но для общего опору делать на объединенные ячейки я б не стал. У 1C есть собственное мнение как выгрузить и бывает что в отчет вставлена шапка из применяемого фильтра и от её состава зависит объединение основной таблицы. ПО опыту на отступ или на группировку надежнее. Выше я на группировку сделал, так как не нашел получения уровня отступа через макрофункцию, Если через VBA то Ident level надежнее.
 
Цитата
БМВ написал:
так как не нашел получения уровня отступа через макрофункцию
это свойство OutLineLevel обьекта Range
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Цитата
Ігор Гончаренко написал:
OutLineLevel обьекта Range
нет, я про IndentLevel .  OutLineLevel - GET.CELL(28
 
Цитата
_Igor_61 написал: 1С при выгрузке в Excel умеет ячейки красить разноцветными красками?
Нет, не умеет))

Цитата
БМВ написал: Похоже задача интересна всем кроме ТС :-)
Был очень занят. Сейчас с удовольствием все прочту.
Спасибо за интерес к теме!))
 
Kuzmich, результат выполнения(
Видимо что-то делаю не так.
1.PNG (14.92 КБ)
 
hardcoremaddog, Вы все предложенные варианты опробовали?

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
JayBhagavan, пробую Ваш. Не могу разобраться почему ошибка.
В примере работает. С любой выгруженной таблицей из 1С - нет.
Изменено: hardcoremaddog - 11 Дек 2018 15:46:48
 
hardcoremaddog, потому что Вы используете пользовательскую функцию, которой нет в файле выгруженном из 1С. Скопируйте модуль из файла в персональную книгу макросов и вставьте UDF через вставку функции (Категория: Определённые пользователем). При желании, можете её переименовать в модуле как Вам удобно.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Цитата
hardcoremaddog написал:
Нет, не умеет
Вот потому и спрашивал! hardcoremaddog, исходя из этого возникает такое предположение: Вы получаете выгрузку, а потом вручную красите строки? Это так и надо? Очень сомневаюсь... Даже если и умеет - в любом случае лучше привязываться к самим данным, а не к краскам. Либо настроить саму выгрузку - (в которой указывается где эти товары находятся - на складе или по дороге к складу, хотя, исходя из подхода к задаче тут проблемы не только в выгрузке, но и с вводом данных в 1С) - был бы идеальный вариант.  
 
JayBhagavan, я предварительно скопировал модуль) Завтра попробую разобраться, спасибо.

_Igor_61, вручную я конечно ничего не расскрашиваю. Я думал, имеется в виду умеет ли она раскрашивать в другие цвета. По логике - да, но это настраивается непосредственно на сервере. У меня есть права только получать уже готовый документ на выходе. Доступа к настройке вида таблицы у меня нет.
Имена заголовков в каждом выгруженном файле одинаковы. Может тогда опираться на это? Заголовки, это грубо говоря наименования подразделений, а ниже уже строки с дефицитом. Только вот количество дефицитных позиций разное.
Есть, кстати, вариант отображения всех строк, не только дефицитных. Т.е. под каждым подразделением будет всегда фиксированное количество строк, но окрашены в зеленый, желтый или красный.
Изменено: hardcoremaddog - 11 Дек 2018 22:55:07
 
Kuzmich, разобрался как работает Ваш код. Благодарю за идею!
А возможна реализация поиска заголовка путем перебора известных заголовков?
В реальной таблице у меня 14 заголовков всегда с одинаковым названием.
 
В общем получилось заставить работать в любой таблице код от Kuzmich.
От себя добавил другим макросом добавление символа '_' в конце каждого заголовка, а в коде уже поиск этого символа.
Все работает исправно!))

Всех благодарю за помощь!
Изменено: hardcoremaddog - 12 Дек 2018 09:06:59
 
Короче, пока ничего не получается без пометки нужных ячеек (заголовков) символом, по которому и ищутся ячейки для цикла.
Для корректной работы помечать нужно вручную, т.к. случается так, что ячейки с заголовками находятся в разных документах, в разных местах. Это уже портит весь процесс автоматизации.
Искать объединенные, как я понял, тоже не вариант, т.к. там объединены не только заголовки.

Буду признателен, если кто-то подкинет еще идейку))
Изменено: hardcoremaddog - 12 Дек 2018 13:00:13
Страницы: 1 2 След.
Читают тему (гостей: 4)
Наверх