Структура документа (для понимания): В документе ведется учет сотрудников некой фирмы с формированием статистических данных. Листы: - приложения отчета, в которые сводятся все данные по критериям отбора сотрудников; - список сотрудников с указанием личных данных и перерасчетом стажа работы (и многое другое); - список сотрудников по отделам с подкатегориями: Принятые на работу * Руководители * Специалисты * Технические исполнители Уволенные с работы * Руководители * Специалисты * Технические исполнители
Задача, при решении которой возникла проблема: Необходимо сформировать отчет, в котором будет подсчитано количество сотрудников по трем критериям: 1. Возраст - попадание возраста сотрудника в один из шести диапазонов (1) до 30 лет, 2) от 30 до 39 лет и т.д.) 2. Специальность - одна из четырех специальностей (Врач, ММП, СМП, Прочие) 3. Образование - одно из шести вариантов (среднее полное, среднее специальное, высшее, 2 и более высших, основное общее и неполное высшее). Два последних входят по отдельности в сумму к среднему полному и среднему специальному.
Данный отчет должен содержать в себе статистику из трех подразделов принятых сотрудников (Руководители, Специалисты и Технические исполнители)
Проблема заключается в том, что ячейки, в которых записан вызов макроса, реализующий выполнение поставленной задачи, не обновляют данные при открытии документа, выводят везде ноль вместо того, что должно быть. Если в таких ячейках нажать F2, а потом Enter, то данные пересчитываются локально. Комбинации F9 и Shift+F9 не помогают. Параметры вычислений везде стоят в автоматическом режиме (На панели и во вкладке Параметры - Формулы). Остальные мною написанные функции сбоя при работе не дают, но они не обрабатывают массив данных через циклы.
Макрос прописан в модуле под стандартным названием "Module1". Текст макроса: [vba]
Код
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
[/vba]
Упрощенную выдержку из рабочей таблицы прилагаю в формате xls 2003. В основном документе много других расчетов и выделений ячеек по условиям.
Как сделать автоматическое обновление значений ячеек с использованием вышеуказанного макроса при изменении данных и перезапуске самого документа?
Доброго времени суток. Использую Excel 2016.
Структура документа (для понимания): В документе ведется учет сотрудников некой фирмы с формированием статистических данных. Листы: - приложения отчета, в которые сводятся все данные по критериям отбора сотрудников; - список сотрудников с указанием личных данных и перерасчетом стажа работы (и многое другое); - список сотрудников по отделам с подкатегориями: Принятые на работу * Руководители * Специалисты * Технические исполнители Уволенные с работы * Руководители * Специалисты * Технические исполнители
Задача, при решении которой возникла проблема: Необходимо сформировать отчет, в котором будет подсчитано количество сотрудников по трем критериям: 1. Возраст - попадание возраста сотрудника в один из шести диапазонов (1) до 30 лет, 2) от 30 до 39 лет и т.д.) 2. Специальность - одна из четырех специальностей (Врач, ММП, СМП, Прочие) 3. Образование - одно из шести вариантов (среднее полное, среднее специальное, высшее, 2 и более высших, основное общее и неполное высшее). Два последних входят по отдельности в сумму к среднему полному и среднему специальному.
Данный отчет должен содержать в себе статистику из трех подразделов принятых сотрудников (Руководители, Специалисты и Технические исполнители)
Проблема заключается в том, что ячейки, в которых записан вызов макроса, реализующий выполнение поставленной задачи, не обновляют данные при открытии документа, выводят везде ноль вместо того, что должно быть. Если в таких ячейках нажать F2, а потом Enter, то данные пересчитываются локально. Комбинации F9 и Shift+F9 не помогают. Параметры вычислений везде стоят в автоматическом режиме (На панели и во вкладке Параметры - Формулы). Остальные мною написанные функции сбоя при работе не дают, но они не обрабатывают массив данных через циклы.
Макрос прописан в модуле под стандартным названием "Module1". Текст макроса: [vba]
Код
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
[/vba]
Упрощенную выдержку из рабочей таблицы прилагаю в формате xls 2003. В основном документе много других расчетов и выделений ячеек по условиям.
Как сделать автоматическое обновление значений ячеек с использованием вышеуказанного макроса при изменении данных и перезапуске самого документа?maonang
maonang, а по F9 пересчитывает? Проверьте тогда автопересчет формул (может вручную в книге стоит) я на вашем примере удалял данные, функция пересчитывалась.
maonang, а по F9 пересчитывает? Проверьте тогда автопересчет формул (может вручную в книге стоит) я на вашем примере удалял данные, функция пересчитывалась.sboy
а по F9 пересчитывает? Проверьте тогда автопересчет формул (может вручную в книге стоит) я на вашем примере удалял данные, функция пересчитывалась.
Проблема в том, что при открытии документа обновление данных с расчетом не происходит. Автоматический режим расчетов установлен. Все остальные формулы же работают.
Только после "перерасчета" формул в проблемных ячейках через комбинацию клавиш F2 -> Enter, данные изменяются при корректировке значений в связных ячейках. F9 до вышеописанного действия не дает результата.
Цитата
а по F9 пересчитывает? Проверьте тогда автопересчет формул (может вручную в книге стоит) я на вашем примере удалял данные, функция пересчитывалась.
Проблема в том, что при открытии документа обновление данных с расчетом не происходит. Автоматический режим расчетов установлен. Все остальные формулы же работают.
Только после "перерасчета" формул в проблемных ячейках через комбинацию клавиш F2 -> Enter, данные изменяются при корректировке значений в связных ячейках. F9 до вышеописанного действия не дает результата.maonang
Сообщение отредактировал maonang - Пятница, 15.12.2017, 18:10
Привет, InExSu. Я попробовал добавить оператор Stop в макрос. При открытии документа на нем остановка не происходит, значит, макрос не выполняется. Перерасчет происходит только тогда, когда идет изменение указанных в аргументе функции ячеек. Это логично. А так как в реализации задачи используются адреса на динамически изменяющийся диапазон, то значения в ячейках с данной функцией изменяться не будут при корректировке данных самих сотрудников.. Спасибо за предложение, InExSu. Возник дополнительный вопрос: учитывая данное исполнение листа проще сделать перерасчет формул после нажатия дополнительной кнопки или как-то можно без вмешательства пользователя обойтись?
Привет, InExSu. Я попробовал добавить оператор Stop в макрос. При открытии документа на нем остановка не происходит, значит, макрос не выполняется. Перерасчет происходит только тогда, когда идет изменение указанных в аргументе функции ячеек. Это логично. А так как в реализации задачи используются адреса на динамически изменяющийся диапазон, то значения в ячейках с данной функцией изменяться не будут при корректировке данных самих сотрудников.. Спасибо за предложение, InExSu. Возник дополнительный вопрос: учитывая данное исполнение листа проще сделать перерасчет формул после нажатия дополнительной кнопки или как-то можно без вмешательства пользователя обойтись?maonang
Сообщение отредактировал maonang - Суббота, 16.12.2017, 03:40
Проблема решена. Нужно было в качестве аргумента добавить диапазон с данными сотрудников, чтобы при изменении или открытии документа пересчитываемые параметры влияли на ячейки с указанным выше макросом. Измененный макрос (добавлен аргумент ДиапазонДляПерерасчета): [vba]
Код
Public Function КоличествоТекущийГод(ДиапазонНачало, ДиапазонКонец As Integer, _ СпециальностьНазвание As String, _ СпециальностьСтолбец As Range, _ Высшее As String, _ ОбразованиеСтолбец, КНСтолбец, ДНСтолбец, ВозрастСтолбец, ДиапазонДляПерерасчетаТекущийЛист, ДиапазонДляПерерасчетаСведенияОСотрудниках As Range) As Integer 'Привязка к листу, от которого происходит вызов функции Dim ws As Worksheet Set ws = Application.Caller.Worksheet Dim ОбщаяСумма As Integer ОбщаяСумма = 0 Dim i As Integer For i = ДиапазонНачало To ДиапазонКонец Step 1 If ws.cells(i, ВозрастСтолбец.Column).Value <> "" And _ ws.cells(i, СпециальностьСтолбец.Column).Value = СпециальностьНазвание Then If ws.cells(i, ОбразованиеСтолбец.Column).Value = "+" Or (Высшее <> "" And _ (ws.cells(i, КНСтолбец.Column).Value = "+" Or _ ws.cells(i, ДНСтолбец.Column).Value = "+")) Then ОбщаяСумма = ОбщаяСумма + 1 End If End If Next i КоличествоТекущийГод = ОбщаяСумма End Function
[/vba]
Формула в одной из ячейки (добавлен диапазон в конце A:AD): [vba]
Но возникла немного другая. Если перейти в другой лист, изменить там данные, то значения ячеек, с которыми была проблема, обнуляются. Почему так может происходить? Попробовал передать в качестве аргумента диапазон столбцов другого листа, но нули так и остались.
Проблема решена. Нужно было в качестве аргумента добавить диапазон с данными сотрудников, чтобы при изменении или открытии документа пересчитываемые параметры влияли на ячейки с указанным выше макросом. Измененный макрос (добавлен аргумент ДиапазонДляПерерасчета): [vba]
Код
Public Function КоличествоТекущийГод(ДиапазонНачало, ДиапазонКонец As Integer, _ СпециальностьНазвание As String, _ СпециальностьСтолбец As Range, _ Высшее As String, _ ОбразованиеСтолбец, КНСтолбец, ДНСтолбец, ВозрастСтолбец, ДиапазонДляПерерасчетаТекущийЛист, ДиапазонДляПерерасчетаСведенияОСотрудниках As Range) As Integer 'Привязка к листу, от которого происходит вызов функции Dim ws As Worksheet Set ws = Application.Caller.Worksheet Dim ОбщаяСумма As Integer ОбщаяСумма = 0 Dim i As Integer For i = ДиапазонНачало To ДиапазонКонец Step 1 If ws.cells(i, ВозрастСтолбец.Column).Value <> "" And _ ws.cells(i, СпециальностьСтолбец.Column).Value = СпециальностьНазвание Then If ws.cells(i, ОбразованиеСтолбец.Column).Value = "+" Or (Высшее <> "" And _ (ws.cells(i, КНСтолбец.Column).Value = "+" Or _ ws.cells(i, ДНСтолбец.Column).Value = "+")) Then ОбщаяСумма = ОбщаяСумма + 1 End If End If Next i КоличествоТекущийГод = ОбщаяСумма End Function
[/vba]
Формула в одной из ячейки (добавлен диапазон в конце A:AD): [vba]
Но возникла немного другая. Если перейти в другой лист, изменить там данные, то значения ячеек, с которыми была проблема, обнуляются. Почему так может происходить? Попробовал передать в качестве аргумента диапазон столбцов другого листа, но нули так и остались.maonang
Сообщение отредактировал maonang - Среда, 20.12.2017, 15:40
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
