エクセル2003 特定の文字が入ったら ０ にする

質問

いつも回答して頂きありがとうございます。

各シートの特定セルに書かれた情報をD3：G14へ表示させています。
式は以下の通り
=IF(ISERROR(INDEX(INDIRECT($B3&"!$B$4:$C$25"),MATCH(D$2,INDIRECT($B3&"!$B$4:$B$25"),0),2))=TRUE,"",INDEX(INDIRECT($B3&"!$B$4:$C$25"),MATCH(D$2,INDIRECT($B3&"!$B$4:$B$25"),0),2))

Ｉ3：L14にD列からG列各列の済の個数をカウントさせています。
式は以下の通り
=COUNTIF(OFFSET($D$3,,,ROW()-2,1),"済")

D列からG列の各列で"済"が表示されたら（I列からL列の各列で"１以上"が表示されたら）、それに対応する列の"済"以降の表示が消える様になっています。

質問の内容ですが、D列からG列の各列で"済"が表示してから以降において、"済"以外が表示された場合、それに対応するI列からL列の表示を0に戻したいのですが、良い案が思い浮かびません。宜しくお願い致します。

回答No.5

MackyNo1

>済 で表示してほしい箇所も全て空白になったという事です。

基本的に数式の最後に「&""」を付けた場合は、「済」のような文字列を参照する場合、そのままの文字列「済」が返り、空白セルの場合のみ「0」の代わりに空白文字列「""」が返る数式になります。

したがって、「済」と表示されるセルは空白表示になることはありませんので、数式や参照範囲などに誤りがないか調べてみてください。

回答No.4

MackyNo1

>来月分の内容を今月からやり始めた場合、検索文字列を復活させなければなりません。ですので、自分の考えでは、カウントを１から０にする事で検索文字列の復活が可能になるのかなと思い質問しました。

ということは、済の数をカウントするのではなく、済の入力されているセルを確認するだけでよいということですか？

＞「&""」の入力箇所って何処でしょうか？教えて頂けると嬉しいのですが・・・。最後に入力しましたが、全て空白になってしまいました（泣）

「すべて空白」とはどのような状況なのでしょうか？
お礼の中の補足で示されたようにINDEX関数の最後に&""を付けておけば、通常の文字列（済やそれ以外の文字）を返す場合は、そのデータそのものを、空白セルを返す場合は「0」ではなく空白文字列「""」を返します。

＞No.2で提示された0の時の空白の方法が分からなかったので、IF関数で空白にしました。その後、今回提示して頂いた関数をべたっと貼り付けた所、見事に0になっていました。ありがとうございました。

もし空白文字列ではなく、添付画像のような「0」が表示されているリストのまま集計するなら、以下のような関数になります。

=SUM((D4="済")*1,I3)*NOT((D4<>0)*(D4<>"済"))

＞余談ですが、これを貼り付けてから分かった事なんですが、検索文字列を消している事に起因する不具合が発生しちゃいました。

どのような不具合なのでしょうか？
上記の補足内容を含めて、まだご希望の操作が完全に理解できていないので、もしかするともっと簡便な関数や処理方法があるかも知れません。

より効率的な数式を含めて、新たな問題が発生した場合は、この質問はいったん解決処理して、問題点を整理し、添付画像も実際のデータ例で説明するなどして、回答者にわかりやすい形で質問しなおすほうが良いと思います（関連質問お場合はこの質問のリンクを付ける）。

補足

済 が一つでも入力されると、各シート(２日のシートに 済 を入力した場合、３日のシート）から検索文字列が空白に置き換わるので、MATCHで検索文字列を拾う事はありえません。で、不具合と言うのは、検索文字列が空白に置き換わるとMATCHで拾えなくなるという現象です。せっかくMackyNo1 さんに提示して頂いた式で 0 に出来たのですが、検索文字列を空白に置き換えている為、検索文字列の右隣のセルの情報を読めなくなったのです。ですので、済 が入力されると、空白に置き換えるのでは無く、検索文字列はそのまま残して、条件付き書式で塗り潰す等の方法でいこうかなと思っています。ちょっと暇が出来なくてまだ試していませんが、行き詰った時には、新たに質問しようかなと思っています。

＞「すべて空白」とはどのような状況なのでしょうか？
お礼の中の補足で示されたようにINDEX関数の最後に&""を付けておけば、通常の文字列（済やそれ以外の文字）を返す場合は、そのデータそのものを、空白セルを返す場合は「0」ではなく空白文字列「""」を返します。

済 で表示してほしい箇所も全て空白になったという事です。

回答No.3

MackyNo1

補足回答がありませんので、D列のセルには空白文字列と「済」、済以外の文字列が入力されているケースで、D列のその行までの「済」の数をカウントし、「済」以外の文字が出てきたら、カウントを0にして再度その下の行から済の数をカウントしなおすという条件なら、I4セルに以下の関数を入力し、右方向及び下方向にオートフィルコピーします。

=SUM((D4="済")*1,I3)*NOT((LEN(D4)>0)*(D4<>"済"))

お礼

No.2で提示された0の時の空白の方法が分からなかったので、IF関数で空白にしました。その後、今回提示して頂いた関数をべたっと貼り付けた所、見事に0になっていました。ありがとうございました。

余談ですが、これを貼り付けてから分かった事なんですが、検索文字列を消している事に起因する不具合が発生しちゃいました。自分が思っている事を実現するには、検索文字列を空白にするのは不味いみたいです。条件付き書式で対応してみます（塗り潰す等）。本当に助かりました。ありがとうございました。

回答No.2

MackyNo1

補足回答を見ましたが、まだブックの内容を把握できません。

＞=COUNTIF(OFFSET($D$3,,,ROW()-2,1),"済")
ですが、たぶん作った時に最初に行だけ絶対参照して、横へドラッグ・次に全てを絶対参照にして下へドラッグしたからでしょう。

普通なら「=COUNTIF(OFFSET(D$3,,,ROW()-2,1),"済")」を入力して右方向および下方向にオートフィルしたのではないでしょうか？

＞済はMATCHで検索した文字の右のセルに"入力されています。なので、D3：G14には、済以外も表示されます。

この部分は了解しています。

＞済が表示されて以降は、日付をふったシートから検索の語句が消えてしまうようにしています（１日のシートに済を入力したら、２日のシート以降から検索の語句が消えてしまう）。
こんな式を入力しています。
=IF(INDEX(一覧!$I$3:$L$14,MATCH(DAY($B$2)&"日",一覧!$B$3:$B$14,0)-1,MATCH(一覧!$I$2,一覧!$I$2:$L$2,0))>0,"",一覧!$I$2)

検索文字列に同じ値ではなく空白文字列「""」になっているということですね。

＞ここまで良いだろうと思っていましたが、途中で済以外の語句を入れる時も有るだろうと思い、その時はその日付からカウントを0に出来たらいいのになと思い今回の質問をしました。

最終的な目的は、最初に済が出てくる場所から、次に済以外の文字列が表示されるセルさえわかればよいということではなく、「済」の数をカウントしたいことが主目的ということでしょうか（それによって合理的な数式が変わります）。

さらに、「済」以外の文字の後に「済」が出てきた場合はどのように処理するのでしょうか？

また確認ですが、添付画像ではD～G列の「済」以外のセルは「0」が返っていますが、空白セルを参照しているということでしょうか(検索値はあるがそのセルの右が空白のパターン）。
その場合は数式の最後に「&""」を挿入して、空白文字列が変える数式にしたほうが処理しやすいと思います。

＃目的によって、最も合理的な数式が異なりますので、上記の補足が寄せられたら、それに対応した具体的な回答を提出したいと思います。

お礼

>最終的な目的は、最初に済が出てくる場所から、次に済以外の文字列が表示されるセルさえわかればよいということではなく、「済」の数をカウントしたいことが主目的ということでしょうか（それによって合理的な数式が変わります）。

済 が入力されると、日付の入ったシートから検索文字列が消えるようにしていると前回説明しましたが、来月分の内容を今月からやり始めた場合、検索文字列を復活させなければなりません。ですので、自分の考えでは、カウントを１から０にする事で検索文字列の復活が可能になるのかなと思い質問しました。

>また確認ですが、添付画像ではD～G列の「済」以外のセルは「0」が返っていますが、空白セルを参照しているということでしょうか(検索値はあるがそのセルの右が空白のパターン）。
その場合は数式の最後に「&""」を挿入して、空白文字列が変える数式にしたほうが処理しやすいと思います。

空白の場合もあります。
「&""」の入力箇所って何処でしょうか？教えて頂けると嬉しいのですが・・・。最後に入力しましたが、全て空白になってしまいました（泣）
=IF(ISERROR(INDEX(INDIRECT($B3&"!$B$4:$C$25"),MATCH(D$2,INDIRECT($B3&"!$B$4:$B$25"),0),2))=TRUE,"",INDEX(INDIRECT($B3&"!$B$4:$C$25"),MATCH(D$2,INDIRECT($B3&"!$B$4:$B$25"),0),2)&"")

回答No.1

MackyNo1

>各シートの特定セルに書かれた情報をD3：G14へ表示させています。
式は以下の通り
=IF(ISERROR(INDEX(INDIRECT($B3&"!$B$4:$C$25"),MATCH(D$2,INDIRECT($B3&"!$B$4:$B$25"),0),2))=TRUE,"",INDEX(INDIRECT($B3&"!$B$4:$C$25"),MATCH(D$2,INDIRECT($B3&"!$B$4:$B$25"),0),2))

上記の数式はおそらくVLOOKUP関数で対応できると思いますので、無駄が多い（＝TRUEなども不要）数式になっているような気がしますが、式の意味は理解することができます。
しかし、それ以下の説明との関連が不明です。

＞Ｉ3：L14にD列からG列各列の済の個数をカウントさせています。
式は以下の通り
=COUNTIF(OFFSET($D$3,,,ROW()-2,1),"済")

最初の数式にはセルの値を参照しているように思うのですが、どこから「済」の値が出てくるのでしょうか？
また、上記の数式はD3セルを絶対参照しているので、D列だけの済の個数をカウントする数式になっていますが、F列にも同じ数式が入力されているのでしょうか？

＞D列からG列の各列で"済"が表示されたら（I列からL列の各列で"１以上"が表示されたら）、それに対応する列の"済"以降の表示が消える様になっています。

この説明の意味も分かりません。
条件付き書式でフォント色を白にしている、あるいは数式で「””」表示をしているということでしょうか？

＞質問の内容ですが、D列からG列の各列で"済"が表示してから以降において、"済"以外が表示された場合、それに対応するI列からL列の表示を0に戻したいのですが、

添付画像のデータでは「済」が１つしかありませんが、「"済"が表示してから以降において、"済"以外が表示された場合」とはどのようなことを意味するのでしょうか？

＃今回の質問は、内容をピンポイントに絞った適切な質問方法だと思いますが、最終目的を説明していただければ、おそらくもう少し簡単な関数に変更できたり、補助列などを使用しないでもご希望の操作ができると思います（わざわざ複雑なシート校正にしているような気がします）。
あわせて、現在の表示をこのように変更したいというような例の添付画像を付けて投稿されたほうが回答者が質問内容を理解しやすいと思います（タイトルと質問内容の関係もよくわかりません）。

お礼

シート名が日付（1日・２日・・・）の各シートから検索したい語句をMATCH（行が挿入によって追加される為）で検索して表示させています。検索される文字は"済"ではありません。

済はMATCHで検索した文字の右のセルに"入力されています。なので、D3：G14には、済以外も表示されます。

済が表示されて以降は、日付をふったシートから検索の語句が消えてしまうようにしています（１日のシートに済を入力したら、２日のシート以降から検索の語句が消えてしまう）。
こんな式を入力しています。
=IF(INDEX(一覧!$I$3:$L$14,MATCH(DAY($B$2)&"日",一覧!$B$3:$B$14,0)-1,MATCH(一覧!$I$2,一覧!$I$2:$L$2,0))>0,"",一覧!$I$2)

ここまで良いだろうと思っていましたが、途中で済以外の語句を入れる時も有るだろうと思い、その時はその日付からカウントを0に出来たらいいのになと思い今回の質問をしました。

自分なりに考えて説明文を書いたつもりですが、かなり文法が下手くそなので、理解させる事が出来たのか分かりませんが、宜しくお願い致します。

補足

=COUNTIF(OFFSET($D$3,,,ROW()-2,1),"済")
ですが、たぶん作った時に最初に行だけ絶対参照して、横へドラッグ・次に全てを絶対参照にして下へドラッグしたからでしょう。