Excel-Problem

[James]

Habe da ein Problem mit Excel und denke, dass ich es am besten hierher packe.
Vorneweg: Mir ist bewusst, dass das wohl nicht alles in eine Formel zu packen ist. Googlen fiel quasi flach, weil das etwas komplizierter ist.

Jetzt das eigentliche Problem:

Ich habe eine Tabelle mit fast 6000 Zellen und über 20 Spalten.
Ich möchte alle Werte der Spalte M summieren, wenn von 2 oder meherern Zeilen die Spalten B und I identisch sind. (Nicht, wenn B = I ist, sondern wenn z.b B2 = B3 UND I2 = I3 ist)
Optimalerweise hätte ich es quasi so, dass aus mehreren Zellen, deren Spalten B und I identisch sind, eine Zelle wird, aber in der Spalte M soll die Summe aller vorherigen Werte von M stehen.

Ein Beispiel:

B .... I .... M

3 .... 2 .... 6
3 .... 1 .... 2
4 .... 3 .... 8
3 .... 1 .... 7
3 .... 2 .... 11
3 .... 2 .... 9

Am Ende sollten dann da stehen:

3 .... 2 ..... 26
3 .... 1 ..... 9
4 .... 3 ..... 8

(Alle anderen Spalten sind da irrelevant und sollten einfach übernommen werden)

Wäre toll, wenn mir da jemand weiterhelfen könnte. Bin da zur Zeit recht aufgeschmissen.

 

[ElDonoX]

welche Ansätze hast du denn schon versucht?

Ich glaube du musst hier mit einer WENN DANN funktion arbeiten, die aber länger ist als alles bekannte

 

[Anker]

Ich würde doch eher den Querverweis nehmen.

 

[James]

Versucht habe ich bisher eigentlich noch gar nichts, da ich mich mit Excel-Formeln überhaupt nicht auskenne.

Werde mir dann mal später morgen "WENN DANN" und "VERWEIS" ansehen. Danke schon einmal!

 

[Golan]

Jesass Junge, mach dich nicht unglücklich! Nimm ein ordentliches Framework für Datenverarbeitung! Und wenn's nur ist, dass du's als csv in Python einliest, als string parst und dann wieder als csv speicherst.

Was verstehst du eigentlich darunter, dass die anderen Spalten irrelevant sind? Wenn du die nicht verarbeitest wirst du deren Content wohl oder übel löschen, nicht übernehmen.

 

[Ares]

Also für dein verwendetes Beispiel hätte ich eine Lösung die allerdings gewisse Voraussetzungen hat:

- Die Zahlen in B und I sind jeweils einstellig
- Es ist manueller Aufwand erlaubt, d.h. bei Änderung der Daten müssen die Schritte wiederholt werden
- Die Struktur der Datei kann vorrübergehend verändert werden (Ergänzen von Spalten etc.)
- Die Auswertung ersetzt nicht automatisch die vorhandenen Daten sondern wird an anderer Stelle durchgeführt

Wenn das bei dir erfüllt ist, kann ich meinen Ansatz mal erläutern.

Edit: Mir ist noch eine andere Möglichkeit eingefallen bei der auch mehrstellige Zahlen erlaubt sind. Letztendlich hängt es davon ab wie flexibel du bei der Umsetzung bist.

 

[James]

Also nach deinem Edit sind wohl alle Voraussetzung erfüllt (ich habe mehrstellige Zahlen und Wörter in Spalte B), wenn der manuelle Aufwand nicht zu groß ist. Ich habe alleine in Spalte B schon 353 verschiedene Werte; in I habe ich noch nicht "gezählt", aber ich denke, dass ich im Endeffekt auf locker 1500 verschiedene Zeilen kommen würde. Einen Vorgang 1500 mal zu wiederholen war da eigentlich nicht mein Ziel.
Wäre dir dankbar, wenn du deinen Ansatz erläutern könntest.

Was verstehst du eigentlich darunter, dass die anderen Spalten irrelevant sind? Wenn du die nicht verarbeitest wirst du deren Content wohl oder übel löschen, nicht übernehmen.

War schlecht ausgedrückt. Natürlich sollen sie in meiner Endtabelle dabei sein, aber an diesen Spalten soll rein gar nichts geändert werden.

 

[Golan]

Ich meine, wie soll das gehen? Sagen wir du hast in Zeile D extra:
3 ..2.. 2 .... 11
3 ..6.. 2 .... 9
3 ..1.. 2 .... 9

Ist dann das Ergebnis
3 ..2.. 2 .... 20
oder
3 ..9.. 2 .... 20
oder
3 .."2,6,1".. 2 .... 20
?

Mit Python ist es übrigens in etwa 30 Zeilen Code machbar.

 

[Time]

Aha da haben wir es ja.
Der James wertet wohl nun woanders die Sport-Ergebnisse aus nur jetzt im grossen stil

 

[James]

Ich meine, wie soll das gehen? Sagen wir du hast in Zeile D extra:
3 ..2.. 2 .... 11
3 ..6.. 2 .... 9
3 ..1.. 2 .... 9

Ist dann das Ergebnis
3 ..2.. 2 .... 20
oder
3 ..9.. 2 .... 20
oder
3 .."2,6,1".. 2 .... 20
?

