Datenüberprüfung: Ein Datum zulassen

Wie es der Titel schon sagt. Diesmal soll mit der Datenüberprüfung sichergestellt werden, dass nur die Eingabe eines Datums möglich ist.

Aktiviere dazu eine Zelle deiner Wahl und rufe die Datenüberprüfung im Menü Daten auf.

Unter Daten kannst du jetzt aus acht Vorgaben auswählen.

DatenüberprüfungDat1

Angenommen, du hast „zwischen“ gewählt, ist nur die Eingabe zwischen dem Anfangs- und dem Enddatum möglich. Beides musst du in den folgenden Textfeldern eintragen.

Die Maske füllst du z.B. so aus:

DatenüberprüfungDat2

Mit dieser Einstellung kannst du ein Datum in allen Datumsformaten, auch benutzerdefiniert, eingeben.

z.B.     DatenüberprüfungDat3

Eingaben außerhalb des eingestellten Bereichs werden mit einer Fehleranzeige beantwortet.

Alle anderen möglichen Masken-Einstellungen unter Daten sind selbst erklärend.

Was du noch wissen solltest. Hast du unter Daten die Einstellung „ungleich“ und das Datum 1.2.17 gewählt, ist es nicht gestattet, das Datum „1.2.17“ einzugeben.

Du kannst aber jedes andere Datum, jede andere Zahl oder eine Formel eingeben. Das wird erlaubt.

Aber: Texteingaben werden nicht akzeptiert.

Wie du Diagramme bedingt formatierst

Du arbeitest gern mit der bedingten Formatierung von Excel? Mit geht es genauso.

Du erstellst auch gern Diagramme in Excel, weil visuell viele Fakten noch schneller zu erfassen sind? Dann haben wir schon wieder etwas gemeinsam.

Und hast du auch schon einmal gedacht, wie praktisch es wäre, wenn du deine Diagramme bedingt formatieren könntest? Mir ist dieser Gedanke neulich gekommen.

Natürlich wissen wir beide, dass Diagramme nicht direkt bedingt formatiert werden können. Direkt nicht, aber indirekt schon, mit einem kleinen Trick.

Wie das geht, will ich dir hier zeigen.

Weiterlesen „Wie du Diagramme bedingt formatierst“

29 empfehlenswerte Artikel zu SVERWEIS / VLOOKUP

SVERWEIS bzw. VLOOKUP gehören zweifelsfrei zu den bekanntesten, aber auch beliebtesten Funktionen in Excel.

Selbst in den sozialen Medien, wie Twitter, Facebook oder Google+ vergeht kaum ein Tag, an dem nicht ein Artikel dazu promotet wird.

Du möchtest etwas mehr über die Funktion erfahren, als es dir die Excel-Hilfe offeriert? Kein Problem. Es gibt unzählige Suchergebnisse bei Google, wenn du nach „sverweis“ suchst. Google meint, es wären ungefähr 209.000 Einträge.

Weiterlesen „29 empfehlenswerte Artikel zu SVERWEIS / VLOOKUP“

VBA-Steuerelemente: Die TextBox

Die TextBox kann als Steuerelement sowohl im Arbeitsblatt als auch in einer UserForm verwendet werden.

In diesem Beitrag geht es um die Verwendung im Arbeitsblatt.

Aus Anwendungsprogrammen kennst du sicherlich Fälle, in denen TextBoxen zu finden sind. Z.B. kann es in einem Finanzbuchhaltungsprogramm eine Kontoauskunft geben.

In einer TextBox gibst du die Konto-Nr. ein und in einer zweiten TextBox wird der Konto-Name ausgegeben.

Hierzu hast du dir eine kleine Liste mit ein paar Konten gesucht. [1]

textbox1

In eine der folgenden TextBoxen gibst du manuelle eine Konto-Nr. ein. Mit einem Makro willst du den dazugehörigen Konto-Namen finden und in die zweite TextBox eintragen.

Sub TextBox1()
Dim strKonto As String
Dim rngFund As Range
strKonto=Tabelle1.TextBox3.Value
Set rngFund=Range(„B4:B27“).Find(strKonto,LookIn:=xlValues)
Tabelle1.TextBox4.Text=rngFund.Offset(0,1)
End Sub

 Was geschieht?

