表計算ソフトの基本

(5)表計算ソフトの例(その3)

[例題3] 下の表は、例題2を行と列を入れ替え、一部を削除したものである。いまG列にどんぶりものの熱量点数の高い方からの順位を入れ、表を完成させなさい。また、合計の熱量得点の高いもの順に、データを並べ替えなさい。合計の代わりに、第一群〜第四群まで試みなさい。                                         
  

操作手順

STEP1 例題2の完成した表から、行と列を入れ替えて複写する。
a)複写元を選ぶ。
b)ホームのコピーアイコン を押す。 
c)複写先を指定する。 

行列入替えd)左図のように、ホームの「貼り付け」アイコンの下にある▽矢印をクリックし、出てくるメニューから
「行列を入れ替える」をクリックすれば、行と列を入れ替えて複写される。

STEP2  ワークシート上で、セルG2に式=RANK(F2,F2:F12,0)を入力する。 
順位は統計関数RANKをRANK(基準値,データの範囲,順序)という形で用いる。 ここで順序は0(大きい順、省略可)か1(小さい順)。
 
「“親子丼”は“親子丼、…、ビビンバ丼”の中で“(大きい順に)”何位か。」の例を考えると、
意味
“親子どん”
“親子どん、…、平均の4つ”
(大きい順に)
関数の変数
数値
参照
順序
ワークシート
F2
F2:F12
0
というふうに、 “親子丼”は“親子丼、…、ビビンバ丼”の中で“(大きい順に)”の3つに対応する3変数、数値、参照、順序が決まり、G2にはいる関数は上のようになる。
 
STEP3 データの範囲を絶対番地に変える。G2の式=RANK(F2,F$2:F$12,0) 
STEP4 セルG2からG3:G12まで「複写」する。

計算式の複写のRULEその2(絶対番地$)

   実習その2の「計算式の複写のRULE」に従うと、
 
@複写元と複写先のセル番地の列記号が同じで行番号だけが変わる場合
複写元
 複写先
セルG2:=RANK(F2,F2:F12,0) セルG3:=RANK(F3,F3:F13,0)
セルG4:=RANK(F4,F4:F14,0)
セルG5:=RANK(F5,F5:F15,0)
となり、例えばG3の“カツ丼”へ複写すると、基準値はF3になりOKだが、参照は、そのままでは、F3:F13にかわり、“親子どん、…、ビビンバ丼”の中での順位にならない。

   そこで、変わっては困る「列記号」または「行番号」の前に、$をつける。

(これを、絶対番地に変えるという。)例えばSTEP3のように、絶対番地に変えてから複写すると
 
絶対番地に変えてから複写した場合
複写元
 複写先
セルG2:=RANK(F2,F$2:F$5,0) セルG3:=RANK(F3,F$2:F$13,0)
セルG4:=RANK(F4,F$2:F$14,0)
セルG5:=RANK(F5,F$2:F$15,0)
となり、データの範囲は、F2:F5、すなわち、“親子どん、…、ビビンバ丼”の中での順位になりうまくいく。

 なお、A複写元と複写先のセル番地の行番号が同じで列記号だけが変わる場合、
には、「行番号」の前ではなく、「列記号」の前に$をつける。(例:$F2:$H2)

また@Aの場合以外には、「行番号」「列記号」の両方の前にをつける
(例:$F$2:$H$2)
 
STEP5 データーの並び替えは、メニューバーのホームから並べ替えとフィルター→ユーザー設定の並べ替え(U)を選ぶ。
  先頭行をデータの見出しとして使用し、 最優先されるキーとして「合計」、順序として、「降順」を選び、「OK」を押す。


(6)表計算ソフトの例(その4)

[例題4] 18歳女性の平均身長は、157.8cmである。下図のように、体重か40kgから2kgづつ100kgまでの31ケースのそれぞれに対して、BMIを求めよ

  A B C D E
1 No 身長(m) 体重(kg) BMI 肥満判定
2 1 1.578 40    
3 2 1.578 42    
4 3 1.578 44    
  ・・ ・・・ ・・・    
30 29 1.578 96    
31 30 1.578 98    
32 31 1.578 100    

 なお、BMIはW÷H÷Hで与えられる。ここで、Wは体重(kg)、Hは身長 (m)である。

(2)また、BMIを使って、BMI<18.5 ・・・ やせ、 18.5BMI<25 ・・・ 正常 25BMI ・・・ 肥満 と肥満判定することが可能である。
IF関数を2つ使って、31ケースすべてに対して、肥満判定しなさい。

(3)(2)までに作表したシート丸ごとコピーし、肥満判定の結果が「やせ」のものだけ、抽出しなさい。 なお、シート名は、「やせ抽出」とすること。

操作手順

(1) オートフィルを使った表の入力
a)見出しの行を含め、(3列) 3行分入力
b)3行全体を選び、カーソルを右下に合わせる。カーソルが十字形に変わったら、Ctrlキーを押しながら下方向へドラッグ。
c) BMI(D2のセル)の計算式を、セル番号B2およびC2で表わす。
d)D2を、D3からD8までコピーする。
(2) まず、if文を使って、BMIが18.5未満の場合、「やせ」とE列に表示させなさい。
うまくいけば、その式を修正し、if文を2つ使って、肥満判定(やせ、正常、肥満)とE列に表示させなさい。
(3) 並べ替えとフィルター→フィルターとし、肥満判定のをクリックし、出てくる選択肢から「やせ」のみを残す。

STEP3 (2)のシートに、F列に、肥満判定(詳細)として、

BMI<18.5 やせ
18.5BMI<25 正常

25≦BMI<30

肥満1度
30BMI<35 肥満2度
35≦BMI<40 肥満3度
40BMI 肥満4度
と6段階で、肥満判定しなさい。