[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『3列の中に共通の文言があればlookupで抽出したいです』（こころ）
別シートに都道府県とそのコードが書いてるマスタ用のシートがあります。
A列 B列 1 北海道 コード1 2 青森 コード2 ・ ・ ・ ・
そして大元のシートに
3列都道府県の住所が書いている列があります。
3列のどれかに必ず住所が入っていて、違う都道府県が入ることはありません。全て当てはまらない場合は、空白にしたいです。
A列 B列 C列 D列
1 秋田県〜 コード6
2 香川県 コード35
3 金沢県 コード11
4 空白を返す
このA〜C列の都道府県名から、別シートの表を参照にして
その都道府県コードをD列に反映させたいです。
うまく抽出できるような関数の数式を教えていただけないでしょうか。
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
確認だけ
大元シートの A〜C列には都道府県名だけではなく住所が入るってことですよね？ ＞違う都道府県が入ることはありません。 同じ都道府県なら 2つ以上住所が入ることもあるんですか？ ＞金沢県 石川県ですよね？
以上 （笑） 2022/12/02(金) 14:13:14
大元シートの A〜C列には都道府県名だけではなく住所が入るってことですよね？
基本的には住所が入っていますが、支社名だけの場合もありその場合は、
「秋田支社」という文言が入っている場合もあります。
＞違う都道府県が入ることはありません。 同じ都道府県なら 2つ以上住所が入ることもあるんですか？
少ないですが、その場合もあります。たとえば、
A列が、秋田県〜〜という住所が書いてあり
B列には秋田支社
という場合もあります。
＞金沢県
石川県ですよね？
すみません、完全に間違えました。はい、石川県になります。
（こころ） 2022/12/02(金) 14:21:33
支社名はすべて都道府県名なんですか？ だとしたら、マスターシートが Sheet2 だとして
D1 =IF(A1&B1&C1="","",XLOOKUP(LEFT(A1&B1&C1,2)&"*",Sheet2!$A$1:$A$47,Sheet2!$B$1:$B$47,"",2)) または D1 =IF(A1&B1&C1="","",IFERROR(VLOOKUP(LEFT(A1&B1&C1,2)&"*",Sheet2!$A$1:$B$47,2,FALSE),""))
以上、参考まで （笑） 2022/12/02(金) 14:29:34
先ほど教えていただいた式で問題が発生しました。
東京都だけ23区別に分けて表示必要があり、東京の場合は別の列のマスタを参照する
という式に変更することはできますでしょうか。
住所の列は３列ではなく1列にまとめたので検索するところは1列で大丈夫になりましたので
今入っている数式は以下になります。でもこれだと東京はすべて東京で
ひとくくりになってしまうため23区で分けることはできません。
=IF(A1="","",IFERROR(VLOOKUP(LEFT(A1,2)&"*",Sheet2!$A$1:$B$47,2,FALSE),""))
●追加したい条件…東京が含まれている場合はその隣のC〜D列のマスタを参照させる Sheet2!$C$1:$D$47
説明があまり得意ではないのでうまく伝わらないようでしたら申し訳ありません。
（こころ） 2022/12/02(金) 17:47:46
具体例を出して説明してくれませんか。
Sheet2のC列には何が入力されてるんですか？ 港区とか品川区とかの23区名？
では元の住所が「東京都八王子市･･･」のように、23区以外だったらどうするんですか？ 他にも「東京支社」だったら？
というか、実際のデータも本当に住所？ 違うんだったらそう言ってくださいよ、無駄なやりとりが続くだけなので。
それと ＞Office365 これは間違いないですか？
以上 （笑） 2022/12/02(金) 20:07:18
なので東京支社というのはありません。
しっかり東京都〜住所が入っているデータになります。
教えていただいた式だと東京だけで判断されるのですべて東京になってしまうため東京専用のマスタを参照した方がいいのかと思いました。
東京で参照するマスタは、23区とそれ以外の市町村も全て入っているので
東京都の場合はそのマスタを参照して
「東京23区」と「東京23区以外」の二つで分けて表示させたいと思っています。
それ以外の都道府県はそのままです
Office365は間違いありません。
色々教えていただいて大変感謝しています。
どうぞよろしくお願いします。
（こころ） 2022/12/02(金) 21:38:29
くり返しますけど具体的に答えてください。 ＞Sheet2!$C$1:$D$47 ＞23区とそれ以外の市町村も全て入っている このSheet2の「C列」には何がどのように入力されてるんですか？ 千代田区など23区以外に「町田市」「東村山市」「武蔵村山市」などと入力されてるってこと？
全てだったら「47行」では足りませんよね？ 実際は何行あるんですか？ ＞東京都の場合はそのマスタを参照して ＞「東京23区」と「東京23区以外」の二つで分けて表示させたいと思っています。 これはどういうこと？ 例を出して、どのセルがどうなればいいのか、具体的に説明してください。 ＞Office365は間違いありません。 では 2022/12/02(金) 14:29:34 の数式で XLOOKUP の方はうまくいかなかったとのことですが、具体的にどうなったんですか？ VLOOKUP と同じ結果になるはずなんですが･･･
以上 （笑） 2022/12/02(金) 23:07:20
実際は何行あるんですか？
今は113行あります。東京だけは仰る通りそのように23区とそれ以外はそのように 入力しています。
新宿区 コード13 23区
杉並区 コード13 23区
墨田区 コード13 23区
八王子市 コード13 23区以外
立川市 コード13 23区以外
武蔵野市 コード13 23区以外
三鷹市 コード13 23区以外
青梅市 ・
府中市 ・
という形です。コードは会社で使用している別の名称なので実際にはこのような名称ではありません。
これはどういうこと？ 例を出して、どのセルがどうなればいいのか、具体的に説明してください。
＞これは、例えば東京が「コード13」と本来は全て表示されるとすると
23区なら「コード13 23区」
それ以外なら「コード13 23区以外」
と分けて表示できるようにしたいです。
江戸川区だったら「コード13 23区」
立川市だったら「コード13 23区以外」
という形です。東京だけは住所の東京都（〜区〜市）までを読み取ってこの2種類に分けたいです。
最初の私が書いた質問のところの
A列に住所が書いてあります。
B列とC列は無視することにしたのでもう関係ないです。
A列が、東京都中野区なら「コード13 23区」
東京都三鷹市なら「コード13 23区以外」とD列に表示されるようにしたいです。
それ以外は、大阪なら大阪市のコード…と表示したいです。
では 2022/12/02(金) 14:29:34 の数式で XLOOKUP の方はうまくいかなかったとのことですが、具体的にどうなったんですか？ VLOOKUP と同じ結果になるはずなんですが･･･
＞先ほどもう一度やってみたところちゃんとできました。確認不足で申し訳ありません。
最初にやった時に二つの式を入れて見比べてみたところ結果が違うところが
ありましたのでそう思い込んでいましたが、何かが違ってうまくいっていなかったのかもしれません。
何度も申し訳ありません。
まだ説明不足で不明点などありましたらお手数ですが追加で説明させていただきますのでどうぞよろしくお願いします。
（こころ） 2022/12/05(月) 13:34:15
Xlookupでやると5文字目まで引っ張るというやりかたで頭に「東京都」をつけて
東京都足立区
東京都荒川区
東京都板橋区
東京都江戸川区
東京都大田区
とすると、東京だけはきれいに分けられるのですが、
そうすると他の件はヒットせずに結果がブランクになってしまいます。
■東京だけ
=IF(M12="","",XLOOKUP(LEFT(M12,5)&"*",マスタ!$C$3:$C$113,マスタ!$D$3:$D$113,"",2))
↑ここです。
■東京以外のもの
=IF(M12="","",XLOOKUP(LEFT(M12,2)&"*",マスタ!$C$3:$C$113,マスタ!$D$3:$D$113,"",2))
なので、普通の件は２文字までを検索して、東京だけは5文字まで検索するという
式にして分けないと抽出できないのかなと思いました。
（こころ） 2022/12/05(月) 15:26:33
＞今は113行あります。 なんで「113」もあるんですかね？ 3行目から113行目までだとしても「111」行
23区以外の市町村数は「39」のはずですけど？ 23区を合わせて 23＋39 で「62」では？
それ以外の行には何が入力されてるんですか？
東京は C、D列を検索、東京以外は A、B列を検索という話だったはずですが まさか変わってませんよね？ ＞コード13 東京は 23区もそれ以外もすべてこれ？ だとしたら、住所から「23区かどうか」さえわかればいいことになりますが それでいいんですか？
念のため確認ですけど 住所というのは「東京都大田区○○△△」のように市区町村名の後も番地まで続くんですよね？ それとも東京都の後は市区町村名だけ？ ＞それ以外は、大阪なら大阪市のコード…と表示したいです。 何ですか、大阪市のコードって？ 大阪府では？
以上 （笑） 2022/12/05(月) 16:00:56
東京は C、D列を検索、東京以外は A、B列を検索という話だったはずですが まさか変わってませんよね？
すみません。全てまとめた数を言っていました。
今のマスタは以下のような感じです。B列とD列が会社の支社名になります。
具体的には支社名になりまして、
●●?竃k海道支社
●●?叶ﾂ森支社
●●?鞄結梹x社23区
●●?鞄結梹x社23区以外
という表にしたいと思っています。
A列 B列 C列 D列
東京以外の都道府県 46個 支社名 東京 66個 支社名
東京は 23区もそれ以外もすべてこれ？
だとしたら、住所から「23区かどうか」さえわかればいいことになりますが それでいいんですか？ ＞ はい、東京は23区かそれ以外で分けられれば大丈夫です。
念のため確認ですけど
住所というのは「東京都大田区○○△△」のように市区町村名の後も番地まで続くんですよね？ それとも東京都の後は市区町村名だけ？ ＞ きちんと番地まで住所が入っています。
それ以外は、大阪なら大阪市のコード…と表示したいです。
何ですか、大阪市のコードって？ 大阪府では？ ＞ 書き方が悪くてすみません。大阪のコードというより支社名を抽出して出したいと思っています。 大阪の住所なら、上記のB列の「●●?椛蜊緕x社」と出したいです。
（こころ） 2022/12/06(火) 13:13:03
●●株式会社 北海道支社
●●株式会社 青森支社
●●株式会社 東京支社23区
●●株式会社 東京支社23区以外
となります。
（こころ） 2022/12/06(火) 14:22:02
↓ がD列に入っているってこと？ ＞●●株式会社 東京支社23区 ＞●●株式会社 東京支社23区以外
東京と東京以外で処理を分ける
=IF(A1="","",IF(LEFT(A1,2)="東京","●●株式会社 東京支社23区"&IF(ISERR(FIND("区",LEFT(A1,7))),"以外",""),【東京以外の検索】))
東京の住所で、左から7文字以内に「区」を含んでいれば「23区」、含んでいなければ「23区以外」と判定 ※23区以外で、7文字以内に「区」を含む住所があるかないかまでは調べてません 市町村名が4文字以上なら問題ないので、3文字の市町村名でということ
以上、参考まで （笑） 2022/12/06(火) 15:00:11
次回からは説明をうまくできるように気を付けます。
本当にありがとうございました。
（こころ） 2022/12/06(火) 16:06:52
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.