Hàm trong Excel

GHT(chuỗi, số kí tự muốn lấy): Lấy các kí tự phía bên phải của chuỗi.
=RIGHT(“Tp.HCM”, 3) {=HCM}
3.3. MID(chuỗi, số bắt đầu, n): Lấy n kí từ từ số bắt đầu. 
=MID(“Vu Thi Thu”, 4, 3) {=Thi}
3.4. UPPER(chuỗi): Đổi chuỗi thành chữ in.
=UPPER(“van an”) {=VAN AN}
3.5. LOWER(chuỗi): Đổi chuỗi thành chữ thường.
=LOWER(“VAN AN”) {=van an}
3.6. PROPER(chuỗi): Đổi kí tự đầu mỗi từ thành chữ in.
=PROPER(“nguyen thanh huyen coi”) {=Nguyen Thanh Huyen Coi}
3.7. TRIM(chuỗi): Cắt bỏ khoảng trống ở đầu và cuối chuỗi.
=UPPER(“ SCC ”) {=SCC}
4. Nhóm hàm ngày giờ:
4.1. NOW(): Trả về thời điểm hiện tại.
=NOW() {=12:25}
4.2. TODAY(): Trả về ngày tháng năm hiện tại.
=TODAY() {=10/29/2000}
4.3. YEAR(tháng-ngày-năm): Trả về số của năm.
=YEAR(B3) {=98}
4.4. MONTH(tháng-ngày-năm): Trả về số của tháng.
=MONTH(B3) {=11}
4.5. DAY(tháng-ngày-năm): Trả về số của ngày.
=DAY(B3) {=6}
4.6. WEEKDAY(tháng-ngày-năm): Trả về số thứ trong tuần.
=WEEKDAY(B1) {=6}
5. Nhóm hàm điều kiện:
5.1. IF(điều kiện, giá trị đúng, giá trị sai): Trả về giá trị đúng nếu đúng điều kiện, ngược lại trả về giá trị sai.
=IF(C4>=5, “Nhanh”, “Chậm”) {=Nhanh}
5.2. SUMIF(vùng 1, “điều kiện”, vùng 2): Tính tổng các ô trong vùng 2 tương ứng với các ô vùng 1 đã thoả mãn điều kiện.
Dữ liệu trong vùng B4:B7 là 3, 6, 8, 6
Dữ liệu trong vùng C4:C7 là 6, 9, 9, 4
=SUMIF(B4:B7, “>5”, C4:C7) {=22}
Tính tổng giá trị các ô trong vùng C4:C7 tương ứng với các ô trong vùng B4:B7 nếu giá trị các ô này lớn hơn 5, như vậy các ô có giá trị 6, 8, 6 trong vùng B4:B7 thoả mãn điều kiện, tương ứng với các ô có giá trị 9, 9, 4 trong vùng C4:C7, kết quả tổng bằng 22.
5.3. COUNTIF(vùng, “điều kiện): Đếm các ô thoả mãn điều kiện trong vùng.
(Lấy dữ liệu của B4:B7 ở ví dụ trên)
=COUNTIF(B4:B7, “<5”) {=1}
6. Nhóm hàm logic:
6.1. AND(các biểu thức điều kiện): Trả về giá trị “Và” logic (True hay False) của các biểu thức điều kiện.
=IF(AND(B3>5, E3=”A”), “Đ”, “H”)
Nếu B3 lớn hơn 5 và (AND) E4 là A thì cho kết quả là “Đ”, ngược lại kết quả là “H”.
6.2. OR(các biểu thức điều kiện): Trả về giá trị “Hoặc” logic (True hay False) của các biểu thức điều kiện.
=IF(OR(B3<5, C3<1), “Không đạt”, “Đạt”)
Nếu B3 nhỏ hơn 5 hoặc (OR) C3 nhỏ hơn 1 thì cho kết quả là “Không đạt”, ngược lại kết quả là “Đạt”.
6.3. NOT(các biểu thức điều kiện): Trả về giá trị “Không” logic (True hay False) của các biểu thức điều kiện.
=IF(AND(B3>5, NOT(C3=0)), “Đ”, “H”)
Nếu B3 lớn hơn 5 và (AND) C3 không (NOT) bằng 0 thì kết quả là “Đ”, ngược lại sẽ cho kết quả là “H”.
7. Nhóm hàm tìm kiếm:
7.1. VLOOKUP(x, vùng tham chiếu, n, 0): Tìm giá trị x ở cột thứ nhất trong vùng tham chiếu và lấy giá trị tương ứng ở cột thứ n.
(Xem hình bên) Dựa vào bảng mã hàng điền tên thích hợp vào cột tên hàng của bảng phiếu giao hàng.
Lập công thức cho ô C13:
= VLOOKUP(B13, $A$2:$B$5, 2, 0) {=Sắt}
Tìm một giá trị bằng giá trị của ô B13 trong cột thứ nhất của vùng A2:B5 và lấy giá trị tương ứng ở cột thứ 2 (kết quả =Sắt).
* Trong công thức nên đặt giá trị tuyệt đối cho địa chỉ vùng để không bị thay đổi khi sao chép.
7.2. HLOOKUP(x, vùng tham chiếu, n, 0): Tìm giá trị x ở dòng thứ nhất trong vùng tham chiếu và lấy giá trị tương ứng ở dòng thứ n.
(Xem hình bên): Dựa vào bảng giá để điền vào cột giá của bảng phiếu giao hàng.
Lập công thức cho ô D13:
=HLOOKUP(B13, $A$7:$D$8, 2, 0) {=100}
Tương tự như hàm VLOOKUP nhưng tìm theo dòng thay vì cột.
Phần 2: Quản trị dữ liệu
I- Khái niệm về cơ sở dữ liệu:
* Trên một cơ sở dữ liệu dạng bảng của Excel ta có thể: Lọc (Filter), xoá (Delete) rút trích (Extract) những dòng dữ liệu thoả mãn một tiêu chuẩn nào đó. Để thực hiện các thao tác này cần tạo ra những vùng sau:
1. Vùng dữ liệu (Database): Là vùng cơ sở dữ liệu gồm ít nhất 2 dòng (Row). Dòng đầu tiên chứa các tiêu đề cột (Field name), các dòng còn lại chứa dữ liệu gọi là mẩu tin (Record).
2. Vùng tiêu chuẩn (Criteria): Là vùng tiêu chuẩn chứa điều kiện để tìm kiếm, xoá, rút trích hay trích lọc. Vùng này gồm ít nhất 2 dòng. Dòng đầu chứa tiêu đề (Field name), các dòng còn lại chứa điều kiện.
3. Vùng rút trích (Extract): Là vùng trích dữ liệu chứa các mẩu tin của vùng dữ liệu (Database) thoả điều kiện của vùng tiêu chuẩn. Vùng rút trích (Extract) cũng có dòng đầu tiên chứa các tiêu đề muốn rút trích.
II- Sắp xếp dữ liệu:
- Đưa con trỏ ô vào vùng dữ liệu cần sắp xếp.
- Thực hiện Data / Sort, hộp thoại Sort xuất hiện:
- Chọn cột cần sắp xếp trong hộp Sort by. Chọn Ascending để sắp xếp tăng dần. Chọn Descending để sắp xếp giảm dần.
- Nếu muốn sắp xếp cho 2 hay 3 cột cùng lúc, chọn chúng trong các hộp Then by còn lại.
- Chọn Header row để xác nhận chỉ sắp xếp các dòng chứa thông tin (các bản ghi) trong vùng dữ liệu (không sắp xếp dòng tiêu đề).
- Chọn No header row để sắp xếp cả dòng tiêu đề.
- Nhấn OK.,/
III- Lọc, rút trích dữ liệu:
- Khi muốn tìm kiếm hay liệt kê các mẩu tin (Record) theo một yêu cầu nào đó. Trong trường hợp này ta dùng phương pháp lọc (Filter) hoặc phương pháp rút trích dữ liệu (Extract).
1. Lọc tự động:
- Đưa con trỏ ô vào vùng cần lọc (hoặc chọn vùng cần lọc).
- Thực hiện Data\Filter\Autofilter.
- Khi đó các nút mũi tên sẽ xuất hiện tại tiêu đề các cột trong vùng cơ sở dữ liệu.
- Nhấn chuột vào nút mũi tên để chọn các giá trị của cột cần xác định điều kiện lọc. Khi đó các mẩu tin thoả điều kiện sẽ được hiển thị còn các mẩu tin không thoả sẽ bị dấu (hide) đi.
- Muốn hiển thị lại các mẩu tin, chọn Data\Filter\Show all.
- Muốn bỏ chế độ lọc, chọn Data\Filter\Autofilter một lần nữa.
* Trong danh sách còn có những lựa chọn khác:
- All: Hiện trở lại tất cả các bản ghi.
- Top 10: Xuất hiện hộp thoại Top 10 AutoFilter cho phép lọc ra một số bản ghi chứa giá trị cao (Top) hoặc thấp (Bottom).
+ Chọn Top hay Bottom trong hộp thứ nhất.
+ Thay đổi số bản ghi cần lấy trong hộp thứ hai.
+ Hộp thứ ba cho phép xác định số bản ghi cần lấy theo số đếm bình thường (Items) hay theo đơn vị phần trăm (Percent).
- Custom: Xuất hiện hộp thoại Custom AutoFilter cho phép lọc theo các điều kiện phức hợp.
* Nếu có một điều kiện lọc thì chỉ cần sử dụng hai hộp danh sách bên trên như sau:
+ Chọn một toán tử so sánh trong hộp danh sách bên trái:
- equals	=
- does not equal	
- is greater than	>
- is greater than or equal to	>=
- is less than	<
- is less than or equal to	<=
- begins with	bắt đầu bằng kí tự...
- does not begin with	không bắt đầu bằng kí tự...
- ends with	kết thúc bằng kí tự...
- does not end with	không kết thúc bằng kí tự...
- contains	có chứa kí tự...
- does not contain	không chứa kí tự...
+ Nhập vào hộp bên phải giá tr
ị cần so sánh.
* Trường hợp có hai điều kiện lọc thì phải lặp lại các thao tác trên đối với 2 hộp danh sách còn lại (chọn toán tử so sánh trong hộp bên trái, nhập giá trị cần so sánh vào hộp bên phải).
+ Chọn And để kết hợp hai điều kiện trên theo tiêu chuẩn "và" (giống hàm And).
+ Chọn Or để kết hợp hai điều kiện trên theo tiêu chuẩn "và" (giống hàm Or).
+ Nhấn OK.
* Ví dụ: Cho bảng danh sách các học sinh trong lớp.
 Yêu cầu 1: Hiện ra màn hình những học sinh tiên tiến (có điểm trung bình từ 7,5 đến 8,5).
- Thực hiện Data / Filter / AutoFilter để xuất hiện các nút mũi tên bên phải các tiêu đề cột.
- Nhấn nút mũi tên tại cột "Điểm trung bình" và chọn Custom trong danh sách hiện ra.
- Nhập vào hộp Custom AutoFilter các giá trị như sau rồi nhấn OK.
Yêu cầu 2: Tìm những học sinh có họ Lê.
- Nhấn nút mũi tên tại cột "Họ tên" và chọn Custom trong danh sách hiện ra.
- Nhập vào hộp Custom AutoFilter các giá trị như sau rồi nhấn OK.
Vùng tiêu chuẩn (Criteria)
2. Rút trích dữ liệu:
* Trong thao tác này ta cần xác định vùng dữ liệu (Database) và tạo trước vùng tiêu chuẩn (Criteria) cùng vùng rút trích (Extract).
- Đưa con trỏ ô vào ô bất kì trong vùng dữ liệu.
- Thực hiện Data / Filter / Advanced Filter, xuất hiện hộp thoại Advanced Filter.
- Chọn Filter the list, in-place: Các mẩu tin đã trích lọc sẽ xuất hiện trong vùng dữ liệu này.
- Chọn Copy to another location: Đưa các mẩu tin đã trích lọc sang vùng khác.
- Nhập vùng dữ liệu cần trích lọc trong hộp List range. Nhập vùng điều kiện (tiêu chuẩn) trong hộp Criteria range. Nhập địa chỉ ô đầu của vùng rút trích trong hộp Copy to.
- Nếu chọn Unique record only, dữ liệu lọc ra nếu có những dòng trùng nhau sẽ chỉ hiện một dòng.
- Nhấn OK.
IV- Các dạng tiêu chuẩn:
1. Tiêu chuẩn số:
- Ô điều kiện có kiểu số.
Văn
6
2. Tiêu chuẩn chuỗi:
- Ô điều kiện có kiểu chuỗi.
Họ tên
T*
- Trong ô điều kiện có thể chứa các kí tự gộp.
* : Thể hiện cho một nhóm kí tự.
? : Thể hiện cho một kí tự bất kì.
Ví dụ: Cho biết danh sách sinh viên có họ tên bắt đầu là chữ T.
3. Tiêu chuẩn so sánh:
- Ô điều kiện chứa toán tử so sánh kèm với giá trị so sánh.
Văn
>6
- Các toán tử so sánh:
> : Lớn hơn
>= : Lớn hơn hoặc bằng
< : Nhỏ hơn
<= : Nhỏ hơn hoặc bằng
= : Bằng
 : Không bằng
Ví dụ: Cho biết danh sách các sinh viên có điểm văn > 6.
4. Tiêu chuẩn công thức:
- Ô điều kiện có kiểu công thức, trường hợp này cần lưu ý 2 yêu cầu sau:
+ Ô tiêu đề của vùng tiêu chuẩn phải khác với tất cả các tiêu đề của vùng dữ liệu.
+ Trong ô điều kiện phải lấy địa chỉ của ô trong mẩu tin đầu tiên (sau dòng tiêu đề hay là dòng thứ 2 của vùng dữ liệu) để so sánh (ví dụ: Các ô B3, C3, D3).
Ví dụ: Trích lọc danh sách các sinh viên có tổng điểm > 15.
Tổng
=Sum(B3:D3)>15
Trong vùng điều kiện tên tiêu đề đặt là Tổng (khác với các tiêu đề vùng dữ liệu), công thức trong ô điều kiện được xác lập là =Sum(B3:D3)>15. Kết quả như hình bên.
5. Tiêu chuẩn liên kết:
- Có thể lọc, xoá hay rút trích các mẩu tin trong vùng dữ liệu bằng phép giao (And) hay phép hội (Or) của nhiều điều kiện khác nhau.
- Phép AND: Nếu các ô điều kiện cùng dòng.
- Phép OR: Nếu các ô điều kiện khác dòng.
Ví dụ 1: Trích lọc danh sách sinh viên có điểm Văn > 5 và điểm Địa > 5.
Văn
Địa
>5
>5
Ví dụ 2: Trích lọc danh sách sinh viên có điểm Văn lớn hơn hoặc bằng 6.
Văn
>6
6