EnglishEnglish العربيةالعربية PortuguêsPortuguês 日本語日本語
404 Not Found

404 Not Found

nginx

RでのExcelファイルの読み書き：readxlとwritexlの完全ガイド

前回のレッスンではCSVについて学びましたが、実際のプロジェクトにおけるデータの60％はExcelで保存されています。マネージャー、営業担当者、財務担当者など、誰もがExcelファイルを共有することを好みます。このレッスンでは、RでExcelファイルを読み書きするための標準的な手法であるreadxl + writexlについて学びます。これは（JDKを必要とするxlsxパッケージとは異なり）Javaに依存しません。

このレッスンを修了すると、複数のシートにわたる財務諸表を読み解き、書式設定済みのExcelレポートを作成できるようになります。

1. 学習内容

2. 月次財務報告書の経緯

(1) 悩み：30ものExcel作業に頭を抱えている

アリスは金融業界で働いており、毎月月初めに30の支店からの「月次売上報告書」をまとめる必要があります：

Excelに組み込まれているPower Queryを使用する場合、ファイル間の処理は複雑になります。Python openpyxl を使用すると処理が遅くなり、R xlsx を使用するにはJavaのインストールが必要になります――

(2) Rを用いた解決策

R 
# 1. Packing(Not dependent on Java)
install.packages("readxl")
install.packages("writexl")

# 2. List all Excel Documents
files <- list.files("reports/", pattern = "\\.xlsx$", full.names = TRUE)

# 3. Read all in bulk sheet
library(readxl)
all_data <- lapply(files, function(f) {
  list(
    sales = read_excel(f, sheet = "Sales Details"),
    payment = read_excel(f, sheet = "Payment"),
    stock = read_excel(f, sheet = "Inventory")
  )
})

# 4. Consolidated Analysis
library(dplyr)
combined <- bind_rows(lapply(all_data, function(d) d$sales))

たった5行のコードで、30個のExcelファイル×3シート＝90枚のワークシートを処理。これこそがreadxlの真価です。

100% 
graph TB
    A[30 branch offices Excel Documents] --> B[list.files List]
    B --> C[lapply Batch Read]
    C --> D[excel_sheets check sheet]
    D --> E[read_excel Each sheet]
    E --> F[bind_rows Merge]
    F --> G[group_by + summarise Summarize]
    G --> H[write_xlsx Generate Report]

    style A fill:#cce5ff
    style B fill:#d4edda
    style C fill:#fff3cd
    style D fill:#f8d7da
    style E fill:#e1d4ff
    style F fill:#ffe1d4
    style G fill:#cce5ff
    style H fill:#d4edda

3. Excelファイルの3つの形式

R 
mindmap
    root((Excel Documents<br/>3 Type of Format))
        .xlsx
            Excel 2007+
            Maximum @,000 rows of
            Modern Standards
            Tools: readxl / openxlsx
        .xls
            Excel 97-2003
            Maximum 65536 row
            Old Format
            Tools: readxl
        .xlsm
            Enable Macros
            Contains VBA
            Tools: readxl Do not read macros
        Select
            New Project: .xlsx
            Old data: .xls
            Han Hong: .xlsm

(1) フォーマットの比較

形式 ファイル拡張子 行数・列数の最大値 互換性 読みやすさ
Excel 2007以降 .xlsx 1048576 × 16384 現代標準 readxl / openxlsx
Excel 97-2003 .xls 65536 × 256 レガシー形式 readxl
Excelでマクロを有効にする .xlsm xlsxと同じ VBAを含む readxl（マクロは読み込まない）

(2) Excelファイルを読み込むための3つのRパッケージの比較

パッケージ 依存関係 速度 機能 推奨度
readxl 純粋なR (C++) 高速 読み取り専用 ⭐⭐⭐ 読み込みに最適な選択肢
writexl 純粋な R (C++) 高速 書き込み専用 ⭐⭐⭐ 書き込みに最適な選択肢
openxlsx 純粋なR 中国語 読み取り・書き込み + 書式設定 ⭐⭐ 書式設定が必要な場合に利用
xlsx Javaが必要 処理が遅い 読み取り/書き込み + 数式 ❌ 推奨されません（依存度が高い）
💡 ヒント：書式設定を必要としない読み取り専用データの場合は、readxl ＋ writexl を使用してください（最も簡単な方法）。セルの書式設定や数式が必要なデータの場合は、openxlsx を使用してください。xlsx パッケージの使用は避けてください（Java が必要です）。

