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 đề phía trên 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 ví dụ cho một trang tính.

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. Hiển thị hộp thoại Tùy chọn Excel. (Trong Excel 2007, hãy nhấp vào nút Office và sau đó nhấp vào Tùy chọn Excel. Trong Excel 2010 và Excel 2013, hiển thị tab Tệp của dải băng và sau đó nhấp vào Tùy chọn.)

  2. Nhấp vào liên kết Công thức ở phía bên trái của hộp thoại. (Xem Hình 2.)

  3. Đảm bảo rằng hộp kiểm Sử dụng Tên Bảng trong Công thức được chọn.

  4. Bấm OK.

Với việc 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 '2013' – Sales '2008'

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 “2013” trả về ô ở giao điểm của Phạm vi Bán hàng và cột “2013”. 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 (9177) áp dụng cho Microsoft Excel 2007, 2010 và 2013. Bạn có thể tìm phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: