Google Sheets Nâng Cao Bài 1: Data Validation Và Dropdown List Chuyên Nghiệp

Chào mừng bạn đến với Series Google Sheets Nâng Cao của SheetStore Academy. Nếu bạn đã nắm vững các kỹ năng cơ bản như nhập liệu, công thức SUM/AVERAGE, và định dạng ô, thì đây là lúc nâng trình lên một tầm cao mới.

Bài học đầu tiên trong series nâng cao sẽ đi sâu vào Data Validation (Xác thực dữ liệu) — tính năng giúp bạn kiểm soát chặt chẽ dữ liệu nhập vào bảng tính, tránh lỗi, và tạo ra các dropdown list thông minh. Đây là nền tảng để xây dựng các spreadsheet chuyên nghiệp, đặc biệt khi nhiều người cùng sử dụng.

1. Data Validation Là Gì Và Tại Sao Quan Trọng?

Data Validation là tính năng cho phép bạn đặt ra các quy tắc về loại dữ liệu được phép nhập vào một ô hoặc vùng ô trong Google Sheets. Khi người dùng nhập dữ liệu không đúng quy tắc, hệ thống sẽ hiển thị cảnh báo hoặc từ chối nhập.

Lợi ích khi sử dụng Data Validation

• Giảm thiểu lỗi nhập liệu: Ngăn chặn việc nhập sai định dạng, sai giá trị ngay từ đầu.
• Đồng nhất dữ liệu: Đảm bảo tất cả mọi người nhập đúng format quy định (ví dụ: ngày tháng đúng format DD/MM/YYYY).
• Tăng tốc nhập liệu: Dropdown list giúp chọn nhanh thay vì gõ tay toàn bộ.
• Bảo vệ công thức: Tránh người dùng vô tình nhập text vào ô chứa số, làm hỏng công thức.
• Hướng dẫn người dùng mới: Input hints giúp người không quen biết phải nhập gì.

Kịch bản thực tế cần Data Validation

2. Các Loại Data Validation Trong Google Sheets

Google Sheets cung cấp nhiều loại validation khác nhau, phù hợp với từng nhu cầu cụ thể.

Cách mở Data Validation

1. Chọn ô hoặc vùng ô muốn đặt validation
2. Vào menu Data → Data validation
3. Hoặc click chuột phải → View more cell actions → Data validation
4. Panel "Data validation rules" sẽ hiện bên phải màn hình

2.1. Dropdown (List of items)

Tạo dropdown với danh sách giá trị cố định. Người dùng chỉ có thể chọn từ danh sách, không được gõ tự do.

// Cách thiết lập:
// Criteria: Dropdown (list of items)
// Enter values: Hà Nội, Hồ Chí Minh, Đà Nẵng, Cần Thơ, Hải Phòng
// Mỗi giá trị cách nhau bằng dấu phẩy
  

2.2. Dropdown (từ vùng dữ liệu)

Tạo dropdown với danh sách lấy từ một vùng ô khác trong spreadsheet. Lợi thế: khi cập nhật danh sách nguồn, dropdown tự động cập nhật theo.

// Criteria: Dropdown (from a range)
// Range: Sheet2!A2:A50
// Hoặc dùng named range: tinh_thanh_list
  

2.3. Số (Number)

Chỉ cho phép nhập số với các điều kiện: bằng, khác, lớn hơn, nhỏ hơn, trong khoảng, ngoài khoảng.

// Ví dụ validation cho điểm thi (0-10):
// Criteria: Number → is between → 0 and 10
// Hoặc số nguyên (whole number): Number → is greater than → 0
  

2.4. Văn bản (Text)

Kiểm tra nội dung văn bản theo điều kiện: chứa chuỗi, không chứa, bắt đầu bằng, kết thúc bằng, đúng bằng, là email hợp lệ, là URL hợp lệ.

// Kiểm tra email hợp lệ:
// Criteria: Text → is valid email

// Kiểm tra URL hợp lệ:
// Criteria: Text → is valid URL

// Mã sản phẩm bắt đầu bằng "SP":
// Criteria: Text → starts with → SP
  

2.5. Ngày (Date)

Chỉ cho phép nhập ngày với các điều kiện: trước ngày X, sau ngày X, trong khoảng, là ngày hợp lệ.

// Chỉ nhận ngày trong năm 2026:
// Criteria: Date → is between → 01/01/2026 and 31/12/2026

// Chỉ nhận ngày không phải tương lai:
// Criteria: Date → is before or on → =TODAY()
  

2.6. Checkbox

Tạo ô checkbox. Mặc định TRUE/FALSE, có thể tùy chỉnh giá trị khi tick và khi không tick.

