ゼロはじ　「第27回　Excel(エクセル)でデータの並び替え」

　始めに、図27-1の農林水産省が指定している多面活動の報告様式1-7を見てください。この表では、I列に数式が記載されているため、日付順にデータを入力していけば、I列の白いセルに設定された数式が正常に計算をして、間違いなく残高を表示してくれます。ですから、基本的に初心者は、日付を間違わないようにデータを順に入力することさえ心がければ、問題なく報告書は出来上がります。

　ただ、気を付けてデータを入力していても、やはり記載漏れをすることはありますし、場合によっては、日付は関係なくデータを入力しておいて、後で、順番を並び替えることで、日付順にしたい場合もあると思います。特に、私などは、確認しているデータから順に入れておいて、後で並び変えて日付順に整理したいタイプの性格で、初めからデータを順番通りに入力することができません。

　そんな場合、もし、データの入力漏れがあったとしても、修正が少ない場合は、図27-2のように、一行挿入して、数式があるところは、数式を記述するか、同じ数式をコピーして、漏れたデータについて新たに書き込むことで処理していけばなんとかなります。

　ただ、一行の挿入程度ではなんともならない場合もあります。そんな時に便利なのが「並べ替え」の機能です。「ソート」と言われています。数式の並べ替えについては少し難しいので、後で説明することにして、先ずは数式の入っているI列を削除した図27-3を使って、並び替えの基本を学びます。

10.並べ替え(ソート)

10.1.並び替え(ソート)の基本

　図27-3では、B列の日付が順序よく並んでいませんね。4/6の前年度持越のデータは4/1より下なので、本来は5行目にあるべきですし、5/5の日当は4/21の購入・リースの下、10行目に記載されているべきですし、5/17の日当は5/15の下ですから、12行目に記載されているべきです。そこで、B4～B15のセルの値の順番を並べ替えましょう。ただ、B列を並べ替えた場合には、当然C列～L列も連動して順番が並び替えられないといけません。そんな場合には、図27-4のように、セルB4で左ボタンを押したままマウスをスライドしてセルのL15までを範囲選択してグレー色に変えてください。この範囲を選択したままで、メニューで「データ」を選択すると、左上図のように、リボンに「並び替え」が表示されますので、これを左クリックします。すると、右下図の並び替えの条件設定の画面が表示されます。左から「優先されるキー」、「並べ替えのキー」、「順序」が表示されていますので、優先されるキーは「列B」、並べ替えのキーは「セルの値」、順序は「古い順」と設定してください。設定が終われば、「OK」をクリックして、実行します。

　結果は図27-5です。B列の日付が古い順に並び替えられていることが分かりますね。他にも設定条件は選択できますが、ここで覚えておくべきことは、「優先されるキー」とはどの列を対象とするかということであり、「順序」は並べ替えの規則を指しているということです。図27-6にいくつかの例を示しておきます。

10.2.数式がある場合の並び替え(ソート)

　図27-7の上図はI列に数式が入ったままで、日付順に並んでいないデータです。この場合に、10.1で学んだことを元に、単純に並び替えをしてみます。B4～L15までを範囲選択して、グレー色に変えて、優先されるキーは「列B」、並べ替えのキーは「セルの値」、順序は「古い順」と設定しました。すると、図27-7下図となります。

　うまく並べ替えができているところもあるのですが、駄目なところも出てきました。I4とI5は並び替えられたのですが、数式まで並び変わってしまい、下図のI4には本来「=G4-H4」が入るところ、「IF((OFFSET(I4,-1,0)+G4-H4)<>0,OFFSET(I4,-1,0)+G4-H4,0)」が入り、下図のI5は「=IF((OFFSET(I5,-1,0)+G5-H5)<>0,OFFSET(I5,-1,0)+G5-H5,0)」であるべきところ、「=G5-H5」になってしまいました。さらに、OFFSET関数で「OFFSET(I4,-1,0)」はI4の-1下のセル、0右のセルはI3ですから、「残高(円)」という文字列が入っていて、文字+G4-H4は計算できないので、「#VALUE!」と表示されています。

　こうなると、農林水産省の様式をこのまま使うことはできません。これを打開するためには、式を全部同じにして、かつ文字列の場合は0という数値にしてしまうことです。文字列を0として読み取るためには、ファイル→オプション→詳細設定で、「計算方式を変更する」に✓入れます。これを設定しているのが図27-8です。

　なお、ここでOFFSET関数のままでも良いのですが、この関数は難しいので、非常に単純に、前の行のI列の値に収入のG列を足して、支出のH列を差し引くということで十分理解できるので、図27-9のように、I4には「=I3+G4-H4」として、以下はこの式をコピーします。

　図27-10がその結果です。問題なく並べ替えができています。

※二十八番札所は成相山成相寺です。「なりあいでら」と読みます。京都は宮津市、日本三景の一つ「天の橋立」を眼下に望むお寺です。巡礼と観光が両方できて最高です。本尊は身代わり観音、美人観音として名高い聖観世音菩薩です。「成り合い」とは、出来上がって整うことを言うそうですが、それが転じて、願いが成って、整うということで、成り合う寺が「成相寺」となったそうです。