如何将数据分组到列中?

I have numeric data under fifty samples that are mostly similar. I want to count identical columns and give statistics on the same. There are too many rows to select them (37,888). Data looks like:

我有50个样本下的数值数据，它们大部分是相似的。我想要数相同的列并给出相同的统计数据。有太多的行可以选择它们(37,888)。数据看起来像:

Sample 1 Sample 2 Sample 3     ........ Sample 50
4          4         0
4          4         0
4          4        ...
0          0
0          0
0          0
0          0
...        ...
upto thousands of rows for each sample.

There is a column for date/time as well, would be nice if I could include that in the grouping.

也有一个日期/时间的列，如果我能把它包含在分组中就好了。

In this snippet, there are many rows. Sample 1 and 2 are identical hence should be grouped together. Sample three would form another group and so on.

在这段代码中，有许多行。样本1和2是相同的，因此应该组合在一起。样本3会形成另一个组，以此类推。

1 个解决方案

#1

While I'm not sure what "There are too many rows to select them" means in this context (there is no limit on the number of rows or items that can be selected and included in a formula), this looks like a job for array formulas.

虽然我不确定在此上下文中“有太多行要选择它们”意味着什么(可以选择并包含在公式中的行或项的数量没有限制)，但这看起来像是数组公式的工作。

If you want to determine (for instance) whether columns C and D are equal, from rows 1 through 37888, you can use this formula:

如果您想确定(例如)列C和D是否相等，从第1行到37888行，您可以使用这个公式:

=AND(C1:C37888=D1:D37888)

To make Excel treat this as an array formula, you need to press CTRL-SHIFT-ENTER (Windows) or CMD-ENTER (Mac) after typing the formula. The "AND" function will return TRUE if and only if all corresponding entries are equal: C1=D1, C2=D2, C3=D3, ..., C37888=D37888. It returns FALSE if any corresponding entries disagree.

要使Excel将其视为一个数组公式，您需要在键入公式后按CTRL-SHIFT-ENTER (Windows)或CMD-ENTER (Mac)。当且仅当所有对应项均为:C1=D1, C2=D2, C3=D3，…,C37888 = D37888。如果对应项不一致，则返回FALSE。

Exactly what you do next will depend on the nature of the statistics that you want to compute for each group, but this formula will at least help you figure out which columns belong in the same group together.

接下来要做的具体操作将取决于您希望为每个组计算的统计数据的性质，但这个公式至少将帮助您确定哪些列属于同一个组。

#1