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“

Advertisements

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