如何使用具有多个条件的“IF”代码？(How to use “IF” code with multiple conditions?)

如何使用具有多个条件的“IF”代码？(How to use “IF” code with multiple conditions?)

我有大约5万行的5列数据。 这是救护车对事故的响应时间。 我试图找出多辆救护车应对单一事故的事件总数。 在多个救护车同时到达的情况下，“IF”功能在某种程度上是有用的，但在不同时，它认为这是一个不同的事件。 我想添加一个20分钟的缓冲区，但我无法弄清楚如何合并它。 第二个问题是事件编号。 事件2014-014374-006,2014-014374-009是一样的，只是结局数字是不同的。 我如何区分？ 我可以在Excel或其他平台上做到吗？ http://imgur.com/a/30VHl

I have 5 columns of data with around 50,000 rows. This is the ambulance response times to an incident. I am trying to figure out the total number of incidents as multiple ambulances respond to a single incident. The 'IF' function has been useful upto a certain extent where the multiple ambulances reached at the same time but when it is not at the same time, it considers it as a different incident. I would like to add a buffer of 20 minutes but I am not able to figure out how to incorporate that. The second problem is with the incident number. The incidents 2014-014374-006, 2014-014374-009 are the same, just the ending numbers are different. How do I differentiate? Can I do it in excel or other platforms? http://imgur.com/a/30VHl

最满意答案

要返回事件编号，请使用如下公式：“= IF（ISERROR（SEARCH（” - “，D5，SEARCH（” - “，D5）+1）），D5，LEFT（D5，SEARCH（” - “ ，D5，SEARCH（“ - ”，D5）+1）-1））“其中D5是具有您的事件编号的单元格。

这个公式所做的是首先创建一个if语句，它将允许您确定事件是否有第二个短划线或者它没有。 如果没有，那么它会返回单元格中的值。 如果是，它会将文本返回到该短划线的左侧。 搜索函数查找子字符串，并通过嵌套搜索并向第一个搜索的值添加“1”，我们正在查找找到第一个搜索后的子字符串。

这是第一次使用它时一个相当混乱的公式，但一旦你理解它，它就像一个魅力。

我会看看我能否确定日期。 这可能会更困难。 如果您可以简单地使用事件编号，您可能会有更轻松的时间。

To return the incident number, use a formula like this: "=IF(ISERROR(SEARCH("-",D5,SEARCH("-",D5)+1)),D5,LEFT(D5,SEARCH("-",D5,SEARCH("-",D5)+1)-1))" where D5 is the cell with your incident number.

What this formula does is it first creates an if statement that will allow you to determine if the incident has the second dash or if it doesnt. If it doesn't, then it will return the value in the cell. If it does, it will return the text to the left of that dash. The search function looks for a substring, and by nesting a search and adding "1" to the value of the first search, we are looking for the substring that comes after the first one is found.

It is a rather confusing formula the first time you use it, but it works like a charm once you understand it.

I will see if I can figure the dates out. That might be more difficult. If you can simply use the incident number for this, you will likely have an easier time.