別シートへ飛ばす2

質問

エクセルの関数またはマクロでどうしたらできるのでしょうか？

内容ですが、元データはsheet1にA4に整理番号があり、B4に識別番号があり、C4に名前があり、D4にフリガナがあり、E4に区分があります。
ここでE4セルの区分ですが、種類が（要支援1、要支援2、要介護1～5）あり、要支援1、要支援2であれば、その左のセルの情報A4B4C4D4がsheet2のA4～E4へ上から順番に埋まっていくように飛ばすにはどうしたらいいでしょうか？

たくさん羅列してあるデータから条件を満たしている人が別シートの上から順に埋めていきたいのです。

宜しくお願いします。

回答No.5

MackyNo1 · ベストアンサー

Sheet2のA4セルに以下の式を入力して下方向に適当数オートフィルしてください。

=INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$E$4:$E$1000<>"要支援1")*(Sheet1!$E$4:$E$1000<>"要支援2")*10000+ROW($E$4:$E$1000),),ROW(A1)))&""

B4セルには以下の式を入力して右方向に3つ、下方向に適当数オートフィルしてください。

=IF($A4="","",VLOOKUP($A4*1,Sheet1!$A:$D,COLUMN(B1)))

お礼

ありがとうございます。
使わせていただきます。

回答No.6

TAKA_R

フィルタオプションの設定が、いい機能だと思います。
希望の要素がすべて叶えられます。
ただ条件を書くスペースが要ります。（といっても縦並んで3セルだけですが）
それから必要になる度に、設定の画面を埋めるのがちょっと面倒です。
vbaでも簡単にかけるので、そしたらボタン一押しですね。

使うのが１度っきりなら、直で使ってみてください。
まず、どこかに「種類」（？、フィールド名）、「要支援１」（値）、「要支援２」（値）を３行並べて１列に書きます。
書き出したいシート（今回はシート２）を開き、A４セルを選択した状態で、メニュー＞データ＞フィルタ＞フィルタオプションの設定を選びます。
ダイアログが開くので、ラジオボタンは２番目を選び、リスト範囲はシート１のデータを選択して、検索条件範囲は条件を書いた３セルを選択、抽出範囲はA4が選択されている状態にする。

あとはOKを押せば、終り。

お礼

ありあとうございます。

フィルタでできると思いませんでした。
一度きりでは都合が悪くすみません。

回答No.4

tom04

こんにちは！
関数での方法は出ていますので、VBAでの一例です。

Sheet1のデータは4行目以降にあり、Sheet2も4行目以降に表示すれば良いわけですよね？
そういうことだとして・・・

質問では
＞その左のセルの情報A4B4C4D4がsheet2のA4～E4へ上から・・・
となっていますが、とりあえずSheet1のA列～E列全てを表示するようにしています。

Alt＋F11キー　→　画面左下の「This Workbook」をダブルクリック
VBE画面に↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, k As Long
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
k = ws2.Cells(Rows.Count, 1).End(xlUp).Row
If k > 3 Then
Range(ws2.Cells(4, 1), ws2.Cells(k, 5)).ClearContents
End If
k = 3
For i = 4 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If ws1.Cells(i, 5) = "要支援1" Or ws1.Cells(i, 5) = "要支援2" Then
k = k + 1
Range(ws1.Cells(i, 1), ws1.Cells(i, 5)).Copy Destination:=ws2.Cells(k, 1)
End If
Next i
End Sub 'この行まで

※　関数ではないので、すぐにSheet2に反映されません。
Sheet1のデータ変更があるたびにマクロを実行してください。

参考になりますかね？m(__)m

お礼

ありがとうございます。

まさに、自分が目指すべき、やりたい答えです。

ただ今回は関数が都合がいいようです。毎回実行よりも自動的に抽出してくれる関数を使用させていただきます。
ですが、最終的には関数はもちろん、マクロを使いこなしていきたいと思ってますので、ある意味この回答がベストアンサーです。

回答No.3

KURUMITO

マクロで対応するよりも関数で対応することが分かり易く簡単ですね。
例えばシート1のＡ列からＥ列までで、4行目から下方にデータが有るとして、Ｇ1セルから下方にシート2に抽出して表示したい区分における条件を入力することにします。例えばＧ1セルに要支援1、Ｇ2セルに要支援2と入力します。
その上でＦ4セルには次の式を入力して下方にドラッグコピーします。

=IF(A4="","",IF(COUNTIF(G:G,E4),MAX(F$3:F3)+1,""))

シート2に条件により抽出された人のデータを表示させることにして、Ａ4セルには次の式を入力して右横のＥ4セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$F:$F),"",INDEX(Sheet1!$A:$E,MATCH(ROW(A1),Sheet1!$F:$F,0),COLUMN(A1)))

これでお望みの表がシート2に表示されます。抽出の条件を変える時はシート1のＧ列のデータを変更すればよいでしょう。

お礼

ありがとうございます。
こうしたいと思うときに、これだけ色々な方法があるのはとても面白く感じます。

今回の数式とても参考になりました。
また宜しくお願いします。

回答No.2

k-josui

直接の回答にはなりませんが・・・
それはデーターベースの範疇になりますので、Accessで管理するべき案件です。
Accessならクエリの抽出で簡単に欲しい条件を抽出できます。

お礼

ありがとうございます。
ただ、今後仕事する上でどうしても、間違えないように分けて管理していきたく質問しました。

回答No.1

aokii

飛ばすという意味が解りませんが、sheet1のE列を条件付きフィルタを使って、要支援1、要支援2のもののみ表示してはだめでしょうか。また、必要なら、フィルタを使って表示された状態でそのままコピーして、別シート貼り付ければ作れます。

あるいは、sheet1を別シートに全てコピーしてから、別シートのE列を基準に並べ替えて、要介護1～5の行を削除してから、整理番号順に並べ替えて、順番をもとに戻す方法もあります。

全自動でやるには、これらの操作をマクロに登録してはいかがでしょう。

お礼

マクロで組みたいのですが、なかなか思うように作成できません。
関数でなんとかやりました。

ありがとうございます。