Fragen und Antworten zu Office: Integrierte Excel-Funktionen, die komplexe Ausdrücke überflüssig machen

Viele Benutzer sehen in Excel eine Lösung mit nur einer Methode - komplexe Ausdrücke, Funktionen und sogar Arrays. Es kann schwierig sein, Ausdrücke zu schreiben, und es kann Stunden dauern, sie zu testen und Fehler zu beheben, wenn Sie mit Syntax und Erwartungen nicht vertraut sind. Oft können Sie dieselben Ergebnisse stattdessen mit einer einfach zu implementierenden Funktion erzielen. Diesen Monat habe ich zwei Benutzern gezeigt, wie komplexe Ausdrücke mithilfe integrierter Funktionen entfernt werden können.

Wenn Sie mitmachen möchten, laden Sie die XLS- oder XLSX-Dateien herunter.

Text in Spalten zum Parsen

Bruce muss eine Zeichenfolge analysieren, damit er die numerischen Werte in anderen Berechnungen verwenden kann. Leider kann er nicht kontrollieren, wie die Daten zu ihm gelangen - er bleibt bei einer langen Reihe von Werten (siehe Abbildung A), die die Dimension eines Containers darstellen. Bruce hat eine Reihe von Parsing-Ausdrücken mit LEFT (), RIGHT (), MID (), LEN () und FIND () ausprobiert und es ist ihm gelungen, den ersten Wert mit dem folgenden Ausdruck herauszuquetschen:

= LINKS (A5, FINDEN ("x", A5) -1)

Er kann den Funktionsmix nicht genau richtig machen, um die Werte der zweiten und dritten Dimension zu analysieren.

Abbildung A.

Excel importiert die drei Dimensionen in einer Zeichenfolge.

Bruce arbeitet viel härter als nötig. Die Excel-Funktion "Text in Spalten" analysiert die Werte schnell und einfach ohne Ausdrücke. Lassen Sie uns anhand von Bruce 'Blatt ein kurzes Beispiel durcharbeiten.

Bevor Sie beginnen, sollten Sie entscheiden, ob Sie die ursprüngliche Zeichenfolge beibehalten möchten. Wenn ja, kopieren Sie die Zeichenfolgenwerte und arbeiten Sie mit der Kopie, da Text To Columns über die ursprüngliche Zeichenfolge schreiben. Abbildung B zeigt das Ergebnis des Kopierens der Originalzeichenfolgen in A4: A14 auf ein neues Blatt. Dies ist der Datensatz, den wir tatsächlich analysieren werden.

Abbildung B.

Das Arbeiten mit einer Kopie ist eine gute Idee.

Aktivieren Sie bei vorhandener Kopie die Funktion "Text in Spalten" wie folgt:

  1. Wählen Sie A2: A12 (der entsprechende Bereich mit den Zeichenfolgen, die Sie analysieren möchten).
  2. Klicken Sie auf die Registerkarte Daten.
  3. Klicken Sie in der Gruppe Datentools auf Text in Spalten. In Excel 2003 befindet sich diese Funktion im Menü Daten.
  4. Behalten Sie im daraufhin angezeigten Dialogfeld die Standardoption "Begrenzt" bei und klicken Sie auf "Weiter", um fortzufahren (wir verwenden das x-Zeichen als Trennzeichen).
  5. Aktivieren Sie in der Liste Trennzeichen die Option Andere.
  6. Geben Sie ein x in das Steuerelement rechts ein ( Abbildung C ). Dadurch wird die folgende Vorschau aktualisiert, in der die x-Zeichen weggelassen werden und die in drei Spalten analysierten Werte angezeigt werden.
    Abbildung C.
  7. Weiter klicken.
  8. Im nächsten Bereich können Sie den Datentyp jeder Spalte angeben. In diesem Beispiel ist Excel standardmäßig Allgemein ( Abbildung D ). Behalten Sie die Standardeinstellung bei, indem Sie auf Fertig stellen klicken.
    Abbildung D.
  9. Wenn (wenn) Excel Sie auffordert, zu bestätigen, dass über die ursprünglichen Zeichenfolgen geschrieben wird, klicken Sie auf OK. Abbildung E zeigt die analysierten Ergebnisse.
    Abbildung E.

Da Excel den Datentyp auf Allgemein gesetzt hat, können Sie die Werte in Berechnungen verwenden. Es ist möglich, mit Ausdrücken dieselben Ergebnisse zu erzielen, diese Methode ist jedoch einfach und schnell zu implementieren.

Flash Fill für 2013 Benutzer

Wenn die Zeichenfolgenwerte in ihrer Struktur konsistent sind, können Sie die neue Flash-Füllfunktion von Excel 2013 ausprobieren. In diesem speziellen Fall konnte ich Flash Fill nicht dazu bringen, alle dreidimensionalen Werte genau zu analysieren. Die Funktion ist jedoch oft nützlich, daher möchte ich sie erwähnen.

Zunächst benötigen Sie einen Platz für die drei Wertespalten. Fügen Sie daher drei Spalten rechts von den ursprünglichen Zeichenfolgen ein (in Spalte A). Gehen Sie wie folgt vor, um die Werte mit Flash Fill schnell zu analysieren:

  1. Geben Sie rechts von den ursprünglichen Zeichenfolgen in B4: B6 die ersten drei Werte 22.1, 11.33 und 113 manuell ein, um das Muster festzulegen.
  2. Wählen Sie die nächste leere Zelle in der Spalte aus (B7).
  3. Klicken Sie auf die Registerkarte Daten.
  4. Klicken Sie in der Gruppe Datentools auf Flash-Füllung ( Abbildung F ). Excel versucht, die Liste gemäß dem in Schritt 1 festgelegten Muster zu erstellen.
    Abbildung F.
  5. Geben Sie zum Generieren der zweiten Liste (Analysieren der mittleren Komponenten) die ersten drei mittleren Werte manuell in C4: C6 ein (mit dieser Zeichenfolge konnte ich kein Muster für die zweite Komponente erstellen, und die Flash-Füllung ist fehlgeschlagen).
  6. Wählen Sie C7 und klicken Sie auf Flash Fill, um die zweite Liste der Dimensionen zu erstellen.
  7. Wiederholen Sie diesen Vorgang, um die dritte Liste der Dimensionen in Spalte D zu erstellen.

