こんにちは、usagi-sanです。今回は、R言語でエクセルファイルに色を塗る方法を紹介します。
統計解析を行う中で、解析結果の表などをエクセルに保存することがあると思います。解析結果を見やすくするために、エクセルファイルを開いてセルの編集を行ったり、マクロを作成したりすることがあり、Rだけでこの一連の操作を実行したいなと思った人は少なくないでしょう。
この記事で紹介する関数を用いれば、エクセルファイルのセルの色やフォントなどをRスクリプトから実行するだけで変更することができます。
関数は以下からダウンロード可能です。
また、この間数は次の記事で紹介されているパッケージにも含まれています。
-
R言語 自作パッケージ UsagiSan
こんにちは、usagi-sanです。 R言語の自作パッケージを紹介します。 統計解析のアルバイトをしている中、暇な時間を見つけて自分でパッケージを作ってみました。 Rのパッケージには、統計解析用のパッ ...
続きを見る
関数colorCells_excel
関数colorCells_xlsxの説明をします。
colorCells_xlsx(dataName, fileName, sheetName, coloredCols, coloredCondition = NULL, cellColor, fontSize = 11, fontName = "Yu Gothic", fontColor = "#000000")
| dataName | 編集したいエクセルファイルの名前。 |
| fileName | 編集後のファイル名。 |
| sheetName | 色塗りをするエクセルシートの名前。 |
| coloredCols | 色塗りをする列。numeric型のベクトル。 |
| coloredCondition | 色塗りをする行。logical型のベクトル。 |
| cellColor | セルの編集の際に適用される色。"#000000"（黒）のようにcharacter型で指定。 |
| fontSize | フォントサイズ。 |
| fontName | フォントの名前。 |
| fontColor | フォントの色。 |
また、関数colorCells_xlsxは以下となります。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | colorCells_xlsx <- function(dataName, fileName, sheetName, coloredCols, coloredCondition = NULL, cellColor, fontSize = 11, fontName = "Yu Gothic", fontColor = "#000000") { if (!is.character(dataName)) { stop("The data-name must be character") } if (!is.character(fileName)) { stop("The file-name must be character") } wb <- loadWorkbook(paste0(dataName, ".xlsx")) openxlsx::modifyBaseFont(wb, fontSize = fontSize, fontColour = fontColor, fontName = fontName) data <- as.data.frame(openxlsx::read.xlsx(paste0(dataName, ".xlsx"), sheet = sheetName, colNames = FALSE, skipEmptyRows = FALSE, skipEmptyCols = FALSE)) data <- replace(data, is.na(data), "") st <- openxlsx::createStyle(fontName = fontName, fontSize = fontSize, fgFill = cellColor) coloredRows <- as.numeric(rownames(data[coloredCondition, ])) for (i in coloredCols) { openxlsx::addStyle(wb, sheetName, style = st, cols = i, rows = coloredRows) } openxlsx::saveWorkbook(wb, paste0(fileName, ".xlsx"), overwrite = TRUE) } |
この後、関数colorCells_xlsxの使用例についてみていきます。実際に、解析結果を色塗りしていきます。
関数colorCells_xlsxの使用例
特定の行に色を加える
エクセルファイルの特定の行に色を加えていきます。
まず、関数やエクセルファイルの入出力に必要なパッケージを準備しましょう。
1 | library(openxlsx) |
次のようにデータセットinfertを用いて重回帰分析を行います。この解析結果をwrite.xlsxを用いて、エクセルファイルを作成します。下を実行すると作業ディレクトリに"重回帰分析.xlsx"というファイルが作成されます。
1 2 3 4 5 6 7 8 9 10 11 12 | dataset <- infert factorNames <- c("parity", "induced", "case", "spontaneous") for(i in factorNames) { dataset[, i] <- as.factor(dataset[, i]) } summaryResult <- summary(lm(stratum ~., data = dataset)) fileName <- "重回帰分析" resultTable <- summaryResult$coefficients write.xlsx(resultTable, paste0(fileName, ".xlsx"), row.names = TRUE) |
解析結果であるresultTableは、次のように回帰係数やp値などで構成されています。この解析結果を見やすくするために、表のヘッダーの色塗やp値に関して色塗りをしていきます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | > resultTable Estimate Std. Error t value Pr(>|t|) (Intercept) 2.7874320 10.7539066 0.2592018 0.795708396 education6-11yrs 13.8421341 8.6366036 1.6027289 0.110349271 education12+ yrs 41.2311038 17.9839609 2.2926598 0.022759669 age 0.2136807 0.2055004 1.0398065 0.299507615 parity2 -8.3264309 3.4368120 -2.4227194 0.016168498 parity3 -11.5811813 5.8563968 -1.9775267 0.049160752 parity4 -17.4029510 7.3890448 -2.3552369 0.019341084 parity5 -22.5877398 8.5018822 -2.6567928 0.008434219 parity6 -7.5298091 7.2498936 -1.0386096 0.300062937 induced1 -3.2058300 1.7852293 -1.7957525 0.073829520 induced2 -1.0059412 2.7644193 -0.3638888 0.716271034 case1 -0.6961095 1.6251661 -0.4283313 0.668805606 spontaneous1 0.7284681 1.8407999 0.3957345 0.692663009 spontaneous2 3.0258411 2.8585024 1.0585407 0.290905164 pooled.stratum 0.4001068 0.3136582 1.2756137 0.203362376 |
早速、"重回帰分析.xlsx"を編集していきます。まず、resultTableのヘッダーに対応する行を緑で塗っていきます。
次のようにcolorCells_xlsxの引数を指定すると、ヘッダーに対応する行に色を加えることができます。
1 2 3 | headerColor <- "#92D050" headerRows <- c(TRUE, rep(FALSE, nrow(resultTable))) colorCells_xlsx(fileName, fileName, "Sheet 1", seq_len(ncol(resultTable) + 1), headerRows, headerColor)#ヘッダーの色塗り |
次に、p値に関して色塗りをしていきます。p値が0.1未満、0.05未満、0.01未満に関して色塗りをしていきます。
次のように、色塗りを行う条件とそれに対応する色を指定します。
1 2 3 4 5 6 7 | is_pvalueLessThan0.1 <- c(FALSE, summaryResult$coefficients[, "Pr(>|t|)"] < 0.1) is_pvalueLessThan0.05 <- c(FALSE, summaryResult$coefficients[, "Pr(>|t|)"] < 0.05) is_pvalueLessThan0.01 <- c(FALSE, summaryResult$coefficients[, "Pr(>|t|)"] < 0.01) color0.1 <- "#CCC0DA" color0.05 <- "#B7DEE8" color0.01 <- "#FCD5B4" |
色塗りを行う行と色を指定した後はこれらをリストに格納し、for文で色塗りを繰り返すことでp値に関して色塗りを行うことが可能です。
1 2 3 4 5 6 7 | conditionAndColor <- list(list(condition = is_pvalueLessThan0.1, color = color0.1), list(condition = is_pvalueLessThan0.05, color = color0.05), list(condition = is_pvalueLessThan0.01, color = color0.01)) for (i in conditionAndColor) { #p値が0.1未満、0.05未満、0.01未満をそれぞれ色塗り colorCells_xlsx(fileName, fileName, "Sheet 1", seq_len(ncol(resultTable) + 1), i$condition, i$color) } |
上を実行すると、解析結果である"重回帰分析.xlsx"は次のように変化しています。ヘッダーは緑になり、p値が0.1未満の行は紫、0.05未満の行は水色、0.01未満の行はピンクに変わっています。
エクセル色塗り
エクセルファイルを開かなくても簡単に色塗りができました。
自由に色塗り
実用性はないですが、この間数を用いれば様々な模様や絵をエクセルファイルに描くことが可能です。
次のように繰り返しcolorCells_xlsxを実行することで、自由自在にエクセルファイルのセルを編集することができます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | #色塗りの範囲を指定 height <- 20 width <- 20 fileName <- "エクセル色塗り" write.xlsx(matrix(rep(" ", height * width), nrow = height), "エクセル色塗り.xlsx", colNames = FALSE) colors <- c("#CCC0DA", "#B7DEE8", "#FCD5B4") for (i in seq_len(3)) { colorWidth <- (15 - 2 * (i - 1)) center <- 8 for (j in seq_len(ceiling(colorWidth / 2))) { cols <- (center - (j - 1)) : (center + (j - 1)) rowTop <- replace(rep(FALSE, height), j + (i - 1), TRUE) rowBottom <- replace(rep(FALSE, height), 16 - j - (i - 1), TRUE) colorCells_xlsx(fileName, fileName, "Sheet 1", cols, rowTop, colors[i]) colorCells_xlsx(fileName, fileName, "Sheet 1", cols, rowBottom, colors[i]) } } |
上記を実行すると、次の画像にある幾何学的模様を描くことができます（実用性は多分ないです）。
色塗りをする行と列を指定する引数coloredConditionとcolordColsを変更するだけで柔軟に編集できます。
幾何学的模様
まとめ
R言語でエクセルファイルのセルを色塗りする方法や実行例をみていきました。
この記事で紹介した関数を用いれば、エクセルファイルに出力した表などを簡単に編集することが可能です。
また、自由に色塗りで紹介したように、複雑な幾何学的模様を描くこともできます。
興味があったら是非使ってみてください。
