Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Замена аргументов в формуле массива переменной или ссылкой - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Замена аргументов в формуле массива переменной или ссылкой (Формулы/Formulas)
Замена аргументов в формуле массива переменной или ссылкой
maverick_77 Дата: Среда, 01.07.2015, 10:43 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Добрый день.
Прошу помощи в решении такой задачи:
есть формула массива:
Код
{=НАИБОЛЬШИЙ(СТРОКА($1:$30)*ЕОШИБКА(ПОИСКПОЗ(СТРОКА($1:$30);B$1:B1;0));СЛУЧМЕЖДУ(1;30-СЧЁТ(B$1:B1)))}

Она возвращает в ячейку произвольное число от 1 до 30
Такие формулы стоят в некотором количестве ячеек, так, что во всех них результаты расчёта (произвольные числа) не повторяются и во всех эти числа выбираются из указанного диапазона (1 - 30).
Необходимо сделать так, чтобы верхняя граница диапазона произвольных чисел (в примере - 30) могла меняться. Например, в соответствии с тем, какое число показала формула в заданной ячейке

В прикреплённом примере необходимо чтобы формулы в ячейках B2:B31 возвращали произвольное значение из диапазона чисел от 1 до количества записей в списке (D2:D1000). В случае именно этого примера - до 166; или до результата расчёта формулы в ячейке D1
Можно ли как-то вместо цифры 30 в формулах поставить ссылку на D1? Или ещё как-то?...
К сообщению приложен файл: 4815817.xlsx (16.8 Kb)

если нельзя, но очень хочется, то можно!

Сообщение отредактировал maverick_77 - Среда, 01.07.2015, 10:43
 
Ответить
СообщениеДобрый день.
Прошу помощи в решении такой задачи:
есть формула массива:
Код
{=НАИБОЛЬШИЙ(СТРОКА($1:$30)*ЕОШИБКА(ПОИСКПОЗ(СТРОКА($1:$30);B$1:B1;0));СЛУЧМЕЖДУ(1;30-СЧЁТ(B$1:B1)))}

Она возвращает в ячейку произвольное число от 1 до 30
Такие формулы стоят в некотором количестве ячеек, так, что во всех них результаты расчёта (произвольные числа) не повторяются и во всех эти числа выбираются из указанного диапазона (1 - 30).
Необходимо сделать так, чтобы верхняя граница диапазона произвольных чисел (в примере - 30) могла меняться. Например, в соответствии с тем, какое число показала формула в заданной ячейке

В прикреплённом примере необходимо чтобы формулы в ячейках B2:B31 возвращали произвольное значение из диапазона чисел от 1 до количества записей в списке (D2:D1000). В случае именно этого примера - до 166; или до результата расчёта формулы в ячейке D1
Можно ли как-то вместо цифры 30 в формулах поставить ссылку на D1? Или ещё как-то?...
Автор - maverick_77
Дата добавления - 01.07.2015 в 10:43
Pelena Дата: Среда, 01.07.2015, 11:05 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19517
Репутация: 4632 ±
Замечаний: ±

Excel 365 & Mac Excel
Здраствуйте.
Например, так
Код
=НАИБОЛЬШИЙ(СТРОКА(ДВССЫЛ("$1:$"&$D$1))*ЕОШИБКА(ПОИСКПОЗ(СТРОКА(ДВССЫЛ("$1:$"&$D$1));B$1:B1;0));СЛУЧМЕЖДУ(1;$D$1-СЧЁТ(B$1:B1)))

