Microsoft Excel

Herbers Excel/VBA-Archiv

Arrayfunktion gesucht

Betrifft: Arrayfunktion gesucht von: Marc
Geschrieben am: 06.08.2014 13:50:28

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

  

Betrifft: mehrfach VERGLEICH() en ... von: neopa C (paneo)
Geschrieben am: 06.08.2014 14:20:56

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
.. , - ...


  

Betrifft: AW: mehrfach VERGLEICH() en ... von: Marc
Geschrieben am: 06.08.2014 16:53:41

Hallo Werner,

vielen Dank für deine schnelle Hilfe. Die Formel funktioniert super.

Viele Grüße
Marc


  

Betrifft: AW: mehrfach VERGLEICH() en ... von: Marc
Geschrieben am: 11.08.2014 08:54:12

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


  

Betrifft: AW: mehrfach VERGLEICH() en ... von: Marc
Geschrieben am: 11.08.2014 09:20:51




  

Betrifft: wieder VERGLEICH()en, prüfen und summieren ... von: neopa C (paneo)
Geschrieben am: 11.08.2014 11:42:35

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
.. , - ...


  

Betrifft: AW: wieder VERGLEICH()en, prüfen und summieren ... von: Marc
Geschrieben am: 11.08.2014 12:46:43

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


  

Betrifft: Du VERGLEICH()st ungleiche Textwerte ... von: neopa C (paneo)
Geschrieben am: 11.08.2014 13:23:21

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
.. , - ...


  

Betrifft: AW: Du VERGLEICH()st ungleiche Textwerte ... von: Marc
Geschrieben am: 11.08.2014 14:16:54

Hallo Werner,

Vielen Dank für die Formel, welche meine Auswertung jetzt beschleunigt.
Problem somit gelöst.

Viele Grüße
Marc


  

Betrifft: AW: Du VERGLEICH()st ungleiche Textwerte ... von: Marc
Geschrieben am: 12.08.2014 09:36:56

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


  

Betrifft: für die Formel ist das keine kleine Änderung ... von: neopa C (paneo)
Geschrieben am: 12.08.2014 11:10:03

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
.. , - ...


  

Betrifft: AW: für die Formel ist das keine kleine Änderung ... von: Marc
Geschrieben am: 12.08.2014 11:17:53

Hallo Werner,

weitere Strukturänderungen sind nicht geplant.

Viele Grüße
Marc


  

Betrifft: richtige Berücksichtigung verbundener Zellen ... von: neopa C (paneo)
Geschrieben am: 12.08.2014 12:44:36

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
.. , - ...