ブログトップ

カクレ理系のやぶにらみ

tamm.exblog.jp

時間のある方はお読みください。軽い気持ちで読み始めると頭が痛くなります。

カテゴリ:Excel のあの手この手( 36 )

 たとえば、縦(列)方向に入力されているデータを横(行)方向に並べ替えたいという場合があります。この場合、縦方向のデータを選択してコピーしてから、右クリックで「形式を選択して貼り付け」を選び、「値」にチェックをいれてから「行列を入れ替える」をチェックして「OK」をクリックする方法があります。

c0136904_21383444.jpg


 ただ、この方法だと数値の貼付になってしまうので、元のデータが更新された場合同じ作業をしなければコピー先に変更が反映されることはありません。これだとちょっと不便ですよね。
 そこで、今回はOFFSET関数と作業用セルを使って、データが入っているセルの参照式を横方向に一気にコピーするという方法をご紹介します。なお、この方法の応用技として、横方向に1個動くと縦方向に2個動いたセルを参照するというトリッキーな複写ができるので、覚えておいて損はないかもしれません。


 下の図をご覧ください。

c0136904_2138454.jpg



 セルB4から縦方向に文字が入力されています。これを横方向に並べ替えたいのですが、数式を使ってこれをやってのけようというのが今回の目的です。

 そのために、まずOFFSET関数についておさらいしておきましょう。OFFSET関数の引数は次の通りです。

 OFFSET(基準となるセル,行数,列数,高さ,幅)

 ここで、「高さ」と「幅」は省略可能であり、今回は使わないのでないものと思ってもらって構いません。そうするとOFFSET関数の構文は次の通りとなります。

 OFFSET(基準となるセル,行数,列数)

 これが何を意味するかというと、「基準となるセルから縦方向に動かす数(行数)と横方向に動かす数(列数)を指定すると、その分移動した先のセルを参照する」ということになります。
 具体例をあげてみましょう。

 =OFFSET(A1,1,1) という数式は、セルA1から縦方向に1つ横方向にも1つ動いたところ、つまりセルB2を参照します。

 同様に、=OFFSET(A1,1,0)という数式はセルA1から縦方向に1つだけ動いたところであるセルA2を参照します(横方向の移動は0です)。

 そこで図に戻ると、セルB4には「あ」という文字が入力されていて、セルB5には「い」という文字が入力されています。セルB5というのは、基準となるセルであるB4から縦方向に1つ下に移動したセルでもあります。同様に、セルB6は基準となるセルB4から縦方向に2つ下に動いたセルということになります。

 そこで、まず作業用セルを用意しましょう。
 まず、セルE9に1という数値を入力します。次にその右隣であるセルF9に「=E9+1」という数式を入力し、この数式を右方向にコピー&ペーストします。(数式の答えが14となるところまでコピペしてください。)

 次に、セルD10に「=B4」という数式を入力します。すると「あ」という文字が表示されます。今度はセルE10に「=OFFSET($B$4,E9,0)」という数式を入力します。その結果は「い」という文字が表示されることを確認してください。

 ここでちょっと説明しておくと、OFFSET関数の引数は数値なので、答えが数値になる関数や数式を引数に入れることもできるのです。つまり、セルE9には2という数値が入っているので、「=OFFSET($B$4,E9,0)」という数式は「=OFFSET($B$4,2,0)」と同じなのです。
 今回のように、縦方向にデータが入っている場合、基準となるセルからの距離が1つずつ増えていくわけなので、OFFSET関数の引数も1つずつ増えていくことになります。その場合、引数を数値で入力するよりも作業用セルを使う方が作業効率は圧倒的に改善されます。
 作業用セルにあらかじめ連続する数値をいれておき、OFFSET関数の引数としてこの作業用セル参照するように数式を設定して、右方向にコピペすれば、OFFSET関数の引数が自動的に変化してくれるようになるのです。

 もう一つ注意していただきたいのは、基準となるセルであるB4を絶対参照で指定しておくということです。こうしておけば、OFFSET関数をコピペしても基準となるセルは固定されたままです。
 試しに、元のデータを変更すると、横方向に並び替えたところにもその変更が反映されるはずです。


 なお、応用編として、作業用セルの値を2つずつ増やすようにすると、縦方向に1つおきに参照するようになります。よかったら試してみてください。

追記
 今回は縦方向だけの移動でしたが、縦横の移動をがあるという場合、作業用セルを2段にして1段目を行数、2段目を列数に充てて、それぞれ参照する数式をOFFSET関数に組み込んでください。
by t_am | 2016-07-09 21:40 | Excel のあの手この手
 使いこなすと便利なExcelですが、使いこなすというのが案外ハードルが高いように思います。
 実は、Excelを使いこなすにはコツがあって、それは「両手を使って作業をすること」なんですね。キーボードのブラインドタッチができる人はおわかりだと思いますが、両手を使って入力できるようになると、作業のスピードが格段に上がったはずです。同じように、Excelでの作業もできるだけ両手を使って行うようにした方がいいのです。

 そのためのポイントは、ショートカットキーを使うところにあります。

 今回はExcelを使う上で、知っておくと便利なショートカットキーについてそれぞれ掲載しておきます。全部覚える必要はありませんが、覚えておいて損はないという項目には◎をつけてあります。
 ショートカットキーは、CtrlキーやAltキーと組み合わせて使うことが多いのですが、次のような指使いをするとよいと思います。

左Shiftキー(薬指)
左Ctrlキー(小指)
左Altキー(親指)


 まず最初に、マウスでしかできない操作もしくはマウスを使った方が効率がよくなる操作について掲載しておきます。


c0136904_18584637.jpg



 次は入力中のキー操作です。F2キーによる編集モードへの切替やCtrl+Zキーによる直前の操作に戻すやり方は是非とも覚えていただきたいと思います。


 
c0136904_1923012.jpg




 意外と知られていないのが、入力の確定です。


c0136904_19514.jpg



 このアクティブセルの移動と、その次の範囲選択を覚えておくと作業効率が飛躍的にアップします。


c0136904_1961077.jpg



c0136904_1962672.jpg

 

 あともう少しの辛抱です。

c0136904_1972829.jpg



c0136904_198893.jpg



c0136904_1982432.jpg

by t_am | 2014-01-08 19:09 | Excel のあの手この手
 仕事でExcelやWordを使っていると、割と頻繁に使うファイルというのがあることに気づきます。一例を挙げれば、「業務日報」や「旅費精算書」、「議事録」などが該当すると思います。
 これらの文書ファイルに共通するのは、「書式(書き方)が決まっている」ということです。そこで、多くの人が、いったん作成したファイルの中身を修正して新たな書類を作成するといういわゆる「ファイルの使い回し」をしていることと思います。
 そのようなやり方に慣れてしまった方に、それをやめろと強制することはできませんが、ちょっとした手順を踏めば、以後使い回しをする必要がなくなるということは覚えておいて損はないと思います。

 今回ご紹介するやり方は、ファイルをテンプレート(雛形)として自分のパソコンに保存するというものです。応用も利くので覚えておくと便利です。

1.Excelの場合
 下図は、Office.comからダウンロードした「町内会議事録」というテンプレートのサンプルです。これを自分用にカスタマイズしてから、テンプレートとして自分のパソコンに保存することにします。


c0136904_1985100.jpg


 まず、○○町内会というのは常に同じなので、ここには固有名詞を入れてしまいます。同様に、いつも同じ会場を使うはずなので、ここにも固有名詞を入れてしまいます。また、出席者もいつも同じ名前が並ぶはずなので、ここもあらかじめ名前を入れておきます。欠席した場合(欠席)とでも書いておけばいいでしょう。
 日時の欄ですが、年月日と時刻さえ入力すれば、あとは自動的に表示してくれるという方が便利です。そこで、セルA7を右クリックして、「セルの書式設定」→「ユーザー定義」で下図のように、「ggge"年"m"月"d"日"(aaa) hh:mm"~"」と入力します。ggge は和暦を指定する記号で、(aaa)は曜日をカッコの中に表示させる記号です。ちなみに(aaaa)とすると、(日曜日)という表示になります。

(補足)
ggge だと「平成」や「昭和」になります。
gge だと「平」や「昭」になります。
ge だと「H」や「S」になります。

c0136904_1984085.jpg


 注意:Excel2007のユーザーは、(aaa)の替わりに(ddd)と入力して下さい。

 こうしておくと、たとえば「2014/1/1 13:00」と入力すると、「平成26年1月1日(水) 13:00~」と表示してくれるようになります。

 以下に、完成形を載せておきます。

c0136904_1918431.jpg



 次に、このファイルをテンプレートして保存します。「名前を付けて保存」ダイヤログボックスを表示させたら、「ファイルの種類」を「Excel テンプレート(*.xltx)」に変更してから、わかりやすい名前をつけて「保存」ボタンをクリックします。
 次回これを開くときは、「ファイル」タブ→「新規作成」→「ホーム」グループ→「マイテンプレート」をクリックすると「個人用テンプレート」というのが開くので、そこから選択します。


2.Wordの場合
 Wordでも基本的な手順は同じですが、最後のテンプレートの保存のしかたがExcelとはやや異なります。

(Windows Vistaの場合)
(1)「名前を付けて保存」ダイヤログボックスを表示させます。
(2)「お気に入りリンク」の下の「詳細」をクリックして「Templates」をクリックします。
(3)「ファイルの種類」を「Wordテンプレート(*.dotx)」に変更し、わかりやすい名前をつけてから「保存」ボタンをクリックします。

(Windows7の場合)
(1)「名前を付けて保存」ダイヤログボックスを表示させます。
(2)フォルダーの一覧の 一番上までスクロールし、「Microsoft Word」 の下の 「テンプレート」 をクリックします。
(3)「ファイルの種類」を「Wordテンプレート(*.dotx)」に変更し、わかりやすい名前をつけてから「保存」ボタンをクリックします。

 保存したテンプレートを使うには、Excel同様に、新規作成でマイテンプレートから開くことができます。

 Excelでは、ファイルの種類をテンプレートに変更すると、自動的に保存する場所も変更してくれますが、Wordでは自分で保存先を変更する作業を行わなければなりません。こういう操作性のちぐはぐさというのは、ユーザーを戸惑わせるものだと思いますし、もうちょっと何とかならないのかという気もします。
by t_am | 2013-12-29 19:18 | Excel のあの手この手
 以前、「Excelで住所録をつくる(1)」の中で、カードビュー・シートから当該レコードの相手に対し、メールソフトを立ち上げる手法としてハイパーリンクを用いるとご説明していましたが、誤りがあることがわかりましたので、お詫びして下記のように訂正いたします。

(誤り)
 カードビューに、数式を使ってメールアドレスを表示させたが、これにハイパーリンクを再度設定することはできません。一見ハイパーリンクが設定されているように見え、メールソフトも起動しますが、宛先欄には数式が記述されてしまうので、メールを送ることはできません。

(訂正)
 最もシンプルなやり方として、てデータベース・シートの中にある当該レコードのメールアドレスをクリックするという操作を、数式と単純なマクロを組み合わせて行うというものに訂正しました。

 既にサンプルファイル・ブログの内容ともに正しい方法のものに訂正していますので、既にサンプルファイルをダウンロードされた方は、恐縮ですが今一度ダウンロードしていただきますようお願いいたします。 
 

「Excelで住所録をつくる(1)」
http://tamm.exblog.jp/20557922/


(サンプルファイルの保管先)
http://p.tl/UrYx
by t_am | 2013-08-11 01:48 | Excel のあの手この手
 Excelではセルの中にかなり長い文字列を入力することができる反面、それを一度に全部表示させるというのは面倒ですし、使い勝手もよくありません。そこで、セルの内容をいったんテキストボックスに表示させ、そこに加えた変更を元のセルにも反映させる方法についてご紹介します。


 下図のように、長い文字列が入っているセルをアクティブにしてから、テキストボックスをクリックすると、セルの内容がテキストボックスに反映されます。この例では、セルS2の内容がテキストボックスに表示されています。
 この状態で編集可能なので(修正したものはただちにセルに反映されます)、修正が終わったら元のセルをクリックするか、そのひとつ右のセルをクリックしてください。Home キーを押すと、その行の行頭に移動します。
 なお、テキストボックスの中で改行するときは、Shift + Enter キーを押します。セル内改行(Alt + Enter)とは操作が異なるのでご注意下さい。

c0136904_151281.jpg



(手順)
1.開発タブを有効にします。
 Excel2007の場合、Officeボタンをクリックして、「Excelのオプション」→「基本設定」→「[開発]タブをリボンに表示する」にチェックを入れてオンにします。
 Excel2010の場合は、「ファイル」タブ→「オプション」→「リボンのユーザー設定」で右側にある「開発」にチェックを入れてオンにします。(下図参照)

c0136904_1515584.jpg



2.テキストボックスを配置します。
 下図のように、「開発」タブをクリックしてから「挿入」をクリックし、「Active X コントロール」のうち「テキストボックス」をクリックします。
 
c0136904_1522066.jpg



 次に、ワークシートの適当なところをドラッグすると、下図のようにテキストボックスが挿入されます。このときのテキストボックスは、普段お使いのテキストボックスとは少し異なります。
 また、リボンのところで「デザインモード」がオンになっていることに注意してください。

c0136904_152468.jpg



3.テキストボックスをアクティブセルにリンク付けします。
 今挿入したテキストボックスをダブルクリックします。すると下図のウィンドウが開くので、右の方にある「Change」と表示されているドロップダウンリストを展開して「GotFocus」をクリックしてください。画面上に新たに「Private Sub TextBox1_GotFocus()」という文字列が表示されます。


c0136904_1531089.jpg



c0136904_1533170.jpg


 「TextBox1_GotFocus」というのは、ユーザーがこのテキストボックス(TextBox1という名前がつけられています)をクリックして選択状態になったときのことを指しています。
 なお、その上にある

Private Sub TextBox1_Change()

End Sub


 という2行は不要なので、Delete キーで消しておきます。


c0136904_154378.jpg



 さらに、次のように、真ん中に「TextBox1.LinkedCell = ActiveCell.Address()」という文字列を1行付け足してやります。(下図参照)追加が終わったら、VBAのウィンドウを閉じてもらってもかまいません。

Private Sub TextBox1_GotFocus()

TextBox1.LinkedCell = ActiveCell.Address()

End Sub



c0136904_1543656.jpg
 


 リンク付けそのものはこれで終了です。以外とあっけないことがおわかりいただけると思います。そこで、少しばかりコードの説明をしておきます。

 TextBox1.LinkedCell というのは、テキストボックスと特定のセルをリンクさせるためのプロパティであり、通常はセルの番地をそのまま入力します。そうすることで、指定したセルの内容がテキストボックスに表示され、さらに編集した結果がセルにフィードバックされるようになります。今回は、特定のセルではなく、ユーザーが指定したセル(「アクティブセル」といいます)とテキストボックスをリンクさせるようにしなければならないので、

