[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『シートごとに別れているマスターから仕訳伝票を作成』（エクセル初心者）
シートごとに科目や補助科目、取引先マスターなどの内容があり、
日付ごとに仕訳を作りたいのですが、どの関数を使えば良いか分かりません。
お教え願いますでしょうか？
?資料の出し方が各シート毎になっていること。
?課税区分がマスタ以外になる場合がある。
?部門、勘定科目、補助、取引先が「52-759-3-2017」とハイフン(-)
を挟んで書き込まれていること。
?合計額の数字を使用すること
などとなっております。
＜ 使用 Excel：unknown、使用 OS：Windows8 ＞
ご返信ありがとうございます。
素人故どうも解決できませんでした。可能であれば、すぐに処理できる関数ご教示頂けませんでしょうか。
なにせ関数が苦手なので詳しくお教え頂きたくお願い致します。
大変恐れ入りますが、どうぞよろしくお願い致します。
詳しい詳細は以下になります。
引落データと入金データのシートがあり、マスターとして勘定科目マスター、部門マスターなどが別に
各シートごとに分かれています。それを元に仕訳データを作成しますという内容です。
引落データ
引落日 部門-勘定-補助科目-取引先 摘要（そのまま貼付） 小計 合計
2018/7/27 53-710-5 ｱｽｸﾙ 6月分 \126,574
33-710-10 ｱｽｸﾙ 6月分(志明館) \8,214 33-710-10 ｱｽｸﾙ 6月分(AFA) \6,063 \140,851
科目マスター
勘定科目コード 勘定科目名 インデックス 貸借区分コード 貸借区分 科目区分コード 科目区分名 使用区分コード 使用区分 借方税区分コード 借方税区分名 貸方税区分コード 貸方税区分名 消費税自動計算コード 消費税自動計算 端数処理コード 端数処理 事業区分コード 事業区分名 #NAME? #NAME? 負の名称 資金科目コード 資金科目 借方資金繰項目コード 借方資金繰項目名 貸方資金繰項目コード 貸方資金繰項目名 調整区分コード
100 現 金 0 借方 A1220 現金計 1 使用 0 対象外 0 対象外 0 しない 1 四捨五入 5 第５種(サービス業等) 0 する 1 資金 3
101 小口現金 0 借方 A1220 現金計 1 使用 0 対象外 0 対象外 0 しない 1 四捨五入 5 第５種(サービス業等) 0 する 1 資金 3
110 当座預金 0 借方 A1230 預金計 1 使用 0 対象外 0 対象外 0 しない 1 四捨五入 5 第５種(サービス業等) 0 する 1 資金 3
115 普通預金(MUFG金山） 0 借方 A1230 預金計 1 使用 0 対象外 0 対象外 0 しない 1 四捨五入 5 第５種(サービス業等) 0 する 1 資金 3
135 売掛金(団体管理費) 0 借方 A1300 当座資産計 1 使用 0 対象外 0 対象外 0 しない 1 四捨五入 5 第５種(サービス業等) 0 する 0 非資金 2 売掛金回収 2 売掛金回収 1
136 売掛金(その他) ｿﾉﾀ 0 借方 A1300 当座資産計 1 使用 0 対象外 0 対象外 0 しない 1 四捨五入 5 第５種(サービス業等) 0 する 0 非資金 2 売掛金回収 2 売掛金回収 1
140 未収収益 0 借方 A1300 当座資産計 1 使用 0 対象外 0 対象外 0 しない 1 四捨五入 5 第５種(サービス業等) 0 する 0 非資金 2 売掛金回収 2 売掛金回収 1
145 未収入金 0 借方 A1300 当座資産計 1 使用 0 対象外 0 対象外 0 しない 1 四捨五入 5 第５種(サービス業等) 0 する 0 非資金 2 売掛金回収 2 売掛金回収 1
↓上記を元に以下のように仕訳データを作ります。
完成形（ここで使う関数です。）
日付 借方 貸方 摘要
ｺｰﾄﾞ 部門 ｺｰﾄﾞ 借方科目 ｺｰﾄﾞ 補助 課税 金額 ｺｰﾄﾞ 取引先 ｺｰﾄﾞ 部門 ｺｰﾄﾞ 貸方科目 ｺｰﾄﾞ 補助 課税 金額 ｺｰﾄﾞ 取引先
2018/7/27 53 ?Xその他諸経費-人件費・事務所諸経費 710 消耗品費 5 事務所消耗品 1 126,574 ｱｽｸﾙ 6月分
33 ?V講習費-教材費 710 消耗品費 10 寮消耗品(志明館) 1 8,214 ｱｽｸﾙ 6月分(志明館) 33 ?V講習費-教材費 710 消耗品費 10 寮消耗品(志明館) 1 6,063 0 その他 115 普通預金(MUFG金山） 0 140,851 ｱｽｸﾙ 6月分(AFA)
（エクセル初心者） 2018/10/04(木) 23:35
ただ、提示されているものをみると、"仕分け"したいのではなく、連結されたコードから、対応する部分のコードを抜き出して、項目名を抜き出したいといったことじゃないんでしょうか・・・
例えば
（項目１、区分、項目２）が↓のように並んでるとして、
123-1-456 789-0-012
区分コード
0 借方 1 貸方
であれば、区分に注目した場合、5文字目ということは固定になるわけですから、
MID関数で5文字目を取り出して、それを条件にして、VLOOKUP関数やMATCH関数とINDEX関数を組み合わせで対応する項目名導けますよね。
（もこな２） 2018/10/05(金) 07:46
ご返事ありがとうございます。
MID関数で区分コードまでは導けたのですが、MATCH関数とINDEX関数を組み合わせた結果
＃N/Aが出てしまいました。
何がよくなかったのでしょうか？
何度も申し訳ありませんがご教示頂きたくお願い致します。
入力した数式は以下の通りです。
=INDEX(部門!B2:B16,MATCH(仕訳!B37,部門!A2:A16,0))
（エクセル初心者） 2018/10/05(金) 23:45
検索値について推測したとおり「123-1-456」みたいなものからMID関数などで
"文字列"として取り出しているのであれば、検索される側（部門!A2:A16）も
文字列である必要があるとおもいますが、そこは確認されているのでしょうか？
（逆に、検索値に1を掛けたり、0を足したりして、数値にするという手もあります。）
（もこな２） 2018/10/07(日) 12:25
ご返事ありがとうございます。
実力不足の為詳しい内容を教えて頂けないでしょうか？
ちなみに入手金処理の資料と科目や部門マスターを使って仕訳を完成させたいです。
（入出金資料）
引落日 出金先 部門-勘定-補助科目-取引先 摘要(そのまま貼付） 小計 合計
7月4日 東邦ガス 53-752-6-3702 門脇ﾋﾞﾙ302 ｶﾞｽ代 6月分 \3,588
7月4日 東邦ガス 53-752-6-3702 門脇ﾋﾞﾙ303 ｶﾞｽ代 6月分 \3,588 \7,176
部門ｺｰﾄﾞ 部門名
（部門マスター）21?U職業紹介費-人件費
22?U職業紹介費-交通費
23?U職業紹介費-実習生来日渡航費用
24?U職業紹介費-事前研修費
〜
52?Xその他諸経費-相談・支援費用
53?Xその他諸経費-人件費・事務所諸経費
0 その他
（科目マスター）ｺｰﾄﾞ科目名 ｲﾝﾃﾞｯｸｽ 貸借区分ｺｰﾄﾞ 貸借区分 科目区分コード
100 現金 0 借方 A1220
〜
752 水道光熱費 0 借方 P3000
753 支払手数料 0 借方 P3000
754 支払手数料* 0 借方 P3000
作りたい仕訳
日付 部門ｺｰﾄﾞ 部門名 ｺｰﾄﾞ 借方科目 ｺｰﾄﾞ 補助
2018/7/4 53 ?Xその他諸経費-人件費・事務所諸経費 752 水道光熱費 6 ガス(富士ﾌﾟﾚｽ寮)
部門ｺｰﾄﾞをMID関数から取り出しましたが、そのあとの部門名や科目については、どのような
数式で引用できますでしょうか？
そのままコピーできるような形で数式を教えて頂けないでしょうか？
よろしくお願いいたします。
（エクセル初心者） 2018/10/10(水) 23:37
とりあえず、以前にも指摘したかとおもいますけど「仕分け」と仰ってるけどやりたいことは、”コード番号”から対応する項目名を参照させたいだけなんじゃないです？
文字化けしてるのか私の環境ではわかりませんが
【コード】が↓みたいなかんじであれば、
部門 勘定 補助科目 取引先 53 752 6 3702
【部門】マスター
ｺｰﾄﾞ 項目名 53?X その他諸経費-人件費・事務所諸経費 ←これ
【勘定】マスター
ｺｰﾄﾞ 科目名 ｲﾝﾃﾞｯｸｽ 貸借区分ｺｰﾄﾞ 貸借区分 科目区分コード 752 水道光熱費 0 借方 P3000 ←これ
※【補助科目】【取引先】は提示がないので割愛
上記の項目名（科目名）が分かればいいんですよね？
であれば、MID関数を使って必要な部分のコードを抜き出して、MATCH関数＆INDEX関数なりVLOOKUP関数でなり対応する項目名（科目名）を参照するだけではないでしょうか？
ちなみに、”仕分け”って言葉だけ考えると、例えば支出科目は借方に、収入科目は貸方にそれぞれ計上したいときに、科目を条件に、借方と貸方に”仕分け”したい というような感じで使うと思うんですが、そう思うの私だけですかね・・・
ところで、
＞MATCH(仕訳!B37,部門!A2:A16,0)でエラーが発生してるのでは？
とコメントつけたとおもいますが、実際のところどうだったんです？
まだ確認されていないのであれば、そこから始めてみてはどうですか。
（もこな２） 2018/10/11(木) 00:46
52-759-3-2017 について確認 ↓ で間違いないですか？
・52 部門 （2桁） ・759 科目 （3桁） ・3 補助科目（1桁） ・2017 取引先 （4桁）
それぞれの桁数は上のカッコ内のように統一されていますか？
■部門コード「0」について 「2018/10/10(水) 23:37」の中に 部門コード「0」その他 というのがありますが 部門コードには「1桁」もあるんですか？ それとも「0」だけ例外ですか？
例外だとすると、部門コード「0」は「52-759-3-2017」のようなとき どのように入力されていますか？
1）0-759-3-2017 2）00-759-3-2017 3）その他（具体的に）
■コードの抜き出し その「0」は無視して、他はすべて桁数が統一されているのなら 「52-759-3-2017」が、B2セルに入っているとして
部門 ： =LEFT(B2,2) 科目 ： =MID(B2,4,3) 補助 ： =MID(B2,8,1) 取引先： =RIGHT(B2,4)
ただし、見た目は同じ「52」でも、データの型によって「数値」と「文字列」に分かれます。 LEFT、MID、RIGHT関数が返す結果は文字列です（数値ではない）
■文字列 → 数値に ＞MID関数で区分コードまでは導けたのですが、MATCH関数とINDEX関数を組み合わせた結果 ＞＃N/Aが出てしまいました。
おそらくマスターは数値なんでしょう。 マスターが数値なら、MATCHやVLOOKUPの検査値（検索値）も数値にしないと#N/Aエラーになります。
どうすれば数値になるか？ ↓ のように「1」を掛けてやればいいです。
=LEFT(B2,2)*1 =MID(B2,4,3)*1 =RIGHT(B2,4)*1
B37セルに部門コードを抜き出し、C37に部門名を表示なら
B37 =IF(B2="","",LEFT(B2,2)*1) ← B2 は「52-759-3-2017」等が入っているセル
C37 =IF(B37="","",VLOOKUP(B37,部門!$A$2:$B$16,2,FALSE)) または C37 =IF(B37="","",INDEX(部門!$B$2:$B$16,MATCH(B37,部門!$A$2:$A$16,0)))
・数式を入れるのが仕訳シートなら「仕訳!B37」のようにシート名をつける必要はない ・数式をコピーするのなら「$A$2:$B$16」のように絶対参照にすること
■念押し 部門コード「0」についての質問にお答え下さい。
以上 （笑） 2018/10/11(木) 15:19
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.