[[20070719153455]] 『入力規則リストで選択した人名をリストから消去し』(みず64) >>BOT

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

 

『入力規則リストで選択した人名をリストから消去したい』(みず64)
  
全く同一質問が[[20020530162213]]に存在しました。
 (みやほりん)さんの回答に関してご教示をお願い致します。
 A1基準では、回答の数式で満足した結果が得られました。しかし、1行目に行を挿入すると入力規則用リストはデータが減少します。挿入した行数の2倍のデータ数が減少します。
 さらに、任意の行に適用すると全く駄目でした。#REFエラー表示になりました。
 いろいろ学習(トライ)してみた結果、INDEX関数の行番号を取得するSMALL関数において順位を与えるROW関数式の範囲指定に問題があると推定しました。それ以上はなんぼ考えても正解を導き出すことが出来ません。お手上げです。EELP!です。
 数式は、{=TEXT(INDEX(B$41:B$100,SMALL(IF(COUNTIF(A$41:A$100,B$41:B$100)=0,ROW(A$40:A$99)),ROW(A40))),"#")}を入力しました。
 ROW(A40)の箇所を⇒ROW(A40)-39に変更すると、#REFエラー表示は消えて空白スペースになりました。

 ROW(A$40:A$99) --> ROW(A$1:A$60)
 とされたらどうなりますか?
 (ROUGE)

 >1行目に行を挿入すると
そのような使い方は想定していません。また、置き換えると次のような書き方となります。
 
=TEXT(INDEX([Range-All],SMALL(IF(COUNTIF([Range-Input],[Range-All])=0,ROW([Number])),ROW(A1))),"#")
 
[Range-All]   全リストが入力された範囲 
[Range-Input] 入力規則でInputする範囲
[Number]      一行目から始まる[Range-All]と同じ行数の範囲
 
[Range-All] [Range-Input] は任意の行から始まる行範囲でケッコウですが、
[Number]の部分は必ず一行目から始まるセル範囲である必要があります。
 
[Range-Input]に入力されていない[Range-All]のデータには[Number]により
1から始まる連番が、[Range-Input]に入力されている[Range-All]のデータに
はFalseが割り当てられます。その中のRow(An)番目に小さい数が[Range-All]
の中から表示すべきデータになります。INDEX関数は[Range-All]の何番目か、
を指定することで表示すべきデータを特定する関数です。
B$41:B$100・・・60行のデータの中から検索するのに、何番目か、を指定す
る部分でROW(A$40:A$99)・・・40〜99と検索範囲よりも大きい行番号が返る
指定となります。また、SMALL関数の「何番目に小さい数」にあたる引数に
ROW(A40)を与えた場合、「40番目に小さい値」となるので、[Range-All]の
範囲の行数よりも大きい数値が返ってしまうことになる場合(60行分のリス
トなのに80などが返る場合がある)、#REF!エラーとなります。
 
正解は下記でしょうか。
=TEXT(INDEX(B$41:B$100,SMALL(IF(COUNTIF(A$41:A$100,B$41:B$100)=0,ROW(A$1:A$60)),ROW(A1))),"#")
 
数式バーで
IF(COUNTIF(A$41:A$100,B$41:B$100)=0,ROW(A$1:A$60))
の部分だけマウスで選択反転させてF9キーを押すと、計算している配列を
見ることが出来ますので、参考にしてください。
(少し範囲を小さくしたものでやった方がわかりやすいのですが)
(みやほりん)(-_∂)b

 [Range-Input]、[Range-All]および関数式を入力する[Range-List]を任意の行・列に
 配置することができ、目的・目標とおりのドキュメントを完成することができました。
 ありがとうございました。
 F9キーを使って配列の内部を見て、なんだか配列のカラクリを覗き見たようです。
 手品のタネ明かしをしたような気分です。
 特にROW([Number])の箇所は、{1;2;3;4;---略----}で置き換えられるとは……。
 吃驚仰天です。配列への理解が一歩・二歩前進した思いです。
 (みやほりん)さん ありがとうございました。(みず64)

コメント返信:

[ 一覧(最新更新順) ]


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