Das Clever-Excel-Forum.de - Treffen
... 14.-16. September 2018 im Allgäu ...

Verweis, Matrix oder welche Formel?
#1
Moin zusammen,

habe eine kleine Tabelle gemacht, wo ich einen Wert auslesen möchte.
Bei Blatt 1 kann ich die Varianten (Breite/ Tiefe) eingeben und soll aus Blatt 3 rausgesucht werden.
Wenn nicht genau, das nächst größere passende Maß.
Hatte dies mit der Verweis und Wenn Funktion gemacht, leider wurde diese zu lang oder hab den Überblick verloren.
Hatte erst die Werte in Blatt 3 aufsteigend, aber dann irgendwo gelesen, dass absteigend besser wäre.

Wie würdet ihr dieses Problem lösen!?
Habe auch mal die Datei hochgeladen. Hoffe ihr könnt mir helfen!


Vielen Dank im Voraus!

Gruß
Thomas


Angehängte Dateien
.xlsm   Kostenschätzung variabel.xlsm (Größe: 8,17 KB / Downloads: 5)
to top
#2
Hi Thomas, ich würde dir dringend raten, dich mal mit der Formel/Funktion SUMME() vertraut zu machen... Du benutzt die hier inflationär... (... zudem solltest du bei dieser Sachlage die Tabelle in Tabelle3 aufsteigend sortieren, sowohl horizontal als auch vertikal...)

Für die eigentliche Frage solltest du dich hier: http://www.excelformeln.de/formeln.html?welcher=38 mal umsehen...
cu Jörg eine Rückmeldung wäre ganz reizend XL2003 bis XL2013
to top
#3
Hi Thomas,

meintest du das so?

Tabelle1

ABCDEF
25Unterdachmarkise mit E-Motor1795
Formeln der Tabelle
ZelleFormel
F25=INDEX(Tabelle3!$A$1:$H$12;VERGLEICH($E$13;Tabelle3!$A$1:$A$12;-1);VERGLEICH($F$13;Tabelle3!$A$1:$H$1;-1))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#4
Hallo,


bei der Tabelle reicht das:


Code:
=wennfehler(INDEX(Tabelle3!$B$3:$H$12;13-KÜRZEN(E13/50;);9-KÜRZEN(F13/50;));"")
@Günter,
Deine Formel liefert einen Fehler. Hier mal eine korrigierte Version:

Code:
=INDEX(Tabelle3!$B$3:$H$12;VERGLEICH(E13;Tabelle3!$A$3:$A$12;-1);VERGLEICH(F13;Tabelle3!$B$1:$H$1;-1))
=INDEX(Tabelle3!$A$1:$H$12;VERGLEICH(E13;Tabelle3!$A$1:$A$12;-1);VERGLEICH(F13;Tabelle3!$A$1:$H$1;-1))

wobei ich es vorziehe, im Index nur den tatsächlichen Wertebereich anzusprechen.
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#5
Hi Edgar,

Zitat:Deine Formel liefert einen Fehler.

guggscht du mal, wann ich meinen Fauxpas (hatte aus meiner Testdatei falsch kopiert) ausgebügelt habe? Tonguesmiley
 
Zitat:wobei ich es vorziehe, im Index nur den tatsächlichen Wertebereich anzusprechen.

yep, kann man machen.
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#6
Hallo Günter,

während ich dran gearbeitet habe.

@Thomas,

Die Formel in F29 ist völliger Blödsinn.
1. ist dieser Teil: WENN(UND(E13>Tabelle2!B9;E13<=Tabelle2!B6+Tabelle2!B7);AUFRUNDEN(Tabelle2!C7+Tabelle2!C7+Tabelle2!C11+Tabelle2!C13;0) viermal vorhanden und bringt das gleiche Ergebnis wie dieser Teil, nur ist da 2*Tabelle2!C7 drin statt Tabelle2!C7+Tabelle2!C7
2. wäre das ein Fall für VERWEIS, dazu müsstest Du in Tabelle 2 nur eine Zeile einfügen und die Zahlen umordnen!
3. beißen sich die Varianten mit der maximalen Größe der Markise in Tabelle 3!

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFG
9  Kostenschätzung    
10       
11       
12    BreiteTiefe 
13Ihre gewünschte Terrassengröße in cm:   300330 
14       
15Variante 1:    1340,00 € 
16Holz-Terrassenüberdachung mit 8 mm      
17Verbundsicherheitsglas (VSG)      
18       
19Variante 2:    990,00 € 
20Holz-Terrassenüberdachung mit 16 mm      
21Stegdoppelplatten Klassik "klar" oder "opal weiß"      
22       
23Abbund    250,00 € 
24       
25Unterdachmarkise mit E-Motor    1924,00 €1924
26       
27Baldachin-Beschattung    990,00 € 
28       
29Alu-Wandanschlussprofil    104,53 € 
30       
31Dachrinne"Auswahl"     
32       
33Farbe      
34       
35Zustell-Service    49,00 € 
36       
37       
38       
39   Variante 1: 2733,53 € 

ZelleFormel
F15=AUFRUNDEN(E13/100*F13/100*Tabelle2!C3;-1)
F19=E13/100*F13/100*Tabelle2!C4
F23=Tabelle2!C5
F25=INDEX(Tabelle3!$B$3:$H$12;13-KÜRZEN(E13/50;);9-KÜRZEN(F13/50;))
G25=INDEX(Tabelle3!$A$1:$H$12;VERGLEICH(E13;Tabelle3!$A$1:$A$12;-1);VERGLEICH(F13;Tabelle3!$A$1:$H$1;-1))
F27=E13*F13%
F29=WENN(E13>820;"nicht möglich";SUMME(VERWEIS(E13;Tabelle2!B6:C10);Tabelle2!C12;Tabelle2!C14))
F39=SUMME(F15+F23+F27+F29+F35)
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg


Arbeitsblatt mit dem Namen 'Tabelle2'
 ABC
3Variante 1 mit VSG 8 mm: 135
4Variante 2 mit Stegplatte: 100
5Abbund 250
6Wandanschluss 305 cm081,13
7Wandanschluss 410 cm305109,06
8Wandanschluss 510 cm410135,66
9Wandanschluss 610 cm510162,26
10Wandanschluss bis 820 cm610218,12
11lfdm Preis 26,6
12Seitenkappen 14,4
13Wandanschlussverbinderplatte 5
14Silikon 9
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#7
Hi Edgar,

hilf mir mal auf die Sprünge - wieso bekomme ich bei deiner Formel falsche Ergebnisse?


Tabelle1

EFGH
12BreiteTiefe
13268255
14
24Bereich $A$1:$H$12Bereich $B$3:$H$12Edgar
25188618861798
Formeln der Tabelle
ZelleFormel
F25=INDEX(Tabelle3!$A$1:$H$12;VERGLEICH($E$13;Tabelle3!$A$1:$A$12;-1);VERGLEICH($F$13;Tabelle3!$A$1:$H$1;-1))
G25=INDEX(Tabelle3!$B$3:$H$12;VERGLEICH(E13;Tabelle3!$A$3:$A$12;-1);VERGLEICH(F13;Tabelle3!$B$1:$H$1;-1))
H25=INDEX(Tabelle3!$B$3:$H$12;13-KÜRZEN(E13/50;);9-KÜRZEN(F13/50;))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Tabelle3

ABCDEFGH
1Ausfall450400350300250200150
2Achsmaß
36003013288227372469234222001762
45502796267625042293217220911850
55002668256024172206211020391959
64502508242823202137205019812062
74002301219221182047193818532194
83502194211320181961185118052301
93002062199319241886177617482508
102501959189518331798170616662668
112001850179517401669161515572796
121501762171416691611156515203013

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Das will/braucht Thomas

Zitat:Wenn nicht genau, das nächst größere passende Maß.


Ich komme aber auch bei passenden Maßen auf ein anderes Ergebnis.

Tabelle1

EFGH
12BreiteTiefe
13350350
14
24Bereich $A$1:$H$12Bereich $B$3:$H$12Edgar
25201820182113

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Tabelle3

ABCDEFGH
1Ausfall450400350300250200150
2Achsmaß
36003013288227372469234222001762
45502796267625042293217220911850
55002668256024172206211020391959
64502508242823202137205019812062
74002301219221182047193818532194
83502194211320181961185118052301
93002062199319241886177617482508
102501959189518331798170616662668
112001850179517401669161515572796
121501762171416691611156515203013

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Woran liegt's?
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#8
Grrrrr - Spaltenzahl hat nicht gepasst.

So klappts:

Zitat:=INDEX(Tabelle3!$B$3:$H$12;13-KÜRZEN(E13/50;);10-KÜRZEN(F13/50;))
Allerdings dürfen die gesuchten Werte nicht unter dem kleinsten liegen. Bei einem Maß von 145 x 145 cm müsste das nächstgrößere, also 150 x 150 genommen werden. Bei der Index-Fkt. kommt richtigerweise der Referenzwert 3013 raus, bei deiner Formel erhalte ich einen Bezug-Fehler.
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#9
Hallo alle zusammen,

vielen Dank euch, die mir geholfen haben hier.
Habe das mit der Index Tabelle jetzt gemacht.
=INDEX(Tabelle3!$B$3:$H$12;VERGLEICH(E13;Tabelle3!$A$3:$A$12;-1);VERGLEICH(F13;Tabelle3!$B$1:$H$1;-1))

Scheint so zu klappen.

Freut mich, dass es so schnell gelöst wurde.


Fall ich mal wieder nicht weiterkomme, bin ich sicher wieder hier!

Danke nochmal!!!

Gruß
Thomas
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste