Thema: Formeln und Funktionen

Rechnet Excel richtig? 1

 in Formeln und Funktionen / Statistik  Stichworte: QUADRATESUMME / STABW / VARIANZ Von:
Der heutige Gastartikel zeigt, dass in Excel viele Wege zu einer Lösung führen. Und beantwortet eine provokante Frage!
 

Heute gibt es eine Premiere hier bei mir auf dem Blog: Es erscheint der erste Gastartikel, und ich hoffe, es wird nicht der letzte sein!

Den Anfang macht Gerhard Pundt, seines Zeichens Controller und natürlich Excel-Profi. Wer etwas mehr über Gerhard erfahren möchte, findet weitere Informationen am Ende dieses Artikels.

In seinem heutigen Gastbeitrag stellt er die Frage „Rechnet Excel richtig“ und geht damit gleich ans Eingemachte. Viel Spaß beim Lesen und vielen Dank an Gerhard, für diesen schönen und interessanten Beitrag!

Weiterlesen »

Automatische Sortierung (Teil 2) 43

 in Formeln und Funktionen / Nachschlagen und Verweisen / Statistik  Stichworte: INDEX / VERGLEICH / ZÄHLENWENN / ZEILEN Von:
Mit ein paar Excel-Funktionen und einem kleinen Trick lassen sich auch alphanumerische Listen automatisch sortiert ausgeben.
 

In der letzten Woche habe ich dir gezeigt, wie man eine unsortierte numerische Liste automatisch sortiert an einer anderen Stelle ausgeben kann. Was ist aber, wenn die zu sortierende Spalte keine Zahlen, sondern Texte enthält? Dann funktioniert die vorgestellte Weg leider nicht.

Aber es wäre ja gelacht, wenn es für dieses Problem nicht auch eine einfache Lösung gäbe. Mit einem kleinen Trick und dem geschickten Einsatz der folgenden Excel-Funktionen bekommen wir auch Texte automatisch sortiert:

  • INDEX
  • VERGLEICH
  • ZÄHLENWENN

Und so geht’s:

Weiterlesen »

Automatische Sortierung (Teil 1) 38

 in Formeln und Funktionen / Nachschlagen und Verweisen / Statistik  Stichworte: INDEX / KGRÖSSTE / KKLEINSTE / VERGLEICH / ZEILEN Von:
Das automatische Sortieren von Excel-Listen ist mit den Funktionen KKLEINSTE und KGRÖSSTE gar nicht so schwer
 

Ein Leser hatte kürzlich wegen einer automatischen Sortierung einer Tabelle angefragt: Datensätze werden (unsortiert) in einem Arbeitsblatt eingegeben und sollen in einem anderen Arbeitsblatt automatisch sortiert angezeigt werden.

Da dies auch für andere Excel-Anwender interessant sein könnte, nehme ich diese Inspiration (danke, Daniel!) zum Anlass für einen entsprechenden Blogartikel.

Je nach Art und Inhalt der zu sortierenden Spalte können unterschiedliche Methoden zum Einsatz kommen.

Heute sehen wir uns die automatische Sortierung von numerischen Spalten an und nutzen dabei vor allem die folgenden Funktionen:

  • KKLEINSTE
  • KGRÖSSTE

Außerdem zeige ich dir, welche Stolpersteine es dabei zu beachten gilt.

Und so geht’s:

Weiterlesen »

Bedingte Summen in Excel (Teil 3) 13

 in Datum und Uhrzeit / Formeln und Funktionen / Mathematik und Trigonometrie / Nachschlagen und Verweisen  Stichworte: BEREICH.VERSCHIEBEN / MONAT / SUMME / VERGLEICH Von:
Maximale Flexibilität bei der Bildung von Summen in Excel: Sowohl Zeilen als auch Spalten können variabel sein!
 

Letzte Woche habe ich dir hier gezeigt, wie man mit der geschickten Kombination der Funktionen SUMME und BEREICH.VERSCHIEBEN in Excel Werte unter bestimmten Bedingungen aufsummieren kann.

Dabei war die zu summierende Zeile variabel, die Spalten hingegen fix. Heute gehen wir noch einen Schritt weiter und wollen zusätzlich auch die zu summierenden Spalten möglichst flexibel halten.

Und so geht’s:

Weiterlesen »

Bedingte Summen in Excel (Teil 2) 4

 in Formeln und Funktionen / Mathematik und Trigonometrie / Nachschlagen und Verweisen  Stichworte: BEREICH.VERSCHIEBEN / SUMME / VERGLEICH Von:
Summenbildung unter bestimmten Bedingungen kann manchmal etwas vertrackt sein. Die Lösung gibt's hier.
 

Das Aufsummieren von Werten in Excel-Tabellen unter bestimmten Bedingungen ist manchmal nicht ganz so trivial, wie es auf den ersten Blick aussieht.

Im Artikel „Alle Kriterien erfüllt? Bedingte Summen in Excel“ habe ich schon verschiedene Möglichkeiten aufgezeigt.

Manchmal ist die Aufgabenstellung aber etwas anders, so dass die gezeigten Wegen möglicherweise nicht zum Ergebnis führen. Wie bildet man z.B. eine Summe über mehrere Spalten, wenn die jeweilige Zeile variabel ist, also von einem bestimmten Kriterium abhängt?

So geht’s:

Weiterlesen »

Wenn’s auf den Einzelnen ankommt: Werte in Pivot-Tabellen ansprechen 37

 in Daten- und Analysetools / Nachschlagen und Verweisen  Stichworte: Pivot-Tabelle / PIVOTDATENZUORDNEN Von:
Mit der Funktion PIVOTDATENZUORDNEN kannst du gezielt Ergebnisse aus deiner Pivot-Tabelle herauspicken und weiterverarbeiten.
 

Wie man zu Analysezwecken seine Daten mit Hilfe von Pivot-Tabellen verdichtet, habe ich schon in den Artikeln Der beste Freund des Excel-Analysten: Pivot-Tabellen und Der Horror geht weiter: Pivot-Tabellen Teil 2 gezeigt.

Wie greift man jetzt aber gezielt auf einzelne Ergebnisse innerhalb einer Pivot-Tabelle zu, um diese woanders weiterzuverarbeiten? (Und warum sollte man so etwas überhaupt wollen?)

Hier bekommst du die Antworten darauf:

Weiterlesen »

Statistik leicht gemacht: Den häufigsten Wert ermitteln 14

 in Formeln und Funktionen / Statistik  Stichworte: MODALWERT / MODUS.EINF / MODUS.VIELF / ZÄHLENWENN / ZUFALLSBEREICH Von:
Mit ein paar einfachen Excel-Funktionen lassen sich die häufigsten Werte in einer Tabelle ermitteln
 

Wie heißt es doch: Traue keiner Statistik, die Du nicht selbst gefälscht hast…

Excel bietet eine Vielzahl von statistischen Funktionen. Wer bei diesem Begriff jetzt zusammenzuckt und mit Schrecken an gruselige Statistik-Vorlesungen im Studium zurückdenkt, den kann ich beruhigen: So schlimm wird es nicht.

Einige dieser Funktionen sind durchaus alltagstauglich, wie z.B. MAX oder MIN zur Bestimmung des größten oder kleinsten Wertes einer Tabelle.

Wie ermittle ich jedoch den oder die am häufigsten vorkommenden Wert(e) in einer Liste? Auch hierbei läßt Excel einen nicht im Stich:

  • MODALWERT
  • MODUS.EINF
  • MODUS.VIELF

Und so geht’s:

Weiterlesen »

Trans Am: Was ein amerikanischer Sportwagen und Excel gemeinsam haben 1

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: Matrixformel / MTRANS / Transponieren Von:
Nicht für jeden Einsatzzweck geeignet, aber in bestimmten Fällen die einzige Lösung: Eine Funktion zum Vertauschen von Zeilen und Spalten.
 

Die etwas älteren und vor allem männlichen Leser werden sie noch kennen: die amerikanische Sportwagen-Legende Pontiac Firebird, auch Trans Am genannt.

