Tipsによると
= ROUND(A1, 2-INT(LOG(ABS(A1))))
四捨五入の桁数をセルの桁数に応じて動的に変えるという技。
便利と思って使ったら時々桁数が減る。
何かと思って調べてみると、単に下の位が0で切られていただけだった。
以下その調査。桁数を4桁にしてある。
| 数値 | LOG(ABS(A2)) | INT(LOG(ABS(A2))) | 3-INT(LOG(ABS(A2))) | CONCATENATE(“_”,ROUND(A2,3-INT(LOG(ABS(A2)))),”_”) |
| 0.10000 | -1 | -1 | 4 | _0.1_ |
| 0.12345 | -0.908508906 | -1 | 4 | _0.1235_ |
| 0.14690 | -0.832978204 | -1 | 4 | _0.1469_ |
| 0.17035 | -0.768657862 | -1 | 4 | _0.1704_ |
| 0.19380 | -0.712646227 | -1 | 4 | _0.1938_ |
| 0.21725 | -0.663040215 | -1 | 4 | _0.2173_ |
| 0.24070 | -0.61852391 | -1 | 4 | _0.2407_ |
| 0.26415 | -0.578149385 | -1 | 4 | _0.2642_ |
| 0.28760 | -0.541211118 | -1 | 4 | _0.2876_ |
| 0.31105 | -0.507169794 | -1 | 4 | _0.3111_ |
| 0.33450 | -0.475603878 | -1 | 4 | _0.3345_ |
| 0.35795 | -0.446177633 | -1 | 4 | _0.358_ |
| 0.38140 | -0.418619311 | -1 | 4 | _0.3814_ |
| 0.40485 | -0.392705856 | -1 | 4 | _0.4049_ |
| 0.42830 | -0.368251926 | -1 | 4 | _0.4283_ |
| 0.45175 | -0.345101839 | -1 | 4 | _0.4518_ |
| 0.47520 | -0.323123568 | -1 | 4 | _0.4752_ |
| 0.49865 | -0.302204177 | -1 | 4 | _0.4987_ |
| 0.52210 | -0.282246307 | -1 | 4 | _0.5221_ |
| 0.54555 | -0.26316544 | -1 | 4 | _0.5456_ |
| 0.56900 | -0.244887734 | -1 | 4 | _0.569_ |
| 0.59245 | -0.227348296 | -1 | 4 | _0.5925_ |
| 0.61590 | -0.210489796 | -1 | 4 | _0.6159_ |
| 0.63935 | -0.19426133 | -1 | 4 | _0.6394_ |
| 0.66280 | -0.1786175 | -1 | 4 | _0.6628_ |
| 0.68625 | -0.163517643 | -1 | 4 | _0.6863_ |
| 0.70970 | -0.148925195 | -1 | 4 | _0.7097_ |
| 0.73315 | -0.134807161 | -1 | 4 | _0.7332_ |
| 0.75660 | -0.121133663 | -1 | 4 | _0.7566_ |
| 0.78005 | -0.107877559 | -1 | 4 | _0.7801_ |
| 0.80350 | -0.095014119 | -1 | 4 | _0.8035_ |
| 0.82695 | -0.082520748 | -1 | 4 | _0.827_ |
| 0.85040 | -0.070376748 | -1 | 4 | _0.8504_ |
| 0.87385 | -0.058563109 | -1 | 4 | _0.8739_ |
| 0.89730 | -0.047062332 | -1 | 4 | _0.8973_ |
| 0.92075 | -0.035858272 | -1 | 4 | _0.9208_ |
| 0.94420 | -0.024936004 | -1 | 4 | _0.9442_ |
| 0.96765 | -0.014281699 | -1 | 4 | _0.9677_ |
| 0.99110 | -0.003882524 | -1 | 4 | _0.9911_ |
| 1.01455 | 0.006273455 | 0 | 3 | _1.015_ |
| 1.03800 | 0.016197354 | 0 | 3 | _1.038_ |
| 1.06145 | 0.025899541 | 0 | 3 | _1.061_ |
| 1.08490 | 0.035389709 | 0 | 3 | _1.085_ |
| 1.10835 | 0.044676926 | 0 | 3 | _1.108_ |
| 1.13180 | 0.05376969 | 0 | 3 | _1.132_ |
| 1.15525 | 0.062675977 | 0 | 3 | _1.155_ |
| 1.17870 | 0.071403284 | 0 | 3 | _1.179_ |
| 1.20215 | 0.079958661 | 0 | 3 | _1.202_ |
| 1.22560 | 0.088348752 | 0 | 3 | _1.226_ |
| 1.24905 | 0.096579824 | 0 | 3 | _1.249_ |
| 1.27250 | 0.104657791 | 0 | 3 | _1.273_ |
| 1.29595 | 0.112588246 | 0 | 3 | _1.296_ |
| 1.31940 | 0.12037648 | 0 | 3 | _1.319_ |
| 1.34285 | 0.128027504 | 0 | 3 | _1.343_ |
| 1.36630 | 0.135546068 | 0 | 3 | _1.366_ |
| 1.38975 | 0.142936683 | 0 | 3 | _1.39_ |
| 1.41320 | 0.150203629 | 0 | 3 | _1.413_ |
| 1.43665 | 0.157350977 | 0 | 3 | _1.437_ |
| 1.46010 | 0.164382601 | 0 | 3 | _1.46_ |
| 1.48355 | 0.171302188 | 0 | 3 | _1.484_ |
| 1.50700 | 0.178113252 | 0 | 3 | _1.507_ |
| 1.53045 | 0.184819146 | 0 | 3 | _1.53_ |
| 1.55390 | 0.191423067 | 0 | 3 | _1.554_ |
| 1.57735 | 0.19792807 | 0 | 3 | _1.577_ |
| 1.60080 | 0.204337076 | 0 | 3 | _1.601_ |
| 1.62425 | 0.210652875 | 0 | 3 | _1.624_ |
| 1.64770 | 0.216878142 | 0 | 3 | _1.648_ |
| 1.67115 | 0.223015433 | 0 | 3 | _1.671_ |
| 1.69460 | 0.229067202 | 0 | 3 | _1.695_ |
| 1.71805 | 0.235035799 | 0 | 3 | _1.718_ |
| 1.74150 | 0.240923479 | 0 | 3 | _1.742_ |
| 1.76495 | 0.246732407 | 0 | 3 | _1.765_ |
| 1.78840 | 0.252464661 | 0 | 3 | _1.788_ |
| 1.81185 | 0.25812224 | 0 | 3 | _1.812_ |
| 1.83530 | 0.263707065 | 0 | 3 | _1.835_ |
| 1.85875 | 0.269220982 | 0 | 3 | _1.859_ |
| 1.88220 | 0.274665769 | 0 | 3 | _1.882_ |
| 1.90565 | 0.280043139 | 0 | 3 | _1.906_ |
| 1.92910 | 0.285354741 | 0 | 3 | _1.929_ |
| 1.95255 | 0.290602164 | 0 | 3 | _1.953_ |
| 1.97600 | 0.29578694 | 0 | 3 | _1.976_ |
| 1.99945 | 0.300910548 | 0 | 3 | _1.999_ |
| 2.02290 | 0.305974414 | 0 | 3 | _2.023_ |
| 2.04635 | 0.310979916 | 0 | 3 | _2.046_ |
| 2.06980 | 0.315928383 | 0 | 3 | _2.07_ |
| 2.09325 | 0.3208211 | 0 | 3 | _2.093_ |
| 2.11670 | 0.32565931 | 0 | 3 | _2.117_ |
| 2.14015 | 0.330444213 | 0 | 3 | _2.14_ |
| 2.16360 | 0.335176973 | 0 | 3 | _2.164_ |
| 2.18705 | 0.339858712 | 0 | 3 | _2.187_ |
| 2.21050 | 0.344490519 | 0 | 3 | _2.211_ |
| 2.23395 | 0.349073449 | 0 | 3 | _2.234_ |
| 2.25740 | 0.353608521 | 0 | 3 | _2.257_ |
| 2.28085 | 0.358096725 | 0 | 3 | _2.281_ |
| 2.30430 | 0.36253902 | 0 | 3 | _2.304_ |
| 2.32775 | 0.366936335 | 0 | 3 | _2.328_ |
| 2.35120 | 0.371289573 | 0 | 3 | _2.351_ |
| 2.37465 | 0.375599608 | 0 | 3 | _2.375_ |
| 2.39810 | 0.379867289 | 0 | 3 | _2.398_ |
| 2.42155 | 0.384093441 | 0 | 3 | _2.422_ |
| 2.44500 | 0.388278863 | 0 | 3 | _2.445_ |
| 2.46845 | 0.392424335 | 0 | 3 | _2.468_ |
| 2.49190 | 0.39653061 | 0 | 3 | _2.492_ |
| 2.51535 | 0.400598424 | 0 | 3 | _2.515_ |
| 2.53880 | 0.40462849 | 0 | 3 | _2.539_ |
| 2.56225 | 0.408621502 | 0 | 3 | _2.562_ |
| 2.58570 | 0.412578135 | 0 | 3 | _2.586_ |
| 2.60915 | 0.416499047 | 0 | 3 | _2.609_ |
| 2.63260 | 0.420384877 | 0 | 3 | _2.633_ |
| 2.65605 | 0.424236246 | 0 | 3 | _2.656_ |
| 2.67950 | 0.428053761 | 0 | 3 | _2.68_ |
| 2.70295 | 0.431838012 | 0 | 3 | _2.703_ |
| 2.72640 | 0.435589573 | 0 | 3 | _2.726_ |
| 2.74985 | 0.439309004 | 0 | 3 | _2.75_ |
| 2.77330 | 0.442996852 | 0 | 3 | _2.773_ |
| 2.79675 | 0.446653647 | 0 | 3 | _2.797_ |
| 2.82020 | 0.450279908 | 0 | 3 | _2.82_ |
本業設計!! 