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

2021年5月16日 23:08

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

エクセルで、こういう計算を、簡単な数式でできないでしょうかね？

毎回悩んで、（少なくとも簡単な数式では）無理だな、、、と諦めてます。 pic.twitter.com/hlquaTQTUr
— はけた＠経理のExcel仕事術発売中 (@excelspeedup) May 15, 2021

最終的には、こんな処理まで視野に入っています。
（列数が増えただけです） pic.twitter.com/6QiqrO0tcS
— はけた＠経理のExcel仕事術発売中 (@excelspeedup) May 15, 2021

面白い問題だったので 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」

A〜D列: 「例題13_trn」

一番最初に思いつくのは，「例題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」を配置します．

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

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

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

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

分離できています．

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

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

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

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

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

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

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

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

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

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

区分1〜4の全判定

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

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

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

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

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

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

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

ポリシー選択

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

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

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

得られる結果

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

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

得られる結果

マスター登場順優先

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

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

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

結果は以下の通りです．

まとめ

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

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

この記事が気に入ったらサポートをしてみませんか？