TextBox1 enthält die Information „Konto-Nr.“, TextBox 2 enthält „Bezeichnung“.

Den Inhalt von TextBox3 (deine Eingabe) deklarierst du als Variable „strKonto“. Die Fundstelle in obiger Liste deklarierst du als Variable „rngFund“. Der Variablen „strKonto“ weist du den wert aus TextBox1 zu.

Jetzt befiehlst du, dass im Bereich B4:B27 der Wert von strKonto gefunden werden soll. Diese Zelle deklarierst du als „rngFund“

In TextBox4 soll letztlich der Wert eingetragen werden, den du, ausgehend von rngFund und mittels der Funktion Offset (=BEREICH.VERSCHIEBEN) in der Spalte rechts daneben (C4:C27) findest.

textbox2

Das Makro hat richtig den zugehörigen Konto-Namen gefunden und eingetragen.

Die Inhalte der TextBoxen (3 und 4) kannst du hiermit löschen:

Sub TextBox2()
Tabelle1.TextBox3.Text=““
Tabelle1.TextBox4.Text=““
End Sub

Für den umgekeh´rten Fall, Eingabe des Namens und Suche der Nummer, verwendest du z.B. dieses Makro:

Sub TextBox3()
Dim strName As String
Dim rngFund As Range
strName=Tabelle1.TextBox4.Value
Set rngFund=Range(„C4:C27“).Find(strName,LookIn:=xlValues)
Tabelle1.TextBox3.Text=rngFund.Offset(0,-1)
End Sub

Wie kann es nun weitergehen?

Für die Kontoauskunft gibt es nun die Möglichkeiten, sich den Saldo des Kontos oder die Kontobewegungen für ein Geschäftsjahr und/oder einer Periodenauswahl anzeigen zu lassen. Dem könnte sich dann noch eine Druckvorschau  und der Ausdruck anschließen.

Dazu müsstest du ein Makro schreiben, das dies alles machen kann und dabei auf die Inhalte der TextBoxen 3 und 4 zugreift.

Das aber ist eine andere Geschichte, die in diesem Beitrag nicht mehr eingebaut werden kann.

Quellen:

[1] DATEV-Standardkontenrahmen (SKR) 04, 2015,

http://www.werz-kraft-vogt.de/fileadmin/Dateien/Dateien(SKR 04.pdf

Runden oder nicht runden?

Wer kennt das Problem nicht? In einem Bericht werden die Zahlenwerte mit zwei Nachkommastellen dargestellt. Im Bericht selbst werden aber Prozentwerte berechnet oder Konstanten wie die Kreiszahl Pi oder die Eulersche Zahl e bei Rechenoperationen verwendet. Die Ergebnisse haben dann in der Regel mehr Nachkommastellen als zwei.

Die gezeigten Endergebnisse sind ohne Zweifel richtig errechnet. Aber wenn ein Kollege oder ein Vorgesetzter horizontal und vertikal die sichtbaren Ergebnisse mit dem Taschenrechner nachrechnen, passt irgendetwas meistens nicht.

Ein Beispiel soll das verdeutlichen:

runden1

Formeln:

D6   =WENN(UND(C6>180000;C6<200000);0,03; WENN(UND(C6>200000;C6<220000);0,05; WENN(C6>220000;0,07;““)))

E6   =WENN(D6<>““;C6*D6;““)

E13   =SUMME(E6:E12)

Dabei gelten diese Regeln:

Umsatz >220000   Provision 7%
Umsatz >200000   Provision 5%
Umsatz >180000   Provision 3%

Je nachdem, welche der Regeln auf einen Verkäufer zutrifft, wird der erreichte Umsatz mit dem Prozentsatz multipliziert und die Provision mit zwei Nachkommastellen ausgewiesen, eben so, wie dann die Auszahlung erfolgen wird.

Nun verändere das Zahlenformat für die Provisionen derart, dass vier Nachkommastellen gezeigt werden.

runden3

Zu sehen ist, dass jeweils eine dritte Nachkommastelle errechnet wurde.

Addierst du die Werte so, wie sie zu sehen sind, also 16.895,515+5.957,364 usw., erhältst du das Ergebnis 65.689,971, so wie es jetzt in der Tabelle zu sehen ist.

