以下のような按分計算をしたいのですが、解決法ご存知の方、教えて頂けますか?宜しくお願い致します。
テーブルは2つです。"NO."でリレーションしています。この2つは別管理したかったので分けています。「テーブルB」へは"運賃合計金額"をルックアップ値を設定しています。
「テーブルA」…NO.ごとにかかった運賃を入力していくもの。請求書の内容を入力するテーブル。
フィールド…"NO.""運賃合計金額"の2つ。
このタイプ…テキストと数字。
「テーブルB」…NO.には複数商品アイテムがあり、主にその入庫&原価管理するもの。
フィールド…"NO.""商品名""商品代""商品代/NO.ごとの合計金額""運賃按分結果""運賃合計金額"
このタイプ…"運賃按分結果"以外は、すべてテキストと数字。
"運賃按分結果"には、以下のような計算式を入力したい。
Round("商品代金"÷"商品代/NO.ごとの合計金額"×"運賃合計金額")
この計算式をあてはめると按分計算は問題ないのですが、ここからが困っています。
「テーブルA」のNO.には、枝番があるときがあって、必ず一方にトータル金額が請求されます。
例えば、ABC/001は1000円とABC/002には0円というような感じです。枝番があったとしてもNO.すべての商品に按分されるような式を作りたいと考えていますが、上手くいきません。
良きアドバイスを宜しくお願い致します。
No.2ベストアンサー
- 回答日時:
>5、そのとおりなんです。
まさに計算結果は、例にして下さったとおりです。であれば次の方法はいかがでしょうか。
まずテーブルABCとも、それぞれ「No.」の別に例えば「No.記号」とでも名付けたフィールドを作り、タイプは計算とします。
計算式は
LEFT(No.,3)
とします。なお、「3」というのはABCの部分が3字以上あるという意味で、2字の場合があるときは「3」が「2」になります。例では「ABC」が答えになりますね。
2字にした場合、重複値が生じると問題ですが。)
なお、計算結果はテキストにします。(数値にするとルックアップができません。)
そして、運賃合計額も商品代合計額もすべて「No.」でなく、この「No.記号」を基準として入力するわけです。
この「No.記号」を照合値としてルックアップ定義を行います。するとABC/1からABC/4まですべてに同じ運賃合計額(例えば例5の1,000)が入りますね。
それで試されてみてはいかがでしょうか。
(私が試してみた結果ではOKでした。)
1円の誤差の問題は、別にテーブルを作って修正する方法があるかと思いますが、ここでは省きます。
No.4
- 回答日時:
すみません。
実は私、仕事依頼者の関係で専らFM6を使っていて、それでテストしたもんだからあのような表現になり、かえって混乱させましたね。FM7も別のノートPCにあるんですが殆ど家内がPCを独占していて、まだあまり7には馴染んでないんです。
たった今7を開いてみて6と7ではリレーション関係の表現や手順が大幅に変わっていることに気づきました。だから先の補足は無視してください。
同じ内容のテーブルをコピーして使うという意味では決してありません。
要は計算式にテーブルBの「商品代合計金額」及び「運賃合計額」をストレートに引用すると答が出ない可能性があるということを言いたかっただけです。
6ではルックアップの際にリレーション定義をし、それに名前を付けることになっているのでああいう補足をしましたが、気にしないでください。結果がうまくいったとのことですから、少しでもお役に立てて満足です。
何度回答下さり、申し訳ございません。
ファイルメーカーは、バージョンアップしたごとに機能が増えるだけではなくて、使えなくなる機能とかも多くあるんですよね。用途によってバージョンを使い分けてるってことも聞いたことがあります。
私は7しか使った事がないので、リレーション名という使い方が上の説明とみて、やはり違っていました。
ですが、回答NO.2のおかげで解決しました。
本当に有難うございました!
No.3
- 回答日時:
「運賃按分結果」の計算式の設定について、ひとつ書き漏らしたことがありますので補足します。
(余計なお節介かも知れませんが、老婆心ながら追記しておきます)
リレーション名をそれぞれ「テーブルB」←→「テーブルC」の分は「商品代合計」、「テーブルB」←→「テーブルA」の分は「運賃合計」と仮定します。
計算式に引用するフィールドは、現在のテーブル「テーブルB」の「商品代合計金額」及び「運賃合計額」をそのまま持ってこないで、次の式になるよう、計算式の指定・左上欄右の▼をクリックして現れる「商品代合計」及び「運賃合計」の「::商品代合計金額」及び「::運賃合計額」をそれぞれダブルクリックして引用してください。
でないと正しく答が出ないと思います。
Round( 商品代 / 商品代合計::商品代合計金額 * 運賃合計::運賃合計額 , 0 )
この回答への補足
先程のお礼で、
>一応この解釈でやってみて出来たつもりです。
(もし違っていたらご指摘くださると幸いです…)
このような文章を書いてから、しまった~という事に気がつきました。
改めまして、補足というより私がお礼で書きましたリレーション名の解釈について合っているかどうか回答いただけないでしょうか?
何度も申し訳ございません。宜しくお願いいたします。
補足して下さって有難うございます。
回答NO.2で上手くいってたような気がしましたが、補足して頂いた内容で再度チャレンジしました。
リレーション名という言葉がちょっとわからなくて戸惑いましたが、例えば「テーブルB」「テーブルC」というテーブルがそれぞれ同じ内容のものが2つ存在し、そのコピーした(という表現でいいのかな?)テーブル同士を”商品代合計”でリレーションシップを定義する。という風に解釈して作ってみました。(←自己リレーションシップを使用する。ということですよね?)
一応この解釈でやってみて出来たつもりです。
(もし違っていたらご指摘くださると幸いです…)
No.1
- 回答日時:
ご質問の内容がどうしても理解できず、私の頭脳では回答の道筋が立てられませんので、次の諸点について補足説明をお願いします。
1、「NO.」は枝番も含めてA、B両テーブルで完全に一致しているのでしょうか。例えばAにあってBにない(あるいはその逆)ということはないのでしょうか。
また枝番がない単独の「NO,」もあるのでしょうか。
また、枝番の数は2個だけじゃなく、「ABC/001~ABC/009」のように数多く存在していることもあるのでしょうか。
2、「商品代/NO.ごとの合計金額」と「商品代」(式では商品代金?)の関係はどうなっているのでしょうか。
「商品代/NO.ごとの合計金額」とは当該ファイルのレコードにある特定の商品代の合計なのでしょうか。それともファイルにない分の商品代も含まれているのでしょうか。もし前者であれば、例えば商品代が5レコードあれば「商品代/NO.ごとの合計金額」も同じ金額を5レコードに手作業で入力しなければならないと思いますが、その辺はどうなっているのでしょうか。(それぞれに入力しなければ按分の計算式が成り立たない、)
もし、「商品代/NO.ごとの合計金額」のフィールドタイプが計算なら、その式はどうなっているのでしょうか(Excelと違ってSUMIF関数などないので……)
3、テーブルAに入力する「運賃合計額」についても、「按分する」とあるから、同様に1レコード(「NO,」)について1データじゃなく、同じ「商品代/NO.ごとの合計金額」に属する複数の「NO,」の数だけ同じ金額が入力されているような気がしますが(でなければ枝番がない「NO,」について按分の必要がないので。)これも同じ数字を手作業で入力しているのでしょうか。
4、運賃按分額は運賃合計額を「商品代/NO.ごとの合計金額」中に占める「商品代(金?)」の割合で按分する、というように受け取れますが、ROUND関数で計算した場合、按分額のトータルが運賃合計金額と一致しない(1円程度違う)こともあり得ますけど、原価管理が目的だからそれは構わないということでしょうか。
5、最後にこれだけはおぼろげながら分かるような気がしますが、たとえばテーブルAとBに枝番つきの「NO.」がそれぞれ3個ずつあったとしても、テーブルAではその最前レコード(例えばABC/001)にしか運賃合計額が入力されない(テーブルBではルックアップで同じレコード《ABC/001》にしか運賃合計額が反映されない)ので、結果としてABC/002以下には按分額が割り振られない、というように受け取れますが、そういう意味でしょうか。
文章で書くと分かりづらいので、例をあげて説明すると、
NO. 商品代 商品代/NO.ごとの合計金額 運賃按分結果 運賃合計額
ABC/001 50,000 100,000 500 1,000
ABC/002 30,000 100,000 300 1,000
ABC/003 20,000 100,000 200 1,000
のような結果を出したいけど、運賃合計額の1,000円がABC/001にしか反映されないので、ABC/002及びABC/003については、按分額の300円、200円が算出されない。これを何とか解消したい。つまりこういうことでしょうか。もし違っていたら併せて具体例でご説明ください。
とりあえず上記例のようなことだと仮定して私見を述べさせていただきます。
ファイルメーカーProはExcelと違って、MATCH、SUMIFなど検索機能を備えた関数がないのが大きな弱点で、こういう例の場合、全レコードの中から「No.」にABCとあるものを「関数」で検索し、更に条件付き関数で他のデータの数値を引用する、というのはまず無理な相談だと思います。
リレーション、スクリプトを駆使しても枝番データが一種だけならまだしも、複数存在する場合はその数に応じたスクリプト定義をしなければならないなど、合理的な方法ではないでしょう。それをするぐらいなら、前記の例でいうとABC/○○○とあるものを検索モードで検索し、Copy&Pasteで1,000と入力する方が余程早いかも知れません。
これでは答えにならないので、とりあえずこういう場合に私がやっていることを紹介させていただきます。
その方法は一言で言えばExcelを活用する(介在させる)ことです。
幸いファイルメーカーはレコードのインポート、エクスポート機能が充実しているので、先にExcelで表を作るか、又はファイルメーカーに入力したデータをExcel(SILK形式)にエクスポートし、IF、MATCH、LEFT等の関数で必要な計算を行い、その結果をファイルメーカーにバック(インポート)することでです。
Excelでの作業を簡単に文章で要約すると、運賃合計額の右にもう1列設け、本来の運賃合計額欄が空白のときは、「No.」の左3桁の文字(例えばABC)が同じデータの運賃合計額を転記するというような式を設定する訳です。
いずれにしてもこの「運賃合計額」と「商品代/NO.ごとの合計金額」がどういう構成になっているか(つまりレコードにある分の合計なのか、それともレコードにない分の額も含まれているのか)、が分からないと答が出ないので、その説明を承ってからにいたします。
この回答への補足
zenjeeさん、至らぬ質問ですみませんが補足説明させて頂きます。
1、”NO.”は、テーブルAとB両方に存在し、完全一致します。枝番は/002までがほとんどですが、/004くらいまである時がまれになるくらいです。
2、式には”商品代金”と書いてましたが、間違いです。”商品代”が正しいです。
あと、フィールド名が分かりにくい表現になってたようですみません。”商品代/NO.ごとの合計金額”→”商品代合計金額”という書き方に変えてみます。このフィールド”商品代合計金額”は、入力する前にNO.ごとで商品代の合計を算出しておき、手入力しています。
(質問では書いていませんが、”商品代合計金額”はテーブルCという別テーブルがあって、そこでは入力されている為、テーブルBの”商品代合計金額”をルックアップ値で設定する予定です。)
3、「運賃合計額」は、テーブルAで入力し、テーブルBではルックアップ値で設定されています。AとBどちらもタイプは数字です。
4、案分結果は1円などの誤差が生じると思いますので、フィールドを2つ作ろうと思っています。”差額調整用”と「”運賃按分結果”-”差額調整用”」(タイプ:計算)。
5、そのとおりなんです。まさに計算結果は、例にして下さったとおりです。
度々恐れ入りますが、よろしくお願い致します。
補足要求の状態でしたが、ファイルメーカー勉強中の私にとって、参考になりました。
有難うございます。
最初はzenjeeさんのように、具体的には回答NO.1の5、のように、表と質問内容を書いてたのですが、文字数オーバーしてたせいか、なかなか投稿できず、はしょりすぎた結果たくさん補足するはめになりました。
以後気をつけたいと思います。
ファイルメーカーは、インポート&エクスポート機能が優れているんですね。
この按分をするにあたり、この機能を使うとなると、設定にかなりの時間を要しそうですので、今回はパスし何かの機会に使ってみたいと思います。
以前に練習用で一回だけ、テキスト(CSVで合ってますかね?)でエクセルへエクスポートしてみた事がありますが、教えて下さった「SILK形式」やそれ以外にもソフトに適したものなどがたくさんあるんですね。先程本をみて勉強しました。
ファイルメーカーって奥が深いですね・・・。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Access(アクセス) MAXで0が出力される 1 2022/03/31 21:43
- その他(データベース) accessでの請求管理について 2 2022/06/13 21:51
- Excel(エクセル) グループの最後の行に書式、計算式なども同じ行を追加するマクロを教えてもらえませんか。 7 2022/05/18 10:13
- 経営情報システム accessでの請求管理について 12 2022/06/11 16:20
- 財務・会計・経理 管理会計論 運転資金 2 2022/11/21 14:28
- 高校 日商簿記3級の勉強中なのですが 精算表が完成せず困っています。 こちらの問題の回答を教えていただきた 2 2023/03/02 09:07
- その他(データベース) pythonでsqlight勉強中、クエリー結果の利用法教えて下さい 1 2022/04/28 20:38
- 営業・販売・サービス レジ打ちの仕事をしてる方 (飲食店や弁当屋等) レジ締めした時に金額が合わない場合は お釣りの渡した 1 2023/07/14 12:12
- 不動産業・賃貸業 建物、土地一括売却時の按分 2 2023/06/01 09:44
- 消費税 消費税の納税額の計算 1 2023/02/19 18:12
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【SQL】他テーブルに含まれる値...
-
テーブル名が可変の場合のクエ...
-
2つのテーブルをLIKE演算子のよ...
-
既存データをINSERT文にして出...
-
ACCESS クエリーでソートの不具合
-
ExcelのMatch関数のようなもの...
-
三科目合計点のクラス別平均点...
-
「都道府県の面積の大きい順に...
-
親子関係があるテーブルでの抽出
-
SQLサーバに対するSQL文で抽出...
-
副問合せを使わずにUNIONと同様...
-
フラグをたてるってどういうこ...
-
スキーマ
-
SELECT INTOで一度に複数の変数...
-
sqlに記述できない文字
-
エラーを起こす方法
-
Accessで今日から5日後
-
カラム名を変更するには
-
timestampのデータはどのように...
-
SELECTした結果に行番号を求めたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【SQL】他テーブルに含まれる値...
-
テーブル名が可変の場合のクエ...
-
結合したテーブルをSUMしたい
-
既存データをINSERT文にして出...
-
SQLで、Join句で結合したテ...
-
データ無し時は空白行にしたい...
-
Accessの構成をコピーしたい
-
ACCESSのVBAにてExcelに行...
-
2つのテーブルをLIKE演算子のよ...
-
SQLサーバに対するSQL文で抽出...
-
ExcelのVLOOKUP関数の動作をMyS...
-
PRIMARY KEYのコピー
-
同一テーブル内での比較(最新...
-
ExcelのMatch関数のようなもの...
-
ADO+ODBCでテーブルに接続する...
-
Access 別のDBに対してDlookup...
-
改行を含んだデータのインポート
-
ACCESS クエリーでソートの不具合
-
複数のテーブルからデータを取...
-
副問合せを使わずにUNIONと同様...
おすすめ情報