エクセル縦データをズレが無く横並びに

質問

エクセルバージョンは、2002です。

A列に毎日処理するデータが1000行程あります。

構成としまして、基準となるデータから10行が1つのデータの構成で、途中から1行減って9行の構成に変化します。

途中から9行の構成部分から、10個目の部分に基準となる先頭部分が格納されて、構成データがズレてしまいます。

ズレるイメージ下記の通りです。

縦に並んでいるデータ(データは、文字列や数字です。)

1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9

このデータを各列のセル毎に格納

1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 1　→　ズレて10の部分に1が格納
2 3 4 5 6 7 8 9 1 2　→　ズレて9と10の部分に1と2が格納

上記に組んである数式は、下記の通りです。

=INDEX($A$2:$A$1001,ROW(B2)*10+COLUMN(B2)-11)

この式をB列からK列迄コピーして、必要な行数迄コピーしています。

このズレが無い様に表示させたい表示は

1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8 9

このズレが出来ない方法をご教授頂ければ幸いです。

宜しくお願い致します。

回答No.9

bunjii · ベストアンサー

再提示された模擬データでは下記の数式で目的の処理が出来ると思います。
L列は作業用です。
L2=LARGE(INDEX((LEFT(A$2:A$1001,1)="P")*ROW(A$2:A$1001)+(LEFT(A$2:A$1001,1)="C")*ROW(A$2:A$1001),0),SUMPRODUCT((LEFT(A$2:A$1001,1)="P")+(LEFT(A$2:A$1001,1)="C"))-ROW()+2)
L2セルを下へ必要数コピーします。
B2=INDEX(OFFSET($A$1,$L2-1,0,10),COLUMN(B2)-1)
B2セルをJ2セルまでコピーします。
K2=IF(OR(LEFT(INDEX(OFFSET($A$1,$L2-1,0,10),COLUMN(K2)-1),1)="P",LEFT(INDEX(OFFSET($A$1,$L2-1,0,10),COLUMN(K2)-1),1)="C"),"",INDEX(OFFSET($A$1,$L2-1,0,10),COLUMN(K2)-1))&""
B2からK2セルを選択して下へコピーしてください。
尚、データの中でシリアル値の列については表示書式を目的に合わせて修正してください。

お礼

ご対応、ご教授頂き有難うございます。

目的通りに表示する事が出来ました。

今後質問する時がありましたら、もっと分かり易く掲示していく事にします。

回答No.8

bunjii

>判断出来るものとして、文字列の左端に必ず、pとcいずれかの文字を含んでいます。
A列の模擬データを再提示してください。
P1
2
3
4
5
6
7
8
9
10
C1
2
3
4
5
6
7
8
9
のようになるのか？
P1
P2
P3
P4
P5
P6
P7
P8
P9
P10
C1
C2
C3
C4
C5
C6
C7
C8
C9
のようになるのでしょうか?
尚、Pの場合が10行1組でCの場合が9行1組と言う関係でしょうか？
条件によって最適な数式が変わります。
質問の内容が情報不足で回答者泣かせのように思われます。

補足

言葉足らず多く、大変申し訳ありません。

10行データと9行データの参考データが以下の通りになります。

10行データ

P製品1
-4
4000円
0.29 (7.14%)
製造中
16,500円
2016/9/14
4100円
0.31 (8.05%)
--

C製品1
-4
3000円
0.39 (7.14%)
製造停止中
16,900円
2016/10/14
4150円
0.201 (8.05%)
--

9行データ

P製品1
-4
4000円
0.29 (7.14%)
製造中
16,500円
2016/9/14
4100円
0.31 (8.05%)

C製品1
-4
3000円
0.39 (7.14%)
製造停止中
16,900円
2016/10/14
4150円
0.201 (8.05%)

回答No.7

msMike

[No.6補足]へおコメント、
私がヤルと、添付図のようになります。
ただし、セル A1 から下方に
1、2、3、4、5、6、7、8、9、10、11、12、…、19、21、22、…、29、31、32、…、39、41、42、…、49、51、52、…、59、61、62、…、69、71、72、…、79、81、82、…、89、91、92、…、99、101、102、…、109
を入力した結果を示しています。
何処か齟齬がありますか？

》 上記の式を組んでみましたら、…
「組んでみました」が気になります。自分で“組まない”で、私のメッセージ中の式をそっくりコピーして、Excel に貼り付けたら如何？セルアドレスも変更せずに！「何も足さない、何も引かない」ですよ。

回答No.6

msMike

[No.4補足]へのコメント、
》 10行のデータでしたら、以下の式で…
》　9行のデータの場合は、以下の式…
何を仰りたいのか？
私が提示した式では不都合があったのでしょうか？ＹＥＳなら、具体的に何処が貴方の意図するところではなかったのかをご指摘ください。

》 エクセルには、or条件は無いかと思いますので…
なぜそう考えるのですか？

》 10行用のデータと9行用のデータを…する事は可能でしょうか？
繰り返しになるけど、私の提示式は貴方の前回の[補足]である「10行のデータの後に9行のデータが並んでいる状態」を満足しているか否かを述べてください。

つまり、貴方の今回の[補足]はサッパリポンです。

補足

言葉足らず失礼致しました。

=IF(COLUMN(A1)>10-(ROW()>1),"",OFFSET($A$11,+(ROW(A1)-2)*9-(ROW()=1)+COLUMN(A1)-1,))

上記の式を組んでみましたら、10行のデータの1行目で1番右端に10行目のデータが表示されるかと思い、その部分には空白のままとなり、2行目の基準になる左端に表示されます。
基準になるデータが、1行目では1番左側で問題ないのですが、2行目から1つずつ右側に移動して表示していきます。

10行のデータは、日々の処理データ毎回違っていきます。

試しに、10行のデータを最初のデータとして1つにして、その後のデータ全て9行のデータで確認してみましたら、最初に表示するセルB2からJ2迄は表示されます。
K2には10行目のデータが表示はされず、B3に表示されます。
B2に基準となる文字列が次の行以降は、9行データ全てのデータはC列にB2で表示しています基準文字列が表示します。

回答No.5

ushi2015

こんにちは
セルB2に
=IF(ROW(B2)=2,OFFSET($A$1,COLUMN(A1),0),IF(COLUMN(B2)=11,"",OFFSET($A$1,ROW(B1)*9-9+COLUMN(B2),0)))
で、K2までフィルコピー、必要な行数迄フィルコピーで。

補足

ご回答有難うございます。

=IF(ROW(B2)=2,OFFSET($A$1,COLUMN(A1),0),IF(COLUMN(B2)=11,"",OFFSET($A$1,ROW(B1)*9-9+COLUMN(B2),0)))

の数式を組んでみたところ、最初の行はイメージ通り表示出来ましたが、2行目から基準になる文字列(1番左側に位置する文字列)が、右側に1つズレた位置に来ます。
3行目は、2行目に対して1つ右側にズレてしまいます。

回答No.4

msMike

セル C1 に次式を入力して、此れを右方へセル L1 までオートフィルした後に、範囲 C1:L1 を下方へズズーッとオートフィルしてみたら如何？
=IF(COLUMN(A1)>10-(ROW()>1),"",OFFSET($A$11,+(ROW(A1)-2)*9-(ROW()=1)+COLUMN(A1)-1,))

【独白】何だか自己嫌悪的作業だなぁ～(*^_^*)

補足

ご回答ありがとうございます。
10行のデータでしたら、以下の式でイメージ通りに表示しました。

=IF(COLUMN(A1)>10-(ROW()>1),"",OFFSET($A$12,+(ROW(A1)-2)*10-(ROW()=1)+COLUMN(A1)-1,))

9行のデータの場合は、以下の式で表示しました。

=IF(COLUMN(A1)>10-(ROW()>1),"",OFFSET($A$11,+(ROW(A1)-2)*9-(ROW()=1)+COLUMN(A1)-1,))

エクセルには、or条件は無いかと思いますので、or条件みたいに2つの式いずれかを満たしたら、10行用のデータと9行用のデータをそれぞれ表示する事は可能でしょうか？

回答No.3

dogs_cats

2002はISERROR関数しか使用出来ないので、同じ関数を２度記載しなければならいので長い数式になりました。
もっとスマートな方法はあるかと思います。
C1~L1には数値データは入力しないで下さい。文字の入力は可能です。

C2式
=IF(COUNT($C$1:$L1)+COLUMN(A1)>COUNT($A$2:$A$1001),"",IF(ISERROR(INDEX(INDIRECT("A"&COLUMN(A2)+(ROW(A1)-1)*7&":A"&COLUMN(A2)+(ROW(A2)-1)*7+7),MATCH(COLUMN(A2),INDIRECT("A"&COLUMN(A2)+(ROW(A1)-1)*7&":A"&COLUMN(A2)+(ROW(A2)-1)*7+7),0))),"",INDEX(INDIRECT("A"&COLUMN(A2)+(ROW(A1)-1)*7&":A"&COLUMN(A2)+(ROW(A2)-1)*7+7),MATCH(COLUMN(A2),INDIRECT("A"&COLUMN(A2)+(ROW(A1)-1)*7&":A"&COLUMN(A2)+(ROW(A2)-1)*7+7),0))))

C2を右方向、下方へオートフィル

補足

ご回答有難うございます。

数字のみでしたら、問題なく表示出来ました。

"縦に並んでいるデータ(データは、文字列や数字です。)"

実際のデータは、文字列を含んでいる為、数字を文字列に置き換えて確認してみたところ、文字列を認識せず数式を組んだセルは空白になってしまいます。

回答No.2

bunjii

1組のデータが10行のものと9行のものがあるとしても先頭行が判断できる指標が無いと処理できません。
提示された模擬データがA2=1、A3=2、・・・、A10=9、A11=10、A12=1、A13=2、・・・のように続いていますが、これをB2=1、C2=2、・・・J2=9、K2=10、B3=1、C3=2、・・・J3=9、K3＝””、・・・、のように処理できれば良いと言うことでしょうか？
指標となる値が1から10の順列であり各組の先頭行が1である場合は作業用の列を使うことで処理できるでしょう。
作業用にL列を使うものとして次の数式を設定すれば各組の先頭行が割り出せます。
L2=SMALL(INDEX((A$2:A$1001=1)*ROW(A$2:A$1001)+(A$2:A$1001<>1)*2000,0),ROW()-1)
L2を下へ必要数コピーして作業用の列とします。
B2=INDEX(OFFSET($A$1,$L2-1,0,10),COLUMN(B2)-1)
C2=IF(AND(B2<>"",B2<INDEX(OFFSET($A$1,$L2-1,0,10),COLUMN(C2)-1)),INDEX(OFFSET($A$1,$L2-1,0,10),COLUMN(C2)-1),"")
C2セルをK2セルまでコピーします。
B2～K2を選択して下へ必要数コピーすれば目的に合うでしょう。
但し、他の付随する情報については処理上の条件が提示されていませんので何のための情報処理なのか推測できません。
貼付画像はExccel 2013で処理したものですがExcel 2002でも再現できると思います。

補足

ご回答ありがとうございます。

>1組のデータが10行のものと9行のものがあるとしても先頭行が判断できる指標が無いと処理できません。

判断出来るものとして、文字列の左端に必ず、pとcいずれかの文字を含んでいます。

回答No.1

msMike

》 途中から1行減って9行の構成に変化します。
「途中から」てのが曲者です。
Excel には、ましてや Excel 2002 には人工知能は装備されていないので、Excel に最初から「途中から」の定義を教えてあげなければならないことは自明の理ですよね。
そういう次第なので、どのような「途中から」なのかを「ご教授頂ければ幸いです」。
ちなみに、私もいまだに古典的な Excel 2002 を愛用しております。

補足

どの様な途中とは、カテゴリーの違う物で情報量が1行違っています。
先ずは、10行のデータが連続してありまして、10行のデータの後に9行のデータが並んでいる状態です。