Başka bir sütundaki benzersiz değerlere göre bir sütundaki hücreler nasıl transpoze edilir?
Diyelim ki iki sütundan oluşan bir veri aralığınız var ve bu sütunlardan birindeki hücreleri, diğer sütundaki benzersiz değerlere göre yatay satırlara dönüştürmek istiyorsunuz. Excel’de aşağıdaki sonucu elde etmek için bu sorunu çözmek üzere akıllıca fikirleriniz var mı?
Formüllerle bir sütundaki hücreleri başka bir sütundaki benzersiz değerlere göre transpoze etme
VBA koduyla bir sütundaki hücreleri başka bir sütundaki benzersiz değerlere göre transpoze etme
Bir sütundaki hücreleri başka bir sütundaki benzersiz değerlere göre Kutools for Excel ile transpoze etme
Formüllerle bir sütundaki hücreleri başka bir sütundaki benzersiz değerlere göre transpoze etme
Benzersiz değerleri ayıklayıp bunlara karşılık gelen verileri yatay satırlara transpoze etmek için aşağıdaki dizi formüllerini kullanabilirsiniz. Lütfen aşağıdaki adımları izleyin:
1. Bu dizi formülünü boş bir hücreye (örneğin D2) girin:=INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) ve 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 hemen üstündeki hücredir.
2. Ardından tüm benzersiz değerleri çıkarmak için dolgu tutamacını aşağı doğru sürükleyin; ekran görüntüsüne bakın:
3. Ardından E2 hücresine aşağıdaki formülü 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, değerlerin transpozuna dayanacağınız sütundur; ve D2, 1. Adımda (1) çıkardığınız benzersiz değeri içerir.
4.Ardından transpoze edilmiş verileri listelemek istediğiniz hücrelere doğru dolgu tutamacını 0 görüntülenene kadar sağa doğru sürükleyin; ekran görüntüsüne bakın:
5. Ardından, aşağıdaki ekran görüntüsünde gösterildiği gibi transpoze edilmiş verileri almak için dolgu tutamacını hücre aralığı boyunca aşağı doğru sürüklemeye devam edin:
VBA koduyla bir sütundaki hücreleri başka bir sütundaki benzersiz değerlere göre transpoze etme
Formüller sizin için karmaşık gelebilir; bu durumda istediğiniz sonucu elde etmek için aşağıdaki VBA kodunu çalıştırabilirsiniz.
1. ALT + F11 tuşlarına basarak Microsoft Visual Basic for Applications penceresini açın.
2. Ekle > Modül seçeneğine tıklayın ve aşağıdaki kodu Modül penceresine yapıştırın.
VBA kodu: Bir sütundaki hücreleri başka bir sütundaki benzersiz değerlere göre 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 Sub3.Ardından bu kodu çalıştırmak için F5tuşuna basın; ardından kullanmak istediğiniz Veri Aralığı’yi seçmeniz için bir uyarı kutusu açılacaktır; ekran görüntüsüne bakın:
4. Ardından, sonuçların yerleştirileceği hücreyi seçmeniz için başka bir uyarı kutusu açılacaktır; Tamam düğmesine tıklayın. Ekran görüntüsüne bakın:
6.Tamamdüğmesine tıklayın; B sütunundaki veriler A sütunundaki benzersiz değerlere göre transpoze edilmiş olacaktır; ekran görüntüsüne bakın:
Bir sütundaki hücreleri başka bir sütundaki benzersiz değerlere göre Kutools for Excel ile transpoze etme
Eğer Kutools for Excel’e sahipseniz, Gelişmiş Satırları Birleştir ve Hücreleri Böl araçlarını bir araya getirerek bu görevi herhangi bir formül veya kod kullanmadan hızla tamamlayabilirsiniz.
Kutools for Excel yüklendikten sonra Kutools for Excel, lütfen aşağıdaki gibi yapın:
1. Kullanmak istediğiniz veri aralığını seçin. (Orijinal verileri korumak istiyorsanız, öncelikle verileri başka bir konuma kopyalayıp yapıştırın.)
2. Ardından Kutools > Birleştir ve Ayır > Gelişmiş Satırları Birleştir seçeneğine tıklayın; ekran görüntüsüne bakın:
3.Satırları Birleştir Based on Columniletişim kutusunda lütfen aşağıdaki işlemleri yapın:
(1.) Transpoze edilecek verilere dayanmak istediğiniz sütun adını tıklayın ve Birincil Anahtarseçeneğini belirleyin;
(2.) Transpoze etmek istediğiniz diğer sütuna tıklayın ve birleştirilen verileri ayırmak için boşluk, virgül veya noktalı virgül gibi bir ayırıcı seçmek üzere Birleştir seçeneğine tıklayın.
4. Ardından Tamam düğmesine tıklayın; B sütunundaki veriler, A sütununa göre tek bir hücrede birleştirilecektir. Ekran görüntüsüne bakın:
5. Ardından birleştirilmiş hücreleri seçin ve Kutools > Birleştir ve Ayır > Hücreleri Böl seçeneğine tıklayın; ekran görüntüsüne bakın:
6.Hücreleri Böliletişim kutusunda Sütunlara Bölseçeneğini Türaltında seçin ve ardından birleştirilmiş verilerinizi ayıran ayırıcıyı belirleyin; 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üş sonucun yerleştirileceği hücreyi seçin; ekran görüntüsüne bakın:
8.Tamam seçeneğine tıklayın, ihtiyacınız olan sonucu hemen elde edin! Ekran görüntüsüne bakın:
Kutools for Excel’i hemen indirin ve ücretsiz deneyin!
Demo: Bir sütundaki hücreleri başka bir sütundaki benzersiz değerlere göre Kutools for Excel ile transpoze etme
En İyi Office Üretkenlik Araçları
Excel becerilerinizi Kutools for Excel ile geliştirin ve verimlilikte hiç yaşamadığınız bir deneyim yaşayın.Kutools for Excel, üretkenliği artırmak ve Zamanı Kaydet için 300’den fazla gelişmiş özelliğe sahiptir.İhtiyacınız olan özelliği hemen edinmek için buraya tıklayın…
Office Tab, Office uygulamalarına sekmeli bir arayüz kazandırarak işlerinizi çok daha kolay hale getirir.
- Word, Excel, PowerPoint, Publisher, Access, Visio ve Project'te sekmeli düzenleme ve okuma özelliğini etkinleştirin.
- Belgeleri yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açıp oluşturun.
- Günlük üretkenliğinizi %50 artırır ve her gün yüzlerce fare tıklamasından tasarruf etmenizi sağlar!
Tüm Kutools eklentileri — tek bir kurulum dosyasında!
Kutools for Office paketi, Excel, Word, Outlook ve PowerPoint eklentilerini ve ayrıca Office Tab Pro’yu içeren kapsamlı bir çözümdür; Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi bir arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans— birkaç dakikada hazır (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı üretkenlik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi fiyat performans— ayrı ayrı eklenti satın almaya göre tasarruf sağlar