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

Обновлено: 05.07.2024

в следующей таблице перечислены функции, которые Visual Basic предоставляет в Microsoft.VisualBasic.Strings классе для поиска и работы со строками. их можно рассматривать как Visual Basic встроенных функций; то есть вам не нужно вызывать их как явные члены класса, как показано в примерах. Дополнительные методы и в некоторых случаях дополняют методы, доступны в System.String классе.

Можно использовать инструкцию Option Compare , чтобы задать, сравниваются ли строки с использованием порядка сортировки текста без учета регистра, определенного языковым стандартом системы ( Text ) или внутренними двоичными представлениями символов ( Binary ). Метод сравнения текста по умолчанию — Binary .

Пример: Укасе

В данном примере функция UCase используется для возврата строки в верхнем регистре.

Пример: LTrim

В данном примере функция LTrim используется, чтобы убрать пробелы в начале, а функция RTrim — чтобы убрать пробелы в конце строковой переменной. Функция Trim в примере используется для удаления обоих типов пробелов.

Пример: mid

В этом примере Mid функция используется для возврата указанного числа символов из строки.

Пример: len

В данном примере Len используется для возврата числа знаков в строке.

Пример: InStr

В данном примере функция InStr используется для возврата позиции первого вхождения одной строки в другую.

Пример: Format

В данном примере показаны различные способы использования функции Format для форматирования значений с применением как форматов String , так и определенных пользователем форматов. Фактическое отображение системой разделителя даты ( / ), разделителя времени ( : и индикаторов AM/PM ( t и tt ) зависит от региональных параметров, применяемых кодом. При отображении времени и даты в среде разработки используется короткий формат времени и даты региональных установок кода.

Для языков, использующих 24-часовой формат, индикаторы AM/PM ( t и tt ) не отображаются.

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

Допустим, что у нас имеется база данных по клиентам, где одному названию компании может соответствовать несколько разных email'ов ее сотрудников. Наша задача состоит в том, чтобы собрать все адреса по названиям компаний и сцепить их (через запятую или точку с запятой), чтобы сделать потом, например, почтовую рассылку по клиентам, т.е. получить на выходе что-то похожее на:

Другими словами, нам нужен инструмент, который будет склеивать (сцеплять) текст по условию - аналог функции СУММЕСЛИ (SUMIF) , но для текста.

Способ 0. Формулой

Не очень изящный, зато самый простой способ. Можно написать несложную формулу, которая будет проверять отличается ли компания в очередной строке от предыдущей. Если не отличается, то приклеиваем через запятую очередной адрес. Если отличается, то "сбрасываем" накопленное, начиная заново:

Минусы такого подхода очевидны: из всех ячеек полученного дополнительного столбца нам нужны только последние по каждой компании (желтые). Если список большой, то чтобы их быстро отобрать придется добавить еще один столбец, использующий функцию ДЛСТР (LEN) , проверяющий длину накопленных строк:

Теперь можно отфильтровать единички и скопировать нужные склейки адресов для дальнейшего использования.

Способ 1. Макрофункция склейки по одному условию

Если исходный список не отсортирован по компаниям, то приведенная выше простая формула не работает, но можно легко выкрутиться с помощью небольшой пользовательской функции на VBA. Откройте редактор Visual Basic нажатием на сочетание клавиш Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer) . В открывшемся окне вставьте новый пустой модуль через меню Insert - Module и скопируйте туда текст нашей функции:

Если теперь вернуться в Microsoft Excel, то в списке функций (кнопка fx в строке формул или вкладка Формулы - Вставить функцию) можно будет найти нашу функцию MergeIf в категории Определенные пользователем (User Defined) . Аргументы у функции следующие:

Способ 2. Сцепить текст по неточному условию

Если заменить в 13-й строчке нашего макроса первый знак = на оператор приблизительного совпадения Like, то можно будет осуществлять склейку по неточному совпадению исходных данных с критерием отбора. Например, если название компании может быть записано в разных вариантах, то мы можем одной функцией проверить и собрать их все:

Поддерживаются стандартные спецсимволы подстановки:

По умолчанию оператор Like регистрочувствительный, т.е. понимает, например, "Орион" и "оРиОн" как разные компании. Чтобы не учитывать регистр можно добавить в самое начало модуля в редакторе Visual Basic строчку Option Compare Text, которая переключит Like в режим, когда он невосприимчив к регистру.

Таким образом можно составлять весьма сложные маски для проверки условий, например:

Способ 3. Макрофункция склейки текста по двум условиям

В работе может встретиться задача, когда сцеплять текст нужно больше, чем по одному условию. Например представим, что в нашей предыдущей таблице добавился еще один столбец с городом и склеивание нужно проводить не только для заданной компании, но еще и для заданного города. В этом случае нашу функцию придется немного модернизировать, добавив к ней проверку еще одного диапазона:

Применяться она будет совершенно аналогично - только аргументов теперь нужно указывать больше:

Способ 4. Группировка и склейка в Power Query

Решить проблему можно и без программирования на VBA, если использовать бесплатную надстройку Power Query. Для Excel 2010-2013 ее можно скачать здесь, а в Excel 2016 она уже встроена по умолчанию. Последовательность действий будет следующей:

Power Query не умеет работать с обычными таблицами, поэтому первым шагом превратим нашу таблицу в "умную". Для этого ее нужно выделить и нажать сочетание Ctrl + T или выбрать на вкладке Главная - Форматировать как таблицу (Home - Format as Table) . На появившейся затем вкладке Конструктор (Design) можно задать имя таблицы (я оставил стандартное Таблица1):

Теперь загрузим нашу таблицу в надстройку Power Query. Для этого на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013) жмем Из таблицы (Data - From Table) :

В открывшемся окне редактора запросов выделяем щелчком по заголовку столбец Компания и сверху жмем кнопку Группировать (Group By) . Вводим имя нового столбца и тип операции в группировке - Все строки (All Rows) :

Жмем ОК и получаем для каждой компании мини-таблицу сгруппированных значений. Содержимое таблиц хорошо видно, если щелкать левой кнопкой мыши в белый фон ячеек (не в текст!) в получившемся столбце:

Теперь добавим еще один столбец, где с помощью функции склеим через запятую содержимое столбцов Адрес в каждой из мини-таблиц. Для этого на вкладке Добавить столбец жмем Пользовательский столбец (Add column - Custom column) и в появившемся окне вводим имя нового столбца и формулу сцепки на встроенном в Power Query языке М:

Обратите внимание, что все М-функции регистрочувствительные (в отличие от Excel). После нажатия на ОК получаем новый столбец со склееными адресами:

Осталось удалить ненужный уже столбец ТаблАдресов (правой кнопкой мыши по заголовку - Удалить столбец) и выгрузить результаты на лист, нажав на вкладке Главная - Закрыть и загрузить (Home - Close and load) :

Важный нюанс : в отличие от предыдущих способов (функций), таблицы из Power Query не обновляются автоматически. Если в будущем произойдут какие-либо изменения в исходных данных, то нужно будет щелкнуть правой кнопкой в любое место таблицы результатов и выбрать команду Обновить (Refresh) .

Есть ли способ программно группировать / разгруппировать столбцы или строки в Excel 2010?

команда "вручную" находится в Data> Outline> Group / Ungroup
в Excel 2003 это работало: someSheet.columns(i).ShowDetail = True / False , но больше не работает в 2010 для групп (только для сводных таблиц и групп промежуточных итогов)
запись макроса не дает никакого кода, который я мог бы использовать

Точнее, вызов myRange.ShowDetail = True в Excel 2010 действительно расширяет свернутую группу, но вызывает ошибку, если группа уже развернута. А свойство ShowDetail возвращает True независимо от того, развернута группа или нет.

3 ответа

В Excel 2010 свойство ShowDetail всегда возвращает значение true для группы, независимо от того, свернута она или развернута. Вместо этого можно использовать свойство Hidden :

Что касается строк не в сводных таблицах . По моему опыту в Excel 2010 ShowDetail ВСЕГДА оценивается как True. Я думал, что это так, но не понимал, что мне нужно быть в итоговой строке, чтобы это свойство работало должным образом. Во-вторых, я не осознавал, что итоговая строка по умолчанию находится ПОД сгруппированными строками. Тестирование на свертывание / развертывание стало намного понятнее, когда я изменил этот параметр, чтобы строка сводки отображалась над сгруппированными строками (на ленте: Данные> Структура, Показать поле Outline Dlg).

Если выбранная мной ячейка находится в итоговой строке, ShowDetail получает значение True, если сгруппированные записи отображаются, и False, если это не так. Ключевым моментом для меня было нахождение в итоговой строке, чтобы увидеть, как это поведение работает таким образом. Наличие дочерних / сгруппированных строк выше по умолчанию действительно бросило меня.

Вот мой макрос, который динамически разворачивает и сворачивает сгруппированные записи, привязанные к итоговой строке, когда я выбираю ячейку в итоговой строке. И это делает мою ячейку в столбце A жирным шрифтом, если раздел расширен. Этот макрос не запускается, если я выбрал более одной ячейки.

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

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

Лучше всего просто изменить ширину столбца ячейки в скрытом диапазоне. Это автоматически разгруппирует выбор.

у меня есть код ниже, который, похоже, не работает. По существу, rngList относится к определенному диапазону имен в Excel, который составляет около 500 строк длиной и каждый n количество строк есть текст (есть примерно 32 строки из 500, которые имеют текст). Я пытаюсь перейти к непустым ячейкам (имитируя ctrl + down команда в Excel).

Я проверяю, пусты ли они, и если да, я хочу сгруппировать эту ячейку. Если он не пуст, я хочу проверить ячейка слева, и если она равна 0, я также хочу сгруппировать ее. Код, который у меня есть сейчас, по существу пытается сделать это, но я получаю ошибку ниже:

Group Method of Range Class Failed

затем он переходит к выделению следующей строки:

EDIT: скажем, вместо группировки пустых строк я хочу сгруппировать строки, в которых есть 1. Таким образом, crtl + down фактически перейдет в эту ячейку, а не в последнюю строку.

большое спасибо за помощь!

несмотря на возраст этого поста, я думал, что брошу свои два цента для любого, кто может наткнуться на него. Надеюсь я правильно понял ваш вопрос. Вот что я понял:--7-->

цель: для каждой строки в столбце интересов, группировать строки на основе критериев.

критерии только rows in the group являются те, которые либо не имеют значения (пустой, null, пустой) или имеют значение и имеют соседнюю ячейку (непосредственно слева), которая имеет значение 0. Единственное rows not in the group это те, которые не пустой и иметь соседнюю ячейку, которая не 0.

вот некоторые примеры данных:

Примечание: серии B1:B12 макияж названный диапазон rngList , как говорит ОП.

Данные Перед Запуском Макроса:

Данных После Запуска Макроса - Группировка Uncollapsed:

Данных После Запуска Макроса - Группировка Развалилась:

код, который обрабатывает это:

чтобы этот код работал: в VBE (редактор Visual Basic) откройте лист, содержащий данные для группировки (также содержит именованный диапазон rngList ) и вставьте этот код, затем запустите макрос.

Примечание: комментарии добавляются, чтобы объяснить некоторые части более подробно, хотя я считаю, что сам код написан таким образом, который может объяснить себя (например, имена переменных значимы, и логика имеет смысл).

я использовал код Сэма для группировки без использования столбца A. думал, что другие могут найти его полезным.

Читайте также: