Excelの小技(8)オートフィル

オートフィルという機能がある。連続する数字や文字を、自動的に入力する機能である。
到底「技」と呼ぶべくもない至極当り前のものではあるが、知っているのと知らないのとでは、効率が断然違う。

使い方としては、たとえば、一番上のセルに「1月」と入力して、

autofill

カーソルをセルの右下に合わせて「+」印が出るようにした状態でドラッグする。

autofill

と、

autofill

連続した月のセルが出来上る。
「+」印が赤いのは、判りやすくするためであって、実際には色は変わらない。

なお、数字だけの場合、一番上のセルに「1」と入れて引っ張るとすべてのセルが「1」になってしまうので、「1 2 3…」という連番を振りたい場合には「CTRL」を押しながらドラッグする。

さて、冒頭で「数字や文字」と書いたように、オートフィルは文字にも有効である。
「月曜日」と入力して、同様にドラッグすると、以下のようになる。

autofill

もちろん連続したことばのないものは、当然連続しない。

「田中」は、

autofill

あくまでも「田中」であるが、一々入力しなくとも、ドラッグした限りのセルが「田中」になる。

autofill

次に、作業が完了している月に「完」、完了していない月に「未」と入れることにする。
現時点ではまだ完了している月がなく、すべてに「未」と入れたい。こういう場合に、オートフィルが役に立つ。
そこで、一番上のセルに「未」と入れて、

autofill

オートフィル!

autofill

…ありゃ?!

いや、ただそれだけ。

Excelの小技(7)バーコードを出力する(その3)

Excelの小技(6)バーコードを出力する(その2)」の続き。

「Microsoftバーコードコントロール」が出て来ない理由は、どうやら Access が入っていないためらしい。そう言えば、前にバーコードを作成したPCには、確かに使いもしない Access が入っていた。
とは言え、「Excelの小技」として書き始めた以上、「皆さんAcsessをインストールして試してください!」で済ませるわけにも行かない。

そこで、物は試し、Access Runtime をダウンロードしてみた。Access Runtime は、Access がインストールされていないPCでも、Access データベースを動作させることのできるアプリケーションで、Microsoft の公式サイトから無料でダウンロードができる。

ダウンロードしてみると、「コントロールの選択」に、「Microsoft バーコードコントロール 14.0」というものが現われた。以前僕が目にしたのは「9.0」くらいだった記憶があるから、ずいぶん進化したものである…と言っても、大したことをするわけではないので、違いはないだろう。

JANCODE

その「Microsoft バーコードコントロール 14.0」を選択すると、カーソルが範囲指定をする状態になるから、適当な大きさにドラッグする。

すると、バーコード状のものが出て来るので、その上で右クリック → 「プロパティ」をクリックする。

JANCODE

「LinkedCell」の右側の欄に、コードが入ったセル番号、ここでは「B2」を入れる。

JANCODE

「B2」セルに入っている数値のバーコードが、出力される。

JANCODE

バーコードリーダーで読めば、きちんとコードを認識してくれる…はず。手許にバーコードリーダーがないから、確認はしていないけれど…。

Excelの小技(6)バーコードを出力する(その2)

Excelの小技(5)バーコードを出力する(その1)」の続き。

まず、左上のOfficeマークをクリックして、出て来たプルダウンから「Excelのオプション(I)」を選択。

JANCODE

基本設定 → 「開発」タブをリボンに表示する にチェックを入れる

JANCODE

すると、こんな

JANCODE

だったところに、「開発」という文字が出て来る。
なお、この「開発」で選択できる機能は、以前ならツールバーに入っていたものも多いから、バーコード云々は別として、表示しておいた方が、何かと便利である。

JANCODE


それをクリックして、さらに、「挿入」→ 一番右下の「コントロールの選択」アイコンをクリック。
すると、「Microsoftバーコードコントロール」というのが出て来るはずなので、それを選択…

JANCODE

…あれっ、ない!

Excelの小技(5)バーコードを出力する(その1)

蔵書やCDの管理にバーコードを導入したいと思っている人も、もしかしたらいるかもしれない。いや、きっといるに違いない。絶対にいる。
そういう人のために、コードを生成してバーコードを作成する方法を書く。

JANCODEと呼ばれる日本の標準規格があって、国内で流通している商品には、たいていこのJANCODEが付いている。
どうせならそういうきちんとした体系に則ったコードで管理したいし、バーコード作成のためのソフトでは、きちんとしたコードでないと出力できないものも少なからずあると思われる。
JANCODEには8桁のものも存在するが、13桁が一般的である。そこで、ここでは13桁のコードを前提に、説明しよう。

13桁の数字を、仮に「ababababababX」とする。むろん実際には、「a」「b」「X」はいずれも数字だと思って欲しい。
「a」「b」は任意の数字を入れて良いのだが、「X」はチェックデジットと言って、法則に則った数字が入る。適当な数字を入れると、JANCODEとしては無効な数字になる。

チェックデジットの計算方法は、

1 すべての偶数位置の数字を加算する。
2 1の結果を3倍する。
3 すべての奇数位置の数字を加算する。
4 2の答えと3の答えを加算する。
5 最後に"121"の下1桁の数字を"10"から引く。この場合は"10"から"1"を引き算した答えの"9"がチェックデジットである。
6 下1桁が"0"となった場合は、チェックデジットはそのまま"0"となる。("0"の場合は"0")


というかなり面倒臭いものである。(流通システム開発センターHPより。例は上12桁が「456995111617」の場合)

そこで、まず答えを先に書いておくと、B2セルに下記の計算式を入力して、A2セルに任意の12桁の数字を入力すれば、チェックデジット付きの13桁のJANCODEを生成することができる。
何故A2・B2かというと、A1・B1セルにはヘッダーを付ける想定だからである。ヘッダーが要らなければ、「2」を「1」に修正してもらって構わない。

=A2&RIGHT(10-RIGHT((MID(A2,1,1)+MID(A2,3,1)+MID(A2,5,1)+MID(A2,7,1)+MID(A2,9,1)+MID(A2,11,1)+(MID(A2,2,1)+MID(A2,4,1)+MID(A2,6,1)+MID(A2,8,1)+MID(A2,10,1)+MID(A2,12,1))*3),1),1)


この計算式は先の手順「5」 のとおりでなく、手順「4」から10を引いた数の下1桁になっている程度の違いはあるのだが、結果は同じである。
計算式を細かく説明しても仕方がないのだが、さわりだけちょっと見ておくと、

 A2&RIGHT(10-(…),1) ―― A2セルと以下の計算結果の1番右の数字を繋げる
 MID(A2,1,1)+MID(A2,3,1)+… ―― A2セルの1桁目から1桁+A2セルの3桁目から1桁+…

と、いうようなことである。

Excelの小技(4)曜日を自動入力する

エクセルを使っていて、一つのセルに日付と曜日を纏めて入力したいと思ったことはないだろうか? いや、きっとあるのに違いない。
たとえば、A1セルに「6/5」、B2セルに「水」と入力すればそれで事足りると言えば足りるのだが、これが過去日付だったりすると、一々曜日を調べるのも面倒くさい。できれば自動的に入力したい。

その場合、日付を入力するセルを右クリックして「セルの書式設定」をクリックする。

excel4_1

「表示形式」で、「ユーザー書式」を選択して、右側に出て来る「種類」の欄に「m"/"d(aaa)」と入力する。

excel4_2

さて、そのセルに戻って日付を入力…

excel4_3

すると…、

excel4_4

意図的に別の「年」を入力しなければ自動的に当年の日付になるので、2013年1月1日、すなわち火曜日が、自動的に表示されるのである。
当然ながら、日付を「1/2」にすれば「水」が、「1/3」にすれば「木」が、それぞれ自動で入力される。

Excelの小技(3)特定の文字を含む場合に印を付ける

no10.jpg

この10人の中から、「藤」の付く名前の人だけを選びたい。
ここは定番のif式を使ってみよう。

=if(a2="*藤*","○","×")


a2セルが「*藤*」の場合は「○」を、違う場合は「×」を入れろ、という指定である。
「*」はワイルドカードと言って、不特定の文字列を指す。「*藤*」なら、「藤」の前後の文字は関係なく、途中に「藤」の入っている文字列を認識する。「後藤田」のように「藤」の前後に別の文字が入っているものは勿論、「加藤」とか「藤原」とか、前後どちらかに別の文字が入っているものでも、「藤」単独のものでも良い。これで、「藤」の付く人には「○」が、そうでない人には「×」が付く…はずである。
それでは、実際にこの式を入れてみよう。

no11

佐藤一郎君にも伊藤六郎君にも加藤十郎君にも「藤」が付いているのに、全員「×」になってしまった。
理由は判らないが、if式の中にワイルドカードは使えないようで、「*藤*」ごと文字列として認識されてしまう。本当に「*藤*」という名前の人がれば「○」が付くけれども、無論のことそんな人はいない。

そこで、countif式を使う。
countif式というのは、ある特定のセル(これを引数という)の中に、指定した文字や数字が入ったものが何個あるかを数えるためのものである。
たとえば、

=COUNTIF(B2:B11,"欠")


という式をB12セルに入れると、B2セルからB11セルの間に「欠」と入ったセルがいくつあるか、つまり、10人の中で欠席の人が何人いるか、数えてくれる。

no12.jpg

これが、counif式の一般的な使い方だろう。

ここで最初のテーマに戻ると、まず、

=countif(a2,"*藤*")


という式を使う。a2セルに「藤」の入っている文字列がある個数をカウントする式である。この式には引数が1つしかないから、答えは「1」か「0」しかない。なお、これはセルの個数を数える式だから、仮に「藤村藤男」というセルがあったとしても、答えは「1」になる。

no13.jpg

次に、b2セルが「1」なら「○」、そうでなければ(「0」なら)「×」の式をc2セル以下に入れる。

=IF(B2=1,"○","×")


no14.jpg

これで、名前に「藤」の付く人には「○」、付かない人には「×」が付いた。
当初の目的は達したのだが、計算式をもう少しスマートにしたい。そこで、最初のcountif式と次のif式を組み合わせる。

=if(countif(a2,"*藤*")=1,"○","×")


a2セルに「藤」の文字が入っていれば「○」、入っていなければ「×」を入れる、という式になる。

no15.png

なお、この数式のように引数が1つしかなく、答えが「1」(TRUE)か「0」(FALSE)しかない場合、「=1」を省略して、

=if(countif(a2,"*藤*"),"○","×")


とすることもできる。が、どういう場合に省略可能になるのかを考えるくらいなら、何も考えずに「=1」を入れてしまった方が楽だろう。ただし、もしこの計算式を入れたシートを他の人に渡すのなら、「=1」を省略した方が格好良いかもしれない。

Excelの小技(2)得点に合った評価を表示する

Excelの小技(1)」の続編。

前回に続いて、掛け値なしの「小技」。
ここまで小手調べ技だと、忘れることもなく、調べ直すまでもない。だとすれば、書いておく意味もあまりないのだが、あまりにも一般的でないものもどうかと思って書くことにした。

サンプル1

さて、こんな表に得点は入力したけれども、それを評価に直さなければならない、ということが起こった場合。

まず、90点以上に「S」を付けたい。その場合にはif式を使う。

=IF(B2>90, "S","×")


「B2>90」という条件に合う場合は「S」という文字を、合わない場合は「×」という文字を入れる、という式である。「"」で囲っているのは、数値ではなく文字だということ。「"」がないとExcelが理解してくれなくてエラーになるので必須である。
この式を「C2」セルに入力して、それを「C11」セルまでドラッグしてコピーする。

サンプル2

とりあえず出来たように見える。が、良く見ると、中村八郎君は90点なのに「×」が付いてしまっている。これは「B2>90」が「B2が90より大きい」だからである。「B2>89」にすれば、この例のように整数しかない場合には問題ないが、小数点以下がある場合、得点が89.1点の場合にも「S」になってしまう難点がある。かと言って、「B2>89.99999999…」などとするのも如何なものか。
そこで、

=IF(B2>=90, "S","×")


というふうに、「B2」と「90」を「>=」で結んでやる。すると、「B2が90より大きい」または「B2が90と等しい」、つまり、「B2が90以上」ということになる。

サンプル3

これで中村八郎君も、無事に良い評価を獲得することができた。が、これでは「S」評価以外の人は落第になってしまう。同時に、80以上を「A」に、70以上を「B」に、60以上を「C」にしたい。

最も簡単に思いつくのは、上に倣って「B2が80以上なら『A』」「B2が70以上なら『B』」「B2が60以上なら『C』」という式、

=IF(B2>=80, "A","×")

=IF(B2>=70, "B","×")

=IF(B2>=60, "C","×")


を、横のセルに追加して行くやり方である。

サンプル4

一応はできた。が、これでは、伊藤六郎君が落第なのは判るが、後は誰がどんな評価なのか、一見して判りにくい。こんなことならExcelを使う意味がないから、「評価」欄に、「評価2」~「評価4」欄の分まで、すべて集約したい。

こういう場合には、if式を入子にするのが有効である。
最初に使った「=IF(B2>=90, "S","×")」という式は、判りやすく言えば「B2」が90以上という条件(TRUE)があって、それに合致しない場合(FALSE)は「×」を入れる、ということである。
この式は、「B2>=90」が条件で、「TRUE」が「S」、「FALSE」が「×」である。この「FALSE」の部分を「×」ではなく『80以上は「A」・未満は「×」』というif式(IF(B2>=80, "A","×")に変えてやれば良いのである。

=IF(B2>=90, "S",(IF(B2>=80, "A","×")))


「B2>=90」という条件に合致しない場合、「IF(B2>=80, "A","×"」の計算をせよ、ということである。
更に、この「×」に『70以上は「B」・未満は「×」』の式を入れ、更に更に、その「×」に『60以上は「C」・未満は「×」』を順々に入子にして行く。

=IF(B2>=90, "S",(IF(B2>=80, "A",(IF(B2>=70, "B","×")))))

=IF(B2>=90, "S",(IF(B2>=80, "A",(IF(B2>=70, "B",(IF(B2>=60, "C","×")))))))


これをやっていると、しばしば閉じ括弧の数を間違えるのだが、その程度ならExcelが勝手に修正してくれるから問題ない。
最後の式を「C2」セル以下に入れると、結果、こんな表が出来上る。

サンプル5

「A」を付ける条件に「80以上」のほかに「90未満」という条件を設けなくても良いのか、「B」を付ける条件に…(以下同)と思う人がいるかもしれないが、そして、それ自体はor式を使えばできるのだが、この場合にはそこまでする必要がない。最初に「90以上」なら「S」を入れるとしているので、「A」を入れる式は「FALSE」すなわち90未満の数だけが対象になっている。更に「B」を入れる式は「FALSE」すなわち80未満の数だけが対象となっており…(以下同)というわけである。

同じ結果を得るためのもっとシンプルな式はきっとあるのだろうが、素人の僕が自力で作るものとしては、このくらいのものが限界だし、かつ、このくらいのもので十分である。

Excelの小技(1)連番で行番号を振る

Excel でちょっとした表を作ろうとすると、タマにしか使わない簡単な計算式に限って忘れていることが多々ある。完全に忘れ去っているわけではなく、何となくは覚えているのだが、「=」だの「,」だのが漏れただけで、思い通りの結果にはならない。それで、改めて調べることになるのだが、それもどうにも面倒だ。
そこで、そんな時の備忘のために、思い立ってメモしておくことにした。メモすることで、忘れにくくなるだろうという目論見もある。つまり、自分のためである。
もっとも、MOS(マイクロソフト・オフィス・スペシャリスト)などとは程遠い、一介の素人の書くことだから、他人が見ても、初歩的過ぎて大して役には立たないだろう。が、僕と同程度のレベルの人になら、かえって専門家の解説よりも判りやすいということがあるかもしれない。

まずは、小手調べである。
小手調べにも程がある、と言われるかも知れないが、滅多に使わないから、微妙に覚え違いをしていて上手く行かないことがある。こんなものをその度に調べるのも馬鹿々々しいことこの上ないから、これを第1弾とする。

行番号を連番で振る方法。

最も簡単なのは、一番上の行に「1」と入れて、

行No

それを、セルの右下隅にカーソルを合せて「+」型になった状態でドラッグすると、

No2.jpg

最初のセルの内容がコピーされる…のだが、それだと、

No3

というように、同じ数字が入ってしまう。そこで、「CTRL」キーを押しながら先ほどと同じ操作をすると、右下の四角の中に出る数字が、下に動かすに連れて増えて行く。

No4

その状態で、適当な所で離すと、

No5

というように、連番で振られることになる。

以上、終わり…では幾らなんでも酷いので、本題を書く。

上記の方法は、安直ではあるものの、問題がある。それは、ある程度表を作ったところで途中の行を削除すると番号が飛ぶし、並べ替えをするとバラバラになるから、改めて振り直さなければならない、という点である。
そうならないためには、行番号を固定しない方法がある。行番号を入れたいセルに、

=ROW()


という式を入れるのである。これは、行番号に、欄外の数字と同じものを振る式である。

No6

が、ふつう、一番上の行には列タイトルを入れるから、実際には1行目からデータが始まるわけではない。
その場合、

=ROW()-1


という式を入れる。欄外の数字より「1」少ない番号を振る、ということである。

No7

画像では、数字を入れているのか計算式を入れているのかは判らないが、実際、「=ROW()-1)」を入れて作っている。
なお、2行目ではなく3行目から始めたければ「-2」にすれば良いし、5行目から始めたければ「-4」にすれば良い。

さて、これだけではいくらなんでも簡単過ぎるので、少しだけ手の込んだことを書いておく。

=IF(B2=0,"",ROW()-1)


「A2」セルにこの式を入れて下の方までずっとドラッグしてコピーする。すると、その時点では、この計算式の入っているセルには何も表示されない。

no9

本当に計算式が入っているかどうか判らないだろうが、本当に入っているのである。それが、「B2」セルに名前を入力すると、

no8

「A2」セルに行番号が表示される。「B3」セル以下も同。
if式を使って、もし「B2」セルが「0」と等しければ「""」つまり空欄に、それ以外の場合は「ROW()-1)」を入れろ、ということである。

もうひとつついでに。
どこかのセルに次の式を入れておく。

=LOOKUP(99999,A:A)


そうすると、A列に入っている一番最後の数字が何番かを表示することができる。

最初に断った通り、大したことはない。