Soru Verilen Tarihe eşit ve önceki en büyük tarihteki fiyat

emsey · 2 Mart 2023

Saygıdeğer üstadlar,

A sütunda Tarih, B Sütunu Stok kodu, C Sütunu Alış fiyatı şeklinde ürünlerin alındıkları tarihlere göre bir Alış isimli sayfam var. Ayrıca bunun yanında satış sayfamda aynı şekilde A Sütunu Tarih, B Sütunu Stok Kodu ve C Sütunu Satış fiyatı şeklinde. Ben Satış sayfasındaki D sütununa A sütunundaki Satış tarihine eşit ve küçük olan son alış fiyatını getirmek istiyorum. Ben aşağıdaki şekilde bir formül yazdım son alış fiyatı tarihe küçük ve eşit olarak geliyor ama burada sadece tarihe bakıyor, ikinci bir koşul ile B2'deki Stok koduna göre de bakarak ilgili stok koduna ait son alış fiyatını istiyorum. Yazdığım formül aşağıdaki şekildedir. Beceremedim kaldım öyle yardımcı olursanız çok mutlu olurum. Başka bir formül ile yapılıyorsa oda olabilir.

Satış D2'ye yazdığım formül :
=MAK(EĞER(Alış!A2:A16<=A2;Alış!C2:C16))

Korhan Ayhan · 2 Mart 2023

Koşulu içiçe eğerlerle arttırabilirsiniz...

=MAK(EĞER(Alış!A2:A16<=A2;EĞER(Alış!B2:B16=B2;Alış!C2:C16)))

emsey · 2 Mart 2023

Korhan Ayhan' Alıntı:
Koşulu içiçe eğerlerle arttırabilirsiniz...

=MAK(EĞER(Alış!A2:A16<=A2;EĞER(Alış!B2:B16=B2;Alış!C2:C16)))

Üstad cevabın ve ilgin için çok teşekkür ederim. Yalnız bu formül ile eğer'lere bakmadan her halukarda C2:C16 içindeki en büyük fiyatı getiriyor. Veya A2:A16<=A2 yani tarih koşulunu iptal edip sadece B2:B16=B2 'ye göre çalışıyor da denilebilir. İkisi birlikte olmadı

Korhan Ayhan · 2 Mart 2023

DİZİ formül olarak uygulamanız gerekir. Bunu bildiğinizi düşünerek yazmamıştım.

emsey · 2 Mart 2023

Üstad benimde aklıma ilk gelen o oldu ve tekrar tekrar denedim. Formülü buraya kopyala yapıştır yaparken büyük parantezleri almıyor ama hücrede formül dizi formülü olarak kayıtlı aslında fakat nedense formül gidiyor en büyük sayısı getiriyor
{=MAK(EĞER(Alış!A2:A16<=H2;EĞER(Alış!B2:B16=I2;Alış!C2:C16)))}

Korhan Ayhan · 2 Mart 2023

Bu durumda kontrol sağlamak için örnek dosya paylaşmalısınız.

emsey · 2 Mart 2023

Korhan Ayhan' Alıntı:
Bu durumda kontrol sağlamak için örnek dosya paylaşmalısınız.

Üstad altın üyeliğim bitmiş onu yeniledim şimdi.
Birazdan aktif olduğunda atacağım

emsey · 2 Mart 2023

Soru ile ilgili örnek dosyamı ekledim

Korhan Ayhan · 2 Mart 2023

Deneyiniz.

DİZİ formüldür.

K2;

C++:

=MAK(EĞER(A2:A16<=H2;EĞER(B2:B16=I2;A2:A16)))

L2;

C++:

=MAK(EĞER(A2:A16=K2;EĞER(B2:B16=I2;C2:C16)))

emsey · 2 Mart 2023

Korhan Ayhan' Alıntı:
Deneyiniz.

DİZİ formüldür.

K2;

C++:

=MAK(EĞER(A2:A16<=H2;EĞER(B2:B16=I2;A2:A16)))

L2;

C++:

=MAK(EĞER(A2:A16=K2;EĞER(B2:B16=I2;C2:C16)))

Üstad çok güzel oldu eline emeğine sağlık. Teşekkür ederim

erdalerolayas · 3 Mart 2023

merhaba bu kodu kullan.

Sub sonAlisFiyati()
Dim alisSayfa As Worksheet
Dim satisSayfa As Worksheet
Dim alisSonSatir As Long
Dim satisSonSatir As Long
Dim alisTarih, Tarih As Date
Dim stokKodu, kod As String
Dim sonAlisFiyati As Variant
Dim i As Long
Dim j As Long
Set alisSayfa = ThisWorkbook.Sheets("Alış")
Set satisSayfa = ThisWorkbook.Sheets("Satış")
alisSonSatir = alisSayfa.Cells(alisSayfa.Rows.Count, "A").End(xlUp).Row
satisSonSatir = satisSayfa.Cells(satisSayfa.Rows.Count, "H").End(xlUp).Row
Tarih = alisSayfa.Cells(2, "K").Value
kod = alisSayfa.Cells(2, "I").Value
For i = 2 To satisSonSatir
alisTarih = alisSayfa.Cells(i, "A").Value
stokKodu = alisSayfa.Cells(i, "B").Value
sonAlisFiyati = ""
For j = alisSonSatir To 2 Step -1
If alisSayfa.Cells(j, "A").Value <= Tarih And alisSayfa.Cells(j, "B").Value = kod Then
sonAlisFiyati = alisSayfa.Cells(j, "C").Value
Exit For
End If
Next j

alisSayfa.Cells(2, "L").Value = sonAlisFiyati
Next i
End Sub

emsey · 4 Mart 2023

erdalerolayas' Alıntı:
merhaba bu kodu kullan.

Sub sonAlisFiyati()
Dim alisSayfa As Worksheet
Dim satisSayfa As Worksheet
Dim alisSonSatir As Long
Dim satisSonSatir As Long
Dim alisTarih, Tarih As Date
Dim stokKodu, kod As String
Dim sonAlisFiyati As Variant
Dim i As Long
Dim j As Long
Set alisSayfa = ThisWorkbook.Sheets("Alış")
Set satisSayfa = ThisWorkbook.Sheets("Satış")
alisSonSatir = alisSayfa.Cells(alisSayfa.Rows.Count, "A").End(xlUp).Row
satisSonSatir = satisSayfa.Cells(satisSayfa.Rows.Count, "H").End(xlUp).Row
Tarih = alisSayfa.Cells(2, "K").Value
kod = alisSayfa.Cells(2, "I").Value
For i = 2 To satisSonSatir
alisTarih = alisSayfa.Cells(i, "A").Value
stokKodu = alisSayfa.Cells(i, "B").Value
sonAlisFiyati = ""
For j = alisSonSatir To 2 Step -1
If alisSayfa.Cells(j, "A").Value <= Tarih And alisSayfa.Cells(j, "B").Value = kod Then
sonAlisFiyati = alisSayfa.Cells(j, "C").Value
Exit For
End If
Next j

alisSayfa.Cells(2, "L").Value = sonAlisFiyati
Next i
End Sub

Kod olarak da bilgisayar başına geçince deneyeceğim ama şimdiden emek vermişsiniz kod olarakda çok işime yarar emeğinize sağlık.

emsey · 6 Mart 2023

Değerli üstadlar gerçek zamanlı stok hareketlerinde formül ve vba ikisi de çok ciddi kastı. Bu istediğimi daha hızlı bir şekilde nasıl yapacağım. Bilgisayar çok ciddi kasıyor. 1000 satıra kadar normal ama sonrasında çok zor.

Alış ve Satış iki adet sorgum var logo programının veritabanından aldık. SQL sorguyu yazan arkadaşa istediğimi söyledim yapamadı. Bende Alış ve Satış sorgularını excele çekip orada çalışmak istedim ama olmadı. Bunu hızlı cevap verebilecek alternatif bir şekilde yapamaz mıyız. Yardımcı olursanız çok mutlu olacağım.

