Endtermine exakt berechnen: Vertragscontrolling mit EDATUM

Vorgestern wurde ich im Excel-Kurs in Berlin gefragt, wie sich ein Vertragsende berechnen lässt, wenn folgende Regeln gelten:

  • Das Vertragsende liegt genau 2 Jahre minus 1 Tag nach Vertragsbeginn – beim Beginn 15.06.2012 wäre das der 14.06.2014.
  • Fällt das Vertragsende auf ein Wochenende, muss der Termin auf den Freitag davor datiert werden.

Die Funktion EDATUM ist hier der Schlüssel zur Lösung. Sie wird kombiniert mit WOCHENTAG und WENN.

Weiterlesen

Starke Konkurrenz für Pivot – Wichtige Projekt-Kennzahlen auf einen Blick in einem Cockpit mit SUMMEWENNS & Jokern

Diese Woche ist Teil 2 einer Beitragsserie erschienen, die Alternativen zu Pivot-Tabellen vorstellt. Demonstriert wird dies am Beispiel der Auswertung von Projektdaten. Die Ergebnisse werden in einem Cockpit dargestellt.

Gezeigt wird, wie sich komplexe Abfragen aufbauen lassen, die mehrere Abfragekriterien miteinander kombinieren. Beispielsweise mit der seit Excel 2007 hinzugekommenen Funktion SUMMEWENNS. Hier ein Auszug aus dem Beitrag, der im Projekt Magazin (Ausgabe 17-2012) veröffentlicht wurde.

Vorschau auf das Auswertungs-Cockpit für Projekte

Bild 1: Blick auf das fertige Cockpit zur Projektauswertung – erstellt mit den Formeln SUMMEWENN, SUMMEWENNS, MITTELWERTWENNS und SUMMENPRODUKT

Weiterlesen

Excel 2010: Listen lassen sich auch nach Farbe sortieren

Nicht immer sollen Listen nach Texten oder Zahlen sortiert werden. Manchmal sind einfach nur bestimmte Zellen farbig markiert – beispielsweise Kosten, die über oder unter einem Grenzwert liegen. Um solche Einträge mit farbigen Markierungen (siehe unten gezeigte Tabelle) ganz oben anzuordnen, wird die Tabelle mit den folgenden Mausklicks ganz einfach nach Farben sortiert.

Die unsortierte Liste mit farbigen Markierungen

Bild 1: Die Liste mit farbig markierten Zellen soll nach Farben sortiert werden

Weiterlesen

Beitrag im Projekt Magazin 16/2012: Starke Konkurrenz für Pivot in Excel – Wichtige Projekt-Kennzahlen auf einen Blick Teil 1

Zum Projekt Magazin

Wenn es darum geht, die Auswertung umfangreicher Daten in möglichst kompakter Form darzustellen, denken die meisten zuerst an Pivot-Tabellen. Doch es gibt auch andere Techniken, mit denen sich Projektdaten analysieren und wichtige Kennzahlen übersichtlich anordnen lassen – zum Teil sogar flexibler als bei Pivot-Tabellen.
Erfahren Sie, wie Sie in Excel 2007 und 2010 wichtige Kennzahlen aus einer Vielzahl von Projekten gewinnen und kompakt in einem Cockpit darstellen, indem Sie die Funktionen ZÄHLENWENN(S), SUMMEWENN(S), MITTELWERTWENN(S) sowie SUMMENPRODUKT einsetzen.
In Teil 1 wird die Anzahl der Projekte nach deren Status aufgeschlüsselt. Außerdem werden die Kosten nach Kostenstelle oder Kostenstellenbereich aufsummiert.

Hier geht’s zu dem Beitrag im Projekt Magazin

Excel 2010: Flexible Statusanzeige mit Harvey Balls

Ob Projektübersicht, Umsatzauswertung oder Kostenaufstellung – eine Anzeige, die den aktuellen Status wiedergibt, ist oft wünschenswert. Excel bietet mit den Symbolsätzen der bedingten Formatierung gute Vorlagen für Status- oder Fortschrittsanzeigen – beispielsweise den Symbolsatz 5 Viertel – auch als Harvey Balls bekannt.

Allerdings hat der Symbolsatz drei Nachteile. Wie die sich beseitigen lassen, zeige ich in Kapitel 8 des eben bei Microsoft Press erschienenen Buches  Keine Angst vor Excel!.

Bild 1 zeigt eine Vorschau auf die Statusanzeige für den Projektfortschritt mit fünf unterschiedlichen Symbolen.

Flexible Statusanzeige mit Harvey Balls

Bild 1: Informative Statusanzeige mit Harvey Balls

Weiterlesen

Access 2010: Wie aus langen Listen mittels Kreuztabelle informative Jahres-, Monats- oder Quartalsvergleiche werden

Sind in Excel Daten für ein weiteres Jahr einzugeben, wird eine neue Spalte hinzugefügt. In Access läuft das flexibler. Sollen beispielsweise jährliche Mitgliedsbeiträge oder Umsätze pro Monat oder Reisekosten erfasst werden, wird eine Detailtabelle angelegt, in der jeder Eintrag ein eigener Datensatz ist.

Der Vorteil: Die Detailtabelle ist flexibel, denn ohne Änderung ihrer Struktur können beliebig viele Beiträge erfasst werden.
Der Nachteil: Die Detailtabelle ist weder übersichtlich, noch informativ. Beispielsweise wenn für jedes Mitglied die Beiträge der letzten drei Jahre oder die Umsätze der letzten sechs Monate oder die Reisekosten über alle vier Quartale zu vergleichen sind.

In Excel wäre eine Pivot-Tabelle das geeignete Werkzeug, um eine informative Auswertung aufzubauen. In Access sorgt eine Kreuztabelle für den Überblick.

Weiterlesen

Access-VBA: Das Aktualisieren von berechneten Steuerelementen in Formularen per Marke erledigen

Kürzlich stand ich in einem Kundenprojekt vor folgender Frage:
Wie kann ich in einem ungebundenen Access-Formular berechnete Steuerelemente bei Eintreten eines Ereignisses mit minimalem Aufwand aktualisieren?

Das Problem: In ungebundenen Formularen lässt sich das Neuberechnen der Steuerelemente nicht über die Formularaktualisierung steuern. Für jedes berechnete Steuerelement muss eine eigene REQUERY-Anweisung erfolgen.
Das kann allerdings recht mühsam und zeitraubend sein, wenn wie in Bild 1 das Formular eine ganze Reihe von berechneten Steuerelementen besitzt.

Das Aktualisieren von berechneten Steuerelementen per Marke regeln

Bild 1: Ungebundenes Formular mit zahlreichen berechneten Steuerelementen

Wenn im obigen Beispiel alle berechneten Steuerelemente nach Aktualisierung des Kombinationsfeldes Fiscal Year aktualisiert werden sollen,  wären folgende Schritte nötig:

  1. Vergeben eines eigenen Namens für jedes der Steuerelemente, um den VBA-Code leserlich zu halten
  2. Erstellen einer REQUERY-Anweisung für jedes der Steuerelemente

Mit einem kleinen Trick lässt sich diese zeitraubende Arbeit viel schneller erledigen.

Weiterlesen

Excel-VBA: Per Makro chemische Formeln leichter erfassen (Teil 1)

Wie lassen sich in Excel alle Zahlen in einer chemischen Formel mühelos tiefstellen?

Word besitzt in der Registerkarte Start dafür das Symbol Tiefgestellt oder auch die Tastenkombination STRG>+#. In Excel lässt sich dieses Problem über das Dialogfeld Zahlen formatieren, im Register Schrift über den Effekt Tiefgestellt lösen.

Aber das kann ziemlich mühsam und zeitraubend sein, wenn wie im folgenden Beispiel der Tiefstellvorgang gleich mehrfach zu wiederholen ist. Denn fatalerweise lassen sich nicht – wie in Word – mehrere nicht zusammenhängende Zeichen markieren.

C7H4N2O7

Ein kleines Makro nimmt die Arbeit des Tiefstellens ab

Weiterlesen

Excel 2010: Mit Pfeil-Symbolen einen Trend bildhaft darstellen

Wie lässt sich der Vergleich zwischen zwei Werten mit einem Symbol so darstellen, dass ein Trend deutlich wird?

Mit einer Hilfsspalte und der Bedingten Formatierung ist dies schnell erledigt.

Eine Hilfsspalte, die den Wert für die Trenddarstellung liefert

Angenommen, in A1 steht der Ausgangswert und in A2 der Wert, für den ein Vergleich gewünscht wird, dann kann in der Hilfsspalte B in der Zelle B2 folgende Formel stehen:

=WENN(A2>A1;1;WENN(A2=A1;0;-1))

In B2 ergibt sich also

  • eine 1, wenn A2 größer als A1,
  • eine 0, wenn A2 gleich A1 und
  • eine -1, wenn A2 kleiner als A1.

Anhand der drei möglichen Werte 1, 0 und -1 lässt sich dann in B2 (also direkt NEBEN A2) ein passendes Symbol platzieren. Hier die Vorschau, in der alle drei Fälle zu sehen sind:

 

 

Weiterlesen

Excel 2010: Bestellungen optimieren mit Rundungsfunktionen: AUFRUNDEN oder OBERGRENZE – welche ist besser?

Die Arbeit am Buch “Keine Angst vor Excel” steht kurz vor dem Abschluss – ab Mitte August 2012 ist es auf dem Markt verfügbar. Hier ein Einblick in Kapitel 7, in dem es um den richtigen Einsatz der Rundungsfunktionen geht.
Mehr zum Buch erfahren
Am Beispiel der Bevorratung mit Schrauben wird demonstriert, wie sich die jeweils passende Bestellmenge ermitteln lässt. Um zu sichern, dass stets die Mindestmenge der benötigten Schrauben bestellt wird, wird auf das nächstgrößere Vielfache der Liefereinheit aufgerundet.
Für solche Berechnungen stellt Excel die Funktionen AUFRUNDEN und OBERGRENZE bereit. Doch welche der beiden Rundungsfunktion eignet sich besser zur Bestelloptimierung: AUFRUNDEN oder OBERGRENZE?

Weiterlesen

Beiträge durchblättern:  << 1 2 3 4 5 6 7 8 9 >>