Здравствуйте. Дело в следующем, в таблице использовал функцию АГРЕГАТ для поиска значения по нескольким критериям (т.к. в критериях есть и текст, и выводить тоже нужно текст, СУММЕСЛИМН не вариант). Но так как требуется проводить поиск сразу по нескольким группам критериев и получать результаты на них одновременно, формула находиться в нескольких ячейках. Изначально проблема с ней в том что в каждой ячейке приходилось в ручную подгонять формулу чтобы работала, но освоив функцию СМЕЩ, решил переделать прошлую функцию, что бы можно было её протягивать. Написал формулу грамотно без ошибок, но в результате получал ошибку. Стал смотреть как она вычислялась и увидел что СМЕЩ почему то вместо диапазона выдавала ошибку ЗНАЧ, но когда решил проверить, работает ли СМЕЩ с АГРЕГАТом записав мелкий вариант, увидел что СМЕЩ нормально даёт диапазон. Постепенно добавляя формулы в АГРЕГАТ я обнаружил что ошибка начинает выскакивать когда я в параметры "смещ_по_строкам/столбцам" добавляю формулы, в моём случае СТРОКА и СТОЛБЕЦ. Единственный выход который я смог найти, это вынести расчёт столбцов и строк в другие ячейки и уже на эти ячейки ссылать параметры смещ_по..., но всё же хотелось бы сделать формулу без использования дополнительных ячеек, так что прошу подскажите, кто знает, можно ли как то заставить СМЕЩ нормально работать с функциями СТРОКА и СТОЛБЕЦ.
Здравствуйте. Дело в следующем, в таблице использовал функцию АГРЕГАТ для поиска значения по нескольким критериям (т.к. в критериях есть и текст, и выводить тоже нужно текст, СУММЕСЛИМН не вариант). Но так как требуется проводить поиск сразу по нескольким группам критериев и получать результаты на них одновременно, формула находиться в нескольких ячейках. Изначально проблема с ней в том что в каждой ячейке приходилось в ручную подгонять формулу чтобы работала, но освоив функцию СМЕЩ, решил переделать прошлую функцию, что бы можно было её протягивать. Написал формулу грамотно без ошибок, но в результате получал ошибку. Стал смотреть как она вычислялась и увидел что СМЕЩ почему то вместо диапазона выдавала ошибку ЗНАЧ, но когда решил проверить, работает ли СМЕЩ с АГРЕГАТом записав мелкий вариант, увидел что СМЕЩ нормально даёт диапазон. Постепенно добавляя формулы в АГРЕГАТ я обнаружил что ошибка начинает выскакивать когда я в параметры "смещ_по_строкам/столбцам" добавляю формулы, в моём случае СТРОКА и СТОЛБЕЦ. Единственный выход который я смог найти, это вынести расчёт столбцов и строк в другие ячейки и уже на эти ячейки ссылать параметры смещ_по..., но всё же хотелось бы сделать формулу без использования дополнительных ячеек, так что прошу подскажите, кто знает, можно ли как то заставить СМЕЩ нормально работать с функциями СТРОКА и СТОЛБЕЦ.
Pelena, Нет, дело в том что в файле упрощённая версия, с той частью где ошибка возникает, ПОИСКПОЗ не позволял сделать то чего я хотел добиться, вот и перешёл на АГРЕГАТ. Вот как выглядит формула из основной таблицы
Как видите, тут есть критерии, которые некуда вставить в функцию ПОИСКПОЗ Я хочу вот эту часть переделать с использованием СМЕЩ,
Код
=($F$165:$F$219="Б")
т.к. в верхней формуле приходилось вручную сдвигать диапазон и менять букву "Б"
Pelena, Нет, дело в том что в файле упрощённая версия, с той частью где ошибка возникает, ПОИСКПОЗ не позволял сделать то чего я хотел добиться, вот и перешёл на АГРЕГАТ. Вот как выглядит формула из основной таблицы
Не убедили. Приложите более полный пример. Функцию СМЕЩ надо использовать только в крайних случаях, когда другие способы не работают. Из-за её волатильности
Не убедили. Приложите более полный пример. Функцию СМЕЩ надо использовать только в крайних случаях, когда другие способы не работают. Из-за её волатильностиPelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Pelena, так то да, можно вынести проблемный участок формулы что бы всё заработало, но как я писал в начале, хотелось бы не задействовать дополнительные ячейки, чисто из эстетических мотивов. поэтому и хочу узнать можно ли как то заставить работать СМЕЩ нормально
Код
=СМЕЩ($C$126;(СТОЛБЕЦ()-СТОЛБЕЦ($B$159))/3;)
, когда вставляю её в АГРЕГАТ. Что можно сделать с формулами СТОЛБЕЦ, что бы из-за них СМЕЩ не выдавал ошибку?
Pelena, так то да, можно вынести проблемный участок формулы что бы всё заработало, но как я писал в начале, хотелось бы не задействовать дополнительные ячейки, чисто из эстетических мотивов. поэтому и хочу узнать можно ли как то заставить работать СМЕЩ нормально
Код
=СМЕЩ($C$126;(СТОЛБЕЦ()-СТОЛБЕЦ($B$159))/3;)
, когда вставляю её в АГРЕГАТ. Что можно сделать с формулами СТОЛБЕЦ, что бы из-за них СМЕЩ не выдавал ошибку?ZetMenChavo
Pelena, вторая формула это мой изначальный вариант, где наличие формул СТОЛБЕЦ заставляет СМЕЩ выдавать ошибку, а вариант с ИНДЕКС работает, но ещё остается левая часть с диапазоном. Он по плану должен смещаться в право при протягивании ячейки с формулой вниз. Я для того и обратил внимание на СМЕЩ что смещение диапазона
Код
=$F$14:$F$68
можно сделать зависимым от строки, но там уже формула СТРОКА приводит к той же ошибке что и СТОЛБЕЦ.
Pelena, вторая формула это мой изначальный вариант, где наличие формул СТОЛБЕЦ заставляет СМЕЩ выдавать ошибку, а вариант с ИНДЕКС работает, но ещё остается левая часть с диапазоном. Он по плану должен смещаться в право при протягивании ячейки с формулой вниз. Я для того и обратил внимание на СМЕЩ что смещение диапазона
Код
=$F$14:$F$68
можно сделать зависимым от строки, но там уже формула СТРОКА приводит к той же ошибке что и СТОЛБЕЦ.ZetMenChavo
Сообщение отредактировал ZetMenChavo - Понедельник, 10.05.2021, 11:30
Pelena, Я заметил что, при вычислении, СТОЛБЕЦ()-СТОЛБЕЦ($B$8) становятся цифрами в фигурных скобках. Я заменял функции Столбцов в ячейке на числа без скобок в ручную, и СМЕЩ нормально становился диапазоном, а когда при помощи F9 превращал в ячейке СТОЛБЕЦ()-СТОЛБЕЦ($B$8) в {2}-{2}, то снова не работала. Похоже это из-за них. Что означают эти фигурные скобки, и как можно избавиться от них?
Pelena, Я заметил что, при вычислении, СТОЛБЕЦ()-СТОЛБЕЦ($B$8) становятся цифрами в фигурных скобках. Я заменял функции Столбцов в ячейке на числа без скобок в ручную, и СМЕЩ нормально становился диапазоном, а когда при помощи F9 превращал в ячейке СТОЛБЕЦ()-СТОЛБЕЦ($B$8) в {2}-{2}, то снова не работала. Похоже это из-за них. Что означают эти фигурные скобки, и как можно избавиться от них?ZetMenChavo
Участок _xlfn.SINGLE, то ли у меня версия старая что этой формулы нет, толи ещё что то, но в вашем файле у меня не заработало. В принципе вычисление столбцов и строк через ф.ЯЧЕЙКА решила мою проблему, но учту и ваши варианты, поэкспериментирую с новыми формулами, может где пригодятся.
А касательно моей проблемы, я не сильно разбираюсь как разрабы делали все эти формулы, но думаю понял что работало не так. АГРЕГАТ позволяет работать с массивами без использования Ctrl+Shift+Enter, т.е. все функции которые поддерживают работу массивов, автоматически считаются как массивы, без исключений. Функции СТОЛБЕЦ и СТРОКА одни из них, и хоть они были в ф.СМЕЩ, в целом они всё равно находились в АГРЕГАТе и выдавали результатом массив, даже если значение было всего одно, отсюда и брались цифры в фигурных скобках
Код
=СТРОКА(A1:A3)={1:2:3}
Код
=СТРОКА(А1)={1}
а СМЕЩ похоже не может использовать массивы для вычислений смещения ссылки или ширины с высотой, даже если значение всего одно, вот и выдавала ошибку #ЗНАЧ. А функция ЯЧЕЙКА наоборот не вычисляет массивы, поэтому и СМЕЩ принимает результаты вычисления этой функции.
Код
=ЯЧЕЙКА("строка";A1:A3)=1
Чтож, в целом Pelena спасибо, помогли отстраниться от корня проблемы и порассматривать другие варианты, что и помогло, а то со вчера сидел не мог разобраться
Участок _xlfn.SINGLE, то ли у меня версия старая что этой формулы нет, толи ещё что то, но в вашем файле у меня не заработало. В принципе вычисление столбцов и строк через ф.ЯЧЕЙКА решила мою проблему, но учту и ваши варианты, поэкспериментирую с новыми формулами, может где пригодятся.
А касательно моей проблемы, я не сильно разбираюсь как разрабы делали все эти формулы, но думаю понял что работало не так. АГРЕГАТ позволяет работать с массивами без использования Ctrl+Shift+Enter, т.е. все функции которые поддерживают работу массивов, автоматически считаются как массивы, без исключений. Функции СТОЛБЕЦ и СТРОКА одни из них, и хоть они были в ф.СМЕЩ, в целом они всё равно находились в АГРЕГАТе и выдавали результатом массив, даже если значение было всего одно, отсюда и брались цифры в фигурных скобках
Код
=СТРОКА(A1:A3)={1:2:3}
Код
=СТРОКА(А1)={1}
а СМЕЩ похоже не может использовать массивы для вычислений смещения ссылки или ширины с высотой, даже если значение всего одно, вот и выдавала ошибку #ЗНАЧ. А функция ЯЧЕЙКА наоборот не вычисляет массивы, поэтому и СМЕЩ принимает результаты вычисления этой функции.
Код
=ЯЧЕЙКА("строка";A1:A3)=1
Чтож, в целом Pelena спасибо, помогли отстраниться от корня проблемы и порассматривать другие варианты, что и помогло, а то со вчера сидел не мог разобраться ZetMenChavo
Сообщение отредактировал ZetMenChavo - Понедельник, 10.05.2021, 14:56
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
О! Я нашёл способ обойти проблему не отдаляясь от первичной задумки слишком далеко 
