カテゴリー: オフィス系ソフトウェア

ユーザー定義関数で解決！

先の VBA の話の実践適用例です。

例えば、「シート1」、「シート2」、…「シート10」までの 10個のワークシートにデータが入っていて、これを集計・操作した「サマリー」シートを作ることを考えてみます。 このような場面はよくあることではないかと思うのですが、ここで困るのがシートの参照の仕方です。 ご存知のように「シート1」のセル A1 は以下のように参照します。

シート1!A1

このようにワークシートはシート名で参照するので、素直に作業を進めるとワークシートのタブから名前をコピーしてセルに貼り付けるような操作が必要になってしまうわけです。 列は A、B、C、…、行は 1、2、3、… と参照できますが、ワークシートについては名前でしか参照できず、残念ながら「N番目のワークシートを参照する」みたいなワークシート関数も存在しません。

そこをもう少し表計算ソフトらしく処理するために「ワークシート名を取り出す処理」を考えようということになるのですが、それも簡単ではありません。 例えば、こちらにその方法が丁寧に説明されていますが、何だかなあ、という感じです。

こんなとき VBA を使って自作関数を作ることができれば、以下のような関数を作って呼べば済みます。

Function GetWorkSheetNameByIndex(i)
  GetWorkSheetNameByIndex = Worksheets(i).Name
End Function

1枚目のシートの名前は「=GetWorkSheetNameByIndex(1)」、2枚目は「=GetWorkSheetNameByIndex(2)」というように呼び出して使うことができます。 このようにワークシート関数だけでやろうとすると複雑になってしまう処理でも VBA を使えばシンプルでわかりやすくなる場合があります。

VBA ユーザー定義関数の再計算

ここで気をつけねばならないのですが、VBA で作成したユーザー定義関数は F9 を押しても再計算されません。 この問題に対処するために Application.Volatile という関数が用意されていて、これを GetWorkSheetNameByIndex の中で呼ぶとセル修正のたびにこの関数の呼び出しが再計算されるようになります。

しかし、どこかのセルを修正する度に再計算されることになるので、Application.Volatile を利用するかどうかは慎重に検討すべきです。 先の例ではサマリーシートを作成する段階であればデータ用シート名をそうそう頻繁には変えないでしょうから、わざわざ Application.Volatile を呼ばなくても関数を呼び出しているセルを編集し直して再計算という方法で十分でしょう。

おまけ

まあ、「同じ位置のセルを単純集計する」程度の話であれば、ユーザー定義関数を作らなくても 3-D 集計を知っておくだけで済むかも知れません。

Excel を使いこなしていくと「ワークシート関数だけではちと厳しいかな」と限界を感じるときがあると思います。 そんな時は気軽に VBA で関数を作っちゃいましょう。 作った関数はワークシート関数と同様に呼び出すことができます。 VBA 自作関数の書き方と呼び出し方を簡単に説明します。 (Excel 2007 以降を想定しています)

Visual Basic Editor を表示する

以下の手順で Visual Basic Editor を表示します。

1. Office ボタン (一番左上) から選べる「Excel のオプション」－「基本設定」で「[開発]タブをリボンに表示する」をチェックします。 続いて新たに表示された「開発」タブより「Visual Basic」を選択します。
2. Visual Basic Editor が起動するので、そこから「挿入」－「標準モジュール」を選びます。

ここまでで以下のように「Module1」が挿入されます。 この Module1 に関数を書いていくことになります。

関数を書いてみる

VBA の文法等について書き始めると収拾がつかないので、簡単にサンプルコードを書いてみます。 Visual Basic Editor の「Module1 (コード)」ウィンドウに以下のコードを書きます。 単純に 10倍の値を返す関数です。

Function myfunc(m)
  myfunc = m * 10
End Function

あとはシートに戻ってワークシート関数と同様に呼び出すだけです。

簡単ですよね！

おまけ

ちなみに VBA のコードの中からワークシート関数を呼び出すには、以下の形式を使います。

WorksheetFunction.ワークシート関数名

例えば、SUM(A1:A10) と同じことをしたい場合は以下のようになります。

Function sum_a()
  sum_a = WorksheetFunction.Sum(Range("A1:A10"))
End Function

範囲を表すのに Range 関数を使ったりするので、全く同じ感覚で使えるわけではないですが、VBA からワークシート関数を呼べるのは知っておくと良いと思います。 Range とか Cells とか Select とか使い始めるとどんどん VBA にハマっていくわけですが、ハマりたい人はとっかかりとしてこのあたりの記事を読むと良いかと思います。

仕事をしているとプロジェクターで Excel を映して議論を重ねるような場面もあると思いますが、意外と基本的な操作を知らない人が多いようなので、そんな時にもたつかないための基本操作をまとめておきます。 Excel 2007 以降のバージョンを想定しています。

リボンを隠す

ワイド表示のディスプレイ全盛の時代にあって、ますます邪魔に感じてしまう「リボン」インターフェイスですが、隠す方法 (正確には最小化する方法) をご存知でしょうか？ タブの部分 (「ホーム」、「挿入」、…) をダブルクリックすると非表示になります。

この状態で各タブを選ぶとメニューは都度表示されます。 元の動作に戻すには再度タブをダブルクリックです。

…って、これは Excel だけじゃなくて、Microsoft Office 全般の話ですね。

ズーム

作業用のシートをプロジェクターで映してみると小さく表示されてしまうので、拡大表示をしたくなりますよね？ そんな時、わざわざ「表示」メニューを選ばなくても Ctrl＋マウスホイール回転でズーム調整することができます。

これは Microsoft Office だけじゃなくて、Windows 全般で使える技です。(Excel じゃないじゃん…)

幅の調整

CSV ファイルを開いた後の「####」表示を一列ずつ幅調整して直していたりしませんか？ 以下の手順で一括して調整できます。

1. 左上コーナー (下図) をクリックして全セルを選択
   
2. 「ホーム」－(セル)「書式」－「列の幅の自動調整」

ご存知だとは思いますが、画面表示で幅が収まっていても印刷するとはみ出すことがあるので、印刷時はプレビューで確認する必要があります。 念のため。

とりあえずこれだけ覚えておけば、プロジェクター投影時も恥ずかしくないでしょう。 「こんな操作知ってる。レベルが低い。」とお感じの方は是非こちらの記事もご覧ください。

以前書いた Word のプロパティ活用法の Word 2007 以降対応版です。 書こう書こうと思いつつこれまで書きそびれていました。 Word 2007 では文書のプロパティ設定メニューは見つけにくい場所になっているのですが、考え方は同じです。 Word 2010 は手元にないので、Microsoft の公式情報 (その1、その2、その3)を参考にしています。

そもそもの話

話の趣旨としては、文書のあちらこちらに表示する可能性のあるタイトルや更新日などはきちんとプロパティ値として設定して使おうといことです。 そうすれば、後で変更になってもプロパティ値を変えるだけで済みます (厳密に言うとその後 Ctrl+A で全選択してから F9 を押してフィールドの内容を全て最新の状態にする必要がありますが)。 というわけで、プロパティ値の設定方法とそれを文書に埋め込む方法を紹介します。

プロパティを設定する

Word 2007 では Microsoft Office ボタン (一番左上のボタン) を押して「配布準備」－「プロパティ」と選択します。

すると下の図のようにドキュメント情報パネル (というらしい) が表示されます。 作成者やタイトル等の主だったプロパティの値はここで直せますが、ここに表示されないものは「ドキュメントのプロパティ」－「詳細プロパティ」と選びます。

後は先の記事の通り、「ファイルの概要」タブや「ユーザー設定」タブで任意のプロパティの値を設定します。 なお、Word 2010 では「ファイル」タブ－「情報」－「プロパティ」－「詳細プロパティ」と選択するようなっているようです。

文章中へのプロパティの挿入

