Функция СМЕЩЕНИЕ в Excel замедлит расчет вашей книги. Есть лучшая альтернатива: необычный синтаксис INDEX.
Это нишевый наконечник. Существует удивительно гибкая функция под названием OFFSET. Он гибкий, потому что может указывать на диапазон другого размера, который рассчитывается на лету. На изображении ниже, если кто-то изменит раскрывающееся меню # Qtrs в H1 с 3 на 4, четвертый аргумент OFFSET обеспечит расширение диапазона до четырех столбцов.
Гуру электронных таблиц ненавидят OFFSET, потому что это непостоянная функция. Если вы перейдете в совершенно не связанную ячейку и введете число, все функции СМЕЩЕНИЯ будут вычислены. Даже если эта ячейка не имеет ничего общего с H1 или B2. В большинстве случаев Excel очень осторожен и тратит время только на вычисление ячеек, которые необходимо вычислить. Но как только вы вводите OFFSET, все ячейки OFFSET, а также все, что находится ниже OFFSET, начинает вычисляться после каждого изменения в рабочем листе.
Я судил финал конкурса ModelOff 2013 года в Нью-Йорке, когда несколько моих друзей из Австралии указали на странный обходной путь. В приведенной ниже формуле перед функцией ИНДЕКС стоит двоеточие. Обычно показанная ниже функция ИНДЕКС возвращает 1403 из ячейки D2. Но когда вы помещаете двоеточие с обеих сторон функции ИНДЕКС, она начинает возвращать адрес ячейки D2 вместо содержимого D2. Это дико, что это работает.
Почему это важно? ИНДЕКС не изменчив. Вы получаете все гибкие возможности OFFSET без лишних затрат времени на повторные вычисления.
Впервые я узнал этот совет от Дэна Майо из Fintega. Спасибо Access Analytic за предложение этой функции.
Смотреть видео
Стенограмма видео
Изучите Excel из подкаста, выпуск 2048 -: ИНДЕКС заменит неустойчивое смещение!
Привет, я подкастирую все свои советы из этой книги, нажмите «i» в правом верхнем углу, чтобы перейти к списку воспроизведения!
Хорошо, СМЕЩЕНИЕ - потрясающая функция! СМЕЩЕНИЕ позволяет нам указать ячейку в верхнем левом углу, а затем использовать переменные, чтобы определить оттуда, сколько строк вниз, сколько строк наверху, а затем определить форму, хорошо. Итак, вот, если я хочу сложить или сделать среднее, скажем, 3/4, эта формула прямо здесь будет смотреть на формулу. СМЕЩЕНИЕ говорит, что мы начинаем с B2, начиная с Q1, мы опускаемся на 0, более 0, фигура будет высотой в 1 строку, и это будет H1, другими словами, шириной 3 ячейки, хорошо. Итак, в этом случае все, что мы на самом деле делаем, это меняем количество складываемых ячеек, мы всегда начинаем с B2, или B3, или B4, когда я копирую, а затем он решает, сколько ячеек шириной. Хорошо, OFFSET - это крутая штука, она выполняет всевозможные удивительные функции, но вот хлопот, она непостоянна!Это означает, что даже если что-то не входит в цепочку вычислений, Excel потребуется время, чтобы пересчитать это, что, конечно, замедлит работу.
Эта удивительная версия ИНДЕКСА, верно, обычно, если бы я попросил ИНДЕКС этих 4 ячеек, 3, он вернет число 1403. Однако, когда я ставлю двоеточие до или после ИНДЕКСА, происходит что-то совсем другое, мы рассмотрим эту формулу здесь. Итак, проиндексируйте 4 ячейки, какую из них я хочу, я хочу третью, но вы видите: прямо там. Итак, мы всегда будем переходить от B2: E2, и я покажу вам, если мы перейдем к формулам, вычисляем формулу, хорошо, так что прямо здесь он будет вычислять число 3. А здесь ИНДЕКС с: next к нему, вместо того, чтобы сообщить нам 1403, как обычно вычисляет INDEX, он вернет $ D2, а затем AVERAGE будет делать среднее из этих 3. Абсолютно удивительно, как это работает, и дополнительные преимущества, это не изменчиво, хорошо,и это даже может быть использовано для замены невероятно сложного СМЕЩЕНИЯ.
Итак, здесь с помощью этого СМЕЩЕНИЯ мы основываемся на этих значениях. Если я выберу Q2 и Central, хорошо, эти формулы используют ПОИСКПОЗ и СЧЁТЕСЛИ, чтобы выяснить, сколько строк вниз, сколько столбцов сверху, какая высота, какая ширина. И затем здесь СМЕЩЕНИЕ использует все эти значения для определения медианы. Мы просто проведем тест, чтобы убедиться, что он работает, так что = СРЕДНЯЯ для этого синего диапазона, лучше 71, хорошо, и мы выберем что-то другое здесь, Q3 и Запад, так что. Нажмите F2, я просто перетащу его и изменим размер, чтобы переписать эту формулу, нажмите Enter, и он работает со смещением.
Итак, используя ИНДЕКС, у меня на самом деле есть двоеточие посередине, ИНДЕКС с левой стороны и ИНДЕКС с правой стороны, посмотрите, как это вычисляется в Формуле вычисления. Итак, он начинается, оценивает, оценивает, и прямо здесь ИНДЕКС собирается превратить его в адрес ячейки. Итак, H16 является 1-м, West, Q3, и справа будет оценка, еще пара, а затем справа она изменится на I20. Так что круто, круто энергонезависимо заменить СМЕЩЕНИЕ с помощью функции ИНДЕКС. Хорошо, этот совет и многое другое в этой книге, нажмите на букву «i» в правом верхнем углу, чтобы купить книгу.
Отличное резюме: СМЕЩЕНИЕ, потрясающая, гибкая функция, когда вы освоите, вы сможете делать все, что угодно. Наведите указатель мыши на верхнюю левую ячейку переменной, укажите на диапазон, который имеет переменную форму, но непостоянен, поэтому они вычисляют при каждом вычислении. Excel обычно выполняет интеллектуальные вычисления или пересчитывает ячейки, которые необходимо вычислить, но при использовании OFFSET он всегда вычисляется. Вместо OFFSET вы можете использовать INDEX: или: INDEX или даже INDEX: INDEX, всякий раз, когда INDEX имеет двоеточие рядом с ним, он вернет адрес ячейки вместо значения этой ячейки. А преимущество в том, что INDEX не изменчив!
Хорошо, я хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз на другом сетевом трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2048.xlsm
