Excel。hyphen。ハイフンがあったりなかったりで、ないものだけにハイフンをいれたい。

Excel。ハイフンがあったりなかったりで、ないものだけにハイフンをいれたい。

＜LEFT+&+RIGHT関数＞

少し前に、ハイフンが入ってないデータにハイフンを入れる。
または、その逆で、
ハイフンが入っているデータからハイフンを削除するということをご紹介したのですが、
先日、データによって、文字が入っていたり、いなかったりする表があって、どうやったら、
データを綺麗にすることは出来ますかね？というご相談を受けたのですが、

そもそも、データがバラバラということ自体が、NGなわけですが、やはり、現場。

いろんな方がExcelをいろんな風に使っているわけで、
想定していないことも発生しちゃうわけですね。

そこで、
今回はハイフンが入ってないデータにハイフンを入れるように修正する方法をご紹介します。

さて、以前ご紹介したことがある、ハイフンを入れるために使ったREPLACE関数を使えば、
別に問題がないように思うかもしれませんが、そうはいかないのです。

次の表をご覧ください。

A列の元データにすでに、ハイフン(-)が入っているものをREPLACE関数を使ってしまうと、
ハイフンが二重になってしまうのです。

よって、REPLACE関数を使っての変更をすることが出来ないのです。

そこで、このような場合、
データの構成がどのようになっているのか？というパターンを見つけていきましょう。

ハイフン(-)は、何文字目にあるのかを考えてみましょう。

左から4文字目。右から5文字目に必ずハイフンがはいってほしいわけです。

なので、ハイフンが入っているデータでも、入ってないデータでも、かまわずに、
左から3文字にハイフン(-)を加えて、右から4文字を加えたものを作るようにしてあげれば、
いいことがわかります。

それでは、B列のデータを削除して、B2をクリックしましょう。

左から3文字ということなので、LEFT関数ダイアログボックスを表示しましょう。

文字列には、A2。
文字数には、3。
OKボタンをクリックしましょう。

=LEFT(A2,3)

という数式が出来ました。このあとに &”-“&と入力しましょう。

そうしたら、残り右側の文字列を合体させますので、
RIGHT関数ダイアログボックスを表示させましょう。

文字列には、A2。
文字数には、4。
入力したら、OKボタンをクリックしましょう。

これで、完成しましたので、オートフィルで数式をコピーしてみましょう。

なお、数式は、

=LEFT(A2,3)&"-"&RIGHT(A2,4)

本当ならば、データが統一されていてほしいのですが、
そういうことばかりとは限りませんので、
様々なパターンパターンで対応できるようにExcel力を日々アップできるといいですよね。

Excel。ShortCut。Excelのショートカットキーをまとめております。

Excel。Excelのショートカットキーをまとめております。

＜ショートカットキー＞

Excelのショートカットキーは、右側にある、

ショートカットからリンク先に移動して確認できるようにしました。

まだまだ、作成中です。

リンク先は、

YandSシステムズ　の　ShortCutページ　です。

http://yandss.p2.weblife.me/shortcutkey.html

Excel。MAX。結合したセルにオートフィル…アレレ！番号が振れないどうしよう。

Excel。結合したセルにオートフィル…アレレ！番号が振れないどうしよう。

＜MAX関数＞

委託訓練などのExcelの経験値が少ない方が多くいる会場とか、
あるいは、Excelが得意でないご年配の方が多い会社さんにお邪魔したりしていると、
よく、「これが上手くいかないんだよねぇ~」というご質問を受けるのですが、
その最たるものが、

【Excelの表は紙の表ではない】

ということを
あまりご理解いただけてないで、Excelを使っている為に発生してしまうものが多いようです。

例えば、今回ご紹介したいのは下記のような表がありまして。

A列の番号を連番でいれたいそうでして、オートフィルを使って連続コピーしようとすると、
同じ行数を結合したセルは連続コピーが出来るけど、違う行数を結合したセルだと、

このような、同じサイズの結合したセルが必要ですと表示されて、
できないので、自力でコツコツと番号を入力したとのこと。

そこで、何かうまい方法はないかなぁ~というものでした。

このように同じ商品で量が違うだけだったりするので、
NOや商品名を結合してしまったのでしょうけども、
そもそもデータベースの考え方からすると、
このように結合してしまうこと自体がありえないわけです。

同じ商品名であったとしても、量が違う。すなわち別物であるわけですから、
本当ならば、NOはバラバラの固有識別番号があって然るべきなのです。

このあたりの考え方も、紙の資料ではなくて、Excelでの表を作成している意味になるのです。

しかし、現場では、すでにこの表で「稼働」しているわけですから、
結合したセル数が異なる場合の連続番号の設定方法を考えていくことにしましょう。

結合されている行数が違う、
今回のようなケースの場合、オートフィルを使う事が出来ません。

そこで、今回登場するのがMAX関数なのです。

とりあえず、やってみましょう。
まずは、最初の結合されているセルにスタートになる数値を入力します。

今回は1～の連番ですので、1と入力します。

連番を設定したい残りのセルを選択します。

そして、MAX関数の登場なのですが、数式を作成してオートフィルでコピーが出来ませんので、
次の数式を手入力します。

=MAX($A$2:A4)+1

最後。ここがポイントになります。ctrl + Enter で数式を確定します。

ctrl + Enterは、範囲選択したところを一度に同じ文字や数式を入力が出来るテクニックを
応用しています。

これで、結合したセルの個数が違うために、オートフィルで数式はコピーできなくても、
数式をコピーすることが出来ます。

これで完成はしましたが…

最初にも書きましたが、本来の【Excelの表】のルールに基づいていれば、
こんな苦労はしなくていいわけですね。

ただ表を作るのではなくて、少しExcelの表ということを意識するようになると、Excel。

もっと楽に、便利になりますので、機会があれば、
Excelをどんどん触ってみるのはどうでしょうか。

今週のFacebookページの投稿 2016/7/11-2016/7/24

今週のFacebookページの投稿　2016/7/11-2016/7/24

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

7月11日
Excel。シート名には、?（クエスション）は使えませんね。

7月12日
Excel。シート名には、*（アスタリスク）は使えませんね。

7月13日
Excel。シート名には、[ ]（大カッコ）は使えませんね。

7月14日
Excel。ショートカット。Ctrlキー+F6キーで、ウインドウの切り替えをすることが出来ますね。

7月15日
Excel。ショートカット。Ctrlキー+Kキーで、
パイパーリンクの編集ダイアログボックスを表示することが出来ますね。

7月16日
Excel。ショートカット。Ctrlキー+F3キーで、
名前の管理ダイアログボックスを表示することができますね。

7月17日
Excel。ショートカット。F5キーで、
ジャンプ ダイアログボックスを表示することが出来ますね。

7月18日
Excel。グラフ。Altキーを押しながら、大きさを変えると、
セルの枠線に合わせてサイズを変更することが出来ますね。

7月19日
Excel。スパークライン。スパークラインはExcel2010で登場した新機能ですね。

7月20日
Excel。スパークライン。
折れ線は、時間の経過によるデータの流れを表す場合に使えますね。

7月21日
Excel。スパークライン。縦棒は、データの大小関係を比べるのにたけていますね。

7月22日
Excel。スパークライン。
勝敗は、正と負を基準としたデータの勝敗を表したものですね。

7月23日
Excel。シート名は、シート見出しをダブルクリックすると変更できますね。

7月24日
Excel。シート名は、シート見出しの上で右クリックして、
ショートカットメニューから、名前の変更で変更できますね。

Excelテクニック and  MS-Office recommended by PC training

https://www.facebook.com/exceltechniqueandmsoffice/

Excel。Graph 。グラフ作成トラブル！セルの結合が命取り？！陥る罠を回避しよう

Excel。グラフ作成トラブル！セルの結合が命取り？！陥る罠を回避しよう

＜グラフ＞

新人研修シーズンもひと段落。

Excel初めてとか、あまり経験がないという人たちも、現場の荒波にもまれながら、
Excelのスキルアップを始めたものと思いますが、Excel。簡単なのですが、
少し、クセがありまして、よくExcelの経験値が少ない方から、
うまく出来ないのですが…という話を耳にします。

そこで、今回は、その中から、グラフに関するトラブル回避方法を書いてみようかと。

まず、こういうグラフになってしまって…というものの代表格からご紹介しましょう。

横軸が、1.2.3とかの数値になってしまっていますよね。あるいは、

凡例が、系列1とかになってしまった場合。

これは、何が原因かというと、範囲選択が失敗したのが原因なのですね。

ご覧のように、見出し行が含まれていませんよね。これが原因なのですね。

Excelグラフの経験値が少ない方は、
範囲選択をし直して改めて作り直したほうが早いと思います。

範囲選択は、B2:E5にして作成し直してみましょう。

このように、グラフを作成する最大のポイントは、範囲選択。
これを間違えると、作成したくても、うまく作成することが出来ません。

あわてずに、範囲選択を確認するようにしましょう。

続いてもグラフ作成時の代表的なトラブル。

今度は範囲選択自体には問題がないのですが、
グラフがオカシイ！表示されないというケースがあります。

次の表があります。この表を元にして、先ほどと同じ、第一四半期売上グラフと、
合計の円グラフを作ってみることにしましょう。

範囲選択は、A9:E12で、集合縦棒を作成してみると…

あれれ、4月の棒グラフの前になぞの空白が？

範囲選択は、A9:A12とF9:F12で2-D円グラフを作成してみると…

あれれ！円グラフが表示されない！！

範囲選択は問題がないように思えるのですが…

この原因の犯人。実は【セルの結合】が犯人なのです。

表を改めてみて見ましょう。

何気なく、セルの結合を行っている表を見かけますが、結合してしまうと、
グラフがうまく作成できないのです。人間の見た目には問題ない範囲選択ですが、
Excelからだと、先ほどの円グラフの範囲選択は、
A9:A12とB9:B12とF9:F12を範囲選択していることになるのです。

要するに、A9:B12で円グラフを作成してしまったので、表示されなかったわけです。

ですので、セルの結合を解除してみると、普通に作成することが出来ます。

そして最後は、バージョン違いでの困った問題の解決方法をご紹介しましょう。

何でもない、普通の集合縦棒です。

これは、Excel2013で作成した集合縦棒なのですが、
次のグラフは、Excel2010で作成した集合縦棒グラフです。

凡例の場所は、ともかく、よく耳にするのが、棒グラフ同士の”隙間”のあるなしなのです。

つまり、昨年まで棒グラフ同士の隙間がないグラフだった。

しかしExcel2013を導入したら、隙間があって、その隙間をなくすことはできませんか？という、
ご質問があるわけなんですね。

その解決方法をご紹介しましょう。

棒グラフをダブルクリック。
あるいは、棒グラフをクリックして、書式タブの選択対象の書式設定をクリックします。

右側に作業ウインドウが表示されます。

データ系列の書式設定の系列オプションにある、【系列の重なり】を0%に変更しましょう。

これで問題解決ですね。

今回ご紹介した3つは、どれもよく耳にするグラフ作成に関するトラブルですが、
どこかしらに原因があるはずなので、あわてずに確認して作成してみましょう。

Excel。TIME。深夜料金発生を知りたいので22時以降にマークをつけようとしたら…

Excel。深夜料金発生を知りたいので22時以降にマークをつけようとしたら…

＜IF関数+条件＞

「IF関数を使っているのですが、ちゃんと動いてくれませんので、先生！助けて～」
ということで、
会社に赴いて早速、そのファイルを見せていただきまして、
なるほど…こりゃ、確かにクセがあるのでしょうがないなぁ～ということがありましたので、
それを今回ご紹介していきます。

そもそも、どんなものだったのか？というと、
なんでも、深夜料金発生のようなファイルだそうでして、
終了時間が22時以降かどうかを判断したかったというもの。

下記のようなファイルを作りたいわけです。

別に終了時間が22:00以上だったら○と表示するようにIF関数を作ればいいだけじゃないの？
と思うのが普通なのですが、
実は、論理式。つまり質問文の条件が厄介なんですね。

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

論理式に、C3>=22:00　と入力すると、正しくありません　と表示されていますよね。

OKボタンをクリックしても、当然、正しくありませんとメッセージが出ます。

そうか！
22:00の前後に”（ダブルコーテーション）を付けてないから正しくないといってくるのだと思って、
”（ダブルコーテーション）を付けてみましょう。

今度は、数式としては大丈夫のようですからOKボタンをクリックしましょう。

あれれ！完成と思ったら、なんと、D3に○が表示されていません！

オートフィルで数式をコピーしてみても、表示されません。

D3のセルをアクティブにして、念のため数式バーで数式を確認しても、
問題はないように思えます。

なのに、結果がおかしい。

そう、これが、今回のトラップだったのです。

これで、助けて～メールが来たわけですね。

実は、”（ダブルコーテーション）で時間を囲ってしまうと、

【時刻】だったのが、【文字】になってしまうのです。

つまり、C3は時刻なので、シリアル値としては、かなり小さくなっているわけです。

ですので、その時刻である、
シリアル値と文字である、22:00を比べても文字である22:00を越えることはないわけです。

ですので、空白が算出されてしまったわけなのです。

では、どうやったら、いいのでしょうか？

それは、
”（ダブルコーテーション）で囲った時刻に*1を付けてあげるだけで解決してしまいます。

とりあえず、D3の数式を次のように修正しましょう。

=IF(C3>="22:00"*1,"○","")

そして、オートフィルで数式をコピーしてみましょう。

なんと、ちゃんと算出することが出来ました！

なぜ、*1をしたら算出できたのか？というと、

“（ダブルコーテーション）で囲うと【文字】にはなるのですが、
これに*1をつけてあげると、シリアル値に変換してくれるのです。

なので、シリアル値同士の比較となったことで、算出することが出来たわけです。

ただし、D1に22:00と用意しておいて、そのセルを使うと話は別になります。

D3の数式を、

=IF(C3>=$D$1,"○","")

と、修正しても、きちんと算出することが出来ましたね。

このようにセルを使用した場合と、
直接数式を作る場合で条件が変わってきますので、注意が必要ですね。

困ったときは、*1を忘れずに

今週のFacebookページの投稿 2016/7/04-2016/7/10

今週のFacebookページの投稿　2016/7/04-2016/7/10

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

7月04日
Excel。アクティブセルの太枠の左部線上で、
ダブルクリックをするとデータ範囲の左端列に移動できますね。

7月05日
Excel。アクティブセルの太枠の右部線上で、
ダブルクリックをするとデータ範囲の右端列に移動できますね。

7月06日
Excel。名前ボックスにセル番地を入力して、
Enterキーを押すと、そのセル番地にジャンプできますね。

7月07日
Excel。シート名は31文字までOKです。全角半角は関係ありませんね。

7月08日
Excel。シート名には、:（コロン）は使えませんね。

7月09日
Excel。シート名には、￥（円マーク）は使えませんね。

7月10日
Excel。シート名には、/（スラッシュ）は使えませんね。
ということは、日付。2013/1/1はNGなんですね。

Excelテクニック and  MS-Office recommended by PC training

https://www.facebook.com/exceltechniqueandmsoffice/

Excel。Graph。合格ラインがわかるように棒グラフと積み上げ面グラフで表現してみる

Excel。合格ラインがわかるように棒グラフと積み上げ面グラフで表現してみる

＜棒グラフ+積み上げ面グラフ＞

多くの方に見ていただいている、棒グラフ＋折れ線グラフを使った、
2015年3月5日に投稿した

【Excel。Graph。縦棒グラフに自動的に平均値の線を引くにはどうしたらいい？】

https://infoyandssblog.blogspot.jp/2015/03/excelgraph.html
このテクニックを、アチコチの研修でご紹介しているのですが、
そこでご質問があって、

「線ではなくて、
プロットエリアを塗れるともっとわかりやすくなるのですが、条件付き書式とかを使って、
そんなこと出来ませんかね？」

というご質問をいただきました。

なるほど、確かに現場ならではのご質問ですよね。

ということで、それを受けまして、今回は、次のようなグラフの作り方をご紹介しちゃいます。

合格ラインを設定して合格ラインを越えたところが緑色で、
それ以下が赤色で塗りつぶしてある、グラフですね。

これはプロットエリアを塗りつぶしているわけではなくて、
棒グラフ＋積み上げ面グラフで作成してあるのです。

では、早速作り方をご紹介していきましょう。

次の表を用意します。

C列は棒グラフになるところ。
D列は赤色の面グラフに当たるデータです。今回は75点で設定してあります。
E列には、=100-D3という数式が設定してあります。
今回は、100点がマックスなので、そこから合格ラインの75を引いた値が、
緑色の面グラフになるわけですね。

まず始めは、B2:E8を範囲選択して、集合縦棒グラフを挿入しましょう。

このようなグラフが作成されてきます。

赤色の棒グラフをクリックして、第2軸での表示に変更します。

第2縦軸が表示され、赤色の棒グラフが前に出てきましたので、
これを積み上げ面グラフに変更します。

デザインタブのグラフの種類の変更ボタンをクリックして、グラフを変更しましょう。

続いて、緑色の棒グラフも同じように第2軸での表示に変更して、
グラフを積み上げ面グラフに変更しましょう。

後は、細かいアレンジに入ります。第1縦軸・第2縦軸ともメモリが合致していませんので、
両方とも、最大値を100に変更します。

軸の書式設定ダイアログボックスを表示しましょう。

最大値を固定にして100にします。これを両軸とも設定しましょう。

凡例の塗りつぶしを削除して、あとは、面グラフの両側が引き延ばされていないので、
引き延ばす工程に入りましょう。

まずは、第2横軸をラベルなしで表示しましょう。そして、これが一番のポイントですね。

第2軸には、縦軸だけではなくて、横軸というのもあるのです。

レイアウトタブの軸にある、第2横軸から、ラベルなしで軸を表示をクリックします。
すると、面グラフがおかしな表示になります。

これを直すのには、第2縦軸の軸の書式設定ダイアログボックスを表示しましょう。

横軸の交点を、自動にチェックをつければ、元に戻ります。

そして、第2横軸の軸の書式設定ダイアログボックスを表示しましょう。

第2縦軸を非表示にして、完成しましたね。

このように、棒グラフと積み上げ面グラフとの合わせ技など、
第2軸をうまく使えば、様々な表現をすることが出来ますので、
アレコレ考えてみて使っていきましょう！

Excel。Rankings。成績TOPをSとして次をAとした評価表を作成したい

Excel。成績TOPをSとして次をAとした評価表を作成したい

＜RANK.EQ関数とCHOOSE関数＞

アルファベットの評価表を作りたいというお話を受けまして、
なんでも、一番いい成績をスペシャルのSとして、次の成績がAとしていきたい。
そして、同点ならば、同じアルファベットのランキング。というような評価表。
順位表を作成したいというものでして。

なかなか、難題ですよね。

イメージがし難いと思いますので、下記のような表を作りたいわけです。

さて、どうしたらいいでしょうかね？

IF関数でしょうか？一番大きい数値ならSとかですかね？
けど、一番大きな数値はSとは出来ても、2番目。3番目はどうしましょうか？

そこで、このような場合、つまり多分岐するような場合には、
IF関数では太刀打ちが難しいので、CHOOSE関数を使ってあげると、いいように考えられます。

では、CHOOSE関数を使うとして考えた場合ですが、
CHOOSE関数の特性を知っておく必要がありますね。

CHOOSE関数は、1だったら、○○。2だったら、○○。
というように1・2・3という数値を用意してあげる必要があります。

では、どのようにしたら、1とか2という数値を用意できるのでしょうか？

成績が1位2位という順位を求めれば、１とか2とかという数値を用意できそうですね。

となると、RANK.EQ関数が使えそうですね。

そして、このような場合、一つの計算式にすると、煩雑になったり、わかりにくくなってしまうので、
作業列を作って、一度順位をRANK.EQ関数で算出させて、
それから、CHOOSE関数を使って、
アルファベットのランキングに変化させるという2段階で作っていきましょう。

F3をクリックして、RANK.EQ関数ダイアログボックスを表示しましょう。

数値は、C3
参照は、$C$3:$C$12。絶対参照を忘れないように設定しましょう。
順序は、0
を入力してOKボタンをクリックしましょう。

順位が算出されますので、オートフィルで数式をコピーしましょう。

F3の数式は、

=RANK.EQ(C3,$C$3:$C$12,0)

となっています。

順位がまずは算出できましたね。4位が2個ありますね。その代り5位はありません。

さていよいよ、本題のABC順位を求めていきましょう。

D3にCHOOSE関数ダイアログボックスを表示しましょう。

インデックスには、先程順位を算出した、F3。

値1から順々に、S・A・Bと入力していきます。今回は、Jまでになります。

あとは、OKボタンをクリックしましょう。そして、オートフィルで数式をコピーしていきます。
数式は、

=CHOOSE(F3,"S","A","B","C","D","E","F","G","H","I","J")

となっています。

これで、完成しましたね。

このようにすると、スペシャルのSから始めることができますよ。

なお、どうしても、作業列を作りたくない場合は、このような数式になりますね。

=CHOOSE(RANK.EQ(C3,$C$3:$C$12,0),"S","A","B","C","D","E","F","G","H","I","J")

これでもいいけど、一目でわかりにくいですよね。
現場では作業効率も考えないといけませんので、ケースバイケースで作成してみてください。