Thema: Formeln und Funktionen

Den Rang innerhalb einer Gruppe berechnen 22

 in Formeln und Funktionen / Statistik  Stichworte: RANG.GLEICH / SUMMENPRODUKT Von:
Wenn die normale RANG-Funktion nicht weiterhilft: Rang-Berechnung innerhalb von Gruppen oder Kategorien.
 

Vor geraumer Zeit habe ich gezeigt, wie man in einer Werteliste einfache Rangfolgen berechnet.

Einige Leser wollten nun wissen, wie man so etwas macht, wenn die Liste nicht „flach“ ist, sondern Gruppierungsmerkmale enthält. In Umsatzlisten könnten das Regionen sein, im Sport Altersklassen, Geschlecht oder ähnliches.

Der heutige Artikel zeigt eine Möglichkeit, Rangfolgen innerhalb solcher Kategorien zu berechnen.

Und so geht’s:

Weiterlesen »

Excel-Funktionen von A bis Z

 in Formeln und Funktionen  Stichworte: Funktionsübersicht Von:
Eine Übersicht aller Excel-Funktionen in Deutsch und Englisch inkl. Verfügbarkeit in den verschiedenen Excel-Versionen
 

In vielen Unternehmen sind aus unterschiedlichsten Gründen nicht überall die gleichen Excel-Versionen im Einsatz. Da kann es sehr schnell passieren, dass eine in der eigenen Arbeitsmappe verwendete Funktion bei einem Kollegen in der Nachbararbteilung leider nur zu einer kryptischen Fehlermeldung führt, da er mit einer älteren Excel-Version arbeitet.

Damit dies zukünftig nicht mehr passiert, habe ich mir etwas Besonderes überlegt und eine vollständige Übersicht aller eingebauten Excel-Funktionen und deren Verfügbarkeit in den verschiedenen Excel-Versionen erstellt. Und nicht nur das: Die Übersicht liefert neben den deutschen Funktionsnamen auch gleich noch die englische Variante mit, für den Fall, dass auf deinem Computer Excel in der englischsprachigen Version installiert ist.

Und das bietet meine Übersicht:

  • Mit deutschen und englischen Funktionsnamen
  • Angabe der Verfügbarkeit in den verschiedenen Excel-Versionen
  • Verlinkung über den Funktionsnamen zu bereits veröffentlichten Artikeln hier auf dem Blog
  • Freie Sortierung der Tabelle über die kleinen Pfeilsymbole neben dem Spaltennamen
  • Freitextsuche und Filter

Und hier geht’s zur Funktionsübersicht…

Weiterlesen »

Die Matrix Reloaded. Oder in Excel: {STRG+Umschalt+Eingabe} 14

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: Matrixformel / MODALWERT / ZÄHLENWENN Von:
Noch mehr Tipps zu Array-Formeln in Excel
 

Ich geb’s ja gerne zu: Mir waren diese gruseligen Array-Formeln mit den geschweiften Klammern selbst lange Zeit unheimlich. Wenn man eine Formel schon mit dieser komischen Tastenkombination abschließen muss, dann kann doch etws nicht stimmen.

Sie sind anfangs schwer zu durchschauen und es bedarf einiger geistiger Kopfstände, bis man den Zugang findet. Aber ich kann dir versprechen, es lohnt sich. Heute geht’s weiter mit ein paar praktischen Anwendungsbeispielen:

  • Quersumme berechnen
  • Listen mit Fehlerwerten summieren
  • Liste auf Dubletten überprüfen
  • Liste per Formel alphabetisch sortieren

Und los geht’s:

Weiterlesen »

Ab in die Matrix: Eine kleine Einführung in Array-Formeln 53

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: KGRÖSSTE / Matrixformel Von:
Für viele ein Buch mit sieben Siegeln: Array- oder Matrix-Formeln erklärt.
 

Auf dem Weg zu den höheren Excel-Weihen führt kein Weg daran vorbei: Du musst den Sprung in die Matrix wagen. Nein, die Rede ist nicht vom Film-Klassiker mit Keanu Reeves. Du bist hier schließlich auf einem Excel-Blog gelandet. Ich spreche von Matrixformeln, auch Array-Formeln genannt.