emsey · 7 Mart 2023

Korhan Ayhan' Alıntı:
Deneyiniz.

DİZİ formüldür.

K2;

C++:

=MAK(EĞER(A2:A16<=H2;EĞER(B2:B16=I2;A2:A16)))

L2;

C++:

=MAK(EĞER(A2:A16=K2;EĞER(B2:B16=I2;C2:C16)))

erdalerolayas' Alıntı:
merhaba bu kodu kullan.

Sub sonAlisFiyati()
Dim alisSayfa As Worksheet
Dim satisSayfa As Worksheet
Dim alisSonSatir As Long
Dim satisSonSatir As Long
Dim alisTarih, Tarih As Date
Dim stokKodu, kod As String
Dim sonAlisFiyati As Variant
Dim i As Long
Dim j As Long
Set alisSayfa = ThisWorkbook.Sheets("Alış")
Set satisSayfa = ThisWorkbook.Sheets("Satış")
alisSonSatir = alisSayfa.Cells(alisSayfa.Rows.Count, "A").End(xlUp).Row
satisSonSatir = satisSayfa.Cells(satisSayfa.Rows.Count, "H").End(xlUp).Row
Tarih = alisSayfa.Cells(2, "K").Value
kod = alisSayfa.Cells(2, "I").Value
For i = 2 To satisSonSatir
alisTarih = alisSayfa.Cells(i, "A").Value
stokKodu = alisSayfa.Cells(i, "B").Value
sonAlisFiyati = ""
For j = alisSonSatir To 2 Step -1
If alisSayfa.Cells(j, "A").Value <= Tarih And alisSayfa.Cells(j, "B").Value = kod Then
sonAlisFiyati = alisSayfa.Cells(j, "C").Value
Exit For
End If
Next j

alisSayfa.Cells(2, "L").Value = sonAlisFiyati
Next i
End Sub

Bana yardımcı olacak bir üstad yok mu acaba? Formül çok fazla kasıyor hesaplama çok uzun sürüyor. VBA kodu da çok kastı.
Korhan hocam Erdal hocam yardımcı olabileceğiniz bir imkan varsa çok mutlu olacağım.

Korhan Ayhan · 8 Mart 2023

Deneyiniz.

C++:

Option Explicit

Sub Get_Last_Date_And_Price()
    Dim My_Connection As Object, My_Query As String, My_Recordset As Object
   
    Set My_Connection = CreateObject("AdoDB.Connection")
   
    My_Connection.Open "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" & _
    ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;Hdr=Yes"""
   
    My_Query = "Select Last(Tarih),Last([Alış Fiyatı]) From [Alış$] " & _
               "Where Tarih <= " & CLng(Range("H2")) & " And [Stok Kodu] = '" & Range("I2") & "'"
   
    Set My_Recordset = My_Connection.Execute(My_Query)
   
    Range("K2").CopyFromRecordset My_Recordset
   
    If My_Recordset.State <> 0 Then My_Recordset.Close
    If My_Connection.State <> 0 Then My_Connection.Close
   
    Set My_Recordset = Nothing
    Set My_Connection = Nothing
   
    MsgBox "İşleminiz tamamlanmıştır.", vbInformation
End Sub

emsey · 8 Mart 2023

Korhan Ayhan' Alıntı:

Deneyiniz.

C++:

Option Explicit

Sub Get_Last_Date_And_Price()
    Dim My_Connection As Object, My_Query As String, My_Recordset As Object
  
    Set My_Connection = CreateObject("AdoDB.Connection")
  
    My_Connection.Open "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" & _
    ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;Hdr=Yes"""
  
    My_Query = "Select Last(Tarih),Last([Alış Fiyatı]) From [Alış$] " & _
               "Where Tarih <= " & CLng(Range("H2")) & " And [Stok Kodu] = '" & Range("I2") & "'"
  
    Set My_Recordset = My_Connection.Execute(My_Query)
  
    Range("K2").CopyFromRecordset My_Recordset
  
    If My_Recordset.State <> 0 Then My_Recordset.Close
    If My_Connection.State <> 0 Then My_Connection.Close
  
    Set My_Recordset = Nothing
    Set My_Connection = Nothing
  
    MsgBox "İşleminiz tamamlanmıştır.", vbInformation
End Sub

Üstad sorgu ile harika aslında ama sıkıntı şu Range("H2") ile Range("I2") bölümünde ben örnek dosyada sadece bir satır ürün ekledim. orada tüm satışlar var. Yani veri çok tek satır değildi orayı yanlış anlatmış olabilirim. Önceki formüllerin kasmasının sebebi çok satır olmasındaydı ancak sorgu mükemmel olmuş ve çok stabil çalışıyor eline emeğine sağlık, sorgu ile müthiş olmuş yalnız where kısmında H2,H3,H4,H5 ve I2,I3,I4,I5 .... şeklinde dinamik olmalı sadece tek bir satıra değilde tüm satırlara veri almam lazım. Alış ve satış aslında bende iki farklı sayfada bunlar open ile query açıp yeni bir sayfada satış satırlarının her birinin yanına alış sayfasından yukarıdaki sorgudaki query'nin tek satıra getirdiği verileri alamaz mıyız ?

Korhan Ayhan · 8 Mart 2023

Kaç satır veriniz var..

dost · 8 Mart 2023

Merhaba,

Sayın @Korhan Ayhan soruyu kod ile çözecektir.

Eğer formül ile çözmek isterseniz aşağıdaki formülü deneyiniz.

C++:

=MAX(($A$2:$A$16=MAX(IF(($B$2:$B$16=I2)*($A$2:$A$16<=H2);$A$2:$A$16)))*($B$2:$B$16=I2)*($C$2:$C$16))

emsey · 8 Mart 2023

dost' Alıntı:
Merhaba,

Sayın @Korhan Ayhan soruyu kod ile çözecektir.

Eğer formül ile çözmek isterseniz aşağıdaki formülü deneyiniz.

C++:

=MAX(($A$2:$A$16=MAX(IF(($B$2:$B$16=I2)*($A$2:$A$16<=H2);$A$2:$A$16)))*($B$2:$B$16=I2)*($C$2:$C$16))

alış ve satış satırları fazla olduğu için formül hesaplaması çok uzun sürüyor. Hiç alakasızmış ama ben her ihtimale karşı formülden sonra aşağıdaki verdiğim kod ile formülü filldown yapıp değerlere çevirdim. Önceki tüm satırlar Formül olarak kaldığı için önceki her satırda hesaplama uzun sürüyor diye düşündüm ancak fark eden olmadı. Yine hesaplama uzun sürüyor.

Kod:

  Range("M2").FormulaArray = "=MAX(($A$2:$A$160000=MAX(IF(($B$2:$B$160000=I2)*($A$2:$A$160000<=H2),$A$2:$A$160000)))*($B$2:$B$160000=I2)*($C$2:$C$160000))"
sonsatir = Cells(Rows.Count, "H").End(xlUp).Row
        Range("m2:M" & sonsatir).Select
        Selection.FillDown
               Range("m2:M" & sonsatir).Value = Range("m2:M" & sonsatir).Value

Korhan Ayhan · 8 Mart 2023

Çoklu veri için örnek dosya paylaşırsanız sorguyu revize edip kontrol edebilirim.

Soru Verilen Tarihe eşit ve önceki en büyük tarihteki fiyat

emsey

Korhan Ayhan

Administrator

emsey

Korhan Ayhan

Administrator

emsey

Korhan Ayhan

Administrator

emsey

emsey

Ekli dosyalar

Korhan Ayhan

Administrator

emsey

erdalerolayas

emsey

emsey

emsey

Korhan Ayhan

Administrator

emsey

Korhan Ayhan

Administrator

dost

emsey

Korhan Ayhan

Administrator