Có thể bạn quan tâm: Cách Copy Bảng Excel Sang Word Nhanh Chóng, Tiện Lợi Và Chuẩn Đẹp Cho Mọi Tài Liệu

Mục lục bài viết

Mở Đầu

Excel là một trong những công cụ phần mềm văn phòng mạnh mẽ nhất hiện nay, được sử dụng rộng rãi trong các lĩnh vực như kế toán, tài chính, marketing, quản trị dự án và thậm chí trong giáo dục. Khi làm việc với các bảng tính phức tạp, chúng ta thường phải sử dụng nhiều công thức (hàm) để tính toán, phân tích dữ liệu. Tuy nhiên, một trong những vấn đề thường gặp là khóa hàm – tức là cố định một ô hoặc một dải ô trong công thức để tránh việc thay đổi khi sao chép công thức sang các vị trí khác. Nếu không biết cách khóa hàm đúng cách, bạn sẽ gặp phải những lỗi tính toán nghiêm trọng và mất thời gian sửa chữa.

Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ cơ bản đến nâng cao, về cách khóa hàm trong Excel. Chúng tôi sẽ giải thích khái niệm, các ký hiệu, các phương pháp thực hành, cũng như một số mẹo và thủ thuật để tối ưu hoá quy trình làm việc. Hãy cùng khám phá!

Có thể bạn quan tâm: Chỉnh Đơn Vị Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Khái Niệm Cơ Bản Về Khóa Hàm

1.1. Tại Sao Cần Khóa Hàm?

Khi bạn sao chép một công thức từ ô này sang ô khác, Excel tự động điều chỉnh các tham chiếu ô trong công thức dựa trên vị trí mới (đây gọi là tham chiếu tương đối). Điều này rất hữu ích khi bạn muốn tính toán cho một dải dữ liệu liên tục. Tuy nhiên, trong một số trường hợp, bạn muốn một ô tham chiếu không thay đổi khi sao chép công thức. Ví dụ:

Giá trị hằng số: Bạn có một ô chứa tỷ lệ thuế cố định (ví dụ: 0.1) và muốn áp dụng tỷ lệ này cho nhiều dòng tính thuế. Khi sao chép công thức tính thuế, ô chứa tỷ lệ thuế không nên thay đổi.
Bảng tham chiếu: Khi sử dụng hàm VLOOKUP hoặc INDEX/MATCH, bạn cần tham chiếu đến một bảng dữ liệu cố định.
Công thức phức tạp: Khi công thức chứa nhiều phần, một phần có thể cần cố định trong khi phần còn lại thay đổi.

1.2. Hai Loại Tham Chiếu

Excel hỗ trợ ba loại tham chiếu trong công thức:

Loại tham chiếu	Ký hiệu	Mô tả
Tương đối (Relative)	`A1`	Tham chiếu thay đổi khi sao chép công thức.
Tuyệt đối (Absolute)	`$A$1`	Cố định cả cột và hàng, không thay đổi khi sao chép.
Hỗn hợp (Mixed)	`$A1` hoặc `A$1`	Cố định một trong hai (cột hoặc hàng) còn lại thay đổi.

Việc nắm vững ba loại này là nền tảng để “khóa hàm” một cách hiệu quả.

Có thể bạn quan tâm: Chuyển Hàng Thành Cột Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Cách Thực Hiện Khóa Hàm Cơ Bản

2.1. Sử Dụng Phím F4

Trong Excel, phím F4 là công cụ nhanh nhất để chuyển đổi giữa các kiểu tham chiếu. Khi con trỏ đang đặt trong công thức và bạn muốn khóa một ô:

Nhập công thức (ví dụ: =A2B2).
Di chuyển con trỏ tới phần tham chiếu cần khóa (ví dụ: A2).
Nhấn F4 → Excel sẽ chuyển từ A2 → $A$2 → A$2 → $A2 → trở lại A2.
Lặp lại cho các ô cần khóa.

Lưu ý: Trên một số máy tính, đặc biệt là laptop, bạn có thể cần nhấn Fn + F4 hoặc thay đổi cài đặt BIOS để bật chức năng F-keys.

2.2. Nhập Thủ Công

Nếu bạn không muốn dùng phím F4, bạn có thể tự nhập ký hiệu $:

Để khóa cột, đặt $ trước ký tự cột: $A2.
Để khóa hàng, đặt $ trước số hàng: A$2.
Để khóa cả cột và hàng, đặt $ trước cả hai: $A$2.

2.3. Ví Dụ Thực Tế

Ví dụ 1: Tính Thuế

Giả sử:

Ô C1 chứa tỷ lệ thuế: 0.1.
Cột A là doanh thu, cột B là chi phí.
Bạn muốn tính thuế ở cột D: =A2($C$1).

Khi sao chép công thức từ D2 xuống D3, D4,…, tham chiếu $C$1 vẫn giữ nguyên, vì nó là ô hằng số.

Ví dụ 2: Sử Dụng VLOOKUP

Bạn có bảng danh sách sản phẩm trong phạm vi F2:G10. Khi sử dụng VLOOKUP để tra cứu giá:

=VLOOKUP(A2,$F$2:$G$10,2,FALSE)

Ở đây, $F$2:$G$10 được khóa để công thức luôn tra cứu trong cùng một bảng, bất kể bạn sao chép công thức xuống bao nhiêu hàng.

3. Khóa Hàm Trong Các Hàm Nâng Cao

3.1. Hàm INDEX + MATCH

Thay vì VLOOKUP, nhiều người dùng kết hợp INDEX và MATCH để có tính linh hoạt hơn.

=INDEX($D$2:$D$100, MATCH(A2, $C$2:$C$100, 0))

$D$2:$D$100 và $C$2:$C$100 được khóa vì chúng là dải dữ liệu cố định.
A2 là giá trị tìm kiếm, sẽ thay đổi khi sao chép công thức.

3.2. Hàm SUMIF / SUMIFS

Có thể bạn quan tâm: Cách Cố Định Hình Ảnh Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Khi tổng hợp dữ liệu dựa trên nhiều tiêu chí, dải tiêu chí cần được khóa.

=SUMIFS($E$2:$E$500, $B$2:$B$500, "Kinh doanh", $C$2:$C$500, ">2026-01-01")

Ở đây, các dải $E$2:$E$500, $B$2:$B$500, $C$2:$C$500 đều được khóa để công thức luôn tham chiếu đúng dải dữ liệu.

3.3. Hàm OFFSET Kết Hợp Với COUNTA

Giả sử bạn muốn tính tổng một dải động dựa trên số lượng mục đã nhập:

=SUM(OFFSET($A$1,0,0,COUNTA($A:$A),1))

$A$1 là ô bắt đầu, cố định.
COUNTA($A:$A) tính số ô không rỗng trong cột A, cho phép dải mở rộng tự động.

4. Thực Hành: Tạo Bảng Tính Phức Tạp Với Khóa Hàm

4.1. Bước 1: Chuẩn Bị Dữ Liệu

A (Sản phẩm)	B (Đơn giá)	C (Số lượng)	D (Chiết khấu %)	E (Thuế %)	F (Thành tiền)
SP001	120,000	10	5%	10%
SP002	85,000	5	3%	10%
SP003	210,000	2	7%	10%
…	…	…	…	…	…

Ô D1 chứa “Chiết khấu %” cố định (ví dụ: 5% cho toàn bộ). Nếu có chiết khấu khác cho từng sản phẩm, bạn sẽ nhập trực tiếp trong cột D.
Ô E1 chứa “Thuế %” cố định: 0.1.

4.2. Bước 2: Công Thức Tính Thành Tiền

Công thức tính thành tiền (cột F) bao gồm:

Giá bán chưa giảm: B2 C2
Áp dụng chiết khấu: (1 - D2)
Thêm thuế: (1 + $E$1)

Công thức trong ô F2:

= B2 C2 (1 - D2) (1 + $E$1)

$E$1 được khóa vì thuế là hằng số.
Các ô B2, C2, D2 không khóa vì chúng thay đổi theo từng dòng.

Sau khi nhập công thức, kéo xuống các dòng còn lại. Excel sẽ tự động áp dụng công thức cho từng sản phẩm mà vẫn giữ $E$1 cố định.

4.3. Bước 3: Tổng Hóa

Để tính tổng thành tiền cho toàn bộ danh sách, bạn có thể sử dụng:

=SUM(F2:F100)

Nếu muốn tính tổng chiết khấu đã áp dụng, bạn có thể dùng:

=SUMPRODUCT(B2:B100, C2:C100, D2:D100)

Ở đây, không cần khóa vì dải dữ liệu đã được xác định.

5. Mẹo Và Thủ Thuật Nâng Cao

5.1. Sử Dụng Tên Dải (Named Ranges)

Thay vì nhập $A$1:$A$100 mỗi lần, bạn có thể đặt tên cho dải:

Chọn dải muốn đặt tên (VD: $A$2:$A$100).
Vào Formulas > Define Name.
Đặt tên (VD: DanhSachSanPham).

Sau đó, công thức có thể viết ngắn gọn:

=VLOOKUP(A2, DanhSachSanPham, 2, FALSE)

Tên dải luôn được coi là tuyệt đối, vì vậy không cần thêm $.

5.2. Khóa Hàm Khi Dùng Bảng Động (Dynamic Tables)

Khi làm việc với Excel Tables (Insert > Table), Excel tự động sử dụng structured references. Ví dụ:

=SUM(Table1Doanh thu)

Không cần khóa vì Excel sẽ tự quản lý dải dữ liệu khi thêm hoặc xóa hàng.

5.3. Định Dạng Có Điều Kiện (Conditional Formatting) Với Tham Chiếu Khóa

Bạn có thể áp dụng định dạng có điều kiện dựa trên một ô hằng số:

Chọn dải cần định dạng (VD: B2:B100).
Vào Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Nhập công thức: =B2>$D$1 (giả sử D1 chứa mức ngưỡng).
Đặt định dạng (màu nền, font, …).

Ở đây, $D$1 được khóa để điều kiện luôn dựa trên cùng một ô.

5.4. Sử Dụng Hàm INDIRECT Để Tham Chiếu Động

Nếu bạn muốn tham chiếu tới một ô dựa trên nội dung của một ô khác, bạn có thể dùng INDIRECT. Ví dụ:

Ô G1 chứa địa chỉ ô muốn tham chiếu, như "C5".
Công thức: =INDIRECT(G1) sẽ trả về giá trị của ô C5.

Lưu ý: INDIRECT luôn trả về tham chiếu tuyệt đối, không bị ảnh hưởng bởi sao chép công thức.

5.5. Tránh Lỗi Khi Khóa Sai

#REF!: Xảy ra khi bạn xóa ô hoặc dải đã được khóa.
#VALUE!: Khi công thức cố gắng thực hiện phép tính trên ô chứa văn bản không hợp lệ.
#NAME?: Khi bạn nhập sai ký hiệu $ hoặc tên dải không tồn tại.

Để giảm thiểu lỗi, luôn kiểm tra lại công thức sau khi khóa và sử dụng Formula Auditing (Formulas > Evaluate Formula) để xem quá trình tính toán.

6. Tổng Kết

Khóa hàm trong Excel không chỉ là một kỹ năng “đẹp mắt” mà còn là yếu tố quyết định độ chính xác và hiệu quả trong công việc xử lý dữ liệu. Từ việc:

Hiểu rõ ba loại tham chiếu (tương đối, tuyệt đối, hỗn hợp);
Sử dụng phím F4 để nhanh chóng chuyển đổi;
Áp dụng trong các hàm cơ bản như SUM, VLOOKUP;
Mở rộng sang hàm nâng cao như INDEX/MATCH, SUMIFS, OFFSET;
Kết hợp với các công cụ như Named Ranges, Tables, Conditional Formatting, INDIRECT;

bạn sẽ có thể tạo ra những bảng tính mạnh mẽ, linh hoạt và ít lỗi. Hãy thực hành thường xuyên, tạo ra các ví dụ thực tế trong công việc của bạn để nắm vững kỹ thuật này. Khi đã thành thạo, bạn sẽ nhận ra rằng việc “khóa hàm” không còn là một rào cản mà trở thành một công cụ mạnh mẽ giúp bạn tối ưu hoá thời gian và nâng cao chất lượng báo cáo.

Chúc bạn thành công trong hành trình làm chủ Excel!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.

Cách Khóa Hàm Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao