Kennt sich jemand mit Excel aus?

[Bif-Bof]

Ich habe folgendes Problem

Kat.__Wert A______Wert B

1_____Wert 1______Wert 2
1_____Wert 3______Wert 4
1_____Wert 5______Wert 6
2_____Wert 7______Wert 8
2_____Wert 9______Wert 10
2_____Wert 11_____Wert 12
3_____Wert 13_____Wert 14
3_____Wert 15_____Wert 16
3_____Wert 17_____Wert 18


Ich würde jetzt gerne folgendes tun (vielleicht mit Summewenn, aber ich weiß nicht genau wie)

Wenn die Kategorie 1 ist, dann möchte ich die Summe von Wert 1-6 haben
Wenn die Kategorie 2 ist, dann möchte ich die Summe von Wert 7-12 haben usw....

 

[h0hiro]

Du brauchst pro Kat. eine neue Spalte in dem du die Summen zusammenrechnest.
Das machst du mit der Formel:
=WENN(A1=KAT1;SUMME(B1:C1);0)
Dann hast du für jede Kategorie die Summe pro Zeile. (Wenn die Kat=2 ist, dann ist die Summe halt 0.
Davon ziehst du am Ende für diese Hilfsspalte die Summe und du hast die Summe für alle Werte aus Kategorie 1.
Das musst du halt für jede mögliche Kategorie machen.
Eine weniger umständliche Lösung fällt mir ohne Matrix oder VBSkript nicht ein.

Ich hoffe ich konnte helfen.

 

[Bif-Bof]

Hi

Danke, aber ich wollte die Hilfsspalten vermeiden, weil ich 31 Kategorien habe (sprich die Tage im Monat)

Ich mein ich habe mir jetzt eine Pivot gebaut in der Zwischenzeit, aber da bin ich mit dem Ergebnis auch nicht so zufrieden, weil

1. Muss ich die Pivot ständig manuell updaten (ich weiß, dass ich beim Dokument öffnen automatisch aktualisieren kann, aber ich hätte es gerne in Echtzeit)

2. Jedes Mal nach dem aktualisieren stellt er die Formatierung auf die Formatvorlage zurück und ich weiß nicht wie ich eine Formatvorlage nach meinem Ermessen verändern kann.

Hat noch jemand Ideen?

 

[sucinum]

Kannst doch beliebig verschachteln:
WENN(A2=KAT1;SUMME(B2:C4);WENN(A1=KAT2;SUMME(B5:C7);etc)

 

[Bif-Bof]

Kannst doch beliebig verschachteln:
WENN(A2=KAT1;SUMME(B2:C4);WENN(A1=KAT2;SUMME(B5:C7);etc)

31 Verschachtelungen funktionieren? Ich dachte das Limit wäre bei 7, zumindest habe ich das vor 100 Jahren mal so gelernt

 

[sucinum]

Anders kommst du um die Hilfsspalte nicht drumrum. Mit einer Hilfsspalte (Summe aus Spalte B und C) wäre es recht einfach:
SUMMEWENN(A1-AZ99;"=1";D1-DZ99)
Wenn dich das optisch stört, kannst die Hilfsspalte ausblenden oder auf ein anderes Tabellenblatt packen.

 

[Bif-Bof]

Ja da hast du natürlich recht. Ich dachte halt man kann es "elegant" auch lösen.
Vielleicht schreibe ich auch ein Makro, aber das wollte ich eigentlich vermeiden, weil da sitze ich einen ganzen Tag für und das ist es mir glaub ich auch nicht Wert. Oder ich lebe mit der nicht schön formatierten Pivot Tabelle

Danke auf jeden Fall

 

[TomGrenn]

SVERWEIS

Wenn Du 31 Tage hast (und 31 WENN eben nicht gehen), dann kannst Du die Werte aus einer jeweiligen Summenhilfszeile mit SVERWEIS abfragen. Also für jeden Tag eine Summenzeile einfügen, diese z.B. mit 1a, 2a usw. kategorisieren (soll unterschiedlich zu 1, 2, 3,... sein sonst werden diese abgefragt) und per SVERWEIS das dortige Ergebnis relativ bequem abholen.
Falls etwas - nachdem Du Dich in SVERWEIS eingearbeitet hast - immer noch unklar sein sollte, dann

brauchen wir mehr Infos: ist die Anzahl der Werte-Spalten varialbel, ist die Anzahl der Werte-Zeile variabel,... ?

 

[Bif-Bof]

Habe diese Woche keine Zeit mehr aber ich lese mich dann nächste Woche in SVERWEIS ein und lasse euch meinen Fortschritt wissen

Danke

 

[mfb]

=SUMMEWENN(A1:A8;1;B1:B8)+SUMMEWENN(A1:A8;1;C1:C8)
Wobei der mittlere Parameter jeweils die Kategorie ist, die jeweils summiert werden soll. B1:B8 und C1:C8 enthalten die Daten, über die summiert wird, und A1:A8 sind die Kategorieeinordnung.

Keine Hilfsspalte nötig.

 

[Bif-Bof]

@mfb: Danke - genau so wollte ich das ursprünglich haben, als ich gemeint habe "vielleicht mit SummeWenn, aber ich weiß nicht wie genau"

Mein Fehler war, dass ich beide Wertespalten in einer SummeWenn drinnen haben wollte, aber auf die Idee dass ich 2 SummeWenn addiere bin ich leider nicht gekommen

 

[Bif-Bof]

Sorry aber da habe ich gleich die nächste und übernächste Frage:

Ich habe in "A1" ein Datum like: Mi, 11.07.2012

Jetzt möchte ich in
A2 = Mittwoch
A3 = 11
A4 = Juli
A5 = 2012

stehen haben. Den 11er habe ich schon =TAG(A1) und 2012 habe ich auch schon mit =JAHR(A1). Aber wie bekomme ich den Mittwoch in A2? Mit Wochentag() funktioniert es nicht, weil da bekomme ich nur eine Zahl als Ergebis. Wenn ich das ganze formatiere zB: [$-C07]TTTT dann steht da zwar Mittwoch, aber der Wert der Zelle ist immer noch eine Zahl. Das selbe Problem habe ich beim Monat.

Könnt ihr mir hier auch helfen?

PS: Ich habe gerade überlegt es mit einer Wenn zu versuchen. Wenn Monat von A1 = 1, dann Januar, sonst wenn monat von A1 = 2, dann Februar usw. aber 12 Verschachtelungen funktionieren bei Wenn ja auch nicht, soviel ich weiß

 

[mfb]

Wenn-Funktionen oder "Mittwoch" nur als Anzeige zu verwenden (mit internem Zahlenformat) wären auch meine Ansätze. Wo liegt das Problem bei letzterem?

Verschachtelungen sind nicht nötig, du kannst es auch als Verkettung machen:
=WENN(A1=1;"Januar";"")&WENN(A1=2;"Februar";"")&...

Ansonsten können zumindest neuere Excelversionen problemlos 12 Ebenen Schachtelung.

 

[Bif-Bof]

@mfb: Das mit den Verkettungen hat funktiniert => Das kannte ich gar nicht, ich kannte nur verschatelungen bis Ebene 7, aber für Verkettungen scheint es kein Limit zu geben - Danke

PS: Bist du der Excel Gott?

 

[Shihatsu]

Für die Zukunft: SVERWEIS, VERWEIS (der grosse bruder vom sverweis) und INDEX helfen hier sehr wenns mal wirklich dreckig von den werten her wird - was die drei zusammen icht können brauch eh nen makro. und ja, mfb ist der excel-gott

 

[Bif-Bof]

Ich habe wieder mal eine ähnliche Frage:

Wie ihr sehen könnt, habe ich eine Tabelle mit Datum und einen Wert dabei.
Jetzt möchte ich haben, dass alle Werte wo zB der Montag der 1. ist zusammengerechnet werden.

Im Livebetrieb hätte ich dann eine Tabelle mit 365 Zeilen (für jeden Tag eine) und (31*7) Summen (MO 1, MO 2..., DI 1, DI 2,...usw)

Gibt es dafür eine Lösung?

 

[mfb]

Falls Wochentag und Datum in getrennte Spalten kommen können:

=SUMMEWENNS(G6:G9;E6:E9;"Montag";F6:F9;1)
Wobei in Spalte E der Wochentag und in Spalte F der Tag des Monats ist, und Spalte G die Stunden hat.

Falls dein Datum als Text vorliegt:
=SUMMEWENNS(G6:G9;E6:E9;"=Montag*";E6:E9;"=*1.*")
Wobei Spalte E das Datum hat und Spalte G die Stunden.

=SUMMEWENNS(G6:G9;E6:E9;"="&E12&"*";E6:E9;"=*"&F12&".*")
So wird nach dem Wochentag der in E12 und dem Datum das in F12 steht gesucht.

Wozu brauchst du so eine seltsame Summe?

 

[Bif-Bof]

Naja für zB eine Überstundenstatistik

Danke für die Hilfe, aber SUMMEWENNS (mit S hinten) habe ich noch nie gehört und das kennt mein Excel offensichtlich auch nicht

Ich habe aber jetzt mal das Datum zerlegt:

und ich schaffe auch einige Summen, aber ich denke, ich muss jetzt beide einzelnen Summewenn zu einer verschachteln,...

aber ich möchte auch nicht 31x7 Hilfsspalten haben:

 

[mfb]

Also Excel 2007 kann SUMMEWENNS, bei älteren Versionen weiß ich es nicht.
Ein Makro könnte das auch gut machen, sofern das vorstellbar ist.

Makrofreie Lösung mit einer Hilfsspalte: Kombiniere Wochentag+Datum darin, als =B2&C2, und lass SUMMEWENN nach "Mi01" und so weiter suchen. "Mi01" in der Bedingung für SUMMEWENN kann ggf. auch wieder aus Zellen "Mi" und "01" mit & zusammengefügt werden.
=SUMMEWENN(D4:D7;"Mi01";F4:F7)
D ist hier die Hilfsspalte.

 

[Bif-Bof]

Danke, dass muss ich morgen in der Firma durchdenken, jetzt ist mein Hirn schon runtergefahren