YouTubeで開く
セルの表示形式の設定方法について基礎から応用、発展まで解説しています。
Excelを使いこなすためには必要な内容になります。
特に「日時を扱う際の表示形式」が重要になります。(シリアル値の解説を含む)
00:00 挨拶
00:25 表示形式とは
05:16 数値を扱う際の表示形式
14:18 日時を扱う際の表示形式
19:10 ユーザー定義の基礎
30:25 ユーザー定義の発展
36:50 補足
37:49 まとめ
>>昔の記事はこちら
▼実践用ファイル▼
IT予備
業務効率化のコツが無料で学べる!YouTube解説&配布サイト
セルの表示形式の設定方法について基礎から応用、発展まで解説しています。
Excelを使いこなすためには必要な内容になります。
特に「日時を扱う際の表示形式」が重要になります。(シリアル値の解説を含む)
00:00 挨拶
00:25 表示形式とは
05:16 数値を扱う際の表示形式
14:18 日時を扱う際の表示形式
19:10 ユーザー定義の基礎
30:25 ユーザー定義の発展
36:50 補足
37:49 まとめ
>>昔の記事はこちら
▼実践用ファイル▼
VBAでExcelファイル配下のパスを取得するために「ThisWorkbook.Path」などを活用することが多いかと思います。
このコードを活用したマクロファイルをOneDrive内に配置したら、正しく実行できなくなったという報告を受けました。
今回は、OneDrive内に配置した場合に「なぜ正しく実行できなくなったのか」と「どうすれば実行できるのか」の2点について解説していきます。
なぜ正しく実行できなくなったのかというと、取得されたパスに問題があるためです。
試しにOneDrive内に保存した「正しく実行できないExcelファイル」に以下のコードを記述し実行してみます。
そうすると、恐らく以下のようにURLが表示されるかと思います。
これが原因です。
本来であれば、ローカルパス(C:\Users\[アカウント]\OneDrive\確認用)が取得されるべきですが、URLが取得されてしまっているため、コードの内容によってはこれが原因でエラーになります。
※Workbooks.Openでは実行できますが、Openステートメントを使う場合(テキストファイルなどを読み込む場合)などはエラーになる可能性があります。
ちなみに、私のOneDriveフォルダはDドライブは以下に配置しております。
D:\OneDrive\
※デフォルト設定の場合は以下になるかと思います。
C:\Users\[アカウント]\OneDrive\
なぜURLが取得されるのかというと、OneDriveの「ファイルのコラボレーション」機能が設定されているためです。
この機能により、OneDrive上のファイルを他のユーザーとリアルタイムで共同編集することができます。
この共同編集とはブックの共有とは異なります。
※ブックの共有とは以前の機能で、共同編集の機能とは異なり機能制限が多くあります。共同編集の機能は古いバージョンには対応していません。
この共同編集とブックの共有の違いについてはこちらでは割愛します。
上記にて、OneDriveの「ファイルのコラボレーション」機能が設定されていることでパスがURLになることが分かりました。
この理屈から次の対策方法が考えられます。
①ファイルをOneDriveの外に移動する
②「ファイルのコラボレーション」機能をオフにする
ただ、この2つの方法の場合は、共同編集ができなくなってしまいます。
「ファイルのコラボレーション」機能をオンの状態で対応したい場合は、次の方法などがあります。
③URLをローカルパスに変換する機能をVBAで実装する
①に関しては、ファイルの位置を変更することになるため、根本的な解決にはなりません。
そのため②と③について、順に解説していきます。
Windows11の場合について解説していきます。
※Windows11以外の場合に関しても同じような手順で行えます。
まず初めに、画面右下のOneDriveのアイコンをクリックします。
以下の画面が表示されたら、設定を開きます。
設定画面の中に「同期とバックアップ」という項目があります。その中の下へ移動すると詳細設定があるため、そちらを展開します。
展開したところに「ファイルのコラボレーション」という項目が表示されるため、こちらをオフに切り替えれば設定完了です。
対象のExcelファイルを開いて以下の関数を作成します。
GetPath関数により、パスに「https:」が含まれる場合は、ローカルパスに変換して返すような仕組みになっています。
こちらは以下のように活用できます。
※こちらではメッセージボックスで表示させていますが、変数に格納するなどとして活用できます。
GetPath関数は、OneDriveの外であったとしても問題なく活用できるように開発しています。
第2引数の「”D:\OneDrive\”」はOneDriveのフォルダのパスになるので、必要に応じて変更してください。
GetPath関数の仕組みは、第1引数で受け取ったパスに「https:」が含まれる場合は、パスの前半部分を第2引数のOneDriveのローカルパスに置換して返すようになっています。
OneDrive内のパスのURLには法則性があります。
その法則は「4つ目の”/”以降が実際のOneDrive内の相対パス」というものです。
なので、4つ目の「/」の位置をInStrで探して、Left関数により、その位置よりも前の文字列を抽出して置換しています。
GetPath関数を活用することで、以下のようにローカルパスを取得できるようになります。
OneDriveのパスを正しく取得する方法については以上になります。
カレンダーの日付入力フォームの導入方法について解説しています。
曜日や祝日などの確認を行いながら日付を選択するだけで入力することができます。
領収書や請求書、見積書のフォーマットなどに組み合わせて使うと便利です。
00:00 挨拶
00:18 活用例
00:33 導入方法
02:08 使い方
03:53 注意点
07:28 活用例
08:10 まとめ
こちらではExcelの計算の仕組みや注意点、対策について解説しています。
まず最初に、次の計算結果を確認します。
これはB列の値からC列の値を引いた答えをD列に数式により結果を表示させています。
画像ではすべてが「0.1」と表示されています。
では、上記の内容を踏まえ、「引いた答えが0.1の場合に○」という条件式を書いてみます。
そうすると、ところどころ「○」ではない箇所が見つかります。
ここで多くの人は「え?」となります。
ただ、これはExcelの不具合ではなく、コンピュータで計算する上でどうしようもない問題になります。
最初の引き算の答え「0.1」の表示桁数を増やしてみます。
ところどころ、「0.1」ではなくなってしまっています。
それ以外でも、次の場合も正しく計算できません。
まずコンピュータがどのように数字を扱っているのかについて確認します。
通常、私たちが生活で目にする数字は0から9まで数えたら桁が上がり10,11,12,13となる10進数と呼ばれているものになります。
0から9の10種類の数字で成り立っているため10進数と呼ばれます。
しかし、コンピュータでは10進数でデータを扱うことができません。
基本的にコンピュータは電気で動いています。
電気の流れから取得できる情報は「電圧がある値よりも大きいのか小さいのか」といった情報になります。
そのため、送られてくる電気から取得できる情報は1回あたり2種類になります。
これを「0~0.9Vを0、1~1.9Vを1、…」のように10種類にすれば10進数でできるように感じますが、電圧は安定しません。
そのため、多少の誤差を考慮できるように、ある値(閾値)よりも大きいのか小さいのかで表現されています。
2種類の情報しかないため、10進数では表現できず2進数で数字を表現することになります。
2進数は0と1の2種類の数字で成り立っています。
そのため、0から1まで数えたら桁が上がり10,11,100,101,110となっていきます。
2進数から10進数を求める方法は2のn乗で掛けて計算します。
例えば、2進数「1001」の場合は、「1×2^3 + 0×2^2 + 0×2^1 + 1×2^0」となり10進数「9」になります。
2進数では小数も扱うことができます。
例えば、2進数「10.1」の場合は、「1×2^1 + 0×2^0 + 1×2^-1」となり10進数「2.5」になります。
つまり2進数で表現できる数は必ず2のn乗の合計になります。
そのため、10進数「0.1」など2進数では正しく表現ができない数が存在します。
10進数「0.1」を2進数で表現すると「0.000110011001100…」という感じに循環小数になります。
これは10進数「10」を3で割り切れないのと似ています。
このように2進数では10進数よりも表現できる数の種類が少ないため、誤差が生じます。
ここまでの内容を踏まえた上で「でも、Excelで0.1と計算できているのもあるじゃん」と思われた方もいるかと思います。
これは、ExcelがIEEE754の仕様に基づいて作成されているためです。
IEEE754というのは、IEEE(米国電気電子学会)という学会が決めたルールになります。
→コンピュータを全世界で連携するためにはルールを共通化する必要があります。
その仕様上、10進数で表現できる精度が15桁になるため、15桁より大きい数字は切り捨てられてしまいます。
そのため、上記の計算結果をさらに細かく書くと以下のようになります。
※Excelでは表現することができないため文字列で直接入力しています。
実際は0.1ではないのですが、15桁で切り捨てられた結果0.1になったというのが答えになります。
今までの内容を考慮すると、「小数点以下を含む計算の場合は、誤差が生じる可能性がある」ということを考慮する必要があります。
では、どのように対策すればいいのか3通りの方法を解説します。
「ROUND関数を用いて指定した桁数で四捨五入する」という方法があります。
ROUND関数の使い方は以下のようになります。
=ROUND(数値,桁数)
桁数に小数点の位置を指定します。
桁数が「1」の場合は小数点第一位まで表現できるように四捨五入、
桁数が「2」の場合は小数点第二位まで表現できるように四捨五入、
桁数が「0」の場合は一の位まで表現できるように四捨五入、
桁数が「-1」の場合は十の位まで表現できるように四捨五入、
というように表現できます。
計算結果にROUND関数を使うことで以下のように正しく判定することができます。
「元の値を10のn乗して整数にして計算する」という方法があります。
この方法を使った場合は以下のようになります。
この場合は、数字に対して単位などを書いておくとより分かりやすくなるかと思います。
「表示桁数で計算」という方法があります。
この方法では、先ほどのIF文のように直接0.1と比較する場合には使うことができません。
ただ以下のように計算結果を表示させる場合には活用することができます。
これは、見た目上は0.1だけど実際は異なるものになります。
そのため、この値と0.1と比較すると以下のようになってしまいます。
これに対し、以下の設定を行います。
[ファイル]→[オプション]→[詳細設定]→[次のブックを計算するとき]→[表示桁数で計算する]
この時に「データの正確さが失われます。元に戻すことはできません。」と表示されます。
これは「実際に見た目で確認ができる数字を正として計算する」という設定です。
そのため、正確さが場合によっては失われるので、そのような警告になります。
上記の設定を行うと以下のように反映されます。
この設定はブック全体に反映されます。
そのため、最終手段として使用を考えてみるといいかと思います。
※私的には推奨しません。
というような感じで以上になります。
Excelの奥は深いですね…
こちらでは、作成したマクロを効率よく呼び出すためのショートカットキーに割り当てる方法について解説しています。
まずは、一般的なCtrlキーとの組み合わせのショートカットキーの設定方法について解説していきます。
[開発]タブから[マクロ]を選択します。
※[表示]タブの中の[マクロ]でも大丈夫です。
以下の画面が表示されましたら、[オプション]を選択します。
ショートカットキーと書かれたCtrlの隣のテキストボックスに半角のアルファベットを入力します。
以下の例ですと、「a」と入力しています。
このように設定して[OK]で確定することで、「Ctrl + A」のショートカットキーが割り当てられました。
マクロに設定したショートカットキーは、既存のショートカットキーよりも優先順位が高くなります。
そのため、通常の「Ctrl + A」の全選択は無効になり、マクロ「test」が実行されるようになります。
ちなみに、設定時にShiftキーを押しながら半角のアルファベットを入力すると、以下のように「Ctrl + Shift」のショートカットキーが設定できます。
他、VBAでショートカットキーを設定する場合は以下のように記述します。
Application.MacroOptions Macro:="test", ShortcutKey:="a"
「test」がプロシージャ名で、「a」がショートカットキーになります。
上記の場合は、「Ctrl + A」に割り当てられます。
CtrlキーとShiftキーとの組み合わせを設定する場合は、大文字にします。
以下のように設定すると、「Ctrl + Shift + A」に割り当てられます。
Application.MacroOptions Macro:="test", ShortcutKey:="A"
設定したショートカットキーを解除する場合は、キーの部分を「””」にすることで解除できます。
Application.MacroOptions Macro:="test", ShortcutKey:=""
ショートカットキーの設定切替ボタンなどを作りたい場合は、VBAで直接設定する方法も覚えておくと便利です。
先ほど解説した内容ですと、割り当てることができるショートカットキーは全て「Ctrl」との組み合わせになります。
しかし、Excelでは「Ctrl」以外にも割り当てることができます。
その方法は、OnKeyメソッドというものを活用します。
一旦、先ほどと同じCtrlとの組み合わせのショートカットキーを割り当ててみます。
以下のように設定します。
Application.OnKey "^a", "test"
「test」がプロシージャ名で、「^a」がショートカットキーになります。
こちらを実行することで「Ctrl + A」に割り当てることができます。
注意しなければいけない点として、こちらの設定の場合は、Excelを終了するとショートカットキーが無効になります。
Excelを開いた後に、再度、上記の内容を実行しなければいけません。
Excelを開いたと同時に、上記の内容を実行できるようにするためには、ブックモジュールのOpenイベントプロシージャ内に記述するといいです。
「ThisWorkbook(デフォルトの場合)」と書かれているブックモジュールを開き、オブジェクト一覧からWorkbookを選択します。
そうすると、以下のような「Workbook_Open()」という特殊なプロシージャが入力されるため、その中に先ほどのショートカットキーを割り当てる内容を記述します。
「Workbook_Open()」はExcelファイルが立ち上げられたと同時に実行される特殊なイベントプロシージャというものになります。
また、OnKeyメソッドの設定は、対象のファイルが閉じられた後に関しても動かすことができてしまいます。
前半のオプションでの設定の場合は、対象のExcelファイルが閉じられると同時に、ショートカットキーが無効になります。
しかし、OnKeyの場合は、対象のExcelファイルを閉じた後にショートカットキーを入力すると、対象のExcelファイルが自動で立ち上げられ、マクロが実行されてしまいます。
対象のExcelファイルが閉じられたときに無効にする場合は、BeforeCloseというイベントプロシージャが活用できます。
先ほど作成したOpenイベントプロシージャ内にカーソルを置いた状態で、プロシージャ一覧からBeforeCloseを選択します。
そうすると、以下のような「Workbook_BeforeClose(Cancel As Boolean)」という特殊なプロシージャが入力されるため、その中に以下のように記述します。
プロシージャ名を省略することによって、ショートカットキー「^a」の割り当てをキャンセルすることができます。
イベントプロシージャについてはこちらの記事で詳しく解説しています。
続いて、Ctrlキー以外の割り当て方法についても確認していきます。
先ほど設定していた「^」はCtrlキーを意味します。
組み合わせて指定する場合は、「^」の他にも以下のものがあります。
+:シフトキー(大文字を直接入力してもシフトキーとの組み合わせ扱いになります) %:Altキー *:コマンドキー(2011年ExcelMacのみで動作すると公式には記載されています) |
例えば、「Ctrl + Shift + A」に割り当てる場合は、「+^a」や「^+a」、「^A」などと設定します。
他にも、「^6」で「Ctrl + 6」などとアルファベット以外にも設定することができます。
ただ、F1キーやHomeキーなど一部特殊な入力が必要なキーも存在します。
特殊なキーは以下になります。
BackSpace:{BACKSPACE} または {BS} Break:{BREAK} CapsLock:{CAPSLOCK} Clear:{CLEAR} Delete または Del:{DELETE} または {DEL} ↓:{DOWN} 終了:{END} Enter (テンキー):{ENTER} Enter:~ (ティルダ) Esc:{ ESCAPE} または {ESC} HELP:{HELP} ホーム:{HOME} Ins:{INSERT} ←:{LEFT} NumLock:{NUMLOCK} PageDown:{PGDN} PageUp:{PGUP} Return:{RETURN} →:{RIGHT} ScrollLock:{SCROLLLOCK} Tab:{TAB} ↑:{UP} F1 ~ F15:{F1} から {F15} |
こちらは以下の公式サイトを参考にしています。
https://learn.microsoft.com/ja-jp/office/vba/api/excel.application.onkey
こちらの内容を踏まえ、「Alt + F1」というショートカットキーを設定する場合は以下のようになります。
Application.OnKey "%{F1}", "test"
Enterキーのショートカットキーを活用したマクロはこちらの記事が参考になります。
という感じで、作成したマクロにショートカットキーを設定する方法については以上になります。
色んな日付を求める方法について解説していきます。
給料日の算出や年齢の算出などに役立ちます。
はじめに、日付はどのようにExcelで管理されているのかを理解する必要があります。
日付をセルに入力すると、同時に表示形式が”日付”や”ユーザー定義”などに変わるかと思います。
ちなみに、”2022/1/1(土)”などと直接入力されているのを見たことがありますが、編集時に曜日まで編集が出来てしまうのであれば、日付として認識されていません。
これでは日付の計算ができないため、修正する必要があります。
もしセル内に曜日も表示したい場合は、そのような表示形式で”yyyy/m/d(aaa)”などを設定する必要があります。
表示形式の設定方法についてはこちらを参考にご確認お願いします。
日付で管理されている場合は、表示形式を”標準”などにすることで、日付の実体であるシリアル値を表示させることができます。
シリアル値は「ある日を基準にした通し番号」になります。
Excelでは、基本1900年1月1日を「1」として数えられています。
ちなみに時間は、24分の1で計算します。
つまり、”44562″が2022年1月1日の場合、”44562.25″は2022年1月1日の6:00を表していることになります。
日付がシリアル値で管理されていると解説しました。
日付の実体が数字であることから、通常の計算式が使えることが分かります。
そのため、1日後や1日前を求める場合は、足し算と引き算で求めることができます。
ちなみに、今日の日付を基準に計算したい時は、今日の日付のシリアル値を返すTODAY関数が使えます。
※「返す」という表現は関数を実行した結果を受け取ることを言います。
明日の日付を求めたい時は、「=TODAY()+1」で求めることができます。
単純な日付の差を求める場合は、引き算で求めることができます。
この時、正しく表示されない場合は、表示形式が影響している可能性があります。
以下の例では、黄色のセルの表示形式は”標準”にしています。
上記の方法は、日にちの場合には便利ですが、月や年で求める際には計算が複雑になってしまいます。
そんな時に使えるのがDATEDIF関数になります。
※DATEDIF関数は入力候補に表示されない特殊な関数になります。直接入力することで使えます。
使い方は、「=DATEDIF(開始日,終了日,単位)」になります。
便利な単位のみ紹介しますと以下になります。
※DATEDIF関数は特殊な関数で、いくつか注意する点があります。
詳しくはこちらの公式サイトをご確認ください。
年齢を求める場合に関しては以下のように求めることができます。
他に、DATEDIF関数を使わずに年を求める方法も紹介します。
日付の年月日は、YEAR関数、MONTH関数、DAY関数で抽出することができます。
つまり、YEAR関数で年のみを抽出して引き算するという方法です。
ただ、単純な引き算ですと日にちなどが考慮されていないので注意する必要があります。
年月日をそれぞれ設定してシリアル値に変換するDATE関数を使います。
使い方は、「=DATE(年,月,日)」です。
基準日の翌月の5日を求める場合は以下のようになります。
※年月を別々で設定し、月に関してのみ+1して計算しています。
ちなみに、このDATE関数の月に「13」や「-1」など通常では存在しない値を設定したとしても、正しく計算されます。
日に関しても同様で大きすぎた分や小さすぎた分は年月に反映されます。
→つまり翌年は月に+12しても求めることができます。
月末を求めるにはEOMONTH関数を使います。
使い方は、「=EOMONTH(開始日,月)」です。
ちなみに、先ほど解説したDATE関数でも求めることができます。
※日の「0」は1日より1日前として計算されます。
営業日(週末や休日を除いた日付)を求めるには、WORKDAY関数やWORKDAY.INTL関数を使います。
WORKDAY関数の使い方は「=WORKDAY(開始日,日数,[祭日])」です。
こちらの関数は週末が土日で計算されます。祭日(休日)を設定しない場合は省略することができます。
WORKDAY.INTL関数の使い方は「=WORKDAY.INTL(開始日,日数,[週末],[祭日])」です。
こちらの関数はWORKDAY関数に加え、週末を自由に設定することができます。
週末の設定値は以下になります。
※省略時は土日として設定されます。
WORKDAY.INTL関数の方がWORKDAY関数に比べ汎用性があるため、こちらでは、WORKDAY.INTL関数で解説していきます。
例えば土・日を週末とし、土・日・休日を除いた3日後を求める場合、以下のようになります。
休日に関しては、引数の[祭日]に”L5:L9″と休日一覧の範囲を指定します。
次に、同じ条件で3日前を求めていきます。
引数の[日数]を負の値にすることで簡単に求めることができます。
次に週末を金・土・日にした場合の3日前を求めていきます。
週末の候補に金・土・日がないため、”0000111″と記述して求めていきます。
給料日などを求める際に、このような計算が必要になってくるかと思います。
指定日の直近の営業日を求めるにはDATE関数とWORKDAY(WORKDAY.INTL)関数を組み合わせることで実現できます。
まず、土・日・休日に含まれる翌月の7日(2023/1/7)を求めます。
この求めた日付を元に、直近の営業日を求めます。
営業日を求める際に、WORKDAY(WORKDAY.INTL)関数を使います。
ただ、WORKDAY(WORKDAY.INTL)関数では0日後や0日前を指定することができません。
そのため、次の営業日を求める場合は、1日前から1日後を求めます。
先ほどのDATE関数の引数の[日]を”6″にして、DATE関数をそのままWORKDAY(WORKDAY.INTL)関数の引数の[開始日]に設定します。
そうすると、以下のように求めることができます。
では、前の営業を求める場合についても確認していきます。
先ほどの逆で求めることができるので、1日後から1日前というようにしていきます。
基準日が今日で来月の給料日(例えば、25日、土・日・休日の場合は、次の営業日)を求める場合は、以下のようになります。
以上が、色んな日付の計算方法になります。
マクロ(VBA)を実行している時の待機時間の効率化として、ブック(Excelファイル)を操作する方法について解説していきます。
通常、マクロ(VBA)を実行している最中にExcelを操作することができません。
ブックを複数立ち上げると、通常は同じExcelアプリケーション上で複数のブックが立ち上げられます。
マクロの大元のVBAはExcelアプリケーションに紐づいています。
そのため、マクロを実行してExcelアプリケーションが待機している時間に関しては、他のブックであっても操作することができません。
マクロ実行中にブックを操作する方法には以下のようなものがあります。
・DoEvents関数を用いる
・別のアプリを立ち上げる
順に解説していきます。
前者の「DoEvents関数を用いる」に関しては、少し専門的な内容になります。
マクロを実行している最中は、CPU上でExcelアプリケーションの処理をしていることになります。
この処理が完了しない限り、Excelアプリケーションの他の処理は実行できません。
Excelを操作するという処理は、このCPUの中の「Excelアプリケーション」にて行われます。
そのため、Excelアプリケーションがマクロ処理を行っている時は操作ができなくなります。
ではどのようにしたらExcelの操作ができるようになるのかというと、
Excelアプリケーションでマクロを実行している間のExcel操作を他の処理に割り当てるという方法です。
※具体的に言うと、オペレーティングシステムにて処理させる方法です。
それがDoEvents関数になります。
例えば、以下のマクロ実行させるとしばらくの間、Excelが操作できなくなります。
Sub test()
For i = 1 To 100000
Range("A1").Value = i
Next i
End Sub
これはセルA1に1~100000を順番に入力するというプログラムです。
この中に以下のようにDoEvents関数を入れてみます。
Sub test()
For i = 1 To 100000
DoEvents
Range("A1").Value = i
Next i
End Sub
このようにして実行すると実行中にExcelの操作ができるようになります。
DoEventsが実行されるタイミングにExcelを操作した内容がオペレーティングシステムで処理されるようになります。
CPUのExcelアプリケーションとは別に、処理を割り込ませるイメージです。
Excelの操作が、DoEventsが実行されるタイミングで行われる必要があるため、下記のように記述した場合は、Excelの操作が出来ません。
Sub test()
DoEvents
For i = 1 To 100000
Range("A1").Value = i
Next i
End Sub
また、無理やり割り込んで操作をしているため、予期せぬエラーが起こる可能性があります。
特にセルに文字を書き込む処理などはエラーが起こりやすいです。
Range("A1").Value = i
また、エラーが発生した場合は処理が中断されてしまいます。
そのため、エラーにより処理が中断されないようにする必要があります。
エラーの対策として、エラーが発生した場合に無視をして実行するという方法があります。
それが「On Error Resume Next」になります。
これが実行されたプロシージャに関しては、プロシージャが終了するまでエラーが無視され続けます。
もし途中でエラーを無視したくなくなった場合は「On Error GoTo 0」を実行すると無効になります。
「On Error Resume Next」を活用した例が以下になります。
Sub test()
On Error Resume Next
For i = 1 To 100000
DoEvents
Range("A1").Value = i
Next i
End Sub
DoEventsを記述するとDoEventsの処理が増えるため、比較的、処理が遅くなります。
処理の遅延だけですと、GetInputState関数(Win32API)を使うという方法もありますが、問題点は遅延だけではなく、想定外のエラーが起こりやすくなるという点もあります。
そのため、こちらの方法に関して「マクロ実行中にExcelで別の作業をする」という目的での使用は推奨できません。
後者の「別のアプリを立ち上げる」に関しては、実行中のブックの操作ができません。
別のブックを操作する方法になります。
その方法のイメージは、以下の図のようになります。
別のExcelアプリケーションの立ち上げ方について2つ紹介します。
WindowsキーとRキーを同時に押して「ファイル名を指定して実行」を立ち上げます。
※Windowsマークのスタートボタンを右クリックして「ファイル名を指定して実行」を選択することでも立ち上げられます。
「ファイル名を指定して実行」が立ち上がりましたら、「excel /x」と入力してEnterもしくは「OK」を押します。
※「excel」と「/x」の間には半角スペースがあります。
これで別のExcelアプリケーションを立ち上げることができます。
後は、操作したいブックをこちらから開くことでマクロの実行中にExcelを操作することができます。
Excelアプリケーションを立ち上げているとタスクバーにExcelのアイコンが表示されているかと思います。
このアイコンを、ShiftキーとAltキーを押しながら左クリックすると以下のようなメッセージが表示されます。
「はい」を選択することで、別のExcelアプリケーションを立ち上げることができます。
後は、操作したいブックをこちらから開くことでマクロの実行中にExcelを操作することができます。
ピボットテーブルを用いて個別集計シートを作成する方法と、スライサーとタイムラインの活用について解説しています。
00:00 挨拶
00:11 作成(ピボットテーブル)
00:51 作成(個別集計シート)
02:15 作成(テーブルからピボットテーブル)
03:54 作成(スライサー)
05:41 作成(タイムライン)
06:12 補足
06:37 まとめ
ピボットテーブルをVBAで自動更新する方法はこちら
▼準備ファイル・完成ファイル▼
以下2点について順番に解説してきます。
そもそもVBSファイルとは何なのかについて解説します。
VBSファイルとはプログラミング言語「VBScript」で書かれたファイルのことで拡張子は「.vbs」になります。
このVBとは「Visual Basic」のことでMicrosoft社が開発したWindows上で動かすためのプログラミング言語になります。
そのVisual Basicをベースに作られたのが「VBScript」になります。
VBSファイルはメモ帳などで簡単に作成することができます。
メモ帳で書いた内容を保存すると通常は「.txt」で保存されますが、こちらを「.vbs」に変更すれば完成です。
例えば、下記のExcelファイルの「Set_Time」を実行する方法について解説します。
では、VBScriptで記述する内容を確認します。
Const FILE_NAME = "C:\Users\…\ファイル名.xlsm"
Const PROC_NAME = "Set_Time"
Dim app
Set app = CreateObject("Excel.Application")
With app
'Excel非表示
.Visible = False
Dim wb
Set wb = .Workbooks.Open(FILE_NAME)
.Run wb.Name & "!" & PROC_NAME
'アラートを消す
.DisplayAlerts = False
wb.Save
wb.Close
End With
app.Quit
基本的な書き方はVBAと同じになります。
①
「Const FILE_NAME = “C:\Users\…\ファイル名.xlsm”」で対象のファイルを指定しています。
「Const PROC_NAME = “Set_Time”」で実行したいプロシージャ名を指定しています。
「Dim app」でappという変数を用意し、「Set app = CreateObject(“Excel.Application”)」でExcelを操作するためのオブジェクトを用意します。
②
次に変数appを使ってExcelを操作していきますが、app.Visibleやapp.Runなどとappを何度も書くのは大変なのとまとまりがないため、「With app」でappを省略できるようにしています。
WithからEnd Withの中で「.」から始まっているものがappが省略されているものになります。
③
「.Visible = False」でExcelを非表示にしています。
「Dim wb」でwbという変数を用意し、「Set wb = .Workbooks.Open(FILE_NAME)」でFILE_NAMEに設定したExcelファイルを開いてExcelファイルの情報をwbに設定しています。
「.Run wb.Name & “!” & PROC_NAME」でPROC_NAMEに設定したプロシージャを実行させています。
実行させるプロシージャは、「(Excelファイル名)!(プロシージャ名)」で指定します。
④
残りは閉じる操作になります。
「.DisplayAlerts = False」で「…を保存しますか?」の確認画面を非表示にしています。
「wb.Save」でExcelファイルを保存しています。保存する場合は、「…を保存しますか?」が表示されないため、上記の「.DisplayAlerts = False」を省略しても問題ありません。
「wb.Close」でExcelファイルを閉じています。
一連の流れが完了したため、「app.Quit」でExcelアプリを終了させて完了です。
こちらでVBSファイルとテスト用のExcelファイルを配布しています。
今回は「タスクスケジューラ」を活用した方法について解説していきます。
こちらを活用することにより決まった時間にExcelファイルを立ち上げることができます。
また「1.マクロ(VBA)を実行させるVBSファイルとは」で解説したVBSファイルを設定することで決まった時間にマクロを実行させることもできます。
まずは、Windowsのスタートメニューにて「タスク スケジューラ」と検索し、アプリ「タスク スケジューラ」を開きます。
タスクスケジューラが立ち上がりましたら、「タスクの作成」より作成できます。
ただ、既存のタスクの中に追加で作成したものが混ざってしまうと管理がややこしくなってしまうので、新しいフォルダを用意していきます。
まずは「タスク スケジューラ ライブラリ」を選択します。
中央のモザイクがかかっている場所が「タスク スケジューラ ライブラリ」のフォルダ内にあるタスク一覧になります。
今回は新しいフォルダで作成していくので「新しいフォルダー…」を選択します。
→フォルダ名の入力画面が表示されましたら、好みの名前を設定します。
※こちらでは「TEST」という名前を設定しています。
フォルダ名の設定して「タスク スケジューラ ライブラリ」を展開すると作成したフォルダが見つかります。
作成したフォルダを選択して「タスクの作成」を選択します。
以下の画面が表示されましたら、好みの名前を設定します。
※他の諸々の設定については今回は割愛します。
次に「操作」を設定します。
ここで「新規」を選択し「Excelファイルを開く」という内容を設定します。
※VBSファイルの場合もここで設定します。
以下の画面で対象のファイルを選択し「OK」を選択します。
以下のように設定できましたら、「トリガー」を設定します。
※トリガーとは操作をするキッカケのようなものになります。
ここで「トリガー」を選択し「新規」を選択します。
ここでどのタイミングで実行するのかを設定します。
こちらでは「1回」のみ実行で指定した日時に実行する設定をしています。
設定できましたら「OK」で保存します。
これで設定が完了です。
後は時間になれば自動的に設定した内容が実行されます。
使い終わったタスクは、必要に応じて編集や削除するといいです。
VBAでシステムを開発し運用すると以下のような問題が起こる可能性があります。
・「Worksheets(“シート名”)」というように指定していたら、シート名が変更された
・「Worksheets(1)」というように指定していたら、シートの位置が移動された
…
この対策を兼ねて、シートの指定方法について「4選」それぞれの目的を兼ねて解説します。
※最後にまとめています。
シートを指定する方法に「Worksheets(“シート名”)」のようにシート名で指定する方法があります。
この方法のメリットとデメリットについて解説します。
[メリット]
普段使用しているシートではなく、別のシートに反映したい時に、別のシート名を変更することで別シートに反映させることができます。
メインのシートを変更したいと思った時に、変更先のシート名をメインのシート名にすることで反映先を変更することができます。
※同じシート名は複数設定できないので、変更時は先にメインのシート名を変更する必要があります。
他にもシートの順番が変更されたとしても問題なく実行することができます。
[デメリット]
シート名が変更されて対象のシート名が見つからなくなった場合にエラーになってしまいます。
例えば、「シート名」というシートを「2022年度」などと気を利かせたつもりで変更してしまうとVBAは正しく処理できなくなってしまいます。
シートを指定する方法に「Worksheets(1)」のようにインデックス番号で指定する方法があります。
シートのインデックス番号とは左から数えた通し番号になります。
この方法のメリットとデメリットについて解説します。
[メリット]
対象のシートを変更する際にシートの順番を変更するだけで対応することができます。
例えば、以下の場合、実行対象を「対象」シートから「シート名2」シートに移動するだけで実行対象を変更することができます。
他にもシート名を変更した場合でも、シートの位置さえ正しければ問題なく実行することができます。
[デメリット]
VBAのシステムについて詳しくない方がシートを移動させてしまうと、正しく処理ができなくなってしまいます。
対策として、「ブックの保護」をして動かせないようにするという方法などがあります。
シートにはオブジェクト名という固有のIDが設定されています。
プロジェクト一覧の中にある「Sheet1 (シート名)」の「Sheet1」がオブジェクト名になります。
このオブジェクトがシートそのものになるため、「Worksheets(…)」などの指定をする必要なく、直接オブジェクト名で指定することができます。
このプロジェクト一覧が表示されていない場合は、「表示」から「プロジェクト エクスプローラー」を選択することで表示することができます。
また、オブジェクト名は自由に変更することができます。
対象のシートオブジェクトを選択し、プロパティ内の「オブジェクト名」で変更できます。
※オブジェクト名は固有の名前である必要があるため、同じ名前は設定できません。
このプロパティが表示されていない場合は、「表示」から「プロパティ ウィンドウ」を選択することで表示することができます。
この方法のメリットとデメリットについて解説します。
[メリット]
シート名とは別の、シートそのもののオブジェクトの名前で指定しているため、シート名の変更やシートの位置の変更に対しても影響なく実行することができます。
[デメリット]
シートのオブジェクト名で指定されているため、見た目からは分かりにくくなります。
そのため、実行するシートを変更するためにシート名を変更したり、シートを移動されたりしても反映することができません。
→プログラム内でしか修正することができません。
有識者でない場合は、改修時に困惑される可能性があります。
シートを指定する方法に「ActiveSheet」と指定する方法があります。
「ActiveSheet(アクティブシート)」とは文字通りアクティブなシートになります。
この「アクティブ」という表現はITの分野において「現在有効なもの」という意味になります。
そのため、現在開いているシートがアクティブシートになります。
「シート名」シートを開いていると「シート名」シートがアクティブシートになり、「シート名2」シートを開いていると「シート名2」シートがアクティブシートになります。
ちなみに、以下のように省略した場合に関しては、自動的にアクティブシート扱いになることがあります。
アクティブシートになるのか、特定のシートになるのかは記述されているモジュールによって異なるため、詳しくはこちら(各モジュールの違い)にてまとめています。
この方法のメリットとデメリットについて解説します。
[メリット]
複数のシートに対して同じ機能を使用する際に活用できます。
また、「ActiveSheet.Name」でアクティブシートのシート名を確認することができるため、「指定したシート名の場合は実行する」などとといった分岐も可能です。
下記の場合、シート名が「2021年度」「2022年度」「2023年度」のいずれかの場合は処理が実行されます。
[デメリット]
用意したボタンから実行される前提で作成した場合、ボタンが押されるのは対象のシートがアクティブな場合のみなので、問題ないように感じます。
しかし、別のシートを選択している際にマクロから直接実行されると正しく処理が実行できない可能があります。
それぞれにメリット・デメリットがあるため、その時にあった指定方法で実装するのが良いかと思います。
「紙で管理していたデータ」や「スクショ画像」をExcelに読み込めるようになりました。
※2022/11/6現在では365のみ対応
1.[データ]→[データの取得と変換]にある[画像から]を選択します。
2.画像ファイルから読み込む場合は[ファイルからの画像]を選択し、スクリーンショット(スクショ)から読み込む場合は[クリップボードからの画像]を選択します。
※スクショしたい時はWindowsPCの場合、「Shift + Win + S」で簡単に範囲を指定してコピーできます。
3.画像を読み込むと以下のような画面が表示されます。
正しく読み込めている場所もあれば読み込めていない場所もあります。
修正が必要な値は下の表を選択することで直接修正することができます。
※対象の値を選択すると画像が拡大されます。
4.次のように修正できましたら、[データの挿入]を選択します。
5.次のような警告が表示されましたら、[データを挿入]を選択します。
6.このように読み込むことができました。
まだ文字認識の精度が完璧ではありませんが、画像を見ながらExcelに直接入力するよりも、「同じ値を入力してしまう」などの入力ミスが軽減されるかと思います。
紙データを集計する際などにぜひ試してみてください。
VBAでマクロ開発した処理の速度を高速化する方法を紹介します。
まずは処理時間・速度が遅くなる原因について考えます。
処理が遅くなる原因はほとんどの場合は以下の4つのいずれかに当てはまるかと思います。
それぞれについて詳しく解説します。
処理の中でセルを参照している回数や書き込む回数が多いと処理時間がかかります。
その理由は、毎回セルに確認する必要があるからです。
例えば、セルA1からA3に「あ」という文字を入力してみます。
Range("A1").Value = "あ"
Range("A2").Value = "あ"
Range("A3").Value = "あ"
このように処理をすると、3回の出力命令を行っていることになります。
次のように記述することもできます。
Range("A1:A3").Value = "あ"
このように処理をすると、1回の出力命令で完了します。
要するに、なるべく少ない出力にした方が処理時間が早くなります。
他にも、セルの情報には背景色や枠線など多くの情報があります。
そのため、①のようにセルの情報を毎回参照するのではなく、②のように変数に入れてから設定するの方が処理速度が早くなります。
1回だけの処理の場合は大きく変わりませんが、繰り返し処理などで数万回と処理するとなると影響してきます。
そのため、複数のセルを操作する場合は、配列に格納して処理を行うことで処理速度を改善できます。
※配列についての説明は割愛します。
「IF文の中にIF文、さらにその中にIF文」や「For文の中にFor文、さらにその中にFor文」などネストが深いと処理時間がかかります。
その理由は、単純に確認などの処理が増えてしまうためです。
IF文で例えると、①の場合だと、a~dすべてが0の場合、4回も確認作業をする必要があります。
それに対し、②の場合は1回の確認作業になります。※厳密には違います
これも、基本は「a=0」になる可能性が低い処理ならば、③のようにしても良いのですが、不規則の場合は②の方が良くなります。
※「a=0」が少ない場合は、初めのIF文のみで「ほとんどの場合にb、c、dの比較処理をしなくて済むためです
For文で例えると以下のような処理があるとします。
100回繰り返す処理の中に100回繰り返す処理があると、10000回の処理を行うことになります。
内側のFor文の数字が100から1000になれば、処理回数は900回増えるだけではなく、実際は90000回増えてしまいます。
処理の回数が増えれば増えるだけ処理に時間がかかるので、本当に必要な繰り返しなのかを考える必要があります。
次の①は無駄な処理になっています。
For文の中の、更にFor文の中のIF文に関して、本当にそこに必要なのでしょうか?
この場合は、i行目のA列に空白がなくても10000回繰り返されます。
それに対し②の場合は、内側のFor文を繰り返す前にIF文で絞っています。
この場合、i行目のA列に空白がなければ、繰り返しは100回しか行われません。
このような無駄な繰り返しを減らすことが処理の高速化のポイントになります。
計算方法が自動(数式が自動で計算される)になっている場合は処理時間がかかります。
その理由は、セルを操作する度に数式の更新が行われるためになります。
セル上の数式の実行結果を使用したい場合にはできませんが、数式の実行結果の反映を処理後にしてよい場合は、手動にすることで処理速度を上げられます。
ただ、プログラムを実行する度に数式を手動に変換するのは大変です。
そんな時は、以下の内容を処理の前後に記述することで解決します。
「Application.Calculation = xlCalculationManual」で計算方法の設定を手動にしています。
処理が終了した後に「Application.Calculation = xlCalculationAutomatic」で自動に戻しています。
これだけで劇的に改善されることがあります。
処理の内容をリアルタイムでExcelに反映していると処理時間がかかります。
その理由は、Excelに表示させるという動作が増えるためです。
長い処理などで途中経過を表示したいこともあるかと思いますが、基本的には処理が完了するまでExcelに反映する必要はないかと思います。
そんな時は、以下の内容を処理の前後に記述することで処理中の反映を止めることができます。
「Application.ScreenUpdating = False」で反映を止めて、「Application.ScreenUpdating = True」で戻しています。
次の処理だと動作のイメージしやすいです。
こちらはA1に「あ」と表示させた後に約5秒間停止して処理を終了するという内容です。
このコードの場合は、「Range(“A1”).Value = “あ”」が実行された時点でA1に「あ」と表示されます。
それに対し次のように書き換えると、5秒後の処理が終わるまで「あ」が表示されなくなります。
以上が処理速度を早くする方法です。
以下の4つを紹介しました。
前半の2つは、いきなりはハードルが高いかもですが、後半の2つだけでも処理時間がかなり短縮されることがあるので、ぜひ試してみてください。
※後半の2つを同時に使用する例は以下になります