如何在R中实现Excel同款IF函数的发票编号连续性校验逻辑

Got it, let's replicate that Excel formula's logic in R exactly as you need it—checking if each invoice number's last 7 digits are consecutive with the next row, flagging repeats or gaps.

Step-by-Step Implementation #

First, let's start with your sample data. We'll put it into a data frame for easy handling:

# Sample data matching your A列 entries
invoice_data <- data.frame(
  A = c(
    "SERIAL/1000146",
    "SERIAL/1000147",
    "SERIAL/1000147",
    "SERIAL/1000149",
    "SERIAL/1000150",
    "SERIAL/1000151",
    "SERIAL/1000151",
    "SERIAL/1000153",
    "SERIAL/1000154",
    "SERIAL/1000155",
    "SERIAL/1000156",
    "SERIAL/1000157",
    "SERIAL/1000158"
  ),
  stringsAsFactors = FALSE
)

Next, we'll extract the last 7 digits from each entry. We can use the stringr package (super handy for string manipulation) to grab the rightmost 7 characters, then convert them to numeric values:

library(stringr)

# Extract last 7 digits and convert to numeric
invoice_data$last_7_digits <- as.numeric(str_sub(invoice_data$A, start = -7))

If you prefer base R (no extra packages), you can use substr instead:

invoice_data$last_7_digits <- as.numeric(substr(invoice_data$A, nchar(invoice_data$A) - 6, nchar(invoice_data$A)))

Then, we'll replicate the Excel IF logic. We need to compare each row's last_7_digits + 1 to the next row's last_7_digits. For the last row, there's no next row, so we'll set it to NA (similar to how Excel would return a #REF! error if you drag the formula to the last row):

# Apply the continuity check logic with dplyr
library(dplyr)
invoice_data$check_result <- ifelse(
  (invoice_data$last_7_digits + 1) == lead(invoice_data$last_7_digits),
  "Correct Invoice Number",
  "Repeated"
)

Again, if you want to stick to base R, replace lead with a manual shift:

# Base R alternative for shifting values down
next_row_digits <- c(invoice_data$last_7_digits[-1], NA)
invoice_data$check_result <- ifelse(
  (invoice_data$last_7_digits + 1) == next_row_digits,
  "Correct Invoice Number",
  "Repeated"
)

Final Output #

When you run this, your invoice_data data frame will have the check_result column that matches exactly what your Excel formula produces. Key examples from the data:

• Row 1: 1000146 +1 = 1000147 (matches row 2) → Correct Invoice Number
• Row 2: 1000147 +1 = 1000148 (doesn't match row 3's 1000147) → Repeated
• Row 3: 1000147 +1 = 1000148 (doesn't match row4's 1000149) → Repeated

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