Excel関数でデータを抽出する方法

質問

Excel初心者教室での課題で、以下のような問題を出されました。

次の表から、データが全てあるものだけを抽出し、sheet1からsheet2に移しなさい。

〈名前〉〈 E社〉〈 F社〉〈 G社〉
〈 A 〉〈 1 〉 〈 2 〉 〈 3 〉
〈 B 〉 〈 2 〉 〈 2 〉
〈 C 〉〈 3 〉〈 1 〉〈 5 〉
〈 D 〉 〈 1 〉

上の表からAとC(3社共にデータがある)ものだけを抽出したいのですが、回答条件が

●データ抽出にはオートフィルター機能は使用せず、関数で処理すること
●今まで習った知識で回答可能

とあり、まだ初心者クラスで、データ抽出に関係がありそうな関数はIF関数やVLOOKUPくらいしか思い当たらず、それらを使って抽出を試みたのですが「三社共にデータがあるものだけ抽出」という条件が処理出来ず、上手く出来ませんでした。
手詰まりで先に進めずに今、困っています。

そこで、Excelに詳しい方にアドバイス頂けたらと思い、書き込みしました。
宜しくお願いします。

文章力が低いので分かり辛い文面で、表も見辛くてすみません。

回答No.5

MarcoRossiItaly · ベストアンサー

No.1 です。結構、いろいろご存じのようですね。

ではオートフィルタを使わず、作業列は使っていいってことなら、こんな感じではいかが？

E2　　　=a1+(counta(b2:e2)=4)
　　　　あるいは
　　　　=a1+if(counta(b2:e2)=4,1)
G2～G5　1～4
H2　　　=vlookup($G2,$A$2:$E$5,column(H$1)-column($G$1)+1,)　　　　　　　　 ……エラー回避なし
　　　　あるいは
　　　　=iferror(vlookup($G2,$A$2:$E$5,column(H$1)-column($G$1)+1,),"-")　　……エラー回避あり

H2 セルを H2:K5 のセル範囲にコピペあるいはドラッグ。

VLOOKUP 関数は第 4 引数の指定内容により、データを拾ってくる行が変わるという点にご注意。試しに第 4 引数に 1 を入れてみれば分かります。

何か分からないことがあればお尋ねください。

お礼

あけましておめでとうございます。
二度目のご回答、有難うございます(>.<)
ご丁寧な表までお作り頂いて、すみませんでした(;＿q)

早速課題に取り組み、無事に直ぐに作業を終えることが出来ました。
その後、課題の続きに手間取ってしまい(先程無事に終わりました)お礼が遅れてしまい、申し訳ございませんでした。

この度はお忙しい中、本当に有難うございました。

回答No.6

KURUMITO

初心者でも分かり易い方法は作業列を作って対応する方法です。
例えばシート1に元の表が有るとしてA1セルからD1セルには項目名が2行目から下方にそれぞれのデータが入力されているとします。ここではF列までもが利用される場合を示します。
G列を作業列としてG2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(B2:F2,"<>")=COUNTA(B$1:F$1),MAX(G$1:G1)+1,"")

この式の意味はB2セルからF2セルの間で空白以外のセルの数が1行目の項目に書いた項目の数と一致している場合にはG$1からG1セルの最大値にプラス1を下数値を表示しなさいとのことです。つまり3つの項目がB1セルからF1セルの間に入力されていればデータのあるセルの数が3個の場合に数値を上から順に１つずつ加算していくことになります。

シート２はお求めの表としてA列からF列までの２行目から下方に表示させるとしてA２セルには次の式を入力してF２セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$G:$G),"",IF(INDEX(Sheet1!$A:$F,MATCH(ROW(A1),Sheet1!$G:$G,0),COLUMN(A1))="","",INDEX(Sheet1!$A:$F,MATCH(ROW(A1),Sheet1!$G:$G,0),COLUMN(A1))))

お礼

あけましておめでとうございます。
お忙しい中、ご回答有難うございました(>.<)
とても分かりやすい説明で、目から鱗が落ちました。
課題も無事に終わりました。

本当に助かりました。有難うございました。

回答No.4

web2525

>今まで習った知識で回答可能

習ったことだけで答えを導き出せってことかな？

だとすると何を習っているのかがはっきりしないと、的確な回答は難しい

>三社共にデータがあるものだけ抽出

〈名前〉 E社 F社 G社
〈 A 〉〈 1 〉〈 2 〉〈 3 〉
〈 C 〉〈 3 〉〈 1 〉〈 5 〉

こんな感じの表を
sheet2に作れって事でいいのかな？

sheet1に作業列使用が可能であれば
IF関数、COUNT関数、ROW関数、、RANK関数かLARGE関数、VLOOKUP関数
を利用して出来そうですが、全部習っていますか？

お礼

こんばんは。お忙しい年の瀬に、ご回答有難うございました。
説明不足で分かり辛い文章になってしまい、申し訳ございませんでした。

「習ったことだけ(習ったことの応用)で答えを導き出せ」ということだと思います。

はい。
〈名前〉E社 F社 G社
〈A〉〈1〉〈2〉〈3〉
〈C〉〈3〉〈1〉〈5〉

上のような表を、sheet2に作りたいです。

sheet1に作業列可能です。
IF関数、COUNT関数、ROW関数、RANK関数、RARGE関数、VLOOKUP関数、全て習っております。
お忙しい中大変恐縮ですが、ご指導いただけましたら幸いです。

回答No.3

keithin

＞データが全てあるものだけを抽出し、sheet1からsheet2に移しなさい

どういう体裁に仕上げたいのかご質問で情報提供されていませんが、初心者さん向けであれば、添付図の上段左から右のようにします。

シート２のＡ２：
=IF(COUNT(Sheet1!$B2:$D2)=3,Sheet1!A2,"")
右にコピー、下にコピー。

もしも添付図の下段のように詰めて表示したいと思っているのですと、課題が本当にそんなのを求めているのか再確認してください。
もちろん頑張って細工すれば、やればできる内容ですが、あまり初心者さん向けの課題とは言えません。

シート１のＣ２：
=IF(COUNT(E2:G2)=3,SUM($C$1:C1,1),"")
以下コピー

シート２のＣ２：
=IF(ROW(C1)>MAX(Sheet1!C:C),"",ROW(C1))
以下コピー

シート２のＤ２：
=IF($C2="","",VLOOKUP($C2,Sheet1!$C:$G,COLUMN(B2),FALSE))
右にコピー、下にコピー。

お礼

こんばんは。お忙しい年の瀬に、ご回答有難うございました。
わざわざ表までお作り頂いて、お付き合い下さり、本当にすみません(;＿q)
有難うございます。

お貼り頂いた画像が正にそうしたかった形でしたので、泣きそうになりました。
全く違うことをしていたので、自分ではとても解答出来なかったと思います。

本当に助かりました。有難うございました。

回答No.2

tom04

こんばんは！
＞●今まで習った知識で回答可能
とありますが、どの程度の関数をご存じなのか判らないので、
一般的な関数を使った方法の一例です。

↓の画像のようにSheet1に作業用の列を設けます。
Sheet1の作業列E2セルに
=IF(COUNTA(B2:D2)=3,ROW(),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

Sheet2のA2セルに
=IF(COUNT(Sheet1!E:E)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!E:E,ROW(A1))))
という数式を入れ下へオートフィルでコピーで画像のような感じになります。

※　上記数式内の関数で判らない関数があれば、ご自身で調べてみてください。m(_ _)m

お礼

こんばんは。お忙しい年の瀬に、ご回答有難うございました。
記述が足らなくて、分かり辛い文章になってしまい、すみませんでした。

わざわざ表までお作り頂いて、本当に有難うございます(;＿q)
何をすれば良いのかも分からなくなってしまい、途方にくれていたので、助かりました。
お答え頂いた関数は、幸い見たことのあるものばかりでしたので、参考にさせて頂きます。

本当にすみませんでした。有難うございました。

回答No.1

MarcoRossiItaly

AND 関数とか OR は習いましたか？「空白セルである」とか「空白セルでない」条件とは、どう書くのでしょうか。また、複数の種類の関数を組み合わせるときは、どんな数式になるのでしたでしょうか。質問者さんのご存じのものだけでできそうですね？実際に数式を作ってみた上で、うまく行かないようならその数式を示しながらまたお尋ねください。

お礼

こんばんは。お忙しい年の瀬に、ご回答有難うございました。
「データの抽出」と課題にあったので、今までIF関数のことばかり考えていました。
「空白セルである」「空白セルでない」条件はどう書くのか、授業のレジュメを参考に、もう一度数式を考え作ってみます。

有難うございました。