Office: Suchabfrage mit zwei Kriterien über mehrere Spalten

Hallo Forums-Teilnehmer, ich darf mich kurz vorstellen. Mein Name ist Mister Monk und ich werde mit Excel noch bekloppt :-)

Nein, habe ein Problemchen, bei dem mir vielleicht jemand weiterhelfen kann. Ich habe das betreffende Tabellenblatt als Anlage hinterlegt.

Für dieses Tabellenblatt möchte ich nun auf einem zweiten Tabellenblatt eine Abfrage erstellen. Und zwar sollen dabei:

1.) der Tag (z.B. 29.03.)
2.) eine Zahl (mind. verfügbare Betten)

als Suchabfragekriterien eingegeben werden können. D.h. das hochgeladene Tabellenblatt soll für die jeweilige Eingabe am Tag x nach mindestens y verfügbaren Betten durchsucht werden (das sind die jeweils grün hinterlegten Zellen). Bei Treffer soll die ID des dazugehörigen Objektes (Spalte A) angezeigt werden.

Ist so was möglich, wenn ja, wie??? Habe schon mit verschiedenen Optionen herum probiert und mir gefühlte 5,8 Mio. Haare ausgerissen :-)

Für Eure Hilfe wäre ich sehr dankbar!!!

Wünsche allen ein schönes Wochenende,

Mister Monk

Ich hoffe, dass ich mich verständlich ausgedrückt habe...

 

Hallo,
mit diesem Tabellenaufbau sehe ich schwarz. Mit einem anderen Tabellenaufbau und einem Makro müsste man das schon hinkriegen.
Kannst du deinen Tabellenaufbau in etwa so umbauen?

 

Hallo mister.monk,
um die kniffelige Suche in den Zeilen für die Objekte zu ermöglichen, habe ich eine kleine VBA-Funktion gebastelt, wie folgt:

Code:

Option Explicit

Function GenugFrei(Anz&, Rg As Range) As Long
  Dim Zl As Long
  For Zl = 1 To Rg.Rows.Count
    If Rg.Cells(Zl, 1) >= Anz& Then GenugFrei = Zl: Exit Function
  Next Zl
  GenugFrei = -1
End Function

1) Obigen Code mit der Maus selektieren, Tasten Strg+C drücken, nach EXCEL wechseln, dort die Tasten Alt+F11 (VBA-Code-Editor) drücken, im Menü 'Einfügen - Modul' klicken; anschließend in den Code-Editier-Bereich klicken und die Tasten Strg+V drücken, um den kopierten Code dort einzufügen.
2) Zurückwechseln ins EXCEL-Arbeitsblatt 'Tabelle1', wo die Objektverwaltung enthalten ist.
3) In diesem zB. Zelle B25 für den gewünschten Tag vorsehen, dort zB. 28.03.2008 eingeben;
Zelle B26 für die Anzahl benötigter Betten vorsehen, dort zB. 6 eingeben
4) In Zelle B27 folgende Formel eingeben: '=VERGLEICH(" Zimmer/Betten "&TEXT(B25;"TT.MM.JJJJ");$D$1:$IS$1;0)+3'
Diese Formel ermittelt die Nummer der 1.Spalte für einen bestimmten Tag (für den 28.3.2008 ist das Spalte 29)
5) In Zelle B28 folgende Formel eingeben: '=GenugFrei(B26;BEREICH.VERSCHIEBEN(A1;3;B27+1;12;1))'
Diesel Formel durchsucht den grünen Zellbereich für den fraglichen Tag und gibt die Zeilennummer desjenigen Objektes zurück, für das, beginnend bei Zeile 4, als erstes genügend freie Betten gefunden wurden.
6) In Zelle A29 folgende Formel eingeben: '=INDEX(A4:A15;B28)' um die Objekt-ID des gefunden Objektes anzuzeigen
7) In Zelle B29 folgende Formel eingeben: '=INDEX(B4:B15;B28)' um den Objekt-Namen des gefundenen Objektes anzuzeigen.
8) Waren irgendwelche Suchmanöver nicht erfolgreich, steht in Zelle A29 bzw. B29 ein Fehlerwert.

Einige Anmerkungen zum Schluss: da EXCEL2003 maximal 255 Spalten darstellen kann, können auf diese Art maximal 10 Tage untergebracht werden; eine vertikale Anordnung der Tage wäre daher vermutlich dieser horizontalen vorzuziehen.
Ein weiteres unsicheres Element für die Suche ist der Umstand, dass die einzelnen Tagesdatums-Angaben eine möglicherweise unsichere Formatierung aufweisen (1 Leerzeichen zu Beginn, Tage und Monate mit 2 Ziffern, Jahre 4-stellig); eine kleine Abweichung bewirkt, dass dieser Tag von der Suchformel in Zelle B27 nicht gefunden wird.

Gruß Exl121150

 

Daaaaaaaaanke!!!!!!!!!

Für mich seid ihr Götter - vielen vielen Dank, damit habt ihr mir wirklich unglaublich weiter geholfen. Vor allem die VBA-Lösung funktioniert ausgezeichnet!

Dass eine andrere Formatierung der Tabelle evtl. mehr Sinn machen würde ist mir auch klar. Nur ist das in dem von mir zu betreuenden Projekt so eine Sache, da mir die Tabelle von anderer Stelle in dieser Formatierung vorgegeben wird. Die 256 Spalten reichen exakt dafür aus, von dem her ist es ok.

Nochmals vielen Dank und einen schönen 2. Advent weiterhin.

PS. Kennt ihr eine gute Seite, auf der man so eine Art "VBA-Crash- oder Einführungskurs" aufrufen kann??? Gibt es sowas???

