Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit

Excel 2000: Einzelteile der Formel funktionieren, gesamte Formel nicht

Frage Microsoft Microsoft Office

Mitglied: NickMcNaim

NickMcNaim (Level 1) - Jetzt verbinden

27.02.2014, aktualisiert 13:44 Uhr, 2862 Aufrufe, 9 Kommentare

Hallo zusammen!

Ich kann auf der Arbeit nur Excel 2000 nutzen und versuche folgende Formel zum Laufen zu bekommen:

=index(adresse(summe(zeile(index($G$1:$G$149; vergleich($A177; $G:$G; 0))); 1); 7):$G$149; vergleich("*"; $V:$V; 0))

Leider gibt mir Excel immer aus, die eingegebene Formel enthalte einen Fehler, spezifiziert diesen aber nicht weiter.

Besonders seltsam finde ich das, weil die Einzelteile der Formel problemlos funktionieren:

"=adresse(summe(zeile(index($G$1:$G$149; vergleich($A177; $G:$G; 0))); 1); 7)" gibt das korrekte Ergebnis "$G$7" aus.
"=index($G$7:$G$149; vergleich("*"; $V:$V; 0))" gibt das korrekte Ergebnis "2091102" aus.

Ich würde mich freuen, wenn mir jemand dabei helfen könnte, die Gesamtformel lauffähig zu machen.

Vielen Dank im Voraus.
Mitglied: Biber
27.02.2014, aktualisiert um 19:49 Uhr
Moin NickMcNaim,

auch ohne nun extra ein Excel oder Calc dafür anzuwerfen: Das kann so nicht funktioneren und ist auch um drei Ecken zu kompliziert gedacht.


Ich glaube verstanden zu haben, dass die Formel

- in der Spalte G erstma' nach dem Wert aus $A177 suchen soll
- eine Zeile nach der Fund-Zeile beginnt die eigentliche Suche
(z.B. Wert von $A177 gefunden in Zelle $G$6 -> dann neuer Suchbeginn = $G$7)

- Im Bereich $G$7:$:G$149 soll dann das Element x genommen werden
- mit x=vergleich("*"; $V:$V; 0) = erste Zelle mit Test in Spalte V

Dat geiht einfacher, wenn das das Ziel ist.

Der Fehler oben in der Formel ist
"=index($G$7:$G$149; vergleich("*"; $V:$V; 0))" gibt das korrekte Ergebnis "2091102" aus.
Mag ja sein, aber in der Gesamt-Formel hast du nicht stehen:

01.
=index($G$7:$G$149; vergleich("*"; $V:$V; 0))  // das hast du NICHT stehen 
02.
=index("$G$7":$G$149; vergleich("*"; $V:$V; 0))  // das hast du  stehen  !!
Also einen Text "$G$7" unmittelbar gefolgt von einem Doppelpunkt und irgendwas... TILT.

Das wird sich auch bei Excel-Versionen >= 2013 nicht interpretieren lassen.

Sag noch mal, was denn nun der Plan ist, dann entwickeln wir eine Lösung dafür.

Grüße
Biber
Bitte warten ..
Mitglied: NickMcNaim
28.02.2014, aktualisiert um 14:19 Uhr
Hallo Biber!

Dankeschön für Deine schnelle Antwort.

Ich hab's dank Deinem Hinweis gerade doch noch geschafft:

=index(indirekt(adresse(summe(zeile(index($G$1:$G$149; vergleich($A177; $G:$G; 0))); 1); 7)):$G$149; vergleich("*"; $V:$V; 0))

INDIREKT war also des Rätsels Lösung.

Vielen Dank und viele Grüße
NickMcNaim
Bitte warten ..
Mitglied: Biber
28.02.2014, aktualisiert um 15:34 Uhr
Moin NickMcName,

Zitat von NickMcNaim:

Hallo Biber!

Dankeschön für Deine schnelle Antwort.
War ja eher eine Rückfrage als eine Antwort.

