У меня есть таблица в excel, и я хочу создать макрос в VBA для группировать строки с тем же значением в столбце F и выделить группу заголовков жирным шрифтом.
Другими словами, все строки с одинаковым значением в столбце F должны быть группами в одну строку с выделенной жирным шрифтом. Возможно ли это?
Это то, что у меня есть:
Фактические данные
Это то, что я хочу:
Большое спасибо, я буду очень благодарен
1 ответ
Я считаю более привлекательным визуальное выделение только самой верхней строки, но вы запросили все строки, так что это объяснит, как это сделать в обоих направлениях. Я на 100% уверен, что это крайне неэффективно, но обеспечивает минимальное изменение существующего кода и обеспечивает желаемый результат.
Для обеих версий вам потребуется еще одна переменная диапазона. Технически это не нужно, но мне не хотелось заниматься умственной гимнастикой с i и j
И почти в самом низу вставить условный тест и форматирование.
Чтобы отформатировать только самую верхнюю строку в группе, добавьте else и форматирование — НЕ ИСПОЛЬЗУЙТЕ ЭТОТ СЕГМЕНТ, ЕСЛИ ВЫ ХОТИТЕ ВЫДЕЛИТЬ ВСЕ СТРОКИ
Чтобы отформатировать каждую строку в группе, объявите еще одну итеративную переменную и используйте вместо этого этот сегмент
Один из этих двух вариантов должен получить вас куда вы хотите пойти.
Описание проблемы
Программисты 1С любят создавать отчеты Excel, которые содержат группировку строк. С одной стороны это достаточно удобно, однако появляется ряд проблем:
- Сложно скопировать строки определенного уровня, так как при этом отображаются уровни выше.
- При копировании строк определенного уровня копируются также и скрытые уровни.
- При протягивании формул они распространяются и на скрытые уровни.
Как помогает функция
С помощью функции можно заполнить отдельный столбец, который содержит нумерацию группировки строк. А далее установить фильтр по данному столбцу. При этом описанных выше проблем не будет.
Функция =УРОВЕНЬСТРОКИ(ЯЧЕЙКА) вернет уровень группировки строки (от 1 до 8) в указанной ячейке.
Если у вас есть список данных, которые вы хотите сгруппировать и суммировать, вы можете создать структуру до восьми уровней. Каждый внутренний уровень, представленный большим числом в символы структуры, отображает детали для предыдущего внешнего уровня, представленного меньшим числом в символах структуры. Используйте структуру для быстрого отображения сводных строк или столбцов, а также для отображения подробных данных для каждой группы. Вы можете создать структуру строк (как показано в приведенном ниже примере), структуру столбцов или структуру строк и столбцов.
 |
