有没有一种高效的方法来执行这个Excel函数?

huangapple go评论98阅读模式
英文:

Is there an efficient way to do this excel function

问题

在Excel中,我想要将行程的开始时间区分为四个“一天中的时间段”(早上、下午、晚上、夜晚)。

我制作了这个函数,start_time列包含时间(hh:mm:ss):

  1. =IF(
  2. AND([@[start_time]] >= TIME(20,0,0),
  3. [@[start_time]] <= TIME(23,59,59)
  4. ),
  5. "夜晚",
  6. IF(
  7. AND([@[start_time]] >= TIME(0,0,0),
  8. [@[start_time]] < TIME(6,0,0)
  9. ),
  10. "夜晚",
  11. IF(
  12. AND([@[start_time]] >= TIME(6,0,0),
  13. [@[start_time]] < TIME(12,0,0)
  14. ),
  15. "早上",
  16. IF(
  17. AND([@[start_time]] >= TIME(12,0,0),
  18. [@[start_time]] < TIME(16,0,0)
  19. ),
  20. "下午",
  21. "晚上"
  22. )
  23. )
  24. )
  25. )

我想知道是否有改进这个函数或使其更简单的方法。谢谢!

英文:

In excel I wanted to differentiate the starting time of a trip into 4 'times of day' (Morning, afternoon, evening, Night)

I made this script, the start_time column consists of time (hh:mm:ss)

  1. =IF(
  2. AND([@[start_time]] &gt;= TIME(20,0,0),
  3. [@[start_time]] &lt;= TIME(23,59,59)
  4. ),
  5. &quot;Night&quot;,
  6. IF(
  7. AND([@[start_time]] &gt;= TIME(0,0,0),
  8. [@[start_time]] &lt; TIME(6,0,0)
  9. ),
  10. &quot;Night&quot;,
  11. IF(
  12. AND([@[start_time]] &gt;= TIME(6,0,0),
  13. [@[start_time]] &lt; TIME(12,0,0)
  14. ),
  15. &quot;Morning&quot;,
  16. IF(
  17. AND([@[start_time]] &gt;= TIME(12,0,0),
  18. [@[start_time]] &lt; TIME(16,0,0)
  19. ),
  20. &quot;Afternoon&quot;,
  21. &quot;Evening&quot;
  22. )
  23. )
  24. )
  25. )

I was wondering if there is any way to improve this function or make it more easy
Thanks in advance!

答案1

得分: 1

使用vlookup()函数如下所示:

  1. VLOOKUP(E2,$B$4:$C$8,2,1)

有没有一种高效的方法来执行这个Excel函数?

我发现的优势是时间和文本可以轻松编辑。

英文:

So, using vlookup() as suggested:

  1. VLOOKUP(E2,$B$4:$C$8,2,1)

有没有一种高效的方法来执行这个Excel函数?

The advantage I find is that the times and the text are easily edited.

答案2

得分: 0

近期的Excel版本有两个函数可用于此目的:switch() 处理多个 IF(),而 LET() 则允许您在公式内部创建临时变量。 (我本来会将此内容放在评论中,但两个较长的URL会使评论过长)

英文:

Recent Excel version has two functions you might use for this: switch() handles the multiple IF() and LET() gives you the opportunity to create a temporary variable inside your formula. (I would have put this in a comment, but two large URLs make this comment too large)

答案3

得分: 0

首先,你可以通过去掉AND部分来显著简化你的IF语句:

  1. =IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(6,0,0), "Night",
  2. IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(12,0,0), "Morning",
  3. IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(16,0,0), "Afternoon",
  4. IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(20,0,0), "Evening", "Night")
  5. )
  6. )
  7. )

这里的-ROUNDDOWN([@[Start Time]],0)允许你输入带有日期或不带日期的时间。如果你的时间没有日期附加,则不需要这部分。

使用查找表替代

使用查找表的好处在于你可以轻松查看和更改单个位置的截止时间,而不必搜索多个公式。

对于查找,你绝对不应该使用VLOOKUPINDEX/MATCH在各个方面都更好。它更高效、更稳定、更灵活,而且在熟悉后可能更容易使用。

  1. =INDEX(RefTbl[Times Of Day],MATCH([@[Start Time]]-ROUNDDOWN([@[Start Time]],0),RefTbl[End Time],-1))

有没有一种高效的方法来执行这个Excel函数?

英文:

Firstly, you can significantly simplify your IF statement by removing the ANDs:

  1. =IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) &lt; TIME(6,0,0), &quot;Night&quot;,
  2. IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) &lt; TIME(12,0,0), &quot;Morning&quot;,
  3. IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) &lt; TIME(16,0,0), &quot;Afternoon&quot;,
  4. IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) &lt; TIME(20,0,0), &quot;Evening&quot;, &quot;Night&quot;)
  5. )
  6. )

The -ROUNDDOWN([@[Start Time]],0) here, allows you to enter a time with or without a date. It is not needed if your times do not also have a date attached.

有没有一种高效的方法来执行这个Excel函数?

Using a lookup table instead

The benefit of using a lookup table is that you can easily see and change the cut off times in a single place, rather than searching through multiple formulas.

For Lookups, you should NEVER use VLOOKUP. INDEX/MATCH is better in every way. It is more efficient, less fragile, more versatile, and arguably easier to use, once you are familiar with it.

有没有一种高效的方法来执行这个Excel函数?

  1. =INDEX(RefTbl[Times Of Day],MATCH([@[Start Time]]-ROUNDDOWN([@[Start Time]],0),RefTbl[End Time],-1))

huangapple
  • 本文由 发表于 2023年2月6日 18:30:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/75360104.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定