Excel как выделить столбцы до последнего
Не правда ли, было бы замечательно, если бы Excel мог изменять диапазон данных, например, для графиков или диаграмм, при добавлении новых строк или столбцов. На самом деле, возможность создания динамических диапазонов существует, и она реализована с помощью функции СМЕЩ.
Функция СМЕЩ возвращает ссылку на диапазон с определенной отправной точкой с заданной высотой и шириной.
Что это значит? К примеру, у вас есть диапазон данных, который используется для построения диаграммы, но количество строк настолько велико, что диаграмма становиться нечитабельной для пользователей. Выходом из ситуации станет отображение только определенной части данных. Например, пользователям необходимо будет указать последний квартал или год и функция СМЕЩ отправит новый диапазон данных на построение диаграммы.
Перевод статьи с сайта Chandoo
Синтаксис формулы СМЕЩ
Формула СМЕЩ выглядит следующим образом:
=СМЕЩ(ссылка; смещение по строкам; смещение по столбцам; высота; ширина)
- Ссылка: отправная точка, эта ячейка, относительно которой необходимо смещаться
- Смещение по строкам и столбцам: Указывает, на какое количество строк или столбцов необходимо сместитьсяот отправной точки. Может иметь положительные и отрицательные значения, а также равняться нулю.
- Высота и ширина: Это размер диапазона, который вы хотите вернуть. К примеру, 4X3 вернет диапазон содержащий 4 строки в высоту и 3 столбца в ширину.
И конечно, все аргументы могут ссылаться на другие ячейки. Это означает, что вы можете написать формулу =СМЕЩ(A1;D1;D2;D3; D4), которая вернет диапазон
- Смещенный от ячейки A1
- На D1 строк и D2 столбцов
- Размером D3 строчки и D4 столбца
На рисунке изображен пример работы формулы СМЕЩ для лучшего понимания.
Зачем использовать формулу СМЕЩ?
Почему просто нельзя явно указать название диапазона, например, A1:C4?
Вот пара причин почему:
- Динамические диапазоны: Ссылка типа A1:C4 всегда будет возвращать диапазон A1:C4, т.е. она статична. Но иногда нам необходимо, чтобы диапазон был динамичным. Это требуется потому что данные могут меняться (с каждым месяцем добавляются новые строчки, запускаются новые продукты — добавляются колонки)
- Мы не знаем точного адреса: Иногда мы не можем знать фактического адреса. Чаще всего нам известна только стартовая ячейка. В такой ситуации нам поможет формула СМЕЩ.
Интерактивное описание формулы СМЕЩ
Формула СМЕЩ довольно непростая для понимания, поэтому была создана интерактивная таблица, чтобы вы могли лучше понять, как она работает. Изменяя 5 аргументов формулы, Excel будет выделять диапазон, который вы выбрали. После нескольких минут баловства, вы поймете формулу лучше.
Скачать книгу с интерактивной таблицей формулы СМЕЩ.
Практикуемся с формулой СМЕЩ — среднее значение последней недели
Предположим, что мы работаем в службе качества фабрики по производству шоколадных батончиков. Одним из показателей эффективности (KPI), который мы должны отслеживать является бракованные батончики. Лист сбора данных будет выглядеть примерно следующим образом.
Так как же мы будем считать средний показатель брака за неделю?
Если предположить, что данные находятся в диапазоне B2:B23, тогда необходимо записать =СРЗНАЧ(B2:B23).
Но нам необходимо, чтобы эта формула менялась ежедневно!!!
Используя формулу СМЕЩ мы можем один раз посчитать среднее значение и больше не возвращаться к этому вопросу.
=СРЗНАЧ(СМЕЩ(B2;СЧЁТЗ(B2:B300)-7;0;7;1))
Разберемся, как работает эта формула
- Для того, чтобы посчитать среднее значение последней недели, мы должны вытянуть последние 7 строк со всего массива данных.
- Вот откуда взялась формула СЧЁТЗ(B2:B300)-7, которая считает количество значений и затем, от полученной цифры отнимается 7.
- Формула СМЕЩ перемещается с ячейки B2 на стартовую ячейку последней недели.
- На гифке показано, как работает динамический диапазон.
