Bulan 3: Spreadsheet untuk Analisis Data

Struktur sheet "DataNilai":
A: Nama Siswa
B: Kelas           (10A / 10B / 11A / 11B)
C: Mata Pelajaran  (Matematika / IPA / IPS / Bahasa Indonesia / Inggris)
D: Nilai UTS       (0-100)
E: Nilai UAS       (0-100)
F: Nilai Akhir     =ARRAYFORMULA(IF(A2:A="","",D2:D*0.4+E2:E*0.6))

Isi minimal 50 baris dengan data realistis, mix acak antar kelas.

Formula QUERY yang harus Abbas tulis (4 query berbeda):

1. Rata-rata Nilai Akhir per Kelas, diurutkan dari tertinggi:
=QUERY(DataNilai!A1:F200,
  "SELECT B, AVG(F)
   GROUP BY B
   ORDER BY AVG(F) DESC
   LABEL B 'Kelas', AVG(F) 'Rata-rata Nilai Akhir'",
  1)

2. Siswa yang belum lulus (Nilai Akhir < 70) di kelas 10A saja:
=QUERY(DataNilai!A1:F200,
  "SELECT A, C, F
   WHERE B = '10A' AND F < 70
   ORDER BY F ASC
   LABEL A 'Nama', C 'Mata Pelajaran', F 'Nilai Akhir'",
  1)

3. Statistik per Mata Pelajaran (rata-rata, minimum, maksimum):
=QUERY(DataNilai!A1:F200,
  "SELECT C, AVG(F), MIN(F), MAX(F), COUNT(A)
   GROUP BY C
   ORDER BY AVG(F) DESC
   LABEL C 'Mapel', AVG(F) 'Rata-rata',
         MIN(F) 'Terendah', MAX(F) 'Tertinggi', COUNT(A) 'Jumlah Siswa'",
  1)

4. Jumlah siswa per kelas yang Nilai Akhir di atas 80:
=QUERY(DataNilai!A1:F200,
  "SELECT B, COUNT(A)
   WHERE F > 80
   GROUP BY B
   LABEL B 'Kelas', COUNT(A) 'Siswa di atas 80'",
  1)

Struktur setiap file kantin (kolom identik):
A: Tanggal
B: Nama Item
C: Kategori  (Makanan / Minuman / Snack)
D: Jumlah Terjual
E: Harga Satuan
F: Total Penjualan  (=D*E)

Langkah 1: Di file rekap, buat 3 sheet import:

Sheet "Kantin_A":
=IMPORTRANGE("URL_FILE_KANTIN_A", "Penjualan!A2:F200")

Sheet "Kantin_B":
=IMPORTRANGE("URL_FILE_KANTIN_B", "Penjualan!A2:F200")

Sheet "Kantin_C":
=IMPORTRANGE("URL_FILE_KANTIN_C", "Penjualan!A2:F200")

Langkah 2: Gabungkan semua di sheet "Rekap_Semua" dengan array curly braces:
=QUERY(
  {Kantin_A!A2:F200;
   Kantin_B!A2:F200;
   Kantin_C!A2:F200},
  "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col1 ASC",
  0
)

Langkah 3: Buat ringkasan kategori dari rekap gabungan:
=QUERY(Rekap_Semua!A:F,
  "SELECT Col3, SUM(Col6), COUNT(Col1)
   WHERE Col3 IS NOT NULL
   GROUP BY Col3
   ORDER BY SUM(Col6) DESC
   LABEL Col3 'Kategori', SUM(Col6) 'Total Omset', COUNT(Col1) 'Jumlah Transaksi'",
  1)

Catatan: karena QUERY pada array pakai Col1, Col2 (bukan nama kolom asli).

Script Apps Script (buka via Extensions > Apps Script):

function arsipkanMingguIni() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetData = ss.getSheetByName('DataHarian');
  var sheetArsip = ss.getSheetByName('Arsip');

  // Buat sheet Arsip jika belum ada
  if (!sheetArsip) {
    sheetArsip = ss.insertSheet('Arsip');
    sheetArsip.appendRow([
      'Tanggal Arsip', 'Periode', 'Total Baris', 'Total Nilai', 'Rata-rata Nilai'
    ]);
  }

  var data = sheetData.getDataRange().getValues();
  var jumlahBaris = 0;
  var totalNilai = 0;
  var sekarang = new Date();

  // Hitung data 7 hari terakhir
  for (var i = 1; i < data.length; i++) {
    var tanggal = new Date(data[i][0]);
    var selisihHari = (sekarang - tanggal) / (1000 * 60 * 60 * 24);
    if (selisihHari <= 7 && data[i][3] !== '') {
      jumlahBaris++;
      totalNilai += Number(data[i][3]); // kolom D = nilai
    }
  }

  var rataRata = jumlahBaris > 0 ? (totalNilai / jumlahBaris).toFixed(1) : 0;
  var periode = Utilities.formatDate(sekarang, 'Asia/Jakarta', 'dd/MM/yyyy');

  // Simpan ringkasan ke sheet Arsip
  sheetArsip.appendRow([sekarang, periode, jumlahBaris, totalNilai, rataRata]);

  SpreadsheetApp.getUi().alert(
    'Arsip selesai!\n' +
    jumlahBaris + ' baris data minggu ini.\n' +
    'Total nilai: ' + totalNilai + '\n' +
    'Rata-rata: ' + rataRata
  );
}

// Cara jadwalkan otomatis setiap Jumat:
// Apps Script > klik ikon Triggers (jam) > Add Trigger
// Pilih fungsi: arsipkanMingguIni
// Event source: Time-driven
// Type: Week timer > Every Friday > pukul 15.00-16.00

Target output yang diinginkan (desain dulu, baru buat):

+--------+------+------+------+-------+------+---------+
| Kelas  | MTK  | IPA  | IPS  | B.IND | ING  | Rata2   |
+--------+------+------+------+-------+------+---------+
| 10A    | 78.2 | 82.1 | 75.0 | 80.5  | 77.3 | 78.6    |
| 10B    | 74.8 | 79.4 | 80.2 | 76.1  | 72.9 | 76.7    |
| 11A    | 83.0 | 85.2 | 79.8 | 84.1  | 81.6 | 82.7    |
| 11B    | 80.5 | 81.0 | 82.3 | 79.4  | 78.8 | 80.4    |
+--------+------+------+------+-------+------+---------+
| Avg    | 79.1 | 81.9 | 79.3 | 80.0  | 77.7 | 79.6    |
+--------+------+------+------+-------+------+---------+

Ini adalah format "crosstab" (tabulasi silang). Cara terbaik di Sheets:

Pendekatan 1: Pivot Table (paling mudah, otomatis)
- Rows: Kelas
- Columns: Mata Pelajaran
- Values: AVG(Nilai Akhir)
- Show row totals: aktifkan
- Show column totals: aktifkan

Pendekatan 2: Formula manual per sel (lebih kontrol tapi banyak formula)
=AVERAGEIFS(NilaiAkhir, KolomKelas, "10A", KolomMapel, "Matematika")
Perlu 20 formula untuk 4 kelas x 5 mapel.

Pendekatan 3: QUERY mendekati crosstab (tapi terbatas di Sheets)
=QUERY(DataNilai!A:F,
  "SELECT B, AVG(F) WHERE C = 'Matematika' GROUP BY B LABEL B 'Kelas'", 1)
Lalu buat 5 QUERY terpisah, satu per mapel, letakkan berdampingan.

Kesimpulan: untuk tabel silang kelas x mapel, Pivot Table adalah
pilihan terbaik di Google Sheets karena satu konfigurasi menghasilkan
seluruh tabel sekaligus.

Skenario: Sistem pencatatan nilai ulangan harian guru

Langkah 1: Buat Google Form di forms.google.com
- Pertanyaan 1: Nama Siswa (Short answer)
- Pertanyaan 2: Kelas (Multiple choice: 10A, 10B, 11A, 11B)
- Pertanyaan 3: Mata Pelajaran (Dropdown, isi 5 mapel)
- Pertanyaan 4: Nilai (Short answer, validasi angka 0-100)
- Pertanyaan 5: Tanggal Ulangan (Date)
- Klik "Responses" > ikon Sheets hijau > Create spreadsheet

Form otomatis terhubung ke Sheets. Setiap submit = satu baris baru.

Langkah 2: Tambahkan kolom kalkulasi otomatis di Sheets
Di kolom G (Status Kelulusan):
=ARRAYFORMULA(IF(F2:F="","",IF(F2:F >= 70,"Lulus","Remedial")))

Di kolom H (Grade Huruf):
=ARRAYFORMULA(IF(F2:F="","",
  IF(F2:F >= 90,"A",
  IF(F2:F >= 80,"B",
  IF(F2:F >= 70,"C",
  IF(F2:F >= 60,"D","E"))))))

Kolom G dan H mengisi diri otomatis setiap ada submit baru.

Langkah 3: Dashboard sheet terpisah yang update sendiri
=QUERY(A:H,
  "SELECT B, COUNT(A), AVG(F), COUNTIF(G,'Lulus')
   WHERE B IS NOT NULL
   GROUP BY B
   ORDER BY AVG(F) DESC
   LABEL B 'Kelas', COUNT(A) 'Total Siswa', AVG(F) 'Rata-rata',
         COUNTIF(G,'Lulus') 'Jumlah Lulus'",
  1)

KPI summary di atas dashboard:
=COUNTA(A2:A)             total submission hari ini
=COUNTIF(G2:G,"Lulus")   total siswa lulus
=AVERAGE(F2:F)            rata-rata nilai semua kelas