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。文字が入力されている行全体を塗りつぶしたい【character】

Excel。文字が入力されている行全体を塗りつぶしたい

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

決まった文字ではないですが、文字が入力されているセルがあれば、わかりやすいようにその行全体をぬりつぶしたい場合、どのようにしたら、いいのでしょうか。

次の表をつかって、紹介します。

提出状況のB列には、提出日が入力されているセルもあれば、対象外など文字が入力されているセルも含まれています。

B4やB5のように文字が入力されているセルならば、その行全体を塗りつぶしたいわけです。

条件のついた書式なので、条件付き書式をつかいます。

問題となるのは、どのような、条件式を設定するかです。

数値や今回のように日付ならば、ここからここまで以外ならのようにすれば、いいかもしれません。

文字が固定されているならば、その文字を指定することで対応できますが、決まっていない文字の場合を考えるとなかなか、大変そうです。

しかし、条件付き書式の条件式は、IFERROR関数だけで対応することができます。

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

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

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

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

条件式を設定します。

=IFERROR($B2*0,1)

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

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

これで、文字列だけを対象にして塗りつぶすことができました。

ではどうして、

=iferror($B2*0,1)

という条件式で文字列だけを対象にできたのでしょうか。

IFERROR関数は、エラーならば、どのようにするのかという関数です。

最初の引数は、値です。

B2に「×0」します。ここで、B2が数値ならば、「0」になります。

数値でなければ、「#VALUE!」というエラーが表示されます。

「#VALUE!」は、数値以外のものをつかって計算していますというエラーです。

要するに、文字列の場合ということです。

これで、数値なのか、そうでないかを判断することができます。

今回は、日付ですが、Excelでは、日付はそもそも、シリアル値なので、数値です。

そして、2番目の引数で、エラーの場合どうするのか、設定できます。

ここで、エラーならば、「1」と設定します。

なぜ1なのかというと、Excelは1=TRUEということで、条件が成立というTRUEという意味を含めることができるからです。

なので、「=1」というように、1と等しいとしなくてもいいというわけです。

IFEROR関数をつかって、ワザとエラーを発生させて、数値と文字をわけることができる方法をつかってみました。

Excel。対象がエラーの場合に指定した値を返すIFERROR関数【IFERROR】

Excel。対象がエラーの場合に指定した値を返すIFERROR関数

＜関数辞典：IFERROR関数＞

IFERROR関数

読み方： イフエラー　　

分類： 論理 

IFERROR(値,エラーの場合の値)

対象がエラーの場合に指定した値を返す

Excel。検索文字を含むなら「○」という判定を手早く行うにはどうしたらいいの【contains characters】

Excel。検索文字を含むなら「○」という判定を手早く行うにはどうしたらいいの

＜IFERROR+IF+SEARCH関数＞

東京都の23区内なのかなど、セル内に該当する文字が含まれているのかを判定したい場合、単純にIF関数だけでは対応することができません。

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

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

C列に23区内ならば、「○」を表示するとします。

C2に設定する数式は、

=IFERROR(IF(SEARCH("東京都*区",B2),"○",""),"")

として、オートフィルで数式をコピーすると、完成なのですが、IF関数以外にもIFERROR関数やSEARCH関数をつかっています。

なぜIF関数以外をつかっているのかというと、IF関数の論理式に「ワイルドカード」が使えないからです。

「東京都」という文字で始まり「区」を含んでいるのかを判断させる必要が生じます。

そこで、SEARCH関数をつかって、判断させます。

IF関数にSEARCH関数をネストした数式、

=IF(SEARCH("東京都*区",B2),"○","")

オートフィルで数式をコピーしてみると、合致しないセルに「#VALUE!」というエラーが表示されています。

SEARCH関数で困るのは、合致しない場合、「#VALUE!」というエラーを表示してしまうことなんです。

つまり、偽の場合の設定よりもエラーが強いので、偽の場合の表示設定を行ってくれません。

そのため、エラーをコントロールするIFERROR関数をつかって「#VALUE!」というエラーを表示しない作業をさせる必要があるわけです。

なお、IF関数は、偽の場合を設定しないとエラーになるので、「#VALUE!」と表示されます。
そのため、「””」を設定しておきます。

また、検索するSEARCH関数と同じようにFIND関数がありますが、FIND関数は、ワイルドカードをつかうことができませんので、SEARCH関数をつかう必要があります。

