Başka bir sütundaki benzersiz değerlere göre tek bir sütundaki hücreleri nasıl transpoze edebilirim?

Diyelim ki, iki sütun içeren bir veri aralığınız var ve şimdi bir sütundaki hücreleri başka bir sütundaki benzersiz değerlere göre yatay satırlara transpoze etmek istiyorsunuz. Bu sonucu elde etmek için Excel'de bu sorunu çözmek için iyi bir fikriniz var mı?

Formüllerle benzersiz değerlere göre tek bir sütundaki hücreleri transpoze etme

VBA koduyla benzersiz değerlere göre tek bir sütundaki hücreleri transpoze etme

Kutools for Excel ile benzersiz değerlere göre tek bir sütundaki hücreleri transpoze etme

Formüllerle benzersiz değerlere göre tek bir sütundaki hücreleri transpoze etme

Aşağıdaki dizi formülleriyle, benzersiz değerleri çıkarabilir ve bunlara karşılık gelen verileri yatay satırlara transpoze edebilirsiniz, lütfen şu adımları izleyin:

1. Boş bir hücreye, örneğin D2'ye şu dizi formülünü girin: =INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) ve ardından doğru sonucu almak için Shift + Ctrl + Enter tuşlarına birlikte basın, ekran görüntüsüne bakın:

Not: Yukarıdaki formülde, A2:A16, benzersiz değerleri listelemek istediğiniz sütundur ve D1, bu formül hücresinin üstündeki hücredir.

2. Ardından tüm benzersiz değerleri çıkarmak için hücreler boyunca doldurma tutamacını aşağı çekin, ekran görüntüsüne bakın:

3. Ve ardından şu formülü E2 hücresine girin: =IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0), ve sonucu almak için Shift + Ctrl + Enter tuşlarına basmayı unutmayın, ekran görüntüsüne bakın:

Not: Yukarıdaki formülde: B2:B16, transpoze etmek istediğiniz sütun verisidir, A2:A16, transpoze etmek istediğiniz değerlere dayanan sütundur ve D2, Adım 1'de çıkardığınız benzersiz değeri içerir.

4. Ardından, 0 görüntülene kadar transpoze edilmiş verileri listelemek istediğiniz hücrelerin sağına doğru doldurma tutamacını sürükleyin, ekran görüntüsüne bakın:

5. Ve ardından transpoze edilmiş verileri almak için hücrelerin aralığı boyunca aşağıya doğru doldurma tutamacını sürüklemeye devam edin, aşağıdaki ekran görüntüsünde gösterildiği gibi:

VBA koduyla benzersiz değerlere göre tek bir sütundaki hücreleri transpoze etme

Belki formüller sizin için anlamakta zor geliyor, burada istediğiniz sonucu elde etmek için aşağıdaki VBA kodunu çalıştırabilirsiniz.

1. ALT + F11 tuşlarını basılı tutarak Microsoft Visual Basic for Applications penceresini açın.

2. Ekle > Modül'e tıklayın ve aşağıdaki kodu Modül Penceresine yapıştırın.

VBA kodu: Başka bir sütundaki benzersiz değerlere göre tek bir sütundaki hücreleri transpoze etme:

Sub transposeunique()
'updateby Extendoffice
    Dim xLRow As Long
    Dim i As Long
    Dim xCrit As String
    Dim xCol  As New Collection
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    Dim xCount As Long
    Dim xVRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count <> 2) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xLRow = xRg.Rows.Count
    For i = 2 To xLRow
        xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
    Next
    Application.ScreenUpdating = False
    For i = 1 To xCol.Count
        xCrit = xCol.Item(i)
        xOutRg.Offset(i, 0) = xCrit
        xRg.AutoFilter Field:=1, Criteria1:=xCrit
        Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
        If xVRg.Count > xCount Then xCount = xVRg.Count
        xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
        xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next
    xOutRg = xRg.Cells(1, 1)
    xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
    xRg.Rows(1).Copy
    xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
    xRg.AutoFilter
    Application.ScreenUpdating = True
End Sub

3. Ardından bu kodu çalıştırmak için F5 tuşuna basın ve size kullanmak istediğiniz veri aralığını seçmenizi hatırlatmak için bir uyarı kutusu açılacaktır, ekran görüntüsüne bakın:

4. Ve ardından Tamam düğmesine tıklayın, size sonuçları yerleştireceğiniz bir hücre seçmenizi hatırlatmak için başka bir uyarı kutusu açılacaktır, ekran görüntüsüne bakın:

6. Tamam düğmesine tıklayın ve B sütunundaki veriler A sütunundaki benzersiz değerlere göre transpoze edilmiştir, ekran görüntüsüne bakın:

Kutools for Excel ile benzersiz değerlere göre tek bir sütundaki hücreleri transpoze etme

Eğer Kutools for Excel'iniz varsa, Gelişmiş Satırları Birleştir ve Hücreleri Böl araçlarını birleştirerek, herhangi bir formül veya kod olmadan bu görevi hızlıca tamamlayabilirsiniz.

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

1. Kullanmak istediğiniz veri aralığını seçin. (Eğer orijinal verileri korumak istiyorsanız, lütfen verileri önce başka bir konuma kopyalayıp yapıştırın.)

2. Ardından Kutools > Birleştir ve Böl > Gelişmiş Satırları Birleştir'e tıklayın, ekran görüntüsüne bakın:

3. Sütuna Göre Satırları Birleştir iletişim kutusunda, lütfen aşağıdaki işlemleri yapın:

(1.) Verileri transpoze etmek istediğiniz sütun adına tıklayın ve Anahtar Seç'i seçin;

(2.) Transpoze etmek istediğiniz başka bir sütuna tıklayın ve Birleştir'e tıklayın, ardından birleştirilmiş verileri ayırmak için bir ayırıcı seçin, örneğin boşluk, virgül, noktalı virgül.

4. Ardından Tamam düğmesine tıklayın, B sütunundaki veriler A sütununa göre tek bir hücrede birleştirilmiştir, ekran görüntüsüne bakın:

5. Ve ardından birleştirilmiş hücreleri seçin ve Kutools > Birleştir ve Böl > Hücreleri Böl'e tıklayın, ekran görüntüsüne bakın:

6. Hücreleri Böl iletişim kutusunda, Tür seçeneği altında Sütunlara Böl'ü seçin ve ardından birleştirilmiş verilerinizi ayıran ayırıcıyı seçin, ekran görüntüsüne bakın:

7. Ardından Tamam düğmesine tıklayın ve açılan iletişim kutusunda bölünmüş sonucu yerleştirmek için bir hücre seçin, ekran görüntüsüne bakın:

8. Tamam'a tıklayın ve ihtiyacınız olan sonucu elde edeceksiniz. Ekran görüntüsüne bakın:

Kutools for Excel'i Şimdi İndirin ve Ücretsiz Deneyin!

Demo: Kutools for Excel ile benzersiz değerlere göre tek bir sütundaki hücreleri transpoze etme