筛选空白单元格_具有空白单元格的动态列表

筛选空白单元格

If a list contains blank cells, the usual method for creating a dynamic named range doesn’t work. Usually, you would use an OFFSET formula, and count the entries in the column, to calculate the number of rows in the range. Here is a workaround to create a dynamic list with blank cells.

如果列表包含空白单元格，则创建动态命名范围的常用方法不起作用。 通常，您将使用OFFSET公式并计算该列中的条目，以计算该范围内的行数。 这是一种使用空白单元格创建动态列表的解决方法。

列中的空白单元格 (Blank Cells in a Column)

When there are blanks, as in the screen shot below, the dynamic range with blank cells is incorrect. There are 9 items, so 9 rows are included, but the last item is in the 12th row of the list.

当有空白时，如下面的屏幕截图所示，带有空白单元格的动态范围不正确。 有9个项目，因此包含9行，但最后一项在列表的第12行中。

If you create a drop down list based on this dynamic range  of 9 rows, it includes blanks, and August is the last month, instead of December.

如果您基于此9行的动态范围创建一个下拉列表，则该列表将包含空格，并且8月是最后一个月，而不是12月。

解决问题 (Fix the Problem)

To fix the problem, create a list with all the items, and no blank cells. That will create a usable source list for the drop downs, instead of trying to create a dynamic list with blank cells.

要解决此问题，请创建一个包含所有项目且没有空白单元格的列表。 这将为下拉列表创建可用的源列表，而不是尝试使用空白单元格创建动态列表。

编号非空白单元格 (Number the Non-Blank Cells)

To create a drop down list without blanks, based on a range with blank cells, add formulas to the worksheet.

若要基于空白单元格的范围创建不带空白的下拉列表，请向工作表中添加公式。

First, add a formula in cell A2, and copy it down to cell A13. This will number the cells that are not blank.

首先，在单元格A2中添加一个公式，然后将其复制到单元格A13中。 这将为非空白的单元格编号。

=IF(B2="","",MAX(A$1:A1)+1)

= IF(B2 =“”，“”，MAX(A $ 1：A1)+1)

创建一个没有空格的列表 (Create a List Without Blanks)

Next, to create a source list without blanks, use formulas to pull the numbered items into a new column.

接下来，要创建不带空格的源列表，请使用公式将编号的项目拉到新列中。

Enter this formula into cell D2, and copy down to D13. This INDEX/MATCH formula creates a list with all the blanks at the end.

在单元格D2中输入此公式，然后向下复制到D13。 此INDEX / MATCH公式创建一个列表，所有末尾都有空白。

=IFERROR(INDEX($B$2:$B$13,MATCH(ROW()-ROW($D$1),$A$2:$A$13,0)),"")

= IFERROR(INDEX($ B $ 2：$ B $ 13，MATCH(ROW()-ROW($ D $ 1)，$ A $ 2：$ A $ 13,0))，“”)

创建没有空白的动态范围 (Create a Dynamic Range Without Blanks)

After you use formulas to create a second list, without the blanks, you can name that range, and base the dynamic range on that.

使用公式创建第二个列表(不含空格)后，可以命名该范围，并在此基础上确定动态范围。

This range is named ListMonths, and uses the MAX from column A as the row count.

此范围名为ListMonths，并使用A列中的MAX作为行数。

=OFFSET(Sheet1!$D$1,1,0,MAX(Sheet1!$A:$A),1)

=偏移(Sheet1！$ D $ 1,1,0，MAX(Sheet1！$ A：$ A)，1)

新动态范围 (New Dynamic Range)

If you create a drop down list based on the ListMonths range, it does not include blanks, and December is the last month.

如果您基于ListMonths范围创建下拉列表，则该列表不包含空格，并且12月是最后一个月。

Instead of the original dynamic list with blank cells, the new list does not have blanks, and can be used for the drop down lists.

新列表没有空白，而不是原始的动态列表具有空白单元格，并且可以用于下拉列表。

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

To see the formulas, you can download the file from my Contextures website.

若要查看公式，可以从Contextures网站下载文件。

On the Sample Excel Files page, go to the Data Validation, and look for DV0063 - Dynamic List With Blank Cells. The zipped file is in xlsx format, and does not contain macros.

在“样本Excel文件”页上，转到“数据验证”，然后查找DV0063-带有空白单元格的动态列表 。 压缩文件为xlsx格式，不包含宏。

翻译自: https://contexturesblog.com/archives/2014/02/27/dynamic-list-with-blank-cells/

筛选空白单元格