Index und Vergleichproblem

Bild

Betrifft: Index und Vergleichproblem
von: Andy
Geschrieben am: 08.10.2003 12:30:52

Hi Members,
als Anhang habe ich mal meine Arbeitmappe angefügt. Wäre nett wenn ihr mir helfen könntet.

Problem:
In der Tabelle "Auswertung 2002" soll die Fläche der Wohnung (in der Spalte U) angeben werden. Die vorhandene Fläche soll aus der Tabelle "Fläche 2002" herausgesucht werden. Dafür muss geprüft werden, ob die Wohnungsnummer (Spalte A) und der Vertragsbeginn (Spalte V) aud der Tabelle "Auswertung 2002" mit der Wohnungsnummer (Spalte A)und dem Nutzungsbeginn (Spalte C) in der Tabelle "Fläche 2002" übereinstimmt.
Ich habe schonmal eine Array-Formel entworfen bei der ich dachte, dass sie funktionieren könnte jedoch ist dem nicht so.

Wäre nett wenn mal jemand nachschauen kann warum dat nicht geht. Vielen dank für die Hlfe!!!

MfG Andy

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

Bild


Betrifft: Index und Vergleichproblem - TEXT statt DATUM
von: Boris
Geschrieben am: 08.10.2003 13:04:32

Hi Andy,

das Problem ist, dass die Datümer in der Tabelle "Mieter" keine Datümer sind, da sie als TEXT formatiert sind.
Daher ergibt auch die Verkettung von A2&V2 eine Ergebnis wie "09.01.0136591".
Da ist VERGLEICH sehr penibel.
Entweder du wandelst die Text-Datümer in echte Datümer um oder du verwendest folgende Formel:

{=INDEX(Fläche2002!B$2:B$226;VERGLEICH(N(A2*1)&V2;Fläche2002!A$2:A$226&Fläche2002!C$2:C$226;0))}

Mit N(A2*1) wird aus dem TEXTdatum in A2 die zugehörige Datumszahl - und dann funktioniert es.

Grüße Boris


Bild


Betrifft: Folgeproblem
von: Andy
Geschrieben am: 08.10.2003 13:50:17

erstmal vielen Dank für deine Hilfe Boris. Funzt super.

Nun habe ich jedoch gleich ein Folgeproblem -und da gebe ich gleich zu, dass übersteigt mein Verständnis für Excel:

jetzt habe ich festgestellt, dass beim beim runterziehen der Formel #wert angezeigt wird. Dies hat damit zutun, dass in der Tabelle "Fläche 2002" der gesamte Nutzungszeitraum der Wohung vorhanden ist. Das heißt, dass Excel nun auch noch innehalb dieser Formel überrüfen muss, ob der Vertragsbeginn aus Tabelle "Auswertung 2002" in Spalte F innerhalb der Nutzungszeit (also Tabelle "Fläche 2002" NZ_BEGINN <= Spalte F und NZ_ENDE >= Spalte F) liegt..... huuii


Bild


Betrifft: Ich hab ja völligen Quatsch geschrieben...
von: Boris
Geschrieben am: 08.10.2003 13:56:45

Hi Andy,

...denn ich hatte gar nicht gesehen, dass Spalte A Wohnungsnummern enthält, sondern ich dachte, es wären Datümer...Reiner Zufall also, dass die Formel funktioniert hat.
Insofern ist das natürlich richtig, dass es als TEXT formatiert ist.
Auf den ersten Blick sehe ich aber, dass im Blatt "Fläche" die Spalte A als DATUM formatiert ist - das muss dann auch als TEXT umformatiert werden.

So long - ich melde mich nochmal...

Grüße Boris


Bild


Betrifft: Schade
von: Andreas
Geschrieben am: 08.10.2003 13:58:58

ach schade ich war gerade so froh das es funzt


Bild


Betrifft: Behalte deine ursprüngliche Formel bei, ABER...
von: Boris
Geschrieben am: 08.10.2003 14:08:13

Hi Andy,

du MUSST vorher im Blatt "Fläche2002" die gesamte Spalte A als Text formatieren.
Achtung: Wenn du sie einfach markierst und dann umformatierst, dann werden die ersten Eingaben von Zeile 2 bis Zeile 20 in ZAHLEN umgewandelt - die verbergen sich nämlich hinter den Datümern.

Mach dazu folgendes:
Schreibe im Blatt "Fläche2002" in I2 folgende Formel und kopier sie runter bis I20:
=TEXT(A2;"TT.MM.JJ")
Anschließend kopiere I2:I20 und füge an gleicher Stelle NUR die Werte wieder ein (Inhalte einfügen-Werte).
Dann I2:I20 erneut kopieren und in A2:A20 einfügen. I2:I20 anschließend wieder löschen.
Jetzt ist alles Text und du brauchst es nicht mehr einzutippen.

Deine Ursprungsformel kannst du dann auch einfach runterkopieren. Wenn ein VERGLEICH nicht gefunden wird, dann erscheint #NV - diesen kannst du entweder über bedingte Formatierung "unsichtbar machen" (Schriftfarbe = Hintergrundfarbe), oder du baust deine Formel in eine WENN(ISTFEHLER(...))-Bedingung ein:
{=WENN(ISTFEHLER(Deine Array-Formel);"";Deine Array-Formel)}

Grüße Boris


Bild


Betrifft: naochmal zum Folgeproblem
von: Andy
Geschrieben am: 08.10.2003 15:04:45

Nun habe ich jedoch gleich ein Folgeproblem -und da gebe ich gleich zu, dass übersteigt mein Verständnis für Excel:

jetzt habe ich festgestellt, dass beim beim runterziehen der Formel #wert angezeigt wird. Dies hat damit zutun, dass in der Tabelle "Fläche 2002" der gesamte Nutzungszeitraum der Wohung vorhanden ist. Das heißt, dass Excel nun auch noch innehalb dieser Formel überrüfen muss, ob der Vertragsbeginn aus Tabelle "Auswertung 2002" in Spalte F innerhalb der Nutzungszeit (also Tabelle "Fläche 2002" NZ_BEGINN <= Spalte F und NZ_ENDE >= Spalte F) liegt..... huuii

=(INDEX(Fläche2002!B$2:B$226;VERGLEICH(A2&V2;Fläche2002!A$2:A$226&Fläche2002!C$2:C$226;0)))

Kann ich den fettmarkierten Bereich noch eine Wennfunktion einsetzen um das obengenannte Problem zu beseitigen??


Bild


Betrifft: #WERT! kann nicht sein - es dürfte nur #NV...
von: Boris
Geschrieben am: 08.10.2003 15:10:43

Hi Andy,

...erscheinen. Hast du vergessen, die Formel als Array-Formel einzugeben?

Grüße Boris


Bild


Betrifft: AW: ja genau #NV sorry
von: Andy
Geschrieben am: 08.10.2003 15:24:24

ja stimmt nicht #wert sondern #nv


Bild


Betrifft: Dann hab ich doch schon geschrieben...
von: Boris
Geschrieben am: 08.10.2003 15:51:34

Hi Andy,

...was du machen kannst (bedingte Formatierung oder ISTFEHLER).
Aber vielleicht hab ich es noch nicht ganz durchschaut...

Grüße Boris


Bild


Betrifft: bestimmt nicht richtig erläutert
von: Andreas
Geschrieben am: 08.10.2003 16:05:30

Hi Boris,
da habe ich mich bestimmt nicht richtig erklärt.
Erstmal funktioniert die Formel jetzt so wie sie soll. Aber nun ist es leider so, dass in der Tabelle "Fläche 2002" der gesamte Nutungszeitraum der Wohnung bis zur nächsten Flächenänderung angegeben ist. Das heißt also eine Wohnung kann vom 01.01.2002 bis zum 31.07.2003 exestiert haben, aber es gibt in der Tabelle ´"Auswertung 2002" z.B 3 Mieter die innerhalbs dieses Zeitraums in der Wohung gewohnt haben. (Mieter1 vom 01.01.2002 - 28.02.2002..wobei dann hier die Formel stimmen würde, und Mieter2 vom 01.03.2002 - 30.06.2002 liegt innerhalb des Nutzungszeitraums der Wohnung und damit auch die gesuchte Fläche aber bei der jetzigen Formel würde Excel diese nicht auswerten.)
Wäre super wenn du mir sagen könntest wie man die Formel umschreiben muss, damit Excel überprüft ob der Vertragsbeginn des Mieters (Tabelle "Auswertung 2002" Spalte V) innerhalb des Nutzungszeitraumes der Wohnung (Tabelle "Fläche 2002")NZ_Beginn und NZ_ Ende liegt.

=(INDEX(Fläche2002!B$2:B$226;VERGLEICH(WENN(A3=Fläche2002!A$2:A$226;WENN(UND(V3>=Fläche2002!C$2:C$226;ODER(V3<=Fläche2002!D$2:D$226;Fläche2002!D$2:D$226=""));Fläche2002!C$2:C$226;""));"");0))

