R语言：按组识别异常值并为Excel输出设置单元格条件格式

You can achieve this using R with the tidyverse package for data processing and openxlsx to write formatted Excel files. Here's a step-by-step implementation:

Step 1: Load Required Libraries #

library(tidyverse)
library(openxlsx)

Step 2: Prepare Sample Data #

# Your sample dataset
df <- tibble(
  BIR_ID = c(12001, 12001, 12001, 12001, 12001, 12002, 12002, 12002, 12002),
  label = c("SS", "nhd", "vic", "wab", "Q50", "SS", "nhd", "vic", "Q50"),
  Jan = c(0.44, 0, 6.202288, 1.77, 0.44, 29, 3, 138.290818, 16),
  Feb = c(1.32, 5, 7.86695, 3.37, 4.185, 71.2, 29, 158.67887, 71.2)
)

Step 3: Identify Outliers per Group and Month #

We reshape the data to calculate median/MAD from non-Q50 values, then flag outliers using the condition abs((x - median)/MAD) > 2:

outlier_flags <- df %>%
  pivot_longer(cols = c(Jan, Feb), names_to = "month", values_to = "value") %>%
  group_by(BIR_ID, month) %>%
  mutate(
    # Extract non-Q50 values for the group/month
    non_q50_vals = list(value[label != "Q50"]),
    # Calculate median and MAD from non-Q50 values
    group_median = median(unlist(non_q50_vals)),
    group_mad = mad(unlist(non_q50_vals)),
    # Flag outliers
    is_outlier = abs((value - group_median)/group_mad) > 2
  ) %>%
  pivot_wider(names_from = month, values_from = c(value, is_outlier))

Step 4: Create Formatted Excel File #

Define a highlight style, then apply it to cells marked as outliers:

# Define highlight style (light red fill)
highlight_style <- createStyle(fgFill = "#FFC7CE")

# Create workbook and add worksheet
wb <- createWorkbook()
addWorksheet(wb, "Outlier_Highlighted")
writeData(wb, "Outlier_Highlighted", df)

# Get row indices for outliers (add 1 to account for header row)
jan_outlier_rows <- which(outlier_flags$Jan_is_outlier) + 1
feb_outlier_rows <- which(outlier_flags$Feb_is_outlier) + 1

# Apply highlight to Jan column (column 3)
if (length(jan_outlier_rows) > 0) {
  addStyle(wb, "Outlier_Highlighted", highlight_style, rows = jan_outlier_rows, cols = 3, gridExpand = TRUE)
}

# Apply highlight to Feb column (column 4)
if (length(feb_outlier_rows) > 0) {
  addStyle(wb, "Outlier_Highlighted", highlight_style, rows = feb_outlier_rows, cols = 4, gridExpand = TRUE)
}

# Save the workbook
saveWorkbook(wb, "outlier_highlighted.xlsx", overwrite = TRUE)

Key Notes #

• Outlier detection uses median and MAD calculated only from non-Q50 values in each BIR_ID group and month.
• The resulting Excel file will have outlier cells filled with light red. For your sample data, this includes the Jan values for vic in both BIR_ID groups.

内容的提问来源于stack exchange，提问作者FATPickens