Partage de compétences pratiques sur Excel : Comment extraire des nombres ?

Dans l'article précédent « Partage de conseils pratiques sur Excel : 7 façons d'améliorer l'efficacité de l'affichage des tableaux », nous avons découvert 7 conseils pour améliorer l'efficacité de l'affichage des tableaux. Aujourd'hui, nous allons parler de l'extraction de nombres dans Excel et présenter plusieurs situations d'extraction courantes à expliquer. Venez apprendre !

Utilisez des formules pour extraire des nombres dans des cellules Vous devez trouver un certain modèle basé sur la structure de données spécifique pour concevoir la formule correspondante. Bien sûr, il existe également la formule d'extraction dite universelle, mais elle est très compliquée et nécessite beaucoup de calculs, elle n'est donc expliquée que brièvement à la fin de l'article.

Premier type de situation : le numéro est à gauche

Exemple 1 : Le numéro est à gauche, et le nombre de chiffres est fixe.

Dans cet exemple, les données sont très régulières, et les nombres sont tous dans les trois chiffres à gauche. Si vous souhaitez extraire le numéro d'étudiant séparément, il vous suffit d'utiliser la fonction de gauche : =LEFT(. A2,3)

La formule est également très simple. Le premier paramètre est la cellule à partir de laquelle le nombre doit être extrait, et le deuxième paramètre est le nombre de cellules à extraire (en comptant à partir de la gauche). Pour ce type de données dans l'exemple 1, il suffit de modifier le deuxième paramètre en fonction de la longueur du nombre lors de son utilisation.

Exemple 2 : Les chiffres sont à gauche. Le nombre de chiffres n'est pas fixe, mais il y a des séparateurs évidents.

Si la longueur du numéro n'est pas fixe à trois chiffres, la méthode précédente ne fonctionnera pas, comme indiqué ci-dessous. À ce stade, vous devez trouver les règles de la source de données, puis utiliser les règles pour fonctionner. Les données actuelles comportent uniformément un signe "-". Vous pouvez déterminer la longueur du nombre en fonction de la position du signe "-", puis utiliser la gauche pour l'extraire.

Dans ce cas, une autre fonction doit être utilisée pour aider, c'est la fonction rechercher.

Utilisez la formule =FIND("-",A2,1) pour déterminer la position de "-". =FIND("-",A2,1)可以确定"-"的位置。

find函数有三个参数，第一个参数是要找什么内容，第二个参数是从哪里找，第三个参数是从第几个字开始找（从左边算起）。

=FIND("-",A2,1) 意思是在A2单元格找“-”，从开始位置找（左边第一位）。公式的结果是数字，代表“-”在单元格的位置（第几个字符），此时我们需要提取的数字长度就是find的结果减1，因此方法就有了：=LEFT(A2,FIND("-",A2,1)-1)

trouver La fonction a trois paramètres. Le premier paramètre est le contenu à rechercher, le deuxième paramètre est l'endroit où le rechercher et le troisième paramètre est le mot à rechercher (en partant de la gauche).

=FIND("-",A2,1) signifie trouver "-" dans la cellule A2, en commençant par le début (le premier à gauche). Le résultat de la formule est un nombre, qui représente la position de "-" dans la cellule (quel caractère à ce moment, la longueur du nombre que nous devons extraire est le résultat de la recherche moins 1, donc la méthode est). : =LEFT(A2 ,FIND("-",A2,1)-1)

Cette méthode a un large éventail d'applications, à condition de pouvoir trouver des symboles de délimitation plus évidents (il peut s'agir de symboles, de caractères chinois, de lettres, etc.), vous pouvez utiliser cette méthode.

Exemple 3 :

Les chiffres sont à gauche, le nombre de chiffres n'est pas fixe et il n'y a pas de séparateurs évidents.

Si les données sont comme ça, alors les méthodes précédentes ne peuvent pas être utilisées. La longueur des nombres est incertaine et il n'y a pas de séparateurs. La seule règle est qu'il n'y a que des chiffres et des caractères chinois dans les données (non). lettres et autres symboles).

Dans Excel, il existe deux types de longueurs de caractères. Les caractères demi-chasse (chiffres, lettres et symboles saisis en mode anglais) ont une longueur de 1, tandis que les caractères pleine chasse (caractères chinois et symboles chinois, etc.) ont une longueur de 1. une longueur de 2. Vous pouvez comprendre cela à travers un exemple : la fonction

