[[20230327083655]] 『データの入力規制にて』(綾) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『データの入力規制にて』(綾)

共有されているブックにてデータの入力規制しているセルがあります。

E14セルに=INDIRECT(D14)

この時にE14セルのリストを選択すると、
共有ブックに入力された数式やデータ テーブルの配列は、コピーまたは移動できません。
となります。

ちなみに参照先のリストの計算式は以下の様になっています。
{=IFERROR(INDEX($B$11:$B$80,SMALL(IF($Y$11:$Y$80>=$AI$1,ROW($1:$70)),ROW(AI1!))),"")}

どこがいけないんでしょうか?教えてください。

< 使用 Excel:Excel2010、使用 OS:Windows10 >


 過去ログから同様の事例っぽいのが見つかりました。
[[20200326155308]] 『ブックの共有時のデータの入力規制にて』(未来)

 抜粋
 |「入力規則の元の値」の指定範囲に配列数式があるとだめ
 |(中略)
 | 要は配列数式の結果を通常数式で参照して、
 | 入力規則の元の値には通常数式が入った範囲を指定するという方法です。

 現状だと、表の形やデータ等が分かりかねますので、曖昧な返事しかできません。
 自力解決が難しいようでしたら、
[[20110209184943]] 『[談]シートレイアウトの投稿どうしてますか?』(momo)
 こちらのVBAを使って、レイアウトの提示いただくか、
 手入力で表の形を表現いただければお手伝いできそうです。

(稲葉) 2023/03/27(月) 10:51:56


お返事ありがとうございます。

配列数式に問題があるみたいです。

この数式を配列数式から普通の数式に変更できますか?
(綾) 2023/03/27(月) 11:07:46


 繰り返します。
 > 現状だと、表の形やデータ等が分かりかねますので、曖昧な返事しかできません。
 >自力解決が難しいようでしたら、
[[20110209184943]] 『[談]シートレイアウトの投稿どうしてますか?』(momo)
 >こちらのVBAを使って、レイアウトの提示いただくか、
 >手入力で表の形を表現いただければお手伝いできそうです。

 aggregate関数に置換できそうですが、データが分からないので確約できないです。
(稲葉) 2023/03/27(月) 11:11:10

ありがとうございます。
B11:B80に氏名が入力されています。
C11:C80に日付が入力されている場合があります。
Y11:Y80に=EDATE(C11,3)-IF(DAY(C11)=DAY(EDATE(C11,3)),1,0)の計算式があります。
AI1セルに日付があり、Y11:Y80の日付がAI1の日付以降の場合、B列の同じ行の一覧をAI2セルより下に求めている形になります。

例として
B11セルにのび太、C11セルに2022/12/27、するとY11セルは2023/3/26、AI1セルが2023/3/1のAI2セルにのび太と来るようにしています。
B12セルにジャイアン、C12セルに2020/9/3、するとY12セルは2020/12/2、AI1セルが2023/3/1なのでAI3セルには反映されないです。
B13セルにスネ夫、C13セルに2023/2/25、するとY13セルは2023/5/24、AI1セルが2023/3/1なのでAI3セルにスネ夫と来るようにしています。

こんな感じでわかりますか?
よろしくお願いいたします。

(綾) 2023/03/27(月) 11:57:37


 こうなればいいんですよね?
 作業列を使えば配列はいらなくなると思います。
 Z11=IF(Y11>=$AI$1,ROW(),"")
 下方向にコピー

 AI2=IFERROR(INDEX(B:B,SMALL(Z:Z,ROW(A1))),"")
 下方向にコピー

 >E14セルに=INDIRECT(D14)
 この説明が理解できてないですが、配列は使ってないです。

     |[B]       |[C]       |[Y]      |[Z]|[AI]    
 [1] |          |          |         |   |2023/3/1
 [2] |          |          |         |   |のびた  
 [3] |          |          |         |   |すねお  
 [4] |          |          |         |   |        
 [5] |          |          |         |   |        
 [6] |          |          |         |   |        
 [7] |          |          |         |   |        
 [8] |          |          |         |   |        
 [9] |          |          |         |   |        
 [10]|氏名      |日付      |         |   |        
 [11]|のびた    |2022/12/27|2023/3/26| 11|        
 [12]|じゃいあん|2020/9/3  |2020/12/2|   |        
 [13]|すねお    |2023/2/25 |2023/5/24| 13|        
(稲葉) 2023/03/27(月) 12:19:03

返信ありがとうございます。

>E14セルに=INDIRECT(D14)
>この説明が理解できてないですが、配列は使ってないです。

D14セルの入力値によってE14セルの入力するリストを変更する形になります。
そのリストがAIセルの何行目からかを名前にて指定しています。

やはり作業列を作成して行った方がよろしいですか?
全体的に計算式がかなりありますので少しでもファイルを軽くしたいです。

よろしくお願いいたします。
(綾) 2023/03/27(月) 13:02:49


 配列のほうが重いですよ
 =IFERROR(INDEX($B$11:$B$80,AGGREGATE(15,6,ROW($1:$70)/($AI$1<=$Y$11:$Y$80),ROW(A1))),"")
 ↑も配列数式にしていませんが、共有ファイルで使えるかどうかは確かめてないです。

(稲葉) 2023/03/27(月) 13:06:23


お返事ありがとうございます。
=IFERROR(INDEX($B$11:$B$80,AGGREGATE(15,6,ROW($1:$70)/($AI$1<=$Y$11:$Y$80),ROW(A1))),"")
これで上手くいきそうです。

これを見よう見まねで一部変更したのですが下記の計算式ですが、
=IFERROR(INDEX($B$11:$B$80,AGGREGATE(15,6,ROW($1:$70)/($AI$1>$Y$11:$Y$80),ROW(A1))),"")
やりたい事は、
AI2:AI71まで上記の計算式を入力し
AI72:AI142までは下記の計算式を入力しようと思います。
C列の日付によってB列の名前を上と下に振り分けたいです。

問題なのが下の振り分けにてB列とC列が空白の場合、AI列に0と表示されてしまいます。
ここを何も表示しないようにするにはどうしたらいいですか?

よろしくお願いいたします。

(綾) 2023/03/27(月) 14:54:16


 =IFERROR(INDEX($B$11:$B$80,AGGREGATE(15,6,ROW($1:$70)/($AI$1<=$Y$11:$Y$80),ROW(A1)))&"","")
(稲葉) 2023/03/27(月) 15:08:00

ありがとうございます。
無事にできました。
(綾) 2023/03/27(月) 16:56:30

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.