こんな時に使えるexcelの関数ってありますか？

質問

excelの関数で、右のセルを検索値とすることが出来るものはあるんでしょうか？

もしくは、1行まるごと別シートへ自動的に入るように出来る関数ってないですかね？

たとえば、こんな時なんかに。

【シート１】（元データ）
　　　A　　　　 B　　 　C　　　 D
1　 1-1 　キッチン 　456　 皿A
2　 1-1 　キッチン 　123　 皿B
3　 2-1 　ガーデン 　258　 糸A
4 　1-1 　キッチン 　987 　皿C
5 　3-2 　文房具　 　369 　ペンA
6 　1-1 　キッチン 　741　 皿D
　　　：　　　 ：　　　　 ：　　　 ：

キッチンの行だけを別シートに自動的に入るようにする。
↓

【シート２】（別のシート）
　　　A　　　 B　　　　 C　　　 D
1　 1-1　 キッチン 　456　 皿A
2　 1-1　 キッチン 　123　 皿B
3　 1-1　 キッチン　 987　 皿C
4　 1-1　 キッチン 　741　 皿D
　　　： 　　　：　　　　　 ： 　　：

列とかズレてたらすみません；

自分だけではもうお手上げだったので、
どなたか宜しくお願いします。

回答No.8

hallo-2007

VBAとオートフィルターを使った方法ですが、最近ほかの方へ紹介して便利といわれましたので。
別シートのシート名のタブを右クリックして　コードの表示　をクリック
VBエディターが起動するので
Private Sub Worksheet_Activate()
Cells.Clear
Sheets("元データ").Rows("1:" & Sheets("元データ").Range("A1").End(xlDown).Row).Copy
Range("A1").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
をコピーして閉じてください。

元データのオートフィルターを設定して
キッチン　だけが表示されるようにします。
別シートにするとオートフィルターで抽出した結果が出ています。

回答No.7

web2525

ややこしい関数を利用するより
フィルタオプションで抽出する方が分かりやすいと思う

http://allabout.co.jp/gm/gc/297791/

元データのSheet1を頻繁に修正してリアルタイムでの反映が必要の場合なら計算式が必要になるけど
その場合はデータの入力形式や、処理法法を工夫して単純化する事の方が後々トラブルが少なくなると思う

回答No.6

imogasi

（１）関数は関数式を入れたところの、１セルだけの値を決めることしか出来ません（エクセル関数の大原理）
（２）しかしあるセルの関数式を連続して、複写することで、参照する番地を変化させる仕組みがあり、これが使えるときは
（使えるように式を工夫して）、望みの行（列）に値をセットできる。
そのため関数式では出来ないとはならない。
ーー
しかしGoogleで「imogasi方式」で照会すると数百の、この質問コーナーに出た、（しかし全部ではない）関数による抜き出し
方法の回答やその他が出る。
その考え方のタイプには、数種ある。
作業列を使うものと、そうでないもの、配列数式を使うものなどの分類ができる。
作業列など使わないほうが良いが、指揮が複雑になって質問するレベルの人には何を屋って居るか直ちには理解できないだろう。
エクセル関数では、条件による（本質問例では「キッチン」）抜き出し問題は、適してないと思う。
ーー
エクセルにはフィルタやフィルタオプションなどの操作がありから、それを使うべきと思う。
ーー
抜き出し先のシートは、全セル長い複雑な関数式で埋め尽くされるので（元データが変わると全セル再計算されるので）、数千行になると、処理速度も鈍ると思う。
ーー
毎週数回はこのタイプの問題を質問する質問者が出るが、その難しさを判ってない初心者が多い。
＞右のセルを検索値とすることが出来るものはあるんでしょうか
意味が不明。この質問の内容からすると、不適当。
＝＝＝
imogasi方式でやってみる
例データ　Sheet1のA-E列　　E列が作業列
1月1日 キッチン 456 皿A 1
1月1日 キッチン 123 皿B 2
2月1日 ガーデン 258 糸A
1月1日 キッチン 987 皿C 3
3月2日 文房具 369 ペンA
1月1日 キッチン 741 皿D 4
Sheet2のA1には「キッチン」（抜出する文字列）を入れておく。
E２の式は　=IF(B2=Sheet2!$A$1,MAX($E$1:E1)+1,"")
やっていることは、B列で「キッチン」の行に、上から連番を振っている。
ーー
Sheet2に行って、（A1はキッチン）
A2に　=INDEX(Sheet1!$A$1:$D$100,MATCH(ROW()-1,Sheet1!$E$1:$E$100,0),COLUMN())
と式を入れる。（とりあえず１００行までの例にしている）
右方向にD列まで式を複写。
A2:D2を下方向に式を複写。
結果　Sheet2
キッチン
2011/1/1 キッチン 456 皿A
2011/1/1 キッチン 123 皿B
2011/1/1 キッチン 987 皿C
2011/1/1 キッチン 741 皿D
#N/A #N/A #N/A #N/A
A,列は表示形式を「日付、C列は数値に設定すること。
A1セルの「キッチン」を「文房具」に変えてみてください。
ーー
＃N/Aを出さないようにするには上記の式にIF関数をかぶせて
=IF(ROW()-1>MAX(Sheet1!$E$1:$E$100),"",INDEX(Sheet1!$A$1:$D$100,MATCH(ROW()-1,Sheet1!$E$1:$E$100,0),COLUMN()))
として（Sheet2の行数が）Sheet1のE列の最高値（キッチンの該当行数のこと）を越えたら空白を返すようにする。
＞excelの関数ってありますか
１つの関数では、そんな関数はない。今後は、単純な課題以外は（ここに質問が出るレベルの問題では）、「関数の組み合わせ」でやるのだ、と考えること。

回答No.5

tom04

No.4です！
たびたびごめんなさい。
画像がうまくアップできないようなので、Sheetの配置だけ・・・

前回の数式はSheet1の2行目からデータがあり、（1行目からでも構いません）
Sheet2のA2セルにSheet1のB列データの検索したいものを入力した場合に表示される数式です。

数式を入れるセルはSheet2のどこのセルでも構いません。

何度も失礼しました。m(__)m

回答No.4

tom04

こんにちは！
すでに回答は出ていますが・・・
一例です。

一つ気になったのですがSheet1のA列は文字列表示になっている訳ですよね？
（通常　1-1　のような入力だと日付として判断されますので・・・）

データ量が多すぎる場合はオススメしませんが敢えて配列数式を使ってみました。

↓の画像でSheet2のA2セルに検索したい物を入力すると表示するようにしています。

Sheet2のA5セルに
=IF(OR($A$2="",COUNTIF(Sheet1!$B:$B,$A$2)<ROW(A1)),"",INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$B$1:$B$1000=$A$2,ROW($A$1:$A$1000)),ROW(A1))))

これは配列数式になってしまいますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これを列方向と行方向にオートフィルでコピーすれば、画像のような感じになります。

尚、余計なお世話かもしれませんが、質問に

＞右のセルを検索値とすることが・・・

とあるのですが、　1-1　を検索値にすればVLOOKUP関数が使用可能のような気がします。

参考になれば良いのですが・・・m(__)m

回答No.3

KURUMITO

作業列を使って作業を進めることがおすすめです。
例えばシート１のE1セルには次の式を入力して下方にオートフィルドラッグします。

=B1&COUNTIF(B$1:B1,B1)

これで品名ごとに上の行から番号のついたデータが表示されます。

次にシート２では例えばA１セルにキッチンのデータを抽出した表を表示させるのでしたらキッチンと入力します。
次に例えばA２セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

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

これでシート２のA１セルにキッチン以外の品名を入れたらそのデータが表示されますね。

回答No.2

kagakusuki

作業列と関数を併用する方法と、関数のみで行えるが、計算処理の負担が大きい（結果が表示されるまでに、少し時間がかかる）方法があります。
　今仮に、抽出するデータを指定するために、Sheet2のB1セルに、例えば

キッチン

と入力すると、Sheet2のB2以下や、Sheet2のA列、C列、D列の表示が自動的に行われるものとします。

【作業列と関数を併用する方法】
　まず、適当な列（例えばSheet3のA列）を作業列として使用する事を決めます。
　次に、Sheet3のA1セルに、次の数式を入力して下さい。

=IF(Sheet1!$B1="","",Sheet1!$B1&COUNTIF(Sheet1!$B$1:$B1,Sheet1!$B1))

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に(Sheet1の元データの表の行番号を上回るのに充分な行数となるまで)貼り付けて下さい。
　次に、Sheet2のB2セルに次の数式を入力して下さい。

=IF(ROWS($1:2)>COUNTIF(Sheet1!$B:$B,B$1),"",B$1)

　次に、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNTIF(Sheet1!$B:$B,$B$1),"",INDEX(Sheet1!A:A,MATCH($B$1&ROWS($1:1),Sheet3!$A:$A,0)))

　次に、Sheet2のA1セルをコピーして、Sheet2のA2セルとSheet2のC1～D2の範囲に貼り付けて下さい。
　次に、Sheet2のA2～D2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上で準備は完了で、後はSheet2のB1セルに、抽出の基準となる検索値を入力すると、抽出結果が表示されます。

【関数のみで行う方法】
　まず、Sheet2のB2セルに、【作業列と関数を併用する方法】で入力した数式と全く同じ、次の数式を入力して下さい。

=IF(ROWS($1:2)>COUNTIF(Sheet1!$B:$B,B$1),"",B$1)

　次に、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNTIF(Sheet1!$B:$B,$B$1),"",INDEX(Sheet1!A:A,SUMPRODUCT(ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH("゛",Sheet1!$B:$B,-1)))*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH("゛",Sheet1!$B:$B,-1))=$B$1)*(COUNTIF(OFFSET(Sheet1!$B$1,,,ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH("゛",Sheet1!$B:$B,-1)))-ROW(Sheet1!$B$1)+1),$B$1)=ROWS($1:1)))))

　次に、Sheet2のA1セルをコピーして、Sheet2のA2セルとSheet2のC1～D2の範囲に貼り付けて下さい。
　次に、Sheet2のA2～D2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上で準備は完了で、後はSheet2のB1セルに、抽出の基準となる検索値を入力すると、抽出結果が表示されます。

お礼

すごく丁寧に教えていただいて、ありがとうございます！

おかげで上手く作れました(^ｖ^)♪

回答No.1

yo5728

的外れな回答だったらごめんなさい。
関数を使わなくても、別シートのデータを入れたいセルに『＝』で元シートのデータのセル値を入れると引っ張れませんか？