automatisch erweiternde Tabelle ohne Dublikat auf neuem Blatt

[wernqq]

Hallo Forum,
Ich bin Excel-Beginner und habe heute meinen halben Tag mit folgendem, wahrscheinlich trivialen, Problem verschwendet :
Kurze Vorgeschichte:
-Ich habe auf den Excelblättern 1-3 eine Tabelle mit Spalten für Namen und dazugehörigen Werten.
 in diese Tabellen werden im Laufe der Zeit vorhandene und neue Namen inkl Daten eingefügt.
 (das ist soweit kein Problem, da ich die neue Datenreihe ja nur drunter schreiben muss, bzw manuell verlängern kann)
Bsp.:
Name Wert
aaa      2
aaa      3
abb      1
abb      3
ccc      0

aaa      1

bbb     -4
....      ...

-Ich möchte aber die Namen und ihre Werte auf ein 4tes Excelblatt zur Auswertung zusammentragen, um für die jeweiligen Namen (ohne Dublikat) seinen Durchschnittswert zu errechnen.


Ich habe es mir wie folgt vorgestellt:
Name  Durchschnitt
aaa        6
abb        2
ccc         0
bbb       -4
....       ....

-Meine Vorgehensweise:
Meine Namensspalte hab ich mit =Tabelle1[Name] übertragen,
daneben nocheinmal, aber ohne Dublikate (manuell entfernt mit Datentools-button)  :s
Mittelwert hab ich dann mit =MITTELWERTWENN errechnet.
Das klappt im kleinen Maßstab zwar, aber ich möchte es so, das es selbstständig weiterläuft wenn immerwieder neue Daten dazukommen.

Problem:
Mein momentanes Problem ist, das sich bei Eingabe von Daten in den Datenblättern die Auswertungstabelle nicht automatisch erweitern.
ziehe ich die Tabellen länger, sind die Namen zwar dank auto-fill vorhanden, allerdings ist die Tabelle dann stets endlich und ich will sie nicht manuell ständig erneuern.

Kurz gefasst:
Ich suche also eine Funktion, damit die Namen aus den Datenblättern automatisch ins Auswertungsblatt übertragen werden, Dublikate entfernt werden, und den Mittelwert der absolut vorkommenden Namen errechnet wird.
Das alles soll automatisch und ohne ständiges manuelles aktualisieren funktionien.
(wahrscheinlich ist mein Weg umständlich, darum ist ein besserer Weg auch gerne Willkommen)

Tut mir Leid für diese ausführliche Beschreibung,
aber ich wollte Eindeutig sein, denn im Internet hab ich nichts hilfreiches gefunden :22:

Also falls sich jemand die Mühe macht, Bedanke ich mich schonmal im Vorraus 1000fach :15:

[atilla]

Hallo,

schau Dir mal diesen Workshop an:

[Excel] Pivottabelle aus mehreren Tabellenblättern erstellen


Wenn Du damit nicht klar kommst, wäre der Einsatz von VBA (Makros) eine Möglichkeit.

[wernqq]

Danke. :)
Mit Pivot funktioniert es und andere sachen schnell und einfach, danke. :)
Aber gibt es keine Formel zum automatischen zusammentragen?
Mit Makros kenne ich mich leider überhaupt nicht aus :s 

mfg

[Rabe]

Hi,

Aber gibt es keine Formel zum automatischen zusammentragen?
Mit Makros kenne ich mich leider überhaupt nicht aus :s 

kein Problem:

1. Makro-Aufzeichnung starten
   
2. Daten zusammentragen
   
3. Makro-Aufzeichnung beenden
   

schon hast Du ein Makro,
das kannst Du dann hier vorstellen und wir verkürzen es sinnvoll und verallgemeinern es gemeinsam.

[atilla]

Hallo,

ich habe Dir mal etwas zusammengebastelt.

Folgendes wird vorausgesetzt:
-Es existiert eine Tabelle mit Namen "Übersicht", wenn sie bei Dir anders heißt dann im Code ändern
-Alle Tabellen, die zusammengeführt werden sollen haben die Überschrift in Zeile1 und es werden die Spalten A+B kopiert und zusammengeführt

Wenn das bei Dir so aufgebaut ist wie oben, dann kopier folgenden Code in das Codemodul der Tabelle "Übersicht"
Rechtsklick auf den Tabellenreiter -> Code anzeigen wählen und in das sich öffnende Fenter

Code:

Option Explicit

Private Sub Worksheet_Activate()
   Call Mittelwert_aus_allen
End Sub

Sub Mittelwert_aus_allen()
   Dim lngZ As Long, i As Long
   Dim lngErste As Long
   Dim arrTab
   
   arrTab = Array("Tabelle1", "Tabelle2", "Tabelle3")
   lngErste = 2
   Application.ScreenUpdating = False
   With Worksheets("Übersicht")
       lngZ = Application.Max(2, .Cells(.Rows.Count, 1).End(xlUp).Row)
       .Range("A2:B" & lngZ).ClearContents
       For i = 0 To UBound(arrTab)
           lngZ = Sheets(arrTab(i)).Cells(Sheets(arrTab(i)).Rows.Count, 1).End(xlUp).Row
           Sheets(arrTab(i)).Range("A2.B" & lngZ).Copy
           .Range(.Cells(lngErste, 1), .Cells(lngErste - 2 + lngZ, 2)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
           lngErste = lngErste + lngZ - 1
       Next i
       lngZ = lngErste - 1
       .Range("C2:C" & lngZ).FormulaLocal = "=MITTELWERTWENN($A$2:$A$" & lngZ & ";A2;$B$2:$B$" & lngZ & ")"
       .Range("C2:C" & lngZ).Value = .Range("C2:C" & lngZ).Value
       .Range("C1") = "Mittelwert"
       Application.CutCopyMode = False
       .Range("$A$1:$C$" & lngZ).RemoveDuplicates Columns:=1, Header:=xlYes
       .Range("A1").Select
   End With
   Application.ScreenUpdating = True
End Sub

In Spalte C der Tabelle "Übersicht" wird der Mittelwert rein geschrieben.
Das zusammenführen und errechnen des Mittelwerts passiert automatisch, sobald Du die Tabelle aktivierst.
Nach dem Du den Code eingefügt hast, zu Excel wechseln und die Mappe mit Makros speichern (.xlsm Format)
Jetzt eine andere Tabelle als die Tabelle "Übersicht" aktivieren. Wenn Du nunn wieder die Tabelle "Übersicht aktivierst, sollten alle Tabellen eingelesen (ohne Duplikate) und der Mittelwert eingetragen sein. Das hin und her wechseln zwischen den Tabellen ist nur beim ersten Mal nötig

[schauan]

Hallöchen,

zum Zusammenfassen von Daten ohne Duplikate gibt es eine Formellösung, siehe www.herber.de/excelformeln und bitte suchen .... Hier mal ein Beispiel:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	aaa	1	aaa	3
2	aaa	2	bbb	4
3	bbb	3	ccc	4
4	ccc	2	ddd	3
5	bbb	1		0
6	ccc	2		0
7	ddd	3		0

Zelle	Formel
C1	=A1
D1	=SUMMENPRODUKT(($A$1:$A$7=C1)*$B$1:$B$7)
C2	{=WENNFEHLER(INDEX(A:A;VERGLEICH(1;(ZÄHLENWENN(C$1:C1;A$1:A$99)=0)*(A$1:A$99<>"");0));"")}
D2	=SUMMENPRODUKT(($A$1:$A$7=C2)*$B$1:$B$7)
C3	{=WENNFEHLER(INDEX(A:A;VERGLEICH(1;(ZÄHLENWENN(C$1:C2;A$1:A$99)=0)*(A$1:A$99<>"");0));"")}
D3	=SUMMENPRODUKT(($A$1:$A$7=C3)*$B$1:$B$7)
C4	{=WENNFEHLER(INDEX(A:A;VERGLEICH(1;(ZÄHLENWENN(C$1:C3;A$1:A$99)=0)*(A$1:A$99<>"");0));"")}
D4	=SUMMENPRODUKT(($A$1:$A$7=C4)*$B$1:$B$7)
C5	{=WENNFEHLER(INDEX(A:A;VERGLEICH(1;(ZÄHLENWENN(C$1:C4;A$1:A$99)=0)*(A$1:A$99<>"");0));"")}
D5	=SUMMENPRODUKT(($A$1:$A$7=C5)*$B$1:$B$7)
C6	{=WENNFEHLER(INDEX(A:A;VERGLEICH(1;(ZÄHLENWENN(C$1:C5;A$1:A$99)=0)*(A$1:A$99<>"");0));"")}
D6	=SUMMENPRODUKT(($A$1:$A$7=C6)*$B$1:$B$7)
C7	{=WENNFEHLER(INDEX(A:A;VERGLEICH(1;(ZÄHLENWENN(C$1:C6;A$1:A$99)=0)*(A$1:A$99<>"");0));"")}
D7	=SUMMENPRODUKT(($A$1:$A$7=C7)*$B$1:$B$7)

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg