エクセルセミナー研修のすごい改善~Excel社員研修と業務効率化

Excelセミナー毎週開催中。なぜか全国の企業から会社経営や業務システムの相談まで殺到する銀座の小さなExcel教室。

【Excel】セル参照をINDIRECT関数で行う事例


こんにちは。

すごい改善の吉田です。


少し前にツイッターで拝見したご質問。


「例えばA1セルにCって書いてあって、A2セルに100って書いてあるとき、A3セルにC100セルの値を参照させる方法ってないのかな」


ということでした。


つまり、A1セルで参照したいセルの列、A2セルで参照したいセルの行を指定して、この二つの文字列から参照と作りたいということですね。


具体的には上記の例なら、C100セルに「A」と書かれているなら、A3セルにそのセルを参照して「A」という値を入れたいということです。


ここまでで、セルの「参照」という概念がわからない場合はぜひ弊社のセミナー、『Excelを仕事で使いこなす100の極意』セミナーにご参加ください。


では、このようなケースではどうすればいいかというと、こんな時にINDIRECT関数が使えます。


この関数は非常に使用法、活用シーンがイメージしにくい関数です。現に私も滅多に使いません。でも知っておくとこんなケースに役に立つわけですね。


上記ケースでは、A3セルに次の式を入れればOKです。


=INDIRECT(A1&A2)


A1&A2という引数は、A1セルの値とA2セルの値が結合演算子の&(アンパサンド)でつながれていますから、「C100」という文字列を取得しています。


この文字列を引数にして、セル参照をできるのがINDIRECT関数です。間接的に参照する、ということです。


一番活躍するのはおそらくリスト入力を二段階で設定するテクニックの場面だと思いますが、まずは上記のケースで基本的な機能のイメージを持って頂ければと思います。


たった1日で即戦力になるExcelの教科書/技術評論社
¥1,922
Amazon.co.jp

いいね!した人  |  リブログ(0)

【Excel】重複のあるデータでVLOOKUPを行う際の方法


こんにちは。
すごい改善の吉田です。


こちらの記事で、VLOOKUP関数のご紹介をしました。


【関数名】
VLOOKUP関数


【書式】
=VLOOKUP(検索値,検索範囲,列数,0)


【機能】

検索範囲の一番左の列から検索値と同値のセルを探し、そのセルから第三引数で指定した列数目にあるセルの値を返す。


…という関数でした。


この関数の使用において、データの検索値が重複している場合、注意することが一点あります。


1.こちらのデータをコピーして、エクセルシートのA1セルに貼って下さい。


No,KEY,取引先名,担当者名,,取引先名,担当者名,No,KEY
,,アラバスタ王国,ネフェルタリ・コブラ,,アラバスタ王国
,,アラバスタ王国,ペル,,アラバスタ王国
,,アラバスタ王国,コーザ,,アラバスタ王国
,,ウォーターセブン,アイスバーグ,,ウォーターセブン
,,ウォーターセブン,パウリ―,,ウォーターセブン
,,ウォーターセブン,ロブ・ルッチ,,ウォーターセブン
,,ウォーターセブン,フランキー,,ウォーターセブン
,,バラティエ,ゼフ,,王下七武海
,,バラティエ,パティ,,王下七武海
,,バラティエ,カルネ,,王下七武海
,,王下七武海,ジュラキュール・ミホーク,,王下七武海
,,王下七武海,ドンキホーテ・ドフラミンゴ,,王下七武海
,,王下七武海,バーソロミュー・くま,,王下七武海
,,王下七武海,ボア・ハンコック,,王下七武海
,,王下七武海,サー・クロコダイル,,王下七武海
,,王下七武海,ジンベエ,,バラティエ
,,王下七武海,マーシャル・D・ティーチ,,バラティエ
,,王下七武海,ゲッコー・モリア,,バラティエ


2.そのまま、「データ」タブから「データ区切り」をクリックします。


3.「元のデータの形式」が「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」が選択されていることを確認し、「次へ」をクリック


4.「区切り文字」で「カンマ」にチェックを入れ、「完了」をクリック


これで、上記のデータがエクセルで使えるようになったと思います。


データをエクセルに取り込めたら、次のような状態になっていることを確認してください。


A1→No
B1→KEY

C列→取引先名
D列→担当者名
F列→取引先名
G1→担当者名
H1→No
I1→KEY


取引先名のデータが重複しているパターンで、かなり頻繁にご相談頂くパターンです。


このデータにおいて、セル範囲[C:D]のデータを元に、G列に取引先ごとの担当者名を入れたいという場合。


普通にVLOOKUP関数を使うだけではうまく行きません。


実際に入力してみます。


1.G1セルに次の式を入力


=VLOOKUP(F2,C:D,2,0)


2.これを19行目までコピー


すると、G列には、取引先ごとに全て同じ担当者名が入力されてしまいます。


アラバスタ王国の場合であれば、ネフェルタリ・コブラ、ペル、コーザの3人の担当者名を入力したいわけですが、すべて「ネフェルタリ・コブラになってしまっています。


このように、VLOOKUPの検索値に重複がある場合、この関数は最初に一致した検索値のセルを対象として処理します。


結果、G3セルもG4セルもG5セルも、同じ「アラバスタ王国」を検索値としていますので、検索範囲であるC列で最初に登場したC2セルを対象にVLOOKUPの処理を行い。「ネフェルタリ・コブラ」という値を返しているということになります。


この問題を解決するには、重複データがあるC列、F列のデータをそれぞれユニークの状態に加工する、つまり重複がない状態に加工する方法が考えられます。ここでは、作業列を追加して行います。


考え方としては、重複する取引先名データのそれぞれに固有の番号を振ることによって固有化していきます。


1.A2セルに次の式を入力し、19行目までコピー


=COUNTIF($C$2:C2,C2)


2.H2セルに次の式を入力し、19行目までコピー


=COUNTIF($F$2:F2,F2)


3.B2セルに次の式を入力し、19行目までコピー


=C2&A2


4.I2セルに次の式を入力し、19行目までコピー


=F2&H2


A列とH列のCOUNTIF関数で、重複する取引先名それぞれに固有の番号を振ってあげました。

次に、B列とI列でそれぞれの取引先名と番号を結合し、それぞれが固有のデータとなるように加工しました。


この上で、再度G列のVLOOKUP関数を入力し直します。


今度は検索値をI列で指定し、範囲を[B:C]に設定します。


G2セルに次の式を入力し、19行目までコピーします。


=VLOOKUP(I2,B:D,3,0)


今後は、取引先名ごとにそれぞれ個別の担当者名が入力されました。


検索値が重複しているデータがVLOOKUP関数を使用するには、COUNTIF関数で各データの番号(登場回数)を設定し、その番号と検索値を結合することによってできた新たな固有の検索値をKEYにしてVLOOKUP関数を使用することで正確な結果を得ることができるようになる、というこれはエクセルの機能というよりは発想のお話ですね。


いいね!した人  |  リブログ(0)

【Excel】VLOOKUP関数を理解しておきましょう


こんにちは。
すごい改善の吉田です。


マクロ、VBAで自在に処理を自動化するにはまずワークシート上で、手作業でその処理をきちんとできることが重要です。そのためには、ワークシート関数の知識も着実に増やしておく必要があります。


今日は数あるエクセル関数の中でも超必須関数の代表格、VLOOKUP関数をご紹介します。


【関数名】
VLOOKUP関数


【書式】
=VLOOKUP(検索値,検索範囲,列数,0)


【機能】


検索範囲の一番左の列から検索値と同値のセルを探し、
そのセルから第三引数で指定した列数目にあるセルの値を返す。


顧客管理エクセルや請求書作成エクセルなどで活躍します。


請求書番号を変えるだけで、請求書の中身…社名や商品名、金額など…を瞬時に変え、いくつもの請求書を簡単に作ることができたりします。


では次のデータを使って実際に書いてみましょう。


1.こちらの6行をコピーして、エクセルシートのA1セルに貼って下さい。


請求書No,顧客名,請求金額,,請求書No,顧客名,請求金額  
1,株式会社ブラゼル,100000
2,野村研修株式会社,120000
3,金田ソフト株式会社,150000
4,佐藤ブライダル株式会社,123000
5,株式会社すごい改善,189000


2.そのまま、「データ」タブから「データ区切り」をクリックします。


