Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 08:56:18
Textbox in Tabellenblatt ohne Bestätigung in Zelle eintragen
20.04.2024 07:57:54
Tablle fürs E-Auto - Preis wenn kürzer als 60 Min Ladezeit
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1556to1560
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1556to1560
1556to1560
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

SVERWEIS / Bereich_Verweis - anders als früher

SVERWEIS / Bereich_Verweis - anders als früher
15.05.2017 15:47:09
Claus
Hallo Spezialisten,
sagt mal, früher war doch das dritte Kriterium beim SVERWEIS so, dass man -1 oder 0 oder 1 angegeben hat. 0 ist klar - genaue Übereinstimmung. Das brauch ich zu 98 %...
Jetzt habe ich aber eine Liste von Werten (natürlich aufsteigend sortiert) und ich möchte die Zeile zurückgegeben haben, wo der Wert erstmals größer als mein Suchkriterium ist. Ist die SVERWEIS-Formel denn jetzt tatsächlich so geändert, dass ich nur noch die Zeile zurückbekomme wo der Wert am ähnlichsten ist? Das mag ja in etlichen Fällen sehr praktisch sein (hätte ich das früher gebraucht, hätte ich ja in zwei Spalten die Werte mit Bereich_Verweis 1 und Bereich_Verweis -1 geholt und mit einer Wenn-Formel die geringere Abweichung ermittelt. Kompliziert aber gut lösbar) aber ich brauche es jetzt nun mal anders. Nämlich so wie es früher ging. (oder bin ich da jetzt ganz auf dem falschen Dampfer?)
Gibt es da eine Möglichkeit?
Vielen Dank und liebe Grüße
Claus

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Du meinst VERGLEICH() und nicht SVERWEIS() owT
15.05.2017 16:04:50
Sheldon
Gruß
Sheldon
AW: SVERWEIS / Bereich_Verweis - anders als früher
15.05.2017 16:36:08
Claus
Oha! Vielen Dank an UweD und Sheldon.
Tatsächlich ist es bei VERGLEICH() so... und da ich sehr oft damit arbeite kenne ich es natürlich von da. Trotzdem bin ich mir immer noch ziemlich sicher, dass es bei SVERWEIS() in einer früheren Version auch mal so war. Für mich was der SVERWEIS immer VERGLEICH mit Zusatz. Aber vielleicht ist das ja nur meine Denke, weil es bei Bereich_Verweis 0 (was es in beiden Formeln gibt) ja auch passt und ich meist mit Materialnummern zu tun habe, wo eben eins daneben ganz bewusst nie gefunden werden soll... da ist also Bereich_Verweis immer 0.
Also vielen Dank und ich versuche es jetzt mal mit den entsprechenden anderen Formeln (im ersten Schnellversuch hat es nicht geklappt... mein Suchkriterium ist eine Variable und die Ergebnisspalte enthält Zeitwerte... und jetzt hab ich keine Zeit mehr heute.... ich berichte dann, ob es geklappt haben wird... ;-)
Anzeige
AW: ham einfachsten mit AGGREGAT() ...
15.05.2017 19:25:50
...
Hallo Claus,
... angenommen Deine Zeitangaben stehen in A2:A99 (mit oder auch ohne Leerzellen) und Dein Vergleichswert steht in E1, dann reicht folgende Formel bereits aus, um die kleinste Zeit die größer als die die Zeit in e1 ist zu ermitteln:
=AGGREGAT(15;6;A2:A99/(A2:A99>E1);1)
bzw. wenn nur deren Zeilennummer interessieren sollte, so: 

=AGGREGAT(15;6;ZEILE(A2:A99)/(A2:A99>E1);1)
dafür müssten noch nicht einmal die Zeiten in Spalte A irgendwie sortiert sein.
Gruß Werner
.. , - ...
Anzeige
das sind Äpfel und Birnen
15.05.2017 20:28:15
WF
Hi,
die erste Formel ermittelt die kleinste Zahl, die größer als E1 ist - korrekt.
Die zweite aber nicht die Zeilen-Nr. dieser Zahl, sondern die erste Zeilen-Nr., in der eine größere Zahl als E1 vorkommt.
WF
AW: beide meist wohlschmeckend, aber ...
16.05.2017 09:19:13
...
Hallo WF,
... im dem Fall war aber letztere Frucht ääh Formel faul. Danke für den Hinweis.
Die Formel zur Zeilenermittlung hatte ich nachträglich in meinen Text eingefügt, nachdem ich vor dessen Absenden mir nochmal die Ausgangsfragestellung angeschaut hatte und feststellte, dass ja vordergründig nach der Zeilennummer gefragt war. Bzgl. der Ausgangsfragestellung ist meine 2. Formel zwar auch korrekt (weil da die Zeitdatenwerte aufwärts sortiert sein sollten) aber bzgl. meines gestrigen letzten Aussagesatzes, der ursprünglich nur für die erste Formel geschrieben war und da auch uneingeschränkt gilt.
Bei unsortierten Zeiten in Spalte A, müsste eine korrekte Formel für die Zeilenermittlung des kleinsten Wertes größer E1 natürlich dann z.B. so lauten:
=VERGLEICH(AGGREGAT(15;6;A2:A99/(A2:A99>E1);1);A:A;)
Gruß Werner
.. , - ...
Anzeige
AW: beide meist wohlschmeckend, aber ...
16.05.2017 09:37:16
Claus
Hallo zusammen,
vielen Dank für diese Hinweise.
Also ich werte ein GPX-Datei aus. Von einem Lauf - und ich möchte z. B. den schnellsten gelaufenen Kilometer ermitteln.
Das Navi macht ungefähr alle 4 Sekunden einen Wegpunkt. Aber eben nicht genau alle 4 Sekunden - dann wäre es einfacher.
Für die zurückgelegte Strecke von Wegpunkt zu Wegpunkt habe ich die Berechnung schon. Eine Summenspalte hierzu natürlich auch.
Diese Summenspalte ist also zwangsläufig aufsteigend sortiert... (selbst wenn ich rückwärts laufen würde zählt das hinzu... ;-) )
Jetzt möchte ich also zu jeder zurückgelegten Entfernung (dessen zugehörige Zeit daneben steht) die zugehörige Zeit, (und dann die Differenz) der um x höheren Entfernung (x wäre z. B. 1 Km... das soll aber variabel sein... das schreibe ich in die Kopfzeile) haben. Aber eben mindestens um x höher - und nicht ungefähr um x höher... ich will ja nicht mogeln.
Das Minimum dieser Differenzzeiten wäre dann mein gesuchter schnellster Km (bzw. schnellste Strecke x)
Das hätte ich mit der SVERWEIS-Formel, so wie sie in meiner Vorstellung früher war, problemlos geklappt. Geht das denn nun mit der AGGREGAT - Formel auch?
Anzeige
AW: ja, kann man so ermitteln ...
16.05.2017 14:26:32
...
Hallo Claus,
... stelle doch einfach mal eine Datei mit entsprechenden Daten hier ein. Dann können wir eine entsprechende konkrete Formellösung aufstellen.
Gruß Werner
.. , - ...
AW: ja, kann man so ermitteln ...
16.05.2017 16:16:17
Claus
Also meine Formel für I2 (und das dann eben ganz runterziehen) wäre jetzt:
=(INDEX(H:H;VERGLEICH(G2+$I$1;G:G;1)+1;1)-H2)
Ob das jetzt mit AGGREGAT() noch kürzer oder eleganten geht weiß ich nicht.
Meine Lösung hat einen kleinen Schönheitsfehler: Ich muss unten an die Werte der zurückgelegten Strecke (Spalte G) einen großen Wert anhängen (z. B. 999) und daneben (Spalte H) auch (z. B. 23:59:55)
Anzeige
AW: so zwar möglich, aber ...
17.05.2017 13:38:44
...
Hallo Claus,
... diese so ermittelten Werte sind mE nicht korrekt bzw. nur Näherungswerte, wobei die Abweichung natürlich relativ gering ist. dafür ist die Ermittlung natürlich einfach und schnell.
Im Gegensatz zu meinem Lösungsansatz (die Formeln I2:K2 einfach weit genug nach und M2 wohl max 5 Zellen unten kopieren.
GPSies

 IJKLM
10,200 kmnach ca:v  in km/hBestzeit.nach …
20:01:060,200 km11,106 km/h0:00:582,400 km
30:01:040,400 km11,365 km/h 4,000 km
40:01:000,600 km11,873 km/h  
50:00:590,800 km12,287 km/h  
60:01:001,000 km11,765 km/h  
70:01:021,200 km11,796 km/h  
80:01:051,400 km11,274 km/h  

Formeln der Tabelle
ZelleFormel
I2=WENNFEHLER(SVERWEIS(AGGREGAT(15;6;G$2:G$999/(G$2:G$999>=ZEILE(X1)*I$1); 1); G:H;2;)-WENN(ZEILE(X2)>2;SVERWEIS(AGGREGAT(15;6;G$2:G$999/(G$2:G$999>=(ZEILE(X1)-1)*I$1); 1); G:H;2;); H2); "")
J2=WENN(I2="";"";ZEILE(X1)*I$1)
K2=WENNFEHLER((AGGREGAT(15;6;G$2:G$999/(G$2:G$999>=ZEILE(X1)*I$1); 1)-AGGREGAT(15;6;G$2:G$999/(G$2:G$999>=(ZEILE(X1)-1)*I$1); 1)*(ZEILE(X2)>2))/I2/24;"")
L2=MIN(I:I)
M2=WENNFEHLER(AGGREGAT(15;6;J$2:J$99/(I$2:I$99=L$2); ZEILE(A1)); "")

Gruß Werner
.. , - ...
Anzeige
AW: so zwar möglich, aber ...
18.05.2017 13:07:00
Claus
Vielen Dank Werner,
kurze völlig wertfreie Vorbemerkung: Du bist kein Läufer... Km/h spielt da nämlich keine Rolle, wir wollen die pace in Minuten / Km. Die Umrechnung ist aber kein Problem.
Also ich glaube schon, dass meine Werte korrekt sind - im Sinne dessen was ich wollte. Ich wollte nämlich für jeden Wegpunkt die verstrichene Zeit bis zum ersten Wegpunkt der mindestens 0,2 Km (wir bleiben mal beim Beispiel x=0,2 - blaues Datenfeld I1) weiter ist. Dabei verliere ich sozusagen die Strecke, die über diese 0,2 Km rausgeht.
Du hast mich jetzt allerdings auf eine Idee gebracht: Sagen wir, der Wegpunkt wäre 0,205 Km weiter, und die Zeit wäre 58 Sekunden fortgeschritten, dann rechnete ich bisher 58 Sek. pro 0,2 Km. Ich habe ja aber sogar einen um 5 m längere Strecke in diesen 58 Sek. zurückgelegt... also darf ich als Rekordpace die 58 Sek. pro 0,205 Km verbuchen... denn die bin ich ja in echt allerhöchstwahrscheinlich auch auf 200 m mindestens gelaufen.
Sodele, nun habe ich deine Formeln reinkopiert und versuche zu verstehen, was die machen. Ich habe z. B. den innersten Teil rauskopiert... und versuche das nachzuvollziehen... aber mir fehlen zu viele Unbekannte sozusagen. Dein Aufbau ist ja ein ganz Anderer.... du berechnest alle x Km (Bsp.: 0,200) den lokalen Rekord... aber so ganz durchschaue ich das leider nicht. Die von mir berechneten schnellsten 0,2 Km Zeiten liegen alle vor 1,085 Km (Beginn) - deine offensichtlich bei ca. 2,4 und 4 Km. Da passt was nicht.
Insofern komme ich jetzt mit deinen Formeln nicht weiter - und bleibe bei meiner... bzw. bei meiner ergänzten:
=(INDEX(F:F;VERGLEICH(H2+$J$1;H:H;1)+1;1)-F2)*$J$1/((INDEX(H:H;VERGLEICH(H2+$J$1;H:H;1)+1;1))-H2)
Liebe Grüße, Claus
Anzeige
AW: ist mE ncht so ...
18.05.2017 23:44:35
...
Hallo Claus,
... ich schrieb Dir ja, dass "...(Deine)so ermittelten Werte sind mE nicht korrekt bzw. nur Näherungswerte, wobei die Abweichung natürlich relativ gering ist. dafür ist die Ermittlung natürlich einfach und schnell".
Auch die von mir ermittelten Zeitangaben lassen sich eigentlich nicht wirklich vergleichen, weil diese zwar eher Deiner Abschnittslängen-Vorgabe entsprechen, aber ja trotzdem nicht exakt dieser.
Deswegen hatte ich die Geschwindigkeit für die "wahre" Streckenlängen ausgerechnet, für die die jeweiligen Zeit ermittelt wurde. Und nur diese kannst Du mE vergleichen (die Umrechnung in Min/km kannst Du ja einfach vornehmen). Danach hattest Du die "schnellste Beine" zwischen 600 und 800 m
Gruß Werner
.. , - ...
Anzeige

300 Forumthreads zu ähnlichen Themen

Matrixformel Bereich verschieben
Baywatch

HI
Meine Frage wie kann ich folgenden Matrixausdruck erweitern, das er nicht nur den Wert aus spalte A sondern auch aus Spalte D wiedergibt? wenn es geht mit kurzer erklärung, damit ich sowas demnächst selber machen kann danke.
{=BEREICH.VERSCHIEBEN($A$1;SUMME(WENN(C1:C6=MAX(WENN(B1:B6=...
14.08.2008 19:08:00
Thread-ID: 1001948
2 Beiträge
Anzeige
Namensformel Bereich.Verschieben
Ralf

Hallo Forum,
ich habe in einer Arbeitsmappe zwei Namen (mit hilfe des Herber Forums) definiert, die mir Werte für ein xy-Diagramm definieren:
x-Werte: =BEREICH.VERSCHIEBEN(Diagramm!$C$5;0;0;1;Anzahl_Werte_xAchse)
y-Werte: =BEREICH.VERSCHIEBEN(Diagramm!$C$5;0;1;1;Anzahl_Werte_yAchse)...
29.07.2013 13:02:11
Thread-ID: 1324644
22 Beiträge
Array Formel Problem mit Bereich.Verschieben
Alex
Hallo Zusammen
leider kann ich die Tabelle nicht auf den Server stellen. Ich hätte eine Frage die vielleicht jemand beantworten kann.
Wenn ich diesen Teil der Formel (es scheint auch nur das dort ein der Wertfehler autritt) so schreibe:
(WENN((BEREICH.VERSCHIEBEN(D$10;MAX(WENN(ZEILE(Dat...
15.04.2016 15:16:47
Thread-ID: 1488245
4 Beiträge
Anzeige
Formel mit BEREICH.VERSCHIEBEN???
Ha-Jü

Hallo zusammen,
in der Beispielmappe ist ein Schema wie eine
Formel funktionieren soll,
https://www.herber.de/bbs/user/8797.xls
ich komme nur einfach nicht zur Formellösung
Für einen Tipp wäre ich Super Dankbar!!!
Gruss Ha-Jü
22.07.2004 14:17:43
Thread-ID: 459131
4 Beiträge
SVERWEIS funktioniert nicht
sascha76er

Hallo,
hoffentlich kann mir jemand hier helfen, bei mir funktionieren die SVERWEISE für die Spalten
AI, AQ, AR, AS
nicht.
Leider ist meine Liste größer wie 300 kb, deshalb musste ich die Liste bei einem 1Klickhoster bereitstellen.
Liste: http://www.mediafire.com/?oxyk0vmb21x...
08.10.2008 11:23:24
Thread-ID: 1014967
7 Beiträge
Wenn-Formel oder Sverweis?
Gerd

Tach auch!
Eine wahrscheinlich einfache Frage.
In Zelle A1 kann eine Angabe stehen (drei Variabeln: X, Y oder Z). In Zelle A2 soll entsprechend etwas dazu ausgewiesen werden:
Wenn A1 = X dann 1
Wenn A1 = Y dann 2
Wenn A1 = Z dann 3
Wie mache ich das am klügsten? Danke fü...
30.10.2008 10:28:00
Thread-ID: 1020502
6 Beiträge
Wenn Formel mit Spalte
Maris

Hi zusammen,
ist das möglich, dass ich eine Wenn-Formel auf eine Spalte und eine Zelle in der Zeile beziehe? Bsp.:
Tabelle A B C Ergebnis ERM VOLL OHNE 1 x ERM 2 x...
05.11.2008 16:30:00
Thread-ID: 1021984
6 Beiträge
Wenn-Oder Formel über VBA funktioniert nicht
sharky1000

Hallo,
ich habe eine etwas komplexere Wenn-Oder Formel erstellt, welche ich über VBA in eine Zelle einfügen möchte.
Formel in E2:
=WENN(ODER(LINKS(D2;2)="M1";LINKS(D2;2)="M2";LINKS(D2;2)="M3";LINKS(D2;2)="M4";LINKS(D2;2)="M5"); LINKS(D2;1);WENN(LINKS(D2;2)="NM";LINKS(D2;2);WENN(LIN...
06.11.2008 14:11:01
Thread-ID: 1022285
7 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
1
4
21
3
2
3
6
5
5
6
4
6
3
3
6
4
7
8
7
7
3
23
6
13
5