オートフィルタをかけた表に一行おきに行を入れる

質問

エクセル2003での質問です。

オートフィルタをかけた表に一行おきに行を入力しようとすると、
フィルタがかかっている部分の、最後の行に一行足すのが難しいです。

行を選んで「挿入」すると上に足されてしまうので…

下に足す方法はないでしょうか。

回答No.9

MackyNo1 · ベストアンサー

訂正と補足です。

「置換」と「検索」を間違えていました。
正しい操作を簡便に述べると以下のような操作になります。

フィルタされた1番上のセルに「１」と入力し、そのセルの右下をダブルクリックし、フィルタ列の▼から「すべて」でフィルタモードを解除し、そのまま選択範囲の周囲にカーソルを合わせて下方向に1つドラッグ、Ctrl+Fで「検索」ダイアログを出して検索する文字列に「1」を入力して「すべて検索」し、Ctrl+Aのショートカット操作で１のセルすべてを選択し、選択セルの上で右クリックから「挿入」で「行全体」にチェックを入れ「OK」します。

お礼

お礼が遅くなりました。

結局、質問時のデータをどのように処理したか、
本人もわからなくなってしまいましたが

おそらくまたこのような作業をすることになると思い
その際にはこちらを参照したいと思います。

BAは何度もご回答くださった、こちらに。

皆様ありがとうございました。

回答No.8

MackyNo1

>実際のところは、私の表では
「=A1="検索文字列"」という数式で「TRUE」であるはずなのに「TRUE」を返してこない部分が大量発生します。（逆もあり）
おそらくそれは私の表の、フィルタする列にときおり空欄が存在するためだと思われます。

基本的に1つ上のセルを参照（または条件が一致する）する数式を入力すれば、空白データなどに関係なく「TRUE」となるはずです（数式を使ったフィルタオプションの設定と同じような数式を入力する）。

具体的なフィルタ条件を提示していただければ、数式例が提示できると思います。

ちなみに、No3の方の回答を利用するなら以下のような操作が簡単です。

該当データをフィルタしたら、フィルタされた1番上のセルに「１」と入力し、そのセルの右下をダブルクリックして、フィルタされたすべてのセルに１を入力します（フィルタモードのオートフィルは連続データではなく必ず「コピー」されます）。

フィルタモードを解除し、そのまま選択範囲の周囲にカーソルを合わせて下方向に1つドラッグします。
そのまま（１のセルが1つ下にずれたセルを選択した状態で）、Ctrl+Fで置換ダイアログを出して検索する文字列に「1」を入力して「すべて置換」し、Ctrl+Aのショートカット操作で１のセルすべてを選択します。
そのまま1のセルが選択された状態で、右クリックから「挿入」で「行全体」をすれば、どのようなレイアウトでもご希望の位置に一気に空白行を挿入することができます。

お礼

ありがとうございます。

＞基本的に1つ上のセルを参照（または条件が一致する）する数式を入力すれば、空白データなどに関係＞なく「TRUE」となるはずです（数式を使ったフィルタオプションの設定と同じような数式を入力する）。

そうなんですか…しかし全部ならないわけでなく、なったりならなかったりするので
なにかがおかしいのかもしれません。

＞具体的なフィルタ条件を提示していただければ、数式例が提示できると思います。

説明が難しいのですが
Ａ～Ｗ列、２～２０００行に主に文字データが入っていて、
Ｊ列で10個の項目があって、そのひとつを選んでフィルタをかけています。

＞該当データをフィルタしたら、フィルタされた1番上のセルに「１」と入力し、そのセルの右下をダブルクリックして、フィルタされたすべてのセルに１を入力します（フィルタモードのオートフィルは連続データではなく必ず「コピー」されます）。

ダブルクリックをしても反応がありません。
ドラッグ＆ドロップすると入力が可能でしたのでやりました
（なお、１は文字列として使用しており混乱しましたので　★でやりました）

＞フィルタモードを解除し、そのまま選択範囲の周囲にカーソルを合わせて下方向に1つドラッグします。

ドラッグするとコピーされてセルはズレませんでした

＞そのまま（１のセルが1つ下にずれたセルを選択した状態で）、

１マス挿入して、ズラして選択しました

＞Ctrl+Fで置換ダイアログを出して検索する文字列に「1」を入力して「すべて置換」し、Ctrl+Aのシ＞ョートカット操作で１のセルすべてを選択します。
＞そのまま1のセルが選択された状態で、右クリックから「挿入」で「行全体」をすれば、どのようなレ＞イアウトでもご希望の位置に一気に空白行を挿入することができます。

残念ながら一行おきではなく、まとまって空白行が入りました

やはりエクセルそのものの状態がおかしいのかもしれません。

回答No.7

mu2011

NO3です。
>入力しようとすると行範囲選択は外れてしまってうまくできませんでした。
　⇒例えば、フィルタ後に空き列（仮にＦ列とします）のFxx:Fyy範囲を選択、数式バーに１を入力、ctrl＋enterキー（日本語入力モードならenterキーは２回）を同時押下としてみて下さい。

お礼

なるほど！！

これは今回に限らず応用範囲が広い感じがします！

皆さん普通にご存知のことでしょうか、
ＢＡ入れずに待ってよかったです

ありがとうございます。

回答No.6

tom04

こんばんは！
VBAでの一例です。

画面左下にある操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので、
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）
尚、マクロはオートフィルタを掛けた後に実行し、マクロ実行後オートフィルタを解除してみてください。
1行目はタイトル行で、データは2行目からあるとします。

Sub test() 'この行から
Dim i As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Rows(i).Hidden = False Then
Rows(i + 1).Insert
End If
Next i
End Sub 'この行まで

以上、参考になれば良いのですが・・・m(_ _)m

お礼

ありがとうございます。

初心者です、マクロは全くわかりません、と質問に書くべきでした。
全くわからず、試すことができません。

しかし、同じ件で検索した方で
わかる方には助けになると思うので、大変ありがたく思います。

回答No.5

MASUKUBO

例えば元の表がシート1のA列からD列まであるとして1行目には項目名があるとします。オートフィルタは項目名以下の行について行われることになりますね。
そこでフィルタ操作をおかなわない元の表についていくつかの作業列を設けます。
E2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",ROW(A1))

F2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(SUBTOTAL(2,INDEX(E:E,ROW()))=1,INDEX(E:E,ROW())+0.5,"")

G2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ISERROR(SMALL($E:$F,ROW(A1))),"",SMALL($E:$F,ROW(A1)))

H2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(G2="","",RANK(G2,G:G,1))

これらの作業列のデータをもとにシート2に表を作成します。
シート2のA1セルからD1セルにはシート1での項目名を入力するとして、A2セルには次の式を入力してD2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(INDEX(Sheet1!$A:$D,IF(COUNTIF(Sheet1!$E:$E,INDEX(Sheet1!$G:$G,MATCH(ROW(A1),Sheet1!$H:$H,0))),MATCH(INDEX(Sheet1!$G:$G,MATCH(ROW(A1),Sheet1!$H:$H,0)),Sheet1!$E:$E,0),10000),COLUMN(A1))=0,"",INDEX(Sheet1!$A:$D,IF(COUNTIF(Sheet1!$E:$E,INDEX(Sheet1!$G:$G,MATCH(ROW(A1),Sheet1!$H:$H,0))),MATCH(INDEX(Sheet1!$G:$G,MATCH(ROW(A1),Sheet1!$H:$H,0)),Sheet1!$E:$E,0),10000),COLUMN(A1)))

以上の準備作業を行った上でしーと１の項目のどれかでオートフィルタの操作を行ってください。シート2にはお望みの表が表示されます。このシート2の表はシート1でのオートフィルタが行われた状態で有効に働きますのでオートフィルタを解除すればすべての行に空白行が入った表になってしまいます。そこでそれらの影響を無くするためにはオートフィルタを行った後でシート2全体をコピーし、例えばシート3のA2セルを選択してから右クリックし「形式を選択して貼り付け」で「値」にチェックをしては貼り付けを行います。オートフィルタで選ばれた行の下の行に空白行が入った表が得られることになります。

