3 Complex excel problemas de extração resolvido e explicado

Muitas pessoas lutam com a extração de informações a partir de células complexas em Microsoft Excel. Os muitos comentários e perguntas em resposta ao meu artigo sobre Como extrair um número ou texto de Excel com esta função

o prova. Aparentemente, nem sempre está claro como isolar os dados desejados a partir de uma folha de Excel.Como extrair um número ou texto de Excel com esta funçãoComo extrair um número ou texto de Excel com esta funçãoMistura números e texto em uma planilha do Excel pode apresentar desafios. Nós vamos mostrar-lhe como alterar a formatação de suas células e números separados de texto.consulte Mais informação

Escolhemos algumas das perguntas de que o artigo de andar por aqui, assim você pode ver como o soluções de trabalho. Aprendizagem Excel rapidamente Não é fácil, mas usando problemas do mundo real como estes ajuda muito.

1. A extracção utilizando um separador

Leitor Adrie convidados a seguinte pergunta:

Gostaria de extrair o primeiro conjunto de números de uma lista, i.e. (122,90,84,118.4,128.9)
Algumas ideias sobre o que a fórmula que eu posso usar?

COIL112X2.5
COIL90X2.5
COIL84X2.0
COIL118.4X1.8
COIL128.9X2.0

O fato de que os números a serem extraídos são diferentes comprimentos torna este um pouco mais complicado do que apenas usando a função MID, mas pela combinação de algumas funções diferentes, podemos nos livrar das cartas no, bem como o “X” à esquerda e os números à direita, deixando apenas os números desejados em uma nova célula.

Aqui está a fórmula que vamos usar para resolver este problema:

= VALOR (ESQUERDA ((DIREITA (A1, (LEN (A1) -4))), FIND ("X", A1) -5))

Vamos começar no meio e trabalhar o nosso caminho para fora para ver como ele funciona. Primeiro, vamos começar com a função FIND. Neste caso, estamos usando FIND ( “X”, A1). Esta função olha através do texto na célula A1 para a letra X. Quando ele encontra um X, ele retorna a posição que está. Pela primeira entrada, COIL112X2.5, por exemplo, ele retorna 8. Para a segunda entrada, ele retorna 7.
Em seguida, vamos olhar para a função LEN. Isso simplesmente retorna o comprimento da corda no 4. menos célula Combinando isso com a função DIREITA, temos a string a partir da célula menos os quatro primeiros caracteres, que remove “COIL” desde o início de cada célula. A fórmula para esta parte tem a seguinte aparência: DIREITA (A1, (LEN (A1) -4)).

O próximo nível para fora é a função ESQUERDA. Agora que já determinou a posição do X com a função de encontrar e se livrado de “COIL” com a função DIREITA, a função ESQUERDA retorna o que sobrou. Vamos decompô-lo um pouco mais. Eis o que acontece quando nós simplificar estes dois argumentos:

= ESQUERDA ("112X2.5", (8-5))

A função ESQUERDA retorna as três mais à esquerda caracteres da cadeia (o “-5” no final do argumento assegura que o número correcto de caracteres são eliminados por representando os quatro primeiros caracteres na cadeia).

Video: Convert MS Excel Formula to VBA code

Finalmente, a função valor assegura que o número retornado é formatado como um número, em vez de como texto. Este exemplo não é tão divertido como a construção de uma jogo de Tetris trabalhar no Excel, mas serve como um grande exemplo de como combinar algumas funções para resolver um problema.7 Fun & Coisas estranhas você pode criar com o Microsoft Excel7 Fun & Coisas estranhas você pode criar com o Microsoft ExcelImagine o Excel foi divertido! Excel oferece muita margem para projetos que vão além de seu uso pretendido. O único limite é a sua imaginação. Aqui são os exemplos mais criativas de como as pessoas estão usando o Excel.consulte Mais informação

2. Puxando números fora de Cordas mistos

Uma pergunta semelhante foi perguntado pelo leitor Yadhu Nandan:

Eu quero saber como separar o numérica e alfabetos.

Exemplo é - 4552dfsdg6652sdfsdfd5654

Eu quero 4552 6652 5654 em células diferentes

Outro leitor, Tim K SW, desde a solução perfeita para este problema em particular:

Assumindo que 4552dfsdg6652sdfsdfd5654 está em A1 e você quer que estes números extraídos em 3 diferentes células. 4452 na célula B1 digamos e 6652 em C1 e 5654 em D1 você pode usar estes.

B1:
= MID (A1,1,4)

C1:
= MID (A1,10,4)

D1:
= MID (A1,21,4)

As fórmulas são bastante simples, mas se você não estiver familiarizado com a função MID, você pode não entender como ele funciona. MID recebe três argumentos: uma célula, o número de caracteres em que o resultado é iniciado eo número de caracteres que deve ser puxado. MID (A1,10,4), por exemplo, diz Excel para tomar quatro caracteres começando o personagem décimo na seqüência.

Video: Excel put a complex number

