Bài giảng Microsoft Excel

* Microsoft Excel *   BÀI 1: GIỚI THIỆU CHUNG Microsoft Excel là một phần mềm thuộc bộ Microsoft Office chuyên dùng để xử lý, tính toán với các số liệu theo bảng biểu. * I. KHỞI ĐỘNG VÀ THOÁT KHỎI EXCEL 1. Khởi động Excel Vào Start/ Programs/ Excel Hoặc: Kích trái chuột vào biểu tượng trên Office Bar 2. Thoát khỏi Excel Vào Office button/ Exit Hoặc: Kích trái chuột vào nút Close ở góc phải trên cửa sổ. Hoặc: Ấn tổ hợp phím Alt + F4 * 3. Lưu tệp tin Excel Vào Office Button/Save hoặc kích chuột vào biểu tượng hoặc ấn tổ hợp phím Ctrl + S * Có hai trường hợp xảy ra: - Trường hợp lưu tệp tin lần đầu tiên sẽ XHHT Save As Trong hộp thoại này: + Save in: Để chọn ổ đĩa thư mục chứa tệp tin + File name: Đặt tên cho tệp tin + Save: Để lưu tệp tin Trường hợp lưu lại những nội dung thay đổi trong tệp tin thì ta chỉ việc chọn Save * 4. Mở tệp tin Excel đã có sẵn Vào Office Button / Open hoặc kích chuột vào biểu tượng hoặc nhấn tổ hợp phím Ctrl +O  Trong hộp thoại Open + Look in: Tìm đến ổ đĩa thư mục chứa tệp tin cần mở + Open: Chọn tên tệp tin cần mở * II. MỘT SỐ KHÁI NIỆM CƠ BẢN CỦA EXCEL 1. WorkBook (hay một tệp tin Excel) Trong Microsoft Excel, một WorkBook là một tập tin. Trên một WorkBook có thể có một hoặc nhiều WorkSheet (Bảng tính). Thông thường, trên một WorkBook mặc định chỉ có 3 WorkSheet có tên là Sheet1, Sheet2, Sheet3 * 2. WorkSheet (hay bảng tính) Là nơi dùng để liệt kê và phân tích dữ liệu. Trên mỗi bảng tính có tối đa là 256 cột (Column) được đặt tên là: A, B, C…IV và có 65536 hàng (Row) được đánh số từ 1,2,3…65536. Bảng tính có tên mặc định là Sheet1, Sheet2,…. * 3. Cell (hay ô) Giao giữa cột và dòng gọi là ô (Cell). Mỗi ô có một địa chỉ. Địa chỉ ô được xác định bởi tên cột và tên dòng Ví dụ: ô C3 * 4. Vùng Là tập hợp nhiều ô đứng kề nhau, địa chỉ vùng được xác định bởi địa chỉ của ô ở góc trên bên trái và ô ở góc phải dưới của vùng, hai địa chỉ này được ngăn cách với nhau bởi dấu hai chấm(:). Ví dụ: Vùng C3:E9 * 5. Địa chỉ tương đối và tuyệt đối Tên gọi của mỗi ô hay vùng được gọi là địa chỉ ô hay vùng đó. Có hai loại địa chỉ, là địa chỉ tương đối và địa chỉ tuyệt đối * a) Địa chỉ tương đối Địa chỉ thông thường của ô hoặc vùng khi nhập vào trong công thức gọi là địa chỉ tương đối Ví dụ: {Tổng Lương = Lương Chính + Phụ Cấp} Tại ô G3 ta nhập công thức như sau: = D3+E3 (D3, E3 là các địa chỉ tương đối) Tính chất của địa chỉ tương đối: Khi sao chép công thức các địa chỉ tương đối trong công thức sẽ tự thay đổi theo vị trí tương ứng ở nơi được sao chép đến * b) Địa chỉ tuyệt đối Các địa chỉ trong công thức đứng sau dấu $ sẽ trở thành địa chỉ tuyệt đối. Có thể phân ra các trường hợp sau đây: + Địa chỉ tuyệt đối cột, tương đối dòng: Ví dụ: = C3*$D3 ($D3 là địa chỉ tuyệt đối cột) + Địa chỉ tương đối cột, tuyệt đối dòng: Ví dụ: = C3*D$3 (D$3 là địa chỉ tương đối cột, tuyệt đối dòng) * + Địa chỉ tuyệt đối dòng, tuyệt đối cột: Ví dụ: = C3*$D$3 ($D$3 là địa chỉ tuyệt đối cột, tuyệt đối dòng) Tính chất của địa chỉ tuyệt đối: Khi sao chép công thức, các địa chỉ tuyệt đối trong công thức sẽ không thay đổi b) Địa chỉ tuyệt đối * III. CÁC KIỂU DỮ LIỆU TRONG EXCEL Kiểu số Kiểu chuỗi Kiểu dữ liệu ngày tháng Kiểu công thức * III. CÁC KIỂU DỮ LIỆU TRONG EXCEL 1. Kiểu số Dữ liệu nhập vào là các chữ số. Ở chế độ mặc nhiên dữ liệu dạng số sẽ hiện thị phía bên phải của ô, nếu là số âm thì có thể nhập dấu (-) trước hoặc để trong ngoặc đơn. * 2. Kiểu chuỗi Dữ liệu nhập vào ô là các chuỗi ký tự Mặc nhiên chuỗi sẽ hiện thị ở phía bên trái của ô. Khi dữ liệu là số, muốn Excel hiểu là dạng chuỗi phải nhập dấu nháy đơn (‘) vào trước số đó III. CÁC KIỂU DỮ LIỆU TRONG EXCEL * 3. Dữ liệu kiểu ngày tháng Dữ liệu là các chuỗi ký tự thể hiện ngày tháng. Ta có thể sử dụng dấu phân cách là (-) hoặc gạch chéo (/), để phân cách giữa ngày, tháng, năm Trong Excel mặc định kiểu nhập ngày tháng theo thứ tự: Tháng, Ngày, Năm III. CÁC KIỂU DỮ LIỆU TRONG EXCEL * 4. Kiểu công thức Dữ liệu kiểu công thức sẽ là các phép toán hay các hàm. Muốn nhập dữ liệu dạng công thức phải nhập dấu bằng (=) trước sau đó đến công thức hoặc tên hàm III. CÁC KIỂU DỮ LIỆU TRONG EXCEL * BÀI 2: MỘT SỐ THAO TÁC ĐỊNH DẠNG I. CÁC THAO TÁC LIÊN QUAN ĐẾN CỘT, HÀNG Thay đổi độ rộng cột (Column Width). Có nhiều cách: Cách 1: Đưa chuột vào đường phân cách của cột cần thay đổi, khi biểu tưởng chuyển thành hình dấu cộng có mũi tên hai chiều, ấn và giữ chuột trái đồng thời di chuột sang phải (nếu mở rộng) hoặc di sang trái (nếu thu hẹp) Cách 2: Chọn một hay nhiều cột cần thay đổi độ rộng. - Vào Home/Format / Column / Width / XHHT ta nhập độ rộng vào Column Width và chọn OK để kết thúc. * 2. Thay đổi chiều cao của hàng (Row Height) Có nhiều cách: Cách 1: đưa chuột vào đường phân cách của hàng cần thay đổi độ rộng, khi biểu tưởng chuột chuyển thành dấu cộng có mũi tên hai chiều, ấn và giữ chuột trái đồng thời di chuột xuống dưới (nếu mở rộng) hoặc di chuột lên trên (nếu thu hẹp). Cách 2: Bôi đen một hay nhiều hàng cần thay đổi độ rộng. Vào Home/Format / Row / Height / XHHT ta nhập độ rộng vào Row Height và chọn OK. * 3. Chèn thêm cột vào bảng tính - Đặt con trỏ tại cột bên phải cột định chèn - Vào Insert / Column 4. Chèn thêm hàng vào bảng tính - Đặt con trỏ tại hàng phía dưới hàng cần chèn - Vào Insert / Row * 5. Xoá cột trong bảng tính Chọn các cột cần xoá - Vào Home / Delete (Hoặc kích phải chuột vào cột đã bôi đen chọn Delete) 6. Xoá hàng trong bảng tính - Chọn các hàng cần xoá - Vào Home / Delete (hoặc kích phải chuột vào hàng đã bôi đen chọn Delete) * III. CÁC THAO TÁC LIÊN QUAN ĐẾN BẢNG TÍNH Chèn thêm bảng tính vào tập tin Đổi tên bảng tính Xoá bảng tính Hợp ô và tách ô Định dạng đường viền trong bảng tính * 1. Chèn thêm bảng tính vào tập tin Một WorkBook (Một tập tin Exel) mặc định có 3 WorkSheet (bảng tính) có tên là Sheet1, Sheet2, Sheet3 Tuy nhiên ta có thể chèn thêm bảng tính vào tập tin bằng cách vào Insert / WorkSheet 2. Đổi tên bảng tính Kích chuột phải vào tên bảng tính cần đổi, xuất hiện thực đơn chọn lệnh Rename Nhập tên mới cho bảng tính sau đó ấn phím Enter trên bàn phím III. CÁC THAO TÁC LIÊN QUAN ĐẾN BẢNG TÍNH * 3. Xoá bảng tính Kích phải chuột vào tên bảng tính cần xoá, xuất hiện thực đơn, chọn Delete. Xuất hiện thông báo, chọn OK để xoá bảng tính. 4. Hợp ô và tách ô Hợp ô: Để nối nhiều ô thành một ô ta thực hiện như sau: Chọn những ô cần hợp thành một. Kích chuột trái vào biểu tượng trên thanh công cụ. Hoặc: Vào Format / Cells / XHHT / Alignment, đánh dấu vào ô Merge Cells / OK. b) Tách ô: (Chỉ tách những ô đã hợp) Cách thực hiện như sau: - Kích chuột vào ô đã hợp - Kích chuột phải / Delete *Chú ý: Nếu ô cần tách có chứa dữ liệu thì sau khi tách dữ liệu trong ô sẽ mất. III. CÁC THAO TÁC LIÊN QUAN ĐẾN BẢNG TÍNH * 5. Định dạng đường viền trong bảng tính. Trong mỗi bảng tính mặc định khi in ra không có đường viền. Nếu muốn có đường viền ta thực hiện như sau: - Chọn vùng bảng cần tô đường viền. - Vào Format / Cells / XHHT / Border - Style: Kích chuột để chọn kiểu đường viền muốn tô. - Kích trái chuột vào các cạnh tương ứng trong khung Border. - Kích chọn OK. * III. Các thao tác liên quan đến dữ liệu 1. Nhập số thứ tự tự động Cách 1: Nhập số bắt đầu sau đó nhập số kế tiếp vào hai ô đầu tiên - Bôi đen hai ô này - Di chuột vào góc phải dưới của khung bôi đen xuất hiện hình dấu cộng (+), ấn và giữ chuột trái đồng thời di chuột theo chiều mà ta cần đánh số thứ tự đến vị trí cần thả * 2. Định dạng kiểu chữ - Bôi đen vùng dữ liệu cần định dạng - Vào Format / Cells / XHHT / Font, trong đó: * + Font Style: Kiểu chữ + Size: Kích cỡ chữ + Underline: Tạo chữ có gạch chân + Color: Màu chữ + Preview: Xem trước phần định dạng - Trong khung Effects có ba lựa chọn: + Strikethrough: Tạo đường gạch ngangqua dữ liệu + Superscript: Tạo chỉ số trên + Subscript: Tạo chỉ số dưới  Chọn OK để kết thúc * 3. Xoay chiều dữ liệu trong bảng tính Dữ liệu nhập vào bảng tính có vị trí mặc định theo chiều ngang. Tuy nhiên, có thể thay đổi hướng dữ liệu bằng cách: - Bôi đen các ô dữ liệu cần thay đổi hướng. - Vào Format / Cells / XHHT / Alignment: + Orientation: Thay đổi hướng dữ liệu trong khung. + Degrees: Điều chỉnh góc xoay trong ô. - Chọn OK để kết thúc. * 4. Định dạng xuống dòng trong ô Mặc định Excel chỉ nhập dữ liệu trên một dòng trong cùng một ô. Để có thể nhập nhiều dòng trong cùng một ô, ta thực hiện như sau: - Muốn xuống hàng trong ô ta nhấn đồng thời hai phím Alt + Enter, rồi tiếp tục nhập dữ liệu cho dòng thứ hai và tiếp tục cho đến hết dòng cần nhập. * 5.Căn chỉnh dữ liệu vào giữa ô theo chiều ngang và chiều dọc Đánh dấu vùng dữ liệu cần căn giữa Vào Format / Cells / XHHT Format Cells Chọn Alignment:+ Chọn Center trong ô Horizontal: Cănvào giữa ô theo chiều ngang + Chọn Center trong ô Verical: Căn vào giữa ô theo chiều dọc - Kích chọn OK để kết thúc * 6. Định dạng ký tự phân cách hàng nghìn của dữ liệu kiểu số Đánh dấu vùng số cần định dạng Vào Format / Cells / XHHT / Number. + Chọn mục Number trong ô Category + Nếu sử dụng ký tự phân cách hàng nghìn, kích chuột đánh dấu vào ô: Use 1000 Separator - Nhập số lẻ của phần thập phân trong ô Decimal places - Kích chọn OK để kết thúc * 7. Sao chép dữ liệu trong bảng tính Bôi đen vùng dữ liệu cần sao chép: Vào Home / Copy hoặc dùng tổ hợp phím (CTRL+C) hoặc kích chuột vào biểu tượng trên thanh Ribon- Kích chuột vào ô đầu tiên của vùng cần sao chép tới Vào Home / Paste hoặc ấn tổ hợp phím (CTRL+V), hoặc kích chuột vào biểu tượng trên thanh công cụ Ribon Sao chép đặc biệt: - Chỉ sao chép phần dữ liệu (kết quả) được tính từ việc lập công thức hoặc dùng các hàm - Đưa trỏ chuột vào ô chứa kết quả mới tính được, xuất hiện dấu cộng kéo chuột đến vị trí cần sao chép (sao chép công thức). * 8. Di chuyển dữ liệu trong bảng tính - Bôi đen vùng dữ liệu cần di chuyển - Home / Cut hoặc ấn tổ hợp phím (Ctrl + X) hoặc kích chuột vào biểu tượng trên thanh công cụ Ribon - Kích chuột vào ô đầu tiên của vùng mới - Vào Home/ Paste hoặc ấn tổ hợp phím (Ctrl + V) hoặc kích chuột vào nút lệnh Paste trên thanh công cụ Ribon * 9. Xoá dữ liệu trong bảng tính. - Bôi đen vùng dữ liệu cần xoá. - Vào Home / Clear: Trong mục này chọn: + All: Nếu xoá tất cả trong vùng đã bôi đen (Format: Định dạng, Contents: Nội dung, Comments: Ghi chú). + Formats: Chỉ xoá phần định dạng kiểu dữ liệu trong vùng. + Contents (hoặc Delete): Chỉ xoá phần nội dung nhưng vẫn tồn tại phần định dạng kiểu dữ liệu (nếu có) và phần ghi chú * (nếu có) trong vùng. + Comments: Chỉ xoá phần ghi chú trong vùng (nếu có). IV. CÁC THAO TÁC ĐỊNH DẠNG HỆ THỐNG. Định dạng kiểu nhập ngày tháng. - Chọn vùng dữ liệu cần định dạng. - Vào Format / Cells / XHHT Chọn Number / Date / Custom: * - Nhập kiểu định dạng (dd/mm/yyyy) trong ô Type. Trong đó: + dd (date - ngày): Hiện thị hai số của phần ngày. + mm (month - tháng): Hiện thị hai số phần tháng. + yyyy (year - năm): Hiện thị đầy đủ bốn số của phần năm. - Chọn OK để kết thúc. * BÀI 3 CÔNG THỨC VÀ TÍNH TOÁN CƠ BẢN 1. Cách lập công thức. Công thức Excel là sự kết hợp dữ liệu giữa các ô, thông qua các phép toán như: Cộng (+), Trừ (-), Nhân (*), Chia (/), Luỹ thừa (^),… Khi nhập công thức vào ô phải bắt đầu bằng dấu bằng (=). Nhập xong công thức gõ phím Enter để chương trình tự động tính kết quả. * Ví dụ: Để tính kết quả của cột Lương Chính trong bảng dưới đây ta chỉ cần lập công thức thec cách tính sau: Lương Chính = Hệ Số*290000. - Tại ô D3 ta lập công thức: = C3*290000. - Tính kết quả của cột Phụ Cấp theo quy định sau: Phụ cấp của tất cả cán bộ trong cơ quan bằng 5% của Lương Chính. - Tại ô E3 ta lập công thức: = D3*5%. * 2. Sao chép công thức trong bảng tính. Sau khi đã tính kết qủa cho đối tượng thứ nhất bằng cách lập công thức, thì kết quả của những đối tượng tiếp theo sẽ được tính bằng cách sao chép công thức đã tính cho người đầu tiên. Cách sao chép công thức như sau: - Kích chuột vào ô kết quả vừa tính được. - Di chuột vào góc phải dưới của ô kết quả cho chuột xuất hiện hình dấu cộng (+), ấn và giữ chuột trái đồng thời di chuột đến đối tượng cuối cùng cần tính. * BÀI 4: CÁC HÀM CƠ BẢN I. NHÓM HÀM SỐ. 1. Hàm ABS. - Hàm lấy giá trị tuyệt đối của một số. - Cú pháp: = ABS (Số hoặc địa chỉ ô chứa số). Ví dụ: = 2 + ABS (- 5.2) = 7.2. 2. Hàm INT. Hàm lấy phần nguyên của một số. - Cú pháp: = INT (Số hoặc địa chỉ ô chứa số). * Ví dụ: = INT (6.7) = 6. = INT (-6.1) = -7 (trả về số nhỏ hơn). 3. Hàm MOD. Hàm lấy giá trị dư của phép chia. - Cú pháp: = MOD (Số bị chia, Số chia). - Trong đó: Số bị chia, số chia có thể là các địa chỉ cuả các ô. Ví dụ: = MOD (10,3) = ?. = MOD (D4,C4) (Lấy giá trị của ô D4 chia cho giá trị của ô C4). * 4. Hàm ROUND. Hàm làm tròn số. - Cú pháp: = ROUND (Số cần làm tròn, số lẻ). - Trong đó: Số cần làm tròn có thể là địa chỉ của ô chứa số cần làm tròn. Ví dụ: = ROUND (1234.345 ;2) = = ROUND (15.813;0) = = ROUND (1645.453; -3)= = ROUND (1445.453; 3) = ROUND (D4,2) {Làm tròn số trong ô D4, lấy hai số lẻ sau phần thập phân}. * 5. Hàm SQRT. Hàm lấy giá trị là căn bậc hai. - Cú pháp : = SQRT (Số). - Trong đó: Số có thể là địa chỉ của ô chứa số cần tính căn bậc hai. Ví dụ: = SQRT (16) = 4. = SQRT (D4) {Tính căn bậc hai của số trong ô D4}. II. NHÓM HÀM THỐNG KÊ. 1. Hàm AVERAGE: Hàm tính giá trị trung bình của dãy số. * - Cú pháp: = AVERAGE(Số thứ 1, số thứ 2,…) - Trong đó: Số thứ 1, số thứ 2,...có thể là địa chỉ của các ô không liên tiếp, hoặc là một vùng dữ liệu liên tiếp. Ví dụ: = AVERAGE (10,5,6) {Kết quả = 7}. = AVERAGE (D2: D10) {Tính giá trị trung bình của dãy số từ ô D2 đến ô D10}. 2. Hàm MAX: Hàm lấy giá trị lớn nhất của dãy số. - Cú pháp: = MAX (Số thứ nhất, số thứ hai,…). - Trong đó: Số thứ nhất, số thứ hai,…có thể là * địa chỉ của các ô chứa số, hoặc là một vùng dữ liệu liên tiếp. Ví dụ: = MAX (100,46,120) {Kết quả = 120}. = MAX (D2: D10) {Lấy giá trị lớn nhất của dãy số có địa chỉ từ D2 đến D10}. 3. Hàm MIN: Hàm lấy giá trị nhỏ nhất của dãy số. - Cú pháp: = MIN (Số thứ 1, số thứ 2,…) - Trong đó: Số thứ 1, số thứ 2,…có thể là địa chỉ của các ô chứa số, hoặc là một vùng dữ liệu liên tiếp. * Ví dụ: = MIN (100,46,120) {Kết quả = 46}. = MIN (D2: D10) {Lấy giá trị nhỏ nhất của dãy số có địa chỉ từ D2 đến D10}. 4. Hàm SUM: Hàm tính tổng của dãy số. - Cú pháp: = SUM (Số thứ nhất, số thứ hai,…) - Trong đó: Số thứ nhất,…có thể là địa các chỉ ô chứa số, hoặc là một vùng dữ liệu liên tiếp. Ví dụ: = SUM (12,4,56) {Kết quả = 72}. = SUM (A2: A10) {Tính tổng của dãy số từ A2 đến A10}. * III. NHÓM HÀM XỬ LÝ CHUỖI. 1. Hàm LEFT: Hàm lấy ký tự bên trái của chuỗi. - Cú pháp: = LEFT(Chuỗi, số ký tự cần lấy). - Trong đó: Chuỗi có thể là địa chỉ của ô chứa chuỗi ký tự. Ví dụ: = LEFT(“ANH”,2) {Kết quả = AN}. Chú ý: Khi nhập chuỗi vào hàm phải để trong dấu nháy kép “ ”, còn đối với địa chỉ chứa ô không cần dấu nháy kép. 2.Hàm Mid:Hàm lấy ký tự ở giữa của chuỗi -Cú pháp: = Mid(Chuỗi,Ký tự bắt đầu,kýtự kết thúc) -Ví dụ: Mid(“Anh”,2,1) {kết quả = n} * 2. Hàm RIGHT: Hàn lấy ký tự bên phải của chuỗi. - Cú pháp: = RIGHT(Chuỗi, số ký tự cần lấy). - Trong đó: Chuỗi có thể là địa chỉ của ô chứa chuỗi ký tự. Ví dụ: = RIGHT(“ANH”, 1) {Kết quả = H}. * Chú ý: Khi nhập chuỗi vào hàm phải để trong dấu nháy kép “ ”, còn đối với địa chỉ chứa ô không cần dấu nháy kép. = RIGHT (C2, 1) {Lấy một kí tự bên phải của chuỗi ký tự trong ô C2}. * IV. NHÓM HÀM CÓ ĐIỀU KIỆN. Hàm IF: Hàm trả về giá trị đúng nếu điều kiện đúng, ngược lại trả về giá trị sai. - Cú pháp: = IF (Điều kịên, giá trị đúng, giá trị sai). Ví dụ: = IF (C3 > =5, “Đậu”, ‘‘Trượt’’). {Nếu giá trị trong ô C3 lớn hơn hoặc băng 5 thì đưa ra kết quả Đậu, ngoài điều kiện đó là thì đưa ra kết quả Trượt}. * Chú ý: Kết quả đưa ra dưới dạng chuỗi thì phải đặt trong dấu ngoặc kép “ ”. * V. NHÓM HÀM NGÀY THÁNG. 1. Hàm DAY Hàm đưa ra giá trị ngày - Cú pháp: = DAY(Ngày/Tháng/Năm) - Trong đó: Ngày/Tháng/Năm có thể là địa chỉ của ô chứa Ví dụ: = DAY (“12/11/2003”) {Kết quả = 12 (ngày 12)} Nếu dữ liệu trong ô C3 là 12/11/2003 thì ta thành lập hàm như sau: = DAY (C3) {Kết quả = 12} * 2. Hàm MONTH. Hàm lấy giá trị tháng. - Cú pháp: = MONTH(Ngày/Tháng/Năm). - Trong đó: Ngày/Tháng/Năm có thể là địa chỉ của ô chứa. Ví dụ: = MONTH(“12/11/2003”) {Kết quả = 11 (tháng 11)}. Nếu dữ liệu trong ô C3 là 12/11/2003 thì ta thành lập hàm như sau: = MONTH(C3) {Kết quả = 11}. 3. Hàm YEAR. Hàm lấy giá trị năm. * - Cú pháp = YEAR(Ngày/Tháng/Năm). - Trong đó: Ngày/Tháng/Năm có thể là địa chỉ của ô chứa. Ví dụ: = YEAR(“12/11/2003”) {Kết quả = 2003 (năm 2003)}. Nếu dữ liệu trong ô C3 là 12/11/2003 thì ta lập hàm như sau: = YEAR(C3) {Kết quả = 2003}. *Hàm NetworkDay:tính số ngày(ngày nghỉ,lễ…) VD:Hôm nay là ngày 9/6/2010 và ngày 21/8/2010 là ngày nghỉ lễ.Tính số ngày từ ngày 9/6 đến 21/8 VI. NHÓM HÀM LOGIC. Hàm AND (Và). Hàm có điều kiện đồng thời. * - Cú pháp = AND (Biểu thức điều kiện thứ nhất, biểu thức điều kiện thứ 2, …) Ví dụ: Đưa ra kết quả với điều kiện thi tuyển: Điểm toán và điểm lý phải lớn hơn hoặc băng 5 thì đậu, còn lại thì trượt. Ta thành lập hàm như sau: = IF (AND (C3 > = 5, D3> = 5), “Đậu”, “trượt”) {C3 chứa điểm toán, D3 chứa điểm lý}. 2. Hàm OR (Hoặc). Hàm đưa ra giá trị đúng nếu một trong hai điều kiện của biểu thức là đúng. * - Cú pháp: = OR(Biểu thức điều kiện thứ nhát, biểu thức điều kiện thứ hai,…). Ví dụ: Đưa ra kết quả xet tuyển với điều kiện sau: Nếu điểm toán < 5 hoặc điểm văn < 5 thì không xét, ngược lại thi được xét. Thành lập hàm như sau: = IF (OR (C2 < 5, D3 < 5), “không xét”, “được xét”). - Trong đó: Ô C2 chứa điểm toán, ô D3 chứa điểm văn. * VII. NHÓM HÀM TÌM KIẾM. 1. Hàm VLOOKUP: Hàm tìm kiếm và tham chiếu theo cột thoả mãn điều kiện. - Cú pháp: = VLOOKUP (X,D,C,0). Tìm kiếm giá trị trong cột C của vùng tham chiếu D thoã mãn điều kiện của ô có địa chỉ X. - Trong đó: X: Địa chỉ của ô đầu tiên trong cột dò tìm. D: Địa chỉ tuyệt đối của vùng tham chiếu. C: Số thứ tự của cột giá * trị tìm kiếm trong vùng tham chiếu. 0: Cách dò tìm 1: Cách dò tìm. Ví dụ: Hãy tìm giá trị trong bảng tham chiếu để điền vào cột Chức Vụ và cột Tiền Tết trong bảng chi Tiền Tết. * Yêu cầu: Nếu ký tự đầu của cột Mã CB là A thì Chức Vụ là Chủ Tịch, Tiền Tết là 500000, nếu là B thì Chức vụ là Phó Chủ Tịch, Tiền Tết là 400000, nếu là C thì Chức Vụ là Chuyên Viên, Tiền Tết là 300000, còn lại là Cán Bộ và Tiền Tết là 200000. * Cách thành lập hàm như sau: + Tại ô C3 của cột chức vụ ta nhập công thức như sau: = VLOOKUP (LEFT(B3,1),$B$14: $D$17,3,0). + Tại ô D3 của cột Tiền Tết ta nhập công thức như sau: = VLOOKUP (LEFT(B3,1),$B$14: $D$17,4,0). 2. Hàm HLOOKUP: Hàm tìm kiếm và tham chiếu theo hàng thoã mãn điều kiện. - Cú pháp: = HLOOKUP (X,D,Y,0). Tìm kiếm giá trị trong hàng Y của vùng tham chiếu * D thoả mãn điều kiện của ô có địa chỉ X. - Trong đó: X: Địa chỉ của ô đầu tiên của hàng dò tìm. D: Địa chỉ tuyệt đối của vùng tham chiếu. Y: Số thứ tự của hàng giá trị tìm kiếm trong vùng tham chiếu. 0: Cách dò tìm 1: Cách dò tìm * BÀI 6: TRÍCH LỌC DỮ LIỆU TRONG DỮ LIỆU TRONG BẢNG TÍNH 1. Trích lọc dữ liệu tự động đơn giản. - Kích chuột vào dòng tiêu đề cột của bảng dữ liệu. - Vào Data / Filter / Autofilter. - Kích chuột vào nút tại cột dữ liệu cần lọc, danh sách các giá trị cần lọc hiện ra, kích chuột chọn giá trị cần lọc. Khi đó, danh sách dữ liệu thoả mãn điều kiện lọc hiện ra. * * Chú ý: + Muốn trở lại danh sách ban đầu, kích chuột vào nút có màu xanh chọn All. + Bỏ chức năng lọc tự động: Vào lại: Data / Filter / Autofilter. 2. Trích lọc nâng cao. - Kích chuột vào dòng tiêu đề cột của bảng dữ liệu. - Vào Data / Filter / Autofilter. - Kích chuột vào nút tại cột có dữ liệu cần lọc, danh sách các đề mục hiện ra, kích chuột vào mục Custom / XHHT: * - Kích chuột vào mũi tên của ô: Show Rows Where để chọn điều kiện lọc. Trong đó: + Equals: Bằng. + Does not equals: Khác. + Is greater than: Lớn hơn + Is greater than or equal to: Lớn hơn hoặc bằng. + Is less than: Nhỏ hơn. + Is less than or equal to: Nhỏ hơn hoặc bằng. + Begins with: Bắt đầu bởi… + Does not begin with: Không bắt đầu bởi… * + Ends with: Kết thúc bằng giá trị… + Does not end with: Không kết thúc bằng giá trị… + Co