対象

エクセルが(宗教上)嫌いな人
なんでもRで片付けたい人

はじめに

まず前提として、以下のようなエクセルファイルがあったとします。

ちなみにこのデータは疑似個人情報データ生成サービスというWebサイトで作成しました。
この中で、色で塗りつぶされているセルだけを抽出して、データの解析を行いたいとします。Excel職人の方は「んなもんフィルタ掛けて「色で並べ替え」をクリックすればできるやん」と言うかもしれません。確かに言った通りのことをすればできるのですが、あのリボンの中からフィルタの項目探すの結構大変です（私だけ？）。さらに言えばシートの数が多かったり複数のエクセルでこれをいちいち手でやるのはメンドクサイ。マクロ組むなんてもっとメンドクサイ。
そんな方に朗報。この操作、Rでもできます。

tidyxl

エクセルシートを読み込むためのパッケージとして、{tidyxl}というものがあります。「{tidyxl}？ {readxl}ちゃうんか？」と思うかもしれませんが、別物です。
インストールは例のごとくinstall.packages("tidyxl")。

{tidyxl}でエクセルファイルを読み込む関数に**xlsx_cells()**があります。{readxl}におけるread_excel()ですね。
この挙動を見てみましょう。

{xlsx_cells.R}

library(tidyxl)
tidyxl::xlsx_cells("Sample_data.xlsx")

# A tibble: 1,206 x 21
   sheet  address   row   col is_blank data_type error logical numeric date   character       
   <chr>  <chr>   <int> <int> <lgl>    <chr>     <chr> <lgl>     <dbl> <dttm> <chr>           
 1 Sheet1 A1          1     1 FALSE    character NA    NA           NA NA     連番            
 2 Sheet1 B1          1     2 FALSE    character NA    NA           NA NA     氏名            
 3 Sheet1 C1          1     3 FALSE    character NA    NA           NA NA     氏名（カタカナ）
 4 Sheet1 D1          1     4 FALSE    character NA    NA           NA NA     性別            
 5 Sheet1 E1          1     5 FALSE    character NA    NA           NA NA     電話番号        
 6 Sheet1 F1          1     6 FALSE    character NA    NA           NA NA     生年月日        
 7 Sheet1 A2          2     1 FALSE    character NA    NA           NA NA     008             
 8 Sheet1 B2          2     2 FALSE    character NA    NA           NA NA     野原鉄太郎      
 9 Sheet1 C2          2     3 FALSE    character NA    NA           NA NA     ノハラテツタロウ
10 Sheet1 D2          2     4 FALSE    character NA    NA           NA NA     男              
# ... with 1,196 more rows, and 10 more variables: character_formatted <list>, formula <chr>, is_array <lgl>,
#   formula_ref <chr>, formula_group <int>, comment <chr>, height <dbl>, width <dbl>, style_format <chr>,
#   local_format_id <int>

少し様子が違うことに気づいたでしょうか？そうです、この関数は各セルの情報(シート番号、アドレス、書いてある内容 etc...)を一つの行で格納しているのです。
各セルのフォーマットについてはidが振られており、変数$local_format_idでアクセスすることができます。

{local_format_id.R}

cells <- tidyxl::xlsx_cells("Sample_data.xlsx")
cells$local_format_id[1:10] #長いので一部を抜粋
> cells$local_format_id[1:10]
 [1] 4 4 5 4 4 4 9 9 9 9

１番目からA1, B1, C1,...のセルの書式について数字が振られています。A1 ～ F1までの書式がボールド体である一方、A2から通常の書式になっているので番号が大きく変わっています。しかしこれだけでは、「どのセルが塗りつぶされたセルか」がわかりません。そこで、同じパッケージ内にある関数**xlsx_formats()**を使います。

公式曰く、

Cell formatting is returned by xlsx_formats(). ~(中略)~
To look up the local formatting of a given cell, take the cell's local_format_id value (my_cells$Sheet1[1, "local_format_id"]), and use it as an index into the format structure. E.g. to look up the font size, my_formats$local$font$size[local_format_id]. To see all available formats, type str(my_formats$local).

(DeepL訳：セルの書式設定はxlsx_formats()で返されます。これらは、xlsx_formats()の$styleサブリストと$localサブリストで返され、構造は同じです。あるセルのローカルな書式を調べるには、そのセルのlocal_format_id 値 (my_cells$Sheet1[1, "local_format_id"]) を取り、それを書式構造のインデックスとして使用します。例えば、フォントサイズを調べるにはmy_formats$local$font$size[local_format_id]と入力します。利用可能なすべてのフォーマットを見るには、str(my_formats$local)と入力します。)

とあります。ちょっと何言ってるかわからないと思うので噛み砕いて説明します。

まずxlsx_formats()を使うと、各セルの全ての書式情報(フォントのサイズや色、罫線の有無、背景色 etc...)。が格納されたオブジェクト(リスト)が生成されます。先程、cells$local_format_idで得られた番号が割り当てられるということを話しましたが、実際にはxlsx_formats()によって割り当てる番号を決定していることになります。
セルの背景色については変数$local$fill$patternFill$fgColor$rgb[変数$local_format_id]でアクセスすることができます。
つまり、

{result.R}

cells <- tidyxl::xlsx_cells("Sample_data.xlsx")
formats <- xlsx_formats("Sample_data.xlsx")

formats$local$fill$patternFill$fgColor$rgb[cells$local_format_id][1:50]

> formats$local$fill$patternFill$fgColor$rgb[cells$local_format_id][1:50]
 [1] NA         NA         NA         NA         NA         NA        
 [7] NA         NA         NA         NA         NA         NA        
[13] NA         NA         NA         NA         NA         NA        
[19] NA         NA         NA         NA         NA         NA        
[25] NA         NA         NA         NA         NA         NA        
[31] NA         NA         NA         NA         NA         NA        
[37] NA         NA         NA         NA         NA         NA        
[43] NA         NA         NA         NA         NA         NA        
[49] "FF70AD47" "FF70AD47"

これで背景色の情報を得ることができました！見てみると多くはNA(背景色なし)ですが、
FF70AD47というデータがあります。これが今回探している背景色のカラーコードです。

ここまで来れば後は簡単！
local_format_idの中で"FF70AD47"というカラーコードになっている行をフィルターすれば背景色のあるセルのみを抽出できます。

{filter.R}

  cells %>% 
  filter(
    local_format_id %in% which(formats$local$fill$patternFill$fgColor$rgb == "FF70AD47")
    ) 

# A tibble: 210 x 21
   sheet  address   row   col is_blank data_type error logical numeric
   <chr>  <chr>   <int> <int> <lgl>    <chr>     <chr> <lgl>     <dbl>
 1 Sheet1 A9          9     1 FALSE    character NA    NA           NA
 2 Sheet1 B9          9     2 FALSE    character NA    NA           NA
 3 Sheet1 C9          9     3 FALSE    character NA    NA           NA
 4 Sheet1 D9          9     4 FALSE    character NA    NA           NA
 5 Sheet1 E9          9     5 FALSE    character NA    NA           NA
 6 Sheet1 F9          9     6 FALSE    character NA    NA           NA
 7 Sheet1 A14        14     1 FALSE    character NA    NA           NA
 8 Sheet1 B14        14     2 FALSE    character NA    NA           NA
 9 Sheet1 C14        14     3 FALSE    character NA    NA           NA
10 Sheet1 D14        14     4 FALSE    character NA    NA           NA
# ... with 200 more rows, and 12 more variables: date <dttm>,
#   character <chr>, character_formatted <list>, formula <chr>,
#   is_array <lgl>, formula_ref <chr>, formula_group <int>,
#   comment <chr>, height <dbl>, width <dbl>, style_format <chr>,
#   local_format_id <int>

A9からのtibbleになっていることから、正しく背景色のあるセルだけを選択できています。解析の際には、これをtidy dataに直せば良いのでRのみでセル抽出→データ解析ができるようになります。また、「背景色がある」という条件だけでいいのであれば、filter()の条件式を!= "NA"と記述することでも同様の結果が得られます。
（pivot_wider()やselect()については多くの記事があるので割愛）

{result.R}

pacman::p_load(
  tidyverse,
  readxl,
  here,
  tidyxl 
)

col_names_excel <- read_excel("Sample_data.xlsx") %>% colnames() # もともとの列名を保存

cells <- tidyxl::xlsx_cells("Sample_data.xlsx") # セルの情報を取得
formats <- xlsx_formats("Sample_data.xlsx") # セルの書式情報を取得

df <- 
  cells %>% 
  filter(
    local_format_id %in% which(formats$local$fill$patternFill$fgColor$rgb != "NA")
    ) %>%
  select(row, col, character) %>% 
  pivot_wider(
    names_from = col,
    values_from = character
  ) %>% select(!row) 

colnames(df) <- col_names_excel

> df
# A tibble: 35 x 6
   連番  氏名       `氏名（カタカナ）` 性別  電話番号   生年月日  
   <chr> <chr>      <chr>              <chr> <chr>      <chr>     
 1 008   野原鉄太郎 ノハラテツタロウ   男    0877500712 1973/10/10
 2 013   吉岡春奈   ヨシオカハルナ     女    0155191924 1994/09/27
 3 014   荻野飛鳥   オギノアスカ       女    0873632701 1989/03/05
 4 016   川島知治   カワシマトモハル   男    0441682525 1992/03/30
 5 018   関口希実   セキグチノゾミ     女    0234256551 1979/07/22
 6 022   猪俣遥華   イノマタハルカ     女    0192956110 1971/03/30
 7 024   三宅穂乃佳 ミヤケホノカ       女    0189808393 1971/12/07
 8 025   野呂千春   ノロチハル         女    0883007604 1969/01/31
 9 031   小森実     コモリミノル       男    0990693955 1970/09/21
10 037   持田麗奈   モチダレナ         女    0451228474 1996/06/15
# ... with 25 more rows

おわりに

今回の記事では「色で塗りつぶされたセルを抽出する」ということにフォーカスをしました。しかし、この方法を応用することで「フォントがbold体になっているセル」や「コメントの入っているセル」の抽出も可能になります。
割とクセがありますが一度関数化してしまえば反復処理もできるので非常に良いパッケージと言えるのではないでしょうか。