Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Интервальный просмотр по двум параметрам
 
#1
23.11.2014 20:41:00
Инспирировано статьей ВПР (VLOOKUP) с интервальным просмотром

Цена на товар не является статической и со временем меняется. Необходимо найти возможность получить цену товара, которая была актуальной на момент его продажи.

Предлагается решение задачи (реализовано в Excel 2010)




Создаем две таблицы: Прайс и Журнал. В прайсе указываем цены на два товара Товар1 и Товар2, которые изменяются с течением времени.
В таблице Журнал ведем журнал продаж.

формулу для цены в таблице Журнал для удобства я разбил на две части. Первая часть вычисляет ближайшую меньшую дату из таблицы Прайс для выбранного товара:

Код
БлижМенДата=НАИБОЛЬШИЙ(Прайс[Дата]*(Прайс[Дата]<=Журнал[@Дата])*(Прайс[Товар]=Журнал[@Товар]);1)
Эту часть формулы записываем в Диспетчер имен (раздел Формулы).

Далее в таблицу Журнал в столбец Цена вводит следующую формулу:

Код
{=ВПР(БлижМенДата&[@Товар];ВЫБОР({1;2};Прайс[Дата]&Прайс[Товар];Прайс[Цена]);2;0)}
Это формула массива, поэтому вводим ее при помощи Ctrl+Shift+Enter

В результате, находим цену для каждого товара, которая действовала на момент его продажи.

Решение реализовано таким образом, что не зависит от сортировки таблицы Прайс.
Изменено: Cepro - 23.11.2014 20:48:26
 
 
 
#2
23.11.2014 20:58:33
И? Если желаете поделиться с другими - почему не в файле? Вдруг можно проще или по-другому - рассматривать картинку, а потом создавать отдельный файл... сделать снимок, чтобы показать свою картинку?
 
 
 
#3
23.11.2014 21:00:33
Цитата
vikttur пишет: почему не в файле?
Как не файле? Приложил же!
(Только не с первого раза - слегка запутался в интерфейсе)
 
 
 
#4
23.11.2014 21:01:35
много букав если чесно не понял че надо даже посмотрев пример
Лень двигатель прогресса, доказано!!!
 
 
 
#5
23.11.2014 21:01:40
Уже вижу.
Когда писал сообщение, Вы еще путались в интерфейсе :)
 
 
 
#6
23.11.2014 21:24:00
Без использования именованного диапазона:
=ПРОСМОТР(2;1/(($A$3:$A$10<=E3)*($B$3:$B$10=F3));$C$3:$C$10)
 
 
 
#7
23.11.2014 21:49:18
Здорово! Отличное решение.
Перевел в диапазоны:
Код
=ПРОСМОТР(2;1/((Прайс[Дата]<=[@Дата])*(Прайс[Товар]=[@Товар]));Прайс[Цена])
Если не трудно, разъясните как работает, пожалуйста. Справка по функции ПРОСМОТР не помогает ((
("Ищет значение в ... массиве. Включен для ... обратной совместимости" ;)
 
 
 
#8
23.11.2014 21:58:43
1/(($A$3:$A$10<=E3)*($B$3:$B$10=F3)) - массив из единиц и ошибок (деление на ноль).
Просим функцию найти наибольшее число, которого точно не будет в диапазоне поиска - двойку. Функция найдет позицию последнего числового значения, игнорируя ошибки, и покажет то, что мы просим показать - значение диапазона $C$3:$C$10, которое находится на найденной позиции.

Вычитал в заглавном сообщении:
Цитата
Решение реализовано таким образом, что не зависит от сортировки таблицы Прайс.
Для этого случая сложнее (формула массива):
Код
=ИНДЕКС(Прайс[Цена];ПОИСКПОЗ(МАКС(ЕСЛИ(Прайс[Товар]=[@Товар];ЕСЛИ(Прайс[Дата]<=[@Дата];Прайс[Дата])));ЕСЛИ(Прайс[Товар]=[@Товар];ЕСЛИ(Прайс[Дата]<=[@Дата];Прайс[Дата]));))
 
 
 
#9
23.11.2014 22:09:07
Да, с функцией ПРОСМОТР пришел к тому же выводу... )
Проверил универсальный вариант - ОК.

Но мой вариант, по-моему, нагляднее  ;)
Изменено: Cepro - 23.11.2014 22:27:07
 
 
 
#10
23.11.2014 22:10:50
Вы читайте, читайте. Сообщение Выше.
 
 
 
#11
23.11.2014 22:17:36
Да, форум - не чат ))
Не успел я отредактировать сообщение, как вы уже ответили на первый вариант ))

Спасибо!
 
 
 
#12
23.11.2014 22:34:06
Цитата
...вариант... нагляднее
Выведите найденный диапазон в имя:
=ИНДЕКС(Прайс[Цена];ПОИСКПОЗ(МАКС(диапазон;диапазон;))
 
 
 
#13
24.11.2014 10:38:25
Да, отлично получилось!
Обновил файл - сделал два варианта:
 
 
 
Страницы: 1
Наверх