Профессиональные приемы работы в Microsoft Excel

Пожалуйста, войдите или зарегистрируйтесь.


Расширенный поиск  

Новости:

К первому сообщению темы должен быть прикреплен файл примера в формате xls*.
Приложив пример, Вы избавите себя и других от вопросов типа "А какой критерий?", "А куда выводить результат?", "А сколько строк?" и все тех же просьб выложить файл. Рисовать за Вас Ваши же таблички с заданиями, а затем и решение к ним, никто желанием не горит. Да и, как показывает практика, в большинстве случаев без файла решения не найти.

Автор Тема: VBA макрос объединения ячеек в столбце по условию  (Прочитано 624 раз)

0 Пользователей и 1 Гость просматривают эту тему.

alfatboy

  • Пользователь
  • **
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 20

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

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

Serge 007

  • Администратор
  • Ветеран
  • *****
  • Уважение: +341/-0
  • Оффлайн Оффлайн
  • Сообщений: 3 033
    • Мир Excel

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

...нужно объединить соседние ячейки по столбцам L, M, N, O, Z, AA...
А какой смысл в данном действии?
С таким результатом работать далее будет затруднительно, или, даже, невозможно, ведь объединенные ячейки дадут "кривую" статистику в случае попытки анализа

ИМХО - лучше отменить объединение имеющихся ячеек и использовать сводную (VBA не понадобится, всё средствами Excel)
Записан
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Яндекс-деньги: 41001419691823 | WMR:126292472390

alfatboy

  • Пользователь
  • **
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 20

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

Serge 007

  • Администратор
  • Ветеран
  • *****
  • Уважение: +341/-0
  • Оффлайн Оффлайн
  • Сообщений: 3 033
    • Мир Excel

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

А вот написать макрос, который будет объединять ячейки по условиям, с выбором максимальной даты - это полноценная работа, которая займет много (относительно другого варианта) времени
Записан
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Яндекс-деньги: 41001419691823 | WMR:126292472390

alfatboy

  • Пользователь
  • **
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 20

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

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

Записан

Serge 007

  • Администратор
  • Ветеран
  • *****
  • Уважение: +341/-0
  • Оффлайн Оффлайн
  • Сообщений: 3 033
    • Мир Excel

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

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

Записан
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Яндекс-деньги: 41001419691823 | WMR:126292472390

alfatboy

  • Пользователь
  • **
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 20

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

Serge 007

  • Администратор
  • Ветеран
  • *****
  • Уважение: +341/-0
  • Оффлайн Оффлайн
  • Сообщений: 3 033
    • Мир Excel

...не считает правильно это когда столбцы  L - N не объединены.
Где именно не считает правильно?
Укажите, пожалуйста, конкретные адреса ячеек
Записан
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Яндекс-деньги: 41001419691823 | WMR:126292472390

alfatboy

  • Пользователь
  • **
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 20

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

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

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

Serge 007

  • Администратор
  • Ветеран
  • *****
  • Уважение: +341/-0
  • Оффлайн Оффлайн
  • Сообщений: 3 033
    • Мир Excel

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

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

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

PS Сегодня отключаюсь, завтра утром, по возможности, вернусь в тему
Записан
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Яндекс-деньги: 41001419691823 | WMR:126292472390

alfatboy

  • Пользователь
  • **
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 20

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

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

Serge 007

  • Администратор
  • Ветеран
  • *****
  • Уважение: +341/-0
  • Оффлайн Оффлайн
  • Сообщений: 3 033
    • Мир Excel

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

PS Так же поправил другие формулы
Записан
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Яндекс-деньги: 41001419691823 | WMR:126292472390

alfatboy

  • Пользователь
  • **
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 20

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

Serge 007

  • Администратор
  • Ветеран
  • *****
  • Уважение: +341/-0
  • Оффлайн Оффлайн
  • Сообщений: 3 033
    • Мир Excel

Serge 007, спасибо огромное!
На здоровье  ;)
И Вам спасибо за тему  :D

С наступающим Новым Годом!
Записан
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Яндекс-деньги: 41001419691823 | WMR:126292472390
 



Темы без ответов

09.08.2019 14:09 Макрос для заполнения таблиц через форму 4998
18.07.2019 16:02 Рассылка почты из Excel при помощи почтовой программы TheBAT! 4413
07.02.2019 01:36 Как удалить дубликаты из выпадающего связанного списка? 5972
03.03.2018 00:00 Подсчет отработанного времени, за исключением заранее определенных перерывов 3117
23.05.2017 11:20 Копирование данных из одной таблицы в умную таблицу по условию 4917
15.03.2017 15:45 автозамена картинок PowerPoint 3838
11.03.2017 13:43 Изменить нумерацию страниц 3764
07.02.2017 18:43 Блокировка ячеек по наступлению даты 2880
28.08.2016 19:29 Одинаковые заголовки после обновления оглавления 3379
07.08.2016 17:33 Определить нумерацию как элемент стиля 3594





Яндекс цитирования msexcel.ru Яндекс.Метрика

Страница сгенерирована за 0.144 секунд. Запросов: 124.