Excel シート上のデータを並べ替えたいことがあります。
例えば、下のExcel 現金出納帳には日付が順不同で入力してあります。
（9/3 の次に 9/1 が来たり。空白の行もあります。）
現金出納帳への入力は毎日行うのが理想ですが、どうしても数日間たまってしまうことはあるかと。
その場合、領収書類を日付順に並べてから入力すれば良いのかも知れませんが、それが面倒なこともあるでしょう。
とりあえず領収書類を見ながら日付は順不同に入力しても、後から Excel 現金出納帳への入力を日付順に並び替えることもできます。
今日は、Excel で VBA を使って、また Spreadsheet で GAS（Google Apps Script）を使って、現金出納帳のデータを日付順に並び替える方法について説明します。
1、現金出納帳
現金出納帳の項目は、日付、勘定科目、摘要、収入金額、支払金額、差引残高とします。
差引残高の列は H列で、例えばセル「H13」であれば「=H12+F13-G13」と入力してあります。
H列の他の行も同様です。
ごちゃっとなった領収書類を入力した前提で、日付は順不同になっています。
空白の行（入力を飛ばしてしまった行）もあるものとします。
2、Excel VBA でデータを日付順に並び変える方法
さて、まずは Excel VBA（マクロ）でデータを日付順に並び替えてみたいと思います。
VBA コード
VBE（エディタ）に書き込む VBA コードは以下のようになります。
プロシージャ名は Sort としています。
|
1 2 3 4 5 6 7 8 9 |
Sub Sort() Dim Last_Row As Double '日付の入力がある最後の行' Last_Row = Cells(Rows.Count, 3).End(xlUp).Row Range(Cells(7, 3), Cells(Last_Row, 8)).Sort Key1:=Range("C7"), Order1:=xlAscending, Header:=xlNo End Sub |
このコードを実行すると、日付順にデータが並び替えられて空白も無くなります。
コード説明
|
1 |
Dim Last_Row As Double |
で変数 Last_Row を Double 型として定義しています。
|
1 |
Last_Row = Cells(Rows.Count, 3).End(xlUp).Row |
で日付の列である3列目（C列）で、入力のある一番最後の行番号を変数 Last_Row に入れ込んでいることになります。
もう少し分解すると
Rows.Count というのはシートの行数になります。
シートの一番下の行番号なので、これは恐ろしく大きい数値（Excel 2016 で 1048576）になります。
Cells(Rows.Count, 3) でシートの一番下の行の3列目のセルを意味するわけですね。
Cells(Rows.Count, 3).End(xlUp)は End プロパティを使っています。
End プロパティは以下のように記述して使います。
今回、Range オブジェクトは Cells(Rows.Count, 3)、引数は xlUp としています。
これで、シートの一番下の行の3列目のセル（Cells(Rows.Count, 3)）を選択した状態で、 Ctrl キーを押しながら上（↑）キーを押した際に選択されるセルを意味します。
シートの一番下の行から「Ctrl + ↑」を押すと何か入力があるセルまで飛びます。つまり、3列目（C列）で入力がある一番下のセルまで飛ぶわけですね。そのセルが Cells(Rows.Count, 3) に該当します。
そして
Cells(Rows.Count, 3).End(xlUp).Row
と最後に Row を付けることで、上記で指定したセルの行番号を意味します。
次に
|
1 |
Range(Cells(7, 3), Cells(Last_Row, 8)).Sort Key1:=Range("C7"), Order1:=xlAscending, Header:=xlNo |
の部分について説明します。
この部分では
Cells(7, 3)と Cells(Last_Row, 8)で囲まれる範囲をセル「C7」を基準として昇順で並び替えています。
Sort メソッドを使っています。
Key1 は並び替えの基準となるセルを指定します。今回は日付の列の一番上のセルのセル「C7」を指定しています。
Order1 並び替えの順番を表しており、xlAscending とすると昇順を意味します。
Header は一番上の行を見出しとするか否かを指定しており、xlNo とすることで見出しとしないことを意味します。
この Sort メソッドで並び替えれば、空白の行も埋めてくれます。また、H列に入れ込んである数式の行番号も並び替えによって自動的に入れ替わります。
3、GAS（Google Apps Script） でデータを日付順に並び変える方法
次に GAS を使う場合について説明します。
GAS は Spreadsheet 版の VBA みたいなものです。
Excel シートの内容を直接 Spreadsheet にインポートすることができます（数式やセルの色も含めて）。
インポートした現金出納帳↓
なお、インポートの仕方については以下の記事を参考にして下さい。
| スプレッドシートは、Excel（エクセル）の Web版みたいなものです。Google アカウントさえあれば、Google ドライブの中で無料で使うことができます。Google ドライブへは Google のトップページからアクセスすることができます↓「新規」から「Google スプレッドシート」を選択します。するとこのような↓シートが開きます。パッと見 Excelとそっくりですよね。このスプレッドシートですが、使い勝手も Excel と似ています。さて、今日は Excel（エクセル）で作った現金出納帳をスプレッドシートに取り込んで共有して使う方法を紹介しま... Excel（エクセル）現金出納帳を Google スプレッドシートに取り込んで共有して使う... - My タックスノロジー |
GAS を使う場合は、Excel VBA の場合と比べてよりオブジェクトを意識した書き方をしなければいけません。
GAS コード
シートの中の「ツール」タブの「スクリプトエディタ」を開き以下の GAS コードを書き込みます。
関数名は Sort としています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
function Sort() { var Sheet; var Last_Row; var Last_Col; Sheet = SpreadsheetApp.getActiveSheet(); Last_Row = Sheet.getLastRow(); Last_Col = Sheet.getLastColumn(); Sheet.getRange(7, 3, Last_Row, Last_Col).sort(3); } |
実行すると Excel VBA の場合と同じく
コード説明
GAS では変数を定義するときは、すべて冒頭に var と記述します。
|
1 2 3 |
var Sheet; var Last_Row; var Last_Col; |
上記の部分では、それぞれ
・Spreadsheet 自体を意味する変数「Sheet」
・データの入力がある一番下の行番号を入れ込む変数「Last_Row」
・データの入力がある一番右の列番号を入れ込む変数「Last_Col」
として定義しています。
|
1 |
Sheet = SpreadsheetApp.getActiveSheet(); |
の部分では SpreadsheetApp.getActiveSheet() によって Spreadsheet のアクティブなシートをオブジェクトとして変数「Sheet」に入れ込んでいます。
|
1 |
Last_Row = Sheet.getLastRow(); |
の部分では、アクティブなシート上でデータの入力がある一番下の行番号を変数 Last_Row に入れ込んでいます。
|
1 |
Last_Col = Sheet.getLastColumn(); |
の部分では、アクティブなシート上でデータの入力がある一番右の行番号を変数 Last_Col に入れ込んでいます。
|
1 |
Sheet.getRange(7, 3, Last_Row, Last_Col).sort(3); |
の部分ではデータの並び替えを行っています。
アクティブなシートのセル（7,3）（「C7」のこと）とセル（Last_Row, Last_Col）で囲まれる範囲を C列を基準にして並び替えています。
getRange メソッドは次のように記述して使います。
これで、セル（a1,a2）とセル（b1,b2）で囲まれる範囲を意味します。
sort メソッドは次のように記述して使います。
これで指定した列番号の列を基準として昇順に並び替えを行います。
sort(3)とすると、3列目（日付の列）を基準として並び替えることを意味しています。
4、経理業務で使えるExcel現金出納帳プレゼント！！
2022年3月29日に追記致します！
上記でお話しした現金出納帳にマクロを組み込んでさらに使いやすくしたものをプレゼント致します！
▼▼以下よりダウンロードできますので、ぜひ有効活用して下さい▼▼
現金出納帳ダウンロード
なお、使い方につきましては以下の動画▼の中で解説しておりますので参考にして下さい。
5、まとめ
Excel と Spreadsheet は似ています。
Excel の方が一般的で操作もしやすいですが、
Spreadsheet の方はネット環境があればいつでもアクセスできて複数人でも同時に編集できるという利点があります。
両者を上手に使い分けると良いでしょう。
今回は、Excel、Spreadsheet 両方で同じ現金出納帳を例にあげてデータを日付順に並び替えるプログラミングコードを作ってみました。
今回の例に限らず、会計事務所や経理職では日付を基準とした並び替えはけっこう使えるので参考にして頂ければと思います。
編集後記
昨日（9/3）午後一に、顧問先訪問して決算の内容を報告。
仕事の後は歯科で歯の掃除をしに行った際に、丁寧な歯磨き指導を受けました。
歯を大事にしたか否かの結果は歳を取ってから出る、、、、大事にしなければいけません！