Stichprobenziehung mit Excel

In der Sozialforschung ist es meistens aus Zeit- und Kostengründen nicht realisierbar, in eine geplante Erhebung die gesamte Population zu integrieren (Vollerhebung).

Daher wird mit Stichproben gearbeitet (Teilerhebung). Wenn Du eine statistische Erhebung durchführen willst, wählst Du zufällig Dinge aus, stellst Fragen und notierst Antworten.

Eine Stichprobe ist die Menge der Dinge, die ausgewählt wird. Stichproben stellen eine Teilmenge aller Untersuchungseinheiten dar, welche die für das Forschungsziel relevanten Eigenschaften der Grundgesamtheit möglichst genau abbilden.

Der Stichprobenumfang ist die Anzahl dieser Dinge.

Der Beitrag zeigt, wie mit Excel aus der Menge der erfassten Daten eine Stichprobe zu ziehen.

Weiterlesen „Stichprobenziehung mit Excel“

VBA-Methoden: Arbeitsblätter verschieben und umbenennen

Am Ende des letzten Beitrags enthielt die Arbeitsmappe mehrere Arbeitsblätter, die allerdings nicht in einer gewünschten /  logischen Reihenfolge angeordnet waren.

Eine gewünschte Reihenfolge kannst Du nun herstellen, indem Du die Arbeitsblätter verschiebst.

1. Arbeitsblätter verschieben

Fürs Verschieben verwendest Du in VBA die Methode „Move“ und die Argumente „Before“ und „After“.

Die Endmappe des letzten Beitrags enthielt Arbeitsblätter in dieser Reihenfolge:

Tabelle2, Tabelle1, Tabelle3, Tabelle5, Tabelle4

Mit folgendem Code bringst Du mit einem Makro alle Arbeitsblätter in die richtige Reihenfolge:

Sub ArbBlattVerschieben()

Worksheets(„Tabelle1“).Move Before:=Worksheets(„Tabelle2“)

Worksheets(„Tabelle5“).Move After:=Worksheets(„Tabelle4“)

End Sub

2. Arbeitsblätter umbenennen

Die Arbeitsblätter werden jetzt standardmäßig nur mit „Tabelle1“ bis „Tabelle5“ bezeichnet.

Du willst sie aber mit Monatsnamen benennen, Januar bis Mai.

Mit dem folgenden Code erhalten alle fünf Arbeitsblätter einen neuen Namen:

Sub ArbBlattUmbenennen()

Worksheets(„Tabelle1″).Name=“Januar“

Worksheets(„Tabelle2″).Name=“Februar“

Worksheets(„Tabelle3″).Name=“März“

Worksheets(„Tabelle4″).Name=“April“

Worksheets(„Tabelle5″).Name=“Mai“

End Sub

Die Arbeitsblätter haben jetzt alle den gewünschten Namen erhalten.

VBA-Methoden: Ein neues Arbeitsblatt erstellen und einfügen

Es kann innerhalb einer VBA-Prozedur erforderlich werden, in einer Arbeitsmappe ein weiteres Arbeitsblatt einzufügen und ggf. namentlich zu benennen.

Eine neues Arbeitsblatt zu erstellen, erfordert nur eine Codezeile.

Sub NeuesArbBlatt1()
Worksheets.Add
End Sub

Das ist schon alles. Vor dem aktuellen Arbeitsblatt wird das neue eingefügt.

Dir gefällt nicht, dass das neue Blatt an vorderster Stelle eingefügt wurde.

Mit dem Argument „After“ veranlasst Du, dass das neue Blatt nach einem vorhandenen eingefügt wird.

Sub NeuesArbBlatt2()
Worksheets.Add After:=Worksheets(„Tabelle1“)
End Sub

Um ein neues Arbeitsblatt vor einem vorhandenen Blatt einzufügen, verwendest Du das Argument „Before“.

Sub NeuesArbBlatt3()
Worksheets.Add Before:=Worksheets(„Tabelle3“)
End Sub

Hierzu erspare ich uns die Abbildung.

Du kannst ein neues Arbeitsblatt auch grundsätzlich an letzter Stelle einfügen.

Verwende dazu diesen Code:

Sub NeuesArbBlatt4()
Worksheets.Add After:=Worksheets(Worksheets.Count)
End Sub

Der Vollständigkeit halber, Du kannst auch gleich mehrere neue Arbeitsblätter einfügen. Dazu verwendest Du das Argument „Count“.

Sub NeuesArbBlatt5()
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=2
End Sub

Mit „Count:=2“ hast Du Dich für zwei weitere Blätter entschieden. Die Blätter „Tabelle5“ und „Tabelle4“ wurden hinter den vorhandenen neu eingefügt.

Du kannst den Blättern jetzt manuell andere Namen geben oder Du wartest auf den nächsten Beitrag zu den VBA-Methoden.

