Вопросы по созданию шаблонов Excel

[Михаил Александрович]

Всем доброго времени суток. Назрел такой вопрос: при формировании документа есть необходимость отсортировать данные на разные листы по значению переключателя (На 1 лист улетают данные, если свалка ликвидирована, на второй - если действующая).


Если в шаблоне изначально между шапкой и телом данных вставить строку и ячейку ликвидирована/действующая на 1 и 2 листе соответственно (чтобы в окошке фильтра появилось это значение), включить фильтр, то при создании документа листы остаются пустыми (при включенном фильтре).


Вариант каждый раз ставить фильтр в руне, делать два отчета и сшивать вместе не комильфо, при этом на основе этих двух листов создается третий сводный, который включает в себя данные по количеству (по сути на скрытых вкладках Excel сформировать нужные столбцы, отфильтровать и суммировать значения либо посчитать функцией счет()).

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

И сразу не отходя далеко вопрос по условному форматированию в шаблоне. По какой то причине при попытке окрасить диапазон ячеек при создании документа красит только первую ячейку диапазона, а остальные нет (при чем если в уже сформированном документе просто удалить ломаное правило на строке и сделать абсолютно такое же то оно красит весь диапазон). Если прописывать правило на каждую ячейку то работает, в чем может быть причина?

Всем заранее спасибо и успехов!

 

[Vladimir]

Добрый день, Михаил Александрович.
Пока в конструкторе нет возможности разбить дочерний список, или выводить в разных местах данные одного списка - такая возможность появится в 7-ой версии.
А сейчас можно попробовать использовать вариант с удалением строк с заданным статусом. Недавно обсуждался вопрос удаления записей из таблицы, который использовать для решения данной задачи: Текстовое выражение денежной суммы.

И сразу не отходя далеко вопрос по условному форматированию в шаблоне.

Попробуйте задать формат не одной ячейке, в которой вписано поле из Руны, а этой и ниже расположенной ячейке, которая расположена на строке с завершающим тегом.
Дело может быть в том, что когда Руна добавляет строку в блоке - при этом формат для ячеек добавленной строки берётся из нижней строки, а не из верхней.

 

[Михаил Александрович]

Спасибо за наводку, разобрался с макросами, тема конечно не за пять минут осваивается, но все что хотел, сделал. Проблему с условным форматированием тоже решил макросом, просто прописал перебор ячеек 1го столбца, куда вывожу условие, составляю список на выделение а по окончанию все выделенное окрашиваю. проблему с фильтрацией решил также, только вместо выделения - удаление, и фильтр не один, а два, как и переменных, тк выводится на два листа. по окончании макрос удаляет столбец с условием, и все по красоте.

 

[Михаил Александрович]

Решил кстати проблему еще в субботу, и допиливал помаленьку.

Заметил интересную особенность, может будет полезно кому то:

Если выводить данные списком (не одну запись, а весь объект или форму связи как таблицу) в РАЗНЫЕ листы эксель (у меня например на 3 листа: на первом для подсчета сводного отчета, на втором ликвидированные, на третьем действующие, ) и ПРИ ЭТОМ в одном листе есть данные, которых нет в другом (у меня на втором листе это пару столбцов по ликвидации свалок, которых не должно быть в третьем) то он их просто не выводит. при этом на первом листе для свода вообще всего несколько столбцов из формы и все выводилось, а на втором хрен!

Промучился два вечера, а решение заметил вообще случайно)))

Когда перекинул первый лист (где почти нет данных) в конец, то случилось следующее: в остальных листах просто исчезли данные, которых в нем нет, а вместо них название полей в скобках [ ].

И получается что заполнение данных происходит с последнего листа, т.е. справа налево.
остается просто переставить самый информативный лист в конец и забыть о проблеме. а если нужен определенный порядок листов, то три варианта:
1. Создать полную таблицу на последнем листе и каждый раз удалять
2. Написать макрос который это сделает сам
3. Или как я сделал: в самую крайнюю таблицу вывести все, а ненужные столбцы удалить макросом

 

[Михаил Александрович]

Sub runa()
         Dim ra As Range, delra As Range, Фильтр_1 As String              ' переменные для первого листа
    Application.ScreenUpdating = False                                    ' отключение обновления экрана для ускорения
    Sheets("Ликвидированные-заросшие").Activate                           'переход на первый лист
    Фильтр_1 = "Действующая"                                              ' установка фильтра №1
        For Each ra In ActiveSheet.UsedRange.Columns(1).Cells             ' перебор строк 1 столбца
        If Not ra.Find(Фильтр_1, , xlValues, xlPart) Is Nothing Then      ' если в строке найден текст по фильтру
            If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)   ' то добавляем строку в список на удаление
        End If
    Next
    If Not delra Is Nothing Then delra.EntireRow.Delete                   ' Удаляем отфильтрованные строки
            ' ТУТ ВАРИАНТ С ПОСЛЕДОВАТЕЛЬНЫМ УДАЛЕНИЕМ, ТОЕСТЬ НАШЕЛ-УДАЛИЛ,
            ' НО ЕСЛИ ПОДРЯД ИДУТ ДВЕ СРОКИ ПОДХОДЯЩИЕ ПО ФИЛЬТРУ, ТО ВТОРУЮ ОН ПРОПУСКАЕТ
'            For Each cell In ActiveSheet.UsedRange.Columns(1).Cells
'                If cell.Value = "Действующая" Then cell.EntireRow.Delete
'            Next
'            For Each cell In ActiveSheet.UsedRange.Columns(1).Cells
'                If cell.Value = "Действующая" Then cell.EntireRow.Delete
'
'            Next
    Columns("A").Delete                                                     ' удаление столбца с фильтром
        Dim fa As Range, delfa As Range, Фильтр_2 As String                 ' переменные для второго листа
    Sheets("Действующие").Activate                                          'переход на второй лист
    Фильтр_2 = "Ликвидированна"                                             'установка фильтра №2
    For Each fa In ActiveSheet.UsedRange.Columns(1).Cells                   ' перебор строк 1 столбца
        If Not fa.Find(Фильтр_2, , xlValues, xlPart) Is Nothing Then        ' если в строке найден текст по фильтру
            If delfa Is Nothing Then Set delfa = fa Else Set delfa = Union(delfa, fa)   ' то добавляем строку в список на удаление
        End If
    Next
    If Not delfa Is Nothing Then delfa.EntireRow.Delete                 ' Удаляем отфильтрованные строки
    Columns("A").Delete                                                 ' удаление столбца с фильтром
    '   ДАЛЕЕ ИДЕТ КОД НА УДАЛЕНИЕ ЛИШНИХ СТОЛБЦОВ НА ВКЛАДКЕ ДЕЙСТВУЮЩИЕ - ТОЕСТЬ НА САМОМ ПРАВОМ ЛИСТЕ
    Sheets("Действующие").Activate
    Columns("S:U").Delete
    ' КОНЕЦ УДАЛЕНИЯ
    Sheets("Сводная информация").Activate                               ' переход на главный (в моем случае) лист
    Application.ScreenUpdating = True                                   ' включение обновления экрана
End Sub

 

[Михаил Александрович]

Это был макрос на удаление ненужных строк на двух листах и удаление лишних столбцов в крайнем правом листе.

А это макрос на окрас подходящих по условию ячейки первого столбца строк (так же перебирает строки, только в конце окрашивает в красный, а не удаляет):

Sub runa()
         Dim ra As Range, delra As Range, Фильтр As String              ' переменные
    Application.ScreenUpdating = False                                  ' откл. обновления экрана
    Фильтр = "Нет"                                                      ' утановка фильтра
    For Each ra In ActiveSheet.UsedRange.Columns(1).Cells               ' перебор строк 1 столбца
        If Not ra.Find(Фильтр, , xlValues, xlPart) Is Nothing Then      ' если в строке найден текст по фильтру
            If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)   ' то добавляем строку в список на окрас
        End If
    Next
    If Not delra Is Nothing Then delra.EntireRow.Interior.Color = 255   ' Красим строки
    Columns("A").Delete                                                 ' удаляем столбец с фильтром
    Application.ScreenUpdating = True                                   ' вкл. обновление экрана
End Sub

 

[Ирина]

Здравствуйте!
Как круто, кода пользователи делятся своими решениями! Спасибо Вам.
Руна - это такой механизм, что не всегда сразу приходят решения нужных задач, но как показала практика, всё реализуемо, поэтому опыт других пользователей очень ценен.

P.S. Я без макросов реализовала подкраску нужных строк и ячеек через условное форматирование (пример в теме "Поделиться: закрасить строку и ячейки в шаблоне Excal по условию").
Всё в итоге проще - просто при прописывании правила (условное форматирование) в шаблоне нужно выделить весь диапазон данных (всю таблицу) кроме шапки.
Если в шаблоне только одна строка (кроме шапки), то выделяем только её. При формировании исходного документа форматирование распространится на все строки и ячейки по заданному условию, подкрасив весь нужный диапазон.
Спасибо

​

 

[Anti]

Если выводить данные списком (не одну запись, а весь объект или форму связи как таблицу) в РАЗНЫЕ листы эксель (у меня например на 3 листа: на первом для подсчета сводного отчета, на втором ликвидированные, на третьем действующие, ) и ПРИ ЭТОМ в одном листе есть данные, которых нет в другом (у меня на втором листе это пару столбцов по ликвидации свалок, которых не должно быть в третьем) то он их просто не выводит. при этом на первом листе для свода вообще всего несколько столбцов из формы и все выводилось, а на втором хрен!

Добрый день, Михаил Александрович!
Данная ситуация вызвана ошибкой в программе, которую мы уже нашли. Исправим в следующем обновлении.
Изящное решение с использованием макроса runa, которое Вы нашли, можно будет использовать до устранения проблемы.
Благодарю Вас за то, что поделились им с форумчанами!