時短エクセル(第59回)地域のプレミアム商品券の使用状況をExcelで管理する

パソコン スキルアップ

公開日:2022.10.03

  • PDF PDF
  • ボタンをクリックすることで、Myクリップ一覧ページに追加・削除できます。追加した記事は、「Myクリップ」メニューからいつでも読むことができます。なお、ご利用にはBiz Clipに会員登録(無料)してログインする必要があります。

 自治体が発行している「プレミアム商品券」。地域によってプレミアム率はさまざまだが、5000円で6000円分利用できたり、1万円で1万2500円分の利用が可能だったりする。

 この商品券、何枚使ってあと何冊と何枚残っているか、管理するのがなかなか大変だ。そこで、Excelを使い、使用金額を入力すれば残りの冊数と端数の枚数がすぐにわかるよう、計算式を作ってみよう。

項目を入力し、表を作成する

 まずは必要な項目を入力し、表の原型を作成する。テーブルを設定しておけば追加しても計算式を継承してくれる。

必要な項目を入力する

 

使用日の列を指定し、「数値の書式」で「短い日付形式」を設定する

 

商品券の取得日を入力する。「10/5」と入力しただけで「2022/10/5」と表示される

 

3行程度余白を含めて表を選択し、「テーブルとして書式設定」から好きなデザインを選ぶ

 

ダイアログで「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認して「OK」をクリックするとテーブルが作成される

 

プレミアム率に応じて計算式を入力する

 表の原型ができたら、今度はプレミアム率に応じた計算式を入力していく。最初の行は取得日で、使用場所と使用金額は未記入でよい。

「残り金額」欄に取得した商品券の合計額を記入する

 

「残り枚数」欄には「残り金額」を商品券1枚の額面の金額で割る計算式を入力する。ここでは額面が500円として「=D2/500」と入力している

 

 「残り冊数」欄には「残り金額」を1冊の金額で割ればいい。例えば1冊が額面500円×11枚つづりの場合、44000円÷5500円=8冊となる。ただし、毎回ぴったり1冊ずつ使うわけではない。6000円使った場合、1冊と1枚を使うことになり、端数が生じてくる。そういう場合に利用できる便利な関数が、端数を切り捨てる「ROUNDDOWN」だ。

 「ROUNDDOWN」は引数が2つ。1つめが数値で、2つめが桁数。1つめで指定した数値を、2つめで指定した桁数で切り捨てる。「残り金額」(D2)を1冊の金額(ここでは500円×11枚)で割った数字が冊数(数値)だ。それを整数に切り捨てるので、2つめの引数を0とする。

 端数処理する桁数は、小数点第一位を端数処理する(一の位まで残す)ときを「0」とし、それを基に以下のように指定する。

処理する桁              桁数
一の位     →   -1
小数点第一位  →   0
小数点第二位  →   1

「残り冊数」のF2には「=ROUNDDOWN(D2/5500,0)」と入力する

 

 なお、切り上げたい場合は「ROUNDUP」、四捨五入したい場合は「ROUND」という関数を使う。引数や、桁数の指定方法はROUNDDOWNと同様だ。

「端数の枚数」のG2には「=(D2-(5500*F2))/500」と入力する

 

 残り金額から残り冊数の合計額を引いた金額を額面の500円で割れば、端数の枚数が計算できる。

D3は、前行の残り金額から使用金額を引けばいいので「=D2-C3」と記入する

 

 次の行まではドラッグしてコピーしておこう。

わかりやすく単位を付ける…

続きを読むにはログインが必要です

\ かんたん入力で登録完了 /

会員登録3つのメリット!!

  • 最新記事をメールでお知らせ!
  • すべての記事を最後まで読める!
  • ビジネステンプレートを無料ダウンロード!

執筆=内藤 由美

大手ソフト会社で14年勤務。その後、IT関連のライター・編集者として活動。ソフトウエアの解説本、パソコンやスマートフォンの活用記事などを執筆、編集している。

【T】

「パソコン」人気記事ランキング

連載バックナンバー

時短エクセル

オンラインセミナー動画

  • 新着記事

配信期間

2024年9月2日(月)~2025年3月31日(月)

カーボンニュートラル関連

カーボンニュートラル実現への第一歩~温室効果ガスの可視化・削減の最新動向と具体事例~