Excel。3D集計（串刺し）をつかってみよう！

Excel。3D集計（串刺し）をつかってみよう！

研修や講義で人気が高い、3D集計（串刺し）。しかし、中々わかりづらいので、ご紹介。

条件として、同じセル番地に同種のデータが入っていることが必要。

第2四半期売上集計のB4には、7月～9月までの新宿店Aランチの合計金額を算出したい。

算出したい範囲を選択する。今回はB4:E8。なお、合計列行に数式を設定している場合は、B4:D7とします。

算出範囲の左上セル。今回はB4をクリックして、Σ(オートSUMボタン)をクリックする。

グループ作業の設定をします。
先頭シートをクリック後、最後のシートをShift+クリック。

自動的に先頭シートに移動しているので、そのシートの左上のセル、今回はB4をクリックする。
その後、再度、Σ(オートSUMボタン)をクリックする。

算出したいシートに自動的に戻り、算出されている。

担当者は、各店舗にExcelデータを渡すようにして、そのシートを使用するようにするべきですね。統合という方法もありますが、効率的でないので、一元管理が望ましいです。

Excel。足し算。和算はSUM関数を使うことをお勧めします。

Excel。足し算。和算はSUM関数を使うことをお勧めします。
Excel。
基金訓練などの集中講座や、マンツーマン講習で、
Excel初心者の方に、
必ずお話しすることがあります。
その中の１つをご紹介しましょう。

それが、SUM関数を使うこと。です。

単純な和算だとしても、SUM関数を使用したほうが、汎用性がよいといえます。

D2は、B2+C2という計算式
D3は、=SUM(B3:C3)というSUM関数を使って計算しています。

別に問題はないように思えますが、
仮に、後期は数字が無い場合に”-”(ハイフン)を入力したらどうなるでしょうか？

D2には、#VALUE!というエラーが表示されてしまいました。
D3は、そのまま30という計算がされています。

つまり、SUM関数は、計算範囲に文字が入っていても、
それを除いてエラーを出さずに算出してくれますが、
単純な和算では、文字を足せないということで、エラーが出てしまいます。

些細な事ですが、汎用性ということを意識していくといいでしょう。

Excel。Phonetic関数はふりがなの関数です。

Excel。Phonetic関数はふりがなの関数です。

顧客名簿やら、社員名簿を作成するときに、必要な項目の一つに、
【ふりがな】っていうのがあります。
そこで、簡単なので、知っておきたい関数があります。

それが、PHONETIC関数。

名前って、
微妙な読み方の方もいらっしゃって、
例えば、中田さん　なかたさん　なのか　なかださんなのか？

ビジネスシーンに限らず、失礼がない様にしないといけない所ですね。

さらに、難読地名とかもあるし…。

そこで、まず、Excelのふりがなボタンから、ふりがなを振ると、

このように、振りたいセルの上に表示されるわけです。
人数が少ない場合は、これでもOKでしょうけど、
人数が多くなると、行の高さは、初期値のままにしておきたいわけですね。

そこで、隣の列にふりがなを表示させることができないか？ということで、
今回ご紹介のPhonetic関数を使ってみましょう。

この関数、便利なので、色んな所でご紹介をするのですが、
使い方を間違える方を良く見かけるので、ご注意のほど。
下記のような名簿があったとします。

まず、関数=数式なので、お一人分のセルを範囲選択して、
関数挿入ボタンから、Phonetic関数を検索します。

ふりがなで検索すると便利ですね。
ここで、参照と聞いてきます。

旧バージョンでは、範囲なっていて、わかりづらかったのですが、
参照となって、わかりやすくなっています。
では、名前をクリックします。
これで、終了。

その後、オートフィルタでCOPY。

範囲全部をドラッグしてはいけませんね。ここが注意。
けど、ひらがなで表示したいとか、半角カタカナにしたいという時にどうするのか？

これも、簡単。ふりがなボタンから、ふりがなの設定を選び

表示したい形式を選択すれば完了。

なかなか便利な関数なのですが、欠点もあって、
実はこれ、Excel上で入力した文字でないと表示されません。

例えば、
Wordで作った名簿をCOPYして、
Excelに貼り付けると、文字入力情報が欠落してしまうので、
何も表示されませんので、ご注意の程。

それと、最近、キラキラネームというのでしょうか。
お名前が読めないといいますか、アテ字の方がいらっしゃいます。

Excelで入力した文字情報を、引っ張ってきますので、
一文字ずつ違う読み方で入力した場合、その入力した文字が表示されてしまうので、
結局、入力しなおす必要があるので、
その件数にもよりますが、場合によっては、Phonetic関数を使わない方が
効率がいいことも考えれます。

Excel。VLOOKUP関数。応用編。２つの表から検索するには？

VLOOKUP関数。応用編。２つの表から検索するには？
【名前の定義】+【INDIRECT関数】+【VLOOKUP関数】

部品販売会社さんのPCコンサルをしていて、先日、見積書が困っていると相談がありまして、
複数の表から、見積書を作っているので、
いちいち確認する必要があって大変とのこと。
社外秘なので、下記に似たようなデータを作ってみました。

このケースでは、野菜と果物という別々のリストがあって、
そこから、見積書を作成するというもの。
VLOOKUP関数をしようとしても、管理番号が同じものがあるので、
一つのリストにすることが出来ません。

では、やはり自力でやらないといけないのでしょうか？

そこで、登場するのが、【名前の定義】と【INDIRECT関数】です。

今回は、【名前の定義】+【INDIRECT関数】+【VLOOKUP関数】をご紹介します。

まず、野菜と果物のリストに名前の定義を行います。
G3:I5までを範囲選択して、名前ボックスに、「野菜」と入力します。

これで、名前が設定されます。
同様に、G10:I12を範囲選択して、名前ボックスに「果物」と入力します。

それでは、いよいよ、VLOOKUP関数の登場です。
 
算出するC3をクリックして、VLOOKUP関数を挿入します。
まず、検索値は、B3。
次の範囲。ここで、INDIRECT関数が登場します。

参照文字列をA3。つまり野菜をクリックします。

これは、何をやっているのかというと、
先ほど名前の定義をした野菜の範囲を選択しております。
このINDIRECT関数は、指定した文字列で定義された名前の範囲を表示します。

ですので、A列のジャンルと名前の定義で設定した名前は同じでなければいけません。
「野菜≠やさい」
ということで、
INDIRECT関数と名前の定義を使うことによって、

VLOOKUP関数の範囲を替えることが出来るようになります。
あとは、列番号は2。検索方法はfalse　

で完成！

今回は2種類のリストでしたが、何種類でもOKです。

Excel。VLOOKUP関数。範囲って何？列番号ってどれ？

Excel。おなじみ、VLOOKUP関数。
範囲って何？列番号ってどれ？

Excelで、おなじみのVLOOKUP関数。

見積書・請求書・納品書などで、使用している方も多いでしょうね。
最近では、メジャーな関数だと思われます。

今回は、ピンク色の商品名にリストにある、商品名をもってくるというのを参考にやってみましょう。

しかし、この関数。
結構、引っかかって、うまくいかない人が多いのが特徴ですね。

新人研修や集中講座や、個別などでも、うまくいかない人が多いのです。

で、どこで、引っかかるのか？
まず、1つめは、【何をしようとしているのわかっていない】
このVLOOKUP関数を簡単に説明しようとしたら、コンビニでおなじみのPOSレジをイメージしてみましょう。
コンビニのレジで、商品のバーコードをピッってやると、レジの表示板に、商品名と値段が表示されるわけです。

これをExcelでやりたい。
なぜ、やるのか。時間短縮はもとより、"入力ミスを減らしたい"わけですね。
単価の打ち間違いなんで、論外ですからね。

まず、この目的=やりたいこと　をハッキリさせる必要があるわけです。
次に引っかかるのが、VLOOKUP関数のダイアログボックスのコトバ。

最初のボックスの【検索値】って何？って思いません？
先ほどのPOSレジを思い出してください。
最初に何をしますか？
商品のバーコードをピッってするのですよね。
すなわち、バーコード=商品番号。のことですね。
つまり、今回は、B4をクリックします。

次の【範囲】。
説明を読むとわかる～分けないでしょう。初めての人は。
なんで、関数って"一見さんお断り"なのでしょうか？
この【範囲】は、商品リストのことなのです。
データベースとかリストとかのほうがわかりやすいと思います。
ですので、今回は、F3:H12を選択します。見出しをいれても、動きますので、大丈夫ですが、ここで忘れてはいけないのは、"絶対参照"をしておく必要がありますね。
このようなテーブルとかリストとかは、必ず"絶対参照"をする必要がありますね。

次に、【列番号】。
説明を読んでも、よくわからない人が多いのは、ここですね。
今回の目的は、C2に、リストから商品名を参照したいわけですね。
ですので、商品名を参照。
すなわち、【範囲】の中で、商品名は、左から何列目にあるのか？
Excelに、参照したいアイテムのある列を教えてあげる必要があるわけです。
それが、この【範囲】です。
ですので、商品名は、【範囲】の左から"2"列目にありますので、２を入力。

最後に、【検索方法】
これは、0=false 1=true。のどちらかをいれます。
よく、省略できるとありますが、必ずいれるようにしましょう。
完全合致=falseというのは、わかりづらいですね。
今回のようなケースの場合は、falseを使用します。
なぜか？

例えば、コンビニで、コカコーラをレジにもっていった、バーコードでピッとしたら、たまたま商品リストに入っていない。
なので、"似ている"ペプシをピックアップしてきたら、どう思います？
値段が同じならOKというのは、のぞいて…

これって、よくないですよね。
ですので、完全合致しないと、いけないわけです。
ということで、これで、OKをクリックして完成します。

まぁ、これがVLOOKUPの基本形ですね。
まだまだ、アレンジしないと、仕事では使いにくいですね。
そのあたりは、またの機会に。

Excel。AVERAGE関数とMEDIAN関数

Excel。AVERAGE関数とMEDIAN関数

平均値と中央値？
平均を算出することは、結構あっても、
中央値を算出するというのはなかなかないようでして。
これ、意外と使っていない人が多いですね。

数字というのは、面白いもので、見た目のイメージで、いい悪いを判断しちゃうことが多い。

例えば、下記のような採点結果がある場合、
平均だと、62.14点になり、川崎さんと蒲田さん以外は平均値以下となる。
しかし、
川崎さんと蒲田さんを除いたら、おおむね50点が平均になるのでは？

12点も算出方法で異なる

そこで、登場するのが、中央値という考え方。
中央値を算出する関数=MEDIAN関数

MEDIAN関数をつかって算出してみると、50ポイント。

今回の英語のテストは50点が中心で、
川崎さんと蒲田さんは英語が得意なのか、高得点だったので、
イレギュラーなわけですね。

12点も違う訳です。これは、大きい。

仮に、平均より下は、追試なんてことになってら、5名が追試なわけですね。
けど、中央値という角度から見ると、鶴見さんと桜木さんが追試なわけですね。

まぁ、使用する用途によっては、中央値のほうがすぐれていることがあるわけです。

数字をただ、羅列した表というのは、そろそろ卒業して、
色んな角度からみて、推測と仮定を繰り返し、
分析していくというPowerが備わるといいですよね。
今後、ちょっと、覚えておくといい関数ですね。

そうそう、関数の見つけ方ですが、
関数ダイアログボックスで、中央値で検索すると早く見つかりますよ。
MEDIANって覚えにくい場合は、是非。

検索で、中央値と入力すると見つけやすいですよ。

Excel。RANK関数。RANK.EQ とRANK.AVGの違い。

Excel。RANK関数。RANK.EQ とRANK.AVGの違い。

Excel2010 で、順位を算出のときに使用する関数。
Excel2007までは、RANK関数でしたが、
Excel2010では、RANK.EQ関数とRANK.AVG関数へと変更がありました。

ちなみに、EQは、イコールで、AVGは、アベレージです。

旧来のRANK関数は、RANK.EQ関数を使用します。
新登場のAVGは、どうなるのかというと、仮に、2位が2つある場合は、EQではそのまま同率2位だったのですが、AVGでは、2.5になります。（2位＋3位）/2つ＝2.5ってことですね。
下記図を参考にしてください。

EQだと、品川と渋谷が同率2位ですね。
しかし、AVGだと、同率2.5位になっていますね。

なお、RANK関数は、すべての関数にしないと、"発見"できませんので、
旧バージョンで作成したファイルを2010で修正する場合は注意する必要がありますね。

しかし、何気なく、使おうとして、見つからないとびっくりしますよね。
2007の時の、印刷プレビューはどこ？を思い出します。

あと、関数ダイアログボックスの順序。
0以外だと、昇順だとか説明書きはありますが、初めての人には、意味不明。
もっと改善できないのかなぁ～。

Excel。累積を求めるときには、sum関数の範囲をアレンジ。

Excel。
累積を求めるときには、sum関数の範囲をアレンジ。

下記の表で、累積を求めようとした場合。

よく見かけるのが、
C3には、=B3として、
C4には、=C3+B4にして、オートフィルで連続COPYしていくという方法が一般的だと思います。
しかし、仮に4月5日が、臨時休業して、0と表記するのではなく、
【-】のような文字を入力したとすると…

当然、文字と数字は足せないので、エラーが表示されちゃうわけですね。
これじゃ困っちゃうわけです。
しかし、sum関数を使うと、エラーは回避できる。
こんな感じ。

ここで、ポイントになるのが、sum関数の引数。すなわち範囲。
c3には、=SUM($B$3:B3)と設定します。
こうすることで、オートフィルで、連続COPYすると、B3が最終的にB11になります。
つまり、
B3を起点として、距離がどんどん広がっていく計算ができるわけですね。
エラーを回避するためにも知っておきたい技の一つですね

Excel。曜日を表示するには、ユーザー定義書式

Excel。曜日を表示するには、ユーザー定義書式

Excelで、よく曜日を入力することがあると思いますが、いちいち、この日は何曜日？ってことってありますよね。
例えば、

上記のように、日付があって、そのとなりに、曜日を入れるとします。
今回のように一度きりならば、カレンダーで確認してもいいでしょうけど、汎用性を考えた場合に、いちいちカレンダーで確認するのは、面倒くさいですよね。
Weekday関数を使用して算出する方法もありますが、ここは、もっとシンプルに生きたいと思いますので、今回は、【ユーザー定義書式】を使って紹介していきます。

まずは、準備としてC3に=B3という数式を設定します。

このC3の表示形式をアレンジしていきましょう。

色んな方法で、セルの書式設定を表示できますが、ショートカットキーで、ctrl+1が便利だと思われます。この１は、テンキーではダメですので、ご注意を。
 
さて、分類を、ユーザー定義書式にあわせて、種類を一度削除して、aaaaを４つ入力してみます。

で、OKを押すと、

表示形式がかわって、水曜日と表示されました。

不思議ですよね。
ちなみに、
aaaaで水曜日と表示されます。
aaaで水
ddddでWednesday
dddでWed
と表示されます。非常に便利ですので、使ってみませんか？

(c)YandSシステムズ