【エクセル関数】採番いろいろ

エクセルは表計算ツールなので、データの順番を振る場面が多々あると思います。

使う人や場面によっていろいろな作法があるのではないでしょうか。

1. 上の行＋１

使用頻度：★☆☆

使い勝手：★★☆

利点：一番上の行以外、行を入れ替えても採番が崩れない。見やすい

難点：行の入れ替え時に採番が狂う

=(上のセル) + 1

2. ROW関数

使用頻度：★★★

使い勝手：★★☆

利点：行を入れ替えても採番が崩れない

難点：-

=ROW() - (任意の値)

ROW関数で行番号が求められるため、そこから任意の値を引き算することで採番する。

１．と比べこちらのほうが使い勝手がいいため、普通はこちらを使う。

3. 固定値

使用頻度：★★★

使い勝手：★★☆

利点：行を入れ替えると採番が崩れる

難点：行を入れ替えると採番が崩れる

数値直指定

オートフィル機能を使いあえて数値を直接指定することもある。

特に複数人が編集する管理簿などで、勝手に並び順を変えられたりしたら困るような局面ではあえて固定で数値を入力する。

【関数】フィルターでの部分一致(複数項目)

前回のポストの3−2−2では部分一致の場合、VBAを駆使しても３項目までしかフィルターがかけられないという問題について触れました。
今回のポストでは、別の方法で３項目以上のフィルターがけを解説します。

◎関数での対処
フィルター対象に対して、部分一致での複数項目がけができないため、フィルター対象が複数項目の条件を満たすかを判定する列を別に追加し、追加した列の判定結果を完全一致でフィルターがけすることで擬似的に３項目以上のフィルターがけを実施します。



上の図では、下の条件のいずれかを満たす商号または名称を絞るため、H列に判定フラグを設けています。
・「　株式会社」を含む
・「和」を含む
・「エンジニア」を含む

判定フラグの列で「TRUE」を絞り込めば、上記条件を満たす商号または名称が絞られることとなります。

○関数解説
判定フラグ列にはこんな風に関数を記載しています。

=(3*LEN($G3) -(LEN(SUBSTITUTE($G3,"　株式会社","")) +LEN(SUBSTITUTE($G3,"和",""))+LEN(SUBSTITUTE($G3,"エンジニア","")))>0

論理式で指定の文字列を含むかを判定しているのですが、わかりづらいので補足すると、
「商号または名称」の文字数と、「商号または名称」から条件の文字列を空で置換した文字列の文字数を比較することで、指定の文字列を含んでいるかを判定しています。

これは文字列がある文字列を含んでいるかを確認する一般的な関数の使い方で、以下のように使います。

■文字列が条件の文字列を含むかを確認する方法
LEN(文字列) 
　- LEN(SUBSTITUTE(文字列,条件の文字列,"")) > 0 

今回は３つ条件があるため、3*LEN($G3)と減算対象を３倍にしているわけです。

【エクセル関数】文字列の加工① MID関数とFIND関数

外部のデータを利用する上で、一部のデータを加工したい場合があると思います。
たとえば官公庁のデータだったり、お客様のデータや外部システムからの出力データだったりと実際そういう場面は多々あるでしょう。

そういう時活躍する関数がMID関数です。

◼︎MID(文字列,開始位置,文字数)
引数「文字列」の文字列から、引数「開始位置」に指定した文字数目から数えて引数「文字数」分を取得します。

たとえば、文字列「WindowsVista」の1文字目から7文字目まで、つまり「Windows」を取得しようと考えた場合、

=MID("WindowsVista",1,7)

と記述します。

またこの文字列から「Vista」を取得しようと考えた場合、8文字目から5文字分を取得することとなるため、
=MID("WindowsVista",8,5)
と記述します。
この場合、指定する「文字数」はオーバーしても良いため、このように書くこともできます。
=MID("WindowsVista",8,100)

◼︎MID関数の用法
下図に日本郵便の配布するCSVから取得した郵便番号と住所の一部を記載しました。ただし、CSV上では郵便番号がハイフン「-」抜きで書かれているため、MID関数を用いてハイフン「-」ありに文字列を加工することにします。

こんな風に書きます。

=MID($C3,1,3)&"-"&MID($C3,4,4)

「MID($C3,1,3)」で1文字目から3文字の「604」を取得して、「MID($C3,4,4)」で4文字目から4文字の「0991」を取得、最後にハイフン「-」でつなげて郵便番号の形にしています。

このようにMID関数を使えば、抜き出す文字列の始まりと始まりから取得する文字数がわかる場合に限り、文字列を抽出し、加工することができます。
今回は郵便番号が必ず4文字目にハイフン「-」がくる8文字の文字列となるという規則性に着目してMID関数を利用しましたが、文字数が決まっていない場合にもMID関数の利用場面があります。

たとえば、メールアドレスから「@」以降を省いたユーザーIDを取得する場合、こんな風に取得できます。

=MID($C3,1,FIND("@",$C3)-1)

ユーザーIDを取得するにはMID関数を利用するには「開始位置」が1文字目であるかわかっているので、「@」の手前までの「文字数」を取得する必要が有ります。
そのためこの関数式ではFIND関数で「＠」の文字が何文字目に来るかを取得し、その手前までの文字数を計算してMID関数の第3引数にしています。

◼︎FIND(検索文字列,対象,[開始位置])
「対象」に指定した文字列から「検索文字列」を検索し最初の文字列から何文字目であるかを取得する。オプショナルな引数である「開始位置」を指定すると指定した文字数目から「検索文字列」を探すようになります。

◼︎FIND関数の用法
上述の通り。


◼︎より応用的な使い方として
FIND関数のオプショナルな引数である「開始位置」を活用すると、以下のように「検索文字列」が複数ある場合でも対応できます。

例えばC4セルにある「osama-no-mimi@lobanomimi.com」から「-」に囲まれた「no」をMID関数を用いて取得する場合を考えます。
この時FIND関数で初めて「-」がくる位置と、次に「-」がくる位置を求められれば「開始位置」と開始位置からの「文字数」を取得でき、そうすればMID関数を使うことができます。

計算してみると、 この場合最初の「-」は

=FIND("-",C4)
で6文字目に来ることがわかり、

次の「-」は「開始位置」を以下のように設定することで、

=FIND("-",C4,FIND("-",C4)+1)

9文字目に来ることがわかります。

よってMID関数で指定すべき「開始位置」は、
=FIND("-",C4) + 1
(=7文字目)
「文字数」は、

=FIND("-",C4,FIND("-",C4)+1) - FIND("-",C4) - 1
(=2文字)

となります。

以上から「no」を取得するには

=MID(C4,FIND("-",C4)+1,FIND("-",C4,FIND("-",C4)+1) - FIND("-",C4) - 1)

と記述すれば良いこととなります。


さすがにここまで来るとちょっと読みにくいですね。

【エクセル関数】HLOOKUP関数とVLOOKUP関数

みなさんHLOOKUP関数って使いますか？

多分使わないし、そもそも何？って方もおられるのではないでしょうか。
でもこれが「VLOOKUP関数って使いますか？」ということでしたら結構な人がつかってるよと言ってくれるのではないでしょうか？

今回はそんなLOOKUP関数兄弟の話です。

まずVLOOKUP関数から

◼︎VLOOKUP(検索値, 範囲, 列番号, [検索の型])
範囲指定の右端の列から検索値を探し出して、見つけ出したセルの行から指定された列番号分右に行ったセルの値を検索します。［検索の型］はオプショナルでTRUE(検索値近似一致；1で代用可)かFALSE(検索値完全一致；0で代用可)を指定します。通常は近似値を検索してくるというのは意外と厄介なのでFALSEを指定することが多いでしょう。

◼︎VLOOKUP関数の用法
下図に支社コードにエリアと電話番号、代表者を持った電力事業者や通信事業者にありがちなの支社データのようなものを用意しました。

これをB12セルの支社コードを元にB13セルに代表者を取得しようとすると、図のように
=VLOOKUP($B12,$B$3:$E$7,4,FALSE)
という風になります。
ちなみに絶対参照を使っているのは、B12セルをそのままコピーしてB13セルに貼り付けても範囲指定がずれないようにするためです。

じゃあ本題のHLOOKUP関数ってなんだよと言いますと、簡単に申しますとHLOOKUPの縦列と横列が逆になったものと考えてください。

こんな感じです。

◼︎HLOOKUP(検索値, 範囲, 列番号, [検索の型])
範囲指定の上端の列から検索値を探し出して、見つけ出したセルの列から指定された行番号分下に行ったセルの値を検索します。［検索の型］はVLOOKUPと同様の使い方です。

◼︎HLOOKUP関数の用法
下図にVLOOKUPの時に使用したデータの縦列と横列を入れ替えたデータを用意しました。

同様にこれをB9セルの支社コードを元に代表者名を取得しようとすると、図のように
=HLOOKUP($B9,$C$2:$F$5,4,0)
という風になります。


さて、そんなHLOOKUP関数ですが、特殊な事情がない限り使われません。
そもそも項目を縦向きに並べて横にレコードを積み重ねていくことが普通ないからです。

ただし、使う場面になると結構役立つのでそんな関数があったなと覚えておいて損はないですよ。