Soru Listeden Veri Alarak Liste Oluşturma

yildirim52 · 14 Ocak 2022

Merhabalar,

ÜRETİLEN MAMÜL	Un	Şeker	Yağ	Badem Unu	Yumurta Akı	Kabartma Tozu
Un Kurabiyesi	20	12	5			0,5
Acıbadem Kurabiyesi		35		25	12

Acıbadem Kurabiyesi	Şeker	35	Un Kurabiyesi	Un	20
	Badem Unu	25		Şeker	12
	Yumurta Akı	12		Yağ	5
				Kabartma Tozu	0,5

1. tablo verilerin alınacağı tablo, örneğin "üretim" isimli çalışma sayfası. Her bir üretim için başka bir "rapor" çalışma sayfasından üretim raporu alınıyor. Normalde tabloda tarih, üretilen miktar, parti numarası vb. veriler de var. Biz bu verileri en son üretilen örneğin "acıbadem kurabiyesi" ürünü üzerinden rapora kodlar yardımıyla çekiyoruz. Kullanılan hammaddeleri de 2. tablodaki gibi alt alta sıralamak istiyoruz. Şimdilik bunları elle girmekteyiz.

Şöyle düşünün ki "rapor" çalışma sayfasındaki veriler saklanmıyor. Sadece en son "X" ürünü üretiminin raporu çıkarılıyor. Ama "üretim" çalışma sayfasındaki veriler saklanıyor. Bu veriler üzerinden aylık üretim, yıllık üretim, yıllık hammadde kullanımı, maliyet vs. bir sürü başka çalışma sayfalarına veri aktarılıyor.

Aynı bilgileri hem "üretim" hem "rapor" sayfalarına girmemek için bir çare bulamadık.

2. tablonun A1'inde seçili olan mamülün 2. ve 3. sütündaki gibi alt alta gelmesini sağlamamız gerekiyor. 2. tablonun yan tarafına A1'de Un Kurabiyesi seçili olduğu durumu da koydum örnek olarak.

Sorun şu ki 1. tabloda sağa doğru un, şeker, yağ... diye uzayan tabloda 20-30 kadar çeşit hammade var ve üretilen mamül çeşidine göre 3-7 çeşidini kullanıyoruz. Yani boş olan hammadde sütunlarını tespit edip onları atlaması gerekiyor. Ve alt alta sıralaması gerekiyor.

Umarım anlatabilmişimdir. Teşekkürler.

ÖmerFaruk · 14 Ocak 2022

Dosyanızı paylaşmanızı rica edeceğim.
altın üye değilseniz harici link ekleyebilirsiniz.

yildirim52 · 15 Ocak 2022

Örnek Dosya

İçindeki firma ile ilgili bilgileri gizleyerek dosyanın tamamını ekledim. Umarım birilerine faydası olur.
Not: Sadece 2022 üretimlerini kapsadığı için içinde çok az veri var.

Örneğin "YMamülÜrtRaporu" adlı çalışma sayfasının "AC1" hücresinden (ki bu hücre veri doğrulama hücresidir) "Yarımamül4" seçtiğimde "KULLANILAN MALZEME" kısmına "Yarımamül" çalışma sayfasının en son "Yarımamül4" satırındaki verileri getirmeye çalışıyorum. Aynı satırdaki verilerden tarih, miktar, gerçekleşen maliyet, işçilik vs. verilerini çekebiliyorum. Kullanılan malzeme kısmının çok fazla çeşit içeriyor olması ve birçoğunun boş olması nedeniyle geri kalanını almayı başaramadık.

Bahsettiğim üretimde Hammadde1, Hammadde2, Hammadde4 ve Hammadde5 (fire boş) kullanılmış görünüyor. Vereceği çıktı aşağıdaki tablodaki gibi olmalıdır.

ADI	PARTİ NO	MİKTAR	MİKTAR
Hammadde1		4,7	0,05
Hammadde2		3,26	0,04
Hammadde4		17,4	0,2
Hammadde5		9,04

Eğer başarabilirsek aynı uygulamayı "Mamül" ve "MamülÜrtRaporu" sayfalarına da uyarlayacağım. Teşekkürler..

ÖmerFaruk · 16 Ocak 2022

Dosyanız oldukça karışık. Formülle de yapmakta zorlandım. Kodla daha kolay ve daha sade sade oluyor.
Aşağıdaki kodu YMamülÜrtRaporu sayfanızın kod sayfasına yapıştırın.
AC1 hücresindeki yarımamül adını değişince kodlar çalışacaktır.

C++:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("AC1")) Is Nothing Then: [AK1] = [AK1] + 1: Exit Sub
    Application.EnableEvents = False
    Call YarMamRaporDoldur
    Application.EnableEvents = True

End Sub
Sub YarMamRaporDoldur()
    Dim Bak As Range, BulSon As Range, Tarih As Date, i As Integer, xRow As Integer
    With Worksheets("Yarımamül")
        For Each Bak In .Range("A2:A" & WorksheetFunction.Max(2, .Range("A" & Rows.Count).End(3).Row))
            If Bak.Value = Range("AC1").Value Then
                If IsDate(Bak.Offset(, 1)) Then
                    If Tarih <= Bak.Offset(, 1) Then Tarih = Bak.Offset(, 1): Set BulSon = Bak
                End If
            End If
        Next Bak
        Range("S4:AG22").ClearContents
        If BulSon Is Nothing Then
            MsgBox Range("AC1") & " yarımamülüne ait kayıt bulunamadı"
            Exit Sub
        End If
        ' Arzu ederseniz bu satırlardaki formülleri iptal edip kodla halledebilirsiniz.
        'Range("A3") = BulSon.Offset(0, 1)
        'Range("A8") = BulSon.Offset(0, 2)
        'Range("H14") = BulSon.Offset(0, 4)
        xRow = 4
        For i = 5 To .Range("A1").End(xlToRight).Column
            If Left(.Range("A1").Offset(, i), 8) = "Hammadde" And .Range("A1").Offset(, i + 1) = "Fire" And BulSon.Offset(, i) > 0 Then
                Range("S" & xRow) = .Range("A1").Offset(, i)
                Range("AD" & xRow) = BulSon.Offset(, i)
                Range("AG" & xRow) = BulSon.Offset(, i + 1)
                xRow = xRow + 1
                If xRow > 22 Then Exit Sub
            End If
        Next i
    End With
End Sub

yildirim52 · 17 Ocak 2022

Örnek dosya üzerinde denedim, bir şekilde çalıştırdım. Yalnız daha önce makrolarla çalışmamıştım ve bu kodların asıl dosyaya nasıl uyarlanacağını çözemedim. Bunu excel formülleri ile çözmenin bir yolu yok mudur?
Karmaşık olmayan yeni bir dosya ekledim.

Ornek.xlsx dosyasını indir - download

Ornek.xlsx dosyasını indir, download. Dosya.tc .Dosya Upload. Dosya Paylaş. Dosya Yükle

s6.dosya.tc

Sadece 5 satır veri ekledim. Verilerin hemen altında A11'de seçilen ürünü B11 : D14 aralığında listeleyecek şekilde kurguladım.
Ya da yukarıdaki VB kodlarının mantığını birşekilde öğrenmem lazım. Biraz uğraşmayı denedim. Sürekli debug almaktayım.
İlginiz için teşekkürler.

Erdem Akdemir · 17 Ocak 2022

Aşağıdaki dizi formülünü kullanabilirsiniz.
Verilerinizin A1 hücresinden başladığı düşünülerek hazırlanmıştır.

I1 hücresinde Acıbadem Kurabiyesi yazıyor gibi J1 hücresine aşağıdaki formülü kopyalayın. CTRL+SHIFT+ENTER ile kapatmayı unutmayın.

Kod:

=EĞERHATA(DOLAYLI(METNEÇEVİR(KÜÇÜK(EĞER(KAYDIR($B$2;KAÇINCI($I$1;$A$2:$A$12;0)-1;;;6)>0;EĞER((SÜTUN()-SÜTUN($I$1))=1;100;KAÇINCI($I$1;$A$1:$A$12;0)*100)+SÜTUN($B1:$G1));SATIR(A1));"R0C00"););"")

Erdem Akdemir · 17 Ocak 2022

İlk eklediğiniz dosya ile sonradan eklediğiniz dosyanın yapısı tamamen birbirinden farklı, boşuna zaman kaybı.

yildirim52 · 17 Ocak 2022

ÖmerFaruk' Alıntı:

Dosyanız oldukça karışık. Formülle de yapmakta zorlandım. Kodla daha kolay ve daha sade sade oluyor.
Aşağıdaki kodu YMamülÜrtRaporu sayfanızın kod sayfasına yapıştırın.
AC1 hücresindeki yarımamül adını değişince kodlar çalışacaktır.

C++:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("AC1")) Is Nothing Then: [AK1] = [AK1] + 1: Exit Sub
    Application.EnableEvents = False
    Call YarMamRaporDoldur
    Application.EnableEvents = True

End Sub
Sub YarMamRaporDoldur()
    Dim Bak As Range, BulSon As Range, Tarih As Date, i As Integer, xRow As Integer
    With Worksheets("Yarımamül")
        For Each Bak In .Range("A2:A" & WorksheetFunction.Max(2, .Range("A" & Rows.Count).End(3).Row))
            If Bak.Value = Range("AC1").Value Then
                If IsDate(Bak.Offset(, 1)) Then
                    If Tarih <= Bak.Offset(, 1) Then Tarih = Bak.Offset(, 1): Set BulSon = Bak
                End If
            End If
        Next Bak
        Range("S4:AG22").ClearContents
        If BulSon Is Nothing Then
            MsgBox Range("AC1") & " yarımamülüne ait kayıt bulunamadı"
            Exit Sub
        End If
        ' Arzu ederseniz bu satırlardaki formülleri iptal edip kodla halledebilirsiniz.
        'Range("A3") = BulSon.Offset(0, 1)
        'Range("A8") = BulSon.Offset(0, 2)
        'Range("H14") = BulSon.Offset(0, 4)
        xRow = 4
        For i = 5 To .Range("A1").End(xlToRight).Column
            If Left(.Range("A1").Offset(, i), 8) = "Hammadde" And .Range("A1").Offset(, i + 1) = "Fire" And BulSon.Offset(, i) > 0 Then
                Range("S" & xRow) = .Range("A1").Offset(, i)
                Range("AD" & xRow) = BulSon.Offset(, i)
                Range("AG" & xRow) = BulSon.Offset(, i + 1)
                xRow = xRow + 1
                If xRow > 22 Then Exit Sub
            End If
        Next i
    End With
End Sub

Benim orjinal dosyamda AC1'in yer aldığı satırın üstünde 4 satır daha var. Seçimin yapıldığı kısım AC5 hücresinde yer alıyor. Bir yolunu bulup kodları çalıştıramadım. Ayrıca merak ettim, kodlardaki [AK1] = [AK1] + 1 'deki AK1 bir hücreye mi denk geliyor. Gösterim şekli farklı ve boş bir hücre AK1.
Gerekirse bu vesileyle VB kodlarına biryerden başlayacağım.
Teşekkürler

ÖmerFaruk · 17 Ocak 2022

Bu satırı düzeltmeden yollamışım. İkinci satırdaki gibi yapın.

Kod:

'Yanlış satır
If Intersect(Target, Range("AC1")) Is Nothing Then: [AK1] = [AK1] + 1: Exit Sub
'Doğrusu
If Intersect(Target, Range("AC1")) Is Nothing Then Exit Sub

AC1 yerine AC5 kullanıyorsanız. Kodlarda AC1 yazan yerleri AC5 yapabilrisiniz.

yildirim52 · 18 Ocak 2022

Şuan orjinal dosyada yine çalıştıramıyorum.

If Left(.Range("A1").Offset(, i), 8) = "Hammadde" And .Range("A1").Offset(, i + 1) = "Fire" And BulSon.Offset(, i) > 0 Then

satırındaki "Hammadde" kodu işlem yapmıyor. Çünkü benim dosyamda hammadderin isimlerinde hammadde geçmiyor. Firmaya özgü bilgiler içerdiği için hammade isimlerini Hammadde 1-2 vs diye değiştirmiştim.

İkinci bir problem daha var.

"YMamülÜrtRaporu" çalışma sayfasında "F8" ve "A28" hücresinde olduğu gibi birim de yazdırmamız gerekiyor. "&" ekleyerek yapmıştım.

F8 hücresi formülü

=KAYDIR(Yarımamül!$A$1;TOPLA.ÇARPIM(MAK(--($AC$1=Yarımamül[[#Tümü];[Sütun18]])*SATIR(Yarımamül[[#Tümü];[Sütun18]])))-1;3)&" "&KAYDIR(Fiyat!I1;TOPLA.ÇARPIM(MAK(--($AC$1=Fiyat[[#Tümü];[Sütun3]])*SATIR(Fiyat[[#Tümü];[Sütun3]])))-1;2)

Size ekte yeniden düzenlediğim dosyayı gönderiyorum. VB kodlarını ekledim. Hammadde isimlerini rastgele değiştirdim. Ve herbir hammaddenin yazılması gereken birimini ekledim. Birimler fiyat sayfasının C sütununda.

Örnek Dosya

Teşekkürler

ÖmerFaruk · 21 Ocak 2022

Kodları aşağıdakiyle değiştirin.

C++:

Sub YarMamRaporDoldur()
    Dim Bak As Range, BulSon As Range, Tarih As Date, i As Integer, xRow As Integer
    Dim BulBirim As Range
    With Worksheets("Yarımamül")
        For Each Bak In .Range("A2:A" & WorksheetFunction.Max(2, .Range("A" & Rows.Count).End(3).Row))
            If Bak.Value = Range("AC1").Value Then
                If IsDate(Bak.Offset(, 1)) Then
                    If Tarih <= Bak.Offset(, 1) Then Tarih = Bak.Offset(, 1): Set BulSon = Bak
                End If
            End If
        Next Bak
        Range("S4:AG22").ClearContents
        If BulSon Is Nothing Then
            MsgBox Range("AC1") & " yarımamülüne ait kayıt bulunamadı"
            Exit Sub
        End If
        ' Arzu ederseniz bu satırlardaki formülleri iptal edip kodla halledebilirsiniz.
        'Range("A3") = BulSon.Offset(0, 1)
        'Range("A8") = BulSon.Offset(0, 2)
        'Range("H14") = BulSon.Offset(0, 4)
        
        xRow = 4
        For i = 6 To .Range("A1").End(xlToRight).Column Step 2
            If InStr(1, .Cells(1, i), "Maliyeti") > 0 Then Exit For
            If BulSon.Offset(, i - 1) > 0 Then
                Range("S" & xRow) = .Range("A1").Offset(, i - 1)
                Range("AD" & xRow) = BulSon.Offset(, i - 1)
                Range("AG" & xRow) = BulSon.Offset(, i)
                Set BulBirim = Worksheets("Fiyat").Range("A:D").Find(.Range("A1").Offset(, i - 1), , xlValues, xlWhole)
                If Not BulBirim Is Nothing Then
                x1 = BulBirim.Column
                x2 = BulBirim.Row
                Range("AD" & xRow) = Range("AD" & xRow) & " " & BulBirim.Offset(, 2)
                Range("AG" & xRow) = Range("AG" & xRow) & " " & BulBirim.Offset(, 2)
                End If
                xRow = xRow + 1
                If xRow > 22 Then Exit Sub
            End If
        Next i
    End With
End Sub

yildirim52 · 21 Ocak 2022

Kodları çalıştırdım. Daha sonra kendi dosyama uyarladım. "YMamülÜrtRaporu" çalışma sayfam sorunsuz çalışıyor. Aynı kodları daha sonra "MamülÜrtRaporu" sayfasında kullandım. Orada da çalıştırdım.

Yalnız iki sorun kaldı çözemediğimiz.
Birincisi çok önemli olmamakla beraber..
Firesi olmayan hammaddelerin de birimini getiriyor. Normalde boş kalması gerekiyordu.

İkincisi ve önemli olan..
"MamülÜrtRaporu" çalışma sayfasında "YMamülÜrtRaporu" çalışma sayfasından farklı olarak veriler iki ayrı alana alınıyor. Hammadde ayrı, ambalaj ayrı alana giriliyor. Sayfaya iki ayrı "sub end sub" açarak iki fonksiyon tanımladım ve "Z1" seçildiğinde bu iki fonksiyonun çağrılmasını sağladım. Ama veriler aynı satırda sıralı olduğundan her iki alana da aynı verileri çekti.

Bunun bir çözümü var mıdır?

Yaptığım değişikliklerin ekli olduğu dosya

İlgileriniz için teşekkür ederim.

ÖmerFaruk · 21 Ocak 2022

Firesi olmayan hammaddelerin de birimini getiriyor. Normalde boş kalması gerekiyordu.
İlgili iki satırın başına IF sorgusu ilave edebilrisiniz.

C++:

If Range("AD" & xRow) <> "" Then Range("AD" & xRow) = Range("AD" & xRow) & " " & BulBirim.Offset(, 2)
If Range("AG" & xRow) <> "" Then Range("AG" & xRow) = Range("AG" & xRow) & " " & BulBirim.Offset(, 2)

ÖmerFaruk · 21 Ocak 2022

İkinci kısımdaki
For i = 6 To .Range("A1").End(xlToRight).Column Step 2
bu satır, gangi sütunlarda döngünün çalışacağınız belirliyor.

Ben zaten ilk sorunuza bakarken Mamül Raporu için de peşinden soru gelecek demiştim kendimce.

Buradaki sorun aşılır da şöyle bir soru var kafamda.
Mamül sayfanızda Ambalajlar ve YarıMamullere verdiğiniz isimler sanırım yine geçici.
Haliyle bize Gerçek adlar ya da yazan tanımın Ambalj mı YarıMamul mu olduğunu belirtecek bir kriter var.
Mamul sayfanızda bu kriter net olmadığı için iş karışacak.

Ben olsam Mamul ve hatta Yarı Mamul sayfalarınızı bu formatta yapmazdım.
Buradaki tüm sütunları İlgili Fiyat sayfasından çekerdim. Tek bir formülle ya da VBA ile.
Fiyat sayfasından gelmeyenleri en baş tarafa çekerim, diğerleri fiyat sayfasından gelir. Fİyat listesi uzadıkça da sütunlarınız raharlıkla artar.

yildirim52 · 21 Ocak 2022

ÖmerFaruk' Alıntı:
Ben zaten ilk sorunuza bakarken Mamül Raporu için de peşinden soru gelecek demiştim kendimce.

Evet konuyu aslında excel formülleri ile çözülecek düşüncesiyle sordum. Konu bambaşka bir yere geldi.

Aslında örnek dosyada da veriler hep "Fiyat" sayfasından çekiliyor. Mesela "Mamül" sayfasında F1 hücresindeki "Yarımamül 1" değerini =Fiyat!I2 diye oraya çekmiştim. Ama tabi bahsettiğiniz gibi otomatik sütun ekleme vs. durumu yok ve nasıl yapıldığını bilmiyorum. Ayrıca "Mamül" sayfasındaki mesela F ve G sütunu "Yarımamül1" ürünü ile ilgili olup DW sütununda A sütünunda yazılı mamülün hammadde maliyetini hesaplıyor. Bu durumda bu förmülün vs. hepsinin VBA'ya bağlanması gerekir sanırım.

Bu arada mevcut halde F:AK sütunları arası üstteki "Kullanılan Hammadde" listesine, AL : DU sütunları arası "Kullanılan Ambalaj" listesine aktarılacak. Yani karışık dizili değil. Fiyat sayfasında da "Kullanılan Hammadde"deki birimler I:L sütunları aralığında, "Kullanılan Ambalaj"daki birimler E:H sütunları aralığında.

For i = 6 To .Range("A1").End(xlToRight).Column Step 2

Buradaki i = 6 'daki 6 sanırım kaçıncı sütundan başladığını gösteriyor diye düşündüm. Hammaddeler 6. sütundan, ambalajlar ise 38. sütundan başlıyor. Ambalajı çeken kodlardan 6'yı 38 olarak değiştirdim. Yalnız işe yaramadı.

Teşekkürler

ÖmerFaruk · 21 Ocak 2022

O sayıyı 38 yaptıysanız
.Range("A1").End(xlToRight).Column
sonucunun AM1 hücrenizin boş bırakılmasından ötürü 38 olmasıdır.
AM1 hücresine sanırım Fire yazmayı unuttunuz.

yildirim52 · 23 Ocak 2022

Şimdi tamamen sorunsuz çalıştı. Çok teşekkürler.
Bununla beraber mevcut durumda bir hammadde ya da ambalaj eklemek ya da çıkarmak çok uğraştırıcı. Bunu da kolaylaştırmaya çabalayacağım zamanla. Ya da sizden destek alabiliriz böyle bir imkanımız varsa.

yildirim52 · 25 Ocak 2022

ÖmerFaruk' Alıntı:
Ben olsam Mamul ve hatta Yarı Mamul sayfalarınızı bu formatta yapmazdım.
Buradaki tüm sütunları İlgili Fiyat sayfasından çekerdim. Tek bir formülle ya da VBA ile.
Fiyat sayfasından gelmeyenleri en baş tarafa çekerim, diğerleri fiyat sayfasından gelir. Fİyat listesi uzadıkça da sütunlarınız raharlıkla artar.

Ömer Faruk Hocam, sil baştan bir düzenleme ile dosyayı yeniden kurguluyorum. Burada yazdıklarınızı biraz araştırmayı denedim. Ama bir sonuca ulaşamadım. Bununla ilgili formül ve vba ile uygulanmış bir örnek paylaşmanız mümkün mü?

ÖmerFaruk · 25 Ocak 2022

Öyle bir örnek dosya malesef yok.
Exceli çoğu zaman terzi usulü kendi verilerimize ve tablolarımıza göre kurgularız.
Bunu yaparken exceli kareli defter gibi kullanmaktan kaçınırız.
Görselliğe önem veririz.
Başlıkların düzenlenmesi , verilerin formatı hatta sayfa adları. Hepsini bir şekilde belli düzen ve kurallar içerisinde oluştuturuz ki
Ne excel bizi ne biz exceli yoralım

yildirim52 · 26 Ocak 2022

Sil baştan düzenliyorum diye bahsettiğim dosyayı ekledim.
Örnek Dosya
Fiyat sayfasının adını Listeler yaptım. Listeler sayfasında "Tablo olarak biçimlendir" şeklinde girdiğim 5 adet tablo bulunuyor.

Hammade_Listesi
Ambalaj_Listesi_Y
Yarımamul_Listesi
Ambalaj_Listesi_M
Mamul_Listesi

1. ve 2. tablo verilerini "Yarımamül" sayfasında 3. ve 4. tablo verilerini ise aynı şekilde "Mamul" sayfasında kullanacağız.

"Yarımamül" ve "Mamül" sayfasındaki tabloların diğer sütunlarını dediğiniz şekilde en başa topladım. Tabloları "Üst Bilgi Satırı" ekleyerek oluşturdum ki başlıklar formüllerde kullanılabiliyor. Yarımamül sayfasının renklendirilmiş kısmına örnek olarak birer ekleme yaptım.

Teşekkürler.

Soru Listeden Veri Alarak Liste Oluşturma

yildirim52

ÖmerFaruk

Destek Ekibi

yildirim52

ÖmerFaruk

Destek Ekibi

yildirim52

Ornek.xlsx dosyasını indir - download

Erdem Akdemir

Destek Ekibi

Erdem Akdemir

Destek Ekibi

yildirim52

ÖmerFaruk

Destek Ekibi

yildirim52

ÖmerFaruk

Destek Ekibi

yildirim52

ÖmerFaruk

Destek Ekibi

ÖmerFaruk

Destek Ekibi

yildirim52

ÖmerFaruk

Destek Ekibi

yildirim52

yildirim52

ÖmerFaruk

Destek Ekibi

yildirim52