Как сопоставить ордера на ПОКУПКУ/ПРОДАЖУ в Excel?

У меня есть электронная таблица с 4 столбцами (тип товара, тип заказа, цена и объем). Электронная таблица работает как торговая книга.

Что я пытаюсь сделать, так это создать формулу для сопоставления типов ордеров на ПОКУПКУ и ПРОДАЖУ из списка ордеров по объему.

Так, например, если существует заказ на ПОКУПКУ 100 апельсинов по цене 12 долларов США и существует заказ на ПРОДАЖУ 100 апельсинов по цене 13 долларов США, оба будут иметь совпадение 0%.

Если существует заказ на ПОКУПКУ для 100 яблок по цене 12 долларов США и существует заказ на ПРОДАЖУ для 85 яблок по цене 11 долларов США, в столбце соответствия заказа на продажу будет 100%, а в столбце соответствия заказа на ПОКУПКУ — 85%.

Затем я бы отдал предпочтение совпадению по самой низкой цене.

Так, например:

Item Type        Order Type     Price     Volume     Match
----------       ----------    -------   --------   -------

Orange            SELL          $13        100       0%
Orange            BUY           $12        100       0%       
Apple             SELL          $11         85       100%
Apple             BUY           $12        100       85%

Я пытался связать бесконечно сложные операторы ЕСЛИ с разными (ВПР, ИНДЕКС, ПОИСКПОЗ), но они становятся слишком сложными и теряют смысл.

Мне даже не нужен кто-то, чтобы помочь написать формулу, может быть, просто указать мне правильное структурное направление с тем, что использовать, чтобы я мог написать что-то, что работает.

Любая помощь могла бы быть полезна.


excel excel-formula
person Jared Smith    schedule 06.04.2019    source источник
comment
Добро пожаловать в stackoverflow.com. Для этого нет маленькой формулы. Что вы можете сделать, так это использовать SUMIFS для получения объема каждого ордера, а затем использовать IF для определения значения столбца соответствия. Другой вариант - написать пользовательскую функцию VBA для ее расчета, и в этом случае сложность будет перенесена в VBA, и у вас может быть простая формула в вашей электронной таблице.   -  person GCSDC    schedule 07.04.2019
comment
Спасибо GCSDC. Я попробовал что-то вроде этого: =ЕСЛИ(B3:B=B2,IF(C3:C‹›C2,СУММЕСЛИ(D3:D,‹=D2))) Но не смог заставить его работать . Не уверен, что это вообще имеет смысл :) Но идея была в том, что если товар совпадает, а тип заказа не совпадает, суммировать число.   -  person Jared Smith    schedule 07.04.2019

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете использовать эту формулу (учитывая, что ваши данные начинаются с ячейки A1):

=IF(SUMIFS($D$2:$D$5;$A$2:$A$5;A2;$B$2:$B$5;"SELL") = SUMIFS($D$2:$D$5;$A$2:$A$5;A2;$B$2:$B$5;"BUY"); 0; SUMIFS($D$2:$D$5;$A$2:$A$5;A2;$B$2:$B$5;IF(B2="SELL";"BUY";"SELL")))

который проверяет, равна ли сумма объема покупки и продажи для типа элемента. Если да, то взять сумму объемов противоположной операции. Если нет, вернуть 0;

Вы также можете использовать именованные диапазоны, чтобы сделать формулу чище и понятнее.

person GCSDC    schedule 06.04.2019