エクセルで空白行に１個上の行を一度にコピー

質問

A列　　　B列　　　Ｃ列
1行　　　　　123　　　　酒　　　 100
2行　　　　　123　　　　酒　　　 100
3行　　　　　　　　　　　　　　　 (合計)　　
4行　　　　　215　　　ビール　 　200
5行　　　　　215　　　ビール　 　200
6行　　　　　215　　　ビール　 　200
7行　　　　　　　　　　　　　　　 (合計)
8行　　　　　324　　　　ワイン　 300
9行　　　　　324　　　　ワイン　 300
10行 　　　　324　　　　ワイン　 300
11行 　　　　324　　　　ワイン 　300
12行　　　　　　　　　　　　　　　(合計)

(合計)表記の行のA列B列にひとつ上の行の数字文字をコピーし、かつ合計の行だけ残し、
他の行は削除したいのですが。一度にできる良い方法がございましたら教えて頂けませ
んでしょうか。要は合計だけの表作成です。
実際のデータは３千行ほどあります。何卒よろしくお願いいたします。

回答No.10

tom04

No.1・5です。
補足の件について・・・
「合計」欄はSUM関数で表示していて、他の列の配置も最初の質問とは変わっていますので、
もう一度コードを載せてみます。
（今回は行すべてを削除としています）
1行目はタイトル行でデータは2行目からとして、

Sub test()
Dim i As Long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row + 1 To 2 Step -1
If Cells(i, 1) = "" Then
With Cells(i, 6)
.Value = Val(Cells(i, 6))
.Offset(, -1) = Cells(i - 1, 5)
.Offset(, -2) = WorksheetFunction.SumIf(Columns(1), Cells(i - 1, 1), Columns(4))
.Offset(, -3) = Cells(i - 1, 3)
.Offset(, -4) = Cells(i - 1, 2)
.Offset(, -5) = Cells(i - 1, 1)
End With
Else
Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub

今度は何とかご希望に近い形になれば良いのですが・・・m(__)m

回答No.9

KURUMITO

回答Ｎｏ3です。
関数を使って十分対応できる内容です。
補足の説明で示されているデータの場合にはシート1のＡ1セルからＦ1セルまでに項目名が有り、2行目から下方にデータが有るとしたらＧ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(A$2:A2,A2)=1,MAX(G$1:G1)+1,"")

シート2にお求めの表を表示させるとして1行目はシート1の項目名をコピーして貼り付けます。
Ａ2セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$G:$G),"",IF(OR(COLUMN(A1)<=3,COLUMN(A1)=5),INDEX(Sheet1!$A:$F,MATCH(ROW(A1),Sheet1!$G:$G,0),COLUMN(A1)),IF(COLUMN(A1)=4,SUMIF(Sheet1!$A:$A,$A2,Sheet1!$D:$D),IF(COLUMN(A1)=6,SUMIF(Sheet1!$A:$A,$A2,Sheet1!$F:$F),""))))

回答No.8

mar00

