Как я могу получить данные из таблицы Excel на основе переменного числа критериев?

Question

У меня есть следующие данные о зарплате, например:

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 гораздо предпочтительнее. Также были бы оценены предложения относительно соответствующего формата данных для таких запросов.

Answer 1

Ваш вариант использования идеально подходит для функций базы данных Excel. Чтобы их использовать, вам сначала нужно отменить вывод данных, чтобы каждая строка соответствовала одному наблюдению в ваших данных. Это означает, что у вас будет 4 колонки: Country , State , Year и Salary .

Затем вы можете использовать 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() вокруг него гарантирует, что DGET не показывает #NUM! когда вы не указали значения во всех трех входах в строке 6. Это потому, что он не может найти единственное значение для зарплаты, если, скажем, вы не указываете, какой год фильтровать.

Answer 2

Я согласен с Дейвом Рук, что именно здесь вы должны использовать SQL.

Это также можно сделать полностью с формулами Excel (что не так?). Вот как:

Прежде всего вам понадобится способ адресации каждого столбца. У вас есть два варианта для этого.

1. Во-первых, (как предположил Дейв Рук), более простым и красивым, является определение именованных диапазонов. Просто убедитесь, что каждый именованный диапазон достаточно большой, чтобы содержать все будущие записи (или использовать какой-то динамический именованный диапазон). К сожалению, для каждого столбца требуется щелчок, поэтому он не масштабируется при наличии 100 столбцов.

2. Другой вариант (который я предпочитаю для сложных вычислений) - генерировать строки адресов диапазона столбцов, используя, например, =ADDRESS(ROW(C3);COLUMN(C3);4;;"sheet1") & ":" & ADDRESS(ROW(C3)-1+$A$2;COLUMN(C3);4) для доступа к данным в столбце C с количеством записей, хранящихся в ячейке A2 . Затем вы обращаетесь к диапазону с помощью формулы INDIRECT() везде, когда вы обычно вставляете именованный диапазон из метода 1.

После того, как все ваши столбцы (поля) будут названы диапазонами, вы сможете выполнять на них различный поиск и ссылочные задачи.

Например: для определения номера записи, содержащей слово "Shanghai", вы должны использовать =MATCH("Shanghai";State;0) , где State - это именованный диапазон для столбца "State".

Получив номер записи (скажем, он находится в ячейке X10 , вы можете получить информацию о зарплате с помощью =INDEX(Salary;X10) .

Все виды задач возможны в формулах Excel; Например, вы можете создать список уникальных значений (или значений, которые соответствуют определенным критериям), вы можете вычислить любую форму суммы / среднего. Вы даже можете отсортировать их (да!).

Обратите внимание, что в моем регионе я использую точку с запятой ; в качестве разделителя аргументов (не запятая). Англоязычные страны обычно используют , и в этом случае, чтобы использовать мои формулы, вам нужно будет заменить все ; ->.

Answer 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))}

Каждая из этих строк является динамичной, поэтому вы можете изменить свои «вопросы» по мере необходимости. Важно отметить, что 4-ая (самая высокая) формула - это формула массива, которая требует использования CTRL+SHIFT+ENTER при ее вводе.

п.с. Ваш ответ на 3-й вопрос неверный, средняя зарплата в Китае в 2013 году должна составить 1333.

Answer 4

Перейти на этот vba aproach, он должен работать без особых настроек. Обработка ошибок может быть обновлена, хотя:

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

Answer 5

Я думаю, что один из способов - назвать столбец. Например, щелкните правой кнопкой мыши ячейку со значением 1400 (зарплата в Шанхае за 2013 год) и define name

Затем в другой ячейке или просто обратитесь к этой ячейке - прелесть в том, что не имеет значения, если эта ячейка будет перемещена, она останется с тем содержимым, которое вы хотите.