エクセル、日付を範囲指定し、項目ごとに別表に

質問

添付した画像を見てください。

業者、「金」「銀」「銅」とあり、その仕入日を4/21/～5/20迄のものの
データだけ取り出して右側の表のように作りたいと思います。

いろいろ調べたのですが、どうもわかりません。
VLOOKUPでは無理そうですし、
ピポットテーブルというのは、経験がなく、何をどうしたら良いのか
さっぱりです

どなたか教えて下さい。

回答No.6

KURUMITO · ベストアンサー

回答Ｎｏ3です。
Ｆ，Ｇ，Ｈ列は正常に表示されているようですね。
Ｋ3セルにはお示しした次の式をＯ3セルまでドラッグコピーしたのちに下方にもドラッグコピーしたのでしょうか？
Ｍ列のみで他に何の表示もないということはおかしいですね。
Ｊ列はお示しされている表のように分散した形で表示されるのは当然のことでしょう。

お礼

出来ました！！！

O3までドラッグコピーをしておりませんでした！

こちらで実際の表示も試してみます！

ご回答頂きありがとうございます。
勉強になりました

回答No.5

tom04

No.4です。
補足の件について・・・

前提条件として前回アップした通りの配置だとします。

回答No.4の数式「=」からをマウスでドラッグし範囲指定（最後の括弧も残さず）　→　右クリック　→　コピー
→　I4セルを選択　→　画面上側にある「数式バー」内で一度クリック　→　右クリック　→　貼り付け
これで数式バーにご自身で数式を入れたのと同じコトになりますので、
そのままShift＋Ctrlキーを押しながらEnterキーで確定します。

あとは前回の通りオートフィルで列・行方向にコピーです。

※　当然作業列部分の数式も同様にコピー＆ペーストできます。
（作業列の数式に関しては配列数式ではありませんのでそのまま確定でOKです）m（_ _）m

補足

やはり真っ白でした。

回答No.4

tom04

こんにちは！
一例です。

↓の画像は一つの業者だけですが、やり方は他の業者でも一緒です。
I1・K1セルに表示したい期間の始まりと終わりを入力します。

作業列F2セルに
=IF(C2="","",IF(AND(C2>=I$1,C2<=K$1),A2,""))
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてI4セルに
=IF(COUNTIF($F:$F,$H$4)<ROW(A1),"",INDEX(A$1:A$1000,SMALL(IF($F$1:$F$1000=$H$4,ROW($A$1:$A$1000)),ROW(A1))))
これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面から数式をコピー＆ペーストする場合は
I4セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能のまま）
Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これを列・行方向にオートフィルでコピー！
最後にI列の表示形式を「日付」にすると画像のような感じになります。

とりあえず1000行まで対応できる数式にしていますが、もっとデータがある場合
配列数式はPCに負担をかけますので、作業列を増やしてでも配列数式にしない方がよいと思います。

この程度で参考になりますかね？m（_ _）m

補足

回答ありがとうございます。

やってみたのですが、私の頭では理解ができず、
下記の文章以降が、どうしたら良いのか、分からずできませんでした。

>これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
>この画面から数式をコピー＆ペーストする場合は
>I4セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能のまま）
>Shift＋Ctrlキーを押しながらEnterキーで確定！
> 数式の前後に｛　｝マークが入り配列数式になります。
>これを列・行方向にオートフィルでコピー！
> 最後にI列の表示形式を「日付」にすると画像のような感じになります

回答No.3

KURUMITO

業者名は金銀銀といった限られたものではなくもっとたくさんあるのではと思いますね。
次のようにすればよいでしょう。
お示しのデータがＡ列からＥ列までにあるとして1行目は項目名で2行目から下方にデータが入力されているとします。
結構内容が複雑ですので作業列を作って対応します。
ＦからＩ列までを作業列としてお求めの表はＪ列からＯ列に表示させるとします。
そこで仕入日の範囲ですがＫ1セルに仕入日範囲と文字を入力し、Ｌ1セルには開始の日で4/21と入力し、終わりの日はＭ1セルに例えば5/20と入力します。
その上で作業列に式を入力していきます。
Ｆ2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,MAX(F$1:F1)+1,""))

これで業者の数だけ数値が表示されます。
Ｇ2セルには次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(AND(C2>=L$1,C2<=M$1),INDEX(F:F,MATCH(A2,A:A,0)),""))

これで指定の仕入日の範囲にある行に番号が付きます。
Ｈ2セルには次の式を入力して下方にドラッグコピーします。Ａ列で表示されている行よりもさらに下方までドラッグコピーします。

=IF(A2<>"",IF(G2="","",G2*1000+COUNTIF(G$2:G2,G2)),IF(ROW(A1)<=COUNT(C:C)+MAX(F:F),(ROW(A1)-COUNT(C:C))*1000+999,""))

以上で作業列の操作は終わって次にお求めの表ですがＫ2セルからＯ2セルまでにはＡ1セルからＥ1セルまでの項目名を並べます。

Ｋ3セルには次の式を入力してＯ3セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>COUNT($H:$H),"",IF(INDEX($A:$E,MATCH(SMALL($H:$H,ROW(A1)),$H:$H,0),COLUMN(A1))="","",INDEX($A:$E,MATCH(SMALL($H:$H,ROW(A1)),$H:$H,0),COLUMN(A1))))

Ｊ3セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(AND(K2="業者",K3<>""),AND(K2="",K3<>"")),K3,"")

最後にＭ列はシリアル値が表示されていますのでＭ列を選択して右クリックし、「セルの書式設定」の「表示形式」で「日付」からお望みの表示にします。

データが新たに追加されれば瞬時に表が変わります。また、仕入日の範囲を変えることでお求めの表が瞬時に変わります。
なお、作業列が目障りでしたらそれらの列を選択してから右クリックして「非表示」を選択すればよいでしょう。

補足

回答ありがとうございます。

上記のとおりに試してみましたが、うまくいきません。
何が悪いのかも、ちょっと分からないです。

Fの列に1か2か3の数字が表示され
Gの列に1か2か3の数字が表示され
Ｈの列に1001、3001、1002～とよくわからない数字
JとKには金銀銅がランダムに表示されただけです。

回答No.2

FEX2053

ピボットテーブルのデータ更新は、こういう手順です。
http://www11.plala.or.jp/koma_Excel/contents3/mame3030/mame303001.html
http://www.eurus.dti.ne.jp/yoneyama/Excel2007/excel2007-piv_kousin.html

2007以降は[Alt]+[F5]でも更新できます。
http://www.relief.jp/itnote/archives/003832.php

補足

更新ができるのですね。
知らなかったです。
しかしながらピポットテーブルが初心者の為
どれを、どのように使えばよいのか分からず
更新をやってみましたが、できませんでした。
もう少し、勉強します。

回答No.1

FEX2053

>ピポットテーブルというのは、経験がなく、何をどうしたら良いのか
>さっぱりです

どぞ。質問者さんの目的にはぴったりです。
http://allabout.co.jp/gm/gc/297727/
http://www.officepro.jp/exceltips/pivot/

Excel2007以降だと、こちらを。
http://www.eurus.dti.ne.jp/yoneyama/Excel2007/excel2007-piv.html

ダイアログに回答するだけでできますので、下手に関数をいじりまくる
より、ずっと簡単ですよ。

お礼

やはりピポットテーブルですかね。

やってみたのですが、どの項目をどうしたら良いのか
分からず。。。
すべてのデータをピポットテーブルにしてみたのですが、
自分がやりたいのは、元のデータが変化したら、
業者毎のデータも変わるようにしたいのです。

ピポットテーブルでは、それを作った時のデータでしか
反映されないようなので、難しいのかな、と思いました。

関数とかで解決できるのかと考えておりましたが。。。