出産前後の痔にはご注意!

お知恵を拝借したいのですが。
1チーム4名で10チームの組合せ表を作りたいのですが、
例)
   1   2     3    4    5
A 1組目 山田太郎 鈴木一夫 佐藤一郎 佐々木清
B 2組目 山下清  山田栄二 斉藤仁司 仲居靖
C 3組目 豊田一郎 小田琢磨 小林琢磨 中村博



J 10組目 安田孝三 千田卓也 木村洋二 田中譲二
セル「K2」にセル「K1」で入力した氏名を入れると該当する組名を返す関数は無いでしょうか?
「K1」山田栄二→「K2」2組目
皆さんアドバイスをお願いします。

A 回答 (3件)

一例です。


行・列番号表記が紛らわしいので2通り回答します。
範囲がA1:E10のケース
=IF(COUNTIF(B1:E10,K1),INDEX(A:A,SUMPRODUCT((B1:E10=K1)*ROW(B1:E10))),"")
範囲がA1:J5のケース
=IF(COUNTIF(A2:J5,K1),INDEX(A1:J1,,SUMPRODUCT((A2:J5=K1)*COLUMN(A2:J5))),"")
    • good
    • 1
この回答へのお礼

ありがとうございます。
まさに、求めていたものです。
皆さん、行列番号が逆になっていて申し訳ありませんでした。あわてて質問したので、思いっきり間違えていました。とても助かりました。
またよろしくお願いします。

お礼日時:2009/02/05 22:47

誰も2組掛け持ちしないと仮定します



これはどうでしょう
=sumproduct(($A$1:$J$5=$K$1)*column($A$1:$J$1))&"組目"

要は此の50個のセルの内1つしかK1と一致しない
つまりイコールを掛けた結果が
1つだけ1で
これ以外全部0になる訳ですが
その1がある列が何処か分かれば良いのですよね

※) エラー処理は省きました
するとしたら
=choose(sign(countif($A$1:$J$5,$K$1)-1)+2,"該当無し",sumproduct(($A$1:$J$5=$K$1)*column($A$1:$J$1))&"組目","重複あり")
極端な話し
全てのセルが一致してもエラーにはならない筈です
    • good
    • 0

表のレイアウトがどちらかわからないので..



●右方向に1組目,2組目...のとき
_____A_____B_____C_____K_
1___1組目___2組目___3組目_田中譲二_
2_山田太郎__山下清_豊田一郎__10組目_
3_鈴木一夫_山田栄二_小田琢磨______
4_佐藤一郎_斉藤仁司_小林琢磨______
5_佐々木清__仲居靖__中村博______

K2
=IF(COUNTIF($A$2:$J$5,K1)=1,SUMPRODUCT(COLUMN($A:$J)*($A$2:$J$5=K1))&"組目",COUNTIF($A$2:$J$5,K1)&"!")

●下方向に1組目,2組目...のとき
______A_____B_____C_____D_____E_
_1___1組目_山田太郎_鈴木一夫_佐藤一郎_佐々木清_
_2___2組目__山下清_山田栄二_斉藤仁司__仲居靖_
_3___3組目_豊田一郎_小田琢磨_小林琢磨__中村博_
11_山田太郎___1組目________________

B11
=IF(COUNTIF($B$1:$E$10,A11)=1,SUMPRODUCT(ROW($1:$10)*($B$1:$E$10=A11))&"組目",COUNTIF($B$1:$E$10,A11)&"!")

なければ0!、重複していれば2!を返します。
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QExcel MATCH関数で検索範囲内に同じ値の検索値が複数ある場合

MATCH関数で、検索した値が複数ある場合に1つしか出なくて困っています。
(例)
   A B C D
1 年月日 種類 番号 備考
2 4月1日 肉類 1
3 4月2日 野菜 2
4 4月3日 肉類 1
5 4月4日 果物 2
6 4月5日 野菜 1
7 4月6日 果物 3
8 4月7日 果物 2
9 4月8日 肉類 1

この表で、D9の備考の欄に「種類:肉類、番号:1」と同じものがあった時の日付を、新しい日付で取り出したいのです。

INDEX(A2:A8,(MATCH(B9,B2:B8,0)+MATCH(C9,C2:C8,0))/2)
としたのですが、本当は4月3日を取り出したいのですが、4月1日が出てきます。
どのようにしたら良いでしょうか?
Excel2003です。
よろしくお願いします。

Aベストアンサー

MATCH関数を使用しませんが、D9の書式を日付にして、
=MAX((A2:A8)*(C2:C8=1)*(B2:B8="肉類"))
と、入力してShift+Ctrl+Enterキーで入力完了してみてください(配列式になります)。

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

QExcelですが、同一データが複数あるとき、検索して、その全部を抽出する方法

Excelですが、検索キーに、同一データ(レコード)が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行(レコード)を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお願いします。

<データシート>
 列A  列B    列C 列C
行1 No 会社名   所属 担当者
行2 1 A株式会社  ○事業部  坂下順人
行3 2 A株式会社  △事業部  滿山友人
行4 3 A株式会社  △事業部  目標達子
行5 4 B株式会社  設計部山下清人
行6 5 B株式会社  営業部横浜美人
行7 6 C株式会社  営業部川崎次郎
行8・・・・・・・・・・・・

<検索結果:別のシートにおいて>
行1 会社名 所属  担当者
行2 A株式会社 ○事業部 坂下順人
行3 A株式会社 △事業部 滿山友人
行4 A株式会社 △事業部 目標達子

Excelですが、検索キーに、同一データ(レコード)が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行(レコード)を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお...続きを読む

Aベストアンサー

#03です
>最後のROW(T1)で、T1を参照しておりますが

ROW(T1)は「1番目」を意味しています。T1セルに何もなくても関係ありません。コピーしたの行はROW(T2)になりますが、これは2番目のという意味です

#REF!エラーになりますか?
私が示した条件と何か変えていませんか?
別シートのA1に「抽出する会社名」を入力してあれば、回答したような結果になるはずですが‥ (テストしてありますので)

ただし2000行もあるならVBAの方が良いかもしれません
きっと「シートが重くなる」と思います

QVLOOKUP関数の範囲に、複数の範囲を指定したい

VLOOKUP関数の範囲に、複数の範囲を指定したいのですが方法がわかりません。
例えば同じシート内のC3:L8の範囲とM3:V8の範囲とW13:AF18の範囲を同時に
検索範囲として指定したいと考えております。

選択範囲に名前をつけて定義し、それを範囲として指定してみても、
なかなか上手くいきません。

効果的な方法があれば、御教鞭頂きたく思います。

Aベストアンサー

ごめんなさい。手抜きですね。
以下が条件です。

検索値がA1にある
検索範囲の中で検索値に合致する値が複数ない

=IF(ISERROR(VLOOKUP(A1,C3:L8,2,0)),IF(ISERROR(VLOOKUP(A1,M3:V8,2,0)),IF(ISERROR(VLOOKUP(A1,W13:AF18,2,0)),"",VLOOKUP(A1,W13:AF18,2,0)),VLOOKUP(A1,M3:V8,2,0)),VLOOKUP(A1,C3:L8,2,0))

最初の範囲で検索値に一致する物がなければ、次の範囲を探します。
その繰り返しです。
最終的にどこにもなければ空白です。

返す値の列番号は範囲の中で左から2番目です。
たとえば最初の範囲なら、D列の値が帰ります。

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

Q【Excel】 INDEX ,MATCH でいいのか。該当が複数ある場合

項目の検索で行き詰まっています。

A1 から A30 に名前
B1 から B30 に得点
が入力されています。
B31 に最高点を表示するのは、MAX 関数でできました。

B32 にその最高点をとった人物が誰かを表示するのに
=INDEX($A$1:$A$30,MATCH($B$31,$B$1:$B$30),1)
と入力したら、
該当者が1人しかいない時は問題ないのですが、複数いる場合でも1人しか表示されません。
該当が複数ある場合の出し方は他に方法があるのでしょうか?

どなたか力を貸してください。
よろしくお願いします。

Aベストアンサー

誰が最高点なのか確かめるだけであれば、
#1さんのご提案されたオートフィルタが恐らく最短ですね。

ただ、ご質問の例をみますと、
やはり特定のセルに値を返す必要があるのではないかとも思われます。
その場合、#2さんがご提案されたように作業列を使って引くのが順当な手順かと思います。

さて。
たまたまつい先日似た質問にお答えしたので、
正直あまりお奨めはしないのですが、「一発関数回答」を。

B32セル:
 =IF(ROW()-ROW(B$31)>COUNTIF($B$1:$B$30,B$31),"",INDEX($A$1:$A$30,MATCH(LARGE(($B$1:$B$30=B$31)/ROW($B$1:$B$30),ROW()-ROW(B$31)),1/ROW($B$1:$B$30),0)))

を配列数式として入力し下方に十分な数だけフィル。(Excel2003で動作確認済)
※通常の数式は、数式を入力した後Enterキーで確定しますが、
 これは配列数式なので、CtrlとShiftを押しながらEnterで確定してください。

数式中に、B$31,$A$1:$A$30,$B$1:$B$30の3種類の参照が複数回登場しますが、
いずれも質問文の数式と同様のニュアンスで考えていただいて結構です。
位置が変わる場合はそれぞれ置き換えてください。
(ただし、最高点のセルの直下に、縦に並べて名前を表示することを想定しています)

数式の意味内容については、やや手前味噌の感もありますが、
先日した回答のURLを挙げておきますのでご参考ください。
http://oshiete1.goo.ne.jp/qa3328660.html
(このケースでは行列(縦横)が逆になっています)

誰が最高点なのか確かめるだけであれば、
#1さんのご提案されたオートフィルタが恐らく最短ですね。

ただ、ご質問の例をみますと、
やはり特定のセルに値を返す必要があるのではないかとも思われます。
その場合、#2さんがご提案されたように作業列を使って引くのが順当な手順かと思います。

さて。
たまたまつい先日似た質問にお答えしたので、
正直あまりお奨めはしないのですが、「一発関数回答」を。

