1. Một số khái niệm
1.1. Địa chỉ ô
Địa chỉ tương đối
Bạn gọi A1 là địa chỉ tượng đối của ô A1, A1:B3 là địa chỉ tương đối của vùng dữ liệu từ ô A1 đến ô A3. Được sử dụng khi chúng bạn tham chiếu tới một ô hay một vùng dữ liệu nào đó. Khi bạn kéo Fillhand cho các ô khác địa chỉ sẽ thay đổi chỉ số hàng cột theo.
Địa chỉ tuyệt đối
Có dạng: $Cột$Dòng
Là loại địa chỉ khi đứng trong công thức tính toán, sao chép công thức đi nơi khác, nó không thay đổi địa chỉ.
Ví dụ: Bạn có bảng tính sau nhưng nhập công thức cho ô D1 là: =$BS1, thì khi Copy công thức đến bất kỳ ô nào trong bảng tính, địa chỉ này không thay đổi và cho kết quả luôn giống nhau:
Địa chỉ hỗn hợp
Địa chỉ hỗn hợp có hai loại.
Loại 1: Địa chỉ tuyệt đối cột, tương đối dòng.
Có dạng: $Cột Dòng
Là loại địa chỉ mà khi bạn đứng trong công thức tính toán sao chép công thức đi nơi khác nó không bị thay đổi địa chỉ cột mà chỉ thay đổi địa chỉ dòng.
Loại 2: Địa chỉ tương đối cột, tuyệt đối dòng
Có dạng: Cột$Dòng
Là loại địa chỉ mà khi bạn đứng trong công thức tính toán, sao chép công thức đi nơi khác nó không bị thay đổi địa chỉ dòng mà chỉ thay đổi địa chỉ cột.
1.2. Địa chỉ vùng dữ liệu
– Đánh dấu ô hay khối ô cần đặt tên.
– Nhấp chuột vào khung Name Box trên thanh công thức.
– Nhập tên cho ô hay khối ô.
– Đặt tên xong, nhấn Enter để chấp nhận.
2. Các phép toán
2.1. Dữ liệu chuỗi
Dữ liệu chuỗi cho phép bạn thực hiện được trên các phép toán quan hệ và phép toán nối chuỗi.
Toán tử quan hệ
=: Bằng
<: Nhỏ hơn
<=: Nhỏ hơn hoặc bằng
> Lớn hơn
>= Lớn hơn hoặc bằng
<> Khác nhau
Thực hiện phép so sánh, kết quả sẽ là: Nếu đúng cho ra giá trị TRUE, ngược lại sai cho ra giá trị FALSE.
2.2. Dữ liệu số học
Dữ liệu số cho phép bạn thực hiện được trên các phép toán: Toán học, luận lý, và quan hệ.
Toán tử số học có các ký hiệu
+: Phép cộng
-: Phép trừ
*: Phép nhân
/: Phép chia
%: Phần trăm
^: Số mũ
Toán tử NOT
Số 0 mang giá trị True và các số khác 0 mang giá trị False. Tại ô A5 bạn nhập 0, ô A6 bạn nhập 1. Tại ô B5 bạn gõ công thức: NOT(A5) rồi nhấn Enter bạn kéo Fillhand cho ô còn lại.
Toán tử AND
Bạn nhập lại giá trị vào ô B5 là 1 ô B6 là 1, di chuyển tới ô C5 và gõ vào công thức: =IF(AND(A5=1,B5=1) ,TRUE, FALSE), sau đó bạn nhấp Enter, rồi kéo Fillhand cho ô còn lại:
Toán tử OR
Với bảng dữ liệu như hai ví dụ trên, bạn di chuyển chuột tới ô C5 và nhập lại công thức sau: =OR(A5=1,B5=1,TRUE, FALSE), nhập xong nhấn Enter.
Toán tử quan hệ có các ký hiệu:
=: Bằng
<: Nhỏ hơn
<=: Nhỏ hơn hoặc bằng
>: Lớn hơn
>=: Lớn hơn hoặc bằng
<>: Khác nhau
Thực hiện phép so sánh, kết quả sẽ là: Nếu đúng cho ra giá trị là TRUE, ngược lại nếu sai cho giá trị là FALSE.
Độ ưu tiên của các toán tử
Trong công thức toán tử nào có độ ưu tiên cao, được thực hiện trước. Các toán tử có độ ưu tiên bằng nhau, Excel tự động thực hiện từ trái qua phải.
| Toán tử | Độ ưu tiên |
| () | 1 (cao nhất) |
| ^ | 2 |
| * và / | 3 |
| + và – | 4 |
| =, <, <=, >, >=, <> và NOT, AND, OR | 5 (thấp nhất) |
2.3. Dữ liệu ngày tháng
Bạn có thể sử dụng các phép toán số học (cộng và trừ) và các phép toán tử quan hệ.
Ví dụ: Bảng tính trên cột A và B là những dữ liệu được nhập từ bàn phím. Trước khi thực hiện công thức hãy chọn kiểu hiển thị cho ô đó. Ô A1 và B1 chọn kiểu hiển thị Ngày/ Tháng/Năm (trong hộp thoại là: 14/3/2001, Vietnamese, Date).
Ô A2, B2 chọn kiểu hiển thị Ngày/Tháng/Năm (trong hộp thoại là: 14/march/2001, Vietnamese, Date).
Ô A3, B3 chọn kiểu hiển thị Ngày/Tháng/Năm (trong hộp thoại là: 14/Mar/01, Vietnamese, Date).
Ô C1,C2, C3 chọn kiểu hiển thị số (trong hộp thoại là: – 1234, Number).
Ô D1,D2,D3 chọn kiểu hiển thị Ngày/Tháng/Năm.
Công thức ô C1 =B1- A1
Công thức ô C2 =B2- A2
Công thức ô C3 =B3- A3
Công thức ô D1 =A1+ 5
Công thức ô D2 =B2+ 5
Công thức ô D1 =A3+ 5
Công thức ô E1 =A1<B1
Công thức ô E2 =A2>B2
Công thức ô E3 =A3=B3.
Muốn cho ô hay khối ô hiển thị số bình thường hãy nhấn Ctrl+ Shift+ ~.
3. Công thức, hàm
3.1. Công thức trong Excel
Cú pháp: = (Công thức).
Phân loai:
– Công thức chỉ gồm các số: Loại công thức này gồm các toán tử và toán hạng
– Công thức chỉ có các địa chỉ ô: Loại công thức này gồm các toán tử và các địa chỉ ô hoặc địa chỉ các vùng.
– Công thức vừa có địa chỉ ô vừa có số: Loại công thức này gồm các toán tử, các địa chỉ ô hoặc địa chỉ các vùng và các con số.
– Công thức chỉ có các hàm: Loại công thức này sử dụng các hàm trong Excel.
– Công thức tổng quát: Thành phần của các loại công thức này bao gồm các hàm, các phép toán, số, ký tự, ô địa chỉ.
Cách nhập công thức: Để có thể nhập công thức cho một Cell nào đó bạn nhấp đúp chuột hoặc di chuyển chuột đến Cell đó nhấn phím F2. Khi dấu nhắc chuột xuất hiện hãy nhập công thức cho Cell bắt đầu với dấu (=).
Ví dụ tại ô C2 chúng ta có công thức là: A2+B2
Để chỉnh sử công thức bạn thao tác tương tự như cách trên hoặc có thể nhấp chọn ô chứa công thức cần sửa sau đó tiến hành sửa công thức trên thanh Formula.
Lưu ý: Một cách đơn giản hơn để nhập công thức cho Cell là bạn chọn tới Cell đó rồi gõ trực tiếp công thức. Tuy nhiên cách này không dùng để chỉnh sửa công thức.
3.2. Hàm trong Excel
Khái niệm
Hàm là một công thức được định nghĩa sẵn trong Excel nhằm thực hiện một chức năng tính toán riêng biệt nào đó. Trong quá trình tính toán và xử lý đôi khi các hàm do Excel cung cấp không đáp ứng được vì vậy bạn có thể viết ra những hàm mới thích hợp cho riêng mình.
Cấu trúc
Cú pháp: = Tên hàm (đối số 1, đối số 2, …, đối số n)
Giải thích:
– Dấu (=): Bắt buộc phải có trước hàm, nếu không có dấu bằng Excel sẽ hiểu đó là một chuỗi bình thường, nó không tính toán gì cả.
– Tên hàm: Muốn sử dụng một hàm nào đó bạn phải ghi tên hàm đó ra, tên hàm không có khoảng trắng để Excel nhận diện và thực hiện đúng hàm cần dùng.
– Dấu ngoặc đơn bật “(“: Sau tên hàm là dấu ngoặc đơn mở, nó phải được đứng ngay sau tên hàm không được có khoảng trắng.
– Các đối số (đối số 1, đối số 2, …, đối số n): Là giá trị hay ô hoặc khối ô được hàm sử dụng, ngoài ra chúng bạn có thể sử dụng các hàm làm đối số cho hàm khác hay nói khác đi là hàm lồng nhau, trong Excel cho phép tối đa 7 mức hàm lồng nhau.
– Dấu ngoặc đơn đóng “)”: Dùng để kết thúc một hàm.
Sử dụng hàm
Để sử dụng các hàm trong Excel, bạn có thể nhập trực tiếp tên hàm và các đối số từ bàn phím.
Hoặc dùng hộp thoại Insert Function. Để gọi hộp thoại Insert Function bạn làm như sau:
– Từ thanh công cụ Ribbon nhấp chọn Tab Formula, tìm tới nhóm Function Library nhấp chọn lệnh Insert Funtion
Hoặc từ thanh công cụ Formula Bar nhấp chọn biểu tượng Insert Function.
Hộp thoại Insert Function xuất hiện như hình dưới đây:
– Nhấp vào mũi tên hình tam giác của chức năng Or select a function để xuất hiện danh sách các nhóm hàm.
– Trong khung Select a function thể hiện các hàm của nhóm hàm được chọn trong khung Or Select a function. Phía dưới khung Select a function cho bạn biết những thông tin về hàm được đánh dấu trong khung Select a function.
– Bạn cũng có thể nhấp vào Help on this Function để biết thêm cách sử dụng của hàm.
– Chọn một hàm thích hợp cần sử dụng, nhấp OK hộp thoại Function Arguments hiện lên.
– Trong hộp thoại, nhập các thông số cần tính toán cho hàm.
– Nhập dữ liệu xong, nhấp OK hay nhấn phím Enter để thực hiện tính toán.
Hiển thị tất cả các công thức trong bảng tính
Để hiển thị tất cả công thức có trong bảng tính bạn làm như sau:
– Từ thanh công cụ Ribbon nhấp chọn Tab formula, trong Tab này tìm tới nhóm Formula auditing.
– Danh sách lệnh trong nhóm xuất hiện bạn nhấp chọn lệnh Show Formulas hoặc nhấn tổ hợp phím Ctrl + ~.
Hiển thị đường dẫn tới các thành phần công thức
– Chọn ô chứa công thức cần xem
– Từ thanh công cụ Ribbon nhấp chọn Tab Formula, tìm tới Tab Formula auditing.
– Nhấp chọn lệnh Trace Precedents.
Ví dụ dưới đây ô C2 có công thức là : =SUM(A2:B2)+C6
Để xóa các đường dẫn này bạn nhấp chọn lệnh Remove all arrows
Hiển thị đường dẫn của các thành phần trong công thức tới kết quả.
– Chọn ô thành phần trong công thức cần xem.
– Từ thanh công cụ Ribbon nhấp chọn Tab Formula, tìm tới Tab Formula auditing.
– Nhấp chọn lệnh Trace Dependents.
Cũng với ví dụ trước chúng ta xem ô C6 là thành phần của kết quả nào.
Để xóa các đường dẫn này bạn nhấp chọn lệnh Remove all arrows
4. Phân loại hàm
– Nhóm hàm Financial (tài chính): Nhóm hàm này cung cấp cho bạn các hàm nhằm tính toán về mặt tài chính như: tính tiền đầu tư, tính tiền lợi nhuận.
– Nhóm hàm Date & Time (ngày tháng và thời gian): Nhóm hàm này cung cấp cho bạn các hàm nhằm tính toán về thời gian giữa hai mốc ngày, đổi một ngày sang dạng số…
– Nhóm hàm Math & Trig (toán học và lượng giác): Nhóm hàm này cung cấp cho bạn các hàm nhằm tính toán về toán học và kỹ thuật căn bản.
– Nhóm hàm Statistical (thống kê): Nhóm hàm này cung cấp cho bạn các hàm nhằm tính toán về thống kê như tính toán xác suất…
– Nhóm hàm Lookup & Reference (tìm kiếm và tham chiếu): Nhóm hàm này cung cấp cho bạn những thông tin về bảng tính như: trả về số cột của một tham chiếu…
– Nhóm hàm DaTabase (cơ sở dữ liệu): Nhóm hàm này cung cấp cho bạn các hàm nhăm trợ giúp những thông tin về cơ sở dữ liệu chẳng hạn: Tính giá trị nhỏ nhất và lớn nhất trong cơ sở dữ liệu…
– Nhóm hàm Text (văn bản): Nhóm hàm này cung cấp cho bạn các hàm nhằm quản lý văn bản.
– Nhóm hàm Logical (luận lý): Nhóm hàm này cung cấp cho bạn các hàm nhằm tính toán các giá trị bằng các biểu thức luận lý dựa trên những điều kiện của bảng tính.
– Nhóm hàm Information (thông tin): Nhóm hàm này cung cấp cho bạn các hàm nhằm tính toán và trả về các thông tin chung trong bảng tính.