Страницы: 1 2 След.
RSS
Поиск значений по 3-м параметрам с не точным совпадением одного параметра...
 
Доброго времени суток!
Не думал что столкнусь с такой проблемой. Достаточно хорошо знаю Excel. Но все таки нужно обращаться за помощью. Уже второй день сижу и не могу найти решение.
С помощью функции ВПР или ИНДЕКС и ПОИСКПОЗ или СМЕЩ можно найти интересующее значение по двум параметрам. С этой задачей данные функции справляются на ура. А что делать если нужно найти значение по 3-м параметрам? Можно было бы использовать СУММЕСЛИМН, но есть маленькая деталь... Эта деталь заключается в поле "Дата".
Есть таблица с информацией об изменении окладов (лист "Исходник) где стоит дата изменения, и нужно из этой таблицы вытащить данные в другую таблицу по Дате, ФИО и по названию вида затрат (Оклад или налог). Проблема в том что Дата в конечной таблице не будет совпадать с датой в источнике и нужно найти ближайшую дату изменения оклада, потом ФИО и вытянуть расходы. Так как в Исходнике есть информация о предыдущих окладах то нужно чтобы в новую таблицу они попали по датам правильно.
Прилагаю файл с примером. В лист "База" нужно вытянуть данные об окладе и ндфл из листа "Исходник". Ячейки окрашены.
Может кто-нибудь подскажет хитроумную конструкцию)
Изменено: dimabk - 04.04.2019 00:39:38
 
dimabk, пример не правильный. формула не суммирует и по дате ищет равное или старше.
Код
=ПРОСМОТР(2;1/(Таблица2[@Менеджер]=Таблица1[ФИО])/(Таблица2[Дата]>=Таблица1[Дата изменения]);ВЫБОР(ПОИСКПОЗ(Таблица2[@[Статья 4 уровень]];{"Заработная плата на руки":"НДФЛ"};0);Таблица1[Оклад];Таблица1[НДФЛ]))

показали бы свои попытки, особенно, с функцией СМЕЩ.
 
... а почему в примере на 05.01.2019 з/пл = 10440? Если  правильно понял, то должна быть 13450 (то же и НДФЛ)
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Цитата
Михаил Лебедев написал:
почему в примере на 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", и нужная фамилия уже оказывается вне диапазона.
Просто я знаю только единственный способ когда можно искать ближайшие даты это с помощью конструкции ИНДЕКС ПОИСКПОЗ, третий необязательный аргумент функции ПОИСКПОЗ как раз за это отвечает.
 
Цитата
artyrH написал:
пробуйте. пример у вас вообще не тот
Извиняюсь, забыл поменять данные. Теперь тот.
Изменено: dimabk - 04.04.2019 07:47:15
 
Цитата
artyrH написал:
если вопрос ко мне,
Нет, не к Вам :)
Вопрос - к dimabk. Еще раз:
... а почему в примере на 05.01.2019 з/пл = 10440? Если  правильно понял, то должна быть 13450 (то же и НДФЛ)
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
может сводная решит вопрос?
По вопросам из тем форума, личку не читаю.
 
Цитата
Михаил Лебедев написал:
... а почему в примере на 05.01.2019 з/пл = 10440? Если  правильно понял, то должна быть 13450 (то же и НДФЛ)
A сообщением выше исправил это недоразумение)
 
Цитата
БМВ написал:
может сводная решит вопрос?
Сводные таблицы я тоже перепробовал. Проблема все сохраняется. Как я вытащу суммы?
 
Цитата
dimabk написал:
Проблема все сохраняется. Как я вытащу суммы?
все ли случаи рассмотрели?
 
Я правильно понимаю, что все кроме D заполняется руками и надо просто подтянуть актуальные на ту дату суммы?
=SUMIFS(INDEX(Таблица1[[Оклад]:[НДФЛ]];;([@[Статья 4 уровень]]="НДФЛ")+1);Таблица1[ФИО];[@Менеджер];Таблица1[Дата изменения];MAX((Таблица1[ФИО]=[@Менеджер])*(Таблица1[Дата изменения]<=[@Дата])*Таблица1[Дата изменения]))
Изменено: БМВ - 04.04.2019 12:01:52
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
надо просто подтянуть актуальные на ту дату суммы?
судя по последнему примеру, нужно подтянуть не сумму, а значение с ближайшей датой. на всякий случай я закинул формулу с Просмотр.  
 
artyrH, Суммы  - это чисто бухгалтерское понятие в данном случае употребил. Несмотря на функцию, конечно там будет одно значение, конечно если в один день не наплодить несколько изменений у одного сотрудника, но такое почти не случается.
Изменено: БМВ - 04.04.2019 12:09:44
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Я правильно понимаю, что все кроме D заполняется руками и надо просто подтянуть актуальные на ту дату суммы?
Правильно. Из листа "Исходник" необходимо подтянуть только суммы, остальное вводится руками.
 
Цитата
БМВ написал:
Я правильно понимаю, что все кроме D заполняется руками и надо просто подтянуть актуальные на ту дату суммы?=SUMIFS(INDEX(Таблица1[[Оклад]:[НДФЛ]];;([@[Статья 4 уровень]]="НДФЛ")+1);Таблица1[ФИО];[@Менеджер];Таблица1[Дата изменения];MAX((Таблица1[ФИО]=[@Менеджер])*(Таблица1[Дата изменения]<=[@Дата])*Таблица1[Дата изменения]))
Какая-то межгалактическая формула))
Формула массива сильно будет тормозить файл при нарастании базы. В случае добавления новых полей формула их не ловит. Например, если добавить в листе Исходник справа от столбца НДФЛ столбец Страховые взносы и в листе База в новой строчке вписать то сумма не подтянется
 
Цитата
artyrH написал:
все ли случаи рассмотрели?
всякий случай смотрели?
 
Цитата
dimabk написал:
Формула массива сильно будет тормозить файл при нарастании базы.
Я правильно понимаю, что у вас или сотни тысяч строк за много лет или AT комп c 86v процом?
Цитата
dimabk написал:
В случае добавления новых полей формула их не ловит
ну так а что вы хотели , как пример составлен так и формула сделана. делайте реальный пример или сами адаптируйте.
По вопросам из тем форума, личку не читаю.
 
Цитата
artyrH написал:
всякий случай смотрели?
Я не могу знать все ли я случаи рассмотрел. Могу чего то не знать. Но данная сводная не отличается от исходника, проблема отлавливания даты изменения сохраняется.
 
Цитата
БМВ написал:
ну так а что вы хотели , как пример составлен так и формула сделана. делайте реальный пример или сами адаптируйте.
Адаптировать вряд ли получится. Сложноватая для меня формула)
 
=SUMIFS(
INDEX(Таблица1;;MATCH([@[Статья 4 уровень]];Таблица1[#Headers];));
Таблица1[ФИО];[@Менеджер];
Таблица1[Дата изменения];
MAX((Таблица1[ФИО]=[@Менеджер])*(Таблица1[Дата изменения]<=[@Дата])*Таблица1[Дата изменения]))

MAX((Таблица1[ФИО]=[@Менеджер])*(Таблица1[Дата изменения]<=[@Дата])*Таблица1[Дата изменения])
- находит макимальную дату изменений по сотруднику меньшую заданной.
INDEX(Таблица1;;MATCH([@[Статья 4 уровень]];Таблица1[#Headers];)) - нужную колонку исходя из статьи, но важно чтоб заголовок совпадал иначе нужно делать таблицу соответвий.
По вопросам из тем форума, личку не читаю.
 
Цитата
dimabk написал:
не могу знать все ли я случаи рассмотрел
в сообщении #4 на всякий случай имеются формулы.  
 
artyrH, да нормальная у Вас формула, разве что чувствительна к сортировке, ну и если Choose на Index  заменить будет и короче и универсальнее.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Choose на Index
а возможно? а то утром что то пытался я сделать с индексом. не получилось и вместо индекс использовал выбор. еще и к умным таблицам пока не привык.  
 
Цитата
artyrH написал:
а возможно?
так мой вариант с этим INDEX(Таблица1;;MATCH([@[Статья 4 уровень]];Таблица1[#Headers];))
Изменено: БМВ - 04.04.2019 20:21:13
По вопросам из тем форума, личку не читаю.
 
БМВ, спасибо. понял я теперь как применить. у меня еще момент:
Цитата
БМВ написал:
чувствительна к сортировке
при какой сортировке и что может быть?
формула что, может не отловить при сортировке дат?
не уловил я пока..
 
artyrH, исходные данные должны быть отсортированы по дате или полностью, или внутри сотрудника.  По идее так должно быть всегда, но разные случаи бывают
По вопросам из тем форума, личку не читаю.
 
БМВ, понятно что просмотр из нескольких подходящих по условиям вернет тот что ниже по списку
еще вариант для этого случая АГРЕГАТ
Код
=ИНДЕКС(Таблица1;АГРЕГАТ(15;6;СТРОКА(Таблица1[[#Все];[Дата изменения]])/([@Менеджер]=Таблица1[ФИО])/([@Дата]>=Таблица1[Дата изменения]);1);ПОИСКПОЗ([@[Статья 4 уровень]];Таблица1[#Заголовки];0))
 
Подскажите как работает данная конструкция. Я сделал, все работает. Есть конечно ограничения но не критично. Но я не понимаю как она работает.
Код
=ПРОСМОТР(2;1/(Таблица2[@Менеджер]=Таблица1[ФИО])/(Таблица2[@Дата]>=Таблица1[Дата изменения]);ВЫБОР(ПОИСКПОЗ(Таблица2[@[Статья 4 уровень]];{"Заработная плата на руки":"НДФЛ"};0);Таблица1[Оклад];Таблица1[НДФЛ]))
 
можно дополнительный столбец в обе таблицы добавить который будет объединять необходимые параметры через например "_" и потом искать по одному параметру
Страницы: 1 2 След.
Наверх