[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『 すいません 又間違えて書いてしまっていました』（素人）
さっきも 間違えていて質問して解決したばっかりだったのですが、実は[[20100516133044]] で 又失敗をしていました。 本当に情けない限りです… コタさんに 折角教えて頂いたのに、又私が 抜きとってくるセルを間違えて書いていました。すいません 本当に。
＞シートAの AA列に 請求日が入ったら シートBの A C E G I K に あらかじめ入れてある 同じ請求日の列に シートAの 会社名と金額が入るようにしたいです。
と書いたのですが、金額は Ｎ列になっていたのですが すいません Ｎの金額 でなくＹの未収金額 を抽出したかったのです 又間違えていました。
私がよく確認せず 金額が入って成功したものと思い込んでいました。コタさんに 本当に申し訳ありませんでした。
シート２のＡ列に シート１のＹ列の未収金額を、 シート２のＢ列に シート１のＬ列の会社名を 抽出したいのですが 自分で 悪戦苦闘しながら 直そうとしましたが 全く駄目でした。 本当に又又で 申し訳ないです。ごめんなさい。 あと累計金額の所は 大丈夫です。合計金額の所も大丈夫です。
シート２の Ａ３に 何と入れ直したら 良いでしょうか？ それと A26・A49・A72・A95・A118 と 右隣に 7月5日からの同じ表が 又作ってあるのですが そこの M3・M26・M49・M72・M95・M118 には 何と入れたら 良いでしょうか
どうか 宜しくお願い致します 本当に ごめんんさい
こんばんは。 今度はこちらの関連ですね。 2.[[20100516133044]]『日付を入力したら別シートにその内容を抽出したいです』（素人）
それと↓のご質問も関連してきますので記載。 3.[[20100523171805]]『すいません…折角教えて頂いたのに私が間違えていました』（素人） 1.[[20100516002008]]『シート1からシート2へ特定の会社名のいくつかの内容のセルを抽出したいです』（素人）
＞ Ｎの金額 でなくＹの未収金額 を抽出したかったのです そういえば前回の回答のときに、抽出先をどう決めているのかを、ご説明していなかったですね。 今回はそれも併せて回答してみます。
○【シートA】シート （1月分） ※「_」セルは未入力または"" [R/C] [A] [B] [J] [K] [L] [M] [N] [Y] [Z] [AA] [AB] [1] _ _ _ _ _ _ _ _ _ _ _ [2] _ 会社名 番号 日付 会社名 現場名 金額 未収金額 作業列 請求日 作業列2 [3] _ あ建設 50 1/24 省略 Ａ邸 10000 20000 あ建設-1 1/10 01/10-1 [4] _ い建設 51 1/26 省略 Ｂ邸 20000 40000 い建設-1 1/5 01/05-1 [5] _ う建設 52 1/27 省略 Ｃ邸 30000 60000 う建設-1 1/5 01/05-2 [6] _ え建設 53 1/30 省略 Ｄ邸 40000 80000 え建設-1 1/20 01/20-1 [7] _ お建設 54 1/30 省略 Ｅ邸 50000 100000 お建設-1 1/15 01/15-1 [8] _ か建設 55 1/5 省略 Ｆ邸 60000 120000 か建設-1 1/30 01/30-1 [27] _ _ _ _ _ _ _ _ _ _ _
Z3 =IF(B3="","",B3&"-"&COUNTIF(B$3:B3,B3)) →Z3:Z27フィルコピー ※質問3.で変更済み（のはず） AB3 =IF(AA3="","",TEXT(AA3,"mm/dd")&"-"&COUNTIF(AA$3:AA3,AA3)) →AB3:AB27フィルコピー （前と同じ）
○【シートA2】シート （2月分） [R/C] [A] [B] [J] [K] [L] [M] [N] [Y] [Z] [AA] [AB] [1] _ _ _ _ _ _ _ _ _ _ _ [2] _ 会社名 番号 日付 会社名 現場名 金額 未収金額 作業列 請求日 作業列2 [3] _ あ建設2 78 2/21 省略2 Ａ邸2 10028 20028 あ建設2-1 2/15 02/15-1 [4] _ い建設2 79 2/23 省略2 Ｂ邸2 20028 40028 い建設2-1 2/10 02/10-1 [5] _ う建設2 80 2/24 省略2 Ｃ邸2 30028 60028 う建設2-1 2/10 02/10-2 [6] _ え建設2 81 2/27 省略2 Ｄ邸2 40028 80028 え建設2-1 2/25 02/25-1 [7] _ お建設2 82 2/27 省略2 Ｅ邸2 50028 100028 お建設2-1 2/25 02/25-2 [8] _ か建設2 83 2/2 省略2 Ｆ邸2 60028 120028 か建設2-1 2/25 02/25-3 [27] _ _ _ _ _ _ _ _ _ _ _
○【シートB】シート
[R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L]
[1] _ _ _ _ _ _ _ _ _ _ _ _
[2] 1月5日 会社名 1月10日 会社名 1月15日 会社名 1月20日 会社名 1月25日 会社名 1月30日 会社名
[3] 40000 い建設 20000 あ建設 100000 お建設 80000 え建設 _ _ 120000 か建設
[4] 60000 う建設 _ _ _ _ _ _ _ _ _ _
[5] _ _ _ _ _ _ _ _ _ _ _ _
[22] _ _ _ _ _ _ _ _ _ _ _ _
[23] 100000 合計金額 20000 合計金額 100000 合計金額 80000 合計金額 0 合計金額 120000 合計金額
[24] 100000 累計金額 20000 累計金額 100000 累計金額 80000 累計金額 0 累計金額 120000 累計金額
[25] 2月5日 会社名 2月10日 会社名 2月15日 会社名 2月20日 会社名 2月25日 会社名 2月30日 会社名
[26] _ _ _ _ _ _ _ _ _ _ _ _
[27] _ _ _ _ _ _ _ _ _ _ _ _
[28] _ _ _ _ _ _ _ _ _ _ _ _
[45] _ _ _ _ _ _ _ _ _ _ _ _
[46] 0 合計金額 0 合計金額 0 合計金額 0 合計金額 0 合計金額 0 合計金額
[47] 100000 累計金額 20000 累計金額 100000 累計金額 80000 累計金額 0 累計金額 120000 累計金額
[48] 3月5日 会社名 3月10日 会社名 3月15日 会社名 3月20日 会社名 3月25日 会社名 3月30日 会社名
[71] 4月5日 会社名 4月10日 会社名 4月15日 会社名 4月20日 会社名 4月25日 会社名 4月30日 会社名
[94] 5月5日 会社名 5月10日 会社名 5月15日 会社名 5月20日 会社名 5月25日 会社名 5月30日 会社名
[117] 6月5日 会社名 6月10日 会社名 6月15日 会社名 6月20日 会社名 6月25日 会社名 6月30日 会社名
[118] _ _ _ _ _ _ _ _ _ _ _ _
[119] _ _ _ _ _ _ _ _ _ _ _ _
[120] _ _ _ _ _ _ _ _ _ _ _ _
[137] _ _ _ _ _ _ _ _ _ _ _ _
[138] 0 合計金額 0 合計金額 0 合計金額 0 合計金額 0 合計金額 0 合計金額
[139] 100000 累計金額 20000 累計金額 100000 累計金額 80000 累計金額 0 累計金額 120000 累計金額
[140] 0 -1 0 -1 0 -1 0 -1 0 -1 0 -1
[141] 24 1 24 1 24 1 24 1 24 1 24 1
A3 =IF($Y3>COUNTIF(シートA!$AB$3:$AB$27,TEXT(OFFSET(A3,-$Y3,A$140),"mm/dd")&"*"),"",
INDEX(シートA!$L$3:$N$27,MATCH(TEXT(OFFSET(A3,-$Y3,A$140),"mm/dd")&"-"&$Y3, シートA!$AB$3:$AB$27,0),A$141))
↓ ↓
INDEX(シートA!$B$3:$Y$27,MATCH(TEXT(OFFSET(A3,-$Y3,A$140),"mm/dd")&"-"&$Y3, シートA!$AB$3:$AB$27,0),A$141))
→A3:L22フィルコピー
☆数式の変更は上の2箇所のみです。
・会社名の取得先をシートAのL列からシートAのB列に変更
・金額 の取得先をシートAのN列からシートAのY列に変更
☆月によってシート名が異なっているので、月ごとの表で、同じ場所を変更してください。
☆もう1つ変更するのは、作業行2（141行目）です。作業行2は、「24」と1の繰り返しになります。
☆以下、抽出先の決定方法の説明
・A3の数式のINDEXの部分は、INDEX(シートA!$B$3:$Y$27, MATCH(〜), A$141)となっており、これは
シートAの$B$3:$Y$27という範囲から、行番号がMATCH(〜)、列番号がA$141であるデータを抽出する
という意味です。
・例えば、
INDEX(シートA!$B$3:$Y$27, 1, 1)なら、$B$3:$Y$27の1番目の行で 1番目の列のデータ、つまりB3セルの「あ建設」（会社名）、
INDEX(シートA!$B$3:$Y$27, 1,24)なら、$B$3:$Y$27の1番目の行で24番目の列のデータ、つまりY3セルの「20000」（未収金額）、
を抽出します。
・ということで、抽出先の列番号として、作業行2に24と1を繰り返し入力しておけば、どの日付についても、
未収金額と会社名を抽出してくるのです。
（応用として、シートAの範囲を$A$3:$Y$27にして、作業行2を25と2の繰り返しにしても、全く同じ結果になります）
[R/C] [M] [N] [W] [X] [Y]
[1] 100000 累計金額 120000 累計金額 _
[2] 7月5日 会社名 7月30日 会社名 作業列
[3] _ _ _ _ 1
[4] _ _ _ _ 2
[5] _ _ _ _ 3
[22] _ _ _ _ 20
[23] 0 合計金額 0 合計金額 合計金額
[24] 100000 累計金額 120000 累計金額 累計金額
[25] 8月5日 会社名 8月30日 会社名 _
[26] _ _ _ _ 1
[27] _ _ _ _ 2
[28] _ _ _ _ 3
[45] _ _ _ _ 20
[46] 0 合計金額 0 合計金額 合計金額
[47] 100000 累計金額 120000 累計金額 累計金額
[48] 9月5日 会社名 9月30日 会社名 _
[71] 10月5日 会社名 10月30日 会社名 _
[94] 11月5日 会社名 11月30日 会社名 _
[117] 12月5日 会社名 12月30日 会社名 _
[118] _ _ _ _ 1
[119] _ _ _ _ 2
[120] _ _ _ _ 3
[137] _ _ _ _ 20
[138] 0 合計金額 0 合計金額 合計金額
[139] 100000 累計金額 120000 累計金額 累計金額
[140] 0 -1 0 -1 作業行1
[141] 24 1 24 1 作業行2
（コタ）
遅かったのに 本当に すいませんでした m(__)m
それで やってみたのですが
＞ ・会社名の取得先をシートAのL列からシートAのB列に変更
・金額 の取得先をシートAのN列からシートAのY列に変更
ここの部分ですが すいません 私の書き方が悪かったですね。シート２のＡ列には シート１のＹ列で シート２のＢ列には 今までどおりの シート１のＬ列のままでした。 すいません つまり
＞ 金額 の取得先をシートAのN列からシートAのY列に変更 の部分だけが 変更部分でした。
紛らわしい書き方で すいませんでした。 自分で 説明を 見ながら しようと試みましたが 頭がついていかず コタさんの書いて下さった 説明は 後から じっくりじっくり 研究しながらでないと ついけいけないようです (-_-;) 本当に すいません
コタさんの説明文を 考えながら 自分でやってみましたが あっていますでしょうか？ 見てください m(__)m
シート２のＡ３＝IF($Y3>COUNTIF(シートA!$AB$3:$AB$27,TEXT(OFFSET(A3,-$Y3,A$140),"mm/dd")&"*"),"",INDEX(シートA!$J$3:$Y$27,MATCH(TEXT(OFFSET(A3,-$Y3,A$140),"mm/dd")&"-"&$Y3, シートA!$AB$3:$AB$27,0),A$141))
と入れて 下の作業列の140行目は ０と−１の繰り返しで
141行目は １６と３ の繰り返しに しました。
抽出は してきていますが 偶然だといけないので すいません 合っていますでしょうか？
宜しくお願い致します m(__)m
ご迷惑を おかけします m(__)m
＞ シート２のＢ列には 今までどおりの シート１のＬ列のままでした。 ありゃ、これは私の早とちりでしたか。失礼しました。
＞ 自分でやってみましたが あっていますでしょうか 範囲をシートA!$J$3:$Y$27にして、141行目を16と3ですね。 大丈夫です。正しく抽出していますよ。
以下のように考えます。 ・J列からY列までは16列あるので、未収金額（Y列）を抽出するなら、141行目を16にする←OK ・L列の会社名は、J列から数えて3列目なので、会社名（L列）を抽出するなら、141行目を3にする←OK
なお他には、 ・修正前と同様にL列を範囲の左端と考え、シートA!$L$3:$Y$27にして、141行目を14と1にする ・いっそどの列でも抽出できるようにと、 シートA!$A$3:$Y$27にして、141行目を25と12にする などの変更でもOKです。 （コタ）
本当に いつも コタさんは 優しく 教えて下さって 又 私が間違えていたにもかかわらず コタさんの 文面から とても優しい人柄が伝わってきて こちらも 暖かな気持ちに なれます。 嬉しいです。 本当に ありがとうございました m(__)m 又 宜しくお願い致します m(__)m
今ファイルに入力していたのですが コタさんに教えてもらいたい事があります 昔 どこかで 聞いたら 無理と言われたので 諦めていたのですが 質問させて下さい 私の作っているファイルは 1月 というファイルの中に ２５枚シートがあります。 その25枚のシートから 売上表（これがシート１と書いていたものです）に リンク貼り付けで 1枚の ひと月分の 売上表として まとめています
そしてシート２に 入金予定日表が あります（今回教えて頂いた シート２と書いていたものです）
そして シート３に 以前教えて頂いた 会社別売上表 のシートがあります。 つまり全部で ２８枚のシートが あります これが 1月〜12月まで ファイルとして作ってあります。 2010年度 売上等 として一つのフォルダの中に 1月〜12月 のファイルとして あります
それで 今回も見て頂いた 入金予定日表は 1月5日〜1月30日 から12月5日〜12月30日 までの表が 1枚のシートとして 作ってありますが 結局 その中でも 使うのは 例えば 1月のファイルだとしますと、入金予定日は1月5日〜せいぜい 3月30日位までしか ないので、 だから 見る所も 1月5日〜3月30日 の表位までです。 入金が そんなに 長引く会社はないからです
なので 4月5日〜それ以降の 表の所は 1月の時には 必要ない と言えます。 他の 例えば 今月の5月も 入金予定日表を 開くと 見る所は 5月5日〜7月30日位までです あとの 8月以降の 表の所は 不要と言えば不要ですが
12月30日までの 表を何故 1枚ずつ 1月〜12月 までのファイルに 作ったのかと言いますと、 本当は まとめて 別のファイルとして 1年間入金予定日表 として 別のファイルで作って そこに 1月〜12月の それぞれのファイルから 入金予定日を 抽出して まとめたかったのですが、
別の所で昔 質問したら 配列数式とかＩＮＤＩＲＥＣＴとかになるので 動作が重くなってしまうのと、
1月〜12月までの ファイルを いったん全部 開いておいてからでないと その別ファイルとして 作った 入金予定日表は 見れなくて、 1月ファイルを 開いたら 1月ファイルから 抽出してきたものは見れるけど あとの 例えば 4月5日〜12月30日 までの 表の 所は ＶＡＬＵＥ だったか忘れましたが 見れないと言われたので
1月ファイルを 結局 同じファイルの中で 隣のシートに ひと月ずつ 作りました
本当は 12枚の 1月〜12月の ファイルの 売上表シート（シート１）の ＡＡ列に 入金予定日を それぞれに 入れたら 別ファイルの 年間入金予定日表 のファイル（しーと２の表です）の一枚に 全てが 抽出されて その日付の下に 例えば 3月5日ならば その下に 3月5日 の予定の 金額と会社名を 羅列してくれるように なればいいのですが、 やはり これは 無理なのでしょうか？
1月ファイルの 入金予定日に 例えば 3月5日 が出ますし、 2月のファイルにも 3月5日が 出ますし 3月のファイルにも 3月5日は 出てきます。
それぞれ 12枚の ファイルに 同じ3月5日 が何個か 出るのを 一枚の 別ファイルとして 入金予定日表に 集めて どんどん 下に羅列させていく… などというのは 出来るものなのでしょうか？ すいません 読んでくださるか 不安ですが いちおう ここに書きました 宜しくお願い致します
まとめると、こういうことですかね。
○現行 ・下記体系のブックが、1〜12月分の12個ある。 [1月分.xls]ブック ├【01】シート │ 〜 ├【25】シート ├【売上表】シート ※同ブック内の01〜25シートをまとめたもの ├【入金予定日表】シート ※同ブック内の売上表シートからリンク └【会社別売上表】シート ※同ブック内の売上表シートからリンク →これだと、入金予定日表シートには、1月分ブックの情報しかリンクされないので、使いづらい。
○変更 ・下記体系のブックを、1〜12月分のまとめとして1個作りたい。 [入金予定日表.xls]ブック └【入金予定日表】シート ※別ブックの売上表シートからリンク
以下に問題点などを、挙げてみます。
・今回の入金予定日表シートなどにも使用していますが、OFFSET関数についても、リンク先が別ブックで かつ、閉じられていると、#VALUE!エラーとなります。 →これについては、以下のようにシートを追加することで、回避できるかと思います。 （OFFSET関数などを使わずに、単純にリンクするだけなら、参照先ブックが閉じられていても更新可能） [入金予定日表.xls]ブック ├【売上表01月分】シート ※ 1月分.xlsブック内の売上表シートをそのままリンク │ 〜 ├【売上表12月分】シート ※12月分.xlsブック内の売上表シートをそのままリンク └【入金予定日表】シート ※同ブックの売上表01〜12月分シートからリンク
＞ 1月ファイルの 入金予定日に 例えば 3月5日 が出ますし、 2月のファイルにも 3月5日が 出ますし 入金予定日とは、「請求日」のことですよね。
・回答済の数式だと、 1月5日から1月30日の表については、【シートA 】シート （1月分）を参照し、 2月5日から2月30日の表については、【シートA2】シート （2月分）を参照し、 という想定でしたので、これだと、1月分の売上表シートに3月5日と入力されていると、抽出してきません。 →この場合は、【売上表年間分】シートを作成し、そこに1年分のデータを集めたうえで、それを 入金予定日表シートの全ての参照先にすれば、各月に抽出されるかと思います。 [入金予定日表.xls]ブックを以下のように変更 ├【売上表年間分】シート ※1月分.xls〜12月分.xlsブック内の売上表シートをそのままリンク（縦に並べる） └【入金予定日表】シート ※同ブックの売上表年間分シートからリンク
と、こんな感じにすれば実現できそうなのですが、まあやってみないことには分かりません。^^; ちょっと時間がかかるかもしれませんが、考えてみます。 （コタ）
私も考えてみましたが コタさんが 教えて下さった事が 私に理解できているのか … 違っていたら 本当に すいません m(__)m
＞ [入金予定日表.xls]ブックを以下のように変更 ├【売上表年間分】シート ※1月分.xls〜12月分.xlsブック内の売上表シートをそのままリンク（縦に並べる） └【入金予定日表】シート ※同ブックの売上表年間分シートからリンク
この部分ですが ５月分ﾌﾞｯｸの中の 売上表シート AAに 例えば5月5日 と入力したら 隣のシートの 入金予定日表 の 5月5日の下に 抽出されてきますよね それで 5月5日が 何個か出た場合、入金予定日表の 5月5日と書いた下に 何個か羅列していって それで 次に 6月分の ﾌﾞｯｸに 入力するときに、又 5月5日が 何個か出るのですが
それが 隣のシートの 入金予定日表シートの 5月5日 の真下に 又 5月ﾌﾞｯｸと 同じように 真下に向かって どんどん 抽出されてきて、 同じく 7月ﾌﾞｯｸにも 5月5日請求日のものが 入金予定日表シートの 5月5日 と書いてある 真下に どんどん 下に向けて 並んでいって、 3か月分が 同じ列の 大体同じ セルの場所 に並んでいく感じになります 5月5日から下に向けて 大体同じ請求日が ひと月の中で5個〜6個位 並ぶので、3ヶ月のシート が みんな A３〜A９位に 収まる感じです
すると その12枚の 入金予定日表シートから 一年分の入金予定日表シートに 向けて リンク貼り付け をする時には
1年分の 1年間入金予定日表ﾌﾞｯｸの中の 年間入金予定日表シートの Ａ２の5月5日 の列に 5月分ﾌﾞｯｸの 入金予定日表シートの 5月5日の列と 6月分ﾌﾞｯｸの 入金予定日表シートの 5月5日の列と 7月分ﾌﾞｯｸの 入金予定日表シートの 5月5日の列とを
1枚の年間分の 入金予定日表シートの 5月5日 の列に リンク貼り付けを する時は 同じ5月5日 の列に 3か月分の 5月5日の列を 重ねて リンク貼り付けを すればいいですか？ 5月を貼り付けて、次に6月を 同じ場所に貼り付けると 先の5月の リンクも 生きたままになりますか？
その場合 5月〜7月の 3か月分の 5月5日 の情報を 全て一年分の 入金予定日表のシート内の 5月5日の段の真下に 3か月分が どんどん羅列されていく という感じでしょうか コタさんの 言われている事を 私が理解できてないかも しれません すいませんm(__)m 本当に 知識が 乏しく お手間を 掛けていると思います ややこしくて 本当に 申し訳ないです コタさん 本当にすいません m(__)m なにとぞ 宜しくお願い致します
ちょっとイメージのずれがありますね。 ＞ すると その12枚の 入金予定日表シートから 一年分の入金予定日表シートに 向けて リンク貼り付け をする時には 「入金予定日表シート」からではなく、「売上表シート」からリンクします。
1.まずは、以下の3ブック4シートを、テスト用に作成してみてください。 2.次に、[1月分.xls]ブックだけ開いて、例えば、AA3の請求日を「1/10」→「1/15」に変更して、保存終了してください。 3.[入金予定日表.xls]ブックだけを開くと、2.の変更が反映されているのが、確認できると思います。
○ [1月分.xls]ブック【売上表】シート ※「_」セルは未入力または"" [R/C] [A] [B] [J] [K] [L] [M] [N] [Y] [Z] [AA] [AB] [1] _ _ _ _ _ _ _ _ _ _ _ [2] _ 会社名 番号 日付 会社名 現場名 金額 未収金額 作業列 請求日 作業列2 [3] _ あ建設 50 1/24 あ建設様 Ａ邸 10000 20000 あ建設-1 1/10 01/10-1 [4] _ い建設 51 1/26 い建設様 Ｂ邸 20000 40000 い建設-1 1/5 01/05-1 [5] _ う建設 52 1/27 う建設様 Ｃ邸 30000 60000 う建設-1 1/5 01/05-2 [6] _ え建設 53 1/30 え建設様 Ｄ邸 40000 80000 え建設-1 3/20 03/20-1 [7] _ お建設 54 1/30 お建設様 Ｅ邸 50000 100000 お建設-1 1/15 01/15-1 [8] _ か建設 55 1/5 か建設様 Ｆ邸 60000 120000 か建設-1 2/25 02/25-1 [27] _ _ _ _ _ _ _ _ _ _ _
Z3 =IF(B3="","",B3&"-"&COUNTIF(B$3:B3,B3)) →Z3:Z27フィルコピー （前と同じ） AB3 =IF(AA3="","",TEXT(AA3,"mm/dd")&"-"&COUNTIF(AA$3:AA3,AA3)) →AB3:AB27フィルコピー （前と同じ）
○ [2月分.xls]ブック【売上表】シート [R/C] [A] [B] [J] [K] [L] [M] [N] [Y] [Z] [AA] [AB] [1] _ _ _ _ _ _ _ _ _ _ _ [2] _ 会社名 番号 日付 会社名 現場名 金額 項目16 作業列 請求日 作業列2 [3] _ あ建設2 78 2/21 あ建設様2 Ａ邸2 10028 20028 あ建設2-1 2/15 02/15-1 [4] _ い建設2 79 2/23 い建設様2 Ｂ邸2 20028 40028 い建設2-1 3/10 03/10-1 [5] _ う建設2 80 2/24 う建設様2 Ｃ邸2 30028 60028 う建設2-1 2/10 02/10-1 [6] _ え建設2 81 2/27 え建設様2 Ｄ邸2 40028 80028 え建設2-1 2/25 02/25-1 ←注目 [7] _ お建設2 82 2/27 お建設様2 Ｅ邸2 50028 100028 お建設2-1 1/15 01/15-1 ←注目 [8] _ か建設2 83 2/2 か建設様2 Ｆ邸2 60028 120028 か建設2-1 2/25 02/25-2 ←注目 [27] _ _ _ _ _ _ _ _ _ _ _
○ [入金予定日表.xls]ブック【売上表年間分】シート ※ここに、1年間分の売上表データをリンクしてくる。 [R/C] [A] [B] [J] [K] [L] [M] [N] [Y] [Z] [AA] [AB] [1] _ _ _ _ _ _ _ _ _ _ _ [2] _ 会社名 番号 日付 会社名 現場名 金額 項目16 作業列 請求日 作業列2 [3] _ あ建設 50 1/24 あ建設様 Ａ邸 10000 20000 あ建設-1 1/10 01/10-1 [4] _ い建設 51 1/26 い建設様 Ｂ邸 20000 40000 い建設-1 1/5 01/05-1 [5] _ う建設 52 1/27 う建設様 Ｃ邸 30000 60000 う建設-1 1/5 01/05-2 [6] _ え建設 53 1/30 え建設様 Ｄ邸 40000 80000 え建設-1 3/20 03/20-1 [7] _ お建設 54 1/30 お建設様 Ｅ邸 50000 100000 お建設-1 1/15 01/15-1 [8] _ か建設 55 1/5 か建設様 Ｆ邸 60000 120000 か建設-1 2/25 02/25-1 [27] _ _ _ _ _ _ _ _ _ _ _ [28] _ あ建設2 78 2/21 あ建設様2 Ａ邸2 10028 20028 あ建設2-1 2/15 02/15-1 [29] _ い建設2 79 2/23 い建設様2 Ｂ邸2 20028 40028 い建設2-1 3/10 03/10-1 [30] _ う建設2 80 2/24 う建設様2 Ｃ邸2 30028 60028 う建設2-1 2/10 02/10-1 [31] _ え建設2 81 2/27 え建設様2 Ｄ邸2 40028 80028 え建設2-1 2/25 02/25-2 ←注目 [32] _ お建設2 82 2/27 お建設様2 Ｅ邸2 50028 100028 お建設2-1 1/15 01/15-2 ←注目 [33] _ か建設2 83 2/2 か建設様2 Ｆ邸2 60028 120028 か建設2-1 2/25 02/25-3 ←注目 [52] _ _ _ _ _ _ _ _ _ _ _
A3 =IF([1月分.xls]売上表!A3="","",[1月分.xls]売上表!A3) →A3 :AA27フィルコピー ※1月分からのリンク
A28 =IF([2月分.xls]売上表!A3="","",[2月分.xls]売上表!A3) →A28:AA52フィルコピー ※2月分からのリンク
※↑の数式は、各月のブックを同時に開いてある状態でのもの
（月ブックを閉じると、[〜]の部分がパス付に変わる）
※以下25行ずつ、最終302（=25*12+2）行目まで数式を設定 ←☆テスト段階では52行目まででOK
※各ブロックの左上の数式は、参照先シートのA3セルとする
AB3 =IF(AA3="","",TEXT(AA3,"mm/dd")&"-"&COUNTIF(AA$3:AA3,AA3)) →AB3:AB302フィルコピー
※各請求日が、年間を通じて何回出現するかカウントする必要がある
（↑の表で、31〜33行目のAB列の値が、[2月分.xls]ブック【売上表】シートの6〜8行目と異なることに留意）
○ [入金予定日表.xls]ブック【入金予定日表】シート
[R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L]
[1] _ _ _ _ _ _ _ _ _ _ _ _
[2] 1月5日 会社名 1月10日 会社名 1月15日 会社名 1月20日 会社名 1月25日 会社名 1月30日 会社名
[3] 40000 い建設様 20000 あ建設様 100000 お建設様 _ _ _ _ _ _
[4] 60000 う建設様 _ _ 100028 お建設様2 _ _ _ _ _ _
[5] _ _ _ _ _ _ _ _ _ _ _ _
[22] _ _ _ _ _ _ _ _ _ _ _ _
[23] 100000 合計金額 20000 合計金額 200028 合計金額 0 合計金額 0 合計金額 0 合計金額
[24] 100000 累計金額 20000 累計金額 200028 累計金額 0 累計金額 0 累計金額 0 累計金額
[25] 2月5日 会社名 2月10日 会社名 2月15日 会社名 2月20日 会社名 2月25日 会社名 2月30日 会社名
[26] _ _ 60028 う建設様2 20028 あ建設様2 _ _ 120000 か建設様 _ _
[27] _ _ _ _ _ _ _ _ 80028 え建設様2 _ _
[28] _ _ _ _ _ _ _ _ 120028 か建設様2 _ _
[45] _ _ _ _ _ _ _ _ _ _ _ _
[46] 0 合計金額 60028 合計金額 20028 合計金額 0 合計金額 320056 合計金額 0 合計金額
[47] 100000 累計金額 80028 累計金額 220056 累計金額 0 累計金額 320056 累計金額 0 累計金額
[48] 3月5日 会社名 3月10日 会社名 3月15日 会社名 3月20日 会社名 3月25日 会社名 3月30日 会社名
[49] _ _ 40028 い建設様2 _ _ 80000 え建設様 _ _ _ _
[50] _ _ _ _ _ _ _ _ _ _ _ _
[51] _ _ _ _ _ _ _ _ _ _ _ _
[68] _ _ _ _ _ _ _ _ _ _ _ _
[69] 0 合計金額 40028 合計金額 0 合計金額 80000 合計金額 0 合計金額 0 合計金額
[70] 100000 累計金額 120056 累計金額 220056 累計金額 80000 累計金額 320056 累計金額 0 累計金額
[71] 4月5日 会社名 4月10日 会社名 4月15日 会社名 4月20日 会社名 4月25日 会社名 4月30日 会社名
[94] 5月5日 会社名 5月10日 会社名 5月15日 会社名 5月20日 会社名 5月25日 会社名 5月30日 会社名
[117] 6月5日 会社名 6月10日 会社名 6月15日 会社名 6月20日 会社名 6月25日 会社名 6月30日 会社名
[118] _ _ _ _ _ _ _ _ _ _ _ _
[119] _ _ _ _ _ _ _ _ _ _ _ _
[120] _ _ _ _ _ _ _ _ _ _ _ _
[137] _ _ _ _ _ _ _ _ _ _ _ _
[138] 0 合計金額 0 合計金額 0 合計金額 0 合計金額 0 合計金額 0 合計金額
[139] 100000 累計金額 120056 累計金額 220056 累計金額 80000 累計金額 320056 累計金額 0 累計金額
[140] 0 -1 0 -1 0 -1 0 -1 0 -1 0 -1
[141] 25 12 25 12 25 12 25 12 25 12 25 12
A3 =IF($Y3>COUNTIF(売上表年間分!$AB$3:$AB$302,TEXT(OFFSET(A3,-$Y3,A$140),"mm/dd")&"*"),"",
INDEX(売上表年間分!$A$3:$Y$302,
MATCH(TEXT(OFFSET(A3,-$Y3,A$140),"mm/dd")&"-"&$Y3, 売上表年間分!$AB$3:$AB$302,0),A$141))
→A3:L22フィルコピー
A23 =IF(A$140=0,SUM(A3:A22),$Y23) →A23:L23フィルコピー （不変）
A24 =IF(A$140=0,SUM(A1,A23),$Y24) →A24:L24フィルコピー （不変）
※↑で1表分作ったあと、他の表にコピペしてください。
☆今回は、参照先シートが、売上表年間分シートだけなので、コピペ後に数式を変える必要はありません。
M1 =A139 →M1:X1フィルコピー （不変）
※↑累計金額の数式を、全て同じにするために、6月末累計を右半分の1行目にもってきます。
※作業行・列の数値は、手入力です。
作業行1は、0と-1の繰り返し。作業行2は、25（A列から数えたY列の位置）と12（L列）の繰り返しです。
[R/C] [M] [N] [W] [X] [Y]
[1] 100000 累計金額 0 累計金額 _
[2] 7月5日 会社名 7月30日 会社名 作業列
[3] _ _ _ _ 1
[4] _ _ _ _ 2
[5] _ _ _ _ 3
[22] _ _ _ _ 20
[23] 0 合計金額 0 合計金額 合計金額
[24] 100000 累計金額 0 累計金額 累計金額
[25] 8月5日 会社名 8月30日 会社名 _
[26] _ _ _ _ 1
[27] _ _ _ _ 2
[28] _ _ _ _ 3
[45] _ _ _ _ 20
[46] 0 合計金額 0 合計金額 合計金額
[47] 100000 累計金額 0 累計金額 累計金額
[48] 9月5日 会社名 9月30日 会社名 _
[49] _ _ _ _ 1
[50] _ _ _ _ 2
[51] _ _ _ _ 3
[68] _ _ _ _ 20
[69] 0 合計金額 0 合計金額 合計金額
[70] 100000 累計金額 0 累計金額 累計金額
[71] 10月5日 会社名 10月30日 会社名 _
[94] 11月5日 会社名 11月30日 会社名 _
[117] 12月5日 会社名 12月30日 会社名 _
[118] _ _ _ _ 1
[119] _ _ _ _ 2
[120] _ _ _ _ 3
[137] _ _ _ _ 20
[138] 0 合計金額 0 合計金額 合計金額
[139] 100000 累計金額 0 累計金額 累計金額
[140] 0 -1 0 -1 作業行1
[141] 25 12 25 12 作業行2
ボリュームが大きいので、ゆっくりと確認してみてください。 （コタ）
今やっていますが ちょっと時間がかかります。失敗を繰り返しながら じっくり やっています ちょっと 待ってくださいね 出来るまで ちょっと待ってください m(__)m m(__)m m(__)m すいません
コタさんって 凄い人ですね。何か本当に凄いですねー。やはり 日々勉強されているんですねー 驚きます。
もう 1月ブック〜12月ブックの中にある 入金予定日表シートは 削除しても大丈夫でしょうか？ それと 一つ判らなかったのが
＞※↑の数式は、各月のブックを同時に開いてある状態でのもの （月ブックを閉じると、[〜]の部分がパス付に変わる）
の所ですが、1月も2月も開いていなくても そのまま入金予定日表も 売上表年間分のシートも 両方 反映してきています。
＞（月ブックを閉じると、[〜]の部分がパス付に変わる） の所の意味は すいません 判りません (^_^;) しかしながら 綺麗に 抽出してきます！！ 本当に お世話をお掛けいたしました。 感謝致します。コタさん どうも ありがとうございます。
できましたか、よかったですね。
＞ もう 1月ブック〜12月ブックの中にある 入金予定日表シートは 削除しても大丈夫でしょうか？ リンクの参照元にはなっていないので、削除しても大丈夫ですよ。
＞ ※↑の数式は、各月のブックを同時に開いてある状態でのもの ＞ （月ブックを閉じると、[〜]の部分がパス付に変わる） ここの記述は、数式の表示が状況によって変わりますよ、ということを言いたかっただけでした。 抽出できるできないには、とくに影響しませんので、ご心配なく。
もう少し細かく説明しますと、以下のようなことです。 [入金予定日表.xls]ブック【売上表年間分】シートのA3セル数式について、
(1) [1月分.xls]ブックを閉じている場合
=IF('C:\〜\〜\[1月分.xls]売上表'!A3="","",'C:\〜\〜\[1月分.xls]売上表'!A3)
こんな感じで、ブックファイルのパス＋ブック名等という数式に（自動的に）なっていますね。
これは、[1月分.xls]ブックが閉じられているので、ファイルの保存されている場所まで指定しないと、
参照元のデータを抽出できないからです。
(2) [1月分.xls]ブックを同時に開いている場合 A3 =IF([1月分.xls]売上表!A3="","",[1月分.xls]売上表!A3) のように、パスが消えて、ブック名（＋シート名＋セル）だけの数式になります。 これは、[1月分.xls]ブックが同じExcel内で開かれているので、ブック名だけ指定すれば、 参照元のデータを抽出できるからです。 ※ちなみに、同ブック内の別シートを参照する場合には、例えば、売上表年間分!$AB$3:$AB$302 のように ブック名の指定も不要になります。 さらに、同シート内ならシート名も不要で、AA3のようにセルアドレスだけになりますね。 （コタ）
それと試しに やってみたのですが、 入金予定日表の1月〜2月とかの 月の区切りに 間に、つまり27行目の下に1列 合計を出す行を入れて 1ヶ月が28行にして、そして 次の 2月とかの月の初めの上の 1行目に 2月度売上表 みたいなのを書く行を 入れて、又下に 合計の行を入れて…としてみましたら、
ひと月が 28行分になるように 行を 挿入していってみたら 最後が３２５行になりました それで 入金予定日表の A3の数式を見たら
A3=IF($Y30>COUNTIF('売上表年間分 (2)'!$AB$3:$AB$324,TEXT(OFFSET(C30,-$Y30,C$140),"mm/dd")&"*"),"",
INDEX('売上表年間分 (2)'!$A$3:$Y$324,
MATCH(TEXT(OFFSET(C30,-$Y30,C$140),"mm/dd")&"-"&$Y30, '売上表年間分 (2)'!$AB$3:$AB$324,0),C$141))
となっておりまして 抽出はしてきています。しかし こんな事しても 大丈夫でしょうか？ すいません 宜しくお願い致します コタさん ありがとうございます m(__)m
最後は３３６行で
A3=IF($Y3>COUNTIF('売上表年間分 (2)'!$AB$3:$AB$335,TEXT(OFFSET(A3,-$Y3,A$140),"mm/dd")&"*"),"",
INDEX('売上表年間分 (2)'!$A$3:$Y$335,
MATCH(TEXT(OFFSET(A3,-$Y3,A$140),"mm/dd")&"-"&$Y3, '売上表年間分 (2)'!$AB$3:$AB$335,0),A$141))
になりました 大丈夫でしょうか？
＞ 入金予定日表の1月〜2月とかの 月の区切りに これは、[入金予定日表.xls]ブックの【売上表年間分】シートのことだと思いますが、
・現行のシート構成は、 空白行（1行）、タイトル行（1行）、1月分（25行）、2月分（25行）、、、12月分（25行） で、2+25*12=302行目が最終行となっています。ここから、 1月分と2月分の間、2月分と3月分の間、、、11月分と12月分の間の計11箇所に、 合計行、区切り行、月毎タイトル行の3行ずつを挿入し、 12月分の下に合計行を1行追加 すれば、3*11+1=34行増えますので、302+34=336行目が最終行で正しいです。
・ところで、[入金予定日表.xls]ブック【入金予定日表】シートの数式のほうは、 '売上表年間分 (2)'!$AB$3:$AB$335 、'売上表年間分 (2)'!$A$3:$Y$335 のように、335行目までが範囲となっていますが、これは、このままでもいいし、336に変更してもどちらでもいいです。
・もともと【売上表年間分】シートのAB列（作業列2）が、「xx/xx-x」の形式になっているときだけ、 【入金予定日表】シートに抽出されます。 なので、合計行などを追加したとしても、AB列はブランクですから、【入金予定日表】シートには影響しません。
・ただし、念のため【売上表年間分】シートの、各月毎の左上の数式が、参照元シートのA3セルになっているか、 は確認しておいたほうがいいかと思います。 （行挿入しただけなら、参照元は変わらないはずですが、念のため）
A3 =IF([1月分.xls]売上表!A3="","",[1月分.xls]売上表!A3) A31 =IF([2月分.xls]売上表!A3="","",[2月分.xls]売上表!A3) A59 =IF([3月分.xls]売上表!A3="","",[3月分.xls]売上表!A3) などなど （コタ）
本当に この度は お世話になりました いろいろと ややこしいことを 言い出して 本当に すいませんでした
長くつきあって頂き 本当にすみませんでした m(__)m コタさんに 教えて頂いて 本当に 仕事が楽になれました。 どうもありがとうございました
本当に ありがとうございました 感謝致します m(__)m m(__)m m(__)m
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.