Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Интерактивный график с макросами и СУММПРОИЗВ - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Интерактивный график с макросами и СУММПРОИЗВ (Макросы/Sub)
Интерактивный график с макросами и СУММПРОИЗВ
Невилл Дата: Четверг, 24.03.2016, 15:08 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
Добрый день всем! Нужна Ваша помощь с файлом.
Сразу предупреждаю, вопрос непростой.

В общем, суть такова:
1) Сделал файл для учета заявок на разные виды сервисных работ.
2) Кроме самой "базы данных" на первом листе, добавил 3 интерактивных графика (суточный, 4-часовой и месячный)
Каждый из них в идеале должен цветными ячейками отображать время или период, когда запланированы те или иные работы в строке нужной бригады.
3) При щелчке (активации) этой цветной ячейки нужно показать справа от графика все сведения по строке из таблицы первого листа, которая соответствует этому квадрату.
4) Если в какой-либо строке указано дату переноса работы, в графике стрелкой отображается, на какой период она сдвинута.
5) На макросы фильтрации и рисования линий можете не обращать внимание (работают правильно), а Worksheet_SelectionChange может и стоит глянуть, чтобы наглядней было то, о чем я написал.

В общем, набросал файл и макросы, но столкнулся с парой непродуманных проблем (на примере, 4 февраля):

1) Для цветового выделения выбрал метод условного форматирования и функцию СУММПРОИЗВ.
В большинстве случаев срабатывало правильно, но при "накладке" (два переноса в один день) на месячном графике мой вариант формул выдает не те результаты, что хотелось бы.
Может, Вы подскажете, как мне добиться нужного результата?

Сейчас пробую безуспешно for each rCell in Range, но не могу правильно задать алгоритм в ВБА.

2) Зеленоватые ячейки справа от каждого графика:
Вывод информации изначально реализовал через ИНДЕКС+ПОИСКПОЗ с привязкой к ключевому полю (НомерБрогады&Дата).
Теперь, когда появилась необходимость учитывать заявки чаще 2 раз в сутки такой подход не работает.

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

ИТОГ:
Мне нужно как-то перекроить имеющийся набор графиков, чтобы при выделении любой ячейки можно было получать полный набор данных по этому заказу и допились до ума СУММПРОИЗВ (либо заменить на любой другой способ), желательно не запоров сильно быстродействие.
А свои идеи пока иссякли.
Был бы очень рад любому совету.

P.S. Простите, если слишком запутанно объяснил %)
К сообщению приложен файл: __1.0.1--.xlsm(99Kb)
 
Ответить
СообщениеДобрый день всем! Нужна Ваша помощь с файлом.
Сразу предупреждаю, вопрос непростой.

В общем, суть такова:
1) Сделал файл для учета заявок на разные виды сервисных работ.
2) Кроме самой "базы данных" на первом листе, добавил 3 интерактивных графика (суточный, 4-часовой и месячный)
Каждый из них в идеале должен цветными ячейками отображать время или период, когда запланированы те или иные работы в строке нужной бригады.
3) При щелчке (активации) этой цветной ячейки нужно показать справа от графика все сведения по строке из таблицы первого листа, которая соответствует этому квадрату.
4) Если в какой-либо строке указано дату переноса работы, в графике стрелкой отображается, на какой период она сдвинута.
5) На макросы фильтрации и рисования линий можете не обращать внимание (работают правильно), а Worksheet_SelectionChange может и стоит глянуть, чтобы наглядней было то, о чем я написал.

В общем, набросал файл и макросы, но столкнулся с парой непродуманных проблем (на примере, 4 февраля):

1) Для цветового выделения выбрал метод условного форматирования и функцию СУММПРОИЗВ.
В большинстве случаев срабатывало правильно, но при "накладке" (два переноса в один день) на месячном графике мой вариант формул выдает не те результаты, что хотелось бы.
Может, Вы подскажете, как мне добиться нужного результата?

Сейчас пробую безуспешно for each rCell in Range, но не могу правильно задать алгоритм в ВБА.

2) Зеленоватые ячейки справа от каждого графика:
Вывод информации изначально реализовал через ИНДЕКС+ПОИСКПОЗ с привязкой к ключевому полю (НомерБрогады&Дата).
Теперь, когда появилась необходимость учитывать заявки чаще 2 раз в сутки такой подход не работает.

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

ИТОГ:
Мне нужно как-то перекроить имеющийся набор графиков, чтобы при выделении любой ячейки можно было получать полный набор данных по этому заказу и допились до ума СУММПРОИЗВ (либо заменить на любой другой способ), желательно не запоров сильно быстродействие.
А свои идеи пока иссякли.
Был бы очень рад любому совету.

P.S. Простите, если слишком запутанно объяснил %)

Автор - Невилл
Дата добавления - 24.03.2016 в 15:08
Невилл Дата: Пятница, 25.03.2016, 00:45 | Сообщение № 2
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
Разобрался сам. Решил задачку через создание пользовательского формата данных и динамического массива.

Тему, наверное, можно удалить или закрыть.
 
Ответить
СообщениеРазобрался сам. Решил задачку через создание пользовательского формата данных и динамического массива.

Тему, наверное, можно удалить или закрыть.

Автор - Невилл
Дата добавления - 25.03.2016 в 00:45
китин Дата: Пятница, 25.03.2016, 07:27 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3454
Репутация: 549 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
Решил задачку через создание пользовательского формата

а показать?я бы поучился yes


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщение
Решил задачку через создание пользовательского формата

а показать?я бы поучился yes

Автор - китин
Дата добавления - 25.03.2016 в 07:27
Невилл Дата: Пятница, 25.03.2016, 10:02 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
"Поучиться" у меня пока мало чему можно))
Постараюсь немного оптимизировать и структурировать код, а потом выложу здесь.
Пока задачу-то он выполняет, то слишком много лишнего и местами слишком по-китайски циклы сделаны. Стыдно :'(

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

Вдруг и правда, кому-то сгодится тоже.
ОК

Автор - Невилл
Дата добавления - 25.03.2016 в 10:02
Невилл Дата: Пятница, 25.03.2016, 15:23 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
Китин, как и обещал, выкладываю. Все еще сыро, но работает, в целом.

Не могу понять, правда, где в процедуре FindEvents (Модуль Events) я нарушил что-то с буфером обмена.
При активации листов с графиками буфер очищается, хотя это уж лишнее.

Не критично, но если кто подскажет, буду благодарен.

Любые советы по оптимизации кода также приветствуются))
К сообщению приложен файл: _1.0.9-.xlsm(80Kb)


Сообщение отредактировал Невилл - Пятница, 25.03.2016, 15:25
 
Ответить
СообщениеКитин, как и обещал, выкладываю. Все еще сыро, но работает, в целом.

Не могу понять, правда, где в процедуре FindEvents (Модуль Events) я нарушил что-то с буфером обмена.
При активации листов с графиками буфер очищается, хотя это уж лишнее.

Не критично, но если кто подскажет, буду благодарен.

Любые советы по оптимизации кода также приветствуются))

Автор - Невилл
Дата добавления - 25.03.2016 в 15:23
_Boroda_ Дата: Пятница, 25.03.2016, 15:27 | Сообщение № 6
Группа: Модераторы
Ранг: Экселист
Сообщений: 9381
Репутация: 3951 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
У меня Ваш файл не качается.
Если без него, то очистка буфера обмена происходит при изменении макросом каких-то значений на листе Excel. Или в именованных диапазонах, или еще где, главное, что не внутри VBA, а именно в Excel.
Ан нет, скачался. В код не вникал, но сразу вижу, что Вы пренебрегаете отключением обновления экрана
[vba]
Код
Application.ScreenUpdating = 0
[/vba]Очень рекомендую.


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеУ меня Ваш файл не качается.
Если без него, то очистка буфера обмена происходит при изменении макросом каких-то значений на листе Excel. Или в именованных диапазонах, или еще где, главное, что не внутри VBA, а именно в Excel.
Ан нет, скачался. В код не вникал, но сразу вижу, что Вы пренебрегаете отключением обновления экрана
[vba]
Код
Application.ScreenUpdating = 0
[/vba]Очень рекомендую.

