Excel でメルセンヌ素数の桁を求めます

[EX06] メルセンヌ素数の桁

 $2^n-1$ の形をした素数を メルセンヌ素数 (Mersenne number) とよびます。29 番目のメルセンヌ素数 $2^{110503}-1$ の桁数を求めてください。

【ヒント】指数計算の演算子を使って「 =2^110503 」などとしてしまうとエラーが表示されてしまいます。 Excel で扱える数字の桁の限界を超えているからです。問われているのはあくまで 桁数 です。ちなみに、この 29 番目のメルセンヌ素数はウォルター・コルキットとルーク・ウェルシュという人物によって発見されたそうです。

【解答】桁を求めるときは常用対数をとるのが定石です

 $-1$ は桁に関係がないので無視して、
 
\[10^x=2^{110503}\]
とおきます($x$ の整数部に 1 を加えた値が桁数です)。両辺の常用対数をとると
 
\[x=\log_{10}2^{110503}=110503\log_{10}2\]
となります。Excel には常用対数を計算する LOG10 という関数が用意されているので、セルに

=110503*LOG10(2)

と入力すると 33264.71761 という値が得られます。よって答えは 33265 桁 となります。あるいは底を指定して対数を計算する LOG関数を使って

=110503*LOG(2,10)

としても同じ値を得ることができます。

【EX07】車の平均時速

 車で 5km 先の目的地まで移動したとき、1 km ごとの速度 [km/h] は次の表のようになっていました。

区間 [km] 時速 [km/h]
0 – 1 58
1 – 2 60
2 – 3 68
3 – 4 63
4 – 5 61

 この車の平均時速 [km/h] を求めてください。

【ヒント】少しマイナーですけど、Excel にはこの種の平均を計算する関数も用意されています。それを知らなくても他の関数で代用することもできます。あと、上の表を貼りつける前に、区間の列が入る部分の表示形式は [文字列] にしておいてください。

【解答】調和平均で計算します

 N 個のデータ $x_1,\:x_2,\:\cdots\:x_N$ があって、N を個々の値 $x_i$ の逆数の平均値で割った値を 調和平均 (harmonic mean) といいます。
 
\[M_h=\frac{N}{\displaystyle \sum_{i=1}^{N}(1/x_i)}\]
 調和平均は主に 平均速度 を計算するときに用います。たとえば今回の例では 1 km ごとの速度が

\[58,\ 60,\ 68,\ 63,\ 61\ \mathrm{[km/h]}\]
なので、各区間を移動するのに要した時間はそれぞれ

\[\frac{1}{58},\ \frac{1}{60},\ \frac{1}{68},\ \frac{1}{63},\ \frac{1}{61}\]
となります。したがって全区間の距離 5km を移動するのに要した時間は

\[T=\frac{1}{58}+\frac{1}{60}+\frac{1}{68}+\frac{1}{63}+\frac{1}{61}\]
となるので、$T/5$ を計算すれば平均速度が得られます。Excel には調和平均を計算する HARMEAN関数 があります。問題文の表を貼りつけて、下のようなシートを作ってください。

 Excelで車の平均時速を計算

 ただし、データを貼りつける前に予め B 列の表示形式を [文字列] にしておいてください。そうしないと「 2 – 3 」というデータが勝手に「 2 月 3 日」というように日付で表示されてしまいます(貼りつけた後で表示を変えるとシリアル値になってしまいます)。セル C9 に

=HARMEAN(C3:C7)

と入力すると、61.82 が返ってきます。よって答えは 61.82km です。もし HARMEAN を使わないのであれば、下のように D 列に 1 km 当たりの所要時間(つまり速度の逆数)を入れてから、セル C9 に範囲の総和をとる SUM関数を使って

=5/SUM(D3:D7)

と入力すると同じ答えが得られます。

 平均速度計算HARMEAN関数

【EX08】世界史テストの点数の平均値と中央値

 都立東池袋高校(架空の学校です)で学期末試験が行われ、 2 年 3 組の世界史の結果は次のようになりました。

氏名 点数 氏名 点数
小畠 龍子 65 素利 宏伸 55
兼政 暁 73 可世木 康市 99
分須 宏朗 51 上井 基保 27
阪井 摩利香 34 東郷 もなみ 79
五島 綾乃 67 栩木 浩賢 64
森脇 まなせ 90 樋富 錦次 89
天沼 結花 33 蛇場見 宏光 36
金指 雅子 36 十河 花音 69
都築 威子 66 坂爪 一喜 78
六本木 晃弥 38 幸正 慶孝 22

 点数の平均値と中央値(メジアン)を計算してください。

