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

Вход

Регистрация

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

 

= Мир MS Excel/Оптимизация поиска в диапазоне и записи найденного значения - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Оптимизация поиска в диапазоне и записи найденного значения (Формулы/Formulas)
Оптимизация поиска в диапазоне и записи найденного значения
Mechanic Дата: Вторник, 01.03.2016, 08:50 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрый день!

Помогите, пожалуйста, в решении проблемы оптимизации поиска ячейки в диапазоне по 3 критериям и записи её значения в определённую ячейку на другом листе.
Для начала опишу что я пытаюсь получить в данном файле:
1. Есть Лист с данными за месяца (в данном случае это "01.01.2016") из этого листа нужно взять из колонок таблицы "3/П Вод", "ЗП.Кон" и "перерасход топлива" и "разница" нужно взять значение соответствующее дню месяца, Водителю и организации и подставить на соответствующий дист в соответствующую ячейку на итоговых страницах ("общ ведом январь 1-15" или "общ ведом январь 16-31")
2. Позднее в данную книгу будут добавляться аналогичные листы: "01.02.2016", "общ ведом февраль 1-15", "общ ведом февраль 16-31" и т.д. для каждого месяца.
3. Сейчас я реализовал это следующим образом:
-Прописал формулу массива в ячейки на страницах "общ ведом январь 1-15" и "общ ведом январь 16-31" для поиска по колонке "З/П Вод"
Код
=ЕСЛИОШИБКА(ОКРУГЛ(ИНДЕКС(PrevSheet($A$1:$AA$771);ПОИСКПОЗ(1;($B7=PrevSheet($V$1:$V$771))*(D$4=ДЕНЬ(PrevSheet($B$1:$B$771)))*($C7=PrevSheet($AA$1:$AA$771));0);19);0);"")

и "перерасход топлива"
Код
=ЕСЛИОШИБКА(ОКРУГЛ(ИНДЕКС(PrevSheet($A$1:$AA$771);ПОИСКПОЗ(1;($B7=PrevSheet($V$1:$V$771))*(D$4=ДЕНЬ(PrevSheet($B$1:$B$771)))*(PrevSheet($J$1:$J$771)>0);0);10);0)*(-1);"")

для "ЗП.Кон" и "разница" формулы аналогичны.
-Создал 2 пользовательских функции для нахождения предыдущего и предпредыдущего листов
[vba]
Код
Function PrevSheet(rCell As Range)
    Application.Volatile
    Dim i As Integer
    i = rCell.Cells(1).Parent.Index
    PrevSheet = Sheets(i - 1).Range(rCell.Address)
End Function
Function PrevPrevSheet(rCell As Range)
    Application.Volatile
    Dim i As Integer
    i = rCell.Cells(1).Parent.Index
    PrevPrevSheet = Sheets(i - 2).Range(rCell.Address)
End Function
[/vba]
Собственно проблема оптимизации и "торможения" начинается уже после заполнения ячеек формулой для одного-двух водителей, всего подобных строк по водителям и кондукторам в рабочем файле будет около 1000 на каждой итоговой странице, а на страницах с исходными данными в рабочем файле будет около 2500 строк.
Можно ли каким-то образом оптимизировать решение данной задачи? Можно ли обойтись формулами или без VBA тут никак?
Пароль от файла примера:21
Заранее благодарю за любую оказанную помощь.
К сообщению приложен файл: 5569911.zip(94Kb)


Сообщение отредактировал Mechanic - Вторник, 01.03.2016, 09:01
 
Ответить
СообщениеДобрый день!

Помогите, пожалуйста, в решении проблемы оптимизации поиска ячейки в диапазоне по 3 критериям и записи её значения в определённую ячейку на другом листе.
Для начала опишу что я пытаюсь получить в данном файле:
1. Есть Лист с данными за месяца (в данном случае это "01.01.2016") из этого листа нужно взять из колонок таблицы "3/П Вод", "ЗП.Кон" и "перерасход топлива" и "разница" нужно взять значение соответствующее дню месяца, Водителю и организации и подставить на соответствующий дист в соответствующую ячейку на итоговых страницах ("общ ведом январь 1-15" или "общ ведом январь 16-31")
2. Позднее в данную книгу будут добавляться аналогичные листы: "01.02.2016", "общ ведом февраль 1-15", "общ ведом февраль 16-31" и т.д. для каждого месяца.
3. Сейчас я реализовал это следующим образом:
-Прописал формулу массива в ячейки на страницах "общ ведом январь 1-15" и "общ ведом январь 16-31" для поиска по колонке "З/П Вод"
Код
=ЕСЛИОШИБКА(ОКРУГЛ(ИНДЕКС(PrevSheet($A$1:$AA$771);ПОИСКПОЗ(1;($B7=PrevSheet($V$1:$V$771))*(D$4=ДЕНЬ(PrevSheet($B$1:$B$771)))*($C7=PrevSheet($AA$1:$AA$771));0);19);0);"")

и "перерасход топлива"
Код
=ЕСЛИОШИБКА(ОКРУГЛ(ИНДЕКС(PrevSheet($A$1:$AA$771);ПОИСКПОЗ(1;($B7=PrevSheet($V$1:$V$771))*(D$4=ДЕНЬ(PrevSheet($B$1:$B$771)))*(PrevSheet($J$1:$J$771)>0);0);10);0)*(-1);"")

для "ЗП.Кон" и "разница" формулы аналогичны.
-Создал 2 пользовательских функции для нахождения предыдущего и предпредыдущего листов
[vba]
Код
Function PrevSheet(rCell As Range)
    Application.Volatile
    Dim i As Integer
    i = rCell.Cells(1).Parent.Index
    PrevSheet = Sheets(i - 1).Range(rCell.Address)
End Function
Function PrevPrevSheet(rCell As Range)
    Application.Volatile
    Dim i As Integer
    i = rCell.Cells(1).Parent.Index
    PrevPrevSheet = Sheets(i - 2).Range(rCell.Address)
End Function
[/vba]
Собственно проблема оптимизации и "торможения" начинается уже после заполнения ячеек формулой для одного-двух водителей, всего подобных строк по водителям и кондукторам в рабочем файле будет около 1000 на каждой итоговой странице, а на страницах с исходными данными в рабочем файле будет около 2500 строк.
Можно ли каким-то образом оптимизировать решение данной задачи? Можно ли обойтись формулами или без VBA тут никак?
Пароль от файла примера:21
Заранее благодарю за любую оказанную помощь.

Автор - Mechanic
Дата добавления - 01.03.2016 в 08:50
buchlotnik Дата: Вторник, 01.03.2016, 08:58 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
пароль прикажете самим ломать?


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
Сообщениепароль прикажете самим ломать?

Автор - buchlotnik
Дата добавления - 01.03.2016 в 08:58
Mechanic Дата: Вторник, 01.03.2016, 08:59 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
buchlotnik, забыл написать, пароль 21
 
Ответить
Сообщениеbuchlotnik, забыл написать, пароль 21

Автор - Mechanic
Дата добавления - 01.03.2016 в 08:59
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Оптимизация поиска в диапазоне и записи найденного значения (Формулы/Formulas)
Страница 1 из 11
Поиск:

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