Birden fazla çalışma sayfasında veya çalışma kitabında bir değeri nasıl hızlıca arayabilirim?

Excel'de belirli bir değeri farklı sayfalarda veya hatta birden fazla çalışma kitabında bulmanız gerekti mi? Bu, özellikle büyük projelerde, aylık raporlarda veya birden çok dosyada tutulan verileri bir araya getirirken sıkça karşılaşılan bir durumdur. Her bir sayfa veya dosyada elle arama yapmak zaman alıcı olmasının yanı sıra hatalara da açıktır. Bu rehberde, ister tek bir çalışma kitabında, ister birden fazla çalışma kitabında, ister formül tabanlı ya da konsolide edilmiş veri çözümü arıyor olun, verileri etkili bir şekilde aramanıza olanak sağlayan çeşitli yöntemleri bulacaksınız. Bu yöntemler, günlük Excel çalışmalarınızda veya veri analizi projelerinde karşılaşabileceğiniz pratik gereksinimlere çözüm sunar.

Bir çalışma kitabının birden fazla çalışma sayfasında bir değeri Bul ve Değiştir özelliğiyle arama

Bir klasördeki tüm çalışma kitaplarında VBA ile bir değeri arama

Kutools for Excel ile birden fazla açık çalışma kitabında hızlıca bir değeri arayın

Excel formülleriyle birden fazla çalışma sayfasında bir değeri arayın

Bir çalışma kitabının birden fazla çalışma sayfasında bir değeri Bul ve Değiştir özelliğiyle arama

Excel’in Bul ve Değiştir özelliği, aynı çalışma kitabındaki birden fazla çalışma sayfasında belirli değerleri hızlıca bulmak için temel ama etkili bir yoldur. Bu yöntem, hangi sayfaları aramak istediğinizi bildiğinizde veya veriniz nispeten tek bir dosya içinde düzenliyse en uygunudur. Farklı dosyalar veya kapalı çalışma kitapları arasında arama yapmayı desteklemez, ancak hızlıca veri bulmak için doğrudan bir yol sunar.

1. Öncelikle, Ctrl tuşuna basılı tutarak ve Çalışma Sayfası Sekmesi çubuğunda her bir çalışma sayfasına ayrı ayrı tıklayarak aramaya dahil etmek istediğiniz çalışma sayfalarını seçin. Bu, aramanın aynı anda tüm seçili çalışma sayfalarında uygulanmasını sağlar. Ekran görüntüsüne bakın:

2. İstediğiniz çalışma sayfalarını seçtikten sonra, Ctrl + F tuşlarına basarak Bul ve Değiştir iletişim kutusunu açın. Bulunacak metin kutusuna bulmak istediğiniz değeri yazın ve Bul sekmesinde Bul Tümünü butonuna tıklayın. Excel, aradığınız değeri içeren seçili çalışma sayfalarındaki tüm hücreleri ve konumlarını anında listeleyecektir. Ekran görüntüsüne bakın:

İpucu: Bul ve Değiştir aracı yalnızca açık ve görünür olarak seçilmiş çalışma sayfalarında arama yapar. Ekstra çalışma sayfalarında arama yapmak istiyorsanız, yukarıda belirtildiği gibi onları da seçtiğinizden emin olun. Bir sayfayı yanlışlıkla atladıysanız, onu seçip işlemi tekrarlayabilirsiniz.

Önlemler: Bu yöntem kapalı veya gizli çalışma kitaplarında arama yapmaz ve hücreleri otomatik olarak vurgulamaz – yalnızca sonuçları gezinmek için listeler.

Sorun Giderme: Beklediğiniz sonuçları göremiyorsanız, çalışma sayfası seçiminizi tekrar kontrol edin ve aramayı engelleyebilecek filtre veya hücre koruması olup olmadığını doğrulayın.

Birden Çok Sayfa ve Çalışma Kitabı Arasında Değerleri Kolayca Bulun ve Değiştirin

Kutools for Excel'in gelişmiş Bul ve Değiştir özelliği, birden çok sayfa veya hatta tüm açık çalışma kitapları arasında değerleri aramak ve değiştirmek için etkili bir yöntem sunar. Bu gelişmiş özellik sayesinde, büyük veri setleriyle çalışırken zaman kazanabilir ve hatalardan kaçınabilirsiniz, böylece Excel görevleriniz daha hızlı ve daha doğru hale gelir.

