Excelを勉強しましょう EnjoyExcel

アイコンの機能や関数の説明、VBA初心者向けの勉強方法について情報掲載しています。関数、VBAともに理解が進むように出来るだけ事例を交えて説明しています。

目次

f:id:EnjoyExcel:20220221092751p:plain

 当ブログについて

こんにちは。一般企業の会社員です。Excelを使って仕事をしています。

 

このサイトではExcel初心者様、中級者様に向けて情報を発信しています。主に私自身が勉強時に感じた事や関係者の皆様からご質問いただいた内容について解説しています。

 

ボタン1つで済むような処理から関数、VBAまで広く浅く情報を用意しています。加えて勉強方法や本の選び方等環境構築関連についても情報を準備しました。

 

全ての記事に対して気を付けるのは難しいのですが機能の説明だけにならない様に出来るだけ事例を主に機能を説明する様にしています。

 

理由は機能と仕事が結び付かず機能が活かしきれないというコメントが多かった為です。そんなわけで機能や説明をシンプルに説明するという建付けではなく少し文章多めの内容となっています。ご了承ください。

 

Excelに関する問い合わせはお問い合わせフォームから受け付けております。

お問い合わせフォームを準備してみました - Excelを勉強しましょう EnjoyExcel

 

最後に以下記事一覧となります。

 カテゴリー 各カテゴリーへジャンプします

 

  • ExcelVBA
  1. VBA-1 ExcelVBAとは?マクロとの違い できる事を解説

  2. VBA-2 環境構築をしましょう

  3. VBA-3 コードを書きましょう

  4. VBA-4 オブジェクト、プロパティ、メソッド・・・後で考えよう

  5. VBA-5 Rangeだけでもいろんな書き方がありますよ

  6. VBA-6 表の最終行と最終列ってどうやって指定するの?

  7. VBA-7 いろんなものが入る箱。変数を使いましょう

  8. VBA-8 変数っていろんな型がある。万能ですが形にこだわります

  9. VBA-9 セルに罫線を書く & 実行ボタン を用意してみよう

  10. VBA-10 メソッドについて触れてないので少し話をします

  11. VBA-11 メッセージボックス。アプリケーションっぽくなってきた

  12. VBA-12 繰り返し処理が出来ると沢山の仕事をこなせます

  13. VBA-13 VBAでは関数とワークシート関数って分けて使います

  14. VBA-14 VLOOKUP関数を知らない人でも使えるツールを作ってみよう

  15. ExcelVBA番外編 勉強初期にオブジェクトとプロパティは理解した方が良いのか? 

  16. 条件分岐-1 If~Then

  17. 条件分岐-2 Select~Case 

  18. 条件分岐-3 ElseIf 

  19. 条件分岐-4 If~Then(Ifのネスト) 

  20. 繰り返し処理-1 For~Next 

  21. 繰り返し処理-2 Do~ 

  22. 繰り返し処理-3 For Each~Next と ウォッチウインドウの使い方

  23. AからZZまで英語文字列を並べてみよう VBAの配列を使います

  24. ExcelVBAの配列を使って任意の文字列を作り出す箱を用意する

 

  • 関数

 

  • 既存の機能、ショートカット

 

  • 便利ツール

 

  • 勉強方法

 

不定期となりますが情報は随時追加していきます。

ExcelVBAの配列を使って任意の文字列を作り出す箱を用意する

f:id:EnjoyExcel:20220213205640p:plain

ExcelVBAの配列を使って任意の文字列を作り出す箱を用意する

 

 

では配列を使ったコードを見ていきましょう。前回からの続きでExcelVBAの配列を使ったコードを用意して作業をしていきます。

 

今回配列を使います。配列を覚えると処理時間が劇的に速くなり大幅にやれる事が増えます。ぜひご覧ください。

前回の記事

前回は主に配列の考え方と今回実行するコードの構想について書いています。続けて見ていただくと分かりやすいと思います。

enjoyexcel.hatenablog.com

コード:構成

早速コードを紹介します。まずはコードの構成です。

f:id:EnjoyExcel:20220213210241p:plain

コード:構成の1、2

続けて前半3ブロックまでのコードです。今回は配列を使うので画面遷移が無いです。ScreenUpdationgの効果は得られない為書かなくても良いのですがこれを説明するとまた話がそれるのでスルーしてください。というわけで今回は書いても書かなくてもどちらでも良いです。

f:id:EnjoyExcel:20220216091017p:plain

 

まずは変数の定義です。変数定義ゾーンの一番最初に居る

Dim E(1 to 702) As String

というのが配列の定義です。他は今までの記事を見ていただいていれば大丈夫だと思うので説明しません。

 

配列を使う際は以下内容を決める必要があります。

配列を使う際の決まり

種類を決める ・・・1次元配列か2次元配列かを決める。

枠の宣言をする ・・・どれだけ枠をストックするのか。型も決める。 

情報を収納する ・・・前回記事のロジックで情報を収納。

その後の処理に展開する ・・・ここではセルに書き出すという事になります。

 

Dim E(1to702) As String で配列の種類と枠の宣言に対応しています。配列のかっこ内の数値を添え字というのですが1次元配列を見てみると添え字が1つです。

E(1)

E(2)

E(3)

といった感じです。

 

宣言時は1と702をtoで繋いでいるのでかっこ内の数値としては2つある様に見えるのですが1つの添え字(値)が1から702まで変化しますよという事を定義していますので1つと数えます。

 

以下で添え字が2つの2次元配列を見てもらいます。実際見てみると違いが分かると思います。

 

2次元配列というのはコンマで区切られたうえで添え字が2つになります。配列Eを使ってたとえると・・・

E(1,1)

E(1,2)

E(1,3)

といった感じです。軸が2つ持てます。前回記事のマンションの様に例えてみるとEタワーマンションの1階の1号室、2号室、3号室という様に1つの階層の中でさらに階層を構築出来るようになります。ワークシートのセルと同じです。ってことはCellsで行列を表現するのに似てますね。これは後でヒントになるので頭の片隅に置いておきましょう。

 

次は枠の宣言です。いくつ枠を使うのかあらかじめ宣言します。配列は最初にいくつの枠を用意するのか決まっている場合と決まってない(決められない)場合があります。今回は終わりが分かっているので事前に添え字は(1to702)と宣言してしまいます。決まってない場合の説明は別の機会にします。

 

今回は1次元配列を使うという事が決まりました。添え字と一緒に型の宣言もします。アルファベットは文字なのでStringにしましょう。

 

これで配列を使う際の決まりである「種類を決める」「枠の宣言をする」が完了しました。コードの構成の「変数の定義」ブロックが完了です。

 

次はコード構成の「AからZまでを準備する」というブロックです。ここで配列を使う際の決まりにありました「情報を収納する」という処理を行います。

 

For emt = 1 To 26

    E(emt) = Chr(n)
    n = n + 1

Next

 

For~Nextでemtという変数が1から26まで変化しますね。その中で関数Chrの引数を1つずつ変化させて配列に値を収納させています。関数Chrの説明は前々回の記事を参照ください。

enjoyexcel.hatenablog.com

 

E(1) = Chr(65) A

E(2) = Chr(66) B

E(3) = Chr(67) C

・・・

 

という結果になります。配列でも基本的に左辺に右辺を代入という形は変わりません。これで簡単に配列に値をセットする事が来ます。ローカルウインドウを見ながらやっていくと分かりやすいと思います。

コード:構成の3、4

コード構成の3つ目「AAからZZを用意するための変数を準備」ブロックに入ります。

 

n = 65
nmr = 1

 

nはChr関数をAに戻す為に65をセットし直しています。nmrはアルファベットの1桁目を管理するのに使用する変数に最初の値として1をセットしています。

 

では後半のコードです。最初は「AAからZZまでを準備」ブロックです。

f:id:EnjoyExcel:20220214103443p:plain

For emt = 27 To 702

    E(emt) = E(nmr) & Chr(n)
    n = n + 1
    
    If emt Mod 26 = 0 Then
    
        n = 65
        nmr = nmr + 1
    
    End If

Next

 

最初のFor~Next文は配列Eの添え字27から添え字702までをループするためのコードです。

 

そのあとは前回記事で説明した建付けを実現させるようにコードを書いてます。ちょっと違うのはループの中に  If emt Mod 26 = 0 Then というIF文が居ますね。このコードがポイントです。これで1桁目をAからBに送る細工をしています。

 

今回はModという関数を使っています。「emtを26で割った時の余りがゼロの時は・・・」と聞いています。つまり変数emtが26の倍数の時だけTrueという事です。この時は必ずnが90を示すようにしています。つまり2桁目がZを示すときという事です。

 

2桁目がZだったら1桁目の添え字を1つ送る+Chrの添え字を65に戻すというコードになってますので以下のようなアルファベットの並びが準備出来るようになります。

 

E(27) = E(1) & Chr(65)  A & A  AA

E(28) = E(1) & Chr(66)  A & B  AB

E(29) = E(1) & Chr(67)  A & C  AC

E(30) = E(1) & Chr(68)  A & D  AD

E(31) = E(1) & Chr(69)  A & E  AE

・・・

E(52) = E(1) & Chr(90)  A & Z  AZ

E(53) = E(2) & Chr(65)  B & A  BA

・・・

E(706) = E(26) & Chr(90) → Z& Z → ZZ

コード:構成の5

コード構成の最後は「AからZZまでをセルに書きだす」のブロックです。配列を使う際の決まりの中の最後の要素「その後の処理に展開する」という処理になります。

 

今回は1次元配列でしたね。この1次元配列を2次元配列に書き直すと以下のようになります。

 

E(1) E(1,1)

E(2) E(1,2)

E(3) E(1,3)

・・・

という様に1次元配列の添え字は2次元配列に置き換えると2つめの添え字と同じになります。これをCellsに置き換えると1行目で(以下画像の赤枠)列方向に値が用意されていくことになりますよね。でもデータの貼付けは1列目固定で行方向(以下画像の青枠)に値を用意したいと思っています。

f:id:EnjoyExcel:20220214101823p:plain

 

そんな時は「行/列の入れ替え」を使います。ワークシートでの作業時にセルの内容をコピーしてから貼り付けたい場所で右クリックすると貼付けのオプションが出てくると思います。

 

その時の左から4番目に出てくるアイコンと同じ作業をVBAでやってからセルに貼り付けることで行方向に値が並ぶように貼付けを実行する事が出来ます。

f:id:EnjoyExcel:20220213222246p:plain

VBAで書くとTranspose関数になります。引数で配列のEを指定します WorksheetFunctionを忘れない様にしてください。

 

Range(Cells(1, 1), Cells(702, 1)) = WorksheetFunction.Transpose(E)

 

E(1,1) E(1,1)

E(1,2) E(2,1)

E(1,3) E(3,1)

 

というように添え字が変化します。(実際は変化しませんが行列を入れ替えると上記の様に表現する事が出来るという事です)

 

これでセルA1からセルA702にAAからZZを用意する事が出来ました。

まとめ

配列を使うとセルにあらかじめ値を用意しておかなくても色んなことが出来ます。しっかりと「配列とは・・・」という説明はしてないのですが使ってみるとなんとなくわかってもらえると思います。

 

そういえば前回記事にもう一つの配列を使う条件として「手数の多すぎるコードを書いて処理が間に合わない時」というのがありましたね。

 

今回のように情報が少ないかつ簡単な時はまだ良いのですが数十万、数百万のセルをかまうというコードになるとCellsやRangeで書いていると処理が追い付かずフリーズしてしまいます。配列を使う事で毎回セルの値を参照しなくても良いので処理が高速化されてフリーズを防ぐ事が出来ます。

 

イメージですが図書館で本の場所を検索→本を取りに行く→本を読む→内容を理解するという人より図書館に入った瞬間に本の場所と内容を一瞬で全部暗記してしまう人が居たら(居ないけど・・・)ダントツで後者の方が速いですよね。

 

VBAは遅い」とたまに言われますがコードが悪いだけで配列を使えばストレスなく高速に処理する事が出来ます。

 

どんなコードだと処理が遅くなってどうやって書けば速く処理出来るのかはまた別の機会に説明させていただきます。

 

他にも2次元配列の詳しい説明や枠の宣言時に収納する量が決まってない時のコード等々説明してない事がたくさんありますが今回はここまでとさせていただきます。

 

また質問があったら記事を書いていこうと思います。失礼しました。

AからZZまで英語文字列を並べてみよう VBAの配列を使います

f:id:EnjoyExcel:20220212021604p:plain

AからZZまで英語文字列を並べてみよう VBAの配列を使います

 

前回の記事ではAからZまで並べて英語をみましたがさらに「AAからZZまで欲しい」って事でしたね。

 

関数でも出来るんですよ。ネット記事を見てもいろいろ書いてあります。

 

でも1列で全部書くことが出来る関数はなかなか無くてAからZまで作ってからAAからAZまで作って・・・を繰り返してZZまでというものが多い様です。

 

  • 関数をたくさん書きたくない
  • とにかく手数を減らしたい
  • 再現性が欲しい
  • 1列でAからZZまで書きたい
  • 関数よりVBAのが好き

 

という事もありVBAで書いてみます。配列も使いたかったのでちょうど良いと思って今回記事を書いてみます。

前回記事からの続き

enjoyexcel.hatenablog.com

VBAの書き方についてはシリーズで書いた記事がありますのでそちらを読んでいただいてからの方が理解が早いと思います。

enjoyexcel.hatenablog.com

早速コードの検討に入りましょう。

コード:A~Z

では始めます。まずはAからZまでを用意するコードです。セルA1にA、セルA26にZが記入されるコードです。

f:id:EnjoyExcel:20220212022452p:plain

VBAではChrという関数を使います。ワークシート関数CHARと少し文字が違いますが内容は同じです。

 

ポイントとしてはFor~Nextでは行を進める変数を用意しループ内でASCIIコードを進めるコードを書いているという事ですね。

enjoyexcel.hatenablog.com

コード:AA~ZZ 配列を使い最後にセルに書き込みます

AからZまでを用意するコードに続いてAからZZまでを用意するコードの説明に入ります。1番大きなポイントである配列を使います。難しいので2回に分けて説明させていただきます。今回は大まかに理屈を説明して次回でコードを準備します。

 

配列はVBA側で記憶領域を確保してそこに情報を収納するという機能です。一旦必要な情報を先に揃えましょうという時に使うコードです。

 

先?ただ情報を収納するだけ?何でそんなことするの?って思いませんか。

 

このあたりでいろんなネット記事や本ではまず「配列とは・・・」から書いてますが私はイマイチ分からなかったので今回はコードを説明して動きを確認しながら「配列とは・・・」をやろうと思います。多分その方が分かると思ったからです。

 

では話を戻します。私は配列の勉強をした時に何で配列を使うのかが全く分かりませんでした。セルに情報は居るからそれを使えばいいのに何で先に情報を全部取り込まないといけないの?と思っていました。

配列:どんな時に使うのか

というわけでまずはどんな時に配列を使わないといけないのかを提示します。私が疑問に思った「何で配列を使わないといけないのか」についても分かると思います。

 

  • セルじゃないところに情報を持ちたい時
  • 手数の多すぎるコードを書いて処理が間に合わない時

 

大きく分けてこの2つの場面に対面したときに配列を使わないといけなくなります。

 

今回は「セルじゃないところに情報を持ちたい」という事例なので配列を選択しました。

配列:考え方

「ん?」、「なに言ってんの?」ってなっていると思いますのでまず画像を見てもらってから説明しますね。どうやってこの状態が作られたかは後で説明しますので画像の並びだけざっくり見てください。

f:id:EnjoyExcel:20220213172204p:plain

f:id:EnjoyExcel:20220213172106p:plain

長すぎるので2つにして最初と最後だけ表示させています。これはVBEのローカルウインドウの一部を見てもらっています。

 

Eというのは適当に付けた配列の名前です。先程配列はVBA側で用意された記憶領域だと書きましたね。上記画像は記憶領域に用意された配列Eにアルファベットを順に収納したものを見てもらっています。配列Eの型はString型で1to702と書いてあります。1から702までという事です。

 

かっこ内の702という数値はAからZZまで702個の情報が発生するので先に収納する場所を確保していると思ってください。建物に例えるとEタワーマンションは702階あり各階には部屋が1つずつあります。1階にAさん、2階にBさん・・・702階にZZさんという感じでしょうか。「各階に1部屋で702階?」ってあまり無い構造ですがまずはイメージとしてとらえてください。

 

これは1次元配列というものになります。ひとまずこの説明も一旦止めて今回は先に進みます。

 

情報の収納が完了するとローカルウインドウで見たときに上記のような状態となります。最終的にはE(702)には"ZZ"が収納されているという状態です。

 

セルに書き出す前にExcelの中でAからZZの並びを作って配列に収納したものを見てもらいました。これが配列を使用する際の要件として提示した「セルじゃないところに情報を持ちたい」という事です。

 

確認ですがこの作業はVBAの記憶領域内で情報を作っているのでExcelのセルにはまだ1回もアクセスしてません。こうすることでセルにAからZの情報が無くてもAからZZの文字を準備する事が出来ます。あとはセルに貼るだけです。

コードの建付けを決める

実際にコードの検討に入ります

では具体的にどうやって情報を準備したのかを説明します。

 

私が考えたのは以下のような方法です。先に説明した様にE(1)からE(26)はAからZになるはずなのでE(27)からは・・・

 

E(27) = E(1) & Chr(65)  A & A  AA

E(28) = E(1) & Chr(66)  A & B  AB

E(29) = E(1) & Chr(67)  A & C  AC

E(30) = E(1) & Chr(68)  A & D  AD

E(31) = E(1) & Chr(69)  A & E  AE

・・・

E(52) = E(1) & Chr(90)  A & Z  AZ

E(53) = E(2) & Chr(65)  B & A  BA

・・・

E(706) = E(26) & Chr(90) → Z& Z → ZZ

 

のように出来ないかと。。。ポイントとしては以下5つ

 

  1. 1桁目と2桁目のループを一旦別々に考える。
  2. 1桁目と2桁目を&でつないで配列に収納する。
  3. 1桁目はE(1)からE(26)をループさせる。AからZという事ですね。
  4. 2桁目はAから始まりZになった時に1桁目が次のアルファベットに移行する。
  5. 2桁目は1桁目がループしたらまたAからZのループを繰り返す。

 

セルにアクセスせずこれが出来れば1列にAからZZまで規則的にアルファベットを並べることが出来るのではと考えました。

 

セルの一部にAからZを2列書いておいてそれぞれをループさせて&で繋いでも出来ますしこの方がはるかに簡単です。ただし一旦セルに情報を書いておかないといけないですよね。それが嫌で「セルじゃないところに情報をもちたい」時には配列を使うようにしています。

イメージしやすいようにセルの情報を使う場合の画面を用意してみました。A列B列ともにAからZまでアルファベットが並んでいます。

 

A列1行目のAを1桁目にしてB列をループ。B列の各セルを参照時に1桁目と2桁目を&で繋げばAA、ABのような文字を用意する事が出来ます。B列でZまで到達したらA列をループさせてBにするというコードを書けばこれでも出来るのですが画像にある様に一旦セルに記号を書いておく必要があります。

まとめ

セルに値を置いてからという方法でも全然良いのですがどうせなら下準備とかなく全部VBAでやりたくないですか。それだけです。

 

では実際にコードを考えましょう。続きは次回とさせていただきます。

Excelで英語のAからZまで書き出したい CHAR関数を使う

f:id:EnjoyExcel:20220212014423p:plain

Excelで英語のAからZまで書き出した CHAR関数を使う

 

問い合わせ

「関数を使ってAからZまで書き出したいんだけど・・・」出来ますか?という問い合わせがありました。この案件を解説してみようと思います。全部で記事は3つになります。

 

 

最初の記事はAからZまでを関数で用意してみました。残りの2つの記事ではAからZZまでをVBAを使って用意してみました。

解説

イメージですが数字みたいに簡単にアルファベットが並ぶのではないかと考えてる人はけっこう多いと思います。私も昔はそうでした。たとえばセルA1に1を書きます。

f:id:EnjoyExcel:20220212003351p:plain

セルの右下にカーソルを合わせてCtrlを押しながら下に引っ張るとセルには連番が入力されます。下の写真はひとまず10まで入力しました。

f:id:EnjoyExcel:20220212003309p:plain

ってことでセルA1にAって書いて同じことやったらB、C、D・・・と値が入るような気がするのですが入りません。なぜでしょうか??

 

文字なので特に計算で決まるものでもなく人が決めた決まりだから数字のような規則性がないという事に尽きるのですがとはいえエービーシーディー・・・みたいな歌もあるのでどっかでは決まってます。

 

どっかというのはASCIIという規格です。

American Standard Code For Infomation Interchange という英語の略らしく私はASCⅡ(最後の2はローマ数字)かと思ってましたがASCII(最後はIを2つ)らしいですね。

 

今は American National Standards Institute(略してANSI)という名前です。ANSIなら聞いたことある人も居るかもしれません。ざっくり覚えるならば規格の名前です。日本のJISみたいなもんですね。

 

文字コードって結構奥が深くてCSVを変換する時にも結構厄介なことが起こるのでしっかり勉強しないといけないのですが今回はここを掘り下げてもそんな成果はないのでやめます。

 

ひとまずASCIIコードの中ではアルファベットは連番で定義されてるんだなと思ってください。まず連番ですが我々は日常生活の中で10進数を使っています。その10進数で表現するところの65番目がASCIIコードだとAにあたります。

f:id:EnjoyExcel:20220212010258p:plain

上記のような並びになっています。これは決まりなので理屈は無いです。覚えてください。

これを表現できる関数があれば数字みたいに連続でアルファベットを並べることが出来ます。

CHAR関数について

今回は CHAR という関数を使います。

 

CHAR(文字コード に対応する番号)

 

具体的にはセルに =CHAR(65) と書けば A が出てくるわけですが数字のようにセルの右下をつかんで下方向に展開してもずっとAが表示されます。

f:id:EnjoyExcel:20220212011254p:plain

かっこの中の数字は進まないのでずっと同じ関数が入り値はずっとAです。これだと目的が達成されません。どうしましょうか。

 

他に数字を使えるところを探しましょう。

f:id:EnjoyExcel:20220212011905p:plain

こんな感じでアルファベットを表示させたいセルの横に数値を書いておいて参照するかたちで引数に使うという手もありますが数値を書いている列が1列余計ですよね。

 

よく見るとA列に並んでる数値のさらに左に数値が居ますね。・・・これ行番号です。使えるの??と思う方居ると思いますが実は使えます。

CHAR関数にROW関数を組み合わせる

これを使うにはROWという関数を使います。

 

ROW(行番号が知りたいセル)です。

 

()のようにかっこ内に何も書かないと関数を書いたセルの行数を返します。これを使いましょう。

 

セルA1に関数を書くとこのようになります。

=CHAR(64+ROW())

A1セルに出来た緑の枠の右下をつかんで下方向に展開すると相変わらず同じ関数になりますが表示はA、B、C、D・・・のように英語が並びます。ROW関数によってかっこ内の数値を進めることが出来る結果このような値を得ることが出来ます。

f:id:EnjoyExcel:20220212013354p:plain

これはかっこ内の数値は他セルに展開しても変わらないというのを逆手に取った書き方になりますね。

 

もう1つのポイントとしてはスタートのセルに書くCHAR関数のかっこ内の引数には書き込む数値と行の値を足して65にする様にしてください。

 

理由は先ほど説明したようにASCIIコードのAは10進数の65だからです。

まとめ

ちょっとテクニック的な要素も入ってますがゆっくり考えるとわかると思います。これがエクセルの楽しいところだと思います。

 

これで依頼者さんへの回答になったかなと思っていたのですがほんとは「AAみたいに2桁にしたい」って言うんですよ。

 

「最終的にはZZまで欲しい」って。欲張りなわけですよ。さ~どうしましょうか。

 

もちろん関数でも出来るのですがせっかくなのでVBAでやってみようと思います。配列を使う良い機会だと思いますので配列を交えてコードを書いてみようと思います。

VLOOKUP関数の第4引数"TRUE"の使い方 事例を使って解説

VLOOKUP関数 第4引数"TRUE"の使い方 事例を使って解説

 

 

本やネットで紹介されているのは第4引数がFALSE(完全一致)での使い方の方が圧倒的に多いのであまり知られてないのですがこれもまた便利な機能です。

 

質問

「最後の引数がTRUEを使う時はどんな時ですか?」という質問をいただきました。

 

確かにVLOOKUP関数の使い方(条件)によっては第4引数はTRUEじゃないといけない時が出てきます。この4番目の引数の使用方法について説明していきたいと思います。

 

実際関数を書くと以下のように説明が出てきますよね。

TRUE - 近似一致:範囲の先頭列の値は昇順で並べ替えておく必要があります。

FALSE - 完全一致:VLOOKUPでは、完全一致のみ検索されます。

 

エクセルさんは親切に・・・というつもりですが分かってる人じゃないと分からない説明をしてくるので何の事なのか分かりにくいです。対応出来てそうで来てない感じがあります。

 

どちらも使用にあたっての注意事項なのですが近似一致は関数を書く前に注意することを指摘していて完全一致は関数を書いた後の結果について指摘をしています。

 

完全一致については「第1引数の検索値と一致したものしか抽出しませんよ」と言っています。VLOOKUP関数を完全一致で使っている方はイメージで「そうだよね」となると思います。

VLOOKUP関数について

参考までにVLOOKUP関数に関連する記事を載せておきます。(第4引数は完全一致で紹介しています)

近似一致については使用するにあたっての注意事項です。では実際に使用してみましょう。

事例紹介

B列に宿泊施設名、C列に価格。D、E列はG、H列の情報をもとにランクを設定しています。D列はIF関数で抽出しE列はVLOOKUP関数で抽出しています。B列は商品名、C列を単価とかにしても使えそうです。余裕がある方は皆さんの身近な事例で置き換えてこの記事と一緒に作業してみてください。

 

D、E列では結果が同じになるようにしています。

 

1つのシートに2つのリストを用意してみたりリストなのに空欄が・・・等々突っ込みどころもあるのですが説明の為あえてこのような仕様にしています。ここはひとまずご容赦ください。

D4セルに書かれている関数です。

2つの関数を比較

=IF(C4<$G$4,"",IF(C4<$G$5,$H$4,IF(C4<$G$6,$H$5,IF(C4<$G$7,$H$6,IF(C4<$G$8,$H$7,IF(C4>=$G$8,$H$8,""))))))

 

ん~・・・見たくない。でもこういった関数の使い方は皆様の身近なところにもありませんか。ある意味理解出来ているので文字数の多い関数が使えるのですが誰得なのか分かりませんよね。出来るだけやめたいものです。

 

代わってE4セルに書かれている関数です。

 

=IFERROR(VLOOKUP(C4,$G$4:$H$8,2,TRUE),"")

 

違いは明らかです。省略されているわけではなく式がすっきりしてますよね。エラーも出てないです。一見信じがたいのですがIF関数で書いた式とVLOOKUP関数で書いた式は同じ戻り値を返すという事になります。


コピーしたかの様にデータは完全に一致しています。入力が少なくて済むのでエラーが出にくい式をセットする事が出来ました。

 

第4引数TRUEの式について説明

IFERROR関数については以下記事で説明しています。(先程紹介させていただいた記事と一緒です)

enjoyexcel.hatenablog.com

関数を読めば分かるのですがVLOOKUP関数で指定している範囲は以下のようにG、H列に居る価格と範囲を示したテーブルになります。

先程書きました関数を書いているときに出てくる注意点はこのように範囲を指定した際に注意するべき事を言っています。

 

TRUE - 近似一致:範囲の先頭列の値は昇順で並べ替えておく必要があります。

見てみると範囲の先頭列(価格列)は昇順になってますよね。上記ふまえて次の数式をみていきましょう。

 

=IFERROR(VLOOKUP(C4,$G$4:$H$8,2,TRUE),"")

 

C4にいる宿泊施設の価格が5,000円から9,999円までがランクD、10,000円から14,999円までがランクC・・・という様に検索値がどの価格帯に当てはまるのかを考えてくれて結果を返してくれます。5,000円未満は空欄となります。

 

察しが良い方なら既にお気づきかと思いますが先程範囲を昇順にしたのはこのためです。すごいのは範囲の上限を超えた値までしっかり戻り値を返してくれるところです。下限を下回る値はIFERROR関数でカバーする必要があります。

 

D列の様にIF関数を使っても書けるのですが明らかにVLOOKUP関数を使った方がスッキリしていますよね。検索値が文字列だと使えない等制約はありますがハマれば良い使い方が出来そうですよね。


VLOOKUP関数のツボとコツがゼッタイにわかる本

スピードマスター 1時間でわかる エクセル VLOOKUP関数 デキる同僚はみんな使ってる!

スピードマスター 1時間でわかる エクセル VLOOKUP関数 [改訂2版]

お知らせです

 関数は自力でも十分勉強出来ます。関数については知識の定着を目指す中で書籍が有効なツールの1つだと思っています。

 

私は習得したい知識についてはネットで色々調べますが最終的は書籍を購入しています。厚めの本は電子書籍で購入しています。

 

数値化出来ない主観的な話ですが読み返すことで知識の定着率が高まると感じています。書籍はおすすめコンテンツの中の1つだと思っています。

 

書籍の選び方や読み方についても解説しています。良かったらご覧ください。

Excel初心者の方向け本の選び方と読み方について解説します

 

最後に私が用意しておりますすべての記事へのリンクです。

目次 - Excelを勉強しましょう

 

今回はここまでとさせていただきます。

パソコンの画面情報を簡単に切り取りたい 作業効率を上げる方法

f:id:EnjoyExcel:20220224111757p:plain

パソコンの画面情報を簡単に切り取りたい

 

Excelで作業するにあたって重要なアプリケーションを紹介します。Snipping Toolという便利機能です。画像処理においてかなり効率良く作業出来るようになりますので是非ご覧ください。

 

これはWindowsの便利機能になります。最近のPCならほぼ搭載されていると思いますので皆さんご使用いたけると思います。

 

これをショートカットで起動するとものすごく作業が速くなります。インプット出来たら必ず使うツールになりますよ。

事例紹介

早速ですが作業した内容を残したい時にマニュアルを作成する事ってありませんか。

 

マニュアルには画像を付けてあげると分かりやすいものになるのですが処理が面倒なので文章量で内容をカバーしてしまう事もあるかと思います。

 

他にも質問したい時に相手に向けてこちらの画面のスクリーンショットをメールで送ってから電話する・・・等々色んな場面で画面に映っているデータを切り出したい時があります。

 

「画像がサクサク用意出来たら楽なんだけどな~」って声を結構いただくんですよ。

 

検索すると 画像コピー、スクショ、画面の切り取り(画面切る??)等々色んなワードで検索されていました。

 

そんな中私は以下アプリを使ってくださいと伝えています。Windowsに備わっている機能ですのでお金もかかりません。ダウンロードする手間も無くすぐ使えます。今回はこのアプリを紹介させていただきます。

アプリの紹介

Windows10では「切り取り&スケッチ」というアプリです。

f:id:EnjoyExcel:20220224112844p:plain

 

Windows11では「Snipping Tool」というアプリ名です。もともと「Snipping Tool」でしたがWin10では「切り取り&スケッチ」みたいです。

f:id:EnjoyExcel:20220224112907p:plain

ショートカットで起動させよう


ただし毎回アプリを起動させて画像を切り取ると面倒ですので起動にはショートカットを使いましょう。Windowsマーク+Shift+S」で起動します。

f:id:EnjoyExcel:20220224123901p:plain

押しにくいのでWindowsマークとShiftは左手」で押してから最後に「Sを右手」といった感じで分けてもOKです。

 

同じタイミングでは押せないので最終的に3つ押す事が出来ればOKと考えてトライしてみてください。私は両手使ってますよ。

操作説明

ではアプリが起動するとどうなるのか。やってみましょう。(一部ぼかし入れてます)

f:id:EnjoyExcel:20220224125035p:plain

 

画面が薄暗くなって中央上部に以下の様な表示が出ています。右端のバツボタンは「閉じる」ボタンですので薄暗い画面から抜けたい時に押します。残りのアイコンはどうやって画像を選択するのかを選ぶことが出来るようになっています。

 

一番右側の図形だけ青くなってますがデフォルトで選択が入っているだけです。カーソルを合わせて数秒待っていると説明が出てきます。

f:id:EnjoyExcel:20220224125225p:plain

 

一番左のアイコンは「四角形の領域切り取り」という名前です。名前の通りでマウスで指定した四角の範囲内の画像だけ切り取ってくれます。

f:id:EnjoyExcel:20220224125545p:plain

 

オートシェイプで四角形を書く時の様に範囲を指定出来るので好きなところだけ簡単に画像を取得する事が出来ます。

 

1つ左のアイコンは「フリーフォーム領域切り取り」という名前です。こちらも名前の通りでマウスで指定した任意の範囲内の画像だけ切り取ってくれます。使っていただくと分かるのですがこれは難しいです。

f:id:EnjoyExcel:20220224125945p:plain

 

続いて真ん中のアイコン。「ウインドウ領域切り取り」です。

f:id:EnjoyExcel:20220224130331p:plain

 

取得したい画面を選択してから1画面全てを画像として取得します。例えばインターネットの画面が一番下に表示されていて手前に少し小さい画面でExcelの画面が表示されていたとします。その際手前のExcelの画面だけ選択して画像を取得する事が出来ます。

 

この時に一番下に居るインターネットの画面を選択すると手前のExcelの画面も一緒に画像として取得されてしまいますので気を付けましょう。こんな感じです。(一部ぼかし入れてます)

f:id:EnjoyExcel:20220224131730p:plain

 

続いて左から4番目のアイコン。「全画面表示の領域切り取り」です。

f:id:EnjoyExcel:20220224130417p:plain

 

これは問答無用でとにかく画面に映っているもの全部を画像として取得します。デュアルディスプレイの際は2画面分の画像を取得しますのでご注意下さい。

 

最後です。先程もお伝えしましたが右端のアイコンは「閉じる」ボタンです。だいたいイメージつきますよね。

 

画像を切り取ると薄暗い画面から抜けて元の画面に復帰します。切り取った画像は貼り付けたいところでマウスを右クリックして「貼り付け」を選択する事で簡単に貼り付ける事が出来ます。

 

まとめ

後は使っていただきどのアイコンが自分の仕事に合っているのか確認していただけると良いかなと思います。

 

お勧めはショートカットで起動させるという事です。グループで画面を見ている時にサクッと画像を切り取ると「おおっ!何それ?」ってなりますよ。

 

是非試してみてください。

繰り返し処理-3 For Each~Next と ウォッチウインドウの使い方

f:id:EnjoyExcel:20211104165929p:plain

では最後のコード「For Each~Next」です。

 

今回はコードを実行するにあたってどんな動きをしているか見ていただきたいのでコードの紹介に合わせてウォッチウインドウの使い方も紹介させていただきます。

 

前回は「Do~」の皆様について紹介させていただきました。

enjoyexcel.hatenablog.com

 

今回は1個だけですよ。安心してください。4つになりませんので。

 

余談ですが中国語って1文字で発音4つあるんですよ。大学で勉強したんですけどほとんど忘れました。「ただただ難しかった」という印象しかないです。

 

・・・話がそれましたね。本題に戻しましょう。

 

For Each~Nextはオブジェクト毎に先に範囲を決めてから範囲内をループさせるという使い方をします。

 

オブジェクトが分かりにくい方もいますので具体的に申し上げますと以下のような感じになります。

 

・フォルダの中にある複数のエクセルブック

・1つのエクセルブックの中にある複数のシート

・1つのシート内にある複数のセル

 

イメージでとらえていただきたいのですがコードを書く上で操作対象となるものをオブジェクトと呼びます。

 

実際はただのデータですので捜査対象のように物体、要素としてとらえるのは難しいのですがたくさんコードを書いているとイメージ出来るようになってくると思います。

 

上記のくくりの様にある時点で「中身は違うけど同じ要素」を最初に指定して(オブジェクト毎)その中をループして処理をしましょうというのが「For Each~Next」になります。

 

一見便利そうなんですけど変数の定義の知識、オブジェクトの知識がないと難しい処理となります。加えてループさせる順番の規則性はあるのですが指定は出来ないのでとにかく範囲内の物体に対して処理をしたい時には有効ですがそうじゃない時は処理も増えてしまうので気を付けて使ってください。

 

そんなわけで私はFor~Nextが一番使いやすいのですがFor Each~Nextで書いてあるコードを読むことがあるかもしれませんのである程度理解はしておく必要があります。

 

では説明していきます。

 

先にコードの内容をお伝えします。やろうとしている事は1つのエクセルブック内のシートをすべてループして各シートの「セルA1」にそれぞれのシート名を書くというコードを用意しました。

 

「sheet1」の「セルA1」は「sheet1」と記載されます。

f:id:EnjoyExcel:20211105133303p:plain

f:id:EnjoyExcel:20211105133416p:plain

 

sheet2~4も同様の処理がされます。

 

最後に「sheet5」の「セルA1」は「sheet5」と記載されます。

f:id:EnjoyExcel:20211105133504p:plain

f:id:EnjoyExcel:20211105133446p:plain

 

シートがあるだけループされて処理が進みます。シート名を変えておけばそのシート名が各シートの「セルA1」に展開されます。

 

以下コードを紹介します。

f:id:EnjoyExcel:20211105131923p:plain

 

Each の後に変数を設けて In の後に用意したコレクション内のオブジェクトをループしながら順番に変数にセットするという処理をしています。

 

というわけで今回は変数「w」にはワークシートが順番にセットされます。よって変数「w」の定義はWorksheet(単数)で行います。

 

In の後のWorksheets はコレクションですので複数形です。ロジカルに考えるよりはイメージを持っていただきたいのですが沢山あるシートを1つずつ変数「w」にセットしていくよと思って下さい。だから母数となるものは複数形で1つずつ変数に収納するので収納先となる変数は単数形だよぐらいで考えてください。

 

続いて w.Range("A1") = w.Name は読み替えると Sheet1.Range("A1") = Sheet1.Name となります。シートは画面で表示されている左から変数にセットされるのでここではSheet1からSheet5に向かって順にセットされることになります。

 

上記のとおりシートは5枚ありますので変数「w」はループするたびに次のシートを掴みにいきます。よって以下の様にコードが変化していく事になります。

 

Sheet2.Range("A1") = Sheet2.Name

Sheet3.Range("A1") = Sheet3.Name

Sheet4.Range("A1") = Sheet4.Name

Sheet5.Range("A1") = Sheet5.Name

 

最後にNextまでいったら先頭に戻ります。ループするオブジェクトが無くなったらループを終えて次のコードに進みます。建付けと動きについてはざっとこんな感じとなります。

 

・・・ちょっと難しくないですか。私は最初「意味が分からない」って思いました。

 

当時何を思っていたのかを思い返してみると勉強初期はオブジェクトの事も分かってないからそもそもコードが書けないしVBEの使い方も慣れてなかったのでデバッグしても繰り返し処理の中で何が起こっているのか分からなかった事を思い出しました。

 

一言で言うと「ブラックボックス」でしたね。だから使いたくなかったです。

 

今はオブジェクトについても理解が進んだこととVBEの使い方も分かってきたので使いにくさは無くなりました。理解を深める為にVBEを使って実際にどうやってシートを掴んでいるか見てみましょう。

 

「F8」でコードを送っていくとローカルウインドウで変数「w」が出てきます。どんな動きをしているのか確認しようと思い変数「w」の横にある+をクリックするとOutlookが起動してしまいます。シートオブジェクトを見に行こうとすると決まって起きるのですがどうやらバグの様です。

f:id:EnjoyExcel:20211105141014p:plain

 

これが出てきます。全部×ボタンで消してください。

f:id:EnjoyExcel:20211105141245p:plain

この処理以外はローカルウインドウは正常に動きます。とても便利ですので「いつもお世話になっております」という感じなのですがこの件に限っては使いにくいです。

 

毎回Outlookを消せばローカルウインドウでもコードを追いかける事が出来るのですが都合が悪いので今回はウォッチウインドウを使いましょう。VBEにウォッチウインドウが居ない時はVBEの表示タブからセット出来ますので試してみてください。

f:id:EnjoyExcel:20211105141931p:plain

ウォッチウインドウの枠内で右クリック。「ウォッチ式の追加」を選択。

f:id:EnjoyExcel:20211105141823p:plain

式の枠内に今回は「w.name」と書いてください。対象とウォッチの種類は基本的にはそのままでOKです。

f:id:EnjoyExcel:20211105141901p:plain

ウォッチウインドウにn.Nameが準備されます。nameのNが大文字に変化してますよね。

f:id:EnjoyExcel:20211105142313p:plain

あとは実際のコードを「F8」キーで1つずつ進めてコードをループさせてみましょう。ウォッチウインドウの値の欄にシート名が表示されます。最終的にはSheet5まで変化します。これでどのタイミングでどのシートを掴んでいるのか分かりますよね。

f:id:EnjoyExcel:20211105142423p:plain

今回は「For Each~Next」とウォッチウインドウの紹介をしました。

 

繰り返し処理についてはまだ種類があるのですがこれぐらいで十分だと思います。まずは使ってみてください。コードを書くだけではなく実際に動かして何が起こっているのか把握する事が重要です。

 

そうしないと不具合が起きた時にデバッグ処理に苦労します。処理を追いかける事が出来なくなり自分で書いたコードが直せないという事が起こるかもしれません。

 

便利ですが内容把握してないと後でツケが回ってきやすいコードです。でも使えると世界が広がりますので是非頑張って勉強してみてください。