- Tham gia
- 4/6/06
- Bài viết
- 901
- Được thích
- 2,715
Chiêu thứ 77: Chuyển đổi dữ liệu dọc thành ngang bằng công thức
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?
Một số bài viết có liên quan:
1/ Chiêu thứ 76: Không bỏ sót ô rỗng
2/ Chiêu thứ 75: Đối chiếu dữ liệu cũ và mới
3/ Chiêu thứ 74: Di chuyển công thức chứa địa chỉ tương đối mà không làm thay đổi tham chiếu
4/ Chiêu thứ 73: Thêm mô tả vào công thức của bạn
5/ Chiêu thứ 72: Tạo số tổng cộng cho biểu đồ cột nhiều thành phần (Stacked Column Chart)
6/ Chiêu thứ 71: Đánh dấu tên trục theo điều kiện
7/ Chiêu thứ 70: Thay thế điểm bằng hình
8/ Chiêu thứ 69: Biểu đồ cơn lốc
9/ Chiêu thứ 68: Vị trí thông số của trục
10/ Chiêu thứ 67: Chỉnh lại trục X khi sử dụng dữ liệu ngày tháng
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?
Một số bài viết có liên quan:
1/ Chiêu thứ 76: Không bỏ sót ô rỗng
2/ Chiêu thứ 75: Đối chiếu dữ liệu cũ và mới
3/ Chiêu thứ 74: Di chuyển công thức chứa địa chỉ tương đối mà không làm thay đổi tham chiếu
4/ Chiêu thứ 73: Thêm mô tả vào công thức của bạn
5/ Chiêu thứ 72: Tạo số tổng cộng cho biểu đồ cột nhiều thành phần (Stacked Column Chart)
6/ Chiêu thứ 71: Đánh dấu tên trục theo điều kiện
7/ Chiêu thứ 70: Thay thế điểm bằng hình
8/ Chiêu thứ 69: Biểu đồ cơn lốc
9/ Chiêu thứ 68: Vị trí thông số của trục
10/ Chiêu thứ 67: Chỉnh lại trục X khi sử dụng dữ liệu ngày tháng
Lần chỉnh sửa cuối:
Upvote
0
