Chuyển dữ liệu dọc thành ngang trong excel

Ksmanh.com- Theo thông thường, khi bạn có một ô có công thức =A1, sau đó bạn kéo sang phải qua các cột, bạn sẽ lần lượt nhận được kết quả là =B1, =C1, =D1, =E1. Tuy nhiên, bạn lại không muốn vậy. Thực chất điều bạn muốn khi kéo sang phải lại là =A2, =A3, =A4, =A5.

Thật không may Excel lại không hề có chức năng có sẵn nào tương tự như vậy cả. Nhưng với khả năng kết hợp công thức, bạn hoàn toàn có thể làm được với hàm INDIRECT.

Ví dụ bạn có một dữ liệu từ A1 đến A10 lần lượt là từ 1 đến 10. Và sau đó tại ô D1, bạn gõ công thức sau: =INDIRECT(ADDRESS(COLUMN(A:A),1)) và sau đó kéo sang bên phải như hình dưới đây. Bạn có thể thấy nó đúng với mong muốn của bạn, bắt đầu từ ô D1 là số 1, và ô E1 lấy giá trị 2 tương ứng với ô A2 và cứ thế.



Để có thể hiểu được công thức này, bạn cần tìm hiểu hàm INDIRECT. Nói nôm na, hàm INDIRECT có thể giúp bạn lấy giá trị của một địa chỉ mà bạn quy định trước, đó là lý do mà tôi phải dùng hàm ADDRESS(hàng, cột) để ra kết quả là một địa chỉ. Và để lấy địa chỉ chính xác, tôi sử dụng hàm COLUMN để lấy giá trị số của cột, chẳng hạn cột A là số 1 để khi kéo ngang qua, cột B sẽ mang số 2 và cột C là số 3 vậy. Kế đó, phần tham số cột thì tôi để 1 tương ứng với cột A. Như vậy, hàm ADDRESS sẽ lần lượt lấy địa chỉ hàng 1 cột 1 tương ứng A1, hàng 2 cột 1 tương ứng A2,… giúp tôi.

Cách làm này đặc biệt hữu ích với những bảng tính có tiêu đề theo hàng (thay vì theo cột như truyền thống), và bạn cần phải biến chuyển điều đó bằng cách linh hoạt sử dụng công thức kể trên.

Trong trường hợp trên, bạn đã được trải nghiệm cách tham chiếu với một ô. Vậy còn nếu bạn muốn tham chiếu với một vùng thì phải làm sao?

Trở lại với ví dụ hồi nãy, bây giờ tôi muốn ô D1 sẽ bằng ô A1, ô E1 sẽ bằng ô A1 + A2, và cứ thế, ô F1 sẽ bằng ô A1 + A2 + A3. Bạn hãy áp dụng công thức sau vào ô D1: =SUM(INDIRECT("A1:" &ADDRESS(COLUMN(A:A),1))) rồi sau đó cũng lại lần nữa kéo sang bên phải. Lần này kết quả sẽ như sau:



Ở đây mọi thứ cũng như cũ trừ sự xuất hiện của ký tự “A1:” trước công thức ADDRESS. Bạn có thể liên tưởng về kết quả của công thức trước, sau đó nối “A1:” vào thì bạn sẽ hiểu thôi. Ví dụ như ở ô E1 sau khi hàm ADDRESS xong tôi sẽ có kết quả A2, và sau đó nối “A1:” vào thì tôi sẽ được giá trị mới là A1:A2, và kế tiếp thì hàm INDIRECT và cuối cùng hàm SUM sẽ thực hiện nốt phần còn lại. Thú vị phải không bạn?