植物を触れないのでケーレス購入サポートツール作成

URLをコピーしました！

植欲が。。。。。

単身赴任で植欲が溜まっています。

なので

エクセルでケーレスの購入支援ツールを作成してみました。

いつもは種の名前をドラッグして右クリックで検索して。。。。面白そうならカートに入れる。

と一つ一つ見ていましたが、結構時間がかかる。

一覧で画像があれば、

知らない種でも自分が好きそうな植物か判別しやすいと思いと思い作りました。

今回は、作り方を解説します。（office365)

概要

ケーレスの

Download seedlist of cacti, succulents, Mesembryanthemum and Tillandsia – Koehres-Kakteen Online Shop

にあるPDFを

エクセルのクエリで落とし込んで、

種の名前でgoogle画像検索し、

最初の５個の画像をセル内に表示させる関数を作っていきます。

エクセルの情報を更新すれば、自動でケーレスの最新版の価格表を引っ張ってきます。

出来上がりはこんな感じ。

ユーフォルビアだけでもいろいろあるので画像で選べればわかりやすいですよね。

しかも5画像あれば大体雰囲気がわかると思います。

早速作っていきます（仕事でも使ってみてね！！）

データ取得

新規のエクセルを作成して

データタブのWebからをクリック

URLの欄に

ケーレスのPDFリンクのURLをブラウザーからコピーして貼り付けます

ブラウザーに表示されているURLをコピーして

あわせて読みたい

貼り付けてOKを押下。

すると

あたらいいウインドウでナビゲーターが出てくるのでTable001（Page 1）をクリックします。

すると右側のプレビューの欄にリストが表示されるので、

下の読み込みの▼を押下して読み込みを選択。

しばらくすると、ウインドウが消え

エクセルの画面でリストが表示されます。

右のTabel001(Page 1)をWクリックするとクエリの設定が開きます。

Dataの右にある展開マークをクリックするとポップが出るので、展開を選んでOKをクリック。

すると、全ページのデータが表示されるようになります。

このままだと、重複しているものがあるので整理します。

Kindという列の展開マーク▼を押して　Tabeleのみチェックを入れOKを押します。

次にホームのタブの、変換の項目にある1行目をヘッダーとして使用をクリックします。

すると

表の1行目についていたタイトルが列のタイトルとして認識されます。

そのあとに、左の3つの列タイトルを右クリックして列の削除をクリックします

成形

種子名の列を選択しホームタブの分割にある列の分割の展開▼を押して区切り記号による分割をクリック

など、データを見やすく整理していきます。

整理した内容は↓に置いておくのでホームのクエリの詳細エディターに張り付ければ同じものができます。

let
// — PDF 読み込みと前処理 —
ソース = Pdf.Tables(Web.Contents(“https://www.kaktus-koehres.de/Downloads/sukkulenten.pdf”), [Implementation=”1.3″]),
展開されたData = Table.ExpandTableColumn(ソース, “Data”, {“Column1”, “Column2”, “Column3”, “Column4”, “Column5”, “Column6”, “Column7”}, {“Data.Column1”, “Data.Column2”, “Data.Column3”, “Data.Column4”, “Data.Column5”, “Data.Column6”, “Data.Column7”}),
フィルターされた行 = Table.SelectRows(展開されたData, each ([Kind] = “Table”)),
昇格されたヘッダー = Table.PromoteHeaders(フィルターされた行, [PromoteAllScalars=true]),
型変更 = Table.TransformColumnTypes(昇格されたヘッダー, {
{“Table001”, type text}, {“Table001 (Page 1)”, type text}, {“Table”, type text},
{“Column4”, type text}, {“Column5”, type text}, {“Column6”, type text},
{“Artikel”, type text}, {“Portion/€”, type text}, {“100 Seeds/€”, type text}, {“1000 Seeds/€”, type text}
}),

// --- カンマがある列を右にずらし、行長を一定に保つ処理 ---
全列 = Table.ColumnNames(型変更),
対象列名リスト = {"Column6", "Artikel"},
対象列Indexリスト = List.Transform(対象列名リスト, each List.PositionOf(全列, _)),

ShiftRow = (row as list, colIndex as number) as list =>
    let
        colValue = row{colIndex},
        result =
            if colValue is text and Text.Contains(colValue, ",") then
                let
                    left = List.FirstN(row, colIndex),
                    right = List.Skip(row, colIndex + 1),
                    shifted = List.InsertRange(right, 0, {colValue}),
                    combined = left & {null} & shifted,
                    trimmed = List.FirstN(combined, List.Count(row))  // ← 列数を元の行数に合わせる！
                in
                    trimmed
            else
                row
    in
        result,

処理済み = Table.FromRows(
    List.Transform(
        Table.ToRows(型変更),
        each List.Accumulate(
            対象列Indexリスト,
            _,
            (currentRow, colIdx) => ShiftRow(currentRow, colIdx)
        )
    ),
    全列
),
結合された列 = Table.CombineColumns(Table.TransformColumnTypes(処理済み, {{"Artikel", type text}}, "ja-JP"),{"Column5", "Column6", "Artikel"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"結合"),
区切り記号による列の分割 = Table.SplitColumn(結合された列, "結合", Splitter.SplitTextByEachDelimiter({" 5"}, QuoteStyle.Csv, true), {"結合.1", "結合.2"}),
変更された型 = Table.TransformColumnTypes(区切り記号による列の分割,{{"結合.1", type text}, {"結合.2", type text}}),
追加されたプレフィックス = Table.TransformColumns(変更された型, {{"結合.2", each "5" & _, type text}}),
区切り記号による列の分割1 = Table.SplitColumn(追加されたプレフィックス, "結合.1", Splitter.SplitTextByEachDelimiter({" 10"}, QuoteStyle.Csv, true), {"結合.1.1", "結合.1.2"}),
変更された型1 = Table.TransformColumnTypes(区切り記号による列の分割1,{{"結合.1.1", type text}, {"結合.1.2", type text}}),
追加されたプレフィックス1 = Table.TransformColumns(変更された型1, {{"結合.1.2", each "10" & _, type text}}),
区切り記号による列の分割2 = Table.SplitColumn(追加されたプレフィックス1, "結合.1.1", Splitter.SplitTextByDelimiter(" (", QuoteStyle.Csv), {"結合.1.1.1", "結合.1.1.2"}),
変更された型2 = Table.TransformColumnTypes(区切り記号による列の分割2,{{"結合.1.1.1", type text}, {"結合.1.1.2", type text}}),
追加されたプレフィックス2 = Table.TransformColumns(変更された型2, {{"結合.1.1.2", each "(" & _, type text}}),
削除された列 = Table.RemoveColumns(追加されたプレフィックス2,{"Table001", "Table001 (Page 1)", "Table"}),
#"名前が変更された列 " = Table.RenameColumns(削除された列,{{"Column4", "発注番号"}, {"結合.1.1.1", "名前"}, {"結合.1.1.2", "（DE)"}, {"結合.1.2", "10K"}, {"結合.2", "5K"}})
in
#"名前が変更された列 "

Succulents Seeds用

最後に閉じて読み込むをクリックします。

で

できたテーブルがこちら。

大体きれいに仕上がりました。（一部ずれがありますが元のデータのずれなのでそのままにしています）

ここから

google先生の

画像検索結果を取り込む

数式を作っていきます。

Hの列までテーブルができているので

追加でI以降のタイトル行に

検索先情報（WEBSERVICE関数）　 1 　2　 3 　4　 5

と入力していきます。（

検索ページの情報取得

I2のセルに

=WEBSERVICE(“https://www.google.com/search?q=”&[@名前]&”&tbm=isch”)

と入力します。（上記を貼り付けでもよいです）

エンターを押すと

下まで同じ関数が入っていきます。

このWEBSERVICE関数は、（）の中のURLの情報を取得する関数です。

検索URLの作成

googleで「塊根植物」を画像検索するとURLは

https://www.google.com/search?q=塊根植物&tbm=isch問情報がURLに出ていると思います

これを利用して塊根植物というところをBの列の種の名前に一行ずつ置き換えています。

検索ページの情報取得

上のURLで出てきたページの情報をWEBSERVICE関数が取得します。

出てきた結果は

<!DOCTYPE html PUBLIC “-//WAPFORUM//DTD XHTML Mobile 1.0//EN” “http://www.wapforum.org/DTD/xhtml-mobile10.dtd”><html xmlns=”http://www.w3.org/1999/xhtml” lang=”ja”><head><meta content=”application/xhtml+xml; charset=UTF-8″ http-equiv=”Content-Type”/><meta content=”no-cache” name=”Cache-Control”/><title>ADANSONIA digitata – Google 検索</title><style>a{text-decoration:none;color:inherit}a:hover{text-decoration:underline}a img{border:0}body{font-family:sans-serif;padding:8px;margin:0 auto;max-width:700px;min-width:240px;}.FbhRzb{border-left:thin solid #dadce0;border-right:thin solid #dadce0;border-top:thin solid #dadce0;height:40px;overflow:hidden}.n692Zd{margin-bottom:10px}.cvifge{height:40px;border-spacing:0}.QvGUP{height:40px;padding:0 8px 0 8px;vertical-align:top}.O4cRJf{height:40px;width:100%;padding:0;padding-right:14px}.O1ePr{height:40px;padding:0;vertical-align:top}.kgJEQe{height:36px;width:98px;vertical-align:top;margin-top:4px}.lXLRf{vertical-align:top}.MhzMZd{border:0;vertical-align:middle;font-

略

のように、検索ページの情報がそのまま文字出てできます。

ここから、画像のリンクを見つけて、それをそのままセルの中に表示させます。

画像リンクの抽出

次にJ2のセルに

=IMAGE(SUBSTITUTE(I2,LEFT(I2,FIND(“src=”&CHAR(34),I2,FIND(“metabota”,SUBSTITUTE(I2,”src=”&CHAR(34),”metabota”,$J$1))+1)+4),””))

と入力してエンターを押します。

すると

アダンソニアの画像が出てきます。

googleの画像検索のページでは

src=”

から画像のリンクが始まるのでsrc=”を探してそれ以降の文字を切り取っています。

ただし、src=”の一個目は検索画像とは関係ないリンクなので、それを飛ばして2個目（1個目は関係ない画像）の画像のリンクを取得しています。（かなり力技です。本当はもっと便利な関数があるのですが３６５でしか使えないようなので汎用性が低いため今回の関数で対処しています）

2個目Iの列のタイトル＋１のように処理していますので、Jの列でIに張り付けた情報をJ1に変更すれば2個目の画像が表示されます。

同様に5個作成すると

５個表示されます。

同様に、下の列も複写すれば全部の画像が表示されます。

これで、あとはサイズなどをお好みに整えれば完成です。

パキポディウムもこのとおり

絵で見て種を選べるので時短になると思います。

気に入ったものがあれば番号コピーして、

Quick Cart – Koehres-Kakteen Online Shop

に張り付ければそのまま注文できます。

かなり時間の節約になると思います。

ほかにも作ったのでおいておきます。

データを小さくするために、

Iの列のデータを

‘=WEBSERVICE(“https://www.google.com/search?q=”&[@名前]&”&tbm=isch”)

としていますので頭の

‘

を外して使ってください。

サイズ等は自分で調整してください。

サボテン	https://metabotanical.jp/excel/Cacti Seeds99.xlsx
多肉（今回）	https://metabotanical.jp/excel/Succulents Seeds99.xlsx
屋内植物	https://metabotanical.jp/excel/indoor99.xlsx
メセン	https://metabotanical.jp/excel/mesembry99.xlsx
小売	https://metabotanical.jp/excel/retail99.xlsx
卸売り	https://metabotanical.jp/excel/Wholesale99.xlsx