2018年 8月 の投稿一覧

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…

 

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

 

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

 

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

 

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

 

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

 

ではでは♪