Mister Monk

 

wie jetzt aber mehrere Treffer anzeigen lassen?

Ich nochmal... :-)

Letzte Frage diesbezüglich, versprochen :-) Die Sache mit Deinem Makro funktioniert prima. Nur wirft mir diese Möglichkeit nur einen Treffer aus. Wenn ich nun mehrere Hotel-IDs untereinander angezeigt bekommen möchte, die die Suchkriterien erfüllen, was muss ich dazu am Makro, bzw. an den Formeln verändern??

Vielen Dank schon mal!!!

Mister Monk

 

Hallo mister.monk,

um mehrfache Suchresultate zu erhalten, muss der VBA-Code folgendermaßen ausschauen:

Code:

Option Explicit

Function GenugFrei(Anz&, Rg As Range) As Variant()
  Dim Zl As Long, Treffer() As Variant, I As Long
  On Error GoTo Err_GenugFrei
  ReDim Treffer(1 To 20)
  I& = 0
  For Zl = 1 To Rg.Rows.Count
    If Rg.Cells(Zl, 1) >= Anz& Then
      I& = I& + 1
      Treffer(I&) = Zl
    End If
  Next Zl
  Treffer(I& + 1) = Error(9)
  ReDim Preserve Treffer(1 To I& + 1)
  GenugFrei = WorksheetFunction.Transpose(Treffer)
  Exit Function
Err_GenugFrei:
  ReDim Preserve Treffer(1 To I& + 10)
  Resume
End Function

1) Obigen Code mit der Maus selektieren, Tasten Strg+C drücken, nach EXCEL wechseln, dort die Tasten Alt+F11 (VBA-Code-Editor) drücken, im Menü 'Einfügen - Modul' klicken; anschließend in den Code-Editier-Bereich klicken und die Tasten Strg+V drücken, um den kopierten Code dort einzufügen.
2) Zurückwechseln ins EXCEL-Arbeitsblatt 'Tabelle1', wo die Objektverwaltung enthalten ist.
3) In diesem zB. Zelle B25 für den gewünschten Tag vorsehen, dort zB. 28.03.2008 eingeben;
Zelle B26 für die Anzahl benötigter Betten vorsehen, dort zB. 6 eingeben
4) In Zelle B27 folgende Formel eingeben: '=VERGLEICH(" Zimmer/Betten "&TEXT(B25;"TT.MM.JJJJ");$D$1:$IS$1;0)+3'
Diese Formel ermittelt die Nummer der 1.Spalte für einen bestimmten Tag (für den 28.3.2008 ist das Spalte 29)
5) In Zelle C28 folgende Formel eingeben: '=GenugFrei(B26;BEREICH.VERSCHIEBEN(A1;3;B27+1;12;1))'
Im Gegensatz zur 1. Lösung muss diese Formel als Array-Formel eingegeben werden. Da es 12 mögliche Objekte gibt, die gefunden werden können, bitte den Zellbereich C28:C39 markieren; daraufhin muss in der Eingabezeile die gerade eingegebene Formel zu sehen sein; in diese Eingabezeile mit der linken Maustaste klicken, sodass dort irgendwo der Eingabe-Cursor zu sehen ist; daraufhin die Tasten Strg+Umschalt+Eingabe drücken. Nach erfolgreicher Betätigung dieser Tasten müsste die Formel eingeschlossen von geschweiften Klammern zu sehen sein.
Diesel Formel durchsucht den grünen Zellbereich für den fraglichen Tag und gibt die Zeilennummern derjenigen Objekte zurück, für die, beginnend bei Zeile 4, genügend freie Betten gefunden wurden.
6) In Zelle A28 folgende Formel eingeben: '=INDEX(A$4:A$15;$C28)' um die Objekt-ID des 1. gefunden Objektes anzuzeigen
7) Diese Formel in die Zelle B28 kopieren, um den Objekt-Namen des 1. gefundenen Objektes anzuzeigen.
8) Die Formeln im Bereich A28:B28 in den Bereich A29:B39 kopieren, um die Objekt-IDs und die Objektnamen der weiteren 11 möglichen Objekte anzuzeigen.
9) Waren irgendwelche Suchmanöver nicht erfolgreich, stehen in den Zellen A28:C39 Fehlerwerte.

Gruß Exl121150

 

Hallo mister.monk,

es gibt auch eine Lösung ohne VBA-Code, nur mit Excel-Arbeitsblattfunktionen:
1) Zellen B25:B27 wie bereits oben anlegen:
Zelle B25: gewünscheter Tag (zB. 28.03.2008)
Zelle B26: Anzahl benötigter Betten (zB. 6)
Zelle B27: Formel: '=VERGLEICH(" Zimmer/Betten "&TEXT(B25;"TT.MM.JJJJ");$D$1:$IS$1;0)+3'
2) In Zelle C28 folgende Formel zuerst in die Zelleditor-Zeile eingeben:
'=KKLEINSTE(WENN(BEREICH.VERSCHIEBEN($A$1;3;$B$27+1;12;1)>=$B$26;ZEILE($1:$12));ZEILE($A1))' und anschließend die Tasten Strg+Umschalt+Eingabe drücken, sodass in der Zelle eine in geschweiften Klammern eingeschlossene Formel zu stehen kommt.
3) In Zelle B28 folgende Formel eingeben: '=INDEX(B$4:B$15;$C28)'
4) Die Zelle B28 in die Zelle A28 kopieren
5) Den Zellbereich A28:C28 in den Zellbereich A29:C39 kopieren.
Das müsste das gleiche Ergebnis erzielen wie meine vorherige Lösung.