みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

    日付 1  2  3・・・
従業員名
-----------------------------
青木     A  ~  公
麻井     13 5  非
舟木     13 D  ~
前田     9  9  7
和田     7  7  7
・・
・・
-----------------------------
13     麻井 三原 田中
13     舟木 田中 太田

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

投稿日時 - 2010-02-26 11:52:59

QNo.5707429

困ってます

質問者が選んだベストアンサー

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

一番下の回答(回答番号No1)は最も古い回答で、一番上のこの回答(No5)が最新の回答です。

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

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

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

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

これを表示したくないなら、COUNTIF関数でそのデータの数をカウントし、その数字よりROW(A1)の部分が、大きい範囲は空白を表示する数式にしてください。、

投稿日時 - 2010-03-02 00:31:56

お礼

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

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

投稿日時 - 2010-03-02 22:53:37

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.4

>おっしゃる通り、基本は2人作業です。業務13は2人、業務5も2人、業務7も2人、業務9は2~3人が多いです。

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

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

投稿日時 - 2010-03-01 09:57:19

ANo.3

>基本作業が二人作業ですので7,9,13等は二人表示したいのです。(場合によっては3人)

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

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

>単純に
 =SMALL(IF($G$4:$G$52=$B$62,ROW($G$4:$G$52)-3,""),ROW(A1))
と入れただけでは#VALUE! とエラー表示が出てしまいました。

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

投稿日時 - 2010-02-27 00:14:42

補足

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

おっしゃる通り、基本は2人作業です。業務13は2人、業務5も2人、業務7も2人、業務9は2~3人が多いです。

投稿日時 - 2010-02-27 01:58:13

ANo.2

訂正と補足です。

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

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

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

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

投稿日時 - 2010-02-26 12:21:28

補足

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

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

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

投稿日時 - 2010-02-26 14:20:15

ANo.1

現在の数式をそのまま利用するなら、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で確定して配列数式にしてください。

投稿日時 - 2010-02-26 12:08:40

補足

>「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)
では勤務表に並んだ従業員氏名の何人か割り当てのある勤務13に対し
 勤務表の上(最初?)に表示された従業員が表示されます。
 勤務表は管理職3名に続いて他の従業員が上からアイウエオ順に表示されています。
MATCH~をご指摘の様に改変したパターン
=VLOOKUP(SMALL(IF($G$4:$G$52=$B$62,ROW($G$4:$G$52),""),ROW(A1))
では MATCH~を使用した場合から下に3人目の従業員氏名が
 表示されるようです。

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

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

投稿日時 - 2010-03-01 21:27:04

あなたにオススメの質問