Một cách mạnh mẽ để hoán đổi hàng và cột trong Google Sheets là sử dụng hàm TRANSPOSE. Tuy nhiên, không chỉ có một lựa chọn duy nhất để làm điều này, bạn còn có thể thực hiện việc tương tự bằng cách sử dụng:
- Lệnh Transpose: Tương tự như hàm TRANSPOSE, nhưng cung cấp khả năng kiểm soát được cải thiện qua các tùy chọn sao chép-dán.
- Các hàm TOCOL và TOROW: Chuyển đổi các hàng hoặc cột cụ thể thành các cột hoặc hàng mới.
Mỗi phương pháp này có nhược điểm và ưu điểm riêng, phù hợp cho các tình huống khác nhau.
Trong bài viết này, chúng ta sẽ tập trung vào hàm TRANSPOSE để khám phá sự mạnh mẽ của nó, nhưng cũng sẽ tóm tắt một số phương pháp khác để có nhận thức toàn diện hơn.
Hàm TRANSPOSE: Cú pháp, Đối số và Các trường hợp sử dụng chính
Cú pháp:
TRANSPOSE(mảng_hoặc_dãy)
Đối số:
mảng_hoặc_dãy: Mảng hoặc dãy các ô chứa dữ liệu bạn muốn hoán đổi. Đây có thể là một ô duy nhất, một dãy ô hoặc thậm chí là một phạm vi đã đặt tên.
Khi bạn thay đổi hướng dữ liệu từ hàng sang cột hoặc ngược lại, các thay đổi hình ảnh sau đây xảy ra: Cột thứ n trở thành hàng thứ n, và hàng thứ n trở thành cột thứ n.
Ví dụ, cột thứ ba sẽ biến thành hàng thứ ba và ngược lại. Áp dụng hàm TRANSPOSE hai lần sẽ giữ nguyên hướng ban đầu.
Các trường hợp sử dụng:
- Manipulation dữ liệu: Việc hoán đổi dữ liệu có thể giúp bạn sắp xếp lại dữ liệu để dễ dàng phân tích, chẳng hạn như lọc hàng theo giá trị cột hoặc ngược lại. Nó cho phép bạn làm việc với dữ liệu trên một trục khác.
- Trình bày dữ liệu: Nó cung cấp tính linh hoạt trong cách dữ liệu được trình bày, giúp bạn dễ dàng tạo ra biểu đồ hoặc báo cáo Pivot Table.
- Định dạng tiêu đề Pivot QUERY: Đây là một mẹo nâng cao hơn cho người dùng QUERY. Nếu dữ liệu Pivot trên QUERY của bạn có các ngày ở hàng đầu và bạn muốn định dạng chúng thành tháng, năm-tháng hoặc năm, trong công thức QUERY:
- Di chuyển cột ngày vào mệnh đề GROUP BY thay vì mệnh đề PIVOT.
- Định dạng cột ngày cho hiển thị mong muốn bằng cách sử dụng mệnh đề FORMAT.
- Sau đó, bạn có thể sử dụng hàm TRANSPOSE để hoán đổi dữ liệu nhận được nếu cần.
- Bạn có thể tìm hiểu về cách định dạng các hàng tiêu đề Pivot QUERY tại đây: How to Format Query Pivot Header Row in Google Sheets
Ví dụ về Hàm TRANSPOSE trong Google Sheets và So sánh với Lệnh Transpose
Dữ liệu ví dụ:
Trong bảng dưới đây, dữ liệu ban đầu được sắp xếp theo cột.
Thay đổi hướng dữ liệu:
Để thay đổi hướng từ cột sang hàng, bạn có thể sử dụng hàm TRANSPOSE hoặc lệnh “Transpose” trong Google Sheets.
Bảng đã hoán đổi:
Sử dụng Công thức TRANSPOSE:
Nếu dữ liệu ban đầu nằm trong phạm vi ô A1:G2, bạn có thể sử dụng công thức sau:
=TRANSPOSE(A1:G2)
Sử dụng Lệnh Transpose:
- Sao chép dữ liệu trong phạm vi ô A1:G2.
- Nhấp chuột phải vào bất kỳ ô nào nằm bên ngoài phạm vi A1:G2 để mở menu ngữ cảnh.
- Chọn “Paste Special” > “Transposed”.
Bạn cũng có thể sử dụng phím tắt trong Google Sheets trong trường hợp này.
- Windows: Ctrl + C để sao chép phạm vi được chọn, và Alt + E + S + E để áp dụng lệnh dán được hoán đổi.
- Mac: Command + C để sao chép dữ liệu đã chọn, và Control + Option + E + S + E để áp dụng lệnh dán được hoán đổi.
Lựa chọn giữa Hàm TRANSPOSE và Lệnh Transpose
Trước khi lựa chọn, hãy xem xét các ưu điểm và nhược điểm của mỗi phương pháp:
Hàm TRANSPOSE:
- Cập nhật nguồn: Những thay đổi trong mảng nguồn sẽ được phản ánh trong mảng hoán đổi.
- Định dạng: Hàm này không giữ định dạng từ dữ liệu nguồn.
- Chỉnh sửa: Kết quả không thể chỉnh sửa trực tiếp; công thức sẽ bị hỏng.
Lệnh Transpose:
- Cập nhật nguồn: Các cập nhật trong mảng nguồn sẽ không được phản ánh trong dữ liệu hoán đổi.
- Định dạng ô: Lưu trữ định dạng, bao gồm màu sắc, chữ đậm, nghiêng, v.v.
- Chỉnh sửa: Dữ liệu đã hoán đổi có thể được chỉnh sửa trực tiếp mà không ảnh hưởng đến nguồn.
Lồng Hàm TRANSPOSE
Như đã đề cập trước đây, việc hoán đổi dữ liệu, thực hiện các thao tác và sau đó hoán đổi lại là một thực hành phổ biến trong Google Sheets. Khi làm điều này, điều quan trọng là tránh hoán đổi cột hoặc hàng ban đầu chứa nhãn.
Hãy xem xét trường hợp bạn muốn sắp xếp dữ liệu trong phạm vi A1:G2 theo các phòng ban bằng cách sử dụng hàm SORT. Vì chúng ta chỉ có thể sắp xếp dữ liệu theo cột, không phải theo hàng, và dữ liệu phòng ban nằm ở hàng thứ hai, chúng ta cần hoán đổi phạm vi B1:G2, thực hiện việc sắp xếp và sau đó hoán đổi nó trở lại.
Ngoài ra, bạn cũng có thể sử dụng hàm HSTACK để kết hợp các nhãn trong bảng gốc với bảng mới.
Công thức:
=HSTACK(A1:A2, TRANSPOSE(SORT(TRANSPOSE(B1:G2), 2, 1)))
Giải thích công thức
Công thức Hàm TRANSPOSE lồng nhau trên có thể được chia thành bốn phần: Hoán đổi trong, Sắp xếp, Hoán đổi ngoài và Gắn nối.
Hoán đổi trong (TRANSPOSE(B1:G2))
Hàm TRANSPOSE ở mức sâu nhất hoán đổi hàng và cột của dữ liệu gốc trong phạm vi B1:G2.
Sắp xếp (SORT(…, 2, 1))
Hàm SORT được áp dụng vào dữ liệu đã hoán đổi để sắp xếp theo trường ‘Phòng ban’.
Hoán đổi ngoài (TRANSPOSE(…))
Một hàm TRANSPOSE khác được áp dụng vào dữ liệu đã được sắp xếp. Điều này hoán đổi dữ liệu đã được sắp xếp trở lại với hướng ban đầu.
Gắn nối (HSTACK(A1:A2, …))
Hàm HSTACK gắn nối dữ liệu trong phạm vi ô A1:A2 (nhãn) và dữ liệu đã được hoán đổi và sắp xếp thu được từ các bước trước đó.
TOCOL và TOROW: Các phương pháp thay thế cho việc hoán đổi một hàng hoặc một cột trong Google Sheets
Mặc dù TRANSPOSE là một công cụ linh hoạt để hoán đổi hàng và cột, TOCOL và TOROW cung cấp các phương pháp thay thế khác cho việc xử lý dữ liệu một hàng hoặc một cột duy nhất.
Từ góc nhìn hoán đổi, bạn có thể sử dụng các hàm này với một cột hoặc hàng duy nhất, vì chúng được thiết kế ban đầu để chuyển đổi một mảng hoặc phạm vi ô thành một cột hoặc hàng duy nhất.
Các hàm này có thể bỏ qua các ô trống và lỗi, làm cho chúng lý tưởng để làm sạch dữ liệu trước khi phân tích thêm.
- TOCOL: Sử dụng nó để chuyển đổi dữ liệu một chiều từ hàng sang dạng dưới dạng cột.
- TOROW: Sử dụng nó để chuyển đổi dữ liệu một chiều từ cột sang dưới dạng dưới dạng hàng.
Ví dụ:
Giả sử bạn có dữ liệu trong ô A1:A10 với các ô trống và lỗi. Hãy thử các công thức sau để thay đổi hướng.
=TOROW(A1:A10, 3) tạo ra một hàng mới, bỏ qua các ô trống và lỗi.
=TRANSPOSE(A1:A10) tạo ra một hàng mới với tất cả dữ liệu, bao gồm cả các ô trống và lỗi, điều này có thể không mong muốn.
Một số Mẹo thêm
Hàm TRANSPOSE thường không yêu cầu hỗ trợ ARRAYFORMULA. Tuy nhiên, bạn có thể gặp ví dụ kết hợp chúng, và công thức có thể thất bại nếu bạn loại bỏ hàm ARRAYFORMULA.
Điều này xảy ra khi bạn sử dụng biểu thức trong hàm TRANSPOSE thay vì một phạm vi vật lý. Biểu thức đòi hỏi hỗ trợ ARRAYFORMULA để mở rộng.
Ví dụ, hãy xem xét một tình huống liên quan đến SPLIT. Bạn muốn chia một chuỗi phân tách bằng dấu phẩy và hoán đổi nó.
=TRANSPOSE(SPLIT("Apple, Orange, Mango", ","))
Công thức trên sẽ chia chuỗi văn bản và thay đổi hướng từ một hàng duy nhất thành một cột duy nhất.
Tuy nhiên, điều này có thể để lại một số ký tự khoảng trống với mỗi phần text được chia. Để khắc phục điều này, bạn có thể sử dụng TRIM với sự kết hợp trên.
=ARRAYFORMULA(TRANSPOSE(TRIM(SPLIT("Apple, Orange, Mango", ","))))
Trong trường hợp này, bạn cần ARRAYFORMULA vì TRIM yêu cầu nó để trả về nhiều giá trị.
Lỗi thường gặp
Dưới đây là hai lỗi phổ biến nhất mà bạn có thể gặp khi làm việc với hàm TRANSPOSE trong Google Sheets:
- Lỗi “#REF!”
- Nguyên nhân: Lỗi này xảy ra khi tham chiếu đến phạm vi nguồn bị hỏng.
- Giải pháp: Kiểm tra công thức của bạn và đảm bảo phạm vi nguồn vẫn tồn tại ở vị trí mong đợi.
- Lỗi “#VALUE!”
- Nguyên nhân: Lỗi này chỉ ra rằng hàm TRANSPOSE nhận nhiều hơn một phạm vi làm đầu vào. Nó chỉ có thể xử lý một phạm vi duy nhất hoặc một ô duy nhất là dữ liệu nguồn.
- Giải pháp:
- Xem lại công thức của bạn và đảm bảo bạn chỉ tham chiếu một phạm vi duy nhất, không phải là nhiều phạm vi được phân tách bằng dấu phẩy hoặc các toán tử khác.
- Nếu bạn cần kết hợp dữ liệu từ nhiều phạm vi để hoán đổi, hãy xem xét việc sử dụng các hàm lồng nhau hoặc phương pháp khác để tạo ra một phạm vi đầu vào duy nhất.
Hy vọng thông tin trên sẽ giúp bạn giải quyết các lỗi liên quan đến hàm này.
