Trong thế giới bảng tính kỹ thuật số, Google Sheets là một công cụ mạnh mẽ và linh hoạt, được hàng triệu người dùng trên toàn thế giới tin dùng để tổ chức, phân tích và quản lý dữ liệu. Để khai thác tối đa sức mạnh của nó, việc hiểu và vận dụng các loại tham chiếu ô là điều vô cùng cần thiết. Tham chiếu ô cho phép bạn chỉ định vị trí của dữ liệu mà bạn muốn sử dụng trong công thức, và cách chúng hoạt động khi bạn sao chép công thức có thể tạo nên sự khác biệt lớn giữa một bảng tính hiệu quả và một mớ hỗn độn.
Có ba loại tham chiếu ô cơ bản trong Google Sheets: tham chiếu tương đối, tham chiếu tuyệt đối và tham chiếu hỗn hợp. Mỗi loại có mục đích và cách thức hoạt động riêng, nhưng tất cả đều xoay quanh việc kiểm soát cách công thức thay đổi khi được di chuyển hoặc sao chép. Phím tắt F4 chính là “chìa khóa vàng” giúp bạn chuyển đổi linh hoạt giữa các loại tham chiếu này một cách nhanh chóng, tiết kiệm thời gian và công sức đáng kể. Bài viết này của thoibaocongnghe.net sẽ đi sâu vào từng loại tham chiếu, hướng dẫn bạn cách sử dụng phím F4 hiệu quả để biến đổi các công thức của mình, từ đó nâng cao kỹ năng làm việc với Google Sheets lên một tầm cao mới. Hãy cùng khám phá!
Để thực hành theo hướng dẫn này, bạn có thể tự tạo một bản sao của bảng tính Google Sheets được sử dụng trong các ảnh chụp màn hình. Sau khi nhấp vào liên kết, bạn sẽ được yêu cầu đăng nhập vào tài khoản Google của mình nếu chưa thực hiện. Khi đã đăng nhập, hãy nhấp vào “Tạo bản sao” để thêm một bản sao của bảng tính mà chỉ bạn mới có thể xem và chỉnh sửa vào Google Drive của mình.
Hiểu Về Tham Chiếu Tương Đối Trong Google Sheets
Tham chiếu tương đối là loại tham chiếu mặc định trong Google Sheets. Điều này có nghĩa là khi bạn tạo một công thức và sao chép nó sang các ô khác, các tham chiếu trong công thức sẽ tự động điều chỉnh dựa trên vị trí mới của ô. Đây là một tính năng rất hữu ích cho các tác vụ lặp đi lặp lại trên các hàng hoặc cột dữ liệu khác nhau.
Giả sử bạn có một bảng điểm của các đội và muốn tính điểm trung bình của mỗi thành viên trong mỗi đội.
=SUM(C2/B2)Khi bạn nhập công thức trên vào ô D2 và nhấn Enter, nó sẽ chia điểm của đội A (ô C2) cho số thành viên của đội (ô B2) để cho ra điểm trung bình của một thành viên đội A.
Công thức =SUM(C2/B2) trong Google Sheets sử dụng tham chiếu tương đối để tính điểm trung bình
Vì các tham chiếu đến các ô C2 và B2 trong công thức này là tương đối – nghĩa là công thức tham chiếu đến các ô cách ô hiện tại một và hai vị trí về bên trái tương ứng – khi bạn áp dụng công thức này cho các ô còn lại trong cột bằng cách nhấp và kéo Fill Handle (ô vuông nhỏ ở góc dưới bên phải của ô D2) xuống dưới, các tham chiếu sẽ tự điều chỉnh để thực hiện cùng phép tính trên mỗi hàng tương ứng.
Sao chép công thức với tham chiếu tương đối xuống các ô khác trong Google Sheets bằng cách kéo Fill Handle
Google Sheets thậm chí có thể đề nghị tự động hoàn tất quá trình điền này cho bạn. Để tăng tốc quá trình này hơn nữa, thay vì nhấp và kéo Fill Handle, bạn có thể nhấp đúp vào nó. Thao tác này buộc Google Sheets lặp lại công thức trong mọi ô có dữ liệu ở ô liền kề.
Điền tự động công thức tham chiếu tương đối trong Google Sheets bằng cách nhấp đúp vào Fill Handle
Tuy nhiên, cần lưu ý rằng việc sao chép công thức từ thanh công thức (formula bar) ở đầu cửa sổ Google Sheets và dán vào một ô khác sẽ sao chép công thức chính xác như nó vốn có. Nói cách khác, các tham chiếu sẽ không thay đổi tương đối so với vị trí ô đích. Để đảm bảo tham chiếu tương đối hoạt động như mong đợi, bạn cần thực hiện thao tác sao chép và dán trong chính bảng tính. Dù nội dung của ô được sao chép được nhân đôi ở bất kỳ đâu trong bảng tính này hoặc bảng tính khác, nó sẽ luôn chia giá trị trong ô bên trái của ô hiện hoạt một vị trí cho giá trị trong ô bên trái của ô hiện hoạt hai vị trí.
Cuối cùng, để định dạng số cho gọn gàng, hãy chọn tất cả các ô kết quả và nhấp vào nút “Giảm số thập phân” (Decrease Decimal Places) trên thanh công cụ cho đến khi bạn hài lòng với cách dữ liệu được trình bày.
Chọn các ô chứa số thập phân trong Google Sheets và nhấn nút Giảm số thập phân để định dạng lại
Sử Dụng F4 Tạo Tham Chiếu Tuyệt Đối Trong Google Sheets
Như tên gọi của chúng, tham chiếu tuyệt đối trong Google Sheets vẫn giữ nguyên cố định, ngay cả khi chúng được di chuyển hoặc sao chép đến các vị trí khác nhau trong bảng tính. Điều này cực kỳ hữu ích khi bạn muốn một công thức luôn tham chiếu đến một ô cụ thể, không đổi.
Hãy tưởng tượng bạn được giao nhiệm vụ tính tổng chi phí của mười sản phẩm sau khi cộng thuế. Thuế suất được cố định ở một ô duy nhất.
Bảng tính Google Sheets với cột trống, nơi sẽ tính giá sản phẩm sau khi cộng thuế bằng tham chiếu tuyệt đối
Để thực hiện điều này, trong ô C2, bạn nhập công thức:
=SUM(B2+(B2*E2))Và nhấn Enter. Công thức này tính giá sản phẩm đầu tiên sau thuế.
Tính giá sản phẩm sau thuế bằng công thức ban đầu trong Google Sheets, trước khi cố định tham chiếu E2
Tuy nhiên, khi bạn áp dụng công thức này cho các ô còn lại trong cột C bằng cách nhấp đúp vào Fill Handle trong ô C2, kết quả lại không như mong đợi. Điều này là do mỗi khi công thức di chuyển xuống một hàng, tất cả các tham chiếu ô của nó cũng di chuyển theo.
Ví dụ, trong ô C3, thay vì tham chiếu đến thuế suất trong ô E2, công thức lại tham chiếu đến ô E3, vốn đang trống.
=SUM(B3+(B3*E3))
Lỗi tham chiếu trong Google Sheets khi sao chép công thức mà không cố định ô thuế, minh họa vấn đề của tham chiếu tương đối
Thay vào đó, bạn chỉ muốn tham chiếu đến ô trong cột B điều chỉnh theo hàng mà nó đang nằm, còn tham chiếu đến ô E2 phải luôn cố định.
Để làm được điều này, trước tiên, hãy quay lại ô ban đầu chứa công thức (trong trường hợp này là ô C2), và nhấn F2 để vào chế độ chỉnh sửa ô. Sau đó, dùng các phím mũi tên để di chuyển con trỏ trực tiếp bên cạnh hoặc bên trong tham chiếu E2, rồi nhấn F4. Bạn sẽ thấy các dấu đô la ($) được thêm vào cả tham chiếu cột và hàng của ô E2.
Công thức trong Google Sheets với tham chiếu tuyệt đối $E cho tỷ lệ thuế, cố định ô tham chiếu bằng phím F4
Điều này có nghĩa là một tham chiếu tương đối trước đây đã được chuyển đổi thành một tham chiếu tuyệt đối, vì vậy nó sẽ luôn giữ nguyên bất kể công thức được sao chép ở đâu trong bảng tính.
Lần tới, thay vì phải quay lại chỉnh sửa ô, bạn có thể nhấn F4 ngay sau khi đã nhập một tham chiếu trong công thức để biến nó thành tham chiếu tuyệt đối ngay lập tức.
Bây giờ, nhấn Enter để xác nhận công thức đã chỉnh sửa, rồi nhấp và kéo Fill Handle từ ô C2 xuống dưới để thay thế các công thức sai trong các ô còn lại của cột C bằng các công thức chính xác.
Sao chép công thức với tham chiếu tuyệt đối bằng Fill Handle trong Google Sheets, đảm bảo kết quả chính xác
Nhấp đúp vào bất kỳ ô kết quả nào để kiểm tra xem công thức hoạt động đúng như mong đợi với tham chiếu tuyệt đối đến ô E2.
Kiểm tra công thức trong Google Sheets sau khi áp dụng tham chiếu tuyệt đối, hiển thị giá sản phẩm đã bao gồm thuế
Dùng F4 Tạo Tham Chiếu Hỗn Hợp Trong Google Sheets
Loại tham chiếu cuối cùng trong Google Sheets là tham chiếu hỗn hợp, loại tham chiếu này chứa cả tham chiếu tương đối và tham chiếu tuyệt đối cùng một lúc. Chúng hữu ích khi bạn muốn cố định tham chiếu cột nhưng để tham chiếu hàng linh hoạt, hoặc ngược lại. Chúng ta hãy xem một ví dụ cho từng trường hợp.
Tham Chiếu Hỗn Hợp: Cố Định Tham Chiếu Cột (Hàng Thay Đổi)
Trong ví dụ này, giả sử bạn cần tính tổng lương của mỗi nhân viên trong ba năm, bao gồm cả các khoản thưởng.
Bảng tính Google Sheets với các cột trống, chuẩn bị tính tổng lương và thưởng cho nhân viên
Hãy bắt đầu bằng cách nhập công thức đầu tiên vào ô F2 để tính tổng thanh toán cho nhân viên A vào năm 2019, rồi nhấn Enter. Trong trường hợp này, công thức là:
=B2+E2
Công thức ban đầu trong Google Sheets để cộng lương và thưởng năm 2019 cho nhân viên A, trước khi cố định cột E
Bây giờ, trước khi bạn sao chép công thức này, hãy xem xét từng phần để xác định những phần nào cần tương đối và những phần nào cần tuyệt đối:
- B: Tham chiếu đến cột B cần là tương đối, để khi bạn sao chép công thức từ cột F sang cột G và H, công thức sẽ tính toán chính xác tổng thanh toán cho năm 2020 và 2021 tương ứng.
- 2: Tham chiếu đến hàng 2 cũng phải là tương đối, vì bạn muốn các tham chiếu trong công thức điều chỉnh cho chi tiết của từng nhân viên khi bạn di chuyển xuống các hàng.
- E: Mọi tham chiếu đến các ô trong cột E phải không thay đổi, ngay cả khi bạn sao chép công thức từ cột F sang cột G và H, vì vậy đây phải là một tham chiếu tuyệt đối.
- 2: Giống như tham chiếu trước đến hàng 2, tham chiếu này cần là tương đối để áp dụng cho số liệu của từng nhân viên.
Tóm lại, bạn cần tham chiếu đến cột E trong mỗi công thức là tuyệt đối, trong khi tất cả các tham chiếu khác vẫn là tương đối.
Để làm điều này, nhấp đúp vào ô F2 để vào chế độ chỉnh sửa ô. Sau đó, đặt con trỏ vào hoặc bên cạnh tham chiếu đến ô E2, và nhấn F4 (ba lần) cho đến khi chỉ có tham chiếu đến cột E được đặt trước bởi ký hiệu $.
=B2+$E2
Tham chiếu hỗn hợp $E2 trong công thức Google Sheets, cố định cột E nhưng cho phép hàng thay đổi
Tiếp theo, nhấn Enter để xác nhận công thức, và nhấp và kéo Fill Handle từ ô F2 đến ô H2 để hoàn thành các phép tính cho nhân viên A.
Kết quả tính tổng lương và thưởng của nhân viên A trong ba năm bằng công thức tham chiếu hỗn hợp trong Google Sheets
Để kiểm tra xem tham chiếu hỗn hợp đã hoạt động đúng như mong đợi, nhấp đúp vào ô G2 và xác minh rằng các ô tiền lệ chính xác được tô sáng – trong trường hợp này là các ô C2 và E2.
Kiểm tra các ô tiền lệ của công thức trong Google Sheets sau khi sử dụng tham chiếu hỗn hợp $E2
Bây giờ, hãy hoàn thiện dữ liệu của bạn bằng cách chọn các ô từ F2 đến H2 và nhấp đúp vào Fill Handle để sao chép công thức này xuống các hàng còn lại.
Chọn ba ô chứa công thức tham chiếu hỗn hợp và kéo Fill Handle để sao chép xuống các hàng còn lại trong Google Sheets
Một lần nữa, nhấp đúp vào bất kỳ ô nào để xác nhận rằng công thức nó chứa tham chiếu đến các ô chính xác.
Tham Chiếu Hỗn Hợp: Cố Định Tham Chiếu Hàng (Cột Thay Đổi)
Trong ví dụ cuối cùng này, mục tiêu của bạn là xác định mỗi cửa hàng dự kiến sẽ kiếm được bao nhiêu tiền dựa trên tốc độ tăng trưởng hàng năm được dự kiến.
Bảng tính Google Sheets với các ô trống, chuẩn bị tính lợi nhuận dự kiến của mỗi cửa hàng dựa trên tốc độ tăng trưởng
Như thường lệ, hãy bắt đầu bằng cách nhập công thức đầu tiên, và từ đó xác định những gì cần tương đối và những gì cần cố định. Trong trường hợp này, trong ô C4, bạn nhập:
=B4+C3Để cộng điểm khởi đầu năm 2020 cho cửa hàng 1 với tăng trưởng dự kiến cho năm 2021.
Công thức ban đầu trong Google Sheets để cộng doanh số khởi điểm với tăng trưởng dự kiến năm 2021 của cửa hàng
Bây giờ, hãy xem xét các tham chiếu trong công thức này:
- B: Tham chiếu cột này cần là tương đối, vì bạn muốn tổng cộng dồn khi bạn di chuyển ngang qua hàng, cộng tổng hàng năm vào tăng trưởng dự kiến của năm tiếp theo.
- 4: Tham chiếu hàng này cũng cần là tương đối, vì bạn muốn tham chiếu thay đổi theo hàng mà nó đang nằm.
- C: Giống như tham chiếu đến cột B, tham chiếu đến cột C cần là tương đối, để cho phép tăng trưởng cộng dồn.
- 3: Tham chiếu đến hàng 3, tuy nhiên, cần phải là tuyệt đối, vì hàng này chứa tăng trưởng dự kiến cho mỗi cửa hàng mỗi năm và không được thay đổi.
Vì vậy, hãy đặt con trỏ của bạn vào hoặc bên cạnh tham chiếu đến ô C3, và nhấn F4 (hai lần) cho đến khi dấu đô la xuất hiện trước tham chiếu đến hàng 3.
=B4+C$3
Tham chiếu hỗn hợp C trong Google Sheets, cố định hàng 3 (tốc độ tăng trưởng) nhưng cho phép cột thay đổi
Cuối cùng, áp dụng công thức cho các ô còn lại trong phạm vi bằng cách sử dụng Fill Handle, và nhấp đúp vào một ô ngẫu nhiên để kiểm tra xem tham chiếu hỗn hợp hoạt động đúng như mong đợi.
Kiểm tra công thức chứa tham chiếu hỗn hợp C trong Google Sheets, đảm bảo tính toán lợi nhuận dự kiến chính xác
Kết Luận
Việc nắm vững cách sử dụng phím F4 để tạo các loại tham chiếu khác nhau – tương đối, tuyệt đối và hỗn hợp – là một kỹ năng cơ bản nhưng vô cùng mạnh mẽ cho bất kỳ người dùng Google Sheets nào. Nó không chỉ giúp bạn xây dựng các công thức chính xác hơn mà còn tăng tốc đáng kể quy trình làm việc của bạn khi phải sao chép và điều chỉnh công thức trên nhiều ô dữ liệu. Từ việc tính toán điểm trung bình đơn giản đến quản lý tài chính phức tạp, hiểu rõ về các loại tham chiếu này sẽ đảm bảo các phép tính của bạn luôn đúng đắn và linh hoạt.
Khi quyết định nên sử dụng Google Sheets hay Microsoft Excel, có một số điểm khác biệt chính giữa hai chương trình mà bạn nên cân nhắc. Ví dụ, Excel nổi tiếng với việc cung cấp các công cụ phân tích dữ liệu nâng cao hơn, nhưng khả năng cộng tác theo thời gian thực trong Sheets lại hoạt động mượt mà hơn. Tuy nhiên, bất kể bạn chọn công cụ nào, việc biết cách hoạt động của các tham chiếu tương đối, tuyệt đối và hỗn hợp là chìa khóa để tạo ra các công thức hoạt động đúng như mong đợi. Hãy áp dụng những kiến thức này vào thực tế để tối ưu hóa công việc của bạn trên Google Sheets ngay hôm nay!
