个人中心
gpt4 book ai didi

Unique values and frequencies across multiple sheets(跨多个工作表的唯一值和频率)

转载 作者：bug小助手 更新时间：2023-10-28 22:26:14 29 4
gpt4 key购买 nike



I am using Office 365 / Microsoft Forms to collect information into an Excel workbook. The number for sheets (and forms) can increase over time. Hence I have bookmarked these sheets by adding a sheet "Start" to the left of these sheets and sheet "Stop" to the right.

enter image description here

我正在使用Office 365/Microsoft表单将信息收集到Excel工作簿中。随着时间的推移，工作表(和表单)的数量可能会增加。因此，我在这些页面的左侧添加了一个“开始”页面，在右侧添加了一个“停止”页面，从而为这些页面添加了书签。


The relevant piece of information collected on the sheets is the email address of the respondents, which arrives in column A (all sheets have the same structure).

enter image description here

在表格上收集的相关信息是受访者的电子邮件地址，到达A栏(所有表格都具有相同的结构)。


What I would like to do is to have a "Summary" sheet after the "Stop" sheet, in which I have a dynamic list of all the unique emails across all sheets between "Start" and "Stop" and their frequencies in columns A and B respectively.

我想做的是在“停止”表之后有一个“摘要”表，在其中我有一个动态列表，其中包括所有在“开始”和“停止”之间的所有表中的所有独特的电子邮件，以及它们分别在A列和B列中的频率。


I reckon that once I have a list of emails, I could calculate the frequency of each via a COUNTIF across sheets - though that is less than ideal as the list would grow over time, so this solution would require copying over the formula to new rows ever so often. But what I am really struggling with is how to get the list of all unique values from column A of all the sheets between "Start" and "Stop"?

我估计，一旦我有了一个电子邮件列表，我就可以通过一个跨表的COUNTIF来计算每一封电子邮件的频率--尽管这并不理想，因为列表会随着时间的推移而增加，所以这个解决方案需要经常将公式复制到新行中。但我真正苦苦挣扎的是如何从“Start”和“Stop”之间的所有表的A列中获取所有唯一值的列表？


更多回答

There is a trick here for getting a list of tabs but it entails macro-enabling your sheet journalofaccountancy.com/issues/2018/nov/….

这里有一个获得选项卡列表的诀窍，但它需要宏观启用您的工作表JouralofacCountancy.com/Issues/2018/nov/…。

Thanks, @TomSharpe, I reckon it is possible to refer to the whole range of sheets, e.g. Start!A1:Stop!A1 without knowing the sheet names. My trouble is really how to grab unique values across those sheets ...

谢谢，@TomSharpe，我想可以参考整个系列的床单，例如开始！A1：停！A1不知道表名。我的问题是如何在这些表格中找到唯一的值...

优秀答案推荐

Retrieve Values Across Worksheets



  • This is a basic formula. If there are any issues, let us know.


=LET(data,Start:Stop!A1:A1000,
    d,TOCOL(data,1),
    u,UNIQUE(d),
HSTACK(u,BYROW(u,LAMBDA(ur,ROWS(FILTER(d,d=ur))))))

enter image description here



Edit after comment from @P.b.

Only certain functions can accept 3D References.
If you have Office 365 and if the total number of non-blank cells being referenced is less than 2^20+1, then you can use this formula:

在@P.B.发表评论后进行编辑。只有某些函数可以接受3D参照。如果您有Office 365，并且引用的非空单元格总数小于2^20+1，则可以使用此公式：


=LET(
    cols, Start:Stop!A:A,
    col, TOCOL(cols, 1),
    UNIQUE(col)
)


  • cols: all of the sheet ColA data as a multicolumn array

  • col: cols converted to a single column with blank cells removed

  • UNIQUE



=LET(v,TOCOL(Start:Stop!A:A,1),
     u,UNIQUE(v),
HSTACK(u,MMULT(N(TOROW(v)=u),SEQUENCE(ROWS(v),,,0))))

Basically the same as VBasic2008's answer, however the sum uses MMULT instead of BYROW LAMBDA.

与VBasic2008的S答案基本相同，不过总和使用MMULT而不是BYROW Lambda。


enter image description here


更多回答

I'm surprised. TOCOL is not listed as being supported for 3D references. ALSO your formula seems to handle full column references with number of cells exceeding 2^20

我吃了一惊。未将TOCOL列为3D参照支持。此外，您的公式似乎处理了单元格数量超过2^20的完整列引用

@RonRosenfeld Maybe it's still a Beta Channel thing at the moment.

@RonRosenfeld也许目前这仍然是Beta Channel的事情。

I'm not a beta channel subscriber.

我不是Beta频道的订阅者。

@VBasic2008 Worked like a charm. Thank you!

@VBasic2008就像一个护身符。谢谢!

Very educational. I figured it out by trial and error. Didn't know the details behind it.

很有教育意义。我是通过反复试验才弄明白的。不知道背后的细节。

Tocol(cols,1) would already filter out blanks. (Untested)

TOCOL(COLS，1)已经过滤掉了空格。(未经测试)

@P.b Thanks. I missed that argument.

@P.B谢谢我错过了这场争论。

TOCOL without the 1-parameter would have converted all blanks to zeros and FILTER just came too late. FILTER will fail if there is an error value. UNIQUE behaves the same as TOCOL except that it removes duplicates, even error duplicates. The issue with TOCOL and UNIQUE is that they do not get rid off cells containing nothing e.g. ="" or ' or these being copied as values etc. That is where FILTER steps in: ... TOCOL(cols,3),f,FILTER(col,col<>""),UNIQUE(f)) or ... TOCOL(cols,2),f,FILTER(col,col<>""),UNIQUE(f)) or even ... TOCOL(cols,3),u,UNIQUE(col),FILTER(u,u<>"")).

没有1参数的TOCOL会将所有空格转换为零，而FILTER来得太晚了。如果存在错误值，则FILTER将失败。UNIQUE的行为与TOCOL相同，除了它删除重复项，甚至错误重复项。TOCOL和UNIQUE的问题是，它们不会删除不包含任何内容的单元格，例如="”或“，或者这些被复制为值等。TOCOL（col，3），f，FILTER（col，col<>””），UNIQUE（f））或. TOCOL（col，2），f，FILTER（col，col<>””），UNIQUE（f））或甚至. TOCOL（col，3），u，UNIQUE（col），FILTER（u，u<>"”））。

@VBasic2008 Yes, I realized that and edited my answer after P.b. posted a comment.

@VBasic2008是的，我意识到了这一点，并在P.B.之后编辑了我的答案。发布了一条评论。

29 4 0
文章推荐： c++ - 如何在 C++ 中查找字符串中出现的字符串？
文章推荐： android - 奇怪的 "Receiver not registered"异常
文章推荐： android - 是什么让某些 android 类 "must -keep"？
文章推荐： c++ - 如何在 MS VS2010 的 64 位 Windows 7 上安装 GLUT？
bug小助手
个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章
滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com