Excel職人のためのPython入門｜表記揺れ・空欄・VLOOKUPを5分で置き換える【Colabテンプレ配布】

Excel職人のためのPython入門｜表記揺れ・空欄・VLOOKUPを5分で置き換える【Colabテンプレ配布】 データ分析入門
誰のための記事か
  • 普段はExcel中心で、前処理・結合・集計に毎回時間がかかっている方
  • 属人化や関数ズレ、人為的ミスを減らしたい方
  • 一度“型”にして、次回からはCSV差し替え→実行だけにしたい方
導入前後の実績（実務の数字）
  • 【導入前】
    →週次レポート作成 30分/回、表記揺れ修正 4件/月、VLOOKUPミス 5件/月
  • 【導入後】
    →5分/回（▲83%）、属人ミス 3件→0件、提案スライド追加時間 +25分
  • 【使った仕組み】
    →前処理辞書（媒体・デバイス・地域）、mergeで複合キー結合、groupbyで週次集計、to_excelで配布
  • 【データ規模】
    →300行×15列／月次（得意先の広告配信実績）
  • 【つまずきTOP2】
    →文字化け、日付崩れ

一度「自動で整える→結合→集計→Excel出力」の型を作ると、以降は差し替え実行だけで回せます。

1. Excelだけで回す限界と、Pythonに置き換える判断軸

置き換えのサイン

  • 毎週/毎月、同じ前処理・結合・集計を繰り返している
  • 担当者が変わると壊れる（参照セル/関数ズレ）
  • ファイルが重く、計算や保存に時間がかかる
  • 手作業ミス（コピペ忘れ、桁区切り、%混在）が起きがち

Pythonにするメリット

  • 一度コード化＝以降は「CSV差し替え→実行」で完了
  • 同じ結果を誰でも再現（属人化の解消）
  • 拡張性（複合キー結合、検品、欠損一括処理）
  • 速度（段階処理で大きなCSVも対応しやすい）

まずは 前処理＋結合＋集計＋Excel出力 の自動化だけでも、投資回収は十分に見込めます。

2. 3分でColab準備｜最初の成功体験（CSV→グラフ1枚）

手順
  1. Googleにログイン → Google Colab を開きます
  2. 新規ノートブックを作成します
  3. 左のフォルダアイコン → input.csv をアップロードします
  4. 下記をコピペ→実行します
import pandas as pd
import matplotlib.pyplot as plt

# 1) CSVを読み込みます（行数・列数と先頭5行を表示）
df = pd.read_csv('input.csv')
print('行数・列数:', df.shape)
print(df.head())

# 2) 'date' 列を日付型に変換します（文字列でも安全に変換）
df['date'] = pd.to_datetime(df['date'], errors='coerce', infer_datetime_format=True)

# 3) 日付順に並べて日別インプレッションの折れ線グラフを描画します
df.sort_values('date').plot(x='date', y='impressions')
plt.title('Daily Impressions'); plt.xlabel('Date'); plt.ylabel('Impressions'); plt.show()
このコードでやっていること
  • pd.read_csv：CSVを表形式のデータ（DataFrame）に読み込みます
  • to_datetime：文字列や不揃いな日付を日付型に直します
  • plot：日付×インプレッションの折れ線グラフを1枚描きます
  • まずは「動いた」体験を作ることが最優先です。ここから“掃除→結合→集計→出力”を積み上げます。

3. 前処理は「辞書化」と「型指定」で9割終わります

3-1. 表記揺れを辞書で一括統一します

ポイント
  • replace に辞書を渡すと、複数の揺れを一括で置き換えられます。
  • Excelで毎回手作業していた直しを一発で再現できます。
# 'YouTube' のように媒体名の表記ゆれを辞書で統一します
map_media = {
    'youtube': 'YouTube', 'Youtube': 'YouTube',
    'fb': 'Facebook', 'FB': 'Facebook', 'フェイスブック': 'Facebook'
}
df['media'] = df['media'].astype(str).replace(map_media)

3-2. 記号や単位を剥がして“数値化”します

ポイント
  • % を小数に、桁区切り,を削除し、k や「万」も実数化します。
  • 数字計算できるように、すべて数値型へ揃えます。
# %やカンマ、kや「万」などの単位を取り除き、数値に直します
def to_number(x):
    if pd.isna(x): return None
    s = str(x).strip().lower().replace(',', '')
    if s.endswith('%'):  return float(s[:-1]) / 100   # 例: '12.5%' -> 0.125
    if s.endswith('k'):  return float(s[:-1]) * 1_000 # 例: '100k' -> 100000
    if '万' in s:        return float(s.replace('万','')) * 10_000
    try: return float(s)
    except: return None

for c in ['ctr','cvr','spend']:
    if c in df.columns:
        df[c] = df[c].apply(to_number)

3-3. 欠損（空欄）の基本方針を決めます

ポイント

欠損の扱いはルール化が大切です。0埋め・除外など、方針を決めてから適用します。

# コストは0で埋め、日付が無い行は分析対象から外します
df['spend'] = df['spend'].fillna(0)
df = df.dropna(subset=['date'])

3-4. データ型を明示して事故を減らします

ポイント

「数値のはずが文字列だった」といった型の事故を未然に防ぎます。

# 整数/小数など、型を揃えて次の処理でのエラーを防ぎます
dtype_map = {'impressions':'Int64', 'clicks':'Int64', 'spend':'float64'}
df = df.astype({k:v for k,v in dtype_map.items() if k in df.columns})

4. VLOOKUPはmergeで再現＆強化（複合キー／外部結合）

# 取引明細 df（media_code, date, spend, clicks …）
# マスタ master（media_code, media_name, channel …）

# 4-1. 左結合（VLOOKUP的な動き）
df = df.merge(master, on='media_code', how='left')

# 4-2. 複合キー（media_code と date が両方一致）
cost = pd.read_csv('media_cost.csv')
df = df.merge(cost, on=['media_code','date'], how='left')

# 4-3. マッチ状況を検品（どの行が結合できたか）
chk = df.merge(master, on='media_code', how='left', indicator=True)
print(chk['_merge'].value_counts())  # both/left_only/right_only
このコードでやっていること
  • how='left' は VLOOKUPと同等の動き（左表を基準に右表の情報を付ける）です。
  • on=['key1','key2'] は 複合キーでの結合です。Excelでは難しい設定も簡単です。
  • indicator=True で 結合できた/できなかった行を数で確認できます（検品に便利です）。

5. 集計はgroupbyとpivot_tableで“再現可能な型”に

# 週次の基準日（週の始まり）を作ります
df['week'] = df['date'].dt.to_period('W').apply(lambda r: r.start_time)

# 媒体×週で基本指標を集計します
agg = (df.groupby(['media','week'])
         .agg(impressions=('impressions','sum'),
              clicks=('clicks','sum'),
              spend=('spend','sum'))
         .reset_index())

# 派生指標（CTR/CPM/CPC）を加えます
agg['ctr'] = agg['clicks'] / agg['impressions']
agg['cpm'] = agg['spend'] * 1000 / agg['impressions']
agg['cpc'] = agg['spend'] / agg['clicks']

# ピボット（行：週、列：媒体、値：インプレッション合計）
pv = pd.pivot_table(agg, index='week', columns='media',
                    values='impressions', aggfunc='sum', fill_value=0)
このコードでやっていること
  • groupby：媒体×週の単位で合計値を出し、同じ式で毎回再現します。
  • 指標（CTR/CPM/CPC）を自動計算して、比較しやすくします。
  • pivot_table：行×列×値の形に並べ替えて、視覚的に差が見やすくなります。

6. 共有は“戻す”か“見せる”か（Excel/スプシ/Looker）

# 3つのシート（raw/weekly_media/pivot_impr）でExcelに出力します
with pd.ExcelWriter('output.xlsx') as w:
    df.to_excel(w, sheet_name='raw', index=False)
    agg.to_excel(w, sheet_name='weekly_media', index=False)
    pv.to_excel(w, sheet_name='pivot_impr')
print('output.xlsx を出力しました')
このコードでやっていること
  • ExcelWriter を使って、複数シートに分けて書き出します。
  • 「整形後の明細」「集計」「ピボット」の3枚を、配布しやすい形で保存します。
運用の選択肢
  • Excelに戻す：既存の社内導線にそのまま乗せられます。
  • スプシに出す：gspread などで自動更新も可能です。
  • Looker Studio：出力CSV/スプシをつなぎ、差し替え＝自動更新にできます。

7. つまずき対処：文字化け・日付崩れ・改行ズレの一発チェック

7-1. 代表的な文字コードを順に試して当てます

for enc in ['utf-8-sig','utf-8','cp932','shift_jis','latin1']:
    try:
        test = pd.read_csv('input.csv', encoding=enc)
        print('OK ->', enc, test.shape)
        break
    except Exception as e:
        print('NG ->', enc, repr(e))
解説
  • 文字化けの多くは文字コードの不一致が原因です。
  • 上から順に試し、読めた時点で採用します（Windowsは cp932 が効くことが多いです）。

7-2. ‘date’ を含む列名を推定し、日付型に強制変換します

date_cols = [c for c in df.columns if 'date' in c.lower()]
for c in date_cols:
    df[c] = pd.to_datetime(df[c], errors='coerce', infer_datetime_format=True)
解説
  • 日付が文字列でも、自動で日付型に直します。
  • うまく変換できない値は NaT（日付の欠損）にして、後で扱いやすくします。

7-3. 列ズレ対策：引用符や区切り、改行コードの扱いを厳密にします

df = pd.read_csv('input.csv', encoding='utf-8-sig', quotechar='"', engine='python')
# タブ区切りの場合は sep='\t' を指定します
# df = pd.read_csv('input.tsv', sep='\t', encoding='utf-8-sig')
解説
  • フィールド内にカンマや改行があると列ズレします。quotechar='"' を指定して正しく囲みを扱います。
  • 書き出し側の仕様（区切り・引用符・改行コード）をあわせてチームで統一すると安定します。

8. ぜんぶ入りColabテンプレ（コピペOK）

このセルを丸ごと貼って実行すると、前処理→結合→集計→Excel出力まで一気に動きます。
列名はご自身のCSVに合わせて微調整ください。

# ==== 0) 必要ライブラリ ====
import pandas as pd
import matplotlib.pyplot as plt

# ==== 1) 入力 ====
INPUT_RAW = 'input.csv'           # 元データ
INPUT_MASTER = 'master_media.csv' # 例：媒体マスタ（任意）
OUTPUT_XLSX = 'output.xlsx'

# ==== 2) 読み込み（文字コードを当てながら読みます） ====
def read_safely(path):
    for enc in ['utf-8-sig','utf-8','cp932','shift_jis','latin1']:
        try:
            return pd.read_csv(path, encoding=enc)
        except Exception:
            pass
    raise ValueError('文字コードが合いません')

df = read_safely(INPUT_RAW)
master = read_safely(INPUT_MASTER) if INPUT_MASTER else None

# ==== 3) 前処理：表記揺れ・記号・型 ====
map_media = {'youtube':'YouTube','Youtube':'YouTube','fb':'Facebook','FB':'Facebook','フェイスブック':'Facebook'}
if 'media' in df.columns:
    df['media'] = df['media'].astype(str).replace(map_media)

def to_number(x):
    if pd.isna(x): return None
    s = str(x).strip().lower().replace(',', '')
    if s.endswith('%'):  return float(s[:-1]) / 100
    if s.endswith('k'):  return float(s[:-1]) * 1_000
    if '万' in s:        return float(s.replace('万','')) * 10_000
    try: return float(s)
    except: return None

for c in ['ctr','cvr','spend']:
    if c in df.columns:
        df[c] = df[c].apply(to_number)

if 'date' in df.columns:
    df['date'] = pd.to_datetime(df['date'], errors='coerce', infer_datetime_format=True)
df = df.dropna(subset=['date'])

dtype_map = {'impressions':'Int64','clicks':'Int64','spend':'float64'}
df = df.astype({k:v for k,v in dtype_map.items() if k in df.columns})

# ==== 4) 結合（VLOOKUP相当・複合キー可） ====
if master is not None and 'media_code' in df.columns and 'media_code' in master.columns:
    df = df.merge(master, on='media_code', how='left', indicator=False)

# ==== 5) 集計（週×媒体の基本指標と派生指標） ====
df['week'] = df['date'].dt.to_period('W').apply(lambda r: r.start_time)
agg = (df.groupby(['media','week'])
         .agg(impressions=('impressions','sum'),
              clicks=('clicks','sum'),
              spend=('spend','sum'))
         .reset_index())
