英文:
Is there an efficient way to do this excel function
问题
在Excel中，我想要将行程的开始时间区分为四个“一天中的时间段”（早上、下午、晚上、夜晚）。
我制作了这个函数，start_time列包含时间（hh:mm:ss）：
=IF(
AND([@[start_time]] >= TIME(20,0,0),
[@[start_time]] <= TIME(23,59,59)
),
"夜晚",
IF(
AND([@[start_time]] >= TIME(0,0,0),
[@[start_time]] < TIME(6,0,0)
),
"夜晚",
IF(
AND([@[start_time]] >= TIME(6,0,0),
[@[start_time]] < TIME(12,0,0)
),
"早上",
IF(
AND([@[start_time]] >= TIME(12,0,0),
[@[start_time]] < TIME(16,0,0)
),
"下午",
"晚上"
)
)
)
)
我想知道是否有改进这个函数或使其更简单的方法。谢谢！
英文:
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)
=IF(
AND([@[start_time]] >= TIME(20,0,0),
[@[start_time]] <= TIME(23,59,59)
),
"Night",
IF(
AND([@[start_time]] >= TIME(0,0,0),
[@[start_time]] < TIME(6,0,0)
),
"Night",
IF(
AND([@[start_time]] >= TIME(6,0,0),
[@[start_time]] < TIME(12,0,0)
),
"Morning",
IF(
AND([@[start_time]] >= TIME(12,0,0),
[@[start_time]] < TIME(16,0,0)
),
"Afternoon",
"Evening"
)
)
)
)
I was wondering if there is any way to improve this function or make it more easy
Thanks in advance!
答案1
得分: 1
使用vlookup()函数如下所示：
VLOOKUP(E2,$B$4:$C$8,2,1)
我发现的优势是时间和文本可以轻松编辑。
英文:
So, using vlookup() as suggested:
VLOOKUP(E2,$B$4:$C$8,2,1)
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语句：
=IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(6,0,0), "Night",
IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(12,0,0), "Morning",
IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(16,0,0), "Afternoon",
IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(20,0,0), "Evening", "Night")
)
)
)
这里的-ROUNDDOWN([@[Start Time]],0)允许你输入带有日期或不带日期的时间。如果你的时间没有日期附加，则不需要这部分。
使用查找表替代
使用查找表的好处在于你可以轻松查看和更改单个位置的截止时间，而不必搜索多个公式。
对于查找，你绝对不应该使用VLOOKUP。INDEX/MATCH在各个方面都更好。它更高效、更稳定、更灵活，而且在熟悉后可能更容易使用。
=INDEX(RefTbl[Times Of Day],MATCH([@[Start Time]]-ROUNDDOWN([@[Start Time]],0),RefTbl[End Time],-1))
英文:
Firstly, you can significantly simplify your IF statement by removing the ANDs:
=IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(6,0,0), "Night",
IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(12,0,0), "Morning",
IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(16,0,0), "Afternoon",
IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(20,0,0), "Evening", "Night")
)
)
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.
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.
=INDEX(RefTbl[Times Of Day],MATCH([@[Start Time]]-ROUNDDOWN([@[Start Time]],0),RefTbl[End Time],-1))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论