Excel
基本的には Microsoft の製品は使いたくない(Windows2000だけは気に入って愛用している)のだが、仕事上 Word や Excel は避けて通れない。
ここでは、私が Excel で使った技(と言うほどのものでは全然ない)を紹介する。もし、もっと簡単にやる方法があったら教えて欲しい。
セル(範囲)を指定する
A1形式とR1C1形式
セルの位置を指定するには、列と行を指定する。Excelのシートの画面では、列がA, B, C などのアルファベット。行が1,2,3の数字で表されている。
セルの位置を指定するには、この列のアルファベットと行の数字を続けて書けばよい。たとえば、５列目の４行目なら、「E4」となる。(これをA1形式と呼ぶ)
同じことを、「R4C5」と書くことができ、こちらをR1C1形式と呼ぶ。(R1C1形式を使うには、[ツール]->[オプション]->[全般]の[R1C1参照形式を使用する]のチェックをonにする必要がある)
絶対参照と相対参照
A1形式では、たとえば B1 のセルに、=A1*10 と書いてあるときに、このセルをB2にコピーすると、B2の中身は =A2*10 となる。これは、A1形式のデフォルトが相対参照だから。
常に同じ場所を指定したい場合は、$を前に付ける。同じように、C1のセルに =A$1*10 と書いてあるときに、C1の内容をC2にコピーしても、C2の内容は =A$1*10 のままである(絶対参照)。
ただし、C1の内容をD1にコピーすると、=B$1*10 となる。これは、行を指定する1には$を付けてあるが、列を指定するAには$を付けていないため。このように、行と列のそれぞれを絶対参照にするか相対参照にするか選択できる。
これに対して、R1C1形式は、デフォルトで絶対参照である。そして、R1C1形式の相対参照は、数字の部分を[]で囲む。ただし、[]の中には、対象のセルからの相対位置を書く。
たとえば、E4のセルから、E3のセルを指定するには、=R[0]C[-1] となる。
セル範囲の指定は、セルの指定を:または,で並べる。:は、:の左のセルから:の右のセルまでのすべてのセル、,は、それぞれのセルを個別に指定したことになる。
たとえば、A1:A5はA1,A2,A3,A4,A5と同じ。
数える
仕事で、お客さんが作成したテスト仕様書を使ってテストを行うことになった。毎日テストを実施し、その結果を報告する。最初は、手で何件のテストをこなしたか数えていたのだが、さすがに馬鹿らしいので Excel に数えさせることにした。
元のシート(デフォルメしてある)
| A | B | C | D | E |
|---|
| 1 | 項番 | 試験内容 | 確認内容 | 確認日 | 判定 |
|---|
| 2 | 1 | こ〜ゆ〜試験をやって | これを確認して | 11/28 | OK |
|---|
| 3 | | | あれも確認して | 11/28 | NG |
|---|
| 4 | 2 | あ〜ゆ〜試験をやって | それも確認して | 11/29 | N/A |
|---|
| 5 | | | どれも確認して | 11/29 | N/A |
|---|
- 特定の日付のOK, NG, N/Aの数
- まず、以下のように列を追加して、OK, NG, N/Aを数値化した。
| A | B | C | D | E | F | G | H |
|---|
| 1 | 項番 | 試験内容 | 確認内容 | 確認日 | 判定 | OK | NG | N/A |
|---|
| 2 | 1 | こ〜ゆ〜試験をやって | これを確認して | 11/28 | OK | 1 | 0 | 0 |
|---|
| 3 | | | あれも確認して | 11/28 | NG | 0 | 1 | 0 |
|---|
| 4 | 2 | あ〜ゆ〜試験をやって | それも確認して | 11/29 | N/A | 0 | 0 | 1 |
|---|
| 5 | | | どれも確認して | 11/29 | N/A | 0 | 0 | 1 |
|---|
Fの列には、F2: =IF($E2="OK",1,0)のような式を入力する。G, Hもそれぞれ"OK"を"NG", "N/A"に変えて同じ。
続いて、=SUMIF($D$2:$D$65535, "11/28", $F$2:$F$65535)のようなセルを作ると、11/28日のOKの数を数えることができる。
- 確認項目の数
- 単純に、I列に1を入れた。
| A | B | C | D | E | F | G | H | I |
|---|
| 1 | 項番 | 試験内容 | 確認内容 | 確認日 | 判定 | OK | NG | N/A | 確認項目数 |
|---|
| 2 | 1 | こ〜ゆ〜試験をやって | これを確認して | 11/28 | OK | 1 | 0 | 0 | 1 |
|---|
| 3 | | | あれも確認して | 11/28 | NG | 0 | 1 | 0 | 1 |
|---|
| 4 | 2 | あ〜ゆ〜試験をやって | それも確認して | 11/29 | N/A | 0 | 0 | 1 | 1 |
|---|
| 5 | | | どれも確認して | 11/29 | N/A | 0 | 0 | 1 | 1 |
|---|
そして、=SUM($I$2:$I$65535)のセルを確認項目数とした。
- 項番の数
- 非常に馬鹿らしくて申し訳ないのだが、項番の数。
最初は、=MAX($A$2:$A$65535)で計算していたのだが、項番のフィールドに重複があることがわかった。すでに仕様書は印刷済みなので、番号が変わるのは望ましくない。
そこで、J2: =IF($A2<>"", 1, 0)のようにして、
| A | B | C | D | E | F | G | H | I | J |
|---|
| 1 | 項番 | 試験内容 | 確認内容 | 確認日 | 判定 | OK | NG | N/A | 確認項目数 | 項目数 |
|---|
| 2 | 1 | こ〜ゆ〜試験をやって | これを確認して | 11/28 | OK | 1 | 0 | 0 | 1 | 1 |
|---|
| 3 | | | あれも確認して | 11/28 | NG | 0 | 1 | 0 | 1 | 0 |
|---|
| 4 | 2 | あ〜ゆ〜試験をやって | それも確認して | 11/29 | N/A | 0 | 0 | 1 | 1 | 1 |
|---|
| 5 | | | どれも確認して | 11/29 | N/A | 0 | 0 | 1 | 1 | 0 |
|---|
=SUM($J$2:$J$65535)のセルを項目数とした。（これは、後で項番が正しく昇順になるように採番しなおしたことを報告しておく）
false@wizard-limit.net