Excel成绩单

Question

所以，我还没有弄清楚如何做到这一点。

基本上，我想要这样的东西:

P1    P2    P3                TOTAL SCORE
--    --    --                P1   P2   P3
21    /     13                 1    2    0
/     17    10
6      7    /

因此，三列必须相互比较(“/”表示玩家没有玩过该游戏，但不必打印)，三列中最大的在总分选项卡。

另外，有没有比将一个单元格与另一个单元格进行比较更简单的方法呢？我的意思是，是否有可能拖动并标记所有三列上的所有单元格，并确保它们只比较同一行中三列中的单元格？

Answer 1

让我们假设数据显示为 Sheet1 中的图片(不要更改结构):

打开 Excel

按 ALT 和 F11 打开可视化编辑器

从 > 插入(在上部工具栏中)添加模块 - 模块(第三个选项)

粘贴以下代码并执行 Sub Evaluation()(当光标在 Sub Evaluation 时按 F5)

要存储 lastrow 以便从下一条记录继续，我使用 sheet2 range A1

尝试:

Option Explicit

Public Sub Process_Data(ByVal I_Value As Long)

Dim LastRow As Long
Dim i As Long
Dim CA As Integer
Dim CB As Integer
Dim CC As Integer

With Sheet1

    LastRow = .Range("A" & Rows.Count).End(xlUp).Row

    For i = I_Value To LastRow '<= Lets say that the first score is at sheet1 column A row 3.LastRow represent the row of the last data in column A
        CA = 0
        CB = 0 '<= Every time that i change value we zero our variables to get the new value
        CC = 0
        If .Range("A" & i).Value = "/" Then '<= Check if there is a number or "/".if there is "/" we zero variable
            CA = 0
        Else
            CA = .Range("A" & i).Value
        End If
        If .Range("B" & i).Value = "/" Then
            CB = 0
        Else
            CB = .Range("B" & i).Value
        End If
        If .Range("C" & i).Value = "/" Then
            CC = 0
        Else
            CC = .Range("C" & i).Value
        End If

        If CA > CB And CA > CC Then ' <= Check which number is bigger
            .Range("E3").Value = .Range("E3").Value + 1 '<= At one point to each category
        ElseIf CB > CA And CB > CC Then
            .Range("F3").Value = .Range("F3").Value + 1
        ElseIf CC > CA And CC > CB Then
            .Range("G3").Value = .Range("G3").Value + 1
        End If
    Next i
End With

End Sub

Sub Evaluation()

Dim Value As Long
Dim LastRow As Long

LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

If (LastRow = 2) Or (LastRow = Sheet2.Range("A1").Value) Then '<= Check if the table has new data
    Exit Sub
Else
    If Sheet2.Range("A1").Value = "" Then '<=Check which value will adopt be i
        Value = 3
    Else
        Value = Sheet2.Range("A1").Value + 1
    End If
End If

Call Process_Data(I_Value:=Value)

Sheet2.Range("A1").Value = Sheet1.Range("A" & Rows.Count).End(xlUp).Row '<= Record the lastrow processed out

End Sub