Học Excel - Thủ Thuật Excel: Thiết lập các công thức nâng cao (phần 2)

Học Excel - Thủ Thuật Excel Thiết lập các công thức nâng cao (phần 2) Hướng dẫn thiết lập các công thức nâng cao (phần 2) : 4.4. Áp dụng các quy tắc Data-Validation cho các ô Có một điều không may khi làm việc với các bảng tính là các công thức của bạn chỉ hoạt động theo những dữ liệu mà chúng nhận được. Đó là hiệu ứng GIGO, như các nhà lập trình thường nói,garbage in, garbage out (nếu ta nạp dữ liệu vào máy sai thì ta cũng thu được kết quả sai). Đối với những lỗi dữ liệu cơ bản (ví dụ như nhập sai ngày tháng, nhập sai số), bạn không thể làm gì khác ngoài việc tự cổ vũ chính mình và những người sử dụng các bảng tính của bạn là phải nhập dữ liệu cách cẩn thận! Nhưng cũng còn chút may mắn là bạn có một số khả năng kiểm soát để ngăn chận việc nhập dữ liệu không đúng. Không đúng ở đây có nghĩa là dữ liệu rơi vào một trong hai loại sau: · Dữ liệu có kiểu nhập không đúng — ví dụ, nhập một chuỗi dạng văn bản vào trong một ô đòi hỏi phải nhập vào một con số. · Dữ liệu nằm ngoài một giới hạn cho phép — ví dụ, nhập 200 vào một ô mà ô này chỉ cho phép nhập trong khoảng từ 1 đến 100. Bạn có ngăn chận những loại dữ liệu nhập không đúng này trong một phạm vi nhất định bằng cách thêm các chú thích nhằm cung cấp chi tiết về những gì có thể được phép nhập vào trong một ô cụ thể. Tuy nhiên điều này đòi hỏi những người khác phải vừa đọc vừa làm theo những chú thích của bạn. Một cách khác là sử dụng một số kiểu định dạng số để “định dạng” một ô với một thông báo lỗi sẽ xuất hiện nếu như dữ liệu nhập vào không đúng. Điều này thì hữu dụng, nhưng chỉ có tác dụng với một số lỗi nhập liệu nhất định nào đó thôi. Giải pháp tốt nhất để ngăn ngừa các lỗi nhập liệu là sử dụng tính năng data- validation của Excel. Với data-validation, bạn có thể tạo ra những quy tắc nhằm xác định chính xác loại dữ liệu nào có thể được nhập và những giới hạn cho loại dữ liệu đó. Bạn cũng có thể đưa ra những thông báo sẽ xuất hiện khi có một ô được chọn, hay khi nhập liệu không đúng (với những quy tắc bạn đã đưa ra). Để thiết lập các quy tắc data-validation, bạn theo các bước sau: 1. Chọn ô hay dãy ô mà bạn muốn áp dụng quy tắc data-validation. 2. Chọn Data, Data Validation. Excel mở ra hộp thoại Data Validation như minh họa ở hình 4.14. Figure 4.14 (Hình 4.14) 3. Trong tab Settings, sử dụng một trong các quy tắc dưới đây của danh sách Allow: o Chấp nhận bất kỳ loại giá trị nào nhập vào trong dãy. (Điều này cũng có nghĩa là gỡ bỏ quy tắc đã được thiết lập trước đây. Khi bạn gỡ bỏ một quy tắc hiện có, bạn hãy nhớ xóa luôn cả thông báo lỗi, nếu bạn đã tạo ra nó như cách làm ở bước 7 dưới đây.) o Chỉ cho phép nhập số nguyên vào dãy. Sử dụng thêm danh sách Data để chọn một loại toán tử so sánh (nằm trong khoảng, bằng, tối thiểu, v.v…), và nhập một tiêu chuẩn cụ thể. (Ví dụ, nếu bạn chọn Between (nằm trong khoảng), bạn phải nhập một giá trị Minimum và một giá trị Maximum). o Chỉ cho phép nhập giá trị là số (số thập phân hoặc số nguyên). Sử dụng thêm danh sách Datađể chọn một loại toán tử so sánh, và nhập một tiêu chuẩn cụ thể. o Chỉ cho phép nhập các giá trị được xác định trong danh sách kèm theo. Dùng hộp Source để xác định một dãy trong cùng một sheet hoặc một tên dãy trong bất kỳ sheet nào có chứa danh sách các giá trị được phép nhập. (Đặt một dấu bằng trước dãy hoặc tên dãy). Hoặc, bạn có thể nhập trực tiếp các giá trị này vào trong hộp Source (cách nhau bằng dấu phẩy). Nếu bạn muốn người dùng có thể chọn những giá trị này từ một danh sách xổ xuống (drop-down list), bạn kích hoạt tùy chọn In-Cell Drop-Down. o Chỉ cho phép nhập các giá trị ngày tháng. (Nếu người sử dụng nhập vào các giá trị thời gian, thì các giá trị này sẽ không hợp lệ). Sử dụng thêm danh sách Data để chọn một loại toán tử so sánh, và nhập một tiêu chuẩn ngày tháng cụ thể (chẳng hạn như ngày bắt đầu và ngày kết thúc). o Chỉ cho phép nhập các giá trị thời gian. (Nếu người sử dụng nhập vào các giá trị ngày tháng, thì các giá trị này sẽ không hợp lệ). Sử dụng thêm danh sách Data để chọn một loại toán tử so sánh, và nhập một tiêu chuẩn thời gian cụ thể (chẳng hạn như thời gian bắt đầu và thời gian kết thúc). o Chỉ cho phép nhập các chuỗi text với chiều dài được chỉ định. Sử dụng thêm danh sách Datađể chọn một loại toán tử so sánh, và nhập một tiêu chuẩn độ dài chuỗi cụ thể (chẳng hạn như độ dài tối thiểu và tối đa của chuỗi text). o Dùng tùy chọn này để nhập một công thức xác định một điều kiện nào đó. Bạn có thể nhập trực tiếp công thức vào hộp Formula (phải chắc chắn rằng có dấu bằng ở trước công thức), hoặc nhập một tham chiếu dẫn tới một ô chứa công thức. Ví dụ, nếu bạn giới hạn ô A2 và bạn muốn chắc chắn rằng giá trị nhập vào sẽ luôn khác giá trị trong ô A1, bạn gõ công thức: = A2A1. 4. Để bao gồm cả những giá trị rỗng, trong chính ô đó hoặc trong những ô là một phần của những thiết lập validation, bạn kích hoạt tùy chọn Ignore Blank. Nếu bạn không kích hoạt tùy chọn này, Excel sẽ xem các ô rỗng có giá trị là zero và áp dụng những quy tắc validation phù hợp với chúng. 5. Nếu dãy đã có một quy tắc validation đang áp dụng cho những ô khác, bạn có thể áp dụng luôn quy tắc mới vào các ô khác đó bằng cách kích hoạt tùy chọn Apply These Changes to All Other Cells with the Same Settings. 6. Chọn tab Input Message. 7. Nếu bạn muốn có một thông báo xuất hiện khi người dùng chọn ô được giới hạn hay bất kỳ ô nào trong dãy được giới hạn, bạn kích hoạt tùy chọn Show Input Message When Cell Is Selected. Xác định nội dung của thông báo trong khung Title and Input Message. Ví dụ, bạn có thể cung cấp cho người dùng biết thông tin về loại và khoảng giá trị được phép nhập vào. 8. Chọn tab Error Alert. 9. Nếu bạn muốn có một hộp thoại xuất hiện khi người dùng nhập những dữ liệu không hợp lệ, kích hoạt tùy chọn Show Error Alert After Invalid Data Is Entered. Trong danh sách Style, chọn kiểu báo lỗi mà bạn muốn xuất hiện: Stop, Warning, hoặc Information. Xác định nội dung của thông báo trong khung Title and Input Message. Chú ý: Chỉ có loại Stop mới có thể ngăn cản người dùng cố tình bỏ qua lỗi và nhập dữ liệu không hợp lệ bằng mọi giá. 10. Nhấn OK để áp dụng quy tắc Data-validation. 4.5. Sử dụng các nút điều khiển trong một bảng tính Trong phần trước, bạn đã thấy việc chọn List trong các loại quy tắc Validation đã cung cấp cho chính bạn hoặc cho người sử dụng chọn ra một giá trị hợp lệ trong một danh sách xổ xuống. Nếu như bạn không chắc chắn lắm giá trị nào mới là hợp lệ, để nhập vào, thì đây mà một phương pháp nhập liệu tốt. Một trong những tính năng hay nhất của Excel là nó cho phép bạn mở rộng ý tưởng này, và đặt không chỉ các danh sách mà còn có thêm các công cụ điều khiển (dialog box control) khác chẳng hạn như các spinner (cái nút nhỏ làm tăng hoặc giảm giá trị lên xuống từng nấc một) hay là các check box (cái nút vuông để đánh dấu các lựa chọn) trực tiếp ngay trên bảng tính. Sau đó, bạn có thể liên kết các giá trị được trả về từ những cái nút điều khiển này đến một ô, để tạo ra một phương pháp nhập liệu khá tốt. 4.5.1. Sử dụng Form Controls Trước khi bạn có thể làm việc với các công cụ điều khiển, bạn cần hiển thị tab Develope của thanh Ribbon (tab này, theo mặc định khi cài Excel 2007 vào máy, sẽ không hiển thị ra): 1. Chọn Office, Excel Options để mở hộp thoại Excel Options. 2. Chọn Popular. 3. Nhấn chuột để kích hoạt tùy chọn Show Developer Tab in the Ribbon. 4. Nhấn OK. Bạn thêm vào bảng tính các công cụ điều khiển bằng cách chọn tab Develope, Insert, và rồi chọn các công cụ từ bảng Form Controls như minh họa ở hình 4.15. Chỉ có một số công cụ là có sẵn cho công việc với bảng tính. Tôi sẽ nói chi tiết về các công cụ điều khiển khác trong phần sau. Figure 4.15 (hình 4.15) 4.5.2. Thêm công cụ điều khiển vào một bảng tính Bạn thêm các công cụ điều khiển vào trong một bảng tính theo các bước tương tự như cách mà bạn thường sử dụng để tạo các đối tượng đồ họa. Đây là các thủ tục cơ bản: 1. Chọn Developer, Insert và nhấp vào công cụ mà bạn muốn tạo. Khi đó con trỏ chuột sẽ có dạng là dấu chữ thập. 2. Di chuyển con trỏ trên bảng tính đến điểm mà bạn muốn xuất hiện công cụ điều khiển. 3. Nhấn và rê chuột để tạo ra công cụ điều khiển. Theo mặc định, Excel gán một chú thích vào các Group Boxes (hộp nhóm), Check Boxes (hộp kiểm), và Option Buttons (nút tùy chọn). Để sửa lại nội dung các chú thích này, bạn có hai cách để bắt đầu: · Nhấp phải chuột vào công cụ điều khiển và chọn Edit Text. · Nhấn giữ phím Ctrl và nhấp chuột vào công cụ điều khiển để chọn nó. Rồi nhấn thêm một cái vào bên trong công cụ điều khiển. Sửa lại nội dung (chú thích) cho phù hợp. Khi đã xong, nhấp chuột ra bên ngoài công cụ điều khiển. 4.5.3. Liên kết một công cụ điều khiển với giá trị trong một ô Để sử dụng các công cụ điều khiển cho việc nhập dữ liệu, bạn cần phải kết hợp mỗi công cụ điều khiển với một ô trong bảng tính. Thủ tục sau đây hướng dẫn cho bạn cách thực hiện điều này: 1. Chọn công cụ điều khiển mà bạn muốn làm việc với nó. (Nhắc lại, để chọn một công cụ điều khiển, bạn hãy nhớ nhấn và giữ phím Ctrl trước khi bạn nhấp chuột vào nó). 2. Nhấp phải chuột vào công cụ và chọn Format Control (hay là nhấn Ctrl+1) để hiển thị hộp thoại Format Control. 3. Chọn tab Control và nhập tham chiếu ô vào trong khung Cell Link. Bạn có thể nhập tham chiếu này bằng tay hoặc chọn nó trực tiếp trên bảng tính. 4. Nhấn OK để quay về bảng tính. TIP: Một cách khác để tạo liên kết cho một công cụ điều khiển tới một ô là chọn công cụ đó và nhập một công thức ở trên thanh formula với dạng = cell. Ở đây, cell là tham chiếu đến ô mà bạn muốn liên kết với công cụ đó. Ví dụ, để liên kết một công cụ điều khiển với ô A1, bạn nhập công thức = A1. 4.5.4. Tìm hiểu các công cụ điều khiển bảng tính Để tận dụng tối đa khả năng của các công cụ điều khiển bảng tính, bạn cần biết những điểm riêng biệt về cách hoạt động của mỗi công cụ và cách bạn có thể sử dụng mỗi công cụ cho việc nhập dữ liệu. Các mục tiếp theo đây sẽ trình bày chi tiết mỗi công cụ điều khiển. ■ Group Boxes Bản thân các Group Box (hộp nhóm) không làm được gì nhiều. Thay vào đó, bạn sử dụng chúng để tạo một nhóm có hai hoặc nhiều nút tùy chọn (option button). Sau đó người dùng chỉ chọn được một tùy chọn trong nhóm. Để tạo các Group Box, bạn theo các bước sau đây: 1. Chọn Developer, Insert, Group Box trong danh sách Form Controls. 2. Nhấn và rê chuột để vẽ một Group Box trong bảng tính. 3. Chọn Developer, Insert, Option Button trong danh sách Form Controls. 4. Nhấn và rê chuột bên trong Group Box để tạo một Option Button. 5. Lập lại bước 3 và 4 để tạo thêm những Option Button khác. Luôn nhớ rằng, điều quan trọng là tạo Group Box trước, và rồi vẽ những cái Option Button của bạn ở bên trong Group Box. NOTE: Nếu bạn có một (và chỉ một) cái Option Button nằm ngoài một nhóm, bạn vẫn có thể bao gồm nó vào trong một cái Group Box. (Nếu bạn có nhiều cái Option Button nằm ngoài một Group Box, thì không thể làm được). Để làm điều này, đầu tiên bạn nhấn giữ phím Ctrl và nhấn vào cái Option Button để chọn nó. Rồi thả phím Ctrl ra, nhấn và rê một mép của cái Option Button vào bên trong Group Box. ■ Option Button Option button (các nút tùy chọn) là những công cụ điều khiển thường xuất hiện với một nhóm gồm 2 hoặc nhiều cái, và người sử dụng chỉ có thể kích hoạt được 1 cái trong nhóm đó (nghĩa là chỉ được phép chọn một tùy chọn trong nhiều tùy chọn). Như tôi đã nói trong phần trước, nút tùy chọn làm việc cùng với Group Box và người dùng chỉ có thể kích hoạt được một tùy chọn trong Group Box mà thôi. Theo mặc định, Excel vẽ mỗi cái nút tùy chọn trong trạng thái không được chọn (unchecked). Do đó, bạn nên xác định trước nút nào trong số các nút tùy chọn là nút được chọn sẵn (checked): 1. Nhấn giữ phím Ctrl và nhấp chuột vào nút tùy chọn mà bạn muốn hiển thị nó trong trạng thái được chọn. 2. Nhấn phải chuột vào nút này và chọn Format Control (hoặc nhấn Ctrl+1) để hiển thị hộp thoạiFormat Control. 3. Trong tab Control, kích hoạt tùy chọn Checked. 4. Nhấn OK. Trong bảng tính, việc kích hoạt một nút tùy chọn cụ thể sẽ thay đổi giá trị được lưu trữ trong ô liên kết. Giá trị được lưu trữ phụ thuộc vào nút tùy chọn, cụ thể là nút đầu tiên sẽ có giá trị là 1, nút thứ hai có giá trị là 2, v.v… Ưu điểm của điều này là cho phép bạn chuyển đổi một tùy chọn ở dạng text thành một giá trị số. Ví dụ, hình 4.16 minh họa một bảng tính có các nút tùy chọn cho người dùng lựa chọn 3 hình thức vận chuyển: Surface Mail, Air Mail, và Courier. Giá trị của tùy chọn được lưu trữ trong ô được liên kết, là ô E4. Ví dụ, nếu Air Mail được chọn, thì ô E4 sẽ có giá trị là 2. Figure 4.16 (hình 4.16) ■ Check Box Các Check Box (hộp kiểm) cho phép bạn đưa ra các tùy chọn mà người dùng có thể bật hoặc tắt (chọn hay không chọn). Giống như các Option Button, Excel vẽ các Check Box với trạng thái mặc định là không được chọn. Nếu bạn muốn một Check Box cụ thể nào đó xuất hiện với trạng thái ban đầu là được chọn, bạn dùng hộp thoại Format Control để kích hoạt tùy chọn Checked như đã nói trong bài trước. Trên bảng tính, một Check Box được chọn sẽ lưu giữ giá trị TRUE trong ô liên kết với nó; nếu Check Box bị xóa (không được chọn) thì nó lưu giữ giá trị FALSE (xem hình 4.17). Điều này cho phép bạn thêm một chút logic vào trong các công thức của mình. Nghĩa là, bạn có thể thử xem Check Box có được kích hoạt hay không và điều chỉnh công thức cho phù hợp. Hình 4.17 minh họa hai ví dụ: Figure 4.17 (hình 4.17)  Use End-Of-Period Payments —Check Box này có thể được dùng để quyết định xem trong công thức tính các khoản thanh toán hằng tháng cho một khoản vay, thì những khoản thanh toán đó được thực hiện ở cuối mỗi kỳ hạn (TRUE) hay ở đầu mỗi kỳ hạn (FALSE).  Include Extra Monthly Payments —Check Box này có thể dùng để xác định xem khi lập công thức để tính toán một kế hoạch trả nợ, có bao gồm khoản thanh toán lãi + gốc hằng tháng hay không. Trong cả hai trường hợp, và trong hầu hết các công thức có dùng kết quả của Check Box (ở ô liên kết), bạn nên sử dụng hàm IF() để đọc giá trị hiện hành ở ô liên kết (với Check Box) và phân nhánh (chọn đối số trong hàm IF) cho phù hợp. ■ List Box & Combo Box Công cụ List Box tạo ra một danh sách mà từ đó người dùng có thể chọn ra một mục. Các mục trong danh sách này được định nghĩa bởi những giá trị trong một dãy ô đã được xác định trong bảng tính, và giá trị trả về tại ô liên kết là số thứ tự của mục được chọn trong danh sách. Combo Box cũng tương tự như List Box, cái khác là công cụ này chỉ hiện ra mỗi lần một mục trong danh sách, muốn xem những mục khác thì bạn phải nhấn cho nó mở ra. List Box và Combo Box khác với những công cụ điều khiển khác, bởi vì bạn phải xác định một dãy chứa các mục xuất hiện trong danh sách. Những bước sau đây hướng dẫn bạn cách làm điều đó: 1. Nhập danh sách các mục trong một dãy. (Các mục này phải được liệt kê trong một hàng đơn hoặc một cột đơn.) 2. Thêm một List Box hoặc một Combo Box vào trang tính (nếu bạn chưa làm điều này), và rồi chọn nó. 3. Nhấn phải chuột vào công cụ và nhấn Format Control (hoặc nhấn Ctrl+1) để mở hộp thoạiFormat Control. 4. Chọn tab Control, rồi nhập tham chiếu của dãy chứa các mục trong danh sách vào khungInput Range. Bạn có thể nhập tham chiếu bằng tay hoặc chọn trực tiếp nó trên bảng tính. 5. . Nhấn OK để trở về bảng tính. Hình 4.18 minh họa một bảng tính với một List Box và một Combo Box (hoặc có thể gọi là mộtDrop-down List – danh sách xổ xuống) Figure 4.18 (hình 4.18) Cả hai công cụ đều dùng danh sách ở dãy A3:A10. Chú ý rằng, ô được liên kết hiển thị con số thứ tự của mục chọn trong danh sách chứ không phải là chính mục chọn đó. Để có được (tên của) mục chọn này, bạn có thể sử dụng Hàm INDEX(). Ví dụ, để tìm mục đang được chọn trong Combo Box ở hình 4.18, bạn dùng công thức sau đây (như minh họa ở ô E12): =INDEX(A3:A10,E10) ■ Scroll Bar & Spin Box Công tụ Scroll Bar giống như một thanh cuộn của một cửa sổ. Bạn dùng Scroll Bar để chọn một con số từ một dãy các giá trị. Nhấp chuột vào các mũi tên (ở hai đầu) hoặc rê hộp cuộn (cái nút hình vuông ở giữa Scroll Bar) sẽ thay đổi giá trị của nó. Giá trị này là kết quả trả về ở ô liên kết. Bạn có thể tạo một thanh cuộn ngang hoặc một thanh cuộn dọc. Trong hộp thoại Format Control của một Scroll Bar, tab Control có những tùy chọn sau đây: · Current Value —Giá trị ban đầu của Scroll Bar. · Minimum Value —Giá trị của Scroll Bar khi hộp cuộn nằm ở vị trí tận cùng bên trái (của thanh cuộn ngang) hoặc ở vị trí trên cùng (của thanh cuộn dọc). · Maximum Value —Giá trị của Scroll Bar khi hộp cuộn nằm ở vị trí tận cùng bên phải (của thanh cuộn ngang) hoặc ở vị trí dưới cùng (của thanh cuộn dọc). · Incremental Change —Lượng giá trị thay đổi trên Scroll Bar khi người dùng nhấp (một cái) vào các mũi tên của Scroll Bar. · Page Change —Lượng giá trị thay đổi trên Scroll Bar khi người dùng nhấp vào vị trí giữa cái mũi tên và cái hộp cuộn của Scroll Bar. Cong cụ Spin Box cũng tương tự như Scroll Bar, nghĩa là, bạn có thể dùng một Spin Box để chọn một con số giữa giá trị lớn nhấn và giá trị nhỏ nhất bằng cách nhấp vào các mũi tên. Con số được trả về ở ô liên kết. Các tùy chọn của Spin Box giống hệt các tùy chọn của Scroll Bar, ngoại trừ việc bạn không thể đặt giá trị Page Change. Hình 4.19 minh họa một ví dụ về Scroll Bar và Spin Box. Chú ý rằng con số ở trên Scroll Bar cho biết giá trị lớn nhất và nhỏ nhất (số 0 và số 100) là những con số mà tôi nhập vào bằng tay (trong ô C4). Đây là một sáng kiến hay bởi vì nó cho người dùng biết giá trị giới hạn của công cụ. Figure 4.19 (hình 4.19)