Verweis, Matrix oder welche Formel?

[Thomas75]

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

[Jockel]

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.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=38 mal umsehen...

[WillWissen]

Hi Thomas,

meintest du das so?

Tabelle1

	A	B	C	D	E	F
25	Unterdachmarkise mit E-Motor					1795

Formeln der Tabelle

Zelle Formel 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

[BoskoBiati]

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.

[WillWissen]

Hi Edgar,

Deine Formel liefert einen Fehler.

guggscht du mal, wann ich meinen Fauxpas (hatte aus meiner Testdatei falsch kopiert) ausgebügelt habe?
 

wobei ich es vorziehe, im Index nur den tatsächlichen Wertebereich anzusprechen.

yep, kann man machen.

[BoskoBiati]

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'
	A	B	C	D	E	F	G
9			Kostenschätzung
10
11
12					Breite	Tiefe
13	Ihre gewünschte Terrassengröße in cm:				300	330
14
15	Variante 1:					1340,00 €
16	Holz-Terrassenüberdachung mit 8 mm
17	Verbundsicherheitsglas (VSG)
18
19	Variante 2:					990,00 €
20	Holz-Terrassenüberdachung mit 16 mm
21	Stegdoppelplatten Klassik "klar" oder "opal weiß"
22
23	Abbund					250,00 €
24
25	Unterdachmarkise mit E-Motor					1924,00 €	1924
26
27	Baldachin-Beschattung					990,00 €
28
29	Alu-Wandanschlussprofil					104,53 €
30
31	Dachrinne	"Auswahl"
32
33	Farbe
34
35	Zustell-Service					49,00 €
36
37
38
39				Variante 1:		2733,53 €

Zelle	Formel
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'
	A	B	C
3	Variante 1 mit VSG 8 mm:		135
4	Variante 2 mit Stegplatte:		100
5	Abbund		250
6	Wandanschluss 305 cm	0	81,13
7	Wandanschluss 410 cm	305	109,06
8	Wandanschluss 510 cm	410	135,66
9	Wandanschluss 610 cm	510	162,26
10	Wandanschluss bis 820 cm	610	218,12
11	lfdm Preis		26,6
12	Seitenkappen		14,4
13	Wandanschlussverbinderplatte		5
14	Silikon		9

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[WillWissen]

Hi Edgar,

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


Tabelle1

	E	F	G	H
12	Breite	Tiefe
13	268	255
14
24		Bereich $A$1:$H$12	Bereich $B$3:$H$12	Edgar
25		1886	1886	1798

Formeln der Tabelle

Zelle Formel 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

	A	B	C	D	E	F	G	H
1	Ausfall	450	400	350	300	250	200	150
2	Achsmaß
3	600	3013	2882	2737	2469	2342	2200	1762
4	550	2796	2676	2504	2293	2172	2091	1850
5	500	2668	2560	2417	2206	2110	2039	1959
6	450	2508	2428	2320	2137	2050	1981	2062
7	400	2301	2192	2118	2047	1938	1853	2194
8	350	2194	2113	2018	1961	1851	1805	2301
9	300	2062	1993	1924	1886	1776	1748	2508
10	250	1959	1895	1833	1798	1706	1666	2668
11	200	1850	1795	1740	1669	1615	1557	2796
12	150	1762	1714	1669	1611	1565	1520	3013

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Das will/braucht Thomas

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

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

Tabelle1

	E	F	G	H
12	Breite	Tiefe
13	350	350
14
24		Bereich $A$1:$H$12	Bereich $B$3:$H$12	Edgar
25		2018	2018	2113

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Tabelle3

	A	B	C	D	E	F	G	H
1	Ausfall	450	400	350	300	250	200	150
2	Achsmaß
3	600	3013	2882	2737	2469	2342	2200	1762
4	550	2796	2676	2504	2293	2172	2091	1850
5	500	2668	2560	2417	2206	2110	2039	1959
6	450	2508	2428	2320	2137	2050	1981	2062
7	400	2301	2192	2118	2047	1938	1853	2194
8	350	2194	2113	2018	1961	1851	1805	2301
9	300	2062	1993	1924	1886	1776	1748	2508
10	250	1959	1895	1833	1798	1706	1666	2668
11	200	1850	1795	1740	1669	1615	1557	2796
12	150	1762	1714	1669	1611	1565	1520	3013

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Woran liegt's?

[WillWissen]

Grrrrr - Spaltenzahl hat nicht gepasst.

So klappts:

=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.

[Thomas75]

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