Смещ excel выдает ошибку
Привет. Сегодня расскажу про функцию OFFSET , она работает и в Excel, и в Google-таблицах. В русском Excel функция называется СМЕЩ (от "смещение") и как результат, выдает ячейку или диапазон заданные со смещением от исходной ячейки (или диапазона). В статье будет описание функции и примеры использования. Расскажу, как эта функция может быть полезна.
Функция СМЕЩ принимает до 5 аргументов, первые 3 обязательные, остальные два опциональные:
=СМЕЩ( <ссылка>;<смещение по строкам>;<смещение по столбцам>;<высота>;<ширина> )
Аргументы функции
<ссылка> - ссылка на исходный диапазон. Может быть задана как напрямую, абсолютная или относительная, например: $A$1 или B2 , с помощью функции ИНДЕКС (INDEX) или с помщью функции ДВССЫЛ (INDIRECT). Пример ниже будет указывать на ячейку B1 на листе " Лист1 "
<смещение по строкам> и <смещение по столбцам> - целое число, может быть отрицальным, нулем или положительным, говорит на сколько от исходной ссылки нужно сдвинуться по вертикали (смещение по строкам) и по горизонтали (смещение по столбцам). Если число положительное, то смещение происходит слева направо и сверху вниз (в сторону увеличения буквенного значения и индексов ячейки). Если отрицательное, то наоборот. Если значение смещения равно нулю, то смещения по данной координате не будет. К примеру, СМЕЩ(A1,0,0) будет указывать на ячейку A1 .
Если задать ссылку и эти два параметра, опустив последующие два, то размеры получившегося диапазона будут такие же, как и размеры изначального. Если изначално <ссылка> указывала на ячейку - будет ячейка. Если указывала на диапазон, то будет диапазон.
<высота> и <ширина> - целое число, может быть отрицальным, нулем или положительным, отвечает за то, насколько будет расширен (или сужен при отрицательных аргументах) диапазон относительно изначально заданного.
В этой статье описаны синтаксис формулы и использование функции СМЕЩ в Microsoft Excel.
Описание
Данная функция возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть отдельной ячейкой или диапазоном ячеек. Можно задавать количество возвращаемых строк и столбцов.
Синтаксис
Аргументы функции СМЕЩ описаны ниже.
Смещ_по_строкам Обязательный. Количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента "смещ_по_строкам" задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять строк ниже, чем указано в аргументе "ссылка". Значение аргумента "смещ_по_строкам" может быть как положительным (для ячеек ниже начальной ссылки), так и отрицательным (выше начальной ссылки).
Смещ_по_столбцам Обязательный. Количество столбцов, которые требуется отсчитать влево или вправо, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента "смещ_по_столбцам" задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять столбцов правее, чем указано в аргументе "ссылка". Значение "смещ_по_столбцам" может быть как положительным (для ячеек справа от начальной ссылки), так и отрицательным (слева от начальной ссылки).
Высота Необязательный. Высота (число строк) возвращаемой ссылки. Значение аргумента "высота" должно быть положительным числом.
Ширина Необязательный. Ширина (число столбцов) возвращаемой ссылки. Значение аргумента "ширина" должно быть положительным числом.
Примечания
Если высота или ширина опущена, то предполагается, что используется та же высота или ширина, что и в аргументе "ссылка".
Функция СМЕЩ фактически не передвигает никаких ячеек и не меняет выделения; она только возвращает ссылку. Функция СМЕЩ может использоваться с любой функцией, в которой ожидается аргумент типа "ссылка". Например, с помощью формулы СУММ(СМЕЩ(C2;1;2;3;1)) вычисляется суммарное значение диапазона, состоящего из трех строк и одного столбца и расположенного одной строкой ниже и двумя столбцами правее ячейки C2.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Здравствуйте.
Дело в следующем, в таблице использовал функцию АГРЕГАТ для поиска значения по нескольким критериям (т.к. в критериях есть и текст, и выводить тоже нужно текст, СУММЕСЛИМН не вариант). Но так как требуется проводить поиск сразу по нескольким группам критериев и получать результаты на них одновременно, формула находиться в нескольких ячейках. Изначально проблема с ней в том что в каждой ячейке приходилось в ручную подгонять формулу чтобы работала, но освоив функцию СМЕЩ, решил переделать прошлую функцию, что бы можно было её протягивать.
Написал формулу грамотно без ошибок, но в результате получал ошибку. Стал смотреть как она вычислялась и увидел что СМЕЩ почему то вместо диапазона выдавала ошибку ЗНАЧ, но когда решил проверить, работает ли СМЕЩ с АГРЕГАТом записав мелкий вариант, увидел что СМЕЩ нормально даёт диапазон.
Постепенно добавляя формулы в АГРЕГАТ я обнаружил что ошибка начинает выскакивать когда я в параметры "смещ_по_строкам/столбцам" добавляю формулы, в моём случае СТРОКА и СТОЛБЕЦ.
Единственный выход который я смог найти, это вынести расчёт столбцов и строк в другие ячейки и уже на эти ячейки ссылать параметры смещ_по. но всё же хотелось бы сделать формулу без использования дополнительных ячеек, так что прошу подскажите, кто знает, можно ли как то заставить СМЕЩ нормально работать с функциями СТРОКА и СТОЛБЕЦ.
Из прикреплённого файла:
Формула в B5
Здравствуйте.
Дело в следующем, в таблице использовал функцию АГРЕГАТ для поиска значения по нескольким критериям (т.к. в критериях есть и текст, и выводить тоже нужно текст, СУММЕСЛИМН не вариант). Но так как требуется проводить поиск сразу по нескольким группам критериев и получать результаты на них одновременно, формула находиться в нескольких ячейках. Изначально проблема с ней в том что в каждой ячейке приходилось в ручную подгонять формулу чтобы работала, но освоив функцию СМЕЩ, решил переделать прошлую функцию, что бы можно было её протягивать.
Написал формулу грамотно без ошибок, но в результате получал ошибку. Стал смотреть как она вычислялась и увидел что СМЕЩ почему то вместо диапазона выдавала ошибку ЗНАЧ, но когда решил проверить, работает ли СМЕЩ с АГРЕГАТом записав мелкий вариант, увидел что СМЕЩ нормально даёт диапазон.
Постепенно добавляя формулы в АГРЕГАТ я обнаружил что ошибка начинает выскакивать когда я в параметры "смещ_по_строкам/столбцам" добавляю формулы, в моём случае СТРОКА и СТОЛБЕЦ.
Единственный выход который я смог найти, это вынести расчёт столбцов и строк в другие ячейки и уже на эти ячейки ссылать параметры смещ_по. но всё же хотелось бы сделать формулу без использования дополнительных ячеек, так что прошу подскажите, кто знает, можно ли как то заставить СМЕЩ нормально работать с функциями СТРОКА и СТОЛБЕЦ.
Из прикреплённого файла:
Формула в B5
функция ИНДЕКС(ПОИСКПОЗ)
Здравствуйте форумчане. Подскажите пожалуйста можно ли связкой функции ИНДЕКС(ПОИСКПОЗ) что бы не.
Функция ПОИСКПОЗ выдает ошибку
Функция ПРОСМОТР должна возвращать значение из столбца K, если в столбце L присутствует "да". Но.
Функция send() работает некорректно после применения closesocket()
Доброго времени суток! Имеется простенький тестовый сервер: SOCKET s; SOCKET s1; SOCKET s2 =.
правильная,т.к.до адреса 3845 все работает правильно.
Эта формула определяет последнюю занятую ячейку в столбце А и выдает на выходе диапазон с А2 до этой найденной ячейки. Такая относительно сложная конструкция нужна, чтобы впоследствии дописывать новые коды к нашему списку и не думать об исправлении диапазона. Если ничего не дописывать ,то можно вместо ввода этой страшноватой формулы просто указать =A2:A3999.
Не понятно почему она не работает выше данной ячейки? Эта формула определяет последнюю занятую ячейку в столбце А
Враки. моя точно определяет а ваша нет.
в вашей: СЧЕТЗ считает к-во не пустых ячеек (их у вас 3853)
СМЕЩ формирует диапазон: т.к. начало А1 и смещение по строкам 1 то начало получаем А2, т.кю высоту диапазона вы находите через счетз то получаем высота диапазона 3853-1=3852, добавляем к А2 получаем, диапазон А2:А3853.
у вас со 2 строки по 146 пустые ячейки вот они и мешаю правильному расчету через ф-цию СЧЕТЗ
Добавлено через 7 минут
если у вас эксель 2007 то ctrl+f3 в открывшемся окне щелкаете по "фотоальбом", потом щелкаете внизу в поле где находится формула данного именованного диапазона. на листе курсивом выделится ваш полученный диапазон, посмотрите какой он у вас.
Читайте также: