Hàm Transpose trong Excel

Đôi khi, người dùng Excel có thể có nhiệm vụ chuyển phạm vi dữ liệu có cấu trúc theo chiều ngang thành chiều dọc. Quá trình này được gọi là chuyển vị. Từ này là mới đối với hầu hết mọi người, bởi vì trong công việc máy tính thông thường, bạn không cần phải sử dụng thao tác này. Tuy nhiên, những ai phải làm việc với lượng dữ liệu lớn thì cần phải biết cách thực hiện. Hôm nay chúng ta sẽ nói chi tiết hơn về cách thực hiện nó, với chức năng gì và cũng xem xét chi tiết một số phương pháp khác.

Hàm TRANSPOSE - chuyển đổi phạm vi ô trong Excel

Một trong những phương pháp chuyển vị bảng chức năng và thú vị nhất trong Excel là hàm GIAO THÔNG. Với sự trợ giúp của nó, bạn có thể biến một dải dữ liệu ngang thành một dải dữ liệu dọc hoặc thực hiện thao tác ngược lại. Hãy xem làm thế nào để làm việc với nó.

Cú pháp hàm

Cú pháp cho hàm này cực kỳ đơn giản: TRANSPOSE (mảng). Có nghĩa là, chúng ta chỉ cần sử dụng một đối số, đó là tập dữ liệu cần được chuyển đổi sang dạng xem ngang hoặc dọc, tùy thuộc vào những gì nó ban đầu.

Chuyển đổi phạm vi ô theo chiều dọc (cột)

Giả sử chúng ta có một cột có phạm vi B2: B6. Chúng có thể chứa cả giá trị và công thức tạo sẵn trả về kết quả cho các ô này. Nó không quá quan trọng đối với chúng tôi, chuyển vị có thể thực hiện được trong cả hai trường hợp. Sau khi áp dụng chức năng này, độ dài hàng sẽ giống với độ dài cột phạm vi ban đầu.

Trình tự các bước để sử dụng công thức này như sau:

1. Chọn một dòng. Trong trường hợp của chúng ta, nó có chiều dài là năm ô.
2. Sau đó, di chuyển con trỏ đến thanh công thức và nhập công thức vào đó = VẬN TẢI (B2: B6).
3. Nhấn tổ hợp phím Ctrl + Shift + Enter.

Đương nhiên, trong trường hợp của bạn, bạn cần chỉ định phạm vi tiêu biểu cho bảng của bạn.

Chuyển đổi phạm vi ô ngang (hàng)

Về nguyên tắc, cơ chế hoạt động gần giống như đoạn trước. Giả sử chúng ta có một chuỗi với tọa độ đầu và cuối B10: F10. Nó cũng có thể chứa cả giá trị và công thức trực tiếp. Hãy tạo một cột từ nó, cột này sẽ có cùng kích thước với hàng ban đầu. Chuỗi các hành động như sau:

1. Chọn cột này bằng chuột. Bạn cũng có thể sử dụng các phím Ctrl trên bàn phím và mũi tên xuống, sau khi nhấp vào ô trên cùng của cột này.
2. Sau đó, chúng tôi viết công thức = VẬN CHUYỂN (B10: F10) trong thanh công thức.
3. Chúng tôi viết nó dưới dạng công thức mảng bằng tổ hợp phím Ctrl + Shift + Enter.

Vận chuyển với Dán đặc biệt

Một tùy chọn chuyển vị khả thi khác là sử dụng chức năng Dán Đặc biệt. Đây không còn là một toán tử được sử dụng trong công thức, nhưng nó cũng là một trong những phương pháp phổ biến để chuyển cột thành hàng và ngược lại.

Tùy chọn này nằm trên tab Trang chủ. Để truy cập nó, bạn cần tìm nhóm “Clipboard” và ở đó tìm nút “Paste”. Sau đó, mở menu nằm dưới tùy chọn này và chọn mục “Transpose”. Trước đó, bạn cần chọn phạm vi mà bạn muốn chọn. Kết quả là, chúng ta sẽ nhận được cùng một phạm vi, chỉ có điều ngược lại là được nhân đôi.

3 cách chuyển đổi bảng trong Excel

Nhưng trên thực tế, có nhiều cách hơn để chuyển cột thành hàng và ngược lại. Hãy mô tả 3 phương pháp mà chúng ta có thể chuyển đổi một bảng trong Excel. Chúng tôi đã thảo luận về hai trong số chúng ở trên, nhưng chúng tôi sẽ đưa ra một vài ví dụ khác để bạn hiểu rõ hơn về uXNUMXbuXNUMXbhow để thực hiện quy trình này.

Phương pháp 1: Dán đặc biệt

Phương pháp này là đơn giản nhất. Chỉ cần nhấn một vài nút là người dùng sẽ nhận được một phiên bản chuyển đổi của bảng. Hãy đưa ra một ví dụ nhỏ để rõ ràng hơn. Giả sử chúng ta có một bảng chứa thông tin về số lượng sản phẩm hiện có trong kho, cũng như tổng giá của chúng. Bảng chính nó trông như thế này.

Chúng tôi thấy rằng chúng tôi có một tiêu đề và một cột có số sản phẩm. Trong ví dụ của chúng tôi, tiêu đề chứa thông tin về sản phẩm nào, giá bao nhiêu, còn hàng trong kho và tổng chi phí của tất cả các sản phẩm liên quan đến mặt hàng này có trong kho là bao nhiêu. Chúng tôi lấy chi phí theo công thức trong đó chi phí nhân với số lượng. Để làm cho ví dụ trực quan hơn, hãy đặt tiêu đề màu xanh lá cây.

Nhiệm vụ của chúng ta là đảm bảo rằng thông tin có trong bảng được đặt theo chiều ngang. Đó là, để các cột trở thành hàng. Trình tự các hành động trong trường hợp của chúng tôi sẽ như sau:

1. Chọn phạm vi dữ liệu mà chúng tôi cần xoay. Sau đó, chúng tôi sao chép dữ liệu này.
2. Đặt con trỏ ở bất kỳ vị trí nào trên trang tính. Sau đó nhấp vào nút chuột phải và mở menu ngữ cảnh.
3. Sau đó nhấp vào nút "Dán đặc biệt".

Sau khi hoàn thành các bước này, bạn cần nhấp vào nút "Transpose". Thay vào đó, hãy chọn hộp bên cạnh mục này. Chúng tôi không thay đổi các cài đặt khác, sau đó nhấp vào nút “OK”.

Sau khi thực hiện các bước này, chúng ta còn lại với cùng một bảng, chỉ có các hàng và cột của nó được sắp xếp khác nhau. Cũng lưu ý rằng các ô chứa thông tin giống nhau được đánh dấu bằng màu xanh lá cây. Câu hỏi: điều gì đã xảy ra với các công thức trong phạm vi ban đầu? Vị trí của họ đã thay đổi, nhưng bản thân họ vẫn còn. Địa chỉ của các ô chỉ đơn giản là thay đổi thành địa chỉ được hình thành sau khi chuyển vị.

Hầu hết các hành động tương tự cần được thực hiện để chuyển đổi các giá trị, không phải công thức. Trong trường hợp này, bạn cũng cần sử dụng menu Dán Đặc biệt, nhưng trước đó, hãy chọn phạm vi dữ liệu chứa các giá trị. Chúng ta thấy rằng cửa sổ Dán Đặc biệt có thể được gọi theo hai cách: thông qua một menu đặc biệt trên ruy-băng hoặc menu ngữ cảnh.

Phương pháp 2. Hàm TRANSP trong Excel

Trên thực tế, phương pháp này không còn được sử dụng tích cực như lúc mới bắt đầu xuất hiện chương trình bảng tính này. Điều này là do phương pháp này phức tạp hơn nhiều so với việc sử dụng Paste Special. Tuy nhiên, nó được sử dụng trong việc tự động hóa chuyển vị bảng.

Ngoài ra, hàm này có trong Excel, vì vậy bạn bắt buộc phải biết về nó, mặc dù nó gần như không còn được sử dụng nữa. Trước đó chúng ta đã xem xét thủ tục, cách làm việc với nó. Bây giờ chúng ta sẽ bổ sung kiến ​​thức này bằng một ví dụ bổ sung.

1. Đầu tiên, chúng ta cần chọn phạm vi dữ liệu sẽ được sử dụng để chuyển đổi bảng. Bạn chỉ cần chọn khu vực ngược lại. Ví dụ, trong ví dụ này, chúng ta có 4 cột và 6 hàng. Do đó, cần chọn vùng có đặc điểm đối lập: 6 cột và 4 hàng. Hình ảnh cho thấy nó rất tốt.
2. Sau đó, chúng tôi ngay lập tức bắt đầu điền vào ô này. Điều quan trọng là không vô tình loại bỏ lựa chọn. Do đó, bạn phải chỉ định công thức trực tiếp trong thanh công thức.
3. Tiếp theo, nhấn tổ hợp phím Ctrl + Shift + Enter. Hãy nhớ rằng đây là công thức mảng, vì chúng ta đang làm việc với một tập hợp lớn dữ liệu cùng một lúc, dữ liệu này sẽ được chuyển sang một tập hợp ô lớn khác.

Sau khi nhập dữ liệu xong, chúng ta nhấn phím Enter, sau đó sẽ có kết quả như sau.

Chúng tôi thấy rằng công thức đã không được chuyển sang bảng mới. Định dạng cũng bị mất. Poo

Tất cả điều này sẽ phải được thực hiện thủ công. Ngoài ra, hãy nhớ rằng bảng này có liên quan đến bảng gốc. Do đó, ngay sau khi một số thông tin được thay đổi trong phạm vi ban đầu, các điều chỉnh này sẽ tự động được thực hiện đối với bảng chuyển vị.

Do đó, phương pháp này rất phù hợp trong trường hợp bạn cần đảm bảo rằng bảng đã hoán vị được liên kết với bảng ban đầu. Nếu bạn sử dụng một phụ trang đặc biệt, khả năng này sẽ không còn nữa.

bảng tóm tắt

Đây là một phương pháp mới về cơ bản, giúp nó không chỉ có thể chuyển đổi bảng mà còn có thể thực hiện một số lượng lớn các hành động. Đúng, cơ chế chuyển vị sẽ hơi khác so với các phương pháp trước đây. Chuỗi các hành động như sau:

1. Hãy tạo một bảng xoay. Để làm điều này, bạn cần chọn bảng mà chúng ta cần chuyển vị. Sau đó, chuyển đến mục “Chèn” và tìm “Bảng tổng hợp” ở đó. Một hộp thoại giống như trong ảnh chụp màn hình này sẽ xuất hiện.
2. Tại đây, bạn có thể chỉ định lại phạm vi mà nó sẽ được thực hiện, cũng như thực hiện một số cài đặt khác. Bây giờ chúng tôi chủ yếu quan tâm đến vị trí của bảng xoay - trên một trang tính mới.
3. Sau đó, bố cục của bảng tổng hợp sẽ được tạo tự động. Cần phải đánh dấu vào đó những vật dụng mà chúng ta sẽ sử dụng, và sau đó chúng phải được chuyển đến đúng nơi. Trong trường hợp của chúng tôi, chúng tôi cần chuyển mục “Sản phẩm” thành “Tên cột” và “Giá mỗi kiện” thành “Giá trị”.
4. Sau đó, bảng tổng hợp cuối cùng sẽ được tạo. Một phần thưởng bổ sung là tính toán tự động giá trị cuối cùng.
5. Bạn cũng có thể thay đổi các cài đặt khác. Ví dụ: bỏ chọn mục "Giá mỗi sản phẩm" và chọn mục "Tổng chi phí". Kết quả là chúng ta sẽ có một bảng chứa thông tin về giá của các sản phẩm. Phương pháp chuyển vị này có nhiều chức năng hơn các phương pháp khác. Hãy mô tả một số lợi ích của bảng tổng hợp:

1. Tự động hóa. Với sự trợ giúp của bảng tổng hợp, bạn có thể tóm tắt dữ liệu tự động, cũng như thay đổi vị trí của các cột và các cột tùy ý. Để làm điều này, bạn không cần thực hiện thêm bất kỳ bước nào.
2. Tính tương tác. Người dùng có thể thay đổi cấu trúc của thông tin nhiều lần nếu anh ta cần để thực hiện nhiệm vụ của mình. Ví dụ, bạn có thể thay đổi thứ tự của các cột, cũng như nhóm dữ liệu một cách tùy ý. Điều này có thể được thực hiện nhiều lần nếu người dùng cần. Và nó thực sự mất chưa đầy một phút.
3. Dễ dàng định dạng dữ liệu. Rất dễ dàng để sắp xếp một bảng xoay theo cách một người muốn. Để làm điều này, chỉ cần thực hiện một vài cú nhấp chuột.
4. Nhận giá trị. Số lượng lớn các công thức được sử dụng để tạo báo cáo nằm trong khả năng truy cập trực tiếp của một người và dễ dàng tích hợp vào bảng tổng hợp. Đây là những dữ liệu như tính tổng, lấy trung bình cộng, xác định số ô, nhân, tìm giá trị lớn nhất và nhỏ nhất trong mẫu xác định.
5. Khả năng tạo biểu đồ tóm tắt. Nếu PivotTable được tính toán lại, các biểu đồ liên quan của chúng sẽ tự động được cập nhật. Có thể tạo nhiều biểu đồ tùy ý. Tất cả chúng có thể được thay đổi cho một nhiệm vụ cụ thể và chúng sẽ không được kết nối với nhau.
6. Khả năng lọc dữ liệu.
7. Có thể xây dựng một bảng tổng hợp dựa trên nhiều hơn một tập hợp thông tin nguồn. Do đó, chức năng của chúng sẽ trở nên tuyệt vời hơn.

Đúng, khi sử dụng bảng tổng hợp, phải tính đến các hạn chế sau:

1. Không phải tất cả thông tin đều có thể được sử dụng để tạo bảng tổng hợp. Trước khi chúng có thể được sử dụng cho mục đích này, các tế bào phải được chuẩn hóa. Nói một cách đơn giản, hãy làm đúng. Yêu cầu bắt buộc: có dòng tiêu đề, đầy đủ tất cả các dòng, định dạng dữ liệu bình đẳng.
2. Dữ liệu được cập nhật bán tự động. Để nhận thông tin mới trong bảng tổng hợp, bạn phải nhấp vào một nút đặc biệt.
3. Bảng tổng hợp chiếm nhiều không gian. Điều này có thể dẫn đến một số gián đoạn của máy tính. Ngoài ra, tập tin sẽ khó gửi qua E-mail vì điều này.

Ngoài ra, sau khi tạo bảng tổng hợp, người dùng không có khả năng thêm thông tin mới.