[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『台帳から入力規則のリストを２段階作りたい』（まこさん）
最近からエクセルの勉強を始めたばかりで、説明不足があるかと思いますが宜しくおねがいします。
発注書の台帳を作っているのですが、台帳でデータから入力規則のリストを作ろうとしています。台帳データから規格マスタはマクロを使って抽出することは成功した為、入力シートの規格の入力はプルダウンで行える様になりました。
今度は規格を選んだ後にサイズの入力も台帳データから抽出して、プルダウンで行いたいのですが、方法が見当付きません。
良い方法を教えて下さい。
ちなみに規格マスタはネット上の情報を元に下記の方法で抽出しています。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Sheets("台帳").Select
Columns("A:A").Select
Sheets("規格マスタ").Select
Columns("A:A").Select
Sheets("台帳").Range("A2:A" & myRow1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Columns("A:A"), Unique:=True
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
台帳
Ａ Ｂ Ｃ Ｄ
１ 規格 サイズ ・・・
２ ナット Ｍ６
３ 板 ２ｔ
４ 丸棒 φ３
５ ナット Ｍ５
６ 板 ３ｔ
７ ナット Ｍ３
・ ・ ・
・ ・ ・
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
入力シート
規格 サイズ
ナット （Ｍ３）
（Ｍ５）
（Ｍ６）
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
規格マスタ
Ａ
１ 規格
２ ナット
３ 板
４ 丸棒
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
まず、テーマとは離れますが、項目が羅列されている列の内容を一意化するには、かって（xl2003以前）は フィルターオプションの重複を無視する処理が一般的、というか、それで、代用していたわけですが xl2007以降は、「専用機能」として、データタブの重複の削除というものがあります。 マクロで実行する場合は RemoveDuplicatesメソッドになります。
で、本題ですが、要件としては、選択された規格にあわせ、それに紐付くサイズのリストを作成することですか？ それとも、それを、サイズ欄への入力規則条件として設定することですか？
（β） 2016/01/12(火) 21:18
とりあえず操作として実現する手順をメモします。 ＶＢＡ処理が希望ならこの処理をマクロ記録すれば基本的なコードは入手できます。
１．台帳シートにオートフィルターを設定します。 ２．規格シート上で一意化されている規格で、１つずつ、フィルタリングします。 ３．抽出結果のＢ列の領域をタイトル含めて選択し、規格シートのどこかの列にコピペ。アップされた例では ３つの列ができます。 ４．この列のタイトル行を除いた領域を選択して名前定義。たとえば ナット。 ５．入力シートのサイズ欄に入力規則。リスト選択で、元の値欄に =INDIRECT(規格欄のセル)
（β） 2016/01/12(火) 21:51
上記の1.〜4.までの処理コード案です。 規格シートのＡ列に規格、Ｂ列以降に、各規格ごとのサイズ一覧を生成し、それぞれにその企画名を名前として設定します。
あとは、5.（サイズ欄の入力規則）を一度手作業で設定しておけばＯＫかと思います。
（ここも、さらには 規格欄の入力規則設定も、もちろん、マクロ内で自動実行できますが）
Sub Sample()
Dim shK As Worksheet
Dim shD As Worksheet
Dim c As Range
Dim x As Long
Application.ScreenUpdating = False
Set shK = Sheets("規格マスタ")
Set shD = Sheets("台帳")
shK.UsedRange.ClearContents
With shD
.Columns("A").Copy shK.Range("A1") '規格
shK.Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes '重複の削除
.AutoFilterMode = False
.Range("A1").AutoFilter
x = 2 'B列
For Each c In shK.Range("A2", shK.Range("A" & Rows.Count).End(xlUp)) '一意化された規格を取り出す
.AutoFilter.Range.AutoFilter Field:=1, Criteria1:=c.Value 'オートフィルターによる抽出
.AutoFilter.Range.Columns("B").Copy shK.Cells(1, x) '規格シートに抽出結果をコピペ
shK.Cells(1, x).Value = c.Value
'そのリストに名前定義
ThisWorkbook.Names.Add Name:=c.Value, RefersTo:="=" & shK.Range(shK.Cells(2, x), shK.Cells(Rows.Count, x).End(xlUp)).Address(External:=True)
x = x + 1
Next
.AutoFilterMode = False
End With
End Sub
（β） 2016/01/12(火) 23:15
分かり難い質問に対応頂きありがとうございます。
教えて頂いたコードで試しに処理させてみたところ、上手く規格毎に分類してくれました。
ありがとうございます。
入力シートの入力規則も一旦選択しておけばＯＫな様です。
ただ新たな問題が発生致しまして、例では規格内の文字は漢字やカナだったので問題無かったのですが、
数字が入ってくると最後の「リストに名前定義」でエラーが出ます。
これって手動でもエラーが出るので無理なんですかね？
（まこさん） 2016/01/13(水) 09:19
先頭が漢字ならＯＫみたいですね。
ＳＳ４００みたいに英字＋数字は不可ですか
（まこさん） 2016/01/13(水) 09:41
参考に。 ヘルプで「名前」を検索、「数式で名前を定義し使用する」-「名前の構文規則」から引用。
>有効な文字 名前の最初の文字には、文字、下線 (_)、円記号 (\) しか使用できません。最初の文字以外には、文字、数値、ピリオド、および下線を使用できます。
（ねむねむ） 2016/01/13(水) 11:15
ねむねむさんから コメントがありますが、加えて、セル番地 と同じ名前も使うことができません。
SS400 というセル、存在しますので ＮＧなんですね。
こういった場合はＯＫ，こういった場合はＮＧと悩むのも面倒なのでいっそのこと 各規格の名前を 規格_hoge にしてはいかがでしょう。 そうすると、hoge が 123 でも SS300 でも いろは でも ABC でもＯＫになります。
ThisWorkbook.Names.Add Name:=c.Value, RefersTo:="=" & shK.Range(shK.Cells(2, x), shK.Cells(Rows.Count, x).End(xlUp)).Address(External:=True)
これを
ThisWorkbook.Names.Add Name:="規格_" & c.Value, RefersTo:="=" & shK.Range(shK.Cells(2, x), shK.Cells(Rows.Count, x).End(xlUp)).Address(External:=True)
入力規則の数式を =INDIRECT("規格_"&規格欄のセル)
（β） 2016/01/14(木) 09:00
念のため、補足。
>>いっそのこと 各規格の名前を 規格_hoge にしてはいかがでしょう
規格そのものは いろは や ABC や SS400 や 567 でＯＫです。その領域につける名前を 規格_hoge にするということです。
（β） 2016/01/14(木) 09:14
色々教えて頂きありがとうございます。
ハイフンを長音にしたりして乗り切ろうかと考えていたのですが、名前自体に追加する方法がベストですね。
時間がある時に試してみます。
ありがとうございました。
（まこさん） 2016/01/14(木) 19:53
Ａ Ｂ Ｃ Ｄ １ 番号 規格 サイズ ・・・ ２ 1 ナット Ｍ６ ３ 2 板 ２ｔ ４ 3 丸棒 φ３ ５ 4 ナット Ｍ５ ６ 5 板 ３ｔ ７ 6 ナット Ｍ３ ・ ・ ・ ・ ・ ・ となっていた為、コードを
With shD
.Columns("B").Copy shK.Range("A1")
shK.Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes
.AutoFilterMode = False
.Range("A1").AutoFilter
x = 2 'B列
For Each c In shK.Range("A2", shK.Range("A" & Rows.Count).End(xlUp))
.AutoFilter.Range.AutoFilter Field:=2, Criteria2:=c.Value
.AutoFilter.Range.Columns("C").Copy shK.Cells(1, x)
変えたのですが上手く抽出されません。
どこが間違っているのでしょうか？
（まこさん） 2016/01/19(火) 16:48
Field:=2 は、ＯＫですね。２列目で抽出ですから。
おしかったですね。最初の抽出条件は、それが何列目であろうと Criteria1 です。 Criteria2 は、And とか Or とか ２つの値の間 といった条件の時に与える追加情報です。
（β） 2016/01/19(火) 17:18
この方法で規格とサイズを使ってどんどん台帳に保存していくと、
サイズに同じ物が増えていきます。
調べた感じではフィルタオプションを使って重複を除いてコピーが出来そうなのですが、
今のコードに反映することが出来ませんでした。
良い方法はありませんか？
（まこさん） 2016/01/20(水) 09:26
2003までは、重複の削除の専門機能がなく、フィルターオプション機能を代用していましたね。 もちろん、2007以降も使えますが、新しく（アップしたコードでも使っていますが）データメニューに 【重複の削除】という専門機能がリリースされています。ＶＢＡコードでいえば、RemoveDuplicats メソッドです。 規格マスタに展開する各規格別のサイズにも、それを適用しましょう。
アップ済みのコードに1行追加しました。
Sub Sample3()
Dim shK As Worksheet
Dim shD As Worksheet
Dim c As Range
Dim x As Long
Application.ScreenUpdating = False
Set shK = Sheets("規格マスタ")
Set shD = Sheets("台帳")
shK.UsedRange.ClearContents
With shD
.Columns("B").Copy shK.Range("A1") '規格
shK.Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes '重複の削除
.AutoFilterMode = False
.Range("A1").AutoFilter
x = 2 'B列
For Each c In shK.Range("A2", shK.Range("A" & Rows.Count).End(xlUp)) '一意化された規格を取り出す
.AutoFilter.Range.AutoFilter Field:=2, Criteria1:=c.Value 'オートフィルターによる抽出
.AutoFilter.Range.Columns("C").Copy shK.Cells(1, x) '規格シートに抽出結果をコピペ
shK.Cells(1, x).Value = c.Value
shK.Columns(x).RemoveDuplicates Columns:=1, Header:=xlYes '重複の削除 ★追加
'そのリストに名前定義
ThisWorkbook.Names.Add Name:="規格_" & c.Value, RefersTo:="=" & shK.Range(shK.Cells(2, x), shK.Cells(Rows.Count, x).End(xlUp)).Address(External:=True)
x = x + 1
Next
.AutoFilterMode = False
End With
End Sub
（β） 2016/01/20(水) 12:17
教えて頂いた重複の削除を追加して、さらに規格の昇順並べ替えや規格の名前の定義も
教えて頂いたコードを参考に自動で行えました。
大変たすかりました。
ありがとうございました。
（まこさん） 2016/01/20(水) 16:30
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.