Usando as três funções MID diferentes em três células puxa os números fora deste longa seqüência de números e letras. Obviamente, este método só funciona se você sempre tem o mesmo número de caracteres - letras e números - em cada célula. Se o número de cada varia, você vai precisar de uma fórmula muito mais complicado.

3. Obter um número de uma cadeia mista com espaços

Um dos pedidos mais difíceis publicados no artigo era um presente, a partir de leitor Daryl:

Oi, eu só quero perguntar como separar entrada muito não formatado como:

Rp. 487,500 (OFF útil 50%)
R $ 256,500 Nett 40% OFF
Rp99.000
Rp 51.000 / utan útil (50% Off)

Eu passei algum tempo trabalhando em um presente, e não foi capaz de chegar a uma solução de mim mesmo, então eu levei para Reddit. Usuário UnretiredGymnast desde que esta fórmula que é longo e muito complexo:

= SUMPRODUCT (MID (0&LEFT (A1, IFERROR (SEARCH ("%", A1) -4, LEN (A1))), Grande (INDEX (ISNUMBER (- MID (LEFT (A1, IFERROR (SEARCH ("%", A1) -4, LEN (A1))), FILEIRA ($ 1: $ 99), 1)) * FILEIRA ($ 1: $ 99), 0), FILEIRA ($ 1: $ 99)) + 1,1) * 10 ^ FILEIRA ($ 1: $ 99) / 10)

Como você pode ver, decodificar esta fórmula leva muito tempo, e requer um conhecimento bastante sólido de um monte de funções do Excel. Para ajudar a descobrir exatamente o que está acontecendo aqui, vamos precisar de olhar para algumas funções que você pode não estar familiarizado. O primeiro é IFERROR, que pega um erro (normalmente representado com um sinal de libra, como # N / A ou # DIV / 0) e substitui-lo com outra coisa. No exemplo acima, você verá IFERROR (SEARCH ( “%”, A1) -4, LEN (A1)). Vamos decompô-lo.16 fórmulas do Excel que o ajudará a resolver problemas da vida real16 fórmulas do Excel que o ajudará a resolver problemas da vida realA ferramenta certa é metade do trabalho. Excel pode resolver cálculos e dados processo mais rápido do que você pode encontrar a sua calculadora. Nós mostramos-lhe as fórmulas chave do Excel e demonstrar como usá-los.consulte Mais informação

IFERROR olha para o primeiro argumento, que é SEARCH ( “%”, A1) -4. Então, se “%” aparece na célula A1, a sua localização é retornado e quatro é subtraído. E se "%" não aparecem na cadeia, Excel irá criar um erro, eo comprimento do A1 é retornado em vez.

As outras funções que você pode não estar familiarizado com são SUMPRODUCT um pouco simpler-, por exemplo, se multiplica e adiciona elementos de matrizes. retornos GRANDES o maior número em um intervalo. ROW, combinado com um cifrão, retorna uma referência absoluta a uma linha.

Vendo como todas essas funções trabalham juntos não é fácil, mas se você começar no meio da fórmula, e trabalhar para fora, você vai começar a ver o que está fazendo. Vai demorar um pouco, mas se você estiver interessado em ver exatamente como ele funciona, eu recomendo colocá-lo em uma folha de Excel e brincar com ele. Essa é a melhor maneira de obter uma idéia do que você está trabalhando com.

(Além disso, a melhor maneira de evitar um problema como este é importar seus dados mais ordenadamente - nem sempre é uma opção, mas deve ser a sua primeira escolha quando é).Como importar dados em suas planilhas do Excel da Neat & Jeito fácilComo importar dados em suas planilhas do Excel da Neat & Jeito fácilVocê já lutou com importação ou exportação de dados em uma planilha? Este tutorial irá ajudá-lo a dominar a arte de mover dados entre o Microsoft Excel, CSV, HTML e outros formatos de arquivo.consulte Mais informação

Um passo de cada vez

Como você pode ver, a melhor maneira de resolver qualquer problema Excel é um passo de cada vez: começar com o que você sabe, ver o que você recebe, e de lá ir. Às vezes você vai acabar com uma solução elegante, e às vezes você vai ter algo que é muito longo, confuso e complexo. Mas, enquanto ele funciona, você conseguiu!

E não se esqueça de peça por ajuda. Há alguns usuários extremamente talentosos Excel lá fora, e sua ajuda pode ser valiosa na resolução de um problema difícil.Precisa aprender Excel? 10 especialistas irá ensiná-lo de graça!Precisa aprender Excel? 10 especialistas irá ensiná-lo de graça!Aprender a usar recursos mais avançados do Excel pode ser difícil. Para torná-lo um pouco mais fácil, temos rastreou os melhores gurus do Excel que podem ajudá-lo a dominar o Microsoft Excel.consulte Mais informação

Você tem alguma sugestão sobre a resolução de problemas de extração difíceis? Você sabe de quaisquer outras soluções para os problemas que abordados acima? Compartilhá-los e quaisquer pensamentos que você tem nos comentários abaixo!