Chris đã thiết lập một trang tính trong đó anh ấy sử dụng các phạm vi (hàng) được đặt tên trong công thức của mình. Anh ấy đã đặt tên cho toàn bộ hàng bán hàng là “Bán hàng” và sau đó sử dụng tên Bán hàng trong các công thức khác nhau. Ví dụ: trong bất kỳ cột nhất định nào, anh ta có thể nói = Doanh số và giá trị của hàng Doanh số, đối với cột đó, được trả về bằng công thức. Chris đã tự hỏi làm thế nào để sử dụng cùng một kỹ thuật công thức để tham chiếu đến các ô trong các cột khác nhau.

Có một số cách khác nhau có thể được thực hiện. Trước hết, bạn có thể sử dụng hàm INDEX để tham chiếu đến các ô. Cách nghiêm ngặt để tham chiếu đến giá trị của Doanh số trong cùng một cột như sau:

=INDEX(Sales,1,COLUMN())

Điều này hoạt động nếu phạm vi có tên Bán hàng thực sự tham chiếu đến toàn bộ hàng trong trang tính. Nếu không (ví dụ: Bán hàng có thể tham chiếu đến các ô C10: K10), thì công thức sau đề cập đến giá trị của Doanh số trong cùng một cột mà công thức xảy ra:

=INDEX(Sales,1,COLUMN()-COLUMN(Sales)+1)

Nếu bạn muốn tham chiếu đến một cột khác, thì chỉ cần điều chỉnh giá trị được thêm vào ký hiệu cột trong hàm INDEX. Ví dụ: nếu bạn muốn xác định sự khác biệt giữa doanh số bán hàng cho cột hiện tại và doanh số bán hàng trong cột trước đó, thì bạn sẽ sử dụng như sau:

=INDEX(Sales,1,COLUMN()-COLUMN(Sales)+1) - INDEX(Sales,1,COLUMN()-COLUMN(Sales))

Phiên bản “viết tắt” cho công thức này sẽ như sau:

=Sales - INDEX(Sales,1,COLUMN()-COLUMN(Sales))

Có những hàm khác mà bạn có thể sử dụng ngoài INDEX (chẳng hạn như OFFSET), nhưng kỹ thuật này vẫn giống nhau — bạn phải tìm cách tham chiếu đến một phần bù từ cột hiện tại.

Tuy nhiên, có một cách dễ dàng hơn để có được dữ liệu mong muốn. Giả sử rằng phạm vi Bán hàng của bạn cũng có một hàng tiêu đề về nó, tương tự như những gì được hiển thị trong hình sau: (Xem Hình 1.)

image

Hình 1. Dữ liệu bán hàng ví dụ.

Hàng tiêu đề liệt kê các năm cho phạm vi và các giá trị trong tiêu đề là những giá trị thực sự tạo nên phạm vi Bán hàng. Để đảm bảo kỹ thuật này sẽ hoạt động, hãy làm theo các bước sau:

  1. Chọn Tùy chọn từ menu Công cụ. Excel sẽ hiển thị hộp thoại Tùy chọn.

  2. Đảm bảo rằng tab Tính toán được hiển thị. (Xem Hình 2.)

  3. Đảm bảo rằng hộp kiểm Chấp nhận Nhãn trong Công thức được chọn.

  4. Bấm OK.

Khi thay đổi cấu hình này được thực hiện, bạn có thể sử dụng công thức sau đây làm công thức của mình:

=Sales '2009' – Sales '2004'

Những gì bạn thực sự đang làm là hướng dẫn Excel làm việc với sự kết hợp của các ô. Trong trường hợp này, Bán hàng “2009” trả về ô ở giao điểm của Phạm vi Bán hàng và cột “2009”. Một kết hợp tương tự được trả về cho phần công thức ở bên phải dấu trừ. Kết quả là phép trừ hai giá trị bạn muốn.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (3202) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: