Học Excel – Bài 16: Tổng quan về các bí quyết trong Excel Update 09/2021

Bài học Microsoft Excel này sẽ cung cấp cách để tạo bí quyết và sử 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 On-line

Cần thiết: Kết quả của bí quyết và một vài người hàm Excel có thể không giống nhau một tí giữa Home windows PC x86 hoặc x86-64 và PC Home windows RT dùng kiến trúc ARM.

Tạo bí quyết chỉ giá trị trong các ô khác

1. Chọn ô.

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

Chú ý: Bí quyết trong Excel luôn bắt tay vào làm bằng dấu =

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

Chọn ô nhập dữ liệu trong Excel

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

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

Nhập toán tử vào ô Excel

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

Xem bí quyết

1. Khi nhập bí quyết vào một ô, nó cũng hiện trong thanh Components.

Thanh công thức trên Excel

2. Để thấy bí quyết, chọn ô và nó sẽ hiện trong thanh bí quyết.

Nhập công thức vào Excel

Nhập bí quyết chứa một hàm

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

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

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

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

Chọn phạm vi ô trong Excel

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

Các thành phần nhất định trong bí quyết Excel

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

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

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ị textual content vào bí quyết, VD: 2.
4. Toán tử: Dấu mũ ^ tăng lũy thừa cho một vài người và toán tử hoa thị * nhân các số với nhau.

Sử dụng hằng số trong bí quyết Excel

Hằng số là giá trị không được tính toán. Nó luôn giống nhau. VD, ngày 10/9/2008, số 210 và textual content “Quarterly Income” 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 như bạn sử dụng hằng số trong bí quyết thay vì tham chiếu tới các ô (VD: =30+70+110). Kết quả chỉ thay đổi khi mà bạn chỉnh sửa bí quyết 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 đơn giản thay đổi nếu như cần, sau đấy tham chiếu những ô đấy trong bí quyết.

Sử dụng các tham chiếu trong bí quyết Excel

Một tham chiếu nắm rõ 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 mong muốn sử dụng trong bí quyết. Bạn sẽ sử dụng các tham chiếu để dùng dữ liệu nằm ở các phần còn lại nhau của bảng tính trong một bí quyết hoặc sử dụng giá trị này từ một ô ở một vài người bí quyết. 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òn được nhắc đên là liên kết hoặc tham chiếu bên ngoài.

Kiểu tham chiếu A1

Mặc định, Excel sử 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òn được nhắc đên 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. VD, B2 tham chiếu tới ô giao cắt giữa cột B và hàng 2.

Để tham chiếu tới 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
Tất cả ô trong hàng 5 5:5
Tất cả ô từ hàng 5 tới 10 5:10
Tất cả ô ở cột H H:H
Tất cả ô 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 VD sau, hàm AVERAGE tính giá trị trung bình cho phạm vi B1:B10 trên worksheet được đặt tên Advertising and marketing trong cùng workbook.

Hàm trung bình trong Excel
  1. Tham chiếu tới worksheet và Advertising and marketing.
  2. Tham chiếu tới phạm vi ô từ B1 tới B10.
  3. Dấu cảm thán ! tách tham chiếu worksheet khỏi tham chiếu phạm vi ô.

Chú ý: Nếu như worksheet được tham chiếu có khoảng cách hay số bên trong, bạn phải cần thêm dấu nháy đơn () trước và sau tên worksheet, VD: =’123′!A1 hoặc =’January Earnings’!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 bí quyết như A1 dựa trên vị trí tương đối của ô chứa bí quyết và ô tham chiếu tới. Nếu như vị trí của ô đấy chứa các thay đổi bí quyết, tham chiếu được thay đổi theo. Nếu như bạn sao chép hay điền bí quyết theo hàng ngang hoặc cột dọc, Microsoft Excel sẽ tự động xoay chỉnh tham chiếu. Mặc định, các bí quyết mới sử dụng hàm tương đối. VD, nếu như 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 xoay chỉnh từ =A1 tới =A2.

Sao chép công thức trong Excel

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

Tham chiếu tuyệt đối trong Excel

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 mẫu biểu A$1, B$1… Nếu như vị trí của ô đấy chứa các thay đổi bí quyết, tham chiếu tương đối cũng thay đổi, tham chiếu tuyệt đối vẫn giữ nguyên. Nếu như bạn sao chép hoặc điền bí quyết theo cột ngang hoặc dọc, tham chiếu tương đối tự động được xoay chỉnh, tham chiếu tuyệt đối không thay đổi. VD, nếu như bạn sao chép hoặc điền một tham chiếu hỗn hợp từ ô A2 tới B3, nó xoay chỉnh từ =A$1 tới =B$1.

Tham chiếu hỗn hợp trong Excel

Kiểu tham chiếu three-D

Tham chiếu tiện lợi nhiều bảng tính: Nếu như mong 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 three-D. Một tham chiếu three-D gồm có tham chiếu ô hoặc phạm vi, trước đây là phạm vi tên worksheet. Excel sử dụng worksheet bất kỳ được lưu giữa tên bắt tay vào làm và kết thúc. VD, =SUM(Sheet2:Sheet13!B5) thêm tất cả giá trị nằm trong ô B5 trên toàn bộ bảng tính giữa và gồm có Sheet 2 và Sheet 13.

  • Bạn sẽ sử dụng các tham chiếu three-D để tham chiếu tới các ô trên sheet khác, để nắm rõ tên và tạo bí quyết bằng việc 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 three-D không thể được dùng trong bí quyết mảng.
  • Tham chiếu three-D không thể được dùng cùng với toán tử giao intersect (một khoảng trắng) hoặc trong bí quyết sử dụng giao cắt ngầm.

Điều gì sẽ xảy đến khi mà bạn di chuyển, sao chép, chèn hoặc xóa worsheet: VD sau sẽ trình bày điều diễn ra khi mà bạn di chuyển, sao chép, chèn hoặc xóa worksheet trong tham chiếu three-D. VD này sử dụng bí quyết =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 như 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 VD này), Excel gồm có tất cả giá trị trong ô A2 tới A5 từ sheet được chèn vào trong phần tính toán.
  • Xóa: Nếu như 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 như 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 như di chuyển Sheet2 hoặc Sheet6 tới vị trí khác trong cùng workbook. Excel xoay chỉnh tính toán thích 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 như xóa Sheet2 hoặc Sheet6, Excel xoay chỉnh tính toán thích 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 trên cả hàng và cột tại worksheet được đánh số. Kiểu tham chiếu R1C1 có ích có í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ứ 2 và trong cột thứ 2.
R[-1] Tham chiếu tương đối tới tất cả 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 vài người lệnh bằng việc sử dụng kiểu tham chiếu R1C1. VD, nếu như bạn ghi một lệnh, giống như click on nút AutoSum để chèn một bí quyết bổ sung một phạm vi ô, Excel ghi bí quyết bằng việc sử dụng kiểu tham chiếu R1C1, không phải kiểu A1.

Bạn sẽ bật/tắt kiểu tham chiếu R1C1 bằng việc cài đặt hoặc bỏ dấu tích ô R1C1 reference taste trong phần Running with formulation ở danh mục Formulation của hộp thoại Choices. Để hiện hộp thoại này, click on tab Tệp.

Tổng quan về công thức trong Microsoft Excel

Trên đây chính là tổng quan về bí quyết, kiểu tham chiếu căn bản trong Excel. Hello vọng bài học Microsoft Excel này có ích với các bạn.