在excel中加入文本的预过滤表 [英] textjoin a prefiltered table in excel

问题描述

让我们说我们在excel中有一个表.有人使用ui(从一列中的箭头)向整个表格应用了过滤器.

Lets say that we have a table in excel. And someone applies a filter using ui (from the arrow in one column) to the whole table.

因此我想从此过滤后的表中将一列(过滤后的行)加入文本.

So from this filtered table i want to textjoin a column (the filtered rows).

让我们说我们有table1，我想文本联接列Language.

Lets say we have table1 and i want to text join column Language.

如果我使用功能

=TEXTJOIN(",";1;Table1[[#Data];[Language]])

如果将结果过滤掉，结果将合并该列的所有行，因此我认为小计在这里行不通.

the result joins all the rows of the column irrelevant if it is filtered, and subtotal i think can not work here.

我该怎么办，我可以联接已过滤表中的所有行，因此每次过滤器更改时，textjoin的结果都会改变?

What can i do so i can join all rows from a filtered table so everytime the filter changes the result of the textjoin will change?

因此，可以说这是我要textjoin的列，有人过滤掉了第一行和第三行.所以我希望textjoin忽略这些行

So lets say that this is the column that i want to textjoin and someone filters out the first and the third row. So i want the textjoin to ignore these rows

推荐答案

我认为这可以做到:

=TEXTJOIN(",";1;IF(SUBTOTAL(103;OFFSET(Table1[[#Data];[Language]];ROW(Table1[[#Data];[Language]])-MIN(ROW(Table1[[#Data];[Language]]));;1))=1;Table1[[#Data];[Language]];""))

如果某个内容被隐藏，则带有偏斜的小计将按角色筛选出一个角色，并返回适当的字段，以便textjoin起作用

where the subtotal with the offseet filters out role by role if something is hidden and returns the proper fields so textjoin works

这篇关于在excel中加入文本的预过滤表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！