Addierst du aber die Werte so, wie du sie in der ersten Tabelle siehst, also 16.895,52+5.957,36 usw., lautet die Summe 65.689,98. In E13 steht aber 65.689,97.

Fest steht, dass Werte gerundet werden müssen, aber welche?

Es könnten die einzelnen Positionen oder die Summe der Provisionen gerundet werden.

Zuerst rundest du die einzelnen Provisionen:

runden4

Die Formel in E6 hast du geändert in:

=WENN(D6<>““;RUNDEN(C6*D6;2);““)

Das Zahlenformat kann jetzt wieder auf zwei Nachkommastellen reduziert werden und das Ergebnis 65.689,98 entspricht nun auch dem Additionsergebnis aus den sichtbaren Werten in der ersten Tabelle.

Du willst noch die zweite Variante beim Runden ausführen und nur die Summe runden.

runden5

Die Formeln lauten:

In E89   =WENN(D6<>“;C6*D6;““)

In E13   =RUNDEN(SUMME(E6:E12);2)

Zu sehen ist, dass in diesem Beispiel eben der Wert erscheint, der schon ursprünglich zu sehen war. Er ist nicht exakt.

Fazit:

Wird mit Prozenten oder Konstanten wie MWSt-Satz, Pi oder e gerechnet, sollten sinnvollerweise die unmittelbaren Einzelergebnisse auf die Anzahl Nachkommastellen gerundet werden, die sichtbar sein soll.

Das Argument „Anzahl_Stellen“ in der Rundungsformel muss der Anzahl der Nachkommastellen gemäß Zahlenformat entsprechen. Die Regel ist, dort zu runden, wo die Abweichungen entstehen.

Dies ist nun die korrekte Tabelle:

runden6

Daten in Excel konsolidieren

Ein kleines Unternehmen soll drei Standorte mit einer unterschiedlichen Anzahl an Mitarbeitern haben. Zum Teil arbeiten Mitarbeiter auch wechselnd an verschiedenen Standorten.

Die Zentrale bekommt monatlich die geleisteten Arbeitsstunden mit gleichartig aufgebauten Arbeitsmappen gemeldet. Die Daten sind in den Arbeitsmappen „Ort1.xlsx“, „Ort2.xlsx“ und „Ort3.xlsx“ enthalten.

ort1

ort2

ort3

Die Arbeitsmappen enthalten jeweils alle Stunden des bis zum Berichtsmonat aufgelaufenen Jahres.

Aufgabe ist es nun, die Einzelberichte zu einem Bericht zusammenzufassen.

Du könntest dir nun eine Tabelle nach diesem Muster aufbauen und die Stunden der Mitarbeiter addieren. Das kann bei einer großen Anzahl an Mitarbeitern schwierig werden, da nicht alle Mitarbeiter in allen Standorten arbeiten und die Einzelberichte nicht sortiert sein müssen.

Hier hilft dir ein Datentool, nämlich „Konsolidieren“.

Was ist unter „Konsolidieren zu verstehen?

Einfach gesagt werden beim Konsolidieren mehrere Einzelgrößen zu einer Gesamtgröße zusammengefasst bzw. verdichtet.

Wie musst du dazu vorgehen?

Schau dir die Meldungen der drei Standorte an. Sie haben eine unterschiedliche Zeilenzahl und die Namen der Mitarbeiter sind nicht identisch.

Gehe zuerst in die Arbeitsmappe, die die verdichteten Informationen aufnehmen soll. Klicke auf die künftige linke obere Zelle (z.B. B5), gehe ins Menü Daten/Datentools und rufe „Konsolidieren“ auf. Es erscheint diese Maske:

konsolidieren1

Unter Funktion findest du alle möglichen Funktionen, entscheide dich hier für Summe.

Klicke rechts neben Verweis auf den Pfeil nach oben Markiere in der Mappe „Ort1.xlsx“ den Bereich B4:E9 und klicke dann auf hinzufügen. Markiere anschließend die gleichen Bereiche in „Ort2.xlsx“ und „Ort3.xlsx“. Die markierten Bereiche müssen in allen Datenmappen gleich groß sein.

