Supportnet / Forum / Tabellenkalkulation
WENN-Funktion mit 36 Abfragen ??
Frage
Hallo,
ich habe eine Tabelle in der ich Preise abhängig von Entfernung und Gewicht berechnen möchte. Ich glaube aber so viele verschachtelte WENN-Abfragen sind nicht möglich. Aber wie kann ich das Problem lösen?
Zum Verständnis - die Tabelle sieht wie folgt aus:
In A12 bis A27 stehen die Entfernungen
z. B. A12 = < 40 Km
A13 = < 75 Km
In B11, C11, D11, E11, .... steht das Gewicht
z. B. B11 = < 50
C11 = < 100
Bei Km < 40 und Gewicht < 50 muss der Preis von B12 ausgegeben werden, bei Entfernung < 75 und Gewicht < 50 muss B13 ausgegeben werden, usw.
Verständlich ???
Das muss doch möglich sein !!
Antwort 1 von sutadur
Ich denke nicht, dass das vom Programm her ein Problem darstellt. Hast Du es denn schon ausprobiert? Und wenn ja, was genau hat dann nicht funktioniert bzw. wurde als Fehlermeldung ausgegeben?
Antwort 2 von Petra65
Laut Excel-Hilfe können nur 7 WENN-Funktionen verschachtelt werden !!!
Ist aber für meine Zwecke nicht ausreichend.
Ist aber für meine Zwecke nicht ausreichend.
Antwort 3 von sutadur
Hm ... ich hab mal eben gegoogelt. Heraus kam u.a. das hier: WENN mit mehr als 8 Bedingungen. Vielleicht hilft das weiter.
Antwort 4 von Locke
Du kannst ja nur ein Kriterium verschachteln und damit den Spaltenindex von Sverweis errechnen.
Also angenommen in A1 steht die Entfernung und in B1 das Gewicht:
=SVERWEIS(A1;A12:H20;WENN(B1<50;2;WENN(B1<100;3;WENN(B1<150;4;WENN(B1<200;5;WENN(B1<250;6;WENN(B1<300;7;WENN(B1>300;8;)))))));WAHR)
Der Bereich A12-H20 muss natürlich angepasst werden und das Gewicht im 50KG-Abstand evtl. auch.
Gruß
Locke
Also angenommen in A1 steht die Entfernung und in B1 das Gewicht:
=SVERWEIS(A1;A12:H20;WENN(B1<50;2;WENN(B1<100;3;WENN(B1<150;4;WENN(B1<200;5;WENN(B1<250;6;WENN(B1<300;7;WENN(B1>300;8;)))))));WAHR)
Der Bereich A12-H20 muss natürlich angepasst werden und das Gewicht im 50KG-Abstand evtl. auch.
Gruß
Locke
Antwort 5 von Locke
Natürlich kannst Du die Gewichtsunterschiede auch variabel gestalten :
=SVERWEIS(A1;A12:H20;WENN(B1<B11;2;WENN(B1<C11;3;WENN(B1<D11;4;WENN(B1<E11;5;WENN(B1<F11;6;WENN(B1<G11;7;WENN(B1>H11;8;)))))));WAHR)
Gruß
Locke
=SVERWEIS(A1;A12:H20;WENN(B1<B11;2;WENN(B1<C11;3;WENN(B1<D11;4;WENN(B1<E11;5;WENN(B1<F11;6;WENN(B1<G11;7;WENN(B1>H11;8;)))))));WAHR)
Gruß
Locke
Antwort 6 von Locke
Und wenn Du unterhalb der Gewichte in der Zeile 11 eine Hilfszeile einfügst und da den entsprechenden Spaltenindex, beginnend mit 2 einträgst (B12=2, C12=3. D12=4, usw), kannst Du die Formel beliebig erweitern:
=SVERWEIS(A1;A13:H20;WVERWEIS(B1;B11:H12;2;WAHR);WAHR)
Die Entfernungen beginnen dann halt erst ab A13.
Die Zeile 12 kannst Du dann ja mit Zeilenhöhe 0 ausblenden.
Gruß
Locke
=SVERWEIS(A1;A13:H20;WVERWEIS(B1;B11:H12;2;WAHR);WAHR)
Die Entfernungen beginnen dann halt erst ab A13.
Die Zeile 12 kannst Du dann ja mit Zeilenhöhe 0 ausblenden.
Gruß
Locke
Antwort 7 von nighty
hi all :)
oder eine benutzedefinierte function :))
gruss nighty
leicht zu erweitern geht
Function WennEndlos(Zellen As Range) As Double
Application.Volatile
Dim Zelle As Range
Dim vergleich(2, 2) As Long
vergleich(0, 0) = 1
vergleich(0, 1) = 5
vergleich(0, 2) = 100
vergleich(1, 0) = 5
vergleich(1, 1) = 10
vergleich(1, 2) = 200
vergleich(2, 0) = 10
vergleich(2, 1) = 20
vergleich(2, 2) = 300
For zaehler = 0 To 2
If Cells(Zellen.Row, 1) > vergleich(zaehler, 0) And Cells(Zellen.Row, 1) < vergleich(zaehler, 1) Then
WennEndlos = vergleich(zaehler, 2)
End If
Next zaehler
End Function
oder eine benutzedefinierte function :))
gruss nighty
leicht zu erweitern geht
Function WennEndlos(Zellen As Range) As Double
Application.Volatile
Dim Zelle As Range
Dim vergleich(2, 2) As Long
vergleich(0, 0) = 1
vergleich(0, 1) = 5
vergleich(0, 2) = 100
vergleich(1, 0) = 5
vergleich(1, 1) = 10
vergleich(1, 2) = 200
vergleich(2, 0) = 10
vergleich(2, 1) = 20
vergleich(2, 2) = 300
For zaehler = 0 To 2
If Cells(Zellen.Row, 1) > vergleich(zaehler, 0) And Cells(Zellen.Row, 1) < vergleich(zaehler, 1) Then
WennEndlos = vergleich(zaehler, 2)
End If
Next zaehler
End Function
Antwort 8 von Petra65
Man oh Man - da muss ich aber erstmal in Ruhe drüber schauen...
Wie und wo baut man denn so eine Funktion ein????
Vielen Dank erstmal für die Anregungen!
Wie und wo baut man denn so eine Funktion ein????
Vielen Dank erstmal für die Anregungen!
Antwort 9 von nighty
hi all :)
einzufuegen in einem allgemeinen modul
alt f11/projektexplorer/einfuegen/modul
nachdem ist die function unter function/einfuegen verfuegbar
=WennEndlos(A1)
Function WennEndlos(Zellen As Range) As Double
Application.Volatile
Dim Zelle As Range
rem bei jedem weiteren vergleich eine dimension erhöhen
rem nächste wäre 3,2
Dim vergleich(2, 2) As Long
rem folgende zuweisung
rem 0,0 maximum
rem 0,1 minimum
rem 0,2 wertzuweisung
vergleich(0, 0) = 1
vergleich(0, 1) = 5
vergleich(0, 2) = 100
vergleich(1, 0) = 5
vergleich(1, 1) = 10
vergleich(1, 2) = 200
vergleich(2, 0) = 10
vergleich(2, 1) = 20
vergleich(2, 2) = 300
rem eine weitere erhöhung würde so aussehen
rem vergleich(3, 0) = 20
rem vergleich(3, 1) = 30
rem vergleich(3, 2) = 400
rem nicht zu vergessen die erhöhung bei der oberen dimensionierung
rem hier werden die anzahl der vergleiche angegeben bzw. der dimensionen
For zaehler = 0 To 2
If Cells(Zellen.Row, 1) > vergleich(zaehler, 0) And Cells(Zellen.Row, 1) < vergleich(zaehler, 1) Then
WennEndlos = vergleich(zaehler, 2)
End If
Next zaehler
End Function
einzufuegen in einem allgemeinen modul
alt f11/projektexplorer/einfuegen/modul
nachdem ist die function unter function/einfuegen verfuegbar
=WennEndlos(A1)
Function WennEndlos(Zellen As Range) As Double
Application.Volatile
Dim Zelle As Range
rem bei jedem weiteren vergleich eine dimension erhöhen
rem nächste wäre 3,2
Dim vergleich(2, 2) As Long
rem folgende zuweisung
rem 0,0 maximum
rem 0,1 minimum
rem 0,2 wertzuweisung
vergleich(0, 0) = 1
vergleich(0, 1) = 5
vergleich(0, 2) = 100
vergleich(1, 0) = 5
vergleich(1, 1) = 10
vergleich(1, 2) = 200
vergleich(2, 0) = 10
vergleich(2, 1) = 20
vergleich(2, 2) = 300
rem eine weitere erhöhung würde so aussehen
rem vergleich(3, 0) = 20
rem vergleich(3, 1) = 30
rem vergleich(3, 2) = 400
rem nicht zu vergessen die erhöhung bei der oberen dimensionierung
rem hier werden die anzahl der vergleiche angegeben bzw. der dimensionen
For zaehler = 0 To 2
If Cells(Zellen.Row, 1) > vergleich(zaehler, 0) And Cells(Zellen.Row, 1) < vergleich(zaehler, 1) Then
WennEndlos = vergleich(zaehler, 2)
End If
Next zaehler
End Function
Antwort 10 von Event
...da halt´ ich mich ´raus...
Antwort 11 von nighty
hi all :)
bei 36 wenn bedingingen dann so
gruss nighty
wobei die min wie maxwerte wie auch ausgabewerte angepasst werden sollten
vergleich(index,0)= grösser
der nächste index ist mit und verknüpft
vergleich(index,1)=kleiner
vergleich(index,2)=ausgabewert
wie oben beschrienem einzufuegen ist
Function WennEndlos(Zellen As Range) As Double
Application.Volatile
Dim Zelle As Range
Dim zaehler As Integer
Dim vergleich(35, 2) As Long
vergleich(0, 0) = 1
vergleich(0, 1) = 5
vergleich(0, 2) = 100
vergleich(1, 0) = 5
vergleich(1, 1) = 10
vergleich(1, 2) = 200
vergleich(2, 0) = 10
vergleich(2, 1) = 20
vergleich(2, 2) = 300
vergleich(3, 0) = 1
vergleich(3, 1) = 5
vergleich(3, 2) = 100
vergleich(4, 0) = 5
vergleich(4, 1) = 10
vergleich(4, 2) = 200
vergleich(5, 0) = 10
vergleich(5, 1) = 20
vergleich(5, 2) = 300
vergleich(6, 0) = 10
vergleich(6, 1) = 20
vergleich(6, 2) = 300
vergleich(7, 0) = 1
vergleich(7, 1) = 5
vergleich(7, 2) = 100
vergleich(8, 0) = 5
vergleich(8, 1) = 10
vergleich(8, 2) = 200
vergleich(9, 0) = 10
vergleich(9, 1) = 20
vergleich(9, 2) = 300
vergleich(10, 0) = 1
vergleich(10, 1) = 5
vergleich(10, 2) = 100
vergleich(11, 0) = 5
vergleich(11, 1) = 10
vergleich(11, 2) = 200
vergleich(12, 0) = 10
vergleich(12, 1) = 20
vergleich(12, 2) = 300
vergleich(13, 0) = 1
vergleich(13, 1) = 5
vergleich(13, 2) = 100
vergleich(14, 0) = 5
vergleich(14, 1) = 10
vergleich(14, 2) = 200
vergleich(15, 0) = 10
vergleich(15, 1) = 20
vergleich(15, 2) = 300
vergleich(16, 0) = 10
vergleich(16, 1) = 20
vergleich(16, 2) = 300
vergleich(17, 0) = 1
vergleich(17, 1) = 5
vergleich(17, 2) = 100
vergleich(18, 0) = 5
vergleich(18, 1) = 10
vergleich(18, 2) = 200
vergleich(19, 0) = 10
vergleich(19, 1) = 20
vergleich(19, 2) = 300
vergleich(20, 0) = 1
vergleich(20, 1) = 5
vergleich(20, 2) = 100
vergleich(21, 0) = 5
vergleich(21, 1) = 10
vergleich(21, 2) = 200
vergleich(23, 0) = 10
vergleich(23, 1) = 20
vergleich(23, 2) = 300
vergleich(24, 0) = 1
vergleich(24, 1) = 5
vergleich(24, 2) = 100
vergleich(25, 0) = 5
vergleich(25, 1) = 10
vergleich(25, 2) = 200
vergleich(26, 0) = 10
vergleich(26, 1) = 20
vergleich(26, 2) = 300
vergleich(27, 0) = 100
vergleich(27, 1) = 110
vergleich(27, 2) = 999
vergleich(28, 0) = 1
vergleich(28, 1) = 5
vergleich(28, 2) = 100
vergleich(29, 0) = 5
vergleich(29, 1) = 10
vergleich(29, 2) = 200
vergleich(30, 0) = 10
vergleich(30, 1) = 20
vergleich(30, 2) = 300
vergleich(31, 0) = 5
vergleich(31, 1) = 10
vergleich(31, 2) = 200
vergleich(32, 0) = 10
vergleich(32, 1) = 20
vergleich(32, 2) = 300
vergleich(33, 0) = 10
vergleich(33, 1) = 20
vergleich(33, 2) = 300
vergleich(34, 0) = 1
vergleich(34, 1) = 5
vergleich(34, 2) = 100
vergleich(35, 0) = 5
vergleich(35, 1) = 10
vergleich(35, 2) = 200
For zaehler = 0 To 35
If Cells(Zellen.Row, 1) > vergleich(zaehler, 0) And Cells(Zellen.Row, 1) < vergleich(zaehler, 1) Then
WennEndlos = vergleich(zaehler, 2)
End If
Next zaehler
End Function
bei 36 wenn bedingingen dann so
gruss nighty
wobei die min wie maxwerte wie auch ausgabewerte angepasst werden sollten
vergleich(index,0)= grösser
der nächste index ist mit und verknüpft
vergleich(index,1)=kleiner
vergleich(index,2)=ausgabewert
wie oben beschrienem einzufuegen ist
Function WennEndlos(Zellen As Range) As Double
Application.Volatile
Dim Zelle As Range
Dim zaehler As Integer
Dim vergleich(35, 2) As Long
vergleich(0, 0) = 1
vergleich(0, 1) = 5
vergleich(0, 2) = 100
vergleich(1, 0) = 5
vergleich(1, 1) = 10
vergleich(1, 2) = 200
vergleich(2, 0) = 10
vergleich(2, 1) = 20
vergleich(2, 2) = 300
vergleich(3, 0) = 1
vergleich(3, 1) = 5
vergleich(3, 2) = 100
vergleich(4, 0) = 5
vergleich(4, 1) = 10
vergleich(4, 2) = 200
vergleich(5, 0) = 10
vergleich(5, 1) = 20
vergleich(5, 2) = 300
vergleich(6, 0) = 10
vergleich(6, 1) = 20
vergleich(6, 2) = 300
vergleich(7, 0) = 1
vergleich(7, 1) = 5
vergleich(7, 2) = 100
vergleich(8, 0) = 5
vergleich(8, 1) = 10
vergleich(8, 2) = 200
vergleich(9, 0) = 10
vergleich(9, 1) = 20
vergleich(9, 2) = 300
vergleich(10, 0) = 1
vergleich(10, 1) = 5
vergleich(10, 2) = 100
vergleich(11, 0) = 5
vergleich(11, 1) = 10
vergleich(11, 2) = 200
vergleich(12, 0) = 10
vergleich(12, 1) = 20
vergleich(12, 2) = 300
vergleich(13, 0) = 1
vergleich(13, 1) = 5
vergleich(13, 2) = 100
vergleich(14, 0) = 5
vergleich(14, 1) = 10
vergleich(14, 2) = 200
vergleich(15, 0) = 10
vergleich(15, 1) = 20
vergleich(15, 2) = 300
vergleich(16, 0) = 10
vergleich(16, 1) = 20
vergleich(16, 2) = 300
vergleich(17, 0) = 1
vergleich(17, 1) = 5
vergleich(17, 2) = 100
vergleich(18, 0) = 5
vergleich(18, 1) = 10
vergleich(18, 2) = 200
vergleich(19, 0) = 10
vergleich(19, 1) = 20
vergleich(19, 2) = 300
vergleich(20, 0) = 1
vergleich(20, 1) = 5
vergleich(20, 2) = 100
vergleich(21, 0) = 5
vergleich(21, 1) = 10
vergleich(21, 2) = 200
vergleich(23, 0) = 10
vergleich(23, 1) = 20
vergleich(23, 2) = 300
vergleich(24, 0) = 1
vergleich(24, 1) = 5
vergleich(24, 2) = 100
vergleich(25, 0) = 5
vergleich(25, 1) = 10
vergleich(25, 2) = 200
vergleich(26, 0) = 10
vergleich(26, 1) = 20
vergleich(26, 2) = 300
vergleich(27, 0) = 100
vergleich(27, 1) = 110
vergleich(27, 2) = 999
vergleich(28, 0) = 1
vergleich(28, 1) = 5
vergleich(28, 2) = 100
vergleich(29, 0) = 5
vergleich(29, 1) = 10
vergleich(29, 2) = 200
vergleich(30, 0) = 10
vergleich(30, 1) = 20
vergleich(30, 2) = 300
vergleich(31, 0) = 5
vergleich(31, 1) = 10
vergleich(31, 2) = 200
vergleich(32, 0) = 10
vergleich(32, 1) = 20
vergleich(32, 2) = 300
vergleich(33, 0) = 10
vergleich(33, 1) = 20
vergleich(33, 2) = 300
vergleich(34, 0) = 1
vergleich(34, 1) = 5
vergleich(34, 2) = 100
vergleich(35, 0) = 5
vergleich(35, 1) = 10
vergleich(35, 2) = 200
For zaehler = 0 To 35
If Cells(Zellen.Row, 1) > vergleich(zaehler, 0) And Cells(Zellen.Row, 1) < vergleich(zaehler, 1) Then
WennEndlos = vergleich(zaehler, 2)
End If
Next zaehler
End Function
Antwort 12 von nighty
hi all :)
die zeile war noch fest auf spalte a bezogen ,noch vom testen
gruss nighty
diese
If Cells(Zellen.Row, 1) > vergleich(zaehler, 0) And Cells(Zellen.Row, 1) < vergleich(zaehler, 1) Then
gegen diese austauschen
If Cells(Zellen.Row, Zellen.Column) > vergleich(zaehler, 0) And Cells(Zellen.Row, Zellen.Column) < vergleich(zaehler, 1) Then
die zeile war noch fest auf spalte a bezogen ,noch vom testen
gruss nighty
diese
If Cells(Zellen.Row, 1) > vergleich(zaehler, 0) And Cells(Zellen.Row, 1) < vergleich(zaehler, 1) Then
gegen diese austauschen
If Cells(Zellen.Row, Zellen.Column) > vergleich(zaehler, 0) And Cells(Zellen.Row, Zellen.Column) < vergleich(zaehler, 1) Then
Antwort 13 von Saarbauer
Hallo,
mein Lösungvorschlag
=SUMMENPRODUKT((A11:A26<A30)*(A12:A27>=A30)*(A11:Y11<B30)*(B11:Z11>=B30)*(B12:Z27))
In Zelle A 30 steht das Entfernung
In Zelle B30 steht das Gewicht
Die Formel oben kann an jeder belibigen Stelle stehen, ausser A11 da hier eine 0 zwingend erforderlich ist.
Gruß
Helmut
mein Lösungvorschlag
=SUMMENPRODUKT((A11:A26<A30)*(A12:A27>=A30)*(A11:Y11<B30)*(B11:Z11>=B30)*(B12:Z27))
In Zelle A 30 steht das Entfernung
In Zelle B30 steht das Gewicht
Die Formel oben kann an jeder belibigen Stelle stehen, ausser A11 da hier eine 0 zwingend erforderlich ist.
Gruß
Helmut
Antwort 14 von Saarbauer
Hallo,
habe festgestellt, dass auch die Zeille 28 leer bleiben muss und die Spalte AA,
ausserdem ist die Formel
=SUMMENPRODUKT((A11:A27<A30)*(A12:A28>=A30)*(A11:Z11<B30)*(B11:AA11>=B30)*(B12:AA28))
zu nutzen, sonst wird die letzte Zeile und Spalte nicht ausgewertet
Gruß
Helmut
habe festgestellt, dass auch die Zeille 28 leer bleiben muss und die Spalte AA,
ausserdem ist die Formel
=SUMMENPRODUKT((A11:A27<A30)*(A12:A28>=A30)*(A11:Z11<B30)*(B11:AA11>=B30)*(B12:AA28))
zu nutzen, sonst wird die letzte Zeile und Spalte nicht ausgewertet
Gruß
Helmut
Antwort 15 von unsereiner
=WENN(A1=1;B2;)&WENN(A1=2;B2;)&WENN(A1=3;B3;)
&WENN(A1=4;B4;)&WENN(A1=5;B5;)
&WENN(A1=6;B6;)&WENN(A1=7;B7;)
&WENN(A1=8;B8;)&WENN(A1=9;B9;)
Du kannst mit jeder WENN-Funktion mehr als 7 Abfragen stellen.
Einfach nur ein & vor das WENN schreiben.
Probier es mal.
Gruß Frank
&WENN(A1=4;B4;)&WENN(A1=5;B5;)
&WENN(A1=6;B6;)&WENN(A1=7;B7;)
&WENN(A1=8;B8;)&WENN(A1=9;B9;)
Du kannst mit jeder WENN-Funktion mehr als 7 Abfragen stellen.
Einfach nur ein & vor das WENN schreiben.
Probier es mal.
Gruß Frank