合計の値だけがのこればいいのであれば
Sub Macro1()
WS = "Sheet1" 'ここを変更
Sheets(WS).Copy After:=Sheets(WS)
Application.ScreenUpdating = False
Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
myRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For i = myRow To 1 Step -1
If IsEmpty(Cells(i, 1)) Then
Cells(i, 1) = Cells(i - 1, 1)
Cells(i, 2) = Cells(i - 1, 2)
Else
Cells(i, 1).Clear
End If
Next
Range("A1:A" & myRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("A1").Select
Application.ScreenUpdating = True
End Sub
一度実行すると元に戻せなくなるので、シートをコピーしてから実行されるようにしていますのでシート名を変更してください。

回答No.7

MarcoRossiItaly

No.4です。

なるほど、質問文で「合計」と表示されているセルにSUMが入力されているという意味なのですね？こういう作りのデータベースにしてしまうと、後の処理が少ししにくくなりますね…できますが。

とりあえず思うのは、余計な行を単に削除してしまうと、当然ながら、SUMが参照しているセルも削除されるので、正しい値ではなくなるということです。

まずシートをコピーして増やし、加工用のシートとしましょう。次に、シート全体を選択（Ctrl+A）・コピー（Ctrl+C）。最後に、全体が選択された状態のまま、「形式を選択して貼り付け」で「値」のみ貼り付けましょう。そうすることで、数式は全て除かれ、数式の計算結果が保存されます。その後で削除したい行は削除すればよい。

あるいは、No.6さんの方法で抽出表を作り、それを他シートに値のみ貼り付けてもいいです。数式は全て除かれ、数式の計算結果が保存されます。

回答No.6

MackyNo1

質問を勘違いしていました。
合計の行だけの表を作りたいという意味なら、以下のような操作が簡単です。

フィルタでA列の「空白」だけにチェックを入れ、合計行だけを表示しておきます。
その空白行のA列のセルに1つ上のセルを参照する数式（一番上のセルがA5セルなら「＝A4」）を入力し、右方向及び下方向にオートフィルします。
このデータ範囲をコピーして、新規シートで右クリック「形式を選択して貼り付け」で「値」貼り付けすればご希望のデータが作成できます。

回答No.5

tom04

No.1です！
補足の件について・・・
前回のコードは行すべてを削除していました。
A～C列の行のみを削除し、他の列はそのまま！というのがご希望ですね？

前回のコードは削除して↓のコードに変更し、マクロを実行してみてください。

Sub test()
Dim i As Long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row + 1 To 1 Step -1
If Cells(i, 1) = "" Then
With Cells(i, 1)
.Value = Cells(i - 1, 1)
.Offset(, 1) = Cells(i - 1, 2)
.Offset(, 2) = WorksheetFunction.SumIf(Columns(2), Cells(i, 2), Columns(3))
End With
Else
Range(Cells(i, 1), Cells(i, 3)).Delete (xlUp)
End If
Next i
Application.ScreenUpdating = True
End Sub

こんな感じではどうでしょうか？m(_ _)m

補足

ご回答、ありがとうございます。
申しわけございません。質問方法が悪かったようです。
SMU関数が入力された行のみ残し、それ以外の行を削除したいのです。
普通にSMU関数の行だけ残すと何の商品か分からなくなります。
そこで、SMU関数の行にも商品名等を入力したいのですが。
実際に近い表であらわすと、

コード 品名　　容量　　数　　　単価　　合計金額　...　...　...　....　
123　　濁酒　　5Ｌ　　５個　　100円　　500円
123　　濁酒　　5Ｌ　　３個　　100円　　300円
　　 　　 　　　　　　　　８個　　　　　　　　800円
223 　 ビア 　 1Ｌ　　３個　　200円　　 600円　　　　　　
223 　 ビア 　 1Ｌ　　５個　　200円　　1000円
　　 　　 　　　　　　　　８個　　　　　　　　1600円

列も行ももっと沢山あります。この表の３行目と６行目の様に合計関数が入っている行に
コード 品名　容量　数　単価　合計金額　その他を表示して、合計行のみ残したいのです。
可能でしょうか。

回答No.4

MarcoRossiItaly

ご希望の「一度にできる良い方法」に該当するか分かりませんが、「簡単に短時間でできる方法」を示します。

(1)
D2～F2に次の式を入力し、D2:F2の範囲を下方向にオートフィル。

D2　=if(a2,,a1)
E2　=b1
F2　=c2

(2)
D:Fの列の範囲をコピーし、H:Jの範囲に値のみ貼り付け。

(3)
H1:H3000の範囲（H列全体ではないことに注意）を選択した状態で置換（Ctrl+H）のダイアログを起動し、「0」→「」（未入力）の指定により、「0」のセルを未入力のセルにする。

(4)同じ範囲を選択した状態でジャンプ（Ctrl+G）のダイアログを起動し、「セル選択」ボタンで「空白セル」を指定し、「OK」ボタンを押下。

(5)
未入力セルのみが選択された状態になっています。続けて「行全体を削除」（Ctrl+ -（マイナス）と押し「行全体」を選択）を実行。

以上の操作により、C列に「(合計)」が載っている行のみがワークシート上に残っています。また、1つ上の行にあったデータもコピーされています。

★
もしも、同一の品目については行によらず同一の数値（価格？）になっていて、品目・価格のリストが得られればいいだけ、という話だすれば、更に簡単な方法があります。元の表を選択し、「ピボットテーブル」という機能をいじくってください。何となく、A・B列から必要データを抽出した表が得られます。その表を別のシートにコピーして、後から「(合計)」をくっつけるなどしてください。

回答No.3

KURUMITO

シート1にはお示しのデータが1行目は項目名で2行目から下方に入力されているとします。
合計のある行ではＡ列は空欄でＣ列に合計額が入力されているとします。
作業列をＤ列に作って、お求めの表はシート2に表示させることにします。
シート1のＤ2セルには次の式を入力して下方にドラッグコピーします。

=IF(AND(A3="",C3<>""),INT(MAX(D$1:D1))+1,IF(AND(A2="",C2<>""),INT(MAX(D$1:D1))+0.1,""))

シート2に移ってＡ1セルからＣ1セルには項目名を入力するとしてＡ2セルには次の式を入力してＣ2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$D:$D),"",IF(COLUMN(A1)<=2,INDEX(Sheet1!$A:$C,MATCH(ROW(A1),Sheet1!$D:$D,0),COLUMN(A1)),IF(COLUMN(A1)=3,INDEX(Sheet1!$A:$C,MATCH(ROW(A1)+0.1,Sheet1!$D:$D,0),3),"")))

回答No.2

MackyNo1

実際にC列に「（合計）」と入力されているなら以下のような操作で一括入力することができます。

ご使用のエクセルがExcel2007以降のバージョンの場合、C列にカーソルを置いて（またはデータ範囲を選択して）、データタブの「フィルタ」をクリックし、C列の「（合計）」のチェックを外し合計以外の行だけを表示します。

その状態でデータ範囲を選択し、Ctrl+Gでジャンプダイアログを出し、「セル選択」から「空白セル」にチェックを入れ「OK]し、そのまま「=A2」(空白セルの一番上のセルの1つ上のセルを参照する数式）と入力し、Ctrlキーを押しながらEnterで確定してください。
最後にフィルタを解除すればご希望のデータになっています。

Officeソフトはバージョンによって使用できる機能や、操作方法が大きく異なりますので、質問の際には必ずバージョンを明記するようにしましょう。

回答No.1

tom04

こんにちは！
データは質問通り、品名ごとに並んでいる訳ですよね？
VBAになってしまいますが・・・
一例です。
データはお示し通り1行目からあるとします。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row + 1 To 1 Step -1
If Cells(i, 1) = "" Then
With Cells(i, 1)
.Value = Cells(i - 1, 1)
.Offset(, 1) = Cells(i - 1, 2)
.Offset(, 2) = WorksheetFunction.SumIf(Columns(2), Cells(i, 2), Columns(3))
End With
Else
Rows(i).Delete
End If
Next i
End Sub 'この行まで

※　一旦マクロを実行すると元に戻せませんので、別Sheetでマクロを試してみてください。

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

補足

早速のご回答ありがとうございます。
参考になりました。ただ、実際の表では他列にもデータがあり、
SUM関数が入力されたセルもございます。
表をいくつかに分けてから教えて頂いたマクロ処理をし、後に合体させて作成しても良いのですが。
できれば、応用したいと思います。
他の列の空白行のコピーとSUM関数の入力されたセルをつぶさず処理させるには
どこを変更あるいは追加すれば用意のでしょうか。

切羽詰っております。ご教授頂けましたら幸いです。