エクセル関数について教えてください

質問

sheet1にはA列にコード番号が入力されており、B列にグループごとの通し番号、C列にグループ名が表示されています。
そこからsheet2にC列グループ名とB列通し番号から検索し、A列コード番号が抽出されるようにしたいのですが、やり方が解りません。
どなたかご教授お願いします。

Sheet1　　　　　　　　　　　　　　　　　　　Sheet2
　　　A　　　B　　 　 C　　　　　　　　　　　　　　　　　　A　　　　　　B
1　 1898　　1　　東京　　　　　　　　　　　1　　グループ名　グループ名
2　 1854　　2　　東京　　　　　　　　　　　2　
3　 1876　　3　　東京　　　　　　　　　　　3 　　A1の通番1　　B1の通番1
4　22354　　1　　青森　　　　　　　　　　 4　　 A1の通番2　　B1の通番2
5　22398　　2　　青森　　　　　　　　　　　5　　A1の通番3　　B1の通番3
6　22376　　3　　青森　　　　　　　　　　　6　　A1の通番4　　B1の通番4
7　 3898　　4　　東京　　　　　　　　　　　7　　A1の通番5　　B1の通番5
8　16622　　2　　群馬　　　　　　 　　　　 8　　A1の通番6　　B1の通番6
9　12242　　1　　大阪　　　　　　　　　　　9　　A1の通番7　　B1の通番7

実際にはもっと複雑なのですが、これだけ解れば応用で何とか変更は出来ると思うので、お願いします。
ただ、出来れば作業用のセルは使用しない方向でお願いします。
他のデータに影響が出てきますので・・・

回答No.2

kagakusuki · ベストアンサー

>ただ、出来れば作業用のセルは使用しない方向でお願いします。
>他のデータに影響が出てきますので・・・

　何故その様に思われておられるのでしょうか？
　別に作業列を使用した処で、他のデータに影響する事は無いと思います。

　それと、確認しておきたいのですが、Sheet1のB列の通し番号は、同じグループ内であれば、大きな番号が小さな番号よりも上の行に来る事はあり得ない（例えば東京の3番が、大阪の1番よりも上の行に存在する事はあっても、東京の2番よりも上の行に存在する事は無い）と考えても宜しいのでしょうか？
　もし、この条件が守られているのでしたら、Sheet2のA3セルに、次の関数を入力してから、Sheet2のA3セルをコピーして、Sheet2においてコード番号を表示させる全てのセル（添付画像ではA列～D列の3行目以下）に貼り付けると良いと思います。

=IF(OR(INDEX($1:$1,COLUMN())="",ROWS($3:3)>COUNTIF(Sheet1!$C:$C,"="&INDEX($1:$1,COLUMN()))),"",INDEX(Sheet1!$A:$A,SUMPRODUCT((COUNTIF(OFFSET(Sheet1!$C$1,,,ROW(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH("゛",Sheet1!$C:$C,-1)))-ROW(Sheet1!$C$1)+1),"="&INDEX($1:$1,COLUMN()))<ROWS($3:3))*1)+1))

　後は、Sheet2の1行目に、各グループ名を入力しますと、各々のグループ毎に抽出されたコード番号が、Sheet1において上にあるものからから順番に、自動的に表示されます。

お礼

ありがとうございました。

ただ、上記数式で例のデータではうまくいったのですが、実際のデータではこちらの応用力が足りなかったようでうまくいきませんでした。
うまくいかなかったとしてもこれ以上この質問で引っ張るのもおかしいので、再度新たに書き直して実際のデータ表を利用して質問させていただきます。
しかし今回の質問内容としては最初に答えていただいたこちらをベストアンサーとさせていただきます。
ありがとうございました。

補足

>ただ、出来れば作業用のセルは使用しない方向でお願いします。
>他のデータに影響が出てきますので・・・

今のブック自体が至るところに作業列をしようしており、何が何か分からない状態となっており、その見直しを図っている状態です。
なので、消してしまう作業列も存在しており、今新たに作業列を増やしてしますとエラーが出た際にどこの作業列に問題があるのか分からなくなる恐れがあるためです。
実際今の作業列一行消すだけでも、至るところでエラーが発生しており、何の作業列かも分からない状態です。

回答No.5

tom04

こんばんは！
お望みの関数ではなく、VBAでの一例です。

Sheet1に元データがありSheet2に表示させるとします。
画面左下のSheet1のSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j, k As Long
Dim ws As Worksheet
Set ws = Worksheets(2)
Application.ScreenUpdating = False
ws.Cells.Clear
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(ws.Columns(1), Cells(i, 2)) = 0 Then
ws.Cells(Rows.Count, 1).End(xlUp).Offset(1) = Cells(i, 2)
End If
Next i
ws.Columns(1).Sort Key1:=ws.Cells(2, 1), Order1:=xlAscending
For k = ws.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If ws.Cells(k, 1) - ws.Cells(k - 1, 1) <> 1 Then
ws.Rows(k).Insert
ws.Cells(k, 1) = ws.Cells(k - 1, 1) + 1
End If
Next k
ws.Cells(1, 1).Insert (xlDown)
ws.Cells(1, 1) = "通番"
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(ws.Rows(1), Cells(i, 3)) = 0 Then
ws.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) = Cells(i, 3)
End If
Next i
For j = 1 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
For k = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 3) = ws.Cells(1, j) And Cells(i, 2) = ws.Cells(k, 1) Then
ws.Cells(k, j) = Cells(i, 1)
End If
Next i
Next k
Next j
Application.ScreenUpdating = True
End Sub 'この行まで

※　Sheet1の「通番」が昇順でなくても、対応できるようにしてみました。

最初に書いたように関数ではないので、ご希望の方法でなければ無視してくださいね。m(_ _)m

お礼

ありがとうございます。

しかし今回は関数を希望でしたので、VBAではこちらの意図と違います。
ダメというよりは、出来上がった際にこのブック使用者に意味をこちらが全く説明できないし、不具合又は改良を加える際に手の打ちようがないので申し訳ありません。

回答No.4

keithin

シート２のＡ３：
=IF(ROW(A1)>COUNTIF(Sheet1!$C:$C,A$1),"",SUMPRODUCT((Sheet1!$C$1:$C$999=A$1)*(Sheet1!$B$1:$B$999=ROW(A1)),Sheet1!$A$1:$A$999))
をふつーに入力，下にコピー，右にコピー

＃別の解法，簡略化バージョン
シート２のＡ３：
=INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$C$1:$C$999=A$1,ROW(Sheet1!$C$1:$C$999),9999),ROW(A1)))&""
をコントロールキーをシフトキーを押しながらEnterで入力，下にコピー，右にコピー。

ご利用のエクセルのバージョンが不明ですが，Excel2007以降ならもっと簡略化できます
実際のデータの配置がご質問に例示されたみたいに「１行目からびっちり並んでる」みたいな作り方をしていなければ，応用でもう少し簡略化できる可能性があります。

お礼

ありがとうございました。

ただ、上記数式で例のデータではうまくいったのですが、実際のデータではこちらの応用力が足りなかったようでうまくいきませんでした。
(数式の意味は分かるのですが、なぜか空白が解答されてしまいます、なぜだろう…)

うまくいかなかったとしてもこれ以上この質問で引っ張るのもおかしいので、再度新たに書き直して実際のデータ表を利用して質問させていただきます。
その時はご協力お願いいたします。

回答No.3

KURUMITO

データの数が多くなったりしますと計算に負担がかかります。作業列を作って対応することです。
例えばJ列までが使われている表としたらK,L列などを作業列として使用すれば他の計算などに影響を及ぼすこともありませんし、それらの列が目障りでしたらそれらの列を非表示にすればよいでしょう。
例えばシート1では1行目が項目名で2行目からデータが入力されているとしてK2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(C$2:C2,C2)=1,ROW(A1),"")

L2セルには次の式を入力して下方にオートフィルドラッグします。

=C2&B2

そこでお求めの表ですがシート2のA1セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(COLUMN(A1)>COUNT(Sheet1!$K:$K),"",INDEX(Sheet1!$C:$C,MATCH(SMALL(Sheet1!$K:$K,COLUMN(A1)),Sheet1!$K:$K,0)))

県名が表示されます。
次にA2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ISERROR(INDEX(Sheet1!$A:$A,MATCH(A$1&ROW(A1),Sheet1!$L:$L,0))),"",INDEX(Sheet1!$A:$A,MATCH(A$1&ROW(A1),Sheet1!$L:$L,0)))

エクセルの計算ではカッコ良さを追求するよりも分かり易く簡単な方法で問題を解決するようにすることが大切のように思います。

お礼

ありがとうございました。

ただ、今回は作業列を使わない方法を希望でしたので、こちらの意図と違います。

回答No.1

7772

もう少し詳細お願いします。

>そこからsheet2にC列グループ名とB列通し番号から検索し、A列コード番号が抽出されるようにしたいのですが、やり方が解りません。

の意味がよくわかりません。

次の質問内容でいいですか？
”Sheet1に3つの情報「コード」「グループ」「グループ毎の通し番号」がある。
Sheet2では、Sheet1のデータを参照し、「グループ」「グループ毎の通し番号」が一致するコードを返す。
たとえば、Sheet2のA列にグループ名を入力する、
次に、Sheet2のB列にグループ毎の通し番号を入力する、
この二つのデータを持つコードをC列に入力する”
このやり方が知りたい。

補足

残念ながらそれではこちらの意図と違います。

実際には上記の例の形ではないのですが、例でいえばSheet2の1行目には、Sheet1のグループ名を調べ、任意に順位を付けたグループが有れば上から順に関数を使い表示されるようにしています。
例えばこの表では、
1東京　2千葉　3北海道　4青森　5大阪　6奈良　7群馬
だとして、Sheet2のA1には東京・B1には青森・C1には大阪・D1には群馬が表示されるようになってます。
千葉・北海道・奈良は無いので表示されません。
そして、3行目以降に1行目に表示されたグループ名の1番から順に通し番号でコード番号が表示されるようにしたいんです。

つまりSheet2に欲しいのはA3：B9で、Sheet2のA1(B1）で表示されたグループをSheet1のC列から検索しかつ通し番号順にコードだけを返せる数式を求めています。

説明が下手ですが、どうかよろしくお願いします。