3.「元のデータの形式」が「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」が選択されていることを確認し、「次へ」をクリック


4.「区切り文字」で「カンマ」にチェックを入れ、「完了」をクリック


これで、上記のデータがエクセルで使えるようになったと思います。

データをエクセルに取り込めたら、次のような状態になっています。


A列→請求書No
B列→顧客名
C列→請求金額


E1セル→「請求書No」
F1セル→「顧客名」
G1セル→「請求金額」


ここでは、E2セルに請求書Noを入力すると、自動的にF2セル、G2セルに、それぞれ請求書Noに対応した顧客名、請求金額が表示されるように設定したいと思います。


まず、F2セルに次の数式を入力します。


=VLOOKUP($E2,$A:$C,2,0)


これをドラッグコピーでG2セルにコピーします。この時点では、「#N/A」というエラー値が出ていますね。


では、E2セルに1を入力してみて下さい。

F2セルに「株式会社ブラゼル」、G2セルにも「株式会社ブラゼル」と表示されたと思います。


G2セルには請求金額を出したいのでこのままではいけないですね。

G2セルの数式を次のように修正します。


=VLOOKUP($E2,$A:$C,3,0)


第三引数が「2」だったのを「3」に直しました。すると、G2セルには「100000」が表示されたと思います。


あとは、E2セルに1から5までの数字を順番に入れてみて下さい。E2セルの請求書番号に、左側の表内で対応している顧客名、請求金額にF2セル、G2セルの値が次々と変わります。


これが、VLOOKUP関数です。もう一度書式と機能を確認してみましょう。


【書式】
=VLOOKUP(検索値,検索範囲,列数,0)


【機能】
検索範囲の一番左の列から検索値と同値のセルを探し、そのセルから第三引数で指定した列数目にあるセルの値を返す。


今回の例では、次のような処理を行っています。


=VLOOKUP($E2,$A:$C,2,0)


→セル範囲[A:C]の一番左の列(つまりA列)でE2セルと同値のセルを探し、見つかったらそのセルから2列目にあるセルの値を返す。


E2セルに「1」を入れた場合、この値はA列の2行目にありますね。A2セルから数えて2列目のセルにある「株式会社ブラゼル」がこの数式を入力したセルに返されるわけです。


=VLOOKUP($E2,$A:$C,3,0)


→セル範囲[A:C]の一番左の列(つまりA列)でE2セルと同値のセルを探し、見つかったらそのセルから3列目にあるセルの値を返す。


E2セルに「1」を入れた場合、この値はA列の2行目にありますね。A2セルから数えて3列目のセルにある「100000」がこの数式を入力したセルに返されるわけです。


あらかじめ台帳やマスタを作っておくことが必要ですが、これを使えば、例えば見積書を作る際など楽になります。商品Noだけ入れれば隣のセルに商品名、単価が自動的に入力できるようになるわけですね。


そして、このVLOOKUP関数の第三引数である「列数指定」ですが、今回は直接入力で2を3に直すなど行いましたが、これも右方向にこの関数を入力する列が増えると修正や入力も大変になります。


そこで、この第三引数に「関数」または「セル参照」を使用すれば、一つのセルに一回数式を入力してしまえば、残りのセルにはそのセルを全てコピーして使えるという工夫ができます。


この方法は別途ご紹介しますが、VLOOKUP関数の第三引数に数値の直接入力以外の方法として用いる関数としてはCOLUMN関数、MATCH関数がその代表格です。


たとえば今回の場合、F2セルに次のように入力すれば、それをそのままG2セルにドラッグコピーするだけで、
列数指定を修正することなく使えてしまいます。


=VLOOKUP($E2,$A:$C,COLUMN()-4,0)


または、


=VLOOKUP($E2,$A:$C,MATCH(F$1,$A$1:$C$1,0),0)


VLOOKUP関数を覚えただけではすごい改善はできません。しかし、VLOOKUP関数の第三引数の列数指定においてセル参照もしくは関数によって数値をする、という発想を身に着けたとき、劇的なすごい改善が起こります。


たかがエクセル、単なる表計算アプリケーションなのですが、ちょっとした違いが、感動的な業務改善を実現してしまうのですね。

いいね!した人  |  リブログ(0)