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

パソコン スキルアップ

公開日：2022.10.03

　自治体が発行している「プレミアム商品券」。地域によってプレミアム率はさまざまだが、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」と記入する

 

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

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

　これで計算式はすべて入力できたが、数字の羅列ではわかりづらいので、わかりやすく単位を付けよう。

C列、D列をドラッグして選択し、「通貨表示形式」をクリックし、円マークとカンマを表示する

 

F列をドラッグして選択し、「Ctrl」＋「1」キーを押して「セルの書式設定」ダイアログを開く

 

「表示形式」タブの「ユーザー定義」を選び、「G/標準」の後ろに「”冊”」と入力する

 

数字の後ろに「冊」という文字が表示された

 

　これは形式で便宜上追加している表示なので、計算も可能だ。同様にE列を選択したあと「Ctrl」キーを押してG列を選択して同じように書式設定画面を開き、「G/標準”枚”」と入力する。これで準備は完了だ。

実際に使った金額を入力して確認する

　では実際に、食事や買い物で利用した金額を入力してみよう。使用日、使用場所、使用金額を入力すると、正常に残りの枚数、冊数、端数の枚数が自動で計算された。

7500円の使用ということは1冊と4枚、8冊から1冊と4枚を引くと、残りは6冊と7枚ということになる

 

　購入した金額（額面の金額）や1冊が何枚つづりなのかなどは、プレミアム率や自分の購入額に合わせて調整してほしい。

※解説用画面はWindows 11上でMicrosoft 365のExcelを使用。一部メニュー名などが異なる場合がある
※第60回は2022年11月7日（月）公開

【ダウンロード】
「時短エクセル」　地域のプレミアム商品券の使用状況をExcelで管理する　のお試しファイルのダウンロードはこちらから（Excelファイル）

執筆＝内藤 由美

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

【T】

あわせて読みたい記事

  • 目的別ショートカットまとめ（第44回）

    【ファイル操作編】右クリックメニューやプロパティを表示するショートカットキー

    パソコン スキルアップ

    2025.01.24

  • 時短エクセル（第86回）

    都道府県別データを3Dマップで表現する

    パソコン スキルアップ

    2025.01.21

  • 目的別ショートカットまとめ（第43回）

    【Windows編】画面のスクリーンショットや切り抜きに便利なショートカットキー

    パソコン スキルアップ

    2025.01.10

「パソコン」カテゴリーから探す

連載バックナンバー

時短エクセル

もっと見る

カテゴリー　一覧