Excelでデータ抽出をする際の関数について。
A列の情報(文字列)と、B列の情報(文字列)の2つの組み合わせに対し、条件がある
・下記のような情報がある時、
A列 B列
Blue Red
Blue Yellow
Red Blue
Yellow Red
Red Green
・下記の条件、
A列 B列
Blue と Blue は Aグループ
Blue と Yellow は Bグループ
Blue と Red は Cグループ
Blue と Green は Dグループ
Red と Blue は Aグループ
Red と Yellow は Bグループ
Red と Red は Cグループ
Red と Green は Dグループ
・・・つづく
にあてはめて、条件の●グループを引っ張ってきたいのですが、
どのような関数を入れたらいいのでしょうか?
よろしくお願いします。
No.1
- 回答日時:
IF関数でグループ分けはできますがそれを集計する為には更に別の関数を使い集計を行うか、VBAでユーザー関数を作成するしかありません
ワークシート関数だけで行うには
IF関数でグループ分けをし、COUNTA関数でそれぞれのグループを数えます
例えばC列にグループ分けの出力をするのであればC1セルに
=IF(A1="blue”&B1=”blue”、A,if(A1=blue”&B1="yellow"、B、IF(・・・・
とグループ分けの数だけIF関数を繰り返し
C列のデータ最下行に
=COUNTA(c1:c??、”A”)
その下に
=countA(c1:C??,”B”)
とグループの数だけ入れるだけです
No.2
- 回答日時:
組み合わせが沢山あるようなので、マクロで仕分けをしたらいかがですか?
Sub Macro1()
Y=1 :' データ行は1行目
while cells(Y,"A")>""
if cells(y,"A")="Blue" and cells(y,"B")="Blue" then
cells(y,"C")="A"
elseif cells(y,"A")="Blue" and cells(y,"B")="Yellow" then
cells(y,"C")="B"
elseif cells(y,"A")="Blue" and cells(y,"B")="Red" then
cells(y,"C")="C"
elseif cells(y,"A")="Blue" and cells(y,"B")="Green" then
cells(y,"C")="D"
elseif ・・
・組み合わせの条件を書きます
・
endif
y=y+1
wend
end sub
A列が空白になるまでC列にグループ分けをします
回答いただき、ありがとうございます。
残念ながら、マクロが得意ではなく短時間で処理できそうにありません。
今後の参考にさせていただきます。ありがとうございました。
No.3ベストアンサー
- 回答日時:
条件が1つなら、一般的にはVLOOKUP関数で可能なケースだと思いますが
2つあるということなので、少し工夫する必要がありますね。
「A列の文字列とB列の文字列を結合した文字列」と
「それに対応するグループ」の対応表を準備します。
「BlueBlue」「Aグループ」
「BlueYellow」「Bグループ」
「BlueRed」「Cグループ」
「BlueGreen」「Dグループ」
「RedBlue」「Aグループ」
「RedYellow」「Bグループ」
「RedRed」「Cグループ」
「RedGreen」「Dグループ」
仮に上記の対応表がSheet2のA1:B8に入っているとすると
=VLOOKUP(A1&B1,Sheet2!$A$1:$B$8,2,FALSE)
のような式で可能かと思います。
ありがとうございます。
私のスキルレベルでは、ご教示いただいた内容が一番スムーズに作業できそうです。
助かりました、ありがとうございました。
No.4
- 回答日時:
グループ分けの一覧表があり、データーベースからそのグループのデータを抽出したいという意味なら、以下のような関数で表示できます(添付画像参照)。
抽出したいグループ名をE1セルに入力し、I~K列にグループ分けの一覧表がある場合、E3セル(どのセルでも同じ数式)に以下の式を入力し、右方向および下方向に適当数オートフィルします。
=INDEX(A:A,SMALL(INDEX(($K$2:$K$100<>$E$1)*1000+ROW($K$2:$K$100),),ROW(A1)))&""
No.5
- 回答日時:
一例です。
(1)別シートに5×5のマトリクス表を作成(行・列見出しに色名称を設定)
(2)C列にOFFSET又はINDEX関数でマトリクス表から抽出
マトリクス表はSheet2のA1:E5範囲、データシートのC1に以下の関数を設定、下方向にコピー
(例)=OFFSET(Sheet2!$A$1,MATCH($A1,Sheet2!$A$1:$A$5,0)-1,MATCH($B1,Sheet2!$A$1:$E$1,0)-1)
No.6
- 回答日時:
No4の回答に誤りがありましたので訂正です。
E3セルに入力する式は以下のような複雑な数式になります。
=INDEX(A:A,MAX(INDEX((INDEX($I:$I,SMALL(INDEX(($K$2:$K$100<>$E$1)*1000+ROW($K$2:$K$100),),ROW(A1)))=$A$2:$A$100)*($B$2:$B$100=INDEX($J:$J,SMALL(INDEX(($K$2:$K$100<>$E$1)*1000+ROW($K$2:$K$100),),ROW(A1))))*ROW($A$2:$A$100),)))&""
上記の数式は、データベースに同じデータが2件以上ないという条件の数式になっています。
重複データがあるなど、データベースにさらに条件が付くようであれば、数式で対応するよりも(数式だけでも対応できる可能性はあります)フィルタオプションの設定で抽出するほうが計算負荷も少なく簡単なような気がします。
No.7
- 回答日時:
ご希望の関数ではありませんが、複雑な条件にも対応できる最も簡単で確実な方法である、フィルタオプションの設定を利用する方法を提示します。
グループ分け対応表で、「データ」「フィルタ」で抽出したいグループを選択し、この抽出範囲をコピーし、条件のセル(添付画像のE2:F3セルの部分)に貼り付けます(1行目は項目名)。
これで「データ」「フィルタ」「フィルタオプションの設定」(2007は「詳細設定」)をクリックし、リスト範囲を少し大きめに選択しておき(今後のデータの追加に対応できるようにする)、検索条件範囲にE1:F3を指定し、抽出先を「指定した範囲」にして、例えばE5セルを選択します。
上記の操作を、マクロの記録で実行すれば、自動化することも可能です。
なお、この場合は、上記のフィルタ操作の代わりに最初に提示した数式(No4の回答)で該当するA列とB列のデータを表示させておき(グループを選択すると対応するデータが表示される)、名前定義を利用してこの検索条件範囲を自動的に取得できるようにする必要があります。
もしこの方法に興味があれば具体的な操作法を提示しますので、ご連絡ください。
No.9
- 回答日時:
> A列 B列
Blue と Blue は Aグループ
Blue と Yellow は Bグループ
Blue と Red は Cグループ
Blue と Green は Dグループ
Red と Blue は Aグループ
Red と Yellow は Bグループ
Red と Red は Cグループ
Red と Green は Dグループ
・・・つづく
とありますが、B列の値のみでグループ分けをするのでしょうか?
それとも、
A列 B列
Blue と Blue は Aグループ
Blue と Yellow は Bグループ
Blue と Red は Cグループ
Blue と Green は Dグループ
Yellow と Blue は Eグループ
Yellow と Yellow は Fグループ
Yellow と Red は Gグループ
Yellow と Green は Hグループ
Red と Blue は Iグループ
Red と Yellow は Jグループ
Red と Red は Kグループ
Red と Green は Lグループ
Green と Blue は Mグループ
Green と Yellow は Nグループ
Green と Red は Oグループ
Green と Green は Pグループ
の間違いなのでしょうか?
取り敢えず、後者であるものとして話を進めます。
まず、適当なSheet(例えばSheet2)に以下の様なリストを作成しておきます。
A列 B列 C列 D列 E列
1行目 Blue Yellow Red Green
2行目 Blue A B C D
3行目 Yellow E F G H
4行目 Red I J K L
5行目 Green M N O P
次に、元の条件が入力されているSheetのC1セルに、次の数式を入力して下さい。
=IF(OR(COUNTIF(Sheet2!$A2$:$A$5,$A1)=0,COUNTIF(Sheet2!$B$1:$E$1,$B1)=0),"",INDEX(Sheet2!$B$2:$E$5,MATCH($A1,Sheet2!$A2:$A$5,0),MATCH($B1,Sheet2!$B$1:$E$1,0))&"グループ")
続いて、C1セルをコピーして、C2以下に貼り付けて下さい。
以上です。
ありがとうございます。
本当にいろんな考え方や関数があるのだと驚いております。
とても参考になりました。ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- C言語・C++・C# C言語の質問です。バイナリ形式で保存されたWindows Bitmap形式の画像ファイルを読み込み、 3 2023/07/19 14:58
- Excel(エクセル) Excel VBAでのセルやシェイプの色指定について。 たとえば、”red”という文字列を用いて … 4 2022/11/15 09:44
- Ruby pandasでsqlite3にテーブル作成・追加・読み出しでindexの取り扱い方教えてください 5 2023/03/08 09:57
- HTML・CSS img と p を縦中央に配置したいのですがうまくいきません。 2 2023/01/12 14:38
- JavaScript jsで、配列内の文章を改行する際どのようにすればいいですか。 3 2022/07/05 20:40
- その他(プログラミング・Web制作) VS CodeでTEXファイルにPDF形式の図を挿入する方法 2 2023/01/19 11:56
- Excel(エクセル) 指定した条件でTRANSPOSE関数を使う 5 2023/08/18 19:45
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- PHP imageフォルダに、画像をリサイズして保存する時のファイル名を変更したい 1 2023/05/30 11:39
- Visual Basic(VBA) 【困っています2】VBA 追加処理の記述を教えてください。 2 2022/08/26 11:42
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
MicrosoftOfficeの1ユーザー2...
-
MicrosoftOffice2019なんですが、
-
Excel 日付を比較したら、同じ...
-
Microsoft365の「お支払いを更...
-
ウィンドウィズ メモ帳で日付だ...
-
Excelで空白以外の値がある列の...
-
エクセルのシフト表を簡単にGoo...
-
理由を教えてください。
-
VBA
-
web上にあるエクセルをショート...
-
バソコンが二台とも壊れ後換装...
-
【マクロ】文字を1文字づつ、...
-
Excelのセルの重複チェックが出...
-
マイクロソフト 一時使用コード...
-
office365って抵抗感ないですか?
-
Outlook 電源OFFの受診の仕方
-
エクセルで例えば、A1に㈱ベ...
-
自分の専門分野の仕事。初見で...
-
excelの画面のグリッド線の消滅。
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報