Excelファイル200個に含まれているセルを確認する
社でもう10年以上続いている統計セミナーに途中から講師として参加を強要された。これまで数名の社員が担当してきており、資料も課題もすでに用意されているので、それを、そのままやれということだ。内容の良し悪しはともかく、というか、とんでもなく昭和な資料なのだが、そこは我慢する。
が、問題は課題の確認で、200名近くの受講生に毎回課題を与えてExcelファイルを提出させている。これを同僚らは1つ1つダブルクリックして開いているという。具体的にはワークシートの適当なセルに正しい「式」が入力されているかなどを確認しているそうな。
チェックポイントは一枚のワークシートに10箇所近くあり、チェック対象となるワークシートが2,3枚、そしてこれが約200人分。大量というほどではないけど、手作業でやるには多すぎる。同僚らは丸一日かけているそうな。
こんな課題の是非はとりあえず問わないが、特定のセルに入力された式なり値が正しいかどうかであれば、なにもファイルをいちいち開く必要はない。なので、自分だけは以下に説明する手順を応用して自動的にチェックしている。すべてのファイルを処理するのに1分もかからない。
XLConnetパッケージを利用する
Excelファイルを読むパッケージは他にも readxl などがあるが、セルに入力された式を(多分)そのまま取り出せないようなのでXLConnet を使う。ただしXLConnetを利用するにはJavaとrJavaが必要。
余談だが、Rのインストール後に Java をインストールしたり、アップデートした場合は、コンソールで以下を実行しておく。
R CMD javareconf
ファイルの読み込み
まずファイルの読み込み。ファイルが破損している場合を考慮して try()
にラップする。
install.packages("rJava")
install.packages("XLConnect")
library(XLConnect)
wb <- try (loadWorkbook("test.xlsx") )
入力された「式」のチェック
読み込んだファイルのシートと番地を指定してセルに入力された「式」を抽出する。getCellFormula()
を使う。返り値は"AVERAGE(G2:G20)"
のような文字列となる。
セルの番地に指定するのは行番号と列番号である。アルファベットを列番号にかえるには [LETTERS]
から換算する。
ただし、セルが空白だったり、数値などが入力されていて、式ではないと判断された場合、エラーになってしまう。なので、これも try()
にラップする。
## G2セルであれば
G <- which(LETTERS=="G")
G2 <- try(getCellFormula(wb, "Sheet1", 2, G), silent = TRUE)
if(class(G2) == "try-error") G2 <- NA
G2
[1] "AVERAGE(G2:G22)"
セル範囲の値を抽出
あるセル範囲に入力されている数値などを取りたい場合もある。以下ではG6からG25に入力されている数値を取り出し、その合計を求めている。
G <- which(LETTERS== "G")
cells <- try (readWorksheet(wb, sheet = "Sheet1", startRow = 6, endRow = 25,
startCol = G, endCol = G, header = FALSE), silent = TRUE)
G_SUMS <- ifelse(class(cells) == "try-error", NA, sum(cells ))
ちなみにExcel式が必要ではなく、値そのもの、あるいは列名など取り出す場合は readWorksheet()
でデータフレームとして取り出すのが吉。
df <- try (readWorksheet(wb, sheet = "Sheet1"))
以上では同じエクセルファイルから、シートを指定した読み込みを何度も繰り返しているのだが、あるいはもっと効率的なやり方があるかもしれない。
「式」を取り出す必要がなく、単に数値ないし文字列として取り出すだけであれば、readxl パッケージを使えば、単純にデータフレームとして読み込まれるので、添え字などを使って取得すれば良いだろう。
ループで200人分のファイルをチェック
こうした処理を対象となる200人分のファイルすべてかまして、各ファイルについてチェック対象となるセルに記録された式なりを取り出す。例えば以下のように(以下の処理はOSを問わず実行可能)。
# ファイルリストを取得
files <- list.files("/path/to/files/folder", pattern = "xlsx",full.names = TRUE)
# 予めファイル数と同じ数のベクトルを用意しておく
N = length(files)
IDS <- character(N) # 社員番号
NAMES <- character(N) # 氏名
A6 <- character(N) # A6セルの式を文字列として取る
B_SUM <- numeric(N) # B6-B25の範囲の数値の合計
COMMENT <- character(N) # 受講者の感想があるので、それも取る
A <- which(LETTERS== "A")
G <- which(LETTERS== "G")
library(stringr)
for (i in seq_len(N) ){
print(files[i])# ファイル名は12345678-山本五十六.xlsxという形式なのでIDと名前を分離
IDS[i] <- str_extract(files[i], "\\d{8}")
NAMES[i] <- str_extract(files[i], "(\\p{Han}|\\p{Hiragana}|\\p{Katakana}){2,}")
#ファイル読み込み
wb <- try(loadWorkbook(files[i], create = FALSE))
if(class(wb) == "try-error") {
## ファイルの読み込みに失敗した場合
A6[i] <- B6_SUM[i] <- COMMENT[i] <- NA
} else {
## ファイルが読み込めれば、必要なセルをチェックする
a6_check <- try(getCellFormula(wb, "Sheet1", 6, A), silent = TRUE)
A6[i] <- ifelse(class(a6_check) == "try-error", NA, a6_check)
G6_25check <- try (readWorksheet(wb, sheet = "Sheet1", startRow = 6, endRow = 25,
startCol = G, endCol = G, header = FALSE), silent = TRUE)
## 空のベクトルないしデータフレームが取得されている可能性もあるので NROW()で行数をチェック
B6_SUM[i] <- ifelse(class(G6_25check) == "try-error" || NROW(G6_25check) < 1,
NA, sum(G6_25_check) )
comment_check <- try (readWorksheet(wb, sheet = "Sheet1"))
COMMENT[i] <- ifelse(class(comment_check) == "try-error", NA, comment_check)
}
}
## ループを抜けてからデータフレームを生成
dat <- data.frame(IDS, NAMES, A6, B6_SUM, COMMENT)
## ファイルに保存(Windowsで開けるように文字コードを設定)
write.csv(dat, file = "hyouka.csv", row.names = FALSE, fileEncoding = "CP932")
その上でデータフレームに保存された「式」などをチェックすればいい(上記のループの中でしてしまってもいいけど)。