Eu tenho um conjunto de dados baixado de um banco de dados da web que possui formatação variável. O objetivo é colocar todos os dados em linhas para que possam ser classificados e potencialmente transformados em um arquivo CSV-KMZ (KML). Depois de desfazer a mesclagem das células, os dados são formatados comoesse.
A solução de força bruta que encontrei envolve o uso='cell number'nas células à direita (Assim.) e, em seguida, excluindo todas as células vazias para consolidar todos os dados na mesma linha (exemplo aqui). A partir daqui, posso simplesmente selecionar e arrastar para baixo para copiar a matriz de células nas células inferiores. O problema surge ao se deparar com entradas de Parte Responsável com vários sites, como na primeira imagem, pois eles perdem o espaçamento que envolve muito copiar e colar para contorná-los.
Como esse conjunto de dados vai até a linha 10.000 ou mais e provavelmente haverá mais conjuntos como esse, espero que alguns dos grandes talentos aqui possam pensar em outra solução. Obrigado a todos que pensam neste problema!
Responder1
O exemplo de layout de dados ilustrado é a chave para o seu sucesso. Isso ébem caracterizadodados. Isso significa que ele segue um padrão definido em vez de estar em todo lugar. E isso significa que você pode escrever fórmulas simples e diretas para coletar cada parte dele.
Quando li a introdução antes de clicar, pensei que veria dados em todos os lugares. Por exemplo, {Cidade} pode estar na célula D3, E3 ou F3, talvez, e outras células semelhantes em todo o lugar, ou talvez combinada com {Estado}, e cada registro diferente: não combinado em alguns, seis seguidos com {City} na E3, depois alguns na F3, esse tipo de coisa.
Você não sofre NADA desse tipo de falha!
Sua única dificuldade está nas informações do site que consistem talvez em vários sites, em vez de apenas um por registro, período. Mas é uma complicação menor resolvida com duas coisas:
1) Um julgamento sobre quantos sites são possíveis por registro. Você já deve estar pensando nisso, pois planeja distribuir os resultados em uma única linha por RP, então use isso. 2) Uso da função IF() para testar um determinado dado que lhe dirá se você deve extrair informações do site ou iniciar um novo registro.
O segundo parecerá um pouco estranho, já que você decidirá isso em dois lugares, por assim dizer, e não apenas em um.
Uma vez decididas, as células na linha de saída podem ter fórmulas mais simples que testam o conteúdo da célula decisiva e avançam na linha.
O que você faz? Primeiro, presumo que os itens em negrito foram adicionados para maior clareza e não precisam ser extraídos. (Se o fizerem, faça da mesma maneira.) Também estou planejando uma saída simples que terá uma linha de dados, depois uma série de linhas "vazias" e depois outra linha de dados e assim por diante. A ideia no final será Copiar e Colar|Especiais|Valores, depois classificar e excluir a enorme pilha de linhas "vazias" no final. Pode-se ficar mais sofisticado, mas isso não está nos planos às 23h30...
Para extrair as informações principais, os dados "RP" (vou usar a ideia de começar em T2, e assumir que "Party ID é o" RP # ".), você precisa de um pouco de dados para corrigir e depois relacionar as posições observadas para as outras peças de volta a ele. O "RP#" parece ideal para isso.
= C1
Agora use OFFSET() para encontrar todo o restante dos dados RP. Mas lembre-se de que cada linha na saída deve verificar se deve coletar dados. Então você precisa agrupar isso em um IF() para ver se a linha obtém dados ou não:
= IF( C1 = "", "", C1)
Isso preencherá o T2 com algum RP#. Do T3 ao T7 aparecerá "". Vá para o U2. Insira a seguinte fórmula:
= IF( C1="", "", OFFSET( C1, 0, 2 ) )
Se C1 tivesse um RP#, então você encontrará o valor da célula 0 linhas abaixo e 2 colunas à direita. Caso contrário, você obterá "" em U2 (e continuará recebendo isso para todas as colunas que buscam os dados que não são do Site).
Continue. Basta variar os dois valores (as linhas são o primeiro, as colunas são o segundo) conforme necessário para localizar cada peça em relação a C1. Thant lida com todos os dados que não são do Site procurados. (Interessante lembrar, e ser usado a seguir, é que seus deslocamentos podem ser valores negativos, então você pode usar OFFSET() para olhar para a esquerda e para cima, bem como para a direita e para baixo.)
Para a célula AB2, insira um deslocamento de +4 linhas (paraabaixo) e -1 colunas (entãoesquerda). Portanto, o teste IF() simples e depois o deslocamento. Parece impossível, considerando os dados, que não haja no mínimo um Site para cada RP, mas se não houver, adicione ao teste IF():
= IF( OR( C1="", OFFSET( C1, 4, -1 ) = "" ), "", OFFSET( C1, 4, -1 ) )
Movendo para a direita coletando dados do site, retorne ao teste IF() simples, mas use AB2 em vez de C1. (Se C1 não era um RP#, você tem um AB2 "vazio", então um AB2 "vazio" significa que C1 também estava "vazio", então não há necessidade de testar todas as vezes.) Colete todos os dados desse Site como você fez com o RP dados.
Agora o cerne da questão: existe um segundo Site ou o início de um novo recorde? A célula, na mesma coluna, mas 7 linhas abaixo do RP# deste registro, é um novo RP# ou está em branco. "Em branco" pode ser testado como feito anteriormente. Digamos que AK2 seja onde os dados do segundo Site deveriam começar. Basta testar essa célula em branco ou não. Se estiver em branco, então há um segundo site e você localiza seus dados da mesma forma que acima. Usar:
= IF( OR( C1="", OFFSET( C1, 7, 0 ) = "" ), "", OFFSET( C1, 7, -1 ) )
que obtém o número do site, se existir, ou um "". O mesmo tipo de fórmula do primeiro site, apenas alterando o deslocamento da linha (os deslocamentos da coluna serão os mesmos). Se não houver nenhuma informação do site, modifique-a para testar também AB2 para "" para que, se estiver "vazio", os resultados "" ondularão para a direita a partir daqui.
Faça isso para quantos conjuntos de dados do site você decidir serem possíveis. Além disso, talvez mais um ou dois, hein?
Agora a “outra metade” da existência dos dados do Site: passamos para a linha 3 e célula T3. Copie todas as células da linha 2 até, digamos, a linha 15. Haverá uma série de linhas vazias, C2, c3, etc., que não terão RP # até que C8 seja alcançado. Então os dados aparecem novamente.
E como T8 não será "", a linha à direita será preenchida com dados. Yay!
Você pode estar pensando que as células de site à direita dos conjuntos de sites reais podem ter entradas bizarras ou confusas porque são lidas no próximo registro, ou dois, ou quatro, para obter seus dados. Mas a PRIMEIRA verificação para ver se há alguma razão para eles e resulta em "" para o (preocupado em ser espúrio) Site # e então isso ondula para a direita, resultando em entradas "" em vez de ler o que seriam os dados de algum outro registro . Sem problemas.
Copie e cole todas as linhas de dados que você possui ou quantas você puder trabalhar (lembrando que você não apenas fará com que sua máquina controle todas as fórmulas nessas 10.000 linhas, mas também copie e cole seus valores ao mesmo tempo). Digamos que não haja preocupações aqui, mas se houver, é algo que você precisa gerenciar em conjuntos de 1.000 linhas ou o que quer que funcione bem para você.
Depois que todo o cálculo estiver concluído, copie as células de saída e cole|Special|Values em... algum outro lugar, como uma segunda planilha. (Neste ponto, se a potência da máquina for uma preocupação, exclua todas as linhas de fórmulas, exceto as primeiras.)
Estar em uma planilha totalmente diferente permite que você faça qualquer coisa na saída sem afetar as células de extração de fórmulas ou os dados de origem.
Uma vez na segunda planilha, você tem apenas mais uma preocupação: será a próxima a classificar a saída. Às vezes é necessária a ordem dos dados original e NÃO é uma ordem de classificação que o Excel produzirá. Se for esse o caso, insira uma coluna à esquerda e preencha-a como quiser com uma lista de números sequenciais. Só tem que ser "constantes" e não fórmulas, então nada muda devido às fórmulas se recalcularem após serem classificadas ...
OK, é hora de se livrar de todas aquelas linhas "vazias" e ter apenas um conjunto restrito de linhas de dados. Classifique os dados (E a coluna de numeração também, se você precisar ou quiser). Faça isso de baixo para cima, como de costume, para que as linhas "vazias" apareçam na parte inferior. Encontre a primeira dessas linhas "vazias". Muitas maneiras de fazer isso... rolando, digamos... ou talvez primeiro vá para QUALQUER linha vazia e coloque "zzzzzzzzzzzzz" em sua primeira célula (mais à esquerda) para que ele classifique até o final dos dados, o que o tornará o primeira linha antes de todas as linhas "vazias". Assim que chegar à primeira célula da primeira linha de "vazios", pressione Ctrl-Shift-End para destacar TODO o lixo que você precisa retirar. Limpe o conteúdo com a tecla Delete.
Agora que suas linhas de dados estão todas juntas, não há linhas falsas não realmente vazias para atrapalhar o uso dos dados e você está pronto para prosseguir. Trabalhe nele, ou copie e cole em um local de descanso final, talvez em alguma outra planilha, e vá para a cidade!
Aliás, a construção é muito fácil e nada demorada. Uma vez feito isso, será feito para sempre, desde que os dados não se movam. Você saberá que os sites mudam, o software é atualizado e colunas totalmente novas mudam as coisas, mas há muitas maneiras fáceis de contornar esses problemas à medida que você atualiza lentamente seu trabalho inicial.
Uma vez construídos, os dados são lidos no instante em que são carregados, então basta copiar e colar, classificar, excluir (tudo apenas pressionando as teclas, um minuto, literalmente, não horas) e pronto. Menciono isso porque, tendo estado em seu lugar e resolvido as coisas com esforço bruto, assim como você mencionou no Questiton, acredito que você pode achar difícil de acreditar, mas HORAS de trabalho árduo e brutal através de tais erros que precisam ser desfeitos por a tonelada, os deslizamentos do mouse e assim por diante, agora durarão literalmente um minuto. Aproveite a vida que não te rouba mais!