【ヒント】中央値(メジアン)とは、点数を小さい順(あるいは大きい順)に並べたときに、中央にくる数のことです。データ個数が奇数であれば、ちょうど中央の数を、データ個数が偶数のときは中央に並んだ2つの数の平均をとります(上の表のデータ個数は偶数です)。

【解答】平均値は AVERAGE で計算します

 問題文の表を貼りつけて、次のようなシートを作ります。

 Excel世界史の点数の平均値と中央値

 Excel では平均値を AVERAGE関数で計算できます。データが 2 列になっているので、それぞれの範囲を半角カンマで区切って指定します。

=AVERAGE(C4:C13,E4:E13)

 58.6 が得られます。中央値(メジアン)は MEDIAN関数で計算します。

=MEDIAN(C4:C13,E4:E13)

と入力すれば 64.5 が返るはずです。

【EX09】条件に合うデータ件数を集計します

 Excel の RANDBETWEEN関数を使って 1 から 3 までの乱数を 100 個生成すると次のようになりました。

2 3 1 2 1 2 1 2 1 2
1 2 3 1 1 2 3 3 1 3
3 3 2 3 3 2 1 3 2 2
2 3 3 1 1 3 3 3 3 2
3 2 2 2 2 2 1 1 1 1
3 1 2 1 2 2 2 3 3 1
1 3 3 3 2 3 1 1 2 2
3 2 1 1 2 2 2 1 1 1
2 2 1 3 3 3 3 3 3 2
3 1 1 3 1 1 2 2 2 3

 1, 2, 3 がそれぞれ何個ずつあるか数えてください。

【ヒント】条件に合うデータの件数を数える関数があります。

【解答】COUNTIF で指定条件を満たすセルを数えます

 次のようなシートを作ります。

 COUNTIF関数で条件に合うデータ検索

 数式の入力を1回で済ませたいので、セル B13 から B15 にはそれぞれ 1, 2, 3 という数を入れてあります。指定した条件に合うデータの件数を数えるときは COUNTIF関数 を使います。その記述形式は

=COUNTIF(範囲,条件)

なので、E2 セルに

=COUNTIF($B$2:$K$11,B13)

と入力すると「 1 」というデータの入ったセルを数えて、31 を返します(オートフィルするので範囲は絶対参照にしておきます)。この数式をまでオートフィルすると E15 までデータが入り、「 2 」と「 3 」がそれぞれ 35, 34 個あることがわかります。

【EX10】平均年齢以上の人は何人?

 次のような氏名と年齢が載った名簿があります。

氏名 年齢 氏名 年齢
馬鳥 久敬 16 万代 ゆり 31
安本 美菜 39 横地 ひさこ 30
赤塩 基久 50 蓮見 騎一郎 70
干場 計佐久 53 今関 絹代 22
夏凪 鋼志 36 津久井 育代 36
白神 結菜 49 井手矢 賢利 70
蒔田 世里 31 栄島 浩吉 41
宿谷 正枝 52 中筋 梢 44
白浜 花織 42 永井 さくら 62
笹部 奈央 27 稲津 春名 33

 平均年齢以上の人は何人いるか求めてください。

【ヒント】簡単そうに見えて案外難しいです。1つの数式で求めることもできますが、先にどこかのセルに平均年齢を計算しておいたほうがわかりやすいです。

【解答】不等号をダブルクォーテーションで囲みます

 下の図のようなシートを作ります。

 ExcelVBAで平均年齢以上の人数を計算
 
 ここではセル C3:C12 と E3:E12 を引数として

=AVERAGE(C3:C12,E3:E12)

とします。あるいは D 列もまとめて

=AVERAGE(C3:E12)

としてもかまいません。41.7 という値が表示されるはずです。次はこの 41.7 歳以上の人の人数をカウントします。指定した条件に合うデータの入ったセルを数えるときは COUNTIF関数 を用います。

=COUNTIF(範囲,条件)

 2つめの引数である [条件] に不等式を書くのですが、そういう場合には数値の前に不等号をダブルクォーテーションで囲んで書き、& で後ろの数値(あるいは参照セル)とつなげます。今の例では

=COUNTIF(C3:E12,">="&E14)

という記述になります。9 が表示されます。

コメント

タイトルとURLをコピーしました