[[20160624163401]]　『下のセルがどのセルを参照しているか表示する』（かげつ）　ページの最後に飛ぶ

『下のセルがどのセルを参照しているか表示する』（かげつ）

sheet1

        a　　　　　　　　 b
1 　　 ☆　　　　　　　  ★
2　　 202(=Sheet2!b2)　 303(=Sheet2!c3)

sheet2
　　　　 a　　　　　　b　　　　　　c　　　　　　d
1　　　101　　　　　102　　　　　103　　　　　104
2　　　201　　　　　202　　　　　203　　　　　204
3　　　301　　　　　302　　　　　303　　　　　304
4　　　401　　　　　402　　　　　403　　　　　404

☆に[Sheet2の2行目]、★に[Sheet2の3行目]と関数で表示させる方法ってありますか？

A1に[Sheet B2]といれたらA2に202と表示させる方が楽なのはわかりますが、既存のものをいじるのであえて面倒なやり方を探してます。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

A1="Sheet2の" & MAX(SUMPRODUCT((Sheet2!A:A=A2)*1*ROW(A:A)),SUMPRODUCT((Sheet2!B:B=A2)*1*ROW(B:B)),SUMPRODUCT((Sheet2!C:C=A2)*1*ROW(C:C)),SUMPRODUCT((Sheet2!D:D=A2)*1*ROW(D:D))) & "行目"
（mm） 2016/06/24(金) 18:09

おぉ、すごい！
ありがとうございます。

整理しきれてないところもありますが、調べつつ、既存書類のスタイルに合わせてみます！

ありがとうございました
（かげつ） 2016/06/24(金) 18:25

 何行目かだけわかればいいんだったら、
 200番台は2行目、300番台は3行目･･･てことじゃないんですよね？

 A1 =SUMPRODUCT((Sheet2!$A$1:$D$4=A2)*ROW(A1:A4))

 右にコピー

 これでできませんか？
（笑） 2016/06/24(金) 18:47

そちらでも出来ました。
ありがとうございます。

既存のデータに入れて、試しました。

SUMPRODUCTで行数が表示できるのは分かりましたが、
これだと、どのシートかは分からないんですね

何か別のと組み合わせればいいのでしょうか
（かげつ） 2016/06/25(土) 02:27

 > これだと、どのシートかは分からないんですね

 どのシートも何も、Sheet2しか見てないんだから Sheet2 でしょ？
　
　
 > ☆に[Sheet2の2行目]、★に[Sheet2の3行目]と関数で表示させる方法

 表示を ↑ にしたいってこと？

 A1 ="Sheet2の"&SUMPRODUCT((Sheet2!$A$1:$D$4=A2)*ROW(A1:A4))&"行目"

 ただ、これだと検索値がSheet2になかったら「Sheet2の0行目」となってしまうので、
 その場合は非表示なら（本当にシート名が Sheet2 だとして）

 A1 =TEXT(SUMPRODUCT((Sheet2!$A$1:$D$4=A2)*ROW(A1:A4)),"""Sheet2""の0行目;;")

 または表示形式でもいいのであれば、
 数式は　=SUMPRODUCT((Sheet2!$A$1:$D$4=A2)*ROW(A1:A4))　のままで

 表示形式～ユーザー定義　"Sheet2の"0"行目";;

 参考まで。
（笑） 2016/06/25(土) 22:04

追記の返事遅れてしまいました（汗

将来的に参照先をSheet3、Sheet4と増やしたとき、どう対応でしようかな、と思った次第です。

今回の目的は達成したので、とりあえず教えて頂いたもので対応してみます。
ありがとうございました
（かげつ） 2016/06/28(火) 09:18

 対象がSheet2、Sheet3の場合で両方に同じ値がない場合。

 =INDEX({"Sheet2","Sheet3"},MATCH(1,INDEX(COUNTIF(INDIRECT({"Sheet2","Sheet3"}&"!A1:D4"),A2),0),0))&"の"&
  SUMPRODUCT(((Sheet2!$A$1:$D$4=A2)+(Sheet3!$A$1:$D$4=A2))*ROW(A1:A4))&"行目"

 =INDEX({"Sheet2","Sheet3"},MATCH(1,INDEX(COUNTIF(INDIRECT({"Sheet2","Sheet3"}&"!A1:D4"),A2),0),0))
 でシート名
 =SUMPRODUCT(((Sheet2!$A$1:$D$4=A2)+(Sheet3!$A$1:$D$4=A2))*ROW(A1:A4))&"行目"
 で行数を求めている。

 なお、mmさんの式の４か所の*1は必要ない。
（ねむねむ） 2016/06/28(火) 09:50

 少し方向性の違うもの。
 ただし2016では使えないかもしれないが。
 （2016では使えても先のバージョンで使えなくなる可能性あり）

 また、ブックはXLSMのマクロありの形式で保存する必要あり。

 カーソルをA1セルに置いた状態で数式-名前の定義を開き名前はすきな名前で（例としてSUUSIKI）参照範囲を
 =GET.CELL(6,Sheet1!A2)
 と指定する。

 これでセルに
 =SUUSIKI
 と入力すると下のセルの式をテキストとして表示する。

（ねむねむ） 2016/06/28(火) 10:10

古いの掘り返して申し訳ありません。

コレ(SUMPRODUCTの関数)ってシート2の数値がその度、変わるモノだったら成り立たないのですね。
(シート2に同じ数値があったら、カウントしてしまうので)

シート2でリンゴ、バナナの数を計算して、
シート1でリンゴとバナナをリンクして、くだもの
として集計するイメージだったのですが……

分かりづらくてすみませんでした

（かげつ） 2016/07/15(金) 15:26

 Excel2013ならFORMULATEXTって関数使えるんでないの？
 ※持ってないので試してないけど。
（GobGob） 2016/07/15(金) 16:15

GobGobさん

これです！
その「FORMULATEXT関数」に「セル内から数字だけを抽出する方法(他所で調べました)」と組み合わせて、
目的通りになりました！

こんどは関数の仕組み理解して納得したので間違いなさそうです（笑）
ありがとうございました！
（かげつ） 2016/07/19(火) 10:19

コメント返信：

[ 一覧(最新更新順) ]

[[20160624163401]] 『下のセルがどのセルを参照しているか表示する』（かげつ） ページの最後に飛ぶ

[[20160624163401]]　『下のセルがどのセルを参照しているか表示する』（かげつ）　ページの最後に飛ぶ