Excel'de transpoze yapıştırırken formül başvurularını nasıl koruyabilirsiniz?

Excel ile çalışırken, Veri Yer Değiştirme özelliği genellikle verilerin yönünü sütunlardan satırlara veya tam tersi şekilde değiştirmek için kullanılır. Ancak bu verilerde formüller bulunması durumunda ortaya çıkan yaygın bir sorun vardır - Excel varsayılan olarak hücre başvurularını yeni yöne uyacak şekilde değiştirir. Aşağıdaki ekran görüntüsünde gösterildiği gibi, bu otomatik ayarlama özellikle karmaşık veya bağlantılı veri setlerinde amaçlanan hesaplamaları bozmaktadır. Finansal modeller, mühendislik hesaplamaları veya formül bütünlüğünün korunmasının önemli olduğu bağlantılı panolarla uğraşan herkes için transpoze edilmiş verileri yapıştırırken orijinal formül başvurularını korumanın yollarını öğrenmek önemlidir. Bu makale, bu sonucu elde etmek için birkaç pratik yöntem açıklıyor, en iyi ve en kötü kullanım senaryolarına değiniyor ve daha sorunsuz işlemler için sorun giderme önerileri sunuyor.

Formül başvurularını mutlak hale getirmek ve veriyi transpoze etmek için F4 tuşunu kullanın

Bul ve Değiştir işleviyle transpoze edin ve başvuru koruyun

Kutools for Excel ile transpoze edin ve başvuru koruyun

VBA Kodu - Formül başvurularını (göreceli veya mutlak) koruyarak hücreleri transpoze etme

Excel Formülü - INDIRECT veya adres oluşturma ile transpoze formülleri manuel olarak yeniden oluşturun

Formül başvurularını mutlak hale getirmek ve veriyi transpoze etmek için F4 tuşunu kullanın

1. Formül Hücresini Seçin

Ayarlamak istediğiniz formülü içeren hücreye tıklayın.

2. Formül Çubuğunu Açın

Formül çubuğuna tıklayarak imlecinizi formülün içine yerleştirin.

3. Mutlak Başvurulara Dönüştürün

Formül çubuğundaki tüm formülü seçin, ardından F4 tuşuna basın.

Bu işlem, başvuru formatını göreceli, mutlak ve karışık arasında değiştirir.

Formüldeki tüm hücre başvuruları tamamen mutlak olana kadar bunu tekrarlayın.

4. Veriyi Kopyalayın

Kopyalamak istediğiniz veri aralığını seçin ve Ctrl + C tuşlarına basın.

5. Transpoze Edilmiş Veri Olarak Yapıştırın

Hedef hücreye sağ tıklayın, ardından Özel Yapıştır → Transpoze seçeneğini seçin.

İpucu:
Mutlak başvurular, formülün her zaman aynı hücrelere atıfta bulunmasını sağlar, kopyalansa veya taşınmış olsa bile. Veriyi transpoze etmek, satırları sütunlara veya sütunları satırlara dönüştürür - veri düzenini yeniden organize etmek için mükemmeldir.

Bul ve Değiştir işleviyle transpoze edin ve başvuru koruyun

Excel'de bir hücre aralığını transpoze etmek ve orijinal formül başvurularını korumak için, formülleri geçici olarak metne dönüştürmek, yeniden konumlandırmak ve ardından tekrar formüllere döndürmek için Bul ve Değiştir özelliğini kullanabilirsiniz. Bu yaklaşım küçük ila orta boyutlu veri setleri için uygundur ve ek eklentiler yüklemek istemediğiniz veya VBA kullanmak istemediğiniz durumlarda faydalıdır.

1. İlk olarak, transpoze etmek istediğiniz formülleri içeren hücre aralığını seçin. Bul ve Değiştir iletişim kutusunu açmak için Ctrl + H tuşlarına basın.

2. İçinde Bul ve Değiştir iletişim kutusunda yazın = içindeki Bulunacak metin alanına yazın ve #= içindeki Değiştirilecek metin alanına yazın. Bu adım eşit işaretini değiştirerek canlı formülleri düz metne çevirir. Bu işlem, Excel'in kopyalama ve transpoze sürecinde formül hücre başvurularını dönüştürmesini engeller.

3. Tıklayın Tümünü Değiştir. Yaptığınız değişiklik sayısını gösteren bir iletişim kutusu açılacaktır. Tıklayın Tamam ve ardından Kapat iletişim kutularından çıkmak için.

4. Şimdi metne dönüştürülmüş hücreler hala seçiliyken, Ctrl + C tuşlarına basarak onları kopyalayın. İstenilen yapıştırma konumuna gidin, sağ tıklayın ve içerik menüsünden Özel Yapıştır > Transpoze seçeneğini seçerek transpoze edip yapıştırın. Büyük bir veri setiniz varsa veya formülleriniz uçucu işlevler içeriyorsa, yapıştırılan sonuçları dikkatlice kontrol etmeniz gerekebilir.

5. Yapıştırdıktan sonra tekrar Ctrl + H tuşlarına basarak Bul ve Değiştir iletişim kutusunu açın. Şimdi, orijinal değişikliği geri alın: yazın #= içindeki Bulunacak metin kutusuna ve = içindeki Değiştirilecek metin kutusuna yazın. Bu işlem metni tekrar işlevsel formüllere çevirecektir.

6. Tıklayın Tümünü Değiştir, ardından Tamam > Kapat işlemi tamamlamak için. Formülleriniz artık transpoze edilmiş ve orijinal aralıkta olduğu gibi başvuruları korumuştur.

Bu manuel yöntem küçük veri setleri için en iyi şekilde çalışır. Daha karmaşık aralıklarla veya karışık başvuru stilleriyle uğraşırken, formüllerin beklendiği gibi yeniden hesaplandığından emin olmak için sonuçları iki kez kontrol edin. Adlandırılmış aralıklarınız veya harici başvurularınız varsa, bu başvurular transpoze ettikten sonra gözden geçirilmelidir.

Kutools for Excel ile transpoze edin ve başvuru koruyun

Eğer formüller içeren verileri sık sık transpoze etmeniz gerekiyorsa, Kutools for Excel size akıcı bir yaklaşım sunar. Hücre Referanslarını Dönüştür özelliği, seçili tüm formül başvurularını transpoze etmeden önce mutlak başvurulara hızlıca dönüştürmenizi sağlar. Bu, transpoze ettikten sonra orijinal başvuruların bozulmadan kalmasını sağlar, manuel müdahalenin ve kırık formüllerin riskini en aza indirir.

Kutools for Excel'i yükledikten sonra şu adımları izleyin:

1. Transpoze etmek istediğiniz formülleri içeren hücreleri seçin, ardından tıklayın Kutools > Daha Fazla (Formül grubunda) > Hücre Referanslarını Dönüştür. Bu, başvuru dönüştürme iletişim kutusunu açar.

2. İçinde Formül Başvurularını Dönüştür iletişim kutusunda, seçin Mutlaka Dönüştür seçeneğini ve ardından Tamam'a tıklayın. Bu adım, seçili tüm formüllerdeki hücre başvurularının mutlak başvurular olarak ayarlandığından (% işaretiyle) emin olur. Sonuç olarak, hücreleri transpoze ettiğinizde başvurular değişmez.

3. Şimdi hücreleri tekrar seçin ve Ctrl + C tuşlarına basarak onları kopyalayın. İstenilen yapıştırma konumunda sağ tıklayın ve içerik menüsünden Transpoze seçeneğini Özel Yapıştır alt menüsünden seçin. Bu işlem verinizi transpoze edecek ve doğru formül başvurularını koruyacaktır.

İpucu: Eğer büyük veri tablolarını satırlardan sütunlara hızlıca değiştirmek isterseniz (örneğin, geniş veri tablolarında), Kutools for Excel ayrıca Tablo Boyutlarını Dönüştür özelliğini de sunar. Bu araç, veri bağlantılarını veya biçimlendirmelerini kaybetmeden büyük tabloları hızlıca yeniden yapılandırmak için özellikle kullanışlıdır. Araç sınırlı sayıda gün boyunca ücretsiz deneme sürümü olarak mevcuttur; tüm özellikleri denemek için buradan indirin.

Kutools çözümü, özellikle daha büyük veri hacimleri veya birden fazla formül içeren daha karmaşık elektronik tablolarla sık sık bu tür görevler gerçekleştirdiğinizde en etkilidir. Önlem olarak, transpozasyondan sonra mutlak başvuruların istenip istenmediğini her zaman kontrol edin; başvuruları gerektiği gibi göreceliye geri dönüştürmek için aynı özelliği kullanmanız gerekebilir. Orijinal formülleriniz göreceli ve mutlak başvuruları karıştırıyorsa, dönüşüm ve transpoze işleminden sonra bunları doğruluk açısından gözden geçirin.

VBA Kodu - Formül başvurularını (göreceli veya mutlak) koruyarak hücreleri transpoze etme

Gelişmiş senaryolar için, VBA makrosu yazmak formülleri transpoze ederken orijinal başvuru türlerini korumak için süreci otomatikleştirmenizi sağlar - bunlar göreceli, mutlak veya karışıksa bile. Bu çözüm, makrolara aşina olan kullanıcılar için uygundur ve özellikle büyük veri aralıkları için veya bu işlemin sıkça gerçekleştirildiği durumlarda kullanışlıdır. VBA esneklik sunar, karmaşık başvuru desenlerini barındırır ve çeşitli formül yapılarını doğrudan yönetir.

1. İlk olarak, Excel'de Geliştirici sekmesi henüz görünür değilse etkinleştirin. Geliştirici > Visual Basic giderek VBA düzenleyicisini açın.

2. VBA düzenleyicisinde Ekle > Modül'e tıklayarak yeni bir modül penceresi açın, ardından aşağıdaki VBA kodunu bu pencereye kopyalayıp yapıştırın:

Sub TransposeFormulasPreserveReferences()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destRange As Range
    Dim numRows As Long, numCols As Long
    Dim i As Long, j As Long
    Dim tempArray As Variant
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set sourceRange = Application.InputBox("Select the range you want to transpose", xTitleId, Selection.Address, Type:=8)
    
    If sourceRange Is Nothing Then Exit Sub
    
    numRows = sourceRange.Rows.Count
    numCols = sourceRange.Columns.Count
    
    Set destRange = Application.InputBox("Select the upper-left cell for the transposed output", xTitleId, , Type:=8)
    
    If destRange Is Nothing Then Exit Sub
    
    tempArray = sourceRange.Formula ' Store original formulas
    
    ' Transpose formulas, cell by cell
    For i = 1 To numRows
        For j = 1 To numCols
            destRange.Offset(j - 1, i - 1).Formula = tempArray(i, j)
        Next j
    Next i
End Sub

3. Kodu çalıştırmak için düğmesine tıklayın veya F5tuşuna basın. Talimatları izleyin: transpoze etmek istediğiniz kaynak veriyi (formüller dahil) ve çıktı için başlangıç hücresini seçin. Makro, tüm formülleri kopyalayacak ve transpoze edecek, başvuruları orijinal konumdaki gibi aynı tutacaktır. Formülleriniz göreceli başvurular kullanıyorsa, bağlamın değişebileceğini (sonuç değerleri orijinallerle eşleşmeyebilir) unutmayın, ancak formül metni kendisi ayarlanmayacak ve başvuru türünü koruyacaktır.

Bu yaklaşım özellikle büyük veri setleri, tekrarlanan işlemler veya ince kontrole ihtiyaç duyulduğunda oldukça kullanışlıdır. Bir hata oluşursa (örneğin, yeterli büyüklükte bir hedef alanı seçilmediğinde), makroyu yeniden çalıştırın ve aralık seçimlerinizi dikkatlice kontrol edin.

Özetle, Excel, orijinal formül başvurularını koruyarak verileri transpoze etmek için çeşitli yöntemler sunar - manuel Bul ve Değiştir, Kutools gibi gelişmiş araçlar, VBA otomasyonu ve INDIRECT veya ADDRESS kullanılarak formül tabanlı yaklaşımlar. Bir yöntem seçerken veri boyutunuzu, formül karmaşıklığınızı ve otomasyona karşı manuel kontrole ihtiyacınızı göz önünde bulundurun. Her zaman sonucu iki kez kontrol edin - özellikle göreceli başvurularla - hesaplamaların doğru kaldığından emin olun ve toplu değişiklikler yapmadan veya makroları çalıştırmadan önce çalışma kitabınızın bir yedek kopyasını kaydedin. "ref" hataları veya beklenmedik değerler gibi sorunlarla karşılaşırsanız, hiçbir başvurunun doğru aralığın dışında kalmadığından veya karışık mutlak/göreceli başvuruların yanlış kaydırılmadığından emin olun. Emin değilseniz, işleme güven kazanmak için yöntemi önce küçük bir örnekte deneyin.