VALIDATION (Ràng buộc dữ liệu) – TẠO LIST ĐỘNG - CONDITIONAL FORMATTING (Tô màu)

I. Validation (Ràng buộc dữ liệu)

• Khi xây dựng bảng tính trong Excel, một số trường hợp yêu cầu nhập liệu là giá trị cụ thể hoặc trong danh sách sẵn có. Chức năng Data Validation sẽ giúp bạn làm được điều này.
• Chọn vùng cần đặt giá trị giới hạn -> Data\ Data Tools\ Data Validation.

•  Cửa sổ Dava Validation hiện ra

 

1. Thẻ Setting: Thiết lập điều kiện

• Allow: Giá trị cho phép nhập
• Data: Điều kiện
• Formula: Công thức

   

• Whole number: Chỉ cho phép nhập số nguyên (khi chọn chức năng Data xuất hiện cho phép khống chế phạm vi giá trị nhập)
• Between: Trong vùng …
• Not Between: Ngoài vùng …
• Equal to: Bằng
• No Equal to: Không bằng
• Greater then: Lớn hơn
• Less than: Nhỏ hơn
• Greater than or equal to: >=
• Less than or equal to: <=
• Decimal: Chỉ cho phép nhập số nguyên hoặc số thập phân.
• List: Chỉ cho phép nhập từ một danh sách có sẵn (danh sách này có thể nhập trực tiếp, từ một vùng trong sheet hoặc từ tên “Name” vùng được gán sẵn). Nên sử dụng chức năng này khi chúng ta cần nhập liệu được lấy từ cơ sở dữ liệu sẵn có nhằm hạn chế sai sót, rút ngắn thời gian.
• Date: Chỉ cho phép nhập dữ liệu kiểu ngày. Việc điều khiển về khoảng ngày nhập giống Whole number.
• Text length: Cho phép nhập liệu là chuỗi có độ dài xác định.

 

2. Thẻ Input Message: Cho phép hiển thị thông tin nhập liệu khi di chuyển chuột vào ô đó, từ đó định hướng cho việc nhập liệu.

 
 

3. Thẻ Error Alert: Xuất hiện hộp thoại cảnh báo nếu nhập liệu không đúng qui định

• Mục Style: Shop (Không cho nhập), Warninh (Cảnh báo), Information: (Thông tin)
• Title: Gõ tiêu đề hộp thoại cảnh báo.
• Error message: Nội dung cảnh báo.

 
Vd1: Nhập Số Lượng từ 0 đến 10, hiện thông báo khi nhập sai

• Khối cột Số Lượng vào Data -> Data Validation -> Data Validation

 

 
Vd2:  Tạo List Tên hàng

Kết quả

II. Tạo List động

1. Giới thiệu

• Là vùng mà khi bớt xóa sửa thì các đối tượng có tham chiếu list này sẽ được cập nhật theo

2. Cách tạo

• Quét khối dữ liệu có sẵn hoặc để trỏ tại vùng cần tạo List. Vào Insert\ Table

•  Cửa sổ Create Table hiện ra

 

• Where is the data your Table?: Drag mouse để xác định danh sách có sẵn hoặc click ô nào đó xác định ô chứa list
• My table has header: Lấy dòng đầu làm tiêu đề.

 3. Hiệu chỉnh List: Chọn vùng list đã tạo -> Design

• Table Name: Tên vùng list
• Resize Table: Thay đổi địa chỉ vùng list
• Remove Duplicates: Loại bỏ các vùng trùng lắp
• Convert to Range: Chuyển đổi dữ liệu trong table thành vùng dữ liệu trên sheet
• Table Style Options: Các tùy chọn thể hiện thông tin
• Table Styles: Các dạng trình bày table

 

• Sau khi tạo list nên đặt tên để sau này cần tham chiếu đến chỉ sử dụng tên thay vì dùng địa chỉ vùng.

 
 

III. Conditional Formatting (CF)

1. Giới thiệu

• CF trong Excel là một công cụ mạnh giúp chúng ta định dạng các chuỗi văn bản trong các ô, các giá trị về màu sắc, kiểu mẫu nền, kẻ khung…
• CF khi áp dụng vào các ô nó sẽ đè lên các định dạng thông thường của ô về màu sắc, kiểu thể hiện văn bản và số. Tuy nhiên khi chúng ta xóa bỏ CF thì các các định dạng trước kia của các ô sẽ được phục hồi.

 2. Cách tạo

• Chọn ô hay vùng muốn áp dụng một CF, sau đó vào Home\ Style\ Conditional Formatting và có một số lựa chọn như:

• Higlight Cells Rules: Chứa các qui luật định dạng làm nổi các ô chứa giá trị lớn hơn một giá trị xác định, nằm giữa hai giá trị, chứa một chuỗi xác định hoặc các giá trị trùng nhau…
• Top/Bottom Rules: Chứa các qui luật định dạng làm nổi các ô chứa nhóm giá trị lớn nhất, nhóm 10% các giá trị lớn nhất và nhóm các giá trị trung bình…
• Data Bars: Áp dụng các đồ thị hình thang trực tiếp trong các ô tương ứng với giá trị đang chứa trong ô.
• Color Scales: Áp dụng màu nền tương ứng với giá trị chứa trong ô.
• Icon Sets: Hiển thị các Icon trực tiếp trong các ô, Các Icon hiển thị phụ thuộc vào giá trị chứa trong ô.
• New Rules: Cho phép ta chọn lựa CF khác, bao gồm cả qui luật CF dựa vào kết quả luận lý của công thức.
• Clear Rules: Xóa tất cả các CF đang áp dụng cho các ô đang chọn.
• Manage Rules: Hiển thị hộp thoại Conditional Formatting Rules Manager. Ở đây bạn có thể tạo mới, chỉnh sửa hoặc xóa các CF.

 3. Các qui luật có thể áp dụng với CF tự tạo (New Rules)

• Format all cells based on their values: Định dạng theo điều kiện căc cứ vào giá trị số chứa trong các ô bảng tính. Hiển thị các kiểu định dạng như là data bars, color scales hoặc icon sets.
• Format only cells that contain: Định dạng theo điều kiện căn cứ vào nội dung chứa trong các ô. Có thể áp dụng các phép so sánh (lớn hơn, nhỏ hơn, lớn hơn hoặc bằng, nhỏ hơn hoặc bằng, bằng, khoảng, ngoài khoảng), các qui luật có thể áp dụng cho các kiểu dữ liệu chuỗi, ngày tháng, ô trống, và các lỗi trong ô.
• Format only top or bottom ranked values: Qui luật này chỉ áp dụng định dạng nhóm các ô chứa các giá trị số lớn nhất và nhỏ nhất (tính theo %).
• Format only unique or duplicate values: Qui luật này áp dụng cho các ô chứa giá trị số là duy nhất hoặc trùng lắp.
• Use a formula to determine which cells to format: Qui luật này áp dụng định dạng dựa trên kết quả luận lý của công thức.

 
Vd1: Sử dụng Data Bars để vẽ đồ thị để so sánh số lượng học viên tham gia các khóa học.

• Chọn vùng C3:C8
• Vào Home\ Conditional Formatting\ Data Bars

 

• Data Bars chỉ cung cấp 6 màu cơ bản, nếu muốn chọn thêm thì bấm vào More Rules… hộp thoại New Formatting Rule cung cấp thêm một số lựa chọn:
• Show bar only: Ẩn các giá trị trong ô.
• Type và Value: Để điều chỉnh cách hiển thị của các thanh đồ thị.
• Bar Appearance: Danh mục các màu cho thanh đồ thị.

 
Vd2: Tô màu hồng cho các ô có giá trị >100.

 

• B1: Chọn vùng cần định dạng B3:E11
• B2: Vào Home\ Conditional Formatting\ New Rules
• Xuất hiện hộp thoại New Formatting Rule chọn Format only cells that contain.
• B3: Tại Format only cells with chọn Cell Value.
  • Chọn toán tử so sánh là greater than.
  • Nhập vào giá trị 100 tại hộp thoại bên phải.

 

• B4: Nhấn nút Format, hộp thoại Format Cells xuất hiện -> chọn tab Fill -> chọn Color là màu hồng -> OK.

 
Vd3: Tô màu hồng cho các ô có ngày <=01/05/2011

 
Vd4: Tô màu chữ xanh cho các ô có số trùng lắp.