Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 11:49:48
VAB und Bedingte Formatierung nimmt die Farbe im Makro nicht
26.04.2024 11:38:46
Acrobat PDF Formularfelder mit Excel ausfüllen
26.04.2024 11:23:04
zählen wenn - aber nur die selektierten Zeilen
26.04.2024 10:08:11
Summieren von Zellen mit bedingter Formatierung
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1840to1844
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
1840to1844
1840to1844
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Wert finden per Formel

Wert finden per Formel
08.08.2021 16:59:12
Peter
Hallo ihr Excelspezialisten,
ich suche eine Formel mit welcher ich einen Wert auslesen kann.
In Spalte B befinden sich Datumwerte in Spalte H befinden sich Werte in € gebildet durch Formeln.
Wenn nun in Zelle AB2 das Enddatum steht wird mit der Formel "=SVERWEIS(AB2;B11:B500;1)"
das erste Datum vor dem Datum von AB2 ausgelesen. Ich benötige jedoch den Wert in € aus Spalte H.
Könnt ihr mir bitte helfen.
Gruss
Peter

28
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Wert finden per Formel
08.08.2021 17:01:55
Hajo_Zi
Hallo Peter,
"=SVERWEIS(AB2;H11:B500;7;0)"
GrußformelHomepage
AW: Wert finden per Formel
08.08.2021 17:12:23
Peter
Hallo Hajo,
besten Dank. Wenn ich das Datum "31.07.2020" eingebe dann funktioniert es. Wenn ich "31.07.2021" eingebe funktioniert es nicht.
Noch besser wäre es, wenn ich den ersten Wert zwischen Anfangsdatum = AA2 und Enddatum = AB2 auslesen könnte.
Gruss
Peter
AW: Wert finden per Formel
08.08.2021 17:17:01
Hajo_Zi
Hallo Peter,
Zu Deiner Datei kann ich nichts schreiben, was wohl daran liegt das ich nicht auf fremde Rechner schaue.Ich baue keine Datei nach.
Sollte die Datei verlinkt werden?
Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten
http://www.ms-office-forum.de/forum/showthread.php?t=322895
ändern.
Bilder lade ich mir nicht runter, da Excel damit nichts anfangen kann.
http://www.excel-ist-sexy.de/bilder-statt-datei/
Hochgeladene Bilder können zwar als solche in Excel importiert werden, sind jedoch bei der Lösung von Problemen nicht sehr hilfreich, da man die eigentlichen Daten nicht ohne große und zeitraubende Umwege direkt in die Tabelle übertragen kann.
Das ist nur meine Meinung zu dem Thema.
http://www.excel-ist-sexy.de/bilder-statt-datei/
Hochgeladene Bilder können zwar als solche in Excel importiert werden, sind jedoch bei der Lösung von Problemen nicht sehr hilfreich, da man die eigentlichen Daten nicht ohne große und zeitraubende Umwege direkt in die Tabelle übertragen kann.
Das ist nur meine Meinung zu dem Thema.
Gruß Hajo
Anzeige
falsche Formel
08.08.2021 17:45:53
Hajo:Zi
Hallo Peter,
=SVERWEIS(AB2;B11:H500;1;0)"
Gruß Hajo
hier meine Ausschnitt
08.08.2021 17:54:57
Hajo:Zi
Hallo Peter,

Tabelle1
 ABAC
231.07.20206
331.07.202115
Schriftart wird in dieser Tabelle nicht dargestellt

verwendete Formeln
Zelle Formel Bereich N/A
AC2:AC3=SVERWEIS(AB2;$B$11:$H$500;7;0)  

Zahlenformate
Zelle Format Inhalt
AB2 'TT.MM.JJJJ  42581
AB3 'TT.MM.JJJJ  42946
Zellen mit Format Standard werden nicht dargestellt

Schriftformate  
Zelle Rot Grün Blau Color Stil Unterstreichung Effekte Durchgestrichen Schriftart
AB2:AC3 0 0 0 0        Arial 
Zellen mit Schriftformatierung automatisch werden nicht dargestellt
http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://Hajo-Excel.de/tools.htm
Add-In-Version 25.23 einschl. 64 Bit
XHTML-Tabelle zur Darstellung in Foren, einschl. Funktion zum Zurücklesen in eine Excel-Tabelle


Gruß Hajo
Anzeige
richtiger Name
08.08.2021 18:01:16
Hajo_Zi
AW: so wie Du es beschrieben hast ...
08.08.2021 18:46:51
neopa
Hallo Peter,
... und unter der zwingenden Voraussetzung, dass Deinen Datumswerte in Spalte B aufwärts sortiert sind, dann folgende Formel:
=SVERWEIS(AB2;B11:H500;7)
Die von Hajo zuletzt angegebene Formel, ergibt dagegen nur dann ein Ergebnis , wenn das in AB2 angegebene Datum in B11:B500 auch exakt vorhanden ist.
Zu klären wäre aber noch, welchen Wert Du ermitteln willst, wenn das Datum in AB2 in Spalte B exakt vorhanden ist.
Die von mir bisher angegebene Formel ermittelt dann den Wert, welcher dem Datum exakt zugeordnet ist. Solltest Du aber den Wert ermitteln wollen, der unmittelbar vor dem in AB2 angegebenen liegt (Du hattest geschrieben: ...erste Datum vor dem Datum von AB2... ), dann folgende Formel:
=INDEX(H:H;AGGREGAT(14;6;ZEILE(B11:B500)/(B11:B500&ltAB2)/(B11:B500&gt0);1))
Diese Formel könntest Du auch dann nutzen, wenn die Datumswerte in Spalte B unsortiert sein sollten.
Gruß Werner
.. , - ...
Anzeige
AW: so wie Du es beschrieben hast ...
08.08.2021 19:42:01
Peter
Hallo Werner,
vom Prinzip fast richtig.
Ich füge eine Musterdatei bei: https://www.herber.de/bbs/user/147510.xlsm
In den Zellen J3:J9 habe ich meine Vorstellung dargelegt. Ich hoffe diese ist verständlich.
Gruss
Peter
Du brauchst nur das von-Datum
08.08.2021 22:40:18
WF
Hi,
und Deine null bei 01.08.2021 erfordert ne Zusatzabfrage.
Folgende Arrayformel:
{=(MAX(B:B)>=AA2)*INDEX(H:H;MAX(WENN(B10:B133<=AA2;ZEILE(X10:X133))))}
Salut WF
AW: Du brauchst nur das von-Datum
09.08.2021 06:32:42
Peter
Hallo WF,
vielen Dank. Funktioniert einwandfrei.
Wünsche noch eine schöne Zeit.
Gruss
Peter
AW: Du brauchst nur das von-Datum
09.08.2021 06:55:09
Peter
Hallo WF,
leider doch noch nicht ganz geklärt.
Wenn die Formel geändert wird auf Ende der Spalte 65536 oder weniger dann geht es nicht mehr.
Wo liegt mein Fehler?
Gruss
Peter
Anzeige
Du musst nur die Leerstellen=Nullen ausschließen
09.08.2021 07:53:56
WF
Hi,
={(MAX(B:B)>=AA2)*INDEX(H:H;MAX(WENN((B10:B999>0)*(B10:B999<=AA2);ZEILE(X10:X999))))}
WF
AW: Du musst nur die Leerstellen=Nullen ausschließen
09.08.2021 13:57:15
Peter
Hallo,
danke für Deine Nachricht.
Ich habe die Formel abgeändert: 

