gototopgototop

Материалы Excel

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

Сейчас 53 гостей онлайн
Диапазон таблицы Excel. Функция «СМЕЩ» в Excel.

Функция «СМЕЩ» в Excel.

      Рассмотрим, как сделать динамический диапазон в Excel с помощью функцию «СМЕЩ» в Excel. Функция очень полезная. Про эту функцию говорят, что она смещает диапазон на заданное количество строк и столбцов.
      Ничего не понять – где смещает, для чего смещает, т.д. Скажем простым языком. Функция «СМЕЩ» в Excel автоматически меняет в формуле Excel адрес диапазона ячеек, относительно конкретной ячейки таблицы. Не нужно вручную постоянно менять в формуле адрес диапазона таблицы, при добавлении в таблицу строк и столбцов.
      Эта функция пригодится при составлении различных выпадающих списков, графиков, т.д.. Пример смотрите в статье "Раскрывающийся список в Excel с картинками" тут.
      Функция «СМЕЩ» поможет считать данные из определенном диапазоне. Например, нам нужно считать данные только последних семи ячеек в таблице, которая заполняется ежедневно.
      Сначала разберем аргументы (условия) функции «СМЕЩ» в Excel.
На закладке «Формулы» в разделе «Библиотека функций» нажимаем на кнопку «Ссылки и массивы». Из появившегося списка выбираем функцию «СМЕЩ». Появится такое окно. Заполнять его можно полностью, можно частично.Функция       Рассмотрим аргументы функции «СМЕЩ» в Excel.
«Ссылка» - здесь пишем адрес ячейки, диапазона смежных ячеек, от которой будет сдвигаться диапазон (это - стартовая ячейка). Эту строку заполнять обязательно. Адрес ячейки может быть относительный, тогда диапазон будет полностью перемещаться по таблице. Абсолютный адрес ячейки – тогда верхний край диапазона смещаться не будет.
«Смещ по строкам» - здесь ставим цифру, на какое количество строк нужно сдвинуть диапазон. Если поставим положительное число (например, 6), то диапазон сдвинется вниз. (на 6 строк). Если поставим отрицательное число (-6), то диапазон поднимется вверх (на 6 строк). Заполнять эту строку обязательно. Если не нужно сдвигать строку, то ставим нуль (0).
«Смещ по столбцам» - здесь ставим цифру, на какое количество столбцов нужно сдвинуть диапазон. Если поставим положительное число (например, 3), то диапазон сдвинется вправо (на 3 столбца). Если поставим отрицательное число (-8), то диапазон сдвинется влево (на 8 столбцов). Заполнять эту строку обязательно. Если не нужно сдвигать столбец, то ставим нуль (0).
      Внимание!
Если поставили большее количество строк или столбцов, чем диапазон таблицы, то в ячейке выйдет сообщение «#ССЫЛ!.». Например, если стартовая ячейка В1, а мы указали смещение по строкам – минус 3, то ячейки влево сдвигаться не куда. Выйдет ошибка. За границы таблицы диапазон не смещается.
«Высота» - число строк в диапазоне, который будем сдвигать, считать.
«Ширина» - число столбцов в диапазоне, который будем перемещать.
      Показатели «Высота» и «Ширина» не обязательно заполнять. Тогда размер диапазона будет такой, какой написали в строке «Ссылка». Но, если заполняем эти строки (аргументы функции), то ставим только положительное число.
После заполнения диалогового окна, нажимаем кнопку «ОК».
      Про относительные и абсолютные ссылки в формулах, читайте в статье «Относительные и абсолютные ссылки в Excel» здесь.
О диапазонах смежных, несмежных ячеек, смотрите статью «Диапазон в Excel».
      Теперь рассмотрим, как работает функция «СМЕЩ» в Excel.
У нас такая таблица с данными. 
Напишем формулу, чтобы отображалось значение конкретной ячейки. В ячейке Е1 устанавливаем функцию «СМЕЩ». Диалоговое окно функции заполнили так. 
Получилось так.
Формула в ячейке Е1 получилась такая.  =СМЕЩ(B3;3;1)
Этой формулой мы говорим -  показать значение ячейки, которая находится от ячейки B3 (на картинке ячейка синим цветом) ниже на три строки и правее на один столбец. Это, получилась ячейка С6 (зеленая ячейка).
      Функцией «СМЕЩ» в Excel можно перевернуть (переместить) данные в строке справа на лево. Смотрите статью «Как поменять местами столбцы в Excel».
      Можно перевернуть данные в столбце – снизу-вверх. Об этом читайте статью «Как перевернуть таблицу в Excel».
Можно сложить данные, вычесть среднее значение, т.д. в определенном динамичном диапазоне.
      Как сделать динамический диапазон в таблице функцией "СМЕЩ", смотрите в статье "Чтобы размер таблицы Excel менялся автоматически" здесь.
      Как и где применить функцию "СМЕЩ", как считать последние данные таблицы, которые добавляются каждый день, т.д., читайте в статье "Примеры функции "СМЕЩ" в Excel".