SVERWEIS mit zwei Suchkriterien

Die Anwendung der Excel-Funktion SVERWEIS mit einem Suchkriterium ist allgemein bekannt.

Hast Du jedoch den Wunsch, einen Wertebereich nach zwei Suchkriterien abzufragen, geht das mit der Standardfunktion nicht.

Der Beitrag soll Dir einen Weg aufzeigen.

Weiterlesen „SVERWEIS mit zwei Suchkriterien“

VBA: Das Steuerelement OptionButton

Der OptionButton (Optionsfeld) ist eine Steuerelement aus den ActiveX-Steuerelementen.

Verwende ein OptionButton, um anzuzeigen, ob ein einzelnes Element innerhalb einer Gruppe ausgewählt wurde.

Du willst beispielsweise mit ausgewählten Werten aus der folgenden Liste wahlweise in zwei Verfahren rechnen.

Einerseits sollen die Werte aus B26 und B28 subtrahiert werden, wenn ein OptionButton aktiv ist.

Andererseits sollen die Werte in B27 und B29 multipliziert werden, wenn ein zweiter OptionButton aktiv ist.

Füge zunächst zwei OptionButton in Dein Arbeitsblatt ein. Gehe dazu im Menü „Entwicklertools“ auf „Einfügen“ und wähle den OptionButton bei den ActiveX-Steuerelementen.

Aktiviere danach eine Zelle Deines Arbeitsblattes, um das Steuerelement einzufügen.

Um den Hintergrund farbig zu gestalten, schaltest Du unter den „Entwicklertools“ in den „Entwurfsmodus“ und rufst „Eigenschaften“ auf.

Wähle unter „BackColor“ eine Farbe aus.

Beide OptionButton stehen nun zur Verfügung.

Die Berechnungsergebnisse sollen rechts daneben, in den Zellen D2 bzw. D4 ausgegeben werden.

Eröffne das Makro mit z.B. Sub OptionRechnen() in einem Modul.

Deklariere nun zuerst die Variablen für Dein Makro.

Dim Subtraktion As Long

Dim Multiplikation As Long

Weise ihnen Werte zu.

Subtraktion = Range(„B7“).Value – Range(„B9“).Value

Multiplikation = Range(„B8“).Value * Range(„B10“).Value

Weise nun an, was geschehen soll, wenn der OptionButton1 aktiv ist. Das machst Du mit einer If-Anweisung. Die Anweisung lautet, die definierte Subtraktion auszuführen und gleichzeitig das Ergebnisfeld für OptionButton2 zu leeren.

If Worksheets(„Tabelle1“).OptionButton1.Value = True Then

Range(„D2“) = Subtraktion

Range(„D4“).ClearContents

Weise dann an, was geschehen soll, wenn der OptionButton2 aktiv ist. Hier lautet die Anweisung, die definierte Multiplikation auszuführen und das Ergebnisfeld für OptionButton1 zu leeren.

ElseIf Worksheets(„Tabelle1“).OptionButton2.Value = True Then

Range(„D4“) = Multiplikation

Range(„D2“).ClearContents

Beende das Makro mit End If und End Sub.

Komplett sieht das Makro so aus:

Sub OptionRechnen()

Dim Subtraktion As Long

Dim Multiplikation As Long

Subtraktion = Range(„B7“).Value – Range(„B9“).Value

Multiplikation = Range(„B8“).Value * Range(„B10“).Value

If Worksheets(„Tabelle1“).OptionButton1.Value = True Then

Range(„D2“) = Subtraktion

Range(„D4“).ClearContents

ElseIf Worksheets(„Tabelle1“).OptionButton2.Value = True Then

Range(„D4“) = Multiplikation

Range(„D2“).ClearContents

End If

End Sub.

Füge zuletzt auf Deinem Arbeitsblatt noch eine Schaltfläche aus den Formularsteuerelementen ein und weise diesem das Makro „OptionRechnen“ zu.

Aktiviere nun OptionButton1 und klicke auf die Schaltfläche. Es wird die Subtraktion ausgeführt.

Aktiviere jetzt OptionButton2 und klicke auf die Schaltfläche. Es wird die Multiplikation ausgeführt.

Dies war nur ein einfaches Beispiel der Anwendung von OptionButton. Vieles andere ist denkbar.

Z.B. könnte die Aktivierung von OptionButto1 bzw. von OptionButton2 jeweils ein weiteres Makro starten.

Du kannst auch Makros am Steuerelement als Private Sub hinterlegen. Gehe dazu in den Entwurfsmodus, aktiviere das Steuerelement und rufe mit der rechten Taste „Code anzeigen“ auf.

