よねさんのWordとExcelの小部屋｜Excel(エクセル)講座の総目次｜Excel(エクセル)実用編：目次｜年齢早見表の作成
- エクセルで年齢早見表を作成してみます。
- また生年月日を入力して、年齢、干支、星座を求めるようにしてみました。
- 計算する年をC3セルに入力すると年齢早見表が作成されます。
- F2:H2セルで生年月日を選択すると、当日の年齢などがI2:K2に計算されます。
- C3セルの計算基準年で計算される年齢がD,H,L列に表示されます。
- Excel(Windows版)で計算できる1900年以降の表を作成してみます。
- 列見出し、計算する年など基本となる部分を入力します。
- 西暦はA5セルに1900と入力し、[ホーム]タブの編集グループの[フィル]→[連続データの作成]を選択します。
- 範囲：列 種類：加算 増分値：1 停止値：1949 として1949までの連続値を作成します。
- 同様に、E5:E54に1950〜1999、I5：I54に2000〜2049までを入力します。
元号(和暦)の表示 topへ
- 西暦の横に元号(和暦)を表示します。
- B5セルに=TEXT(DATE(A5,1,1),"ggge") としフィルハンドルをダブルクリックしてB54セルまで数式をコピーします。
その年の1月1日の元号を表示しますので、微妙に異なるのは了承ください。
- 数式の意味
DATE(A5,1,1)：A5セルの年数からその年の1月1日の日付けデータ(シリアル値)に変換します。
=TEXT(DATE(A5,1,1),"ggge")：その日付けをTEXT関数で元号の表示形式の文字列に変換します。
- B5セルをコピーしてF5,J5セルへ貼付け後、それぞれした方向へ数式をコピーします。
(同様にフィルハンドルをダブルクリックします。)
- 干支(えと）：十干と十二支を求めて結合します。
- 十干(じっかん)：甲,乙,丙,丁,戊,己,庚,辛,壬,癸の10個が繰り返されます。
- MOD(A5,10)+1：年数(A5)を10で割り余りを求めると、0〜9となるのでそれに1を加えて1〜10が得られるようにします。
- MID("庚辛壬癸甲乙丙丁戊己",MOD(A5,10)+1,1)
1900年は「庚」になるので、順番をずらした文字列にし、それぞれの余り値で該当する値を取出します。
(計算の都合がいいように十干を並べ替え、計算式を簡便にしています)
- 十二支(じゅうにし)：
- MOD(A5,12)+1,1)：12年サイクルですので、12で割った余りに1を加え1〜12が得られるようにします。
- MID("申酉戌亥子丑寅卯辰巳午未",MOD(A5,12)+1,1)：1900年は「子」になるので、十干と同様に計算の都合がいいように並べ替えます。
- 干支(えと)：上記で求めた値を「十干&十二支」で結合します。
- =MID("庚辛壬癸甲乙丙丁戊己",MOD(A5,10)+1,1)&MID("申酉戌亥子丑寅卯辰巳午未",MOD(A5,12)+1,1)
- 元号(和暦)と同様に数式を下方向へコピーします。G,K列へも同様に数式をコピーします。
- 求める年をC3セルに入力します。このセルの年で年齢を計算します。
- 計算式は=$C$3-A5 となります。ただし、計算する年以降はマイナスになるのでIF関数で空白にします。
=IF($C$3-A5<0,"",$C$3-A5)
- 元号(和暦)と同様に数式を下方向へコピーします。H,L列へも同様に数式をコピーします。
- 生年月日(和暦)から年齢、干支、星座を求めてみます。
入力するセルの設定 topへ
- キーボードから入力するのは面倒ですので、入力規則を利用してマウスで選択するようにします。
- E2セルを選択し、[データ]→[入力規則]を選択します。 Excel2007以降では［データ］タブの［入力規則］を実行します。
- [設定]タブで入力値の種類で「リスト」を選択します。
元の値に「明治,大正,昭和,平成」と入力します。
- 年月日もそれぞれ元の値に入力しても良いのですが、面倒ですので、Sheet2に数値を入力して名前を定義して、入力規則を設定します。
- 名前の定義
- Shet2へ年を1〜64、月を1〜12、日を1〜30と連続値を入力します。
- 年：Sheet2のJ2:J66セルを選択して(列見出しを含んで選択します)、[挿入]→[名前]→[作成]で名前を定義します。
リボンでは[数式]タブの定義された名前グループの[選択範囲から作成]を選択します。
月はSheet2K2:K14、日はSheet2L2:L33セルで同様に名前を定義します。
- セルの表示形式の設定
- この状態ではF2〜H2セルには数字が表示されるので、[書式]→[セル]で表示形式を設定します。
Excel2007以降では［ホーム］タブの数値グループにあるダイアログボックス起動ツールをクリックしてセルの書式設定ダイアログを表示します。
ショートカットキーは [Ctrl]+[1]です。
- F2セルは「G/標準」の後ろに「"年"」と入力します。
G2セルは同様に[G/標準"月"]、H2セルは「G/標準"日"」とします。
- 入力規則の設定
- F2セルを選択し、[データ]→[入力規則]を選択します。 Excel2007以降では［データ］タブの［入力規則］を実行します。
入力値の種類で「リスト」を選択し、元の値に「=年」と入力します。
- G2セルを選択し、[データ]→[入力規則]を選択します。Excel2007以降では［データ］タブの［入力規則］を実行します。
入力値の種類で「リスト」を選択し、元の値に「=月」と入力します。
- H2セルを選択し、[データ]→[入力規則]を選択します。 Excel2007以降では［データ］タブの［入力規則］を実行します。
入力値の種類で「リスト」を選択し、元の値に「=日」と入力します。
- F2:H2セルはドロップダウンリストからの選択で入力できるようになりました。
和暦からの年齢計算 topへ
- E2&F2&"年"&G2&"月"&H2&"日"：E2:H2セルへ入力された値を日付データ(文字列)にします。
(日付の形式の文字列はExcelが日付と解釈して計算してくれるのを利用しています)
- DATEDIF関数で年齢を求めます。
=DATEDIF(E2&F2&"年"&G2&"月"&H2&"日",TODAY(),"y")
和暦から干支を求める topへ
- DATEVALUE(E2&F2&"年"&G2&"月"&H2&"日")：和暦の文字列を日付データ(シリアル値)に変換します。
- =YEAR(DATEVALUE(E2&F2&"年"&G2&"月"&H2&"日"))：西暦の年を取出します。
- 前に干支を計算式に取出した西暦の年を入れます。
- =MID("庚辛壬癸甲乙丙丁戊己",MOD(YEAR(DATEVALUE(E2&F2&"年"&G2&"月"&H2&"日")),10)+1,1)
&MID("申酉戌亥子丑寅卯辰巳午未",MOD(YEAR(DATEVALUE(E2&F2&"年"&G2&"月"&H2&"日")),12)+1,1)
作成した年齢早見表から取り出す方法 topへ
- 上の方で年齢早見表を作成しているので、その表を利用しようと思います。
かなり面倒な計算式になりました・・・上の数式の方が楽です。
- E2:F2に入力される「昭和」「27」といった和暦の年を早見表から拾い出し、その隣の干支を取出します。
=INDEX(干支のあるセル範囲,和暦の年が一致する行位置)
=INDEX(干支のあるセル範囲,MATCH(和暦の年,和暦のセル範囲,0))
で、求められそうです。
- 早見表は3分割されているのでどの列にあるのかをCOUNTIF関数で調べます。
- 干支が記載されているセル範囲はB5列を基準とすれば、右へ1，5，9列目にあるので
B列にあったら「1」、F列にあったら「5」、J列のあったら「9」を返すようにします。
COUNTIF(B5:B54,E2&F2)*1+COUNTIF(F5:F54,E2&F2)*5+COUNTIF(J5:J54,E2&F2)*9
- 取出したい干支があるセル範囲は、B5セルを基準にすれば
OFFSET(B5,0,COUNTIF(B5:B54,E2&F2)*1+COUNTIF(F5:F54,E2&F2)*5+COUNTIF(J5:J54,E2&F2)*9,50)
になります。
- 入力した和暦(E2&F2)と一致するものがあるセル範囲は、上を同じようにA5を基準にすればよいので(1列左へずらせばよい)
OFFSET(A5,0,COUNTIF(B5:B54,E2&F2)*1+COUNTIF(F5:F54,E2&F2)*5+COUNTIF(J5:J54,E2&F2)*9,50)
となります。
- これらを組み合わせると
=INDEX(OFFSET(B5,0,COUNTIF(B5:B54,E2&F2)*1+COUNTIF(F5:F54,E2&F2)*5+COUNTIF(J5:J54,E2&F2)*9,50),
MATCH(E2&F2,OFFSET(A5,0,COUNTIF(B5:B54,E2&F2)*1+COUNTIF(F5:F54,E2&F2)*5+COUNTIF(J5:J54,E2&F2)*9,50),0))
となります。
- Sheet2に12星座の区切りとなる日付と星座名を文字列で入力します。
下表のように '01/01 と頭にアポストロフィ（[Shift]+[7]で入力します）を付けて入力します。
|
星座 |
| '01/01 |
やぎ座 |
| '01/20 |
みずがめ座 |
| '02/19 |
うお座 |
| '03/21 |
おひつじ座 |
| '04/20 |
おうし座 |
| '05/21 |
ふたご座 |
| '06/22 |
かに座 |
| '07/23 |
しし座 |
| '08/23 |
おとめ座 |
| '09/23 |
てんびん座 |
| '10/24 |
さそり座 |
| '11/22 |
いて座 |
| '12/22 |
やぎ座 |
- 入力したセル範囲を選択し、[編集]→[名前]→[定義]でセル範囲に「星座」と名前を定義します。
↓
名前に「星座」と入力して、参照範囲が選択しているセル範囲になっているのを確認して[OK]ボタンをクリックします。
- Excel2010などのリボンでは、下図のようにセル範囲を選択して、[数式]タブの[名前の定義]を実行します。
↓
名前に「星座」と入力して、参照範囲が選択しているセル範囲になっているのを確認して[OK]ボタンをクリックします。
- G2:H2セルに入力された月日を「00/00」の形式の文字列に変換します。
TEXT(G2,"00")&"/"&TEXT(H2,"00")
- VLOOKUP関数でセル範囲「星座」から該当する星座名を取出します。
=VLOOKUP(TEXT(G2,"00")&"/"&TEXT(H2,"00"),星座,2)
- 十干と十二支の読みを表示します。
- Sheet2へ十干と十二支の読みの表を作成します。
- セル範囲Sheet2!B3:E12の「十干」、Sheet2!G3:H14の「十二支」と名前を定義します。
- J2セルに表示される干支の左1文字、右1文字を使いそれぞれVLOOKUP関数で求めます。
- 十干と十二支を行内で改行するために「CHAR(10)」を挟んでいます。
- =VLOOKUP(LEFT(J2,1),十干,4,0)&CHAR(10)&VLOOKUP(RIGHT(J2,1),十二支,2,0)
- J3セルを[書式]→[セル]の[配置]タブで「折り返して全体を表示する」にチェックを入れます。
- 干支の読みが表示されました。
条件付き書式で目立たせる topへ
- 年齢早見表の該当箇所に色を付けて目立つようにします。
- セル範囲A5:D54を選択し、[書式]→[条件付き書式]を選択します。
「数式が」「=$B5=$E$2&$F$2」として「書式」を設定します。
- Excel2007以降では［ホーム］タブの［条件付き書式］→［新しいルール］を実行し、「数式を使用して、書式設定をするセルを決定」を選択します。
- セル範囲E5:H54,I5:L54も同様に条件付き書式を設定します。
数式はそれぞれ =$F5=$E$2&$F$2、=$J5=$E$2&$F$2 となります。
- 設定後の状態
スポンサードリンク
よねさんのWordとExcelの小部屋｜Excel(エクセル)講座の総目次｜Excel(エクセル)実用編：目次｜年齢早見表の作成
PageViewCounter
Since2006/2/27
