Google Trang tính chuyển đổi nhiều hàng thành một cột

Bài viết này sẽ trình bày cách chuyển đổi nhiều cột thành một cột trong Excel bằng công thức Excel, mã VBA và công cụ Power Query. Hình ảnh sau đây làm nổi bật mục đích của bài viết này

Mục lục

Tải sách bài tập thực hành

Bấm vào nút sau và tải xuống bảng tính Excel miễn phí để bạn thực hành

Hoán đổi nhiều cột thành một. xlsm

3 phương pháp hiệu quả để chuyển đổi nhiều cột thành một cột trong Excel

Ở đây chúng tôi đang làm việc với một danh sách một số nhóm người. Vì một số lý do, chúng tôi cần chuyển các cột thành một cột theo thứ tự chuyển đổi. Chúng tôi sẽ sử dụng 3 công thức Excel khác nhau, mã VBA và công cụ truy vấn nguồn cho việc này. Vì vậy, không cần thảo luận thêm nữa, hãy đi sâu vào các phương pháp

1. Sử dụng Công thức Excel để Chuyển nhiều Cột thành Một Cột

1. 1 Kết hợp các hàm INDEX, INT, MOD, ROW và COLUMNS

Trong phương pháp đầu tiên của chúng tôi, chúng tôi sẽ sử dụng một công thức được hình thành bằng cách kết hợp các hàm INDEX, INT, MOD, ROW và COLUMNS

bước

❶ Chuyển đến ô F5 và nhập công thức sau

=INDEX($B$5:$D$8,1+INT((ROW(B5)-5)/COLUMNS($B$5:$D$8)),MOD(ROW(B5)-5+COLUMNS($B$5:$D$8),COLUMNS($B$5:$D$8))+1)

Ghi chú

Trong công thức đầu tiên, bạn phải đặt ROW(A1)-1) thay vì ROW(B5)-5) nếu tập dữ liệu của bạn bắt đầu từ ô A1

❷ Nhấn phím ENTER và kéo biểu tượng núm điều khiển điền cho đến khi mục nhập cuối cùng xuất hiện trong danh sách một cột

Ở đây, ảnh chụp màn hình sau đây mô tả ngắn gọn toàn bộ quy trình và cả kết quả. 👇

Tuy nhiên, công thức có vẻ không dễ dàng. Vì vậy, chúng tôi sẽ giải thích nó dưới đây

🔎 Giải thích công thức.

• Hàm INDEX có 3 đối số ở đây. Mảng, số hàng và số cột
• Hàm INDEX đếm số hàng bắt đầu từ hàng đầu tiên trong phạm vi đã cho. Vì vậy, trong mảng đã cho, hàng thứ 5 sẽ được coi là hàng đầu tiên cho hàm INDEX ở đây
• $B$5. $D$8 là mảng tham chiếu ở đây
  đầu ra. {“Damien”,”Jackob”,”Nick”;”Chris”,”Tom”,”Rose”;”Martin”,”Harry”,”Peter”;”James”,”Ron”,”George”}

• 1+INT((ROW(B5)-5)/COLUMNS($B$5. $D$8))
  Phần này đề cập đến đối số số hàng. Như chúng ta có thể thấy từ tập dữ liệu, chúng ta có 4 hàng ở đây.

• CỘT($B$5. $D$8)
  đầu ra. 4
• HÀNG(B5)-5
  đầu ra. 0
• (ROW(B5)-5)/COLUMNS($B$5. $D$8)
  đầu ra. 0
• 1+INT((ROW(B5)-5)/COLUMNS($B$5. $D$8))
  đầu ra. 1
  Khi chúng ta sao chép công thức xuống, phần ROW(B5)-5 đầu ra sẽ trở thành 4 tại ô F9. Vì vậy, (ROW(B5)-5)/COLUMNS($B$5. $D$8), phần này sẽ trả về 4/4 = 1 lần này và 1+1= 2 sẽ được trả về bởi 1+INT((ROW(B5)-5)/COLUMNS($B$5. $D$8)) một phần. Vì vậy, hàm INDEX sẽ tìm kiếm giá trị ở hàng thứ hai trong phạm vi đã cho của nó

• MOD(ROW(B5)-5+COLUMNS($B$5. $D$8),CỘT($B$5). $D$8))+1
  Đây là số cột cho hàm INDEX tại đây. Hàm MOD có hai đối số là số và số chia. HÀNG(B5)-5+CỘT($B$5. $D$8) cung cấp đối số số và COLUMNS($B$5). $D$8) phần cung cấp đối số chia. Hàm MOD trả về số dư sau phép chia.
  đầu ra. {1}

• INDEX({“Damien”,”Jackob”,”Nick”;”Chris”,”Tom”,”Rose”;”Martin”,”Harry”,”Peter”;”James”,”Ron . damien.
  Output: Damien.

1. 2 Sử dụng Công thức với các hàm OFFSET, CEILING, MOD, ROW và COLUMNS

Trong phương pháp thứ hai, chúng ta sẽ sử dụng một công thức khác kết hợp các hàm OFFSET, CEILING, MOD, ROW và COLUMNS

Công thức chung

=OFFSET(Tham chiếu tuyệt đối của ô bắt đầu, CEILING((ROW(Tham chiếu ô bắt đầu)-m)/Số cột,1), MOD(ROW(Tham chiếu ô bắt đầu)+Số cột-n, Số cột)

Ở đâu,

m= Số hàng của ô bắt đầu+1;

m= Số hàng của ô bắt đầu +2;

n= một số tạo nên kết quả của (ROW(Tham chiếu ô bắt đầu. )+Số cột-n) chia hết cho số cột

Bây giờ, thực hiện các bước sau

bước

❶ Sao chép công thức sau và dán vào ô F5

=OFFSET($B$4,CEILING((ROW(B5)-4)/COLUMNS($B$5:$D$8),1),MOD(ROW($B5)+COLUMNS($B$5:$D$8)-2,3))

❷ Nhấn ENTER

❸ Cuối cùng kéo hết biểu tượng Fill Handle

Toàn bộ quy trình được mô tả ngắn gọn trong hình sau. 👇

🔎 Giải thích công thức

• Hàm OFFSET có 3 đối số bắt buộc ở đây. tham chiếu, hàng và cột
• $B$4 là tham chiếu của hàm OFFSET mà từ đó hàm sẽ bắt đầu bù
• TRẦN((ROW(B5)-4)/COLUMNS($B$5. $D$8),1) đề cập đến đối số hàng
  đầu ra. {1}

• MOD(ROW($B5)+CỘT($B$5). $D$8)-2,3) đề cập đến đối số cột
  đầu ra. {0}

• OFFSET($B$4,{1},{0})
  đầu ra. {“Damien”}

1. 3 Kết hợp các chức năng OFFSET, ROUNDUP, MOD và ROWS

Đây là phương pháp cuối cùng trong bài viết này sử dụng công thức. Chúng tôi sẽ kết hợp các hàm OFFSET, ROUNDUP, MOD và ROWS trong công thức này

Công thức chung

=OFFSET(Tham chiếu tuyệt đối. của ô nằm ngay phía trên ô bắt đầu, ROUNDUP((ROWS($X. X))/Số cột, 0), MOD(ROWS($X. X)+n, Số cột)

Ở đâu,

X= Hàng bắt đầu của tập dữ liệu (không phải tiêu đề)

n= một số tạo ra kết quả của  ROWS($X. X)+n chia hết cho số cột trong tập dữ liệu của bạn

bước

❶ Nhập công thức bên dưới vào ô F5 và nhấn ENTER

=OFFSET($B$4,ROUNDUP(ROWS($5:5)/3,0),MOD(ROWS($5:5)-1,3))

❷ Kéo hết biểu tượng Fill Handle

Cuối cùng, bạn sẽ thấy kết quả

Đọc thêm. Excel VBA. Chuyển nhiều hàng trong nhóm thành cột

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

• Cách chuyển hàng thành cột dựa trên tiêu chí trong Excel
• Cách chuyển hàng thành cột bằng Excel VBA (4 ví dụ lý tưởng)
• Cách chuyển hàng thành cột trong Excel (5 phương pháp hữu ích)
• Cách chuyển cột thành hàng trong Excel (6 phương pháp)

2. Sử dụng mã VBA Excel để chuyển đổi nhiều cột thành một cột

Trong phương pháp thứ tư, chúng ta sẽ thấy một mã VBA giúp chúng ta chuyển một số cột thành một cột. Hãy áp dụng các bước sau

bước

❶ Nhấn Alt+F11

❷ Chuyển đến tab Chèn và chọn Mô-đun để mở một mô-đun VBA mới

❸ Sao chép mã VBA sau và dán vào mô-đun đã mở

Option Explicit
Sub ConvertMultColumnsintoOne()
Dim Xrng1 As Range, Xrng2 As Range, Xrng As Range
Dim index_row As Integer
Dim xTitleId As String
xTitleId = "Convert Multiple Columns to One"
Set Xrng1 = Application.Selection
Set Xrng1 = Application.InputBox("Source Columns:", xTitleId, Xrng1.Address, Type:=8)
Set Xrng2 = Application.InputBox("Transpose to (one column):", xTitleId, Type:=8)
index_row = 0
Application.ScreenUpdating = False
For Each Xrng In Xrng1.Rows
    Xrng.Copy
    Xrng2.Offset(index_row, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    index_row = index_row + Xrng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

❹ Nhấn phím F5 để chạy mã

❺ Chọn Cột nguồn, i. e. , B5. D8

❻ Nhấn OK

❼ Bây giờ hãy chọn ô, tôi. e. , F5, nơi bạn muốn đặt dữ liệu đã chuyển đổi

❽ Nhấn OK

Cuối cùng, bạn sẽ nhận được kết quả như thế này. 👇

Đọc thêm. VBA để chuyển nhiều cột thành hàng trong Excel (2 phương pháp)

3. Sử dụng Công cụ Power Query

Trong phần này của bài viết, chúng tôi sẽ làm việc trên một bộ dữ liệu khác với bộ dữ liệu chúng tôi đã sử dụng cho đến nay. Ở đây chúng tôi có tên sản phẩm, kích thước và số lượng trong các cột khác nhau. Những gì chúng ta cần làm là thu thập dữ liệu về sản phẩm, kích thước và số lượng trong các cột đơn lẻ. Để làm điều này, chúng tôi sẽ sử dụng công cụ Power Query

❶ Trước hết, chọn toàn bộ tập dữ liệu hoặc bất kỳ ô nào trong tập dữ liệu của bạn

❷ Chuyển đến tab Dữ liệu và nhấn nút Từ Bảng/Phạm vi từ nhóm Nhận & Chuyển đổi Dữ liệu

Cửa sổ Tạo bảng sẽ xuất hiện

❸ Đánh dấu hộp kiểm Bảng của tôi có tiêu đề và nhấn OK

Cửa sổ Power Query Editor sẽ xuất hiện

❹ Bây giờ, hãy chuyển đến tab Chuyển đổi và chọn cột đầu tiên của dữ liệu của chúng tôi

❺ Nhấp vào menu thả xuống Bỏ xoay cột từ nhóm Cột bất kỳ

❻ Chọn tùy chọn Unpivot Other Columns từ danh sách

Bạn nhận được điều này. 👇

❼ Bây giờ, hãy chuyển đến tab File và nhấn nút Close and Load To…

Cửa sổ sau sẽ xuất hiện

❽ Chọn vị trí phù hợp của bạn, bảng tính hiện có hoặc mới

❾ Cuối cùng, nhấn OK

Đây là kết quả của bạn. 👇

Vì tôi đã chọn tùy chọn Trang tính mới, kết quả của tôi được đặt trên một trang tính mới

Đọc thêm. Truy vấn nguồn Excel. Chuyển hàng thành cột (Hướng dẫn từng bước)

Những điều cần ghi nhớ

• Bạn phải sửa đổi các công thức theo kích thước tập dữ liệu của mình
• Bắt #REF. Lỗi hoặc bắt đầu bằng 0 có nghĩa là bạn đã cộng tất cả các mục vào một cột rồi

Phần kết luận

Để kết thúc, chúng ta đã thảo luận về 5 phương pháp hiệu quả để chuyển nhiều cột thành một cột trong Excel với hình minh họa thích hợp. Nếu bạn có bất kỳ sự nhầm lẫn hoặc thắc mắc nào, vui lòng hỏi chúng tôi trong hộp bình luận. Truy cập trang web ExcelDemy của chúng tôi để đọc thêm các bài viết liên quan đến Excel