Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1328to1332
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
Inhaltsverzeichnis

Formeln automatisch durch Wert ersetzen

Formeln automatisch durch Wert ersetzen
30.08.2013 08:42:54
Ralf
Guten Morgen Excel - Experten,
ich habe eine Tabelle, in der in Spalte A
untereinander die "Datümer" stehen, z.B.:
12.08.2013
13.08.2013...
In Spalte E stehen sverweis - Formeln.
Nun möchte ich gerne in E 1 ein Anfangsdatum
eingeben z.B: 12.08.2013 und in E 2 ein
Enddatum z.B: 16.08.2013 und nun wäre es gaaanz
doll, wenn mir Excel in Spalte E nur die Formeln
auf Wert setzt, die in Spalte A zu dem oben angegebenen
Zeitraum (12.08.2013 bis 16.08.2013 )gehören.
Ob so etwas geht, weiß ich nicht, wäre aber für
jeden Tipp echt dankbar !!!
viele :-) Grüße
Ralf

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formeln automatisch durch Wert ersetzen
30.08.2013 09:41:34
JACKD
Hallo Ralf
meinst du es in etwa so?



Tabelle1
 ABCDEF
101.01.20131  Startdatum02.01.2013 
202.01.20132  Enddatum08.01.2013 
303.01.20133  Suchkriterium06.01.2013 
404.01.20134  Sverweis 6,0  
505.01.20135       
606.01.20136       
707.01.20137       
808.01.20138       
909.01.20139       
1010.01.201310       
1111.01.201311       
1212.01.201312       
1313.01.201313       
1414.01.201314       
1515.01.201315       
1616.01.201316       
1717.01.201317       
1818.01.201318       
1919.01.201319       
2020.01.201320       
2121.01.201321       
2222.01.201322       
2323.01.201323       
2424.01.201324       
2525.01.201325       
2626.01.201326       
2727.01.201327       
2828.01.201328       
2929.01.201329       
3030.01.201330       

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
E4=WENNFEHLER(SVERWEIS(E3;INDIREKT("A"&VERGLEICH(E1;A:A) &":B"&VERGLEICH(E2;A:A) ) ;2;FALSCH) ;"Gesuchter Wert nicht vorhanden, oder ausserhalb des Bereichs")   =IFERROR(VLOOKUP(R[-1]C,INDIRECT("A"&MATCH(R[-3]C,C[-4]) &":B"&MATCH(R[-2]C,C[-4]) ) ,2,FALSE) ,"Gesuchter Wert nicht vorhanden, oder ausserhalb des Bereichs")

XHTML Tabelle für Darstellung in Foren,
einschl. Bedingter Formatierung ab Version 2007
XHTML-Version 11.26



Grüße

Anzeige
AW: Formeln automatisch durch Wert ersetzen
30.08.2013 10:07:54
Ralf
Hallo JackD,
vielen DANK für Deine Antwort. Die Formel muß ich mir
noch genauer anschauen, so schnell versteh ich das nicht :-)
Ich vergas aber wohl zu erwähnen, daß die sverweis - Formeln
in Spalte E sich die ganze Spalte von E 3 bis
E 20.000 durchziehen und parallel dazu die "Datümer"
in Spalte A stehen. Nun war es mein Ziel, eine Zeitspanne
einzugeben ( E 1 und E 2), die Excel dann in
Spalte A sucht, parallel dazu nach rechts in Spalte E
geht und NUR DORT die sverweis - Formeln mit dem
vorhandenen Wert überschreibt.
viele :-) Grüße
Ralf

Anzeige
AW: Formeln automatisch durch Wert ersetzen
30.08.2013 10:19:29
JACKD
Hallo Ralf
Also "überschreiben" kann man die Formeln nicht mit einer Formel. Das könntest du maximal über ein Makro Lösen (was wieder den Nachteil hat, dass du die Sverweise dann wieder eintragen musst)
Vielleicht hab ich deine Formel auch falsch verstanden
Wenn du quasi die Auswahl haben willst
1. Zeile ist im angegebenen Datumsbereich dann ändere den Wert zu irgendwas
2. Zeile ist nicht im angegebenen Datumsbereich dann lasse den Sverweis



Tabelle1
 ABCDEF
1      Startdatum02.01.2013Mein Wert :
2      Enddatum09.01.2013 
3Datum Ausgabewert  Formel   
401.01.20131  2   
502.01.20132  Im Datenbereich mein Wert ist    
603.01.20133  Im Datenbereich mein Wert ist    
704.01.20134  Im Datenbereich mein Wert ist    
805.01.20135  Im Datenbereich mein Wert ist    
906.01.20136  Im Datenbereich mein Wert ist    
1007.01.20137  Im Datenbereich mein Wert ist    
1108.01.20138  Im Datenbereich mein Wert ist    
1209.01.20139  Im Datenbereich mein Wert ist    
1310.01.201310  11   
1411.01.201311  12   
1512.01.201312  13   
1613.01.201313  14   
1714.01.201314  15   
1815.01.201315  16   
1916.01.201316  17   
2017.01.201317  18   
2118.01.201318  19   
2219.01.201319  20   
2320.01.201320  21   
2421.01.201321  22   
2522.01.201322  23   
2623.01.201323  24   
2724.01.201324  25   
2825.01.201325  26   
2926.01.201326  27   
3027.01.201327  28   
3128.01.201328  29   
3229.01.201329  30   

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
D4:D32=WENN(UND(A4>=$E$1;A4<=$E$2) ;"Im Datenbereich mein Wert ist " & G1;SVERWEIS(A5;A:B;2;FALSCH) )   =IF(AND(RC[-3]>=R1C5,RC[-3]<=R2C5) ,"Im Datenbereich mein Wert ist " & R[-3]C[3],VLOOKUP(R[1]C[-3],C[-3]:C[-2],2,FALSE) )

XHTML Tabelle für Darstellung in Foren,
einschl. Bedingter Formatierung ab Version 2007
XHTML-Version 11.26



sähe dann so aus.
Die Werte sind nicht besonders :-D
und auch der Sverweis ist weitestgehend sinnfrei aber da kommt ja eh deiner rein =)
Grüße

Anzeige
Korrektur ..der kleine Fehlerteufel
30.08.2013 10:21:15
JACKD



Tabelle1
 ABCDEFG
1      Startdatum02.01.2013Mein Wert :666
2      Enddatum09.01.2013   
3Datum Ausgabewert  Formel     
401.01.20131  2     
502.01.20132  Im Datenbereich mein Wert ist 666     
603.01.20133  Im Datenbereich mein Wert ist 666     
704.01.20134  Im Datenbereich mein Wert ist 666     
805.01.20135  Im Datenbereich mein Wert ist 666     
906.01.20136  Im Datenbereich mein Wert ist 666     
1007.01.20137  Im Datenbereich mein Wert ist 666     
1108.01.20138  Im Datenbereich mein Wert ist 666     
1209.01.20139  Im Datenbereich mein Wert ist 666     
1310.01.201310  11     
1411.01.201311  12     
1512.01.201312  13     
1613.01.201313  14     
1714.01.201314  15     
1815.01.201315  16     
1916.01.201316  17     
2017.01.201317  18     
2118.01.201318  19     
2219.01.201319  20     
2320.01.201320  21     
2421.01.201321  22     
2522.01.201322  23     
2623.01.201323  24     
2724.01.201324  25     
2825.01.201325  26     
2926.01.201326  27     
3027.01.201327  28     
3128.01.201328  29     
3229.01.201329  30     

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
D4:D32=WENN(UND(A4>=$E$1;A4<=$E$2) ;"Im Datenbereich mein Wert ist " & $G$1;SVERWEIS(A5;A:B;2;FALSCH) )   =IF(AND(RC[-3]>=R1C5,RC[-3]<=R2C5) ,"Im Datenbereich mein Wert ist " & R1C7,VLOOKUP(R[1]C[-3],C[-3]:C[-2],2,FALSE) )

XHTML Tabelle für Darstellung in Foren,
einschl. Bedingter Formatierung ab Version 2007
XHTML-Version 11.26



Anzeige
Danke JackD !
30.08.2013 10:44:51
Ralf
Hallo JackD,
vielen Dank für Deine Mühe !
Ich habe dank Dir wieder eine ganz
neue Syntax kennengelernt, die ich bestimmt
bald anwenden kann !
viele :-) Grüße
Ralf