Mit Python ist es übrigens in etwa 30 Zeilen Code machbar.

Ich habe etwas überlegt und mich festgelegt, dass es wirklich am besten wäre, wenn in der Ergebnis-Tabelle wirklich nur die Spalten B, I und M wären und die anderen darin gar nicht mehr auftauchen.
(Das Problem war, dass fast alle Spaltenwerte dem Wert in B zugeordnet waren. Aber eben nur fast alle ... wären es alle gewesen, wäre das von dir zuerst genannte Ergebnis das "richtige" gewesen)

Mit Python kenne ich mich rein gar nicht aus, hoffe also mal, dass das nicht zu kompliziert ist.

 

[Ares]

Ich habe zwei Vorschläge zur Lösung. Beide finden sich in der angehängten Datei dargestellt.

Variante 1 (nur einstellige Zahlen erlaubt)

1) Verketten der beiden Spalten B und C in A
2) Kopieren der Zahlen aus A in einen anderen Bereich (Spalte F)
3) Aus diesem Bereich Duplikate entfernen (Daten/Duplikate entfernen)
4) Über summewenn() in G die entsprechenden Werte addieren lassen
5) In F stehen die Zahlen jetzt noch verkettet. Um die Ursprungsform zu bekommen kann man entweder die Option Text in Spalten verwenden oder sich über einen sverweis() die Zahlen aus B und C holen

Variante 2 (nur möglich mit Excel 2010)

1) Verwendung von summewenns() in Spalte E
2) Spalte E als Wertekopie einfügen
3) Markierung von B2:E7 ; Duplikate entfernen (Datei/Duplikate entfernen ; Spalten B und C in der Auswahl anklicken)

View attachment Beispiel.xlsx

 

[James]

Wunderbar! Das sollte jetzt eigentlich klappen.

 

[Golan]

Mit Python hast du leider erst mal einiges an Verwaltungsaufwand, die normale Shell von Windows ist leider nicht so der Bringer. Das ist jetzt für dich die Entscheidung, ob es dir der Aufwand wert ist - machst du sowas nur bei jedem dritten Vollmond wenn der große Wagen den Polarstern überfährt, lohnt es sich nicht; Python ist eine vollständige Scriptingsprache und entsprechend anspruchsvoll. Dafür ist Python schnell zu lernen und sowohl mächtig als auch schnell in der Verarbeitung von Textdateien (wie csv).
Du müsstest erst einmal Python installieren. Exportiere deine Excel-Datei als CSV (Trennzeichen Komma ","). Dann die Datei aus dem Zipanhang bzw. den Sourcecode im Verzeichnis, wohin du die CSV gelegt hast, speichern.

# -*- coding: utf-8 -*-
import sys

# Hilfsfunktion - entweder tatsächlichen Zahlenwert oder 0 zurückgeben
def tointeger(string):
	try:
		return float(string)
	except:
		return 0

try:
	# input zum lesen öffnen, output zum schreiben öffnen
	data = open(sys.argv[1],'r')
	outfile = open("out"+sys.argv[1],'w')
	# dictionary für zwischenspeichern von ergebnissen anlegen
	store={}
	# input zeilenweise durchgehen
	for line in data:
		# auskommentieren um ursprüngliche daten im output neu zu speichern
		#outfile.write(line)
		# zeile aufspalten an kommas (standard für CSV)
		linecontent = line.split(",")
		# input von zeilen mit gleichem B und I in der gleichen variable aufaddieren
		try:
			store[linecontent[1],linecontent[8]] += tointeger(linecontent[12])
		except KeyError:
			store[linecontent[1],linecontent[8]] = tointeger(linecontent[12])
	# dummy für zeilenlänge
	temp = ['']*13
	# kondensierte ergebnisliste durchgehen, jeweils doppelindex (B,I) und wert M auslesen
	for BIKey, MSum in store.items():
		# B, I und M in den dummy speichern
		temp[1],temp[8],temp[12] = str(BIKey[0]), str(BIKey[1]), str(MSum)+'\n'
		# die dummy-zeile in die output-datei speichern
		outfile.write(",".join([str(item) for item in temp ]))
except:
	# falls etwas schief läuft, fehler ausgeben und stoppen
	input("Fehler. Wahrscheinlich wurde eine falsche Datei angegeben. Enter drücken...")
	raise

Jetzt die Windows-Konsole öffnen mit Ausführen->'cmd' + [Enter]. Mit cd kannst du jetzt das Verzeichnis auswählen, in dem dein Script/CSV ist. Dort rufst du jetzt Python auf und übergibst den Namen deines Scripts sowie den Namen deiner CSV Datei. Bei mir sieht das z.B. so aus:

cd C:\Users\MaxFischer\Documents\Test\excelpy
C:\Python32\python.exe tablepy.py table1.csv

Mann, wie Hardcore so ein paar Zeilen Code und Kommandos wirken koennen. oO

 

[James]

Diese spezielle Aufgabe mache ich nur bei jedem dritten Vollmond wenn der große Wagen den Polarstern überfährt, aber dieses Programm scheint sehr interessant zu sein.
Von daher werde ich mich damit mal beschäftigen. Wer weiß, wann ich das nochmal brauche?
Daher nochmals ein dickes