SVerweis mehrere Ergebnisse advanced

Bild

Betrifft: SVerweis mehrere Ergebnisse advanced
von: Varus
Geschrieben am: 06.11.2015 15:18:09

Hallo Zusammen,
dies ist mein erster Beitrag im Forum und an dieser Stelle danke für die Aufnahme!
Ich habe ein bisschen Probleme diese Hilfestellung http://www.excelformeln.de/formeln.html?welcher=28
auf mein konkretes Problem zu übertragen.
Ich habe eine Liste "Altes Quartal" und möchte nun eine neue Liste "neues Quartal" unter anderem aus der alten generieren. (Es gibt noch eine weitere Liste die ebenfalls Einfluss auf die neue Liste hat, aber diese lasse ich erstmal außen vor weil es sonst zu unübersichtlich wird).
Das alte Quartal besteht nun aus Projektnummern, die mehrmals vorkommen können, da unterschiedliche Mitarbeiter an dem Projekt arbeiten können. Nun möchte ich die Liste für das neue Quartal aus dem Alten generieren, diese kann aber auch neue Projekte enthalten. Dabei sollen alle Projekte die im neuen Q. vorkommen inklusive Mitarbeiter übernommen werden. Da das manuelle erzeugen eines neuen Quartals sehr viel arbeit ist möchte ich das Verfahren gerne automatisieren. Es gibt maximal 6 Mitarbeiter die an einem Projekt arbeiten können, daher habe ich für jedes Projekt direkt 6 Zeilen reserviert (ich weiß es nicht besser). Nun so wie ich es hier dargestellt habe funktioniert es soweit auch, allerdings kann ich die Funktion nicht ohne weiteres runter ziehen. Ich muss bei jedem neuen Projekt an letzter Stelle die Zeile wieder auf "A1" korrigieren was bei großen Datensätzen sehr mühselig ist.
Hier mal ein Bild von der Tabelle wie schematisch aussehen könnte.
Userbild
Ich hoffe ihr versteht mein Problem und könnt mir weiter helfen. Vielen Dank schonmal!
Gruß Varus

Bild

Betrifft: AW: AGGREGAT(), INDEX() und WENNFEHLER() ...
von: ... neopa C
Geschrieben am: 06.11.2015 16:22:05
Hallo Varus,
... in zwei Formeln ohne das Daten umgestellt /sortiert etc werden müssen. Formeln nach unten kopieren. Die 99 in der Formel auf den max notwendigen (!; nicht möglichen) Bereich anpassen:

 ABCDE
1AQMA NQMA
2123a 123a
3123b 123b
4123c 123c
5123bb 123bb
6555g 123 
7555h 123 
8555xx 555g
9888d 555h
10999tz 555xx
11999sh 555 
12999ff 555 
13   555 
14   999tz
15   999sh
16   999ff
17   999 
18   999 
19   999 
20     

Formeln der Tabelle
ZelleFormel
D2=WENNFEHLER(WENN(REST(ZEILE(A1)-1;6); D1;AGGREGAT(15;6;A$2:A$99/(ZÄHLENWENN(D$1:D1;A$2:A$99)=0)/(A$2:A$99>0); KÜRZEN(ZEILE(A6)/6;))); "")
E2=WENN(D2="";"";WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(A$1:A$99)/(A$1:A$99=D2); ZÄHLENWENN(D$2:D2;D2))); ""))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Bild

Betrifft: AW: AGGREGAT(), INDEX() und WENNFEHLER() ...
von: Varus
Geschrieben am: 06.11.2015 17:15:45
Hallo Werner!
Coole Sache du hast mir echt geholfen! Ich glaube aber dass man für die Formel von D2 im letzten Teil "Zeile(A6..." noch mit F4 einfrieren muss (nur ein Ausschnitt):
Tabelle2

 ABCDE
1Altes QuartalMitarbeiter Neues QuartalMitarbeiter
23000123sd 3000123sd

Formeln der Tabelle
ZelleFormel
D2=WENNFEHLER(WENN(REST(ZEILE(A1)-1;6); D1;AGGREGAT(15;6;A$2:A$99/(ZÄHLENWENN(D$1:D1;A$2:A$99)=0)/(A$2:A$99>0); KÜRZEN(ZEILE($A$6)/6;))); "")
E2{=WENN(D2="";"";WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(A$1:A$99)/(A$1:A$99=D2); ZÄHLENWENN(D$2:D2;D2))); ""))}


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Ich glaube es funktioniert jetzt, vielen Dank dafür! Wenn du lust hast würde ich mich über eine kurze Erklärung freuen ;-) (weiß leider nicht so genau was da so passiert).
Gruß Varus

Bild

Betrifft: AW: AGGREGAT()-Formel bedarf keine {}- Eingabe ...
von: ... neopa C
Geschrieben am: 07.11.2015 14:57:17
Hallo Varus,
... Die Du für die E2-Formel benutzt hast. Es ist zwar eine Matrixfunktion(alität)sformel, die braucht aber im Gegensatz zu den ("echten ") Matrixformel eben den Eingabeabschluss nicht!
Was Du mit "Zeile(A6)..." noch mit F4 einfrieren muss" meinst, ist falsch.
Nein, in D2 muss wirklich nur ...ZEILE(A6)... stehen, ohne das $ vor der 6 weil das Vielfache von 6 geteilt durch 6 abgerundet auf eine Ganzzahl für die Auswertung maßgebend für den nächsten Wert ist. Deshalb muss der Bezug von ZEILE(A6) relativ bleiben und nicht absolut gesetzt werden und deshalb beginne ich mit ZEILE(A6) und nicht ZEILE(A1) für den ersten Wert.
Gruß Werner
.. , - ...


 Bild

Beiträge aus den Excel-Beispielen zum Thema "SVerweis mehrere Ergebnisse advanced"