Analisis Dataset Menggunakan Excel atau Google Sheets
Bersihkan dataset, hasilkan quick stats, bina Pivot Table dan sediakan visualisasi asas secara langkah demi langkah.
Satu fail dengan helaian Data Asal, Data Bersih, Log Pembersihan dan Analisis; dua Pivot Table; empat carta; serta rumusan dapatan.
A · Sediakan fail kerja
- Muat turun dataset dan buka menggunakan Excel, atau import fail ke Google Sheets.
- Tukar nama helaian asal kepada Data Asal.
- Gandakan helaian dan namakan salinan Data Bersih.
- Cipta helaian Log Pembersihan dan Analisis.
- Bekukan baris tajuk dan aktifkan Filter.
- Catat bilangan rekod menggunakan formula berikut.
=COUNTA(A:A)-1
Tolak satu untuk mengecualikan tajuk lajur. Laraskan lajur A kepada lajur pengenal yang sentiasa berisi.
B · Kesan data kosong
- Tambah lajur Status Data.
- Masukkan formula pada baris pertama data dan seret ke bawah.
- Tapis status Tidak Lengkap.
- Tentukan sama ada nilai perlu dilengkapkan, dikekalkan atau dikeluarkan.
- Catat keputusan dalam Log Pembersihan.
=IF(COUNTA(A2:F2)<6,"Tidak Lengkap","Lengkap")=IF(C2="","Kosong","Ada Data")=COUNTBLANK(C2:C1000)
C · Bersihkan teks dan kategori
- Tambah lajur sementara di sebelah lajur teks.
- Buang ruang dan aksara tersembunyi.
- Seragamkan huruf mengikut keperluan.
- Semak kategori secara manual supaya kategori berbeza tidak digabungkan.
- Salin hasil dan pilih Paste Special → Values.
=TRIM(B2)=CLEAN(B2)=TRIM(CLEAN(B2))=UPPER(B2)=LOWER(B2)=PROPER(B2)
D · Semak rekod pendua
- Bina ID gabungan daripada lajur yang sepatutnya unik.
- Kira kemunculan setiap ID.
- Tapis label Pendua dan bandingkan keseluruhan rekod.
- Padam atau gabungkan hanya rekod yang benar-benar sama.
- Catat bilangan rekod sebelum dan selepas pembersihan.
=TEXTJOIN("|",TRUE,A2:C2)=COUNTIF($G$2:G2,G2)=IF(COUNTIF($G$2:G2,G2)>1,"Pendua","Unik")
E · Seragamkan tarikh dan nombor
- Periksa sama ada tarikh dan nombor dijajarkan serta dibaca sebagai nilai.
- Tukar tarikh berbentuk teks kepada tarikh sebenar.
- Wujudkan lajur Tahun dan Bulan jika diperlukan.
- Tukar nombor berbentuk teks dan bundarkan nilai.
=DATEVALUE(D2)=YEAR(D2)=MONTH(D2)=TEXT(D2,"mmmm")=VALUE(E2)=ROUND(E2,2)
F · Hasilkan Quick Stats
| Statistik | Formula contoh |
|---|---|
| Bilangan | =COUNT(E2:E1000) |
| Jumlah | =SUM(E2:E1000) |
| Purata | =AVERAGE(E2:E1000) |
| Median | =MEDIAN(E2:E1000) |
| Minimum / maksimum | =MIN(E2:E1000) / =MAX(E2:E1000) |
| Sisihan piawai | =STDEV.S(E2:E1000) |
| Kiraan kategori | =COUNTIF(B2:B1000,"Kategori A") |
| Dua syarat | =COUNTIFS(B2:B1000,"Kategori A",C2:C1000,">=2024") |
| Jumlah kategori | =SUMIF(B2:B1000,"Kategori A",E2:E1000) |
| Peratus kategori | =COUNTIF(B2:B1000,"Kategori A")/COUNTA(B2:B1000) |
G · Bina dua Pivot Table
- Pilih keseluruhan Data Bersih.
- Excel: pilih Insert → PivotTable. Google Sheets: pilih Insert → Pivot table.
- Pivot 1: letakkan kategori pada Rows dan ukuran pada Values.
- Pilih Sum, Count atau Average berdasarkan persoalan.
- Pivot 2: letakkan Tahun pada Rows, kategori pada Columns dan ukuran pada Values.
- Tambah Filter atau Slicer jika sesuai.
- Bandingkan Grand Total Pivot dengan
=SUM(E2:E1000).
H · Hasilkan empat carta
- Bar chart: pilih Pivot 1 untuk membandingkan kategori.
- Line chart: gunakan tahun yang disusun menaik dan nilai trend.
- Pie chart: gunakan hanya beberapa kategori yang tidak bertindih; kira peratus dengan
=B2/SUM($B$2:$B$6). - Scatter plot: pilih dua lajur numerik; kira korelasi dengan
=CORREL(E2:E1000,F2:F1000). - Tambah tajuk, label paksi, unit, legenda dan sumber pada setiap carta.
I · Tulis dapatan
- Tulis tiga dapatan yang mengandungi angka.
- Nyatakan satu trend, satu anomali, satu batasan dan satu cadangan.
- Semak setiap angka terhadap formula atau Pivot Table.
Berdasarkan analisis terhadap [bilangan rekod], kategori [nama] mencatat [angka], bersamaan [peratus] daripada jumlah keseluruhan.