Chuyển đổi dữ liệu trong Excel từ hàng thành cột và ngược lại

Đối với những ai thường xuyên sử dụng Excel, việc chuyển dữ liệu từ hàng sang cột hoặc ngược lại có thể giúp dữ liệu trở nên dễ đọc và phục vụ tốt hơn cho việc phân tích hay trình bày bằng đồ thị sau khi đã tạo một bảng biểu phức tạp.

Bạn sẽ được Siêu Marketing chỉ dẫn một số phương pháp để đảo hàng và cột (hoặc cột thành hàng) trong Excel qua bài viết này. Những cách thức này áp dụng được cho Excel 2016, 2013, 2010, 2007 và các phiên bản cũ hơn. Chúng bao gồm các trường hợp có thể xảy ra, giải thích các lỗi thông thường và cung cấp ví dụ về cách sử dụng hàm và công thức Transpose của Excel.

Biến hàng thành cột trong Excel thông qua Paste Special (Transpose)

Xét trường hợp bạn đã thiết kế một bảng tính Excel giống như hình dưới đây, với các tên quốc gia được liệt kê theo hàng. Để dễ dàng theo dõi trên màn hình, chúng ta cần chuyển từ hàng này sang cột:

  1. Chọn những hàng hoặc cột mà bạn muốn chuyển. Để chuyển toàn bộ bảng, sử dụng tổ hợp phím Ctrl + Home và Ctrl + Shift + End.
  2. Copy vùng dữ liệu đã chọn bằng cách click chuột phải và chọn Copy, hoặc sử dụng phím tắt Ctrl + C, hoặc click vào nút copytab Home tab > Clipboard group.
  3. Tiếp theo, click vào ô đầu tiên nơi bạn muốn dán dữ liệu đã chuyển đổi.

Chú ý: Quan trọng là bạn phải chọn một ô không thuộc phạm vi chứa dữ liệu gốc, để tránh sự chồng chéo giữa vùng copy và vùng dán. Nếu bạn có dữ liệu gồm 4 cột và 10 hàng, khi chuyển đổi thì bạn sẽ cần một vùng trống với kích thước ít nhất là 10 cột và 4 hàng.

  1. Thực hiện dán dữ liệu copy bằng Paste Special > Transpose. Có một số cách để mở hộp thoại Paste Special của Excel:
  • Trong tab Home tab > Clipboard group, click vào mũi tên nhỏ màu đen dưới nút Paste, và chọn Transpose.
  • Click chuột phải vào ô đích và chọn Paste Special từ menu, sau đó chọn Transpose.
  • Hãy thử phím tắt Ctrl + Alt + V, sau đó chọn Transpose và ấn OK.

Select Transpose in the Paste Special dialog.

Chú ý: Khi chuyển những ô có chứa công thức, bạn cần đảm bảo rằng tất cả các ô đều sử dụng tham chiếu tuyệt đối (dấu $), vì công thức cũng sẽ được chuyển và các tham chiếu ô sẽ tự động được điều chỉnh.

Tính năng Transpose của Excel giúp bạn chuyển đổi nhanh chóng giữa hàng và cột (hoặc ngược lại) chỉ trong vài giây. Cách này rất hiệu quả khi bạn chỉ cần chuyển đổi bảng biểu một lần duy nhất và nó còn sao chép luôn cả định dạng từ dữ liệu gốc của bạn, tăng cường cho việc hiển thị dữ liệu.

