Thema: Die Werte zwischen zwei Suchwerten zusammenrechnen

Hallo,

ich versuche Werte die zwischen einem Suchwert "w1" und "w2" liegen zusammen zu rechnen.

"w1" befindet sich z.B. in B10 und "w2" in E25.
In den Zellen B11, C15, C25 und E20 befinden sich die Werte 5, 10, 2, 18
= Summe( B11+ C15+C25+E20) -> Ergebnis: 35

Wie schaffe ich es, diese Werte zu addieren, damit sich das Ergebnis automatisch angleicht,
ohne dass ich eine Summe manuell eingeben muss:

- Wert-Angaben werden nachträglich eingegeben, z.B. Wert 20 in B15
-> 'manuelle' Lösung = Summe( B11+ C15+C25+E20) + B15 -> Ergebnis 55

- der Suchwert "w2" wird von E25 nach F5 verschoben, 
in F2 befindet sich der Wert 5, der nun nach der Verschiebung auch berücksichtigt werden muss.
-> 'manuelle' Lösung = Summe( B11+ C15+C25+E20) + F2 -> Ergebnis 40

Ich vermute, dass ich SVerweise und Wenn-Funktionen verschachteln muss... ?

LG
Anja

2

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Bitte versuch mal, die Tabelle hier darzustellen - kann mir gerade nicht wirklich verstellen, wie das aussehen soll.
Aber ein Kleinigkeit: in der Summe-Funktion brauchst Du das + nicht mehr (es heißt ja schon SUMME), also Summe(B11;C15;C25;E20).

Eine tolle Funktion in Verbindung mit Kriterien und Summen ist die Funktion =SUMMEWENN(). Schau mal, ob die Dir weiterhilft (läßt sich nicht durch WENN und SUMME ersetzen sondern hat eine ganz eigene Aufgabe!). Und wenn sie Dir hier nicht hift, schadet's nix sie zu kennen...

LG,
Oliver

[color=#555555]...und hier noch Olivers Kochtipp: wenn Du die Zahnstocher mitessen kannst, hast Du die Rouladen zu lange gekocht!!! :-)[/color]

3

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Ok, Danke.
Werde mal mit Summewenn weiterknobeln.
Kann ich die Tabelle zur Darstellung hochladen?

4

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Nein, Hochladen geht hier aus Sicherheitsgründen nicht. Du musst die Tabelle irgendwie mit Text hier darstellen. Wenn's zu viel ist, schick die Tabelle an meine Email-Adresse - wenn Du eingeloggt bist, ist der Email-Link links neben meinen Beiträgen sichtbar. Ich mach dann einen Screenshot und stells hinein.
LG, Oliver

[color=#555555]...und hier noch Olivers Kochtipp: wenn Du die Zahnstocher mitessen kannst, hast Du die Rouladen zu lange gekocht!!! :-)[/color]

5

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Hallo,
mein Ansatz ist: Summe wenn der SVerweis "w1" und "w2" findet.
(Alle Zahlen, die sich in einem Matrix-Bereich zwischen den beiden Werten "w1" und "w2" befinden sollen dann addiert werden).

=SUMMEWENN(SVERWEIS("w1";$A$8:$V$30;0);UND;SVERWEIS("w2";$A$8:$V$30;0))

???

Tabelle ist email-technisch unterwegs. wink

Wegen Screenshot: Die Tabelle ist recht speziell (weil für Cricket entworfen), vielleicht müssen wir hier also erst mal noch so weiterknobeln, bevor die anderen Forumsmitglieder die Ergebnisse für deren individuellen Gebrauch nützlich finden können ... ?
Schau mal was du davon hälst.

LG
anja

6

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

___A   B   C   D   E   F   G   H   I   J   K   L   M   N   O   P   Q   R   S   T   U   V
01
02
03
04
05
06
07
08  1    6   4
09  2              4   6
10                          w1  6    4   
11
12  2    4        1   2   6   w2             
13

Meine Aufgabe ist:
Suche genau den Wert "w1", rechne alle Zahlenwerte nach der Zelle in der sich w1 befindet zusammen, bis du auf den Wert "w2" triffst!


Alle Werte, die nach (F10)=w1 und vor (G12)=w2 liegen sollen also zusammengerechnet werden:

(G10)=6 + (H10)=4 + (A12)=2 + (D12)=1 + (E12)=2 + (F12)=6
Ergebnis: 25


Folgende Werte sollen aber NICHT zusammengerechnet werden:
(A8)=1, (B8)=6, (C8)=4, (A9)=2 weil sie vor (F10)=w1 liegen



Ansatz 1:
Suche genau den Wert "w1" -> SVERWEIS("w1";$A$8:$V$30;0)
Suche genau den Wert "w2" -> SVERWEIS("w2";$A$8:$V$30;0)

rechne alle Zahlenwerte zusammen (aber nur wenn du auf die obigen Parameter triffst)
-> SUMMEWENN

=SUMMEWENN(SVERWEIS("w1";$A$8:$V$30;0);UND;SVERWEIS("w2";$A$8:$V$30;0))


Die Bestimmung des zu berechnenden Bereiches durch Verknüpfung zweier SVERWEIS Funktionen mit UND funktioniert leider nischd ...  hmm




Ansatz 2:


Rechne alle Zahlenwerte des Bereichs A8:V30 bis w2 zusammen
-> SUMMEWENN(SVERWEIS("w2";$A$8:$V$30;0))

Rechne alle Zahlenwerte des Bereichs A8:V30 bis w1 zusammen
-> SUMMEWENN(SVERWEIS("w1";$A$8:$V$30;0))
und subtrahiere alle Werte vor (F10)=w1 von dieser Gesamtsumme

= SUMMEWENN(SVERWEIS("w2";$A$8:$V$30;0))-SUMMEWENN(SVERWEIS("w1";$A$8:$V$30;0))


Geht auch nischd ...  roll

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Hallo Anja,

unter der Annahme, dass die Zahlen oberhalb von w1 nur in den Spalten A bis Spalte von w1 stehen, ist die Lösung ganz einfach:

1. Benenne den Bereich A8 bis V30 "Suchbereich" (oder beliebig anders)
2. Benenne die Zelle mit w1 "Start"
3. Trage in die gewünschte Ergebniszelle die Formel ein: =summe(Suchbereich)-summe(a8:Start)

Beim Verschieben von w1 benutze bitte die Maus, sodass der Name Start mit verschoben wird.

MfG

Zuletzt bearbeitet von Joachim (11-08-2009 19:19:12)

8

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Super, ich probier das mal aus!   cool
Vielen lieben Dank.

9

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Joachim schrieb:

... unter der Annahme, dass die Zahlen oberhalb von w1 nur in den Spalten A bis Spalte von w1 stehen, ist die Lösung ganz einfach

Hallo,
das hat wunderbar funktioniert, vielen Dank!
Wenn ich dann aber die Werte zwischen w1 und w2  (6+4+2+4+1+2+6) zusammenrechnen will, funktioniert das nicht mehr ...    sad

Also muss ich vielleicht doch den Summewenn-Weg wählen?

10

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

mit =(WVERWEIS("w1";A1:A13;10;WAHR)) schaffe ich es, dass mir das System in Zelle V 10 den Wert "w1" zurückspielt
mit =SUMMEWENN(V1:V13;"w1";A10:S10) spielt mir dann das System in Zelle W10 den Wert "1" zurück
rechnet also nicht 6 + 4 (aus G10 und H10) zusammen... yikes

Also wieder Fehlanzeige.    roll

11

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Hallo Anja,

leider hatte ich nur das halbe Problem gelesen, nämlich Summe aller Werte nach w1; w2 hatte ich übersehen. Bitte vergiss meinen ersten Beitrag.
Dein Problem erfordert eine etwas umfangreichere Lösung:

Ich benutze die ersten 5 Zeilen des Arbeitsblatts, Du kannst aber jeden beliebigen anderen Bereich verwenden. Erforderlich sind die
Funkltionen wenn(), summe(), zeile(), spalte(), adresse() und indirekt(),

1. Benenne die Zelle mit w1 "Start"
2. Benenne die Zelle mit w2 "Ende"
3. Formatiiere A1 bis F1 mit Zeilenumbruch und Textausrichtung oben
4. Vergrößere Zeilenhöhe Zeile 1 auf ca. 30 pt
5. Trage in folgende Zellen als Überschriften ein (optional, aber übersichtlicher):
    A1: Zelle nach w1
    B1: Ende Zeile w1
    C1: Anfang Zeile unter w1
    D1: Ende  Zeile über w2
    E1: Anfang Zeile w2
    F1: Zelle vor w2
6. Trage in folgende Zellen als Formeln ein:
    A2: =ADRESSE(ZEILE(Start);SPALTE(Start)+1;1)
    B2: =ADRESSE(ZEILE(Start);256;1)
    C2: =ADRESSE(ZEILE(Start)+1;1;1)
    D2: =ADRESSE(ZEILE(Ende)-1;256;1)
    E2: =ADRESSE(ZEILE(Ende);1;1)
    F2: =ADRESSE(ZEILE(Ende);SPALTE(Ende)-1;1)
/. Trage in Zelle A4 als Überschrift ein: Summe der Werte zwischen w1 und w2
8. Trage in Zelle A5 folgende Formel ein:

    =WENN(ZEILE(Ende)-ZEILE(Start)>1;SUMME(SUMME(INDIREKT($A$2):INDIREKT($B$2));SUMME(INDIREKT($C$2):INDIREKT($D$2));
      SUMME(INDIREKT($E$2):INDIREKT($F$2)));WENN(ZEILE(Ende)-ZEILE(Start)=1;SUMME(SUMME(INDIREKT($A$2):INDIREKT($B$2));
      SUMME(INDIREKT($E$2):INDIREKT($F$2)));SUMME(INDIREKT($A$2):INDIREKT($F$2))))

9. Verschiebe w1 und/oder w2 bitte mit der Maus oder mit Ausschneiden und Einfügen und prüfe, dass die Namen (Start/Ende) mit verschoben werden.

Ich schicke einen Screenshot an Oliver, vielleicht kann er ihn hier darstellen. (Das hat leider nicht geklappt, ich kann kein Bild anhängen).

Viel Spaß

Joachim

Zuletzt bearbeitet von Joachim (17-09-2009 19:24:04)

12

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Ohh, das sieht ja ganz schön kompliziert aus. Ich werde mich gleich mal ans Knobeln setzten.
Vielen lieben Dank für deine freundliche Unterstützung!   cool

Wegen dem Screenshot musst du dem Oli nur eine mail schicken, dann antwortet er dir und du erhältst damit seine email-adresse an die du den Screenshot schicken kannst. Ich habe ihm mein Halbkonstrukt auch schon zugemailt, damit er ggf. ein paar Sachen daraus extrahieren und den anderen Forummitgliedern als Veranschaulichung zur Verfügung stellen kann.

Wenn ich mich recht entsinne, so hat er momentan aber etwas viel um die Ohren (Auslandaufenthalt oder so).

Zuletzt bearbeitet von anja (30-09-2009 21:24:23)

13

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Hallo Anja,

Der Joachim hat sich da wirklich Mühe gegeben. Mir wäre so etwas nie eingefallen. Die Frage ist, ob der Aufwand bei Deiner Monstertabelle, die Du mir geschickt hast noch vertretbar und sinnvoll ist. Leider habe ich von Cricket nicht die leiseste Ahnung und sitze etwas hilflos über Deinem Werk. Ich glaube es macht nicht viel Sinn, die Tabelle hier zu posten - Dein oben in Textform dargestellter Ausschnitt repräsentiert die prinzipielle Problematik ja ganz gut (der Rest würde dann wohl eher in ein Cricket Forum passen).
Joachims Ansatz funktioniert tatsächlich - allerdings nur unter der Voraussetzung, dass man die Zellen W1 etc. mit der Maus verschiebt, weil sonst der Name nicht mitverschoben wird. Oder man muss jedesmal, wenn man z.B. w1 eingebt, die Zelle zusätzlich im Namensfeld mit "Start" ("Ende" o.ä.) benennen. Praxistauglich ist das wahrscheinlich wenig(?).

Um die Sache wirklich zu automatisieren, müsste Excel in einem Bereich nach w1 Suchen und den Zellbezug zurückliefern. Genau diese einfache Aufgabe kann man in Excel nicht so einfach in einer Formel machen. Sverweis oder Summewenn durchsuchen auch immer nur die erste Spalte einer Matrix und liefern dann auch nicht Zellbezüge zurück.
Hier müsste wohl entweder ein Programmierer her, der Dir ein VBA-Skript schreibt, oder die ganze Tabelle müsste ganz anders angelegt werden, so dass die Aufgabe mit mehr oder weniger einfachen Formeln zu bewerkstelligen ist. Letzteres kann man natürlich kaum, wenn man das Spiel nicht kennt.

LG, Oliver (aus dem Ausland zurückgekehrt)

[color=#555555]...und hier noch Olivers Kochtipp: wenn Du die Zahnstocher mitessen kannst, hast Du die Rouladen zu lange gekocht!!! :-)[/color]

14

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Oliver schrieb:

Hallo Anja,
Der Joachim hat sich da wirklich Mühe gegeben. Mir wäre so etwas nie eingefallen. Die Frage ist, ob der Aufwand bei Deiner Monstertabelle, die Du mir geschickt hast noch vertretbar und sinnvoll ist. Leider habe ich von Cricket nicht die leiseste Ahnung und sitze etwas hilflos über Deinem Werk.

Das liegt wahrscheinlich weniger daran, dass du Cricket nicht kennst sondern eher daran, dass ich von Excel keine Ahnung habe.   sad
Cricket funktioniert ähnlich wie Baseball mit dem Unterschied, dass zwei Spieler nach dem Schlagen laufen
und man u.a. den Erfolg solcher Partnerschaften statistisch erfassen will.

Oliver schrieb:

Joachims Ansatz funktioniert tatsächlich - allerdings nur unter der Voraussetzung, dass man die Zellen W1 etc. mit der Maus verschiebt, weil sonst der Name nicht mitverschoben wird. Oder man muss jedesmal, wenn man z.B. w1 eingebt, die Zelle zusätzlich im Namensfeld mit "Start" ("Ende" o.ä.) benennen.

- Sobald man w1 für einen Spieler eingibt, soll dass heißen, dass dieser Spieler 'Out' ist.
- Die Punkte die er zusammen mit dem im Spiel bleibenden Partner erzielt hat müssen gezählt werden.
- Dabei müssen seine Zahlen und die Zahlen seines Spielpartners gesondert aufgeführt werden. Und das ist der Knackpunkt.      roll

Der verbleibende Spieler beginnt dann eine neue Partnerschaft die dann mit der Eingabe w2 beim nächsten 'Out' endet (usw. bis zur 10. Partnerschaft).

Oliver schrieb:

Hier müsste wohl entweder ein Programmierer her, der Dir ein VBA-Skript schreibt, oder die ganze Tabelle müsste ganz anders angelegt werden, so dass die Aufgabe mit mehr oder weniger einfachen Formeln zu bewerkstelligen ist.

Ok. Ich schau mal, ob ich das einfacher aufbauen kann.
Momentan löse ich das Problem indem ich eine Summenformel angebe und dann immer den Zellbereich auswähle, der zur jeweiligen Partnerschaft gehört.
Jetzt freue ich natürlich, dass sich der Joachim so eine Mühe gemacht hat. Ich habe mir Beschreibung zur Indirekt Funktion auch schon durchgelesen wusste aber leider nicht, wie ich sie anwenden kann.

Vielen lieben Dank für eure Hilfe!     smile

15

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Hallo Oliver,

mir gefällt Anjas Problem, weil es eine interessante Excel-Aufgabe ist, die zudem einen  praktischen Hintergrund hat. Ich würde es deswegen gern zu Ende bringen. Es wäre schön, wenn Du mir (Anjas Einverständnis vorausgesetzt) ihre "Monstertabelle" zuschicken könntest.

Gruß

Joachim

16

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Hallo Joachim,

herzlichen Dank für dein Angebot!
Die Tabelle hat sich mittlerweile wieder verändert (layout und design), damit sie für den täglichen Gebrauch besser genutzt werden kann. Ich schicke dir die neue Version am besten selbst zu.

Sobald das ganze Ding gebacken ist können wir die Bestandteile, die für dieses Forum relevant sind
dann der Allgemeinheit wieder zur Verfügung stellen. Ist das ok für Euch?

LG
Anja

17

Re: Die Werte zwischen zwei Suchwerten zusammenrechnen

Hallo Anja,

ja, schick bitte an *******

Gruß

Joachim

Zuletzt bearbeitet von Joachim (05-10-2009 11:09:23)