Excel。住所から横浜市のデータだけを抽出して別表をつくりたい【address】

Excel。住所から横浜市のデータだけを抽出して別表をつくりたい

＜FILTER+IFERROR+FIND関数＞

住所から横浜市が含まれているデータを行全体で抽出したい。

抽出したデータの別表をつくりたいということなんですね。

住所には、都道府県から入力されているので、横浜市を含むという「*横浜市*」のようなワイルドカードをつかう方法があります。

また、オートフィルターで、横浜市を含むという条件で抽出する方法もあります。

今回は、FILTER関数をつかって、処理してみましょう。

FILTER関数は、抽出して別表をつくることができる関数です。

=FILTER(A2:D11,IFERROR(FIND(D13,D2:D11)>0,0),"")

と、A15に設定するだけで、横浜市を含むデータを抽出することができます。

FILTER関数は、スピル機能対応の関数なので、オートフィルで数式をコピーする必要はありません。

今回は、D13に条件を入力することで、その条件に合致するデータを抽出するようにしましたが、D13に用意しない場合には、

=FILTER(A2:D11,IFERROR(FIND("横浜市",D2:D11)>0,0),"")

というように数式を設定してもOKです。

では、数式を確認してみましょう。

FILTER関数よりも、先に、FILTER関数内の引数にある数式を確認しましょう。

FIND関数をつかっています。

これは、セル内に、横浜市という文字列があるかないかを処理しています。

左から何文字目に登場するかという数値を返してくれます。

神奈川県横浜市　ですから、5文字目に横浜市がありますので、5を返してくれるというわけです。

ただ、FIND関数の欠点は、該当のデータがなかった場合、#VALUE!というエラーが発生してしまうことです。

エラーがあると、最終的にFILTER関数をつかってデータを抽出したくても、#VALUE!というエラーが表示されてしまうので、FIND関数の時点でエラーを処理する必要があります。

そのため、IFERROR関数をつかって、エラーを表示しないようにします。

その場合、空白とせず、0にします。

よって、FILTER内の引数は、「IFERROR(FIND("横浜市",D2:D11),0)」となるわけです。

では、FILTER関数を確認します。

最初の引数は、「配列」。

範囲なので、A2:D11と設定します。

スピル機能がありますから、絶対参照にする必要はありません。

2つ目の引数は、「含む」。

条件です。

ここで、先ほど確認した、「IFERROR(FIND(D13,D2:D11)>0)」を設定します。

「>0」としたのは、0よりおおきければ、該当の文字が含まれていることを意味しています。

このためIFERROR関数で空白ではなく、0にしたわけです。

3つ目の引数は、「空の場合」。該当データがなかった場合は、「””」空白にします。

FILTER+IFERROR+FIND関数を組み合わせることで、関数だけで、該当する含むデータを抽出して、手早く別表にすることができます。

Excel。文字列の一部が該当したら行全体をぬりつぶしたい【Fill】

Excel。文字列の一部が該当したら行全体をぬりつぶしたい

＜条件付き書式+FIND関数＞

B列の住所。

その中で、横浜市ならば、行全体を塗りつぶしたいのですが、どのようにしたらいいのでしょうか。

横浜市ならば、塗りつぶすということは、条件付き書式をつかって対応します。

あと、問題になってくるのが、条件式です。

どのような条件式を作ればいいのでしょうか。

横浜市ならばということですから、横浜市をどのように確認させるかということになります。

ただ、住所の列は、横浜市　だけではなく、都道府県を含め、市区町村などもすべて含まれているデータです。

横浜市　という条件。

つまり完全一致のデータではなく、部分一致の条件式をどのように作るのかというのが、ポイントになるわけです。

さて、部分一致ということならば、「ワイルドカード」をつかいたくなりますが、今回は、文字を検索する「FIND関数」だけをつかって、対応していきます。

A2:B6を範囲選択します。

ホームタブの条件付き書式にある、「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」をクリックして、条件式を設定します。

=FIND("横浜市",$B2)

あとは、書式ボタンをクリックして、セルを塗りつぶす色を設定します。

OKボタンをクリックして、完成です。

このように、横浜市が含まれている行全体を塗りつぶすことができました。

では、条件式を確認しておきます。

=FIND("横浜市",$B2)

FIND関数は、セル内に、最初の引数で設定した文字が最初に出てくる文字数を返す。

すなわち、含まれているかを確認することができる関数です。

最初の引数は、「横浜市」と設定します。

2つ目の引数で、検索対象のセルを設定します。

また、列固定の複合参照にすることで、行全体を対象にすることができます。

行全体を塗りつぶすことができるというわけです。

Excel。全角空白を入力させるように、そして半角空白では入力できないようにしたい【Validation Rules】

Excel。全角空白を入力させるように、そして半角空白では入力できないようにしたい

＜入力規則＋ISNUMBER+FIND関数＞

入力されているセル内の空白。

その空白が、全角ではなく半角だとしたら、それを全角にするという方法はありますが、そもそも、入力時にチェックできればいいわけですね。

では、そのようなことができるのでしょうか。

次の表をつかって、説明していきます。

入力時に全角空白が入力されているのか、半角空白ではないのかをチェックしたいわけです。

入力時ということですから、入力規則をつかうことで対応することができます。

では、B2:B6を範囲選択します。

データタブにある、データの入力規則をクリックします。

データの入力規則ダイアログボックスが表示されます。

 設定タブをクリックします。

入力値の種類を「ユーザー設定」にします。

数式のボックスに、

=isnumber(find("　",b2))

と設定します。

そして、エラーメッセージも設定しますので、エラーメッセージタブに移動します。

スタイルは、停止　を選択します。

全角空白でないと許可しないとすることができます。

タイトルには、「全角空白」とします。

エラーメッセージには、「全角空白を入力してください。」と設定します。

では、OKボタンをクリックして確認してみましょう。

B2には、全角空白をつかって、苗字と名前をわけて入力すると、普通に入力することができました。

B3には、全角空白をつかわないで、確定しようとしたら、エラーメッセージが表示されました。

このように、全角空白が入力されていないと、確定することができません。

修正することで入力することができました。

B4には、半角空白を入力して入力したところ、確定することができませんでした。

このように、全角空白が入力されていないと、確定できないように入力規則をつかって設定することができます。

では、条件に設定した数式を確認しておきましょう。

=isnumber(find("　",b2))

はじめのISNUMBER関数は、数値か日付（シリアル値）ならば、TRUEをかえし、そうでなければ、FALSEをかえすという関数です。

では、引数を確認すると、FIND関数をつかって、セル内に「”　“」全角空白があるかを探しています。

全角空白があれば、そのセル内の何文字目にあるのかをかえします。

B2の場合は、3文字目に全角空白があるので、3と返します。

するとISNUMBER関数が、数値があるのでTRUEをかえすという数式です。

つまり、TRUEということで、全角空白が入力されていることがわかるという仕組みです。

逆に全角空白がない場合は、見つからないので、FIND関数の結果は、#VALUE!をかえします。

よって、ISNUMNER関数は、TRUEをかえせないので、含まれていないということがわかります。

入力規則をつかうことで、今回のような全角空白が入力されているかどうかのチェックを入力時におこなうことができます。

Excel。FIND関数をつかって、検索する文字列の位置を算出します。【FIND】

Excel。FIND関数をつかって、検索する文字列の位置を算出します。

＜関数辞典：FIND関数＞

FIND関数

読み方： ファインド　　

分類： 文字列操作 

FIND(検索文字列,対象,[開始位置])

検索する文字列の位置を算出する

Excel。文字列から該当の文字を見つけることができるFIND関数【FIND】

Excel。文字列から該当の文字を見つけることができるFIND関数

＜FIND関数＞

住所から都道府県を抽出するときや、氏名から苗字を抽出するときに、お馴染みの関数といえば、FIND関数ですね。

今回は、住所から県という文字が何文字目にあるのかを確認するためにFIND関数をつかってみましょう。

B列に住所が入力しています。

C2には、FIND関数をつかった数式を設定します。

=FIND("県",B2)

C4まで、オートフィルで数式をコピーします。

FIND関数の引数は、

最初の引数は、「検索文字列」。

ここに設定した文字を検索しますので「”県”」と設定します。

2つ目の引数は、「対象」。検索したい対象なので、B2と設定します。

これで、「県」という文字が文字列の左側から何文字目にあるのかを算出することができます。

Excel。セル内に指定した文字が含まれていたら、行全体を手早く塗りつぶしたい【contains】

Excel。セル内に指定した文字が含まれていたら、行全体を手早く塗りつぶしたい

＜条件付き書式＋FIND関数＞

指定した文字が含まれるセルだけを塗りつぶしするならば、条件付き書式のセルの強調表示ルールにある「文字列」をつかって、設定すれば手早く設定することができます。

では、指定した文字が含まれていた場合、セルではなくて、行全体を塗りつぶすにはどのようにしたらいいのでしょうか。

今回は、B列の住所に「横浜市」が含まれていたら、行全体を塗りつぶすようにします。

条件に一致したら、行全体を塗りつぶしたいわけですから、「条件付き書式」をつかっていきます。

あとは、どのような条件を設定するかを考えていきます。

A2:B6を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」をクリックします。

「次の数式を満たす場合に値を書式設定」のボックスに数式を設定します。

=FIND("横浜市",$B2)

あとは、書式ボタンをクリックして、塗りつぶしたい色を設定したら、OKボタンをクリックすれば完成です。

設定した数式は、FIND関数をつかった数式です。

FIND関数は、指定した文字列が含まれているかどうかを判断する関数です。

「=FIND("横浜市",$B2)」

と設定することで、横浜市が含まれていたら＝TRUE。

含まれていなければ＝FALSEを返します。

TRUEならば条件が満たされているということになりますので、塗りつぶす対象になるというわけです。

なお、「$B2」と列を固定した複合参照にすることで、行全体を対象にすることができます。

複合参照をつかうことで、行全体を塗りつぶすことができるというわけですね。

Excel。VLOOKUP関数で先頭の複数文字と合致するデータを抽出したい【first character】

Excel。VLOOKUP関数で先頭の複数文字と合致するデータを抽出したい

＜VLOOKUP＋LEFT+FIND関数＞

別表から合致するデータを検索し抽出することができる「VLOOKUP関数」。

例えば、商品コードと合致する商品名や単価などを検索し抽出することはできますが、次のような、先頭からハイフンまでの複数文字を別表から抽出するにはどのようにしたらいいのでしょうか。

VLOOKUP関数は、4番目の引数の「検索方法」を近似値か完全一致でなければなりません。

講座番号のハイフンまでの左の文字列と合致するものという場合には、VLOOKUP関数だけでは対応することができません。

そこで、B2の数式は、ちょっと長くなりますが、次のように設定することで、対応することができます。

=VLOOKUP(LEFT(A2,FIND("-",A2)-1),$A$8:$B$10,2,FALSE)

あとは、オートフィルで数式をコピーします。

完全一致は完全一致ではあるのですが、ハイフンまでというのを、どう表現すればいいのかを考えていきます。

VLOOKUP関数の最初の引数「検索値」をハイフンより前の文字列としてあげればいいわけです。

そこで、検索値には、LEFT関数とFIND関数をつかうことで、ハイフンよりも前の文字列を抽出することができます。

「LEFT(A2,FIND("-",A2)-1)」

ハイフンの位置が文字列の何文字目にあるのかを、算出すればいいわけなので、FIND関数をつかって、ハイフンの文字位置を確認します。

「EX-001」の場合は、「3」と算出されます。

ただし、この「3」のままLEFT関数で使ってしまうと、左から3文字目までを抽出ということになり「EX-」となってしまうので、検索することができません。

そこで、「-1」しているというわけです。

これで、「EX」というハイフンより前の文字列を抽出することができましたので、あとは、VLOOKUP関数を設定するだけで完成します。

なお、今回は、ハイフンで区切られていたので、ハイフンより左側の文字列を抽出するというルールがありましたが、区切られているルールがない場合は、この数式をつかうことはできません。

Excel。セル内の「*」だけを手早く削除したいけど、どうしたらいいの【Asterisk】

Excel。セル内の「*」だけを手早く削除したいけど、どうしたらいいの

＜置換：「~*」, REPLACE+FIND関数＞

意外と簡単な処理と思っていても、実際やってみると、上手くいかないとか、面倒な処理だったというケースは多々あります。

例えば、セル内の「*（アスタリスク）」だけを取り除きたい、消したい場合もそのケースの一つだといえます。

次の表を用意しました。

B列のセルには、「*（アスタリスク）」が含まれています。

この「*（アスタリスク）」だけを取り除きたいわけですね。

関数をつかって対応する方法もあります。

C2の数式は、

=REPLACE(B2,FIND("*",B2,1),1,"")

と設定してあります。

オートフィルを使って数式をコピーすることで、セル内から「*（アスタリスク）」を取り除くことができました。

REPLACE関数は指定の場所を置換することができる関数なので、指定の場所はFIND関数をつかいます。

「*（アスタリスク）」がセル内の左から何文字目にあるのかを算出することで、「*（アスタリスク）」を取り除くことができたわけです。

このように、関数をつかった方法でもいいのですが、もっと楽にセル内の「*（アスタリスク）」を取り除くことができます。

それは「置換」です。

置換をつかうことで、新たに列を設ける必要もありませんし、試行錯誤しなら数式を設定する必要もありません。

ただし、「置換」で処理するにあたり、注意するポイントがありますので、確認しておきましょう。

対象範囲のB2:B5を範囲選択します。

ホームタブの「検索と選択」にある「置換」をクリックします。

検索と置換ダイアログボックスが表示されます。

オプションは特に設定しません。

検索する文字列に「~*」（チルダ＋アスタリスク）と入力したら、設定完了です。

「すべて置換」をクリックします。

これで、「*（アスタリスク）」を手早く取り除くことができました。

ポイントは「~*」（チルダ＋アスタリスク）だということ。

単純に「*（アスタリスク）」を「検索する文字列」に入力して、実行すると、すべての文字が空白に置換されてしまいます。

今回のように、一見簡単そうに思える作業でも、実際処理してみると、スムーズに処理できないこともあるようですね。

また、関数を使えば、セルの途中だけなど、細かく設定することも可能なので、色々な対応方法を増やしていくといいかもしれませんね。

Excel。検索する値の一部だけ含む表から該当するデータを抽出したい。【Part of the data】

Excel。検索する値の一部だけ含む表から該当するデータを抽出したい。

＜LOOKUP+FIND関数＞

VLOOKUP関数は、とても使い勝手がいい関数なので、アチラコチラで使用されているのですが、対応できないケースというのも、結構あります。

例えば、次のような場合です。

やりたいことは、B3の担当者を、住所から判断して、その地域を担当している担当者名をB6:B8の中から抽出したいわけです。

簡単に言えば、神奈川県厚木市にお住いのお客様の担当は、誰なんだということ。

この程度の表ならば、目視で解決しますが、データ量が増えれば目視でというわけにもいきません。

VLOOKUP関数を使えば、抽出できるように思えますが、VLOOKUP関数では対応できません。

理由は、VLOOKUP関数の検索値です。