お礼

す、すごく大量の作業が必要なのですね…

今、自分が抱えている作業は、下記の方の方法の方が早いようで
取り急ぎはそれでやりますが

今後大量のデータを扱う際にはぜひ
参考にさせていただきたいと思います

ありがとうございました。

回答No.4

MackyNo1

フィルタするデータ数が多い場合でも、ご希望の操作を簡便に行うなら、以下のような補助列を使用した操作手順がお勧めです。

たとえば、1行目が項目名でA列でフィルタする場合、リストの右側の補助列の2行目（例えばC2セル）に「=A1="検索文字列"」と入力し、下方向にオートフィルし（1つ上のセルを参照する数式は、実際のフィルタ条件によって数式を変更してください）、「データ」「フィルタ」で補助列の「TRUE」を選択します。
次に、フィルタされた行全体を選択してから、「編集」「ジャンプ」「セル選択」で「空白セル」にチェックを入れ、右クリックから「挿入」してまとめて空白行を挿入します。

フィルタ対象セルが連続しない場合は、きちんと該当セルの下に空白行が挿入されていますが、連続データの場合はその数だけ空白行が連続していますので、この部分だけドラッグアンドドロップで行の移動をしてください。

お礼

ありがとうございます。

おそらくこの方法が質問に対しては一番合致していると思うのですが

実際のところは、私の表では
「=A1="検索文字列"」という数式で「TRUE」であるはずなのに「TRUE」を返してこない部分が大量発生します。（逆もあり）
おそらくそれは私の表の、フィルタする列にときおり空欄が存在するためだと思われます。
なので検証ができないのですが…たぶんそれがうまくいけば一番この方法が楽ではないかと思うので…

私の各所お礼で書き込んだコメントに対して、何か書かれる方がいると思うのでもう少し待ちまして
その後、ＢＡを決めたいと思います、
ありがとうございました。

回答No.3

mu2011

NO2です。
>元データに反映させたいのです。
　⇒行挿入は指定行前方にしかできない。
安直ですが、フィルタリングされた表の空き列で行範囲選択→フラグ入力（例えば、１を入力）→ctrl＋enter→フィルタモードを解除→フラグ入力のある行を起点に行挿入しては如何でしょうか。

お礼

ありがとうございます。

フィルタリングされた表の空き列で行範囲選択→フラグ入力（例えば、１を入力）→ctrl＋enter→

というのをやろうとするのですが
入力しようとすると行範囲選択は外れてしまってうまくできませんでした。
たぶん何か私が勘違いしていると思うのですが…

回答No.2

mu2011

>下に足す方法はないでしょうか。
　⇒元データを加工する事は芳しくないと思いますので、フィルタされた表をコピーして別シートに貼り付けてから加工しては如何でしょうか。

お礼

ありがとうございます。

フィルタされた表にではなく、元データに反映させたいのです。
説明不足ですみません。

回答No.1

keithin

＞下に足す方法はないでしょうか。

一つのすぐにできる方法としては。
たとえば今オートフィルタで「ｂに等しい」行を表示させているとしましょうか。
条件を逆にして「ｂに等しくない」で絞り込み，その条件で表示されている行範囲のカタマリのそれぞれ先頭に空行を挿入すれば，「ｂの下」に空行が挿入できます。

＃その状態から改めて「ｂおよび空行」を表示したいときは，オートフィルタの条件で「ｂに等しい　または　空白に等しい」で絞り直す格好になります

別の手としては，とりあえず同じく「ｂに等しい」で絞って表示させているとして，ｂの行の「上」に空行を挿入し，ｂの行を今挿入した空行に移動してしまえば下に空欄が出来ます。

いずれにしても通常の操作では，「下に行を挿入」を行う方法はありません。
空行を移動して挿入するような方法でもダメです。

お礼

お礼が遅くなりました。

すばやくわかりやすいご返信をいただきありがとうございました。