Office: (Office 2013) Zeile nach mehreren Werten durchsuchen und einen Spaltenwert wiedergeben

Hallo,

um mein Problem etwas zu verdeutlichen, habe ich eine Beispielmappe angehängt.

Ich versuche mich zurzeit an einer Formel, die folgendes können soll:

1. Die Formel soll in der unteren Tabelle dort stehen, wo zurzeit "Datum" in Feld C14 eingetragen ist

2. Es soll zuerst nach dem "SUCHBEGRIFF" innerhalb der oberen Tabelle (Zeile 1) gesucht werden (dieser SUCHBEGRIFF ist eine Spaltenüberschrift)

3. Danach soll in dieser "SUCHBEGRIFF"-Spalte nach Eintragungen gesucht werden. Zum Beispiel den Zahlen 1-6.

4. Immer, wenn jetzt eine dieser Zahlen gefunden wird, soll überprüft werden, ob die Eintragungen der selben Zeile in den Spalten "Wochentag" "Nummer" und "Tageszeit" denen in der unteren Tabelle entsprechen. In meiner Beispielmappe also für den ersten Fall, in dem eine Zahl zwischen 1-6 gefunden wurde (2. Zeile, da dort eine "3" steht), ob
- F2 = C12
- G2 = A15
- H2 = B15
gilt.

5. Wenn alles übereinstimmt, soll der Wert aus der "Datum"-Spalte eingetragen werden. Andernfalls soll die Zelle leer sein.
In diesem Fall würde Zeile 2 rausfallen, da unter Tageszeit "mittags" und nicht "vormittags" steht. Die Formel sollte dann weitersuchen bis eine solche Übereinstimmung gefunden wird (Zeile 5) und dann den entsprechenden Datumswert (B5) eintragen. Oder es eben keine Übereinstimmung gibt und die Zelle somit leer ist.


Falls das eben beschriebene schonmal möglich wäre, wäre das klasse! Jetzt käme jedoch das nächste Problem dazu: Wenn zwei Zeilen vollständig mit den Feldern "Wochentag" "Nummer" und "Tageszeit" übereinstimmen, können natürlich nicht beide "Datum"-Angaben in die eine Zelle geschrieben werden... gibt es hier eine Möglichkeit, dass der 2. übereinstimmende Wert in eine andere Zelle geschrieben wird? Hier z.B. dann in C17.
Vielleicht so, dass die Formel ihre Suche beendet, wenn ein Wert gefunden wurde und eine weitere Formel in der anderen Zelle die Suche ab diesem Punkt weiterführt? Es kann zu maximal 2 Übereinstimmungen kommen.


Wenn ich mich nicht passend ausgedrückt habe, dann bitte ich um Rückfragen und ich versuche es dann noch einmal anders und deutlicher zu erläutern.


Viele Grüße
tosymb

:)

 

Hallo tosymb,

ich habe da ma ne Frage?!?! *eek.gif*

Was willst Du uns damit sagen?

Ich bin mir noch nicht ganz sicher was Du genau erreichen möchtest.
Ich bin bis zum Punkt 3 gekommen, danach verwandelte sich alles in blablabla... *wink.gif*

Bis dahin waren es 3 Dinge die Du aufeinander basierend prüfen wolltest. Was bestimmt irgendwie in eine Formel passt, aber ich bezweifele, das dass dann noch übersichtlich ist und bedauere jetzt schon den der sich das in nem halben Jahr wieder angucken muss, weil Änderungen rein sollen!

Kannst Du bitte noch ein paar Hintergründe erläutern, dass man nachvollziehen kann was genau Du erreichen möchtest!

P.S.: "In der Zelle X1 soll Monatag stehen" reicht da denke ich nicht... *wink.gif*

 

Hallo,

ich versuche es mal in Worte zu fassen, wie ich es im Kopf habe:

1. Ich habe eine "sehr große" Tabelle, aus denen ich die Daten auslesen möchte (ich nenne sie mal "Datentabelle").

2. Ich erstelle eine neue Tabelle, der ich eine bestimmte Überschrift gebe, wobei diese Überschrift auch genau einer Spaltenüberschrift in der Datentabelle entspricht.

3. In der neuen Tabelle befinden sich jetzt 3 verschiedene Werte und eine weitere Zelle, in der die Formel eingefügt werden soll.

4. Die Formel soll nun folgendes tun:
a) Mit Hilfe der Überschrift (die in einer Zelle steht) die entsprechende Spalte in der Datentabelle finden
b) In dieser Spalte nach Zellen suchen, in denen eine Zahl von 1 bis 6 steht.
c) Wird solch eine Zelle gefunden, soll nun die Zeile betrachtet werden, in der diese Zahl steht.
d) Innerhalb dieser Zeile gibt es in bestimmten Spalten Eintragungen, die jetzt mit den 3 verschiedenen Werten (siehe Punkt 3) in der neuen Tabelle verglichen werden sollen.
e) Kommt es bei allen dreien zu einer Übereinstimmung, soll ein weiterer Wert, der sich in einer bestimmten Zelle innerhalb der Zeile befindet, wiedergegeben werden. Falls ein oder mehrere Werte nicht übereinstimmen, soll weiter gesucht werden. Wird kein Eintrag gefunden, der diesen Kriterien gehorcht, soll die Zelle leer bleiben.


So habe ich mir das vorgestellt... Vielleicht ist es so etwas veständlicher? Danach kam dann in meinem Anfangsbeitrag noch eine weitere Frage, was die Formel am besten noch können sollte, aber darüber kann man danach vielleicht noch reden. Das ist erstmal der erste Schritt, falls so etwas verwirklicht werden kann.

Ich würde dann für jede zu betrachtende Spalte eine einzelne Tabelle erstellen, die sich eben ausschließlich auf diese Spalte bezieht.


Viele Grüße
tosymb

 

Also so wie Du das beschreibst, klingt es für mich so als das es in einer Formel nicht wirklich umsetzbar ist.
Ein solches Problem würde ich über VBA lösen.

In Formeln kannst Du meines Wissens nach keine Schleifen einbringen und damit wäre die Anforderung "weitersuchen" nicht umsetzbar. Somit müsstest Du für jeden potentiellen Treffer eine neue Zelle erstellen. Und selbst dann wäre das meiner Meinung nach nicht praktikabel!

 

Danke für Deine Antwort!

Hättest du denn da eine Idee? Mit VBA kenne ich mich nämlich leider so gut wie gar nicht aus... ich weiß wie ich die Macros verwende, aber schreiben kann ich sowas leider selbst gar nicht.
Eine Formel ist kein Muss, ich dachte nur vielleicht, dass man es damit lösen könnte, aber ich bin für jegliche Lösung dankbar.


Viele Grüße
tosymb

 

Hast Du eine Beispieldatei, die dann dem Aufbau der Datei entspricht mit ein paar anonymisierten Daten drin, dass man testen kann.
Dann kann ich mir das gerne mal anschauen.

 

Hallo,

entschuldige, dass ich erst heute dazu kam.
Ich habe mal eine Beispielmappe vorbereitet, die meiner eigentlichen ziemlich gleicht.

Die erforderlichen Daten stehen in der "Datentabelle". Dazu habe ich die "Zieltabelle" für die Spalte "ABC" aufgestellt (so sollte sie dann analog auch für "DEF", "GHI", etc. aussehen).

In der "Zieltabelle" steht nun oben "ABC". Dieser Eintrag soll benutzt werden, um in der "Datentabelle" zu suchen.
Dort befindet sich "ABC" als Überschrift in der Spalte "D".

Nun soll in dieser Spalte nach Eintragungen von 1-6 gesucht werden (die Zeilen mit dem "a" sollen also nicht beachtet werden). In diesem Beispiel sollte dann in Zeile 2 die Zahl "3" gefunden werden. Ab hier geht es dann weiter:
Jetzt soll der "Wochentag", die "Wochennummer" und die "Tageszeit" mit den Eintragungen in der "Zieltabelle" verglichen werden. In diesem Beispiel steht in Zeile 2:
- Wochentag: Montag
- Wochennummer: 1
- Tageszeit: vormittags

In der Zieltabelle soll nun der "Name", das "Datum", die "Uhrzeit", die "Adresse" und der "Leiter" in die passenden Felder eingetragen werden. Das wären für die Daten oben also die Felder ganz oben links.

So soll dann weiter vorgegangen werden und die vollstände "Zieltabelle" ausgefüllt werden. Felder, für die es keine Werte in der "Datentabelle" gibt, sollten leer sein.


In den Spalten "DEF", etc. stehen extra keine Werte, damit es nicht noch unübersichtlicher wird... diese wären sonst nach dem Schema wie in "ABC" ausgefüllt und würden auch jeweils eine eigene "Zieltabelle" bekommen.



Wäre so etwas irgendwie möglich? Falls nicht: Vielleicht wäre es in Einzelschritten annähernd möglich?


Viele Grüße
tosymb

 

Guten Morgen tosymb,

soweit habe ich das jetzt verstanden und mir ist klar was Du möchtest.

Ein paar Fragen habe ich allerdings noch, was gibt dir das ABC an? Und wie entscheidest Du nach welcher der Zahlen in ABC gesucht werden soll?
In den Basisdaten, können mehrere Alternativen für ein Feld vorkommen, also kann es 2 Personen geben die an einem Tag vormittags eingeteilt werden können oder gibt es immer nur eine Person?
Wenn es mehrere sind, wie soll entschieden werden, welche Person ausgewählt wird?

 

Also mein erster Ansatz wäre doch ohne VBA über "SVERWEISE" mit mehreren Kriterien, da jede Zelle genau einen Inhalt haben soll.

Nun muss geklärt werden, wie Du entscheidest, welche Person jeweils eingetragen werden soll, sprich über die Auswahl aus der Spalte ABC, wo steht dieser Wert in deiner Zieltabelle oder wie wird entschieden, welcher Wert der richtige für eine entsprechende Position ist?

Als Ansatz:
=INDEX(Datentabelle!$L:$L;VERGLEICH(3&$A4&$B4;Datentabelle!$D:$D&Datentabelle!$J:$J&Datentabelle!$K:$K;0))

=> als ArrayFormel abschließen!!!

L:L -> Datumsspalte -> Suchspalte
3 -> momentan fixe Auswahl Spalte ABC
A4 -> Kalenderwoche
B4 -> Tageszeit
Datentabelle!D : D -> Spalte ABC
Datentabelle!J : J -> Spalte Wochentag
Datentabelle!K : K -> Spalte Tageszeit

 

Guten Morgen,

danke für Deine Antwort und, dass du Dir so viel Zeit für mein Problem, das ich so schwierig in Worte fassen kann, nimmst.

Es kann zu maximal zwei Überschneidungen in allen 3 Bedingungen (Wochentag, Wochennummer und Tageszeit) kommen. Daher sind in der Zieltabelle für jede Tageszeit (vormittags, mittags, nachmittags) 2 Blöcke, die ausgefüllt werden müssten (Name, Datum, Uhrzeit, Adresse, Leiter).

Der Wert in "ABC" ist "relativ egal", es soll mit jedem Wert (1 bis 6!) gleich umgegangen werden. Die Zahlen haben also "eigentlich" keine unterschiedliche Bedeutung. "Eigentlich", da sie dafür da sind, dass die Eintragungen in der Zieltabelle dann je nach Nummer eine unterschiedliche Farbe bekommen sollten, aber das wäre dann der nächste Schritt und damit möchte ich jetzt nicht zusätzlich verwirren.

Um es nochmal am Beispiel zu erläutern:
1. Schritt: Es soll in Spalte ABC nach einem Wert von 1-6 gesucht werden.
2. Schritt: Wenn ein Wert gefunden wird, sollen anhand der Angaben unter "Wochentag", "Wochennummer" und "Tageszeit" die anderen Angaben (Datum, Adresse, Leiter, Datum, Uhrzeit) in die passenden Stellen in der Zieltabelle eingefügt werden.

Anschaulicher:
- Es wird in ABC gesucht -> in Zeile 2 steht eine "3" -> also passenden Wert gefunden -> Unter Wochentag, Wochennummer und Tageszeit stehen "Montag", "1" und "vormittags" -> In der Zieltabelle soll also in C4 der "Name" aus Zeile 2 der Datentabelle, in C5 das "Datum", in D5 die "Uhrzeit", etc. eingetragen werden.

