計算元のデータが入力されるまで、数式は入っていてもセルは空白状態にしておきたいことはありませんか?

ところが下表のように、項目が未入力の残高欄(E6、E7)に数式を入力しておくと・・・

img20151005_01

↓ ↓ ↓

img20151005_02

直前の計算結果(E5)が表示されてしまいます。

そこで今回は、データ(項目)が未入力のときはセル(残高)が空白になるように、IF関数を使って設定する方法をご紹介しましょう。

◇IF関数・・・IF(論理式、真の場合、偽の場合)
論理式を満たすときは真の場合を返し、満たさない場合は偽の場合を返す

 

数式の結果を表示しないで空白にする方法(IF関数)

今回使うIF関数の内容は以下のようになります。
・論理式…指定したセルが空白ならば
・真の場合…(論理式どおりなら)セルを空白にする
・偽の場合…(論理式と違うなら)計算式をもとめる

 

では、まずE3の計算式を削除してから、IF関数を使った数式を入力してE7まで数式をコピーしましょう。

1.E3を選択し、[Delete]キーを押してデータを削除します。

img20151005_03

 

2.関数の挿入ボタン「fx」をクリックします。

img20151005_04

 

3.関数の挿入ウインドウが開くので、[関数の分類(C)]から「論理」を選び、[関数名(N)]から「IF」を選び「OK」ボタンをクリックします。

img20151005_05

 

4.「論理式」欄をクリックし、セルB3をクリックし、続けて「=””と半角入力します。

img20151005_06

img20151005_07

※「”」間に文字列を入れないことで空白になります。

 

5.「真の場合」欄をクリックし、「””と入力します。

img20151005_08

 

6.「偽の場合」欄をクリックし、残高をもとめる計算式(E2+C3-D3)を入力します。
セル[E2]クリック→「+」入力→[C3]クリック→「」入力→[D3]クリックする。(全文を直接入力しても可)
最後に「OK」ボタンをクリックします。

img20151005_09

 

数式が入力されて残高が表示されました。

img20151005_10

 

下のセルまで数式をコピーしましょう。

7.コピーするセルの右下にマウスポインターをおいて、フィルハンドル(+)をセル[E7]までドラッグします。

img20151005_11

 

項目が未入力の残高欄が、数式は入力されてますが空白になりました。

img20151005_12

 

新規入力した場合、項目と入金(出金)を入力すると残高は自動的に計算表示されます。

img20151005_13

 

 

データの増減に対して、自動的に合計を再計算してくれたら便利だと思いませんか?

実は、関数の指定方法でそれができるのです。

指定方法は簡単ですので活用してみてください。

データを増減したときに自動的に再計算する方法

下表の「世帯主(A列)」と「人数(B列)」のデータが増減した場合に、「総世帯数」欄(D2)と「総人数」欄(E2)が自動的に再計算されるように関数を指定してみましょう。

「総世帯数」を求めるにはCOUNTA関数、「総人数」を求めるにはSUM関数を使います。

img20150924_01

 

1.「総世帯数」欄(D2)を選択し、数式バー横の関数の挿入ボタン「fx」をクリックします。

img20150924_02

 

2.[関数の分類(C)]から「総計」を選び、[関数名(N)]から「COUNTA」を選び「OK」ボタンをクリックします。

img20150924_03

 

3.列の「A」をクリックすると、値1に「A:A」(”指定した列番号全範囲”を意味します)と表示されます。

img20150924_04

 

4.セルA1の項目名「世帯主」を除くために、「A:A」のうしろに「-1」と入力し、「OK」ボタンをクリックします。

img20150924_05

 

関数が入力されて、総世帯数が表示されました。

img20150924_06

 

同様に、総人数欄にも関数を指定しましょう。

6.「総人数」欄(E2)を選択し、数式バー横の関数の挿入ボタン「fx」をクリックします。

img20150924_08

 

7.[関数の分類(C)]から「数学/三角」を選び、[関数名(N)]から「SUM」を選び「OK」ボタンをクリックします。

img20150924_09

 

8.列の「B」をクリックすると、数値1に「B:B」(”指定した列番号全範囲”を意味します)と表示されます。
「OK」ボタンをクリックします。

img20150924_10

 

関数が入力されて、総人数が表示されました。

img20150924_11

 

では、データを増やしてみましょう。
12行目に「佐藤 浩一」「5」とデータを入力します。
すると、自動的に「総世帯数」と「総人数」の値が再計算されます。

img20150924_12

 

 

範囲内の合計は、SUM関数を使ってもとめるのが一般的です。

では累計をもとめたいときはどのようにすればよいでしょうか?

実はこの場合もSUM関数を使います。最初の数値を絶対参照にするのがポイントです。
(絶対参照についての詳細はこちらをご覧ください。)

累計をもとめる方法(SUM関数・絶対参照)

下表の累計をSUM関数を使ってもとめてみましょう。

img20150921_01

 

1.累計をもとめたい一番上のセル(D2)を選択します。

img20150921_02

 

2.D2セルをクリックし「=SUM(」と入力します。
次に、C2セルをクリックし、[F4]キーを1回押して絶対参照$C$2」にします。

img20150921_03

 

3.「=SUM($C$2」の次をクリックし、続けて「:」と入力C2セルをクリック→「)」と入力します。
最後に[Enter]キーを押します。

img20150921_04

 

計算式が入力され、結果が表示されました。

img20150921_05

 

次は下のセルに計算式をコピーしましょう。

4.D2セルの右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

img20150921_06

 

下のセルにもコピーされて累計がもとめられました。

img20150921_07

 

次に、データを追加した場合のコピーの方法をご説明します。

1.6行目にデータを追加入力します。

img20150921_08

 

2.数式の入った一番下のセルを選択し、セルの右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

img20150921_09

 

追加したデータの累計がもとめられました。

img20150921_10

 

 

売上データを支店別に複数のシートに分けて作った場合、どのようにすれば手早く集計できるでしょうか?

連続して並んでいる複数のシートの同じセル位置のセルを参照することで簡単に集計することができます。

複数シートの同じ位置のセルデータを集計する方法

シート「本店」「札幌店」「横浜店」のそれぞれの売上額を、シート「全本支店」の売上額に集計してみましょう。

img20150914_01

 

1.集計するセルを選択します。

img20150914_02

 

2.[ホーム]タブ→[編集]→「ΣオートSUM」を選ぶと、セル内に「=SUM()」と自動的に表示されます。

img20150914_03

↓ ↓ ↓

img20150914_04

 

3.シート名「本店」をクリックし、集計を表示するセルと同じ位置のセル(ここではセルB2)をクリックします。

img20150914_05

 

4.「Shift」キーを押しながら、シート「横浜店」(集計したい範囲の最後のシート名)をクリックし、[Enter]キーを押します。

数式バーに「=SUM(’本店:横浜店’!B2)」と自動的に表示されます。

img20150914_06

 

シート「全本支店」のセルに各本支店の売上額が集計されました。

img20150914_07

次に、この数式を下のセルにコピーしましょう。

 

6.数式の入ったセルの右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

img20150914_08

 

全部のセルに数式がコピーされて集計されました。

img20150914_09

 

エクセルの特長ともいえるのが数式です。

この数式を使えば、集計やカウントや変換などが便利で、効率的に作業をすすめることができます。

今回はセル参照での数式の入力方法と、数式に使う各種記号(演算子)をご紹介しましょう。

 

★数式を入力する方法★

下表の[売上計]欄を数式を使って求めましょう。

img20150608_01

 

1.数式を入力するセルを選択します。

img20150608_02_1

 

もしくは、セルを選択して数式バーをクリックします。

img20150608_03

 

2.セル(または数式バー)に半角で「=」と入力し、「C2」セルをクリックします。

(セルと数式バーには「=C2」と表示されます)

img20150608_04

 

3.続けて同様に半角で「*」と入力し、「D2」セルをクリックします。

 数式の入力が終わったら「ENTER」キーを押して決定します。

(セルと数式バーには「=C2*D2」と表示されます。)

img20150608_05

 

 

数式によって計算ができました。

img20150608_06

 

★数式に使う主な各種記号(演算子)★

【算術】

+(プラス) 加算(D1+D2) D1とD2を足す
 -(マイナス)  減算(D1-D2) D1からD2を引く
 *(アスタリスク)  乗算(D1*D2) D1とD2を掛ける
 /(スラッシュ)  除算(D1/D2) D1をD2で割る
 ^(キャレット)  べき乗(D12) D1の二乗

 

【比較・文字列】

= 等号(D1=D2) D1はD2と等しい
>  より大きい(D1>D2) D1はD2より大きい
 < より小さい(D2<D1) D2はD1より小さい
>=  以上(D1>=D2) D1はD2以上
<=  以下(D2<=D1) D2はD1以下
<> 不等号(D1<>D2) D1とD2は等しくない
& 文字列結合(B1&C1) “売上”&”個数”

 

【参照】

 :(コロン)  セル範囲(A1:D1) A1からD1間の全てのセルを
参照する
 ,(カンマ)  複数のセル範囲
(A1:D1,C2:F2)
A1からD1間の全セルと、
C2とF2間の全セルを参照する