Excelの関数だけで簡易的な「今日のゴミ出し確認アプリ」を作る方法

未分類

今回は、ゴミ出しアプリをExcelで作ってみました。
かなり簡易的なものですが、以下のような感じです。

PC画面

Excel画像

スマートフォン画面(今回はスプレッドシートでスマートフォンでも使えるようにしています。)

スマートフォン画像

今日の日付を読み込み、ゴミの種別などが切り替わるようになっています😀

この記事で以下のExcelテクニックが学べます。

  • AND関数とOR関数の使い分け
  • 第○ △曜日などの対応方法
  • 作ったファイルをスマートフォンでも使えるようにする方法(アプリ化)
yukimi
yukimi

通知機能はないんですけど、「今日のゴミってなんのゴミだったっけ?」とわからなくなる人はおすすめです。😄

タイムラインのタイトル
  • 手順1
    メイン画面の作成
  • 手順2
    ゴミの種類と曜日の洗い出しと表の作成
  • 手順3
    関数式の入力①(ゴミ種別を曜日で判定)
  • 手順4
    関数式の入力②(ゴミの詳細情報の抽出)
  • 手順5
    関数式の入力③(粗大ごみなど第○△曜日の判定)
  • 手順6
    条件付き書式の設定
  • 手順7
    スマートフォンにショートカットを作成(アプリ化)

手順1:メイン画面の作成

メイン画像

上記のようにメイン画面を作っていきます。

必要な情報は以下の4つです。

  • 今日の日付を表示するエリア
  • ゴミの種類を表示するエリア
  • ゴミの詳細を表示するエリア
  • 粗大ごみ等毎週出ないものを判定するエリア

タイトルなどはお好みでつけて下さい。

手順2:ゴミの種類と曜日の洗い出しと表の作成

ゴミの種別は何種類あり、それぞれ何曜日にあるのかを調べて予めまとめておくと良いです。

次に準備として、ごみの種類と曜日を表にします。
作成した表は以下のような感じです。

ゴミの種類と曜日の洗い出し表の作成

のちに使用するWeekday関数のデフォルトが日曜日スタートなので日曜日から作っておくといいですよ。

手順3:関数式の入力①(ゴミ種別を曜日で判定)

対象となる場所

やることは、上の日付(Today関数で今日の日付を取得)をもとに、ゴミの種別を判定します。
ごみの種別の判定には、先ほど作成しました表をもとに情報を抽出します。

TODAY関数を使う事で、その日の日付を自動で取得できます。
とても便利な関数ですので、覚えておきましょう。

ゴミの種別を判別する式は以下のとおりです。

=INDEX(Sheet2!$A$2:$D$8,MATCH(WEEKDAY(A2,1),Sheet2!$A$2:$A$8,0),3)

やっていることは

  1. Weekday関数で今日の曜日を判別
    (この時のWeekday関数の戻り値は数値になります。)
    Weekday関数の種類は今回は1で設定しています。(WEEKDAY(A2,1)
    この設定は1=日曜日、2=月曜日…7=土曜日となります。
  2. テーブルのNoとWeekday関数で求めた数値を照合
  3. 3列目のゴミの種別にあるデータをピックアップ

です。

2と3でやっていることはVlookupと同じことをやっています。

手順4:関数式の入力②(ゴミの詳細情報の抽出)

手順3と同じで、抽出する先の情報を変えるだけです。

=INDEX(Sheet2!$A$2:$D$8,MATCH(WEEKDAY(A2,1),Sheet2!$A$2:$A$8,0),4)

変わったところは、一番最後の数字(列番号)が3→4に変更しただけです。
図にすると以下のようなイメージです。

列番号変更のイメージ

メイン画面を見ると、ごみの種別・詳細がそれぞれ抜き出せるようになっているはずです。

この段階では、詳細の方が改行されていない状態になっていると思います。
改行方法ですが、セルを選択し右クリック → セルの書式設定 → 配置タブ → 折り返して全体を表示するにチェックを入れます。

セル内改行する方法

これでセル内が改行されます。

手順5:関数式の入力③(粗大ゴミなど第○△曜日の判定)

続いてはこちらの、粗大ごみなど毎週ではなく、決まった曜日の判定の方法です。

決まった曜日の判定

ここで重要なのが、曜日と週を言語化し数式にすることです。

今回のケース、第1と第3金曜日が小物金属と粗大ごみになっているので、それを言語化します。

まずは曜日ですが、Weekday関数を使うと6が金曜日になります。

第1の求め方は、第1週目となる日は1~7日の間のいずれかの日です。
ここで日付を求める関数Day関数を使い、Day関数が1~7日の範囲(1以上7以下)が第1週になります。
下図のように金曜日の第1週は1~7のいずれかで始まる7パターンのみ(他の曜日でも同様)

第1週の日にちイメージ

同じように第3週は15日~21日の間になりますので(15以上21以下)となります。

これを言語化すると、Weekday関数が6の時かつDay関数が1以上~7以下又は15以上~21以下が○、それ以外は×とします。

それを更に数式にしたものが以下のものです。(セルA2はToday関数で求めた値を参照しています。)

=IF(AND(WEEKDAY(A2,1)=6,OR(AND(DAY(A2)>=1,DAY(A2)<=7),AND(DAY(A2)>=15,DAY(A2)<=21)))=TRUE,”○”,”×”)

これで、ゴミの種別と粗大ごみなどの判定する方法が完了しました。
あとは、条件付き書式を使って、背景色を入れわかりやすくしたら完成です。

手順6:条件付き書式の設定

条件付き書式は、ゴミ種別に表示されるものによって色を変えるようにしています。

条件付き書式はホームタブ → 条件付き書式 → 新しいルール から作成ができます。

条件付き書式の設定
条件付き書式の設定②

式はゴミの種別部分を変更するだけです。
式をコピーしながら作るとスムーズです。

手順7:スマートフォンにショートカットを作成(アプリ化)

yukimi
yukimi

今回作成した、Excelファイルはスマートフォンでも使う事が可能です。
スマートフォンで使えることで、手軽に確認ができるようになりますので、より便利になります。
事前にGoogle アカウントを入手が必要です。

まずは、Google Driveにアクセスし下図のエリアにExcelファイルをドラッグ&ドロップします

Google  Driveへアップロード

アップロードした後は、一度スプレッドシートでファイルを開きます。

GoogleスプレッドシートとしてExcelファイルを開くのですが、この状態だとToday関数が正しく動かない場合があります。
理由はタイムゾーンの設定が日本になっていないため起きる現象で、それを修正する必要があるためです。

スプレッドシートで開いたら、ファイル → 設定の順にクリックして、設定画面を開きます。
設定画面を開きましたら、以下のようにタイムゾーンを日本に合わせます。

タイムゾーンの設定

設定ができたら、スプレッドシートは閉じて大丈夫です。

次はスマートフォンでの操作になります。
手順にすると以下の流れです。

  1. スマートフォンでGoogle Driveを起動します。
  2. 該当のファイルの…をタップします。
  3. 設定のホーム画面に追加をタップします。
  4. 自動的に追加をタップします。

下図が手順のイメージです。

これでアプリを起動すれば、閲覧できます。

スマートフォンのサイズによっては、見切れてしまっている場合があるかと思います。
その時は、指でピンチイン・アウト(縮小・拡大)を使って調整してみてください。

まとめ

いかがでしたでしょうか。

今回のExcelを使ったファイルは、通知機能がついていないためバッテリーの減りの心配がありません。
引っ越し等で、ゴミ捨てのルールが変わることがあるかと思いますが、それに合わせて表を変えてあげるだけで、使えますので汎用性もいいです。

ぜひ使ってみてください😀

コメント