page contents

プログラマじゃなくてもできるExcelの自動編集ーKnimeの使用例

はじめに

社会人が使うアプリケーションのTOP5に入るのがだと思います。
フィルターかけてデータを絞り込んだり、別なファイルの値同士をまとめたり、システムエンジニアであればログを調べるのに使うなど、様々な画面でExcelを使っていると思います。
同じような作業をするのに、毎回手作業で操作するのは大変だけど、マクロの作り方が分からない、マクロを作っても行が変わったり、計算が変わって作り直すのに結構手がかかる、そんなあなたに向けておススメしたいのが、というデータ分析ツールです。

Knimeで何ができるの?

Knimeはデータ分析ツールなので、その神髄は重回帰分析やB木作成など、学の分野になりますが、データ分析は事前にデータのクレンジング(異常値を抜いたり、データの整形など、分析のための前準備)が必要になるので、表を編集するのは得意分野なのです。

具体的な以下のことができます。

  • 行や列のフィルタリング
  • 複数カラムを使った計算式カラムの追加
  • オートフィル
  • 行のソート
  • などなど

次から具体的な使用例を書いていきます。
使えそうだと思ったあなたはそのあと、インストールの方法も書いていきます。

Knimeの具体例

一覧からデータを絞り込んで、別なファイル(シート)に書き込む

起動した後の画面

knime,Excel,自動化,rpa

新しいワークフローの作成

knime,Excel,自動化,rpa
左上のウィンドウのようなアイコンをクリック。
knime,Excel,自動化,rpa
New KNIME Workflow を選択。
knime,Excel,自動化,rpa
好きな名前を入力。
knime,Excel,自動化,rpa
これで新規のWorkFlowができました。

読み込み元のデータ

項番セイメイ年齢郵便番号都道府県
1神保貞子シンボサダコ34107-8364愛知県
2廣瀬章二ヒロセショウジ44381-2824
3寺門朱里ジモンアカリ19215-8205
4高沢新一タカザワシンイチ21753-3435
5東山勝男トウヤマカツオ38888-4734
6木戸竜也キドタツヤ41188-6713
7森川佳乃モリカワヨシノ24686-4548
8二木謙多郎ニキケンタロウ28694-7275
9四宮講一ヨツミヤコウイチ36534-5971
10桝田マスダテル20892-5801
11大月杏奈オオツキアンナ52459-5402茨城県
12守屋悦代モリヤエツヨ56324-3243
13大下菜那オオシタナナ39480-3045
14生駒和彦ウブコマカズヒコ24868-7368
15前野章治郎マエノショウジロウ23083-5498
16市原初男イチハラハツオ36803-7529
17磯部静香イソベシズカ31974-0833
18三浦金蔵ミウラキンゾウ47706-0005岡山県
19山際幹雄ヤマギワミキオ5453-2356
20今井凛子イマイリンコ45590-0148
21牛尾隆明ウシオタカアキ20725-3287

上記のデータはテスト用に作成したものであり、実在する人物とは一切関係ありません。また、全体で300件のデータがありますが、全て表示すると長くなるので、21件を表示しています。

実行画面

一つ一つの作り方は次から説明しますが、まずは参考に下図をご参照ください。
このように、処理を一つずつアイコンで追加することで、Excelのデータを操作することが可能です。
マクロと比較して、とても分かりやすいと思います。

knime,Excel,自動化,rpa

上記は、一覧から、宮がつく県の方を抽出し、新しいExcelに書き込んでいます。
また、年齢層という項目を追加したり、姓名を追加するなど、いろいろな処理を行っています。
結果となるExcelの例を下図に貼付します。
ぜひ次の項からの作り方もご参照ください。

knime,Excel,自動化,rpa

Knimeアイコンの解説

Excelファイル読み込みーExcel Reader

knime,Excel,自動化,rpa,excel reader
IO > Read > Excel Reader をダブルクリック
この状態では実行できないので赤信号になっています。
ダブルクリックして設定しましょう。
knime,Excel,自動化,rpa,excel reader
・Column Names:チェックすると、列番号(上図では1)の列をカラム名に使用します。
・Row IDs:チェックすると、指定したカラム(上図ではA)を列番号として扱います。
・On evaluation error:Insert a mssing cellがおすすめです。
knime,Excel,自動化,rpa,excel reader
実行できるようになったので、黄色信号になりました。
あとは右クリックして「Execute」をクリックすると実行されます。
knime,Excel,自動化,rpa,excel reader
knime,Excel,自動化,rpa,excel reader
実行した結果はこうなります。
「?」になっているのは、データが無い状態です。

オートフィルーMissing Value

都道府県の項目が空いているので、これをExcelのオートフィルのように埋めてみます。

knime,Excel,自動化,rpa,missing value
Manipulation > Column > Transform > Missing Value をクリック。
編集元のデータが無いと動作しないので、上記で読み込んだデータの(右側の▲)をクリックし、そのままMissing Valueのインプット(左側の▲)につないであげます。
knime,Excel,自動化,rpa,missing value
文字列を一律で設定変更できますし、下図のようにカラムごとに設定もできます。
今回はカラムで設定してみましょう。
knime,Excel,自動化,rpa,missing value
都道府県をダブルクリックし、Previous Value*を選択します。
これは、空白のセルの一つ上のセルをコピーします。
knime,Excel,自動化,rpa,missing value
実行した結果が上図です。
?だった箇所が一つ上の値で代入されているのがわかりますね。

列の絞り込みーRow Splitter

knime,Excel,自動化,rpa,row splitter
Manipulation > Row > Filter > Row Splitter をダブルクリック。
knime,Excel,自動化,rpa,row splitter
都道府県が「宮」で始まる項目のみを抽出します。
「contains wild cards」にチェックを入れ、 「宮」の後にワイルドカード「*」を付ければOKです。
他にも、完全一致「case sensitive match」や正規表現「regular expression」が使えます。
「use pattern matching」以外にも、「use range checking」で値の範囲指定や「only missing values match」で空白の行を対象にできます。
「 only missing values match」 を使う場合、左ペインの「include~」(該当行を含む)を選択していると、アイコンの左側、上部の▲から項目が空白の行が表示されます。左ペインの「Exclude~」(該当行を含まない)を選択していると、上下が逆になります。
knime,Excel,自動化,rpa,row splitter
結果は上図の通り、宮から始まる「宮崎県」「」の項目だけ抽出されています。
knime,Excel,自動化,rpa,row splitter
右クリックのFilteredが上部の▲、Filtered Outが下部の▲になります。
この場合、下部の▲は「宮」から始まらない項目になります。
knime,Excel,自動化,rpa,row splitter
左列のRow IDが47から55まで空いているのがわかると思います。

ちなみに、同じような機能でRow Filterがあります。
Splitterとの違いは、文字通り、フィルタリングして合致する行しか出力しないか、合致/非合致を分割して両方出力できるようにするのか、です。

複数行の文字列を編集して結果行を追加ーString Manipulation

今度は文字列の行同士を結合したり、置き換える処理を使って、姓と名を結合した、フルネーム(姓名)をあらわす行を追加します。

knime,Excel,自動化,rpa,String Manipulation
knime,Excel,自動化,rpa,String Manipulation
結合するために、joinを使用します。
最初にjoinを選択し、それから左ペインから”姓”と”名”を選択します。
間に「” “」を入れているのは、姓と名の間に半角スペースを入れるためです。
ここでは、「Append Column」を選択しているので、「姓名」というカラムが追加されますが、
既存のカラムと置き換えたい場合は、「Replace Column」を選択しましょう。
他にも、文字列を置換する「Replace」や文字列を切り取る「Substr」、文字列を比較する「Compare」などがあります。
knime,Excel,自動化,rpa,String Manipulation
実行した結果、姓名という行が増え、姓と名を半角スペースでつないだ値が挿入されています。

条件式から行を追加ーRule Engine

他にも、例えば、年齢が10代以下を若年層、20代から30代を青年層、40代から50代を中年層、60代以上を壮年層と分けたい場合、Rule Engineを用いて条件式とそれに応じた結果を格納した行を作れます。

knime,Excel,自動化,rpa,Rule engine
Manipulation > Row > Other > Rule Engineをダブルクリック。
knime,Excel,自動化,rpa,Rule engine

上から順に実行されるので、最後の老年層の条件式は、TRUE => “老年層”でも同じ結果になります。
(TRUEは無条件で実行されます)
knime,Excel,自動化,rpa,Rule engine
実行した結果、年齢層というカラムに、年齢ごとにカテゴリが表記されています。

行の絞り込みーColumn Splitter

次は、実年齢を隠します。

knime,Excel,自動化,rpa,Column Splitter
Manipulation > Column > Filter > Column Filter をダブルクリック。
knime,Excel,自動化,rpa,Column Splitter
外したいカラムを左のペインに移動します。
knime,Excel,自動化,rpa,Column Splitter
実行した結果、年齢が表示されなくなりました。

カラムの並び替えーColumn Resorter

Excelに保存したときに使いやすいようにカラムを並び替えます。

knime,Excel,自動化,rpa,Column Resorter
Manipulation > Column > Transform > Column Resorter をダブルクリック。
knime,Excel,自動化,rpa,Column Resorter
姓名を先頭にし、年齢層をセイと郵便番号の間に挿入しました。
knime,Excel,自動化,rpa,Column Resorter
実行した結果、設定通りにカラムの順番が変わっています。

項番を振りなおすーRow ID

Excelに出力する前に項番を1から振り直します。

knime,Excel,自動化,rpa,row id
Manipulation > Row > Other > Row IDをダブルクリック。
knime,Excel,自動化,rpa,row id
もともとあった「Row ID」のカラムを置き換えるので、「Replace RowID」を選択します。
knime,Excel,自動化,rpa,row id
実行した結果、Row IDの項目が0番から振り直されています。
1番から振りたい、Rowがいらない、という方は次の方法で項番を作ってください。

項番の作り方ーMath Formula

knime,Excel,自動化,rpa,Math Formula
Manipulation > Column > Convert & Replace > Math Formula をダブルクリック。
そのままだと「RowID」アイコンの後に追加されるので、ここでは「Column Resorter」の後につなぎ直しています。
処理を分岐することができるのもKnimeのメリットの一つです。
knime,Excel,自動化,rpa,Math Formula
新しい行を追加するので「Append Column」を選択し、名前を項番にします。
(Row IDは自動で作成されたカラムのため、ここでは表示されません)
ROWINDEXはそのままの意味で列の番号を表しており、前述の通り0から始まっています。
そのため、ここでは1を追加することで、1番から作成することができます。
knime,Excel,自動化,rpa,Math Formula
実行すると、上図のように、項番の行が追加され、番号が1から始まる連番になっています。

Excelへの出力ーExcel Sheet Appender

いよいよExcelへの書き込みを行います。
Excel Writerでもできますが、せっかくなので、シートを追加するAppenderで実行し、複数シートの書き込みをやってみましょう。

knime,Excel,自動化,rpa,Excel Sheet Appender
IO > Writer > Excel Sheet Appender をダブルクリック。
knime,Excel,自動化,rpa,Excel Sheet Appender
読み込んだファイルと同じフォルダに、末尾に「_編集後」とつけて保存します。
シート名は「宮がつく県」です。
いくつか設定があるので、次で説明します。

Excel Sheet Appenderの設定を簡単に説明します。

  • Abort if file does not exist
    →チェックを入れると、ファイルが存在しない場合、エラーになります。チェックが無ければ新規で作成します。
  • Abort if sheet already exists
    →チェックを入れると、すでに同じ名前のシートが存在する場合、エラーになります。チェックが無ければ、上書きします。
  • open file after execution
    →実行後、対象のファイルを開きます。複数のシートを書き込んでチェック入れる場合、必ず最後に書き込むシートでチェックを入れてください。
  • add column header
    →Knimeのカラム名をExcelの1列目に書き込みます。
  • add row ids
    →KnimeのRow IDをExcelのA行に書き込みます。

せっかくなので、複数のシートを書き込む際の、ちょっとしたTIPSをご説明します。
複数のシートを書き込む場合、何もしないと早く終わった順番に書き込まれるため、シートの順番が一定になりません。
例えば、下図だと、上部のExcel Sheetが先に書き込まれるかもしれませんし、下部が先になるかもしれません。

knime,Excel,自動化,rpa,Excel Sheet Appender
上部のExcel Sheet Appender のオプションは下図です。
knime,Excel,自動化,rpa,Excel Sheet Appender

今回は下から実行させてみましょう。
そのためには、まず下のExcel Sheet Appenderを右クリックし、「Show Flow Variable ports」をクリックします。

knime,Excel,自動化,rpa,Excel Sheet Appender,show flow variable
knime,Excel,自動化,rpa,Excel Sheet Appender,show flow variable
クリックすると、赤い〇がアイコンの上部、左右に表示されます。

「Show Flow Variable ports」で表示される、赤い〇は変数の受け渡しを表すものです。
本来の具体的な使い方は本ページの別項で説明しますが、まずは左の赤い〇から、自分の後に実行したいアイコンに線をつなぐことで順序性を持たせられる、と覚えておきましょう。

knime,Excel,自動化,rpa,Excel Sheet Appender,show flow variable
左の赤い〇をドラッグして、上のExcel Sheet Appenderにつなぎ、分かりやすいように上のExcel Sheet Appenderを右に移動しました。
では実行してみましょう。
画面上部の緑の〇に白い△がつながっているアイコンをクリックしてください。
knime,Excel,自動化,rpa,Excel Sheet Appender,show flow variable
knime,Excel,自動化,rpa,Excel Sheet Appender,show flow variable
knime,Excel,自動化,rpa,Excel Sheet Appender,show flow variable

作成されたExcelを開くとこうなっています。

knime,Excel,自動化,rpa
knime,Excel,自動化,rpa

実際に実行してみて流れを見ないと分かりづらいですが、下のアイコンから実行され、上のアイコンが実行されることで、最初に「宮がつく県」シートが作成され、次に「宮がつく県_項番がRowID」シートが作成されています。

シート名に日付を入れたい場合ーCreate Date&Time Range

定期的に実行する場合、シート名に作成した日の日付を入れたくなると思います。
そこで、シート名に日付を入れる方法を説明します。

knime,Excel,自動化,rpa,Create Date&Time range
Other Data Types > Time Series > Manipulate > Create Date&Time Range をダブルクリック。
knime,Excel,自動化,rpa,Create Date&Time range
Output Type をDateに設定。
New column nameを適当に設定。ここではtoday。
Number of rowsを1(1日分あればよいため)に設定。
Starting PointおよびEnding Pointのどちらも、Use execution date&time にチェック。
knime,Excel,自動化,rpa,Create Date&Time range
実行すると、実行した日の日付データが作成されます。
knime,Excel,自動化,rpa,Create Date&Time range
続いて、Workflow Control > Variables > Table Row to Variable をダブルクリック。
knime,Excel,自動化,rpa,Create Date&Time range
stringの項目に、変数名を入力します。ここではtoday。
knime,Excel,自動化,rpa,Create Date&Time range
右クリックして、Variables Output をクリックすると、上図の画面が表示されます。
Nameにtoday、Valueに日付データが入っているのがわかります。
これで、today という変数名に、2020-01-13 というデータが格納されました。

次に、もう一工夫して、シート名に日付がついた変数を作成します。

knime,Excel,自動化,rpa,String Manipulation,variable
Workflow Control > Variables > String Manipulation(Variable) をダブルクリック。
Table Row to Variable から線をつなぎます。
knime,Excel,自動化,rpa,String Manipulation,variable
join を使って、「宮がつく県」と日付を結合し、新しい変数名「NewSheetName」に格納します。
knime,Excel,自動化,rpa,String Manipulation,variable
実行すると、新しく「NewSheetName」という変数ができているのがわかります。

続いて、String Manipulation(Variable) の右の赤い〇をドラッグし、Excel Sheet Appender につなぎます。
Excel Sheet Appenderの設定を変更します。

knime,Excel,自動化,rpa,String Manipulation,variable,Excel sheet appender
Name of the sheet 項目の右端の「V」というアイコンをクリックします。
knime,Excel,自動化,rpa,String Manipulation,variable,Excel sheet appender
Use Variableにチェックを入れ、NewSheetNameを選択します。
knime,Excel,自動化,rpa,String Manipulation,variable,Excel sheet appender
変数が選択されていると、Name of the sheetは入力できなくなるため、グレーアウトされます。
では、実行しましょう。

実行すると、以下のようにシート名に日付が付与された状態でExcelが作成されます。

knime,Excel,自動化,rpa,String Manipulation,variable,Excel sheet appender

Knimeのインストール

インストール自体は非常に簡単です。
単に圧縮ファイルを好きなところに解凍するだけです。

公式サイトにアクセス

https://www.knime.com/

Downloadをクリック

画面の右上に、黄色の背景に黒文字で、目立つようにDownloadとあるので、それをクリック。

②Download Knime をクリック

①Register for Help & Updates は入れなくても大丈夫。
画面上部の②Download Knimeをクリック。

ダウンロードするファイルを選択

32bitと64bitがありますが、今なら大体のPCが64bitだと思います。
また、exeファイルを実行してインストールするか、exeファイルを実行して解凍先を指定するか、zipファイルでダウンロードして解凍するかは好きなファイルを選択ください。

解凍できたら実行して完了

解凍できたら後はknime.exeを実行して完了。

Proxy経由で使う場合の注意点

おまけです。
もしProxy経由でKnimeを使用する場合、アップデート等に支障がでるので、設定を変更しましょう。

File > Preferences > General > Network Connections を開き、Active ProviderをManualに変更し、HTTPとHTTPSだけ、Proxyの設定を入れましょう。
このとき、SOCKSにも設定を入れるとうまく接続できなくなります。
また、UserとPasswordはあらかじめ入力しておきましょう。

メモリ使用量の変更

Knimeの標準は2048MBになっています。
使用するPCのメモリに余裕があるのであれば、増やしておくと実行速度が速くなる可能性があります。
設定は、直接ファイルを編集する必要があります。
knime.exeファイルと同じフォルダに、knime.iniというファイルがあるので、開いて「-Xmx」から始まる行の数字を変更しましょう。
末尾”m”はメガバイトの意味なので、”g”とすればギガバイトになります。

Knimeをバッチで動かす方法

別な記事でも記載していますが、下記のバッチファイルで実行できます。

(Knimeのパス)knime.exe -consoleLog -nosplash -reset –launcher.suppressErrors -application org.knime.product.KNIME_BATCH_APPLICATION -workflowDir=”(実行したいWorkFlowがある)ディレクトリのパス”

おわりに

だ、だと言われている昨今、Excelの自動化をしたいけど、RPAを導入してもらえない、マクロを作るのは大変だし、保守できない、とお悩みの方にはぜひ試していただきたいのが、無料で使えるし、保守が楽なKnimeです。

ご紹介したアイコンはほんの一部なので、こんなことしたいけど、できる? とか、分からない点があれば、お気軽にコメント欄までご記載ください。
すぐには返信できませんが、必ず返信いたします。

また、自動化に興味がある方は、ぜひ私が実践している術を書いた、下記の記事も読んでいただけると幸いです。

http://54.250.95.37/post-154/