- ベストアンサー
EXCEL 自動的に行を追加
初めまして、エクセル初心者です。 簡単な帳簿を作りたいのですが、わからない作業があり、 gooleやこちらのサイトを検索したのですが思うような情報が得られなかったため相談させていただきたいのです。 日 付 品目 収入 支出 合計 １／１ あめ １０ １０ １／１ ガム ２０ ３０ １／２ 支払 １０ ２０ １／５ あめ １０ ３０ １／５ あめ １０ ４０ １／５ ガム ２０ ６０ １／５ 支払 ４０ ２０ 【ココに自動的に行を追加したいです。】 １ヶ月のうちに売り上げと支出が数回あります。 例えば１ヶ月に３０件の収支記録を入力する時もありますし、 ５件の時もあります。 件数に差が大きいため最初から表を作るのではなく、 日 付 品目 収入 支出 合計 １／５ 支払 ４０ ２０←データ入力後に自動的に 【ココに自動的に行を追加したいです。】 ＊もちろん、前行の計算式を引き継いだ行を自動的に入るようにしたいのですがIF関数でしょうか？ 初心者なのでできるだけ簡単な方法で、自動的に入れたいのです。 わかりにくい質問表現で申し訳ありませんが、どうぞよろしくお願いいたします。
- みんなの回答 （8）
- 専門家の回答
質問者が選んだベストアンサー
annyanさんのしたい事が分って来ました。 今までの回答の流れで行なうと次のような手順で・・ （１）１２ヶ月分のシートを作成する。ここでシート名は「１月」、「２月」、・・のように「月」という文字を入れるのが必須（ＶＢＡの適用シートを絞りこむため）。 （２）各月のシートに項目名等をインプットする。（３行目はＶＢＡ適用行の境界認識に必須） A 列 Ｂ列 Ｃ列 Ｄ列 Ｅ列 １行目 日 付 品目 収入 支出 合計 ２行目 ３行目 ○△月合計 （３）Ａｌｔキーを押したままＦ１１キーを押し、ＶＢＡのコードウィンドウを表示 （４）画面左側に表示されているプロジェクトエクスプローラ画面の中の「ThisWorkbook」を右クリック→コードの表示（Ｏ）を選択 （５）次のコードを貼り付け Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'セルにデータ入力時の処理 Dim r As Long If InStr(ActiveSheet.Name, "月") >= 1 Then 'シート名に"月"が含まれるシートの場合の処理 r = Range("a1:a65536").Find(What:="合計", lookat:=xlPart).Row If Target.Count = 1 And Target.Row < r And Target.Row >= 2 And Target.Column < 5 Then 'データ入力のセルが2行目～合計行未満の行かつ2～5列された場合の処理 Cells(Target.Row, 5).FormulaR1C1 = "=SUM(R2C[-2]:RC[-2])-SUM(R2C[-1]:RC[-1])" 'E列のセルに残高計算式をセット If r - Range("E1").End(xlDown).Row <= 1 Then '合計行の手前に空白行がない場合の処理 Rows(Range("a1:a65536").Find("合計").Row).Select Selection.Insert Shift:=xlDown '1行空行を挿入 Cells(r + 1, 3).FormulaR1C1 = "=SUM(R[-2]C:R[" & Str(1 - r) & "]C)" '収入合計計算式をセット Cells(r + 1, 4).FormulaR1C1 = "=SUM(R[-2]C:R[" & Str(1 - r) & "]C)" '支出合計計算式をセット Cells(r + 1, 5).FormulaR1C1 = "=R[-2]C" '残高 End If End If Else 'シート名に"月"が含まれないシートの場合 Exit Sub '何もせず End If End Sub 既に入力済みのデータを活用する場合は、合計行の上に行挿入してエリアを確保し貼り付けてください。 「累計数値を翌月(次のシート)へ引き継ぐのに『sheet3のセル='sheet1'!セル名+'sheet2'!セル名』というのを使おうと思っていますが、VBAに不具合がでますか？」の回答 →不具合はありません。 …「こうやって身につけた！」への回答 →簡潔に回答するのは難しいです。好奇心から始めた素人なので。
その他の回答 (7)
- pc_knight
- ベストアンサー率66% (52/78)
No.4です。スミマセンNo.6の回答にて誤りがありました。「（正）○○○←（誤）下の表 」でした。 No.6回答への補足でannyanさんのしたいことがかなり分ってきました。 「月合計・累計の自動表示」の自動表示までを１シート内で行なおうとされていますね。その場合、シート内の構成を下の例のように細かく規定する必要があります。 １シート内で行なう場合、うっかりシート内の構成を変更するとＶＢＡにエラーを生じ自動表示が機能しなくなったり、後々構成を変更しようとするとＶＢＡ変更の必要が生じるなど融通性が損なわれる欠点があります。またＶＢＡコードも複雑さが増します。 従って、月に３０件程度のデータ件数でしたらNo.2のmu2011さんが回答されているようにＥ２列に計算式を入れ、それをNo.3のhana-hana3の回答のように下にコピーする手作業の方法が良いかと思います。 どうしてもＶＢＡによる自動化をお望みの場合、ＶＢＡを作る立場では、月合計・累計を別シートにするのが望ましいです。 それで宜しければ月合計・累計用の別のＶＢＡ作成と追加で済みます。 但し、シート内の構成を更に詳しく知らせていただかないとやりようがありません。 明確にして頂きたいことは、次の事項です。 （１） No6.の回答補足にて「月合計と累計の表示」が追加されました。No4.の回答補足にて「質問した表の下に別な表」とありますが、「質問した表の下に別な表」とは「月合計と累計の表示」のことか。 （２） シート内のデータ構成はどのようになっているか。 ・シート内に含まれるデータ：１ヶ月分、それとも１年分、あるいは複数年分？ ・シート内に含まれるデータが複数月分の場合、合計行の位置：各月の境界の間に月合計行を設ける、それとも全データの最終行の次の行から順次、各月の月合計行を設ける？ 次の例のようにシート内のデータ構成を明示されると回答がしやすくなります。 （シート内のデータ構成例） A 列 Ｂ列 Ｃ列 Ｄ列 Ｅ列 日 付 品目 収入 支出 合計 １／１ あめ １０ １０ ～～～～～間の日のデータ省略～～～～～～ １／３１ 支払 １０ １２０ １月合計 ８５０ ７３０ ２／１ ガム ２０ １４０ ～～～～～間の日のデータ省略～～～～～～ ２／２８ あめ ３０ １７０ ２月合計 ７２０ ６７０ 累計 １６７０ １４００ 月合計・累計の自動表示
補足
PCの調子が悪くお返事が遅くなってしまいすみませんでした。 私の説明が悪いばっかりに、pc_knightさんにお手間を取らせてしまってすみません。 No.6でpc_knightさんが教えてくださった内容がようやくうまくいきました!! ありがとうございます。 作成したいのはpc_knightさんが記入していただいた例題のような表です。 （１） No6.の回答補足について はい。「質問した表の下に別な表」は「月合計と累計」のことです。 （２）シート構成について 可能であれば１シートに1年分又は半年分作成できればいいのですが、扱うのが初心者なもので、1シートに1ヶ月分がいいと思っています。 1シートに複数データを入れあって訂正など加えたときにうっかりするとデータがおかしくなったことがありましたので。 なので、1シートに1ヶ月分の表・累計・合計でお願いします。 表の中身は日付・項目・収入・支出・合計です。 <sheet1> A 列 Ｂ列 Ｃ列 Ｄ列 Ｅ列 日 付 品目 収入 支出 合計 １／１ あめ １０ １０ ～～～～～間の日のデータ省略～～～～～～ １／３１ 支払 １０ １２０ ココにデータ入力後「１月合計・累計が下へ」 １月合計 ８５０ ７３０ １２０ 累計 ８５０ ７３０ １２０ <sheet2> ２／１ ガム ２０ １４０ ～～～～～間の日のデータ省略～～～～～～ ２／２８ あめ ３０ １７０ sheet1と同条件(データ入力後下へ) ２月合計 ７２０ ６７０ １７０ 累計 １５７０ １４００ １７０ 相変わらず説明べたですが…意味がわかりますでしょうか？ ※月合計はsheetつどあるのでよいのですが、累計数値を翌月(次のシート)へ引き継ぐのに『sheet3のセル='sheet1'!セル名+'sheet2'!セル名』というのを使おうと思っていますが、VBAに不具合がでますか？もし、出る又は他にお勧めがありましたら教えてください。 余談ですが、pc_knightさんのようにExcelを扱えるようになるにはやはり数こなすしかないのでしょうか…。「こうやって身につけた！」などありましたらご伝授ください。 重ね重ねの質問ですみません。 急ぎませんのでよろしくお願いいたします。
- pc_knight
- ベストアンサー率66% (52/78)
No.4です。annyanさんの質問の意味を理解しきれず回答していました。 仮に、３行目までデータが入力済み、４行目が次のデータ入力用の空白行、６行目以降が、下の表用のデータエリアであり次のようになっているとします。 ＜シートのフォーマット＞ A 列 Ｂ列 Ｃ列 Ｄ列 Ｅ列 １行目 日 付 品目 収入 支出 合計 ２行目 １／１ あめ １０ １０ ３行目 １／１ ガム ２０ ３０ ４行目 ５行目 ○○○ ← 下の表のタイトル（Ａ列に必須、シート中には、二種類の表が含まれるので、下表の開始行を示す識別が必要なため、ここで宣言） ６行目以降は、下の表のデータ そこで、４行目にデータ入力が行われると （１） ４行目Ｅ列セルに計算式がセットされ（前回回答とおなじ） （２） ５行目に行挿入が行われ、下の表が下にシフトされる（今回回答での追加機能） という風にしたい、これが質問の意味と解釈しました。外れていたらすみません。 即ち、４行目にデータ入力後は A 列 Ｂ列 Ｃ列 Ｄ列 Ｅ列 １行目 日 付 品目 収入 支出 合計 ２行目 １／１ あめ １０ １０ ３行目 １／１ ガム ２０ ３０ ４行目 １／２ 支払 １０ ２０ ５行目 ６行目 ○○○← 下の表のタイトル ７行目以降は、下の表のデータ のようになるとすると Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count = 1 And Target.Row < Range("a1:a65536").Find("○○○").Row And Target.Row >= 2 And Target.Column < 5 Then Cells(Target.Row, 5).FormulaR1C1 = "=SUM(R2C[-2]:RC[-2])-SUM(R2C[-1]:RC[-1])" 'E列のセルに残高計算式をセット If Range("a1:a65536").Find("下の表").Row - Range("e1").End(xlDown).Row <= 1 Then Rows(Range("a1:a65536").Find("下の表").Row).Select Selection.Insert Shift:=xlDown End If End If End Sub になります。 ここで、コード中の「○○○」は、５行目Ｅ列に定義した、下の表のタイトルと同じにします。
補足
ご回答ありがとうございます。 私の書き方が悪かったばっかりにお手数をおかけしてしまい申し訳ありません。父親に質問されて代理質問として投稿してるのですが、私が理解していなかった新たな部分が出てきました。 今回答を数回読んでいるのですが、丁寧に書いてくださってるにもかかわらず私の頭が弱いばっかりに少し理解に時間がかかりそうです。 もう一度読み直してこれからやってみたいと思います。 もしかしたら、新たな部分にも対応しているかもしれないので。とりあえずやってみます！ 一応新たな部分の追記です。 質問例に出した表の一番下の段(2行)に 月合計と 累計を表示させるそうです。その為、 最後の日付・品目・(収入・支出)・合計のデータ 空欄行自動挿入(計算式等引継ぎ)→データが入ったら空欄行自動挿入 月合計 累計 とこんな感じの表です。 説明は下手だし、説明不足ばかりで本当に申し訳ありません。 やってみます！
- pc_knight
- ベストアンサー率66% (52/78)
Ｅ列への自動的計算セット機能を、例えば30行まで働かせ31行以降には働かないようにするには、前回回答のＶＢＡの２行目を次のように変更します。 「If Target.Row <= 30 And Target.Row >= 2 And Target.Column < 5 Then」 ここで「Target.Row <= 30 And Target.Row >= 2 And Target.Column < 5 」の意味は、「行No.が30以下2以上且つ列が左端から数えて5列未満（＝Ａ～Ｄ列）のセルにデータがインプットされたら3行目の計算式セットの処理を行い、それ以外のセルにデータがインプットされても何もされない」という処理方法の判定機能です。 annyanさんの目的にマッチするよう「Target.Row <= 30」内の「30」の数値を上限行の値に変更して下さい。
補足
※ここに書く内容はANo.6の補足の後に入力しました。 Ａ列 Ｂ列 Ｃ列 Ｄ列 Ｅ列 1行目 日付 品目 収入 支出 合計 2行目 1/1 あめ 10 10 3行目 1/1 ガム 20 30 4行目 5行目 文具 6行目 日付 品目 収入 支出 合計 7行目 1/10 ぺん 10 10 このような表を１つのシートにつくりました。 補足した「月合計・累計」のことですが、↑の表でいう4行目Ｅ列にデータが入ったことによる自動的な行の挿入により↑の5行目が6行目になる仕組みがわかれば同じことだと気づきました。 ↓のデータをsheet1(コード)へ入力したのですが、1番最初に教えていただいた E65536まで自動的にE3の計算式が入る方法すら無効になっているのですが‥。 何が原因でしょうか？ まず書いてあるSUM以外の英単語・数字の意味がほぼわからずgoole使って単語調べたりしましたが、なんとな～くしか意味がわからず、無駄な抵抗でした。 予想ですが↓この部分が「自動的に行を増やしなさい」という命令に思ったのですが…。 If Range("a1:a65536").Find("下の表").Row - Range("e1").End(xlDown).Row <= 1 Then Rows(Range("a1:a65536").Find("下の表").Row).Select Selection.Insert Shift:=xlDown 【sheet1に入力したデータ】 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row >= 2 And Target.Column < 5 Then Cells(Target.Row, 5).FormulaR1C1 = "=SUM(R2C[-2]:RC[-2])-SUM(R2C[-1]:RC[-1])" 'E列のセルに残高計算式をセット End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count = 1 And Target.Row < Range("a1:a65536").Find("文具").Row And Target.Row >= 2 And Target.Column < 5 Then Cells(Target.Row, 5).FormulaR1C1 = "=SUM(R2C[-2]:RC[-2])-SUM(R2C[-1]:RC[-1])" 'E列のセルに残高計算式をセット If Range("a1:a65536").Find("下の表").Row - Range("e1").End(xlDown).Row <= 1 Then Rows(Range("a1:a65536").Find("下の表").Row).Select Selection.Insert Shift:=xlDown End If End If End Sub とりあえず、脳みそが溶け出そうです(*＿*) 最初はエラーが何回もでてやり直し、訂正。そうこうしているうちに エラーはでなくなりましたが、うんともすんともいわなくなりました。 pc_knightさんがどのような仕事をなさっているかわかりませんが、 こんな難しいことをさらりと教えてくださるとは、常人をはるかに超えた脳内をお持ちだと。。。尊敬しています。
- pc_knight
- ベストアンサー率66% (52/78)
ご質問の内容 「日 付 品目 収入 支出 合計 １／５ 支払 ４０ ２０←データ入力後に自動的に 【ココに自動的に行を追加したいです。】＊もちろん、前行の計算式を引き継いだ行を自動的に入るようにしたいのですが」の意味が、最終データ行のＥ列に残高を求める計算式を自動的にセットしたいということでしょうか。 もし、それでしたら以下のＶＢＡにより自動的にさせる方法でいかがでしょう？（初心者にはちっょと根気がいるかもしれませんが、次の操作をトライしてみてください。） 質問の意味がちがっていたらすみません。 ☆操作内容 （１）Ｅｘｃｅｌ画面の下端にあるシート見出し（例えば「Sheet1」）上を右クリックし （２）表示されたメニューから「コードの表示（Ｖ）」を選ぶ （３）コードウィンドウ（Ｉカーソルの点滅しているエリア）に以下のコードをコピーして貼り付け Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row >= 2 And Target.Column < 5 Then Cells(Target.Row, 5).FormulaR1C1 = "=SUM(R2C[-2]:RC[-2])-SUM(R2C[-1]:RC[-1])" 'E列のセルに残高計算式をセット End If End Sub （４）右上の「×」をクリックして、「Ｍｉｃｒｏｓｏｆｔ Ｖｉｓｕａｌ Ｂａｓｉｃ ～ ○○ ～（コード）」を閉じる。以上で終了。あとは、通常のデータ入力を実施する都度その行のＥ列に直ちに計算式が自動的にセットされます。
補足
ご回答ありがとうございました。 操作内容を詳しく書いていただきありがとうございました。 初心者の私でもとてもわかりやすくすんなりできました。 私の求めていた内容に一番近いような気がします！ 申し訳ないのですが、更に教えていただきたいことが。 質問した表の下に別な表があります。 教えていただいた方法だとE列すべてにこの作業?が適応されるようですが…。 下の表はまた別な内容のため、一定の場所(Eセル)でVBAというのでしょうか、それを止めることは可能でしょうか？ お手すきのときでかまいませんので教えていただければ幸いです。
- hana-hana3
- ベストアンサー率31% (4940/15541)
どのような表になっているのかわかりませんが、フィルでコピーすれば幾らでも行は増やせます。 最下行の日付～合計までのセルを選択して、フィルハンドルをドラッグするだけで表や式のコピーが出来ます。 http://www.eurus.dti.ne.jp/~yoneyama/Excel/nyuryoku.htm#filsuusiki
お礼
ご回答ありがとうございます。 私の説明が悪かったと思うのですが、質問に書いたような表の下にまったく別な表があります。また、使う行数が月によってかなり差があるので先に必要行をかくほすることも難しいのです。 その為、行の追加→選択したセルののドラッグ(コピー)→一部のデータ(日付等)を消して入力の作業が思うようにできません。 ご紹介いただいたURLで新しい操作方法がわかり大変参考になりました。ありがとうございます。 とてもわかりやすいサイトで助かりました。
- mu2011
- ベストアンサー率38% (1910/4994)
ご提示の表の合計列に次に数式を設定し、事前に必要分（多目）下方向にコピーしておき、日付列の入力契機に計数する方法ですが如何でしょうか。 =IF($A2<>"",SUMIF($C$2:C2,"<>")-SUMIF($D$2:D2,"<>"),"")
補足
ご回答ありがとうございます。 入力契機とはどのような意味でしょうか？ この方法は事前に使う行が決まっていないと無理でしょうか？ 私の質問が悪かったのですが、1つのシートに上下2つの表をつくっています。毎月の入力件数(つかう行数)にかなり差があるため自動的に行を増やすことができればと思い質問させていただきました。 補足として E2には「C2-D2」の式 E3には「C3+E2-D3」が入っています。 この状態でE3のセルに=IF($A2<>"",SUMIF($C$2:C2,"<>")-SUMIF($D$2:D2,"<>"),"")をさらに追加することはできるのでしょうか？ 質問ばかりですみません。 お手すきのときでかまいません再度ご回答いただければ幸いです。
- kjtyn7
- ベストアンサー率24% (300/1223)
Excelを開き、もっと活用エクセル（画面左上）をClickすると、表の作成要領が分かりやすく書いてあります。
お礼
ご回答ありがとうございます。 試みようとしたのですが、残念ながら私のExcelにはそのような機能?オプションはありませんでした。 試すことができなくて残念です。
お礼
教えていただいた方法でうまくいきました！！父親も大変喜んでいました。 最後までお付き合いいただき、本当にありがとうございました。 こちらの説明不足等にもかかわらず、親切、丁寧、わかりやすい回答をしていただき、感謝の気持ちでいっぱいです。 好奇心から始められた素人の方とはとても思えなかったです(^^*) 自分では解決出来ない問題がでましたらこちらのサイトを利用することがあるかと思います、ご縁がありましたらご回答いただければ幸いです。