FC2ブログ

少し、面白い問題 重複しないランダムな値

問題文

 画像のような配置図

 201905311010.png

 薄い緑色で塗りつぶされたセルに、名前を表示させる
 その名前は、以下のように五十音順に入力されている
 ※ K列に、通し番号 L列に、「名前」 セル範囲は、K14:L53

  201905311011.png

条件 1

 A1 が、空欄の場合は、
  B3 から順番に、H12まで、あいうえお順


  201905311012.png

条件 2

 A1 に、何か入力されたら、
  ランダムな名前を 入力する


  ここで必要なことは、ランダムで、なおかつ、重複しないもの
  名前= L13:L53 を、ランダムに、重複しないように選択する
  そのために、隣のセルにRAND 関数を入力する
  RAND 関数
   0 以上で 1 より 小さい実数の乱数を 返します

  201905311013.png

  さらに、N 列に、今入力した関数で表示される乱数に順位を付ける
  N14 へ、入力する関数は、順位を求める RANK 関数
  =RANK(M14,$M$14:$M$53)

  201905311014.png

  この関数で、求められた数値は、
  「名前」を 「 1 」 から 「 40 」 の 数値で 区別した

あとは、
 どうやって「名前」を求めるか?

最初の「条件 1」の場合は
 セル B3 に入力する数式は、「 =L14 」
なので、
「A1 セルが、空欄の場合」 これは、 I F 関数を使用する
 =IF($A$1="",L14,

  これから、「名前」を 表示するには、L14~L53 を 求めればよい
  それに、ランダムの数値を参照する
  N列で、求めたのは、 1~40 なので、
  N 列の数値 足す 「13」のL列のセル番地
  セル参照を文字列で表示できる関数
  INDIRECT 関数を 使用する
  =INDIRECT("L"&N14+13) 
  この数式を 入力した場合、表示されるのは
  N14 セルに、表示されるランダムな(1-40)の数値足す「13」
  L列の L14~L53のセルのいずれかの「名前」

結 局
  
  B3 セルに入力する関数は
  =IF($A$1="",L14,INDIRECT("L"&N14+13))
  下へ オートフィル

  D3 セルに入力する関数は
  =IF($A$1="",L24,INDIRECT("L"&N24+13))

  F3 セルに入力する関数は
  =IF($A$1="",L34,INDIRECT("L"&N34+13))

  H3 セルに入力する関数は
  =IF($A$1="",L44,INDIRECT("L"&N44+13))

. 
スポンサーサイト



複数ある検索データを取り出す 5 作業列なし関数

作業列を使わない
   関数で求める方法


 条件と一致する行が、何行目か 計算
 求めた行数の小さい方から
 順番に データを取り出す
 これを、一つの関数内で設定する


使用するのは、今までと同じ

  201905280011.png
 Sheet2 は、以下のような状態

  201905311001.png

  Sheet2 の A5 セルに
 =IF(COUNTIF(Sheet1!$D$3:$D$47,$A$2)<ROW(A1),"",INDEX(Sheet1!B$3:B$47,SMALL(IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45)),ROW(A1))))
 注意:ネットにアップするために、関数内の「<」を全角にしています、本来は半角です
数式欄に入力が終わったら
 [Ctrl]+[Shift]+[Enter]キーを 押す
  ※ 関数内が、配列なので、 「配列数式
 {=IF(COUNTIF(Sheet1!$D$3:$D$47,$A$2)<ROW(A1),"",INDEX(Sheet1!B$3:B$47,SMALL(IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45)),ROW(A1))))}
 注意:ネットにアップするために、関数内の「<」を全角にしています、本来は半角です


I F 関数
 「 論理式 」
  COUNTIF(Sheet1!$D$3:$D$47,$A$2)<ROW(A1)
  注意:ネットにアップするために、関数内の「<」を全角にしています、本来は半角です
 「 真の場合 」
  ""
 「 偽の場合 」
  INDEX(Sheet1!B$3:B$47,SMALL(IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45)),ROW(A1)))

「 論理式 」

 COUNTIF(Sheet1!$D$3:$D$47,$A$2)<ROW(A1)
 Sheet1 の D列=「性別」の列に、
 選択した性別「男」の数を COUNTIF 関数で求め
 関数を入力したセルの数と比較する
 データが増える=行数が増える
 最初のセルに ROW(A1) なので、データの数と同じになる
 つまり、行数が、データの数より増えた時点で、TRUE ( 真 )

 「 真の場合 」
 
 行数が、データの数を超えたら、空欄=""

 「 偽の場合 」

 INDEX(Sheet1!B$3:B$47,SMALL(IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45)),ROW(A1)))

 INDEX 関数

 配列
  Sheet1!B$3:B$47
 行番号
  SMALL(IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45)),ROW(A1))

配列
求める値は、一番左の列「受験番号」のB列
設定する数式を 右へオートフィルして、別の列のデータを求めるので
列は、固定しない

行番号
Small 関数
配列
 IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45))
順位
 ROW(A1)

配列は、D列が、「男」の場合 行番号を求め
順位は、関数を設定したセル上から順番に求める

A5 セルに、入力し、配列数式に決定

 201905311002.png

右へ オートフィルする
 
 201905311003.png

下へも オートフィル
 ※ 予想データ数より すこし多めに オートフィルする

 201905311004.png

結果

 201905311005.png

A2 には、リストを設定してあるので、「女」に変更する

 201905311006.png

 201905311007.png

ここでは、すべてのデータを表示しましたが、
「 氏名 」だけを抽出したい場合
 {=IF(COUNTIF(Sheet1!$D$3:$D$47,$A$2)ROW(B1),"",INDEX(Sheet1!C$3:C$47,SMALL(IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45)),ROW(B1))))}
 注意 : ネットにアップするために、関数内の「<」を全角にしています、本来は半角です
  コピーして使用する場合は、注意してください


INDEX 関数の「範囲」を 目的の「 氏名 」のセル範囲にするだけで良い

201905311009.png

 201905311008.png


使用している セル ・セル範囲
 1. D3:D47 = 検索対象のセル範囲
 2. ROW(A1) これは、2回とも データを 1から確認する方法
    A1でも B1 でも C1 行数が「 1 」 ならば 構わない
 3. INDEX 関数の範囲 求めるデータの列(セル範囲)
 4. Sheet2!$A$2 は、検索の値
 5. ROW(Sheet1!$B$1:$B$45) データと同じ幅を持つセル範囲
   列は、どの列でも構わない、行が、「 1 」から 必ず、データの量と同じ行数にする

変更するには、
以上のことを 考慮し 数式を変更し
最後に、必ず、配列数式に 設定する
[Ctrl]+[Shift]+[Enter]キーを 押す

. 

複数ある検索データを取り出す 4 ちょっと変わった方法

複数ある検索データを取り出す 
ここに、入れようか迷ったのですが
私が、面白いって、思ってしまい
どうしても忘れないうちに、書いておこうと思い
無理やり、ここへ入れました

ちょっと前に、書いた 新しい関数の TEXTJOIN 関数を使った方法

使用するのは、今までと同じ一覧表
条件も同じ「 男 」のデータ

自分用と思ってください
ややこしいです

Sheet2 は、以下のようにする

 201905280027.png

A5 セルに、以下の数式
=TEXTJOIN(",",1,IF($A$2=Sheet1!$D$3:$D$47,Sheet1!B$3:B$47,""))
[Crtl]+[Shift]+[Enter]キーで、確定
配列数式です
{=TEXTJOIN(",",1,IF($A$2=Sheet1!$D$3:$D$47,Sheet1!B$3:B$47,""))}

201905280028.png

こうなってから、右へオートフィル

 201905280029.png

そのまま、コピーして
少し離れた A7セルで、右クリック
「形式を選択して貼り付け」をクリック

 201905280030.png

[形式を選択して貼り付け]ダイアログ
【 〇 値 】 に、チェックを 入れ
【 □ 行列を入れ替える 】 に、チェックを入れ
[ OK ] クリック

 201905280031.png

データが、「 , 」区切りで、表示される

 201905280032.png

データ]タブ 「区切り位置」をクリックする

 201905280033.png

データの区切り位置 設定ウィザード]ダイアログ
【 〇 区切り記号 】 に、チェックを入れる
[ 次へ ] を クリック

 201905280034.png

データの区切り位置 設定ウィザード- 2/3]ダイアログ
【 □ カンマ 】 に、チェックを入れる
[ 完了 ] を クリック

 201905280035.png

データが、横ですが、表示される

 201905280036.png

A7 セルから、[Ctrl]+[Shift]+[→]で、右端まで選択
[Ctrl]+[Shift]+[↓]で、すべて選択
コピーする
A16 セルに、先ほどと同様
「形式を選択して貼り付け」
【 〇 値 】 に、チェックを 入れ
【 □ 行列を入れ替える 】 に、チェックを入れ
[ OK ] クリック
行列を入れ替えて、値で貼り付ける

 201905280037.png

この後は、5行目から15行を削除する

 201905280038.png

実用には、向かないかもしれませんが
     何かのヒントには、なりそう


 

複数ある検索データを取り出す 3 関数_作業列あり

前の 2回は、
 * オートフィルタ
 * フィルターオプションの詳細設定

でしたので、一度抽出すると それでおしまいでした

そこで、
 抽出条件を変えるだけで
 抽出内容が 変化するように
 関数を使いたいという方がいる
 元のデータは、以前と同じ

  201905280011.png

比較しやすいように
 抽出するのは、「 男 」のデータ

最初に、書いておきますが
     いろいろな方法が あります
その1つの方法で、作業列を使用します


 考え方を 理解していただければ、
  ご自分のケースに 応用できると思います


 1.抽出する Sheet 2 に、下の画像のように入力します
   条件のセル
   抽出する項目行

  201905280021.png
  ※ Sheet 2 A2 には、リストで、「男」・「女」を選択できるようにする
  ※ 仮に、「男」にしておく

 2.Sheet 1 の一覧表の右端に、「作業列」を作成します
   「 男 」 を 探すと、どの行も 同じ「 男 」で しかないので
   一番上にある「 男 」・2番目・・と 区別する目的
   設定する数式は
   「性別」が、Sheet 2 の A2 セルと一致する場合
   Sheet 1 D列で、その行までに、一致するデータがあった個数を表示
   一致しない場合は、空欄
   =IF(D3=Sheet2!$A$2,COUNTIF($D$3:D3,Sheet2!$A$2),"")

  201905280022.png

 J3 セルに、入力した数式について
  =IF(D3=Sheet2!$A$2,COUNTIF($D$3:D3,Sheet2!$A$2),"")
  D3=Sheet2!$A$2 これは、「性別」が、Sheet 2 で、指定した性別ということ
  D3 は、数式をオートフィルする場合に、同じように移動して欲しいのでそのまま
  Sheet2!A2 は、移動したら困るので、絶対参照 ($)設定
  COUNTIF($D$3:D3,Sheet2!$A$2) は、D列に、指定した性別がいくつあるか?
  D列のセル範囲 $D$3:D3 
  始まりは、D3 セル 移動したら困るので 絶対参照
  セル範囲の終わりは、その数式がある行まで 移動して欲しいのでそのまま
  数式を オートフィルする場合、
  セルやセル範囲の変化を 想像して 「 $ 」 を 設定する


作業列に 設定した数式により
抽出したいデータは、
  作業列に、数値がある行ということになる


ここからが、ややこしいのですが
Sheet2 のデータを表示したいセル Sheet2 A5 に 以下の数式を入力する
 ※ 文字で書こうとしたら、記号が良くないのか表示できないので、画像になってしまった
201905280023.png

A5 セルを 右へオートフィルする

  201905280024.png

そのまま データがありそうな位置まで、下へオートフィルする
 ※ 少し長めに設定する

  201905280025.png

結果
 文字列の配置や罫線は ともかく データは、表示される

  201905280026.png

肝心の関数の意味

 =IF(MAX(Sheet1!$J$3:$J$47)<ROW(A1),"",INDEX(Sheet1!B$2:B$47,MATCH(ROW(A1),Sheet1!$J$2:$J$47,0)))
 ※ 「<」を 「<」(全角)に変更したら、アップ出来ました

一番外側は、I F 関数
 条 件
 MAX(Sheet1!$J$3:$J$47)<ROW(A1)
  MAX関数、作業列の最大値=求めるデータの総数
  ROW関数 数式を設定した行の数
  これを比較して、数式を設定した行が、データの数を超えるかどうか
 真の場合
  「データの数」が、「数式を設定した行」より少ない場合、空欄
  ※ データが 無い行は、空欄にする
 偽の場合
  データがある場合
  INDEX(Sheet1!B$2:B$47,MATCH(ROW(A1),Sheet1!$J$2:$J$47,0))
  まず INDEX 関数
  範 囲
  元の一覧表(Sheet 1)の表示したい列のセル範囲
  数式を入力したのが、「受験番号」の列なので、Sheet 1 では、 B列
  数式を オートフィルするので  列は、自由 行は、固定
  
  MATCH 関数で、
  最初のセルは、1 次のセルは 2 を 探したいので
  ROW 関数で、探す数値を決定
  探す範囲は、「作業列」
  照合の型
  完全一致の 「 0 」 を 入力

数式を 右へ オートフィルすると
 INDEX 関数の範囲が、1つづつ右へずれる
 下へオートフィルすると
 ROW 関数が、1つづつ増えて、順に探す行が移動する
 データがなくなると、I F 関数によって、空欄になる

応用する方法

この例に使用した一覧表では、わかりやすく
条件を 1つにしましたが
「 作業列 」 に、入力する数式を変更する
例えば、**で、〇〇の場合などは
AND 関数を使用する
**または、〇〇の場合
OR 関数をしようするなど
ポイントは、
「 作業列 」に 表示したいデータの行に順番に番号を表示
必要ない行は、空欄にする

この例では、すべての列を 表示しましたが
「 名 前 」 だけを、表示したい場合は
Sheet 1 の C列のセル範囲だけを 取り出せばよい
INDEX 関数の「 範囲 」 を Sheet1!C$2:C$47 にするだけ
=IF(MAX(Sheet1!$J$3:$J$47)<ROW(B1),"",INDEX(Sheet1!C$2:C$47,MATCH(ROW(B1),Sheet1!$J$2:$J$47,0)))
※ 「<」(全角)になっています、コピーして使用する場合は、「<」(半角)に変更してください

. 

複数ある検索データを取り出す 2

今回は、
フィルタ オプションの設定(データ抽出)

サンプルは、前回と同じ

  201905280011.png

 抽出する場所を、別のシートへ抽出する

 この場合は、抽出する条件の「見出し」をコピー
 条件は、「性別」が、「 男 」 なので、
 見出しは、「性別」を コピーする

 別シートへ 貼り付ける この場合 Sheet2
 そして、そのセルの下のセルへ条件を入力する
 条件は、男

  201905280013.png

  この入力したセルから 
  1列 1行以上離したセルをクリック A4 セル
  [データ]タブ
  「詳細設定」をクリックする

  201905280014.png

 [フィルタオプションの設定]画面が表示される
 
  201905280015.png

 「抽出先」
 【 〇 指定した範囲 】 に、チェックを入れる


  201905280016.png

 「リスト範囲」欄に、既に「Sheet2!$A$4」とあるが
 削除し、Sheet1 の一覧表を選択する

  201905280017.png
  セル範囲 B2~I47 を選択した場合
  シート名が表示され、セル範囲は、絶対参照($)になる
次の 「検索条件範囲」欄には、
 先ほど作成した A1:A2 を選択する

  201905280018.png

 最後の 「抽出範囲」欄には、
  最初にクリックしたセル A4 を選択する
  ※ この位置が、抽出するデータの左上になる

 201905280019.png

 [OK]を クリックすると
 データが表示される

  201905280020.png

これで、データは抽出できたので
後は、列幅を調整するだけ


次回は、関数を使用して表示する方法

.