Gerhard Pundt - Clevercalcul

VBA-Methoden: Arbeitsblätter zählen, Anzahl festlegen

Wie viele Arbeitsblätter hat eine Arbeitsmappe?

Befindest du dich in einer geöffneten Arbeitsmappe, kannst du das sofort sehen, wenn sich die Anzahl auf ein überschaubares Maß beschränkt.

Geht es aber um 95 oder 134 Arbeitsblätter, müsstest du zählen.

Das aber kannst du auch durch ein kleines Makro erledigen lassen.

Sub ArbBlattZählen1()
Dim intZahl As Integer
intZahl=ActiveWorkbook.Sheets.Count
MsgBox intZahl
End Sub

So sieht die Antwort aus. Bestätige sie mit „OK“.

Lässt sich die Anzahl der Arbeitsblätter für eine andere, geöffnete, Mappe ermitteln?

Sub ArbBlattZählen2()
Dim intZahl As Integer
Workbooks(„VBA_Datei öffnen.xlsm“).Activate
intZahl=ActiveWorkbook.Sheets.Count
MsgBox intZahl
End Sub

Hier gibt die MsgBox z.B. die Anzahl „3“ zurück.

Nun fragst du dich, ob du diese Abfrage auch an eine geschlossene Datei richten kannst.

Nachfolgendes Makro verwendet die GetObject-Funktion, die ich hier nicht weiter erläutere. Nur soviel, GetObject wird für den Zugriff auf ein ActiveX-Object verwendet. Mit der Set-Anweisung wird dem Objekt die Objektvariable zugewiesen.

Sub ArbBlattZählen3()
Dim intZahl As Integer
Dim wb As Workbook
Set wb=GetObject(„C:\Excel\VBA\VBA_Arbeitsblätter Anzahl.xlsm“)
intZahl=wb.Sheets.Count
MsgBox intZahl
End Sub

Die MsgBox gibt hier die Anzahl „5“ zurück.

Anzahl Arbeitsblätter festlegen

Eine neue Excel-Arbeitsmappe enthält standardmäßig drei Arbeitsblätter. Dies ist in den Excel-Optionen unter „Allgemein“ so eingestellt.

Wenn du aber eine neue Mappe haben willst, die 12 Arbeitsblätter enthält, kannst du das mit einem Makro erledigen lassen.

Du fragst dazu zuerst die hinterlegte Anzahl der Arbeitsblätter für neue Arbeitsmappen ab.

Dim intBlatt As Integer
intBlatt=Application.SheetsInNewWorkbook

Dann legst du die Anzahl der Blätter für deine neue Mappe fest, in diesem Fall sollen es 12 sein.

Application.SheetsInNewWorkbook=12

Jetzt legst du eine neue Mappe an und gibst ihr einen Namen. Wie das geht, habe ich in meinem Beitrag „VBA-Methoden: Eine Datei neu erstellen und speichern als“ beschrieben:

Workbooks.Add
ActiveWorkbook.SaveAs Filename:=“C:\Excel\VBA\VBA_NeuDatei.xlsx“

Den Ablageort gibst du mit der Pfadangabe gleich mit an.

Du hast jetzt eine neue Arbeitsmappe mit 12 Arbeitsblättern.

Nun solltest du die Einstellung für die Anzahl der Blätter wieder auf den Standard setzen und die Datei speichern und schließen.

Application.SheetsInNewWorkbook=intBlatt
ActiveWorkbook.Save
ActiveWorkbook.Close

Das Makro gesamt:

Sub ArbBlätter()
Dim intBlatt As Integer
intBlatt=Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook=12
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=“C:\Excel\VBA\VBA_NeuDatei.xlsx“
Application.SheetsInNewWorkbook=intBlatt
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Natürlich kannst du den 12 Arbeitsblättern in deiner neuen Mappe gleich noch neue Namen geben, z.B.“Januar“ bis „Dezember“.

Füge dazu im einfachsten Weg diese Codezeilen in dein Makro vor den Anweisungen .Save und .Close ein:

ActiveWorkbook.Sheets(1).Name=“Januar“
ActiveWorkbook.Sheets(2).Name=“Februar“

usw. bis „Dezember“.

Summe unter Bedingungen bei nichtnumerischen Werten

Einleitung

Angeregt durch die Anfrage eines Lesers, die ich zunächst nicht beantworten konnte, ist dieser Beitrag entstanden.

In der Anfrage ging es darum, Flugzeiten zu addieren, wenn zwei Bedingungen in einer anderen Spalte eingehalten werden. Das Problem war, dass die Spalte mit den Flugzeiten auch nichtnumerische Werte enthalten sollten, z.B. in Klammern gesetzt oder mit Buchstaben versehen.

