作者別: it-yobi

Excelで選択している行のデータを縦向きに抽出する方法について解説します。

実は、マクロを使用せずに簡単な数式のみで実現できます。
順番に手順を解説していきます。

1. 先頭行のデータを縦方向に抽出

まずは、先頭行（No.1）のデータをB列のセルに抽出していきます。

抽出する際に、横方向のデータを縦方向に転置する必要があります。
転置するには、TRANSPOSE関数を活用します。

抽出する先頭のセルB3に以下の数式を入力します。

=TRANSPOSE(D3:M3)
// D3:M3：先頭行（No.1）のデータ

数式を入力して確定することで、以下のように先頭行のデータを縦方向に変換して抽出することができます。

2. 抽出する行番号を数値にする

現状の数式は、抽出するデータが「D3:M3」と参照になっています。

=TRANSPOSE(D3:M3)

最終的には選択している行のデータを抽出する必要があるため、１行目、２行目と行番号を変更するだけで抽出対象のデータが変更できるように修正します。

こちらでは、OFFSET関数を活用して行番号で指定できるように修正します。
OFFSET関数とは、指定した範囲から指定した行数、列数移動した位置の範囲を取得することができる関数です。
OFFSET関数を活用して１行移動した先がセル「D3:M3」になるように、以下のような数式にします。
※No.1のデータが１行移動した先になるようにします。

=TRANSPOSE(OFFSET(D2:M2,1,0))
// D2:M2：移動する前の範囲
// 1：1行下へ移動
// 0：0列右へ移動

数式を入力して確定することで、以下のように抽出することができます。

OFFSET関数で指定した行数を以下のように修正するだけで、抽出するデータの行を変更することができます。

=TRANSPOSE(OFFSET(D2:M2,2,0))
// 2：2行下へ移動

3. 行番号を自動取得する

最後に、OFFSET関数で指定している行数を自動で取得する仕組みを実現します。
実は、この仕組みも数式のみで実現できます。

選択しているセルの行番号を取得するには、CELL関数を活用します。
CELL関数に、以下のように「row」と指定することで、数式を更新する度、選択しているセルの行番号を取得することができます。

=CELL("row")

数式は、[数式]タブの中の[再計算実行]を選択、もしくは、F9キーを押すことで更新できます。

毎回、[数式]タブから[再計算実行]を選択するのは大変ですので、F9キーを活用した方がよいです。

では、先ほどの数式のOFFSET関数に指定した行数をCELL関数に置き換えます。
OFFSET関数の行数が１の時に、先頭行（３行目）のデータが抽出されるため、CELL関数で取得した行番号から２を引く必要があります。
そのため、以下のような数式になります。

=TRANSPOSE(OFFSET(D2:M2,CELL("row")-2,0))
// CELL("row")-2：選択しているセルの行番号から2を引いた数値を取得

数式を入力して確定することで完成です。

いずれかのセルを選択して、F9キーを押して数式を更新するだけで、以下のように選択しているセルの行のデータを抽出することができます。

▼サンプルファイル▼

「No」や「ID」の項目などで重複のない番号を自動入力する際に、以下のようにROW関数を活用して入力することがあります。

しかし、ROW関数を活用して入力していると、表を並べ替えた時に番号が更新されてしまい、番号と他の項目との相関性がなくなってしまいます。

ということで今回は、相関性を保ったまま重複のない番号を自動入力する仕組みの実現方法について解説します。
※こちらで開発したファイルは記事の最後にて配布しています。

1. 開発準備

今回は、以下の表の「Name」の項目に値が入力されると同時に、「No」の項目に重複のない番号を入力するという仕組みを実現します。

そのように、特定のシートで特定のセルが編集された時に処理を自動で実行するには、「シートモジュール」の「イベントプロシージャ」を活用します。
シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、該当するシートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、該当するシートのセルに値を入力すると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャは、該当するシート上のいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

ただ、今回使用するイベントプロシージャは、セルに値を入力すると同時に処理が実行されるものになります。
そのため、右上のリストから「Change」を選択します。

表示された「Worksheet_Change」というプロシージャを活用します。
「Worksheet_SelectionChange」というプロシージャに関しては削除して問題ないです。

2. コードの記述

以下のコードを記述します。

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Row >= 3 And _
        Target.Column = 3 Then
        
        If Cells(Target.Row, "B").Value = "" Then
            Cells(Target.Row, "B").Value = _
                WorksheetFunction.Max(Columns("B")) + 1
        End If
        
    End If
    
End Sub

では、コードについて解説していきます。

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Row >= 3 And _
        Target.Column = 3 Then
        
        '省略
        
    End If
    
End Sub

「Worksheet_Change」というプロシージャの引数の「Target」に、編集されたセルの情報が渡されるため、そのセルの情報から、表の対象のセルが編集されたかどうかを確認しています。
こちらでは、Targetの行番号が３以上、尚且つ、列番号が３の時、要するに「Name」の項目が編集されたかどうかを判定しています。

この条件を満たした場合に、Ifの中の処理を実行します。

        If Cells(Target.Row, "B").Value = "" Then
            Cells(Target.Row, "B").Value = _
                WorksheetFunction.Max(Columns("B")) + 1
        End If

先ほどの条件を満たしたときに、編集されたセルと同じ行のB列、即ち「No」の項目が空かどうかを確認しています。
「No」の項目が空の場合に、その項目に、B列に入力されている数値の最大値に１を加えた数値を入力しています。

3. 完成

以上の内容で実現できます。

「Name」の項目に値を入力した時、尚且つ、その行の「No」の項目が空の場合に、今までの番号の最大値に１を加えた数値が自動で入力されます。

また、数式ではなく直接数値が入力されているため、並べ替えた際に他の項目との相関性がなくなってしまうことはありません。

▼サンプルファイル▼