Giảng viên: Vũ Thị Tuyết Lan
Tr-êng ®¹i häc lao ®éng x· héi
---------------0o0------------- LÝ THUYẾT HÀM M«n Tin häc c¬ b¶n I Ng N - g ê - i ê ch c u h È u n È n b Þ b : G i G ¶n ¶ g n g v ivªn ª n V ò ò T h T Þh Tu T y u Õ y t Õ tL a L n a n Hµ néi - 9/2021 1
Giảng viên: Vũ Thị Tuyết Lan hàm điều kiện
IF(bt logic, gt đúng, gt sai)
IF(bt logic1, gt đúng 1, if(bt logic 2, gt đúng 2, …, if(bt logic n, gt đúng n, gt sai)…))
IF(left(text,num_chart)="…",gt đúng, gt sai)
IF(and(bt logic1, bt logic 2,…),gt đúng, gt sai)
nếu hôm nay trời mưa tôi sẽ nghỉ học ( điều kiện là "nếu trời mưa")
=IF(ĐIỀU KIỆN KIỂM TRA, GIÁ TRỊ NẾU ĐK ĐÚNG, GIÁ TRỊ NẾU ĐK
=IF(HÔM NAY = “TRỜI MƯA ”,”TÔI SẼ NGHỈ HỌC ”,”TÔI PHẢI ĐI HỌC ”) Ví dụ 1:
Tại cột A tôi có cột dữ liệu dạng số. Nhu cầu của tôi là sử dụng hàm IF để tại cột B
tôi có được kết quả là:
Nếu giá trị bên cột A=0 thì cột B hiển thị "Không", nếu cột A khác 0 thì hiển thị
Ta chỉ cần gõ công thức (không phân biệt HOA thường) tại ô B2, như sau:
Copy công thức này cho ô B3 đến được kết quả: Ví dụ 2:
Vẫn với cột giá trị tương tự như tại ví dụ 1. Nhưng nhu cầu của tôi giờ là làm sao
để cột B hiển thị theo mong muốn 2
Giảng viên: Vũ Thị Tuyết Lan
Nếu giá trị cột A = 0 thì hiển thị "Không", nếu <0 thì hiển thị "Âm", nếu >0 thì hiển thị "Dương".
Ta viết công thức tại ô B2 như sau:
=IF(A2=0,"Không",IF(A2<0,"âm","dương"))
a có thể hiểu công thức trên qua minh họa
Copy công thức vừa nhập tại B2 cho các ô B3 đến B7, ta có được kết quả như Ví dụ 3:
Nếu chức vụ là GĐ thì thưởng
,nếu chức vụ là PGĐ thì thưởng
còn lại không thưởng E2="GĐ" E2="PGĐ"
có nhiều hàm IF lồng nhau
chưa hết hàm IF chưa được đóng ngoặc 3
Giảng viên: Vũ Thị Tuyết Lan
Hàm INT và MOD. Ví dụ: ta đi du lịch viết gọn số lại là INT: lấy số nguyên
D7)/30):lấy số 1 trong số 1.7 MOD: lấy phần dư
D7,30): lấy số 7 trong số 1.7 – – HOẶC
Cú pháp: AND(giá trị 1 , giá trị 2,...)
(giá trị 1 , giá trị 2,...)
OR(D3="Xuất sắc",H3<1000) Kết hợp lồng hàm AND vào hàm IF IF(AND(giá trị 1
rị 2,...),"giá trị nếu đk đúng","giá trị nếu đk sai")
ụ dựa vào điểm trung bình, hãy xếp loại học lực của học sinh đó cụ thể : ế Đ Đ xếp loại trung bình
nếu ĐTB >=6.5 VÀ ĐTB < 8.0 thì xếp loại Khá,
nếu ĐTB > 8.0 thì xếp loại giỏi
ĐTB < 5.0 thì xếp loại yếu.
ứ tính cột Xếp Loại (đặt con trỏ tại ô K7) 4
Giảng viên: Vũ Thị Tuyết Lan Ví dụ 2:
IF(E3>=9,"học bổng",IF(AND(E3>=8,H3="Nam"),"xem xét" IF(AND(OR(E3>=5,E3<=6.5),I3="giỏi"),"chờ xét","
CÁC HÀM XỬ LÝ CHUỖI
LEFT: LẤY CÁC KÝ TỰ BÊN TRÁI CỦA CHUỖI
LEFT(địa chỉ ô chứa ký tự cần lấy, số ký tự cần lấy) Kết hợp
Câu hỏi: Điề dữ liệu cho cột Mặt hàng, biết rằng:
ế ký tự đầu của cột mã HĐ thì ghi “ aki”,
ếu ký tự đầu của cột mã HĐ thì ghi “Jean”.
Để làm câu hỏi trên, ta cần dùng hàm IF để tìm mặt hàng với diển giải như sau:
Ký tự đầu tiên của mã hóa đơn =K
àm thế nào để tách được kí tụ đầu tiên từ Mã HĐ ta sẽ sử dụng hàm Left =Left ( mã HĐ,1)
Cách sử dụng hàm IF kết hợp hàm LEFT trong ví dụ này như sau:
=IF(LEFT(B3,1)=”K”,”Kaki”, ”Jean”)
Ví dụ lồng hàm IF –
LẤY CÁC KÝ TỰ BÊN PHẢI Tương tự hàm LEFT
LẤY CÁC KÝ TỰ Ở GIỮA
MID(địa chỉ ô cần lấy dữ liệu, bắt đầu từ ký tự thứ mấy, lấy bao nhiêu ký tự) 5
Giảng viên: Vũ Thị Tuyết Lan =IF(MID(B4,3,1)=”G” ,”Gạo”,IF(MID(B4,3,
1)=”D”,”Đường”,”T han”)
Điền cột Tên SP: nếu ký tự thứ 3 của cột Mã số
thì là Gạo, là Đường,
BIỂU THỨC SỐ CẦN LÀM TRÒN Ví dụ: ẾP THỨ
RANK(ô đầu tiên của cột cần xếp thứ,bôi toàn bộ cột cần xếp thứ bấm phím F4)
Ví dụ: Xếp thứ hạng cho sinh viên trong lớp, dựa vào Điểm TB của sinh viên
NGÀY (DAY), THÁNG (MONTH), NĂM (YEAR):
ại ô B2, ta có ngày tháng năm Để lấy ngày 15: Để lấy tháng 9: Để lấy năm 2015: 6
Giảng viên: Vũ Thị Tuyết Lan
Công thức tính tuổi
năm hiện tại – YEAR(ô chứa ngày tháng năm)
ĐỊNH DẠNG DỮ LIỆU: Từ chữ số, từ về số,… 7
Giảng viên: Vũ Thị Tuyết Lan
HÀM TÌM KIẾM từ trên xuống (theo cột)
VLOOKUP(Giá trị dò tìm, bôi đen bảng phụ bấm F4, số cột bôi đen bảng phụ, 0 Dạng 1: Dạng 2: Dạng 3: Ví dụ
Điền dữ liệu cho cột tên hàng dựa vào ký tự của cột mã hàng của bảng phụ Giá trị dò tìm
Số cột bôi đen của
lấy ở bảng chính) bảng phụ là 2 mặc định là số 0 Bôi đen 2 cột bảng phụ, bấ hí F4
xác định giá trị dò tìm nhìn cột mã hàng ở bảng chính nó sẽ trùng với mã hàng ở cột bảng phụ Ví dụ dạng
Giờ cột mã hàng ở bảng chính không là I, O, S nữa mà
hưng mã hàng ở bảng phụ vẫn là I
. Ta phải tách và chỉ lấy I, O
(1 ký tự bên trái) ở bảng chính để tham chiếu giống cột mã hàng ở Bảng phụ. nằm bên tay trái của phải Cách làm tương tự ví dụ 1 8
Giảng viên: Vũ Thị Tuyết Lan Ví dụ dạng hàm Vlookup lồng IF
Câu hỏi xác định phải dùng Vlookup hoặc Hlookup. Vì đây là tìm kiếm theo cột nên dùng Vlookup
Giá trị dò tìm của hàm Vlookup là lấy 2 kí tự đầu trong cột Mã VT nên phải
Đơn giá có 2 cột, không biết lấy cột nào nên phải dùng hàm IF để tính.
Đơn giá lại phụ thuộc vào 2 ký tự cuối của cột Mã VT nên phải dùng hàm
là hàm chính, nên sẽ để ngoài cùng. Sau đó, LEFT lấy
giá trị dò tìm. Sau đó, bôi đen bảng phụ bấm F4 Đến số cột của bảng phụ, vì
Đơn giá chia làm 2 cột loại “a” và loại “b”
không biết lấy cột nào nên phải
ì “a”, “b” nằm bên tay phải của cột mã VT nên phải dùng tiếp
RIGHT. Giá trị cuối cùng của VLOOKUP luôn mặc định là 0. Kết quả như sau: Giá trị dò tìm Bôi đen bảng Số cột của Mặc định là 0 phụ bấm F4 bảng phụ
HLOOKUP: Tìm kiếm từ trái
LOOKUP(Giá trị dò tìm, bôi đen bảng phụ bấm F4, số dòng bôi đen bảng phụ, 0 Dạng 1: Dạng 2: Dạng 3:
Hàm HLOOKUP làm tương tự như VLOOKUP, 9
Giảng viên: Vũ Thị Tuyết Lan
Đưa ra danh sách, lọc ra, trích sang vùng mới ta dùng Advance filter Dạng câu hỏi:
B1: tạo vùng điều kiện
Bấm chuột ra 1 ô trống ( nằm ngoài vùng bảng chính)
Tạo tiêu đề cho vùng điều kiện
Đánh vùng điều kiện bên dưới tiêu đề Ví dụ: B1: Chọn
ôi đen toàn bộ bảng chính
ôi đen toàn bộ vùng điều kiện
ấm 1 phím trái chuột ra 1 ô trắng để đặt kết quả B5: Bấm OK
Chú ý: kết quả trả về của Advance filter là 1 danh sách 10