ICT業務改善

入力作業はしたくない|セルの値を連動させる

 

前回の記事ではプルダウンリストについてお話ししました。

納品書の商品名が、リストから選択出来るようになりましたね。

 

さて、商品名を選んだら次は単価を入力するのですが、これもまた億劫な作業です。商品名が決まれば単価も自ずと決定するわけですから、商品選択と同時に単価も入力しておいて欲しいですよね。

ということで今回は、他のセルの値を連動させる方法について、ご紹介します。

 

では早速、前回に引続き納品書を例に進めていきます。

まず最初に、納品書とは別のシートで管理している商品名リストの隣に、単価リストを作成します。

このリストに基づき、納品書の商品名セルで商品1を選択すると、単価セルに1,000が入力されるようにしていきます。

 

次に、納品書の単価セル内に

=VLOOKUP(B17,リスト!A2:B11,2,FALSE)

を入力します。

これで、商品名選択と同時に単価が自動入力されるようになりました。

 

もう終わってしまいました。

ちょっと意味が分からないと思うので、解説します。

先程、単価セルに入力した式

=VLOOKUP(B17,リスト!A2:B11,2,FALSE)

の構造は

=VLOOKUP(①検索値, ②範囲, ③列番号, ④検索方法)

となっています。

VLOOKUPとは、「ある値を指定した範囲から探し、それに紐づくデータを取得してくれる」Excel関数です。

それぞれの項目が示す内容は以下の通りです。

①検索値:指定範囲から検索したい値。この例では、商品名セルの値。

②範囲:検索値の検索範囲と、それに紐づくデータの格納範囲。検索値は指定した範囲の左端の列から検索されます。この例では、商品名リストから単価リスト迄の範囲を指定しており、検索値は商品名列から検索されます。範囲の書き方は、「シート名!範囲の左上端セル名:範囲の右下端セル名」です。

③列番号:指定した範囲の中で、左から何列目のデータを取得したいかを指定します。この例では単価を取得したいので、「2」を指定しています。

④検索方法:FALSEを指定すると、検索値と完全一致する値だけを検索します。TRUEを指定すると、検索値が指定範囲内に存在しない場合に近似値を検索します。

 

つまり、

=VLOOKUP(B17,リスト!A2:B11,2,FALSE)

とは、

・B17(商品名セル)の値を

・「リスト」シートのA2~B11迄の範囲の1列目(商品名列)から検索し

・ヒットした行の2列目の値(単価)を取得します

・検索は完全一致で行いますよ

という意味なのです。

指定する内容が多いので、少しとっつきにくいかも知れませんが、意味が分かってしまえば大したことはありません。これを使えば、「お客様名を入力したら、住所、電話番号も自動入力する」なんて事も出来るようになりますね。

使いどころの多いVLOOKUP関数、是非覚えておいて下さいね。

 

 

さて、折角ですから納品書を最後まで完成させましょう。明細の他の行にも同じ関数をコピーして、「①検索値」が行毎の商品名セルに変わっているか確認します。

(コピーの際、「②範囲」の値がズレないように、絶対参照に変えておきます。)

全ての明細行に関数をコピーしたら、これで完成……

 

おや、何やらエラーが出ています。

これは「該当データ無し」を意味するエラーです。

明細左を見てください。商品名が空欄ですね。VLOOKUPの検索値が空白なので、「指定範囲を検索したけどデータが無いよ」と言っているのです。

これは困りました。こんな表示が出ていては、印刷してお客様に渡すような使い方は出来ません。

このようなエラーを出さないようにするには、単価セルの式を

=IF(B17=””,””,VLOOKUP(B17,リスト!$A$2:$B$11,2,FALSE))

に変更します。

式を変更した行は、商品名が空白でも単価にエラーが出なくなりました。

 

これも少し説明しましょう。

=IF(B17=””,“”,VLOOKUP(B17,リスト!$A$2:$B$11,2,FALSE))

この式の構造は

=IF(論理式, ②真の場合, ③偽の場合)

となっています。

IFは、「ある条件が真の場合と、偽の場合で、表示する値を変える」Excel関数です。

それぞれの項目の意味は、

①論理式:表示値を分ける条件。

②真の場合:①の条件が真の場合に表示する値。

③偽の場合:①の条件が偽の場合に表示する値。

 

つまり、

=IF(B17=””,””,VLOOKUP(B17,リスト!$A$2:$B$11,2,FALSE))

が意味するのは、

・B17(商品名セル)= ””(空白)の場合

・””(空白)を表示し

・そうでない場合はVLOOKUP(B17,リスト!$A$2:$B$11,2,FALSE)(検索した単価)を表示する

という事です。

この式を全ての明細行にコピーすると……

エラーの出ない納品書が完成しました。これで安心して印刷出来ますね。

 

本題よりエラー対処の方が長くなってしまいました。まぁ実際の作業も得てしてそんなモノです。頑張って使ったExcel関数でエラーが出てしまうと、挫けそうになりますが、解決策は大体用意されています。諦めずに色んな関数を試してみて下さいね。

 

次回は「プルダウンリスト同士を連動させる方法」について、紹介します。

あるセルで商品カテゴリを選んだら、別のセルでは選択カテゴリの商品のみ選択出来るようにしたい。そんな内容です。

 

※画像は全て、Excel2007のもので統一しています。

 

 

このようなExcelの機能を駆使すれば、様々な入力作業が大幅に削減できます。Excelの機能自体は何も目新しいものではありませんが、自社が今まで使っていなかった機能を利用して業務が変わるのならば、それは一つのイノベーションと言っても良いのではないでしょうか?

色々と工夫できることは多そうだけど、自分たちでやるには知識も時間も足りないとお困りの方は、アーティに是非一度ご相談ください。業務内容をお聞かせ頂ければ、Excel機能を始めとするITの知見で最適な改善方法をご提案します。最初のご相談は無料ですので、是非お気軽にお問合せ下さいね。