B32セル:
 =IF(ROW()-ROW(B$31)>COUNTIF($B$1:$B$30,B$31),"",INDEX($A$1:$A$30,MATCH(LA...続きを読む

QEXCEL-複数列から特定内容の行のみ抽出

EXCEL2010です。

添付画像のように、「好物1~3」に入力されている内容で特定の文字(メロン)を含む行のみを抽出するにはどのようにしたらよいでしょうか。

Aベストアンサー

貼付画像はExcel 2013で検証してた結果です。
Excel 2010でも同様な結果を得られると思います。

チョット面倒臭い数式になりますが次のようにされると良いでしょう。
A13=IFERROR(IF(INDEX($A$1:$E$6,SUMPRODUCT(SMALL(($C$2:$E$6="メロン")*ROW(A$2:A$6),COUNTIF($C$2:$E$6,"<>メロン")+ROW(A1))),COLUMN())<>"",INDEX($A$1:$E$6,SUMPRODUCT(SMALL(($C$2:$E$6="メロン")*ROW(A$2:A$6),COUNTIF($C$2:$E$6,"<>メロン")+ROW(A1))),COLUMN()),""),"")
A13の数式を右と下へオートフィルでコピーしました。

上の表のデータが空欄のとき下の表に0を表示しても良ければIF関数を除けますので簡略化できます。
A13=IFERROR(INDEX($A$1:$E$6,SUMPRODUCT(SMALL(($C$2:$E$6="メロン")*ROW(A$2:A$6),COUNTIF($C$2:$E$6,"<>メロン")+ROW(A1))),COLUMN()),"")

貼付画像はExcel 2013で検証してた結果です。
Excel 2010でも同様な結果を得られると思います。

チョット面倒臭い数式になりますが次のようにされると良いでしょう。
A13=IFERROR(IF(INDEX($A$1:$E$6,SUMPRODUCT(SMALL(($C$2:$E$6="メロン")*ROW(A$2:A$6),COUNTIF($C$2:$E$6,"<>メロン")+ROW(A1))),COLUMN())<>"",INDEX($A$1:$E$6,SUMPRODUCT(SMALL(($C$2:$E$6="メロン")*ROW(A$2:A$6),COUNTIF($C$2:$E$6,"<>メロン")+ROW(A1))),COLUMN()),""),"")
A13の数式を右と下へオートフィルでコピーしました。

上の表の...続きを読む

Qエクセルの複数条件に一致したセルを抽出したいのですが、関数で教えてくだ

エクセルの複数条件に一致したセルを抽出したいのですが、関数で教えてください。

               A列   B列    C列
Aファイルには 行 【注文月日】、【氏名】、【商品金額】が記入されています。
Bファイルには 行 【注文月日】、【氏名】、【税金額】が記入されています。

AファイルのD列に、Aファイルの【注文月日】かつ【氏名】がBファイルの【注文月日】かつ【氏名】と一致する場合のBファイルC列の【税金額】を表示したいのです。

VLOOKUP関数では条件が1つだけなので使用できませんが、似たような関数で複数条件ができる関数があるなら教えてください。

Aベストアンサー

こんばんは!
一例です。
↓の画像で右側(Bファイル)の方に作業用の列を設けています。
作業列D2セルを
=A2&B2
としてオートフィルで下へずぃ~~~!っとコピーします。

そして、AファイルのD2セルに
=IF(OR(A2="",COUNTIF([Bファイル.xls]Sheet1!D$2:D$1000,A2&B2)=0),"",INDEX([Bファイル.xls]Sheet1!C$2:C$1000,MATCH(A2&B2,[Bファイル.xls]Sheet1!D$2:D$1000,0)))
という数式を入れ、オートフィルで下へコピーすると
画像のような感じになります。
尚、数式はBファイルの1000行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m

Qエクセル 複数列を検索し左3列目を返す

よろしくお願いします。

シート1の5列(E,J,O,T,Y)の中から,該当するセルを検索,該当セルの左隣3列目のセルを返す関数を考えています。検索条件の重複はありません。

検索結果を表示するシートをシート1とは別に作成します。
A列に検索条件を入力し,B列に結果を返したいです。

例シート1
  A  B   C   D   E    F  G   H    I     J   ・・・・・
  ID 品名 項目 単価 1年目 ID2 2品名 2項目 3単価 2年目 ・・・・・
1 1  a   b   c    1-1   e    f    g   h   1-11
2 2  i    j   k     2-2  m    n    o   p   2-22

 検索結果
  A   B
1 1-1  a
2 2-22  n

このような感じのファイルになっています。IFやLOOKUPなどを試しましたが,複数列をうまく検索する関数を作成することができませんでした。
また,検索対象となるシートが複数になる可能性もあり,その際にも対応できるとありがたいです。

よろしくお願いいたします。

よろしくお願いします。

シート1の5列(E,J,O,T,Y)の中から,該当するセルを検索,該当セルの左隣3列目のセルを返す関数を考えています。検索条件の重複はありません。

検索結果を表示するシートをシート1とは別に作成します。
A列に検索条件を入力し,B列に結果を返したいです。

例シート1
  A  B   C   D   E    F  G   H    I     J   ・・・・・
  ID 品名 項目 単価 1年目 ID2 2品名 2項目 3単価 2年目 ・・・・・
1 1  a   b   c    ...続きを読む

Aベストアンサー

>日付

それならそうと最初から情報提供を。

数値を参照してきたいなら
=IFERROR(INDEX(Sheet1!B:B,MATCH(A1,Sheet1!E:E,0)),0)
+IFERROR(INDEX(Sheet1!G:G,MATCH(A1,Sheet1!J:J,0)),0)
+ …以下同文

セルの書式はもちろん日付等適切に。


人気Q&Aランキング