学習内容
■キーワードをもとにファイルを検索する
■データの計算を行う数式を作成する
■セルに名前を付ける
■数式中で名前やラベルを利用する
Lessn2～4では、Exce1の基本的な機能や操作方法を試しながら、テンプレートを作成します。本Lessonでは、テンプレートとなる送付状に数式を追加し、数式の作成方法について学びます。テンプレートを作成し、利用することで、ワークシートの設定などの煩雑な作業を省略できます。テンプレートを利用するには、そのコピーを開き、必要なデータを入力します。テンプレートワークシートの既定の位置にはすでに数式が用意されているので、あらためて数式を入力する必要はありません。以下のLessonでは、商品の合計、小計、消雪税、合計金額を自動的に求める数式を、送付状に追加します。また、セルに名前を付け、その名前やラベルを数式中で利用する方法についても学びます。
まずは、[Excel SBS Practice]フォルダの[03Lesson.xls]ファイルを開いてください。
アイランド物産の送付状ファイルがどんなに増えても、これらのファイルが共通に持っているファイルブ口パティを検索することで、いつでも簡単に送付状ファイルだけを見つけ出すことができます。Lesson2で作成したファイルを引き続き利用するにしても、03Lesson.xlsファイルを利用するにしても、まずは必要なファイルを見つける必要があります。
この実習では、キーワードを使って[ITC Invoice]ファイルを検索し、開きます。
1 [標準]ツールバーの[開く]ボタンをクリックします。
2 [検索先]テキストボックスの下向き矢印をクリックし、ハードディスクのドライブ番号アイコン(通常は(C:))を選択します。
・Excelは、ハードディスク全体を検索の対象とします。
3 [詳細設定…]ボタンをクリックします。
・[詳細な条件を使用した検索]ダイアログボックスが表示されます。
4 [詳細な条件を使用した検索]ダイァログボックス下部の[プロパティ]リストボックスの下向き矢印をクリックし、"キーワード"を選択します。
5 [条件]テキストボックスに"という語を含む"と表示されていることを確認し、[値]テキストボックスに送付状と入力します。
6 [条件に追加]ボタンをクリックします。
・ダイアログボックス上部の[次の条件でファイルを検索します]リストボックスにキーワードが追加されます。
7 [サブフォルダも検索する]チェックボックスをオンにします。
8 [検索開始]ボタンをクリックします。
・検索結果が[ファイルを開く]ダイアログボックスに表示されます。
9 [lesson03]ファイルをダブルクリックします。
・ファイルが開きます。
1[ファイル]ー[名前を付けて保存...]を選択します。
・[ファイル名を付けて保存]ダイアログポッ>スが表示されます。[保存先]テキストボックスに[Ex㏄l
SBS Practice]と表示されていることを確認する。
2 [ファイル名]テキストボックスのファイル名部分をダブルクリックし、lTC lnvoiceと入力します。
3 [保存]ボタンをクリックするか、Enterを押します。
以下の実習では、送付状に必要な数式を記述しますまずは、注文された商品ごとの合計を求める数式を作成し、次に、注文の小計、消費税、総合計を求める数式を作成します。
数式を追加したなら、正しい結果が表示されるかどうかによって、数式が正しく機能しているかを確認します。この実習では、仮のデータを入力することで、正しい数式が入力されているかどうかを確認します。
1 セルA10を選択します。
2 10と入力し、tabを押し、キーマンと入力し、Enterを押します。
・Enterキーを押すと、オートリターン機能によって、アクティブセルは次の行の先頭へと移動します。
3 手順2を繰り返して、ダージリンを15キロ、コナコーヒーを50キロ注文します。
4 セルI10を選択します。
5 2580と入力し、Enterを押します。
6 手順5を繰り返し、ダージリンの単価"5410"と、コナコーヒーの単価“2800"を入力します。
・画面の様子は次の図のようになります
この講習では、単価に数量をかけて、商品ごとの合計を求める数式を作成します。
1 セルJ1Oを選択します。
2 =と入力し、セルA10をクリックし、*と入力し、セルI1OをクリックしてEnterを押します。
先ほど作成した数式は、"合計"列のすべてのセルで使用します。しかし、セルを1つずつコピーしていたのでは面倒なので、ここではオートフィル機能を利用します。
オートフィルによって、数式を隣接するセル範囲にまとめてコピーすることが可能です。また、数式中のセル参照は、自動的に調整されるため、コピー先でも正しい計算結果が表示されます。
この講習では、オートフィルを使って、先ほど作成した数式を"合計"列のすべてのセルにコピーします。
1 セルJ1Oを選択します。
2 アクティブセルの右下の小さな黒い四角形("フィルハンドル")をポイントします。
・マウスポインタの形は、黒い十字型に変化します。
3 フィルハンドルをセルJ23までドラッグし、マウスボタンを離します。
・数式はセルJ11からJ23までコピーされますJ13からJ23にはOが表示されますが、これはA13からA23,I13からI23にデータがまだ入力されていないためです。
この講習では、商品ごとの合計をさらに合計する数式を作成します。このような数式は、ＳＵＭ関数を利用することで簡単に作成できます。
1 セルJ24を選択します。
2 [標準]ツールバーの[オートSUM］ポタンをクリックします。
・[オートSUM]ボタンは、SUM関数を使用した数式を挿入しますまた、合計の対象となる範囲の周囲にマーキーが表示され、そのセル参照が数式で使用されます。ここでは、[オートSUM]ボタンが選択した範囲(J1O:J23)をこのまま受け入れます。
3 Enterを押します。
・小計セルには、SUM関数の結果"246950"が表示されます。
アイランド物産は、すべての注文に対して5%の消費税を課し、送付状にも消費税額を提示します。この講習では、小計セルの値をもとに、消費税額を求める数式を作成します。
1 セルJ26を選択します。
2 =と入力し、セルJ24をクリックし、*.05と入力し、Enterを押します。
・税額を求める数式が入力されます。
3 ワークシートに加えて変更結果を保存します。
これまで作成してきた数式は書式的にも間違いはなく、正しく機能していますが、あまり説明的ではありません。数式を見ただけでは、どんな計算を行っているかわかりません。セルやセル範囲に名前を付けることや、セルに入力されているデータの役割をはっきりと明示できます。そして、その名前をセル参照の代わりに数式中で利用すれば、数式はよりわかりやすくなります。たとえば、"=単価*数量"という数式は、"=A16*I16"という数式よりも直接的でわかりやすいでしょう。以下の講習では、小計、送料、税額のセルに名前を付けます。
この講習では、小計、送料、および消費税の各セルに名前を付けます。
1 セル範囲I24:J26を選択します。
・このセル範囲には、小計、送料、消費税のラベルおよびデータセルが含まれています。ここでは、必ずラベルとデータが含まれるセルの両方を選択してください。
2 [挿入]－[名前］－[作成...]を選択します。
・[名前の作成]ダイアログボックスが表示されます選択範囲の左側の列にラベルがあると判断され、[左端]チェックボックスがオンになっています。
3 [0K]ボタンをクリックします。
・セルJ24からJ26に、セルI24からI26のラベルが名前として付けられます。
4 数式バーの[名前コボックスの下向き矢印をクリックします。
・作成された名前が表示されます。
5 "小計"を選択します。
・小計という名前のセルが選択されます。
この講習では、名前の付けられたセルの値を合計する数式を合計金額セルに作成します。
1 セルJ27を選択し、=と入力します。
・[名前]ボックスが[関数]ボックスに置き替わります
2 [関数]リストの"SUM"をクリックします。
・SUM数式の作成をサポートする数式パレットが表示されます。[数値1］テキストボックスの内容が強調表示されています。
3 セルJ24をクリックします。
・[数値1]ボックスには、このセルの名前が表示されます
4 Tabを押して、セルJ25(送料)をクリックし、Tabを押して・セルJ26(消費税)をクリックします。
・数式パレットには、セルの名前が挿入されます
5 [OK]ボタンをクリックします。
・ＳＵＭ数式が合計金額セルに入力されます。数式の中ではセル名が使用されているため、どのセルが計算の対象になっているかが一目でわかります。
セルがラベルの近くにある場合、セル参照の代わりにそのラベルを使った数式を作成できます。ラベルを名前として定義する必要もなく、その分だけ、作業も簡単に済ませることができます。しかし、ラベルを利用するよりも、名前を利用したほうが効率的な場合もあります。たとえば、数式中でセル範囲のラベルを使用する場合、範囲全体をドラッグしなければなりませんが、その範囲にあらかじめ名前が付けられていれば、その名前を入力するだけで済みます。また、名前は不連続なセル範囲に対しても付けることができますが、ラベルではそうはいきません。また、ラベルとデータがあまりにも離れている場合、Exce1はそれをラベルとは認識しないこともあり
ます。
[ITC Invoice]のワークシートでは、"数量"列と"単価"列のラベルは自動的に認識されます。ですから、ラベルを数式に入力しても、どのセルが計算の対象かが自動的に判断されます。この講習では、商品ごとの合計を求める数式を、ラベルを使った数式に書き直します。
1 セルJ1Oに=数量*単価と入力し、Enterを押します。
・ラベルを使った新しい数式は、セル参照だけの数式よりも意味が明解です。また、ワークシートのラベルを変更しても(たとえば、A10の"数量"を
"注文数"に変更するなど)、数式中のラベルは自動的に更新されます。
2 オートフィルを使って、新しい数式をセル範囲J11:J23にコピーします。
3 ワークシートに加えた変更結果を保存します。
送付状にデータを入力する際、送料をいちいち手作業で算出しなくても済むように、注文に対する送料の計算も自動化することにしました。このセクションでは、送付する商品の総数から総重量を計算し、25キロ未満の注文に対しては低い送料を、25キロ以上の注文に対しては、高い送料を課す数式を作成します。
このような特殊な数式を記述するには、数値式の結果が真か偽かを判定する"論理"関数であるIF関数を使用します。数値式の結果が真ならば、IF関数は2つの値のうちの一方の値を返し、偽ならば、もう一方の値を返します。
たとえぱ=IF(A3<5,100,350)という例を見てみましょう。この数式は、"A3が5未満ならば返される値は100となり、A3が5以上ならば返される値は350となる"という意味です。次の実習では、商品の総重量(25キロ未満か以上か)をもとに送料(1200円か2400円)を決定するIF式を作成します。このIF式は、入れ子式のSUM関数を使って商品の総重量を求めます。入れ子式"の関数とは、他の関数の引数として使用される関数のことで、この例では、SUM関数がIF関数の引数として使用されます。Exce1はSUM関数を最初に計算し、次に、SUM関数の結果を使ってIF関数を計算します。
セル範囲の値の合計を求める場合は、ラベルを使用するよりも、名前を使用したほうが効率的です。そのため、この実習では、"数量"列のセル範囲に名前を付けまず。
1 セル範囲A10：A23を選択します。
2 数式バーの[名前]ボックスをクリックします。
・セル参照が強調表示されます。
3 キロと入力し、Enterを押します。
・[名前]ボックスに新しい名前が表示されます。他のセルをクリックする前に、必ずEnterキーを押します。
1 数式バーの[名前]ボックスの下向き矢印をクリックし、"送料"を選択します。
・送料という名前のセルがアクティブになります。
2 ＝と入力し、[名前]ボックスが変化した、[関数]ボックスの下向き矢印をクリックします。
3 [関数]リストの"IF"を選択します。
・この結果、IF式の作成をサポートする数式パレットが表示されます。挿入ポイントは、IF関数が必要とする３つの"引数"のうち最初の[論理式]テキストボックスに表示されます。
4 sum(キロ)<25と入力し、Tabを押します。
・Sum(キロ)が、数式パレットおよび数式バーに入力され、挿入ポイントは[真の場合]テキストボックスヘ移動します。入れ子関数SUM(キロ)がIF数式に挿入されます。
5 [真の場合]テキストボックスに1200入力し、Tabを押します。
6 [偽の場合]テキストボックスに2400と入力し、[OK]ボタンをクリックします。
・商品の総重量をもとに送料を計算するIF式が、送料セルに入力されます。
7 ワークシートに加えた変更結果を保存する。