if 'impressions' in agg.columns and 'clicks' in agg.columns:
    agg['ctr'] = agg['clicks'] / agg['impressions']
if 'impressions' in agg.columns and 'spend' in agg.columns:
    agg['cpm'] = agg['spend'] * 1000 / agg['impressions']
if 'clicks' in agg.columns and 'spend' in agg.columns:
    agg['cpc'] = agg['spend'] / agg['clicks']

pv = pd.pivot_table(agg, index='week', columns='media',
                    values='impressions', aggfunc='sum', fill_value=0)

# ==== 6) 可視化（任意の確認用） ====
if 'date' in df.columns and 'impressions' in df.columns:
    df.sort_values('date').plot(x='date', y='impressions')
    plt.title('Daily Impressions'); plt.xlabel('Date'); plt.ylabel('Impressions'); plt.show()

# ==== 7) 出力（配布しやすいExcel） ====
with pd.ExcelWriter(OUTPUT_XLSX) as w:
    df.to_excel(w, sheet_name='raw', index=False)
    agg.to_excel(w, sheet_name='weekly_media', index=False)
    pv.to_excel(w, sheet_name='pivot_impr')
print('完了：', OUTPUT_XLSX)
このテンプレの流れ
  • 読み込み → 表記揺れ統一 → 単位/記号を数値化 → 欠損・型を整理
  • 必要ならマスタ結合 → 週×媒体で集計 → Excelに3シートで書き出し
  • 次回はCSV差し替え→「すべてのセルを実行」で完了します。

9. サンプルCSVと運用フロー

列名の例（匿名・再現可能）

date, campaign, media_code, media, device, spend, impressions, clicks, conversions

date,campaign,media_code,media,device,spend,impressions,clicks,conversions
2025-07-01,cmp_a,M01,YouTube,Mobile,120000,450000,1200,45
2025-07-01,cmp_a,M02,Facebook,Desktop,80000,210000,680,22
2025-07-02,cmp_a,M01,YouTube,Mobile,90000,380000,980,38
2025-07-02,cmp_a,M03,Instagram,Mobile,60000,150000,520,17
2025-07-03,cmp_a,M02,Facebook,Mobile,70000,190000,610,20
運用フロー（誰が・いつ・何をするか）
  • 担当者がCSVを共有フォルダに保存 → Colabノートを実行 → output.xlsxを配布します。
  • 差し替え実行は 「すべてのセルを実行」→完了 のみです（Slackに完了メッセージを投稿）。
  • 編集用ノートは限定共有、配布物はExcel/閲覧専用シートにします。
実際によくあったつまづき
  • 文字化け：Windows出力のShift_JISを encoding='cp932' で読み、保存は UTF-8(BOM) に統一します。
  • 日付崩れ：to_datetime(errors='coerce') で強制変換し、Excelの 1900/1904 差を統一します。

10. 次に読む：深掘りガイド

11. FAQ（導入・運用でよくある質問）

Q1. Excelだけで何とかできませんか？
A. できます。ただし「毎回同じ作業」なら、Pythonで型化した方が速く・安全で・再現性も高いです。

Q2. どこからPythonに置き換えるべきですか？
A. 前処理 → 結合（VLOOKUP代替） → 集計 の順で十分効果が出ます。ここまでで8割の効率化が進みます。

Q3. 社内共有はどう運用すべきですか？
A. まずはExcel出力で運用し、将来はスプシ/Lookerへ移行するとスムーズです。更新はCSV差し替えだけに設計します。

Q4. 文系でも本当にできますか？
A. 本記事のコードはコピペで動くように設計しています。最初はCSV読み込み→1枚グラフで成功体験を作ってください。

Q5. 次に学ぶと良いものは何ですか？
A. groupby/merge の応用、to_excel の出力分岐、matplotlib の図表テンプレが実務で役立ちます。

まとめ

  • Excelの“毎回同じ”は、Pythonの独壇場です。
  • 前処理→結合→集計→出力 を 一度型化 すれば、以降は 差し替え→実行だけで回ります。
  • まずは“動かす体験”から始めて、徐々に自動化の範囲を広げていきましょう。

Excel職人は、Pythonも使いこなせ！！

コメント

タイトルとURLをコピーしました