De nombreuses personnes ont du mal à extraire des informations de cellules complexes dans Microsoft Excel. Les nombreux commentaires et questions en réponse à mon article sur Comment extraire un nombre ou un texte d’Excel avec cette fonction le prouvent. Apparemment, il n’est pas toujours clair comment isoler les données souhaitées d’une feuille Excel.
Nous avons choisi quelques-unes des questions de cet article à parcourir ici, afin que vous puissiez voir comment les solutions fonctionnent. Apprendre rapidement Excel n’est pas facile, mais utiliser des problèmes du monde réel comme ceux-ci aide beaucoup.
1. Extraction à l’aide d’un séparateur
Reader Adrie a posé la question suivante:
Je voudrais extraire le premier ensemble de nombres d’une liste, c’est-à-dire (122,90,84,118,4,128,9)
Des idées sur la formule que je peux utiliser?BOBINE112X2.5
BOBINE90X2.5
COIL84X2.0
BOBINE118.4X1.8
BOBINE128.9X2.0
Le fait que les nombres à extraire soient de longueurs différentes rend cela un peu plus compliqué que d’utiliser simplement la fonction MID, mais en combinant quelques fonctions différentes, nous pouvons nous débarrasser des lettres à gauche ainsi que du «X» et les nombres sur la droite, ne laissant que les nombres désirés dans une nouvelle cellule.
Voici la formule que nous utiliserons pour résoudre ce problème:
=VALUE(LEFT((RIGHT(A1,(LEN(A1)-4))),FIND("X",A1)-5))
Commençons par le milieu et travaillons notre chemin pour voir comment cela fonctionne. Tout d’abord, nous allons commencer par la fonction FIND. Dans ce cas, nous utilisons FIND («X», A1). Cette fonction recherche dans le texte de la cellule A1 la lettre X. Lorsqu’elle trouve un X, elle renvoie la position dans laquelle il se trouve. Pour la première entrée, COIL112X2.5, par exemple, elle renvoie 8. Pour la deuxième entrée, elle renvoie 7.
Ensuite, regardons la fonction LEN. Cela renvoie simplement la longueur de la chaîne dans la cellule moins 4. En combinant cela avec la fonction DROITE, nous obtenons la chaîne de la cellule moins les quatre premiers caractères, ce qui supprime «COIL» du début de chaque cellule. La formule de cette partie ressemble à ceci: DROITE (A1, (LEN (A1) -4)).
Le niveau suivant est la fonction GAUCHE. Maintenant que nous avons déterminé la position du X avec la fonction FIND et que nous nous sommes débarrassés de «COIL» avec la fonction RIGHT, la fonction LEFT renvoie ce qui reste. Décomposons cela un peu plus loin. Voici ce qui se passe lorsque nous simplifions ces deux arguments:
=LEFT("112X2.5", (8-5))
La fonction GAUCHE renvoie les trois caractères les plus à gauche de la chaîne (le «-5» à la fin de l’argument garantit que le bon nombre de caractères est éliminé en tenant compte des quatre premiers caractères de la chaîne).
Enfin, la fonction VALUE garantit que le nombre renvoyé est formaté sous forme de nombre et non de texte. Cet exemple n’est pas aussi amusant que de créer un jeu fonctionnel de Tetris dans Excel, mais il constitue un excellent exemple de la façon de combiner quelques fonctions pour résoudre un problème.
2. Extraire des nombres de chaînes mixtes
Une question similaire a été posée par le lecteur Yadhu Nandan:
Je veux savoir comment séparer les chiffres et les alphabets.
Exemple: 4552dfsdg6652sdfsdfd5654
Je veux 4552 6652 5654 dans différentes cellules
Un autre lecteur, Tim K SW, a fourni la solution parfaite à ce problème particulier:
En supposant que 4552dfsdg6652sdfsdfd5654 est en A1 et que vous voulez que ces nombres soient extraits dans 3 cellules différentes. 4452 dans la cellule B1 et 6652 dans C1 et 5654 dans D1 vous les utiliseriez.
B1:
= MID (A1,1,4)C1:
= MID (A1,10,4)D1:
= MID (A1,21,4)
Les formules sont assez simples, mais si vous n’êtes pas familier avec la fonction MID, vous ne comprendrez peut-être pas comment cela fonctionne. MID prend trois arguments: une cellule, le numéro du caractère où le résultat commence et le nombre de caractères à extraire. MID (A1, 10, 4), par exemple, indique à Excel de prendre quatre caractères à partir du dixième caractère de la chaîne.
L’utilisation des trois fonctions MID différentes dans trois cellules extrait les nombres de cette longue chaîne de chiffres et de lettres. Évidemment, cette méthode ne fonctionne que si vous avez toujours le même nombre de caractères – lettres et chiffres – dans chaque cellule. Si le nombre de chacun varie, vous aurez besoin d’une formule beaucoup plus compliquée.
3. Obtenir un nombre à partir d’une chaîne mixte avec des espaces
L’une des demandes les plus difficiles postées sur l’article était celle-ci, du lecteur Daryl:
Salut, je veux juste demander comment séparer une entrée très non formatée comme:
Rp. 487.500 (50% de réduction net)
Rp 256.500 Nett 40% de réduction
Rp99.000
Rp 51.000 / orang Nett (50% de réduction)
J’ai passé du temps à travailler sur celui-ci et je n’ai pas été en mesure de trouver une solution moi-même, alors je me suis tourné vers Reddit. L’utilisateur UnretiredGymnast a fourni cette formule longue et très complexe:
=SUMPRODUCT(MID(0&LEFT(A1,IFERROR(SEARCH("%",A1)-4,LEN(A1))),LARGE(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^ROW($1:$99)/10)
Comme vous pouvez le voir, le décodage de cette formule prend un certain temps et nécessite une connaissance assez solide de nombreuses fonctions Excel. Pour vous aider à comprendre exactement ce qui se passe ici, nous devrons examiner quelques fonctions que vous ne connaissez peut-être pas. Le premier est IFERROR, qui détecte une erreur (généralement représentée par un signe dièse, comme # N / A ou # DIV / 0) et la remplace par autre chose. Dans ce qui précède, vous verrez IFERROR (SEARCH («%», A1) -4, LEN (A1)). Décomposons cela.
IFERROR examine le premier argument, qui est SEARCH («%», A1) -4. Ainsi, si «%» apparaît dans la cellule A1, son emplacement est renvoyé et quatre en est soustrait. Si « % » ne fait pas apparaissent dans la chaîne, Excel créera une erreur et la longueur de A1 est renvoyée à la place.
Les autres fonctions que vous ne connaissez peut-être pas sont un peu plus simples; SUMPRODUCT, par exemple, multiplie puis ajoute des éléments de tableaux. LARGE renvoie le plus grand nombre d’une plage. ROW, combiné avec un signe dollar, renvoie une référence absolue à une ligne.
Voir comment toutes ces fonctions fonctionnent ensemble n’est pas facile, mais si vous commencez au milieu de la formule et que vous travaillez vers l’extérieur, vous commencerez à voir ce qu’elle fait. Cela prendra un certain temps, mais si vous souhaitez voir exactement comment cela fonctionne, je vous recommande de le mettre dans une feuille Excel et de jouer avec. C’est la meilleure façon d’avoir une idée de ce avec quoi vous travaillez.
(De plus, le meilleur moyen d’éviter un problème comme celui-ci est d’importer vos données plus proprement – ce n’est pas toujours une option, mais cela devrait être votre premier choix quand c’est le cas.)
Un pas après l’autre
Comme vous pouvez le constater, la meilleure façon de résoudre tout problème Excel est une étape à la fois: commencez par ce que vous savez, voyez ce que cela vous procure et partez de là. Parfois, vous vous retrouverez avec une solution élégante, et parfois vous obtiendrez quelque chose de vraiment long, compliqué et compliqué. Mais tant que cela fonctionne, vous avez réussi!
Et n’oubliez pas de demander de l’aide. Il existe des utilisateurs d’Excel extrêmement talentueux et leur aide peut être inestimable pour résoudre un problème difficile.
Avez-vous des suggestions pour résoudre des problèmes d’extraction difficiles? Connaissez-vous d’autres solutions aux problèmes que nous avons abordés ci-dessus? Partagez-les et toutes vos pensées dans les commentaires ci-dessous!
