excel 限制输入金额_限制在Excel中输入的总金额

excel 限制输入金额

限制在Excel中输入的总金额 (Limit the Total Amount Entered in Excel)

You can use Excel's Data Validation to limit the total amount that users enter in a group of cells. For example, if budget is $3500, you can prevent entries that will go over that total amount.

您可以使用Excel的数据验证来限制用户在一组单元格中输入的总数。 例如，如果预算为$ 3500，则可以防止条目超过该总额。

• Cell F1 contains the total amount allowed for the budget, and the cell is named BudgetTotal.

  单元格F1包含预算允许的总额，该单元格名为BudgetTotal 。

• The user can enter budget amounts in cells C3:C7.

  用户可以在单元格C3：C7中输入预算金额。

• Cell C8 sums the amounts entered in cells C3:C7.

  单元格C8对在单元格C3：C7中输入的金额求和。

• Cell F3 shows the amount remaining (BudgetTotal minus total budgeted in C8).

  单元格F3显示剩余金额(BudgetTotal减去C8中的总预算)。

添加数据验证 (Add the Data Validation)

The data validation is applied to cells C3:C7, because this is where the data entry occurs.

数据验证将应用于单元格C3：C7，因为这是发生数据输入的位置。

• Select cells C3:C7
  选择单元格C3：C7
• On the Ribbon, click the Data tab, then click Data Validation. (In Excel 2003, choose Data|Data Validation)
  在功能区上，单击“数据”选项卡，然后单击“数据验证”。 (在Excel 2003中，选择“数据|数据验证”)

• Choose Allow: Custom
  选择允许：自定义

• =SUM($C$3:$C$7)<=BudgetTotal

  = SUM($ C $ 3：$ C $ 7)<=预算总计

• Click OK to close the Data Validation dialog box.
  单击“确定”关闭“数据验证”对话框。

测试数据验证 (Test the Data Validation)

After setting up the data validation, you can test it, to make sure that it works as expected.

设置数据验证后，您可以对其进行测试，以确保它可以按预期工作。

1. In cell C7, enter $900, which is higher than the amount remaining.
   在单元格C7中，输入$ 900，该数字高于剩余金额。
2. Press Enter, and you should see an error message.
   按Enter键，您应该看到错误消息。
3. Click Cancel or Retry, and enter a valid amount.
   单击取消或重试，然后输入有效金额。

翻译自: https://contexturesblog.com/archives/2009/04/07/limit-the-total-amount-entered-in-excel/

excel 限制输入金额