# ============================================================
# ANALISIS EVALUASI KOMPETENSI & KEBUGARAN REGION VI - 2026
# BPN | BDJ | PNK | PKY
# ============================================================
# ============================================================
# 1. PACKAGE
# ============================================================
library(readxl)
## Warning: package 'readxl' was built under R version 4.3.3
library(dplyr)
## Warning: package 'dplyr' was built under R version 4.3.3
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
library(tidyr)
## Warning: package 'tidyr' was built under R version 4.3.3
library(ggplot2)
## Warning: package 'ggplot2' was built under R version 4.3.3
library(stringr)
library(lubridate)
## Warning: package 'lubridate' was built under R version 4.3.3
##
## Attaching package: 'lubridate'
## The following objects are masked from 'package:base':
##
## date, intersect, setdiff, union
library(writexl)
## Warning: package 'writexl' was built under R version 4.3.3
# ============================================================
# 2. LOKASI FILE
# ============================================================
folder_data <- "C:/Users/ASUS/OneDrive/Documents/ANALISIS EVALUASI KOMPETENSI & KEBUGARAN REGION VI - 2026"
file_BPN <- file.path(
folder_data,
"1. BPN EVALUASI KOMPETENSI & KEBUGARAN 2026.xlsx"
)
file_BDJ <- file.path(
folder_data,
"2. BDJ EVALUASI KOMPETENSI & KEBUGARAN 2026.xlsx"
)
file_PNK <- file.path(
folder_data,
"3. PNK EVALUASI KOMPETENSI & KEBUGARAN 2026.xlsx"
)
file_PKY <- file.path(
folder_data,
"4. PKY EVALUASI KOMPETENSI & KEBUGARAN 2026.xlsx"
)
file_ARFF <- file.path(
folder_data,
"ARFF MONITORING 2026.xlsx"
)
folder_output <- file.path(folder_data, "HASIL ANALISIS 2026")
if (!dir.exists(folder_output)) {
dir.create(folder_output, recursive = TRUE)
}
# ============================================================
# 3. FUNGSI MEMBACA DATA
# ============================================================
baca_data <- function(
file,
sheet,
data_start,
nama_kolom) {
data <- read_excel(
file,
sheet = sheet,
skip = data_start - 1,
col_names = FALSE,
col_types = "text"
)
data <- as.data.frame(data)
# Pastikan jumlah kolom sesuai
data <- data[
,
seq_len(length(nama_kolom)),
drop = FALSE
]
# Tetapkan nama kolom
names(data) <- nama_kolom
# Pastikan seluruh kolom bertipe character
data[] <- lapply(
data,
as.character
)
# Bersihkan spasi
data[] <- lapply(
data,
function(x) {
str_trim(x)
}
)
# Hapus baris yang seluruhnya kosong
data <- data %>%
filter(
if_any(
everything(),
~ !is.na(.x) &
.x != ""
)
)
return(data)
}
# ============================================================
# 4. FUNGSI TANGGAL
# ============================================================
ubah_tanggal <- function(x) {
# Jika sudah Date
if (inherits(x, "Date")) {
return(x)
}
# Jika POSIXct / POSIXlt
if (inherits(x, "POSIXt")) {
return(as.Date(x))
}
# Ubah ke character
x <- str_trim(as.character(x))
# Kosong menjadi NA
x[x == ""] <- NA
hasil <- rep(as.Date(NA), length(x))
# ----------------------------------------------------------
# 1. Coba format tanggal dengan lubridate
hasil <- suppressWarnings(
parse_date_time(
x,
orders = c(
"ymd HMS",
"ymd HM",
"ymd",
"dmy HMS",
"dmy HM",
"dmy",
"mdy HMS",
"mdy HM",
"mdy"
),
quiet = TRUE
)
)
hasil <- as.Date(hasil)
# ----------------------------------------------------------
# 2. Jika masih NA dan berupa angka Excel
masih_na <- is.na(hasil) & !is.na(x)
if (any(masih_na)) {
angka <- suppressWarnings(
as.numeric(x[masih_na])
)
valid_excel <- !is.na(angka) &
angka > 20000 &
angka < 60000
if (any(valid_excel)) {
hasil[which(masih_na)[valid_excel]] <-
as.Date(
angka[valid_excel],
origin = "1899-12-30"
)
}
}
return(hasil)
}
# ============================================================
# 5. FUNGSI ANGKA
# ============================================================
ubah_angka <- function(x) {
if (is.numeric(x)) {
return(as.numeric(x))
}
x <- as.character(x)
x <- str_trim(x)
x <- str_replace_all(
x,
"[^0-9,.-]",
""
)
x <- str_replace_all(
x,
",",
"."
)
suppressWarnings(
as.numeric(x)
)
}
# ============================================================
# 6. NAMA KOLOM SETIAP SHEET
# ============================================================
# ----------------------------
# KEBUGARAN
nama_kolom_kebugaran <- c(
"NO",
"NAMA",
"TANGGAL LAHIR",
"WAKTU PELAKSANAAN",
"Umur",
"Jenis Kelamin",
"Golongan",
"VO2 Level",
"Tingkat",
"Sit up",
"Push up",
"Planking",
"VO2",
"Sit Up score",
"Push Up score",
"Planking score",
"Keterangan VO2",
"Keterangan Sit Up",
"Keterangan Push Up",
"Keterangan Planking",
"Nilai VO2",
"Nilai Otot",
"Nilai Akhir",
"Keterangan Nilai Akhir"
)
# ----------------------------
# BMI
nama_kolom_BMI <- c(
"NO",
"Nama",
"Tanggal Lahir",
"Tanggal Pelaksanaan",
"Umur",
"Jenis Kelamin",
"BB (Kg)",
"TB (Cm)",
"Nilai BMI",
"Keterangan",
"Keterangan Nilai Akhir",
"Nilai Akhir"
)
# ----------------------------
# TEORI
nama_kolom_Teori <- c(
"NO",
"Nama",
"Tanggal Lahir",
"Tanggal Pelaksanaan",
"Umur",
"Jenis Kelamin",
"Jumlah Mengikuti",
"Nilai Tertinggi",
"Nilai Akhir"
)
# ----------------------------
# PRAKTEK
nama_kolom_Praktek <- c(
"NO",
"Nama",
"Tanggal Lahir",
"WAKTU PELAKSANAAN",
"Umur",
"Kategori",
"Keterampilan 1",
"Keterampilan 2",
"Keterampilan 3",
"Keterampilan 4",
"Keterampilan 5",
"Keterampilan 6",
"Keterampilan 7",
"Keterampilan 8",
"Keterampilan 9",
"Keterampilan 10",
"Keterampilan 11",
"Keterampilan 12",
"Keterampilan 13",
"Keterampilan 14",
"Keterampilan 15",
"Nilai Akhir"
)
# ----------------------------
# EVALUASI
nama_kolom_Evaluasi <- c(
"NO",
"Nama",
"BMI",
"Kebugaran",
"Teori",
"Praktek",
"Nilai Evaluasi"
)
# ============================================================
# 7. IMPORT DATA BPN
BPN_Kebugaran <- baca_data(
file_BPN,
"Kebugaran",
data_start = 6,
nama_kolom = nama_kolom_kebugaran
) %>%
mutate(Cabang = "BPN")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
## • `` -> `...14`
## • `` -> `...15`
## • `` -> `...16`
## • `` -> `...17`
## • `` -> `...18`
## • `` -> `...19`
## • `` -> `...20`
## • `` -> `...21`
## • `` -> `...22`
## • `` -> `...23`
## • `` -> `...24`
BPN_BMI <- baca_data(
file_BPN,
"BMI",
data_start = 4,
nama_kolom = nama_kolom_BMI
) %>%
mutate(Cabang = "BPN")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
BPN_Teori <- baca_data(
file_BPN,
"Teori",
data_start = 4,
nama_kolom = nama_kolom_Teori
) %>%
mutate(Cabang = "BPN")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
BPN_Praktek <- baca_data(
file_BPN,
"Praktek",
data_start = 4,
nama_kolom = nama_kolom_Praktek
) %>%
mutate(Cabang = "BPN")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
## • `` -> `...14`
## • `` -> `...15`
## • `` -> `...16`
## • `` -> `...17`
## • `` -> `...18`
## • `` -> `...19`
## • `` -> `...20`
## • `` -> `...21`
## • `` -> `...22`
BPN_Evaluasi <- baca_data(
file_BPN,
"Evaluasi",
data_start = 6,
nama_kolom = nama_kolom_Evaluasi
) %>%
mutate(Cabang = "BPN")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
# ============================================================
# 8. IMPORT DATA BDJ
# ============================================================
BDJ_Kebugaran <- baca_data(
file_BDJ,
"Kebugaran",
data_start = 6,
nama_kolom = nama_kolom_kebugaran
) %>%
mutate(Cabang = "BDJ")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
## • `` -> `...14`
## • `` -> `...15`
## • `` -> `...16`
## • `` -> `...17`
## • `` -> `...18`
## • `` -> `...19`
## • `` -> `...20`
## • `` -> `...21`
## • `` -> `...22`
## • `` -> `...23`
## • `` -> `...24`
BDJ_BMI <- baca_data(
file_BDJ,
"BMI",
data_start = 4,
nama_kolom = nama_kolom_BMI
) %>%
mutate(Cabang = "BDJ")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
BDJ_Teori <- baca_data(
file_BDJ,
"Teori",
data_start = 4,
nama_kolom = nama_kolom_Teori
) %>%
mutate(Cabang = "BDJ")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
BDJ_Praktek <- baca_data(
file_BDJ,
"Praktek",
data_start = 4,
nama_kolom = nama_kolom_Praktek
) %>%
mutate(Cabang = "BDJ")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
## • `` -> `...14`
## • `` -> `...15`
## • `` -> `...16`
## • `` -> `...17`
## • `` -> `...18`
## • `` -> `...19`
## • `` -> `...20`
## • `` -> `...21`
## • `` -> `...22`
BDJ_Evaluasi <- baca_data(
file_BDJ,
"Evaluasi",
data_start = 6,
nama_kolom = nama_kolom_Evaluasi
) %>%
mutate(Cabang = "BDJ")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
# ============================================================
# 9. IMPORT DATA PNK
# ============================================================
PNK_Kebugaran <- baca_data(
file_PNK,
"Kebugaran",
data_start = 6,
nama_kolom = nama_kolom_kebugaran
) %>%
mutate(Cabang = "PNK")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
## • `` -> `...14`
## • `` -> `...15`
## • `` -> `...16`
## • `` -> `...17`
## • `` -> `...18`
## • `` -> `...19`
## • `` -> `...20`
## • `` -> `...21`
## • `` -> `...22`
## • `` -> `...23`
## • `` -> `...24`
PNK_BMI <- baca_data(
file_PNK,
"BMI",
data_start = 4,
nama_kolom = nama_kolom_BMI
) %>%
mutate(Cabang = "PNK")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
PNK_Teori <- baca_data(
file_PNK,
"Teori",
data_start = 4,
nama_kolom = nama_kolom_Teori
) %>%
mutate(Cabang = "PNK")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
PNK_Praktek <- baca_data(
file_PNK,
"Praktek",
data_start = 4,
nama_kolom = nama_kolom_Praktek
) %>%
mutate(Cabang = "PNK")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
## • `` -> `...14`
## • `` -> `...15`
## • `` -> `...16`
## • `` -> `...17`
## • `` -> `...18`
## • `` -> `...19`
## • `` -> `...20`
## • `` -> `...21`
## • `` -> `...22`
PNK_Evaluasi <- baca_data(
file_PNK,
"Evaluasi",
data_start = 6,
nama_kolom = nama_kolom_Evaluasi
) %>%
mutate(Cabang = "PNK")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
# ============================================================
# 10. IMPORT DATA PKY
# ============================================================
PKY_Kebugaran <- baca_data(
file_PKY,
"Kebugaran",
data_start = 6,
nama_kolom = nama_kolom_kebugaran
) %>%
mutate(Cabang = "PKY")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
## • `` -> `...14`
## • `` -> `...15`
## • `` -> `...16`
## • `` -> `...17`
## • `` -> `...18`
## • `` -> `...19`
## • `` -> `...20`
## • `` -> `...21`
## • `` -> `...22`
## • `` -> `...23`
## • `` -> `...24`
PKY_BMI <- baca_data(
file_PKY,
"BMI",
data_start = 4,
nama_kolom = nama_kolom_BMI
) %>%
mutate(Cabang = "PKY")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
PKY_Teori <- baca_data(
file_PKY,
"Teori",
data_start = 4,
nama_kolom = nama_kolom_Teori
) %>%
mutate(Cabang = "PKY")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
PKY_Praktek <- baca_data(
file_PKY,
"Praktek",
data_start = 4,
nama_kolom = nama_kolom_Praktek
) %>%
mutate(Cabang = "PKY")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
## • `` -> `...14`
## • `` -> `...15`
## • `` -> `...16`
## • `` -> `...17`
## • `` -> `...18`
## • `` -> `...19`
## • `` -> `...20`
## • `` -> `...21`
## • `` -> `...22`
PKY_Evaluasi <- baca_data(
file_PKY,
"Evaluasi",
data_start = 6,
nama_kolom = nama_kolom_Evaluasi
) %>%
mutate(Cabang = "PKY")
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
# ============================================================
# 11. GABUNG DATA KEBUGARAN
# ============================================================
Kebugaran <- bind_rows(
BPN_Kebugaran,
BDJ_Kebugaran,
PNK_Kebugaran,
PKY_Kebugaran
)
# ============================================================
# 12. MEMBERSIHKAN DATA KEBUGARAN
# ============================================================
Kebugaran <- Kebugaran %>%
mutate(
`TANGGAL LAHIR` =
ubah_tanggal(`TANGGAL LAHIR`),
`WAKTU PELAKSANAAN` =
ubah_tanggal(`WAKTU PELAKSANAAN`),
Umur =
ubah_angka(Umur),
`VO2 Level` =
ubah_angka(`VO2 Level`),
`Sit up` =
ubah_angka(`Sit up`),
`Push up` =
ubah_angka(`Push up`),
Planking =
ubah_angka(Planking),
VO2 =
ubah_angka(VO2),
`Nilai VO2` =
ubah_angka(`Nilai VO2`),
`Nilai Otot` =
ubah_angka(`Nilai Otot`),
`Nilai Akhir` =
ubah_angka(`Nilai Akhir`),
Status_Physical_Fitness =
str_trim(
as.character(
`Keterangan Nilai Akhir`
)
)
)
# ============================================================
# 13. REKAP PHYSICAL FITNESS PER CABANG
# ============================================================
Rekap_Physical_Fitness <- Kebugaran %>%
group_by(Cabang) %>%
summarise(
Jumlah_Peserta = n(),
Lulus = sum(
Status_Physical_Fitness == "Lulus",
na.rm = TRUE
),
Tidak_Lulus = sum(
Status_Physical_Fitness == "Tidak Lulus",
na.rm = TRUE
),
Pemantauan = sum(
str_detect(
str_to_lower(
Status_Physical_Fitness
),
"pemantauan"
),
na.rm = TRUE
),
Rata_Rata_Nilai =
mean(
`Nilai Akhir`,
na.rm = TRUE
),
.groups = "drop"
)
# ============================================================
# 14. REKAP STATUS PHYSICAL FITNESS
# ============================================================
Rekap_Status <- Kebugaran %>%
count(
Cabang,
Status_Physical_Fitness,
name = "Jumlah"
)
# ============================================================
# 15. DATA MONITORING INDIVIDU 2026
# ============================================================
Monitoring_2026 <- Kebugaran %>%
mutate(
Nama_Normalisasi =
str_squish(
str_to_upper(
as.character(NAMA)
)
),
ID_Monitoring = paste(
Nama_Normalisasi,
format(
`TANGGAL LAHIR`,
"%Y%m%d"
),
sep = "_"
)
) %>%
transmute(
ID_Monitoring,
Nama = NAMA,
Tanggal_Lahir =
`TANGGAL LAHIR`,
Cabang,
Tanggal_Tes =
`WAKTU PELAKSANAAN`,
VO2_Level =
`VO2 Level`,
Sit_Up =
`Sit up`,
Push_Up =
`Push up`,
Planking,
Nilai_VO2 =
`Nilai VO2`,
Nilai_Otot =
`Nilai Otot`,
Nilai_Akhir =
`Nilai Akhir`,
Status =
Status_Physical_Fitness,
Tahun = 2026
)
# ============================================================
# 16. TEMPLATE MONITORING TAHUN BERIKUTNYA
# ============================================================
Monitoring_Template <- Monitoring_2026 %>%
select(
ID_Monitoring,
Nama,
Tanggal_Lahir,
Cabang,
Nilai_Akhir,
Status,
Tahun
)
# ============================================================
# 17. FUNGSI PERBANDINGAN TAHUN
# ============================================================
# Fungsi ini digunakan ketika data tahun berikutnya sudah ada.
# Contoh: hasil_2027 <- bandingkan_tahun(Monitoring_2026, Monitoring_2027)
bandingkan_tahun <- function(
data_lama,
data_baru) {
hasil <- data_lama %>%
select(
ID_Monitoring,
Nama_2026 = Nama,
Cabang_2026 = Cabang,
Nilai_2026 = Nilai_Akhir
) %>%
inner_join(
data_baru %>%
select(
ID_Monitoring,
Nama_2027 = Nama,
Cabang_2027 = Cabang,
Nilai_2027 = Nilai_Akhir
),
by = "ID_Monitoring"
) %>%
mutate(
Perubahan_Nilai =
Nilai_2027 - Nilai_2026,
Persentase_Perubahan =
ifelse(
Nilai_2026 == 0,
NA_real_,
(
(Nilai_2027 - Nilai_2026) /
Nilai_2026
) * 100
),
Status_Perubahan =
case_when(
Perubahan_Nilai > 0 ~ "Naik",
Perubahan_Nilai < 0 ~ "Turun",
Perubahan_Nilai == 0 ~ "Stagnan",
TRUE ~ NA_character_
)
)
return(hasil)
}
# ============================================================
# 18. DATA BMI
# ============================================================
BMI <- bind_rows(
BPN_BMI,
BDJ_BMI,
PNK_BMI,
PKY_BMI
)
BMI <- BMI %>%
mutate(
`Tanggal Lahir` =
ubah_tanggal(
`Tanggal Lahir`
),
`Tanggal Pelaksanaan` =
ubah_tanggal(
`Tanggal Pelaksanaan`
),
Umur =
ubah_angka(Umur),
`BB (Kg)` =
ubah_angka(`BB (Kg)`),
`TB (Cm)` =
ubah_angka(`TB (Cm)`),
`Nilai BMI` =
ubah_angka(`Nilai BMI`),
`Nilai Akhir` =
ubah_angka(`Nilai Akhir`)
)
# ============================================================
# 19. REKAP BMI
# ============================================================
Rekap_BMI <- BMI %>%
count(
Cabang,
Keterangan,
name = "Jumlah"
)
# ============================================================
# 20. DATA TEORI
# ============================================================
Teori <- bind_rows(
BPN_Teori,
BDJ_Teori,
PNK_Teori,
PKY_Teori
)
Teori <- Teori %>%
mutate(
`Tanggal Lahir` =
ubah_tanggal(
`Tanggal Lahir`
),
`Tanggal Pelaksanaan` =
ubah_tanggal(
`Tanggal Pelaksanaan`
),
Umur =
ubah_angka(Umur),
`Jumlah Mengikuti` =
ubah_angka(
`Jumlah Mengikuti`
),
`Nilai Tertinggi` =
ubah_angka(
`Nilai Tertinggi`
),
`Nilai Akhir` =
ubah_angka(
`Nilai Akhir`
)
)
# ============================================================
# 21. REKAP TEORI
# ============================================================
Rekap_Teori <- Teori %>%
group_by(Cabang) %>%
summarise(
Jumlah_Peserta = n(),
Rata_Rata_Nilai =
mean(
`Nilai Akhir`,
na.rm = TRUE
),
.groups = "drop"
)
# ============================================================
# 22. DATA PRAKTEK
# ============================================================
Praktek <- bind_rows(
BPN_Praktek,
BDJ_Praktek,
PNK_Praktek,
PKY_Praktek
)
Praktek <- Praktek %>%
mutate(
`Tanggal Lahir` =
ubah_tanggal(
`Tanggal Lahir`
),
`WAKTU PELAKSANAAN` =
ubah_tanggal(
`WAKTU PELAKSANAAN`
),
Umur =
ubah_angka(Umur),
`Nilai Akhir` =
ubah_angka(
`Nilai Akhir`
)
)
# ============================================================
# 23. REKAP PRAKTEK
# ============================================================
Rekap_Praktek <- Praktek %>%
group_by(Cabang) %>%
summarise(
Jumlah_Peserta = n(),
Rata_Rata_Nilai =
mean(
`Nilai Akhir`,
na.rm = TRUE
),
.groups = "drop"
)
# ============================================================
# 24. DATA EVALUASI
# ============================================================
Evaluasi <- bind_rows(
BPN_Evaluasi,
BDJ_Evaluasi,
PNK_Evaluasi,
PKY_Evaluasi
)
Evaluasi <- Evaluasi %>%
mutate(
BMI =
ubah_angka(BMI),
Kebugaran =
ubah_angka(Kebugaran),
Teori =
ubah_angka(Teori),
Praktek =
ubah_angka(Praktek),
`Nilai Evaluasi` =
ubah_angka(
`Nilai Evaluasi`
)
)
# ============================================================
# 25. REKAP EVALUASI
# ============================================================
Rekap_Evaluasi <- Evaluasi %>%
group_by(Cabang) %>%
summarise(
Jumlah_Peserta = n(),
Rata_Rata_BMI =
mean(
BMI,
na.rm = TRUE
),
Rata_Rata_Kebugaran =
mean(
Kebugaran,
na.rm = TRUE
),
Rata_Rata_Teori =
mean(
Teori,
na.rm = TRUE
),
Rata_Rata_Praktek =
mean(
Praktek,
na.rm = TRUE
),
Rata_Rata_Evaluasi =
mean(
`Nilai Evaluasi`,
na.rm = TRUE
),
.groups = "drop"
)
# ============================================================
# 26. ARFF MONITORING 2026
# ============================================================
# Struktur file:
# NO | PROGRAM | PERIODE | BPN | BDJ | PNK | PKY
ARFF_Raw <- read_excel(
file_ARFF,
sheet = "R6",
skip = 5,
col_names = FALSE
)
## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
ARFF_Raw <- as.data.frame(
ARFF_Raw
)
# Ambil 7 kolom utama
ARFF_Raw <- ARFF_Raw[
,
1:7,
drop = FALSE
]
names(ARFF_Raw) <- c(
"NO",
"PROGRAM",
"PERIODE",
"BPN",
"BDJ",
"PNK",
"PKY"
)
# Hapus baris kosong
ARFF_Raw <- ARFF_Raw %>%
filter(
!is.na(PROGRAM) &
str_trim(
as.character(PROGRAM)
) != ""
)
# Ubah menjadi format long
ARFF_Monitoring <- ARFF_Raw %>%
pivot_longer(
cols = c(
BPN,
BDJ,
PNK,
PKY
),
names_to = "Cabang",
values_to = "Timeline"
) %>%
mutate(
Timeline =
as.character(Timeline)
) %>%
filter(
!is.na(Timeline) &
str_trim(Timeline) != ""
) %>%
select(
NO,
PROGRAM,
PERIODE,
Cabang,
Timeline
)
# ============================================================
# 27. REKAP ARFF PER CABANG
# ============================================================
Rekap_ARFF <- ARFF_Monitoring %>%
group_by(Cabang) %>%
summarise(
Jumlah_Program =
n_distinct(PROGRAM),
.groups = "drop"
)
# ============================================================
# 28. GAP
# ============================================================
# threshold GAP diubah dari 8 menjadi 7 untuk SDM, FSN, dan Fasilitas.
# Dari 4 workbook evaluasi yang tersedia, tidak ditemukan kolom yang secara eksplisit bernama GAP, SDM, FSN, atau Fasilitas.
# Karena itu tidak dibuat rumus GAP baru.
GAP_THRESHOLD <- 7
# Cek seluruh nama kolom dari data sumber
semua_kolom <- unique(
c(
names(BPN_Kebugaran),
names(BPN_BMI),
names(BPN_Teori),
names(BPN_Praktek),
names(BPN_Evaluasi)
)
)
kolom_GAP_terdeteksi <- semua_kolom[
str_detect(
str_to_lower(
semua_kolom
),
"gap|sdm|fsn|fasilitas"
)
]
if (
length(kolom_GAP_terdeteksi) == 0
) {
GAP_Catatan <- data.frame(
Keterangan = c(
"Arahan GAP",
"Threshold GAP baru",
"Data GAP terdeteksi",
"Kolom terkait yang terdeteksi",
"Status"
),
Nilai = c(
"Perubahan threshold dari 8 menjadi 7",
GAP_THRESHOLD,
"Belum ditemukan",
"Tidak ada",
"Perhitungan GAP belum dilakukan karena sumber/format GAP belum tersedia dalam workbook evaluasi"
)
)
} else {
GAP_Catatan <- data.frame(
Keterangan = c(
"Arahan GAP",
"Threshold GAP baru",
"Kolom terkait yang terdeteksi"
),
Nilai = c(
"Perubahan threshold dari 8 menjadi 7",
GAP_THRESHOLD,
paste(
kolom_GAP_terdeteksi,
collapse = ", "
)
)
)
}
# ============================================================
# 29. GRAFIK STATUS PHYSICAL FITNESS
# ============================================================
grafik_status <- ggplot(
Rekap_Status,
aes(
x = Cabang,
y = Jumlah,
fill = Status_Physical_Fitness
)
) +
geom_col(
position = "dodge"
) +
labs(
title =
"Status Physical Fitness Tahun 2026",
x = "Cabang",
y = "Jumlah Peserta",
fill = "Status"
) +
theme_minimal()
ggsave(
filename = file.path(
folder_output,
"01_Status_Physical_Fitness_2026.png"
),
plot = grafik_status,
width = 10,
height = 6,
dpi = 300
)
grafik_status
# ============================================================
# 30. GRAFIK RATA-RATA PHYSICAL FITNESS
# ============================================================
grafik_rata2 <- ggplot(
Rekap_Physical_Fitness,
aes(
x = Cabang,
y = Rata_Rata_Nilai
)
) +
geom_col() +
labs(
title =
"Rata-rata Nilai Physical Fitness per Cabang",
x = "Cabang",
y = "Rata-rata Nilai"
) +
theme_minimal()
ggsave(
filename = file.path(
folder_output,
"02_Rata_Rata_Physical_Fitness_2026.png"
),
plot = grafik_rata2,
width = 10,
height = 6,
dpi = 300
)
grafik_rata2
# ============================================================
# 31. GRAFIK BMI
# ============================================================
grafik_BMI <- ggplot(
Rekap_BMI,
aes(
x = Cabang,
y = Jumlah,
fill = Keterangan
)
) +
geom_col(
position = "dodge"
) +
labs(
title =
"Distribusi Kategori BMI Tahun 2026",
x = "Cabang",
y = "Jumlah Peserta",
fill = "Kategori BMI"
) +
theme_minimal()
ggsave(
filename = file.path(
folder_output,
"03_Distribusi_BMI_2026.png"
),
plot = grafik_BMI,
width = 10,
height = 6,
dpi = 300
)
grafik_BMI
# ============================================================
# 32. EXPORT HASIL KE EXCEL
# ============================================================
hasil_excel <- list(
# Physical Fitness
Rekap_Physical_Fitness =
Rekap_Physical_Fitness,
Rekap_Status =
Rekap_Status,
Monitoring_2026 =
Monitoring_2026,
Monitoring_Template =
Monitoring_Template,
# BMI
Rekap_BMI =
Rekap_BMI,
# Teori
Rekap_Teori =
Rekap_Teori,
# Praktek
Rekap_Praktek =
Rekap_Praktek,
# Evaluasi
Rekap_Evaluasi =
Rekap_Evaluasi,
# ARFF
Rekap_ARFF =
Rekap_ARFF,
ARFF_Monitoring =
ARFF_Monitoring,
# GAP
GAP_Catatan =
GAP_Catatan
)
write_xlsx(
hasil_excel,
path = file.path(
folder_output,
"HASIL_ANALISIS_EVALUASI_REGION_VI_2026.xlsx"
)
)
# ============================================================
# 33. SELESAI
# ============================================================
cat(
"\n========================================\n"
)
##
## ========================================
cat(
"ANALISIS SELESAI\n"
)
## ANALISIS SELESAI
cat(
"========================================\n"
)
## ========================================
cat(
"\nFile hasil tersimpan di:\n"
)
##
## File hasil tersimpan di:
cat(
folder_output
)
## C:/Users/ASUS/OneDrive/Documents/ANALISIS EVALUASI KOMPETENSI & KEBUGARAN REGION VI - 2026/HASIL ANALISIS 2026
cat("\n\n")