Excelで条件に満たしたセルを飛ばす

色々と前の投稿を見ましたが、どうしても答えが分からず質問させてもらいます！！

1から31(1ヶ月)のセルの中で、毎月ランダムにたくさんのアルファベットが入ります。
その中で、「a」というアルファベットのみを別シートに飛ばし、その別シート上では「1」として表記したいです。さらに、その「a」が入った日付も一緒に別シートに飛ばしたいです。
一ヶ月の中で「a」が入るのは2～5回ぐらいなので、別シートには7日分の表を用意するつもりです。
ただ、その1ヶ月の中でどこに「a」が入るかは決まっていません。

日付も全く未定なので、「a」が入ったら別シートに1として、さらに日付が入ったセルの数字も一緒に飛ばす。

マクロは全く分からないので、出来れば関数が良いです！！

No.5 回答者： tom04 回答日時： 2014/11/20 19:03

こんばんは！

＞マクロは全く分からないので、出来れば関数が良いです！！
というコトですので、一例です。

↓の画像のような配置になっているとします。
Sheet2のA2セルに
=IFERROR(INDEX(Sheet1!A$1:A$50,SMALL(IF(Sheet1!B$1:B$50="a",ROW(A$1:A$50)),ROW(A1))),"")
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　A2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

B2セル（配列数式ではありません）には
=IF(A2="","",1)
という数式を入れ　A2・B2セルを範囲指定　→　B2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

※　お望みの方法でないVBAでもやってみました。
Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト

Sub Sample1() 'この行から
Dim lastRow1 As Long, lastRow2 As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")
With Worksheets("Sheet1")
lastRow1 = .Cells(Rows.Count, "A").End(xlUp).Row
lastRow2 = wS.Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
If lastRow2 > 1 Then
Range(wS.Cells(2, "A"), wS.Cells(lastRow2, "B")).ClearContents
End If
.Range("A1").AutoFilter field:=2, Criteria1:="a"
If Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
Range(.Cells(2, "A"), .Cells(lastRow1, "A")).SpecialCells(xlCellTypeVisible).Copy wS.Range("A2")
lastRow2 = wS.Cells(Rows.Count, "A").End(xlUp).Row
If lastRow2 > 1 Then
Range(wS.Cells(2, "B"), wS.Cells(lastRow2, "B")) = 1
End If
End If
.AutoFilterMode = False
Application.ScreenUpdating = True
End With
End Sub 'この行まで

次に画面左下にある「Sheet1」のSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りSheet1のB列にデータを入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim lastRow1 As Long, lastRow2 As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
Call Sample1
End Sub 'この行まで

これでも同じ結果になると思います。

※　VBAの場合はB列の数式が消えてしまいます。m(_ _)m

No.4 回答者： MarcoRossiItaly 回答日時： 2014/11/20 05:14

D4　1

E4　=if(countif(sheet1!B$4:B$34,"a")<d4,"",index(sheet1!a:a,small(index(row(sheet1!B$4:B$34)+999*(sheet1!B$4:B$34<>"a"),),d4)))
F4　=if(e4="","",1)

自動的に転記するためには、上のように、難しい数式が必要です。それよりも、単にオートフィルタで絞り込むだけにとどめるのが通常の手法です。自動にする必要性があまりないケースが多いので。別シートに保存したければ、絞り込んだ状態でコピペしてください。

なおセルに数式をセットするだけではこのように難しいのですが、マクロなら初級レベルでもできる課題です。

この回答へのお礼

回答ありがとうございます！！
すごい詳しく実際に作ってもらえてありがたいです！

やっぱり数式だとすごい複雑ですね～。
マクロをこれから覚えていこうと思います。
ありがとうございました！

お礼日時：2014/11/20 08:07

No.3 回答者： MackyNo1 回答日時： 2014/11/19 23:35

元データのレイアウトがわからないのですが、Sheet1のA2セル以下に日付、B2セル以下に「a」という文字が入力されているなら、別シートのB2セルに以下の式を入力し下方向にオートフィルします。

=IF(COUNTIF(Sheet1!$B$2:$B$32,"a")<ROW(1:1),"",1)

同様にA2セルには以下の式を入力して下方向にオートフィルします。

=IF(B2="","",INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$B$2:$B$32<>"a")*100+ROW($2:$32),),ROW(1:1))))

No.2 回答者： bin-chan 回答日時： 2014/11/19 23:20

「別シートに飛ばす」ってハヤリなの？

１）オートフィルタ掛けて、「ａを含む」を表示させ、
２）セル選択で「可視セル」のみ選択してコピーして、
３）別シートヘペースト

ではダメですか？

No.1 回答者： shintaro-2 回答日時： 2014/11/19 23:16

>マクロは全く分からないので、出来れば関数が良いです！！

「ａを飛ばす」という言葉が、
別シートに”１”を記入して、元のシートから”a”を削除するという意味なら
関数では不可能です。