Como posso reorganizar os dados quando um arquivo é aberto?

Question

Eu tenho uma pasta de trabalho com dados que gostaria de reordenar (aleatoriamente, se isso for importante) toda vez que a pasta de trabalho for aberta. (Por exemplo, reclassificando pela coluna B, gerada por uma chamada rand() , na pasta de trabalho mostrada abaixo, toda vez que o arquivo é aberto.)

Eunãosouroteirista,masnãopareceque

• Excel 2013, os dados podem ser reclassificados automaticamente?
• Classificação automática para a planilha do Excel

chega bem lá. (Se eu estou lendo corretamente, eles estão falando sobre atualizar as coisas quando uma atividade é tomada dentro da planilha: para o meu propósito, é importante que a nova classificação aconteça quando a planilha for aberta pela primeira vez.)

Eu presumo que há algo como um script de quatro linhas que eu poderia escrever para fazer isso, mas não tenho ideia de como.

Como posso reordenar uma pasta de trabalho no arquivo aberto?

_{[versão do Excel 14.0.7180.5002, parte do office 2010.]}

_{Caso alguém esteja curioso, o uso é: Estou enviando uma lista para minha comunidade de uma lista de candidatos para votar; Eu gostaria que a lista fosse apresentada aleatoriamente, de modo a não inclinar os votos para o topo da pilha. (Existem mais de cem candidatos e apenas um voto para o elenco.)}

    

Answer 1

No VBA, isso pode ser feito com uma linha de código em uma sub-rotina:

Private Sub Workbook_Open()

    Sheet1.Range("A:B").Sort Key1:=Sheet1.Range("B:B"), Order1:=xlAscending, Header:=xlYes

End Sub

(Substitua cada instância de "sheet1" pelo nome da sua planilha (não pela pasta de trabalho))

Explicação de Layman: O evento Workbook_Open acontece quando a pasta de trabalho do Excel é aberta e é executada uma vez. Lá dentro, a única linha de código é basicamente dizendo: Use o método de classificação nesta área de células com base na coluna B. Classifique os resultados ascendentes. Ignore a linha superior porque é um cabeçalho.

Observe que os valores na coluna B não aparecerão classificados. Isso ocorre porque o Excel está recalculando a função rand () duas vezes - uma vez quando a pasta de trabalho é aberta e uma vez quando os dados são classificados. Você poderia adicionar mais código para controlar quando os cálculos acontecem, mas no seu caso, isso não parece importar; o resultado final é que os nomes estão em ordem aleatória.

Passo-a-passo sobre como fazer isso funcionar:

1. Salve sua pasta de trabalho como uma pasta de trabalho habilitada para macro. Arquivo > Salvar como > Altere a lista suspensa "Salvar como tipo" para "Pasta de trabalho habilitada para macro do Excel (* .xlsm).

2. Ative a guia do desenvolvedor. Arquivo > Opções > Personalizar faixa de opções > Marque "Desenvolvedor" nas abas principais. Pressione OK.

3. guia Desenvolvedor > clique em "Visual Basic". Uma nova janela é aberta. Clique com o botão direito em "ThisWorkbook" e clique com o botão esquerdo em "View Code".

4. Cole a sub-rotina na área de código à direita. Altere o nome do objeto de planilha da planilha1 para o que deveria ser. Salve, feche, reabra e você verá aleatoriamente os nomes.

No que diz respeito ao material sob o capô e ao funcionamento interno do VBA, há muitos recursos na Internet. Infelizmente, ainda faço um pouco disso na minha vida profissional e tenho o "Mastering VBA for Microsoft Office 2010" da Sybex como uma boa referência de escritório.

    

Answer 2

Se você quiser evitar o uso de VBa, a solução mais simples é usar uma tabela dinâmica.

Selecione suas colunas, insira uma tabela dinâmica em uma nova planilha, organize as colunas em " Forma tabular " e use o filtro manual ( descendente ou ascendente , não importa) em um com o aleatório número, em seguida, vá para a guia Opções (da Tabela Dinâmica), selecione Opções e procure a sub-aba Dados, clique em " atualiza os dados na abertura ".

Você pode ocultar a primeira folha contendo os dados de origem, se desejar.

Voilà!

Toda vez que você abrir o arquivo, a tabela dinâmica será reordenada.

    

Answer 3

Ainda mais uma alternativa a considerar (livre de VBA).

Supondo que uma lista original esteja em $H$2:$H$7 , e a fórmula da célula em um intervalo $B$2:$B$7 é RAND() , Digite uma fórmula

=INDEX($H$2:$H$7,SUMPRODUCT(--((FLOOR($B$2:$B$7*100000000,1)*0.00000001+(ROW($B$2:$B$7)-ROW($B$2))*0.000000001)<FLOOR(B2*100000000,1)*0.00000001))+1,1)

em A2 e copie / cole a célula em A3:A7 e salve o arquivo.

A lista em A2:A7 será reclassificada sempre que abrir.

Masobservequeeletambémseráreclassificadosempreque$B$2:$B$7forrecalculado.

Vocêpodemoveralistaestáticadaexibição,(naoutrapágina,/ocultar/proteger,etc).

Comentário:apartecomafunçãoFLOOR()

(FLOOR($B$2:$B$7*100000000,1)*0.00000001+(ROW($B$2:$B$7)-ROW($B$2))*0.000000001)

foiadicionadoapenasnocasodegarantirexclusividadedaschavesaleatóriasanexandouma"cauda" única a cada valor aleatório.

Editar

É melhor inserir a fórmula em A2 como fórmula de matriz ( Ctrl + Deslocamento + Enter ). Surpreendentemente, isso não faz nenhuma diferença notável para Excel ou LibreOffice Calc , mas com o formulário de matriz Gnumeric somente funciona corretamente.

Editar 2

Isso é ainda mais simples fórmula de matriz ( Ctrl + Deslocamento + Enter ) para colocar em A2 :

=INDEX($H$2:$H$7,1+SUM(1*($B$2:$B$7&$H$2:$H$7<B2&H2)))

    

Answer 4

Então, o aleatório torna um pouco mais interessante. A coluna A neste exemplo (a coluna st 1 ) é = rand () e você está na planilha Breaks com 10000 linhas p>

Private Sub Workbook_Open() 

 ActiveWorkbook.Worksheets("Breaks").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("A1:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Breaks").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub