TEXTSPLIT関数の入れ子、TAKE関数との組合せでの不具合

Question

複数の検査項目を抜打ち検査でチェックした結果として以下のような表があったとします。

「○」は「問題なし」、「－」は「検査未実施」、「×」は「問題あり」です。

	1月	2月	3月	4月	5月	6月	7月	8月	9月	10月	11月	12月
検査項目01	○	－	○	○	－	○	○	－	－	○	○	○
検査項目02	○	○	－	×	○	－	×	○	○	○	－	○

各検査項目において、「問題なし」が直近で何回連続しているかをカウントするため、

以下の数式を作成しました。

=COUNTA(TEXTSPLIT(TAKE(TEXTSPLIT(CONCAT(LEFT(B2:M2,B2:M2<>"－")),"×"),,-1),"○"))-1

しかし、結果は0が返ってしまいました。

そこで、作業セルO2を用いて、O2セルに

=TAKE(TEXTSPLIT(CONCAT(LEFT(B2:M2,B2:M2<>"－")),"×"),,-1)

としてから、

=COUNTA(TEXTSPLIT(O2,"○"))-1

とすると8が返ります。

同様に、

=COUNTA(TEXTSPLIT(TAKE(TEXTSPLIT(CONCAT(LEFT(B3:M3,B3:M3<>"－")),"×"),,-1),"○"))-1

とすると、こちらも0が返ってしまいます。

そこで、作業セルO3を用いて、O3セルに

=TAKE(TEXTSPLIT(CONCAT(LEFT(B3:M3,B3:M3<>"－")),"×"),,-1)

としてから

=COUNTA(TEXTSPLIT(O3,"○"))-1

とすると、4が返ります。

これはバグでしょうか?

あるいはTEXTSPLITの入れ子、TAKE関数との組合せに何か制限があるのでしょうか?

Answer 1

> 質問文で提示されておられる数式は、求めておられる値が何なのか今一つ分かりません> 複数の検査項目を抜打ち検査でチェックした結果として以下のような表があったとします。> 「○」は「問題なし」、「－」は「検査未実施」、「×」は「問題あり」です。

・B列～M列までの各行のセルに、1月から12月までの各月に実施された
　検査項目ごとの検査結果が"〇"、"－"、"×"のいずれかの記号で示されている。

> 各検査項目において、「問題なし」が直近で何回連続しているかをカウントする

・ここでは「直近」、つまり12月時点における連続合格回数を
　各検査項目（行）ごとに求めようとしている。

・検査未実施の月（セルの値が"－"である列）は評価不能なので無視する。

　（抜き打ち検査＝不定期なので実施されない月もある）

・「問題あり」になった月（セルの値が"×"である列）で連続合格記録は途絶える。

・例示された表の場合、「検査項目01」については「（1月から12月まで）8回連続問題なし」、
　「検査項目02」については「（8月から12月まで）4回連続問題なし」という結果を
　数式によって得られればよい、ということになる。

> TEXTSPLIT関数の入れ子、TAKE関数との組合せでの不具合> =COUNTA(TEXTSPLIT(TAKE(TEXTSPLIT(CONCAT(LEFT(B2:M2,B2:M2<>"－")),"×"),,-1),"○"))-1

で、h-engine さんが作成された数式のロジック自体は概ね正しいのだけれど、
「 TAKE 関数と TEXTSPLIT 関数を1つの数式内で組み合わせた結果が期待通りにならない

（しかし作業セルを間にかませば期待通りの結果になる）のは何故？」
というのがご質問の主旨。

Answer 2

・TEXTSPLIT 関数の第 1 引数に渡すべきなのは単一の文字列である。

・TAKE 関数の戻り値はスピル配列である。

まず以上の 2 点を踏まえていただくとして、

> TAKE(TEXTSPLIT(CONCAT(LEFT(B2:M2,B2:M2<>"－")),"×"),,-1)

上記の TAKE 関数によって返されるのは、1 行 1 列のスピル配列です。
単一のセルや文字列ではありません。

> =COUNTA(TEXTSPLIT(TAKE(TEXTSPLIT(CONCAT(LEFT(B2:M2,B2:M2<>"－")),"×"),,-1),"○"))-1

そのスピル配列全体をそのまま TEXTSPLIT 関数に渡しても、正常な結果は得られません。

> そこで、作業セルO3を用いて、O3セルに> =TAKE(TEXTSPLIT(CONCAT(LEFT(B3:M3,B3:M3<>"－")),"×"),,-1)> としてから> =COUNTA(TEXTSPLIT(O3,"○"))-1> とすると、4が返ります。

上記の場合は単一のセルの値を TEXTSPLIT 関数に渡しているため、期待通りの結果を得られます。

1つの数式のみで完結させたいなら、次のような方法が考えられます。

・INDEX 関数によってスピル配列の1つの要素の値を抜き出し、 TEXTSPLIT 関数に渡すようにする。

---

=COUNTA(TEXTSPLIT(INDEX(TAKE(TEXTSPLIT(CONCAT(LEFT(B2:M2,B2:M2<>"－")),"×"),,-1),1,1),"○"))-1

---

・1 行 1 列のスピル配列全体を 1 つの文字列に変換した上、その文字列の長さ（"〇"の個数）を取得する。

---

=LEN(TEXTJOIN("",FALSE,TAKE(TEXTSPLIT(CONCAT(LEFT(B2:M2,B2:M2<>"－")),"×"),,-1)))

---

Answer 3

他の回答者さんからのご指導もあり、原因がつかめました。

TEXTSPLIT関数とTAKE関数との組合せ問題をお尋ねしたものであり、TEXTSPLITの引数が「文字列にみえているもの」であって「文字列」ではなかったことが原因でした。

「文字列」にしたところ、解決しました。

ご返信ありがとうございました。

Answer 4

ご返信ありがとうございます。

質問の主旨はsk.exeさんの再説明のとおりです。

従って、「〇 が 2個以上連続した回数」を求める別の数式を探しているわけではなく、TEXTSPLIT関数とTAKE関数の組合せで発生する問題の解決方法を尋ねたわけです。

sk.exeさんのご指摘により解決しました。

いろいろご教示いただきましたことに御礼申し上げます。

Answer 5

当方の質問の主旨を再説明していただき、ありがとうごさいます。

また、有用なご指摘をいだだき、数式を以下のように修正したところ、正しい結果が得られました。

誠にありがとうございました。

=COUNTA(TEXTSPLIT(CONCAT(TAKE(TEXTSPLIT(CONCAT(LEFT(B3:M3,B3:M3<>"－")),"×"),,-1)),"○"))-1