Bir sütundaki hücreleri başka bir sütundaki benzersiz değerlere göre nasıl aktarabilirim?
Diyelim ki, iki sütun içeren bir veri aralığınız var, şimdi, aşağıdaki sonucu elde etmek için bir sütundaki hücreleri başka bir sütundaki benzersiz değerlere dayalı olarak yatay satırlara dönüştürmek istiyorsunuz. Excel'de bu sorunu çözmek için iyi fikirleriniz var mı?
Formüller ile benzersiz değerlere göre hücreleri bir sütunda değiştirin
VBA kodu ile benzersiz değerlere göre hücreleri bir sütunda değiştirin
Kutools for Excel ile hücreleri benzersiz değerlere göre bir sütunda değiştirin
Formüller ile benzersiz değerlere göre hücreleri bir sütunda değiştirin
Aşağıdaki dizi formülleriyle, benzersiz değerleri çıkarabilir ve bunlara karşılık gelen verileri yatay satırlara aktarabilirsiniz, lütfen aşağıdaki işlemleri gerçekleştirin:
1. Şu dizi formülünü girin: = ENDEKS ($ A $ 2: $ A $ 16, EŞLEŞTİR (0, EĞERSAY ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) örneğin D2 gibi boş bir hücreye girin ve ÜstKrktr + Ctrl + Enter doğru sonucu almak için anahtarları bir araya getirin, 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 dolgu tutamacını hücrelere sürükleyin, ekran görüntüsüne bakın:
3. Ve sonra bu formülü E2 hücresine girmeye devam edin: =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 basmayı unutma ÜstKrktr + Ctrl + Enter sonucu almak için anahtarlar, ekran görüntüsüne bakın:
not: Yukarıdaki formülde: B2: B16 sırasını değiştirmek istediğiniz sütun verileridir, A2: A16 değerleri temel alarak değiştirmek istediğiniz sütundur ve D2 1. Adımda çıkardığınız benzersiz değeri içerir.
4. Ardından doldurma tutamacını, sırasını değiştirmiş verileri listelemek istediğiniz hücrelerin sağına 0 görüntülenene kadar sürükleyin, ekran görüntüsüne bakın:
5. Ardından, aşağıda gösterilen ekran görüntüsü gibi dönüştürülmüş verileri almak için dolgu tutamacını hücre aralığına sürüklemeye devam edin:
VBA kodu ile benzersiz değerlere göre hücreleri bir sütunda değiştirin
Formüller anlamanız için karmaşık olabilir, burada ihtiyacınız olan istenen sonucu elde etmek için aşağıdaki VBA kodunu çalıştırabilirsiniz.
1. Basılı tutun ALT + F11 tuşlarını açmak için Uygulamalar için Microsoft Visual Basic pencere.
2. tıklayın Ekle > modülve aşağıdaki kodu modül Pencere.
VBA kodu: Bir sütundaki hücreleri başka bir sütundaki benzersiz değerlere göre değiştirin:
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. Daha sonra tuşuna basın. F5 Bu kodu çalıştırmak için bir tuşa basın ve kullanmak istediğiniz veri aralığını seçmenizi hatırlatmak için bir istem kutusu açılır, ekran görüntüsüne bakın:
4. Ve sonra tıklayın OK düğmesi, sonucu koymak için bir hücre seçmenizi hatırlatmak için başka bir istem kutusu açılır, ekran görüntüsüne bakın:
6. tıklayın OK düğmesi ve B sütunundaki veriler A sütunundaki benzersiz değerlere göre aktarılmıştır, ekran görüntüsüne bakın:
Kutools for Excel ile hücreleri benzersiz değerlere göre bir sütunda değiştirin
Eğer varsa Kutools for Excel, birleştirerek Gelişmiş Kombine Satırları ve Bölünmüş hücreler yardımcı programlar, bu görevi herhangi bir formül veya kod olmadan hızlı bir şekilde tamamlayabilirsiniz.
Kutools for Excel : 300'den fazla kullanışlı Excel eklentisi ile 30 günde sınırlama olmaksızın ücretsiz deneyin. |
Kurduktan sonra Kutools for Excellütfen aşağıdaki işlemleri yapın:
1. Kullanmak istediğiniz veri aralığını seçin. (Orijinal verileri saklamak istiyorsanız, lütfen önce verileri kopyalayıp başka bir yere yapıştırın.)
2. Sonra tıklayın Kutools > Birleştir ve Böl > Gelişmiş Kombine Satırları, ekran görüntüsüne bakın:
3. In Sütuna Göre Satırları Birleştirme iletişim kutusu, lütfen aşağıdaki işlemleri yapın:
(1.) Verileri temel alarak dönüştürmek istediğiniz sütun adını tıklayın ve Birincil anahtar;
(2.) Sırasını değiştirmek istediğiniz başka bir sütuna tıklayın ve Birleştirmek daha sonra, boşluk, virgül, noktalı virgül gibi birleştirilmiş verileri ayırmak için bir ayırıcı seçin.
4. Sonra tıklayın Ok düğmesi, B sütunundaki veriler A sütununa göre tek bir hücrede birleştirildi, ekran görüntüsüne bakın:
5. Ve sonra birleştirilmiş hücreleri seçin ve Kutools > Birleştir ve Böl > Bölünmüş hücreler, ekran görüntüsüne bakın:
6. In Bölünmüş hücreler iletişim kutusunu seçin Sütunlara Böl altında Tip seçeneğini 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. Sonra tıklayın Ok düğmesini tıklayın ve bölünme sonucunu açılan iletişim kutusuna koymak için bir hücre seçin, ekran görüntüsüne bakın:
8. tıklayın OKve sonucu ihtiyacınız olduğu kadar alacaksınız. Ekran görüntüsüne bakın:
Kutools for Excel'i şimdi indirin ve ücretsiz deneyin!
Demo: Kutools for Excel ile hücreleri benzersiz değerlere dayalı olarak bir sütunda değiştirin
En İyi Ofis Üretkenlik Araçları
Kutools for Excel ile Excel Becerilerinizi Güçlendirin ve Daha Önce Hiç Olmadığı Gibi Verimliliği Deneyimleyin. Kutools for Excel, Üretkenliği Artırmak ve Zamandan Tasarruf Etmek için 300'den Fazla Gelişmiş Özellik Sunar. En Çok İhtiyacınız Olan Özelliği Almak İçin Buraya Tıklayın...
Office Tab, Office'e Sekmeli Arayüz Getirir ve İşinizi Çok Daha Kolay Hale Getirir
- Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
- Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
- Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!