У меня возникли проблемы с поиском решения, которое позволило бы мне скопировать ту же формулу, но увеличить количество строк массива таблицы на 15. В своей первой попытке я просто попытался вручную создать три соответствующие формулы, а затем всю ручную формулу в надежде, что она уловит закономерность, но мне это не удалось.
Я ищу такой вывод:
=MAX(VLOOKUP($A2,Sheet2!$A2:$B17,2,0),VLOOKUP($A2,Sheet2!$E2:$F17,2,0),VLOOKUP($A2,Sheet2!$I2:$J17,2,0))
=MAX(VLOOKUP($A2,Sheet2!$A17:$B32,2,0),VLOOKUP($A2,Sheet2!$E17:$F32,2,0),VLOOKUP($A2,Sheet2!$I17:$J32,2,0))
=MAX(VLOOKUP($A2,Sheet2!$A32:$B47,2,0),VLOOKUP($A2,Sheet2!$E32:$F47,2,0),VLOOKUP($A2,Sheet2!$I32:$J47,2,0))
Я в этом деле новичок, поэтому любые советы приветствуются. Заранее спасибо.
решение1
OFFSETбыла бы функцией, к которой большинство людей тяготели бы для этого варианта. однако это изменчивая функция. Изменчивые функции — это не конец света. Это просто означает, что они пересчитываются каждый раз, когда изменяется ячейка на листе, даже если эта ячейка не имеет отношения к изменчивой формуле. Обычные формулы пересчитываются только в том случае, если что-то, что на них влияет, изменяется. В результате вы можете вызвать много лишних вычислений, используя изменчивые функции. Особенно если это что-то скопировано и повторно используется во многих ячейках.
Те же результаты OFFSETмогут быть достигнуты с помощью INDEX, которая является обычной формулой (неизменяемой). INDEXдовольно часто предполагается, что возвращает значение в заданной строке (одномерный диапазон) и столбце (двумерный диапазон). Что INDEXна самом деле возвращается в адресе ячейки, который затем извлекает значение из этого адреса. Используя этот возврат адреса, вы можете определить начальную точку диапазона, используя одну INDEXфункцию, а затем используя другую INDEXфункцию, чтобы определить конец диапазона. Разделите две INDEXфункции с помощью :, и теперь у вас есть диапазон!
Теперь давайте рассмотрим математику определения шаблона строки. Формула 1 начинается в строке 2, Формула 2 начинается в строке 17, Формула 3 начинается в строке 32... и так далее. Таким образом, шаблон здесь по сути (Формула №-1)*15+2. Так что теперь нам просто нужно разработать счетчик, который увеличивается на единицу по мере копирования формулы вниз. Вы можете заполнить столбец числами 1, 2, 3 и т. д. или вместо этого вы можете просто использовать ROW(A1). При первом использовании в любом месте он вернет 1. При копировании вниз он вернет 2, затем 3 и т. д. Идеальный маленький счетчик. (Примечание: Column(A1)может использоваться для подсчета по горизонтали).
Как было сказано ранее, INDEXпринимает форму:
INDEX(SELECT RANGE, ROW in SELECT RANGE, COLUMN in SELECT RANGE)
Пара замечаний:
Номер строки и столбца относятся к выбранному диапазону и не совпадают с рабочим листом, если только выбранный диапазон не начинается с ячейки A1 для 2D-диапазона или со строки 1 или столбца A для 1D-диапазона.
Номер столбца не требуется в 1D выбранном диапазоне, только номер строки. Если выбранный диапазон горизонтальный, номер строки фактически является номером столбца.
Если в качестве номера строки или столбца введено значение 0, то функция INDEX воспринимает его как возвращающий введенную строку или столбец выбранного диапазона.
Давайте вернемся к построению формулы. Давайте начнем с поиска начальной точки диапазона. Так что в этом случае мы хотим сказать index найти A2, A17, A32 и т.д.
=INDEX($A:$A,(ROW(A1)-1)*15+2)
А чтобы найти конечную точку диапазона B17, B32, B47, формула будет выглядеть примерно так:
=INDEX($B:$B,ROW(A1)*15+2)
Теперь объединив эти два значения для определения вашего диапазона, формула будет выглядеть так:
=INDEX($A:$A,(ROW(A1)-1)*15+2):INDEX($B:$B,ROW(A1)*15+2)
Теперь эта формула сама по себе не будет выглядеть много, так как вы не можете поместить больше, чем значение одной ячейки в одну ячейку. Однако она будет работать для ваших формул поиска. Таким образом, подставляя уравнение диапазона в вашу исходную формулу, вы получите:
=MAX(VLOOKUP($A2,INDEX(SHEET2!A:A,(ROW(A1)-1)*15+2):INDEX(SHEET2!B:B,ROW(A1)*15+2),2,0),VLOOKUP($A2,INDEX(SHEET2!E:E,(ROW(A1)-1)*15+2):INDEX(SHEET2!F:F,ROW(A1)*15+2),2,0),VLOOKUP($A2,INDEX(SHEET2!I:I,(ROW(A1)-1)*15+2):INDEX(SHEET2!J:J,ROW(A1)*15+2),2,0))