или с доп. столбцом без формул массива (см. второй лист в файле)
К сообщению приложен файл: 8077318.xlsx (30.0 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдраствуйте.
Например, так
Код
=НАИБОЛЬШИЙ(СТРОКА(ДВССЫЛ("$1:$"&$D$1))*ЕОШИБКА(ПОИСКПОЗ(СТРОКА(ДВССЫЛ("$1:$"&$D$1));B$1:B1;0));СЛУЧМЕЖДУ(1;$D$1-СЧЁТ(B$1:B1)))

или с доп. столбцом без формул массива (см. второй лист в файле)
Автор - Pelena
Дата добавления - 01.07.2015 в 11:05
AlexM Дата: Среда, 01.07.2015, 11:27 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
В формуле фрагмент
Код
СТРОКА($1:$30)
можно заменить на
Код
=СТРОКА($A1:ИНДЕКС(A:A;$D$1))


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеВ формуле фрагмент
Код
СТРОКА($1:$30)
можно заменить на
Код
=СТРОКА($A1:ИНДЕКС(A:A;$D$1))
Автор - AlexM
Дата добавления - 01.07.2015 в 11:27
Макрописец Дата: Среда, 01.07.2015, 11:37 | Сообщение № 4
Группа: Заблокированные
Ранг: Форумчанин
Сообщений: 128
Репутация: 7 ±
Замечаний: 100% ±

Excel 2010
Цитата AlexM, 01.07.2015 в 11:27, в сообщении № 3
можно заменить на

И сделать формулу длиннее.? %)

ТЕРПЕНИЕ И ТРУД - ВСЁ ПЕРЕТРУТ!
 
Ответить
Сообщение
Цитата AlexM, 01.07.2015 в 11:27, в сообщении № 3
можно заменить на

И сделать формулу длиннее.? %)
Автор - Макрописец
Дата добавления - 01.07.2015 в 11:37
Pelena Дата: Среда, 01.07.2015, 11:40 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 19517
Репутация: 4632 ±
Замечаний: ±

Excel 365 & Mac Excel
Макрописец, предложите свой вариант решения без удлиннения формулы

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеМакрописец, предложите свой вариант решения без удлиннения формулы
Автор - Pelena
Дата добавления - 01.07.2015 в 11:40
Макрописец Дата: Среда, 01.07.2015, 11:52 | Сообщение № 6
Группа: Заблокированные
Ранг: Форумчанин
Сообщений: 128
Репутация: 7 ±
Замечаний: 100% ±

Excel 2010
Pelena, см. Тут короче.

ТЕРПЕНИЕ И ТРУД - ВСЁ ПЕРЕТРУТ!
 
Ответить
СообщениеPelena, см. Тут короче.
Автор - Макрописец
Дата добавления - 01.07.2015 в 11:52
_Boroda_ Дата: Среда, 01.07.2015, 12:02 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16895
Репутация: 6612 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Цитата maverick_77, 01.07.2015 в 10:43, в сообщении № 1
есть формула массива:

Где-то я ее уже видел :D
Цитата Макрописец, 01.07.2015 в 11:37, в сообщении № 4
И сделать формулу длиннее.?

Скорость работы формулы в общем случае практически никак не зависит от ее (формулы) длины.
Ну вот чуть покороче, но принцип не изменен
Код
=НАИБОЛЬШИЙ(СТРОКА(ДВССЫЛ("1:"&D$1))*ЕОШИБКА(ПОИСКПОЗ(СТРОКА(ДВССЫЛ("1:"&D$1));B$1:B1;0));СЛУЧМЕЖДУ(1;D$1-A2))

Цитата AlexM, 01.07.2015 в 11:27, в сообщении № 3
=СТРОКА($A1:ИНДЕКС(A:A;$D$1))

Леш, ты доллар туда засунул? - не $A1, а A$1
Код
=НАИБОЛЬШИЙ(СТРОКА(ДВССЫЛ("1:"&D$1))*ЕОШИБКА(ПОИСКПОЗ(СТРОКА(ДВССЫЛ("1:"&D$1));B$1:B1;0));СЛУЧМЕЖДУ(1;D$1-A2))
К сообщению приложен файл: 4815817_1.xlsx (17.6 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата maverick_77, 01.07.2015 в 10:43, в сообщении № 1
есть формула массива:

Где-то я ее уже видел :D
Цитата Макрописец, 01.07.2015 в 11:37, в сообщении № 4
И сделать формулу длиннее.?

Скорость работы формулы в общем случае практически никак не зависит от ее (формулы) длины.
Ну вот чуть покороче, но принцип не изменен
Код
=НАИБОЛЬШИЙ(СТРОКА(ДВССЫЛ("1:"&D$1))*ЕОШИБКА(ПОИСКПОЗ(СТРОКА(ДВССЫЛ("1:"&D$1));B$1:B1;0));СЛУЧМЕЖДУ(1;D$1-A2))

Цитата AlexM, 01.07.2015 в 11:27, в сообщении № 3
=СТРОКА($A1:ИНДЕКС(A:A;$D$1))

Леш, ты доллар туда засунул? - не $A1, а A$1
Код
=НАИБОЛЬШИЙ(СТРОКА(ДВССЫЛ("1:"&D$1))*ЕОШИБКА(ПОИСКПОЗ(СТРОКА(ДВССЫЛ("1:"&D$1));B$1:B1;0));СЛУЧМЕЖДУ(1;D$1-A2))
Автор - _Boroda_
Дата добавления - 01.07.2015 в 12:02
AlexM Дата: Среда, 01.07.2015, 12:10 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Цитата _Boroda_, 01.07.2015 в 12:02, в сообщении № 7
Леш, ты доллар туда засунул?
Не туда. Ошибся. Не проверил, из-за отсутствия в 2003 функции СЛУЧМЕЖДУ(). Конечно надо так
Код
=СТРОКА(A$1:ИНДЕКС(A:A;D$1))
Цитата Макрописец, 01.07.2015 в 11:37, в сообщении № 4
И сделать формулу длиннее.?
Зато не летучая.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Среда, 01.07.2015, 12:12
 
Ответить
Сообщение
Цитата _Boroda_, 01.07.2015 в 12:02, в сообщении № 7
Леш, ты доллар туда засунул?
Не туда. Ошибся. Не проверил, из-за отсутствия в 2003 функции СЛУЧМЕЖДУ(). Конечно надо так
Код
=СТРОКА(A$1:ИНДЕКС(A:A;D$1))
Цитата Макрописец, 01.07.2015 в 11:37, в сообщении № 4
И сделать формулу длиннее.?
Зато не летучая.
Автор - AlexM
Дата добавления - 01.07.2015 в 12:10
maverick_77 Дата: Среда, 01.07.2015, 12:54 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Цитата _Boroda_, 01.07.2015 в 12:02, в сообщении № 7
Где-то я ее уже видел

Да-да!... =))) только не в той ветке, что вы указали, а прямо мне подсказывали. За что Вам ещё раз спасибо =)

Макрописец, для меня длина формулы в данном случае не имеет значения. Во всяком случае - её удлинение настолько.
Поэтому, AlexM, Pelena, _Boroda_, спасибо за предложенные варианты!

И если не сложно, поясните разницу между вариантами
Код
СТРОКА(A$1:ИНДЕКС(A:A;D$1))

и
Код
СТРОКА(ДВССЫЛ("1:"&D$1)  /  СТРОКА(ДВССЫЛ("$1:$"&$D$1)

если нельзя, но очень хочется, то можно!

Сообщение отредактировал maverick_77 - Среда, 01.07.2015, 12:55
 
Ответить
Сообщение
Цитата _Boroda_, 01.07.2015 в 12:02, в сообщении № 7
Где-то я ее уже видел

Да-да!... =))) только не в той ветке, что вы указали, а прямо мне подсказывали. За что Вам ещё раз спасибо =)

Макрописец, для меня длина формулы в данном случае не имеет значения. Во всяком случае - её удлинение настолько.
Поэтому, AlexM, Pelena, _Boroda_, спасибо за предложенные варианты!

И если не сложно, поясните разницу между вариантами
Код
СТРОКА(A$1:ИНДЕКС(A:A;D$1))

и
Код
СТРОКА(ДВССЫЛ("1:"&D$1)  /  СТРОКА(ДВССЫЛ("$1:$"&$D$1)
Автор - maverick_77
Дата добавления - 01.07.2015 в 12:54
AlexM Дата: Среда, 01.07.2015, 13:10 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Цитата maverick_77, 01.07.2015 в 12:54, в сообщении № 9
поясните разницу между вариантами

Волатильные (Пересчитываемые или Летучие) функции.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Среда, 01.07.2015, 13:13
 
Ответить
Сообщение
Цитата maverick_77, 01.07.2015 в 12:54, в сообщении № 9
поясните разницу между вариантами

Волатильные (Пересчитываемые или Летучие) функции.
Автор - AlexM
Дата добавления - 01.07.2015 в 13:10
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Замена аргументов в формуле массива переменной или ссылкой (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2025 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!