gototopgototop

Материалы Excel

Сейчас на сайте

Сейчас 98 гостей онлайн
Диапазон таблицы Excel. Примеры функции «СМЕЩ» в Excel.

Примеры функции «СМЕЩ» в Excel.

      В Excel функция «СМЕЩ» поможет всегда считать последние данные в таблице, если в таблицу постоянно добавляют новые данные. Чтобы понять, для чего функция «СМЕЩ» и как она работает, прочитайте статью «Функция «СМЕЩ» в Excel».
      Здесь разберем примеры функции «СМЕЩ» в Excel. Эта функция автоматически меняет в формуле адрес диапазона таблицы, относительно конкретной ячейки. Помогает настроить таблицу, чтобы считались новые добавленные данные за определенный период. Чтобы в графике отражались последние данные из таблицы за определенный период времени, т.д. Об этом читайте статью "Динамические графики в Excel".
У нас такая таблица.
      Первый пример.
Например, таблица дополняется новыми данными каждый день. Нам нужно посчитать сумму значений только за последние три дня.
В ячейке Е5 пишем такую формулу.   =СУММ(СМЕЩ(A2;СЧЁТЗ(A2:A20)-3;0;3;1))
      Пояснение формулы:
В формуле функции «СУММ» мы указали не конкретные адреса ячеек для сложения (А5, А6, А7), а вложили функцию «СМЕЩ», указав от какой ячейки и что считать.
СМЕЩ(А2 – считать от этой ячейки.
СЧЁТЗ(А2:А20)-3 – считать только заполненные ячейки, не зависимо от того, смежные они или нет. А2:А20 – это диапазон всего столбца. Эта функция будет определять в этом диапазоне заполненные ячейки и три заполненных нижних ячейки (-3) брать для подсчета суммы. Формула «СМЕЩ» переместит стартовую ячейку А2 на ячейку, посчитанную функцией «СЧЁТЗ», возьмет последние три заполненные ячейки для счета из указанного в формуле диапазона (из А2:А20).
«0» - нет смещения по столбцам.
«3» - брать диапазон для счета, состоящий из трех строк (ячеек).
«1» - в диапазоне один столбец.
Получается, нужно посчитать сумму ячеек А5, А6, А7.
      Получится так.
Сумма равна 15.
Но, если добавить в ячейку А8 данные, то формула посчитает сумму трех нижних ячеек – А6, А7, А8.
Примеры функции СМЕЩ в Excel.      Второй пример.
Вместо цифр, указывающих количество строк, столбцов, в формуле функции «СМЕЩ» можно писать адрес ячейки, в которой написано это число. Это дает возможность, выбирать, считать разные диапазоны, меняя число в ячейке. Например, в ячейках столбца F мы написали числа смещения. Вставили в формулу адреса этих ячеек, вместо конкретных чисел. Получилось так.      Если в этой формуле вместо функции «СУММ» (см. первый пример), напишем другую функцию, например, «СРЗНАЧ», то формула посчитает среднее значение в трех последних ячейках.
      Третий пример.
Вместо цифр в формуле функции «СМЕЩ», указывающих количество строк и столбцов, можно написать имя диапазона. Формула получится такая.
=СУММ(СМЕЩ(B2;СЧЁТЗ(_1_кв.)-3;0;3;1))
В формуле написано «_1_кв.» - это имя динамического диапазона столбца, в котором считаются данные в трех последних ячейках.
      В Excel можно посчитать не только конечные итоги, но и промежуточные - по разделам, строкам, т.д. В Excel есть специальная функция, с помощью которой, можно быстро настроить таблицу на подсчет промежуточных итогов. Подробнее про эту функцию читайте в статье "Промежуточные итоги в Excel".