Excelデータ処理自動化入門：サンプルデータから始めよう

はじめに

Excelの作業を自動化したい！でも、どこから始めればいいのかわからない…。 そんな方のために、実践的なサンプルデータとPythonスクリプトを用意しました。

このチュートリアルで学べること

• サンプルデータの作成方法
• 実践的なExcel自動処理の基礎
• データクリーニングの基本テクニック

サンプルデータの入手方法

方法1：Pythonでサンプルデータを自作する

プログラミングに慣れている方は、以下のPythonスクリプトを使って自分でサンプルデータを作成できます。

import pandas as pd
import numpy as np
import datetime
import os

def generate_sales_data():
    """売上データのサンプルを生成"""
    # 日付の生成
    start_date = datetime.datetime(2023, 1, 1)
    dates = [start_date + datetime.timedelta(days=x) for x in range(365)]
    
    # 商品カテゴリ
    categories = ['文具', '家電', '食品', '衣類', '日用品']
    
    # データ生成用の空のリスト
    data = []
    
    # サンプルデータの生成
    for date in dates:
        for _ in range(np.random.randint(3, 8)):  # 1日あたり3-7件のデータ
            category = np.random.choice(categories)
            amount = np.random.randint(1000, 100000)
            data.append({
                '日付': date,
                '年月': date.strftime('%Y-%m'),
                '商品カテゴリ': category,
                '売上金額': amount,
                '担当者': f'担当者{np.random.randint(1, 6)}'
            })
    
    # DataFrameの作成
    df = pd.DataFrame(data)
    
    # 3つのファイルに分割して保存
    splits = np.array_split(df, 3)
    
    # 保存用のフォルダ作成
    if not os.path.exists('excel_files'):
        os.makedirs('excel_files')
    
    # ファイル保存
    for i, split_df in enumerate(splits):
        split_df.to_excel(f'excel_files/sales_data_{i+1}.xlsx', index=False)
    
    # 統合前の元データも保存
    df.to_excel('sales_data_original.xlsx', index=False)

def generate_messy_data():
    """クリーニングが必要な不完全なデータを生成"""
    # 基本データの作成
    data = {
        '顧客名': ['山田 太郎 ', ' 鈴木 花子', '佐藤 次郎  ', '山田 太郎', '  鈴木 花子 '],
        '年齢': [30, np.nan, 45, 30, 28],
        'メールアドレス': ['yamada@example.com', 'suzuki@example.com', '', 'yamada@example.com', 'suzuki_h@example.com'],
        '購入金額': [5000, 3000, 4000, 5000, 3000]
    }
    
    df = pd.DataFrame(data)
    df.to_excel('messy_data.xlsx', index=False)

if __name__ == "__main__":
    # サンプルデータの生成
    generate_sales_data()
    generate_messy_data()
    
    print("以下のファイルが生成されました：")
    print("1. excel_files/sales_data_1.xlsx")
    print("2. excel_files/sales_data_2.xlsx")
    print("3. excel_files/sales_data_3.xlsx")
    print("4. sales_data_original.xlsx")
    print("5. messy_data.xlsx")

このスクリプトを実行すると、以下のファイルが生成されます：

1. 売上データ3分割ファイル（sales_data_1.xlsxなど）
2. クリーニング用の不完全データ（messy_data.xlsx）
3. 検証用の完全データ（sales_data_original.xlsx）

方法2：サンプルデータをダウンロードする

プログラミングが初めての方は、以下のリンクから直接サンプルデータをダウンロードできます。

サンプルデータをダウンロード（excel_samples.zip）

※ zipファイルには以下が含まれています：

• 売上データ（3分割）
• クリーニング用データ
• 検証用データ

サンプルデータの内容説明

1. 売上データ（sales_data_*.xlsx）

• 1年分の日次売上データ
• 含まれる情報：
  • 日付
  • 年月
  • 商品カテゴリ（文具、家電、食品、衣類、日用品）
  • 売上金額
  • 担当者

2. クリーニング用データ（messy_data.xlsx）

以下のような「汚い」データを含んでいます：

• 重複したデータ
• 空白のセル
• 余分な空白を含む文字列
• 不統一なフォーマット

3. 検証用データ（sales_data_original.xlsx）

• 完全な形式の売上データ
• 自動処理の結果を検証する際の正解データとして使用

次のステップ

サンプルデータの準備ができたら、以下の自動処理にチャレンジしてみましょう：

1. 分割されたExcelファイルの結合
2. データのクリーニング
3. 月次レポートの自動作成

import pandas as pd
import os

def analyze_sales(df):
    """
    売上データを分析し、結果を返す関数
    """
    # データ分析
    analysis = df.copy()
    
    # 月次の集計
    monthly_sales = analysis.groupby('年月')['売上金額'].agg([
        ('合計', 'sum'),
        ('平均', 'mean'),
        ('件数', 'count')
    ]).round(2)
    
    # カテゴリ別集計
    category_sales = analysis.groupby('商品カテゴリ')['売上金額'].sum()
    
    return monthly_sales, category_sales

def clean_customer_data(df):
    """
    顧客データのクリーニングを行う関数
    """
    # データのコピーを作成
    cleaned = df.copy()
    
    # 文字列の前後の空白を削除
    for column in cleaned.select_dtypes(include=['object']):
        cleaned[column] = cleaned[column].str.strip()
    
    # 重複行の削除
    cleaned = cleaned.drop_duplicates()
    
    # 欠損値の処理
    cleaned['年齢'] = cleaned['年齢'].fillna(cleaned['年齢'].mean())
    
    return cleaned

def combine_excel_files(folder_path):
    """
    指定フォルダ内のExcelファイルを結合する関数
    """
    # 全てのExcelファイルを読み込み
    all_files = glob.glob(os.path.join(folder_path, "sales_data_*.xlsx"))
    
    # データフレームを格納するリスト
    df_list = []
    
    # 各ファイルを読み込んでリストに追加
    for file in all_files:
        df = pd.read_excel(file)
        df_list.append(df)
    
    # 全データフレームを結合
    combined_df = pd.concat(df_list, ignore_index=True)
    
    return combined_df

# メイン処理
if __name__ == "__main__":
    # 1. 分割ファイルの結合
    print("売上データファイルを結合中...")
    combined_sales = combine_excel_files("excel_files")
    combined_sales.to_excel("combined_sales.xlsx", index=False)
    
    # 2. 売上データの分析
    print("売上データを分析中...")
    monthly_summary, category_summary = analyze_sales(combined_sales)
    
    # 3. クリーニング処理
    print("顧客データのクリーニング中...")
    messy_data = pd.read_excel("messy_data.xlsx")
    cleaned_data = clean_customer_data(messy_data)
    
    # 結果の保存
    with pd.ExcelWriter("analysis_results.xlsx") as writer:
        monthly_summary.to_excel(writer, sheet_name="月次サマリー")
        category_summary.to_excel(writer, sheet_name="カテゴリ別売上")
        cleaned_data.to_excel(writer, sheet_name="クリーニング済データ")
    
    print("処理が完了しました！")

スクリプトの実行方法

1. 上記のコードをexcel_automation.pyとして保存します。
2. コマンドプロンプトまたはターミナルで以下を実行：

python excel_automation.py

何が起こるの？

このスクリプトを実行すると、以下の処理が自動的に行われます：

1. ファイルの結合処理：
   • excel_filesフォルダ内の分割された売上データファイルを1つに結合
   • 結合されたファイルはcombined_sales.xlsxとして保存
2. 売上データの分析：
   • 月次の売上集計（合計、平均、件数）
   • 商品カテゴリ別の売上集計
   • 結果はanalysis_results.xlsxの各シートに保存
3. データクリーニング：
   • 顧客データから重複を除去
   • 文字列の余分な空白を削除
   • 欠損値を適切な値で補完

出力ファイル

スクリプト実行後、以下のファイルが生成されます：

1. combined_sales.xlsx：
   • 結合された売上データ
2. analysis_results.xlsx：
   • 「月次サマリー」シート：月別の売上集計
   • 「カテゴリ別売上」シート：商品カテゴリごとの売上
   • 「クリーニング済データ」シート：整形された顧客データ

カスタマイズのヒント

このスクリプトは以下のように拡張できます：

1. 分析機能の追加：
   • 売上予測の追加
   • 成長率の計算
   • 異常値の検出
2. レポート形式の改善：
   • グラフの自動生成
   • 条件付き書式の適用
   • ピボットテーブルの作成

NameError: name 'glob' is not defined

このエラーが表示された場合：

原因：

• globモジュールがインポートされていません。

解決方法： スクリプトの先頭に以下の行を追加してください：

import glob

完全なインポート文は以下のようになります：

import pandas as pd
import os
import glob  # 追加

2. その他よくあるエラーと対処法

ModuleNotFoundError: No module named ‘pandas’

ModuleNotFoundError: No module named 'pandas'

解決方法： コマンドプロンプトで以下を実行してpandasをインストール：

pip install pandas

ModuleNotFoundError: No module named ‘openpyxl’

ModuleNotFoundError: No module named ‘openpyxl’

解決方法： コマンドプロンプトで以下を実行：

pip install openpyxl

PermissionError: [Errno 13] Permission denied

PermissionError: [Errno 13] Permission denied: 'combined_sales.xlsx'

解決方法：

1. 出力先のExcelファイルが開いていないか確認
2. ファイルを閉じてから再実行
3. 必要に応じて管理者権限でスクリプトを実行

実行時の注意点

1. フォルダ構成の確認：
   • スクリプトと同じフォルダにexcel_filesフォルダがあるか確認
   • 必要なサンプルファイルが正しい場所にあるか確認
2. パッケージのインストール確認：

# 必要なパッケージを一括インストール
pip install pandas openpyxl

3. ファイルの使用状態確認：
   • 処理対象のExcelファイルが開いていないことを確認
   • 出力先のファイルが他のプログラムで使用されていないか確認

これらのエラー対策を実施しても問題が解決しない場合は、以下を確認してください：

• Pythonのバージョン（3.6以上推奨）
• 各パッケージの最新バージョンへのアップデート
• Windows/Macなど、OSに応じた適切なパス指定

次回予告：より高度な自動化へ

次回は、このスクリプトをベースに、さらに進んだ機能を実装していきます：

• AIライクな自動インサイト発見
• インタラクティブなダッシュボード
• パターン分析機能

まずはこの基本的な自動化スクリプトを使いこなすことから始めてみましょう！

Minokamo Citizens Blog

Pythonで始めるExcel自動化と売上データ分析 Pythonとpandas、scipyを活用してExcelファイルを自動化し、売上データを効率的に分析する方法をステップバイステップで解説。スクリプト作成からエラーデバッグまで詳しく…