Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
19.04.2024 16:34:52
gemeinsamer Filter über drei Spalten
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1804to1808
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1804to1808
1804to1808
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Matrix mit ersten und letztem Wert

Matrix mit ersten und letztem Wert
15.01.2021 12:56:37
Sven
Hallo zusammen,
ich habe folgendes Problem: in einer Exceltabelle (Link unten) stehen in Spalte A einzelne Aufträge (Pxxxxx). In Spalte B steht die Bezeichnung. Darunter beginnen die einzelnen Arbeitsschritte. In Spalte E und F sind die Zeiten und Mengen hinterlegt, welche jeweils in der letzten Spalte zusammen gerechnet werden. Danach folgt eine Leerzeile, bevor ein neuer Auftrag beginnt.
Ich suche nun nach einer Formel oder einem Makro, dass mit die Auftragsnummer, die Bezeichnung sowie die Gesamtsummen nebeneinander ausgibt, so wie in Zeile 36-38 zu sehen.
Wichtig zu wissen ist, dass originale Tabelle weit aus länger ist und somit mehr als nur die drei Auträge aufweist. Es sind auch immer unterschiedliche Anzahlen an Zeilen, wie man hier schon sieht. Die Bezeichnung vom Auftrag (Pxxxxx) variiert, die Bezeichnung der letzten Zeile "Gesamt" ist immer identisch, falls das hilft.
Ich hoffe, ich habe mich soweit verständlich ausgedrückt und jemand kann helfen.
Im Vorfeld besten Dank!
https://www.herber.de/bbs/user/143029.xlsx

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Matrix mit ersten und letztem Wert
15.01.2021 13:11:00
Rudi
Hallo,
Projektnummer in I2.
J2: =INDEX(E:E;VERGLEICH("Gesamt";INDEX(B:B;VERGLEICH(I2;A:A;)):$B$10000;)+VERGLEICH(I2;A:A;)-1)
Gruß
Rudi
AW: verschiedene Möglichkeiten ...
15.01.2021 13:12:56
neopa
Hallo Sven,
... mein Vorschlag wäre:
In A36: =WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$33)/(A$2:A$33&gt0);ZEILE(A1)));"")
In B36: =WENN(A36="";"";SVERWEIS(A36;A:B;2;0))
In E36: =WENNFEHLER(INDEX(E:E;AGGREGAT(15;6;ZEILE(E$2:E$33)/($B$2:$B$33="Gesamt");ZEILE(E1)));"")
Alle nach unten ziehend kopieren und E36 zuvor auch nach F36
Gruß Werner
.. , - ...
AW: verschiedene Möglichkeiten ...
15.01.2021 14:55:18
Sven
Hallo Werner,
die Formeln funktionieren so sehr gut, danke. Ich habe jetzt noch ein weiteres Problem, woran ich eben nicht gedacht habe:
Die Exceldatei besteht aus fünf Blätter, jeweils eine feste Bezeichnung (MA1, MA2, etc.). Nun würde ich die Formeln jeweils ans Ende setzen und hätte somit auf jedem Blatt die jeweilige Zusammenfassung; soweit top.
Optimal wäre es jedoch, wenn ich das auf einem separatem Blatt untereinander setzen könnte, indem ich in der Formel auf das Blatt "MA1" verweise. In Spalte G lasse ich mir dann der Übersicht halber noch den Blattnamen ausgeben.
Ich bekomme die Formel aber nicht so modifiziert, dass ich auf "MA1" bzw. "MA2" etc. verweisen kann. Kannst Du mir noch einmal helfen?
DANKE
Anzeige
AW: so wie Du schreibst, nutze dazu INDIREKT() ...
15.01.2021 15:01:44
neopa
Hallo Sven,
... bzw. stelle noch einmal eine entsprechende Beispieldatei ein.
Gruß Werner
.. , - ...
AW: so wie Du schreibst, nutze dazu INDIREKT() ...
15.01.2021 15:21:18
Sven
Hallo Werner,
eine Beispieldatei bringt sicher was, da hast Du recht:
https://www.herber.de/bbs/user/143035.xlsx
Darin sind nun vier Blätter: MA1, MA2, MA3 sowie die Auswertung.
Bitte nicht von den Spalten in "Auswertung" irritieren lassen: bei der Auswertung kann ich auf Spalte C+D der Blätter MA1/2/3 verzichten, weshalb ich E+F nach links verschoben habe. Wenn es leichter geht, können sie auch da stehen bleiben.
Anzeige
AW: dann ...
15.01.2021 17:01:28
neopa
Hallo Sven,
... würde ich der Einfachheit halber mit zwei (ausblendbaren) Hilfsspalten arbeiten (hier z.B. H:I)
In Spalte H schreibe Deine Namen untereinander und in I1 eine 0 (wichtig)
Dann kopiere die Formel I2 und die Formel E2 nach unten.
Danach erstelle die Formel in A2. Die Formeln in B2:D2 kannst Du nun in Anologie sicherlich selbst vervollständigen und alle nach unten kopieren
Arbeitsblatt mit dem Namen 'Auswertung'
 ABCDEFGHI
1AuftragProjektAufwand in Std.AnzahlBlattname  MA1
2P77238Projekt Nr. 10,171MA1  MA23
3P77259Projekt Nr. 22,592MA1  MA36
4P77247Projekt Nr. 30,6712MA1   9
5P77238Projekt Nr. 12,1723MA2    
6P77299Projekt Nr. 48,592MA2    
7P77311Projekt Nr. 5934MA2    
8P77238Projekt Nr. 10,171MA3    
9P77322Projekt Nr. 68,676MA3    
10P77333Projekt Nr. 7104MA3    
11         

