Страницы: 1
RSS
Получение индекса строки из таблицы значений по относительно сложному условию, суммировать значения, пока не будет достигнуто или превышено значение случайного числа
 
Есть таблица значений, есть генератор случайных чисел, надо найти строку Икс.
Строка Икс — это та строка, до которой нужно суммировать значения (начиная сверху) таблицы, чтобы получить число равное или минимально превышающее значение случайного числа.

Значения в таблице могут быть любыми, в любом порядке.
Использование дополнительных столбцов и ячеек нежелательно.
Очень бы хотелось обойтись без макроса.

Microsoft Office 365, версия 1810 (сборка 11001.20108)
Изменено: Евгений Сергеевич - 06.12.2018 20:33:16 (Дополнение условий.)
 
Попробуйте, может так? Точнее, лучше так (файл перезалил), см формулу в желтой ячейке.
Изменено: Пытливый - 06.12.2018 17:20:56
Кому решение нужно - тот пример и рисует.
 
Нет, надо без столбца с суммированием.
 
Евгений Сергеевич, покажите результат который должен быть для конкретного числа

так?
Код
=СУММ($A$2:ИНДЕКС(Таблица1[[#Данные];[#Итоги];[Значения]];ПОИСКПОЗ($C$2;Таблица1[[#Данные];[#Итоги];[Значения]];0);1))
Изменено: Mershik - 06.12.2018 17:38:19
Не бойтесь совершенства. Вам его не достичь.
 
Цитата
Mershik написал:
результат который должен быть для конкретного числа
Допустим:
  • случайное число 1 — тогда индекс должен быть 1, так как первое же значение из таблицы равняется значению случайного числа (1 >= 1);
  • случайное число 2 — тогда индекс должен быть 2, так как первого значения недостаточно, чтобы сравниться со случайным числом или минимально превзойти его, нужно добавить второе значение (3 >= 2 && 2 > 1);
  • случайное число 68 — тогда индекс должен быть 12, так как для того, чтобы сравниться или минимально превзойти случайное число, необходимо суммировать первые 12 значений из таблицы (78 >= 68 && 68 > 66).
Более наглядно это видно в файле Пытливого, но необходимо исключить создание новых строк и столбцов.
Изменено: Евгений Сергеевич - 06.12.2018 17:51:55 (Исправление ошибки.)
 
Цитата
Mershik написал:
так?
Нет.

На всякий случай добавлю, что значения в таблице могут быть любыми. Больших цифр там, скорее всего не будет, но они могут идти в любом порядке с любыми интервалами.
 
Решение могло бы быть возможно, если бы имелась функцию цикличного повторения действий с нарастающим значением и дающим на выходе массив данных. Размер массива определялся бы по количеству строк из таблицы, а каждое значение вычислялось бы с использованием индекса строки.
 
попробуйте такой вариант массивная
Код
=ПОИСКПОЗ(C2;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(A2;;;СТРОКА(A2:A32)-1;));1)+1
Лень двигатель прогресса, доказано!!!
 
Цитата
Сергей написал:
попробуйте такой вариант массивная
#Н/Д — совсем не работает.

В процессе поиска решения столкнулся со следующей проблемой.
Если я пишу так, то формула работает:
Код
=ИНДЕКС({19:19:19:19:19:19:19:19:19};1;1)

А если так, то почему не работает?:

Код
=ИНДЕКС("{"&ПОВТОР("19:";8)&"19}";1;1)

Как из строки получить массив? Возможно, это поможет мне решить данную задачу.
 
Цитата
Евгений Сергеевич написал:
совсем не работает.
Работает. Смотрите файл.
Мой вариант:
=ПОИСКПОЗ(C2;МУМНОЖ(--(СТРОКА(A2:A32)>=ТРАНСП(СТРОКА(A2:A32)));A2:A32);1)+1
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Bema, вы просто магистр Экселя.
Ребята, всех благодарю за помощь.
Осталось только овладеть этим методом, чтобы в будущем применять его самостоятельно.
Изменено: Евгений Сергеевич - 07.12.2018 22:45:42 (Изменил «Ты» на «Вы».)
 
Немного математики (сумма ряда и квдратные уравнения):
=ОКРУГЛВВЕРХ(((1+8*C2)^0,5-1)/2+0,01;)
Формула применима для натурального ряда.

Если ряд натуральных чисел начинается не с 1, в В2 указываем первое число ряда
=ОКРУГЛВВЕРХ(((1+8*C2)^0,5-1)/2+0,01;)-ОКРУГЛВВЕРХ(((1+8*(B2-1))^0,5-1)/2+0,01;)
Можно все обвернуть одной ОКРУГЛВВЕРХ. Если правильно сократил:
=ОКРУГЛВВЕРХ(((1+8*C2)^0,5-(1+8*(B2-1))^0,5)/2+0,01;)
 
vikttur, к сожалению, не работает.

Формула Bema тоже работает не с нужной точностью. К примеру, 1 должна относится к первой позиции, а не ко второй, если только в первой позиции не 0. Но его формула умеет собирать массив правильных значений — до сих пор я и понятия не имел, что это возможно в экселе.
Код
=МУМНОЖ(--(СТРОКА(Таблица1[Количество])>=ТРАНСП(СТРОКА(Таблица1[Количество])));Таблица1[Количество])
Более точное же определение позиции есть во второй формуле Пытливого.
Код
=ЕСЛИ(ИНДЕКС(Таблица1[Сумма вместе с предыдущими];ПОИСКПОЗ(C2;Таблица1[Сумма вместе с предыдущими];1);1)=C2;ПОИСКПОЗ(C2;Таблица1[Сумма вместе с предыдущими];1);ПОИСКПОЗ(C2;Таблица1[Сумма вместе с предыдущими];1)+1)
Но его формула не умеет самостоятельно собирать массив, поэтому Пытливый сделал массив в отдельном столбце.
Я объединил две эти формулы и получил очень длинную строку.
Код
=ЕСЛИ(ИНДЕКС(МУМНОЖ(--(СТРОКА(Таблица1[Количество])>=ТРАНСП(СТРОКА(Таблица1[Количество])));Таблица1[Количество]);ПОИСКПОЗ(C2;МУМНОЖ(--(СТРОКА(Таблица1[Количество])>=ТРАНСП(СТРОКА(Таблица1[Количество])));Таблица1[Количество]);1);1)=C2;ПОИСКПОЗ(C2;МУМНОЖ(--(СТРОКА(Таблица1[Количество])>=ТРАНСП(СТРОКА(Таблица1[Количество])));Таблица1[Количество]);1);ПОИСКПОЗ(C2;МУМНОЖ(--(СТРОКА(Таблица1[Количество])>=ТРАНСП(СТРОКА(Таблица1[Количество])));Таблица1[Количество]);1)+1)
Но позиция стала высчитываться точно за исключением ситуации с нулевыми значениями.
Свои тесты и их результаты я оформил в новый документ, который прикладываю к этому посту. Приведённые выше формулы имеют связь именно с ним.
Остаётся решить проблему с правильным определением позиции при нулевых значениях. Буду рад помощи.
 
Евгений Сергеевич, я позже дополнил сообщение:
Цитата
Формула применима для натурального ряда.
В Вашем примере - для столбца С.
 
Да, проверил. Результаты добавил в таблицу.
Я вижу, что ваша формула работает очень точно, но по несколько иным правилам. Нужно чтобы переход на следующую позицию осуществлялся не при достижении случайного числа следующей позиции, а при превышении случайного числа настоящей позиции. Так как вы, я математику не знаю, поэтому ваши формулы точно никак поправить не смогу.
 
Цитата
Евгений Сергеевич написал: переход на следующую позицию... при превышении случайного числа
Уберите из формулы 0,01
 
Нет, при изменении значений первой колонки (напоминаю, что там могут быть самые разные величины) на другие, ваша, vikttur, формула перестаёт работать. Невозможно по одному ли рандомному числу определить какие величины суммировались и в какой позиции это случайное число оказалось.
Прикрепляю новый документ, в нём всё пересчитано.

На всякий случай хочу привести аналогию. Допустим у нас книжный магазин и стоит стопка книг на продажу, сортированных по названию, а так же есть список с указанием каких книг столько в этой стопке, соблюдая порядок — это первая колонка. Далее мы отсчитываем от верха например 10 и берём десятую книгу — это случайное число. Смотрим на название — это индекс. То есть, в первой колонке указывается количество объектов, и нужно получить индекс объекта, в который попадает случайное число.
 
Евгений Сергеевич, третий раз:
Цитата
Формула применима для натурального ряда
Тема останется. Возможно, эта формула поможет другим. В Вашем же примере формула будет работать только со столбцом С. Точнее - с  рядом натуральных чисел.
Математика. 5 класс
 
Евгений Сергеевич, попробуйте просто так
=MATCH(C2;MMULT(--(ROW(Таблица1[Значения])>TRANSPOSE(ROW(Таблица1[Значения])));Таблица1[Значения]+9^-9))
=ПОИСКПОЗ(C2;МУМНОЖ(--(СТРОКА(Таблица1[Значения])>ТРАНСП(СТРОКА(Таблица1[Значения])));Таблица1[Значения]+9^-9))

Я запутался в ваших примерах но учитывает и 0 и то что сумма должна быть равна или больше
По вопросам из тем форума, личку не читаю.
 
БМВ, это то, что надо. Только что закончил тестирование и ошибок не обнаружил.
Благодарю вас.
 
Цитата
Евгений Сергеевич написал:
А что означает этот элемент? 9^-9 Почему именно эти цифры и именно эта операция с ними?
Это 9 в степени -9 - что является самым короткой записью очень маленького числа.  0,0000000026 .  Если нет борьбы за количество символов в формуле то можено 10^-9 Или 10^-10 … но сильно не увлекайтесью.  Прибавляется она для того, чтоб при равенстве, например 1 и 1, мы взяли другое значение. Конечно есть вероятность совпадения и при таком раскладе, но малая.
Так как я убрал из сравнения знак равенства, то все значения сумм идут как бы со сдвигом и начинаются от 0.
В результате получается последовательность 0; 2,5811747917132E-09; 7,00000000516235; 7,00000000774352 для последовательности 0;7;0; 1
берем 0 .  это первое значение.
0,5 - оно больше 2,5811747917132E-09 значит будет 2 а так как мы хотим получить равное или большее, то это сумма 0 и 7
Для того чтоб был сбой нужно чтоб то что ищем было больше 0, но меньше 2,5811747917132E-09
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх