Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1344to1348
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Funktion Bereich.Verschieben

Funktion Bereich.Verschieben
16.01.2014 18:20:03
lee
Hat jemand eine Idee, wie man diese Funktion etwas kürzer zusammenfassen kann?
SUMME(BEREICH.VERSCHIEBEN(EL11;0;$B$2-988;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-969;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-950;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-931;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-912;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-893;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-874;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-855;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-836;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-817;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-798;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-779;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-760;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-741;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-722;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-703;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-684;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-665;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-646;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-627;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-608;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-589;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-570;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-551;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-532;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-513;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-494;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-475;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-456;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-437;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-418;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-399;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-380;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-361;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-342;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-323;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-304;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-285;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-266;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-247;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-228;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-209;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-190;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-171;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-152;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-133;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-114;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-95;1;1);(BEREICH.VERSCHIEBEN(EL11;0;$B$2-76;1;1);BEREICH.VERSCHIEBEN(EL11;0;$B$2-57;1;1);BEREICH.VERSCHIEBEN(EL11;0;$B$2-38;1;1);BEREICH.VERSCHIEBEN(EL11;0;$B$2-19;1;1))))))))))))))))))))))))))))))))))))))))))))))))))/52
Mit Hilfe dieser FUnktion wird eine Summe gebildet, wobei sich der Summenbereich in verschiedenen Spalten befinden.
Gruß
lee

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Besser wäre Musterdatei,...
16.01.2014 18:34:53
Matze
Hallo lee,
...relevante Daten kannst du durch Musterbeispiele ersetzen.
So erkenne ich zumindest den Aufbau/Ablauf nicht. Hab auch keine Lust das nach zu bauen.
Dürfte auch VBA in Frage kommen? Gib mal Input dazu.
Gruß Matze

AW: Besser wäre Musterdatei,...
20.01.2014 18:01:44
lee
Ich würde gerne auf VBA verzichten.

AW: Funktion Bereich.Verschieben
16.01.2014 19:07:38
{Boris}
Hi,
ohne Beispieldatei nur allgemein in dieser Art:
=SUMMENPRODUKT((REST(SPALTE(EL:IV)+B2;19)=0)*EL11:IV11)/52
VG, Boris

Anzeige
AW: Funktion Bereich.Verschieben
20.01.2014 18:06:35
lee
Hi Boris,
leider funktioniert deine Funktion nicht.
Nun habe ich eine Beispieldatei hochgeladen.
Vielleicht ist das Problem nun klarer.
Gruß
lee

AW: Funktion Bereich.Verschieben
16.01.2014 20:45:35
Dieter
Hallo lee,
du kannst das mit der folgenden benutzerdefinierten Funktion machen:
Function Summation(startZelle As Range, versatz As Range) As Currency Dim i As Long Dim startSpalte As Long Dim startZeile As Long Dim vers As Long Dim ws As Worksheet Set ws = Application.Caller.Parent startZeile = startZelle.Row startSpalte = startZelle.Column vers = versatz.Value For i = 1 To 52 Summation = Summation + ws.Cells(startZeile, startSpalte + vers - i * 19) Next i Summation = Summation / 52 End Function Aufruf der Funktion im Tabellenblatt:
=Summation($E$11;$B$2)
mfg
Dieter

Anzeige
ohne BEREICH.VERSCHIEBEN
18.01.2014 11:35:39
Erich
Hi,
probier mal die Formeln in B3 und B4:
 ABC
1 38402 
2 1100 
3 38402 
4 38402 
5   
11123

Formeln der Tabelle
ZelleFormel
B1=SUMME(BEREICH.VERSCHIEBEN(EL11;0;$B$2-988;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-969;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-950;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-931;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-912;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-893;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-874;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-855;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-836;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-817;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-798;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-779;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-760;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-741;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-722;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-703;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-684;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-665;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-646;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-627;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-608;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-589;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-570;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-551;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-532;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-513;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-494;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-475;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-456;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-437;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-418;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-399;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-380;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-361;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-342;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-323;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-304;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-285;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-266;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-247;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-228;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-209;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-190;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-171;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-152;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-133;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-114;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-95;1;1); (BEREICH.VERSCHIEBEN(EL11;0;$B$2-76;1;1); BEREICH.VERSCHIEBEN(EL11;0;$B$2-57;1;1); BEREICH.VERSCHIEBEN(EL11;0;$B$2-38;1;1); BEREICH.VERSCHIEBEN(EL11;0;$B$2-19;1;1))))))))))))))))))))))))))))))))))))))))))))))))))
B3{=SUMME(WENN(REST(SPALTE(INDEX(11:11;B2-846): INDEX(11:11;B2+123)); 19)=REST(B2-10;19); INDEX(11:11;B2-846):INDEX(11:11;B2+123)))}
B4=SUMMENPRODUKT((REST(SPALTE(INDEX(11:11;B2-846): INDEX(11:11;B2+123)); 19)=REST(B2-10;19)) *INDEX(11:11;B2-846):INDEX(11:11;B2+123))
A11=SPALTE()
B11=SPALTE()
C11=SPALTE()
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: ohne BEREICH.VERSCHIEBEN
20.01.2014 18:16:00
lee
Hi,
danke für die Hilfe, aber leider klappt es nicht. Habe nun eine Bsp.Datei hochgeladen,
vielleicht ist das Problem jetzt eindeutiger.
https://www.herber.de/bbs/user/88887.xls
Gruß
lee
Gruß
lee

nicht so toll
20.01.2014 19:23:06
Erich
Hi,
da stören mich jetzt mindestens 6 Dinge:
1.
"aber leider klappt es nicht" reicht als Fehlermeldung einfach nicht aus.
In der Mappe ist nicht zu sehen, dass du eine der Formeln ausprobiert hättest.
2.
Mit einer Tabelle, in der haufenweise "#BEZUG!" oder "#WERT!" steht, auch in Zellen,
die für die Formeln relevant sind, lässt sich wenig anfangen.
Insbesondere: In der großen Formel kommt ständig $B$2 vor. Die Formel in B2 ist aber
=WVERWEIS(MIN(1:1);#BEZUG!;2;0)
Worüber geht da der WVERWEIS?
Für die Antwort auf deine Frage (Verkürzung/Vereinfachung der langen Formel) hätte eine konstante Zahl in B2
vermutlich ausgereicht.
3. In der Mappe ist ein Name "Jahr" definiert. JAHR ist schon Name einer Excelfunktion.
4.
Die Mappe enthält zu Jahr eine Verknüpfung auf eine uns nicht vorliegende andere Mappe.
W:\7242-Vertrieb\Distrib\Program meeting\REPORTS\ORDER ENTRY_NEW\[Weekly Rates 2014.xlsm
5.
Du schreibst nicht, welche Zelle mit der Formel berechnet werden soll (in (Spalte EI? oder EJ?).
Auch die zu ersetzende lange Formel (die aus deinem ersten Post) habe ich nicht gefunden.
6. Du schreibst nicht, welches Ergebnis die Formel haben soll.
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: nicht so toll
24.01.2014 17:12:35
lee
Hi,
Sorry, habe die falsche Datei hochgeladen.
Eigentlich möchte ich den Durchschnittswert der letzten 4 & 52 Kalenderwochen ermitteln.
Jetzt zu deinen Fragen bzw. Unklarheiten:
1.
Ich habe es versucht, aber leider nicht die richtige Mappe hochgeladen. Dumm von mir.
2. In der Zelle B2 wird die Stelle der aktuellen Kalenderwoche ermittelt. Es ist wichtig,
um die Durchschnittswerte der letzten 4 & 52 Wochen mit Hilfe der Funktion „Bereich.Verschieben“ zu ermitteln.
z.B. wenn wir jetzt in der KW05 sind, dann sollen die Werte aus KW04 bis KW01 (rückwärts) addiert dann durch 4 geteilt werden.
Dies gilt auch für die 52 Wochen.
Für die Ermittlung des Durchschnittswertes der letzten 4 Wochen, kannst du in Zeile B2, den Wert 103 eingeben.
3. Den Namen „JAHR“ kannst du ignorieren
4. hat keinen Einfluss auf die Berechnung
5. Den Durchschnittswert der letzten vier Wochen in der Spalte E1 und die der 52 Wochen in der Spalte EJ.
Ich möchte die Werte aus den gelb markierten Spalten pro Zeile addieren und dann durch 4 (in Spalte E1) und 52 (in Spalte EJ) teilen.
6. z.B. Wir sind in der KW05, d. h. der Wert in Zelle B2 ist dann 103
Dann ergibt sich in der Zelle das Ergebnis 13 (EW25 =4 + FT25 = 48 + GQ25 = 0 + HN25 = 0, Summe = 52 durch 4 ist dann 13
Wenn du eine andere bessere Idee hast, um die letzte 4 und 52 Wochen, ohne den Hilfswert in der Zelle B2, zu ermitteln, dann bin ich auch froh darüber.
Gruß
lee
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige