sheet1のデータを参照して、空白を詰めてsheet2へ表示させたいんです!
sheet1(元データになるもの)
A列 |B列|C列・・・
1行目A|100|100
2行目B| |200
3行目C|100|
4行目D| |200
5行目E|100|
6行目F| |100
・
・
sheet2(sheet1でB列に入力があるものを抽出)
A列 |B列
1行目A|100
2行目C|100
3行目E|100
sheet3(sheet1でC列に入力があるものを抽出)
A列 |B列
1行目A|100
2行目B|200
3行目D|200
4行目F|100
sheet2のA1に下記の式を入力してA列とB列に数式をコピー
=IF(COUNT(Sheet1!$B$1:$B$6)<ROW(A1),"",INDEX(Sheet1!A$1:A$6,SMALL(IF(Sheet1!$B$1:$B$6<>"",ROW($A$1:$A$6)),ROW(A1))))
すると下記のように表示されます。
A1=A B1=100
A2=#NUM! B2=#NUM!
A3=#NUM! B3=#NUM!
4行目から空白
sheet2のA2のところにエラーが出ていますが、「関数の引数」のところで「数式の結果」には「100」と
正解が表示されています。(B2、A3、B3も同様に)数式の結果のところには正解が表示されています。
答えの「#NUM!」のところに正解を表示させるには、どうしたらいいですか?
見よう見まねでつくったのもで。。。関数にあまり詳しくありません。
よろしくお願い致します。
No.2ベストアンサー
- 回答日時:
No.1です!
たびたびごめんなさい。
投稿した後で質問をもう一度読み返して見ました。
数式は合っていると思いますよ。
ただ、配列数式になっていないのでエラーになっているだけみたいですね。
そのままの数式で
Shift+Ctrl+Enterキーで確定してみてください。
ちゃんと表示されるはずです。
ということは・・・
前回の私の回答は余計なお世話になってしまいました。
どうも何度も失礼しました。m(__)m
Shift+Ctrl+Enterキーで解決できました!
数日間悩んで、いろんなサイトを見て解決できずにいたので助かりました!
ありがとうございます。
No.3
- 回答日時:
計算速度の点で質問のような数式は好ましくありません。
が、配列数式は面白いので ちょっとお遊び。
好ましいし数式ではありませんが、計算速度は多少改善されます。
Sheet3!A1:B1セルを選択して
=IF(COUNT(Sheet1!$C$1:$C$6)<ROW(A1),"",
INDEX(Sheet1!A$1:C$6,
SMALL(IF(Sheet1!$C$1:$C$6<>"",ROW($A$1:$A$6)),
ROW(A1)),
{1,3}))
[Ctrl]+[Shift] +[Enter] で確定({}で囲まれる)
下へオートフィル
ちなみに、オートフィルタで代替できないのでしょうか?
Sheet1にデータが順次追加されていくので、オートフィルタはあまりつかえないのです。。。
[Ctrl]+[Shift] +[Enter] でできました!
ありがとうございました。
No.1
- 回答日時:
こんばんは!
色々方法はあると思いますが・・・
一例です。
↓の画像で説明させていただきます。
Sheet2のみの回答ですが、Sheet3も数式を少しアレンジすれば同様にできます。
尚、A列は配列数式になりますので
もし、この画面から数式をコピー&ペーストしただけではエラーになると思います。
貼り付けた後にF2キーを押すか、数式バー内を一度クリックします。
そうすると編集可能になりますので
Shift+Ctrl+Enterキーを押してみてください。
数式の前後に{ }マークが入り配列数式になります。
(ご自分で数式を作られるときは最後がOKではなく、Shift+Ctrl+Enterです)
Sheet2のA1セルに
=IF(COUNTA(Sheet1!$B$1:$B$100)>=ROW(A1),INDEX(Sheet1!$A$1:$A$100,SMALL(IF(Sheet1!$B$1:$B$100<>"",ROW($A$1:$A$100)),ROW(A1))),"")
B1セルに(こちらは配列数式ではありません)
=IF(A1="","",VLOOKUP(A1,Sheet1!$A$1:$C$100,2,0))
という数式をいれ、A1・B1セルを範囲指定し、B1セルのフィルハンドルで下へコピーすると
画像のような感じになります。
尚、余計なお世話かもしれませんが、
今回はタイトル行がないのでこのような数式になりましたが、
通常1行目はタイトル行があるかと思います。
その場合のA2セルの数式は
=IF(COUNTA(Sheet1!$B$2:$B$100)>=ROW(A1),INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$B$2:$B$100<>"",ROW($A$1:$A$99)),ROW(A1))),"")
(配列数式)
B2セルは
=IF(A2="","",VLOOKUP(A2,Sheet1!$A$2:$C$100,2,0))
となります。
数式は100行まで対応できるようにしていますが、
範囲指定の領域はデータ量によってアレンジしてみてください。
以上、参考になれば幸いですが、
他によい方法があれば読み流してくださいね。m(__)m
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Sheet「状況」から、分類の年齢別カウント数をSheet「D表」へ転記する下記マクロを作っています 7 2022/12/14 17:57
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
- C言語・C++・C# C言語初心者 ポインタについて、お助けください、、 2 2023/03/15 23:50
- Excel(エクセル) EXCEL 行内のデータを2行に分けて、表を作り直したいのです。教えてください。 5 2023/06/25 14:00
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- Excel(エクセル) エクセルでのコピーペースト 6 2022/09/03 07:14
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
Excel 複数のデータを別シートに上から詰めて表示させたい
Excel(エクセル)
-
マクロで空白セルを詰めて別シートに転記
Visual Basic(VBA)
-
別シートに空白セルを詰めデータを自動コピー
Excel(エクセル)
-
-
4
エクセル 空白セルを詰めたい
その他(Microsoft Office)
-
5
excelで、空白を除いてデータを抽出する方法について
Excel(エクセル)
-
6
EXCELで、空白セルを除いて別シートに転記する方法
Excel(エクセル)
-
7
特定のセルが空白だったら、その行を非表示にしたい。。。
Visual Basic(VBA)
-
8
リンク元の日付が空白の時リンク先セルも空白にしたい
Excel(エクセル)
-
9
数式による空白を無視して最終行を取得するマクロ
Excel(エクセル)
-
10
エクセルで条件に一致したセルの隣のセルを取得したい
その他(Microsoft Office)
-
11
エクセル関数 空白(関数あり)の列を削除し、左に詰めたい
Excel(エクセル)
-
12
エクセルで入力シートから別シートに蓄積方法について
Excel(エクセル)
-
13
VBA 空白行に転記する
Visual Basic(VBA)
-
14
表内、縦位置の中央揃えがど~~してもできない!!
Word(ワード)
-
15
空白セルをつめる。
Excel(エクセル)
-
16
(Excel)あるセルに文字を入力しただけで、同じブック内のほかのワークシートにも、同じ文字が自動的に入るようにするには?
Excel(エクセル)
-
17
別のシートから値を取得するとき
Visual Basic(VBA)
-
18
あるセルに特定の文字列を打つと、他のセルに決められた文字が自動入力するように
Excel(エクセル)
-
19
空白セルを飛ばして転記したい
Access(アクセス)
-
20
EXCELの条件付き書式で数式を空白と認識してくれる方法
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
会社PCのメールが更新されない
-
英数字のみ全角から半角に変換
-
【Microsoft Forms】回答を削除...
-
VLOOKUP関数について
-
Microsoft Formsの「個人情報や...
-
【スプレッドシート】指定の日...
-
outlookのメールが固まってしま...
-
vb.net オブジェクト指向につい...
-
Outlook で宛先が複数の場合の人数
-
【スプレッドシート】白色のセ...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルでXLOOKUP関数...
-
Microsoft Formsで「応答」から...
-
Office 2021 Professional Plus...
-
エクセルのシフト表を簡単にGoo...
-
Microsoft Edgeの「ニュースと...
-
Microsoft Officeに似たキング...
-
Excel VBA 日程表からスケジュ...
マンスリーランキングこのカテゴリの人気マンスリー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
-
自分の専門分野の仕事。初見で...
おすすめ情報