Автор - _Boroda_
Дата добавления - 25.03.2016 в 15:27
Невилл Дата: Пятница, 25.03.2016, 15:40 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
Boroda, спасибо за совет!
По ошибке считал ScreenUpdating и EnableEvents тождественными вещами)) Теперь почитал, поправил!
Дерганье экрана исчезло при не 100%-ном масштабе сразу!
 
Ответить
СообщениеBoroda, спасибо за совет!
По ошибке считал ScreenUpdating и EnableEvents тождественными вещами)) Теперь почитал, поправил!
Дерганье экрана исчезло при не 100%-ном масштабе сразу!

Автор - Невилл
Дата добавления - 25.03.2016 в 15:40
Невилл Дата: Понедельник, 28.03.2016, 20:23 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
Ребята, файл тот же, проблемка другая. Не стал новую тему создавать.

В общем, у меня файл работает нормально (Win7x32 Office16)

А кое у кого из коллег макросы выдают ошибку при переключении листов (к активации листов "График" привязаны макросы):



Пытаюсь понять, в чем дело. Если есть минутка времени, гляньте файл, пожалуйста.
Compile error in hidden module DrawLines
Ругается на модуль DrawLines (для создания стрелок-автофигур). Код там - слегка модифицированная версия с какого-то англофорума.

Что я мог не учесть? Нужно что-то добавить для поддержки совместимости в код?

P.S. Пароль проекта: 123
К сообщению приложен файл: _1.1.1.xlsm(67Kb)


Сообщение отредактировал Невилл - Понедельник, 28.03.2016, 20:24
 
Ответить
СообщениеРебята, файл тот же, проблемка другая. Не стал новую тему создавать.

В общем, у меня файл работает нормально (Win7x32 Office16)

А кое у кого из коллег макросы выдают ошибку при переключении листов (к активации листов "График" привязаны макросы):



Пытаюсь понять, в чем дело. Если есть минутка времени, гляньте файл, пожалуйста.
Compile error in hidden module DrawLines
Ругается на модуль DrawLines (для создания стрелок-автофигур). Код там - слегка модифицированная версия с какого-то англофорума.

Что я мог не учесть? Нужно что-то добавить для поддержки совместимости в код?

P.S. Пароль проекта: 123

Автор - Невилл
Дата добавления - 28.03.2016 в 20:23
nilem Дата: Вторник, 29.03.2016, 08:43 | Сообщение № 9
Группа: Авторы
Ранг: Ветеран
Сообщений: 1057
Репутация: 400 ±
Замечаний: 0% ±

Excel 2013
у меня работает (Win8x64 Office10х32)


Яндекс.Деньги 4100159601573
 
Ответить
Сообщениеу меня работает (Win8x64 Office10х32)

Автор - nilem
Дата добавления - 29.03.2016 в 08:43
SLAVICK Дата: Вторник, 29.03.2016, 09:29 | Сообщение № 10
Группа: Модераторы
Ранг: Старожил
Сообщений: 1849
Репутация: 614 ±
Замечаний: 0% ±

2007,2010,2013,2016
у меня работает

У меня тоже работает.
кое у кого из коллег макросы выдают ошибку при переключении листов

У Вас в модуле нет четкой привязки к книге, и листу.
Вот что это такое: Range("График")
Именованный диапазон - содержится на каком-то листе, но в модуле Вы программе не говорите с каким листом работать.
Запустите DrawArrowInCell с 1-го листа - получите ошибку.
Иногда у офиса происходят глюки и он делает активным другой лист, или книгу(особенно если есть UDF или волатильные функции)
Чтобы такого избежать нужно четко указывать к диапазону какого листа Вы обращаетесь:
к примеру:
[vba]
Код
With ThisWorkbook.Sheets(n)
    For m = 1 To .Range("График").Rows.Count
[/vba]
где n - имя или индекс листа


Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
у меня работает

У меня тоже работает.
кое у кого из коллег макросы выдают ошибку при переключении листов

У Вас в модуле нет четкой привязки к книге, и листу.
Вот что это такое: Range("График")
Именованный диапазон - содержится на каком-то листе, но в модуле Вы программе не говорите с каким листом работать.
Запустите DrawArrowInCell с 1-го листа - получите ошибку.
Иногда у офиса происходят глюки и он делает активным другой лист, или книгу(особенно если есть UDF или волатильные функции)
Чтобы такого избежать нужно четко указывать к диапазону какого листа Вы обращаетесь:
к примеру:
[vba]
Код
With ThisWorkbook.Sheets(n)
    For m = 1 To .Range("График").Rows.Count
[/vba]
где n - имя или индекс листа

Автор - SLAVICK
Дата добавления - 29.03.2016 в 09:29
Невилл Дата: Вторник, 29.03.2016, 10:06 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
Slavick, спасибо за наводку. Попробую с этим блоком поработать дополнительно
Хотя задумка там была в том, процедура DrawArrowInCell вызывается только при событии Worksheet_Activate() исключительно на трех листах. То есть, ActiveSheet уже, вроде, и так нужный выбран.
На каждом из них есть именованный диапазон с идентичным названием "График" (область-соответствующий лист, а не книга).

То есть, процедура DrawArrowInCell с первого листа без входа в редактор VBA и ручного запуска происходить, вроде и не должна.
 
Ответить
СообщениеSlavick, спасибо за наводку. Попробую с этим блоком поработать дополнительно
Хотя задумка там была в том, процедура DrawArrowInCell вызывается только при событии Worksheet_Activate() исключительно на трех листах. То есть, ActiveSheet уже, вроде, и так нужный выбран.
На каждом из них есть именованный диапазон с идентичным названием "График" (область-соответствующий лист, а не книга).

То есть, процедура DrawArrowInCell с первого листа без входа в редактор VBA и ручного запуска происходить, вроде и не должна.

Автор - Невилл
Дата добавления - 29.03.2016 в 10:06
SLAVICK Дата: Вторник, 29.03.2016, 10:51 | Сообщение № 12
Группа: Модераторы
Ранг: Старожил
Сообщений: 1849
Репутация: 614 ±
Замечаний: 0% ±

2007,2010,2013,2016
вызывается только при событии Worksheet_Activate() исключительно на трех листах. То есть, ActiveSheet уже, вроде, и так нужный выбран

Иногда у офиса происходят глюки и он делает активным другой лист, или книгу(особенно если есть UDF или волатильные функции)
Чтобы такого избежать нужно четко указывать к диапазону какого листа Вы обращаетесь:

хоть ActiveSheet и выбран - указать процедуре куда смотреть - лишним не будет.
создайте переменную к примеру sh и работайте с ней - так удобней. Или как я уже написал ThisWorkbook.Sheets(n) - так длинее и не удобно, но тоже правильно.
[vba]
Код
set sh = ActiveSheet
[/vba]
[vba]
Код
with sh
For m = 1 To .Range("График").Rows.Count
....
end with
[/vba]
или
[vba]
Код
For m = 1 To sh.Range("График").Rows.Count
[/vba]


Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
вызывается только при событии Worksheet_Activate() исключительно на трех листах. То есть, ActiveSheet уже, вроде, и так нужный выбран

Иногда у офиса происходят глюки и он делает активным другой лист, или книгу(особенно если есть UDF или волатильные функции)
Чтобы такого избежать нужно четко указывать к диапазону какого листа Вы обращаетесь:

хоть ActiveSheet и выбран - указать процедуре куда смотреть - лишним не будет.
создайте переменную к примеру sh и работайте с ней - так удобней. Или как я уже написал ThisWorkbook.Sheets(n) - так длинее и не удобно, но тоже правильно.
[vba]
Код
set sh = ActiveSheet
[/vba]
[vba]
Код
with sh
For m = 1 To .Range("График").Rows.Count
....
end with
[/vba]
или
[vba]
Код
For m = 1 To sh.Range("График").Rows.Count
[/vba]

Автор - SLAVICK
Дата добавления - 29.03.2016 в 10:51
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Интерактивный график с макросами и СУММПРОИЗВ (Макросы/Sub)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!