Excel。IFERROR関数じゃダメな時ってどんな時？後編

Excel。IFERROR関数じゃダメな時ってどんな時？後編

IFERROR関数とIF関数

前回は、IFERROR関数を中心に、使い方のご紹介をしていきました。
それによって、エラー表示を回避して、空白に表示することができるようになりました。
仕事でつかえるExcel講座でも、お話ししますが、
今回は、IFERROR関数では都合が悪いケースを、ご紹介していきます。

要するに、IFERROR関数では対応できない時は、
IF関数のネストを使って対応する方法をご紹介していきます。

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

これからF21にSUM関数を設定して小計を算出してみたいと思います。
では、F21をクリックして、オートSUMボタンをクリックしてSUM関数の数式を作っていきましょう。

計算式は、=SUM(F13:F20)

問題なく計算されましたね。問題になるのはココからです。
B列の商品番号とE列の数量を全部消去してみましょう。要するに、空の状態にするわけです。
さて、どうなったのかというと、当然B列とE列は空欄になったのと、
商品名や単価、そして金額はエラーが出ないで、空欄のままです。

しかし、F21の小計には0（ゼロ）と表示されていますね。ついでに、F23の合計金額も。
このままでもいいかもしれませんが、用紙を空白にしたいはずなのに、
小計には0（ゼロ）と表示されているのは、ちょっとカッコ悪いですね。

では、ここで、IFERROR関数を使えば、OKなのでしょうか？
F21をクリックして、数式をいったん削除しておきます。

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

値には、先程の計算式を作っていきます。SUM(F13:F20)ですね。
名前ボックスの▼をクリックして、

その中から、SUM関数を選びます。SUM関数のダイアログボックスが表示されますね。

数値1には、F13:F20を範囲選択します。
そして、ここで、OKボタンを押してはいけませんね。
まだ、IFERROR関数を作っている最中でした。よくやってしまうのですが、
関数のネストの時には、上の関数が作成途中の場合戻る必要が生じます。

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

エラーの場合の値には、0（ゼロ）を表示させたくないので、空白を意味する、
””を入力してOKボタンをクリックします。

さて、どうなったでしょうか？

結局、0（ゼロ）が消せませんでした。
要するに、そもそも、F21は、エラーではないからです。

このような時には、IFERROR関数では対応できないわけです。
エラーじゃないですからね。
となると、この0（ゼロ）を表示しないようにするには、
IF関数のネストでないと0（ゼロ）を表示しないように出来ないわけです。

IF関数の空白にする方程式

ではありませんが、ご存知の方も多いかと思いますが、やってみましょう。
F21を削除して、IF関数のダイアログボックスを表示しましょう。
まず、論理式ですが、B13の商品番号が入力されているか、
いないかを判断に浸かってみようと思いますので、

論理式は、B13=""
真の場合は、””
偽の場合は、SUM(F13:F20)

これで、OKボタンをクリックすると、

小計のF21は、空白になりましたね。
このように、IFERROR関数は、あくまでもエラーが発生した時はどうするの？
という時に使いますので、注意が必要です。

それに、IFERROR関数は、Excel2007以降で登場した関数ですので、
昔のExcelにはありませんので、IF関数での処理方法を知っているのは、
決して無駄ではありませんね。

Excel。IFERROR関数じゃダメな時ってどんな時？前編

Excel。IFERROR関数じゃダメな時ってどんな時？前編

IFERROR関数とIF関数

Excelの講座で、IF関数を使って、エラーを表示させない方法を紹介した後に、
テキストによっては、IFERROR関数をご紹介する流れになっていたりします。
仕事でつかえるExcel講座などの実践的な講座でも、初心者向けの講座であっても、
このIFERROR関数をご紹介していくのですが、このIFERROR関数に関して、
あるご質問が出てくるのです。

それは、
IFERROR関数で全部OKじゃないの？IF関数（のネスト）を使う必要はないのでは？
とか、
IF関数（のネスト）を使った方がいいのか？IFERROR関数を使った方がいいのかが、
わかりません。

という質問なのです。

まずIFERROR関数についでですが、この関数は、結果がエラーだったら、
どう処理をしますか？という関数なのです。

下記の表をご覧ください。

F13に、単価×数量の数式を入れてみます。=D13*E13ですね。
そして、オートフィルハンドルを使って、F20まで数式をコピーしてみましょう。

すると、F18以降で、エラーが表示されましたね。この#VALUEは、数値がはいっていないよ。
というエラーです。

空欄だったので、エラーが発生した訳です。

今回の様な請求書ですと、案件によっては、１件だけだったり、全部うまっていたりと、
様々な案件数になる訳でして、その都度、数式をコピーするのでは、間違いの元。
かといって、このエラーが表示されたまま、印刷してお客様に渡すわけにも行きませんよね。

そこで、どうやってエラーを表示させないようにすればいいかというと、
まず、IF関数を使ってみましょう。
算出しましたF13:F20までのデータを削除しておきます。

F13をクリックして、IF関数のダイアログボックスを表示しましょう。
もし数量が空白だったら、空白。
そうでなかったら、単価×数量というIF関数を作っていきましょう。

論理式は、E13=””
真の場合は、””
偽の場合は、D13*E13
と入力をして、OKボタンをクリックして、オートフィルハンドルを使って、
F20までコピーしてみます。

今回はエラーが表示されませんでした。

今度は、この計算式をまた削除して、今度は、IFERROR関数を使用してみましょう。
F13をクリックして、IFERROR関数のダイアログボックスを表示しましょう。

値には、単価×数量の計算式、すなわち、D13*E13を入力します。
エラーの場合の値には、エラーが出た時にどうしますか？ということですから、
空白にしたいので、””を入力します。

あとは、OKボタンをクリックして、オートフィルハンドルでF20までコピーします。
こちらでもエラーが表示されませんでした。

基本的に、IF関数でもIFERROR関数でもエラーを表示させないようできましたが、
IF関数を使わなくてもいいのでは？と思われる方も多いので、

最初に書きましたように、IF関数とIFERROR関数の違いに関して、ご質問が多い訳です。

では、次の場所でIF関数とIFERROR関数を比べてみましょう。

Excel。グラフの中に吹き出しを描くと取り出せない。

Excel。グラフの中に吹き出しを描くと取り出せない。

グラフと図形

職業訓練などの初心者さん対応の講座も多くやっておりますと、些細なことでも、
当たり前ですが、不安に思う事が出てきたりして、よく質問を頂くのですが、
先日も聞かれましたので、今回はその質問を掲載しようと思います。

それは、なんなのかというと、グラフの中に、吹き出しとかの図形を描くと、
グラフから描いた図形を取り出せないということです。

グラフは図形が集まったものというか、
グラフエリアがWordでいうところの「描画キャンパス」になっているわけなので、
グラフの中に図形を描いてしまうと、グラフの外に移動することが出来なくなっております。

下記の表を元に、集合縦棒グラフを作成しております。
この一番売れているところをアピールしたいので、吹き出しで、好成績とか書きます。

では、まず、グラフをクリックして、挿入タブの図形をクリックして、
好きな図形を選んで描いてみましょう。今回は、角丸四角形吹き出しを選んでおります。

描き始めをグラフ内にしておきます。大きさは適度な大きさとします。

角丸四角形吹き出しを描く事が出来ましたね。ついでなので、
好成績という文字も入力してみましょう。

では、この吹き出しを、グラフの外に移動したいと思いますので、
図形の外周をクリックして実践になったら、ドラッグして、グラフの外に移動してみましょう。

出来ませんよね。
このことが、今回ありました、質問なのです。確かに、図形がグラフの外に出せませんね。
では、今度は、グラフの外で、図形を描いて、グラフの中に移動してみましょう。

別の図形を描いてもいいのですが、その図形だから…となってもマズいので、今回も同じ、
角丸四角形吹き出しを描いてみます。大きさは、お任せします。

なお、角丸四角形吹き出しの黄色のマーカーを動かすと、
吹き出しの”口”を動かすことが出来ます。

意外とご存じでない方がいますので、黄色のマーカーがある図形の時は、
動かしてみるといいでしょう。

では、この描いた、角丸四角形吹き出しをグラフの中に移動してみましょう。

グラフの中に角丸四角形吹き出しが移動できましたね。そして今度は、
この移動した角丸四角形吹き出しをグラフエリアの外に移動してみましょう。

先程は、出来ませんでしたが、今回は、どうでしょうか？

今回は、グラフエリアの外に角丸四角形吹き出しが移動できましたね。

このように、同じ角丸四角形吹き出し。図形であっても、どこで描いたかによって、
このような差があるわけです。

別にどうでもいいだろう。と思う人もいるかもしれませんが、このような差が、
Excel初心者の方から見ると、不思議というか、どうして？って思われるので、
よく質問があるわけです。

このような、差を知らないと、初心者の方を講義するときに、説明することが出来ませんので、
このような違いも知っておいて損はありませんね。

Excel。東京神奈川千葉埼玉の順で並び替えをするには？ユーザー設定リスト

Excel。東京神奈川千葉埼玉の順で並び替えをするには？

ユーザー設定リスト

前回は、ユーザー設定リストに登録すると、
オートフィルハンドルを使っての連続データでコピーが出来て便利ですよね。
ということをご紹介しましたが、今回は、そのユーザー設定リストを設定しておくと、
自分の思いのままに並び替えができるのです。

それを、今回はご紹介していきたいと思います。

このテクニックは、仕事で使えるExcel講座や、企業研修などでもご紹介をしております。

さて、下記のようなデータがあります。

これを、東京・神奈川・千葉・埼玉の順番で並び替えをしたいとします。
それでは、やってみましょう。B2の都道府県をクリックして、
データタブの並び替えとフィルターにある、昇順のボタンをクリックしてみましょう。

すると、

神奈川・埼玉・千葉・東京と並びましたね。
これは、これらの文字情報を元に並び替えを行ったわけです。
かながわ・さいたま・ちば・とうきょう。

か→さ→ち→と　という情報から並び替えを行ったのです。
これでは、今回の東京・神奈川・千葉・埼玉の順番で並び替えを行う事が出来ないわけです。

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

そこで、ユーザー設定リストが登場するわけです。
まずは、ユーザー設定リストに、東京・神奈川・千葉・埼玉と設定していきましょう。
前回は、リストを使って設定しましたので、今回は入力していくやり方を紹介していきます。
ファイルタブのオプションをクリックします。

詳細設定をクリックして、ユーザー設定リストの編集ボタンをクリックしましょう。

ユーザー設定リストダイアログボックスが表示されたら、
リストの項目に、東京・神奈川・千葉・埼玉と、入力して、追加ボタンをクリックしましょう。

ユーザー設定リストに登録されましたので、OKボタンをクリックしましょう。
また、Excelのオプションダイアログボックスに戻りますので、ここもOKボタンをクリックしましょう。
これで準備は、完了しましたので、いよいよ、

東京・神奈川・千葉・埼玉と並び替えをやってみましょう。

B2をクリックして、データタブの並び替えとフィルターの並び替えボタンをクリックします。

そうすると、並び替えダイアログボックスが表示されてきます。

列は、都道府県。並び替えのキーは、値のままでOKですね。
そして、順序はユーザー設定リストを選びます。
ユーザー設定リストダイアログボックスが表示されてきますので、ユーザー設定リストから、

東京,神奈川,千葉,埼玉を選んで、

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

並び替えダイアログボックスに戻ってきます、順序には、
東京,神奈川,千葉,埼玉と表示されているのが確認できたら、OKボタンをクリックしましょう。

これで、希望通りに、東京・神奈川…埼玉と並びましたね。
ちょっと知っているだけで、アレコレ使えるテクニックですので、覚えておくといいですね。

Excel。ユーザー設定リストを使って、連続データを入力してみる

Excel。ユーザー設定リストを使って、
連続データを入力してみる

ユーザー設定リスト＋オートフィル

Excelの企業研修や、仕事とでつかえるExcel講座をはじめとして、
講座の序盤にオートフィルハンドルを使って連続コピーを紹介すると、
【おおっ！！】と歓声が上がることがあります。

確かに、日曜日と入力して、[＋]のマウスカーソルで、ドラッグすると、あら不思議、
データが連続されて、つまり、月曜日,火曜日,水曜日…と表示されると、

そりゃ～Excel初心者さんなら、びっくりするわけですし、
当然、業務の速度も改善されるわけですから、歓声もあがるのもうなずけます。

ただ、この連続コピーは登録されているので、表示されるわけです。
つまり、登録されていない、星座では、このオートフィルハンドルをつかっての、連続コピーでは、
牡羊座…とできないわけです。

また、次のような表を作成するに当たり、いつも店舗名が登録された順、あるいは、
47都道府県のような北海道から沖縄までの場合、

通常では、頑張って作るたびに入力するしか方法が無いわけです。

そこで、今回ご紹介したのは、ユーザー設定リスト。
このユーザー設定リストに登録すると、色々と便利な機能のアシストになることがありますので、
まずは、ユーザー設定リストへの登録方法をご紹介します。

今回は、このB4～B8まで入力されている新宿本店から高田馬場店をこの順番で、
表示されるようにしたいとします。

では、ファイルタブのオプションをクリックして、
Excelのオプションダイアログボックスを表示しましょう。

Excelのオプションダイアログボックスの詳細設定をクリックして、下の方までスクロールすると、
ユーザー設定リストの編集ボタンが見えてきますので、クリックしましょう。

ユーザー設定リストダイアログボックスが表示されてきますね。

今回は、幸いにも、B4:B9にリストのデータとなるデータがありますので、これが使えますので、
手で入力する必要はありませんので、インポートボタンの左側のボックスをクリックして、
B4:B9を範囲選択しましょう。

$B$4:$B$9と入力されたのを確認して、インポートボタンをクリックしましょう。

ユーザー設定リストに、範囲選択したデータが入力されたのが確認できますね。
あとは、OKボタンをクリックしましょう。
そして、Excelのオプションダイアログボックスに戻ってきますので、
ここもOKボタンをクリックしましょう。

さて、連続コピーをしてリストを作れるのか確認してみましょう。

B11に、新宿本店と入力しましょう。

そして、オートフィルタハンドル（＋）をドラッグしてみましょう。
なんと、新宿本店～高田馬場店までが入力されましたね。
このように、いつも入力するような、店舗名とか部署名とか従業員名などなど、
登録しておきますと、簡単に入力することが出来ます。

そして、ユーザー設定リストに登録しておきますと、便利な機能がありますので、
それは、次回ご紹介したいと思います。

Excel。数字をどうしても全角で表示したい場合の対処方法

Excel。数字をどうしても全角で表示したい場合の対処方法

表示形式と入力規則

先日の企業研修で、数字を全角で入力したいところがあるのですが、
どうしても、半角になっちゃいます。とご質問がありました。Excelは計算ソフトということもあって、
数字は数値として扱うため、全角で入力しても、半角に代わってしまいます。

Excelでは、入力した数字は数値型として自動的に変わってしまうようになっているようです。
とはいえ、仕事上では、それでは困るという事がありますよね。

ということで、今回は、全角でいれた数字が半角になった後、
その半角を全角にするための方法をご紹介します。

まずは、表示形式を使って、半角の数値を全角の数値にする方法をご紹介します。

B3とC3に半角で数値が入っています。これからC3を全角に替えていきます。
まず、C3をクリックして、セルの書式ダイアログボックスを表示しましょう。
Ctrl＋1キーというショートカットが便利ですね。

セルの書式設定ダイアログボックスが表示されたら、分類をその他。
種類は全角(１２３４５)を選択して、OKボタンをクリックしましょう。

そうすると、C3が全角の１２３４５になったことが確認できます。

この場合の利点は、なんといっても、数値型なんだけど、
表示形式が全角になっているという事ですね。つまり、計算にしようしてもエラーにならない。
これを文字型にしちゃうと、エラーになってしまいます。

せっかくの機会ですので、表示形式を詳しく見てみたいと思います。
C3をクリックして、セルの書式設定ダイアログボックスを再び表示して、
今度は、分類をユーザー定義にしてみましょう。

種類が、[DBNum3][$-411]0と設定されていますね。これが、全角の表示方法なのです。
以前もご紹介しましたが、
[DBNum3]は、
[$-411]は、
さて、他の方法はないのかな？と思いつくのは、
入力規則で入力する時に全角にしちゃえばいいんだ！
と思いつく方もいると思いますので、それでは、今度は、入力規則を紹介してみます。

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

そうしますと、データの入力規則ダイアログボックスが表示されてきますので、
日本語入力タブに合わせて、日本語入力をコントロールなしから全角英数字に変えて、
OKボタンをクリックしてみましょう。

では、言語バーが【あ般】になっていることを確認して、C4に全角で数字を入力してみましょう。
するとどうでしょうか。言語バーは【A般】と表示されているので確かに、全角英数字なのですが、
数字。結局半角になっちゃいましたよね。

確かに当たり前といえば、当たり前なのですが…最初にも書きましたが、
Excelは数値がセルに入力されると数値型になってしまう訳で、全角で入力しても、
半角になってしまう訳ですね。ですが、Ｂ３０とC4に入力してみましょう。

すると今度は数字はちゃんと全角になっていますよね。

これは、Bがついているので、Excelが数値型ではなく、文字型として認識した訳です。
ですので、全角になりました。

結果として、入力規則としてはイマイチかなぁ～と。
最後は、JIS関数を使ってみましょう。
このJIS関数は、半角を全角に変える関数です。
それでは、C5をクリックして、JIS関数のダイアログボックスを表示しましょう。

そうしましたら、文字列のボックスにB5と入力しましょう。
そして、OKボタンをクリックすると、完成。

ただ、紹介しておいてなんですが…JIS関数を使うとしたら、
別セルに入力してその結果を全角のセルで見るのか？

または、JIS関数で算出した結果をコピーして値として再度貼り付けるのか？
という手間をかける必要がありますので、これも、実務的にはイマイチなのかなぁ～と、
思います。