Özetlenemeyenler (Unpivot)

Tanım

Pivot tablolar (pivottables) veri analizinde güçlü bir araçtır. Bununla birlikte, bazen tüm benzer değerlerin aynı sütunda olması için normal bir tablo formatına koymak için döndürülen verileri (aksi takdirde "düzleştirme" veya verileri özetleme olarak da bilinir) özetlemeniz (unpivot) yapmanız gerekir. Bu, örneğin yeni veya değiştirilmiş bir grafik veya rapor oluşturmak için gereklidir.

Bu dönüşümü Excel'de Power Query aracını kullanarak gerçekleştirebilirsiniz, ancak hızlı değildir ve pek kullanışlı değildir. Özet verileri Power Query olmadan kullanıma sunmak için farklı bir yaklaşım deneyelim.

Sorunun formülasyonu

Bazı veri kümelerini pivot tablodan düz tabloya (Unpivot) dönüştürmemiz gerektiğini düşünün:

Böyle bir görev bir makro yardımıyla daha iyi çözülür. Veri içeren bir çapraz tabloyu (çapraz tablo) normal (düz) tablo formatına dönüştürebilecek kendi prosedürümüzü yazalım, bu bize tekrar izin verecek özet tablolar obraz değiştirilmiş veya eklenen verilerle.

UnPivotTable prosedürü için VBA kodu

Visual Basic düzenleyicisini açın (Servis - Makro - Düzenleyici Visual Basic), yeni modüllü takın (Insert - Module) ve bu makronun metnini buraya kopyalayın:

Sub UnPivotTable()
  'moonexcel.com.ua  
  Dim InVal    As Variant
  Dim OutVal() As Variant
  Dim j, k, i  As Long
  Dim NewSheet
  
  i = 1
  InVal = Selection.Formula
  ReDim OutVal(1 To Selection.Count, 1 To 3)
  
  For j = 2 To UBound(InVal, 1)
    For k = 2 To UBound(InVal, 2)
      If InVal(j, k) <> "" Then
        OutVal(i, 1) = InVal(j, 1)
        OutVal(i, 2) = InVal(1, k)
        OutVal(i, 3) = InVal(j, k)
        i = i + 1
      End If
    Next k
  Next j
  
  Set NewSheet = Worksheets.Add
  NewSheet.Range("A1").Resize(UBound(OutVal, 1), 3).Value = OutVal
End Sub

Bundan sonra, VBA editörünü kapatabilir, Excel'ye dönebilir, kaynak tabloyu seçebilir (tamamen, başlık ve ülkelerin bulunduğu ilk sütunla birlikte) ve makromuzu menüden çalıştırabilirsiniz. Hizmet - Makro - Makrolar (Tools - Macro - Macros) (ALT+F8) .

Makro, çalışma kitabına yeni bir sayfa ekleyecek ve üzerinde seçilen tablonun yeni, yeniden yapılandırılmış bir sürümünü oluşturacaktır. Büyük tabloları işlemek ve analiz etmek için Excel araçlarının tüm cephaneliğini kullanarak böyle bir tabloyla "tam programla" çalışabilirsiniz.

İlgili Makaleler: