excel - 如何对元胞数组的每个元素应用数学计算(如在 Excel 中)

Question

friend 们，

我有两个以下单元格数组(或两个 Excel 表，如有必要)。
Bodies 的元胞数组

[Body]     [Weigh]  [warranty]
[A1]        [3.5]     [2]
[A2]        [6.2]     [3]
[B1]        [7.1]     [1]
[B2]        [3.9]     [4]
[B3]        [4.2]     [5]
[C1]        [1.3]     [7]
...         ....     ...

部分 Objects 的元胞数组在每个 Body

   [Object] [x_min] [x_max] [y_min] [y_max] [Volume]
   [A1-a1]   [5.7]   [7.3]   [8.9]   [4.3]   [5.1]
   [A1-a2]   [2.6]   [5.6]   [9.3]   [5.7]   [5.2]
   [A1-a3]   [3.6]   [7.3]   [5.3]   [7.3]   [5.8]
   [A2-a1]   [8,2]   [5.8]   [2.7]   [5.3]   [6.3]
   [A2-a2]   [8.4]   [6.3]   [8.5]   [6.3]   [9.3]
   [B1-b1]   [7.1]   [6.3]   [8.2]   [8.5]   [5.3]
   [B1-b2]   [8.9]   [8.4]   [4.5]   [6.2]   [4.5]
   [C1-c1]   [7.0]   [7.1]   [1.3]   [8.9]   [1.3]
   [C1-c2]   [6.9]   [4.8]   [3.2]   [9.2]   [3.7]
   [C1-c3]   [5.3]   [2.5]   [4.2]   [6.4]   [6.3]
    ...       ...     ...     ...     ...     ...

我想编写一个程序，它自动对对象执行以下步骤:

根据公式计算每个物体的重量:

Weigh_obj = Weigh_body * Volume_obj / Sum of every Volume_obj in the body

例如

Weigh_A1-a1 = Weigh_A1 * Volume_A1-a1 / (Volume_A1-a1 + Volume_A1-a2 + Volume_A1-a3)
            = 3.5 * 5.1 / (5.1+5.2+5.8)

将每个对象的坐标与其他对象的坐标进行比较，找出是否存在 Touch任意两个对象之间:

例如，有两个 Objects Object1和 Object2 :

if  ( (x2_min <=x1_min<=x2_max)or(x2_min <=x1_max<=x2_max)...
   and (y2_min <=y1_min<=y2_max)or(y2_min <=y1_max<=y2_max) )

    % returns '1' in corresponding positions in the square matrix of `n` objects, like this: 

    [ X]  [O1] [O2] [O3] ... [O_n]
    [O1]  [X]  [1]  [ ]  ... 
    [O2]  [1]  [X]  [ ]  ... 
    [O3]  [ ]  [ ]  [X]  ... 
    ...   ...  ...  ...  [X]
    [On]  ...  ...  ...  ... [X]  


 else
     returns '0' in the corresponding Positions in the matrix
 end

这样最后我得到一个完整的 Object -矩阵

我希望我已经足够清楚地解释了我的问题。

提前致谢!

非常感谢你的帮助 !

对于 1) 步骤:碰撞检测:我想检测第二个表中任意两个对象之间的碰撞。即不仅 A1-a1 与 A1-a2...，而且 A1-a1 与 B1-b1...、C1-c1... 等等。之后我想建立一个这样的矩阵

[ X]      [A1-a11] [A1-a2] [A1-a3] ...  [B1-b1] [B1-b2] [C1-c1]
[A1-a1]   [X]       [ ]     [ ]   ...      [ ]    [ ]     [ ] 
[A1-a2]   [ ]       [X]     [ ]   ... 
[A1-a3]   [ ]       [ ]      [X]   ... 
...       ...       ...      ...   [X]
[B1-b1]   ...       ...      ...   ...      [X]  
[B1-b2]
[C1-c1]   ...       ...      ...   ...      ..     ...     [X]

并用碰撞检测的结果填充矩阵。你有什么想法吗？

对于 2) 步骤，您已经完全符合我的需要。但是在这里我们没有找到物体的密度。我们只是找到物体的重量。因为您使用公式计算了“比容”:

    [object volume / total body volume] = [m^3 / m^3] = [1] (no unit, just a quote)

最后 [ 体重 * specificVolumes]=[kg * 1] = [kg] = [重量] 物体

问候 !

Answer 1

如果我理解正确，您想要进行碰撞检测并计算单个对象的密度。

信不信由你，碰撞检测比体积计算容易得多。这主要是由于您的数据是如何组织的。

这是我得出的结论:

% [Body]     [Weight]  [warranty]
Bodies = {...
    'A1'      3.5      2
    'A2'      6.2      3
    'B1'      7.1      1
    'B2'      3.9      4
    'B3'      4.2      5
    'C1'      1.3      7
};

%   [Object]  [x_min] [x_max] [y_min] [y_max] [Volume]
Objects = {...
    'A1-a1'   5.7     7.3     8.9     4.3     5.1
    'A1-a2'   2.6     5.6     9.3     5.7     5.2
    'A1-a3'   3.6     7.3     5.3     7.3     5.8
    'A2-a1'   8.2     5.8     2.7     5.3     6.3
    'A2-a2'   8.4     6.3     8.5     6.3     9.3
    'B1-b1'   7.1     6.3     8.2     8.5     5.3
    'B1-b2'   8.9     8.4     4.5     6.2     4.5
    'C1-c1'   7.0     7.1     1.3     8.9     1.3
    'C1-c2'   6.9     4.8     3.2     9.2     3.7
    'C1-c3'   5.3     2.5     4.2     6.4     6.3
};

% Rename variables for clarity
BodyNames = Bodies(:,1);
Weights =  cat(1, Bodies{:,2});

ObjectNames = Objects(:,1);
x_min  = [Objects{:,2}].';
x_Max  = [Objects{:,3}].';
y_min  = [Objects{:,4}].';
y_Max  = [Objects{:,5}].';
Volume = [Objects{:,6}].';

% Find densities
% --------------------

% find which objects belong to which bodies
ObjInds = cellfun(@(x) regexp(ObjectNames, x), BodyNames, 'UniformOutput', false);
ObjInds = cellfun(@(x) ~cellfun('isempty', x), ObjInds, 'UniformOutput', false);

% Compute the specific volumes (object volume / total body volume)
specificVolumes = cellfun(@(x) Volume(x) ./ sum(Volume(x)), ObjInds, 'UniformOutput', false);

% Compute densities (= body weight * specificVolumes)
densities = cellfun(@(x,y)x.*y, num2cell(Weights), specificVolumes, 'UniformOutput', false);
densities = cat(1, densities{:});


% Collsion detection 
% --------------------

% This is it: 
Colissions = ...
    bsxfun(@le, x_min, x_Max') & bsxfun(@ge, x_min, x_min') & ...
    bsxfun(@le, y_min, y_Max') & bsxfun(@ge, y_min, y_min');

现在，上面的碰撞检测会给出一些“奇怪”的结果。这是因为您的一些 x_min > x_max还有一些 y_min > y_max .这很容易用 sort() 纠正。或类似的，但我会留给你。