夢見る税理士の独立開業繁盛記

神戸市東灘区で開業している駆け出し税理士の、試行錯誤日記

Excelの関数で、空白を詰めたリストに組み替えるときは?

前回の日記に引き続き、エクセルのリストのお話。

前回と同じ「預金通帳明細→弥生会計仕訳日記帳変換シート」ですが、今度はところどころに空白がある列を入力規則でリスト入力することに。
こういうリストをそのまま範囲指定してしまうと、ドロップダウンリストも以下のような感じで歯抜けになってしまい、入力がちょっとまどろっこしくなってきます。
f:id:kobarin:20170920174105j:plain


このリストから空白を詰めたリストを作成し、それをリスト入力の範囲とすると、ドロップダウンリストも歯抜けにならずきれいになるのですが、以下のような配列数式を使うとVBAを使わずとも空白を抜いたリストに自動的に変換できます。
f:id:kobarin:20170920154023j:plain

=[IFERROR(INDEX(D:D,SMALL(IF($D$9:$D$1300<>"",ROW($D$9:$D$1300)),ROW()-8)),"")]

何をやっている数式か、自分でもよく分からなくなってきますが(^^;、内側の関数から見ていくと

  1. IF関数を使って、D列で空白じゃないセルがあれば、その行番号を返す。
  2. SMALL関数を使って、1の行番号と、数式が入っているセルの行番号の、どちらか小さい行番号を返す。
  3. INDEX関数を使って、2の行番号に該当するD列の値を返す
  4. IFERROR関数で、エラーが出た場合は空白とする。
  5. これを範囲内のすべてのセルに対して行う。

うーむ・・・、自分で手順を書いていても、頭がこんがらがってきそうです・・・。


この配列数式や配列、関数でもVBAでも使いこなせると便利だと思うのですが、個人的にはどうしても苦手意識が強いです。作業セルを使って二段階で計算できる場合には、ブサイクでもそちらの方法を選んでしまいます。
数式を書いていても、一つの配列数式で複数の計算を同時に行うというのが、なかなかイメージしにくいのですよね・・・。
この辺りはやはり文学部の人間の頭の限界でしょうか(^^;?


※文学部でググってみると、こんな記事がヒット。
withnews.jp
母校の文学部の卒業式の式辞ということなのですが、

「しかし、文学部の学問が本領を発揮するのは、人生の岐路に立ったときではないか、と私は考えます」

大学を卒業してから、友人からは渡り鳥人生と笑われる人生を送ってきましたが、自分自身で振り返ると「好きなようにやってきた割には、大きな失敗もせずうまくやってきてるなあ」と、まあ今のところは満足できる結果になっているような気がします。
確かに人生の岐路に立つたびに、その時点での自分を客観視した上で結論を出して選択を行ってきましたが、「ここまでは大丈夫かな」と自分の能力の見極めができたのは文学部の史学科で勉強した賜物なのかしらん・・・。


神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です

エクセルの入力規則で、リストの範囲を可変にするには

私の事務所のホームページで公開している「預金通帳明細→弥生会計仕訳日記帳変換シート」。
銀行の入出金データなどを弥生会計に取り込める形式に自動変換するエクセルシートなのですが、この度このファイルを自社用にカスタマイズしてほしいとのご依頼をいただきました。ありがとうございますm(_ _)m。


ご要望の一つに、取り込める銀行口座(補助科目)を現行の5個よりもっと増やして欲しいというものがあったので、30個まで増やしてみたのですが、そうするとドロップダウンリストがびろーんと間延びした感じに・・・。
f:id:kobarin:20170915185829j:plain
入力規則で右のF列を参照しているのですが、エクセルの入力規則で参照先を固定セルにしてしまうと、件数が多い場合はちょっと使いづらくなってしまうんですよね。


こういった場合には、入力規則の参照先にOFFSET関数を使うと、値が入力されているセルだけを参照してドロップダウンリストにすることができます。
以下のように、OFFSET関数で先頭行から、値が入力されたセルの数だけ参照範囲として行数を下に伸ばすというようにしておくと
f:id:kobarin:20170915185839j:plain
こんな感じで、セルに4件入力があると、リストも4件だけを参照範囲にすることができるのですよね。
f:id:kobarin:20170915185849j:plain
入力件数が10件になれば、リストも10件。3件に減らせば、リストも3件と、自動でリストの範囲が変わって行くことになります。
OFFSET関数はINDIRECT関数と並んでなかなか直感的に分かりにくい関数ですが、入力規則を使う場合この二つの関数が使えると、活用の幅がぐんと拡がります。



※私の所属する税理士さんの団体で、10月に不動産の評価に絡む研修が二連続で開催されることになりました。
f:id:kobarin:20170915084600j:plain
この数か月不動産周りの法律などの勉強をしているので、都市計画法建築基準法の知識やそれを実務で具体的にどう活用していくのかの研修は、バタバタしている中でもやっぱり出席したくなる誘惑に駆られてしまいます。
勉強することは昔から好きでしたが、不惑の年を二年ほど過ぎた今でも、未だに何か新しいことにチャレンジしたり、新しい知識を学ぶことはワクワクしてしまうのですよね(^^;。


神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です

独立開業体験記集を作成することになりました

私の所属する若手税理士の団体で、独立を目指している若い税理士さん向けに、独立開業体験記集を作成することになりました。
若い会員の中には勤務税理士をしながら、いつかは独立と考えている方が結構いらっしゃるので、そういう方が独立開業を決心するための後押しになればという企画なのですが、三十歳代、四十歳代で開業してそれなりに成功している会員も多いので、個人的にもそんな方の体験記を読んでみたいと楽しみな企画です。


税理士の場合、税理士になるまでにバックグラウンドもいろいろですし、事務所の運営方法も人によって全然違うので、以下のような項目のうち書ける部分について書いていただければなと思っています。

1、プロフィール

2、事務所概要

3、独立のきっかけ

4、事務所の選び方

5、独立形態の決定方法

6、事務員

7、開業資金の額と使途

8、使っている会計ソフト、税務申告ソフトなど

9、パソコン、複合機など

10、ある1週間の業務の流れ

11、顧問先獲得の方法

12、顧問先への対応で意識していること

13、本会会務、支部会務、青税活動などへの対応

14、プライベートはどうしているか

15、ズバリ開業前と開業後を比べて収入は?

16、事務所運営がうまくいくコツは?

17、独立にあたっての失敗談

18、独立してうれしかったこと

19、独立してから大変だった出来事

20、今、不安なこと

21、将来像

22、正直独立してどうか

まだ独立していない若い税理士さんとお話するときには、いつも独立開業を勧めてしまうのですが、税理士事務所は実際なかなか失敗しにくい業種だと思います。
税理士事務所は机とパソコンと電話があれば開業できるといいますが、他業種に比べると設備も仕入も大きな事務所も不要ですし、一人でやる場合には人件費も要らないので、大きく始めない限り設備資金や運転資金はほとんどかからないのですよね。
また資格業で参入障壁も高いので、比較的競争者も少ないと思います。

飲食業や小売業などの場合、多額の設備資金や運転資金が必要な上競争者も多く、今までも結構失敗事例を見てきて失敗したときのダメージの大きさも知っているので、うかつに開業を勧められませんが(^^;、税理士事務所の場合独立のリスクよりリターンの可能性が十分大きいような気がしますし、なにより周りの独立した税理士さんで失敗したという話を聞いたことがないのですよね。
この独立開業体験記集が、若い税理士さんの独立のきっかけの一つになればなあと思います。


※自分の開業体験記、とりあえず書いてみました。
f:id:kobarin:20170815091701j:plain
私も開業して来月でとうとう10年目になるのですよね。
私のような文学部卒で経理と債権回収と会計事務所の経験しかなくて、営業なんて到底できない人間でも、独立してそれなりに満足する結果になったと思っているので、そんな体験も誰かの参考になってくれるかなあ・・・。



神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です

ローテクかもしれないけど・・・

前回の日記で書いたExcel研修の講師をお引き受けした件、すき間時間でレジュメを少しずつ作成中。
f:id:kobarin:20170718175643j:plain
当日はパソコン教室でプロジェクターで説明しつつ実際に手を動かして体験できる形になるので、Excelと会計ソフトを連動させればこれだけ効率的に仕事ができるということを実感してもらおうと、練習ファイルなどもいろいろ作っていると、楽しい反面なかなか終わらないですね(^^;。
f:id:kobarin:20170718182121j:plain


ただこの研修でぜひ持って帰っていただきたいのは、データとして存在する取引データはうまく使えば、そのまま会計ソフトに取り込む方法がきっとあるはずということです。
顧問先が作成したこんなシンプルな現金出納帳でも、
f:id:kobarin:20170718184621j:plain
勘定科目がないから使えないとあきらめずに、今回の研修でお渡しするエクセルファイルを使えば以下の動画のように、

顧問先が作成している出納帳を弥生会計に取り込む方法
弥生会計に取り込むことができる形式に変換することができます。
f:id:kobarin:20170718183819j:plain
クラウド会計などに比べると、Excelを使った取引データの取り込みはローテクで面白みが感じられないかもしれませんが、お金をかけずに手元のExcelだけでもこれだけのことができるんだということを感じていただければ嬉しいですね。



※私の事務所のホームページで公開している「所得拡大促進税制計算シート」。京都の税理士さんから500人対応版も作ってほしいとの依頼をいただいていたので、29年4月以降開始対応版にアップデートするついでに作成してみました。
f:id:kobarin:20170718173624j:plain
納税があり、かつ従業員の給与が増えていないと使えないこの制度ですが、私のお客様でもだんだん使えるケースが増えてきているような気がします。
本日お伺いしたお客様も今期は使えそうな感じだったのですが、この制度が使えそうということは従業員の給与を増やしたうえに利益が出ているという順調な証ですので、やっぱりうれしいですよね(*^^*)。


神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です

上手く使えば早く帰れるExcelの力、伝えられるかなあ・・・?

税理士さん向けのExcelの活用方法の研修講師を依頼いただいたので、どんなことを紹介したらお役に立てるかなと、筆をなめなめレジュメを作っています。
f:id:kobarin:20170629181307j:plain
一番にお伝えしたいことは「データとして存在する取引記録を、会計ソフトに再度手入力することは愚の骨頂」、「仕訳帳など会計ソフトのデータは、そのまま色々な資料を作成する材料になる」ということですが、割と長丁場な研修になりそうなので、関数の使い方やショートカットなど基本的なこともお話ししようかなと考えています。


税理士登録して色々な税理士さんとお話しするようになってから時々感じることに、「会計ソフトと税務ソフトだけで、Excelをほとんど使わない会計事務所というのも結構あるんだなあ」ということがあります。

新卒で一般企業の経理になって以来、生の販売データや会計データを加工して、請求書などの外部向け資料や売掛金の回収状況などの上司・他部門への報告書類など毎日Excelでガンガン作成していたので、「経理業務にExcelは必須スキル」と思っていたのですが、会計事務所の場合は意外とそうでもない感じなんですよね。

  • 既にExcelに入力されたデータを印刷して、会計ソフトに打ち込みなおす・・・。
  • Excelの表にある数字を、計算機で足し算していく・・・。
  • 取引先ごとの売上金額の集計を、元帳を印刷して、マーカーを引いて、それを計算機で足し上げる・・・。

Excelに親しんできた人間から見たら悪い冗談としか思えない話を、知り合いの税理士さんから聞くたびにトホホな気分になっていたのですが、会計事務所は一般企業に比べて早くから専用機によるOA化が進んでいたことで、逆に専用機一辺倒になってExcelなどの汎用ソフトを使わなくても業務が回る環境が出来上がっているのかもしれませんね。


会計・経理業務とExcel、これ以上相性のいい組み合わせは滅多にないはずです。「会計ソフトと税務ソフトだけで十分」と思っている税理士さんや会計事務所の職員の方に、Excelを使えば早く帰れるからもっとExcelと仲良くなりましょうって、そんなことをお伝えできる研修にしたいなあと思います。


※味気ない文書にイラストでもと、レジュメにいらすとやさんの画像を使わせていただきました。
f:id:kobarin:20170629181320j:plain
宮本武蔵のイラストまであるのがすごいです(^^;。


神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です

だんだん記載欄が増えていく・・・

私の事務所のホームページで公開しているエクセルの「所得拡大促進税制計算シート」ですが、29年4月以降開始事業年度から計算方法がかなり変わるので、改良版を作ろうと検討中。


とりあえず新しく公開された別表(別表六(二十三))をエクセルで作ってみました。
f:id:kobarin:20170612151242j:plain

上半分の記載欄は22個。以前のものは13個だったので、だいぶん計算欄や判定欄が増えてしまってますよねえ。
f:id:kobarin:20170612151401j:plain
特に右側の判定がかなりややこしくなった感じです。
f:id:kobarin:20170612151251j:plain
4月以降開始事業年度とそれより前の事業年度も同じ別表で計算できるようにしているのが、ちょっと無理しているというか・・・。


29年4月以降、特に中小企業は、

「雇用者給与等支給額の基準年度(24年)の雇用者給与等支給額からの増加額の10%」+「雇用者給与等支給額の前年度の雇用者給与等支給額からの増加額の12%」

と制度がぐんと拡充されて良くなっているのですけど、その分計算が煩雑になってしまうのは致し方ないのでしょうかねえ。
エクセルファイルも、がんばって仕上げてみたいと思います。


6月より所属する支部の調査研究委員長になってしまったので、出来上がったら新しい所得拡大促進税制、勉強会のテーマに自薦してみようかな(^^;。


※とりあえず適当な数字を入れて動作テストをしてみましたが、前年の数字などにももちろんよりますが、上乗せ分の12%は結構インパクトある感じです。
f:id:kobarin:20170613175912j:plain
今までの倍ぐらいの控除額になるケースも、結構出てくるのかもしれませんね。


神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です

「こんなことできたらいいな・・・」が実現できるのがVBA

建設業許可を取っているお客様があると、毎年の決算変更届に必要な財務諸表の作成を依頼されることがあります。

この財務諸表、税務で作成した決算書を下記のような決められた書式に転記しなければならず、また若干勘定科目の組み換えも必要ということで、単純な割には結構面倒な作成作業になってしまいます。
f:id:kobarin:20170530180620j:plain

毎年発生するこの作業をどうにか楽に済ませたいと去年作成したのが、こちらのホームページからダウンロードできる「建設業許可決算書自動作成シート」です。
f:id:kobarin:20170530175657j:plain
弥生会計の試算表データをエクスポートして取り込むと、貸借対照表、損益計算書、完成工事原価報告書、株主資本等変動計算書をほぼ自動で作成します。
今年もこのファイルを使ったところ、前回まで決算書から手で転記して、勘定科目組み替えて、内容チェックしてという作業に数十分はかかっていたのが、今回は弥生会計からデータを出力して、取り込んで、内容チェックしてという感じで10分もかからず終わらせることができました。

このファイルのように個人的に「こんなことができれば楽なのに・・・」と思っても、ニッチすぎて他にニーズがないようなものの場合、それを自分で実現できるのがこのExcelVBAですよね。
VBAはパソコンにExcelが入っていればすぐに作成を始めることができますし、私のような文学部出身の人間でも、仕事の片手間に時々触っているだけでもなんとなく使えるレベルにはなるほどハードルは低いので、「この作業、単純なのに時間だけかかって本当に面倒・・・」というような作業があれば、VBAは頼りになる解決法の一つだと思います。
私もそうでしたが、「この毎日やってる不毛な作業を自動化出来たら、もっと早く帰れるのに」というような(不純な?)モチベーションがあれば、VBAは意外とすぐに使えるようになる気がしますね(^^;。


神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です