Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

SVERWEIS | mehrere Treffer untereinander auflisten lassen
#1
Hallo Liebes Forum,

ich habe ein erneutes Problem. Ich bin zuversichtlich, dass mir doch bestimmt jemand helfen kann :)

Situation:

Ich möchte gerne eine Abfrage via SVERWEIS durchführen, doch ist bekannt, dass bei einem einfachen SVERWEIS bei einem "Treffer" nur einen Wert zurück gibt.

Nun möchte ich gerne, dass bei einem Suchkriterium alle Treffer untereinander aufgelistet werden. In was für einer Reihenfolge, ist zunächst irrelevant.

Im Anhang könnt ihr die Tabelle einsehen. Könnte mir außerdem jemand sagen wie ich in dem Thread hier die Tabelle hätte nachstellen können, sprich das Attachement vermeiden.

Danke schon mal vorab.^



Gruß,
Tim



Angehängte Dateien Thumbnail(s)
   
Antworten Top
#2
Hola,

da keine Tabelle zu sehen ist, hier mal als Einstieg:

http://excelformeln.de/formeln.html?welcher=28

Gruß,
steve1da
Antworten Top
#3
Aus dem Link werde ich leider nicht schlau zumindest hier das Attachment    
Antworten Top
#4
Hola,

Code:
=WENNFEHLER(INDEX($C$4:$C$12;AGGREGAT(15;6;ZEILE($C$4:$C$12)-3/($B$4:$B$12=$H$3);ZEILE(A1)));"")

Gruß,
steve1da
[-] Folgende(r) 2 Nutzer sagen Danke an steve1da für diesen Beitrag:
  • Timi,
Antworten Top
#5
Moin Steve1da,


vielen Dank für die hilfreiche Antwort!

Die Formel funktioniert einwandfrei, nur hab ich ein kleines Verständnis problem bzgl. der Formel.

Ich verstehe soweit alles, außer die Rot markierten Bereiche. Könntest du mir hier wohl zum Verständnis erklären, was dort genau passiert?

Code:

=WENNFEHLER(INDEX($C$4:$C$12;AGGREGAT(15;6;ZEILE($C$4:$C$12)-3/($B$4:$B$12=$H$3);ZEILE(A1)));"")

Danke nochmal und Gruß,
Tim
Antworten Top
#6
Hallo Tim,

zunächst zu Deinen Fragen:
- In der Formel wird ZEILE(A1) wie ein Zähler genutzt, denn in der ersten Ergebniszeile ist ZEILE(A1) =1 in der zweiten 2 usw.
- mit -3 wird in der Formel berücksichtigt, dass in Deinem Beispiel die auszuwertenden Daten nicht in Zeile 1 sondern erst in Zeile 4 als 1. auszuwertende Datenzeile beginnt.


Ich rate Dir jedoch für Deine momentane Beispieldaten (bei denen ich aus den Bilddaten nicht erkennen kann, ob sie "Als Tabelle formatiert" sind oder nicht; letzteres nehme ich an) jedoch zur folgenden Formel in G7:

