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

Excel

Excel使用歴約20年の杉山貴隆です。

今回は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グループに分ける

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

タイトルとURLをコピーしました