Excelで表に入力されたデータを1列に並び替え

質問

第1行に47都道府県名、列Aに1980ー1999年の入った表があります
つまり、B2は北海道の1980年のデータ、AV21は沖縄の1999年のデータが入ります
この表を以下のように1列に変換するにはどうすればよいでしょうか
INDEX関数などで試そうとしましたができませんでした

列A 列B 列C
北海道 1990 元B2のデータ
北海道 1991 元B3のデータ
(中略)
沖縄 1998 元AV20のデータ
沖縄 1999 元AV21のデータ

回答No.1

e10go · ベストアンサー

こんばんわ。

INDIRECT関数を使用して、R1C1形式のセル指定で、変換データを表示する計算式を考えました。

元データがシート"Sheet1"にあるとして、変換データを別シートに表示する計算式を下に示します。
なお、変換データは、セルA2～C941に入れるものとします。

A列(A2～A941)の式
=INDIRECT("Sheet1!R1C["&INT((ROW()-2)/20)+1&"]",FALSE)
B列(B2～B941)の式
=INDIRECT("Sheet1!R["&INT((ROW()-2)/20)*(-20)&"]C1",FALSE)
C列(C2～C941)の式
=INDIRECT("Sheet1!R["&INT((ROW()-2)/20)*(-20)&"]C["&INT((ROW()-2)/20)-1&"]",FALSE)

お礼

ありがとうございました

回答No.2

imogasi

質問の例がわかりにくいが、Index関数でできます。
Sheet2の、第１行は
A1=空白、B1＝北海道、C1=青森、D1=秋田、E1=岩手、・・・AV1＝沖縄ですね。
A2=1980、A3=1981,A4=1982、・・・・・A21=1999ですね。
B2:AV21までに計数が入っている。
北海道 青森 岩手　右は省略
1980 1 21 41
1981 2 22 42
1982 3 23 43
1983 4 24 44
1984 5 25 45
1985 6 26 46
1986 7 27 47
1987 8 28 48
1988 9 29 49
1989 10 30 50
1990 11 31 51
1991 12 32 52
1992 13 33 53
1993 14 34 54
1994 15 35 55
1995 16 36 56
1996 17 37 57
1997 18 38 58
1998 19 39 59
1999 20 40 60
計数は、結果が正しいか、わかりやすくするためわざと連番にしている。
ーーーーーー
府県をA列に２０行ずつ並べるやり方。
Sheet3に
A2に
=INDEX(Sheet2!$B$1:$AV$2,1,INT((ROW()-2)/20)+1)
と入れて２０＊４７＋１行まで式を複写する。
計数部分について
Sheet3の
B2に
=INDEX(Sheet2!$B$2:$AV$21,MOD((ROW()-2),20)+1,INT((ROW()-2)/20)+1)
と入れて、２０＊４７＋１行まで式を複写する。
結果
北海道 1
北海道 2
北海道 3
北海道 4
北海道 5
北海道 6
北海道 7
北海道 8
北海道 9
北海道 10
北海道 11
北海道 12
北海道 13
北海道 14
北海道 15
北海道 16
北海道 17
北海道 18
北海道 19
北海道 20
青森 21
青森 22
青森 23
青森 24
青森 25
青森 26

お礼

ありがとうございました