Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1872to1876
1872to1876
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Spalten suche

Forumthread: Spalten suche

Spalten suche
16.03.2022 15:53:39
Marco
Hallo Zusammen,
Ich habe mal wieder ein kleines Problem. Ich habe eine Excel Datei, welche als Arbeitsplaner fungiert. Es gibt für jede Kalenderwoche ein Tabellenblatt. Die Namen stehen untereinander. Rechts neben den Namen sind die Spalten für Montag bis Sonntag.
Im jeweiligen Tag steht immer nur ein Feld, was auch immer für den jeweiligen Dienst gleich bezeichnet ist. Beispiel Spätdienst = Spät, Frühdienst = Früh, Fortbildung = FoBi
Ich würde jetzt gerne eine Übersicht auf einem neuen Tabellenblatt erstellen, wer wie viele Spätdienste frühdienste, Fortbildungen etc gemacht hat.
Wäre das in VbA oder per Formeln einfacher zu lösen?
Es müssen ja auf jeden fall alle Kalenderwochen ausgelesen werden... in der Übersicht ersetzen die Tagezellen dann die Namen der Dienste. Siehe vereinfachte Beispieldatei.
Am liebsten wäre mir es, wenn es ständig aktualisiert wird.
https://www.herber.de/bbs/user/151809.xlsx
Vielen Dank im Voraus!
LG Marco
Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Spalten suche
16.03.2022 15:55:20
SF
Hola,

Es gibt für jede Kalenderwoche ein Tabellenblatt.
warum?
Pack alles auf ein Blatt, sind ja eh identisch aufgebaut und lös das Problem mit Summenprodukt().
Gruß,
steve1da
AW: ... musst Du nicht und ...
16.03.2022 16:41:53
neopa
Hallo Marco,
... auch die Zusammenführung Deiner KW-Tabellen ist auch nicht unbedingt notwendig. Vorausgesetzt es stehen wirklich immer die identischen Namen in der Spalte B und in Spalte C:F immer die identischen Textwerte, dann kannst Du es mit zwei kleinen zu kopierenden Formeln lösen.
Zuerst gruppiere alle Deine KW-Tabellenblätter (ohne das Übersichten-Tabellenblatt).
Schreibe in K2:N2 Früh Spät Nacht Fortbildung und anschließend in K3 folgende Formel:
=ZÄHLENWENN($C3:$I3;K$2) und kopiere diese nach rechts bis N2 und dann alle Formeln nach unten so weit wie erforderlich.
Danach hebe die Gruppierung der Tabellenblätter wieder auf!
Im Übersichten-Tabellenblatt schreibe in C3: =SUMME('1:2'!K3) und kopiere diese nach rechts und unten.
Das war es dann auch schon.
Gruß Werner
.. , - ...
Anzeige
AW: Spalten suche
16.03.2022 17:56:36
UweD
Hallo
hier noch eine VBA Lösung
- Die KW Blätter sind einfach nur als Ziffern 1 bis 53 benannt
- Fehlerbehandlung, falls mal ein Blatt fehlt, ist (noch) nicht eingebaut