Excel。VLOOKUP関数で「&””」で0表示は消せるが、数値型だとうまくいきません。【numeric type】

Excel。VLOOKUP関数で「&””」で0表示は消せるが、数値型だとうまくいきません。

＜IFERROR+VALUE+VLOOKUP関数＞

VLOOKUP関数で、検索値はあるけども、抽出する値が無い場合には、「0（ゼロ）」と表示されてしまいます。

B2に設定した数式は、

=IFERROR((VLOOKUP(A2,$A$7:$B$9,2,FALSE)),"")

この数式を、オートフィルでコピーしています。

検索値が無い場合は、IFERROR関数を組み合わせることで、「#N/A」のエラーを消すことができます。

ただ、B3のように、検索値はあるけども、抽出する値が無い場合には「0（ゼロ）」が表示されてしまいます。

そこで、VLOOKUP関数のあとに「&””」をつけることで、非表示にすることができます。

B2の数式は、

=IFERROR((VLOOKUP(A3,$A$7:$B$9,2,FALSE)&""),"")

と修正して、オートフィルで数式をコピーしています。

これで、「0（ゼロ）」を非表示にすることができました。

さて、ここまでは、抽出する値が「文字型」だったので問題はなかったのですが、B7の値を「鉛筆」から、わざと「100」という数値に変更してみましょう。

B4の値は、「100」と表示されていますが、左揃えになっています。

つまり、数値型ではなく「数値文字型」という文字型に変わってしまっています。

「&””」はたしかに、「0（ゼロ）」を非表示にしてくれますが、数値型を「文字型」に変えてしまいます。

そこで、VALUE関数を追加することで、数値型のままにすることができます。

=IFERROR(VALUE(VLOOKUP(A2,$A$7:$B$9,2,FALSE)&""),"")

これで、数値型のままにすることができました。

しかし、数値型に対応した数式なので、この数式を使って、文字型を抽出しようとしても、表示することができません。

このようなことから、抽出するデータが数値の場合と文字の場合で「VALUE関数」の使い分けをする必要がありそうです。

なお、VALUE関数は、文字列として入力されている数値を、数値列に変換する関数です。

Excel。VLOOKUP関数で０が表示されても「&””」を追加すれば解決できます。【zero】

Excel。VLOOKUP関数で０が表示されても「&””」を追加すれば解決できます。

＜IFERROR+VLOOKUP関数＞

色々なケースを考えると、VLOOKUP関数だけでは、対応することができません。

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

B2に設定した数式は、

=VLOOKUP(A2,$A$7:$B$9,2,FALSE)

この数式をB4までオートフィルで数式をコピーしています。

B2の結果は「#N/A」と表示されました。

原因は、A2の「4」は、参照先の「範囲」であるA7:A9に無いので、検索抽出できないことによるエラー「#N/A」が表示されてたわけです。

IFERROR関数をつかうことで、エラーに対応することができます。

B2の数式を次のように修正しました。

=IFERROR(VLOOKUP(A2,$A$7:$B$9,2,FALSE),"")

エラーだったら、空白にするようにしたわけですね。

この数式も、オートフィルで数式をコピーしています。

B2は、たしかにエラーを消すことができましたが、B3は、「0（ゼロ）」が表示されたままです。

この原因は、検索抽出データは、A2:A4にあるのですが、検索結果が空白になってしまっていることで、発生したわけです。

検索するデータはあるわけなので、エラーは表示されないけど、表示したいデータが空白の為、「0」になってしまう。

このような場合は、「&””」をつけると、「0」の表示を消すことができます。

B2の数式を修正して、オートフィルで数式をコピーしてみます。

修正したのが次の数式です。

=IFERROR((VLOOKUP(A2,$A$7:$B$9,2,FALSE)&""),"")

VLOOKUP関数のうしろに「&””」を追加しました。

これで、「0（ゼロ）」の表示も消すことができました。

検索するデータに空白のデータがある場合には「0（ゼロ）」と表示されてしまうので、「&””」をつけることで、対応することができます。

なお、VLOOKUP関数に限らず、「0(ゼロ)」が表示されてしまう場合は、「&””」をつけることで対応することができます。

Excel。VLOOKUP関数で#N/Aというエラーが表示されたら、どう解決するの【Error】

Excel。VLOOKUP関数で#N/Aというエラーが表示されたら、どう解決するの

＜IFERROR＋VLOOKUP関数＞

VLOOUP関数をつかった数式の算出結果で「#N/A（ノーアサイン）」というエラーが表示されることがあります。

どのようにしたら、エラーを表示させないで済むのでしょうか。

B2の数式は、

=VLOOKUP(A2,$A$5:$B$7,2,FALSE)

と設定してあります。

B2に表示されている「#N/A」の原因は、検索するコードが、範囲にないので探せないというエラーです。

今回の場合は、A2に「A04」と入力されていますが、A5:A7には、「A04」はありません。

そのため、抽出することができないため、「#N/A」が表示されてしまったというわけです。

そこで、エラーが発生した時に、どのようにするのか処理できるIFERROR関数をつかうことで、「#N/A」を表示しないようにすることができます。

B2の式を次のように修正します。

=IFERROR(VLOOKUP(A2,$A$5:$B$7,2,FALSE),"")

これで、「#N/A」エラーを非表示にすることができます。

Excel。オートフィルターをつかわずに、該当するデータを別のセルに表示したい【Extract】

Excel。オートフィルターをつかわずに、該当するデータを別のセルに表示したい

＜IF＋ROW関数　IFERROR＋INDEX＋SMALL＋ROW＋COLUMN関数＞

データベースや表から、該当する条件のデータを抽出して、別のセルに表示したい場合、オートフィルターでデータを抽出して、コピーをする。

この作業でもいいのですが、抽出条件が変わるとなると、オートフィルターで抽出してコピーするという作業が面倒になってきます。

そこで、数式だけで、対応することもできます。

A1:C8にデータがあって、G1のクラス名に合致するデータを、F4を起点として抽出表示したいわけです。

G1を「B」にしたら、クラスBのメンバーが抽出され表示するというものです。

最初は準備として、D2に次の数式を設定し、D8までオートフィルで数式をコピーします。

=IF(B2=$G$1,ROW()-1,"")

B列のデータと、G1のデータが合致したら、行番号-1を算出するという数式ですね。

ROW関数は行番号を算出します。

「-1」するのは、あとでINDEX関数をつかうのですが、座標がわかりやすくするために、「1」からの値にしたいからです。

今回は、データの1件目が2行目にありますので、「-1」すれば、「1」から表示することができます。

F4にメインとなる数式を設定します。

設定したら、オートフィルで数式をコピーします。

=IFERROR(INDEX($A$2:$C$8,SMALL($D$2:$D$8,ROW(A1)),COLUMN(A1)),"")

長い数式ですが、これだけで、該当するデータを抽出し表示することができます。

F4:H8まで数式を設定してあります。

それでは、数式を確認していきます。

最初のIFERROR関数ですが、データの件数が少ない場合、「#NUM!」というエラーが表示されてしまうので、その防止のためにIFERROR関数をつかっています。

INDEX関数は、設定した範囲の中から、指定した行番号と列番号のデータを抽出するという関数です。

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

「$A$2:$C$8」と設定します。

オートフィルで数式をコピーしますので、絶対参照を忘れないようにしましょう。

2番目の引数は、「行番号」です。

SMALL($D$2:$D$8,ROW(A1))

先程、IF関数で算出した結果を、SMALL関数を使い、1番小さい値を抽出させます。

1番小さい値、次に2番目の小さい値というようにしたいので、SMALL関数をつかい、さらに、ROW関数でA1を指定します。

オートフィルで数式をコピーすると、A1がA2とかわり、2番目・3番目に小さい値のある場所を見つけてくれるという仕組みです。

最後の引数は、「列番号」です。

COLUMN(A1)

こちらも、先程と同じ仕組みで、オートフィルで数式をコピーするときに、座標がズレるようにしています。

このように、INDEX関数をつかうことで、データを抽出することもできます。

Excel。セルごとに分割された数値を合算し、その値もセルごとに分割したい【cell merging】

Excel。セルごとに分割された数値を合算し、その値もセルごとに分割したい

＜CONCAT・SUM・IFERROR・MID・LEN・COUNT関数＞

なんでそんなことしちゃったのという、Excelの帳票をみることがあります。

例えば、数値をセルごとに分割してある帳票。

そもそも、1500と分割しないでセル内にあれば、単純な和算で済むわけですが、セルごとに分割してあるわけですね。

このような場合、一の位で合算して、繰り上がったら…なんてやっていたら、桁数が増えたら大変というか、数式を考えるだけでも大変です。

最初にやることは、結合して1つのセルにまとめて数値にします。

A6に次の数式を作り、交通費を一つのセルにまとめます。

A6に設定した数式は、

=CONCAT(B2:G2)

同じように、A7に、宿泊費を一つのセルにまとめます。

A8には、合算値を算出したいので、A8に設定する数式は、

=SUM(A6:A7)

ところが、「0」と算出されてしまいました。

原因は、よくみると、A6とA7は、左揃えになっています。

これは文字型になっていることを表しています。

CONCAT関数は、文字結合をする関数なので、算出されたものは、文字になっていて数値ではないわけです。

そこで、A6とA7の数式のうしろに「*1」を追加します。

=CONCAT(D2:G2)*1

こうすると、文字型は文字型でも、数値文字型に変わるので、合算値を算出することができます。

あとは、どうやって、一つずつのセルに分割して表示させる数式を考えていきます。

合計のB4の数式は、

=IFERROR(MID($A$8,LEN($A$8)-COUNT(C1:$G$1),1)*1,"")

あとは、この数式をG4まで、オートフィルで数式をコピーすれば完成です。

数式が長くなったので、説明していきます。

B1:G1までの見出し行は、「十万・一万…一」と表示してますが、元は、普通の数値が入力してあって、表示形式をつかって、漢数字にしています。

別に直接、漢数字で入力しても問題はありません。

最初の「IFERROR関数」は、数値がない場合、#VALUEというエラーが発生するので、エラーならば空白にするようにしています。

MIDはセル内の文字列を指定の場所にある、指定した文字数分だけ抽出する関数です。

先程、合算値を算出したA8を指定します。

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

何文字目の文字なのかを指定します。

「LEN($A$8)-COUNT(C1:$G$1)」

LEN関数は、文字数を算出する関数です。これで合算値が何桁なのかを算出します。

その値から、C1:G1の数値の個数を算出した値を減算します。

桁数をずらす必要があるので、終点のみを絶対参照に設定しておきます。

今回の例としては、

LEN($A$8)は、「5」

COUNT(C1:$G$1)は、「5」

5-5=0なので、0文字目を抽出ということになるのですが、0文字目を抽出というのは、おかしいので、エラーが表示されるわけですね。

このように、ちょっと複雑な数式になっていますね。

帳票上の必要があるかもしれませんが、基本的には、数値を一つずつ、一つのセルごとに分割する場合には、アレコレ、アイディアが必要になりますね。

Excel。わかりやすいように、文字が入力されている行全体を塗りつぶしたい【Paint the line】

Excel。わかりやすいように、文字が入力されている行全体を塗りつぶしたい

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

列内のデータで数値ではなく、文字が入力されている場合、その行全体を手早く塗りつぶすにはどのようにしたらいいのでしょうか

次の表を用意しました。

B列の数値が入力されているデータはそのままで、文字が入力されているデータがあれば、その行全体を塗りつぶしたいわけです。

文字が同じならば、その文字をつかって、条件付き書式のルールを作ることもできますが、様々な文字の場合、Excelでは、判断する関数が用意されていません。

数値を除外したくても、例えば、1～10と決まった範囲ならば、それ以外ということも出来るかもしれませんが、それもできません。

ただ、Excelにおいて、文字か数値かを判断するには、それほど、難しくはありません。

では、条件付き書式を設定していきます。

A2:B6を範囲選択して、ホームタブの条件付き書式にある、「新しいルール」をクリックします。

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

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

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

=IFERROR($B2*0,1)=1

あとは、書式を設定したら、OKボタンをクリックします。

これで、文字が入力されているデータの行全体を塗りつぶすことができました。

ところで、設定した数式は、どう動いているのでしょうか。

確認しておきましょう。

=IFERROR($B2*0,1)=1

B2に0を乗算しています。数値に「0（ゼロ）」を乗算すれば「0（ゼロ）」になります。

文字の場合、「0（ゼロ）」を乗算すれば、「#VALUE!」というエラーが表示されます。「#VALUE!」は、文字と数値で計算をした場合に表示されるエラーです。

要するに、エラーが表示されたら、文字ということがわかるというわけです。

そこで、IFERROR関数をつかうことで、エラーだった「1」を算出するように設定します。