Was bei dem unbedarften Excel-Anwender ein fragendes Stirnrunzeln auslöst, jagt manch anderem einen Schauer des Entsetzens über den Rücken. Haftet den Array-Formeln vielleicht nicht der Ruf von schwarzer Magie an, so handelt es sich dabei doch zumindest um schwer zu durchschauendes Teufelswerk. Oder?

Und genau mit diesem Vorurteil möchte ich mit der heutigen kleinen Einführung aufräumen.

Weiterlesen »

Nur jede x. Zeile addieren 16

 in Formeln und Funktionen / Mathematik und Trigonometrie  Stichworte: REST / SUMMENPRODUKT / ZEILE Von:
Mit der SUMMENPRODUKT-Funktion nur jede zweite (dritte, vierte) Zeile addieren.
 

Kürzlich hatte ich im Artikel Zählen und addieren mal ganz anders beschrieben, wir man eine Rechenoperation nur auf gerade oder nur auf ungerade Zahlen anwendet.

Das heutige Problem ist ähnlich gelagert: Diesmal soll jedoch nur mit jeder zweiten, dritten oder x.ten Zeile in der Tabelle gerechnet werden.

Auch hier ist die SUMMENPRODUKT-Funktion wieder unser Freund.

Und so geht’s:

Weiterlesen »

Zählen und addieren mal ganz anders 11

 in Formeln und Funktionen / Logisch / Mathematik und Trigonometrie  Stichworte: ISTUNGERADE / REST / SUMMENPRODUKT Von:
Rechnen nur mit ungeraden (oder nur geraden) Zahlen: Ganz einfach, wenn man weiß, wie!
 

Heute gibt’s mal wieder ein paar Tipps aus der klassischen Excel-Formelküche. Es geht eigentlich um ganz einfache Fagen: Die Bestimmung der Anzahl von Werten in einer Liste und die Summe daraus. Wer jetzt gelangweilt gähnt und schon ein Fingerzucken über der linken Maustaste verspürt, für den habe ich noch eine kleine Zusatzinformation:

Gezählt und summiert werden sollen nämlich nur die geraden oder nur die ungeraden Werte in der Liste. Wenn du jetzt ein wenig ins Grübeln kommst, solltest du vielleicht doch weiterlesen…

Und so geht’s:

Weiterlesen »

Die Befreiung von Klammern und Schachteln 3

 in Formeln und Funktionen / Logisch  Stichworte: WENNS Von:
Mit der neuen WENNS-Funktion lässt sich manche Verschachtelung vermeiden.
 

Wie im letzten Artikel zu den verschiedenen Bedingungsfunktionen schon angekündigt, geht es heute um ein weiteres „Schmankerl“ in Excel beim Einsatz von Bedingungen.

Es geht um die neue Funktion WENNS.

Auch auf die Gefahr hin, dass sich jetzt gleich viele Leser ärgern:
Diese Funktion ist nur in der Excel-Version 2016 im Rahmen von Office 365 verfügbar. „Standalone“-Excel 2016 und Vorgängerversionen können damit leider nichts anfangen.

Das soll uns aber nicht daran hindern, trotzdem einen Blick auf diese interessante Funktion zu werfen.

Weiterlesen »

WENNS mal wieder ein bisschen mehr Excel sein darf… 18

 in Formeln und Funktionen / Mathematik und Trigonometrie / Statistik  Stichworte: ANZAHL / ANZAHL2 / MAX / MAXWENNS / MIN / MINWENNS / MITTELWERT / MITTELWERTWENN / MITTELWERTWENNS / SUMME / SUMMEWENN / SUMMEWENNS / ZÄHLENWENN / ZÄHLENWENNS Von:
Überblick: Bedingungsfunktionen in Excel (..WENN/..WENNS)
 

Die weit mehr als 450 Funktionen, die Excel in den aktuellen Versionen mitbringt, können Fluch und Segen zugleich sein. Einerseits gibt es kaum einen Bereich, für den es keine eingebaute Funktion gibt. Auf der anderen Seite ist es schwierig, den Überblick zu bewahren und die im Einzelfall benötigte Funktion überhaupt zu finden – geschweige denn zu wissen, ob es die Funktion überhaupt gibt.

Dabei schlummern einige echte Perlen in den Excel-Tiefen, die eigentlich fast jeder brauchen kann – aber nicht jeder kennt.

Der heutige Artikel gibt einen Überblick über die verschiedenen – wie ich sie nenne – Bedingungsfunktionen. Auch wenn du schon länger mit Excel arbeitest, wirst du in dieser Übersicht vielleicht noch ein paar neue Freunde finden…

Und los geht’s:

Weiterlesen »

4 Wege in Excel, um Texte zu verketten 34

 in Formeln und Funktionen / Text  Stichworte: TEXTKETTE / TEXTVERKETTEN / VERKETTEN Von:
Verschiedene Wege, mehrere Zellen miteinander zu verknüpfen.
 

Excel ist nicht nur gut im Umgang mit Zahlen, sondern bietet auch viele praktische Funktionen im Umgang mit Texten. Ein Teilbereich davon ist das Verketten von Texten oder das Verknüpfen von verschiedenen Zellen.

Wozu man so etwas braucht? Zum Beispiel, um aus den einzelnen Feldern einer Adressentabelle einen einzigen Datensatz zu bilden.

Im heutigen Artikel zeige ich dir 4 verschiedene Möglichkeiten, wie man in Excel Texte bzw. Zellen miteinander verketten kann.

Und so geht’s:

Weiterlesen »

In Excel mit Farben rechnen 18

 in Formeln und Funktionen / Mathematik und Trigonometrie  Stichworte: TEILERGEBNIS Von:
Mit einem kleinen Trick lässt sich in Excel auch mit Farben rechnen
 

Excel bietet bei den Autofiltern ja die ungemein praktische Möglichkeit, nach Text- oder Schriftfarbe zu filtern.

Nun stellt sich manch einer die Frage, ob man nicht auch auf Basis von Zellenfarben rechnen kann. Also zum Beispiel die Summe über alle rot eingefärbten Zellen oder die Anzahl aller gelben Zellen berechnen.

Das ist in Excel leider (oder zum Glück?) nicht vorgesehen. Wer jedoch partout nicht darauf verzichten will, kommt mit einem kleinen Trick doch auf das gewünschte Ergebnis.

Und so geht’s:

Weiterlesen »

Den Wert an einem Schnittpunkt finden (Variante 2) 3

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: BEREICH.VERSCHIEBEN / VERGLEICH Von:
Eine weitere Möglichkeit, den Wert eines Schnittpunkts in einer Tabelle auszugeben.
 

Vor ziemlich genau einem Jahr habe ich eine Methode vorgestellt, um mit Hilfe der INDIREKT-Funktion und dem sogenannten Schnittmengenoperator den Wert auszugeben, der sich am Schnittpunkt einer bestimmten Spalte und einer bestimmten Zeile befindet.

Die damals gezeigte Lösung ist zwar sehr elegant, hat aber ein paar kleine Nachteile, so dass sie nicht für alle Szenarien immer auch praktikabel ist.

Heute zeige ich eine weitere Möglichkeit, das Problem zu lösen.

Und so geht’s:

Weiterlesen »

Der Zufall als Assistent 2

 in Formeln und Funktionen / Mathematik und Trigonometrie / Statistik  Stichworte: ANZAHL / AUFRUNDEN / MITTELWERT / SUMME / WURZEL / ZÄHLENWENN / ZUFALLSZAHL Von:
Ein kreativer Einsatz der Excel-Funktion ZUFALLSZAHL
 

Heute kommt wieder einmal ein Gastautor zu Wort. Martin Fuchs, seines Zeichens Analyst und Controller und somit ein Parade-Excel-Anwender, hat sich einige Gedanken zur Funktion ZUFALLSZAHL() gemacht.

Er zeigt, wie man sich mit dieser und ein paar anderer Excel-Funktionen der Kreiszahl Pi nähern kann. Das dabei zur Veranschaulichung erstellte Diagramm wird auch die mathematischen Abstinenzler unter uns auf jeden Fall ansprechen.

Vielen Dank an Martin Fuchs für diesen schönen Beitrag!

Und los geht’s:

Weiterlesen »

Wer hat das letzte Wort in Excel? 17

 in Formeln und Funktionen / Text  Stichworte: FINDEN / LÄNGE / RECHTS / WECHSELN Von:
In Excel das letzte Wort in einem Text finden: Eine nur auf den ersten Blick einfache Aufgabe...
 

Für die Arbeit mit Texten und Textteilen bietet Excel einige sehr praktische Funktionen. So lassen sich mit den Funktionen LINKS() oder RECHTS() gezielt bestimmte Teile aus einem Text herauslösen und anschließend getrennt weiterverarbeiten.

Wie kann man nun z.B. aus einem Satz variabler Länge gezielt das letzte Wort herauslösen?

In Kenntnis der RECHTS-Funktion eine auf den ersten Blick trivial erscheinende Aufgabe, die es bei näherer Betrachtung aber in sich hat. Wozu man das brauchen sollte? Um z.B. in einem kombinierten Namensfeld nur den Nachnamen zu isolieren.

Der folgende Artikel stellt zwei mögliche Lösungen vor.

Weiterlesen »

Excel-Tricks mit Steuerelementen (Teil 2) 23

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: BEREICH.VERSCHIEBEN / Formulare / INDEX / Steuerelemente / VERGLEICH Von:
Im Teil 2 der Artikelserie geht es um das Kombinationsfeld aus den ActiveX-Steuerelementen.
 

In der letzten Woche habe ich hier eine Möglichkeit vorgestellt, mit nur einem einzigen Dropdown-Feld eine ganze Liste in Excel zu befüllen.

Für den Einstieg ganz nett, aber noch nicht so richtig komfortabel.

Heute greife ich das Thema nochmal auf und zeige Dir, wie sich mit wenig Aufwand noch deutlich mehr aus den Steuerfeldern herausholen lässt.

Und so geht’s:

Weiterlesen »

Excel-Tricks mit Steuerelementen (Teil 1) 34

 in Formeln und Funktionen / Nachschlagen und Verweisen / Statistik  Stichworte: ANZAHL2 / BEREICH.VERSCHIEBEN / Formulare / Steuerelemente Von:
Mit einem kleinen Trick lässt sich das Ergebnis eines Steuerelements flexibler ausgeben
 

Excel eignet sich hervorragend dafür, eigene Formulare zu gestalten. Über eine Vielzahl von vordefinierten Steuerelementen lassen sich Auswahllisten, Checkboxen, Optionsfelder und einiges mehr realisieren.

Üblicherweise wird dabei mit diesen Steuerelemente eine fixe Zelle verknüpft, in der das ausgewählte Ergebnis eingetragen wird.

Im heutigen Artikel zeige ich dir am Beispiel des Kombinationsfeldes (= Dropdown-Feld), wie man den Ausgabebereich für solche Steuerelemente ein wenig flexibler gestalten kann.

Und so geht’s:

Weiterlesen »

Dropdown-Liste Spezial 47

 in Daten- und Analysetools / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: BEREICH.VERSCHIEBEN / Datenüberprüfung / Formatierte Tabelle / Pivot-Tabelle Von:
Mit ein paar kleinen Tricks ist die Pflege von Dropdown-Listen ein Kinderspiel.
 

Auswahllisten oder Dropdown-Listen sind in Excel extrem beliebt. Sie erleichtern dem Anwender die Dateneingabe, verhindern Fehler und lassen deine Tabelle einfach professionell erscheinen.

Es gibt nur ein kleines Ärgernis: Wenn neue Einträge in der Auswahlliste dazukommen sollen, ist meistens Handarbeit angesagt.

Heute zeige ich dir, wie man eine Dropdown-Liste erstellt, die

  • automatisch neue Einträge aufnimmt
  • alphabetisch sortiert ist
  • keine doppelten Einträge enthält

Und das alles (fast) vollautomatisch!

Und so geht’s:

Weiterlesen »