Sub Zusammenfassung()
Dim Tb As Worksheet, LC As Integer, RNG As String, i As Integer
Set Tb = Sheets("Übersicht Stunden")
RNG = "C3:F50" 'Zielbereich
Application.ScreenUpdating = False
LC = Tb.Cells(2, Tb.Columns.Count).End(xlToLeft).Column 'letzte Spalte der Zeile 2
With Tb.Range(RNG)
'reset Tempbereich
.Offset(0, 5).ClearContents
For i = 1 To 53
.Formula2R1C1 = _
"=SUMPRODUCT(('" & i & "'!R3C2:R50C2=RC2)*('" & i & "'!R3C3:R50C9=R2C))+RC[5]"
.Offset(0, 5).Value = .Value 'Werte in Tempbereich setzen
Next
'aus Temp zurücklesen und löschen
.Value = .Offset(0, 5).Value
.Offset(0, 5).ClearContents
End With
MsgBox "Fertig"
End Sub
LG UweD
Anzeige
AW: Spalten suche
17.03.2022 07:47:32
Marco
Hallo an alle, vielen herzlichen Dank für die zahlreichen Antworten.
Die Lösung von Werner verstehe ich, die funktioniert auch. Vielen Dank!
Klaus, genau, das Problem haben wir auch bereits erkannt und haben die Einträge auf Auswahllisten nach Rechtsklick auf diese limitiert. Es werden keine anderen zugelassen. Danke für den Hinweis trotzdem! :)
UweD, deine VBA Lösung verstehe ich noch nicht ganz. Ich habe sie bei mir in die Arbeitsmappe in die "Change" Routine eingebaut und auf meine Spalten und Reihen angepasst. Du deklarierst ja "LC". Das "LC" kommt aber im späteren Verlauf nicht mehr vor. Es wird dann nur noch von RC gesprochen. Ist das ein Tippfehler oder bennenst du hier R als Reihe und C als Spalte?
Danke nochmal vorab für die Rückantwort!
Lg Marco
Anzeige
AW: Spalten suche
17.03.2022 09:25:04
UweD
Hallo
Oh. Das LC ist noch aus einem früheren Versuch übrig geblieben und kann ersatzlos gelöscht werden.
LG
AW: Spalten suche
17.03.2022 09:43:50
Marco
Perfekt, vielen Dank!
Dennoch macht das Makro bei mir rein gar nichts....alle Spalten in der Übersicht bleiben leer...
das ist mein angepasster Code auf die reale Tabelle:
Hinweis: Nummerierung in Spalte A, Abteilung in Spalte B, Name in Spalte C und ab Spalte D dann die Wochentage. Bei der Übersicht ab Spalte D dann die Spalten für die jeweiligen Aufzählungen bis Spalte O.
Erste Zelle Wochentag erster Name und letzter Wochentag letzter Name geht von D4 bis K70. 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tb As Worksheet, LC As Integer, RNG As String, i As Integer
Set Tb = Sheets("Übersicht ET")
RNG = "D4:M70" 'Zielbereich
Application.ScreenUpdating = False
With Tb.Range(RNG)
'reset Tempbereich
.Offset(0, 12).ClearContents
For i = 1 To 53
.Formula2R1C1 = _
"=SUMPRODUCT(('" & i & "'!R4C3:R70C3=RC3)*('" & i & "'!R4C4:R70C11=R3C))+RC[12]"
.Offset(0, 12).Value = .Value 'Werte in Tempbereich setzen
Next
'aus Temp zurücklesen und löschen
.Value = .Offset(0, 12).Value
.Offset(0, 12).ClearContents
End With
End Sub
Lg Marco
Anzeige
AW: Spalten suche
17.03.2022 16:08:22
UweD
Das passt doch gar nicht mehr zu der Musterdatei, die du hochgeladen hast.
Da gab es in Übersicht nur A bis F
und in den Wochenblättern A bis I
Userbild
Userbild
Was denn nun?
Also neue Datei hochladen
Anzeige
AW: Spalten suche
17.03.2022 17:05:41
Marco
Hey,
Sorry, die Originaldatei ist sehr komplex und tatsächlich streng vertraulich....und hat um die 100 Tabellenblätter. Wichtig ist aber, dass nur die KWs rein als Zahlen von 1 bis 53 bezeichnet sind. Alle anderen Tabellenblätter sollen nicht ausgewertet werden und haben Texte als Namen.
Ich habe meine erste Datei komplett vereinfacht und hoffte, dass ich den Code dann selbst anpassen kann. Dazu reichen aber meine Kenntnisse leider nicht aus.
Die Datei im Anhang jetzt ist wieder sehr vereinfacht und hat sich auf 2 KWs und die Übersicht beschränkt. Die Anordnung der Zeilen, Spalten etc ist jetzt aber identisch zur Originaldatei.
Am besten sollte das Makro dann ablaufen, sobald was auf den jeweiligen Kalenderblätter geändert wurde, sprich im Workbook-Modul in der Change Variante.
Hier die Datei:
https://www.herber.de/bbs/user/151827.xlsx
Vielen herzlichen Dank nochmals! Und sorry für die Umstände!
Lg Marco
Anzeige
AW: Spalten suche
18.03.2022 15:38:50
UweD
Hallo
es war doch einiges aufwändiger als gedacht.
Den Rest musst du alleine machen
LG UweD
In den Codebereich von "Diese Arbeitsmappe"

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo Fehler
Const APPNAME = "Workbook_SheetChange"
Dim rngU As String, TbU As Worksheet, rngS As Range, Z, WF
Dim SP As Integer, NName As String, Akt As String, Zeile As Integer, Spalte As Integer
Dim Arr, i As Integer, rTmp As Range, Altwert As String
Set TbU = Sheets("Übersicht")
Set rngS = TbU.Range("D2:O2")
rngU = "D4:J70"
SP = 3 'Spalte mit Name
Set WF = WorksheetFunction
Select Case Sh.Name
Case 1 To 53 'Nur innerhalb dieser Blätter
If Not Intersect(Target, Sh.Range(rngU)) Is Nothing Then 'nur im festgelegten Bereich
For Each Z In Target.Cells
NName = Sh.Cells(Z.Row, SP) ' Name des Mitarbeiters
Akt = Z.Value 'Aktion
If Akt = "" Then
'wenn Aktion entfernt wird
With Application
.EnableEvents = False
.Undo
Altwert = Z.Value
If Altwert  "" Then
Akt = Altwert
Else
Exit Sub
End If
.Undo
.EnableEvents = True
End With
End If
Spalte = WF.CountIf(rngS, Akt) 'ist Aktion vorhanden
If Spalte > 0 Then
Spalte = WF.Match(Akt, rngS, 0)
Zeile = WF.CountIf(TbU.Columns(SP), NName) 'ist Name vorhanden
If Zeile > 0 Then
Zeile = WF.Match(NName, TbU.Columns(SP), 0) 'in welcher Zeile
ReDim Arr(1 To rngS.Columns.Count)
For i = 1 To 53 'alle KWs durchlaufen
If Not IsError(Evaluate(CStr(i) & "!A1")) Then 'Blatt vorhanden?
'alle Tage der Woche durchzählen und in Array merken
Set rTmp = Intersect(Sheets(CStr(i)).Range("D:J"), Sheets(CStr(i)).Rows(Zeile))
Arr(Spalte) = Arr(Spalte) + WF.CountIf(rTmp, Akt)
End If
Next
'zurückschreiben in Zieltabelle
Application.EnableEvents = False
Arr(Spalte) = IIf(Arr(Spalte) = 0, "", Arr(Spalte)) 'Null duch leer ersetzen
rngS.Offset(Zeile - 2, Spalte - 1).Resize(1, 1) = Arr(Spalte)
Application.EnableEvents = True
Else
MsgBox "Fehler: '" & NName & "'  nicht gefunden"
End If
Else
MsgBox Akt & ":  nicht gefunden in " & TbU.Name
End If
Next
End If
End Select
'*** Fehlerbehandlung
Err.Clear
Fehler:
Application.EnableEvents = True
If Err.Number  0 Then MsgBox "Fehler in Sub """ & APPNAME & """" & vbCrLf _
& "Fehlernummer: " & Err.Number & vbLf & Err.Description: Err.Clear
End Sub

https://www.herber.de/bbs/user/151849.xlsm
Anzeige
AW: bitteschön owT
17.03.2022 10:32:21
neopa
Gruß Werner
.. , - ...
AW: Spalten suche
16.03.2022 18:38:29
KlausFz
Hallo Marco,
ein prinzipielles Problem bei solchen Datenbanken: Du oder sonst wer - machst die Einträge händisch.
Tippfehler sollen ja mal vorkommen ...
Dann hast Du falsche Übersichten - und merkst es nicht einmal.
Sorge mit Datenüberprüfung und Auswahl aus einer zentral hinterlegten Liste dafür,
dass immer nur exakte Eingaben möglich sind.
Gruß!
Klaus
Anzeige
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige