エクセルで専門専門知識なしでもビジネスプランがシミュレーションできる！What if分析の使い方

あなたは会社で事業の中期計画や

担当する新規プロジェクトの今後数年先の

ビジネスプラン、つまり収益の見通しを

求められることはないでしょうか？

そして色んなシナリオを想定した

複数パターンを

用意して分析するケースもあるでしょう。

これが経営の肝である感応度分析です。 

いくつも前提条件があって、

それを力技で計算して

収益計画に落とし込むのはとても難しく

現実的ではありません。

そんなことは普通できないから、

あなたが超適当にエクセルで計算するか

ビジネスをよく知らない

社内の専門家に任せるか

何十万円を払って外部のコンサルに頼まないと

普通はできないと思いますよね。

実際私の周りでも

財務部やM＆A担当部署以外の

人でまともにできる人を

みたことがありません。

しかしあなたが仮に財務モデリングの

専門知識がなくても

エクセルの機能さえ使えれば

十分あなたの新規プロジェクトや

社内のビジネスプランの

感応度分析はできます。

自社の競争力、該当マーケットの状況といった

実務に詳しいあなたがもし

ビジネスプランの感応度分析

を瞬時にできたら間違いなく

周囲を驚かせることが

できるでしょう。

というのも実ビジネスを担当しているあなたが

経営の重要なスキルも使えるわけですから。

感応度分析の例をあげて使い方をお伝えします。

2020年の売上を起点にインフレや市場環境を加味して

毎年5％で売上が上昇すると仮定します。

そして他の諸条件を踏まえて

2024年時の利益計画は

180,626万円となったとします。

その時に2020年の売上の起点と

その成長率が変化すると

2024年の利益がどう変化するか

見える化されています。

これが感応度分析の例です。

この例ではわかりやすくするため

条件付き書式設定を

入れて19万円以上に緑色、

18万円以下に赤色をつけて

わかりやすく整理しています。

他にも同じやり方で直近の見込みの振れ幅を

精査をするにも役立つでしょう。

これはエクセルのWhat if分析という機能を

使うことで実現しています。

２つの変数を縦軸と横軸に並べ、

それぞれ振れ幅を入力したら

あとは自動的に感度分析の計算がされます。

What if分析の使い方

まずはPLを作っておきます。

ポイントはPLの外に前提条件を示した

ベタ打ちのテーブルを用意することです。

1.求めたい変数とそれに影響する変数２つを縦横軸に設定

求めたい変数（目的変数）を決めます。

今回は2024年の営業利益にとします。

そしたら同じシート内からそれを示しているセルを参照して、

マトリックスの一番左に入力してください。

そして営業利益に影響を及ぼす年次成長率を縦に、

売上を横軸に適当に数字を並べます。

 2.What if分析メニューのデータテーブルをクリック

 マトリックス表をセル選択してから

What if分析メニューの"データテーブル"をクリックします。

次にデータテーブルに参照形式で引っ張ります。

ここではベタ打ちになっている所つまり

前提条件がかかれたB列から売上と

年次成長率のセルを

それぞれ参照します。

ベタ打ちセルを参照する

これで”OK”を押して終わりです。

あえていえばマトリックス表の左上にいれた

目的変数の180,626円を白色文字で見えないようにすると

より表が見やすくなるでしょう。

まとめ

ファイナンシャルな感応度分析は

専門知識や経験が必要とされがちです。

確かに特殊な地域、マーケットや新市場など

勘所が掴みにくい変数の設定などをする際は

プロの力が必要になるでしょう。

しかしあなたが行っている通常のプロジェクトレベルの

感応度分析ならエクセルのWhat if分析という

機能さえ使えれば、専門知識がなくても十分可能です。

専門化以外使える人があまりいないからこそ

ビジネスで実務をやっているあなたが

もし使えるようになれば

とてつもなく差別化できる

スキルになると確信しています。

あなたがもし業務で利益計画を

求めらているなら

What if分析は強力な武器になることでしょう。

エクセルの”〇〇以外”の条件に該当する関数を作る方法

エクセルで〇〇に当てはまる条件

を足し算したり該当するセルを見つける

ことはよくしますが、

〇〇以外に該当する、〇〇という文字を含まない

セルを平均したり、数えたりするケース

もあると思います。

今日はそういった条件付きの集計方法の

代表例をいくつかお伝えします。

無料のエクセルファイルです。

こちらを使って解説します。

今回解説しきれていない例も含まれているので

ダウンロードしてみてください。

眺めているだけでなくダウンロードして

実際に自分の手を動かすことをおススメします。

というのも解説を読んで

なんとなくわかっても

いざ自分でやろうとすると

できない！ってことはよくあるので。

drive.google.com

〇〇以外は"<>任意の文字"

例えばこのような１K１LDKの平均家賃相場の

データがあったとします。

例として

東急田園都市線以外の平均家賃と

東京都以外の平均家賃、

東急田園都市線以外かつ東京都以外

の３つを取り上げます。

 

１）東急田園都市線以外

条件に当てはまる平均を求めるので

AVERAGEIFS関数を使います。

使い方は

平均対象範囲：どこを平均する？（どの数値）

条件範囲１：条件をつける項目の範囲は？

条件１：その条件範囲にどんな条件をつける？

で作れます。

条件１に"<>東急田園都市線"といれます。

今回は複数条件ではないので条件２は入れずにOKです。

２）東京都以外

条件１に"<>東京都"といれます。

３）東急田園都市線以外かつ東京都以外

 今度は１かつ２の条件、つまり

東急田園都市線以外かつ東京都以外の平均家賃を

求める時はどうしたら良いでしょうか？

１の式に２の式を後ろに続けて書くだけです。

ただし頭の平均対象範囲は１の時だけでOKです。

１から３番まで解説してきました。

その他の例もあるのでそれらは

エクセルをダウンロードしてご参照ください。

そのファイルの計算式をパクッて

あなたのお仕事にお役立てください。

エクセルの勉強は本や動画を読み込むより

