excel - 在 Excel 中分配具有下限和上限的数据
问题描述
这是一个屏幕截图的链接,其中包含 B 列中使用的公式和一些示例数据
我有一个电子表格,A 列中有 48 行数据
值范围从 0 到 19
这 48 行的平均值 = 8.71
总体标准差 = 3.77
鉴于我知道平均值 (8.71)、std dev (3.77) 和 x(无论 A 列中的内容),我在 B 列中的 excel 中使用了 STANDARDIZE 函数来返回 A 列中每个项目的 Z 分数。
例如(第 2 行)有:
x = 2
z = -1.779
使用 z 值,我想创建一个下 (4) 和上 (24) 边界并计算第三列中的值。
本质上,如果 x = 0(最小值),则 z = -2.3096,columnC = 4(下边界条件)
相反,如果 x = 19(最大值),则 z = 2.9947,columnC = 19(上边界条件)
然后将计算 0 到 19 之间的所有其他值....
有什么想法可以通过 C 列中的公式来完成吗?
解决方案
因此,如果您的最低原始值是0
并且您的最高值是并且您想将它们从to19
重新分配,我们假设两者都是线性的,这意味着:4
24
由于两者都是线性的,我们必须使用这些公式:
我们开发了第一个,c
所以我们得到
并用那个替换c
第二个等式中的,所以我们得到
并将其发展m
为如下
如果我们把这个和上面的第三个等式放在一起,我们得到:
所以我们终于有了 和 的方程m =
,c =
我们可以使用旧下限和新上限的数字来得到:
您可以将这些值与
其中x
is 是 A 列中的旧值,y
是 B 列中的新分布值:
如果您更改边界,则可以进行一些可视化:
非线性解决方案的想法
如果你想4
和24
作为边界和平均值应该是12
解决方案当然不能是线性的。但是您可以使用例如任何其他公式,例如
因此,您可以将这个公式用于 D 列y2
的以下值a
, b
,c
以及计算 D 列的平均值、最小值和最大值y2
。
然后使用求解器:
- 目标是:平均值
$M$15
应该是 12 - 次要条件:(
$M$16 = 4
下边界)和$M$17 = 24
(上边界) - 可变单元格是
a
,b
和c
:$M$11:$M$13
求解器现在将调整 values a
,以便您非常接近您的目标并获得以下结果
b
:c
最小值是4
最大值24
,平均值是几乎12
,这可能是您可以使用数值方法获得的最接近的值。
推荐阅读
- kotlin - SmallRye Reactive Messaging 的 Emitter<>.send 不会通过带有 Quarkus 的 AMQP 代理在 Kotlin 中发送
- aframe - 如何使用 aframe 缩小 360 度图像?
- laravel - 有没有办法设置可选的路线位置部分?
- c# - 不同表之间的数据验证
- javascript - 错误类型错误:无法设置未定义的属性“继续”
- java - 在“AppEntry”类中添加多个控制器
- c# - 如何存储和转换极数?
- android - 如何跨多个屏幕尺寸统一处理触摸事件?
- c# - 使用 IFileProvider 监视目录以获取文件更改
- input - 在 rust 中,当存在默认值时请求输入的好方法是什么,并且空输入应该是可能的?