Supportnet / Forum / Tabellenkalkulation

Moin, Habe folgendes Problem, Ich habe eine Tabelle, aufgebaut wie ein Stundenplan in der Schule, also oben die Wochentage und rechts die Zeiten. Jetzt möchte ich die Fächer, die eingetragen sind in einem neuen Tabellenblatt alle untereinander EINMAL aufführen. Wie geht sowas? Gruss BB

Hallo Bubbelup,

kleine Rückfrage zum Verständnis: Sind die Fächer alle in einem rechteckigen Bereich von 5 oder 6 Spalten und 4 bis 8 Zeilen einfach oder mehrfach enthalten?

Sollen auf dem zweiten Tabellenblatt die Fächer nur jeweils einmal aufgeführt werden (oder soll auch die Anzahl der Wochenstunden ermittelt werden)? Reicht unsortiert?

Um das mit Formeln zu lösen braucht man, glaube ich, eine Hilfsspalte. Einfacher wird´s mit 2. Mit VBA geht es natürlich auch, sogar noch leichter. An was hattest Du denn so gedacht?

Gruß,
CaroS

Hallo CaroS

Die Fächer sind mehrfach enthalten. Eine Sortierung oder Zählung ist nicht nötig.

Der Lösungsweg ist mir völlig egal. VBA, Makro, Hilfsspalten etc. alles möglich. Muss nur schneller sein wie das manuelle raussuchen, da es sich in wirklichkeit nicht um einen einfachen Schulstundenplan handelt, sondern um eine etwas größere Matrix (ca. 30 Spalten und 100 Zeilen)

Gruss
BB

Hallo Bubbelup,

ich hätte hier eine Formellösung (mit 1 Hilfsspalte) für 6 Wochentage (Spalten) und zurzeit max. 1000 Unterrichtseinheiten (erweiterbar).

Angenommen, es stehen in
A2 bis A10: 1. Stunde ... bis ... 9. Stunde (Beschriftung)
B1 bis G1: Mo, Di, Mi, ... , Sa (Beschriftung)
B2 bis G10: die Unterrichtsstunden (Text / bel.)

Dann könnte man auf demselben Tabellenblatt eine beliebige freien Spalte als Hilfsspalte benutzen, z. B. H, und schreibt in Zelle H1 die Formel:

=WENN(ZEILE() <= ANZAHL2(B$2:B$10); INDIREKT("B" & ZEILE() + 1);
WENN(ZEILE() - ANZAHL2(B$2:B$10) <= ANZAHL2(C$2:C$10); INDIREKT("C" & ZEILE() - ANZAHL2(B$2:B$10) + 1);
WENN(ZEILE() - ANZAHL2(B$2:B$10) - ANZAHL2(C$2:C$10) <= ANZAHL2(D$2:D$10); INDIREKT("D" & ZEILE() - ANZAHL2(B$2:B$10) - ANZAHL2(C$2:C$10) + 1);
WENN(ZEILE() - ANZAHL2(B$2:B$10) - ANZAHL2(C$2:C$10) - ANZAHL2(D$2:D$10) <= ANZAHL2(E$2:E$10);
INDIREKT("E" & ZEILE() - ANZAHL2(B$2:B$10) - ANZAHL2(C$2:C$10) - ANZAHL2(D$2:D$10) + 1);
WENN(ZEILE() - ANZAHL2(B$2:B$10) - ANZAHL2(C$2:C$10) - ANZAHL2(D$2:D$10) - ANZAHL2(E$2:E$10) <= ANZAHL2(F$2:F$10);
INDIREKT("F" & ZEILE() - ANZAHL2(B$2:B$10) - ANZAHL2(C$2:C$10) - ANZAHL2(D$2:D$10) - ANZAHL2(E$2:E$10) + 1);
WENN(ZEILE() - ANZAHL2(B$2:B$10) - ANZAHL2(C$2:C$10) - ANZAHL2(D$2:D$10) - ANZAHL2(E$2:E$10) - ANZAHL2(F$2:F$10) <= ANZAHL2(G$2:G$10);
INDIREKT("G" & ZEILE() - ANZAHL2(B$2:B$10) - ANZAHL2(C$2:C$10) - ANZAHL2(D$2:D$10) - ANZAHL2(E$2:E$10) - ANZAHL2(F$2:F$10) + 1); ""))))))

H2 usw.: H1 markieren und so weit runterkopieren/-ziehen wie nötig

