Office中国论坛/Access中国论坛

标题: 求对一组字符串中部分数据进行计算的公式 [打印本页]

作者: botong 时间: 2009-10-21 12:23
标题: 求对一组字符串中部分数据进行计算的公式
本帖最后由 botong 于 2009-10-22 13:50 编辑

103.750000:100::1627207:3232478;20509:28315;77.700000:100::1627207:3232478;20509:28316;77.700000:100::1627207:3232484;20509:28315;77.700000:100::1627207:3232484;20509:28316; 51.750000:100::1627207:28320;20509:28383; 62.500000:100::1627207:28341;20509:28314;46.800000:100::1627207:28341;20509:28315;46.800000:100::1627207:28341;20509:28316; 33.250000:100::1627207:28324;20509:28383;24.900000:100::1627207:28340;20509:28383;24.900000:100::1627207:3232478;20509:28383; 30.500000:100::1627207:28329;20509:28315;22.800000:100::1627207:2329;20509:28316;22.800000:100::1627207:28341;20509:28315;22.800000:100::1627207:28341;20509:28316; 50.750000:100::1627207:3232481;20509:28314;38.000000:100::1627207:3232481;20509:28315;38.000000:100::1627207:3232481;20509:28316; 55.750000:100::1627207:28320;20509:28383;41.700000:100::1627207:3232478;20509:28383;41.700000:100::1627207:3232480;20509:28383; 63.750000:100::1627207:28341;20509:28314;47.700000:100::1627207:28341;20509:28315;47.700000:100::1627207:28341;20509:28316; 42.500000:100::1627207:28320;20509:28314;31.800000:100::1627207:28320;20509:28315;31.800000:100::1627207:28320;20509:28316;31.800000:100::1627207:28341;20509:28314;31.800000:100::1627207:28341;20509:28315;31.800000:100::1627207:28341;20509:28316;31.800000:100::1627207:3232480;20509:28314;31.800000:100::1627207:3232480;20509:28315;31.800000:100::1627207:3232480;20509:28316; 55.750000:100::1627207:28338;20509:28314;41.700000:100::1627207:28338;20509:28315;41.700000:100::1627207:28338;20509:28316;41.700000:100::1627207:3232478;20509:28314;41.700000:100::1627207:3232478;20509:28315;41.700000:100::1627207:3232478;20509:28316; 109.250000:100::1627207:28320;20509:28314;81.900000:100::1627207:28320;20509:28315;81.900000:100::1627207:28320;20509:28316;81.900000:100::1627207:28329;20509:28314;81.900000:100::1627207:28329;20509:28315;81.900000:100::1627207:28329;20509:28316;81.900000:100::1627207:28332;20509:28314;81.900000:100::1627207:28332;20509:28315;81.900000:100::1627207:28332;20509:28316; 59.750000:100::1627207:28320;20509:28315;44.700000:100::1627207:28320;20509:28316;44.700000:100::1627207:3232480;20509:28315;44.700000:100::1627207:3232480;20509:28316; 99.750000:100::1627207:28332;20509:28315;74.700000:100::1627207:28332;20509:28316;74.700000:100::1627207:28341;20509:28315;74.700000:100::1627207:28341;20509:28316; 94.500000:100::1627207:28324;20509:28315;70.800000:100::1627207:28324;20509:28316; 43.750000:100::1627207:28320;20509:28314;32.700000:100::1627207:28320;20509:28315;32.700000:100::1627207:28320;20509:28316;32.700000:100::1627207:28332;20509:28314;32.700000:100::1627207:28332;20509:28315;32.700000:100::1627207:28332;20509:28316;32.700000:100::1627207:28338;20509:28314;32.700000:100::1627207:28338;20509:28315;32.700000:100::1627207:28338;20509:28316; ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 有以上一列数据串，每一行在一个单元格中(在附件中有下载，直接复制到excel中会乱格式)，要求编一个公式对这组数据做如下计算：对形如109.250000进行计算，比如除以3然后乘以4，其它部分不变。（注意：每一个串中有多个这样的数。）并且要求计算后数的格式不变，即小数点后保留两位有效数字，然后在其后补四个零。 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 举例：计算前：62.500000:100::1627207:28341;20509:28314;46.800000:100::1627207:28341;20509:28315;46.800000:100::1627207:28341;20509:28316; 计算后： 83.330000:100::1627207:28341;20509:28314;62.400000:100::1627207:28341;20509:28315;62.400000:100::1627207:28341;20509:28316; ---------------------------------------------- 希望各位高手帮忙，不甚感谢，最好能解释一下公式。数字串原始文件点这里[attach]40069[/attach]下载。

作者: pureshadow 时间: 2009-10-21 12:39
我按您的建议复制到EXCEL里了，所有的数据都在一个单元格里，那个叫混乱啊……
您还是直接发个工作簿吧

作者: botong 时间: 2009-10-22 13:56
2# pureshadow
我刚才试了一下还真是乱得很啊，现在我已经上传了附件了，谢谢管理。

作者: pureshadow 时间: 2009-10-22 15:50
一个单元格里的字符超过256了，还是分列后计算然后再合并吧
数据》分列》分隔符号
冒号有现成的，分号没有，就在其他里填上。

作者: botong 时间: 2009-10-26 17:04
4# pureshadow
你说的倒是提供了一个思路，可是分列后如何再合并回来呢？我不会啊。具体怎么操作能不能详细说一下啊？
另外为什么超过256个字节就要分列了呢？

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)