Praktischer Austausch von Excel-Kenntnissen: Wie extrahiere ich Zahlen?

Im vorherigen Artikel „Praktisches Teilen von Excel-Tipps: 7 Möglichkeiten zur Verbesserung der Tabellenanzeigeeffizienz“ haben wir 7 Tipps zur Verbesserung der Tabellenanzeigeeffizienz kennengelernt. Heute werden wir über das Extrahieren von Zahlen in Excel sprechen und einige gängige Extraktionssituationen zur Erläuterung vorstellen. Kommen Sie und lernen Sie!

Verwenden Sie Formeln, um Zahlen in Zellen zu extrahieren. Sie müssen ein bestimmtes Muster basierend auf der spezifischen Datenstruktur finden, um die entsprechende Formel zu entwerfen. Natürlich gibt es auch die sogenannte universelle Extraktionsformel, diese ist jedoch sehr kompliziert und erfordert viele Berechnungen, sodass sie am Ende des Artikels nur kurz erläutert wird.

Die erste Art von Situation: Die Zahl steht links

Beispiel 1: Die Zahl steht links und die Anzahl der Ziffern ist festgelegt.

In diesem Beispiel sind die Daten sehr regelmäßig und die Zahlen sind alle dreistellig auf der linken Seite. Wenn Sie die Studentennummer separat extrahieren möchten, müssen Sie nur die linke Funktion verwenden: =LEFT( A2,3)

Die Formel ist ebenfalls sehr einfach. Der erste Parameter ist die Zelle, aus der die Zahl extrahiert werden soll, und der zweite Parameter ist die Anzahl der zu extrahierenden Zellen (von links gezählt). Ändern Sie für diese Art von Daten in Beispiel 1 einfach den zweiten Parameter entsprechend der Länge der Zahl, wenn Sie ihn verwenden.

Beispiel 2: Die Zahlen sind auf der linken Seite nicht festgelegt, aber es gibt offensichtliche Trennzeichen.

Wenn die Zahlenlänge nicht auf drei Ziffern festgelegt ist, funktioniert die vorherige Methode nicht, wie unten gezeigt. Zu diesem Zeitpunkt müssen Sie die Regeln der Datenquelle finden und diese dann zum Betrieb verwenden. Die aktuellen Daten haben einheitlich ein „-“-Zeichen. Sie können die Länge der Zahl anhand der Position des „-“-Zeichens bestimmen und sie dann mit links extrahieren.

In diesem Fall muss eine andere Funktion zur Hilfe verwendet werden, nämlich die Suchfunktion.

Verwenden Sie die Formel =FIND("-",A2,1), um die Position von „-“ zu bestimmen. =FIND("-",A2,1)可以确定"-"的位置。

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

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

find Die Funktion verfügt über drei Parameter. Der erste Parameter gibt an, nach welchem ​​Inhalt gesucht werden soll, der zweite Parameter gibt an, wo danach gesucht werden soll, und der dritte Parameter ist das Wort, nach dem gesucht werden soll (von links).

=FIND("-",A2,1) bedeutet, „-“ in Zelle A2 zu finden, beginnend am Anfang (der ersten auf der linken Seite). Das Ergebnis der Formel ist eine Zahl, die die Position von „-“ in der Zelle darstellt (welches Zeichen). Zu diesem Zeitpunkt ist die Länge der Zahl, die wir extrahieren müssen, das Ergebnis von find minus 1, also ist die Methode : =LEFT(A2 ,FIND("-",A2,1)-1)

Diese Methode hat ein breites Anwendungsspektrum, sofern offensichtlichere Begrenzungssymbole gefunden werden können (Es können Symbole, chinesische Schriftzeichen, Buchstaben usw. sein.) Sie können diese Methode verwenden.

Beispiel 3:

Die Zahlen stehen auf der linken Seite, die Anzahl der Ziffern ist nicht festgelegt und es gibt keine offensichtlichen Trennzeichen.

Wenn die Daten so sind, können die vorherigen Methoden nicht verwendet werden. Die Länge der Zahlen ist unsicher und es gibt keine Trennzeichen. Die einzige Regel ist, dass die Daten nur Zahlen und chinesische Zeichen enthalten (Nr Buchstaben und andere Symbole).

In Excel gibt es zwei Arten von Zeichenlängen. Zeichen mit halber Breite (im englischen Modus eingegebene Zahlen, Buchstaben und Symbole) haben eine Länge von 1, während Zeichen mit voller Breite (chinesische Zeichen und chinesische Symbole usw.) eine Länge haben eine Länge von 2. Sie können dies anhand eines Beispiels verstehen: Die Funktion

len ist eine Funktion, die speziell die Länge des Zellinhalts berechnet. Sie unterscheidet nicht zwischen voller Breite und halber Breite. Ihre Länge entspricht unserem üblichen Verständnis. was der „Anzahl“ der Zeichen entspricht. Werfen wir einen Blick auf den Unterschied zwischen Vollbreite und Halbbreite:

🎜🎜 Die Lenb-Funktion funktioniert genauso wie die Len-Funktion. Der Unterschied besteht darin, dass bei der Berechnung der Länge von Halbbreite und Vollbreite unterschieden wird Der Inhalt lässt sich deutlich an den Ergebnissen ablesen. Die auf Basis der Vollbreite berechnete Länge des Textes beträgt ohne Unterscheidung das Doppelte der Vollbreite und der Halbbreite, während die Zahlen und Buchstaben in beiden Fällen genau gleich sind. 🎜🎜Bei Beispiel 3 müssen Sie len und lenb verwenden, um die Länge der Zahl zu bestimmen, und sie dann mit left extrahieren. 🎜

Verwenden Sie die Formel =LEN(A2)*2-LENB(A2), um die Länge der Zahl zu bestimmen, wie in der Abbildung unten gezeigt. =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(:)))

Warum Erhält len*2-lenb die Länge der Zahl?

Gemäß den Vergleichsergebnissen der beiden vorherigen Fälle beträgt die statistische Länge von lenb die Textlänge das Doppelte des statistischen Werts von len, und die Zahlenlänge stimmt mit dem statistischen Wert von len überein, sodass die Formel len*2-lenb berechnet werden kann Länge der Zahl. Nachdem man die Länge der Zahlen gezählt hat, ist die Lösung offensichtlich: =LEFT(A2,LEN(A2)*2-LENB(A2))

Diese Situation in Beispiel 3 ist ebenfalls recht typisch, denken Sie daran dass es ausreicht len ​​*2-lenb Wenn Sie sich wirklich nicht daran erinnern können, können Sie es separat berechnen, wenn Sie auf Probleme stoßen. Berechnen Sie zuerst die Länge der Zahl und kombinieren Sie sie dann.

Beispiel 4:

Die Zahlen stehen links und enthalten Zeichen halber Breite wie Buchstaben.

🎜🎜🎜Dies Diese Situation ist sehr kompliziert und die einzige Regel ist, dass die Zahl links steht. Mal sehen, wie wir es lösen können. 🎜🎜Sie können die Formel verwenden: =-LOOKUP(1,-LEFT(A2,ROW($1:$9))). 🎜🎜🎜🎜Über Die Erklärung dieser Formel wird wahrscheinlich 5.000 Wörter erfordern, Sie müssen sich also nur die Routine merken. Das Einzige, was geändert werden kann, ist die 9 am Ende. Wenn die längste Zahl 9 Ziffern überschreitet, müssen Sie die 9 ändern . Zum Ausfüllen einfach 99 verwenden. 🎜🎜Schließlich 🎜diese Formel funktioniert für jede Situation, in der sich die Zahl auf der linken Seite befindet 🎜. 🎜🎜🎜🎜Die zweite Art von Situation: Die Zahl befindet sich auf der rechten Seite. 🎜🎜🎜🎜Die Situation, in der die Zahl auf der rechten Seite steht, ist der Zahl auf der linken Seite sehr ähnlich. Das Folgende ist nur eine kurze Erklärung: 🎜🎜Wenn Die Anzahl der Ziffern ist festgelegt. Verwenden Sie die rechte Seite, um sie direkt zu extrahieren. png" alt="1Praktischer Austausch von Excel-Kenntnissen: Wie extrahiere ich Zahlen?"/>🎜🎜Die Anzahl der Ziffern ist nicht festgelegt. Wenn jedoch Trennzeichen vorhanden sind, verwenden Sie die Suchfunktion und die Mid-Funktion, um die Extraktion abzuschließen: =MID(A2,FIND(" -",A2,1)+1,9)🎜🎜🎜🎜(🎜Hinweis: 🎜Der letzte Parameter in der Formel Der Wert „9“ muss entsprechend der maximalen Anzahl von Ziffern geändert werden. Wenn beispielsweise die maximale Anzahl Wenn die Anzahl der extrahierten Ziffern 10 beträgt, muss sie als 10 oder eine Zahl größer als 10 geschrieben werden.)🎜🎜Sie können len weiterhin verwenden, wenn die Anzahl der Ziffern nicht festgelegt ist und kein Trennzeichen vorhanden ist. Kombination mit lenb: = RIGHT(A2,LEN(A2)*2-LENB(A2))🎜🎜🎜🎜Weiter damit, dass die Suche große Bewegungen ohne Regeln verwendet: =-LOOKUP(1,- RIGHT(A2,ROW($1:$9) ))🎜🎜🎜🎜🎜🎜Die dritte Art von Situation: Die Zahl liegt in der Mitte🎜🎜🎜🎜Die Zahl liegt in der Mitte. Das ist etwas ganz Besonderes. Diese Situation kommt in standardisierten Datenquellen im Allgemeinen nicht vor. Geben Sie an Beispiel: 🎜🎜🎜 🎜Wenn beispielsweise für Daten wie die im Bild die Länge vor oder nach der Zahl festgelegt ist, können Sie den Inhalt zunächst mit einer Funktion in die zuvor besprochene Situation ändern und ihn dann verarbeiten: 🎜🎜🎜🎜Die Formel ist also relativ einfach Ich werde es nicht erklären. Was den nächsten Schritt betrifft, wissen Sie es bereits aufgrund der vorherigen Studien, oder? 🎜🎜Die unten erwähnte Methode ist eine allgemeinere Methode. Manche Leute nennen sie die universelle Extraktionsformel: 🎜🎜=-LOOKUP(0,-MID(A1,MIN(FIND(ROW($1:$10) )-1 ,A1&1/17)),ROW($1:$9)))🎜🎜🎜🎜🎜Array-Formel müssen Sie zum Beenden die drei Tasten „🎜Strg+Umschalt+Eingabetaste🎜“ drücken. 🎜🎜🎜🎜Zusammenfassung🎜🎜🎜🎜 In Bezug auf die Verwendung von Formeln zum Extrahieren von Zahlen in Zellen wurden die meisten Situationen erläutert, aber natürlich nicht alle, beispielsweise Situationen, die Dezimalstellen enthalten noch spezieller. 🎜

Die letzte Matrixformel kann als allgemeinere Formel betrachtet werden. Es muss betont werden, dass die meisten Probleme aufgrund nicht standardmäßiger Datenquellen sehr problematisch werden. Machen Sie es sich daher zur Gewohnheit, nur ein Datenattribut in einer Zelle zu speichern, z. B. Name und Menge, Preis und andere Informationen werden separat gespeichert. Dies wird die statistische Effizienz erheblich verbessern.

Unter dem Gesichtspunkt der Verbesserung der Fähigkeit, Formeln zu verwenden, sind natürlich auch Probleme wie die Zahlenextraktion gute Themen für die Funktionsübung. Ich hoffe, Sie können die zuvor gelernten Funktionen kombinieren und sich weitere Formeln zum Extrahieren von Zahlen ausdenken.

Verwandte Lernempfehlungen: Excel-Tutorial

Das obige ist der detaillierte Inhalt vonPraktischer Austausch von Excel-Kenntnissen: Wie extrahiere ich Zahlen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!