[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『相見積もり書の作成』（吉岡）
お世話になっております。
相見積もり書の作成についてお知恵を貸していただけませんか。
現在の表はこのようになっています。
sheet１
A B C
１ AAA商事 御中
２ BBB商事 御中
３ CCC商事 御中
４
５ 商品名 メーカー（オートフィルター） 個数
６ ネジ A社 １０
７ 金具 B社 ２０
８
sheet２
A B C D
１ A社 AAA商事 BBB商事 CCC商事
２ B社 AAA商事 BBB商事
３ C社 BBB商事 CCC商事
４ D社 AAA商事 BBB商事
５ E社、F社、AAA商事、BBB商事というように１０００行ほど続く
６
A１〜A３が宛先です。
A１〜A３はVLOOCKUP関数が入っており、B６のA社を基準にそれぞれの商社が宛先に入るようになっています。
現在はA社の場合、BBB商事とCCC商事をデリートキーで削除して、印刷、戻るボタンでBBB商事、CCC商事を戻して、次はAAA商事と、CCC商事を消して印刷、というような使い方をしています。
これをもっと自動化できないでしょうか。
オートフィルターでA社を選択するとAAA商事、BBB商事、CCC商事あての印刷が自動でされる、AAA商事、BBB商事、CCC商事を自在に表示したり、非表示にしたりなどそのようなことは可能でしょうか。
漠然とした質問で申し訳ありませんが、どうか力添えをよろしくお願いします。
＜ 使用 Excel：Excel2019、使用 OS：Windows10 ＞
ご希望の回答では無いと思いますが、 相見積り先が3社が上限だとしたら、1つのシート内に3ページ分用意して、 各ページにそれぞれSheet2のB列、C列、D列を参照するようにしてみては。 ケースによって2社なら、印刷するページを2ページまでにすれば良いでしょう。 3ページ分用意するといっても、2〜3ページは1ページ目をセルごと参照させれば良いですし。 結果として、各社宛ての書類が印刷されると思います。
質問に対する回答は、引き続き他の方の回答をお待ちください。 （TKG） 2022/03/22(火) 20:44
そのあたりのルールを明確にされると、コメントが付きやすいかもしれません。
（γ） 2022/03/22(火) 20:45
１．Sheet1のA2セルに下記入力規則を設定する。
入力の種類→リスト
元の値ボックス→ =INDEX(Sheet2!$B$1:$D$1300,MATCH(LOOKUP(1,0/SUBTOTAL(3,INDIRECT("B"&ROW(A$6:A$900))),B6:B900),Sheet2!A:A,0),0)
２．Sheet1のB5セルのオートフィルタを実行してから、A2セルを選択する。 A2セルに入力規則のリストが出るので、「上から順に選択→印刷する」を繰り返す
(半平太） 2022/03/22(火) 22:04 (修正23:11)
返信が遅くなって申し訳ありません。
TKGさん、アドバイスありがとうございます。
γさん、このような表を最終的に作っています。
ネジの場合
A B C
１ AAA商事 御中
２
３
４
５ 商品名 メーカー（オートフィルター） 個数
６ ネジ A社 １０
７
８
A B C
１
２ BBB商事 御中
３
４
５ 商品名 メーカー（オートフィルター） 個数
６ ネジ A社 １０
７
８
A B C
１
２
３ CCC商事 御中
４
５ 商品名 メーカー（オートフィルター） 個数
６ ネジ A社 １０
７
８
という３パターンを作成します。
宛先がA１、A２、A３とズレてしまうので、A１に固定出来たら大変うれしいです。
宛先は最大で６社（AAA商事、BBB商事〜FFF商事という具合）になる場合があります。
オートフィルターをしているB６のメーカー（A社）に対して、sheet2のB１〜D１の見積先を宛先に表示しています。
半平太さん、具体的な方法をありがとうございます。
これから試してみます。
重ね重ねお礼申し上げます。
（吉岡） 2022/03/23(水) 14:47
[A] [B] [C] [1] A社 ▼ [2] BBB商事▼ 御中 [3] [4] [5] 商品名 メーカー（オートフィルター） 個数 [6] ネジ A社 10 [7] 金具 B社 20
Sheet2>>
[A] [B] [C] [D] [1] A社 B社 C社 D社 [2] AAA商事 AAA商事 BBB商事 AAA商事 [3] BBB商事 BBB商事 CCC商事 BBB商事 [4] CCC商事
このようなシートを作って、
1）Sheet1のA1に入力規則のリストで社名を選択するようにする。
2）Sheet1のA2にA1セルに連動したリストを選択できるようにする。
詳しくは、
「入力規則 リスト 連動」で検索してください。
やり方の画像付きの解説がいくつか見つかると思います。
>A社を選択するとAAA商事、BBB商事、CCC商事あての印刷が自動でされる
選択を切り替えて、印刷をくりかえすということを自動化するなら、
マクロを作ることになりますが、手を出さない方が幸せではあると
思います。（逆にここで頑張れば、後で楽ができるのですが。。。）
（まっつわん） 2022/03/23(水) 15:25
まっつわんさんもありがとうございました。
検索して勉強したいと思います。
この相見積もり表が完成すれば大きな時間短縮になるのでVBAは大変ですが、実装したいと思います。
半平太さんの数式をいじっていたらうまく行きました。
本番の環境ではオートフィルターはD８にあります。
元のボックス
=INDEX(sheet2!$B$1:$D$1300,MATCH(LOOKUP(1,0/SUBTOTAL(3,INDIRECT("D"&ROW(C$9:C$74))),D9:D74),sheet2!A:A,0),0)
数式をバラバラにして検索したら何をしているかなんとなくわかったのですが、以下の部分は何をしているのでしょうか。
INDIRECT("D"&ROW(C$9:C$74))),D9:D74)
表の範囲はA９〜I７４まであります。
そのうちD９でオートフィルターを使用しているので、"D"になると思うのですが、(C$9:C$74))),D9:D74)は何をしているのでしょうか。
（吉岡） 2022/03/23(水) 23:20
=INDEX(sheet2!$B$1:$D$1300,MATCH(LOOKUP(1,0/SUBTOTAL(3,INDIRECT("D"&ROW(C$9:C$74))),D9:D74),sheet2!A:A,0),0)
うまく動くのですが、ドロップリストが常に３行の表示で固定されています。
見積もり先は最大１０件になるのでどこを変えたら良いのでしょうか。
見積もり先が１件の時も空白が２つ入ってしまいます。
なんとなくわかったと大きな口を叩いてしまって恥ずかしい。
申し訳ありませんが、再度よろしくお願いします。
（吉岡） 2022/03/23(水) 23:30
＞この相見積もり表が完成すれば大きな時間短縮になるのでVBAは大変ですが、実装したいと思います。
VBAでやった方が良さそう。
１．後記マクロをSheet1のシートモジュールに貼り付ける(標準モジュールではないです。念の為）
２．Sheet1のD8セルのオートフィルタを実施する。(日常作業はここから）
３．Sheet1のA1セルを右クリックする。
すると、対象となる商事名が順に出て来て
"No.1 AAA商事 を印刷しますか？"
などど聞かれるので、Yes/Noボタンをクリックして答えればよい。
’<シートモジュールに貼り付けるマクロ>
’ ↓
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim rCriteira As Range, wsCust As Worksheet
Dim rAddressee As Range, ListRowV, RW
Dim Serial As Long, i As Long, Cols As Long
If Target.Address <> "$A$1" Then
Exit Sub
ElseIf Me.FilterMode = False Then
MsgBox "オートフィルターを掛けてください"
Exit Sub
Else
Cancel = True
Set rCriteira = Range("D8").End(xlDown)
End If
Set wsCust = Worksheets("Sheet2") '←実際の宛先シート名に変更してください。
Set rAddressee = wsCust.Range("A1", wsCust.Cells(Rows.Count, "A").End(xlUp))
RW = Application.Match(rCriteira, rAddressee, 0)
If IsNumeric(RW) Then
Cols = Me.UsedRange.Columns.Count
ListRowV = rAddressee(RW, 1).Resize(, Cols).Value
Serial = 0
For i = 2 To Cols
If ListRowV(1, i) <> "" Then
Serial = Serial + 1
Range("A1") = ListRowV(1, i)
If vbYes = MsgBox("No." & Serial & " " & ListRowV(1, i) & " を印刷しますか？", vbYesNo) Then
Me.PrintOut
End If
End If
Next
Else
MsgBox "該当ありません"
Exit Sub
End If
End Sub
（半平太） 2022/03/24(木) 17:55
ありがとうございます。
あなたは弊社の恩人です。
なんとお礼を言えば良いのか、適切な言葉が見つかりません。
ただただ感謝。
本当にありがとうございました。
（吉岡） 2022/03/24(木) 21:53
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.