Hướng dẫn excel row to column formula - công thức từ hàng sang cột trong excel

Nếu bạn có một bảng tính với dữ liệu trong các cột mà bạn cần xoay để sắp xếp lại nó thành hàng, hãy sử dụng tính năng chuyển vị. Với nó, & nbsp; bạn có thể nhanh chóng chuyển dữ liệu từ các cột sang hàng hoặc ngược lại.Transpose feature. With it, you can quickly switch data from columns to rows, or vice versa.

Ví dụ: nếu dữ liệu của bạn trông như thế này, với các vùng bán hàng trong các tiêu đề cột và các quý dọc theo bên trái:

Tính năng chuyển vị sẽ sắp xếp lại bảng sao cho các phần được hiển thị trong các tiêu đề cột và các vùng bán hàng có thể được nhìn thấy ở bên trái, như thế này:

Lưu ý: & NBSP; Nếu dữ liệu của bạn nằm trong bảng Excel, tính năng chuyển tiếp sẽ có sẵn. Bạn có thể chuyển đổi bảng thành một phạm vi trước hoặc bạn có thể sử dụng chức năng chuyển vị để xoay các hàng và cột.If your data is in an Excel table, the Transpose feature won’t be available. You can convert the table to a range first, or you can use the TRANSPOSE function to rotate the rows and columns.

Đây là cách làm điều đó:

1. Chọn phạm vi dữ liệu bạn muốn sắp xếp lại, bao gồm bất kỳ nhãn hàng hoặc cột nào và nhấn Ctrl+C.

   Lưu ý: & nbsp; đảm bảo rằng bạn sao chép dữ liệu để thực hiện việc này, vì sử dụng lệnh cắt hoặc ctrl+x won công việc.Ensure that you copy the data to do this, since using the Cut command or Ctrl+X won’t work.

2. Chọn một vị trí mới trong bảng tính nơi bạn muốn dán bảng chuyển vị, đảm bảo rằng có nhiều chỗ để dán dữ liệu của bạn. Bảng mới mà bạn dán ở đó sẽ hoàn toàn ghi đè lên bất kỳ dữ liệu / định dạng nào mà đã có ở đó.

   Nhấp chuột phải vào ô trên cùng bên trái của nơi bạn muốn dán bảng chuyển tiếp, sau đó chọn chuyển vị.Transpose

   .

3. Sau khi xoay dữ liệu thành công, bạn có thể xóa bảng gốc và dữ liệu trong bảng mới sẽ vẫn còn nguyên.

Bạn có thể dán dữ liệu dưới dạng dữ liệu được chuyển trong sổ làm việc của bạn. Chuyển đổi định hướng lại nội dung của các ô được sao chép khi dán. Dữ liệu tính bằng các hàng được dán vào các cột và ngược lại.

Đây là cách bạn có thể chuyển đổi nội dung tế bào:

1. Sao chép phạm vi ô.

2. Chọn các ô trống nơi bạn muốn dán dữ liệu được chuyển.

3. Trên tab Trang chủ, nhấp vào biểu tượng Dán và chọn Transpose.Home tab, click the Paste icon, and select Paste Transpose.

Hướng dẫn chỉ ra cách chuyển đổi hàng sang cột trong Excel bằng tính năng đặc biệt dán, chức năng chuyển vị, mã VBA và một công cụ đặc biệt.

Chuyển dữ liệu trong Excel là nhiệm vụ quen thuộc với nhiều người dùng. Rất thường xuyên, bạn xây dựng một bảng phức tạp chỉ để nhận ra rằng nó có ý nghĩa hoàn hảo để xoay nó để phân tích hoặc trình bày dữ liệu tốt hơn trong biểu đồ.

Trong bài viết này, bạn sẽ tìm thấy một số cách để chuyển đổi hàng sang các cột (hoặc cột thành hàng), cho dù bạn gọi nó là điều tương tự :) Những giải pháp này hoạt động trong tất cả các phiên bản của Excel 2010 đến Excel 365, bao gồm nhiều kịch bản có thể, có thể, và giải thích hầu hết những sai lầm điển hình.

Chuyển đổi hàng sang các cột trong Excel bằng cách sử dụng Paste Special

Giả sử bạn có bộ dữ liệu tương tự như những gì bạn thấy ở phần trên của đồ họa dưới đây. Tên quốc gia được tổ chức trong các cột, nhưng danh sách các quốc gia quá dài, vì vậy chúng tôi sẽ thay đổi các cột thành các hàng cho bảng để phù hợp với màn hình:

Để chuyển hàng sang các cột, thực hiện các bước sau:

1. Chọn dữ liệu gốc. Để nhanh chóng chọn toàn bộ bảng, tức là tất cả các ô có dữ liệu trong bảng tính, nhấn Ctrl + home và sau đó Ctrl + Shift + End.Ctrl + Home and then Ctrl + Shift + End.
2. Sao chép các ô đã chọn bằng cách nhấp chuột phải vào lựa chọn và chọn sao chép từ menu ngữ cảnh hoặc bằng cách nhấn Ctrl + C.Copy from the context menu or by pressing Ctrl + C.
3. Chọn ô đầu tiên của phạm vi đích.

   Hãy chắc chắn chọn một ô nằm ngoài phạm vi chứa dữ liệu gốc của bạn, để các khu vực sao chép và khu vực dán không trùng nhau. Ví dụ: nếu bạn hiện có 4 cột và 10 hàng, bảng được chuyển đổi sẽ có 10 cột và 4 hàng.

4. Nhấp chuột phải vào ô đích và chọn Dán đặc biệt từ menu ngữ cảnh, sau đó chọn Chuyển đổi.Paste Special from the context menu, then select Transpose.

Để biết thêm thông tin, xin vui lòng xem cách sử dụng dán đặc biệt trong Excel.

Ghi chú. Nếu dữ liệu nguồn của bạn chứa các công thức, hãy chắc chắn sử dụng đúng các tài liệu tham khảo tương đối và tuyệt đối tùy thuộc vào việc chúng nên được điều chỉnh hay vẫn bị khóa cho một số ô nhất định. If your source data contain formulas, be sure to properly use relative and absolute references depending on whether they should be adjusted or remained locked to certain cells.

Như, bạn vừa thấy, tính năng đặc biệt dán cho phép bạn thực hiện các phép biến đổi hàng đến cột (hoặc cột sang hàng) theo nghĩa đen trong vài giây. Phương pháp này cũng sao chép định dạng dữ liệu gốc của bạn, điều này bổ sung thêm một đối số có lợi cho nó.

Tuy nhiên, phương pháp này có hai nhược điểm ngăn không cho nó được gọi là một giải pháp hoàn hảo để chuyển dữ liệu trong Excel:two drawbacks that prevent it from being called a perfect solution for transposing data in Excel:

• Nó không phù hợp để xoay các bảng Excel đầy đủ chức năng. Nếu bạn sao chép toàn bộ bảng và sau đó mở hộp thoại đặc biệt dán, bạn sẽ tìm thấy tùy chọn Chuyển đổi bị vô hiệu hóa. Trong trường hợp này, bạn cần sao chép bảng mà không có tiêu đề cột hoặc chuyển đổi nó thành một phạm vi trước.
• Paste Special> Chuyển vị không liên kết bảng mới với dữ liệu gốc, vì vậy nó chỉ phù hợp chỉ để chuyển đổi một lần. Bất cứ khi nào thay đổi dữ liệu nguồn, bạn cần lặp lại quy trình và xoay bảng một lần nữa. Không ai muốn lãng phí thời gian của họ để chuyển đổi cùng một hàng và cột nhiều lần, phải không?

Cách chuyển đổi bảng và liên kết nó với dữ liệu gốc

Hãy xem cách bạn có thể chuyển các hàng sang các cột bằng kỹ thuật đặc biệt dán quen thuộc, nhưng kết nối bảng kết quả với bộ dữ liệu gốc. Điều tốt nhất về phương pháp này là bất cứ khi nào bạn thay đổi dữ liệu trong bảng nguồn, bảng lật sẽ phản ánh các thay đổi và cập nhật cho phù hợp.

1. Sao chép các hàng bạn muốn chuyển đổi thành các cột (hoặc các cột sẽ được thay đổi thành hàng).
2. Chọn một ô trống trong cùng hoặc một bảng tính khác.
3. Mở hộp thoại đặc biệt dán, như được giải thích trong ví dụ trước và nhấp vào liên kết Dán ở góc dưới bên trái: Bạn sẽ có kết quả tương tự như thế này:Paste Link in the lower left-hand corner:
   
   
   You will have a result similar to this:
   
4. Chọn bảng mới và mở hộp thoại Tìm và thay thế của Excel (hoặc nhấn Ctrl + H để đến tab Thay thế ngay lập tức).Ctrl + H to get to the Replace tab straight away).
5. Thay thế tất cả "=" ký tự bằng "xxx" hoặc bất kỳ (các) ký tự nào khác không tồn tại ở bất cứ đâu trong dữ liệu thực của bạn. Điều này sẽ biến bảng của bạn thành một thứ gì đó hơi đáng sợ, như bạn thấy trong ảnh chụp màn hình bên dưới, nhưng đừng hoảng sợ, chỉ thêm 2 bước và bạn sẽ đạt được kết quả mong muốn.
   
   
   This will turn your table into something a bit scary, as you see in the screenshot below, but don't panic, just 2 more steps, and you'll achieve the desired result.
   
6. Sao chép bảng với các giá trị "xxx", sau đó sử dụng Paste Special> Chuyển đổi thành các cột lật sang các hàng
7. Cuối cùng, mở hộp thoại Tìm và thay thế thêm một lần nữa để đảo ngược sự thay đổi, tức là thay thế tất cả "xxx" bằng "=" để khôi phục các liên kết đến các ô ban đầu.
   

Đây là một chút dài nhưng giải pháp thanh lịch, phải không? Hạn chế duy nhất của phương pháp này là định dạng ban đầu bị mất trong quá trình và bạn sẽ cần khôi phục nó theo cách thủ công (tôi sẽ chỉ cho bạn một cách nhanh chóng để thực hiện điều này hơn nữa trong hướng dẫn này).

Cách chuyển đổi trong Excel bằng cách sử dụng các công thức

Một cách nhanh hơn để chuyển đổi các cột động thành các hàng trong Excel là sử dụng công thức chuyển vị hoặc chỉ mục/địa chỉ. Giống như ví dụ trước, các công thức này cũng giữ các kết nối với dữ liệu gốc nhưng hoạt động hơi khác một chút.

Thay đổi hàng thành các cột trong Excel bằng hàm chuyển đổi

Như tên của nó cho thấy, chức năng chuyển đổi được thiết kế đặc biệt để truyền dữ liệu trong Excel:

=TRANSPOSE(array)

Trong ví dụ này, chúng tôi sẽ chuyển đổi một bảng khác liệt kê các tiểu bang của Hoa Kỳ theo dân số:

1. Đếm số lượng hàng và cột trong bảng ban đầu của bạn và chọn cùng số lượng ô trống, nhưng theo hướng khác.

   Ví dụ, bảng mẫu của chúng tôi có 7 cột và 6 hàng, bao gồm các tiêu đề. Vì hàm chuyển vị sẽ thay đổi các cột thành hàng, chúng tôi chọn một phạm vi 6 cột và 7 hàng.

2. Với các ô trống được chọn, hãy nhập công thức này:

   =TRANSPOSE(A1:G6)
   

3. Vì công thức của chúng tôi cần được áp dụng cho nhiều ô, hãy nhấn Ctrl + Shift + Enter để biến nó thành một công thức mảng.Ctrl + Shift + Enter to make it an array formula.

Voilà, các cột được chuyển đổi thành các hàng, chính xác như chúng tôi muốn:

Ưu điểm của chức năng chuyển vị:

Lợi ích chính của việc sử dụng hàm chuyển đổi là bảng xoay được giữ lại kết nối với bảng nguồn và bất cứ khi nào bạn thay đổi dữ liệu nguồn, bảng chuyển đổi sẽ thay đổi theo đó.

Điểm yếu của chức năng chuyển vị:

• Định dạng bảng ban đầu không được lưu trong bảng được chuyển đổi, như bạn thấy trong ảnh chụp màn hình ở trên.
• Nếu có bất kỳ ô trống nào trong bảng ban đầu, các ô được chuyển sẽ chứa 0 thay thế. Để khắc phục điều này, hãy sử dụng chuyển đổi kết hợp với hàm IF như được giải thích trong ví dụ này: Cách chuyển vị không có số không.
• Bạn không thể chỉnh sửa bất kỳ ô nào trong bảng xoay vì nó phụ thuộc rất nhiều vào dữ liệu nguồn. Nếu bạn cố gắng thay đổi một số giá trị ô, bạn sẽ kết thúc với lỗi "Bạn không thể thay đổi phần của mảng".

Gói lên, bất cứ điều gì tốt và dễ sử dụng chức năng chuyển vị là, nó chắc chắn thiếu sự linh hoạt và do đó có thể không phải là cách tốt nhất để đi trong nhiều tình huống.

Để biết thêm thông tin, vui lòng xem chức năng chuyển đổi Excel với các ví dụ.

Chuyển đổi hàng sang cột với các chức năng gián tiếp và địa chỉ

Trong ví dụ này, sẽ sử dụng kết hợp hai chức năng, điều này hơi khó khăn. Vì vậy, hãy xoay một bảng nhỏ hơn để có thể tập trung tốt hơn vào công thức.

Giả sử, bạn có dữ liệu trong 4 cột (A - D) và 5 hàng (1 - 5):

Để có các cột được chuyển đổi thành hàng, hãy làm như sau:

1. Nhập công thức bên dưới trong ô bên trái nhất của phạm vi đích, nói A7 và nhấn phím Enter:Enter key:

   =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

2. Sao chép công thức phải và xuống dưới nhiều hàng và cột khi cần bằng cách kéo chữ thập màu đen nhỏ ở góc dưới bên phải của các ô đã chọn:
   

Đó là nó! Trong bảng mới được tạo của bạn, tất cả các cột được chuyển sang các hàng.

Nếu dữ liệu của bạn bắt đầu trong một số hàng khác ngoài 1 và cột khác, bạn sẽ phải sử dụng một công thức phức tạp hơn một chút:

=INDIRECT(ADDRESS(COLUMN(A1) - COLUMN($A$1) + ROW($A$1), ROW(A1) - ROW($A$1) + COLUMN($A$1)))

Trong đó A1 là ô trên cùng bên trái của bảng nguồn của bạn. Ngoài ra, xin vui lòng để tâm đến việc sử dụng các tài liệu tham khảo tế bào tuyệt đối và tương đối.

Tuy nhiên, các tế bào được chuyển đổi trông rất đơn giản và buồn tẻ, so với dữ liệu gốc:

Nhưng đừng thất vọng, vấn đề này có thể dễ dàng khắc phục. Để khôi phục định dạng ban đầu, đây là những gì bạn làm:

• Sao chép bảng gốc.
• Chọn bảng kết quả.
• Nhấp chuột phải vào bảng kết quả và chọn Tùy chọn Dán> Định dạng.Paste Options > Formatting.

Ưu điểm: Công thức này cung cấp một cách linh hoạt hơn để chuyển các hàng sang các cột trong Excel. Nó cho phép thực hiện bất kỳ thay đổi nào trong bảng chuyển đổi vì bạn sử dụng một công thức thông thường, không phải là một công thức mảng.: This formula provides a more flexible way to turn rows to columns in Excel. It allows making any changes in the transposed table because you use a regular formula, not an array formula.

Thiếu sót: Tôi chỉ có thể thấy một - định dạng của dữ liệu thứ tự bị mất. Mặc dù, bạn có thể nhanh chóng khôi phục nó, như được hiển thị ở trên.: I can see only one - the formatting of the ordinal data is lost. Though, you can quickly restore it, as shown above.

Cách thức hoạt động của công thức này

Bây giờ bạn đã biết cách sử dụng tổ hợp gián tiếp / địa chỉ, bạn có thể muốn có được cái nhìn sâu sắc về những gì công thức đang thực sự đang làm.

Như tên của nó cho thấy, hàm gián tiếp, được sử dụng để gián tiếp tham chiếu một ô. Nhưng sức mạnh thực sự của gián tiếp là nó có thể biến bất kỳ chuỗi nào thành một tham chiếu, bao gồm một chuỗi mà bạn xây dựng bằng cách sử dụng các chức năng khác và các giá trị của các ô khác. Và đây chính xác là những gì chúng ta sẽ làm. Nếu bạn đang theo dõi điều này, bạn sẽ hiểu tất cả phần còn lại một cách dễ dàng :)

Như bạn nhớ, chúng tôi đã sử dụng thêm 3 chức năng trong công thức - địa chỉ, cột và hàng.

Hàm địa chỉ có được địa chỉ ô theo các số và số cột bạn chỉ định, tương ứng. Vui lòng nhớ đơn hàng: Cột thứ nhất, thứ hai -.

Trong công thức của chúng tôi, chúng tôi cung cấp các tọa độ theo thứ tự ngược lại, và đây là những gì thực sự thực hiện thủ thuật! Nói cách khác, phần này của địa chỉ công thức (cột (a1), hàng (a1)) hoán đổi hàng vào các cột, tức là lấy số cột và thay đổi nó thành số hàng, sau đó lấy số hàng và chuyển nó thành một cột con số.

Cuối cùng, hàm gián tiếp xuất ra dữ liệu quay. Không có gì khủng khiếp ở tất cả, phải không?

Chuyển dữ liệu trong Excel bằng Macro VBA

Để tự động hóa việc chuyển đổi các hàng sang các cột trong Excel, bạn có thể sử dụng macro sau:

Sub TransposeColumnsRows () Dim Sourcerange dưới dạng Phạm vi Dim Destrange As Phạm vi Setcerange = application.InputBox (nhắc: = "Vui lòng chọn phạm vi để chuyển vị", tiêu đề: " .InputBox (Prompt: = "Chọn ô trên bên trái của phạm vi đích", Tiêu đề: = "Hàng chuyển đổi sang cột", loại: = 8) xlnone, skipblanks: = false, chuyển đổi: = application true TransposeColumnsRows() Dim SourceRange As Range Dim DestRange As Range Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8) Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8) SourceRange.Copy DestRange.Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Application.CutCopyMode = False End Sub

Để thêm một macro vào bảng tính của bạn, vui lòng làm theo các hướng dẫn được mô tả trong cách chèn và chạy mã VBA trong Excel.

Ghi chú. Chuyển đổi với VBA, có giới hạn là 65536 yếu tố. Trong trường hợp mảng của bạn vượt quá giới hạn này, dữ liệu bổ sung sẽ được sử dụng âm thầm. Transposing with VBA, has a limitation of 65536 elements. In case your array exceeds this limit, the extra data will be silently cast away.

Cách sử dụng macro để chuyển đổi hàng sang cột

Với macro được chèn vào sổ làm việc của bạn, hãy thực hiện các bước dưới đây để xoay bảng của bạn:

1. Mở bảng tính đích, nhấn ALT + F8, chọn macro TransposeColumnsRows và nhấp vào Chạy.Alt + F8, select the TransposeColumnsRows macro, and click Run.
   
2. Chọn phạm vi mà bạn muốn chuyển đổi và nhấp vào OK:
   
3. Chọn ô trên bên trái của phạm vi đích và bấm OK:
   

Tận hưởng kết quả :)

Nếu bạn cần thực hiện các phép biến đổi hàng sang cột một cách thường xuyên, bạn thực sự có thể đang tìm kiếm một cách nhanh hơn và đơn giản hơn. May mắn thay, tôi có một cách như vậy trong Excel của mình, và những người dùng khác của bộ cuối cùng của chúng tôi :)

Hãy để tôi chỉ cho bạn cách chuyển hàng sang các cột trong Excel theo nghĩa đen trong một vài cú nhấp chuột:

1. Chọn bất kỳ ô đơn nào trong bảng của bạn, truy cập vào tab ABLEBITS> Nhóm biến đổi và nhấp vào nút Chuyển đổi.Transpose button.
   
2. Các cài đặt mặc định hoạt động tốt trong hầu hết các trường hợp, vì vậy bạn chỉ cần nhấp vào Chuyển đổi mà không thay đổi bất cứ điều gì.Transpose without changing anything.

   Nếu bạn chỉ muốn dán các giá trị hoặc tạo liên kết vào dữ liệu nguồn để buộc bảng xoay tự động cập nhật với mọi thay đổi bạn thực hiện vào bảng gốc, hãy chọn tùy chọn tương ứng.
   

Xong! Bảng được chuyển đổi, định dạng được bảo tồn, không cần thao tác nữa:

Nếu bạn tò mò muốn thử điều này và hơn 70 công cụ chuyên nghiệp khác cho Excel, tôi mời bạn tải xuống phiên bản dùng thử của bộ Ultimate Suite của chúng tôi. Cảm ơn bạn đã đọc và tôi hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Bạn cũng có thể quan tâm

Làm cách nào để chuyển đổi một hàng sang một cột trong Excel?

Làm thế nào để bạn sao chép một công thức từ một hàng sang một cột?

Làm cách nào để chuyển đổi nhiều hàng thành các cột trong Excel?