一番上の入り口へ > excel > 整形データ、テストデータを作成する!(レッツトライExcel)

整形データ、テストデータを作成する!
(レッツトライExcel)

Excelはちょっとした関数や編集機能も大量に用意されているので いろいろ出来て便利です。 今回は、質素なデータから単純な整形データを作成したり、ランダムデータを作成する方法に チャレンジしてみます。

(A)データを編集して出力
(B)データ要素を組み合わせる
(C)整列したデータをぐちゃぐちゃに並べ替え(ランダムに)する
(D)一定の範囲内のデータを作成

学べるスキル

・データの加工技術
・・Rand,floor関数
・・フィルタ、ソート機能
・・セルの名前の使い方

作業ダイジェスト

(A)データを編集して出力、(B)データ要素を組み合わせる

データを変形させるパターン
xml & xsltを使ったり、テキストエディターを使って置換する方法もありますが いったん作成しておくと、左側の表の中だけ修正しておくだけで、右側の中が自動的に 更新されるので楽です。

このようにデータとデータ表示形式を分離して定義すると、スッキリ整理され修正も簡単になります。
この例だけだと ホームページビルダーにコピペした方が速いですね。
エクセルの中で 改行した物をテキストでコピーすると ""で囲まれますので、 一つのセルに最終的にまとめるか、テキストエディターなどで置換・編集する必要があります。
他にもカンマ区切り(データの区切りを使う)や、xmlのデータ構造に変換したり、 表を簡単なデータベースとして扱ったデータリストに変換したりする方法もあると思います。
基本構造部分を定義して REPLACE関数と組み合わせて使うと、埋め込み部分が整理できます。
<tr><td>@1</td><td>@2</td></tr>


(C)データの並び替え(ランダムに並び替える、漢字を並び替える)

データをランダムに並び替えるとき


新たな列を追加して、乱数で作ります。


そこでソートすると番号がランダムになります。 (左側列参照)
漢字を並び替えるときは→読み仮名の列や、独自のID(部署コードなど)を作ってそこでソートします。

複数の列で並び替えるときは→今回の例のように列を追加して、そこに文字列結合させたデータを利用することで Excel 2003で対応できないほどの列数に(3列以上に)対応できます。


(D)一定範囲のデータを作成

0〜100の範囲のデータのように一定間隔のデータが欲しいときに使える技です。
ちょっと難しくしていますが、日付のある期間〜期間のデータを作成します。
(Excelの内部表現として1日 =1.0です)

まず開始日と、終了日を決め、差分として間隔を求めます。 ついでにわかりやすくなるようにセル名に名前を付けています。最終日を含めたいので、差分に+1しています。


実際のデータではこの式のように Rand()と floor()関数を組み合わせて、整数値の乱数を求めています。

※更新する度に数値が変動するため、作成した後はテキストコピーするか、更新を止めておく必要があるかもしれません。


式に直接感じなどを使う方法(名前についてはこちら)

まとめ

データをExcelで加工し、作成する技を学びました。

応用例

SQL向けにデータを作成したり、とりあえず的にデータを作成したり、Excelで入力するデータをExcelで作成したりいろいろと応用がきくと思います。またテキストエディターと連携して置換や加工をして、より効率の良い方法を探してください。

テストデータ

四国八十八箇所(wikipedia)

類似リンク

商品コード検索表を作る(レッツトライExcel)

リストの過不足をチェック!(レッツトライExcel)

マクロを使わずに重複行をサクッと削除するちょいわざ

PWなど適当な文字列を作るちょいわざ

セルの値や何番目のセルかを表の中から検索したい時ベストな関数の使い分けはこれ!(Excel関数使いこなし編)


一番上の入り口へ > excel > 整形データ、テストデータを作成する!(レッツトライExcel)