書類を送るために送り先住所が必要なのですが、都道府県、市町村などが別のセルに入力されている住所データしかありません。さてどうしましょう?

そのようなときに役立つ関数があります。
それは、「CONCATENATE(コンカティネイト)関数」といって、セルのデータをつなげて表示します。

「&」を使ってつなげていく方法もありますが、CONCATENATE関数で式を作るほうがミスなく簡単にできます。特に、つなげるセルが多いときは関数を使ことをおすすめします。

◇CONCATENATE関数・・・複数のセルに入力されている文字列を結合します。(30個まで)

CONCATENATE=(文字列1,文字列2,文字列3,・・・)

セル内のデータをつなげる方法(CONCATENATE関数)

下の別々のセルに入力されている住所データをひとつにつなげてみましょう。

img20151207_01

 

1.ひとつにした住所を表示するセル(A5)を選択し、関数の挿入ボタン「fx」をクリックします。

img20151207_02

img20151001_02_1

 

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

img20151207_03

 

3.「文字列1」欄にマウスカーソルがあるのを確認し、セルA2を選択します。

img20151207_04

img20151207_05

 

4.「文字列1」欄に「A2」と自動的に入力されたら、次に「文字列2」欄をクリックし、セルB2を選択します。

img20151207_06

「文字列2」欄をクリックしてから↓

img20151207_07

 

5.「文字列2」欄に「B2」と自動的に入力されたら、同様に「文字列5」までセルを選択していき、スクロール下の「▼」を1回クリックします。

img20151207_08

↓ ↓ ↓ ↓

img20151207_09

 

6.住所と建物名の間に「スペース」を入れたいので、「文字列6」の欄が表示されたら、キーボードから半角スペースを入力します。

そして、スクロール下の「▼」を1回クリックします。

img20151207_10

 

7.「文字列7」の欄が表示されたら、セルF2を選択します。

img20151207_11

 

8.「文字列7」欄に「F2」と入力されたら、「OK」ボタンをクリックします。

img20151207_12

 

住所がひとつにつながって表示されました。

img20151207_13

 

 

会員名簿や商品のデータベースなどで、同じデータが重複していたことはありませんか?

重複したデータを目で見て探し出すのは大変な作業です。

そんなときに便利なのは、エクセルの「重複の削除」という機能です。

基準項目をチェックするだけで、重なったデータをすばやく削除してくれます。

重複したデータを削除する方法

下の名簿の中の重複したデータを削除しましょう

img20151029_01

 

1.表の項目をひとつ選択し、[データ]タグ→「重複の削除」ボタンクリックします。

 

img20151029_02

↓ ↓ ↓

img20151029_02-2

 

2.「重複の削除」ウインドウが開くので、表示されている項目の「No.」のチェックをはずして「OK」ボタンをクリックします。

(「氏名」「年齢」「性別」「生年月日」が一致すると重複してるとみなされます)

img20151029_03

 

3.重複するデータの数残ったデータの数が表示されるので、確認をしたら「OK」をクリックします。

img20151029_04

 

3つのデータが削除されました。

img20151029_09

 

<削除されたデータを知りたい場合>

データが削除されていると、項目「No.」の上のセルとの数字の差が「2」以上になるので、「下のセル-上のセル」という数式で数字の差をもとめてみます。

 

1.セルF3に「=A3-A2」と半角入力し、「Enter」キーを押します。

img20151029_05

 

「1」と表示されました。

img20151029_06

 

2.F3セルの右下のフィルハンドルをダブルクリックして、下のセルに数式をコピーします。

img20151029_07

 

数字の差が「2」と「3」が、データが削除された箇所です。
消えているNo.をみてみると、No.16、No.24、No.25が削除されたデータとなります。

img20151029_08

 

 

商品別、社員別、科目別など、項目別にそれぞれの合計をだしたいときはありませんか?
そんなときには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

 

 

データの個数を求めるのに、ひとつずつ数えると手間がかかって大変ですよね。
そんなときには、関数を使うと便利です。

◇COUNT関数・・・指定した範囲内の数値のセルの個数をかぞえます

◇COUNTA関数・・・指定した範囲内の空白以外のセルの個数をかぞえます

データの個数をもとめる方法(COUNT関数・COUNTA関数)

下表の「参加世帯数」と「所属世帯数」の数を関数を使って求めてみましょう。

img20150917_01

まずは「参加世帯数」を求めましょう

1.参加世帯数を表示させるセル(B12)を選択、関数の挿入ボタンをクリックします。

img20150917_02

 

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

img20150917_03

 

3.関数の引数ウインドウが開くので、個数を求める範囲(B2からB11)をドラッグして選択します。

img20150917_04

img20150917_05

 

4.値1欄に「B2:B11」と表示されているのを確認して「OK」ボタンをクリックします。

img20150917_06

 

個数(6世帯)が求められました。

img20150917_07

img20150917_07_1

 

次は「所属世帯数」を求めましょう。

1.所属世帯数を表示させるセル(B13)を選択、関数の挿入ボタンをクリックします。

img20150917_08

 

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

img20150917_09

 

3.関数の引数ウインドウが開くので、個数を求める範囲(A2からA11)をドラッグして選択します。

img20150917_10

 

4.値1欄に「A2:A11」と表示されているのを確認して「OK」ボタンをクリックします。

img20150917_11

 

個数(10世帯)が求められました。

img20150917_12

img20150917_12_1

 

 

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

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

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

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

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

 

「自動表示させた土日祝に色をつけたい」の3回目です。

「祝日に色をつける方法」として、条件付き書式とCOUNTIF関数を使います。
※COUNTIF関数
 COUNTIF(範囲、検索条件)…指定した範囲から検索条件を満たすデータの数を求める

設定方法を細かくご紹介しているのでやや長めになっていますが、これで完成となりますので試してみてくださいね。

 

祝日に色をつける方法(条件付き書式・COUNTIF関数)

以下の順序で設定していきましょう。
・祝日一覧表の作成
・条件付き書式のルール設定
・ルールの優先順位変更

 

1.別のシートに祝日の一覧表を作成し、シート名を「祝日」とします。

img20150907_01

 

2.土日に色がついている状態で、色をつけたい曜日の列(C列)を範囲選択します。

img20150907_02

 

3.[ホーム]タブ→[スタイル]の「条件付き書式」→「新しいルール(N)」を選択します。

img20150903_02

img20150907_04

 

4.[ルールの種類を選択してください(S)]から「►数式を使用して、書式設定するセルを決定」を選択します。

img20150907_05

 

5.次の数式を順次入力していきます。

=COUNTIF(祝日!$A$2:$A$16,’5月’!$B2)=1

これは「祝日シート-祝日一覧表の範囲(祝日!$A$2:$A$16)の中から、検索条件の日付(’5月’!$B2)の数が1回(=1)であれば色をつける」という意味をあらわしています。

つまり、5月シートの日付(C列)のうち、祝日一覧表の中の日付と一致する日付の曜日は色が変わり、一致しなければ変わらないということです。

 

では以下の順で数式を完成させていきましょう。

[次の数式を満たす場合に値を書式設定(O)]の欄に「=COUNTIF(」と半角で入力します。

img20150907_06

 

6.「祝日」シートの祝日一覧表から、祝日の日付部分(A2からA16)をドラッグして範囲選択します。

img20150907_07

すると、下図の赤線部分のように自動的に範囲が入力されます。

img20150907_08

 

7.続けて「,」と半角入力し、カレンダー日付の「2015/5/1」(セルB2)をクリックします。

img20150907_09

img20150907_10

 

8.セルB2を選択したら「$B$2」となっているので、[F4]キーを2回押して$B2」(列は絶対値、行は相対値)にしてから、続けて「)=1」と半角入力します。

img20150907_11

=COUNTIF(祝日!$A$2:$A$16,’5月’!$B2)=1」と入力できていれば、「書式(F)」を選択します。

 

9.[フォント]タブ→[色(C)]の「▼」→「オレンジ」を選択し、欄がオレンジ色になっていれば「OK」ボタンをクリックします。(プレビューに表示されます)

img20150907_12

 

10.ルールの一覧に追加されているのを確認したら「適用」をクリックします。

img20150907_14

祝日(5/3、4、5)の曜日がオレンジ色になりました。

img20150907_15

 

日曜日が祝日であるとき、日曜日の赤色を優先したい場合は、ルールの表示順位を変更しましょう。

ルールの順位は上からが優先となるので、「祝日のルール」を「日曜のルール」の下に移動させます。

11.「祝日のルール」を選んで、右上部の「▼」ボタンをクリックします。

img20150907_16

 

「祝日のルール」が「日曜のルール」の下に移動しました。

img20150907_17

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

 

5/3(日・祝)の曜日がオレンジ色から赤色に変わりました。

img20150907_18

 

 

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)
 :