(Die Hilfsspalte auf einem anderen Tabellenblatt anzulegen ginge zwar vom Prinzip her auch, aber wegen der dann notwendigen Bezüge zurück auf dieses Tabellenblatt würde die Formel die Länge von maximal 1024 Zeichen deutlich überschreiten.)

Die "Liste" der Fäche kann dagegen ohne weiteres auf einem anderen Tabellenblatt stehen, es muss nur der Bezug dorthin hergestellt werden (in diesem Beispiel "Tabelle3!..."). In eine Zelle der ersten Zeile ist folgende Matrixformel (ohne {}-Klammern) zu schreiben

{=WENN(SUMME(WENN(Tabelle3!H$1:H$100 = ""; 0; 1 / ZÄHLENWENN(Tabelle3!H$1:H$100; Tabelle3!H$1:H$100))) < ZEILE(); ""; INDIREKT("Tabelle3!H" & KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Tabelle3!H$1; ; ; ZEILE($1:$100)); Tabelle3!H$1:H$100) = 1; ZEILE($1:$100)); ZEILE(H1))))}

und mit Strg + Umsch + Eingabe einzugeben. Danach Zelle markieren und so weit runterkopieren/-ziehen wie nötig.

So, nun hat das Ganze allerdings den Nachteil, dass es momentan nur für maximal 6 Spalten funktioniert und leider nicht beliebig erweiterbar ist, jedenfalls ist das nicht ganz so einfach. Dass es so gekommen ist liegt zum einen daran, dass in der Fragestellung die Tabelle zuerst "wie ein Stundenplan in der Schule" beschrieben wurde, und zum anderen daran, dass ich schon mitten in der Arbeit gesteckt habe, als die Antwort kam, dass es nun ca. 30 Spalten sein sollen. Da bin ich auch nicht ständig am Aktualisieren.

Das ist ein wenig ärgerlich, wenn am Anfang nur unvollständige und ungenaue Angaben bekommt, aber ich bin ja selber schuld, ich hätte ja mit dem Arbeitsbeginn auch warten können, bis alle Angaben beisammen sind. - So ist das leider. Mal wartet man lange und manchmal sogar umsonst, ein anderes Mal kommt dann doch noch eine Überraschung.

Vielleicht kann jemand diese Lösung noch für "normale" Stundenpläne gebrauchen oder als Beispiel, wie es mit Formeln gehen würde, vielleicht kann man auch die Formel für die Hilfsspalte so weit verbessern, dass sie für ca. 30 Spalten einsetztbar ist.

Die nächste Lösung wird ja vermutlich ein VBA-Makro sein, diese Entscheidung wäre völlig klar gewesen, wenn man die Größenordnung (ca. 30 Spalten und 100 Zeilen) von Anfang an gekannt hätte.

Gruß,
CaroS

Hallo Bubbelup,

ich habe die Formellösung so verbessert, dass sie auch für 30 (oder 50) Spalten funktioniert.

Wieder angenommen, im Tabellenblatt "Tabelle1" stehen in
A2 bis A150: beliebige Beschriftungen (1. Stunde ... bis ... 149. Stunde)
B1 bis AF1 (= 31 Spalten): beliebige Beschriftungen (Datumswerte)
B2 bis AF150: die Unterrichtsfächer, die aufgelistet werden sollen (Text / bel.)

Nun kann man auf dem Tabellenblatt, wo die Liste der Unterrichtsfächer erstellt werden soll, eine beliebige freien Spalte als Hilfsspalte wählen, z. B. "H", schreibt in Zelle H1 die Matrixformel:

{=MAX(NICHT(ISTLEER(Tabelle1!A1:F150))*ZEILE(1:150))}

und schließt die Eingabe mit Strg + Umsch + Eingabe ab. In Zelle H2 schreibt man die Formel:

=WENN(INDIREKT(ADRESSE(REST(ZEILE() - 2; Tabelle1!H$1) + 1; GANZZAHL((ZEILE() - 2) / Tabelle1!H$1) + 1;;;"Tabelle1")) = ""; ""; INDIREKT(ADRESSE(REST(ZEILE() - 2; Tabelle1!H$1) + 1; GANZZAHL((ZEILE() - 2) / Tabelle1!H$1) + 1;;;"Tabelle1")))

und kopiert anschließend die Zelle (bzw. Formel) so weit nach unten, wie es für die Anzahl Zeilen x Spalten (149 x 31 = 4619) nötig ist, damit alle in "Tabelle1" eingetragenen Fächer - einschließlich ein paar Leerzellen - in dieser Hilfsspalte untereinander stehen.