"{=(MAX(B10:B65536)>=J2)*INDEX(H:H;MAX(WENN((B10:B999>0)*(B10:B999
Dies war erforderlich, da in der Spalte B zwischen B1 und B9 eine Postleitzahl steht. Jetzt funktioniert alles hervorragend.
Zu Testzwecken habe ich von AA2 auf J2 umgestellt.
Noch eine letzte Frage: Funktioniert dies auch, wenn die Einträge über 999 Zeilen sind?
Gruss
Peter
Anzeige
Du kannst auch bis B37123 oder höher gehen
09.08.2021 14:10:41
WF
kostet aber Rechenzeit
AW: Du kannst auch bis B37123 oder höher gehen
09.08.2021 18:05:16
Peter
Hallo,
ich glaube Du hast mich nicht richtig verstanden.
Ich habe bei Index Max und Zeile B999 eingetragen.
Wenn ich den 1000. Eintrag vornehme funktioniert die Formel dann immer noch?
Gruss
Peter
AW: hierzu ...
11.08.2021 15:51:46
neopa
Hallo Peter,
... noch anzumerken: In B10 Deiner Beispieldatei könnte ja auch anstelle des 01.08 z.B. ein 02.08.19 stehen.
Und damit die Daten auch noch beliebig ergänzt werden können, ohne das die Formel händisch kontrolliert oder gar angepasst werden muss, sollte die Datenliste gemäß Beispieldatei B9:H133 mit der Funktion "Als Tabelle formatiert" in eine intelligente Tabelle gewandelt werden.
Dafür würde dann folgende Formel das angestrebte jeweilige Anfangssaldo ermitteln:
=WENNFEHLER(INDEX(Tabelle1[Bestand];AGGREGAT(15;6;(ZEILE(Tabelle1)-ZEILE(Tabelle1[#Kopfzeilen]))/(Tabelle1[Datum]>=AA2);1)-(ZÄHLENWENN(Tabelle1[Datum];AA2)=0)*(MIN(Tabelle1[Datum])
Die Formel für Ermittlung den jeweiligen letzte Saldowert in einem Datumsbereich zwischen den Daten in AA2 und AB2, würde ähnlich definiert werden können.
Gruß Werner
.. , - ...
Anzeige
AW: vorgegebenes Datum
12.08.2021 08:44:34
Peter
Hallo Werner,
vielen Dank für Deinen Rat.
In diesem konkreten Fall ist dies jedoch nicht erforderlich, da das Datum automatisch vorgegeben ist.
D. h. bei einem Kalenderjahr immer der 1.1. bei einem Schuljahr immer der 1.8 eines Jahres.
Gruss
Peter
AW: in Deiner Beispieldatei ...
12.08.2021 10:53:05
neopa
Hallo Peter,
... war zwar der 01.08.19 angeben, jedoch nicht der 01.08.20. Deshalb hatte ich dies verallgemeinert.
Wenn aber immer der jeweils 1. Tag des auszuwertenden Bereiches angeben ist, wie Du nun schreibst, würde sich die Formel noch etwas vereinfachen.
Gruß Werner
.. , - ...
AW: in Deiner Beispieldatei ...
12.08.2021 10:54:47
Peter
Hallo Werner,
ich werde es testen, besten Dank.
Gruss
Peter
Anzeige
AW: gerne owT
12.08.2021 11:07:14
neopa
Gruß Werner
.. , - ...
AW: gerne owT
12.08.2021 11:27:42
Peter
Hallo Werner,
ich stehe gerade auf dem Schlauch!
Was für eine Datenliste muss ich erstellen und wie bekomme ich die Funktion hierzu eingefügt?
Gruss
Peter
AW: dazu ...
12.08.2021 12:45:28
neopa
Hallo Peter,
... markiere in Deiner eingestellten Beispieldatei die Zellen B9:H133 und aktiviere danach die Tastenkombination [StrG]+[t] und betätige bei gesetzter Option "Tabelle hat Überschriften" den [OK]-Button. Aus der Datenliste wird so eine "intelligente" Tabelle die im Namensmanager automatisch zunächst den Namen: Tabelle1" zugewiesen bekommt. Dafür hatte ich meine Formel definiert.
Gruß Werner
.. , - ...
Anzeige
AW: noch kleiner Fehler
13.08.2021 12:00:00
Peter
Hallo Werner,
ich habe das jetzt soweit hinbekommen.
Wenn ich das erste Datum auf 02.08.2019 ändere wird der richtige Saldo angzeigt. Ebenso bei 2020. Bei 2021 wird richtigerweise "0" angezeigt. Ich habe dann die letzte Zeile um Datum 03.08.2021 ergänzt alles richtig.
Wenn ich jedoch das Datum 01.08.2018 eingebe, dann sollte eigentlich "0" angezeigt werden. Es wird aber der Stand vom 01.08.2019
angezeigt. Ist es ein grosses Problem, dies noch zu vervollständigen?!
Noch etwas, wenn die Tabelle kopiert und eingefügt wird, dann wird ein neuer Tabellen-Name vergeben. Dieser ist weder identisch mit dem Namen den ich vergeben habe noch mit dem in VBA. Ist wohl kein Problem oder?
Du gibts an: ... markiere in Deiner eingestellten Beispieldatei die Zellen B9:H133. Wenn jedoch der Eintrag grösser ist z. B. H135 dann geht das wohl nur in einer neuen Datei, da die Formel für die gesamten Tabellen gilt.
Gruss
Peter
Dann noch eine letzte Frage:
Anzeige
AW: hierzu ...
13.08.2021 15:14:25
neopa
Hallo Peter,
... wenn auch ein Datenbereich ausgewertet werden soll, der vor Deinen in der Datentabelle gelisteten Daten liegt und dafür eine 0 ermittelt werden soll, dann muss in der Formel das * vor MIN(... durch ein / ersetzt werden.
So wie Du Dein Anliegen jetzt weiter beschreibst und ich dies interpretiere, war dies von Dir bisher nicht beschrieben. Demzufolge war meine Formel auch nur für die Auswertung einer (!) formatierten Tabelle aufbereitet. Wenn Du nun aber eine gleiche Datenstruktur in mehr als nur einem Tabellenblatt auswerten willst, erhalten die "intelligenten" Tabellen bei Kopie des Tabellenblattes natürlich in jedem neuen Tabellenblatt einen eineindeutigen anderen Namen und die Formel muss dann dann entsprechend angepasst werden.
Ohne formatierte Tabelle gäbe es allerdings auch mit Hilfe einer "frei" definierten spez. benannten Formel die Möglichkeit, eine Auswertung mit dieser ohne Formelanpassung vorzunehmen, wenn das jeweilige Tabellenblatt kopiert wird Dies setzt allerdings voraus, dass die Tabellenstruktur wirklich starr beibehalten wird und der max auszuwertende Datenbereich darin wenigsten in etwa bekannt ist.
Gruß Werner
.. , - ...
AW: es klappt einwandrei...
13.08.2021 15:51:56
Peter
Hallo Werner,
ich habe jetzt das Prinzip der intelligenten Tabelle soweit verstanden, dass ich damit zurecht komme.
Vielen herzlichen Dank für Deine Hilfe. Es funktioniert einwandfrei.
Gruss
Peter
AW: bitteschön owT
13.08.2021 15:59:57
neopa
Gruß Werner
.. , - ...
AW: noch eine kurze Frage
17.08.2021 09:45:45
Peter
Hallo Werner,
wenn ich in die bestehende Datei die Tabellen umwandle in intelligente Tabellen gibt es dann einen Interessenkonflickt bezüglich der Versionen?
Die Dateien wurden in den letzten zwei Jahren erstellt mit Excel 2013 und wird künftig mit Excel 2016 verwendet. Kann es hier Probleme geben?
Gruss
Peter
AW: diesbzgl. sollte es keinen Konflikt geben owT
17.08.2021 20:09:30
neopa
Gruß Werner
.. , - ...

300 Forumthreads zu ähnlichen Themen

Formel "Text" ROTE Zahl bei Minuswert?
dieter(drummer)

Hi Formelspezialisten,
in Zelle B2 steht dise Formel (funktionert auc h):
=TEXT(I4-D4;"00.0")&" ("&TEXT(I4/D4-1;"0%")&")".
Mein Ziel, dass eine Minuszahl in ROT erscheint. Hab's mit bedingte Formatierung versucht und bekomme es nicht hin.
Gibt es eine Möglichkeit, a...
12.08.2008 10:59:29
Thread-ID: 1001135
7 Beiträge
Anzeige
Letzter Wert einer Spalte (Matrixformel)
{mskro}

Hallo Excelaner,
ich benötige mal wieder eine Hilfestellung.
Es soll der Wert in einer Spalte angezeigt werde, der in der untersten Zeile steht.
Mittels der Recherche habe ich nun folgende MatrixFormel erstellt:
=INDEX(Z1:Z5000;MAX(WENN(Z1:Z5000"";ZEILE(1:5000)));1)
Nur wird...
28.08.2008 09:59:21
Thread-ID: 1005303
9 Beiträge
Array-Formel in VBA-Code umwandeln?
Robert

Hallo,
ich bräuchte bitte eure Hilfe.
Hab folgende Formel...
Selection.FormulaArray = _
=IF(ROW(R[-107]C[-3])>COUNTA('1'!R5C[4]:R200C[4]),"""",INDEX('1'!R5C4:R200C4,SMALL(IF('1'!R5C[4]:R200C[4]<>"""",ROW(R1:R196),""""),ROW(R[-107]C[-3]))))"
Mein Problem ist, dass ic...
26.10.2008 10:56:28
Thread-ID: 1019326
13 Beiträge
Anzeige
Formel in Makro umwandeln?
Josef_T

Guten Abend zusammen.
Habe mal wieder ein Problem, dass ich alleine nicht gelöst bekomme.
Es geht darum, einen Ausdruck zu machen wobei nur Zeilen gedruckt werden sollen, die in C einen Wert haben, der grösser Null ist.
Habe mal ein Muster hochgeladen, weil meine Fragen und Erklärun...
29.11.2008 19:08:00
Thread-ID: 1028896
6 Beiträge
Formel einfügen wenn in A:A Werte stehen
Micha

Hallo zusammen,
ich bastle mal wieder an meinen Münzdateien und komme nicht so richtig weiter.
Mein Problem:
In Spalte A stehen Werte (z.B. 5 Pf), diese möchte ich nun trennen [in B2 soll per VBA die Funktion =Links(A2;1) / FormulaR1C1 = "=LEFT(RC[-53],1) ].
Diese Formel sollte...
14.01.2009 21:28:00
Thread-ID: 1039822
13 Beiträge
Formel wird nicht ausgeführt
Andy

Hallo Leute,
Guten Abend. Ich habe ein ganz großes Problem.
Ich habe eine Exceldatei mit vielen Datensätzen (ca. 8000). In Spalte C habe ich eine Formel, die auf Spalte A und B Bezug nimmt und aus einer Matrix den entsprechenden Wert sucht. Das ganze hat bis jetzt super funktioniert. W...
19.02.2009 16:47:00
Thread-ID: 1052226
8 Beiträge
Datum in Textbox in eine Zahl umwandeln
TCO99

Hallo zusammen,
ich habe folgendes Problem.
In einer Spalte einer Tabelle befinden sich Datumsangaben. Ich filtere diese Liste nach einem Datum. Dieses Datum wird vor der Filterung in eine Textbox eingegeben. Als richtiges Datum fuktioniert die Filterung aber nicht mehr. Da habe ich ei...
26.02.2009 23:31:12
Thread-ID: 1054555
15 Beiträge
Kann Formel nicht in Makro umwandeln. mit bsp xls
Julia


Gestern habe ich hier tolle Hilfe zu einer Berechnung bekommen. Dank der Tipps hab ich die Formeln bekommen
Jetzt hat sich die Ausgangstabelle geändert. Ich hab zwar die Formeln bekomme das aber nicht als Makro hin.
Habe eine Bsp.xls hochgeladen:
https://www.herber.de/bbs/user/...
13.03.2009 11:11:25
Thread-ID: 1059789
4 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
6
3
8
12
2
5
12
7
14
3
3
2
15
5
4
2
2
2
10
4
6
1
4
1
17