<   2015年 01月 ( 10 )   > この月の画像一覧

Excelワザ_重複データを除いたデータの数をカウント

さて、ウォーミングアップの次は、
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)

Excelワザ_可変する範囲の名前の定義

過去ブログにも載せましたが…
名前の定義を
範囲が増減しても対応できる方法。
<例>
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)

Excelワザ_SUMPRODUCT関数の補足

例えば、担当者名の列の中で
重複を除いた担当者の数を求める時、
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)


能力評価・キャリア形成


by school-pc

プロフィールを見る
画像一覧

S M T W T F S
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 31

案内

所在:京都府舞鶴市南田辺105
TEL : 0773-75-5512

タグ

(162)
(144)
(83)
(69)
(58)
(58)
(46)
(33)
(18)
(16)
(15)
(13)
(10)
(8)
(6)
(6)
(5)
(2)
(2)
(2)

カテゴリ

全体
タウンパーク舞鶴
PC-School
未分類

以前の記事

2017年 09月
2017年 07月
2017年 06月
2017年 05月
2017年 04月
2017年 03月
2017年 02月
2017年 01月
2016年 12月
2016年 11月
more...

フォロー中のブログ

エキサイトブログ向上委員会

最新のコメント

ありがとうございます! ..
by Faber Sid at 21:02
ありがとうございました。..
by ぴ at 08:44
MSさん、7/29迄に何..
by school-pc at 13:49
もうWin10の強制アッ..
by yuki at 23:34
school-pcさま ..
by kizen777 at 01:58
参考になるのでしたらどう..
by school-pc at 16:59
内容、修正しました。 ..
by school-pc at 10:19
こんにちは。こちらでいろ..
by Tatsuya at 17:59
2013…なんかちがう(..
by school-pc at 09:20
まじですか!?会社のPC..
by ユキンコ at 07:51

最新のトラックバック

venussome.com
from venussome.com
venuspoor.com
from venuspoor.com
www.whilelim..
from www.whilelimit..
http://while..
from http://whileli..
http://www.v..
from http://www.val..
DELL ノートパソコン..
from まいにちの、こと。

ライフログ

ファン

記事ランキング

ブログジャンル

キャリア・資格

画像一覧