Задача, при решении которой возникла проблема: Необходимо сформировать отчет, в котором будет подсчитано количество сотрудников по трем критериям: 1. Возраст - попадание возраста сотрудника в один из шести диапазонов (1) до 30 лет, 2) от 30 до 39 лет и т.д.) 2. Специальность - одна из четырех специальностей (Врач, ММП, СМП, Прочие) 3. Образование - одно из шести вариантов (среднее полное, среднее специальное, высшее, 2 и более высших, основное общее и неполное высшее). Два последних входят по отдельности в сумму к среднему полному и среднему специальному.
Проблема заключается в том, что ячейки, в которых записан вызов макроса, реализующий выполнение поставленной задачи, не обновляют данные при открытии документа, выводят везде ноль вместо того, что должно быть. Если в таких ячейках нажать F2, а потом Enter, то данные пересчитываются локально. Комбинации F9 и Shift+F9 не помогают. Параметры вычислений везде стоят в автоматическом режиме (На панели и во вкладке Параметры - Формулы). Остальные мною написанные функции сбоя при работе не дают, но они не обрабатывают массив данных через циклы.
Макрос прописан в модуле под стандартным названием "Module1". Текст макроса:
Код
Public Function КоличествоТекущийГод(ДиапазонНачало, ДиапазонКонец As Integer, _
СпециальностьНазвание As String, _
СпециальностьСтолбец As Range, _
Высшее As String, _
ОбразованиеСтолбец, КНСтолбец, ДНСтолбец, ВозрастСтолбец As Range) As Integer
'Application.Volatile
Dim ОбщаяСумма As Integer
ОбщаяСумма = 0
Dim i As Integer
For i = ДиапазонНачало To ДиапазонКонец Step 1
If cells(i, ВозрастСтолбец.Column).Value <> "" And _
cells(i, СпециальностьСтолбец.Column).Value = СпециальностьНазвание Then
If cells(i, ОбразованиеСтолбец.Column).Value = "+" Or (Высшее <> "" And _
(cells(i, КНСтолбец.Column).Value = "+" Or _
cells(i, ДНСтолбец.Column).Value = "+")) Then
ОбщаяСумма = ОбщаяСумма + 1
End If
End If
Next i
КоличествоТекущийГод = ОбщаяСумма
End Function
Дополнительная информация: 1. Строка ниже является одной из моих попыток решить проблему. Раскомментирование ничего не дает.
Цитата
'Application.Volatile
2. Если добавить в макрос оператор Stop, то при открытии документа расчеты не останавливаются при достижении новой строки с добавленной командой. Значит макрос при открытии документа почему-то не выполняется. 3. Параметры из "Центра управления безопасностью": Параметры ActiveX:
Включить все элементы управления
Параметры макросов:
Включить все макросы
Доверять доступ к объектной модели проектов VBA
Защищенный просмотр - Все CheckBox сняты Внешнее содержимое:
Включить все подключения к данным
Включить автоматическое обновление для всех связей в книге
Параметры блокировки файлов - Не менял
Вопрос: Как сделать автоматическое обновление значений ячеек с использованием вышеуказанного макроса при изменении данных и перезапуске самого документа?
Упрощенную выдержку из рабочей таблицы прилагаю в формате xls 2003. В основном документе много других расчетов и выделений ячеек по условиям.
Это последние сохраненные данные, а если поставить "+" в другом столбце образования первого сотрудника, то данные при открытии не обновятся, равным счетом, как и после изменения, так как они не входят в аргументы функции, описанной макросом. Но изначально все столбцы у сотрудника кроме ФИО выводятся из другого листа, так что они меняются автоматически после соответствующих изменений данных пользователем на другом листе.
Есть вариант обновлять через нажатие на кнопку, но это на крайний случай... Может изменить алгоритм в макросе? Но суть в том, что диапазон количества сотрудников может меняться, поэтому установлены "опорные" адреса на ячейки, которые сами изменяются при добавлении или удалении строк с сотрудниками. Но, изменив макрос нет полной уверенности, что он будет вызываться при открытии документа.
Пробовал:
изменить местоположение макроса (перенос из Module1 в Лист 1 и потом в ЭтаКнига) - не помогло
Убрать Public вначале (авось..) - не помогло
Добавить к аргументам функции ссылку на ячейку, данные которой берутся из другого листа. Ведь как бы при открытии она обновит автоматически значение от другого листа, а вслед за этим и функция должна (как бы) среагировать и выполнить код из макроса... - не помогло
Изменено: maonang - 19.12.2017 02:39:34(Дополнил вариантом решения и проделанными попытками)
макросом (не функцией) вычислять и вставлять данные
Написать макрос для 144 ячеек, при контролировании изменения одного диапазона? (В области "Данные для приложения №15") Можно для трех диапазонов, но суть не меняется, так как разгруппировать их из-за количества критериев подсчета не получится.
maonang написал: Вкратце: ячейки, в которых записан вызов макроса, не обновляют данные при открытии документа
Функция пересчитывается, если изменяются значения, переданные ей в качестве аргументов. Например, если поставить 0 в N6, пересчитаются все ячейки с функциями. Функция не пересчитывается, если изменяются ячейки, к которым код обращается через Cells - построитель последовательности пересчета Excel не анализирует поведение кода. Если Вы хотите, чтобы функция пересчитывалась при изменении какого-то диапазона, передайте ей этот диапазон как аргумент.
maonang написал: Написать макрос для 144 ячеек...?
Нет, макросом заполнять ячейки, зависящие от значения в изменяемой ячейке. Если разделить сложно или зависимостей прописывать прописывать много, то макросом пересчитывать весь диапазон таблицы.
Функция не пересчитывается, если изменяются ячейки, к которым код обращается через Cells - построитель последовательности пересчета Excel не анализирует поведение кода.
Этого не знал, спасибо! Если передать в качестве аргумента диапазон A:AD, то это сильно нагрузит работу макроса, учитывая, что я ничего с ним в коде не делаю?
Цитата
vikttur: макросом заполнять ячейки, зависящие от значения в изменяемой ячейке. Если разделить сложно или зависимостей прописывать прописывать много, то макросом пересчитывать весь диапазон таблицы.
Проще оказалось просто передать в качестве аргумента диапазон с данными сотрудников. Спасибо за совет! Проблема решена! Теперь при открытии и изменении документа данные автоматически пересчитываются.
Но возникла немного другая. Если перейти в другой лист, изменить там данные, то значения ячеек, с которыми была проблема, обнуляются. Почему так может происходить? Попробовал передать в качестве аргумента диапазон столбцов другого листа, но нули так и остались.
+Как ускорить работу документа без потери функционала? Около трех секунд нужно ждать после каждого ввода. Знаю, что можно сделать перерасчет в ручном режиме, но может есть другие варианты? Всего в документе 10 листов:
7 для отчета;
1 для подстановки данных в поля подписей, даты;
1 с данными сотрудников;
1 с распределением сотрудников по отделам (от него идут данные в отчеты)
maonang написал: Если перейти в другой лист, изменить там данные, то значения ячеек, с которыми была проблема, обнуляются. Почему так может происходить?
Если смотреть на код из первого поста (Вы не привели код после изменения) - Cells без указания принадлежности относятся к текущему листу (а не к тому, с которого вызвана функция). Поэтому надо либо использовать диапазон, который теперь передается как параметр (надо полагать, он и содержит требуемые ячейки), либо получить привязку к листу, с которого вызвана функция:
Код
Dim ws As Worksheet
Set ws = Application.Caller.Worksheet
И заменить все сиротливые Cells на ws.Cells - можно с помощью Ctrl+H.
maonang написал: Как ускорить работу документа без потери функционала?
Обращение к ячейке - медленная операция. Следует получить массив значений диапазона и в цикле работать с ним. Получите ускорение как минимум на порядок. Примеров на форуме множество.
Обращение к ячейке - медленная операция. Следует получить массив значений диапазона и в цикле работать с ним. Получите ускорение как минимум на порядок. Примеров на форуме множество.
спасибо, учту! Рационально в рамках одной процедуры производить разноплановый расчет всего листа?
Цитата
И заменить все сиротливые Cells на ws.Cells - можно с помощью Ctrl+H.
Заменил, помогло, теперь данные не обнуляются! Думал, что где функция вызывается, оттуда и берутся значения, если строго не прописать Sheets. Получается, что перерасчет в функции происходит с использованием ячеек активного листа, хотя они объявлены на других? vikttur, прошу прощения, учту.