Home > Excel エクセルの使い方-ユーザー定義関数/VBA
Excel エクセルの使い方-ユーザー定義関数/VBA
スペースを改行にするために、TRIM関数やSUBSTITUTE関数などを使った数式をご紹介しました。
この数式では1つ以上のスペースが改行になりますが、実務では2つ以上の連続したスペースだけを改行にしたいという要望のほうが、よくあるかもしれません。
セルの先頭から一つ目の改行までの文字列を取得するワークシート関数を組み合わせた数式をご紹介しました。
実際に他のシステムにデータを渡す場合、VBA（Visual Basic for Applications）が書けないという方なら、ご紹介した数式を作成しておいて、形式を選択して貼り付けなどで改行までの文字列にする作業が必要になります。
こういう部分は、VBAがわかるとやはり楽です。
VBAがわかる方なら、マクロにしてしまいましょう。
Excelをあまりご存知ない方が作ったExcelファイルを、何らかのシステムの中で利用しようとすると、いろいろな前処理・準備処理が必要となります。
最近、私が弄らせていただいているExcelファイルでは、セル内の文字列の前や後に、複数の改行が入っていることがあります。
［セルの書式設定］ダイアログ－［配置］タブ－［縦位置］欄で「中央揃え」をすれば、文字列がセルの中の上下方向の中央に配置されるということをご存知ない方が、縦方向で中央揃えをしたいと考えた苦肉の策ではないかと推測しています。
Excelで、連続する複数の改行を一つにまとめるために、正規表現を使ったユーザー定義関数をご紹介しました。
実務上は、複数の改行が1個になるのではなく、2個以上の連続する改行を2個の改行にしたいという要望のほうがよくあるかもしれません。
Excelは表計算ソフトですが、計算機能もついた表型ワープロソフトとして利用する方は少なくありません。
計算機能付き表型ワープロとしてExcelを利用することを嫌悪する方がいらっしゃる一方で、本来のワープロソフトであるWordが、実は、結構難しいソフトであるため、Excelをワープロ替わりにする方は少なくありません。
Excelをワープロ替わりに利用しているような職場だと、文字列整形に関する要望が、いろいろと出てきます。
複数の改行をまとめて一つにしいたい、という要望はその代表的なものの一つです。
［Alt］＋［Enter］で複数の改行を入れられているときに、とりあえず改行を一つにしたいという要望です。
今年（2011年）に入ってから、「2011」が素数であることから、それに関連したツイートやブログ記事を見かける機会が何度かありました。
VBA（Visual Basic for Applications）でも、素数かどうかを判定するユーザー定義関数を作ることは可能です。
ちなみに素数というのは、
「1よりも大きな、1と自分以外で割り切れない自然数」
です。
一番わかりやすい素数かどうかの判定は、2から、調べたいその数字から「1」をマイナスした値までの自然数で順番に割ってみて、割り切れるかどうかを調べればできます。
例えば、「11」が素数かどうかを調べるには、「2」から「10」までの自然数で割ってみて、割り切れなければ素数と判定できます。
「Excel URLデコード 方法」
という検索が行われていました。
ExcelでURLデコードを行う方法を探している検索です。
このサイト・インストラクターのネタ帳では、Windows 2000以降のWindowsに標準で含まれているScriputControlを使ったユーザー定義関数を作ってやればUTF-8によるURLエンコードができることをご紹介しています。
Excelで表を作成しておいて、テーブルタグを出力するネタは、そこそこの数はてなブックマークされています。
確かに便利な方法なのですが、実際にこの技を実務で試した方は、どれくらいいらっしゃるのでしょう。
この方法を実務で頻繁に使うとなると、少々面倒なのも事実だと思うのです、私は。自分で紹介しておいてアレですが。
かといって、ExcelからHTMLを出力するツールをインストールしたりするのはちょっと、というケースもあります。
Wordで複数の空白スペースを一つに置換する方法をご紹介しました。
実務では、ExcelデータやCSVデータを受け取ったときなどにも、これと似たような処理を欲しくなることがあります。
同じ文字列が連続しているデータがあり、それを一つにしたいという要望が実務で出てくることがあります。
具体的な例をあげると、
「おめでとう！！！！！」
といったデータが入力されているときに
「！」が連続しているのでこれを一つだけにして
「おめでとう！」
にしたいといった要望です。
この例のように「！」が必ず5つ連続しているといいきれるのなら、Excel標準のワークシート関数・SUBSTITUTEを使ったり、置換機能で一つにすることも可能ですが、いくつ連続するか決まっていない場合、Excel標準の機能だけで対処するのはなかなか難しそうです。
自分用にちょっとしたツールをExcelで作っていて、文字列をUTF-8でURLエンコードする処理が必要だったため、その実装方法を調べました。
もう10年以上前になりますが、WindowsNT 4.0上のAccessで、URLエンコード・デコード関連の処理が必要になったことがありました。その当時はWindows標準のCOMなどで、URLエンコードする処理をやってくれるものがなく、信頼できそうなCOMを探したりして、ちょっと苦労した記憶があります。
その当時から考えると、今は無茶苦茶簡単にExcelでもUTF-8のURLエンコードできてしまいます。
条件付き書式を設定しているようなときなどに、数式が入力されているかどうかを判断したい、調べたいということがあります。
Excelには、数値かどうかを判断するISNUMBER関数、奇数か偶数かを判断するISODD関数とISEVEN関数、空白かどうかを判断するISBLANK関数、エラーかどうかを判断するISERROR関数などが、ワークシート関数として用意されています。
しかし、数式かどうかを判断するワークシート関数（エクセル関数）は、残念ながら用意されていません。
セルのコメント機能は便利です。
ですがあくまでも一時的に使うものでしょう。
例えば、Excelで何らかのデータベースを作成しているようなときに、コメントを大量に使ってしまう方がいらっしゃいます。
そのような場合、コメントを多用するより、何らかのフィールドを作成してセルに文字列を入力しておくほうが、後々の使い勝手はいいはずです。
そんなコメントの多用状態を解消するために、コメントに入力されている文字列をセルに移動するといった作業が実務で発生することがあります。
コメントの量が少なければ手作業でおこなっても構いませんが、大量のコメントが挿入されているときに、コメントに入力されている文字列を手作業でセルに移動するのは、かなりの手間です。
通常Excelを使う場合、列はA列・B列・C列・・・のように、文字列（アルファベット）であらわすことが一般的です。（R1C1モードも用意されていますが。）
しかし、VBA（Visual Basic for Applicatins）でExcelマクロを作成する場合、列も行と同じように数値で扱うほうが便利なことが少なくありません。
プロシージャでループ処理などを行うときなど、もし列を文字で表記したままコーディングしようとすると、面倒で仕方ありません。
列を文字であらわすより数字であらわすほうが、簡単にコーディングできることが、はるかに多いでしょう。
しかし、例えばユーザーに何らかのメッセージを出すような場合、列を数字から文字列（アルファベット）にする処理が必要になります。
例えば、何らかのメッセージで
「26列目」と表示するよりは
「Z列」と表示するほうが
ユーザーにとってはわかりやすいはずです。
「文字列から数字だけを抽出するにはどうしたらいいのでしょう？」
「数値と文字が含まれているセルから、文字を取り除いて数値だけにするにはどうしたらいいのでしょうか？」
といったご質問をいただくことがあります。
Excelを使いこなしている方なら、計算に利用する可能性のある数値を含むデータを入力する際、数値だけを入力して書式設定で
「単価350円」
といった表示にします。
（この場合「"単価"#,##0"円"」とユーザー定義書式を設定します。）
しかし、Excelに慣れていない方の場合、
「単価350円」
といった入力をそのまま行ってしまいます。
（このような入力をした場合、文字列になってしまうので「350」を計算に利用できません。）
このようなExcelに慣れていない方が入力した
「単価350円」
といったセルから
「350」
という数字だけを抽出したいというのが、ご質問の主旨です。
右隣のシートを相対参照するユーザー定義関数と、左隣のシートを相対参照するユーザー定義関数を、ご紹介しました。
ASC関数と［ふりがな］機能とPHONETIC関数を合わせて利用することで、そこそこの手間でカタカナは全角、英数は半角という状態にできることをご紹介しました。
一発で英数字だけ半角にしたいのならば、VBA（Visual Basic for Applications）でユーザー定義関数を作成しましょう。
「常に隣のシートを参照するにはどうしたらいいのでしょう？」
「前のシートを相対参照するようなことはできないのでしょうか？」
といったご質問をいただくことがあります。
実務では、月ごとにワークシートを作成して前月分と今月分の合計を計算する、といったようなことがよく行われます。
この場合、既存のシートをコピーして新しい月のシートを作成するということが多いわけで、そのときに前月分のシートを参照している数式を修正する必要が出てきます。
これを楽にしたいため、隣のシートを相対参照できないのかという要望をいただくわけです。
セルの相対参照と同じイメージで、常に前月のワークシートのセルを参照することができれば、シートコピー後の修正作業を無くすことができるからです。
「文字列の後のスペースだけ削除したいのですが・・・？」
「セル内の文字の後ろの空白だけを削除するにはどうしたらいいのでしょう？」
といったご質問をいただくことがあります。
多くのプログラミング言語には、文字列の後ろのスペースだけを削除する「RTRIM」といった関数が用意されていますが、Excelの場合ワークシート関数には「TRIM」はありますが「RTRIM」がありません。
（また、ExcelのTRIM関数はちょっと微妙な仕様です。）
拡張子ぬきでファイル名を取得するユーザー定義関数についてご紹介しました。
ですが、、、はてなブックマークで、
「ファイル名にドットがある場合にも対応するには右から検索すべき。」
とのコメントをいただきました。
ファイル名・ワークブック名を取得するユーザー定義関数と、FIND関数とLEFT関数を組み合わせて拡張子なしでファイル名・ワークブック名をセルに表示する計算式をご紹介しました。
Excelで非常によくいただく要望ながら、標準の機能では行うことのできない操作があります。
セルの色を指定したデータ抽出です。
例えば、何らかのリストがあって、そのリストを目視確認しながら気になるデータのセルに色をつけ、あとから色をつけたデータだけを表示するようにフィルタリングしたい、といった要望をいただくのです。
残念ながら現在のExcelではそういった利用方法を想定していないので、該当するコマンドなどはありません。
エラー表示をしないようにするために、
・ISERROR関数とIF関数を利用する
・条件付き書式を利用する
という2種類の方法をご紹介しました。
VBA（Visual Basic for Applications）のわかる方なら、エラー表示をさせないように、ワークシート関数をラップしたユーザー定義関数を作成するというのも有効な方法でしょう。
ワークシート側では通常のワークシート関数を利用するのではなく、エラー表示回避処理の施されたユーザー定義関数を利用するのです。
業務アプリケーションを作成する際、複数のファイルを利用する処理が含まれていると、処理をはじめる前にそのファイル・ワークブックが開かれているかを調べる必要が出てくるため、VBA（Visual Basic for Applications）の講座で実務的なコーディング段階まで進むと、
ファイルが開かれているかどうかをチェックするにはどうすればいいのでしょう？
ワークブックが開いているかを調べたいのですが？
というご質問をいただくことになります。
ワークシート関数を使ってブック名を表示させる方法では、上手くワークブック名が取得できない場合があるとこととその対策をご紹介しました。
同様の問題は、ワークシート関数を使ってシート名をセルに表示させる場合にもやっぱり発生します。
CELL関数・FIND関数・LEN関数・RIGHT関数を組み合わせてワークシート名を取得する方法をご紹介しましたが、やっていることはCELL関数を使って「絶対パス名＋[ファイル名]＋シート名」を取得して、その中から「]」の位置をみつけてワークシート名を切り出していたわけです。
ファイル名やフォルダ名に「]」が含まれていれば、ブック名の取得同様、シート名も正しく取得することはできません。
CELL関数・FIND関数・MID関数を駆使して、ワークブック名をセルに表示させる方法をご紹介しました。
ご紹介しておいて何ですが、実はあの方法で上手くいかないケースもあります。
フォルダ名やファイル名に「［」や「]」が使われているとダメなのです。
このサイトではこれまでいくつかユーザー定義関数をご紹介してきました。
- ファイルの最終更新日時を取得したい−LastSaveTime関数
- 数式を別のセルに表示させたい−ViewFormula関数
- ユーザー定義書式を表示させたい−ViewFormat関数
- 範囲指定で文字列を連結−ConcatenateRangeText関数
いずれもコードを標準モジュールに貼り付けていただければ、通常のワークシート関数と同じように使えるとご紹介してきました。
しかし通常のワークシート関数と異なっている部分もあります。
［関数の挿入］［関数の引数］ダイアログなどに、通常のワークシート関数ならば、関数の説明が表示されていますが、ユーザー定義関数では説明が表示されないのです。
複数のセルに入力されているデータを連結する方法をご紹介しました。
このネタで、CONCATENATE関数で「:」（コロン）を使って範囲指定できそうな気がするけれども、できないということを指摘しました。
でも、やっぱりそういう要望はあるわけです。
ユーザー定義関数を使ってセルに入力されている数式を別のセルに表示させる方法を、ご紹介しました。
似たようなことですが、
セルに設定されている表示形式を別のセルに表示させたい
という要望をセルの書式設定の重要性に気づいた方からいただくことがあります。
また、セルの情報を取得できるCELL関数を知った方からは、
CELL関数の第1引数に「"format"」を指定すると、第2引数で指定されたセルの表示形式が調べられるみたいですが、ユーザー定義書式を表示させるにはどうすればいいのでしょう？
というご質問をいただくことがあります。
以前、「数式や関数の表示や印刷をしたい」というネタをご紹介しました。
数式の計算結果ではなく数式そのものを表示・印刷するために、［オプション］ダイアログで設定したり、［Ctrl］＋［Shift］＋［@］キーを使って、表示を切り替えるという方法でした。
この数式そのものを表示・印刷したいというのと似たような件で、
あるセルに入っている数式を別のセルに表示するにはどうすればいいのか？
数式と計算結果ともに表示・印刷するにはどうすればいいのか？
というご質問をいただくことがあります。
Wordで最終保存日時を表示させる方法をご紹介しました。
同じことをExcelで行うにはどうすればいいのか、とご質問をいただきました。
最終更新日時を表示させたいのだが何と言うワークシート関数を使えばいいのか、というご質問をいただくこともあります。
［関数の挿入］ダイアログやヘルプを相当探す方も少なくないようですが、残念ながらブック（ファイル）の最終保存日時（最終更新日時）を取得するワークシート関数は、用意されていないようです。
Home > Excel エクセルの使い方-ユーザー定義関数/VBA
