Ｅｘｃｅｌ 任意の数値を上から順に参照、表示する方法

質問

図1
　　Ａ　Ｂ　　　 Ｃ　Ｄ
１　　　３　　　 １　３
２　１　４　⇒　２　５
３　　　５
４　２

(1)　図1の様に、Ａ列の任意のセルに入力された値を上から順に選択して、Ｃ列の１行目から表示させる方法

(2)　上記同様Ｂ列の任意のセルに入力された値を上から順に選択して、Ｄ列の１行目から表示させる方法、但しＢ列の左側に値が有る場合（図1の場合はＢ２セル）は次の値から参照する

以上２点の回答（Ｃ、Ｄ列に入力するべき関数）をお願いします。
※Ａ列及びＢ列には値が入らない（空白）場合もあります
※今回、関数のみでの回答を希望しております（マクロ不可）
※実際の表では選択と表示させるシートは分かれています

返答が一時遅れる場合も有りますがご了承下さい。

回答No.4

imogasi

一見して、C列に持って来るセルが、不定の課題は関数では複雑になるので、これも複雑な式になる事が経験から判る。
関数の特徴を知らないから、難しいことも判らず、色々回答に注文をつけることになる。
・操作（フィルタやフィルタオプションの設定）で出来ないか考える　既回答あり。略。
・VBAでは極極く簡単になる場合がある（これなどコード数行）ので、VBAを質問者が勉強すべきだ。
簡単なVBAに比べると、配列数式を理解するほうが、もっと難しいのではないかと思うぐらいだ。
このコーナーは回答者や読者にたいする、テスト問題ではないのだから、色々の型の回答に接して、自分の不勉強を知り、勉強のこと。
ーー
配列数式になるような場合、作業列を使うと、やや考えやすくなる。
（１）の場合
例データ　データは第2行目から。元の質問のB列データではない。
A列　　　B列　　　C列
データ 連番 つめて表示
1 1 1
ー ー 4
ー ー 2
4 2
ー ー
2 3
A列ーは空白セル、B列は関数の結果で空白

BれつB2の式　=IF(A2<>"",MAX($B$1:B1)+1,"")
下方向に式を複写。
C2は　=INDEX($A$2:$A$100,MATCH(ROW()-1,$B$2:$B$100,1))
下方向に式を複写。
（２）は
データ データ 作業列 つめて表示
ー 3 1 3
1 4 ー 5
ー 5 2 6
8 5 ー
ー 6 3
C２の式　=IF(AND(A2="",B2<>""),MAX($C$1:C1)+1,"")
下方向に式を複写
D2の式　=INDEX($B$2:$B$100,MATCH(ROW()-1,$C$2:$C$100,1))
下方向に式を複写。
（１）はC列、（２）はD列において複写行数を作業列のMAX数字を超えないようにしないとならない。これはとりあえず省略する。
一種の関数による抜き出し問題といえる。
（１）は空白でない、（２）は左列が空白でないという条件。
Googleで「imogasi方式」で照会すれば、私の回答したやり方と、他の回答者の方法が載った、抜き出し問題がたくさん出てくる。

補足

回答有難うございます。

返答に時間が掛かってしまい申し訳ないです。

今回VBAやフィルタを使用しない理由は、作成したファイルを他人が使用するからです。
作成したシートはパスワードを掛けてロックしてしまいますので、殆ど編集できない状態になります。
マクロなどパスワードを求めるものも使用できません。

imogasi様の式も実行しましたが、やはり上手くいきませんでした。

＞このコーナーは回答者や読者にたいする、テスト問題ではないのだから、色々の型の回答に接して、自分の不勉強を知り、勉強のこと。

仰るとおりだと思います。
私自身時間に余裕がなく余り他者の閲覧等は行なえていないです。
時間を見つけて少しでも勉強しようとは思っております。

回答No.3

cistronezk

＞以上２点の回答（Ｃ、Ｄ列に入力するべき関数）をお願いします。

"関数"のご指名をされる方が多いですが、オートフィルタの方が、複雑で理解できないであろう式より判り易く簡単だと思うのですが。

まず、A、B列を選択して「データ」－「フィルタ」ー「オートフィルタ」を設定。
■C列の求め方
・A列のリストから「空白以外」を選択。
・A列をコピー、C列にペースト。
■D列の求め方
・A列のリストから「空白」を選択し、さらにB列のリストから「空白以外」を選択。
・B列をコピー、D列にペースト。
以上です。

補足

回答有難うございます。

オートフィルターですと目視で確認するには良いのですが、今回はＣ，Ｄ列に自動で参照したく思っておりました。

それから今回の作成したデータは殆どＰＣに無知な他人が使用することになりますので操作を簡略化させる必要があります。
ですのでコピー＆ペーストの操作も行ないませんし、参照先セルもロックしておりますので操作自体出来なくしております。

説明不足で申し訳ありませんでした。

回答No.2

tom04

こんばんは！
一例です。

↓の画像のように作業列を挿入させてもらっています。
どの列でも構いませんが、C列に作業列を挿入させてもらっていますので
C・D列が1列ずれていますのでご容赦願います。

まず作業用の列のC2セルに
=IF(AND(A2="",B2<>""),ROW(A1),"")
という数式を入れ、オートフィルで下へコピーします。
（数式は1000行まで対応できるようにしていますので、1000行くらいまでコピーしておいても構いません）

そして、D2セルは配列数式になりますので
この画面からコピー＆ペーストする場合は、単に貼り付けただけではエラーになると思いますので、
貼り付け後、F2キーを押すか、または数式バー内で一度クリックし、編集可能にします。
そして、Shift＋Ctrl＋Enterキーを押してみてください。
数式の前後に｛　｝マークが入り配列数式になります。

D2セルは、
=IF(COUNTA($A$2:$A$1000)>=ROW(A1),INDEX($A$2:$A$1000,SMALL(IF($A$2:$A$1000<>"",ROW($A$1:$A$999)),ROW(A1))),"")
という数式を入れています。

次にE2セル（配列数式ではありません）は
=IF(COUNT($C$2:$C$1000)>=ROW(A1),INDEX($B$2:$B$1000,SMALL($C$2:$C$1000,ROW(A1))),"")

とし、D2・E2セルを範囲指定し、
E2セルのフィルハンドルで下へコピーすると画像のような感じになります。

尚、作業列が目障りであれば列全てを範囲指定し、非表示にしてはどうでしょうか？

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

補足

回答有難うございます。

上記画像の通り式を当てはめましたところ、希望の値を得ることが出来るのですが、No.1様の補足欄のとおり実際のセルに当て嵌めましたところ結果が表示されませんでした。

今回、P及びQ列に対して以下のとおり式を当て嵌めました。

R＞=IF(AND(P9="",Q9<>""),ROW(P8),"")

S＞=IF(COUNTA($P$9:$P$70)>=ROW(P8),INDEX($P$9:$P$70,SMALL(IF($P$9:$P$700<>"",ROW($P$8:$P$69)),ROW(P8))),"")　※配列数式

T＞=IF(COUNT($R$9:$R$70)>=ROW(P8),INDEX($Q$9:$Q$70,SMALL($R$9:$R$70,ROW(P8))),"")

列と行の数字を入れ替えただけですので、そもそも解答の捉え方が間違っているのかもしれません。
知識不足で非常に申し訳ありませんが、何方か補足いただければ幸いです。

回答No.1

noname#204879

下記の式は何れも配列数式です。
C1: {=IF(ISERROR(SMALL(IF(A$1:A$1000="","",ROW(A$1:A$1000)),ROW(A1))),"",INDEX(A$1:A$1000,SMALL(IF(A$1:A$1000="","",ROW(A$1:A$1000)),ROW(A1))))}

D1: {=IF(ISERROR(SMALL(IF(A$1:A$1000="",IF(B$1:B$1000="","",ROW(A$1:A$1000)),""),ROW(A1))),"",INDEX(B$1:B$1000,SMALL(IF(A$1:A$1000="",IF(B$1:B$1000="","",ROW(A$1:A$1000)),""),ROW(A1))))}

補足

回答有難うございます。

試す時間が無く返答が遅れてしまい申し訳ありませんでした。
今回mike_g様の式を使用してみました。結果は次の通りです。

図1を用いて験させていただいたところ使用できましたが、実際使うセルを用いてでシートを跨いで入力したところ結果が出ませんでした。
以下がその通りに入力した数式です。

I4＞=IF(ISERROR(SMALL(IF(入力!$P9:$P70="","",ROW(入力!$P9:$P70)),ROW(入力!P9))),"",INDEX(入力!$P9:$P70,SMALL(IF(入力!$P9:$P70="","",ROW(入力!$P9:$P70)),ROW(入力!P9))))

※入力箇所は、シート1としましてI4～M4（図1でいうC列）の最大５枠のみで横方向（行）に結果表示させようとしています。

仮に「入力」というシートからの参照ですが、参照範囲は見ての通りP9～P70（質問の図1でいうとA列）です。
結合セルも含まれていませんのでエラーにはならないと思います。

跨いでの参照では結果がでなかった為、図1のとおり同一シートのR列に式を入力させてみましたが、結果は表示されませんでした。
以下がその式です。

R9＞=IF(ISERROR(SMALL(IF(P$9:P$70="","",ROW(P$9:P$70)),ROW(P9))),"",INDEX(P$9:P$70,SMALL(IF(P$9:P$70="","",ROW(P$9:P$70)),ROW(P9))))

私の式に不具合があるのだと思いますが、その箇所が分からなかったので何方か補足して頂けたら有り難いです。