// Checkbox mặc định: TRUE/FALSE
// Checkbox tùy chỉnh: Tick = "Đã xong", Bỏ tick = "Chưa xong"
// Criteria: Checkbox → Use custom cell values
// Checked: Đã xong | Unchecked: Chưa xong
  

2.7. Custom Formula

Viết công thức tùy chỉnh — mạnh mẽ nhất nhưng cũng phức tạp nhất. Phần này sẽ được trình bày chi tiết ở mục 5.

3. Tạo Dropdown List Cơ Bản

Bước 1: Chuẩn bị danh sách nguồn

Best practice: Tạo một sheet riêng (đặt tên "Lists" hoặc "Danh Sach") để chứa tất cả các danh sách dropdown. Điều này giúp dễ quản lý và cập nhật.

// Sheet "Lists" - Cột A: Tỉnh thành
A1: Tỉnh/Thành phố (header)
A2: Hà Nội
A3: Hồ Chí Minh
A4: Đà Nẵng
A5: Cần Thơ
A6: Hải Phòng
A7: Nha Trang
// ...

// Sheet "Lists" - Cột B: Loại sản phẩm
B1: Loại sản phẩm
B2: Điện tử
B3: Thời trang
B4: Thực phẩm
B5: Mỹ phẩm
B6: Đồ gia dụng
  

Bước 2: Tạo Named Range cho danh sách

Named range giúp tham chiếu đến vùng dữ liệu bằng tên thân thiện thay vì địa chỉ ô khó nhớ.

1. Chọn vùng A2:A50 trong sheet "Lists"
2. Vào Data → Named ranges
3. Đặt tên: tinh_thanh
4. Nhấn Done

Bước 3: Áp dụng validation với Named Range

// Trong cài đặt Data Validation:
// Criteria: Dropdown (from a range)
// Range: tinh_thanh
// (Thay vì gõ Lists!A2:A50)

// Lợi ích: Thêm tỉnh mới vào Lists!A2:A50
// → Dropdown tự cập nhật không cần sửa validation
  

Bước 4: Bật tính năng hiển thị dropdown arrow

Trong cài đặt validation, đảm bảo check vào "Show dropdown list in cell" để người dùng thấy mũi tên dropdown khi click vào ô.

Tips: Tìm kiếm trong dropdown dài

Với danh sách dài (>30 mục), người dùng có thể bắt đầu gõ vài ký tự để lọc nhanh. Ví dụ, gõ "hà" sẽ lọc ra "Hà Nội", "Hà Tĩnh", "Hà Giang".

4. Dropdown Phụ Thuộc (Dependent Dropdown)

Đây là tính năng nâng cao và rất hay: dropdown ở ô B thay đổi nội dung tùy theo giá trị đã chọn ở ô A. Ví dụ: chọn "Tỉnh" → dropdown "Quận/Huyện" hiển thị đúng quận huyện của tỉnh đó.

Phương pháp 1: Dùng INDIRECT + Named Ranges

Đây là phương pháp phổ biến nhất cho dependent dropdown trong Google Sheets.

Bước 1: Tạo named ranges cho từng danh mục con

// Tạo Named Range cho từng tỉnh:
// Tên: "Ha_Noi" → Lists!B2:B20 (danh sách quận Hà Nội)
// Tên: "Ho_Chi_Minh" → Lists!C2:C30 (danh sách quận HCM)
// Tên: "Da_Nang" → Lists!D2:D10 (danh sách quận Đà Nẵng)

// LƯU Ý: Named range KHÔNG được chứa dấu cách
// Phải dùng gạch dưới thay cho dấu cách
  

Bước 2: Thiết lập dropdown chính (Tỉnh/Thành)

// Ô A2: Dropdown chọn Tỉnh/Thành
// Criteria: Dropdown (from a range): tinh_thanh
  

Bước 3: Thiết lập dropdown phụ thuộc (Quận/Huyện)

// Ô B2: Dropdown chọn Quận/Huyện
// Criteria: Dropdown (from a range)
// Range: =INDIRECT(SUBSTITUTE(A2," ","_"))

// INDIRECT: Chuyển text thành tham chiếu vùng ô
// SUBSTITUTE: Thay dấu cách bằng gạch dưới
// Khi A2 = "Hà Nội" → INDIRECT("Ha_Noi") → dùng named range Ha_Noi
  

Cách hoạt động chi tiết

// Khi A2 = "Hà Nội":
SUBSTITUTE("Hà Nội", " ", "_") = "Hà_Nội"
INDIRECT("Hà_Nội") = Tham chiếu đến named range "Hà_Nội"
→ Dropdown B2 hiển thị: Hoàn Kiếm, Ba Đình, Đống Đa, Tây Hồ...

// Khi A2 = "Hồ Chí Minh":
SUBSTITUTE("Hồ Chí Minh", " ", "_") = "Hồ_Chí_Minh"
INDIRECT("Hồ_Chí_Minh") = Tham chiếu đến named range "Hồ_Chí_Minh"
→ Dropdown B2 hiển thị: Quận 1, Quận 2, Bình Thạnh, Gò Vấp...
  

Phương pháp 2: Dùng FILTER + INDIRECT cho danh sách động

// Sheet "Lists" có cấu trúc:
// Cột A: Danh mục lớn | Cột B: Danh mục con
// A2: Điện tử | B2: Điện thoại
// A3: Điện tử | B3: Laptop
// A4: Điện tử | B4: Máy tính bảng
// A5: Thời trang | B5: Áo
// A6: Thời trang | B6: Quần

// Dropdown cấp 2 dùng FILTER:
// Criteria: Dropdown (from a range)
// Range: =FILTER(Lists!B:B, Lists!A:A=A2)
// → Tự lọc danh mục con theo giá trị A2
  

Ví dụ thực tế: Form nhập đơn hàng 3 cấp

// Cấp 1 (Cột A): Loại sản phẩm
// Dropdown: Điện tử, Thời trang, Thực phẩm

// Cấp 2 (Cột B): Danh mục
// Dropdown phụ thuộc A: Điện thoại / Laptop (nếu A=Điện tử)

// Cấp 3 (Cột C): Thương hiệu
// Dropdown phụ thuộc B: Apple / Samsung (nếu B=Điện thoại)

// Named ranges:
// Điện_tử → Điện thoại, Laptop, Máy tính bảng
// Điện_thoại → Apple, Samsung, Xiaomi, Oppo
// Laptop → Dell, HP, Asus, Lenovo, Apple
  

5. Custom Formula Validation Nâng Cao

Custom formula cho phép bạn viết bất kỳ công thức nào trả về TRUE/FALSE. Đây là loại validation mạnh mẽ nhất.

Cú pháp cơ bản

// Validation chấp nhận khi công thức = TRUE
// Từ chối khi công thức = FALSE hoặc lỗi

// Công thức tham chiếu ô hiện tại: dùng địa chỉ ô đang chọn
// Ví dụ đang validate ô A1: dùng A1 trong công thức
// Khi copy validation xuống A2, A3... Google Sheets tự điều chỉnh
  

5.1. Kiểm tra định dạng mã sản phẩm

// Mã sản phẩm phải có đúng 8 ký tự và bắt đầu bằng "SP"
=AND(LEN(A1)=8, LEFT(A1,2)="SP")

// Mã SKU: chỉ chứa chữ in hoa và số
=REGEXMATCH(A1,"^[A-Z0-9]+$")

// Số điện thoại Việt Nam (10 số, bắt đầu 0)
=AND(LEN(A1)=10, LEFT(A1,1)="0", ISNUMBER(VALUE(A1)))
  

5.2. Không được trùng giá trị (unique validation)

// Kiểm tra không trùng trong cột A (A1:A100)
=COUNTIF($A$1:$A$100,A1)=1

// Giải thích: Đếm số lần A1 xuất hiện trong toàn cột
// Nếu = 1 (chỉ xuất hiện 1 lần = chính nó) → hợp lệ
// Nếu > 1 → đã có giá trị trùng → từ chối
  

5.3. Phụ thuộc vào giá trị ô khác

// Ô B1 chỉ bắt buộc nhập khi A1 = "Có"
=OR(A1<>"Có", B1<>"")

// Ngày kết thúc phải sau ngày bắt đầu
// Validation cho ô B1 (ngày kết thúc):
=B1>A1

// Tổng A1+B1 không được vượt quá 100
=A1+B1<=100
  

5.4. Kiểm tra ngày làm việc (không phải cuối tuần)

// WEEKDAY: 1=CN, 2=T2, 3=T3, 4=T4, 5=T5, 6=T6, 7=T7
// Chỉ cho phép nhập ngày thứ 2-6
=AND(WEEKDAY(A1,2)>=1, WEEKDAY(A1,2)<=5)

// WEEKDAY(date, 2): 1=T2, 2=T3, ..., 6=T7, 7=CN
  

5.5. Kiểm tra email doanh nghiệp

// Chỉ chấp nhận email từ domain công ty
=REGEXMATCH(A1,"@company\.com$")

// Email hợp lệ (có @ và dấu chấm sau @)
=REGEXMATCH(A1,"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}$")

