[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『エクセルのデータをカレンダーにリンクさせる』（初心者ルキア）
おはようございます。何度も申し訳ありません。
[[20110414113354]]でお世話になりました ルキアです。
以前教えて頂いた
関数での リンクとても上手くいったのですが。。。
Sheet2のB2セルに =IF(COUNTIF(Sheet1!$E$2:$E$3,$A2)=0,"",INDEX(Sheet1!$A$2:$E$3,MATCH($A2,Sheet1!$E$2:$E$3,0),COLUMN(A1))) 右に下にフィルコピーをしています。
同じ日付に １種類だけだとカレンダーには 上手く表示されるのですが、５種類など 増えてしまうと
カレンダーの その日の 日付を増やしてもエクセルのデータが先頭のものしか反映されません。
改善するには どうしたらいいのでしょうか。
＜Sheet1＞ A B C D E 1 品番 業者 品名 数量 納期 2 001 ○○ ×× 6pcs 4月10日 3 035 ●● ▲▲ 18pcs 5月2日 4 088 □□ ✕✕ 7pcs 5月30日 5 115 ○○ ○○ 6pcs 5月30日 6 117 ●● ▲▲ 18pcs 5月30日
＜Sheet2＞
A B C D E
1 日付 品番 業者 品名 数量
2 4/1
3 4/2
4 4/3
5 4/4
6 4/5
7 4/6
8 4/7
9 4/8
10 4/9
11 4/10 001 ○○ ×× 6pcs
12 4/11
13 4/12
・ ･
・ ・
・ ・
5/30 088 □□ ✕✕ 7pcs
5/30 115 ○○ ○○ 6pcs
5/30 117 ●● ▲▲ 18pcs
というようにしたいのですが。。。
まだ 関数の意味を調べながら 進んでいるので いまいち理解できずに困っています。
色々と教えてください お願いします。
excel2003 WindowsXP
数式の主要部は下記です。 INDEX(Sheet1!$A$2:$E$3,MATCH($A2,Sheet1!$E$2:$E$3,0),COLUMN(A1)) INDEX(範囲,行,列) 範囲の中から行、列で指定した位置の値を返します。 INDEX(Sheet1!$A$2:$E$3,2,1) だと、Sheet1!$A$2:$E$3のセル範囲の２行目の１列目、つまりSheet1!A3のセルの値が返ります。 上記「２行目」の変わりに下記の関数が使われています。 MATCH($A2,Sheet1!$E$2:$E$3,0) 「$A2の値をSheet1!$E$2:$E$3の範囲から完全一致で検索してその行位置を計算しなさい」 「１列目」の代わりにはCOLUMN(A1)が使われています。 多分これの働きがわからないのではないでしょうか。 B1に=COLUMN(A1)とすると「１」が返ります。C1にコピーすると=COLUMN(B1)となって「２」 が返ります。つまり、式を右にコピーしていくと、「１」「２」「３」・・・となるように したいので、COLUMN関数を使っているわけです。 あとはセル範囲に使われている「$」を使いこなせていないかも？ もしくは、行数を合わせていなかったり・・・？ =IF(COUNTIF(Sheet1!$E$2:$E$30,$A2)=0,"",INDEX(Sheet1!$A$2:$E$30,MATCH($A2,Sheet1!$E$2:$E$30,0),COLUMN(A1))) (みやほりん)(-_∂)b
Sheet2のB2セルに =IF(COUNTIF(Sheet1!$E$2:$E$6,$A2)=0,"",INDEX(Sheet1!A$2:A$6,SMALL(IF(Sheet1!$E$2:$E$6=$A2,COUNTIF(Sheet1!$G$1:$G1,$A2)+RANK($A2,Sheet1!E$2:E$6,1)),1)))
Ctrl + Shift + Enter で確定 下にフィルコピー
Sheet2のC2セルに =IF($B2="","",VLOOKUP($B2,Sheet1!$A$2:$D$6,COLUMN(B1),FALSE)) 右に下にフィルコピー
でどうでしょうか？
(se_9)
(se_9)さん
ありがとうございます。
行数や ＄を よく確認しましたが。。。
打ち込み間違いもないようで。。。
(se_9)さん sheet2のB2セルに入れる数式なのですが。。。
SMALL(IF(Sheet1!$E$2:$E$6=$A2,COUNTIF(Sheet1!$G$1:$G1,$A2)
というような式が出てきますが、
G1とセル番号が出てきます。
なぜ G列が出てくるのでしょうか？？？
RANK関数が出てきたので sheet1のデータに 同じ日付のデータに順番を認識してもらうため
少し変える必要があるのでしょうか？
うまく 説明できなくてすみません。。。
（初心者ルキア）
あ、すいません。同じシート内でテストしてうまくいったのでシート分けしたのですが その時に直すのを忘れていたようです。なので COUNTIF(Sheet1!$G$1:$G1,$A2)はCOUNTIF($A$1:$A1,$A2)に変えてください。
(se_9)
COUNTIF($A$1:$A1,$A2)のところ 選択範囲を広げて入力しましたら
うまく 反映できました。
しかし やはり 同じ日付のところはその日付の先頭のデータが
連続して入ってしまいます。
どこがいけなかったのでしょうか？
（初心者ルキア）
＞COUNTIF($A$1:$A1,$A2)のところ 選択範囲を広げて入力しましたらうまく 反映できました。
いや、そこはいじったらだめですよ。 手直ししていいのは$E$2:$E$6やA$2:A$6の部分だけです。
(se_9)
確かに おかしくなってしまいました。
すみません<(_ _)>
（初心者ルキア）
どうやら 日付→品番→商品名（一番文字数が多いデータ）
基準になっているらしく
一番文字数の多いデータのみ挿入されている感じでした。
私の説明不足だったのですが、
品番が同じでも 受注業者 商品名が 多数ありまして
品番もとても大切なのですが またそこから多岐に分裂しているといった具合です。
こういった場合は 品番に番号を打って 違うものと認識できるように
して行った方がいいのでしょうか？
それとも 根本的に間違っているのでしょうか？？
<例>
ABC-0123
のところを ABC-0123(1) ABC-0123(2) ABC-0123(3) というように。。。
どうか教えてください 宜しくお願いします。
（初心者ルキア）
レスがつかないですね。
結局実際のデータはどのように成っていたのでしょう？ ↑のご説明ではちょっと良く分からないのですが。
それから、今回で３回目ですので このやり取りが終わった後に 「実は〜」なんて事にならない様に もう一度元データを確認してみて下さい。
(HANA)
(HANA)さん レスありがとうございます。 うまく説明できずに申し訳ありません。 言葉の使い方もうまく出来ずに反省しております。 根本的に間違っていたのでは とは 私の作成したsheet1の作り方が間違っていたのでしょうか？ という意味でした。 最初の 質問した時の例に挙げたsheet説明ももっと詳しく上げるべきでした。 反省です。
それを踏まえてもう一度作り直してみました。
<sheet1>
A B C D E
1 品番 業者 品名 数量 納期
2 RD-1010 海山商事 ✕✕✕ 2pcs 5/2
3 FT-039 笹川物産 ●●● 20pcs 5/10
4 RD-007 鈴木商店 □□□□□ 5pcs 5/30
5 RD-007 BBB日本 ◎◎◎◎ 150pcs 5/30
6 FT-040 長岡産業 ▲▲▲ 25pcs 5/31
7 RD-007 長岡産業 ●●● 10pcs 6/20
8 RD-007 長岡産業 ＋＋ 1pc 6/30
9 RD-007 BBB日本 ◎◎◎◎ 30pcs 6/30
10 RD-007 鈴木商店 □□□□□ 8pcs 6/30
<sheet2>(sheet名 5月）
A B C D E
1 日付 品番 業者 品名 数量
2 5/1
3 5/2 RD-1010 海山商事 ✕✕✕ 2pcs
4 5/3
5 5/4
6 5/5
7 5/6
8 5/7
9 5/8
10 5/9
11 5/10 FT-039 笹川物産 ●●● 20pcs
12 5/11
‖
省略
‖
27 5/26
28 5/27
29 5/28
30 5/29
31 5/30 RD-007 鈴木商店 □□□□□ 5pcs
32 5/30 RD-007 鈴木商店 □□□□□ 5pcs
33 5/31 FT-040 長岡産業 ▲▲▲ 25pcs
<sheet3>(sheet名 6月）
A B C D E
1 日付 品番 業者 品名 数量
2 6/1
3 6/2
4 6/3
5 6/4
‖
省略
‖
21 6/20 RD-007 鈴木商店 □□□□□ 5pcs
22 6/21
23 6/22
24 6/23
25 6/24
26 6/25
27 6/26
28 6/27
29 6/28
30 6/29
31 6/30 RD-007 鈴木商店 □□□□□ 5pcs
32 6/30 RD-007 鈴木商店 □□□□□ 5pcs
33 6/30 RD-007 鈴木商店 □□□□□ 5pcs
と 今の現状況です。
sheet1のセル範囲は A1からE72まで広げています。
どうやら 5/30と6/30の 同じ日付が重なっている部分が sheet1の 4 RD-007 鈴木商店 □□□□□ 5pcs を 参照しているようなのです。
改善するにはどうしたらよいでしょうか？
Sheet2のB2セルに =IF(COUNTIF(Sheet1!$E$2:$E$72,$A2)=0,"",INDEX(Sheet1!A$2:A$72,SMALL(IF(Sheet1!$E$2:$E$72=$A2,COUNTIF(Sheet1!$A$1:$A1,$A2)+RANK($A2,Sheet1!E$2:E$72,1)),1)))
Ctrl + Shift + Enter で確定 下にフィルコピー
Sheet2のC2セルに =IF($B2="","",VLOOKUP($B2,Sheet1!$A$2:$D$72,COLUMN(B1),FALSE)) 右に下にフィルコピー しています。 (初心者ルキア)
そう言う事でしたか。
C列以降は、B列の数式と違って VLOOKUP関数を使っていますが これをやめて、B列の数式をそのまま使う事にすれば 表示されると思います。
そのまま横にドラッグすると ＞RANK($A2,Sheet1!E$2:E$72,1) の部分の参照が Sheet1のE列からずれて仕舞うので 先に絶対参照にしてから、ドラッグしてみて下さい。
ちなみに、A列に関してはふれられていませんが 良いのでしょうか？
(HANA)
少々、お尋ねします。 m(__)m
> Sheet2のC2セルに > =IF($B2="","",VLOOKUP($B2,Sheet1!$A$2:$D$72,COLUMN(B1),FALSE))
１．Sheet1のデータ構成で、この数式を使うと同じ品番の最上行にあるデータを 引っ張って来る事になりますので、修正を要します。
２．月別シートの日付は、どうやって決めているのですか？ 手入力ですか？ (手入力だとして、同じ日の取引が複数あるなんてことをどうやって判断しているのですか？） (納期が存在しない日まで表示する必要があるのですか？ 実際は、納期は毎日あると云うことですか？)
３．Sheet1のデータ行について (1) 実際に72行目迄しかないのですか？ (2) 同じ日に同じ業者で同じ品番が複数あると云うこともありますか？ (3) 納期(E列)は、昇順に並んでいるのですか？ 並んでいないとして、作業前に納期順>品番>順業者順に並べ替えることは許されますか？
４．このブックは、何月まで続くのですか？ 4月から翌年3月までですか？（重く感じないですか？)
(半平太) 2011/04/25 13:37
(HANA)さん、(半平太)さん レスありがとうございます。 (HANA)さん ＞RANK($A2,Sheet1!E$2:E$72,1) を ＞RANK($A2,Sheet1!$E$2:$E$72,1)と変更してみましたが 変化はみられませんでした。
(HANA)さんA列にについてですが 今までそこまで思い到らなかったのですが sheet1に 入力すると 自動で月別カレンダーの日付が増えるということも 可能ということなのでしょうか？
(半平太)さん
２．月別シートは 前もって1ヶ月1sheetとし、12か月分を作っておきます。
1sheetに 入力した時に日にちがかぶっていたら
その都度月別シートの 日付を増やしていき、
数式のみをコピーしている状態です。
一応 カレンダー仕様にし、5月を開いたら5月分一覧がわかるようにしています。
今日の日付の列の色が変わるようにし、
納期の 3日前から日付部分のセルの色が警告色に変わるように設定しています。
納期は ほぼ 毎日ではありません。
３．(1)sheet1のデータはまだ72行しか設定していません、
しかしこれから増えていく予定です。
(2)同じ日付 品番 ということはあります。品名と数量だけ違う場合が
多々あります。
(3)sheet1に入力の際には 昇順降順ボタンで 昇順に変更しています。
品番 業者も必要であればします。
４．このブックは、1年分翌3月まで作成しています。
(半平太)さん C2セルどのように修正したらいいのでしょうか？？ 手引書などを調べながらですがいまいちそこまで辿り着いていないのが本現状です。
（初心者ルキア）
１．私だったら、こんな風に作ります。それじゃダメなんでしょうか？ ↓ (1)月別シートを全部一度に見ることはないので、１２シート作る必要はないと思います。 月別シートは１枚だけ作り、指定月をドロップダウンリストから選択すると 指定月のデータ表が表示される様に作れば、手間はそんなに違わないと思います。
それだけで、エクセル君の負担は12分の1に減ります。
ファイルも肥大化しないで済みます。
(2)月別のシートの日付は自動的に出す。 納期が存在しない日は出さない。逆に、同一日が複数ある場合は、あるだけ自動で出す。 納期が存在しない日も表示する必要があるのかどうか、私には分かりません。 邪魔な様でもあり、時間的な感覚が掴めていいような気もします。 ただ、自動的に納期を出すと云うことになると、存在しない日は出さないでいいとする方が楽です。
２．現在、数式ベースで話が進んでいますが、、、 フィルタオプションで抽出しても大差ないものが出来る様な気がするのですが、 実際の表は、もう少し複雑でそれではダメと云うことでしょうか？
それとも、数式処理によってリアルタイムに結果が欲しいと云うな事なのでしょうか？ (でも、必要に応じて並べ替えをしている様ですから、 リアルタイム性はそんなに重要ではないと云う気がしますが？）
> 手引書などを調べながらですがいまいちそこまで辿り着いていないのが本現状です。 手引書をみて作れるレベルではないです。 データ情報とご希望を正確にお伝えいただいて、 後は回答者に考えてもらうしかないと思います。
仕様さえ決定すれば、回答者なら誰でも対応できます。
(半平太) 2011/04/25 16:07
(半平太)さんありがとうございます。 ドロップダウンリストからとは まったく考えつきませんでした。
カレンダー仕様で作らなくてはならないので やはり 納期がなくても日付は表示されている状態が望ましいです。
私ひとりならフィルタオプションで抽出して その日だけでも確認できるのですが
ほかの人が見て1ヶ月間の納期と 納期が近いな などと認識してもらう為に カレンダー表示にしたいのです。
データ情報は ほぼ 今日アップしたものと変わらないです。
是非 皆さんのお力をお貸しください。 宜しくお願いします。 (初心者ルキア）
＞(HANA)さん ＞＞RANK($A2,Sheet1!E$2:E$72,1) ＞を ＞＞RANK($A2,Sheet1!$E$2:$E$72,1)と変更してみましたが ＞変化はみられませんでした。
式の一部が間違っているからです。 COUNTIF(Sheet1!$A$1:$A1,$A2)をCOUNTIF($A$1:$A1,$A2) にしてみてください。
(se_9)
> ほかの人が見て1ヶ月間の納期と 納期が近いな などと認識してもらう為に > カレンダー表示にしたいのです。
そう云う意味でしたら、月別ベースで表を作るのは善し悪しです。 何故なら5月末近くになったら、6月の納期が見通せなくなるからです。
<仕様> １．月別シートは1枚のみ作成する（計算負担を軽減するため） ２．日付は、数式で自動的に出力する ３．何日から出力するかは、ユーザーが1行目(A1,B1,C1セル）で指定するものとする。 ４．出力範囲は出力指定日から5週間とする ※ 月末で打ち切りたい場合は、最後尾の数式を使用して下さい)
<月別シートの数式> (1) A3セル =DATE(A1,B1,C1)
(2) A4セル =IF(A3<DATE($A$1,$B$1,$C$1+35),IF(COUNTIF(A$2:A3,A3)<COUNTIF(Sheet1!E:E,A3),A3,A3+1),"") 下にフィルコピー
(3) B3セル =IF(A3="","",IF(COUNTIF(Sheet1!$E$2:$E$72,$A3)=0,"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$E$2:$E$72=$A3,ROW($A$2:$A$72)),COUNTIF($A$2:$A3,$A3))))) Ctrl+Shift+Enter で数式を入力後、右へ下へフィルコピー
以上で、通常は、B1のセルに5とか6とか、希望する月を入力すればいいと思います。
<月別シート 結果図> <Sheet1 サンプルデータ>
行 ____A____ ___B___ ____C____ ___D___ __E__ 行 ___A___ ____B____ ___C___ __D__ ___E___
1 2011 5 1 1 品番 業者 品名 数量 納期
2 日付 品番 業者 品名 数量 2 RD-1010 海山商事 ××502 1pcs 5月2日
3 2011/5/1 3 FT-039 笹川物産 ●●510 2pcs 5月10日
4 2011/5/2 RD-1010 海山商事 ××502 1pcs 4 RD-007 鈴木商店 □□530 3pcs 5月30日
5 2011/5/3 5 RD-007 BBB日本 ◎◎530 4pcs 5月30日
6 FT-040 長岡産業 ▲▲531 5pcs 5月31日
11 2011/5/9 7 RD-007 長岡産業 ●●620 6pcs 6月20日
12 2011/5/10 FT-039 笹川物産 ●●510 2pcs 8 RD-007 長岡産業 ＋＋630 7pcs 6月30日
13 2011/5/11 9 RD-007 BBB日本 ◎◎630 8pcs 6月30日
10 RD-007 鈴木商店 □□630 9pcs 6月30日
31 2011/5/29
32 2011/5/30 RD-007 鈴木商店 □□530 3pcs
33 2011/5/30 RD-007 BBB日本 ◎◎530 4pcs
34 2011/5/31 FT-040 長岡産業 ▲▲531 5pcs
35 2011/6/1
39 2011/6/5
※ 月末で表示を打ち切る場合、上記(2)の式を下記へ変更してください。 A4セル =IF(A3="","",IF(AND(DAY(A3+1)=1,COUNTIF(A$2:A3,A3)>=COUNTIF(Sheet1!E:E,A3)),"",IF(COUNTIF(A$2:A3,A3)<COUNTIF(Sheet1!E:E,A3),A3,A3+1)))
(半平太) 2011/04/25 21:05
↑
すみません。月末打ち切りの式を修正しました。
(半平太) 2011/04/25 21:55
(半平太)さん (se_9)さんレスいつもありがとうございます。
(半平太)さんに教えて頂いた方法をしています。 自動で日付が追加されるようになりました。 今度は 連続する日付に＃NAMという表示が出るようになりました。 あと 納品日の所に品番 業者 品名 数量 全て 表示されるようになったのですが、 どうやら Sheet1の行番号を参照している様子。 納品日には 表示されているのに 内容はsheet1の同じ行番号の内容を反映させています。
何度か作り直して見ましたが 変わりません。 何度も申し訳ないのですが 教えてください。お願いします。
半平太さんの方法を試してみましたが、何の問題もありませんでしたよ。 セル位置や範囲が違っているということはありませんか？
あと ＞＃NAMという表示が出るようになりました。 ですが、エクセルには＃NAMというエラーはありません。 #NAME? #N/A #NUM! のどれかですかね？
(se_9)
(se_9)さん お世話になっています。 #NAM!でした。 あまりにも焦ってしまい 間違えました。
sheet1のデータに入力するために余裕を持たせたかったので 余分に空白のセルを取っていたのが原因でした。
本当はデータは 52行までしかなかったのですが72行まで広げて表を作っていました。
データの最終列を再指定して 入力しなおしたら 無事に sheet1のデータが反映されました。
とても使いやすいカレンダーになりました。 (半平太)サンありがとうございます。
しかし 空白のセルを持たせていないと sheet1に入力する度に数式に手を加えなければならなくなると思うのですが。。。 改善方法はありますか？
だんだん欲張りになってきて本当にもうしわけありません。。。。
(初心者ルキア)
たとえば半平太さんのサンプルデータだとSheet1には10行目までしかデータが入力されて いないので、$E$2:$E$72や$A$2:$A$72の部分を$E$2:$E$10や$A$2:$A$10にしないとうまく 反映されないということですか？
こちらでは$E$2:$E$72や$A$2:$A$72の状態でもちゃんと反映されます。 う〜ん、謎だなぁ･･･。
(se_9)
そうです、私の場合52行きっちりに入力したら 落ち着きました。
もしかしたら 試行錯誤した末なので ちょっとデータが壊れかけているのかも。。。
もう一度まっさらなところからBOOKごと作り直してみようと思います。
(初心者ルキア)
おはようございます。
新しいbookに作り直した結果 空白のセルが含まれています72行まで広げても カレンダーにきちんと反映される様になりました。 やっと理想の形に仕上げることができました。
(半平太)サン (se_9)サン (HANA)サン (みやほりん)(-_∂)bサン 親切に教えて頂き本当にありがとうございました。
私の言葉足らずの説明に根気よく付き合って頂き本当に感謝しています。
関数 とても難しいですけれども これから沢山勉強していきたいと思います。 また 教えていただくことも多々あると思いますが その時はまた宜しくお願いします。
(初心者ルキア)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.