見出し画像

twitter で流れていた Excel の問題を解いてみる

IPOC株式会社 エンジニアリング部隊

こんな問題を見つけました．

面白い問題だったので ALZETA で解くことができるか試してみました．

問題の定義と難しさ

Excel の H〜L列で，「区分1〜4の値のパターンとそれに対応する結果値」が定義されています．この定義に基づいて，A〜D列の区分1〜4値に応じた結果値を決定して E列に記入する．という問題だと解釈できます．

ALZETA では，H〜L列 と A〜D列を別のデータで与えて，E列を計算するという問題にします．早速それぞれデータを Excel で作成して ALZETA にアップロードしました．（「例題13_*」となっているのは，この記事が 13番目の note 記事だからで，特別な意味はありません）

H〜L列: 「例題13_mst」

画像2

A〜D列: 「例題13_trn」

画像2

一番最初に思いつくのは，「例題13_mst」と「例題13_trn」を内部結合／外部結合してしまうことです．しかし，この問題のいやらしいのは，「例題13_mst」にはワイルドカード（"*"）が含まれていることです．内部結合，外部結合は，値を完全一致させる場合にのみ利用することができます．例えば，単純に結合してしまうと「例題13_trn」の1行目

A, AAA, X

は，「例題13_mst」の1行目

A, AAA, X, *, aaax

とマッチしてくれ！という気持ちになりますが，ALZETA の結合 PM では，「例題13_trn」の1行目の「区分4」は空データであるのに対し，「例題13_mst」1行目は "*" という文字列なので一致しないという判定になり，意図する結果になりません．

ま，もともと結合で単純に解決できるくらいなら，Excel でも vlookup で単純に解決できるので，こんな tweet は流れてこないのですが．

どうするか

結合を基本線に，少し工夫してみます．結合の問題は，ワイルドカードを「ハズレ」判定してしまうことなので，ワイルドカードの部分だけ特別扱いします．そのため，区分1〜4を一気に判定するのではなく，「区分1判定」「区分2判定」「区分3判定」「区分4判定」を行い，それぞれの判定において

1. 値一致でマッチしたらもちろん合格（「結合」利用）
2. ワイルドカードでマッチしたら合格

とし，区分1〜4判定全てに合格したものを採用します．「不合格」はレコード抹消で表現し，区分1〜4判定結果全てにレコードが存在する（→内部結合で区分1〜4判定を結合してを絞っていく）という方針です．

実は，複数の解が合格になります．たとえば「例題13_trn」の1行目

A, AAA, X

は，「例題13_mst」の1行目，2行目，5行目とマッチしますので答は3通りあります．

1行目

A, AAA, X, *, aax

2行目

A, AAA, *, *, aaaa

5行目

A, *, *, *, aaa

このうち，どの解を採用するかの条件の一つとしたいので，値一致とワイルドカードマッチにメリハリをつけることにします．

1. 値一致でマッチしたら合格，加点+1 点
2. ワイルドカードでマッチしたら合格．加点+0点

区分2判定

では区分判定を実装していきましょう．ここでは，ワイルドカードを含む「区分2」判定を先に作ってみます．

まず，「例題13_mst」と「例題13_trn」を配置します．

画像3

これからそれぞれのデータから「区分2」を引き出してマッチさせていきます．「例題13_mst」については「区分2」に加えて，項目「結果」を引き出します．項目「結果」は「例題13_mst」でレコードを特定できるキー項目となっていますのでこれで OK です．一方，「例題13_trn」はそういったレコード特定可能な項目がありませんので，それを「ランク」PMで作成します．項目名は「NR」としています．

画像4

そして，それぞれ項目引き出しを済ませました．

画像5

次に「例題13_mst」側で，区分2がワイルドカードなものとそうでないものを分離します．「条件分割」PM で「区分2」の値が "*" か否かで「区分2値指定あり」「区分2ワイルドカード」にデータを分割します．

画像6

