Excel関数で当月に該当する誕生月のメンバーを自動で抽出する方法

未分類

今回は、当月(9月なら9月)に該当する誕生月メンバーを自動的に抽出する方法です。
下図はイメージです。

Excelで作った画像

ファイルを作成した後は、GoogleDriveに保存することで、スマートフォンでも使う事ができるようになります。

スマートフォン画面

この記事は以下の人向きの記事です

  • メンバーを管理するリーダー
  • 学校の先生やケアマネージャー
  • Excelでリストから自動でフィルターをかける方法を知りたい
yukimi
yukimi

誕生日とかに「おめでとう!」って言ってもらえるのは嬉しいですよね。
でも、一人一人の誕生日を覚えるのは難しいです。
月1回(月はじめに)誕生日のチェックができるそんな方法です。

手順
  • 手順①
    メンバーリスト作成
  • 手順②
    誕生月のピックアップ
  • 手順③
    当月誕生月メンバーのピックアップするための設定
  • 手順④
    当月誕生月メンバーのピックアップ
  • 手順⑤
    誕生日の昇順・降順(手動)

手順①:メンバーリスト作成

まずはメンバーリストを作成します。

最低でも必要な項目は4つ

  • 名前
  • 生年月日
  • 誕生月
  • カウント(何人いるかカウント)
メンバーリストの作成

名前と生年月日を入力しておきます。
生年月日は日付形式で入力します。

手順②:誕生月のピックアップ

誕生月のピックアップはTEXT関数を使います。
TEXT関数は、TEXT(値,表示形式)という式になっています。
今回は、月だけ表示するので表示形式は”M”を入力します。

誕生月のピックアップ

手順③:当月誕生月メンバーのピックアップするための設定

当月誕生月のメンバーをピックアップする際、重要なのが

  • 同じ誕生月の人数をカウントすること
  • 指定した月以外は非表示にすること
  • 番号が重複せず、1~始まる連番にすること

です。
その仕組みに必要なのがIF関数とCountif関数になります。
使用している式はこちらです。

=IF($A$1=[@誕生月],COUNTIF($D$3:D3,$A$1),””)

Countifで同じ誕生月の数をカウントする。

IF関数は当月(今回は9月)以外は空白になるようにしています。
カウント列が空白になることで、数字のあるところだけを抽出できるようにしています。

Countifは「範囲」と「検索条件」の情報が必要で、範囲で指定した中に検索条件がいくつあるかを数えます。
一般的にはCountifを使用する際は、範囲を設定する際はセル上:セル下の絶対参照にして使用します。
(この表の場合は$D$3:$D$10)

今回は、番号を連番にする必要があるため、左側を$D$3:D3としています。
このようにすることで、Countifで計算する範囲が下の行に行けば行くほど増えていきます。
(下図は3行目の式を見ると、+2行のD5になっていることがわかります。)

Countifの範囲選択時のポイント

これでCountifで連番を作ることができます。

Countifの検索条件には、セルA1の値を絶対参照にしています。
セルA1には、TEXT(TODAY(),”M”)が入っています。

当月の値を自動で取得する方法

これでExcelを起動する度に当月を抽出するようにしています。
※この時は9月なので、9という数字になっています。

Countifで連番を作る場合は、範囲設定の右側を相対参照にする。

手順④:当月誕生月メンバーのピックアップ

この後は、カウント列をユニークキーとして、情報をIndex関数とMatch関数の組み合わせで抜き出しています。IFNA関数は、値がないものはエラーになりますので空白にするようにしています。

使用している式はこちらです。

=IFNA(INDEX(テーブル3,MATCH(ROW()-2,テーブル3[カウント],0),1),””)

当月誕生月メンバーのピックアップ

やっていることは難しくありません。
テーブル3の表のカウント列を見て、1から順に抽出しているだけです。

当月誕生月メンバーのピックアップできる理由

ポイントはMATCH関数でカウント列の1~連番になるように指定する所ですが、こちらはROW関数を使って値を出すようにしています。
1行目に1の情報を2行目に2の情報を持ってくるやり方であれば、なんでもOKです。

手順⑤:誕生日の昇順・降順(手動)

この部分だけ手動になりますが、今回は誕生日が昇順になっています。
(9/1・9/1・9/18・9/19と順番になっている。)

ベースの表(今回の表でいうとテーブル3)の順番がそのまま抽出されますので、日付が今回のように昇順にならない時があるかと思います。
その際は、ベースの表に誕生日列を追加し、こちらでフィルターの昇順をしていただくと並べ替えることができます。

昇順・降順設定

この部分を昇順がはやく簡単です。

おまけ

ちなみに関数で自動的にする方法は、下のように誕生日の他にRank関数で順位付け同じ誕生日のカウントで更に順位をつけることでできます。

昇順・降順の自動化

まずは全体の誕生日の順位を出して、そのあと該当のものだけの順位を算出
その後、Countifで出した値-1+Rank2の値をすると、順位を求めることができます。

まとめ

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

メンバーの誕生日を知っていて、みんなで祝おうとするのってなんか素敵だなって思います。
英語でもHappy Birthday!ですからね。
「ありがとう!」や「おめでとう!」は人をそれだけで幸せにしますね。

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

コメント