1000件のデータを50個づつに区切って抽出する問題をやってみた。

Excel Maniacs の先週問題　　第１２０回　紙を大切に　を解いてみました。

 

あまりに難しすぎると、やろうとする気がおきませんが

解けそうで解けない問題は、ついついはまってしまいますよね。(^^ゞ

すでに優秀な方の解法が発表されていますが

私なりに解いた方法をまとめてみます。

 

1000件のデータがA1～C1001セルに入っています。

このデータを別のシートに50件づつ抽出して、なおかつ1行目にはそれぞれ見出しを入れ

3列と3列の間に空白列を挿入する作業を、A1セルに入れた一つの式で（CA51セルまでコピーして）

できないかという問題です。

 

問題を見て、パッと思いついたのがINDEX関数です。

行数と列数を自由に設定できるからです。

 

空白列の挿入はD列までを範囲にすれば列を挿入できるなと考え

4列一組で順次コピーできる方法を考えてみました。

具体的に言うと、A1セルに COLUMN()としてフィルコピーしていくと123456.…となります。

これを、A列からD列までを抽出する　1234　1234　1234　1234....となるように。

これはMOD関数を使って商の残りを出すことでできないかと。

こんな感じ、MOD(COLUMN(D1),4)+1

この式をA1セルに入力して横にフィルコピーしていくと狙った通りになりました。

 

次は行数です。

データ1からデータ50まではROW関数でそのまま出せますから簡単ですが、

5列目のE2セルにはデータ51が、9列目のI2セルにはデータ101が

という答えが返されないと抽出されません。

5～8列は 1、9～12列は 2....これに掛ける50とすれば50、100、150...が算出されます。

1～4列は 0（A列はそのまま行数で抽出すればいいので0×50でいいですよね。）

000　1111　2222　3333　とコピーされるような、、、。

 

INT関数とCOLUMN関数を組み合わせて作ってみました。

A1セルに　INT(COLUMN()/4)　の式を入力して右にフィルコピーしていくと上記のような答えが返されました。　

D列は1が返されますが、ここは空白列が入りますので問題なし、

この式では行数を求めますので、列がずれるように感じますが後の列でも問題ありません。

では具体的に、A1セルの式を　ROW()+INT(COLUMN()/4)*50　としてCA列までフィルコピーすると

BX列～CA列には951が返されます。

（実際にデータが抽出されるのはBY～CAの3列になります。）

 

行数・列数を求める式をINDEX関数に組み込んでみますと

INDEX(DB!$A$1:$D$1001,ROW()+INT(COLUMN()/4)*50,MOD(COLUMN(AJ1),4)+1)

これをA1セルに入力してH1セルまでフィルコピーしてみると

D1セルには 0 が返されます。

E1セルには50番目のデータが返されます。

下方向にさらにコピーすると それぞれのセルに該当するデータと 0 が返されました。

 

各1行目に見出しが入るのとエラー処理を考えてみました。

とりあえず、IF関数で場合分けして

1行目と言う条件で、見出し行を作ってしまったらどうだろうかと。

条件は、ROW()=1

見出し行を作る式は、INDEX(DB!$A1:$D1,MOD(COLUMN(D1),4)+1)

 

エラー処理には悩みました。

返される答えを文字列にしてしまったらどうだろうか。

T関数が使えないか。

A1セルに=IF(ROW()=1,T(INDEX(DB!$A1:$D1,MOD(COLUMN(D1),4)+1)),"AAA")

Falseのところにダミーの式を入れてH1セルまでコピー。

すると、見出しを作り、空白を返してくれてエラーが消えました。

 

見出し＆エラー処理ができましたので

データを抽出する式を組み合わせて

=IF(ROW()=1,T(INDEX(DB!$A1:$D1,MOD(COLUMN(D1),4)+1)),T(INDEX(DB!$A$1:$D$1001,ROW()+INT(COLUMN()/4)*50,MOD(COLUMN(D1),4)+1)))

とA1セルに入力、右方向、下方向に適当にコピー。

見出しが作成されそれぞれの文字データは抽出できました。

が、、、数値のデータはやはり抽出されず、空白が返されました。

 

では、TEXT関数でデータに指定したらどうだろうか。

セルの表示形式で###0 とかありまし、先日の表示形式の記事で####も使ってみましたので

これが使えないだろうか。

 

でっ、以下のような式に。

=IF(ROW()=1,T(INDEX(DB!$A1:$D1,MOD(COLUMN(D1),4)+1)),TEXT(INDEX(DB!$A$1:$D$1001,ROW()+INT(COLUMN()/4)*50,MOD(COLUMN(D1),4)+1),"#"))

#は最初3個で式を作りました。

その後、減らしてみても有効でしたので上記の式に。

エラー処理が行え、かつ数値データの抽出もできました。

BY51～CA51セルには1000番目のデータが抽出されました。