Ich hatte mir das ungefähr so gedacht...aber Denkste. Ich bekomme immer die Fehlermeldung #wert.


Bild


Betrifft: Hab jetzt leider keine Zeit mehr...
von: Boris
Geschrieben am: 08.10.2003 16:21:16

Hi Andy,

...aber ich schau mir das heute Abend nochmals genauer an.
Wenn sich bis dahin kein anderer gemeldet hat, dann melde ich mich wieder - hoffentlich mit der Lösung...;-)

Grüße Boris


Bild


Betrifft: also ich bleib hier sitzen und warte
von: Andy
Geschrieben am: 08.10.2003 20:29:03

*heul keiner antwortet


Bild


Betrifft: Ich bin noch da, aber...
von: Boris
Geschrieben am: 08.10.2003 20:52:17

Hi Andy,

ich weiss nicht, ob ich heut noch den Kopf dafür habe - morgen aber auf JEDEN FALL - versprochen!

Nur, damit du heute nicht mehr zu lange wartest...

Grüße Boris


Bild


Betrifft: AW: also ich bleib hier sitzen und warte
von: Panicman
Geschrieben am: 08.10.2003 22:04:45

Hallo Andy,

ich mische mich zwar ungern ;-)) ein, aber ich habe da mal einen anderen Ansatz als Vergleich genommen.

Vorher aber noch "grundlegende Dinge". Wie Boris schon geschrieben hat, muß erst in Fläche2002 die SpalteA aus dem Datum ein Text gemacht werden. Dannach kannst du auch "richtig" sortieren.
Dann MUSST du in der Tabelle Mieter2002 bei Mietanfang und Mietende etwas reinschreiben wenn kein Datum vorhanden ist. Es darf NICHT Leer sein, da sonst ein falsches Ergebnis kommt.

Ich habe das mal mit dieser Formel gemacht:
=WENN(SUMMENPRODUKT((Fläche2002!$A$1:$A$226=A2)*(Fläche2002!$C$1:$C$226<=V2)*(Fläche2002!$D$1:$D$226>=W2)*ZEILE(Fläche2002!$A$1:$A$226))=0;"k.A.";INDEX(Fläche2002!B$1:B$226;SUMMENPRODUKT((Fläche2002!$A$1:$A$226=A2)*(Fläche2002!$C$1:$C$226<=V2)*(Fläche2002!$D$1:$D$226>=W2)*ZEILE(Fläche2002!$A$1:$A$226));0))

Ich weiß Monsterlegerformel ;-). Das wenn() hab ich eingebaut, damit man keine ARRAY-Formel daraus machem MUSS. Denn ohne Array werden auch falsche Werte geliefert, wenn Summenproduckt NULL ergibt.

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


Ich übernehme keine Verantwortung auf Richtigkeit. Mußt wohl alles noch Nachprüfen.

Gruß
Holger


Bild


Betrifft: Danke für die Unterstützung...
von: Boris
Geschrieben am: 08.10.2003 22:30:15

Hi Holger,

...denn ich hab heute Abend wirklich keinen Kopf mehr dafür gehabt.
Allerdings:
Warum ZEILE(Fläche2002!$A$1:$A$226)
und nicht einfach
ZEILE($1:$226) ?
Dann wird das Monster wenigstens etwas kürzer...;-)

Hab Deine Lösung zwar weder nachvollzogen noch getestet - aber ich denke doch, dass es funktioniert.
Spätestens die Rückmeldung von Andy wird´s zeigen...:-)

Grüße Boris


Bild


Betrifft: Vielen Dank
von: Andi
Geschrieben am: 08.10.2003 22:32:16

Hi Holger,
ich gebe zu dass ich nich mal ansatzweise verstehe wie die formel funktioniert aber es klappt supper.

Vielen Dank


Bild


Betrifft: Bitteschön, hat Spaß gemacht. o.T.
von: Panicman
Geschrieben am: 08.10.2003 22:36:48




Bild


Betrifft: nochmal Danke
von: Andy
Geschrieben am: 08.10.2003 22:41:08

Leider kann man ja in diesem Forum seine Nachrichten nicht editieren. Wollte mich natürlich bei euch beiden bedanken. War ne schwere Geburt! ..lol


 Bild

Beiträge aus den Excel-Beispielen zum Thema " Wenn Formel"