Setze abschließend noch die Häkchen bei „Oberster Zeile“, „Linker Spalte“ und „Verknüpfungen mit Quelldaten“. Mit „OK“ schließt du den Prozess ab und hast die konsolidierten Daten als Tabelle vorliegen.

konsolidieren2

Du siehst am linken Tabellenrand, dass gleichzeitig auch noch eine Gruppierung erfolgt ist. Dadurch wird es dir möglich, dir Zwischenergebnisse anzeigen zu lassen. Klicke dazu auf die Gliederungsebene 2 (links oben in der Ecke). Nun sieht die Tabelle so aus:

konsolidieren3

Darin sind soweit alle Daten, die mit den einzelnen Berichtsmappen gemeldet wurden. Du siehst, dass z.B. Susanne Wilke an zwei Orten gearbeitet hat. Du siehst, wie viele Stunden jeweils auf die einzelnen Monate entfallen. Du hast Summen je Ort und Monat und die gesamten Stunden je Monat. Damit ist das Ziele, drei Tabellen zu konsolidieren, bereits erreicht.

Aber, die Tabelle gefällt dir so nicht. Formatiere sie deshalb über das Menü Start/Formatvorlagen als Tabelle. Vergiss nicht, den Haken bei „Tabelle hat Überschriften“ zu setzen. So sieht es dann aus:

Gliederungsebene 1

konsolidieren4

Gliederungsebene 2

konsolidieren5

Nützlich wären jetzt noch Zeilensummen. Klicke dazu in die Märzspalte und rufe mit der rechten Maustaste „Zeilen/Spalten einfügen / Tabellenspalte nach rechts“ auf. Ändere die Überschrift auf „Summe“. Schreibe in Gliederungsansicht 2 jetzt in die erste Summenzelle z.B. die Formel:

 =SUMME(Tabelle1[@[Jan]:[Mrz]])

Mit dem Klick auf Enter steht diese Formel in allen Zeilen.

Überschreibe noch „Spalte 1“ mit „Mitarbeiter“, „Spalte 2“ mit „Ort“ und passe die Spaltenbreiten an. Formatiere die Ergebniszeile evtl. noch fett und fülle sie mit einer anderen Farbe. Das Ergebnis könnte dann so aussehen:

konsolidieren6

Bei Änderungen in den Meldemappen („Ort1.xlsx“ usw.) ändern sich auch die Daten in der konsolidierten Tabelle.

Datenüberprüfung: Liste zulassen

In Punkto „Datenüberprüfung“ hast du bisher die Möglichkeiten „Jeden Wert“ und „Ganze Zahl“ zulassen kennen gelernt.

Die dritte Möglichkeit ist, eine „Liste“ zuzulassen.

Dazu muss natürlich zuerst einmal eine Liste vorhanden sein. Zwei Möglichkeiten zur Erinnerung:

Du schreibst die Inhalte der Liste, z.B. die zwölf Monate, untereinander und markierst den Bereich B5:B16.

liste1

Gehe nun im Menü Formeln auf Namen definieren und fülle die Maske wie folgt aus:

liste2

Nachdem nun die Liste vorhanden ist, markierst du z.B. die Zelle B45 deines Arbeitsblattes und gehst über Daten/Datentools auf die Datenüberprüfung.

Dort wählst du unter „Zulassen“ die „Liste“ aus und trägst unter „Quelle“ die gerade erstellte Liste ein, nämlich „Monate“.

liste3

Die zweite Möglichkeit, eine Liste anzulegen, ist, sie in der Datenüberprüfung direkt anzulegen.

Markiere z.B. die Zelle B72 und rufe über Daten/Datentools die Datenüberprüfung auf.

Lasse „Liste“ zu und schreibe als Quelle die Monate von Januar bis Dezember, ohne Gleichheitszeichen und getrennt mit Semikolon (ohne Leerzeichen).

liste4

Einen Namen trägt diese Liste nicht. Verwenden kannst du sie aber nur in dieser einen Zelle, da du nur diese eine Zelle aktiviert hast.

Sollen mehrere Zellen diese Gültigkeitskriterien erhalten, markiere alle diese Zellen und trage die Kriterien wie zuvor ein.

Möglich ist aber auch, die Zelle mit der entsprechenden Gültigkeit an eine andere Stelle zu kopieren.