Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1428to1432
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Tabellenwerte vergleichen (Urlaubsplaner)

Tabellenwerte vergleichen (Urlaubsplaner)
31.05.2015 21:42:15
Tim
Guten Abend liebe Forengemeinde
Dies ist mein erster Beitrag in diesem Forum. Habe zuvor schon mit Suchmaschinen etc. nach Codeschnipseln gesucht die mir mit meinem Problem weiterhelfen könnten. Leider nur mit mäßigem Erfolg. Dies wird erstrecht schwierig, wenn man die Codeschnipsel nicht "fachgerecht" aneinanderreihen kann. Nun zu meinem Problem:
Ich habe ein Tabellenblatt (Tabelle1) in denen in Spalte A verschiedenste Namen stehen und in den weiteren Spalten die Daten des Jahres.
In einem anderen Tabellenblatt (Tabelle2) stehen in Spalte A Namen die mit den aus Tabelle1 Spalte A identisch sein können. In Spalte B stehen dazugehörige Daten.
An einem Beispiel wird dies deutlicher:
Tabelle1:
Spalte1: Namen
Spalte2: 1.1.2015
Spalte3: 2.1.2015
Spalte4: 3.1.2015
Spalte5: [..]
Spalte366: 31.12.2015
Tabelle2:
Paul 1.5.2015
Paul 2.5.2015
Peter 1.5.2015
Max 10.5.2015
Max 11.5.2015
Max 12.5.2015
Nun wäre ein Makro genial, welches in Tabelle1 die Namen durchläuft, bei Fund in Tabelle2 das Datum kopiert (oder ein X) und dies in die entsprechende Spalte in Tabelle1 schreibt.
Ein erster Schritt wäre dies gefundene Makro: Sub Uebetragen() Dim rng As Range Dim iRowL As Integer, iRow As Integer iRowL = Cells(Rows.Count, 1).End(xlUp).Row For iRow = 1 To iRowL If Not IsEmpty(Cells(iRow, 1)) Then With Worksheets("Tabelle2") Set rng = .Cells.Find(Cells(iRow, 1), _ lookat:=xlWhole, LookIn:=xlValues) If Not rng Is Nothing Then Cells(iRow, 2) = .Cells(rng.Row, 2) Cells(iRow, 3) = .Cells(rng.Row, 3) End If End With End If Next iRow End Sub
Das stößt jedoch bei doppelten Werte auf seine Grenzen...
Vielen Dank für eure Ideen, Tips und Ratschläge. Wenn jemand eine rein Excelbasierte Lösung parat hat wäre das natürlich auch denkbar. Jedoch hab ich schon mit Funktionen wie Vergleich, Sverweis und Index Versuche angestellt, die aber in diesem Falle nicht zielführend waren.
Gruß Tim

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Tabellenwerte vergleichen (Urlaubsplaner)
31.05.2015 21:50:12
Sepp
Hallo Tim,
dazu braucht man kein VBA, allerdings wäre eine Beispieldatei hilfreich.
Gruß Sepp

AW: Tabellenwerte vergleichen (Urlaubsplaner)
31.05.2015 21:56:25
Tim
Hallo
Hier habe ich mal eine kleine Beispieldatei erstellt.

Die Datei https://www.herber.de/bbs/user/97957.xlsx wurde aus Datenschutzgründen gelöscht


Ich hoffe das ist logisch was ich vorhabe?
In Tabelle 1 sollen überall beispielsweise ein "X" stehen wenn Max Urlaub hat. Wann er das hat, steht in Tabelle2
Gruß Tim

Anzeige
AW: Tabellenwerte vergleichen (Urlaubsplaner)
31.05.2015 22:18:00
Sepp
Hallo Tim,
Tabelle2 habe ich ein klein wenig angepasst, ist so einfacher und übersichtlicher.
Tabelle1

 ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAU
1Name01.01.201502.01.201503.01.201504.01.201505.01.201506.01.201507.01.201508.01.201509.01.201510.01.201511.01.201512.01.201513.01.201514.01.201515.01.201516.01.201517.01.201518.01.201519.01.201520.01.201521.01.201522.01.201523.01.201524.01.201525.01.201526.01.201527.01.201528.01.201529.01.201530.01.201531.01.201501.02.201502.02.201503.02.201504.02.201505.02.201506.02.201507.02.201508.02.201509.02.201510.02.201511.02.201512.02.201513.02.201514.02.201515.02.2015
2Max                                              
3Moritz                                              
4Paul                                              
5Tim                                              
6Klaus                                              
7Peter                                              

Formeln der Tabelle
ZelleFormel
B2=SUMMENPRODUKT((_name=$A2)*(_von<=B$1)*(_bis>=B$1))
Namen in Formeln
ZelleNameBezieht sich auf
B2_bis=Tabelle2!$C$2:INDEX(Tabelle2!$C:$C;ANZAHL2(Tabelle2!$A:$A))
B2_name=Tabelle2!$A$2:INDEX(Tabelle2!$A:$A;ANZAHL2(Tabelle2!$A:$A))
B2_von=Tabelle2!$B$2:INDEX(Tabelle2!$B:$B;ANZAHL2(Tabelle2!$A:$A))
Namen verstehen

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B21. / Formel ist =B2=1Abc


Tabelle2

 ABC
1NameVonBis
2Max01.01.201504.01.2015
3Moritz06.02.201509.02.2015
4Paul05.01.201505.01.2015
5Tim01.01.201514.01.2015
6Klaus08.01.201511.01.2015
7Peter12.01.201514.01.2015
8Max05.02.201514.02.2015
9   


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Hier noch die Datei.
https://www.herber.de/bbs/user/97958.xlsx
Gruß Sepp

Anzeige
AW: Tabellenwerte vergleichen (Urlaubsplaner)
31.05.2015 22:29:23
Tim
Hallo Sepp,
Ich bin begeistert! Vielen Dank für deine schnelle und kompetente Hilfe. Wie das genau mit der Multiplikation der Bereiche funktioniert muss ich mir noch mal näher anschauen und etwas einlesen.
Problem ist jedoch noch folgendes: Tabelle2 liegt so wie in meiner erstellten Datei vor.
Entweder müsste man deine Formel darauf anpassen oder die Daten aus Tabelle2 in einer Hilfstabelle anders aufbereiten.
Gruß Tim

AW: Tabellenwerte vergleichen (Urlaubsplaner)
31.05.2015 22:35:35
Sepp
Hallo Tim,
kein Problem.
https://www.herber.de/bbs/user/97960.xlsx
Gruß Sepp

Anzeige
AW: Tabellenwerte vergleichen (Urlaubsplaner)
31.05.2015 22:37:48
Tim
Hallo Sepp
Also ich muss schon sagen....Wahnsinn. Vielen lieben Dank. Wie du das jetzt genau gemacht hast muss mich mir noch aneignen. Eventuell finde ich morgen dafür Zeit und würde bei Rückfragen nochmal nachhaken wenn das ok ist?
Gruß Tim

AW: Tabellenwerte vergleichen (Urlaubsplaner)
31.05.2015 22:40:16
Sepp
Hallo Tim,
das war keine besonders schwere Übung. Bei Unklarheiten einfach fragen.
Gruß Sepp

AW: Tabellenwerte vergleichen (Urlaubsplaner)
04.06.2015 14:56:53
Tim
Hallo Sepp
Also ich habe mir das nochmal angeschaut. Komme damit auch zurecht aber verstanden hab ich das immer noch nicht. Könntest du versuchen mir das noch etwas näher zu erklären?
Gruß Tim

Anzeige
AW: Tabellenwerte vergleichen (Urlaubsplaner)
04.06.2015 15:43:15
Sepp
Hallo Tim,
was verstehst du nicht? Das sind doch ganz einfache Formeln und bedingte Formatierung.
Gruß Sepp

