Excel関数を使って繰り返し入力の手間を劇的に減らす方法

Excel関数を使って繰り返し入力の手間を劇的に減らす方法

Excelの資格を2つ持ってるKiryuです(*^o^*)

今回はExcel関数を使って繰り返し入力の手間を劇的に減らす方法を解説します。

3つの関数を紹介しますが、具体的な活用シーンに沿って説明しますので、ぜひ参考にしてみてください。

スポンサーリンク
スポンサーリンク

【REPT関数】評価を★の数で表示しよう

REPTリピート関数は、指定した回数だけ文字列の表示を繰り返す関数です。

この関数は「評価素点を★マークで表示する」といったケースで繰り返し入力の手間を減らしてくれます。

たとえば次のような「商品名ごとの評価素点を記した表」があったとしましょう。

エクセル 商品名ごとの評価素点を記した表の例

そして、この表の「評価素点」の部分を次のように「★」の数で表したくなったとします。

エクセル 評価素点を星の数で表現

でも、「★」を手作業で繰り返し入力するのはあまりにも手間がかかりますよね。

そこで、REPT関数を使って楽をしましょう。手順は次の通りです。

  1. エクセル REPT関数を入力C2に「=REPT(“★”,B2)」と入力します。
  2. エクセル REPTが星を繰り返し表示セルを確定すると、「★」が4個表示されます(REPT関数がB2セルの評価素点4を回数とみなして、その回数分「★」を繰り返して表示しています)。
  3. エクセル オートフィルで他のセルも星で表示C2を起点として下方向にオートフィルをかければ、他の商品の評価素点も同様に「★」で表示されます。

【VLOOKUP関数】

VLOOKUPブイルックアップ関数は、表を縦方向に検索し、検索中の値が見つかった場合はその値に対応づけられたデータを取り出す関数です。

初級編:商品名で検索して単価データを取り出そう

VLOOKUP関数は「単価一覧表を参照して販売実績表に単価を入力する」といったケースで繰り返し入力の手間を減らしてくれます。

たとえば次のような「時系列で販売実績を記録した表」があるとして、

エクセル 販売実績表

一番右の「単価」の空欄を埋めたいとします。

商品ごとの単価を記した一覧表は隣に用意されているとします。

エクセル 単価を記した一覧は左にある

左側の単価一覧を目視で確認しながら、右側の販売実績表の単価欄をひとつひとつ手作業で埋めることも不可能ではありません。

でも手間がかかりますし、そういう作業を人がやると必ずいつか間違えます。

そこで、VLOOKUPを使って自動的に単価を入力しましょう。次の手順を実施します。

  1. エクセル VLOOKUP関数を入力G2に「=VLOOKUP(E2,A:B,2,FALSE)」と入力します。(ちなみに、この数式の意味は「E2に記された商品名(ラムネ味やきいも)を、A~B列の表中で検索し、商品名が見つかったら2列目のデータをとる」というものです。)
  2. エクセル VLOOKUP関数が成功セルを確定すると、ラムネ味やきいもの単価である「300」が正しく表示されました。
  3. エクセル オートフィルで他のセルにも単価を表示するG2セルを起点として下方向にオートフィルをかければ、他の商品の単価が自動的に表示されます。

応用編:商品名をn回縦方向に繰り返そう

VLOOKUP関数を応用すると、「任意の文字列を指定した回数分、縦方向に繰り返し表示する」こともできます。

たとえば次のような感じです。

エクセル 縦方向への繰り返しの例

A~B列で「商品名」と「繰り返し回数」が指定されています。

この指定に従って、D列では「チョコ味やきいも」が縦方向に4回繰り返され、「イチゴ味やきいも」が3回繰り返され…という表示になっています。

D列のような表示を再現したいとき、手作業で繰り返し入力するなんてやってられません。

VLOOKUP関数を利用して近道をしましょう。補助列を2つ追加する必要があるのでややレベル高めですが、手順を1つずつわかりやすく解説します。

  1. エクセル 最終行の処理この状態からはじめます。商品名と繰り返し回数の最終行(A7とB7)に「-」と書き入れてください。
  2. エクセル 補助列1を作る商品名の列の左側に「補助列1」を導入します。
  3. エクセル 補助列1に入力A2には「1」を入力し、A3には「=A2+C2」を入力します。
  4. エクセル 補助列1にオートフィルA3を起点として下方向にオートフィルをかけ、補助列1の空欄を埋めます。
  5. エクセル 補助列2を作る「繰り返し表示」の列の左側に補助列2を導入します。
  6. エクセル 補助列2に入力E2に「1」を入力します。
  7. エクセル 補助列2にオートフィルE2を起点として下方向にオートフィルをかけ、補助列2の空欄を埋めます。
  8. エクセル VLOOKUP関数を入力F2に「=VLOOKUP(E2,A:B,2,TRUE)」を入力します。
  9. エクセル 縦方向に繰り返しできたF2を起点として下方向にオートフィルをかけ、F列の空欄を埋めます。商品名が縦方向に繰り返し表示されることを確認します。

ここでは、VLOOKUP関数の第4引数に「TRUE」を指定して、近似一致で検索しているのがポイントです。

INFO

VLOOKUP関数の近似一致は、次のように動作します。

表の中で検索値を見つけた場合
見つけた値を一致する値とみなす(完全一致と同じ動作)
表の中で検索値を見つけられなかった場合
検索値よりも小さい値で、かつ最も検索値に近い値を一致する値とみなす

【MOD関数】社員番号を使って社員を4グループに分けよう

MOD関数は、割り算の余りを求める関数です。

(モッド関数またはモジュラス関数と読みます。)

この関数は「社員番号を使って社員を4グループに分ける」といったケースで繰り返し入力の手間を減らしてくれます。

たとえば次のような12人分の社員の社員番号の一覧があって、

エクセル 社員番号一覧表

12人の社員を1, 2, 3, 0の4つのグループに分けたいとします。

エクセル 4つのグループに分ける

1, 2, 3, 0を繰り返し手入力するのは手間です。12人ならコツコツ打ち込むのも不可能ではないですが、12,000人なら到底できません。

そんな作業もMOD関数を使えば簡単です。次の手順を実施します。

  1. エクセル MOD関数を入力B2に「=MOD(A2,4)」を入力します。
  2. エクセル セルを確定セルを確定すると「1」が表示されます。
  3. エクセル オートフィルで他のセルにもMOD関数を入力B2を起点としてオートフィルをかけると、各社員番号に対して1, 2, 3, 0のグループ番号が割り振られます。

なお、グループ番号を「1, 2, 3, 4」としたい場合は、IF関数を組み合わせて次のようにすると良いでしょう。

  1. エクセル IF関数とMOD関数を入力B2に「=If(MOD(A2,4)=0,4,MOD(A2,4))」を入力します(ちなみに、この数式の意味は「余りがゼロのときは4に置き換えて、その他の場合は余りをそのまま表示する」というものです)。
  2. エクセル セルを確定セルを確定すると「1」が表示されます。
  3. エクセル オートフィルで他のセルにもIF関数とMOD関数を入力B2を起点としてオートフィルをかけると、各社員番号に対して1, 2, 3, 0のグループ番号が割り振られます。

この記事のまとめ

今回は繰り返し入力に使える3つのExcel関数を解説しました。

関数名と使いどころを復習しておきましょう。

REPT関数
評価を★の数で表示する
VLOOKUP関数
初級編:商品名で検索して単価データを取り出す
応用編:商品名をn回縦方向に繰り返す
MOD関数
社員番号を使って社員を4グループに分ける

以上、参考になれば嬉しいです。

スポンサーリンク
PC
キリュログ