- Jetzt soll weiter nach 1 bis 6 in der Spalte ABC gesucht werden -> in Zeile 4 wird eine "2" gefunden -> Hier soll analog wie oben vorgegangen werden -> "Mittwoch", "1" und "mittags" -> in der Zieltabelle sollen also die passenden Werte aus Zeile 4 in I10 ("Name"), I11 ("Datum"), J11 ("Uhrzeit"), K11 ("Adresse") und K12 ("Leiter") eingetragen werden.

- So soll jetzt bis zum Ende der Spalte ABC vorgegangen werden und die Zieltabelle sich somit mit den Daten aus der Datentabelle füllen.

- Leere Einträge oder z.B. Einträge wie das "a" in der ABC-Spalte sollen dabei ignoriert werden, bzw. nicht beachtet werden, da nur nach 1,2,3,4,5 und 6 gesucht werden soll.


Viele Grüße
tosymb

 

Kein Problem, Helfe gerne wo ich kann *Smilie...

Ok, das habe ich soweit verstanden!

Besteht die Möglichkeit, in den Basisdaten noch ein Feld einzuführen, welches angibt, wer der erste und wer der zweite in einem Tagesabschnitt ist?
Dann wäre die Arbeit sehr gering und einfach umsetzbar!!

Hintergrund, Du musst die Datensätze eindeutig machen, da VERGLEICH oder SVERWEIS immer nur das erste Vorkommen findet.

 

So schau mal hier...

die Lösung im Moment kommt ohne VBA aus, aber die Formeln sind teilweise sehr langsam...

In der Datentabelle habe ich jetzt mal ein Feld eingefügt am Ende, was ermittelt, ob eine Person die erste oder zweite im Zeitabschnitt eines Tages ist: Achtung ARRAYFORMEL
=WENN(VERGLEICH(I2&J2&K2;I:I&J:J&K:K;0)=ZEILE();1;2)

darauf basierend kann jetzt nach den gewünschten Datensätzen mit 4 Kriterien gesucht werden:
=INDEX(Datentabelle!$A:$A;VERGLEICH(1&$A4&C$3&$B4;Datentabelle!$N:$N&Datentabelle!$J:$J&Datentabelle!$I:$I&Datentabelle!$K:$K;0))

Hoffe das hilft Dir weiter... So kommst Du zumindest ohne VBA aus... *Smilie

Bei Fragen gern!

 

Danke!

Mit der Extraspalte müsste ich mal schauen... wenn es anders kaum möglich ist, dann muss sie eben rein. *biggrin.gif*

Mit Arrayformeln kenne ich mich peinlicherweise gar nicht aus. Da muss ich mich dann wohl noch etwas einlesen.
Habe gerade einfach mal die Formel für die "Namen" in die ganze Zeile 4 gezogen und diese wurden auch erfolgreich ausgefüllt. Jetzt steht nur leider in den Zellen, für die es in der Datentabelle keine Daten gibt "#NV" -> kann das durch eine einfache "Leerzelle" ersetzt werden, also das dort gar nichts steht?

Und mit der Formel hätte ich so keine Probleme, wenn ich sie für alle anderen Felder verwende (außer vielleicht kleine Veränderungen), bzw. in mehrere Tabellen (für DEF, GHI, usw.) kopiere?


Viele Grüße
tosymb

 

Wenn Du die Formel bearbeitest, muss du die Bearbeitung mit Strg+Shift+Enter abschließen anstatt mit Enter -> dann werden die geschweiften Klammern gesetzt und die Formel als ArrayFormel interpretiert!

Zu ziehen geht die Formel teilsweise. Du musst auf jeden Fall die Verweise prüfen, da Du verbundene Zellen nutzt werden diese nicht 100% sauber gesetzt, da Excel trotzdem weiter zieht, also nach dem ziehen/kopieren wieder die verbundene Zelle komplett setzen. Dann sollten Änderungen kein Problem mehr sein!

 

Also ich glaube, dass ich die Formel einfach nicht verstehe. *frown.gif*

Wo bezieht sie sich denn auf die Spalte "ABC"? Also was müsste ich ändern, wenn ich jetzt die Spalte "DEF" mit einzelnen Werten ausgefüllt hätte (von 1 bis 6) und diese dann in einer "Zieltabelle" darstellen möchte?


Viele Grüße
tosymb