Elettracompany.com

Компьютерный справочник
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel vba группировка строк

Многоуровневая группировка строк

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

Предположим, что мы работаем вот с такой сложной многоуровневой таблицей с данными:

Обратите внимание на то, что в таблице ясно и четко прослеживаются вложенные друг в друга уровни данных. Такую структуру часто можно увидеть в:

  • бюджетировании — статьи доходов/расходов группируются в блоки (cost centers) или по географическому признаку (страна-край-город)
  • управлении проектами — этапы проектов разбиты обычно на более мелкие подзадачи и действия
  • строительных сметах — похожим образом обычно расписываются расчеты расхода материалов и их стоимости при строительстве
  • и т.д. — дальше придумайте сами.

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

Нажмите сочетание клавиш ALT+F11, чтобы открыть редактор Visual Basic. В нем выберите в меню команду Insert — Module, чтобы вставить новый модуль и скопируйте туда текст макроса:

При необходимости, текст можно слегка подкорректировать под ваши особенности, а именно изменить:

  • FIRST_ROW — номер первой строки списка, начиная с которой пойдет группировка. Если у вас шапка не из одной строки или над таблицей есть данные — меняйте.
  • FIRST_COLUMN — номер первого столбца списка, с которого начинается анализ и группировка. Если слева от вашей таблицы есть еще колонки, то эту константу также нужно изменить.
  • NUMBER_OF_LEVELS — количество уровней (столбцов) для анализа. В приведенном выше примере мы хотим проанализировать три первых столбца, поэтому значение этой константы =3

Важно! Макрос предполагает, что:

  • Уровни заполняются по порядку, т.е., например, уровень 3 не может быть написан, если ему не предшествовал уровень 2.
  • В первом столбце списка в последней строке должно быть слово Конец, которое необходимо, чтобы макрос понял, где заканчивается список и пора остановиться:

Чтобы запустить добавленный макрос для списка на текущем листе, нажмите сочетание клавиш ALT+F8, выберите в списке наш макрос Multilevel_Group и нажмите кнопку Выполнить (Run) .

Grouping Rows in VBA

I have the code below that doesn’t seem to be working. Essentially, rngList refers to a defined name range in Excel that is about 500 rows long and every n number of rows there is text (there are approximately 32 rows out of the 500 that have text). I am trying to go to the non-blank cells (by mimicking the ctrl + down command in Excel).

I am checking to see if they are blank, and if they are I want to group that cell. If it is not blank, I want to check the cell to the left and if it is 0, I also want to group it. The code I have now is essentially trying to do this but I am receiving the error below:

Group Method of Range Class Failed

It then goes on to highlight the following line:

Читать еще:  Как открыть таблицу excel

EDIT: Let’s say instead of grouping rows that are blank, I want to group rows that have 1 in them. That way the crtl + down will actually go to that cell rather than the last row.

Thank you very much for the help!

The code is below:

Создан 12 ноя. 12 2012-11-12 01:20:59 Max_1234

How can ‘Selection.Value = «»‘ when you’ve used ‘Selection.End(xlDown).Select’ to arrive at it? That should only happen for the very last row of the sheet. – Jamie Bull 12 ноя. 12 2012-11-12 01:46:37

Then how would I best approach this problem of grouping rows that have no entries in them? – Max_1234 12 ноя. 12 2012-11-12 01:53:58

ALso, right now the code is creating 8 different types of grouping. I just want it to all be grouped in one outline, not multiple seperate outlines. Thanks! – Max_1234 12 ноя. 12 2012-11-12 02:23:06

2 ответа

Despite the age of this post, I thought I’d throw in my two cents for anyone who might stumble upon it. I hope I understand your question correctly. Here’s what I’ve gathered:

Goal: For every row in the column of interest, group rows based on a criteria.

Criteria: The only rows in the group are those that either have no value (blank, null, empty) OR have a value AND have a neighboring cell (directly to the left) that has a value of 0. The only rows not in the group are those that are not blank and have a neighboring cell that is not 0.

Here is some sample data:

Note: the Range B1:B12 makeup the named range rngList , like the OP says they have.

Data Before Running Macro:

Data After Running Macro — Grouping Uncollapsed:

Data After Running Macro — Grouping Collapsed:

The code that handles this:

To make this code work: In the VBE (Visual Basic Editor), open the worksheet that contains the data to group (also contains the named range rngList ) and paste this code, then run the macro.

Note: The comments are added to explain certain parts in further detail, though I believe the code itself is written in a way that can explain itself (e.g. variable names are meaningful and logic makes sense).

Создан 19 фев. 13 2013-02-19 20:58:21 Sam

Сводные таблицы Excel

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

Хорошая новость заключается в том, что в Excel 2010 группировка данных в сводных таблицах облегчена до предела. Теперь вместо загадочных формул типа =А2+1-ДЕНЬ(А2) , применяемых для преобразования ежедневных сведений в ежемесячные данные, можно обратиться к возможностям группирования Excel, выбрав произвольный уровень группировки (по месяцам, кварталам и т.д.), кпримером може служиь группировка данных по количеству смс уведомлений своих клиентов отправленных за отчетный период.

Группировка по дате в VBA выполняется довольно нестандартно. Метод .Group может применяться только к единственной ячейке сводной таблицы, а эта ячейка должна включать дату либо надпись поля Дата. Как показано на рис. 12.10, можно выбрать либо заголовок Дата, отображенный в ячейке Р2, либо одну из дат в диапазоне Р3:АРМ3 . Первый метод довольно ненадежный, особенно если сводная таблица будет создаваться в новом столбце. Два других метода более надежны.

Читать еще:  Установка кодеков для просмотра видео

Если вы не собираетесь использовать различное число полей строк, можете обозначить заголовок Дата, находящийся в ячейке на пересечении строки 1 и столбца 3, как TableRange2. Эта ячейка может быть выделена с помощью
следующей строки кода:

Если количество полей строк будет изменяться, добавьте комментарий, в котором упоминается, что для изменения числа полей строк следует изменить 3 на иное число. Еще один метод выделения ячейки основан на использовании свойства LabelRange для поля Дата. Следующий код всегда выбирает ячейку, содержащую заголовок поля Дата:

Для группировки ежедневных дат в даты по годам следует определить сводную таблицу, в качестве поля строки которой используется поле Дата. Для гарантированного создания сводной таблицы отключите свойство ManualCalculation.

Для поиска подписи даты воспользуйтесь свойством LabelRange. К ячейке, содержащей подпись даты, примените метод .Group. Для аргумента Periods этого метода следует определить массив, включающий семь нулевых значений. Эти семь значений соответствуют секундам, минутам, часам, дням, месяцам, кварталам и годам. Например, для группировки по годам применяется следующий код.

После выполнения группировки по годам поле по-прежнему называется Дата. В этом заключается отличие от группировки по нескольким полям. Для группировки по месяцам, кварталам и годам используется следующий код.

После выполнения группировки по месяцам, кварталам и годам в поле Дата отображаются месяцы. В сводной таблице появляется два новых виртуальных поля: Кварталы и Годы.

Для группировки по неделям выбирается период Day (День), а затем используется аргумент By для группировки по периодам, состоящим из семи дней.

Поскольку показанная на рис. 12.10 сводная таблица будет группироваться по годам, используется следующий код.

На рис. 12.11 показана сводная таблица с датами продаж, сгруппированными по годам.

Рис. 12.11. Группировка по годам выполнена с помощью метода .Group

Excel vba группировка строк

Для данных типа String существует только одна операция — конкатенация (объединение). Например, результатом операции конкатенации трех строковых значений «Петр» & » » & «Иванович» будет строка «Петр Иванович». Возможно также использование другого оператора для операции конкатенации, например: «десяти» + «тысячник». Разница между этими выражениями состоит в том, что в первом случае операндами могут быть значения любого типа (они просто будут преобразовываться в строковые), а во втором — оба операнда должны иметь тип String.

Для работы со строками существует большое количество функций (таблица. Функции работы со строками).

Таблица «Функции работы со строками»

Для сравнения строковых значений можно использовать обычные операторы сравнения числовых значений, так как при сравнении символов сравниваются их двоичные коды.

Для сравнения строковых значений также применяется оператор Like, который позволяет обнаруживать неточное совпадение, например выражение «Входной сигнал» Like «Вход*» будет иметь значение True, так как сравниваемая строка начинается со слова «Вход». Символ звездочка (*) в строке заменяет произвольное число символов. Другие символы, которые обрабатываются оператором Like в сравниваемой строке:

  • ? — любой символ (один);
  • #- одна цифра (0-9);
  • [ ] — символ, совпадающий с одним из символов списка;
  • [! ] — символ, не совпадающий ни с одним из символов списка.

Следующие три функции позволяют работать с массивом строк

  • Split ( [, ]) — преобразует строку в массив подстрок. По умолчанию в качестве разделителя используется пробел. Данную функцию удобно использовать для разбиения предложения на слова. Однако можно указать в этой функции любой другой разделитель. Например, Split(3, «Это тестовое предложение») возвращает массив из трех строковых значений: «Это», «тестовое», «предложение».
  • Join ( [, ]) — преобразует массив строк в одну строку с указанным разделителем.
  • Filter ( , [, ] [, ]) — просматривает массив строковых значений и ищет в нем все подстроки, совпадающие с заданной строкой.
Читать еще:  Как убрать зеленые треугольники в excel

Эта функция имеет четыре аргумента:

  • — искомая строка;
  • — параметр (boolean значение), который указывает, будут ли возвращаемые строки включать искомую подстроку или, наоборот, возвращаться будут только те строки массива, которые не содержат искомой строки в качестве подстроки;
  • — параметр, определяющий метод сравнения строк.

Еще три функции обеспечивают преобразование строк:

  • LCase ( ) — преобразует все символы строки к нижнему регистру, например функция LCase(«ПОЧTA») возвращает строку «почта»;
  • UCase ( ) — преобразует все символы строки к верхнему регистру;
  • StrConv ( , ) — выполняет несколько типов преобразований строки в зависимости от второго параметра. Этот параметр описывается встроенными константами, например функция StrConv(«poccия», VbProperCase) возвращает значение «Россия».

И последние две функции генерируют строки символов

  • Space ( ) — создает строку, состоящую из указанного числа пробелов;
  • String ( , ) — создает строку, состоящую из указанного в первом аргументе числа символов. Сам символ указывается во втором аргументе.

Пример

Создать программу, работающую со строковыми переменными. Для этого создать форму, в метки которой выходят следующие сообщения:

1 метка: сообщается длина строки, введенной в первое текстовое поле (1 строка);

2 метка: преобразует все символы третьего текстового поля (3 строка) в заглавные буквы;

3 метка: выводит вместе содержание первого и второго текстовых полей (1 и 2 строки).

Хороший фреймворк (framework) лучшее решение для безпроблемной работы со строками.

Технология выполнения

  • Откройте приложение Word, сохраните документ и перейдите в редактор VBA.
  • Создайте форму аналогично приведенному рисунку.
  • Пропишите обработчик события кнопки ОК.
  • Откомпилируйте программу.
  • Запустите форму на выполнение.

Форма примера в режиме конструктора и в рабочем состоянии

Excel vba группировка строк

Давно Вывожу отчеты в Excel. Захотел фичу — группировка строк.

В макросе на VBA все выглядит так:

Rows(«24:26»).Select
Range(«A26»).Activate
Selection.Rows.Group

Как из Delphi достучаться до интерфейса Selection?
Или как-то еще запустить Group?


Романов Р.В. ( 2003-11-11 15:08 ) [1]

Selection это и есть Range по своей сути


Малиновский Владимир ( 2003-11-11 15:16 ) [2]

Романов Р.В. © (11.11.03 15:08) [1]
Selection это и есть Range по своей сути

Ну и как, запустить Group для Range?
Подскажите чайнику, оч-чень хочется.


Романов Р.В. ( 2003-11-11 15:26 ) [3]

В справке по VB написано


Малиновский Владимир ( 2003-11-11 15:27 ) [4]

В VB я умею! В Delphi надо!


Style ( 2003-11-11 15:31 ) [5]

uses
ActiveX, ComObj
.

var
ExcelApplication: variant;
begin
ExcelApplication := CreateOLEObject(«Excel.Application.8»);
ExcelApplication.WorkBooks.Add;
ExcelApplication.Visible := true;
ExcelApplication.Range[«A10:A20»].Select;
ExcelApplication.Range[«A10:A20»].Group;

А чего этот Group делает раз ты знаешь.


Малиновский Владимир ( 2003-11-11 15:53 ) [6]

Ну, спасибо. Group — выполняет группировку данных по
строкам/столбцам.
Сделай вот так
ExcelApplication.Range[«A10:A20»].Rows.Select;
видишь, кнопка появилась, если нажать на нее — строки сворачиваются.


Малиновский Владимир ( 2003-11-11 15:54 ) [7]


Малиновский Владимир ( 2003-11-11 16:03 ) [8]

Да, вот еще подказали — при работе с TExcelApplication и т.п.:

Всем спасибо ! Отчеты — просто блеск!

Ссылка на основную публикацию
Adblock
detector