[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『下のセルがどのセルを参照しているか表示する』(かげつ)
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 >
整理しきれてないところもありますが、調べつつ、既存書類のスタイルに合わせてみます!
ありがとうございました
(かげつ) 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さんの式の4か所の*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
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.