Doch was hat der Flitzer, der unter anderem als K.I.T.T. aus der Serie „Knight Rider“ bekannt ist, mit Excel gemeinsam? Nun, es hat mit Kopieren und Einfügen zu tun, mit Matrix-Formeln und Arrays…

Lass dich überraschen!

Weiterlesen »

Alle Kriterien erfüllt? Bedingte Summen in Excel 40

 in Datenbank / Formeln und Funktionen / Mathematik und Trigonometrie  Stichworte: DBSUMME / SUMMEWENN / SUMMEWENNS Von:
Zur Ermittlung von Summen, die bestimmten Kriterien entsprechen sollen, gibt es mehrere Möglichkeiten in Excel.
 

DIE Standardfunktion in Excel, die jeder kennt, ist die SUMME-Funktion. Was macht man aber, wenn man nicht einfach nur sämtliche Werte einer Liste summieren möchte, sondern nur solche, die eine bestimmte Bedingung erfüllen?

Und was, wenn es sich nicht nur um eine Bedingung handelt, sondern gleich um mehrere?

Folgende Funktionen sehen wir uns dazu an:

  • SUMMEWENN
  • SUMMEWENNS
  • DBSUMME

Und so geht’s:

Weiterlesen »

5 nützliche Excel-Funktionen… zum Runden 15

 in Formeln und Funktionen / Mathematik und Trigonometrie  Stichworte: ABRUNDEN / AUFRUNDEN / GERADE / RUNDEN / UNGERADE / VRUNDEN Von:
Mit speziellen Rundungsfunktionen lassen sich in Excel auch ausgefallenere Rundungsanforderungen erfüllen.
 

Dass Rechenergebnisse in Excel oftmals viele Nachkommastellen haben, liegt in der Natur der Sache. Speziell beim Rechnen mit Geldbeträgen kommt daher häufig die bekannte RUNDEN-Funktion zum Einsatz, um die Beträge auf maximal zwei Nachkommastellen zu runden.

Manchmal reichen jedoch die normalen Rundungsregeln nicht aus. Was ist, wenn man z.B. nicht auf- sondern immer abrunden möchte? Oder man will auf die nächste gerade Zahl runden?

Excel bietet für diese und ähnliche Anforderungen eine Reihe von speziellen Rundungsfunktionen an:

  • AUFRUNDEN
  • ABRUNDEN
  • GERADE
  • UNGERADE
  • VRUNDEN

Und so geht’s:

Weiterlesen »

Das Ende der Verschachtelungen: Du hast die WAHL 2

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: WAHL Von:
Die WAHL-Funktion bietet oft einen eleganten Ausweg aus unübersichtlichen WENN-Verschachtelungen.
 

Wer kennt das nicht: Man erstellt eine Excel-Formel, in der man mehrere Bedingungen prüfen muss. Solange es sich nur um eine oder zwei Bedingungen handelt, bekommt man das mit ein paar verschachtelten WENN-Funktionen hin.

Spätestens jedoch bei der dritten Verschachtelung wird es aber unübersichtlich und man verstrickt sich hoffnungslos in einem Wust aus Klammern und Bedingungen. Eine andere Lösung muss her!

Etwas weniger bekannt als ihre „großen“ Brüder SVERWEIS, VERWEIS & Co. bietet die WAHL-Funktion oft einen eleganten Ausweg aus dem Verschachtelungs-Dilemma.

Und so geht’s:

Weiterlesen »

5 nützliche Excel-Funktionen… für Zahlenjongleure 7

 in Formeln und Funktionen / Mathematik und Trigonometrie / Statistik  Stichworte: KGRÖSSTE / KKLEINSTE / OBERGRENZE / ZUFALLSBEREICH / ZUFALLSZAHL Von:
Nützliche, aber teils wenig beachtete Helferlein in Excel beim Umgang mit Zahlen
 

Excel bietet mehrere 100 eingebaute Funktionen und Formeln. Viele davon sind sehr speziell und nur für ganz spezifische Einsatzgebiete geeignet.

Aber es befinden sich auch ein paar Perlen darunter, die man durchaus als „Normalanwender“ gebrauchen kann, die aber in der langen Liste der Funktionen etwas untergehen.

Dazu gehören auch diese 5 Funktionen:

  • ZUFALLSZAHL
  • ZUFALLSBEREICH
  • KGRÖSSTE
  • KKLEINSTE
  • OBERGRENZE

Und so geht’s:

Weiterlesen »

Wie heißt du? Den Namen des Arbeitsblattes in Excel ermitteln

 in Formeln und Funktionen / Informationen / Text  Stichworte: FINDEN / LÄNGE / RECHTS / ZELLE Von:
Der Name eines Excel-Arbeitsblattes lässt sich mit der ZELLE-Funktion und einem kleinen Trick ermitteln.
 

Eine auf den ersten Blick triviale Fragestellung entpuppt sich als ziemlich kniffelig: Wie kann man eingentlich in einer Excel-Formel den Namen eines Arbeitsblattes ermitteln?

Wozu man so etwas braucht? Zum Beispiel um ein Inhaltsverzeichnis seiner Arbeitsblätter zu erstellen, welches sich auch automatisch anpasst, wenn ein Blatt umbenannt wird.

Kaum zu glauben, aber es gibt in Excel dafür keine Standardfunktion. Trotzdem ist eine Lösung ohne großen Aufwand möglich.

Und so geht’s:

Weiterlesen »

Das Beste kommt zum Schluß: Die letzte Zeile einer Excel-Tabelle 36

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: ANZAHL2 / INDEX / VERGLEICH Von:
Mit der simplen Kombination von ein paar Excel-Funktionen lässt sich der letzte Eintrag einer Tabelle finden - ganz ohne VBA.
 

Manchmal möchte man den jeweils letzten Eintrag seiner Excel-Tabelle zusätzlich noch an einer anderen Stelle ausgeben. Zum Beispiel ganz am Anfang einer sehr langen Tabelle, um den Eintrag jederzeit im Blick zu haben. Oder um ihn vielleicht in einem Formular ausgeben zu können.

Da die Tabelle aber ständig länger wird, sich das Ende als dynamisch nach unten verschiebt, kann man nicht einfach eine Verknüpfung zu einer festen Zeile erstellen, denn man kennt ja das Ende der Tabelle noch nicht.

Mit der geschickten Kombination einiger Excel-Standardfunktionen gibt es auch für dieses Problem ein Happy End. Und zwar ganz ohne VBA-Programmierung bemühen zu müssen.

Und so geht’s:

Weiterlesen »

Unikate gesucht: Listenabgleich in Excel 3

 in Formatierung und Layout / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: Bedingte Formatierung / Matrixformel / SUMMENPRODUKT Von:
Mit der SUMMENPRODUKT-Funktion lassen sich in Excel Unikate in zwei unterschiedlichen Listen finden.
 

Die Verarbeitung von Listen gehört bekanntermaßen zu den Standardanwendungen von Excel. Wie man Dubletten in solchen Listen findet, habe ich im Artikel „Das doppelte Lottchen“ ja schon gezeigt.

Doch was macht man, wenn man stattdessen Unikate in zwei unterschiedlichen Listen identifizieren möchte? Sozusagen das schwarze Schaf in der weißen Herde? Den Osterhasen unter den Nikoläusen?

Eine geschickte Kombination der SUMMENPRODUKT-Funktion mit der bedingten Formatierung führt hier zum Ziel.

Und so geht’s:

Weiterlesen »

Ein echtes Dream-Team: INDEX und VERGLEICH 82

 in Allgemeine Excel-Tipps / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: INDEX / Matrixformel / MAX / SVERWEIS / VERGLEICH Von:
Mit der INDEX-Funktion lassen sich die Grenzen des SVERWEIS überwinden!
 

Eine bei vielen Anwendern oft verschmähte oder wenig verstandene Funktion führt völlig zu Unrecht ein ziemliches Schattendasein in der Excel-Welt: INDEX.

Dabei bietet diese unscheinbare Funktion das Potential, selbst die universelle SVERWEIS-Funktion blass aussehen zu lassen, wenn man ihr noch einen Partner zur Seite stellt.

Das neue Dream-Team heißt daher:

  • INDEX
  • VERGLEICH

Und so geht’s:

Weiterlesen »