タグ:Excelワザ ( 167 ) タグの人気記事

b0186959_18114216.jpg

A1に計算式
(=は、いらない)
この計算結果を
B1に表示させます。

1.名前の定義
 数式→名前の定義
 「名前→計算結果表示」
 「参照範囲に
 =EVALUATE(A1)」と入力。→OK。
b0186959_1871715.jpg

2.表示させるB1に
 「=計算結果表示」と入力。
b0186959_18143747.jpg

3.すると、
b0186959_18154394.jpg

出ました!
4.式を変えてみると、
b0186959_18285132.jpg

※このファイルは「マクロ有効ブック」
にしないとだめです。
一般機能の関数の中には「EVALUATE]
という関数はありません。
[PR]
by school-pc | 2015-02-06 18:16 | Comments(0)

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

Excelには、様々なグラフが簡単に作成できます。しかし、伝えたい内容を最も適切なグラフで視覚的に表現するためには、少しコツが必要です。数値の意味がより明確になり、伝えたい内容の説得力が増すグラフを作成しましょう。

伝えたい内容に応じてグラフを選ぶ!
3大基本グラフ
①棒グラフ   →数量:要素の大きさや量を示したい
②折れ線グラフ→推移:量や数値の推移を示したい
③円グラフ   →割合:全体における割合を示したい
そのほかのグラフ
①面積図:多数の要素における割合を示したい
②面グラフ:時間軸に沿った総量の変化を示したい
③ヒストグラム:量のバラつきを示したい
④散布図:要素同士の関係性を示したい
⑤レーダーチャート:標準値に対する差異を複数観点で示したい
⑥エラーバーグラフ:平均だけでなく最大値や最小値を示したい

まずは、3大基本グラフ
①棒グラフ→数量:要素の大きさや量を示したい
<例>店舗別商品分類別の売上一覧をグラフにする
b0186959_1456584.jpg

・セル範囲「A3:F8」を選択し、
①集合縦棒グラフにすると…
b0186959_1456251.jpg

  店舗別の売上高が表示されたが、
  5本も縦棒が並ぶと見た目が煩雑になりデータの意味がわかりづらい。 
②そこで、これを積み上げ縦棒グラフに変更すると…
b0186959_15181799.jpg

  各店舗の全体の売上も商品分類の構成もわかりやすくなった。
  さらにもう一段アップ!
  系列の棒の太さを太くし、区分線を入れると…
b0186959_1504358.jpg

  力強さと比べやすさが格段にアップした!
③これを100%積み上げ縦棒グラフにすると、
b0186959_1513215.jpg

店舗ごとに各商品分類が売上全体のどの位の割合を占めているか、を示すことができる。
縦軸が金額からパーセンテージになり、売上金額とは無関係に、各商品分類の構成比を比較できる。

横棒グラフについて
次にこのように項目の文字数が多い場合
b0186959_152412.jpg

集合縦棒にすると、
b0186959_1521664.jpg

項目名が斜めに表示され、棒グラフのスペースが狭くなる。
一般に、グラフは縦よりも横の幅が長くなることが多いため、
このような場合は、横棒グラフにすることで、収まりの良いグラフが作成できる。
b0186959_1531990.jpg

項目名が長いグラフは、横棒グラフにするのが見栄えUPですね!

②折れ線グラフ→推移:量や数値の推移を示したい
<例>5年ごとの出荷量の推移を見たいとき…
b0186959_1545835.jpg

集合縦棒グラフだと…
b0186959_1551216.jpg

見た目が煩雑になり、
    それぞれの野菜の出荷量が独立した印象になり、
    時系列の推移がわかりづらい。
 そこで、時系列を表すのに最適なのが、折れ線グラフ!
b0186959_156960.jpg

それぞれの野菜の出荷量がひとつながりのデータとして示され、
    全体を通して各野菜がどの程度の水準かが一目でわかります。
時系列の推移は折れ線グラフで!

③円グラフ→割合:全体における割合を示したい
商品分類ごとに売上高を表示したい。集合縦棒グラフなら…
b0186959_1571236.jpg

  売上高全体の中で、それぞれの分類の占める割合を見たいとき、
  縦棒グラフではわかりにくい。
  そんな時は、円グラフを使う。
b0186959_1575390.jpg

  全体の中での割合が一目瞭然!
横帯グラフもOKです。
b0186959_1583883.jpg


その他、散布図、面グラフ、レーダーチャート等ありますが、またの機会に…

データを表現するのに、最適なグラフを選びましょう!
[PR]
by school-pc | 2014-12-03 15:10 | Comments(0)

雑誌からのネタ
並べ替えたりするとき、
会社名などに、
「株式会社」や「有限会社」や
「社団法人」等々…
があると、並べ替えがうまくいかない…
「株式会社…」が続いたり…
b0186959_21235247.jpg

そこで、「株式会社」を一発で取り去るワザ
チト難しい…
配列数式を使います。
「Shift」と「Ctrl」押しながら「Enter」
を押す配列方式!

1.「会社形態」を
 名前定義しておく。
 セル範囲「D2:D7」に「会社形態」と定義。
2.セル「B2」に式
 「=INDEX(SUBSTITUTE(PHONETIC(A2),会社形態,""),MATCH(MIN(LEN(SUBSTITUTE(PHONETIC(A2),会社形態,""))),LEN(SUBSTITUTE(PHONETIC(A2),会社形態,"")),0))
3.式が立ったら
「Shift」と「Ctrl」押しながら「Enter」
を押す
b0186959_21263252.jpg

式の前と後ろに「{ }」中括弧が付きます。
これを、昇順に並べ替えると、
b0186959_2117287.jpg

うまく並べ替えられました。

SUMIFSやCOUNTIFSなど「S」
の付く関数ができたことによって、
配列数式、もう活用することはないかと
思いきや、まだまだ、活用法があるか~~~(゜.゜)
[PR]
by school-pc | 2014-11-26 21:19 | Comments(0)

「重複の削除」
リストの中で、重複するレコード(行)
を削除する機能。
で、これを使うと、
「担当者」だけ、「取引先」だけ
などを取り出すことができます。
例えば、売上表があって、
担当者の人数だけ、
何人か調べたいとき
ピボットテーブルを使うと、出ますが、
単純に担当者を抜き出したいとき、
「重複の削除」を使うと便利です。
こんな売上表から
b0186959_8541563.jpg

担当者は何人かなぁ…
1.まず担当者の列をコピーして、
 新しいシートに貼り付け、
b0186959_9413726.jpg

2.貼り付けた状態から
 データ→「重複の削除」をクリック。
b0186959_9425212.jpg

3.こんなメッセージが出て
b0186959_9435847.jpg

4.結果、102人の中から
 担当者は「9人」でした~~(^^♪
b0186959_9474057.jpg

同様に、取引先は
b0186959_950251.jpg

3件か~~。
ちょっと、便利に使えるんじゃない(^_-)-☆
[PR]
by school-pc | 2014-10-23 09:46 | Comments(0)

そうか、こんな手があったか~~
今更ですが^_^;
一度に複数の名前の定義が出来るワザを紹介。

1.名前の定義をしたい複数列の範囲を選択。
b0186959_19383719.jpg

2.数式タブ→定義された名前
 →選択範囲から作成
b0186959_19411331.jpg

3.以下に含まれる値から名前を作成
 →上端行にチェック→OK
b0186959_19414224.jpg

4.名前の管理をクリックして確認
→4つの範囲が名前定義されています!
b0186959_19432789.jpg

一度に複数の名前定義したいとき
便利かも(^_-)-☆
[PR]
by school-pc | 2014-07-23 23:36 | Comments(0)

2013から便利な機能「フラッシュフィル」
2014-6-20の記事

氏名に分けるだけならいいが、
こんな場合、
会社名、支店名、部署名
に分けたいとき、
支店や、部署がない
空白の場合は、
フラッシュフィルでは
うまく取り出すことができません。
b0186959_1432206.jpg


そこで、Excelには、
良い機能があります。
「区切り位置指定ウィザード」
1.区切りたいデータを選択、
2.データ→区切り位置
3.「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」
  にチェック→次へ
b0186959_14535828.jpg

4.「スペース」にチェック→次へ
b0186959_21162177.jpg

5.列のデータ形式「G標準」.
 表示先を表示したいセルを選択→完了
b0186959_14542698.jpg

6.「既にデータがありますが、置き換えますか?」
  と表示されるが、「OK」
b0186959_14501065.jpg

b0186959_1447414.jpg

データのないところは、空白になってますねぇ~
Goodな機能です。
[PR]
by school-pc | 2014-07-21 14:48 | Comments(0)

能力評価・キャリア形成
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