 |
【Excel Tips】
Excelでアプリケーションを作成する際に知っていると役立つと思われる事項を中心に掲載しています。ご自身でアプリケーションを作成される際のヒントになれば幸いです。
[基本操作]
●デザインモードに切り替える
●VBエディタ(Visual Basic Editor)を起動する
●セキュリティの設定
[ワークシート]
●セルの値をリスト選択入力可能にする
●セルごとにIMEの入力モードを設定する
●入力された値によりセルの文字色を変える
●セルに入力された数値の小数点の位置を揃える
●印刷時に行タイトルを印字する
●印刷結果をPDFファイルとして出力する方法
[VBA (マクロ)]
●マクロを自動作成する
●名前定義を利用する
●ブック起動時に特定の処理を自動実行させる
●四捨五入の注意点
●画面の更新を一時停止する
●フォームを使った顧客管理の作成
●カレンダフォームの作り方
●テンキーフォームの作り方
[その他]
●Excelが起動できない・起動が遅い・動作がおかしい場合の対処方法
●原因不明なエラーや現象発生時の対処法
●Excel便利技のまとめサイト
●Excel関連のＱ＆Ａサイト
[基本操作]
Excelシート上にボタンやラベルを配置・変更したり、一時的にマクロを無効にして変更を行う場合は、デザインモードに切り替えます。
Excelメニューバー「開発→デザインモード」(開発タブが表示されていない場合は「ファイル→オプション→リボンのユーザー設定→メインタブ→[開発]をチェックONする」)
VBAコードやVBAユーザフォームを表示するには、VBエディタ(Visual Basic Editor)を起動します。
Excelメニューバー「開発→Visual Basic Editor」(開発タブが表示されていない場合は「ファイル→オプション→リボンのユーザー設定→メインタブ→[開発]をチェックONする」)
VBA(マクロ)を使用したExcelファイルを起動する場合、Excelのセキュリティ設定を確認しておきましょう。
Excelメニューバー「ファイル→オプション→トラストセンターの設定→マクロの設定」で、[警告を表示してすべてのマクロを無効にする] にチェックを付けてください。
VBAを使用したExcelファイルを起動するとリボンの下へ「セキュリティ警告」が表示されますので「コンテンツの有効化」をクリックするとマクロを有効にして起動することができます。
[ワークシート]
入力規則のリストを設定しておくことにより、セルの値をリストから選択入力させることが可能となります。
例えば、C4セルが性別を入力するセルの場合、C4セルで
Excelメニューバー「データ→データの入力規則」
を選択して「データの入力規則」ダイアログを表示し、「設定」タブ内にある「入力値の種類」欄へ「リスト」を設定し、「元の値」欄へ「男,女」(選択値をカンマで区切る)を入力しておきます。これにより、B5セルに移動すると▼ボタンが表示され、リスト(男/女)から選択して入力することが可能となります。
また、別シートへリスト選択値用の値を入力しておき、その値からリスト選択入力することも可能です。
例えば、「担当者」という名前のシートに担当者のリストを入力しておき、担当者を入力したいセルで入力規則のリストの「元の値」欄へ「=担当者!$A$2:$A$20」を設定しておくと値がリスト表示されます。
入力規則の日本語入力を設定することにより、セルごとにIMEの入力モードを自動的に切り替えることができます。例えば、B2セルが名前欄でB6セルが日付欄の場合、B2セルで
Excelメニューバー「データ→データの入力規則」
を選択して「データの入力規則」ダイアログを表示し、「日本語入力」タブ内にある「日本語入力」欄へ「ひらがな」を設定し、同様にB6セルの「日本語入力」欄へ「オフ」を設定することにより、B2へアクティブセルが移動するとひらがな入力モードとなり、B6へアクティブセルが移動するとIMEがオフとなって英数字のみが入力可能となります。
条件付き書式を設定しておくことにより、入力された値によってセルの文字色を変化させることが可能です。例えば、セルへ50未満の数値が入力された場合に文字色を赤色、70未満は黄色、70以上は青色で表示するなどのように視覚的に分かりやすく表示することができます。
Excelメニューバー「ホーム→条件付き書式→ルールの管理」
また、セルの値がプラス値かマイナス値かにより文字色を変える場合は、セルの書式設定の「表示書式」の「ユーザ定義」で設定することも可能です。例えば、プラス値は青文字、マイナス値は赤文字で表示したい場合は、 [青]#,##0;[赤]-#,##0 のように(プラス値書式;マイナス値書式)設定します。色は[黒][青][水][緑][紫][赤][白][黄]の8色が指定可能です。
１列に入力されたセルの小数点位置を揃えたい場合、表示形式に #,##0.00 を設定すると小数以下の足りない桁は0で埋められて表示されます。
表示形式
標準 |
→ |
表示形式
#,##0.00 |
| 1 |
|
1.00 |
| 1.2 |
|
1.20 |
| 1.23 |
|
1.23 |
| 1000 |
|
1,000.00 |
また、表示形式に ?,???.?? を設定すると足りない桁はスペースで表示されます。
表示形式
標準 |
→ |
表示形式
?,???.?? |
| 1 |
|
1. |
| 1.2 |
|
1.2 |
| 1.23 |
|
1.23 |
| 1000 |
|
1,000. |
たくさんのデータを入力して印刷する場合、2頁目以降には項目名やタイトルが表示されず印字されているデータの意味が分かりづらい場合があります。そんな時は行タイトルを設定します。
３行目からデータが入力されている場合、
Excelメニューバー「ページレイアウト→印刷タイトル→タイトル行」
へ「$1:$2」を設定すると、2頁目以降も１行目と２行目がページ上部へ常に印字されるようになります。
Excelで作成した内容をPDFファイルにしてメール添付したり資料として保存しておきたい場合もあるかと思います。Windows10以降はプリンタ名に「Microsoft
Print to PDF」を指定して印刷するとPDFファイルとして出力できます。
(「Microsoft Print to PDF」が無いPC環境の場合でも、AdobePDFなどのPDFプリンタドライバをインストールしてプリンタ名に指定して印刷することによりPDFファイルとして出力できます)
[VBA (マクロ)]
VBAマクロを一から覚えて記述していくことは意外と大変な作業ですが、Excelにはマクロを自動作成してくれる機能が備わっていますので、まずは、この機能を利用してマクロを作成してみましょう。
Excelメニューバー「開発→マクロの記録」(開発タブが表示されていない場合は「ファイル→オプション→リボンのユーザー設定→メインタブ→[開発]をチェックONする」)
上記の手順でマクロの記録を開始したら、自動化したい作業をいつも通りの手順で操作します。自動化したい作業を操作し終わったら「記録終了」ボタンをクリックしてマクロの記録を終了させます。VBエディタ(Visual Basic Editor)を起動して、標準モジュールを見ると、先程おこなった作業内容がVBAマクロとして記述されています。そのマクロをコマンドボタンに割り当てれば、自動実行できるVBAマクロの完成です。まずは、この機能でVBAマクロを自動作成し、記述されている内容をExcelヘルプやマニュアル本などで理解しながら、必要に応じて手直ししたり、記述方法を研究したりしていくと、自然にVBAマクロが理解できるようになります。(自動作成したVBAマクロの記述には不要な記述が含まれてしまうことが多々ありますので、動作速度が遅いと感じる場合等は、記述の見直しを行って処理を最適化しましょう)
VBAコードでシート上のセルに入力された値を参照する場合、セルアドレス(Range("B4")、Cells(4,2))等を記述すると何の項目か判断しづらく、また、そのセルの上下左右にセルを挿入したり削除したりするとセルアドレスが変わってしまうためその都度、VBAの記述も変更しなければならなくなります。そのような手間を省くためにもVBAコードから特定のセルの値を参照する際は名前定義を利用すると便利です。
例えば、B4セルに自社名が入力されている場合、B4セルに名前定義で「自社名」と設定しておくと、VBAコードからは Range("自社名") で値を参照することが可能となります。また、他のセルから自社名の値を参照する場合も =自社名 で値を参照することが可能となります。
Excelメニューバー「数式→名前の管理」
ブックを開いた際に特定の処理を自動実行させたい場合は、ThisWorkbookに処理を記述しておきます。
例えば、ブックを開いた時には「メニュー」という名前のシートを表示した状態としたい場合は、VBエディタ(Visual Basic Editor)のプロジェクトエクスプローラ内にある「ThisWorkbook」に下記を記述しておきます。
Private Sub Workbook_Open()
Worksheets("メニュー").Select
End Sub
また、同様にブックを閉じる際に実行したい処理(Workbook_BeforeClose)やシート変更時の処理(Workbook_SheetChange)、シート追加時の処理(Workbook_NewSheet)等、ブックに関連するイベントの発生時の処理をThisWorkbookへ記述することが可能です。
四捨五入の関数といえば、「Round関数」と思いがちですがVBAでは注意が必要です。
VBAでのRound関数は、"算術型" の丸め処理ではなく "銀行型" の丸め処理が行われます。
"算術型" の丸め処理とは・・・ ".5" は常に切り上げられます。
"銀行型" の丸め処理とは・・・ ".5" は、結果が偶数になるように丸め処理が行われ、切り上げられることも切り捨てられることもあります。
ワークシート関数のRoundは算術型丸めを行っているため、VBAのRound関数も算術型丸めだと思って使ってしまうと、思わぬ結果をもたらしてしまいます。
一般的な業務で使用する四捨五入は算術型丸めが多いと思いますので、VBAのRound関数は使用せず、ワークシート関数を使用するよう覚えておきましょう。
wAtai = Application.WorksheetFunction.Round(数値，桁数)
また、VBAには切り上げや切り捨ての関数は用意されていませんので、切り上げ・切り捨ての場合にもワークシート関数を使用しましょう。
切り捨て･･･ wAtai = Application.WorksheetFunction.RoundDown(数値，桁数)
切り上げ･･･ wAtai = Application.WorksheetFunction.RoundUp(数値，桁数)
VBAは作業を自動化させるものであるため処理実行中は作業内容どおり画面に表示され、あまり表示させたくない処理経過が表示されたり、画面がちらついて表示されたりする場合があります。そのような場合には、処理の始めに
Application.ScreenUpdating = False
を記述して画面の更新を一時停止し、処理の終わりに
Application.ScreenUpdating = True
を記述して画面の更新を再開させます。
処理中に画面の更新を停止させることにより処理速度も向上します。
ユーザーフォームを使った顧客管理ソフトの作成手順を下記サイトで解説していますのでご参考ください。
ユーザーフォーム(VBAフォーム)とは
第1回 [初級編]
第2回 [検索ボタンの追加]
第3回 [移動ボタンの追加]
第4回 [名前定義の利用]
第5回 [顧客分類の追加]
第6回 [生年月日の追加]
カレンダフォームの作成手順を下記サイトで解説していますのでご参考ください。
カレンダフォームの作成
テンキーフォームの作成手順を下記サイトで解説していますのでご参考ください。
テンキーフォームの作成
[その他]
Excelがエラーで起動できない場合やExcelの起動が異常に遅い、Excelの動作がおかしい等の場合、下記方法などで解決する場合があります。
・C:\Users\アカウント名\AppData\Roaming\Microsoft\Excel\XLSTART フォルダ内のファイルを削除。(XLSTARTフォルダはExcel起動時に表示したいファイルを置く場所です)
・C:\Users\アカウント名\AppData\Roaming\Microsoft\Excel フォルダ内にExcel??.xlbファイルがあれば削除。(??の箇所はExcelのバージョンによって数値が異なります)
・C:\Users\アカウント名\AppData\Local\Microsoft\Office\Excel.officeUI を削除。(Excel.officeUIはクイックアクセスツールバーのカスタマイズ情報が保存されており、削除すると初期状態に戻ります)
・MSForms.exdファイルをエクスプローラで検索して削除。(MSForms.exd は削除すると次回Excel起動時に自動生成されます)
・マルチディスプレイを使用されている場合は、Excel右下に表示される「表示設定」で「互換性に対応した最適化」を選択。
Excelでアプリケーションを作成している際に原因不明なエラーや現象に遭遇し、エラーメッセージに表示されているヘルプを参照しても原因が判明しない場合は、[Microsoftサポート]サイトや[Microsoftコミュニティ]サイトの検索欄でエラーメッセージや現象などを入力して検索してみてください。同様の現象がすでに報告されていれば対処方法などが掲載されています。
また、Excel自身の不具合による現象である場合も多々ありますので、Excelを最新にUpdateすることで解決する場合もありますが、最新のUpdateで発生する現象もあり、その場合は、Excelの修正Updateを待つことになります。
MicrosoftサポートやMicrosoftコミュニティにも該当事項が見当たらない場合は、Googleにエラーメッセージや現象などを入力して検索してみてください。同じトラブルに遭遇した人の解決方法等が掲載されていることも多くとても参考になります。
Excelのちょっとした便利技をまとめたサイトです。
Excel便利技(http://excelman-waza.seesaa.net/)
Excelでアプリケーションを作成する際など、Excelについて分からない事や疑問に思う事が発生し、ご自身の周りにもExcelに詳しい方がいない場合、
モーグ(https://www.moug.net/)
など、ExcelのＱ＆Ａサイトがあります。
Ｑ＆Ａサイトを利用される際のマナーとして他の人が以前同じ質問をしていないか必ず確認してから質問しましょう。(Ｑ＆Ａサイトでは過去ログ検索機能等がありますので、まずはそこから検索してみましょう)
|
|
 
|
