索引并匹配其他条件（仅适用于Excel） [英] Index and match with an additional condition (excel only)

问题描述

继续此主题，我想补充一下还有一个条件。我有以下开始，结束和操作列：

Continuing this topic, I would like to add one more condition. I have the following start, end, and action columns:

11/9/2007   10/10/2008  SELL
11/14/2008  5/29/2009   BUY
11/27/2009  2/5/2010    SELL
10/8/2010   4/8/2011    SELL

从2007年4月1日到今天，我还有目标天（周末除外）。我想执行以下操作：

I also have target days (ex-weekends) starting from April 1, 2007 to today. I would like to do the following:

• 如果目标日在开始和结束的范围是
  AND 操作是卖出，然后打印出 -1 ；


• 如果目标的日期在开始和结束日期
  AND 操作为购买，然后打印 1 ；


• 如果目标的天不在开始和结束日期，然后打印出 0 ；

• If the target day is in the range of start and end dates AND action is SELL, then print out -1;
• If the target day is in the range of start and end dates AND action is BUY, then print out 1;
• If the target day is not in the range of start and end dates, then print out 0;

任何指导/提示都一如既往。

Any guidance/tips appreciated as always.

推荐答案

我认为这段代码将帮助您解决问题

I think this code will help you problem

Dim dStart As Date
Dim dEnd As Date
Dim dDate As Date
Dim iCol As Integer

iCol = 2

Do While Cells(iCol, 2).Value <> ""
    dStart = Format(Cells(iCol, 2).Value, "mm/dd/yyyy")
    dEnd = Format(Cells(iCol, 3).Value, "mm/dd/yyyy")
    if dDate > dStart and dDate < dEnd then
        if Cell(iCol,4).Value = "SELL" then
            printout -1
        else
            printout 1
        end if
    else
        printout 0
    end if
    iCol = iCol + 1
Loop

如果只需要excel功能

If you just only need excel function

=IF(AND(J3 >= F3,J3 <= G3, H3="SELL"),-1,IF(AND(J3 >= F3,J3 <= G3, H3="BUY"),1,0))

这篇关于索引并匹配其他条件（仅适用于Excel）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！