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

Nội dung được viết bởi Tommy Dũng Lê

Ở 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:

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

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.

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

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é.

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

Ở đâ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ả:

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

phần trước, Gitiho đã chỉ cho bạn cách sử dụng tham chiếu cell: tham chiếu tương đốitham 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:

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

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?

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

Ở đâ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

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

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

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

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:

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

Ở đâ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:

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

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

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

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:

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

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 đốitham 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

Khóa học phù hợp với bất kỳ ai đang muốn tìm hiểu lại Excel từ con số 0. Giáo án được Gitiho cùng giảng viên thiết kế phù hợp với công việc thực tế tại doanh nghiệp, bài tập thực hành xuyên suốt khóa kèm đáp án và hướng dẫn giải chi tiết. Tham khảo ngay bên dưới!

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

0 thảo luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội
Giấy phép mạng xã hội số: 588, cấp bởi Bộ thông tin và truyền thông