4. readxlの4つの主要な機能

(1) 関数クイックリファレンス表

機能 目的
read_excel() .xlsx または .xls ファイルを開く（自動検出）
read_xlsx() 読み取り専用 .xlsx（処理が速い）
read_xls() 読み取り専用 .xls（旧形式）
excel_sheets() すべてのシート名を表示
R 
library(readxl)

# 1. List all sheet
sheets <- excel_sheets("report.xlsx")
print(sheets)
# [1] "Sales Details" "Payment"     "Inventory"    

# 2. Read as Specified sheet
sales <- read_excel("report.xlsx", sheet = "Sales Details")
# or press sheet Number
sales <- read_excel("report.xlsx", sheet = 1)

# 3. Read All sheet(Back list)
all_sheets <- lapply(excel_sheets("file.xlsx"), function(name) {
  read_excel("file.xlsx", sheet = name)
})
names(all_sheets) <- excel_sheets("file.xlsx")

(2) read_excelの8つの一般的なパラメータ

パラメータ 機能 例
path ファイルパス "data/sales.xlsx"
sheet シート名または番号 1 または "Sales Details"
range 読み取り範囲 "A1:D100" または "A1:D100"
col_names TRUE / カスタムベクトル TRUE
col_types リストタイプ "text", "numeric", "date"
na NAマーカー c("", "NA")
skip 行をスキップ 2 (ヘッダー行をスキップ)
n_max 読み込む行の最大数 1000

(3) 実践編：一般的なパラメータ

R 
# 1. Specification sheet
df <- read_excel("data.xlsx", sheet = "Sales Details")

# 2. Specified Range(Avoid reading comments outside the table header)
df <- read_excel("data.xlsx", range = "A1:D1000")

# 3. Skip the header row
df <- read_excel("data.xlsx", skip = 2)

# 4. Read-only 1000 row
df <- read_excel("data.xlsx", n_max = 1000)

# 5. Specify Column Type
df <- read_excel("data.xlsx", col_types = c("text", "numeric", "date"))

# 6. Custom NA
df <- read_excel("data.xlsx", na = c("", "NA", "N/A"))

5. read_excel パラメータの詳細な説明

(1) sheet パラメータ

R 
# Method 1:sheet name(Recommendations)
df <- read_excel("data.xlsx", sheet = "Sales Details")

# Method 2:sheet number(Starting from 1)
df <- read_excel("data.xlsx", sheet = 1)

# Method 3:NULL(By default, read the first one)
df <- read_excel("data.xlsx")

(2) range パラメータ（最も有用）

Excelには、見出しやコメント、空白行が含まれていることがよくあります。正確に読み取るには、範囲を指定してください：

R 
# 1. String Range
df <- read_excel("data.xlsx", range = "A1:D100")

# 2. Anchor Cell(Automatically expand to non-empty areas)
df <- read_excel("data.xlsx", range = "A1:D1")  # Read-only 1 row(Automatic Scaling)

# 3. Complete A1 Quote
df <- read_excel("data.xlsx", range = "A1:Z1000")

# 4. Naming Area(Excel as defined in Named Range)
df <- read_excel("data.xlsx", range = "SalesTable")

(3) col_types パラメータ

R 
# Method 1:String Shorthand
col_types = c("text", "numeric", "date", "guess", "skip")

# Method 2:List
col_types = list(
  text,        # Character
  numeric,     # Numbers
  date,        # Date
  guess,       # Automatic Inference
  skip         # Skip
)

# Skip All(Do not read the data)
col_types = c("skip", "skip", "skip")

# All characters
col_types = c("text")

利用可能なタイプ："guess"（デフォルトの推論）、"logical"、"numeric"、"date"、"text"、"skip"、"list"（ネストされたテーブル）

6. Excelへの書き込み：writexl

(1) writexlのメリット

(2) 基本的な構文

R 
library(writexl)

# 1. Single sheet Output
write_xlsx(df, "output.xlsx")

# 2. Multiple sheet Output(Use list)
write_xlsx(
  list(
    "Sales Details" = sales_df,
    "Payment" = payment_df,
    "Inventory" = stock_df
  ),
  "output.xlsx"
)

# 3. Append to the existing file
# writexl Does not directly support additional entries,Required openxlsx

(3) 実践的な応用

R 
# Export R data frames in batch
list_of_dfs <- list(
  Sales = sales_df,
  Payment = payment_df,
  Inventory = stock_df
)
write_xlsx(list_of_dfs, "monthly_report.xlsx")
cat("=== The report has been generated:monthly_report.xlsx ===\n")
⚠️ 注意：writexlはセルの書式設定（色、フォント、数式）に対応していません。書式設定を行うには、openxlsxを使用してください：

R 
library(openxlsx)

# Write formatted text Excel
wb <- createWorkbook()
addWorksheet(wb, "Sales")
writeData(wb, "Sales", sales_df)
addStyle(wb, "Sales", style = createStyle(fontColour = "red"),
         rows = 2:10, cols = 5)
saveWorkbook(wb, "formatted_report.xlsx")

7. 実践編：財務諸表における複数のシートの一括読み込み

以下は、複数のシートを含む複数のファイルをバッチ処理する方法を示す、完全なワークフローの例です。

▶ サンプル：30の支店からの月次財務報告書の概要

R 
# ============================================
# 30 Summary of Monthly Financial Reports from Each Branch
# Features:Batch Read 30 ea Excel × 3 ea sheet
# ============================================

library(readxl)
library(dplyr)
library(purrr)
library(writexl)

# 1. Preparing Sample Data
set.seed(42)
make_sales <- function(city) {
  tibble(
    order_id = 1:5,
    date = as.Date("2024-01-01") + 0:4,
    product = sample(c("A", "B", "C"), 5, replace = TRUE),
    amount = sample(1000:5000, 5)
  )
}
make_payment <- function(city) {
  tibble(
    order_id = 1:5,
    paid = sample(c(TRUE, FALSE), 5, replace = TRUE),
    paid_date = as.Date("2024-01-15") + 0:4
  )
}
make_stock <- function(city) {
  tibble(
    product = c("A", "B", "C"),
    stock = sample(50:200, 3)
  )
}

# Simulation 3 Each branch's Excel(Each 3 sheet)
dir.create("temp_reports", showWarnings = FALSE)
for (city in c("Beijing", "Shanghai", "Guangzhou")) {
  write_xlsx(
    list(
      "Sales Details" = make_sales(city),
      "Payment" = make_payment(city),
      "Inventory" = make_stock(city)
    ),
    file.path("temp_reports", paste0(city, ".xlsx"))
  )
}

# 2. List all Excel Documents
files <- list.files("temp_reports", pattern = "\\.xlsx$", full.names = TRUE)
cat("Found", length(files), "ea Excel Documents:\n")
print(files)

# 3. View each file's sheet name
for (f in files) {
  cat("\nDocuments:", basename(f), "\n")
  cat("  Sheet:", paste(excel_sheets(f), collapse = ", "), "\n")
}

# 4. Read all in bulk sheet
cat("\n=== Bulk reading in progress ===\n")
all_reports <- lapply(files, function(f) {
  city <- tools::file_path_sans_ext(basename(f))
  list(
    sales = read_excel(f, sheet = "Sales Details") |> mutate(city = !!city),
    payment = read_excel(f, sheet = "Payment") |> mutate(city = !!city),
    stock = read_excel(f, sheet = "Inventory") |> mutate(city = !!city)
  )
})
names(all_reports) <- tools::file_path_sans_ext(basename(files))

# 5. Consolidate all sales data
all_sales <- bind_rows(lapply(all_reports, function(d) d$sales))
cat("\n=== Sales Summary ===\n")
print(all_sales)

# 6. Consolidate all payment receipt data
all_payment <- bind_rows(lapply(all_reports, function(d) d$payment))
cat("\n=== Summary of Receivables ===\n")
print(all_payment)

# 7. Merge all inventory data
all_stock <- bind_rows(lapply(all_reports, function(d) d$stock))
cat("\n=== Inventory Summary ===\n")
print(all_stock)

# 8. Comprehensive Analysis
cat("\n=== Sales Analysis by City ===\n")
city_stats <- all_sales |>
  group_by(city) |>
  summarise(
    Number of Orders = n(),
    Total Sales = sum(amount),
    Average Order = round(mean(amount), 2)
  ) |>
  arrange(desc(Total Sales))
print(city_stats)

# 9. Generate a Summary Report
write_xlsx(
  list(
    "Sales Summary" = all_sales,
    "Summary of Receivables" = all_payment,
    "Inventory Summary" = all_stock,
    "City Statistics" = city_stats
  ),
  "summary_report.xlsx"
)
cat("\n=== The summary report has been generated.:summary_report.xlsx ===\n")

# 10. Clear Temporary Files
unlink("temp_reports", recursive = TRUE)
▶ 試してみよう

期待される出力（抜粋）：

R 
=== Sales Analysis by City ===
# A tibble: 3 × 4
  city  Number of Orders Total Sales Average Order
  `<chr>`  `<int>`   `<int>`     `<dbl>`
1 Shanghai      5   14325    2865  
2 Beijing      5   12289    2458. 
3 Guangzhou      5   11234    2247.

❓ よくある質問

Q readxl で文字化けが発生した場合はどうすればよいですか？
A readxl は UTF-8 および GBK エンコーディングを自動的に処理します。それでも文字化けが発生する場合は、読み込む前に iconv を使用してファイルのエンコーディングを変換してください。
Q 特定のシートを表示するにはどうすればよいですか？
A read_excel("file.xlsx", sheet = "Sales Details") または sheet = 1（番号指定）。excel_sheets(file) を実行すると、すべてのシート名が表示されます。
Q 特定の範囲のみを読み取るにはどうすればよいですか？
A range = "A1:D100" を使用してセル範囲を指定してください。または、range = "A1:D1" を使用してヘッダーに固定し、自動的に範囲を拡張するように設定してください。
Q セルが結合されたExcelファイルをどのように読み込めばよいですか？
A 結合されたセルには、左上隅の値のみが含まれており、その他の位置は空欄です。readxlはこれらを「結合されていない」ものとして扱うため、結合領域内の「左上隅以外」の位置は NA となります。この問題に対処するには、openxlsx および mergeCells パラメータを使用してください。
R 
library(openxlsx)
wb <- loadWorkbook("existing.xlsx")
addWorksheet(wb, "newsheet")
writeData(wb, "newsheet", new_df)
saveWorkbook(wb, "existing.xlsx")

📖 まとめ

📝 練習問題

  1. 基本演習：3つのシート（「Sales」、「Receivables」、「Inventory」）を含むExcelファイルを作成し、write_xlsx()を使用してエクスポートしてください。excel_sheets()を使用してシート名が正しいことを確認し、read_excel()を使用して「Sales」シートを読み込み、データが正しいことを確認してください。

  2. 基本演習：前の問題のExcelファイルを開き、range = "A1:B5" を使用して、1行目から5行目までのA列とB列のみを読み込んでください。読み込まれたのが2列と5行のみであることを確認してください。

  3. 基本演習：前の問題の「Sales」シートを読み込み、col_types = c("numeric", "date", "text", "numeric") を使用して列の型を明示的に指定し、その型が正しいことを確認してください。

  4. 応用演習：Excelで3つの支店をシミュレートしてください（各支店につき3枚のシート）。lapplyを使用してすべてのシートを一括読み込みし、bind_rowsを使用して同じ種類のデータを統合してください。各都市の総売上高を算出してください。

  5. 課題：openxlsx を使用して、書式設定済みの Excel ファイルを作成してください。① ヘッダー行を赤色の太字にします。② 数値列を右揃えにします。③ 数値列に千の位区切り記号を設定します。④ 列幅を自動調整に設定します。ファイルを formatted_report.xlsx として保存し、Excel で開いて結果を確認してください（スクリーンショットを撮って保存してください）。

Web-Tutorial.com

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

100%