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.5
- 回答日時:
こんばんは!
お望みの関数ではなく、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
- 回答日時:
シート2のA3:
=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))
をふつーに入力,下にコピー,右にコピー
#別の解法,簡略化バージョン
シート2のA3:
=INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$C$1:$C$999=A$1,ROW(Sheet1!$C$1:$C$999),9999),ROW(A1)))&""
をコントロールキーをシフトキーを押しながらEnterで入力,下にコピー,右にコピー。
ご利用のエクセルのバージョンが不明ですが,Excel2007以降ならもっと簡略化できます
実際のデータの配置がご質問に例示されたみたいに「1行目からびっちり並んでる」みたいな作り方をしていなければ,応用でもう少し簡略化できる可能性があります。
ありがとうございました。
ただ、上記数式で例のデータではうまくいったのですが、実際のデータではこちらの応用力が足りなかったようでうまくいきませんでした。
(数式の意味は分かるのですが、なぜか空白が解答されてしまいます、なぜだろう…)
うまくいかなかったとしてもこれ以上この質問で引っ張るのもおかしいので、再度新たに書き直して実際のデータ表を利用して質問させていただきます。
その時はご協力お願いいたします。
No.3
- 回答日時:
データの数が多くなったりしますと計算に負担がかかります。
作業列を作って対応することです。例えば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.2ベストアンサー
- 回答日時:
>ただ、出来れば作業用のセルは使用しない方向でお願いします。
>他のデータに影響が出てきますので・・・
何故その様に思われておられるのでしょうか?
別に作業列を使用した処で、他のデータに影響する事は無いと思います。
それと、確認しておきたいのですが、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.1
- 回答日時:
もう少し詳細お願いします。
>そこから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列から検索しかつ通し番号順にコードだけを返せる数式を求めています。
説明が下手ですが、どうかよろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで80万行、50列位のデ...
-
ゼロを表示
-
Excelの計算が合いません。 諸...
-
Excelの警告について
-
今まで文字化けなく開けていたc...
-
EXCELの散布図で日付が1900年に...
-
エクセルでファイルの最終更新...
-
エクセルの数式バーのフォント...
-
作成した数式を値として表示し...
-
ExcelでASCを使って全角を半角...
-
Excelセルを跨いで合計を出す方法
-
Excel 大小比較演算子による「...
-
Excel関数について教えてくださ...
-
マクロの処理が遅くなった
-
エクセルで入力すると隣のセル...
-
Excel関数について教えてくださ...
-
エクセルの文字が途中から消える
-
エクセルを使用して、円周率を...
-
エクセルのセル内に分数などの...
-
条件付き書式設定で罫線を引き...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報