Có thể bạn quan tâm: Cách Lập Bảng Cân Đối Kế Toán Trên Excel: Hướng Dẫn Chi Tiết Và Thực Tiễn

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

Giới thiệu

Excel là một công cụ mạnh mẽ giúp người dùng quản lý dữ liệu, thực hiện phân tích và báo cáo. Một trong những đặc tính quan trọng nhất của Excel là khả năng tự động tính toán và điền dữ liệu thông qua công thức. Việc lập công thức điền dữ liệu không chỉ giúp tiết kiệm thời gian mà còn giảm thiểu sai sót trong quá trình làm việc. Bài viết này sẽ cung cấp một hướng dẫn chi tiết, từ các công thức cơ bản đến các kỹ thuật nâng cao, đồng thời đi kèm với các ví dụ thực tế để bạn có thể áp dụng ngay trong công việc hàng ngày.

Có thể bạn quan tâm: Cách Lấy Thông Tin Từ Sheet Khác Trong Excel: Hướng Dẫn Chi Tiết Và Các Phương Pháp Tối Ưu

1. Kiến Thức Cơ Bản Về Công Thức Excel

1.1 Định Nghĩa Công Thức

Trong Excel, công thức là một chuỗi ký tự bắt đầu bằng dấu “=”. Khi nhập công thức vào ô, Excel sẽ tự động tính giá trị dựa trên các tham chiếu ô, فوتن Ati và hàm có sẵn. Công thức có thể thực hiện các phép toán số học, xử lý văn bản, điều kiện, và nhiều chức năng phức tạp hơn nữa.

1.2 Tham Chiếu Ô

Tham chiếu tương đối: Ví dụ A1. Khi sao chép công thức sang ô bên phải, tham chiếu sẽ tự động điều chỉnh (B1, C1, …).
Tham chiếu tuyệt đối: Sử dụng dấu $ để cố định cột hoặc hàng, ví dụ $A$1. Khi sao chép, tham chiếu không thay đổi.
Tham chiếu hỗn hợp: Ví dụ $A1 (cột cố định, hàng thay đổi) hoặc A$1 (hàng cố định, cột thay đổi).

1.3 Hàm Cơ Bản

Hàm	Mô tả	Ví dụ
`SUM`	Cộng dãy số	`=SUM(A1:A10)`
`AVERAGE`	Tính trung bình	`=AVERAGE(B1:B10)`
`IF`	Kiểm tra điều kiện	`=IF(C1>100,"Đạt","Thiếu")`
`VLOOKUP`	Tìm kiếm giá trị	`=VLOOKUP(D1,$E$1:$F$10,2,FALSE)`
`COUNTIF`	Đếm số ô thỏa mãn điều kiện	`=COUNTIF(G1:G10,">5")`

Các hàm này là cột mốc để xây dựng các công thức phức tạp hơn.

Có thể bạn quan tâm: Cách Lấy Giá Trị Từ Sheet Khác Trong Excel

2. Cách Lập Công Thức Đơn Giản Để Điền Dữ Liệu

2.1 Sử Dụng Công Thức Tự Động Tính

Giả sử bạn có bảng dữ liệu bán hàng như sau:

A	B	C	D
Sản phẩm	Đơn giá	Số lượng	Thành tiền
—	—	—	—
1	500,000	2
2	300,000	5
3	150,000	10

Để tính “Thành tiền” trong cột D, bạn nhập công thức vào ô D2:

=B2C2

Sau đó, kéo công thức xuống cho các ô còn lại. Nhờ tham chiếu tương đối, công thức sẽ tự động điều chỉnh cho mỗi hàng.

2.2 Sử Dụng Hàm `IF` Để Kiểm Tra Điều Kiện

Bạn muốn đánh dấu “Khiếm khuyết” nếu số lượng bán giảm dưới 5. Thêm một cột mới E với công thức:

=IF(C2<5,"Khiếm khuyết","Đạt")

Kéo công thức xuống.

2.3 Áp Dụng Hàm `ROUND` Để Định Dạng Số

Để làm tròn thành tiền đến hàng nghìn, bạn có thể sửa công thức thành:

=ROUND(B2C2, -3)

Điều này sẽ làm tròn số tới hàng nghìn gần nhất.

3. Tự Động Điền Dữ Liệu Bằng Công Thức Phức Tạp

3.1 Kết Hợp Nhiều Hàm Trong Một Công Thức

Giả sử bạn muốn tính tổng doanh thu, trừ các chi phí, và đưa ra kết quả cuối cùng. Công thức có thể như sau:

=SUM(D2:D10)-SUM(H2:H10)

Ở đây, SUM(D2:D10) tính tổng doanh thu, SUM(H2:H10) tính tổng chi phí.

3.2 Sử Dụng Hàm `INDEX` và `MATCH` Thay Thế `VLOOKUP`

ске thường được sử dụng để tìm kiếm dữ liệu trong một cột, nhưng INDEX + MATCH mang lại tính linh hoạt hơn:

=INDEX($B$1:$B$10, MATCH($奖励1,$A$1:$A$10,0))

Trong đó, $B$1:$B$10 là vùng chứa giá trị trả về, $A$1:$A$10 là vùng tìm kiếm, $奖励1 là giá trị cần tìm.

3.3 Sử Dụng Hàm `OFFSET` Để Tạo Dải Dữ Liệu Thay Đổi

Nếu bạn muốn tính tổng một dải ô có thể thay đổi tùy theo ngày, OFFSET rất hữu ích:

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

Công thức này tính tổng của cột A, bắt đầu từ A1, độ dài bằng số ô không trống trong cột.

4. Tự Động Điền Dữ Liệu Với `Array Formula`

4.1 Định Nghĩa Array Formula

Array formula cho phép thực hiện tính toán trên một dải ô đồng thời trả về kết quả trongOMG hai ô hoặc một ô duy nhất. Để nhập array formula, bạn thường nhấn Ctrl+Shift+Enter thay vì chỉ Enter.

4.2 Ví Dụ: Tính Tổng Số Lượng Giảm Như

Giả sử bạn có bảng dữ liệu bán hàng và muốn tính tổng số lượng bán ra trong những ngày bán ra vượt quáuvw giá trị cao hơn 100. Công thức array như sau:

=SUM(IF(B2:B10>100, C2:C10, 0))

Nhấn Ctrl+Shift+Enter, Excel sẽ tự động bao quanh công thức bằng dấu {}.

4.3 Sử Dụng Hàm `SUMPRODUCT` Để Thay Thế Array Formula

SUMPRODUCT là một hàm mạnh mẽ, cho phép tính toán mà không cần nhấn Ctrl+Shift+Enter Target:

=SUMPRODUCT((B2:B10>100)(C2:C10))

Công thức này cũng tính tổng số lượng bán ra khi đơn giá lớn hơn 100.

5. Tự Động Điền Dữ Liệu Với `Dynamic Named Ranges`

5.1 Tạo Dynamic Named Range

Bạn có thể tạo một tên vùng dữ liệu tự động mở rộng khi dữ liệu mới được thêm vào. Ví dụ, tạo một tên Sales với công thức:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

5.2 Sử Dụng Tên Vùng trong Công Thức

Sau khi tạo, bạn có thể dùng tên này trong công thức:

=SUM(Sales)

Khi thêm dữ liệu mới vào cột A, tính tổng sẽ tự động cập nhật.

6. Kiểm Tra Và Sửa Lỗi Công Thức

6.1 Sử Dụng Công Cụ “Evaluate Formula”

Trong Excel, bạn có thể chọn ô chứa công thức, sau đó vào Formulas > Evaluate Formula. Công cụ này sẽ hiển thị từng bước tính toán, giúp bạn xác định lỗi.

Cách Lập Công Thức Điền Dữ Liệu Trong Excel

Có thể bạn quan tâm: Cách Lập Bảng Theo Dõi Công Nợ Bằng Excel

6.2 Kiểm Tra Tham Chiếu

Đôi khi lỗi xuất hiện do tham chiếu không hợp lệ. Kiểm tra xem các ô tham chiếu cósatz không trống, có đúng kiểu dữ liệu không.

6.3 Sử Dụng “Error Checking”

Excel cung cấp tính năng “Error Checking” (Formulas > Error Checking) để phát hiện lỗi phổ biến như #DIV/0!, #VALUE!, #REF!, v.v.

7. Kỹ Thuật Tự Động Điền Dữ Liệu Nâng Cao

7.1 Sử Dụng Hàm `INDIRECT` Cho Tham Chiếu Động

Hàm INDIRECT cho phép tham chiếu đến ô dựa trên chuỗi ký tự. Ví dụ:

=SUM(INDIRECT("A"&ROW(A1)&":A"&ROW(A10)))

Công thức này tính tổng từ A1 đến A10, nhưng nếu thay đổi ROW(A10) thành một biến, bạn có thể điều chỉnh dải dữ liệu.

7.2 Kết Hợp `CHOOSE` Với `ROW` Để Tạo Dải Dữ Liệu Phânخرة

Nếu bạn muốn lặp qua nhiều cột hoặc hàng khác nhau,وادث CHOOSE có thể hữu ích:

=SUM(CHOOSE({1,2,3},A1:A10,B1:B10,C1:C10))

Công thức này tính tổng của ba cột A, B, C trong cùng một dải hàng.

7.3 Sử Dụng Hàm `FILTER` Để Lọc Dữ Liệu

Excel 365 cung cấp hàm FILTER, cho phép lọc dữ liệu theo điều kiện:

=FILTER(A2:D10, C2:C10>5)

Công thức này trả về các hàng trong bảng có số lượng bán ra > 5.

7.4 Kết Hợp `SORT` Và `UNIQUE` Để Sắp Xếp Và Loại Bỏ Trùng Lặp

Bạn có thể sắp xếp và loại bỏ trùng lặp dữ liệu:

=UNIQUE(SORT(A2:A10))

Kết quả là một danh sách duy nhất, đã được sắp xếp.

8. Tự Động Điền Dữ Liệu Trong Bảng Định Dạng (Table)

8.1 Tạo Bảng

Chọn dữ liệu, nhấn Insert > Table. Bảng sẽ tự động mở rộng khi bạn thêm dữ liệu mới.

8.2 Công Thức Trong Bảng

Khi nhập công thức vào bảng, Excel sẽ tự động áp dụng công thức đó cho toàn bộ cột. Ví dụ, nhập =@Đơn giá@Số lượng vào cột “Thành tiền” trong bảng.

8.3 Dùng `@` Để Tham Chiếu Trên Hàng

Trong bảng, @ đại diện cho ô cùng hàng hiện tại. Ví dụ:

=IF(@Số lượng < 5, "Thiếu", "Đạt")

Công thức này sẽ tính trên từng hàng riêng biệt.

9. Ứng Dụng Thực Tế: Tự Động Tạo Báo Cáo Doanh Thu

9.1 Bảng Dữ Liệu Cuốn Đầu

Giả sử bạn có bảng dữ liệu bán hàng với các cột: Ngày, Sản phẩm, Đơn giá, Số lượng, Thành tiền. Bạn muốn tự động tính doanh thu theo ngày, theo sản phẩm, và tổng doanh thu.

Ngày	Sản phẩm	Đơn giá	Số lượng
01/01/2026	A	500,000	10
01/01/2026	B	300,000	5
02/01/2026	A	500,000	8
02/01/2026	C	200,000	12

9.2 Công Thức Tính Thành Tiền

Trong cột “Thành tiền”, nhập:

=@Đơn giá@Số lượng

9.3 Tổng Doanh Thu Theo Ngày

Sử dụng SUMIFS:

=SUMIFS(Thành tiền,Ngày,$A$2)

Trong bảng PivotTable, bạn có thể dùng SUM để tính tổng doanh thu theo ngày nhanh chóng.

9.4 Tổng Doanh Thu Theo Sản Phẩm

Sử dụng SUMIFS:

=SUMIFS(Thành tiền,Sản phẩm,$B$2)

9.5 Tổng Doanh Thu Tổng Như

Dùng SUM:

=SUM(Thành tiền)

9.6 Sử Dụng PivotTable Để Tự Động Tóm Tắt

Chọn bảng dữ liệu.
Đến Insert > PivotTable.
Kéo “Ngày” vào Rows, “Sản phẩm” vào Columns, “Thành tiền” vào Values.
Bảng Pivot sẽ tự động tính tổng doanh thu theo ngày và theo sản phẩm.

10. Tự Động Điền Dữ Liệu Với Power Query

10.1 Giới Thiệu Power Query

Power Query cho phép bạn nhập dữ liệu từ nhiều nguồn, biến đổi, và tải vào Excel. Bạn có thể viết sabendo các bước tự động để xử lý dữ liệu trước khi tính toán.

10.2 Ví Dụ: Thêm Cột Tính Giá-values

Mở Power Query: Data > Get Data.
Chọn nguồn dữ liệu (ví dụ Excel workbook).
Trong Power Query Editor, chọn Add Column > Custom Column.
Nhập công thức: =Đơn giá Số lượng.
Đặt tên cột: “Thành tiền”.
Nhấn Close & Load để tải dữ liệu đã xử lý vào bảng.

10.3 Lợi Ích

Sử dụng Power Query giúp bạn:
– Tự động tải dữ liệu mới mỗi khi file được cập nhật.
– Tránh nhập công thức thủ công trong từng ô.
– Giữ dữ liệu sạch, nhất quán.

11. Lời Kết

Việc lập công thức điền dữ liệu trong Excel không chỉ là kỹ năng cơ bản mà còn là chìa khóa để nâng cao hiệu suất làm việc. Bằng cách nắm vững các công thức cơ bản, học cách kết hợp các hàm phức tạp, áp dụng các kỹ thuật nâng cao như array formula, dynamic named ranges, và Power Query, bạn sẽ có thể tự động hóa quy trình làm việc, giảm thiểu lỗi và tối ưu thời gian. Hãy thử áp dụng những phương pháp trên vào các dự án thực tế của bạn, và bạn sẽ nhanh chóng thấy sự khác biệt rõ rệt trong năng suất và độ chính xác của công việc.

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