计算最后一个单元格和下一个非空白单元格之间的行中空白单元格的数量 [英] Count number of blank cells in row between last cell and next non-blank cell

问题描述

是否有可能(最好使用公式)计算行中空白单元格的数量，其中计数从给定列开始，然后向后(例如从右到左)计数空白单元格的数量直到非空白找到单元格了吗?在下面的示例中，计数从 H 列开始并向左进行.使用 COUNTA 或 COUNTIF 似乎是合理的工具，但我不确定一旦找到非空白单元格如何终止计数.

Is it possible (with a formula preferably) to count the number of blank cells in row, where the counting starts at a given column and counts going backward (e.g. right to left) the number of blank cells until a non-blank cell is found? In the example below, the counting begins at Column H and proceeds leftward. Using COUNTA or COUNTIF seem like reasonable tools to use, but I am unsure on how to terminate the counting once a non-blank cell is found.

推荐答案

如果表中的值都是文本，您可以使用以下内容:

You can use something like this if the values in your table are all text:

=COUNTBLANK(INDIRECT(CHAR(97+MATCH("zzzz",B2:H2))&ROW()&":H"&ROW()))

MATCH("zzzz",B2:H2) 返回最后一个非空白单元格所在的列号.

MATCH("zzzz",B2:H2) returns the column number in which the last non-blank cell is.

CHAR(97+ column number) 返回该列的字母.

将其附加到行号以提供参考，其中 COUNTBLANK 必须以 &ROW()

Append it to the row number to give the reference where the COUNTBLANK has to start with &ROW()

&":H"&ROW()) 给出最后一个单元格的引用，即 H 加上行号.

&":H"&ROW()) gives the reference of the last cell, which is H plus the row number.

INDIRECT 将连接的文本转换为 Excel 可以计算的范围.

INDIRECT turns the concatenated text into a range that Excel can evaluate.

这篇关于计算最后一个单元格和下一个非空白单元格之间的行中空白单元格的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！