Eine Excel-Buch-Empfehlung

Der Tabellenexperte Martin Weiß hat ein neues Buch geschrieben. Sein Titel ist „Excel Pivot-Tabellen für Dummies. Erschienen ist das Buch erst vor Kurzem im Wiley-VCH Verlag.

Wie ich das Buch sehe, darum geht es in diesem Blogbeitrag.

Weiterlesen „Eine Excel-Buch-Empfehlung“

Advertisements

Monatliche Planwerte mit Excel ermitteln

 

Wenn in einem Unternehmen die einzelnen Planwerte nur für das gesamte Planjahr ermittelt werden, bleibt die Aufgabe, diese Jahreswerte sinnvoll auf die Planmonate aufzuteilen.

Weiterlesen „Monatliche Planwerte mit Excel ermitteln“

Kursdiagramm Volumen-Höchst-Tiefst-Schlusskurs erstellen

Das Thema „Kursdiagramm“ war bereits zweimal Gegenstand eines Blogbeitrages:

Der Beitrag heute erläutert das „Volumen-Höchst-Tiefst-Schlusskurs“-Diagramm.

Weiterlesen „Kursdiagramm Volumen-Höchst-Tiefst-Schlusskurs erstellen“

Excel-Minis 20171128

Excel-Minis sind an dieser Stelle immer Kurzbeiträge, natürlich zum Thema Excel.

Für heute habe ich drei kleine Themen vorbereitet, vielleicht hast du Spaß damit.

Weiterlesen „Excel-Minis 20171128“

Excel-Tabellen in Word-Berichte einbetten

Als Controller kennst du diese Aufgabe bestimmt. In einem monatlichen Managementbericht, den du in MS Word erstellst, ist u.a. die Liquiditätsentwicklung des Unternehmens darzustellen.

Sinnvollerweise führst du die Liquiditätsentwicklung in MS Excel. Die Aufgabe besteht nun darin, die Excel-Tabelle in den Word-Bericht einzuarbeiten und beides zu verknüpfen.

Weiterlesen „Excel-Tabellen in Word-Berichte einbetten“

Zellen in Excel bedingt formatieren, die enthalten

Das sich mit der bedingten Formatierung eine Menge Effekte in einer Excel-Arbeitsmappe erreichen lassen, hast du schon in mehreren Beiträgen sehen können.

Eine weitere Möglichkeit ist Gegenstand dieses Beitrages. Du kannst damit Zellinhalte formatieren, die bestimmte Eigenschaften haben oder bestimmte Werte beinhalten: Nur Zellen formatieren, die enthalten

Weiterlesen „Zellen in Excel bedingt formatieren, die enthalten“

Versuchs doch mal mit INDIREKT

1. Beschreibung der Funktion

Die Excel-Funktion INDIREKT habe ich schon in vielen Beiträgen eingesetzt, sie aber bisher nicht erläutert.

INDIREKT ist im Funktionskatalog von Excel unter der Kategorie „Nachschlagen und Verweisen“ zu finden.

Die Funktion kann nützlich sein, wenn in Dateien mit vielen Arbeitsblättern dynamisch auf bestimmte Zellen oder Zellbereiche in den Blättern zugegriffen werden muss.

Doch Achtung! INDIREKT ist eine volatile Funktion. Peter Haserodt von Online-Excel schrieb einmal dazu [1]:

Indirekt sollte nur sehr sparsam angewendet werden und nicht bei rechenintensiven Formeln. Diese Funktion hat nämlich den großen Nachteil, dass sie volatil ist, d.h. sie wird immer neu berechnet, nach jeder Eingabe. Selbst dann, wenn sie in einer anderen geöffneten Arbeitsmappe steht!

Wichtig zu wissen ist auch, dass mit INDIREKT nur auf geöffnete Dateien zugegriffen werden kann.

Was schreibt die Excel-Hilfe?

Hier heißt es, INDIREKT „Gibt den Bezug eines Textwertes zurück. Bezüge werden sofort ausgewertet, so dass die zu ihnen gehörenden Werte angezeigt werden.“

Die Syntax lautet:

=INDIREKT(Bezug;[A1])

Bezug ist eine erforderliches Argument, möglich ist der Bezug auf eine Zelle, die einen Bezug in der A1-Schreibweise, einen Bezug in der Z1S1-Schreibweise, einen definierten Namen als Bezug oder einen Zellbezug als Zeichenfolge enthält.

Das Argument A1 kann optional eingesetzt werden. Es ist ein Wahrheitswert, der angibt, welcher Art der Bezug in der Zelle ist.

Ist A1 gleich WAHR, liegt die A1-Schreibweise vor.

Ist A1 gleich FALSCH, liegt die Z1S1-Schreibweise vor.

2. Beispiele

Beispiel 1: Wert einer Zelle im gleichen Arbeitsblatt abfragen

Die nachfolgende Tabelle (Arbeitsblatt Tabelle2) zeigt 12 Monate und Umsatzgrößen.

Indirekt1

Mit INDIREKT willst du den Umsatz des Monats, der in Zelle F2 angegeben ist, in Zelle F4 zeigen. Du schreibst in Tabelle2 in F4 die Formel

