Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1372to1376
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
Inhaltsverzeichnis

Arrayfunktion gesucht

Arrayfunktion gesucht
06.08.2014 13:50:28
Marc
Hallo Excel Gemeinde,
ist es in Excel möglich, mit Hilfe einer Matchtabelle nach allen zugehörigen Gruppen einer Gruppenbezeichnung zu suchen, und zugehörige Zahlen aus einer Spalte zu addieren ?
Das ganze sollte, wenn möglich, ohne Makro realisiert werden können.
Als Beispiel habe ich eine Beispieldatei angehängt:
https://www.herber.de/bbs/user/91935.xlsx
Die Datei besteht aus den Basisdaten, in denen eine Nummer mit mehreren Zeiten der unterschiedlichen Gruppen vorkommen kann.
Ebenso gibt es eine Matchtabelle, wo die Zuordnung der Gruppen zu den Gruppenbezeichnungen passiert.
Das letzte Tab ist das gewünschte Ergebnis, wo keine Nummer aus den Basisdaten dopplet vorkommen darf, und die addierten Zeiten der Gruppenbezeichnungen ermittelt werden.
Welche Excel Funktion kann mir hier weiterhelfen ?
Vielleicht irgendwas in Kombination von INDEX, VERGLEICH und SUMME ?
Für eure Hilfe sage ich schon einmal vielen Dank im Voraus.
Viele Grüße
Marc

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
mehrfach VERGLEICH() en ...
06.08.2014 14:20:56
neopa
Hallo Marc,
... Formel nach rechts und unten kopieren:
Ergebnis

 ABC
1NummerFensterDach
21111596568
3157101160
4177188530
5199050
62000843

