よくわからないエンジニア

よく分からないエンジニアの日々の記録

よくわからないエンジニア

関数で苦労した話

大学生時代、コールセンターで働いていた。詳しい事は割愛するが、電話をかけていたのは最初の一年くらいで、途中からPCで表やリストを作る事が主な仕事になった。 顧客データの管理は主に「ACCESS」と「EXCEL」、別段詳しく無かったがググれば大抵の事は解決出来た。この時から私の悪い癖はついていたようで、「なんでかよく分からないけど、解決したから良いや」とろくに理解を深める事をしなかった。結果、以前解決したはずの事象に再度ぶち当たり、再び数時間かけて解決する。。という無能っぷりを遺憾なく発揮していた。

時が経過し、昨日久しぶりにEXCELを仕事で使った。現時点で会社の人々から「無能」と認識されていない(つもりの)私は、「あぁ、それくらいなら直ぐに作っちゃいますよ」と言い残し、自席に戻るなり以下の単語を検索した。

「EXCEL 検索 複数 1セル」

こんな格好悪い検索を二度としないで済むように、備忘録的に手順を書き残す事にした。

EXCEL関数の忘備録

【ステップ1】

「検索範囲内に特定の文字列があった場合に、該当する右側セルの値を全て抜き出し、別シートの1セルに収める」大本の数式は以下で解決する事が出来た

oshiete.goo.ne.jp

同じ事を考えている人がいるものだな。本件で使う表は以下になる。

「sheet1」
f:id:unknownengineer:20160416185418j:plain
「sheet2」
f:id:unknownengineer:20160416185808j:plain

つまりsheet2のB列に地方ごとの県庁所在地を全て表示させたいわけなのだよ。まずはリンク先の手順通りに下準備、sheet1のD2に以下関数を入れて下までスクロールします。ここで地方名/番号を作業列に入力されます。

=IF(A2="","",A2&"/"&COUNTIF(A$2:A2,A2))

f:id:unknownengineer:20160416233446j:plain

そしてsheet2に県庁所在地を持ってくるには以下関数を

=IF(COUNTIF(Sheet1!C:D,A2&"/"&1)=0,"",INDEX(Sheet1!C:C,MATCH(A2&"/"&1,Sheet1!D:D,0)))  

なんですが、これだけだと最初に該当する1個しか連れてこれないので最大9個の県庁所在地を取得する場合…

=IF(COUNTIF(Sheet1!C:D,A2&"/"&1)=0,"",INDEX(Sheet1!C:C,MATCH(A2&"/"&1,Sheet1!D:D,0)))&IF(COUNTIF(Sheet1!D:D,A2&"/"&2)=0,"","・"&INDEX(Sheet1!C:C,MATCH(A2&"/"&2,Sheet1!D:D,0)))&IF(COUNTIF(Sheet1!D:D,A2&"/"&3)=0,"","・"&INDEX(Sheet1!C:C,MATCH(A2&"/"&3,Sheet1!D:D,0)))&IF(COUNTIF(Sheet1!D:D,A2&"/"&4)=0,"","・"&INDEX(Sheet1!C:C,MATCH(A2&"/"&4,Sheet1!D:D,0)))&IF(COUNTIF(Sheet1!D:D,A2&"/"&5)=0,"","・"&INDEX(Sheet1!C:C,MATCH(A2&"/"&5,Sheet1!D:D,0)))&IF(COUNTIF(Sheet1!D:D,A2&"/"&6)=0,"","・"&INDEX(Sheet1!C:C,MATCH(A2&"/"&6,Sheet1!D:D,0)))&IF(COUNTIF(Sheet1!D:D,A2&"/"&7)=0,"","・"&INDEX(Sheet1!C:C,MATCH(A2&"/"&7,Sheet1!D:D,0)))&IF(COUNTIF(Sheet1!D:D,A2&"/"&8)=0,"","・"&INDEX(Sheet1!C:C,MATCH(A2&"/"&8,Sheet1!D:D,0)))&IF(COUNTIF(Sheet1!D:D,A2&"/"&9)=0,"","・"&INDEX(Sheet1!C:C,MATCH(A2&"/"&9,Sheet1!D:D,0)))

それを下まで引っ張ってあげれば
f:id:unknownengineer:20160417000314j:plain

はい、完成!!

【ステップ2】

「検索範囲内に特定の文字列があった場合に、該当する右側セルの値を全て抜き出し、別シートの1セルに収める。ただし、全て改行する」
改行はCHAR(10)を使いますので、以下を突っ込みます。

=IF(COUNTIF(Sheet1!C:D,A5&"/"&1)=0,"",INDEX(Sheet1!C:C,MATCH(A5&"/"&1,Sheet1!D:D,0)))&CHAR(10)&IF(COUNTIF(Sheet1!D:D,A5&"/"&2)=0,"",""&INDEX(Sheet1!C:C,MATCH(A5&"/"&2,Sheet1!D:D,0)))&CHAR(10)&IF(COUNTIF(Sheet1!D:D,A5&"/"&3)=0,"",""&INDEX(Sheet1!C:C,MATCH(A5&"/"&3,Sheet1!D:D,0)))&CHAR(10)&IF(COUNTIF(Sheet1!D:D,A5&"/"&4)=0,"",""&INDEX(Sheet1!C:C,MATCH(A5&"/"&4,Sheet1!D:D,0)))&CHAR(10)&IF(COUNTIF(Sheet1!D:D,A5&"/"&5)=0,"",""&INDEX(Sheet1!C:C,MATCH(A5&"/"&5,Sheet1!D:D,0)))&CHAR(10)&IF(COUNTIF(Sheet1!D:D,A5&"/"&6)=0,"",""&INDEX(Sheet1!C:C,MATCH(A5&"/"&6,Sheet1!D:D,0)))&CHAR(10)&IF(COUNTIF(Sheet1!D:D,A5&"/"&7)=0,"",""&INDEX(Sheet1!C:C,MATCH(A5&"/"&7,Sheet1!D:D,0)))&CHAR(10)&IF(COUNTIF(Sheet1!D:D,A5&"/"&8)=0,"",""&INDEX(Sheet1!C:C,MATCH(A5&"/"&8,Sheet1!D:D,0)))&CHAR(10)&IF(COUNTIF(Sheet1!D:D,A5&"/"&9)=0,"",""&INDEX(Sheet1!C:C,MATCH(A5&"/"&9,Sheet1!D:D,0)))

f:id:unknownengineer:20160416235656j:plain

引っ張ってくる値の個数に合わせて式を書き足さなきゃいけないので、クッソ関数が長くて死ぬほどカッコ悪いです。まぁ、それでもとりあえずやりたい事は実現出来たので目を瞑ります…(もっと格好良い式で実現出来る方法をご存知の方、助言頂けると嬉しいです)