3 complessi problemi di estrazione Excel risolti e spiegati

Molte persone hanno difficoltà a estrarre informazioni da celle complesse in Microsoft Excel. I molti commenti e domande in risposta al mio articolo su Come estrarre un numero o un testo da Excel con questa funzione Come estrarre un numero o un testo da Excel con questa funzione Come estrarre un numero o un testo da Excel con questa funzione il testo in un foglio di calcolo Excel può presentare delle sfide. Ti mostreremo come modificare la formattazione delle celle e separare i numeri dal testo. Leggi di più lo dimostra. Apparentemente, non è sempre chiaro come isolare i dati desiderati da un foglio Excel.

Abbiamo scelto alcune delle domande di questo articolo per passare qui, in modo da poter vedere come funzionano le soluzioni. Imparare Excel in modo rapido 8 Suggerimenti su Come imparare Excel in modo rapido 8 Suggerimenti su Come imparare Excel in modo rapido Non è comodo con Excel come vorresti? Inizia con semplici suggerimenti per aggiungere formule e gestire i dati. Segui questa guida e sarai in grado di accelerare in pochissimo tempo. Leggi di più non è facile, ma l'utilizzo di problemi del mondo reale come questi aiuta molto.

1. Estrazione utilizzando un separatore

Reader Adrie ha posto la seguente domanda:

Vorrei estrarre il primo gruppo di numeri da un elenco, ad esempio (122,90,84,118,4,128,9)
Qualche idea su quale formula posso usare?

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

Il fatto che i numeri da estrarre siano di lunghezza diversa rende questo un po 'più complicato dell'utilizzo della funzione MID, ma combinando alcune funzioni diverse, possiamo sbarazzarci delle lettere a sinistra e “X” e i numeri sulla destra, lasciando solo i numeri desiderati in una nuova cella.

Ecco la formula che useremo per risolvere questo problema:

= VALUE (LEFT ((DESTRA (A1, (LEN (A1) -4))), TROVA ( "X", A1) -5))

Iniziamo a metà e lavoriamo per vedere come funziona. Innanzitutto, inizieremo con la funzione Trova. In questo caso, stiamo usando FIND (“X”,A1). Questa funzione controlla il testo nella cella A1 per la lettera X. Quando trova una X, restituisce la posizione in cui si trova. Per la prima voce, COIL112X2.5, ad esempio, restituisce 8. Per la seconda voce, restituisce 7.
Successivamente, diamo un'occhiata alla funzione LEN. Questo semplicemente restituisce la lunghezza della stringa nella cella meno 4. Combinandola con la funzione RIGHT, otteniamo la stringa dalla cella meno i primi quattro caratteri, che rimuove “BOBINA” dall'inizio di ogni cellula. La formula per questa parte si presenta così: RIGHT (A1, (LEN (A1) -4)).

Il livello successivo è la funzione SINISTRA. Ora che abbiamo determinato la posizione della X con la funzione FIND e ci siamo sbarazzati di “BOBINA” con la funzione DESTRA, la funzione SINISTRA restituisce ciò che è rimasto. Let's rompere questo un po 'più lontano. Ecco cosa succede quando semplifichiamo questi due argomenti:

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

La funzione SINISTRA restituisce i tre caratteri più a sinistra della stringa (il “-5” alla fine dell'argomento si assicura che il giusto numero di caratteri venga eliminato tenendo conto dei primi quattro caratteri nella stringa).

Infine, la funzione VALUE garantisce che il numero restituito sia formattato come un numero, anziché come testo. Questo esempio non è divertente come costruire un gioco di Tetris in Excel 7 Divertimento e cose strane che è possibile creare con Microsoft Excel 7 Divertimento e cose strane che è possibile creare con Microsoft Excel Immaginare Excel è stato divertente! Excel offre molto spazio per progetti che vanno oltre la sua destinazione d'uso. L'unico limite è la tua immaginazione. Ecco gli esempi più creativi di come le persone utilizzano Excel. Leggi altro, ma è un ottimo esempio di come combinare alcune funzioni per risolvere un problema.

2. Estrarre numeri da stringhe miste

Una domanda simile è stata posta dal lettore Yadhu Nandan:

Voglio sapere come separare il numerico e gli alfabeti.

L'esempio è - 4552dfsdg6652sdfsdfd5654

Voglio 4552 6652 5654 in diverse celle

Un altro lettore, Tim K SW, ha fornito la soluzione perfetta a questo particolare problema:

Supponendo che 4552dfsdg6652sdfsdfd5654 sia in A1 e desideri che questi numeri vengano estratti in 3 celle diverse. 4452 nella cella B1 e 6652 in C1 e 5654 in D1 le useresti.

B1:
= MID (A1,1,4)

C1:
= MID (A1,10,4)

D1:
= MID (A1,21,4)

Le formule sono abbastanza semplici, ma se non hai familiarità con la funzione MID, potresti non capire come funziona. MID accetta tre argomenti: una cella, il numero di caratteri in cui inizia il risultato e il numero di caratteri da estrarre. MID (A1,10,4), ad esempio, dice a Excel di prendere quattro caratteri a partire dal decimo carattere della stringa.

L'utilizzo delle tre diverse funzioni MID in tre celle estrae i numeri da questa lunga stringa di numeri e lettere. Ovviamente, questo metodo funziona solo se hai sempre lo stesso numero di caratteri - sia lettere che numeri - in ogni cella. Se il numero di ciascuno varia, avrai bisogno di una formula molto più complicata.

3. Ottenere un numero da una stringa mista con spazi

Una delle richieste più difficili pubblicate sull'articolo era questa, dal lettore Daryl:

Ciao, voglio solo chiedere come separare una voce molto non formattata come:

Rp. 487.500 (sconto netto del 50%)
Rp 256.500 Nett 40% OFF
Rp99.000
Rp 51.000 / orang Nett (50% di sconto)

Ho passato un po 'di tempo a lavorare su questo, e non sono riuscito a trovare una soluzione da solo, quindi ho partecipato a Reddit. User UnretiredGymnast ha fornito questa formula che è lunga e molto complessa:

= MATR.SOMMA.PRODOTTO (MID (0 & LEFT (A1, IFERROR (SEARCH ( "%", A1) -4, LEN (A1))), GRANDE (INDEX (ISNUMBER (- MID (LEFT (A1, IFERROR (SEARCH ( "%" , A1) -4, LEN (A1))), ROW ($ 1: $ 99), 1)) * ROW ($ 1: $ 99), 0), ROW ($ 1: $ 99)) + 1,1) * 10 ^ FILA ($ 1: $ 99) / 10)

Come puoi vedere, la decodifica di questa formula richiede un po 'di tempo e richiede una buona conoscenza di molte funzioni di Excel. 16 Formule di Excel che ti aiuteranno a risolvere problemi di vita reale 16 Formule di Excel che ti aiuteranno a risolvere problemi di vita reale Lo strumento giusto è metà del lavoro. Excel può risolvere calcoli ed elaborare i dati più velocemente di quanto tu possa trovare la tua calcolatrice. Vi mostriamo le formule chiave di Excel e dimostriamo come usarle. Leggi di più . Per aiutare a capire esattamente cosa sta succedendo qui, dovremo esaminare alcune funzioni che potrebbero non essere familiari con. Il primo è IFERROR, che rileva un errore (solitamente rappresentato con un cancelletto, come # N / D o # DIV / 0) e lo sostituisce con qualcos'altro. In quanto sopra, vedrai IFERROR (SEARCH (“%”,A1) -4, LEN (A1)). Rompiamo questo.

IFERROR guarda il primo argomento, che è RICERCA (“%”,A1) -4. Quindi se “%” appare nella cella A1, la sua posizione viene restituita e quattro vengono sottratti da essa. Se “%” non compaiono nella stringa, Excel creerà un errore e la lunghezza di A1 verrà invece restituita.

Le altre funzioni che potresti non conoscere sono un po 'più semplici; SUMPRODUCT, ad esempio, moltiplica e quindi aggiunge elementi di array. LARGE restituisce il numero più grande in un intervallo. ROW, combinato con un segno di dollaro, restituisce un riferimento assoluto a una riga.

Vedere come tutte queste funzioni funzionano insieme non è facile, ma se inizi nel mezzo della formula e lavori verso l'esterno, inizierai a vedere cosa sta facendo. Ci vorrà un po ', ma se sei interessato a vedere esattamente come funziona, ti consiglio di inserirlo in un foglio Excel e di giocarci. Questo è il modo migliore per avere un'idea di cosa stai lavorando.

(Inoltre, il modo migliore per evitare un problema come questo è quello di importare i dati in modo più accurato Come importare i dati in Excel I fogli di calcolo in modo semplice e intuitivo Come importare i dati in Excel I fogli di calcolo in modo semplice e intuitivo Avete mai avuto problemi con importare o esportare dati in un foglio di calcolo Questo tutorial ti aiuterà a padroneggiare l'arte dello spostamento dei dati tra Microsoft Excel, CSV, HTML e altri formati di file.Per saperne di più - non è sempre un'opzione, ma dovrebbe essere la tua prima scelta quando è.)

Un passo alla volta

Come puoi vedere, il modo migliore per risolvere qualsiasi problema di Excel è un passo alla volta: inizia con quello che sai, vedi cosa ti porta e vai da lì. A volte ti ritroverai con una soluzione elegante, ea volte otterrai qualcosa di veramente lungo, disordinato e complesso. Ma finché funziona, ci sei riuscito!

E non dimenticare di chiedere aiuto Hai bisogno di imparare Excel? 10 esperti ti insegneranno gratis! Hai bisogno di imparare Excel? 10 esperti ti insegneranno gratis! Imparare a utilizzare le funzionalità più avanzate di Excel può essere difficile. Per renderlo un po 'più semplice, abbiamo rintracciato i migliori guru di Excel che possono aiutarti a padroneggiare Microsoft Excel. Leggi di più . Ci sono alcuni utenti di Excel estremamente talentuosi là fuori, e il loro aiuto può essere inestimabile per risolvere un problema difficile.

Hai qualche suggerimento su come risolvere i difficili problemi di estrazione? Conosci altre soluzioni ai problemi che abbiamo affrontato sopra? Condividili e ogni pensiero che hai nei commenti qui sotto!

Scopri di più su: Microsoft Excel, foglio di calcolo.