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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ご要望の一つに、取り込める銀行口座（補助科目）を現行の５個よりもっと増やして欲しいというものがあったので、３０個まで増やしてみたのですが、そうするとドロップダウンリストがびろーんと間延びした感じに・・・。

入力規則で右のF列を参照しているのですが、エクセルの入力規則で参照先を固定セルにしてしまうと、件数が多い場合はちょっと使いづらくなってしまうんですよね。

こういった場合には、入力規則の参照先にOFFSET関数を使うと、値が入力されているセルだけを参照してドロップダウンリストにすることができます。
以下のように、OFFSET関数で先頭行から、値が入力されたセルの数だけ参照範囲として行数を下に伸ばすというようにしておくと

こんな感じで、セルに４件入力があると、リストも４件だけを参照範囲にすることができるのですよね。

入力件数が10件になれば、リストも10件。3件に減らせば、リストも3件と、自動でリストの範囲が変わって行くことになります。
OFFSET関数はINDIRECT関数と並んでなかなか直感的に分かりにくい関数ですが、入力規則を使う場合この二つの関数が使えると、活用の幅がぐんと拡がります。

※私の所属する税理士さんの団体で、１０月に不動産の評価に絡む研修が二連続で開催されることになりました。

この数か月不動産周りの法律などの勉強をしているので、都市計画法や建築基準法の知識やそれを実務で具体的にどう活用していくのかの研修は、バタバタしている中でもやっぱり出席したくなる誘惑に駆られてしまいます。
勉強することは昔から好きでしたが、不惑の年を二年ほど過ぎた今でも、未だに何か新しいことにチャレンジしたり、新しい知識を学ぶことはワクワクしてしまうのですよね(^^;。

★こちらの研修会の案内は、以下のリンクからダウンロードできます。
ご興味ある方は、ぜひご参加ください！
１０月３日「財産評価に必要な、都市計画法・建築基準法」研修会
１０月１９日「減価要因見逃さない、不動産調査（現地調査・役所調査）」研修会

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

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

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

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

１、プロフィール

２、事務所概要

３、独立のきっかけ

４、事務所の選び方

５、独立形態の決定方法

６、事務員

７、開業資金の額と使途

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

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

１０、ある１週間の業務の流れ

１１、顧問先獲得の方法

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

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

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

１５、ズバリ開業前と開業後を比べて収入は？

１６、事務所運営がうまくいくコツは？

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

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

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

２０、今、不安なこと

２１、将来像

２２、正直独立してどうか

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

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

※自分の開業体験記、とりあえず書いてみました。

私も開業して来月でとうとう１０年目になるのですよね。
私のような文学部卒で経理と債権回収と会計事務所の経験しかなくて、営業なんて到底できない人間でも、独立してそれなりに満足する結果になったと思っているので、そんな体験も誰かの参考になってくれるかなあ・・・。

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

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

前回の日記で書いたExcel研修の講師をお引き受けした件、すき間時間でレジュメを少しずつ作成中。

当日はパソコン教室でプロジェクターで説明しつつ実際に手を動かして体験できる形になるので、Excelと会計ソフトを連動させればこれだけ効率的に仕事ができるということを実感してもらおうと、練習ファイルなどもいろいろ作っていると、楽しい反面なかなか終わらないですね(^^;。

ただこの研修でぜひ持って帰っていただきたいのは、データとして存在する取引データはうまく使えば、そのまま会計ソフトに取り込む方法がきっとあるはずということです。
顧問先が作成したこんなシンプルな現金出納帳でも、

勘定科目がないから使えないとあきらめずに、今回の研修でお渡しするエクセルファイルを使えば以下の動画のように、

顧問先が作成している出納帳を弥生会計に取り込む方法
弥生会計に取り込むことができる形式に変換することができます。

クラウド会計などに比べると、Excelを使った取引データの取り込みはローテクで面白みが感じられないかもしれませんが、お金をかけずに手元のExcelだけでもこれだけのことができるんだということを感じていただければ嬉しいですね。

※私の事務所のホームページで公開している「所得拡大促進税制計算シート」。京都の税理士さんから５００人対応版も作ってほしいとの依頼をいただいていたので、２９年４月以降開始対応版にアップデートするついでに作成してみました。

納税があり、かつ従業員の給与が増えていないと使えないこの制度ですが、私のお客様でもだんだん使えるケースが増えてきているような気がします。
本日お伺いしたお客様も今期は使えそうな感じだったのですが、この制度が使えそうということは従業員の給与を増やしたうえに利益が出ているという順調な証ですので、やっぱりうれしいですよね(*^^*)。

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

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

税理士さん向けのExcelの活用方法の研修講師を依頼いただいたので、どんなことを紹介したらお役に立てるかなと、筆をなめなめレジュメを作っています。

一番にお伝えしたいことは「データとして存在する取引記録を、会計ソフトに再度手入力することは愚の骨頂」、「仕訳帳など会計ソフトのデータは、そのまま色々な資料を作成する材料になる」ということですが、割と長丁場な研修になりそうなので、関数の使い方やショートカットなど基本的なこともお話ししようかなと考えています。

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

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

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

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

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

※味気ない文書にイラストでもと、レジュメにいらすとやさんの画像を使わせていただきました。

宮本武蔵のイラストまであるのがすごいです(^^;。

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

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

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

とりあえず新しく公開された別表（別表六（二十三））をエクセルで作ってみました。

上半分の記載欄は２２個。以前のものは１３個だったので、だいぶん計算欄や判定欄が増えてしまってますよねえ。

特に右側の判定がかなりややこしくなった感じです。

４月以降開始事業年度とそれより前の事業年度も同じ別表で計算できるようにしているのが、ちょっと無理しているというか・・・。

２９年４月以降、特に中小企業は、

「雇用者給与等支給額の基準年度（２４年）の雇用者給与等支給額からの増加額の10％」＋「雇用者給与等支給額の前年度の雇用者給与等支給額からの増加額の12％」

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

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

※とりあえず適当な数字を入れて動作テストをしてみましたが、前年の数字などにももちろんよりますが、上乗せ分の12％は結構インパクトある感じです。

今までの倍ぐらいの控除額になるケースも、結構出てくるのかもしれませんね。

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