エクセル２０００で見積書を作成したいです

質問

エクセル２０００で見積書（予算書）を作成したいです。
エクセル素人ですが水道配管部材の見積書（予算書）作成をしようと思ってます。

部材番号記入での見積書（予算書）にしたいのですが、
シート１を印刷用シート、それぞれの部材リスト用の各シート名を作成（エルボ、チーズ、ソケット、
バルソケなどなど）とします。

１，印刷用（見積書又は予算書）シートＡ列に部材番号、Ｂ列に部材メーカー名、Ｃ列に型番（形式）、Ｄ列に数量入力、Ｅ列に単位、Ｆ列に部材見積単価、Ｇ列に見積金額（数量×部材見積単価）、
Ｈ列に実行単価（仕入れ単価）、　Ｉ列に実行金額（数量×仕入れ単価）、Ｊ列に利益金額（Ｇ列見積金額－Ｉ列実行単価）、Ｋ列に利益率（この場合、利益金額÷実行単価になるでしょうか？）とします。

２，部材リスト各シートのＡ列に部材番号、Ｂ列に部材メーカー名、Ｃ列に型番（形式）、Ｅ列に単位、Ｆ列に部材単価、、Ｈ列に実行単価（仕入れ単価）とします。
例えばシート名がエルボで部材番号はＬ００１～Ｌ１００・・・・、シート名がチーズで部材番号がＴ００１～Ｔ１００・・・・。

３，印刷用（見積書又は予算書）シート２０行目まで必要項目（見積先、社名、日付など）とします。

４，２１行目以降のＡ列に必要な部材番号を記入（各部材リストシートから部材番号をコピー
などして）すると、それに該当する列項目が記入され、それぞれ金額などが出るようにしたいです。

現在、部材リストシートを作成しながら（始めたばかりですが）、その箇所をコピーして見積書（予算書）を作成しています。

関数の=VLOOKUP(検索値,範囲,列位置,検索の型)とかでなるものでしょうか？（意味は分かりませんが・・・）

ご教授のほどよろしくお願いいたします。

回答No.2

web2525 · ベストアンサー

VLOOKUP関数のみで単純に計算式を立てることは出来ません
質問の内容から
検索値はＬ００１やＴ１００などの部材番号になりますが、検索する範囲が部材によってシートが異なるので、範囲を単純に指定することが出来ません

部材によって異なる検索範囲を指定する手段としてINDIRECT関数を利用する方法

http://pc.nikkeibp.co.jp/article/technique/20080625/1005458/

準備1・部材リスト各シートの表に対しシート名と同じ名前をつける
準備2・部材番号に対応したシート名の一覧を作成

部材番号から対応するシート名をVLOOKUP関数などで取り込み
INDIRECT関数でVLOOKUP関数の範囲を指定する

こういった手法が必要になります

補足

早々のご回答いただきありがとうございます。

＞部材によって異なる検索範囲を指定する手段としてINDIRECT関数を利用する方法

の参照サイトをのぞいてみました。すぐには理解できませんが・・・。

＞準備1・部材リスト各シートの表に対しシート名と同じ名前をつける
＞準備2・部材番号に対応したシート名の一覧を作成
すいません、どのようにすればよいでしょうか？

回答No.4

imogasi

回答者の希望としては、自分の商売の（業界の）言葉をやたら出されると、世ｍン出理解するのが難しくてかなわない。エクセルでは、そんなことよりも、列と行の形が達成の難易を左右する。
このタイプをつかめないで、説明できないようでは、エクセルの進歩は無い。
何がしたいのか。
（１）コードから名前（部品名）や単価を引きたいのか。それならＶＬＯＯＫＵＰ関数で出来る。
（２）見積書を発行したいのか。それなら印刷が必要だろう。
Ｇｏｏｇｌｅででも「imogasi請求書」で照会し、出てきた中に行データから、請求書を作成して印刷する例を説明している
過去の回答がある。
http://okwave.jp/qa/q2798008.html　　など（点数通知書の例）
（３）エクセルのシートのデータを基にして
ワードの差込印刷の機能を使う方法もあると思う。

補足

素人ながらの質問の仕方で大変すみません。
回答者様には貴重な時間を使っていただき、ありがとうございます。

＞回答者の希望としては、自分の商売の（業界の）言葉をやたら出されると、世ｍン出理解するのが難しくてかなわない。
おっしゃる通りでした。果物に替えてと思いましたが・・・

＞（１）コードから名前（部品名）や単価を引きたいのか。それならＶＬＯＯＫＵＰ関数で出来る。
　部品コード名（数字だけがよいかと）から拾い上げたいです。

＞（２）見積書を発行したいのか。それなら印刷が必要だろう。
　見積書発行のため印刷は必要です。

見積書（印刷兼用）シートと部材情報シートを作成し、そこにＡ列に部品コード番号（4桁くらい）、他の列に部品の定価、仕入れ単価等を記入し、見積書シートの行２０以降に部品コードを入力（又は部材情報シートの部品コード番号からコピー）すると各行の部品コードに該当する（あるいは必要な箇所のみ）
項目を記入できるようにしたいです。不必要な部分は印刷範囲よりはずしておきます。

部材情報シートから、見積書シートの各行に部品コードを入力・・・ということになりますでしょうか。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

#1です

＞＞部品リストは、1枚にしておくこと。
＞拾い上げが簡単、部品リストの印刷が簡単なのと、頻繁に定価金額が変更するので部品リストを分けておりました。
オートフィルタ(必要により並べ替え)でいずれの場合も対応できます。

＞1枚にまとめて、部品番号を００００１からをチーズ、１０００１からをエルボ、２０００１からをソケットとかに分けるといいでしょうか？
T001でも重複さえなければよろしいかと思います。

＞＞部品名(チーズ・エルボなど）の列を設ければよいだけの話。
＞　部品点数がかなり多いので、列はかなり増えますね。でもその方法で行こうと思います。
列が増えるのは1列だけです。使用する行数は増えるでしょうが、65535件分まで1シートで可能ですから。

INDEX/MATCH関数の組み合わせを覚えると応用範囲が広がります
単位によってm単価か本単価のどっちかを選ぶ必要がありそうだと思ったので。

補足

No.４さんと同様、貴重な時間を使ってのご回答ありがとうございます。

No.４さんの補足で記入していますが、この方法だと

=IF(ISERROR(VLOOKUP($B20,部材情報!$B$30:$F$356,2)), "",VLOOKUP($B20,部材情報!$B$30:$F$356,2,FALSE))

こんな感じの関数でいいでしょうか？未だ検証はしていませんが・・・（意味がわからないものでして・・・たまたま似たようなのがありまして）

回答No.1

某HN クロメート(Chromate)（@CoalTar）

部品リストは、1枚にしておくこと。
部品名(チーズ・エルボなど）の列を設ければよいだけの話。

以下理由。
構成により、VLOOKUP関数で何とかなる可能性が出てくる
INDEX/MATCH関数でできるようになる。
シートを分けると、SUBSTITUTE関数やINDIRECT関数または、CHOOSE関数を使うことになり、
数式が複雑化する。
さらに、INDIRECT関数はシート名を変えたときに追従しないので数式を変更する必要がある。
(置換機能でいっぺんに変更できるが)
INDIRECT関数を数多く使うと、全く関係のないセルを編集・削除するだけでも時間がかかるようになり、データ入力もおぼつかなくなる。(今回の場合は当てはまらないでしょうけど)

VLOOKUP関数
http://allabout.co.jp/gm/gc/297725/
INDEX、MATCH関数
http://allabout.co.jp/gm/gc/297772/4/

補足

早々のご回答いただきありがとうございます。

＞部品リストは、1枚にしておくこと。
拾い上げが簡単、部品リストの印刷が簡単なのと、頻繁に定価金額が変更するので部品リストを分けておりました。
1枚にまとめて、部品番号を００００１からをチーズ、１０００１からをエルボ、２０００１からをソケットとかに分けるといいでしょうか？

＞部品名(チーズ・エルボなど）の列を設ければよいだけの話。
　部品点数がかなり多いので、列はかなり増えますね。でもその方法で行こうと思います。