layui 单元格下拉_清除相关的下拉单元格

layui 单元格下拉

You can use data validation to create a drop down list on an Excel worksheet. For example, show a list of countries, if you click a cell in column B.

您可以使用数据验证在Excel工作表上创建一个下拉列表。 例如，如果您单击B列中的单元格，则显示国家列表。

After you’ve set up that first list, you can make another list, in column C, to show the cities for the selected country.

设置完第一个列表后，您可以在C列中创建另一个列表，以显示所选国家/地区的城市。

Here is the list of cities, showing only the options for Canada.

这是城市列表，仅显示加拿大的选项。

We’ll see how to set this up, and prevent problems, by clearing out the city cell, when necessary

我们将了解如何进行设置，以及在必要时通过清除城市单元来防止问题

设置从属下拉列表 (Set up Dependent Drop Down Lists)

When one drop down list is based on a selection in another cell, I call that a dependent drop down. I’ve seen other names for the technique, such as “conditional drop down list”. Whatever you call it, it’s fairly easy to set up, as you can see in the video below.

当一个下拉列表基于另一个单元格中的选择时，我称其为依赖下拉列表。 我见过该技术的其他名称，例如“有条件的下拉列表”。 不论您怎么说，它都非常容易设置，如下面的视频所示。

For written instructions, visit one of these pages on my website:

有关书面说明，请访问我的网站上的以下页面之一：

• Create Dependent Drop Down Lists

  创建从属下拉列表

• Dependent Dropdowns from a Sorted List

  排序列表中的从属下拉列表

  Dependent Lists With INDEX

  INDEX的从属列表

  演示地址

  选择不匹配的问题 (Problems with Mismatched Selections)

  This technique works nicely, until some troublemaker decides to go back to column B, and select a different country, after selecting a city.

  该技术效果很好，直到麻烦制造者决定在选择城市之后决定返回B列并选择其他国家/地区。

  If they don’t re-select from the city column too, you’ll end up with mismatched countries and cities.

  如果他们也没有从“城市”列中重新选择，那么您最终将遇到不匹配的国家和城市。

  

  防止数据验证不匹配 (Prevent Mismatches With Data Validation)

  To prevent mismatches, you can change the data validation formula in the Country column. Instead of just showing the Country list, the data validation can check column C for an entry first. Then, if there is something in the City column, Excel doesn’t show the Region list.

  为防止不匹配，您可以在“国家/地区”列中更改数据验证公式。 数据验证不仅可以显示“国家/地区”列表，还可以首先检查C列是否有条目。 然后，如果“城市”列中有内容，则Excel不会显示“地区”列表 。

  For example, use this formula:

  例如，使用以下公式：

  =IF(C2="",CountryList,INDIRECT(“Nothing”))

  = IF(C2 =“”，CountryList，INDIRECT(“ Nothing”))

  If a City is selected in a row, the Country drop down doesn’t work. You’ll have to clear the City cell, then select a different Country.

  如果连续选择一个城市，则“国家/地区”下拉列表将不起作用。 您必须清除城市单元格，然后选择其他国家。

  

  使用宏清除城市单元格 (Use a Macro to Clear the City Cell)

  If you frequently make changes in the first drop down, you might get tired of manually clearing the dependent cell. Life is too short, to be aggravated by little things like that!

  如果您经常在第一个下拉列表中进行更改，则可能会厌倦了手动清除相关单元格的麻烦。 生命太短了，不能被诸如此类的小事情加重！

  So, if you’re able to use macros in your workbook, you can set up a few lines of code that runs automatically, to clear the city cell, when someone selects a country. When cell B5 is changed in the screen shot below, cell C5 will be cleared.

  因此，如果您能够在工作簿中使用宏，则可以设置几行自动运行的代码，以在有人选择一个国家时清除城市单元格。 在下面的屏幕截图中更改了单元格B5时，将清除单元格C5。

  

  清除单元格的代码 (Code to Clear the Cell)

  In the sample code below, the first drop down is in column B, and the code will clear any data in column C, in the same row.

  在下面的示例代码中，第一个下拉列表在B列中，该代码将清除同一行中C列中的所有数据。

  • The code checks the column number of the cell that was changed, to see if it is in column B (2).
    该代码检查已更改的单元格的列号，以查看它是否在B列(2)中。
  • If there is a data validation drop down list in that cell, the Validation.Type property is equal to 3
    如果该单元格中有一个数据验证下拉列表，则Validation.Type属性等于3
  • In that case, the cell that is 1 column to the right has its contents cleared.
    在这种情况下，右边1列的单元格将清除其内容。

  This code would be pasted onto the sheet’s code module. There are instructions here.

  该代码将粘贴到工作表的代码模块上。 这里有说明 。

  Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  If Target.Column = 2 Then
    If Target.Validation.Type = 3 Then
     Application.EnableEvents = False
     Target.Offset(0, 1).ClearContents
    End If
  End If
  
  exitHandler:
    Application.EnableEvents = True
    Exit Sub
  
  End Sub
  

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

  To download the sample file, go to the Dependent Data Validation page, and go to the Download section.

  要下载示例文件，请转到Dependent Data Validation页面 ，然后转到Download部分。

  In the Download section, look for the file named Clear Dependent Cell After Selecting.

  在“下载”部分中，选择后查找名为Clear Dependent Cell的文件。

翻译自: https://contexturesblog.com/archives/2014/09/11/clear-dependent-drop-down-cells/

layui 单元格下拉