Электронные таблицы: Как мне выполнять функции над множеством / массивом, описанным рядом пар (количество, значение)?

Question

У меня есть Excel 2000, Google Docs и последний доступный калькулятор для офиса. Я в поиске решения в любом из них, в идеале.

У меня есть мультимножество данных, описываемых строками пар (количество, значение). например

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, 3, 3, 4, 4, 5, 6, 6, 6, 6 , 6, 6, 6, 6}

Я хотел бы выполнить некоторую статистику по окончательному набору, например, AVERAGE() или MODE() в Excel. Как мне это сделать? Как «развернуть» пары (число, значение) в набор / массив, с которым может работать программа работы с электронными таблицами?

В настоящее время я могу делать только статистику по значениям в таблице, что, очевидно, не правильно.

Ниже приведена тривиальная реализация 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

Answer 1

Чтобы создать мультимножество в 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)

Answer 2

Немного запутанный способ использования 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