Excel セル式でRの%in%, Pythonのin(pandasのisin)のような処理をする

まとめ

• XMATCH
• ISNA

を組合せて使うとよい。

前提

• Office 365 の Excel

Filter などと併用しない場合は古いバージョンでもinのような処理自体はできる気はするが、未確認です。

お話

Excelでpower query, power pivot を使うと便利ですが、Filter, Sort, Unique のような dynamic array (?) を処理する関数群も使ってみると非常に便利です。

ある配列がほかの配列に含まれるかどうかを返すような処理が必要になり、調べてみるとCOUNTIFセル関数を使う例が出てきました。が、これはどうも「セル範囲」に対する参照を引数に取るものであって、「配列」は取れないため使えません。

さらに調べると、isnaとmatchを使った例が紹介されていました。要点は次の2点です。

• match(もしくはxmatch) を使うことで、セル参照ではなく配列を引数に取れる
• matchの結果でmatchしないものは#N/Aとなるので、isna関数でtrue, false の配列に変換する

不満

ちょっと脇にそれますが、愚痴です。

• Letで使える変数名に数値が含められないことと、含めたときにエラーメッセージが表示されること
• countifが配列に対して使えると明らかにシンプルになる場合があるので、何とかしてほしい...

例題&コード例

例題

共通のvalueを持つkeyが何件あるか。

Excel

=LET(
x, $A$1:$B$10,
xx, INDEX(x,,1),
xxx, INDEX(x,,2),
v, FILTER(xxx, xx=D6),
vv, NOT(ISNA(XMATCH(xxx, v,0,1))),
vvv, (ISNA(XMATCH(xx,D6,0,1))),
SUM(IF(vv=vvv,1,0))
)

R

d <- data.frame(key=c(1,1,1,2,2,3,3,3,3), value=c(10,20,30,20,40,10,20,50,60))
result <- sapply(unique(d$key), function(v) {vv <- d$value[d$key == v]; sum(d$value %in% vv) - length(vv)})

R最近書いてないのでこんな感じになりましたが、もっといいコード例を気軽にコメントしてくださいね！

Python

時間に余裕ができたら書きます...

参考