Thành Thạo Excel: 4 Phương Pháp Đã Chứng Minh Chuyển Đổi Hàng Trùng Lặp Thành Cột

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

Tải Xuống Workbook

4 Cách Chuyển Hàng Trùng Lặp Thành Cột Trong Excel

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

Chúng tôi đã sử dụng phiên bản Microsoft Excel 365 ở đây, bạn có thể sử dụng bất kỳ phiên bản nào khác 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 Hàng Trùng Lặp Thành Cột

Ở đây, chúng ta sẽ sử dụng Định dạng có điều kiện (Conditional Formatting) để làm nổi bật các hàng trùng lặp, vì vậy chúng ta sẽ có thể chuyển các hàng trùng lặp dễ dàng sau đó vào các cột Cột 1 (Column1) và Cột 2 (Column2).

Các bước:
➤ Chọn phạm vi dữ liệu sau đó đi đến thẻ Trang chủ (Home) >> Nhóm Kiểu (Styles) >> Thả xuống Định dạng có điều kiện (Conditional Formatting) >> Tùy chọn Quy tắc làm nổi bật ô (Highlight Cells Rules) >> Tùy chọn Giá trị trùng lặp (Duplicate Values).

Sau đó, hộp thoại Giá trị trùng lặp (Duplicate Values) sẽ xuất hiện.
➤ Chọn Trùng lặp (Duplicate) và tùy chọn Màu đỏ nhạt với chữ đỏ đậm (Light Red Fill with Dark Red Text) trong hộp Định dạng ô chứa (Format cells that contain).
➤ Nhấn OK.

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

➤ Chọn các Hàng cho sản phẩm Hạt óc chó và Táo bằng cách nhấn CTRL khi chọn chúng và để sao chép chúng nhấn CTRL+C.

➤ Để dán các hàng thành cột Chuột phải (Right-Click) vào ô đích và sau đó chọn tùy chọn Chuyển vị (Transpose) từ các Tùy chọn dán (Paste Options) khác nhau.

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

Đọc thêm: Chuyển Vị 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 ta sẽ kết hợp tên nhân viên bán hàng và giá trị doanh số với toán tử Ampersand trong cột Extra1, sau đó chúng ta 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 cột Extra2. Cuối cùng, chúng ta sẽ chuyển các hàng lặp lại thành cột sử dụng tùy chọn Chuyển vị (Transpose).

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

Ở đây, B4 là tên Nhân viên bán hàng (SalesPerson), C4 là giá trị Doanh số (Sales), và & sẽ nối chúng.

➤ Nhấn ENTER và kéo xuống công cụ Fill Handle.

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

➤ Để đếm số lần lặp lại của các hàng của cột Extra1, hãy 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 cho Hàng 8 phạm vi sẽ là $D$4:D8.

➤ Nhấn ENTER và kéo xuống công cụ Fill Handle.

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

➤ Chọn các Hàng cho Lara và Howard bằng cách nhấn CTRL khi chọn chúng và để sao chép chúng nhấn CTRL+C.

➤ Để dán các hàng thành cột Chuột phải (Right-Click) vào ô đích và sau đó chọn tùy chọn Chuyển vị (Transpose) từ các Tùy chọn dán (Paste Options) khác nhau.

Cuối cùng, chúng ta sẽ có thể chuyển hàng trùng lặp cho Lara và Howard thành cột.

Đọc thêm: Cách Chuyển Hàng Thành Cột Trong Excel (5 Phương Pháp Hữu Ích)

Đọc Tương Tự

• Cách Chuyển Mỗi n Hàng Thành Cột Trong Excel (2 Phương Pháp Dễ Dàng)
• Excel Power Query: Chuyển Hàng Thành Cột (Hướng Dẫn Bước Đi Từng Bước)
• Excel VBA: Chuyển Nhiều Hàng Trong Nhóm Thành Cột
• Chuyển Nhiều Cột Thành Một Cột Trong Excel (3 Phương Pháp Tiện Lợi)

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

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

Các bước:
➤ Gõ công thức sau trong ô E4.

Ở đây, $B$4:$B$13, $C$4:$C$13, và $D$4:$D$13 là phạm vi thứ nhất, 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.
  Kết quả → 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 vì thỏa mãn điều kiện ngược lại FALSE.
  Kết quả → TRUE

• IF(COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4)>1, “Repeated Row”, “”) trở thành
  IF(TRUE, “Repeated Row”, “”) → trả về chuỗi “Repeated Row” cho TRUE, ngược lại để trống.
  Kết quả → Repeated Row

➤ Nhấn ENTER và kéo xuống công cụ Fill Handle.

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

➤ Chọn các Hàng cho sản phẩm Hạt óc chó và Táo bằng cách nhấn CTRL khi chọn chúng và để sao chép chúng nhấn CTRL+C.

➤ Để dán các hàng thành cột Chuột phải (Right-Click) vào ô đích và sau đó chọn tùy chọn Chuyển vị (Transpose) từ các Tùy chọn dán (Paste Options) khác nhau.

Cuối cùng, chúng ta nhận được các hàng trùng lặp đã chuyển vị như hình sau.

Đọc thêm: Cách Chuyển 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 Hàng Trùng Lặp Thành Cột Trong Excel

Trong phần này, chúng ta sẽ sử dụng một mã VBA để chuyển các hàng trùng lặp cho sản phẩm Hạt óc chó và Táo thành cột.

Các bước:
➤ Đi đến thẻ Nhà phát triển (Developer) >> Tùy chọn Visual Basic.

Sau đó, Trình soạn thảo Visual Basic (Visual Basic Editor) sẽ mở ra.
➤ Đi đến thẻ Chèn (Insert) >> Tùy chọn Mô-đun (Module).

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

➤ Viết 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 ta đã khai báo sht là Worksheet, item, extra_column là Double, storage_object là Object, và sau đó đặt sht thành trang tính VBA và gán Total_column cho 3.
storage_object được đặt thành đối tượng Dictionary sẽ giúp chúng ta lưu trữ các mục ở đây.
Sau đó, chúng ta đã sử dụng Vòng lặp FOR cho phạm vi tăng từ hàng 4 đến 13, và sau đó sử dụng hàm TRANSPOSE hai lần và sau đó hàm JOIN chúng ta đã kết hợp tất cả các ô của các hàng với dấu phân tách * vào biến item. Sau đó, chúng ta đã thêm các mục vào storage_object và sau đó 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ị doanh số cho các hàng trùng lặp sẽ được thêm vào cột liền kề của cột Doanh số (Sales).
Sau đó sử dụng Câu lệnh IF chúng ta sẽ kiểm tra giá trị doanh số với giá trị của ô liền kề và khi thỏa mãn điều kiện chúng ta sẽ sao chép hàng tương ứng đó và chuyển vị hàng này.

➤ Nhấn F5.
Sau đó, chúng ta sẽ có thể dọn dẹp các hàng trùng lặp từ tập dữ liệu cùng với việc chuyển vị các hàng trùng lặp.

Đọc thêm: Cách Chuyển Hàng Thành Cột Sử Dụng Excel VBA (4 Ví Dụ Lý Tưởng)

Phần Thực Hành

Để tự thực hành, chúng tôi đã cung cấp phần Thực hành (Practice) như bên dưới trong trang tính tên Thực hành (Practice). Vui lòng tự làm.

Kết Luận

Trong bài viết này, chúng tôi đã cố gắng bao quát các cách để chuyển hàng trùng lặp thành 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 thoải mái chia sẻ chúng trong phần bình luận.

Đọc Thêm

• Cách Chuyển Vị Trong Excel (5 Cách Dễ Dàng)
• Chuyển Nhiều Hàng Trong Nhóm Thành Cột Trong Excel
• Cách Chuyển Cột Thành Hàng Trong Excel (6 Phương Pháp)
• Chuyển Cột Thành Hàng Trong Excel Sử Dụng Power Query
• Cách Chuyển Cột Thành Hàng Trong Excel Dựa Trên Giá Trị Ô
• VBA Chuyển Nhiều Cột Thành Hàng Trong Excel (2 Phương Pháp)
• Cách Chuyển Cột Thành Danh Sách Phân Cách Bằng Dấu Phẩy Với Dấu Ngoặc Đơn
• Cách Chuyển Vị Trong Excel VBA (3 Phương Pháp)