Excel: Lấy ngẫu nhiên 100 trong 1500 khách hàng
Tình huống hằng ngày:
Bạn Nguyệt Minh, thành viên của HelloICT, có một danh sách 1500 khách
hàng. Nhiệm vụ của bạn ấy là làm sao lấy ngẫu nhiên 100 khách hàng
trong số 1500 khách hàng trong danh sách và các khách hàng được lấy
không trùng nhau. Bạn ấy cũng có thể lấy ngẫu nhiên 100 khách hàng 2
đến 3 lần. Sau khi tìm hiểu và thử nghiệm không thành, Nguyệt Minh đã
nhờ đến HelloICT giúp đỡ. Trong vòng 20 phút, mình đã có câu trả lời
cho bạn ấy!
Điều kiện:
1. Danh sách gồm 1500 khách hàng
2. Làm sao để lấy ngẫu nhiên 100 trong 1500 khách hàng. 100 khách hàng này không trùng nhau.
3. Lấy ngẫu nhiên 100 khách hàng 2-3 lần.
Giải pháp của HelloICT:
Bước 1: Đánh số thứ tự cho 1500 khách hàng. Bắt đầu số 1 cho khách hàng thứ nhất và số 1500 cho khách hàng cuối cùng. Mục đích của bước này là gán 1 số đại diện cho một khách hàng. Ví dụ: số 10 đại diện cho khách hàng A, số 11 đại diện cho khách hàng B. Các số này không được lặp lại 2 lần.
Bước 2: Tạo 100 số
ngẫu nhiên. Bước này dùng lệnh =randbetween() sẽ gặp vấn đề các số ngẫu
nhiên có thể trùng nhau. Giải pháp tốt nhất để lấy 100 số ngẫu nhiên mà
không trùng nhau là sử dụng VBA.
Thực hiện: Nhấn Alt + F11 để vào phần soạn thảo mã VBA >> Vào
Insert > Module > Copy và dán đọan code bên dưới vào khung soạn
thảo.
‘——————————–
‘Code của DONGPN
‘===================
Sub TaoNgauNhien()
Dim i As Integer, j As Integer
Dim Matran(), gtri, gtri2
Dim max_i, max_j As Integer
max_i = 1500
max_j = 100 ‘ So so ngau nhien muon lay ra
ReDim Matran(1 To max_i)
For i = 1 To max_i
Do
gtri = Round(Rnd() * max_i, 0)
If gtri = 0 Then GoTo tieptuc
For j = 1 To i – 1
If gtri = Matran(j) Then GoTo tieptuc
Next j
Matran(i) = gtri
Exit Do
tieptuc:
Loop While True
Next i
For i = 1 To max_j
Cells(ActiveCell.Row + i, ActiveCell.Column) = Matran(i)
Next i
End Sub
Trong đoạn code này có hai biến mà các bạn cần lưu ý:
max_i: là khoảng sẽ tạo giá trị ngẫu nhiên.
max_j: là số số ngẫu nhiên muốn lấy
* Cảm ơn bạn DONGPN đã bổ sung đoạn code VBA này!
Nhấn Alt + Q để đóng của sổ soạn thảo code VBA. Tiếp tục đặt con trỏ
vào một cột trống trong Sheet, nhấn Alt + F8 và chạy macro này để tạo
100 số ngẫu nhiên.
Buớc 3: Dùng hàm vlookup đối chiếu giữa cột số ngẫu nhiên và bảng danh sách khách hàng để tìm ra Tên khách hàng tương ứng với từng số ngẫu nhiên. Ví dụ ta được con số ngẫu nhiên là 152 thì tên khách hàng là gì trong bảng danh sách?
Công thức: =vlookup(cell-số-ngẫu-nhiên, vùng-bảng-danh-sách-gồm-cột-số-thứ-tự, số-thứ-tự-cột-cần-lấy-giá-trị-tính-từ-cột-số-thứ-tự). Ví dụ: =VLOOKUP(K4,$A$4:$D$1725,4). Nên nhớ địa chỉ bảng danh sách phải là địa chỉ tuyệt đối.
Như vậy, tương ứng với 100 số ngẫu nhiên lấy ra từ 1500 con số thứ tự
của khách hàng, chúng ta sẽ có 100 khách hàng ngẫu nhiên tương ứng.
{tipbox_right}Lưu ý rằng, mỗi khi có thay đổi, các con số ngẫu nhiên sẽ thay đổi và vì thế tên khách hàng cũng thay đổi theo.
Chúc Nguyệt Minh và các bạn thử nghiệm thành công!
Bạn làm như vậy làm sao lấy được 100 người không trùng. Vì hàm randbetween có thể cho ra các số ngẫu nhiên trùng nhau. Như vậy sẽ có trường hợp có người xuất hiện hơn 1 lần. Trong hình của bạn Số 6 xuất hiện tới 3 lần.
úi úi! Bạn nói không sai! Mình sai ngay phần này rồi! Sẽ update!
dongpn nói đúng rồi, hàm randbetween không thể cho ra các số ngẫu nhiên không trùng nhau được. Tìm cách khác đi bạn ơi.
Các bạn có thể làm theo cách tạo một Macro rồi chạy macro đó. Nó sẽ cho ra 100 số ngẫu nhiên không trùng (Muốn cho ra bao nhiêu cũng được).
——————–
Thao tác
——————–
Nhấn Atl F11 để vào VBA
Nhấn Insert/Module, rồi thên đoạn code phía dưới
Trở về MS Excel (nhấn Alt Q)
Nhấn Alt F8 rồi chọn và chạy macro TaoNgauNhien la xong
Luu y: Sau khi co 100 số ngẫu nhiên thì dùng hàm Vlookup như bạn BiBO nói để lầy tên người nhé.
‘——————————–
‘Doạn code:
‘——————————–
Option Explicit
Sub TaoNgauNhien()
Dim i As Integer, j As Integer
Dim Matran(), gtri, gtri2
Dim max_i, max_j As Integer
max_i = 1500
max_j = 100 ‘ So so ngau nhien muon lay ra
ReDim Matran(1 To max_i)
For i = 1 To max_i
Do
gtri = Round(Rnd() * max_i, 0)
If gtri = 0 Then GoTo tieptuc
For j = 1 To i – 1
If gtri = Matran(j) Then GoTo tieptuc
Next j
Matran(i) = gtri
Exit Do
tieptuc:
Loop While True
Next i
For i = 1 To max_j
Cells(ActiveCell.Row i – 1, ActiveCell.Column) = Matran(i)
Next i
End Sub
——————–
Trong đoạn code này có hai biến mà các bạn cần lưu ý:
max_i: là khoảng sẽ tạo giá trị ngẫu nhiên.
max_j: là số số ngẫu nhiên muốn lấy
Các bạn có thể làm theo cách tạo một Macro rồi chạy macro đó. Nó sẽ cho ra 100 số ngẫu nhiên không trùng (Muốn cho ra bao nhiêu cũng được).
——————–
Thao tác
——————–
Nhấn Atl F11 để vào VBA
Nhấn Insert/Module, rồi thên đoạn code phía dưới
Trở về MS Excel (nhấn Alt Q)
Nhấn Alt F8 rồi chọn và chạy macro TaoNgauNhien la xong
Luu y: Sau khi co 100 số ngẫu nhiên thì dùng hàm Vlookup như bạn BiBO nói để lầy tên người nhé.
‘——————————–
‘Doạn code:
‘——————————–
Option Explicit
Sub TaoNgauNhien()
Dim i As Integer, j As Integer
Dim Matran(), gtri, gtri2
Dim max_i, max_j As Integer
max_i = 1500
max_j = 100 ‘ So so ngau nhien muon lay ra
ReDim Matran(1 To max_i)
For i = 1 To max_i
Do
gtri = Round(Rnd() * max_i, 0)
If gtri = 0 Then GoTo tieptuc
For j = 1 To i – 1
If gtri = Matran(j) Then GoTo tieptuc
Next j
Matran(i) = gtri
Exit Do
tieptuc:
Loop While True
Next i
For i = 1 To max_j
Cells(ActiveCell.Row i – 1, ActiveCell.Column) = Matran(i)
Next i
End Sub
——————–
Trong đoạn code này có hai biến mà các bạn cần lưu ý:
max_i: là khoảng sẽ tạo giá trị ngẫu nhiên.
max_j: là số số ngẫu nhiên muốn lấy
Đọan code của DONGPN bị lỗi ở dòng
[quote]Cells(ActiveCell.Row plus i – 1, ActiveCell.Column) = Matran(i) [/quote]
Nhưng mình đã edit lại rồi! Dúng của nó là
[quote]Cells(ActiveCell.Row plus i, ActiveCell.Column) = Matran(i)[/quote]
Rất hay, thỉnh thoảng mình vẫn cần sử dụng thủ thuật này.
Cảm ơn nhé.
Tớ đã từng gặp vấn đề này. Nhưng không hiểu tại sao các bạn lại phải làm phức tạp đến thế.
/
Cách của tớ đơn giản thế này thôi:
1.Tạo 1 cột mới gồm 1500 số ngẫu nhiên bằng cách dùng hàm rnd() hay rand() gì đó
2. Copy toàn bộ cột đó, nhấn chuột phải lên cột khác chọn paste special (chỉ copy giá trị, bằng cách không chọn copy operation hay formulation gi` dó). Có bạn hỏi tại sao lại phải copy sang cột khác. Vì cột số ở bước 1 sẽ thay đổi mỗi khi ta thay đổi bất kì thứ gì trong file excel này nghĩa là mỗi lần chạy sẽ cho ra 1 cột ngẫu nhiên khác nhau. Làm như tó sẽ được 1 cột gồm 1500 số ngẫu nhiên cố định
3. Chọn 2 cột gồm 1500 khách hàng và 1500 số ngẫu nhiên cố định kia rồi chọn chức năng sort (sắp xếp) theo cột gồm các số ngẫu nhiên trước rồi mới đến cột khách hàng
4. Sau bước 3 ta lấy 100 khách hàng trên cùng chính là 100 khách hàng được chọn ngẫu nhiên
p/s: Nếu không muốn dùng lại dãy 1500 số ngẫu nhiên thì không cần đến bước 2. (Bước 2 đưa vào là để tránh trường hợp thế này:Ta muốn chọn ra 100 người với ứng với các số ngẫu nhiên nhỏ nhất hay lớn nhất trong số 1500 người, lệnh sort sẽ căn cứ vào giá trị trước đó. Nhưng khi thực hiện sort xong cột 1500 số ngẫu nhiên kia lại tự thay đổi và ta không thấy còn đúng thứ tự nữa. Chẳng hạn có người họ muốn xem sự công minh trong cách làm của ta, thì giải thích sao đây.
lam the nao de lay ra gia tri dai dien trong danh sach gia tri duoc lap lai nhieu lan