________________________________________
Bạn muốn lập một giải đấu vòng tròn? nhưng lại phải ghi vở hay làm thủ công? nếu vậy đó không phải là cách, hãy dùng excel để tạo lịch và làm nó. Bài viết sau tôi sẽ hướng dẫn cho bạn làm từ A-Z hoàn chỉnh nhất.
Bước 1: Xác định số đội tham gia
Khi bắt đầu, bạn cần xác định số lượng các đội tham gia cuộc thi. Ở ví dụ này. Tôi giả tưởng rằng bạn xác định 8 đội tham gia, 8 đội này là 8 đội tuyển quốc gia đã từng vô địch World Cup trong lịch sử. Và giải này do bạn đặt tên là: FIFA World League Champions Cup.
Bước 2: Xác định thể thức giải đấu
Bạn phải xác định thể thức giải. Ví dụ: bạn chọn 8 đội thi đấu vòng tròn bao gồm cả lượt đi và lượt về, như vậy sẽ có 7 vòng ở lượt đi và 7 vòng ở lượt về, mỗi vòng có 4 cặp đấu. Tính ra cả mùa giải sẽ có 4x14=56 trận đấu.
Và vấn đế quan trọng nhất là bạn phải sắp xếp lịch thi đấu cho nó. Việc sắp xếp lịch thi đấu vòng tròn cho 3, 4 đội là rất đơn giản. Bạn cho dù không rành về toán học vẫn có thể tạo ra lịch cho nó.
Ví dụ với 3 đội a, b, c, ta dễ dàng xếp lịch lượt đi cho nó như sau: vòng 1: a vs b (c nghỉ); vòng 2: a vs c (b nghỉ); vòng 3: b vs c (b nghỉ). Với 4 đội, thì dễ thấy với quy tắc xếp cho 3 đội bên trên, ta chỉ cần them đội d vào làm đối thủ cho các đội được “nghỉ” là xong.
Nhưng nếu số đội tăng lên 8 đội hay 10, 12, 16, 18, 20 hay 100 đội thì sẽ như thế nào? Câu trả lời là bạn sẽ không dễ để sắp xếp cho các đội gặp nhau mà không bị trùng lặp cặp đấu ở mỗi vòng đâu!
Nếu số đội lớn, cỡ 16 hay 20 đội trở lên, người ta sẽ sử dụng các công cụ toán học đồng dư hay toán hình học, ánh xạ…v.v hay cả phần mềm để tạo lịch thi đấu. Tôi giới thiệu cho bạn các cách sau:
Cách 1: Dùng đồng dư
Ta thử sắp xếp lịch thi đấu cho sheet có sáu đội, sáu đội này thi đấu theo thể thức đấu vòng tròn một lượt. Kí hiệu x là số phiên hiệu các đội x ∈{1, 2,...,6}, r kí hiệu x vòng thi đấu r ∈{1, 2,...,5} như vậy mỗi đội phải tiến hành năm vòng đấu. Dưới đây là sheet sắp xếp lịch thi đấu cho sáu đội trong năm vòng thi đấu. Trong sheet có r hàng, x cột, số phiên hiệu mỗi đội là y, số vòng đấu là r.
Sheet lịch thi đấu theo cách dùng đồng dư
Trước hết xin giới thiệu khái niệm “đồng dư”. Với hai số nguyên a, b, nếu chọn được một số msao cho khi a, b chia cho m (số chia) thì ta được một thương số là số nguyên nhưng phép chia có số dư bằng nhau. Ví dụ với hai số a = 34 và b = 12 và nếu chọn m = 11 thì số dư của hai phép chia bằng nhau và bằng 1. Người ta nói a và b có mối liên quan với nhau qua đồng dư m và viết: a ≡ b (mod m). Ta đọc a và b đồng dư theo mođun m. Khái niệm đồng dư ra đời rất sớm từ thế kỉ thứ V. Ở Trung Quốc khái niệm đồng dư xuất hiện đầu tiên trong bộ sách “Sách toán Tôn Tử”. Trong đời sống hằng ngày chúng ta cũng thường gặp hiện tượng đồng dư. Ví dụ trong một tháng nào đó nếu ngày 2 là thứ tư thì các ngày 9, 16, 23 cũng là ngày thứ tư. Vì thế các số 9, 16, 23 liên quan với nhau qua đồng dư theo mođun 7.
Nói chung để xếp lịch thi đấu theo thể thức thi đấu vòng tròn có N đội tham gia chỉ cần ở vòng đấu thứ r ta chọn giá trị y thế nào cho x + y = r (mod N - 1) là được.
Như trong ví dụ trên, ta phải chọn y thế nào để x + y chia 5 có số dư bằng r là được.
Ví dụ ở vòng đấu thứ nhất (r = 1, x+y = 6) nên với các giá trị x = 1; y = 5; x =2; y = 4 thì đều đáp ứng được yêu cầu. Nhưng x = 3; y = 3 thì gặp trường hợp đội thứ ba lại đấu với chính mình nên không thể được. Vì vậy trong trường hợp này, ta quy ước chọn đội cuối cùng là đội số 6 thi đấu với đội 3. Như vậy ở hàng thứ nhất ta giải quyết xong.
Ở vòng thi đấu thứ hai (r = 2, x + y = 7), ở hàng thứ hai không gặp trở ngại gì.
Ở vòng đấu thứ ba (r = 3; x + y = 8), khi x = 1, y = 7 vì không có đội bóng có phiên hiệu này, nên trong trường hợp này ta chọn x + y = r thì x = 1, y = 2; x = 2, y = 1. Sau đó lại quay về x + y = 8 thì x = 3, y = 5; khi x = 4 thì y = 4 nên bây giờ y không thể bằng 4 mà lấy bằng 6.
Bằng cách tương tự người ta có thể lập lịch thi đấu cho thể thức thi đấu vòng tròn của một sheet có 6 đội.
Như vậy nếu số các đội ghi tên thi đấu là số chẵn, thì mỗi đội trong một vòng đấu đều có đấu thủ khác nhau. Tuy nhiên đây không phải là lịch đấu duy nhất. Nếu số đội tham gia thi đấu là số lẻ, thì cách xếp lịch thi đấu như vừa trình bày sẽ không thích hợp.
Cách 2: Dùng toán hình học
Ví dụ này là xếp lịch vòng tròn cho 10 đội.
Hãy đặt mỗi đội tương ứng với 1 đỉnh của hình 9 cạnh đều và tâm của nó.
Mỗi lượt thi đấu, ta dựng các đoạn thẳng song song nối các cặp đỉnh với nhau và đoạn thẳng nối tâm với đỉnh bị lẻ.
Ta có lịch thi đấu như sau (mỗi dòng là 1 vòng đấu)
(1,2)(1,3)(1,4)(1,5)(1,6)
(1,7)(1,8)(1,9)(1,10)(3,9)
(4,9)(2,3)(2,4)(2,5)(2,6)
(2,7)(2,8)(2,9)(4,8)(5,8)
(5,9)(6,,9)(3,4)(3,5)(3,6)
(3,7)(3,8)(5,7)(6,7)(6,8)
(7,8)(7,9)(8,9)(4,5)(4,6)
(4,7)(6,10)(2,10)(7,10)(3,10)
(8,10)(4,10)(9,10)(5,10)(5,6)
Cách 3: Dùng toán ánh xạ
Ta có các cặp đấu:
(1,8),(2,7),(3,6),(4,5),(9,10)
Dễ thấy là đáp án của cách 1 và cách 2 giống nhau.
Như vậy, theo các cách trên, bạn cần phải am hiểu và tự xây dựng nên lịch thi đấu. Sẽ rất tốt nếu bạn tự làm được, nhưng nếu không, tôi cung cấp cho bạn 2 trang web mà bạn có thể tự điền tên các đội, số đội vào và chỉ cần click, webstie sẽ cho ra kết quả sắp xếp lịch thi đấu từ 3 đến 100 đội.
Website 1: League & Tournament Scheduler | LeagueLobster | LITE
Website 2: Create Your Own Soccer League Fixtures and Table » ExcelTemplate.net
Chỉ cần làm theo website yêu cầu, bạn sẽ có kết quả, hãy copy nó về excel!
Bước 3: Xác định quy tắc xếp hạng
Trong giải đấu giả tưởng dành cho 8 đội VĐTG này, bạn sẽ sử dụng các quy tắc sau để xếp hạng:
Cách tính điểm xếp hạng
Một đội sẽ giành được 3 điểm cho mỗi trận thắng, 2 điểm cho trận thắng trên loạt sút luân lưu (nếu có), 1 điểm cho trận hòa và 0 điểm khi thất bại
Quy tắc xếp hạng:
1. Đội đạt được nhiều điểm nhất sẽ vô địch, các đội ít điểm hơn được xếp tiếp theo sau.
2. Nếu có hai hoặc nhiều đội có cùng số điểm, hiệu số bàn thắng bàn thua cao hơn được xếp trên.
3. Nếu có hai hoặc nhiều đội có cùng tiêu chí 1, 2, tổng số bàn thắng ghi được cao hơn được xếp trên.
4. Nếu có hai hoặc nhiều đội có cùng tiêu chí 1, 2, 3, tổng số bàn thua thấp hơn được xếp trên.
5. Nếu có hai hoặc nhiều đội có cùng tiêu chí 1, 2, 3, 4 thì thành tích đối đầu tốt hơn được xếp trên.
6. Nếu có hai hoặc nhiều đội có cùng tất cả các tiêu chí thì tổ chức đá 1 trận phân hạng, nếu hòa sẽ đá luân lưu.
Bạn có thể thêm nhiều quy tắc tùy theo tình hình và mong muốn của bạn, bởi vì một khi bạn hiểu rõ cách thức xây dựng giải đấu thì bất kỳ quy tắc hay tiêu chí nào bạn đưa ra cũng có thể được diễn giải thành công thức excel một cách dễ dàng.
Bước 3: Tạo sheet Lịch thi đấu
Bây giờ, bạn phải tạo sheet có tên là Lịch thi đấu (gồm cả chỗ để ghi tỉ số, kết quả trận đấu)
Sẽ có 56 trận đấu qua 14 vòng đấu, có cột A đến cột H như ảnh.
Bước 4: Tạo sheet BXH
Tạo sheet BXH như ảnh
Bước 5: Điền tỉ số trận đấu vào lịch thi đấu
Trong ví dụ này, bạn điền kết quả của tất cả 56 trận đấu trong sheet Lịch thi đấu vào, tôi đã điền trong file đính kèm bên dưới bài viết.
Sau khi làm xong các bước, hãy bắt đầu thôi!
Bước 6: Tạo công thức tính số trận trắng trên sân nhà (ô L4)
Bây giờ, bạn sẽ tạo công thức tính tổng số trận thắng trên sân nhà của Brazil, đội đầu tiên trong sheet BXH. Như bạn có thể thấy trong sheet lịch thi đấu, bạn sẽ dùng hàm SUMPRODUCT để tính số các trận thắng trên sân nhà của Brazil như sau:
L4 = SUMPRODUCT((Lịch thi đấu! $ D $ 2: $ D $ 71 = $ B4) * (Lịch thi đấu! $ E $ 2: $ E $ 71> Lịch thi đấu! $ F $ 2: $ F $ 71))
Điều kiện A = Lịch thi đấu! $ D $ 2: $ D $ 71 = $ B4 được tạo để tìm một đội có tên Brazil trong cột D
Điều kiện B = Lịch thi đấu! $ E $ 2: $ E $ 71> Lịch thi đấu! $ F $ 2: $ F $ 71 để tìm các ô trong đó giá trị tỉ số của cột E lớn hơn cột F, có nghĩa là Brazil giành chiến thắng.
Nhân 2 điều kiện lại, nó sẽ cho ra số trận thắng trên sân nhà của Brazil.
Việc sử dụng dấu $ là để ngăn thay đổi tham chiếu ô khi sao chép công thức sang các ô khác.
Bước 7: Tạo công thức tính số trận hòa trên sân nhà (ô M4)
ô M4 có hai điều kiện phải được đáp ứng, nhưng có một điều kiện phải được xem xét lại, công thức tính số trận hòa trên sân nhà của Brazil như sau:
M4 = SUMPRODUCT ((Lịch thi đấu! $ D $ 2: $ D $ 71 = $ B4) * (Lịch thi đấu! $ E $ 2: $ E $ 71 = Lịch thi đấu! $ F $ 2: $ F $ 71) * (Lịch thi đấu! $ E $ 2: $ E $ 71 <> “”))
Điều kiện A = Lịch thi đấu! $ D $ 2: $ D $ 71 = $ B4 được tạo để tìm một đội có tên Brazil trong cột D
Điều kiện B = Lịch thi đấu! $ E $ 2: $ E $ 71 = Lịch thi đấu! $ F $ 2: $ F $ 71 được tạo để tìm các ô tỉ số có giá trị là bằng nhau, có nghĩa là hòa.
Điều kiện C = Lịch thi đấu! E $ 2: E $ 71 <> “0” (không cần gõ số 0 cũng được) được tạo để ngăn công thức đưa các ô trống vào tính toán vì các ô trống sẽ được excel coi là 0 và nó sẽ cho kết quả, giả dụ kết quả trận Brazil vs England để trống thì excel nó sẽ hiểu là 0-0 (1 trận hòa 0-0).
Bước 8: Tạo công thức tính số trận thua trên sân nhà (ô N4)
Sau bước tính số trận thắng và hòa trên sân nhà, tôi nghĩ bạn biết phải làm gì trong ô N.
Công thức hoàn chỉnh là:
N4 = SUMPRODUCT ((Lịch thi đấu! $ D $ 2: $ D $ 71 = $ B4) * (Lịch thi đấu! $ E $ 2: $ E $ 71 <Lịch thi đấu! $ F $ 2: $ F $ 71))
Bước 9: Tạo công tính tổng số bàn thắng ghi được trên sân nhà (ô F4)
Đối với ô này, hãy sử dụng hàm SUMIF vì bạn cần tính tổng số bàn thắng ghi được cho từng đội. Nói tới tính tổng, bạn sẽ nghĩ đến hàm SUM, nhưng nếu SUM thì nó lại tính tổng tất cả các bàn thắng ghi được trong cột E của sheet lịch thi đấu mà không phân biệt đội nào. Do chúng ta cần lấy tổng bàn thắng cho đội Brazil và kéo công thức tính cho 7 đội còn lại tương ứng. Vì vậy, công thức sẽ là:
O4 = SUMIF (Lịch thi đấu! $ D $ 2: $ D $ 71, $ B4, Lịch thi đấu! $ E $ 2: $ E $ 71)
Bước 10: Tạo công thức tính tổng bàn thua trên sân nhà (ô P4)
Sao chép công thức ở ô bàn thắng sân nhà (ô O4) và dán nó vào ô P4. Thay đổi tham chiếu cột E thành tham chiếu cột F.
P4 = SUMIF (Lịch thi đấu! $ D $ 2: $ D $ 71, $ B4, Lịch thi đấu! $ F $ 2: $ F $ 71)
Bước 11: Tạo công thức tính hiệu số bàn thắng bàn thua, tổng điểm trên sân nhà và tổng số trận trên sân nhà (ô Q4, R4 và P4)
3 ô này thì đơn giản rồi. Bạn dễ dàng gõ vào. Nhưng tôi vẫn ghi công thức trong ví dụ của tôi ra đây:
ô Q4 = 04-P4
ô R4 =L4 * 3 + M4 * 1 (đúng hơn nữa thì thêm +N4*0)
P4 = SUM (L4: N4)
Bạn đã hoàn thành việc tạo công thức cho các cột số liệu trên sân nhà của Brazil. Bạn hãy kiểm tra và so sánh kết quả thủ công thử xem!
Ngon rồi, giờ thì tính cho 7 đội khác thì chỉ cần sao chép công thức của ô đội Brazil và dán nó vào 7 hàng bên dưới (hay nói gọn là “kéo công thức xuống”)
Bước 12: Tạo công thức cho các cột bên sân khách
Đơn giản thôi, giờ hãy thử lặp lại bước 6 - 12. Cách sử dụng công thức được sử dụng là như bên mảng sân nhà, nhưng hãy sửa lại logic công thức mà thôi, hãy xem Brazil như là một đội khách (làm ngược từ phải qua trái, tức là cột G so qua cột D bên sheet Lịch thi đấu). Tôi nghĩ sau khi bạn đã làm được mảng công thức cho sân nhà, thì bạn sẽ có thể tự mình hoàn thành mảng sân khách. Tôi cũng ghi ra đây công thức của các ô bên mảng sân khách:
Ô tổng số trận trên sân khách: W4 =SUM(X4:Z4)
Ô tổng số trận thắng trên sân khách: X4=SUMPRODUCT(('Lich thi dau'!$G$3:$G$71=$B4)*('Lich thi dau'!$F$3:$F$71>'Lich thi dau'!$E$3:$E$71))
Ô tổng số trận hòa trên sân khách: Y4 =SUMPRODUCT(('Lich thi dau'!$G$3:$G$71=$B4)*('Lich thi dau'!$F$3:$F$71='Lich thi dau'!$E$3:$E$71))
Ô tổng số trận thua trên sân khách: Z4 = =SUMPRODUCT(('Lich thi dau'!$G$3:$G$71=$B4)*('Lich thi dau'!$F$3:$F$71<'Lich thi dau'!$E$3:$E$71))
Ô tổng số bàn thắng ghi được trên sân khách: AA4 =SUMIF('Lich thi dau'!$G$3:$G$71,$B4,'Lich thi dau'!$F$3:$F$71)
Ô tổng số bàn thua trên sân khách: AB4 =SUMIF('Lich thi dau'!$G$3:$G$71,$B4,'Lich thi dau'!$E$3:$E$71)
Ô hiệu số bàn thắng bàn thua trên sân khách: AC=AA4-AB4
Ô tổng điểm trên sân khách: AD =X4*3+Y4
Bước 13: Tạo công thức bên mảng toàn mùa giải
Càng đơn giản nữa, các cột ở mảng tổng toàn mùa giải chính bằng tổng của các cột bên mảng sân nhà và sân khách. Bạn sẽ dễ dàng gõ vào. Tôi cũng ghi công thức ra đây:
Ô tổng số trận đã đá: G4= =O4+W4
Ô tổng số trận thắng: H4 =P4+X4
Ô tổng số trận hòa: I4 =Q4+Y4
Ô tổng số trận thua: J4 =R4+Z4
Ô tổng số bàn thắng: K4 =S4+AA4
Ô tổng số bàn thua: L4 =T4+AB4
Ô hiệu số bàn thắng/thua: M4 =K4-L4
Ô tổng điểm của cả mùa: N4 =K4-L4
Bước 14: Tạo công thức tính xếp hạng
Công thức cột A4 =RANK(N4,$N$4:$N$11) ???
Một vấn đề xảy ra, như trong ảnh, Brazil và Italy có cùng điểm nên cùng thứ hạng là 3, vì vậy, bạn phải tạo thêm cột để so sánh tiếp và tìm ra đội nào ở vị trí thứ 4, bạn cần dựa vào các tiêu chí tính xếp hạng của giải đã đề cập phần đầu. Cho nên công thức cột A4 không phải là =RANK(N4,$N$4:$N$11) đâu. Do đó việc xếp hạng theo các tiêu chí khác không hề đơn giản như mọi người nghĩ!
Trước khi tạo công thức, bạn chèn bốn cột mới giữa cột B và cột C. Chọn cột C, nhấp chuột phải, nhấp và chọn insert. Chèn bốn lần, bạn sẽ có các cột trống từ C đến F giữa cột C và G. Đặt tên cho nó là so sánh xếp hạng như ảnh trên.
Bước 15: Tạo công thức xếp hạng dựa trên các tiêu chí phụ.
Bây giờ, nhắc lại quy tắc xếp hạng nếu có các đội cùng chỉ số
Quy tắc xếp hạng:
1. Đội đạt được nhiều điểm nhất sẽ vô địch, các đội ít điểm hơn được xếp tiếp theo sau.
2. Nếu có hai hoặc nhiều đội có cùng số điểm, hiệu số bàn thắng bàn thua cao hơn được xếp trên.
3. Nếu có hai hoặc nhiều đội có cùng tiêu chí 1, 2, tổng số bàn thắng ghi được cao hơn được xếp trên.
4. Nếu có hai hoặc nhiều đội có cùng tiêu chí 1, 2, 3, tổng số bàn thua thấp hơn được xếp trên.
5. Nếu có hai hoặc nhiều đội có cùng tiêu chí 1, 2, 3, 4 thì thành tích đối đầu tốt hơn được xếp trên.
6. Nếu có hai hoặc nhiều đội có cùng tất cả các tiêu chí thì tổ chức đá 1 trận phân hạng, nếu hòa sẽ đá luân lưu.
Như vậy, Brazil và Italy cùng tiêu chí 1, vậy nên công thức =RANK(N4,$N$4:$N$11) phải dành cho cột C4, do đó bạn cần xét đến tiêu chí 2. Vì vậy, hàm excel phù hợp nhất để làm điều này là hàm SUMPRODUCT.
Chuyển đến ô D4. Công thức bạn phải sử dụng ở đây là:
D4=SUMPRODUCT(($C$4:$C$11=$C4)*($M$4:$M$11>M4))
Điều kiện A = $ C $ 4: $ C $ 11 = $ C4, được tạo để tìm giá trị trong cột C có cùng giá trị với giá trị C4 (so sánh điểm)
Điều kiện B = $ M $ 4: $ M $ 11> M4, được tạo để tìm giá trị trong cột M lớn hơn giá trị trong M4 (nếu điểm bằng thì so sánh hiệu số, đội nào có hiệu số nhỏ hơn thì cộng thêm 1 – nghĩa là xếp hạng tụt xuống 1 bậc)
Giá trị D4 sẽ là tổng của tích giữa điều kiện A và B.
Sau khi D4 xong, ta thấy Brazil có hiệu số thấp hơn Italy nên bị cộng thêm 1, nghĩa là xếp hạng của họ thấp hơn Italy 1 bậc.
Suy ra công thức ở ô A4=SUM(C4:F4).
Tôi mở rộng thêm, giả dụ tôi sửa hiệu số 2 đội này bằng nhau, thì ta sẽ tiếp tục so sánh tới tổng số bàn thắng ghi được (tiêu chí 3).
Công thức ô E4 là =SUMPRODUCT(($C$4:$C$11=$C4)*($M$4:$M$11=$M4)*($K$4:$K$11>$K4)).
Tiếp tục, nếu tôi sửa 2 đội bằng cả số bàn thắng, thì ta so tiếp số bàn thua,
công thức ô F4 là =SUMPRODUCT(($C$4:$C$11=$C4)*($M$4:$M$11=$M4)*($K$4:$K$11=$K4)*($L$4:$L$11<$L4)) – dấu < <$L4 biểu thị việc đội nào có số bàn thua ít hơn thì xếp hạng cao hơn, đội có bàn thua nhiều hơn thì phải bị cộng thêm 1 (tụt 1 bậc).
Nếu cả 5 tiêu chí bằng nhau, ta so thành tích đối đầu.
Như vậy, bạn đã hoàn thành xong. Nếu cần, theo tôi, bạn nên tạo thêm 1 sheet BXH chính thức, với mẫu y như sheet BXH nhưng xóa 4 cột so sánh xếp hạng đi. Rồi dùng hàm VLOOKUP điền vào các ô để bạn có được 1 BXH hoàn chỉnh với vị trí xếp hạng các đội từ 1-8 nằm ở vị trí từ trên xuống dưới mà không cần phải lọc, sắp xếp.
File excel của giải đấu giả tưởng trên: download
Viết bởi: quanphamkt