Kutools for Excel - Excel'i 300'den fazla temel araçla güçlendirin. Sürekli ücretsiz AI özelliklerinden yararlanın! Hemen Edinin

Bir klasördeki tüm çalışma kitaplarında VBA ile bir değeri arama

Bir klasörde kaydedilmiş birden fazla çalışma kitabında – açmamış olsanız bile – belirli bir değeri aramanız gerekiyorsa, Excel'in yerleşik araçları bunu doğrudan yapamaz. Bu durumda, bir VBA (Visual Basic for Applications) makrosu işlemi sizin için otomatikleştirerek klasördeki her bir çalışma kitabını sırayla açar, tüm çalışma sayfalarını tarar ve eşleşmelerin bulunduğu yerleri kaydeder. Bu yöntem, periyodik denetimler veya arşivlenmiş ya da toplu dosyalarda değer kontrolü gibi durumlar için oldukça pratiktir.

1. Excel'de yeni (veya boş) bir çalışma kitabı açın. Sonuçların görüneceği hücreyi seçin (genellikle A1 hücresi). Alt + F11 tuşlarına basarak Microsoft Visual Basic for Applications editör penceresini açın.

2. VBA editörü içinde, Ekle > Modül menüsüne giderek yeni bir modül oluşturun ve aşağıdaki VBA kodunu bu pencereye yapıştırın.

VBA: Bir klasördeki tüm çalışma kitaplarında bir değeri arayın.

Sub SearchFolders()
'UpdatebyKutoolsforExcel20200913
    Dim xFso As Object
    Dim xFld As Object
    Dim xStrSearch As String
    Dim xStrPath As String
    Dim xStrFile As String
    Dim xOut As Worksheet
    Dim xWb As Workbook
    Dim xWk As Worksheet
    Dim xRow As Long
    Dim xFound As Range
    Dim xStrAddress As String
    Dim xFileDialog As FileDialog
    Dim xUpdate As Boolean
    Dim xCount As Long
    Dim xAWB As Workbook
    Dim xAWBStrPath As String
    Dim xBol As Boolean
    Set xAWB = ActiveWorkbook
    xAWBStrPath = xAWB.Path & "\" & xAWB.Name
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a forlder"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    xStrSearch = "KTE"
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Set xOut = Worksheets.Add
    xRow = 1
    With xOut
        .Cells(xRow, 1) = "Workbook"
        .Cells(xRow, 2) = "Worksheet"
        .Cells(xRow, 3) = "Cell"
        .Cells(xRow, 4) = "Text in Cell"
        Set xFso = CreateObject("Scripting.FileSystemObject")
        Set xFld = xFso.GetFolder(xStrPath)
        xStrFile = Dir(xStrPath & "\*.xls*")
        Do While xStrFile <> ""
            xBol = False
            If (xStrPath & "\" & xStrFile) = xAWBStrPath Then
                xBol = True
                Set xWb = xAWB
            Else
                Set xWb = Workbooks.Open(Filename:=xStrPath & "\" & xStrFile, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
            End If
            For Each xWk In xWb.Worksheets
                If xBol And (xWk.Name = .Name) Then
                Else
                Set xFound = xWk.UsedRange.Find(xStrSearch)
                If Not xFound Is Nothing Then
                    xStrAddress = xFound.Address
                End If
                Do
                    If xFound Is Nothing Then
                        Exit Do
                    Else
                        xCount = xCount + 1
                        xRow = xRow + 1
                        .Cells(xRow, 1) = xWb.Name
                        .Cells(xRow, 2) = xWk.Name
                        .Cells(xRow, 3) = xFound.Address
                        .Cells(xRow, 4) = xFound.Value
                    End If
                    Set xFound = xWk.Cells.FindNext(After:=xFound)
                Loop While xStrAddress <> xFound.Address
                End If
            Next
            If Not xBol Then
            xWb.Close (False)
            End If
            xStrFile = Dir
        Loop
        .Columns("A:D").EntireColumn.AutoFit
    End With
    MsgBox xCount & " cells have been found", , "Kutools for Excel"
ExitHandler:
    Set xOut = Nothing
    Set xWk = Nothing
    Set xWb = Nothing
    Set xFld = Nothing
    Set xFso = Nothing
    Application.ScreenUpdating = xUpdate
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

3. Makroyu çalıştırmak için F5 tuşuna basın veya Çalıştır butonuna tıklayın. Bir Klasör Seç penceresi açılacak ve aramak istediğiniz çalışma kitaplarını içeren klasörü seçmenizi isteyecektir. Ekran görüntüsüne bakın:

4. Tamam'a tıklayın. Arama tamamlandıktan sonra, belirtilen değeri içeren toplam hücre sayısını bildiren bir mesaj kutusu görüntülenecek. Ekran görüntüsüne bakın:

5. Mesajı kapatmak için Tamam'a tıklayın. Bulunan değerlerin tüm konumları, çalışma kitabı adı, çalışma sayfası adı, hücre adresi ve o hücrenin tam içeriği dahil olmak üzere yeni bir çalışma sayfasında listelenir.

İpucu: Geçerli arama terimi VBA kodunda “KTE” olarak ayarlıdır. Bunu, kod içindeki xStrSearch = "KTE" satırındaki değeri istediğinizle değiştirerek özelleştirebilirsiniz.

Önlemler: Çalıştırmadan önce tüm ilgili çalışma kitaplarının kaydedildiğinden ve kapalı olduğundan (makroyu çalıştırdığınız hariç) emin olun. Çok sayıda veya karmaşık Excel dosyaları olan klasörler işlem süresini uzatabilir. Makro çalışırken işlemi bölmeyin.

Sorun Giderme: Hata alırsanız, tüm dosyaların gerçek bir Excel çalışma kitabı olduğundan (bozuk veya parolalı olmadığından) ve makro güvenlik ayarlarınızın kodu çalıştırmaya izin verdiğinden emin olun. Makro tamamlanmazsa, daha az sayıda dosya ile deneyin.

Kutools for Excel ile birden fazla açık çalışma kitabında hızlıca bir değeri arayın

Bir Excel oturumunda birden çok açık çalışma kitabında arama yapmak istediğinizde, Kutools for Excel kullanışlı bir Bul ve Değiştir bölmesi sunar; bu sayede arama işlemi çok daha kolay ve düzenli hale gelir. Özellikle, birden fazla dosya ile sık sık çalışan ve karmaşık bir kurulum ya da kodlama olmadan kolayca arama yönetmek isteyen kullanıcılar için uygun, kullanıcı dostu ve pratik bir araçtır.

1. Açık olan herhangi bir çalışma kitabınızda şuraya gidin: Kutools sekmesine tıklayın ve Gezinme. Ardından Bul ve Değiştir butonuna tıklayın, böylece Bul ve Değiştir bölmesi genellikle Excel pencerenizin sol kısmında açılır. Ekran görüntüsüne bakın:

2. Ardından Bul sekmesinde aramak istediğiniz değeri Bulunacak metinmetin kutusuna girin. Tüm Çalışma Kitapları seçeneğini İçinde açılır menüsünden seçin ve ardından Tümünü Bul butonuna tıklayarak eşleşen tüm hücrelerin ve konumlarının anında listelenmesini sağlayın. Ekran görüntüsüne bakın:

İpucu: Kutools for Excel’in gelişmiş Bul ve Değiştir işlevi, verileri yalnızca tüm açık çalışma kitaplarında değil, aynı zamanda seçilen sayfalarda, geçerli çalışma kitabında, aktif çalışma sayfasında ya da yalnızca mevcut aralığınızda aramanızı ve değiştirmenizi sağlar. Bu da ihtiyacınıza göre esnek bir kontrol olanağı sunar.

Önlemler: Aramak istediğiniz tüm çalışma kitaplarının arama başlamadan önce açık olduğundan emin olun, çünkü bu araç Excel'de açık olmayan dosyalarda arama yapamaz.

Sorun Giderme: Bazı dosyalar arama sonuçlarında görünmüyorsa, dosyaların Excel’de tamamen yüklendiğinden ve korumalı görünümde ya da salt okunur modda olmadığından emin olun, çünkü bu durumlar aramayı kısıtlayabilir.

Demo: Kutools for Excel ile birden fazla açık çalışma kitabında değer arayın

Excel formülleriyle birden fazla çalışma sayfasında bir değeri arama

Çalışma kitabınızda ismini bildiğiniz birden fazla sayfa varsa ve belirli bir değerin o sayfalarda olup olmadığını denetlemek istiyorsanız, Excel formüllerini kullanarak bu sayfalarda dinamik arama yapabilirsiniz. Bu yöntem, arama sonuçlarını otomatik güncel tutmak istediğinizde ve sayfa listeniz nispeten sabit ya da ayrı bir tabloda yönetiliyorsa uygundur.

Bu yöntem, aranacak tüm sayfa adlarını önceden bilmenizi veya listelemenizi gerektirir. Otomatik kontroller, panolar veya özet referans tabloları oluşturmak istediğinizde en verimli sonuçları verir.

Avantajları: Sonuçlar veri değiştikçe otomatik güncellenir; betik veya eklenti gerekmez; her şey çalışma kitabı içinde yapılır.
Dezavantajları: Çalışma sayfası adları sık değişiyorsa veya çok sayıda sayfanız varsa bu yaklaşım uygun değildir.

Örnek senaryo: Diyelim ki Sheet1, Sheet2 ve Sheet3 adlarında üç sayfanız var. "Invoice123" gibi belirli bir değerin A1 hücresinde hangi sayfada olduğunu veya bu değer o sayfalarda herhangi bir yerde var mı yok mu olduğunu bilmek istiyorsunuz.

Adım1. D2:D4 aralığında (D2: Sheet1, D3: Sheet2, D4: Sheet3) sayfa adlarının bir listesi olduğunu varsayalım. E1 hücresine aramak istediğiniz değeri (ör. "Invoice123") girin. Ardından F2'ye şu formülü yazın:

=IF(COUNTIF(INDIRECT("'"&D2&"'!A:A"), $E$1) >0, "Found", "Not Found")

Adım2. Formülü F2'den F4'e doğru sürükleyin ve D2:D4'te listelenen tüm sayfaları kontrol edin. Her bir sayfa için "Bulundu" veya "Bulunamadı" sonucu döner.

Nasıl çalışır: Formülde, INDIRECT fonksiyonu ile her bir listelenen çalışma sayfasına başvuru oluşturulur ve COUNTIF ile E1’deki değerin ilgili sayfanın A sütununda olup olmadığı kontrol edilir. Gerekirse A:A aralığını başka bir sütunu veya belirli bir aralığı (ör. A1:Z100) hedefleyecek şekilde değiştirebilirsiniz.

Ek İpucu: Değerin bulunduğu çalışma sayfası adlarını almak için aşağıdaki dizi formülünü kullanabilirsiniz (eski Excel sürümlerinde Ctrl + Shift + Enter ile, Microsoft365/Excel2021+’da ise normal formül olarak girin):

=TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT("'"&D2:D4&"'!A:A"), $E$1)>0, D2:D4, ""))

Bu formül, değerin bulunduğu tüm sayfa adlarının virgülle ayrılmış bir listesini döndürecektir. INDIRECT fonksiyonunu dikkatli kullanın; yalnızca açık çalışma kitaplarında çalışır ve kapalı dosyalarda arama yapmaz.

Önlemler: Çalışma sayfası adı değişirse ya da silinirse, formül #BAŞV! hatası döndürür; sayfa adı listesinin doğru olduğundan her zaman emin olun. Büyük çalışma kitaplarında INDIRECT tabanlı formüller performansı yavaşlatabilir.

Sorun Giderme: Hata görüyorsanız, başvurulan tüm sayfaların var olduğundan ve arama aralığının doğru olduğundan emin olun. Dinamik sayfa listeleri için, listenin otomatik güncellenmesini sağlamak adına adlandırılmış aralıklar veya Veri Doğrulama kullanmayı değerlendirin.

İlgili Makaleler:

• Birden fazla çalışma sayfası veya tüm çalışma kitabında hızlıca nasıl Bul ve Değiştir yapılır?
• Birden fazla açık Excel dosyasında hızlıca nasıl Bul ve Değiştir yapılır?
• Excel’de en büyük negatif değeri (0’dan küçük) nasıl bulabilirim?
• Excel’de bir sütundaki tüm yinelenen değerleri nasıl çıkartabilirim?