☰

电子表格：如何对由一行（计数，值）对描述的集合/数组执行函数？

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet openoffice-calc google-spreadsheets
电子表格：如何对由一行（计数，值）对描述的集合/数组执行函数？

我有 excel 2000、google docs 和最新的 open office calc。理想情况下，我需要其中任何一个的解决方案。

我有一个由（计数，值）对的行描述的多集数据。例如

count,     value
     3,    1,
     7,    2,
     6,    3,
     2,    4,
     1,    5,
     8,    6,

这表示多重集 {1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 5, 6, 6, 6, 6, 6, 6, 6, 6}

我想对最终集合执行一些统计，例如 excel 的 AVERAGE() 或 MODE()。我该怎么做？如何将 (计数，值) 对“扩展”为电子表格程序可以处理的集合/数组？

目前我只能对表格中的数值进行统计，这显然是不正确的。

下面是我所谈论内容的一个简单的 Python 实现。

set_desc = [
        #count, value
        (3, 1),
        (7, 2),
        (6, 3),
        (2, 4),
        (1, 5),
        (8, 6),
     ]

multiset = []
# [3] * 5 in python would make the list [3,3,3,3,3]
for (count, value) in set_desc:
    print "Addding", [value] * count
    multiset.extend([value] * count)


sorted_multiset = sorted(multiset)
i0 = (len(sorted_multiset)-1)/2
i1 = (len(sorted_multiset))/2
print "final values in multiset are", sorted_multiset
print "median value(s) lies at index", i0, i1
print ""

print "mean average is", float(sum(sorted_multiset, 0))/len(sorted_multiset)
print "median value is", float(sorted_multiset[i0] + sorted_multiset[i1])/2

答案1

使用 Excel 通过数组公式扩展多集的稍微复杂的方法。一般的想法是创建一个新列（如果需要，可以在新工作表中），其中包含可以应用标准 Excel 公式的扩展值数组。

  1. 向多集定义添加两列，给出扩展集的开始和结束索引（可以使用标准算术轻松完成）
  2. 创建一个新列，使用 ROW（单元格）来确定集合中的索引并检索正确的集合元素。
  3. 复制/粘贴数组公式，直到最后一个集合元素（当传递完最后一个元素后，公式将得出 0）

扩展列的公式（记得使用 Ctrl-Shift-Enter 输入以使其成为数组公式）

=SUM((ROW(E1)>=$C$2:$C$7)*(ROW(E1)<=$D$2:$D$7)*$B$2:$B$7)

结果值

count value start end   expanded
3     1     1     3     1
7     2     4     10    1
6     3     11    16    1
2     4     17    18    2
1     5     19    19    2
8     6     20    27    2
                        2
                        2
                        2
                        2
                        3
                        3
                        3
                        3
                        3
                        3
                        4
                        4
                        5
                        6
                        6
                        6
                        6
                        6
                        6
                        6
                        6

答案2

要在 中创建多重集Column D，请在 中输入以下内容D2：

=B2

输入以下内容D3并填写直到出现“#REF!”错误：

=IF(COUNTIF(D$2:D2,D2)=INDEX($A$2:$A$7,MATCH(D2,$B$2:$B$7,0)),INDEX($B$2:$B$7,MATCH(D2,$B$2:$B$7,0)+1),D2)

相关内容