Office: (Office 365) Werte finden und ausgesuchte Werte addieren

Liebe Excel-Experten,

ich bitte euch um Hilfe bei folgender Aufgabenstellung.

Ich bekomme aus einer externen Datenquelle Informationen in diesem Schema:

| | A | B | C | D | E | F | G | H |
| 1 | Rasse | Planet | userId | TNG_1 | TNG_2 | TOS_1 | VOY_1 | TNG_3 |
| 2 | Mensch | Erde | Picard | 11 | | 3 | 13 | |
| 3 | Klingone | Kronos | Worf | 13 | 17 | 5 | | 4 |
| 3 | Mensch | Erde | Riker | | 14 | 7 | 15 | 9 |
| 4 | Mensch | Erde | Picard | | | 9 | | 1 |
| 5 | Android | O.Theta | Data | 7 | 9 | 1 | 9 | 11 |
| 6 | Mensch | Erde | Riker | 2 | 12 | | 3 | |
| 7 | Reisender | Kosmos | Wesley | | | 2 | | |
| 8 | Mensch | Erde | Picard | 3 | 11 | | 7 | 24 |

Ich benötige nun in einem neuen Arbeitsblatt eine eindeutige Auflistung aller Personen (Spalte mit "userId" in Zeile 1) und der Summe aller Spalten die in Zeile 1 mit "TNG" beginnen in den Zeilen (mehrfaches Vorkommen möglich) in dem dieser Name überall vorkommt.

Die Spalte mit "userId" in Zeile 1 ist dynamisch, es kann also nicht immer von Spalte C ausgegangen werden.
Auch die Spalten mit "TNG" am Beginn sind dynamisch wechselnd und können eine beliebige Anzahl haben.
Die "Überschriften" in Zeile 1 sind natürlich immer in dieser Zeile zu finden.

Beispiel Ergebnis in Arbeitsblatt 2:

| | A | B |
| 1 | Data | 27 |
| 2 | Picard | 50 |
| 3 | Riker | 37 |
| 3 | Wesley | 0 |
| 4 | Worf | 34 |

Ich würde VBA gern vermeiden.
Könnt ihr mir helfen?

Vielen Dank und "Lebe lange und in Frieden" :)

 

Die Lösung findet sich hier:
AW: mit INDEX(), VERGLEICH() & SUMMENPRODUKT() ...

Danke.

 

Beachte
Regeln
Punkte 2.3 zu Crossposting.

 

Hallo,

hier Werte finden und ausgesuchte addieren ist mein Lösungsvorschlag zu finden.

@HL,
künftig unterlasse Doppelposting in verschiedenen Foren.

 

\\// warum magst Du kein VBA?

 

Hallo,

mein Formelvorschlag, der nur die Vorgabe des Datenbereichs voraussetzt, lautet:
=LET(m;A1:H9; ub; INDEX(m;1;0); md; WEGLASSEN(m;1);
spu;VERGLEICH("userId";ub;0); spud;INDEX(md;0;spu); u;SORTIEREN(EINDEUTIG(spud));
su; NACHZEILE(u; LAMBDA(uzl; SUMME( WENNFEHLER((spud=uzl)*(LINKS(ub;4)="TNG_")*md;0) )));
WAHL({1.2}; u;su))

mit dem Ergebnis:

Version: Excel365 Insider Beta

 

Danke dir Exl121150, dein Formelvorschlag funktioniert für das angegebene Beispiel hervorragend. Leider habe ich nicht bedacht, dass diese Art der Verarbeitung bei tausenden von Zeilen und Spalten an seine Grenzen stößt. Ich werde meine Input-Daten also von Hand vorbearbeiten und dann die Formel drüberlaufen lassen.

 

Moin HL,

Genau da hilft VBA ...

Grüße!

 

... das stoße ICH dann an meine Grenzen

 

Hallo,

wie bereits von mir geschrieben, dann eben PQ.

 

Hallo,

in der beiliegenden Datei habe ich dir meine 2 Varianten eingebaut:
1) in Zelle A13 mit Arbeitsblattfunktionen (Version Excel365 Insider Beta wird vorausgesetzt).
2) in Zelle D13 mit einer benutzerdefinierten VBA-Funktion: =GruppenSummieren(Bereich)

VBA Funktion:

Code:

Public Function GruppenSummieren(rngBereich As Range) As Variant
   Dim rngHeader As Range, rngDaten As Range
   Dim lngSpU As Long, rngSpU As Range
   Dim arrU As Variant, arr As Variant
   Dim rngUzl As Range, Sp As Long, SuUzl As Double
   Dim strU As String, lngUZl As Long, Wert As Double, varWert As Variant

   With rngBereich                                       'm
      Set rngHeader = .Rows(1)                           'ub
      Set rngDaten = .Resize(.Rows.Count - 1).Offset(1)  'md
   End With
   With WorksheetFunction
      lngSpU = .Match("userId", rngHeader, 0)            'spu
      Set rngSpU = rngDaten.Columns(lngSpU)              'spud
      arrU = .Sort(.Unique(rngSpU))                      'u
   End With
   
   arr = arrU
   ReDim Preserve arr(1 To UBound(arrU), 1 To 2) As Variant
   
   On Error GoTo Err_Zellwert
   For Each rngUzl In rngDaten.Rows
      With rngUzl
         SuUzl = 0#
         For Sp = 1 To rngDaten.Columns.Count
           If rngHeader.Cells(Sp).Value Like "TNG_*" Then 'LINKS(ub;4)="TNG_"
              varWert = .Cells(Sp).Value
              SuUzl = SuUzl + CDbl(varWert)
           End If
         Next Sp
         strU = .Cells(lngSpU).Value
         lngUZl = WorksheetFunction.Match(strU, arrU, 0) 'spud=uzl
         arr(lngUZl, 2) = arr(lngUZl, 2) + SuUzl         'su
      End With
   Next rngUzl
   
   GruppenSummieren = arr                                'WAHL({1.2}; u;su)
   
   Exit Function
   
Err_Zellwert:
   varWert = Trim(varWert)
   If varWert Like "'*" Then varWert = Mid$(varWert, 2)
   If Len(varWert) = 0 Then varWert = "0"
   Resume
End Function