教えて! goo のコンテンツに対する取り組みについて

いつもお世話になります。
OSは、Win10 エクセルは、2016 です。

sheet"一覧"に参照図のように全角カタカナのア~ンがあります。

例えば
別sheet"B2" = ガ 021
別sheet"C2" = ハ 081

上記の、021 081 を求める数式を御教授いただけませせんか。

「ある範囲の中で指定の文字列が該当する番号」の質問画像

質問者からの補足コメント

  • うれしい

    早速のご回答を有難うございます。

    "検索文字"のところで試させていただきました。
    中々うまくゆきません。
    誠に申し訳ありませんが再指導いただけませんでしょうか。


    大変失礼いたしました。
    ご不明の
    検索する文字がどこにあって、数式をどのシートに記入するのかはっきりしませんので

    ガ = 顧客名簿Sheet1)B2 数式の入力セル = 顧客名簿Sheet1)B3
    ハ = 顧客名簿Sheet1)C2 数式の入力セル = 顧客名簿Sheet1)C3

    因みに ア~ンのカタカナの一覧は

     一覧(Sheetc2) です。

    No.1の回答に寄せられた補足コメントです。 補足日時:2021/12/04 17:04
gooドクター

A 回答 (2件)

No1です。



>中々うまくゆきません。
式を入力しているのが、別のシートのようですので、式中のセルやセル範囲の参照には全てシート名を付す必要があります。
そのあたりではないでしょうか?

こちらで再確認したところ、SUMPRODUCT関数だと、どうも挙動がおかしなところがありそうなので、もう少し厳密に検索できるようにロジックを変えてみました。

>因みに ア~ンのカタカナの一覧は
> 一覧(Sheetc2) です。
えぇ~っと・・
シート名は「一覧」でいいんですよね?
もしも、シート名が「一覧(Sheet2)」の場合は、以下の式中の「一覧」を全て「一覧(Sheet2)」に変えてください。

数式の入力セルB3に以下の式を入力すれば、変換できるものと思います。
=IF(COUNTIF(一覧!$B$2:$F$16,B2),INDEX(一覧!$A$2:$A$16,AGGREGATE(15,6,ROW(一覧!$B$2:$F$16)/(一覧!$B$2:$F$16=B2),1)-1)&INDEX(一覧!$B$1:$F$1,AGGREGATE(15,6,COLUMN(一覧!$B$2:$F$16)/(一覧!$B$2:$F$16=B2),1)-1),"該当なし")


もしも、式が長いようであれば、「一覧シートの$B$2:$F$16」に名前を付けておくことで、多少は短くすることができます。
仮に、上記の範囲に「表」という名前を付けたとすれば、B3の式は
=IF(COUNTIF(表,B2),INDEX(OFFSET(表,0,-1),AGGREGATE(15,6,ROW(表)/(表=B2),1)-1,1)&INDEX(OFFSET(表,-1,0),1,AGGREGATE(15,6,COLUMN(表)/(表=B2),1)-1),"該当なし")
のようにすることが可能です。
    • good
    • 0
この回答へのお礼

早速の再指導を誠にありがとうございます。

うまくできました。

お礼日時:2021/12/04 18:34

こんにちは



検索する文字がどこにあって、数式をどのシートに記入するのかはっきりしませんので、以下の仮定をしました。

数式は表のあるシートに記入するものと仮定し、検索対象の文字は式中では「検索文字」として表してあります。
(式中に「検索文字」は2か所ありますので、適宜置き換えてください)

=IFERROR(INDEX($A$1:$A$16,SUMPRODUCT(($B$2:$F$16=検索文字)*ROW($B$2:$F$16)))&INDEX($A$1:$F$1,SUMPRODUCT(($B$2:$F$16=検索文字)*COLUMN($B$2:$F$16))),"該当なし")

※ 表内には同じ文字の重複は無いものと仮定しています。
 同じ文字が存在する場合は、上式では正しい結果が得られません。
 (もっと長い式にすれば、同じ文字があっても可能ですけれど・・・)
この回答への補足あり
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング