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

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

Excelで連続するアルファベットやひらがな50音を入力する方法【オートフィル、CHAR関数、CODE関数】

Excelにはオートフィルで連続データを入力できる便利な機能があります(参考:オートフィルを利用した表の作成手順と入力練習)。1、2、3、・・・と入力できます。曜日(月、火、水・・・)や十二支(子、丑、寅・・・)なども入力できます。

しかし、アルファベットやひらがなを連続で入力することはできません。この点について、Excelオプションの「ユーザー設定リスト」に登録する方法を紹介しているサイトもあるようですが、そこに登録するためにはA、B、C・・・と手入力する必要があり、自動で入力したいのに手入力をするというのは本末転倒と言わざるを得ません。

そこで、今回は、Excel関数でアルファベットやひらがなを連続で入力する方法について出題します。

f:id:waenavi:20191102214936j:plain

目次

1.CHAR関数とCODE関数を用いた基本的な方法

(1)昇順の場合

問題

セルA1に「A」と入力した。セル範囲A2:A26に、B~Zの文字を関数で入力しなさい。

f:id:waenavi:20191102183521j:plain

 

解説

まずは、こちらの表をご覧ください。これは文字コードを抜粋して一覧表にしたものです(Shift_JISの10進数表記)。

f:id:waenavi:20190308231406j:plain

 

すべての文字には、文字コードと呼ばれる連続の番号があります(参考:【ExcelとCSV】本気で理解したい初心者のためのCSV勉強会資料)。例えば、「A」のコード番号は65番、「B」のコード番号は66番、「C」のコード番号は67番と決まっています。A~Zのコード番号は連続しています。

f:id:waenavi:20191102203026j:plain

 

「A」という文字に1を足すことはできませんが、コード番号に1を足すことは可能です。

f:id:waenavi:20191102184833j:plain

 

「A」から「B」を求めるには、いったん「A」を文字コードに変換して、それに1を足してから文字に戻すという計算をします。文字をコード番号に変換するのがCODE関数、逆にコード番号を文字にするのがCHAR関数です。

f:id:waenavi:20191102185225j:plain

 

 

まず、Aのコードを求めます。「=CODE(A1)」です。65になります。

f:id:waenavi:20191102185320j:plain

 

これに1を足します。66になります。

f:id:waenavi:20191102185345j:plain

 

これをCHARで囲みます。「=CHAR(CODE(A1)+1)」です。これで、Bになります。

f:id:waenavi:20191102185423j:plain

 

相対参照なのでオートフィルでC~Zが表示されます。

f:id:waenavi:20191102185440j:plain

 

横向きにオートフィルをする場合も同じです。

f:id:waenavi:20191102185507j:plain

 

最初の文字を「G」にするとH~Zが表示されます。

f:id:waenavi:20191102185530j:plain

 

最初の文字を小文字の「a」にすると、小文字のb~zが表示されます。コード番号が連続しているからです。

f:id:waenavi:20191102185554j:plain

 

最初の文字を全角の「A」にすると、全角大文字のB~Zになります。

f:id:waenavi:20191102185618j:plain

 

1つおきにするには+2にすればよいです。

  • =CHAR(CODE(A1)+2)

f:id:waenavi:20191102190005j:plain

 

*補足*アルファベットの文字コードは次の通りです。

  • 半角のA~Z・・・65番~90番
  • 半角のa~z・・・97番~122番
  • 全角のA~Z・・・9025番~9050番
  • 全角のa~z・・・9057番~9082番

 

(2)降順の場合

問題

セルA1に「Z」と入力した。セル範囲A2:A26に、Y~Aの文字を関数で入力しなさい。

f:id:waenavi:20191102185723j:plain

 

解説

逆の順にするためには文字コードを1ずつ減らせばよいです。したがって、「=CHAR(CODE(A1)-1)」となります。

f:id:waenavi:20191102185810j:plain

 

2.最初の文字が無かったらどうするか

問題

セルC3~C28に、A~Zの文字を入力しなさい。

f:id:waenavi:20191102191630j:plain

 

解説

連続するコード番号の文字を入力するためには「連番」が必要です。通常、縦方向の場合は行番号を返すROW関数、横方向の場合は列番号を返すCOLUMN関数を使います。
たとえば、ROW(A1)はセルA1の行番号なので1です。「A」のコード番号は65番なので行番号に64を足せばよいです。

  • =CHAR(ROW(A1)+64)

f:id:waenavi:20191102191543j:plain

 

ROW(A1)は相対参照なので下向きにオートフィルをすればB~Zが表示されます。

f:id:waenavi:20191102191622j:plain

 

ちなみに、「C」から始めたい場合は3番目の文字なのでA3にします。

  • =CHAR(ROW(A3)+64)

f:id:waenavi:20191102191704j:plain

 

また、右方向のオートフィルの場合はCOLUMNにします。

  • =CHAR(COLUMN(A1)+64)

f:id:waenavi:20191102191807j:plain

 

別解その1

「+64」というのは「A」のコード番号から1を引いたものなので、CODE("A")-1 と記述することもできます。「A」のコード番号が分からない場合にはこのように記述すればよいです。

  • =CHAR(ROW(A1)+CODE("A")-1)

f:id:waenavi:20191102191901j:plain

 

例えば、全角小文字の「d」のコード番号は9060番ですが、そんなことを知らなくても、=CHAR(ROW(A1)+CODE("d")-1)と入力すれば、d、e、f・・・と入力することができます。

f:id:waenavi:20191102191936j:plain

 

別解その2

自分自身の行番号ROW()を使う方法もあります。なお、3行目からオートフィルを開始するので3を引いていますが、コード番号を増やしたり減らしたりしてうまく調整する必要があります。

  • =CHAR(ROW()-3+65)

f:id:waenavi:20191102192715j:plain

 

3.文字列とセットの場合

問題

「商品A」「商品B」「商品C」・・・と入力しなさい。

f:id:waenavi:20191102192814j:plain

 

解説

A、B、C・・・が「=CHAR(ROW(A1)+64)」なので、これに文字列を連結すればよいです(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)。

  • ="商品"&CHAR(ROW(A1)+64)

f:id:waenavi:20191102192841j:plain

 

4.アルファベットを繰り返す

(1)AAABBBCCCと繰り返すパターン

問題

セル範囲A1:A26に「=CHAR(ROW()+64)」と入力するとA~Zが表示される。

f:id:waenavi:20191102193004j:plain

 

このことを利用して、AAABBBCCC・・・と3つずつ繰り返すように入力しなさい。

f:id:waenavi:20191102213727j:plain

 

解説

111222333のように3個ずつ同じものを繰り返すには行番号を3で割ってINT関数で切り捨てます。N個ずつ繰り返すには行番号をNで割ってINT関数で切り捨てます。ただし、何個かずれるので割る前に調整が必要です。

f:id:waenavi:20191102213911j:plain

 

123456を111222にするには、2を足してから3で割ります。

  • =CHAR(INT( (ROW()+2)/3+64))

f:id:waenavi:20191102213803j:plain

 

(2)ABCABCABCのパターン

問題

セル範囲A1:A26に「=CHAR(ROW()+64)」と入力するとA~Zが表示される。このことを利用して、ABCABCABC・・・と3つずつ繰り返すように入力しなさい。

f:id:waenavi:20191102214202j:plain

 

解説

同じものが3つ連続する場合はINTでしたが、3個のグループを繰り返す場合はMODです。N個ずつ繰り返す場合はNで割った余りです。ただし、何個かずれるので割る前に調整が必要です。

f:id:waenavi:20191102214057j:plain

 

123456を012012にするには1を引いてから3で割ります。

  • =CHAR(MOD( (ROW()-1),3)+65)

f:id:waenavi:20191102214227j:plain

 

(3)Zの次はAに戻るようにする

問題

セル範囲A1:A26に「=CHAR(ROW()+64)」と入力するとA~Zが表示される。セルA27以降もA~Zが表示されるようにしなさい。

f:id:waenavi:20191102214326j:plain

 

解説

このままオートフィルをすると、Zの次の文字コードにあたる記号が表示されてしまいます。

f:id:waenavi:20191102193043j:plain

 

A~Zを繰り返すということは26文字を繰り返すので、26で割った余りを求めたらよいです。

  • =CHAR(MOD( (ROW()-1),26)+65)

f:id:waenavi:20191102193551j:plain

f:id:waenavi:20191102193640j:plain

 

5.Excelの列番号のようにZの次をAAにする

問題

A、B、C・・・Zの次がAA、AB、ACとなるように入力しなさい。

f:id:waenavi:20191102193730j:plain

 

解説

(1)XFDまでの場合

Excelの列番号はZ列の次がAA列です。ZZの次はAAA列です。連番から列番号の文字を取得する関数はADDRESS関数です。「=ADDRESS(1,ROW(A1),4)」と入力します。第3引数の4は相対参照のことです。A1、B1、C1、・・・となります。

f:id:waenavi:20191102193819j:plain

 

Z1の次はAA1になっています。

f:id:waenavi:20191102193934j:plain

 

この「1」を削除すればよいです。SUBSTITUTE関数で置換します。

  • =SUBSTITUTE(ADDRESS(1,ROW(A1),4),1,)

f:id:waenavi:20191102195008j:plain

 

右方向の場合はCOLUMNにします。

  • =SUBSTITUTE(ADDRESS(1,COLUMN(A1),4),1,)

f:id:waenavi:20191102195103j:plain

 

ただし、ExcelはXFD列(16384列)までしかないので、それ以上はエラーになります。

f:id:waenavi:20191102195251j:plain

 

(2)XFEより先はどうするか

行番号が1~26^2については、A~ZZであることが分かっているので、26^2で割った商と余りで、ZZZZまで変換することが可能です。

  • =SUBSTITUTE(IFERROR(ADDRESS(1,INT(ROW(A1)/26^2),4),)&ADDRESS(1,MOD(ROW(A1),26^2),4),1,)

f:id:waenavi:20191102195808j:plain

f:id:waenavi:20191102195859j:plain

 

さらに、INTとMODを組み合わせれば、AAAAA以降もできますが数式は省略します。

 

6.ひらがなカタカナ50音の連続入力

問題

次の図のように半角カタカナ、全角カタカナ、全角ひらがなを50音順に入力しなさい。ただし、濁音、半濁音、拗音、促音、長音を含まない。

f:id:waenavi:20191102200510j:plain

 

解説

(1)半角カタカナ

半角カタカナは文字コード番号177番から始まります。そして、半角カタカナの場合、濁点(点々)や半濁点(丸)は別の記号になっています。

f:id:waenavi:20191102202937j:plain

 

したがって、アルファベットと同じように177番から始まるように行番号を調整すればよいです。

  • =CHAR(ROW(A1)+176)

f:id:waenavi:20191102201214j:plain

f:id:waenavi:20191102212024j:plain

 

ただし、ワとンの間にヲがありません。

f:id:waenavi:20191102201328j:plain

 

「ヲ」は別のところにあって連続していません。

f:id:waenavi:20191102202910j:plain

 

ワ、ヲ、ンの順にしたいのであれば若干の調整が必要です。

  • =CHOOSE(SIGN(ROW(A1)-45)+2,CHAR(ROW(A1)+176),"ヲ","ン")

f:id:waenavi:20191105122430j:plain
f:id:waenavi:20191102202347j:plain

 

(2)全角カタカナ

全角カタカナは9506番から始まりますが、小さい文字や濁音が入ります。

f:id:waenavi:20191102211402j:plain

 

そこで、半角カタカナを全角に直すことを考えます。半角文字を全角に変換する関数は、JIS関数です。JIS()で囲むだけです。

  • =JIS(CHAR(ROW(A1)+176))

f:id:waenavi:20191102203348j:plain

 

ちなみに、セルA1に全角カタカナ1文字を入力したときに、次の全角カタカナを求めるにはいったん半角にしてからコード番号に1を足して全角に戻します。半角にする関数はASC関数です。

f:id:waenavi:20191102203828j:plain

  • 次の全角カタカナ:=JIS(CHAR(CODE(ASC(A1))+1))
  • 前の全角カタカナ:=JIS(CHAR(CODE(ASC(A1))-1))

f:id:waenavi:20191102204022j:plain

 

(3)全角ひらがな

全角ひらがなは9250番から始まりますが、小さい文字や濁音が入ります。

f:id:waenavi:20191102211505j:plain

 

全角ひらがなの文字コードは、全角カタカナの文字コードより256少ないだけなので、全角カタカナを文字コードに変換して、256を引いてから文字に変換すればよいです。

  • =CHAR(CODE(JIS(CHAR(ROW(A1)+176)))-256)

f:id:waenavi:20191102212158j:plain

 

ちなみに、セルA1にひらがな1文字を入力したときに、次の全角ひらがなを求めるには文字コードに256を足し、半角にしてからコード番号に1を足し、全角に戻して文字コードから256を引きます。

  • =CHAR(CODE(JIS(CHAR(CODE(ASC(CHAR(CODE(A1)+256)))+1)))-256)

f:id:waenavi:20191102205439j:plain

f:id:waenavi:20191102212748j:plain

 

7.応用問題:3つ後の文字

問題

セルA1にアルファベットを入力した。3つ後の文字を求めなさい。

f:id:waenavi:20191102213220j:plain

 

例えば、Aの3つ後はDであり、X、Y、Zの3つ後はA、B、Cである。

f:id:waenavi:20191102213110j:plain

 

解説

Aの3文字後はDですが、文字コードに変換して3を加算します。

  • =CHAR(CODE(A1)+3)

f:id:waenavi:20191102213352j:plain

 

XYZはABCにする必要があるため、26で割った余り(MOD)が必要です。65を引いて、MODを求めてから65を足します。

  • =CHAR(MOD(CODE(A1)+3-65,26)+65)

f:id:waenavi:20191102213421j:plain

 

ちなみに3つ前は「+3」を「-3」にするだけです。

  • =CHAR(MOD(CODE(A1)-3-65,26)+65)

 


解説は以上です。


 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]