Tuy nhiên, có hai nhược điểm khiến cho cách làm này không phải lúc nào cũng là lời giải tối ưu cho việc chuyển dữ liệu trong Excel:

  • Khi sao chép dữ liệu bảng Excel đầy đủ các hàm, bạn không thể dùng tùy chọn transpose như với các bảng dữ liệu đơn giản. Trong trường hợp này, bạn cần phải sao chép bảng mà không có tiêu đề cột, hoặc bạn cần chuyển đổi bảng thành kiểu Range (click chuột phải vào ô bất kỳ trong bảng và chọn  Table
    • Một nhược điểm lớn của phương pháp này là nó chỉ tạo ra bản sao dữ liệu gốc và bạn cần phải thực hiện lại các bước mỗi khi dữ liệu nguồn thay đổi, điều này tốn nhiều thời gian và không hiệu quả.

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

    Hãy khám phá cách để biến đổi dữ liệu từ hàng sang cột trong Excel sử dụng phương pháp kéo và thả mà vẫn giữ liên kết với dữ liệu ban đầu. Lợi ích lớn của phương pháp này là bất kỳ thay đổi nào trong dữ liệu nguồn cũng sẽ được cập nhật tự động trong bảng đã chuyển đổi.

    1. Chép hàng hay cột mà bạn muốn chuyển đổi.
    2. Đi tới một ô trống, trên cùng một trang tính hoặc một trang tính khác.
    3. Truy cập Paste Special qua cửa sổ hộp thoại và chọn Paste Link ở phía dưới cùng bên trái của cửa sổ.

    Mở hộp thoại Paste Special và chọn Paste Link.

    Đây là kết quả nhận được:

    Đường link tới dữ liệu gốc được sao chép tạo thành bảng mới.

    1. Mở hộp thoại Find and Replace của Excel (sử dụng phím tắt Ctrl + H để đi tới tab Replace).
    2. Thực hiện thay thế tất cả dấu “=” bằng “xxx” hoặc một ký tự bất kỳ không xuất hiện trong dữ liệu gốc của bạn.

    Thay tất cả dấu '=' bằng ký tự khác

    Bảng của bạn sau khi thay thế sẽ trở nên không quen thuộc nhưng đừng lo, chỉ cần thực hiện thêm hai bước nữa.

    1. Chép toàn bộ bảng đang có mã “xxx” và sử dụng chức năng Paste Special > Transpose để đổi hàng thành cột hoặc ngược lại tuỳ thuộc vào yêu cầu của bạn.
    2. Mở lại hộp thoại Find and Replace để hoàn tác thay đổi, tức là thay thế lại “xxx” với “=”, nhằm khôi phục liên kết đến dữ liệu ban đầu. Và chúng ta đã hoàn tất!

    Bảng đã được đổi chỗ với tất cả các ô được liên kết lại với dữ liệu gốc.

    Giải pháp này không chỉ nhanh và đơn giản mà còn rất tiện lợi, đúng không? Rắc rối duy nhất là định dạng của bảng gốc không được giữ nguyên và bạn cần phải thiết lập lại nó thủ công (tôi sẽ hướng dẫn bạn cách làm nhanh chóng ngay trong phần này).

    Cách chuyển đổi dữ liệu trong Excel sử dụng công thức

    Nếu bạn muốn thử thách hơn, hãy sử dụng các công thức Excel để biến đổi cột thành hàng hoặc ngược lại trong bảng Excel của bạn.

    Đổi chỗ hàng và cột trong Excel dùng hàm TRANSPOSE

    Hàm TRANSPOSE được tạo ra riêng để chuyển đổi dữ liệu trong Excel. Nếu bạn chưa quen với các công thức Excel hoặc cụ thể là hàm mảng, hãy chắc chắn rằng bạn theo dõi đúng các bước sau đây.

    Hãy nhìn vào ví dụ của việc chuyển đổi bảng dữ liệu dân số các tiểu bang Hoa Kỳ:

    Đầu tiên, hãy…

    Đầu tiên, bạn phải xác định rõ ràng số cột và hàng trong bảng mà bạn muốn chuyển đổi. Ví dụ, bảng mà chúng tôi sẽ sử dụng mẫu có 7 cột và 6 hàng, bao gồm cả hàng tiêu đề. Hãy nhớ kỹ những số lượng này trước khi tiến hành chuyển đổi các hàng.

    1. Kiểm đếm số lượng ô cần chuyển đổi, trong trường hợp này là 7 cột và 6 hàng.
    2. Lựa chọn một khu vực trống. Vì hàm TRANSPOSE sẽ chuyển vị chiều ngang và dọc của dải ô đã chọn, bạn cần chọn một khoảng không gian có số hàng và cột tương ứng với bảng nguồn. Trong ví dụ, ta chọn 6 cột và 7 hàng.
    3. Nhấn F2 để chuyển sang chế độ chỉnh sửa.
    4. Điền vào công thức TRANSPOSE và đưa vào phạm vi ô bạn muốn chuyển đổi trong dấu ngoặc: =TRANSPOSE(mảng). Trong trường hợp này, ta nhập =TRANSPOSE($A$1:$G$6).

    Lưu ý: Bạn cần phải sử dụng tham chiếu absolut khi dùng công thức TRANSPOSE.

    1. Bấm tổ hợp phím Ctrl + Shift + Enter.

    Chú ý: Bạn cần nhấn Ctrl + Shift + Enter do đây là một công thức mảng, bấm Enter sẽ không thực thi.

    Thực hiện xong! Dữ liệu đã được chuyển đổi và giờ đây chúng ta có 7 cột chuyển thành 6 cột, đúng theo như mong đợi:

    Bảng được chuyển đổi và các hàng chuyển thành cột.

    Điểm mạnh của hàm TRANSPOSE:

    Ưu điểm lớn của việc sử dụng công thức TRANSPOSE là bảng đã chuyển sẽ tiếp tục liên kết với bảng gốc và cập nhật theo thay đổi từ dữ liệu ban đầu.

    Nhược điểm của hàm TRANSPOSE:

    • Định dạng của bảng gốc không được giữ lại trong quá trình chuyển đổi, như bạn có thể thấy trong hình chụp màn hình phía trên.
    • Nếu bảng nguồn có các ô trống, các ô được chuyển đến sẽ điền số 0 thay vì để trống.
    • Không thể chỉnh sửa từng ô trong bảng đã chuyển vì công thức TRANSPOSE phụ thuộc nhiều vào dữ liệu nguồn. Nếu cố gắng thay đổi giá trị của một ô, Excel sẽ báo lỗi “You cannot change part of an array” [Bạn không thể chỉnh sửa một phần của mảng].

    Ngoài ra, dù công thức TRANSPOSE hữu ích và đơn giản để sử dụng, nhưng không thực sự linh hoạt và vì thế có thể không phải là lựa chọn tốt nhất trong mọi tình huống.

    Chuyển Hàng thành Cột/Cột thành Hàng bằng Hàm INDIRECT

    Cách thức chuyển đổi này khá tương đồng với phương pháp đã nêu trên. Tuy nhiên, nó có ưu thế là cho phép chỉnh sửa bảng đích và vẫn duy trì liên kết với bảng nguồn.

    Chúng ta sẽ thử nghiệm phương pháp này với một bảng nhỏ để bạn có thể tập trung vào quy trình thay vì mất tập trung vào dữ liệu, vì ta sẽ dùng kết hợp các hàm INDIRECT và ADDRESS có phần phức tạp.

    Giả sử bảng dữ liệu của bạn bao gồm 4 cột (A – D) và 5 hàng (1 – 5):

    Dùng hàm INDIRECT để chuyển hàng thành cột

    1. Điền vào công thức sau vào ô bên trái khu vực đã chọn, ví dụ ô A7 và nhấn phím Enter:

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

    Nếu dữ liệu của bạn không bắt đầu từ hàng 1 hoặc cột A, bạn cần dùng công thức phức tạp hơn:

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

    Ở đây A1 là ô đầu tiên của bảng nguồn. Đồng thời cần chú ý việc sử dụng tham chiếu tuyệt đối và tương đối cho các ô.

    1. Để sao chép công thức vào phạm vi mà bạn mong muốn chứa dữ liệu đã chuyển, hãy thực hiện kéo điểm neo hình chữ thập màu đen nằm ở góc phải dưới cùng của ô hoặc nhiều ô đã chọn như bình thường.

    Copy the formula through the range where you want to put the transposed data.

    À, xong rồi nha! Trong bảng mới của chúng ta, các cột đã biến thành hàng rồi đấy.

    Xin lưu ý, dù đã chuyển đổi nhưng các ô trông khá đơn giản và không mịn màng như dữ liệu gốc.

    The formatting of transposed data is lost.

    Tuy vậy, đừng vội nản lòng, bởi lẽ đây không phải là điều quá khó khăn. Việc khôi phục lại định dạng dữ liệu gốc có thể thực hiện một cách nhanh chóng theo cách sau:

    • Bạn hãy tiến hành sao chép toàn bộ bảng dữ liệu gốc;
    • Tiếp đến, hãy chọn bảng chuyển đổi;
    • Bấm chuột phải vào bảng đã chọn và chọn Formatting trong phần Paste Options.

    Một cách khái quát, công thức INDIRECT có vẻ là phương pháp linh động hơn khi chuyển hàng thành cột trong Excel so với hàm TRANSPOSE.

    Lợi ích: bạn có thể chỉnh sửa bất kỳ ô nào trong bảng chuyển đổi mà vẫn duy trì được sự liên kết với dữ liệu gốc và tự động cập nhật mỗi khi có sự thay đổi trong bảng nguồn.

    Nhược điểm: thực tế mà nói, tôi chỉ thấy một vấn đề là định dạng không được giữ nguyên. Tuy nhiên, nó lại có thể được phục hồi một cách nhanh chóng như ví dụ đã nêu ở trên.

    Bạn giờ có lẽ đã hiểu rõ về hàm INDIRECT, hãy thử tìm hiểu thêm về cách thức hoạt động và lý do sử dụng hàm này kết hợp với hàm ADDRESS.

    Hàm INDIRECT và ADDRESS – cấu trúc và lý thuyết

    Hàm INDIRECT, theo tên gọi của nó, dùng để tham chiếu gián tiếp đến một ô cụ thể. Chẳng hạn, muốn lấy giá trị từ ô B1 vào ô A8, bạn nhập công thức trong A8: =INDIRECT(“B1”).

    The syntax of the INDIRECT function
    Nhưng sự tiện ích của INDIRECT thể hiện khi hàm này có khả năng biến chuỗi bất kỳ thành tham chiếu, kể cả chuỗi được tạo nên từ các hàm khác hoặc từ giá trị các ô khác. Đây chính xác là điều chúng ta sẽ thực hiện tiếp theo. Nếu bạn nắm vững phần này, mọi phần sau sẽ trở nên dễ hiểu hơn nhiều 🙂

    Chúng ta đã biết 3 hàm được sử dụng kèm trong công thức đó là ADDRESS, COLUMN và ROW.

    Hàm ADDRESS giúp xác định địa chỉ của ô dựa trên số dòng và số cột được chỉ ra. Lưu ý rằng thứ tự là dòn

    g trước, cột sau. Ví dụ, ADDRESS(8,1) sẽ trả lại giá trị $A$8.

    Hàm COLUMN cho biết số cột của ô tham chiếu cụ thể. Ví dụ, =COLUMN(A3) sẽ cho kết quả là 1, vì cột A đứng đầu tiên. Hàm ROW hoạt động tương tự nhưng dành cho dòng.

    Cùng nhắc lại công thức chúng ta dùng để chuyển đổi hàng thành cột trong bảng tính Excel: =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

    Bạn cần nhìn nhận đây là một thủ thuật khéo léo. Hãy ghi nhớ rằng ADDRESS đánh giá con số đầu tiên là dòng và second là cột. Nói cách khác, ADDRESS sẽ biến số cột từ COLUMN thành dòng, sau đó biến dòng từ ROW thành cột, như vậy là hoán đổi hàng thành cột.

    Hàm COLUMN và ROW đưa ra số dòng và cột, và ADDRESS chuyển cột thành hàng, hàng thành cột -> INDIRECT lấy ra dữ liệu đã hoán đổi vị trí trong bảng tính Excel của bạn. Dễ hiểu phải không nào?

    Đó là một lượng thông tin không nhỏ đúng không? Tất cả những kiến thức này bạn hoàn toàn có thể học được trong khóa học SM90 – Excel từ cơ bản đến chuyên gia do Siêu Marketing cung cấp. Khóa học này sẽ hỗ trợ bạn xây dựng kiến thức một cách chi tiết và toàn diện. Thêm vào đó, không có hạn chế về thời gian học nên bạn có thể tự do học mọi lúc, tiện lợi khi cần tra cứu. Hiện nay, hệ thống đang triển khai chương trình khuyến mãi hấp dẫn cho những người đăng ký khóa học. Để biết thêm chi tiết, hãy truy cập: sieumarketing.com

    Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop