美人秘書のための  『会社来訪履歴管理』


匠への技

 「匠への技」第4弾の見所は、会社名入力から日付の新しい順に5履歴表示する点、リストから取引先の一覧を表示するなどなど..........  新しく6種類の関数を使いました。

今回も配列数式を用いていますが、配列数式や配列関数は集計や分析するのに非常に効果のある便利な関数なのですが、データボリュームに起因してExcelの動作に悪影響を及ぼし動作効率の悪い定義となることも認識してた方が良いでしょう。

 基本画面とアウトライン
 2つのシートで構成しています。
面会日に名刺、またはアポ記録からデータ登録を行います。シート「来訪リスト」の内容を参照ください。

今回もマクロを組んでいます。シート「統計」を見てください。来訪データ入力の名称の赤いボタンがあります。そのボタンをクリックするとExcelのフォーム機能をコールし、カード形式でデータ登録を行えるようにしました。単純にメニューバー「データ」→「フォーム」を選択すればすむ話ですが.....   ^o^ゞ。 フォームを利用した入力は、連続した見出しセルに対して入力対象となりますので、シート「来訪リスト」のワークセルとなるH列以降を対象外とするためG列をデータなしの非表示としています。
シート「統計」は、会社名を入力するとシート「来訪リスト」から来訪日の新しい順で5明細プロットし表示します。また、シート「来訪リスト」から来訪した会社の一覧をプロットし表示しています。

−おことわり−
 シート:来訪リストの範囲は30明細打ち止めとしています。最終行を関数で判断する式をINDIRECT関数で求めている箇所「シナリオ8」がありますので参考に関数式を修正して完全なブックを完成させてください。



<画面デザイン>

○シート名:統計


○シート名:来訪リスト



     ※後続の章「シナリオに基づく関数の説明」への戻り先です。

  シナリオ1   シナリオ2   シナリオ3   シナリオ4   シナリオ5   シナリオ6   シナリオ7   シナリオ8



 使用する関数の種類    この色は新たに用いた関数です。
  1. IF(論理式,真の条件,偽の条件)   
  2. COUNTIF(範囲,検索条件) 
  3. INDIRECT(参照文字列,参照形式)
  4. COUNT(数値1,数値2,...)
  5. ROW(配列)
  6. SMALL(範囲,順位)
  7. INDEX(範囲,行番号,列番号,領域番号)
  8. MATCH(検査値,検査範囲,照合の型
  9. MONTH(シリアル値) 
・・・・・4回目登場
・・・・・3回目登場
・・・・・2回目登場
・・・・・初回
・・・・・初回      (配列数式)
・・・・・初回
・・・・・初回
・・・・・2回目登場
・・・・・初回

※SMALL関数において配列数式を用いています。

配列数式とは?
1つまたは複数の値セットに基づいて複数の計算を実行し、1つまたは複数の結果を返す数式。配列式は中括弧{}で囲まれ、Ctrl + Shift キーを押しながら Enter キー押下により配列数式が定義されます。


 表作成のシナリオ
  1. シート:来訪リスト》 来訪データの登録  (項目「来訪日」〜「電話番号」まで)
  2. シート:来訪リスト》 訪問月を求める
  3. シート:来訪リスト》 来訪先の重複チェック  (シート「統計」の「会社一覧」表示に関連)
  4. シート:来訪リスト》 会社識別   (シート「統計」の「会社一覧」表示に関連)
  5. シート:統計》 会社名から来訪回数(人数)を求める
  6. シート:統計》 同様に最新の来訪日より5履歴遡り表示する
  7. シート:統計》 交流のある会社数を表示する
  8. シート:統計》 その会社名を表示する


 シナリオに基づく関数解説
■ シナリオ 「1」                    》 基本画面とアウトラインへJUMP

@ 来訪データを登録する。
 シート:統計の来訪データ入力ボタンをクリックすることによりフォーム入力を行うことができます。
作成したマクロは次の通りでボタンとリンクさせています。

Sub datain()

  Sheets("来訪リスト").Select
  Range("A1").Select
  ActiveSheet.ShowDataForm
  Sheets("統計").Select
  Range("A6").Select

End Sub


■ シナリオ 「2」                    》 基本画面とアウトラインへJUMP
A 訪問月を求める。  シート:来訪リスト セルH2の関数書式

  =IF(A2<>"",MONTH(A2),"")  IF関数MONTH関数の組み合わせです。まず、 IF(A2<>"" は来訪日が無記入の場合を判断しています。これは、データ入力されているか否かの判断です。 真の場合は、 MONTH(A2) 来訪日からMONTH関数により月を抽出し代入します。偽の場合は "" 無記入を代入します。
セルH31までフィルドラッグします。


■ シナリオ 「3」                    》 基本画面とアウトラインへJUMP
B 項目「会社名」を元にリスト内の重複している2件目以降のデータに"●"を代入する。  
シート:来訪リスト セルI2の関数書式

 =IF(COUNTIF($B$2:B2,B2)>1,"●","")  IF関数COUNTIF関数の組み合わせです。一見して理解できますか?
それでは解説します。  =IF(COUNTIF($B$2:B2,B2)>1 が論理式です。COUNTIF関数は、条件に一致したデータの件数を求める関数です。 COUNTIF($B$2:B2,B2) 範囲、絶対セル$B$2(コピーしても変化しないセル定義) : からセル B2 を範囲とし、セル ,B2 と同一会社名の件数を求めています。範囲内から ,B2 と同一の会社名の件数を求め  >1 1件以上か否かを判断しています。要するに1件以上存在する場合は範囲内に同じ会社名の行(重複)が存在することになります。真の場合は "●" を代入し、偽の場合は "" 無記入が代入されます。この式は同じ行(セルB2内)で判断していますので間違いなく偽の式になり無記入が代入されます。この関数式をI31まで複写します。
セルI3の関数式を考察しましょう。複写した結果 =IF(COUNTIF($B$2:B3,B3)>1,"●","") 太字のところが変化します。件数を求める範囲が $B$2:B3 となります。セルB2からB3までを範囲としてセルB3と同じ会社名の件数を求めています。例えばB2と同じ会社名であれば2件となりますので >1 の判断からすると真の条件となり "●" が代入されます。以降、COUNTIF関数で求められる件数が1件(重複していない条件)であれば "" 無記入が代入され、1件以上(重複している条件)であれば "●" が代入され重複データがこの関数式により判明します。


■ シナリオ 「4」                   》 基本画面とアウトラインへJUMP
C シナリオ3の結果から無記入セルの場合カウントアップし●の場合は全行のセルと同一の値を代入する。
 シート:来訪リスト セルJ2の関数式とセルJ3以降の関数式とは別定義となりますので留意してください。ここで求める値は新しく出現する会社名の順位です。セルJ2関数式を見ましょう。
  =IF(B2<>"",0+(I2=""),"")  IF関数論理式の和の組み合わせです。ちなみに先頭行では会社名が重複することはないので単純に1を代入してもよい。ここでは、関数式に拘ります。
  IF(B2<>"" がセルB2会社名が入力されているか否かの判断です。入力されている場合は、0+(I2="") 論理式の和を求めます。論理式 (I2="") は、TRUEの時 1 、FALSEの時 0 を返します。 0+(I2="") は (I2="") が真の時に 0+1 となり、偽の時に 0+0 となります。 結果は0+1=1です。
B2<>"" セルB2にデータが入力されていない時は、偽の条件の "" が代入されます。

D 次にセルJ3関数式です。
  =IF(B3<>"",J2+(I3=""),"")  違いがわかりますか? セルJ3以降は前のセルの値 J2 に論理式の値を加算します。
ようするにTRUE(1)の場合は前のセルに+1カウントアップされ、FALSE(0)の場合は前のセルの値と同じ値が代入されます。値は画面デザインで確認してください。この書式をJ31までフィルドラッグします。


ここから、シート:統計のシナリオです。配列数式など結構複雑な式が待っていますので挫折しないでください。


■ シナリオ 「5」                   》 基本画面とアウトラインへJUMP
E セルA6に入力された会社名から来訪リスト内の該当する会社名の件数を求める。   
シート:統計  セルB6の関数式です。
  =IF(A6<>"",COUNTIF(来訪リスト!B2:B31,A6),"")  IF関数COUNTIF関数の組み合わせです。
 =IF(A6<>"" は会社名が入力されているか否かの判断です。無記入の場合は、偽の式の "" 無記入が代入されます。真の場合は COUNTIF(来訪リスト!B2:B31,A6) シート:来訪リストの範囲セル B2:B31 会社名の範囲から条件 A6 入力した会社名と一致する件数を表示します。
    (セルB31の31とは、シート:来訪リストの最終データの位置です。)


■ シナリオ 「6」                   》 基本画面とアウトラインへJUMP
F セルA6の会社名からその会社名の来訪履歴を降順(最新の訪問日順)に5明細表示する。
 シート:統計  セルC6の関数書式
  {=IF($B$6>=ROW(A1),SMALL(IF(来訪リスト!$B$2:$B$31=$A$6,来訪リスト!$A$2:$A$31),$B$6+6-ROW()),"")}
 {} 配列数式です。IF関数ROW関数SMALL関数の組み合わせです。
IF関数の論理式ですが、 =IF($B$6>=ROW(A1) の $B$6 はシナリオ5で求めた存在件数、ROW(A1) とは行を返す関数で、ここではそのセルで求める順番を意味しています。ROW関数()内に A1 を指しているので当然 1 が返されます。  (例えばセルC10へ =ROW() の関数式を定義すると 10 と返されます。)
これは、存在件数までの残カウンターとして利用しています。次のセル行に複写したした場合を考えていただければ理解できると思います。セルB6は絶対セルの定義ですから変化はありません。ROWの()内のA1はA2に変化します。つまり2が返されます。
すなわち、真の条件で定義している SMALL関数 を存在行分、配列検索を実行するための条件文が $B$6>=ROW(A1) 論理式となります。意味は、存在行数(セルB6)以上になったか否かの判断で、存在行を超えた場合(偽の時)は "" 無記入を代入し、超えていない場合(真の時)は SMALL(IF(来訪リスト!$B$2:$B$31=$A$6,来訪リスト!$A$2:$A$31),$B$6+6-ROW()) が実行されます。
真の条件の時の SMALL(IF(来訪リスト!$B$2:$B$31=$A$6,来訪リスト!$A$2:$A$31),$B$6+6-ROW()) 関数式ですが、範囲に IF(来訪リスト!$B$2:$B$31=$A$6,来訪リスト!$A$2:$A$31) IF関数を定義しています。論理式 来訪リスト!$B$2:$B$31=$A$6 は、シート:来訪リストの配列範囲 来訪リスト!$B$2:$B$31 と入力した会社名 $A$6 の配列検索を行い、真の時は、来訪リスト!$A$2:$A$31 論理式で一致した同じ配列の位置から日付を引用します。
しかし、SMALL関数を組み合わせています。SMALL関数でのパラメータ「順位」の定義を見てみましょう。 $B$6+6-ROW() ???  数値を代入すると具体的になります。 例えば入力した会社名の訪問回数が3件だったとします。セルB6は訪問回数です。セルB6の値3件に+6の結果9になります。 ROW() で求められる値は C6の位置なので 6 です。つまり、9-6の答えですので 3 が求められます。要するにここでは、降順ですので日付の最後の順位(セルB6で求めた件数で最後とは直近の訪問日となる)がそのまま順位として引用されます。次の行に書式を複写したことを考えればより具体的です。セルC7へ書式を複写すると $B$6+6-ROW()) なります。見た目は複写前と同じです。しかし複写したセル位置が違います。C7になりますので ROW() ROW関数で返される値は 7 です。 9-7 の式ですから答えは 2 になります。セルC6で求めた順位の次の降順の番号となります。当然次のセルC8へ書式を複写すれば 9-8=1 となり、その次以降は 0 が返され偽の条件の "" 無記入が代入されることになります。
5名細分なのでセルC10までフィルドラッグします。


■ シナリオ 「7」                   》 基本画面とアウトラインへJUMP
G 交流ある会社数を求める。
シート:統計  セルE2の関数式です。
  =COUNTIF(来訪リスト!I2:I15,"")  シート:来訪リストのI列は、シナリオ3で説明したとおり、最初に検索された会社名以降存在する場合に ● を代入する式でしたので、無記入の件数を数えれば会社数がわかります。
説明は、省略します。わからないときは、Q&Aへ投稿下さい。


■ シナリオ 「8」                   》 基本画面とアウトラインへJUMP
H つき合いのある会社一覧を表示する。
シート:統計  セルE5の関数式です。
  =IF(ROW(A1)>INDIRECT("来訪リスト!$J$"&COUNT(来訪リスト!$J:$J)+1),"",INDEX(来訪リスト!B$2:B$31,MATCH(ROW(A1),来訪リスト!$J$2:$J$31,)))  このシナリオでの関数式は、シナリオ4で求めた順位を参照します。シート:来訪リストのJ列の順位なる値を判断して重複している箇所を除く会社名を引用するように組み立てています。また、このシナリオのみ論理式においてリスト内J列の最終セルをINDIRECT関数を用いて求めています。

IF関数ROW関数INDIRECT関数COUNT関数INDEX関数MATCH関数の組み合わせです。
  =IF(ROW(A1)>INDIRECT("来訪リスト!$J$"&COUNT(来訪リスト!$J:$J)+1) ROW関数によりセルA1の行を求めています。ここで求められる値は 1 です。つまり今抽出しようとする順位です。次の行に書式を複写すると()のセルがA2に変化し2が返されます。順位2番目を意味します。

次に INDIRECT("来訪リスト!$J$"&COUNT(来訪リスト!$J:$J)+1) INDIRECT関数は、参照文字列によって指定されるセルに入力されている文字列を介して、間接的なセル "来訪リスト!$J$"&COUNT(来訪リスト!$J:$J)+1 の位置に入力されている値が参照されます。参照文字列で指定されるセル参照を返しますが、セル参照はすぐに計算され、結果として間接セルの内容が表示されます。INDIRECT 関数を使うと、数式自体を変更しないで数式内で使用しているセルへの参照を変更することができます。要するに COUNT(来訪リスト!$J:$J)+1 で求めるJ列の行(但し、1行目がタイトル文字列なのでその分 +1 で補完しています。)位置を求めて、J列の最終データ位置 "来訪リスト!$J$"&COUNT(来訪リスト!$J:$J)+1 によって入力最終行の値を参照します。 & は参照するセル列行の文字連結です。
論理式を文書にすると「ROW関数で求めた順位とシート:来訪リストJ列(重複しない会社の順位を求めているセル列)のデータ最終行値の順位を比較して、> 大きかったら "" 無記入を代入 (大きいとは、J列の最終データの値を超えたことになり以降データが存在しないことになる。) 大きくなければ INDEX(来訪リスト!B$2:B$31,MATCH(ROW(A1),来訪リスト!$J$2:$J$31,)) の結果を代入しなさい」 となります。

INDEX(来訪リスト!B$2:B$31,MATCH(ROW(A1),来訪リスト!$J$2:$J$31,)) は、INDEX関数MATCH関数の組み合わせです。この組み合わせは、さまざまなスタイルで多用されます。初めて縁する方は会得すべき関数と思います。
 INDEX関数は、テーブルまたはセル範囲にある値、あるいはその値のセル参照を返します。INDEX 関数には、配列形式とセル範囲形式の 2 種類があり、ここではセル範囲形式で定義し、戻り値は参照セルに入力されている値が引用されます。要するにその求められるセル位置 ROW(A1) の順位で該当する会社名が求められるということです。
来訪リスト!B$2:B$31 シート:来訪リストの会社名B$2:B$31をセル範囲としてMATCH関数 MATCH(ROW(A1),来訪リスト!$J$2:$J$31,) で得られる相対位置のセル行のデータを引用します。

 MATCH関数は、指定された照合の型に従って検査範囲内を検索し、検査値 ROW(A1) 順位と一致する要素の、範囲内 来訪リスト!$J$2:$J$31 での相対的な位置(行)を表す数値を返します。画面デザインを参照すると順位の順に並んでいるものの重複した番号が存在しています。重複した順位が存在するときは、その先頭行が適用となります。シナリオ4で求めた論理式演算の意味がわかると思います。新たな会社名の順位は必ずその順位の先頭に来ています。この点はもう一度考えてみてください。不明なときは、Q&Aでお会いします。

関数式をセルE13までフィルドラッグし 「匠への技」 VOL-4 は終了です。



 もひとつ おまけ
シナリオ8 IF(ROW(A1)>INDIRECT("来訪リスト!$J$"&COUNT(来訪リスト!$J:$J)+1) を分解しROW(A1)INDIRECT("来訪リスト!$J$"&COUNT(来訪リスト!$J:$J)+1で求められる値をダウンロードファイルに用意しています。
理解を深めるために参考ください。

冒頭に記述の通り、リストに対する配列数式はデータ量が増加するにつれて非常に動作効率が悪くなります。
リスト一覧、データベース等を分析する場合は、ピボットテーブルやオートフィルタ機能を使う方が効率的と思います。
ダウンロードファイルのシート:来訪リストには、メニューバー「データ」→「フィルタ」→「オートフィルタ」を起動させています。各項目の条件を付与し動作を確認してみてください。


○「匠への技」VOL-4で作成したExcelブックのダウンロード

  演習できるExcelブックを用意しています。下図のダウンロードボタンをクリックして下さい。

     Excel Book Name: takumivol-004.xls  Download file: takumivol-004.lhz