Die Ergebnis-Liste kann ebenfalls in einer beliebigen Spalte erstellt werden, z. B. in "A". In eine Zelle der ersten Zeile ist die Matrixformel (ohne {}-Klammern) zu schreiben

{=WENN(SUMME(WENN(H$2:H$150 = ""; 0; 1 / ZÄHLENWENN(H$2:H$150; H$2:H$150))) < ZEILE(); ""; INDIREKT("H" & KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(H$2; ; ; ZEILE($2:$150)); H$2:H$120) = 1; ZEILE($2:$150)); ZEILE(H1))))}

und mit Strg + Umsch + Eingabe einzugeben. Danach die Zelle markieren und so weit runterkopieren/-ziehen wie nötig.

Ich denke, das war´s. Alle Formeln können dem Größenbedarf entsprechend erweitert/angepasst und in den Spalten nach unten kopiert werden. Läuft ab einer gewissen Größenordnung nicht mehr rasend schnell, aber läuft.

Gruß,
CaroS

Sorry, da sind mir leider ein paar Fehler passiert, da ich "eine Nummer kleiner" und ohne die Überschriftenzeile B1:AF1 getestet habe. Die Adressrechnung muss um eine Zeile verschoben werden und die Breite von 6 auf 31 Spalten erweitert werden. Die erste Matrixformel lautet richtig:

{=MAX(NICHT(ISTLEER(Tabelle1!B2:AF150))*ZEILE(2:150))}

Die anderen Korrekturen kommen gleich. / CaroS

Hallo!

Hier noch einmal die richtigen Formeln, einschließlich einer kleinen "Schönheits-korrektur": in der Liste der Fächer sollte nun zwischendurch keine Leerzelle mehr auftreten.

Matrixformel in H1 (wie in AW5):

{=MAX(NICHT(ISTLEER(Tabelle1!B2:AF150)) * ZEILE(2:150))}

Formel in H3, H2 bleibt leer (Fehler- + Schönheitskorrektur):

=WENN(INDIREKT(ADRESSE(REST(ZEILE() - 3; Tabelle1!H$1 - 1) + 2; GANZZAHL((ZEILE() - 3) / (Tabelle1!H$1 - 1)) + 1; ; ; "Tabelle1")) = ""; ""; INDIREKT(ADRESSE(REST(ZEILE() - 3; Tabelle1!H$1 - 1) + 2; GANZZAHL((ZEILE() - 3) / (Tabelle1!H$1 - 1)) + 1; ; ; "Tabelle1")))

Matrixformel in A1 (korrigiert):

{=WENN(SUMME(WENN(H$2:H$150 = ""; 0; 1 / ZÄHLENWENN(H$2:H$150; H$2:H$150))) < ZEILE(); ""; INDIREKT("H" & KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(H$1; ; ; ZEILE($2:$150)); H$2:H$150) = 1; ZEILE($2:$150)); ZEILE(H1))))}

Nun müsste das funktionieren. Bin aber an Rückmeldungen aller Art interessiert.

Gruß,
CaroS

Uiii,
Danke für die große Mühe!
Ich hab allerdings Gestern am späten Nachmittag selbst eine Lösung per Makro / VBA konstruiert.

Sub Makro1()
Dim n1, n2, m1, m2 As Integer

n1 = 4
m1 = 2
m2 = 1
n2 = 1

For m1 = 2 To Worksheets("Tabelle1").UsedRange.Columns.Count
    For n1 = 4 To Worksheets("Tabelle1").UsedRange.Rows.Count
        Suchtext = Worksheets("Tabelle1").Cells(n1, m1)
            If findetext(Suchtext) = False Then
                Worksheets("Tabelle2").Cells(n2, 1) = Worksheets("Tabelle1").Cells(n1, m1)
                n2 = n2 + 1
            End If
    Next n1
Next m1

End Sub
*********************************************************
Function findetext(ByVal Suchtext As String) As Boolean
On Error Resume Next

If IsEmpty(Worksheets("Tabelle2").Cells.Find(What:=Suchtext, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False).Address) Then
        findetext = False
Else
        findetext = True
End If
        
End Function

Zur kurzen Erklärung:
in MAKRO1 sind die schleifen, die durch die betreffenden Zellen der Tabelle1 wandern und in der Function SUCHETEXT wird nachgeschaut ob die aktuelle Zelle schon in Tabelle2 vorhanden ist.

Trotzdem Danke für deine Mühe.

Gruss
BB

• Excel-Tabelle ind Word einsetzen
• Daten aus zwei Tabellen über einen gemeinsamen Wert zusammenfassen
• Datenprüfung in Excel
• excel import in access
• Verknüpfungen in Access