TÀI LIỆU BÀI GIẢNG EXCEL NÂNG CAO

y cũng là những công thức không phải là hằng số. Nó giúp bạn tính toán chính xác hơn. Lưu ý đến thứ tự year, month, day trong hàm vì bạn có thể nhầm lẫn vì hiểu theo quy cách ngày của Việt Nam: day, month, year. Ví dụ Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức bên dưới vào. A B C 1 2 Ngày Tháng Năm 40 10 2005 Công thức Giải thích =DATE(C2,B2,A2) Trả về 09/11/2005 Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 7  Hàm NOW: Công dụng Trả về ngày giờ hiện tại trong hệ thống của bạn. Nếu định dạng ô là General trước khi hàm nhập công thức, kết quả trả về ở định dạng ngày tháng. Công thức =NOW() Lưu ý! Số bên phải trong chuỗi số thập phân đại diện cho giờ, số bên trái đại diện cho ngày tháng. Hàm NOW() chỉ thay đổi khi Worksheet được tính toán lại. Nó không tự động cập nhật được. Để cập nhật nó bạn có thể viết Marco để sau một khỏang thời gian nào đó gọi hàm NOW() để nó cập nhật.  Hàm TODAY: Công dụng Trả về ngày hiện tại trong hệ thống của bạn. Nếu định dạng ô là General trước khi hàm nhập công thức, kết quả trả về ở định dạng ngày tháng. Công thức =TODAY() Lưu ý! Hàm NOW() trả về định dạng ngày tháng và thời gian hiện tại, Hàm TODAY() chỉ trả về ngày tháng hiện tại.  Hàm DAY: Công dụng Trả về thứ tự của ngày từ chuỗi ngày tháng. Công thức =DAY(serial_number) serial_number dạng chuỗi số tuần tự của ngày cần tìm. Ngày tháng này nên nhập bằng hàm DATE hoặc kết quả trả về từ hàm khác. Có thể bị lỗi nếu bạn nhập serial_number là một chuỗi dạng văn bản. Ví dụ Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức bên dưới vào. A B C 1 2 3 Họ và tên Ngày sinh ĐTB Nguyễn Nhật Minh 10/09/1990 7.8 Công thức Giải thích =DAY(B2) Trả về 10  Hàm MONTH: Công dụng Trả về thứ tự của tháng từ giá trị kiểu ngày tháng. Công thức =MONTH(serial_number) serial_number dạng chuỗi số tuần tự của tháng cần tìm. Ngày tháng này nên nhập bằng hàm DATE hoặc kết quả trả về từ hàm khác. Có thể bị lỗi nếu bạn nhập serial_number là một chuỗi dạng văn bản. Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 8 Ví dụ Để dễ hiểu bạn có thể copy dữ liệu bên trong bảng sau vào một trang bảng tính mới và nhập công thức bên dưới vào. A B C 1 2 3 HỌ VÀ TÊN NGÀY SINH ĐTB Nguyễn Nhật Minh 10/09/1990 7.8 Công thức Giải thích =MONTH(B2) Trả về 09  Hàm YEAR: Công dụng Trả về năm của một giá trị hoặc chuỗi đại diện cho ngày tháng. Công thức =YEAR(serial_number) serial_number dạng chuỗi hoặc số thập phân đại diện ngày tháng mà bạn cần tìm số năm của nó. Giá trị này nên được nhập bằng hàm DATE hoặc là kết quả các công thức hoặc hàm khác. Có thể bị lỗi nếu bạn nhập serial_number là một chuỗi dạng văn bản. Ví dụ Để dễ hiểu bạn có thể copy dữ liệu bên trong bảng sau vào một trang bảng tính trắng và nhập công thức bên dưới vào. A B C 1 2 3 HỌ VÀ TÊN NGÀY SINH ĐTB Nguyễn Nhật Minh 10/09/1990 7.8 Võ Tấn Tuấn 08/10/1991 5.6 Công thức Giải thích =YEAR(B2) Trả về 1990. =YEAR(B3)>YEAR(B2) Trả về TRUE. Buổi 02, 03, 04: Các hàm thông dụng, sử dụng thường xuyên - VLookup, HLookup - Match, Index - Các hàm thống kê đơn giản: Sum, CountA, Max, Min, Average - Các hàm thống kê có điều kiện: CountIf, SumIf - Hàm Round, Mod, Int - Tham khảo các hàm thống kê database: DSum, DCount, DCountA, DMax, DMin, DAverage  Hàm VLOOKUP: Công dụng Dò tìm một giá trị ở cột đầu tiên bên trái của một bảng dữ liệu. Nếu tìm thấy sẽ trả về giá trị ở cùng trên dòng với giá trị tìm thấy trên cột mà bạn chỉ định. Hàm VLOOKUP thường dùng để điền thông tin vào bảng dữ liệu từ bảng dữ liệu phụ. VLOOKUP xuất phát từ vertical lookup : dò tìm theo phương đứng, hay theo cột. Công thức =VLOOKUP(lookup_value,table_array,row_index_num,range_lookup) Viết cách khác: =VLOOKUP(trị dò,bảng dò,cột chứa kết quả,cách dò) lookup_value: là tìm một giá trị dùng để tìm kiếm, nó có thể là một giá trị, một tham chiếu hay một chuỗi ký tự. Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 9 table_array là vùng chứa dữ liệu cần tìm. Đây là bảng dữ liệu phụ có nội dung thường cố định, bao quát để bạn lấy dữ liệu.  Các giá trị ở cột đầu tiên có thể là giá trị số, chuỗi ký tự, hoặc logic.  Nếu range_lookup là TRUE (range_lookup=1) thì các giá trị ở cột đầu tiên của bảng dữ liệu phụ này phải được sắp xếp tăng dần từ -2,1,0,1,2,...,A- Z,FALSE,TRUE. Nếu không hàm VLOOKUP sẽ trả giá trị không chuẩn xác.  Để sắp xếp các giá trị trong bảng dữ liệu từ trên xuống để hàm cho kết quả phù hợp khi bạn dùng range_lookup là TRUE: Chọn vùng dữ liệu cần sắp xếp, kích vào menu Data, Sort. Nhấn nút Options bên dưới, đánh dấu Soft top to bottom, rồi nhấn OK. Kích chọn cột cần sắp xếp trong danh sách. Chọn Ascending, và nhấn OK row_index_num số thứ tự cột trên bảng dữ liệu phụ mà dữ liệu bạn cần lấy. Giá trị trả về nằm trên cột bạn chỉ định này và ở dòng mà hàm tìm thấy giá trị dò tìm lookup_value. range_lookup là giá trị logic bạn chỉ định muốn VLOOKUP tìm kiếm chính xác hay là tương đối. Nếu range_lookup là TRUE (range_lookup=1) hàm sẽ trả về kết quả tìm kiếm tương đối. Nếu không tìm thấy kết quả chính xác, nó sẽ trả về một giá trị lớn nhất mà nhỏ hơn giá trị tìm kiếm lookup_value. Nếu range_lookup là FALSE (range_lookup=0) hàm tìm kiếm chính xác, nếu không có trả về lỗi #N/A! Lưu ý! Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của bảng dữ liệu phụ, VLOOKUP trả về lỗi #N/A!. Khi xuất hiện lỗi #N/A! bạn có thể mắc lỗi nhập dư một khoảng trống ở phía sau giá trị dò tìm hoặc trong bảng dữ liệu, kể cả chính và phụ. Khi dùng hàm VLOOKUP để điền dữ liệu cho một bảng dữ liệu thì trong công thức cần phải tạo địa chỉ tuyệt đối cho bảng dữ liệu phụ table_array để công thức đúng cho các hàng còn lại khi bạn copy công thức xuống các ô bên dưới. Ví dụ Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng dưới và nhập các công thức sau vào ô trống bất kỳ trong bảng tính. A B C D E 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 DANH MỤC HÀNG HÓA NHẬP KHẨU THÁNG 12 STT Mã hàng Tên hàng Ngày nhập Số lượng 1 HDD ? 01/12/2006 20 2 CPU ? 03/12/2006 50 3 CDW ? 05/12/2006 70 4 HDD ? 07/12/2006 100 5 CPU ? 08/12/2006 200 6 USB ? 07/12/2006 500 BẢNG TÊN HÀNG Mã hàng Tên hàng CDR Ổ CD - Rom CDW Ổ ghi CD-Rom HDD Ổ đĩa cứng USB Ổ đĩa cứng USB CAS Thùng máy Công thức Giải thích =VLOOKUP(B3,$C$11:$D$15,2,0)Nhập công thức vào ô C3 để lấy tên hàng tương ứng với mã hàng từ bảng dữ liệu phụ. Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 10 Copy công thức xuống các ô còn lại. Lưu ý địa chỉ vùng ô của bảng dữ liệu phụ phải là địa chỉ tuyệt đối để khi copy công thức hàm VLookup mới đúng cho các ô tiếp theo.  Hàm HLOOKUP: Công dụng Dò tìm một giá trị ở dòng đầu tiên của một bảng dữ liệu. Nếu tìm thấy sẽ trả về giá trị ở cùng trên cột với giá trị tìm thấy trên hàng mà bạn chỉ định. Hàm HLOOKUP thường dùng để điền thông tin vào bảng dữ liệu từ bảng dữ liệu phụ. HLOOKUP xuất phát từ horizontal lookup : dò tìm theo phương ngang, hay theo dòng. Công thức =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) Viết cách khác: =HLOOKUP(trị dò,bảng dò,dòng chứa kết quả,cách dò) lookup_value: là tìm một giá trị dùng để tìm kiếm, nó có thể là một giá trị, một tham chiếu hay một chuỗi ký tự. table_array là vùng chứa dữ liệu cần tìm. Đây là bảng dữ liệu phụ có nội dung thường cố định, bao quát để bạn lấy dữ liệu.  Các giá trị ở dòng đầu tiên có thể là giá trị số, chuỗi ký tự, hoặc logic.  Nếu range_lookup là TRUE thì các giá trị ở dòng đầu tiên của bảng dữ liệu phụ này phải được sắp xếp tăng dần từ -2,1,0,1,2,...,A-Z,FALSE,TRUE. Nếu không hàm HLOOKUP sẽ trả giá trị không chuẩn xác.  Để sắp xếp các giá trị trong bảng dữ liệu từ trái qua phải để hàm cho kết quả phù hợp khi bạn dùng range_lookup là TRUE: Chọn vùng dữ liệu cần sắp xếp, kích vào menu Data, Sort. Nhấn nút Options bên dưới, đánh dấu Soft left to right, rồi nhấn OK. Kích chọn dòng cần sắp xếp trong danh sách. Chọn Ascending, và nhấn OK row_index_num số thứ tự dòng trên bảng dữ liệu phụ mà dữ liệu bạn cần lấy. Giá trị trả về nằm trên dòng bạn chỉ định này và ở cột mà hàm tìm thấy giá trị dò tìm lookup_value. range_lookup là giá trị logic bạn chỉ định muốn HLOOKUP tìm kiếm chính xác hay là tương đối. Nếu range_lookup là TRUE hàm sẽ trả về kết quả tìm kiếm tương đối. Nếu không tìm thấy kết quả chính xác, nó sẽ trả về một giá trị lớn nhất mà nhỏ hơn giá trị tìm kiếm lookup_value. Nếu range_lookup là FALSE hàm tìm kiếm chính xác, nếu không có trả về lỗi #N/A! Lưu ý! Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong dòng đầu tiên của bảng dữ liệu phụ, HLOOKUP trả về lỗi #N/A!. Khi xuất hiện lỗi #N/A! bạn có thể mắc lỗi nhập dư một khoảng trống ở phía sau giá trị dò tìm hoặc trong bảng dữ liệu, kể cả chính và phụ. Khi dùng hàm HLOOKUP để điền dữ liệu cho một bảng dữ liệu thì trong công thức cần phải tạo địa chỉ tuyệt đối cho bảng dữ liệu phụ table_array để công thức đúng cho các hàng còn lại khi bạn copy công thức xuống các ô bên dưới. Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 11 Ví dụ Để dễ hiểu hơn, bạn hãy nhập các công thức sau vào ô trống bất kỳ trong bảng tính. A B C D E 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 DANH MỤC HÀNG HÓA NHẬP KHẨU THÁNG 12 STT Mã hàng Thuế Ngày nhập Số lượng 1 HDD ? 01/12/2006 20 2 CPU ? 03/12/2006 50 3 CDR ? 05/12/2006 70 4 HDD ? 07/12/2006 100 5 CPU ? 08/12/2006 200 6 USB ? 07/12/2006 500 THUẾ NHẬP KHẨU MH HDD CPU CDR USB Thuế 5% 8% 7% 10% Công thức Giải thích =HLOOKUP(B3,$B$10:$E$11,2,0)Nhập công thức vào ô C3 để lấy mức thuế nhập khẩu tương ứng biểu thuế của từng mặt hàng bên dưới. Copy công thức xuống các ô còn lại. Lưu ý địa chỉ vùng ô của bảng dữ liệu phụ phải là địa chỉ tuyệt đối để khi copy công thức hàm HLookup mới đúng cho các ô tiếp theo.  Hàm MATCH: Công dụng Trả về vị trí (chỉ mục) của một giá trị từ một dãy giá trị. Công thức =MATCH(lookup_value, lookup_array,match_type) Viết cách khác: =MATCH(trị dò,dãy dò,cách dò) lookup_value là giá trị cần tìm trong bảng giá trị. lookup_array là một dãy ô liên tục để tìm kiếm giá trị. match_type là một số -1, 0 hoặc 1 chỉ định kiểu tìm kiếm.  match_type = 1 (hoặc không nhập) thì bắt buộc dãy giá trị lookup_array phải đựơc sắp xếp theo thứ tự tăng dần.  match_type = 0, dãy giá trị lookup_array không cần sắp xếp.  match_type = -1, thì bắt buộc dãy giá trị lookup_array phải được sắp xếp theo thứ tự giảm dần. Lưu ý! Nếu không tìm thấy giá trị cần tìm trong bảng giá trị, MATCH trả về lỗi #N/A. Nếu match_type = 0, và giá trị cần tìm là ký tự, thì bạn có thể dùng dấu sao (*) để đại diện cho nhiều ký tự, dùng dấu hỏi (?) để đại diện cho ký tự tại vị trí mà bạn đặt nó. Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 12  Hàm INDEX Trả về một giá trị hoặc một tham chiếu tới một giá trị của một bảng (hoặc một mảng). Hàm INDEX() có hai dạng: dạng MẢNG và dạng THAM CHIẾU. Dạng mảng luôn luôn trả về giá trị hoặc mảng các giá trị; Còn dạng tham chiếu luôn luôn trả về kết quả là một tham chiếu. INDEX DẠNG MẢNG (Array Form) Cú pháp: = INDEX(array, row_num, [column_num]) array : Là một dãy ô hoặc là một hằng mảng. Nếu array chỉ có một dòng hoặc một cột, thì row_num hay column_num tương ứng là tùy chọn. Nếu array có nhiều hơn một dòng hoặc nhiều hơn một cột, và chỉ có hoặc là row_num hoặc là column_num được sử dụng, INDEX() sẽ trả về toàn bộ dòng hay cột của array row_num : Dòng trong array để lấy giá trị trả về. Nếu bỏ qua row_num thì buộc phải có column_num. column_num : Cột trong array để lấy giá trị trả về. Nếu bỏ qua column_num thì buộc phải có row_num. Lưu ý:  Nếu hai đối số column_num và row_num đều sử dụng, INDEX() sẽ trả về giá trị của ô là giao điểm của column_num và row_num.  Nếu đặt row_num = 0, INDEX() sẽ trả về một mảng các giá trị là toàn bộ cột; và ngược lại, nếu đặt column_num = 0, INDEX() sẽ trả về một mảng các giá trị là toàn bộ dòng.  Để kết quả trả về là các giá trị dưới dạng mảng, cần nhập INDEX() theo dạng công thức mảng, mảng ngang cho dòng và mảng đứng cho cột.  column_num và row_num phải chỉ tới một ô trong mảng, nếu không, INDEX() sẽ báo lỗi #REF! Ví dụ: Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 13 INDEX DẠNG THAM CHIẾU (Reference Form) Cú pháp: = INDEX(reference, row_num, [column_num], area_num) reference : Là một tham chiếu tới một hoặc nhiều dãy ô. Nếu đang nhập một dãy ô gồm nhiều phần rời rạc cho tham chiếu, cần đặt dãy đó trong cặp dấu ngoặc đơn. Nếu mỗi vùng trong tham chiếu chỉ có một dòng hoặc một cột, đối số row_num hoặc column_num là tùy chọn, theo thứ tự này. Ví dụ, đối với tham chiếu chỉ gồm một dòng, dùng cú pháp: INDEX(reference, ,column_num) row_num : Là chỉ số Dòng trong tham chiếu. column_num : Là chỉ số Cột trong tham chiếu. area_num : Chọn một dãy trong tham chiếu để trả về giao điểm của column_num và row_num trong dãy đó. Vùng thứ nhất được đánh số là 1, vùng thứ hai là 2, v.v... Nếu bỏ qua area_num, INDEX sẽ mặc định dùng vùng 1. Lưu ý:  Sau khi reference và area_num đã chọn một dãy riêng biệt, row_num và column_num đã chọn ô riêng biệt: row_num 1 là dòng thứ nhất trong dãy, column_num 1 là cột thứ nhất trong dãy, v.v... tham chiếu được trả về bởi INDEX() là giao của dòng và cột.  Nếu đặt row_num = 0, INDEX() sẽ trả về tham chiếu cho toàn bộ cột; và ngược lại, nếu đặt column_num = 0, INDEX() sẽ trả về tham chiếu cho toàn bộ dòng.  column_num, row_num và area_num phải chỉ tới một ô bên trong tham chiếu, nếu không, INDEX() sẽ báo lỗi #REF! Nếu bỏ qua column_num và row_num, INDEX() sẽ trả về một vùng trong tham chiếu, được chỉ định bởi area_num.  Kết quả của INDEX() là một tham chiếu, nhưng tùy thuộc vào công thức sử dụng mà giá trị của INDEX() sẽ được sử dụng dưới dạng một tham chiếu hay một giá trị. Thực tế ta thường sử dụng cú pháp như sau: Cú pháp: = INDEX(reference, row_num, [column_num]) Hoặc viết lại như sau: = INDEX(khối dò, dòng dò, cột dò) Dòng dò thường kết hợp với hàm MATCH Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 14 Ví dụ: Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 15  Hàm SUM: Công dụng Tính tổng tất cả các số trong dãy số. Công thức =SUM(number1,number2,....) number1, number2 các số bạn muốn tính tổng. Lưu ý! Số, giá trị logic, hay chữ đại diện cho số mà bạn gõ trực tiếp vào công thức thì được tính. Công thức tham chiếu tới giá trị logic, text đại diện cho số thì giá trị đó được bỏ qua. Ví dụ Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào. A B C 1 2 3 4 5 6 Doanh thu tháng 12 Tên hàng Số tiền Ghi chú Monitor 15000000 CPU 20000000 CD-ROM 4000000 Công thức Giải thích =SUM(B3:B5) Trả về 39000000  Hàm COUNTA: Công dụng Đếm số ô có dữ liệu (không phải là ô rỗng) trong vùng tham chiếu. Công thức =COUNTA(value1,value2,...) value1,value2,... các vùng giá trị mà bạn muốn đếm Lưu ý! Để đếm số ô chứa dữ liệu kiểu số dùng hàm COUNT, để đếm ô rỗng dùng hàm COUNTBLANK. Ví dụ Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức bên dưới vào. A B C D E F 1 2 3 4 5 6 7 8 9 10 11 12 BẢNG ĐIỂM TỔNG KẾT STT Họ Tên Ngày sinh ĐTB Xếp loại 1 Nguyễn Văn Ánh 01/01/1990 6.7 2 Lê Văn Bình 10/05/1991 5.6 3 Nguyễn Hữu Chánh 06/05/1990 4.5 4 Phạm Hùng Dũng 02/07/1990 7.5 5 Huỳnh Tấn Dương 06/04/1991 8.0 6 Võ Thị Thu Hương 03/08/1990 6.5 7 Trần Thị Kim Kiều 08/12/1990 6.5 8 Võ Thị Thu Loan 06/11/1991 6.2 Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 16 Công thức Giải thích =COUNTA(B3:B12) Trả về 10. =COUNT(B3:B12) Trả về 0.  Hàm MAX: Công dụng Trả về giá trị lớn nhất của dãy số. Công thức =MAX(number1,number2,...) number1,number2,... có thể có từ 1 đến 30 số mà bạn muốn tìm số lớn nhất. Lưu ý! Các tham số có thể là số, ô rỗng, giá trị logic, hoặc chuỗi đại diện cho giá trị số. Nếu là giá trị lỗi, chuỗi không thể đổi thành số thì hàm trả về lỗi. Nếu các đối số là một mảng hoặc vùng tham chiếu thì hàm những ô trống, chứa giá trị logic, chuỗi, hay giá trị lỗi đều được bỏ qua. Để xét giá trị logic, chuỗi thì bạn phải dùng hàm MAXA. Nếu các đối số không phải là số, hàm trả về 0. Ví dụ Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức bên dưới vào. A B C D E F 1 2 3 4 5 6 7 8 9 10 11 12 BẢNG ĐIỂM TỔNG KẾT STT Họ Tên Ngày sinh ĐTB Xếp loại 1 Nguyễn Văn Ánh 01/01/1990 6.7 2 Lê Văn Bình 10/05/1991 5.6 3 Nguyễn Hữu Chánh 06/05/1990 4.5 4 Phạm Hùng Dũng 02/07/1990 7.5 5 Huỳnh Tấn Dương 06/04/1991 8.0 6 Võ Thị Thu Hương 03/08/1990 6.5 7 Trần Thị Kim Kiều 08/12/1990 6.5 8 Võ Thị Thu Loan 06/11/1991 6.2 Công thức Giải thích =MAX(E3:E12) Trả về 8 - ĐTB lớn nhất =MAX(D3:D12) Trả về 06/11/1991 - Ngày sinh lớn nhất (người nhỏ tuổi nhất)  Hàm MIN: Công dụng Trả về giá trị nhỏ nhất của dãy số. Công thức =MIN(number1,number2,...) number1,number2,... có thể có từ 1 đến 30 số mà bạn muốn tìm số nhỏ nhất. Lưu ý! Các tham số có thể là số, ô rỗng, giá trị logic, hoặc chuỗi đại diện cho giá trị số. Nếu là giá trị lỗi, chuỗi không thể đổi thành số thì hàm trả về lỗi. Nếu các đối số là một mảng hoặc vùng tham chiếu thì hàm những ô trống, chứa giá trị logic, chuỗi, hay giá trị lỗi đều được bỏ qua. Để xét giá trị logic, chuỗi thì bạn phải dùng Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 17 hàm MINA. Nếu các đối số không phải là số, hàm trả về 0. Ví dụ Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức bên dưới vào. A B C D E F 1 2 3 4 5 6 7 8 9 10 11 12 BẢNG ĐIỂM TỔNG KẾT STT Họ Tên Ngày sinh ĐTB Xếp loại 1 Nguyễn Văn Ánh 01/01/1990 6.7 2 Lê Văn Bình 10/05/1991 5.6 3 Nguyễn Hữu Chánh 06/05/1990 4.5 4 Phạm Hùng Dũng 02/07/1990 7.5 5 Huỳnh Tấn Dương 06/04/1991 8.0 6 Võ Thị Thu Hương 03/08/1990 6.5 7 Trần Thị Kim Kiều 08/12/1990 6.5 8 Võ Thị Thu Loan 06/11/1991 6.2 Công thức Giải thích =MIN(E3:E12) Trả về 4.5 - ĐTB nhỏ nhất =MIN(D3:D12) Trả về 01/01/1990 - Ngày sinh nhỏ nhất (người lớn tuổi nhất)  Hàm AVERAGE: Công dụng Tính trung bình cộng của các đối số. Công thức =AVERAGE(number1,number2,...) number1,number2 có thể có từ 1 đến 30 số mà bạn cần tính trung bình cộng. Lưu ý! Nếu đối số là tham số kiểu text, logic, ô rỗng thì giá trị đó được bỏ qua. Nếu là zero thì được tính. Ví dụ Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức bên dưới vào. A B C D E F 1 2 3 4 5 6 7 8 9 10 11 12 BẢNG ĐIỂM TỔNG KẾT STT Họ Tên Ngày sinh ĐTB Vị thứ 1 Nguyễn Văn Ánh 01/01/1990 6.7 2 Lê Văn Bình 10/05/1991 5.6 3 Nguyễn Hữu Chánh 06/05/1990 4.5 4 Phạm Hùng Dũng 02/07/1990 7.5 5 Huỳnh Tấn Dương 06/04/1991 8.0 6 Võ Thị Thu Hương 03/08/1990 6.5 7 Trần Thị Kim Kiều 08/12/1990 6.5 8 Võ Thị Thu Loan 06/11/1991 6.2 9 Đỗ Văn Minh 12/12/1990 5.6 10 Phan Thanh Quang 11/12/1990 4.5 Công thức Giải thích Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 18 =AVERAGE(E3:E12) Trả về 6.16 là điểm trung bình chung của cả lớp.  Hàm COUNTIF: Đếm số lượng các ô trong một vùng thỏa một điều kiện cho trước. Cú pháp: = COUNTIF(range, criteria) Range : Dãy các ô để đếm, có thể là ô chứa số, text, tên, mảng, hay tham chiếu đến các ô chứa số. Ô rỗng sẽ được bỏ qua. Criteria : Điều kiện để đếm. Có thể ở dạng số, biểu thức, hoặc text. Ví dụ, criteria có thể là 32, "32", "> 32", hoặc "apple", v.v... Lưu ý:  Có thể dùng các ký tự đại diện trong điều kiện: dấu ? đại diện cho một ký tự, dấu * đại diện cho nhiều ký tự (nếu như điều kiện là tìm những dấu ? hoặc *, thì gõ thêm dấu ~ ở trước dấu ? hay *)  Khi điều kiện để đếm là những ký tự, COUNTIF() không phân biệt chữ thường hay chữ hoa. Ví dụ 1: Có bảng tính như sau Đếm số ô có chữ "Táo" trong dãy A2:A5 COUNTIF(A2:A5, "Táo") = 2 Đếm số ô có chữ "Lê" trong dãy A2:A5 COUNTIF(A2:A5, A4) = 1 Đếm số ô có chữ "Táo" và số ô có chứa chữ "Lê" trong dãy A2:A5) COUNTIF(A2:A5, A4) + COUNTIF(A2:A5, A2) = 3 Đếm số ô có giá trị > 55 trong dãy B2:B5 COUNTIF(B2:B5, "> 55") = 2 Đếm số ô có giá trị khác 75 trong dãy B2:B5 COUNTIF(B2:B5, "" & B4) = 3 Đếm số ô có giá trị lớn hơn hoặc bằng 32, nhưng không lớn hơn 85, trong dãy B2:B5 COUNTIF(B2:B5, ">=32") - COUNTIF(B2:B5, ">85") = 3 Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 19 Ví dụ 2: Có bảng tính như sau Đếm số ô có 2 chữ cuối là "es" trong dãy A2:A7 COUNTIF(A2:A7, "*es") = 2 Đếm số ô có 2 chữ cuối là "es" trong dãy A2:B7 COUNTIF(A2:B7, "*es") = 6 Đếm số ô có 2 chữ cuối là "es" trong dãy A2:A7 mà có chính xác 7 chữ ký tự COUNTIF(A2:A7, "?????es") = 2 Đếm số ô có chữ (text) trong dãy A2:A7 COUNTIF(A2:A7, "*") = 4 Đếm số ô không chứa chữ (text) trong dãy A2:A7 COUNTIF(A2:A7, ""&"*") = 2 Tính tỷ lệ số ô chứa chữ "No" trong dãy ô B2:B7, bao gồm cả những ô rỗng COUNTIF(B2:B7, "No") / ROWS(B2:B7) = 33% Tính tỷ lệ số ô chứa chữ "Yes" trong dãy ô B2:B7, không tính những ô rỗng COUNTIF(B2:B7, "Yes") / (ROWS(B2:B7) - COUNTIF(B2:B7, "" & "*")) = 0.5 = 50%  Hàm SUMIF: Tính tổng các ô trong một vùng thỏa một điều kiện cho trước. Cú pháp: = SUMIF(range, criteria, sum_range) Range : Dãy các ô để tính tổng, có thể là ô chứa số, tên, mảng, hay tham chiếu đến các ô chứa số. Ô rỗng và ô chứa giá trị text sẽ được bỏ qua. Criteria : Điều kiện để tính tổng. Có thể ở dạng số, biểu thức, hoặc text. Ví dụ, criteria có thể là 32, "32", "> 32", hoặc "apple", v.v... Sum_range : Là vùng thực sự để tính tổng. Nếu bỏ qua, Excel sẽ coi như sum_range = range. Lưu ý:  Sum_range không nhất thiết phải cùng kích thước với range. Vùng thực sự để tính tổng được xác định bằng ô đầu tiên phía trên bên trái của sum_range, và bao gồm thêm những ô tương ứng với kích thước của range. Ví dụ: - Nếu Range là A1:A5, Sum_range là B1:B5, thì vùng thực sự để tính tổng là B1:B5 - Nếu Range là A1:A5, Sum_range là B1:B3, thì vùng thực sự để tính tổng là B1:B5 - Nếu Range là A1:B4, Sum_range là C1:D4, thì vùng thực sự để tính tổng là C1:D4 - Nếu Range là A1:B4, Sum_range là C1:D2, thì vùng thực sự để tính tổng là C1:D4 Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 20  Có thể dùng các ký tự đại diện trong điều kiện: dấu ? đại diện cho một ký tự, dấu * đại diện cho nhiều ký tự (nếu như điều kiện là tìm những dấu ? hoặc *, thì gõ thêm dấu ~ ở trước dấu ? hay *).  Khi điều kiện để tính tổng là những ký tự, SUMIF() không phân biệt chữ thường hay chữ hoa. Ví dụ: Có bảng tính như sau Tính tổng của những huê hồng mà có doanh thu > 160,000 ? = SUMIF(A2:A5, ">160000", B2:B5) = 63,000 Tính tổng của những doanh thu > 160,000 ? = SUMIF(A2:A5, ">160000") = 900,000 Tính tổng của những huê hồng mà có doanh thu = 300,000 ? = SUMIF(A2:A5, "=300000", B2:B3) = 21,000  Hàm DSUM: Cộng các số trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. Cú pháp: = DSUM(database, field, criteria)  Hàm DCOUNT: Đếm các ô chứa số liệu trong một cột của danh sách hay các trị của một cơ sở dữ liệu, theo một điều kiện được chỉ định. Trong hàm DCOUNT(), đối số field là tùy chọn. Nếu bỏ qua đối số này thì DCOUNT() sẽ đếm tất cả các mẩu tin trong cơ sở dữ liệu theo điều kiện được chỉ định. Cú pháp: = DCOUNT(database, [field], criteria)  Hàm DCOUNTA: Đếm các ô "không rỗng" trong một cột của danh sách hay các trị của một cơ sở dữ liệu, theo một điều kiện được chỉ định. Cú pháp: = DCOUNTA(database, [field], criteria)  Hàm DMAX: Trả về trị lớn nhất trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 21 Cú pháp: = DMAX(database, field, criteria)  Hàm DMIN: Trả về trị nhỏ nhất trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. Cú pháp: = DMIN(database, field, criteria)  Hàm DAVERAGE: Tính trung bình các giá trị trong một cột của danh sách hay các trị của một cơ sở dữ liệu, theo một điều kiện được chỉ định. Cú pháp: = DAVERAGE(database, field, criteria)  Hàm ROUND: Làm tròn giá trị số đến số chỉ định. Cú pháp: = ROUND(number, num_digits)  number: Con số sẽ làm tròn  num_digits: Là một số nguyên, vị trí chỉ định làm tròn num_digits > 0 : làm tròn đến số thập phân được chỉ định num_digits = 0 : làm tròn đến số nguyên gần nhất num_digits < 0 : làm tròn đến phần nguyên được chỉ định Ví dụ: Với con số 12345.54321 =ROUND(12345.54321,2) = 12345,54 =ROUND(12345.54321,0) = 12346 =ROUND(12345.54321,-2) = 12300  Hàm MOD: Dùng để lấy số dư của một phép chia Cú pháp: = MOD(number, divisor) number: Số bị chia divisor: Số chia Ví dụ: MOD(24, 10) = 4 (24 chia 10 được 2, dư 4)  Hàm INT và hàm TRUNC: Hai hàm này gần như giống nhau nếu như bạn muốn làm tròn một số thành một số nguyên. Cú pháp: = INT(number) Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 22 = TRUNC(number [, num_digits])  number: Số cần làm tròn  num_digits: Là một số nguyên, chỉ cách mà bạn muốn cắt bớt số num_digits > 0 : nếu number là một số thập phân, thì num_digits chỉ ra số con số thập phân mà bạn muốn giữ lại (sau dấu phẩy) num_digits = 0 hoặc không nhập: cắt bỏ hết phần thập phân của number (nếu có) num_digits < 0 : làm tròn number thành một số nguyên và làm tròn number sang trái thành một bội số của 10 (xem thêm ở ví dụ) Hàm INT() làm tròn một số tới số nguyên gần nhất Đối với số dương, hàm INT() và TRUNC() cho kết quả giống nhau (num_digits của TRUNC() = 0 hoặc không có), nhưng đối với số âm thì hai hàm này sẽ cho kết quả hoàn toàn khác nhau. Ví dụ: INT( 123.456) = 123 | TRUNC( 123.456) = 123 INT(-123.456) = -124 | TRUNC(-123.456) = -123 Khi num_digits khác 0, TRUNC() khác hẳn với ROUND() ở chỗ: ROUND() thì làm tròn, còn TRUNC() chỉ cắt bỏ bớt số chứ không làm tròn. Một số ví dụ về hàm TRUNC() Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 23 Buổi 05: Quản lý danh sách dữ liệu - Nhập dữ liệu bằng Userform (Menu Data/Form...) - Sắp xếp dữ liệu (Data/Sort...) - Trich lọc tự động (Data/Filter/AutoFilter) - Trích lọc nâng cao (Data/Filter/Advanced Filter…) - Thông báo lỗi khi nhập (Data/Validation...) - Tính lương dùng Goal Seek (Tools/Goal Seek…) Buổi 06: Tổng hợp số liệu và báo cáo - SubTotal (Data/SubTotals…) - Consolidate (Data/Consolidate…) - Pivot Table (Data/ PivotTable and PivotChart Report) - Freeze Panes (Window/Freeze Panes) Buổi 07: Trang trí định dạng bảng tính, dữ liệu và bảo vệ dữ liệu - Auto Format (Format/Auto Format…) - Style (Format/Style…) - Condition Formatting (Format/Condition Formatting…) - Paste Special (Edit/ Paste Special…) - Protect Sheet, Protect Workbook (Tools/Protection) Buổi 08, 09: Macro và lập trình VBA - Giới thiệu Macro, Record New Macro, sử dụng Macro - Giới thiệu ngôn ngữ Visual Basic Application (VBA) - Tạo lập 1 UserForm mẫu bằng VBA (Tools/Macro/Visual Basic Editor) - Viết hàm bằng VBA và tạo Add-in cho Excel (Tools/Add-ins..) Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 24 Chú ý: Để sử dụng được macro Ta phải chuyển chế độ an toàn xuống mức trung bình hoặc thấp. Menu Tool  Chọn Macros  chọn Security  chọn mức Medium (trung bình) hoặc low (thấp) Chấp nhận sử dụng macro khi mở file: Khi mở một file Excel có chứa Macro thì Excel sẽ báo cho bạn biết điều này để xem bạn có quen biết nó và cần sài nó hay kô * Ghi và thực hiện macro: Macro là gì? Macro là tập hợp một số các dòng lệnh. Bạn sử dụng chức năng Macro Recorder là một ý tưởng hay để từng bước thực hiện các công việc, nhất là lúc đầu tìm hiểu về macro. Excel đã hỗ trợ ghi lại (recorder) các công việc bạn đã thực hiện và chỉ không ghi lại khi bạn dừng ghi. Ví dụ, một ô (cell) được chọn (selected) ở hiện tại sẽ không được ghi cho đến khi bạn thực hiện công việc trong ô đó. Ngoài ra, Excel cũng không ghi lại các công việc khi đang sử dụng bảng điều khiển (dialog box) cho đến khi bạn ấn nút OK trên bảng điều khiển đó. Hình 1: Thực hiện ghi macro Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 25 Hình 2: Cửa sổ Record Macro Trong suốt thời gian ghi, macro đã được lưu lại với tên xác định trong module, module được tạo ra trong quá trình ghi và là một phần của Workbook. Marco được ghi lại có thể được lưu trong This Workbook (Workbook hiện hành), New Workbook (Workbook mới) hoặc trong Personal Macro Workbook (những macro sở hữu riêng). Những lệnh (code) được lưu trong Personal.xls, những macro sở hữu riêng đều sử dụng được khi bạn mở Excel ra. Các macro trong các Workbook khác nhau có thể sử dụng bất cứ lúc nào khi các Workbook đang mở (kể cả sử dụng chúng từ Workbook khác). Điều kiện để có thể tiến hành ghi macro: 1. Bảng tính Excel hiện hành (Activate Excel). 2. Sử dụng Workbook mới. Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 26 Ghi macro trong trường hợp sử dụng tham chiếu địa chỉ ô tuyệt đối: Bạn hãy ghi lại macro tại địa chỉ ô B3, B4 trình bày tên cty và địa chỉ như sau: (Chú ý đây là ghi macro Tiếng Việt có dấu nhé) Hình 3: Quá trình ghi 1. Trong Tools/Macro, chọn Record New Macro (hình 1). 2. Trong Macro name: gõ Ghi_Tieng_Viet để đặt tên macro đó (hình 2). 3. Chuyển sang Shortcut key: để trống (sẽ thực hiện sau). 4. Trong Store macro in: để mặc định là This Workbook. 5. Trong Description: bạn gõ nội dung sau: Địa chỉ bắt đầu ô B3 6. Bấm OK. 7. Thanh Stop Recording sẽ xuất hiện. Bạn có thể di chuyển nó đến vị trí khác nếu thấy cần thiết. 8. Trong thanh Stop Recording, ấn vào nút Relative Reference cho mờ đi (ghi ở chế độ tuyệt đối). 9. Trong Sheet1, bấm vào B3 và gõ tên Cty. Ô ở dưới gõ địa chỉ cty. 10. Cho toàn bộ các chữ đậm và nghiêng. 11. Bấm vào ô B5. 12. Trong Stop Recording, bấm vào nút Stop Recording. Đặc điểm là ký tự đầu tiên là của tên macro phải là chữ. Còn các ký tự khác có thể là chữ, số hoặc ký tự gạch dưới (ký tự _). Các ký tự đặc biệt như khoảng trống (Space), @, %, $, #, &, ... không được chấp nhận, bạn có thể dùng ký tự _ để tách tên trong macro. Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 27 Như vậy, macro có tên Ghi_Tieng_Viet đã được ghi lại. Những ô mà bạn đã sử dụng trong quá trình ghi được thể hiện dưới dạng địa chỉ tuyệt đối. Vì vậy, những ô trong Worksheet đó sẽ thực hiện khi bạn cho chạy macro, tên và địa chỉ cty sẽ được tạo ra đúng vị trí ô trong Worksheet. Ví dụ bạn gõ từ "Công Ty CP ĐT & PT Viễn Thông Miền Tây" (font Time New Roman) ngoài bảng tính và record thì ghi trong module như thế này: ActiveCell.FormulaR1C1 = "Công Ty CP ĐT & PT Vi?n Thông Mi?n Tây" (Để xem mã lệnh nhấn phím tắt Alt+F11 để mở VBA sẽ thấy mã trong module1) 2 ký tự ễ và ề bị chuyển thành ? vì 2 ký tự đó có mã >255 nên không nhập được trong cửa sổ soạn thảo. Đa số các ký tự riêng của tiếng Việt đều nằm trong nhóm này. Để khắc phục, bạn phải đi đường vòng. Dùng hàm ChrW(mã) để chuyển mã thành ký tự. Biết ễ có mã 7877, ề có mã 7873, câu trên viết lại theo kiểu ghép chuỗi: ActiveCell.FormulaR1C1 = "Công ty CP ÐT & PT Vi" & ChrW(7877) & "n Thông Mi" & ChrW(7873) & "n Tây" Làm sao biết ề có mã 7873 ? Bạn dùng hàm CodeUni nó sẽ cho bạn biết mã của ký tự đó. Để viết hàm này ta mở VBA ra rồi insert thêm module (Vào Menu Insert/Module…) vào rồi chèn đoạn code bên dưới vào là ta có thể dùng được. Mã: Function CodeUni(text As String) As Integer CodeUni = AscW(text) End Function Hình: Thực hiện lấy mã Unicode của các ký tự tiếng Việt Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 28 Hình: mã mới vừa ghi xong Hình: mã sau khi đã chỉnh sửa Chạy macro khi sử dụng bảng điều khiển macro (Macro dialog box) Bạn cho chạy macro trên từ Sheet2 như sau: 1. Chọn sang Sheet2 và bấm vào ô nào đó ngoài ô B3. 2. Trong menu Tools/Macro, chọn Macros (hình 1). 3. Bấm vào macro có tên Ghi_Tieng_Viet trong danh sách macro (hình 4). 4. Bấm vào nút Run. Sau đó bạn sẽ thấy nội dung ở Sheet2 giống như ở Sheet1. Ghi chú: Nếu bạn muốn huỷ quá trình chạy macro trước khi kết thúc, ấn vào nút Esc. Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 29 Hình 4: Chạy macro ở Sheet2 Dùng phím tắt để thực hiện một macro (shortcut key) 1. Tại Sheet2 bạn chọn vào 1 ô (ví dụ ô H14). 2. Ấn tổ hợp phím Ctrl+Shift+V. Khi đó tên và địa chỉ sẽ xuất hiện đúng ô B3 và B4. Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 30 Ghi macro trong trường hợp sử dụng tham chiếu địa chỉ ô tuyệt đối: Làm tương tự như trên nhưng ở bước 8 có sự thay đổi chút. 8. Trong thanh Stop Recording, ấn vào nút Relative Reference cho sáng lên (ghi ở chế độ tương đối). * Viết hàm bằng VBA: - Các kiểu dữ liệu: Byte Số từ 0 đến 255 1 Integer Số từ -32768 đến 32767 2 Long Số từ -2,147,483,648 đến 2,147, 483,647 4 Single Số từ -3.4x10 38 đến 3.4x10 38 4 Double Số từ -1.79x10 308 đến 1.79x10 308 8 Date Ngày từ 1/1/100 đến 31/12/9999 8 String Chuỗi độ dài từ 0 đến 65400 ký tự Tùy thuộc độ dài chuỗi Boolean Có hai giá trị là True và False 2 Variant Là kiểu mặc định chứa được kiểu dữ liệu khác 16 - Khai báo biến: Dim As Kiểu dữ liệu Ví dụ: Dim s As String -> Biến s dùng kiểu chuỗi String Dim x As Integer, y As Integer -> Biến x,y dùng kiểu số Integer - Các phép toán:  Phép gán:  Cú pháp: =  Ý nghĩa: Lệnh gán lấy giá trị bến phải đặt vào bên trái. Biến sẽ nhận được giá trị mới.  Ví dụ: x= 100 : gán giá trị 100 vào biến x Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 31  Các phép toán số học: +, -, *, /  Phép toán nối chuỗi: &  Các phép toán so sánh: >, =, Khi so sánh kết quả nhận được là giá trị True hoặc False  Phép toán luận lý: Not, And, Or Dùng để cho kết quả là True hoặc False khi ghép các phép toán so sánh lại với nhau - Các hàm điều kiện IF:  Cú pháp 1: IF điều kiện Then Lệnh X Ý nghĩa: Điều kiện là phép toán so sánh. Trong trường hợp có nhiều phép toán so sánh thì ta dùng các phép toán luận lý And, Or để xử lý. Nếu điều kiện là True thì chương trình sẽ thực thi lệnh X.  Cú pháp 2: IF điều kiện Then Lệnh 1 Lệnh 2 Lệnh … End IF Ý nghĩa: Nếu điều kiện có giá trị True thì thực hiện các lệnh. Lưu ý: Các lệnh phải viết xuống dòng bên dưới dòng IF … Then. Kết thúc cú pháp phải có End IF để báo hiệu kết thúc.  Cú pháp 3: IF điều kiện Then Lệnh 1 Lệnh 2 Lệnh … Else Lệnh A Lệnh B Lệnh … End IF Ý nghĩa: Nếu điều kiện có giá trị True thì thực hiện các lệnh 1, lệnh 2, … Ngược lại (điều kiện là False) thì thực hiện các lệnh A, lệnh B, … Lưu ý: Các lệnh phải viết xuống dòng bên dưới dòng IF … Then và dòng Else Kết thúc cú pháp phải có End IF để báo hiệu kết thúc. Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 32  Cú pháp 4: IF điều kiện 1 Then Lệnh 1 Lệnh 2 Lệnh … ElseIf điều kiện 2 Then Lệnh A Lệnh B Lệnh … Else Lệnh X Lệnh Y Lệnh … End IF Ý nghĩa: Cú pháp này dùng để giải quyết bài toán có nhiều điều kiện. Nếu điều kiện 1 có giá trị True thì thực hiện các lệnh 1, lệnh 2, … Ngược lại (điều kiện 1 là False) nếu điều kiện 2 có giá trị True thì thực hiện các lệnh A, lệnh B, … Ngược lại (điều kiện cuối cùng có giá trị False) thì thực hiện các lệnh X, lệnh Y, … Lưu ý: Các lệnh phải viết xuống dòng bên dưới dòng IF … Then và dòng Else Kết thúc cú pháp phải có End IF để báo hiệu kết thúc. - Viết hàm bằng VBA:  Hàm – Function: Hàm là một đoạn mã chương trình được gói gọn vào trong một khối. Đoạn mã chương trình này dùng để thực hiện một công việc nào đó. Khối chương trình này có đầu vào là các đối số và đầu ra là giá trị trả về của hàm.  Cú pháp hàm: Tên_Hàm(các_đối_số) Ví dụ: Left(“Hello”, 2): Đầu vào của hàm Left là chuỗi “Hello” và số 2. Đầu ra giá trị trả về là “He”. Left(S,n): S là một biến chứa chuỗi, n là một biến chứa một giá trị số. Chúng là các giá trị đầu vào.  Cú pháp khai báo khi tạo hàm: [Public]/[Private] Function Tên_Hàm(đối_số_1 As Kiểu dữ liệu, …) As kiểu dữ liệu trả về … End Function Ví dụ 1: Khai báo khi tạo hàm Left: Public Function Left(text As String, num_chars As Long) As String … End Function Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 33 Ví dụ 2: Viết hàm Max2So tiếp nhận 2 đối số. Hàm trả về giá trị lớn nhất. Public Function Max2So(a As Single, b As Single) As Single … End Function Hoặc ta có thể khai báo: Function Max2So(a,b) … End Function  Nơi viết hàm : - Thông thường, các hàm sẽ được chứa trong module của chương trình. Ta phải tạo đối tượng module trong cửa sổ chương trình để chứa các hàm. - Tại vùng Project Explorer cửa sổ Visual Basic Editor, chọn đối tượng ThisWorkbook. Vào menu Insert/Module. Khi ấy, ta thấy đối tượng module xuất hiện trong vùng Project Explorer. - Nhấp đúp vào đối tượng Module1, cửa sổ bên phải xuất hiện nội dung của module là nơi ta gõ các hàm vào.  Cấu trúc phần thân hàm: [Public]/[Private] Function Tên_Hàm(đối_sô_1 As kiểu dữ liệu,…) As kiểu dữ liệu trả về Vị trí thường khai báo biến Thân hàm, các lệnh tính toán xử lý công việc Tên_Hàm = Giá trị trả về End Function Ví dụ 1: Viết hàm Max2So như sau: Public Function Max2So(a,b) Dim max max = a If max < b Then max = b Max2So = max End Function Ví dụ 2: Viết hàm Max3So nhận đầu vào là 3 giá trị số thực, giá trị trả về của hàm trả về là giá trị lớn nhất trong 3 đối số. Public Function Max3So(a As Single, b As Single, c As Single) As Single Dim max As Single If a > b Then If a > c Then max = a Else max = c End If Else If b > c Then max = b Else max = c End If End If Max2So = max End Function Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 34 * Tạo Add-in cho Excel: Cách tạo add-in hàm tính thế thu nhập cá nhân (Hàm này ta có thể tự viết hoặc sưu tầm trên mạng về để dùng cho mục đích riêng). Để các hàm VB (user-defined) có thể dùng chung cho nhiều tập tin Excel, bạn chỉ cần gom chúng lại thành tập tin .XLA (Microsoft Excel Add-In). Cách làm như sau: 1. Khởi động Excel, tạo một workbook mới. Chọn Tools > Macro > Visual Basic Editor (Alt+F11) để mở cửa sổ soạn thảo VBA. Trong cửa sổ Project Explorer (nếu không thấy thì chọn View > Project Explorer), chọn VBAProject (Book1). Chọn Insert > Module để thêm một module vào workbook. 2. Gõ nội dung hàm CodeUni của bạn vào module mới tạo. Function CodeUni(text As String) As Integer CodeUni = AscW(text) End Function 3. Chọn Tools > Macro > Macros để hiển thị hộp thoại Macro. Gõ tên hàm CodeUni vào ô Macro name, bấm Options để mở tiếp hộp thoại Macro Options, gõ nội dung mô tả hàm vào ô Description, bấm OK để đóng hộp thoại Macro Options, bấm Cancel để đóng hộp thoại Macro. 4. Ghi workbook lên đĩa thành tập tin CodeUni.xla. Lưu ý: bạn phải đặt tập tin này đúng chỗ để Excel tự động nạp nó khi khởi động. Ví dụ, đối với Excel XP trên Windows XP, chọn thư mục C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns; Đối với Vista thì: UserName\AppData\Roaming\ Microsoft\AddIns. Đóng Excel mà không cần ghi lại các thay đổi trên Book1. Trong đó: Username là tên người sử dụng máy tính. Ghi chú: Có thể copy file CodeUni.xla vào một thư mục bất kỳ rồi khi browse thì chỉ đường dẫn đến vị trí của file CodeUni.xla 5. Khởi động lại Excel, cài đặt add-in vừa tạo cho Excel bằng cách chọn Tools > Add-Ins, chọn tên add-in cần cài đặt trong danh sách Add-Ins available, rồi bấm OK. Từ đây trở về sau, mỗi lần mở Excel, bạn đương nhiên sử dụng được hàm CodeUni() trong add-in CodeUni.xla. Buổi 10: Thi cuối khóa Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 35 Một số thủ thuật hay với Excel: Hiển thị đối số của các hàm: Để xem đối số trong một công thức, hãy nhấn Ctrl- Shift- A. Ví dụ: Nếu bạn gõ =RATE và nhấn Ctrl- Shift -A, bạn có thể nhìn thấy tất cả các đối số cho hàm này (ví dụ =RATE (nper, pmt, pv, fv, type, guess)). Nếu bạn muốn biết chi tiết hơn, gõ =RATE. Và nhấn Ctrl+A để hiển thị theo Function Wizard. Tham chiếu các hàm: Nếu bạn ko nhớ hết các hàm thì có thể mở Insert Funtion để xem trước hướng dẫn các hàm. Nhìn trên thanh công cụ có ký hiệu fx hoặc dùng phím tắt Shift-F3, Excel sẽ đưa ra hộp thoại chứa tất cả các hàm ứng dụng và hướng dẫn các cú pháp cụ thể cho từng hàm khi bạn di chuyển con trỏ điểm sáng đến hàm muốn tham chiếu. Đây cũng là cách nhập công thức nhanh mà không cần gõ toàn bộ cú pháp. Sử dụng ô tham chiếu và nhãn text trong các công thức: Để sử dụng các tham chiếu ô cùng với nhăn text trong một công thức, bạn chỉ việc gõ một ký hiệu (&) ở giữa tham chiếu và text. Ví dụ, để hiển thị dòng dữ liệu là "25 Departments", gõ (=A1 & "Departments"), trong đó A1 chứa số 25. Làm thế nào để ấn định một macro tới một nút? Bạn muốn chạy macro hay hàm chỉ bằng một thao tác nhắp vào một nút? Bằng cách gán một hàm hay một macro tới một nút tuỳ biến bạn có thể thực hiện rất nhanh chóng mà không phải chọn Tools/Macro. Để ấn định một macro tới một nút, chọn View/Toolbars/Customize. Nhắp vào tab Commands và chọn Macros trong điều khiển Categories. Nếu đã có sẵn ở ngoài thì bạn chọn View/Toolbars/Control Toolbox hoặc Click phải chuột lên thanh công cụ check vào Control Toolbox Tách Họ và Tên thành từng cột riêng biệt: Chọn vùng cần tách vào Menu Data/Text to Columns/chọn Delimited/chọn Space/Mục Destination chọn vùng cần tách/Chọn Finish. Hiển thị công thức một cách an toàn Bạn nhớ phải luôn luôn ghi bảng tính trước khi chuyển tới công thức hiển thị bằng cách ấn Ctrl - ~. Khi bạn quay lại bảng tính sau khi hiển thị các công thức, bạn có thể tìm thấy một vài định dạng khác nhau. Ví dụ, Excel có thể đã thay đổi độ rộng cột cùng một vài cột khác của bạn. Nếu điều này xảy ra, đơn giản mở lại bảng tính để quay lại định dạng đã mất. Phương pháp khác là chuyển bảng tính theo cách ở trên và tắt bằng cách vào menu Tools/Options chọn Views và đánh dấu chọn vào ô Formulas. Nhắp và OK để kết thúc. Sắp xếp các ký tự in hoa và in thường Excel không quan tâm đến trường hợp các ký tự chữ cái khi sắp xếp, trừ khi bạn thông báo tới nó. Nếu bạn muốn phân biệt giữa chữ in hoa và in thường trong quá trình sắp xếp, chọn dữ liệu bạn muốn sắp xếp, chọn Data/Sort. Nhắp vào Options trong hộp thoại Sort và chọn lựa chọn Case Sensitive. Nhắp vào OK hai lần. Bạn sẽ phải nhớ để chọn lựa chọn này mỗi lần vì Excel sẽ không giữ lại thiết đặt này. Chuyển đổi các nhăn tới các số Text dạng số là một kiểu dữ liệu hợp lệ. Ví dụ một số phone không hoàn toàn là một số. Khi lưu trữ kiểu dữ liệu này, bạn có thể thêm vào dữ liệu nhập vào bằng ký tự ('). Tuy nhiên, thỉnh thoảng các giá trị số được nhập vào được nhập như là text số và điều này có thể xảy ra rắc rối. May thay, có thể dễ dàng chuyển đổi text số tới dạng số. Bạn theo các bước sau: chọn các ô dữ liệu bạn muốn chuyển đổi. Chọn Data\ Text To Column. Nhắp vào Next hai lần. Chọn General format và nhắp vào Finish. Excel sẽ chuyển đổi text số thành dữ liệu số. Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 36 Xóa mật khẩu Protect Sheet của Excel Như đã biết Excel có 1 chức năng khóa 1 sheet bất kỳ bằng passwords( menu Tool/ Option/ Protection/ Protect sheet) Khi 1 sheet đã bị khóa, bạn ko thể modify( chèn thêm nội dung, chèn cột, địnnh dạng ô...). Thế nhưng bạn quên passwords thì sao? Đây là giải pháp: 1. Nhấn chuột phải vào tên sheet cần gỡ bỏ passwords, chọn View Code.....sẽ hiện ra 1 của sổ nhập liệu. 2. Copy đoan mã bên dưới và paste vào ô nhập liệu trên. Sub PasswordBreaker() Dim i As Integer, j As Integer, k As Integer Dim l As Integer, m As Integer, n As Integer Dim i1 As Integer, i2 As Integer, i3 As Integer Dim i4 As Integer, i5 As Integer, i6 As Integer On Error Resume Next For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If ActiveSheet.ProtectContents = False Then ActiveWorkbook.Sheets(1).Select Range("a1").FormulaR1C1 = Chr(i) & Chr(j) & _ Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _ Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) Exit Sub End If Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next End Sub 3. Vào menu Run> Run sub/User form và chờ giây lát. 4. Vào menu File> Close and return. Bây giờ bạn có thể modify thoải mái, passwords đã bị gỡ bỏ. Tạo siêu liên kết các Sheet tự động Khi làm việc với một tập tin Excel có nhiều Sheet, các bạn sẽ gặp nhiều khó khăn khi di chuyển giữa các bảng tính này. Giải pháp: Chúng ta sẽ tạo ra một Sheet mới với tên là MụcLục. Sau đó thêm một đoạn mã vào Sheet này để tạo mục lục tự động. Các bước thực hiện: 1. Trở về Sheet đầu tiên. Vào Insert > Worksheet. Đặt tên của Sheet này lại thành MụcLục hay để nguyên tùy thích. 2. Right-click lên thẻ của Sheet này chọn View Code. 3. Copy tất cả đoạn mã này vào: Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn Nguyễn Thanh Long - Excel Nâng cao Trang 37 Private Sub Worksheet_Activate() Dim wSheet As Worksheet Dim M As Long M = 1 With Me .Columns(1).ClearContents .Cells(1, 1) = "INDEX" .Cells(1, 1).Name = "Index" End With For Each wSheet In Worksheets If wSheet.Name Me.Name Then M = M + 1 With wSheet .Range("H1").Name = "Start" & wSheet.Index .Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Ind ex", TextToDisplay:="Back to Index" End With Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", SubAddress:= "Start" & wSheet.Index, TextToDisplay:=wSheet.Name End If Next wSheet End Sub 4. Nhấn Alt + Q và Save lại. 5. Từ Excel, vào Tool > Macro > Security. Trong hộp thoại này, chọn mức Low. Vì trong file Excel của bạn có dùng Macro, nên đôi khi mức Macro security ở mức high, đoạn lệnh của bạn sẽ không có tác dụng. Nếu bạn chọn Medium, bạn sẽ được nhắc là chạy macro không. Trong trường hợp này, bạn chọn Enable Macro để thấy tác dụng. 5. Nhấn Ctrl + W để đóng lại Workbook và mở lại Workbook này. Đến đây, khi nhấp vào Sheet có tên là MụcLục, bạn sẽ thấy danh sách các bảng tính hiện ra. Nhấp vào Sheet nào mà bạn muốn di chuyển nhanh đến để làm việc. Cách tính thâm niên làm việc của nhân viên Để tính chính xác số tháng và năm làm việc thì ta phải sử dụng hàm sau: = DATEDIF (start_date, end_date [, unit]) Ví dụ 1: công thức sau đây tính số ngày từ hôm nay đến lễ Giáng Sinh: = DATEDIF(TODAY(), DATE(YEAR(TODAY()), 12, 25), "d") Hàm DATEDIF() có thể đơn giản hóa đáng kể công thức tính tuổi của một người. Nếu ngày tháng năm sinh của một người nằm trong một ô có tên là Birthday, công thức sau đây tính tuổi chính xác của người đó: = DATEDIF(Birthdate, TODAY(), "y") Ví dụ 2: Tính thâm niên của một người bắt đầu làm việc từ 27/07/2006 đến hiện tại Cách 1: = YEAR(TODAY()) – YEAR(“27/07/2006”) = 3 (Tính bằng cách này thì nó tự làm tròn là 3 năm nhưng thực tế còn thiếu 4 tháng mới đủ 3 năm) Cách 2: Chính xác số tháng và năm làm việc là 2 năm 8 tháng = DATEDIF(“27/07/2006”,TODAY(),”y”) = 2 năm = DATEDIF(“27/07/2006”,TODAY(),”ym”) = 8 tháng