Проблема: 1. На всех вкладках кроме "Реестр инцидентов" очень много формул, практически каждая из которых работает с массивами. 2. Из-за того, что на каждом листе тысячи формул, открытие, сохранение файла занимает очень длительное время. 3. Отключение авторасчета при открытии файла не срабатывает, поскольку оно активируется только после открытия файла, а на открытие уходит время: [vba]
Код
Private Sub Workbook_Open() 'Больше не обновляем страницы после каждого действия Application.ScreenUpdating = False
'Расчёты переводим в ручной режим Application.Calculation = xlCalculationManual
'Отключаем события Application.EnableEvents = False Application.MsgBox "Авторасчет отключен" End Sub
[/vba] 4. "Формулы - Параметры вычислений - Вручную" использовать нельзя, т.к. с файлом будут работать другие сотрудники внутри корпоративной сети.
Логика работы: 1. Вкладки с фамилиями - названия групп, в каждой группе есть сотрудники. В файл-примере оставлена одна группа. 2. Со вкладки "Реестр инцидентов" на все остальные вкладки возвращаются значения: - "Имя+фамилия", - "Номер инцидента", - "Дата создания", - "Состояние", - "Ошибка регистрации". 3. На каждой вкладке группы по очереди из массива возвращаются ФИО сотрудников по блокам. 4. Записей с "Имя фамилия" на вкладке "Реестр инцидентов" по столбцу "Н" может быть несколько, поэтому используются массивы, а не, например, функция ВПР. 5. Файл после расчета выглядит таким образом: на вкладке "Хайрварин" есть, например, "Шуляк Дмитрий Викторович", его "имя+фамилия" встречаются на вкладке "Реестр инцидентов" 5 раз, поэтому к нему в блок было возвращено 5 записей.
Необходимо: 1. Избавиться от массивов поскольку они очень сильно грузят файл. 2. Обеспечить автоматическое распределение значений со вкладки "Реестр инцидентов" по всем остальным вкладкам.
Прошу помочь
Доброго дня!
Проблема: 1. На всех вкладках кроме "Реестр инцидентов" очень много формул, практически каждая из которых работает с массивами. 2. Из-за того, что на каждом листе тысячи формул, открытие, сохранение файла занимает очень длительное время. 3. Отключение авторасчета при открытии файла не срабатывает, поскольку оно активируется только после открытия файла, а на открытие уходит время: [vba]
Код
Private Sub Workbook_Open() 'Больше не обновляем страницы после каждого действия Application.ScreenUpdating = False
'Расчёты переводим в ручной режим Application.Calculation = xlCalculationManual
'Отключаем события Application.EnableEvents = False Application.MsgBox "Авторасчет отключен" End Sub
[/vba] 4. "Формулы - Параметры вычислений - Вручную" использовать нельзя, т.к. с файлом будут работать другие сотрудники внутри корпоративной сети.
Логика работы: 1. Вкладки с фамилиями - названия групп, в каждой группе есть сотрудники. В файл-примере оставлена одна группа. 2. Со вкладки "Реестр инцидентов" на все остальные вкладки возвращаются значения: - "Имя+фамилия", - "Номер инцидента", - "Дата создания", - "Состояние", - "Ошибка регистрации". 3. На каждой вкладке группы по очереди из массива возвращаются ФИО сотрудников по блокам. 4. Записей с "Имя фамилия" на вкладке "Реестр инцидентов" по столбцу "Н" может быть несколько, поэтому используются массивы, а не, например, функция ВПР. 5. Файл после расчета выглядит таким образом: на вкладке "Хайрварин" есть, например, "Шуляк Дмитрий Викторович", его "имя+фамилия" встречаются на вкладке "Реестр инцидентов" 5 раз, поэтому к нему в блок было возвращено 5 записей.
Необходимо: 1. Избавиться от массивов поскольку они очень сильно грузят файл. 2. Обеспечить автоматическое распределение значений со вкладки "Реестр инцидентов" по всем остальным вкладкам.
А для чего в формуле СТРОКА(ДВССЫЛ("'Реестр инцидентов'!H1:H"&ЧСТРОК(Сотрудник))ведь это возвращает только № строки который и без ДВССЫЛ известен, он соответствует количеству в массиве Сотрудник, это можно заменить безболезненно на СТРОКА($1:$993). Удалил ДВССЫЛ и обращение к имени Сотрудник, через Найти--Заменить (Ctrl+H), открываться файл стал быстрее, возможно это только кажется.
А для чего в формуле СТРОКА(ДВССЫЛ("'Реестр инцидентов'!H1:H"&ЧСТРОК(Сотрудник))ведь это возвращает только № строки который и без ДВССЫЛ известен, он соответствует количеству в массиве Сотрудник, это можно заменить безболезненно на СТРОКА($1:$993). Удалил ДВССЫЛ и обращение к имени Сотрудник, через Найти--Заменить (Ctrl+H), открываться файл стал быстрее, возможно это только кажется.gling
А для чего в формуле СТРОКА(ДВССЫЛ("'Реестр инцидентов'!H1:H"&ЧСТРОК(Сотрудник))ведь это возвращает только № строки который и без ДВССЫЛ известен, он соответствует количеству в массиве Сотрудник, это можно заменить безболезненно на СТРОКА($1:$993). Удалил ДВССЫЛ и обращение к имени Сотрудник, через Найти--Заменить (Ctrl+H), открываться файл стал быстрее, возможно это только кажется.
Да, можно и так, но добавив буквально 3 вкладки, нагрузка на файл примерно та же, а в конечном результате должно быть 10 групп...
И еще вариант без массивных формул, но с доп столбцом.
К сожалению, так не считает, можно проверить если скопировать, например, на вкладке "Реестр инцидентов" массив данных и вставить их ниже. Конечно же растянув новый столбец. Поиск останавливается сразу после того, как находит 1-е совпадение, но все равно задумка интересная.
А для чего в формуле СТРОКА(ДВССЫЛ("'Реестр инцидентов'!H1:H"&ЧСТРОК(Сотрудник))ведь это возвращает только № строки который и без ДВССЫЛ известен, он соответствует количеству в массиве Сотрудник, это можно заменить безболезненно на СТРОКА($1:$993). Удалил ДВССЫЛ и обращение к имени Сотрудник, через Найти--Заменить (Ctrl+H), открываться файл стал быстрее, возможно это только кажется.
Да, можно и так, но добавив буквально 3 вкладки, нагрузка на файл примерно та же, а в конечном результате должно быть 10 групп...
И еще вариант без массивных формул, но с доп столбцом.
К сожалению, так не считает, можно проверить если скопировать, например, на вкладке "Реестр инцидентов" массив данных и вставить их ниже. Конечно же растянув новый столбец. Поиск останавливается сразу после того, как находит 1-е совпадение, но все равно задумка интересная.
добавлять не стОит. СМЕЩ - волатильная (посмотрите в поиске, например, здесь) функция. По возможности их стараются избегать. Гораздо производительнее будет
Код
ИНДЕКС(K$1:K$50;ПОИСКПОЗ(A15;L$2:L$50;))
А вот кусок
Код
СМЕЩ($K$1;ПОИСКПОЗ(A15;$L$2:$L$50;0);0)
добавлять не стОит. СМЕЩ - волатильная (посмотрите в поиске, например, здесь) функция. По возможности их стараются избегать. Гораздо производительнее будет
Потому что в формулах диапазон ограничен 171 строкой листа "Реестр инцидентов". Увеличите диапазон через Заменить до требуемого, все будет считать. 'Реестр инцидентов'!$A$4:$P$171 и 'Реестр инцидентов'!$Q$4:$Q$171, добавьте им по два нолика (по типу $Q$4:$Q$17100) сами увидите.
Потому что в формулах диапазон ограничен 171 строкой листа "Реестр инцидентов". Увеличите диапазон через Заменить до требуемого, все будет считать. 'Реестр инцидентов'!$A$4:$P$171 и 'Реестр инцидентов'!$Q$4:$Q$171, добавьте им по два нолика (по типу $Q$4:$Q$17100) сами увидите.gling
ЯД-41001506838083
Сообщение отредактировал gling - Воскресенье, 19.07.2015, 19:20
iRustem, Если, все-таки, решитесь перейти на макросы, то посмотрите переделку Макрос запускается при добавлении или удалении имени в столбике Состава группы
iRustem, Если, все-таки, решитесь перейти на макросы, то посмотрите переделку Макрос запускается при добавлении или удалении имени в столбике Состава группыmiver
Воспользовался макросом, расчеты проходят вообще незаметно, Excel не грузится. miver, подскажите, пожалуйста, как изменить событие, чтобы данные обновлялись не при изменении списка сотрудников, а при изменении значений (любых) на вкладке "Реестр инцидентов"?
\\ Добавлено: обошел этот момент, решил добавить кнопку, которая вносит изменения в список сотрудников на всех вкладках
Добрый день!
Всем большое спасибо за помощь!
Воспользовался макросом, расчеты проходят вообще незаметно, Excel не грузится. miver, подскажите, пожалуйста, как изменить событие, чтобы данные обновлялись не при изменении списка сотрудников, а при изменении значений (любых) на вкладке "Реестр инцидентов"?
\\ Добавлено: обошел этот момент, решил добавить кнопку, которая вносит изменения в список сотрудников на всех вкладках iRustem
Сообщение отредактировал iRustem - Понедельник, 20.07.2015, 13:06
Скорей всего остался Target. Попробуйте заменить на такой код [vba]
Код
For Each Cel In Лист1.Range("L2", "L41") If Cel.Value = "" Then Cel.Offset(0, -1).ClearContents Else Arr = Split(Cel.Value) If UBound(Arr) > 1 Then Cel.Offset(0, -1).Value = Arr(1) & " " & Arr(0) Erase Arr End If Next Cel
[/vba]
Скорей всего остался Target. Попробуйте заменить на такой код [vba]
Код
For Each Cel In Лист1.Range("L2", "L41") If Cel.Value = "" Then Cel.Offset(0, -1).ClearContents Else Arr = Split(Cel.Value) If UBound(Arr) > 1 Then Cel.Offset(0, -1).Value = Arr(1) & " " & Arr(0) Erase Arr End If Next Cel
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
Вот так и тянется, хотя с ним быстрее.
