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

こんにちは。

横合いから失礼します。

最初から確認で恐縮なのですが、ここで求めておられるのは、関数の使い方（仕様など）についてでしょうか？、それとも、何らかの計算結果を求めるための数式についてでしょうか？。

既に、関数の使い方などについては様々なアドバイスが寄せられていますので、ここでは、下記のような計算結果を求める場合の数式について考えてみました。

• 1月～12月の間の「〇 の個数」を求める場合。
  この場合、「検査項目01」では答えは 8 となるはずですし、「検査項目02」では答えは 7 となるはずです。
• 1月～12月の間で「〇 が 2個以上連続した回数」を求める場合。
  この場合、「検査項目01」では答えは 3 となるはずですし、「検査項目02」では答えは 2 となるはずです。

質問文で提示されておられる数式は、求めておられる値が何なのか今一つ分かりませんが、手元の環境で確認した限り、いずれも数式としての計算結果そのものに間違いは無く、各関数の使い方（引数の指定など）が特殊すぎるため、結果がバグっているように見えただけかと思います。

ただ、それらの数式でご希望の結果が得られないとしたら、数式を組む際の考え方をもう一度確認した上で、関数の選択とその組み合わせ方などを考え直してみたほうが良いかと思うのですが、いかがでしょう？。

例えば、「〇 の個数」を求める場合は、下記の数式１または数式２を使ってみてください。一例です。

※最新版の Excel（ Microsoft365 の Excel、Web 版の Excel など）をお使いのようですので、数式１は配列数式にしなくても使えます。

・数式１

=SUM((B2:M2="〇")*1)

・数式２

=COUNTIF(B2:M2,"〇")

図１は、数式１を N2セルに入れ、これをコピーし、N3セルに貼り付けた状態です。

※検査項目が増える場合、更に下方向（行方向）に貼り付けて使うことも可能です。

・図１

また、「〇 が 2個以上連続した回数」を求める場合は、下記の数式３または数式４を使ってみてください。一例です。

※最新版の Excel（ Microsoft365 の Excel、Web 版の Excel など）をお使いのようですので、数式３、数式４はその前提で組んでいます。

なので、旧来のバージョンの Excel では使用できません。念のため。

・数式３

=SUM(MAP(B2:L2,LAMBDA(dt,IF(dt="〇",IF((OFFSET(dt,,-1)<>"〇")*(OFFSET(dt,,1)="〇"),1,0),0))))

・数式４

=REDUCE(0,B2:L2,LAMBDA(ac,dt,IF(dt="〇",IF((OFFSET(dt,,-1)<>"〇")*(OFFSET(dt,,1)="〇"),ac+1,ac),ac)))

※数式３や数式４では、計算対象のセル範囲を B2:L2 としています。これは、間違えている訳ではありません。

これを B2:M2 とした状態で N2セルにこれらの数式を入れた場合、「循環参照」に関する警告メッセージが表示され正しく計算されませんので、ご注意ください。

・LAMBDA 関数

https://support.microsoft.com/ja-jp/office/lambda-%E9%96%A2%E6%95%B0-bd212d27-1cd1-4321-a34a-ccbf254b8b67

・MAP 関数

https://support.microsoft.com/ja-jp/office/map-%E9%96%A2%E6%95%B0-48006093-f97c-47c1-bfcc-749263bb1f01

・REDUCE 関数

https://support.microsoft.com/ja-jp/office/reduce-%E9%96%A2%E6%95%B0-42e39910-b345-45f3-84b8-0642b568b7cb

図２は、数式４を N2セルに入れ、これをコピーし、N3セルに貼り付けた状態です。

※検査項目が増える場合、更に下方向（行方向）に貼り付けて使うことも可能です。

・図２

以上です。

各数式の動作や使い方について、何かご不明な点がありましたらお知らせください。

尚、求めたい値が上記以外の場合、数式を使ってどのような結果を求めたいのか、その数式を入れるセルアドレス（ N列［ N2セルなど ］で良いのでしょうか？）も含めて出来るだけ詳しく教えていただければと思います。

＞「問題なし」が直近で何回連続しているかをカウントする・・・

と書かれていますが、「直近」の意味合いがよく分かりません。この意味合いしだいで数式は全く異なるものとなりますので。

下記の組み合わせで動作確認しています。

・Windows11 と Microsoft365 のExcel

ご参考になれば幸いです。

＜追記＞

最後に書いている「尚、求めたい値が上記以外の場合・・・」について、言葉足らずの部分を追記しました。

Answer 4

横からすみません。

TEXTSPLIT の仕様ということですね？

テストしたら下記のようになりました。

(1) TEXTSPLITに渡す第一引数が配列の場合は各文字列毎に分割されてその最初の１つが表示されるようです。

(2) 数式のTEXTSPLIT関数にカーソルをあてて表示される内容は数式の結果ではなく配列の最初の文字列に適用した結果のようです。

数式の結果だと思い、なぜそのままセルに反映されないのか不思議に思っていました。

勘違いしやすいですね。

追記）

上記の例③についてはいままで、TEXTJOINとTEXTSPLITを組み合わせて下記のようにしていました。

=TEXTSPLIT(TEXTJOIN(";",FALSE,{"9○8○7○6○5○4○3○2○1","a○b○c"}),"○",";",FALSE,0,"")

これで問題なかったので、あまり深く考えていませんでした。

追記２）

TEXTSPLITで配列を指定した場合はスピルできないので各要素は下記のように「共通部分演算子: @」をつけた式になるようです。

例②の場合

=@TEXTSPLIT("○○○○○○○○","○")

例③の場合

=@TEXTSPLIT("9○8○7○6○5○4○3○2○1","○") <br><br>=@TEXTSPLIT("a○b○c","○")

それぞれ１つのセルになりますが数式のTEXTSPLITにカーソルを置くと分割された状態で表示されます。

Answer 5

この応答は自動的に翻訳されています。 その結果、文法上の誤りや奇妙な言い回しが生じる可能性があります。

なぜcountifを直接使用しないのですか?

=COUNTIF($B 2:$M 2,O$1)

これはバグのようです。あなたはあなたの式を段階的に評価して、式の結果をチェックすることができます。

こちらがこちらです

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

=COUNTA(テキスト分割(H6,"○"))-1