テーブルより特定の文字列（数値、日付、文字列）を複数検索し、行列番号の小さい順に一覧表を作成したい。

３行２列のブロックからできているテーブルより特定の文字列（数値、日付、文字列）を、左上から右下のブロックまで行番号の小さい順に、かつ列番号の小さい順に複数検索し、該当ブロックの検索値の一つ上のセルと一つ下の左右のデータを取出して一覧表を作成したいと思います。

因みに、A列B列だけだとしたら、
セル11の取り出しは、　{=IF(COUNTIF($A$1:$A$18,$M$1)<ROW($A1),"",INDEX($A$1:$A$18,SMALL(IF($A$1:$A$18=$M$1,ROW($A$1:$A$18)-1),ROW($A1))))}で
セル26の取り出しは、上を列方向にオートフィルして取り出せました。
同様に、データ11とデータ26も取り出せました。
また、コード11とコード26の取り出しは、難しかったですが
{=IF(COUNTIF($A$1:$A$18,$M$1)<ROW($A1),"",INDEX($B$1:$B$18,SMALL(IF($A$1:$A$18=$M$1,ROW($B$1:$B$18)+1),ROW($A1))))}としたら、取り出せました。

しかし、質問のように複数の列がある場合にはどうすればよいのか、または別の関数（例えばSUMPRODUCT関数など）を利用しなければできないのか、としたら、どういう計算式になるのか、まったく混迷しています。
関数だけで解決できる方法が有りましたら、是非とも教えてください。

質問者からの補足コメント

• 日付の検索順序は、各ブロックの上の行にセルNO.を振ってありますが、簡単に言って最上段の左端にあるセル１ブロックの日付から順に⇒セル２⇒・・セル５迄検索し、次は、すぐ下の段の左端に戻りセル６から右へ・・・セル10迄、次はまたその下の段の左端のセル11から順にというように、最上段の左端から右端へ、次は上から２段目の左端から右端へと検索していき、最後は最下段の右端のセル30のブロックの日付の照合をしたいのです。その結果の一覧表は項番１から縦に検索が終わるまで空欄を作らず（最上段の左端から最下段の右端まで連続して）同じ検索日付の所定セルデータを取込みたいと考えています。

  No.7の回答に寄せられた補足コメントです。 補足日時：2018/12/20 12:50

• 初心者なので理解しやすい固定テーブルで質問しましたが、じつは本来のテーブルは、ブロックが横に２０～７０位、縦に１０～３０位の大きさでExcel Sheet 単位で可変してますので、シート毎にVBAをその都度変更するは大変です。そこで、ブロックの開始セル最上段左とブロック終了セル最下段右、及び結果一覧表の項番見出しの位置の設定セル、を検索日付の設定位置の下に用意して、例えば、最上段左$C$10、最下段右$AL$105、結果一覧表左上$AN$16 のように文字列で設定（パラメーター化）しておいて、それを参照して検索できるVBAができれば現実的で利用可能になります。パラメータ化でないとしたら、ポイントとなるセルに目印の記号例えば”＃＃＃”から検索開始し"％％％"までで終了するような形式も考えられます。いづれにしてもVBAでのプログラムの記述が分かりませんので、解説付きでよろしくお願いいたします。

  
  No.5の回答に寄せられた補足コメントです。 補足日時：2018/12/27 10:42

No.1 回答者： nouble1 回答日時： 2018/12/19 20:06

2点、

SMALLは、
一方向だけではなく、
面範囲にも　適応できる、

xy位置を　知りたければ、

例えば、
ROW(セル位置).(小数点位置)COLUMN(セル位置)
と、

数値内　各桁毎に　
多情報を　持たす、
仕様を、
予め　決め、
運用すれば　良い。

この回答へのお礼

nouble1　様　私は本日、教えて！Ｇｏｏに質問デビューしました。　
回答してもらえるかどうか心配でしたが、質問から２時間で回答が頂けるなんて素晴らしいです。　
あなた様は、記念すべき第一号者様です。　大変ありがとうございます。
ところで頂いたご回答ですが、どのように数式を書けば質問の一覧表ができるか、全く理解できていません。
出来ましたらもう少し具体的にお教え願えれば幸いです。
宜しくお願い致します。
ありがとうございました。

お礼日時：2018/12/20 00:22

No.2 回答者： tom04 回答日時： 2018/12/19 20:42

こんばんは！

行だけ、または列だけの検索であればお示しのような配列数式で検索可能ですが、
行、列を一気に！となると相当厄介だと思います。
＞（例えばSUMPRODUCT関数など）を利用しなければできないのか、・・・

SUMPRODUCT関数の場合、表内に重複がなければ可能ですが画像を拝見すると
重複するデータがあるので、SUMPRODUCT関数も使えないと思います。

VBAだと簡単にできますが、関数での方法をご希望だというコトなので
一例です。

↓の画像（画像が小さくて判りにくいと思いますが）のように別シートを作業用のシートとして使用するのはどうでしょうか？
画像ではSheet2を作業用のシートとして、A1セルに
=IF(Sheet1!A1=Sheet1!$M$1,ROW()*1000+COLUMN(),"")

という数式を入れSheet1と同列・同行分ほどフィル＆コピーしています。

Sheet1のM～O列の表示はこの作業用Sheetを利用します。
M5セルに
=IFERROR(INDEX(A:J,INT(SMALL(Sheet2!$A:$J,ROW(A1))/1000)-1,MOD(SMALL(Sheet2!$A:$J,ROW(A1)),1000)),"")

という数式を入れます。

N5セルに
=IFERROR(INDEX($A:$J,INT(SMALL(Sheet2!$A:$J,ROW(A1))/1000)+1,MOD(SMALL(Sheet2!$A:$J,ROW(A1)),1000)+COLUMN(A1)-1),"")

という数式を入れ隣りのO5セルまでフィル＆コピー！
最後にM5～O5セルを範囲指定　→　O5セルのフィルハンドルで下へコピーすると
画像のような感じになります。m(_ _)m

この回答へのお礼

tom04　様
こんばんは。　　　　　　　　　　　Q.A-infKU　です。

早速のご回答を、誠にありがとうございます。
まだ、検証や式の意味など理解できておらずにいますが、明日には検索日付を変更したりしていろいろ確認したいと思います。

またＶＢＡにつきましては、全くの初心者なのでとりあえず、関数で可能であればと考えましたが、「VBAだと簡単にできます」ということでしたら、
ＶＢＡのプログラムによる解法も研究したく存じます。
そんなに急ぎませんので、是非教えていただければ幸いです。

ありがとうございました。

お礼日時：2018/12/20 01:03

No.3 回答者： nouble1 回答日時： 2018/12/20 00:28

そうそう、

該当項が　複数間場合は、

例えば、
評価値　行ナンバー　小数点区切り　列ナンバー
等と、
データ仕様を　決めて、
SMALL等で　並べ直させ、

順に　表示させれば、
イケますよ。

No.4 回答者： nouble1 回答日時： 2018/12/20 00:37

あぁ、

遅れて　済みません、

お礼　拝見しました。


此処は、
人口が　少ないのか、
こう言った　ジャンルは、
特に、

皆さん　質問に、
飢えているかも　知れません故、

レスポンスも、
連れて　早いものと、
思います。　(*^_^*)


さて、記載には、
お日にちを　頂きます。


書く　気力が、
出るかも、
少し、微妙ですが、
お許し　頂けますか？

No.5 回答者： tom04 回答日時： 2018/12/20 08:59

No.2です。

VBAでの方法もご希望だというコトなので、一例です。
お示しの画像通りの配置だとします。
いろんな方法がありますが、今回はチェンジイベントにしてみました。

Excel画面左下にある、お示しの画像のSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）
M1セルの日付を色々入れ替えてみてください。

Private Sub Worksheet_Change(ByVal Target As Range) '//この行から//
　Dim i As Long, j As Long, lastRow As Long
　　With Target
　　　If .Address = "$M$1" And .Count = 1 Then
　　　　If .Value <> "" Then
　　　　　If IsDate(.Value) Then
　　　　　　lastRow = Cells(Rows.Count, "M").End(xlUp).Row
　　　　　　　If lastRow > 4 Then
　　　　　　　　Range(Cells(5, "M"), Cells(lastRow, "O")).ClearContents
　　　　　　　End If
　　　　　　For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row Step 3 '//2行目～A列最終行まで2行おきに//
　　　　　　　For j = 1 To 9 Step 2 '//A列～I列まで1列おきに//
　　　　　　　　If Cells(i, j) = .Value Then
　　　　　　　　　With Cells(Rows.Count, "M").End(xlUp).Offset(1)
　　　　　　　　　　.Value = Cells(i - 1, j)
　　　　　　　　　　.Offset(, 1).Resize(, 2).Value = Cells(i + 1, j).Resize(, 2).Value
　　　　　　　　　End With
　　　　　　　　End If
　　　　　　　Next j
　　　　　　Next i
　　　　　Else
　　　　　　MsgBox "日付を入力してください。"
　　　　　　.Select
　　　　　　Exit Sub
　　　　　End If
　　　　End If
　　　End If
　　End With
End Sub '//この行まで//

※　最初に記述したように、画像の配置が大前提のコードです。
1行、1列でも違えばまったく意図しない結果になります。m(_ _)m

この回答へのお礼

tom04　様

こんにちは。　　　　　　　　　　　Q.A-infKU　です。

素早いご回答を大変感謝しています。
しかし何分にも理解力が鈍く、予定の検証結果が得られていません。

もう少しお時間下さい。頑張ってみます。

以上　ありがとうございました。

お礼日時：2018/12/20 11:50

No.6 回答者： fujillin 回答日時： 2018/12/20 11:00

こんにちは

関数だけでも可能だと思いますが、およそ表計算には向いていない体裁ですので、本質ではないその処理に手間がかかるばかりになります。
式も長くなりますし、一般化すればするほど間違いも混入しやすくなりますね。
ご提示の体裁のまま処理なさりたいのであれば、VBAを用いた方がいろいろな処理がしやすいものと予想します。

以下は、ひとまず関数で算出する例です。
添付図は、日付欄（＝K2セル）に検索値を入力する場合に対応した式を作成した例です。
（一般化すれば同じ式にまとめられますが、とりあえず方法を示すものとして生のままです。）

K6セルに
=IFERROR(INDEX($A$1:$H$99,MOD(SMALL(IF((MOD(ROW($A$1:$H$99),3)=2)*(MOD(COLUMN($A$1:$H$99),2)=1)*($A$1:$H$99=$K$2),COLUMN($A$1:$H$99)*10000+ROW($A$1:$H$99)),ROW(A1)),10000)-1,INT(SMALL(IF((MOD(ROW($A$1:$H$99),3)=2)*(MOD(COLUMN($A$1:$H$99),2)=1)*($A$1:$H$99=$K$2),COLUMN($A$1:$H$99)*10000+ROW($A$1:$H$99)),ROW(A1))/10000)),"")

L6セルに
=IFERROR(INDEX($A$1:$H$99,MOD(SMALL(IF((MOD(ROW($A$1:$H$99),3)=2)*(MOD(COLUMN($A$1:$H$99),2)=1)*($A$1:$H$99=$K$2),COLUMN($A$1:$H$99)*10000+ROW($A$1:$H$99)),ROW(A1)),10000)+1,INT(SMALL(IF((MOD(ROW($A$1:$H$99),3)=2)*(MOD(COLUMN($A$1:$H$99),2)=1)*($A$1:$H$99=$K$2),COLUMN($A$1:$H$99)*10000+ROW($A$1:$H$99)),ROW(A1))/10000)),"")

M6セルに
=IFERROR(INDEX($A$1:$H$99,MOD(SMALL(IF((MOD(ROW($A$1:$H$99),3)=2)*(MOD(COLUMN($A$1:$H$99),2)=1)*($A$1:$H$99=$K$2),COLUMN($A$1:$H$99)*10000+ROW($A$1:$H$99)),ROW(A1)),10000)+1,INT(SMALL(IF((MOD(ROW($A$1:$H$99),3)=2)*(MOD(COLUMN($A$1:$H$99),2)=1)*($A$1:$H$99=$K$2),COLUMN($A$1:$H$99)*10000+ROW($A$1:$H$99)),ROW(A1))/10000)+1),"")

それぞれ配列数式として確定し、下方にコピーしています。

※ 当初はK1：L2に対応する検索値（複数可）を自由に設定すれば、それに応じた結果を抽出するというイメージで作りかけましたが、検索値入力の有無のチェックやそのケースの場合分けなどが絡んでくるため、関数式が膨大になりそうなので、とりあえず単純な検索項目固定の場合の方法の提示としました。
複数項目検索なども視野に入れるのであれば、やはり最初に言及したようなVBAでの解決の方が簡単かと思います。

この回答へのお礼

fujillin　様

こんにちは。　　　　　　　　　　　　　Q.A-iufKU　です。

ご回答を有難うございます。
いろいろな方法があるのだと、改めて感じました。

いろいろなケースにて検証できるよう、頑張ってみます。

宜しくお願いいたします。

お礼日時：2018/12/20 12:10

No.7 回答者： fujillin 回答日時： 2018/12/20 11:31

No6です

今気が付きましたが・・・

質問タイトル：「行列番号の小さい順に～～」
質問本文：「列番号の小さい順に～～」
となっていますが、どちらなのでしょうね？？

ちなみにNo6の式は、本文にある「列番号の順」となるように作成しています。
行を優先する場合は、優先順位の判断を入れ替えれば良いはずです。

この回答へのお礼

説明不足で申し訳ありません。
補足を入れましたので、NO.6のご回答に変更がありましたら、宜しくお願いいたします。
出来ましたら、各数式の意味合いも解説頂けると理解が早まり助かります。
以上　ありがとうございます。

お礼日時：2018/12/20 13:00

No.8 回答者： fujillin 回答日時： 2018/12/20 14:03

No7です

すみません、完全に、私の早とちりでしたね。
本文の方も「行番号の小さい順に、かつ列番号の小さい順に～」と行優先と書いてありました。
私が勝手に、後半だけを見てしまっていただけです。


No6の式は列優先になっていますので、行番号優先にするには行と列を入れ替えて判断すればよく、ついでに各セルの式も同じものになるようにしました。
No6の添付図で、K6セルに
=IFERROR(INDEX($A$1:$H$99,INT(SMALL(IF((MOD(ROW($A$1:$H$99),3)=2)*(MOD(COLUMN($A$1:$H$99),2)=1)*($A$1:$H$99=$K$2),row($A$1:$H$99)*10000+column($A$1:$H$99)),ROW(A1))/10000) + IF(COLUMN(A1)>1,1,-1),MOD(SMALL(IF((MOD(ROW($A$1:$H$99),3)=2)*(MOD(COLUMN($A$1:$H$99),2)=1)*($A$1:$H$99=$K$2),row($A$1:$H$99)*10000+column($A$1:$H$99)),ROW(A1)),10000) + IF(COLUMN(A1)>2,1,0)),"")

の式を入れて配列数式として確定。
確定後、右、下方にフィルコピーすれば行優先の関数式になります。
（ただし、右方向は3列以上コピーすることを想定していません。対応すると式がさらに長くなるだけなので）

＞各数式の意味合いも解説頂けると理解が早まり助かります。
No6にも述べましたが、ほとんどの部分が参照しにくい形式に対応するための計算です。
普通の表形式であれば、通常の抽出の手順で抽出可能ですが、それに対して
　・データの1単位が3×2セルである
　・表全体が、上記データ単位を縦横のマトリックス形式で埋める構成
という事によって位置の計算が面倒になっていることを処理する分だけ長い式になっているだけで、根本的な部分は通常の抽出と同じ考え方です。

簡単に説明するなら、
1）表全体から、3×2セルを1単位と考えて、位置が合致（月日部分なので、各単位の2行目かつ1列目）するセルで
2）かつ値が「検索値」と一致するものをピックアップし
3）行番号優先（←No7の式）とする順位で呼び出して
4）表示セル位置（列位置）に応じて、ヒットしたセルから見て、上、下、下右側のセルの値を表示する
5）検索エラーが生じた場合は空白とする（エラー処理）
といった感じです。

実際には、（例えば）日付で検索する際に他の項目で日付と値が合致する可能性が無いと保証されているなら、上記の1）のセル位置のチェックは省略することが可能で、直接各セルの値と検索値を比較してしまうようにすることで、式はかなり簡略化できると思います。
他の項目と比較しても一致することが無く、ピックアップされることがないので…
類似の内容（値）が存在するような場合には、同じセル位置（＝項目）であることのチェックを省けません。

この回答へのお礼

fujillin　様

こんばんは。　　　　　　　　　　　　　Q.A-iufKU　です。

変更のご回答を有難うございます。

添付されていたテーブルの仕様に合わせて、シミュレーションしてみました。
考えていた通りの一覧表が出来上がりました。　素晴らしいの一言です。

ちなみに、NO.5 様の　ＶＢＡによっても検証してみましたが、こちらも一発ですんなりと、希望している一覧表が作成されました。
本当に素晴らしいです。　感謝感謝です。

しかし、これからが大変です。
解説していただいた数式の機能と論理を研究し、実際の参照テーブルに応用できるようにならないと意味がありませんから。

じつは、本来のテーブルは、横に２０～７０ブロック位で、縦に１０～３０ブロック位の大きさで決まっていません。
Excel Sheet 単位でまちまちですので、シートごとに配列数式をその都度入れ替えるのでは効率も悪く大変ですから、
横と縦のブロック数を　横＝mm、縦＝nn のようにパラメーター化しておいて、それを参照して検索できる配列数式を作成して、
所定のブロックのデーターが取り出せるようにならないかと、考えている所です。

NO.8への質問の仕方も　「教えて！Ｇｏｏ」　にデビューしたばかりで分かっていませんが、上記のパラメーター化についても
再度の質問をさせて頂くことになるかもしれません。

その節は宜しくお願いいたします。

以上　この度は、本当にありがとうございました。

お礼日時：2018/12/20 20:58

No.9 ベストアンサー 回答者： fujillin 回答日時： 2018/12/21 17:14

No8です。

しばらくの間はエクセル環境が無いので検証はできないのですが・・・

＞シートごとに配列数式をその都度入れ替えるのでは効率も悪く大変で～
No8の式は、データの範囲（$A$1:$H$99）に関しては一般化したつもりでいますので、範囲の限定はないと思います。
（とりあえず、1万列未満と想定してはありますが）
範囲の記述が式中に何ヵ所もあるので、まとめて正しく修正しなければなりませんが、お手数ではありますが試してみてください。
また、範囲がいろいろある場合は、データの範囲を名前の定義を利用して設定するようにしておけば、比較的間違えを起こしにくく、修正する際にも手間を減らす事ができるのではないかと思います。

あるいは、サイズの異なるデータ範囲であっても、残りのセルに紛らわしい値が無いことがわかっていれば、一番大きなサイズの式をそのまま使うことも可能と思います。
ただし、配列数式なのであまり広い範囲を設定すると計算に余計な時間がかかるようになるかもしれません。

また、いろいろなバリエーションが必要な場合には、No6にも書きましたようにVBAを用いた方が無駄な計算をしなくてすみますし、汎用化しておくことで、様々な機能に対応させることも可能になると思います。
No6にも書きましたが、ご提示のデータ構成自体が、もともと表計算（=関数式）に向いているとは言い難いものになっていますので。

この回答へのお礼

ujillin　様

おはようございます。　　　　　　　　　　　　　Q.A-iufKU　です。
いろいろ教えて頂き感謝しております。
Excelの関数の壮大さを改めて感じております。
自分自身まだまだ実用レベルには到達していませんので、もっと頑張らねばと思っております。
今後ともよろしくお願いいたします。

お礼日時：2018/12/27 10:55