[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『参照先がおかしくなってしまいます』（たむりん）
いつもお世話になっております。
今日は具体的な質問でなく、ちょっとご相談なのですが・・・。 以下のような上司が作った集計表があります。
〔A〕 〔B〕 〔C〕 〔D〕 1 月 № 取引先からのコメント 理由 2 4月 123-456-789 4/8出荷 納期遅れ 納期遅れ 3 123-456-790 手配したのに届かなかった 手配漏れ 4 5 5月 123-456-456 同じ場所に不具合が出た 修繕ミス 6 456-789-014 作業員の仕事が遅い スキル不足 7 339-156-321 値段が高い 価格不満 8 9 6月 123-456-880 6/20出荷 納期間に合わず 納期遅れ 10 123-456-888 6/25出荷 納期間に合わず 納期遅れ 11
D列は入力規制にて入力していて、同じシートの別表に理由別にカウントしています。 4月分は参照範囲をD2からD4となっています。 これ以上行を増やす場合は４行目を指定して挿入をすれば問題ないのですが、 上司は４月分を２行目に挿入したり５行目にする為、別表が参照先を反映してくれません。 以前も同じようなことがあり、その度に計算式を手直ししています。 この表の作り方にも問題があると思うのですが、毎月こんな目に遭わないようにする為の解決策を模索中です。 良い方法があれば、ご教授願いますm(__)m ヒントでもいただければ幸いです。
・・・毎月こんな事やってられません；；
こんにちは～♪
>毎月こんな事やってられません
大変ですね。。。 そういう上司の方,うちの会社にもいますよ～。。。
>同じシートの別表に理由別にカウントしています。
どんな感じですか？ 月別ではないんでしょうか？
トータルなら、COUNTIFで、範囲は列参照で良さそうですけれど。
式を提示して頂くと良いのですが？
。。。Ms.Rin～♪♪
Ms.Rinさん、ありがとうございます。 どこの会社にも、そんな方がいらっしゃるのですねーー；
月別、各拠点ごとで集計しています。（以下抜粋です。）
現象分類 4月 5月 6月 納期遅れ 1 0 2 手配漏れ 1 0 0 単なる不満 0 0 0
例えば、4月納期遅れのところには=COUNTIF($D$2:$D$4,A745)という様な感じで入っています。 （たむりん）
最初のシート（仮にSheet1とします。)のA列で4月と入力されている行から5月の行の前までを COUNTIFの範囲として指定したいということでしょうか。 便宜上、A列の6月の下のほうに（上の例ではA11に）「最終」と入力して、
Sheet2
A B C D E
1 4月 5月 6月 最終
2 納期遅れ 1 0 2
3 手配ミス 0 0 0
4 修繕ミス 0 1 0
5 スキル不足 0 1 0
6 価格不満 0 1 0
7 手配漏れ 1 0 0
Sheet2のB2に、 =COUNTIF(OFFSET(Sheet1!$D$1,MATCH(B$1,Sheet1!$A$2:$A$20,0),0,MATCH(C$1,Sheet1!$A$2:$A$20,0)-MATCH(B$1,Sheet1!$A$2:$A$20,0)),$A2)
と入力して縦横にコピーするとこんな感じになります。 最後の行を判断するのに「最終」の文字を入力しておく必要がありますが苦肉の策ということで。 もっといい方法もあるとは思うのですが、今のところ思いつきません。 (sato)
おはようございます。satoさん、考えて下さってありがとうございます。 ただ、4月～翌年3月までは既に表が作ってあります。 （最初に提示した表も、後から提示した表も。） 2つ目の表には全てにCOUNTIFの計算式が入っており、データを引っ張ってくるようにしてあります。 今現在、同じシートに作ってありますが、やはり別シートで作った方がいいのですかね？ satoさんの書いて下さった式は今から検証してみます。 （たむりん）
同じシートでも別シートでもどちらでも良いと思います。 ただ、たむりんさんの集計表にセル番地が書いてなかったので、 例としてSheet2のA1を基点として考えただけです。 同じシートのほうが、わざわざ Sheet1!・・・なんてつけなくても良いので、 そういう意味では同じシートのほうが楽ですし、式も見やすいですね。 (sato)
satoさんと衝突してしまいましたm(__)m 今、色々やってみているのですが（汗） 以下そのままコピペです。
すみません、もう１つ問題が・・・。 最初に提示したD列の理由は別表から引っ張って来て、リスト入力にしてあります。 別シートにすると定義で範囲指定をして、名前をつけてあげないといけませんよね？ 理由はどんどん増えていくので、範囲指定をすることが出来ません；； （たむりん）
お邪魔します。A列の月データを空白なく埋めると、SUMPRODUCT関数で出せると思うのですが、 いかがでしょう？
下のような配置だとすれば、B2に =SUMPRODUCT(($A$9:$A$100=B$1)*($D$9:$D$100=$A2)) として、右に下にコピー。 隙間なくデータを埋めるには、[[20060711115806]]『空白セルを含む並べ替えについて』（ワーキングママ） こちらで、川野鮎太郎さんがご紹介されている方法はどうでしょう。 また、月データの重複を見えなくするためには、条件付き書式でフォントを背景色にするで対応で。。。 A B C D 1 4月 5月 6月 2 納期遅れ 1 0 2 3 手配漏れ 1 0 0 4 修繕ミス 0 1 0 5 スキル不足 0 1 0 6 価格不満 0 1 0 7 8 月 № 取引先からのコメント 理由 9 4月 123-456-789 4/8出荷 納期遅れ 納期遅れ 10 4月 123-456-790 手配したのに届かなかった 手配漏れ 11 4月 12 5月 123-456-456 同じ場所に不具合が出た 修繕ミス 13 5月 456-789-014 作業員の仕事が遅い スキル不足 14 5月 339-156-321 値段が高い 価格不満 15 5月 16 6月 123-456-880 6/20出荷 納期遅れ 納期遅れ 17 6月 123-456-888 6/25出荷 納期遅れ 納期遅れ (かなれっと)
かなれっとさん、ありがとうございます。 この方法で出来ました。 でも、うちの上司が川野鮎太郎さんの方法で作業をしてくれるとは思えませんーー； ○月と入力してくれれば問題ないのですがねぇ。 自分で入力して数が合わないと「たむりんさん、数が合わないよ！！計算式間違ってない？！」とおお騒ぎすると思うので、 その時はやはりわたしがやるのでしょうね^^; 上司がデータ入力して完了する方法はなかなか難しそうですね・・・。 （たむりん）
こんにちは～♪ Rinです。。。
>○月と入力してくれれば問題ないのですがねぇ。 そうです。そうで～す。
エクセルとはそう言うもんです。 わかって下さ～い!!。 な～んて、言ってあげられれば良いですね。
この事だけでマクロを使うのはどうかと 思いますけれど。 どうかな～。。。
★順序 その１→ALT+F11キーで、VBEを起動します。 その２→左上のプロジェクトウィンドウのSheet1 を ダブルクリック。 その３→真中の白いウィンドウ（コードウィンドウ に以下のコードをコピペして下さい。
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 2 Then Exit Sub
If .Value = "" Then Exit Sub
Application.EnableEvents = False
Range("A" & .Row).Value = Range("A" & .Row).Offset(-1).Value
Application.EnableEvents = True
End With
End Sub
これは、月毎に、空白行を挿入した場合、 B列のセルに、Noを入力した時、左のA列のセルに その上の月を表示させるイベントマクロです。
マクロのスキルが無いので、月の初めの最初のA列のセルには 月を入力しておいて下さい。
新規ブックで、試してみてください。 大先輩の回答者の皆さ～ん。おかしい箇所があったら 直して下さ～い。
☆それから、関数で月を表示する方法ですが。。 たとえば、最初の表のE列に作業列を作ります。
E2 =IF(COUNTA(B2),INDEX(A:A,MATCH("ー",$A$1:A2)),"")
を下にコピーします。
データ行だけ、月が表示されます。
これで、皆さんのSUMPRODUCTで計算できます。
又、
E2 =IF(COUNTA(B2),INDEX(A:A,MATCH("ー",$A$1:A2))&D2,"")
下にコピー。
にすれば、SUMIF関数で計算出来ます。
ただ、この式を入れても行の挿入があると その行の式は、無いので、E列の式のある行を下にコピー しなくてなけませんけれど。
月を入れるよりは、楽ですね。。。
こちらもご参考にどうぞ～。。。
。。。。Ms.Rinでした～♪♪
これは別シートに、例えば「チョンボのデータ集計」とかというSheetネームを付けて
おき、そのシートを開くたんびに入力シート（現時点ではSheet1）の最新データを転
送するもんです。
以下はそれぞれ違うシートにコピペして試してみてくらはい。
うまくいかんかったら容赦を・・・（笑
（弥太郎）
’Sheet1のモジュールへ
'-------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
flag = True
End Sub
'抽出したいシートモジュールへ
'----------------------
Private Sub Worksheet_Activate()
Dim dic1 As Object, i As Long, n As Integer, tbl_1, tbl, x, ary()
Dim dic2 As Object, maxrow As Long, maxcol As Integer, j As Integer
If Not flag Then Exit Sub
Set dic1 = CreateObject("scripting.dictionary")
Set dic2 = CreateObject("scripting.dictionary")
maxrow = Range("a" & Rows.Count).End(xlUp).Row
maxcol = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(2, 2).Resize(maxrow, maxcol).ClearContents
tbl_1 = Range("a1").Resize(maxrow, maxcol)
For i = 0 To UBound(tbl_1, 1) - 2
ReDim Preserve ary(i)
ary(i) = tbl_1(i + 2, 1)
Next i
For n = 2 To UBound(tbl_1, 2)
dic1(tbl_1(1, n)) = ary
dic2(tbl_1(1, n)) = n - 1
Next n
With Sheets("sheet1")
tbl = .Range("a2").Resize(.Range("b" & Rows.Count).End(xlUp).Row, 4)
ReDim x(1 To UBound(tbl_1, 1) - 1, 1 To UBound(tbl_1, 2) - 1)
i = 0
Do While i < UBound(tbl, 1)
i = i + 1
If dic1.exists(tbl(i, 1)) Then
j = i
Do While tbl(j, 2) <> ""
If tbl(j, 2) <> "" Then
s = Application.Match(tbl(j, 4), dic1.Item(tbl(i, 1)), 0)
If Not IsError(s) Then
x(s, dic2.Item(tbl(i, 1))) = x(s, dic2.Item(tbl(i, 1))) + 1
End If
End If
j = j + 1
Loop
End If
i = j
Loop
End With
Cells(2, 2).Resize(UBound(tbl_1, 1) - 1, UBound(tbl_1, 2) - 1) = x
Cells(1, 1).Resize(UBound(tbl_1, 1), UBound(tbl_1, 2)).SpecialCells(xlCellTypeBlanks) = 0
flag = False
Set dic1 = Nothing
Set dic2 = Nothing
End Sub
'標準モジュールへ
'-------------------
Public flag As Boolean
Sub auto_open()
flag = True
End Sub
'-------------------- なんせ急ごしらえなもんで、ディクショナリーが２個も登場する豪華マクロですわ。（笑
sheet1
〔A〕 〔B〕 〔C〕 〔D〕
1 月 № 取引先からのコメント 理由
2 4月 123-456-789 4/8出荷 納期遅れ 納期遅れ
3 123-456-790 手配したのに届かなかった 手配漏れ
4
5 5月 123-456-456 同じ場所に不具合が出た 修繕ミス
6 456-789-014 作業員の仕事が遅い スキル不足
7 339-156-321 値段が高い 価格不満
8
9 6月 123-456-880 6/20出荷 納期間に合わず 納期遅れ
10 123-456-888 6/25出荷 納期間に合わず 納期遅れ
11 最終
最終行に 最終と入力の事、
sheet2
A B C D E
1 4月 5月 6月 最終
2 納期遅れ 1 0 2
3 手配ミス 0 0 0
4 修繕ミス 0 1 0
5 スキル不足 0 1 0
6 価格不満 0 1 0
7 手配漏れ 1 0 0
sheet2!B2に
=COUNTIF(INDIRECT("Sheet1!D"&MATCH(B$1,Sheet1!$A:$A,0)&":D"&MATCH(C$1,Sheet1!$A:$A,0)-1),$A2)
右へ下へフィールコピー
（HHHH)
一応聞いておいてみますが >すみません、もう１つ問題が・・・。 >最初に提示したD列の理由は別表から引っ張って来て、リスト入力にしてあります。 >別シートにすると定義で範囲指定をして、名前をつけてあげないといけませんよね？ >理由はどんどん増えていくので、範囲指定をすることが出来ません；； これは集計シートを別にした場合・・・ですよね？ 「範囲可変で名前の定義」なんて事は出来ると思いますが ・・・結局どの方針で（シートは一緒が良いのか、別が良いのか。 最終行にフラグを入れられるのか、入れられないのか・・・等） 何が現在の問題点でしょう？（まぁ、最大の問題点は 上の方が エクセルをエクセルとして扱ってくれないことでしょうけど。笑）
(HANA)
うひゃ～。一夜明けたら、怒涛のレスが＠＠ 皆様ありがとうございます！！ 今日はちょっと検証している時間がないので、後日検証結果をお知らせしたいと思います。 マクロはほとんど理解が出来ません＞＜ どこをどう直してよいのかわからないあんぽんたんですm(__)m あと・・・シートは別にする必要はなさそうです^^ 本当にありがとうございます！！！m(__)m
独り言・・・安い時給のパートを悩ませないで欲しいです（涙）＜上司様 （たむりん）
どなたか存じませんが
[返信・編集]から入ると、他の人の文章が変更出来るからと言って 勝手に変更してはいけません。 うっかり変更してしまったのなら、充分注意して下さい。
(HANA)
おはようございます。 未だ検証中です、、、、やはりシートは分けない方が良いとの結論に達しました。 せっかくコードを書いていただいたのですが、 マクロがわかっていないので、後日不具合が出た時に自分でなおせません；； 使えればかなり便利になるとは思うのですが、、、残念です。 上司がどういう行動に出るのか予測不能ですので、 わたしの理解出来る関数を使い、地道に直していくしかないのだなと思いました。
役席者で本当にエクセルをエクセルらしく使っている人が少ないので、びっくりします。 （例えば、日付を文字列で入れているとかーー；） これでもまだうちの上司はマシな方かもしれません。 他の一覧表も期限管理をしないといけないのに、元の表が悲惨な状態ですので本当に困ります。 長々と愚痴を書いてしまってすみません。 また、新しく表を作り直さないといけないと思います。 その時はよろしくお願い致します。 皆さんのご協力、本当に感謝しております。 （たむりん）
なんだかこのスレ収束モードなのですが、レスしても大丈夫ですかね？ 昨日は小言で満足して、肝心な事をすっかり書き忘れてました。（笑）
最初の問題点 >上司は４月分を２行目に挿入したり５行目にする為、 これだけが問題なら、範囲を上下に一行ずつ多くとっておくのはどうでしょう。
つまり、条件としては
１．別表にはあらかじめ各数式を入力しておく。
(例えば、4月納期遅れのところには=COUNTIF($D$2:$D$4,A745)という様な感じで)
２．一つの月は一つのまとまりとして入力される。
３．データは行の挿入を行って入力する。
４．月と月の間には一行空白行が入る。
(４に関しては数式次第ですが）
この条件で、２行目や５行目に行を挿入されても
勝手に参照セルが拡張されて計算される（はず）の数式です。
たとえば、A744から別表が始まっているとして （A745が「納期遅れ」の項目です。）その式は =COUNTIF($D$1:$D$5,$A745)-($D$5=$A745) こんな感じです。 実際のカウントしたいセル範囲は「$D$2:$D$4」ですがその範囲を 上下に一行ずつ増やします。「$D$1:$D$5」 これで、２行目や５行目に挿入されても数式は勝手に変更されます。 問題は、D5が「納期遅れ」の場合それもカウントされてしまうので 「-($D$5=$A745)」この部分で引き算します。
もう一つは、心理作戦として（のってもらえるかどうかは不明ですが） リストに罫線を引いておいて、月の枠のみ太線にします。 ┏━━━┯━━━━━━┓（細い横線は省略してます。） ┃月 │No ┃ ┣━━━┿━━━━━━┫ ┃4月 │123-456-789 ┃ ┃ │123-456-790 ┃ ┃ │ ┃ ┣━━━┿━━━━━━┫ ┃5月 │123-456-456 ┃ ┃ │456-789-014 ┃ ┃ │339-156-321 ┃ ┃ │ ┃ ┣━━━┿━━━━━━┫ ┃6月 │123-456-880 ┃ ┃ │123-456-888 ┃ ┃ │ ┃ ┗━━━┷━━━━━━┛
たとえば、２行目を選んで挿入した場合 ┏━━━┯━━━━━━┓ ┃月 │No ┃ ┣━━━┿━━━━━━┫ ┃ │ ┃←いかにも４月っぽくない。 ┣━━━┿━━━━━━┫ ┃4月 │123-456-789 ┃ ┃ │123-456-790 ┃ ┃ │ ┃ ┣━━━┿━━━━━━┫ ┃5月 │123-456-456 ┃
５行目を選んで挿入した場合も同じように挿入した部分だけ 太い枠線で囲まれます。
上の方に言ってやって下さい。 「この枠の中のものがその月として集計される式になっています」 いや、通用するかしないかは別ですよ。（笑）
(HANA)
おはようございます。 HANAさん、懇切丁寧にありがとうございます。 こちらも参考にさせていただきますね。 実際の表にはもちろん罫線が引かれています。 で、上でHANAさんが書かれていらっしゃる「いかにも4月っぽくない」箇所の罫線を修正して、 何事もなかったかの様に取り繕う知恵だけは持っています^^; まぁ計算式にブランクがあるので、ここに行を追加して修正したなっていう事は探せばわかるのですけど。 説明して、その時は「わかった」と返事があるのですが、健忘症で忘れてしまうらしいですーー； 溜息しか出ない、（たむりん）でした。
これでだめでしたか
sheet2!B2に
=COUNTIF(INDIRECT("Sheet1!D"&MATCH(B$1,Sheet1!$A:$A,0)&":D"&MATCH(C$1,Sheet1!$A:$A,0)-1),$A2)
右へ下へフィールコピー
（HHHH)
同じシートなら
=COUNTIF(INDIRECT("D"&MATCH(B$1,$A:$A,0)&":D"&MATCH(C$1,$A:$A,0)-1),$A2)
A B C D E
10 4月 5月 6月 最終
11 納期遅れ 1 0 2
12 手配ミス 0 0 0
13 修繕ミス 0 1 0
14スキル不足 0 1 0
15 価格不満 0 1 0
16手配漏れ 1 0 0
"D"&MATCH(B$1,$A:$A,0)&":D"&MATCH(C$1,$A:$A,0)-1 でその月の範囲選択
HHHHさんもありがとうございます。 何度もすみませんでした。
皆さんのアイデアを参考にして、自分なりに考えていきたいと思います。 重ね重ねありがとうございました。
（たむりん）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.