Planilhas: Como executo funções em um conjunto / matriz descrito por uma linha de pares (contagem, valor)?

Question

Tenho excel 2000, google docs e o mais recente open office calc disponível para mim. Eu estou atrás de uma solução em qualquer um desses, idealmente.

Eu tenho um multiset de dados descritos por linhas de pares (count, value). por exemplo,

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

Isto representa o multiconjunto {1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3,  3, 3, 4, 4, 5, 6, 6, 6, 6, 6, 6, 6, 6}

Gostaria de realizar algumas estatísticas no conjunto final, por exemplo MÉDIA do excel () ou MODE (). Como eu faço isso? Como faço para 'expandir' os pares (count, value) em um conjunto / array que o programa de planilha eletrônica pode trabalhar?

Atualmente, só posso fazer as estatísticas sobre os valores na planilha, que obviamente não estão corretos.

Uma implementação em Python trivial do que estou falando está abaixo.

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

Uma maneira ligeiramente complicada usando o Excel para expandir o multiconjunto usando fórmulas de matriz. A idéia geral é criar uma nova coluna (em uma nova planilha, se desejado) contendo a matriz expandida de valores nos quais as fórmulas padrão do Excel podem ser aplicadas.

1. Adicione duas colunas à sua definição multiconjunto, fornecendo um índice inicial e final do conjunto expandido (pode ser feito facilmente com a aritmética padrão)
2. Crie uma nova coluna que use ROW (célula) para determinar o índice no conjunto e recupere o elemento do conjunto correto.
3. Copie / cole a fórmula da matriz até o último elemento do conjunto anterior (a fórmula produzirá 0 quando o último elemento tiver sido passado)

Fórmula para a coluna expandida (lembre-se de entrar com Ctrl-Shift-Enter para torná-la uma fórmula de matriz)

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

Valores resultantes

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

    

Answer 2

Para criar seu multiconjunto em Column D , digite o seguinte em D2 :

=B2

Digite o seguinte em D3 e preencha até obter um #REF! erro:

=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)