シート1
1|品名 種類 名前 データ
2|A 1 あ 21
3|B 2 い 11
4|F 2 あ 51
5|A 1 い 21
6|B 2 あ 64
7|A 1 あ 84
シート2
1|品名 A
2|種類 1
3|
4|名前
5|あ
6|い
シート1のデータから、シート2の2つの条件(A1:B2)で、重複しない名前を表示する方法を教えて下さい。
上記のデータでいうと、品名Aであり種類が1の名前を重複なしで特定の位置(A5)から詰めて縦に表示するようにして、
シート1のデータ、シート2の条件を変更したらA5からの名前が自動更新されるようにしたいです。
以下できたら追加したいこと
1.表示する名前の順番が、シート2の条件で集計したデータ値が多い名前順
2.シート2のB2の種類に0を入れると種類の条件無しで表示
実際はA5からの名前の行は数行を結合したもので右側には複数の行でデータ項目別になって、名前が入力された時に日毎のデータを自動表示しています。
全部で名前の種類は30以上ありますが、品名の条件をつければ名前は10種類以下になります。
グラフは名前別で表示しており、不要な名前が増えると表示しずらいので、必要な名前だけのデータを表示するようにしたいです。
どうかよろしくお願いします。
No.3ベストアンサー
- 回答日時:
No.1です!
補足を読ませていただきました。
データは10000行位あり、今後も増える予定だというコトですので、
VBAでの方法はどうでしょうか?
※ 実は前回、
>1.表示する名前の順番が、シート2の条件で集計したデータ値が多い名前順
>2.シート2のB2の種類に0を入れると種類の条件無しで表示
の件を無視していましたので、
Sheet1・Sheet2の配置はお示し通りになっていて、Sheet3を作業用に使用しています。
(Sheet3は現在なにも使用していないという前提です。)
画面左下にあるSheet2のSheet見出し上で右クリック → コードの表示 → VBE画面が出ますので
↓のコードをコピー&ペーストしてSheet2のB1・B2セルのデータを変更してみてください。
Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim i, j As Long
Dim ws1, ws3 As Worksheet
Set ws1 = Worksheets(1)
Set ws3 = Worksheets(3)
Application.ScreenUpdating = False
If Target.Row <= 2 And Target.Column = 2 Then
If WorksheetFunction.CountBlank(Range("B1:B2")) Then Exit Sub
i = Cells(Rows.Count, 1).End(xlUp).Row
If i > 4 Then
Range(Cells(5, 1), Cells(i, 1)).ClearContents
End If
ws1.Columns("A:D").Copy Destination:=ws3.Cells(1, 1)
ws3.Columns("A:D").Sort key1:=ws3.Cells(1, 4), order1:=xlDescending
ws3.Columns(1).Insert
If Cells(2, 2) = 0 Then
For i = 2 To ws3.Cells(Rows.Count, 2).End(xlUp).Row
If ws3.Cells(i, 2) = Cells(1, 2) And _
WorksheetFunction.CountIf(ws3.Columns(1), ws3.Cells(i, 4)) = 0 Then
ws3.Cells(i, 1) = ws3.Cells(i, 4)
End If
Next i
For j = 2 To ws3.Cells(Rows.Count, 1).End(xlUp).Row
If ws3.Cells(j, 1) <> "" Then
Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws3.Cells(j, 1)
End If
Next j
Else
For i = 2 To ws3.Cells(Rows.Count, 3).End(xlUp).Row
If ws3.Cells(i, 2) = Cells(1, 2) And ws3.Cells(i, 3) = Cells(2, 2) And _
WorksheetFunction.CountIf(ws3.Columns(1), ws3.Cells(i, 4)) = 0 Then
ws3.Cells(i, 1) = ws3.Cells(i, 4)
End If
Next i
For j = 2 To ws3.Cells(Rows.Count, 1).End(xlUp).Row
If ws3.Cells(j, 1) <> "" Then
Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws3.Cells(j, 1)
End If
Next j
End If
ws3.Cells.ClearContents
Application.ScreenUpdating = True
End If
End Sub 'この行まで
※ B2セルが「0」の場合は「種類」は無視して、「品名」ごとの最大データの人を表示!
※ B2セルが「0」以外の場合は「品名」・「種類」が一致する最大データの人を表示!
という考え方にしています。
お役に立てば良いのですが・・・m(_ _)m
ご回答有難うございます。
わざわざプログラムを書いて頂きありがたいです。
実際の形式にするのにすぐには無理そうなので、あとでとっておきます。
とりあえず教えて頂いた追加列で種類判定もつけてうまくいっております。
有難うございました。
No.2
- 回答日時:
補助列なしに関数で重複のない名前データを表示するなら(最大10件まで)、以下のような複雑な数式を使う必要があります。
ただし配列数式ですので入力後Ctrl+Shift+Enterで確定してください。
=INDEX(Sheet1!C:C,SMALL(IF((MATCH(Sheet1!$A$2:$A$10000&Sheet1!$B$2:$B$10000&Sheet1!$C$2:$C$10000,Sheet1!$A$2:$A$10000&Sheet1!$B$2:$B$10000&Sheet1!$C$2:$C$10000,)=ROW($A$2:$A$10000)-1)*($B$1&$B$2=Sheet1!$A$2:$A$10000&Sheet1!$B$2:$B$10000),ROW($A$2:$A$10),10000),ROW(A1)))&""
一般的に、上記のような配列数式はデータ範囲を大きくしたり、数式で表示するセルが多くなると再計算に時間がかかりシートの動きが重くなるなどのデメリットがあります。
今回のような表示件数が10件までで良いなら、おそらくそれほど大きな影響はないかもしれませんが、シートの動きが重く感じたら、シートの計算方法を「手動」に設定しておき、必要に応じてF9キーで再計算するような対応をしてください。
一方、1万行もあるデータに対して、補助列の多数のセルにCOUNTIF関数やMATCH関数など比較的メモリーを多く消費する関数を入力すると、パソコンがハングアップすることがありますので、今回のようなケースでは補助列を使う方法はあまりお勧めできません。
しかし、一般的に配列数式を駆使して表示する場合は、数式そのものの意味がわかりにくく、数式の変更などのメンテナンスができないと思いますので(表示条件が複雑になるほど数式が複雑になります)、関数ではなく、フィルタオプションの設定などの一般機能を利用した方法で、該当データを抽出する方法を利用されることをお勧めします。
ご回答有難うございます。
分かりやすく書いて頂き勉強になりました。
配列数式ではかなり重くなったので、別の方法でためそうと思います。
No.1
- 回答日時:
こんばんは!
色々方法はあると思いますが、一例です。
↓の画像のようにSheet1に作業列を2列設けています。
(目障りであれば遠く離れた列にするか、作業列を非表示にします)
Sheet1の作業列1E2セルに
=IF(COUNTBLANK(A2:C2),"",A2&"_"&B2&"_"&C2)
作業列2のF2セルに
=IF(AND(COUNTIF($E$2:E2,E2)=1,A2&"_"&B2=Sheet2!$B$1&"_"&Sheet2!$B$2),ROW(),"")
という数式を入れオートフィルでずぃ~~~!っと下へコピーしておきます。
Sheet2のA5セルに
=IF(COUNT(Sheet1!F:F)<ROW(A1),"",INDEX(Sheet1!C:C,SMALL(Sheet1!F:F,ROW(A1))))
という数式を入れオートフィルで下へコピーすると
画像のような感じになります。
参考になれば良いのですが・・・m(_ _)m
早速のご回答有難うございます。
画像まで付けて頂きとても参考になりました。ちょっと理解に時間かかりそうですが、実際の条件に修正して試してみようと思います。
あと書いてなかったので補足させていただきます。
実際シート1のデータは、月のデータで、列数が50くらいあり、行数は多くて10000くらいあります。
毎週くらいに更新して追加していく予定で、更新するたびに行数が増えていきます。
もし作業列の追加がない例があれば是非ご教授お願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) vbaについて 主に以下のような設定をしたいです。 Aブックの表の行数が20未満だったら Bブックの 1 2023/06/08 23:40
- Visual Basic(VBA) 【困っています2】VBA 追加処理の記述を教えてください。 2 2022/08/26 11:42
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) 複数セルデータを別シートの単一セルにコピーしたい。(詳細をご参照ください) 1 2022/12/14 15:08
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/02 10:20
- Visual Basic(VBA) エクセルについて教えてください。 3 2023/06/28 09:11
- Excel(エクセル) 【エクセル」 特定のセルで条件抽出した列を、別シートに上から詰めて表示したい。 8 2022/04/08 16:00
- Excel(エクセル) エクセル シート比較で書式を変えるを繰り返したい 2 2023/05/05 11:25
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
VLOOKUP関数について
-
1つのPCに「Excel 2010」「Exc...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
Office 2021 Professional Plus...
-
会社PCのメールが更新されない
-
マイクロソフト オフィスについて
-
vb.net オブジェクト指向につい...
-
【スプレッドシート】白色のセ...
-
Microsoft Formsの「個人情報や...
-
Excel VBA 日程表からスケジュ...
-
スプレッドシートにて、条件付...
-
【スプレッドシート】最初の契...
-
teams設定教えて下さい。 ①ビデ...
-
Windows 11で、IME言語バー(IM...
-
大学のレポート A4で1枚レポー...
-
outlookで宛先が異なるメールを...
-
エクセルで例えば、A1に㈱ベ...
-
Googleのスプレッドシートでシ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
officeビジネス型のワードやエ...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
Microsoft Officeを2台目のPCに...
-
何このステータスバー
-
2つのシートの一致する行のセ...
-
会社のTeamsのことで相談です。...
-
エクセルにリンクされるのをし...
-
Windows 11で、IME言語バー(IM...
-
office2010とoffice365の共存で...
-
Microsoftのパソコンです。 エ...
-
エクセルでXLOOKUP関数...
-
Excel関数について質問ですm(__)m
-
VBA
-
自分の専門分野の仕事。初見で...
おすすめ情報