如何使用Excel表格对Excel工作表中的行进行排序并打印到文件？

用户名

我有一张叫学生的桌子。我正在尝试根据他的成绩打印相应的学生姓名。我想使用Excel宏打印到文件。

我的表格包含标题，名称为学生姓名1、2、3等，并带有相应的标记1、2、3、4和“-”。

我想在VB中编写函数以将对应的行作为每个主题（0,1,2,3，... 7）进行排序，还将值打印到文件中。

输出（file.txt）

NULL,NULL,NULL,NULL,NULL
STUDENT 2 ,STUDENT 5 ,STUDENT 4 ,NULL,NULL
STUDENT 4,STUDENT 5,NULL,NULL,NULL
etc..

行中的列应按升序排序，如果任何列中的“-”应作为剩余值打印为NULL。

我已经写了

Sub test()
'create and write into file txt
Dim fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")

    Dim Fileout As Object
    Set Fileout = fso.CreateTextFile("MyFile.txt", True, True)

    'Write logic for sorting 

    Fileout.Close

End Sub

如何使用VB脚本在Excel中排序并打印这些行？

普拉格梅特克

这是一种实现方式，可以使其适应您的需求：

' Naive O(N^2) sort
Sub Sort(arr() As Long, students() As String)
    If UBound(arr) <= 1 Then Exit Sub

    Dim i As Long, j As Long
    For i = 0 To UBound(arr) - 1
        ' Look for the minimum index in the sub-array going from i
        Dim indexOfMin As Long
        indexOfMin = i
        For j = i To UBound(arr)
            If arr(j) < arr(indexOfMin) Then
                indexOfMin = j
            End If
        Next j
        ' Put the minimum mark at the beginning of the sub-array
        Dim tmp As Variant
        tmp = arr(i)
        arr(i) = arr(indexOfMin)
        arr(indexOfMin) = tmp
        ' Put the student with the minimum value at the beginning of the students sub-array
        tmp = students(i)
        students(i) = students(indexOfMin)
        students(indexOfMin) = tmp
    Next i
End Sub

Sub SortAndSave()
    Dim dataRange As Range
    Set dataRange = Range("A1:F9")

    Dim data As Variant
    data = dataRange.Value

    Dim NSubject As Long, NStudents As Long
    NSubject = UBound(data, 1) - 1
    NStudents = UBound(data, 2) - 1

    Dim text As String
    Dim i As Long, j As Long
    For i = 1 To NSubject
        ' Read marks and students names
        Dim subjectMarks() As Long
        ReDim subjectMarks(0 To NStudents - 1)
        Dim students() As String
        ReDim students(0 To NStudents - 1)
        For j = 1 To NStudents
            ' Use a big enough number 999 so that students with no mark will be pushed to the end
            subjectMarks(j - 1) = IIf(data(i + 1, j + 1) <> "-", data(i + 1, j + 1), 999)
            students(j - 1) = data(1, j + 1)
        Next j

        ' Sort marks and students
        Sort subjectMarks, students

        ' Build display row for subject
        Dim row As String
        row = ""
        For j = 1 To NStudents
            ' If there is a mark render the student name
            If subjectMarks(j - 1) <> 999 Then
                row = row & students(j - 1)
            ' Otherwise render NULL
            Else
                row = row & "NULL"
            End If
            ' Add a comma if not the latest
            If j <> NStudents Then
                row = row & ","
            End If
        Next j
        text = text & row
        ' Add a \r\n if not the latest
        If i <> NSubject Then
            text = text & vbCrLf
        End If
    Next i
End Sub

结果：

NULL,NULL,NULL,NULL,NULL
STUDENT 2,STUDENT 5,STUDENT 4,NULL,NULL
STUDENT 4,STUDENT 5,NULL,NULL,NULL
STUDENT 4,STUDENT 5,NULL,NULL,NULL
STUDENT 2,STUDENT 5,STUDENT 4,NULL,NULL
STUDENT 5,STUDENT 4,STUDENT 2,STUDENT 1,NULL
STUDENT 5,STUDENT 4,STUDENT 2,STUDENT 1,NULL
STUDENT 4,STUDENT 5,NULL,NULL,NULL

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。