Çoklu birleştirme aralıklı PivotTable Alanları (Çok dilli değişken tablo başlıklarından)

[Kayhan.tr]

Öncelikle desteğiniz için çok teşekkür ederim.

Cash1, Cash2, Cash3 sayfalarında bulunan Tab_Cash1, Tab_Cash2, Tab_Cash3 tablolarını

PivotTable ve PivotChart sihirbazındaki çoklu birleştirme aralıkları ile birleştirdim ve yukarıdaki PivotTable oluştu.

Sağdaki PivotTable alanlarında görüldüğü gibi pivottable alanları gruplanmış olarak geliyor.

Kasa başlığında : Kasa 1, Kasa 2 ve Kasa 3;

Items başlığında : Tarih, Gelir, Gider, Bakiye

Tutar alanında : Hem tutarlar hem tarihler alt alta

Kasa Adı alanında : Kasa 1, Kasa 2 ve Kasa 3 mevcut.

Items başlığındaki Tarih alanını Satırlar alanına almak istiyorum. Bunun bir yolu var mıdır? Gruplanmış başlıklar içinden başlık seçilerek alanlara taşınabilir mi?

Ayrıca Bakiye alanı için Hesaplanmış alan oluturmak istediğimde de (Bakiye=Gelir-Gider), Alanlar kısmında Gelir, Gider alanlarını göremiyorum. NOT: D1 hücresi TR ise tablo başlıkları türkçe, D1 hücresi EN ise tablo başlıkları ingilizce olarak değişiyor. Bunu toplamda 7 dil olacak şekilde genişletmeyi düşünüyorum. Bundan dolayı Tablo Başlıkları değiştiğinde pivotTable bağlantılarını bozmadığından pivottable alanlarının gruplanmış olarak gelmesi ÖNEMLİ ve gerekli.

 

[muhasebeciyiz]

1. PivotTable Alanlarının Gruplanmış Gelmesi

Çoklu birleştirme aralıkları (Multiple Consolidation Ranges) yöntemiyle PivotTable oluşturduğunda, Excel otomatik olarak alanları gruplanmış başlıklar halinde getirir:

• Kasa → Kasa 1, Kasa 2, Kasa 3
• Items → Tarih, Gelir, Gider, Bakiye
• Tutar → Tutarlar ve tarih alanı alt alta
• Kasa Adı → Kasa 1, Kasa 2, Kasa 3

Bu yöntem klasik PivotTable alanlarının birebir esnekliğini sağlamaz. Yani “Items → Tarih” alanını doğrudan Satırlar alanına taşıyamazsın. Çünkü Excel bu tabloyu özet tablo gibi görse de aslında arka planda bir “konsolide tablo” var.

• Eğer mutlaka “Tarih” alanını bağımsız olarak kullanmak istiyorsan, her bir tabloyu Power Query ile tek tabloya birleştirmen gerekir. Power Query ile Cash1, Cash2, Cash3 tablolarını append (birleştirme) yaparsın. Böylece gerçek bir tek tablo PivotTable elde edersin ve Tarih alanını Satırlara serbestçe koyabilirsin.
• Çoklu birleştirme aralıkları yöntemi kullanmaya devam edersen, bu sınırlamayı aşman mümkün değil. Alanlar grup halinde kalır.

2. Hesaplanmış Alan (Bakiye = Gelir - Gider)

Çoklu aralık birleştirme ile oluşturulmuş PivotTable’da, alan adları gerçek tablo alanı gibi tanınmaz. Bu yüzden “Gelir” ve “Gider” alanları hesaplanmış alan formülünde çıkmaz. Çünkü Excel bunları tek tek alan değil, “Items” grubu içinde listelenmiş kalemler gibi görüyor.

• Yine burada da Power Query ile tabloları birleştirip gerçek alanları kullanırsan, PivotTable içinde “Hesaplanmış Alan” seçeneğinde “Gelir” ve “Gider” alanlarını görebilirsin.
• Alternatif olarak, her tabloda ayrı bir “Bakiye” sütunu ekleyip (Gelir - Gider), konsolidasyona o sütunu da dahil edebilirsin. Böylece PivotTable’da hazır gelir.

3. Çok Dilli Başlıklar (TR / EN / diğer 7 dil)

Bu noktada seçtiğin yöntem kritik:

• Eğer çoklu aralık konsolidasyonu ile devam edersen, alanların gruplanmış kalması sayesinde dil değişiminde bağlantılar bozulmaz, ama esnekliğin çok sınırlı kalır (Tarih alanını satıra koyamamak gibi).
• Eğer Power Query ile tabloyu birleştirirsen, alan adları gerçekten tablo alanı olur. Çok dilli başlık için ya:
  • Power Query’de başlıkları sabit tutup, kullanıcıya farklı dil karşılıklarını slicer/lookup ile gösterebilirsin,
  • veya D1 hücresindeki dile göre PivotTable alan adlarını VBA ile dinamik olarak değiştirebilirsin.

• Çoklu konsolidasyon ile çalışmaya devam edersen, “Tarih” alanını Satırlara taşıman ve hesaplanmış alan kullanman mümkün değil.
• Bu kısıtları aşmak için Power Query ile tabloları tek tabloda birleştirmen gerekir. Çok dilli başlıkları bozmadan da yapılabilir ama biraz ek Power Query/VBA mantığı kurmak lazım.

 

[muhasebeciyiz]

Power Query ile Birleştirme

1. Veri → Veri Al → Tablo/Aralık ile her tabloyu Power Query’e al.
2. Cash1, Cash2, Cash3 sorgularını içeri aktardıktan sonra:
   • Menüden Birleştir → Sorguları ekle (Append Queries) seç.
   • Üç tabloyu tek sorguda birleştir.
     

Sonuçta şöyle bir tablo elde edeceksin:

Kasa	Tarih	Gelir	Gider
Kasa 1	…	…	…
Kasa 2	…	…	…
Kasa 3	…	…	…

Burada “Kasa” alanını istersen Power Query’de ekleyebilirsin (her tabloya ek sütun koyarak).

Power Query içinde yeni bir sütun ekle:

=Bakiye = [Gelir] - [Gider]

Artık birleşik tabloda Bakiye alanı hazır olacak. PivotTable’da ekstra hesap yapmana gerek kalmayacak.

Çok Dilli Başlıklar

Burada iki yöntem var:
Yöntem A – Power Query’de sabit başlıklar + Dil Tablosu

• Power Query’den çıkan birleşik tablo başlıklarını sabit (ör: EN) tut.
• Excel’de ayrı bir “Çeviri Tablosu” yap:

| Alan | TR | EN | DE | FR | ES | … |
|------|----|----|----|----|----|
| Date | Tarih | Date | Datum | Date | Fecha |
| Income | Gelir | Income | Einnahmen | Revenu | Ingreso |
| Expense | Gider | Expense | Ausgaben | Dépense | Gasto |
| Balance | Bakiye | Balance | Saldo | Solde | Saldo |

• PivotTable alan adlarını VBA ile D1 hücresindeki dile göre bu çeviri tablosundan değiştir.

Yöntem B – Power Query’de Dil Seçimli Başlık

• Power Query’de D1 hücresindeki dili oku (Excel.CurrentWorkbook(){[Name="DilSecim"]}[Content]).
• Sütun adlarını bu dile göre yeniden adlandır.

Örn: Eğer D1 = "TR" ise → Tarih, Gelir, Gider, Bakiye;
D1 = "EN" ise → Date, Income, Expense, Balance.

Böylece PivotTable alan adları otomatik o dilde gelir.

5. PivotTable Kurulumu

• Artık tek birleşik tabloyu Excel’e yükle.
• Bu tablodan PivotTable oluşturduğunda:
  • Tarih alanını Satırlara atabilirsin.
  • Kasa alanını Filtre veya Sütunlarda kullanabilirsin.
  • Bakiye alanı otomatik hesaplı çalışır.

Ve en önemlisi: Çok dilli başlıklar değiştiğinde PivotTable bozulmaz.

Senin senaryonda ben Yöntem A’yı öneririm (sabit alanlar + çeviri tablosu). Çünkü daha esnek: 7 dil eklemek kolay olur, PivotTable’lar bozulmaz, sadece başlık metinleri güncellenir.

 

[muhasebeciyiz]

(A) Power Query ile tek tablo + sabit (iç) alan adları
(B) Pivot başlıklarını D1’deki dil koduna göre otomatik çeviren kısa bir VBA yordamı

Böylece:

• “Tarih”i satırlara özgürce atarsın,
• “Bakiye = Gelir – Gider” hazır gelir,
• 7 dile kadar başlıklar D1’e göre değişir ama pivot bağlantıları hiç bozulmaz (çünkü iç alan adları sabit kalır).

A) Power Query (M) — tek tablo + sabit iç alanlar
0) Küçük hazırlık

1. D1 hücresini adlandır: LangCode (örn. TR, EN, …)
2. Bir çeviri tablosu oluştur (ayrı bir sayfada). Adını Tbl_Translations yap (Tablo Nesnesi).
   Örnek sütunlar ve satırlar:

Internal (sabit)	TR	EN	DE	FR	ES	IT	AR
CashName	Kasa	Cash	Kasse	Caisse	Caja	Cassa	صندوق
Date	Tarih	Date	Datum	Date	Fecha	Data	تاريخ
Income	Gelir	Income	Einnahmen	Revenu	Ingreso	Entrate	دخل
Expense	Gider	Expense	Ausgaben	Dépense	Gasto	Uscite	مصروف
Balance	Bakiye	Balance	Saldo	Solde	Saldo	Saldo	رصيد

Not: Bu tablo, kaynak tablolarında başlık hangi dilde gelirse gelsin Power Query’nin onları Internal sabit isimlere dönüştürmesi için. (Pivot tarafında da aynı tabloyu kullanıp başlıkları dile çevireceğiz.)

Kaynak tabloların adları: Tab_Cash1, Tab_Cash2, Tab_Cash3 (sende zaten var).

1) Yardımcı fonksiyon: mevcut başlıkları “Internal” sabitlerine çevir

Power Query’de Yeni Sorgu → Boş Sorgu oluştur, adını fnNormalizeHeaders yap ve aşağıdaki M kodunu yapıştır:

// fnNormalizeHeaders(tbl as table) as table
(let
  fnNormalizeHeaders = (tbl as table) as table =>
  let
    // 1) Çeviri tablosunu ve mevcut sütunları al
    XlatRaw   = Excel.CurrentWorkbook(){[Name="Tbl_Translations"]}[Content],
    Xlat      = Table.TransformColumnTypes(XlatRaw, List.Transform(Table.ColumnNames(XlatRaw), each {_, type text})),
    curNames  = Table.ColumnNames(tbl),

    // 2) Her mevcut başlığın, çeviri tablosunda hangi satıra denk geldiğini bul
    //    (Internal dahil tüm dil sütunlarında arıyoruz)
    ToInternal = List.Transform(
      curNames,
      (c) =>
        let
          hit = Table.SelectRows(Xlat, (r) => List.Contains(Record.ToList(Record.RemoveFields(r, {})), c))
        in
          if Table.RowCount(hit) = 1
          then {c, hit{0}[Internal]}
          else {c, c} // eşleşme yoksa olduğu gibi bırak
    ),

    // 3) Yeniden adlandır ve türleri makulleştir
    Renamed = Table.RenameColumns(tbl, ToInternal, MissingField.Ignore)
  in
    Renamed
in
  fnNormalizeHeaders)

Bu fonksiyon, kaynak tablo başlıkları hangi dilde olursa olsun, onları Internal sütunundaki sabit isimlere dönüştürür: CashName / Date / Income / Expense / Balance.

2) Kaynak tabloları içeri al ve normalize et

Her biri için Veri → Tablo/Aralık ile ayrı sorgu oluştur (veya Boş Sorgu + M kodu).
Aşağıdaki gibi 3 sorgu (adlar: Cash1_q, Cash2_q, Cash3_q):

Cash1_q

let
  Source   = Excel.CurrentWorkbook(){[Name="Tab_Cash1"]}[Content],
  Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
  Normal   = fnNormalizeHeaders(Promoted),
  // Kasa adını sabit iç alan adıyla ekle (eğer kaynakta yoksa)
  WithCash = if List.Contains(Table.ColumnNames(Normal), "CashName")
             then Normal
             else Table.AddColumn(Normal, "CashName", each "Kasa 1", type text),
  Typed = Table.TransformColumnTypes(
            WithCash,
            {{"CashName", type text}, {"Date", type date}, {"Income", type number}, {"Expense", type number}},
            "tr-TR"
          )
in
  Typed

Cash2_q (aynı mantık, sadece Kasa adı farklı)

let
  Source   = Excel.CurrentWorkbook(){[Name="Tab_Cash2"]}[Content],
  Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
  Normal   = fnNormalizeHeaders(Promoted),
  WithCash = if List.Contains(Table.ColumnNames(Normal), "CashName")
             then Normal
             else Table.AddColumn(Normal, "CashName", each "Kasa 2", type text),
  Typed = Table.TransformColumnTypes(
            WithCash,
            {{"CashName", type text}, {"Date", type date}, {"Income", type number}, {"Expense", type number}},
            "tr-TR"
          )
in
  Typed

Cash3_q

let
  Source   = Excel.CurrentWorkbook(){[Name="Tab_Cash3"]}[Content],
  Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
  Normal   = fnNormalizeHeaders(Promoted),
  WithCash = if List.Contains(Table.ColumnNames(Normal), "CashName")
             then Normal
             else Table.AddColumn(Normal, "CashName", each "Kasa 3", type text),
  Typed = Table.TransformColumnTypes(
            WithCash,
            {{"CashName", type text}, {"Date", type date}, {"Income", type number}, {"Expense", type number}},
            "tr-TR"
          )
in
  Typed

Tabloları birleştir + Bakiye sütunu

Yeni bir Ekleme (Append) sorgusu oluştur, adına Cash_All de:

let
  Appended = Table.Combine({Cash1_q, Cash2_q, Cash3_q}),
  WithBal  = Table.AddColumn(Appended, "Balance", each [Income] - [Expense], type number),
  // Tipler güvence
  Typed    = Table.TransformColumnTypes(
               WithBal,
               {{"CashName", type text}, {"Date", type date}, {"Income", type number}, {"Expense", type number}, {"Balance", type number}},
               "tr-TR"
             )
in
  Typed

Yükleme ayarı: Cash_All’ı Tablo olarak sayfaya (ör. Data sayfasına) ve istersen Veri Modeline de yükleyebilirsin. PivotTable’larını Cash_All üzerinden kur.

Artık Pivot’ta:

• Rows (Satırlar): Date
• Columns (Sütunlar): CashName (veya filtre)
• Values (Değerler): Income, Expense, Balance (toplam)
• Tam esneklik var; hesaplanmış alan gerekmez çünkü Balance hazır.

 

[muhasebeciyiz]

VBA — Pivot başlıklarını D1’deki dile göre çevir

Bu makro, PivotTable alanlarının Caption’ını değiştirir; SourceName (iç isim) aynı kaldığı için pivot bağlantıları asla bozulmaz.
Tbl_Translations tablosunu tekrar kullanıyoruz (Internal + diller).

1. VBA Düzenleyici (ALT+F11) → bir Modül ekle, şu kodu yapıştır:

Option Explicit

Private Function GetTranslationDict(langCode As String) As Object
    ' Tbl_Translations: sütunlar = Internal, TR, EN, DE, FR, ES, IT, AR ...
    ' İç isim → hedef dile karşılık gelen başlık
    Dim ws As Worksheet, lo As ListObject, r As ListRow
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("PT") ' fark etmez; tablo hangi sayfadaysa onu seç
    On Error GoTo 0

    ' Çeviri tablosunu çalışma kitabında bul
    Dim loFound As ListObject
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Worksheets
        For Each lo In sht.ListObjects
            If LCase(lo.Name) = LCase("Tbl_Translations") Then
                Set loFound = lo
                Exit For
            End If
        Next lo
        If Not loFound Is Nothing Then Exit For
    Next sht
    If loFound Is Nothing Then
        MsgBox "Tbl_Translations bulunamadı.", vbCritical
        Set GetTranslationDict = dict
        Exit Function
    End If

    Dim internalCol As Long, langCol As Long, i As Long
    internalCol = 0: langCol = 0

    ' Sütun indekslerini bul
    For i = 1 To loFound.ListColumns.Count
        If LCase(loFound.ListColumns(i).Name) = "internal" Then internalCol = i
        If LCase(loFound.ListColumns(i).Name) = LCase(langCode) Then langCol = i
    Next i

    If internalCol = 0 Or langCol = 0 Then
        MsgBox "Tbl_Translations sütunları eksik. (Internal / " & langCode & ")", vbCritical
        Set GetTranslationDict = dict
        Exit Function
    End If

    ' Sözlüğü doldur
    Dim internalName As String, captionName As String
    For Each r In loFound.ListRows
        internalName = Trim(CStr(r.Range.Cells(1, internalCol).Value))
        captionName = Trim(CStr(r.Range.Cells(1, langCol).Value))
        If Len(internalName) > 0 And Len(captionName) > 0 Then
            dict(internalName) = captionName
        End If
    Next r

    Set GetTranslationDict = dict
End Function

Public Sub LocalizeAllPivots()
    Dim lang As String
    lang = Trim(CStr(ThisWorkbook.Names("LangCode").RefersToRange.Value)) ' D1 adı

    If Len(lang) = 0 Then
        MsgBox "Dil kodu (LangCode) boş.", vbExclamation
        Exit Sub
    End If

    Dim dict As Object
    Set dict = GetTranslationDict(lang)

    Dim ws As Worksheet, pt As PivotTable, pf As PivotField
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            ' Alan başlıklarını dil karşılıklarına çevir
            For Each pf In pt.PivotFields
                On Error Resume Next
                If dict.Exists(pf.SourceName) Then
                    pf.Caption = dict(pf.SourceName)
                End If
                On Error GoTo 0
            Next pf
            pt.RefreshTable
        Next pt
    Next ws
End Sub

D1 değiştiğinde otomatik tetikleme istersen, D1’in bulunduğu sayfanın kod penceresine (ör. PT sayfası) şu olayı ekle:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo SafeExit
    If Not Intersect(Target, Range("LangCode")) Is Nothing Then
        Application.EnableEvents = False
        Call LocalizeAllPivots
    End If
SafeExit:
    Application.EnableEvents = True
End Sub

İstersen buton da koyup LocalizeAllPivots’a bağlayabilirsin.

Kullanım akışı (kısaca)

1. Cash_All sorgusunu yenile → tek tablo hazır (CashName, Date, Income, Expense, Balance).
2. PivotTable’ını Cash_All’dan kur.
3. Tbl_Translations’a dillerini ekle.
4. D1 (LangCode) → TR, EN, DE, … yap.
5. LocalizeAllPivots’u çalıştır → Pivot alan başlıkları o dile döner.
   • İç (SourceName) alanlar sabit kaldığı için pivotlar bozulmaz.

Notlar / İpuçları

• Power Query’de başlıklar dil değişse de fnNormalizeHeaders sayesinde iç alanlar daima aynı (Internal).
• Ek alan eklersen (örn. “Tutar”), Tbl_Translations’a bir satır ekleyip Cash_All’a da o sütunu dahil et.
• İstersen Pivot alan sıralamasını dil bazlı da değiştirebilirsin (VBA’da Orientation, Position).
• Tarih gruplama (Yıl/Çeyrek/Ay) gibi klasik Pivot özellikleri artık tam çalışır.

Böylece hem çok dilli başlıklar korunur, hem de istediğin “Tarih’i satıra al”, “Bakiye hesapla” gibi esneklik sağlanır.

 

[Kayhan.tr]

Öncelikle vakit ayırıp tek tek cevapladığınz için çok teşekkür ederim.
Söylediklerinizi aşama aşama yapmaya çalıştım ama dil değiştiğinde yada dosya ilk açıldığında hata veriyor. Mümkünse inceleyebilir misiniz?

 

[muhasebeciyiz]

“kasa bulunamadı” hatasının kökü, dil değişince kaynak sütun adlarının değişmesi ve Power Query adımlarının (özellikle Changed Type, Renamed Columns, vb.) artık bulamadığı isimlere referans vermesi. Bunu yüzde 100 sağlam almak için tek kural:

Power Query’nin ilk işi, gelen başlıkları hangi dilde olursa olsun “iç (sabit) alan adlarına” çevirmek olmalı.
Sonraki tüm adımlar yalnızca bu iç adlarla çalışmalı.

Aşağıya, bu sorunu tamamen çözmek için;

1. Başlıkları ilk adımda normalize eder (TR/EN/… fark etmeksizin),
2. “CashName/Kasa” sütunu yoksa ekler, varsa dokunmaz.
   Not: Kaynak Tablo adlarını (Tab_Cash1, Tab_Cash2, Tab_Cash3) asla çevirmeyin/değiştirmeyin. Sorgular o adlara bağlanıyor.

 

[Kayhan.tr]

Verdiğiniz kodu ekledim. Şu an dil değiştiğinde Pivot Tablo alanları değişiyor fakat pivot tablo başlıklarına bu değişiklik yansımıyor.

 

[muhasebeciyiz]

Denermisiniz Dil değişince otomatik değişir Pivot verisini yenilediğinde Excel bazen başlıkları varsayılana döndürebilir. Her pivot güncellemesinden sonra çeviriyi yeniden uygulatabilirsin.Workbooka kodlar eklendi

Translations tablosunda şu anahtarları mutlaka bulunsun:
Row Labels, Column Labels, Grand Total, (blank), Sum of, Count of, Average of, Min of, Max of
(Bunlar veri alanı başlıklarının ve otomatik etiketlerin doğru çevrilmesi için kilit.)
Veri alanları için (ör. Income, Expense, Balance) “Internal” sütunundaki adlarla bire bir eşleştiğinden emin ol.
Gerekirse “(blank)” satırını boş değerleri çevirmek için ekle (ör. TR: “(boş)”).

 

[Kayhan.tr]

Emeğinize sağlık. ilgi ve desteğiniz için çok teşekkür ederim.