Cách chuyển dòng trùng lặp thành cột trong Excel (4 cách)

Nếu bạn đang tìm kiếm một số cách dễ nhất và hiệu quả nhất để chuyển các hàng trùng lặp sang cột trong Excel, thì bạn đã đến đúng chỗ. Vì vậy, hãy đi sâu vào bài viết chính để biết chi tiết về quy trình chuyển đổi các hàng trùng lặp một cách nhanh chóng.

Tải xuống Workbook

4 cách để chuyển dòng trùng lặp thành cột trong Excel

Ở đây, chúng tôi có tập dữ liệu sau chứa hồ sơ bán hàng của các sản phẩm khác nhau và lưu ý rằng chúng tôi cũng có một số hàng lặp lại. Sử dụng các phương pháp sau, chúng tôi sẽ cố gắng chuyển đổi các hàng trùng lặp thành cột một cách dễ dàng.

Chúng tôi đã sử dụng Microsoft Excel 365 phiên bản ở đây, bạn có thể sử dụng bất kỳ phiên bản nào khác tùy theo sự thuận tiện của bạn.

Phương pháp-1 :Sử dụng định dạng có điều kiện để chuyển đổi các hàng trùng lặp thành cột

Ở đây, chúng tôi sẽ sử dụng Định dạng có điều kiện để đánh dấu các hàng trùng lặp, vì vậy chúng tôi sẽ có thể dễ dàng chuyển đổi các hàng trùng lặp sau đó trong Column1 và Column2 cột.

Các bước :
➤ Chọn phạm vi dữ liệu và sau đó đi tới Trang chủ Tab>> Kiểu Nhóm>> Định dạng có điều kiện Thả xuống>> Đánh dấu quy tắc ô Tùy chọn>> Giá trị trùng lặp Tùy chọn.

Sau đó, Giá trị trùng lặp hộp thoại sẽ xuất hiện.
➤ Chọn Bản sao và Tô màu đỏ nhạt với văn bản màu đỏ đậm các tùy chọn trong Định dạng ô có chứa hộp.
➤ Nhấn OK .

Sau đó, các hàng trùng lặp cho các sản phẩm Quả óc chó và Apple sẽ được đánh dấu, vì vậy chúng tôi có thể dễ dàng chuyển đổi các hàng đầu tiên của mỗi bản sao thành cột.

➤ Chọn Hàng cho các sản phẩm Quả óc chó và Apple bằng cách nhấn CTRL trong khi chọn và sao chép chúng, hãy nhấn CTRL + C .

➤ Để dán các hàng dưới dạng cột, Nhấp chuột phải trên ô đích rồi chọn Chuyển vị từ nhiều Tùy chọn dán khác nhau .

Cuối cùng, bạn sẽ có thể chuyển các hàng trùng lặp thành cột.

Đọc thêm: Chuyển đổi có điều kiện trong Excel (2 Ví dụ)

Phương pháp-2 :Sử dụng Hàm COUNTIF

Trong phần này, chúng tôi sẽ kết hợp tên của nhân viên bán hàng và giá trị bán hàng với Ký hiệu và toán tử trong Extra1 và sau đó chúng tôi sẽ đếm số lần lặp lại của các hàng bằng cách sử dụng hàm COUNTIF trong Extra2 cột. Cuối cùng, chúng tôi sẽ chuyển đổi các hàng lặp lại thành cột bằng cách sử dụng Transpose tùy chọn.

Các bước :
➤ Nhập công thức sau vào ô D4 để kết hợp tất cả các ô của mỗi hàng.

Đây, B4 là tên của Người bán hàng , C4 là Bán hàng giá trị và & sẽ tham gia cùng họ.

➤ Nhấn ENTER và kéo xuống Xử lý điền công cụ.

Sau đó, bạn sẽ nhận được sự kết hợp của các ô của mỗi hàng trong Extra1 cột.

➤ Để đếm số lần lặp lại các hàng của Extra1 viết công thức sau trong ô E4 .

Đây, D4 là tiêu chí và $ D $ 4:D4 là phạm vi và phạm vi này sẽ từ hàng bắt đầu đến hàng mà công thức được sử dụng, chẳng hạn như đối với Hàng 8 phạm vi sẽ là $ D $ 4:D8 .

➤ Nhấn ENTER và kéo xuống Xử lý điền công cụ.

Sau đó, chúng tôi sẽ có số lần lặp lại trong Extra2 cột. Đây, 2 cho Lara và Howard có nghĩa là chúng đã xuất hiện hai lần với giá trị bán hàng của chúng trong tập dữ liệu này và vì vậy bây giờ chúng tôi sẽ chuyển đổi các hàng này thành cột.

➤ Chọn Hàng cho Lara và Howard bằng cách nhấn CTRL trong khi chọn và sao chép chúng, hãy nhấn CTRL + C .

➤ Để dán các hàng dưới dạng cột, Nhấp chuột phải trên ô đích rồi chọn Chuyển vị từ nhiều Tùy chọn dán khác nhau .

Cuối cùng, chúng tôi sẽ có thể chuyển đổi các hàng trùng lặp cho Lara và Howard dưới dạng cột.

Đọc thêm: Cách chuyển đổi hàng thành cột trong Excel (5 phương pháp hữu ích)

Bài đọc tương tự

• Cách chuyển đổi mỗi n hàng thành cột trong Excel (2 phương pháp dễ dàng)
• Truy vấn Excel Power:Chuyển đổi hàng thành cột (Hướng dẫn từng bước)
• VBA trong Excel:Chuyển đổi Nhiều Hàng trong Nhóm thành Cột
• Chuyển đổi Nhiều Cột thành Một Cột trong Excel (3 Phương pháp Tiện dụng)

Phương pháp-3 :Sử dụng Kết hợp Hàm IF và COUNTIFS

Ở đây, chúng tôi sẽ sử dụng IF và COUNTIFS các chức năng để phát hiện xem hàng có trùng lặp hay không. Bằng cách này, chúng tôi có thể dễ dàng xác định các hàng trùng lặp và sau đó chuyển đổi chúng thành các cột.

Các bước :
➤ Nhập công thức sau vào ô E4 .

Đây, $ B $ 4:$ B $ 13 , $ C $ 4:$ C $ 13 và $ D $ 4:$ D $ 13 là phạm vi đầu tiên, thứ hai và thứ ba. $ B4 , $ C4 và $ D4 là tiêu chí thứ nhất, thứ hai và thứ ba.

• COUNTIFS ($ B $ 4:$ B $ 13, $ B4, $ C $ 4:$ C $ 13, $ C4, $ D $ 4:$ D $ 13, $ D4) → trả về một số đại diện cho số lần lặp lại của các hàng.
  Đầu ra → 2

• COUNTIFS ($ B $ 4:$ B $ 13, $ B4, $ C $ 4:$ C $ 13, $ C4, $ D $ 4:$ D $ 13, $ D4)> 1 trở thành
  2> 1 → trả về TRUE để đáp ứng điều kiện nếu không thì FALSE .
  Đầu ra → ĐÚNG

• IF (COUNTIFS ($ B $ 4:$ B $ 13, $ B4, $ C $ 4:$ C $ 13, $ C4, $ D $ 4:$ D $ 13, $ D4)> 1, “Hàng lặp lại” , “”) trở thành
  IF (TRUE, “Hàng lặp lại”, “”) → trả về chuỗi “Hàng Lặp lại” cho TRUE , nếu không thì là một ô trống.
  Đầu ra → Hàng lặp lại

➤ Nhấn ENTER và kéo xuống Xử lý điền công cụ.

Kết quả là chúng tôi nhận được chuỗi văn bản Hàng lặp lại cho các hàng trùng lặp.

➤ Chọn Hàng cho các sản phẩm Quả óc chó và Apple bằng cách nhấn CTRL trong khi chọn và sao chép chúng, hãy nhấn CTRL + C .

➤ Để dán các hàng dưới dạng cột, Nhấp chuột phải trên ô đích rồi chọn Chuyển vị từ nhiều Tùy chọn dán khác nhau .

Cuối cùng, chúng tôi nhận được các hàng trùng lặp được hoán vị như hình sau.

Đọc thêm: Cách chuyển đổi hàng thành cột dựa trên tiêu chí trong Excel (2 cách)

Phương pháp-4 :Sử dụng mã VBA để chuyển dòng trùng lặp thành cột trong Excel

Trong phần này, chúng tôi sẽ sử dụng VBA mã để chuyển đổi các hàng trùng lặp cho các sản phẩm Walnut và Apple thành các cột.

Các bước :
➤ Đi tới Nhà phát triển Tab>> Visual Basic Tùy chọn.

Sau đó, Trình chỉnh sửa Visual Basic sẽ mở ra.
➤ Đi tới Chèn Tab>> Mô-đun Tùy chọn.

Sau đó, một Mô-đun sẽ được tạo.

➤ Viết đoạn mã sau

Sub transform_same_rows()
Dim sht As Worksheet
Dim item, extra_column As Double
Dim storage_object As Object
Set storage_object = CreateObject("scripting.dictionary")
Set sht = Worksheets("VBA")
Total_column = 3
For increment = 4 To 13
item = Join(Application.Transpose(Application.Transpose( _
sht.Cells(increment, 2).Resize(1, Total_column))), "*")
If Not storage_object.exists(item) Then storage_object.Add item, New Collection
storage_object(item).Add sht.Cells(increment, Total_column + 1).Value
sht.Rows(increment).ClearContents
Next increment
increment = 4
For Each item In storage_object
sht.Cells(increment, 2).Resize(1, Total_column).Value = Split(item, "*")
extra_column = Total_column + 1
For Each str_value In storage_object(item)
sht.Cells(increment, extra_column) = str_value
extra_column = extra_column + 1
Next str_value
increment = increment + 1
Next item
For increment = 4 To 13
If sht.Cells(increment, 5).Value = sht.Cells(increment, 4).Value Then
sht.Range(Cells(increment, 2), Cells(increment, 4)).Copy
sht.Cells(increment, 6).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=True
End If
Next increment
End Sub

Ở đây, chúng tôi đã khai báo sht dưới dạng Trang tính , mục , extra_column dưới dạng Double , storage_object dưới dạng Đối tượng rồi đặt sht vào trang tính VBA và chỉ định Total_column đến 3 .
Storage_object được đặt thành Từ điển đối tượng sẽ giúp chúng tôi lưu trữ các mục ở đây.
Sau đó, chúng tôi đã sử dụng vòng lặp FOR cho một phạm vi tăng từ hàng 4 đến 13 và sau đó sử dụng chức năng TRANSPOSE hai lần rồi đến hàm JOIN chúng tôi đã kết hợp tất cả các ô của các hàng bằng dấu phân cách * vào biến item . Sau đó, chúng tôi đã thêm các mục vào storage_object và sau đó chúng tôi xóa tất cả các hàng.
Sau đó, các hàng duy nhất sẽ được nhập vào ba cột bằng cách sử dụng hàm SPLIT và sau đó giá trị bán hàng cho các hàng trùng lặp sẽ được thêm vào cột liền kề của Bán hàng cột.
Sau đó, sử dụng câu lệnh IF chúng tôi sẽ kiểm tra giá trị bán hàng thành giá trị của các ô liền kề và để đáp ứng điều kiện, chúng tôi sẽ sao chép hàng tương ứng đó và sau đó chuyển đổi hàng này.

➤ Nhấn F5 .
Sau đó, chúng tôi sẽ có thể xóa các hàng trùng lặp khỏi tập dữ liệu cùng với việc chuyển đổi các hàng trùng lặp.

Đọc thêm: Cách chuyển đổi hàng thành cột bằng Excel VBA (4 ví dụ lý tưởng)

Phần thực hành

Để tự mình thực hành, chúng tôi đã cung cấp một Thực hành như bên dưới trong trang tính có tên Thực hành . Hãy làm điều đó một mình.

Kết luận

Trong bài viết này, chúng tôi đã cố gắng đề cập đến các cách chuyển đổi các hàng trùng lặp sang cột trong Excel. Hy vọng bạn sẽ thấy nó hữu ích. Nếu bạn có bất kỳ đề xuất hoặc câu hỏi nào, hãy chia sẻ chúng trong phần bình luận.

Bài đọc thêm

• Cách Chuyển đổi trong Excel (5 Cách Dễ dàng)
• Chuyển đổi Nhiều Hàng trong Nhóm thành Cột trong Excel
• Cách chuyển đổi các cột thành hàng trong Excel (6 phương pháp)
• Chuyển đổi Cột thành Hàng trong Excel bằng Power Query
• Cách chuyển đổi cột thành hàng trong Excel dựa trên giá trị ô
• VBA để chuyển đổi nhiều cột thành hàng trong Excel (2 phương pháp)
• Cách chuyển đổi cột thành danh sách được phân tách bằng dấu phẩy với dấu ngoặc kép
• Cách chuyển đổi vị trí trong Excel VBA (3 phương pháp)