Excelの外部リンク（外部参照）について調べてみた

私はシステム関連のサラリーマンなので、普段の業務でExcelをバリバリ使っており、そこそこ使える方だと思っている。ただ、自分が作る資料では他のブックのセルの参照、いわゆる外部リンクは使わないので、たまに、他人が作成したExcelを開いた時に
「このブックには、安全ではない可能性のある外部ソースへのリンクが1つ以上含まれています。」
などの警告が出ると、（ほとんどの場合は作成者が意図とは別にリンクが残っているケースなので）「ちゃんと後始末しておけよ」という思いでイラッとしてしまう。

ところが、最近、意図して外部リンクを使っている奴とファイルを共有することが多くなったのだが、そのファイルの改廃において、外部リンクのパスが勝手に変わったりする現象に出くわしたことから、この外部リンクなるものの仕様・挙動を自分なりに調べてみた。

本記事ではその調査結果について記載する。

調査したExcelのバーションは 365 であるが、ざっと見た限りでは 2016でも同じ動きをするようだ。

【用語について】

• 本記事ではExcelファイルしか現れないため、ブックと呼ばず全てファイルに統一する。
• パスは、ドライブ、フォルダ、ファイル名までを含む。
• ファイルAの中にファイルBを参照する式を記載していた場合、私の感覚では「ファイルAがリンク元、ファイルBがリンク先」なのだが、Excelの用語ではファイルBをリンク元と呼んでいるので、本記事の記載もそれに合わせる。一方、ファイルAを本記事では「基準ファイル」と記す。

　
【外部リンクのあるExcelファイルを開くときの警告】

本題に入る前に、外部リンクを含むExcelファイルを開くときに表示される警告について説明する。

• 「セキュリティの警告：リンクの自動更新が無効にされました」が上部に帯状に表示されることがある。
  
  
  
  ここでとり得る選択肢と動作は以下の通りである。
  
  1. 「コンテンツの有効化」ボタンを押下　⇒　リンク元のファイルの内容を参照して、基準ファイル内の外部リンクを含むセルの値を最新のものに更新する。
  2. 帯の右端の X印を押下して帯を消す　⇒　内容を更新せずに基準ファイルを開く。
     
     
• 「このブックには、安全ではない可能性のある外部ソースへのリンクが1つ以上含まれています。リンクを信頼できる場合、リンクを更新して最新データを取り込みます。信頼できない場合は、データをそのまま手元で処理してかまいません。」というダイアログが表示されることがある。
  
  
  ここでは以下の選択肢がある。
  
  
  1. 「更新しない」を押下　⇒　内容を更新せずに基準ファイルを開く
  2. 「更新する」を押下　⇒　リンク元のファイルの内容を参照して、基準ファイル内の外部リンクを含むセルの値を最新のものに更新する。
     ただし、外部リンクにリンク切れがある場合（リンク元のファイルが見つからない場合）
     「ブック内の一部のリンクはすぐに更新できません。値を更新せずにそのままにするか、間違っていると思われるリンクを編集してください。」のダイアログがでる。
     
     
     
     ここでは以下の選択肢がある。
     
     
     • 「続行」を押下　⇒　内容を更新せずに基準ファイルを開く。
     • 「リンクの編集」を押下　⇒　「リンクの編集」画面が表示される。（後述）

　
これらの警告を乗り越えると、やっと基準ファイルを開くことができる。

それでは、外部リンクの挙動の説明に入る。

【説明のために用意したファイル】

以下の4ファイルを準備した。

G:\Excel_test\基準フォルダ　というフォルダの直下に「基準ファイル」と名付けたExcelファイルを置き、そのファイルの中に、以下をそれぞれ参照する式を記載した。

1. サブフォルダに置いたExcelファイルの参照
2. 上位フォルダに置いたExcelファイルの参照
3. 他のドライブ(E:\temp)に置いたExcelファイルの参照

上記のリンク元の3ファイルの内容も記載しておく。

 左から、サブフォルダ、上位フォルダ、他ドライブのファイルの内容
    

【基準ファイル内の外部リンクの式の表記】

まず、基準ファイルだけを開いた場合の外部リンクの計算式の表記を見てみると、

=ドライブレター:\フォルダのパス\[ファイル名]シート名!$A$1

のように、フルパス＋シート名＋セル番地 で記載されている。

基準ファイル内の計算式

基準ファイル内の値表示

なお、上図の計算式と値の表示切替は
「ファイル」－「オプション」－「詳細設定」－「次のシートで作業する場合の表示設定」の「計算結果の代わりに数式をセルに表示する」のチェックをON/OFFすることで切り替えている。この後の図でも、特に記載なしに適宜このチェックをON/OFFして表示を切り替えているので留意して欲しい。

 「計算結果の代わりに数式をセルに表示する」 
 

【リンク元のファイルを開いた場合】

上記の状態から、リンク元のファイルも開くと、その瞬間に基準ファイルの計算式は以下のように変化する。

すなわち、フォルダ部分は表示から消え、ファイル名＋シート名＋セル番地　だけとなる。
※なお、上図は、3ファイルとも開いた場合。当然ながら、開いたリンク元ファイルを参照している式だけがこのように変化する。
リンク元のファイルを閉じると、その瞬間に元のフルパス付きの表現に戻る。

また、基準ファイルとリンク元のファイルを同時に開いた状態から、リンク元ファイルを「名前を付けて保存」で別のパスやファイル名で保存すると、その瞬間に基準ファイル内の外部リンクの計算式もそれに変更される。

　
【外部リンク内のパスやシート名を手動で変更しようとした場合の挙動】

基準ファイル内の外部リンクのパスを手で修正しようとした場合、以下のケースに分かれる。

• 修正しようとした外部リンクのファイルが存在し、かつ、シート名も存在する場合、修正は成功するとともに、その値が読み込まれ更新される。
• 修正しようとした外部リンクのファイルは存在するが、シート名が存在しない場合、条件は不明だが以下の２つのケースがあった。
  　
  
  1. シート名は変更されず（手修正が反映されず元のまま）、何も起こらない（値も変わらない）。
  2. 「シートの選択」というダイアログが現れ、実際に存在するシートの一覧が表示されるので、その中から１つ選択する。その結果、外部リンクのシート名は変更され、値は、そのシートの現在の値に変更される。
     「シートの選択」でキャンセルを選択した場合、シート名は手修正による変更が反映されたまま（存在しないシート名のまま）で、値は #REF! になる。
     　　
     シートの選択
     
     　　
     　
• 修正しようとした外部リンクのファイルが存在しない場合、「値の更新」というファイルを選択するダイアログが現れるので、以下のどれかを行う。なお、外部リンクのパスは、以下のどの場合でも、修正しようとした「存在しないパス」に変更される。
  
  
  1. ファイルダイアログでキャンセルした場合　⇒　何も起こらない。（外部リンクのパスは修正されたままで、表示（値）は #REF! になる。
  2. ファイルダイアログで、存在するファイルを指定した場合は、以下のケースに分かれる。
     
     
     • 指定したファイル内に外部リンクに記載したシート名が存在する場合　⇒　そのシートが採用され、表示（値）はその値に更新される。ただし、外部リンクのパスは修正されたまま（存在しないパスのまま）、シート名は正しい（存在する）名前に変更される。
     • 指定したファイル内にシートが１つしかない場合　⇒　（シート名の一致、不一致にかかわらず）そのシートが採用され、表示（値）はそのシートの値に更新される。その結果、外部リンクのパスは修正されたまま（存在しないパスのまま）、シート名は正しい（存在する）名前に変更される。
     • 指定したファイル内にシートが複数存在し、外部リンクに記載したシート名が存在しない場合　⇒　「シートの選択」というダイアログが現れて、実際に存在するシートの中からシートを１つ選択する。その結果、外部リンクのパス内のシート名は選択したシート名に変更される。その結果、外部リンクのパスは修正されたまま（存在しないパスのまま）、シート名はダイアログで指定したシート名に変更され、かつ表示（値）は当該シートの値に更新される。
       「シートの選択」ダイアログでキャンセルを押下した場合、シート名は変更されず存在しないシート名のままで、値は #REF! となる。

　
【Excelの変換機能で外部リンクの計算式内のパスやシート名を変更しようとした場合の挙動】

Excelの変換機能で、外部リンクのパスを存在しないパスやシート名にに変換しようとした場合の挙動は、基本的には手修正の挙動と同じで、それが変換個所の数だけ繰り返される。
ただし、途中のダイアログでキャンセルを選択すると、そこで変換処理は中断され、残った変換個所は処理されない。

 
【フォルダーをコピーまたは移動した場合の挙動】

外部リンクを含むファイル（基準ファイル）を、（ファイル単体ではなく）そのファイルを含むフォルダごと他の場所へコピーした場合は、以下のような挙動となる。

• リンク元のファイルも同時にコピーされる場合（基準ファイルとリンク元ファイルの両方が、コピーするフォルダ配下にある場合）
  　⇒　基準ファイルとリンク元ファイルの相対関係が維持される形で、外部リンクのパスもコピー先のパスに変更される。
• 上記以外の場合　⇒　外部リンクのパスは変わらない。

図で示すと以下となる。（G:\Excel_test フォルダを E ドライブ直下にコピーした場合）
図の実線矢印が前者、点線矢印が後者の場合である。

 

なお、フォルダを移動した場合の挙動もコピーの場合と同じである。
また、フォルダごとではなく、基準ファイルだけを単独で別の場所へコピーまたは移動した場合は、外部リンクのパスは変更されない。

以上が、私が調査した、外部リンクの挙動である。

以下では、外部リンクに関するExcelの機能「リンクの編集」について記載する。
他の人が送ってきたファイルなど、構造はわからないけど外部リンクの警告がでるようなものについては、まずこの機能で外部リンクの状況を把握すると良い。
「リンクの編集」は以下の操作で表示される。

 「データ」－「クエリと接続」グループ－「リンクの編集」
 

【リンクの編集】

リンクの編集ダイアログを開くと、使用している外部リンクの一覧が表示され、最初は「状態」は全て「不明」となっている。
ここで右側のボタン押下で以下の操作が行える。
1 ～ 4 は選択した行に対する操作で、5 は全行に対する操作となる。

1. 値の更新
   外部リンクが示すファイルを参照して値を更新しようとし、成功すれば「状態」は「OK」となる。
   ファイルやシートが存在しなかった場合の挙動は、【外部リンクの計算式内のパスやシート名を手動で変更しようとした場合の挙動】と同じである。例えば、ファイルが存在しない場合は、「値の更新」というファイルダイアログが現れるので、ファイルの指定を行う。ただし、ファイルを指定しても、外部リンクのパスが変わらない点も同じである。
2. リンク元の変更
   「リンク元の変更」というファイルダイアログがでてくるので、リンク元のファイルを指定する。また、外部リンクに記載したシートが存在しない場合は、「シートの選択」ダイアログでシートを選択する。その結果、外部リンクのパスやシート名は指定されたものに変更され、値が更新されて状態が「OK」となる。
3. リンク元を開く
   リンク元のファイルを開く。リンク切れの場合（リンク元ファイルが存在しない場合）は、「申し訳ございません。[外部リンクのパス名の表記]が見つかりません。名前が変更されたか、移動や削除が行われた可能性があります。」というエラーが表示される。
4. リンクの解除
   外部リンクを値に変更する。（外部リンクの情報は削除されるので注意）
   リンク切れの場合（リンク元ファイルが存在しない場合）は、値が「#REF!」に変更される。
5. 状態の確認
   外部リンクの状態を確認して（ファイルやシートの存在を確認して）、「状態」欄を以下の通り更新する。　
   
   • ファイルが存在しない場合　⇒　「エラー：ソースが見つかりません」
   • ファイルは存在するが、シートが存在しない場合　⇒　「エラー：シートが見つかりません」
   • ファイルとシートが存在し、そのファイルが開かれている場合　⇒　「リンク元は開かれています」
   • ファイルとシートが存在するが、まだ値を読み込んでいない（更新していない）場合　⇒　「警告! 他のブックを参照している値は更新されませんでした」
   • ファイルとシートが存在し、既に以前の操作で値を読み込んでいる場合　⇒　「OK」

　
利用者目線で言うと、その外部リンクを使用している場所（セルなど）を知りたいのだが、残念ながら検索で調べる以外に方法はないようだ。

ブック内のリンク (外部参照) を検索する

　
では。

outlookのメール一覧をExcelに貼り付けるためのVBScriptを作ってみた

前回の記事で、outlookから msgファイルと添付ファイルを抽出するVBScriptを公開したが、その応用編として、メールの件名や内容の一覧をExcelに貼り付けるVBScriptを作成したので公開する。
スクリプトの大半は前回のものの流用であるが、添付ファイルやWindowsのフォルダを扱わないのでこちらの方がシンプルである。

それでは、まず仕様から。

• outlook上の特定メールフォルダに格納されているメールを対象とする。従って、実行前に対象メールを「特定メールフォルダ」にコピーする必要がある。
  【2019年5月2日追記】
  対象メールを「outlookで選択されているメール」に改良したバージョンを作成したので、興味のある方は本記事の文末を参照。
• 本スクリプトではExcelへの貼り付け自体は行わず、タブ区切りの一覧をクリップボードにコピーするまでを行う。従って、本スクリプトを実行した後、Excelの任意のセルを選択して貼り付け操作をすれば一覧がExcelに展開される。
• 貼り付けられる項目は、受信日時、件名、送信者、本文 とした。

以下、本スクリプトの利用手順である。

1. 対象ファイルを outlookの受信フォルダ直下の「export」フォルダにコピーする。フォルダ名はスクリプトの10行目で定義しており変更は可能だが、事前に決めておく必要がある。
2. 本スクリプトをダブルクリックで実行する。
3. outlookへのアクセス許可を求める以下のダイアログが表示されるので、「許可」を選択する。
   
   　
4. Excelの任意のセルを選択し、貼り付け操作（Control-vなど）を行う。
   　

テストした環境は Windows7と Office2010の組合せのみなのであしからず。
スクリプト（コードはSJIS）は以下のリンクからご自由に。（右クリックして保存）

「outlook2tsv.vbs」をダウンロード

読みにくいが、内容は以下の通り。

---

Option Explicit

Dim objOA, objNS, objOLFolder, objItm, objWS
Dim Mystring

Const olFolderInbox = 6
Const ErrNoOLFolder = -2147221233

'outlook の対象フォルダ名（受信トレイの下）
Const oLFolderName = "export"

Mystring = "受信日時" & vbTab & "件名" & vbTab & "送信者"& vbTab & "本文" & vbCrLf

Set objOA = CreateObject("Outlook.Application")
Set objNS = objOA.GetNamespace("MAPI")

Err.clear
On Error Resume Next

Set objOLFolder = objNS.GetDefaultFolder(olFolderInbox).Folders(oLFolderName)

If (Err.Number = ErrNoOLFolder) Then
  MsgBox "指定した outlook のフォルダ " & oLFolderName & " が存在しません"
  WScript.Quit
End If

On Error Goto 0

For Each objItm In objOLFolder.Items
  Mystring = Mystring & objItm.ReceivedTime & vbTab & objItm.Subject & vbTab & objItm.Sender & vbTab & """" & objItm.Body & """" & vbCrLf
Next

Set objWS = CreateObject("WScript.Shell")
objWS.Exec("clip").StdIn.Write Mystring

---

では。

【2019年5月2日追記】

以下の改良を行ったバージョンを作成したので、興味のある方は下記リンクから。（右クリックして保存）

• 対象メールを「outlookの特定メールフォルダ下にあるメール」から「outlook上で選択されているメール」に変更
• メール本文がHTML形式の場合、異常終了する不具合に対応（ただし、HTML形式の場合、 "?"などの不正文字が残ることがある）

ダウンロード - export_mails_from_outlook_in_tsv_format.vbs

エクスプローラでファイル名をクリップボードにコピーする

Windowsのエクスプローラを使っていて「何でこんなこともできないんだ 」と叫びたいことが何度もあるが、その最たるものが、ファイル名のクリップボードへのコピーである。
フリーソフトを使えば何とかなるのであるが、会社のPCにはインストールできない。

コマンドプロンプトを開いて dir を叩くという手もあるがやはり面倒くさい ・・・
などなど思いながら、いろいろググってみると、ファイルなどを選択した状態で右クリックでメニューが表示されるが、Shiftを押しながら右クリックすると、現れるメニュー項目が増え、その中の「パスのコピー」を使えばフルパスの情報がクリップボードにコピーされることが判った。

以下は1フォルダと1ファイルを選択して実行した例である。

　"E:\test_folder\folder1"
　"E:\test_folder\file01.txt"

確かにこれでも悪くないのだが、自分の好み的には以下の点が不満であった。

• パス名（上の例では E:\test_folder\folder1 ）は最初に1行あればよく、あとはファイル名やフォルダ名だけの一覧の方が良い
• 両端のダブルコーテーションはいらない

しばらくはこれで乗り切っていたのだが、やはり何とかならないかと再度ググってみたところ以下の記事を発見。

　Palm84 某所の日記 - 「送る」でファイル名をごにょごにょするバッチファイル

エクスプローラの右クリックメニューの「送る」と自作のバッチファイルを組み合わせて、フルパスやファイル名などの一覧をテキストファイルに出力する例が4種類公開されている。
仕様的に私の欲しいものとは違っているのでこのまま使わせてもらおうとは思わなかったが、『「送る」とバッチファイルの組合せで欲しいものができるのでは？』というのは大きな発見であった。

ということで、これを参考にバッチファイルの自作にチャレンジしてみた。
以前の記事「ＲＥＧＺＡが来た　～写真と動画再生編～」でも10行程度のWindowsバッチファイルの自作にチャレンジし、その文法の謎仕様で苦戦したことを書いたが、今回もメチャクチャに苦戦し、結局都合1日（8時間）以上費やした気がする。

それでも何とか試行錯誤しながら 3種類のバッチを完成した。
　

■共通仕様や言い訳など

1. エクスプローラ上で対象とするフォルダやファイルを選択し（複数選択可）、右クリックメニューで「送る」⇒サブメニューを選択することで実行する。
2. 結果はクリップボードにコピーされる。
3. 実行中はコマンドウィンドウが表示される。
4. 内部的には 環境変数 %TEMP%フォルダに一時ファイルを作成している。（実行終了時に削除）
5. バッチ内にはコメントは入れていないが、高々数十行なのでご容赦いただきたい。
6. ループ処理に for文を使ったり goto文を使ったり、フォルダか否かの判断に 属性を見たり exist文を使ったり、などなど、識者から見ると謎の記述になっていると思うが、私にも謎である 。 会社でのドメイン/ファイルサーバ利用の環境下でも動作するように修正していった結果、結局この記述になってしまった ・・・

3については、表示されない方が好みの方もいると思うが、

• 表示しないようにするのは難しい（webの情報を見てチャレンジしたがやはり一瞬表示される）
• サブフォルダ以下も階層的にファイル情報をコピーする機能では時間がかかる場合があり、処理終了タイミングを把握するためにウィンドウ表示があった方が良い

という理由から今の仕様とした。

各バッチの仕様と実行例は以下の通りである。

■実行例に使ったフォルダ構成

興味半分でジャンクションやシンボリックリンクも張っているが（参考：ジャンクションを使ってみる（その１））、興味のない方は無視してもらって構わない。
ファイルサイズは、ファイル名の下1桁が1のものは 200バイト、2のものは 500バイトである。
　
【フォルダ構成】


エクスプローラとdirコマンドでは以下のように表示される。
　※ショートカットのサイズは何故か 1131バイト、ファイルのシンボリックリンクのサイズは空白

　

　
■3つのバッチの仕様と実行例

　
１．フォルダ名とファイル名をコピー

1行目にパス名、2行目以降に選択したファイルやフォルダの名前の一覧をコピーする。（フォルダ以下は見ない）
　
【実行例】　
test_folder直下の全フォルダとファイルを選択した場合、以下がクリップボードにコピーされる。

　E:\test_folder\
　folder2
　folder2_SL
　file01.txt
　file02.txt
　file11_HL.txt
　file12_SL.txt
　file21.txt - ショートカット.lnk
　folder1
　folder1_JC
　
　
２．Excel用ファイル情報をコピー

Excelにそのまま貼り付けできるよう、パス、ファイル名、サイズ、更新日時をタブ区切りでコピーする。コピー対象ファイルは、以下のとおりである。

• 選択されている対象がフォルダ1つの時は、そのフォルダ以下の全ファイル（サブフォルダ以下も全て表示、フォルダ自体の行はなし）
• 選択されている対象がそれ以外の場合は、選択されているフォルダとファイル（フォルダ以下は見ない）

【実行例１】　
test_folder フォルダを1つ選択した場合、以下がクリップボードにコピーされる。（そのままExcelに貼り付けた例）
　※ジャンクションやシンボリックリンクは実際に実体があるかのように表示される

　

【実行例２】
test_folder直下の全フォルダとファイルを選択した場合

　

　
　
３．tree情報をコピー

選択されている対象がフォルダ1つの時のみ dosコマンドの tree文の結果をコピー、それ以外が選択されているときはエラー。

【実行例】　
test_folder フォルダを1つ選択した場合、以下がクリップボードにコピーされる。
※ジャンクションやシンボリックリンクは実際に実体があるかのように表示される
　

　フォルダー パスの一覧:  ボリューム XXXXXXXXXX
　ボリューム シリアル番号は XXXXXXXXXXXXXX です
　E:\TEST_FOLDER
　│  file01.txt
　│  file02.txt
　│  file11_HL.txt
　│  file12_SL.txt
　│  file21.txt - ショートカット.lnk
　│ 
　├─folder1
　│      file11.txt
　│      file12.txt
　│      
　├─folder1_JC
　│      file11.txt
　│      file12.txt
　│      
　├─folder2
　│      file21.txt
　│      file22.txt
　│      
　└─folder2_SL
　        file21.txt
　        file22.txt

　
■各バッチファイルのダウンロード

以下のリンクを右クリックし、メニューから「名前を付けてリンク先を保存」や「対象をファイルに保存」などを選択しダウンロードする。（文字コードは SJISを使っているため web上で表示した場合は文字化けするので注意。）

「1_copy_filenames.bat.txt」をダウンロード

「2_copy_file_info_for_Excel.bat.txt」をダウンロード

「3_copy_tree_info.txt」をダウンロード

　
■実装手順

1. 使いたいバッチファイルをダウンロードし、ファイル名の最後の「.txt」を削除してから保管したいフォルダに移動する。
2. エクスプローラでパスを入れるところで「shell:sendto」と入力して、SendTo フォルダを開く。
   私の場合（Windows10 Home）は以下のフォルダである。
   　　C:\Users\ユーザ名\AppData\Roaming\Microsoft\Windows\SendTo
3. 1のバッチファイルのショートカットを 2の SendToフォルダの直下に作成し、名称を判りやすいものに変更する。
   「送る」メニューは名前順に表示されるので、なるべく並んで表示されるよう、先頭の名称を合わせた方が良い。
   私の場合、以下のように「copyN：」を先頭に付けている。

　
　

　
今、会社でこれらのバッチを使用しているが、なかなかいい感じだ

では。
　

【2020年5月6日追記】
　
２．Excel用ファイル情報をコピー
３．tree情報をコピー

のスクリプトに、パスに空白が含まれている場合に正しく動作しないという不具合があったので修正した。

【2020年5月6日追記】

VBScript で動作する改良版を作成したので興味のある方は以下の記事へ。

エクスプローラからファイル一覧を出力するVBScript（検索結果もOK）

Excelの荒技（その５） 行の高さを調整するVBA（その２）

前回の「行の高さを調整するVBA」の続きである。

前回、行の高さを調整する方法として以下の３つを挙げ、1番目について VBAのコードを掲載した。

方法１： 現在の高さの1.1倍とする
方法２： セルの最後に強制的に改行を挿入した後、高さを自動調整する
方法３： 列幅を0.8倍に変更 => 高さを自動調整 => 列幅を元に戻す

今回は残り全部、即ち、方法２と３のコードと、それらをアドイン化してメニューバーから起動する方法について説明する。
最後に、アドインファイルもアップする。
従って、コードの内容に興味がなく、「行の高さを調整するVBA」を試してみたいだけの人は、下の方の「２．作成したアドインファイルの存在を Excel に認識させる。」から見てもらってもよい。

方法２と３のコードを以下に示す。
なお、コメント「選択範囲のチェック～ＸＸＸＸＸ　をコピー」のところには、前回載せたコードの該当行をそのままコピーしてくれば良い。
また、表示幅の関係で、行の途中で改行されている箇所もあるので注意。

方法２

Private Sub 改行挿入()
    Dim a As Range
    Dim lastCell_Row As Long
    Dim sdHeight As Double
   
    '選択範囲のチェック～最終有効行　をコピー
    For Each a In Selection
        '最終有効行に達したら終わり
        If a.Row > lastCell_Row Then
            Exit Sub
        End If
        If VarType(a.Value) = vbString And Len(Trim(a.Value)) > 0 And a.RowHeight > sdHeight Then
            '最後に改行を挿入
            a.Value = a.Value & Chr(10)
            a.Rows.AutoFit
        End If
    Next a
   
End Sub

方法３

Private Sub 幅縮小拡大()
    Dim a1, a2 As Range
    Dim lastCell_Col As Long
    Dim rate As Double
   
    '幅の縮小率
    rate = 0.8
   
    '選択範囲のチェック～画面の更新を止める　をコピー

    '最終有効桁
    lastCell_Col = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

    For Each a1 In Selection.Areas
        For Each a2 In a1.Columns
            '最終有効桁に達したら終わり
            If a2.Column > lastCell_Col Then
                Exit For
            End If
            a2.ColumnWidth = a2.ColumnWidth * rate
        Next a2
    
        a1.Rows.AutoFit
       
        For Each a2 In a1.Columns
            '最終有効桁に達したら終わり
            If a2.Column > lastCell_Col Then
                Exit For
            End If
            a2.ColumnWidth = a2.ColumnWidth / rate
        Next a2
       
    Next a1
   
End Sub

なお、前回、方法１のコードを載せたが、倍率（例えば 1.1倍）のパラメータ付きで呼ばれる形式にしていたので、以下に「呼び側」のコードも載せておく。

Public Sub 行高さ定率変更X11()
    行高さ定率変更 (1.1)
End Sub

それぞれのプログラムの内容については、見ればだいたい判ると思うので特に説明はしない。Range オブジェクトに関する Columns や Rows の使い方が私自身も怪しいが、とりあえず動作するようなので良しとしよう。

さて、これらのプログラムをアドイン化して Excel のファイルメニューから起動できるようにする方法に移る。

手順としては、以下の流れになる。
１．アドインファイル(.xla)を作成する。
２．作成したアドインファイルの存在を Excel に認識させる。
３．アドインを有効にする。

早速、順に示す。
なお、手順１でアドインファイルを作成する方法を示すが、この記事の最後に置いてあるアドインファイル「行高さ調整.xla」を使うのであれば、手順２から始めれば良い。

１．アドインファイル(.xla)を作成する。

Excelを開き、[ツール]->[マクロ]->[Visual Basic Editor]で、Microsoft Visual Basic ウィンドウを表示する。ここで、左側の「プロジェクト」のサブウィンドウの「ThisWorkbook」をダブルクリックして ThisWorkbook ウィンドウを表示させ、そこに以下のコードを貼り付ける。

Private Sub Workbook_AddinInstall()
    '新たにMenuBarを追加する
    Set Menu = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
    'MenuBarの名前
    Menu.Caption = "行高さ調整(&G)"

    'MenuBarにサブMenuを追加
    Set SubMenu1 = Menu.Controls.Add
    'サブMenuの名前
    SubMenu1.Caption = "× 1.1(&U)"
    'サブMenuにマクロを登録
    SubMenu1.OnAction = "行高さ定率変更X11"

    'MenuBarにサブMenuを追加
    Set SubMenu3 = Menu.Controls.Add
    'サブMenuの名前
    SubMenu3.Caption = "改行挿入(&I)"
    'サブMenuにマクロを登録
    SubMenu3.OnAction = "改行挿入"

    'MenuBarにサブMenuを追加
    Set SubMenu5 = Menu.Controls.Add
    'サブMenuの名前
    SubMenu5.Caption = "幅縮小拡大(&W)"
    'サブMenuにマクロを登録
    SubMenu5.OnAction = "幅縮小拡大"

End Sub

Private Sub Workbook_AddinUninstall()
    'MenuBarを削除
    Application.CommandBars("Worksheet Menu Bar").Controls("行高さ調整(&G)").Delete
End Sub

上のコードの内容を少し説明すると、手順３でアドインを有効にした際に、Workbook_AddinInstall の内容が実行されて ファイルメニューに 「行高さ調整」とそのサブメニュー項目が追加される。上の例では、1つ目のサブメニュー項目名を「× 1.1」、それを選んだときに実行されるプログラム名を「行高さ定率変更X11」と指定している。
逆に、アドインを無効にした場合は、Workbook_AddinUninstall の内容が実行されて ファイルメニューから「行高さ調整」が削除される。

次に、実行されるプログラムを記述する。
Microsoft Visual Basic ウィンドウで、[挿入]->[標準モジュール]を選択して Module1 と書かれたウィンドウを表示し、ここに、前回および今回記載したコード群を全て貼り付ける。

最後に、[ファイル]->[Book1の上書き保存]を選択し、既定のアドイン格納場所へ 「行高さ調整.xla」 としてファイルを格納する。

アドインの既定の格納場所は以下である。なお、「ファイル名を付けて保存」ダイアログで「ファイルの種類」を「*.xla」とすると、自動的にこのフォルダが表示される。

　　C:\Documents and Settings\ユーザ名\Application Data\Microsoft\AddIns

２．作成したアドインファイルの存在を Excel に認識させる。

通常のExcelのウィンドウに戻り [ツール]->[アドイン]でアドイン画面を表示し、[参照]ボタンからアドインファイル（行高さ調整.xla）を指定すれば良い。
なお、１で書いた既定の格納場所に格納しておけば、Excelを再起動すれば自動的に認識されるので、この操作は不要である。

３．アドインを有効にする。

ファイルメニューの　[ツール]->[アドイン]のアドイン画面で　[行高さ調整]にチェックを入れる。
これで、ファイルメニューに「行高さ調整」という項目が追加され、使用可能となる。

以上である。なお、ファイルメニューから「行高さ調整」の項目を消したい場合は、手順３の逆で、チェックをはずせばよい。

最後に、アドインファイルをアップする。

「行高さ調整.xla （zipで圧縮）」をダウンロード

サブメニューは、以下の5つとなっている。
[× 1.1]  [× 0.9]  [改行挿入]  [挿入取消] [幅縮小拡大]

ここで、
　[× 1.1]　は 方法１
　[× 0.9]　は 方法１の取消し（高さを 1/1.1 倍する）
　[改行挿入]　は 方法２
　[挿入取消]　は 方法２の取消し（行末の改行を削除する）
　[幅縮小拡大]　は 方法３
である。（取消し系のコードは、アドインファイルの中を参照）

なお、最後にお決まりの言い訳。

• Windows XP + Excel 2000 or 2002 の環境を前提にしており、他は確認していない。
• デバッグはあまりしていない。
• 今回の内容は、1週間程度でWEBからかき集めた情報を 自分勝手に解釈して記載しており、書いた本人もよくわかっているわけではない。（キッパリ ）

あしからず。

Excelの荒技（その４） 行の高さを調整するVBA

ずっと以前から仕事でExcelを使っているのだが、使い方というよりも、MSの開発ポリシーの点で、理解できないことが多い。

その代表的なものが、画面上ではちゃんとセル内に収まっているのに、印刷すると字が隠れるという現象だ。これは、私がExcelを使い始めた15年前ぐらいから脈々と受け継がれているExcelの「仕様」で、このお蔭で、行の高さ調整・印刷プレビュー・印刷を延々と繰り返さなければならず、日本中で相当な資源（労力＆紙）が無駄になっているハズだ。

また、ご丁寧にも、行の高さや列幅を自動調整する機能（行間や列間でダブルクリックする）があるが、使っているフォントによっては行の高さが足りず、表示上においてすら見にくい場合がある。印刷用に頑張って高さ調整した後で、間違って「自動調整」してしまった暁には気が狂うしかない。

使わないお節介機能が次々と実装される中で、このような基本的「不具合」が未だに修正されないのは、「MSの社員は実は使っていないのでは？」と勘ぐりたくなる。
と、嘆いては見たものの、今やExcel抜きでは仕事にならないので使い続けている。

最近も、客先から受領したExcelファイルが、行数が多い上に文字がテンコ盛りで表示上も隠れまくっており、高さ調整する気にもならない。（と、言っても結局したが・・・）

行の高さを調整するマクロをWEB上で探してみたのであるが、VBAのサンプルコードっぽいのしか見つけられなかった。（実は、これまで、VBAはさわったこともなかったので、サンプルコードの実装の仕方すら解らなかった・・・ ）

そこで、意を決して、自分で作って見ることにした。
あまり、こんなことに時間を費やしたくなかったので、1週間、会社の休み時間にWEBでVBAのサンプルコードをかき集め、土曜日にそれらを参考に一気に書き上げた。

VBAの文法については、Z80の時代のBASIC言語（古っ！）の記憶とWEB上の情報を参照すれば、それほど悩むことはなかったが、VBA 独特の オブジェクト、特に Selection（Excelのセル選択）の扱いには相当手こずった。

例えば、Excel上で選択されたセルを順に処理するには

For Each 変数 in Selection

というループ処理を使うようなのであるが、この方法では、選択範囲が、単純な１つの矩形領域ではなく複数の跳び地であった場合は、セルを左上から順に拾ってくれず、選択した順番通りに拾われてしまう。その結果、選択の仕方によっては、同じ行や列が何度も繰り返し現れる場合があり、「行の高さを調整する」今回のプログラムのような「行単位の処理」には都合が悪い。
回避策をあれこれ考えたが、凝ったコードを書いてもうまくいかない例外ケースが出てしまうので、結局あまり難しいことは考えずに、（少々の例外ケースには目をつぶって）シンプルにいく方針とした。

主な仕様としては以下とした。

・　選択されたセル（の行）を対象に行の高さを広げる
・　広げ方は以下の3通りとする（1番目だけでもよいかも知れないがVBAの勉強も兼ねて）

1. 現在の高さの1.1倍とする
2. 最後に強制的に改行を挿入した後、高さを自動調整する
3. 列幅を0.8倍に変更 => 高さを自動調整 => 列幅を元に戻す
   　

まだ、ほとんどテストもできていないが、まず 1．のコードを載せておく。

１．現在の高さの1.1倍とする（倍率は可変）

Private Sub 行高さ定率変更(ByVal rate As Double)
    Dim a As Range
    Dim lastCell_Row As Long
    Dim sdHeight As Double
    Dim current_Height As Double
   
    '選択範囲のチェック
    If TypeName(Selection) <> "Range" Then
        MsgBox "対象セルを選択してから実行してください"
        Exit Sub
    End If
   
    '画面の更新を止める
    Application.ScreenUpdating = False
   
    '標準の行の高さ
    sdHeight = ActiveSheet.StandardHeight
    '最終有効行
    lastCell_Row = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

    For Each a In Selection.Rows
        '最終有効行に達したら終わり
        If a.Row > lastCell_Row Then
            Exit Sub
        End If
       
        current_Height = a.RowHeight
        If current_Height <> sdHeight Then
            a.RowHeight = current_Height * rate
        End If
    Next a
   
End Sub

2. 以降は、次回とする。
近いうちに、3. まで含めた xla ファイルをアップする（つもり ）

【2009年4月26日追記】
続き（残りのコードとアドインファイル）をアップしました。
ココです。

では。

Excelの荒技（その３）

3月1日の記事「Excelの荒技（その２）とお奨めのPDF作成ソフト」にて「複数シートでできたブックを１つの文書としてまとめて、割付印刷したり両面印刷する方法」を書いたが、内容に一部間違いがあったので早速元記事を訂正した。
今日は、そのお詫びという訳ではないが、元記事で挙げた方法１と３を組み合わせた新たな方法を発見したので、それについて書く。（奇策なので実用性は？だが・・・）

なお、元記事でも書いた通り、ここに記した方法は、私が web で見つけた情報を参考にして、限られた環境・時間・条件で試してみた結果を記載しているものなので、環境によって異なることもあるかもしれないし、抜けや間違いがあるかもしれないことを、あらかじめ断っておく。なお、操作手順は全て Windows XP の場合である。

さて、本題だ。

方法４・・・pdfを作成せず、かつ「印刷品質」も揃えなくてよい方法

1. 方法１の手順２と同じ。以下に再掲する。
   「スタートメニュー」→「設定」→「プリンタとFAX」　で現れる画面で、「プリンタの追加」を行い、新しいプリンタ（今回印刷に使うプリンタ）を新規に登録する。
   追加したプリンタのアイコンを右クリック→「印刷設定」で、今回印刷したい割付や両面などの設定を行う。
   以前にも書いた通り、新規にプリンタを登録せずに、既存のプリンタに対して設定変更してもよい。が、その場合は、手順２を実行する前に、「印刷」ダイアログでそのプリンタを再度指定しなおした方が良い。
2. 方法３の手順１と２に類似した方法で　対象文書の 印刷イメージファイル（prnファイル ＝ 詳しくは知らないがプリンタが解釈できる形式のファイルらしい）を作成する。
   やり方は、通常の印刷と同様に、「印刷」ダイアログで、 印刷対象を「ブック全体」、 プリンタを 手順1で作成したプリンタ、 「ファイルへ出力」にチェックを入れて「OK」ボタンを押す。
   すると「出力先ファイル名」ウィンドウが開くので、そこに、作成するprnファイル名をフルパスで指定して「OK」ボタンを押す。
   
   入力例：　c:\Document\文書.prn
   
   これで、割付や両面などの印刷設定が反映された印刷イメージファイルができた。理由はわからないが、通常の印刷と異なり、「印刷品質」が揃っていなくても、印刷イメージファイルではページが分かれたりはしていないはずだ。
   
   あとは、印刷イメージファイルをプリンタに送ればよい。
   方法は、キートン増田さんの作った Prnout というフリーソフトを使うのが簡単そうだが、実は私は試していない。（なぜなら、setup.exe でインストールする必要があったから。）
   少し面倒であるが、私は以下の方法で行った。
3. 「スタートメニュー」→「設定」→「プリンタとFAX」で、実際に印刷するプリンタの右クリックメニュー → 「プロパティ」→「共有」タブ 　で「このプリンタを共有する」をチェックし、共有名に適当な名称を設定する。
   
   

   
   
   DOSプロンプトから、以下のコマンドを実行する。
   
   copy  /B prnファイル名   \\コンピュータ名\共有名
   
   入力例：　copy  /B  c:\Document\文書.prn  \\MyPC\testprinter

以上である。

なお、手順３で共有設定するプリンタは、必ずしも手順１で印刷設定したプリンタと同一である必要はないが、機種は同じ（プリンタドライバは同じ）でなければならない。
また、割付や両面などの印刷設定は、手順１と２の段階で印刷イメージファイルに書き込まれているので、手順３で共有するプリンタの印刷設定には影響されない。

では。

Excelの荒技（その２）とお奨めのPDF作成ソフト

今日は、Excelの荒技（その２）である。
前回予告したとおり、「複数シートでできたブックを１つの文書としてまとめて、割付印刷したり両面印刷する方法」について説明する。

これについては、この問題で困ったことのない人には何のことを言っているかわからないと思うので、最初に、問題の背景を簡単に説明する。
ご存知のように、Excelは何枚でもシートを作ることができるので、1つの文書を作成するのに、
表紙を1シート目、第一章を2シート目、・・・　
といったように複数シートに分けて作成することがある。このような文書を印刷する時に、それぞれのシートを別々の用紙に印刷するのであれば問題ないのであるが、全てのシートをつなげて、割付印刷（これはプリンタによって 2in1 とか 集約印刷 とか名称が異なるが、要は1枚の用紙に複数ページ印刷する機能）や両面印刷しようとした場合に、単純に、

• 複数シートを選択して印刷、または、印刷ダイアログで「ブック全体」を指定
• 印刷ドライバの方で割付や両面指定

を設定しただけでは、

• シートが分かれているところで、強制的に用紙も分かれる
• 2シート目以降、割付や両面指定が全く無視され、1枚に片面1ページずつ印刷される

となり、ブックのページ数が多い場合に涙目になることがある。

この現象については、思い通りに印刷できるExcelファイルもあるので、やる方からしてみれば非常に不思議であるし、たまにwebの Q&Aサイトで同じような質問を見ることがあるが、「私の方ではできます。プリンタの設定は確認しましたか？」みたいな、質問した方から見れば「初心者扱いするな～」と叫びたくなるような回答をされていることも多い。

答えから先に言うと、要は、印刷しようとしている Excelファイルに依るのである。（後で説明する 方法１ のように、全シートのページ設定の「印刷品質」の内容を同一に揃えればこの問題は発生しない・・・多分。）

以下、「割付印刷したり両面印刷する方法」について、３つ説明する。それぞれ長所短所があるので、自分のニーズに合わせてうまく使い分けて欲しい。

以下に書いた方法は、web で見つけた情報を参考にして、限られた環境・時間・条件で自分で試してみた結果を記載している。従って、環境によって異なることもあるかもしれないし、抜けや間違いがあるかもしれないので、あらかじめ断っておく。なお、操作方法は WindowsXP を対象に書いている。

本来、これらの情報は、Microsoft がもっと提供しても良いと思うのだが、あまり見たことがない。
Microsoft については、「inside Windows」のような 書籍が出版され売れること自体が、会社として非常に恥ずべきことであると私は思うのであるが、そのような感性はないのだろうか？
また、Windows や Office についても、バージョンアップに投入している開発リソースが、おせっかい機能や単なる見栄えを良くするための機能に費やされ（その結果、全世界のPCのCPU/メモリリソースも費やされる）、肝心の「使い勝手を良くする」という方向とは真逆に向かっているような気がしてならない。
まあ、この点は、また機会があれば、1つの独立した話題として取り上げたいと思う。

方法１・・・Excel だけで済ませる（pdfを作成しない）方法

1. 全シートで、「ページ設定」の「印刷品質」を同一の値に揃える。
   全シートの「印刷品質」の値をワンアクションで揃える方法はなく、各シートを個別に確認・設定するしかないが、通常「印刷品質」は同一の値になっているのでこのハードルは低いと思う。
2. 「スタートメニュー」→「設定」→「プリンタとFAX」　で現れる画面で、「プリンタの追加」を行い、新しいプリンタ（今回印刷に使うプリンタ）を新規に登録する。
   追加したプリンタのアイコンを右クリック→「印刷設定」で、今回印刷したい割付や両面などの設定を行う。
   
   新規にプリンタを登録せずに既存のプリンタに対し、プリンタのアイコンを右クリック→「印刷設定」　で割付や両面などの設定をしてもよい。（ただし、印刷後に戻すのを忘れないように。）　ただし、この方法を使う場合は、Excel文書を印刷する前に「印刷」ダイアログでそのプリンタを（一旦別のプリンタに変えてから）指定し直した方が良いようだ。（そうしないと、「印刷設定」が反映されない場合がある。）
   また、別の手として、新規にプリンタを登録せずに、「ファイル」→「ページ設定」→「オプション」　で 割付や両面などの設定を全シートに対して実施しても良いと推測されるが、私は試していない。この場合、シートの枚数だけ個別に実施する必要があり、全てのシートを選択してから1回だけ実施してもダメである。
   　　
3. 通常の方法で印刷する。この時、プリンタを 2で作成したプリンタとする。

以下に述べる方法２と３は、一旦pdfファイルを作成しそれを印刷する方法なので、方法１に比べ、１つ余計に手間がかかる。
方法１も２も「印刷品質」を揃える手間は同じなので、方法１の手順２のプリンタ操作ができない場合や pdfを作る必要性がある場合を除けば、方法２を選ぶ理由はないように思う。

方法２・・・一旦、pdfを作成する方法（その１）

1. 全シートで、「ページ設定」の「印刷品質」を同一の値に揃える。
   これは、方法１と同じである。
2. 対象文書の pdfファイルを作成する。やり方は、通常の印刷と同様であるが、「印刷」ダイアログで、プリンタを pdf作成用仮想プリンタ（後述）とする。
3. 2でできた pdfファイルを印刷する。通常のファイルなので、割付や両面など好みの設定が可能である。

方法１や２の欠点は、手順1で全シートの「印刷品質」を確認して揃える必要があることである。先に記述したように、実際上は大した手間ではないと思うが、そうでない場合は方法３となる。

方法３・・・一旦、pdfを作成する方法（その２）

この方法は、ひりさんのblog 「h8b 日記と雑記と　Excelブックの印刷　[メモ]」 を大いに参考にさせていただいた。

1. 対象文書の pdfファイルを作成する。やり方は、通常の印刷と同様であるが、「印刷」ダイアログで、プリンタを 「クセロPDF2」（クセロ社のフリーのpdf作成用仮想プリンタ（後述））とし、「ファイルへ出力」にチェックを入れて　「OK」ボタンを押す。

   

2. 「出力先ファイル名」ウィンドウが開くので、そこに、作成するPDFファイル名をフルパスで指定して　「OK」ボタンを押す。
   
   入力例：　c:\Document\文書.pdf

   
   
   少し脱線するが、このウィンドウは、通常の「ファイルの保存ウィンドウ」と異なり、フォルダを選んだりすることができず、どのように入力すればよいのか非常にわかりにくい。Excelに何故こんなウィンドウが残っているのか理解に苦しむ。
   Microsoft については、・・・・（上で述べたので以下省略）

3. 「名前をつけて保存」のウィンドウが開くので、適当なファイル名を入れて　「保存」ボタンを押す。これで、2で指定したファイル名で pdfファイルが作成される。3で指定したファイルは作成されないので無視する。

4. 3でできた pdfファイルを印刷する。通常のファイルなので、割付や両面など好みの設定が可能である。

方法３の欠点は、私が試した限り「クセロPDF2」しか動作しないということである。Bullzip PDF Printer もダメである。（ファイルはできるが、PDF書式ではないようだ。）　
理由はよくわからないが、そもそも「ファイルへ出力」のチェックボックスの意味自体が、あまり公になっていないような気がするので、「クセロPDF2」でうまくいくことの方が例外的なのかも知れない。または、Bullzip PDF Printer はファイルの作成に Ghostscript を利用しているようなので、その辺りが関係しているのかも知れない。

【2009年3月12日修正】
なお、奇策っぽくはあるが、方法１と３を組み合わせて、pdfを介さず、かつ「印刷品質」も揃えなくてよい方法を見つけたので、「Excelの荒技（その３）」にアップした。

最後に、pdf作成用仮想プリンタ と pdf作成ソフトについて説明する。

pdfを作成するためのフリーソフトはたくさんあるが、ほとんどどれも、インストールすると専用のプリンタ が作成される。
対象ドキュメントを その「pdf作成用仮想プリンタ」を指定して印刷すると、pdfファイルが作成される仕組みだ。

私の今お奨めのソフトは、クセロPDF2 と Bullzip PDF Printer だ。
これらの詳しい紹介は、いつか別の機会で行いたいと思うが（多分、気が向けば・・・）、簡単に特長（私の印象）などを説明する。

【2010年1月10日追記】
残念ながら、クセロ社は2009年4月にアンテナハウス社に吸収されたので、現在では「クセロPDF2」のような pdfソフトはフリーでは提供されていない。

クセロPDF2 は、国産で安心感の高いソフトというイメージがある。
クセロ社は PDF 関連のソフトを数多く開発しており、一部は無料で配布しているので非常にありがたい。瞬間PDF ZERO という多機能のPDF加工ソフトも無料配布しており、これもなかなか便利である。私は、複数のpdfファイルを1つのファイルに結合する機能しか使ったことがないが、操作がシンプルで処理も速い。他の機能は、使ったことがないので説明できないが、イメージを掴んでもらうために画面をアップする。

なお、瞬間PDF ZERO をインストールすると自動的に クセロPDF2 もインストールされる。そのため、クセロPDF2 がインストール済みの場合、先にアンインストールしなければならないので、両方試すのであれば最初から 瞬間PDF ZERO だけをインストールすればよい。
なお、クセロ社のフリーソフト を動作させるためには、名前やメールアドレスをクセロ社のHPに登録してシリアル番号（ライセンスキーのようなもの）を取得する必要がある。また、使用中もWebブラウザに製品のご案内ページ（広告ページ）が表示される。そのためインターネットにつながる環境でないと動作しないので注意が必要だ。

Bullzip PDF Printer は私も最近インストールしたソフトだが、私的にはかなり気にいっている。そのポイントとしては、

• PDFだけでなく、JPEGなどの画像ファイルとしての出力も可能
• 既存PDFファイルの後ろに結合することが可能
• 「スタートメニュー」→「Bullzip」→「PDF Printer」→「オプション」で各種設定のデフォルト値を設定できるので、PDF作成時に毎回設定しなおす必要がない

である。もちろん、インターネットにつながっている必要はない。
詳しい説明は以下の Gigazine のサイトを参照いただきたい。

PDFファイルを作成可能なフリーソフト「BullZip PDF Printer」

それでは、また。

【2010年1月10日追記】

1. 修正や追記で読みにくくなったので、それらを本文に取り込んだ
2. Bullzip PDF Printer の オプション画面の表示項目が文字化けする場合は、以下の記事が参考になる。
   札幌 パソコン サポート データ復旧 修理 フリースカイブログ: Bullzip PDF Printerの文字化けについて　
3. 気付いている人も多いと思うが念のため。
   Bullzip PDF Printer の「既存PDFファイルの後ろに結合することが可能」という機能は重要で、これを使えば、複数のファイルを１つの pdfファイルにまとめることができる。すなわち、複数のファイルをつなげて　2in1 や 両面で印刷できるということである。

Excelの荒技（あらわざ）

今日は、Excelの荒技についてである。
私は、結構、仕事でExeclを使っているので、Excelについては、関数まわりを中心に相当の使い手である（と思っている。）
その私がお薦めする２つのテクニックを紹介する。
1つ目が「配列数式」、
2つ目が、複数シートでできたブックを１つの文書としてまとめて、割付印刷したり両面印刷する方法である。

これらを選択した観点は2つある。

• 実用的で役に立つ
• まだ、あまり人に使われていない

私の職場では、全員Excelは使えると思うのだが、それでも私以外の人が今回紹介する方法を使っているのを見たことがない。

まず1つ目の配列数式であるが、ひとことで言えば、式の中に配列（A1:A3 など連続した複数のセル参照）を使う方法である。こうすれば、「配列の1つ1つに対して式を適用した結果を最後に合計する」など、通常だと中間結果を一旦書き込むセルなどを用意しなければならないような複雑な計算でも、一気に答えを求めることができる。

配列数式の詳しい情報は、「配列数式」でググれば、たくさん出てくる。
最初にでてくる

日経PC21 / エクセル（Excel）「配列数式」講座

に配列数式の書き方や使用例などが相当判りやすく書かれているので、今さら、それ以上の説明をここで書こうとは思わない。
ここでは、とりあえず配列数式を使った例（例自体は小規模なサンプルであるが、使い方は私がよく実務で使っているものから持ってきた）を紹介して便利さをわかってもらい、一人でも多くの人に使おうという気になってもらいたいと思っている。

まず、例で扱うデータである。8名の生徒の国語と算数の点数の表である。

ここでは、後の例で計算式を見やすくするために、性別、クラス、国語、算数の各列を、それぞれの名前で登録している。例えば、「クラス」は「$C$2:$C$9」である。この「名前の登録」は、配列数式とは直接関係ないので、以下の例で「クラス」と書く代わりに「$C$2:$C$9」と書いても何も問題ない。（私自身も通常、名前登録はしていない）

さて、いきなり、使用例を挙げる。

見ればわかるように、性別別、クラス別の両教科の平均点を計算している。ここで、ピンク色のセルの数式は

{=AVERAGE(IF((性別=$A16)*(クラス=$B16),国語))}

である。（他のセルは、ピンク色のセルをコピーすればよい。これ以降の例でも同様である。）

ここで、最初と最後の{}は、キーボードで入力するのでなく、

=AVERAGE(IF((性別=$A16)*(クラス=$B16),国語))

と入力した後に、Control + Shift + Enter （３つのキーを同時に押す）ことで自動的に前後に {} が付く。これをしないと、配列数式は正しく動作しないかエラーとなる。（私も配列数式を最初に知ったときに、この特殊な入力方法に驚いた。）

簡単に式を解説すると、IF関数の中は、『性別が$A16（1行目で言えば「男」）、かつ、クラスが$B16（1行目で言えば「梅」）に等しければ、国語　の値』という意味だ。それを、AVERAGE関数で囲んでいるので、全体の意味としては、各行（A～Hまでの8行）に対して『』の中を試し、各行の結果の AVERAGE を計算するという意味になる。すなわち、

AVERAGE(Aの『』の結果, Bの『』の結果, ・・・・)
= AVERAGE(39, ,72, , , , , )
= 55.5
となる。

配列数式でIF文を使用する時の注意として、複数条件の論理積は AND 関数ではなく * 、論理輪は OR 関数ではなく + を用いる。もちろん、IF関数を入れ子にしてもよい。

2番目の使用例である。

クラス別、国語の点数別の人数の表である。ここで点数が 75 の行は、1つ下のレベルの点に1を足した 51 ～ 75 を意味する。-1 の行は、25 の行を 0 ～ 25 の範囲とするために便宜的に置いているもので、実際の場合は非表示行にすればよい。

ピンク色のセルの数式は

{=SUM((クラス=$A24)*(国語>$B23)*(国語<=$B24))}
または
{=SUM(IF((クラス=$A24)*(国語>$B23)*(国語<=$B24),1,0))}

のどちらでもよいが、私は後者の方が好きである。

後者の式を簡単に解説すると、IF関数の中は、『クラスが$A24（1行目で言えば「梅」）、かつ、国語が$B23 より大きく $B24 以下（1行目で言えば -1 より大きく 25以下）であれば 1 そうでなければ 0』という意味だ。それを、SUM関数で囲んでいるので、全体の意味としては、各行（A～Hまでの8行）に対して『』の中を試し、各行の結果の SUM を計算するという意味になる。すなわち、

SUM(Aの『』の結果, Bの『』の結果, ・・・・)
= SUM(0, 1, 0, 0, 0, 0, 0, 0)
= 1
となる。

3番目の使用例は、国語と算数の点数別人数のマトリクス表である。

ピンク色のセルの数式は

{=SUM(IF((国語>$B36)*(国語<=$B37)*(算数>B$36)*(算数<=C$36),1,0))}

である。

最後の使用例として、IF関数の実行部に 配列を用いた式 を書いた例を示す。
性別別、クラス別の両教科の平均点のマトリクス表である。

ピンク色のセルの数式は

{=SUM(IF((性別=$A44)*(クラス=B$43),国語+算数))/SUM(IF((性別=$A44)*(クラス=B$43),2))}

である。
このように、SUM 関数や IF関数を組み合わせた式も使える。（この例の問題では、AVERAGE関数を使えばもっと簡単にできるが・・・）
また、使える関数としては、SUM や AVERAGE 以外にも色々あるようだが、私の経験上、SUM が使えれば十分である。

配列数式の説明は以上である。
便利さが少しでも伝わっただろうか。

思いの外、記述量がかさんだので、冒頭に書いた2つ目の「複数シートでできたブックを、１つの文書として、まとめて 割付印刷したり両面印刷する方法」は、また次回ということにする。

では。