下記のようなデータがあります。
データは1セル毎に「氏名」「所属」「役職」が入力されています。
織田信長調達部1課長
豊臣秀吉販売部2課長
徳川家康企画部係長
宮本武蔵企画部2課長
源頼朝販売部主任
平清盛販売部主任
このデータを基に別シートで、「所属」「役職」を入力すると「氏名」を出力したいと思っております。
例えば、A1セルに「販売部」B1セルに「2課長」と入力するとC1セルに「豊臣秀吉」を出力する という具合です。
OFFSET と MATCH 関数である程度までは上手くいくのですが、同じ「所属」に同じ「役職」があると最初のデータしか反映できません。
作業セルになんらかの数式をいれれば、なんとかなりそうな気がしますが、良い考えが思いつきません。
ご指示の程よろしくお願いします。
A 回答 (7件)
- 最新から表示
- 回答順に表示
No.7
- 回答日時:
No.4です。
> 別シートで、「所属」「役職」を入力すると「氏名」を出力したい
「別シートで」を見落としていました。
一応、訂正しておきますね。
【仮定】
元データが Sheet1のA列からC列にあり、1行目が見出し、データは2行目から。
Sheet2の A1に「所属」、B1に「役職」を入力すると、C1以下に該当する「氏名」をすべて抽出。
作業列は、Sheet1の F列を使用。
Sheet1のF2に
=IF(AND(B2=Sheet2!$A$1,C2=Sheet2!$B$1),ROW(),"")
を入れ、元データの最終行までフィルコピー
今後もデータが増えることを考えて、多めにコピーしておいた方がいいかもしれません。
作業列が邪魔なら非表示にしてください。
Sheet2のC1に
=IF(COUNT(Sheet1!F:F)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!F:F,ROW(A1))))
を入れ、適当に下にフィルコピー
ROW(A1)の A1は、先頭がどのセルであってもそのままにしておいてください。
以上で、質問の要件は満たしていると思います。
「配列」の知識などは特に必要ありません。
以下、蛇足です。
No.4で余計なことを書かなければよかったのでしょうが、No.5さんはどうもわたしのコメントを曲解されているようです。
わたしは配列数式を使うべきではないと言っているのではありません。
配列数式が便利なことは承知しておりますし、実際に多用もしています。
ただ、配列数式が使える場面であっても、作業列を1列使うだけで数式がシンプルになれば、数式のメンテひとつとってもよりベターな場合があります。
状況に応じて使い分けたほうがいいのではないかという個人的な感想を述べたまでです。
会社などでいろいろな人が使うケースなどは特にそのように感じます。
もちろん上記の方法を押しつけるつもりはありませんし、どんな方法をとられるのかは質問者さんが決めればいいことです。
選択肢のひとつとして、こんな方法もあるのかと参考にしていただければ幸いです。
No.6
- 回答日時:
>作業セルになんらかの数式をいれれば・・
そのとおりと思います。
私は同じ型のことを何度も回答してますが。(imogasi方式?)
例でやって見ます。
A1:C8に下記データがあるとします。
A2:B8がデータです。
A1とB1は探す条件の文字列を入れるとします。
C列は下記に説明する関数式の結果です
ax
ax1
sy
ax2
ax3
du
fv
gx
C2に=IF(AND(A2=$A$1,B2=$B$1),MAX($C$1:C1)+1,"")と入れて
C8まで式を複写します。
後は、C列の1,2、3・・の連続数字を、ROW()関数で発生させる連続数と関連付けて
MATCH関数を使って行を割り出し、OFFSETやINDEXで値を
拾います。列数だけ、列を示すオフセット値を増やして拾います。
私の過去の回答例をご覧ください。
No.5
- 回答日時:
#4 さんの
>ご質問のケースで「配列数式」を使うメリットはあまり感じられません。
一応、配列数式を書いた私としては、コメントをいれておきます。
配列数式は、PCの性能やメモリ、またVersionにも依存するようです。以前、調べた結果ですと、内部的な配列の制限が、Excel 2000 ですと、5500個程度です。それ以上のVersion では、この制限はなくなりました。全列・全行を指定しなければ可能なはずです。
また、ワークシートの配列数式の場合は、セルが5500程度ということではなく、累計で参照セルが、5500 どまりですから、縦横のマトリックスで検索する場合は、それは500行や、ひどいときは、100行で一杯になることもあります。
それ以外の場合は、私はひじょうに便利だと思いますね。
ただ、ある程度の大きさの場合は、簡単なイベント型のマクロを使ったフィルタ・オプションを用いれば、良いのかと思います。
No.4
- 回答日時:
こんにちは~
1つの数式でまとめようとすれば 「配列数式」になると思いますが、「配列数式」にすると、
・数式が複雑になる
・データ量が多いと重たくなる
というデメリットがあります。
個人的には、ご質問のケースで「配列数式」を使うメリットはあまり感じられません。
作業列を使った方がシンプルです。
A1に「所属」、B1に「役職」の検索値
「氏名」「所属」「役職」の一覧が、それぞれ F列・G列・H列 にあるとして。
作業列を仮にJ列とします。
一覧の1行目が見出しだとすれば、J2に
=IF(AND(G2=$A$1,H2=$B$1),ROW(),"")
と入れ、一覧表の最終行までフィルコピー
C1に
=IF(COUNT(J:J)<ROW(A1),"",INDEX(F:F,SMALL(J:J,ROW(A1))))
と入れ、適当に下にフィルコピー
ご参考まで。
No.3
- 回答日時:
通常は従業員コード等の固有のキーを検索値にします。
上記の例だと"販売部","主任"が二人ということでこれをキーにしたい場合は
"1主任","2主任"の様に固有になるような設定が必要です。
例 上記の表がSheet1!A2:C7にあるとして
D2=B2&C2&TEXT(SUMPRODUCT((B$2:B2=B2)*(C$2:C2=C2)),"000")
これをD7までコピー
C1=IF(COUNTIF(Sheet1!$D$2:$D$7,$A$1&$B$1&"*")>=ROW(),LOOKUP($A$1&$B$1&TEXT(ROW(),"000"),Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7),"")
これを表示したい人数分下方にコピー
No.2
- 回答日時:
キーというのを聞いたことがありますか?
例えば、
1 りんご 100円
2 みかん 80円
3 バナナ 30円
とあったとすると、1といえば[りんご 100円]、3といえば[バナナ 30円]とわかりますよね。
それが、
1 りんご 100円
1 みかん 80円
1 バナナ 30円
だとして、わたしがあなたに、「1を一つ下さい」と言ったらあなたは何を渡してくれますか?困りますよね?コンピューターも同じで、この場合とりあえず、[りんご 100円]を適用しているだけです。
この1とか2とかがキーと言います。キーはその一つのレコード(りんご 100円などの情報)に一つづつオリジナルなものでないといけません。それを使った検索にしなければならないのもおわかり頂けますよね?
この場合なんですが、コードというものをキーにされれば、うまく検索されると思います。
例えば
>源頼朝 販売部 主任
なら HS01
>平清盛 販売部 主任
ならHS02
>織田信長 調達部 1課長
ならCK11 というようにコードをオリジナルなものにして(単なる数字でもよろしいかと思いますが、入力時に覚えていられないので、意味のあるアルファベットを利用されるほうが良いかと思います)、そのコードを入力すると、所属、役職、氏名が表示されるというような形が一番よろしいかと思います。
No.1
- 回答日時:
こんにちは。
関数で処理にするには、データベース関数か、配列の知識がないと出来ません。
F G
1 販売部 主任
と置いたら、
A1:C7 までの中で、項目行が、1行目にあるとして、実際の氏名、所属、役職の内容は、2行目から始っているとしています。
=IF(SUMPRODUCT(($B$2:$B$7=$F$1)*($C$2:$C$7=$G$1))<ROW(A1),"",INDEX($A$1:$A$7,SMALL(IF(($B$2:$B$7=$F$1)*($C$2:$C$7=$G$1),ROW($A$2:$A$7),""),ROW(A1)),))
配列数式ですから、式を、一旦入力したら、F2を押して、『ShiftとCtrlを押しながらEnterキー』を押して、配列数式として再確定させます。
後は、必要なだけフィルダウン・コピーします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel 売上管理シートに入力した売上データを、日報に自動反映させたいと考えています。 売上管理シ 3 2023/04/29 18:08
- Excel(エクセル) Excelで日報を自動で作成したい 売上管理シートに入力した売上データを、日報に自動反映させたいと考 1 2023/04/29 18:07
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Excel(エクセル) エクセルの関数について 5 2023/01/26 15:26
- Excel(エクセル) エクセル表作成について 5 2023/03/12 13:25
- Excel(エクセル) エクセルで住所と建物名を分けるには? 5 2022/09/08 14:01
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) Excelについて A1からA12まで、1月〜12月と入力し、 B1からB12の範囲に、C1とD1に 4 2022/05/26 22:48
- Visual Basic(VBA) Sheet2からオートフィルターで売上日を抽出した件数をカウントし、その件数をSheet1のセルB1 2 2023/01/12 12:24
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの作業計算方法について
-
Microsoft1Officeの互換ソフト...
-
【マクロ】その時、その時で変...
-
はがきについて。
-
【マクロ】読取専用のファイル...
-
エクセル初心者です 関数の入れ...
-
【関数】適切な文字数の数字を...
-
LOOKUP関数を使えばいいのでし...
-
【関数】先頭だけにある、半角...
-
Excel ピボットテーブルで日付...
-
Excelのpivotについて質問です
-
時間によってファイル名が変わ...
-
エクセル 白黒印刷で白線を印刷...
-
Aというブックの1というシート...
-
エクセル関数を教えてください
-
WPS OFFICEでの縦書きについて
-
Excelのチェックボックスの使い...
-
エクセルの条件付き書式につい...
-
エクセルのセルに同じ大きさの...
-
エクセルの関数について教えて...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報