// Không phải email gmail (dùng cho form công việc)
=NOT(REGEXMATCH(A1,"@gmail\.com$"))
  

5.6. Kiểm tra tồn kho đủ khi xuất hàng

// Cột A: Mã hàng | Cột B: Số lượng xuất
// Bảng kho: Sheet "Kho"!A:B (Mã hàng | Số tồn)
// Validation cho cột B (số lượng xuất):
=B1<=IFERROR(VLOOKUP(A1,Kho!A:B,2,0),0)

// Giải thích: Số xuất phải <= số tồn trong kho
// VLOOKUP tìm số tồn theo mã hàng
// IFERROR trả về 0 nếu không tìm thấy mã hàng
  

6. Cấu Hình Thông Báo Lỗi Chuyên Nghiệp

Khi người dùng nhập sai, Google Sheets có thể hiển thị thông báo lỗi. Thiết kế thông báo rõ ràng giúp người dùng biết cách sửa.

Hai loại phản hồi khi nhập sai

• Warning (Cảnh báo): Hiển thị cảnh báo nhưng vẫn cho phép nhập. Phù hợp khi muốn nhắc nhở nhưng không bắt buộc.
• Reject input (Từ chối): Không cho phép nhập giá trị sai. Phù hợp với dữ liệu quan trọng cần chính xác tuyệt đối.

Cách thiết lập thông báo lỗi tùy chỉnh

// Trong panel Data Validation:
// 1. Mở rộng "Advanced options"
// 2. Trong "If the data is invalid":
//    - Show a warning
//    - Reject the input
// 3. Tick "Show validation help text"
// 4. Nhập Title và Description

// Ví dụ thông báo hay:
// Title: "Số điện thoại không hợp lệ"
// Description: "Vui lòng nhập số điện thoại 10 chữ số, bắt đầu bằng 0. Ví dụ: 0901234567"
  

Best practices cho thông báo lỗi

• Nói rõ vấn đề: "Số điện thoại phải có 10 chữ số" tốt hơn "Dữ liệu không hợp lệ".
• Đưa ví dụ: "Ví dụ đúng: 0901234567" giúp người dùng biết format mong muốn.
• Giải thích lý do: "Mã sản phẩm phải bắt đầu bằng SP để tra cứu trong hệ thống" giúp người hiểu tại sao cần đúng format.
• Tránh tiếng kỹ thuật: Thay vì "Invalid regex pattern", hãy dùng "Sai định dạng".

Thông báo nhập liệu hướng dẫn (Input Message)

// Khác với error message, input message hiển thị TRƯỚC khi nhập
// Khi click vào ô, tooltip hiện ngay để hướng dẫn

// Cách bật: Trong Advanced options
// Tick "Show validation help text"
// Đây CŨNG là input message

// Ví dụ tốt:
// Title: "Ngày xuất hàng"
// Description: "Nhập ngày định dạng DD/MM/YYYY. 
//   Chỉ nhận ngày trong tháng hiện tại.
//   Ví dụ: 15/11/2026"
  

7. Ứng Dụng Thực Tế Trong Quản Lý Bán Hàng

7.1. Form Nhập Đơn Hàng Chuẩn Hóa

Áp dụng data validation toàn diện cho form nhập đơn hàng:

7.2. Bảng Theo Dõi Kho Hàng

// Cột C: Số lượng nhập - validation số nguyên dương
=AND(ISNUMBER(C1), C1=INT(C1), C1>0)

// Cột D: Số lượng xuất - không được vượt tồn kho
=D1<=C1-SUMIF($A$1:$A$100,A1,$D$1:$D$100)+D1

// Cột E: Giá nhập - số dương, tối đa 100 triệu
=AND(E1>0, E1<=100000000)

// Cột F: Đơn vị tính - dropdown
// Cái, Hộp, Kg, Lít, Bộ, Tấm, Cuộn, Thùng
  

7.3. Bảng Chấm Công Nhân Viên

// Cột B: Giờ vào - phải trước 9:00 (không trễ quá)
=B1<=TIMEVALUE("09:00")

// Cột C: Giờ ra - phải sau giờ vào
=C1>B1

// Cột D: Loại nghỉ - dropdown
// Nghỉ phép, Nghỉ bệnh, Nghỉ không lương, Công tác
// (chỉ điền khi cột E = "Nghỉ")

// Validation có điều kiện: D1 bắt buộc nếu E1="Nghỉ"
=OR(E1<>"Nghỉ", D1<>"")
  

8. Tips & Tricks Nâng Cao

Tip 1: Copy Data Validation sang nhiều ô

// Cách 1: Copy-paste thông thường (Ctrl+C → Ctrl+V)
// Sẽ copy cả validation và nội dung ô

// Cách 2: Paste Special - chỉ paste validation
// Ctrl+C → Ctrl+Shift+V → Chọn "Data validation only"
// Giữ nguyên nội dung ô, chỉ thêm validation

// Cách 3: Apply validation cho cả cột từ đầu
// Chọn cả cột C (click vào tiêu đề cột C)
// Áp dụng validation → áp dụng cho toàn cột
  

Tip 2: Xóa Data Validation

// Chọn ô/vùng → Data → Data validation
// Nhấn "Remove rule" để xóa validation
// Nội dung ô không bị ảnh hưởng
  

Tip 3: Highlight ô có validation

// Google Sheets không có tính năng hiển thị tất cả ô có validation
// Cách tìm: Ctrl+A để chọn tất cả → Data → Data validation
// Panel bên phải sẽ liệt kê tất cả validation rules đang có
  

Tip 4: Validation với ARRAYFORMULA

// Tạo danh sách dropdown động dựa trên điều kiện phức tạp
// Ví dụ: Dropdown chỉ hiện sản phẩm còn hàng (tồn > 0)

// Sheet "Kho": Cột A = Tên SP, Cột B = Số tồn
// Tạo named range "sp_con_hang" trỏ đến:
=FILTER(Kho!A:A, Kho!B:B>0)

// Dropdown validation dùng named range "sp_con_hang"
// → Tự động chỉ hiện sản phẩm còn trong kho
  

Tip 5: Bảo vệ ô có validation

// Sau khi setup validation xong, protect sheet để người dùng
// không vô tình xóa/sửa validation rules

// Data → Protect sheets and ranges
// Chọn "Sheet" → Restrict who can edit this range
// Chỉ admin mới được sửa, nhân viên chỉ nhập dữ liệu
  

Tip 6: Dùng Apps Script để tạo validation động

function taoDropdownDong() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('DonHang');
  const range = sheet.getRange('B2:B100');

  // Lấy danh sách từ database (sheet Kho)
  const khoSheet = ss.getSheetByName('Kho');
  const spList = khoSheet.getRange('A2:A50')
    .getValues()
    .flat()
    .filter(v => v !== '');

  // Tạo validation rule
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(spList, true)
    .setAllowInvalid(false)
    .setHelpText('Chọn sản phẩm từ danh sách')
    .build();

  range.setDataValidation(rule);
  console.log('Đã tạo dropdown cho ' + spList.length + ' sản phẩm');
}
  

Bài Tập Thực Hành

Để nắm vững Data Validation, hãy thực hành với bài tập sau:

Bài tập 1: Form nhập khách hàng

Tạo bảng nhập thông tin khách hàng với các validation:

• Họ tên: Text, tối thiểu 5 ký tự
• Số điện thoại: 10 chữ số, bắt đầu 0
• Email: định dạng email hợp lệ
• Tỉnh/Thành: dropdown 63 tỉnh thành
• Phân khúc khách hàng: dropdown (VIP, Thường, Tiềm năng)
• Ngày sinh nhật: ngày hợp lệ, không được là ngày tương lai

Bài tập 2: Form nhập điểm học sinh

Tạo bảng điểm với validation đảm bảo:

• Điểm từng môn: số thực từ 0 đến 10, tối đa 1 chữ số thập phân
• Xếp loại học lực: dropdown (Xuất sắc, Giỏi, Khá, TB, Yếu)
• Mã học sinh: dài đúng 8 ký tự, không trùng trong lớp

Kết Luận

Data Validation là một trong những công cụ mạnh mẽ nhất trong Google Sheets mà nhiều người dùng bỏ qua. Khi được áp dụng đúng cách, nó biến một bảng tính thông thường thành một ứng dụng nhập liệu chuyên nghiệp với kiểm soát chất lượng dữ liệu tự động.

Trong bài tiếp theo của series Google Sheets Nâng Cao, chúng ta sẽ học cách dùng Conditional Formatting nâng cao để trực quan hóa dữ liệu với màu sắc, color scales và icon sets.

Bài Tiếp Theo Trong Series

• Bài 2: Conditional Formatting Nâng Cao Và Color Scales
• Bài 3: IMPORTRANGE Và Kết Nối Dữ Liệu Nhiều Sheets

Tài Nguyên Tham Khảo

• Hướng dẫn Google Sheets Cơ Bản cho người mới
• Hàm QUERY Google Sheets toàn diện
• SheetStore — Phần mềm quản lý bán hàng trên Google Sheets