検索型の数式が上手くできません。

質問

1　　　　2

A　　　　a商事　　100
B　　　　b商事　　200
C　　　　c商事　　300
D　　　　a商事　　500
E　　　 　a商事　　600　

仕事で上記のようなデータをシートIに作成しております。
このデータを別シートへ下記のように抜き出したいのです。

　　　　　　　1　　　2

A　　　　a商事　　100
B　　　　a商事　　500
C　　　　a商事　　600
D 　 　
E

VLOOKUPやLOOKUPでは、上手く出来ません。（一番上の　a商事 100　という値しか返さず、縦にコピーをしても同じ値が返されるだけでした。）
良い方法を教えてください。 　

ちなみに、シートIに作成しているデータがとても多いので、同じ検索値をA行へズラッとコピーしてその横へLOOKUPを使って値を返したのでは、とても手間がかかります。
定期的に集計をとりたいデータですので、シートIのデータが、即他のシートへ拾い出し＆集計できる数式が便利なのですが、どうすればいいかわかりません。
とても困っていますので、どうか宜しくお願いします。

回答No.4

shiotan99 · ベストアンサー

こんにちは～

> シートIのデータが、即他のシートへ拾い出し＆集計できる数式が便利

とのことですので、「オートフィルタ」や「並べ替え」の方法ではなく、関数を使った方法をご紹介します。

ちょっと確認ですが、質問の例では行番号（1、2、3）と列記号（A、B、C）が逆になっているようですが、A列が「会社名」、B列が「数値データ」でよろしいですか？

【仮定】
・元データは Sheet1で、A列が「会社名」、B列が「数値データ」
・抽出シートは Sheet2
・Sheet2 の D1セルに、抽出する「会社名」を入力
・Sheet1 の F列を作業列として使用

作業列を使います。
作業列はどの列を使ってもかまいませんが、ここでは仮に Sheet1 の F列を作業列とします。
作業列を使わない方法（配列数式）もありますが、データが多いとのことですのでお勧めできません。

Sheet1 の F1に（作業列）

=IF(AND(LEN(Sheet2!$D$1),A1=Sheet2!$D$1),ROW(),"")

と入れ、下にフィルコピー（予想されるA列の最大行までコピーしてください。A1000がMAXだと思えば、F1000あたりまでコピー。ちょっと多いかなと思うぐらいまでコピーしておいてください）

Sheet2 の A1に

=IF(COUNT(Sheet1!$F:$F)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$F:$F,ROW(A1))))

と入れ、そのまま右の B1にフィルコピー。
そのまま（A1 と B1が選択された状態で）下にフィルコピー。
最もデータの多い会社で、件数のMAXが300件ぐらいだと思えば、A300、B300あたりまでコピー。上に同じく、ちょっと多いかなと思うぐらいまでコピーです。

抽出を2行目以降からにする場合でも、数式には手を加えず、そのまま上の数式を使ってください。
ただし、作業列をF列以外にする場合は、上の数式の $F:$F の部分（2ヶ所）をその列記号に変更してください。必ず絶対参照にしてください。

以上です。
Sheet2 の D1に抽出したい会社名を入力しておけば、Sheet1 でその会社のデータを新規入力（修正・削除）するだけで、Sheet2にデータが反映されます。

Sheet1 のデータ数が予想を上回ったとしても数式を修正する必要は一切ありません。
作業列および Sheet2のA・B列の数式を必要分、下に追加でフィルコピーするだけでOKです。
作業列が邪魔なら非表示にしてください。

お礼

お礼が遅くなりすいませんでした。
ご回答参考にさせていただきます。
ありがとうございました。

とても参考になりました。今から早速やってみようと思います。本当にありがとうございました。

ちなみに、行番号と列番号は逆でした。すいません。

回答No.3

Wendy02

こんばんは。

配列確定をせずに、関数を利用する方法を考えてみました。

Sheet2 の　A1 ～　下にフィルダウン・コピー

=IF(COUNTIF(Sheet1!$A$1:$A$5,$D$1)<ROW(A1),"",INDEX(Sheet1!$A$1:$B$5,SMALL(INDEX((Sheet1!$A$1:$A$5=$D$1)*ROW($A$1:$A$5),,),COUNTA(Sheet1!$A$1:$A$5)-COUNTIF(Sheet1!$A$1:$A$5,$D$1)+ROW(A1)),1))

Sheet2 の　B2 ～　下にフィルダウン・コピー

=IF(COUNTIF(Sheet1!$A$1:$A$5,$D$1)<ROW(A1),"",INDEX(Sheet1!$A$1:$B$5,SMALL(INDEX((Sheet1!$A$1:$A$5=$D$1)*ROW($A$1:$A$5),,),COUNTA(Sheet1!$A$1:$A$5)-COUNTIF(Sheet1!$A$1:$A$5,$D$1)+ROW(A1)),2))

注:フィルダウン・コピーは十分な数を行ってください。エラー処理されていますので、""　なのか、式がないのか見分けがつきません。

Sheet2 の　D1 に検索値
a商事

範囲を変える場合は、編集置換で行いましょう。
そうすれば、簡単に変更できます。

置換方法 編集－置換　全て置換
例:

$A$5 → $A$10

$B$5 → $B$10

お礼

お礼が遅くなりすいませんでした。
ご回答参考にさせていただきます。
ありがとうございました。

せっかくご回答をいただきましたが、今回のデータは、たびたびテキスト形式で抜き出したものをエクセル上で加工するため、あまり複雑な関数は使用したくありません。

こちらの質問内容が不足しておりました事をお詫びします。

回答No.2

waka_wakana

質問者さまの目的に適うか分かりませんが、
データシートのほうで、オートフィルタ機能を使うのが便利かと思います。

　1.　データの先頭に見出し行(「会社」「個数」など)を作る
　2.　データ内にアクティブセルを置く
　3.　【データ】-【フィルタ】-【オートフィルタ】を選択

上記ステップで、見出しとした先頭行の項目名の右側に下向きの三角形のボタンが表示されます。
「会社名」のところにある下向きの三角形をクリックし、「a商事」を選択すれば、「a商事」のデータのみを抽出することができます。
(三角形ボタンをクリックし、「すべて」を選べば検索条件を解除できます)

抽出したデータをさらに加工したいのであれば、抽出後のデータを選択し、別シートにコピーすることも可能です。

お礼

お礼が遅くなりすいませんでした。
ご回答参考にさせていただきます。
ありがとうございました。
せっかくですが、かなりデータ量が多いので、フィルタをかけたくはありません。
すいません。
こちらの質問内容が不足しておりました。
ありがとうございました。

回答No.1

merlionXX

表を選択し、メニューの「データ」、「並べ替え」で会社名の列を基準とした並べ替えをすればご希望の結果になります。

さらにその状態で、「データ」、「集計」で、それぞれの合計も自動的に求められますよ。

お礼

お礼が遅くなりすいませんでした。
ご回答参考にさせていただきます。
ありがとうございました。