Düşeyara ile çoklu verileri dönüştürmek

Katılım
7 Ekim 2009
Mesajlar
16
Excel Vers. ve Dili
MS Excel Professional Plus 2019
Merhabalar,
Bir sorunum var ancak çözemiyorum yardımcı olabileceğinizi umuyorum.

a

x, y

b

z

a

z, y

c

x, z

b, a

x, y

c, b

x, y, z

a, b, c

x, y, z


Benzeri bir tablom var bu tabloya veri girdikçe ilk sütundaki tekil değerlerin ikinci sütundaki karşılıklarını bulup aşağıdaki gibi sağa ekleye ekleye ilerlemesini istiyorum

a

x, y

z, y

x, y

x, y, z

b

z

x, y

x, y, z

x, y, z

c

x, z

x, y, z

x, y, z

 


Sizlere sormadan önce https://www.ablebits.com/office-addins-blog/vlookup-multiple-values-excel/ bu sayfadaki =IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), "") formülünü kullanmayı denedim ama maalesef bu formül kaynak tablodaki verileri virgülsüz olarak ayıramıyor ve karşılık değerleri karıştırıyor.
Yardımlarınızı bekliyorum. Şimdiden teşekkür ederim.
 

uzmanamele

Uzman
Uzman
Katılım
26 Eylül 2007
Mesajlar
9,141
Excel Vers. ve Dili
excel 2010
Merhaba
Excelin yerleşik fonksiyonları ile bunun yapılması zor.
Hatta, ilk tablonuzdaki düzensizliğin içinde ilgili veriyi bulup getirecek makro yazmak bile zordur.

Örneğin, 2. tabloya a verilerini girdiğinizi düşünelim.
1.a'yı yazdığınızda x,y verisi gelecek
2.a'yı yazdığınızda z,y verisi gelecek
3.a'yı yazdığınızda x,y verisi gelecek (b veya a yazıldığında aynısı gelecek)
4.a'yı yazdığınızda x,y,z verisi gelecek (a veya b veya c yazıldığında aynısı gelecek)

Makro hem girilen verinin a,b,c olduğunu kontrol edecek, hem kaçıncı kez girildiğini kontrol edecek, hem kaçıncı kez girildiğine göre 1. sütundaki birleşik metin içinde bulup 2. sütunundaki değeri getirecek.

Sizin yapmak istediğiniz tablo 3 satır, 5 sütundan ibaret olacak şekilde sabitse formüllerle de yapılır.
Ancak veri girişiniz dinamik, yani her a,b,c veri girişine göre sağa doğru gitsin derseniz o formülle mümkün olmayabilir.

Örnek dosya ekleyerek sorunuzu destekleyin.
 

Ömer

Moderatör
Yönetici
Katılım
18 Ağustos 2007
Mesajlar
21,776
Excel Vers. ve Dili
Microsoft 365 Tr
Ofis 2016 Tr
Merhaba,

Doğru anladısyam aşağıdaki gibi olabilir.

Verileriniz A2:A10 aralında düşünüldü. Ölçütlerinizde E2:E.. aralğında düşünüldü.

F2 hücresine yazıp dizi formülüne çevirdikten sonra yana ve alt hücrelere kopyalayınız.
Kod:
=EĞER($E2="";"";EĞERHATA(İNDİS($B$2:$B$10;KÜÇÜK(EĞER(ESAYIYSA(MBUL(", "&$E2&",";", "&$A$2:$A$10&","))
;SATIR($A$2:$A$10)-SATIR($A$2)+1);SÜTUNSAY($F$2:F2)));""))
 

ÖmerFaruk

Destek Ekibi
Destek Ekibi
Katılım
22 Ekim 2017
Mesajlar
3,788
Excel Vers. ve Dili
365 Tr
Makro ile çözüm isterseniz veya verilerinizde satır sayısı çok fazla ise formüller de kasıyorsa;

Aşağıdaki kodları çalışma kitabınızın VBE penceresinde boş bir Modüle içine kaydedip çalıştırabilirsiniz.
C++:
Sub Yeni1()
    Dim Veri, Dict As Object, Ara As Variant, Yaz As Variant, Sh As Worksheet, i As Integer, k As Integer
    Veri = Worksheets("Sayfa1").Range("A1").CurrentRegion.Value
    Set Dict = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(Veri)
        Ara = Split(Veri(i, 1), ",")
        For k = 0 To UBound(Ara)
            Yaz = Trim(Ara(k))
            If Not Dict.Exists(Yaz) Then
                Dict.Add Yaz, Veri(i, 2)
            Else
                Dict(Yaz) = Dict(Yaz) & " ?? " & Veri(i, 2)
            End If
        Next k
    Next i
    Set Sh = Worksheets("Sayfa2") 'Hangi sayfada görmek istiyorsanız onun adını girersiniz
    For i = 1 To Dict.Count
        'Girdiğiniz sayfa adına göre 1.satır başlık varsayılmış olup
        'A2 hücresinden itibaren kaydeder
        Sh.Range("A1").Offset(i, 0) = Dict.Keys()(i - 1)
        Ara = Split(Dict.Items()(i - 1), " ?? ")
        For k = 0 To UBound(Ara)
            Sh.Range("A1").Offset(i, k + 2) = Ara(k)
        Next k
    Next i
    Erase Veri: Set Dict = Nothing: Erase Ara: Set Sh = Nothing
End Sub
 
Katılım
7 Ekim 2009
Mesajlar
16
Excel Vers. ve Dili
MS Excel Professional Plus 2019
Verileriniz A2:A10 aralında düşünüldü. Ölçütlerinizde E2:E.. aralğında düşünüldü.
Merhaba, cevabınız ve emeğiniz için çok teşekkür ederim. Maalesef verilerim A sütununda A10 dan sonra da devam ediyor, keza B sütunum da devam ediyor, formülünüzün aralığını değiştirmeye çalıştığımda ise (A:A ve B:B şeklinde) çalışmadı.
 
Katılım
7 Ekim 2009
Mesajlar
16
Excel Vers. ve Dili
MS Excel Professional Plus 2019
Makro ile çözüm isterseniz veya verilerinizde satır sayısı çok fazla ise formüller de kasıyorsa;

Aşağıdaki kodları çalışma kitabınızın VBE penceresinde boş bir Modüle içine kaydedip çalıştırabilirsiniz.
Merhaba sizin de cevabınız ve emeğiniz için çok teşekkür ederim. Macro kodunuz çok güzel ve sade. Tam da istediğim şekilde oldu. Tekrar ellerinize sağlık.
 
Katılım
7 Ekim 2009
Mesajlar
16
Excel Vers. ve Dili
MS Excel Professional Plus 2019
Yabancı sitede önerilen çözüm
Hi!
In C1, write the letter "a". Select the range D1:H1 and enter the array formula in the formula bar:
=IFERROR(INDEX($B$1:$B$10, SMALL(IF(ISNUMBER(SEARCH(C1,$A$1:$A$10)), ROW($A$1:$A$10)-0,""), COLUMN()-2)),"")
şeklindeydi ama çalışmadı maalesef. Bilgilerinize sunarım.
 

Ömer

Moderatör
Yönetici
Katılım
18 Ağustos 2007
Mesajlar
21,776
Excel Vers. ve Dili
Microsoft 365 Tr
Ofis 2016 Tr
Merhaba, cevabınız ve emeğiniz için çok teşekkür ederim. Maalesef verilerim A sütununda A10 dan sonra da devam ediyor, keza B sütunum da devam ediyor, formülünüzün aralığını değiştirmeye çalıştığımda ise (A:A ve B:B şeklinde) çalışmadı.
A:A yerine A2:A65000 deneyebilirsiniz. Eski versiyonlarda dizi formülleri tam sütunu seçince çalışmıyor.

Gibi.
Kod:
=EĞER($E2="";"";EĞERHATA(İNDİS($B$2:$B$65000;KÜÇÜK(EĞER(ESAYIYSA(MBUL(", "&$E2&",";", "&$A$2:$A$65000&","))
;SATIR($A$2:$A$65000)-SATIR($A$2)+1);SÜTUNSAY($F$2:F2)));""))



Çalıştıramazsanız örnek dosya ekleyiniz.


.
 
Katılım
7 Ekim 2009
Mesajlar
16
Excel Vers. ve Dili
MS Excel Professional Plus 2019
A:A yerine A2:A65000 deneyebilirsiniz. Eski versiyonlarda dizi formülleri tam sütunu seçince çalışmıyor.

Gibi.
Kod:
=EĞER($E2="";"";EĞERHATA(İNDİS($B$2:$B$65000;KÜÇÜK(EĞER(ESAYIYSA(MBUL(", "&$E2&",";", "&$A$2:$A$65000&","))
;SATIR($A$2:$A$65000)-SATIR($A$2)+1);SÜTUNSAY($F$2:F2)));""))



Çalıştıramazsanız örnek dosya ekleyiniz.


.
Merhaba, formülünüz çalıştı. Çok teşekkür ederim. Ancak A ve B sütunlarım başka bir sayfada, E ve F sütunlarım başka bir sayfada olduğu için sanırım, işlem hesaplama süresi uzun oluyor. 65000 değerini 1000e indirdim ama yine saplama uzun sürüyor. Sonuçta işimi gördüğü için minnettarım. Ellerinize sağlık.
 

uzmanamele

Uzman
Uzman
Katılım
26 Eylül 2007
Mesajlar
9,141
Excel Vers. ve Dili
excel 2010
Merhaba
Benzeri bir tablom var bu tabloya veri girdikçe ilk sütundaki tekil değerlerin ikinci sütundaki karşılıklarını bulup aşağıdaki gibi sağa ekleye ekleye ilerlemesini istiyorum
Ömer beylerin önerdiği çözümler gayet başarılı.
Yalnız benim aanlamadığım; siz veri girişini ana tabloya mı yapacaksınız? 2. tabloya giriş yaparak her defasında ana tablodan veri çekerek sağa doğru eklemesini mi istiyorsunuz?

"Sizin yapmak istediğiniz tablo 3 satır, 5 sütundan ibaret olacak şekilde sabitse formüllerle de yapılır.
Ancak veri girişiniz dinamik, yani her a,b,c veri girişine göre sağa doğru gitsin derseniz o formülle mümkün olmayabilir."

Sanırım muallak olan burası ve ilk yazdığım duruma göre arkadaşlar doğru anlamışlar.
 
Katılım
7 Ekim 2009
Mesajlar
16
Excel Vers. ve Dili
MS Excel Professional Plus 2019
Ömer beylerin önerdiği çözümler gayet başarılı.
Yalnız benim aanlamadığım; siz veri girişini ana tabloya mı yapacaksınız? 2. tabloya giriş yaparak her defasında ana tablodan veri çekerek sağa doğru eklemesini mi istiyorsunuz?

"Sizin yapmak istediğiniz tablo 3 satır, 5 sütundan ibaret olacak şekilde sabitse formüllerle de yapılır.
Ancak veri girişiniz dinamik, yani her a,b,c veri girişine göre sağa doğru gitsin derseniz o formülle mümkün olmayabilir."

Sanırım muallak olan burası ve ilk yazdığım duruma göre arkadaşlar doğru anlamışlar.
Merhaba,
Evet veri girişimi ilk tablodaki her iki sütuna ayrı ayrı yapıyorum, 2. tablomun ilk sütunu statik, hem Ömer beyin (sağolsun) hem de Ömer Faruk beyin (sağolsun) çözümleri ilk tabloma veri girdikçe 2. tablomda sağa doğru kendiliğinden bulunan parametreleri ekleyebiliyor. Yani hem formülle hem macroyla çözülmüş oldu. Kendim günlerdir çözememiştim (virgüllü veriler nedeniyle), sizler sayesinde mutlu oldum. Çok teşekkürler.
 
Katılım
7 Ekim 2009
Mesajlar
16
Excel Vers. ve Dili
MS Excel Professional Plus 2019
Tekrar merhaba,
Acaba

=EĞER($E2="";"";EĞERHATA(İNDİS($B$2:$B$65000;KÜÇÜK(EĞER(ESAYIYSA(MBUL(", "&$E2&",";", "&$A$2:$A$65000&","))
;SATIR($A$2:$A$65000)-SATIR($A$2)+1);SÜTUNSAY($F$2:F2)));""))

bu formüle tarih sütunu ekleyebilir miyiz?

Aşağıdaki tabloda ilk sütundaki tarihleri ikinci sütundaki değerlere göre sıralayıp, 2. tablodaki gibi o değerin en son tarihini yazdırabilir miyiz? Tarih sütunu bazen eskiden yeniye doğru ilerliyor, bazen de rastgele....

01.01.2022

a

x, y

15.12.2022

b

z

03.02.2022

a

z, y

24.04.2022

c

x, z

25.10.2022

b, a

x, y

20.04.2022

c, b

x, y, z

02.05.2022

a, b, c

x, y, z



Alttaki tablodaki gibi (a) nın kronolojik olarak son tarih verisi 25.10.2022, (b) nin 15.12.2022, (c) nin de 02.05.2022 olacak şeklinde bir tablo oluşabilir mi sizce?

25.10.2022

a

x, y

z, y

x, y

x, y, z

15.12.2022

b

z

x, y

x, y, z

x, y, z

02.05.2022

c

x, z

x, y, z

x, y, z

 

Ömer

Moderatör
Yönetici
Katılım
18 Ağustos 2007
Mesajlar
21,776
Excel Vers. ve Dili
Microsoft 365 Tr
Ofis 2016 Tr
Örnekte yazdığınız son tarih verilerini nasıl bulduğunuzu anlamadım. Örneğinizin doğru olduğunu kontrol edip tarih bulma mantığını detaylı açıklar mısınız.
 
Katılım
7 Ekim 2009
Mesajlar
16
Excel Vers. ve Dili
MS Excel Professional Plus 2019
Örnekte yazdığınız son tarih verilerini nasıl bulduğunuzu anlamadım. Örneğinizin doğru olduğunu kontrol edip tarih bulma mantığını detaylı açıklar mısınız.
Merhaba Ömer bey,
Hemen açıklayayım;
Mesela (a) değerinin veya içerisinde (a) bulunan verilerin ((b, a) gibi) ilk tablo ilk sütunda belirtilmiş tarih değerleri arasındaki kronolojik sonuncuyu bulmak istiyorum.
Örneğimde (a) nın (veya içerisinde (a) bulunan verilerin) tarihleri 01.01.2022 , 03.02.2022 , 25.10.2022 , 02.05.2022 şeklindedir. Bunlardan eskiden yeniye son tarih olan (sonuncu girilen değer değil, aralarından en yeni tarih olanı) 25.10.2022 yi ikinci tabloya eklemek istiyorum.
 

Ömer

Moderatör
Yönetici
Katılım
18 Ağustos 2007
Mesajlar
21,776
Excel Vers. ve Dili
Microsoft 365 Tr
Ofis 2016 Tr
Aranan değer E2, Tarihler A2:A1000, aranan verinin arandığı aralık B2:B1000 olarak düşünüldü.

D2 yazıp dizi formülüne çevirdikten sonra alt hücrelere kopyalayınız.
Kod:
=EĞER($E2="";"";MAK(EĞER(ESAYIYSA(MBUL(", "&$E2&",";", "&$B$2:$B$1000&","));$A$2:$A$1000)))
 
Katılım
7 Ekim 2009
Mesajlar
16
Excel Vers. ve Dili
MS Excel Professional Plus 2019
Aranan değer E2, Tarihler A2:A1000, aranan verinin arandığı aralık B2:B1000 olarak düşünüldü.

D2 yazıp dizi formülüne çevirdikten sonra alt hücrelere kopyalayınız.
Kod:
=EĞER($E2="";"";MAK(EĞER(ESAYIYSA(MBUL(", "&$E2&",";", "&$B$2:$B$1000&","));$A$2:$A$1000)))
Mükemmel bir çözüm oldu Ömer bey, ellerinize emeğinize sağlık. Çok teşekkür ederim.
 

Ömer

Moderatör
Yönetici
Katılım
18 Ağustos 2007
Mesajlar
21,776
Excel Vers. ve Dili
Microsoft 365 Tr
Ofis 2016 Tr
Rica ederim, iyi çalışmalar.
 
Üst