Panduan Membuat Plot Batang & Daun di Excel 2007

Pengantar

Plot batang & daun (stem and leaf plot) merupakan diagram statistik yang menampilkan setiap nilai asli sehingga tidak hilang seperti pada diagram pie atau histogram.

Langkah‑Langkah

1. Persiapan Worksheet

Ubah nama worksheet kosong pertama menjadi "Data".

Ubah nama worksheet kosong kedua menjadi "Stem".

2. Masukkan Data

Masukkan daftar angka di kolom A pada worksheet "Data".

3. Jalankan VBA

Tekan Alt + F11 untuk membuka editor Visual Basic.

Di panel kiri, klik dua kali "ThisWorkbook" di bawah Microsoft Excel Objects, lalu tempel kode berikut:

Sub StemAndLeaf()
    dataColumn = 1
    'Clean everything out of the Stem worksheet. Worksheets("Stem").Cells.Clear
    'Look at the Data worksheet. Worksheets("Data").Activate
    'Find the maximum value. rowPointer = 2
    Do Until Cells(rowPointer, 1).Value = ""
        rowPointer = rowPointer + 1
    Loop
    Maximum = Cells(rowPointer - 1, dataColumn).Value
    'Set the divisor to strip off leaves. divisor = 1
    Do Until Maximum / divisor <= 10
        divisor = divisor * 10
    Loop
    'If the first digit of the largest value is less than 5, then
    'use a smaller divisor. Otherwise you could end up with four or fewer rows in the plot.
    If Fix(Maximum / divisor) < 5 Then divisor = divisor * 10
    'Calculate the top stem’s value. topStem = Fix(Maximum / divisor)
    'Set up the Stem worksheet. Worksheets("Stem").Activate
    Cells(1, 1).Value = "Count"
    Cells(1, 2).Value = "Stem"
    Cells(1, 3).Value = "Leaves"
    For rowPointer = 2 To topStem + 2
        Cells(rowPointer, 2).Value = rowPointer - 2
        Cells(rowPointer, 3).Value = "|"
    Next rowPointer
    'Calculate the counts. The following code is slower than it needs to be,
    'but a faster code would be harder to read and understand.
    Worksheets("Data").Activate
    rowPointer = 2
    Do Until Cells(rowPointer, dataColumn).Value = ""
        measurement = Cells(rowPointer, dataColumn).Value
        Stem = Fix(measurement / divisor)
        Worksheets("Stem").Cells(Stem + 2, 1).Value = Worksheets("Stem").Cells(Stem + 2, 1).Value + 1
        rowPointer = rowPointer + 1
    Loop
    'Calculate the shrink factor. Worksheets("Stem").Activate
    maximumCount = 0
    For rowPointer = 2 To topStem + 2
        If Cells(rowPointer, 1).Value > maximumCount Then maximumCount = Cells(rowPointer, 1).Value
    Next rowPointer
    shrinkFactor = Fix(maximumCount / 50)
    If shrinkFactor < 1 Then shrinkFactor = 1
    Cells(1, 4).Value = "Each digit represents" + Str(shrinkFactor) + " cases."
    'Return to the data, and fill the leaves in light of the values in the data.
    Worksheets("Data").Activate
    rowPointer = 2
    Do Until Cells(rowPointer, dataColumn).Value = ""
        measurement = Cells(rowPointer, dataColumn).Value
        Stem = Fix(measurement / divisor)
        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
    'Get to the Stem worksheet. Worksheets("Stem").Activate
End Sub

Tekan F5 untuk menjalankan kode. Plot batang & daun akan muncul di worksheet "Stem".

Tips

Jika data berubah dan Anda perlu membuat plot baru, cukup jalankan macro StemAndLeaf lagi.