Résoudre des Énigmes Excel avec R : Guide Complet

Dans le monde de l’informatique, résoudre des énigmes Excel avec le langage R peut sembler complexe, mais avec les bonnes techniques, cela devient une tâche réalisable et même amusante. Cet article vous guidera à travers plusieurs énigmes intéressantes, en vous montrant comment utiliser R pour les résoudre efficacement.

library(tidyverse)
library(readxl)
path = "Excel/564 Sort Numbers only.xlsx"
input = read_excel(path, range = "A2:D11")
test = read_excel(path, range = "F2:I11")

process_column = function(col) {
  letters = grep("[A-Za-z]", col)
  num_positions = grep("[0-9]", col)
  numbers = as.numeric(col[num_positions])
  numbers = sort(numbers)
  col[num_positions] = numbers
  return(col)
}
input = input %>% map_df(process_column)

all.equal(input, test) #> [1] TRUE

library(tidyverse)
library(readxl)
path = "Excel/565 Even Number and Reversal Perfect Square.xlsx"
test = read_excel(path, range = "A1:A51")

is_even = function(x) { x %% 2 == 0 }
is_perfect_square = function(x) { sqrt_x = sqrt(x); sqrt_x == floor(sqrt_x) }
reverse_number = function(x) { as.numeric(paste(rev(strsplit(as.character(x), NULL)[[1]]), collapse = "")) }
find_even_reverse_perfect_squares = function(n_required) {
  results = vector("list", n_required)
  count = 1
  n = 10
  while (count <= n_required) {
    square = n^2
    reverse_square = reverse_number(square)
    if (is_even(square) && is_even(reverse_square) && is_perfect_square(reverse_square)) {
      results[[count]] = list(original = square, reverse = reverse_square)
      count = count + 1
    }
    n = n + 1
  }
  return(results)
}
result = find_even_reverse_perfect_squares(50) %>% map_df(~ .x)

all.equal(result$original, test$`Expected Answer`) # [1] TRUE

library(tidyverse)
library(readxl)
path = "Excel/566 Count in Columns.xlsx"
input = read_excel(path, range = "A2:C14")
test = read_excel(path, range = "E2:J6")

result = input %>% 
  pivot_longer(everything(), names_to = "basket", values_to = "fruit") %>%
  summarise(Count = n(), .by = c(fruit, basket)) %>%
  na.omit() %>%
  pivot_wider(names_from = Count, values_from = basket, 
              values_fn = list(basket = ~ str_c(sort(.x), collapse = ", "))) %>%
  arrange(fruit) %>%
  select(Count = fruit, `1`, `2`, `3`, `4`, `5`)

all.equal(result, test, check.attributes = FALSE) # [1] TRUE

library(tidyverse)
library(readxl)
path = "Excel/567 ASCII House.xlsx"
test = read_excel(path, range = "C2:Q18", col_names = F) %>% 
  replace(is.na(.), "") %>% as.matrix()

M = matrix("", nrow = 17, ncol = 15)
for (i in 1:7) {
  M[i, (8 - i + 1):(8 + i - 1)] = "#"
}
M[17, ] = "#"
for (i in 8:16) {
  M[i, c(2, 14)] = "#"
}
M[16, -c(1, 15)] = "#"
M[9:11, c(4,6)] = "#"
M[c(9, 11), 5] = "#"
M[9:16, 9] = "#"
M[9, 10:11] = "#"
M[9:16, 12] = "#"
as.data.frame(M)

all.equal(M, test, check.attributes = F) # TRUE

library(tidyverse)
library(readxl)
path = "Excel/568 Fill in the Alphabets.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")

fill_words = function(string) {
  lets = strsplit(string, "")[[1]]
  pairs = map(1:(length(lets) - 1), ~paste(lets[.x:(.x + 1)], collapse = ""))
  df = tibble(
    first = map_chr(pairs, ~str_sub(.x, 1, 1)),
    second = "",
    third = map_chr(pairs, ~str_sub(.x, 2, 2))
  )
  df = df %>% 
    mutate(
      second = map2_chr(first, third, ~{
        first_num = as.numeric(charToRaw(.x))
        third_num = as.numeric(charToRaw(.y))
        letters = map_chr((first_num):(third_num), ~rawToChar(as.raw(.x)))
        paste(letters, collapse = "") %>% str_sub(2, -2)
      }),
      third = if_else(row_number() == n(), "", third)
    ) %>% 
    unite("word", c("first", "second", "third"), sep = "") %>% 
    pull(word) %>% 
    paste(collapse = "")
  return(df)
}
result = input %>% 
  mutate(`Answer Expected` = map_chr(Words, fill_words)) %>% 
  select(-Words)

all.equal(result$`Answer Expected`, test$`Answer Expected`, check.attributes = FALSE) #> [1] TRUE