☰

Хочу использовать функцию ГПР, чтобы перетащить вниз весь столбец. Как это сделать?

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
Хочу использовать функцию ГПР, чтобы перетащить вниз весь столбец. Как это сделать?

Смотрите следующий снимок экрана:

введите описание изображения здесь

Это всего лишь пример, мой реальный рабочий лист сложнее и содержит сотни строк.

Моя текущая формула — =HLOOKUP(A7,A1:D4,2,0), но эта формула не позволяет мне перетаскивать ее вниз по столбцу, чтобы я мог получить "2-янв-12" и "3-янв-12" для Тома тоже. Как мне изменить формулу, чтобы она подходила для перетаскивания вниз?

Обновление: я пробовал =HLOOKUP($A$7,$A$1:$D$4,2,0), но при перетаскивании вниз теперь получаю 5 для "2-янв-12" и "3-янв-12". Я хочу перетащить формулу вниз так, чтобы "2-янв-12" показывало 6, а "3-янв-12" показывало 7.

решение1

Вам также необходимо обновить 2, чтобы вы могли использовать эту версию в B8, скопированную

=HLOOKUP(A$7,$A$1:$D$4,ROWS(B$8:B8)+1,0)

ROWSФункция увеличивается по мере перетаскивания вниз и изменяет row_index_numзначение HLOOKUPна 3, 4 и т. д.

Примечание: приведенный выше пример на самом деле не сопоставляет даты, он просто получает значение из следующей строки независимо от даты. Чтобы точно сопоставить даты, используйте эту версию.

=INDEX(B$2:D$4,MATCH(A8,A$2:A$4,0),MATCH(A$7,B$1:D$1,0))

решение2

Я не уверен на 100%, что это то, что вы ищете, но чтобы отдельные столбцы или строки не менялись при перетаскивании, добавьте $перед ними:

  • A1всегда будет обновляться в зависимости от направления перетаскивания ячейки.
  • $A1обновит строку только при перетаскивании вверх/вниз.
  • A$1будет обновлять столбец только при перетаскивании влево/вправо.
  • $A$1никогда не изменится, как бы/куда бы вы ни перетаскивали ячейку.

Для обновленного вопроса:

Я проверил следующую формулу для ячейки B8:

=HLOOKUP($A$7,$B$1:$D$4,MATCH($A8,$A$1:$A$4,0),0)

Функция MATCHопределит n-ю строку по дате в первом столбце, а затем выберет правильную строку из первой таблицы с помощью HLOOKUP.

решение3

Вы на правильном пути. Вот формула, которую я использовал:

Hlookup

=HLOOKUP($B$1,$C$1:$E$4,ROW(),FALSE)

HLOOKUPПросматривает ваши столбцы и возвращает первое значение $B$1 (используя $ для сохранения абсолютной ссылки). Диапазон поиска также использует абсолютные значения, поэтому он не «ползет» по мере перетаскивания и заполнения формулы. Функция ROWвозвращает значения в той же строке без необходимости выполнять какие-либо дополнительные вычисления. Я также использовал проверку данных, чтобы заполнить искомое значение в $B$1 одним из ваших существующих искомых значений, что дает вам быстрый раскрывающийся список для работы. Сохранение «целевого» значения в том же макете данных (строки и столбцы), что и ваши исходные данные, упрощает управление всем (в отличие от вашего примера, где оно смещено на несколько строк). Хорошая компоновка данных упрощает почти все в Excel.

решение4

Вам необходимо «исправить» ссылки для массива:=HLOOKUP(A7,$A$1:$D$4,2,0)

Связанный контент