FC2ブログ

Excelの関数・14 SUMTIF関数、SUMIFS関数

2015.07.27 19:33|Excel関数
  以前 COUNTIF関数については書いていたのですが、SUMIF関数については書いていなかったので、久しぶりにExcel関数を更新します。

 SUMIFS関数
 条件にあてはまる項目の数だけを合計します。
 実際にはあまりないと思いますが、7月1日から7月15日までに仕入れた合計数を、品目ごとに知りたいとします。


2015072701.png


 この場合、条件は「品目」ごと、「仕入年月日」が「7月1日以上」で「7月15日以下」となります。
 まず、りんごの個数を求めます。
 G3セルにSUMIFS関数を入れます。
 ウインドウが開いたら、以下のように選んでいきます。


2015072702.png


 「合計対象範囲」は、実際に数える個数です。ここではD3からD22のセル。オートフィルを使うので、絶対参照にします。
 条件範囲は、1つめは「品目」とします。C3からC22です。こちらも絶対参照にします。
 条件範囲(ここでは「品目」)からどの条件を拾うのかが「条件1」です。ここは「りんご」を数えたいので、F3セルを選択します。オートフィルで条件を変えられるようにするので、ここは相対参照にします。
 2つめの条件範囲は、日付になります。日付はB3からB22までの範囲。条件は、まずは7月1日以上なので「>=2015/7/1」と入力してあるF10セル。こちらは、「条件」に直接入力しても構いません。セルを選択する場合は、絶対参照にします。
 今回は、合計する期間の範囲を「15日まで」と定めているので、「<=2015/7/15」のセルも、3つめの条件として選びます。条件範囲は、2つめと同じく、日付の範囲(B3:B22)となります。こちらも絶対参照とします。
 オートフィルもしくはダブルクリックで、G4からG8までドラッグすれば、品目ごとの合計数が出ます。

 SUMIF関数
 SUMIFS関数と、考え方はほぼ同じです。
 単純に、品目ごとの合計個数を求めたい場合には、こちらを用います。


2015072703.png


 SUMIFS関数とは異なり、まず最初に「(条件)範囲」を選びます。この場合は、C3からC22までの絶対参照です。
 次に検索条件。こちらは品目名なので、今回はF13です。オートフィルを使うので、相対参照にします。
 合計範囲は、個数になります。D3からD22までの絶対参照です。
 オートフィルを使えば完成です。合計値も合っています。


2015072704.png
スポンサーサイト



テーマ:Office オフィス
ジャンル:コンピュータ

Excelの関数・13 よく使う文字列操作の関数(基本編2)

2014.09.27 20:49|Excel関数
 TRIM関数
 文字列の先頭や末尾に挿入された空白(スペース)をすべて削除して返します。文字列内に空白が連続して含まれている場合には、空白を1つ残して、他の余分な空白は削除します。
 名簿などの氏名欄で、余計な空白を削除する場合に用います。なお、文字列内の半角スペースは半角のまま残ってしまいますので、下の例ではJIS関数と組み合わせて全角にしています。

0927.png


 FIND関数、SEARCH関数
 どちらも検索する文字列が何番目にあるのかを求める関数です。FIND関数が大文字・小文字を区別するのに対して、SEARCH関数は大文字・小文字を区別しないという違いがあります。また、SEARCH関数では、検索文字列にワイルドカード(?、*)が指定できます。
 今回はFIND関数を用いて、TRIM関数で返した氏名を、さらに姓・名に分けて取り出します。

 姓は =LEFT(文字列,FIND(" ",文字列)-1) で求められます。この式は、「 」(全角スペース)までより1字少ない文字列を左から返す、という意味になります。

092702.png


 名は =RIGHT(文字列,LEN(文字列)-FIND(" ",文字列)) で求めます。この式は、文字列から「 」(全角スペース)までの文字数分引いた文字列を、右から返す、という意味になります。

092703.png

Excelの関数・12 よく使う文字列操作の関数(基本編1)

2014.09.26 20:27|Excel関数
 住所録、名簿を作るときなどによく使われる関数です。
 ※ 実際にハガキ・DMなどで使用する場合(宛名やラベル名、Wordの差し込み文書、Accessのレポートなどで用いる場合)には、いったんコピーしてから「値」で貼り付ける必要があります(そのまま用いると、数式が挿入されてしまいます)

 ASC関数
 文字列をすべて半角にして返します。郵便番号などで数字に全角が使われている場合に用います。

 JIS関数
 こちらはASC関数とは逆に、文字列をすべて全角にして返します。
 名前などに半角カタカナが用いられている場合、この関数を用いて全角にします。

 UPPER関数、LOWER関数、PROPER関数
 UPPER関数は、文字列をすべて大文字にして返します。LOWERは逆で、文字列をすべて小文字にして返します。
 PROPER関数は、先頭の1文字を大文字にし、それ以降の文字を小文字にして返します。

 LEFT関数、MID関数、RIGHT関数
 それぞれ、「左から指定した文字数」の文字列、「指定した文字から指定した文字までの文字数」の文字列、「右から指定した文字数」の文字列を返します。
 たとえば、C3に「東京都世田谷区三軒茶屋」とあった場合、任意のセルに「=LEFT(C3,3)」と入力すると、「東京都」が返ってきます。「=MID(C3,4,4)」なら「世田谷区」、「=RIGHT(C3,4)」なら「三軒茶屋」が、それぞれ返されます。
 実際に都道府県名を抜き出したい場合には、「県」まで含めて4文字の県(神奈川県・和歌山県・鹿児島県)がありますので、IF関数で論理式を設定する必要があります。
 考え方としては、文字列の4文字目の1字が「県」である場合(TRUEが(MID(文字列,4,1)="県")は左から4文字目まで返し(LEFT(文字列,4))、それ以外(FALSE)は左から3文字目まで返す(LEFT(文字列,3))と考えます。
 そうすると、数式は =IF(MID(文字列,4,1)="県",LEFT(文字列,4),LEFT(文字列,3)) となります。
 これで、住所のうち、都道府県名だけを返すことができます。

092601.png


 都道府県以下の住所を返すには、さらに文字数を返す「LEN」関数を用います。

 LEN関数
 指定した文字列の、文字数を返します。
 住所のうち、都道府県名以下の住所を抜き出したい場合などに用います。
 例えば、上述のように都道府県名のみを取り出している場合、住所全体の文字列から都道府県名の文字数を引いた文字列を返すことができます。

092602.png


 =RIGHT(C3,LEN(C3)-LEN(D3)) で、「C3セルの文字数からD3セルの文字数を引いた文字数分のC3の文字列を、右から返す」という意味になります。
 RIGHT関数を使わずにMID関数を使って表示することもできます。この場合は、=MID(C3,LEN(D3)+1,LEN(C3)-LEN(D3)) となります。「C3セルの文字列のうち、D3セル(東京都)+1文字目(「世」)からC3の文字数引くD3の文字数分の文字を返す」という意味になります。RIGHT関数のほうが簡単だと思いますが、「MID関数を使って抜き出しなさい」という問題がMOSの模擬試験に出てきたので、一応 記しておきます。

 続きます。

Excelの関数・11 SUBTOTAL関数(集計行を表示する)

2014.09.20 19:13|Excel関数
 集計行を表示するには、「=SUBTOTAL(集計方法,セル範囲)」という関数を入力するのですが、もっと分かりやすい方法で集計行を表示する方法があります。
 まず、データを用意します(参照:統計局ホームページ http://www.stat.go.jp/)。

subtotal001.png


 [データ]タブの「アウトライン」グループから「小計」を選択します。
 「集計の設定」ダイアログボックスから、「グループの基準」、「集計の方法」、「集計するフィールド」を選択します。今回は、「地域区分」別に「総人口」を集計したいので、「グループの基準」は「地域区分」、「集計の方法」は「合計」、「集計するフィールド」は、平成24年推計人口が2つのセルにまたがっていて分かりづらいのですが、「総人口比」です。これで、地域区分別の人口集計を出すことができます。

subtotal002.png


 次に、「地域区分」ごとに「人口性比の平均」を出したいとします。グループの基準は「地域区分」、集計の方法は「平均」、「集計するフィールド」は、正しくは「人口性比」ですが、「平成24年推計人口」がかぶっていて間違ってしまっている「(列D)」です。
 なお、このとき、前に集計した分が消えてしまわないよう、「現在の小計をすべて置き換える」のチェックをオフにします。

subtotal003.png


 地域区分ごとに小計が出され、最終行に全体の平均と、総計が出ます。

subtotal004.png


 また、集計を実行すると自動的に行番号の左側にアウトライン記号が表示されます。アウトライン記号から、下位のグループの表示/非表示を設定できます。
 下位グループを非表示にしたほうが、グラフなどを作りやすいこともあります。

subtotal005.png

Excelの関数・10 COUNTIF関数

2014.09.07 19:58|Excel関数
・COUNTIF関数は、指定した範囲内の、検索条件に設定した文字列や数値を数える関数である。=(セル範囲,検索条件)で記述し、セル範囲は絶対参照とする

 これだけだと何のことだか分かりづらいので、こちらで少し書いたRANK.EQ関数と一緒に例を出してみます。

 まず、点数の順位を求めます。順位にはRANK.EQ関数を用います。
 江藤次郎さんの順位はC4に入力するため、C4のセルを選択し、RANK.EQ関数を挿入します。
 関数の引数ダイアログボックスで、「数値」はB4(点数)を、「参照」は、順位をつけたい全員分の点数の範囲(B4:B9)を設定します。オートフィルで参照値を動かさないために、範囲は絶対参照にします。
 江藤次郎さんの順位は「1」です(2つ目の図に結果が表示されています)。

0907_rankeq.png


 「評価」には、「IF関数」で優・可・不可が表示されるようにしています(160点以上が「優」、140点以上160点未満が「可、140点未満が「不可」です)。
 D4からD9までの範囲の間で、「優」をとったのが何人かを求めたいとします(結果はG4に入力します)。
 G4にCOUNTIF関数を挿入し、関数の引数ダイアログボックスの「範囲」で「D4:D9」を選択し、検索条件に「優」を示す「F4」を指定します。なお、「範囲」はオートフィルで参照値が移動しないよう、絶対参照にします。
 「可」「不可」の項目は、それぞれF5、F6となるので、オートフィルで自動的に結果が出るようになっています。

0907_countif.png


 これで、指定した範囲内(D4:D9)の検索条件に設定した文字列(F4)の個数を数えることができます。
| 2019.11 |
- - - - - 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
カレンダー

10 | 2019/11 | 12
- - - - - 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

最新記事

カテゴリ

月別アーカイブ

検索フォーム

RSSリンクの表示

リンク

QRコード

QR

ページトップへ