[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『表内の一番左のセルの値を抽出』（ぽざ）
以下の様な表を参照して、あるセルの値が堺市なら別のセルに大阪府、八幡市なら京都府の様に、Ｂ列以降を検索してその値があれば、その行の一番左の値を表示させたいのですが、どの様な数式を使えばいいでしょうか？
Ａ Ｂ Ｃ
1 大阪府 堺市 狭山市
2 奈良県 天理市 生駒市
3 京都府 宇治市 八幡市
4 兵庫県 姫路市 神戸市
どなたかご教示いただけると助かります。
宜しくお願いします。
＜ 使用 Excel：Excel2019、使用 OS：unknown ＞
ざっくりした案ですけど
=MAX(INDEX(($B$1:$C$4=[あるセル])*ROW($B$1:$B$4),,))
で、ゼロより大きい数字が出れば、
その数字が該当の行番号になるので、INDEX関数でA列より参照
とか
（白茶） 2022/07/27(水) 19:56
（白茶）さんの式を借りて来て…強引にでも持って行った結果
式が、とんでもなく長く...ｺﾏｯﾀ(･ω･; ゞ ﾄﾞｩｼﾖｩ ﾔﾍﾞｪｪ
|[A] |[B] |[C] |[D]|[E] |[F]
[1]|大阪府|堺市 |狭山市| |八幡市|京都府
[2]|奈良県|天理市|生駒市|
[3]|京都府|宇治市|八幡市|
[4]|兵庫県|姫路市|神戸市|
E1に検索値（市など）
F1式=IF(E1="","",IF(MATCH($E$1,INDIRECT("B"&MAX(INDEX(($B$1:$C$4=E1)*ROW($B$1:$B$4),,))):INDIRECT("C"&MAX(INDEX(($B$1:$C$4=E1)*ROW($B$1:$B$4),,))),0)=1,OFFSET(INDIRECT(ADDRESS(MAX(INDEX(($B$1:$C$4=E1)*ROW($B$1:$B$4),,)),MATCH($E$1,INDIRECT("B"&MAX(INDEX(($B$1:$C$4=E1)*ROW($B$1:$B$4),,))):INDIRECT("C"&MAX(INDEX(($B$1:$C$4=E1)*ROW($B$1:$B$4),,))),0)+1,4)),0,-1),IF(MATCH($E$1,INDIRECT("B"&MAX(INDEX(($B$1:$C$4=E1)*ROW($B$1:$B$4),,))):INDIRECT("C"&MAX(INDEX(($B$1:$C$4=E1)*ROW($B$1:$B$4),,))),0)=2,OFFSET(INDIRECT(ADDRESS(MAX(INDEX(($B$1:$C$4=E1)*ROW($B$1:$B$4),,)),MATCH($E$1,INDIRECT("B"&MAX(INDEX(($B$1:$C$4=E1)*ROW($B$1:$B$4),,))):INDIRECT("C"&MAX(INDEX(($B$1:$C$4=E1)*ROW($B$1:$B$4),,))),0)+1,4)),0,-2))))
関数の達人さんに、叱られそう...｡°(´∩ω∩`)°｡
（あみな） 2022/07/27(水) 23:30
↑ の表なら
F1 =IF(E1="","",IFERROR(INDEX(A:A,TEXT(MAX(INDEX((B1:C10=E1)*ROW(A1:A10),0)),"0;;")),""))
実際の範囲がどのくらいなのか見当もつきませんけど･･･
以上、参考まで （笑） 2022/07/28(木) 00:03
=IF(E1="","",IFERROR(INDEX(A:A,IFERROR(MATCH(E1,B:B,0),MATCH(E1,C:C,0))),""))
（d-q-t-p） 2022/07/28(木) 06:57
こういう表に変換したらだめなんでしょうか
堺市 大阪府 狭山市 大阪府 天理市 奈良県 生駒市 奈良県 宇治市 京都府 八幡市 京都府 姫路市 兵庫県 神戸市 兵庫県
表の変換作業は案外簡単なんですけどね （´・ω・`） 2022/07/28(木) 07:13
こんなのでワークしないかな？
=TEXTJOIN("",TRUE,REPT(A1:A100,B1:C100=あるセル))
（半平太） 2022/07/28(木) 07:54
=INDEX(A:A,MATCH(あるセル,B:B,0))
でいいんでない？
（ngk） 2022/07/28(木) 11:52
B列から横にずらっと各県の市町村名がならんでるのを想像してたんですけど違うのかな？ 当然各県の市町村数は違うので、各行で最終列が違うという．．．
質問者さんが再登場するまでわかりませんが （´・ω・`） 2022/07/28(木) 12:03
確かに市を表の左側に持ってきて縦にするといけそうですね。
＞B列から横にずらっと各県の市町村名がならんでるのを想像してたんですけど違うのかな？
その通りです。都道府県によって最終列が違うデータです。
また、Ｂ列以降は同じ値が入る事は無い表になります。
（ぽざ） 2022/07/28(木) 12:30
＞ Ｂ列以降は同じ値が入る事は無い表になります。
これは「同行の中では」という意味ですか？
それとも「府中市」や「伊達市」みたいなものはないという意味ですか？
（d-q-t-p） 2022/07/28(木) 12:56
何行×何列の表なんですか？
別の場所に ↓ な表を作ってもいいのなら、パワークエリで簡単にできます。
大阪府 堺市 大阪府 狭山市 奈良県 天理市 奈良県 生駒市 京都府 宇治市 京都府 八幡市 兵庫県 姫路市 兵庫県 神戸市
以上 （笑） 2022/07/28(木) 13:27
=INDEX($A$1:$A$10,SUMPRODUCT(($B$1:$F$4=あるセル)*ROW($A$1:$A$10)),1)
でどうでしょう。
必要に応じて、10やFは変更してください。
（hatena） 2022/07/28(木) 14:41
上で例に挙げた「府中市」みたいに広島県にも東京都にもある市名が
あった場合 SUMPRODUCTだと不具合を起こします。
（d-q-t-p） 2022/07/28(木) 15:03
＞行数10、最大最終列がFとして、 ＞=INDEX($A$1:$A$10,SUMPRODUCT(($B$1:$F$4=あるセル)*ROW($A$1:$A$10)),1)
行数「10」なら
=INDEX($A$1:$A$10,SUMPRODUCT(($B$1:$F$10=あるセル)*ROW($A$1:$A$10)),1)
~~~~~
$F$4 → :$F$10 ですよね？
それと「あるセル」に表にないものを入力して その式を 1〜10行目のどこかのセルに入れた場合、エラーにはなりません。 1行目に式を入れていたら「大阪府」が返ります。
実際はもっと広範囲なら 2022/07/28(木) 13:27 のような表に加工するのがベストでしょう。 ※実際の表も都道府県と市町村なのかどうか知りませんけど･･･
以上 （笑） 2022/07/28(木) 15:12
関数は難しいですね。
（hatena） 2022/07/28(木) 15:20
表の内容は全国の都道府県ではなく、もっと小さなエリアなので同じ市の名前は入る事は無いです。
全てのセルにおいて同じ値があ入る事はないです。
（ぽざ） 2022/07/28(木) 15:49
ちょっと遅かったかな こんな表を
[A] [B] [C] [D] [E] 大阪府 堺市 狭山市 奈良県 天理市 生駒市 京都府 宇治市 八幡市 兵庫県 姫路市 神戸市 北海道 札幌市 旭川市 函館市 伊達市 福島県 福島市 郡山市 伊達市 広島県 府中市 東京都 府中市
こんな表に変換します。 [A] [B] 堺市 大阪府 狭山市 大阪府 天理市 奈良県 生駒市 奈良県 宇治市 京都府 八幡市 京都府 姫路市 兵庫県 神戸市 兵庫県 札幌市 北海道 旭川市 北海道 函館市 北海道 伊達市 北海道,福島県 福島市 福島県 郡山市 福島県 府中市 広島県,東京都
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"列1", type text}, {"列2", type text}, {"列3", type text}, {"列4", type text}, {"列5", type text}}),
ピボット解除された他の列 = Table.UnpivotOtherColumns(変更された型, {"列1"}, "属性", "値"),
削除された列 = Table.RemoveColumns(ピボット解除された他の列,{"属性"}),
グループ化された行 = Table.Group(削除された列, {"値"}, {{"カウント", each _[列1], type list}}),
追加されたカスタム = Table.AddColumn(グループ化された行, "カスタム", each Text.Combine([カウント],",")),
削除された列1 = Table.RemoveColumns(追加されたカスタム,{"カウント"})
in
削除された列1
（´・ω・`） 2022/07/28(木) 16:04
最初の表のどこかのセルを選択した状態で、
[Ctrl]+A
[Alt]を押したままA P Tの順に押す。
「OK」をクリック
[Alt]を押したままT Uの順に押す。
「その他の列のピボット解除」をクリック
[Alt]を押したままT Uの順に押す。
[Alt]を押したままH Cの順に押す。
「閉じて読み込む」をクリック
以上
（ふっ） 2022/07/28(木) 16:35
＞表の内容は全国の都道府県ではなく、もっと小さなエリアなので同じ市の名前は入る事は無いです。
重複がないのなら、下記の式のこんなかんじでどうなのでしょうか？
◆このような表かしら？
|[A] |[B] |[C] |[D] |[E] |[F]|[G] |[H]
[1]|大阪府|堺市 |狭山市| | | |西宮市|兵庫県
[2]|奈良県|天理市|生駒市|五條市|奈良市|
[3]|京都府|宇治市|八幡市|宮津市|
[4]|兵庫県|姫路市|神戸市|明石市|西宮市|
G1に、検索値（市など）
H1 =IF(G1="","",INDEX(A:A,MAX(INDEX((B:E=G1)*ROW(A:A),,))))
※最初と最後のINDEXは、(A:A)範囲固定 ※中間にあるINDEXは、(B:最終列指定)で内容によって可変範囲に ※検索値（あるセル）と、抽出セルは、好きなセル番地に
[ 注意 ]
検索値または、表範囲の（市など）に余分な半角スペースなどが あるとNGです。
西宮市 | ←文字列末尾に半角スペースがある場合は スペースを削除しないといけません
（あみな） 2022/07/28(木) 17:36
同じこと言いますけど･･･
＞G1に、検索値（市など） ＞H1 =IF(G1="","",INDEX(A:A,MAX(INDEX((B:E=G1)*ROW(A:A),,))))
その表でG1セルに（表にはない）札幌市と入れたら「大阪府」が返りますよね。
以上 （笑） 2022/07/28(木) 18:12
=IF(E1="","",IFERROR(INDEX(A:A,TEXT(MAX(INDEX((B1:C10=E1)*ROW(A1:A10),0)),"0;;")),""))
こちらの方法でうまくいきました。ありがとうございます。
他の方も回答していただき、ありがとうございました。
私以外の担当者もこのファイルを使用する事もあるのでシンプルなものでいきます。
かつ、何かあっても私がメンテナンスしやすいのもこちらでした。
表を変換する方法もとても勉強になりました。
発想を変えるだけでで解決する事もあるんですね。
一つ質問するだけでこんなに回答を頂き、大変感謝いたします。
皆様の知識にただただ脱帽しかありません。
私自身まだまだ勉強する必要があるとつくづく思いました。
それぞれ頂いた回答も参考にしてみます。ありがとうございました。
（ぽざ） 2022/07/28(木) 18:37
（笑）さん...ありがとうございます。
意味がわかりました。
"0;;" ←この意味がわからんくて〜^^;
で、TEXT変換してるんですね。なんとなくわかってきました。
で、IFERROR にてエラー対応するんですね。いやいやムズイ^^;
じゃあこれでOK?かしら
H1式=IF(G1="","",IFERROR(INDEX(A:A,TEXT(MAX(INDEX((B:E=G1)*ROW(A:A),0)),"0;;")),""))
（あみな） 2022/07/28(木) 18:45
=IF(A12="","",CONCAT(IF(B1:Z10=A12,A1:A10,"")))
Ctrl + Shift + Enterで確定
でいいと思います。
CSE確定が面倒なら数式を名前定義して使えばいいです。
（d-q-t-p） 2022/07/29(金) 08:55
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.