Microsoft Excel

Herbers Excel/VBA-Archiv

BEREICH.VERSCHIEBEN (erst ab Zeile 7) | Herbers Excel-Forum


Betrifft: BEREICH.VERSCHIEBEN (erst ab Zeile 7) von: Bruno
Geschrieben am: 11.01.2012 10:07:48

Guten Morgen werte Excel-Profis

Für eine Auswertungsaufgabe habe ich hier im Forums-Archiv eine Excel-Mappe (unterschiedliche_werte_einer_spalte.xls) mit folgender Formel gefunden:

{=WENN(SUMME(WENN(TabelleD!C$1:C$200="";0;1/ZÄHLENWENN(TabelleD!C$1:C$200;TabelleD!C$1:C$200))) < ZEILE();""; INDIREKT("TabelleD!C"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(TabelleD!C$1:C$200; ;; ZEILE(TabelleD!C$1:C$200));TabelleD!C$1:C$200)=1;ZEILE(TabelleD!C$1:C$200));ZEILE()))) }

Diese funktioniert grundsätzlich, aber leider nur, wenn die Datenquelle (TabelleD) in Zeile 1 oder 2 beginnt (getestet mit einer Tabellenkopie ohne vorgehende 6 datenfreie Zeilen). In meiner TabelleD werden die Zeilen 1 bis 6 für zusätzliche Auswertungen wie Filterwerte und Spaltengruppen-Beschriftungen verwendet, es sind daher erst ab der Zeile 7 Daten eingetragen. Lässt sich die vorstehende Formel auf einen Datenbereich ab Zeile 7 bis zum Tabellenende anpassen und wenn ja, wie?

Dann möchte ich die erhaltenen Resultate eigentlich gerne aufsteigend sortiert; ist dies möglich?

Für Eure Tipps zum Voraus besten Dank.

  

Betrifft: AW: BEREICH.VERSCHIEBEN (erst ab Zeile 7) von: Luschi
Geschrieben am: 11.01.2012 11:23:45

Hallo Bruno,

so klappt es bei mir, wenn die Auswerteformel in Tabelle2 in Zelle A9 beginnt:

{=WENN(SUMME(WENN(Tabelle1!A$2:A$101="";0;1/ZÄHLENWENN(Tabelle1!A$2:A$101;Tabelle1!A$ _
2:A$101))) 
< ZEILE()-8;"";INDIREKT("Tabelle1!A"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Tabelle1!A$2;;;ZEILE(Tabelle1!A$2:A$101));Tabelle1!A$2:A$101)=1;ZEILE(Tabelle1!A$2:A$101));ZEILE()-8)))}
Die Datei (von Hajo) habe ich hier gefunden:
http://www.ms-office-forum.net/forum/showthread.php?t=135767#2

Und die Erklärung gibt es hier hier:
http://www.excelformeln.de/formeln.html?welcher=194

Gruß von Luschi
aus klein-Paris


  

Betrifft: AW: BEREICH.VERSCHIEBEN (erst ab Zeile 7) von: Bruno
Geschrieben am: 11.01.2012 15:09:22

Grüezi Luschi

Erstmal besten Dank für Deine Hinweise. Beim ersten Versuch habe ich vorerst mein Ziel noch nicht erreicht; hier das leider noch unvollständige Ergebnis:

 A
1 
2 
3 
4 
5 
6 
7 
8 
91927
101931
111932
12#ZAHL!
13#ZAHL!
14#ZAHL!
15 

Formeln der Tabelle
ZelleFormel
A9{=WENN(SUMME(WENN(Sturzdaten!C$7:C$104="";0;1/ZÄHLENWENN(Sturzdaten!C$7:C$104;Sturzdaten!C$7:C$104)))< ZEILE()-8;"";INDIREKT("Sturzdaten!C"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Sturzdaten!C$7;;;ZEILE(Sturzdaten!C$7:C$104)); Sturzdaten!C$7:C$104)=1;ZEILE(Sturzdaten!C$7:C$104)); ZEILE()-8)))}
A10{=WENN(SUMME(WENN(Sturzdaten!C$7:C$104="";0;1/ZÄHLENWENN(Sturzdaten!C$7:C$104;Sturzdaten!C$7:C$104)))< ZEILE()-8;"";INDIREKT("Sturzdaten!C"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Sturzdaten!C$7;;;ZEILE(Sturzdaten!C$7:C$104)); Sturzdaten!C$7:C$104)=1;ZEILE(Sturzdaten!C$7:C$104)); ZEILE()-8)))}
A11{=WENN(SUMME(WENN(Sturzdaten!C$7:C$104="";0;1/ZÄHLENWENN(Sturzdaten!C$7:C$104;Sturzdaten!C$7:C$104)))< ZEILE()-8;"";INDIREKT("Sturzdaten!C"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Sturzdaten!C$7;;;ZEILE(Sturzdaten!C$7:C$104)); Sturzdaten!C$7:C$104)=1;ZEILE(Sturzdaten!C$7:C$104)); ZEILE()-8)))}
A12{=WENN(SUMME(WENN(Sturzdaten!C$7:C$104="";0;1/ZÄHLENWENN(Sturzdaten!C$7:C$104;Sturzdaten!C$7:C$104)))< ZEILE()-8;"";INDIREKT("Sturzdaten!C"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Sturzdaten!C$7;;;ZEILE(Sturzdaten!C$7:C$104)); Sturzdaten!C$7:C$104)=1;ZEILE(Sturzdaten!C$7:C$104)); ZEILE()-8)))}
A13{=WENN(SUMME(WENN(Sturzdaten!C$7:C$104="";0;1/ZÄHLENWENN(Sturzdaten!C$7:C$104;Sturzdaten!C$7:C$104)))< ZEILE()-8;"";INDIREKT("Sturzdaten!C"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Sturzdaten!C$7;;;ZEILE(Sturzdaten!C$7:C$104)); Sturzdaten!C$7:C$104)=1;ZEILE(Sturzdaten!C$7:C$104)); ZEILE()-8)))}
A14{=WENN(SUMME(WENN(Sturzdaten!C$7:C$104="";0;1/ZÄHLENWENN(Sturzdaten!C$7:C$104;Sturzdaten!C$7:C$104)))< ZEILE()-8;"";INDIREKT("Sturzdaten!C"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Sturzdaten!C$7;;;ZEILE(Sturzdaten!C$7:C$104)); Sturzdaten!C$7:C$104)=1;ZEILE(Sturzdaten!C$7:C$104)); ZEILE()-8)))}
A15{=WENN(SUMME(WENN(Sturzdaten!C$7:C$104="";0;1/ZÄHLENWENN(Sturzdaten!C$7:C$104;Sturzdaten!C$7:C$104)))< ZEILE()-8;"";INDIREKT("Sturzdaten!C"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Sturzdaten!C$7;;;ZEILE(Sturzdaten!C$7:C$104)); Sturzdaten!C$7:C$104)=1;ZEILE(Sturzdaten!C$7:C$104)); ZEILE()-8)))}
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


Vielleicht kannst Du die Ursache für den Fehler auf die Schnelle lokalisieren. Zumindest das aufsteigende Sortieren scheint geklappt zu haben. Die Quell-Zellen befinden sich in der Sturztabelle der Spalte C ab Zeile 7. Die Formel habe ich gemäss Deinem Vorschlag in A9 der Zieltabelle platziert. Jedenfalls werde ich mir die empfohlenen Seiten etwas näher ansehen und versuchen, die verschiedenen Matrix-Formel besser zu verstehen, damit ich sie richtig interpretieren auf meine Bedürfnisse anpassen kann.

Gruss, Bruno


  

Betrifft: AW: BEREICH.VERSCHIEBEN (erst ab Zeile 7) von: Luschi
Geschrieben am: 11.01.2012 17:40:22

Hallo Bruno,

hier mal die angepaßte Beispieldatei:

https://www.herber.de/bbs/user/78368.xls

Gruß von Luschi
aus klein-Paris


  

Betrifft: AW: BEREICH.VERSCHIEBEN (erst ab Zeile 7) von: Bruno
Geschrieben am: 12.01.2012 11:13:20

Danke Luschi für Deine Bemühungen. Übertrage ich die Spalte mit den Jahrzahlen in eine separate Tabelle, dann funktionierts (ausser der aufsteigenden Reihenfolge!!). Beim Auszug aus der kompletten Datenliste klemmts leider noch immer. Ich werde also vorerst den Umweg über die separate Tabelle nehmen müssen und dabei zugleich die Jahrzahlen vor der Übertragung aufsteigend sortieren.

Gruss, Bruno


  

Betrifft: AW: BEREICH.VERSCHIEBEN (erst ab Zeile 7) von: Luschi
Geschrieben am: 12.01.2012 11:28:19

Hallo Bruno,

da hilft nur reingucken in die Ori-Datei; aber wenn da Betriebsgeheimnisse
drinstehen, ist es für einen Außenstehenden schlecht, den i-Punkt zu finden.

Gruß von Luschi
aus klein-Pris