Đi ngang với Công thức - Mẹo Excel

Mục lục

Xem video

Paste Special Transpose thực hiện một bản chụp nhanh dữ liệu

Ai đó đã xây dựng bảng tra cứu này theo chiều ngang, trải dài trên C1: N2. Tôi nhận ra rằng tôi có thể sử dụng HLOOKUP thay vì VLOOKUP, nhưng tôi muốn chuyển dữ liệu trở lại hướng dọc.

Chép C1: N2. Nhấp chuột phải vào A4 và chọn tùy chọn Transpose từ Tùy chọn dán. Transpose là một từ ưa thích trong Excel để "xoay dữ liệu sang một bên".

Dán Transpose đặc biệt

Tôi chuyển đổi rất nhiều. Nhưng tôi sử dụng alt = "" + E, S, E, Enter để chuyển vị thay vì nhấp chuột phải.

Tuy nhiên đang có một vấn đề. Transpose là một ảnh chụp nhanh dữ liệu một lần. Điều gì sẽ xảy ra nếu bạn có công thức trong dữ liệu ngang? Có cách nào để chuyển đổi với một công thức?

Cách thứ nhất hơi kỳ quái. Nếu bạn đang cố gắng chuyển đổi 12 ô ngang, bạn cần chọn 12 ô dọc trong một lựa chọn. Bắt đầu nhập một công thức chẳng hạn như =TRANSPOSE(C2:N2)trong ô hiện hoạt nhưng không nhấn Enter. Thay vào đó, hãy giữ Ctrl + Shift bằng tay trái rồi nhấn Enter. Điều này đặt một công thức mảng duy nhất trong các ô đã chọn. Công thức TRANSPOSE này sẽ trả về 12 câu trả lời và chúng sẽ xuất hiện trong 12 ô đã chọn.

Ctrl + Shift + Enter cho Công thức mảng

Khi dữ liệu trong bảng ngang thay đổi, các giá trị tương tự sẽ xuất hiện trong bảng dọc của bạn.

Nhưng công thức mảng không được biết đến nhiều. Một số tân binh bảng tính có thể cố gắng chỉnh sửa công thức của bạn và quên nhấn Ctrl + Shift + Enter.

Kết quả của TRANSPOSE

Để tránh sử dụng công thức mảng, hãy sử dụng kết hợp INDEX và ROW, như được hiển thị bên dưới. =ROW(1:1)là một cách thông minh để viết số 1. Khi bạn sao chép công thức này xuống, tham chiếu hàng sẽ thay đổi thành 2: 2 và trả về 2.

Hàm INDEX cho biết bạn đang nhận được câu trả lời từ C2: N2 và bạn muốn mục thứ n từ phạm vi.