中間ファイルで覗いてみましょう．

画像7

画像8

分離できています．

次は，それぞれ加点値情報を加えて，「例題13_trn」側と結合します．

値があるもの（非ワイルドカード）に「演算」PMで「区分2加点値」を固定値 1 で設定．

画像9

値がないもの（ワイルドカード）に「区分2加点値」を固定値 0 で設定（スクリーンショット略）

値があるもの（非ワイルドカード）について，mst 側と trn 側を結合．

画像10

値がないもの（ワイルドカード）について，mst 側の "*" は意味がないので削除します．

画像12

画像11

その後，trn と結合します．（このとき項目「区分2加点値」（全て値ゼロ）をキー項目とします…これ，結果としてデータを総掛けします）

画像13

結合後の結果は以下の通りです．例えばこのデータ上3行は，「1行目 (NRが1) は「区分2」の値が "AAA" なのだが，これは回答 aaay，aaa，bbb のどれにもマッチ（ただし加点値0）」という意味になります．

画像14

最後に，値があるもの（非ワイルドカード）と値がないもの（ワイルドカード）のマッチ結果を合わせます．

画像15

その結果は以下の通りです．

画像16

このデータは，先ほどと同様，1行目（NRが1）のデータに着目すると，「1行目 (NRが1) は「区分2」の値が "AAA" なのだが，これは回答 aax，aaaa には加点値1でマッチ，aaay，aaa，bbb には加点値0でマッチ（データに出てこない，ab，ba にはマッチしない）」という意味になります．

区分1〜4の全判定

残りの区分についても同じことを行います．

画像17

あとは，それぞれの結果から，

「NR（問題データ上のレコード番号）」ー「結果」

の組み合わせの存在の AND （内部結合）を取れば，可能な解の全パターンが出てきます．AND を取る際に，それぞれの区分値の加点値もキープしておきましょう．すなわち，まず以下の通り「NR」「結果」「区分X加点値」」のみ取り出し，

画像18

あとは，「NR」と「結果」をキーにして全結果を内部結合します．

画像19

以上により，以下の結果を得ます．

画像20

「1行目 (NRが1) は，結果 aaa, aaaa, aax」があり得るという結果になっていますね！その他の結果も想定通りだと思います．

ポリシー選択

あとは，複数ある結果のどれを選ぶかは，ポリシー次第です．思いつくポリシーを3点挙げてみましょう．

区分優先度による選択（区分の若番完全一致が優先）

「NR」→「区分1加点値（数値降順）」→「区分2加点値（数値降順）」→「区分3加点値（数値降順）」→「区分4加点値（数値降順）」のキー順序でソートし，先頭レコードを持ってくれば，区分優先度による結果選択となります．

画像21

得られる結果

画像23

加点合計（ロンゲストマッチ）

「演算」PM で「区分1加点値」〜「区分4加点値」を合計した「総合加点値」を計算し，その値が大きいものを選びます．

画像23

得られる結果

画像24

マスター登場順優先

多分 twitter 上ではこれを採用という流れだったのでは？と思います．

これは一旦，「例題13_mst」に「mst登場順」という項目を加えたデータを作成し，

画像25

これを結合して，mst登場順でソート，先頭レコード取り出し，という流れになります．（以下の図で赤枠の部分）

画像27

結果は以下の通りです．

画像26

まとめ

今回の複数の区分値の組み合わせを条件に，新しい区分値を設定する，という処理はビジネスデータ処理上結構登場する処理かと思います．やや PM 数は多くなってしまいましたが，ALZETA でこの問題が解けることがわかりました．また今回の場合，解の候補が複数発生しますが，複数ある解候補から一つの解を選択するポリシーも柔軟に設定できることも説明しました．

また，この問題が頻出するお客様がいらっしゃいましたら，ワイルドカード指定可能な結合 PM というのもカスタマイズで提供可能かと考えておりますので，ご相談ください！

いいなと思ったら応援しよう！