とにかくパクって真似てやってみる、躓いたところだけ

ネットで解説を読んで解決する、これが近道です。

そのために無料でテンプレートを配布しておりますので

ご活用ください。

エクセルの表中にある0と空白の意味は違う、空白セルにゼロを一瞬で入れる方法

エクセルで何か表を作る時、

何となくデータが0のところは入力せず

空白のままにして、データがあるところだけ

入力してしまうことないでしょうか？

これを無意識でやってしまう人は

空白と0の意味を理解できていない

ことになります。

問題はその意味を知らない

ということ自体はではなく、

実はそれが第3者視点に立った表になってない

１つの典型例だということです。

細かいことですが、

０と空白の意味を理解して、

受け手に優しいエクセル表を

作りましょう。

空白と0の違い

一体どういうことか？

まずは次の表を見てください。

あなたがこの表に数字を入れたとします。

データが0のところは入力が面倒なので

空白にしました。

でも今度はあなたが受け取る側、

例えば部下である誰かから上司のあなたに

これが送られてきたとしましょう。

何か思うことないでしょうか？

何かこのデータをそのまま使って

良いのかなと思わないでしょうか？

空白というのは受け手にとって

データがない状態か、

入れ忘れているのかを意味します。

しかし受け手にはわかりません。

なのでデータがなくても原則０と入れましょう。

０というのは入れ忘れではなく、

ないんだよという意味です。

確かに資料が細かく見た目がチカチカするから

0を非表示にすることもありますが、

それはあくまで0を入力した上で

非表示にすると受け手に優しいです。

尚、空白になっているセルを一括して

0を入れたい時ありますよね？

それは下記の通りやると早く確実に

できるのでやってみてください。

空白セルに一括してゼロを入れる方法

1．表のどこかのセルを適当に一つ選択してください。

そのあとにCtrl＋Aを押してください。

表全体が選択されました。

表の中のどこかにセルをアクティブにしてCtrl＋A

2．Ctrl+Gを押して"セルを選択"をクリック

Ctrl+Gを押してジャンプメニューを出す

3.”空白セル”を選択してOKをクリック

4．複数の空白セルが選択される

5、0を入力してEnterを押すと一つのセルに０が入ります。

それを確認してからそのまま最後にCtrl+Enterを押します。

すると空白セルにすべて0が入力されました。

まとめ 

空白というのは受け手にとって

データがない状態か、

入れ忘れているのかを意味します。

しかし受け手には判断できません。

なのでデータがなくても原則０と入れましょう。

０というのは入れ忘れではなく、

ないんだよという意味です。

非常に細かいですがこうしたちょっとした

工夫が受け手にとって優しい資料になり、

印象が変わるものなので、

もしあまり意識してこなかった方は

ぜひ参考にしてみてください。

エクセルの重複データの削除方法

エクセルのデータベースで重複データを

削除したい時どうしましょうか？

目で一つ一つ削除するのは卒業して

エクセルにやらせましょう！

小難しい関数は不要、簡単2ステップで

できますのでご紹介します。

例えば路線別、駅別家賃相場データがあります。

複数路線が通る駅、例えば渋谷駅、新橋駅や

神田駅は重複しています。

今回路線は違うけど

同じ駅名なら1つのデータにしたい

とします。

重複データ

1．データタブの中の重複の削除

データベースの中の任意のセルを選択してから

データタブの中にあるデータツール項目の中に

”重複の削除”があるので選択。

重複の削除

2．重複項目を選択

するとデータベース範囲が自動でつかまれて、

ダイアログボックスがでてきます。

今回は駅名が被ったら重複データとみなし削除したいので

駅だけにチェックをいれてOKを選択。

もし沿線、都道府県、駅、家賃すべての項目で

同一のデータがあった場合重複データとしたい時は

”すべてを選択”を選んでください。

通常はこちらの使い方が多いと思います。

今回それは存在しないことがわかっているので

駅だけにチェックを入れました。

どの項目で重複データがあったら削除したいかを選択

これで重複データが削除されました。

例えば渋谷駅は一つだけになりました。

これでおしまいです。

重複データ削除後

エクセル！初心者用必見！基本的なピボットテーブルの使い方

エクセルのピボットテーブルが苦手という方も

いるのではないでしょうか？

今回は多くの人が興味がありそうな

東京神奈川の個人的に選んだ

主要JR線と私鉄の駅ごとの家賃データを

題材にしてまとめてみました。

身近なデータで

楽しくピボットテーブルを

学んでみませんか？

尚、データはYahoo不動産の

1K1DKカテゴリーの家賃

を使用しています。

まとめたデータは無料で置いておきます。

drive.google.com

ピボットテーブルの作り方

データベースを完成させます。

そしたら形式にした範囲を選択します。

マウスで範囲を選んでも良いですが

"Ctr+A"を同時に押すと早く

データ範囲を捉えられます。

そのあとに

"Alt+N+V"を順番に押します。

するとこんなダイアログボックスが

でてきます。

ピボットテーブルをデータベースと違うシートで

作りたいときは”新規ワークシート”、

同じシートで作りたい時は

"既存のワークシート"を選んで下さい。

今回は前者を選択してOKを押します。

データベースには４つの項目がありました。

それを縦、横、集計データの３つに

分類します。ドラッグ＆ドロップ

でできます。

縦項目に置きたいものを”行”

横項目に置きたいものを”列”

集計したいデータを”値”

にドラッグ＆ドロップします。

最低行か列のどちらか1つと値を選択する

ことが必要です。

尚”値”は文字通り数値がある

項目にしましょう。

"フィルター"は任意です。

絞りたいキー項目があればここに

ドラッグ＆ドロップします。

今回は縦項目に沿線と駅名の

２つを置きました。

横はなしにして、家賃を集計してみました。

都道府県で可変で絞れるようにしたいので

都道府県を”フィルター”に置きました。

するとピボットテーブルが一部だけしか

入りませんがこのように完成です。

ランキング順（降順）

データのところで右クリックして

