Làm cách nào để phân phối danh sách trong excel?

Tôi cần một máy tính/chương trình ngẫu nhiên hóa cho một giải đấu bóng chuyền xáo trộn 36 người chơi để thi đấu hoặc ngồi ngoài trong vòng 12 trận

1) Chúng tôi có 2 sân bóng chuyền, 4 mặt (Đỏ & Xanh lục; Vàng & Xanh lam).
2) Chúng tôi có 36 người chơi. 6 đội mỗi đội 6 người chơi. 24 người chơi sẽ chơi thành 4 đội trên 2 sân cho mỗi trận đấu. 12 người chơi sẽ ngồi ngoài mỗi trò chơi. Mỗi đội sẽ bao gồm 6 người chơi khác nhau trong mỗi trò chơi.
3) Mỗi ​​người chơi sẽ có một số và sẽ chơi theo màu của đội và sân trong mỗi trận đấu.
4) Sẽ có tổng cộng 12 trò chơi. Mỗi người chơi sẽ chơi 8 và ngồi ngoài 4 trong số 12 trận. Mỗi người chơi sẽ chơi hai lần trên mỗi màu sân. Không người chơi nào được chơi quá 2 ván liên tiếp.
5) Với 36 người chơi, 1 người chơi sẽ chơi 8 ván với 5 người chơi khác, mỗi ván.

Chúng tôi cũng muốn sắp xếp chương trình cho. 36 người chơi (6 đội, mỗi đội 6 người);

Tôi muốn chương trình có thể chạy trên máy tính xách tay.
Cảm ơn sự hỗ trợ hoặc nhận xét của bạn.

Chúng tôi có thể gặp phải một số tình huống khi xử lý Microsoft Excel nơi tất cả dữ liệu của chúng tôi nằm trong một cột. Nhưng chúng ta cần chia chúng thành nhiều nhóm bằng nhau. Sau khi tách, dữ liệu trông hấp dẫn hơn. Trong bài viết này, chúng tôi sẽ thảo luận về 3 phương pháp về cách chia dữ liệu thành các nhóm bằng nhau trong Excel với các ví dụ phù hợp và minh họa phù hợp

Mục lục ẩn

Tải sách bài tập thực hành

3 phương pháp chia dữ liệu thành các nhóm bằng nhau trong Excel

1. Kết hợp các hàm INDEX, COLUMNS, ROWS và IF để chia dữ liệu thành các nhóm bằng nhau

2. Tách một danh sách dài thành nhiều nhóm bằng nhau với Excel VBA

3. Sử dụng chức năng VBA tùy chỉnh để chia dữ liệu thành các nhóm chẵn

Cách chia tách dữ liệu và gắn thẻ với số nhóm

1. Tách dữ liệu bằng cách kết hợp các hàm MAX, ROUNDUP và PERCENTRANK

2. Kết hợp các hàm ROUNDUP, ROW và COUNT để chia dữ liệu

Sự kết luận

Những bài viết liên quan

Tải sách bài tập thực hành

Tải xuống sách bài tập thực hành này để thực hành trong khi bạn đang đọc bài viết này

Chia dữ liệu thành các nhóm bằng nhau. xlsm

3 phương pháp chia dữ liệu thành các nhóm bằng nhau trong Excel

Chúng ta có thể chia dữ liệu theo nhiều cách khác nhau. Ta có thể áp dụng điều kiện để tách dữ liệu i. e. mỗi nhóm chứa bao nhiêu nhóm hoặc bao nhiêu ô. Để hiển thị tất cả các phương thức, chúng tôi sẽ sử dụng tập dữ liệu sau

1. Kết hợp các hàm INDEX, COLUMNS, ROWS và IF để chia dữ liệu thành các nhóm bằng nhau

Hàm IF là một trong những hàm được sử dụng rộng rãi của Microsoft Excel. Đây là một hàm logic kiểm tra xem một điều kiện có được đáp ứng hay không và trả về một giá trị nếu TRUE và một giá trị khác nếu FALSE

Hàm INDEX trả về một giá trị hoặc tham chiếu của ô tại giao điểm của một hàng cụ thể và một cột trong một phạm vi nhất định

Hàm COLUMNS trả về số cột trong một mảng hoặc tham chiếu

