エクセル２つのキー一致の複数同一データへ順位付け

質問

仕事で困っており、皆さんのお力をお借りできますでしょうか。
下記に記載致しますのでよろしくお願い致します。

エクセルＶｅｒ．2003　です。
弊社事情でオートフィルターとマクロを使用せず、関数のみ使用を希望しております。
又、データには個人を区別できる固有の項目を使用せず、あくまで課と係の完全一致に
下記内容で順位付けしたいというものです。
リストに複数の課と係があり、課と係の２つをキーとし、完全一致したものに
順位付け（１ ２ ３ ４）を必要な人数分付けたいというものです。
実際には各３課と各３係の９部署あり、各課は最低１０人～２０人おります。
合計９部署の各部署毎に各別列で１、２、３、４と順位付け表示、
ですから各列の中に一致しない箇所が必ずあり、そこは空白になっております。
下記に仮想データを用意しました。
※確認ボタンを押すと空白表示がずれて表示が正確でないかもしれませんが
イメージとして捉えて頂ければ幸いでございます。

A列 B列 C列 　D列 　　　　E列　　　　F列　　　　G列　　　　H列 　　　　I列
No. 課 係 東京１係 東京２係 福岡１係 福岡２係 大阪１係 大阪２係
1 大阪 2 　　　　　　　　　　　　　　　　　　　　　1
2 福岡 2 　　　　　　　　　　　　　　1
3 東京 2 　　　　　　　1
4 福岡 1 　　　　　　　　　　　1
5 大阪 1 　　　　　　　　　　　　　　　　　　1
6 東京 2 　　　　　　　2
7 東京 1 　　　1
8 大阪 2 　　　　　　　　　　　　　　　　　　　　　2
9 大阪 2 　　　　　　　　　　　　　　　　　　　　　3
10 福岡 2 　　　　　　　　　　　　　　2
11 東京 1 　　　2
12 福岡 2 　　　　　　　　　　　　　　3
13 福岡 1 　　　　　　　　　　　2
14 東京 2 　　　　　　　3
15 東京 2 　　　　　　　4

回答No.4

keithin · ベストアンサー

＞記載の通り入力したのですが、結果が全てブランクになってしまいます

通常はデータが間違っているのが原因です。設定や数式の問題じゃありません。

たとえば１行目の「東京１係」の１の数字が全角数字の１になっているのでしょう。この部分を半角数字の1に書き換えます。
あるいはＣ列の書式設定を文字列に変えてから、改めてＣ列に全角文字の数字で１２３を書き入れます。

それとも、どこかにゴミデータが紛れているのが原因かもしれません。たとえば「東京」のつもりが実は「東京□」や「□東京１係」（□はスペース）といった具合です。

何らかの特別な理由で今のデータを修正できない（したくない、たとえば全角の１２３と半角数字の123をナニが何でも使いたい）なら、「実際のデータ」に合うように、数式にもうちょっと手を加える対処も可能です。

いずれにしてもあなたのエクセルのホントの姿がこちらでは目に見えない以上、ご自身で間違い探しをしていただくしかありませんよ。

回答No.5

bunjii

>仕事で困っており、皆さんのお力をお借りできますでしょうか。
仕事の手助けを無償ではしたくありません。
他人の褌で相撲を取って賞金は自分のものと言う甘えの構造です。
仕事で必要な出費は損失に計上しますのであなた自身の負担になりません。
とは言うものの頭の体操のつもりで下記の数式を提案します。

>弊社事情でオートフィルターとマクロを使用せず、関数のみ使用を希望しております。
当方ではExcel 2013で検証しましたがExcel 2003でも再現できると思います。
B2セルに次の数式を入力して右と下へ必要数コピーすれば良いでしょう。
=IF(OR(COUNTIF($B$2:$B2,LEFT(C$1,LEN($B2)))=0,COUNTIF($B$2:$B2,LEFT(C$1,LEN($B2)))=MAX(C$1:C1)),"",COUNTIF($B$2:$B2,LEFT(C$1,LEN($B2))))

数式の論理を理解できなければ他の仕事に応用できませんので数式の解読で知識を高めてください。

回答No.3

tom04

No.1です。

投稿後思ったのですが、前回の画像の配置通りで良いのであれば
D2セルの数式は
=IF($B2&$C2&"係"=D$1,MAX(D$1:D1)+1,"")
だけで大丈夫です。

ただ
＞結果が全てブランクになってしまいます。

考えられる原因としては前回の最後に記載したように
B・C列データと1行目項目データが一致していない！と思われます。
1行目項目は
B・C列のデータ＋「係」という文字列になっているか
今一度確認してみてください。
（途中にスペースなどがあってもダメです）

※　数字の全角・半角の違いでも別データというコトになります。m(_ _)m

回答No.2

keithin

Ｄ２に
=IF($B2&$C2&"係"=D$1,COUNT($D1:D1)+1,"")
右に下にコピー
ぐらいで。

補足

ご回答ありがとうございます。
記載の通り入力したのですが、結果が全てブランクになってしまいます。
設定か何かの関係で結果が皆さんと相違してしまうのでしょうか？

回答No.1

tom04

こんばんは！

「順位付け」の意味が良く分からないのですが、
イメージの配置を拝見すると
↓の画像のような感じで良いのでしょうか？

そうであれば、画像ではD2セルに
=IF(COUNTBLANK($B2:$C2),"",IF($B2&$C2&"係"=D$1,MAX(D$1:D1)+1,""))
という数式を入れ、列・行方向にフィルハンドルでコピーしています。

※　質問文では数値が半角と全角混在していますが、
C列のデータと1行目項目の数値の「半角・全角」はどちらかに統一してください。
そうでないと別データとなりすべて空白になってしまいます。

※　的外れならごめんなさいね。m(_ _)m

お礼

ご回答ありがとうございます。私のイメージも添付頂いた画像の通りで間違いないのですが
記載の通り入力したのですが、結果が全てブランクになってしまいます。
設定か何かの関係で結果が皆さんと相違してしまうのでしょうか？