Excel。divide。氏名を苗字と名前で分割したいというリクエスト 関数編

Excel。氏名を苗字と名前で分割したいというリクエスト　関数編

LEFT&RIGHT&LEN&FIND関数

2013年の9月に掲載したことのあるネタなのですが、最近よく、教えてほしいと耳にしますので、
今回改めて、氏名を苗字と名前に分割する方法をご紹介していきます。

前回は、2回に分けて、苗字と名前に分ける方法をそれぞれ紹介しましたが、
今回は1つにまとめてご紹介していきます。

まず、下記の表があります。

B列の氏名をC列に苗字、D列に名前とそれぞれ、分けて表示するようしていきますので、

まずは、C列の苗字からご紹介してきましょう。

なお、ポイントですが、B列の氏名。苗字と名前の間には、
全角のスペースが一文字分入っております。

苗字は、氏名の左側から空白文字までの文字数ということになりますから、
LEFT関数を使用していきます。

それでは、C3をクリックして、LEFT関数ダイアログボックスを表示しましょう。

文字列には、B3

そして、

文字数ですが、全角の空白を見つけるために、FIND関数を使いますので、
文字数のボックスをクリックして、FIND関数ダイアログボックスを表示していきましょう。

検索文字列は、全角の空白を探しますので、”　“
対象は、B3。
開始位置は、最初に登場する、全角の空白を探しますので、1　と入力しましょう。

あとはOKボタンをクリックします。では完成した数式をオートフィルで連続コピーしましょう。

C3の数式は、
=LEFT(B3,FIND("　",B3,1))
です。

しかし、ここでもう、ワンポイント！

正確には、苗字＋全角空白がC列には表示されているのです。
つまり見た目には、苗字だけが抽出されたように見えますが、全角空白も混ざっています。

C列を自動調整してみると、長曾我部の部のあとに、
一文字分のスペースが残ることがわかります。

ですので、
C3の数式を、

=LEFT(B3,FIND("　",B3,1)-1)

とマイナス1を付けてあげるのが、ポイントになります。

データの正確性と汎用性を考えた時には、マイナス1を付けることを忘れないようにしましょう。

続いて、名前ですね。

今度は、全体の文字数から全角空白までの文字数を引いてあげて、
その答えの文字数を右側から抽出するという方法になりますので、

まずは、RIGHT関数を使います。D4をクリックして、
RIGHT関数ダイアログボックスを表示しましょう。

文字列には、B3。
文字数には、全体の文字数を算出するために、LEN関数ダイアログボックスを表示します。

文字列には、B3　と入力します。これで、文字数が全体の文字数になります。

数式バーの作りかけである、数式のRIGHT　という文字をクリックすると、
RIGHT関数ダイアログボックスに戻ります。

文字数には、LEN(B3)と表示されていますが、
全角空白までの文字数を引きますので、-（マイナス）を入力して、

今度は、FIND関数ダイアログボックスを表示しましょう。

FIND関数は先程のLEFT関数の時と変わりませんので、
検索文字列は、”　“　全角の空白を入力します。

対象はB3

開始位置は1と入力して、OKボタンをクリックしましょう。
あとは、数式をコピーしましょう。

これで、名前の列も完成しました。
このようにすると、苗字・名前を分けることが出来ましたね。

＜過去の掲載＞
Excel。氏名から苗字を抽出する方法
http://infoyandssblog.blogspot.jp/2013/09/excel-leftfind.html

Excel。氏名から名前を抽出する方法
http://infoyandssblog.blogspot.jp/2013/09/excelrightlenfind.html

Excel。Vertical_bar_graph 。なんじゃこりゃ！縦棒グラフを作ってみたら横軸が1・2・3ってなっている！

Excel。なんじゃこりゃ！縦棒グラフを作ってみたら横軸が1・2・3ってなっている！

縦棒グラフ

Excelでグラフを作成するのは、とても簡単なのですが、グラフの基になる表によっては、

【なんじゃこりゃ！】

って言いたくなってしまうようなトラブルが発生することがありまして…

グラフを頻繁に作っている人でしたら、また出たな。
で済むのですが、あまりご縁が無い方ですと、ご質問になるものがあります。

その一つに、ちゃんと範囲選択はしたんだけど、
グラフの横軸が1・2・3…ってなってしまうというもの。

上の図のように、B3:D8を範囲選択をして、集合縦棒グラフを作ってみると、あらら！
横軸が、1・2・3…ってなってしまっていますよね。

これでは、困ってしまうので、これをどうやったら、ご希望のグラフになるのかを、
今回はご紹介していきます。

実は、これ、結構質問があるんですよ。

この表自体は、全く問題はありません。
ただ一つ、Excelが混乱してしまったところがあるのです。それは、B列の年。
この年。
2010～2014まで入力されていますが、【数値】になってしまっているのが原因なのです。

見出しに”年”とありますので、2010年と入力しないで、2010としたことにより、
Excelが混乱してしまったのです。

このようなケースになった場合は、デザインタブのデータの選択を使って、
データの範囲を変更していくのが良いと思われます。

では、グラフをアクティブにしておいて、デザインタブの【データの選択】をクリックしましょう。

データソースの選択ダイアログボックスが表示されました。

横（項目）軸ラベルを見てみると、1～5までの数値が入ってしまっていますので、
これを修正していきますので、編集ボタンをクリックしましょう。

軸ラベルダイアログボックスが表示されてきましたので、軸ラベルの範囲を選択します。

B4:B8を範囲選択しましょう。すなわち、2010～2014ですね。

OKボタンをクリックすると、元に戻ります。

次に、凡例項目（系列）にある、年が不要ですので、選択して、削除ボタンをクリックしましょう。

あとは、OKボタンをクリックしましょう。

これで、横軸が年にかわりましたね。

このように、数値で構成された表をグラフにすると、

今回のような現象が発生してしまいますので、
その場合は、デザインタブの【データの選択】で修正するのがいいでしょう。

なお、このようなケースを発生させないようにするためには、
下記のような表を作るのも一つの手です。

先程の表と違うのは、2010年と入力してあることです。こうすることで、文字認識になります。

では、グラフを作ってみましょう。

修正することなく、グラフを作成することができました。

Excel。Extraction。抽出したデータの元データの条件付き書式を使ってセルを塗りつぶしたい

Excel。抽出したデータの元データの条件付き書式を使ってセルを塗りつぶしたい

条件付き書式

大きなデータから、1行データを抽出したいというリクエストで、
VLOOKUP関数を使って抽出する方法やINDEX関数と配列を使って抽出する方法を
ご紹介してきましたが、実はさらに続編がありまして、抽出したデータが、
元データの表のどこにあるのかが、すぐにわかるように、

【その行に色を付けることはできませんかね？】

というもの。

たしかに、元データのどこにあるのかが、色分けされれば、わかりやすくになりますよね。

ということで、
今回は、抽出したデータの元データの行（レコード）のセルを塗りつぶす方法をご紹介してきます。

下記の表があります。

A2にNOを入力すると、下の表から該当するデータを抽出してくるというものでしたね。

ちなみに、B2には、
=VLOOKUP($A2,$A$6:$H$14,COLUMN(),FALSE)

という数式を設定しています。

VLOOKUP関数とCOLUMN関数のネストのテクニックでした。
詳しくは、前回書いておりますので、ご覧になっていただければと思います。

さて、今回やりたいのは、A2にNOを入力したら、
下の表にある該当するデータの行のセルを塗りつぶしたいわけです。

このような時には、条件付き書式の登場となります。

では、見出し行を除いて、データ全体を範囲選択しますので、A6:H14まで範囲選択をしましょう。

ホームタブの条件付き書式をクリックして、【新しいルール】をクリックしましょう。

セルの強調表示ルールにある、文字列では対応しきれないので、
今回は新しいルールで作っていきます。

新しい書式ルールダイアログボックスが表示されてきましたので、
ルールの種類を選択してくださいの【数式を使用して、書式設定するセルを決定】を選択します。

ルールの内容を編集してくださいにある、
次の数式を満たす場合に値を書式設定のボックスに数式を作っていくわけですね。

このボックスには、
=$A$2=$A6

という数式を入力します。A2は固定しておかないといけませんので、絶対参照を設定します。

わかりにくいのは、$A6。

考え方として、範囲選択した左上のセル。
今回はA6が該当します。まずは、このセルと同じかどうかという数式を作ります。

仮に=$A$2=A6だとして、オートフィルで隣のB6に連続コピーしたイメージすると、
数式が、=$A$2=B6となってしまいます。

今回は、列が変わってしまっては都合が悪いので、
列だけを固定しますので、複合参照になりますので、

=$A$2=$A6

という数式を設定します。
あとは、書式ボタンをクリックして、

セルの書式設定ダイアログボックスが表示されたら、塗りつぶしタブから色を設定しましょう。

OKボタンをクリックすると、元のダイアログボックスに戻ります。

新しい書式ルールダイアログボックスに戻りましたら、OKボタンをクリックしましょう。

これで完成ですね。

条件付き書式は、アイディア次第で色々出来ますよ。

Excel。一覧表から行単位で抽出するならVLOOKUP関数とCOLUMN関数もおススメ。

Excel。一覧表から行単位で抽出するならVLOOKUP関数とCOLUMN関数もおススメ。

VLOOKUP関数+COLUMN関数

前回、INDEX関数と配列関数の組み合わせをつかって、VLOOKUP関数ではなくて、
一覧表から行単位で該当するデータを抽出する方法をご紹介しました。

が、実は、後日談がありまして、
A列のNOが通し番号ではなくて、商品コードのような個別番号だったそうでして…

つまり、前回の方法では通し番号を新たに挿入しないと抽出できないわけです。

NOが、A-01と文字列になっています。ではA2をA-01と入力してみると

ありゃま。#VALUE! というエラーになってしまいましたね。

A2が列番号である数値ではなくなってしまったからですね。

このような場合は、VLOOKUP関数を使うほうが便利になります。
しかし、VLOOKUP関数の列番号をどうしたらいいのか？というのが、今回のポイントになります。

では、B2:H9を削除しておきます。

また、A2に入力するデータがアルファベットと数字の混合なので入力しにくいので、
入力規則のリストを設定しておきます。

A2をクリックして、データタブの入力規則をクリックしましょう。

データの入力規則ダイアログボックスが表示されます

設定の入力値の種類を　リスト　にして、

元の値　には、A6:A14を範囲選択します。　=$A$6:$A$14　と入力されました。
そして、OKボタンをクリックしましょう。

NOが選択しやすくなりましたね。

では、本題のVLOOKUP関数を作っていきましょう。

B2をクリックして、VLOOKUP関数のダイアログボックスを表示しましょう。

検索値には、A2を入力しますが、列方向に数式をコピーしますので、
列を固定しておく必要がありますので、$A2 とします。複合参照ですね。

範囲には、$A$6:$H$14　リストですね。これは絶対参照を設定しておきます。

そして、ポイントになる

列番号には、COLUMN関数をネストしていきます。

列番号のボックスをクリックして、
名前ボックスの▼をクリックして、COLUMN関数のダイアログボックスを表示しましょう。

表示されましたら、
VLOOKUP関数のダイアログボックスに戻りますので、
数式バーのVLOOKUPという文字をクリックすると、
VLOOKUP関数のダイアログボックスに戻ります。

列番号には、COLUMN()　と入力されましたね。
今回は2という数字が欲しいわけです。

今、アクティブにしているセルはB列ですから、2が入ります。

もしずれているようでしたら、プラスマイナスしてください。

検索方法には、完全一致ですから、FALSE　と入力します。

あとは、OKボタンをクリックしましょう。

新宿と表示されましたね。では、オートフィルハンドルを使って、数式をコピーしてみましょう。

では、A2の値を変えてみましょう。

変わりましたね。

このような方法もありますので、使いやす法で作ってみてはいかがでしょうか？

Excel。一覧表から行単位で抽出するならVLOOKUP関数よりもINDEX関数がおススメ。

Excel。一覧表から行単位で抽出するならVLOOKUP関数よりもINDEX関数がおススメ。

INDEX関数+配列

ちょっと前ですが、VLOOKUP関数だと、
修正が面倒で大変なんだよねぇ～とお聞きしたことがあって、どんなことなのかと聞いてみたら、
下記のような作業だそうでして、

抽出結果を表示する行があって、下なのか別シートなのかわかりませんが、データがあって、
その中から、NOを入力したら、該当するデータを抽出したいということらしいのです。

今回は4月から9月というサンプルですが、もっと列数が結構あるそうです。

確かにVLOOKUP関数でも結果を求めることはできますが、列数が多いとなると、
確かに大変かもしれませんね。

では、VLOOKUP関数で考えてみましょう。
B2をクリックして、VLOOKUP関数ダイアログボックスを表示しましょう。

検索値は、A2
範囲は、$A$6:$H$14　右方向にオートフィルハンドルを使って数式をコピーしますので、
絶対参照は欠かせませんね。

列番号は、2

検索方法は、0　の完全一致ですね。FALSEでもOKですね。

数式は、

=VLOOKUP(A2,$A$6:$H$14,2,0)

では、OKボタンをクリックしてみると、

NOが2なので、店舗名には渋谷と表示されましたね。

では、この数式を右方向にオートフィルハンドルを使ってコピーしてみると、

当然、#N/Aというエラーが表示されてしまいましたね。
これは、検索値がA2ではなくて、B2・C2とずれてしまったからですね。
ですので、複合参照のA2を$A2にするといいでしょう。

=VLOOKUP($A2,$A$6:$H$14,2,0)

ただ、これでは、全部渋谷になってしまいます。

もう一か所直さないといけません。実はそここそが、面倒といわれたところなのです。
その場所とは、
【列番号】
列番号が2のままなので、それを3・4・5・6…と変更しなければいけません。

列番号を算出させる、COLUMN関数をアレンジして…なんていう方法もあることはありますが、
それも大変です。

そこで、このような列数がたくさんある場合には、
INDEX関数+配列関数というテクニックを知っていると、アッサリ算出することが出来ますので、
紹介してきます。

では、B2:H2の数式を削除します。

B2をクリックして、INDEX関数ダイアログボックスを表示してきますが、
INDEX関数を選択すると、引数の選択ダイアログボックスが表示されますので、

今回は、配列,行番号,列番号　を選択します。
OKボタンをクリックすると、INDEX関数のダイアログボックスが表示されてきます。

配列は、リストの　B6:H14　を入力します。
行番号は、　A2　を入力します。

列番号は、複数列を参照しませんので、0（ゼロ）を入力します。

OKボタンをクリックしましょう。

渋谷と表示されました。
数式は、=INDEX(B6:H14,A2,0)　となっていますが、この数式をコピーするのではありません。

この数式を配列関数にしていきますので、
B2:H2までを範囲選択します。

そうしましたら、そのまま、数式バーにある数式を範囲選択します。

そして、配列関数にしますので、

Ctrl + Shift + Enter　

を押します。

すると、データが抽出されました。
数式は、

{=INDEX(B6:H14,A2,0)}

と変わったことがわかりますね。
では、A2を変えてみましょう。

このように、VLOOKUP関数でもOKですが、場合によっては、
INDEX関数+配列関数というテクニックもありかもしれませんので、
頭の片隅にあると、いざという時に便利ですよ。

ただ、ひとつ注意があります。A2は行番号です。

つまり、コード番号などの文字ではダメで、数値である必要があります。

Excel。Vertical_bar_graph。積み上げ縦棒グラフの合計値を折れ線グラフを使って表示する方法

Excel。積み上げ縦棒グラフの合計値を折れ線グラフを使って表示する方法

【積み上げ縦棒グラフの合計値】

以前、積み上げ縦棒グラフの合計値を表示する方法をご紹介しましたが、
http://infoyandssblog.blogspot.jp/2014/05/excel_14.html

そもそも、なぜか積み上げ縦棒グラフをつくると、合計値を表示させるところがないので、
どうしたらいいのか？ということで、以前書きましたが、

合計値をテキストボックスを使って表示するにはアイテム数が多すぎで、
面倒だし、大変だし、何か楽な方法ってないですか？というご質問がありました。

確かに、4アイテムぐらいならいいですが、多くなると大変ですよね。

そこで、今回は、
折れ線グラフと第2軸を使って積み上げ縦棒グラフの合計値を表示する方法をご紹介します。

今回は第2軸を使いますので、若干複雑ですが、
複合グラフを作ったことがある方でしたら、こちらの方が楽かもしれませんね。

完成はこんな感じです。

このように、アイテム数が多いと、積み上げ縦棒の合計値を表示させるのは大変ですよね。

では、次の表を用意して、作り方を説明していきます。

まずポイントは合計値を算出しておくこと、
そして、範囲選択はその合計値も含めて設定することです。B2:F11までを範囲選択しましたら、

挿入タブの縦棒の中から、2-D縦棒の積み上げ縦棒を選択しましょう。

すると、積み上げ縦棒グラフが挿入されましたね。わかりやすいように大きくしております。

合計値も混ざっていると思う方もいると思いますが、
この合計値を折れ線グラフに変えていきます。

合計を第2軸でまずは表示させていきますので、合計をクリックすると、
合計すべてが選択されます。

選択したら、グラフツールのレイアウトタブのグラフの要素が、
【系列”合計”】になっていることを確認して、その下の選択対象の書式設定をクリックしましょう。

データ系列の書式設定ダイアログボックスが表示されます。

系列のオプションにある、使用する軸を第2軸にして、閉じるボタンをクリックしましょう。

合計が前面に表示され、第2軸が右側に表示されましたので、
この合計を折れ線グラフに変えていきます。

選択されている状態のまま、
グラフツールのデザインタブにあるグラフの種類の変更をクリックします。

グラフの種類の変更ダイアログボックスが表示されますので、
折れ線を選択してOKボタンをクリックします。

処理が面倒になりますので、マーカー付を選択しないほうがいいでしょう。

合計が折れ線グラフになりました。積み上げ棒グラフの数値と合計の数値が同じですので、
接合しています。

あとは、この折れ線グラフを加工していきます。

合計値を表示させますので、折れ線グラフを選択して、グラフツールのレイアウトタブにある、
データラベルの中にある、上を選択しましょう。

グラフに合計値が表示されました。
あとは、削除関係の処理に入ります。
折れ線グラフの実線はいりませんので、
折れ線グラフを選択して、グラフツールの書式タブの図形の枠線をクリックして、

線なし　を選択します。
折れ線グラフの実線が消えました。

続いて、第2軸を選択して、DELキーを使って、削除します。

最後に凡例の合計のみを選択します。凡例を一度クリックして、
再度合計の上でクリックするといいでしょう。選択できましたら、DELキーで削除します。

コレで完成しました。

このように第2軸というテクニックを使うと、テキストボックスの表示を大量につくらなくても、
合計値を表示することもできますので、用途に合わせて使ってみてください。