Hàm ROWS trả về số hàng trong một tham chiếu hoặc mảng

Ta sẽ lập công thức kết hợp 4 hàm này để tách dữ liệu

Bước 1

• Chúng tôi sẽ chia dữ liệu đã cho thành 3 nhóm bằng nhau. Vì vậy, chúng tôi đã thêm Không. của ô Nhóm trong tập dữ liệu và đặt 3 vào ô tương ứng

Bước 2

• Bây giờ, đi đến Ô D7
• Đặt công thức sau vào ô đó

=IFERROR(IF($F$4>=COLUMNS($B$5:B5),INDEX($B$5:$B$16,(ROWS($B$5:B5))*$F$4-($F$4-COLUMNS($B$5:B5))),""),"")

Bước 3

• Nhấn Enter và kéo biểu tượng Fill Handle về phía bên phải

Chúng tôi mở rộng công thức về phía bên phải. Bởi vì mục đích chính của chúng tôi trước tiên là chia dữ liệu thành các nhóm. Dựa trên các nhóm và số lượng dữ liệu có sẵn, các ô sẽ được thêm vào các nhóm

Bước 4

• Bây giờ, hãy kéo biểu tượng Fill Handle xuống dưới để hoàn thành việc tách

Tất cả dữ liệu được chia đều thành các nhóm khác nhau

Phân tích công thức

Ở đây chúng tôi đang cố gắng lấy đầu vào đầu tiên trong nhóm thứ nhất (trong số 3 nhóm). Các nhóm được trang trí theo cách sắp xếp giống như cột

• $F$4>=COLUMNS($B$5. B5)

Đầu tiên, hàm IF kiểm tra xem điều kiện đã cho có được đáp ứng hay không. Nếu các tiêu chí được đáp ứng, phần sau của công thức sẽ hoạt động, nếu không, hàm IF sẽ trả về “”.
Kết quả. ĐÚNG

• $F$4-COLUMNS($B$5. B5)

Phần công thức này lấy tổng số nhóm trừ đi số lượng cột trong phạm vi đã cho.
Kết quả. 2

• (ROWS($B$5. B5))*$F$4

Ở đây, số hàng trong phạm vi nhất định được nhân với tổng số nhóm.
Kết quả. 3

• (ROWS($B$5. B5))*$F$4-($F$4-COLUMNS($B$5. B5))

Công thức này tạo ra một số hàng mà chúng ta sẽ sử dụng làm đối số cho hàm INDEX.
Kết quả. 1

• CHỈ SỐ($B$5. $B$16,1)

Chúng tôi nhận được một đầu ra ở đây từ phạm vi đã cho như được chỉ định bởi số hàng.
Kết quả. “Jessica”

• IFERROR(IF($F$4>=COLUMNS($B$5. B5),INDEX($B$5). $B$16,(ROWS($B$5. B5))*$F$4-($F$4-COLUMNS($B$5. B5))),””),””)

Hàm IFERROR kiểm tra xem có lỗi nào không. Nếu không tìm thấy lỗi, hãy cung cấp đầu ra của hàm IF.
Kết quả. “Jessica”

Đọc thêm. Cách chia nhỏ dữ liệu trong Excel (5 cách)

2. Tách một danh sách dài thành nhiều nhóm bằng nhau với Excel VBA

Chúng tôi có một danh sách dài chứa 12 ô. Chúng tôi muốn chia chúng thành 3 nhóm với 4 ô mỗi nhóm. Thực hiện theo các bước dưới đây dựa trên mã VBA Excel

Bước 1

• Đầu tiên bạn vào phần Sheet Name ở viền dưới mỗi sheet. Sau đó, nhấn nút bên phải của chuột
• Chọn tùy chọn Xem mã từ danh sách

Bước 2

• Lúc này cửa sổ ứng dụng VBA sẽ hiện ra. Chọn tùy chọn Mô-đun từ tab Chèn

• Cửa sổ mô-đun lệnh VBA xuất hiện ngay bây giờ. Chúng tôi viết mã VBA trên cửa sổ này và chạy từ đây

Bước 3

• Sao chép và dán mã VBA sau vào mô-đun lệnh

Sub split_data_into_equal_groups()
    Dim input_1 As Range
    Dim output_1 As Range
    Dim text_1 As String
    Dim out_array_1 As Variant
    Dim n, m As Long
    On Error Resume Next
    text_1 = ActiveWindow.RangeSelection.Address
Sel:
    Set input_1 = Nothing
    Set input_1 = Application.InputBox("Select input range:", "Range", text_1, , , , , 8)
    If input_1 Is Nothing Then Exit Sub
    If input_1.Areas.Count > 1 Then
        MsgBox "Multiple selections are not supported, select again", vbInformation, "Range"
        GoTo Sel
    End If
    If input_1.Columns.Count > 1 Then
        MsgBox "Multiple selections are not supported, select again", vbInformation, "Range"
        GoTo Sel
    End If
    Set output_1 = Application.InputBox("Select a cell to view the output:", "Start Range", , , , , , 8)
    If output_1 Is Nothing Then Exit Sub
    n = Application.InputBox("Number of cells per column:", "Cell Number", , , , , , 1)
    If n < 1 Then
        MsgBox "incorrect enter", vbInformation, "Cell Number"
        Exit Sub
    End If
    ReDim out_array_1(1 To n, 1 To Int(input_1.Rows.Count / n) + 1)
    For m = 0 To input_1.Rows.Count - 1
      out_array_1(1 + (m Mod n), 1 + Int(m / n)) = input_1.Cells(m + 1)
    Next
    output_1.Range("A1").Resize(n, UBound(out_array_1, 2)) = out_array_1
End Sub

Bước 4

• Nhấn F5 để chạy mã. Một hộp thoại sẽ xuất hiện để chọn phạm vi.
• Chúng tôi chọn dãy B5. B16 làm đầu vào

Bước 5

• hộp thoại thứ 2 xuất hiện. Hộp này yêu cầu tham chiếu ô để bắt đầu xuất dữ liệu. Ta chọn Ô D5 Sau đó nhấn OK

Bước 6

• Hộp thoại thứ 3 xem ngay bây giờ. Nó cho biết mỗi nhóm chứa bao nhiêu ô. Chúng tôi đặt 4 ở đây

Bước 7

• Cuối cùng, nhấn OK

Dữ liệu của chúng tôi được chia thành 3 nhóm bằng nhau. Chúng ta cần đề cập đến số lượng ô trong mỗi nhóm khi chạy mã

bài đọc tương tự

• Cách chia dữ liệu thành nhiều bảng tính trong Excel
• Excel Macro để chia một ô thành nhiều hàng (với các bước đơn giản)
• Cách chia dữ liệu từ một ô thành nhiều hàng trong Excel (3 phương pháp)
• Excel Macro để chia dữ liệu thành nhiều tệp (với các bước đơn giản)

3. Sử dụng chức năng VBA tùy chỉnh để chia dữ liệu thành các nhóm chẵn

Trong phần này, chúng ta sẽ tạo Hàm VBA tùy chỉnh để chia dữ liệu thành các nhóm bằng nhau

Bước 1

• Nhấn Alt+F11 để vào mô-đun lệnh VBA.
• Đặt mã VBA sau vào mô-đun lệnh và lưu mã

Function GroupValues(range_1 As Range)
Dim output As Variant
n = Application.Caller.Columns.Count
m = Application.Caller.Rows.Count
ReDim output(1 To m, 1 To n)
k = 1
For row_1 = 1 To m
    For column_1 = 1 To n
        If range_1.Cells(k) <> "" Then
            output(row_1, column_1) = range_1.Cells(k)
        Else
            output(row_1, column_1) = ""
        End If
        k = k + 1
    Next column_1
Next row_1
GroupValues = output
End Function

Bước 2

• Chuyển đến tập dữ liệu. Chọn phạm vi D5. F8, nơi dữ liệu sẽ biến thành các nhóm
• Bây giờ, hãy chuyển đến thanh công thức và đặt công thức sau

=GroupValues(B5:B16)

Bước 3

• Đây là công thức mảng. Vì vậy, hãy nhấn Ctrl + Shift + Enter .

Chúng ta có thể thấy rằng dữ liệu của một danh sách dài biến đổi thành các nhóm bằng nhau. GroupValues ​​này là một chức năng VBA tùy chỉnh

Cách chia tách dữ liệu và gắn thẻ với số nhóm

Trong phần trước, chúng tôi chia dữ liệu thành các nhóm bằng nhau. Nhưng trong phần này, chúng tôi sẽ chỉ gắn thẻ từng dữ liệu với số nhóm và sẽ không chia chúng thành các nhóm. Ngoài ra, đề cập rằng chúng tôi sẽ chỉ áp dụng các phương pháp dưới đây cho dữ liệu số

1. Tách dữ liệu bằng cách kết hợp các hàm MAX, ROUNDUP và PERCENTRANK

Hàm MAX trả về giá trị lớn nhất trong một tập hợp các giá trị. Bỏ qua các giá trị logic và văn bản

Hàm ROUNDUP làm tròn một số lên, cách xa 0

Hàm PERCENTRANK trả về thứ hạng của một giá trị trong tập dữ liệu dưới dạng phần trăm của tập dữ liệu

Chúng tôi kết hợp các hàm MAX, ROUNDUP và PERCENTRANK để phân chia dữ liệu. Nhưng sự kết hợp này chỉ áp dụng cho dữ liệu số

Chúng tôi sẽ xem xét tập dữ liệu dưới đây để hoạt động

Bước 1

• Chuyển đến ô C5
• Đặt công thức sau

=MAX( ROUNDUP( PERCENTRANK($B$5:$B$12,B5) *4, 0),1)

Bước 2

• Bây giờ, hãy nhấn nút Enter

Ở đây, chúng ta có thể thấy rằng dữ liệu đã cho được gắn thẻ với số nhóm thay vì chia nhỏ chúng. Dữ liệu được đánh dấu bằng một số nhóm tương ứng trong ô liền kề

Phân tích công thức

• PERCENTRANK($B$5. $B$12,B5)

Tính toán tỷ lệ phần trăm của Ô B5 so với Phạm vi B5. B12.
Kết quả. 0. 142

• PERCENTRANK($B$5. $B$12,B5) *4

Nhân kết quả với 4. Đối số nhân này quyết định có bao nhiêu nhóm hình thành.
Kết quả. 0. 568

• LÀM TRÒN( PERCENTRANK($B$5. $B$12,B5) *4, 0)

Điều này làm tròn kết quả lên đến 0 chữ số trong phần thập phân.
Kết quả. 1

• TỐI ĐA( LÀM TRÒN( PERCENTRANK($B$5. $B$12,B5) *4, 0),1)

Điều này cung cấp số lớn nhất dựa trên kết quả làm tròn.
Kết quả. 1

2. Kết hợp các hàm ROUNDUP, ROW và COUNT để chia dữ liệu

Hàm ROW trả về số hàng của tham chiếu

Hàm COUNT đếm số ô trong một phạm vi có chứa số

Ta sẽ kết hợp các hàm ROUNDUP, ROW, COUNT để lấy số nhóm tương ứng của từng dữ liệu số

Bước 1

• Chuyển đến ô C5 và đặt công thức sau

=ROUNDUP(ROW()/(COUNT(B:B)/1),0)

Bước 2

• Nhấn nút Enter để thực hiện công thức

Chúng ta có thể thấy rằng mỗi dữ liệu mang số nhóm tương ứng với chúng. Dữ liệu sẽ không bị chia tách khi áp dụng phương pháp này

Phân tích công thức

• ĐẾM(B. b)

Cái này đếm số ô chứa dữ liệu số của Cột B.
Kết quả. 8

• ĐẾM(B. B)/1

Chia kết quả trước đó cho 1.
Kết quả. 8

• HÀNG NGANG()

Đếm số hàng chứa dữ liệu.
Kết quả. 8

• HÀNG()/(ĐẾM(B. B)/1)

Chia kết quả của hàm ROW cho kết quả của hàm COUNT.
Kết quả. 1

• LÀM TRÒN(ROW()/(ĐẾM(B. B)/1),0)

Làm tròn kết quả trước đó.
Kết quả. 1

Sự kết luận

Trong bài viết này, chúng tôi đã mô tả cách chia dữ liệu thành các nhóm bằng nhau trong Excel. Tôi hy vọng điều này sẽ đáp ứng nhu cầu của bạn. Vui lòng xem trang web của chúng tôi ExcelDemy. com và đưa ra đề xuất của bạn trong hộp bình luận