☰

É necessário adicionar números de identificação de iteração ausentes a uma série em texto ou Excel. Como fazer isso sem codificação?

tag-icon • tag-icon microsoft-excel regex
É necessário adicionar números de identificação de iteração ausentes a uma série em texto ou Excel. Como fazer isso sem codificação?

Eu tenho um arquivo de texto, csv ou excel que se parece com

||--ID-----||--Name--||--Date of birth--||
    1            Jo          1/1/11
    32           Mo          2/2/12
    3382         Ro          3/3/10
    21,252       Do          4/4/09

O conjunto real contém 280.000 delas de um total de 1.000.000 de linhas. Preciso adicionar de alguma forma todos os números de ID ausentes (que são todos iteração sequencial +1) e apenas campos vazios para nome e data de nascimento. Para que eu obtenha algo parecido com:

||--ID-----||--Name--||--Date of birth--||
    1,           "Jo",     "1/1/11"
    2,           "",         ""
    3,           "",         ""
    4,           "",         ""

até 32 e novamente até que todos os números inteiros estejam lá. Existe uma maneira fácil de fazer isso? sem escrever um bloco de código em um loop? Qualquer coisa do Excel, algum aplicativo ou qualquer truque do editor de texto do Windows seria apreciado.

Editar: ignore as vírgulas, aspas, etc. Os números de identificação ausentes são a única coisa crítica.

Responder1

Uma abordagem seria:

  1. importe seu CSV para Excel como planilha 1.
  2. na Folha 2, crie sua lista completa de números de identificação com uma fórmula como a2: =a1+1 e copie para baixo.
  3. use fórmulas vlookup nas outras 2 colunas, referindo-se à sua tabela na folha 1. como b2: =VLOOKUP(A2,Sheet1!A2:C13,2,false)e c2: =VLOOKUP(A2,Sheet1!A2:C13,3,false), ou para corresponder exatamente à sua solicitação, vamos agrupar isso em uma instrução IFNA para retornar "" se não houver valor. b2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,2,FALSE),"")e c2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,3,FALSE),"")(essas fórmulas agora podem ser copiadas na coluna).
  4. Uma vez preenchido, salve a planilha 2 como CSV.

Nota: para que isso funcione corretamente, a tabela da planilha 1 precisará ser classificada na coluna 1. Pelas informações fornecidas, parece que é assim, mas se não estiver, classifique as informações na primeira coluna.

Com um milhão de linhas, isso ficará muito lento em muitos computadores, perdi isso antes de escrever as fórmulas. Certa vez, fiz algo semelhante com um grande conjunto de dados, desativando o relcalc automático para colocar todas as fórmulas no lugar e, em seguida, fiz um recálculo manual. que levou horas, mas foi concluído corretamente.

Responder2

Eu resolveria isso com o suplemento Power Query.

Eu construí um protótipo que você pode visualizar ou baixar - sua "Demonstração do Power Query - Adicionar números de ID ausentes a um series.xlsx" no meu One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

São necessárias algumas etapas para chegar lá e um pouco de codificação na linguagem Power Query (M) para chamar a função List.Numbers (ela não está exposta na UI do Power Query). Ainda assim, essa é apenas uma linha simples de código - o restante pode ser criado clicando no Power Query.

Basicamente minha técnica era usar List.Numbers para gerar uma tabela de números de ID, depois adicionei um Merge para obter as colunas dos dados de entrada (onde existe o número de ID).

A documentação para List.Numbers está aqui:

http://office.microsoft.com/en-au/excel-help/list-numbers-HA104111648.aspx?CTT=5&origin=HA104122363

Grande "gorjeta" para Matt Masson pela técnica de "Configurações".

http://www.mattmasson.com/2014/04/defining-configurable-settings-for-your-queries/

Nota O Power Query pode ler diretamente de um arquivo CSV, que eu usaria como fonte para a consulta "Dados de entrada". Provavelmente é mais fácil excluir essa consulta, criar uma nova a partir do arquivo CSV e nomeá-la como "Dados de entrada". Desmarque a opção Carregar na planilha para economizar recursos.

informação relacionada