Formeln der Tabelle
ZelleFormel
B2{=SUMME((Basisdaten!$A$1:$A$99=$A2)*(WENN(ISTZAHL(VERGLEICH(VERGLEICH(Basisdaten!$B$1:$B$99&"";Matchtabelle!$A$1:$A$10&"";); (Matchtabelle!$B$1:$B$10=B$1)*ZEILE(A$1:A$10); )); Basisdaten!$C$1:$C$99)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Basisdaten

 ABC
1NummerGruppeZeit
2111A505
3111B568
4111A834
5111D257
6157A8823
7157D1293
8177A18853
9199B50
10200B843
11   


Matchtabelle

 AB
1GruppeGruppenbezeichnung
2AFenster
3BDach
4CFenster
5DFenster
6  


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

Anzeige
AW: mehrfach VERGLEICH() en ...
06.08.2014 16:53:41
Marc
Hallo Werner,
vielen Dank für deine schnelle Hilfe. Die Formel funktioniert super.
Viele Grüße
Marc

AW: mehrfach VERGLEICH() en ...
11.08.2014 08:54:12
Marc
Hallo Excel Gemeinde,
wie würde die Formel aussehen, wenn die Werte nicht wie schon oben benötigt in mehreren Zeilen, sondern in einer Zeile aber mehreren Spalten stehen, siehe angehängte Beispieldatei ?
https://www.herber.de/bbs/user/92008.xlsx
Für jede Gruppe in der Basisdatei gibt es eine eigene Spalte, wo die jeweiligen Zeiten der Gruppe angegeben sind.
Wie wird eine Summe aus allen Gruppenwerten pro Zeile gebildet, welche alle der gleichen Gruppenbezeichnung (z.B. Fenster) angehören ?
Wie genau muss die Summe - Vergleichs Formel in diesem Fall aussehen ?
Vielen Dank für eure Hinweise.
Marc

Anzeige
AW: mehrfach VERGLEICH() en ...
11.08.2014 09:20:51
Marc

wieder VERGLEICH()en, prüfen und summieren ...
11.08.2014 11:42:35
neopa
Hallo Marc,
... Formel wieder nach rechts und unten kopierbar:
Ergebnis

 ABC
1NummerFensterDach
21114020
315760
4177350
5199050
6200097
7   

Formeln der Tabelle
ZelleFormel
B2{=SUMME((Basisdaten!$A$2:$A$9=$A2)*Basisdaten!$B$2:$E$9*ISTZAHL(VERGLEICH(RECHTS(Basisdaten!$B$1:$E$1;1)&"";WENN(Matchtabelle!$B$2:$B$9=B$1;Matchtabelle!$A$2:$A$9&""); )))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


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

Anzeige
AW: wieder VERGLEICH()en, prüfen und summieren ...
11.08.2014 12:46:43
Marc
Hallo Werner,
vielen Dank für die Antwort, welche super funktioniert.
Allerdings ist meine Datenbasis zu groß geworden, sodass ich die Datenbasis optimieren musste, damit die Kalkulation nicht zu lange dauert.
Jetzt gibt es nur noch eine Matchtabelle und ein Ergebnistab mit Auszügen aus der Datenbasis, siehe Beispieldatei.
https://www.herber.de/bbs/user/92013.xlsx
Was ist falsch mit meinem Formelvorschlag für Zelle F2, damit am Ende 40 heraus kommt ?
=SUMME($B2:$E2*ISTZAHL(VERGLEICH($B$1:$E$1;Matchtabelle!$A$2:$A$5&"";));(Matchtabelle!$B$2:$B$5=F$1); )
Viele Grüße
Marc

Anzeige
Du VERGLEICH()st ungleiche Textwerte ...
11.08.2014 13:23:21
neopa
Hallo Marc,
... richtig wäre: {=SUMME($B2:$E2*ISTZAHL(VERGLEICH(RECHTS($B$1:$E$1;1)&"";WENN(Matchtabelle!$B$2:$B$9=F$1;Matchtabelle!$A$2:$A$9&"");)))}
Gruß Werner
.. , - ...

AW: Du VERGLEICH()st ungleiche Textwerte ...
11.08.2014 14:16:54
Marc
Hallo Werner,
Vielen Dank für die Formel, welche meine Auswertung jetzt beschleunigt.
Problem somit gelöst.
Viele Grüße
Marc

AW: Du VERGLEICH()st ungleiche Textwerte ...
12.08.2014 09:36:56
Marc
Hallo Excel Freunde, Hallo Werner,
jetzt gbt es doch noch eine kleine Änderung durch den Einzug einer Zwischenzeile, siehe Beispieldatei.
https://www.herber.de/bbs/user/92034.xlsx
Wie lautet die richtige Formel für z.B. Zelle J3, mit der Beachtung von allen Spalten mit "Zeit_A", unter Beachtung der Matchtabelle.
Die Formel:
{=SUMME($B3:$I3*ISTZAHL(VERGLEICH($B$1:$I$1&"";WENN(($B$2:$I$2="Zeit_A") *(Matchtabelle!$B$2:$B$5=$J$1;Matchtabelle!$A$2:$A$5&"");)))) }
gibt leider 0, und nicht 40 zurück.
Wie muss die Zwischenzeile richtig in die Formel eingebaut werden ?
Viele Grüße
Marc

Anzeige
für die Formel ist das keine kleine Änderung ...
12.08.2014 11:10:03
neopa
Hallo Marc,
... vor allem durch Deine verbundene Zellen in Zeile 1. Aber auch das ist von mir mit Formel lösbar.
Doch bevor ich die Formel konstruiere eine Frage an Dich. Ist mit weiteren Strukturänderungen zu rechnen?
Gruß Werner
.. , - ...

AW: für die Formel ist das keine kleine Änderung ...
12.08.2014 11:17:53
Marc
Hallo Werner,
weitere Strukturänderungen sind nicht geplant.
Viele Grüße
Marc

richtige Berücksichtigung verbundener Zellen ...
12.08.2014 12:44:36
neopa
Hallo Marc,
... erfordert schon etwas mehr von der Formel. Deshalb hab ich der Einfachheit halber in B1:I1 einen "Trick" angewendet, der genau so realisiert werden muss! Dazu hab ich Dir in Zeile 9 eine "Hilfe" gegeben, die Du natürlich nur zum Interpretieren meiner Lösung benötigst.
Hebe zunächst die verbundenen Zellen in B1:I1 auf und schreibe in jede Zelle die Werte wie in B9:I9 und nun übertrage das Format aus hier B10:C10 nach B1:I1.
In J1:M1 ist analoges nicht vorgenommen. Die "Auswirkungen" dessen kannst Du in der Formel (...VERWEIS(9;1/....) erkennen. Wenn ich also in B1:II nicht das erwähnte vorgenommen hätte, wäre die Formel J3 noch viel komplexer geworden.
Die Formel J3 kannst Du wieder nach rechts und unten kopieren:
 ABCDEFGHIJKLM
1NummerABCDFensterDach
2Zeit_AZeit_BZeit_AZeit_BZeit_AZeit_BZeit_AZeit_BZeit_AZeit_BZeit_AZeit_B
3111105209003074012209
4157200000406000
517735200000035200
619900500000000500
720000970000000970
8             
9 AABBCCDD    
10 A          

Formeln der Tabelle
ZelleFormel
J3{=SUMME($B3:$I3*($B$2:$I$2=J$2)*ISTZAHL(VERGLEICH($B$1:$I$1;WENN((Matchtabelle!$B$2:$B$9=VERWEIS(9;1/($J$1:J$1<>""); $J$1:J$1)); Matchtabelle!$A$2:$A$9&""); )))}
B9=B1
E9=E1
I9=I1
B10=B1
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


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

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige