Thông thường, hàm TRANSPOSE thường dùng để chuyển hàng thành cột. Tuy nhiên, không trả về kết quả dựa trên tiêu chí nhất định như giá trị duy nhất. Hướng dẫn này chỉ cách chuyển hàng thành cột dựa trên tiêu chí trong Excel.
2 cách dễ dàng chuyển hàng thành cột dựa trên tiêu chí trong Excel
Bao gồm tập dữ liệu một số sản phẩm và số lượng như hình dưới. Các hàng sẽ được chuyển thành cột. Sẽ chuyển hàng thành cột dựa trên tiêu chí của giá trị duy nhất vì có mục nhập trùng lặp ở ô riêng biệt. Trước tiên, dùng các hàm INDEX, MATCH, COUNTIF, IF và IFERROR tạo công thức. Cũng thực thi mã VBA để làm điều tương tự.
1. Áp dụng công thức với hàm INDEX, MATCH, COUNTIF để chuyển hàng thành cột dựa trên tiêu chí
Đầu tiên, áp dụng công thức các hàm INDEX, MATCH, COUNTIF, IF và IFERROR với mảng.
Bước 1: Chèn hàm INDEX, MATCH, COUNTIF
- Ở ô E5, nhập công thức sau để lấy sản phẩm duy nhất.
=INDEX($B$5:$B$12, MATCH(0, COUNTIF($E$4:$E4, $B$5:$B$12), 0))
Bước 2: Áp dụng mảng (Array)
- Để áp dụng công thức với mảng, nhấn Ctrl + Shift + Enter.
- Nhận được kết quả duy nhất đầu tiên.
Bước 3: Tự động điền ô (Auto-Fill)
- Để lấy tất cả giá trị duy nhất, dùng công cụ AutoFill Handle kéo xuống cột.
Bước 4: Nhập hàm IFERROR
- Để chuyển giá trị hàng số lượng thành cột, nhập công thức sau.
=IFERROR(INDEX($C$5:$C$12, MATCH(0, COUNTIF($E5:E5, $C$5:$C$12) + IF($B$5:$B$12<>$E5,1,0),0)),0)
Bước 5: Áp dụng mảng
- Để chèn mảng, nhấn Ctrl + Shift + Enter.
- Kết quả: ô F5 hiển thị giá trị chuyển đổi đầu tiên như hình dưới.
- Kéo xuống với AutoFill Handle Tool để tự động điền cột.
- Cuối cùng, tự động điền hàng với AutoFill Handle Tool.
- Do đó, tất cả hàng chuyển đổi thành cột như hình dưới.
Xem thêm: Chuyển nhiều hàng theo nhóm thành cột trong Excel
Đọc tương tự
- Cách chuyển hàng trùng lặp thành cột trong Excel (4 cách)
- Excel VBA: Chuyển nhiều hàng theo 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)
- Cách đảo ngược chuyển vị trong Excel (3 phương pháp đơn giản)
2. Chạy mã VBA chuyển hàng thành cột dựa trên tiêu chí trong Excel
Bước 1: Tạo Module
- Bấm Alt + F11 để bắt đầu VBA Macro.
- Bấm Chèn (Insert).
- Chọn tùy chọn Module để tạo Module.
Bước 2: Nhập mã VBA
- Dán mã VBA sau:
Sub TransposeRows() 'Define Variables Dim RowsNumber As Long Dim p As Long Dim xColCr As String Dim xColumn As New Collection Dim InputRng As Range Dim OutputRng As Range Dim RngText As String Dim CountRow As Long Dim xRowRg As Range On Error Resume Next 'Set values and Input Box fot Input Range RngText = ActiveWindow.RangeSelection.Address Set InputRng = Application.InputBox("Select Your Input Range for 2 columns:", "ExcelDemy", RngText, , , , , 8) Set InputRng = Application.Intersect(InputRng, InputRng.Worksheet.UsedRange) 'Apply Condition to count only 2 columns and to show msg box for alerting to select only two columns If InputRng Is Nothing Then Exit Sub If (InputRng.Columns.Count <> 2) Or _ (InputRng.Areas.Count > 1) Then MsgBox "The specified range is only area for 2 columns ", , "ExcelDemy" Exit Sub End If 'Set values and Input Box fot Input Range Set OutputRng = Application.InputBox("Select Your Output Range (one cell):", "ExcelDemy", RngText, , , , , 8) If OutputRng Is Nothing Then Exit Sub Set OutputRng = OutputRng.Range(1) 'Count Rows RowsNumber = InputRng.Rows.Count 'Apply For loop For p = 2 To RowsNumber xColumn.Add InputRng.Cells(p, 1).Value, InputRng.Cells(p, 1).Value Next Application.ScreenUpdating = False For p = 1 To xColumn.Count xColCr = xColumn.Item(p) OutputRng.Offset(p, 0) = xColCr InputRng.AutoFilter Field:=1, Criteria1:=xColCr Set xRowRg = InputRng.Range("B2:B" & RowsNumber).SpecialCells(xlCellTypeVisible) If xRowRg.Count > CountRow Then CountRow = xRowRg.Count 'Copy and paste with transpose InputRng.Range("B2:B" & RowsNumber).SpecialCells(xlCellTypeVisible).Copy OutputRng.Offset(p, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Application.CutCopyMode = False Next OutputRng = InputRng.Cells(1, 1) OutputRng.Offset(0, 1).Resize(1, CountRow) = InputRng.Cells(1, 2) InputRng.Rows(1).Copy OutputRng.Resize(1, CountRow + 1).PasteSpecial Paste:=xlPasteFormats InputRng.AutoFilter Application.ScreenUpdating = True End Sub
Bước 3: Chạy chương trình
- Trước hết, lưu (save) và nhấn F5 chạy chương trình.
- Chọn tập dữ liệu có tiêu đề.
- Bấm OK.
- Chọn một ô để lấy kết quả đầu ra (output).
- Bấm OK.
- Kết quả: hàng được chuyển thành cột như hình dưới.
Xem thêm: Cách chuyển hàng thành cột dùng Excel VBA (4 ví dụ lý tưởng)
Kết luận
Hy vọng bài viết đã hướng dẫn cách chuyển hàng thành cột dựa trên tiêu chí trong Excel. Hãy học và áp dụng các thủ tục này cho tập dữ liệu của bạn. Xem Workbook thực hành và kiểm tra kỹ năng. Chúng tôi luôn động lực tạo thêm hướng dẫn nhờ sự hỗ trợ quý báu của bạn.
Nếu có thắc mắc – Hãy hỏi chúng tôi. Cũng đừng ngần ngại để lại bình luận ở phần dưới.
Chúng tôi, đội ngũ Exceldemy, luôn sẵn sàng phản hồi thắc mắc của bạn.
Hãy ở lại cùng chúng tôi & tiếp tục học tập.
Bài viết liên quan
- Chuyển cột thành hàng trong Excel 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 có dấu nháy đơn
- Phím tắt Dán chuyển vị trong Excel: 4 cách dễ dàng sử dụng
- Công thức chuyển vị Excel không thay đổi tham chiếu (4 cách dễ dàng)
- Cách chuyển vị trong Excel VBA (3 phương pháp)