今回の検索値は、「神奈川県厚木市東町」とすると、範囲に該当するデータの地区には、「神奈川県厚木市東町」というデータはありません。

一致しませんから、検索方法の完全一致では対応できませんし、近似値で対応できるわけでもありません。

つまり、検索値と範囲の検索値が同じ、あるいは、数値の近似値でなければVLOOKUP関数をつかうことができないのです。

このようなケースの場合は、LOOKUP関数をアレンジしてつかうことで解決することができます。

B3に次の数式を設定してみましょう。

=LOOKUP(1,0/FIND(A6:A8,B2),B6:B8)

正しくデータを抽出することができました。

設定した数式を確認しておきましょう。

FIND(A6:A8,B2)　は何をしているのかというと、A6～A8のそれぞれのデータにB2の文字と合致するものが、何文字目にあるのかを算出することができます。

今回の場合、A6とA8は含まれていないので、「#VALUE」となり、A7は、1文字目にあるので1と算出されます。

常に1というケースならば、0で除算しなくてもいいのですが、5文字目に見つかった場合にも対応したいので、除算します。

該当するデータのみが「0」、それ以外はエラーとすることができます。

そして、LOOKUP関数をつかい、検索値を1に指定して、「0」と同じ番目にある担当者名を抽出することができます。

今回のケースのように、VLOOKUP関数をつかって対応することができない場合、何か他の関数を使えないのか、色々考えてみると、意外な方法が見つかるかもしれませんね。

Excel。検索する値の一部だけを含む表から対応する値を抽出したい【LOOKUP】

Excel。検索する値の一部だけを含む表から対応する値を抽出したい

＜IFERROR+LOOKUP+FIND関数＞

何をしたいのかを、まずは確認しておきましょう。

A列の住所の文字列。

例えば、A2に横浜市という文字が含まれています。

横浜市という文字が含まれていたら、D1;E4までの担当者リストから、横浜市の担当である。内藤さんの名前をB2に表示するという作業です。

ワイルドカードをつかうのか？VLOOKUP関数をつかうのか？

あれやこれやと考えると思います。

ただ、VLOOKUP関数では、解決しないことは、すぐにわかります。

では、B2には、どのような数式が設定しているのか、確認しましょう。

=IFERROR(LOOKUP(0,0/FIND($D$2:$D$4,A2),$E$2:$E$4),"")

最初のIFERROR関数をつかっているのは、該当するデータが無い場合「#N/A」エラーが表示されるので、エラー表示を空白にするようにするために使っています。

わかりにくいのが、LOOKUP関数ですが、先にFIND関数から説明します。

D列の地区名のデータが、A2の住所の文字内に含まれているのかを確認します。

このケースでは、5,#VALUE!,#VALUE!と算出されます。

5と算出されたのは、A2のセルに横浜市という文字が、左から5文字目にあることを意味しています。

そして、LOOKUP関数。

今回は、LOOKUP(検索値,検索範囲,対象範囲)という引数をもっている、ベクトル形式のLOOKUP関数で処理をします。

最初の検索値は、「0」。

次の引数の「検索範囲」から「0」をみつけるためのものです。

検索範囲は、0を先程紹介したFIND関数で算出した値で除算させます。

なぜこのようなことをしているのかというと、FIND関数で見つからなかったものはエラーと算出されています。

「0/エラー」と計算させたところで、結果はエラーになるわけです。

今回は、「0」を探していますので、エラーと算出されているものを除外することができるというわけです。

逆に、今回のように「5」という算出結果が出ているのであれば「0/5」となり「0」という結果を算出します。

こうすることで、検索値が「0」のものをみつけることができるわけです。

最後の引数の対象範囲には、「$E$2:$E$4」と担当列を設定します。

オートフィルで数式をコピーするので、絶対参照も忘れずに設定しておきます。

これで、「0」の結果の行を抽出することになるので、担当の列に、それぞれの担当者名を表示することができるというわけです。

なかなか、使う機会が少ない、「LOOKUP関数」ですが、VLOOKUP関数でもダメ、HLOOKUP関数でもダメというケースでは、意外と活躍する関数なので、LOOKUP関数も抑えておくといい関数なのかもしれませんね。

Excel。先頭の文字が合致するデータを簡単に検索したいけど、どうしたらいい。【First character】

Excel。先頭の文字が合致するデータを簡単に検索したいけど、どうしたらいい。

＜VLOOKUP+LEFT関数＞

左2文字がカテゴリーを表している商品コードから、そのカテゴリーを別表のデータを使って、どのカテゴリーに分類されているかがわかる表をつくるには、どうしたら、効率よく作ることができるでしょうか？

次の表を使って確認していきます。

A列の商品コードの左2文字が、分類コードになっています。

分類コードから該当するカテゴリーを、C列のカテゴリーを検索して表示したいというわけです。

このような表にしたいわけです。

今回のようなサンプルならば、目視・自力で対応することもできますが、件数が増えれば、そのようにはいきません。

このような表ならば、VLOOKUP関数をつかうというのは、イメージできますが、どうやって、分類コードを作るのかを考えがちになってしまいます。

新しい列を追加して、例えば、フラッシュフィルをつかったり、LEFT関数をつかったりして、分類コードを作るというのが、スタンダードかもしれません。

ただ、それでは、新しい列を追加するなど、作業工程が増えてしまいます。

VLOOKUP関数の引数の検索値は、別に商品コードや、従業員コードのような、固定したデータしか設定できないわけではなく、関数も普通につかうことができます。

今回は、左から2文字が、分類コードということなで、シンプルに、LEFT関数をつかうことで、抽出することができます。

C2の数式を確認しておきましょう。

=VLOOKUP(LEFT(A2,2),$E$2:$F$4,2,FALSE)

検索値に、LEFT(A2,2)とLEFT関数をつかって、左から2文字を検索値としてつかっています。

今回のような場合には、VLOOKUP+LEFT関数というネストで対応できましたが、たまたま、分類コードが2ケタと統一されていたからLEFT関数をつかったわけです。

分類コードの文字数が、バラバラだった場合は、「-（ハイフン）」が何文字目にあるのかを、見つけて、左からその文字目をつかうようにしなければいけません。

C2の数式を次のように修正する必要があります。

=VLOOKUP(LEFT(A2,FIND("-",A2)-1),$E$2:$F$4,2,FALSE)

LEFT関数の中に、FIND関数をつかって、「-（ハイフン）」が何文字目にあるのかを算出させて、その値から1を減算した値を算出する必要があります。

まだ「-（ハイフン）」があるので、区切られているところがわかるのですが、「-（ハイフン）」がないと、どこまでが、分類コードなのかというルール化がされていないと対応することができません。

計算式でも算出できるなど、色々なことを考えて管理することが大切になります。

Excel。セル内の一部の文字が合致したらそのデータの行全体を塗りつぶしたい。【Fill the line】

Excel。セル内の一部の文字が合致したらそのデータの行全体を塗りつぶしたい。

＜FIND関数＋条件付き書式＞

条件付き書式をつかえば、セル内の文字に条件と合致するデータが含まれていれば、そのセル自体に書式を設定することは簡単です。

例えば、次の表。

C列の住所のフィールドのデータから、横浜市のデータに塗りつぶしを設定したいとします。

範囲選択をして、ホームタブの条件付き書式から「セルの強調表示ルール」にある「文字列」をクリックします。

文字列ダイアログボックスが表示されますので、「横浜市」と設定して、書式を設定したら、OKボタンをクリックします。

すると、横浜市という文字が含まれているセルに書式が反映されました。

このように、セルに該当する文字列があれば、条件付き書式を設定することで、書式を反映させることができます。

しかしながら、セルは塗りつぶすことができましたが、該当するレコード。

つまり行全体に条件付き書式を設定し、反映するにはどのようにしたらいいのでしょうか？

それでは、該当するレコード。行全体に条件付き書式を設定していきます。

A2:C10まで範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しいルールダイアログボックスが表示されます。

ルールの種類を「数式を使用して、書式設定するセルを決定」をクリックします。

行全体など、アレンジしたい時は、「数式を使用して、書式設定するセルを決定」をつかうことで、対応することができます。

次の数式を満たす場合に値を書式設定に、次の数式を設定します。

=find("横浜市",$C2)

FIND関数は、セル内に検索したい文字が左から何文字目にあるのかを算出することができる関数です。

今回の場合、セル内に、「横浜市」という文字がなければ、#VALUEという判定になり、条件を満たすことができないと判断されます。

「横浜市」という文字があれば、数値を算出してくれます。

また、引数内の「$C2」ですが、列固定の複合参照とすることで、行を対象として書式を反映することができます。

仮に、「C$2」のように行固定の複合参照にすれば、列を対象として書式を反映することができます。

あとは、書式ボタンをクリックして、任意の塗りつぶしを設定します。

最後に、新しいルールダイアログボックスのOKボタンをクリックします。

ご覧のように、住所のセル内に「横浜市」という文字がある行全体が塗りつぶされていることが確認できました。

条件付き書式は、関数を組み合わせつかうことで、色々対応することができるので、社内の資料などで、つかってみると、よりわかりやすくなるなるかもしれませんね。

Excel。セル内の文字の一部が含まれた行全体を塗りつぶしたい【Fill the line】

Excel。セル内の文字の一部が含まれた行全体を塗りつぶしたい

＜条件付き書式・FIND関数＞

セル内の文字の一部が合致したら、そのセルを塗りつぶすというのは、条件付き書式を使うことで簡単に設定することができます。

次の表を使って確認してみましょう。

住所が横浜市のセルだけを塗りつぶすことにします。

ホームタブの「条件付き書式」にある「セルの強調表示ルール」の「文字列」をクリックします。

文字列ダイアログボックスが表示されますので、「横浜市」と入力して、書式を選択し、OKボタンをクリックすると、セル内に「横浜市」という文字が含まれている場合、セルが塗りつぶしされます。

しかしながら、この方法だと、セルは塗りつぶしすることができますが、そのデータ、つまり行全体を塗りつぶすことはできません。

次のように、住所のセル内に「横浜市」という文字が含まれていたら、その行全体を塗りつぶすにはどうしたらいいのでしょうか？

 

住所の一部に「横浜市」という文字が含まれているのか？いないのか？を判断する必要があるわけです。

では、どのようにしたら、判断することができるのでしょうか？

セル内の文字を見つけるには、『FIND関数』を使えば見つけることができます。

サンプルとして確認してみましょう。

D2に次の数式を設定してみました。

=FIND("横浜市",C2)

 

その数式をオートフィルでコピーすると、横浜市という文字が含まれている場合は、5という値を返します。

これは、セルの左から5文字目からあるよ。

という意味で、逆に、横浜市という文字が含まれていない場合は、見つからないので、「#VALUE!」というエラーを返してきます。

この数式と、条件付き書式を組み合わせて使うことで、対応することができそうです。

D列は、サンプルなので削除しておきます。

A2:D11を範囲選択します。

 

ホームタブの「条件付き書式」にある「新しいルール」をクリックします。

新しいルールダイアログボックスが表示されます。

ルールの種類を「数式を使用して、設定するセルを決定」に合わせます。

ルールを設定しますので、次の数式をボックス内に入力します。

=find("横浜市",$C2)>0

先程と同じ、FIND関数をつかっています。

また、引数の対象を「$C2」と複合参照にしているのは、C列の値をA列もB列も参照させる必要がありますので、相対参照ではなく、複合参照にする必要があります。

この複合参照がポイントになるわけですが、簡単にいえば、条件付き書式で行全体を塗りつぶしたい場合は、列に「$」をつけた複合参照にすればいいと覚えておくといいかもしれませんね。

あとは、セルを塗りつぶす色を設定して、OKボタンをクリックします。

これで完成しました。

条件付き書式は数式をつかうことで、様々なシーンで使える可能性がありますので、試してみると面白いかもしれませんね。

Excel。入力規則のリスト。2列表示で選択出来る方法。かなり強引ですが…【Input rule】

Excel。入力規則のリスト。2列表示で選択出来る方法。かなり強引ですが…

＜入力規則のリスト・LEFT+FIND関数＞

見積書や納品書など、VLOOKUP関数と入力規則のリストを組み合わせて使うと便利ですよね。

例えば、次の表。

A:B列の表は、見積り表のようなものだとして、D:E列が商品リストとします。

A列には、入力規則のリストが設定してあります。

データの入力規則をつかって、次のように設定してあります。

 

B列には、数式を設定しております。

=IFERROR(VLOOKUP(A2,$D$2:$E$6,2,FALSE),"")

VLOOKUP関数をつかうことで、商品リストである、D:E列から商品コードに該当する商品名を検索し表示しています。

このままで十分といえば十分なのですが、このぐらいの商品数ならば、商品コードとその商品名を覚えることもできますが、商品数が多くなったり、あるいは、商品コードがバーコードのように覚えられるボリュームでない場合、イチイチ確認したあとに、入力規則のリストで入力するわけですね。

だったら、コピー&ペーストしたほうが早い場合もでてしまいます。

そこで、入力規則のリストを2列表示することができないものか？と思うわけですね。

こんな感じです。

この方法をご紹介していきますが、かなり強引なので…ご了承のほど。

用意するものがあります、G列に、数式を使ってリストに表示したい列を作ります。

 G2の数式は、

=CONCAT(E2,"…",F2)

CONCAT関数は、旧バージョンにはありませんので、CONCATENATE関数を使いましょう。

「&」をつかった結合でもOKです。

つづいて、A列とB列の間に、列を挿入します。追加した列幅は、狭くてOKです。

その挿入したB列に次のように、入力規則のリストを設定します。

 

C列に変わった、VLOOKUP関数の数式を確認修正します。

=IFERROR(VLOOKUP(A2,$E$2:$F$6,2,FALSE),"")

検索値が、A列になっていることを確認します。

そして、商品コードのA列に数式を設定します。]

入力規則のリストが設定されているようでしたら、消去しておきます。

A2には、次の数式を設定します。

=IFERROR(LEFT(B2,FIND("…",B2,1)-1),"")

何をしているのか説明します。

IFERROR関数は、B列が空白の場合エラーが表示されるので、その予防です。

LEFT関数は、左から指定された文字数を抽出します。

今回対象は入力規則のリストで入力したB列です。

何文字なのかが、FIND関数で算出しています。

FIND("…",B2,1)-1。

今回は、”…”が区切り文字になっています。

最初に登場する、”…”を見つけた文字数を算出したら、”…”の文字数が余計なので、その値から「-1」します。

これで動きを確認してみましょう。

入力規則のリストが2列分表示されているので、検索しやすくなっています。

リストの幅が狭い場合は、B列を広げるとリストの幅も広がります。

動くことは動くのですが、B列の文字が見えていては、カッコ悪すぎですね。

最後の処理となりますが、文字を見えないようにしていきます。

文字の色を「白」にするとかではありません。

表示形式のユーザー定義をつかって、文字を隠していきます。

B2:B6を範囲選択して、セルの書式設定ダイアログボックスを表示します。

ユーザー定義の種類に「;;;」（セミコロン×3）と設定します。

文字を見えなくすることができました。

かなり強引な方法ですが、入力規則のリストを2列表示にする方法でした。