C列の要素によって、３枚のシートに振り分ける

質問

シート１に顧客の名簿がずらっと一覧で入っているとします。
例えばA列…名前、B列…住所、C列…買った物（いちご、メロン、すいかの３種類）、D列…備考

　　A列　　　　　　B列　　　　　C列　　　　D列
　山田太郎　　　山梨県　　　いちご　　　新規さん（５月）
　鈴木良子　　　北海道　　　メロン　　　　 -
　田中浩史　　　東京都　　　すいか　　　常連さん
　高橋幸恵　　　京都府　　　すいか　　　　-
　本田一郎　　　長崎県　　　バナナ　　 　-
　…

というような一覧が数百人分入っているとします。

これをC列の要素によって、シート２～４に自動的に振り分けたいのです。
例えば「シート２」は「いちごを買った人一覧」に、「シート３」は「メロンを買った人一覧」にしたい、
それが、C列に「いちご」などと入力した瞬間に自動的にシート２に反映されるようにしたいです。
もちろんA列やB列、D列の要素をともなったまま。
また隙間を空けずにいちごを買った人が全部で52人なら52行でぴたりと収まるようにしたいです。

そのためにはシート２（もしくは３～４）のC列に、何らかの関数を入れればいいでしょうか？
またどんな関数を入れたらいいでしょうか。

よろしくお願い申し上げます。

回答No.9

tom04

こんばんは！

一例です。
↓の画像で説明すると、
仮に「品目」が3つの場合
Sheet2をアクティブ　→　Shiftキーを押しながらSheet4のSheet見出しをクリック！
これでSheet2～Sheet4が「作業グループ化」されましたので

A4セルに
=IFERROR(INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$C$1:$C$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1))),"")
配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　A4セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
A4セルを列・行方向にフィルハンドルでコピー！　→　Sheet見出し上で右クリック　→　作業グループ解除

最後に各SheetのA1セルに表示したい「品名」を入力します。
これで画像のような感じになります。m(_ _)m

お礼

ありがとうございます。
すごいです！画像まで作って頂き恐縮です。

が、書いてある通りにやってみましたがうまく動きません。

うまく動かなかった理由が、実際のデータと質問用に簡単に示したものの違いから来ていると困るので実際の状態を説明しますと、
・シート１のデータ一覧は４行目から（A4から）始まります。
・シート１には名前がつけてあり「顧客一覧」となっています。シート２の名前も「いちご」になっています。
・項目はA～Lの12項目あり、キーとして使うのはC列でなくF列です。
・A列には住所ではなく「1-1」「1-8」「1-14」「2-3」「2-10」「2-15」といったような「番号」が入っています。
　（ただし、A列も書式設定は「文字列」になっています。）

なので書いて頂いた式の「A$1」を「A$4」に、「C」を「F」に直し

=IFERROR(INDEX(Sheet1!A$4:A$1000,SMALL(IF(Sheet1!$F$1:$F$1000=$A$1,ROW($A$4:$A$1000)),ROW(A4))),"")

といったように書き直し、シート２のA3セル（実際にはA4セルからではなくA3セルから使うので）に入れて、ctrl ＋ shift + enterを押してみました。
するとこの式がそのまま表示されてしまったので、「セルの書式設定」を見ると「文字列」になっていたので「数値」を選びました。

その瞬間、「値の更新」というポップアップ画面が出てきてしまいました。
パソコン全体の中からデータの参照元にするファイルを選べという指示のようだったので、このファイル自体を選びました。
すると、「どのシートを元にするか選べ」というようなポップアップ画面が出たので、また「顧客一覧」シートを選びました。

すると、「循環関数になっているからだめ」というようなエラーが出てしまいました。

何度か条件を変えてやってみると（「A$1」をあえて「A$4」に直さないでそのまま使う、「いちご」シートを使わず新しい「Sheet6」を使うetc）、
「すべてのセルが空白になってしまう」「すべてのセルが『0』という値になってしまう」、「式がそのまま表示されてしまい計算しない（セルの書式設定を数値に直してもだめ）」などのエラーが出ました。

どうにもうまく行きません。いただいた式の書き直し方が間違っているのでしょうか？

-----------------------------
#1～#8の回答者様もありがとうございます。
今、できそうなものから順に試していき、お礼・補足させて頂こうと思います。

補足

すみません、お礼を投稿したあとに#7さまの方法を試してみて、A1に「すいか」と入力する意味が分かりました。
シート２の左上に入れた言葉を参照して、同じものを表示させているんですね。

そうとわかったのでもう一度#9さまの方法に帰り、CをFに直すだけで入力してみたところ、
先ほどと同じようにパソコン全体の中からデータの参照元にするファイルを選べという指示と、
どのシートを更新元にするか選べというような指示が出たので、このファイルのシート１（顧客一覧）を選んだのですが、その瞬間に式が

=IFERROR(INDEX([Sheet1]顧客一覧!A$1:A$1000,SMALL(IF([Sheet1]顧客一覧!$F$1:$F$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1))),"")

になってしまいました。
そして期待した値は得られませんでした。

そこで１つ目の「[Sheet1]顧客一覧!」を削って見たところ、すべてのセルが空白になってしまいだめでした。
２つ目の「[Sheet1]顧客一覧!」を削ってみても同じです。「循環になっているのでだめ」というような警告が出たりもします。

シートを選ぶときに「いちご」のシートを選んでも同じです。

式の直し方が間違っているのでしょうか。