Wenn(Istnv(sverweis... funktioniert nicht

Computer: Software & Programmierung Office Software
1

Hallo zusammen

ich habe mal wieder ein Problem und komme so gar nciht weiter… Hoffe das mir hier jemand behilflich ist!

Ausganglage:
Ich erstelle für Rechnungswesen eine Datei, die alles selbst ausliest. Man muss quasi nur die Kontonummern mit den jeweiligen Anfangs- und Endbeständen und die Buchungssätze eingeben und die Datei ließt sich komplette alles aus und schließt die jeweiligen T-Konten auch übers SBK ab.

Problem:
Es geht darum, dass die Kontonummer nun zugeordnet werden sollen. Ich tippe also alle Kononummer untereinander ein und Excel soll nun die Aktiva-, Passiva und Ertragskonten jeweils auf ein Tabellenblatt übertragen.

Dazu habe ich mir zunächsteinmal ein Tabellenblatt erstellt, auf dem alle Konten drauf sind.

Dann ahbe ich für jede Kontenart auch noch mal ein Hilfsblatt zugefügt, sodass diese quasi ausgelesen werden können… Bei den Aktiv und Passivkonten scheint auch alles zu klappen, aber bei den Ertragskonten funktioniert das ganze nicht… und nciht weiß nciht, woran es liegen kann.

Hier die Formel:
=WENN(‚Konten-Nr.‘!A2="";"";WENN(ISTNV(SVERWEIS(‚Konten-Nr.‘!A2;Konten!A$90:smiley:$186;4;0));"";SVERWEIS(‚Konten-Nr.‘!A2;Konten!A$90:smiley:$186;4;0)))

Konten-Nr. => Blatt, wo die benötigten Kontonummern eingetragen werden
Konten => Blatt mit allen Kontonummern

Weiß jemand, woran es liegen kann, dass ich einfahc kein Ergebniss bekomme??? Ich bin total ratlos!

Lieben Gruss Teufelchen

2

Hi Teufelchen,

ich weiß nich was ein SBK ist und blicke bei den sonstigen Kontoschiebereien auch nicht so durch.

Excelmäßig lese ich die Aussage:

***:den Aktiv und Passivkonten scheint auch alles zu klappen, ***

aber da weiß ich die Formeln nicht.

***:bei den Ertragskonten funktioniert das ganze nicht***
und da postest du die Formel die kein Ergebnis liefert, annehm.

Als Helfer soll ich jetzt feststellen warum mir unbekannte Formeln in 2 Fällen aus mir ubekannten Datensätzen ein richtiges Ergebnis liefern.
Und dann warum eine mir bekannte Formel aus einem mir unbekannten Datensatz kein Ergebnis liefert?

Ich fühle mich da echt überfordert :smile:
Gruß
Reinhard

3

Hallo Teufelchen,

Ausganglage:
Ich erstelle für Rechnungswesen eine Datei, die alles selbst
ausliest. Man muss quasi nur die Kontonummern mit den
jeweiligen Anfangs- und Endbeständen und die Buchungssätze
eingeben und die Datei ließt sich komplette alles aus und
schließt die jeweiligen T-Konten auch übers SBK ab.

Warum die Buchungssätze, die Anfangs- und Endbestände eingeben. Hier sind schnell Zahlendreher möglich. Auch das solltest du einlesen. Ich hatte mir auch mal so eine Datei in gebastelt. Die Daten kamen aus SAP.

Problem:
Es geht darum, dass die Kontonummer nun zugeordnet werden
sollen. Ich tippe also alle Kononummer untereinander ein

Ebenso lassen sich die Kontenbezeichnungen downloaden.

Excel soll nun die Aktiva-, Passiva und Ertragskonten jeweils
auf ein Tabellenblatt übertragen.

Dazu habe ich mir zunächsteinmal ein Tabellenblatt erstellt,
auf dem alle Konten drauf sind.

Dann ahbe ich für jede Kontenart auch noch mal ein Hilfsblatt
zugefügt, sodass diese quasi ausgelesen werden können… Bei
den Aktiv und Passivkonten scheint auch alles zu klappen, aber
bei den Ertragskonten funktioniert das ganze nicht… und
nciht weiß nciht, woran es liegen kann.

Hier die Formel:
=WENN(‚Konten-Nr.‘!A2="";"";WENN(ISTNV(SVERWEIS(‚Konten-Nr.‘!A2;Konten!A$90:smiley:$186;4;0));"";SVERWEIS(‚Konten-Nr.‘!A2;Konten!A$90:smiley:$186;4;0)))

Konten-Nr. => Blatt, wo die benötigten Kontonummern
eingetragen werden
Konten => Blatt mit allen Kontonummern

Wie Reinhard schon meinte, damit kann ein Außenstehender nicht viel anfangen.
Evtl. ist es übersichtlicher, wenn du die Formel anders aufbaust. Ich würde auf die Funktion ISTNV verzichten und statt dessen, erst mit SVERWEIS die Daten in das betreffende Feld holen und anschließend um diese Formel herum mit =Wenn(ZelleXY="";"";SVERWEIS…) die Fehlermeldung abfangen. ZelleXY ist die Zelle, aus der du die Daten holst.
Verstehst du, was ich meine?

Hallo Reinhard,
SBK sind die Sachbuchkonten, ist auch nicht allen kaufmännischen Angestellten oder Buchhaltern bekannt.

Gruß
Maria

4

Hallo Reinhart,

habe mir schon gedacht, dass das ganze ziemlich kompliziert ist… aber leider kann ich es auch nicht anders erklären, das Problem ist, dass ich eigenlich die gleiche Formel verwende… es ändert sich quasi nur die Matrix, in der gesucht wird. Daher ist mir auch unbegreiflich, dass die Formel dann auf einmal nicht funktioniert.

Der Datensatz (also das Blatt, wo alle Kontonummer genannt sind) sieht in etwa (Auszugsweise) so aus:

0800 Andere Anlagen
0810 Werkstätten- und Lagereinrichtungen
2010 Fremdbauteile
2020 Hilfsstoffe
3210 Gesetzliche Rücklagen
3230 Satzugsmäßige Rücklagen
4840 Verbindlichkeiten gegenüber Sozialversicherungsträgern
4850 Verbindlichkeiten gegenüber Mitarbeitern
5420 Entnahme mit USt
5425 Entnahme ohne USt

Mittlerweile habe ich zwischen den eizelnen Kontonummern auch schon keine Leerzeilen…

Tabellnblatt Aktivkonten (Konten mit 0 und 2 am Anfang):
es werden aus dem Eingabeblatt (Konto-Nr.) alle Konten gesucht, die zu dem Bereich (hierbei 0800 bis 2020) gehören und per Namen benannt, welcher in der 4. Spalte des Eingabeblattes steht.
Meine Formel hierzu:
=WENN(‚Konten-Nr.‘!A2="";"";WENN(ISTNV(SVERWEIS(‚Konten-Nr.‘!A2;‚Konten (2)‘!B$6:E$35;4;0));"";SVERWEIS(‚Konten-Nr.‘!A2;‚Konten (2)‘!B$6:E$35;4;0)))
(und es klappt auch!)

Tabellenblatt Passivkonten (Konten mit 3 und 4 am Anfang):
es werden aus dem Eingabeblatt (Konto-Nr.) alle Konten gesucht, die zu dem Bereich (hierbei 3210 bis 4850) gehören und per Namen benannt.
Meine Formel hierzu:
=WENN(‚Konten-Nr.‘!A2="";"";WENN(ISTNV(SVERWEIS(‚Konten-Nr.‘!A2;‚Konten (2)‘!B$36:E$62;4;FALSCH));"";SVERWEIS(‚Konten-Nr.‘!A2;‚Konten (2)‘!B$36:E$62;4;FALSCH)))
(es klappt auch nicht so ganz. wenn man nämlich nur das letzte Konto aufgeführt hat, dann wird es leider nicht gefunden…?)

Tabellenblatt Aktivkonten (Konten ab 5 am Anfang):
ebenso wie die andren…
Meine Formel:
=WENN(‚Konten-Nr.‘!A2="";"";WENN(ISTNV(SVERWEIS(‚Konten-Nr.‘!A2;‚Konten (2)‘!B$63:E$142;4;0));"";SVERWEIS(‚Konten-Nr.‘!A2;‚Konten (2)‘!B$63:E$142;4;0)))
und da klappt leider gar nichts… ich habe es auch schon nur mit sverweis versucht, und dann findet es das Konto aber auch nicht… und ich begreif einfach nicht, warum!

Hoffe, dass mein Problem jetzt etwas deutlicher ist! Wenn nciht, dann kann ich bestimmte Dinge gerne noch deutlicher darstellen…

Lieben Gruß und schon mal vielen Dank für den Willen mir zu helfen! :smile:

Teufelchen

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

5

Hallo Teufelchen,

Hallo Maria

Ausganglage:
Ich erstelle für Rechnungswesen eine Datei, die alles selbst
ausliest. Man muss quasi nur die Kontonummern mit den
jeweiligen Anfangs- und Endbeständen und die Buchungssätze
eingeben und die Datei ließt sich komplette alles aus und
schließt die jeweiligen T-Konten auch übers SBK ab.

Warum die Buchungssätze, die Anfangs- und Endbestände
eingeben. Hier sind schnell Zahlendreher möglich. Auch das
solltest du einlesen. Ich hatte mir auch mal so eine Datei in
gebastelt. Die Daten kamen aus SAP.

Die Daten werden nicht aus einem anderen System eingeselsen, sondern per Hand eingetippt… Es handelt sich dabei um eine Musterlösung für die Berufsschule!

Problem:
Es geht darum, dass die Kontonummer nun zugeordnet werden
sollen. Ich tippe also alle Kononummer untereinander ein

Ebenso lassen sich die Kontenbezeichnungen downloaden.

Excel soll nun die Aktiva-, Passiva und Ertragskonten jeweils
auf ein Tabellenblatt übertragen.

Dazu habe ich mir zunächsteinmal ein Tabellenblatt erstellt,
auf dem alle Konten drauf sind.

Dann ahbe ich für jede Kontenart auch noch mal ein Hilfsblatt
zugefügt, sodass diese quasi ausgelesen werden können… Bei
den Aktiv und Passivkonten scheint auch alles zu klappen, aber
bei den Ertragskonten funktioniert das ganze nicht… und
nciht weiß nciht, woran es liegen kann.

Hier die Formel:
=WENN(‚Konten-Nr.‘!A2="";"";WENN(ISTNV(SVERWEIS(‚Konten-Nr.‘!A2;Konten!A$90:smiley:$186;4;0));"";SVERWEIS(‚Konten-Nr.‘!A2;Konten!A$90:smiley:$186;4;0)))

Konten-Nr. => Blatt, wo die benötigten Kontonummern
eingetragen werden
Konten => Blatt mit allen Kontonummern

Wie Reinhard schon meinte, damit kann ein Außenstehender nicht
viel anfangen.
Evtl. ist es übersichtlicher, wenn du die Formel anders
aufbaust. Ich würde auf die Funktion ISTNV verzichten und
statt dessen, erst mit SVERWEIS die Daten in das betreffende
Feld holen und anschließend um diese Formel herum mit
=Wenn(ZelleXY="";"";SVERWEIS…) die Fehlermeldung abfangen.
ZelleXY ist die Zelle, aus der du die Daten holst.
Verstehst du, was ich meine?

Hallo Reinhard,
SBK sind die Sachbuchkonten, ist auch nicht allen
kaufmännischen Angestellten oder Buchhaltern bekannt.

Gruß
Maria

Gruß

Teufelchen

6

habe mir schon gedacht, dass das ganze ziemlich kompliziert
ist… aber leider kann ich es auch nicht anders erklären, das
Problem ist, dass ich eigenlich die gleiche Formel verwende…
es ändert sich quasi nur die Matrix, in der gesucht wird.
Daher ist mir auch unbegreiflich, dass die Formel dann auf
einmal nicht funktioniert.

Hi Teufelchen,
lösche mal in einer Dateikopie alles außer Spalte A der einen Tabelle und B und E der anderen und lade das mal hoch, www.rapidshare.de , www.badongo.com o.ä.
Gruß
Reinhard

7

Hallo

Der Datensatz (also das Blatt, wo alle Kontonummer
genannt sind) sieht in etwa (Auszugsweise) so aus:

0800 Andere Anlagen
0810 Werkstätten- und Lagereinrichtungen
2010 Fremdbauteile
2020 Hilfsstoffe
3210 Gesetzliche Rücklagen
3230 Satzugsmäßige Rücklagen
4840 Verbindlichkeiten gegenüber Sozialversicherungsträgern
4850 Verbindlichkeiten gegenüber Mitarbeitern
5420 Entnahme mit USt
5425 Entnahme ohne USt
Tabellnblatt Aktivkonten (Konten mit 0 und 2 am
Anfang):
es werden aus dem Eingabeblatt (Konto-Nr.) alle Konten
gesucht, die zu dem Bereich (hierbei 0800 bis 2020) gehören
und per Namen benannt, welcher in der 4. Spalte des
Eingabeblattes steht.
Meine Formel hierzu:
=WENN(‚Konten-Nr.‘!A2="";"";WENN(ISTNV(SVERWEIS(‚Konten-Nr.‘!A2;‚Konten
(2)‘!B$6:E$35;4;0));"";SVERWEIS(‚Konten-Nr.‘!A2;‚Konten
(2)‘!B$6:E$35;4;0)))

In dieser Formel tauchen Bezüge zu zwei Kontoblättern auf: ‚Konten-Nr.‘ und ‚Konten (2)‘ und dann gibt es noch ‚Konto-Nr.‘ wie weiter oben genannt. Ist das so richtig oder handelt es sich hier um einen Schreibfehler?

Eine Formel mit der Funktion SVERWEIS sollte so aussehen:
=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
Deine TeilFormel zu dieser Funktion:

=SVERWEIS(‚Konten-Nr.‘!A2;‚Konten (2)‘!B$6:E$35;4;0)

Das Suchkriterium ist :‚Konten-Nr.‘!A2 ???
Kannst du das bitte prüfen?!
Die Matrix heißt bei in deiner Formel:

‚Konten (2)‘!B$6:E$35

Die Formel holt sich die Daten also nicht aus ‚Konto-Nr.‘ sondern aus 'Konten (2) '. Kannst du auch das bitte prüfen?!
Die Name des Tabellenblattes enthält in der Formel Leerzeichen. Enthält der Name des Tabellenblattes auch tatsächlich die gleichen Leerzeichen?? Kannst du auch das bitte prüfen?!

Und dann ist mir auch noch folgendes unklar:
Wenn die Formel in dem Tabellenblatt Aktivkonto oder Passivkonto steht, woher soll sie die Bezeichnungen der Konten tatsächlich holen?

Gruß
Maria

8

Hallo Reinhard,

hier ist der Link:
http://rapidshare.com/files/4501279/Versuch.xls.html

unter Konten-Nr. werden die zu eröffnenden Kontonummer eingetragen

unter Eingabe sollte die jeweiligen Kontonummern mit ihrem Namen unter der richtigen Rubrick erscheinen.

Konten (2) ist die Datenbank aus der die Namen anhand der eingegeben Nummern in Konten-Nr. rausgesucht werden.

K1: sucht die Aktivkonten raus

K2: sucht die Passivkonten raus

K3: sucht die Ertragskonten raus

Lieben Gruß

Teufelchen

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

9

hier ist der Link:
http://rapidshare.com/files/4501279/Versuch.xls.html
unter Konten-Nr. werden die zu eröffnenden Kontonummer
eingetragen
unter Eingabe sollte die jeweiligen Kontonummern mit ihrem
Namen unter der richtigen Rubrick erscheinen.
Konten (2) ist die Datenbank aus der die Namen anhand der
eingegeben Nummern in Konten-Nr. rausgesucht werden.
K1: sucht die Aktivkonten raus
K2: sucht die Passivkonten raus
K3: sucht die Ertragskonten raus

Hi Teufelchen,

http://www.badongo.com/file/1760352

ps: 6MB ist nicht grad jenes was ich mir unter ner kleinen Beispieldatei vorstelle.

Gruß
Reinhard

10

Hallo Reinhard,

das Problem besteht jetzt nur, wenn die Kontonummer 5000 schon ganz am Anfang eingetragen ist… denn es ist nicht gesagt, dass die Konten in dem ersten Tabellenblatt der Reihe nach aufgelistet werden, außerdem werden es ja auch nie soooo viele Konten… es handelt sich bisher um so ca. 30 Konten. Halt immer andere aus dieser ganzen Palette…

Also ist mein Problem weiterhin nicht wirklich gelöst!

Trotzdem danke, dass du es versucht hast!

LG Teufelchen!

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

11

Nachtrag!
Habe gerade noch was seltsames entdeckt…

Es funktiniert der Bereich 3630 bis 6000 also B50 bis b83 nicht. Alles was darüber oder darunter steht klappt!!!

Weiß jemand, woran das liegen kann??? ist die Tabelle zu groß, oder was???

Lieben Gruß

Teufelchen

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

12

Also ist mein Problem weiterhin nicht wirklich gelöst!

Hi Teufelchen,

nagut schreibe ich halt die 5000 an den Anfang*g:

http://www.badongo.com/file/1760561

Gruß
Reinhard

13

Habe gerade noch was seltsames entdeckt…

Es funktiniert der Bereich 3630 bis 6000 also B50 bis b83
nicht. Alles was darüber oder darunter steht klappt!!!

Weiß jemand, woran das liegen kann??? ist die Tabelle zu groß,
oder was???

Hi Teufelchen,

liegt eher an der Formatierung.

evtl. habe ich dir eben eine nihct aktuelle Version hochgeladen, nimm lieber diesen Link:

http://www.badongo.com/file/1760581

Gruß
Reinhard

14

Hallo Teufelchen,

ich habe das so verstanden. Man gibt in Konten-Nr.! die Nummer des Buchungskontos ein.
Dann erscheint die entsprechende Kontenbezeichnung in K1!, K2! oder K3! je nach dem um welche Kontenklasse es sich handelt.

Hab mir deine Datei angesehen.
Das funktioniert. Dabei erscheinen nicht nur die Ertragskonten sondern auch die Aufwandskonten in K3!, Aktiva und Passiva in K1! bzw. K2!.
Jetzt sollen außerdem die Kontenbezeichnungen in Eingabe! erfolgen. Das geschieht für die Aktiva und für die Passiva, für die Erfolgskonten sind in dem Tabellenblatt keine Formeln hinterlegt. Somit kann hier nichts gefüllt werden.
Übrigens fehlen auch in K3! in Spalte A ab Zeile 38 die Formeln, in Spalte B schon ab Zeile 33, in Spalte C ab Zeile 28. Entsprechend in K1! und K2!
Es funktioniert auch mit Kontonummer 5000 als Eintrag in Konten-Nr.! in A2.

Ich habe alle Kontonummern durch kopieren aus Konten (2)! eingefügt. Die Zellen sind dort als Text formatiert. Sverweis beachtet auch das Format der Zelle, also Text oder Zahl. Evtl. liegt hier dein Problem. Die Eingaben in Konto-Nr.! müssen also auf jeden Fall auch als Text erfolgen.

Du schreibst, dass auch die Kontonummern erscheinen sollen – aber wo? Dafür gibt es keine einzige Formel.

Gruß
Maria

15

Hallo Reinhard,

was meinst du damit, dass es wohl an der Formatierung liegt??? Was muss ich denn anders einstellen, denn bei mir klappt es ja leider nicht! Und die Formeln hast du doch nicht verändert, oder habe ich da falsch geguckt???

Danke schon mal für deine riesen Hilfe!!!

Lieben Gruß

Teufelchen!

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

16

Hallo Teufelchen,

Hallo Maria

ich habe das so verstanden. Man gibt in Konten-Nr.! die Nummer
des Buchungskontos ein.
Dann erscheint die entsprechende Kontenbezeichnung in K1!, K2!
oder K3! je nach dem um welche Kontenklasse es sich handelt.

Soweit schon mal richtig!

Hab mir deine Datei angesehen.
Das funktioniert. Dabei erscheinen nicht nur die Ertragskonten
sondern auch die Aufwandskonten in K3!,

ja stimmt… habe mich vertan… wollte Erfolgskonten schreiben! :smile:

Aktiva und Passiva in

K1! bzw. K2!.
Jetzt sollen außerdem die Kontenbezeichnungen in Eingabe!
erfolgen. Das geschieht für die Aktiva und für die Passiva,
für die Erfolgskonten sind in dem Tabellenblatt keine Formeln
hinterlegt. Somit kann hier nichts gefüllt werden. Ja das stimmt, aber mein Problem ist, dass ich auc hschon in K3 die Bezeichnung nicht erhalte!

Übrigens fehlen auch in K3! in Spalte A ab Zeile 38 die
Formeln, in Spalte B schon ab Zeile 33, in Spalte C ab Zeile
28. Entsprechend in K1! und K2!
Es funktioniert auch mit Kontonummer 5000 als Eintrag in
Konten-Nr.! in A2.

Das habt ihr alle etwas falsch verstanden. Also es werden insgesamt (Aktiva + Passiva + Erfolgskoten) ca. 30 Konten aufgeführt. Bei der Eingabe sollten diese dann aber alle direkt untereinander stehen, also keine Leerzeilen mehr bilden. Dafür habe ich bei K1, K2 und K3 ja noch die Spalten C, E, und G mit total verschachtelten Wenn-Formeln gefüllt, damit diese halt alle direkt untereinander stehen… und dann für die Eingabe die Bezeichnungen direkt aus der Spalte G genommen. (evtl. gibt es dafür ja auch viel einfachere Formeln, quasi dass die Eingabe aus der ersten Zeile nimmt, in der etwas steht… lerne schließlich gerne dazu!)

Ich habe alle Kontonummern durch kopieren aus Konten (2)!
eingefügt. Die Zellen sind dort als Text formatiert. Sverweis
beachtet auch das Format der Zelle, also Text oder Zahl. Evtl.
liegt hier dein Problem. Die Eingaben in Konto-Nr.! müssen
also auf jeden Fall auch als Text erfolgen.

Eigentlihc habe ich jetzt alle Zellen auf Text gestellt und irgendwie scheint es trotzdem nicht zu funktionieren… :frowning: bin schon total verzweifelt!

Du schreibst, dass auch die Kontonummern erscheinen sollen –
aber wo? Dafür gibt es keine einzige Formel.

Gruß
Maria

Gruß Teufelchen

17

liegt eher an der Formatierung.
evtl. habe ich dir eben eine nihct aktuelle Version
hochgeladen, nimm lieber diesen Link:
http://www.badongo.com/file/1760581

was meinst du damit, dass es wohl an der Formatierung liegt???
Was muss ich denn anders einstellen, denn bei mir klappt es ja
leider nicht! Und die Formeln hast du doch nicht verändert,
oder habe ich da falsch geguckt???

Hi Teufelchen,
mir fehlt dein Feedback ob denn meine Datei funktioniert, ich meine ja.

Mit Formatierung, hier Text, meine ich, auch wenn in der Zelle was steht und unter Format sieht man „Text“ so heißt das noch lange nicht dass Excel das auch als Text sieht, gerade bei Sverweis.
wEnn zum Beispiel in A2 5000 steht und in der Suchspalte Bx auch 5000 und beide Zellen sind als Text formatiert, so findet Sverweis nicht unbedingt was.
Dann in beiden Zellen die 5000 jeweils manuell nocheinmal eingeben, dann müßte es klappen.
Ich mutmaße mal, mit K5000 oder nr5000 anstatt 5000 gäbs den Zusatzstress nicht.
Gruß
Reinhard

18

Danke schön!
Hallo Reinhard,

jetzt scheint alles zu klappen!!! :smile:

Habe wie du es gesagt den genannten Bereicht komplett gelöscht und neu eingegeben…

Super!!!
Danke schön!!!

LG

Teufelchen