- 締切済み
エクセル:条件データの選出
エクセル (前提) 項目1から項目5の100行×5列の表 1行目:タイトル (1,1)が項目1、――、(1,5)が項目5 (2,1)から(100,5)がデータ (求めたいこと) 項目1データと項目2データの組合せを重複なく数え G列以降に存在するだけ G列 : H列 :I列 ----------------- 項目1データの1 : 項目1データの2:項目1データの3------------ 対応する項目21データの1 : 項目2データの2:項目2データの3------------: という形で出力したいする。(但し項目1は昇順にしたいが今回それは保留) (例) A列 B列 項目1 :項目2 C に A い B ろ A い B は C に D い (例結果) G列 A:B:B:C:D(ここは必ずしもソートの必要なし) い:ろ:は:に:い どう解決すればということで、 1行目に項目1、2行目に項目2ということで G1=INDEX($A$2:$B$100,SMALL(IF(MATCH($A$2:$A$100&$B$2:$B$100,$A$2:$A$100&$B$2:$B$100,0)=ROW($A$1:$A$99),ROW($A$1:$A$99)),COLUMN(A1)),ROW(A1)) 配列数式です。Ctrl + Shift + Enter 2行目にフィル、エラーが出るまで横へフィル 昇順ではないので、結果をコピー、そのまま[形式を選択して貼り付け]-[値]、 あとは、列単位で並べ替え。 このやり方で一つの立派な解なのですが。 (質問) 項目1と項目2がA列,D列と離れている場合はどうするかということです。 もちろんワーク用にシートをコピー後B列~C列を削除して適用すれば求まるのはわかりますが、そうしないでもできる方法があれば教えてください。
- taktta
- お礼率72% (1031/1430)
- オフィス系ソフト
- 回答数3
- ありがとう数3
- みんなの回答 (3)
- 専門家の回答
みんなの回答
- NNAQ
- ベストアンサー率56% (104/184)
#2のNNAQです。 まず補足質問の件ですが、数式を作るのにマクロの自動記録のようなツールは多分ありません。 1)フィルタオプションなどを使って手作業で求める方法 2)関数で求める方法 #2の回答は、2つの方法をご提示してみました、という意味で「関数のみで求めたのが…」と書きました。 数式も日本語も分かりにくくてすいません。説明させてください。 A列 項目1 C A B A B C D 仮にA列のみ重複なくさせる場合、普通はCOUNTIFを使いますがCOUNTIF関数は配列に組み込みにくいので、MATCHを使って B2=MATCH(A2,$A$2:$A$8,0) C2=ROW(A1) とすると、下の表になります。 A列 B列 C列 項目1 C 1 1 A 2 2 B 3 3 A 2 4 B 3 5 C 1 6 D 7 7 これで、B列とC列の値が同じものを抽出すればいいことになります。 上の2つの式をまとめて、A列の何番目を抽出すべきかを求めると B2=IF(MATCH(A2,$A$2:$A$8,0)=ROW(A1),ROW(A1)) C2=SMALL($B$2:$B$8,ROW(A1)) D2=INDEX($A$2:$A$8,C2,1) 下の表になります。 A列 B列 C列 D列 項目1 C 1 1 C A 2 2 A B 3 3 B A FALSE 7 D B FALSE C FALSE D 7 これを作業列を使わずにひとつのセルに入れると、 =INDEX($A$2:$A$8,C2,1) この式の C2 の部分に SMALL($B$2:$B$8,ROW(A1))が入って、 その $B$2:$B$8 の部分に IF(MATCH(…))が入るので、 どうしても、長くて分かりにくくなってしまいます。 まあ普通はこんな面倒なことはしません。 フィルタオプションで簡単に出来ますから。 つまり、#2で回答した式は G1=INDEX($A$2:$E$100,SMALL(…),1) G2=INDEX($A$2:$E$100,SMALL(…),4) G1はA列(1列目)に入力された値を出すのでINDEX関数の引数の列番号は「1」、 同じくG2はD列(4列目)なので「4」です。 以前のご質問の際にフィルだけで済むように変数化しましたが、 今回はA列とD列の場合ですし、フィルが1行分なので変数にするまでも無いと思い、定数としました。 「1」「4」の前の COLUMN(A1))は、SMALL関数の引数です。 それから、A2&B2 というデータを作る場合、 A列 B列 項目1 :項目2 123 45 12 345 このような値だと A2&B2 と A3&B3 が同じになってしまうので、 =A2&"_"&B2 とか、使ってない記号を挟まなければなりません。状況次第ですが。 長くなりましたが、抽出しなければならないシートが大量にあれば、 VBAで処理したほうが良いでしょうし、少量でも数千行ものデータがあるなら配列数式を使うと処理が重くなります。 また、場合によっては手作業でピボットやフィルタオプションを使った方が良いこともあります。 そんなわけで、わたしの回答は、あくまでも一案です…
- NNAQ
- ベストアンサー率56% (104/184)
F列を作業列として、F1に適当に見出しをつけます。 F2=A2&D2 100行目までコピーしたら[形式を選択して貼り付け]-[値]。 [データ]メニューの[フィルタ]-[フィルタオプションの設定]、 抽出先を[選択範囲内]、リスト範囲を[F1:F100]、[重複するレコードは無視する]にチェックして、[OK]。 抽出された状態でA列をコピー、G1に[形式を選択して貼り付け]-[行列を入れ替える]にチェック。 同じようにD列をコピー、G2に[形式を選択して貼り付け]-[行列を入れ替える]にチェック。 フィルタを解除してF列をクリアして完成。 これを関数のみで求めたのが先の配列数式です。 G1=INDEX($A$2:$E$100,SMALL(IF(MATCH($A$2:$A$100&$D$2:$D$100,$A$2:$A$100&$D$2:$D$100,0)=ROW($A$1:$A$99),ROW($A$1:$A$99)),COLUMN(A1)),1) G2=INDEX($A$2:$E$100,SMALL(IF(MATCH($A$2:$A$100&$D$2:$D$100,$A$2:$A$100&$D$2:$D$100,0)=ROW($A$1:$A$99),ROW($A$1:$A$99)),COLUMN(A1)),4) 複数列のデータを重複無く抜き出すのは、 A2&D2 と、ひとつのデータにしてしまうのが分かりやすいです。 A列C列E列なら =A2&C2&E2 とすれば、離れていても3列以上でも簡単になります。
お礼
いつもお世話になります。ていねいな説明ありがとうございました。 イメージとして式のやってることが掴めるのですが、まだ後ろの row,colomunの役割がよくつかめていません。 何か変数的に動かすのにつかっているはずですが。 なおG2の終わりのところはA2ではないでしょうか。元の場合A2ですがROW($A$1:$A$99)),COLUMN(A1)),4)
補足
>これを関数のみで求めたのが先の配列数式です。 この式は,NNAQさんの頭の中で作り出した式ですか、それとも何か操作した結果、どこからヒントとなる式情報を得て出した結果ですか。
- telescope
- ベストアンサー率54% (1069/1958)
ピボットテーブルで似たようなことが出来ると思います。 「データ」-「ピボットテーブル~」でピボットテーブルウィザードを開きます。 ウィザード3/3で既存のシートを選び、G1セルをクリックすればよいのですが、列数が心配なので、とりあえず新規のシートにしておきます。 項目2と項目1を列のフィールドにドラッグします。 項目1が左側に来たほうが良いので、項目2を先にドラッグします。 (あとからでもドラッグすれば順序は変更できます) データには、項目1、項目2のどちらかをドラッグします。 [A計][B計]などが表示されていますので、右クリックして[表示しない]を選びます。 必要な部分だけコピーして、G1に値を貼り付ければ出来ると思います。 [A計][B計]が表示される分列数が1.5倍ほどになるので列数が心配ですが、 列のフィールドにドラッグするとき、まず項目1をドラッグして、右クリックで[フィールドの設定]を選び、[集計]を[なし]にして[表示しない]ボタンを押します。 改めて、項目2、項目1の順に列のフィールドにドラッグして、データフィールドに項目1、項目2のどちらかをドラッグすれば、[A計][B計]は表示されません。 列数が足りないようでしたら、試してください。
関連するQ&A
- エクセル 間隔が空いたデータをその間隔のままコピペ
A列の5行目から10行目までのデータと D列の5行目から10行目までのデータと G列の5行目から10行目までのデータのデータをコピーし、 それぞれをB列、E列、H列(ひとつ右側の列)に貼り付けたいのです。 (実際にはG列以降の列もコピペしたいのですが、例としてA、D、G列にしてあります) 普通にB列に貼ると、B、C、D列に貼られてしまいます。 また、書式は貼りたくないので、値のみの貼り付けにしたいのです。 今はやり方が分からないので、A列をコピペ、D列をコピペ、・・・と列毎に処理して いますが、一度にまとめてコピペするにはどうすれば、良いのでしょうか?
- ベストアンサー
- その他MS Office製品
- エクセルのマクロでデータを左につめていく
お世話になります。エクセル2002でマクロを作成しようと思っています。 毎回同じ処理をしているので、良いサンプルがあったら教えてください。 やりたいこと A列からF列にランダムにデータが入力されています。 このデータをA列、B列、C列と、左に詰め表示させたいです。 ※G列以降にもデータが入力されていますが、処理したい列範囲はA列~F列です。 ※行数は、都度変わります。(UsedRange.Rows.Countで値を取得) イメージ(処理前) - : 空白セル *** : データが入力されているセル A列 B列 C列 D列 E列 F列 1行目 項目1 項目2 項目3 項目4 項目5 項目6 2行目 *** *** - *** *** - 3行目 - - - *** *** *** 4行目 *** - - - - - 5行目 *** *** - *** - - イメージ(処理後) A列 B列 C列 D列 E列 F列 1行目 項目1 項目2 項目3 項目4 項目5 項目6 2行目 *** *** - *** *** - 3行目 *** *** *** - - - 4行目 *** - - - - - 5行目 *** *** *** - - -
- ベストアンサー
- その他MS Office製品
- エクセルのデータ比較
シート1のA行のデータとB行のデータをすべて比較して、同じデータがあれば C列にそのデータを書き出したいのですが、どうすればよろしいのでしょうか。 行数(比較するデータの数)はそれぞれ数千行あります。 例(a~hはデータです) シート1 A列 B列 a c b d c f d h の場合、比較した結果をC列に次のように表示させます。 A列 B列 C列 a c c b d d c f d h どなたかよろしくご教示くださるよう、お願いします。
- ベストアンサー
- オフィス系ソフト
- excel 2つの条件(第三弾)
http://okwave.jp/qa5057168.html の更に続きですが、 A B C D E F G 1 あ 3 22 4 15 ? 2 い 4 15 4 15 ? 3 う 2 10 4 10 ? 4 え 4 10 5 お 4 15 想定していなかった5行目を追加しました。 E1に=LARGE(B1:B5,1) E2に=LARGE(B1:B5,2) E3に=LARGE(B1:B5,3) F1とF2とF3に{=MOD(LARGE(B1:B5*(MAX(C1:C5)+1)+C1:C5,ROW(B1:B5)),MAX(C1:C5)+1)} G1に=INDEX($A$1:$A$5,SUMPRODUCT((B$1:B$5=E1)*(C$1:C$5=F1)*ROW(A$1:A$5)))を入れましたがB列とC列が同じ組み合わせがある場合ではエラーが出てしまいます。 G1に『い』、G2に『お』を表示させる事は可能でしょうか? 重ね重ね申し訳有りませんがわかる方おりましたらよろしくお願いします。
- 締切済み
- オフィス系ソフト
- エクセルのデータから一致するデータを出したい
エクセルの表から一致するデータに対応する指定列のデータを表示させたいのですがどの関数で設定すればよいのか教えて下さい。 A2からDの30までの表(実際には4000列以上) 1行は見出し A列は1~のナンバリング B列は商品コード(同じコードが複数ある) ・昇順に並べてある C列は日付け ・2番目に優先で昇順に D列は文字列 探したい商品コードをB1入力、それに対応するD列の文字列の昇順のトップをD1に表示させたい この内容で伝わるでしょうか オートフィルターで選ぶのも考えたのですが、入力した時その列のある列のデータが瞬時に見たいのです。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- データの最終行まで計算式をコピーするマクロ
エクセル2013を使用しています。 A列2行からとB列2行から各々 データ行が数百行あり、 C列2行に計算式を入力。 セルの右下フィルハンドルをダブルクリックすると、 例 C2:C600 の固定範囲で計算式がコピーできますが、 毎回このデータ行数が変わります。 計算式はあと4つありますので全部で5回繰り返しとなります。 マクロで最終行を検索してデータの最終行を見つけてコピーするように したいのです。 表のイメージ A B C D E F G H I J K L M 1 data datb 計算式1 計算式 計算式 計算式 計算式 2 ↓ ↓ 3 | 600 data datb 計算式
- ベストアンサー
- オフィス系ソフト
- Excel マクロで連番作成方法を教えてください。
OS:Windwos2000 Excel2000 以下の内容をマクロで実施する方法を教えてください。 過去の質問を確認しましたが、 私の希望する内容とは分部とはことなるため、 質問させていただきました。 A列に入力されているデータの最終行まで 各B列、C列、D列にオートフィル機能を使って数式コピーを行いたい。 ※A列のデータ数は固定ではなく増減します。 ※B列、C列、D列の先頭行には数式を入力済みです。 ※オートフィル機能でなくても問題ありません。 よろしくお願いいします。 A列 B列 C列 D列 E列 データ =LEFT(A1,4) - =MID(A1,8,14) =B1&C1&D1 データ =LEFT(A2,4) - =MID(A2,8,14) =B2&C2&D2 データ =LEFT(A3,4) - =MID(A3,8,14) =B3&C3&D3 データ =LEFT(A4,4) - =MID(A4,8,14) =B4&C4&D4 データ =LEFT(A5,4) - =MID(A5,8,14) =B5&C5&D5 ・
- ベストアンサー
- オフィス系ソフト
- エクセルで複数列のデータに対して検索後、対象行を表示できますか?
よろしくお願いします。 エクセル2002を使っています。 例として 1 E F G 2 データA イ ロ ハ 3 データB ロ ハ 4 データC ニ 5 データD ニ ロ E,F,G列に行方向にイ~ニのデータがランダムに入っています。 オートフィルを使う様に3列中にイを含む行、又はイ又はロを含む行、イ、ロ、ハを含む行を表示させたいのですが、何か方法があるでしょうか? どなたかご存知の方ご教授お願いします。
- ベストアンサー
- オフィス系ソフト
- VBA,二つのExcelのsheetにデータ保存
VBA初心者です。 皆様のお力をお貸し頂きたく質問させて頂きます。よろしくお願いいたします。 質問内容は、下記になります。 Excelのsheet1には、縦列A,B,C・・・とデータが入っております。 sheet1の例 A B C sheet2は、入力するsheetです。 今回はA列の3行目からとします。 問題は、sheet1の縦の列をA,B,C,Dとすればデータの更新は、出来るのですが sheet1のデータのA,B,C一つ飛んでEまた一つ飛んでGという感じでsheet1のデータを 飛ばしてsheet2に表示、更新(保存)をしたいと思います。 ですので、sheet2のA列の3行目からA,B,C,飛んでE飛んでGとsheet1からデータを 表示させ、さらにsheet2の入力値が変更されると、sheet1のデータが入っている A,B,C,E,Gに更新される仕様です。 sheet1(データが入っています) A , B , C , E , G , 値1 , 値2 , 値3 , 値4 , 値5 sheet2(入力する、入力したデータは、sheet1へ更新される) A列 3行目 、sheet1の値1(A列)が入ります。 4行目 、sheet1の値2(B列)が入ります。 5行目 、sheet1の値3(C列)が入ります。 6行目 、sheet1の値4(E列)が入ります。 7行目 、sheet1の値5(G列)が入ります。 以上です。申し訳ございませんが、ご教授よろしくお願いいたします。
- ベストアンサー
- Excel(エクセル)
- Excelで1列のデータを5列に振り分けする
Excel2003でA列に1行にあるデータ(半角英数字)を 次のシートに、1行ずつコピーして、それを5列に振り分けするというマクロを作りたいです。 (ただし、コピーは2つずつの時もあります。) 元データ ●A列 B列 C列 1 2 3 4 5 ・ ・ 加工後 ●A列 B列 C列 D列 E列 1 1 2 2 3 3 4 4 5 5 上記のような感じです。ただデータは数字のみでなくて、英数字です。 Excelのマクロは全くの初心者で大変困っています。 どうぞよろしくお願いたします。
- ベストアンサー
- オフィス系ソフト
お礼
この回答にて全てのもやもやが完全になくなりました。ごていねいなご回答感謝に耐えません。ありがとうございました。 これでもう関数に対する恐怖も一層しました。
補足
NNAQさんのエクセル力いつも感嘆しています。 一つおききしたいのですが、 G1=INDEX($A$2:$B$100,SMALL(IF(MATCH($A$2:$A$100&$B$2:$B$100,$A$2:$A$100&$B$2:$B$100,0)=ROW($A$1:$A$99),ROW($A$1:$A$99)),COLUMN(A1)),ROW(A1)) で (1)ROW($A$1:$A$99)が99の理由 (2)式で$A$99を$A$100にするとエラになる理由 (3)G1式をフィルドラッグして、G3までにもってくるとG3がエラーが出る理由などもしできましたら説明ください。 ト100