EXCEL-同じ組み合わせになった回数

質問

勤務表を作成していてるのですが、画像のように上の３名（C、D、F）としたの３名（Ｊ、Ｋ、Ｌ）が同じシフトになった回数（Ｈは休みなのでカウントせずにAとBの時のみ）を計算したいのですがどのようにしたらいいのでしょうか？

画像の場合ですと
C-J：2回　　C-K：2回　　C-L：3回

D-J：2回　　D-K：4回　　D-L：1回

E-J：4回　　E-K：0回　　E-L：2回

となりますが自動的に表示できるようにするにはどうすればいいでしょうか？
よろしくお願いいたします。

回答No.2

keithin · ベストアンサー

C-J：
=SUMPRODUCT((B2:K2=B6:K6)*(B2:K2<>"Ｈ"))
以下同文のカンジで。

言わずもがなですが式中の「Ｈ」は全角半角を区別しますので、実際にあなたがセルに記入している内容で数式を作成してください。

お礼

簡潔でわかりやすく大変助かりました。
ありがとうございました。

回答No.4

KURUMITO

自動的に表示させるということは組み合わせも含めて表示したいとのことでしょう。
簡単な関数を使って処理するには次のようにするのがよいでしょう。データ数が多くなれば複雑な関数を使った場合には計算に負担がかかります。
お示しの表でＡ２セルにはＣ，Ａ３セルにはＤ，Ａ4セルにはＥ，Ａ6セルにはＪ，Ａ7セルにはＫ，Ａ8セルにはＬのデータがあり右横の列にそれぞれのデータが有るのですがＢ列は空の行にしてＣ列からＬ列までにデータが入力されているとします。
そこで勤務者の組み合わせをＡ10セルからＡ18にかけて、また、それらの組み合わせにおけるＣからＬ列での組み合わせを表示させることにします。そのためにはＡ10セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>9,"",INDEX($A:$L,ROUNDUP(ROW(A1)/3,0)+1,COLUMN(A1))&"-"&INDEX($A:$L,MOD(ROW(A1)-1,3)+6,COLUMN(A1)))

つぎにＣ10セルには次の式を入力して入力してＬ10セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>9,"",INDEX($A:$L,ROUNDUP(ROW(A1)/3,0)+1,COLUMN(C1))&"-"&INDEX($A:$L,MOD(ROW(A1)-1,3)+6,COLUMN(C1)))

次にお求めの各組合せにおける同じ組み合わせになった回数をＢ列に表示させるとしてＢ10セルには次の式を入力して下方にドラッグコピーします。

=IF(A10="","",COUNTIF(C10:L10,"A-A")+COUNTIF(C10:L10,"B-B"))

これでＡ10セルからＢ18セルには勤務者の組み合わせと同じ組み合わせになった回数が自動的に表示されます。
Ｃ10セルからＬ18セルには作業用のデータが表示されて目障りでしたらその範囲を選択して文字の色を白色にするなどの操作をすればよいでしょう。

お礼

全て自動で表示され並び替えたときの手間がなく大変便利で助かりました。
しかし、うちのPCでは問題なかったのですが会社のPCでは重くなってしまいました。
なにぶん低スペックPCなので…
組み合わせが自動で表示されるのは大変便利なので助かりました。
ありがとうございました。

回答No.3

MarcoRossiItaly

No.1です。え～と、No.2さんのおっしゃるとおりで、No.1の式中の「-(b1:k1="H")*(b5:k5="H")」という部分は、「*(b1:k1<>"H")」と書き換えることができます。このほうが短くて良いですね。したがってNo.1の式は、下のとおり書いても同じ結果となります。SUMPRODUCT関数のほうが普通の発想と思いますので、No.1、3のいずれも、ベストアンサーは辞退します。

=sum(index((b1:k1=b5:k5)*(b1:k1<>"H"),))

回答No.1

MarcoRossiItaly

例えばCとJの組み合わせについて。CのシフトがB1:K1のセル範囲に、JがB5:K5に記録されているとします。C-Jの回数を表示したいセルに次の式を入力します。

=sum(index((b1:k1=b5:k5)-(b1:k1="H")*(b5:k5="H"),))

お礼

ご回答ありがとうございます。
上手く行きましたありがとうございました。