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

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

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

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

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

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

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

No.13 回答者： tom04 回答日時： 2015/01/24 10:01

No.9・10です。

＞列まではちゃんと出るのに右端の２列だけが出ない・・・

No.10の数式の場合は必要項目だけ表示させるために
各Sheetの3行目項目を参照させる数式でしたので、K・L列の項目名が「顧客一覧」Sheetの項目と違っている可能性があります。
結局「顧客一覧」SheetのA～L列すべてのデータを表示したいのであれば、No.9の数式で大丈夫みたいですね。
A4セルの数式を
=IFERROR(INDEX(顧客一覧!A$1:A$1000,SMALL(IF(顧客一覧!$F$1:$F$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1))),"")
としてみてください。

そしてもう一つの問題・・・
質問内容を拝見すると「顧客一覧」Sheetのどこかのセルが別ファイルを参照しているのではないでしょうか？
いちいち確認ができない場合は一つの方法として、
（1）「顧客一覧」Sheetすべてを範囲指定
（列番号「A」の左側、行番号「1」の上側の四角をクリック　→　これでSheet全体が選択されます。
（2）選択されているSheet上で右クリック　→　コピー　
（3）そのままの状態でもう一度右クリック　→　「形式を選択して貼り付け」　→　「値」を選択しOK

これで万一数式によって別ファイルを参照しているセルがあっても、実データそのものになりますので、
「このブックには更新できないリンクが１つ以上含まれています。」等の警告はおそらく表示されないと思います。

※　考えられる原因としてはこのくらいなのですが・・・m(_ _)m

No.12 回答者： MackyNo1 回答日時： 2015/01/24 01:27

＞このブックには、ほかのデータソースへのリンクが含まれています。

」
というような警告が出るようになってしまいました。
実際にはほかのデータソースというか、このファイル自体以外へのリンクは入っていないのですが。

少なくとも何か操作上の勘違いをされているようです。
たとえば別ブックを参照するような数式が入っていいないでしょうか？

単純に、新規シートのSheet1に元データをコピー貼り付けし、Sheet2に提示した数式を入力して正しい結果が変えることを確認してみてください。


＞ 警告画面もなぞですが、１％～１００％までゆっくりと計算するのを待つ部分が、もしかすると「エクセルには適さない」とご指摘頂いている原因なのかもしれません。

今回提示した数式は、基本的に再計算に時間がかかるようなことはありません。

少なくともそのブックには何か別のリンク数式がなどが含まれているようなので、新規ブックに「データ範囲」（シートのコピーではなく）をコピー貼り付けして、正常に計算できることを確認してください。

No.11 回答者： MackyNo1 回答日時： 2015/01/24 01:16

＞今、シート１のＣ列の上で「データ」「フィルタ」をし「いちご」を選んでみたのですが、抽出された結果を見ると、上の方はみごとに「いちご」の人だけがずらっと並んでいるのですが、スクロールして下の方を見ると、下の方はなぜか「メロン」「いちご」「すいか」の人の混成になってしまっていました。

データに空白行が含まれていないなら、問題なくオートフィルタできるはずです。
もし、下のほうにフィルタされていないデータがあるなら（リスト範囲内と認識されていない）、まったくデータの入力したいない行が含まれていますので、たとえばCtrl+↓キーで最下行のセルを見つけ、その下のセルに空白行がないか調べてください（最終的には空白行を削除）。

空白行をいれたまま作業がしたいなら、フィルタを行う前に、リスト上のセルにカーソルを置くのではなく、データ範囲を大きめに（下の空白行も含めて）選択してからオートフィルタの操作をしてください。

No.10 回答者： tom04 回答日時： 2015/01/24 00:04

No.9です。

＞・シート１には名前がつけてあり「顧客一覧」となっています。シート２の名前も「いちご」になっています。
というコトは前回の数式の「Sheet1!」の部分が変わってきますね。
ただ他のSheetのSheet名は一切使用しませんので、「顧客一覧」以外のSheet名はどんなSheet名でも構いません。
各SheetのA1セルに検索したい「品目」（F列のデータ）が入っていれば何らかのデータが表示されます。

操作方法は前回同様「作業グループ化」「配列数式」ですが、
画像の各Sheetの3行目項目は表示したい項目名だけを入力しておきます（「顧客一覧」にある項目名）

画像では作業グループ化して
A4セルに
=IFERROR(INDEX(顧客一覧!$A$1:$L$1000,SMALL(IF(顧客一覧!$F$1:$F$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1)),MATCH(A$3,顧客一覧!$3:$3,0)),"")
（今回も配列数式です）
という数式を入れ、列・行方向にフィルハンドルでコピーしています。

他の操作は前回同様です。

今度はどうでしょうか？m(_ _)m

この回答へのお礼

再度ありがとうございます。

今度は期待した値が得られました！
が、A列～J列までだけです。K列とL列は空白のままです。
フィルハンドルで右側にずーっとコピーして、J列まではちゃんと出るのに右端の２列だけが出ないのは不思議ですが。

それと、このファイルをいったん閉じてもう一度開くと、一番上に
「セキュリティの警告　リンクの自動更新が無効にされました　[コンテンツの有効化]」
というのが出てしまいます。
そこで「コンテンツの有効化」をクリックすると、#7の方のところに書いたのと同じ症状で、「このブックには更新できないリンクが１つ以上含まれています。」等の警告が出て来てしまいます。

また、元のデータの「いちご」を「スイカ」に直した時に、シート２（いちごシート）の内容が変わらず前のままのデータで抽出しています。
この部分は最新情報に書き換えていくので、山田さんが「いちご」から「スイカ」になったときに、シートを移動してもらいたいのですが…。


これらのことは、最初に式を入れた直後に、「パソコン全体の中からどのファイルか選べ」「更新元をどのシートにするか選べ」というようなのが出て来るのが関係しているのではないかと思うのですが、いかがなものでしょう。
ファイルとシートを選んだ瞬間に、式の「顧客一覧!」のところがが

[顧客一覧]顧客一覧!

になってしまうので。
（ファイル名は「顧客一覧」ではありません）


何度も申し訳ありません。お時間おありでしたらお暇な時にご教示頂ければ幸いです。

お礼日時：2015/01/24 01:01

No.9 回答者： tom04 回答日時： 2015/01/23 18:35

こんばんは！

一例です。
↓の画像で説明すると、
仮に「品目」が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

この回答への補足

すみません、お礼を投稿したあとに#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]顧客一覧!」を削ってみても同じです。「循環になっているのでだめ」というような警告が出たりもします。

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

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

補足日時：2015/01/24 00:27

この回答へのお礼

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

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

うまく動かなかった理由が、実際のデータと質問用に簡単に示したものの違いから来ていると困るので実際の状態を説明しますと、
・シート１のデータ一覧は４行目から（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の回答者様もありがとうございます。
今、できそうなものから順に試していき、お礼・補足させて頂こうと思います。

お礼日時：2015/01/23 23:09

No.8 回答者： passers-by 回答日時： 2015/01/23 17:59

先にマクロでなら可能という回答をさせて頂きました。

他の方法を思いついたので、参考になれば、と再度回答させて頂きます。

質問者さんがなさりたい作業はExcelのような表計算ソフトよりも
データベースの方が近い作業だと思います。

そこで、Microsoft Queryを使う方法はいかがでしょうか。(Excelのバージョンによっては出来ないかもしれませんが)

１) シート1のデータの部分を　[挿入] - [テーブル]　でテーブル化してしまいます。
2) シート2　[データ] - [その他のデータソース] - [Microsoft Query] で、データソース選択画面から
　　[データベース] - [Excel Files.*] を選びます。
3) データソースになるファイルを選択する画面では、このファイルを選択します。
4) クエリウィザードで、[オプション]ボタンを押して、[テーブルオプション]ウィンドウを表示させ、
　　テーブル、ビュー、システムテーブル、同義語に全部チェックを入れます。（この作業は正直必要かどうかよくわかりませんが…)
5) クエリウィザードの画面で、シートを選択できると思うので、データの入っているシート1を選択し、
　　表示させたい列を選択し、右側のリストへ追加します。
6) そのあと、データの抽出条件などを設定する画面が出てくると思いますので、条件を設定します。
　(例えば、C列に=「いちご」など)

Excel上で、データベースのようにクエリを作る機能です。
この機能を使えば今回やりたいことが実現できませんか？

No.7 回答者： MackyNo1 回答日時： 2015/01/23 16:08

関数を使用するなら以下のような手順が簡単です。

シート１のA列を選択して、右クリックから「挿入」して補助列を挿入し、A２セルに以下の式を入力します（この列は非表示にしておけばレイアウトは変わりません）。

=D2&COUNTIF($D$2:D2,D2)

たとえば、いちごを表示するセルのA1セルに「いちご」と表示したい項目を入力して（このシートをコピーして使えば、同じシートですべての項目が表示できます）、以下の式を適当なセル（添付図ならA4セル）に入力して、下方向および右方向にオートフィルコピーします。

=IFERROR(VLOOKUP($A$1&ROW(A1),Sheet1!$A:$E,COLUMN(B:B),0),"")

この回答への補足

もう一つ、元のデータを「いちご」から「スイカ」に直した際に、それが抽出結果に反映されないというのがあります。
シート１の新しいA列の中身はすぐに変わるのですが、シート２の中身が変わらず、一端ファイルを閉じてまた開いても（前述の警告画面が出て）、抽出された内容が更新されません。

最新情報を反映させる方法はあるでしょうか。

補足日時：2015/01/24 01:04

この回答へのお礼

ありがとうございます。画像まで添付して頂き恐縮＆感謝です。

やってみたのですが、２番目の式をシート２に入力したときに、ポップアップ画面が出て
「パソコン全体の中から、どのデータを元にするのか選べ」
というような指示が出たので、その通りにこのファイル自体を選びました。
すると、期待した通りに値がに表示されました！

やった！と思ったのですが、困ったことに、ファイルをもう一度開くと
「このブックには、ほかのデータソースへのリンクが含まれています。」
というような警告が出るようになってしまいました。
実際にはほかのデータソースというか、このファイル自体以外へのリンクは入っていないのですが。

しかし、その警告に従って「更新する」をクリックすると、今度は
「このブックには更新できないリンクが１つ以上含まれています。」
という警告が出てしまいます。

しかし、その警告に従って「続行」をクリックすると、下の方に「再計算」という表示が出て１％～１００％までゆっくりと計算するのを待つという次第になってしまいました。


警告画面もなぞですが、１％～１００％までゆっくりと計算するのを待つ部分が、もしかすると「エクセルには適さない」とご指摘頂いている原因なのかもしれません。

お礼日時：2015/01/24 00:02

No.6 回答者： Deathscythe#1 回答日時： 2015/01/23 16:03

シート1は質問文の記載のみです。

回答の中身はシート2の「いちご」としていますが
3.4も関数自体は同じで「いちご」とかかれた果物名を変更するだけです。

なお、完全一致で値を見ますので、
果物名はドロップダウンリスト化してずれないようにしておく方がベターです。

No.5 回答者： y_mochiduki 回答日時： 2015/01/23 15:35

参考URLの方法の応用で出来ると思います。

ご確認ください。

参考URL：http://oshiete.goo.ne.jp/qa/3200952.html

No.4 回答者： MackyNo1 回答日時： 2015/01/23 15:34

関数でC列の値ごとのデータを別シートに表示することはできますが、配列数式を使用する必要があるため、表示データ数が多くなると（＝3つのシートに表示すると）再掲賛意時間がかかり、実用的ではありません。

このようなケースではフィルタとマクロの記録の機能を利用されるとよいと思います。

データベースシートのリスト上のセルを選択した状態で、「データ「フィルタ」で「いちご」をフィルタします。
このシートのデータ範囲を大きめに選択し（たとえばA1:D1000、この操作がデータ更新時に有効になります)、「コピー」、シート２のA1セルで「貼り付け」します。
同様に「メロン」で絞り込み、シート３のA1セルに貼り付け、すべてのデータを別シートに貼り付けたら、元のシートのフィルタモードを解除します。

上記の操作を、マクロの記録に登録して、このマクロをボタンなどに設定すれば、ボタンクリックで最新のデータがそれぞれのシートに上書きされます。

＃ご使用のExcelのバージョンが明記されていないので、一般論のような操作方法をていじしていますが、Officeソフトはバージョンによって使用できる機能や操作方法ことなりますので、質問の際には化案らずバージョンを明記するようにしましょう。

この回答へのお礼

ありがとうございます。
まずフィルタで抽出して、その作業をマクロで録音というか録画のようなことをして、それをボタンに入れ込んでおくのですね。

今、シート１のＣ列の上で「データ」「フィルタ」をし「いちご」を選んでみたのですが、抽出された結果を見ると、上の方はみごとに「いちご」の人だけがずらっと並んでいるのですが、スクロールして下の方を見ると、下の方はなぜか「メロン」「いちご」「すいか」の人の混成になってしまっていました。
下の方が「メロン」「すいか」だけでなく「いちご」も混じっているのが不思議です…。

まずフィルタで「いちご」の人だけを完全に抽出することに成功しないと、それをマクロに記録→ボタンに落とし込む、という作業にいけませんよね。
バージョンのせい？でもないでしょうし…。


エクセルのバージョン等書き落しまして、大変失礼いたしました。
エクセル2010、OSはWin7です。

お礼日時：2015/01/23 16:00