PHP-Code:
=WENNFEHLER(INDEX(C:C;AGGREGAT(15;6;ZEILE(B$4:B$99)/(B$4:B$99=H$3);ZEILE(B4)-ZEILE(B$3)));""
und diese weit genug nach unten kopieren.

Dies hat folgende Vorteile:
-  durch ... ZEILE(B$4:B$99)/(B$4:B$99=H$3)... ist sie in der Lage auch künftige Datenerweiterungen ohne Formeländerung zu berücksichtigen
-  für mich wäre noch wichtiger ist: die Formel ermittelt auch dann noch die richtigen Ergebniswert, wenn Du später aus anderen Gründen  z.B vor der Zeile 3 eine oder mehr Zeilen löschst oder einfügst oder Du die Spalte A löschst.

Das wird dadurch realisiert, dass jetzt nicht mehr absolut -3 in der Formel verankert wird, sondern
- dass anstelle ... INDEX($C$4:$C$12 ... nun INDEX(C:C ... und
- anstelle ZEILE(A1) nun der Zähler mittels ZEILE(B4)-ZEILE(B$3) erst gebildet wird.

Noch besser wäre es, wenn Du Deine auszuwertende Datenliste mit der der Funktion "Als Tabelle formatieren" in eine "intelligente" Tabelle formatierst und dafür dann die Auswertungsformel definierst. Dann bedarf es auch nicht mehr ... EILE(B$4:B$99)/(B$4:B$99=H$3) ... sondern wird durch diese dann automatisch berücksichtigt.
Gruß Werner
.. , - ...
[-] Folgende(r) 2 Nutzer sagen Danke an neopa für diesen Beitrag:
  • Timi,
Antworten Top
#7
Vielen Dank schon mal für die zahlreichen und auch schnellen Antworten!

Ich hätte ein weiteres Anliegen bzgl. der Abfrage.
Eine weitere Aufgabe wäre für mich, eine Stammdatei aller existierenden IDs (oder Artikelnr.) anzulegen und manuell die Ergebnisse (generiert durch die Abfrage) für jeden Artikel anzulegen. Da es mehr als 1000 Artikelnummern gibt ist dies alles andere als effizient. Hier wäre eine "automatische" Abfrage + Einfügen in die Stammdatei optimal für mich!

Hier eine genauere Erläuterung der weiteren Aufgabenstellung:

Es gibt X viele Artikelnummern, jede Artikelnummer hat mindestens 1 Ergebnis und maximal X Ergebnisse.
Eine Stammdatei habe ich bereits angelegt, nur weiß ich nicht wie ich die Abfrage in die Stammdatei automatisch einfüge. Aussehen könnte es ungefähr wie im Anhang, allerdings gehe ich davon aus, dass jemand ein besseres Format in Planung für seine individuelle Idee hat.

Wenn jemand eine Idee hätte diese Aufgabenstellung zu lösen, wäre ich sehr dankbar, wenn man diese hier teilen würde.


Danke und Gruß,
Tim


Angehängte Dateien Thumbnail(s)
   
Antworten Top
#8
Hallo,

lade bitte eine Beispieldatei, die vom Aufbau her deinem (geplanten) Original gleichen muss, hoch. Bilder eignen sich nicht für adäquate Hilfe.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#9
(17.07.2019, 08:23)WillWissen schrieb: Hallo,

lade bitte eine Beispieldatei, die vom Aufbau her deinem (geplanten) Original gleichen muss, hoch. Bilder eignen sich nicht für adäquate Hilfe.

Der Aufbau muss dem Original auf keinem Fall gleichen. Ich kann die Stammdatei individuell dem Lösungsansatz anpassen.


Aber trotzdem lade ich gerne eine Beispieldatei hoch :D

Hier jetzt folgende Situation:

Auf dem 1 Tabellenblatt haben wir die Abfrage.

Auf dem 2 Tabellenblatt habe ich eine Stammdatei angelegt und wie es aussehen kann wenn man die "Ergebnisse" durch die Abfrage manuell einfügt, hier der Nachteil, bei mehreren Ergebnissen muss jeweils eine neue Zeile eingefügt werden

Auf dem 3 Tabellenblatt ein anderes Format. Hier werden die Ergebnisse der Abfrage hintereinander in Spalten eingefügt.


Angehängte Dateien
.xlsx   Beispiel3.xlsx (Größe: 17,05 KB / Downloads: 9)
Antworten Top
#10
Hallo, kannst du, darfst Du, willst du Power Query einsetzen? Das steht ab XL2010 in bestimmten Versionen als kostenloses Add-Inn zum Download bereit. Ein Ergebnis könnte so aussehen..:

Arbeitsblatt mit dem Namen 'Tabelle4'
ABCD
1IDErgebnis.1Ergebnis.2Ergebnis.3
21234567527
398765420
498732125
53219872512
665498726
712378913
89164573
952406736
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
Gruß Jörg
ich muss mich erst wieder ganz langsam heran robben. Also bitte ich um Nachsicht

"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste