Softpicks.Net Deutsch

Am Sat, 8 Sep 2007 14:20:12 +0200 schrieb Markus Franz:

> Hallo,
>
> ich habe ein Tabellenblatt, in welchem ich eine Spalte „Bearbeiternummer“
> und „Bearbeiter“ habe. Sobald ich in Spalte „Bearbeiternummer“ als Ziffer
> <<1>> eintrage, erscheint unter „Bearbeiter“ dann z.B. <. Die
> Tabelle, in welcher festgelegt ist, dass Herr Mustermann die Nummer 1 hat,
> liegt auf einem anderen Tabellenblatt (dort ist unter Spalte B ab Zeile 3
> die Nummer und Spalte C ab Zeile 3 der entsprechende Name. Die
> Namenscodetabelle umfasst ca. 100 Eintragungen.
>
> An sich ist das ja mittels SVERWEIS-Funktion ohne weiteres lösbar.
>
> Nun möchte ich in meiner Tabelle gern folgendes Problem lösen:
>
> Manchmal kommt es vor, dass mehr als 1 Bearbeiter eingetragen werden soll.
> Sofern möglich, möchte ich z.B. bei „Bearbeiternummer“ als Ziffer <<1,2>>
> oder auch mit Leerzeichen (<<1, 2>>) eintragen und bei Bearbeiter soll dann
> < erscheinen. Die Funktion müsste so sein, dass auch
> mehrere Zahlen erfasst werden können (z.B. <<1,5,7,19>> bzw. <<1, 5, 7,
> 19>>). Damit dürfte die SVERWEIS-Funktion nicht mehr funktionieren. Die
> entsprechenden Daten soll er sich aus der Namenscodetabelle holen.
>
> Hat jemand eine Idee, wie man so was lösen kann?
snip-


Hallo Markus,

mit dem SVerweis kannst Du viele Möglichkeiten erschließen, wenn Du den
SVerweis kombinierst.

Ich gebe Dir hier ein Beispiel:

=WENN(UND($A12>0;$B12>0);WAHL($A12;SVERWEIS($B12;ArtStamm!$A$5:$B$50;2;0);SVERWEIS($B12;ArtStamm!$A$5:$G$50;7;0);SVERWEIS($B12;ArtStamm!$A$5:$B$50;2;0)&"
*VK*";SVERWEIS($B12;ArtStamm!$A$5:$G$50;7;0)&"*VK*");"")

Da ist der SVerweis mit Wenn Und Wahl kombiniert

in diesem Fall wird die Artikelnummer z.B. in der Spalte B in Zelle B12
eingegeben.

Für Wahl wird in der gleichen Zeile in Spalte A die Ziffer von 1 bis 4
eingegeben und dadurch wird dann der jeweils nächste SVerweis
angesprochen, der einen ganz anderen Text Liefert.

Zum Testen richte mal eine Tabelle mit Namen ArtStamm ein
und schreibe zumindest Text in die Spalten B und G
ab Zeile 5

Also hier eine Musterzeile

A5 = 1 B5 = Berta G5 = Gustav

Nunn bringe meine SVerweis-Formel in der Tabelle1 in die Zelle D12
und dann kannst Du die soweit runterziehen wie Du möchtest.

Sodann schreibe in der Tabelle1 Zelle B12 die Zahl 1
dann bekommst Du Berta als Text,

wenn Du aber in A12 eine 2 eingibst, dann liefert Dir der
gleiche SVerweis den Namen Gustav, weil Wahl die 2. Variante
von SVerweis anordnet.

Also teste mal,

Es gibt noch sehr viele Möglichkeiten der Kombination
mit SVerweis.

MfG
Robert



.

Am Sun, 9 Sep 2007 00:11:05 +0200 schrieb Robert Jakob:

> Hallo Markus,
>
> mit dem SVerweis kannst Du viele Möglichkeiten erschließen, wenn Du den
> SVerweis kombinierst.
>
> Ich gebe Dir hier ein Beispiel:
>
> =WENN(UND($A12>0;$B12>0);WAHL($A12;SVERWEIS($B12;ArtStamm!$A$5:$B$50;2;0);SVERWEIS($B12;ArtStamm!$A$5:$G$50;7;0);SVERWEIS($B12;ArtStamm!$A$5:$B$50;2;0)&"
> *VK*";SVERWEIS($B12;ArtStamm!$A$5:$G$50;7;0)&"*VK*");"")
>
snip--

Leider wurde die Formel nicht richtig übertragen, daher füge ich die Formel
hier nochmals ein.


=WENN(UND($A12>0;$B12>0);WAHL($A12;SVERWEIS($B12;ArtStamm!$A$5:$B$50;2;0);SVERWEIS($B12;ArtStamm!$A$5:$G$50;7;0);SVERWEIS($B12;ArtStamm!$A$5:$B$50;2;0)&"
*VK*";SVERWEIS($B12;ArtStamm!$A$5:$G$50;7;0)&"*VK*");"")

MfG
Robert
.

Am Sun, 9 Sep 2007 10:48:03 +0200 schrieb Robert Jakob:

>> =WENN(UND($A12>0;$B12>0);WAHL($A12;SVERWEIS($B12;ArtStamm!$A$5:$B$50;2;0);SVERWEIS($B12;ArtStamm!$A$5:$G$50;7;0);SVERWEIS($B12;ArtStamm!$A$5:$B$50;2;0)&"
>> *VK*";SVERWEIS($B12;ArtStamm!$A$5:$G$50;7;0)&"*VK*");"")

Nochmals Nachtrag weil die Formel nicht einzeilig übertragen wird.

Das Problem mit der Formel kann man über den Editor lösen,
man kopiere den Formeltext in den Editor und mache dann darus eine Zeile.

oder man verwende den folgenden VBA Code.

Sub SverweisKombinationMitWahl()

Range("D" & ActiveCell.Row).FormulaR1C1 = _
"=IF(AND(RC1>0,RC2>0),CHOOSE(RC1," _
& "VLOOKUP(RC2,ArtStamm!R5C1:R50C2,2,0)," _
& "VLOOKUP(RC2,ArtStamm!R5C1:R50C7,7,0)," _
& "VLOOKUP(RC2,ArtStamm!R5C1:R50C2,2,0)&"" *VK*""," _
& "VLOOKUP(RC2,ArtStamm!R5C1:R50C7,7,0)&""*VK*""),"""")"
End Sub

MfG
Robert
.

[Alexander Wolff]

Immer, wenn man mehrere (= mehr als 1) Einträge einer Zeile, gefunden durch
SVERWEIS(), wiedergeben möchte, ist man mit einer Hilfszelle "Satznummer",
ermittelt mit VERGLEICH(), und einem darauf zugreifenden INDEX() besser
bedient, als mit SVERWEIS.

Das gleiche gilt für die Wiedergabe mehrerer passender Einträge; die
Satznummer des vorherigen Treffers wird jetzt dafür verwendet, die Suche
erst nach diesem Treffer für den nächsten Treffer zu beginnen. Der
Suchbereich wird dafür mit INDIREKT() oder BEREICH.VERSCHIEBEN() angepaßt.

Also: VERGLEICH() und INDEX() statt SVERWEIS() in diesen Fällen!
--
Moin+Gruss Alexander - MVP für MS Excel - www.xxcl.de - mso2000sp3 --7-2


.

Am Sun, 9 Sep 2007 11:41:14 +0200 schrieb Alexander Wolff:

> Immer, wenn man mehrere (= mehr als 1) Einträge einer Zeile, gefunden durch
> SVERWEIS(), wiedergeben möchte, ist man mit einer Hilfszelle "Satznummer",
> ermittelt mit VERGLEICH(), und einem darauf zugreifenden INDEX() besser
> bedient, als mit SVERWEIS.
>
> Das gleiche gilt für die Wiedergabe mehrerer passender Einträge; die
> Satznummer des vorherigen Treffers wird jetzt dafür verwendet, die Suche
> erst nach diesem Treffer für den nächsten Treffer zu beginnen. Der
> Suchbereich wird dafür mit INDIREKT() oder BEREICH.VERSCHIEBEN() angepaßt.
>
> Also: VERGLEICH() und INDEX() statt SVERWEIS() in diesen Fällen!

Hallo Alexander,
kannst Du Deinen Tipp mal mit einem
einfachen Beispiel demonstrieren ?

MfG
Robert
.

[Alexander Wolff]

http://www.xxcl.de/files/MehrereEintraegeSVERWEISzeigen.zip

Achtung: Beide angesprochenen Kategorien der Verwendung von VERGLEICH/INDEX
sind hier verwendet:

1:1 Rechnungs-Nr:Rechnung (mit 4 Daten Name, Straße, Ort, Datum)
1:n Rechnungs-Nr:Rechnungspositionen (mit n*4 Daten Art-Nr, Bez., Anz.
Stückpr.)

Schon im ersteren Falle wäre SVERWEIS schlecht, da es 4mal suchen müßte,
während doch der Datensatz mit einem VERGLEICH doch schon feststeht und mit
INDEX gezogen werden kann.

Beim zweiten Fall geht es kaum anders.

Bitte im gelben Feld Testdaten 1, 2, 3 oder 4 eingeben.
--
Moin+Gruss Alexander - MVP für MS Excel - www.xxcl.de - mso2000sp3 --7-2


.

Am Sun, 9 Sep 2007 17:46:36 +0200 schrieb Alexander Wolff:

> http://www.xxcl.de/files/MehrereEintraegeSVERWEISzeigen.zip
>
> Achtung: Beide angesprochenen Kategorien der Verwendung von VERGLEICH/INDEX
> sind hier verwendet:
>
> 1:1 Rechnungs-Nr:Rechnung (mit 4 Daten Name, Straße, Ort, Datum)
> 1:n Rechnungs-Nr:Rechnungspositionen (mit n*4 Daten Art-Nr, Bez., Anz.
> Stückpr.)
>
> Schon im ersteren Falle wäre SVERWEIS schlecht, da es 4mal suchen müßte,
> während doch der Datensatz mit einem VERGLEICH doch schon feststeht und mit
> INDEX gezogen werden kann.
>
> Beim zweiten Fall geht es kaum anders.
>
> Bitte im gelben Feld Testdaten 1, 2, 3 oder 4 eingeben.

Hallo Alexander,

ich habe mir die Datei mal angesehen und muß bestätige, daß alles
erwartungsgemäß läuft.
Mit SVerweis hat das natürlich gar nichts zu tun und ob das wirklich für
die Faktura praxistauglich ist, da möchte ich noch bezweifeln.

Eine ähnliche Anwendung hatten wir ja kürzlich in der NG und es ist
sicherlich eine interessante Sache z.B. als Grundansatz für eine
Saldenliste aber das macht eine Pivottabelle auch sehr schnell.

So wie Du das aufgebaut hast kann man damit einem Kunden eine Rechnung für
verschiedene Lieferungen schreiben, soweit so gut.

Wie willst Du es bei diesem System regeln, daß ein Kunde keine Rechnung für
die gleiche Lieferung doppelt erhält ?

Die Daten markieren und aus der Tabelle verschieben ist damit doch wohl
nicht noch gleichzeitig möglich.

Weiterhin, die Daten die da zur Rechnung transferiert werden, die müssen ja
mal in die Tabelle gekommen sein.

In Deinem Muster ist mir das klar, wie Du das gemacht hast, Du hast einfach
die Werte von Hand dahin getippt. Bei nur 5 bis 10 Zeilen kann man da auch
nicht so viel falsch machen.

Bist Du sicher, dass Dein Tipp passend für die Frage des OP ist ?

Warten wir es ab was der sagt, oder ist das einer von den Leuten die
nur Fragen stellen und sich dann nicht mehr melden ?

MfG
Robert
.

[Alexander Wolff]

> Mit SVerweis hat das natürlich gar nichts zu tun und ob das wirklich für
> die Faktura praxistauglich ist, da möchte ich noch bezweifeln.

"Rechnung" ist einzig und allein ein passender Daten-Zusammenhang (1 Kopf
mit m1 Feldern, n Positionen mit m2 Feldern), mehr nicht. Beispielsweise
würde man real noch die Artikeldaten auslagern und mittels Art-Nr als
Schlüssel einbinden, so dass R-Positionen (als eine n:m-Relation) nur 3
Felder überhaupt aufwiese.

> Wie willst Du es bei diesem System regeln, daß ein Kunde keine Rechnung
> für
> die gleiche Lieferung doppelt erhält ?

Man könnte die Rechnungen mit Statuskennzeichen (in R-Daten) versehen und
ein erneutes Einlesen verhindern.

> In Deinem Muster ist mir das klar, wie Du das gemacht hast, Du hast
> einfach
> die Werte von Hand dahin getippt. Bei nur 5 bis 10 Zeilen kann man da auch
> nicht so viel falsch machen.

Irgendwo auf dieser Welt werden diese Daten IMMER händisch erzeugt, und sei
es durch Erfassen mittels Barcodescanner oder RFID. - Und wenn NUR ICH mit
der Applikation arbeite, brauche ich keine Prozesse und keine
Benutzerführung und keine Fehlerbehandlung und spare 99,5% der Entwicklung
... Nachrüstungen nicht ausgeschlossen. So halte ich es beispielsweise
mit Überweisungen, Lohnabrechnung und Außer-EU-Auslandszahlungsverkehr,
früher auch LStA und UStVA - alles meine persönlichen Werkzeuge, die mir vor
allem das Pinseln mit der Hand, nicht so sehr das Berechnen, abnehmen.
Einmal habe ich das sogar für Dritte so gemacht - für geringen Preis, aber
der Ankündigung, dass sie aufgrund von möglichen Fehlbedienungen wohl öfters
Nachbetreuung brauchen würden. Bislang hat es sich gelohnt! Statt 7.000 für
ein eingeführtes Produkt mussten sie nur 1.750 für ein massgeschneidertes
bezahlen ... es lief 6 Jahre und wird erst jetzt zum Jahreswechsel aufgrund
größer gewordener Strukturen ersetzt. Nachberechnet habe ich höchstens die
Hälfte nochmal - wie immer geht es dann oft auch um kleine Anpassungen.

> Bist Du sicher, dass Dein Tipp passend für die Frage des OP ist ?

Ja, weil die Frage mit den R-Positionen immer wieder gestellt wird. Das
Beispiel ist so überschaubar, dass OP zumindest verstehen sollte, wie man
passende Folgesätze gleich mit wiedergibt. Ich könnte natürlich jetzt noch
colorieren usw.

> Warten wir es ab was der sagt, oder ist das einer von den Leuten die
> nur Fragen stellen und sich dann nicht mehr melden ?

Sich nicht mehr zu melden, bedeutet auch, dass weniger geschrieben wird, um
es mal positiv auszudrücken. Andererseits hast Du natürlich recht, dass es
schön wäre, bei späterem Nachgoogeln sehen zu können, ob der Thread zur
Zufriedenheit abgeschlossen wurde.
--
Moin+Gruss Alexander - MVP für MS Excel - www.xxcl.de - mso2000sp3 --7-2


.

[Markus Franz]

Hallo Robert, Alexander und Klaus

> Warten wir es ab was der sagt, oder ist das einer von den Leuten die
> nur Fragen stellen und sich dann nicht mehr melden ?

vielen Dank für die Bemühungen.

Nein, ich bin eigentlich nicht so einer, der nur fragt und dann kein
Feedback gibt. Ich komme nur nicht immer gleich dazu.

Ich habe versucht anhand der Tabelle von Alexander das bei mir "einzubauen",
aber irgendwie klappt das nicht.

Auch bin ich leider an Roberts Beispiel gescheitert. Ich weiß nicht, was ich
falsch mache.

>Sodann schreibe in der Tabelle1 Zelle B12 die Zahl 1 dann bekommst Du Berta
>als Text, wenn Du aber in A12 eine 2 eingibst, dann liefert Dir der gleiche
>SVerweis den Namen Gustav, weil Wahl die 2. Variante von SVerweis anordnet.

Wenn ich das so mache, erscheint nichts.

Mein Wunsch war ja, dass ich "1,2" in die Zelle schreibe und er dann
"Mustermann, Müller" in die Nachbarzelle schreibt, aber irgendwie bekomme
ich das nicht so hin, trotz der Beispiele von Robert und Alexander.

Bei der Variante von Klaus hab ich das Problem, dass ich mit den
"Hilfszellen" längerfristig durcheinander kommen würde.

Ich werde nun wohl lieber noch 2 Spalten einfügen, wo die weiteren
Bearbeiter eingetragen werden können oder gibts doch noch eine Lösung?
(evtl. Makro) ;-)

Gruß

Markus



.

[Alexander Wolff]

Hier noch mal mit Erklärung: www.xxcl.de/0054.htm (alter Link ungültig)
--
Moin+Gruss Alexander - MVP für MS Excel - www.xxcl.de - mso2000sp3 --7-2


.

Hallo,

bei mir funktioniert Alexanders Link auf sein Beispiel nicht, deshalb
kann ich dazu nicht n=E4her Stellung beziehen.

Ich h=E4tte zwei Anregungen:

1=2E Die Eingabedaten nicht in einer Zelle mit "1, 2, 8, 12" oder
=E4hnlich darstellen, sondern hierf=FCr z. B. 7 Zellen nebeneinander zu
nehmen, wobei die 1., 3., 5., und 7. Zelle eine Bearbeiternummer
aufnimmt und die Zellen dazwischen f=FCr Eingaben gesperrt sind und mit
bedingter Formatierung ein "," zeigen, falls rechts noch ein
Bearbeiter kommt. Dann werden die SVERWEIS Formeln nicht komplexer.

2=2E Wenn die Eingabe aber unbedingt mit "1, 2, 8, 12" oder =E4hnlich
erfolgen muss, dann w=FCrde ich mit REGEXPRREPLACE die Nummern in
aufeinanderfolgende Zellen extrahieren und dann wieder mit einem
einfachen SVERWEIS arbeiten. Such daf=FCr in dieser Gruppe nach dem
entsprechenden Funktionsnamen.

Viele Gr=FC=DFe,
Bernd

.

[Alexander Wolff]

Erst jetzt sehe ich, dass ich den OP völlig falsch gelesen habe.

Ich wollte mehrere gleiche Einträge zu EINER einzigen Eingabe, falls
existent, auswerfen, jedoch nicht mehrere verschiedene aufgrund einer in
einer Zelle eingegebenen Aufzählung.

Die sauberste Methode ist es, direkt vor die Ausgabe der Daten die Schlüssel
(wie 1 oder 12) einzutragen und entweder SVERWEIS oder VERGLEICH/INDEX zu
verwenden.

Um doppelte Eingaben zu verhinden, könntest Du die Eingabespalte A mittels
Daten Gültigkeit Benutzerdefiniert =ZÄHLENWENN($A:$A;A2)<2 versehen.

Mittels VBA könntest Du darüberhinaus diese Ausgabe immer sortiert halten.
--
Moin+Gruss Alexander - MVP für MS Excel - www.xxcl.de - mso2000sp3 --7-2


.

Am Tue, 11 Sep 2007 20:42:58 +0200 schrieb Markus Franz:

> ... oder gibts doch noch eine Lösung?


Hallo Markus,

klick mit der rechten Maus unten links auf den Reiter des Tabellenblattes,
in dem die Ausgabe erfolgen soll und wähle "Code anzeigen". In das sich
öffnende Fester kopierst Du das Makro und kehrst mit Alt+F11 in die Tabelle
zurück.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Columns("A")) _
Is Nothing Then Exit Sub
Dim intI As Integer
Dim varNr As Variant
Dim strBearb As String
varNr = Split(Target.Value, ",")
For intI = 0 To UBound(varNr)
strBearb = strBearb & WorksheetFunction.VLookup _
(CInt(varNr(intI)), Sheets("Tabelle2") _
.Range("B3:C200"), 2, 0) & ", "
Next
strBearb = Left(strBearb, Len(strBearb) - 2)
Target.Offset(0, 1).Value = strBearb
End Sub


Falls die Namensliste in Tabelle2 länger als 200 (genau: 197) ist, musst Du
in der 5. Codezeile von unten entsprechend anpassen. Unterhalb der
Namensliste dürfen sich bis Zeile 200 in Spalte B keine Zahlen befinden,
sonst kann Vlookup (=Sverweis) ein falsches Ergebnis bringen.

--
Mit freundlichen Grüssen Eberhard
XP home XL 2000
Eberhard(punkt)W(punkt)Funke(at)t-online.de
.

[Markus Franz]

Hallo Eberhard,

das Makro macht genau das, wie ich es mir vorgestellt habe. Nun habe ich
leider das Problem, dass ich schon ein Private Sub Worksheet_Change(ByVal
Target As Range) habe und da weiß ich nicht weiter. Kopiere ich den Code vor
das End Sub macht er leider nix.
Das gleiche Problem habe ich auch bei dem anderen Thread (noch mal
"Einfärben der Zeile nach Eingabe in erster Spalte der Zeile"). Dort möchte
ich auch, dass ein weiteres/anderes Makro abläuft, aber das klappt irgendwie
nicht.

Wie kann man mehrere Makros bei einem Worksheet_Change einbinden?

--
Gruß

Markus
______________________________

Antworten bitte ausschließlich in der Newsgroup!



.

Am Wed, 12 Sep 2007 19:29:49 +0200 schrieb Markus Franz:

Hallo Markus,
>
> das Makro macht genau das, wie ich es mir vorgestellt habe. Nun habe ich
> leider das Problem, dass ich schon ein Private Sub Worksheet_Change(ByVal
> Target As Range) habe und da weiß ich nicht weiter. Kopiere ich den Code vor
> das End Sub macht er leider nix.

Vermutung: Mein Makro steigt gezielt aus
--> If Intersect(Target, Me.Columns("A"))Is Nothing Then Exit Sub
wenn Target nicht in Spalte A steht. Dein Target befindet sich in einer
anderen Spalte.
Wenn bei Deinem Makro Target in *derselben_Zeile* steht, in der auch die
Namen ausgegeben werden sollen, dann könnte man das evtl. einbauen.

Poste bitte mal Dein Makro und erkläre, was dabei passieren soll.

> Das gleiche Problem habe ich auch bei dem anderen Thread (noch mal
> "Einfärben der Zeile nach Eingabe in erster Spalte der Zeile"). Dort möchte
> ich auch, dass ein weiteres/anderes Makro abläuft, aber das klappt irgendwie
> nicht.

Ich habe den ursprünglichen Thread nicht mehr in meinem Newsreader und kann
mit dem nachgeschobenen Posting nicht viel anfangen. Ich nehme an, Dein
vormaliger Helfer wird sich dazu äussern.

>
> Wie kann man mehrere Makros bei einem Worksheet_Change einbinden?

Nicht ironisch gemeint: das kommt auf die Makros an.


--
Mit freundlichen Grüssen Eberhard
XP home XL 2000
Eberhard(punkt)W(punkt)Funke(at)t-online.de
.