Смещ excel выдает ошибку
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции СМЕЩ в Microsoft Excel.
Описание
Данная функция возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть отдельной ячейкой или диапазоном ячеек. Можно задавать количество возвращаемых строк и столбцов.
Синтаксис
Аргументы функции СМЕЩ описаны ниже.
Смещ_по_строкам Обязательный. Количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента "смещ_по_строкам" задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять строк ниже, чем указано в аргументе "ссылка". Значение аргумента "смещ_по_строкам" может быть как положительным (для ячеек ниже начальной ссылки), так и отрицательным (выше начальной ссылки).
Смещ_по_столбцам Обязательный. Количество столбцов, которые требуется отсчитать влево или вправо, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента "смещ_по_столбцам" задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять столбцов правее, чем указано в аргументе "ссылка". Значение "смещ_по_столбцам" может быть как положительным (для ячеек справа от начальной ссылки), так и отрицательным (слева от начальной ссылки).
Высота Необязательный. Высота (число строк) возвращаемой ссылки. Значение аргумента "высота" должно быть положительным числом.
Ширина Необязательный. Ширина (число столбцов) возвращаемой ссылки. Значение аргумента "ширина" должно быть положительным числом.
Примечания
Если высота или ширина опущена, то предполагается, что используется та же высота или ширина, что и в аргументе "ссылка".
Функция СМЕЩ фактически не передвигает никаких ячеек и не меняет выделения; она только возвращает ссылку. Функция СМЕЩ может использоваться с любой функцией, в которой ожидается аргумент типа "ссылка". Например, с помощью формулы СУММ(СМЕЩ(C2;1;2;3;1)) вычисляется суммарное значение диапазона, состоящего из трех строк и одного столбца и расположенного одной строкой ниже и двумя столбцами правее ячейки C2.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Добрый вечер, коллеги!
С формулами на Ты, но в этой задачке затык.
Есть два файла:
1) Исходник, где содержатся данные - ИТОГО данных смещено всегда на 3 строки;
2) И Файл куда собираются ИТОГО.
Задача стоит сложить ИТОГО по названиям продуктов (условно), продукты повторяются в столбце.
Сложить не проблема, вариантов масса, все (на сколько сам знаю) попробовал.
Вот только есть нюанс - при закрытии Исходника выдает ЗНАЧ.
Везде читаю, что это связано с особенностью функции СМЕЩ, но с ИНДЕКС тоже выдает ЗНАЧ.
Варианты: открывать Исходник и макросы не рассматриваются.
Есть у кого-нибудь идеи? Буду признателен за советы.
Добрый вечер, коллеги!
С формулами на Ты, но в этой задачке затык.
Есть два файла:
1) Исходник, где содержатся данные - ИТОГО данных смещено всегда на 3 строки;
2) И Файл куда собираются ИТОГО.
Задача стоит сложить ИТОГО по названиям продуктов (условно), продукты повторяются в столбце.
Сложить не проблема, вариантов масса, все (на сколько сам знаю) попробовал.
Вот только есть нюанс - при закрытии Исходника выдает ЗНАЧ.
Везде читаю, что это связано с особенностью функции СМЕЩ, но с ИНДЕКС тоже выдает ЗНАЧ.
Варианты: открывать Исходник и макросы не рассматриваются.
Есть у кого-нибудь идеи? Буду признателен за советы. Anis625
Есть два файла:
1) Исходник, где содержатся данные - ИТОГО данных смещено всегда на 3 строки;
2) И Файл куда собираются ИТОГО.
Задача стоит сложить ИТОГО по названиям продуктов (условно), продукты повторяются в столбце.
Сложить не проблема, вариантов масса, все (на сколько сам знаю) попробовал.
Вот только есть нюанс - при закрытии Исходника выдает ЗНАЧ.
Везде читаю, что это связано с особенностью функции СМЕЩ, но с ИНДЕКС тоже выдает ЗНАЧ.
Варианты: открывать Исходник и макросы не рассматриваются.
Есть у кого-нибудь идеи? Буду признателен за советы. Автор - Anis625
Дата добавления - 07.06.2018 в 21:56
Ребята выручайте, сломал всю голову, не пойму как сделать.
Прошу прощения если пишу много, просто что бы было понимание того что делаю, а так основная проблема описана ниже.
Создаю структуру с тремя связанными раскрывающимися списками. Соответсвенно использую функцию СМЕЩ().
1-й список - простой диапазон.
2-й список - СМЕЩ() с ПОИСКПОЗ() по уникальным значениям 1-го диапазона.
=СМЕЩ(Лист2!$A$2;1;ПОИСКПОЗ(Лист1!$ K28;Лист2!$B$2:$DA$2;0);ИНДЕКС(Лист 2!$B$1:$DA$1;ПОИСКПОЗ(Лист1!$K28;Ли ст2!$B$2:$DA$2;0)))
3-й список - СМЕЩ() с ПОИСКПОЗ() по НЕ уникальным значениям 2-го диапазона.
=СМЕЩ(Лист2!$A$2;1;ПОИСКПОЗ(Лист1!$ L28;Лист2!$B$2:$DA$2;0);ИНДЕКС(Лист 2!$B$1:$DA$1;ПОИСКПОЗ(Лист1!$L28;Ли ст2!$B$2:$DA$2;0)))
Соответственно, возникает проблема с тем что в 3-м списке при поиске значений по 2-му списку, он находит первое подходящее значение (потому что они НЕ уникальны), и берет пренадлежащий ему диапазон значений, а надо, что бы брал нужный.
Думал решить проблему выбором начальной позиции в функции СМЕЩ(Лист2!$A$2;. ), задав ее формулой, зависящей от выбранного значения в 1-м смиске (Тк 1-й список+2-й спсиок будет уже УНИКАЛЬНОЕ значение).
Но вот тут ПРОБЛЕМА:
Функция СМЕЩ() никак не хочет воспринимать начальную позицию ввиде формулы.
Беру например такую:
=АДРЕС(2;ПОИСКПОЗ(Лист1!$K3;Лист2!$ B$2:$DA$2;0);;;"Лист2")
проверяю в любой ячейке, возвращет значение Лист2!$B$2, то что надо!! Но когда подставляю формулу в СМЕЩ() он ругается и выдает ошибку.
Соответственно получается вот так:
=СМЕЩ(АДРЕС(2;ПОИСКПОЗ(Лист1!$K3;Лист2!$B $2:$DA$2;0);;;"Лист2");1;ПОИСКПОЗ(Лист1!$L28;Лист2!$B$2:$ DA$2;0);ИНДЕКС(Лист2!$B$1:$DA$1;ПОИ СКПОЗ(Лист1!$L28;Лист2!$B$2:$DA$2;0 )))
Всю голову уже сломал, не понимаю что ему не нравится? может все дело в том что списки находятся на листе1, а диапазоны значений на листе 2?!
Или он в принципе не воспринимает начальную позицию ввиде формулы?
Здравствуйте.
Дело в следующем, в таблице использовал функцию АГРЕГАТ для поиска значения по нескольким критериям (т.к. в критериях есть и текст, и выводить тоже нужно текст, СУММЕСЛИМН не вариант). Но так как требуется проводить поиск сразу по нескольким группам критериев и получать результаты на них одновременно, формула находиться в нескольких ячейках. Изначально проблема с ней в том что в каждой ячейке приходилось в ручную подгонять формулу чтобы работала, но освоив функцию СМЕЩ, решил переделать прошлую функцию, что бы можно было её протягивать.
Написал формулу грамотно без ошибок, но в результате получал ошибку. Стал смотреть как она вычислялась и увидел что СМЕЩ почему то вместо диапазона выдавала ошибку ЗНАЧ, но когда решил проверить, работает ли СМЕЩ с АГРЕГАТом записав мелкий вариант, увидел что СМЕЩ нормально даёт диапазон.
Постепенно добавляя формулы в АГРЕГАТ я обнаружил что ошибка начинает выскакивать когда я в параметры "смещ_по_строкам/столбцам" добавляю формулы, в моём случае СТРОКА и СТОЛБЕЦ.
Единственный выход который я смог найти, это вынести расчёт столбцов и строк в другие ячейки и уже на эти ячейки ссылать параметры смещ_по. но всё же хотелось бы сделать формулу без использования дополнительных ячеек, так что прошу подскажите, кто знает, можно ли как то заставить СМЕЩ нормально работать с функциями СТРОКА и СТОЛБЕЦ.
Из прикреплённого файла:
Формула в B5
Здравствуйте.
Дело в следующем, в таблице использовал функцию АГРЕГАТ для поиска значения по нескольким критериям (т.к. в критериях есть и текст, и выводить тоже нужно текст, СУММЕСЛИМН не вариант). Но так как требуется проводить поиск сразу по нескольким группам критериев и получать результаты на них одновременно, формула находиться в нескольких ячейках. Изначально проблема с ней в том что в каждой ячейке приходилось в ручную подгонять формулу чтобы работала, но освоив функцию СМЕЩ, решил переделать прошлую функцию, что бы можно было её протягивать.
Написал формулу грамотно без ошибок, но в результате получал ошибку. Стал смотреть как она вычислялась и увидел что СМЕЩ почему то вместо диапазона выдавала ошибку ЗНАЧ, но когда решил проверить, работает ли СМЕЩ с АГРЕГАТом записав мелкий вариант, увидел что СМЕЩ нормально даёт диапазон.
Постепенно добавляя формулы в АГРЕГАТ я обнаружил что ошибка начинает выскакивать когда я в параметры "смещ_по_строкам/столбцам" добавляю формулы, в моём случае СТРОКА и СТОЛБЕЦ.
Единственный выход который я смог найти, это вынести расчёт столбцов и строк в другие ячейки и уже на эти ячейки ссылать параметры смещ_по. но всё же хотелось бы сделать формулу без использования дополнительных ячеек, так что прошу подскажите, кто знает, можно ли как то заставить СМЕЩ нормально работать с функциями СТРОКА и СТОЛБЕЦ.
Из прикреплённого файла:
Формула в B5
Из прикреплённого файла:
Формула в B5
Pelena, Нет, дело в том что в файле упрощённая версия, с той частью где ошибка возникает, ПОИСКПОЗ не позволял сделать то чего я хотел добиться, вот и перешёл на АГРЕГАТ.
Вот как выглядит формула из основной таблицы
Как видите, тут есть критерии, которые некуда вставить в функцию ПОИСКПОЗ
Я хочу вот эту часть переделать с использованием СМЕЩ,
Pelena, Нет, дело в том что в файле упрощённая версия, с той частью где ошибка возникает, ПОИСКПОЗ не позволял сделать то чего я хотел добиться, вот и перешёл на АГРЕГАТ.
Вот как выглядит формула из основной таблицы
Как видите, тут есть критерии, которые некуда вставить в функцию ПОИСКПОЗ
Я хочу вот эту часть переделать с использованием СМЕЩ,
Как видите, тут есть критерии, которые некуда вставить в функцию ПОИСКПОЗ
Я хочу вот эту часть переделать с использованием СМЕЩ,
т.к. в верхней формуле приходилось вручную сдвигать диапазон и менять букву "Б" Автор - ZetMenChavo
Дата добавления - 10.05.2021 в 09:24
Не убедили. Приложите более полный пример.
Функцию СМЕЩ надо использовать только в крайних случаях, когда другие способы не работают. Из-за её волатильности
Не убедили. Приложите более полный пример.
Функцию СМЕЩ надо использовать только в крайних случаях, когда другие способы не работают. Из-за её волатильности Pelena
Иногда вставка и скрытие столбцов или строк на этом примере не позволяет.
Почему это происходит и что с этим делать
Не всегда можно вставлять строки и столбцы.
Чтобы обойти эту ошибку, необходимо изменить параметр с "Ничего" (скрыть объекты) на "Все". Самый быстрый способ сделать это — нажать CTRL+6.
Кроме того, вы можете изменить параметры Excel. Для этого:
На вкладке Файл нажмите кнопку Параметры и выберите Дополнительно.
Примечание: В Excel 2007 нажмите кнопку "Microsoft Office" и выберите "Параметры Excel".
На вкладке "Дополнительные параметры" прокрутите список до параметров отображения этой книги. В области "Объекты" выберите"Все"вместо ничего (скрыть объекты).
Возможно, вы намеренно скрыли объекты в книге. Например, если в книге много объектов, вы можете скрыть их, чтобы реагировать на запросы. В этом случае для переключения между отображением и скрытием объектов можно использовать сочетания клавиш (CTRL+6).
Столбцы нельзя вставлять и скрывать
Как правило, эту проблему можно устранить, найдя объект, который его удалил, или отодвигая от конца таблицы. Вот несколько советов по поиску объектов:
Чтобы быстро перейти к последней ячейке в строке или столбце, нажмите клавишу END, а затем клавишу СТРЕЛКА ВПРАВО или СТРЕЛКА ВНИЗ.
Чтобы перейти к последней ячейке на сайте, содержавшей данные или форматирование, нажмите CTRL+END.
Чтобы быстро найти и удалить прикомменты, нажмите F5. В диалоговом окне "Перейти" нажмите кнопку "Специальный". Выберите "Приметки"и нажмите кнопку "ОК". Чтобы удалить комментарий, щелкните правой кнопкой мыши ячейку с комментарием и выберите "Удалить комментарий".
Чтобы быстро найти графические объекты, включая диаграммы и кнопки, нажмите клавишу F5. В диалоговом окне "Перейти" нажмите кнопку "Специальный". Выберите "Объекты"и нажмите кнопку "ОК". Найденный объект можно удалить или переместить.
Примечание: Эта проблема обычно возникает при вставке или скрытие столбцов. Такая же проблема может возникнуть и с строками, но она будет не так распространенной из-за ограничения на размер строк в Excel 2007, из-за чего вероятность того, что объекты находятся в самом конце листа, меньше вероятно.
Дополнительные сведения и дополнительные советы по устранению неполадок см. в статье "Не удается скрыть столбцы в Excel".
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Читайте также: