Excelで以下のようなことはできますか?
画像のSheet1の各表の、
「点数計」セル(B6,B12,B18)がゼロでない人の「名前」(B3,B9,B15)等のデータを、
Sheet2の表へ、上から詰めて自動的に表示させたいです。
自動的に、というのは、Sheet1の値が変わったときに自動でShhet2にも反映されるようにしたい、ということです。
他の質問を見ると配列数式?を用いていますが、
私のようにデータの入力されている行や列がバラバラでもできるのかと思い質問しました。
よろしくお願いします。
No.4ベストアンサー
- 回答日時:
続けてお邪魔します。
まず思い当たるのが「配列数式」になっていないのでは?
数式バー内を確認してみてください。
数式の前後に { } マークが入っていますか?ない場合は配列数式になっていないので
Ctrl+Shiftキーを押しながらEnterキーで確定してください。
これで配列数式になります。
※ 画像を拡大して気になる点が・・・
>MOD(ROW(A$1:A$1000),45)=29
となっていますよね。
というコトは一人当たり45行の表で、「点数計」の行は29・74・119・・・
と45行おきにある!となるのでしょうか?
そしてINDEX関数の「行」のところが「-33」となっていますが
万一最初の行(29行目)の「点数計」が0以外の場合、その33行上はないのでエラーになってしまいますね。
配列数式はPCに相当の負担を掛けます。極端に広い範囲を指定してしまうと
計算速度がかなり落ちるので当方が示した数式は1000行までで抑えています。
おそらく3000とか4000行までであれば大丈夫だとは思います。
ただ、画像の数式が正しくてそんなに行が飛んでいたり、データ数が極端に多い場合は
VBAの方が簡単のような気がします。m(_ _)m
データ量はたしかにとても多いです。
0になってしまう理由も分からないままですので、
VBAに挑戦してみようと思います。
とても丁寧な回答、ありがとうございました!
No.3
- 回答日時:
No.2です。
前回投稿した数式のA2セルの方の数式の説明ですね。
あくまで当方がアップした画像の配置での数式なので、
お手元の配置と異なれば数式も変わってきます。
まず、数式の大きな流れを説明します。
前回の数式
=IF(SUMPRODUCT((MOD(ROW(A$1:A$1000),6)=5)*(Sheet1!B$1:B$1000<>0))<ROW(A1),"",INDEX(Sheet1!B$1:B$1000,SMALL(IF((MOD(ROW(A$1:A$1000),6)=5)*(Sheet1!B$1:B$1000<>0),ROW(A$1:A$1000)-3),ROW(A1))))
を判りやすく
=IF(SUMPRODUCT((①*②)<ROW(A1),"",INDEX(Sheet1!B$1:B$1000,SMALL(IF(①*②,ROW(A$1:A$1000)-3),ROW(A1))))
としてみました。
前半の
>IF(SUMPRODUCT((①*②)<ROW(A1),""
の部分はエラー処理でB列「点数計」行のデータ数が数式を入れるセルの行数より少ない場合は空白に!
という意味です。
①*②の掛け算は 数式①かつ数式②が成り立つ「AND」条件になります。
① (MOD(ROW(A$1:A$1000),6)=5)
は
A1~A1000の行で「6」で割ったあまりが「5」の行番号 5・11・17・・・(←点数計の行)
が「TRUE」(=1)で他の行は「FALSE」(=0)です。
② (Sheet1!B$1:B$1000<>0)
B1~B1000 が「0以外」 となり
(SUMPRODUCT((MOD(ROW(A$1:A$1000),6)=5)*(Sheet1!B$1:B$1000<>0)) で
①×② → 「点数計」の行がTRUE、なおかつその行のB列が「0」以外のセル数が求められます。
>(MOD(ROW(A$1:A$1000),6)=5)
の部分は
>(MOD(ROW(Sheet1!A$1:A$1000),6)=5)
としても良いのですが、単に行番号の判定だけなので敢えてSheetは指定していません。
ただ、B列判定はSheetをちゃんと指定する必要があります。
前回の画像では「2」という結果になります。
数式を入れたセルを「<ROW(A1)」 「<1」になります。
それを下へフィル&コピーすれば 「<ROW(A2)」 <2 → 「<ROW(A3)」 <3 ・・・
IF関数で 該当セル数<ROW(A1),""
としていますので、3行目以降は空白になります。(該当データ数分の行数は表示される)
次にINDEX関数の
INDEX(Sheet1!B$1:B$1000
の部分は問題ないと思います。B列のどこの行を表示するか?だけです。
そのB列の
SMALL(IF(①*②,ROW(A$1:A$1000)-3),ROW(A1)
という配列数式で
(IF(①*②,ROW(A$1:A$1000)-3)
の部分は ①かつ② が「TRUE」か「FALSE」かを行ごとに判定し、
(画像の配置では5・17行目が「TRUE」です。)そのすべての行に行番号を掛け合わせます。
すなわち 5・17 行目だけが 「5」・「17」という結果が返り、他の行はすべて「0」になります。
(「FALSE」にいくら大きな数値を掛けても結果は「0」(FALSE)です)
※ この部分が配列数式なので、Ctrl+Shift+Enterの操作が必要になります。
最後の「ROW(A1)」の部分はSMALL関数の「順位」の部分になりますので、
最初の行はTRUEの行の「1番目」→ 5行目 次の行は「ROW(A2)」となり2番目 → 17行目・・・と順に表示させます。
ただ表示させたいのは「点数計」の行ではなくその3行上の「名前」の行なので「-3」としています。
以上長々と書きましたが
逆に判りにくくなりましたかね?m(_ _)m
No.2
- 回答日時:
こんばんは!
Excelのカテゴリの方が回答が付きやすいと思いますが・・・
↓の画像のような配置で元データはSheet1の1行目から始まっているとします。
Sheet2のA2セルに
=IF(SUMPRODUCT((MOD(ROW(A$1:A$1000),6)=5)*(Sheet1!B$1:B$1000<>0))<ROW(A1),"",INDEX(Sheet1!B$1:B$1000,SMALL(IF((MOD(ROW(A$1:A$1000),6)=5)*(Sheet1!B$1:B$1000<>0),ROW(A$1:A$1000)-3),ROW(A1))))
配列数式なので、Ctrl+Shift+Enterで確定!(←必須★)
B2セル(配列数式ではありません)に
=IF($A2="","",INDEX(Sheet1!$A:$F,MATCH($A2,Sheet1!$B:$B,0)+1,COLUMN(A1)*2))
という数式を入れフィルハンドルで右へD2セルまでコピー!
最後にA2~D2セルを範囲指定 → D2セルのフィルハンドルで下へコピーすると
画像のような感じになります。m(_ _)m
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VALORANTのロードが長いです
-
nasneの音
-
秋葉原でお勧めのパソコン専門...
-
USB式の顕微鏡「Dino-Lite P...
-
既存の用紙に文字を入力し、印...
-
アマゾンミュージックからウォ...
-
パソコンのへこみ
-
ダイソーなどで売られているエ...
-
bugsplatというソフトがパソコ...
-
FDDユニットの接続方法
-
パソコン周辺機器のハイテクマ...
-
パソコンの外付けHDD用のACアダ...
-
最近、USB-Cの規格がいろいろあ...
-
マイクロソフトアカウントについて
-
ベンチマークについて(Cineben...
-
モニターにあるdisplayportのメ...
-
自作パソコンにおける電動ドラ...
-
外付けのDVDプレーヤーについて
-
USB給電のブザー、サイレン、警...
-
Windows10でPT3を使いたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
VALORANTのロードが長いです
-
よろしくお願いします。よろし...
-
amzon fire tV stick の無限ル...
-
ヨドバシカメラって、誠実な店...
-
よろしくお願いします。 PCが壊...
-
パソコン購入のアドバイスください
-
複数のPCで同時に音が出せるス...
-
外付HDDにアクセスできなくなっ...
-
1つのUSBヘッドセットを複数のP...
-
タブレットのブルーライト問題
-
USBのMp3プレイヤー
-
既存の用紙に文字を入力し、印...
-
ポータブルテレビのHDDについて
-
どこの故障?デスクトップSFFタ...
-
ドスパラのお店って実際に店に...
-
google chromeのブックマークが...
-
3台のPCから同時に音を出せるマ...
-
ゲーミングチェアを使ってる方
-
nasneの音
-
VRのメタクエスト3を最近買いま...
おすすめ情報
よく分からずにカテゴリ設定してしまいました…
見つけていただきありがとうございます。
そしてこんな丁寧な回答をしていただきありがとうございます。
一つ目の数式を自分の表に当てはめてみましたが、
分からない部分が何カ所かあるので、教えていただけますでしょうか。
自分なりに考えた部分もあるのですが、どうでしょうか…。↓
とっっっても丁寧な回答、ありがとうございます。
自分の作成した表と見比べながらやってみました!!
…が、なぜか結果が「0」になってしまいます。
数式バー左横の関数ボタンでエラーをチェックしても、
画像のように「山田太郎」になっているのですが、結果は「0」です…
なぜなのでしょうか。
何度も何度も申し訳ありません。すごく急いでいるものではありません。
どうぞお手すきの際にご教示くださいませm( _ _ )m