Excel - dynamische Summe mit Bereich.verschieben

In der Praxis steht der Controller häufig vor dem Problem große Datenmengen zu verdichten und aufzubereiten. Der Controller möchte zum Beispiel für eine Auswertung den Umsatz in einem bestimmten Zeitraum für ein Produkt ermitteln. In diesem Tipp stellen wir Ihnen vor, wie Sie aus einer großen Datenmenge eine Summe für Daten ermitteln, die bestimmte Suchkriterien erfüllen.
1. Datengrundlage

In diesem Beispiel liegt dem Controller eine Umsatztabelle des aktuellen Jahres vor.  In dieser Tabelle werden fünf Produkte sowie deren Tagesumsätze des Unternehmens dargestellt. Die hierfür benötigten Daten stammen aus internen Systemen wie zum Beispiel der Fakturierung.

abb1-grunddaten-dyn-summe.jpg
Abb. 1 Datengrundlage

2. Suchmaske erstellen

Die Suchmaske für das Datum ist schnell erstellt. Im Prinzip benötigten Sie hierfür nur zwei leere Zellen. Um die Handhabung einfacher zu gestalten, wird empfohlen, die Suchmaske zu formatieren. Hier wurden die Eingabezellen hellgelb hinterlegt sowie ein Rahmen um die Bezeichnungen und Eingabefelder gezogen. Zusätzlich wurden die Eingabezellen mit einem Datumsformat versehen.

abb2-suchmaske-dyn-summe.jpg
Abb. 2 Suchmaske

3. Produktliste erzeugen

Um im späteren Tool möglichst bequem die Auswahl für das Produkt zu treffen, wird eine Liste der Produkte benötigt. Hierfür wird die Funktion MTRANS herangezogen. Selektieren Sie senkrecht so viele Zellen, bis Sie die Anzahl der Produkte erreicht haben. Im Anschluss geben Sie folgende Formel ein und bestätigen die Eingabe mit Strg + Shift  + Enter.

Formel: {=MTRANS(B3:F3)}


abb3-produktliste-dyn-summe.jpg
Abb. 3 Produktliste

4. Produktauswahl erstellen

Das Auswahlfeld (Zelle I18) für die Produktauswahl wurde über eine Gültigkeitsprüfung eingefügt. Hierfür wählen Sie bei Excel 2003 im Menü Daten den Punkt Gültigkeit. In dem anschließenden Menü wählen Sie Liste und den Zellbereich der Produktliste. In Excel 2010 können Sie eine Gültigkeit einfügen, indem Sie in das Register Daten wechseln und Datenüberprüfung wählen.

Das fertige Feld kann im Anschluss mit wenigen Formatierungen wie folgt aussehen.

abb4-produktauswahl-dyn-summe.jpg
Abb. 4 Produktauswahlbereich
 
5. Erstellung der Formel zur Bestimmung des Umsatzes

Zur Bestimmung des Umsatzes wird eine SUMME-Funktion verwendet, welche mit VERGLEICH und BEREICH.VERSCHIEBEN kombiniert wird.

Formel: =SUMME(BEREICH.VERSCHIEBEN(A1;VERGLEICH(I4;A:A;0)-1;VERGLEICH(I18;3:3;0)-1;VERGLEICH(I5;A:A;0)-VERGLEICH(I4;A:A;0)+1;))

Dabei wird als Grundbezug die Zelle A1 genutzt. Durch den ersten Vergleich werden die Zeilen des Anfangsdatums bestimmt. Der mittlere Vergleich wird zur Bestimmung der Spalte des einzelnen Produkts verwendet. Die letzten beiden Vergleiche berechnen die Länge des Zeitraums.

abb5-ergebnisfeld-dyn-summe.jpg
Abb. 5 Ergebnistabelle


letzte Änderung R. am 04.08.2017
Erstellt: 24.08.2011 15:51:07
Geändert: 04.08.2017 13:39:25
Autor(en):  Redaktion CP

Drucken RSS
Literaturhinweise
Excel-Tools
Weitere Fachbeiträge zum Thema
Kommentar zum Fachbeitrag abgeben

08.08.2016 05:43:37 - Gast

Guten Tag, worin liegt aus Ihrer Sicht der entscheidende Vorteile gegenüber der Verwendung von SUMMEWENN(S)? Viele Grüße K. Linke
[ Zitieren | Name ]
Diskussion im Forum >>
Ihr Name:
Ihre E-Mail:
Smileys
Lachen  happy77  happy73 
Mr. Green  Skeptisch  Idee 
Frage  Ausrufezeichen  Zwinkern 
Böse  Grinsen  Traurig 
Überrascht  Cool  Kuss 
Verlegen  Weinen  schlafen 
denken  lesen  klatschen 
Schutz vor automatischen Mitteilungen:
Schutz vor automatischen Mitteilungen

Symbole auf dem Bild:

 

Anzeige

Controlling- Newsletter

Neben aktuellen Neuigkeiten für Controller und neueingegangene Fachartikel, informieren wir Sie über interessante Literaturtipps, Tagungen , Seminarangebote und stellen Ihnen einzelne Software- Produkte im Detail vor.
zur Newsletter-Anmeldung >>
Anzeige
Anzeige

Community

Community_Home.jpg






Nutzen Sie kostenfrei das Forum für Controller und und diskutieren ihre Fragen zu Controlling-Themen.
Anzeige

Reports strukturieren und dynamisieren

285x120_Reporting.png
Sie arbeiten täglich mit Microsoft Excel als Tool zur Analyse und zur Aufbereitung großer Datenmengen? Lernen Sie Lösungsschritte und Arbeitstechniken kennen, mit denen Sie Ihre Daten effizient und übersichtlich darstellen. Jetzt lesen!
Anzeige
Excel-Vorlage: RS Controlling System
Anzeige

Experten-Tipp

diagramm-pm-stephen-rees.jpgWollen Sie ihre im Unternehmen eingesetzten Excel-Lösungen anpassen oder erweitern? Unser Excel-Experte berät und untersützt Sie zu den Produkten der RS-Reihe oder bei der Anpassung und Erweiterung ihrer eigener Excel-Lösungen. Mehr Infos >>
Anzeige

Excel-Vorlagen

 Investitionsrechnung Excel.jpg
Berechnen Sie die Rentabilität, Kapitalwert oder Armortisationszeit Ihrer Investitionen mit
Excel-Vorlagen für Investitionsrechnung
. Eine Auswahl finden Sie hier >>
Anzeige

Excel-Tool-Beratung und Erstellung

Kein passendes Excel-Tool dabei?

Balken.jpgGern erstellen wir Ihnen Ihr Excel- Tool nach Ihren Wünschen und Vorgaben. Bitte lassen Sie sich ein Angebot von uns erstellen.
Anzeige
lexware-Logo-190px.jpg
Die einfache kaufmännische Komplettlösung für Freiberufler und Kleinbetriebe.
Einer für alle, alle für einen: Perfekt aufeinander abgestimmt arbeiten bei Lexware financial office gleich drei Programme Hand in Hand: Lexware buchhalter, Lexware faktura+auftrag und Lexware lohn+gehalt. 4 Wochen kostenfrei testen >>
Anzeige

Web-Tipp

RWP_Logo.jpg
Rechnungswesen-Portal.de: Zahlreiche Fachbeiträge und News zum Thema Rechnungswesen und Steuern. Umfangreiche Marktüber- sichten für kaufmännische Software, Seminare speziell für die Weiter- bildung von Buchhaltern und ein Forum runden das Angebot ab.
Anzeige

Web-TippVermieter1x1_logo_282px.jpg 

Vermieter1x1.de - Das Internetportal für Vermieter und Immobilien-Manager. Zahlreiche Fachbeiträge und News zum Thema Vernieten und Verwalten von Immobilien. Umfangreiche Marktübersichten für kaufmännische Software, Seminare  und Tagungen runden das Angebot ab.
Anzeige

Webtipp

Sie suchen eine Software für Ihr Unternehmen, z.B. eine Shopsoftware, Newsletter-Software oder ein ERP-System? Im Softwareverzeichnis auf findsoftware.de finden Sie einen Marktüberblick, Vergleichsstudien und ein Forum zum Austausch mit anderen Anwendern.
Anzeige

RS Controlling-System

RS-Controlling-System.jpg Das RS- Controlling-System bietet Planung, Ist- Auswertung und Forecasting in einem Excel-System. Monatliche und mehrjährige Planung. Ganz einfach Ist-Zahlen mit Hilfe von Plan/Ist-Vergleichen, Kennzahlen und Kapitalfluss- rechnung analysieren.  Alle Funktionen im Überblick >>
Anzeige

Excel Tool

Anlagenverwaltung in Excel: Das Inventar ist nach Bilanzpositionen untergliedert, Abschreibungen und Rest- Buchwerte ihrer Anlagegüter werden automatisch berechnet. Eine AfA- Tabelle, mit der Sie die Nutzungsdauer ihrer Anlagegüter ermitteln können, ist integriert. mehr Informationen >>
Anzeige

Bilanzbuchhalter werden !

Bilanzbuchhalter/in IHK ist die beliebteste Aufstiegsfortbildung für kaufmännische Praktiker aus allen Branchen.  Werden Sie mit einem sgd-Fernstudium in 21 Monaten Bilanzbuchhalter mit IHK-Abschluss. Nutzen Sie jetzt Ihre Chance >>
Anzeige

Fachbeitrag hier verkaufen?

Schreiben_pm_Elena_Elisseeva.jpg 
Sie haben einen Fachbeitrag, Checkliste oder eine Berichtsvorlage für Controller erstellt und möchten diese(n) zum Kauf anbieten? Über unsere Fachportale bieten wir Ihnen diese Möglichkeit.  Mehr Infos hier >>