Du gelangst jetzt in den Editor. Die erste und letzte Zeile sind bereits vorgegeben. Füge die Codezeilen dazwischen ein. Ein Klick auf einen OptionButton teilt Dir nun über eine MsgBox z.B. mit, ob ein Gast eine Tasse Kaffee oder eine Tasse Tee wünscht.

Private Sub OptionButton1_Click()

If OptionButton1.Value = True Then

MsgBox („Der Gast wünscht eine Tasse Kaffee!“)

End If

End Sub

Private Sub OptionButton2_Click()

If OptionButton2.Value = True Then

MsgBox („Der Gast wünscht eine Tasse Tee!“)

End If

End Sub

Lösen von Gleichungssystemen mit Excel

(Neubearbeitung 20.07.2021)

Das Lösen von Gleichungssystemen ist bei Schülern eine ganz „beliebte“ Angelegenheit.

Zuerst zeige ich, wie in der Schule eine Lösung herbeigeführt werden kann.

Dann zeige ich, wie es auch mit Excel geht.

Weiterlesen „Lösen von Gleichungssystemen mit Excel“

Das Steuerelement „Kontrollkästchen“

(Neubearbeitung 09.07.2021)

Kontrollkästchen sind Steuerelemente. Sie dienen dazu, Rechenoperationen zuzulassen oder nicht zuzulassen. Ist ein Kontrollkästchen aktiviert (durch Setzen des Hakens), wird eine Operation, die daran geknüpft ist, ausgeführt.

Excel bietet zwei Arten von Kontrollkästchen an, als Formularsteuerelement und als ActiveX-Steuerelement. Wo findest Du sie? Über Entwicklertools / Einfügen wählst Du jeweils das dritte Element von links. Klicke jetzt die Zelle an, in der das Kästchen liegen soll.

Das Formularsteuerelement formatierst Du, indem Du es mit der rechten Maustaste anklickst und „Steuerelement formatieren“ aufrufst. Du kannst z.B. die Füllung des Elements und die Linien gestalten.

Rufst Du „Steuerung“ auf, kannst Du als Zellverknüpfung eine Zelladresse eingeben. Dort wird bei Aktivierung des Kästchens WAHR und bei Deaktivierung FALSCH stehen. bei Deaktivierung FALSCH stehen. Als Verknüpfung kannst Du auch B4, also unter dem Element wählen. Dann sind WAHR oder FALSCH nicht zu sehen. Besser ist allerdings, die Verknüpfungszelle sichtbar zu halten.

Um das ActiveX-Steuerelement zu formatieren, rufst Du unter Entwicklertools den Entwurfsmodus auf. Klicke dann auf Eigenschaften und formatiere. Die Hintergrundfarbe findest Du unter BackColor. Die Zellverknüpfung kannst Du unter LinkedCell eingeben. Verlasse danach wieder den Entwurfsmodus.

Bei Aktivierung erscheint in der verknüpften Zelle WAHR, bei Deaktivierung FALSCH.

In beiden Elementen kannst Du die Inschrift ändern.

Das Formularsteuerelement klickst Du mit rechter Maustaste an, setzt den Cursor in den Text und änderst ihn. Beim ActiveX-Element gehst Du über den Entwurfsmodus und Eigenschaften. Unter „Caption“ kannst Du den Text ändern.

Wozu kannst Du die Steuerelemente nun nutzen?

Am Beispiel des Formularsteuerelements will ich das erklären, mit dem anderen Element arbeitest Du genauso.

Wenn z.B. das Element auf WAHR geschaltet ist, sollen die Werte in B9 und C9 addiert werden. Ist auf FALSCH geschaltet, sollen sie multipliziert werden.

Weitere Verwendungsmöglichkeiten wirst Du sicher selbst finden.

Zum Download hier die Datei:

Ligaverwaltung 1. bis 3. Fußball-Liga 2021 / 2022 mit Excel

In den vergangenen Jahren konntest Du im Blog nur die Spielpläne bis hin zur Abschlusstabelle für die 1. Fußball-Bundesliga finden.

Ich erweitere ab der nächsten Saison das Angebot um die 2. Bundesliga und die 3. Liga.

Die 1. Bundesliga beginnt am 13.08.2021 mit dem ersten Spiel. Die 2. Bundesliga und die 3. Liga beginnen ihren Spielbetrieb am 23.07.2021. Am Ende des Beitrags findest Du jeweils die Spielpläne und die Tabelle. Die Spielpläne entstammen den unten angegebenen Quellen.

Für die drei Ligen wurde die Vorlage zur Verwaltung der Spiele unverändert vom Vorjahr übernommen. Folge den beiden Links zu Nachlesen:

Ligaverwaltung 1. Fußball-Bundesliga 2019/2020 mit Excel – clevercalcul (wordpress.com)

Ligaverwaltung 1. Fußball-Bundesliga 2020/2021 mit Excel – clevercalcul (wordpress.com)

Welche Ergebnisse an allen 34 (1. und 2. Liga) bzw. 38 (3. Liga) Spieltagen erzielt werden, kannst du jeweils bequem in die aktuellen Excel-Vorlagen eintragen. Benutze dazu bitte nur die Tabellenblätter 1 bis 34 bzw. 1 bis 38 und trage die Spielergebnisse in die gelb markierten Zellen ein.

Das Modell rechnet sich dann durch bis zur Abschlusstabelle. Dort musst du lediglich in Zelle E5 den aktuellen Spieltag manuell eingeben.

Die Abschlusstabelle wird in der Reihenfolge nicht nach jedem Spiel mit der offiziellen Tabelle der Deutschen Fußball-Liga übereinstimmen, denn es kann zumindest am Anfang noch so sein, dass zwei Mannschaften dieselben Punkte, dieselbe Tordifferenz und dieselbe Anzahl der erzielten Tore aufweisen.

Beachte bitte: Vor dem ersten Spieltag sind in der Abschlusstabelle 1. Bundesliga nur Borussia Mönchengladbach, in der Abschlusstabelle 2. Bundesliga nur der FC Schalke 04 bzw. in der Abschlusstabelle 3. Liga nur der SC Freiburg II gelistet.

Das liegt daran, dass alle Mannschaften noch auf Rang 1 liegen und in der Abschlusstabelle Spalte C mit der Funktion KKLEINSTE operiert wird. Am Ende des ersten Spieltages enthält die Tabelle alle beteiligten Mannschaften.

Das Modell berücksichtigt bei der Berechnung des Rangs nur

  • Anzahl der Punkte
  • Tordifferenz
  • erzielte Tore

Das hat sich für die vergangene Saison als ausreichend erwiesen.

Die Vorlagen kannst Du Dir hier anschließend herunterladen. Viel Spaß damit.

Quellen:

[1]

http://www.bulibox.de/downloads/download.html

[2]

http://www.bulibox.de/downloads/download.html

[3]

Spieltag/Tabelle :: 3. Liga :: Ligen Männer :: Ligen & Wettbewerbe :: DFB – Deutscher Fußball-Bund e.V.

Wie in Excel eine Matrix definiert werden kann

(Neubearbeitung 12.06.2021)

Matrizen werden in vielen Excel-Funktionen als Kriterium benötigt, z.B.

INDEX                     =INDEX(Matrix;Zeile;Spalte)
SUMMENPRODUKT   =SUMMENPRODUKT(Array1;Array2; …)
SUMMEWENN            =SUMMEWENN(Bereich;Suchkriterium;Summe Bereich)
MTRANS                          =MTRANS(Matrix)
VERGLEICH             =VERGLEICH(Suchkriterium;Suchmatrix;Vergleichstyp)
u.a.m.

Wie definiert sich eine Matrix nun?

Mit der INDEX-Formel zeige ich, wie die Matrix angewandt wird.

1. über die Werte eines Bereichs

=INDEX(B2:B6;2;1)

=38

2. über Werte in geschweiften Klammern

{25;38;;99;12;1245}

=INDEX({25;38;99;12;1245};2;1)

=38

3. über einen namentlich benannten Bereich

Name B8:B12: „Werte“

=INDEX(Werte;2;1)

=38

4. über eine Definition im Namensmanager

Name: „Matrix1“

bezieht sich auf: B14:B18

=INDEX(Matrix1;2;1)

=38

5. Erzeugung der Matrix durch Berechnung

Die Matrix entsteht aus der Multiplikation der Werte in Spalte B mit den Werten in Spalte C, z.B. 3 * 38 = 114.

=INDEX((B20:B24)*(C20:C24);2;1)

=114

Diese Form der Matrix ist im Tabellenblatt nicht zu sehen, wird aber durch die Multiplikation erzeugt.

Pivottabellen: Berechnete Felder

Du möchtest mit Daten aus einer Pivottabelle rechnen. Diese Daten sind nicht in den Quelldaten vorhanden bzw. können dort auch nicht ergänzt werden.

Da hast Du zuerst die Möglichkeit, einfach in einer an die Pivottabelle angrenzenden Spalte diese Berechnungen durchzuführen. Das aber hat den Nachteil, dass diese Spalte bei einer Erweiterung der Spalten der Pivottabelle überschrieben werden kann. Diese Spalte passt sich außerdem nicht einer Veränderung der Zeilenstruktur an.

Das heißt, Du beginnst nach einer Aktualisierung der Pivottabelle erneut, die Werte in der neuen Spalte berechnen zu lassen.

Eine bessere Möglichkeit ist die Arbeit mit sogenannten berechneten Feldern. Das soll der Hauptinhalt dieses Beitrags sein.

Weiterlesen „Pivottabellen: Berechnete Felder“