「挿入」タブの「テキスト」で「クイック パーツ」をクリックし、「文書のプロパティ」または「フィールド」をクリックします。 こちらは Word 2010 も同じ操作のようです。

プロパティ値をクリアする

ところで、昔から使っているドキュメントでトラブルがありました。 昔のバージョンの Word で作ったものをアップデートして使っていたのが原因かも知れませんが、プロパティ設定画面の「ファイルの構成」タブを見ると「タイトル」と「Title」の両方が設定されていたのです。 そして「タイトル」は再設定できるものの「Title」は設定のしようがなく、「構成内容」のところから消すことができなかったのです。

それでどうしたかというと、一旦全てのプロパティ値を消し、再設定し直しました。 プロパティ値のクリアは Word 2007 では Microsoft Office ボタン を押して「配布準備」－「ドキュメント検査」からできます。 Word 2010 では「ファイル」タブ－「情報」－「共有準備」の「問題チェック」－「ドキュメント検査」と選ぶようです。

PowerPoint のプレゼンテーション中に「ペン」を使ってスライドに書き込む機能がそれほど使われていない (ただし、私の観測範囲内) のは、コンテキストメニューを表示してペンを選ぶその操作が野暮ったいからだと推測しています。

でもショートカットキーを覚えればそのあたりをスマートにできます。 ショートカット一覧はこちらのページにありますが、最低限以下のコマンドを覚えておきましょう。

• Ctrl + P ポインタをペンに変更
• Ctrl + A ポインタを矢印に変更
• Ctrl + H ポインタを非表示

ペンには太さの違いにより「ボールペン」、「フェルトペン」、「蛍光ペン」と３種類ありますが、ショートカットで選択できるのはフェルトペンのみです。 ペンの色は「スライドショーの設定」画面で選んでおくことができます。

これで、スライドショー実行中にスマートにペン機能を使うことがができますね。 (この記事は PowerPoint 2007 で動作確認しています)

既に「2010」があるので最新バージョンではなくなっていますが、最近ようやく Office 2007 のメニュー体系に慣れてきました。 Word 2003 を使用していた頃に書いた段落番号の記事は結構アクセスがあるのですが、Word 2007 では若干操作が異なります。

というわけで思い通りに段落番号が振られないときのチェックポイント Word 2007 バージョンをまとめてみました。

同じ段落番号書式が選択されているか？

Word 2007 からは「リストライブラリ」として段落番号書式が管理されます。 まずはじめに各見出しで「ホーム」タブ、「段落」の「アウトライン」メニューを表示して、リストライブラリの中の同じ書式が選択されていることを確認します。

アウトラインレベルと見出しスタイルの対応

番号が思い通りに振られていなかったり、スタイルが合っていない場合は、「アウトライン」メニューの「新しいアウトラインの定義」を選択します。

以下の「新しいアウトラインの定義」ダイアログボックスを表示されるので内容を確認します。 「オプション」も表示してみましょう。 このダイアログボックスは「変更するレベル」を選択してから各項目を設定するという使い方です。 「変更するレベル」を変えるということは設定対象のレベルを選ぶという意味で、文章の今の位置のレベルを設定しているわけではありません。

見出しのスタイルがおかしい場合は、各レベルに適切な「見出しスタイル」が設定されているかを確認します。

ちなみに現在位置のレベル変更は「インデント増 (減)」ボタンの他に Tab (Shift + Tab) でできますね。 ここまで来て今更ですが、アウトライン表示にしてレベルが正しく設定されていることも確認しておきましょう。

番号のリセット条件

上の図の様な並びとするには、レベル１が変わったらレベル２の番号がリセットされるようになっていなければなりません。 このような採番は先の「新しいアウトラインの定義」ダイアログボックスで「リストを開始するレベルを指定する」をチェックして、選択したレベルより一つ上のレベルを選択することで可能になるので、うまくいっていない場合はここを確認します。 先の例では「変更するレベルをクリックしてください」＝「２」で、「リストを開始するレベルを指定する」＝「レベル１」となっていれば OK です。

スタイルとして使用しよう

ところで、「アウトライン」メニューには「新しいアウトラインの定義」の他に「新しいリストスタイルの定義」というメニューがあります。 これらの違いは、前者は使い捨てなのに対して、後者はスタイルとして登録することができるという点です。 登録したスタイルを修正すれば、そのスタイルを適用している全ての箇所に修正が反映されるので、通常はこの「新しいリストスタイルの定義」を使うのがよいでしょう。

「新しいリストスタイルの定義」を選択して出てくるのは「新しいリストスタイルの定義」ダイアログボックスですが、そこから「書式」－「箇条書きと段落番号」を選べば、先程からレベル設定確認で使用しているのと同じ「アウトラインの修正」ダイアログボックスが表示されます。

作成したスタイルはリストライブラリの下に「リストのスタイル」として登録されます。 変更する場合は、ここで右クリックして表示するコンテキストメニューから行うことができます。

また、「新しいアウトラインの定義」を用いた場合はそのままでは使い捨てなのですが、同じようにコンテキストメニューを表示し、「リストライブラリに保存」することができるので、そのような使い方もアリだと思います。

というわけでアップデート版をやっと書き上げて胸のつかえがおりたような気がするのですが、検索結果で古い記事が優先されてしまうとなかなか新しい記事は読んでもらえないのですよね．．．。

Excel の「配列数式」をご存じですか？ 知っている人には取り立てて騒ぐことではないのでしょうが、私は最近知ってこんなことができるのかと感心しました。 これを知っていると配列を使って数式を書くことができ、今まで二段階の計算で求めていた値が一発で計算できるようになると思います。

1. #N/A を含む合計を計算
以下の表の No.1 ～ No.10 までの合計はどのように求めれば良いでしょうか？ 実は単純に SUM(B1:B10) とすると #N/A が表示されてしまいます。 何故ならば、合計する対象にエラーが含まれていると SUM はエラーを返す仕様だからです。

～IF 関数も使えるかも知れませんが、ここでは配列数式を使います。 配列数式を使った以下のような数式で合計できます。 ただし、数式の入力確定時に Enter でなく、Ctrl + Shift + Enter を使います。

=SUM(IF(ISNA(B1:B10),0,B1:B10))

「B1:B10」のように関数の引数に範囲を指定しているところがミソです。

2. 行を選んで合計
例えば以下のような表があったとします。 男のみ、女のみの合計はどのように計算すれば良いでしょうか？

男の合計は、

=SUM(IF(B1:B6 = "男", C1:C6, 0))

女の合計は、

=SUM(IF(B1:B6 = "女", C1:C6, 0))

となります。更に、

=SUM((B1:B6 = "男") * C1:C6)

という書き方も可能です。

最後の例は TRUE = 1、FALSE = 0 であることを利用したものですね。 トリッキーな感じがしますが、Microsoft 公式サイトでも案内されています。 以下のページの「条件に基づいて値を合計する」の部分です。 AND 関数や OR 関数はそのままでは配列数式中で使えないのでこのようなテクニックが必要とのことです。

• 配列数式のガイドラインと例 (Microsoft)

いずれの数式も Ctrl + Shift + Enter で入力する必要があるのでご注意ください。

マルチモニター環境で PowerPoint のスライドショーを実行して全画面表示にする際に「プライマリモニタ」側に表示されてしまうので困った、なんてことありませんか？

ノートパソコンでマルチモニター環境を使いこなしている人は着実に増えているように見えます。 私も使ってみようと思ってまずハマったのがこれです。 最初はプライマリモニタをノートパソコンのディスプレイから外部ディスプレイに変えようとしましたが、これはできませんでした。

で、結局どうすれば良いかと言うと、「スライドショーの設定」メニューより「スライドショーの表示」で出力先モニターを外部ディスプレイに設定すれば良いです。

更に「発表者ツールを表示する」をチェックしておくと、ノートパソコンのモニターに発表者用のツールが表示されます。 このツールを使うとスライド間の移動や、メモ参照、時間の確認等が簡単に出来て便利です。

Excel の VLOOKUP 関数を使ったときに「#N/A」と表示されて困ったことはありませんか？

例えば下の図のように機器のシリアル番号と機器種別の入った表を VLOOKUP 関数で参照してみましょう。

セルの G6 に「104100」を入力し、これに対応する機器種別を参照するために H6 に以下の数式を入力します。

=VLOOKUP(G6, $A$1:$B$4, 2, FALSE)

これで H6 には「サーバ」と表示されるはずなのに、「#N/A」のエラーが表示されてしまいます。

良く見ると「104100」が右詰めになっています。 どうやら数値として扱われているので、セルの書式設定で G6 の表示形式を「文字列」に設定してみます。

すると、G6 の表示は左詰めになったものの、やはり H6 には #N/A が表示されてしまいました。

私はここでハマってしまいました。 「再計算」しても状況は変わりません。 どうすれば良いのでしょう？

実はセルの書式設定を変えただけでは、セルに入力されている値は数値のままなのです。 どうすれば良いかというと、とりあえず一番手軽にできるのは再入力です。 G6 をダブルクリックしてセルを編集する状態にしてから、そのまま内容を変えず Enter キーを押します。 するとうまく表示されるようになります。

既に数値として入力してしまったセルが大量にあった場合、全てを再入力するのは面倒です。 その場合、入力した列をコピーして一旦テキストエディタなどに貼り付け、それをさらにコピーして Excel に戻せば文字列として認識されます。「形式を選択して貼り付け」等でできそうな気もしますが、結局 Excel で完結して実施する方法はよくわかりませんでした (後から見つけました。追記参照)。 なお、これらの動作は Excel 2002、2003 で確認しています。

ちなみにテキストから数値への変換は以下のサポート情報で紹介されています。

• Excel 2002 および Excel 2003 でテキストを数値に変換する方法

VLOOKUP 関数といえば、最後のパラメータのデフォルトが「TRUE」になっている点にも注意が必要ですね。(ハマったのは私だけ？？)

---

2012.7.22 追記:
「教えて！ goo」で見つけたのですが、Excel の中のみで 1列分の数値入力済みセルを文字列に変える方法がありました。「データ」－「区切り位置」メニュー (Excel 2007) を使うのです。この「区切り位置」は本来カンマ等で区切られている値を複数の列に分割するためのものですが、分割はせずに最後に指定する「列のデータ形式」を「文字列」にして実行すれば型変換に使えます。なるほどね。

Microsoft Excel で数式を入力し、他のセルを参照するときの小ネタです。

空のセルの参照
空のセルを普通に参照すると「0」と表示されてしまいます。例えば、A1 が未入力の時 B1 に、

=A1

とすると、B1 は

0

と表示されます。

これを避けるためには ISBLANK を使用するか "" との比較を行います。 以下の例はいずれも A1 が未入力ならば空白表示となります。

=IF(ISBLANK(A1), "", A1)

=IF(A1 = "", "", A1)

では２つは全く同じかというと実は動作が違います。 参照するセルに数式が入っていて、それを評価した結果が空の時は ISBLANK は偽ですが、「= ""」は真となります。

例えば、A1 が空白で、B1に

=IF(ISBLANK(A1), "", A1)

と数式が入っているときに、

=IF(ISBLANK(B1), "blank", "other")

は「other」になりますが、

=IF(B1 = "", "blank", "other")

「blank」になるのです。

改行を含むセルの参照
Alt + Enter で改行が入力され複数行が含まれているセルを参照する場合の注意です。 このようなセルを数式で参照すると改行がなくなってしまうという現象が見られました。

例えば A1 が

a
b
c

なのですが、普通に「=A1」と数式を入力すると

abc

と表示されてしまうのです。 実はこれは表示の問題でセルの書式を「折り返して全体を表示する」にするときちんと、

a
b
c

と表示されます。

---

参考記事

• Microsoft サポート技術情報の空白セルに関する記事一覧