Hallo Liebes Forum,
ich habe folgendes Problem.
Ich habe zwei Datensätze, zu je vier Spalten und einigen Zeilen, in etwa so:
Datensatz 1:
A B C D
5 1 1 1
5 2 1 1
5 3 1 1
5 4 1 1
Datensatz 2:
E F G H
5 -2 1 1
5 -1 1 1
5 0 1 1
5 1 1 1
Jetzt möchte ich, dass Excel mit daraus eine viespaltige Tabelle macht, in der alle Daten vorhanden sind. Sollte allerdings der Fall auftreten, dass die Werte der Spalten BCD und FGH gleich sind, sollen die Werte der zugehörigen Spalte A und E addiert werden. (siehe Fett markiert).
Als Ergebnis sollte in diesem einfachen Beispiel dann folgendes rauskommen:
I J K L
5 -2 1 1
5 -1 1 1
5 0 1 1
10 1 1 1
5 2 1 1
5 3 1 1
5 4 1 1
ich habe leider überhaupt keine Ahnung wie das geht, und hoffe mir kann jemand helfen.
Beste Grüße, Bob
Hi Bob,
hier ein Lösungsvorschlag mit Hilfsspalten, die du natürlich ausblenden kannst.
Tabelle1 | A | B | C | D | E | F | G | H | I | J |
| 1 | 5 | 1 | 1 | 1 | 5111 | 5 | - | 2 | 1 | 5-21 |
| 2 | 5 | 2 | 1 | 1 | 5211 | 5 | - | 1 | 1 | 5-11 |
| 3 | 5 | 3 | 1 | 1 | 5311 | 5 | 0 | 1 | 1 | 5011 |
| 4 | 5 | 4 | 1 | 1 | 5411 | 5 | 1 | 1 | 1 | 5111 |
| 5 | | | | | | 10 | 1 | 1 | 1 | |
| 6 | | | | | | 5 | 2 | 1 | 1 | |
| 7 | | | | | | 5 | 3 | 1 | 1 | |
| 8 | | | | | | 5 | 4 | 1 | 1 | |
| Formeln der Tabelle |
| Zelle | Formel | | E1 | =A1&B1&C1&D1 | | J1 | =F1&G1&H1&I1 | | E2 | =A2&B2&C2&D2 | | J2 | =F2&G2&H2&I2 | | E3 | =A3&B3&C3&D3 | | J3 | =F3&G3&H3&I3 | | E4 | =A4&B4&C4&D4 | | J4 | =F4&G4&H4&I4 | | F5 | =WENN(ZÄHLENWENN($J$1:$J$4;E1)=1;A1*2;A1) | | G5 | =WENN(F5<>"";$B1;"") | | H5 | =WENN(G5<>"";C1;"") | | I5 | =WENN(H5<>"";D1;"") | | F6 | =WENN(ZÄHLENWENN($J$1:$J$4;E2)=1;A2*2;A2) | | G6 | =WENN(F6<>"";$B2;"") | | H6 | =WENN(G6<>"";C2;"") | | I6 | =WENN(H6<>"";D2;"") | | F7 | =WENN(ZÄHLENWENN($J$1:$J$4;E3)=1;A3*2;A3) | | G7 | =WENN(F7<>"";$B3;"") | | H7 | =WENN(G7<>"";C3;"") | | I7 | =WENN(H7<>"";D3;"") | | F8 | =WENN(ZÄHLENWENN($J$1:$J$4;E4)=1;A4*2;A4) | | G8 | =WENN(F8<>"";$B4;"") | | H8 | =WENN(G8<>"";C4;"") | | I8 | =WENN(H8<>"";D4;"") |
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
In den Hilfsspalten erzeugst du einen zusammenhängenden und dadurch vergleichbaren Ausdruck. Unterhalb deines zweiten Blocks fügst du dann die Formeln ein und ziehst sie runter.
Hallo Günter
Ich glaube die Minuszeichen sind Vorzeichen und keine Platzhalter.
(18.02.2015, 06:31)shift-del schrieb: [ -> ]Ich glaube die Minuszeichen sind Vorzeichen und keine Platzhalter.
Hallo Detlef,
das habe ich tatsächlich übersehen - danke für den Hinweis. Mein Vorschlag funktioniert aber auch mit den Minuszahlen.
Hallo,
hier eine VBA Lösung:
Code:
Option Explicit
Sub mach()
Dim i As Long, j As Long, k As Long
Dim lngZ_A As Long, lngZ_E As Long
Dim feld
Dim arr()
Dim varKey
Dim objDic As Object
With Tabelle1
lngZ_A = .Cells(.Rows.Count, 1).End(xlUp).Row
lngZ_E = .Cells(.Rows.Count, 5).End(xlUp).Row
.Range("A2:D" & lngZ_A).Copy .Range("I2")
.Range("E2:H" & lngZ_E).Copy .Range("I" & lngZ_A + 1)
.Range("I1:L" & lngZ_A + lngZ_E - 1).Select
.Range("I2:L" & lngZ_A + lngZ_E - 1).Sort Key1:=.Range("J1"), Order1:=xlAscending, Key2:=.Range("I1") _
, Order2:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
feld = .Range("I2:L" & lngZ_A + lngZ_E - 1)
Set objDic = CreateObject("Scripting.Dictionary")
For i = 1 To lngZ_A + lngZ_E - 2
varKey = feld(i, 2) & "#" & feld(i, 3) & "#" & feld(i, 4)
If InStr(objDic(varKey), feld(i, 2) & "#" & feld(i, 3) & "#" & feld(i, 4)) Then
objDic(varKey) = Split(objDic(varKey), "#")(0) + feld(i, 1) & "#" & feld(i, 2) & "#" & feld(i, 3) & "#" & feld(i, 4)
Else
objDic(varKey) = objDic(varKey) & feld(i, 1) & "#" & feld(i, 2) & "#" & feld(i, 3) & "#" & feld(i, 4)
End If
Next i
j = 0
ReDim arr(objDic.Count, 3)
For Each varKey In objDic
For i = 0 To UBound(Split(objDic(varKey), "#"))
arr(j, k) = Split(objDic(varKey), "#")(i)
k = k + 1
Next i
k = 0
j = j + 1
Next varKey
.Range("I2:L" & lngZ_A + lngZ_E - 1).ClearContents
.Range("I2:L" & j + 1) = arr
End With
End Sub
@Günter
Ich denke, bei Deiner Lösung müssen noch die doppelten raus gelöscht werden.
Der TE hat als Ergebnis für das Beispiel 7 Datensätze Du hast 8
Hi Atilla,
Zitat:@Günter
Ich denke, bei Deiner Lösung müssen noch die doppelten raus gelöscht werden.
Der TE hat als Ergebnis für das Beispiel 7 Datensätze Du hast 8
da könntest du Recht haben. Ich habe die Datensätze nicht gezählt und da in der Problembeschreibung nichts von löschen stand, bin ich auf die Idee gar nicht gekommen.
Dann ist es mM nach eh nicht mit Formeln lösbar. Und auch nicht mit dem Autofilter oder der Bordfunktion "Duplikate entfernen". Es sind nach dem Addieren ja keine doppelten Sätze mehr vorhanden.
Hallo,
1. Die beiden Ausgangs-Blöcke untereinander anordnen, da reicht die Formel, die in meinem Beispiel in A5 steht (nach rechts und unten ziehen).
2. Eine Hilfsspalte, in meinem Beispiel M
3. Die Formel in I1 nach rechts und unten ziehen.
| Arbeitsblatt mit dem Namen 'Tabelle2' |
| | A | B | C | D | E | F | G | H | I | J | K | L | M |
| 1 | 5 | 1 | 1 | 1 | 5 | -2 | 1 | 1 | 5 | -2 | 1 | 1 | -2 |
| 2 | 5 | 2 | 1 | 1 | 5 | -1 | 1 | 1 | 5 | -1 | 1 | 1 | -1 |
| 3 | 5 | 3 | 1 | 1 | 5 | 0 | 1 | 1 | 5 | 0 | 1 | 1 | 0 |
| 4 | 5 | 4 | 1 | 1 | 5 | 1 | 1 | 1 | 10 | 2 | 2 | 2 | 1 |
| 5 | 5 | -2 | 1 | 1 | | | | | 5 | 2 | 1 | 1 | 2 |
| 6 | 5 | -1 | 1 | 1 | | | | | 5 | 3 | 1 | 1 | 3 |
| 7 | 5 | 0 | 1 | 1 | | | | | 5 | 4 | 1 | 1 | 4 |
| 8 | 5 | 1 | 1 | 1 | | | | | | | | | |
| 9 | | | | | | | | | | | | | |
| Zelle | Formel |
| I1 | =WENN($M1="";"";SUMMEWENN($B$1:$B$8;$M1;A$1:A$8)) |
| J1 | =WENN($M1="";"";SUMMEWENN($B$1:$B$8;$M1;B$1:B$8)) |
| K1 | =WENN($M1="";"";SUMMEWENN($B$1:$B$8;$M1;C$1:C$8)) |
| L1 | =WENN($M1="";"";SUMMEWENN($B$1:$B$8;$M1;D$1:D$8)) |
| M1 | =MIN($B$1:$B$8) |
| M2 | {=WENN(M1=MAX($B$1:$B$8);"";MIN(WENN($B$1:$B$8>M1;$B$1:$B$8)))} |
| A5 | =E1 |
| 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.4.1) erstellt. ©Gerd alias Bamberg |
Hallo Edgar,
ein Datensatz ist dann doppelt, wenn drei Spalten gleiche Werte haben.
Im Beispiel vom TE sind die Spalten B:D und J:L zu betrachten.
Wenn diese Spalten gleiche Werte haben, dann wird nur der Wert aus der ersten Spalte addiert die anderen bleiben gleich.
Änder mal in C1 die 1 in 2 um.
Dann müssen acht Datensätze ohne Addition auftauchen.
Ich erhalte dieses Ergebnis:
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| | A | B | C | D | E | F | G | H | I | J | K | L |
| 2 | 5 | 1 | 2 | 1 | 5 | -2 | 1 | 1 | 5 | -2 | 1 | 1 |
| 3 | 5 | 2 | 1 | 1 | 5 | -1 | 1 | 1 | 5 | -1 | 1 | 1 |
| 4 | 5 | 3 | 1 | 1 | 5 | 0 | 1 | 1 | 5 | 0 | 1 | 1 |
| 5 | 5 | 4 | 1 | 1 | 5 | 1 | 1 | 1 | 5 | 1 | 2 | 1 |
| 6 | | | | | | | | | 5 | 1 | 1 | 1 |
| 7 | | | | | | | | | 5 | 2 | 1 | 1 |
| 8 | | | | | | | | | 5 | 3 | 1 | 1 |
| 9 | | | | | | | | | 5 | 4 | 1 | 1 |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
Bei Dir kommt das raus:
| Arbeitsblatt mit dem Namen 'Tabelle3' |
| | A | B | C | D | E | F | G | H | I | J | K | L | M |
| 1 | 5 | 1 | 2 | 1 | 5 | -2 | 1 | 1 | 5 | -2 | 1 | 1 | -2 |
| 2 | 5 | 2 | 1 | 1 | 5 | -1 | 1 | 1 | 5 | -1 | 1 | 1 | -1 |
| 3 | 5 | 3 | 1 | 1 | 5 | 0 | 1 | 1 | 5 | 0 | 1 | 1 | 0 |
| 4 | 5 | 4 | 1 | 1 | 5 | 1 | 1 | 1 | 10 | 2 | 4 | 2 | 1 |
| 5 | 5 | -2 | 1 | 1 | | | | | 5 | 2 | 1 | 1 | 2 |
| 6 | 5 | -1 | 1 | 1 | | | | | 5 | 3 | 1 | 1 | 3 |
| 7 | 5 | 0 | 1 | 1 | | | | | 5 | 4 | 1 | 1 | 4 |
| 8 | 5 | 1 | 2 | 1 | | | | | | | | | |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
Hallo Atilla,
das läßt sich auch lösen:
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| | A | B | C | D | E | F | G | H | I | J | K | L | M |
| 1 | 5 | 1 | 1 | 1 | 5 | -2 | 1 | 1 | 5 | -2 | 1 | 1 | -21 |
| 2 | 5 | 2 | 1 | 1 | 5 | -1 | 1 | 1 | 5 | -1 | 1 | 1 | -11 |
| 3 | 5 | 3 | 1 | 1 | 5 | 0 | 1 | 1 | 5 | 0 | 1 | 1 | 1 |
| 4 | 5 | 4 | 1 | 1 | 5 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 11 |
| 5 | 5 | -2 | 1 | 1 | | | | | 5 | 2 | 1 | 1 | 21 |
| 6 | 5 | -1 | 1 | 1 | | | | | 5 | 3 | 1 | 1 | 31 |
| 7 | 5 | 0 | 1 | 1 | | | | | 5 | 4 | 1 | 1 | 41 |
| 8 | 5 | 1 | 1 | 1 | | | | | | | | | |
| Zelle | Formel |
| I1 | =WENN($M1="";"";SUMMENPRODUKT((($B$1:$B$8&$C$1:$C$8)*1=$M1)*1;A$1:A$8)) |
| J1 | {=WENN($M1="";"";INDEX(B:B;VERGLEICH($M1;($B:$B&$C:$C)*1;0)))} |
| K1 | {=WENN($M1="";"";INDEX(C:C;VERGLEICH($M1;($B:$B&$C:$C)*1;0)))} |
| L1 | {=WENN($M1="";"";INDEX(D:D;VERGLEICH($M1;($B:$B&$C:$C)*1;0)))} |
| M1 | {=MIN(($B$1:$B$8&$C$1:$C$8)*1)} |
| M2 | {=WENN(M1=MAX(($B$1:$B$8&$C$1:$C$8)*1);"";MIN(WENN(($B$1:$B$8&$C$1:$C$8)*1>M1;($B$1:$B$8&$C$1:$C$8)*1)))} |
| 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.4.1) erstellt. ©Gerd alias Bamberg |