LIFE

おすすめ価格比較方法!最安値で家計節約!【excel関数講座】

2020年2月26日

スマホでOK

スーパーに行った時、これってあっちのスーパーの方が安く売ってるかも?と思ったことありませんか?そしてその場でパッパと確認できたら楽ちんですよね!そうなんです、スマホを使えばできちゃうんです!今は便利なアプリがあるし、自分でエクセルで作った表だってスマホさえあればどこでも見れるんです!そんな楽ちんな方法をアラフォー女子kumiが詳しく説明します!

 

 

 

1.スマホで最安値を管理する方法

 

スマホで最安値を管理する方法は

アプリエクセル(スプレッドシート)などがあります

アプリなら分かるけど、PCで作ったエクセルの表はどうやってスマホで見るの?


と思いませんか?

方法はいくつかありますが、一番簡単な方法

googleドライブにアップロードしてしまう方法です


別途スマホにエクセル又はスプレッドシートのアプリ

インストールする必要がありますが

スマホでポチポチ打ち込むのが面倒だわって方にはおすすめな方法です!


最安値を管理するおすすめアプリgoogleドライブの説明は

別で詳しくさせて頂きますが

今回はエクセルで作る最安値比較表の作り方をご説明します!

また、家のPCにエクセルが入ってない方でもgoogleのスプレッドシート

簡単に比較表を作ることができるのでそちらも別で詳しく説明していきます!

 

①最安値比較表

 

今回作った表はこちらです!

最安値比較表

 

この最安値調査で購入する商品を見直し

近所のスーパーからロハコPayPayモール店Amazonに変更した結果

なんと36品目で¥7,740もお得になりました!
 
 
 
お得に購入した方法についてはこちらの記事でご紹介しています
 
 
 
 

 

2.最安値比較表の作り方

 

今回、エクセルでの最安値比較表の作り方を説明しますが

便利なアプリがあるのに何で自分で作るの?


と思ったか方もいるかもしれません

なぜ自分で作ったかその理由は

アプリだと多量なデータを打ち込むのが面倒くさい!
思うように加工ができない!


と思う所が結構あったので、それに対し
エクセルだと

自分で好きなように作れるしカスタマイズもし易いので

ストレスを感じず長く使っていけると思ったからです!

なのでこちらでご紹介する表で参考となる部分だけでも

取り入れて頂けたらと思います!

 

最安値比較表の構成

最安値表 サンプル


まず右からNo(品目数)項目商品名容量単位がきて

次に近所のスーパーロハコAmazonでのそれぞれの価格となっています

最安値を比較している部分は、@(単価)

各商品の税込価格を容量で割って比較をしています!

 

作成工程 4つのポイント

①項目別に行を色分け

 条件付き書式設定で自動で色分けをしています

②最安値を探す

 調査した各安値の単価から最安値を探す

③最安値の背景色を変える

 条件付き書式設定で自動で背景色を変えています

④✔したものを集計する

 購入したい物の合計を把握できるようチェックボックスチェックを

 入れた物だけ集計するようにしています

この順番で作成の仕方を詳しく説明していきます!

 

、その前に後の工程を楽にするポイントをご紹介します!

 

単価@の税込表示について G列


うちの近所のスーパーは税抜表示してるので

税抜額F列(税抜)に入力して、税込額計算式G列(税込)

また、税抜額の欄(F列)が空欄であれば0が表示されるようにしています

セルG11

 

例えば11行目のほんだし600gの場合
G11は =ROUNDDOWN(F11*1.1,0) が入っていて

計算式の意味はまず、税抜額を税込に 

F11(1,429)× 1.1 = 1,571.9  

ROUNDDOWNは切り捨てをする関数で、計算式の最後にが入っていますが

0は小数点以下を切り捨てるという意味なので 

1,571.9 → 1,571 となります

また、税抜の欄が空欄の場合 一つ上のG10の場合

F10が空欄なので 0 → 0×1.1 = 0 なので

そのまま0となります

これをしておくとこの後で説明する最安値を求める時に楽になります

 

それでは各ポイントについて詳しく説明していきます!

 

2-①項目別に行の色を変える方法(条件付き書式)

 

条件付き書式

条件付き書式は、ある条件を満たしたセルの色を変えて

目立たせるようなことが出来ます

今回は、項目ごとに行の色を変えて見やすくしたかったのですが

商品数が異なっていたり、今後商品を追加したり削除したりすると

その度に背景の塗りつぶしをやらなくちゃいけないので

その面倒を省くため自動で色分けをしてくれる設定をします!

具体的には、A列の品目数を示すNoが変わるごとに色分けをする

条件付き書式を設定していきます!

 

ここで今回の条件付き書式keyとなるNo(A列)の簡単な連番を振る方法を説明します!

 

簡単な連番を自動で入力する方法

セルA8

 

A列のNo行を追加削除したりで連番を振り直す手間を省くため

A7の「1」のみ手入力して、A8からは次のような計算式を入れています

=IF(B7=B8,A7,A7+1)

これは項目が変わるとNoに1を追加して連番を振るようにしていて

B列の項目を上下で比較して同じであれば同じ番号を

違っていれば1を足した番号が入力されます

例えばA8の場合

B7(米)=B8(玄米)で違っているので

A7(1)に1を足した「2」が入力されるとなります

 

Point

行を削除した場合、連番の計算式がおかしくなってしまいます
一つ上のセルを1セル分ドラックアンドドロップして計算式をコピーすれば
簡単に元通りになります!
ドラックアンドドロップは1セル分だけで大丈夫ですよ!

 

 

 

条件付き書式 設定の手順

条件付き書式①

条件付き書式①
行の色分けをしたい範囲を全部指定して
 条件付き書式をクリック→新しいルール

 

条件付き書式②

条件付き書式②
数式を使用して、書式設定するセルを決定をクリック

 

 

条件付き書式③

条件付き書式③
①上のようなウィンドウが出てくるので

 ①と同じように =MOD($A7,2) と入力する

②書式をクリックして塗りつぶしタブから好きな色を選択する

③OK→OKで戻る

 

 

解説 =MOD($A7,2) MOD関数


MOD割り算をした余りを求める関数です

この数式だとA7を2で割った余りを求めるという意味です

A7は「1」なので、1÷2=0.5 → 余りがある

この余りがあるという条件を満たすかどうか

条件付き書式の条件としています

なので7行目は余りがある条件を満たすので指定した色で塗りつぶされる

それに対し8・9行目は 2÷2=1 → 余りがない → 塗りつぶさない となります

 

 

POINT $(ドルマーク)について

A7の前に付いてる$(ドルマーク)は

A列を固定するためのものです

これを付けていないと正しく色分けされません!

F4キーで正しい位置に付けて下さい!

 

2-②最安値を探す

 

これは3段階の工程により最安値をピックアップする方法をとっています

step1 各行のスーパー、ロハコ、Amazonの単価を0が表示されないよう算出する(IF)

step2 各行の単価安値を求める(MIN)

step3 求めた単価安値から項目ごとの最安値を求める(MIN、IF)

 

それでは10行目11行目のほんだしを例にして説明します!

 

step1 各行の各単価を求める(IF)0表示なし


ほんだしは近所のスーパーでは450gが、ロハコ、Amazonでは600gが販売していました

g数が違うのでgあたりの単価を算出しますが

該当商品がなく税込額が0となっていてるところを普通に計算すると答えが0となり

後々求める最安値が0という結果になってしまうので、0が表示されない様算出します!

H10には =IF(G10=0,"",G10/D10) が入っています

 

解説 =IF(G10=0,"",G10/D10) IF関数 


IF関数条件にあてはまる場合そうでない場合表示させる結果を変えてくれる関数です

この場合、G10=0 であるなら "" = 空欄にする 

そうでない場合は、G10/D10(G10÷D10)の答えを表示させるという意味です!

セルh10


なので、G10 = 0 → あてはまる → H10は空欄になる

H10の一つ下、H11の場合は =IF(G11=0,"",G11/D11)

G11 = 0 → あてはまらない → G11/D11(1,571÷600)H11=2.62となる

同様の計算式をロハコ単価L列、Amazon単価S列にも入れてあります!

また小数点、第一位で比較にならない場合があるので第二位まで表示させます!

これで近所のスーパー、ロハコ、Amazonの各単価を比較する準備ができました!

 

step2 各行の安値を求める(MIN)


安値の列、T10に =MIN(H10,L10,S10) と入力する

セルT10

 

解説 =MIN(H10,L10,S10) MIN関数 


MIN最小の値を求める関数です

10行目の場合

各単価H10(空欄)L10(1.59)S10(1.43)の中から最小の値を探しを「1.43」を返す

11行目は「2.62」となります

 

step3 求めた安値から項目ごとの最安値を求める(MIN、IF、配列関数)

U10の計算式は

{=MIN(IF((B:B=B10),T:T))}

 

これは配列関数で、この{ }鍵かっこが重要です!

 

ここではstep2で算出したほんだし各行の安値(10行目「1.43」、11行目「2.62」)から

最安値「1.43」を探し出すというのをやりたいのですが

計算式にするとこんな形になってしまいます

 

また、表に新しく商品を追加削除する事によって

範囲検索で指定する最後の行が変わってきます

いちいち計算式をその都度修正するのが面倒なので

行数関係なく列全体を範囲にする工夫もしています!

 

ひとつひとつ詳しく見ていきましょう!

この計算式を分割すると

① IF((B:B=B10),T:T)

② MIN(①) 

 

解説  IF((B:B=B10),T:T)

これは、B:B=B10でB列全体からB10(ほんだし)を探し

それと同じ行のT列数字を探すという意味です!

ほんだしは10行目11行目なので

同じ行T列の 1.43、 2.62 を見つけ出します!

B:BT:T としたのは、行の追加削除で計算式を修正する手間を省くため

B及びT列全体を範囲対象としました!

 

解説  MIN(①)


探し出した1.432.62比較して最小値1.43を返すという意味です

 

これで、各項目の最安値を求めることができました!

 

 

POINT

配列関数なので数式入力後、CTRL+SHIFT+Enterキーで確定させます!
Enterキーのみだと{ }かっこがつきません!

 

 

2-③最安値の背景色を変える(条件付き書式)

条件付き書式

これは先程、2-①項目別に行の色を変える方法(条件付き書式設定)ですが

2-②最安値を探すで算出した最安値と合致することろの背景色を変えていきます

面倒ですが、スーパー、ロハコ、Amazonと別々に条件を指定します

 

スーパーの場合で説明します!

F7~I列の最後の行まで範囲選択する

 条件付き書式をクリック→新しいルール

 →数式を使用して、書式設定するセルを決定をクリック

次の数式を満たす場合に値を書式設定

  =$H7=$U7 と入力する

書式をクリックして塗りつぶしタブ好きな色を選択する

④OK→OKで戻る

ここまでは先程と一緒です

 

条件付き書式④


条件付き書式をクリックルールの管理

 

条件付き書式⑤


三角のボタンで適用される順位を1番目にする

項目ごとに色分けをする条件より、最安値の背景色を変える方の優先順位を上位にします

 

POINT 条件付き書式の優先順位

さっきの条件付き書式とちがうのはこの優先順位です
三角ボタンで優先順位を正しくしましょう!

 

ロハコ(J~L列)、Amazon(P~S列)も範囲を変えて同様に指定すればOK!

 

だいぶ長くなってしまったので、チェックボックスを使用しての集計については

次回じっくりと説明したいと思います

 

 
 
 

まとめ

 

今回は、スマホで持ち歩ける最安値管理表の作り方を説明しました

使用した関数は、ROUNDDOWNIFMODMIN配列関数です

エクセルが使えれば自分で好きな管理表が作れちゃいますよ!

アプリもいいけど、いまいちここがダメなんだよね~なんて思っている方は

参考にして頂けたらと思います!

 

 

 

 

 

 

 

-LIFE
-

© 2020 maroroblog