AW: Tabellenwerte vergleichen (Urlaubsplaner)
04.06.2015 20:07:07
Tim
Hallo Sepp
Um genau zu sagen, ist mir das mit dem Indirekt und der Anzahl2 im Namensmanager noch ein Rätsel. Das daraufhin mit dem Summenprodukt gearbeitet wird, erschließt sich mir dann evlt dann, wenn ich das andere verstanden habe.
Gruß

AW: Tabellenwerte vergleichen (Urlaubsplaner)
04.06.2015 20:52:15
Sepp
Hallo Tim,
INDIREKT() kommt nirgends vor!
Mit
=Tabelle2!$A$2:INDEX(Tabelle2!$A:$A;ANZAHL2(Tabelle2!$A:$A))

wird der Bereich mit den Namen erfasst, die Größe passt sich dabei automatisch an.
Von A2 bis zur Zeile mit den letzten Eintrag, ANZAHL2() liefert die Anzahl(!) der gefüllten Zellen.
Die Summenprodukt-Formel gibt entweder 0 aus, wenn Name und Datum nicht in der Tabelle2 auftauchen und 1 wenn die Kombination aus Name und Datum vorhanden ist. Mit der bedingten Formatierung werden dann die Zellen mit einer 1 gefärbt. Diese Formel könnte man auch direkt in der bed. Formatierung anwenden, aber dann wäre es noch unverständlicher für dich gewesen.
Gruß Sepp

Anzeige
AW: Tabellenwerte vergleichen (Urlaubsplaner)
04.06.2015 21:25:15
Tim
Entschuldige bitte Sepp...ich meinte natürlich INDEX.
1. Was mache ich mit der Formel INDEX?
2. Wozu brauche ich dann die Anzahl der gefüllten Zellen mit ANZAHL2?
Das mit dem Summenprodukt habe ich jetzt aber verstanden. Dankeschön
Gruß Tim

AW: Tabellenwerte vergleichen (Urlaubsplaner)
04.06.2015 22:31:47
Sepp
Hallo Tim,
der Bereichsname "_name" erfasst den Bereich in Tabelle2 in dem die Namen der Personen stehen.
Diesen Bereichsnamen kann man natürlich auch fix setzen (=Tabelle2!$A$2:$A$1000), das hat aber den Nachteil, das der Bereich unnötig groß dimensioniert ist und viele Leerzellen enthält, oder aber irgendwann zu klein bemessen ist und die Formeln die sich darauf beziehen ein falsches Ergebnis errechnen.
Man kann so einen Bereichsnamen auch per Fomel erstellen, so das er sich dynamisch an die tatsächliche Größe bzw. an die Menge der vorhandenen Daten anpasst. Genau das habe ich gemacht.
"=Tabelle2!$a$2" ist die erste Zelle des Bereiches und fix.
Zum Teil ":INDEX(Tabelle2!$A:$A;ANZAHL2(Tabelle2!$A:$A))": "INDEX(Tabelle2!$A:$A" umfasst alle Zellen aus Spalte A, mit "ANZAHL2(Tabelle2!$A:$A)" erhalten wir eine Nummer die eben die Anzahl der gefüllten Zellen in Spalte A ermittelt. Wenn diese Nummer nun z. B. 20 ergibt, verweist INDEX() eben auf die 20te Zelle in Spalte A, also A20. Unser Bereich geht also von A2 bis A20, ganz so als würden wir A2:A20 schreiben. Wenn nun ein neuer Name dazu kommt, dann eben bis A21 usw.
Gruß Sepp

Anzeige
AW: Tabellenwerte vergleichen (Urlaubsplaner)
05.06.2015 15:33:49
Tim
Hallo Sepp
Das muss ich mir noch zwei mal zu Gemüte führen und dann hab ich das verstanden. Vielen lieben Dank nochmals!
Gruß und schönes Wochenede
Tim

299 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige