Excel2010で複数条件下での重複しない件数

質問

「個別明細A」というシートに、B列に発生日、F列に支社名、X列に解消日が並んでおります。
同様に「個別明細B」「個別明細C」・・・とシートが続きます。
これらのシートは列を加えたり、データを並べ替えたり等の変形をすることができません。
この「個別明細」シート群のデータを元に、発生年度別未解消支社数を「集計表」というシートに表示したいと思っております。
この「集計表」シートには、B列に各年度の４月１日の日付が入っており、M列に「個別明細A」、N列に「個別明細B」、L列に「個別明細C」、以下右列に続く・・・に集計結果を表示する予定でいます。ちなみに、B列は「yyyy年度」と書式設定を指定してあります。

発生年度別未解消件数は計算できました。
=COUNTIFS('個別明細A'!$B$2:$B$1000,">="&(DATE(YEAR($B13),MONTH($B13),DAY($B13))),'個別明細A'!$B$2:$B$1000,"<"&(DATE(YEAR($B13)+1,MONTH($B13),DAY($B13))))

全体の支社数も計算できました。
=SUMPRODUCT(1/COUNTIF('個別明細A'!$F$3:$F$1000,'個別明細A'!$F$3:$F$1000))

この2つの合わせ技、発生年度別未解消支社数の算出方法がわかりません。
ご教授の程、よろしくお願いいたします。

回答No.4

MackyNo1

>小躍りしながら早速、開始行を「2」から「3」に変え、出来上がった数式を配列数式に変換してみましたが、なぜか答えはどの年度も「0」に。
)=ROW(B3:B1000)-1))
そこで、他サイトを参考に、式のこの部分の「B3」を「B1」にしてみたところ、0以外の数字が各年度とも出てくるようになりましたが、正しい答えではありませんでした。

開始行を「2」から「3」に変えたら、最後の「ROW(B2:B1000)-1)」を「ROW(B3:B1000)-2)」に変える必要があります（Match関数で3行目のセルからスタートしているためこれが「１」に対応するようにするため）。

＞個別明細A!$F$2:$F$1000,"XX")
という部分の「XX」とは何を意味しているのでしょう？

個別明細AシートのB列のデータが特定の範囲に入っている場合は、F列の値を、それ以外はF列にない適当な値「XX」を返す配列を取得し、この配列を検査値とし、同様に空白文字列「””」を返す配列を範囲とするMATCH関数で最初に検索値が出てくる順番の配列を求め（2回目に出てくる値の返す順番は1回目の順番になりますので、この順が行番号から補正値（3行目から開始する場合は2）を引いた値が等しくなる配列を作成し（最初に検索値が出てくるデータだけにTRUEが返ります）。この配列を１で割り算すると、TRUEの部分のみ１が返り、それ以外はVALUEエラーになります。
COUNT関数はエラー値を無視して数字だけを集計しますので、目的とする数字を得ることができることになります。

補足

詳しく解説いただき、ありがとうございます。
「XX」は、支社としてカウントしないための仮の値で、「ROW(B$3:B$1300)-2」のマイナス部分はMatch関数の配列の開始行を揃えるためのもの・・・という理解であっていますでしょうか。

最終的に、
=COUNT(1/(MATCH(IF('個別明細A'!$B$3:$B$1000>=DATE(YEAR($B13),MONTH($B13),DAY($B13))*('個別明細A'!$B$3:$B$1000<DATE(YEAR($B13)+1,MONTH($B13),DAY($B13))),'個別明細A'!$F$3:$F$1000,"XX"),IF('個別明細A'!$B$3:$B$1000>=DATE(YEAR($B13),MONTH($B13),DAY($B13))*('個別明細A'!$B$3:$B$1000<DATE(YEAR($B13)+1,MONTH($B13),DAY($B13))),'個別明細A'!$F$3:$F$1000,""),0)=ROW(B$3:B$1300)-2))
という式の配列数式にしました。
ところが、解消が0件の年度は「年度を考慮せずにカウントした未解消支社数(年度が相違していても、支社名が同じであれば合わせて１支社とカウント)」になっていたり、解消日が入っている年度は、その年度の総支社数より大きい数値になっていたりという悲しい結果に。

尚、空欄があるのはF列(解消日)だけで、他の列は全て日付か文字が入力されており、空欄はありません。

どうかお助けください・・・

回答No.3

KURUMITO

未解消支社数で、しかもダブりのない支社数となりますと式は複雑になり、しかも配列数式などを使うことで計算にも負担がかかりますね。
作業列を個別明細の各シートに作って対応することが分かり易く計算にも負担がかからない方法としてお勧めです。
例えば次のようにします。
個別明細の各シートで次の操作を行います。
例えばAA２セルには次の式を入力して下方にドラッグコピーします。

=IF(B2="","",DATE(IF(MONTH(B2)>=4,YEAR(B2),YEAR(B2)-1),4,1))

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

=IF(OR(F2="",X2<>""),"",F2)

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

=IF(AB2="","",IF(COUNTIF(INDEX(AB:AB,MATCH(AA2,AA:AA,0)):AB2,AB2)=1,IF(COUNTIF(AA$2:AA2,AA2)=1,1,MAX(INDEX(AC:AC,MATCH(AA2,AA:AA,0)):OFFSET(AC2,-1,0))+1),""))

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

=IF(AC2="","",IF(COUNTIF(AA$2:AA2,AA2)=COUNTIF(AA:AA,AA2),AC2,""))

出来れば上のような作業列をあるシートで作ってそれをコピーして他の個別明細のシートに貼り付けをすればよいでしょう。
なお、B列の日付は昇順に並べられている必要が有ります。

以上で個別シートの作業は終わって集計表シートについてはM１セルから右の列に個別明細Aなどのシート名が入力されているとしてM２セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にドラッグコピーします。

=IF(OR(B2="",M$1=""),"",IF(COUNTIF(INDIRECT(M$1&"!AA:AA"),B2)=0,"",INDEX(INDIRECT(M$1&"!AD:AD"),MATCH(B2,INDIRECT(M$1&"!AA:AA"),0)+COUNTIF(INDIRECT(M$1&"!AA:AA"),B2)-1)))

該当年度におけるダブりのない支社の数は表示されます。

お礼

わかりやすく丁寧なご指南ありがとうございます。

今回、「個別明細」シートは加列等の加工ができないため、教えて頂いた形式を使用するには、「集計表」シートの枠外等に各「個別明細」シートの作業列を格納するという方法が考えられますが、これはこれで膨大なシート間のデータのやり取りを必要とすることになり、負荷がもっと高そうに感じました。
ただ、この形式も勉強になりますし是非試してみたいと思っています。

困っているところに手を差し伸べてくださって、本当にありがとうございました。

回答No.2

MackyNo1

例示の数式ではF列の支社のデータ位置とB列の日付入力セルの位置が一致していませんが、２行目からデータが入力されているなら、以下の式を入力してCtrl+Shift+Enterで確定して配列数式にすれば、重複のない支社数が表示できます。

=COUNT(1/(MATCH(IF(個別明細A!$B$2:$B$1000>=DATE(YEAR($B13),MONTH($B13),DAY($B13))*(個別明細A!$B$2:$B$1000<DATE(YEAR($B13)+1,MONTH($B13),DAY($B13))),個別明細A!$F$2:$F$1000,"XX"),IF(個別明細A!$B$2:$B$1000>=DATE(YEAR($B13),MONTH($B13),DAY($B13))*(個別明細A!$B$2:$B$1000<DATE(YEAR($B13)+1,MONTH($B13),DAY($B13))),個別明細A!$F$2:$F$1000,""),0)=ROW(B2:B1000)-1))

＃ひとまず提示された数式を元に数式を作成してみましたが、他シートを参照すると数式が複雑になるので、同じシートで検証したほうが良いように思います。

補足

ありがとうございます。私が欲しい式はこれです!
また、ご指摘の通り、２行目からではなく３行目からデータが始まっていますので、例示した「B2」「F2」は全て「B3「F3」が正しいです。

小躍りしながら早速、開始行を「2」から「3」に変え、出来上がった数式を配列数式に変換してみましたが、なぜか答えはどの年度も「0」に。

)=ROW(B3:B1000)-1))

そこで、他サイトを参考に、式のこの部分の「B3」を「B1」にしてみたところ、0以外の数字が各年度とも出てくるようになりましたが、正しい答えではありませんでした。

また、手詰まりになってしまいました・・・。

ちなみに、

,個別明細A!$F$2:$F$1000,"XX")

という部分の「XX」とは何を意味しているのでしょう？

質問ばかりで申し訳ございません。
よろしくお願いいたします。

回答No.1

MSZ006

やりたいことがはっきりしないのですが、

１）年度ごとに集計表示する（発生日で判断する）
２）表示セルが明記されてませんが、例えばB列の隣のC列に表示する
３）未解消というからには、各明細シートごとに「解消日」が入力されていない支社の数をカウントする（支社ごとに何件の未解消案件があるかは関係ない）　→この値はM列以降に表示
４）　３）をもとに全てのシートの合計を出すが、支社の重複は省く　→これをC列に表示

というようなことでしょうか？

なお、提示いただいた計算式は何かが間違っていると思います。（一つ目は単に年度ごとの明細件数を数えている、２つ目は計算式の体をなしていない、エラーになる）

補足

うまく伝えきれず、申し訳ありません。

１）年度ごとに集計表示する（発生日で判断する）
→その通りです。

２）表示セルが明記されてませんが、例えばB列の隣のC列に表示する
→M列の任意のセルに個別明細Aの集計結果を表示します。

３）未解消というからには、各明細シートごとに「解消日」が入力されていない支社の数をカウントする（支社ごとに何件の未解消案件があるかは関係ない）　→この値はM列以降に表示
→その通りです。
支社ごとの件数は必要ありません。

４）　３）をもとに全てのシートの合計を出すが、支社の重複は省く　→これをC列に表示
→シートごとに支社数を算出し、個別明細Aの支社数ならM列、個別明細Bの支社数ならN列に表示します。
全てのシートを合わせた支社数は、各シートごとの支社数を表示した列の最右列に表示します。

なお、提示いただいた計算式は何かが間違っていると思います。（一つ目は単に年度ごとの明細件数を数えている、２つ目は計算式の体をなしていない、エラーになる）
→一つ目の式はおっしゃるとおりです。「発生年度別未解消件数」までしか算出できていませんので、この式に、支社の重複を除いた」という条件を付け加えたいのです。