Доброго времени суток! Не думал что столкнусь с такой проблемой. Достаточно хорошо знаю Excel. Но все таки нужно обращаться за помощью. Уже второй день сижу и не могу найти решение. С помощью функции ВПР или ИНДЕКС и ПОИСКПОЗ или СМЕЩ можно найти интересующее значение по двум параметрам. С этой задачей данные функции справляются на ура. А что делать если нужно найти значение по 3-м параметрам? Можно было бы использовать СУММЕСЛИМН, но есть маленькая деталь... Эта деталь заключается в поле "Дата". Есть таблица с информацией об изменении окладов (лист "Исходник) где стоит дата изменения, и нужно из этой таблицы вытащить данные в другую таблицу по Дате, ФИО и по названию вида затрат (Оклад или налог). Проблема в том что Дата в конечной таблице не будет совпадать с датой в источнике и нужно найти ближайшую дату изменения оклада, потом ФИО и вытянуть расходы. Так как в Исходнике есть информация о предыдущих окладах то нужно чтобы в новую таблицу они попали по датам правильно. Прилагаю файл с примером. В лист "База" нужно вытянуть данные об окладе и ндфл из листа "Исходник". Ячейки окрашены. Может кто-нибудь подскажет хитроумную конструкцию)
Михаил Лебедев написал: почему в примере на 05.01.2019 з/пл = 10440?
если вопрос ко мне, то первая формула не суммирует значения и надо поправить ссылку на менеджера. на всякий случай закину еще формул. dimabk, пробуйте. пример у вас вообще не тот
Код
=ПРОСМОТР(2;1/(Таблица2[@Менеджер]=Таблица1[ФИО])/(Таблица2[@Дата]>=Таблица1[Дата изменения]);ВЫБОР(ПОИСКПОЗ(Таблица2[@[Статья 4 уровень]];{"Заработная плата на руки":"НДФЛ"};0);Таблица1[Оклад];Таблица1[НДФЛ]))
=СУММПРОИЗВ((Таблица2[@Менеджер]=Таблица1[ФИО])*(Таблица2[@Дата]>=Таблица1[Дата изменения])*(ВЫБОР(ПОИСКПОЗ(Таблица2[@[Статья 4 уровень]];{"Заработная плата на руки":"НДФЛ"};0);Таблица1[Оклад];Таблица1[НДФЛ])))
artyrH написал: показали бы свои попытки, особенно, с функцией СМЕЩ.
В том то и дело что я и попытки то не могу совершить. С функцией СМЕЩ у меня была идея сместить весь диапазон до нужной даты с помощью вложенной функции ПОИСКПОЗ и этот смещенный диапазон вложить в функцию ИНДЕКС и с помощью ПОИСКПОЗ найти в новом смещенном диапазоне ФИО, но эта попытка не удачная потому что в листе "Исходник" у всех сотрудников произошло изменение окладов в один день 01.01.19, и ПОИСКПОЗ находит не первое встречающееся значение новой даты а последнее, т.е. ячейку "А11" а нужно "А7". Таким образом весь диапазон "А2:D11" смещается до "А11:D21", и нужная фамилия уже оказывается вне диапазона. Просто я знаю только единственный способ когда можно искать ближайшие даты это с помощью конструкции ИНДЕКС ПОИСКПОЗ, третий необязательный аргумент функции ПОИСКПОЗ как раз за это отвечает.
Я правильно понимаю, что все кроме D заполняется руками и надо просто подтянуть актуальные на ту дату суммы? =SUMIFS(INDEX(Таблица1[[Оклад]:[НДФЛ]];;([@[Статья 4 уровень]]="НДФЛ")+1);Таблица1[ФИО];[@Менеджер];Таблица1[Дата изменения];MAX((Таблица1[ФИО]=[@Менеджер])*(Таблица1[Дата изменения]<=[@Дата])*Таблица1[Дата изменения]))
artyrH, Суммы - это чисто бухгалтерское понятие в данном случае употребил. Несмотря на функцию, конечно там будет одно значение, конечно если в один день не наплодить несколько изменений у одного сотрудника, но такое почти не случается.
БМВ написал: Я правильно понимаю, что все кроме D заполняется руками и надо просто подтянуть актуальные на ту дату суммы?=SUMIFS(INDEX(Таблица1[[Оклад]:[НДФЛ]];;([@[Статья 4 уровень]]="НДФЛ")+1);Таблица1[ФИО];[@Менеджер];Таблица1[Дата изменения];MAX((Таблица1[ФИО]=[@Менеджер])*(Таблица1[Дата изменения]<=[@Дата])*Таблица1[Дата изменения]))
Какая-то межгалактическая формула)) Формула массива сильно будет тормозить файл при нарастании базы. В случае добавления новых полей формула их не ловит. Например, если добавить в листе Исходник справа от столбца НДФЛ столбец Страховые взносы и в листе База в новой строчке вписать то сумма не подтянется
Я не могу знать все ли я случаи рассмотрел. Могу чего то не знать. Но данная сводная не отличается от исходника, проблема отлавливания даты изменения сохраняется.
MAX((Таблица1[ФИО]=[@Менеджер])*(Таблица1[Дата изменения]<=[@Дата])*Таблица1[Дата изменения]) - находит макимальную дату изменений по сотруднику меньшую заданной. INDEX(Таблица1;;MATCH([@[Статья 4 уровень]];Таблица1[#Headers];)) - нужную колонку исходя из статьи, но важно чтоб заголовок совпадал иначе нужно делать таблицу соответвий.
artyrH, исходные данные должны быть отсортированы по дате или полностью, или внутри сотрудника. По идее так должно быть всегда, но разные случаи бывают
можно дополнительный столбец в обе таблицы добавить который будет объединять необходимые параметры через например "_" и потом искать по одному параметру