"並べ替え"を選び"降順"を選択しましょう。

そうすると数字が大きい順で並び変わります。

集計方法を合計から平均へ変更する

ピボットテーブルはできあがると基本的に

データを合計されます。しかし今回は

駅や沿線ごとの家賃なので足し算ではなく

平均にします。

平均にするにはデータの所で右クリックをして

"値の集計方法"を選んで"平均"を選んで

みましょう。

ここからはピボットテーブルを使って

東京神奈川の主要沿線の家賃相場

を分析した結果をシェアします。

 東京で一番高い沿線は山手線ではない

東京都内だけに絞って一番高い沿線は

実は山手線ではなく

東京メトロ銀座線でした。

これは山手線沿線でも

比較的家賃が安いエリアがあるため

平均が下がったためです。

そして東京都にある駅の家賃は10.7万円でした。高すぎる。

隣の神奈川になると6.8万円まで落ちます。

だからみんな郊外に住んで都心行きの電車が

猛烈に混むわけですね。

主要沿線別家賃相場

山手線の家賃が安い駅

東京、神奈川家賃ベスト10

東京の1位は”表参道”でした。なんと14.５万円。

都心部ながら自然が多い大きな公園があったり、

閑静な住宅街があったりと都心らしくない何かを

持ち合わせている駅が多いように見えます。

東京家賃ベスト10

神奈川の1位は”馬車道”でした、家賃は8.9万円。

横浜駅ではないのが意外でしたね。

上位はみなとみらい周辺のエリアが多く、

なんと大人気な武蔵小杉がランク外に

なっています。

神奈川主要沿線1K1DK家賃ランキング

エクセルで複数セルの単位を一発で変える方法

今晩は(^^)

今日はエクセルの小ネタのご紹介です。

皆さん作成した表で単位を一括して変えたい時

ないでしょうか？

皆さんもコピペする際によく見慣れている

”形式を選択して貼り付け”というメニューを

いつもと少し違う使い方をするだけで

実現できます。

例えばこんな事業部別売上表があるとします。

 桁数が多くて千円単位にしたい場合

ないでしょうか？ 

電卓で計算するのは論外ですし、

計算式を横に作ってから

値貼り付けするのも不要です。

私はこのやり方を知らなくて

1年前まで計算式を余白で作って、

計算した結果を値貼り付けしていました。

もしああなたも同じやり方しか知らないなら

明日からもっと早くスマートにできる

方法があります。

1．余白のどこかに1,000と入力して、

その1,000と記入したセルをコピーします。

2．単位を変更したいセルを選択します。

3．ショートカットキー"Alt+E+S"を押して

「形式を選択して貼り付け」メニューを

開きます。

もちろん右クリックでも可能です。

値（V)と除算（I)を選択してOKを押します。

4．すると一括して複数セルの単位を

変更できました

今回は割り算をしましたが、もしも単位を

千円から円にしたい時は値（V)と除算（M)を

選択してOKを押します。

この2パターンは個人的には良く使うので

割り算したい時は"Alt+E+S＋V＋I"

掛け算したい時は"Alt+E+S+V+M"という

ショートカットキーを覚えました。

するとより早く単位の変更が

できるようになります。

脱線しますが、

ショートカットキーについて一言二言。

自分がよく使うメニューだけでも

先に覚えると作業効率が上がります。

例えば皆さんの中でもコピペする時

CTL+CでCTL+Vを使う人は多いでしょう。

これはいかがでしょか？

”ALT+W+F+F”

私の場合はこれも良く使います。

これはウインド枠の固定、解除

両方の時に使えます。

ショートカットキーを覚えると

作業効率が上がるとよく言われますが

半分正しくて半分嘘です。

そのヒトのレベル次第で大きく効果は変わります。

エクセルの苦手な人は

ショートカットをひたすら覚えるより

基本的な関数、表、グラフ、デザイン等を

まずは学ぶべきです。

なぜならショートカットキーは作業を早く行う

手段にすぎないからです。

 やるべき作業が明確にわかっていてそれを

実現するのにショートカットキーは効果がありますが、

そもそもイメージ通りエクセルで作業する

工程がイメージできないエクセルが苦手な人にとっては

作業を早くするショートカットよりも、

イメージ通りの成果物を作るために必要な関数の使い方や

グラフの基本的な使い方を使いこなせる

ことの方が重要です。

野球で言えばショートカットキーは

変化球のようなものです。

基本のストレートを

コントロール良く投らげれてから

変化球を身に着けると、

とても効果的ですが、

野球初心者がいきなり変化球を何種類も

覚えようとするのはあまり良い練習とは

思いませんよね？

言い換えるとエクセル初心者にとって

ショートカットは宝の持ち腐れになります。

ショートカットを覚えたは良いけど、

使いどころがわからないってことに

なってしまいます。

色んな関数や表を作ったり

グラフを作れるようになってから、

ショートカットを徐々に覚えると、

より作業効率の改善を実感できるように

なるでしょう。

都度計算式を変える面倒から解放！自動で可変的に足し算する範囲を変える方法！OFFSET関数の仕事での使い方！

おはようございます。

会社の同僚がエクセルの

OFFSET関数を良く使っていました。

実務でも使う場面を想定した

関数の組み合わせや、

可変的な仕組みの１つも同時に

ご紹介したいと思います。

Excelの学習は自分の個別の悩みを

ドンピシャで解決してくれる

答えが中々ネットにはないですよね。

結局はその調べたことを

ヒントに各引数を自分の思い通りに

可変的にアレンジできるかかが重要であり

そこが難しい所ではないでしょうか？

そのために実務で使う例を示して

少しでも皆さんのやりたいことに

繋がるヒントになればと

思っています。

そしてエクセル学習は

見るより触ってなんぼ

というポリシーのもと

無料でファイル提供します。

drive.google.com

OFFSET関数とは？

ある基準セルを起点に行と列をずらし、

特定のセルを探します。そのセルから

特定の範囲を選択して返します。

範囲が複数セル（複数行または複数列または両方複数）

選択になる場合はSUM関数で

その範囲の合計値を求めること等に使います。

基本

OFFSET関数の構成は5つの引数で

構成されます。

OFFSET（基準、行数、列数、高さ、幅）

引数の中身解説

このようなテーブルがあったとします。

右の集計エリアで

7月の売上を抽出したいとします。

先に答えを書くとこのようになります。

基準

起点となるセル。この場合例えばA2とします。

行数

A2から何行下がりますか？

この場合は１行です。つまりA3

ポイントは基準セルを０とし次のセルから１と

カウントすることです。

列数

今度はセルA3から何列右にずれますか？

この場合4列です。つまりE3になります。

ポイントは同じく起点とするセルを０とし

次のセルから１とカウントすることです。

ここで元の基準セルA2から

移動先のセルの指定が終わりました。

ここから選択したい範囲を指定します。

高さ

今度はE3を起点として

下に何行分選択しますか？

ここでは1行分とします。E3です。

ポイントは起点とするセルを１として

カウントすることです。次のセルは２に

なります。行数や列数指定時と異なります。

幅

今度はE3を起点として

右に何列分選択しますか？

ここでは1列分です。E3です。

ポイントは起点とするセルを１として

カウントすることです。次のセルは２に

なります。行数や列数指定時と異なります。

ご覧の通りこの例なら

これならOFFSET関数なんて不要で

普通にVLOOKUP関数で

取ればいいじゃんとなるわけです。

では例えば7月累計の値も取りたいという場合

どうでしょうか？

VLOOKUPだけではできません。

OFFSET関数の威力が出るのはここからです。

SUM関数との組合せ

下記のように累計額も求めたいとします。

その時には、

OFFSET関数で選択範囲を決めて

その範囲を合計するSUM関数を

くっつけます。

先ほどの７月単月の時と

異なるのは幅の設定が

１ではなく４になっている点です。

幅が4というのは4月から7月の

複数セルを返してねという

指示になります。

こうするとOFFSET関数で

範囲が選択されますので、

その範囲を合計するために

頭にSUM関数を入れます。

しかし、ここで問題です。

7月は4列分と指定しましたが

毎月変わるたびに式を修正するのは

エラーの元です。

なので

この幅の選択を可変にしたいですよね？

例えば6月累計なら3列分

となるようにしたい。

その時には４と手入力している部分を

VLOOKUP関数に置き換えて可変にします。

VLOOKUP関数との組合せで可変

VLOOKUPを使うために

下準備として脇の方に下記テーブルを

用意します。

そのうえで幅の指定を

手入力からVLOOKUP関数に置き換えます。

下記オレンジ色部分です。

変更前

 変更後

こうすることで月を選択すれば

その単月及び累計額を自由自在に

集計できます。例えば6月を選択すると

下記のようになります。

OFFSET関数で幅を指定できる特徴を生かして

わざわざ累計の計算を別でしなくても

累計額を可変的に返してくれるように

なりました。

まとめ

OFFSET関数はある基準セルから下と横に

動かして新たな起点を指定する。

その起点セルを中心に

下と横に範囲を指定し返してくれます。

範囲が複数セルの場合は

そのままだとエラー値が

返ってしまいますので、

SUM関数などを使って

複数セルを返すために

他の関数と一緒に使います。

可変にするために

今回はさらにVLOOKUP関数を

使って月を選択するだけで

その月の単月と累計の集計を自動化

させてみました。

エクセル学習、特に関数の使い方は

数学と少し似ているのではないかと感じます。

数学は猛烈に苦手なのであまり変なことは

言えませんが、

ただ公式やルールを覚えるのではなく、

その公式の本質が何かを理解し、

それを応用して、ロジックを頭で描いて

使いこなす作業というのは

どこか数学と近しいものを感じています。

エクセルの折れ線グラフの空白を飛ばしてつなぐ方法

おはようございます。

日々エクセルを使ってお仕事される方

多いですよね。私もその一人です。

時間がなくてタイトルの答えだけ

知りたい方は目次にやり方を記載しているので

そこを見て下さい。 

さてエクセルは便利ですが、思わぬ所で

躓いて時間を浪費してしまうこと

ありませんか？

はまると1時間とかはまりますよね。 

今日はエクセルの折れ線グラフで

私も直面した”あるある”のお悩みの

1つについて解説します。

折れ線グラフを作る時、

途中のデータがなくて

このように空白になってしまうこと

ありませんか？

急いでいる時にこういうことで

時間とられると、

ぬあー！！（T～T)ってなりますよね。

折れ線グラフの空白部分の線がつながらない

折れ線グラフの空白をつなぐ方法

ステップ1　グラフエリアをクリック

グラフエリアの中でクリックして,

"グラフのデザイン"をクリックします。

クリックすると上のメニュータブに”グラフのデザイン”

というメニューが出るので確認しましょう。

ステップ2　"データの選択"をクリック

グラフのデザインタブの中にある

データの選択をクリックします。

ステップ3　"非表示および空白のセル"をクリック

ダイアログボックスが出てくるので,

その中にある"非表示および空白のセル"を

クリックします。

ステップ4　"データ要素を線で結ぶ"をクリック

更にダイアログボックスがでてくるので、

その中にある”データ要素を線で結ぶ”を

クリックしてからOKをクリックして終わり。

他の設定はそのままで大丈夫です。

 完成

空白セルをつないで

折れ線グラフができました。

VLOOKUP関数の実務的な使い方3、列番号の可変と重複データの識別

VLOOKUP関数の実務的な使い方３です。

以前の記事に記載した通り、

この関数を使う時はよくエラーが

起こってしまう関数です。

エラー対策は下記をご参照下さい。

www.shimesaba-ba.com

またうまく目的のデータを拾えない

こともよくあります。

www.shimesaba-ba.com

それを踏まえて

今回もVLOOOKUPを使いこなすために

セットで知っておくと便利な技を

ご紹介します。

エクセルは触りながら学ぶのが一番なので、

今日も無料サンプルをお配りします。

drive.google.com

是非こちらを触りながら

記事をご覧になってみて下さい。

ファイルを見て触りながら読んでいただけると

理解が深まりますし、仮に理解が全てできていなくても、

触ることで理解ができたりします。

うまくいけばほぼそのまま

あなたの仕事に転用できるかもしれません。

第三引数の列番号指定を可変にする

VLOOKUP関数を使う時に下記のように

式を横にコピーすると列番号が直数字のため

可変になっておらずうまくコピーできないこと

ありますよね？マニュアルでイチイチ

修正するのは今日で卒業できます。

COLUMN関数で該当の列番を可変にする

COLUMN関数とはA列から数えて

指定したのセルが何列目か

を返してくれる関数です。

VLOOKUP関数を使う時は

A列から何列目かではなく、

VLOOKUP関数上の範囲の一番左から数えたいので

COLUMN関数の引き算を使って求めます。

4月は"３"列目

 ポイントは起点側を絶対参照（＄マーク）

で固定することです。

それからCOLUMN関数で使う行は

ラベルの行は消す可能性が低いので

5行目をつかいます。

これを横にコピーすると自動で列番号が

変わります。

5月は"4"列目

6月は"５列目"

このCOLUMN関数を

VLOOKUP関数の第三引数の所

に入れ込むと正しいデータが反映されました。

6月に入っている計算式です。

これによる最大のメリットは

列を削除しても式が壊れないので

メンテが原則不要になることです。

サンプルを使って、

試しにどこか削除してみて下さい。

重複データを識別して検索する

全て異なるデータであれば良いのですが、

1つの列に重複データがあることないでしょうか？

例えば田中さんという苗字が2回でてきているが

名前が違うので区別したい、

あるいは同じ商品名が2回出てきているが、

旧モデルと新モデルが違うので区別したい

なんてことないでしょうか？

こういう時にエクセルは

一番初め（上）にでてくる

データを拾ってきてしまいます。

それぞれ区別して該当データを拾いたい時

はどうしたら良いのでしょうか？

COLUMN関数を使って重複データを区別

これを実現するのにCOUNTIF関数を使います。

この関数はある範囲の中で同じデータが

何回登場したかを表してくれます。

下記のように作業列を追加して

番号を振ります。

具体的に田中さんの１人目を１、

田中さんの5人目を５と表示するにはどうすれば

良いのでしょうか？

ポイントは一番上の名前があるセルを

固定することで式を下にコピーすると

一番上からそのセルまでの中で何回同じ名前が

出たか数えられるようになります。

そうしたら後は

オリジナルの名前の列に今作業列で追加した

番号を追加することで同じ名前でも

区別ができるようになります。

これを実現するのはアンド関数を使います。

とても簡単な関数です。

 "田中１"というセルの式はどうなっているかというと

単純にくっつけたいセルとセルの間に

"&"を入れるだけです。

そうすると5人目の田中さんもきちんと

区別されて該当するデータ（部署名）を

拾ってきてくれるようになりました。

注意事項は２つです。

１つは検索するキーを

元の名前の列ではなく、

番号を追加した各名前が区別できた列（J列）を

キーにします。

2つ目はVLOOKUP関数の列の始まりを

新しい名前の列（J列）から

初めるようにして下さい。

なぜならVLOOKUP関数は

検索するキーに該当する列をデータ範囲の中で

一番左になるように設定しないといけないからです。

さもないとエラーになりますので気を付けてください。

 まとめ

VLOOKUP関数を4月から3月まで横にコピーしても

第三引数の列番号の設定が直打ちの場合、

うまくデータが拾えません。

このように列番号を可変にするにはCOLUMN関数を

使ってみて下さい。

また、データ範囲の中で重複データがあることがあります。

この状態でVLOOKUP関数を使うと一番初めに

該当したデータを拾ってきてしまいます。

重複データをきちんと区別して検索してもらいたい時は、

作業列を追加してCOUNTIF関数で重複データにナンバリングして

区別をすることができます。

実際に手を動かす重要性

学生の時、 教科書を読みこんで理解できたとしても

問題が意外と解けない経験なかったなんて

経験ないでしょうか？

仮に今何となく理解できていても、

1週間後に自分で再現してみて下さい。

意外とできないことって多くないですか？

ヒトの記憶力は自分が思っているより低いのです。

www.shimesaba-ba.com

なので、今回で言えば内容を読み込むよりも

とにかくエクセルを開いて触ってみて下さい。

例えばこの無料の表をコピーして

自分の管理表に転用してみたりするのも良いですね。

そうするとわからないことが出てきたりするので

そこだけ調べてみて下さい。

私はこの繰り返しをすることで

時間が経過しても使える

自分のスキルになってきました。

VLOOKUP関数の実務的な使い方2、文字列の一部だけを抽出した検索の仕方

前回はVLOOKUP関数の

実務的な使い方の基本と

よくあるエラーの原因と対策について

書きました。

www.shimesaba-ba.com

今回は色んな検索の仕方に

ついて書きたいと思います。

というのもVLOOKUP関数で検索する時に、

データベースと検索するキーの

粒度が違うことないでしょうか？ 

そうするとそのままVLOOKP関数を使っても

検索ができないことが

実務でよくあると思います。

今回はこういった検索する

キーとデータベースの粒度の違いを

乗り越えて検索をする方法をお伝えします。

過去の私がそうでしたが、

このような実務で発生しやすい問題と

その対処法がわからないから

有名な関数とはいえ実務でVLOOKUPを

なかなか使えない人が多いのだと思います。

エクセル学習のオススメは解説を読み込むより

実際のファイルを見て自分で再現できるか

やってみることだと考えていますので、

そのファイルを無料で置いておきます。

drive.google.com

左から2文字だけ抽出したい

 既存のデータベースのデータに

余計な情報が含まれており、

あなたが検索したい検索キーが見つからないことないでしょうか？

下記の例では

発注オーダー番号に事業部コードと

勘定コードの

組み合わせでできているデータベースです。

それぞれの事業部の発注金額を

調べているとします。

素直にVLOOKUPを使っても

検索エラーになってしまいます。

そこでデータベースの

発注オーダー番号の中から

事業部コード（頭2文字）だけを

抜き出します。

そこで使われるのがLEFT関数です。

LEFT関数

とてもシンプルです。

LEFT（H4,２）というのは、

セルH4から左2文字を取ってきて

ということだけです。

これで事業部コードが取り出せたのであとは

いつも通りにVLOOKUP関数を作れば

検索結果が返ってきました。

同じ発想で例えば右から〇文字を取って

それで検索するといったこともRIGHT関数を

使って同様にできます。

特定の文字の後から4文字を抽出したい

例えば発注オーダー番号の中身を見ると

事業部コードと勘定コードの組み合わせで

できています。

その時に勘定コードを検索キーにして検索

する場合どうすればよいでしょうか？

データベースから勘定コードを

抜き出したいですよね？

この対処法として

MID関数とFIND関数の組み合わせでできます。

少し難しい式があるので解説します。

MID関数とFIND関数の組合わせ

それぞれの関数について触れてから、

最後に組み合わせをしていきます。

MID関数

あるセルについて

左から何番目から何文字取るかを

指定する関数です。

上記の通り目視でやればMID関数だけで

勘定コードは抜き出せます。

しかし下記のように

常に4番目から取るとは限らないことは

実務でよくあります。

頭にあるコード数がマチマチといったことありませんか？

だからこの例では

ハイフンの次の文字から取り始める

工夫をしないといけません。

そこで使われるのがFIND関数です。

FIND関数

特定の文字列があるセルの中で

左から何番目かを教えてくれます。

この例ですとハイフンは左から

それぞれ3番目と4番目とわかります。

尚、文字列を関数に入れるときは必ず""で囲みましょう。

ここで最後の工夫が必要です。

それぞれ１を加えてあげて下さい。

なぜかわかるでしょうか？

今やりたいことは

MID関数が左から何文字目から

取り始めるかを可変にすることです。

このままだとハイフンから文字を

取り始めてしまいます。

だから最後に1を加えて下さい。

MID関数とFIND関数の組合わせ

こちらMID関数とFIND関数

のまとめになります。

あとは通常通りにVLOOKUP関数を使えば

発注金額を無事に拾うことができるようになりました。

まとめ

VLOOKUP関数は初めから

素直に使える場面は限られています。

データベースと検索したい

キーの粒度が違うといった

実務的な問題が発生します。

そういった時、

例えばご紹介したLEFT関数、

FIND関数やMID関数といった

文字列操作関数を使って

粒度をあわせることができます。

VLOOKUP関数が使えるには、

式の作り方を覚えるだけでなく、

こういった実務でよく起こる問題と

その解決法をセットで知っておくことで

知っている知識から使える知識になります。

VLOOKUP関数の実務的な使い方、よくあるエラー値の原因と対処法とは？

VLOOKUP関数は実務をやる上で

よく使われる関数の1つです。

職種にもよると思いますが、

SUM関数やIF関数の次によく使う

と言っても過言ではないでしょう。

しかし意外と難しい関数でもあると思います。

なぜならエラーが起こりやすいからです。

ネットで調べて自分で式を入力しても

#N/Aになってしまうことないでしょうか？

または、１度目はうまく出来たが、

式をコピーしたり、

別の機会で使ってみたらうまくいかかった

ってことないでしょうか？

この関数の重要な点はVLOOKUP関数

そのものの使い方だけではなく、

この関数を使う時によくあるエラーの原因と

その対処法をセットで覚えることです。

この記事を読むことで

VLOOKUP関数でうまくいかない、

"あるある"の悩みとその解決方法を

がわかるようになります。

下記に無料のサンプルエクセルを

置いたのでダウンロードして下さい。

このサンプルを使って解説をするので、

ダウンロードすることで、

よりわかりやすくなると思いますし、

あなたの業務にも

そのまま流用することが

できると思います。

Excelは解説を読みこんで

理解するよりも、

いかに正解を手元に置いて、

パクって自分で手を動かして使い、

壁に当たったら

調べるという繰り返しが

一番の上達の近道です。

なぜならわからないことや、

悩んでいたことがわかることで、

インパクトがあり

記憶に残りやすいからです。

drive.google.com

VLOOKUP関数とは

検索値したい文字列と

データ検索範囲を指定します。

するとデータ範囲の一番左の列で

検索されます。

そして合致したらその範囲の

左から何番目の列のデータ

を拾ってきてね！という関数です。

ポイントは２つです。

縦（列）で検索するという点と

データ範囲の一番左は検索したい

文字列がある項目にすること

（この例だとオーダー番号）です。

ここで例です。

とあるオーダー番号（A)を

データ範囲から探して合致したら

その1Qの個数を拾いたいとします。

こちらが計算式です。

最後の検索範囲は

通常完全一致の"0"を選択する

で大丈夫です。

計算式の中身の解説 

オーダー番号7E1-6431-211を

グレーのデーター範囲の一番左

から探してきてね。

合致したらデータ範囲の左から8番目の列の

データを拾ってきてね。

答えは900になりました。

よくあるエラーの原因

1.範囲が動いてしまっている

Vlookupの計算式を入れた時、

その式をすぐ横や下にコピーすると

データ範囲や検索値も

1列また1行ずれてしまい

エラーが起こってしまいます。

試しに新しく検索したいオーダー番号を

入力して計算式を上から下にコピーしてみると

#N/Aになってしまいました。最悪なやつです。

絶対参照

そういう時のためにデータ範囲

を絶対参照にしましょう。

絶対参照とは計算式の中で

行または列の前に＄マークをいれることで

固定するという意味です。

こうすることで式を

横や下にコピーした時に

データー範囲が壊れることを

防ぐことができます。

例えば絶対参照を使って

下に式をコピーしてみると

絶対参照を使った計算式

絶対参照の＄を使うことで

検索したいものはK列で固定、

データ範囲はBからI列で固定するという

設定をしました。

なので例えば下に式をコピーしても

データ範囲が変わることなく

正しくデータ検索ができました。

テーブル化する

もしデータが今後増えいくことが確実なら

テーブル化してしまうことをオススメします。

なぜならテーブル化してしまえはデータが追加されても

範囲が可変するので数式のメンテが不要になるからです。

テーブルのやり方

1.データ範囲を選択してから"CNTR+T"を押して下さい。

下記ダイアログボックスが出てきます。

選んだ範囲が問題なければOKを押して下さい。

2.そうしたら、テーブルデザインのメニュータブの中の

テーブル名を適当に変更します。

変更前

変更後

3.VLOOKUP関数の範囲指定の時に

先ほど指定した"price"と打ちます。

残りは通常と同じように入力すれば完成です。

テーブル化することで

通常のセル番地よりも視覚的にみやすく、

かつデータを追加しても範囲が自動で

可変されることが大きなメリットです。

2.データに半角スペースが混ざっている 

パッと見では区別できないエラーがあります。

データに半角スペースが入っている場合です。

例ですが下記の場合、

一見7E1-6343-211というデータで

合致しているにも関わらず

データを拾ってきてくれません。

半角スペースの恐ろしさ

実はグレーのデータ範囲の最後に半角スペースを

いれたため、完全一致にならず、

データを拾ってくれませんでした。

実務において

何らかのエラーでデータに半角スペースが

入ってしまうことはよくあると思います。

しかしそのままにするとVLOOKUPする時、

一見すると同じデータなのに拾ってくれない

といった問題が起こるのです。

目視では到底わからないのが厄介な点ですが、

よく遭遇してきました。

そこで使われるのがTRIM関数です。

TRIM関数

 そんな時はデータのお掃除が必要です。

その時に使うのがTRIM関数です。

TRIM関数はそのセルの余計な空白を

削除してくれる関数です。

計算式

計算式はシンプルです。

掃除したいセルを選ぶだけです。

こうしてTRIM関数を使って

余計な空白をの除くと、

7E1-6343-211の該当する

データ1500を拾ってきて

くれるようになりました。

TRIM関数で余計な空白を削除

エラー値を非表示にする

条件一致しない時に出てしまう

エラー値を非表示にしたい

時はどうすれば良いのでしょうか？

IFERROR関数との組合わせ

 そんな時はIFERROR 関数の中に

Vlookup関数を入れ込んで使います。

IFERROR関数とVLOOKUP関数の組み合わせ

こうすることでエラー値を

非表示にすることができました。

尚計算式の""が空白にする意味になります。

もし０と表示したければ下記のようにします。

もし、エラーの時「要確認」という

文字列を表示させたければ、

つまり、文字列を表示するためには

""で文字を挟みます。

文字がないつまり空白にするなら

""そのままとします。

 まとめ

VLOOKUP関数は、縦列条件で一致した場合、

特定の列から該当するデータを拾ってくるという関数です。

この関数の計算式のポイントは２つです。

横（行）の検索はできず、

縦（列）で検索するという点と

データ範囲の一番左は

検索したい文字列がある項目にする点です。

またこの関数にはよくエラー値が

出てしまいます。

なのでそのエラーの典型例を把握して

事前に対応策を知っていることが

VLOOKUP関数を使いこなすポイントです。

その具体例として絶対参照を使って

範囲を固定すること、

TRIM関数を使って

データの余計な余白を削除する

ことをお伝えしました。

表の見た目でエラー値をそのまま表現させない

やり方としてIFERROR関数との組み合わせも

ご紹介しました。

次回の記事では

VLOOKUP関数の続きを書きたいと思います。

今日は単純に条件一致したものを

拾ってくる純粋なやり方でしたが、

様々な検索方法があるので

よく使う事例をご紹介するつもりです。

例えば左から4文字で検索したり、

複数セルを合算した条件検索をしたりすることもできます。

それでは良い週末を！

SUMPRODUCT関数は仕事でこう使う！縦横複数条件の合計集計に効果抜群

エクセルで縦横の条件合致したものを

合計したい場面ってよくあるのではないでしょうか？

地道に目視で探すのも良いですが、

時間がかかってしまいますよね。

例えばこんな場面です。

下記データベースから

縦が「青森」かつ「野菜」

横が「１Q」

という条件に合致した合計を

求めたいとします。

実績データベース

皆さんならどうやってやりますか？

いくつかやり方があると思いますが

重要なことは目的に応じて手段を

使い分けることです。

 サンプルを無料で置いておきますので

ダウンロードしてみて下さい。

ファイルを見ながら参照して頂けると

よりわかりやすいと思います。 

下記がサンプルのエクセルです。

drive.google.com

PIVOTテーブルの活用

縦が「青森」かつ「野菜」

横が「１Q」という時

いくつなのか求めたい時など

特定の条件を入れて調べたり、

集計されるアウトプットの形に拘りがなければ

PIVOTテーブルが確実です。

理由は条件をプルダウンで選ぶだけで

AND条件やOR条件を確実に作ることが

できるからです。

今回、答えは42個でした。

サンプルをダウンロードして頂いた方は

"Pivot実績集計"タブをご覧ください。

このように数を求めることが

目的ならこのやり方が確実です。

複数条件の集計を可能にするSUMPRODUCT関数

しかしレポートのために

データベースのタブが複数あって、

それらをあわせて一元化して

集計したい場面ありませんか？

例えば下記のように

今年の実績と前年の実績が

別テーブルになっていて、

それらを使って条件に合致したものを

集計したい場面です。

黄色の月をリストから任意の月を

選択したら自動でデータが置換されます。

 4月を選択して見ると、

データが自動で置換されました。

これを一瞬で自動的に作り上げるには

どうしたらできるのでしょうか？

これができるとレポート作成のために

データベースから地道にコピペしたり

目視で確認したりマニュアル作業を

各段に減らすことができます。

SUMPRODUCT関数の実務的な使い方

 その時に使うのがSUMPRODUCT関数です。

この関数使い方はパッと見は少し難しいので、

中身を解説します。

私はSUMPRODUCT関数を

使えるようになって、

半日程かかっていたレポーティングが

30分で間違えなくできるようになりました。

だから今回ご紹介しようと思いました。

集計作業が早くなったことも価値ですが、

分析という付加価値を生む作業に

時間をかけることが

できるようになったこと

が良かった点です。

SUMPRODUCT関数で何をしているのか？

では関数の中身をまずは

大枠から説明していきます。

サンプルをダウンロード頂いた方は

"レポート用"タブをご参照下さい。

例として

前年の沖縄の１Qの販売個数47個

のセルの計算式を解説します。

ポイントは

かつ条件の時は＊（アスタリスク）を

使うこと、

条件を設定し終わったら,(カンマ)を打ち、

その後に合計する範囲を設定する2点です。

しかし、

やってみるとわかると思うのですが、

いざ自分で１から計算式を

入れようとすると

間違える可能性大です。

私はできませんでした。

だからまずは式をパクりましょう。

その上で、自分のファイルにあうように

調整する方が確実です。

コチラがその式です。

コピペしてご自身のファイルに

活用してみて下さい。

=SUMPRODUCT((データベース前年!$B$6:$B$26=レポート用!$A5)*(データベース前年!$F$5:$M$5=レポート用!$A$2),データベース前年!$F$6:$M$26)

サンプルのエクセルを

無料でダウンロードできるので、

そのファイルを加工して

使って頂くのも良いと思います。

サンプルエクセルを見ている方は

わかると思いますが、

縦列だけで複数条件の設定

をすることも可能です。

"東日本"かつ"野菜"といった設定した上で、

さらに横列で"4月"に該当するデータを

合計することもできます。

色んな条件設定が可能

かつ条件の時には

アスタリスク（*）を使いましたが、

他にも"または"条件も設定できます。

その時はプラス(+)を使えばできます。

SUMPRODUCT関数のまとめ

メリット

縦列横行に複数条件を設定して

自在に集計ができます。

しかし数字を探すことだけが目的なら

ピボットテーブルがベターです。

なぜなら直感的でわかりやすいからです。

しかし単にデータを探すだけでなく、

定型フォーマットに複数タブを使って

レポーティングを作成するには

ピボットテーブルでは対応できません。

そこでSUMPRODUCTを使い、

複数タブから条件を設定して

条件に一致した集計作業を一瞬にして

行うことができます。

このように目的に応じて

集計する方法を

選択するのが重要です。

デメリット

式がパッと見複雑になってしまうことです。

なのでエクセルに不慣れな人も更新するファイルの場合は難しいかもしれません。

自分だけあるいは

エクセルに詳しい数人しか

更新しないのであれば

使う価値が高い関数です。

ファイルが重たくなる点も

デメリットになります。

何千何万ものデータがあると

動作が重くなる可能性があります。

そういう場合は自動計算をやめて

手動計算にすることをオススメします。

数式タブの計算方法の設定からできます。

10年以上実務でエクセルを使ってきたので、

今後もこういった小技もご紹介したい

と思います。

エクセルの数表を早く、見やすく作る5つのルール

社会人になると色んな資料を作ります。

そんな中でも数字のある資料は

大抵どんな職種でも必要です。

例えば営業の提案資料には見積もり、

その商品の効果を示すデータ、

開発やエンジニアなら、

開発に必要な機会の発注承認を

取るための資料、など。

私は数字を使った資料を作る機会が多く、

営業や開発が作る数字間連資料

をよくみてきました。

この記事を見ることでエクセルの数表を

早く、見やすく作ることが

できるようになります。

前提

資料は目的によって、言いたいことよって

その見せ方や強調する所は変えるものです。

また何色を基本に使うか等、

会社や部署の好みによる所もあります。

そうは言っても基本的な原則はあるので

そこに絞ってお伝えします。

今回は例としてスーパーの店長に

販売数量を報告すると想定して

資料を作ります。

よくある悪い例

自分だけが見るなら問題ありませんが、

他人に見せるなら、これだと読みにくいと思います。

どこをより良くすべきでしょうか？

・タイトル：何を示した表か不明
・単位：個数なのか金額なのか不明

・色使い：カラフルで目がチカチカする。

・罫線：セルひとつひとつの区切りが明確になる代わりにごちゃついた印象

数表作りの基本ルール

原則は極力シンプルにすることです。

そうすることで相手に見やすくする、

後で加工しやすくなります。

具体的なルールは次の通りです。

1．タイトルと単位を入れる

何を示しているかをまず示しましょう。

自分だけで見るなら良いですが、

他人に見せるなら必ずいれましょう。

2．縦の線は使わない

罫線は極力少なくしましょう。

特に縦の線はなしが基本です。

理由は視線の動きにあります。

ヒトの視線は左から右に動くので

それを適度に補助する横線はある程度必要。

一方で、縦線は目線の動きと反するので不要。

インテンドを揃えることで縦のラインを自然に

みせるだけで十分です。

3.背景色は3色以内

色んな色を使うと見にくいです。

コントラストをつけて見やすく

したり強調する所に背景色を使います。

3色以内で十分です。

更に同じ3色でも同系色でまとめると

より目にやさしくなるでしょう。

4.ゼロは表示する

無駄を省くなら０は表示しないでよいのか？

いえこれはNGです。

ゼロというのは意味があります。

空白だと入れ忘れていると疑われる

のできちんと0を入れましょう。

改善後の例

こちらが改善例の１つです。

最初の表とどちらが見やすいでしょうか？

細かいところは好みがあるとは思いますが、

落ち着いた印象かつ、横に視線を動かしやすい

作りにしたので見やすくなっていると思います。

見やすい表(修正例)

尚、ルールには入れませんでしたが

フォントは日本語の資料ならも

「メイリオ」が見やすいです。