エクセル関数

質問

エクセル初心者です。

今練習用に、以下の画像、上段（シート1です）のような
データベースを作成しました。

データベースは販売データで、右に基準月の
セルを作っています。

ここで、このデータベースを基準月以前と以降で
関数を使ってシート2に分離させたいのです。

シート2のＡ２セルとＥ２のセルにどんな関数を
いれればいいのでしょうか。

if関数だと、Ｅ２セルにうまく表示されませんでした。

お願いします。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.3

yosifuji20 · ベストアンサー

Ano.1のお答えのとおり、これは関数では無理があります。
Excelのような表計算ソフトの関数は基本的にきめられた場所のセルの値同士を元にして目的の計算結果を表示させるものです。
この場合、関数を入れるセルと元のデータのあるセルは場所が固定されています。これを動かすことは関数ではできません。
それをするのが、メニューにある並べ替え、コピーペースト、データのフィルターなどの機能です。
これらっと関数を組み合わせてマクロを組めば、自動実行でデータを動かすことができますが、関数だけではかなりの裏技を使わないと無理です。
そのようなことを考えるよりは、上記のマクロを覚える方が簡単です。

あるいは、もっと複雑なデータ抽出をしたければ、Accessを覚えるのも一方法です。これならば殆ど自由にデータの抽出が可能です。
ただExcelだけの経験でいきなりAccessにいくのはちょっと敷居が高いという気はしますが。
とりあえずは、並べ替えやフィルターの遣い方とマクロを習得したらいかがでしょうか。

その場合は、元のデータを年月で並べ替えをして、その結果の一定年月の上下でデータを２分すれば目的のデータは得られますよね。

回答No.2

kagakusuki

同じ販売月が複数回現れる可能性も考えられますから、Sheet2の販売食品や単価を表示させる関数は、VLOOUP関数では無理で、工夫が必要です。

　まず、Sheet1のA列、Sheet2のA列、Sheet2のE列に入力されているデータは、シリアル値として下さい。
　その方法は、セルの書式設定で、表示形式を日付の

2001年3月

の形式とした上で、例えば

1999年9月

と表示させる場合には、

1999/9

と入力します。

　そして、Sheet2のA2セルには、次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF(Sheet1!$A:$A,"<"&DATE(YEAR(Sheet1!$E$1),MONTH(Sheet1!$E$1)+1,1)),"",SMALL(Sheet1!$A:$A,ROWS($2:2)))

　次に、Sheet2のE2セルには、次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF(Sheet1!$A:$A,">="&DATE(YEAR(Sheet1!$E$1),MONTH(Sheet1!$E$1)+1,1)),"",SMALL(Sheet1!$A:$A,ROWS($2:2)+COUNTIF(Sheet1!$A:$A,"<"&DATE(YEAR(Sheet1!$E$1),MONTH(Sheet1!$E$1)+1,1))))

　次に、Sheet2のB2セルには、次の数式を入力して下さい。

=IF($A2="","",INDEX(Sheet1!B:B,SUMPRODUCT(ROW(OFFSET(Sheet1!$A$1,,,MATCH(9^9,Sheet1!$A:$A)))*(OFFSET(Sheet1!$A$1,,,MATCH(9^9,Sheet1!$A:$A))=$A2)*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(OFFSET(Sheet1!$A$1,,,MATCH(9^9,Sheet1!$A:$A)))),$A2)=COUNTIF($A$1:$A2,$A2)))))

　次に、Sheet2のF2セルには、次の数式を入力して下さい。

=IF($E2="","",INDEX(Sheet1!B:B,SUMPRODUCT(ROW(OFFSET(Sheet1!$A$1,,,MATCH(9^9,Sheet1!$A:$A)))*(OFFSET(Sheet1!$A$1,,,MATCH(9^9,Sheet1!$A:$A))=$E2)*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(OFFSET(Sheet1!$A$1,,,MATCH(9^9,Sheet1!$A:$A)))),$E2)=COUNTIF($E$1:$E2,$E2)))))

　次にSheet2のB2セルをコピーして、Sheet2のC2セルに貼り付けて下さい。
　次にSheet2のF2セルをコピーして、Sheet2のG2セルに貼り付けて下さい。
　次にSheet2のB2～G2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上で完成です。

回答No.1

imogasi

こんなのを関数で「やるのは適当でない。
・ソートして範囲の日付の塊をコピペ
・フィルタやフィルタオプションの設定
でやれば良い。
本件は関数による１条件の抜き出しだが、関数では抜き出しは難しい関数式になることを、経験が少なくて知らないのだろう。Ｇｏｏｇｌｅでiｍｏｇａｓｉ方式で照会すれば、私以外の回答者の、長い式（ＩＮＤＥＸとＳＭＡＬＬなど使った）の回答があるので見てみてください。
ーー
作業列を使うが、imogasi方式でやってみる（Ｇｏｏｇｌｅでiｍｏｇａｓｉ方式で照会）
データ例　Ａ列　　Ｃ列は作業列該当に連番を振っている。１０００件以下とする。
Ａ列　　　　　　　　　Ｃ列
2001/1/2 1000
1988/1/2 999
2001/3/1 1001
2004/2/5 1002
1997/2/1 998
1997/2/2 997
2003/3/4 1003
Ｃ２の式は
=IF(A2>DATE(1999,12,31),MAX(1000,MAX($C$1:C1)+1,2),MIN(999,MIN($C$1:C1)-1))
下方向に式を複写。
ーー
説明の簡単のため同一シートで分割する。
２０００年以後は
=INDEX($A$1:$A$100,MATCH(1000+ROW()-2,$C$1:$C$100,0))
（ここでは１００件以下に仮定）
下方向に式を複写。
2001/1/2
2001/3/1
2004/2/5
2003/3/4
#N/A
２０００年より前は
=INDEX($A$1:$A$100,MATCH(999-ROW()+2,$C$1:$C$100,0))
下方向に式を複写。
1988/1/2
1997/2/1
1997/2/2
#N/A
ーーーーー
上記で省いたこと
・データの列が日付以外にもあり複数列
・他シートにそれぞれ分割する
・データ実在数より余分に式を複写すると、＃Ｎ／Ａが出るがそれの防止法
他の質問のimogasi方式の説明を見てください。