テキストボックスは、文字を直接入力するだけのものだと思っていませんか?

実は、それ以外にも、セルの内容をそのまま表示することもできるのです。

見出しに使えば、セルの内容を変えるだけで自動的に見出し文字を変えることができて便利ですよ。

テキストボックス内にセルの内容を表示する方法

テキストボックス内に最大売上の値を表示させて、見出しを完成させましょう。

img20160121_01

 

1.テキストボックスをクリックして、選択された状態にします。

img20160121_02

 

2.数式バーに、まず「=」と半角入力し、セルB11をクリックして[ENTER]キーを押します。

img20160121_03

 

すると、セルB11と同じ数値「5200」が、テキストボックス内に表示されます。

img20160121_04

 

ここから文字を大きくして、テキストボックスの枠線を消しましょう。

3.文字の大きさの▼マークをクリックして「16」を選びます。

img20160121_05

 

4.「描画ツール」の「書式」タグをクリックし、「図形の枠線」→「線なし」を選びます。

img20160121_06

 

文字が大きくなり枠線が消えました。

img20160121_07

 

 

商品別、社員別など同じ条件のデータを合計する方法として、以前、SUMIF関数をご紹介しました。
では「地域別」かつ「商品別」などのように複数の条件がある場合はどのようにすればよいでしょう?

ということで今回は、複数の条件のデータを合計する方法、SUMIFS関数をご紹介します。
「SUMIF」に「S」がつくのでSUMIF関数の複数形のようなものですが、数式の順序が少し違うので気をつけてくださいね。

◇SUMIFS関数・・・合計対象範囲の中から、指定した範囲内の検索条件と同じデータをさがし、そのすべての条件を満たすデータに対する計をもとめます。
SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2,・・・・)

複数の条件のデータを合計する方法(SUMIFS関数)

下表の一覧から、
「ビジネス名刺の配送での売上」
「個人名刺の配送での売上」
ビジネス名刺の店頭での売上」
「個人名刺の店頭での売上」をそれぞれもとめてみましょう。

img20151016_01

<もとめ方>
まず、合計する範囲・売上額(E列)を指定。
条件範囲1に商品名(B列)、条件1に「ビジネス名刺(G3)」を指定、条件範囲2に納品(C列)、条件2に「配送(H2)」をそれぞれ指定します。

 

1.合計を表示したいセル(H3)を選択し、関数の挿入ボタン「fx」をクリックします。

img20151016_02

img20151001_02_1

 

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

img20151016_03

 

3.「合計対象範囲」欄をクリックし、セルE2からE15(売上額)を範囲選択します。

img20151016_04

img20151016_05

 

4.「合計対象範囲」欄に「E2:E15」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を1回押して絶対参照「$E$2:$E$15」にします。
(数式をコピーしたときに、範囲が変わらないように絶対参照にします)

img20151016_05-2

 

5.次に「条件範囲1欄をクリックし、B2:B15(商品名)を範囲選択します。

img20151016_06

条件範囲1」欄に「B2:B15」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を1回押して絶対参照「$B$2:$B$15」にします。
(数式をコピーしたときに、範囲が変わらないように絶対参照にします)

img20151016_06-2

 

6.次に「条件1欄をクリックし、セルG3(「ビジネス名刺」)を選択します。

img20151016_07

条件1」欄に「G3」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を3回押して絶対参照「$G3」にします。
(数式をコピーしたときに、列番号(G列 )が変わらないようにします)

img20151016_07-2

 

7.次に「条件範囲2欄をクリックし、C2:C15(納品)を範囲選択します。

img20151016_08

条件範囲2」欄に「C2:C15」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を1回押して絶対参照「$C$2:$C$15」にします。
(数式をコピーしたときに、範囲が変わらないように絶対参照にします)

img20151016_08-2

 

8.次に「条件2欄をクリックし、セルH2(「配送」)を選択します。

img20151016_09

条件2」欄に「H2」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を2回押して絶対参照「H$2」にします。
(数式をコピーしたときに、行番号(2行目 )が変わらないようにします)

img20151016_09-2

 

これで「ビジネス名刺の配送での売上」が求められました。

img20151016_10

 

では、数式をコピーして他の売上合計ももとめてみましょう。

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

img20151016_11

 

「個人名刺の配送での売上」がもとめられました。

img20151016_11-2

 

10.セルH3を選択し[Ctlr]キー+「C」でコピーをします。

img20151016_12

貼り付けたい先のセルを指定し、[Enter]キーを押します。

img20151016_12-2

 

「ビジネス名刺の店頭での売上」と「個人名刺の店頭での売上」がもとめられました。

img20151016_13

 

◎おまけ
数式のカッコ内をクリックすると、指定している箇所が色別で表示されます。
どこを指定しているかを確認したいときに利用すると便利ですよ。

img20151016_14

 

 

以前に、条件と一致するセルの個数をもとめる方法(COUNTIF関数)をご紹介しました。

COUNTIF関数は指定できる条件が1つですが、今回ご紹介するCOUNTIFS関数は複数の条件を指定できます。

”「COUNTIF」に「S」がついた複数形”と考えると覚えやすいでしょう。

複数の条件と一致するセルの個数をもとめる方法(COUNTIFS関数)

下表のグループ1、グループ2それぞれの条件を満たす人数(セルの数)を関数を使ってもとめてみましょう。

グループ1の条件は「30歳以上」と「男」、グループ2の条件は「30歳以上」と「女」です。

 

1.人数を表示するセル(F2)を選択し、関数の挿入ボタン「fx」をクリックします。

 

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

img20151012_03

 

まず、検索条件範囲1で「年齢」を指定して、そこから検索条件の「30(歳)以上」を指定します。

3.「検索条件範囲1」欄をクリックし、B2からB26をドラッグします。

img20151012_04

img20151012_05

 

4.「検索条件範囲1」欄に「B2:B26」と自動的に入力されます。
そのまま[F4]キー(ファンクションキー)を1回押して絶対参照「$B$2:$B$26」にします。
(数式をコピーしたときに、範囲が変わらないために絶対参照にします)

img20151012_05-2

 

5.「検索条件1」欄に「>=30」(”30(歳)以上”の意味)と直接入力し、「OK」ボタンをクリックします。

img20151012_06

——————————–
【参考】
●と等しい…=●
●以上…>=●
●以下…<=●
●より大きい(上)…>●
●未満、より小さい(下)…<●
●と等しくない…<>●
——————————–

 

次に、検索条件範囲2で「性別」を指定して、そこから検索条件の「男」を指定します。

6.「検索条件範囲2」欄をクリックし、C2からC26をドラッグします。

img20151012_07

 

7.「検索条件範囲2」欄に「C2:C26」と自動的に入力されます。
そのまま[F4]キー(ファンクションキー)を1回押して絶対参照「$C$2:$C$26」にします。
(数式をコピーしたときに、範囲が変わらないために絶対参照にします)

img20151012_07.-2

 

8.「検索条件2」欄クリックしてセルE3(男)を選択し、「OK」ボタンをクリックします。

img20151012_09

※「男」と入力されたセルがなくて指定できない場合は、検索条件欄に直接「男」と入力します

img20151012_14

 

グループ1の「30歳以上」の「男」を満たす人数(セルの数)が表示されました。

 

では、数式をコピーをしてグループ2を満たす人数ももとめてみましょう。

9.条件1の人数セル(F2)を選択し、[Ctrl]キー+「C」を押します。(コピー)

 

10.条件2の人数セル(F7)を選択し、[Enter]キーを押します。(貼り付け)

関数式の「男」と指定したセル(E3)が「女」のセル(E8)に変わっているのを確かめましょう。

グループ2の「30歳以上」の「女」を満たす人数は「5」と表示されました。

 

結果は、グループ1が10(人)、グループ2が5(人)となります。

 

 

以前、同じ条件のデータを合計する方法(SUMIF関数)でカレンダーの商品別売上を求める方法をご紹介しました。

ではもし商品別でなく、カレンダーA4版、B4版、A3版と「大きさ別」で集計したいときは、どのようにすればよいでしょうか?

実はこのようなあいまいな条件を指定するときに、便利な記号があるのです。

その名も「ワイルドカード

今回はそのワイルドカード*」(半角アスタリスク) を使って、大きさ別の売上額をもとめてみましょう。

【ワイルドカードの使い方】
 「東*」…”東”で始まる文字列(例:北、方、海道など)
 「*東」…”東”で終わる文字列(例:北、関、南南など)
 「*東*」…”東”を含む文字列(”東”を含む文字ずべて)

 

あいまいな文字を検索条件にする方法(ワイルドカード)

B列の売上商品から、ワイルドカードを使って大きさ別の売上額を集計してみましょう。

「カレンダー○○版」という文字で始まる商品名を検索の条件として使います。

img20151008_01

 

1.売上額を表示したいセルを選択し、関数の挿入ボタン「fx」をクリックします。

img20151008_02

img20151001_02_1

 

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

img20151008_03

もしくは、関数の挿入ウインドウが開いたときに表示されている[関数の分類(C)]の「最近使った関数」で、[関数名(N)]の中に使いたい関数があればそれを選んでもOKです。

img20151008_03-2

 

3.「範囲」欄をクリックし、セルB2からB23を範囲選択します。

img20151008_04

img20151008_04-2

 

4.「範囲」欄に「B2:B23」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を1回押して絶対参照にします。
(数式をコピーしたときに、範囲が変わらないように絶対参照にします)

img20151008_04-3

 

5.「検索条件欄をクリックし、「“カレンダーA4版*”」と半角入力します。(「*」は半角アスタリスク)

img20151008_05

 

6.次に「合計範囲欄をクリックし、セルE2からE23を範囲選択します。

img20151008_06

 

7.「合計範囲」欄に「E2:E23」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を1回押して絶対参照にします。
(数式をコピーしたときに、範囲が変わらないように絶対参照にします)

「OK」ボタンをクリックします。

img20151008_06-2

 

「カレンダーA4版」という文字で始まる商品の売上が集計されました。

img20151008_07

 

では、数式をコピーして他の大きさの商品の売上合計ももとめてみましょう。

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

img20151008_08

 

9.「カレンダーB4版」の売上額セルを選択し、数式の「A」を「B」に修正[Enter]キーを押します。

img20151008_09

 

同様に「カレンダーA3版」の売上額セル内の数式の「4」を「3」に修正[Enter]キーを押します。

img20151008_13

 

大きさ別の売上額がもとめられました。
正しい数式で計算されているかは、それぞれの表の合計額が同額であるかで確かめましょう。

img20151008_12

 

 

商品別、社員別、科目別など、項目別にそれぞれの合計をだしたいときはありませんか?
そんなときにはSUMIF関数を使ってみましょう。
商品別の数量や金額をもとめるときにとっても便利ですよ!

◇SUMIF関数・・・指定した範囲内の検索条件と同じデータをさがし、そのデータに対する合計範囲の計をもとめます。

同じ条件のデータを合計する方法(SUMIF関数)

下表の商品別売上額をもとめてみましょう。

売上商品名(B列)から商品名(H列の各々)を検索して、 売上額(E列)から商品名(H列の各々)別の売上だけを合計します。

img20151001_01

 

1.合計を表示したいセル(I2)を選択し、関数の挿入ボタン「fx」をクリックします。

img20151001_02

img20151001_02_1

 

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

img20151001_03

 

3.「範囲」欄をクリックし、セルB2からB23を範囲選択します。

img20151001_04

img20151001_04_1

 

 

4.「範囲」欄に「B2:B23」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を1回押して絶対参照にします。
(数式をコピーしたときに、範囲が変わらないように絶対参照にします)

img20151001_04_2

 

5.「検索条件欄をクリックし、セルH2を選択します。

img20151001_05_1

 

6.次に「合計範囲欄をクリックし、セルE2からE23を範囲選択します。

img20151001_06_1

 

7.「合計範囲」欄に「E2:E23」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を1回押して絶対参照にします。
(数式をコピーしたときに、範囲が変わらないように絶対参照にします)

「OK」ボタンをクリックします。

img20151001_06_2

 

セルH2(商品名:カレンダーA4版)の合計がもとめられました。

img20151001_07

img20151001_07_1

売上商品名の範囲($B$2:$B23)から、「カレンダーA4版」(H2)を検索して、
合計する範囲 ($E$2:$E23)から「カレンダーA4版」(H2)の売上だけを合計しました。

 

では、数式をコピーして他の商品の売上合計ももとめてみましょう。

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

img20151001_08

全商品の売上額がもとめられました。

 

正しい数式で計算されているかは、それぞれの表の合計額が同額であるかで確かめましょう。

img20151001_09

 

 

商品別の購入者数、売上額別の社員数など、ある条件を満たすセルの個数(データの数)をもとめたい場合は、COUNTIF関数を使ってみましょう。

範囲と条件を指定するだけで簡単にもとめられます。

◇COUNTIF関数・・・指定した範囲内の検索条件を満たすセルの個数をもとめます。

条件と一致するセルの個数をもとめる方法(COUNTIF関数)

下表の「人数別世帯数」をCOUNTIF関数を使ってもとめてみましょう。

img20150928_01

 

1.世帯数をもとめるセル(E6)を選択し、関数の挿入ボタン「fx」をクリックします。

img20150928_01-1

 

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

img20150928_02

 

3.「範囲」欄をクリックし、B2からB13をドラッグします。

img20150928_03

↓ ↓ ↓

img20150928_04

 

4.「範囲」欄に「B2:B13」と自動的に入力されます。
そのまま[F4]キー(ファンクションキー)を1回押して絶対参照にします。
(数式をコピーしたときに、範囲が変わらないように絶対参照にします)

img20150928_05

 

5.次に「検索条件」欄をクリック→D6を選択します。「OK」ボタンをクリックします。

img20150928_06

 

ほかにも、「2」と直接入力する方法もあります。
(検索条件が文字列の場合は「”」(ダブルクォーテーション)で囲みます。)

img20150928_06-1

 

人数が2の世帯数の数がもとめられました。

【数式の意味】
「B2からB13」の範囲の中で、「D6」(または「2」)と同じデータの数をかぞえます。

img20150928_07

 

数式をコピーして、人数が「3」「4」の世帯数をもとめてみましょう。

6.コピーするセルの右下にマウスポインターをおいて、フィルハンドル(+)を「4」の世帯数のセルまでドラッグします。

img20150928_08

コピーされました。

img20150928_09

 

「5以上」の世帯数をもとめる場合は、検索条件に注意しましょう。

7.「検索条件」欄に「>=5」と直接入力し、「OK」ボタンをクリックします。

img20150928_10

【参考】
「>=○」は「○以上」「<=○」は「○以下」をあらわします。

 

すべての人数別世帯数がもとめられました。

img20150928_11

 

 

範囲内の合計は、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

 

 

3種類ある参照方法のうち、今回は相対参照をご紹介しましょう。

相対参照とは、数式が入力されているセルを基点として、ほかのセルを相対的な位置関係に指定することです。

つまり、相対参照で入力された数式を、別のセルにコピーすると、コピー先の参照元が自動的に変わるということです。

絶対参照の場合は、コピーしても変わりません。詳しくはこちらをご覧ください。

エクセルの初期設定では、入力する数式は相対参照となっています。

 

★数式の相対参照をコピーする方法 ★

下表の売上額(D3)の数式を下のセルにコピーしましょう。

数式は単価と売上数を掛け合わせたものなので、相対参照のままコピーします。

img20150625_03

 

1.D3セルの右下にマウスポインタを重ねて、「+」をダブルクリックします。

img20150625_04

 

下のセルまでコピーされました

img20150625_05

確認すると、相対参照で数式がコピーされています。

img20150625_06

 

 

★(参考)参照方法の切り替え方★

」を直接入力しても可ですが、ファンクションキー[F4]を押すと参照方法が切り替わります。
下表のように、1回押すごとに切り替わっていきます。

初期状態  相対参照(A1)
[F4]押す  絶対参照 ($A$1)
[F4]押す  列が相対、行が絶対参照(A$1)
[F4]押す  列が絶対、行が相対参照($A1)
[F4]押す  相対参照(A1)
 :

 

数式でセルを参照する場合には、3種類の参照方法があります。

 

絶対参照…参照するセル番地を固定する方法。数式をコピーしてもセル番地は変わりません。

相対参照…数式を入力したセルを基点として、ほかのセルの位置関係を相対的に指定する方法。

複合参照…列は相対参照、行は絶対参照のように、絶対参照と相対参照を組み合わせた方法。

 

セル番地の前に「$」マークがついているのを見たことがあるのではないでしょうか。
今回はセルを固定させて参照する「絶対参照」を紹介します。
単価や時給のように、参照元を固定して数式に使う場合は絶対参照を使います。

 

★参照方法を切り替える方法~絶対参照★

下表の「売上額」欄を数式を使って埋めます。

単価の「980(D3)」絶対参照にして数式を作りましょう。

img20150622_01

 

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

img20150622_02

 

2.選択したセルをダブルクリックするか、数式バーをクリックし、「=」と入力します。

続けて、参照するセル「C6」をクリック→「*」と入力→「D3」をクリック→[F4](ファンクションキー)を1回押します

img20150622_10

↓  ↓  ↓  ↓

img20150622_05

D3セルが絶対参照になりました。

 

列番号と行番号の前にがつくと絶対参照をあらわします。

img20150622_06

 

空欄に数式をコピーしましょう。

3.C6セルの右下にマウスポインタを重ねて、「+」をダブルクリックします。

img20150622_07

 

下の欄にも数式がコピーされ、絶対参照もそのままコピーされました。

img20150622_09

 

★参照方法の切り替え方★

」を直接入力しても可ですが、ファンクションキー[F4]を押すと参照方法が切り替わります。
下表のように、1回押すごとに切り替わっていきます。

初期状態  相対参照(A1)
[F4]押す  絶対参照 ($A$1)
[F4]押す  列が相対、行が絶対参照(A$1)
[F4]押す  列が絶対、行が相対参照($A1)
[F4]押す  相対参照(A1)
 :