Leider bietet Flash Fill nicht immer eine perfekte Liste und wird dieser speziellen Aufgabe nicht gerecht. Es ist eine gute Funktion, um es zu versuchen, und deshalb habe ich es erwähnt. Sie sollten die Ergebnisse von Flash Fill sorgfältig auf Unstimmigkeiten überprüfen. Weitere Informationen zu dieser neuen Funktion finden Sie unter "Excel 2013 Flash Fill antizipiert Anforderungen im laufenden Betrieb".

Durchschnittliche eindeutige Werte

Prakash möchte eine Spalte mit Werten mitteln, aber nur die eindeutigen Werte. In diesem Fall ist die (fast) ausdruckslose Lösung einfach: Erstellen Sie mit "Duplikate entfernen" eine Liste eindeutiger Werte. (Diese Funktion ist in Excel 2003 nicht verfügbar. Verwenden Sie die unten erwähnte Option Erweiterter Filter.)

Um diese einfache integrierte Lösung zu veranschaulichen, verwenden wir die in Abbildung G gezeigte einfache Liste. Die Duplikate sind leicht zu erkennen. In der realen Welt müssen Sie möglicherweise Tausende von Zeilen berücksichtigen.

Abbildung G.

Diese einfache Liste enthält einige Duplikate.

Wenn Sie die ursprünglichen Werte nicht dauerhaft entfernen möchten (und wahrscheinlich auch nicht), kopieren Sie die Werte und erstellen Sie eine eindeutige Liste aus der Kopie (was ich tun werde). Sobald Sie eine Kopie erstellt haben, erstellen Sie eine Liste eindeutiger Werte wie folgt:

  1. Wählen Sie die Wertespalte aus. In diesem Fall ist das B2: B10 (nach dem Kopieren der ursprünglichen Liste von Spalte A in Spalte B).
  2. Klicken Sie auf die Registerkarte Daten.
  3. Klicken Sie in der Gruppe Datentools auf Duplikate entfernen.
  4. Ändern Sie im daraufhin angezeigten Dialogfeld die Standardoption, indem Sie die Option Mit der aktuellen Auswahl fortsetzen auswählen ( Abbildung H ). In diesem Fall würde die Standardoption beide Spalten A und B zusammen bewerten, wenn Duplikate gefunden werden. Da Spalte B eine Kopie von Spalte A ist, wäre das Ergebnis in beiden Fällen dasselbe, aber dies ist ein erfundenes Beispiel, und das wird selten der Fall sein. Sie müssen den Unterschied zwischen den beiden Optionen verstehen.
    Abbildung H.
  5. Klicken Sie auf Duplikate entfernen.
  6. Im nächsten Bereich gibt es nichts zu ändern. Klicken Sie daher auf OK.
  7. Wenn Excel die entfernten Duplikate bestätigt, klicken Sie auf OK. Die Ergebnisse sind in Abbildung I dargestellt .
    Abbildung I.

Excel hat die Duplikate entfernt, wodurch sich das Ergebnis der Funktion AVERAGE () in B11 ändert. Die Funktion in B11 wertet weiterhin B2: B10 aus, aber AVERAGE () ignoriert leere Zellen.

Wenn Sie noch Excel 2003 verwenden, verwenden Sie die Funktion "Erweiterter Filter", um eine eindeutige Liste zu erstellen. Lesen Sie "Wie kann ich ... eine eindeutige Liste in Excel zurückgeben?" - speziell der erste Abschnitt, Manuell, der einfache Weg, für spezifische Anweisungen.

Einfacher arbeiten

Die Verwendung einer integrierten Funktion anstelle komplizierter Ausdrücke ist nicht immer möglich. Nehmen Sie sich jedoch eine Minute Zeit, um Ihre Strategie zu überdenken, wenn Sie Schwierigkeiten haben, eine Lösung zu finden. Was müssen Sie wirklich sehen? Wie werden Sie die Ergebnisse verwenden? Oft bietet Excel Funktionen, mit denen Sie ohne harte Arbeit die gewünschten Ergebnisse erzielen können! Wenn Sie eine Reihe komplexer Ausdrücke mithilfe einer integrierten Funktion entfernt haben, teilen Sie Ihre Erfahrungen im Kommentarbereich mit.

Senden Sie mir Ihre Frage zu Office

Ich beantworte die Fragen der Leser, wenn ich kann, aber es gibt keine Garantie. Seien Sie so genau wie möglich, wenn Sie mich kontaktieren. Beispiel: "Bitte beheben Sie Fehler in meiner Arbeitsmappe und beheben Sie, was falsch ist" wird wahrscheinlich keine Antwort erhalten, aber "Können Sie mir sagen, warum diese Formel nicht die erwarteten Ergebnisse zurückgibt?" könnte. Ich werde weder von TechRepublic für meine Zeit oder mein Fachwissen erstattet, noch verlange ich von den Lesern eine Gebühr. Sie können mich unter kontaktieren.

© Copyright 2020 | mobilegn.com