わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】連番を振るなら数式を使え、いや、使ってみてください。


皆さんは、Excelで連番(連続番号、連続データともいう)を入力したいとき、どのような操作をしますか?

Excelで連番を振る方法はいろいろあります。最も基本的な方法は「オートフィル」です(オートフィルを利用した表の作成手順と入力練習)。また、ジャンプやフィルタを使う方法もあります(連続データの作成と連続していないセル範囲に連番を振る)。

ところで、月間予定表(下図)で日付や曜日を入力する場合、Excelを使い慣れている人は「翌月以降のことを考えたら数式で連番を振るべきだ」と考えます。数式を使って連番を振るというのは非常に重要な考え方です。

f:id:waenavi:20181111105901j:plain

 

このように、Excelで連番を振る方法はたくさんあり、状況に応じて使い分けをしなければなりません。そこで、問題です。 

問題

連番を直接入力する方法と、計算式を用いる方法の違いを述べなさい。また、計算式を使用するときの注意点を述べなさい。

f:id:waenavi:20181111111447j:plain

 

 

解説

1.オートフィルの復習(初心者用)

連続データは、オートフィルをすることによって直接入力することができます。オートフィルの基本はこちらをご覧ください。

 

また、「連続データの作成」を使う方法もあります。

f:id:waenavi:20181111110308j:plain

 

2.議論がかみ合わなかった事例

「連番 関数」で検索すると、関数を用いて連番を振る方法がいろんなサイトで紹介されています。

先日、あるIT系のサイトで、住所録や参加者のリストに連番を振るなら関数を使うべきだ!という趣旨の記事が投稿されました(詳細は省略)。このような記事が掲載されると賛成や反対の意見が出てきます。しかし、残念ながら、完全に論点がずれている哀れなコメントがいくつかありました。

  • セルの右下でカーソルが十字のマークにしてからダブルクリックすればいい
  • 1、2と入力して、2つ選択して右下隅に合わせて下までドラッグ・・・

そんなご丁寧なオートフィルの説明、誰も求めてませんから!!!

このように、Excelが得意と自負している人でも、等差数列を数式で作ったことすらない人が多く、知らないうちに恥をかいていることがあるのです。

 

3.相対参照で連番を振る方法

最初の数を入力します。その下に、1を加算する計算式を入力します。

f:id:waenavi:20181111111728j:plain

 

オートフィルをします。

f:id:waenavi:20181111111946j:plain

 

直接入力の場合と計算式の場合の違いを理解して、状況に応じて使い分けることが大事です。

違い(1)初項を変える

最初の数を変えます。直接入力の場合は連動しませんが、計算式の場合は連動します。

f:id:waenavi:20181111113605j:plain

 

単に、固定の連番を振るだけであれば、直接入力で十分です。

f:id:waenavi:20181111113709j:plain

 

しかし、1ずつ増やすことを重視して、最初の数を頻繁に変える場合には、計算式を使います。

f:id:waenavi:20181111113716j:plain

 

最初の月間予定表の例では、予定表を1回しか使わない場合は計算式を使う必要はありません。しかし、翌月以降も使うことがすでに分かっている場合は計算式を使うべきです。セルA2以外はすべて数式にして、表示形式を変えます。

f:id:waenavi:20181111114135j:plain

 

セルA2を変えるだけで翌月以降も使えます。

f:id:waenavi:20181111114231j:plain

 

違い(2)途中の数を変える

今度は、途中の数を変えてみます。直接入力の場合はそのセルより下は変わりませんが、計算式の場合は連動します。

f:id:waenavi:20181111115208j:plain

 

この性質を利用して複雑な表を作ることができます。

f:id:waenavi:20181111182614j:plain

 

違い(3)途中の行を消す

途中の行を削除します。直接入力の場合は番号が飛ぶだけですが、計算式の場合は、参照している、上のセルがなくなるのでエラーになります。

f:id:waenavi:20181111115211j:plain

 

オートフィルをし直します。

f:id:waenavi:20181111115214j:plain

 

違い(4)欠番

途中の数を消して空白にします。直接入力の場合は欠番になるだけですが、計算式の場合、空白はゼロとして計算されるので、1から振り直しになります。

f:id:waenavi:20181111115217j:plain

 

4.関数を使う方法

関数を使う方法は他のサイトに任せますが、様々な例が紹介されています。

最も基本的な例はROW関数です。=row()と入力するだけで行番号を呼び出せるので、適当に数を足したり引いたりすれば、連番になります。ROW関数を使う理由は行の挿入や削除、並べ替えに対応するためです。同じ数式をコピーしているので並べ替えても変わりません。

f:id:waenavi:20181111120627j:plain

その他にMAX関数を使って最大値+1の自動採番をしたり、IFを使ったりOFFSETを使ったりINDIRECTを使ったり、いろいろな関数の使い方が考えられます。状況によって使い分けます。

 

5.計算式、関数を使ってはいけない例

前述の名簿の例で、名簿の連番は個人に割り当てられるコード番号であり絶対に変わってはいけないものだから自動再計算される数式を使うのは間違い、という趣旨の反対意見がありました。個人や商品を識別するための番号(ID)は重複してはならず、しかも固定しなければならないという大原則があります。これを一意性といいます。

連番を入力する場合には、表の性質を見極める必要があります。

番号と内容が完全に紐づけられている表(マスタという)は、行の挿入や削除、並べ替えによって、番号と内容の紐づけが変わってはいけないので、数式で連番を振るのは許されません(ただし、複雑なコード番号を生成するために関数を使うことはあります)。

しかし、番号が正式に割り当てられたものではなく、人数を把握したり、会議で説明するときのために仮に付番する性質のものであれば、数式を使うのが正しいです。

 

f:id:waenavi:20181111174553j:plain

 

6.直打ち問題

(1)セルを保護して済む問題ではない

連番を数式で振るという発想がない人は、当然、番号を振りなおすときも直接入力してしまいます。せっかく数式を入れていても、気が付かないまま数式を消してしまいます。

Excelには直接入力を防ぐ機能があります。例えば、シートの保護でロックをかけ、入力できないようにしてしまう方法です。

f:id:waenavi:20181111120220j:plain

しかし、初心者にとってはエラーが起きている理由がわからないので、何の解決にもなっていません。数式が使える人は、初心者のためにロックして防御し続けなければなりません。お互いにストレスです。

 

(2)解決策はない

数式が入力されていることを知らせる方法として、行や列に色を塗ったり、項目名の色を変える方法があります。

f:id:waenavi:20181111121106j:plain

 

また、入力規則(入力時メッセージ)やコメントで注意を促す方法もあります。ある程度ミスを防げると思いますが、いくら警告をしたとしても完全に直打ちの可能性がなくなるわけではありません。

f:id:waenavi:20181111123233j:plain

 

7.結論

連番には2種類あって、固定が必要なコード番号の場合は普通にオートフィルで入力しますが、連動する番号の場合は数式を使います。

いままで数式で連番を振ったことが無いという方は、今すぐ、累積計算の練習をするべきです。いや、お願いですから、だまされたと思って1度練習してみてください。

 


解説は以上です。


8.動画版はこちら(無料)

この記事は、わえなびExcel新演習 Program2-1-2 の動画の内容を書き起こし、加筆修正したものです。

 

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