エクセル 入力に関する関数の使い方

質問

E8の文字が　
縦6列　横10行の表の中で　ある文字に該当したら　右端の文字を
E10に　入力する方法を教えてください。
下の例ですと　「あ」に該当したら　「－」を入力する

　あ　い　う　え　お　Ａ　－
　か　き　く　け　こ　Ｂ　＋
　　　　　～

　　　　　～
　は　ひ　ふ　へ　ほ　Ｊ　＋

回答No.7

kagakusuki

今仮に、

甲寅甲辰甲午甲申甲戌甲子+
乙卯乙巳乙未乙酉乙亥乙丑-
丙辰丙午丙申丙戌丙子丙寅+
丁巳丁未丁酉丁亥丁丑丁卯-
戊午戊申戊戌戊子戊寅戊辰+
己未己酉己亥己丑己卯己巳-
庚申庚戌庚子庚寅庚辰庚午+
辛酉辛亥辛丑辛卯辛巳辛未-
壬戌壬子壬寅壬辰壬午壬申+
癸亥癸丑癸卯癸巳癸未癸酉-

のような表がセル範囲がG2:M11のセル範囲に設けられているものとした場合、E8の文字が「癸亥」ならE10に「-」を表示させる関数の一例は次の様なものとなります。

=IF(E8="","",IFERROR(INDEX($M:$M,9999999-SUMPRODUCT(MAX(($G$2:$L$11=E8)*(9999999-ROW($G$2:$L$11))))),"(該当無し)"))

　尚、もしE8セルと同じ値が入力されているセルが必ず1つだけしか存在しない事が保障されている場合には、次の様な関数でもOKです。

=IF($E$8="","",IFERROR(INDEX($M:$M,SUMPRODUCT(($G$2:$L$11=$E$8)*ROW($G$2:$L$11))),"(該当無し)"))

回答No.6

imogasi

エクセルの質問に使う、使用用語が無茶で、質問者はエクセル経験は少ないと思うが、なぜこういう（関数でやろうとすると、むつかしい）質問が出てくるのか不思議だ。
ーー
関数での回答はすでに出ている。
だから下記のような回答をしても無駄かもしれないが、将来にでも、質問者でなく、ＷＥＢ照会で他の人が見てくれるかもしれないと思って書いてみる。
ーー
ユーザー定義の関数を作る。
標準モジュールに
Function sagasu(x)
Z = x.Value
Set y = Worksheets("Sheet2").Range("$a$1:$F$10").Find(Z)
v = y.Row
w = Cells(v, 200).End(xlToLeft).Column
sagasu = Cells(v, w)
End Function
検索語句でセル範囲を探し行を求める。
その行のデータのある最右端の列を求め、その行、列のセルのデータを持ってきている。
そういう関数を作ったもの。Ｆｉｎｄというメソッドを使っているだけ。
ＶＢＡと関数の差が出ていると思う。
ーー
例データ
Ｓｈｅｅｔ２で
Ａ１：Ｄ１０に
甲寅 甲辰 甲午 甲申 甲戌 甲子 +
乙卯 乙巳 乙未 乙酉 乙亥 乙丑 -
丙辰 丙午 丙申 丙戌 丙子 丙寅 +
丁巳 丁未 丁酉 丁亥 丁丑 丁卯 -
戊午 戊申 戊戌 戊子 戊寅 戊辰 +
己未 己酉 己亥 己丑 己卯 己巳 -
庚申 庚戌 庚子 庚寅 庚辰 庚午 +
辛酉 辛亥 辛丑 辛卯 辛巳 辛未 -
壬戌 壬子 壬寅 壬辰 壬午 壬申 +
癸亥 癸丑 癸卯 癸巳 癸未 癸酉 -
（Ａ１：Ａ１０にコピペして
データー区切り位置で２文字づつ指定して、各セルに散らばらす、と作れる。）
ーー
例として
Ｓｈｅｅｔ２で
Ｃ１４に「　戊子　」　と入れる。
Ｄ１５に　=sagasu(C14)　と関数を入れる。
結果は　Ｄ１５は　「　+　」

回答No.5

bunjii

>　ROW(A1:A10),0,0))),"")は　必要なのですか？　貼り付け画像にはありませんが。
勘違いしていませんか？
=IF(COUNTIF(G1:L10,E8),INDEX(M1:M10,MAX(INDEX((G1:L10=E8)*ROW(A1:A10),0,0))),"") の一部である ROW(A1:A10),0,0))),"") を省いたら数式自体が誤りになります。
MAX関数の引数にINDEX関数を使っている理由は数式の確定時にEnterキーのみで配列値をMAX関数へ渡すためです。
下記の3つの数式は等価ですが1番目のときは数式を確定するときCtrl+Shift+Enterの打鍵が必要です。
=MAX((G1:L10=E8)*ROW(A1:A10))
=MAX(INDEX((G1:L10=E8)*ROW(A1:A10),0,0))
=SUMPRODUCT((G1:L10=E8)*ROW(A1:A10))
何れもM1:M10から目的の文字を抽出するための行番号を算出しています。
回答No.1のようにOFFSET関数で目的の値を引き出す方法もあります。

回答No.4

bunjii

回答No.3の貼付画像を差し替えます。
尚、「右端の文字をE10に　入力する」と言う表現も正しくありません。
「右端セルの文字を返す数式をE10に設定する」と言うことです。
「入力する」と言う表現は「キーボードで数値や文字列、数式等を打ち込む」ことを言います。計算結果をセルに代入することではありません。

回答No.3

bunjii

>縦6列　横10行の表の中で　ある文字に該当したら　右端の文字をE10に　入力する方法を教えてください。
紛らわしい文言ですね。
「縦6列　横10行の表」とは「6列×10行の表」で良いのですよね？
A1:F10に作成すると数式を設定するE10セルが重なるので、他の位置へ6列×10行の表を作成します。（G1:M10へ作成）
E10=IF(COUNTIF(G1:L10,E8),INDEX(M1:M10,MAX(INDEX((G1:L10=E8)*ROW(A1:A10),0,0))),"")
貼付画像はExcel 2013で検証した結果ですが、他のバージョンでも再現できると思います。

補足

ROW(A1:A10),0,0))),"")は　必要なのですか？　貼り付け画像にはありませんが。

回答No.2

msMike

》 縦6列　横10行の表
Excel的には、それ奇妙に受け取られます。
縦６行、横10列が普通です。

回答No.1

mt2015

添付の図のような事でしょうか？
E10には以下の式を入れています。
=OFFSET(A1,SUMPRODUCT(ROW(A1:J6)*(A1:J6=E8))-1,9)

補足

甲寅 甲辰 甲午 甲申 甲戌 甲子 +
乙卯 乙巳 乙未 乙酉 乙亥 乙丑 -
丙辰 丙午 丙申 丙戌 丙子 丙寅 +
丁巳 丁未 丁酉 丁亥 丁丑 丁卯 -
戊午 戊申 戊戌 戊子 戊寅 戊辰 +
己未 己酉 己亥 己丑 己卯 己巳 -
庚申 庚戌 庚子 庚寅 庚辰 庚午 +
辛酉 辛亥 辛丑 辛卯 辛巳 辛未 -
壬戌 壬子 壬寅 壬辰 壬午 壬申 +
癸亥 癸丑 癸卯 癸巳 癸未 癸酉 -

のような表です。

E8の文字が「癸亥」なら　E10に「-」を入力する方法です。　
（ 縦6行　横10列の表の中で　ある文字に該当したら　右端の文字を
E10に　入力する方法を教えてください。）