Hướng dẫn Excel cơ bản về cách sử dụng Tham chiếu - Phần 1

Ở bài viết này, Gitiho sẽ chỉ cho bạn khi nào nên dùng tham chiếu tuyệt đối (Absolute reference) và tham chiếu tương đối (Relative reference).

Và theo định nghĩa của Microsoft, tham chiếu một cell nói về việc tra cứu một cell nhất định nào đó hoặc một phạm vi cell trên cả bảng dữ liệu để Excel có thể tìm giá trị hay dữ liệu để hoàn thành công thức.

Vậy tham chiếu tương đối là như thế nào. Ở đây bạn được cho một bảng dữ kiện như sau:

Bạn được yêu cầu tính tổng của những ứng dụng bên Cột bên trái, qua từng quý. Vậy công thức của bạn như sau:

F5 = Sum(B5:E5)

Kết quả = 1100

Và kéo xuống để hoàn thiện Cột F

Khi bạn vào cell F6 để xem công thức sẽ thay đổi thành = Sum(B6:E6)

Còn ở cell F7 công thức sẽ là = Sum(B7:E7)

Và đây được gọi là tham chiếu tương đối bởi vì những cell được tham chiếu thay đổi khi công thức thay đổi.

Sau đó, bạn lại được yêu cầu tính phần trăm của từng app trên tổng doanh thu. Bạn phải làm gì ?

Bước 1: Bạn cần phải tính tổng doanh thu. Bạn có thể làm vô cùng đơn giản với hàm Sum với công thức như sau:

= Sum(F5:F7)

= 2495

Giờ đến bước 2, tính phần trăm thì sao. Đây cũng là phép chia căn bản với công thức:

= G5/F7

= 44% (0.440882 và bạn bấm nút phần trăm trong mục number để ra phần trăn nhé)

Vậy bạn có thể kéo xuống như khi tính Cột F không nhỉ. Chúng ta cùng thử nhé.

Ở đây công thức thay đổi sai, đối với ứng dụng Utility, công thức áp dụng đúng nhưng đối với tổng cả 3 ứng dụng thì cần phải được giữ nguyên. Và đây là lúc bạn cần tham chiếu tuyệt đối, nghĩa là bạn cần phải thêm các kí hiệu $ lên các cell được tham chiếu (và bạn có thể dùng phím tắt F4).

Quick tip: Nếu bạn bấm phím F4 1 lần thì cả Dòng lẫn Cột của bạn đều sẽ được đặt dấu $. Bạn F4 lần thứ 2, bạn sẽ chỉ nhận dấu $ trên Dòng của bạn. Đối với lần thứ 3, Cột của bạn sẽ được nhận dấu. Và lần thứ 4 là sẽ bỏ dấu $ trên cả Dòng và Cột của bạn.

Vậy sau khi bạn đã đặt dấu $ lên công thức của bạn, đây sẽ là kết quả:

Ở phần trước, Gitiho đã chỉ cho bạn cách sử dụng tham chiếu cell: tham chiếu tương đối và tham chiếu tuyệt đối. Và ở phần này, để hiểu thêm về cách sử dụng cũng như biết cách kết hợp giữa 2 loại tham chiếu này, Gitiho sẽ hướng dẫn cho bạn rõ ràng hơn ở ví dụ bên dưới:

Cũng là dữ liệu ở phần trước nhưng bạn được yêu cầu tính phần trăm của từng quý trên tổng doanh thu của cả năm. Bạn có thể làm với công thức đơn giản như trên

= B5/F5

= 9%

Tuy nhiên khi bạn kéo sang ngang để hoàn thiện Dòng 5 thì điều gì sẽ xảy ra?

Ở đây công thức thay đổi khi cell thay đổi là điều bạn muốn đối với doanh thu của quý 2 nhưng bạn vẫn muốn phải cố định cell tổng doanh thu. Vì vậy tham chiếu tương đối ở đây vẫn chưa đủ. Vì vậy bạn phải cố định cell với tham chiếu cố định bằng nút tắt F4.

Đây là kết quả mà bạn mong muốn đúng không

Vậy để hoàn thiện bản báo cáo chỉ cần kéo xuống thôi đúng không nào? Tuy nhiên kết quả lại không đúng như vậy

Thoạt nhìn, bạn nghĩ bản báo cáo đúng rồi mà. Yayyy. Xong bản cáo cáo rồi. Nhưng nếu bạn nhìn kĩ lại, tổng phần trăm của các quý cộng lại không bằng 100%. Vậy sai sót ở đâu? Nếu bạn nhấn vào cell quý 4 của ứng dụng Utility, bạn sẽ thấy công thức sai như sau:

Ở đây, công thức đã thay đổi theo Dòng nhưng Cột vẫn giữ nguyên. Điều này đã làm cho Excel trả ra kết quả sai. Phép tính đúng là quý 4 chia cho tổng của ứng dụng Utility cơ mà. Vậy bạn sai ở đâu? Bạn muốn Cột được cố định nhưng Dòng thì thay đổi. Nên bạn chỉ cần bỏ dấu $ trước Dòng 5 như sau:

Vậy kết quả thay đổi:

Nếu bạn chưa tin, bạn có thể cộng lại từng quý xem có đủ 100% không hoặc cách đơn giản là bạn nhấn vào cell cuối cùng và check công thức:

Công thức lấy quý 4 chia cho tổng của ứng dụng Productivity. Đây mới là kết quả đúng cho bản báo cáo.

Vậy là bạn đã được biết thêm về cách kết hợp tham chiếu tương đối và tham chiếu tuyệt đối rồi. Dù đây là những kiến thức cơ bản nhưng lại vô cùng thiết yếu trong những bản báo cáo của bạn. Ngoài ra, bạn có thể truy cập ngay Gitiho.com để tìm hiểu thêm các khóa học hấp dẫn về tin học văn phòng: Word, Excel, Power Point. Có rất nhiều ưu đãi hấp dẫn đang chờ bạn.

 

 

 

 

 

 

 

 

 

 

Đánh giá bài viết này