☰

如何根据可变数量的条件从 Excel 表中检索数据？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 database pivot-table
如何根据可变数量的条件从 Excel 表中检索数据？

例如我有以下薪资数据：

Country  State     2012  2013 -> 2027
=======  =====     ====  ====
China    Other     1000  1100
China    Shanghai  1310  1400
China    Tianjin   1450  1500
India    Orissa    1500  1600

因此现在我希望在另一个 Excel 表中找到以下问题之一的答案：

  1. 2013年上海的工资是多少？（答案是1400）
  2. 2012年湖北省工资是多少？（由于没有列出，因此使用“其他”-1000）
  3. 2013年中国的平均工资是多少？（答案是1333）
  4. 2012年中国最高薪资是多少？（答案是天津）

因此，按照上述优先顺序，我希望使用某种形式的查询将这些数字放在另一个 Excel 表中。我考虑过数据透视表，但我想知道是否还有其他更好、更有效的方法来做到这一点？

我认为 SQL 适合于此，但我对此并不了解。我更喜欢某些 Excel 功能。此外，如果能就此类查询的适当数据格式提出建议，我将不胜感激。

答案1

您的用例非常适合Excel 的数据库函数。要使用它们，您首先需要逆透视数据，以便每行对应数据中的一个观察值。这意味着您将有 4 列：Country、State和。YearSalary

然后，您可以使用 DGET() 获取数据库中的特定条目，并使用 DAVERAGE() 获取平均值。数据库函数的条件需要以单元格对的形式排列，其中过滤字段位于顶部，要过滤的值位于其下方。这是一个工作示例：

在此处输入图片描述

单元格 I10 的公式与 I9 的公式相同，只是它使用了 DAVERAGE。

  • 在I9输入=IFERROR(DGET($B$4:$E$12,$E$4,$G$4:$I$5),"")
  • 在I10输入=IFERROR(DAVERAGE($B$4:$E$12,$E$4,$G$4:$I$5),"")

它的周围IFERROR()确保当您没有在第 6 行的所有三个输入中提供值时，DGET 不会显示 #NUM!。这是因为如果您没有告诉它要筛选哪一年，它就无法找到薪水的单个值。

答案2

我认为一种方法是命名列 - 例如，右键单击值为 1400（2013 年上海的工资）的单元格，然后define name

然后，在另一个单元格中或仅引用该单元格 - 这样做的好处是，即使该单元格被移动，它仍将保留您想要的内容。

答案3

经过一些准备，这也可以在 Excel 中轻松完成。（尽管使用 SQL 可能更容易、更自然）。

准备：按照@Adam 建议的那样使用命名范围 -country针对国家、state针对州，并且我也以这种格式命名了每个年份year2012。

然后你就可以创建你的答题纸了

     A              B         C         D
1 salary in     Shanghai    2013    =IFERROR(INDEX(INDIRECT("year"&C1),MATCH(B1,state,0)),INDEX(INDIRECT("year"&C1),MATCH("Other",state,0)))
2 salary in     Hubei       2012    =IFERROR(INDEX(INDIRECT("year"&C2),MATCH(B2,state,0)),INDEX(INDIRECT("year"&C2),MATCH("Other",state,0)))
3 average       China       2013    =AVERAGEIF(country,B3,INDIRECT("year" & C3))
4 highest       China       2012    {=INDEX(state,MATCH(MAX(IF(country=B4,INDIRECT("year"&C4))),INDIRECT("year"&C4),0))}

CTRL+SHIFT+ENTER每一行都是动态的，因此您可以根据需要更改“问题”。需要注意的重要一点是，第四个（最高）公式是数组公式，输入时需要使用。

ps：你第三个问题的答案是错误的，2013年中国的平均工资应该是1333。

答案4

尝试一下这个 vba 方法，它应该不需要太多调整就可以工作。不过，错误处理可以更新：

Private Sub get_money(byval country as string, byval city as string, byval year as string)
    if country == "" then country == "nope" end if
    if city == "" then city =="nope" end if
    if year == "" then year =="nope" end if
    if autofiltermode = true then autofiltermode = false

    dim all_columns as double
    all_columns = thisworkbook.activesheet.range("A1").currentregion.columns.count
    with thisworkbook.activesheet
        for cell_position = 3 to all_columns
            if .cells(1,cell_position) == year
                year_to_filter = cell_position
                cell_position = all_columns
            end if
        next cell_position
        .Range(.cells(1,1), .cells(1,all_columns)).AutoFilter Field:=1, Criteria1:=country, Operator:=xlFilterValues
        .Range(.cells(1,1), .cells(1,all_columns)).AutoFilter Field:=2, Criteria1:=city, Operator:=xlFilterValues
        .Range(.cells(1,1), .cells(1,all_columns)).AutoFilter Field:=cell_position, Criteria1:=year, Operator:=xlFilterValues 
    end with      
end sub

相关内容