Go to Qiita Advent Calendar Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@hachimitsunu

【実務効率化】広報で使う画像が、自社サイトのどのページに使われているかを自動抽出してみた

Posted at

バックオフィスの自分でも、生成AIを使って結構簡単に実装できました。
cursor + claude3.7sonnet に協力してもらいました。
テスト環境で上手くいったので、これから実務用のデータに置き換えておきます。

注意

  • スクレイピングを用いる時、対象のサイトがスクレイピングOKか確認してください
  • スクレイピングする際、対象のサーバーに迷惑をかけないよう、リクエストの間隔は調整してください（最低でも1秒以上）※どこぞの図書館のようなサーバーだった場合はこれでも捕まりますが....

##背景
広報で使っている画像の管理を全くしておらず、どこのページに何が掲載されているかイチから作る羽目になりました。（広報の人達が最初からちゃんと管理してればよかったのにさ...わしゃ事務員やで...）
サイトを一つ一つ見て、どの画像を使っているかなんて記録するなんてめんどくさすぎるので、webスクレイピングで解決しようと思いました。

出来たこと

  • Excel上で名前を簡単に複数選択できるフォーム
  • 指定Webサイトのどこに、探したい画像があるかを自動調査してExcelに出力

事前準備

  • 自社サイトで掲載している画像の名前（aaaa.png とかそういう感じ）
  • その画像に誰が載っているか

処理の流れ

  1. 調べたいサイトの一番上流のページを指定（例：https://webscraper.io/test-sites/e-commerce/allinone だったら、https://webscraper.io）
  2. そのページから辿ることができる、そのサイトの内部のページを全て検索し、シートのA列にある画像がページに含まれているか確認（https://webscraper.io 以外のサイトは見ない※Twitterとか）
  3. 含まれていたページのURLをC列に、, 区切りで出力

「誰が載っているか」の部分も、表記ゆれや入力の手間を考えて、VBAを使って名前を入力してもらおうと考えました。

1. Excel VBAで「名前一覧」から複数選択できるユーザーフォームを作る

前提・準備

  1. Windows版Excel（マクロ有効ブック：.xlsm）
  2. リボンに「開発」タブが見えていること（なければ_ファイル→オプション→リボンのユーザー設定_で有効化）
  3. VBAエディタを開く（Alt＋F11）

フォルダ構成イメージ

  • InputBook.xlsm（マクロ有効ブック）
    • table シート：テーブル名：T_name
    • scraping シート：B列でフォーム起動（テストで2つの画像をA列に入れてる）

ステップ1：標準モジュールのModule1に「ShowNamePicker」を作成

まず、VBAエディタで挿入 → 標準モジュールを選び、Module1に以下を貼り付けます。

' Module1.bas
Option Explicit

' ダブルクリックされたセルをフォームに渡すグローバル変数
Public TargetCell As Range

' シートのBeforeDoubleClickイベントから呼び出すサブルーチン
Sub ShowNamePicker()
    Dim uf As UserForm1
    ' フォームのインスタンスを作成
    Set uf = New UserForm1
    ' 操作対象のセル（ActiveCell）をフォームに渡す
    Set uf.TargetCell = ActiveCell
    ' フォームを表示（Initialize → Activate が自動実行）
    uf.Show
End Sub

ステップ2：UserForm1を作成し、コントロールを配置

  1. VBAエディタの挿入 → UserFormでフォームを追加
  2. プロパティウィンドウでフォーム名を**UserForm1**に設定
  3. Toolboxから以下をドラッグ＆ドロップし、プロパティを設定（左下のところにプロパティを編集できるところがあります）
コントロール Name Caption 主な設定
ListBox lstNames （空欄） MultiSelect =fmMultiSelectMulti
Label lblPreview （空欄） （そのまま）
CommandButton btnOK OK （そのまま）
CommandButton btnCancel キャンセル （そのまま）

各ブロックのサイズは、見やすいように大きくしちゃいました。

UserForm1 のコードを貼り付け

_左メニューにあるUserForm1_を右クリック→「コードの表示」をして、以下を丸ごと貼り付けてください。

' UserForm1
Option Explicit

' Module1 から渡された「編集対象セル」
Public TargetCell As Range

' 初回ロード：tableシートの T_name から名前を読み込む
Private Sub UserForm_Initialize()
    Dim ws  As Worksheet
    Dim tbl As ListObject
    Dim cel As Range

    ' 名前一覧を置いた「table」シートを指定
    Set ws  = ThisWorkbook.Worksheets("table")
    Set tbl = ws.ListObjects("T_name")

    ' ListBox をクリアしてから項目を追加
    Me.lstNames.Clear
    For Each cel In tbl.DataBodyRange.Columns(1).Cells
        Me.lstNames.AddItem cel.Value
    Next cel
End Sub

' フォーム表示時：既存セル値をプレビュー＆選択状態に反映
Private Sub UserForm_Activate()
    Dim existing As String
    Dim arr() As String
    Dim i As Integer, j As Integer

    ' 対象セルが空でなければ、既存値を取得
    If Not TargetCell Is Nothing Then
        existing = Trim(TargetCell.Value)
        If existing <> "" Then
            ' カンマ区切りで分割
            arr = Split(existing, ",")
            ' 各項目について、ListBoxで該当するものを選択状態にする
            For i = 0 To UBound(arr)
                For j = 0 To Me.lstNames.ListCount - 1
                    If Trim(arr(i)) = Me.lstNames.List(j) Then
                        Me.lstNames.Selected(j) = True
                        Exit For
                    End If
                Next j
            Next i
        End If
    End If
    
    ' プレビューを更新
    Call UpdatePreview
End Sub

' ListBoxの選択が変わったらプレビューを更新
Private Sub lstNames_Click()
    Call UpdatePreview
End Sub

' プレビューラベルを更新
Private Sub UpdatePreview()
    Dim i As Integer
    Dim selected As String
    
    selected = ""
    For i = 0 To Me.lstNames.ListCount - 1
        If Me.lstNames.Selected(i) Then
            If selected = "" Then
                selected = Me.lstNames.List(i)
            Else
                selected = selected & ", " & Me.lstNames.List(i)
            End If
        End If
    Next i
    
    Me.lblPreview.Caption = "選択中: " & selected
End Sub

' OKボタン：選択された名前をカンマ区切りでセルに書き込み
Private Sub btnOK_Click()
    Dim i As Integer
    Dim result As String
    
    result = ""
    For i = 0 To Me.lstNames.ListCount - 1
        If Me.lstNames.Selected(i) Then
            If result = "" Then
                result = Me.lstNames.List(i)
            Else
                result = result & ", " & Me.lstNames.List(i)
            End If
        End If
    Next i
    
    ' 対象セルに書き込み
    If Not TargetCell Is Nothing Then
        TargetCell.Value = result
    End If
    
    ' フォームを閉じる
    Unload Me
End Sub

' キャンセルボタン：何もせずフォームを閉じる
Private Sub btnCancel_Click()
    Unload Me
End Sub

ステップ3：scrapingシートにダブルクリックイベントを設定

scrapingシートのコードエリアに以下を追加します：

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' B列がダブルクリックされた場合のみフォームを表示
    If Target.Column = 2 Then
        Cancel = True  ' 通常のダブルクリック動作をキャンセル
        Call ShowNamePicker
    End If
End Sub

動作イメージ

  1. scrapingシートのB列をダブルクリック
  2. UserForm1が開き、tableシートの名前一覧が表示される
  3. 複数選択してOKを押すと、カンマ区切りでB列に入力される

ステップ4：PythonでWebスクレイピング＆Excel自動更新

前提

  • Python 3.x
  • 必要なライブラリ：requests, beautifulsoup4, pandas, openpyxl, tqdm 
pip install requests beautifulsoup4 pandas openpyxl tqdm

スクリプトの動作イメージ

  1. ExcelのA列から画像ファイル名を読み取り
  2. 指定したWebサイトを全ページクロール
  3. 各ページで画像が使われているかチェック
  4. 結果をExcelのC列に自動記載

Pythonスクリプト全文

import requests
from bs4 import BeautifulSoup
from urllib.parse import urljoin, urlparse
import pandas as pd
from openpyxl import load_workbook
from tqdm import tqdm
import time

# 設定
INPUT_PATH = "test.xlsm"  # Excelファイルのパス
SHEET_NAME = "scraping"   # シート名
START_URL = "https://webscraper.io"  # クロール開始URL

def normalize(url):
    """URLを正規化（フラグメント除去など）"""
    parsed = urlparse(url)
    return f"{parsed.scheme}://{parsed.netloc}{parsed.path}"

def is_internal(url):
    """内部リンクかどうか判定"""
    return urlparse(url).netloc == urlparse(START_URL).netloc

def crawl_all(start_url, max_pages=100):
    """サイト内の全ページをクロール"""
    visited = set()
    queue = [start_url]
    pages = []
    
    while queue and len(pages) < max_pages:
        url = queue.pop(0)
        if url in visited:
            continue
        visited.add(url)
        
        print(f"クロール中: {url}")
        try:
            r = requests.get(url, timeout=10)
            r.raise_for_status()
            html = r.text
        except:
            continue
        pages.append((url, html))

        # サーバーに負荷をかけないよう1秒間隔を開ける
        time.sleep(1)

        soup = BeautifulSoup(html, "html.parser")
        for a in soup.find_all("a", href=True):
            full = normalize(urljoin(url, a["href"]))
            if full not in visited and is_internal(full):
                visited.add(full)
                queue.append(full)
    return pages

def main():
    # 1) Excelをpandasで読み込み
    df = pd.read_excel(INPUT_PATH, sheet_name=SHEET_NAME, engine="openpyxl")
    image_list = df.iloc[:, 0].dropna().astype(str).tolist()

    if not image_list:
        print("A列に画像パスが見つかりません。")
        return

    # 2) サイトクロール
    print("サイトクロール開始…")
    pages = crawl_all(START_URL)
    print(f"取得ページ数: {len(pages)}")

    # 3) マッピング：画像ごとに使われるページURLを収集
    mapping = {img: [] for img in image_list}
    for url, html in tqdm(pages, desc="ページ調査"):
        for img in image_list:
            if img in html:
                mapping[img].append(url)

    # 4) C列用リストを生成
    results = [", ".join(mapping.get(img, [])) for img in image_list]

    # 5) openpyxlでマクロ保持しつつC列を上書き
    wb = load_workbook(INPUT_PATH, keep_vba=True)
    ws = wb[SHEET_NAME]

    # ヘッダー（1行目）に「used_pages」を設定
    ws.cell(row=1, column=3, value="used_pages")
    # 2行目以降に結果を書き込む
    for i, val in enumerate(results, start=2):
        ws.cell(row=i, column=3, value=val)

    wb.save(INPUT_PATH)
    print("完了：Excelに書き戻しました。")

if __name__ == "__main__":
    main()

注意：Excelが開いていると書き込みエラーになるので、実行前に閉じておいてください！

結果は、C列に出力されます。
A2セルにある画像データを検索し、A.htmlとB.htmlとC.htmlの3つで使っていたと判定された場合、C2セルに

A.html, B.html, C.html

という感じで、C2セル内に, 区切りで出力されます。

使い方まとめ

  1. 上記Pythonコードを scrape_and_write.py として保存
  2. 同じフォルダに test.xlsm を配置
  3. ターミナルで python scrape_and_write.py を実行
  4. 終了後、test.xlsm のscrapingシートのC列に自動で結果が入る

おわり

以上、プログラマーのプロとかからしたら「なんじゃこれ...」となるかもですが、バックオフィスがこそこそ自動化する分には使えると信じてます。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?