Ich hab's dank Deinem Hinweis gerade doch noch geschafft:

=index(indirekt(adresse(summe(zeile(index($G$1:$G$149; vergleich($A177; $G:$G; 0))); 1); 7)):$G$149; vergleich("*"; $V:$V; 0))

INDIREKT war also des Rätsels Lösung.
Nein, Nein. Nein.
"Indirekt()" wäre ein Workaround, um mit deiner Strategie (die IMHO um 3 Ecken zu kompliziert gedacht ist), einen überflüssigen Schritt weiterzukommen.

Aber den Weg will ich dir ja ausreden.
Also, folgender Vorschlag:

In deine Zelle $A$187 oder eine x-beliebige andere Zelle mit ein ein paar freien Zellen darunter:

01.
=WENN(ZÄHLENWENN($V$1:$V$200;"*")<ZEILE($A1);"";INDEX($G$1:$G$200;KGRÖSSTE((V$1:V$200="*")*ZEILE($G$1:$G$200);ZÄHLENWENN($V$1:$V$200;"*")+1-ZEILE($A1))))
Als Matrixformel eingeben, also mit Ctrl-Shift-Enter absch(l)iessen statt nur mit Enter.
Dann nach unten kopieren (aber max in die nächsten 200 Zellen.
Alle Belegnummern (Spalte G) mit einem "*" (Sternchen) in Spalte V werden aufgelistet.

P.S.
Ein bissle verständlicher/lesbarer wird die Formel, wenn du die "Anzahl Zellen mit einem Sternchen" in Spalte V in einem benannten Feld "AnzMarkiert" errechnest (Formel =Zählenwenn(...)) aus der obigen Formel.
Dann reduziert sich die Matrixformel auf:
01.
=WENN(AnzMarkiert<ZEILE($A1);"";INDEX($G$1:$G$200;KGRÖSSTE((V$1:V$200="*")*ZEILE($G$1:$G$200);AnzMarkiert+1-ZEILE($A1))))
( wieder mit Ctrl-Shift-Enter eingeben.)

P.P.S. Es wird NICHT explizit auf ein Sternchen (*") geprüft, gedenfalls nicht von der ZÄHLENWENN()-Funktion. Die interpretiert das als Wildcard, also als "beliebigen Text.
FALLS in Spalte V auch etwas anderes stehen kann als "*" oder "leere Zellen", dann würde ich eher statt "*" ein "x" als Markierung verwenden.
Oder aber, das "*"-Zeichen maskieren bei Zählenwenn: =ZÄHLENWENN($V$1:$V$200;"~*").

Grüße
Biber
Bitte warten ..
Mitglied: NickMcNaim
28.02.2014, aktualisiert um 16:30 Uhr
Hallo Biber!

Dass die Idee mit INDIREKT Humbug war, habe ich zwischenzeitlich auch schon festgestellt, aber auch noch keine richtige Lösung gefunden.

Mal abgesehen davon, dass ich Deine Formel leider nicht verstehe (was ich aber gern tue - eine Erklärung wäre sehr willkommen), habe ich sie jetzt mal in meine Tabelle kopiert.

Das erste, was ich dabei feststellen musste, ist, dass Matrixformeln nicht in verbundenen Zellen erlaubt sind - da müsste ich dann ggfs. das Layout anpassen.

Das zweite ist, dass die Formel in meiner Tabelle nicht funktioniert: Ich bekomme immer das Ergebnis 0 ausgegeben. In einer rasch erstellten Testtabelle, die sich auf die für die Formel notwendigen Zellen beschränkt, funktioniert sie hingegen tadellos.

Erklärungsversuch 1: In der "richtigen" Tabelle werden die Belegnummern durch Bezüge aus einem anderen Tabellenblatt übernommen, die Sternchen aufgrund einer einfachen WENN-Formel gesetzt.

Erklärungsversuch 2: Der Tabellenbereich, in dem gesucht werden soll, ist in vier Blöcke unterteilt (28 gefüllte Zeilen, 15 leere, 28 gefüllte, 15 leere, 28 gefüllte, 15 leere, 16 gefüllte).


P.S.: Nachdem ich den Stern mit einer Tilde maskiert habe, gibt er mir die 0 nur noch bei Treffern an, die anderen Ergebniszellen bleiben leer - schonmal ein Fortschritt.
Bitte warten ..
Mitglied: Biber
28.02.2014, aktualisiert um 17:28 Uhr
Moin NickMcNaim,

okay, Matrix-Formeln in verbundenen Zellen sind etwas tricky.... wenn es sich durch eine Layout-Änderung (also einer Anzeige der Werte nicht in Spalte A, sondern in einer "normalen" Spalte) lösen liesse, wäre es weniger aufwendig.

Matrixformeln funktionieren auch in verbundenen Zellen, aber sie müssen eingegeben werden in "normale" Zellen. Wenn die Formel ein Ergebnis liefert, dann kannst nachträglich diese Zelle mit Nachbarzellen verbinden.

Aber: das tut man nicht. Das ist bei Microsoft ein weder dokumentiertes noch zugesichertes Verhalten und evtl. nicht mal gewollt.

Zu deinem Erklärungsversuch 2/"In Blöcke unterteilt":
Meine Formel sucht ja nur in Spalte V.
Da kann nix schiefgehen (wenn da keine Verbund.Zellen dabei sind).

Der erste Erklärungsversuch "Wenn-Formel" ist hilfreicher:
Wenn ich in "meine" Spalte V eine Wenn-Formel-eingebe, dann habe ich auch Unsinn, wenn das P.P.S. aus meinem letzten Kommentar ignoriert wird.

(Nur mein Test für einfache Wenn-Formel, brauchst du NICHT nachturnen!)
Ich habe in V1 und nach unten kopiert:
01.
=WENN(REST(Zeile($A1);3)=0;"*";"")
...damit bekommt jede dritte Zeile ein "*"

Falls der Zählenwenn-Teil meiner Formel KEINE Maskierung mit Tilde enthält, denn werden ALLE Spalte-V-Zellen als Fundstellen von "*" gewertet. Also als "Ich habe einen beliebigen Text gefunden".

Bitte die Formel MIT Maskierung verwenden:, 2. Parameter ist "~*" für "genau ein Sternchen, ich meine jetzt keine Wildcard"
01.
=WENN(ZÄHLENWENN($V$1:$V$200;"~*")<....
Probier mal, ob das greift.

Wenn ja, dann erklär ich auch die Formel, sonst entsorgen wir die.

P.S. Poste doch mal deine angepasste Formel und schreib, in welcher Zelle sie steht.

Grüße
Biber
Bitte warten ..
Mitglied: NickMcNaim
28.02.2014 um 21:08 Uhr
Hallo Biber!

Vielen Dank erst einmal für Deine Hartnäckigkeit und Deine Geduld mit einem Excel-DAU.

Leider hat es auch nicht geholfen, den Stern im ZÄHLENWENN-Teil Deiner Formel mit einer Tilde zu maskieren.

Ich habe die Formel erst einmal etwa zehn Zellen nach unten gezogen. In diesem Bereich gibt er in den ersten beiden Zeilen eine 0 aus, die übrigen Zeilen lässt er leer. Das ist korrekt, da es bislang nur zwei Sterne gibt. Aber leider gibt er eben nur eine 0 aus und nicht die gewünschte Belegnummer.

Zum besseren Verständnis hier einmal der Inhalt der beteiligten Zellen:

Zelle G5 und darunter:
=Tabelle5!$I15
=Tabelle5!$I16
usw.

Zelle V5 und darunter:
=WENN(ODER(Tabelle5!$Y15=""; Tabelle5!$Y15=" "); ""; "*")
=WENN(ODER(Tabelle5!$Y16=""; Tabelle5!$Y16=" "); ""; "*")
usw.

Zelle A177 und darunter:
=WENN(ZÄHLENWENN($V$1:$V$200;"~*")<ZEILE($A1);"";INDEX($G$1:$G$200;KGRÖSSTE((V$1:V$200="*")*ZEILE($G$1:$G$200);ZÄHLENWENN($V$1:$V$200;"*")+1-ZEILE($A1)))) (mit Strg+Shift+Enter als Matrix-Formel eingegeben)
=WENN(ZÄHLENWENN($V$1:$V$200;"~*")<ZEILE($A2);"";INDEX($G$1:$G$200;KGRÖSSTE((V$1:V$200="*")*ZEILE($G$1:$G$200);ZÄHLENWENN($V$1:$V$200;"*")+1-ZEILE($A2)))) (mit Strg+Shift+Enter als Matrix-Formel eingegeben)
usw.
Bitte warten ..
Mitglied: Biber
28.02.2014, aktualisiert 01.03.2014
Moin NickMcNaim,

mein Fehler.
Ich war davon ausgegangen, dass die Spalten G und V ab Zeile 1 gefüllt sind.
Wenn der Spass erst in Zeile 5 losgeht, dann natürlich:

In dem benamsten Feld "AnzMarkiert"
01.
=ZÄHLENWENN($V$5:$V$200;"~*")
In Zelle A177 (und nach unten kopiert:
01.
=WENN(AnzMarkiert<ZEILE($A1);"";INDEX($G$5:$G$200;KGRÖSSTE((V$5:V$200="*")*ZEILE($G$1:$G$196);AnzMarkiert+1-ZEILE($A1))))
Und jezz' die Erklärung:
01.
=WENN(AnzMarkiert<ZEILE($A1);"";INDEX($G$5:$G$200;KGRÖSSTE((V$5:V$200="*")*ZEILE($G$1:$G$196);AnzMarkiert+1-ZEILE($A1))))
Von aussen nach innen:

=WENN(AnzMarkiert<ZEILE($A1);""; wasanderes)

In "AnzMarkiert" steht zB.bei dir der Wert 2 für zwei gefundene Sternchen.
-> wenn die Anzahl der mit "*" markierten Felder im Bereich $V5:V$200
- kleiner ist als (Wert von Zeile(A1) == 1 in der ersten Formel in Zelle $A$177 --> 2 ist nicht kleiner 1, also mach "wasanderes"
- kleiner ist als (Wert von Zeile(A2) == 2 in der zweiten Formel in Zelle $A$178 --> 2 ist nicht kleiner 2, also mach "wasanderes"
- kleiner ist als (Wert von Zeile(A3) == 3 in der dritten Formel in Zelle $A$179 --> 2 ist kleiner 3, also mach nicht "wasanderes", sondern gib einen Leerstring "" aus
usw.

"wasanderes" besteht aus der Formel:
=INDEX($G$5:$G$200;KGRÖSSTE((V$5:V$200="*")*ZEILE($G$1:$G$196);AnzMarkiert+1-ZEILE($A1))
-oder-
Nimm vom der Matrix =INDEX($G$5:$G$200; diesezeile;dieseSpalte)
Das Argument dieseSpalte habe ich nicht angegeben, es wird der Default 1 angenommen, also die Spalte 1 der einspaltigen Matrix.
(Anmerkung: ich hab unterstellt, dass die Werte von $G$5 bis maximal $G$200 gehen.
Wenn die bei dir nur bis $V$149 gehen, dann Bereich anpassen)

diesezeile errechnet sich aus:
=KGRÖSSTE((V$5:V$200="*")*ZEILE($G$1:$G$196); nGrösstesElement)
-> also von allen Elementen in $V$5;$V$200 eine 1 (==WAHR), wenn ein "*" enthalten ist, mal Zeilennummer
-> bzw. von allen Elementen in $V$5;$V$200 eine 0 (==UNWAHR), wenn kein "*" enthalten ist, mal Zeilennummer

Statt ZEILE($G$1:$G$196) hätte ich auch ZEILE($X$1:$X$196) oder auch nur ZEILE($1:$196) schreiben können,
Hauptsache die Länge der Vektors ist gleich der Länge des Vektors $V$5 bis $V$200


nGrösstesElement
Der Vektor, der im vorigen Schritt berechnet wurde, besteht aus vielen Null-Werten (für FALSCH * ZeileX) und einigen wenigen WAHR*ZeileY-Werten.
Nur diese will ich anzeigen, aber nicht die Grösste zuerst (die in der letzten Fund-Zeile), sondern die in der "obersten" Fundzeile zuerst.

Deshalb die kleine Manipulation:
nGrösstesElement=>AnzMarkiert+1-ZEILE($A1) in der ersten Ausgabezeile A177-> 2+1-1==2
nGrösstesElement=>AnzMarkiert+1-ZEILE($A2) in der ersten Ausgabezeile A178-> 2+1-2==1

usw.

Hoffe,es klappt jetzt und ist klarer.

Grüße
Biber
Bitte warten ..
Mitglied: NickMcNaim
01.03.2014 um 23:26 Uhr
Hallo Biber!

Vielen Dank für Deine ausführliche Erklärung. Ich weiß Dein Engagement sehr zu schätzen.

Nachdem ich weiter Fehlersuche im Ausschlussverfahren betrieben habe, habe ich den Grund, weshalb Deine Formel in meiner Tabelle nicht funktionierte, doch noch gefunden: die WENN-Formel in Spalte V. Lösche ich diese aus allen Zeilen und trage bloß einige Sternchen ein, funktioniert Deine Formel selbst in ihrer ursprünglichen Form einwandfrei, also ohne Sternchenmaskierung und mit der Sternchenzählung (ZÄHLENWENN) ab der ersten Zeile.

Wieso Deiner Formel meine Formel in Spalte V allerdings ins Gehege kommt, ist mir wieder mal schleierhaft. Da steht doch bloß drin:
=WENN(ODER(Tabelle5!$Y15=""; Tabelle5!$Y15=" "); ""; "*")
Was soll daran böse sein?
Bitte warten ..
Mitglied: Biber
01.03.2014 um 23:48 Uhr
Moin NickMcNaim,

da kann ich auch nichts Böses sehen in deiner Formel..
Ich habe es eben in meiner Testmappe nachgestellt und (erwartungsgemäß) macht es keinen Unterschied -läuft nach wie vor.

Meine Vermutung war eher, dass die "Bereichsgrenzen" der Spalten G und V in deiner Tabelle andere sind als in meinem Blindtest.

ich kann dir bestenfalls anbieten, deine Exceltabelle per Mail an meine im Profil angegebene Adresse zu schicken - vorausgesetzt, die ist keine 188 Terabyte gross.

Grüße
Biber
Bitte warten ..
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(3)

Tipp von agowa338 zum Thema Windows 10 ...

Ähnliche Inhalte
Microsoft Office
gelöst Excel: Matirx mit Formel automatisch befüllen (5)

Frage von sims zum Thema Microsoft Office ...

Microsoft Office
Excel verliert die Formel, behält aber den Wert (10)

Frage von derausgroenloh zum Thema Microsoft Office ...

Microsoft Office
gelöst Excel-Formel oder VBA (7)

Frage von nicki01 zum Thema Microsoft Office ...

Microsoft Office
gelöst In Excel das Datum aus einer Registerlasche in einer Formel verwenden (7)

Frage von michael1306 zum Thema Microsoft Office ...

Heiß diskutierte Inhalte
LAN, WAN, Wireless
gelöst Server erkennt Client nicht wenn er ausserhalb des DHCP Pools liegt (28)

Frage von Mar-west zum Thema LAN, WAN, Wireless ...

Outlook & Mail
Outlook 2010 findet ost datei nicht (18)

Frage von Floh21 zum Thema Outlook & Mail ...

Windows Server
Server 2008R2 startet nicht mehr (Bad Patch 0xa) (18)

Frage von Haures zum Thema Windows Server ...