VBA макрос объединения ячеек в столбце по условию

[alfatboy]

Добрый день!
Есть большая база данных.
Необходим макрос для объединения ячеек.
В столбце K есть разное количество объединенных ячеек (вкладка пример - выделил желтым), нужно объединить соседние ячейки по столбцам L, M, N, O, Z, AA (вкладка результат - выделил желтым).
При этом по возможности в столбце O оставлять при объединении самую крайнюю дату (например есть даты 23.11.2017 и 26.01.2018 нужно при объединении оставить 26.01.2018).

База очень большая и вручную этот процесс занимает много времени.
Заранее спасибо.
Сам в VBA к сожалению полный ноль.

[Serge 007]

Здравствуйте

...нужно объединить соседние ячейки по столбцам L, M, N, O, Z, AA...

А какой смысл в данном действии?
С таким результатом работать далее будет затруднительно, или, даже, невозможно, ведь объединенные ячейки дадут "кривую" статистику в случае попытки анализа

ИМХО - лучше отменить объединение имеющихся ячеек и использовать сводную (VBA не понадобится, всё средствами Excel)

[alfatboy]

Добрый день!
С одной стороны вы кончено правы, _сводные_ таблицы куда лучше, но тут свая специфика при заполнении базы.
Данную базу ведут с 2017 года и проще объединить соседние ячейки чем все переделывать.
Плюс я прописал необходимые мне формулы и вся необходимая информация считается правильно.
Единственное нужно объединить ячейки и все будет ОК.

[Serge 007]

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

А вот написать макрос, который будет объединять ячейки по условиям, с выбором максимальной даты - это полноценная работа, которая займет много (относительно другого варианта) времени

[alfatboy]

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

В данной базе объединенная ячейка это одно предписание в предписание может быть несколько нарушений (столбец I).
В статистике необходимо считать и количество предписаний (допустим всего 5 шт) и количество нарушений (допустим всего 15 шт).
У меня формула считает отдельно объединенные ячейки (предписания) и количество нарушений (столбец I).
Я пробовал перевести в сводную таблицу и я не знаю как там посчитать количество предписаний.

[Serge 007]

Посмотрите вложение
Для примера подробно всё разложил, одну и ту же сводную 4 раза продублировал (в работе будет одна):
1. Считает предписания
2. Считает нарушения
3. Показывает все нарушения в предписаниях
4. Показывает последние нарушения в предписаниях
Внешний вид сводной таблицы можно настроить, практически, какой угодно, просто перетаскивая мышкой поля и выбирая из контектного меню агрегаторы

PS Предварительная подготовка исходной таблицы заняла около 30 секунд
Если Вас устроит решение, я расскажу как это сделать

[alfatboy]

Решение интересное, но есть несколько вопросов.
У меня база должна иметь определенный вид (во вложении), менять его нельзя.
В шапке строка 1 - строка 52 считаются данные по фильтрам установленные на всех столбцах (тут какая фантазия у начальства по каким параметрам фильтровать).
Таких баз много и каждая в своей вкладке. Есть базы в которых более 2000 предписаний. И все заполнялись с объединением ячеек.
Сейчас необходимо автоматизировать счет данных.
Я для этого прописал формулы, но единственное что не считает правильно это когда столбцы  L - N не объединены.
Возможно ли улучшить данные базы (не меняя внешнего вида) при помощи сводных таблиц или заморочиться с объединением ячеек.

[Serge 007]

...не считает правильно это когда столбцы  L - N не объединены.

Где именно не считает правильно?
Укажите, пожалуйста, конкретные адреса ячеек

[alfatboy]

Во вкладке База 2 оригинал
В столбце Z прописал формулу чтоб считать разность между датами.
В столбце АА прописал условие исходя из результата столбца Z по которому считает предписания
- не устранено
- устранено в срок
- устранено с превышением срока
Кода протаскиваешь эти формулы то счет идет по всем датам.

Для наглядности поставил фильтр по одному предписанию.
В оригинале в стр. 5 "За отчетный период" показывает 4 предписания устранено с превышением, а по факту это 1 предписание. При объединении ячеек формула считает как одно "вкладка База 2 исп"

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

[Serge 007]

1. Т.е.по сути, Вам нужно так:

Код Выделить Развернуть

=СУММПРОИЗВ(--(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;ДВССЫЛ("h"&СТРОКА(A57:A186))))*(ЕТЕКСТ(K57:K186))), но с конкретизацией по датам?

2. Не могу понять принцип объединения дат. Почему из
Фактическая дата устранения нарушения:

18.10.2017

18.10.2017

16.02.2018

22.09.2017

получилось
22.10.2017?

Такой даты вообще нет в списке...

PS Сегодня отключаюсь, завтра утром, по возможности, вернусь в тему

[alfatboy]

Доброе утро!
Если я Вас еще не замучил своими вопросами.
То:
По 1 пункту
я так понял данная функция считает количество ячеек с текстом
По 2 пункту
22.10.2017 это опечатка, просто при объединении дату забивал руками.

Но суть проблемы у меня такая, что в шапке количество выданных предписаний 43 должно равняться сумме "устранено в срок" + "устранено с превышением срока" + "не устранено"
43 = 2+41+0
Но если ячейки в столбце АА не объединены по предписаниям, то получается счет идет по количеству нарушений, а не предписаний и отчет получается не верным
43 = 7+123+0
Имеется ли возможность в excel задать счет по предписаниям "устранено в срок" "устранено с превышением срока" "не устранено", а не по нарушениям.
Просто получается нужно в ручную либо объединять ячейки в столбце АА, либо удалят лишние значения, что занимает много времени.
Хотелось бы этот процесс как-то автоматизировать.

[Serge 007]

...нужно в ручную либо объединять ячейки в столбце АА, либо удалят лишние значения, что занимает много времени...

Ничего не надо вручную делать, используйте эту формулу в Е5:

Код Выделить Развернуть

=СУММПРОИЗВ(--(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;ДВССЫЛ("h"&СТРОКА(57:186))))*($AA57:$AA186=E3)*($K57:$K186<>""))В решении использовал формат ячеек столбцов Е,F,G 3-й строки

PS Так же поправил другие формулы

[alfatboy]

Serge 007, спасибо огромное!
Все работает как нужно!

[Serge 007]

Serge 007, спасибо огромное!

На здоровье 
И Вам спасибо за тему 

С наступающим Новым Годом!