Оглавление:
- 1 30 функций Excel за 30 дней: СМЕЩ (OFFSET) — Информационные технологии — Разбираем подробно
- 2 Функция 26: OFFSET (СМЕЩ)
- 3 Пример 1: Находим сумму продаж для выбранного месяца
- 4 Пример 2: Суммируем продажи за выбранные месяцы
- 5 Пример 3: Создаем динамический диапазон, основанный на подсчете
- 6 Пример 4: Суммируем продажи за последние n месяцев
30 функций Excel за 30 дней: СМЕЩ (OFFSET) — Информационные технологии — Разбираем подробно
Вчера в марафоне 30 функций Excel за 30 дней мы заменяли текстовые строки с помощью функции REPLACE (ЗАМЕНИТЬ) и выяснили, что вставлять символы она тоже умеет.
В 26-й день марафона мы будем изучать функцию OFFSET (СМЕЩ). Она возвращает ссылку заданного размера, отстоящую от стартовой ссылки на указанное количество строк и столбцов.
Итак, давайте изучим информацию и примеры применения функции OFFSET (СМЕЩ), а если у Вас есть дополнительные сведения и свои примеры, пожалуйста, делитесь ими в комментариях.
Функция 26: OFFSET (СМЕЩ)
Функция OFFSET (СМЕЩ) возвращает ссылку, смещённую от заданной ссылки на определенное количество строк и столбцов.
Как можно использовать функцию OFFSET (СМЕЩ)?
Функция OFFSET (СМЕЩ) может возвратить ссылку на диапазон, а также работать в сочетании с другими функциями. Используйте её для того, чтобы:
Синтаксис OFFSET (СМЕЩ)
Функция OFFSET (СМЕЩ) имеет вот такой синтаксис:
OFFSET(reference,rows,cols,[height],[width])
СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])
Ловушки OFFSET (СМЕЩ)
Функция OFFSET (СМЕЩ) пересчитывается каждый раз при любом изменении значений на листе Excel. Используя эту функцию во многих формулах, можно сильно замедлить работу. Вместо OFFSET (СМЕЩ) Вы можете использовать функцию INDEX (ИНДЕКС), чтобы возвратить ссылку. INDEX (ИНДЕКС) пересчитывает результат только при изменении своих аргументов.
Пример 1: Находим сумму продаж для выбранного месяца
При помощи функции OFFSET (СМЕЩ) Вы можете возвращать ссылку на диапазон, отталкиваясь от стартовой ссылки. В этом примере мы хотим получить сумму продаж в ячейке G2.
=OFFSET(C1,F2,0,1,1)
=СМЕЩ(C1;F2;0;1;1)
В ячейке H2 находится формула, очень похожая на предыдущую, которая возвращает имя месяца. Единственное отличие – это сдвиг по столбцам: 1 вместо 0.
=OFFSET(C1,F2,1,1,1)
=СМЕЩ(C1;F2;1;1;1)
Замечание: В данном примере аргументы height (высота) и width (ширина) можно не указывать, поскольку нам нужна ссылка того же размера, что и стартовая.
Пример 2: Суммируем продажи за выбранные месяцы
В этом примере функция OFFSET (СМЕЩ) возвращает ссылку на данные о продажах за выбранный месяц, а функция SUM (СУММ) подсчитывает сумму для этого диапазона. В ячейке B10 указан номер выбранного месяца 3, следовательно, результатом будет сумма продаж в марте (Mar).
=SUM(OFFSET(A3:A6,0,B10))
=СУММ(СМЕЩ(A3:A6;0;B10))
Пример 3: Создаем динамический диапазон, основанный на подсчете
Вы можете использовать функцию OFFSET (СМЕЩ), чтобы создать динамический диапазон. В этом примере мы создали именованный диапазон MonthList с такой формулой:
=OFFSET('Ex03'!$C$1,0,0,COUNTA('Ex03'!$C:$C),1)
=СМЕЩ('Ex03'!$C$1;0;0;СЧЁТЗ('Ex03'!$C:$C);1)
Если к списку в столбце C добавить ещё один месяц, он автоматически появится в выпадающем списке в ячейке F2, который использует имя MonthList, как источник данных.
Пример 4: Суммируем продажи за последние n месяцев
В этом заключительном примере OFFSET (СМЕЩ) работает вместе с SUM (СУММ) и COUNT (СЧЁТ), чтобы показать сумму за последние n месяцев. Как только добавляются новые значения, результат формулы будет автоматически скорректирован, чтобы включить величину продаж за последний месяц. В ячейке E2 количество месяцев равно 2, поэтому складываться будут суммы за август (Aug-10) и сентябрь (Sep-10).
=SUM(OFFSET(C2,COUNT(C:C)-E3+1,0,E3,1))
=СУММ(СМЕЩ(C2;СЧЁТ(C:C)-E3+1;0;E3;1))
Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://blog.contextures.com/archives/2011/01/27/30-excel-functions-in-30-days-26-offset/
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel