CHƯƠNG 1 TỔ CHỨC DỮ LIỆU Mã Chương: MH 25-01 Mục tiêu: Giúp người học cũng cố lại một số hàm về số học, logic, thống kê, chuỗi, dò tìm… làm tiền đề cho việc thiết lập và tính toán các biểu bản trong công tác lập báo cáo tài chính trong doanh nghiệp. Tổ chức dữ liệu kế toán trên bảng tính Excel. Ôn tập và bổ sung một số kiến thức căn bản về Execl: 1. Nhóm hàm số học: + Abs(số): trả về giá trị tuyệt đối của một số.
Ví dụ: =Abs(10) 10 =Abs(-10) 10 + Int(số): trả về phần nguyên của một số.65) 5 + Mod(số bị chia, số chia): trả về phần dư của phép chia nguyên Ví dụ: =Mod(7,3) 1 Ví dụ: =Mod(11,3) 2 + Round(số cần làm tròn, vị trí làm tròn): là tròn số theo vị trí chỉ định VD: ROUND(123. Nhóm hàm logic: + Not(giá trị logic): trả về giá trị phủ định. Ví dụ: ô A1 chứa 5, ô A2 chứa 8 =A1>A2 False =Not(A1>A2) True + And(giá trị logic 1, giá trị logic 2, …): 1 Trả về True, nếu tất cả mọi đối số đều là True Trả về False, nếu có ít nhất một đối số của nó là False Ví dụ: =AND(5>3, 15<18) True =AND(5>3, 15<10) , False + Or(giá trị logic 1, giá trị logic 2, …): Trả về False, nếu tất cả mọi đối số đều là False. Trả về True, nếu có ít nhất một đối số của nó là True.
Ví dụ: =Or(5>3, 15<10) True =Or(5<3, 15<10) , False + If(điều kiện, biểu thức 1, biểu thức 2): Nếu điều kiện cho kết quả là đúng, thì hàm trả về biểu thức 1. Nếu điều kiện cho kết quả là sai, thì hàm trả về biểu thức 2. Nhóm hàm thống kê: + Average(số 1, số 2, …) : trả về giá trị trung bình cộng Ví dụ: =Average(4, 5, 6) 5 + Max(số 1, số 2, …) : trả về giá trị lớn nhất Ví dụ: =Max(4, 5, 6) 6 + Min(số 1, số 2, …) : trả về giá trị nhỏ nhất Ví dụ: =Average(4, 5, 6) 4 + Sum(số 1, số 2, …) : trả về tổng số. Ví dụ: =Sum(4, 5, 6) 15 + Count(số 1, số 2, …) : đếm các ô chứa giá trị số.
Ví dụ: =Count(4, “A”, 6) 2 + CountA(số 1, số 2, …) : đếm các ô chứa giá trị. Ví dụ: =Count(4, “A”, 6) 3 =Count(4, “A”, 6, “”) 3 + SumIf(Khối chỉ mục, Điều kiện, Khối tính tổng) : tính tổng có điều kiện. 2 Khối chỉ mục: Khối được so sánh với điều kiện. Điều kiện: Có thể là một giá trị hay một chuỗi chứa biểu thức so sánh.
Khối tính tổng: Khối được tính tổng. + CountIf(Khối chỉ mục, Điều kiện) : đếm các ô thỏa mãn điều kiện. Khối chỉ mục: Khối được so sánh với điều kiện. Điều kiện: Có thể là một giá trị hay một chuỗi chứa biểu thức so sánh.
Ví dụ: Lập công thức tính tổng số tiền và số lượt bán cho bảng tổng hợp theo Tên mặt hàng.1: Bảng tổng hợp tên mặt hang - 01 A B C D E 1 Bảng kê 2 Tên mặt hang Loại Số lượng Đơn giá Thành tiền 3 Gạo 2 5 55 275 4 Nếp 1 4 75 300 5 Gạo 1 6 65 390 6 Gạo 2 4 55 220 7 Nếp 1 12 75 900 8 9 Bảng tổng hợp 10 Tên mặt hàng Tổng tiền Số lượt bán 11 Gạo 885 3 12 Nếp Công thức cho ô B11: =SumIf($A$3:$A$7,A11,$F$3:$F$7) 275+390+220 = 885 Công thức cho ô C11: =CountIf($A$3:$A$7,A11) 3 1. Nhóm hàm xử lý chuỗi: + Len(chuỗi): trả về số cho biết độ dài của chuỗi. 3 Ví dụ: =Len(“Nguyen Van An”) 13 + Left(chuỗi, n): t rích (lấy) ra n ký tự tính từ bên trái của chuỗi. Ví dụ: =Left(“CĐCĐ Đồng Tháp,4) “CĐCĐ” + Right(chuỗi, n): trích (lấy) ra n ký tự tính từ bên phải của chuỗi.
Ví dụ: =Right(“CĐCĐ Đồng Tháp”,9) “Đồng Tháp” + Mid(chuỗi, m, n): trích (lấy) ra n ký tự bắt đầu tại vị trí m trong chuỗi. Ví dụ: =Mid(“CĐCĐ Đồng Tháp”,6,4) “Đồng” + Trim(chuỗi): cắt bỏ tất cả các khoảng trắng thừa trong chuỗi. Ví dụ: =Len(“Nguyen Van An”) 19 =Len(Trim(“Nguyen Van An”)) 13 + Lower(chuỗi): chuyển tất cả các ký tự trong chuỗi thành chữ thường: Ví dụ: =Lower(“ĐỒNG THÁP”) “đồng tháp” + Upper(chuỗi): Chuyển tất cả các ký tự trong chuỗi thành ký tự in hoa. Ví dụ: =Upper(“Đồng Tháp”) “ĐỒNG THÁP” + Proper(chuỗi): chuyển các chữ đầu mỗi từ thành chữ hoa Ví dụ: =Proper(“chào các bạn”) “Chào Các Bạn” =Proper(“CHÀO CÁC BẠN”) “Chào Các Bạn” + Value(chuỗi): đổi chuỗi ra số Ví dụ: =Value(“123”) 123 + Text(Value, format_text): Dùng để đổi số thành chuỗi theo dạng format_text.
Nhóm hàm tìm kiếm: + Vlookup(gía trị tìm, bảng dữ liệu, cột khai thác, mã dò tìm): hàm dò tìm một giá trị trong cột bên trái của bảng dữ liệu và trả về giá trị tương ứng trong cột khai thác. Giá trị tìm: là giá trị được dùng để dò tìm trong cột bên trái của bảng dữ liệu. Bảng dữ liệu: là vùng chứa dữ liệu được dò tìm lẫn khai thác. 4 Cột khai thác: là số thứ tự của cột khai thác trong bảng dữ liệu, nơi hàm sẽ lấy giá trị trả về.
Mã dò tìm: xác định ta muốn chọn cách dò tìm chính xác hay gần đúng: 1 (hoặc bỏ qua): hàm sẽ thực hiện dò tìm giá trị bằng hoặc nhỏ kề với giá trị dùng để dò tìm. Điều kiện là cột dử liệu bên trái của bảng dữ liệu phải xếp thứ tự tăng dần. 0: hàm sẽ thực hiện dò tìm giá trị bằng với giá trị dùng để dò tìm. + Hlookup(gía trị tìm, bảng dữ liệu, dòng khai thác, mã dò tìm): hàm dò tìm một giá trị trên dòng đầu tiên của bảng dữ liệu và trả về giá trị tương ứng trong dòng khai thác.
Giá trị tìm: là giá trị được dùng để dò tìm trong dòng đầu tiên của bảng dữ liệu. Bảng dữ liệu: là vùng chứa dữ liệu được dò tìm lẫn khai thác. Dòng khai thác: là số thứ tự của dòng khai thác trong bảng dữ liệu, nơi hàm sẽ lấy giá trị trả về. Mã dò tìm: xác định ta muốn chọn cách dò tìm chính xác hay gần đúng: 1 (hoặc bỏ qua): hàm sẽ thực hiện dò tìm giá trị bằng hoặc nhỏ kề với giá trị dùng để dò tìm.
Điều kiện là cột dử liệu bên trái của bảng dữ liệu phải xếp thứ tự tăng dần. 0: hàm sẽ thực hiện dò tìm giá trị bằng với giá trị dùng để dò tìm. + Index(bảng dữ liệu, dòng, cột): hàm truy xuất giá trị trong bảng dữ liệu, nếu biết thứ tự dòng và cột của ô chứa giá trị đó. Bảng dữ liệu: là vùng dữ liệu được tham chiếu.
Dòng: xác định thứ tự dòng của ô chứa dử liệu cần truy xuất. Cột: xác định thứ tự cột của ô chứa dử liệu cần truy xuất + Match(giá trị tìm, khối chỉ mục, mã dò tìm): hàm dò tìm một giá trị trong khối chỉ mục và trả về vị trí của giá trị tìm thấy. Giá trị tìm: là giá trị được dùng để dò tìm trong khối chỉ mục. 5 Khối chỉ mục: là vùng chứa dữ liệu được dò tìm (khối chỉ mục là 1 dòng hay 1 côt).
Mã dò tìm: xác định ta muốn chọn cách dò tìm chính xác hay gần đúng: 1 (hoặc bỏ qua): hàm sẽ thực hiện dò tìm giá trị bằng hoặc nhỏ kề với giá trị dùng để dò tìm. Điều kiện là khối chỉ mục phải xếp thứ tự tăng dần. -1 : hàm sẽ thực hiện dò tìm giá trị bằng hoặc lớn hơn kề với giá trị dùng để dò tìm. Điều kiện là khối chỉ mục phải xếp thứ tự giảm dần.
0: hàm sẽ thực hiện dò tìm giá trị bằng với giá trị dùng để dò tìm. Ví dụ: Lập công thức tính đơn giá cho bảng kê như sau dựa vào Tên mặt hàng và Loại.2: Bảng tổng hợp tên mặt hang - 02 A B C D E 1 Bảng giá 2 Tên mặt hàng/Loại 1 2 3 3 Gạo 65 55 50 4 Nếp 75 65 58 5 6 Bảng tổng hợp 7 Tên mặt hàng Loại Số lượng Đơn giá Thành tiền 8 Gạo 2 9 9 Nếp 1 12 10 Gạo 1 4 Công thức cho ô D8: Cách 1: =Vlookup(A8,$A$3:$D$4,Match(B8,$B$2:$D$2,0),0) Cách 2: =Hlookup(B8,$B$2:$D$4,Match(A8,$A$3:$A$4,0),0) Cách 3: =Index($B$3:$D$4,Match(A8,$A$3:$A$4,0),Match(B8,$B$2:$D$2,0)) 1. Nhóm hàm về thời gian: + Today(): trả về ngày tháng năm hiện hành của hệ thống máy tính. 6 Ví dụ: Hôm nay là ngày 20/10/2007 =Today() 20/10/2007 + Now(): trả về ngày tháng năm, giờ phút giây hiện hành của hệ thống máy tính.
Ví dụ: Hôm nay là ngày 20/10/2007, 11:05:25 =Now() 20/10/2007 11:05:25 + Date(Năm, tháng, ngày): trả về giá trị ngày tháng năm, nếu cung cấp các đối số chỉ năm. Tháng, ngày. Ví dụ: =Date(2007, 10, 20) 20/10/2007 + Day(ngày tháng năm): trả về chỉ số ngày trong tháng từ 1 – 31 + Month(ngày tháng năm): trả về chỉ số tháng trong năm từ 1 – 12 + Year(ngày tháng năm): trả về chỉ số năm. Ví dụ: Hôm nay là ngày 20/10/2007 =Day(Today()) 20 =Month(Today()) 10 =Year(Today()) 2007 + DateValue(chuỗi ngày tháng năm): chuyển chuỗi chứa ngày tháng năm thành giá trị ngày tháng năm.
Ví dụ: Hôm nay là ngày 20/10/2007 =DateValue(“20/10/2007”) + 5 25/10/2007 1. Nhóm hàm khác: + Row(ô tham chiếu): hàm trả về số cho biết thứ tự dòng của ô tham chiếu. Trường hợp không sử dụng đối số, hàm sẽ trả về thứ tự dòng của ô đang tham chiếu. Ví dụ : =Row(B5) 5 ô A3 chứa công thức =Row() 3 + Column(ô tham chiếu): hàm trả về số cho biết thứ tự cột của ô tham chiếu.
Trường hợp không sử dụng đối số, hàm sẽ trả về thứ tự cột của ô đang tham chiếu. Ví dụ : =Column(B5) 2 ô D3 chứa công thức =Column() 4 7 + Address(thứ tự dòng, thứ tự cột, kiểu địa chỉ): hàm trả về chuổi cho biết địa chỉ ô theo thứ tự dòng, thứ tự cột chỉ định. Kiểu địa chỉ: 1 (hoặc bỏ qua): Địa chỉ tuyệt đối 2: Địa chỉ hổn hợp, cố định dòng 3: Địa chỉ hổn hợp, cố định cột 4: Địa chỉ tương đối Ví dụ: =Address(4, 5, 1) $E$4 =Address(4, 5) $E$4 =Address(4, 5, 2) E$4 =Address(4, 5, 3) $E4 =Address(4, 5, 4) E4 + Indirect(địa chỉ chuỗi): hàm trả về địa chỉ thực sự của một chuỗi chứa địa chỉ. Ví dụ: A B C D 1 5 2 $A$1 =$A$1 + 5 10 =Indirect(“$A$1”) + 5 10 =Indirect(D2) + 5 10 =Indirect(Address(1,1)) + 5 10 + ISNA(value): hàm trả về giá trị True nếu gặp lỗi #N/A, ngược lại trả về False.