さて、ウォーミングアップの次は、
Mさんから頂いた課題。
「データがどんどん増えていき、
重複する町内名が出てくるが、
重複データを除いた実際の町内の数はいくつか?」
という課題。
昨日のブログの可変する範囲名のつけ方。
一昨日のブログのSUMPRODUCT関数を使った
(Sumproductの補足も)
複数条件のデータの取り出し。
これらを使って、やってみましょう。

その前に、
いろいろなやり方を検証してみよう。
b0186959_9394871.jpg


準備として、
A列の番号は、自働的にふれるようにしておく。
セルA2に「1
セルA3には「=IF(B3="","",A2+1)
という式で、連番をつけておく。

簡単なものから
<方法1>
1.別シートに必要な列だけ(A列とB列)コピー。
2. データ→重複の削除
3. 町内名にチェック→OK
b0186959_9523980.jpg

b0186959_10393473.jpg

これで、重複を除いた町内は「7」ということが分かる。

<方法2>
ピボットテーブルを使う。
1.挿入→ピボットテーブル
→新しいシート
行エリアに町内名
値エリアに町内名(会員名でも可)
b0186959_1041746.jpg

行ラベルに表示された町内名を
COUNTA関数で出すと、「7」
データの総数は「25」ということが分かる。
それぞれの町内の数もわかる。

さて、方法1,2どちらも
データが変化しない場合です。
データが増えたり、減ったりする場合は、
いちいち範囲を選択しなおしたり、
データ更新をしないとだめです。
そこで、OFFSET関数。
そして、SUMPRODUCT関数。

<方法3>
1.町内名を名前定義。
b0186959_10162064.jpg

2.例、セルG2に式
=SUMPRODUCT(1/COUNTIF(町内名,町内名))
b0186959_1029796.jpg

この式で、町内名が増減しても
一意の町内の数が出てくれます。
例)№7~下のデータを削除すると、
b0186959_10434425.jpg


[PR]
by school-pc | 2015-01-30 09:39 | Comments(0)

過去ブログにも載せましたが…
名前の定義を
範囲が増減しても対応できる方法。
<例>
b0186959_11354571.jpg

店舗名が増えたり、減ったり
するたびに範囲を指定しなおすのは面倒。
で、範囲を可変にするために
Offset関数を使います。
1.名前の定義→新しい名前
 →「店舗名」
 →参照範囲に
 「=OFFSET($B$1,0,0,COUNTA($B:$B)-1,1)」と入力。
b0186959_166516.jpg

2.利用方法
 ①例えば、店舗名一覧リストから
 選択するだけで、店舗名が入るようにするには、
 店舗名を入力したい範囲を選択
 →データ→データの入力規則
 →入力値の種類をリストにし、
 →元の値に「=店舗名」とする。
 
b0186959_1695686.jpg

 ②入力したいセルの右に下向き三角がつくので、
  それをクリックすると、一覧から選択する
  だけで、店舗名が入る。
b0186959_16135330.jpg

 ③店舗が増えると、
  リストの店舗名も増えるし、
  減るとリストから削除される。

次の段階…
重複する町内の数を調べるには…
[PR]
by school-pc | 2015-01-29 11:36 | Comments(0)

例えば、担当者名の列の中で
重複を除いた担当者の数を求める時、
b0186959_1114598.jpg

という式で求められます。
1.まず、COUNTIFで担当者が
何回出てくるかを見ます。
b0186959_1165680.jpg

「井上」は「4」回です。
2.次に出てきた数を「1」で割ります。
=1/countifの値を見ると
b0186959_1114112.jpg

3.どの担当者も合計すると、「1」になるので、
「1/COUNTIF]で求めた値を合計すると、
重複しない担当者の数が出ます。
b0186959_11232380.jpg

4.これを一行の式で表すと、
=SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11))
b0186959_11272152.jpg

[PR]
by school-pc | 2015-01-28 23:46 | Comments(0)

Excelワザ_SUMPRODUCT関数

b0186959_9383481.jpg

売上合計出すのに
ふつうは、=単価×数量で金額を出し、
それをSUM関数で合計する、という手順ですが、

『Sumproduct(サムプロダクト)関数』の使い方

1.単純な合計の出し方
セルD13には、
『=SUMPRODUCT(D2:D11,E2:E11)』
という式を立てます。
それぞれの行の単価と個数を掛けたものを、合計します。
掛けたい範囲を指定するだけで合計が出ます。

2.条件付き合計
「担当者が井上」の合計金額は、
SUMIF関数なら
F列に金額欄を作り、
=SUMIF(B2:B11,"井上",F2:F11)
という式で出ますが、
b0186959_11342855.jpg

金額欄を作らずに一度に出す場合、
Sumproduct関数で、条件付き合計が出ます。
ここでのポイントは、
条件となる範囲を「×」でつなぎます。
これで、「AND」条件の意味になります。
=SUMPRODUCT((B2:B11="井上")*D2:D11*E2:E11)
b0186959_11344491.jpg


3.複数の条件付き合計
「担当者が井上で、かつ単価が100000以上のものの合計」
同様に「×」で条件をつなぐ式を作ります。
=SUMPRODUCT((B2:B11="井上")*(D2:D11>=100000)*D2:D11*E2:E11)
b0186959_11583282.jpg

これも金額欄があると、
SUMIFS関数で出るけど…(^-^;

4.複数の条件付きの検索
合計ではなく、条件に合ったものを検索する場合
「支店名が舞鶴で、担当者が井上で、
単価が100000以上の個数」を調べたい場合、
該当する行をIndex関数で取り出します。
=INDEX(E:E,SUMPRODUCT((A2:A11="舞鶴")*(B2:B11="井上")
*(D2:D11>=100000)*ROW(A2:A11)))

b0186959_12164238.jpg

「支店名が舞鶴で、かつ担当者が井上で、
かつ単価が100000以上である行の行番号を
ROW関数で取り出し、INDEX関数でE列の行番号に
対応するセルの値を取得する。

使いこなすとなかなか便利な関数です。
で、これを範囲が可変のものに
応用すると、…
次に…
[PR]
by school-pc | 2015-01-28 11:02 | Comments(0)

葉室麟の小説面白い!

b0186959_16594437.jpg

[PR]
by school-pc | 2015-01-27 23:13 | Comments(0)

今年は…

今年のおみくじ…
『大安』です!
籠神社、戎神社
b0186959_93141.jpg

b0186959_931431.jpg

1月は…
お葬式やその後のバタバタや…
風邪を引いたり、腰痛になったり…
コンディション最悪の月でした…
さて、ぼちぼち気合を入れないと…
[PR]
by school-pc | 2015-01-26 09:03 | Comments(0)

b0186959_13505060.jpg

[PR]
by school-pc | 2015-01-07 13:50 | Comments(0)

b0186959_13172938.jpg

[PR]
by school-pc | 2015-01-05 13:09 | Comments(0)

読書&DVD鑑賞

前からなかなか良いとは思ってたが
「黒田官兵衛」見てから
さらに
岡田准一君ファンになりました。
「天地明察」は以前見たけど、
この正月休みに
「永遠の0」見ました。
b0186959_1040121.jpg

まぁまぁですね。
岡田准一君は時代劇が似合ってる。
「蜩の記」という映画
まだDVD化されていない?
ので、原作の本、読みました。
Amazon、すごいね、
2日に注文したら、3日午前中に届いた(^-^;
b0186959_104563.jpg

で、2日で読んだ。
面白かった~~~。
結構、侍もの好きです(^^♪
[PR]
by school-pc | 2015-01-04 23:39 | Comments(0)

今年もよろしくお願いいたします。
正月早々舞鶴は大雪です〜
b0186959_10215582.jpg

[PR]
by school-pc | 2015-01-01 16:43 | Comments(0)