Buzz
This Microsoft Excel lesson will guide you through creating formulas and using built-in functions to perform calculations and solve problems.
Creating formulas to evaluate values in different cells
1. Select the cell.
2. Type the equals sign =.
Chú ý: Trong Excel, các công thức luôn bắt đầu bằng ký hiệu =
3. Chọn ô hoặc nhập địa chỉ của nó vào ô lựa chọn.
4. Nhập một toán tử. Ví dụ, dấu - để thực hiện phép trừ.
5. Chọn ô kế tiếp hoặc nhập địa chỉ của nó vào ô lựa chọn.
6. Bấm Enter. Kết quả của phép tính sẽ hiển thị 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 sẽ xuất hiện trong thanh Formula.
2. Để xem công thức, chọn ô và nó sẽ xuất 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õ ký hiệu bằng và nhập hàm. Ví dụ: = SUM để tính tổng doanh thu.
3. Gõ ký hiệu dấu ngoặc đơn mở (
4. Chọn phạm vi ô, sau đó gõ ký hiệu 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ể bao gồm mọi 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ị văn bản 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.
Sử dụng hằng số trong công thức Excel
Hằng số là giá trị không được tính toán. Chúng luôn giống nhau. Ví dụ, ngày 10/9/2008, số 210 và văn bản 'Lợi nhuận quý' là hằng số. Một biểu thức hoặc giá trị từ biểu thức không phải là hằng số. Nếu bạn sử 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. Nói chung, tốt nhất là đặt hằng số trong các ô riêng biệt, ở vị trí bạn có thể dễ dàng chỉnh sửa nếu cần thiết, sau đó tham chiếu các ô đó trong công thức.
Sử dụng 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 bảng tính và thông báo cho Excel biết nơi tìm kiếm giá trị hoặc dữ liệu bạn muốn sử dụng trong công thức. Bạn có thể sử dụng các tham chiếu để truy cập dữ liệu từ các phần khác nhau của bảng tính trong một công thức hoặc sử dụng giá trị từ một ô trong nhiều công thức. Bạn cũng có thể tham chiếu đến các ô trên các bảng tính khác trong cùng một workbook và trên các workbook khác. Các tham chiếu đến ô trong các workbook khác được gọi là liên kết hoặc tham chiếu ngoại vi.
Kiểu tham chiếu A1
Mặc định, Excel sử dụng kiểu tham chiếu A1 cho các cột được đặt tên bằng chữ cái (từ A đến XFD, tổng cộng 16.384) và tham chiếu đến các hàng được đánh số từ 1 đến 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 đến một ô, nhập chữ cái cột trước số hàng. Ví dụ, B2 tham chiếu đến ô nằm ở giao điểm củ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 đến ô hoặc phạm vi trên bảng tính khác trong 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 bảng tính được đặt tên Marketing trong cùng một workbook.
- Tham chiếu đến bảng tính và Marketing.
- Tham chiếu đến phạm vi ô từ B1 đến B10.
- Dấu cảm thánh
!phân tách tham chiếu bảng tính và tham chiếu phạm vi ô.
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 thay đổi công thức, tham chiếu cũng thay đổi theo. Nếu bạn sao chép hoặc đ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 sử dụng tham chiếu 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 thành =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 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 sử 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 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 nhiều bảng tính tiện lợi: Nếu bạn muốn phân tích dữ liệu trong cùng một ô hoặc phạm vi ô trên nhiều bảng tính trong cùng một workbook, bạn có thể sử dụ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 bảng tính. Excel sử dụng bất kỳ bảng tính nào nằm giữa tên bắt đầu và kết thúc. Ví dụ, =SUM(Sheet2:Sheet13!B5) sẽ tính tổng tất cả các giá trị trong ô B5 trên tất cả các bảng tính từ Sheet 2 đến Sheet 13.
- Bạn có thể sử dụng tham chiếu 3-D để tham chiếu đến các ô trên các bảng tính khác nhau, để xác định tên và tạo công thức bằng cách sử 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 sử dụng giao cắt ngầm.
Hiệu ứng khi di chuyển, sao chép, chèn hoặc xóa bảng tính: Ví dụ sau sẽ giải thích hiệu ứng khi bạn di chuyển, sao chép, chèn hoặc xóa bảng tính trong tham chiếu 3-D. Ví dụ này sử dụng công thức =SUM(Sheet2:Sheet6!A2:A5) để tính tổng các ô từ A2 đến A5 trên bảng tính từ 2 đến 6.
- Chèn hoặc sao chép: Nếu bạn 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 sẽ bao gồm toàn bộ giá trị trong ô A2 đến A5 từ bảng tính được thêm vào trong phép tính.
- Xóa: Nếu bạn xóa bảng tính nằm giữa Sheet2 và Sheet6, Excel sẽ loại bỏ các giá trị của chúng trong phép tính.
- Di chuyển: Nếu bạn 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 của bảng tính được tham chiếu, Excel sẽ loại bỏ các giá trị của chúng trong phép tính.
- Di chuyển điểm cuối: Nếu bạn di chuyển Sheet2 hoặc Sheet6 tới một vị trí khác trong cùng một workbook, Excel sẽ điều chỉnh tính toán phù hợp với phạm vi mới của bảng tính giữa chúng.
- Xóa một điểm cuối: Nếu bạn xóa Sheet2 hoặc Sheet6, Excel sẽ đ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ể sử dụng kiểu tham chiếu theo số hàng và số cột trên bảng tính được đánh số. Kiểu tham chiếu R1C1 hữu ích khi tính toán vị trí hàng và cột trong macro. Trong kiểu R1C1, Excel chỉ định vị trí của một ô bằng R (hàng) theo sau là số hàng và C (cột) 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 sử dụng kiểu tham chiếu R1C1. Ví dụ, nếu bạn ghi một lệnh như click vào nút AutoSum để chèn một công thức tổng vào một phạm vi ô, Excel sẽ ghi công thức bằng cách sử dụng kiểu tham chiếu R1C1 thay vì kiểu A1.
Bạn có thể bật hoặc tắt kiểu tham chiếu R1C1 bằng cách chọn hoặc không chọn ô R1C1 reference style trong phần Working with formulas trong danh mục Formulas của hộp thoại Options. Để mở hộp thoại này, hãy nhấp vào tab File.
Trên đây là phần tổng quan về các công thức và kiểu tham chiếu cơ bản trong Excel. Mong rằng bài học về Microsoft Excel này sẽ giúp ích cho các bạn.
