┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.011==│
└──────────────────────────┘
◆ 今日の問題 「特定の範囲を繰り返す数字を作る」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下の図のように、A列は、7で折り返し、B列は、5で折り返しなが
ら数字を繰り返したい。どうすればいいか?ただし、A1のセルとB1の
セルに「1」を手入力し、A2とB2にそれぞれ関数を入れて、 フィルハ
ンドルでコピーするものとする。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃ 1│ 1│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 2│ 2│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ 3│ 3│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 4│ 4│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃ 5│ 5│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃ 6│ 1│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃7┃ 7│ 2│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃8┃ 1│ 3│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃9┃ 2│ 4│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃10┃ 3│ 5│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃11┃ 4│ 1│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃12┃ 5│ 2│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃13┃ 6│ 3│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃14┃ 7│ 4│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃15┃ 1│ 5│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃16┃ 2│ 1│ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
A2のセルに、=MOD(A1,7)+1 と入力
B2のセルに、=MOD(B1,5)+1 と入力
A2とB2のセルを選択して、フィルハンドルでドラッグ
─────────────────────────────────
・今回のように、繰り返す数字が決まっているときは、割り算の余りを
求める MOD関数を応用することができます。割り切れるときは、ゼロ
になりますから、1をプラスすることにより調節します。
・この式を分析すると、
2行目は、=MOD(A1,7)+1 余り1 +1 結果 2
3行目は、=MOD(A2,7)+1 余り2 +1 結果 3
4行目は、=MOD(A3,7)+1 余り3 +1 結果 4
5行目は、=MOD(A4,7)+1 余り4 +1 結果 5
6行目は、=MOD(A5,7)+1 余り5 +1 結果 6
7行目は、=MOD(A6,7)+1 余り6 +1 結果 7
8行目は、=MOD(A7,7)+1 余り0 +1 結果 1
9行目は、=MOD(A8,7)+1 余り1 +1 結果 2
10行目は、=MOD(A9,7)+1 余り2 +1 結果 3
11行目は、=MOD(A10,7)+1 余り3 +1 結果 4
12行目は、=MOD(A11,7)+1 余り4 +1 結果 5
13行目は、=MOD(A12,7)+1 余り5 +1 結果 6
14行目は、=MOD(A13,7)+1 余り6 +1 結果 7
15行目は、=MOD(A14,7)+1 余り7 +1 結果 1
16行目は、=MOD(A15,7)+1 余り0 +1 結果 2
・=MOD関数の公式は、=MOD(数値,除数) でした。この場合は、一つ上の
セルの値を7で割った余りに1を加えているのがわかりますね。B列
もまったく同じですので確認してください。
・よって、このような繰り返しをする場合の公式は
=MOD(一つ上のセル番地,上限値)+1
ということになります。試してみてください。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・割り算の余りの学習は、小学生で終わってしまいますね。中学生にな
ると、ほとんどやりません。しかし、日常生活では、非常に重要な観
念のようです。例えば、曜日は、7で割った余りで区別することがで
きますし、5人のグループならば、5で割った余りで区別することが
できます。いろいろ応用してみてください。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.012==│
└──────────────────────────┘
◆ 今日の問題 「600以上を切り上げる」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下の図のように、元の値の100の位が、600以上の場合は切り上げ、
600未満の場合は切り捨てしたい。B2に関数を入れ、コピーして完成
させなさい。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃元の値 │修正値 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 5200│ 5000│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ 13620│ 14000│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 445600│ 446000│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃ 94900│ 95000│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃ 5500│ 5000│ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B2のセルに =ROUNDDOWN(A2+400,-3) と入力
B2のセルを選択して、フィルハンドルでダブルクリック
─────────────────────────────────
・600以上の数字は、400を加えると繰り上がりますね。この性質を利用
して、400を加えて、切り捨てることにより実現します。600未満の数
字は、400を加えても繰り上がらないので、実質、切捨てられます。
・ROUNDDOWN 関数は、桁数を指定して切り捨てる関数です。
=ROUNDDOWN(セル番地,桁数)
切り捨て位置は、小数点位置を基準として、右方向(小数点以下)に正
の数、左方向に負の数を使います。
例えば、任意のセルに 1248.567 という数値が入っている場合、
=ROUNDDOWN(セル番地,2) 1248.56
=ROUNDDOWN(セル番地,1) 1248.5
=ROUNDDOWN(セル番地,0) 1248
=ROUNDDOWN(セル番地,-1) 1240
=ROUNDDOWN(セル番地,-2) 1200
=ROUNDDOWN(セル番地,-3) 1000
このように、切捨てされます。
・今回のように、特定の数を加えて切り捨てることにより、いろいろな
パターンの丸め方が可能になります。今回のは、599舎600入というこ
とですね。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・何気なく使っている四捨五入とは、5を加えて切り捨てるよという意
味と考えてもいいですね。三捨四入したければ、6を加えて切り捨て
ればいい。こういう考え方は、エクセルを使う上で、非常に大切にな
ります。みなさんも、アイデアを考えて利用してください。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.013==│
└──────────────────────────┘
◆ 今日の問題 「0以外の個数を数える」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のようにA列に、データが入力されているシートがある。C3のセ
ルに、0以外のデータの個数を示す関数を入れなさい。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃データ │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 1600│ │0以外の個数 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ 0│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 2300│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃ 5600│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃ 4200│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃7┃ 0│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃8┃ 2240│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃9┃ 4500│ │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
C3のセルを選択
=COUNTIF(A2:A9,"<>0")と入力して、Enter
─────────────────────────────────
・ポイントは、「COUNTIF 関数」を使うことと、「〜ではない」という
条件を付ける演算子「<>」を使うことです。
・COUNTIF関数は、=COUNTIF(範囲,検索条件)で、条件に合ったものだけ
数える関数です。今回の条件は、「0 以外のもの」ですから、「<>」
を使いました。
気をつけていただきたいのが、検索条件に「""」を付けること。単に
数字を条件にする場合は必要ありませんが、文字列や演算子を使う場
合は必要になります。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・COUNTIF 関数は、これまでにも何度も取り上げていますので、余裕の
ある方は、バックナンバーを探してみてください。実にさまざまなパ
ターンがあります。そして、極めて実践的な関数ですから、積極的に
利用してください。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.014==│
└──────────────────────────┘
◆ 今日の問題 「数字を比較して個数を数える」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように二つの数字が並んでいる。数字Aを基準として、数字B
より 300以上大きいものの個数を数えたい。C列に仮計算の式を入れ
てC9に、計算結果を入れなさい。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃数字A │数字B │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 1800│ 1400│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ 2300│ 2000│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 3400│ 3300│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃ 1200│ 1800│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃ 5600│ 4400│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃7┃ 4200│ 6200│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃8┃ 3300│ 5600│ │ │ ┃
┠─╂────┴────┼────┼────┼────┨
┃9┃ 300以上大きい数 │ │ │ ┃
┗━┻━━━━━━━━━┷━━━━┷━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
C2のセルに =IF(A2-B2>=300,1,0)と入力
C2を選択して、フィルハンドルでダブルクリック
C9のセルに =SUM(C2:C8)と入力
【結果】
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃数字A │数字B │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 1800│ 1400│ 1│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ 2300│ 2000│ 1│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 3400│ 3300│ 0│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃ 1200│ 1800│ 0│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃ 5600│ 4400│ 1│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃7┃ 4200│ 6200│ 0│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃8┃ 3300│ 5600│ 0│ │ ┃
┠─╂────┴────┼────┼────┼────┨
┃9┃ 300以上大きい数 │ 3│ │ ┃
┗━┻━━━━━━━━━┷━━━━┷━━━━┷━━━━┛
─────────────────────────────────
・IF関数を使って、正しいものを「1」正しくないものを「0」と場合分
けします。後は、普通に、SUM関数 を使うだけ。このように、1と0を
うまく使うことにより式が簡単になることがありますので覚えておき
ましょう。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・他にも方法はありますが、今回は、数字を使ってみました。数字をう
まく使える人は、エクセル上手です。いろいろ考えてみてください。
違う方法は、別の機会に出題する予定です。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.015==│
└──────────────────────────┘
◆ 今日の問題 「仮計算をしないで、数字を比較して個数を数える」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
前回と同じように、数字Aを基準として、数字Bより 300以上大きい
ものの個数を数えたい。今回は、仮計算を行わず、直接答えが出る式
をD3に設定しなさい。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃数字A │数字B │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 1800│ 1400│ │ 300以上大きい数 ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ 2300│ 2000│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 3400│ 3300│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃ 1200│ 1800│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃ 5600│ 4400│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃7┃ 4200│ 6200│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃8┃ 3300│ 5600│ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
D3のセルに =SUM(IF(A2:A8-B2:B8>=300,1,0)) と入力し、
Ctrl+Shift+Enterを押す
─────────────────────────────────
・前回は、C列に仮計算の式を入れて、単純化しました。今回は、仮計
算を使わず、いっぺんに計算する方法です。複数のもの同士を計算す
るには「配列」を使います。
・配列は、最後に、Ctrl+Shift+Enterを押して確定させます。すると、
自動的に{=SUM(IF(A2:A8-B2:B8>=300,1,0))}のように、中括弧がつ
きます。この中カッコは、手入力してはなりません。手入力すると文
字と判断されてしまいますので、必ず、Ctrl+Shift+Enterを押して
ください。
・IF(A2:A8-B2:B8>=300,1,0)の部分は、エクセルの内部では、前回の仮
計算と同じように、
=IF(A2-B2>=300,1,0)
=IF(A3-B3>=300,1,0)
=IF(A4-B4>=300,1,0)
=IF(A5-B5>=300,1,0)
=IF(A6-B6>=300,1,0)
=IF(A7-B7>=300,1,0)
=IF(A8-B8>=300,1,0)
このような式で、「1」か「0」を判断しています。そして、SUM 関数
で合計することにより、仮計算のセルを使うことなくいっぺんに答え
を出すことができるのです。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・配列には、実にさまざまな顔があります。この辺は、著書「嫉妬され
るほどエクセルが上手くなる222の技」 にまとめてありますので、興
味のある方は、是非、覗いてみてください。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.016==│
└──────────────────────────┘
◆ 今日の問題 「小数部のみを切り出す」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように、数字の小数部のみを切り出すには?
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃数値 │小数部 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 24.652│ 0.652│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ 32.244│ 0.244│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 45.062│ 0.062│ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B2のセルを選択して、=MOD(A2,1) と入力
フィルハンドルでコピー
─────────────────────────────────
・小数部とは、1で割った余りということですね。MOD関数 については、
これまでに何度か説明しましたが、もう一度記しておきます。
MOD関数は、割り算の余りを返す関数です。
=MOD(数値,除数) で、=MOD(50,8) ならば、2 になります。
つまり、50÷8 で、余りが、2 ということです。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・コンピュータでは、割り算の余りというものが本当に重要なようです。
例えば、5で割った余りは、0、1、2、3、4 の5種類ありますから、
それだけで5つのグループに分けることができます。今回の技は、比
較的簡単ですが、同じ考え方で、いろいろ応用できますので、アイデ
アを出して利用してください。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.017==│
└──────────────────────────┘
◆ 今日の問題 「数字の下4桁と下3桁のみを数値で切り出す」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように、数字の下4桁と下3桁のみを数値で切り出すには?
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃数値 │切り出し部 │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 244652│ 46│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ 329244│ 92│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 456062│ 60│ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B2のセルを選択して、=INT(MOD(A2,10000)/100) と入力
フィルハンドルでコピー
─────────────────────────────────
・これも、前に取り上げたものの応用です。ちょっと気づきにくいかも
しれません。
・まず、A2に入っている数値を 10000で割ります。
244652÷10000 → (商)24(余り)4652
次に、その余りを 100で割ります。
4652÷100=46.52
それを INT関数で小数部を切り捨てますから、46 になります。つま
り、下4桁と下3桁を取り出したことになります。
・この方法を公式化すると
=INT(MOD(セル番地,上側の桁)/下側の桁)
ということになります。今回は、
上側の桁 下4桁 10000
下側の桁 下3桁 100
割る桁数に注意してください。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・今回の技は、あまり実用的ではありませんが、頭の体操的に出題して
みました。MOD 関数は、結構器用だよということを覚えてください。
・尚、INT関数やMOD関数については、コレクション010 に説明していま
すので、初めての方は、参考にしてください。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.018==│
└──────────────────────────┘
◆ 今日の問題 「数値をすべて最も近い偶数に整える」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように、偶数はそのまま、奇数は最も近い偶数に切り上げる
には?
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃ 13│ 14│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 14│ 14│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ 15│ 16│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 16│ 16│ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B1のセルを選択し、=EVEN(A1) と入力して、Enter
B1のセルを選択し、フィルハンドルをダブルクリック
─────────────────────────────────
・EVEN関数は、あまり見かけない関数かもしれません。
=EVEN(セル番地)
で、最も近い偶数に切り上げます。
・ただし、負の数の場合は、-1は、-2という具合に、正の数と同じよう
に処理されますので、実際は切り下げられる形になります。これは、
絶対値で切り上げられてから符号を付けるという構造になっているか
らです。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・この関数は、小数点以下の数字も対象になります。23.456だったら24
になります。この辺も踏まえて使うと、より役に立つと思います。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.019==│
└──────────────────────────┘
◆ 今日の問題 「セル内の文字数を数える」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のB2のセルには、A2のすべての文字数を、C2のセルには「、」を
除いた文字数を示すように関数を入れなさい。
┏━┳━━━━━━━━━━┯━━━━┯━━━━━━━━┓
┃ ┃A │B │C ┃
┣━╋━━━━━━━━━━┿━━━━┿━━━━━━━━┫
┃1┃文字列 │総文字数│、を除いた文字数┃
┠─╂──────────┼────┼────────┨
┃2┃信長、秀吉、家康 │ 8│ 6┃
┗━┻━━━━━━━━━━┷━━━━┷━━━━━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B2のセルに =LEN(A2) と入力
C2のセルに =LEN(SUBSTITUTE(A2,"、","")) と入力
─────────────────────────────────
・セル内の文字列の数を数えるときは、LEN 関数を使います。公式も簡
単で、=LEN(セル番地) でできます。
・読点「、」を除いた数を数えるには、少し工夫が要ります。文字列中
の特定の文字を置き換える関数を使います。それが、SUBSTITUTE関数
です。
・SUBSTITUTE関数の公式は、次のとおりです。
=SUBSTITUTE(セル番地,検索文字列,置換文字列,置換対象)
置換対象は、今回は、使用しません。省略した場合は、文字列中のす
べての検索文字列が置換えの対象になります。(置換対象については、
別の機会に説明します)
・C2のセルに入れた =LEN(SUBSTITUTE(A2,"、","")) の意味は、
「、」を空白、つまり何もないものに置換えて数えたということです。
・わかりにくい方は、どこかのセルに、=SUBSTITUTE(A2,"、","") と入
力してみてください。「信長秀吉家康」になります。つまり、「、」
が除かれた状態になるのがわかると思います。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.020==│
└──────────────────────────┘
◆ 今日の問題 「特定の文字列以外が入っているセルを数える」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のB列の「合格」以外のセルの数を数えられるように、D3のセル
に、関数を入れなさい。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃氏名 │結果 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃大場佳代│合格 │ │合格以外の個数 ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃山形三重│再試験 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃町田聖子│保留 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃早久郁代│不合格 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃本間加奈│再試験 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃7┃後藤真紀│不合格 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃8┃鈴木宗子│合格 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃9┃辻村希望│合格 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃10┃加藤亜衣│未受験 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃11┃清水道代│合格 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃12┃持田羽織│再試験 │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
D3のセルに =COUNTIF(B2:B12,"<>合格") と入力して、Enter
─────────────────────────────────
・COUNTIF関数 は、条件を設定して、それに合致するものを数える関数
です。これまでに何度もやりました。今回は、合致しないものを数え
る方法です。
・COUNTIF関数の公式は、=COUNTIF(セル範囲,検索条件)です。通常は、
数えたいものを条件に入れますが、等しい(=)、大きい(>)などの比較
演算子も使うことができます。
・比較演算子の意味は、次のとおりです。
「 = 」 〜と等しい 「 <> 」 〜と等しくない
「 > 」 〜より大きい 「 < 」 〜より小さい
「 >= 」 〜以上 「 <= 」 〜以下
・今回のように、合致しないものを数えたいときは、「<>」を使ってく
ださい。これが、結構、役立ちます。
★ エクセルの小技へ ★ | ★ TOPのページへ ★ | ★ 次へ(021〜031) ★ |