『シートごとに別れているマスターから仕訳伝票を作成』（エクセル初心者） シートごとに科目や補助科目、取引先マスターなどの内容があり、 日付ごとに仕訳を作りたいのですが、どの関数を使えば良いか分かりません。 お教え願いますでしょうか？ ?資料の出し方が各シート毎になっていること。 ?課税区分がマスタ以外になる場合がある。 ?部門、勘定科目、補助、取引先が「52-759-3-2017」とハイフン(-) を挟んで書き込まれていること。 ?合計額の数字を使用すること などとなっております。 ＜ 使用 Excel：unknown、使用 OS：Windows8 ＞ ---- 聞きたいことが私には理解できませんが、 「52-759-3-2017」について、各コードが数"字"で管理されている(0で桁埋めされているなど桁数がかわらない)のであれば、MID関数を使えば、MATCH関数やVLLOUP関数の検索値として使いたい値は、とりだせそうですね。 （もこな２） 2018/10/04(木) 19:59 ---- もこな2さん ご返信ありがとうございます。 素人故どうも解決できませんでした。可能であれば、すぐに処理できる関数ご教示頂けませんでしょうか。 なにせ関数が苦手なので詳しくお教え頂きたくお願い致します。 大変恐れ入りますが、どうぞよろしくお願い致します。 詳しい詳細は以下になります。 引落データと入金データのシートがあり、マスターとして勘定科目マスター、部門マスターなどが別に 各シートごとに分かれています。それを元に仕訳データを作成しますという内容です。 引落データ 引落日 部門-勘定-補助科目-取引先 摘要（そのまま貼付） 小計 合計 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 ---- 回答者は質問者さんの画面が見えるわけではないので、 仕訳!B37、部門!A2:A16、部門!B2:B16　に何が入ってるか 説明がないのでわかりませんが、 MATCH(仕訳!B37,部門!A2:A16,0)でエラーが発生してるのでは？ 検索値について推測したとおり「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 ---- 生々しいデータその2だけどコンプライアンスとか大丈夫なのかなぁ・・・ とりあえず、以前にも指摘したかとおもいますけど「仕分け」と仰ってるけどやりたいことは、”コード番号”から対応する項目名を参照させたいだけなんじゃないです？ 文字化けしてるのか私の環境ではわかりませんが 【コード】が↓みたいなかんじであれば、 部門 勘定 補助科目 取引先 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