データを管理するのに知っておくと便利、知らないと大変なこととは
Excelでのデータ管理については以下の別記事で説明していますが、その中で知っておくと役に立つ内容や、逆に知らないと(後で気づくと)面倒なこと(やり直し、根本的見直し)になるだろう点をめとめました。
当ページでは「番外」ということで、Excelの細かい点であったり、Excelに限った話ではなかったりですとか、あるいは知っておかないと不便になるかもしれないことも紹介しています。
こういった点を含めてデータやファイルを管理することを意識したときに知っておいたほうが良いだろう点について改めて説明しています。
「表の下に関数があると行を追加できない」を回避する方法
関数を使っている方にとっては割と「あるある」ではないかと思うのが次のような表です。
たとえば、この表の場合、「2行のデータ」と「合計行のデータ」の3行構成になっています。
そして、「合計行のデータ」には、SUM関数を埋め込んでいます。
このような表の構成にしているときにシンドさを感じる作業として「新しいデータの追加」があります。
理屈から言えば、3行目と4行目の間に空列を挿入してデータを入力するだけの話ですが、合計行があるが故、新規のデータを入力するたびに「空列の挿入作業」が必要になります。
新しいデータは「表の下に追加」したい。
しかし「合計行」が邪魔している。
そのために空の行を挿入後、データ入力という工程をしますが、こうするとSUM関数で設定しているセルの参照範囲が変わり、再度手直しが発生しますね。
ちなみに上記の表の場合ですが、行を挿入しても、合計行にあるSUM関数の範囲は(自動で)広がりませんのでご注意ください(関数の範囲は、追加する行の位置によって、変化します)。
表の上に「合計行を移動」してしまう
それではどのような方法でこの空き列挿入と関数の引数変更を防げるでしょうか?
ここでは以下の方法をご紹介します。
単純に「合計行そのものを最上部に移動する」方法です。
このようにしておくと、点線の部分が空き状態となりますので、ここに上に入力している行と同じようにデータを入力できます。
関数のセル範囲で予め最大行数を取得しておく
そして、次にSUM関数内で設定しているセルの範囲でも少し変更を加えておきます。
下の図をご覧になればわかりますが、このようにSUMするセルの範囲を最大値にしておきます。
上記でいうと、3行目の入力から9999行目の間に入力されたセルが合計の対象となるわけです。
言い方を変えると、10000行目に入力された値はSUMの対象になりません。
実際の表として何行目までを必要とするかを予め設定しおくことで、データ入力の都度、SUM関数の範囲を変える必要から開放されるのですね。
基本的には上記のように明細データが入力されている表に「合計自体をここには含めない」のがデータ管理的には望ましいと思います。
データの粒度(目線)が異なるデータのため、規則性が崩れてしまうのですね。合計は別シートなどで行うことで対処できるならば、管理がしやすくなります。
この点については以下の記事も参考になると思います。
表示形式を意識するときは、「数式バー」を見ながら行う
Excelは実際に入力した値を見た目上、わかりやすい形式に変えてくれる機能があります。それはセルの書式設定です。
これはこれでありがたき感謝すべき機能なのです・・・が、時としてデータ管理においては、気づいてないと後の作業で手直しが発生することがありますね。
例えば、セルに何も設定せずに「1/1」、「1-1」と入力すると「1月1日」と表示され、年(西暦、和暦)の表示が省略されます。
このため、期間を指定した集計時などで年まで意識しておかないといけないことがあります。
「セルに月日を入力する」はよくある入力だと思いますが、初期の設定では上記のように「年」が表示されない点にはご注意です。
実際にあった例ですが、年が明けた1月に昨年末(たとえば、12月)のデータを入力したときに、「月と日」だけ入力すると、西暦(年)の部分は、年が明けた西暦になり、昨年の西暦になりません。この場合は、セルに「年と月と日(年月日)」を入力する必要があります。
例えば、Excelで2021年1月に「2020年12月分(昨年)のデータ」を入力しようとします。このとき、セルに「12月1日」とだけ入力すると、西暦は(Excelの判断で)「2021年」となります。
つまり、セルに「月と日だけ」入力した時、入力者は「2020年12月1日」と入力したつもりでも、Excel側では「2021年12月1日」という扱いになってしまうという意味です。
他にも関数や数式が入力されているセルにはその(設定)結果が表示されているため、一見しただけでは分かりません。
もし表形式のデータならば、項目名などで判断することは可能かもしれません。しかし、確実なのは「数式バー」を見ることです。
数式バーには、セルに入力されている値が表示されます。たとえば上記の例では、セルA1に表示されている値は「1月1日」です。しかし、セルに実際に入力されている値は「2021/1/1」ですね。
こうすることでセルにどんな値が入力されているのか分かるため、データ管理の助けになります。
なお、日付の「表示値と数式バーの値」で比較してみました。参考にしてください。
表示値はマチマチですが、実際に入力している値は同じです。
この辺はExcel操作で勘違いや間違いの原因にもなると思いますのでご注意くださいませ。
画像管理は不向きかも!?
単刀直入に言うと、画像管理は、Excelではなく他のソフトに任せた方がよいかもしれません。というのもExcelは画像を管理できる機能に乏しいからです。
マクロなどを使うことである程度は管理を強化できる部分はありますが、強いてExcelで管理するならば次のような形がいいかもしれません。
上記は画像のファイル名を入力した一覧表になっていますが、このように「画像のファイル名」を管理するということです。
このような使い方の場合、「どこどこのフォルダに○○というファイルがある」のような使い方になります。
ただしこの場合、「Excelで管理しているファイル名」と「実際の画像」はリンクされていません(同期が取れない)。
このため、もし「実際の画像」を削除してもExcel上で管理している「ファイル名(入力値)」はそのままです。
つまりはExcel側で一元管理ができず、画像ファイルを移動、ファイル名変更、削除、追加などを行ったらExcel側もそれに合わせて変更する必要が出てきてしまいます(その逆も同様です)。
なおExcelシートに画像そのものを貼り付け表示できますが、この画像を使いたいときに今度は取り出すのが面倒になったりしますし、画像枚数によっては、動作が重たくなります。
この場合でも、実際の画像は別に存在しつづけています。Excelにも貼り付けていますから、ある意味で、画像のコピーをExcelで持っているとも言えるとなります。
したがいまして、やはり「Excelで管理している画像、ファイル名」と「実際の画像」がリンクされてないということを意識して管理しておく必要があります。
画像に限らず、PDFやCSVなどのファイル一覧をExcelで管理するということもあると思います。
その場合、先に説明したように、格納先であるファイルを移動されたりするとExcelで管理している表から検知することは実質不可能になります。
そのため私がこのようなファイルを管理するときは、VBAでファイル一覧を見に行き表を更新させて監視するようにしています。が、それでも面倒ですね。
通し番号は過去を含めて重複禁止にしたほうがよい
※ここで説明する「通し番号」とは単に件数を数えるためだけでない意味を含めています。
データを管理するとき、一般的にデータに通し番号を振ることがあると思いますし、無い場合はデータの中に「商品コード」や「社員ID」などがあることが多いと思います。
通常、多くのデータの中から「あるデータ」を指すときに、それがどのデータのことを言っているのかを相手(人だけでなくExcelにも)に分からせるように「目印」を用意します。
リアルな世界でいうと職場に「鈴木さん」が2人いるときに、ある職場では「男性の鈴木さん」、「女性の鈴木さん」といったような言い方をします。
もし同性ならば下の名前や愛称などで呼んだりして、どの鈴木さんを指しているのかを必ずはっきりさせますね。
これをデータ的にみると「唯一(ユニーク)な値、ID(個別番号)」、簡単に言うと「通し番号」です。
これが無い場合、
「表の真ん中くらいにあるAAA株式会社の中の1行下のデータだけど・・・」とか
「表の一番下から3つ上のデータにある・・・」となり
ミスや漏れの可能性がでたり、関数などを使う際にも困ることが出てきます。
まれに下記のように関数を使って通し番号を作成しているファイルを見かけますが、この場合は単純に「連続した番号を振る」ならば問題ないですが、この方法だと、行全体を削除するだけでなく、データを上下行を入れ替えても番号は変わらないので、上記の見方で言えば好ましくありません。
同様に過去に使用したことのある番号は、「これから100%絶対に表に出ることはない」ならば使い回ししても問題ないですが、「今はないけどもしかしたら、将来使うことがあるかも」ならば、使い回しせずその番号は残し、新規で番号を発行した方が無難です。
ファイルの名づけは面倒でも規則正しく
これはExcel以外にも当てはまりますが、大事なファイルを使ってて「万が一」に備えてファイルをコピーしたり、別名で保存したりする場合があると思います。
こんなとき、下記のようなファイル達を見かけることがあります。
これでは後で見たときに混乱してしまう要因になるため、ファイル名にはルールを持たせておくと管理が容易になります。
分かりやすい例がファイル名に「日付を含ませる」です。
こうすることで「いつのファイル」かがわかるようになります。
桁数を合わせておくと並び替えした時に見やすいのもポイントですね。
またファイル名に「最新」を入れる場合も見ますが、もしこの最新というファイルの内容を変更した場合、「最新1」にするのか、あるいはそのまま「上書き」するのかで、以降少し考え方を変える必要が出てきます。
前者(最新1)にする場合:「番号なし」も存在するため、数が増えてきたときに「番号なし」を最新と見てしまう場合がある
後者(上書き)にする場合:例えばメールの添付でこのデータを探すときに、「いつの最新」か判断がつかなくなる
等が考えられます。特に定期的に使い、ファイルが分割されて使われていく場合などは、意識して名前(ルール)付けをするとよいです。