2つの数値の組合せで区分わけしたい(エクセル）

質問

A行に4桁（1900～2013）の数値が、B行に1～12の数値がそれぞれランダムに4万件ほどあります。
数値の意味的にはA行は「西暦」、B行は「月」なのですが、この組合せを下記の7区分に分ける方法があれば教えていただきたく、よろしくお願いします。
フィルタ▼で検索して作業していたのですが、同じ作業をあと25ファイル分あることに気づき、
作業セルに【区分】の1～7が表示される最適な関数があれば教えてください。
（エクセル2002）

【区分1】　2010年4月以降
【区分2】　2008年4月～2010年3月
【区分3】　2006年4月～2008年3月
【区分4】　2001年4月～2006年3月
【区分5】　1996年4月～2001年3月
【区分6】　1991年4月～1996年3月
【区分7】　1991年3月以前

回答No.1

luka3 · ベストアンサー

Sheet2のA列に
年月
0
199104
199604
200104
200604
200804
201004

B列に（実際は「区分」と付けなくてもいいみたいですが念のため）
区分
区分7
区分6
区分5
区分4
区分3
区分2
区分1

と入力し、元のシートの区分を設定したいセル（例えばセルC2なら）に
=VLOOKUP(A2*100+B2,Sheet2!$A$2:$B$8,2)
と入力してみてください。

Sheet2の年月は古い順に並べてください。

お礼

早急で、かつ、わかりやすい関数で助かりました。

回答No.10

kagakusuki

回答番号ANo.9です。
　間違えました、集計を行った日付を入力しているのは、AA1セルではなく、Z1セルでした。

【誤】

=IF(AND(ISNUMBER(DAY($AA$1)),ISNUMBER(DAY((INDEX($A:$A,ROW())&"/"&INDEX($B:$B,ROW())&"/1")*1))),"区分"&MATCH(YEAR($AA$1)-INDEX($A:$A,ROW())-(INDEX($B:$B,ROW())>3),{-9999,1,3,5,10,15,20}),"")

【正】

=IF(AND(ISNUMBER(DAY($Z$1)),ISNUMBER(DAY((INDEX($A:$A,ROW())&"/"&INDEX($B:$B,ROW())&"/1")*1))),"区分"&MATCH(YEAR($Z$1)-INDEX($A:$A,ROW())-(INDEX($B:$B,ROW())>3),{-9999,1,3,5,10,15,20}),"")

お礼

まだ試しておりませんが、これは集計日(月）を基準にできるようですので
次回のタイミングで挑戦してみようと思います。

回答No.9

kagakusuki

回答番号ANo.8です。
　先程の関数ですが、次の様に簡略化する事が出来ます。

=IF(AND(ISNUMBER(DAY($AA$1)),ISNUMBER(DAY((INDEX($A:$A,ROW())&"/"&INDEX($B:$B,ROW())&"/1")*1))),"区分"&MATCH(YEAR($AA$1)-INDEX($A:$A,ROW())-(INDEX($B:$B,ROW())>3),{-9999,1,3,5,10,15,20}),"")

回答No.8

kagakusuki

もし、

【区分1】　集計した年の、1年前の4月以降
【区分2】　集計した年の、3年前の4月～1年前の3月
【区分3】　集計した年の、5年前の4月～3年前の3月
【区分4】　集計した年の、10年前の4月～5年前の3月
【区分5】　集計した年の、15年前の4月～10年前の3月
【区分6】　集計した年の、20年前の4月～15年前の3月
【区分7】　集計した年の、20年前の3月以前

という事でしたら、次の様にされると良いと思います。

　まず、何時のデータであるのかを明確にするためにも、適当なセル（ここでは仮にZ1セルにします）に集計を行った日付を入力し、そのセルの書式設定の表示形式を[日付]の

2001年3月14日

等としておきます。
　次にAA1セルに「現在」と入力して下さい。
　次に、区分を表示させるセルの中で、最も上にあるセルに、次の関数を入力して下さい。

=IF(AND(ISNUMBER(DAY($Z$1)),ISNUMBER(DAY((INDEX($A:$A,ROW())&"/"&INDEX($B:$B,ROW())&"/1")*1))),"区分"&MATCH(IF(DATE(INDEX($A:$A,ROW()),INDEX($B:$B,ROW()),1)>=(YEAR($Z$1)-1&"/4/1")*1,0,DATEDIF(DATE(INDEX($A:$A,ROW()),INDEX($B:$B,ROW()),1),(YEAR($Z$1)&"/3/1")*1,"y")),{0,1,3,5,10,15,20}),"")

　次に、上記の関数を入力したセルをコピーして、区分を表示させたい全てのセルに貼り付けて下さい。

　これで、集計を行った日付に合わせて、区分が表示されます。
　又、A行やB行の数値が、セルの切取り&貼付けや、セルの削除、セルの挿入、等の編集作業を行なう事で、セルの位置が上下に移動した場合でも、上記の関数の場合には、関数の参照先がおかしくなって、動作不良を起こす事はありません。

回答No.7

KURUMITO

例えば作業列としてF１セルから下方に区分１、区分２、・・区分７とF７セルまでに入力します。G１セルには2010/4/1、G２セルには2008/４/１、G３セルには2006/4/1・・・・のようにG７セルには1991/3/1と入力します。

そこでC列に区分を表示させるとしてC１セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",IF(DATE(A1,B1,1)>=MAX(G:G),F$1,IF(DATE(A1,B1,1)<=MIN(G:G),F$7,INDEX(F:F,MATCH(DATE(A1,B1,1),G:G,-1)+1))))

回答No.6

imogasi

（１）これらの区分を日付として扱うのが良いだろう
そのためにはすべての月の１日の日付だと考える。
（２）日付のセルの値は、日付シリアル値といって正整数の値だということを知ってますか。
不知なら、Ｇｏｏｇｌｅででも「エクセル　日付シリアル値」で照会して勉強のこと。
だから、日付の前後判定や期間幅に入るかどうかの判定は、ある正整数より大や小や、間にあるという判定になるのだ。
（３）隙間の無い区間にコードを振るのは、ＶＬＯＯＫＵＰ函数のＴＲＵＥ型の特徴的な使い方だ。
ＩＦ函数をネスとしたりする方法を使うとしたら、質問者のエクセルの技量に進歩が無いよ。
Ｇｏｏｇｌｅ［ででも、「エクセル　ＶＬＯＯＫＵＰ　ＴＲＵＥ」で照会して、勉強してから質問すべきぐらいなものだ。
（４）ＴＲＵＥ型を使う時は、前記検索テーブルデータは昇順に記述しておく。
旧い年月が上の行に来るということ。
（５）前記検索テーブルデータは区分のスタートの日付を並べる。
ーー
十分テストして無いが
1900/1/1 7
1991/4/1 6
1996/4/1 5
2001/4/1 4
2006/4/1 3
2008/4/1 2
2010/4/1 1
のようになる。
1900/1/1 7
1991/4/1
は、1900/1/1 から　1991/4/1間での間はコード　７を降るという構成である。
X－－＞a
↑
Y
のように見る。検索票の区切りの日はよく注意して区切りの日のテーブルを作ること。
以上、以下、未満的なことに注意。
ーー
例
Ｃ１に　2003
D1に　8
の場合
Ｅ1に =VLOOKUP(DATE(C1,D1,1),$F$1:$G$8,2,TRUE)
のような式になって、（DATE(C1,D1,1),　　は１日に統一（看做す）ということ）
結果
4
--
上記では
=IF(A1="","",VLOOKUP(A1,$F$1:$G$7,2,TRUE)のような　A1="","",の部分は省いて説明している。

回答No.5

某HN クロメート(Chromate)（@CoalTar）

＃４です
E2:E8セル範囲の画像を削ってしまいましたm(_ _)m
{1,33329,35156,36982,38808,39539,40269}
は
1900/1/1 1991/4/1 1996/4/1 2001/4/1 2006/4/1 2008/4/1 2010/4/1
を数値化(シリアル値)したものです

回答No.4

某HN クロメート(Chromate)（@CoalTar）

C2セルに
="区分"&8-MATCH(DATE(A2,B2,1),$E$2:$E$8)
とか 作業列を使わないなら
="区分"&8-MATCH(DATE(A2,B2,1),{1,33329,35156,36982,38808,39539,40269})
とすればよいと思います

回答No.3

tom04

こんにちは！
一例です
データはSheet1の2行目からあり、Sheet1のC列に表示させるとします。

↓の画像のようにSheet2に表を作成しておきます。

Sheet1のC2セルに
=IF(COUNTBLANK(A2:B2),"",VLOOKUP(DATE(A2,B2,1),Sheet2!$A$2:$B$8,2,1))
という数式を入れ、オートフィルで下へコピーすると
画像のような感じになります。

参考になれば良いのですが・・・m(__)m

回答No.2

mu2011

一例です。
（１）以下の様な表を作成（仮にSheet2のＡ，Ｂ列に設定、因みに；は単にセル区切り）
0；7
1991/4/1；6
1996/4/1；5
2001/4/1；4
2006/4/1；3
2008/4/1；2
2010/4/1；1
（２）データシートの空き列２行目に=IF(A2<>"",VLOOKUP(DATE(A2,B2,1),Sheet2!A:B,2,TRUE),"")を入力、A2のフィルボタン（右枠下の■）をダブルクリック