ZelleFormel
A2=WENNFEHLER(INDEX(INDIREKT(E2&"!A:A");AGGREGAT(15;6;ZEILE(A$2:A$99)/(INDIREKT(E2&"!A2:A99")>0);ZÄHLENWENN(E$2:E2;E2)));"")
E2=INDEX(H:H;VERGLEICH(ZEILE(A1)-1;I:I))&""
I2=WENN(H1="";"";ZÄHLENWENN(INDIREKT(H1&"!A:A");"P*")+I1)
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Gruß Werner
.. , - ...
Anzeige
AW: dann ...
18.01.2021 16:30:49
Sven
Hallo Werner,
ich danke dir und ich weiß, es muss deine Gedult strapazieren... Jedoch komme ich bei den Spalten B, C und D nicht weiter. Irgendetwas mache ich falsch...
In B2 war es: =WENN(A36="";"";SVERWEIS(A36;A:B;2;0))
In C2 war es: =WENNFEHLER(INDEX(E:E;AGGREGAT(15;6;ZEILE(E$2:E$33)/($B$2:$B$33="Gesamt");ZEILE(E1)));"")
Was auch immer gerade falsch läuft, aber ich bekomme diese Formeln nicht so angepasst, dass sie sich indirekt auf den Blattnamen beziehen und so die richtigen Werte ziehen.
Ich hoffe, Du bist noch einmal gewillt, mir zu helfen. Die Datei habe ich der Einfachheit halber auch noch einmal hochgeladen:
https://www.herber.de/bbs/user/143091.xlsx
Danke, Sven!
Anzeige
AW: nun ...
18.01.2021 17:06:12
neopa
Hallo Sven,
... zunächst festgestellt, Du hattest In Spalte E die Formel noch nicht weit genug nach unten ziehend kopiert.
Die Formel in Spalte B ist Dir auch nicht gelungen?
Diese erfordert doch lediglich den richtigen Einsatz von INDIREKT()
Also in B2: =WENN(A2="";"";SVERWEIS(A2;INDIREKT(E2&"!A:B");2;0))
In C2:
=WENNFEHLER(INDEX(INDIREKT(E2&"!E:E");AGGREGAT(15;6;ZEILE(E$2:E$99)/(INDIREKT(E2&"!B2:B99")="Gesamt");ZÄHLENWENN(E$2:E2;E2)));"")
Diese kannst Du so nicht direkt nach rechts in Spalte D kopieren, aber die Anpassung dort sollte Dir nun leicht gelingen.
Gruß Werner
.. , - ...
Anzeige
AW: nun ...
18.01.2021 17:36:54
Sven
Hallo Werner,
ich weiß auch nicht, wo es geklemmt hat... Danke, so sieht es schon nahezu perfekt aus!
Eine (hoffentlich) letzte Frage: es ist leider schon vorgekommen, dass jemand auf einem Blatt (z. B. MA3) etwas zwischen die einzelnen Projekte in Spalte A geschrieben hat, wie ich jetzt einmal simuliert habe:
https://www.herber.de/bbs/user/143094.xlsx
Kann man dies ausklammern, indem man sagt, dass wenn hier keine 6-stellige Kombination (P12345, Pxxxxx, P99999 etc.), dies nicht beachtet werden soll?
Oder könnte die Formel in B, C und D geändert werden? Die Formel in Spalte A zeigt ja alles auf, was in Spalte A steht. Die Formeln in B, C und D suchen ja nicht auf Grundlage von A, sondern nur nach dem Blattnamen...
Anzeige
AW: ja...
18.01.2021 17:59:47
neopa
Hallo Sven,
... dazu musst Du lediglich eine Bedingungserweiterung vornehmen.
In A2:
=WENNFEHLER(INDEX(INDIREKT(E2&"!A:A");AGGREGAT(15;6;ZEILE(A$2:A$99)/(LINKS(INDIREKT(E2&"!A2:A99"); 1)="P")/(LÄNGE(INDIREKT(E2&"!A2:A99"))=6);ZÄHLENWENN(E$2:E2;E2)));"")
Gruß Werner
.. , - ...
AW: ja...
18.01.2021 18:09:50
Sven
Wow... Wo kann ich eine Schulung bei Dir buchen?
DANKE!!!
AW: ist möglich ...
18.01.2021 19:26:03
neopa
Hallo Sven,
... solltest Du dies wirklich wollen, kannst Du mir eine Mail senden, über die wir dazu direkt Kontakt aufnehmen.
Meine Mailadresse bekommst Du, wenn Du in eine leere Standardexcelzelle folgende Formel schreibst: =WECHSELN("neopaCode";"Co";ZEICHEN(64)&"email.")
Solltest Du mir eine Mail senden, dann schreibe da in den Betreff Herbers Excelforum thread; Sven (damit ich Deine Mail darüber erkennen kann).
Gruß Werner
.. , - ...
Anzeige
AW: nun ...
18.01.2021 18:00:18
Sven
Hallo Werner,
ich weiß auch nicht, wo es geklemmt hat... Danke, so sieht es schon nahezu perfekt aus!
Eine (hoffentlich) letzte Frage: es ist leider schon vorgekommen, dass jemand auf einem Blatt (z. B. MA3) etwas zwischen die einzelnen Projekte in Spalte A geschrieben hat, wie ich jetzt einmal simuliert habe:
https://www.herber.de/bbs/user/143094.xlsx
Kann man dies ausklammern, indem man sagt, dass wenn hier keine 6-stellige Kombination (P12345, Pxxxxx, P99999 etc.), dies nicht beachtet werden soll?
Oder könnte die Formel in B, C und D geändert werden? Die Formel in Spalte A zeigt ja alles auf, was in Spalte A steht. Die Formeln in B, C und D suchen ja nicht auf Grundlage von A, sondern nur nach dem Blattnamen...
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige