ヒント:他の言語はGoogle翻訳されています。 訪問することができます English このリンクのバージョン。
ログイン
x
or
x
x
LPI ID 登録
x

or

Excelで値が変更されたときに空の行を挿入する方法

データの範囲があると仮定し、値が変更されたときにデータ間に空の行を挿入すると、次のスクリーンショットのように1つの列で連続した同じ値を区切ることができます。 この記事では、この問題を解決するためのいくつかのトリックについて説明します。

小計機能で値が変更されたときに空白の行を挿入する

ヘルパー列を使用して値が変更されたときに空の行を挿入する

VBAコードで値が変更されたときに空の行を挿入する

強力な機能により値が変化したときに特定の数の空白行を挿入します


値が急速に変化する場合、改ページ、空白行、下罫線または塗りつぶし色を挿入します

あなたが持っている場合 Kutools for Excel's 違いを見分ける 機能を使用すると、必要に応じて値が変更されたときに、改ページ、空白行、下枠、または塗りつぶしの色をすばやく挿入できます。 以下のデモをご覧ください。 クリックしてExcelをダウンロードする


小計機能で値が変更されたときに空白の行を挿入する

とともに 合計 次の手順で値が変更されたときに、データ間に空の行を挿入することができます。

1。 使用するデータ範囲を選択します。

2に設定します。 OK をクリックします。 データ > 合計 を開く 合計 ダイアログボックス、および 合計 ダイアログで、以下のオプションを実行してください:

1:値が変更されたときに基づいて空の行を挿入する列名を選択します 各変更時 セクション内;
2:選択 ワンランク上の から 関数を使用します ドロップダウンリスト;
3:小計を挿入する列名を 小計を リストボックス

3。 次に、をクリックします OK小計線が異なる製品間に挿入され、アウトライン記号が表の左側に表示されます。スクリーンショットを参照してください。

4。 そして、小計線のみを表示するには、アウトライン記号の上部にある番号2をクリックします。

5。 次に、小計範囲データを選択し、を押します。 Altキー+; 表示される行のみを選択するショートカットキー、スクリーンショットを参照してください:

6。 表示されている行のみを選択した後、 削除 キーボードのキーを押すと、すべての小計行が削除されました。

7。 次に、他のセルをクリックして戻る データ > グループ解除 > クリアアウトライン アウトラインを削除するには、スクリーンショットを参照してください:

8。 アウトラインシンボルは一度にクリアされ、値が変更されるとデータ間に空白行が挿入されていることがわかります。スクリーンショットを参照してください。

9。 最後に、必要に応じて列Aを削除することができます。


ヘルパー列を使用して値が変更されたときに空の行を挿入する

ヘルパー列を使用すると、数式を最初に挿入してから、 検索および置換 関数、最後に、変化する値の間に空の行を挿入します。 次のようにしてください:

1。 空白のセルC3に、この式を入力してください = A3 = A2、およびセルD4にこの式を入力します = A4 = A3、スクリーンショットを見る:

2。 次に、C3:D4を選択し、これらの数式を適用する範囲に塗りつぶしハンドルをドラッグすると、 真の or セルでは、スクリーンショットを参照してください:

3。 そして、 Ctrl + F キーを押して 検索および置換 ダイアログが表示されます。 間違った情報見つける 下のテキストボックス 見つけます タブをクリックして オプション ボタンをクリックしてこのダイアログを展開し、 バリュー から 覗く ドロップダウンリスト、スクリーンショットを参照してください:

4. ナビゲーションヘッダーの すべて検索 ボタンを押し、次に Ctrlキー+ A 検出結果をすべて選択するには、すべてのFALSEセルが一度に選択されています。スクリーンショットを参照してください。

6。 閉じます 検索および置換 ダイアログ、次のステップ、クリックして空白行を挿入できます ホーム > インセット > シートの行を挿入する列Aに基づいて値が変更された場合は、空の行がデータに挿入されています。スクリーンショットを参照してください。

7。 最後に、必要に応じてヘルパー列CとDを削除することができます。


VBAコードで値が変更されたときに空の行を挿入する

上記のメソッドをうんざりしている場合は、変更された値の間に空の行を挿入するのに役立ちます。

1。 押さえつける Alt + F11 キーを押して Microsoft Visual Basic for Applicationsウィンドウ.

2に設定します。 OK をクリックします。 インセット > モジュール、次のコードを モジュールウィンドウ.

VBAコード:値が変更されたときに空の行を挿入する

Sub InsertRowsAtValueChange()
'Update by Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If
Next
Application.ScreenUpdating = True
End Sub

3。 次に、 F5 このコードを実行するためのキー、プロンプトボックスが表示され、値の変更に基づいて空行を挿入する列データを選択できます。スクリーンショットを参照してください。

4。 そして、 OK列Aに基づいて値が変更されると、空の行がデータ間に挿入されます。


強力な機能により値が変化したときに特定の数の空白行を挿入します

上記の面倒な方法を試された場合は、ここで便利なツールを紹介します。 Kutools for Excel's 違いを見分ける セル値がすばやく簡単に変更されたときに、改ページ、空白行、下罫線、または塗りつぶし色を挿入するのに役立ちます。

ヒント:これを適用する 違いを見分ける 機能は、まず、あなたがダウンロードする必要があります Kutools for Excelその後、この機能をすばやく簡単に適用します。

インストールした後 Kutools for Excel、次のようにしてください:

1に設定します。 OK をクリックします。 クツール > フォーマット > 違いを見分ける、スクリーンショットを見る:

2。 の中に キー列で違いを区別する ダイアログボックスで、以下の操作を行ってください。

  • 使用するデータ範囲を選択してから、空白行を挿入するキー列を選択します。
  • それからチェック 空白行 オプションから オプション セクションを選択し、挿入する空白行の数を入力します。

3。 次に、をクリックします Ok ボタン、およびセル値が変更された場合に特定の数の空白行がデータに挿入されています。スクリーンショットを参照してください。

クリックしてExcel用のKutoolsをダウンロードし、今すぐ無料トライアル!


Kutools for Excelは、ほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート そして、以前に使用したもの; セルを暗号化する パスワード付き メーリングリストの作成 そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストや数式を簡単に編集する) レイアウトを読む (多数のセルを簡単に読んで編集できます)。 フィルター範囲に貼り付ける...
  • セル/行/列を結合 データを失うことなく; セルコンテンツの分割。 重複する行/列を結合する...重複セルの防止。 範囲の比較...
  • 重複または一意を選択します空白行を選択 (すべてのセルは空です)。 スーパー検索とファジー検索 多くのワークブックで。 ランダム選択
  • 完全コピー 式の参照を変更せずに複数のセル。 参照を自動作成 複数のシートに 箇条書きを挿入、チェックボックスなど
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷 セルのコンテンツとコメント間の変換...
  • スーパーフィルター (保存して他のシートにフィルタ方式を適用する)。 高度な並べ替え 月/週/日、頻度などによる。 特殊フィルター 太字、斜体で...
  • ワークブックとワークシートを組み合わせる; キー列に基づいて表をマージします。 データを複数のシートに分割する; xls、xlsx、およびPDFのバッチ変換...
  • 300を超える強力な機能。 Office / Excel 2007-2019および365をサポートします。 すべての言語をサポートします。 企業または組織に簡単に展開できます。 フル機能の30日間の無料トライアル。
KTEタブ201905

OfficeタブはOfficeにタブ付きインターフェイスを提供し、作業をより簡単にします

  • Word、Excel、PowerPointでタブ付き編集と読み取りを有効にする、出版社、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を50%向上させ、毎日数百回のマウスクリックを削減します。
オフィシタブ底
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Hassan · 1 years ago
    Hello,
    Is very usefull in case i need to insert 1 row, but if i need to insert 145 rows in every time the data change in spwcific column, how can i do it??
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Hassan,
      To insert multiple blank rows when value changes in a specific column, you should apply the following VBA code:

      Note: In the below code, you should change the number 99 to your need, for example, when you insert 145 blank rows, you should change the number 99 to 144. Please try it, hope it can help you!

      Sub InsertRowsAtValueChange()
      Dim Rng As Range
      Dim WorkRng As Range
      On Error Resume Next
      xTitleId = "KutoolsforExcel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      Application.ScreenUpdating = False
      For i = WorkRng.Rows.Count To 2 Step -1
      If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
      Range(WorkRng.Cells(i, 1).EntireRow, WorkRng.Cells(i + 99, 1).EntireRow).Insert
      End If
      Next
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        DCalk · 11 months ago
        Very helpful. The code that skyyang shows above worked perfectly. Just make sure that the data doesn't already have spaces in it.

        I don't understand VBA, but I believe if you wanted to add more rows underneath data that already had the spacing, there should be a way to ignore spaces.

        Could a line be added to ignore or skip over blank lines? That might make this code more universal and repeatable if needed. Also a delete function that is similar to this may be useful so undo isn't necessary.
  • To post as a guest, your comment is unpublished.
    Suzi · 1 years ago
    RE: insert blank rows when value changes with vba code
    Is there a way that I can save the Range & not have to pick it every time I run it?
  • To post as a guest, your comment is unpublished.
    Eric F · 1 years ago
    The VBA code worked 1st time and did exactly what I was trying to do. Thank you so much!
  • To post as a guest, your comment is unpublished.
    Steve · 1 years ago
    I've been using my own solution for some time.
    1. Insert a helper column into Column A
    2. In A2, type "if(B2=B1,A1,A1+1)"
    3. Copy that formula down to the last row
    4. Copy all the populated cells in column A and Paste Special (Values) over them
    5. Copy all the cells again and paste them into column A in the first unpopulated cell (e.g. if you have 104 rows of data plus a header row you would paste into cell A106)
    6. Click on Data and Remove Duplicates (only on the cells you just pasted in Step 5; not on all rows)
    7. Sort all of Column A
    8. Delete Column A

    Seems like a lot of steps but only takes a few seconds.
  • To post as a guest, your comment is unpublished.
    Madhur · 2 years ago
    [quote name="Hudson"]Hi all thank you!!

    its awesome , can you guys also let me how to insert 2 rows when the value changes in VBA or through excel.[/quote]

    Please let me know how to insert more than 1 row.
  • To post as a guest, your comment is unpublished.
    Davis · 2 years ago
    Hi there,

    These are almost useful! The first method doesn't work for me because when I follow the steps explicitly, the the data that I delete in the subtotal panes deletes the entire columns that I've sorted.

    In the second method when I get to the step where I insert sheet rows, the rows are inserted ABOVE the FALSE cells which breaks up the data, but the last selection of every group is then added to the group below.

    Any advice???
  • To post as a guest, your comment is unpublished.
    Hudson · 3 years ago
    Hi all thank you!!

    its awesome , can you guys also let me how to insert 2 rows when the value changes in VBA or through excel.
  • To post as a guest, your comment is unpublished.
    Rafael · 3 years ago
    Where in the code would I need to modify to include more than one row, I need to add 10 after each break...

    Thanks
  • To post as a guest, your comment is unpublished.
    Ivo Tamm · 4 years ago
    Thank you very much for describing multiple solutions for the problem.
    I used the Visual Basic one and changed
    [quote]WorkRng.Cells(i, 1).EntireRow.Insert[/quote] with
    [quote]With WorkRng.Cells(i, 1).EntireRow.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlMedium
    End With[/quote]
    This will draw lines between rows with changing values instead of empty lines.
  • To post as a guest, your comment is unpublished.
    Saul · 4 years ago
    Thank you so much, looked everywhere and this was the only solution that worked for me that was also in plain english.

    All the other Excel tutorial websites didn't break it down step-by-step they way you did, thank you very much.

    cheers : )
  • To post as a guest, your comment is unpublished.
    Abner · 4 years ago
    I have a column with the numbers 5,4,3,2 and 1 (a LOT of them) and I wanna separate the Fives from the others, like:

    5
    5
    5

    4
    3
    2
    1

    5
    5
    5
    5
    5

    4
    3
    2
    1

    The numbers are in desc order and there are always a four below a five,
    so I tried:

    Sub InsertRowsAtValueChange()
    'Update 20140716
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Application.ScreenUpdating = False
    For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value = 5 And WorkRng.Cells(i - 1, 1).Value = 4 Then
    WorkRng.Cells(i, 1).EntireRow.Insert
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

    But it didn't work. Can you help me?