プロが教える店舗&オフィスのセキュリティ対策術

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

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

1,印刷用(見積書又は予算書)シートA列に部材番号、B列に部材メーカー名、C列に型番(形式)、D列に数量入力、E列に単位、F列に部材見積単価、G列に見積金額(数量×部材見積単価)、
H列に実行単価(仕入れ単価)、 I列に実行金額(数量×仕入れ単価)、J列に利益金額(G列見積金額-I列実行単価)、K列に利益率(この場合、利益金額÷実行単価になるでしょうか?)とします。

2,部材リスト各シートのA列に部材番号、B列に部材メーカー名、C列に型番(形式)、E列に単位、F列に部材単価、、H列に実行単価(仕入れ単価)とします。
例えばシート名がエルボで部材番号はL001~L100・・・・、シート名がチーズで部材番号がT001~T100・・・・。

3,印刷用(見積書又は予算書)シート20行目まで必要項目(見積先、社名、日付など)とします。

4,21行目以降のA列に必要な部材番号を記入(各部材リストシートから部材番号をコピー
などして)すると、それに該当する列項目が記入され、それぞれ金額などが出るようにしたいです。

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

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


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

A 回答 (4件)

VLOOKUP関数のみで単純に計算式を立てることは出来ません


質問の内容から
検索値はL001やT100などの部材番号になりますが、検索する範囲が部材によってシートが異なるので、範囲を単純に指定することが出来ません

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

http://pc.nikkeibp.co.jp/article/technique/20080 …

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

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

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

この回答への補足

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

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

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

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

補足日時:2011/05/19 16:39
    • good
    • 0

回答者の希望としては、自分の商売の(業界の)言葉をやたら出されると、世mン出理解するのが難しくてかなわない。

エクセルでは、そんなことよりも、列と行の形が達成の難易を左右する。
このタイプをつかめないで、説明できないようでは、エクセルの進歩は無い。
何がしたいのか。
(1)コードから名前(部品名)や単価を引きたいのか。それならVLOOKUP関数で出来る。
(2)見積書を発行したいのか。それなら印刷が必要だろう。
Googleででも「imogasi請求書」で照会し、出てきた中に行データから、請求書を作成して印刷する例を説明している
過去の回答がある。
http://oshiete.goo.ne.jp/qa/2798008.html  など(点数通知書の例)
(3)エクセルのシートのデータを基にして
ワードの差込印刷の機能を使う方法もあると思う。

この回答への補足

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

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

>(1)コードから名前(部品名)や単価を引きたいのか。それならVLOOKUP関数で出来る。
 部品コード名(数字だけがよいかと)から拾い上げたいです。

>(2)見積書を発行したいのか。それなら印刷が必要だろう。
 見積書発行のため印刷は必要です。

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

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

補足日時:2011/05/21 11:03
    • good
    • 0

#1です



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

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

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

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

この回答への補足

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

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

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

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

補足日時:2011/05/21 11:12
    • good
    • 0

部品リストは、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枚にまとめて、部品番号を00001からをチーズ、10001からをエルボ、20001からをソケットとかに分けるといいでしょうか?

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

補足日時:2011/05/19 16:49
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!