今日を乗り切るExcel研究所

Excel に働かされていませんか

Excel のスカスカの表で列を選択したい

今回は、シートの最下行に飛ばされずに表の内部だけの列選択をする方法を調べます。

また、選択ジャンプが表の境界セルで止まるよう設定するマクロを作成します。

f:id:shego:20190308222519p:plain

シートの果て

Excel シートには何行まであるのか知っていますか。

答えは1048576行です。

なぜ知っているのかというと、しょっちゅうそこまで飛ばされているからです。

何かのタイミングでタイプミスをすると、シートの最果ての底の行に飛ばされることがあってイラッとします。

よくあるのは、表作成序盤で見出し行と項番列ぐらいしか埋まっていない、ほとんどスカスカの表を編集しているときです。 しかも何百行もあって1画面に入りきらず終わりが見えないときは特にそうです。

同一の数式を一括入力するときなど列を選択状態にしたいのですが、うかつに Ctrl キーを押しながら範囲選択すると、 表の最終行ではなく、シートの最終行まで選択してしまいます。

キー操作ではなく、マウスドラッグでスクロール範囲選択してもいいのですが、それはそれで加速の勢いが余っては通り過ぎ、何回も行ったり来たりして、またイライラすることになります。

スカスカの表でも、表内の列を一発で選択する方法はないのでしょうか。

表範囲の列選択をするには

調べても、表内の任意の列を一発で選択するという方法は無いようです。 まったく、気の利かない話です。

ただ、Excel は表がある領域をまったく知らないというわけではないようです。 たとえばスカスカでも見出し行などのセルを選択して Ctrl+A を押すと、表全体が選択されます。

このような、今使える機能を活用して、表内の列を選択する手順がないか探ってみます。

Ctrl+Shift+End を使う

表の右下角のセルまでなら、Ctrl+Shift+End を使えばいつでも選択できます。

これを利用して列選択をしてみましょう。

【手順例】

  1. 表内の選択したい列の先頭セルを選択します
    • 見出しは含めません
  2. Ctrl+Shift+End を押します。
    • 表の右下角までが選択状態になります
  3. Shift+(左矢印) を押して右側から選択範囲を狭めていき、1列選択とします

これで表内の列が選択状態になります。

長い表では下までスクロールされて見えなくなっているかもしれませんが、上の方では最初の先頭セルがアクティブなままになっています。

この後このまま、たとえば数式入力なら Ctrl+Enter で一括入力するか、あるいはあらかじめコピーした数式を一括貼り付けすることができます。

f:id:shego:20190308222650g:plain

いつもの癖で無意識に矢印キーを押してしまうと、不意に範囲選択が解除されてしまうので気を付けましょう。

たとえばCtrl+Enter で一括入力をするつもりが Enter してしまうことがあります。 そのとき、あわてて上矢印()を押すのをぐっと我慢して、落ち着いて Shift+Enter すると1個上の元のセルに戻れます。 セルの位置を戻さなくてもその場で一括入力をやり直すだけでもOKです。 ちょっと練習すれば慣れるでしょう。

厳密なことを言えば、Ctrl+EndCtrl+Shift+End で選択できるのは、表の右下角ではなく、「シートの使用セル範囲の右下角」です。 シート上に表が一つしかない状況では、両者は同じセルを指すことになります。

Ctrl+A を使う

ノートパソコンのキーボードだと End キーは押しにくいかもしれません。

今度は Ctrl+A から列選択をつくる手順を考えます。

表内のセルを選択して Ctrl+A を押すと、表全体の領域が全選択状態になります。

全選択状態の表範囲内でセルカーソルを移動するには、 矢印キーの代わりに TAB (右方向)と Shfit+TAB (左方向)や、Enter (下方向)と Shift+Enter (上方向)を使います。

これであちこち移動してみると分かりますが、選択範囲の境界を突き抜けるとセルカーソルは反対側からひょっこり出てきます。

これを応用して、列選択をしてみましよう。

【手順例】

  1. 選択したい列の見出しセルを選択します
  2. Ctrl+A で表全体を選択状態にします
  3. そのまま Shift+Enter を押して最下行へ移動します
    • 一つ左の列の最下セルにセルカーソル出てきます
  4. 右矢印(→)キーで一つ右に移動します
    • 選択状態が解除され、目的の列の最下行のセルが選択されている状態になります
  5. Ctrl+Shift+ で列を範囲選択します
  6. Shift+ を1回押して見出しセルだけを選択解除しておきます
  7. そのまま Enter を押してで列の先頭にセルカーソルを移動します

これで表内の列が選択状態で、先頭のセルがアクティブな状態になります。

文章だと分かりにくいですが、結局何をやっているのかは、画面に収まる小さめの表で練習してみると理解できると思います。 原理が分かれば、行方向の操作も簡単です。

f:id:shego:20190308222747g:plain

うかつに矢印キーを押してしまうと、やり直しになってしまうのは End のときと同じです。

また、列内の途中に値のあるセルがあると、ステップ5が途中止まったりしますので、何回か Ctrl+Shift+ をする必要があるかもしれません。

また、表が途中の空の行で途切れていると Ctrl+A はそこまでしか選択しませんのでこれも注意が必要です。

テーブルを使う

いっそのこと、表の管理を Excel にお任せするという方法もあります。

表を「テーブル」に変換すると、Excel が何かと面倒を見てくれるようになります。

テーブルというのは、あの、デキる人が使っている色がシマシマになるやつです。

表をテーブルにすると、 見た目がきれいになるだけではなく、セルの移動・選択操作も楽になります。

【手順例】

  1. テーブルにしたい表内にあるセル(見出しなど)を選択します
  2. Ctrl+T を押します
    • 「先頭行をテーブルの見出しとして使用する」にチェックを入れ、OKを押します。
  3. 表のセル範囲が自動的にテーブルに変換されます

行の配色がシマシマになり、見出し行にはフィルタのプルダウンが付きます。

まずはテーブル内で Ctrl+矢印CtrlShift+矢印 使ってあちこち移動・選択して見てください。 境界のセルが空でも突き抜けずに止まるはずです。

次に、Ctrl+Space で列選択をしてみてください。 テーブル内の列のだけが選択され、しかも見出しを含みません。 見出しも含めたいときには、Ctrl+Space を2度押しします。 行選択( Shift+Space )や、全選択( Ctrl+A )も同様です。

数式を入れたいなら、列を選択する必要さえありません。 任意のセルに数式を入れると、その列の全てのセルに展開されます。 数式内の参照に列見出し名が使われることにも注目です(構造化参照と言います)。

f:id:shego:20190308222844g:plain

作成する表の中身がいわゆる「データ」であるときには、テーブルを積極的に使うべきです。

表を「テーブルに変換する」というのは、それが「構造をもったデータ」であることを Excel に「申請」するということです。 いったんテーブルとなったデータなら、 Excel は様々なサポートをしてくれます。 テーブル名や構造化参照、ピボットテーブルやリレーションを使った集計、クエリ(PowerQuery)やデータモデルを使ったデータ分析などにより、データが自由に使いまわせるようになり、効率化だけでなく仕事の幅も広がり、お給料も上がります(たぶん)。

便利になる一方、融通が利かなくなるという面もあり、たとえばセルの結合はできなくなります。

表がデータではなく、何かの一覧や管理表のような手作業で管理するようなものなら、かえって不自由を感じるかもしれません。

作業後にテーブルを解除して元の表に戻すには、テープルを右クリックして「テーブル」メニューから「範囲に変換」を実行します。 テープルではなくただのセル範囲なりますが、シマシマまでは戻してくれません。

見えない値を使う

自前で表の範囲を定義することはできないのでしょうか。

Ctrl+矢印 で止まるのは、空白セルと値のあるセルの境界にあるセルです。

あらかじめ表の全てのセルに何らかの値を入力しておけば、表範囲の辺のセルが境界となり、セルカーソルを Ctrl+矢印 でジャンプしても、境界セルで止まるはずです。

数値データだけの表ならあらかじめ最初に 0 で全てのセルを埋めておけばいいでしょう。

一方、テキスト主体の表などでは、見かけ上空白セルにしておきたいです。 空白セルでないにもかかわらず、空白セルに見せかけるような、「見えない値」はないものかと調べると、以下のような入力が考えられます。

  • スペース(空白文字)
  • シングルクオート(')のみ
  • 長さ0の文字列("")

シングルクオートや長さ0の文字列は扱いが難しいので、今回、単純に「スペース」を使ってみることにします。

【手順例】

  1. 表の一番左上のセルを選択します
  2. Ctrl+A で表全体を選択状態にします
  3. そのうち空白セルのみを選択状態にします
    • マウス操作: 「ホーム」タブ⇒「編集」/「検索と選択」メニュー⇒「ジャンプ」ダイアログ⇒「セル選択」ボタン⇒「空白セル」オプション⇒「OK」ボタン
    • アクセスキー: Ctrl+GAlt+SKEnter
  4. アクティブなセルに半角スペース文字を入力して Ctrl+Enter を押します
  5. 矢印キーを押して選択状態を解除します。

見かけ上は分かりませんが、セルの内容を確認するとすべてのセルにスペースが1個入力された状態になっています。

この状態で Ctrl+矢印 を使い、表内をあちこち移動してみてください。 表の外に飛び出さずに境界のセルで止まるはずです。

f:id:shego:20190308223006g:plain

どうでしょうか。

このあと、セルにデータを入力するときも、スペースの存在を気にせずに入力していけます。 通常はこれで問題ないはずですが、数式セルで表内参照するような表では、計算に支障が出る事があります。

スペースを入力するにしても、できれば表全体ではなく、最小限のセルにしておきたいです。

表の下辺と右辺の境界セルにだけスペースを設定すれば、影響を最小限にしつつ、同様の効果が得られるはずです。

これもちょっと使い勝手を試してみたいところですが、表の枠にだけ空白文字を入力するのははやや面倒です。

ここはマクロにやってもらいましょう。ついでに、スペースではなく「長さ0の文字列」を使ってみます。

 



長さ0文字列で境界セルを作成するマクロ

セル範囲の上下左右の辺となるセルに見えない値を設定するとき、これを 境界セル と呼ぶことにします。

指定範囲で境界セルを自動作成するマクロを作成してみました。

本マクロVBAを実行すると、表や選択範囲の上下左右の辺となるセルに「長さ0の文字列」を設定します。 これで、表の内部から Ctrl+矢印 でジャンプしても表の境界で一旦止まり、シートの最終行までいきなり飛ばされるこがなくなるはずです。

2種類のマクロを用意しました。

境界セルの設定_選択範囲 」マクロは、指定した表や範囲に対して境界セルを設定します。 「範囲選択なし」でこのマクロを実行すると、現在のセルを含む表範囲(Ctrl+Aで選択される範囲)を対象とし、その境界セルを設定します。 「範囲選択あり」でこのマクロを実行すると、そのセル範囲で境界セルが設定されます。

境界セルの設定_表示範囲 」マクロは、現在のウィンドウの可視範囲か印刷範囲の境界に対して境界セルを設定します。 「印刷範囲」を改ページプレビューで設定している場合、その範囲(白い有効範囲)の境界に境界セルが設定されます。 印刷範囲設定を使用していない場合、現在のウィンドウの可視範囲に境界セルを設定します。

もう一つ、「 境界セルの解除 」マクロは、全ての境界セル(長さ0文字列)を削除し、もとに戻します。

Option Explicit

Sub 境界セルの設定_選択範囲()
    If TypeName(Selection) <> "Range" Then Beep: Exit Sub
       
    Dim region As Range
    If Selection.Cells.CountLarge = 1 Then
        Set region = Selection.Cells.CurrentRegion
    Else
        Set region = getTotalRegion(Selection)
    End If
    If region.CountLarge = 1 Or hasEntireRange(region) Then Beep: Exit Sub
    
    Application.ScreenUpdating = False
    clearBorder ActiveSheet.UsedRange
    buildBorder region
    region.Select
    Application.ScreenUpdating = True
End Sub

Sub 境界セルの設定_表示範囲()
    If TypeName(ActiveSheet) <> "Worksheet" Then Beep: Exit Sub
    
    Dim region As Range
    If ActiveSheet.PageSetup.PrintArea <> "" Then
        Set region = ActiveSheet.Range(ActiveSheet.PageSetup.PrintArea)
    Else
        With ActiveWindow.VisibleRange
            Set region = .Resize(.Rows.Count - 1, .Columns.Count - 1)
        End With
    End If
    Application.ScreenUpdating = False
    clearBorder ActiveSheet.UsedRange
    buildBorder region
    region.Select
    Application.ScreenUpdating = True
End Sub

Sub 境界セルの解除()
    clearBorder ActiveSheet.UsedRange
End Sub

Private Sub buildBorder(region As Range)
    Dim borders As New Collection
    Dim border As Range
    With region
        borders.Add .Rows(.Rows.Count)
        borders.Add .Columns(.Columns.Count)
        If 1 < .Row Then borders.Add .Rows(1)
        If 1 < .Column Then borders.Add .Columns(1)
        Set border = .Cells(.Rows.Count, .Columns.Count)
    End With
    
    If IsEmpty(border) Then
        setZeroLengthString border
    End If
    
    Dim area As Range
    For Each border In borders
        On Error Resume Next
        For Each area In border.SpecialCells(xlCellTypeBlanks).Areas
            setZeroLengthString area
        Next
        On Error GoTo 0
    Next
End Sub

Private Sub clearBorder(rng As Range)
    clearZeroLengthString rng
End Sub

Private Function getTotalRegion(ByVal rng As Range) As Range
    Set getTotalRegion = rng
    Dim area As Range
    For Each area In rng.Areas
        Set getTotalRegion = Range(getTotalRegion, area)
    Next
End Function

Private Function hasEntireRange(rng As Range) As Boolean
    Const MAX_COLS = 16384
    Const MAX_ROWS = 1048576
    Dim area As Range
    For Each area In rng.Areas
        If MAX_COLS <= area.Columns.Count Or MAX_ROWS <= area.Rows.Count Then
            hasEntireRange = True
            Exit Function
        End If
    Next
    hasEntireRange = False
End Function

Private Sub setZeroLengthString(rng As Range)
    With rng
        .Formula = "="""""
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

Private Sub clearZeroLengthString(rng As Range)
    rng.Formula = rng.Formula
End Sub

【使い方】

  1. 新規標準モジュールに上記VBAプログラムをコピー&ペーストします
  2. 境界セルを設定したい範囲を選択してマクロを実行します
    • 表範囲:
      表内部(見出し行など)にあるセルを選択状態にし、「 境界セルの設定_選択範囲 」マクロを実行します
    • 任意のセル範囲:
      セル範囲を範囲選択して、「 境界セルの設定_選択範囲」マクロを実行します
    • 画面表示範囲:
      印刷範囲が設定されていないシートで「 境界セルの設定_表示範囲 」を実行します。範囲選択は不要です
    • 印刷範囲:
      改ページプレビューで印刷範囲を設定されているシートで「 境界セルの設定_表示範囲 」を実行します。範囲選択は不要です
  3. 指定のセル範囲の境界セルに長さ0文字列が設定されます

マクロを実行しても見かけ上は何も変わりませんが、境界セルには「長さ0の文字列」が設定されています。

確かに、表内を Ctrl+矢印キー で移動すると境界セルで一時停止します。 列を選択してもシートの果てには飛ばされません。

どうでしょうか。

普段改ページプレビューで使用範囲を決めている人は、お試しでも一回これを使ってみてください。 なかなかの新感覚が味わえます。

境界セルにもし既存データが入力済みだったとしても上書きや変更はされません。 また境界セルには、これまで通り値を入力(上書き)して問題ありません。

指定範囲にA列や1行目が含まれていた場合、そこには境界セルを設定しません。(すでに境界なので)

行か列がシート全選択になっていた場合は何もしません。

境界セルが不要になったら、「 境界セルの解除 」マクロを実行してください。

このマクロはシート上の「長さ0の文字列」を全て削除するので、それに起因したよくあるトラブルの解消にも使えます。 (それはまた別の回の記事で話題にします)

境界セル設定マクロを複数回実行したときにも、前回の境界セルは全て消去され、新たな境界セルが設定し直されます。

【注意】 境界セルは見えなくても印刷ページ範囲の領域に影響を与えるので、印刷時に意図しない余分な紙を排出する可能性があります。印刷前には境界セルを全解除してください。

【免責】 本マクロは既存データを壊さないように注意していますが、万一不具合や誤操作で損失や損害が発生しても、当ブログでは責任を負いかねますであらかじめご了承ください。

まとめ

今回、スカスカ表の列を選択する方法を色々試してみました。

やはり、スカスカの表でも、表内に限定した移動・選択できると、作業がだいぶはかどりそうな気がします。 ただ、実際は慣れるまでしばらくやって見ないとわかりません。

もし、これがワンアクションでできるとだいぶうれしいです。 Excel さんには、スカスカでも表の範囲を認識できているのだから、もう一息頑張って欲しいところです。

本記事の検証は Windows 10 上の Excel 2013 を使用しました。