エクセル 配列数式 #DIV/0!があると反映されない

質問

話がややこしいかと思うのですが、宜しくお願いします。

以前に、「楽天みんなで解決」のビジネス＆キャリアで質問させていただいたことに大して再び質問させていただきたいのです・・。
どうかお力を・・・！！

以前はこんな質問を致しました。

ーーーーーーーーーーーー
エクセルについての質問です。
　　A　B　　C
1　○　佐藤　■
2　×　鈴木
3　○　後藤
4　△　近藤
5　△　小林
6　×　大山
7　△　大林
8　×　小山
9　○　松田

のようなデータがあります。
この中で、■（つまり、C1セル）に、「○」の行にある、B列の文字を返したいのですが、■（つまり、C1セル）に
=VLOOKUP("○",$A$1:$B$9,2)
と入力し、C10セルまでコピーしても、うまく反映されません。

私がほしい結果としては、

○　　△　　×
佐藤　近藤　鈴木
後藤　小林　大山
松田　大林　小山
という具合に抽出したいのです。
ーーーーーーーーーーー

この質問にたいする回答といたしまして、
＝＝＝＝＝＝＝＝＝＝
一例です。
データをSheet1、Sheet2の1行目に記号があるとします。
Sheet2のA2に次の数式を入力して、縦横にコピーして下さい。
尚、配列数式の為、入力完了時にshift＋ctrl＋enterキーを同時押下して下さい。
=IF(COUNTIF(Sheet1!$A:$A,A$1)>ROW(A1)-1,INDEX(Sheet1!$B:$B,SMALL(IF(Sheet1!$A$1:$A$100=A$1,ROW(Sheet1!$A$1:$A$100),99999),ROW(A1))),"")
＝＝＝＝＝＝＝＝＝＝
という回答を頂きました。

一度はこれで解決したのですが、今回また問題が発生してしまったのです。
「○」や「△」の記号や「定価」といった文字ではうまくいくのですが、【#DIV/0!】というエラー値が一個でも入ると、とたんにうまく反映されなくなるのです。

例：前述のデータ表に沿うとします。以下のようにA5セルにエラーが入ると、とたんに全部#DIV/0!となってしまいます。
　　A　　　B　　　
1　○　　　佐藤　
2　×　　　鈴木
3　○　　　後藤
4　△　　　近藤
5　#DIV/0! 小林
6　×　　　大山
7　△　　　大林
8　×　　　小山
9　○　　　松田

＜抽出後＞
○　　　　△　　　　×
#DIV/0!　#DIV/0!　#DIV/0!
#DIV/0!　#DIV/0!　#DIV/0!
#DIV/0!　#DIV/0!　#DIV/0!

大変困っております・・・。
自分で解決しようものにも、数式の意味が解読できませんでした。。配列数式とはなんぞや、括弧がたくさんあって意味がよくわからず・・。
解決方法をどなたかご教授を！！
ついでに、なぜこのような現象がおきてしまうのか、できたら教えていただきたいです・・。

回答No.3

mu2011 · ベストアンサー

前回に数式を提供した者です。
>自分で解決しようものにも、数式の意味が解読できませんでした。
⇒関数エラー）が発生している状態では正常に処理ができないのはこの数式に限りません。
　一般的にエラーの元を断つ対応が肝要かと思いますが如何でしょうか。
一例です。
=IF(ISERROR(数式),"",数式）

お礼

すみません。補足に投稿してしまいました。

回答くださいまして、ありがとうございました！

補足

再び、ありがとうございます！！
エラー値に対する関数の存在を知りませんでした！
なるほど。そのようにすればいいのですね！助かりました。。！！

回答No.4

imogasi

配列数式では、内部処理ロジックとして、各行につき１行ずつ指定した処理を、行数分処理して（ｘ）、行数分を足し算するものが多いが、Xの段階で１つでもエラーが起こるデータの有様だと、配列数式全体が使い物にならない（エラーが返る）
こういうことに出くわしているのではないのですか。
このケースではエラー値が対象になっているようだが、これも配列数式に限らず、普通の関数でも個別にチェックすべき問題と思う。
関数の結果でエラー値が出るなら、出ないように式を組み変える（
０で割り算なら、除数が０かどうか聞いて、０の場合は一律空白にするとか）必要があるでしょう。

お礼

配列数式について、というかそもそもこの式についての理解が浅すぎて、原因が特定できず、
＞配列数式では、内部処理ロジックとして、各行につき１行ずつ指定した処理を、行数分処理して（ｘ）、行数分を足し算するものが多いが、Xの段階で１つでもエラーが起こるデータの有様だと、配列数式全体が使い物にならない（エラーが返る）
ということが分からないでいました。
なるほど。そういうことなんですね。

私の感覚では、「なぜデータに一個のエラー値があるだけで、全部だめになってしまうのだろう」と激しく疑問に思ったのです。

エラー値が出ないようにする、という方法をきちんととることにします。

ありがとうございました！

回答No.2

noname#176215

> ついでに、なぜこのような現象がおきてしまうのか、できたら教えて
> いただきたいです・・。
既に理解できていない状態では 何を説明しても半端な理解しかできな
いでしょう。せっかく教えてもらった数式すら理解しないまま「解読で
きませんでした」で済ませてしまう人が それでエラーが起きる理由だ
け 別の人から教えてもらおうとうのは無理な相談です。

理解できなくてもいい人用の数式なら提示できます。

=INDEX(B:B,100-LARGE(INDEX(ISNUMBER(1/(A$1:A$100="○"))*100
-ROW($1:$100),0),ROW(A1)))&""
Enter確定で 下方向にオートフィルコピー

ただこんな式を利用するのは止めた方がいいです。
このような数式を作ってしまったら誰かにも引継ぎできなくなりますよ？
作業列を使って計算すれば こんな複雑怪奇な計算は必要ありません。

■C1セル
=IF(ISERROR(A1),"",IF(A1="","",A1&COUNTIF(A$1:A1,A1)))
下方向にオートフィルコピー

この列を使えば VLOOKUP関数だけで全部求りますね？

お礼

無理な相談、だったのでしょうか。
もし仮に、私の知識で他の人が助かるなら、私は他人を助けますけど…。
言葉が過ぎましたね。申し訳ございません。
ただ私は、「知りたい」と思ったことを質問したに過ぎなかったのですけれども。

とにもかくにも、数式の理解に難しいと感じる私のために、簡易な数式を教えてくださってありがとうございます。
今後ますます、自分の知識・理解を深めていこうと思います。

回答No.1

grumpy_the_dwarf

それが複雑怪奇な関数を駆使した数式だろうと、単純明快な四則演
算だろうと、エラー値を喰わせちゃえばエラーしか返りません。
エラー足す5なんて誰にも計算できないんだから。

というわけで、前回教えてもらったという難しい数式は全然関係な
くて、A列にエラー値が出てくることが一番問題です。割り算の分母
がゼロになってるから#DIV/0!って言われちゃうんで、分母がゼロに
なりそうなら割り算しないで×にするとか空白にするとか対策を考
えて下さい。

お礼

ご丁寧にありがとうございます。
「エラー値」の意味するところを考えていなかったと思います。反省ですね。(エラー値＝0、という感覚だったのかもしれません)

対策を考えます。ありがとうございました。