(4:00)

4:00 xz

Der Leser wollte das Problem mit der Funktion SUMMENPRODUKT lösen, hatte aber keinen Erfolg damit.

Die statischen Excel-Funktionen QUANTIL.INKL und QUANTIL.EXKL

In einem vorangegangenen Beitrag hast du etwas über die Funktion QUANTIL erfahren können. Mit der Excel-Version 2010 kamen zwei neue Funktionen dazu, QUANTIL.INKL und QUANTIL.EXKL.

Dieser Beitrag wird sich diesen Funktionen widmen und die Unterschiede aufzeigen.

Die statistische Excel-Funktion QUANTIL

Was sind Quantile?

Quantile sind Lageparameter einer Beobachtungsreihe. Sie bezeichnen Grenzwerte, wobei ein Teil der Reihe unterhalb und ein Teil oberhalb dieser Grenzwerte liegt.

Lageparameter? Damit bist du im Reich der deskriptiven Statistik angelangt. Dir ist bekannt, dass es in Excel eine Kategorie mit statistischen Funktionen gibt.

Bis einschließlich Excel 2007 gab es nur die Funktion QUANTIL. Ab Excel 2010 findest du zwei neue Ausgestaltungen, die Funktionen QUANTIL.INKL und QUANTIL.EXKL.

Mit der alten Funktion QUANTIL kann dennoch weiter gerechnet werden. Dieser Beitrag beschäftigt sich allein mit ihr.

Ein QUANTIL berechnen

Die Excel-Hilfe führt dazu aus, das die Funktion das Alpha-Quantil einer Gruppe von Daten zurück gibt. Mithilfe der Funktion kannst du einen Akzeptanzschwellenwert festlegen. Du kannst z.B. entscheiden, dass nur Bewerber eingeladen werden, deren Prüfungsergebnisse oberhalb des 85%-Quantils liegen.

Die Syntax lautet:

=QUANTIL(Matrix;k)

Matrix bezeichnet den Datenbereich, den du untersuchen willst. k ist der Alphawert aus einem geschlossenen Intervall zwischen 0 und 1. Beim 30%-Quantil z.B. schreibst du k als „30%“ oder als „0,3“.

Ein Beispiel soll dir die Funktionsweise näher bringen. Angenommen, dir liegen Angaben über den Wasserverbrauch eines Ortes über 15 Monate vor. Diese sind chronologisch, unsortiert, in einer Liste erfasst.

Du willst wissen, welche Werte die unterste und die oberste Toleranzgrenze bilden.

Die unterste Grenze soll durch das 25%-Quantil, die oberste durch das 75%-Quantil abgebildet werden. Zusätzlich errechnest du noch den Median mit der Funktion MEDIAN und als 50%-Quantil.

25%-Quantil =QUANTIL($C$4:$C$18;25%) =303.152,50

50%-Quantil =QUANTIL($C$4:$C$18;50%) =306.994,00

Median =MEDIAN($C$4:$C$18) =306.994,00

75%-Quantil =QUANTIL($C$4:$C$18;75%) =313.950,00

Der Mittelwert liegt übrigens bei =MITTELWERT($C$4:$C$18) =309.168,93 und ist damit größer als der Median.

Mit bedingter Formatierung willst du sichtbar machen, welche Werte zwischen dem 25%- und dem 75%-Quantil liegen. Dazu sortierst du die Liste aufsteigend nach Größe.

Markiere die Liste, gehe auf die Bedingte Formatierung und wähle die Regel „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.

Schreibe die Formel:

=UND(C4>=$F$4;C4<=$F$5)

und formatiere „Ausfüllen“ mit grün.

In der Liste sind nun die sieben Werte zwischen 303.242 und 312.874 farblich markiert. Diese Werte liegen zwischen dem 25%- und dem 75%-Quantil. Vier Werte liegen darunter, vier Werte darüber.

Diese Erkenntnisse kannst du z.B. für Planungszwecke verwenden. Das 25%-Quantil kann als Worst Case, das 75%-Quantil als Best Case dienen.

Umfragen mit Netzdiagramm auswerten

Einleitung

Im Blogbeitrag „Netzdiagramme in Excel“ habe ich gezeigt, wie Ergebnisse einer Umfrage in einem Netzdiagramm dargestellt werden können.

Zwischenzeitliche Anfragen von Lesern sind Grund genug, das Thema noch einmal aufzunehmen. Insbesondere bezogen sich die Anfragen auf die Darstellung der Ergebnisse mehrerer Umfragen in einem Netzdiagramm.