EXCEL関数でフィルタの様にデータ抽出したい

質問

発注品一覧表から社別にデータを抽出した発注シートを関数で作りたいです。
オートフィルタは諸事情あって使用しません。

過去の質問のこちらが、かなり近い回答なのだと思いますが
不勉強で自分用に修正ができませんでした。
http://okwave.jp/qa/q3157199.html

一覧表はこのようになっています。
日付　社名　品目　値段
4/1　　C社　鉛筆　５０円　
4/1　　A社　定規　１５０円
4/2　　C社　ペン　１００円
4/2　　B社　鉛筆　１００円

社別の別シートに日付・品目・値段を一覧から関数で抽出できるでしょうか？
どうぞよろしくお願いします。

回答No.2

hallo-2007 · ベストアンサー

拝見いたしましたが参考にされたサイトですが
データが社名順に並んでいますよね。
今回の質問ですが、データが順に並んでいませんので全く違った発想が必要になります。
簡単な方法では、作業列を一列入れる方法がわかりやすいです。
データのシートに作業列も作れないのであれば　配列関数を使うことになりますが
データの量が多くてその式を縦横にコピーして使うですからパソコンの負担も重くなります。

式の意味を理解が難しいかと思いますが
INDEX関数で　範囲の指定の部分を条件で抽出した内容が入るように工夫した式がよく見受けられます
ので一応紹介しておきます
仮に　シート名　データ
日付　社名　品目　値段
4/1　　C社　鉛筆　５０円　
4/1　　A社　定規　１５０円
4/2　　C社　ペン　１００円
4/2　　B社　鉛筆　１００円

別のシート
　　　社名　品目
　　　C社

日付　社名　品目　値段

とB2セルに抽出したい社名が入っているとして
A4セルに
=INDEX(データ!A:A,SMALL(INDEX((データ!$B$1:$B$100<>$B$2)*1000+ROW(A$1:A$100),),ROW(A1)))&""
と入れて　右へコピー、下へコピーしてみてください。
INDEX((データ!$B$1:$B$100<>$B$2)*1000+ROW(A$1:A$100),)
の部分が理解しにくいと思います。
社名の範囲でC社でなかったら1000倍したとてつもない大きな数字を加える
C社でればそのまま、その行番号　といった架空の列を作成します。
その架空の列の小さい数値の順に　最初のINDEX関数で取り出します
といった感じです。（なかなか文書で説明するのも難しくてすみません）

別案ですが
フィルターオプションの設定（オートフィルターではありません）
をしたほうがシンプルでデータ量が増えた時も勝手に対応してくれます。

お礼

丁寧な説明と回答ありがとうございます。
参考サイトの質問と今回の質問との相違は社順など関係していたんですね…。
目的のための関数の組み合わせがわからなくて式の意味がまだ理解できていないんです…。
まったくわからなかった式も、今回説明いただいた内容でなんとなくですが理解できました！

また、「フィルターオプションの設定」も良いですね！（いつもオートフィルタしか使わなかったので知りませんでした…）
一緒にデータを使う知り合いもあまりエクセルに詳しくないので、相談してどちらが使いやすいか相談の上進めていくことにします。
本当にありがとうございました。

回答No.5

mu2011

ご回答者様には失礼とは思いますが関数はマニアックなもので、非常に難解です。
前回のご回答で解釈できない事ですので次の方法は如何でしょうか。
（１）発注シートのA1に社名と入力、A2に社名を入力
（２）データ→フィルタ→フィルタオプションの設定
（３）「指定した範囲」を選択、リスト範囲欄うぃお右クリック→対象シートクリック→対象列範囲を選択、検索条件範囲欄をクリック→A1:A2を選択→抽出範囲欄を選択、表示開始セルを選択→ＯＫ
（４）操作を簡略したい場合、マクロ記録→（２）（３）操作→マクロ記録終了すればショートカット等のワンタッチ操作で可能です。

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter3.htm

お礼

丁寧なご回答ありがとうございます。
いえいえ、本当に仰るとおり、関数は簡単なものが使えるからといって目的どおりに使うには理解と発想が大きく必要になるんだと今回つくづく感じました。
ほかのご回答にもありましたが、今回ご回答いただいた「フィルタオプションの設定」はとてもいい方法ですね！
マクロの記録の仕方までありがとうございます。マクロを使うとより作業が楽になりますね！！
一緒にデータを使う相手も初心者なので、関数にこだわらずフィルタオプションの設定を勧めてみようと思います。
本当にありがとうございました。

回答No.4

kagakusuki

今仮に、元データの表の中で、「日付」と入力されているセルが、Sheet1のA1セルであり、
別シートのB2セルに、社名を入力するものとします。

　まず、別シートの
A1セルに　　社名
A3セルに　　日付
B3セルに　　品目
C3セルに　　値段
と入力して下さい。
　次に、別シートのA4セルに次の数式を入力して下さい。

=IF(OR(COUNTIF(Sheet1!$1:$1,A$3)=0,ROWS($4:4)>COUNTIF(Sheet1!$B:$B,$B$2)),"",INDEX(OFFSET(Sheet1!$A:$A,,MATCH(A$3,Sheet1!$1:$1,0)-1),SUMPRODUCT(ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$A:$A)))*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$A:$A))=$B$2)*(COUNTIF(Sheet1!$B$1:INDEX(Sheet1!$B:$B,ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$A:$A)))),$B$2)=ROWS($4:4)))))

　次に、別シートのA4セルをコピーして、別シートのB4セルとC4セルに貼り付けて下さい。
　次に、別シートのA4セルの書式設定を[日付]として下さい。
　次に、別シートのA4～C4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　後は、別シートのB2セルに

C社

等の社名を入力すれば、抽出結果が自動的に表示されます。

お礼

簡潔でわかりやすいご回答ありがとうございます。
ご説明いただいたとおり別シートに入力していったところ、すぐに目的の結果が出ました！
あとは教えていただいた数式が理解できるように、これから勉強していきます。
本当にありがとうございました。

回答No.3

KURUMITO

シート１にお示しのデータがあるとしてら作業列を作って対応するのが式も単純で分かり易く計算にも負担がかかりません。
E２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",B2&COUNTIF(B$2:B2,B2))

そこで別のシート例えばC社についてのデータを表示したい場合には別のシートのA１セルにはC社と入力します。
２行目には項目を表示させるとしてA２セルには日付、B２セルには品名、C２セルには値段と入力します。

A３セルには次の式を入力してC３セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF(Sheet1!$E:$E,$A$1&ROW(A1))=0,"",INDEX(Sheet1!$A:$D,MATCH($A$1&ROW(A1),Sheet1!$E:$E,0),IF(COLUMN(A1)=1,1,IF(COLUMN(A1)=2,3,4))))

その後にA列のセルの表示形式を日付にします。
これでA１セルに入力した社名のデータのみが表示されることになります。他社の場合でも入力の式は同じで、A１セルの社名を変えることで良いでしょう。

お礼

簡潔でわかりやすい回答ありがとうございました。
シート１で作業列を作って、ご回答の数式を当てはめたところすぐに思い通りの結果が反映されました。
データを共有する相手からの注文通りの結果にできましたので、これで相手も満足すると思います。
本当にありがとうございました。

回答No.1

imogasi

質問に載っている、質問に回答している者です。
imogasi方式でやってみます。
データ
Ｓｈｅｅｔ１のＡ１：Ｄ５に
日付 社名 品目 値段 C社　＜－Ｆ列です＝作業列
4月1日 C社 鉛筆 ５０円 1
4月1日 A社 定規 １５０円
4月2日 C社 ペン １００円 2
4月2日 B社 鉛筆 １００円
Ｆ１に選択する社名を要れます。
Ｆ２の式は
=IF(B2=$F$1,MAX($F$1:F1)+1,"")
下方向に式を複写します。
Ｆ列には上行からＣ者の行の連番が入ります。
Ｓｈｅｅｔ２に行って、?1には　=Sheet1!F1
Ａ２セルには
=INDEX(Sheet1!$A$1:D10,MATCH(ROW()-1,Sheet1!$F$1:$F10,0),COLUMN())
右方向にＤ列まで式を複写。
その後Ａ２：Ｄ２の式を下方向に式を複写。
D10やＦ１０の１０は実情に合わせて増やしてください
結果
Ｓｈｅｅｔ２
C社
2011/4/1 C社 鉛筆 ５０円
2011/4/2 C社 ペン １００円
Ａ列は表示形式を日付に設定すること。
本件で３件目以降に式を複写すると＃Ｎ／Ａが出ますが、
=IF(ROW()-1>MAX(Sheet1!$F$1:$F$10),"",INDEX(Sheet1!$A$1:D10,MATCH(ROW()-1,Sheet1!$F$1:$F10,0),COLUMN()))
とすると見えなくなります。
ーー
これはＳｈｈｅｔ１で作業列Ｆ列を使ってます（欠点）
ーー
Ｇｏｏｇｌｅで「imogasi方式」で照会すれば、沢山の関数での抜き出し問題が出てきます。
その中に一発の式で、該当のＣ社の１項目セルが出る式が載ってます。この式が理解できれば良いですが、そうでなければ上記方法が難易度で次になると思う。
＝＝
いつも言っているが、関数は抜き出し問題は苦手（不適）だと思う。エクセル（関数）はもともと計算用です。
操作のフィルタなどで考えることをすすめます（特に初心者には）。
ーー
週に3度程度は抜き出し問題がこのコーナーに出て、うんざりしてますが。
上記imogasi方式で出る質問の数個を読めば、解法のタイプは出つくしていると思う。

お礼

丁寧なご回答ありがとうございます。
自分なりに検索をかけてみたのですが、まだまだ甘かったようですね。
フィルタを使うなど未熟なりにできる範囲でやっていけるよう考えます。
教えていただいたこと、きちんと理解できるように勉強します。
ありがとうございました。