同じ行または列で同一文字列を検索・順位をつけて表示するには？

質問

同じ行または列で同一文字列を検索・順位をつけて表示するには？
３交代制の職場でExcel2003で勤務表を作成していますが、基本的には２人一組で作業を行っています。そこで同一作業の担当者を別欄に抜き出して勤務表を見やすくしたいのですが、作成した関数では最初の一人しか認識せず、困っています。お力添えください。イメージは次の通りです。

　　　　日付　１　　２　　３・・・
従業員名
-----------------------------
青木　　　　　Ａ　　～　　公
麻井　　　　　１３　５　　非
舟木　　　　　１３　Ｄ　　～
前田　　　　　９　　９　　７
和田　　　　　７　　７　　７
・・
・・
-----------------------------
１３　　　　　麻井　三原　田中
１３　　　　　舟木　田中　太田

としたいのですが、下記関数では　一人目しか表示してくれません。
すなわち　「麻井」欄に下記関数を入れるとうまく表示しますが、
下の「１３」の欄の「舟木」部分に同様に下記関数をいれても「麻井」しか表示しません。
=VLOOKUP(MATCH(B62,$G$4:$G$52,0),$A$2:$C$52,3)
ここで、B62は別欄に設けた目的とする「１３」（作業の内容）従ってＡ，９等も入ります。
$G$4:$G$52は当日の作業内容（上の例ではＡ，Ｄ，７，９，１３，公、非　　・・等）
$A$2:$C$52は1～52までの連番・従業員（フルネーム）・名字（を抜き出したもの）
の順となっています。
マクロかな、とも思いますが、作成方法がわからず、関数で乗り切れるなら乗り切りたいです。また既出かな、とも思いますが少し調べた状況ではわかりませんでした。
質問のタイトルもこのままでよいのか自信がありません。よろしくお願い致します。

回答No.5

MackyNo1 · ベストアンサー

この掲示板のシステム（レイアウト）を勘違いされているようです。

一番下の回答（回答番号Ｎｏ１）は最も古い回答で、一番上のこの回答（Ｎｏ５）が最新の回答です。

>MATCH～をご指摘の様に改変したパターン
=VLOOKUP(SMALL(IF($G$4:$G$52=$B$62,ROW($G$4:$G$52),""),ROW(A1))
では　MATCH～を使用した場合から下に３人目の従業員氏名が
　表示されるようです。

したがって、No1に対する返答ですが、Ｎｏ２を参考にしてください。

もちろん、Ｇ列以外のデータを参照できるようにするには、$G$4:$G$52は複合参照（G$4:G$52）の数式にして下さい。

この数式で同じ値が3つの場合もオートフィルだけで表示できますが、2つしかないと3つ目のセルはエラーとなります。

これを表示したくないなら、ＣＯＵＮＴＩＦ関数でそのデータの数をカウントし、その数字よりROW（Ａ1）の部分が、大きい範囲は空白を表示する数式にしてください。、

お礼

明日、宿直勤務なので教えて頂いた内容を十分時間をかけて確認したいとおもいます。
なんとなく私の希望する答え近づいているイメージがしてました。

そうそう、コメントを書く欄が間違っていたみたいですみません。

回答No.4

MackyNo1

>おっしゃる通り、基本は２人作業です。業務１３は２人、業務５も２人、業務７も２人、業務９は２～３人が多いです。

私の質問に回答してないように思いますが、配列数式にして2名分表示できたのでしょうか？

ひとまず提示した数式は、共同作業の人数によって自動的に人数分だけ表示できるようになっていません(エラー処理をしていません）が、複雑な数式にすれば、自動的に該当者がない場合は空白表示などにすることもできます。

回答No.3

MackyNo1

>基本作業が二人作業ですので７，９，１３等は二人表示したいのです。（場合によっては３人）

例示のデータでは、９の場合は1人に見えますが、2人なのでしょうか？

また、3人の場合は3名分表示させるのでしょうか？

＞単純に
　=SMALL(IF($G$4:$G$52=$B$62,ROW($G$4:$G$52)-3,""),ROW(A1))
と入れただけでは＃ＶＡＬＵＥ！　とエラー表示が出てしまいました。

すでに回答したように、配列数式ですので、数式を入力後、CtrlキーとShiftキーを押しながらEnterキーを押してください（数式が｛と｝で挟まれます）。

補足

>例示のデータでは、９の場合は1人に見えますが、2人なのでしょうか？
>また、3人の場合は3名分表示させるのでしょうか？

おっしゃる通り、基本は２人作業です。業務１３は２人、業務５も２人、業務７も２人、業務９は２～３人が多いです。

回答No.2

MackyNo1

訂正と補足です。

検索範囲がG4セルから開始しているのですから、このセルが「１」番目になるように、提示した数式を以下のように変更してください。

SMALL(IF($G$4:$G$52=$B$62,ROW($G$4:$G$52)-3,""),ROW(A1))

＃ところで、浅井、舟木の右の名前はどのように表示した（表示したい）データなのでしょうか？
上記の回答は、例示された数式を修正する場合の回答ですが、条件によってはもっと効率的な操作があるかもしれません。

もし、上記の回答でうまくいかないようであれば、元シートのレイアウトと、まとめたい一覧表のレイアウトをもう少し具体的に例示してください。

補足

＞ところで、浅井、舟木の右の名前はどのように表示した（表示したい）データなのでしょうか？
どのように質問したらよいのかわからず、あのような質問となりました。

Ｅｘｃｅｌは左端 A4 からA4従業員名の連番・B4従業員名（フルネーム）・C4従業員（名字だけ）・D4(１日)～AH(３１日)の業務内容となっています。縦方向は従業員名です。
申し遅れましたが、$G$4:$G$52　は　４日目の業務の実データです（Ａ，Ｄ，７，１３等）。
=VLOOKUP(MATCH(B63,$E$4:$E$52,0),$A$2:$C$52,3)
というのは、B63（このセルにはに業務内容を指定します）　の業務の内容を、当日の従業員の業務割り当てから　誰が該当するか　を知りたいのです。基本作業が二人作業ですので７，９，１３等は二人表示したいのです。（場合によっては３人）

単純に
　=SMALL(IF($G$4:$G$52=$B$62,ROW($G$4:$G$52)-3,""),ROW(A1))
と入れただけでは＃ＶＡＬＵＥ！　とエラー表示が出てしまいました。

回答No.1

MackyNo1

現在の数式をそのまま利用するなら、VLOOKUP数式の「MATCH(B62,$G$4:$G$52,0)」の部分を「SMALL(IF($G$4:$G$52=$B$62,ROW($G$4:$G$52),""),ROW(A1))」として、数式入力後、Ctrl+Shift+Enterで確定して配列数式にしてください。

補足

>「MATCH(B62,$G$4:$G$52,0)」の部分を
>「SMALL(IF($G$4:$G$52=$B$62,ROW($G$4:$G$52),""),ROW(A1))」として、数式入力後、 >Ctrl+Shift+Enterで確定して配列数式にしてください。

長い間返事が出来ずに申し訳ありませんでした。
まず配列数式という言葉を知らず（初歩的なマニュアル本には載っていない）
勤務の関係や講習会参加でで解答はチェックできたものの返事が出来ない
状態が続いていました。

今少し触っている状態では
=VLOOKUP(MATCH(B63,$G$4:$G$52,0),$A$2:$C$52,3)
では勤務表に並んだ従業員氏名の何人か割り当てのある勤務１３に対し
　勤務表の上（最初？）に表示された従業員が表示されます。
　勤務表は管理職３名に続いて他の従業員が上からアイウエオ順に表示されています。
MATCH～をご指摘の様に改変したパターン
=VLOOKUP(SMALL(IF($G$4:$G$52=$B$62,ROW($G$4:$G$52),""),ROW(A1))
では　MATCH～を使用した場合から下に３人目の従業員氏名が
　表示されるようです。

残念な事に配列数式が理解できない上に（nikkeiBPで調べて少しわかった）
SMALL(IF～　　・・・ROW(A1))
の意味が理解できず悪戦苦闘中です。

もう少し頑張ります。
また少しわかったら報告します。