Excel Life Hacks für diejenigen, die an der Berichterstattung und Datenverarbeitung beteiligt sind

2024 Autor: Malcolm Clapton | [email protected]. Zuletzt bearbeitet: 2024-01-13 01:08

In diesem Beitrag teilt Renat Shagabutdinov, stellvertretender Generaldirektor von Mann, Ivanov und Ferber Publishing House, einige coole Excel-Lifehacks. Diese Tipps werden für alle nützlich sein, die mit verschiedenen Berichten, Datenverarbeitungen und Präsentationen zu tun haben.

Dieser Artikel enthält einfache Techniken, um Ihre Arbeit in Excel zu vereinfachen. Sie sind besonders nützlich für diejenigen, die sich mit Management-Reporting befassen, verschiedene analytische Berichte basierend auf Downloads von 1C und anderen Berichten erstellen, Präsentationen und Diagramme daraus für das Management erstellen. Ich behaupte nicht, eine absolute Neuheit zu sein - in der einen oder anderen Form wurden diese Techniken wahrscheinlich in den Foren diskutiert oder in Artikeln erwähnt.

Einfache Alternativen zu SVERWEIS und HVERWEIS, wenn die gewünschten Werte nicht in der ersten Spalte der Tabelle stehen: LOOKUP, INDEX + SEARCH

Die Funktionen SVERWEIS und HVERWEIS funktionieren nur, wenn sich die gewünschten Werte in der ersten Spalte oder Zeile der Tabelle befinden, aus der Sie Daten abrufen möchten.

Ansonsten gibt es zwei Möglichkeiten:

1. Verwenden Sie die LOOKUP-Funktion.

   Es hat die folgende Syntax: LOOKUP (lookup_value; lookup_vector; result_vector). Damit es jedoch richtig funktioniert, müssen die Werte des view_vector-Bereichs in aufsteigender Reihenfolge sortiert werden:

   

2. Verwenden Sie eine Kombination aus MATCH- und INDEX-Funktionen.

   Die Funktion MATCH gibt die Ordnungszahl eines Elements im Array zurück (mit ihrer Hilfe können Sie herausfinden, in welcher Zeile der Tabelle sich das gesuchte Element befindet), und die Funktion INDEX gibt ein Arrayelement mit einer bestimmten Nummer zurück (die wir herausfinden werden mit der MATCH-Funktion).

   

   Funktionssyntax:

   • SEARCH (search_value; search_array; match_type) - für unseren Fall benötigen wir einen passenden Typ "exact match", er entspricht der Zahl 0.

   • INDEX (Array; Zeilennummer; [Spaltennummer]). In diesem Fall müssen Sie die Spaltennummer nicht angeben, da das Array aus einer Zeile besteht.

So füllen Sie schnell leere Zellen in einer Liste

Die Aufgabe besteht darin, die Zellen in der Spalte mit den Werten oben auszufüllen (so dass das Thema in jeder Zeile der Tabelle und nicht nur in der ersten Zeile des Bücherblocks zum Thema steht):

Wählen Sie die Spalte "Betreff", klicken Sie auf das Menüband in der Gruppe "Start", die Schaltfläche "Suchen und Auswählen" → "Wählen Sie eine Gruppe von Zellen aus" → "Leere Zellen" und beginnen Sie mit der Eingabe der Formel (dh setzen Sie ein Gleichheitszeichen). Zeichen) und verweisen Sie auf die Zelle oben, indem Sie einfach auf den Aufwärtspfeil auf Ihrer Tastatur klicken. Drücken Sie danach Strg + Eingabetaste. Danach können Sie die empfangenen Daten als Werte speichern, da die Formeln nicht mehr benötigt werden:

So finden Sie Fehler in einer Formel

Berechnung eines separaten Teils einer Formel

Um eine komplexe Formel zu verstehen (in der andere Funktionen als Funktionsargumente verwendet werden, dh einige Funktionen sind in andere verschachtelt) oder um die Fehlerquelle darin zu finden, müssen Sie oft einen Teil davon berechnen. Es gibt zwei einfache Möglichkeiten:

1. Um einen Teil einer Formel direkt in der Bearbeitungsleiste zu berechnen, wählen Sie diesen Teil aus und drücken Sie F9:

   

   In diesem Beispiel gab es ein Problem mit der SEARCH-Funktion - Argumente wurden darin vertauscht. Beachten Sie, dass der berechnete Teil eine Zahl bleibt, wenn Sie die Berechnung des Teils der Funktion nicht abbrechen und die Eingabetaste drücken.

2. Klicken Sie im Menüband in der Gruppe Formeln auf die Schaltfläche Formel berechnen:

   

   Im erscheinenden Fenster können Sie die Formel Schritt für Schritt berechnen und feststellen, an welcher Stelle und in welcher Funktion ein Fehler auftritt (falls vorhanden):

   

So bestimmen Sie, wovon eine Formel abhängt oder worauf sie sich bezieht

Um zu bestimmen, von welchen Zellen eine Formel abhängt, klicken Sie im Menüband in der Gruppe Formeln auf die Schaltfläche Auswirkungen auf Zellen:

Pfeile zeigen an, wovon das Berechnungsergebnis abhängt.

Wenn das im Bild rot hervorgehobene Symbol angezeigt wird, hängt die Formel von den Zellen auf anderen Blättern oder in anderen Büchern ab:

Wenn wir darauf klicken, können wir genau sehen, wo sich die beeinflussenden Zellen oder Bereiche befinden:

Neben der Schaltfläche "Beeinflussende Zellen" befindet sich die Schaltfläche "Abhängige Zellen", die auf die gleiche Weise funktioniert: Sie zeigt Pfeile von der aktiven Zelle mit einer Formel zu den davon abhängigen Zellen.

Mit der Schaltfläche "Pfeile entfernen", die sich im selben Block befindet, können Sie Pfeile zu beeinflussenden Zellen, Pfeile zu abhängigen Zellen oder beide Arten von Pfeilen gleichzeitig entfernen:

So finden Sie die Summe (Anzahl, Durchschnitt) der Zellwerte aus mehreren Blättern

Angenommen, Sie haben mehrere Blätter desselben Typs mit Daten, die Sie hinzufügen, zählen oder auf andere Weise verarbeiten möchten:

Geben Sie dazu in der Zelle, in der Sie das Ergebnis sehen möchten, eine Standardformel ein, z. B. SUM (SUM), und geben Sie den Namen des ersten und letzten Blatts aus der Liste der Blätter an, die Sie verarbeiten müssen das Argument, getrennt durch einen Doppelpunkt:

Die Summe der Zellen mit der Adresse B3 erhalten Sie aus den Blättern "Daten1", "Daten2", "Daten3":

Diese Adressierung funktioniert bei gefundenen Blättern konsequent … Die Syntax lautet wie folgt: = FUNCTION (first_list: last_list! Bereichsreferenz).

So erstellen Sie automatisch Vorlagenphrasen

Mit den Grundprinzipien der Arbeit mit Text in Excel und einigen einfachen Funktionen können Sie Vorlagenphrasen für Berichte vorbereiten. Mehrere Prinzipien der Arbeit mit Text:

• Wir verketten den Text mit dem &-Zeichen (Sie können es durch die Funktion CONCATENATE ersetzen, aber das macht nicht viel Sinn).
• Der Text wird immer in Anführungszeichen geschrieben, Verweise auf Zellen mit Text sind immer ohne.
• Um das Servicezeichen "Anführungszeichen" zu erhalten, verwenden Sie die CHAR-Funktion mit Argument 32.

Ein Beispiel für das Erstellen eines Vorlagensatzes mithilfe von Formeln:

Ergebnis:

In diesem Fall wird neben der Funktion CHAR (zum Anzeigen von Angeboten) die Funktion IF verwendet, mit der Sie den Text in Abhängigkeit von einer positiven Umsatzentwicklung ändern können, und die Funktion TEXT, mit der Sie die Nummer in einem beliebigen Format. Seine Syntax wird im Folgenden beschrieben:

TEXT (Wert; Format)

Das Format wird in Anführungszeichen angegeben, als ob Sie ein benutzerdefiniertes Format im Fenster Zellen formatieren eingeben würden.

Auch komplexere Texte können automatisiert werden. In meiner Praxis gab es die Automatisierung von langen, aber routinemäßigen Kommentaren zum Management-Reporting im Format „INDIKATOR fiel / stieg um XX relativ zum Plan, hauptsächlich aufgrund des Wachstums / Rückgangs von FAKTOR1 um XX, Wachstum / Rückgang von FAKTOR2 um YY …“mit einer sich ändernden Liste von Faktoren. Wenn Sie solche Kommentare häufig schreiben und der Prozess des Schreibens algorithmisiert werden kann, lohnt es sich, einmal rätselhaft eine Formel oder ein Makro zu erstellen, die Ihnen zumindest einen Teil der Arbeit erspart.

So speichern Sie Daten in jeder Zelle nach der Verkettung

Beim Zusammenführen von Zellen wird nur ein Wert beibehalten. Excel warnt davor, wenn Sie versuchen, Zellen zusammenzuführen:

