[求助]Excel 问题：如何取得第 n 个值的位置 (真正已解决)

BillyG

[求助]Excel 问题：如何取得第 n 个值的位置

I have 3 batchs

"Batch-1"
"Batch-2"
"Batch-3"


"Batch-1" consists of item 1st ~ 85th. ( "Batch-1" = 85)
"Batch-2" consists of item 86th~118th. ("Batch-2" = 33)
"Batch-3" Consists of item 119~218th. ("Batch-3" = 100)

at Cell E5, I keyin a value 100 (which can be view as the 100th),
by human eye we can tell that 100th is fall on "Batch-2".

On Excel, how can i made it return "Batch-2" on Cell E6 for the value i keyin in Cell E5?

Thank you

[ 本帖最后由 BillyG 于 25-10-2008 05:02 PM 编辑 ]

BillyG

对不起，可能我没有表达清楚。

1. "Batch-X"并不止限于3个,可能多达一千或一万个.
2. 每个"Batch-X"的上限与下限并非固定的.
3. 我可以把所有"Batch-X"的数目相加得到一个总数.
4. 在 loop 里我能用一个一个慢慢加到指定的数目来再return位置.
5. 我只知道可能需要下面的一些东西.
i. sum(), row(),

问题出在无法好象 loop 里那样 做 increment smming 然后达到条件时立即停止在那位置. sum(), sumif() 多数 excel function 是整个 column 算完的.

刚才想到用一个 column 算 increment sum 可是当我在 E6 打 formula 时发现 excel 还是一次过算完.


有人有办法吗?


谢谢

[ 本帖最后由 BillyG 于 2-10-2008 11:15 PM 编辑 ]

kiam06

原帖由 BillyG 于 2-10-2008 10:56 PM 发表
对不起，可能我没有表达清楚。

1. "Batch-X"并不止限于3个,可能多达一千或一万个.
2. 每个"Batch-X"的上限与下限并非固定的.
3. 我可以把所有"Batch-X"的数目相加得到一个总数.
4. 在 loop 里我能用一个一个慢 ...

不是很清楚你的用处，可以说明些会更好，
如：
1. "Batch-X"并不止限于3个,可能多达一千或一万个.
-那么你的batch-X以后会一直增加吗？

evo9

可以尝试用vlookup 的function

BillyG

原帖由 kiam06 于 3-10-2008 03:28 PM 发表
不是很清楚你的用处，可以说明些会更好，
如：
1. "Batch-X"并不止限于3个,可能多达一千或一万个.
-那么你的batch-X以后会一直增加吗？

对 会一直增加下去。

原帖由 evo9 于 4-10-2008 11:40 AM 发表
可以尝试用vlookup 的function

Vlookup 我有用。可是单单基本的 vlookup 做不到我需要的功能。 必须在 vlookup 里 结合 其他 function 如 if 或 sum.
可是我抓破脑袋也想不到该如何 结合 if/sum.

回复两位，

我想计算一些跟 inventory 有关的数据。
1. "Batxh-X" <-- 是 invoice/DO 号码。
2. 每个 "Batch" 或 invoice/DO 都 有 数量(购买数量)
3. 这些 invoice/DO 跟数量都会被输入一个 table(表) 里，所以会一直增加。

我想要下面这个功能。
4. 当我在一个 cell 输入一个数字(库存量/以经使用的量)，excel 便会在旁边的 cell 显示 这个"以经使用的量"是由那一个 batch 贡献的(base on FIFO)，既显示那个/张 invoice/DO 号码。

也就是 "我已使用到那一张 invoice 的货"

example :
batch-1 = 100  '买100个货 －＞ 库存 100
batch-2 = 150  '买150个货 －＞ 库存 250
bacth-3 = 75   '买75个货  －＞ 库存 325
当库存 = 220, 已经使用掉 = 105, 既是 batch-1 的货已经用完。正在用着 batch-2 的货。
当库存 = 70, 已经使用掉 = 255, 既是 batch-1 & batch-2 的货已经用完。正在用着 batch-3 的货。

--------------------------------
我面对的问题是

如果能做到 有如 银行 bankbook 里的 increment sum/balance 便能弄到我要的功能。

在 excel function formula 里 我不能做到 increment sum.

我试过了 增加 一个 column 在新 column 里 做 increment summing. 可是当我要比对 increment sum 和我输入的 useage/balance 时 我想不到 该怎么比对。

我总觉得 excel 能弄到， 或许我一直在钻牛角尖。。。

[ 本帖最后由 BillyG 于 4-10-2008 09:18 PM 编辑 ]

evo9

能有一个sample 的excel sheet 吗？很难想象下...

kiam06

原帖由 evo9 于 5-10-2008 02:33 AM 发表
能有一个sample 的excel sheet 吗？很难想象下...

对，希望楼主可以提供一个sample，manual弄出来就可以了！

BillyG

我不会在 cari 打格子只好用 图片 连接。

Also previous reply i type "increment summing" not accurate. should be "accumulate sum".



[ 本帖最后由 BillyG 于 5-10-2008 02:09 PM 编辑 ]

BillyG

最接近我要的应该是下面这个

http://www.me.utexas.edu/~jensen/ORMM/computation/unit/do_yourself/ormm_functions.html

MatchIF(A, k, B, m): for the set of entries in the array A that are equal to k, find the index of the entry in array B whose value is the same as m. Return the corresponding index of B.


MS Excel/OO Cals 有这个功能吗？上面是我 google 到的。可是 Excel/Cals 里 好相没有这个功能。

evo9

请问下这个是不是你要的output?
当你key in stock balance 的时候，它帮你算你用着那个batch 的货对吗？

[ 本帖最后由 evo9 于 11-10-2008 02:59 PM 编辑 ]

BillyG

是的，应该是了。
能让我看看你的 formula/function 吗？
我要看你最下面 Cell B15 和 Cell C14 的 formula/functon.

谢谢。

BillyG

给我试到了
结合 index, Match, If 和 array fomular.

INDEX(Range/Array,Row,Column)
Match(Value,Array,Match_type)
If(logic,True,False)
--------------------------------------
{=Index(Array,Match(Value,IF(Array logic,Array,False),Match_type),Column)}
--------------------------------------
{=Index("Batchs/Invoices Array",Match("used Qty",If("stock ID array"="stock ID of used stock","Accumulate total array", False),1),1)}

大大有没有更好的 formula 或能帮帮我看看我的 formula 有改进的地方吗？

谢谢

BillyG

我 13 楼的 formula 理论上是可行，但是实践上 (practically) 受限制于 match function 的 match_type(1,0,-1),以至于当 usage 低于第一个 accumulate qty 时会回复/得到 #N/A 或是第一个 accumulate qty 必须是 0 (零) 才行。

有高手会/能弄出一 能代替 match function 的 fanction 吗？

或

有高手能帮我看看要如何加 if 在我 13 楼的 formula 吗？
if function 有不能比对 array 的问题/限制,在特定条件下才能做 array 比对.

谢谢。

BillyG

(解决了)

我用我 13 楼的 formula 再外加 if() 和 isna() 处理掉 #N/A 的问题。

谢谢三位的回复。

BillyG

真正解决了。
之前虽然试到了，但并没有真正解决我的问题。
下面这个是我今天刚刚试到的。
贴上来希望能帮到其他跟我有一样问题的人。

formula 如下:

{=Index(BatchArray,Match(1,(StockToMatch = StocksPurchaseListArray)*(UsageQty < PurchasedAccumulatedQty),0),1)}

再加上 if(isna()) 屏除当 balance = 0 (usage = total purchase) 和当 stock 不在 StocksPurchaseListArray 里时的 "#N/A" error。

谢谢。