=INDIREKT(„C“&$F$2+2)

Warum das „+2“? Weil die Monate erst in Zeile 3 beginnen. Der dritte Monat befindet sich in Zeile 5.

Änderst du den Eintrag in F2, ändert sich das Ergebnis in F4 entsprechend.

Beispiel 2: Wert einer Zelle in einem anderen Arbeitsblatt abfragen

Angenommen, die Tabelle steht im Arbeitsblatt „Tabelle2“, das Ergebnis soll im Blatt „Tabelle1“ gezeigt werden.

Dann schreibst du in F4 die Formel:

=INDIREKT(„Tabelle2!C“&Tabelle1!F2+2)

und erhältst den gesuchten Umsatz.

Beispiel 3: Summen von Werten abfragen

In diesem Beispiel suchst du die Summe über mehrere Monate des Jahres, z.B. von März (3) bis Mai (5). Die Tabelle befindet sich wieder in „Tabelle2“, das Ergebnis soll ebenfalls in „Tabelle1“ gezeigt werden.

Indirekt2

Verwende diese Formel in F5:

=SUMME(INDIREKT(„Tabelle2!C“&Tabelle1!F2+2):INDIREKT(„Tabelle2!C“&Tabelle1!F3+2))

Wenn dir die Zelladressen der jeweiligen Monate bekannt sind, kannst du auch so vorgehen:

Indirekt3

Schreibe dazu in F4 die Formel:

=SUMME(INDIREKT($F$2))

Beispiel 4: Wert aus einem namentlichen Bereich abfragen

Für dieses Beispiel benennst du den Bereich B3:B14 mit „Monat“ und den Bereich C3:C14 mit „Umsatz“.

Du willst in Tabelle2 in F5 z.B. den Umsatz des Monats März sehen.

Schreibe:

=INDEX(Umsatz;INDIREKT(„F2“);1)

Die Formel macht allerdings nicht viel Sinn, zeigt aber die Funktionsweise von INDIREKT.

Einfacher wäre es so:

=INDEX(Umsatz;F2;1)

Beispiel 5: Summe von Werten eines namentlichen Bereiches abfragen

Willst du nicht nur den Umsatz eines Monats sehen, sondern z.B. die Summe von Mai bis August, kannst du diese Formel verwenden (in F2 steht eine 5, in F3 steht eine 8).

=SUMME(INDEX(Umsatz;INDIREKT(„F2“);1):INDEX(Umsatz;INDIREKT(„F3“);1))

Beispiel 6: Mit INDIREKT auf einen durch Verkettung erzeugten Bereichsnamen zugreifen

Dir liegen die monatlichen Umsätze aus drei Jahren vor. Du vergibst Namen für die Bereiche:

  • C5:C16 Umsatz2014
  • F5:F16 Umsatz2015
  • I5:I16 Umsatz2016

Indirekt4

Für die Abfrage hast du diese Felder vorbereitet:

Indirekt5

In C19 schreibst du das Jahr, in C21 den Monat deiner Wahl.

C23 enthält die Formel

=“Umsatz“&C19

In C25 schreibst du schließlich die Formel

=INDEX(INDIREKT(C23);C21;1)

In C25 kannst du jetzt den Umsatz für den 6. Monat des Jahres 2014 ablesen.

INDIREKT hast du dazu verwendet, den Namen der Matrix, der in C23 durch Verkettung erzeugt wurde, in die INDEX-Formel einzugeben.

Änderst du den Eintrag in C19 auf 2015, erhältst du den Umsatz für Juni 2015. Das Spiel kann beliebig weiter geführt werden.

Ein ausführliches Beispiel findest du u.a. in meinem Beitrag „Arbeiten mit der rS1-Methode – Namenskonventionen und Beispiel„.

Beispiel 7: Die verschiedenen Schreibweisen

In der A1-Schreibweise suchst du z.B. den Umsatz, der in Zelle C10 steht.

Schreibe in Zelle F2 somit „C10“ hinein.

Indirekt6

Der Umsatz soll in Zelle F4 angezeigt werden. Schreibe dort diese Formel:

=INDIREKT(F2;WAHR)

Das Argument WAHR deutet auf die A1-Schreibweise hin, als „C10“.

Mit der Formel wird nun indirekt auf den Inhalt der Zelle C10 zugegriffen. In C10 wird der Wert 15.464 gefunden.

Mit der Z1S1-Schreibweise werden relative Bezüge hergestellt. Schreibe in F4 diese Formel:

=INDIREKT(„Z10S3“;FALSCH)

FALSCH weist auf die Z1S1-Schreibweise hin. Mit der Formel wird nun auf den Inhalt der Zelle in Zeile (Z) und Spalte (S3) zugegriffen. Dort steht, natürlich, der Wert .

Ebenso könntest du die Zelle F2 statt mit „C.“ jetzt mit „ZS3“ füllen.

Indirekt7

Schreibe in F4 diese Formel:

=INDIREKT(F2;FALSCH)

und du erhältst wieder das Ergebnis 15.464.

[1] http://www.online-excel.de/excel/singsel.php?f=24