指定した順でのデータ抽出

質問

Excel2003で、関数を使用して、”区分一覧”から区分ごとの表に、”枝番”順に抽出したいのですが、うまくいかず昇順でしか抽出できません。
また、枝番の重複処理として、重複したら1つ前の枝番のデータを表示させたいのですが、どういう風に組み立てれば良いのか判らないので合わせて、教えて頂きたくお願いします。

回答No.11

piroin654

No10で、

　　'元データの範囲を指定。必要に応じ「B1:F16」のところを変更
　　strDataArea = "Sheet1$B5:P20"

となっていましたが、

＞'元データの範囲を指定。必要に応じ「B1:F16」のところを変更

の、「B1:F16」を「B5:P20」に変更してください。こんなこと見れば
わかりますね。コメントの書き間違いでした。

回答No.10

piroin654

No9よりの続き

以下にコードを示しておきます。
なお、以下のコードはDAOを使用しています。またExcel2003
ということなのでコードの設定はそのまま使えます。またDAOを
使うので、コード表の参照設定から
　　　Microsoft DAO 3.6 Object Library
にチェックがはいっているか確認してください。
もしもDAOが使えないならばADOに切り替えます。
そのときは補足してください。

一応、重複処理をしない場合とする場合の両方の処理を入れておきます。
一方をコメントアウトして実行すればそれぞれの場合のデータが得られます。
以下のコードは寿福処理をしない場合で、する場合はコメントアウトして
います。変更して試してみてください。

Excelのバージョンによる変更や実際の元データの位置など
いくつかは実際に合わせて変更してみてください。一応
コード中にコメントをいれています。

Sub test()
　　Const WL As Long = 15 'データの出力を始める列の幅の確保用
　　Dim db As DAO.Database
　　Dim rs1 As DAO.Recordset
　　Dim rs2 As DAO.Recordset
　　Dim strFile As String
　　Dim strSQL1 As String
　　Dim strSQL2 As String
　　Dim i As Long
　　Dim j As Long
　　Dim m As Long
　　Dim numReg As Long
　　Dim numBranch As Long
　　Dim strDataArea As String

　　'元データの範囲を指定。必要に応じ「B1:F16」のところを変更
　　strDataArea = "Sheet1$B5:P20"
　　'データの区分の名寄せしたデータ取得用のSQL文
　　strSQL1 = "select [区分] from [" & strDataArea & "] " & _
　　　　"group by [区分] order by [区分] asc"
　　'データを区分、枝番、および番号を昇順にしたデータの取得用のSQL文
　　strSQL2 = "select * from [" & strDataArea & "] " & _
　　　　"order by [区分],[枝番],[番号] asc"
　　'自身へのパス
　　strFile = ThisWorkbook.FullName
　　'Excelのバージョンによって以下の 「EXCEL 8.0」を変更
　　Set db = OpenDatabase(strFile, False, False, "EXCEL 8.0;HDR=YES;")
　　Set rs1 = db.OpenRecordset(strSQL1, dbOpenDynaset)
　　Set rs2 = db.OpenRecordset(strSQL2, dbOpenDynaset)
　　'入力を始める行の初期値。最初の「区分1」のデータの出力する行の位置
　　i = 2
　　'区分別に取得したレコード数の初期値
　　j = 0
　　With Worksheets("Sheet1")
　　'SQL文でデータが取得できたか確認
　　If rs1.RecordCount > 0 Then
　　If rs2.RecordCount > 0 Then
　　　　rs1.MoveFirst
　　　　Do Until rs1.EOF
　　　　　　'区分の表示
　　　　　　.Cells(i, WL).Value = rs1!区分
　　　　　　'項目の表示
　　　　　　For j = 0 To rs2.Fields.Count - 1
　　　　　　　　.Cells(i + 1, j + WL).Value = rs2.Fields(j).Name
　　　　　　Next j
　　　　　　'データの表示
　　　　　　rs2.MoveFirst
　　　　　　numReg = 0
　　　　　　numBranch = 0
　　　　　　Do Until rs2.EOF
　　　　　　'########　重複処理をしない場合　#############
　　　　　　　　If rs1!区分 = rs2!区分 Then
　　　　　　　　　　For m = 0 To rs2.Fields.Count - 1
　　　　　　　　　　　.Cells(i + 2, m + WL).Value = rs2.Fields(m).Value
　　　　　　　　　　Next m
　　　　　　　　　　i = i + 1
　　　　　　　　　　numBranch = rs2!枝番
　　　　　　　　　　numReg = rs2!番号
　　　　　　　　　End If
　　　　　　'########　重複処理をする場合　##############
　　　　　'　　If rs1!区分 = rs2!区分 Then
　　　　　'　　　　If numBranch = rs2!枝番 Then
　　　　　'　　　　　　If rs2!番号 > numReg Then
　　　　　'　　　　　　　'何もしない
　　　　　'　　　　　　End If
　　　　　'　　　　Else
　　　　　'　　　　For m = 0 To rs2.Fields.Count - 1
　　　　　'　　　　　.Cells(i + 2, m + WL).Value = rs2.Fields(m).Value
　　　　　　'　　　Next m
　　　　　　'　　　i = i + 1
　　　　　　'　　　numBranch = rs2!枝番
　　　　　　'　　　numReg = rs2!番号
　　　　　　'　　　End If
　　　　　　'　End If
　　　　　　'###################################
　　　　　　rs2.MoveNext
　　　　　　Loop
　　　　　　'取得した各区分間を分離するためにレコード数分ほど行間を確保
　　　　　　i = i + 5
　　　　rs1.MoveNext
　　　　Loop
　　End If
　　End If
　　End With
　　rs1.Close: Set rs1 = Nothing
　　rs2.Close: Set rs2 = Nothing
　　db.Close: Set db = Nothing
End Sub

以上、わからないことがあれば補足してください。

回答No.9

piroin654

質問のデータを質問に即してデータに手を加えずに
関数だけで取り出すの無理だと思います。

このような内容のデータ処理はデータベースでは、質問の
表中の左端の「＃」という列の名称を「番号」と置き換えて、

　　　　select *
　　　　from データテーブル as TempA
　　　　where not exists (
　　　　select min(番号) from データテーブル as TempB
　　　　where TempA.区分 = TempB.区分
　　　　and TempA.枝番 = TempB.枝番
　　　　and TempA.番号 > TempB.番号
　　　　group by 区分
　　　　)
　　　　order by 区分, 枝番 asc;

というSQL文を実行すると質問のすべてに答えられるデータの
一覧が取得できます。これは、どのデータベースでも同じです。
さらには、区分のフィールドをパラメータにすれば、区分別に
データが抽出できます。
しかしながら、ExcelでもSQLを使用してシート状のデータを
加工することはできるのですが、残念ながら上記のSQLは
Excelではデータを取得できません。それは上記のSQL文の中の
「not exists]の部分をExcel上では認識できないからです。
上記のSQLは集合で言えば直積を利用したデータの絞り込みです。
Officeのバージョンによっては可能性はなくはないかもしれません。
Officeの中ではAccessはこのSQL文でデータが取得できます。
ですから、手っ取り早いのはデータをAccessにエクスポートし、
上記のSQL文でクエリを作り、実行結果をExcelに戻してデータを
順番に区分別取り出せばいいのですが。
ただし、上記のSQL文の場合、Accessの場合は番号、区分、枝番
といったフィールド名はすべて[番号]、[区分]、[枝番]・・の
ように[]で囲う必要があります。

そこで、Excelではどうするかといえば、二つのSQL文とVBAによる
データの取り出しを工夫することにより、質問の

＞”区分一覧”から区分ごとの表に、”枝番”順に抽出したい
＞枝番の重複処理として、重複したら1つ前の枝番のデータを表示

の二つの要望を満たすことができます。

以下続きます。

回答No.8

bunjii

回答No.7で申し上げましたExcel 2002での検証結果を提示します。
A6=O6&"-"&P6&"-"&SUMPRODUCT((O$6:O6=O6)*(P$6:P6=P6))
A6セルを元データの最下行までコピーしてください。
T8=IF(U8="","",IF(SUMPRODUCT((O$6:O$50=U$6)*(P$6:P$50=U8))>1,IF(U7=U8,SUM(T7)+1,1),1))
U8=IF(ISTEXT(T7),1,IF((SUMPRODUCT((O$6:O$50=U$6)*(P$6:P$50=U7))-SUM(T7))>0,SUM(U7),IF(SUMPRODUCT((O$6:O$50=U$6)*(P$6:P$50=(SUM(U7)+1))),SUM(U7)+1,"")))
T8とU8セルを下へ必要数コピーします。空欄のセルの下までコピーすると不都合な状態になるでしょう。
V8=IF(U8="","",VLOOKUP($U$6&"-"&$U8&"-"&$T8,$A$6:$P$50,MATCH(V$7,$A$5:$P$5,0),FALSE))=IF(U8="","",VLOOKUP($U$6&"-"&$U8&"-"&$T8,$A$6:$P$50,MATCH(V$7,$A$5:$P$5,0),FALSE))
V8セルを右と下へ必要数コピーします。
尚、今回の検証は「区分1」のみです。
「区分2」以下を抽出するときはU6を参照している数式やT:AGの7行目を参照している数式を修正する必要があります。
数式で解説を必要とする部分がありましたら補足で要請してください。

回答No.7

bunjii

回答No.6の訂正です。
Excel 2003でしたね。
当方のExcelは2013なので提示した数式のCOUNTIFS関数はExcel 2003にありませんので別の方法にしなければ目的通りになりません。
当方にはExcel 2003がありませんのでExcel 2002で検証してから再度の回答をしたいと思います。

回答No.6

bunjii

勝手解釈ですが添付画像のようなことで良いと思います。
A列には検索用キーワードとして「区分」-「枝番」-「No」と言う感じの文字列を作成します。
「No」は「区分」-「枝番」で重複するときの追い番号と考えてください。
A6=$O6&"-"&$P6&"-"&COUNTIFS($O$6:$O6,$O6,P$6:P6,P6)
A6セルを下へ必要数コピーします。
次に区分ごとに抽出するときの処理を考えます。
U列の枝番は重複があることが前提なので追い番号順にすべて列記するものとしました。
区分1の例では枝番の3が3件あるように「◯◯◯◯区分一覧」を加工しました。
U8=IF(ISTEXT(T7),1,IF((COUNTIFS(O:O,U$6,P:P,U7)-T7)>0,SUM(U7:U7),IF(COUNTIFS(O:O,U$6,P:P,SUM(U7:U7)+1),SUM(U7:U7)+1,"")))
下へコピーすると「区分1」の「枝番」を順次表示しますので空欄になるまでコピーします。
T列は「枝番」が重複しているときに1、2、3のように追い番号が発生します。重複無しのときは1のみになります。
「姓」から右へデータを抽出しますので元データと同じ文字列の項目名（フィールド名）で抽出する位置を決めています。
V8=IF($U8="","",VLOOKUP($U$6&"-"&$U8&"-"&$T8,$A$6:$P$20,MATCH(V$7,$A$5:$P$5,0),FALSE))
V8セルを右と下へ必要数コピーします。
別の区分（「区分2」）については数式の形は同じですが扱うセルの番地が異なりますので数式をそのままコピーすると目的と異なる結果になります。
貼付画像が見えにくいときは元データと抽出結果の列を一部非表示にした画像を追加の回答として後追いさせて頂きます。

回答No.5

piroin654

質問の画像のデータを区分別にし、枝番の並びを
昇順に抽出できることは可能ですが、質問の中で、

＞枝番の重複処理として、重複したら1つ前の枝番のデータを表示させたい

とありますが。たぶん「1つ前の枝番のデータ」とは、画像中の元データの
「＃」の列の番号の並びの若いほうのデータを表示する、ということなの
でしょう。
そこで確認ですが、もし同じ区分の中で、枝番の重複が3個とか4個とか
あったらその場合はどのように処理をすればいいのですか？どのデータを
最終的に残すのですか？

回答No.4

bunjii

>Excel2003で、関数を使用して、”区分一覧”から区分ごとの表に、”枝番”順に抽出したいのですが、うまくいかず昇順でしか抽出できません。
提示の画像で肝心な部分（A列）が隠れています。
また、抽出する表で使っている数式の提示も明確ではありません。
従って、あなたの目的通りにするための論理を組み立てるのが困難です。

>また、枝番の重複処理として、重複したら1つ前の枝番のデータを表示させたいのですが、
意味不明です。
自分でやりたいことを第三者に理解できる言い回しで説明してください。
また、同じ区分の中の枝番に重複があることは元データに不備がありますので元データの修正が先決になります。
画像からの推測ですがA6セルには次の数式が入力されていると思います。
=COUNTIF($O$6:$O6,$O6)&$O6
この数式を下へコピーした値が検索範囲であればP列の枝番は無意味になっています。
データの並び順と抽出の仕方をもう1度見直してください。
VLOOKUP関数で抽出する場合は範囲の左端の列が検索対象のキーになりますので重複の無い状態に整備してください。

回答No.3

imogasi

エクセルで、関数をつかって、条件を指定して、別セル範囲になるが、抜出しようなどというのは、初心者にはむつかしく、関数では、無理があります。
https://nyanto.jimdo.com/エクセル関数-問題解決/条件に合うデータ全ての抽出方法2/
の例でも見てください。
Googleで「エクセル関数　条件を満たすデータを抜き出す」などで照会してみるとよい。
ーー
関数でなくて、フィルタ「操作」で何とかしのぐかどうかでしょう。
ーー
VBAなりを、長年勉強して、どう処理するかの、処理を考えるのか、というレベルの問題です。
ーー
＞枝番の重複処理として、重複した・・
こんなのはVBAででも難しいと思います。
＝＝
関数でもよいが、関数を使って作業列に、「修正ソートキー」をつくれるかどうかだが、場合によっては作業列に都合のよいコードを作れるかどうかが、問題。
＝＝＝
また質問として、画像だけ挙げて、
抜出条件が質問文でははっきり文章で、説明されてない。
文章で、○○列の値がXXの行のデータを別セル範囲に抜き出す（ただし上行から詰めて）のように書くべき。
ーー
回答者が、指摘している、質問者がもしVLOOKUP関数を使っているなら（この使用した関数名ことも、質問にはっきり書くべきです）、複数該当分がある場合は、利用に適しない。VLOOKUP関数は、エクセルでは有名関数で、このこと（複数該当分を採れない）は常識で、質問者は勉強や経験が必要です。
ーー
フィルター操作で、条件項目で抜出して、結果を（操作で）並べ替えると質問者の思うようにならないのか？

回答No.2

msMike

確認させてください。
》 重複したら1つ前の枝番のデータを表示させたい
例えば、# が 6、7 の行の区分が何れも「区分1」の場合は「枝番2」が重複するけど、そのときはどう表示させたいと？
枝番2の「1つ前の枝番」である枝番1のデータを表示？！
じゃあ、枝番1が重複したら、どうするどうする？