Vlookupより便利な関数 Index関数とMatch関数の組み合わせ

VlookUp関数はみなさんよく使われる関数かと思います。

セルの表示形式をそろえて参照や列が間違えてなければきちんと表示されますね。

ただし、VlookUp関数の弱点は参照するコードやリストが右側にあると機能してくれません。

下記のリストです。

1行目はVlookUp関数を使用してますがエラーですね。こういうリストを使用する際には

2行目のIndex関数+Match関数を使用しています。

二つの関数が入れ子にされて覚えづらいかもしれませんが覚えれば非常に便利です。

 

ざっくり簡単に説明すると下記のようになります。

Index関数=(参照範囲,行番号,列番号)の値を返す。

Match関数=(調べる値,調べるもしくはの範囲,一致条件)が何番目にあるかを(数値で)返す

※Match関数は1つの行もしくは1つの列の範囲しか選択できません。

 

Index関数+Match関数Index(参照範囲,Match(調べる値,調べる行範囲,一致条件(上記のリストでは完全一致)),列番号)

Match関数で行番号を調べています。

画像のリストでは調べる列が1列しかないので列番号は1となります。

 

調べる列が2列以上ある場合、=(Index関数+Match関数+Match関数)という式になります。

 

関数やVBAは見ているより手を動かしたほうが上達も早いです。

まずは手を動かして実践をして覚えていきましょう♪

 

ではでは♪

セルの先頭文字に0を入力する セルの表示形式を設定する VBA マクロ

初心者の方だと セルに「010」と入力しても「10」としか表示されず、あれ?なぜだ?となる方もいるかと思います。

 

マクロで処理するときにもはまってしまう方もいるかと思います。

まさに自分がそうでしたが笑

 

対策として2点かあります。

・セルの表示形式を 文字列 にする

 

・セルの先頭に ’(シングルクォーテーション) を入力する

 

私のオススメというか私はセルの表示形式を列ごと文字列にして先頭に0を表示させています。

リストや計算の過程でもしかしたら、 シングルクオーテーションにする必要がある可能性も少なからずあるとは思いますが。

私の(未熟ではありますが)経験上ではほとんどのリストで文字列形式でも問題ないです。

 

ちなみに表示形式を標準に戻す場合は下記の通りです。

その他の表示形式をしたい場合はセル上で 

Controlキー +1(セルの書式設定を表示)→表示形式タブ→ユーザー定義のリストを選択→種類タブ上を全選択→コピペ

すれば設定できます(‘ω’)ノ

ではでは♪

ファイルを開く 指定したファイルを開く ダイアログファイルを出してファイルを開く VBA マクロ

ファイルを指定してマクロ処理をしたい!ってよくあることかと思います。

私自身、ファイルを開いて処理をするってことが多々あります。

 

実はマクロでファイルを開くって結構簡単だったりします。

Application.GetOpenFilename メソッドを使用します♪

Application.Getfilenameメソッドは選択したファイルの文字列しか返しません。なので選択すれば開くというわけではありません。

引数の指定は下記です。引数を指定しなくてもいいとは思いますが。

FileFilter→開くファイル形式を指定します。

FilterIndex→ FileFilterで指定したファイルのインデックス番号を指定し規定値を指定します。

Title →ダイアログボックスの名前を指定します。

ButtonText →通常使わないです。

MultiSelect→Trueで複数ファイルを開けます。Falseで一つのファイルのみ開けます。

 

サンプルは下記です。エクセルファイルとCSVファイルのみ開けるように引数指定しています。

ではでは♪

オートフィルターの設定・解除をする マクロ VBA オートフィルターがあるか(設定されているかされていないか)調べるマクロ VBA

オートフィルターの設定のショートカットキーに引き続き、

マクロでオートフィルターを設定・解除する場合についてご紹介します♪

 

まずは設定する方法について。

ややこしいですがオートフィルターには、

Autofilterオブジェクト と Autofilterメソッド があります。

違いとざっくりな使い方は下記の通り

 

Autofilterオブジェクト →  Worksheetオブジェクトに属している

オートフィルターが設定されているかされていないかの判定ができます。

 

Autofilterメソッド→ Rangeオブジェクトに属している

オートフィルターの設定と解除ができる

上のようなリストのラベルにオートフィルターを設定する場合下記のマクロで設定できます。

ただし、Autofilterメソッドでは設定と解除しかできません。

なのでオートフィルターを設定したままにしていたい・解除したままにしていたい時、

マクロ全体の処理がうまくいかない可能性があります。

 

そんな時にAutofilterオブジェクトを使用します。

Autofilterオブジェクトでオートフィルターがあるかないかを判断できます。

 

オートフィルターを設定したままにしたい時は下記サンプルをご確認ください。


※オートフィルターを設定させたくない場合は

If Not Labe Is Nothing Then

とすればオートフィルターを解除させたままにできます。

 

サンプルファイルも添付しておきます♪

オートフィルターサンプル

ではでは(‘ω’)ノ

オートフィルターを設定する ショートカットキー

エクセル操作の手作業では手放せないオートフィルター機能ですよね。

 

今回ご紹介するのは

手作業でも簡単にオートフィルターの設定と解除をする方法・マクロでオートフィルターを設定・解除する方法をご紹介します♪

手作業でオートフィルターを設定する場合、操作が不慣れな方は

 

範囲選択→リボンを開く→ホームタブ→並び替えとフィルターボタン→フィルターを選択(右クリックメニューも同様)。。。

 

フィルターを設定するだけでも面倒な工程を経ています。そして解除をする際も、

 

リボンを開く→ホームタブ→並び替えとフィルターボタン→フィルターを選択(右クリックメニューも同様)。。。

 

わざわざ面倒!って私はなります。

 

そんなときには  ctrl+shift+Lキー でちょちょいのちょいです。

 

まず フィルターかける場所を範囲選択→ ctrl+shift+Lキー で一瞬で設定できます。

 

また解除なんてもっと簡単。そのまま(範囲選択しない) ctrl+shift+Lキー で一瞬で解除できます。

 

次はマクロでオートフィルターを設定する方法をご紹介します。

ではでは

抵抗勢力との向き合い方

業務の改善を進めていると出てくるのが抵抗勢力

社歴が長い人や役職のある人ほど新しいモノを怖がる傾向にあるようにかんじられます。

マクロを導入しても抵抗勢力に

「あなたしかわからないじゃない」、「マクロなんて使わなくてもシート関数でいいじゃん」etc…

 

せっかくマクロを導入して劇的に改善されているのに受け入れられない方もおられます。

 

ようじえさん何で勝手にマクロ作ってるの?なんて言われたときは私もカッとなってしまったことがあります。

 

人を変えることはとても難しいことです。なので自分だけどんどん変わっていきましょう♪

 

改善を導入したものは全て手順書を書き、マクロの内容もきちんと書いておくのがよいかと。

 

それすらも抵抗してくる場合は、そんな環境から卒業することを考えましょう(^^)

 

ではでは♪

セルの色を調べる RGBを調べる マクロ VBA

セルの色の設定は Colorプロパティ と ColorIndexプロパティ があります。

ColorIndexで1~56の色を指定して色を調べる場合、

簡単な方法としてイミディエイトウィンドウを使用して、ColorIndexプロパティを使用しましょう♪

?Activecell.Interior.colorindex で数値を取得しましょう♪

セルの背景色がなければ -4142 となるかと思います。

黄色なら6

その他の色はどんなものかは実際にセルに表示してみてもいいかもしれませんね♪

Colorプロパティを使用する場合

セルの書式設定→塗りつぶしタブ→その他の色→ユーザー設定→色の設定→ユーザー設定を開くと

下記のような項目が表示されます。

ここからRGB番号を調べて

Activecell.Interior.Color=RGB(102,255,51)

※サンプルです。

 

とすれば背景色を設定できます♪

 

ではでは♪

SUBSTITUTE関数とREPLACE関数の違いは? エクセル関数 シート関数

どちらも置換という関数で似ていますね。

ワークシート関数でどのような違いがあるかご紹介します。ちなみにマクロを使っていてもReplace関数は結構使います。

私自身、この二つの関数を知ってはいたものの、違いがわかるまで何度も何度も検証をしましたw

みなさんに共有するためにまずどう違うのかと一言で表すと、

SUBSTITUTE → 替え玉

→指定した文字を置き換える文字にする

REPLACE → 代替え

文字列の中の何番目から何番目までの文字を置き換える文字にする

 

例えを挙げます

A1セルに ランニングはジョギングではない という文字がある場合

Replaceを使う場合

=REPLACE(A1,7,5,”ウオーキングではない”)

5行目から置換していることに注目

 

Substituteを使う場合

=SUBSTITUTE(A1,”ジョギング”,”ウオーキング”)

置換対象文字列を置換対象文字列にするということに注目

 

Replaceは何文字列以降を指定の文字列に置換

Substituteは対象の文字列を指定の文字列に置換

ってな感じになります。

まずはいじってみて確認してみるのが理解の第一歩かと思います。

 

サンプルを添付します。興味があればご確認ください♪

repace,substitute違い

アクティブシートにグラフを挿入する グラフの挿入 VBA マクロ

グラフの挿入はちょっとやっかいです。

メソッドやプロパティがやたら多いので覚えきれないと思います。

ですので、

オブジェクトやメソッド等を最低限必要なものだけ自分なりに簡潔にまとめてみましたので下記ご参照ください♪

ChartMmap

 

もし覚えたい場合は

Chartオブジェクト

ChartオブジェクトのChartWizardメソッド

 

ChartObjectオブジェクト

ChartObjectオブジェクトのAddメソッド

を覚えればグラフを作る土台はできるのではないでしょうか。

 

グラフを作成するにあたってサンプルコードを作成しました。

コードの冒頭部でグラフがあれば削除するという初期化を設定しています。

If ActiveSheet.ChartObjects.Count <> 0 Then
ActiveSheet.ChartObjects.Delete ‘初期化
End If

シートにグラフがあるか・ないかを判断する場合にも応用できますね♪

サンプルファイルを添付します。

グラフサンプル

 

ご参考にしていただければ♪

セルの座標を取得する VBA マクロ 図形の位置 チャートの位置 ボタン 修正

図形やグラフを挿入する際には座標の指定が必要だったりします。

 

そんな時に座標を調べたいときに使うのが、

 

Top ・ Left プロパティ

です。

Topプロパティはエクセル画面上部から指定位置までの距離

Leftプロパティはエクセル画面左端から指定位置までの距離

 

構文は簡単♪

オブジェクト.Top

オブジェクト.Left

下記サンプルではイミディエイトウィンドウに距離を表示します。


結果はこうなります。

range(“G2”)のTopは18.75

range(“G2”)のLeftは331.5

とのことです♪