使用公式在Excel中循环并连接 [英] Loop and Concatenate in Excel using Formula

问题描述

我正在寻找一种在Excel中使用公式创建计算列的方法。

我想在我的Score列中计算出空列数在行在我的EmptyLabels中连接空列的名称。

有没有办法在没有VBA和Mac的Excel中执行此操作？

提前感谢

更新
尝试IF和ISBLANK的多个列不能是一个解决方案。它给了我错误：不能处理多层次的

我实际上有50列，我认为这是不能完成的。
我可以只用于少量的列，正确的解决方案标记在下面。

解决方案

得分是 行中的空列数，这样可以使

I am looking for a way to create a calculated column using formula in Excel.

I want to calculate in my "Score" column, the number of empty columns in the row. And in my "EmptyLabels" concatenate the name of the empty columns.

Is there a way to do that in Excel without VBA and Macros ?

Thanks in advance.

UPDATE Try the IF and the ISBLANK for multiple columns can't be a solution. It gives me the error : can't handle multiple level of imbrication"

I have actually 50 Columns, and I think It cannot be done. I t can works only for a little number of Columns and the right solution is Marked Below.

解决方案

The score is ' the number of empty columns in the row' so that would make the COUNTBLANK function formula in the first row,

=COUNTBLANK(Table1[@[Col 1]:[Col 7]])

The conditional concatenation can be handled by a series of nested IF and ISBLANK functions. Simple string concatenation is provided by the ampersand (e.g. &).

=TRIM(IF(ISBLANK([@[Col 1]]), Table1[[#Headers],[Col 1]]&" ", " ")&
      IF(ISBLANK([@[Col 2]]), Table1[[#Headers],[Col 2]]&" ", " ")&
      IF(ISBLANK([@[Col 3]]), Table1[[#Headers],[Col 3]]&" ", " ")&
      IF(ISBLANK([@[Col 4]]), Table1[[#Headers],[Col 4]]&" ", " ")&
      IF(ISBLANK([@[Col 5]]), Table1[[#Headers],[Col 5]]&" ", " ")&
      IF(ISBLANK([@[Col 6]]), Table1[[#Headers],[Col 6]]&" ", " ")&
      IF(ISBLANK([@[Col 7]]), Table1[[#Headers],[Col 7]]&" ", " "))

If you prefer the more formalized string stitching provided by the CONCATENATE function then,

=TRIM(CONCATENATE(IF(ISBLANK([@[Col 1]]), Table1[[#Headers],[Col 1]], ""), CHAR(32),
                  IF(ISBLANK([@[Col 2]]), Table1[[#Headers],[Col 2]], ""), CHAR(32),
                  IF(ISBLANK([@[Col 3]]), Table1[[#Headers],[Col 3]], ""), CHAR(32),
                  IF(ISBLANK([@[Col 4]]), Table1[[#Headers],[Col 4]], ""), CHAR(32),
                  IF(ISBLANK([@[Col 5]]), Table1[[#Headers],[Col 5]], ""), CHAR(32),
                  IF(ISBLANK([@[Col 6]]), Table1[[#Headers],[Col 6]], ""), CHAR(32),
                  IF(ISBLANK([@[Col 7]]), Table1[[#Headers],[Col 7]], "")))

        

这篇关于使用公式在Excel中循环并连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！