Wenn Sie also eine Formel hatten, die von jeder Zelle abhängt, funktioniert sie nach dem Kombinieren nicht mehr (# N / A-Fehler in den Zeilen 3-4 des Beispiels):

Um Zellen zusammenzuführen und trotzdem die Daten in jeder von ihnen beizubehalten (vielleicht haben Sie eine Formel wie in diesem abstrakten Beispiel; vielleicht möchten Sie Zellen zusammenführen, aber alle Daten für die Zukunft behalten oder absichtlich ausblenden), führen Sie alle Zellen auf dem Blatt zusammen, wählen Sie sie aus und verwenden Sie dann den Befehl Format Painter, um die Formatierung auf die Zellen zu übertragen, die Sie kombinieren möchten:

So erstellen Sie einen Pivot aus mehreren Datenquellen

Wenn Sie einen Pivot aus mehreren Datenquellen gleichzeitig erstellen müssen, müssen Sie den "PivotTable- und Diagramm-Assistenten" zum Menüband oder Schnellzugriffsbereich hinzufügen, der über eine solche Option verfügt.

Dies können Sie wie folgt tun: „Datei“→ „Optionen“→ „Symbolleiste für den Schnellzugriff“→ „Alle Befehle“→ „PivotTable- und Diagramm-Assistent“→ „Hinzufügen“:

Danach erscheint im Menüband ein entsprechendes Symbol, auf das derselbe Assistent aufgerufen wird:

Wenn Sie darauf klicken, erscheint ein Dialogfeld:

Darin müssen Sie den Punkt "In mehreren Konsolidierungsbereichen" auswählen und auf "Weiter" klicken. Im nächsten Schritt können Sie "Ein Seitenfeld erstellen" oder "Seitenfelder erstellen" auswählen. Wenn Sie für jede der Datenquellen unabhängig voneinander einen Namen finden möchten, wählen Sie das zweite Element aus:

Fügen Sie im nächsten Fenster alle Bereiche hinzu, auf deren Grundlage der Pivot aufgebaut wird, und benennen Sie sie:

Geben Sie anschließend im letzten Dialogfeld an, wo der Pivot-Tabellenbericht platziert werden soll - auf einem vorhandenen oder neuen Blatt:

Der Pivot-Tabellenbericht ist fertig. Im Filter "Seite 1" können Sie bei Bedarf nur eine der Datenquellen auswählen:

So berechnen Sie die Häufigkeit des Vorkommens von Text A in Text B ("MTS SuperMTS-Tarif" - zwei Vorkommen der Abkürzung MTS)

In diesem Beispiel enthält Spalte A mehrere Textzeilen, und unsere Aufgabe besteht darin, herauszufinden, wie oft jede von ihnen den Suchtext in Zelle E1 enthält:

Um dieses Problem zu lösen, können Sie eine komplexe Formel verwenden, die aus den folgenden Funktionen besteht:

1. DLSTR (LEN) - berechnet die Länge des Textes, das einzige Argument ist der Text. Beispiel: DLSTR ("Maschine") = 6.
2. ERSATZ - ersetzt einen bestimmten Text in einer Textzeichenfolge durch einen anderen. Syntax: SUBSTITUTE (Text; alter_Text; neuer_Text). Beispiel: ERSATZ („auto“; „auto“; „“) = „mobil“.
3. UPPER - ersetzt alle Zeichen in einer Zeichenfolge durch Großbuchstaben. Das einzige Argument ist Text. Beispiel: UPPER („Maschine“) = „AUTO“. Wir benötigen diese Funktion, um Suchen ohne Berücksichtigung der Groß-/Kleinschreibung durchzuführen. Schließlich ist UPPER ("Auto") = UPPER ("Maschine")

Um das Vorkommen einer bestimmten Textzeichenfolge in einer anderen zu finden, müssen Sie alle ihre Vorkommen in der Originalzeichenfolge löschen und die Länge der resultierenden Zeichenfolge mit der Originalzeichenfolge vergleichen:

DLSTR („Tarif MTS Super MTS“) - DLSTR („Tarif Super“) = 6

Und dann dividiere diese Differenz durch die Länge der gesuchten Zeichenfolge:

6 / DLSTR („MTS“) = 2

Es ist genau zweimal, dass die Zeile "MTS" im Original enthalten ist.

Es bleibt noch, diesen Algorithmus in der Sprache der Formeln zu schreiben (mit „Text“bezeichnen wir den Text, in dem wir nach Vorkommen suchen, und mit „gesucht“- den Text, an dessen Häufigkeit wir interessiert sind):

= (DLSTR (Text) -LSTR (SUBSTITUTE (UPPER (Text); UPPER (Suche), ""))) / DLSTR (Suche)

In unserem Beispiel sieht die Formel so aus:

= (DLSTR (A2) -LSTR (ERSATZ (OBERES (A2), OBERES ($ E $ 1), „“)))) / DLSTR ($ E $ 1)