excel下拉时间分钟递增_下拉⼩时和分钟
excel下拉时间分钟递增
数据验证怎么设置下拉菜单
Last week, someone asked me how to create drop down lists for hours and minutes, and also control the total time that was entered. Production time and Defect time would be entered, and Defect total time could not be greater than the Production total time.
上周，有⼈问我如何为⼩时和分钟创建下拉列表，并控制输⼊的总时间。 将输⼊⽣产时间和缺陷时间，并且缺陷总时间不能⼤于⽣产总时间。
He sent a sample file, with drop down lists in place, but they allowed invalid times to be entered. Was there any way to make it work?
他发送了⼀个⽰例⽂件，并带有下拉列表，但它们允许输⼊⽆效时间。 有什么办法可以使它起作⽤？
做不到 (It Can't Be Done)
My first response was, "You can't do that with drop down lists. Just type the hours and minutes, and ." I've used that technique in other workbooks, to compare totals, like the budget example, shown below.
我的第⼀个回答是，“您不能使⽤下拉列表来做到这⼀点。只需输⼊⼩时和分钟，然后 。 我在其他⼯作簿中使⽤了该技术来⽐较总计，例如下⾯所⽰的预算⽰例。
The reply to my response was polite, but persistent, "Thank you, and if you can think of a way to use drop downs, please let me know."
对我的答复的回复是客⽓的，但始终如⼀：“谢谢，如果您能想到使⽤下拉菜单的⽅法，请告诉我。”
接受挑战 (Accepting the Challenge)
That made me start thinking about ways to control the time entry drop downs. A cell can't combine drop down lists with custom data validation rules, but maybe I could create lists with only the valid numbers.
这使我开始考虑控制时间输⼊下拉菜单的⽅法。 单元格不能将下拉列表与⾃定义数据验证规则结合在⼀起，但是也许我可以仅使⽤有效数字来创建列表。
So, I tackled the challenge, and found a way to create dynamic named ranges with the valid numbers for each list –Production Hours, Defect Hours, Production Minutes and Defect Minutes. I used for the Refers to formulas, but you could use if you prefer.
因此，我解决了这个难题，并到了⼀种使⽤每个列表的有效数字创建动态命名范围的⽅法-⽣产⼩时数，缺陷⼩时数，⽣产分钟数和缺陷分钟数。 我对“引⽤”公式使⽤ ，但是如果愿意，可以使⽤ 。
限制下拉列表 (Limit the Drop Down Lists)
Each drop down list is based on one of the dynamic named ranges, and they follow these rules:
每个下拉列表均基于动态命名范围之⼀，并且遵循以下规则：
1. Production time must be equal to, or greater than, Defect time
⽣产时间必须等于或⼤于缺陷时间
2. Defect time must be less than, or equal to, Production time
缺陷时间必须⼩于或等于⽣产时间
3. Production time must be entered before Defect time can be entered (Defect drop downs do not work until Production
time is entered
必须输⼊⽣产时间才能输⼊缺陷时间(在输⼊⽣产时间之前，缺陷下拉菜单不起作⽤
In the screen shot below, you can see the Defect Hours drop down. Production Hours has been set at 4 hours, so the Defect Hours drop down only shows the numbers from 1 to 4.
在下⾯的屏幕截图中，您可以看到“缺陷时间”下拉列表。 ⽣产时间已设置为4⼩时，因此“缺陷时间”下拉列表仅显⽰从1到4的数字。
There are controls on the Production drop downs too, to prevent people from changing to an invalid time, after entering Defect times.
在“⽣产”下拉菜单中也有控件，以防⽌⼈们在输⼊“缺陷时间”后更改为⽆效时间。
In the screen shot below, the Production Minutes has a minimum of 32, so it can't be set lower than the Defect time.
在下⾯的屏幕截图中，⽣产分钟数⾄少为32，因此不能将其设置为低于缺陷时间。
计算最⼩值和最⼤值 (Calculate the Minimums and Maximums)
There are worksheet formulas that calculate the minimum and maximum number for each list, and the number of items. You can see the formulas, and their explanations, in the sample file, and .
有⼯作表公式，可以计算每个列表的最⼩和最⼤数量以及项⽬数。 您可以在⽰例⽂件和查看这些公式及其说明。
下载样本⽂件 (Download the Sample File)
To see how the drop down lists work, download the sample file from the page on my Contextures website.
若要查看下拉列表的⼯作⽅式，请从我的Contextures⽹站上的“ 页⾯下载⽰例⽂件。
excel下拉时间分钟递增

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。