Hàm Cơ Bản và Toán Tử Trong Excel: Hướng Dẫn Chi Tiết | Xác suất thống kê | Đại học Nông Lâm Thành phố Hồ Chí Minh

1. Các toán tử: nhập từ bàn phím
- Toán tử số học: cộng (+), trừ (-), nhân (*), chia (/), lũy thừa (^), toán tử “âm” (-), phần trăm (%).
- Toán tử nối chuỗi: ampersand (&)
- Toán tử quan hệ: bằng (=), không bằng/khác (<>), lớn hơn (>), lớn hơn hoặc bằng (>=), nhỏ
hơn (<), nhỏ hơn hoặc bằng (<=).
- Toán tử tham chiếu: tham chiếu vùng (:), tham chiếu danh sách (;) - Thứ tự ưu tiên: + Từ trái qua phải
+ Các toán tử quan hệ ; & ; + và - ; * và / ; ^ ; % ; - (âm) ; () (trong trước, ngoài sau)
- Giá trị của TRUE, FALSE và toán tử &:
+ Trong biểu thức số: TRUE = 1 ; FALSE = 0
+ Trong biểu thức chuỗi: TRUE = “TRUE” và FALSE = “FALSE”
- Các toán hạng của toán tử “&” có thể 1 kiểu bất kỳ nhưng kết quả luôn có kiểu chuỗi
2. Một số thông báo lỗi của biểu thức
- #VALUE! : thực hiện các phép tính số học cho dữ liệu kiểu chuỗi
(Công thức không hoàn chỉnh)
- #NUM! : sử dụng các con số trong công thức không hợp lý.
- #REF! : tham chiếu đến ô không có thực.
- #NAME? : Sai tên hàm, tên ô hoặc tên vùng
- #DIV/0! : Chia một số cho không.
- #N/A (Value not available) : Giá trị không tương thích.
(Tham chiếu đến vùng rỗng hoặc vùng không tương thích) 3. Copy và Paste: - Paste: paste bình thường
- Value: chỉ paste giá trị
- Formulas: chỉ copy công thức
- Transpose: chuyển dòng thành cột
- Formating: chỉ paste format của ô/vùng
- Link: tham chiếu đến ô/vùng gốc Number:
- Mặc định là General: excel tự xử lý về kiểu theo quy ước (số, chuỗi, ngày tháng, giờ, true,
false…) đối với kiểu số không nhập dấu phân cách hàng ngàn và không giới hạn số lẻ.
- Number: khai báo kiểu số, bao nhiêu số lẻ thập phân và có dấu phân cách hàng ngàn k? - Percentage: %
- Accounting and Currency: dạng tiền tệ
- Text: chuyển số thành chuỗi -> có thể thực hiện được phép tính số học
- Time: khai báo kiểu số về dạng giờ phút giây
- Date: khai báo kiểu số về dạng ngày tháng năm (vị trí ngày tháng năm phải nhập theo quy
định đã thiết lập trong Control Panel) Alignment: - Horizontal: ngang - Vertical: đứng - Orientation:
- Wrap text: tự xuống hàng nếu ko đủ chỗ (hoặc nhấn Alt-Enter để xuống hàng)
- Shrink to fit: tự điều chỉnh kích thước cho vừa với độ rộng của cột
- Merge cell: kết hợp nhiều ô lại (hoặc chọn biểu tượng trong thẻ Home)
Để lấy lại định dạng mặc định vào Home > Clear > Clear Formats
4. Nhóm hàm chuỗi ký tự: toán tử & dùng để nối chuỗi
- Độ dài chuỗi: LEN(chuỗi)
- Cắt chuỗi: lấy N ký tự bắt đầu từ bên trái, phải, từ ký tự thứ M - LEFT(chuỗi, N) - RIGHT(chuỗi, N) - MID(chuỗi, M, N)
- Chuyển chuỗi sang chữ hoa, thường, tên riêng: UPPER, LOWER, PROPER
- Nối chuỗi: CONCATENATE(chuỗi 1, chuỗi 2…)
- So sánh 2 chuỗi: EXACT(chuỗi 1, chuỗi 2)
- Tìm chuỗi: FIND(chuỗi con, chuỗi lớn, vị trí bắt đầu)
Có thể không hoàn toàn chính xác đối với Tiếng Việt
- CONCATINATE(st1; st2; …): nối các chuỗi
- TRIM(st): bỏ các khoảng trắng dư
- VALUE(st): chuyển chuỗi có định dạng kiểu số thành 1 giá trị kiểu số 5. Nhóm hàm toán học:
- ABS(n): lấy giá trị tuyệt đối
- SUM(n1; n2;…): tính tổng
- INT(n): lấy giá trị nguyên - SQRT(n): lấy căn bậc 2
- PRODUCT(n1; n2;…): phép nhân
- MOD(n1; n2): lấy số dư của n1/n2 - ROUND(n; n1): làm tròn
+ n1 > 0: làm tròn đến n1 số lẻ thập phân
+ n1 = 0: làm tròn hàng đơn vị
+ n1 < 0: làm tròn hàng chục, hàng trăm, hàng ngàn…
- ROUNDUP(x;0) làm tròn số x đến số nguyên gần nhất và lớn hơn
- SUMPRODUCT(v1; v2;….): tổng các tích từng phần tử của vùng v1 với v2….
- SUMIF(v1; gt; v2): trong vùng , tìm các phần tử có v1
giá trị là gt thì cộng các phần tử tương ứng trong vùng v2 SUMIF (A2:B6; “.100”) 6. Nhóm hàm luận lý: - NOT(đk1) - AND(đk1; đk2;…) - OR(đk1; đk2;…)
- IF(đk; gt-đ; gt-s): giá trị của hàm IF là 1 kiểu dữ liệu bất kỳ, nếu có nhiều trường hợp thì sử dụng hàm IF lồng nhau 7. Nhóm hàm thống kê:
- EVERAGE(n1; n2;…): tính trung bình cộng
- MIN(n1; n2;…): tìm giá trị nhỏ nhất
- MAX(n1; n2): tím gái trị lớn nhất
- RANK(n; v; k): n đứng thứ mấy trong vùng v, k=0 or bỏ trống -> giảm dần, k=1 -> tăng dần.
(n phải là 1giá trị của v, không thì -> lỗi #N/A)
- COUNT(gt1; gt2;…): đếm giá trị kiểu số
- COUNTA(gt1; gt2;…): đếm giá trị kiểu bất kỳ
- COUNTIF(v; gt): đếm trong vùng v có bao nhiêu gt, gt có thể bao gồm ký tư thay thế * ? 8. Nhóm hàm thời gian:
- DAY(ng): lấy giá trị ngày của ng, ng là ô tham chiếu hoặc stt tính từ 1/1/1900 hoặc “ngày-
tháng-năm” (theo định dạng trong Control Panel)
- MONTH(ng): lấy giá trị tháng của ng
- YEAR(ng): lấy giá trị năm của ng
- DAYS360(ng1; ng2; k): ng2 – ng1, k thường bỏ qua
- TODAY(): giá trị ngày tháng hiện tại
- NOW(): ngày giờ hiện tại
- WEEKDAY(ng; k): ngày trong tuần, k thường được bỏ qua, CN=1….
- DATE(yyyy; mm; dd): trả về định dạng ngày-tháng-năm theo hệ thống -> sử dụng trong biểu
thức luận lý khi muốn so sánh ngày-tháng-năm
- DATEVALUE(“01/01/1990”) = 1
- Tạo thời gian một cách ngẫu nhiên: cần định dạng dữ liệu về date hoặc time o RAND(): o
RANDBETWEEN(DATEVALUE("24/05/2021"),DATEVALUE("24/05/2022")) - Phím tắt o
Ngày tháng hiện tại: Ctrl ; o
Thời gian hiện tại: Ctrl Shift ; 9. Nhóm hàm dò tìm:
- VLOOKUP(trị-tìm; vgt; stt_cgt; k): tìm kiếm 1 giá trị trong cột bên trái ngoài cùng của vùng
dò tìm, nếu có thì lấy ra giá trị ở cột tương ứng mà ta chỉ định trong cùng hàng được tìm thấy,
stt cột bắt đầu là 1, stt = 0 thì lỗi #VALUE, stt > số lượng cột trong vùng tìm thì lỗi #REF.
Vgt thường cố định (có địa chỉ tuyệt đối-nhấn F4) -> nên đặt tên cho vùng để tiện tham chiếu
k=0: tìm chính xác -> phổ biến.
k=1 hoặc bỏ qua -> tìm tương đối, vùng dò tìm phải được sắp xếp theo thứ tự tăng dần, nếu
trị-tìm < giá trị nhỏ nhất của cột bên trái đầu tiên trong vùng dò tìm thì lỗi #N/A, nếu trị-tìm >
giá trị lớn nhất thì lấy kết quả của cột tương ứng với giá trị lớn nhất.
- HLOOKUP(trị-tìm; vgt; stt_cgt; k): tìm kiếm một giá trị trong hàng trên cùng của vùng dò
tìm, nếu có thì lấy ra giá trị ở hàng tương ứng mà ta chỉ định trong cùng cột được tìm thấy. Lưu ý:
+ Nếu trị-tìm là kiểu chuỗi, thường k=0, và phải lưu ý đến khoảng trắng cuối chuỗi hay vị trí
dấu tiếng Việt, kiểu font
+ Kiểu dữ liệu của trị-tìm phải phù hợp với kiểu dữ liệu của cột / hàng đầu tiên của vgt
+ Sử dụng hàm VALUE để chuyển dữ liệu kiểu chuỗi thành kiểu số, hàm TEXT để chuyển
dữ liệu kiểu số thành kiểu chuỗi.
+ trị-tìm không phải là giá trị của 1 ô rỗng. Sử dụng hàm ISBLANK() để kiểm tra.
- CHOOSE(m; gt1; gt2;…; gt_n): lấy ra giá trị thứ m trong n giá trị, m=0 và m>n thì lỗi #VALUE
- TEXT(gt_số; định_dạng): chuyển gt_số thành text theo định-dạng
VD: A4 ="Hôm nay tôi kiếm được "&TEXT(C28;"#.#,0 đồng")
Kiểu Định dạng Data Format_Text: Kết quả
1. Định dạng số: 1000 =Text(B2,"0,0.00") 1,000.00
2. Định dạng tiền 300 =Text(B3, "$0.00") $300.00
3. Định dạng Ngày 29853 =Text(B4,"dd/mm/yyyy") 24/09/1981
4. Định dạng % 0.3 =Text(B5,"0.00%") 30.00% 10. Nhóm hàm kiểm tra:
- ISNA(value): TRUE nếu value báo lỗi #N/A, FALSE nếu value là một giá trị khác
- ISERROR(value): TRUE nếu value là 1 thông báo lỗi
- ISBLANK(value): TRUE nếu value tham chiếu đến ô rỗng 11. Biểu đồ:
Lưu ý trước khi vẽ biểu đồ:
- Dữ liệu đã được tổng hợp lại, không phải dữ liệu chi tiết: tức là cùng 1 đối tượng nhưng xuất
hiện nhiều lần, trên nhiều dòng, mỗi dòng thể hiện 1 nội dung chi tiết, nhờ đó ta sẽ vẽ được
biểu đồ thể hiện đúng và đầy đủ nội dung.
- Dữ liệu vẽ biểu đồ phải thống nhất về định dạng và loại dữ liệu: như các dữ liệu phải cùng
loại cùng là Text hoặc cùng là Number, % chứ không được lẫn lộn giữa các loại này và các dữ
liệu cùng loại phải nằm trên cùng 1 hàng hoặc cùng 1 cột, nhờ đó ta có thể xác định được
dạng biểu đồ phù hợp.
- Các trường dữ liệu (tên cột, tên hàng) phải có nội dung rõ ràng: việc này ảnh hưởng khi vẽ lên
biểu đồ, ta có thể gặp tình trạng, không biết các thành phần của biểu đồ thể hiện nội dung gì.
Phải xác định rõ các nội dung thì mới đọc được ý nghĩa của biểu đồ, nhờ đó ta sẽ xác định
được ý nghĩa của từng nội dung được thể hiện trên biểu đồ. Các bước:
- Chọnvùng dữ liệu trong bảng mà bạn muốn tạo biểu đồ.
- Chọn thẻ Insert trên thanh công cụ > Trong mục Charts, chọn kiểu biểu đồ phù hợp
- Đổi tên biểu đồ bằng cách nhấn vào mục Chart Title trong biểu đồ, sau đó nhập tên biểu đồ mà bạn muốn đặt.