Microsoft Excel

Herbers Excel/VBA-Archiv

Index Formel - Optimierung oder Alternative?

Betrifft: Index Formel - Optimierung oder Alternative? von: Dominic
Geschrieben am: 04.07.2013 13:51:28

Hi Zusammen,

ich benötige in Spalte AG eines Tabellenblatts eine "Ausgabe" verschiedenseter Informationen in Abhängigkeit von B6 des Tabellenblatts "Überblick".

=WENN(Überblick!$B$6=1;"Kein Mitarbeiter ausgewählt!";
WENN(Überblick!$B$6=2;(INDEX(Datensammlungen!$B1632:$B$1641;VERGLEICH(Überblick!$E$8;Datensammlungen!$D1632:$D$1641;0)));
WENN(Überblick!$B$6=3;(INDEX(Datensammlungen!$B1648:$B$1655;VERGLEICH(Überblick!$E$8;Datensammlungen!$D1648:$D$1655;0)))

Das Problem welches ich habe ist, dass der Bereich in AG von Zeile 3 bis 27 gehen muss, da einige Mitarbeiter mehr Kunden haben als der andere.
Wenn ich die oben genannte Formel aber "runterziehe" bis auf Zeile AG27, dann steht auf einmal bei dem ersten Teil meiner Formel:

(In Zelle AG27)

WENN(Überblick!$B$6=2;(INDEX(Datensammlungen!$B1632:$B$1641;VERGLEICH(Überblick!$E$8;Datensammlungen!$D1641:$D$1656;0)));

Doch der "Endwert" in diesem Fall die B1656 muss zwingend wie zuvor angegeben bei 1641 enden - und diesen letzten Wert zwangsläufig bis Zeile AG27 doppelt aufführen.

Beispiel:

AG3: "Kunde 1" (aus der Zelle B1632 entnommen)
AG4: "Kunde 2" (aus der Zelle B1633 entnommen)
AG5: "Kunde 3" (aus der Zelle B1634 entnommen)
...
Die Formeln dahinter sieht momentan so aus:

AG3:
WENN(Überblick!$B$6=2;(INDEX(Datensammlungen!$B1632:$B$1641;VERGLEICH(Überblick!$E$8;Datensammlungen!$D1632:$D$1641;0))

AG4:
WENN(Überblick!$B$6=2;(INDEX(Datensammlungen!$B1633:$B$1641;VERGLEICH(Überblick!$E$8;Datensammlungen!$D1633:$D$1641;0))

AG5:
WENN(Überblick!$B$6=2;(INDEX(Datensammlungen!$B1635:$B$1641;VERGLEICH(Überblick!$E$8;Datensammlungen!$D1635:$D$1641;0))

Da der Mitarbeiter (B6=2, nennen wir ihn Hans) 10 Kunden hat (B1632-1641)
endet ab der Zeile AG12 sein "Kundenbereich" und dahinter soll immer nur der letzte Kunde bis zur Endzeile aufgeführt werden - Also wie folgt:

AG12: Kunde10 (aus der Zelle B1641 entnommen)
AG13: Kunde10 (aus der Zelle B1641 entnommen)
AG14: Kunde10 (aus der Zelle B1641 entnommen)
.... Bis eben Zeile AG27 kommen, da der MA nunmal keine weiteren Kunden hat.

Bei mir aber wird der Endbereich - obwohl er mit $B$1641 angegeben ist auf einmal ab Zelle AG13 in den folgenden Wert geändert:

WENN(Überblick!$B$6=2;(INDEX(Datensammlungen!$B$1641:$B1642;VERGLEICH(Überblick!$E$8;Datensammlungen!$D$1641:$D1642;0))

Wie bekomme ich das anders / besser gelöst?

Vielen Dank im Voraus!

  

Betrifft: Gremlins? von: Klaus M.vdT.
Geschrieben am: 04.07.2013 13:55:13

Hallo Dominic,

der mit $ fixierte Bereich in der Formel ändert sich nicht einfach so. Entweder du hast Gremlins, oder eine VBA-Ereignissroutine spielt dir da rein.

Zum debuggen müsstest du die Datei - mit reproduzierbarem Fehler! - einmal hochladen.

Grüße,
Klaus M.vdT.


  

Betrifft: AW: Gremlins? von: Dominic
Geschrieben am: 04.07.2013 14:41:03

Hi,

doch genau das macht er. Er ändert einfach den festen Bezug!

https://www.herber.de/bbs/user/86209.xlsx


VG und vielen Dank!


  

Betrifft: Das ist ja mal seltsam -- $Bezug in INDEX von: Klaus M.vdT.
Geschrieben am: 04.07.2013 15:07:17

Hi Dominic,

Warum Excel die Formel hier so frech ändert habe ich noch nicht heraus, aber wann!

Du hast diesen Bezug
INDEX(Datensammlungen!$C1654:$C$1655 ... Höhe: zwei Zeilen
einen nach unten kopiert ergibt das
INDEX(Datensammlungen!$C1655:$C$1655 ... Höhe: eine Zeile
und noch einen nach unten kopiert erwartest du:
INDEX(Datensammlungen!$C1656:$C$1655 ... das geht aber nicht! Denn die Höhe des Bezuges ist ja jetzt nur noch MINUS eine Zeile, und das darf sie nicht sein.

Ich konnte das Verhalten auch ausserhalb deiner Monsterformel nachstellen: in einem leeren Blatt in B1
=INDEX(A1:A$5;1)
nach unten kopiert, bringt bis Zeile 5 das erwartete
=INDEX(A5:A$5;1)
und schlägt dann ab Zeile 6 in das falsche
=INDEX(A$5:A6;1)
um. Das habe ich in einem neuen Blatt in einer frischen Excel-Instanz getestet, die Makrofrage ist also ausgeschlossen (war sie eh, da es ein xlsx-Blatt war...)

Excel überschlägt als selbstständig die Fixierung innerhalb der INDEX-Formel, um seinen Bezug zu retten. Meines erachtens dürfte das nicht passieren, stattdessen hätte ich die Fehlermeldung #BEZUG! erwartet ...

Aber ich kann da jetzt aus einer ganz anderen Richtung heran gehen.
Wenn du versuchst, einen Index auf eine -1 hohen Bereich zu erzeugen, dann ist deine Formel eh falsch! Wir müssen jetzt also zwei sachen erreichen:
1) Dir eine valide Formel verschaffen - dafür bräuchte es aber Massen an mehr Infos als deine kastrierte Musterdatei
2) aus rein akademischem Interesse herausfinden, weshalb INDEX() seine Bezüge ändern darf statt Fehlermeldungen auszuwerfen.

Grüße,
Klaus M.vdT.


  

Betrifft: AW: Das ist ja mal seltsam -- $Bezug in INDEX von: Dominic
Geschrieben am: 04.07.2013 15:21:52

Hi,

okay gut zu wissen das ich nicht der einzige bin, den das verwirrt.

Du also eigentlich habe ich schon alles notwendige erzählt außer noch folgendes:

Im Tabellenblatt Überblick ab Zeile B30 erfolgt die Ausgabe der "Kunden" aus dem Tabellenblatt "Datensammlungen".
Im Prinzip "fake" ich damit nur eine dynamische Tabelle, da ich nicht mit einer Pivot Tabelle arbeiten möchte, blende ich via Makros die nicht benötigten Zeilen einfach aus wenn in meinen zig Drop Down Menüs eine Anzeige ausgewählt wird.

https://www.herber.de/bbs/user/86212.txt

Die Formel konnte ich hier nicht einbinden...

Die Formel hier soll überprüfen ob der "nächste" Kunde sprich in dem Fall Zeile 4 gleich dem Kunden in der Zeile davor ist also Zeile 3, wenn ja dann soll er nichts anzeigen.
Ansonsten blendet er den "nächsten" Kunden aus Zeile 4 ein....

Damit habe ich auch die zuvor doppelt genannten Kunden,

AG12: Kunde10 (aus der Zelle B1641 entnommen)
AG13: Kunde10 (aus der Zelle B1641 entnommen)
AG14: Kunde10 (aus der Zelle B1641 entnommen)


um das Beispiel nochmal hervorzuholen: Den "Kunden 10" in meiner Tabelle im Tabellenblatt "Überblick" nur einmalig aufgeführt! :)


  

Betrifft: Du hast eine ... von: Klaus M.vdT.
Geschrieben am: 04.07.2013 15:24:25

... textdatei hochgeladen. Da kann auch ich keine Formeln einbinden!

Grüße,
Klaus M.vdT.


  

Betrifft: AW: Du hast eine ... von: Dominic
Geschrieben am: 04.07.2013 15:27:34

Nein,
ich habe dir nur die Formeln hier zeigen wollen.
Aber das Forum hat mir ne Fehlermeldung ausgespuckt und ich wusste gerade keinen anderen Weg.

Wenn du diese Formeln in Zelle B30 in "Überblick" kopierst und die nächste Formel in Zelle B36 meiner vorhin hochgeladenen Datei wirst du sehen was ich meine!


  

Betrifft: AW: Du hast eine ... von: Dominic
Geschrieben am: 04.07.2013 15:29:42

Ach und die zweite Formel in dem Txt. Dokument ist falsch, da muss statt A6 der Wert A12 hin, logischerweise.

Vg


  

Betrifft: Die Fleißarbeit machst du bitte selbst :-) von: Klaus M.vdT.
Geschrieben am: 04.07.2013 15:37:24

Hallo Domonic,

davon ab dass man größer/kleiner im Forum mit Tilde-F oder dem PRE-Tag maskieren kann ...

ich helf ja gerne, aber ich werd jetzt nicht aus einer Textdatei eine Musterformel heraus korrigieren und in eine Tabelle einfügen und dann hoffen, dass es so ist wie gewünscht!

Eine valide Mustertabelle mit einem reproduzierbarem Fehler zur Verfügung gestellt zu bekommen ist das geringste was ich verlangen kann, um hier meine Zeit zu investieren ...

Grüße,
Klaus M.vdT.


  

Betrifft: AW: Die Fleißarbeit machst du bitte selbst :-) von: Dominic
Geschrieben am: 04.07.2013 15:47:26

Hi,

okay gerne - hier die aktualisierte Datei.
https://www.herber.de/bbs/user/86214.xlsx


Und vielen Dank schon mal!

Vg


  

Betrifft: AW: Die Fleißarbeit machst du bitte selbst :-) von: Klaus M.vdT.
Geschrieben am: 04.07.2013 16:11:58

Hi Dominic,

In Datensammlung!AG3: und runter kopieren
=wenn(zählenwenn($D$1631:$D$1710;Überblick!$E$8)>zeile(A1);INDEX($B$1631:$B$1710; vergleich(Überblick!$E$8;$D$1631:$D$1710;)-1+zeile(A1));"kein Kunde")

in Datensammlung!AH3: und runter kopieren
=wenn(AG3="kein Kunde";"";sverweis(AG3;$B$1631:$C$1710;2;))

ich lade die Tabelle nicht wieder hoch. Ich bekomme die Fehlermeldung "Excel hat nicht genug Ressourcen, um die Berechnungen durchzuführen". Ich sehe, dass du eine UsedRange jenseits von gut und böse in den Tabelle hast. Und warum irgendwas in Zeile 1631 steht muss ich auch nicht begreifen :-)

Ich meine, meine Formeln haben deine Anfrage beantwortet. Nachträglich lege ich dir sehr ans Herz, deine Tabellenstruktur gründlich zu überdenken!

Grüße,
Klaus M.vdT.


  

Betrifft: AW: Die Fleißarbeit machst du bitte selbst :-) von: Dominic
Geschrieben am: 04.07.2013 16:55:09

Hi,

super vielen Dank dafür. Ich werde das gleich sofort mal ausprobieren.

Ja du hast recht, die Tabelle ist extrem umfangreich, und würdest du die vollständige Tabelle kennen/sehen, würde die Struktur auch nicht mehr so schlimm aussehen, sondern durchaus seinen Sinn ergeben.

Das Blatt "Datensammlungen" verfügt eben über alle notwendigen Daten die zur Anzeige und Berechnungen im Blatt "Überblick" notwendig sind. So dass alles flexibel abrufbar und editierbar ist und bleibt.
Vor allem das ich eine "dynamische" Tabelle mittels Makros erstellt habe sieht gar nicht mal so übel aus. Wie du gemerkt hast, bin ich kein Freund von Pivot Tabellen und mag es lieber "einfacher" in Form von Drop-Down Menüs (Formularsteuerelementen). Einfacher kann man es niemanden machen meine Tabelle im Nachgang zu verwenden.

Bis dahin!

Viele Grüße
Dominic


  

Betrifft: AW: Die Fleißarbeit machst du bitte selbst :-) von: Dominic
Geschrieben am: 04.07.2013 17:02:19

Hi,

leider scheint es noch nicht ganz richtig zu sein.
In Zeile AG12 sollte eigentlich noch der letzte Kunde vom Mitarbeiter "Hans" erscheinen,
aber deine Formel "filtert" den Wert schon vorher aus.

Siehe beigefügtem Sreenshot.




  

Betrifft: schau ich mir morgen an, Dominic! von: Klaus M.vdT.
Geschrieben am: 04.07.2013 17:12:29

Hier am Netbook habe ich kein Excel.

Sieht aber nach ner Kleinigkeit aus, Versuch mal in der Formel ZEILE(A1) um +1 oder -1 anzupassen.

Grüße,
Klaus M.vdT.


  

Betrifft: Sorry, ich kann dir nicht helfen denn ... von: Klaus M.vdT.
Geschrieben am: 05.07.2013 09:11:54

... die Tabelle verursacht bei mir "not enough system ressources to display compeltely" und hört dann auf die Formel zu berechnen. Ich vermute jetzt, die Formel stimmt.

Hier steht ein Core-I5 mit 4GB Ram.

Grüße,
Klaus M.vdT.


  

Betrifft: Zurück, ich schaue es mir doch an :-) von: Klaus M.vdT.
Geschrieben am: 05.07.2013 09:14:17

Hallo,

Google ist mein Freund! Hier steht:

http://www.mrexcel.com/forum/excel-questions/62223-not-enough-system-resources-display-completely-quot.html

Der Systemressourcen-Fehler liegt an den Zoomstufen, und das ist auch so. Ich schau mir jetzt die Formel an :-)

Grüße,
Klaus M.vdT.


  

Betrifft: letzter Fix in Formel von: Klaus M.vdT.
Geschrieben am: 05.07.2013 09:17:17

Hallo Dominic,

wie ich schon vermutet habe, es fehlt eine 1

in AG3 und runter kopieren:

=wenn(zählenwenn($D$1631:$D$1710;Überblick!$E$8)>zeile(A1)-1;INDEX($B$1631:$B$1710;  _
vergleich(Überblick!$E$8;$D$1631:$D$1710;)-1+zeile(A1));"kein Kunde") 



  

Betrifft: hat das geholfen, Dominic? o.w.T. von: Klaus M.vdT.
Geschrieben am: 08.07.2013 12:33:14

.


  

Betrifft: AW: hat das geholfen, Dominic? o.w.T. von: Dominic
Geschrieben am: 10.07.2013 09:47:53

Hi,

sorry für die späte Antwort. Ja deine neue Formel funktioniert einwandfrei.
Vielen Dank dafür!


  

Betrifft: Danke für die Rückmeldung! owT. von: Klaus M.vdT.
Geschrieben am: 10.07.2013 10:04:17

.


  

Betrifft: Excel setzt immer die erste Zelle nach vorn von: Rudi Maintaire
Geschrieben am: 04.07.2013 22:22:56

Hallo,
gib einfach mal =summe(b10:a1) ein.


Gruß
Rudi


  

Betrifft: Genau so ist es und auch das ganze ... von: Luc:-?
Geschrieben am: 05.07.2013 01:53:51

…„Geheimnis“ dahinter, Rudi! ;->
Warum Klaus das noch nie aufgefallen zu sein scheint…?!
Es gibt da noch mehr „Gremlins“ (bei uns heißen die aber Kobolde bzw spätestens seit F.Blunck Rullerpucker!). Man kann bspw auch A1.B2 bzw A1..B2 oder A1...B2 schreiben → es wird immer in A1:B2 umgewandelt (unter Xl9 durfte man wohl alternativ nur A1..B2 schreiben) – und das hat nichts mit der normalen Autoersetzung zu tun, denn die würde lt Voreinstellung '...' in '…' umwandeln! Evtl hat das auch etwas mit uralten Lotus 1-2-3-Zeiten zu tun wie die Umwandlung von @ am ZellText(/Fml-)Anfang in =.
Gruß Luc :-?


  

Betrifft: AW: Genau so ist es und auch das ganze ... von: Klaus M.vdT.
Geschrieben am: 05.07.2013 08:30:07

Hi Luc,

Warum Klaus das noch nie aufgefallen zu sein scheint…?!
das ist warscheinlich so, weil ich mir bisher immer vorher Gedanken über die Bereichsgrößen meiner FOrmel gemacht habe :-)
Wirklich, ich hab das hier zum allerersten Mal gesehen. Und auf die Idee, =SUMME(B1:A1) einzugeben bin ich bisher noch nicht gekommen.
Immerhin verstehe ich jetzt die Logik hinter der "Bereichs-Dreherei". Bleibe aber dabei, ein #BEZUG! wäre hier die sinvollere Ausgabe ...

Grüße,
Klaus M.vdT.


  

Betrifft: Man kann eine solche Adresse $B1632:$B$1641 ... von: Luc:-?
Geschrieben am: 05.07.2013 02:20:09

…nicht schreiben, Dominic,
und dann erwarten, dass die EndZelle immer die gleiche bleibt! Dein Konzept ist falsch!
$B1632:$B$1641 umfasst genau 10 Zeilen. Also ist schon 9 Zeilen weiter die 1.Zelle auf dem gleichen Wert wie die (absolute) 2., d.h. der aufgespannte Bereich geht von 10 Zeilen auf 1 Zeile zurück. Da aber eine Zeilenangabe relativ ist, wdn es ab der 11.Zeile wieder 2 Zeilen und dann stetig anwachsend mehr → $B1642:$B$1641, wobei es zu der von Rudi angemerkten Umkehrung kommt → $B$1641:$B1642.
Solche Effekte kann man auch gezielt ausnutzen, aber das ist hier wohl eher nicht der Fall.
Gruß Luc :-?


 

Beiträge aus den Excel-Beispielen zum Thema "Index Formel - Optimierung oder Alternative?"