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 use a data validation formula to check the totals." 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 the OFFSET function for the Refers to formulas, but you could use the INDEX function if you prefer.

因此，我解决了这个难题，并找到了一种使用每个列表的有效数字创建动态命名范围的方法-生产小时数，缺陷小时数，生产分钟数和缺陷分钟数。 我对“引用”公式使用了OFFSET函数 ，但是如果愿意，可以使用INDEX函数 。

限制下拉列表 (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 the written instructions.

有工作表公式，可以计算每个列表的最小和最大数量以及项目数。 您可以在示例文件和书面说明中查看这些公式及其说明。

下载样本文件 (Download the Sample File)

To see how the drop down lists work, download the sample file from the Excel Data Validation - Select Hours and Minutes page on my Contextures website.

若要查看下拉列表的工作方式，请从我的Contextures网站上的“ Excel数据验证-选择小时和分钟”页面下载示例文件。

翻译自: https://contexturesblog.com/archives/2015/08/13/drop-down-hours-and-minutes/

excel下拉时间分钟递增