需要实现的效果如图:如果是A类,取规格里的最大数,比如30*20取30,所对应数据源里的30~33的价格是红色部分,然后在单价一栏自动按型号填写22/mm,并且自动算总价,总价是(30*单价*数量)如果是B类,数量所对应的范围值列就是对应的价格,比如300,在101~500,则对应的价格是绿色部分,总价就是(数量*单价)如果是C类,则对应指定规格,比如40*60所对应的价格是蓝色部分,和上面一样,也是自动输入单价和总价如果是D类,计算规格里*两边数值的积,比如2*4,得出值是8,8的范围在6~10,则对应价格是紫色部分,如果值小于1,则按照1计算,总价就是2*4*数量*单价数据在SHEET3,需要引用的表格在SHEET1
根据你的需求,可以使用Excel的VLOOKUP函数实现。具体步骤如下:
在SHEET1中,根据输入的A/B/C/D类别,使用VLOOKUP函数查找对应的规格范围、数量范围和单价。例如,对于A类别,可以使用以下公式:
=VLOOKUP(A2,Sheet3!$A$2:$D$7,2,TRUE)
其中,A2是输入的类别,Sheet3!$A$2:$D$7是存储数据的表格,2表示返回第2列(即规格范围),TRUE表示使用近似匹配。
对于数量范围和单价,可以类似地使用VLOOKUP函数查找。
根据规格范围和数量范围,使用IF函数和AND函数判断应该使用哪种计算方式。例如,对于A类别,可以使用以下公式:
=IF(AND(B2>=30,B2<=33),22/C2*D2*E2,B2*D2*C2)
其中,B2是输入的规格范围,C2是输入的数量,D2是查找到的单价,E2是规格范围中的最大值。如果规格范围在30到33之间,则使用第一种计算方式;否则,使用第二种计算方式。
根据计算结果,自动填写总价。例如,在A类别中,可以使用以下公式:
=IF(AND(B2>=30,B2<=33),B2*D2*C2*E2,IF(AND(B2>=101,B2<=500),B2*D2,C2*D2*B2*IF(F2<1,1,F2)))
其中,F2是规格范围两边数值的积,IF函数用于判断积是否小于1,并按照1计算总价。其他类别的计算方式也可以类似地使用IF函数实现。
注意,以上公式中的表格和列名可能需要根据实际情况进行调整。
首先在Excel中打开SHEET3,找到需要计算价格和总价的数据范围,例如A2:B10。
在一个空白的单元格中输入公式“=A2*B2”,其中A2和B2分别代表需要计算的两个数值。按下回车键,Excel会自动计算出这两个数值的积。
选中刚刚计算出的单元格,将鼠标移动到单元格右下角的小黑点上,鼠标变成十字箭头后点击并拖动,直到覆盖需要计算的所有单元格。Excel会自动将公式复制到所有选中的单元格中,并计算出对应单元格的积。
在计算出所有积之后,在一个空白的单元格中输入公式“=IF(积<1,1,
积)”,按下回车键进行计算。该公式的含义是,如果积小于1,则将积的值设为1,否则保留积的原始值。这一步是为了防止在后续的计算中出现价格为0的情况,确保计算结果的正确性。
接下来,需要将得到的积的值与价格范围进行匹配,以确定对应的价格。可以使用Excel的“VLOOKUP”函数来实现这一功能。具体操作步骤如下:
在一个空白的单元格中输入公式“=VLOOKUP(积,Sheet1!A:B,2,TRUE)”,按下回车键进行计算。其中,“积”是前面计算得到的积的单元格地址,“Sheet1!A:B”是数据源的范围,“2”表示需要返回的列数(即价格所在的列),“TRUE”表示需要进行近似匹配。
Excel会根据积的值在数据源中进行查找,并返回对应的价格。如果积的值在数据源中找不到对应的价格范围,则会返回最接近的价格范围的价格。
最后,需要计算出每个物品的总价。可以在每个物品的单元格中输入公式“=规格数量单价”,按下回车键进行计算。其中,“规格”、“数量”和“单价”是对应物品的单元格地址。
在计算出所有物品的总价之后,可以对结果进行格式化和调整,以便更好地展示和使用。例如,可以将价格格式设置为货币格式,并对表格中的数字进行对齐和合并单元格等操作。
以上就是详细的操作步骤,希望可以对您有所帮助。
在Excel中,可以使用多种方法来实现你要求的功能。以下是一种可能的实现方式:
首先,在SHEET3中创建一个表格,用来输入A/B/C/D类别、规格、数量等信息,并计算总价。假设表格数据从第2行开始,列分别为A/B/C/D、规格、数量、单价、总价,则公式如下:
其中,INDEX和MATCH函数用于根据规格查找对应价格,GCD函数用于计算最大公约数。
单价:=IF(A2="A", INDEX(SHEET1!�B2:�E5, MATCH(B2, SHEET1!�A2:�A5, 1), 1), IF(A2="B", INDEX(SHEET1!�B2:�E5, MATCH(C2, SHEET1!�A2:�A5, 1), 2), IF(A2="C", INDEX(SHEET1!�B2:�E5, MATCH(B2&"*"&C2, SHEET1!�C2:�C5, 0), 3), IF(A2="D", MAX(1, GCD(B2, C2))*INDEX(SHEET1!�B2:�E5, MATCH(MAX(1, GCD(B2, C2)), SHEET1!�F2:�F5, 1), 4), "")))
总价:=IF(ISNUMBER(D2)ISNUMBER(E2), D2E2*C2, "")
在SHEET1中创建一个表格,用来输入不同规格的价格信息。假设表格数据从第2行开始,列分别为规格、101500、3033、40*60、6~10,则按照需要填写每个规格对应的价格。
注意,以上仅是一种可能的实现方式,具体实现还需要根据实际情况进行调整和优化。