MOD関数でエクセルの「列数と列番号」を対応させる
MOD関数は使い方次第では非常にパワフルです。先週の『MOD関数を使って「曜日」を特定する超絶テクニック』では、エクセルの日付設計の特徴を利用し、「割り算の余り」を返す「MOD関数」を使って「曜日を数値的に特定する手法」を取り上げました。そして、このMOD関数、意外と強力な使い方がほかにもいくつかあります。本稿は『エク達』というウェブサイトからの転載コンテンツです。
| エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。 |
MOD関数の威力
『MOD関数を使って「曜日」を特定する超絶テクニック』では、エクセルの日付設計の特徴を利用し、「割り算の余り」を返す「MOD関数」を使って「曜日を数値的に特定する手法」を取り上げた。
この「MOD関数」、エクセルの基本的な教科書でさほど多く出てくる関数ではないが、実務上は大変に「使える」関数でもある。というのも、「一定周期で現れる数列、文字列など」を出現させるのに、うまく活用できるからだ。
改めて説明すると、「MOD関数」の書式は、次のとおりである。
MOD(数値,除数)
これは、「数値」を「除数」で割った余りを返す関数であり、たとえば、「数値」に「９」、「除数」に「７」を指定してやると、答えは「２」である（図表１）。
図表１ MOD関数の実例
なお、この場合、「数値」が「９」以外であっても、たとえば「16」「23」「30」など、７つごとに「余り」が「２」になる数値が出現する。この特徴を使い、「エクセルのシリアル値を７で割った余り」で曜日を判定する、というのが、『MOD関数を使って「曜日」を特定する超絶テクニック』でいう「超絶テクニック」だった。
- ０…土曜日
- １…日曜日
- ２…月曜日
- ３…火曜日
- ４…水曜日
- ５…木曜日
- ６…金曜日
※なお、似た関数に「WEEKDAY関数」なるものもあるが、こちらのMOD関数の方が、入力に際しての文字数も少なく、扱いやすいため、オススメである。
アルファベット文字列を繰り返す
このMOD関数、強力な使い方はこれだけではない。次に紹介したいのが、「アルファベット文字列を繰り返す」という使い方だ。
サンプルシートを準備しよう（図表２）。
図表２ サンプルシート
サンプルシートのA2セル、B2セルには、各々次のような数式が入力されている。
- 【A2セル】=ROW()-1
- 【B2セル】=MOD(A2-1,26)+65
これは、こういう意味だ。
「A2セルの値から1を引いたものを26で割った余りに65を足せ」。
いったい、これにどういう意味があるのか。
これは、同じシートに表示されている、実際の計算結果を見ていただく方が早いだろう。
図表２のB2セルの値は「65」と表示されているはずだ。そして、順次、「B3」セルが「66」、「B4」セルが「67」という具合に、ひとつずつ値が増えて行く。この数式を続けていくと、「B27」セルで数値が「90」にまで増え、次の「B28」セルで「65」に戻っていることが確認できるだろう。
では、この「65」、いったいどういう意味があるのか。
さきほどの図表２で作ったサンプルシートのC2セルに、さらにこんな計算式を入力し、それをC3以下のセルにコピーしてみよう。
=CHAR(B2)
これは「CHAR関数」と呼ばれ、使用しているマシンの文字セットのコード番号に対応する文字を返す関数だ。具体的には「C2」セルに「A」という文字が出現し、以下、順次「B」「C」「D」…、が表示されるのが確認できるだろう（図表３）。
図表３ CHAR関数の入力結果
ポイントは、27行目の「Z」の次は、28行目で再び「A」が出てくることだ。そのカギはもちろん、MOD関数でアルファベットの数である「26」を使用していることにある。
エクセルの列名称と列数の対応表も簡単！
さらに、「D28」「E28」「F28」セルに各々次の計算式を入れ、以下のセルにコピー＆ペーストしてみよう。
- 【D28セル】=TRUNC((ROW()-2)/26)+64
- 【E28セル】=CHAR(E28)
- 【F28セル】=E28&C28
すると、今度はこんなシートが出来上がる（図表４）。
図表４ 完成形
F列（図表５の赤枠で囲った部分）は、じつは、エクセルのシートの列番号でもある。
エクセルのシートは、列は「A列」、「B列」、…、という具合に、アルファベットで表示されている（※もちろん、変更する方法はある）。そして、列が「Z列」までいくと、その次は（なぜか）「AA列」になるのだ。
すなわち、このシートは、エクセルの列数と列名称の対応表になっていて、INDIRECT関数などと組み合わせて使ううえで、じつは、大変便利なシートでもあるのだ。
是非、ご活用いただきたい。
| エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。 |
※【重要】ご注意：他サイトの文章の転載は可能な限りお控えください。
やむを得ず他サイトの文章を引用する場合、引用率（引用する文字数の元サイトの文字数に対する比率）は10％以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。
※現在、ロシア語、中国語、韓国語などによる、ウィルスサイト・ポルノサイトなどへの誘導目的のスパムコメントが激増しており、その関係で、通常の読者コメントも誤って「スパム」に判定される事例が増えています。そのようなコメントは後刻、極力手作業で修正しています。コメントを入力後、反映されない場合でも、少し待ち頂けると幸いです。
※【重要】ご注意：人格攻撃等に関するコメントは禁止です。
当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、ＵＲＬの入力は必要ありません（メールアドレスは開示されません）。ブログ、ツイッターアカウントなどをお持ちの方は、該当するＵＲＬを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。
【おしらせ】人生で10冊目の出版をしました
【おしらせ】人生で９冊目の出版をしました
関連記事・スポンサーリンク・広告