[[20190403061209]]　『オートフィル』（まれちゃん）　ページの最後に飛ぶ

『オートフィル』（まれちゃん）

いつもお世話になっております。
オートフィル機能についての質問です。
効率の良い方法をご存知のかたお知恵を拝借させてください。

現在
　A　B　C　D　
1月日曜日　数値
24月3日水　=別シート!A2
3
4
5
6
74月2日火　=別シート!A7
8
9
10

このようなシートがA～AX　2～1000くらいまでのデータでございます。
毎日A2～AX2をオートフィルで下へ引っ張ってオートフィルをしております。
空白が時にはなかったり1行だったり10行だったりランダムです。
そして関数なども随所に入っております。
文字列がある行までオートフィルをしてまた次の行からオートフィルをしてという繰り返しをしておりますが、これを一度で出来る方法ってありますでしょうか？　よろしくお願いいたします。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 おはようございます　＾＾
すこしお勉強しないといけないかもですが、ＶＢＡですとＥｍｐｔｙ
かどうか調べるか、カウントブランク関数で空か調べるかして。何も
無ければA2～AX2をコピーして貼付けすれば出来るかな？。。。
　＾＾；　。。。と思いますです。
（隠居じーさん） 2019/04/03(水) 08:05

 A列選択→Ctrl+G（ジャンプ）→セル選択→空白セル→OK

 多分A3がアクティブセルなのでそのまま=A2と入力しCtrl+Enter

 で如何でしょうか？
（コナミ） 2019/04/03(水) 09:42

オートフィルタは使用できるでしょうか？一時的に設定して、後で解除でも良いですが。

オートフィルタが使えるなら、空欄で絞ってから、数式を埋めたい空欄セルを範囲選択します。このままだと見えていないセルまで範囲に入ってしまいますが、ALT+; を押下すると、見えているセルだけが選択状態になります。

コナミさんの方法と同じ選択状態なので、後はフィル機能で1つ目の式を下方向に全て設定しましょう。
（???） 2019/04/03(水) 09:55

 ???さん、オートフィルタなら可視セル選択しなくても、そのままフィルすれば
 見えている範囲だけにデータが入りますよ。

 手動の非表示の場合は可視セル選択が必要ですが。
（コナミ） 2019/04/03(水) 10:15

おや、本当ですね。 ALT+; なしのままでも、フィルタかけて表示している行だけ埋まりますね。何か勘違いしていたようです。ありがとうございます。
（???） 2019/04/03(水) 10:24

隠居じーさんさん
コナミ　さん
???　さん

お返事ありがとうございます。
コナミさんのやり方でほとんど出来たのですが、関数が入ってる項目がうまく行きません。
？？？さんのやり方も同様で関数の項目がうまく行きません。

例
　　A　　B　C　D　
1　数値　関数
2　200　=A2
3
4
5
6

これを先ほどのやり方でやると
　　A　　B　C　D　
1　数値　関数
2　200　=A2
3 200 =A2
4 200 =A2
5 200 =A2
6 200 =A2
となります

これを

 　　A　　B　C　D　 
1　数値　関数 
2　200　=A2 
3  200  =A3
4  200  =A4
5  200  =A5 
6  200  =A6

としたいのです。
アイデアがございましたらご教示をお願い致します。
（まれちゃん） 2019/04/03(水) 15:04

 え？いや、そうはならないと思いますよ。

 本当にそんな式ですか？
（コナミ） 2019/04/03(水) 15:12

コナミさん

式にはならないんですけど
関数の結果の数字が
A2が２００だとしたら
２００が連続して続くようになってますね

色んな関数組んでるので
A2
A3
みたいな感じになってくれたらありがたいのです。
（まれちゃん） 2019/04/03(水) 15:22

例

　 A　B　 C
1 200 400 200(=A1)
2 300
3 400
4 500

こちらをB2の空白からCtrl+Enterすると

　 A　B　 C
1 200 400 200(=A1)
2 300 400 200(=A1)　
3 400 400 200(=A1)
4 500 400 200(=A1)

となってしまうので
これを
　 A　B　 C
1 200 400 200(=A1)
2 300 400 300(=A2)　
3 400 400 400(=A3)
4 500 400 500(=A4)

にしたいのです。ちなみに（）内はセル内の計算式です

（まれちゃん） 2019/04/03(水) 15:37

 関数ですよね？基本的には上と同じ式をいれるようになってますが、
 オートフィルで変わる部分はきちんとそのようになるはずです。

 でも式の途中でオートフィルでも変えられない部分は、その方法では変わりません。
 それは手動でも同じだと思いますが、手動でできていたのにできないということですか？
