Bài giảng Tin học đại cương - Nguyễn Thị Dung

FALSE khi các tham số truyền vào có giá trị là FALSE, ngược lại trả về giá trị TRUE Ví dụ: OR(3>6,5=5) -> TRUE AND(3>3,6 FALSE 2. Hàm về số Hàm ABS Hàm INT Hàm ROUND Hàm MOD a. Hàm ABS Cú pháp: ABS() Ý nghĩa: Hàm trả về giá trị tuyệt đối của biểu thức truyền vào Ví dụ: ABS(-7)=7 ABS(8)=8 b. Hàm INT Cú pháp: INT() Ý nghĩa: Hàm trả về số nguyên có giá trị nhỏ hơn giá trị của biểu thức truyền vào. Ví dụ: INT(7.4)=4 INT(8.76)=8 c. Hàm MOD Cú pháp: MOD(, ) Ý nghĩa: Hàm trả về phần dư của phép chia / Ví dụ: MOD(7,4)=3 MOD(8,7)=1 d. Hàm ROUND Cú pháp: ROUND(, ) Ý nghĩa: Hàm trả về giá trị được làm tròn của tại vị trí làm tròn do tham số thứ 2 chỉ định Nguyên tắc: Nếu giá trị tại vị trí tham số thứ hai +1 lớn hơn 5, giá trị tại vị trí tham số thứ hai được tăng lên một, ngược lại giữ nguyên Ví dụ ROUND(74.32,1)=74.3 ROUND(74.56,1)=74.6 3. Các hàm về chuỗi Hàm LEFT Hàm RIGHT Hàm MID Hàm LEN a. Hàm LEFT Cú pháp: LEFT (, ) Ý nghĩa: Hàm trả về các ký tự từ bên trái của một chuỗi. Số ký tự trả về được chỉ định trong tham số thứ 2. Ví dụ: LEFT(‘Công ty may 10,7)=‘Công ty’ LEFT(‘CĐN Long Biên’,3)=‘CĐN’ b. Hàm RIGHT Cú pháp: RIGHT (, ) Ý nghĩa: Hàm trả về các ký tự từ bên phải của một chuỗi. Số ký tự trả về được chỉ định trong tham số thứ 2. Ví dụ: RIGHT(‘Công ty may 10’,6)=‘may 10’ RIGHT(‘CĐN Long Biên’,9)=‘Long Biên’ c. Hàm MID Cú pháp: MID (,, ) Ý nghĩa: Hàm trả về các ký nằm ở giữa của một chuỗi. Ký tự bắt đầu được chỉ định trong tham số thứ 2, Số ký tự trả về được chỉ định trong tham số thứ 3. Ví dụ: MID(‘Công ty may 10’,9,3)=‘may’ MID(‘CĐN Long Biên’,5,4)=‘Long’ d. Hàm LEN Cú pháp: LEN () Ý nghĩa: Hàm trả về số ký tự có trong . Ví dụ: LEN(‘Công ty may 10’)=15 MID(‘CĐN Long Biên’)=13 4. Hàm về ngày, giờ Hàm DATE Hàm TIME Hàm DAY Hàm MONTH Hàm YEAR Hàm NOW Hàm HOUR Hàm MINUTE Hàm SECOND Hàm WEEKDAY Hàm DAY360 Hàm DATEVALUE a. Hàm DATE Cú pháp: DATE (,,) Ý nghĩa: Hàm trả về giá trị kiểu ngày dựa trên các tham số truyền vào. Ví dụ: DATE(2009,2,12)=12/9/2009 b. Hàm TIME Cú pháp: TIME (,,) Ý nghĩa: Hàm trả về giá trị kiểu giờ dựa trên các tham số truyền vào. Ví dụ: TIME(4,20,12)=4:20:12 c. Hàm DAY Cú pháp: DAY () Ý nghĩa: Hàm trả về ngày trong tháng dựa trên các tham số truyền vào. Ví dụ: DAY(31/12/2008)=31 d. Hàm MONTH Cú pháp: DAY () Ý nghĩa: Hàm trả vê tháng trong năm dựa trên các tham số truyền vào. Ví dụ: MONTH(31/12/2008)=12 e. Hàm YEAR Cú pháp: YEAR () Ý nghĩa: Hàm trả về năm dựa trên các tham số truyền vào. Ví dụ: YEAR (31/12/2008)=2008 e. Hàm NOW Cú pháp: NOW () Ý nghĩa: Hàm trả về ngày giờ hiện hành của hệ thống. Ví dụ: NOW()=8/10/2009 15:28 g. Hàm HOUR Cú pháp: HOUR () Ý nghĩa: Hàm trả về giờ trong ngày dựa trên tham số truyền. Ví dụ: HOUR (15:25:5)=15 h. Hàm MINUTE Cú pháp: MINUTE () Ý nghĩa: Hàm trả về phút dựa trên tham số truyền vào. Ví dụ: MINUTE (15:25:5)=25 i. Hàm SECOND Cú pháp: SECOND () Ý nghĩa: Hàm trả về giây dựa trên tham số truyền vào. Ví dụ: SECOND (15:25:5)=5 k. Hàm WEEKDAY Cú pháp: WEEKDAY (,) Ý nghĩa: Hàm trả về thứ trong tuần dựa trên tham số truyền vào và phụ thuộc vào giá trị của . 1: giá trị trả về là CN;T2;…,T7 tương ứng giá trị 1-7 2: giá trị trả về là T2;…,T7;CN tương ứng giá trị 1-7 3: giá trị trả về là T2;…,T7;CN tương ứng giá trị 0-6 Ví dụ: WEEKDAY (8/10/2009,1)=5 WEEKDAY (8/10/2009,2)=4 l. Hàm DAYS360 Cú pháp: DAYS360 (, ) Ý nghĩa: Hàm trả về số ngày là khoảng cách giữa ngày bắt đầu và ngày kết thúc. Ví dụ: DAYS360 (“10/8/2009”, “10/30/2009”)=22 m. Hàm DATEVALUE Cú pháp: DATEVALUE () Ý nghĩa: Hàm trả về biểu thức dạng ngày, giờ dựa trên tham số truyền vào. Ví dụ: DATEVALUE (“10/8/2009”)=10/8/2009 5. Các hàm thống kê Hàm SUM Hàm COUNT Hàm AVERAGE Hàm MAX Hàm MIN a. Hàm SUM Cú pháp: SUM (, ,..,) SUM() Ý nghĩa: Hàm trả về giá trị tổng của các tham số truyền vào. Ví dụ: SUM(1,2,3,4,5,6)=21 SUM(A1:D2)=36 b. Hàm COUNT Cú pháp: COUNT (, ,..,) COUNT() Ý nghĩa: Hàm đếm các số trong danh sách các tham số truyền vào. Ví dụ: COUNT(1,2,3,4,5,6)=6 COUNT(A1:D2)=8 c. Hàm AVERAGE Cú pháp: AVERAGE (, ,..,) AVERAGE () Ý nghĩa: Hàm trả về giá trị trung bình qua các số trong danh sách các tham số truyền vào. Ví dụ: AVERAGE (1,2,3,4,5,6)=3.5 AVERAGE (A1:D2)=4.5 d. Hàm MAX Cú pháp: MAX (, ,..,) MAX () Ý nghĩa: Hàm trả về giá trị lớn nhất qua các số trong danh sách các tham số truyền vào. Ví dụ: MAX (1,2,3,4,5,6)=6 MAX (A1:D2)=8 e. Hàm MIN Cú pháp: MIN (, ,..,) MIN () Ý nghĩa: Hàm trả về giá trị lớn nhất qua các số trong danh sách các tham số truyền vào. Ví dụ: MIN (1,2,3,4,5,6)=1 MIN (A1:D2)=1 6. Hàm điều kiện Hàm IF Hàm SUMIF Hàm COUNTIF a. Hàm IF Cú pháp: IF (, ,) Ý nghĩa: Hàm trả về một trong hai giá trị tuỳ theo giá trị của biểu thức logic là TRUE hoặc FALSE. Ví dụ: b. Hàm SUMIF Cú pháp: SUMIF (, ,) Ý nghĩa: Hàm trả về giá trị tổng của những phần tử được chọn trên vùng tính tổng, những phần từ này được chọn ứng với những dòng trên vùng kiểm tra có giá trị thoả mãn điều kiện. Ví dụ: Ví dụ: Tính tổng lương của các nhân viên trong công ty c. Hàm COUNTIF Cú pháp: COUNTIF (, ) Ý nghĩa: Hàm trả về giá trị đếm những phần tử được chọn trên vùng kiểm tra mà thoả mãn điều kiện. Ví dụ: Ví dụ: Đếm số nhân viên trong công ty 7. Hàm tìm kiếm Hàm VLOOKUP Hàm HLOOKUP a. Hàm VLOOKUP Cú pháp: VLOOKUP (, , ,) Mô tả: : Giá trị hàm sẽ tìm trên cột đầu tiên : vùng tìm kiếm có thể là bảng, vùng có đặt tên, một mảng. : là thứ tự cột sẽ lấy giá trị trả về nếu tím thấy. : 0: tìm gần đúng 1: tìm chính xác a. Hàm VLOOKUP Ý nghĩa: Hàm tìm kiếm giá trị trên cột đầu tiên của vùng tìm và trả về một giá trị của dòng tìm thấy trên cột được chỉ định trong hàm. VLOOKUP là hàm tìm kiếm theo chiều dọc (trên cột) Ví dụ: Tính phụ Cap chuc vu b. Hàm HLOOKUP Cú pháp: HLOOKUP (, , ,) Mô tả: : Giá trị hàm sẽ tìm trên dòng đầu tiên : vùng tìm kiếm có thể là bảng, vùng có đặt tên, một mảng. : là thứ tự dòng sẽ lấy giá trị trả về nếu tím thấy. : 0: tìm gần đúng 1: tìm chính xác a. Hàm VLOOKUP Ý nghĩa: Hàm tìm kiếm giá trị trên dòng đầu tiên của vùng tìm và trả về một giá trị của cột tìm thấy trên dòng được chỉ định trong hàm. HLOOKUP là hàm tìm kiếm theo chiều ngang (trên dòng) Ví dụ: Tính phụ cấp chức vụ Bài 14. Cơ sở dữ liệu trên bảng tính Các khái niệm trong cơ sở dữ liệu Các thao tác trên cơ sở dữ liệu 1. Các khái niệm trong CSDL a. Vùng dữ liệu nguồn – Database range Đây là vùng chứa dữ liệu gồm có ít nhất 2 dòng với dòng đầu tiên là tiêu đề của các cột, các dòng còn lại sẽ chứa dữ liệu, mỗi dòng được gọi là một mẩu tin (Record) b. Vùng tiêu chuẩn – Criteria range Vùng này chứa các tiêu chuẩn (hay còn gọi là điều kiện) để giúp người dùng tìm kiếm. xoá hay rút trích những dòng dữ liệu thoả mãn tiêu chuẩn được đặt ra. Vùng này có ít nhất 2 dòng: dòng đầu chứa tiêu đề, các dòng còn lại chứa tiêu chuẩn c. Vùng rút trích – Extract range Trong thao tác rút trích, nếu có yêu cầu đưa dữ liệu rút trích được vào vùng mới trên Worksheet thì vùng mới này gọi là vùng rút trích. Vùng có dòng đầu tiên chứa các tiêu đề của dữ liệu muốn rút trích, các dòng còn lại sẽ chứa dữ liệu rút trích được. Database range Criteria range Extract range d. Các dạng vùng tiêu chuẩn Tiêu chuẩn kiểu số: Ô tiêu chuẩn có giá trị là số và có thể sử dụng các toán tử so sánh , >=, . d. Các dạng vùng tiêu chuẩn Tiêu chuẩn kiểu chuỗi: Ô tiêu chuẩn có giá trị là chuỗi và có thể sử dụng các ký tự: * (thay cho một nhóm ký tự bất kỳ) ? (thay cho một ký tự bất kỳ). d. Các dạng vùng tiêu chuẩn Tiêu chuẩn công thức: Ô tiêu chuẩn dạng là một công thức. Khi dùng tiêu chuẩn công thức phải chú ý hai điểm sau: Tiêu đề của vùng tiêu chuẩn phải khác với tất cả các tiêu đề trên vùng dữ liệu nguồn Trong ô tiêu chuẩn, công thức nhập vào phải lấy địa chỉ của mẩu tin đầu tiên tại cột tương ứng trên vùng dữ liệu làm giá trị so sánh Ví dụ: xây dựng vùng tiêu chuẩn để lọc ra các nhân viên và bảo vệ trong công ty: d. Các dạng vùng tiêu chuẩn Liên kết tiêu chuẩn: Ta có thể liên kết các tiêu chuẩn lại với nhau, khi đó: Những tiêu chuẩn lọc trên cùng một dòng được hiểu là liên kết AND, khác dòng được hiểu là liên kết bằng toán tử OR. 2. Các thao tác trên cơ sở dữ liệu a. Sắp xếp dữ liệu Để sắp xếp dữ liệu ta thực hiện các bước sau: Chọn vùng dữ liệu cần sắp xếp Từ thực đơn Data/Sort Thiết lập các thông số trong hộp thoại sort: Chọn cột sắp xếp Sắp tăng Sắp giảm Lựa chọn khi vùng chọn sắp xếp có tiêu đề b. Lọc dữ liệu tự động – Auto filter Để sử dụng chức năng Auto Filter, ta thực hiện như sau: Chọn vùng dữ liệu cần lọc Từ thực đơn Data/Filter/AutoFilter Chọn nút mũi tên tại cột cần lọc, trên dòng tiêu đề Chọn một giá trị trong danh sách, dữ liệu sẽ lọc theo giá trị đó. Muốn lọc dữ liệu theo các tiêu chuẩn khác chọn Custom Ví dụ: Lọc các nhân viên có mức phụ cấp là 100000. c. Lọc dữ liệu cao cấp – Advanced Filter Các bước thực hiện: Từ thực đơn Data/Filter/Advanced Filter Đưa con trỏ vào ô List range, sau đó quét chọn vùng dữ liệu nguồn. Đưa con trỏ vào ô Criteria range, sau đó quét chọn vùng tiêu chuẩn Chọn mục Copy to another location để xuất kết quả ra vị trí mới, sau đó chọn vùng rút trích Nhấn OK để thực hiện Ví dụ: Lọc ra các nhân viên và bảo vệ trong công ty. 3. Các hàm cơ sở dữ liệu Hàm DSUM Hàm DCOUNT Hàm DAVERAGE Hàm DMAX Hàm DMIN a. Hàm DSUM Cú pháp: DSUM (, ) Ý nghĩa: Hàm trả về tổng các ô trên tên cột thỏa điều kiện của vùng tiêu chuẩn. Ví dụ: Ví dụ: Tính tổng phụ cấp của các nhân viên trong công ty b. Hàm DCOUNT Cú pháp: DCOUNT (, ) Ý nghĩa: Hàm đếm các ô kiểu số trên tên cột thỏa điều kiện của vùng tiêu chuẩn. Ví dụ: Ví dụ: Đếm tổng số các nhân viên trong công ty c. Hàm DAVERAGE Cú pháp: DAVERAGE (, ) Ý nghĩa: Hàm đếm các ô kiểu số trên tên cột thỏa điều kiện của vùng tiêu chuẩn. Ví dụ: Ví dụ: Tính phụ cấp trung bình của Nhân viên và PGĐ d. Hàm DMAX Cú pháp: DMAX (, ) Ý nghĩa: Hàm trả về giá trị lớn nhất của các ô trên tên cột thỏa điều kiện của vùng tiêu chuẩn. Ví dụ: Ví dụ: Tính mức lương cấp cao nhất của PGĐ d. Hàm DMIN Cú pháp: DMIN (, ) Ý nghĩa: Hàm trả về giá trị nhỏ nhất của các ô trên tên cột thỏa điều kiện của vùng tiêu chuẩn. Ví dụ: Ví dụ: Tính mức lương cấp thấp nhất của PGĐ