[[20200929215523]]　『複数条件で値を抽出したい』（みず）　ページの最後に飛ぶ

『複数条件で値を抽出したい』（みず）

お世話になります。

工具名称、ナンバー、条件1、条件2の4項目に合致したナンバーを
工具設定リスト(別BOOK）より抽出したいです。

工具名称ナンバー条件1 条件2
DR-2.2 　　　　 000001 A
DR-2.2 000002 A 1
DR-2.3 000003
DR-2.4 000004 B

工具名称の先頭アルファベットは11種類あります。
工具設定リスト(別BOOK）にシート分け（11シート）しています。

DRのシートで試しているのですが、
条件によりシートを変えて検索するのはマクロを使わないといけないのかなと思い
現在試しているワークシート関数をVBAの記述に変えようと
ネットで調べていますが、思うようにいきません。

どなたかご教授願えないでしょうか？

取りあえず同じシートのＫ2に
=INDEX(テーブル1,SUMPRODUCT((テーブル1[工具名称]=H2)*(テーブル1[条件1]=I2)*(テーブル1[条件2]=J2),ROW(テーブル1[工具名称])-1),2)
と記述しています。

＜使用 Excel：unknown、使用 OS：unknown ＞

表がずれてしまいました
工具名称　ナンバー　条件1 　　条件2
DR-2.2　 000001 　　A
DR-2.2 　000002 　　A 　　　　　1
DR-2.3 　000003
DR-2.4 　000004 　　B
（みず） 2020/09/29(火) 21:59

Excelは
Excel for Microsoft 365 で

Windows10です
（みず） 2020/09/29(火) 22:00

続きです

Sub Sample2()

Dim MyRng As Range, MyRng1 As Range, MyRng2 As Range, MyRng3 As Range

Set MyRng = Range("A1").ListObject.DataBodyRange
Set MyRng1 = Range("A1").ListObject.ListColumns(1).Range
Set MyRng2 = Range("A1").ListObject.ListColumns(3).Range
Set MyRng3 = Range("A1").ListObject.ListColumns(4).Range

MsgBox Application.WorksheetFunction.Index(MyRng, 1, 2) 'INDEX記述の仕方の確認の為、書きました。

End Sub

Index(MyRng, 1, 2)の Index(MyRng,「ここの部分」 1, 2)
「ここの部分」に
SUMPRODUCT((テーブル1[工具名称]=H2)*(テーブル1[条件1]=I2)*(テーブル1[条件2]=J2),ROW(テーブル1[工具名称])
の内容を記述出来れば、うごくのでしょうか？

（みず） 2020/09/29(火) 22:10

 DRシートがなんなのかわかりませんが、
 検索はテーブル1の行番号なのに、別のブックのしかも11シートとも行番号と一致してるもんですか？

 表がずれても構わないので、以下のエクセルの表範囲をコピーペーストしてもらえますか？

 １ 検索する項目のあるテーブル
 ２ 検索条件
 ３ 工具設定リスト、何種類か全然異なるもの

（稲葉） 2020/09/30(水) 07:12

工具名称ナンバー条件1 条件2 条件3
DR-2.2 102200
DR-2.5 102500
DR-3.0 103000
DR-3.1 103100
DR-3.2 103200
DR-3.3 103300
DR-4.2 104200
DR-4.2 104220 　　　　　 C

工具名称ナンバー条件1 条件2 条件3
EMA-9.00 200900
EMA-10.0 201000
EMA-12.0 201100
EMA-15.0 201201 　　　　　　　　　　　　200
EMA-18.0 201300
EMA-20.0 201400

工具名称ナンバー条件1 条件2 条件3
TAP-4.0 300400
TAP-4.0 300407 　D
TAP-5.0 300500
TAP-5.0 300507 　D
TAP-6.0 300600
TAP-6.0 300607 　D
TAP-8.0 300800

11シートあるうちの3シートの一部です。

条件1は材質でアルファベット
条件2は形状の種類分けで文字を入れます。
条件3は全長で数字を入れます。
条件は～5まで増やす予定です。

テーブルは設定途中です。

＞１検索する項目のあるテーブル
どの様にお伝えしたら良いのか分からないので
教えて下さい。
よろしくお願い致します。

（みず） 2020/09/30(水) 08:25

  ＞条件は～5まで増やす予定です。

  結構多いですね。

  検索を容易にする為、最後の列に「キー」を追加したテーブルにできないですか？
 　　　　　　　　　　　　　　　　　　　　　　　　　　　　↓  　　　　　　　　　　　
    工具名称  ナンバー  材質  形状  全長  条件4 条件5　　 キー
    DR-2.2      102200                                DR-2.2-102200-----
    DR-2.5      102500                                DR-2.5-102500-----
    DR-3.0      103000                                DR-3.0-103000-----
    DR-3.1      103100                                DR-3.1-103100-----
    DR-3.2      103200                                DR-3.2-103200-----
    DR-3.3      103300                                DR-3.3-103300-----
    DR-4.2      104200                                DR-4.2-104200-----
    DR-4.2      104220        C                       DR-4.2-104220--C--

  ※キー列に入れる数式
  　J2セル =TEXTJOIN("-",FALSE,テーブル1[@[工具名称]:[条件5]])

  あと
  (1)　別ブックはどこに有るんですか？
 　　　検索させるブックと同じフォルダーの中？

  (2)　この作業をする時、別ブックは開いてあるんですか？

 ＞１ 検索する項目のあるテーブル
 ＞どの様にお伝えしたら良いのか分からないので

 このデータの周辺の情報だと思います。(たった１行だけの話なのか、ずーっと下まであるものなのか）
 　↓
 行  ___H___   I    J   ___K___
  1                            
  2  DR-4.2        C    104220 
  3

（半平太） 2020/09/30(水) 09:59

 船頭多くて・・・なんで、私はフェードアウトします！

 ご質問に対する回答だけ。
 ＞１ 検索する項目のあるテーブル
 ＞どの様にお伝えしたら良いのか分からないので
 一つは私の勘違いです。
 １つの検索テーブルがあって、すべてのブックの行番号を網羅できているものだと勘違いしていました。
 「別ブックの中に、11シート」という説明でしたので、検索ブックの検索シートで特定した設定を、別ブックから情報を引っ張り出すものだと勘違いしていました。

 ＞ ２ 検索条件
 に関してが、半平太さんが説明してくれた内容になります。

 ＞検索を容易にする為、最後の列に「キー」を追加したテーブルにできないですか？
 私もこちらの方向で話進めていたので、お任せ致します！

（稲葉） 2020/09/30(水) 10:36

 ＞  ※キー列に入れる数式
 ＞  　J2セル =TEXTJOIN("-",FALSE,テーブル1[@[工具名称]:[条件5]])

 間違えました。　m(__)m
 ナンバー列を含めるとマズかったです。

 (正) =TEXTJOIN("-",FALSE,[@工具名称],テーブル1[@[材質]:[条件5]])

 <追加質問>

 ＞工具設定リスト(別BOOK）にシート分け（11シート）しています。

 テーブルは、各シートに１つですか？

（半平太） 2020/09/30(水) 11:37

＞検索を容易にする為、最後の列に「キー」を追加したテーブルにできないですか？
キー列の追加をしても大丈夫です。
テーブル内にとの事、テーブルをI列まで拡張して、
I2セル =TEXTJOIN("-",FALSE,テーブル1[@[工具名称]:[条件5]])
としましたが「この数式には問題があります」でました。

＞１検索する項目のあるテーブル
行数はA列の文字入力がある最終行までテーブル範囲になります。

実際の希望の結果（値）を出すまでのプロセスですが
1、CAM（NCプログラム）から加工に使用する工具情報をTEXTデータにエクスポート（出力される工具ナンバーは2桁）
2、工作機械には6桁の工具ナンバーが必要なので「FMS工具設定リスト.xls」のデータを参照して、6桁の工具ナンバーを抽出（目視、手動）

CAMからのテキストファイルをエクセルでCSVに取込で
VLOOKUPなどで「FMS工具設定リスト.xls」から抽出しようと考えていました。
CAMからのデータは情報量が少ないです。
エクセルに取り込むと
A列に1～100の数字、B列に工具の種類（DR,EM,TAPなど）C列に工具径（数字）
No. 工具径
1 FM 125
2 FM 80
3
4
5
6 FM 80
7 FM 63
8 FM 50
9 CD 19
10 CD 10
11 TAP 6
.
.
100

A列の1～100は工作機械に100本の工具が入るので100までです。
B列の工具の種類は
DR＝ドリル（6桁で100000番台を使用、FMS工具設定リスト.xls内シート名（ドリル100000）
EM＝エンドミル（6桁で200000番台を使用、FMS工具設定リスト.xls内シート名（エンドミル200000）
TAP＝タップ（6桁で300000番台を使用、FMS工具設定リスト.xls内シート名（タップ300000）
以下、RM,CD,FM,MTM,HBM,BOA,BOS,特殊工具と11のシートがあります。

6桁の左から1桁目が工具の種類で
2桁目から3桁を工具の径
残り2桁で分類しています。
分類は長さ違いだったり、材質違いなど標準工具ではないものとしています。

＞(1)　別ブックはどこに有るんですか？検索させるブックと同じフォルダーの中？
検索させるブック(FMS_Tコード更新）.XLS)と同じフォルダーです。

＞(2)　この作業をする時、別ブックは開いてあるんですか？
閉じたままを考えています。

まだお伝えしたい内容や
今試している結果などありますが

一旦、この内容を投稿させて頂きます。

（みず） 2020/09/30(水) 11:39

＞テーブルは、各シートに１つですか？
テーブルは、各シートに１つの予定ですが、複数あると検索ややこしくなりますか？
現状は1シートで600行くらいあるシートもあって、大まかに区切って列を増やして目視し易い様にしています。
エクセルで検索させるので、1列にまとめます。

＞(正) =TEXTJOIN("-",FALSE,[@工具名称],テーブル1[@[材質]:[条件5]])
試してみます。
（みず） 2020/09/30(水) 11:50

 内容が膨らんで来た感があるんですが、
 当初の質問と現実にあるレイアウトは同じじゃないですね？

 １．どっちで話を進めるか決めて頂けないですか？
 (1)当初のレイアウトでやって、実際への応用はそちらサイドでアジャストする、か
 (2)実際のレイアウトでやる。

 ２．何故拡張子がxlsなんですか？
 　それは古いファイル形式なので、事情が許すなら、xlsxかxlsmにすべきと思うのですが。。
   (将来、古い形式のファイルのお守りが面倒になってくると思います）

 ＞テーブルは、各シートに１つの予定ですが、複数あると検索ややこしくなりますか？
 ３．1つのシートにテーブルがいくつもあると、どのテーブルを見に行くのか判断が必要になります。
 　すると、テーブル名が何なのかキッチリ決めておかないとならないです。

 ＞＞(2)　この作業をする時、別ブックは開いてあるんですか？
 ＞閉じたままを考えています。
 ４．まともに「閉じたままで処理」と言うのは、プログラミングの手間を増やすだけなので、
 　自動的にプログラムで開いて、処理が終わったら、自動的に閉じると言う塩梅にしたいです。
 （人間側の手間は全くないので、支障ないと思います）

（半平太） 2020/09/30(水) 12:48

半平太様
コメントありがとうございます。
おっしゃる通り、当初の話から実際の話にシフトしていました。
基本的な所をご教授して頂いて実際への応用をしようと思っていましたが
応用する時に質問したい時、説明がややこしくなりそうなので
今のうちに、実際の条件など提示しておいた方が良いと思い
話しが膨らんでしまいました。

質問への回答ですが
1.実際のレイアウトでやる
2、xlsxかxlsmに変更する
3、1シートに1テーブル
4、自動的にプログラムで開いて、処理が終わったら、自動的に閉じる

実際のレイアウトは体裁が整ったら投稿します。

（みず） 2020/09/30(水) 13:17

コメント返信：

[ 一覧(最新更新順) ]

[[20200929215523]] 『複数条件で値を抽出したい』（みず） ページの最後に飛ぶ

[[20200929215523]]　『複数条件で値を抽出したい』（みず）　ページの最後に飛ぶ