Excel。エラーの原因元を探せる参照元・参照先。知っていると便利です。

Excel。エラーの原因元を探せる参照元・参照先

参照元・参照先

MOS（マイクロソフトオフィススペシャリスト）試験のExcel2010Expertの試験範囲でもあるのですが、これは、試験云々というよりも、ビジネスシーンで知っておいていいスキルだと思っておりますので、初心者さんの講座でも、仕事でつかえるExcel講座でも、どんな講座でも、簡単なので、絶対参照のあたりでご紹介をしているものに、【参照元のトレース】と【参照先のトレース】があります。

そんなんあったの？という人も多いと思いますが、コレ。
エラーが出た時に使うと、エラーの謎がだいたい解けちゃうんですね。

今回は、ありがちエラーの構成比を算出したら、#DIV/0!が表示されて、困ったケースを使って、
【参照元のトレース】と【参照先のトレース】をご紹介していきましょう。

まず、下記のような表があります。
料理番組ではありませんが、すでに、H列に構成比を算出してあります。

ご覧のように、H3:H6には、#DIV/0!が表示されています。
確かに、この程度のエラーなら、「絶対参照を忘れた！」と気づくと思いますが、
実務ではもっと、すごい数式で算出しているケースもありますが、
今回はわかりやすく、このケースで話を進めていきます。

まずは、エラーが表示されているH3を見ていくことにしましょう。
この答えを出すための元。すなわち、【参照元のトレース】を使って確認してみましょう。

H3をクリックしたら、数式タブのワークシート分析にある、参照元のトレースをクリックしましょう。

すると、このセルの計算式で使用しているセル番地を矢印で教えてくれるんですね。

H3は、E3とE7を使っていますよという訳です。
すでに、E7は空白セルを使用していますので、これがエラーの原因だとわかりますね。
念のため、H6をクリックして、参照元のトレースをクリックしてみましょう。

一目瞭然で、データが無い所を参照元にしていますよね。
ただ、ビジネスシーンでは、こんな単純ではないと思います。実はもっと遡ることができるんです。
それでは、いったんこのトレース矢印を削除して、H6を遡ってみることにしましょう。

まず、トレース矢印の削除の方法ですが、これまた簡単なので紹介しましょう。

【トレース矢印の削除】をクリックすると削除することが出来ますね。
さて、改めて、H6をクリックしましょう。
まず、参照元のトレースをクリックしてみましょう。

最初のトレース矢印が表示されましたね。
では、また、参照元のトレースをクリックしてみましょう。

お話した後、皆さんからは、便利とか、使えるとか、いってもらえるテクニックの1つですね。
簡単ですので、【参照元のトレース】と【参照先のトレース】。

そして削除するための【トレース矢印の削除】。
覚えておくときっと、実務で使えますよ。

Excel。貼り付けにある演算って何？どう使うの？

Excel。貼り付けにある演算って何？どうつかうの？

貼り付けの演算

初心者さん対象の講座や、仕事でつかえるExcel講座において、「これなんですか？」と聞かれることが多いものの中に、貼り付けのダイアログボックスにある【演算】にある、和算・減算・乗算・除算って何ですか？という質問。

そうですよね。これってなんなんでしょう。どう使うのでしょうか？
ということで、今回は、この演算ブロックの使い方をご紹介しましょう。

ところで、最近、四則演算というコトバも、加減乗除というコトバも「？」の方がおりまして、一応説明をすると…
四則演算は、足し算・引き算・掛け算・割り算の4つ事で、加減乗除とは、加算が足し算・減算が引き算で乗算が掛け算・除算が割り算の意味です。

実は、学生さんが多い時には説明することも結構あるんですよ…以前、五十音順がわからなくて、アイウエオ順って話したこともあったけ。

さて、話は本堂に戻りましょう。
まずは、ダイアログボックスを確認してみましょう。
コピーボタンをおして、貼り付けボタンの▼をクリックして、

形式を選択して貼り付けをクリックすると、形式を選択して貼り付けダイアログボックスが表示されます。
これの真ん中にある、演算。そこに、和算・減算・乗算・除算がありますよね。

これの使い方について、紹介していきましょう。

まず、下記の表があります。そして、B10に1.08。B11に10と入力しておきます。

最初に乗算を紹介します。この金額に1.08、すなわち新消費税の8%が乗ったとした場合の金額を求めたいとします。
まず初めに、B10の1.08をクリックして、コピーボタンをクリックします。そして、算出したいところに範囲選択をします。

そうしたら、貼り付けボタンの▼をクリックして、

形式を選択して貼り付けをクリックすると、形式を選択して貼り付けダイアログボックスが表示されます。乗算にチェックをつけて、OKボタンをクリックしましょう。

すると、表はどうなかったというと、

なんと、B3は、870が939.6に、つまり、870に1.08を乗算した値になりましたね。
こんな風にして使うんです。

通常ならば、例えばプリンの4月を算出するならば、どこかに、B3×B10という式を作って、その値を値のコピーでB3に貼り付ける方法を考えたりしますが、結構面倒ですよね。このような時にこの方法を知っていると便利ですね。

次は、表を元に戻して、10で割ってみましょう。値が大きい時に表示形式を変えるのではなく、直接数値を変えたい場合。すなわち、単位：万円なんてことをする場合ですね。

やり方は、先程と同じです。
まず、B11をクリックし、コピーして、貼り付けボタンの▼をクリックして、形式を選択して貼り付けをクリックすると、形式を選択して貼り付けダイアログボックスが表示されます。

除算にチェックをつけて、OKボタンをクリックしましょう。

すると、10で割った数値になりましたね。

Excel2010。小計で集計した結果を別のシートにコピーしたら？ 可視セルのコピー

Excel2010。小計で集計した結果を別のシートにコピーしたら？

可視セルのコピー

データタブのアウトラインにある。小計。
これを使うと、アイテムごとに集計を算出することが簡単に出来ますね。
この機能は、仕事でつかえるExcel講座や、マンツーマン講座等でも、ご紹介している機能ですね。
テーブルの集計行だと、どうしても一アイテムごとに条件を変えないといけませんけど、
小計なら簡単に算出できるので、こっちも合わせて講義内容に入れている訳です。

小計の使い方は、以前に紹介しておりますので、そちらを確認してもらうとして、
実は、この小計で算出したデータを別のシートにコピーしようとすると、うまくいかないんですね。
それをどうしたらいいのか？というのが今回の目的です。

意外とExcelもアチコチにトラップがあるんですね。

それでは、まず、下記のデータを店舗名を基準に金額の合計を集計してみます。

最初に基準となる列を並び替えないといけないんでしたね。
今回は店舗名ですので、店舗名を昇順に並び替えをしましょう。

並び替えの方法は、B1の店舗名をクリックしてデータタブの並び替えとフィルターにある、
昇順ボタンをクリックします。そうすると、店舗名順で並び替えが出来ますね。

これをやらないで、小計を行ってしまうと、とんでもないことになってしまうことも、
以前紹介しておりますので、そちらを参照してみてください。

また、B1をクリックしましたが、B列を範囲選択をしちゃいますと、
その選択範囲の中のみで並び替えが発生してしまい、
データがバラバラになってしまいますので、分かりやすいように、
今回は、B1の店舗名をクリックして昇順にしております。

並び替えが終わりましたら、データタブのアウトラインにある小計ボタンをクリックしましょう。

そうすると、集計の設定ダイアログボックスが表示されてきますね。

グループの基準は、店舗名ですね。
集計方法は、合計を選択します。
集計するフィールドは金額にチェックをつけましょう。
そうしたら、OKボタンをクリックしましょう。

左側に、アウトラインが設定されました。このアウトラインの2というボタンをクリックしてみましょう。折りたたまれて、店舗ごとの集計が表示されますね。

さて、これからが今回ご紹介したい事なのですが、このデータを別のシートにコピーしたいとします。
では、A1からD806の金額の総計までを範囲選択して、コピーボタンをおして、

別シートに移動して、貼り付けてみましょう。
すると、どうなったでしょうか…

これでいいのでしょうか？

どうなったのかというと、折りたたまれていたデータも一緒にコピーされちゃったんですね。
これが目的ではないんですね。集計されたデータのみを別シートにコピーしたいわけですね。
では、戻るボタンでシートのデータを消しておき、集計されているシートに戻りましょう。

ここで登場するテクニックが、可視セルのコピーというテクニックです。
簡単にいうと、今見えているデータだけをコピーしたい訳ですね。

見えている。つまり【可視】。をコピーするという訳です。

ではやっていきましょう。

まず、範囲選択をしましょう。
次に、ホームタブの検索と選択をクリックして、ジャンプをクリックしましょう。

そうすると、ジャンプのダイアログボックスが表示されます。

セル選択をクリックすると、今度は、選択オプションダイアログボックスが表示されますので、

可視セルを選択してOKボタンをクリックしましょう。
特に変わっておりませんが、

このまま、コピーをしていきます。
コピーボタンをクリックして、シートを切り替えていきましょう。
別のシートのA1をクリックして、貼り付けをしていきましょう。
するとどうでしょう。

うまくいきましたね。テーブルのフィルタやオートフィルタは大丈夫なのですが、
この小計機能だけは、この可視セルのコピーをしないとうまくいかないんですね。

Excel。セル内の好きなところで改行するには？Alt+Enter

Excel。セル内の好きなところで改行するには？

Alt + Enter

Excelの初心者さん講座や、職業訓練。
そして仕事でつかえるExcel講座など、どの会場でも、
「こうやるんですかぁ～」
といわれることが多いテクニックに、セル内の改行があります。

セル内で折り返して表示するにチェックをつける方法をご存知の方は、
いらっしゃるのですが、好きなところで、改行できないという声も多く聞きますね。

そこで、まずは、長い文字をセル内で折りかえして表示する方法もありますので、
まずはそちらからご紹介しましょう。

このように、セルから文字がはみ出しています。
これをH9内で、折りかえして表示してみましょう。

まず、H9をクリックして、セルの書式設定ダイアログボックスを表示しましょう。
セルの書式設定ダイアログボックスは、Ctrl + 1でも表示することができますね。

表示されたら、配置タブに移動して、文字の制御にある、
【折り返して全体を表示する】にチェックをつけて、OKボタンをクリックしましょう。
そうすると、

確かに、H9内で折り返して全体を表示していますが、これではちょっとね。
そこで、列の幅を広げてみましょう。

セルの幅に合わせて、折り返しが変わりました。
しかし、好きなところで折り返すこと、すなわち、改行することができませんね。

確かに、このような長い文章の時は、このままでもいいかもしれませんが、
次のようなケースの場合は、どうでしょう。

この表のF2。売上構成比。
この文字が長いので、売上　（改行）　構成比　というように表示したいとします。
先ほどと同じように、セルの書式設定の配置にある、折り返して全体を表示をやってみましょう。

設定しても、希望通りに、折り返して表示してくれませんね。

列幅を変えないといけませんが、列幅自体がかなり小さくなってしまいますし、
それよりも、売上構　（改行）　成比となってしまって、
これまた、希望のように折り返すことができませんね。

そこで、今回ご紹介する、
セル内改行。

Alt + Enter

を紹介します。

まずは、F2をクリックします。
そして、数式バーの売上の上と構成比の構の字の間にカーソルをいれましょう。

そうしたら、Alt + Enterをします。
その後、他のセルをクリックすると、どうでしょう。

希望のように、セル内の文字を折り返すことができましたね。
ちょっとしたことですが、知っておくといいスキルの一つでしょう。

ただ、注意が必要なのは、行の幅を自分で変更していますと、
自動的に行幅が広がってくれませんので、行幅を変える前に、
文字列を折り返して全体を表示の実行しておくといいですね。

Excel。3D集計したくても、表がバラバラ…。そこで、統合。

Excel。3D集計したくても、表がバラバラ…。そこで、統合。

統合

かなり前ですが、ご紹介したことがある、3D集計。串刺し集計なんていったりもしますが、
これをバージョンアップと言いますか、研修とか、仕事で使えるExcel講座なんかでも、
ちょくちょく、聞かれるものの中に、3D集計を使いたいんだけれど…
支店ごとに表がバラバラで、使えないんですよねぇ～と。

確かに、3D集計は、同じ表で、同じ項目が同じ場所にあることが前提となっている訳ですね。
まずは、3D集計を簡単に紹介しましょう。詳しくは、こちらに掲載しておりますので、ご覧下さい。
Excel。3D集計（串刺し）をつかってみよう！

この3つのシート。どのシートも、同じセル番地に同じ内容が入っています。
これが重要になります。
そして、この3枚のシートの同じセルの合計が、
集計というシートに合算されて算出することが出来る。

これが、3D集計と呼ばれているスキルです。
複数のシートを貫通させる形で算出できるので、多くのシートがあるときには、
こちらの、テクニックも、必要になりますし、現場レベルで使えるスキルですので、
覚えておくといいでしょう。
何度も書きますが、これは、同じセル番地に同じ内容が入っていることが条件なわけです。
しかしながら、現場では、同じ表を使ってないものも、多く、それをどうやって、
算出したらいいのか？という質問を聞くことがあるわけです。

では、できないのか！というと、実は、【統合】というテクニックがあるんですね。
それでは、統合をご紹介しましょう。
まず、それぞれのシートに、下記のような表があります。

ご覧になっていますと、それぞれの表。バラバラですね。これでは、3D集計が使えないわけです。
では、この4つシートの合計を別のシートに算出させていきましょう。
合計行と合計列も含めてもいいのですが、データだけを使っていきたいと思います。
また、それぞれのデータに名前を定義してもいいのですが、
今回は、単純に範囲選択でやってみたいと思います。

それでは、新しいシートのA1をクリックしておきます。

データタブのデータツールにあります、統合をクリックします。

統合のダイアログボックスが表示されます。
集計の方法は、合計のまま。
ちなみに、▼を押してみますと、

合計以外にも、平均や、最大最小値をはじめ、標準偏差などがありますね。
では、合計のままにしておきまして、
統合元範囲をクリックして、それぞれのシートの合計したい範囲を選択していきます。
まずは、本社のシートに移動します。

そして、合計行と合計列を抜いた範囲を選択しますので、A3:D9を範囲選択します。
すると、ボックスには、本社!$A$3:$D$9と表示されますので、追加ボタンをクリックしましょう。

先程、範囲選択した統合元範囲が統合元ボックスに追加されました。
この手順で、他のシートの範囲も選択して、追加ボタンをクリックしていきましょう。
最終的に、このようになります。

ポイントとして、範囲選択はゆっくり、焦らずに選択しましょう。ここで間違えると、
元も子もありません。

それでは、範囲を確認しておきましょう。
本社!$A$3:$D$9
横浜!$A$3:$D$7
川崎!$A$3:$D$10
横須賀!$A$4:$C$12
ですね。
今回は、統合元の項目の並び順が異なった上に、数も異なっていますので、
「項目による統合」をしますので、上端行と左端列にチェックをつけましょう。

なお、項目の並び順・数が同じ場合は、「位置による統合」になりますので、
統合の基準にある、上端行と左端列のチェックはオフのままとします。

チェックをつけたら、OKボタンをクリックしましょう。

これで、完成。意外と簡単でしたね。
ですが、本来ならば、同じ帳票・表を使って管理すべきなので、
ビジネス上で考えた場合は、どうなのかな？と考えちゃいますね。