Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
INDEX(MATCH にINDIRECT
投稿日時: 20/06/18 08:19:46
投稿者: 純

よろしくお願いいたします。
 
Sheet2に以下の表があります。
 

	A	B	C	D	E	F	G
3	担当者コード	担当者名	店舗コード	系列	ID	来店回数	式
4							
							

 
式には、=IF(C4="","",C4&"|"&COUNTIFS(C$2:C3,C4)+1)で同じ店舗をカウントしています。
 
Sheet3で、指定した店舗のIDを持ってくるよう以下の式を入れてます。
 
B3セルに店舗コードを入れるとSheet2のIDコードを順に表示するよう
 
=IFERROR(INDEX(Sheet2!E:E,MATCH("'"&$B$3&"|"&ROW(1:1),Sheet2!G:G,0)),"")
 
教えていただきたいのは、Sheet2のE:EやG:G指定している所をINDIRECTで切り替えたいのですが
 
Sheet2のA〜Gは併用者、H〜Nは○○のみ、O〜Uは△△のみ、V〜ABはどちらも未使用
 
Sheet3のC4セルでプルダウンで切り替えたいのですがどのような式にしたらいいでしょうか
 
どうぞよろしくお願いいたします。
 

回答
投稿日時: 20/06/18 10:18:09
投稿者: WinArrow
投稿者のウェブサイトに移動

説明文の中の
>教えていただきたいのは
までは、理解できそうですが、
それ以降は、意味不明・・・・理解できそうにありません。
 
もう少し詳細、具体例で説明できませんか?

投稿日時: 20/06/18 10:32:29
投稿者: 純

WinArrow さま
 
ありがとうございます。
 
今、(Sheet2!E:E や Sheet2!G:G,としている所を A〜G列の併用者という名前を付けた範囲のE列とG列を取得したく
 
Sheet3のC4セルで「〇〇のみ」にプルダウンで切り替えたら
 
H〜Nの「○○のみ」と名前を付けた範囲のL列とN列、
 
C4セルを「△△のみ」と選択したら
 
O〜Uの「△△のみ」と名前を付けた範囲のS列とU列になるように切り替えたいのですが
 
この説明で大丈夫でしょうか
 
引き続きよろしくお願いいたします。

回答
投稿日時: 20/06/18 10:39:56
投稿者: WinArrow
投稿者のウェブサイトに移動

>この説明で大丈夫でしょうか
 
まったくわかりません。
 
例えば
>A〜G列の併用者という名前を付けた範囲
ここのレイアウト定義されている内容は、
どうなっているのですか?
どこにセル範囲があるのでしょうか?

投稿日時: 20/06/18 10:48:01
投稿者: 純

WinArrow さま
 
A〜G列の併用者という名前を付けた範囲は
 
Sheet2のA列からG列で

	A	B	C	D	E	F	G
3	担当者コード	担当者名	店舗コード	系列	ID	来店回数	式
4							

 
この部分です。列指定が出来ないのであればA4:G10815なんですが
 
ただ「○○のみ」の名前を付けた範囲はO列〜U列ですが、列指定でなければO4:U1254
 
で行数は変わります。
 
よろしくお願いいたします。

回答
投稿日時: 20/06/18 10:48:06
投稿者: WinArrow
投稿者のウェブサイトに移動

なんとなくわかってきました
 
>Sheet2!E:E

INDEX(併用者,,5)
に置き換えてみてください。
 
>INDEX(Sheet2!E:E,

INDEX(INDEX(併用者,,5),
 
こんな感じです。

回答
投稿日時: 20/06/18 11:04:13
投稿者: WinArrow
投稿者のウェブサイトに移動

追加レス
 
↑が成功すると
INDIRECTは不要と思いますが・・・

投稿日時: 20/06/18 11:17:52
投稿者: 純

WinArrow さま
 
ありがとうございます。
 
併用者 と字を入れたら結果が出ました。
 
それで切り替えたいので文字の所を「C4」セル(以下の式)に変えたら
 
#REFになりました。
 
=INDEX(INDEX($C$4,,5),MATCH("'"&$B$3&"|"&ROW(1:1),INDEX(INDEX($C$4,,7),0)))
 
あと、この式をズリっと下にコピーしたいのですが、
 
併用者と字を入れて出た結果を
下へコピーしたら3人目から同じIDコードばかりが表示されました。
 
実際はC4セルで切り替えが出来て下へコピーしたいです。
 
何度もすみません。
 
よろしくお願いいたします。
 
 
 

回答
投稿日時: 20/06/18 11:21:22
投稿者: WinArrow
投稿者のウェブサイトに移動

追加のアドバイス
 
(1)INDEX関数の3通りの使い方
➀行と列を指定
 セル範囲の中の行位置と列位置のクロスしたセルの値を参照します。
 =INDEX(セル範囲,3,2)
A行のみ指定
 セル範囲の中の行位置の値を配列で参照します。
  =INDEX(セル範囲,3)
B列のみ指定
 セル範囲の中の列位置の値を配列で参照します。
  =INDEX(セル範囲,,4)
 
(2)INDIRCET関数について
INDIRECT関数は揮発性関数の一つです。
揮発性関数は、その関数が参照してセル以外のセルの値が変化しても実行されます。
揮発性関数を多用すると、不必要な計算が実行されるのため、レスポンスが悪くなります。
揮発性関数で検索すると、他の揮発性関数を知ることができます。
 

回答
投稿日時: 20/06/18 11:25:31
投稿者: WinArrow
投稿者のウェブサイトに移動

併用者のままではダメ?
 
>=INDEX(INDEX($C$4,,5),MATCH("'"&$B$3&"|"&ROW(1:1),INDEX(INDEX($C$4,,7),0)))

なぜ、
$C$4
に変更するのでしょうか?

投稿日時: 20/06/18 11:30:45
投稿者: 純

WinArrow さま
 
何度もすみません。
 
Sheet3で店舗コードを選択すると同時に
併用者、○○のみ愛用者、△△のみ愛用者、どちらも未使用者と切り替えて
お客さまのIDリストを作成したいと思っています。

回答
投稿日時: 20/06/18 11:47:04
投稿者: WinArrow
投稿者のウェブサイトに移動

説明不足ありませんか??
>Sheet2のA〜Gは併用者、H〜Nは○○のみ、O〜Uは△△のみ、V〜ABはどちらも未使用
という名前定義していますが、
 
もともと
Sheet2のH列〜AB列は、
最初の表の説明には、入っていませんよね?
何が入っているんですか?
○○は、そのまま、"○○"で検索してよいのですか?

投稿日時: 20/06/18 12:58:07
投稿者: 純

 WinArrow さま
 
すみません。
 
H〜AVは、A〜Gと同じ項目が3回繰り返されます。
 
○○は実際は○○ではないのですが・・・
 
アイテム名が入ります。対象者の人数がそれぞれ違いそのアイテムの対象者のIDコードが何行かあります。

回答
投稿日時: 20/06/18 13:41:18
投稿者: WinArrow
投稿者のウェブサイトに移動

Sheet3のセルに入れる数式を設営してください。
 
最初は、質問時に説明している
>=IFERROR(INDEX(Sheet2!E:E,MATCH("'"&$B$3&"|"&ROW(1:1),Sheet2!G:G,0
は、Sheet3のどこのセル?
これは"併用者"に入れ替わるのですよね?
 
その次は?
そのつぎは?
その次は?
 
と4ヶ所の数式を掲示しましょう。
手抜きをしていると、あなたの意図が回答者に伝わりませんよ!

 

投稿日時: 20/06/18 13:55:34
投稿者: 純

WinArrow さま
 
>=IFERROR(INDEX(Sheet2!E:E,MATCH("'"&$B$3&"|"&ROW(1:1),Sheet2!G:G,0
は、Sheet3のどこのセル?
 
これはSheet3のB8セルにいれます。
 
これをB9、B10、B11と下にコピーしたいです。
 
Sheet3のC4セルにプルダウンリストが4つ選択できるようになっています。
 
それが、併用者だったり、あるアイテムのみと切り替えれるようにしたいです。
 
今までは上記の式でよかったのですが、C4セルで切替式にしたくてその方法が
INDIRECTなのかなと思ったのですがどのようにしていいのかわかりませんでした。

回答
投稿日時: 20/06/18 15:25:23
投稿者: WinArrow
投稿者のウェブサイトに移動

Sheet3のC4セルには、何が入っているのでしょう?

投稿日時: 20/06/18 15:31:33
投稿者: 純

WinArrow さま
 
〇とか△で表現して申し訳ありませんが
以下の4つがC4セルのプルダウンで選べる項目です。
 
どちらも未使用者
△△のみ愛用者
〇〇のみ愛用者
併用者
 

回答
投稿日時: 20/06/18 15:47:38
投稿者: んなっと

=IFERROR(INDEX(INDIRECT($C$4),MATCH("'"&$B$3&"|"&ROW(1:1),INDEX(INDIRECT($C$4),,7),0),5),"")
 

投稿日時: 20/06/18 16:02:49
投稿者: 純

んなっと さま
 
いつもありがとうございます。
 
出来ました!
 
初めにイメージしていた式に近いものです。
 
),,7),0),5),"")  が全く知らない方法ですが
何列目かを指定しているという事が
このように作っていただくとなんとなく理解できます。
 
WinArrow さま
 
根気にお付き合いいただき本当にありがとうございました。
 
無事解決出来ました。