□技術メモ - Excel ※管理人の個人的な技術メモです。サンプルソースを実行した結果について 管理人はいかなる責任も負いかねますのでご自身の責任でお試しください。 ----------------------------------------------------------- ○名前を「見える」に設定する ・シートをコピーするときに、名前が定義されていると、同じ名前を使用するかの問い合わせをされることがある。 数が少なければいいが、多いときには非常に煩雑になる。その場合は、画面上部の「名前の管理」で削除してやればよい。 名前が見えない設定になっているときは以下のマクロを実行して「見える」に設定する。 -------- Dim objName as Object For Each objName In Names objName.Visible = True Next objName -------- ○vlookup ・１行を１レコードとして検索して、指定されたカラムの値を返す。 ・引数の意味は以下の通り。 第１引数：検索KEY 第２引数：検索対象のレンジ。一番左のカラムが検索されるKEYになる。 レンジ指定の際に$を付ければコピペしても検索範囲が固定になる。 第３引数：値を返すカラム位置。指定したレンジの一番左を１とする。 第４引数： TRUEなら二分探索を行う。レンジ内のキーでソートしてあること、検索結果が存在することが 条件になる。処理は早いが上記の条件が揃ってない場合はキーが違うレコードの値を無条件に返してくるので注意。 FALSEなら上の行から検索して、最初にキーの合致したレコードの指定カラムの値を返す。 合致するキーが存在しない場合は#N/Aになる。 ※hlookupはvlookupを縦横を逆にした機能。 ○countif ・条件に合致したセルの数をカウントする。 第１引数：検索対象のレンジ。 第２引数：検索条件 (例) countif($C$1:$C$200, "=#N/A") 意味：範囲(C1:C200)において、#N/Aの数をカウントする (例) countif($C$1:$C$200, "=" & $B1) 意味：範囲(C1:C200)において、B1の値と同じセルの件数をカウントする ○ピボットテーブル （準備中） ○Excelが保存できない ・Excelファイルを保存できないときの多くはメモリ不足が原因。 ・ファイルは一度中間ファイルに保存されてから元のファイルに保存されるので、 少なくともファイルサイズの２倍のメモリが必要になる。 従ってメモリを確保することをまず考える。 ブラウザやWORD等の、メモリを多く使っているアプリケーション等を閉じて、 メモリを確保してから保存すればよい。 ・それでもメモリが足りない場合は、新しいBookを開いてシート単位でコピーもしくは移動して、 シート単位で保存していけばよい。 ・画像などを多く貼り付けてファイルサイズが大きくなる場合は、保存できなくなったり、 体裁が崩れたりするなど、不具合が発生する可能性が大きくなる。 シート単位でバックアップを取るなどして警戒すること。 ○ファイルサイズが大きくなりすぎた場合の不具合 ・Excelに画像ファイルなどを張ってファイルサイズが無制限に大きくなっていけば 必ずファイルに不具合が発生する。なぜならメモリサイズ、CPUの処理速度等は有限だからだ。 ハードディスクのサイズは2015年現在は十分に大きいが、かつては外部記憶装置が容量不足になるかどうか 常に気にしていたし、今後そうなることがないとは言い切れない。 ・ファイルサイズが大きくなりすぎた場合、以下のような不具合が発生する。 ファイルを開くのに極端に時間が掛かる、ファイルを保存できない、シート内の体裁が崩れて 画像の位置やサイズが変わってしまう、すべてのセルに改ページ記号が入ってしまう等。 ○Excelによるエビデンス作成 ・これはあくまでも管理人がエビデンスなどのExcelドキュメントを作成するときの方法について 示したメモである。 ・すべてのシートを選択→セルの書式設定→文字→文字サイズ12ポイント→ＭＳゴシック →アライメントを左上に設定→すべてのシート選択を解除 ・画像を張る時は、拡大縮小は100%指定にする。このときは改ページ位置などは気にしないで 作業を進めてしまって問題ない。最後に改ページ位置を調整すればよい。 ・ページの拡大縮小は設定しない。ページ設定で、横１ページ、縦（未指定）にして、 縮尺はExcelの判断に任せる。 ・画像はＡ４縦１ページに対して２枚までとする。 少し詰めれば３枚入る気がするがこれはしない。 上から５セル程度あけて画像を張り、さらに５セル程度あけて画像を張っていく。 ・説明文などは角丸の吹き出しを使って記載する。 吹き出しの先端を四角形の内側にドラッグすれば通常の角丸の長方形になる。 ・データの遷移や流れを示すときは長方形オブジェクトとコネクタを利用する。 コネクタは線の端がオブジェクトに近づくと適当な位置に接続するので細かい調整が必要なくなる。 ・ある程度作業が進んだら表示形式を「改ページプレビュー」にする。 ・ExcelはWYSIWYGになっていない。これは表示と印刷のドライバが違うため。 ・角丸の吹き出しに説明を書いても印刷時に全て印刷されないことがある。 文章の位置が吹き出しのサイズの左上1／4に収まるくらいを目安にする。 ・印刷プレビューの際に、画像サイズが一定にならないことがある。 この場合は画像オブジェクトのプロパティを変更する。 通常は「セルに合わせて移動する、 サイズ変更しない」 もしくは「セルに合わせて移動しない、サイズ変更しない」が多いと思うが、このモードにはバグがある。 （印刷プレビューの際に、画像サイズが一定にならない時点で「サイズ変更しない」になってない） 少し怖い気もするが、思い切って「セルに合わせて移動する、サイズ変更する」に設定すれば問題ない。 セルのサイズが変わっても画像を元のサイズに戻せるし、慣れてしまえば問題なく作業できる。 ○任意のセルに画像を貼り付ける ・ビットマップをコピーしてセルを指定して張り付けると左上（A1)にしか貼り付けられないことがある。 この場合の対策は以下の通り。 セルを右クリック→書式を指定して張り付ける→ビットマップ Ctrl + Alt + V → ビットマップ ・画像の量が多いことがわかっている場合はマクロに登録して Ctrl + (任意のキー) でショートカットにすると使い勝手がよい。