Excelなのにpythonのタグがついてる…！？と思ったあなた、最後まで読めば意味がわかります！
普段、方眼紙と戯れている方も、方眼紙を見て辟易している方も、本来の表計算ソフトとしてのExcelの使い方を見て心を清めましょう。
お題
【ルール】
入力として正の整数 N を与えたら 4 から始まる 合成数 の数列の 1 番目から N 番目までの合計を出力してください
N は最大で 100 とします
これに、Excelで挑みます。
【追加ルール】
私が前回やったWolfram Alphaでは身も蓋もない感じだったので、今回は以下のようなルールを作ります。
- マクロは使わない
- すべてのセルに全く同じ数式を入れる
- 答え・N以外をセルに表示することを許す（ここはあまり本質的ではないので）
結論
コードは以下です。
=IF(COLUMN()=1,IF(OR(AND(MOD(ROW(),2)=0,ROW()<>2),AND(MOD(ROW(),3)=0,ROW()<>3),AND(MOD(ROW(),5)=0,ROW()<>5),AND(MOD(ROW(),7)=0,ROW()<>7),AND(MOD(ROW(),11)=0,ROW()<>11)), IF(ROW()>1,1,0), 0),IF(COLUMN()=2,ROW()*INDIRECT("A"&ROW()),IF(COLUMN()=3,INDIRECT("A"&ROW())*SUM($B$1:INDIRECT("B"&ROW())),LARGE($C:$C,101-ROW()))))
↓環境によっては折り返さず読めないので、適宜読みやすく改行してみました。改行したものはそのまま使えないのでご注意ください。
=IF(COLUMN()=1,
IF(OR(
AND(MOD(ROW(),2)=0,ROW()<>2),
AND(MOD(ROW(),3)=0,ROW()<>3),
AND(MOD(ROW(),5)=0,ROW()<>5),
AND(MOD(ROW(),7)=0,ROW()<>7),
AND(MOD(ROW(),11)=0,ROW()<>11)),
IF(ROW()>1,1,0), 0),
IF(COLUMN()=2,
ROW()*INDIRECT("A"&ROW()),
IF(COLUMN()=3,
INDIRECT("A"&ROW())*SUM($B$1:INDIRECT("B"&ROW())),
LARGE($C:$C,101-ROW())
)))
これを、A1:C133と、D1:D100に貼り付けます。これを一つ一つ貼り付けるの…？と思ったあなた、ご安心を。
A1セルに貼り付けてから、上記の範囲に貼り付けても、ちゃんと数式が変わらないように設計しています。安心と信頼の設計ですね！
雰囲気
解説
やってることは単純なのですが、概要と実際に使った関数の２つの側面から解説をしていきます。
概要
この式はIFによりA列・B列・C列・D列で振る舞いを変えています。
- A列で、その行の数が合成数なら1、そうでなければ0を返すようにしています。
- B列で、その行の数が合成数ならそれを、そうでなければ0を返すようにしています。
- C列で、その数が合成数ならばその数までのすべての合成数の和を、そうでなければ0を返すようにしています。
- これまでの計算でほとんど答えは出ているのですが、D列で、100番目に大きい数、99番目に大きい数、…を出力しています。
関数
IF
条件式を第一引数に指定して、真の時は第二引数、偽の時は第三引数を返します。
AND
各引数の論理積を返します。
OR
各引数の論理和を返します。
MOD
第一引数を第二引数で割ったあまりを返します。
ROW
そのセルが何行目かを返します。よく使いますね。
COLUMN
そのセルが何列目かを返します。個人的にはあまり使いません。
INDIRECT
セルを指定する文字列を引数に与えることで、対応するセルを返します。（説明が多少正確でないかも）
https://support.office.com/ja-jp/article/indirect-%E9%96%A2%E6%95%B0-474b3a3a-8a26-4f44-b491-92b6306fa261
LARGE
第一引数で指定した範囲の、第二引数番目の数を返します。
SUM
引数で指定したセルたちの和を返します。
でも、ここに書いてあるのはNが最大で100だから通用してるよね…？
11ってナニ？※√133を超えない最大の素数
133ってナニ？※100番目の合成数
ばかなの？しぬの？
…出来らあっ！
自分で書いた「マクロは使わない」というルールを守るために、今回はPythonでブックを出力するようにします。
計算をするのはExcelなのでセーフ。Pythonはエディタ。
Macの場合は$ pip3 install openpyxlなどでOpenPyXlをインストールしておきます。
import openpyxl
from math import sqrt
m = int(input('Please input the maximum number of N:'))
max_ = [c for c in range(4, 2 * m + 3) if any([not c % i for i in
range(2, int(sqrt(c)) + 1)])][:m][-1]
and_mod_row = ','.join(
['AND(MOD(ROW(),{0})=0,ROW()<>{0})'.format(p) for p in
range(2, int(sqrt(max_)) + 1) if all(
[p % i for i in range(2, int(sqrt(p) + 1))])])
cell_function = '=IF(COLUMN()=1,IF(OR(' + and_mod_row + \
'),IF(ROW()>1,1,0), 0),IF(COLUMN()=2,ROW()*INDIRECT("A"&ROW()),' + \
'IF(COLUMN()=3,INDIRECT("A"&ROW())*SUM($B$1:INDIRECT("B"&ROW())),' + \
'LARGE($C:$C,' + str(m+1) + '-ROW()))))'
wb = openpyxl.Workbook()
sheet = wb.active
for col in ['A', 'B', 'C', 'D']:
for row in range(1, m + 1 if col == 'D' else max_ + 1):
sheet[col + str(row)] = cell_function
wb.save('test.xlsx')
こら、そこPythonだったら合成数列のところを適当にいじれば実質一行で答えが出るとか言わない！
sum((lambda n: [c for c in range(4, 5*n) if any([not c%i for i in range(2, c//2+1)])][:n])(int(input())))
感想
200番目の合成数が255だということを、これを使って初めて知りました。（だから何）
おしまい
たのしめ！