No.4ベストアンサー
- 回答日時:
No.2です。
Sheet1の「○」部分は実際は数値だというコトなので・・・
もう一度画像をアップしてみます。
今回は作業用の列を設けて配列数式にしないようにしてみました。
画像のF2セルに
=IF(A2=Sheet2!$A$1,ROW(),"")
という数式を入れオートフィルでずぃ~~~!っとしっかり下までコピーしておきます。
Sheet2のA4セルに
=IF(COUNT(Sheet1!$F:$F)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$F:$F,ROW(A1))))
このままではSheet1が空白の場合は「0」が表示されてしまいますので、
A4セルの表示形式をユーザー定義から
G/標準;G/標準;
としておきます。
(オプションから「ゼロ値」のチェックを外せば、セルの表示形式には手を加える必要はありません)
これを列方向・行方向にオートフィルでコピーすると画像のようになります。
※ 前回の数式では空白セルに「0」を表示させないようにしたため表示されたデータは文字列になっていました。
次に
>上からコピーして値で張り付ければよいのですが、IDの数が数百ありますので、その都度教えていただいた関数を張り付けるのは大変ですので
に関してですが、
数式はそのままでSheet2のA1セルのIDを入力すればそのデータが表示されます。
仮に、IDすべてを順番に表示したい!という場合は
VBA等別の方法を考える必要があると思います。
この程度でごめんなさいね。m(_ _)m
tom04 様
お世話になりました。
たくさんの回答をいただいたのですが、自分にとって一番理解しやすかったので、tom04様の回答を
ベストアンサーとさせていただきます。
ありがとうございました。
No.6
- 回答日時:
どうやらシート1のB列からE列に入力されているデータは文字列で入力されているようですね。
そのためにシート2に表示されたデータでもSUM関数などが機能しないのですね。データをシート2に取り込む段階で数字を数値に変えるために*1などの工夫をすればよいでしょう。
配列数式などの難しい関数を使うことなく、作業列を作ってい対応することにします。
シート2のA1セルには抽出のためのIDを入力しるとして、シート1ではお示しのようにデータが有るとしたらF4セルには次の式を入力して下方にドラッグコピーします。
=IF(A4="","",IF(A4=Sheet2!A$1,MAX(F$3:F3)+1,""))
そこで抽出の結果をシート2の2行目は項目名として、A3セルには次の式を入力してD3セルまでドラッグコピーしたのちに下方にもドラッグコピーします。
=IF(ROW(A1)>MAX(Sheet1!$F:$F),"",INDEX(Sheet1!$B:$E,MATCH(ROW(A1),Sheet1!$F:$F,0),COLUMN(A1))*1)
文字列に変える手段は他にもいろいろあるでしょう。ただ、シート2で計算ができなかった問題は上記の方法で解決するでしょう。
KURUMITO 様
お礼が遅くなりました。この度はありがとうございました。
また何か質問する機会もあると思います。その時はまたよろしくお願いします。
No.5
- 回答日時:
今仮に、IDは数値データであり、Sheet2においてIDを入力するのがA2セルであり、Sheet2において「条件1」という項目名が入力されているのがA6セルであるものとします。
【方法その1】作業列を使用する方法です。
まず、適当な未使用シート(ここでは仮にSheet3とします)のA4セルに次の関数を入力して下さい。
=IF(AND(COUNTIF(Sheet1!$A:$A,Sheet2!$A$2),INDEX(Sheet1!$A:$A,ROW())=Sheet2!$A$2),COUNTIF(Sheet1!$A$3:INDEX(Sheet1!$A:$A,ROW()),Sheet2!$A$2),"")
そして、Sheet3のA4セルをコピーして、Sheet3のA5以下に貼り付けて下さい。
次に、Sheet2のA7セルに次の関数を入力して下さい。
=IF(ISERROR(1/(INDEX(Sheet1!B:B,MATCH(ROWS($7:7),Sheet3!$A:$A,0))<>"")),"",INDEX(Sheet1!B:B,MATCH(ROWS($7:7),Sheet3!$A:$A,0)))
次に、Sheet2のA7セルをコピーして、Sheet2のB7~D7の範囲に貼り付けて下さい。
次に、Sheet2のA7~D7の範囲をコピーして、同じ列の8行目以下に貼り付けて下さい。
【方法その2】作業列を使わずに、全自動で処理を行いますが、元データの表の行数が数千行にもなる場合には、処理が重くなってしまう方法です。
まず、Sheet2のA7セルに次の関数を入力して下さい。
=IF(OR(ROWS($7:7)>COUNTIF(Sheet1!$A:$A,$A$2),ISERROR(1/(INDEX(Sheet1!B:B,SUMPRODUCT(ROW(Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))*(Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A))=$A$2)*(COUNTIF(OFFSET(Sheet1!$A$3,,,ROW(Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))-ROW(Sheet1!$A$3)+1),$A$2)=ROWS($7:7))))<>""))),"",INDEX(Sheet1!B:B,SUMPRODUCT(ROW(Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))*(Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A))=$A$2)*(COUNTIF(OFFSET(Sheet1!$A$3,,,ROW(Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))-ROW(Sheet1!$A$3)+1),$A$2)=ROWS($7:7)))))
次に、Sheet2のA7セルをコピーして、Sheet2のB7~D7の範囲に貼り付けて下さい。
次に、Sheet2のA7~D7の範囲をコピーして、同じ列の8行目以下に貼り付けて下さい。
kagakusuki 様
この度は大変失礼しました。
またお礼が遅くなりました。この度はありがとうございました。
また何か質問する機会もあると思います。その時はまたよろしくお願いします。
No.3
- 回答日時:
>この「○」は実際は数字(1~3)のどれかに変わります。
ウソ情報に伴う典型的な二度手間ですね。
ではとりあえず簡単な方法として、方法1と2のどちらも、数式に「&””」と記入してあるこの3文字を「消去」してください。
2番目の方法ではCtrl+Shift+Enterを忘れず行ってください。
空白欄には数字の「ゼロ」が現れますので、数式を記入したセル範囲を選択
セルの書式設定の表示形式でユーザー定義を開始
#
と設定してOKしてください。
#合計の求め方
ふつーにSUM関数でもとめます
#平均の求め方
=SUM(範囲)/COUNTIF(範囲,">0")
と求めます
それとも。数式が簡単じゃなくてももう構わないからふつーに合計や平均を計算したいなら
方法1では、シート2のA4に
=IF(ROW(A1)>COUNTIF(Sheet1!B:B,$A$1),"",IF(VLOOKUP($A$1&TEXT(ROW(A1),"-000"),Sheet1!$A:$F,COLUMN(C1),FALSE)=0,"",VLOOKUP($A$1&TEXT(ROW(A1),"-000"),Sheet1!$A:$F,COLUMN(C1),FALSE)))
と記入、右にコピー、下にコピーします。
方法2はこれ以上複雑になるとどのみち応用も効かないので、諦めましょう。
keithin 様
この度は大変失礼しました。
またお礼が遅くなりました。この度はありがとうございました。
また何か質問する機会もあると思います。その時はまたよろしくお願いします。
No.2
- 回答日時:
こんばんは!
一例です。
データ量が多い場合はあまりオススメできませんが・・・
↓の画像でSheet2のA4セルに
=IF(COUNTIF(Sheet1!$A:$A,$A$1)<ROW(A1),"",INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1)))&"")
これは配列数式になってしまいますので、Shift+Ctrl+Enterキーで確定します。
これを列方向・行方向にオートフィルでコピーすると
画像のような感じになります。
※ この画面から数式をコピー&ペーストする場合は、セルに貼り付けただけでは配列数式になりませんので
貼り付け後数式バー内で一度クリック!
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
※ とりあえずSheet1の1000行まで対応できる数式にしていますが、
もっとデータがある場合は別に作業用の列を設けた方が良いと思います。
参考になりますかね?m(_ _)m
tom04 様
NO.1に回答された方と同じお礼の内容になってしまいますが、申し訳ありません。
ご回答、誠にありがとうございます。ただ、本当にすいません。こちらの説明不足でした。
sheet1の元データに「○」と書きましたが、この「○」は実際は数字(1~3)のどれかに変わります。
教えていただいたやり方できちんとIDごとにソート出来たのですが、それだとsheet2で合計関数や平均関数が使用できないようです。セルの中身が計算式だから??
ここの部分の認識が不足しておりましたので、せっかく教えていただいたのですが、今のままでは厳しいです。本当にすいません。
(上からコピーして値で張り付ければよいのですが、IDの数が数百ありますので、その都度教えていただいた関数を張り付けるのは大変ですので)
その他、何かよい方法はないでしょうか?
No.1
- 回答日時:
非常にしばしば寄せられるご相談の一種で、他の計算ソフトなら簡単にできるものもありますが、エクセルのVLOOKUP関数をはじめとする検索関数は、答えを「1つだけ」しか取ってこれないので、ご質問のようなことを簡単な関数でキレイに片づける方法はありません。
○簡単にVLOOKUP関数でやっつける方法(推奨)
シート1のB列にIDを記述、CからF列にデータを記入
1行目はタイトル行、2行目からデータ
A2に
=IF(B2="","",B2&TEXT(COUNTIF($B$2:B2,B2),"-000"))
と記入、以下コピー。
シート2のA1にIDを記入
シート2のA4に
=IF(ROW(A1)>COUNTIF(Sheet1!B:B,$A$1),"",VLOOKUP($A$1&TEXT(ROW(A1),"-000"),Sheet1!$A:$F,COLUMN(C1),FALSE)&"")
と記入、右にコピー、下にコピー。
○お勧めではない方法
シート1のA:E列に元データ
1行目はタイトル行、2行目からデータ
シート2のA1にIDを記入
シート2のA4に
=INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A:$A=$A$1,ROW(Sheet1!B:B),9999),ROW(A1)))&""
と記入し、必ずコントロールキーとシフトキーを押しながらEnterで入力
右にコピー、下にコピー。
keithin 様
ご回答、誠にありがとうございます。
すいません。こちらの説明不足でした。
sheet1の元データに「○」と書きましたが、この「○」は実際は数字(1~3)のどれかに変わります。
教えていただいたやり方できちんとIDごとにソート出来たのですが、それだとsheet2で合計関数や平均関数が使用できないようです。セルの中身が計算式だから??
ここの部分の認識が不足しておりましたので、せっかく教えていただいたのですが、今のままでは厳しいです。本当にすいません。
(上からコピーして値で張り付ければよいのですが、IDの数が数百ありますので、その都度教えていただいた関数を張り付けるのは大変ですので)
その他、何かよい方法はないでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- スーパー・コンビニ 「コンビニで エクセルをプリントアウト」することができますか? 8 2022/06/16 15:54
- Excel(エクセル) 【Excel質問】別シートにある複数の同型の表から、同じ行項目にある数字を集計する 4 2023/02/16 00:14
- Visual Basic(VBA) VBAでvlookup関数から、別シート参照するやり方・・・ 2 2022/11/14 18:49
- Visual Basic(VBA) vbaのvlookup関数エラー原因を教えていただけないでしょうか。 3 2022/04/25 16:16
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Excel(エクセル) エクセルカレンダーに予定表を反映したいです。 6 2022/09/30 14:39
- Visual Basic(VBA) このプログラムなんですがsheetにデータを置いて表示できるようにしてありますがsheetに101を 2 2023/02/23 20:13
- Visual Basic(VBA) 【変更】ファイルを閉じてダイアログで保存した時、更新したシートだけの処理の実行をする 5 2022/03/26 18:31
- Visual Basic(VBA) VBA For Each 〜 複数条件について 3 2022/10/20 20:05
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
Microsoft365の一部を解約したい
-
エクセルでXLOOKUP関数...
-
Officeの字体
-
office365って抵抗感ないですか?
-
マクロ1があります。 A1のセル...
-
理由を教えてください。
-
複数の写真を1枚に印刷
-
windows10で作業を終了する際の...
-
MicrosoftOffice2019なんですが、
-
MicrosoftOfficeの1ユーザー2...
-
1つのPCに「Excel 2010」「Exc...
-
Excel テーブル内の空白行の削除
-
マクロ自動コピペ 貼り付ける場...
-
会社のPCに入っているExcelでバ...
-
Microsoft 365 の一般法人向け...
-
Excel 日付を比較したら、同じ...
-
Excelで空白以外の値がある列の...
-
エクセルのシフト表を簡単にGoo...
-
office2016のパソコン2台インス...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
英数字のみ全角から半角に変換
-
Excelで空白以外の値がある列の...
-
会社PCのメールが更新されない
-
Excel 日付を比較したら、同じ...
-
マイクロソフト 一時使用コード...
-
ウィンドウィズ メモ帳で日付だ...
-
MicrosoftOfficeの1ユーザー2...
-
Microsoft Formsの「個人情報や...
-
Officeの字体
-
エクセルでXLOOKUP関数...
-
Microsoft365で自動保存が出来...
-
Outlookで、任意のメールアドレ...
-
outlookのメールが固まってしま...
-
Microsoft 365 の一般法人向け...
-
Office2021を別のPCにインスト...
-
Microsoft 365のディフェンダー...
-
Excelに貼ったリンクについて E...
-
MicrosoftOffice2019なんですが、
-
Outlook で宛先が複数の場合の人数
おすすめ情報