新足迹

标题: 在做一个google sheet，输入孩子年龄，身高，体重，自动列出身高体重BMI的percentile。求大神帮助。数据来自cdc [打印本页]

作者: pmp6 时间: 2024-7-15 08:38
标题: 在做一个google sheet，输入孩子年龄，身高，体重，自动列出身高体重BMI的percentile。求大神帮助。数据来自cdc
本帖最后由 pmp6 于 2024-7-16 10:00 编辑

终于成功啦！！！只要输入身高，体重，测量日期自动显示孩子BMI百分位！！！经过测试，和CDC网站所显示百分比一模一样！！！

在做一个google sheet，输入孩子年龄，身高，体重，自动列出身高体重BMI的percentile。求大神帮助。数据来自cdc

数据如下
https://www.cdc.gov/growthcharts ... iagerev.htm#females

在CDC的官网上测percentile，可以准确的得到一个百分比数字。问题是网站没有提供计算公式，如何在google sheet里实现相同的功能呢？感谢

现在的瓶颈如下：
数据库中的月份和BMI不是线性的。把实际身高带入后，如何准确定位现在的percentile。

比如数据库中
24个月时中等BMI（50%）是16.4234，
24.50个月的时候中等BMI（50%）是16.38804。
24个月时BMI（75%）是17.42746，
24.50个月的时候BMI（75%）是17.38582。

但孩子是24.3个月的时候测的BMI17.3。这个时候这么尽量准确的得出现在孩子的percentile是多少？感谢

下面是BMI growth chart，先递减，后递增
[attach]2509240[/attach]

如果套用forcast函数，结果是一直递增。所以这个函数在这里使用是错误的。
[attach]2509241[/attach]

终于成功啦！！！只要输入身高，体重，测量日期自动显示孩子BMI百分位！！！经过测试，和CDC网站所显示百分比一模一样！！！

作者: zhouhaoaaaaa 时间: 2024-7-15 10:09
不知道google sheet的公式是什么，但是如果是excel, 那就是先用xloookup找到age对应的区间，然后用最简单的线性插值去算出这个age每个percentile的值(黄色那行）。然后用输入的的身高体重算出来的BMII17.3 去新的percentile那行里查找。大概还是在50%到70%之间，而且离70%更近。这个时候也可以接着再用线性插值去算是50%到70%的具体某个值。但是肯定就只是更近似的估算值了。
[attach]2509223[/attach]

作者: pmp6 时间: 2024-7-15 10:51
本帖最后由 pmp6 于 2024-7-15 10:53 编辑

zhouhaoaaaaa 发表于 2024-7-15 10:09
不知道google sheet的公式是什么，但是如果是excel, 那就是先用xloookup找到age对应的区间，然后用最简单的 ...

大神厉害。我用FORECAST函数来进行线性插值计算非整月的百分位数。但是得到的奇怪的数字，不知道哪里出了问题

“=FORECAST(IZ$30,$B33: $HL33,$B$30: $HL$30)”

39.23个月时3%百分位数，上面公式求出来是12.79
明明数值应该在38.5个月（13.75）和39.50个月（13.73）中间

作者: pmp6 时间: 2024-7-15 12:01

zhouhaoaaaaa 发表于 2024-7-15 10:09
不知道google sheet的公式是什么，但是如果是excel, 那就是先用xloookup找到age对应的区间，然后用最简单的 ...

我在想我是不是用错函数了。我其实不需要预测未来的日子（forcast），而是需要定位过去的某一天

作者: tvc889 时间: 2024-7-15 12:38
本帖最后由 tvc889 于 2024-7-15 12:39 编辑

pmp6 发表于 2024-7-15 12:01
我在想我是不是用错函数了。我其实不需要预测未来的日子（forcast），而是需要定位过去的某一天 ...

24个月时中等BMI（50%）是16.4234，
24.50个月的时候中等BMI（50%）是16.38804。
24个月时中等BMI（75%）是17.42746，
24.50个月的时候中等BMI（75%）是17.38582。

但孩子是24.3个月的时候测的BMI17.3。这个时候这么尽量准确的得出现在孩子的percentile是多少？感谢

24个月到24.5月BMI提高17.38582-16.38804=0.99778。
24个月到24.5月BMI percentile提高75-50=25。
也就是说BMI改变0.0399112，BMI percentile改变1。

24.3个月的时候测的BMI17.3，和24个月时中等BMI（50%）的16.4234比较，多了0.91196。
相当于BMI percentile提高0.91196/0.0399112=22.84972639点。
所以24.3个月的percentile是50+22.84972639=72.8。

这东西只能算个大概，没法太准确。

作者: pmp6 时间: 2024-7-15 13:34

tvc889 发表于 2024-7-15 12:38
24个月时中等BMI（50%）是16.4234，
24.50个月的时候中等BMI（50%）是16.38804。
24个月时中等BMI（75%） ...

感谢。你知道google sheet中有什么formula可以完成这个功能吗？谢谢

作者: tvc889 时间: 2024-7-15 14:18
本帖最后由 tvc889 于 2024-7-15 14:21 编辑

pmp6 发表于 2024-7-15 13:34
感谢。你知道google sheet中有什么formula可以完成这个功能吗？谢谢

这个过程的需要几个公式，你一步一步分解后写公式就容易了。不大可能一个公式搞定。

那种所谓的线性回归公式不适合这个。再说，你岂不是要做很多个公式？就像我说得那样最简单的就可以。

作者: pmp6 时间: 2024-7-15 14:24
本帖最后由 pmp6 于 2024-7-15 14:54 编辑

tvc889 发表于 2024-7-15 14:18
这个过程的需要几个公式，你一步一步分解后写公式就容易了。不大可能一个公式搞定。

那种所谓的线性回归 ...

这个大公式做好就一劳永逸了。正在研究中。有点费眼睛

=INDEX($B33:$HL33, MATCH(IO$30,$B$30:$HL$30,1))+(INDEX($C33:$HL33, MATCH(IO$30,$B$30:$HL$30,1))-INDEX($B33:$HL33, MATCH(IO$30,$B$30:$HL$30,1)))*(IO$30-INDEX($B$30:$HL$30, MATCH(IO$30,$B$30:$HL$30,1)))/(INDEX($C$30:$HL$30, MATCH(IO$30,$B$30:$HL$30,1))-INDEX($B$30:$HL$30, MATCH(IO$30,$B$30:$HL$30,1)))

上面这个公式可以算出所有时间点对应的BMI。。。。太费眼睛了。

作者: 小小白虎 时间: 2024-7-15 14:28
本帖最后由小小白虎于 2024-7-15 15:31 编辑

楼主这是要监测细到每个月的数据么？？？

作者: pmp6 时间: 2024-7-15 14:56

小小白虎发表于 2024-7-15 14:28
楼主这是要监测细到每个月的数据么？？？

不是的。有点复杂，请看主贴。纯数学和google sheet函数应用问题。做出来后，可以精确track孩子的发展曲线。

作者: bunnie2020 时间: 2024-7-15 17:01
直接在蓝本本上记录各时间点曲线不就出来了么。

作者: zhouhaoaaaaa 时间: 2024-7-15 19:24

pmp6 发表于 2024-7-15 10:51
大神厉害。我用FORECAST函数来进行线性插值计算非整月的百分位数。但是得到的奇怪的数字，不知道哪里出了 ...

不能用Forecast，这个应该是基于线性回归，所以会有你的数据的问题，这种数据不是线性的，所以没有办法去用一个线性方程去拟合。我说的线性插值感觉和你最后的那个大公式应该差不多。但是所有的这些都是瞎搞。因为。。。。

最后的最后，我刚才去看了下这个网站的功能，它的确会计算出来一个数字显示在那张图片上。里面的算法非常复杂。

算法在这里

https://www.cdc.gov/nccdphp/dnpao/growthcharts/resources/sas.htm

网站里用javascript实现的。估计把算法扒出来对你也有难度。只能用近似的方法随便弄弄了

作者: pmp6 时间: 2024-7-15 20:04

zhouhaoaaaaa 发表于 2024-7-15 19:24
不能用Forecast，这个应该是基于线性回归，所以会有你的数据的问题，这种数据不是线性的，所以没有办法去 ...

因为啥？感谢

作者: pmp6 时间: 2024-7-15 20:06

zhouhaoaaaaa 发表于 2024-7-15 19:24
不能用Forecast，这个应该是基于线性回归，所以会有你的数据的问题，这种数据不是线性的，所以没有办法去 ...

我套用这个公式（从chatgpt里），还是有点错误。正在找原因。

你知道怎么把Java的公式抓出来在google sheet里实现同样的计算吗？感激不尽

作者: rainyfriend 时间: 2024-7-15 20:44

小小白虎发表于 2024-7-15 14:28
楼主这是要监测细到每个月的数据么？？？

他是要监测过去某一天的数据。

作者: pmp6 时间: 2024-7-16 09:58
终于成功啦！！！只要输入身高，体重，测量日期自动显示孩子BMI百分位！！！经过测试，和CDC网站所显示百分比一模一样！！！

欢迎光临新足迹 (https://oursteps.co/bbs/forum.php/bbs/bbs/)