散布図で簡易的な図面を作成する(1)
今回は、Excelの散布図を使って簡易的な図面を作成する方法を考えていきます。
■通常の図面作成の特徴
パソコンで図面を描くときは、通常、CADソフトを使います。
CADでは、各点の座標を、具体的な数値で指定することで、対象物を正確な縮尺で拡大・縮小して表現することができます。
一般的に、CADソフトは非常に高価というデメリットはありますが、そこに目をつむれば、図面を作成する手段として、CADを使わないという選択肢はほぼないでしょう。
しかし、CADも万能ではなく、座標を数値で指定するがゆえに、融通が利かないという面もあります。
どういうことか、鋼材を例にとって説明します。
鋼材の中でもポピュラーなH形鋼には、様々なサイズがありますが、
その寸法は、H, B, tw, tf, rの文字で決められており、
H形鋼のカタログには、H, B, tw, tf, rの寸法が示されています。
図面を作るときに、寸法をH, B, tw, tf, rの変数で指定できれば、
H形鋼のサイズが変わったときの作図が非常に楽になるのですが、
通常の2DのCADでは、それができないのです。
そのため、あらかじめすべてのサイズのH形鋼を作図しておき、毎回、使用するサイズの断面をコピー&ペーストするという作業が出てきてしまいます。
■散布図を使用した図面作成の特徴
Excelの散布図を使い、各点の座標を、具体的な数値ではなく、数式として作成しておけば、図面も自動で変化してくれるため、H形鋼のサイズが変わっても、いちいち作図をやり直す必要がありません。
鋼材を例に説明しましたが、工業製品であれば、同様に寸法が文字で規定されていることが多いので、このテクニックを使用すると効率的になる場面は、意外と多いのではないかと思います。
ただし、こちらももちろん万能ではなく、以下の短所があります。
・最初に数式を使って座標を定義するのに時間がかかる
・正確な縮尺、プロポーションで作図することが困難
長所と短所を総合的に考えると、工業製品を使用する場合の納まり検討に向いているテクニックだと思います。
累乗、添字の入力方法
技術系の仕事をしていると、Excelで計算書を作る機会がよくあります。
人に見せることを考えると、説明用に計算式を示したくなるのですが、今回は、累乗や添字つき文字の入力についてお話しします。
----------------------------------------------------------------------------------------
①累乗/添字表示したい文字を選択する。
②「Ctrl」+「1」
③「セルの書式設定」ウィンドウが開くので、累乗表示の場合は「上付き(E)」に、添字表示の場合は「下付き(B)」にチェックをつけて「OK」をクリック。
----------------------------------------------------------------------------------------
やり方としては、これだけなのですが、累乗や添字の数が多くなってくると、③の作業が地味に面倒になります。
そこで、少しでも労力を減らすために、ショートカットを使うことをおすすめします。
ポイントは、③の画面にある「上付き(E)」、「下付き(B)」の文字。
マウスでいちいち動かさなくても、次のキーを押すことでチェックをつけることができるのです。
「Alt」+「E」：「上付き(E)」にチェックをつける
「Alt」+「B」：「下付き(B)」にチェックをつける
行・列を追加するショートカット
「Shift」＋「Space」：選択したセルを含む行を選択する
「Ctrl」＋「Space」：選択したセルを含む列を選択する
「Ctrl」＋「Shift」＋「＋」：行または列を選択した状態で、行/列を挿入する
複数セルに一括入力する「Ctrl」+「Enter」
データを入力するときに、地味だけど役立つショートカットを紹介します。
セルにデータを入力して「Enter」を押すと、選択中のセルは1つ下へ移動します。
一方で、セルにデータを入力した後、「Ctrl」＋「Enter」を押すと、選択中のセルは変わらず、元の位置のままとなります。
地味ですが、データを入力したあと、そのデータをコピーしたい場合には、選択位置に戻す必要がない分、効率的になります。
複数のセルを選択した状態で「Ctrl」＋「Enter」を押した場合は、アクティブセルと同じ内容が、選択中のセルすべてにコピーされます。
その時、アクティブセルの内容が関数で、相対参照となっている場合は、そらが反映されます。
オートカルクを使って最大値、最小値、平均値などをすばやく知る
最大値、最小値、平均値を求める場合、MAX関数、MIN関数、AVERAGE関数を使うのが普通ですが、その場で値を確認するだけなら、オートカルク機能を使ってもっと手軽にできます。
オートカルクとは、最大値、最小値、平均値などのよく使う統計値を、ステータスバーに表示する機能です。
対象とするデータ範囲を選択すれば、右下に統計値が表示されます。
表示する数値は、ユーザー設定で選択することができ、「平均」「データの個数」「数値の個数」「最小値」「最大値」「合計」が選べます。
必要な値が表示されていない場合は、ステータスバーを右クリックし、必要な項目にチェックを入れましょう。
なお、データの選択には、下記の方法が役立つので参考にしてください。
画面を見ながらの打合せ中に、「最大はいくつ？」などとと聞かれた際は、オートカルクですばやく確認するのが良いでしょう。
隣のセルのデータをコピーするショートカット
データを手で入力する時に、隣のセルと同じ値を入力する機会は意外と多いものです。
例えば、日付や単位を入力する場合です。
例①
例②
そんな時に役立つのが、次のショートカットです。
「Ctrl」+「D」：上のセルのデータをコピー
「Ctrl」+「R」：左のセルのデータをコピー
上の例①で、「年」列に1つ上のセルと同じ「2021」を入力するとすると、
通常の方法でコピペする場合は、
「↑」+「Ctrl」+「C」+「↓」+「Ctrl」+「V」
の6回、
直接データを入力する場合は、
「2」+「0」+「2」+「1」
の4回キーを押す必要がありますが、
ショートカットを使えば2回で済みます。
所要時間の差は大したことないかも知れませんが、煩わしさやストレスは、意外と軽減されると思います。
セルの移動に役立つショートカット
膨大なデータを扱うときに困ること
実験データなどをExcelで扱う場合、データが数万行に及ぶことも珍しくありません。
データが膨大だと、スクロールバーが短くなり、マウスでスクロールバーをつかんでスクロールするのが、とても煩わしくなります。
セルの移動を劇的に効率化
選択中のセルの上下左右に連続してデータが入力されている場合、その範囲の上端・下端・左端・右端に移動するショートカットがあります。
「Ctrl」+「→」/「←」/「↑」/「↓」
次の例では、選択中のセル「C4」に連続して、右は「D4」まで、左は「A4」まで、上は「C1」まで、右は「C11」まで、データが連続して入力されています。
ショートカットを使えば、これらのセルまで、一瞬で移動できるのです。
先頭と末尾に移動できるだけでは、あまり使う機会がないのでは、と思うかも知れませんが、そんなことはありません。
データが膨大になればなるほど、グラフ化したり、統計処理をする必要が出てきます。
あるいは、csvファイルに出力されたデータを、Excelファイルにコピーして分析することが必要になります。
グラフ化や統計処理、データのコピーに必要なのは、データの範囲を指定することであり、その時に、データの先頭と末尾に簡単にジャンプできるということは、非常に有効なのです。
「Shift」を追加して範囲選択
上記のショートカットに、「Shift」を追加すると、選択中のセルから、連続データの先頭/末尾のセルまでの範囲を選択することができます。
「Ctrl」+「Shift」+「→」/「←」/「↑」/「↓」
上図の状態から、C列の「C1:C11」を選択したい場合、まず
「Ctrl」+「↑」
で「C1」に移動し、さらに
「Ctrl」+「Shift」+「↓」
とすることで完了します。
すべてキー操作で済むため、データがもっと多い場合でも、マウスでスクロールバーを移動させる操作も必要ありません。
INDEX関数、MATCH関数を利用したデータの検索・抽出
■VLOOKUP関数を利用する方法
前の記事で、VLOOKUP関数を利用したデータの検索・抽出方法を紹介しました。
再掲になりますが、 VLOOKUP関数には、
(1)検索したいデータがある列は、表の一番左になくてはならない
(2)抽出したいデータがある列が表の何列目かを数えなくてはならない
(3)抽出したいデータがある列を列番号で指定するため、表の列を追加・削除すると、適切なデータを抽出できなくなる
といった難点があります。
これらの難点を解消できるのが、INDEX関数とMATCH関数を組み合わせたやり方です。
■INDEX関数
INDEX関数は、ある範囲と、その中の位置を指定することで、そのセルに入っている内容を参照する関数です。
一般的には、次のように記述して使用します。
----------------------------------------------------------------------------------------
=INDEX(範囲, 行番号, 列番号)
----------------------------------------------------------------------------------------
例：下の表の場合、「行番号=2」、「列番号=3」とすると、「h」が返ってきます。
「範囲」に、1列または1行の範囲を指定する場合は、簡略化して記述することができます。
----------------------------------------------------------------------------------------
=INDEX(1列または1行の範囲, 先頭のセルからの位置)
----------------------------------------------------------------------------------------
以下では、この簡略化した INDEX関数の記述方法を使用します。
■MATCH関数
MATCH関数は、検索値が、指定した範囲のどこにあるかを数字で返す関数です。
----------------------------------------------------------------------------------------
=MATCH(検索値, 範囲, 照合の種類)
----------------------------------------------------------------------------------------
「照合の種類」には通常、「0」（完全一致）を指定します。
例：下の表の場合、「検索値=c」とすると、「3」が返ってきます。
■INDEX関数とMATCH関数を利用する方法
INDEX関数の「先頭のセルからの位置」を、MATCH関数を使って求めることで、データの検索・抽出が可能になります。
VLOOKUP関数を利用したデータの検索・抽出
■データベースから必要なデータを検索し、抽出する
例えば、データベースの情報を元に、都道府県名を入力すると、その都道府県の人口が表示されるようにしたいとします。
■VLOOKUP関数を使用する方法
最も一般的な方法は、VLOOKUP関数を使用するものです。
----------------------------------------------------------------------------------------
=VLOOKUP（検索値, 範囲, 列番号, [検索の型]）
----------------------------------------------------------------------------------------
今回の場合、
検索値： 都道府県名を入力するH2セル
範囲： 表の範囲B2:E8
列番号： 3（抽出する人口が表の中の何列目にあるか）
検索の型：0（完全一致のみ検索）
となります。
「検索の型」は、検索値が表の中に見つからなかった場合の対応を指定する引数で、省略可能ですが、意図しないデータを抽出しないためにも、深く考えずに0としておくことをおすすめします。
■VLOOKUP関数のデメリット
さて、このVLOOKUP関数は、検索・抽出の手法として、広く用いられているようですが、以下のようなデメリットがあります。
(1)検索したいデータがある列は、表の一番左になくてはならない
上の例では、「都道府県名」→「県庁所在地」の検索は可能ですが、「県庁所在地」→「都道府県名」の検索はできません。
(2)抽出したいデータがある列が表の何列目かを数えなくてはならない
上の例では、表が4列しかないのですぐ数えられますが、画面を右にスクロールしなければならないほどたくさんの列がある表だとすると、数えるのは相当な手間です。
(3)抽出したいデータがある列を列番号で指定するため、表の列を追加・削除すると、適切なデータを抽出できなくなる
例えば、県庁所在地のデータが不要になってC列を削除してしまうと、面積の列が3列目となるため、面積のデータが抽出されてしまいます。
こうした制約がない検索・抽出の方法として、INDEX関数、MATCH関数を利用した方法を次回紹介します。
データバーで簡易棒グラフを作成
今回は、スケジュール管理に役立つテクニックを紹介します。
下図のように、各タスクの進捗状況を記入して管理する場合、進捗状況が数字だけではなく、視覚的にわかるようにできれば便利です。
そこで、まずは棒グラフを追加してみることにします。
下図は、C列とE列を参照して横棒グラフを挿入し、縦軸を（上下）反転させたものです。
これでも、数字だけの場合と比べると、進捗状況の把握がだいぶしやすくなりましたが、棒グラフを見ただけでは、各タスクの項目（B列）や期限（D列）がわからないといった難点があります。
この原因は、元となる表と、作成した棒グラフが、それぞれ独立した図表になってしまっていることが原因です。
こうした場合に役立つのが、条件付き書式のデータバーという機能です。
データバーを使うと、表の中にグラフが組み込まれたような表現が可能となります。
やり方
①棒グラフのデータ範囲を選択（今回の場合は、進捗を表すE3:E10）
②「条件付き書式」＞「データバー」から、好みのスタイルを選択
