Sverweis aber ohne Leerzeilen
Lars Witter
ich habe ein kleines (?) Problem ...
Also ich habe ein Tabellenblatt auf dem ich verschiedene Werte eingebe
Nummer1, Datum1, Text1a, Text1b
Nummer2, Datum2, Text2a, Text2b
Sowie beiden gemeinsam Nummer1_2
Nun habe ich ein zweites (mehrere) Tabellenblatt, auf dem abhängig
davon ob Text1a oder Text2a gesetzt/gefüllt ist, eine Tabellenstruktur
erstellt werden soll (T-Konto ähnlich aus der Buchhaltung) , nach dem
muster
Nummer1_2, Nummer1, Datum1, Text1a, Text1b <Trennlinie> Nummer1_2,
Nummer1, Datum1, Text2a, Text2b
Wobei die Auswahl der Tabellenblätter von der Nummer1/Nummer2 abhängig
ist/sein soll.
Ich habe es mit Sverweis probiert, aber da erhalte ich N/V bzw. via
ISTNV eben leere Zeilen, und das will ich nicht, ich möchte alle
entsprechenden Werte direkt untereinander und direkt nebeneinander.
Über VBA wäre es ja kein Problem, aber ich möchte kein VBA verwenden.
Jemand nen Tip?
Grüße & und schonmal Danke fürs Lesen bis hier her.
Lars
Lars Witter
vergessen: Meine Excel-Version Excel 2002
Grüße
Lars
Lars Witter
hab ich irgendwas falsch gemacht - oder hat keiner ne idee?
Grüße
Lars
Peter Dorigo
"Lars Witter" <mails...@gmx.de> schrieb im Newsbeitrag
news:9mam5vo8kivvco78n...@4ax.com...
> hi,
>
> hab ich irgendwas falsch gemacht - oder hat keiner ne idee?
zu wenig klar beschrieben? (zumindest für mich)
versuch's nochmals!
mfg Peter
--
MVP für MS-Excel
---------------------------
Lars Witter
sonst sagen die leute immer ich würde zu viel sagen/schreiben ;-)
ich habe eine Liste mit bestimmten werten auf einem blatt und auf
einem 2. blatt eine etwas andere aufteilung der selben liste ähnlich
der eines t-kontos aus der buchhaltung ...
genauso wie in einem t-konto möchte ich bestimmte werte (aus blatt 1)
die mit einem bestimmten wert aus einer zelle auf dem 2. blatt
übereinstimmen entweder links der rechts hinpacken und das ganze ohne
leerzeilen, wie sie bei der verwendung mit sverweis entstehen würden.
schön alle untereinander ... und die die nicht übereinstimmen
außenvorlassen.
mit einer VBA-Schleife hätte ich das ganze ja in etwa 10 minuten +
plus start des VBA-Editors erledigt, das will ich aber nicht ...
Ich verwende Office XP ...
Ich hoffe jetzt wars besser ...
Grüße
Laes
Thomas Ramel
Der Schleier lichtet sich etwas...
Lars Witter schrieb:
>
> ich habe eine Liste mit bestimmten werten auf einem blatt und auf
> einem 2. blatt eine etwas andere aufteilung der selben liste ähnlich
> der eines t-kontos aus der buchhaltung ...
Was ist ein T-Konto? Nicht jeder, der mitliest, ist mit diesem Begriff
vertraut.
> genauso wie in einem t-konto möchte ich bestimmte werte (aus blatt 1)
> die mit einem bestimmten wert aus einer zelle auf dem 2. blatt
> übereinstimmen entweder links der rechts hinpacken und das ganze ohne
> leerzeilen, wie sie bei der verwendung mit sverweis entstehen würden.
> schön alle untereinander ... und die die nicht übereinstimmen
> außenvorlassen.
>
> Ich hoffe jetzt wars besser ...
Kannst Du eine (abgespeckte) Demo-Mappe auf einen Webspace laden und den
Link hier posten?
Ohne die konkreten Angaben der Zellen usw. wird jede Hilfestellung ein
Schuss ins Blaue, der dir dann nicht wirklich weiterhilft.
--
Mit freundlichen Grüssen
Thomas Ramel
- MVP für Microsoft-Excel -
Peter Wolber
> Was ist ein T-Konto? Nicht jeder, der mitliest, ist mit diesem Begriff
> vertraut.
Der Begriff stammt aus der Buchhaltung. In den Vor-PC-Zeiten wurden die
Bewegungen in der Buchhaltung auf Papier niedergeschrieben und zur
Veranschaulichung in T-Kontenform dargestellt. Das sieht an einem
Beispiel mit einer Gutschrift auf der Bank etwa so aus:
Soll Bank Haben
---------------------------------------
Einnahme 116,00 EUR |
|
|
Soll Erlöse-netto Haben
---------------------------------------
| 100,00 EUR Einnahme
|
|
Soll Umsatzsteuer Haben
---------------------------------------
| 16,00 EUR Einnahme
|
|
HTH
Gruss
Peter
Thomas Ramel
Peter Wolber schrieb:
>
>>Was ist ein T-Konto? Nicht jeder, der mitliest, ist mit diesem Begriff
>>vertraut.
>
> Der Begriff stammt aus der Buchhaltung. In den Vor-PC-Zeiten wurden die
> Bewegungen in der Buchhaltung auf Papier niedergeschrieben und zur
> Veranschaulichung in T-Kontenform dargestellt. Das sieht an einem
> Beispiel mit einer Gutschrift auf der Bank etwa so aus:
Danke für die 'Aufklärung.
Doppelte Buchhaltung also, soweit klar.
Damit ist dann auch schon was über die Optik des Wunsches von Lars bekannt.
Wir kommen der 'Sache' näher....
Lars Witter
habe mal eine Beispielmappe gemacht ... mit VBA wäre mein Problem
ruckzuck erledigt, aber das will ich nicht ... bei Herbers Excel-Forum
habe ich ja ne fette Formel gefunden, aber die ist so fett, das sie
kaum noch einer Lesen geschweige denn Erklären kann.
Hier die URL: http://www.mabi.de/~lw/Excel/Mappe2.xls
Grüße
Lars
Thomas Ramel
Lars Witter schrieb:
>
> habe mal eine Beispielmappe gemacht ... mit VBA wäre mein Problem
> ruckzuck erledigt, aber das will ich nicht ... bei Herbers Excel-Forum
> habe ich ja ne fette Formel gefunden, aber die ist so fett, das sie
> kaum noch einer Lesen geschweige denn Erklären kann.
Vielleicht sind meine Formeln genauso fett?
Wenn Du die Buch-Nr. an die erste Spalte verschiebst, kannst Du
anschliessend mit SVERWEIS() zugreifen.
Mit Matrixformeln kannst Du die entsprechenden Buch-Nr. in die
Konten-Übersichten bringne.
Der Datenbereich ist auch mit ein paar Bereichnamen versehen, damit die
Übersicht gewahrt bleibt.
Hier der Link zur aufbereiteten Mappe:
http://users.quick-line.ch/ramel/Demo-Daten/Konto-Bewegungen.ZIP
PS: Kann es sein, dass die erstellte Übersicht nicht ganz korrekt war?
Lars Witter
wrote:
>Grüezi Lars
>
>Lars Witter schrieb:
>>
>> habe mal eine Beispielmappe gemacht ... mit VBA wäre mein Problem
>> ruckzuck erledigt, aber das will ich nicht ... bei Herbers Excel-Forum
>> habe ich ja ne fette Formel gefunden, aber die ist so fett, das sie
>> kaum noch einer Lesen geschweige denn Erklären kann.
>
>Vielleicht sind meine Formeln genauso fett?
zum Glück nicht, die ist wesentlich schlanker, als die, die ich
gefunden hatte.
>
>Wenn Du die Buch-Nr. an die erste Spalte verschiebst, kannst Du
>anschliessend mit SVERWEIS() zugreifen.
>Mit Matrixformeln kannst Du die entsprechenden Buch-Nr. in die
>Konten-Übersichten bringne.
Wo wir beim Thema wären, abgesehen von den Erklärungen der OL...
könntest du mir die einzelnen schritte der Formel mal mit deutschen
worten nachvollziehen - ich raffe nicht ganz den Einsatz von kkleinste
wenn, istfehler und zeile kapiere ich ja grad noch aber KKLEINSTE will
da gar nicht so recht rein ;-))
=WENN(ISTFEHLER(KKLEINSTE(WENN(An=$A$1;Buch;"");ZEILE($A1)));"";KKLEINSTE(WENN(An=$A$1;Buch;"");ZEILE($A1)))
>Der Datenbereich ist auch mit ein paar Bereichnamen versehen, damit die
>Übersicht gewahrt bleibt.
>
>Hier der Link zur aufbereiteten Mappe:
>
>http://users.quick-line.ch/ramel/Demo-Daten/Konto-Bewegungen.ZIP
>
>
>PS: Kann es sein, dass die erstellte Übersicht nicht ganz korrekt war?
wie meinst du das?
Grüße & vielen vielen dank
Lars
Thomas Ramel
Lars Witter schrieb:
>
> Wo wir beim Thema wären, abgesehen von den Erklärungen der OL...
> könntest du mir die einzelnen schritte der Formel mal mit deutschen
> worten nachvollziehen - ich raffe nicht ganz den Einsatz von kkleinste
> wenn, istfehler und zeile kapiere ich ja grad noch aber KKLEINSTE will
> da gar nicht so recht rein ;-))
>
> =WENN(ISTFEHLER(KKLEINSTE(WENN(An=$A$1;Buch;"");ZEILE($A1)));"";KKLEINSTE(WENN(An=$A$1;Buch;"");ZEILE($A1)))
KKLEINSTE lieferT den k-kleinsten Wert einer Matrix, wobei k
unterschiedlich sein kann.
=KKLEINNTE(DeinBereich;1) liefert die kleinste Zahl (analog MIN())
=KKLEINSTE(DeinBereich;2) liefert die zweitkleinste Zahl
=KKLEINSTE(DeinBereich;3) liefert die drittkleinste Zahl
usw.
=KKLEINSTE(DeinBereich;ZEILE($A1)) liefert die kleinste Zahl,
da ZEILE($A1) = 1 ist. Beim runterkopieren wird daraus $A2, $A3 usw.
Also in jeder Zeile die nächstgrössere Zahl aus dem Bereich.
WENN(An=$A$1;Buch;"") liefert eine Matrix (deswegen abschliessen mit
SHIFT+STRG+RETURN) aus allen Werten des Bereiches 'Buch', wenn die Werte
aus Bereich 'An' der Zahl in A1 entspricht. Dies ist der Bereich oder die
Matrix, die an KKLEINSTE übergeben wird.
Das Ganze dann noch in die WENN()-Funktion verpackt, damit die Fehlerwerte
ausgelassen werden.
Ich hoffe, Du kommst nun etwas besser klar.
>>PS: Kann es sein, dass die erstellte Übersicht nicht ganz korrekt war?
>
> wie meinst du das?
Die Einträge in den Muster-Tabellen entsprechen nicht ganz den Vorgaben auf
der Übersicht. Im Konto '2000' sind z.B. zu viele Einträge vorhanden. Daher
die Abweichung meiner Formellösung zu den Musterkonten.
Lars Witter
wrote:
Heute will der Groschen einfach nicht fallen ...
>=KKLEINSTE(WENN(An=$A$1;Buch;"");ZEILE($A1))
Ich verstehe nur das System der Zeile($A$1) nicht, bzw. warum das
immer erhöht werden muß ... was bei Zelle rauskommt ist klar ... aber
mehr auch nicht.
Ich verstehe nicht so recht was genau das mit kkleinste zu tun hat,
das will mir nicht in den Kopf ... bin ich denn nur echt so blöd?
>=KKLEINSTE(DeinBereich;ZEILE($A1)) liefert die kleinste Zahl,
>da ZEILE($A1) = 1 ist. Beim runterkopieren wird daraus $A2, $A3 usw.
>Also in jeder Zeile die nächstgrössere Zahl aus dem Bereich.
In "DeinBereich" steht dann in meinem Fall alles was z.b. mit 2000 zu
tun hat ?!
Danke bishier her tausendmal!!!!!
Grüße
Lars
Thomas Ramel
Lars Witter schrieb:
>
> Heute will der Groschen einfach nicht fallen ...
>
>>=KKLEINSTE(WENN(An=$A$1;Buch;"");ZEILE($A1))
>
> Ich verstehe nur das System der Zeile($A$1) nicht, bzw. warum das
> immer erhöht werden muß ... was bei Zelle rauskommt ist klar ... aber
> mehr auch nicht.
Die ZEILE()-Funktion liefert jeweils die Zeilennummer der Bezugszelle.
ZEILE() allein liefert die Nummer der Zeile in der die Formel selbst steht.
ZEILE($A1) liefert die Zeilennummer von A1, also 1
ZEILE($A2) liefert die Zeilennummer von A1, also 2
ZEILE($A3) liefert die Zeilennummer von A1, also 3
usw.
In dieser Art ist die Formel kopierbar und passt sich jeweils an; mit jeder
Zeile mehr, ehöht sich auch der Wert um 1.
> Ich verstehe nicht so recht was genau das mit kkleinste zu tun hat,
> das will mir nicht in den Kopf ... bin ich denn nur echt so blöd?
Dies nun in angewandet in KKLEINSTE(DeinBereich;ZEILE($A1)) liefert in der
*ersten* Zeile in der die Formel steht, die *kleinste* Zahl aus dem Bereich
'DeinBereich'; in unserem Fall eben aus der Matrix mit den Buchungsnummern,
die das entsprechende Konto betreffen.
Kopierst/Ziehst Du nun die Formel nach unten, passt sich der Bezug '$A1'
laufend an. In der *zweiten* Zeile wird die *zweitkleinste* Zahl
zurückgegeben, in der *dritten* Zeile dann die *drittkleinste* Zahl usw.
Anstelle von ZEILE($A1) könntest Du auch einfach '1' schreiben
Anstelle von ZEILE($A2) könntest Du auch einfach '2' schreiben
Anstelle von ZEILE($A3) könntest Du auch einfach '3' schreiben
usw.
Die Verwendeung von ZEILE() sorgt halt dafür, dass sich der Wert beim
Kopieren/Ziehen jeweils *automatisch* um 1 erhhöht. Es ist gewissermassen
eine Komfortsache, ;-)
>>=KKLEINSTE(DeinBereich;ZEILE($A1)) liefert die kleinste Zahl,
>>da ZEILE($A1) = 1 ist. Beim runterkopieren wird daraus $A2, $A3 usw.
>>Also in jeder Zeile die nächstgrössere Zahl aus dem Bereich.
>
> In "DeinBereich" steht dann in meinem Fall alles was z.b. mit 2000 zu
> tun hat ?!
Das ist korrekt; es stehen in unserem Beispiel alle Buchungsnummern drin,
die sich auf das jeweilige Konto beziehen.
Somit werden die Buchungsnummern, die sich auf das Konto beziehen
untereinander aufgelistet.
Der ganze Rest der Formel dient 'bloss' der Kosmetik; wenn also keine
Buchungen mehr vorhanden sind, soll eine leere Zelle angezeigt werden, die
sich aber füllt, sobald eine neue Buchung für das betreffende Konto erfasst
wird.
Daher ist es notwendig, die Bereichsnamen auf genügend grosse Bereiche zu
vergeben. In der Beispielmappe beziehen sie sich nur auf den vorahndenen
Daenbereich. Erweitere sie entsprechend, und Du hast eine 'automatische'
Verbuchung deiner Einträge in den Konten erreicht.
> Danke bishier her tausendmal!!!!!
Bitte schön, ist gern geschehen.
Danke auch für dein Feedback jeweils.
Lars Witter
vielen Dank ... ich werde mich bei Gelegenheit wieder vertrauensvoll
an dich wenden ;-)
Hat mir echt super geholfen - danke nochmal ...
Grüße
Lars
Thomas Ramel
Lars Witter schrieb:
>
> vielen Dank ... ich werde mich bei Gelegenheit wieder vertrauensvoll
> an dich wenden ;-)
Oh, zuviel der Ehre! :-)
> Hat mir echt super geholfen - danke nochmal ...
Bitte schön, ist gern geschehen.
--
Lars Witter
hast du ne Idee bezügl. meiner Frage zum dynamischen Hyperlink?
Wäre ein Schmankerl in dem Rest meiner Arbeitsmappe ;-)
Grüße & Danke
Lars
Lars Witter
Jochen Belitz hat mir den entscheidenden Tip gegeben - Danke.
=+HYPERLINK($F$157;"Neue Buchung")
=WECHSELN(LINKS(ZELLE("Dateiname");FINDEN("]";ZELLE("Dateiname"))-1);"[";"")
=F156 & "#Journal!"&D157
Ich habe es etwas abgeändert, so dürfte es auch mit jedem x-beliebigen
Namen funktionieren.
Grüße & Danke
Lars