Cara Membuat Plot Stem dan Leaf di Excel 2007

Plot stem dan leaf (juga dikenali sebagai stemplot) ialah satu jenis diagram statistik yang mempamerkan setiap data individu, berbeza dengan carta pai atau histogram yang menyembunyikan nilai terpisah.

Berikut ialah panduan langkah demi langkah untuk membuat plot stem dan leaf menggunakan VBA dalam Excel 2007.

Persediaan

1. Buka Excel 2007 dan buat dua lembar kerja kosong.

• Namakan lembar pertama Data (klik tab bawah dan taip nama baru).
• Namakan lembar kedua Stem.

2. Masukkan senarai nombor anda di lajur A lembar Data.

Mengatur VBA

1. Tekan Alt + F11 untuk membuka Editor Visual Basic.

2. Di panel kiri, klik dua kali This Workbook di bawah Microsoft Excel Objects.

3. Tampal kod VBA berikut ke dalam tetingkap kod:

Sub StemAndLeaf()
    Dim dataColumn As Integer
    dataColumn = 1

    'Kosongkan lembar Stem
    Worksheets("Stem").Cells.Clear

    'Cari nilai maksimum dalam Data
    Dim rowPointer As Long
    rowPointer = 2
    Do Until Cells(rowPointer, 1).Value = ""
        rowPointer = rowPointer + 1
    Loop
    Dim Maximum As Double
    Maximum = Cells(rowPointer - 1, dataColumn).Value

    'Tetapkan divisor untuk memisahkan stem dan leaf
    Dim divisor As Double
    divisor = 1
    Do Until Maximum / divisor <= 10
        divisor = divisor * 10
    Loop
    If Fix(Maximum / divisor) < 5 Then divisor = divisor * 10

    Dim topStem As Long
    topStem = Fix(Maximum / divisor)

    'Sediakan lembar Stem
    Worksheets("Stem").Activate
    Cells(1, 1).Value = "Count"
    Cells(1, 2).Value = "Stem"
    Cells(1, 3).Value = "Leaves"
    Dim i As Long
    For i = 2 To topStem + 2
        Cells(i, 2).Value = i - 2
        Cells(i, 3).Value = "|"
    Next i

    'Kira kira bilangan setiap stem
    rowPointer = 2
    Do Until Cells(rowPointer, dataColumn).Value = ""
        Dim measurement As Double
        measurement = Cells(rowPointer, dataColumn).Value
        Dim Stem As Long
        Stem = Fix(measurement / divisor)
        Worksheets("Stem").Cells(Stem + 2, 1).Value = \
            Worksheets("Stem").Cells(Stem + 2, 1).Value + 1
        rowPointer = rowPointer + 1
    Loop

    'Cari faktor pengecilan bagi menyusun leaf
    Dim maximumCount As Long
    maximumCount = 0
    For i = 2 To topStem + 2
        If Cells(i, 1).Value > maximumCount Then
            maximumCount = Cells(i, 1).Value
        End If
    Next i
    Dim shrinkFactor As Long
    shrinkFactor = Fix(maximumCount / 50)
    If shrinkFactor < 1 Then shrinkFactor = 1
    Cells(1, 4).Value = "Setiap digit mewakili " & shrinkFactor & " kes."

    'Isi leaf berdasarkan data
    rowPointer = 2
    Do Until Cells(rowPointer, dataColumn).Value = ""
        measurement = Cells(rowPointer, dataColumn).Value
        Stem = Fix(measurement / divisor)
        Dim leaf As Long
        leaf = measurement - Stem * divisor
        leaf = Fix(leaf * 10 / divisor)
        Worksheets("Stem").Cells(Stem + 2, 3).Value = \
            Worksheets("Stem").Cells(Stem + 2, 3).Value & Trim(Str(leaf))
        rowPointer = rowPointer + shrinkFactor
    Loop

    Worksheets("Stem").Activate
End Sub

Jalankan Kod

Tekan F5 untuk mengeksekusi makro. Plot stem dan leaf akan muncul di lembar Stem.

Petua

Jika anda mengubah data dan ingin menghasilkan plot baru, cukup jalankan makro StemAndLeaf sekali lagi.