Блог

Мне нужна помощь с формулой в Excel. Я пытаюсь перебрать диапазон значений в разных столбцах с учетом некоторого уникального индекса.

Заранее благодарим вас за вашу любезную поддержку!

У меня есть следующие данные:

Таблица 1 содержит столбцы для GoodNumber (GN), Цены покупки, справочной цены.

 GN | Purchase Price | Reference Price (?)    
E | 2,36 |    
C |4,25 |    
F | 9,3 |    
A   | 1,2   |    
B | 7,63    |    
D   |5,47   | 
  

Таблица 2 содержит список GoodNumbers с разными рыночными ценами за GN (GN из таблицы 1 соответствует GN из таблицы 2):

 GN |    Market Prices    
A | 2,99    
B | 1,49    
B | 2,99    
B | 5,97    
B | 5,97    
B | 2,99    
B | 2,24    
B | 2,99    
C | 3,73    
C | 6,72    
C | 5,22    
C | 5,97    
D | 0,75    
D | 2,99    
E | 3,73    
E | 5,22    
E | 6,72    
E | 2,24    
E | 4,48    
E | 5,22    
E | 6,72    
F | 1,49    
F | 6,72    
F | 2,99    
F | 2,99
  

Я хотел бы найти «справочную цену», которая должна быть:

1. ближайшее соответствие между ценой покупки (таблица 1) и рыночными ценами (таблица 2) с учетом некоторого уникального GN (из таблицы 1), только если какая-либо из рыночных цен> Цена покупки.

2. Возвращает «Справочную цену N / A», если условие не выполнено (рыночные цены> Цена покупки).

Результат в этом случае должен быть следующим:

 GN | Purchase Price | Reference Price (?)    
E | 2,36 | 3,73    
C |4,25 | 5,22    
F | 9,3 | Reference Price N/A    
A   | 1,2   | 2,99    
B | 7,63    | Reference Price N/A    
D   |5,47   | Reference Price N/A
  

Если у вас Office 365 Excel, используйте MINIFS:

 =MINIFS(H:H,H:H,">="amp;B2,G:G,A2)
  

Это вернет 0 not Reference Price N/A , но вы можете поместить это в IF, чтобы сделать это, если хотите:

 =IF(MINIFS(H:H,H:H,">="amp;B2,G:G,A2),MINIFS(H:H,H:H,">="amp;B2,G:G,A2),"Reference Price N/A")
  

Если у вас нет Office 365 Excel, используйте AGGREGATE:

 =IFERROR(AGGREGATE(15,7,$H$2:$H$26/(($H$2:$H$26>=B2)*($G$2:$G$26=A2)),1),"Reference Price N/A")
  

Примечание AGGREGATE — это формула типа массива, и поэтому ссылочные данные должны быть ограничены набором данных, в то время как MAXIFS позволяет использовать полные ссылки на столбцы.