- Home »
- Excelの基本操作の目次 »
- Excel 2016でダミーデータを作成したい
- Excelで関数や機能を使った練習をしたい、とか資料を作成したいといったときにダミーデータを作成したいときがあります。
このサイトでも様々なダミーデータを使用していますが、どのように作成したら楽なのか・・・といったことを書いてみたいと思います。 - ここでは下図のようなダミーデータを作成してみます。
- 連番は行番号と連動させています。
- =ROW() でセルの行番号を得ることができますので、連番としたい数値に調整します。
ここの例では 1001 からの連番としています。数式は =1000+ROW()-1 としました。
2行目なので行番号は「2」となります。 よって、=1000+2-1=1001 となるわけです。
=999+ROW() とか =1001+ROW()-2 といった数式でも構いません。自分で後からわかるような数式であることが望ましいと思います。
- ランダムな日付を作成するのですが、どのような日付の並びにするのかが問題となります。
- ここでは、単純にある期間のランダムな日付であればよいことにしています。
よって、=RANDBETWEEN("2016/10/1","2016/10/7") と 2016/10/1〜10/7の間でランダムになるようにしています。
日付のセルは 表示形式を長い日付形式としています。
- ダミーデータに使用する担当者や商品名などはリストにしておくと便利です。
ここでは Sheet2にテーブル「元データ」として作成してみました。
- データリストを作成後、[ホーム]タブの「テーブルとして書式設定」から書式を選択して、テーブルに変換しています。
- テーブル名は[テーブルツール]リボンの「デザイン」タブのテーブル名で「元データ」としました。
- 担当者は作成したテーブルからランダムな1〜6の数値によって呼び出します。
数式はテーブル名を利用して =VLOOKUP(RANDBETWEEN(1,6),元データ,2) としました。
テーブル名を利用しない場合はセル範囲で書いて、=VLOOKUP(RANDBETWEEN(1,6),Sheet2!$A$2:$D$7元データ,2) となります。
- 商品名の数式は =VLOOKUP(RANDBETWEEN(1,6),元データ,3) としました。
担当者の数式とは 列数が 「2」と「3」 が違うだけです。
- 価格はテーブルを利用した数式だと =INDEX(元データ,MATCH(D2,元データ[商品名],0),4) といった感じになると思いますが、ちょっとややこしいですので却下しました。
数式は =VLOOKUP(D2,Sheet2!$C$2:$D$7,2,FALSE) とセル範囲を利用したものにしました。
この数式では False がポイントになります、商品名は元テーブルで昇順に並んでいないためです。
担当者と商品名の場合は検索値が1〜6で元テーブルでは昇順に並んでいるので TRUE を省略しています。
- 数量はランダムな数値なので、範囲を10〜25 と決めて、=RANDBETWEEN(10,25) としました。
- 金額は 単価*金額 として =E2*F2 としました。
- 消費税を考慮したいケースでは =ROUNDDOWN(E2+F2*1.08,0) といった数式にすることが考えられます。
ここでは 消費税率 8% で小数点以下切り捨てとしています。
- ダミーデータを作成するのに RANDBETWEEN関数 を使っていますので、再計算のたびにデータが変化します。
ここのページで使っている画像のデータが変化しているのはこれが原因です。
[F9]キーを押して、手動で再計算させることもできます。
- 再計算のたびにデータが変化しますので、データが変化しないようにするには、数式を値に変更する必要があります。
- データを選択して、[Ctrl]+[C]でコピーします。
- Shee3のA1セルを選択し、[Ctrl]+[V]で貼り付けます。
右下に表示される [貼り付けオプション]ボタンをクリックして「値」を選択します。
- B2:G24を選択して、[データ]タブの[昇順]をクリックしてデータを日付順に並べ替えました。
一応、これでダミーデータは完成としました。
Home｜Excelの基本操作の目次｜Excelでダミーデータを作成したい