Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1576to1580
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Verschiedene Informationen in einer Zelle suchen

Verschiedene Informationen in einer Zelle suchen
30.08.2017 07:59:40
Max
Guten Morgen zusammen,
mit diesem Tool https://www.herber.de/bbs/user/115872.xlsx sollen später in umfangreicherer Form Speditionen und deren Preise verglichen werden können. Da jede Spedition ihre PLZ-Gebiete verschieden verteilt, habe ich die Gebiete in eigenen Zellen gebündelt (mit Komma getrennt). Mit Eingabe des Gewichts (in kg) und den ersten beiden Stellen der Postleitzahl soll nun der Preis ermittelt werden.
Ich habe wirklich keine Ahnung wie ich B3:K3 durchsuchen kann... ich könnte die gesamte Tabelle auch anders darstellen wenn das sinnvoll wäre. Die Preise und Gebiete werden sowieso von Hand gepflegt...
Ich würde mich über Anregungen freuen.
Viele Grüße, Max

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Verschiedene Informationen in einer Zelle suchen
30.08.2017 08:50:22
Max
Hallo yummi,
Danke für deine Mühe. Damit komme ich dem Ziel schon mal ein Stück näher. Vielleicht kann ich ja "merke" und "rng.Address" in zwei Variablen speichern und mir somit den Preis direkt ausgeben lassen (anstelle einer MsgBox).
Mein VBA ist zwar schon ein bisschen eingerostet, aber du hast mir schon mal sehr weitergeholfen!
Viele Grüße,
Max
AW: Verschiedene Informationen in einer Zelle suchen
30.08.2017 09:03:54
yummi
Hallo Max,
ich bin bei VBA gut davon ausgegangen, dass Dir die Anregung reicht.
Komplett soltte das das gewünschte liefern, wen ndu mehr nicht machen willst:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngPLZ As Range
Dim rngGew As Range
Dim i As Long
Dim merke As Long
If Target.Column = 2 Then
If Target.Row = 12 Or Target.Row = 13 Then
Set rngPLZ = FindeWert(ActiveSheet, "B3:K3", ActiveSheet.Cells(12, 2).Value)
If Not rngPLZ Is Nothing Then
merke = 4
For i = 4 To 6  ' hier bis letzte Zeile
If ActiveSheet.Cells(i, 1).Value > ActiveSheet.Cells(13, 2).Value Then
Exit For
Else
merke = i
End If
Next i
ActiveSheet.Cells(16, 2).Value = ActiveSheet.Cells(merke, rngPLZ.Column).Value
End If
End If
End If
End Sub
Gruß
yummi
Anzeige
AW: Verschiedene Informationen in einer Zelle suchen
30.08.2017 09:09:04
Max
Hallo yummi,
vielen Dank, dass du den Code noch geschrieben hast. Mein VBA war mal ganz gut, ich verstehe deinen Code ja auch komplett - ich brauche nur etwas länger. Vielleicht hast du Recht und ich sollte das VBA-Level nochmal anpassen... :P
Danke nochmal!
AW: Verschiedene Informationen in einer Zelle suchen
30.08.2017 08:52:32
UweD
Hallo
Was ist mit Gewichten größer 3000 kg ?

Spedition
 ABCDEFGHIJK
1 Abrechnungszone
2 Zone 1Zone 2Zone 3Zone 4Zone 5Zone 6Zone 7Zone 8Zone 9Zone 10
3bis kg53, 56, 6535, 50, 51, 55, 57, 60, 6140, 41, 42, 47, 52, 54, 58, 63, 6434, 36, 44, 45, 46, 59, 66, 67, 68, 69, 74, 75, 76, 9732, 33, 37, 48, 49, 70, 71, 77, 91, 98, 9926, 27, 28, 30, 31, 38, 72, 73, 78, 79, 89, 90, 92, 95, 9604, 06, 07, 08, 09, 29, 39, 85, 86, 87, 88, 9301, 20, 21, 22, 80, 81, 82, 84, 9402, 03, 10, 12, 13, 14, 15, 19, 23, 25, 8316, 17, 18, 24
41000234567891011
52000567891011121314
6300010121416182022242628
7           
8           
9           
10           
11Eingabe:          
12PLZ:35         
13Gewicht:1400         
14           
15Ausgabe          
16Preis:3         

 verbundene Zellen 
B1:K1

verwendete Formeln
Zelle Formel Bereich N/A
B16=INDEX(B4:K6;VERGLEICH(B13;A4:A6;1);VERGLEICH("*"&B12&"*";B3:K3;0))  
http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://Hajo-Excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 21.10 einschl. 64 Bit


LG UweD
Anzeige
AW: Verschiedene Informationen in einer Zelle suchen
30.08.2017 09:00:12
Max
Guten Morgen Uwe,
dass hast du sehr schön gelöst, klasse! Ich war eigentlich auch schon bei VBA angekommen, weil die Hoffnung auf eine solch einfache Lösung bereits aufgegeben hatte...
"Was ist mit Gewichten größer 3000 kg ?"
bei dieser Tabelle handelt es sich um eine vereinfachte Version. Später werden Gewichte bis 24t aufgeführt. Mehr als 24t nehmen unsere LKW's dann auch nicht mit.
Deine Formel gibt noch keinen Wert zurück, wenn das Gewicht kleiner ist als 1000kg (siehe 'kg bis') aber das kann man (ich) bestimmt recht schnell lösen.
Danke für deine Hilfe! :)
Koordinatenergebnis
30.08.2017 09:01:59
WF
Hi,
in B16 folgende Arrayformel:
{=INDEX(B4:K6;MIN(WENN(B13<=A4:A6;{1;2;3}));VERGLEICH(1;ISTZAHL(FINDEN(B12;B3:K3))*1;0))}
WF
Anzeige
AW: Koordinatenergebnis
30.08.2017 09:27:55
Max
Hi WF,
Danke, dass du dich mit dem Thema auseinander gesetzt hast! Leider funktioniert deine Formel nicht, aber ich habe auch eine saubere Lösung von UweD bekommen:
=INDEX(B4:K6;VERGLEICH(B13;A4:A6;1);VERGLEICH("*"&B12&"*";B3:K3;0))
Viele Grüße, Max
das ist eine Arrayformel !
30.08.2017 09:37:41
WF
Eingabe Arrayformel:
Du kopierst Dir die Formel.
Dann gehst Du in die Bearbeitungszeile, löschst die {geschweiften Klammern} am Anfang und Ende und schließt ab mit GLEICHZEITIG: Strg Shift Enter (statt Enter allein). Dadurch werden diese Klammern erzeugt.
AW: Ups
30.08.2017 10:03:59
Max
Tut mir Leid...
Für meine Beispieldatei funktioniert deine Formel sehr gut.
Durch

(WENN(B13
ist die Formel jedoch leider sehr eingeschränkt, da in späteren Fassungen die Zeilenanzahl durchaus nicht nur "A4:A6", sondern auch mal "A4:A100" betragen kann. Die manuelle Eingabe von

{1;2;3}
ist daher ein wenig umständlich :/
Auch habe ich gerade gemerkt, dass UweD's Formel mich auch nicht zum Ziel bringt. Durch den Vergleichstyp "1" spruckt diese die falschen Ergebnisse aus.
Ich werde also nochmal an deinem Code, oder an dem vorgeschlagenen VBA Code basteln.
Viele Grüße, Max
Anzeige
statt {1;2;3} nimmst Du dann ZEILE(X1:X3)
30.08.2017 10:14:49
WF
Hi,
da Kannst Du dann auch ZEILE(X1:X100) nehmen.
also:
{=INDEX(B4:K6;MIN(WENN(B13<=A4:A6;ZEILE(X1:X3)));VERGLEICH(1;ISTZAHL(FINDEN(B12;B3:K3))*1;0))}
bzw. (der Vergleichs-Teil von Uwe war einfacher):
{=INDEX(B4:K6;MIN(WENN(B13<=A4:A6;ZEILE(X1:X3)));VERGLEICH("*"&B12&"*";B3:K3;0))}
WF
AW: Perfekt!
30.08.2017 10:27:53
Max
muy bien!
Hier einmal das Ergebnis für dich: https://www.herber.de/bbs/user/115880.xlsx
Vielen Dank für deine Hilfe - die Lösung ist effizient und verständlich :)
Viele Grüße, Max
AW: es geht trotzdem ohne {}, allerdings ...
30.08.2017 10:23:05
...
Hallo Max,
... müsstest Du zuvor noch klären, was für Gewichtseingaben in B13 größer 3000 kg passieren soll.
Die bisher aufgezeigten Formeln ergeben dann nämlich einen falschen Ergebniswert.
Mein nachfolgender Formelvorschlag wirft dagegen dann erst einmal eine Fehlermeldung aus, die ich entsprechender Deiner Information noch abfangen kann.
=INDEX(4:6;AGGREGAT(15;6;{1;2;3}/(B13 
Gruß Werner
.. , - ...
Anzeige
AW: es geht trotzdem ohne {}, allerdings ...
30.08.2017 10:31:21
Max
Hi Werner aka. ... neopa C
"Was ist mit Gewichten größer 3000 kg ?"
bei dieser Tabelle handelt es sich um eine vereinfachte Version. Später werden Gewichte bis 24t aufgeführt. Mehr als 24t nehmen unsere LKW's dann auch nicht mit. Ein Fehlerhafter Wert ist also akzeptierbar bzw. könnte man wirklich mit ISTNV(Wert) o.ä. dann beseitigen.
Wir haben oben eine Recht schicke Lösung gefunden. Trotzdem Danke für deine Beteiligung, AGGREGAT kenne ich noch nicht :)
Viele Grüße, Max
AW: kann Dich nur nochmal darauf hinweisen ...
30.08.2017 10:52:27
...
Hallo Max,
... dass die Ergebnisse der Matrixformeln für die Du Dich jetzt offensichtlich entschieden hast, bei einer Eingabe in B12 von größer 3000 kg, keine Fehlermeldung ergeben, sie würden in der bisherigen Formelfassung jedoch den niedrigsten Preis ermitteln, den Du dafür sicherlich nicht vergeben willst. Mit meinem Formelvorschlag würdest Du mit der dann erscheinenden Fehlermeldung bei einer Falscheingabe auf der sicheren Seite liegen.
Wenn Du Dich aber für eine WF- Matrixformeln entschieden hast, solltest Du verhindern, dass in B12 eine derartig große Eingabe vorgenommen werden kann. Dies könntest Du z.B. mit entsprechender Datengültigkeit vornehmen oder die Matrixformel entsprechend erweitern.
Unabhängig davon sollte jeder Formel noch eine Eingabeprüfung auf Vollständigkeit vorangestellt werden.
Gruß Werner
.. , - ...
Anzeige

317 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige