Frage von bemerkenswert, 145

Wenn eine bestimmte Zeile einen Wert enthält, dann die komplette Spalte woanders angeben?

Huhu.

Mein Beispiel:

AA6 Produktname, AB6 Artikelnummer, AC6 Fertigung.

In einer Extra-Zeile steht die Formel

=WENN(ISTLEER(AC6);"";MONAT(AC6))

Nun steht dort zum Beispiel "1" - für den Monat Januar, weil die Fertigung im Januar war. Dies ist nun eine fortlaufende Tabelle, das heißt, dass es mehrere Fertigungen im Januar gibt.

Nun soll es eine andere Extra-Zeile geben, die mir anzeigt, welche Spalte jeweils mit Produktname, Artikelnummer und Fertigung im Januar waren. Also dass es quasi die komplette Zeile übernimmt.

Ist dies umsetzbar?

Besten Dank.

Expertenantwort
von Oubyi, Community-Experte für Excel, 79

Ein ähnliches Problem habe ich hier schon gelöst (hoffe ich):
https://www.gutefrage.net/frage/aufzaehlung-excel-nach-namen?foundIn=list-answer...
Bezogen auf Deine Tabelle könnte das so aussehen:
Angenommen Die Monats-Formel steht in AD6 und folgende, also Spalte AD, dann diese Formel und drei Zellen nach rechts kopieren und dann alles soweit wie nötig nach unten kopieren:

{=WENNFEHLER(INDEX(AA$1:AA$1000;KKLEINSTE(WENN($AD$6:$AD$1000=1;ZEILE($AD$6:$AD$1000));ZEILE(A1)));"")}

**ACHTUNG!**
Das ist eine Matrixformel.
Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben, sondern die Eingabe der Formel NICHT mit ENTER abschließen, sondern mit:
STRG & SHIFT & ENTER (alle drei gleichzeitig).
DAS erzeugt die { } und macht die Formel zu einer Matrixformel.

Die 1 in der Formel ($AD$6:$AD$1000=1) für den Januar, kannst Du auch durch einen Zellbezug ersetzen.
Also z.B. durch
...$AD$6:$AD$1000=$AF$6...
und dann in die Zelle AF6 die Nummer für den Monat, also z.B. 1, schreiben.

Kommst Du klar?

P.S.: Ach ja, die Spalte, in der die Formel das Datum aus AC anzeigt musst Du nach dem Einfügen der Formeln noch mal als Datum formatieren, sonst wird Dir nur 42041 oder sowas angezeigt.


Kommentar von bemerkenswert ,

#NAME? kommt dann leider bei raus. Glücklicher Weise ist die Monats-Formel in AD6, daher müsste das ja eigentlich so passen. Aber scheint nicht der Fall zu sein.

Kommentar von Oubyi ,

Kann es sein, dass Du noch ein sehr altes Excel hast, also z.B. Excel 2003?
DAS kannte die Funktion WENNFEHLER noch nicht.
[#Name zeigt an, dass Excel einen Begriff nicht interpretieren kann]
Dann teste mal diese Formel. Die ist erstmal ohne Fehlererkennung, was dazu führt, dass am Ende der Liste nur noch #ZAHL ausgegeben wird, statt Leerfelder.

{=INDEX(AA$1:AA$1000;KKLEINSTE(WENN($AD$6:$AD$1000=$AF$6;ZEILE($AD$6:$AD$1000));ZEILE(A1)))}

Wenn das klappt, kann ich Dir auch noch eine Fehlererkennung für Excel 2003 drumherum bauen. Das sieht dann etwas monströser aus, ist aber auch kein Problem.

Falls Du 2003 hast, solltest Du das in Zukunft bei Fragen immer angeben, da es in den aktuellen Versionen viel neue Funktionen gibt, die in 2003 noch nicht integriert waren.

Kommentar von bemerkenswert ,

Top, das klappt schon mal. Ich denke, ich kann damit erstmal was anfangen. Ich melde mich hier in dem Kommentar wieder, wenn diesbezüglich noch was passiert und ich Hilfe brauche.

PS Merke ich mir, gebe es beim nächsten mal immer an!

Vielen Dank.

Kommentar von Oubyi ,

Freut mich, dass ich helfen konnte.
Ich muss jetzt auch erstmal zur Arbeit, schaue aber bestimmt morgen nochmal vorbei.

Expertenantwort
von Iamiam, Community-Experte für Excel, 78

Wenn Dein gefragter Monat -egal. ob über Formel oder direkt eingegeben- in M2 steht (M für Monat), dann folgende Formel in zB M3:

Achtung: Dies ist eine Matrixformel! Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination (gleichzeitig) Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder!). Die {Klammern} entstehen dann automatisch.

Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken! 

{=INDIREKT(ADRESSE(KKLEINSTE(WENNFEHLER(VERGLEICH($M$2&TEXT(ZEILE($C$1:$C$11);"0000000000");$C$1:$C$11&TEXT(ZEILE($C$1:$C$11);"0000000000");0);10^7);ZEILEN(M$3:M3));SPALTE(C$1)))}

Das gibt dir zunächst mal nur den Monat nochmals wieder. Du kannst diese Formel aber nach links, rechts  und nach unten kopieren, dann erhältst Du die entsprechenden Werte (nach Zeilen geordnet)

Du kannst auch M2:M3 auf ein anderes Blatt versetzen und/oder verschieben

Kommentar von Iamiam ,

Da meine derzeitige Rechnerkrücke wegen eines Windows-Updates extrem langsam wurde, hab ich die AW schon mal unkorrigiert weggeschickt.

Du musst die 11 natürlich durch zB 1111 oder vllt auch 55555 etc ersetzen, A durch AA, B durch AB, C durch AC ersetzen.

Zur Erklärung der Formel:

Wenn mehrere/viele gleiche Nennungen individuell zu finden sein sollen, muss man sie unterscheidbar machen. Das geht in dem von Dir wahrscheinlich geforderten Umfang nur mit einer Matrixformel (kannst du dir so vorstellen, dass da im Arbeitsspeicher eine/mehrere Spalten angelegt werden, die die Formelzwischen-ergebnisse (hier Eintrag+Zeile) enthalten.

(Für Näheres empfehle ich  Peter Haserodt, gugl mal danach, wenn Dich die Möglichkeiten überzeugen)

Die Zelle mit dem Abfragemonat und die Basisformel kannst Du am besten gemeinsam- beliebig verschieben. Kopieren nach links hat nach 2 Spalten das Linit A$1 erreicht, bei AC gehts dementsprechend viel weiter..

Nach-unten-kopieren liefert Verwertbares, solange noch Einträge mit dem Suchargument zur Verfügung stehen, unterhalb gibts einen Fehler, das signalisiert: keine weitere Nennung.

Die Fehlermeldungen der Matrix (alle Einträge, die nicht der Monatszahl entsprechen) werden mittels Wennfehler durch eine große Zahl (größer als maximal mögliche Zeilenzahl) ersetzt.

Eine Besonderheit ist vllt noch das K-Argument in KKleinste. Das ist eine Laufzahl, die durch die Zeilenzahl des Bereichs der Basisformel bis zur Position der Formel gebildet wird, also von 1 -- n.

Falls Du noch Fragen hast, gib eine Bewertungab, damit ich was sehe und die Frage nochmals herhole.

viel Erfolg!

Kommentar von bemerkenswert ,

Ich versuche mal mein Glück! Danke schon mal im Voraus!

Antwort
von JayBo78, 81

Nun wenn das Wort Monat zusätzlich dort stehen soll, dann vielleicht so.

=WENN(ISTLEER(AC6);"";"MONAT "& (AC6))

Kommentar von bemerkenswert ,

Nein, das hast du vielleicht missverstanden. Die Extrazeile sieht sowieso niemand, daher ist es egal, ob da "1" oder "Januar" steht. Mir geht es darum, dass wenn "1" in der Zeile vorhanden ist in dieser Extra-Spalte, dass es dann in einer weiteren Extraspalte mir die komplette Zeile "kopiert" bzw mir dort wiedergibt.

Kommentar von JayBo78 ,

Alles in einer Zelle zusammenfassen kann mit dem Zeichen & 

Z.B. =WENN(ISTLEER(AA6);"";AB6 &", "&AC6&", Monat "&AC6)

AA= Produkt, AB = Art. Nr. AC = Fertigung

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten