エクセルデータの並び替え

質問

Ａ列に　1 2 3 4 5 6 7 ・・・・・
Ｂ列に　a b c d e f g h i j k l ・・・・　と文字列があります。

これを　Ｃ列に　５つずつデータをセットにして並べたいのです。

Ａ列　　　　Ｂ列　　　　　　　　　　　Ｃ列
1　　　　　a 　　　　　1　a 　 2　b　 3　c　 4　d　 5　e
2　　　　　b 　　　　　6　f 　 7　g　 8　h　 9　i　 10　j
3　　　　　c 　　　　 11　k 　12　l　 13　m　 14　n　 15　o
4　　　　　d 　　　　 16　p 　 17　q　 18　r　 19　s　 20　t
5　　　　　e 　　　　　以下同じように続く
6　　　　　f
7　　　　　g
8　　　　　h
9　　　　　i
10　　　　 j
11　　　　 k

実際のＣ列のデータは
トマト　tomato 　ネコ　cat 　　机　desk　　いす　chair　　かばん 　bag　　のようにしたいのです。

関数の扱いがわからずに苦戦しています。どうぞ、よろしくお願いします。

回答No.8

Cupper · ベストアンサー

＃１ Cupperです。

＞ｃ１にコピペしたのですが、式がそのまま出てきます。
＞何がいけないのか、関数が複雑で大変ですね。すみません。
自分は便宜上、関数式の前に全角のスペースを入れています。
このスペースまでコピーしていませんか。

※今回は一箇所、全角スペースを入れ忘れていますが…（汗

お礼

全角スペースを入れたら、うまくいきました。
解説していただいた関数の扱いをまた勉強させていただきます。
大変なお手間をとらせてもうしわけありませんでした。
本当にありがとうございました。

回答No.7

noname#235092

No.6です。

今履歴を調べてみたら「エクセルのデータの並び替え」というあなたの質問に一昨日答えていました。中身はソートというより関数でしたがそれも関数というほどのものではありませんでした。

そこでアドバイス。
表計算ソフトは関数が使えるという利点がありますが、やりたいことをすぐ関数に結びつけず、もっとリラックスして柔軟に平易に考えることをおすすめします。
今回のテーマだってそうです。なにをやっておられるのかわかりませんが、忙しそうなあなたにとってこんな問題で時間をとられているようでは・・・。

むずかしい関数よりも表計算のもっと基本的なことを勉強した方があなたにはよほど役に立ちそうです。

回答No.6

noname#235092

No.4です。
空白セルをなくすにはC列を昇順か降順でソート（並べ替え）します。
そうすると空白の行が下に集まり、必要な行だけが上に並びます。

しかし、その必要な行は希望の順序にならないので、元に戻すためにあらかじめ隣の列などに（たとえばD列に）通し番号を入れておくのです。最初にソートするときその通し番号の列も含めてソートします。

そして空白行以外の必要な行だけをもういちどソートします。そのとき通し番号の順にソートすれば出来上がり。

※先日もソートの質問に答えましたが、あなたでしたか？

お礼

まちがって、補足のところにお礼をしてしまいました。

補足

説明していただいた通りにしたら、うまくできました。
「表計算のもっと基本的なことを勉強した方が役に立つ」なるほど、
並び替えたり、くっつけたりしたいだけだったら、関数に頼らずに
できるんですね。
ご指摘していただいてよい勉強になりました。
これからも必要があると思いますので、これを参考にして考えるようにします。度々のご回答ありがとうございました。

回答No.5

noname#204879

A　 B　　　　　　 C　　　　　　　D　 E　　F　 G　　 H　　 I　　 J　　 K
1　　1　a　1 a 2 b 3 c 4 d 5 e　　　　　1 a　　　1 a　 2 b　 3 c　 4 d　 5 e
2　　2　b　6 f 7 g 8 h 9 i 10 j　　　　 2 b　　　6 f　 7 g　 8 h　 9 i　 10 j
3　　3　c　11 k 12 l 13 m 14 n 15 o　　 3 c　　　11 k　12 l　13 m　14 n　15 o
4　　4　d　16 p 17 q 18 r 19 s 20 t　　 4 d　　　16 p　17 q　18 r　19 s　20 t
5　　5　e　　　　　　　　　　　　　　　 5 e
6　　6　f　　　　　　　　　　　　　　　 6 f
7　　7　g　　　　　　　　　　　　　　　 7 g
8　　8　h　　　　　　　　　　　　　　　 8 h
9　　9　i　　　　　　　　　　　　　　　 9 i
10　10　j　　　　　　　　　　　　　　　 10 j
11　11　k　　　　　　　　　　　　　　　 11 k
12　12　l　　　　　　　　　　　　　　　 12 l
13　13　m　　　　　　　　　　　　　　　 13 m
14　14　n　　　　　　　　　　　　　　　 14 n
15　15　o　　　　　　　　　　　　　　　 15 o
16　16　p　　　　　　　　　　　　　　　 16 p
17　17　q　　　　　　　　　　　　　　　 17 q
18　18　r　　　　　　　　　　　　　　　 18 r
19　19　s　　　　　　　　　　　　　　　 19 s
20　20　t　　　　　　　　　　　　　　　 20 t

E1: =A1&" "&B1
G1: =OFFSET($E$1,COLUMN(A1)-1+(ROW(A1)-1)*5,0)
C1: =G1&" "&H1&" "&I1&" "&J1&" "&K1

お礼

ご回答ありがとうございました。
１行目はうまくいったのですが、２行目が０と出てきます。
No.6 No.8さんの回答で解決しました。
お手数をおかけしました。ありがとうございました。

回答No.4

noname#235092

c1に「=A1&B1&A2&B2&A3&B3&A4&B4&A5&B5」を入力する。
c1～c5を選択する。この範囲をドラッグして下に必要なだけ複写する。

これで5つおきに求めたいものが並ぶので値に変えて全体をソートして空白セルを除く。（もちろんソートの前に通し番号を打っておく）
スペースはお好みで。これでどうですか？

補足

>これで5つおきに求めたいものが並ぶので
ここまでできたのですが

>値に変えて全体をソートして空白セルを除く。
これがわかりません。空白セルがなくなればＯＫです。
どうしたら除けますか。

回答No.3

imogasi

関数だけでやれるとは思うが相当複雑で、長い式になるでしょう。
そこでユーザー関数を使ってやってみました。数行で済みます。
言い換えるとＶＢＡ向きの問題だと思います。
ＶＢＥの標準モジュールに
Function aa(a)
r = a.Row()
r = (r - 1) * 5 + 1
s = ""
For i = r To r + 4
s = s & Cells(i, "A") & Cells(i, "B")
Next i
aa = s
End Function
と入れる。aaは関数名でエクセルで使う関数名以外ならＯＫ。
シートに戻り
ｃ１セルに =aa(A1) と入れる。
データ数（行数）÷５＋１行まで、Ｃ１のセルの式を複写する。
結果
1a2b3c4d5e 
6f7g8h9i10j
11k12l13m14n15o
16p17q18r19s20t
21u22v23w24x25y
見やすくスペースを入れるなら
s = s & Cells(i, "A") & Cells(i, "B")
　　｜
　　ｖ
s = s & Cells(i, "A") & Cells(i, "B") & " "　とすると
1a 2b 3c 4d 5e
6f 7g 8h 9i 10j
11k 12l 13m 14n 15o
16p 17q 18r 19s 20t
21u 22v 23w 24x 25y

補足

昔、何度かＶＢＥの練習をしたことがあるのですが、すっかり忘れています。
標準モジュールに
Function aa(a)
r = a.Row()
r = (r - 1) * 5 + 1
s = ""
For i = r To r + 4
s = s & Cells(i, "A") & Cells(i, "B")
Next i
aa = s
End Function
を入れて、そこから先がちょっとわかりません。
初心者ですみません。

回答No.2

redowl

試しに
C1に下記の式をコピペ　してから、C２以降にドラッグ。
=CONCATENATE(INDIRECT("A"&(ROW()-1)*5+1),"　",INDIRECT("B"&(ROW()-1)*5+1)

スペースの文字数が不明なので、全角１文字　挿入してます。
これが確認できたら、

(ROW()-1)*5+●　　の●部分を（2,3,4,5)にして、さらに同様な式を連結することで可能かと・・・。

=CONCATENATE(INDIRECT("A"&(ROW()-1)*5+1),"　",INDIRECT("B"&(ROW()-1)*5+1),"　",INDIRECT("A"&(ROW()-1)*5+2),"　",INDIRECT("B"&(ROW()-1)*5+2))

補足

ｃ１に=CONCATENATE(INDIRECT("A"&(ROW()-1)*5+1),"　",INDIRECT("B"&(ROW()-1)*5+1)
を入れたのですが、式がそのまま出てきます。
なぜでしょうか。すみません。

回答No.1

Cupper

面倒 且つ 分かり難い数式になります。

Ｃ１セルに次の式を入力（コピー）して下へコピーしてください。
　=OFFSET(A$1,ROW()*5-5,0)&OFFSET(A$1,ROW()*5-5,1)&OFFSET(A$1,ROW()*5-4,0)&OFFSET(A$1,ROW()*5-4,1)&OFFSET(A$1,ROW()*5-3,0)&OFFSET(A$1,ROW()*5-3,1)&OFFSET(A$1,ROW()*5-2,0)&OFFSET(A$1,ROW()*5-2,1)&OFFSET(A$1,ROW()*5-1,0)&OFFSET(A$1,ROW()*5-1,1)

《 解 説 》
５行おきに繰り返す数式を考えましょう。
あとは数学的に数字を増やすように考えることです。
参照するセルを指定できる関数には OFFSET関数、INDIRECT関数があります。

まず ROW関数で行を番号を取得します。
ROW関数で行番号を取得できます。
　ROW(A1)
次にOFFSET関数でA1セルを指定してみます。
OFFSET関数は基準のセルからどれだけ離れたセルを参照するかの指定ですのでA1セルを基準とする場合の行指定は

　ROW(A1)*5-5

となります。５倍しているのは５行毎に参照することを考慮しているからです。

　OFFSET($A$1,ROW()*5-5,0)

Ｂ１セルを参照する場合は、同様に

　OFFSET($A$1,ROW()*5-5,1)

二つの値を会わせるため、これを "&" でつなぎます

　OFFSET($A$1,ROW()*5-5,0)&OFFSET($A$1,ROW()*5-5,1)

２行目以降を参照する場合は同様に、

　OFFSET($A$1,ROW()*5-4,0)&OFFSET($A$1,ROW()*5-4,1)
　OFFSET($A$1,ROW()*5-3,0)&OFFSET($A$1,ROW()*5-3,1)
　OFFSET($A$1,ROW()*5-2,0)&OFFSET($A$1,ROW()*5-2,1)
　OFFSET($A$1,ROW()*5-1,0)&OFFSET($A$1,ROW()*5-1,1)

これを "&" で繋いで、始めに示した数式になります。
間に空白が必要であれば、

　=OFFSET(A$1,ROW()*5-5,0)&" "&OFFSET(A$1,ROW()*5-5,1)&" "&OFFSET(A$1,ROW()*5-4,0)&" "&OFFSET(A$1,ROW()*5-4,1)&" "&OFFSET(A$1,ROW()*5-3,0)&" "&OFFSET(A$1,ROW()*5-3,1)&" "&OFFSET(A$1,ROW()*5-2,0)&" "&OFFSET(A$1,ROW()*5-2,1)&" "&OFFSET(A$1,ROW()*5-1,0)&" "&OFFSET(A$1,ROW()*5-1,1)

と、空白を挟み込みましょう。
同様な考え方で INDIRECT関数を使用することも考えられます。
　=INDIRECT("A"&ROW()*5-4)&INDIRECT("B"&ROW()*5-4)&INDIRECT("A"&ROW()*5-3)&INDIRECT("B"&ROW()*5-3)&INDIRECT("A"&ROW()*5-2)&INDIRECT("B"&ROW()*5-2)&INDIRECT("A"&ROW()*5-1)&INDIRECT("B"&ROW()*5-1)&INDIRECT("A"&ROW()*5-0)&INDIRECT("B"&ROW()*5-0)
　=INDIRECT("A"&ROW()*5-4)&" "&INDIRECT("B"&ROW()*5-4)&" "&INDIRECT("A"&ROW()*5-3)&" "&INDIRECT("B"&ROW()*5-3)&" "&INDIRECT("A"&ROW()*5-2)&" "&INDIRECT("B"&ROW()*5-2)&" "&INDIRECT("A"&ROW()*5-1)&" "&INDIRECT("B"&ROW()*5-1)&" "&INDIRECT("A"&ROW()*5-0)&" "&INDIRECT("B"&ROW()*5-0)

※ROW関数、OFFSET関数、INDIRECT関数の詳細は Excelのヘルプを参照してください。

補足

ものすごく長大な式と解説をありがとうございました。
ｃ１にコピペしたのですが、式がそのまま出てきます。
何がいけないのか、関数が複雑で大変ですね。すみません。