Học Excel - Bài 16: Tổng quan về các công thức trong Excel

Bài trước

Mục lục

Bài sau

Bài học Microsoft Excel này sẽ cung cấp cách tạo công thức và dùng các hàm có sẵn để tiến hành tính toán và giải quyết vấn đề.

Microsoft Excel Microsoft Excel cho Android Microsoft Excel cho iOS Microsoft Excel Online

Quan trọng: Kết quả của công thức và một số hàm Excel có thể khác nhau một chút giữa Windows PC x86 hoặc x86-64 và PC Windows RT sử dụng kiến trúc ARM.

Học Excel: Tổng quan về các công thức trong Excel

Tạo công thức chỉ giá trị trong các ô khác
Xem công thức
Nhập công thức chứa một hàm
Các thành phần cụ thể trong công thức Excel
Dùng hằng số trong công thức Excel
Dùng các tham chiếu trong công thức Excel

Tạo công thức chỉ giá trị trong các ô khác

1. Chọn ô.

2. Gõ dấu bằng =.

Lưu ý: Công thức trong Excel luôn bắt đầu bằng dấu =

3. Chọn ô hoặc gõ địa chỉ của nó trong ô lựa chọn.

4. Nhập một toán tử. Ví dụ, dấu - cho phép trừ.

5. Chọn ô tiếp theo hoặc gõ địa chỉ của nó trong ô lựa chọn.

6. Nhấn Enter. Kết quả của phép tính xuất hiện trong ô chứa công thức.

Xem công thức

1. Khi nhập công thức vào một ô, nó cũng hiện trong thanh Formula.

2. Để thấy công thức, chọn ô và nó sẽ hiện trong thanh công thức.

Nhập công thức chứa một hàm

1. Chọn một ô trống.

2. Gõ dấu bằng và nhập hàm. Ví dụ: = SUM để tính doanh thu tổng.

3. Gõ dấu ngoặc đơn mở (

4. Chọn phạm vi ô, rồi gõ dấu ngoặc đơn đóng )

5. Nhấn Enter để nhận kết quả.

Các thành phần cụ thể trong công thức Excel

Một công thức có thể chứa bất kỳ hoặc toàn bộ các thành phần sau: hàm số, tham chiếu, toán tử và hằng số.

1. Hàm số: Hàm PI() trả về giá trị pi: pi: 3.142…
2. Tham chiếu: A2 trả về giá trị trong ô A2.
3. Hằng số: Nhập trực tiếp các giá trị text vào công thức, ví dụ: 2.
4. Toán tử: Dấu mũ ^ tăng lũy thừa cho một số và toán tử hoa thị * nhân các số với nhau.

Dùng hằng số trong công thức Excel

Hằng số là giá trị không được tính toán. Nó luôn giống nhau. Ví dụ, ngày 10/9/2008, số 210 và text "Quarterly Earnings" là hằng số. Một biểu thức hay kết quả giá trị từ biểu thức không phải hằng số. Nếu bạn dùng hằng số trong công thức thay vì tham chiếu tới các ô (ví dụ: =30+70+110). Kết quả chỉ thay đổi khi bạn chỉnh sửa công thức này. Nhìn chung, tốt nhất nên đặt hằng số trong từng ô riêng, tại vị trí bạn dễ dàng thay đổi nếu cần, sau đó tham chiếu những ô đó trong công thức.

Dùng các tham chiếu trong công thức Excel

Một tham chiếu xác định một ô hoặc một phạm vi ô trên một worksheet và nói cho Excel biết vị trí tìm kiếm giá trị hoặc dữ liệu bạn muốn dùng trong công thức. Bạn có thể dùng các tham chiếu để sử dụng dữ liệu nằm ở các phần khác nhau của bảng tính trong một công thức hoặc dùng giá trị này từ một ô ở một số công thức. Bạn cũng có thể tham chiếu tới các ô trên bảng tính khác tại cùng một workbook và tới các workbook khác. Các tham chiếu tới ô trong workbook khác được gọi là liên kết hoặc tham chiếu bên ngoài.

Kiểu tham chiếu A1

Mặc định, Excel dùng kiểu tham chiếu A1 cho các cột chứa chữ cái (từ A tới XFD, tổng cộng 16.384) và tham chiếu tới các hàng chứa số từ 1 tới 1.048.576. Những chữ cái và số này được gọi là tiêu đề hàng và cột. Để tham chiếu tới một ô, nhập chữ cái trong cột trước số hàng. Ví dụ, B2 tham chiếu tới ô giao cắt giữa cột B và hàng 2.

Để tham chiếu tới	Sử dụng
Ô trong cột A và hàng 10	A10
Phạm vi các ô trong cột A và hàng từ 10 tới 20	A10:A20
Phạm vi các ô trong hàng 15 và cột từ B tới E	B15:E15
Toàn bộ ô trong hàng 5	5:5
Toàn bộ ô từ hàng 5 tới 10	5:10
Toàn bộ ô ở cột H	H:H
Toàn bộ ô trong cột từ H tới J	H:J
Tạo tham chiếu tới ô hoặc phạm vi ô trên bảng tính khác ở cùng workbook	A10:E20

Tạo một phạm vi tới ô hoặc phạm vi trên worksheet khác ở cùng một workbook

Trong ví dụ sau, hàm AVERAGE tính giá trị trung bình cho phạm vi B1:B10 trên worksheet được đặt tên Marketing trong cùng workbook.

Tham chiếu tới worksheet và Marketing.
Tham chiếu tới phạm vi ô từ B1 tới B10.
Dấu cảm thán ! tách tham chiếu worksheet khỏi tham chiếu phạm vi ô.

Lưu ý: Nếu worksheet được tham chiếu có khoảng cách hay số bên trong, bạn cần thêm dấu nháy đơn (') trước và sau tên worksheet, ví dụ: ='123'!A1 hoặc ='January Revenue'!A1.

Sự khác biệt giữa tham chiếu tuyệt đối, tương đối và hỗn hợp

a. Tham chiếu tương đối: Tham chiếu tương đối trong một công thức như A1 dựa trên vị trí tương đối của ô chứa công thức và ô tham chiếu tới. Nếu vị trí của ô đó chứa các thay đổi công thức, tham chiếu được thay đổi theo. Nếu bạn sao chép hay điền công thức theo hàng ngang hoặc cột dọc, Microsoft Excel sẽ tự động điều chỉnh tham chiếu. Mặc định, các công thức mới dùng hàm tương đối. Ví dụ, nếu bạn sao chép hoặc điền một tham chiếu tương đối trong ô B2 tới ô B3, nó tự động điều chỉnh từ =A1 tới =A2.

b. Tham chiếu tuyệt đối: Một tham chiếu ô tuyệt đối trong công thức như $A$1, luôn tham chiếu tới ô ở vị trí cụ thể. Nếu vị trí của ô đó chứa các thay đổi công thức, tham chiếu tuyệt đối vẫn giữ nguyên. Nếu bạn sao chép hoặc điền công thức theo cột ngang hoặc dọc, tham chiếu tuyệt đối không thay đổi. Mặc định, các công thức mới dùng tham chiếu tương đối, vì thế, có thể bạn cần chuyển chúng sang tham chiếu tuyệt đối. Ví dụ, nếu bạn sao chép hoặc điền một tham chiếu tuyệt đối trong ô B2 tới ô B3, công thức sẽ giữ nguyên trong cả hai ô: =$A$1.

c. Tham chiếu hỗn hợp: Một tham chiếu hỗn hợp có một cột tuyệt đối và hàng tương đối hoặc hàng tuyệt đối & cột tương đối. Một tham chiếu cột tuyệt đối sẽ đưa vào biểu mẫu A$1, B$1… Nếu vị trí của ô đó chứa các thay đổi công thức, tham chiếu tương đối cũng thay đổi, tham chiếu tuyệt đối vẫn giữ nguyên. Nếu bạn sao chép hoặc điền công thức theo cột ngang hoặc dọc, tham chiếu tương đối tự động được điều chỉnh, tham chiếu tuyệt đối không thay đổi. Ví dụ, nếu bạn sao chép hoặc điền một tham chiếu hỗn hợp từ ô A2 tới B3, nó điều chỉnh từ =A$1 tới =B$1.

Kiểu tham chiếu 3-D

Tham chiếu tiện lợi nhiều bảng tính: Nếu muốn phân tích dữ liệu trong cùng ô hoặc phạm vi ô trên nhiều worksheet ở một workbook bằng tham chiếu 3-D. Một tham chiếu 3-D bao gồm tham chiếu ô hoặc phạm vi, trước đó là phạm vi tên worksheet. Excel dùng worksheet bất kỳ được lưu giữa tên bắt đầu và kết thúc. Ví dụ, =SUM(Sheet2:Sheet13!B5) thêm toàn bộ giá trị nằm trong ô B5 trên tất cả bảng tính giữa và bao gồm Sheet 2 và Sheet 13.

Bạn có thể dùng các tham chiếu 3-D để tham chiếu tới các ô trên sheet khác, để xác định tên và tạo công thức bằng cách dùng các hàm sau: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA và VARPA.
Tham chiếu 3-D không thể được sử dụng trong công thức mảng.
Tham chiếu 3-D không thể được sử dụng cùng với toán tử giao intersect (một khoảng trắng) hoặc trong công thức dùng giao cắt ngầm.

Điều gì xảy ra khi bạn di chuyển, sao chép, chèn hoặc xóa worsheet: Ví dụ sau sẽ giải thích điều xảy ra khi bạn di chuyển, sao chép, chèn hoặc xóa worksheet trong tham chiếu 3D. Ví dụ này dùng công thức =SUM(Sheet2:Sheet6!A2:A5) để thêm các ô từ A2 tới A5 trên bảng tính từ 2 tới 6.

Chèn hoặc sao chép: Nếu chèn hoặc sao chép các bảng tính giữa Sheet2 và Sheet6 (điểm kết thúc trong ví dụ này), Excel bao gồm toàn bộ giá trị trong ô A2 tới A5 từ sheet được thêm vào trong phần tính toán.
Xóa: Nếu xóa bảng tính nằm giữa Sheet2 và Sheet6, Excel loại bỏ các giá trị của chúng trong phần tính toán.
Di chuyển: Nếu di chuyển bảng tính nằm giữa Sheet2 và Sheet6 tới một vị trí nằm ngoài phạm vi sheet được tham chiếu, Excel loại bỏ các giá trị của chúng trong phần tính toán.
Di chuyển điểm cuối: Nếu di chuyển Sheet2 hoặc Sheet6 tới vị trí khác trong cùng workbook. Excel điều chỉnh tính toán phù hợp với phạm vi bảng tính mới giữa chúng.
Xóa một điểm cuối: Nếu xóa Sheet2 hoặc Sheet6, Excel điều chỉnh tính toán phù hợp với phạm vi bảng tính giữa chúng.

Kiểu tham chiếu R1C1

Bạn cũng có thể dùng kiểu tham chiếu trên cả hàng và cột tại worksheet được đánh số. Kiểu tham chiếu R1C1 hữu ích hữu ích khi tính toán vị trí hàng và cột trong macro. Trong kiểu R1C1, Excel chỉ ra vị trí một ô bằng R theo sau là số hàng và C theo sau là số cột.

Tham chiếu	Ý nghĩa
R[-2]C	Tham chiếu tương đối tới ô nằm trên hai hàng và trong cùng một cột.
R[2]C[2]	Tham chiếu tương đối tới ô nằm dưới hai hàng và 2 cột ở bên phải.
R2C2	Tham chiếu tuyệt đối tới ô trong hàng thứ hai và trong cột thứ hai.
R[-1]	Tham chiếu tương đối tới toàn bộ hàng ở trên ô đang hoạt động.
R	Tham chiếu tuyệt đối tới hàng hiện tại.

Khi ghi một macro, Excel ghi lại một số lệnh bằng cách dùng kiểu tham chiếu R1C1. Ví dụ, nếu bạn ghi một lệnh, chẳng hạn như click nút AutoSum để chèn một công thức bổ sung một phạm vi ô, Excel ghi công thức bằng cách dùng kiểu tham chiếu R1C1, không phải kiểu A1.

Bạn có thể bật/tắt kiểu tham chiếu R1C1 bằng cách thiết lập hoặc bỏ dấu tích ô R1C1 reference style trong phần Working with formulas ở danh mục Formulas của hộp thoại Options. Để hiện hộp thoại này, click tab File.