Excel函数:引用单元格中的数组 [英] Excel function: reference to array in cell

问题描述

我在A1单元格中有一个数组，

I have an array in cell A1 via

A1 = {=G6:J6} = {"aa"."b"."ccc".1} 

现在我想将单元格A1用于B1中的数组公式.基本上B1应该是

Now I want to use the cell A1 for array formula in B1. Basically B1 should be

B1 = SUMPRODUKT((C6:C12)*(B6:B12=G6:J6))

但我不想直接引用G6:J6，而是改用A1.我刚刚尝试过:

But instead of the direct reference to G6:J6 I would like to use A1 instead. I just tried:

B1 = SUMPRODUKT((C6:C12)*(B6:B12=A1))
B1 =  {=SUMPRODUKT((C6:C12)*(B6:B12=A1))}

但这是行不通的.有办法使它起作用吗?

But this would not work. Is there a way to make it work?

问候，彼得

对于出现的问题:

单元格G6:J6是输入数据，例如商品编号.我只想在工作表中设置一次输入数据，所以我必须更新较少的数据.G6:J6中的条目是字符串或数字.假设G6 ="aa"，H6 ="b"，I6 ="ccc"和J6 = 1.

Cells G6:J6 are input data for example article numbers. I want to setup the input data only once in my sheet so I have to update less data. entries in G6:J6 are strings or numbers. Let's say G6 = "aa", H6 = "b", I6 = "ccc" and J6 = 1.

单元格B1是我需要使用数据的地方.宁愿放在另一张纸上，但对于更简单的示例，我们假设它是单元格B1.在B1中，我当然可以引用G6:J6，但这使公式不太容易阅读.因此，我想在B1旁边放置一个参考A1，以便可以轻松了解B1使用的数据.

Cell B1 is one point where I need to use the data. It would rather be in another sheet but for simpler examples let's assume it is cell B1. In B1 I could of course refer to G6:J6 but this makes formular less easy to read. Therefore I would like to put a reference A1 next to B1 so one can see easily what data B1 uses.

C6:C12是一些数字，而B6:B12是一些可能与G6:J6匹配的字符串/数字.因此sumproduct应该将匹配项相加.

C6:C12 is some numbers and B6:B12 is some strings/numbers that maybe match G6:J6. So sumproduct should sumup the matches.

推荐答案

您的单元格A1包含数组公式或数组范围，但仅包含该数组或范围中的单个值(每个Excel单元格只能包含单个结果值).

Your cell A1 contains an array formula or array range but it only contains a single value from that array or range (each Excel cell can only contain a single result value).

因此，您需要用数组或范围表达式替换SUMPRODUCT中的A1.

So you need to replace the A1 in your SUMPRODUCT with an array or range expression.

这篇关于Excel函数:引用单元格中的数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！