TextBox1.LinkedCell = ActiveCell.Address()

 というふうに、アクティブセルの番地を求める関数を用いて、テキストボックスのリンクセルプロパティの値がアクティブセルの番地に一致するということにしています。
 すなわち、テキストボックスに表示させたいセルを、ユーザーがクリックしてアクティブにした後、テキストボックスをクリックすることで「GotFocus」の状態になり、リンクセルプロパティに対し、アクティブセルの番地が代入されるわけです。
 なお、リンクプロパティの操作やテキストボックスの大きさの変更は、「デザインモード」でないと行うことができません。


4.テキストボックスの書式を修正しておきます。
 テキストボックスを右クリックして「プロパティ」をクリックするか、リボンの開発タブの真ん中ほどにある「プロパティ」をクリックしてもらうと、下図のようにテキストボックスの「プロパティ」が表示されます。


c0136904_1592327.jpg



 ここでは、「IME Mode」、「MultiLine」「WordWrap」の3カ所のみ変更します。
 まず、「IME Mode」ですが、この値を「4-fmIMEModeHIRAGANA」にします。これはテキストボックスの日本語入力を常にオンにするためのものです。
 「MultiLine」はテキストボックスの内容を複数行にわたって表示させるかどうかを指定するためのプロパティですので、その値を「True」にしておきます。
 最後に、「WordWrap」は文字列を右端で折り返すかどうかの設定ですので、これも「True」にしておきます。そうでないと文字列の折り返しをしなくなるので、セルの内容をテキストボックスに表示させる意味がなくなってしまいます。なお、「WordWrap」をTrueにしても「MultiLine」がTrueになっていないと有効にはならないので注意してください。

 すべての設定が終わったら、「デザインモード」をクリックしてオフにしてやることで、テキストボックスが使用できるようになります。


5.テキストボックスを常に表示させるようにする。
 このようにしてつくったテキストボックスですが、このままだとワークシートをスクロールするとテキストボックスもいっしょに動いてしまうという弱点があります。
 それでは意味がないので、このテキストボックスが常にワークシートに表示させるようにします。
 
 もう一度デザインモードにしてから、テキストボックスをダブルクリックして、VBAのウィンドウを開きます。
 そこで、次の文章を追加(面倒くさいという方はコピペでどうぞ)します。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With ActiveSheet
.Shapes("TextBox1").Top = .Cells(ActiveWindow.ScrollRow + 5, ActiveWindow.ScrollColumn + 1).Top
.Shapes("TextBox1").Left = .Cells(ActiveWindow.ScrollRow + 5, ActiveWindow.ScrollColumn + 1).Left
End With

End Sub


 実際には、下図のようになります。


c0136904_1595296.jpg


 SelectionChange とは、シート上で選択範囲が変更されたときのことをさし、アクティブセルを移動させる場合もこれに該当します。
 したがって、このコードは、アクティブセルが移動するたびに、テキストボックスというオブジェクトの上端と左端の位置を指定するためのもので、それぞれ上から5行分、左端から1列分空けたところにテキストボックスを配置せよという意味になります。このとき、「ウィンドウ枠の固定」を行っていると、固定されているところはカウントされません。

 付記
 このコードについては、

http://oshiete.goo.ne.jp/qa/4145549.html?order=asc&isShow=open 

を参考にさせていただきました。Masa2072さんにお礼を申し上げます。


(サンプルファイルについて)
 今回つかったサンプルファイルは、前回使用したものに追加修正を加えています。最新のサンプルファイルをお持ちでない方はダウンロードしてお使いください。

http://p.tl/UrYx
by t_am | 2013-08-04 15:10 | Excel のあの手この手
 前回はExcelを使って住所録のデータベースををつくる方法をご紹介しました。実は、差し込み印刷というWordの機能を使うと、ExcelのデータはWordで作成した文書で使用することができます。そこで、今回は住所録のデータに基づいてFAX送信票を作成する方法についてご紹介します。

1.差し込み印刷とは
 たとえばご自分がどこかの会社から案内状をもらうことを想像してみて下さい。その宛名が「お客さま各位」となっている場合と、自分の名前がちゃんと描かれている場合とでは、どちらが訴求力が強いでしょうか?
 顧客の名前が一人ずつ書かれている案内状の方が訴える力が強いのは言うまでもないのですが、そうかといって1枚ずつ顧客の名前を入力していたのでは時間がかかってしかたありません。でも、よく考えてみると顧客の名前や住所は既にPCの中にデータとして記録されているわけです。であれば、同じ電気信号なのですからそのデータを活用するのは不可能ではないはずです。
 ExcelやWordといったOfficeソフトは、互いに連携できるようにつくられていて、Wordにある「差し込み印刷」という機能を使うと、Excelで作成した住所録のデータをWord文書に簡単に取り込むことができるようになります。
 差し込み印刷を使うには、Wordの側で印刷する「差し込み文書ファイル」を作成し、Excelの側でWord文書に送りたいデータ(顧客の氏名や連絡先など)が入った「差し込みデータファイル」を作成します。

 差し込み印刷のイメージは下図の通りです。1件の差し込みデータが差し込み文書ファイルに送られ、文書として完成させます。さらに次のデータがあれば差し込み文書がコピーされ、データが送られていき、差し込みデータがなくなるまで差し込み文書がつくられることになります。


c0136904_071187.jpg


 なお、今回もインターネットディスク上に公開しているサンプルファイルに基づいてご説明いたします。下記のリンクから「FAX送付状,doc」というWordファイルをダウンロードしてください。なお、このファイルを開くときに、次の確認メッセージが表示されるので、「いいえ」を選択して下さい。


c0136904_23474969.jpg




http://p.tl/UrYx



2.差し込み印刷のやりかた
(1)管理上、差し込み文書ファイルと差し込みデータファイルは同じフォルダに置いた方が楽です。
(2)差し込み文書ファイルを開きます。といっても、この段階ではまだ差し込み印刷の準備は行われていないので、これからその準備を行っていきます。
(3)まず、差し込み文書ファイル上で、差し込みデータファイルがどこにあってどういう名前であるかという設定を行います。
(4)次に、差し込み文書に対し、差し込みする項目(差し込みフィールド)をどの位置に配置するかを決めます。
(5)差し込みにあたり、特に指定がなければ差し込みデータファイルのすべてのレコードが大賞になります。あらかじめ指定したレコードだけを差し込み印刷の対象にしたいときはクエリオプションを利用します。なお、この指定はExcelの方で行っておきます。
(6)差し込み印刷を開始します。


 下図は、サンプルファイルのうち、差し込み文書ファイルを開いたところです。以下の手順はWord2010のものですが、Word2003 以前のバージョンであれば「ツール」メニューから「差し込み印刷ヘルパー」を開き、上の(1)から(6)の順番に作業をしていきます。Word2010では画面の様子は違いますが、やること自体は大差ありません。


 「差し込み文書」タブから、「差込印刷の開始」をクリックして、さ「差し込み印刷ウィザード」を選択します。


c0136904_23495964.jpg




 画面の右側にウィザードが表示されます。このガイドに基づいて作業を進めていけばいいのですが、最初に「レター」のところにチェックを入れ、下部にある「次へ:ひな形の選択」をクリックします。」




c0136904_23502954.jpg

 「現在の文書を使用」にチェックを入れ、「次へ:宛先の選択」をクリックします。

c0136904_235117100.jpg


 差し込みデータファイルとして「住所録.xlsm」(Word2003以前であれば「住所録.xls」)は既に作成されているので「既存のリストを使用」にチェックを入れ、「参照」をクリックします。


c0136904_23515447.jpg




 「データファイルの選択」ダイヤログボックスが開くので、差し込みデータファイルが保存されているフォルダを開き、「住所録.xlsm」を選択して、「開く」ボタンをクリックします。


c0136904_23523085.jpg




 「テーブルの選択」ダイヤログボックスが開くので、住所録のデータベースが登録されているシートである「データベース$」をクリックして、「OK」をクリックします。


c0136904_23533343.jpg




 「差し込み印刷の宛先」ダイヤログボックスが開くので「フィルター」をクリックします。というのは、住所録のすべてのレコードを印刷するわけではないからです。」

c0136904_23541330.jpg



 「フィルターと並べ替え」ダイヤログボックスが開きます。下図のように、フィールド「印刷」が「が値と等しい」「1」と入力して、「OK」をクリックすると「差し込み印刷の宛先」ダイヤログボックスの中身が、「印刷」フィールドにあらかじめ「1」を入力しておいたものだけを表示するように変更されています。


c0136904_23545335.jpg





c0136904_23552778.jpg




 差込印刷ウィザードで「次へ:レターの作成」をクリックします。
 下図のように「レターの作成」というふうにウィザードが変わります。ここで、「差し込みフィールドの挿入」をクリックすればよいのですが、その前に、差し込み文書のどこに差し込みフィールドを挿入するのか場所を決めて、そこをクリックしておきます。その上で「差し込みフィールドの挿入」をクリックして下さい。
 このサンプルでは、挿入する「差し込みフィールド」は、「会社名」「部署名」「役職」「氏名」「敬称」「TEL」「FAX」となります。


c0136904_23564591.jpg




c0136904_23573719.jpg



 下図のように差し込みフィールドを挿入していきます。
 挿入した差し込みフィールドは、フォントやフォントサイズを自由に変更できるので、見た目の収まりの良さを考慮して大きさを決めてください。
 また、このサンプルでは役職名・氏名・敬称が同じ行に配置されています。この場合、2番目の差し込みフィールド(氏名)の開始位置は(役職)フィールドの最後の文字から1文字空けたところとなります。差し込みフィールドの位置を変えるとフィールド感の間隔も変化することになります。


c0136904_23581929.jpg




 配置が完了したら、ウィザードの「次へ:レターのプレビュー表示」をクリックします。
すると下図のように差し込みデータが差し込み文書に挿入されるのがわかります。これは差し込みフィールドの挿入位置を確認するためにあるので、修正したい場合はウィザードでいったん戻って、位置やフォントサイズなどの修正をおこないます。





 確認して、これでよければ「差込印刷の完了」をクリックして、「印刷」をクリックします。

c0136904_0007.jpg



 このように差し込みデータファイルを設定すると、その関係もいっしょに保存されることになるので、次回この差し込み文書ファイルを開くときに、確認メッセージがひょうじされますが、「いいえ」を選んでください。また、差し込み文書ファイルを閉じるときには「保存しない」を選択します。というのは「保存する」を選ぶと、差し込み完了後の状態でWordの差し込み文書ファイルが保存されるからです。

c0136904_03982.jpg

by t_am | 2013-07-30 00:05 | Excel のあの手この手
 Excelはデータベースを直感的につくることができる便利なツールですが、項目数が増えてくると、どんどん右に入力していくことになり、全体を1つの画面で見ることができなくなるという弱点があります。
 Access を使えばこの問題は解消できるのですが、そもそもAccess を持っていないという人もいます。そこで、少し工夫してExcelでも充分実用に耐える住所録のデータベースを構築してみたいと思います。


 この画面のように、右側に入力されているデータを見ようとするには、右側にスクロールしないと見ることができません。こういうのは意外と不便なものです。

c0136904_1812594.jpg



 さらに、入力されている文字数が多くなると、行の高さや列幅を広げてやらないと、全部を一度に見ることができなくなってしまいます。こういうのも不便ですね。


c0136904_18135455.jpg



(住所録の概要-問題解決のために-)
 データを入力・抽出・検索・修正するためのシート(データベース本体)と、閲覧専用のシート(カードビュー)の2種類を用意しました。
 データベース本体で抽出・検索されたレコードのうち閲覧したいものにフラグ(ここでは数字の1)を建てることにより、その内容をカードビューのシートで見ることができます。実際には、この2種類のシートを切り替えて使うことになります。

c0136904_054427.jpg



 なお、せっかく住所録データを入力するのですから、データベースから直接メールソフトを起動したり、指定した相手宛にFAX送付状(これはWord文書になります)を作成するという機能も装備しました。(差込印刷の方法については、次回で説明しています。)
なお、例によってサンプルファイルをアップしていますので、必要な方は下記のURLからダウンロードして下さい。今回のファイルには簡単なマクロが使われています。開く際に警告メッセージが表示されると思いますので、事前にウィルスチェックをしてから開いて下さい。(Excel2007以降をお使いの方は、「住所録.xlsm」の方をお使いください。)

http://p.tl/UrYx


1.住所録の入力
 住所録として登録しておく情報は、下図のように「面会日」から右側の項目になります。
今回、サンプルファイルに登録している項目は次の通りで、とりあえず千件のデータが入力できます。

面会日      Ctrl + ;(セミコロン)で本日の日付が入力できます
会社名
部署名
役職
氏名
シメイフリガナ
敬称       Alt + ↓ でリストが展開します。適当な敬称を選択して下さい。
郵便番号(〒)
住所
住所2      ビル名やフロア、室名を入力
TEL
FAX
携帯
メルアド(PC)     「メール作成」ボタンをクリックするとメールソフトが起動します
メルアド(携帯)    「携帯メール作成」ボタンをクリックするとメールソフトが起動します
メモ(自由入力)    後で思い出せるように、備考欄として使います
面会者

(1)データベースの入力
・入力は、相手の名刺を見ながら行って下さい。
・1つの項目の入力が終わったら、Tabキーを押すと右側のセルに移動します。
・「メモ(自由入力)」のセルでは、Alt + Enter キーでセル内改行をすることができます。
・最後の「面会者」の入力が終わったらEnter キーを押すと、次の行の面会日に移動します。
・ひとつ上のセルと同じ内容を入力するには、Ctrl + D キーを押すと、コピー&貼付を自動的に行ってくれます。

(2)データベースの検索
・検索したい文字列が含まれている列全体を選択します。
・Ctrl + F キーで「検索」ダイヤログボックスを表示させます。
・キーワードを入力して「次を検索」をクリックします。




・目的のレコードが見つかったら内容の修正をするか、レコード全体を閲覧するには
 「フラグ」に1を入力して「カードビュー」シートに切り替えます。

c0136904_18172882.jpg



c0136904_111670.jpg



(3)データベースの抽出
・項目名の欄にある▼をクリックして、ドロップダウンリストを展開します、
・検索オプションで、キーワードとなる文字列を入力すると、その文字列を含むレコードが抽出されて表示されます。
・閲覧したいレコードの「フラグ」に1を入力して「カードビュー」シートに切り替えます。

(4)閲覧を終えるには
・「カードビュー」シートの「再検索」ボタンをクリックすると、データベースに入力されていたフラグの1は消去され、抽出するためのフィルタも解除されます。
・また「住所録の終了」ボタンをクリックすると、上書き保存してからExcelを終了します。

(5)シート「データベース」の数式
 A列にだけ数式が入力されています。ちなみにA2の数式は次の通りとなります。

=IF(H3="","",A2+1)

 つまり、同じ行の氏名欄が空白の場合はナンバーを割り振らずにおいて、氏名欄が入力されたらナンバー(前の行の番号+1)を割り振るという数式です。
 このナンバーは、カードビューにレコードの内容を表示させる際に用いることになります。

(6)シート「カードビュー」の数式
・基本的に VLOOKUP 関数を用いています。その際の検索の値として、右上にあるセル「No.」には次の数式が入力されています。

=SUMIF(データベース!B2:B1001,1,データベース!A2:A1001)

 これは、「データベース」シートのB列すなわちフラグを入力する列のうち、1が入力されているセルに対し、A列(No.)の数値を合計させるというものです。ところが、フラグは1回につき1個しか建てないので、実際にはフラグの立っている行のNo.の値がここに表示されることになります。
 あとは、VLOOKUP関数で、それぞれの項目について「データベース」シートから値を引っ張ってくるだけのことです。(ちなみに、データベースの範囲には名前「住所録」が定義されています。データが千件以上になる場合、名前の定義-セル範囲-を修正してください。)

(7)カードビューからメールソフトを起動する
 データベースにメールアドレスを入力すると、自動的にハイパーリンクを設定してくれるので、クリックひとつでメールソフトが起動します。しかし、カードビューではVLOOKUP関数でメルアドの値を読み込んでいるだけなので、このままではハイパーリンクにはなりません。
 そこで、データベース・シートにあるメールアドレスをクリックするという比較的単純なマクロを作成して、「メール作成」ボタンに登録しました。マクロの作成といっても、データベース・シートにある該当レコードのメールアドレスのセルをクリックするという操作をマクロに記録した後、カードビューで閲覧しているレコードのメールアドレスはデータベースシートのどの位置にあるセルなのかを取得するための変数n を追加しただけです。変数の値は、カードビューのうち、カードNo.のすぐ下のセルに数式をいれてあります。

Sub メール作成()
'
' メール作成 Macro
''
Dim n As Variant

n = Range("o2").Value

Sheets("データベース").Select

Range(n).Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

End Sub



 ほかにもやり方はあると思いますが、これが一番シンプルなのではないかと思います。


(8)その他
 「印刷」フィールドに1を設定したレコードは、Wordで作成した文書に住所録データを差し込んで印刷することができます。
 差込印刷でよく使う事例として、FAX送信票があります。これは、住所録に登録されているデータをそのままWord文書に送るというもので、けっこう便利な機能です。
 その使い方は次回に申し上げることにしたいと思います。

 
by t_am | 2013-07-28 18:30 | Excel のあの手この手
 Excelで用いる関数には、書き方のルール(文法)があって、それを理解しておくと結構いろいろなことができるようになります。文法といってもごく単純なものですから、中学生程度の読解力があれば充分理解できるはずです。

 なお、今回のサンプルファイルも、例によってインターネットディスク上にアップロードしておきましたので、必要な方はダウンロードしてください。(数式の結果を表示させるのでは、数式そのものを表示させるようにしてあります。結果を表示させるには、Excel2003以前のバージョンであれば、「ツール」メニュー→「オプション」から「数式」のチェックを外してください。Excel2007以降であれば、「ファイル」タブから「オプション」の「詳細設定」から「計算結果の代わりに数式をセルに表示する」のチェックを外してください。)

(サンプルファイル)
http://p.tl/LMF3


(用語)
 関数とは、あたえられた演算要素に基づいて答えを導き出す数式のことをいいます。演算の要素のことを「引数」と呼び、導かれた答えのことを「戻り値」と呼んでいます。

(書き方のルール)
①原則として半角英数で記述します。
②先頭には「=」(半角のイコール)をつけます。
③次に関数名を記述します。
④半角カッコの中に引数を書いていきます。
⑤セルの範囲を引数として用いる場合、セル番地とセル番地を半角コロン「:」でつなぎます。
⑥引数として文字列を用いる場合、二重引用符 ” ”で囲みます。例外として、名前として定義してある文字列の場合二重引用符は不要であり、そのまま名前を記述することができます。
⑦引数が2つ以上ある場合、半角コンマで区切ります。
⑧最後に半角カッコを閉じます。

(引数のタイプ)
 引数には値とセルの範囲の2種類があります。たとえば、Excelでもっともよく使われているSUM関数の引数は値でもセル範囲でもよく、またこの2種類を組み合わせて使うこともできます。(下図参照)


c0136904_22204233.jpg



 Excel で、SUM関数の次に使われている(と思われる)IF関数ではどうでしょうか?IF関数の構文は次の通りです。

=IF(条件式,真の場合の結果,偽の場合の結果)

 条件式は、それが成立するかどうかが判定されるので、真(=1)か偽(=2)のいずれかの値をとります。また、真の場合の結果も偽の場合の結果もIF関数の戻り値をあらかじめ指定しておくというものですから、これも値になります。(セル範囲ではない。)

(引数のタイプ別特徴)
 引数にはタイプによってその記述方法に特徴があるので、これを理解しておくと表の作成とメンテナンスの効率がアップします。

①値の場合
 引数が値型の場合、値を直接記述すか、あるいは、ワークシートのどこかに既に値が存在している場合は、そのセルを参照する数式を記述することもできます。つまり、Excelが値として処理できるようにしれやればいいということです。

②セル範囲の場合
 引数がセル範囲の場合、通常は始点のセル番地と終点のセル番地を記述するという方法をとります。関数をコピー&ペーストした祭にセル範囲が変わるのを嫌うのか、それとも利用するのかによって、セル範囲の記述方法は、相対参照・絶対参照・複合参照を使い分けします。
 なお、セル範囲は「名前」として定義しておく(この場合は絶対参照と同じ扱いになります)こともできます。
 さらに、INDIRCT関数を使って、他のセルの中の値をセル範囲として利用することも可能です。


c0136904_2221183.jpg




 下の表のように、関数の引数の記述方法はいろいろな方法が使えることがわかります。この中には、普段使わないやり方も含まれていると思いますが、表の作成やメンテナンスを考えたときに、知っておくと便利です。


c0136904_22214253.jpg
  


(VLOOKUP関数での活用事例)
 VLOOKUP関数の構文は次の通りですが、このうち「検索する値」と「列番号」のタイプは「値」であり、「検索する範囲」のタイプはセル範囲です。

=VLOOKUP(検索する値,検索する範囲,列番号,[検索の型])

 VLOOKUP関数をお使いの場合、列番号には数値をそのまま入れているケースが多いのではありませんか? それが悪いというわけではありません。ただし、その場合、VLOOKUP関数をコピーして使いたい場合、列番号をいちいち修正しなければならないことがあって煩わしい思いをしたことはありませんか?
 列番号も「値」型なのですから、他のセルに列番号とする数値を入力しておき、セル参照形式で記述しておくと、最初の数式をひとつ作成するだけで、後はコピー&ペーストで表を完成させることができます。(下図参照)


c0136904_2222934.jpg




 なお、Ctrl + F キーで表示される「検索と置換」ダイヤログボックスのうち、「置換」機能は数式の一括修正にも使うことができます。下図のように、修正したい範囲をドラッグしてから、Ctrl + F キーを押して「検索と置換」ダイヤログボックスを呼び出します。数式のうち修正したい文字列と修正後の文字列を入力して「すべて置換」をクリックすれば完了です。コピー&ペーストで直せない場合に有効なので、頭の片隅に入れておくと、いざというときに便利だと思います。


c0136904_22223279.jpg




(参考:IF関数における論理式)
 IF関数の最初の引数(条件式)は、その条件が成立する場合は真(TRUE)を返し、成立しない場合は偽(FALSE)を返します。下図は、点数が60点未満であれば不合格、60点以上80点未満であれば合格、80点以上であれば優秀という判定ルールに基づいて、枠の中に入力された点数を自動的に判定するための数式です。
 別途判定表を作成して、VLOOKUP関数(検索の型はTRUE型とします)を使えばどうってことないのですが、ここではIF関数を使っています。
 60点以上80点未満という条件式をどのように記述するかは、図のように、AND関数を使う方法が一般的ですが、条件式が真(1)か偽(0)のいずれかを返す論理式であることを利用して、論理式と論理式の積という記述にすることも可能です。というのは、1×1=1であって、点数が60点以上(真ならば戻り値は1)と点数が80点未満(真ならば戻り値は1)の2つが同時に成立する(真である)には 1×1=1 という式が成り立たなければならないからです。


c0136904_22225731.jpg

by t_am | 2013-07-07 22:23 | Excel のあの手この手
 自習のために、エクセルでJリーグ順位表を作成しました。J1の試合をデータベースに入力していくと、順位表が作成されるというものです(下図参照)。4月6日現在、わがアルビレックス新潟は、攻撃力(1試合あたりの平均得点数)が、J1の中で最下位となっており、今年も苦戦を強いられることになりそうです。

c0136904_2016543.jpg



(エクセルファイルのダウンロード)
 今回作成したエクセルファイルは、下記のサイトからダウンロードすることができます。例によって、Excel2007以降をお使いの方用とそれ以前のExcelをお使いの方用に分けて作成してあります。よくわからないという方は、ファイル名の末尾が「xls」となっている方をお使いください。

http://p.tl/ZdhC


(シートの構成)  
1.シート「チ-ム」
 データベースを入力する際に用いるチーム名をリスト化するためのデータと、勝ち点を配分するためのデータが登録され、それぞれ名前が定義されています。名前の定義については、名前ボックス(セルA1のすぐ上にある白い枠。通常は、現在カーソルがあるセルの行列番号が表示されています)の右にある▼ボタンをクリックすると、定義されている名前の一覧が表示されるので、それをクリックすると名前の範囲が選択状態になります。


c0136904_2018201.jpg



 名前を定義するには、範囲を選択した状態で名前ボックスに名前を入力するのが簡単です。


2.シート「結果」
 これは、各節の試合の結果を入力するためのシートで、この部分がデータベースになっています。以前も申し上げましたが、データベースは、先頭行が項目名となっていて、2行目以降にデータを入力していくという形式となります。これに対して、集計表というのは先頭行と左端列にそれぞれ項目名が記入されているものをいい、このサンプルでは、シート「順位表」がそれになります。
 このシートでは、誤入力を防ぐために、データ(試合日・チーム名・得点)を入力するところを黄色く着色してあり、さらに、入力中にチーム名を間違えては何にもならないので、チーム名はリストから選択するようにしてあります。

c0136904_2019175.jpg



 なお、試合結果を入力するには、「Jリーグ公式サイト」のトップページに、各節の試合結果が掲載されているので、それを参考にしてください。

(Jリーグ公式サイト)
http://www.j-league.or.jp/

 Windows Vista 以降をお使いの方であれば、「Shipping Tool」というアクセサリを使うと、試合結果のところだけを画像としてパソコンに取り込んで、そのままExcelに貼り付けることができるので便利です。(下図参照)


c0136904_20205573.jpg


 
 また、このシートでは、順位表を作成するための下ごしらえとしていくつかの計算をするようになっています。それらの計算式は、C列とH列の間、J列とO列の間に隠されています。たとえば、C列とH列をドラッグして、右クリックしたメニューから「再表示」を選ぶと、それらが表示されます。アウェイの側にも同様の計算式が隠されているので、再表示していただくとわかりやすいと思います。


c0136904_20214074.jpg




(計算式の説明)
 入力するデータは、試合日とチーム名と得点だけであり、それらのデータから試合の結果などを導き出して、順位表を作成する材料を作成するために、各種計算式が用意されています。

2-1 「勝敗」欄の計算式
 =IF(AND($C30="",$J30=""),"",IF($C30>$J30,"勝",IF($C30<$J30,"負","引分")))

 ここには、同じ行の得点(対戦相手のもの)を比較して、自チームの得点の方が多ければ「勝」、少なければ「負」、同じであれば「引分」と表示させる計算式が入力されています。ただし、試合がまだ行われていない(両チームの得点欄がともに空白)場合、結果については何も表示させないようにしてあります。

2-2 「勝点」欄の計算式
 =IF(ISERROR(VLOOKUP(D30,勝ち点,2,FALSE)),"",VLOOKUP(D30,勝ち点,2,FALSE))
 
 ここには、勝敗欄に表示される値に応じて、そのチームの勝ち点を計算する式が入力されています。勝敗の結果は「勝」「負」「引分」の3種類なので、それに応じた勝ち点を与えればいいのですが、今回はVLOOKUP関数を用いました。なお、試合がまだ行われていない場合、VLOOKUP関数はエラーを返してしまいます。それを防ぐためにIF関数とISERROR関数を使って、試合が行われていない(VLOOKUP関数の結果がエラー)場合は、セルには何も表示させないようにしています。

2-3 「得失差」欄の計算式
 =C30-J30

 自チームと相手チームの得点(=自チームの失点)の差が得失点差ですので、ここは単純な引き算が入力してあります。

2-4 「作業用」欄の計算式
 =B30&D30

 これは、順位表を作成する際に、そのチームの勝ち数、負け数、引分け数をカウントするために設けた作業用セルです。他のチームの勝ち負けと区別するために、「チーム名」+「勝敗」を表示させるようにしてあります。Excelでは2つのセルの内容をくっつけて表示させるときに演算子「&」を用います。


3.シート「順位表」
 これは、シート「結果」で入力したデータベースを集計するためのシートです。「順位」欄と「チーム名」欄には値が入力されており、それ以外の列はすべて計算式が入力されています。(下図参照)なお、ここではわかりやすくするために、行列番号と枠線を表示させています。(非表示にするには、「オプション」の「詳細設定」で設定を変更します)


c0136904_20223523.jpg




3-1 「勝ち点」欄の計算式
 =SUMIF(結果!$B$3:$B$183,C4,結果!$E$3:$E$183)+SUMIF(結果!$I$3:$I$183,C4,結果!$L$3:$L$183)

 シート「結果」において、1試合ごとに、そのチームの勝ち点を計算してあるので、ここではそれを合計すればよいようになっています。そのために、SUMIF関数を用いるのですが、勝ち点はホームとアウェイの2カ所にあるので、両方を足した結果をここに表示させるようにしています。

3-2 「試合数」欄の計算式
 =COUNTIF(結果!$B$3:$B$183,C4)+COUNTIF(結果!$I$3:$I$183,C4)

 シート「結果」のホームとアウェイの両方に記録されているチーム名を数えて、それを合計しています。

3-3 「勝ち」欄の計算式
 =COUNTIF(結果!$G$3:$G$183,$C4&"勝")+COUNTIF(結果!$N$3:$N$183,$C4&"勝") 

 シート「結果」の作業用セルの中にある「チーム名」+「勝」の個数を、ホームとアウェイで数えて、その合計値を表示させるものです。

3-4 「引分」欄の計算式
 =COUNTIF(結果!$G$3:$G$183,$C4&"引分")+COUNTIF(結果!$N$3:$N$183,$C4&"引分")

 考えかたは、「勝ち」欄の計算式と同じです。

3-5 「負け」欄の計算式
 =COUNTIF(結果!$G$3:$G$183,$C4&"負")+COUNTIF(結果!$N$3:$N$183,$C4&"負")

 これまでと同じなので省略します。

3-6 「得点」欄の計算式
 =SUMIF(結果!$B$3:$B$183,C4,結果!$C$3:$C$183)+SUMIF(結果!$I$3:$I$183,C4,結果!$J$3:$J$183)

 シート「結果」のアームとアウェイに記録されている、そのチームの得点をSUMIF関数を使って合計しています。

3-7 「失点」欄の計算式
 =I4-K4

 「得点」欄と同じ考えかたで計算式を組み立てることもできますが、得点-失点=得失点差ですから、ここでは単純に「得失点差」-「得点」としました。

3-8 「得失差」欄の計算式
 =SUMIF(結果!$B$3:$B$183,C4,結果!$F$3:$F$183)+SUMIF(結果!$I$3:$I$183,C4,結果!$M$3:$M$183)

 考えかたは、「得点」欄の計算式と同じです。


 順位表そのものはこれまでご説明した計算式でつくることができます。今回は、せっかく順位表をつくって、データベースを集計できるようにしたのですから、多少なりとも数値分析もしてみたいと思います。

3-9 「1試合あたり平均得点」欄の計算式
 =I4/E4

 式そのものは非常に単純です。これまでの得点の合計を試合数で割るだけです。こうすることで、そのチームの1試合あたりの得点数(=攻撃力)を明らかにすることができます。なお、この数値は試合数が少ないときは偶然が左右する要素も大きいのですが、試合数が増えるにつれて、次第にそのチームの実力を反映したものになっていきます。

3-10 「順位」欄の計算式
 =RANK(M4,$M$4:$M$21)

 そのチームの1試合あたりの平均得点数が、J1全チームの中でどの辺に位置するのかを計算する式です。順位を計算すればいいのですから、単純にRANK関数を用いています。

3-11 「1試合あたりの失点数」欄の計算式
 =J4/E4

 この値が少なければ少ないほど、守備力が高いことになります。

3-12 「順位」欄の計算式
 =RANK(O4,$O$4:$O$21,1)
 
 先ほどと同じ考えかたですが、RANK関数の引数の最後に「1」と指定している点が先ほどと異なります。RANK関数では、最後の引数は省略可能であり、省略した場合順位を降順(値の大きなものから順番)でつけるのですが、ここに「0」以外の数値を指定すると昇順(値の小さな物から順番)に順位をつけるようになります。「守備力=失点を少なくする力」なので、ここでは昇順で順位付けをするようにしているわけです。

3-13 順位表の更新
 試合が行われるたびに、集計結果である「順位表」の内容は変化していきます。その変化に応じて順位付けをする必要があるわけですが、Jリーグではいかのルールに基づいて順位を決めているようです。

1)勝ち点の多いチームが上位になる
2)勝ち点が同じ場合、得失点差の大きい方が上位になる
3)得失点差も同じ場合、得点数が多い方が上位になる

 以上のルールで並べ替えを行うと下図のようになります。


c0136904_2023534.jpg



 なお、この操作をマクロとして記録し、ボタンに貼り付けたものが「順位表更新」です。試合結果を入力した後、このボタンをクリックすると並べ替えを自動で行ってくれます。


4.データベースの入力上の注意点
 データベースを入力する際に、入力ミスがあると集計結果もおかしなものになります。今回のサンプルでいえば、「鹿島」を「鹿嶋」と入力してしまうと、パソコンは別なデータとして処理するので、集計が間違ったものになります。それを防ぐために、リストを作成して、その中から選択するようにしているわけです。
 なお、ドロップダウンリストは、いちいちマウスを使わなくても、Altキーと↓キーの同時押しで展開されます。
by t_am | 2013-04-07 20:24 | Excel のあの手この手
 シミュレーションなど、Excelで原本となるブックを加工して使う場合、きちんと名前をつけて保存しておかないと、後でわからなくなることがあります。また、保存のし忘れということもあり、その場合作業に費やした時間がパーになるのですから、情けなくなります。

 私の場合、(粗忽者なので)そういう経験を嫌というほどしてきたことから、「作業の終了にあたってブックを別な名前で保存してからExcelを終了する(あるいは当該ブックだけを閉じる)」というマクロをご紹介します。
 なお、例によってサンプルファイルをインターネットディスクにアップロードしてあります。下記のアドレスをクリックもしくはWebブラウザに貼り付けていただくと、当該フォルダが開きます。Excel97-2003版(ファイル名の末尾が「.xls])とExcel2007-2010版(ファイル名の末尾が「.xlsm」)の2種類がありますので、お間違えのないようにどうぞ。

http://bit.ly/WkyPzs



c0136904_1224445.jpg



1.マクロの記述
 今回も、マクロのコードを直接書いていくので、Microsoft Visual Basic Editor を起動します。

(Excel2003以前)
 「ツール」メニューの「マクロ」から「Visual Basic」をクリック。

(Excel2007以降)
 リボンの「開発」タブから「Visual Basic」をクリック。


 下図の画面となるので、「標準モジュール」の「Module1」をダブルクリックします。


c0136904_12243989.jpg



 下記のコードを画面にコピー&ペーストするか、自分で入力します。(注意:このコードはExcel2007-2010用です。Excel2003以前のものをお使いの方は、コードの一部を修正しないと使えません。その方法は後でご説明します。)

(マクロのコード))
Sub 日付入力()
'
' 日付入力 Macro
'
'
Range("j3").Value = Now


End Sub

Sub 別名で保存()
'
' 別名で保存 Macro
'
'
Call 日付入力


'エラーが発生した(キャンセルボタンを押した)場合、エラー処理を行います

On Error GoTo Err_名前を付けて保存

'別の名前でファイルを保存します
'まず、保存するファイル名を取得するための変数を定義します

Dim ファイル名 As Variant

'フィル名をつけて保存ダイヤログボックスを表示させます
'保存するファイルの種類をエクセルファイルに限定します

fileSaveName = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel ブック(*.xlsm),*.xlsm")

'保存するファイル名が有効な名称であれば

If fileSaveName <> False Then

'変数にダイヤログボックスで付けた名称を代入します

ファイル名 = fileSaveName

End If

'代入した名称で保存します

ActiveWorkbook.SaveAs (ファイル名)


'ブックを閉じるときにエクセルも終了するかどうかを選択します
'選択結果を取得するための変数を定義します
Dim メッセージ2 As Integer

'メッセージボックスを表示して、選択されたボタンに応じて分岐処理を行います

メッセージ2 = MsgBox("Excelを終了せずにこのブックだけを閉じます。" & Chr(13) & Chr(13) & _
"このブックだけを閉じて引き続きExcelを使う場合は「はい」を、" & Chr(13) & _
"Excelも終了する場合は「いいえ」をクリックしてください。", vbYesNo, "作業の終了")

'「はい」を選択した場合

If メッセージ2 = vbYes Then

'ブックだけを閉じます

ActiveWorkbook.Close

'「いいえ」を選択した場合

Else

'エクセルを終了します

Application.Quit

'分岐処理を終了します

End If

'エラー処理についての記述です

Err_名前を付けて保存:

'キャンセルボタンが押されたときのメッセージを表示します

If Err.Number = 1004 Then

MsgBox "保存処理がキャンセルされました。" & Chr(13) & _
"一連の処理を中止します。"


'エラー処理を終了します

End If


End Sub 

 

 コードの入力が終わると、以下の図のようになるはずです。なお、図の中で、緑の文字列の部分は「マクロのコードについてのメモのようなもの」でマクロの動作には直接影響を与えません。ゆえに、この部分はなくてもよいのですが、後でメンテナンスをするときに何のためにそのコードを設けたのかを記録しておいた方がわかりやすいというメリットがあります。ただし、これがあると却って鬱陶しいという人もいると思うので、その辺は好き好きというものでしょう。


c0136904_12255937.jpg




2.コードの説明
(1)マクロ「日付入力」

Sub 日付入力()
'
' 日付入力 Macro
'
'
Range("j3").Value = Now


End Sub


 これは、そのブックを更新した日時を記録するためのコードです。セルj3に現在の日時を入力するというごく単純なコードになります。具体的には、Range("j3")はセルj3を指定するコードで、Range("j3").Value と書くことで「セルj3の値」という意味になります。また、NowというのはNow関数のことで、現在の日時を返します。すなわち、「セルj3の値は現在の日時ですよ」とExcelに教えているわけです。
 Nowの代わりに、Dateを用いれば本日の日付となりますし、"私は真っ赤なリンゴです"とすれば、セルj3には「私は真っ赤なリンゴです」という文字列が入力されるようになります。
なお、Sub 日付入力()は、「日付入力」というマクロの開始を、End Subはマクロの終了を示す合図です。

2.マクロ「別名で保存」
マクロ「別名で保存」は下図のような構造になっています。


c0136904_1228131.jpg



 最初に、更新日時を取得するためのマクロ「日付入力」を実行させるために、Call 日付入力 というコードを記述しています。このように、Call というのは他のマクロを呼び出して実行するためのものですから、応用範囲は広いものになります。
 
 次に、「名前をつけて保存」ダイヤログボックスを表示させるわけですが、マクロを記述する手順として、次の「エラー処理」を先に書いておきます。これは、「名前をつけて保存」ダイヤログボックスが表示されたときに、何らかの理由でキャンセルされたときにどうするかをという取り決めをしておくというもので、マクロの最初のところには、下記のように、「エラーが発生した(キャンセルボタンを押した)場合は途中のコードを無視してエラー処理のところに行きなさい」という命令を書き込むことになっています。また、エラー処理の具体的な中身は、マクロの最後のところに書くことになっています。


'エラーが発生した(キャンセルボタンを押した)場合、エラー処理を行います

On Error GoTo Err_名前を付けて保存



 次に、作業したブックに「別な名前をつけて保存する」わけですが、単に「名前をつけて保存する」だけであれば、
fileSaveName = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel ブック(*.xlsm),*.xlsm")だけで充分です。ここで、(fileFilter:="Microsoft Excel ブック(*.xlsm),*.xlsm")というのは、ファイルの種類をExcelのブックに限定するというものです。Excel2003以前のものをお使いの方は、この部分を「xslm」ではなく「xls]と訂正してお使い下さい。
 ところが、それだと「別な名前で保存した」後で原本となるブックを閉じたり、Excelを終了させるときに、原本となるブックに加えられた変更を保存しますか?という問い合わせをしてきます。このときに「保存しない」を選択すれば原本となるブックは元のままですが、間違って「保存する」を選択してしまうと原本が改訂されてしまうことになります。
 そこで、少々迂遠ですが、以下のようなコードを書くことで、最後の確認のメッセージを表示させないようにすることができ、誤操作を防ぐことができるようになります。


'別の名前でファイルを保存します
'まず、保存するファイル名を取得するための変数を定義します

Dim ファイル名 As Variant

'フィル名をつけて保存ダイヤログボックスを表示させます
'保存するファイルの種類をエクセルファイルに限定します

fileSaveName = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel ブック(*.xlsm),*.xlsm")

'保存するファイル名が有効な名称であれば

If fileSaveName <> False Then

'変数にダイヤログボックスで付けた名称を代入します

ファイル名 = fileSaveName

End If

'代入した名称で保存します

ActiveWorkbook.SaveAs (ファイル名)



 次に、ブックを閉じるときに、Excelもいっしょに終了させるのか、単にブックだけを閉じるのかを選択できるようにします。そのための方法として、質問に対し「はい」か「いいえ」で答えることで、その返答によって処理を分岐させるというやり方があります。以下は、そのための構文で定型的なものです。
 まず、最初に、質問に対する返答を変数として扱えるようにするために、変数の型の定義を行います。ここでは、「はい」か「いいえ」のどちらかなので、整数型である「Integer」としています。

'ブックを閉じるときにエクセルも終了するかどうかを選択します
'選択結果を取得するための変数を定義します

Dim メッセージ2 As Integer


 次に、メッセージボックス(「はい」か「いいえ」を選択する)の返答を先ほど定義した変数であるとするために、メッセージボックス関数を使います。
 メッセージボックス関数の構文は次の通りとなります。

 MsgBox(表示するメッセージ,メッセージボックスのタイプ,メッセージボックスのタイトル)

 表示するメッセージは文字列となりますから最初と最後を”で囲むのがお約束です。メッセージが長いとき、途中に、Chr(13)というのは改行を指示するコードを書くことがあります。(文字列にくっつけて記述するので、実際には&Chr(13)と書きます。)こうすると、メッセージボックスが表示されたときに、メッセージはこの部分で改行されるようになります。次に、メッセージボックスのタイプですが、通常は「vbYesNo」を使います。さらに、メッセージボックスのタイトルとして、ここでは「作業の終了」という指定をしています。
 なお、下記のコードの途中にある 「&_」は、1行のコードの文字数が増えて長くなると見にくくなるので、次の行に(コードを)改行しているけれども、まだ終わってはいないという意味の記号です。


'メッセージボックスを表示して、選択されたボタンに応じて分岐処理を行います

メッセージ2 = MsgBox("Excelを終了せずにこのブックだけを閉じます。" & Chr(13) & Chr(13) & _
"このブックだけを閉じて引き続きExcelを使う場合は「はい」を、" & Chr(13) & _
"Excelも終了する場合は「いいえ」をクリックしてください。", vbYesNo, "作業の終了")


 以下は、メッセージボックスへの返答によって、処理を分岐させるためのコードです。If 条件  Then  処理1 Else  処理2  End If という構文になります。If関数と似ていることがおわかりいただけると思います。


'「はい」を選択した場合

If メッセージ2 = vbYes Then

'ブックだけを閉じます

ActiveWorkbook.Close

'「いいえ」を選択した場合

Else

'エクセルを終了します

Application.Quit

'分岐処理を終了します

End If


 最後に、「名前をつけて保存」ダイヤログボックスが表示されたときに、「キャンセル」ボタンをクリックしたときの処理方法について記述します。ここでも、If ~Then という構文が使われています。ただし、「キャンセル」されたときの処理だけを記述すればいいので、Else以下は不要となりここでは省いてあります。
 なお、Err.Number = 1004 というのは、「キャンセル」がクリックされたときのエラーコードです。エラー処理としては、「保存処理がキャンセルされました。一連の処理を中止します。」というメッセージを表示して、マクロを終了させるというものです。ここでもメッセージボックスを使っていますが、先ほどのメッセージボックスが関数(「はい」か「いいえ」の返答を取得するため)であったのに比べ、こちらの方は単にメッセンジャーとしての役割だけなので、コードの書き方が違っています。表示させるメッセージを書いているだけで、メッセージボックスのタイプ指定も省略しています。(タイプを省略すると「OK」ボタンが表示されるようになります。)

'エラー処理についての記述です

Err_名前を付けて保存:

'キャンセルボタンが押されたときのメッセージを表示します

If Err.Number = 1004 Then

MsgBox "保存処理がキャンセルされました。" & Chr(13) & _
"一連の処理を中止します。"

'エラー処理を終了します

End If

End Sub 



3.まとめ
 今回ご紹介したマクロは、Excelで作成したブックのパーツとして利用することができます。すなわち、ご自分で作成したブックに貼り付けることで、更新日時が自動入力され、ブックを別名で保存してから閉じることができるようになります。
 今回ご紹介したマクロは、ドラクエの呪文を覚えるよりは難しいと思いますが、マクロの行数そのものは決して多いわけではないので、高校生程度の英語力と根気があれば、充分ご理解いただけるものと思います。
by t_am | 2013-02-02 12:33 | Excel のあの手この手