1. чтобы отобразить строки для уровня, щелкните соответствующие символы структуры 
.
2. Уровень 1 содержит сумму продаж по всем строкам с подробными данными.
3. Уровень 2 содержит сумму продаж по каждому месяцу в каждом регионе.
4. уровень 3 включает в себя строки данных — в этом случае строки от 17 до 20.
5. чтобы развернуть или свернуть данные в структуре, щелкните символы структуры 
и 
или нажмите клавиши Alt + Shift + = , чтобы развернуть и Alt + Shift + = для свертывания.
Убедитесь, что каждый столбец данных, на который вы хотите создать структуру, имеет метку в первой строке (например, область), содержит похожие факты в каждом столбце, и что в диапазоне, который вы хотите структурировать, нет пустых строк или столбцов.
При желании в строках с группировкой данных может быть соответствующая итоговая строка — промежуточный итог. Чтобы создать эти данные, выполните одно из указанных ниже действий.
Вставка итоговых строк при помощи команды Промежуточные итоги
Используйте команду Итоги для вставки функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ непосредственно под или над каждой группой строк с подробными данными и для автоматического создания многоуровневой структуры. Подробнее об использовании функции "Итоги" читайте в статье Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Вставка собственных итоговых строк
Вставьте собственные итоговые строки с формулами непосредственно под или над каждой группой строк с подробными данными. Например, под (или над) строками данных о продажах за март и апрель используйте функцию СУММ, чтобы подсчитать промежуточные итоговые данные по продажам за эти месяцы. Этот пример показан в таблице далее в этой статье.
По умолчанию Excel ищет сводные строки под подробными сведениями, но их можно создавать над строками с подробными данными. Если вы создали сводные строки под подробными сведениями, перейдите к следующему шагу (шаг 4). Если вы создали итоговые строки над строками с подробными сведениями, на вкладке данные в группе Структура нажмите кнопку вызова диалогового окна.
Откроется диалоговое окно "Параметры".
В диалоговом окне Параметры снимите флажок суммарные строки под данными и нажмите кнопку ОК.
Структурируйте данные. Выполните одно из перечисленных ниже действий.
Автоматическое структурирование данных
Выделите ячейку в диапазоне ячеек, которые нужно структурировать.
На вкладке Данные в группе Структура щелкните стрелку рядом с пунктом Группировать, а затем выберите пункт Создать структуру.
Структурирование данных вручную
Важно: Группируя уровни структуры вручную, отобразите на экране все данные во избежание ошибок группировки строк.
Чтобы структурировать внешнюю группу (уровень 1), выделите все строки, которые будет содержать внешняя группа (например, строки с подробными сведениями и, если они были добавлены, суммарные строки).
1. Первая строка содержит надписи и не выделена.
2. так как это внешняя группа, выделите все строки с промежуточными и подробными данными.
3. не выделять общие итоги.
На вкладке Данные в группе Структура выберите Группировать. В диалоговом окне Группа выберите пункт строки, а затем нажмите кнопку ОК.
Совет: Если выделять целые строки, а не только ячейки, Excel автоматически группирует по строкам — диалоговое окно "Группировка" не открывается.
Рядом с группой на экране появятся знаки структуры.
Или структурируйте внутреннюю вложенную группу — строки с данными для определенного раздела данных.
Примечание: Если вы не хотите создавать внутренние группы, перейдите к шагу f, показанному ниже.
Для каждой внутренней вложенной группы выберите строки с подробными данными, соответствующие итоговой строке.
1. Вы можете создать несколько групп на каждом из внутренних уровней. Здесь два раздела уже сгруппированы на уровне 2.
2. Этот раздел выбран и готов к группировке.
3. не выделять итоговую строку для данных, которые нужно сгруппировать.
На вкладке Данные в группе Структура выберите Группировать.
В диалоговом окне Группа выберите пункт строки, а затем нажмите кнопку ОК. Рядом с группой на экране появятся знаки структуры.
Совет: Если выделять целые строки, а не только ячейки, Excel автоматически группирует по строкам — диалоговое окно "Группировка" не открывается.
Продолжайте выделение и группировку внутренних строк до тех пор, пока не будут созданы все необходимые уровни структуры.
Чтобы разгруппировать строки, выделите их, а затем на вкладке Данные в группе Структура выберите команду Разгруппировать.
Вы также можете разгруппировать разделы структуры без удаления всего уровня. Удерживая нажатой клавишу SHIFT, щелкните 
или 
группы, а затем на вкладке данные в группе Структура нажмите кнопку Разгруппировать.
Важно: Если во время разгруппировки структуры подробные данные скрыты, строки с подробными данными могут также не отображаться. Для отображения данных перетащите указатель через номера видимых строк рядом со скрытыми строками. Затем на вкладке Главная в группе ячейки нажмите кнопку Формат, наведите указатель на пункт Скрыть & отобразить, а затем выберите команду отобразить строки.
Убедитесь в том, что в первом столбце каждой строки данных, для которых требуется создать структуру, имеется заголовок, что каждая строка содержит данные одного типа и что в диапазоне данных отсутствуют пустые строки или столбцы.
Вставьте собственные итоговые столбцы с формулами непосредственно справа или слева от каждой группы строк с подробными данными. В таблице, приведенной в шаге 4, показан пример.
Примечание: Для структурирования данных по столбцам обязательно наличие итоговых столбцов с формулами, которые ссылаются на ячейки в каждом из столбцов с подробными данными этой группы.
Если итоговый столбец расположен слева от столбцов сведений, на вкладке данные в группе Структура нажмите кнопку вызова диалогового окна.
Откроется диалоговое окно "Параметры".
В диалоговом окне Параметры снимите флажок суммарные столбцы справа от подробных данных и нажмите кнопку ОК.
Чтобы структурировать данные, выполните одно из перечисленных ниже действий.
Автоматическое структурирование данных
Выделите ячейку в диапазоне.
На вкладке Данные в группе Структура щелкните стрелку под пунктом Группировать, а затем выберите Создать структуру.
Структурирование данных вручную
Важно: Группируя уровни структуры вручную, отобразите на экране все данные во избежание ошибок при группировке столбцов.
Чтобы структурировать внешнюю группу (уровень 1), выделите все подчиненные итоговые столбцы, а также связанные с ними подробные данные.
1. столбец A со заголовками.
2. Выделите все столбцы детализации и промежуточные итоги. Обратите внимание, что если не выделять столбцы целиком, то при нажатии кнопки Группировать (на вкладке данные в группе Структура ) откроется диалоговое окно Группа и попросят выбрать строки или столбцы.
3. не выбирайте столбец общий итог.
На вкладке Данные в группе Структура выберите Группировать.
Над группой появится символ структуры.
Чтобы структурировать внутреннюю вложенную группу столбцов с подробными сведениями (уровень 2 или выше), выделите столбцы с подробными сведениями, расположенные рядом с столбцом, содержащим итоговый столбец.
1. Вы можете создать несколько групп на каждом из внутренних уровней. Здесь два раздела уже сгруппированы на уровне 2.
2. Эти столбцы выделены и готовы к группировке. Обратите внимание, что если не выделять столбцы целиком, то при нажатии кнопки Группировать (на вкладке данные в группе Структура ) откроется диалоговое окно Группа и попросят выбрать строки или столбцы.
3. не выделять столбец сводки для данных, которые нужно сгруппировать.
На вкладке Данные в группе Структура выберите Группировать.
Рядом с группой на экране появятся знаки структуры.
Продолжайте выделение и группировку внутренних столбцов до тех пор, пока не будут созданы все необходимые уровни структуры.
Для разгруппировки столбцов выделите необходимые столбцы, а затем на вкладке Данные в группе Структура нажмите кнопку Разгруппировать.
Вы также можете разгруппировать разделы структуры без удаления всего уровня. Удерживая нажатой клавишу SHIFT, щелкните или группы, а затем на вкладке данные в группе Структура нажмите кнопку Разгруппировать.
Если во время разгруппировки структуры подробные данные скрыты, столбцы с подробными данными могут также не отображаться. Для отображения данных перетащите указатель через номера видимых столбцов рядом со скрытыми столбцами. На вкладке Главная в группе Ячейки нажмите кнопку Формат, выберите пункт Скрыть или отобразить, а затем нажмите кнопку Отображение столбцов
Если вы не видите символы структуры , и , выберите параметры > файлов> Дополнительно, а затем в разделе Параметры отображения для этого листа установите флажок Показывать символы структуры при применении структуры и нажмите кнопку ОК.
Выполните одно или несколько из указанных ниже действий.
Отображение или скрытие подробных данных для группы
Чтобы отобразить подробные данные в группе, нажмите кнопку для группы или нажмите клавиши Alt + Shift + =.
Чтобы скрыть подробные данные группы, нажмите кнопку для группы или нажмите клавиши Alt + Shift +-.
Развертывание или свертывание структуры до определенного уровня
В символы структуры щелкните номер нужного уровня. Подробные данные более низких уровней будут скрыты.
Например, если в структуре четыре уровня, можно скрыть четвертый уровень, нажав кнопку 
, при этом оставшиеся три уровня будут отображаться.
Отображение или скрытие всех структурированных данных
Чтобы отобразить все детали данных, щелкните самый низкий уровень в символы структуры. Например, если в структуре три уровня, нажмите .
Чтобы скрыть все подробные данные, нажмите 
.
Для структурированных строк в Microsoft Excel используются стили, например RowLevel_1 и RowLevel_2. Для структурированных столбцов в Excel используются стили, например ColLevel_1 и ColLevel_2. В этих стилях используются полужирное начертание, курсив и другие текстовые форматы, позволяющие отличать итоговые строки или столбцы данных. Изменяя способ определения каждого из стилей, вы можете применять различные форматы текста и ячеек для настройки внешнего вида структуры. Стиль можно применить к структуре при ее создании или создании структуры.
Выполните одно или несколько из указанных ниже действий.
Автоматическое применение стиля к новым итоговым строкам или столбцам
На вкладке данные в группе Структура нажмите кнопку вызова диалогового окна.
Откроется диалоговое окно "Параметры".
Установите флажок Автоматические стили.
Назначение стиля существующей итоговой строке или столбцу
Выделите ячейки, к которым вы хотите применить стиль.
На вкладке данные в группе Структура нажмите кнопку вызова диалогового окна.
Откроется диалоговое окно "Параметры".
Установите флажок Автоматические стили и нажмите кнопку Применить стили.
Для форматирования структурированных данных также можно применять автоформаты.
Если вы не видите символы структуры , и , выберите параметры > файлов> Дополнительно, а затем в разделе Параметры отображения для этого листа установите флажок Показывать символы структуры при применении структуры .
Используйте символы структуры , и для скрытия подробных данных, которые не нужно копировать.
Выделите диапазон итоговых строк.
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
Нажмите кнопку Выделить группу ячеек.
Выберите пункт только видимые ячейки.
Нажмите кнопку ОК, а затем скопируйте данные.
