Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
МАКСЕСЛИМН (выборка ставки сотрудников в зависимости от дат)
 
#1
14.10.2016 11:41:56
У меня Excel 2007, следовательно МАКСЕСЛИМН нет, и хотелось бы реализовать наиболее корректно поиск ставки сотрудника, а так же изменение её простым добавлением строки.

Есть лист Сотрудники
Фамилия Имя, Дата изменения ставки, Ставка
при этом фамилия сотрудника в столбце может повторяться с другой датой при изменении ставки

на втором листе Зарплата идет список
Дата, время работы, ставка

в зависимости от колонки Дата на листе Зарплата - из таблицы сотрудников колонка Ставка должна получать значение ставки, которая предшествовала (или равна) дате изменения этой ставки.

Сейчас я делаю это функцией
=НАИБОЛЬШИЙ(ЕСЛИ(Сотрудники!A:A=C5;Сотрудники!D:D);ЕСЛИ(G5;2;1))
и Ctrl=Shift+Enter
но при 500 заполненых строках эта таблица дико тормозит (оно и понятно)
кроме того не работает если в интервале было три ставки



Вопрос: как наиболее корректно это сделать?
Изменено: AVPAVP - 14.10.2016 11:51:16
 
 
 
#2
15.10.2016 02:43:52
Попробуйте в вашем файле вставить в ячейку H2 листа "Время работы" такую формулу (как формулу массива):
Код
  =ИНДЕКС(Сотрудники!D$2:D$2000; ПОИСКПОЗ( МАКС((Сотрудники!A$2:A$2000=C2)*(Сотрудники!B$2:B$2000<=A2)*(Сотрудники!B$2:B$2000));  (Сотрудники!A$2:A$2000=C2)*(Сотрудники!B$2:B$2000); 0))
и скопировать её в остальные ячейки колонки H.  А из столбцов F и G удалите формулы. Считать должно правильно для любого количества изменений ставок внутри интервала (единственное, если на листе "Сотрудники" для какого-то сотрудника для какой-то даты будет задвоение, т.е. будут указаны две ставки, то формула выдаст значение, которое стоит в таблице первым). Для 500 строчек тормоза конечно есть, но я бы не назвал их дикими.  Если у Вас таблица на листе "Сотрудники" выходит за 2000ю строку, поставьте в формуле вместо 2000 то кол-во строк, какое вам надо.

Если бы на листе "Сотрудники" записи для каждого сотрудника были бы упорядочены по возрастанию по дате в колонке B, то можно было бы попробовать формулу:
Код
H2=ПРОСМОТР(2;1/ (Сотрудники!A:A=C2) / (Сотрудники!B:B<=A2);Сотрудники!D:D)
 
Это не формула массива, но был бы тут выигрыш в скорости - ещё вопрос.

Ну а так, если скорость всё равно не устраивает - тогда макрос.
 
 
 
#3
15.10.2016 05:20:49
Формула АГРЕГАТ.

А для ускорения лучше использовать строго заданный диапазон без пустых строк.
Например сделать "умную таблицу" и ссылаться на её колонки. В этом случае формулам не придётся лазить по ненужным строкам и всё будет работать быстрее.
Изменено: Zoynels - 15.10.2016 05:23:54
 
 
 
#4
15.10.2016 07:04:58
Попробовал формулу с ПРОСМОТРом (заменил в формуле ссылки на столбцы целиком на ссылки на заполненную часть листа, т.е. вместо А:А поставил A$2:A$2000 и тд). На 500 записях на листе "Время работы" и 2000 записях на листе "Сотрудники" выигрыш в скорости по сравнению с формулой массива есть, но едва заметный.
 
 
 
Страницы: 1
Наверх