AW: Formeln automatisch durch Wert ersetzen
30.08.2013 10:49:26
fcs
Hallo Ralf,
hier entsprechende Makros, Startmakro und eigentliche Ersetzungsprozedur
Gruß
Franz
'Beispiel zum Starten des Ersetzungsmakros
Public Sub Formel_zu_Wert_EA()
If MsgBox("In Spalte E für Datumsbereich in ""E1"" und ""E2"" die Formeln " _
& "durch Werte ersetzen", _
vbQuestion + vbOKCancel, "F O R M E L N   E R S E T Z E N") = vbOK Then
With ActiveSheet
Call fncFormel_zu_Wert(SpalteFormel:=5, SpalteVergleich:=1, _
varMin:=.Range("E1"), varMax:=.Range("E2"), Zeile_1:=4)
End With
End If
End Sub
'Code in einem allgemeinen Modul
Public Function fncFormel_zu_Wert(SpalteFormel As Long, SpalteVergleich As Long, _
varMin, varMax, Optional Zeile_1 As Long = 2, _
Optional wks As Worksheet) As Boolean
' Formeln in einer Spalte durch Werte ersetzen, abhängig von Wertebereich _
in anderer Spalte
' SpalteFormel    = Spalte mit Formel
' SpalteVergleich = Spalte mit Vergleichswert
' varMin          = untere Grenze für Vergleich
' varMax          = obere Grenze für Vergleich
' Zeile_1         = Zeile ab der Vergleich starten soll - Vorgabe = 2
' wks             = Tabellenblatt in dem Ersetz werden soll - Vorgabe = aktives Blatt
Dim varWert
Dim lngZeile As Long, StatusCalc As Long
On Error GoTo Fehler
If varMin > varMax Then
MsgBox "Der obere Vergleichswert ist kleiner als der untere Vergleichswert!", _
vbInformation + vbOKOnly, "Makro: prcFormel_zu_Wert"
fncFormel_zu_Wert = False
Exit Function
End If
If wks Is Nothing Then Set wks = ActiveSheet
With wks
.Columns(SpalteFormel).Calculate 'Spalte mit Formeln neu berechnen
'Makrobremsen lösen
With Application
.ScreenUpdating = False
.EnableEvents = False
StatusCalc = .Calculation
.Calculation = xlCalculationManual
End With
'Zeilen in Spalte mit Formeln abarbeiten
For lngZeile = Zeile_1 To .Cells(.Rows.Count, SpalteFormel).End(xlUp).Row
varWert = .Cells(lngZeile, SpalteVergleich).Value
If varWert >= varMin And varWert 

Anzeige
SUPERTOLL Franz !!!
30.08.2013 11:11:23
Ralf
Hallo Franz,
um bei Robin Hood zu bleiben, Du hast den
Pfeil von Erich g e s p a l t e n :-) !!! - - -
sogar mit einer Zwischenmeldung - WOW !!!
Ganz große KLASSE --- Eine Superhilfe !!!
vielen vielen Dank
und viele :-) Grüße aus dem Teutoburger Wald
Ralf

AW: Formeln automatisch durch Wert ersetzen
30.08.2013 10:17:38
Erich
Hi Ralf,
IMHO spielt es überhaupt keine Rolle, was für Formeln in Spalte E stehen.
Da in E1:E2 Anfangs- und Enddatum stehen (also wohl keine Formeln ;-)), geh ich mal davon aus,
dass die Daten in Zeile 3 beginnen.
Probier mal

Option Explicit
Sub ZeitraumWert()
Dim arQ, zv As Long, zb As Long
zv = Cells(Rows.Count, 1).End(xlUp).Row
zb = Cells(Rows.Count, 5).End(xlUp).Row
arQ = Cells(1, 1).Resize(1 + Application.Min(zv, zb))
If arQ(3, 1) > Cells(2, 5) Or arQ(UBound(arQ) - 1, 1) = Cells(1, 5) Then Exit For
Next zv
For zb = zv To UBound(arQ)
If arQ(zb, 1) > Cells(2, 5) Then Exit For
Next zb
Cells(zv, 5).Resize(zb - zv) = Cells(zv, 5).Resize(zb - zv).Value
End Sub
(2958465 ist der 31.12.9999 im Standardformat...)
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
SUPERTOLL Erich !!!
30.08.2013 10:39:59
Ralf
Hallo Erich,
beim Bogenschießen würde man sagen: MITTEN ins
SCHWARZE !!! ( mit einem Sonderpunkt für die
Beschreibung der Flugbahn :-) !!! )
Echt Super Erich !!!
vielen Dank dafür
und viele :-) Grüße aus dem Teutoburger Wald
( in dem ich nun dank Dir nicht mehr stehe :-)
Ralf

304 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige