【エクセル】特定の文字以外を返す計算式

質問

申し訳ありません。どなたか助けてください。
【Sheet1】　
　　Ａ　　　　　Ｂ　　　　 Ｃ　 　　 Ｄ
１　実績月　部署等　　氏名　　金額
２　４月　　事業部　　佐藤　　100
３　４月　　実績なし　田中　　　0
４　４月　　実績なし　鈴木　　　0
５　５月　　事業部　　佐藤　　 40
６　５月　　営業部　　田中　　 50
７　５月　　販売部　　鈴木　　200
８　６月　　実績なし　佐藤　　　0
９　６月　　営業部　　田中　　 70
９　６月　　販売部　　鈴木　　 30

※【Sheet1】の情報を【Sheet2】のようにまとめたいのです。
※Ｃ、Ｄ、Ｅ列にはSUMPRODUCT関数を使用して集計しています。

【Sheet2】
　　Ａ　　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ
１　氏名　部署等　４月　５月　６月
２　鈴木　販売部　　0　　200　　30
３　佐藤　事業部　100　　40　　　0
４　田中　営業部　　0　　50　　70

※Ｂ列（B2）に以下のような計算式を埋め込みました。
　INDEX(Sheet1!$B:$B,MATCH($A2,Sheet1!$C:$C,0))

※すると以下のようになってしまい、部署名が正しく表示されません。
※Ｂ列が「実績なし」になっている場合はその値は返さず、
　「実績なし」以外の部署名を表示させたいのです。

【Sheet2】
　　Ａ　　　　　Ｂ　　　　 　Ｃ　 　　 Ｄ　　　　Ｅ
１　氏名　　部署等　　４月　　５月　　６月
２　鈴木　　実績なし　　0　　　200 　 30
３　佐藤　　事業部　　100 　 　40 　 0
４　田中　　実績なし　　　0 　 　50 　70

※どなたかお力をお貸しください。
※そもそも部署等欄に、「実績なし」という文字を表示させなければいいのかもしれませんが、訳あってこのようになっています。
※「実績なし」以外の部署名を表示させる関数を教えてください。

回答No.4

Wendy02 · ベストアンサー

こんにちは。

ものすごく根本的なことですが、実績表を以て、それをINDEX で部署を表示させることに問題があるのではありませんか。それは、ピボットテーブルでもうまくいくとは思いません。

実績なし　：　鈴木

左辺と右辺の対応がないものが混ぜてあるのですから、その考え方に、数学的な無理があります。

確かに、SUMPRODUCT 関数で

=INDEX(Sheet1!$B:$B,SUMPRODUCT(MAX((A2=Sheet1!$C$1:$C$100)*(Sheet1!$B$1:$B$100<>"実績なし")*ROW($A$1:$A$100))),1)

このような数式を埋め込めばでますが、実務上では、負担が大きすぎて、他にも同じような数式があるとすると、潜在的に問題が出てくるように思います。

もし、お仕事でしたら、ぜひ、氏名と部署のリスト原簿を作ることをお勧めします。

Sheet1 適当な場所で、

=IF($B1<>"実績なし",$C1,"")
=IF($B1<>"実績なし",$B1,"")

として、一覧を出して、後は、ツール--フィルタ--フィルタオプションの選択
で、重複するレコードは無視するで、一覧のリストが取れますから、それを使えばよいと思います。

また、
数式で、
氏名：
=INDEX($B$1:$C$100,SMALL(INDEX(($B$1:$B$100<>"実績なし")*ROW($C$1:$C$100),),ROW(A1)+COUNTIF($B$1:$B$100,"実績なし")),2)

部署等：
=INDEX($B$1:$C$100,SMALL(INDEX(($B$1:$B$100<>"実績なし")*ROW($C$1:$C$100),),ROW(B1)+COUNTIF($B$1:$B$100,"実績なし")),1)

で、重複は出ますが、これを、コピー・値貼り付けにして、それを並べ替えて重複を削除し、再び並べ替えて、重複を取り去ってもよいと思います。

お礼

>>お仕事でしたら、ぜひ、氏名と部署のリスト原簿を作ることをお勧めします。

アドバイスありがとうございます。
おっしゃるとおり、氏名・部署マスタを作成するのが王道だと思います。
ですが、いろいろな理由で、御提案いただいたSUMPRODUCT 関数を使用させていただけないでしょうか。
=INDEX(Sheet1!$B:$B,SUMPRODUCT(MAX((A2=Sheet1!$C$1:$C$100)*(Sheet1!$B$1:$B$100<>"実績なし")*ROW($A$1:$A$100))),1)

今後ともよろしくお願いします。
ありがとうございました。

回答No.5

hallo-2007

例えば　4月だけの時点ではどうでしょう。
【Sheet1】　
　　Ａ　　　　　Ｂ　　　　 Ｃ　 　　 Ｄ
１　実績月　部署等　　氏名　　金額
２　４月　　事業部　　佐藤　　100
３　４月　　実績なし　田中　　　0
４　４月　　実績なし　鈴木　　　0
田中さんがどの部署なのか情報がありません。このシートでは無理なのです。
別途
Sheet3に
部署名　担当者名
販売部　鈴木
事業部　田中
営業部　佐藤
といったマスターが必要です。
また　マスターがあればSheet1の部署名　或いは　氏名のどちらかが
関数で表示できますので入力作業も省けます。

別途疑問は　Sheet1の部署名に　なぜ実績なし　と表示する必要なのでしょうか？
条件付書式で色を変えて表現するなどの方がベターではないでしょうか？

お礼

御助言ありがとうございます。
おっしゃるとおり、マスタを作成するのが王道だと思います。
ですが、いろいろな理由で、#4様が御提案してくださったSUMPRODUCT 関数を使用させていただきたいと思います。

>別途疑問は　Sheet1の部署名に　なぜ実績なし　と表示する必要なのでしょうか？
>条件付書式で色を変えて表現するなどの方がベターではないでしょうか？

実は、Sheet1を作成するために次のような流れがあります。
Sheet3（データ貼り付けシート）→Sheet4（Sheet3を参照して集計）→Sheet1（Sheet3の見出し＆計以外をコピー値貼付））→Sheet2
※Sheet4→Sheet1はボタンを作成してマクロで実行します。

私も違うシートを作成する場合は、
"hallo-2007"様に御提案いただいた視点を忘れないようにいたします。
今後ともよろしくお願いします。
ありがとうございました。

回答No.3

noname#64582

＃２の者です。
この回答は破棄してください。
エラーが出ました。

失礼しました。

回答No.2

noname#64582

作業列使っちゃだめですかね。

■Sheet1 の　E2 に
=if(B2="実績なし","",B2)
と入れて、下まで引っ張る。

■Sheet2 の　B2　に
=vlookup(A2,Sheet1!$C$2:$F$65536,4,1)

それで下に引っ張る。

これでどうでしょう。
ご意向に沿っていれば幸いです。

回答No.1

noname#204879

“苦手な”関数を一切使わない[ピボットテーブルレポート]で実現できますよ。
結果だけを示しておくので、勉強してください。

合計/金額　　　　　実績月
氏名　　　 部署等　４月　　５月　６月
佐藤　　　 事業部　　 100　　40　　 0
田中　　　 営業部　　　 0　　50　　70
鈴木　　　 販売部　　　 0　 200　　30

補足

早速のご回答ありがとうございます。
[ピボットテーブル]については、私も最近勉強しました。
最近、エクセル2002からエクセル2007に変えたのですが、
随分使い勝手がよくなったと思っています。

折角[ピボットテーブルレポート]の御提案をいただいたのですが、
私よりもエクセルが苦手な者にファイルを使用してもらうことを想定しているため、できれば[ピボットテーブル]ではない選択肢を探しています。（ピボットテーブルの操作自体は自分も難しいと思わないのですが、できれば計算式での対応ができないでようか。）
いろいろ申し訳ありません。