Excel2003 セルA1の次はA4… 知恵を分けてくださいm(__)m

質問

朝から皆様の協力で一つ解決してはまた壁にぶつかっています…もう思考能力も限界です(^_^;)
◎入力シートがあります。
セルA1からD3に入力されるデータが一つのカテゴリです。
A列は各1列目(A1,A4,A7…)のみデータが入ります。
(B,C,D列は任意です)

◎帳票シートにデータをひっぱります。ここで２つの壁です。
１）表A列１行目には=IF(A1="","",A1)２行目は=IF(A4="","",A4)…と入力シートのA列に入っているデータを詰めて表示させます。ここで、数式をコピーすると、セル番が連番になりますよね？それをひとつづつ直しているのですが、最初のセル番+次のデータ行（一定行です。ここでは+3）という数式はできるでしょうか？OFFSETでは少し違うような気がします…。
２）「入力シート」A列が空白の時、データ（ここではA4からD6)をとばして→データを詰めて表示させたいのです。現時点の数式=IF(A1="","",A1)、ここから付け足していきたいのですが、もううまいこと考えられません(T.T)わかりづらい説明で申し訳ないのですが、ヒントいただければありがたいです。

回答No.3

shiotan99 · ベストアンサー

こんにちは～

No.1さんへの補足を見ても、まだ質問の意味がよくわからないのですが‥

壁その1）だけなら OFFSET関数とか INDIRECT関数を使えばできると思います。
例）
=INDIRECT("A"&ROW(A1)*3-2)
=OFFSET($A$1,ROW(A1)*3-3,0)
が、壁その2）は、壁その1）と別の内容ではありませんよね。

入力シートの A1が空白なら、
帳票シートの A1 ← 入力シートの A4の値
入力シートの A1 も A4 も空白なら、
帳票シートの A1 ← 入力シートの A7の値
・
・
というのが、壁その2）だと思うのですが‥

◎ A列の値はそうだとして、B列からD列の値はどこにどのように表示させるのでしょうか？
B列からD列の値も、どの行に入れるかは任意だけれど、ひとつのカテゴリにつき 1コしか入らないということですか？
↑がこの質問最大の謎です。

単に入力シートの A列の値だけを上から詰めて表示させるだけなら、
次のようにすればできます。

・入力シートが Sheet1
・帳票シートが Sheet2 だとします。

■作業列を使う方法です。
作業列はどこでもかまいませんが、ここでは仮に Sheet1 の F列を作業列とします。

★ Sheet1 の F1セルに《数式1》
=IF(A1="","",ROW())

と入力して、データ最終行まで下にフィルコピー

★ Sheet2 の A1セルに 《数式2》
=IF(COUNT(Sheet1!F:F)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!F:F,ROW(A1))))

と入れて、Sheet1 のカテゴリの数だけ下にフィルコピー
カテゴリの数が 50コなら、A50までフィルコピーです。

◆ 作業列を F列以外にする場合は、Sheet1!F:F（ 2ヶ所 ）をその列記号に変更のこと
◆ ROW(A1) は、数式を入れるセルが A1だから A1にしているわけではありません。
どのセルに数式を入れるにしても、A1のままにしておいてください。
（ 最初に数式を入れるセルだけです。あとはコピーするだけで OK ）

◆もし、B列から D列に入る値が各カテゴリにつき 1データしか入らないのなら、
上の 《数式1》を F1に入力後、I1（ ←"アイ" の 1です ）まで右にフィルコピーして、そのままデータ最終行まで下にフィルコピー

《数式2》を Sheet2 の A1セルに入力後、D1まで右にフィルコピーして、そのままカテゴリの数だけ下にフィルコピーしてみてください。

◆作業列が目障りなら非表示にしてください。

補足

ありがとうございます。
本当に説明不足ですね…（;´_`;）
「入力シート」に入っているデータは「帳票シート」の必要箇所に反映されます。データの計算結果や内容は「入力シート」で用が足りているのですが、出力時に「入力シート」のスタイルではダメなので単純に別シートに表をつくり、必要データをそこに反映させたかったのです。

たとえば…
「帳票シート」A1には「入力シート」A1のデータ
「帳票シート」B1には「入力シート」B3のデータ
「帳票シート」C1には「入力シート」D3のデータ

「帳票シート」A2には「入力シート」A4のデータ
「帳票シート」B2には「入力シート」B6のデータ
「帳票シート」C1には「入力シート」D6のデータ

…などという風に入りますが、
「入力シート」A4（のカテゴリ）にデータが無いときは、
「帳票シート」A2には「入力シート」A7のデータ
「帳票シート」B2には「入力シート」B9のデータ
「帳票シート」C1には「入力シート」D9のデータ
が反映されるようにしたいのです。

１）に関しては、「帳票シート」の関数をフィルコピーしてもセル番号がずれてしまうので、一個ずつ直すのは面倒だな…と思ったものですから…(^_^;)

ますますわかりづらい説明ですみません…。
こんな質問につきあっていただき、ありがとうございます。

回答No.6

shiotan99

No.3 ＆ No.5です。
訊き方が悪いせいか、どうもコチラが知りたいことが伝わらないようです。

>> B1と B3のように、1つの列に 2つ以上データが入ることはありえないのですか
> ありえます。
> このデータのセル位置は固定です。

ありえるんですか？ だったら No.3の方法ではうまくいきません。
「このデータ」 とは何ですか？ 帳票シートに反映させるデータ？
「固定」 ならそれは何行目？
↑↑がこの質問の最大のポイントです！！ ここをアイマイにされると、いつまでたってもすれ違いですよ！

もう一度お訊きします。
****************************************
Q1 ）帳票シートに反映させる B列から D列のデータは、
　No.4さんがおっしゃっているように、無条件に
　A1 のカテゴリ→ 3行目、A4 のカテゴリ→ 6行目、A7 のカテゴリ→ 9行目‥ですか？
要は、A列のデータの2行下、各カテゴリの最終行のデータ？

Q2 ）帳票シートの C列は、入力シートの D列のデータを表示させるのですか？
　入力シートの C列のデータは不要ということ？
*****************************************

■ 上記質問がいずれも YESだと仮定して、No.3 の回答を訂正します。
次のようにしてみてください。
★ 数式はコピー＆ペーストしてください。
（ 画面上の数式は見づらいので ）
********************
・入力シートが Sheet1
・帳票シートが Sheet2 だとします。
********************
★ Sheet1 の F1に（ 作業列です ）
=IF(A1="","",ROW())

と入れて、入力データ最終行まで下にフィルコピー

★ Sheet2 の A1に
=IF(COUNT(Sheet1!F:F)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!F:F,ROW(A1))))

◆ ↑の数式についての注意事項は、No.3を参照してください。

以下は、結局 No.4さんと同じような数式になりますが、
★ Sheet2 の B1に
=IF($A1="","",INDIRECT("Sheet1!B"&SMALL(Sheet1!$F:$F,ROW(A1))+2))

と入れ、右の C1にフィルコピーして、
"Sheet1!B" → "Sheet1!D" に変更（ ←コレ以外はそのまま ）
★ Sheet2 の C1は
=IF($A1="","",INDIRECT("Sheet1!D"&SMALL(Sheet1!$F:$F,ROW(B1))+2))

になれば OK。

◆ A1：C1 の数式を入力シートのカテゴリの数だけ下にフィルコピー
カテゴリの数が 50コなら、50行目までフィルコピー

◆ 数式を『解読』されるのはけっこうなことだと思いますが、とりあえず試してみて希望どおりの結果になるかどうかを確認してください。（ ものの 5分もかかりません ）『解読』するのはそのあとにしましょう。

◆ 何度も同じことを言うようで恐縮ですが、上記方法でうまくいかない場合、どううまくいかなかったのかをできるだけ具体的に補足してください。

お礼

本当にお世話をかけてすみません。読み返してみても私の質問があいまいすぎて、shiotan99様とodessa7様にはずいぶんご迷惑をおかけしたと反省しています…。お二人の解説をテストシート上でためしてみると私の求めているものにすごく近いのですが、ここで「できた、できた」だけでは、実際のシートにのせたたときにできなかった時、「ここがこうならなかった」とすぐに補足することもできないと思うのです。

（…また説明不足でした…）「入力シート」も「帳票シート」も碁盤の目の表ではなく、「入力シート」においては、「帳票シート」には必要ないが、「帳票シート」にのせるデータを導き出すための計算や数値、文字が入っていたり、セルの結合されていたりしています。なので、質問欄で図解をするのが困難でした。
説明を省略（たとえがわかりづらかったですね…）しすぎてしまい、申し訳ありませんでした。

実は、shiotan99様の最初の回答(No.3)は、理解しきれていないまま、odessa7様の回答(No.4)が理解しやすかったのでそちらからとりかかっていたのですが、(No.5)でのつっこみで改めて自分の考えを整理しました。疑問を投げかけられたことで、自分の中でも一つずつ整理ができました。
朝から作業をして、結論は、No.3の回答を使わせていただき、理解したうえで応用もさせていただきました。
「解決」しました。本当にすれ違いのままでshirotan99様とodessa7様には不完全燃焼の思いをさせてしまうと思うと心苦しいのですが（;´_`;）これまでの回答が解決に導いてくれたことは紛れもありませんので、とても感謝しています。以後、質問をするときは気をつけたいと思います。ありがとうございました。

回答No.5

shiotan99

No.3です。
補足を見ましたが、謎は深まるばかりです。

まず、No.3の方法は試していただいたのでしょうか？
補足に書いてあるようなことなら、No.3の方法でできると思うのですが‥
No.3の下の方に、A列から D列のデータを反映させるやり方を書いておいたのですが最後まで読んでいただけましたか？

No.4さんのように #NUMエラーが出ることもありません。
それに、No.4さんのだと
B列から D列の値は
A1 のカテゴリは 3行目
A4 のカテゴリは 6行目
A7 のカテゴリは 9行目、のように固定になっていますがそれでいいのですか？

No.4さんのやり方でできたと思われたのなら、No.3の方法で何の問題もないはずですが‥
◆ もしNo.3の方法を試された上で No.3の補足を書かれたのなら、なにがどうダメだったのかを具体的に説明してください。

■ No.3でお訊きしたかったのは下記のようなことです。
入力シートの B列から D列のデータについて
1 ）各カテゴリには各列に 1つのデータしか入らないのですか。
　B1 と B3 のように、1つの列に 2つ以上データが入ることはありえないのですか。

2 ）A列が空白ではないカテゴリでは、B列から D列には必ずデータが入るのですか。 　B列にはデータが入るが D列には入らない、ということはないのですか。

3 ）A列が空白でも、B列から D列にデータが入っている場合もあるのですか。

4 ）上記すべてに関連しますが、最初の質問にある
> (B,C,D列は任意です)
この 「任意」とはどういう意味ですか。
どの行に入力するかが任意なのか、入力するかしないかが任意なのか。

5 ）No.3の補足に
>「帳票シート」C1には「入力シート」D3のデータ
>「帳票シート」C1には「入力シート」D6のデータ

‥とありますが、2つめの C1は、単に Ｃ2のまちがいだとして、
帳票シートの C列に、入力シートの D列の値を表示させるのですか。
（ 入力シートの C列のデータは反映させない、ということ？ ）

◆ 上の 5 ）が YESなら、
No.3の 《数式1》は F1セルに入力後、H1までフィルコピー
H1の値は、=IF(C1="","",ROW()) となるので
これを
=IF(D1="","",ROW())
と書き換え、F1：H1 の数式をデータ最終行までフィルコピー

《数式2》も同じく、A1セルに入力後、C1セルまで右にフィルコピー
C1の数式を
=IF(COUNT(Sheet1!H:H)<ROW(C1),"",INDEX(Sheet1!D:D,SMALL(Sheet1!H:H,ROW(C1))))

と書き換え、（ Sheet1!C:C を Sheet1!D:Dに。←コレのみ変更 ）
A1：C1の数式を、カテゴリの数だけ下にフィルコピーしてください。

◆ くり返しますが、うまくいかない場合、どううまくいかなったのかを具体的に書いてください。

補足

申し訳ないです。昨日の夜の時点で、No.３様の式を解読しようとしていました。（使ったことのない関数ばかりだったので…）補足は不明点の回答をしようと、解読前に書いたものです。そして、今日に至るのですが、No.４様の方法も試してみて、お二方の言われていることがちょっとわかりつつありました。説明がうまくできないので、本当にもうしわけないのですが…
>入力シートの B列から D列のデータについて
>1 ）各カテゴリには各列に 1つのデータしか入らない>のですか。
>　B1 と B3 のように、1つの列に 2つ以上データが入>ることはありえないのですか

ありえます。入力シートの一つのカテゴリの中B列以降は外の計算結果や、文字列なども入っていて、その中で必要なデータを引っ張っています。このデータのセル位置は固定です。

>2 ）A列が空白ではないカテゴリでは、B列から D列に>は必ずデータが入るのですか。 　B列にはデータが入>るが D列には入らない、ということはないのですか。
>3 ）A列が空白でも、B列から D列にデータが入ってい>る場合もあるのですか。

「入力シート」ここはここで、A列が空白の時はB列以降を表示させないようにしているのでデータは表示されません。
…このシートに反映されるデータは、また別のシートにあったりします(__;)

>4 ）上記すべてに関連しますが、最初の質問にある
>> (B,C,D列は任意です)
>この 「任意」とはどういう意味ですか。
>どの行に入力するかが任意なのか、入力するかしない>かが任意なのか。

変な書き方をしてしまってすみませんm(__)m
（碁盤の目の表）ではないと言うことを言いたかったのです…
よって、5)…「入力シート」と「帳票シート」の同じセルが反映されるわけではないのです。

また、混乱させてしまっているかもしれないのですが…
本当に申し訳ないのですが、今日はもうこの問題に手をつけられないために、明日じっくり検証してお伝えしたいと思いますm(__)m

回答No.4

odessa7

補足の文章と併せて、少し理解できました。・・と思います。^_^;

　Ａ｜Ｂ｜Ｃ｜Ｄ｜
１○　○　○　○　
２ 　　○　○　○
３ 　　○　○　○
４ 　　
５ 　　
６ 　　
７×　×　×　×

　上図の場合において、帳票シートに

１○
２×

　と表示すると言うことですね？

　その場合、帳票シートのＡ列にまず、下記の関数を入れてください。
=IF(入力!A1="","",ROW(入力!A1))　
　これを下までコピー＆ペースト。
　※入力シートのシート名を「入力」としています。

　次に帳票シートのＢ列に
=INDIRECT("入力!A"&SMALL($A$1:$A$7,ROW()))　
　これを下までコピー＆ペースト。
　※「$A$1:$A$7」の範囲は、必要なだけ広げてください。

　これでＢ列にお求めの数値（入力シートＡ列の空白を無視した数値）が表示されるはずです。

　入力シートＢ列の数値は、A1ならB3、A7ならB9と、2だけ足したセル番地ですから
=INDIRECT("入力!B"&SMALL($A$1:$A$10,ROW())+2)

　同様にC列は
=INDIRECT("入力!D"&SMALL($A$1:$A$10,ROW())+2)

　間違っていれば再質問してください。

お礼

私の不完全な質問内容を多少なりとも理解していただき、ありがとうございました。結果は、No.3様の回答を使わせて頂きましたが、こちらの回答が理解しやすく、解決の糸口になりました。回答を下さったお二人には本当に感謝しています。以降は聞きたいことをきちんとまとめてから質問したいと思います。感謝です。ありがとうございました。お二人に同ポイントをつけさせて頂きたいのですが、そうもいかないので、ご了承ください。

補足

ありがとうございます。こんな説明でお時間とらせてすみません(;´_`;)
新規シートにテストしてみたら、素敵な結果に！
でも、実際シートに入れてみたら、データが反映されるセルが#NUM!と表示されてしまいます…。
使ったことのない関数ばかりなので、まず式を理解するところからです…しぶとく、質問は締め切りにしないまま(^^ゞもう少し悩んでみます。
お世話かけましたm(__)m

回答No.2

odessa7

ご質問文をよく読み返して気づいたのですが、
=IF(A1="","",A1)
　も、
=A1
　も同じ意味ですよね？

　なので私が書いた式も
=INDIRECT("A"&(ROW(B1)-1)*3+1)
　だけでも、全く同じ値が返ります。すいませんでした。

　・・と、ここまで書いて気付いたんですが、lippi1977さんは下記のようなことをしたいのでしょうか？

　Ａ｜Ｂ｜Ｃ｜Ｄ｜Ｅ
１○　○　○　○　
２ 　　○　○　○
３ 　　○　○　○
４ 　　△　△　△
５ 　　△　△　△
６ 　　△　△　△
７×　×　×　×

　この場合Ｅ列に
　Ｅ
１○
２×

　と表示する・・・これであってますか？

補足

データがある時だけ表示させたいのでifをつかってました。

表の方は、この表を…E列ではなく、別のシートに反映させたいのでした。

度々ありがとうございます。

回答No.1

odessa7

とりあえず１）の回答を。
　INDIRECT関数を使います。

=IF(INDIRECT("A"&(ROW(A1)-1)*3+1)="","",INDIRECT("A"&(ROW(A1)-1)*3+1))

　この式をコピーして貼り付けます。+3ずつのセルの値が返るはずです。

　２）については、ご質問の意味がよくわかりません。

　それと、「行」と「列」をごちゃ混ぜにして記述しておられるようです。例えば、ご質問文の４行目は「A列は各1"行"目」の間違いではないでしょうか？見直してみてください。

　あと出来れば、「どういう表の」「どの値を」「どうしたいのか？」を、簡単な図解で示していただけると解答もつくかと思います。
　補足方、よろしくお願いいたします。

補足

ありがとうございます。
ご指摘のとおり「１行目」です。失礼しました。
２）については、言葉足らずでした…(__;)
「入力シート」の入力箇所は固定されていて、
たとえば
A1　に100.00というポイント（地点…タイトルにあたります）を入力し、それに関するデータをB1～D3に入力。
これで100.00という地点に関するデータ一式です
同じように、
A4 200.00
A7 300.00…とデータを入力していきますが、今回、200.00に関するデータが無いため入力しない（A4も空白）→でも300.00のデータはA7から入力したい…ということなのです。「入力シート」はそのように入力しますが、「帳票シート」では「入力シート」のデータを=A1で単純にひっぱっているので、２行目は空白になる←これではまずいので、「入力シート」の空白を無視して、次の300.00のデータを詰めて表示させたいのです。
ちょっと図解が難しいので、また文での説明でとってもわかりずらいかとはおもいますが…。