“学びたい”を刺激するメディア。

2021.07.23

column

日本人初のMicrosoft MVPが指南する、エクセルの金額、日付表示のテク

日本人初のMicrosoft MVPが指南する、エクセルの金額、日付表示のテク

業務で最も使われるソフトウェアといっても過言ではないMicrosoft Excel。その使いこなし方について、日本人で初めてMicrosoftが授与するMVPのExcel部門を受賞した田中亨先生がレクチャーするのがSchooの人気授業シリーズ「田中先生がみんなの悩みを華麗に解決する『今月のExcelテクニック』」です。

2019年7月も、金額の桁数表示や祝日を除いた営業日の抽出など仕事でよくある悩みに田中先生からダイレクトな回答がデモ付きで示されました。
本記事ではその内容の一部を文章でお届けします!

目次

  • クエスチョン:Excelでどんな機能を使っていますか?
  • 桁の多い金額を省略して表す方法は?
  • 祝祭日を除いた営業日を求めるベストな方法は?

 

 

クエスチョン:Excelでどんな機能を使っていますか?

 

 

毎回授業の冒頭でリアルタイム受講生に向けてワンクエスチョンが投げかけられるこの授業。この月の質問は「エクセルでどんな機能を使っていますか?」です。

 

まずピックアップされた回答が「2つの条件、名前の定義」。セルのことをA4などと行列の組み合わせで言い表すことはポピュラーですが、実はセル自体に名前を付けることもできます。意外と知らない人も多い機能ですが、覚えておきたいところです。

 

つづいては「NETWORKDAYS.INYL関数」。日付の計算に関する関数ですが、「もっと簡単なものがある」と先生は話します。次は便利なショートカットキー「F2」。セルの中にカーソルをワンクリックで表示させられるということで先生も重宝しているということです。

 

また、「縦横3ピクセルにしてイラストを描いています」というユニークな回答や「右下のセルをダブルクリックで隣と同じ行数まで自動入力」というテクニックも。セルの右下のフィルハンドをドラッグしてコピーしている人も多いですが、ダブルクリックを活用することで手間を削減できる場合があります。

 

 

 

ほかにもExcel方眼紙、カメラ機能、「Shift +F2」でコメント入力、フラッシュフィルなど、リアルタイム受講生が実践中の多様な機能がピックアップされました。

 


桁の多い金額を省略して表す方法は?

つづいて、投稿フォームに寄せられた質問への回答へ授業は進みます。

 

最初に取り上げられたのが「桁の多い金額を『~千円』『~百万円』とあらわすことがある」という相談者からの「『~万円』『~億円』と表示形式で表すにはどうすればいいですか?」という質問です。

 

まず、「(望んでいることは)表示形式ではできません」と田中先生。その前提を提示したうえで、以下のデモ画面が呼び出されました。

 

 

 

例えば表示形式設定画面で「0,」と入力すれば、数列における「,000」の部分が省略して表示されることになります。
そして、「0,,」とすれば以下のように「000,000」が省略される結果に。

 

 

 

しかし、これには“3桁ずつしか省略できない”“勝手にカンマ以下で四捨五入されてしまう”といった欠点があります。そこで「別のセルで関数を使って四捨五入するなど別な方法をとるしかない、むしろその方がいい」と先生は説明します。

 

2つ目の質問は先日職場のPCにOffice2019を導入したところ、64bit版がインストールされてしまい、32bit版にしか対応していないマクロが動かなくなってしまったという受講生からのSOSです。

 

先生によると、例えばマクロに「Declare」という文字列が含まれる「API」を含むマクロは32bit版から64bit版に移行すると、100%アウトになってしまうとのこと。ほかにもこのような互換性のないマクロは存在します。

 

ただし、ワークシート関数の計算が変わる、グラフがずれるといったほかの不具合は発生しないと先生は断言します。なぜなら、これはExcelとWindowsの関係の問題だから。なお、32bit版と64bit版の違いは扱えるデータの量ですが、それが挙動に表れるのは何億件といった単位のデータを処理する場合の話。普通に業務やプライベートで使う場合において違いはあまりないと考えてよいそうです。

 


祝祭日を除いた営業日を求めるベストな方法は?

ほかの受講生から寄せられた質問が、「結合セルの探し方」と「日付関係の関数特集」「祝祭日を除いた営業日をWORKDAY関数で求める際、データリストは毎年設定が必要なのか、より効率的な方法はないのか」について。

 

「日付を自在に操れるようになったらExcel上級者といっても過言ではない」と田中先生は語ります。

 

最後の質問について「はい、毎年設定が必要です」と先生。この点については25年ほど前から議論が行われており、“うまい方法はない”という結論が出ているそう。そこで先生が提案する一番良い方法は「祝祭日だけのリストをつくる」ということです。

 

 

 

上の祝祭日のリストは、先生がインターネットから拾ってきた情報を入力して制作したものです。そして、1年分の日付が入力されたシートにて、土日かどうかの判定は「=IF(WEEKDAY(A1,2)>5,TEXT(A1,”aaa”)<””)」でWEEKDAY関数を使って行います。さらに「IFERROR(MATCH(A1,テーブル1[日付],0),””)」でIFERROR関数とMATCH関数を使って祝日かどうかの判定を行わせることも可能になります。

 

このカレンダーを1年に一回作成しておくだけで、例えば以下のように「2019/2/8~2019/2/18」の間に営業日数はどれだけあるかを、自動で計算させることも容易になります。

 

 

 

ここでは、以下の計算式が用いられています。

 

=COUNTBLANK(INDIRECT(”B”&MATCH(開始日,テーブル2[日付],0)+1&:B”&MATCH(終了日,テーブル2[日付],0)+1))

 

続けて解説されたのが「結合セルの探し方」。A列から特定の数値が含まれたセルを探したいという場合、通常「検索と置換」ダイアログボックスを呼び出します。ここで「わかりにくいのが……」と先生が赤い四角で強調したのが「検索対象」の欄。「数式」「値」「コメント」の3つからドロップダウンリストで選択できますが、「数式」と「値」はどのように異なるかみなさんは応えることができますか?

 

「実際にやってみます」と先生。まずはA列を対象に検索をかけます。 まず「数式」を選択すると、「検索する文字列」に入力された数値(100)の含まれたセルが選択されます。

 

 

 

一方、「値」にしても見た目の挙動は同じ。

 

しかし、C列を対象にしたところ、「値」では先ほどと同じ操作が行われますが、「数式」ではC2セルの「100」、C3セルの「200」、C4セルの「300」と選択され、C5セルの「100」はスルーされてしまいました。

 

 

 

この理由は、以下画像のD列に示されている通り、C列の数値は計算式をもとに導き出されていることにあります。そう、「数式」の場合、計算式に「100」が含まれているセルだけが検索されたというわけですね。

 

 

 

日付の入力されたシートでこの違いに気づかず、悩む人は少なくありません。迷ったときに思い出せるようにしておきましょう!

 

上記でこの記事で紹介する2019年7月の授業内容は以上です。現在も続く人気シリーズ「田中先生がみんなの悩みを華麗に解決する『今月のExcelテクニック』」。ぜひリアルタイムで受講してみてください!

 

文=宮田文机

今回取り上げたSchooの授業はこちら!
『田中亨先生が教える「今月のExcelテクニック」(2019年7月)様々なセルの種類を探す「検索」テクニック』

 

今日の生放送

おすすめ記事

ペンシルからのプッシュ通知を設定しておくと、新着記事のお知らせなどをブラウザ上で受信できて便利です。

通知を受信しますか?