Аргументы функции
- reference (ссылка) – ссылка на ячейку, от которой вы хотите сделать смещение. Это может быть ссылка на ячейку или диапазон смежных ячеек;
- rows (смещ_по_строкам) – количество строк для смещения от изначальной позиции. Если вы укажете положительное число, то произойдет смещение строк ниже, если отрицательное – выше;
- cols (смещ_по_столбцам) – количество колонок для смещения от изначальной позиции. Если вы укажете положительное число, то произойдет смещение колонок вправо, если отрицательное число, то влево;
- () – количество строк в указанном диапазоне функции;
- () – количество колонок в указанном диапазоне функции.
Основной принцип работы функции
Функция СМЕЩ , пожалуй, самая запутанная функция в Excel.
Давайте разберем ее работу на простом примере игры в шахматы. В шахматах есть фигура Ладья.
Источник фото: Wikipedia
По правилам игры в шахматы, Ладья может ходить только вправо, влево, вниз и вверх. Фигура не может передвигаться по диагонали.
Теперь давайте представим, что нашей Ладье нужно переместиться не строго влево или вправо, а на ячейку, находящуюся по диагонали от изначальной позиции. Что мы будем делать этом случае?
Правильно, мы будем использовать несколько шагов, для того чтобы привести Ладью к цели. Тот же принцип действует и в функции OFFSET (СМЕЩ) .
Рассмотрим перемещение Ладьи на примере в Excel. Мы хотим начать с ячейки D5 (где находится ладья), а затем перейти на две строки вниз и два столбца вправо и извлечь значение из ячейки. Для этого будем использовать формулу:
=OFFSET(стартовая позиция, на сколько строк сместиться вниз, на сколько столбцов сместиться вправо) – английская версия
=СМЕЩ(стартовая позиция, на сколько строк сместиться вниз, на сколько столбцов сместиться вправо) – русская версия
Как вы видите формула по нашему примеру выглядит так:
=OFFSET(D5,2,2) – английская версия
=СМЕЩ(D5;2;2) – русская версия
Функции задан аргумент старта отсчета с ячейки “D5”, затем смещение на две строки вниз, после этого на две колонки вправо. Так мы переместимся с ячейки “D5” на ячейку “F7”. По завершении перемещения функция выдает значение ячейки “F7”.
На примере выше мы рассмотрели функцию OFFSET (СМЕЩ) с тремя аргументами. Но есть еще два необязательных аргумента, которые можно использовать.
Давайте рассмотрим простой пример:
Предположим, вы хотите использовать ссылку на ячейку “A1” (желтую), и хотите сослаться на весь диапазон, выделенный синим (C2:E4) в формуле.
Как бы вы это сделали с помощью клавиатуры? Сначала нужно перейти к ячейке C2, а затем выбрать все ячейки в диапазоне “C2:E4”.
Теперь посмотрим, как это сделать, используя формулу OFFSET (СМЕЩ) :
=OFFSET(A1,1,2,3,3) – английская версия
=СМЕЩ(A1;1;2;3;3) – русская версия
Если вы используете эту формулу в ячейке, она вернет #VALUE! Но если вы перейдете в режим редактирования, выберете формулу и нажмите клавишу “F9”, вы увидите, что она возвращает все значения, выделенные синим цветом.
Надеюсь, теперь у вас есть базовое понимание использования функции OFFSET (СМЕЩ) в Excel.
ВПР
Функция ВПР позволяет найти данные в текстовой строке таблицы или диапазоне ячеек и добавить их в другую таблицу. Аббревиатура ВПР расшифровывается как «вертикальный просмотр».
Синтаксис
Данная функция состоит из 4 аргументов и представлена следующей формулой:
=ВПР(искомое_значение;таблица;номер_столбца;)
Рассмотрим каждый из аргументов:
- «Искомое значение» указывают в первом столбце рассматриваемого диапазона ячеек. Данный аргумент может являться значением или ссылкой на ячейку.
- «Таблица». Группа ячеек, в которой выполняется поиск искомого значения и возвращаемого. Диапазон ячеек должен содержать искомое значение в первом столбце и возвращаемое значение – в любом месте.
- «Номер столбца». Номер столбца, содержащий возвращаемое значение.
- «Интервальный просмотр» – необязательный аргумент. Это логическое выражение, определяющее – насколько точное совпадение должна обнаружить функция. В связи с этим условием выделяют 2 функции:
- ИСТИНА. Эта функция, вводимая по умолчанию, ищет ближайшее к искомому значение. Данные первого столбца должны быть упорядочены по возрастанию или в алфавитном порядке.
- ЛОЖЬ. Данная функция ищет точное значение в первом столбце.
Примеры
Рассмотрим несколько примеров использования функции ВПР. Ниже приведен пример того, как можно использовать функцию для анализа данных о статистике по запросам. Предположим, что нам нужно найти в данной таблице количество просмотров по запросу «купить планшет».
Функции нужно найти данные, соответствующие значению «планшет», которое указано в отдельной ячейке (С3) и выступает в роли искомого значения. Аргумент «таблица» здесь – диапазон поиска от A1:B6; номер столбца, содержащий возвращаемое значение – «2». В итоге получаем следующую формулу: =ВПР(С3;А1:B6;2). Результат – 31325 просмотров в месяц.
В следующих двух примерах применен интервальный просмотр с двумя вариантами функций: ИСТИНА и ЛОЖЬ.
Функция ВПР является одной из самых популярных функций Excel, достаточно сложной для понимания, но чрезвычайно полезной.
Синтаксис функции СМЕЩ()
- ссылка — ссылка, от которой вычисляется смещение;
- смещ_по_строкам — количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку (по умолчанию =0);
- смещ_по_столбцам – аналогично смещ_по_строкам, только смещение отсчитывается по столбцам (по умолчанию =0);
- высота — число строк возвращаемой ссылки. Значение аргумента «высота» должно быть положительным числом;
- ширина — число столбцов возвращаемой ссылки (по умолчанию =1).
Чтобы было понятнее, потренируемся с функцией СМЕЩ() , используя файл примера .
Дана исходная таблица с тремя столбцами.
Задавая параметры функции СМЕЩ() подсчитаем сумму значений в различных диапазонах таблицы. Для визуального наблюдения диапазона, возвращаемого функцией СМЕЩ() , использовано Условное форматирование. Для удобства изменения параметров функции СМЕЩ() использованы Элементы управления Счетчик.
Для подсчета суммы значений в столбце Продажа1 запишем формулу: =СУММ(СМЕЩ($B$2;0;0;8;1)) диапазон суммирования — $B$2:$B$9 (левый верхний угол — $B$2, высота 8, смещения верхнего угла нет). Результат 34.
Для подсчета суммы значений в столбце Продажа2 запишем формулу: =СУММ(СМЕЩ($B$2;0;1;8;1)) Теперь левый верхний угол диапазона суммирования смещен от $B$2 на один столбец вправо, т.е. диапазон суммирования стал $C$2:$C$9. Результат 68.
Для подсчета суммы значений в столбцах Продажа1 и Продажа2, изменим ширину диапазона. =СУММ(СМЕЩ($B$2;0;0;8;2)) указав ширину в 2 ячейки, результат составит 102, диапазон будет модифицирован в $В$2:$С$9.
Добавив смещение по строкам (+1), получим результат 99: =СУММ(СМЕЩ($B$2;1;0;8;2)) диапазон будет модифицирован в $В$3:$С$9.
Функция СМЕЩ() vs ИНДЕКС()
Пусть имеется диапазон с числами ( А2:А10 ) Необходимо найти сумму первых 2-х, 3-х, …9 значений. Конечно, можно написать несколько формул =СУММ(А2:А3) , =СУММ(А2:А4) и т.д. Но, записав формулу ввиде:
=СУММ(СМЕЩ(A2;;;4))
получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений). Вышеуказанная формула эквивалентна формуле =СУММ(A2:ИНДЕКС(A2:A10;4)) , которая, в свою очередь, эквивалентна формуле =СУММ(A2:A5)
Формула ИНДЕКС(A2:A10;4) возвращает ссылку на ячейку А5 .
Примеры использования функции СМЕЩ в Excel
Пример 1. Ищем последнюю заполненную ячейку в колонке
Представим, что у вас есть данные в колонке. Для того чтобы отобразить последнее значение в колонке используйте формулу:
Эта формула предполагает, что кроме указанных значений нет никаких других, и в этой колонке нет пустых ячеек. Функция работает, подсчитывая общее количество заполненных ячеек и соответствующим образом смещает ячейку «A1».
Например, в указанном примере есть 8 значений, поэтому функция COUNT(A:A) или СЧЁТ(A:A) возвращает 8. Мы смещаем ячейку «A1» на 7, чтобы получить последнее значение.
Пример 2. Создаем динамический выпадающий список с автоматическим дополнением новых данных
Вы можете использовать принцип из Примера 1 для создания динамического выпадающего списка с автоматическим дополнением новых данных. Например, вы создали выпадающий список и хотите, чтобы при добавлении новых строк, значения автоматически подгружались в выпадающий список.
Обратите внимание, что на примере выше, значения автоматически появляются и исчезают из выпадающего списка, как только вы вносите изменения в диапазон ячеек, указанный для выпадающего списка. Это происходит, поскольку формула, которая используется для создания раскрывающегося списка, является динамической и определяет любое добавление или удаление и соответствующим образом корректирует диапазон
Это происходит, поскольку формула, которая используется для создания раскрывающегося списка, является динамической и определяет любое добавление или удаление и соответствующим образом корректирует диапазон.
Как сделать такой список:
- Выберите ячейку, в которой вы хотите создать выпадающий список;
- Нажмите на вкладку Data => Data Tools => Data Validation;
- В диалоговом окне Data Validation, в разделе Настройки выберите List из выпадающего списка;
- В параметрах Source укажите формулу =OFFSET(A1,0,0,COUNT(A:A),1) или =СМЕЩ(A1;0;0;СЧЁТ(A:A);1)
- Нажмите ОК
Как эта формула работает:
Первые три аргумента функции OFFSET (СМЕЩ) A1, 0, 0. Это означает что начальное значение в ячейке «A1», которое не смещается ни по строкам и по колонкам (0, 0); Четвертый аргумент функции указывает на высоту, и здесь функция COUNT (СЧЁТ) возвращает суммарное количество ячеек в диапазоне данных для выпадающего списка. Главное условие — отсутствие пустых ячеек в диапазоне. Пятый аргумент функции “1”, обозначает ширину диапазона данных, которая в нашем случае равна одной колонке.
Функция ДАТАЗНАЧ
Синтаксис:
ДАТАЗНАЧ(дата_как_текст)
Результат: Соответствующее дате сериальное число, представленное в текстовом виде, Функция ДАТАЗНАЧ используется для преобразования даты из текстового представления в числовой формат.
Аргументы:
дата_как_текст — текст, содержащий дату в формате даты Excel. При использовании в Excel истемы дат, принятой по умолчанию для Windows 95/98, аргумент дата_как_текст должен представлять собой дату в диапазоне от 1 января 1900 года до 31 декабря 9999 года. При использовании в Excel системы дат, принятой по умолчанию для Macintosh, аргумент дата_как_текст должен представлять собой дату в диапазоне от 1 января 1904 года до 31 декабря 9999 года. Функция ДАТАЗНАЧ возвращает значение ошибки #ЗНАЧ!, если значение аргумента дата_как_текст выходит за пределы указанных диапазонов. Если в аргументе дата_как_текст опушен год, то функция ДАТАЗНАЧ использует значение текущего года из встроенных часов компьютера. Информация о времени суток в аргументе дата_ как_текст игнорируется.
ПРИМЕЧАНИЕ
См. функцию ГОД.
Аргументы функции
- reference (ссылка) — ссылка на ячейку, от которой вы хотите сделать смещение. Это может быть ссылка на ячейку или диапазон смежных ячеек;
- rows (смещ_по_строкам) — количество строк для смещения от изначальной позиции. Если вы укажете положительное число, то произойдет смещение строк ниже, если отрицательное — выше;
- cols (смещ_по_столбцам) — количество колонок для смещения от изначальной позиции. Если вы укажете положительное число, то произойдет смещение колонок вправо, если отрицательное число, то влево;
- () — количество строк в указанном диапазоне функции;
- () — количество колонок в указанном диапазоне функции.
Excel OFFSET Функция
Excel Функция СМЕЩЕНИЕ возвращает ссылку на ячейку или диапазон ячеек, которые смещены от конкретной ячейки на заданное количество строк и столбцов.
аргументы
Справка (обязательный): Ячейка или диапазон соседних ячеек, которые вы установите в качестве отправной точки.Ряды (обязательный): Количество строк, которые нужно переместить вверх (отрицательное число) или вниз (положительное число) от начальной точки. ошейники (обязательный): Количество столбцов, на которые нужно переместиться влево (отрицательное число) или вправо (положительное число) от начальной точки.Высота (необязательный): Количество строк, которые вы хотите вернуть. Высота должна быть положительным числом.Ширина (необязательный): Количество столбцов, которые вы хотите вернуть. Ширина должна быть положительным числом.
Примечания к функциям
1. # ЗНАЧЕНИЕ! Значение ошибки будет возвращено, если ссылка относится к диапазону прерывистых ячеек. 2. # ССЫЛКА! Значение ошибки будет возвращено, когда ссылка на строки и столбцы смещается за край листа.
Примеры
Пример 1: Базовое использование функции СМЕЩЕНИЕ
Верните ссылку на ячейку с помощью следующей формулы:=OFFSET(B2,3,1)
В этом случае B2 является отправной точкой, числа 3 и 1 означают, что нужно переместить 3 строки вниз и 1 столбец вправо от ячейки B2 и, наконец, вернуть значение в C5. Смотрите скриншот:
Верните ссылку на диапазон ячеек с помощью следующей формулы:=OFFSET(B2,3,1,2,2)
В этом случае вы получите результаты диапазона 2 x 2, который находится на 3 строки ниже и 1 столбец справа от ячейки B2.
Внимание: #ЗНАЧЕНИЕ! Ошибка возникнет, если вы выберете только одну ячейку для применения функции СМЕЩЕНИЕ для возврата диапазона ячеек. Вам нужно выбрать диапазон 2 x 2 (говорит 4 пустых ячейки), ввести формулу и нажать Ctrl + Shift + Enter ключи для получения результатов
Пример 2: Используйте функцию СМЕЩЕНИЕ для суммирования диапазона значений
Как мы упоминали выше, если вы попытаетесь использовать функцию СМЕЩЕНИЕ, например =OFFSET(B2,3,1,2,2) сам по себе в одной ячейке он вернет #VALUE! Ошибка. Однако, если вы объедините функции СУММ и СМЕЩЕНИЕ, как показано на скриншоте ниже, она вернет сумму значений в диапазоне C5: D6 напрямую.
1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ для получения результата.=SUM(OFFSET(B2,3,1,2,2)))
Пример 3: Суммирование столбцов на основе определенных критериев
Как показано на скриншоте ниже, как получить общий объем продаж манго с воскресенья по вторник? Пожалуйста, попробуйте, как показано ниже.
1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ для получения результата.=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))
Заметки:
1. В приведенной выше формуле MATCH(G4,B3:B8) ищет манго и возвращает свою позицию в пределах досягаемости B3: B8. Давайте посмотрим, Mango помещает 5 строк под ячейкой B2, поэтому возвращает число 5; 2. Поскольку результат ПОИСКПОЗ равен 5, а последняя запятая здесь представляет 0, функция СМЕЩЕНИЕ теперь отображается как OFFSET(C2:E2,5,0), что означает, что смещение начинается с диапазона C2: E2, переместите 5 строк вниз и 0 столбцов вправо, чтобы получить ссылку на диапазон C7: E7; 3. Затем функция СУММ отображается как SUM(C7:E7), и, наконец, вернуть сумму значений в диапазоне C7: E7.
Краткое руководство: ввод данных на листе в приложении Excel Начальный
Примечание:
Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки
Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы
Для удобства также приводим
ссылку на оригинал (на английском языке)
.
Для работы с данными на листе сначала в ячейки на листе необходимо ввести данные. После этого, возможно, данные потребуется настроить, чтобы они отображались необходимым образом.
Инструкции
|
Ввод данных
|
|
|
Настройка параметров
|
|
Форматирование данных
|
Дальнейшие действия
Важно знать, как вводить данные, введя его на листе, но также важно знать, как Дополнительно или копирование данных из одного места в другое и как вставка или удаление строк и столбцов и для перемещения данных. Чтобы узнать, как это сделать, читайте в статье Перемещение и копирование ячеек и их содержимого ячейки и Вставка или удаление ячеек, строк и столбцов
Совет. Office.com постоянно пополняется новое содержимое, включая статьи с инструкциями, видео и обучающие курсы. Если вы подключены к Интернету нижней части средства просмотра справки говорит автономный режим, нажмите кнопку автономно и нажмите кнопку Показать содержимое из Office.com.
Функция ЕСЛИ
Возвращает одно значение, если данное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
Функция ЕСЛИ употребляется при проверке критерий для значений и формул.
ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь)
Лог_выражение — это хоть какое значение либо выражение, принимающее значения ИСТИНА либо ЛОЖЬ. К примеру, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение воспринимает значение ИСТИНА. В неприятном случае — ЛОЖЬ. Этот аргумент быть может применен в любом операторе сопоставления.
Значение_если_истина — это значение, которое ворачивается, если лог_выражение равно ИСТИНА. К примеру, если этот аргумент — строчка «В границах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ покажет текст «В границах бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, то ворачивается значение 0. Чтоб показать слово ИСТИНА, нужно применять логическое значение ИСТИНА для этого аргумента. Значение_если_истина быть может формулой.
Значение_если_ложь — это значение, которое ворачивается, если лог_выражение равно ЛОЖЬ. К примеру, если этот аргумент — строчка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ покажет текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ложь опущено (другими словами опосля значение_если_истина нет точки с запятой), то ворачивается логическое значение ЛОЖЬ. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (другими словами опосля значение_если_истина стоит точка с запятой с следующей закрывающей скобкой), то ворачивается значение 0. Значение_если_ложь быть может формулой.
До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь для конструирования наиболее сложных проверок. См. крайний из приведенных ниже примеров.
Когда значения аргументов значение_если_истина и значение_если_ложь вычислены, функция ЕСЛИ возвращает приобретенное значение.
Если хоть какой из аргументов функции ЕСЛИ является массивом, все элементы массива рассчитываются при выполнении функции ЕСЛИ.
Microsoft Excel дает доп функции, которые можно использовать для анализа данных с внедрением критерий. К примеру, для вычисления числа возникновений текстовой строчки либо числа в спектре ячеек используйте функцию СЧЁТЕСЛИ. Для вычисления суммы значений, попадающих в интервал, данный текстовой строчкой либо числами, используйте функцию СУММЕСЛИ. Вычисление значений с внедрением условия.
На листе экономного отчета ячейка A10 содержит формулу для расчета текущего бюджете. Если итог вычислений по формуле в ячейке A10 меньше либо равен 100, то приведенная ниже функция показывает «В границах бюджета». В неприятном случае функция показывает «Превышение бюджета».
В последующем примере, если значение ячейки A10 — 100, то лог_выражение имеет значение ИСТИНА и рассчитывается сумма для ячеек B5:B15. В неприятном случае лог_выражение имеет значение ЛОЖЬ и ворачивается пустой текст («»), очистительный ячейку, которая содержит функцию ЕСЛИ.
ЕСЛИ (A10=100; СУММ (B5:B15); «»)
Представим, что рабочий лист по расходам содержит в ячейках B2:B4 фактические расходы за январь, февраль, март: 1500, 500 и 500 соответственно. Ячейки C2:C4 содержат данные по предполагаемым расходам за те же периоды: 900, 900 и 925.
Можно написать формулу для проверки соответствия бюджету расходов определенного месяца, генерируя тексты сообщений при помощи последующих формул:
ЕСЛИ (B2>C2; «Превышение бюджета»; «OK») приравнивается «Превышение бюджета»
ЕСЛИ (B3>C3; «Превышение бюджета»; «OK») приравнивается «OK»
Представим, что необходимо назначить буквенную категорию числам, на которые ссылаются по имени «СреднийБалл». Группы приведены в последующей таблице.
Как работает функция смещение в excel
В этой статье описаны синтаксис формулы и использование функции СМЕЩ в Microsoft Excel.
Описание
Данная функция возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть отдельной ячейкой или диапазоном ячеек. Можно задавать количество возвращаемых строк и столбцов.
Синтаксис
Аргументы функции СМЕЩ описаны ниже.
Ссылка — обязательный аргумент. Ссылка, от которой вычисляется смещение. Аргумент «ссылка» должен быть ссылкой на ячейку или на диапазон смежных ячеек, в противном случае функция СМЕЩ возвращает значение ошибки #ЗНАЧ!.
Смещ_по_строкам Обязательный. Количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента «смещ_по_строкам» задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять строк ниже, чем указано в аргументе «ссылка». Значение аргумента «смещ_по_строкам» может быть как положительным (для ячеек ниже начальной ссылки), так и отрицательным (выше начальной ссылки).
Смещ_по_столбцам Обязательный. Количество столбцов, которые требуется отсчитать влево или вправо, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента «смещ_по_столбцам» задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять столбцов правее, чем указано в аргументе «ссылка». Значение «смещ_по_столбцам» может быть как положительным (для ячеек справа от начальной ссылки), так и отрицательным (слева от начальной ссылки).
Высота Необязательный. Высота (число строк) возвращаемой ссылки. Значение аргумента «высота» должно быть положительным числом.
Ширина Необязательный. Ширина (число столбцов) возвращаемой ссылки. Значение аргумента «ширина» должно быть положительным числом.
Примечания
Если аргументы «смещ_по_строкам» и «смещ_по_столбцам» выводят ссылку за границы рабочего листа, функция СМЕЩ возвращает значение ошибки #ССЫЛ!.
Если высота или ширина опущена, то предполагается, что используется та же высота или ширина, что и в аргументе «ссылка».
Функция СМЕЩ фактически не передвигает никаких ячеек и не меняет выделения; она только возвращает ссылку. Функция СМЕЩ может использоваться с любой функцией, в которой ожидается аргумент типа «ссылка». Например, с помощью формулы СУММ(СМЕЩ(C2;1;2;3;1)) вычисляется суммарное значение диапазона, состоящего из трех строк и одного столбца и расположенного одной строкой ниже и двумя столбцами правее ячейки C2.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Функция СМЕЩ() vs ИНДЕКС()
Пусть имеется диапазон с числами (А2:А10) Необходимо найти сумму первых 2-х, 3-х, . 9 значений. Конечно, можно написать несколько формул =СУММ(А2:А3) , =СУММ(А2:А4) и т.д. Но, записав формулу ввиде:
получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений). Вышеуказанная формула эквивалентна формуле =СУММ(A2:ИНДЕКС(A2:A10;4)) , которая, в свою очередь, эквивалентна формуле =СУММ(A2:A5)
Формула ИНДЕКС(A2:A10;4) возвращает ссылку на ячейку А5.
В этой статье описаны синтаксис формулы и использование функции СМЕЩ в Microsoft Excel.
Пример использования функции СМЕЩ
Функция СМЕЩ возвращает ссылку, поэтому может использоваться с другими функциями, в которых среди аргументов есть ссылки.
Поэтому теперь рассмотрим как пользоваться данной формулой вместе с другими на примере стандартных типовых задач.
Пример 1. Функция ПОИСКПОЗ
Предположим, что у нас имеются данные с подневными продажами компании и мы хотим определить продажи на конкретное число.
Воспользуемся функцией ПОИСКПОЗ для поиска указанной даты (ячейка D2) в диапазоне с датами (A2:A10).
После чего сместим начальную ячейку (в данном случае B2) на рассчитанную величину вниз за вычетом единицы.
Мы дополнительно вычитаем единицу так как показываем именно смещение относительно начальной ячейки, например, чтобы перейти с первой строки на шестую мы смещаемся ровно на пять строк.
В итоге получаем следующий результат:
Идентичного результата можно добиться и с помощью функции ИНДЕКС — формула =ИНДЕКС(B2:B10;ПОИСКПОЗ(D2;A2:A10;0)) вернет точно такой же результат.
Пример 2. Функция СУММ
Возьмем начальные условия как в предыдущем примере, однако теперь мы посчитаем сумму продаж за последние 7 дней.
Можно воспользоваться стандартной формулой СУММ(B4:B10), но при добавлении новых строчек расчет становится неверным и нам придется каждый раз изменять формулу, поэтому мы пойдем по другому пути.
С помощью функции СЧЁТЗ находим последнюю введенную дату (указываем достаточно большой диапазон A2:A100, чтобы была возможность добавлять новые данные).
Из полученного результата вычитаем 7, чтобы найти первую дату искомого диапазона, поэтому производя сдвиг начальной ячейки (B2) на найденную величину и расширяя диапазон до размеров 7 на 1, мы получим данные за 7 последних дней.
Просуммируем их воспользовавшись функцией СУММ:
При добавлении новых данных в таблицу результат будет автоматически пересчитываться:
