Tự lập bảng thống kê xổ số bằng Excel

Một bảng thống kê xổ số tự làm chỉ cần một trang tính và năm hàm: MOD (hoặc RIGHT) để tách 2 số cuối, COUNTIF để đếm tần suất, COUNTIFS và SUMIFS để đếm số kỳ về, MAXIFS để tính số kỳ gan. Mấu chốt là nhập kết quả theo cột dọc, mỗi giải một dòng. Làm đúng bước này thì mọi công thức phía sau đều ngắn, và thêm kỳ mới chỉ là dán thêm dòng.

Bài dùng xổ số miền Bắc làm ví dụ (27 giải mỗi kỳ). Công thức viết với dấu phẩy ngăn các đối số; nếu máy bạn cài vùng Việt Nam và Excel báo lỗi, đổi dấu phẩy thành dấu chấm phẩy.

Bước 1: Nhập kết quả theo cột dọc

Tạo trang tính với hàng tiêu đề ở dòng 1 và bốn cột nhập tay:

Mỗi kỳ miền Bắc chiếm 27 dòng, cả 27 dòng mang cùng số kỳ ở cột A. Nhập 30 kỳ là 810 dòng, từ dòng 2 đến dòng 811. Các số trong bài đều là số giả định để minh họa.

Trước khi nhập, chọn cả cột D rồi đặt định dạng Text (Văn bản). Nếu để định dạng số, Excel tự bỏ số 0 ở đầu: giải Bảy “09” thành 9, giải Tư “0947” thành 947. Công thức ở bước 2 vẫn tính đúng trong cả hai trường hợp, nhưng giữ nguyên dạng văn bản thì nhìn lại dễ đối chiếu với bảng gốc hơn.

Bước 2: Tách 2 số cuối

Ở ô E2 gõ:

=IF(D2=””,””,MOD(D2,100))

MOD(D2,100) lấy phần dư khi chia cho 100, chính là 2 chữ số cuối: 40726 cho 26, 0947 cho 47, 09 cho 9. Kết quả là một con số từ 0 đến 99. Phần IF phía ngoài để dòng chưa nhập thì ô trống; thiếu nó, MOD của ô rỗng trả về 0 và bảng sẽ đếm thừa số 00.

Kéo công thức xuống hết vùng dữ liệu, hoặc kéo dư tới dòng 5000 để dành chỗ cho các kỳ sau. Muốn số 9 hiển thị thành 09: chọn cột E, vào Format Cells, mục Custom, gõ 00.

Cách thứ hai dùng hàm cắt chuỗi:

=IF(D2=””,””,VALUE(RIGHT(D2,2)))

RIGHT(D2,2) cắt 2 ký tự bên phải, nhưng trả về văn bản (“09”). Bọc VALUE bên ngoài để đổi thành số. Đừng dùng RIGHT trần: cột E khi đó là văn bản còn danh sách 0 đến 99 ở bước 3 là số, các hàm so sánh và MAXIFS dễ cho kết quả lệch mà không báo lỗi.

Nếu cần thêm đầu và đuôi của con loto: đầu là =INT(E2/10), đuôi là =MOD(E2,10). Cách đọc bảng đầu đuôi có ở bài thống kê đầu đuôi loto.

Bước 3: Đếm tần suất bằng COUNTIF

Tạo danh sách 100 số ở cột H: ô H2 gõ 0, ô H3 gõ =H2+1, kéo xuống tới H101 (ra 99). Định dạng 00 như cột E.

Ô I2, cột “Số nháy”:

=COUNTIF($E$2:$E$5000,H2)

Kéo xuống tới I101. Dấu $ khóa vùng đếm để khi kéo công thức vùng không trượt theo. Cột I cho biết mỗi số xuất hiện bao nhiêu lần trong toàn bộ dữ liệu, đếm cả những lần về nhiều nháy trong một kỳ.

Muốn chỉ thống kê 2 số cuối giải Đặc biệt, thêm điều kiện ở cột C:

=COUNTIFS($C$2:$C$5000,”ĐB”,$E$2:$E$5000,H2)

Bước 4: Đếm số kỳ về, không tính nháy

Một số về 2 nháy trong một kỳ vẫn chỉ là một kỳ về. Để đếm theo kỳ, thêm cột phụ F đánh dấu lần xuất hiện đầu tiên của mỗi số trong mỗi kỳ. Ô F2:

=IF(E2=””,””,IF(COUNTIFS($A$2:A2,A2,$E$2:E2,E2)=1,1,0))

Chú ý cách đặt dấu $: $A$2:A2 là vùng nở dần khi kéo xuống, luôn bắt đầu ở dòng 2 và kết thúc ở dòng hiện tại. COUNTIFS đếm từ đầu bảng tới dòng này có bao nhiêu dòng cùng kỳ và cùng con số; nếu bằng 1 thì đây là lần đầu số đó xuất hiện trong kỳ, ghi 1; ngược lại ghi 0.

Ô J2, cột “Số kỳ về”:

=SUMIFS($F$2:$F$5000,$E$2:$E$5000,H2)

Chia J2 cho tổng số kỳ sẽ ra tỷ lệ kỳ có về. Với miền Bắc, con số lý thuyết là 23,8%.

Bước 5: Tính số kỳ gan

Số kỳ gan là số kỳ đã trôi qua kể từ lần cuối một số xuất hiện. Cần hai ô.

Ô K2, “Kỳ về gần nhất”:

=MAXIFS($A$2:$A$5000,$E$2:$E$5000,H2)

Ô L2, “Số kỳ gan”:

=MAX($A$2:$A$5000)-K2

MAX của cột A là số thứ tự kỳ mới nhất. Số nào về ngay kỳ mới nhất có gan bằng 0. Số nào chưa về lần nào trong dữ liệu thì MAXIFS trả về 0, và cột L hiện đúng bằng tổng số kỳ, nghĩa là “gan ít nhất chừng đó kỳ”.

MAXIFS có trong Excel 2019, Microsoft 365 và Google Sheets. Với Excel cũ hơn, dùng công thức mảng sau và kết thúc bằng Ctrl+Shift+Enter thay cho Enter:

=MAX(IF($E$2:$E$5000=H2,$A$2:$A$5000))

Ý nghĩa của con số gan, và vì sao gan lâu không báo hiệu điều gì, đã có ở bài lô gan là gì.

Bước 6: Tự kiểm tra bảng

Nhập tay vài trăm dòng rất dễ sót. Ba phép kiểm sau bắt được hầu hết lỗi:

Tổng lệch 810 thì có kỳ nhập thiếu hoặc thừa giải. Dùng phép kiểm thứ hai dò từng kỳ để tìm.

Muốn biết một số lệch khỏi kỳ vọng bao nhiêu, thêm ô M2:

=(I2-810/100)/SQRT(810*99/10000)

Với 30 kỳ, kỳ vọng là 810 ÷ 100 = 8,1 nháy và độ lệch chuẩn là căn bậc hai của 810 × 0,01 × 0,99 ≈ 2,83. Ô M2 cho biết số đó cách kỳ vọng mấy độ lệch chuẩn. Giá trị trong khoảng từ −2 đến 2 là dao động thường gặp; với 100 số, có vài số vượt ra ngoài khoảng này cũng là bình thường. Nếu dữ liệu không phải 30 kỳ, thay 810 bằng 27 nhân số kỳ. Cách diễn giải chi tiết ở bài cách đọc bảng tần suất loto 30 ngày.

Bảng hoàn chỉnh trông thế nào?

Ví dụ ba dòng dữ liệu (số giả định) sau khi có công thức:

Số 47 về 2 nháy trong kỳ 1: cột I đếm 2, cột J chỉ đếm 1 kỳ nhờ cột F ghi 0 ở lần xuất hiện thứ hai.

Khi có kỳ mới, dán 27 dòng xuống cuối, điền số kỳ tiếp theo vào cột A. Vì các công thức đã quét tới dòng 5000, toàn bộ bảng thống kê tự cập nhật. Muốn chỉ xem 30 kỳ gần nhất khi dữ liệu đã dài, cách gọn nhất là chép 810 dòng cuối sang một trang tính khác có cùng bộ công thức.

Câu hỏi thường gặp

Google Sheets có dùng được các công thức này không?

Có. MOD, RIGHT, VALUE, COUNTIF, COUNTIFS, SUMIFS, MAXIFS đều có trong Google Sheets với cùng cú pháp. Dấu ngăn đối số cũng tùy thiết lập vùng của tệp.

Làm cho miền Nam hoặc miền Trung thì sửa gì?

Mỗi đài một trang tính riêng, mỗi kỳ 18 dòng (giải Tám đến giải Đặc biệt). Thay 27 bằng 18 trong các phép kiểm: 30 kỳ là 540 dòng, tần suất bình quân 5,4 nháy mỗi số. Lưu ý một đài chỉ quay một hoặc hai kỳ mỗi tuần, nên 30 kỳ của một đài trải dài nhiều tháng.

Lấy số liệu ở đâu để nhập?

Chép từ bảng kết quả theo ngày, ví dụ trang kết quả xổ số miền Bắc. Nhập xong kỳ nào, chạy phép kiểm 27 dòng cho kỳ đó luôn.

Bảng tự lập có giúp chọn số dễ trúng hơn không?

Không. Bảng mô tả những gì đã xảy ra; mỗi kỳ quay độc lập với các kỳ trước nên tần suất hay số kỳ gan không làm thay đổi xác suất của kỳ sau. Giá trị của việc tự lập bảng là hiểu các con số thống kê trên mạng được tính ra thế nào, và tự thấy dao động ngẫu nhiên lớn đến đâu.