エクセルの項目の増加に対する式の展開、修正がが分かりません

質問

先日、セルの並び替えを下記のようにしたく、掲示板に質問した者ですが、良いアドバイスを頂戴し、目的をみたせたのですが、
A 　　　 B 　　 C 　　　　 D
順　　　モデル名　　番号　　数量
１　　　ｘ－１０　　　　３２　　１００
２　　　ｙ－１３　　　　１９　　２００
３　　　ｚ－２５　　　　３６　　２８５
４　　　ｗ－６５　　　　４３　　１５５と言うような規則でデーターが横に並んでいるのですが
　　　　　 A 　　　　 B
これを　　順　　　　１
　　　　モデル名　ｘ－１０
　　　　番号　　　　３２
　　　　数量　　　　１００

　　　　　順　　　　　２
　　　　モデル名　ｙ－１３
　　　　番号　　　　１９
　　　　数量　　　　２００に配置替えするべく、ある方から
=INDIRECT("sheet1!R" & CEILING(ROW()/4,1) & "C" & MOD(ROW()-1,4)+1,FALSE)
の式を入れたら実現できました。
　それは良かったのですが、私にはこの式の成り立ち、理屈が今一、理解できていません。

　シート１のセルを間接的に参照し、シート２に配置を換え表示するのは分かりますが、Ｒ”とＣ”の符号、CELING以降の計算式が何故こうなるのか分かりません。

　この式でA列、B列に縦表示できたのですが、E～H列に同個目がA～Ｄ列と同じ並びで増加し、表示しようとC,D列に式をコピーしましたが、A,B列の値しか返りません。

　どう式を調整、修正するとA,B列と同じ配置でC,D列に値を返せるか方法が見つかりません。

　既に質問を締め切ったので回答者さんに聞けず、再度、お願い致します。

回答No.2

neKo_deux · ベストアンサー

> 式を切り取るとエラーが表示され、

切り取り方が良くないんだと思います。

INDIRECTのヘルプを見ると、
INDIRECT(参照文字列,参照形式)
となっています。

前の式を見ると、

=INDIRECT(【"sheet1!R" & CEILING(ROW()/4,1) & "C" & MOD(ROW()-1,4)+1】,【FALSE】)

と、

参照文字列が前半の【】で、参照形式が後半の【】だなとバラして行きます。

まずは知っている関数のヘルプを熟読して、ヘルプの読み方あたりから確認してはどうでしょうか？

補足

INDIRECTは少しは使ったことがあるのですが、この式で言う"R"と"C"の関係、同E～H列をC,D列にA,B列同様の縦表示、列がAB→CDに移動した場合の表示形式、式の数値の加減を先ほどから色々やっていますが、結論が見出せずにいます。

　私のスキルでは無理なのかも知れません。

回答No.3

imogasi

（１）大原則
エクセルの関数は
A.あなた（＝あるセルの値）はあそこ（＝どこそこのセル）へ行きなさい
（＝値をセット）と指示するのではなく！
B。データを受けるセル側で、あの人（＝あのセルの値）を私のところにください。そして式を指定して、処理や演算します。
という方式で考えないといけません。（ちなみにプログラムはA.ができます。）
（２）本問では、実例の後半の
　　　　モデル名　ｘ－１０
　　　　番号　　　　３２
　　　　数量　　　　１００
　　　　モデル名　ｙ－１３
　　　　番号　　　　１９
　　　　数量　　　　２００
のA1:B6のセルの１つ1つの（セルの）側に、身（思考ポイント）をおいて、前半（Sheet1）のどのセルのデータが来てほしいのか考えないといけません。
（３）そこには、人間のように好きにあの人とこの人と印象で選べるのでなく、「（エクセルの）関数式にできる」「規則性」がなければ、式を複写するときに破綻します。
（４）その「規則性」を式にすることが、エクセルの関数式を見つける主要課題です。
（５）さて本文では、A列は３個の文字列の繰り返し複写でh解決できるので
（６）B1、B2、B3・・・がどうなるかを考える必要があります。
（７）Sheet1をA、Sheet2をBとします。
　　　セルについて、第i列の第ｊ列のセルを（i、ｊ）で表すと
　　　B第１行　　Aの（１，１）
　　　B第２行　　Aの（１，２）
　　　B第３行　　Aの（１，３）
　　　B第４行　　Aの（２，１）
　　　B第５行　　Aの（２，２）
　　　B第６行　　ABの（２，３）
　　この中から規則性を考えます。
たとえば、Bの立場で第５行の場合、Aの２行目の２列目のデータを持ってきますが、これは５を３で割った答え（商）１に１を足したもの、列は５を３で割った余りです。ただし行数字にー１したものを対象にし、後で＋１すると言う定石がありますが。余りはMOD関数を使います。商はINT関数で求められます。
データがSheet1のA1:C4に
ｘ－１０ 32 100
ｙ－１３ 19 200
ｚ－２５ 36 285
ｗ－６５ 43 155
とあり（見出しは省略しました）
Sheet2のB1に
=INDEX(Sheet1!$A$1:$C$4,INT((ROW()-1)/3)+1,MOD(ROW()-1,3)+1)
と入れてB１２まで式を複写して
ｘ－１０
32
100
ｙ－１３
19
200
ｚ－２５
36
285
ｗ－６５
43
155
となりました。
（８）（１）－（６）の原理的な点を別にすると、
INT関数
MOD関数
INDEX関数
INT((ROW()-1)/3)+1,MOD(ROW()-1,3)+1の式
上記式で－１し＋１するコツ。
などの経験・知識が必要です。
私にとっては上記の式が最大課題ですが、INDEX関数を知らないであれば
式も役立ちませんが。
前質問の回答のINDIRECT関数を使わず、行の１－４は略したりしてますが、
「考えるプロセス」をできるだけ詳しく説明してみました。私の回答の中でも一番詳しい？です。
ぜひじっくり考えて、私の言わんとすることを分かっていただくとうれしいです。

お礼

実に丁寧、詳細な解説をいただき、有難うございます。私は仕事上でエクセルを使用することが多いのですが、『自分でこうできるといいなぁ』と思い、やり始めました。

　関数も時々、使いますが、検索/行列、文字列操作等比較的簡単なものばかりです。VLOOKUP,HLOOKUPは使えるのですが、INDEX,OFFSETは苦手で避けていました。どうも複雑な思考が苦手で出来ることが限られています。

　今回の一連の質問でアドバイスしていただいて感謝していますが、目的を果たせても、その記述式が理解できないのでは応用が効きません。

　すべての関数をマスターできるに越したことはないのですが、本やネットで調べて我流で覚えました。

　基本を飛ばして応用に進もうとする傾向があり、見慣れない関数で右往左往しています。回答いただいたとおりにやりましたが、エラーが表示され、私の力不足で思う通りに行きませんでした。

もう少し基本に返ってやり直そうと思います。私のような者の質問に貴重な時間を割いていただいたこと、有難うございました。

回答No.1

neKo_deux

> Ｒ”とＣ”の符号、

正確には、
"sheet1!R"
"C"
の符号ですね。

INDIRECT関数をヘルプで調べてみてください。

&は文字列の結合の演算子です。
"A"&"B"
で、
"AB"
という文字列が得られます。

--
> 何故こうなるのか分かりません。

隣のセルなどに、

INDIRECTを外した計算式：
="sheet1!R" & CEILING(ROW()/4,1) & "C" & MOD(ROW()-1,4)+1

最初の"sheet1!R"の文字列を外した式：
=CEILING(ROW()/4,1) & "C" & MOD(ROW()-1,4)+1

CEILINGの部分の計算結果：
=CEILING(ROW()/4,1)

などと、式の項を抜き出して並べてみると、理解の助けになると思います。

補足

回答有難うございます。式を一つ一つ切り離して検証しているところです。

　この式に記述されている関数は今まで全く使ったことがないので、定義から調べていますが、スンナリ理解できません。

　式を切り取るとエラーが表示され、ますます混乱します。エクセルを始めて日が浅いもので、この手の関数はパズルを解くような気がして、気が重いです。

　自分が作ってない（作れるレベルでない）式なので中々、頭に入りません。