Excel応用「フリマの手数料、送料、サイズ」の【計算機】作成方法。

こんな悩みを解決します。

最後まで読んで頂き、実践して、ここへ来なくなった暁には、

あなたはスキルアップしている事でしょう。

もし、サクッと計算だけしたい場合は・・・

「フリマの手数料と送料」WEB計算機【利益と出品価格】を算出します

⇧こちらにWEB計算機も用意してありますので、どうぞご利用下さい。

さて、この記事の題目は

Excel応用「フリマ計算機」って何？

 

フリマアプリで商品が売れた際に、

手数料「10%」がかかり、発送をして「送料」もかかります。

例えば「1000円」の商品が売れたら、手数料10%の「100円」がかかり

送料が「450円」であった場合は「1000円」-「100円」-「450円」=「350円」

わかってはいるものの、結構ショックですよね・・・

「初心者の方で、送料が高くてマイナスになった」なんて方もいるかもしれません。

そこで手軽に確認出来る計算機を作成しました。

上は「設定金額から利益を計算」する計算機です。

赤い枠の「設定金額」の入力と「送料」の発送方法を選択する事で

赤い下線「利益」を計算します。

 

下は「狙い利益から設定金額を計算」する計算機です。

青い枠の「設定金額」の入力と「送料」の発送方法を選択する事で

青い下線「利益」を計算します。

 

おまけでサイズの計算機と、注意事項の表示機能を追加しました。

それでは、作り方を紹介させて頂きます！

 

1限目「フリマ計算機」の作り方：レイアウト

 

レイアウトはすでに完成形をお見せしている通りですが

計算に必要な項目考えて、それを入力するセルを用意して配置するだけです。

今回の例の様に、入力操作をするセルは青、中間計算は黄色、結果は赤、等

セルの色分けをして、使用時にどこを操作するのかを判別しやすくするのも良いでしょう。

 

2限目「フリマ計算機」の作り方：手数料計算

仕組みを入れたセルのみを解説していきます。

赤で囲んだ「手数料%」は「セルの書式設定」のパーセンテージを使用しています。

 

Excel機能について、もっと詳しく知りたい方は

↑コチラをご覧ください

 

次に「手数料を引いた後の金額」の計算結果です

例として、「メルカリ」さんでは、

手数料が切り上げで計算されている様なので

「ROUNDUP」という数値を切り上げする関数を使用します。

式の書き方は「=ROUNDUP(数値,桁数)」となります

「設定金額」から「設定金額」*「手数料%」を引いて「手数料を引いた後の金額」を出す為

今回のセル名に置き換えて式を作成すると「=ROUNDUP(B6-B6*C5,0)」になります。

最後の「,0)」は表示する桁数ですが、金額に小数点以下は使用しないので「0」とします。

 

3限目「フリマ計算機」の作り方：送料計算

送料の部分は発送方法を「ドロップダウンリスト」から選択する仕様になっています。

「ドロップダウンリスト」を設定をする為の準備として

リストに表示させたい「発送方法」の一覧を作成します。

右隣りには「送料」と「注意事項」も記載しておきます。

 

「ドロップダウンリスト」を設定をするセルを選択して

上のメニューから「データ」-「データの入力規則」を選択します。

 

入力値の種類をリストにし、元の値は一覧を入力した範囲をドラッグで選択します。

今回の一覧を入力した範囲である「=$H$4:$H$9」が入力されます。

この他に「名前の定義」で指定する方法もあります。

 

Excel機能について、もっと詳しく知りたい方は

↑コチラをご覧ください

 

次は、選択された「配送方法」の「送料」を一覧から自動的に抽出させる為に

「VLOOKUP」という関数を使用します。

式の書き方は「=VLOOKUP(検索値,範囲,列番号,[検索方法])」となります。

検索値で選択されている「宅配便60」を、赤い範囲の1列目の中にあるか検索して

一致した行の「2」列目にある値「700」を返すという動作をさせる為

この場合の式は「=VLOOKUP(D5,H4:I9,2,0)」となります。

又、「[検索方法]」の「0」は完全一致「1」は一番近い値を返します。

 

4限目「フリマ計算機」の作り方：利益計算と設定金額の逆算

最後に利益の計算です。

単純に「手数料を引いた後の金額」-「送料」なので「=C6-D6」となります。

 

ここで、下の計算機「狙い利益から設定金額を計算」の説明を先にさせて頂きます。

計算の順番が逆になるので順番を変更して

式を「=VLOOKUP(C15,H4:I9,2,0)」と修正します。

上の計算機との違いは、判定セルの「D5」が「C15」になっただけです。

 

次に「狙い利益」と「送料」を足して「手数料を足す前の金額」の計算をします。

単純に「=B16+C16」となります。

 

さて設定金額となりますが

「設定金額」-「設定金額」*「手数料10%」=「7000」の「設定金額」が知りたいので

こちらを「方程式」を使い、「設定金額」を「x」として計算式を作ると

「x-0.1x=7000」→「x(1-0.1)=7000」→「x=7000/(1-0.1)」となります。

こちらでは「手数料を引いた後の金額」で使用した「ROUNDUP」の逆である

「ROUNDDOWN」という数値を切り下げする関数を使用します。

式の書き方は「=ROUNDDOWN(数値,桁数)」となります

これふまえて、式は「=ROUNDDOWN(D16/(1-D15),0)」となります

少しややこしいのですが「D15」セルの表示は

「セルの書式設定」のパーセンテージで「10%」と表示していますが

計算に組込むと今回の例では「10%＝10／100」で計算された値に変換されています。

つまり「D15」セルを指定した段階で「0.1」という値で計算されるという事になります。

 

5限目「フリマ計算機」の作り方：おまけ機能

送料のサイズには決まりがあります

ゆうパケットは・・・「合計60cm 長辺34cm 厚み 3cm」

ネコポスは・・・・・「合計60cm 長辺34cm 厚み 2.5cm」

ですので、確認用の計算式を作ります。

説明不要と思いますが、縦、横、高さ、の合計をする為に「SUM」を使用します。

 

Excel関数について、もっと詳しく知りたい方は

↑コチラをご覧ください

 

次は、選択された「配送方法」の「注意事項」を一覧から自動的に抽出させる為に

「送料」で使用した「VLOOKUP」関数を使用します。

こちらは「送料」の関数式「=VLOOKUP(D5,H4:I9,2,0)」の

「範囲」と「列番号」を変更すれば完了です。

変更後の式は「=VLOOKUP(D5,H4:J9,3,0)」となります。

 

以上で完成となりますので、動作確認をして下さい。

 

Excel応用「フリマ計算機」まとめ

今回のポイントは「VLOOKUP」で送料が自動的に変わって

そのまま計算に反映されるというところです。

又、値だけではなく注意事項の様な、文字列でも「VLOOKUP」は使えるので

仕事で活躍する場面は沢山あると思います。

 

これで悩みが解決し、一つ成長しました。

最後までご観覧頂きありがとうございました！