その「1」と等しかったらセルを塗りつぶすように、条件付き書式を設定すればいいというわけです。

Excelには、エラー発生時に対応する関数が用意されていますので、ワザとエラーを発生させて対応させるという方法もありますよ。

Excel。エラー表示をやめたいなら、IFERROR関数が重宝します。【ERROR】

Excel。エラー表示をやめたいなら、IFERROR関数が重宝します。

＜IFERROR関数＞

見積書や請求書などで、使用するVLOOKUP関数。

ミスを抑制することもできるので、とても重宝する関数なのですが、検索値が見つからない、あるいは、空白だったりすると、「#N/A」（ノーアサイン）というエラーを表示してしまいます。

エラーが表示されたら、数式を削除して、また必要になったら、オートフィルで数式をコピーする。

または、一から数式を作成するのでは、時間がかかってしまいます。

かといって、エラーを表示したまま印刷するわけにもいきません。

そこで、エラーを表示させないように、関数をつかって対応していきます。

エラーを表示しないようにするには、概ね2パターンあります。

ひとつは、「IF関数」をつかったパターン。

商品コードが空白というケースのように、検索値が空白だったら、エラーが表示されるので、エラーだったら空白とするようにIF関数をつかって設定する方法があります。

数式としては、

=IF(B4="","",VLOOKUP(B4,$H$4:$J$7,2,FALSE))

ですね。

そして、もうひとつが、「IFERROR関数」です。

今回はこちらのIFERROR関数をつかって紹介していきます。

このIFERROR関数は、エラーが表示されたらどうするのかを設定すればいいだけの関数なので、IF関数をつかったケースよりもわかりやすいかもしれません。

最初から、数式を作り直してもいいですが、VLOOKUP関数のような場合、引数が多いので、直接数式を修正するほうが、楽かもしれません。

数式を次のよう修正しました。

=IFERROR(VLOOKUP(B4,$H$4:$J$7,2,FALSE),"")

結果は次のようになりました。

C4には、エラーが表示されていないことが確認できました。

このように、IFERROR関数をつかったネストにすることで、手早くエラーが表示を防いだ数式に修正することができました。

IFERROR関数の引数は、IF関数の場合と異なり、数式があって、その数式の算出結果がエラーだったらどのように表示するのかを設定します。

エラーの場合の値を、空白にするので「””（ダブルコーテーション×2）」で対応しています。

Excelでは、色々エラーが表示されることがありますので、対応方法の一つとして、IFERROR関数をつかってみるというのは、いいかもしれませんね。

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。結合したセルの値をまとめた一覧表を作りたいけど、コピペじゃ大変なんです。【Merged cells】

Excel。結合したセルの値をまとめた一覧表を作りたいけど、コピペじゃ大変なんです。

＜ROW関数・IFERROR+INDEX+SMALL関数＞

セルの結合は設定するのが簡単なのですが、結合したセルの値を別のセルにコピーするとか、一覧表を作る場合、意外と大変というか、困ってしまうことがあります。

例えば、次の表のような処理をする場合で、説明していきます。

A列には、セル結合された担当者名が入力されています。

入力規則のリストなどで使うために、D列のように担当者一覧を作りたいとします。

今回は、4名なので、コピー&ペーストでも、どうにかなりますが、件数が多かったらコピー&ペーストでは大変です。

D2に「=A2」というセル参照を設定して、オートフィルで数式をコピーしても、できませんよね。

Excel VBAでマクロをつくってもいいのですが、マクロを作成すること自体も面倒です。

今回のような、結合セルの値から、一覧表をつくるには、ちょっとした関数を組み合わせれば、可能になります。

では、早速作っていきましょう。

C2に、次の数式を設定して、オートフィルで数式をコピーします。

=IF(A2<>"",ROW(A1),"")

このように、算出されます。

数式の説明をします。

縦方向にセルが結合されている場合、結合されている一番上のセルに値が設定されいます。

値が入っているか、どうかを判断したいので、IF関数を使います。

論理式は、「A2<>""」。

A2が空白ではなかったらと、問い合わせます。

真の場合は、「ROW(A1)」。

ROW関数で行番号をつかって、数値を算出させます。

義の場合は、空白　とします。

これによって、値があるセルの場所がわかりました。

続いて、D列にまとめる作業を行います。

D2に次の数式を設定します。

=IFERROR(INDEX($A$2:$A$10,SMALL($C$2:$C$10,ROW(A1))),"")

数式を説明していきます。

IFERROR関数を使うのは、このあと、オートフィルで数式をコピーすると、抽出データがないセルに、「#NUM!」というエラーが表示されるので、その防止のために使います。

INDEX関数をつかうことで、行番号と列番号が交差したデータを抽出することができます。

INDEX関数は、INDEX（参照,行番号,列番号）という引数をもっています。

参照は、担当者名を算出したいわけですから、$A$2:$A$10。

オートフィルで数式をコピーしますので、絶対参照を設定します。

行番号は、

SMALL($C$2:$C$10,ROW(A1))

SMALL関数は、その数値の順位の数値を算出します。

このために、先程のIF関数で算出したわけです。

SMALL関数は、SMALL（配列,順位）という引数をもっています。

配列は、IF関数で算出した範囲の、$C$2:$C$10

絶対参照も忘れずに設定します。

順位は、ROW(A1)　A1を指定することで、「1」という数値を算出することができます。

オートフィルで数式をコピーすると、A2・A3とかわることで「2」「3」とスライドすることができます。

列番号は、今回複数列ではないので、省略します。

すると、このような結果になりました。

このように、セル結合した値でも一覧表を作ることができました。

最後に、C列の数値を表示してあると、カッコ悪いので、非表示にしますので、C2:C10を範囲選択して、セルの書式設定ダイアログボックスを表示します。

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

これで、文字を非表示することができました。

セルの結合は、見た目としては、便利なのですが、そのあとに、何かしたい場合は、うまくいかないことがありますので、見た目を重視する必要がない場合は、データベースとして表を作る方がいいかと思います。

Excel。VLOOKUP関数で発生する#N/AエラーはIFERROR関数で対応しちゃいましょう。【IFERROR】

Excel。VLOOKUP関数で発生する#N/AエラーはIFERROR関数で対応しちゃいましょう。

＜IFERROR＋VLOOKUP関数＞

VLOOKUP関数についてまわるエラー。

それが、「#N/A」。

ノーアサインというエラーなのですが、原因は、検索値がない、つまり「検索するものがないので、探すことができない」ので発生するエラーです。

 

C2に設定してある数式を確認しておきましょう。

=VLOOKUP(B2,$E$2:$F$7,2,FALSE)

VLOOKUP関数の引数は、次のようになっています。

VLOOKUP関数ダイアログボックスをつかって確認しておきましょう。

検索値がB2。ここに入力した値を次の範囲から検索します。

範囲は、$E$2:$F$7。

商品リストなどが該当します。

見出し行は不要ですので、データの部分だけを範囲選択します。

列番号は、2。

上記で選択した「範囲」の左側から何列目のデータを抽出したらいいのか？という意味です。

今回は、商品名を抽出し表示したいわけですね。

範囲で商品名は2列目にあるので、「2」です。

検索方法は、「FALSE」。

完全一致で値を検索します。

さて、B2に検索する値が入力されれば、エラーは表示されなくなりますが、見積書や請求書など、すべての行を使わない場合は、エラーが表示されてしまいます。

基本的には、IF＋VLOOKUP関数というネストで、検索値が空白だったら、空白。

そうでなければVLOOKUP関数という方法もいいのですが、検索値がないものを入力してしまった場合でもエラー「＃N/A」が表示されてしまいます。

 

C2の数機は、

=IF(B2="","",VLOOKUP(B2,$E$2:$F$7,2,FALSE))

C2:C6までは、数式をオートフィルでコピーしています。

そのため、C3:C6はエラーが表示されていません。

B列に入力規則のリストをつかって、対象外のものを入力できないようにすれば、IF+VLOOKUP関数でも問題はありませんが、エラーを表示させなくするには、IF関数との組み合わせでは、エラーに対して確実に対応することができません。

そこで、エラーの時に、このような処理をすると支持する関数があります。

それが『IFERROR関数』。

IFERROR+VLOOKUP関数とすれば、検索値が空白であっても、対象外のデータが入力されていたとしても、エラーを非表示にできます。

C2をクリックして、IFERROR関数ダイアログボックスを表示します。

値には、VLOOKUP関数を設定します。

エラーの場合の値は、エラーが発生した時、どうするのかを設定します。

今回は、空白にしたいので、「””（ダブルコーテーション×2）」。

OKボタンをクリックして、数式をオートフィルでコピーします。

C2の数式は、

=IFERROR(VLOOKUP(B2,$E$2:$F$7,2,FALSE),"")

結果を確認してみましょう。

存在しない商品コードを入力しても、エラーは表示されていないことが確認できました。

テキストなどでは、IF関数との組み合わせを紹介したこともあるようで、IF+VLOOKUP関数のネストでエラーを非表示するようにしていますが、IFERROR+VLOOKUP関数という方法もありますので、ケースバイケースでつかっていくといいかもしれませんね。

Excel関数辞典　VOL.36。IF関数～IMAGINARY関数

＜Excel関数＞

今回は、IF関数～IMAGINARY関数までをご紹介しております。

IF関数

イフ

条件で分岐して異なる計算結果を返す

IF(論理式,真の場合[,偽の場合])

IFERROR関数

イフエラー

対象がエラーの場合に指定した値を返す

IFERROR(計算式,エラーの場合の値)

IFNA関数

イフエヌエー

結果が#N/Aの場合は指定した値を返す　 イフ・ノン・アプリカブルの略

IFNA(計算式,エラーの場合の値)

IFS関数

イフエス（イフズ）

1つまたは複数の条件で分岐して異なる計算結果を返す

IFS(論理式1,真の場合1[,論理式2,真の場合2…])

IMABS関数

アイエムアブス

複素数の絶対値を算出する

IMABS(複素数)

IMAGINARY関数

イマジナリー

複素数の虚数係数を取り出す

IMAGINARY(複素数)

Excel。一覧表に重複除いて何人いるの？目視確認では大変なんです。【Deduplication count】

Excel。一覧表に重複除いて何人いるの？目視確認では大変なんです。

＜COUNTIF関数・IFERROR関数・SUMPRODUCT関数＞

複数のスタッフで店舗を回している一覧表があります。

今回知りたいことは、重複しているスタッフを除いて、いったい何名のスタッフで店舗を回しているのかを確認したいわけです。

一列だったらば、重複を除く方法は色々あるのですが、今回のように、縦横の表になってしまっていると、うまくいきません。

当然、人間による、『目視』なんて、大変以外の何物でもありません。

では、どうやったらいいのでしょうか？
数式一発で算出する方法もありますが、知っている関数を使って算出してきます。

最初に算出するのは、そのスタッフが何回登場しているのかを算出していきます。

F2にCOUNTIF関数をつかって数式を作っていきますので、COUNTIF関数ダイアログボックスを表示しましょう。

範囲には、$B$2:$D$4　と設定します。
範囲は固定しておきたいので、絶対参照を忘れずに設定します。

検索条件には、B2　と設定します。
あとは、オートフィルで数式をコピーします。

F2の数式は、
=COUNTIF($B$2:$D$4,B2)

するとこのような結果になりました。

1でないところが、重複しているわけですね。

2だったら、1というような条件を付けた場合、場合によっては3ということも想定されるので、2だったらというような固定的な考え方では対応できません。

ここからがアイディア。

仮に、全部のデータが1だとしたら、データ全部を合算すれば、人数が算出されますよね。

だったら、2のところを、0.5にすれば、データ全部を合算すればいいわけです。

仮に4だったら、0.25にすればいいわけです。

そのようにするには、算出された値を「1」で除算すればいいわけです。
つまり、
=1/F2という式を作ればいいので、その隣に、改めて表をつくります。

このデータ全部を合算したら、6となるわけです。
つまり6名で3店舗を回していることがわかります。

算出することはできましたが、途中計算を出すのを繰り返すのは、ちょっとスマートではありません。

そこで、COUNTIF関数を使う方法もあります。

F2の数式を次のように変更してみました。
=IFERROR(1/COUNTIF($B$2:$D$4,B2),0)
オートフィルで数式をコピーした結果が次の通りです。

ついでなので、式をまとめただけでなく、空白だった場合エラーになってしまう欠点もIFERROR関数を使って防いでいます。

どうしても、現場ではExcelにとって都合のいい表ばかりではありませんので、様々アイディアを投入して解決ことになりますね。

ちなみに、一発で算出する場合には、SUMPRODUCT関数を使う方法もあります。

=SUMPRODUCT(1/COUNTIF(B2:D4,B2:D4))

ただ、SUMPRODUCT関数はあまり、なじみがないのと、配列関数なので、ちょっとわかりにくいところがありますね。