len est une fonction qui calcule spécifiquement la longueur du contenu d'une cellule. Elle ne fait pas de distinction entre la pleine largeur et la demi-largeur. Sa longueur est cohérente avec notre compréhension habituelle, ce qui équivaut au "nombre" de caractères. Jetons un coup d'œil à la différence entre pleine largeur et demi-largeur :

🎜🎜 La fonction lenb fonctionne de la même manière que la fonction len. La différence est que la demi-largeur et la pleine largeur sont distinguées lors du calcul de la longueur. le contenu. La différence évidente peut être constatée à partir des résultats. La longueur du texte calculée sur la base de la pleine largeur est le double de celle de la pleine largeur et de la demi-largeur sans distinction, tandis que les chiffres et les lettres sont exactement les mêmes dans les deux cas. 🎜🎜Par exemple 3, vous devez utiliser len et lenb pour déterminer la longueur du nombre, puis utiliser left pour l'extraire. 🎜

Utilisez la formule =LEN(A2)*2-LENB(A2) pour déterminer la longueur du nombre, comme indiqué dans la figure ci-dessous. =LEN(A2)*2-LENB(A2)可以确定数字的长度，如下图所示。

为什么len*2-lenb就会得到数字的长度呢？

根据前面两种情况下的对比结果，lenb统计长度，文字长度是len统计值的两倍，而数字长度与len统计值一致，所以公式len*2-lenb就可以计算出数字的长度了。

数字的长度统计出来以后，解决方法显而易见：=LEFT(A2,LEN(A2)*2-LENB(A2))

例三这种情况同样比较典型，只要记住是len*2-lenb就可以了，如果确实记不住，那么遇到问题的时候可以单独算一算，先把数字的长度算出来，再组合left就行了。

例四：数字在左边，并且含有字母等半角字符。

这种情况很复杂，唯一的规律就是数字在左边。来看看如何解决吧。

可以利用公式：=-LOOKUP(1,-LEFT(A2,ROW(:)))。

关于这个公式，要解释的话估计还得五千字，所以各位只需要记得套路即可，唯一有可能修改的就是最后面那个9，当最长的数字超过9位时，就要修改这个9，可以直接用99来完成。

最后，这个公式适用于任何数字在左边的情况。

第二类情况：数字在右边

数字在右边的情况与数字在左边非常类似，以下仅做简单说明：

数字位数固定的直接用right提取；

数字位数不固定但有分隔符号的使用find函数配合mid函数 完成提取：=MID(A2,FIND("-",A2,1)+1,9)

（注：公式中最后一个参数值“9”需要根据数字的最大位数修改，譬如提取的数字最大位数是10，则需要写成10或者大于10的数字。）

数字位数不固定同时没有分隔符的还是可以使用len和lenb的组合：=RIGHT(A2,LEN(A2)*2-LENB(A2))

什么规律都没有的继续让lookup发大招：=-LOOKUP(1,-RIGHT(A2,ROW(:)))

第三类情况：数字在中间

数字在中间这种非常特殊，比较规范的数据源中一般不会出现这样的情况，举一个例子吧：

例如图中这种数据，如果数字前面或者后面的长度固定，可以先用函数将内容变成前面讨论过的情况再去处理：

公式比较简单，不做解释了，至于后面一步怎么做，根据前面的学习你已经会了吧？

下面要说的这个方法就是一种比较通用的方法了，有人称其为万能提取公式：

=-LOOKUP(0,-MID(A1,MIN(FIND(ROW(:)-1,A1&1/17)),ROW(:)))

Pourquoi len*2-lenb obtiendra-t-il la longueur du nombre ?

Selon les résultats de comparaison des deux cas précédents, la longueur statistique lenb, la longueur du texte est deux fois la valeur statistique len et la longueur numérique est cohérente avec la valeur statistique len, donc la formule len*2-lenb peut calculer la longueur du numéro. Après avoir compté la longueur des nombres, la solution est évidente : =LEFT(A2,LEN(A2)*2-LENB(A2))

Cette situation dans l'exemple 3 est également assez typique, rappelez-vous simplement. que c'est len ​​*2-lenb est suffisant. Si vous ne vous en souvenez vraiment pas, vous pouvez le calculer séparément lorsque vous rencontrez des problèmes. Calculez d'abord la longueur du nombre, puis combinez à gauche.

Exemple 4 :

Les chiffres sont à gauche et contiennent des caractères en demi-chasse comme des lettres.

🎜🎜🎜Ceci Cette situation est très compliquée, et la seule règle est que le numéro soit à gauche. Voyons comment le résoudre. 🎜🎜Vous pouvez utiliser la formule : =-LOOKUP(1,-LEFT(A2,ROW($1:$9))). 🎜🎜🎜🎜À propos L'explication de cette formule prendra probablement 5 000 mots, il vous suffit donc de vous rappeler la routine. La seule chose qui peut être modifiée est le 9 à la fin. Lorsque le nombre le plus long dépasse 9 chiffres, vous devez modifier le 9. Vous. peut simplement utiliser 99 pour terminer. 🎜🎜Enfin, 🎜cette formule fonctionne pour toute situation où le chiffre est à gauche 🎜. 🎜🎜🎜🎜Le deuxième type de situation : le numéro est à droite 🎜🎜🎜🎜La situation où le numéro est à droite est très similaire au numéro à gauche. Ce qui suit est juste une brève explication : 🎜🎜Si. le nombre de chiffres est fixe, utilisez right pour l'extraire directement 🎜🎜🎜🎜Le nombre de chiffres n'est pas fixe Mais s'il y a des délimiteurs, utilisez la fonction find et la fonction mid pour terminer l'extraction : =MID(A2,FIND(" -",A2,1)+1,9)🎜🎜🎜🎜(🎜Remarque : 🎜Le dernier paramètre de la formule La valeur "9" doit être modifiée en fonction du nombre maximum de chiffres. Par exemple, si le nombre maximum des chiffres extraits est 10, il doit être écrit comme 10 ou un nombre supérieur à 10.)🎜🎜Vous pouvez toujours utiliser len si le nombre de chiffres n'est pas fixe et qu'il n'y a pas de séparateur avec lenb : =. DROITE(A2,LEN(A2)*2-LENB(A2))🎜🎜🎜🎜Continuez à laisser la recherche utiliser de grands mouvements sans aucune règle : =-LOOKUP(1,- RIGHT(A2,ROW($1:$9) ))🎜🎜🎜🎜🎜🎜Le troisième type de situation : le nombre est au milieu🎜🎜🎜🎜Le nombre est au milieu. C'est une situation très particulière. Cette situation ne se produit généralement pas dans des sources de données plus standardisées. , donnez un exemple : 🎜🎜🎜🎜Par exemple, pour des données comme celle de l'image, si la longueur avant ou après le numéro est fixe, vous pouvez d'abord utiliser une fonction pour modifier le contenu dans la situation discutée précédemment, puis le traiter : 🎜🎜🎜🎜La formule est relativement simple , donc je ne l'expliquerai pas. Quant à la prochaine étape, faites-le. Vous le savez déjà sur la base des études précédentes, n'est-ce pas ? 🎜🎜La méthode mentionnée ci-dessous est une méthode plus générale. Certaines personnes l'appellent la formule d'extraction universelle : 🎜🎜=-LOOKUP(0,-MID(A1,MIN(FIND(ROW($1:$10) )-1 ,A1&1/17)),ROW($1:$9)))🎜🎜🎜🎜🎜Formule matricielle, vous devez appuyer sur les trois touches "🎜Ctrl+shift+Entrée🎜" pour terminer. 🎜🎜🎜🎜Résumé🎜🎜🎜🎜 Concernant la façon d'utiliser des formules pour extraire des nombres dans des cellules, ce qui précède a expliqué la plupart des situations, mais bien sûr pas toutes, comme les situations qui contiennent des décimales. En raison du point décimal, c'est le cas. encore plus spécial. 🎜

La dernière formule matricielle peut être considérée comme une formule plus générale. Il convient de souligner que la plupart des problèmes deviennent très gênants en raison de sources de données non standard. Par conséquent, prenez la bonne habitude de stocker un seul attribut de données dans une cellule, tel que le nom et la quantité, le prix et d'autres informations sont stockées séparément. ce qui améliorera considérablement l’efficacité statistique.

Bien sûr, du point de vue de l'amélioration de la capacité à utiliser des formules, des problèmes tels que l'extraction de nombres sont également de bons sujets pour la pratique des fonctions. J'espère que vous pourrez combiner les fonctions que vous avez apprises auparavant et penser à davantage de formules pour extraire des nombres.

Recommandations d'apprentissage associées : Tutoriel Excel

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!