Страницы: 1
RSS
Функция не пересчитывается при открытии документа
 
Доброго времени суток.
Использую Excel 2016.

Задача, при решении которой возникла проблема:
Необходимо сформировать отчет, в котором будет подсчитано количество сотрудников по трем критериям:
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. В основном документе много других расчетов и выделений ячеек по условиям.
Изменено: maonang - 19.12.2017 02:03:05 (Убрал лишнее)
 
Вы бы еще и упрощенное описание предоставили... Кому охота читать целый трактат?
 
Вкратце: ячейки, в которых записан вызов макроса, не обновляют данные при открытии документа, выводят везде ноль вместо того, что должно быть.
 
Открыл. В E9, E23, F26, J26 единички
 
Это последние сохраненные данные, а если поставить "+" в другом столбце образования первого сотрудника, то данные при открытии не обновятся, равным счетом, как и после изменения, так как они не входят в аргументы функции, описанной макросом.
Но изначально все столбцы у сотрудника кроме ФИО выводятся из другого листа, так что они меняются автоматически после соответствующих изменений данных пользователем на другом листе.
Изменено: maonang - 19.12.2017 02:16:49
 
Есть вариант обновлять через нажатие на кнопку, но это на крайний случай...
Может изменить алгоритм в макросе? Но суть в том, что диапазон количества сотрудников может меняться, поэтому установлены "опорные" адреса на ячейки, которые сами изменяются при добавлении или удалении строк с сотрудниками. Но, изменив макрос нет полной уверенности, что он будет вызываться при открытии документа.

Пробовал:
  • изменить местоположение макроса (перенос из Module1 в Лист 1 и потом в ЭтаКнига) - не помогло
  • Убрать Public вначале (авось..) - не помогло
  • Добавить к аргументам функции ссылку на ячейку, данные которой берутся из другого листа. Ведь как бы при открытии она обновит автоматически значение от другого листа, а вслед за этим и функция должна (как бы) среагировать и выполнить код из макроса... - не помогло
Изменено: maonang - 19.12.2017 02:39:34 (Дополнил вариантом решения и проделанными попытками)
 
Обрабатывать событие изменения в диапазоне листа (процедура в модуле листа) и макросом (не функцией) вычислять и вставлять данные
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("A3:AA5"), Target) Is Nothing Then

При этом разным диапазонам разрешить пересчет разных значений. Зачем пересчитывать все, если изменения касаются только части?
 
vikttur, спасибо за совет.
Есть вопрос:
Цитата
макросом (не функцией) вычислять и вставлять данные
Написать макрос для 144 ячеек, при контролировании изменения одного диапазона? (В области "Данные для приложения №15") Можно для трех диапазонов, но суть не меняется, так как разгруппировать их из-за количества критериев подсчета не получится.
 
Цитата
maonang написал:
Вкратце: ячейки, в которых записан вызов макроса, не обновляют данные при открытии документа
Функция пересчитывается, если изменяются значения, переданные ей в качестве аргументов. Например, если поставить 0 в N6, пересчитаются все ячейки с функциями.
Функция не пересчитывается, если изменяются ячейки, к которым код обращается через Cells - построитель последовательности пересчета Excel не анализирует поведение кода.
Если Вы хотите, чтобы функция пересчитывалась при изменении какого-то диапазона, передайте ей этот диапазон как аргумент.
 
Цитата
maonang написал: Написать макрос для 144 ячеек...?
Нет, макросом заполнять ячейки, зависящие от значения в изменяемой ячейке. Если разделить сложно или зависимостей прописывать прописывать много, то макросом пересчитывать весь диапазон таблицы.
 
азанский,
Цитата
Функция не пересчитывается, если изменяются ячейки, к которым код обращается через Cells - построитель последовательности пересчета Excel не анализирует поведение кода.
Этого не знал, спасибо! Если передать в качестве аргумента диапазон A:AD, то это сильно нагрузит работу макроса, учитывая, что я ничего с ним в коде не делаю?

Цитата
vikttur: макросом заполнять ячейки, зависящие от значения в изменяемой ячейке. Если разделить сложно или зависимостей прописывать прописывать много, то макросом пересчитывать весь диапазон таблицы.
Проще оказалось просто передать в качестве аргумента диапазон с данными сотрудников. Спасибо за совет!
Проблема решена! Теперь при открытии и изменении документа данные автоматически пересчитываются.

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

+Как ускорить работу документа без потери функционала? Около трех секунд нужно ждать после каждого ввода. Знаю, что можно сделать перерасчет в ручном режиме, но может есть другие варианты? Всего в документе 10 листов:
  • 7 для отчета;
  • 1 для подстановки данных в поля подписей, даты;
  • 1 с данными сотрудников;
  • 1 с распределением сотрудников по отделам (от него идут данные в отчеты)
Большое всем спасибо за помощь!
Изменено: maonang - 19.12.2017 16:45:12
 
Один вопрос - одна тема. Второй вопрос не по теме.

О скорости пересчетов. Еще один аргумент для отказа от функций пользователя и переход на вычисления в запускаемой процедуре.
 
Цитата
maonang написал:
Если перейти в другой лист, изменить там данные, то значения ячеек, с которыми была проблема, обнуляются. Почему так может происходить?
Если смотреть на код из первого поста (Вы не привели код после изменения) - Cells без указания принадлежности относятся к текущему листу (а не к тому, с которого вызвана функция).
Поэтому надо либо использовать диапазон, который теперь передается как параметр (надо полагать, он и содержит требуемые ячейки), либо получить привязку к листу, с которого вызвана функция:
Код
Dim ws As Worksheet
Set ws = Application.Caller.Worksheet
И заменить все сиротливые Cells на ws.Cells - можно с помощью Ctrl+H.
 
Цитата
maonang написал:
Как ускорить работу документа без потери функционала?
Обращение к ячейке - медленная операция. Следует получить массив значений диапазона и в цикле работать с ним. Получите ускорение как минимум на порядок. Примеров на форуме множество.
 
Казанский,
Цитата
Обращение к ячейке - медленная операция. Следует получить массив значений диапазона и в цикле работать с ним. Получите ускорение как минимум на порядок. Примеров на форуме множество.
спасибо, учту!
Рационально в рамках одной процедуры производить разноплановый расчет всего листа?
Цитата
И заменить все сиротливые Cells на ws.Cells - можно с помощью Ctrl+H.
Заменил, помогло, теперь данные не обнуляются! Думал, что где функция вызывается, оттуда и берутся значения, если строго не прописать Sheets.
Получается, что перерасчет в функции происходит с использованием ячеек активного листа, хотя они объявлены на других?
vikttur, прошу прощения, учту.
Изменено: maonang - 19.12.2017 17:26:14
Страницы: 1
Наверх