（コナミ） 2019/04/03(水) 15:44

コナミさん
お返事有り難うございます。

手動でオートフィルしていた時は出来ました。

A列選択→Ctrl+G（ジャンプ）→セル選択→空白セル→OK

 多分A3がアクティブセルなのでそのまま=A2と入力しCtrl+Enter

こちらだと関数以外では手動と同様の効果があるのですが
関数が入ると絶対参照になってしまうので
こちらを相対参照にしたいのです。

大元の関数セルを絶対参照や相対参照にいじっても見たのですがどうしても
絶対参照になってしまうのです。

もしかしたらエクセルのオプションをいじれば出来るのかなぁーとも思い始めました。
（まれちゃん） 2019/04/03(水) 15:54

 >関数が入ると絶対参照になってしまうので

 その関数をかいてもらえますか？
（コナミ） 2019/04/03(水) 15:57

コナミさん

実際に使用している関数です
=LEFT(B2,5)
=MID(B2,9,5)
=LEFT(S2,1)*1
=MID(S2,3,1)*1
=IF(P2=W2,"A","")
=CONCATENATE(AF2,AG2,AH2,AI2,AJ2,AK2,AL2,AM2,AN2)

全て行2に60列くらいこれが入っています。
（まれちゃん） 2019/04/03(水) 16:27

 あー、なるほどね。ようやく意味がわかりました。

 この場合は確かに私の提示した方法では難しいですね。
 そう言えば、最初に回答する時に数式などの場合はそこは別のやり方にしてくださいと
 書こうと思っていたことを思い出しました。

 その部分だけは手動でというのは大変ですか？

（コナミ） 2019/04/03(水) 17:07

文字列と関数をシートで分けて文字列だけコナミさんのやり方でやってみます。
これだけでも大幅に時間の節約になりました。

関数の方は今まで通り手動でやって見ようかと思います。
また暇があったら質問してみます。
なんとなく出来る技がありそうな気がしなくもないので。

コナミさんはじめ
隠居じーさんさん
???さん
貴重なお時間を割いて頂いてありがとうございます。

（まれちゃん） 2019/04/03(水) 17:32

 こんな感じでしょうか。。。外していましたらすみません　＾＾；。。。m(_ _)m
新規ＢＯＯＫで　Sheet1　が対象です。列数、行数は実際の情報に合わせて下さいね。
所要時間は１０００行で、２３秒くらいでした（必須！バックアップ。。。(￣▽￣)。。。）

 Option Explicit
Sub main()
    Dim I As Long
    Dim 列のセル数 As Long
    Dim 行のセル数
    Dim T As Date
    T = Timer
    Pstart
    列のセル数 = 50
    行のセル数 = 1000
    With Worksheets("Sheet1")
        .Rows(2).Range("A1:AX1").Copy
        For I = 3 To 行のセル数
            If GetEmptyCount(.Rows(I).Range("A1:AX1")) = 列のセル数 Then
                .Cells(I, 1).PasteSpecial (xlPasteAll)
            End If
            Application.StatusBar = Space(5) & "処理中 = " & Format(I - 2, "0000000")
            DoEvents
        Next
    .Cells(1).Activate
    End With
    Application.CutCopyMode = False
    MsgBox Format(Timer - T, " 0.0  秒")
    Pend
End Sub
Private Function GetEmptyCount(ByVal rR As Range) As Long
    Dim I As Long
    Dim tR As Range
    For Each tR In rR
        If IsEmpty(tR) Then
            I = I + 1
        End If
    Next
    GetEmptyCount = I
End Function
Private Sub Pstart()
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
End Sub
Private Sub Pend()
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .StatusBar = ""
    End With
End Sub
（隠居じーさん） 2019/04/03(水) 17:59

隠居じーさんさん

御返事遅くなり申し訳ございません。

色々とお手間を取らせてしまってすいません。
こちらのマクロでは正しく機能しませんでした。
こちらの説明が下手ですいません。

色々と改変したり、自分でマクロ検証してようやく今朝方出来ましたので、
ご心配おかけしましたが、無事解決致しました。

また何かの機会が御座いましたら、ご協力をお願いします。
（まれちゃん） 2019/04/08(月) 11:37

 恐縮でございます。解決されてよかったですね。
 でわ、また　m(_ _)m
（隠居じーさん） 2019/04/08(月) 12:13

コメント返信：

[ 一覧(最新更新順) ]

[[20190403061209]] 『オートフィル』（まれちゃん） ページの最後に飛ぶ

[[20190403061209]]　『オートフィル』（まれちゃん）　ページの最後に飛ぶ