Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema RefEdit
BildScreenshot zu RefEdit RefEdit-Seite mit Beispielarbeitsmappe aufrufen

Bereich verschieben in Zählenwenn...nur wie?!

Betrifft: Bereich verschieben in Zählenwenn...nur wie?! von: Ria
Geschrieben am: 13.08.2014 12:06:51

Hallo, ich hoffe hier erlöst mich endlich jemand von meinem Excel Problem..

Alsoo, ich habe ein Tabellenblatt 1 mit den auszuwertenden Daten über mehrere Monate. Darin befinden sich z.b. in den Spalten D,F,H, J, L etc. Angaben (insgesamt 4 verschiedene, also Birne, Apfel, Traube, Melone) deren Häufigkeit genau dann gezählt werden soll wenn zuvor in Spalte C der Wert "Yes" steht.
Bisher hat das auch gut geklappt mit Zählenwenn im Tabellenblatt 2:

=ZÄHLENWENNS('Blatt1'!$O7:$O140; "Yes"; 'Blatt1'!V7:V140; "Apfel")
=ZÄHLENWENNS('Blatt1'!$O7:$O140; "Yes"; 'Blatt1'!V7:V140; "Birne")
etc.....
und dann für den nächsten Monat
=ZÄHLENWENNS('Blatt1'!$O7:$O140; "Yes"; 'Blatt1'!X7:X140; "Apfel")

Jetzt habe ich nur das Problem, dass es mich wahnsinnig macht die Formel jeweils an die neue Spalte anzupassen (siehe fett markiertes in Formel oben), da beim kopieren Excel jeweils nur eine Spalte weiterspringt. Mein folgender Versuch hat leider nicht zum Erfolg geführt..:(

=ZÄHLENWENNS('Blatt1'!$O7:$O140; "Yes"; (INDEX(BEREICH.VERSCHIEBEN (Blatt1!$X:$X;;SPALTE()*2-2))); "Apfel")

Jemand eine Idee woran dieser Versuch das ganze mit bereich.verschieben zu lösen scheitert? Hatte auch etwas mit Index versucht...das war aber auch nicht wirklich besser. Wie immer ist die Lösung bestimmt ganz schrecklich einfach und logisch ... :/
Vielen Dank!!

  

Betrifft: das könnte an ZÄHLENWENNS() liegen ... von: neopa C (paneo)
Geschrieben am: 13.08.2014 12:17:48

Hallo Ria,

... die neuen Funktionen sind mE (habs es jetzt nicht noch mal getestet) da bzgl. Integration von noch zu ermittelten Parametern teilweise ziemlich beschränkt bis gar nicht offen.

Nutze für derartige Auswertungen deshalb doch SUMMENPRODUKT() und zwar im Zusammenspiel mit INDEX() , die mE einfacher zu handhaben und vor allem nicht volatil ist.

Gruß Werner
.. , - ...


  

Betrifft: AW: Bereich verschieben in Zählenwenn...nur wie?! von: Daniel
Geschrieben am: 13.08.2014 12:32:25

Hi
ich würde da nicht mit Bereich.Verschieben arbeiten, sondern mit Index.

=ZÄHLENWENNS('Blatt1'!$O7:$O140; "Yes"; Index('Blatt1'!$7:$140;0;$A$1); "Apfel")
in die Zelle A1 schreibst du dann die Nummer der Spalte, die du aktuell sehen willst.
Zum Ändern der Auswertespalte musst du dann nur diese Zelle ändern und du brauchst nicht mehr in die Zählenwenn-Formeln selbst eingreifen.
du kannst hier natürlich auch den Wert aus dem aktuellen Datum berechnen lassen oder die Eingabe über ein DropDown-Feld zur Auswahl komfortabler machen.

Gruß Daniel

ps: warum ist Bereich.Verschieben ungünstig?
Normalerweise berechnet Excel eine Formel nur dann neu, wenn sich im Referenzierten Zellbereich eine Änderung ergeben hat.
Jetzt gibt es Formeln, bei denen das nicht geht, entweder weil sie keinen Zellbereich als paramter haben (Heute(), Zufallsbereich()) oder der Referenzierte Zellbereich ist nicht direkt angegeben sodern man muss erst die Formel berechnen um den tatsächlich referenzierten Zellbereich zu erhalten, dies ist z.B. bei Indirekt und auch Bereich.Verschieben der Fall.
diese Formeln nennt man volatil und sie werden bei JEDER Änderung in Excel neu berechnet, egal wo sie passiert, auch wenns in einem anderen Blatt oder einer anderen geöffneten Datei passiert.
Das ist natürlich schlecht für die Performace und das Arbeiten mit der Exceldatei, insbesondere wenn Zeitaufendige Funktionen damit verbunden sind (wie z.B. ZählenWenn).

bei INDEX wird der referenzierte Zellbereich zwar auch "berechnet", aber da es sich um eine Untermenge des angegebenen Zellbereichs handelt, ist es nicht notwendig diese Funktion volatil zu machen.


  

Betrifft: AW: Bereich verschieben in Zählenwenn...nur wie?! von: Ria
Geschrieben am: 13.08.2014 13:58:35

Hallo Daniel,
danke für die Hilfe. Nur möchte ich die Ergebnisse in einer Tabelle haben (mit der Übersicht über Anzahl der Äpfel etc in den jeweiligen Monaten haben). Dafür müsste ich die Formel ja immer wieder kopieren, in sich an den neuen Zellenbezug anpassen und das dauert ewig...Eine Idee wieso das mit dem SPALTE()*2-2 nicht klappt? Ich möchte excel doch nur sagen dass er beim einfügen der kopierten Formel einfach zwei SPalten weiterspringen soll und nicht nur eine :(
Also, zählenwenns mit zwei Bedingungen wobei eine davon eben immer eine Spalte als Bezug überspringen soll..

ODer vlt versteh ich deine Lösung auch nicht :D


  

Betrifft: AW: Bereich verschieben in Zählenwenn...nur wie?! von: Daniel
Geschrieben am: 13.08.2014 14:08:56

Dann füge in die Index-Formel anstelle des Zellbezugs $A$1 eine Formel ein, die dir die Spaltennummer der Spalte, die du auswerten willst, berechnet.
die Aktuelle Position kannst du mit ZEILE() (Zeilennummer) oder SPALTE() (Spaltennummer) ermitteln.
wie du daraus jetzt die Spaltennummer der Spalte berechnest, musst du selber wissen weil ich deine Datei nicht kenne und nicht weiss, wo was steht, wo welche Formeln hinkopiert werden und welche Spalten sie dann auswerten sollten. Da musst du jetzt mal selber deine grauen Zellen anstrengen.
Du solltest halt möglichst mit INDEX arbeiten und die Finger von BEREICH.VERSCHIEBEN lassen, die Gründe habe ich dir genannt.

Gruß Daniel


  

Betrifft: auch nachgefragt ... von: neopa C (paneo)
Geschrieben am: 13.08.2014 17:40:19

Hallo Ria,

... vielleicht verstehen wir ja auch nur nicht Deine Beschreibung? Wenn Du eine kleine Beispieltabelle hier einstellen könntest, gibt es ja vielleicht auch ganz andere Lösungsideen. Z.B. anstelle der Formeln gleich eine PIVOTauswertung?

Gruß Werner
.. , - ...


  

Betrifft: nachgefragt.... von: neopa C (paneo)
Geschrieben am: 13.08.2014 17:37:29

Hallo Daniel,

... Deine Erklärungen zu "volatil" finde ich gut, doch Deinen letzten Satz verstehe ich entweder nicht oder interpretiere ich falsch. Was genau meintest Du mit "ist es nicht notwendig diese Funktion volatil zu machen.?" Oder steht da "ist" nur aus Versehen und es sollte "war" heißen? Oder?


Gruß Werner
.. , - ...


  

Betrifft: AW: nachgefragt.... von: Daniel
Geschrieben am: 13.08.2014 19:19:37

es war nicht notwendig, diese Funktion volatil zu machen und es ist es heute immer noch nicht ;-)
Gruß Daniel


  

Betrifft: dann sind wir uns ja einig ... von: neopa C (paneo)
Geschrieben am: 14.08.2014 09:33:26

Hallo Daniel,

... Danke für Deine Antwort.

Gruß Werner
.. , - ...


 

Beiträge aus den Excel-Beispielen zum Thema "Bereich verschieben in Zählenwenn...nur wie?!"