先の記事の続編で、これをエクセルで総当たりで調べてみたって話きょうみあります？
面白そうなのでやってみました。
これ、意外と面白いですよ。
ポイントはこんな感じ。
- IF関数で整数かどうかの判定
- MOD関数とROUNDDOWN関数で整数の各位の値の抽出
- IF関数で照合一致判定
- 判定➀と判定②から答えを導く
エクセルを使った解き方の解説
まず、こんな感じでフォーマットを用意します。
A列には元となる3桁の整数を、100～999用意します。
そしてB列にはA列×4.5の計算式を入れます。
これで準備は完了、条件を満たす整数を見つけていきましょう。
条件判定のルールを決める
今回の場合、判定プロセスが３つあり、すべてを満たす整数が答えです。
ということは、３つの条件判定のルールをあらかじめ決めておくと最後の判定の関数が組みやすくなります。
そして3つの判定プロセスがこちら。
- 4.5倍した数値が整数であること
- もとの数値の100の位が4.5倍した数値の1の位と一致すること
- もとの数値の1の位が4.5倍した数値の100の位と一致すること
条件判定のルールを決めるというのは、満たすとき・満たさないときに返す結果を統一しておくということです。
例えば今回の場合では、条件を満たすときは0、条件を満たさないときは１とし、最後の判定で各条件の判定結果を合計して０なら答え、１以上なら答えではないという関数を組めばいいのです。
条件①：４．５倍した数値が整数かどうか。
これを満たす条件式はどのようになるでしょうか。
例えば101と102で比べてみましょう。
101の時は454.5、102の時は459となります。
条件としては、101の時に1（×）102の時に0（〇）を返す関数を組みたいわけです。
それならIF関数を使うとこうですね。
＝IF（ [条件式] ,0[条件を満たす],1[条件を満たさない]）
そして条件式です。これは一つの例ですが、小数点以下の数字があるときに満たさないので、4.5倍した値からそれを0未満切り捨てした値を引いて、0なら条件を満たすと考えたらいいわけですよね。
関数にするとこうです。
＝B3-ROUNDDOWN(B3,0)
こうすれば、
101→454.5-454=0.5>0 （×）
102→459-459=0 (○)
となります。
IF関数をまとめるとこうです。
=IF(B3-ROUNDDOWN(B3,0)>0,1,0)
で、これをすべての整数に展開するとこんな感じです。
では次。各位の数字に分解してみましょう。
3桁の整数から各位の数字を抽出する方法
これには余りを求める関数MOD関数を使います。
使い方はこう。
＝MOD(分子,分母）
で、ここからの方法はこんな感じです。
100の位を出すには1000で割って余った数字を100で割る(切り捨て）。
10の位を出すには100で割って余った数字を10で割る(切り捨て）。
1の位を出すには10で割って余った数字を1で割る(切り捨て）
これを関数にするとこうなります。なお、分子はA列です。
100の位 ROUNDDOWN(MOD($A3,(100*10))/100,0)
10の位 ROUNDDOWN(MOD($A3,(10*10))/10,0)
1の位 ROUNDDOWN(MOD($A3,(1*10))/1,0)
そして100とか10とか１ってのはタイトル列に入れといて、それを参照すれば関数組むのは1回、あとはコピーで使いまわせます。
同じことを4.5倍した数字にも適用すればこんな感じになりますね。
さて次は照合判定です。
100の位1の位の照合
ここは簡単です。
もとの数字の百の位＝4.5倍した数字の1の位
これが成立するときは１、成立しなければ０を返すので、組むべき関数はこうです。
=IF(D3=I3,0,1)
これだけです。
あわせて元の整数１の位と４．５倍した整数の百の位を照合する関数を作ればOKです。
こんな感じです。
結局、答えは？
では、答えを出していきましょう。
ここまでの判定結果から、すべての条件を満たすとき、判定結果を足しても0であるように関数を組みました。
なので、すべての判定結果を合計して０なれば「答え」１以上になれば「×」となるように関数を組みます。
=IF(SUM(C3,J3:K3)=0,”答え”,”×”)
C列が整数かどうか、J列が１００の位と１の位の照合結果、K列が１の位と１００の位の照合結果でした。
結果はこんな感じです。
で、フィルタをかけると。。。
これだけの整数が条件を満たすことが分かりました。
なお、４．５倍後も３桁である条件を加えるならば１７８と１９８だけですね。
エクセルで総当たりで参照するならこんな感じでした。
最後に
エクセルでも解けるよってのをざっとまとめてみました。
本当なら相対参照・絶対参照とかも説明しないといけないかもしれませんが、Excelでやるならこう考えるといいよってのを紹介したかったので、そのあたりの説明は省いてます。
大事なことは、普通に解くにしてもExcelで解くにしてもどうすれば答えにたどり着けるかを想い描けるかではないでしょうか。
そんなことを思いながらやってみました。
最後までお読みいただき、ありがとうございました。
