[решено] Как добавить вторую колонку с автоматическим расчетом процентов?

[Avatar-Lion]

Excel как-то странно себя ведет. Первую колонку с шагом в 0,5 он посчитал нормально. Я ввел сначала 1,0, потом 1,5, далее потянул выбранный столбик вниз и он сам уже добавил все последующие цифры (2,0 и т.д.). Для второй колонки нужно прибавить +7%. Я вручную вбил формулы и он прибавил проценты. После третьей ячейки я подумал, что он поймет чего я от него хочу и аналогичным образом потянул столбик вниз, но вместо того, чтобы рассчитывать проценты из первого столбца, он просто начал дублировать первые три значения, которые я вбивал вручную:
https://a.radikal.ru/a30/2111/87/32d37d0bc65e.png (https://radikal.ru)

Как сделать так, чтобы вторая колонка автоматом добавляла +7% к первой колонке?

[okshef]

Avatar-Lion, лучше файл, чем фото.
Приложите, посмотрим

[megaloman]

Avatar-Lion, Чтобы отобразить формулы в ячейках, нажмите сочетание клавиш Ctrl+` (маленький значок — это значок тупого ударения). Рассмотрите, что за формулы у Вас получились. Покажите. Если руководствоваться Вашей постановкой, третье значение 4.07 кривое (должно быть 2,14000).
Я в ячейке правее 1. написал расчетную формулу, протянул вниз, у меня всё прекрасно. Ищите ошибку в своих действиях. :search:

[Avatar-Lion]

лучше файл, чем фото. »
Не вопрос, держите: http://178.173.19.2:1987/Additional/1.xlsx

megaloman, Да, я там случайно плюс поставил в одной ячейке вместо умножения. Сейчас исправил, но это не помогло. Если хотите, можете сами посмотреть, файл по ссылке выше.

[megaloman]

Avatar-Lion, Что написали, то и получили.=$A$2+$A$2*7%
=$A$3+$A$3*7%
=$A$4+$A$4*7%
При абсолютной адресации вида $A$3 атот адрес пересчитываться не будет. Это абсолютная ссылка. Соответственно, когда вы ее размножили, она размножилась безо всякого изменения.
Хотите, чтобы она пересчитывалась, пишИте A3.

[Iska]

Avatar-Lion, при копировании, заполнении, размножении корректируются автоматически только относительные ссылки.

В Вашем случае я бы предложил в ячейку B1 ввести 107%, а в ячейку B2 внести формулу «=$A2*$B$1»:
https://i.imgur.com/j4eGwmq.png
Тогда при размножении вниз она будет корректироваться в «=$A3*$B$1», «=$A4*$B$1», «=$A5*$B$1» и т.д.

[Avatar-Lion]

megaloman, Погодите, откуда вы это берете все? У меня в левом верхнем углу показываются совсем другие адреса ячеек: R2C1 и т.д.

[Iska]

Avatar-Lion, \Параметры\Общие, снять флажок Стиль ссылок R1C1. Можно и с таким стилем работать, но он не так нагляден.

[Avatar-Lion]

Iska, А, вот оно в чем дело... Да, нашел такую галочку в параметрах, только не в общих, а в формулах. Но не суть. Теперь ячейки имеют такие же номера как у вас. Всё получилось, спасибо большое, теперь проценты высчитываются правильно. И еще, может подскажете... Можно ли теперь полученный результат отсортировать по наименьшему значению в дробной части? Сейчас оно идет по возрастанию:
1,07000
1,60500
2,14000
2,67500
3,21000
3,74500

Ну и т.д. Но мне нужна иная сортировка, чтобы было так:
1,07000
2,14000
3,21000
1,60500
2,67500
3,74500

[Iska]

Ещё один столбец с формулой:
=ЦЕЛОЕ(ОСТАТ($B2;1)*100)
И сортируйте по нему:
https://i.imgur.com/B87ohRX.png

[Avatar-Lion]

Iska, А, вот вы как предлагаете... Любопытно. Да, это вполне рабочий вариант, спасибо. Правда, смущает странное округление, которое выполняет Excel. Для большинства результатов он округляет новую колонку нормально, но для каких-то отдельных чисел он показывает на одну тысячную меньше. Например, для числа 20,330 показывается остаток не 330, а 329. Куда единичка подевалась?

P.S. Файл по ссылке обновил на всякий случай, чтобы вы сами могли посмотреть: http://178.173.19.2:1987/Additional/1.xlsx

[megaloman]

Мой монструозный вариант.

[Avatar-Lion]

megaloman, Ну вы даете :) Я вообще ничего не понял... Во-первых, ваш вариант сортируется по возрастанию как-то очень уж странно, я никакой логики не вижу. Во-вторых, дробная часть явно не соответствует действительности: 1 и 1,07 - тут все правильно, но разница ведь составляет 0,07. У вас же откуда-то берется 0,14.

[Iska]

Правда, смущает странное округление, которое выполняет Excel. »
А это не округление, это отбрасывание. Про округление речи не было. Даже в Вашем примере :).

[Avatar-Lion]

Iska, Почему тогда такая избирательность? Чем ему не угодили именно те цифры?

[megaloman]

Avatar-Lion, я никакой логики не вижу »Логика не при чём. Тупая технология.
В столбце B (в примере в диапазоне $B$3:$B$41) занесены последовательно дробные части, как Вы это обозвали, базовой цены.
Базовая цена вычисляется как B=1+0.5*(i-1) где i=1,2, ... и от этого берётся дробная часть как остаток от деления B/1
В столбце C в ячейках столбца B последовательно ищутся наименьшие 1, 2 , 3 значения и их позиция в этом массиве чисел. По найденной позиции вычисляется значение базовой цены. По базовой цена в столбце D умножается на 1,07. То есть что Вы требовали, если я правильно понял, Вы получили. Базовые цены, умноженные на проценты, рассортированы по возрастанию дробной части.
Еще раз: в столбце B дробные части базовой цены, умноженной на проценты, расположены по возрастанию базовой цены.
В столбце С базовая цена уже расположена по возрастанию дробной части сумм, умноженных на проценты, Очевидно, дробная часть в суммах в столбце D идёт по возрастанию, что мы так упорно добивались .
Столбец B - служебный.
Не нравится - скройте его.
Не уверен, что смогу решить задачку без служебного столбца. Смысла как-то в ней не сильно вижу, хотелось бы понять, зачем это нужно.
Макрос - и флаг в руки, если это не разовая акция.

[Avatar-Lion]

megaloman, Да всё, всё, уже въехал. Спасибо за ваш вариант. Но товарищ Iska тоже толковую идею предложил: показывать сам остаток как таковой. Возможно, у меня даже получится совместить оба варианта для удобства и наглядности.

Что касается того, зачем это нужно... В игре This War of Mine в режиме продажи не показываются цены на товары, они скрыты разработчиками намеренно, чтобы добиться нужной атмосферы в игре, когда ты не знаешь сколько стоит та или иная вещь, но ты можешь примерно обменяться с торговцем. Но очевидно, что сама-то игра как-то всё же дает добро или отказ на совершение сделки. В итоге с помощью редактора сценариев удалось-таки узнать внутриигровые цены на предметы, хотя по факту в режиме торговли все цены делятся на два. Не знаю зачем это надо, я не программист. Например, в редакторе сценариев пистолет стоит 43 единицы, а в игре он будет идти по цене 21,5. Патроны - 7 в редакторе и 3,5 в игре. Ну и т.д. Также есть правило: предложение игрока всегда должно быть больше предложения торговца, т.е. торговать "один к одному" нельзя, всегда нужно переплачивать. Шаг стоимости составляет 0,5 единиц, т.е. при торговле хочешь-нехочешь, а переплатить 0,5 стоимости придется по-любому. Но! Помимо прочих персонажей, имеется два особенных, один из которых дает +7% к продажной цене, а другой +20% (это было уже установлено эмпирическим методом). Соответственно, появляется возможность сэкономить, т.к. игре важен сам факт переплаты, а уж на сколько эта переплата будет больше - на 0,5 или на 0,2 - уже не важно.

Всего доступно 8 слотов у тебя и 8 слотов у продавца. Процедура обмена выглядит следующим образом: я накидываю 8 товаров, вспоминая стоимость каждого из них, т.е. "цена из редактора, поделенная на два". Если товары идентичные, то они обычно складываются в стопки, хотя у каждого товара есть свои пределы по количеству предметов в одном слоте. Например, бутылка самогона стоит 13,5 и продается только поштучно, а вот сигареты стоят 2,0, зато их можно по двадцать штук складывать... Короче, считается общая сумма всех своих товаров, затем набираем аналогичную кучу (по стоимости, а не по типу предметов) у продавца и сделка, наконец, заключается.

Как вы понимаете, сей механизм не очень удобен в эксплуатации, хотя со временем ты запоминаешь цены на ходовые предметы и подглядывать в бумажку на столе практически не требуется. Конечно, в случае с добавлением в +20% всё усложняется, но поскольку прибавить двадцать процентов - это то же самое, что поделить на пять, высчитать в уме итоговую сумму всё-таки можно. Но вот с 7% всё печально, цифра неровная и удержать в уме единовременно столько цифр уже не получается. Это и навело меня на мысль: не пересчитывать каждый раз вручную все возможные комбинации, а сразу задать диапазон от 1,0 до 150,0 (больше этой суммы напихать предметов в 8 слотов едва ли получится), в рамках которого и будет осуществляется торговля. Соответственно, появится возможность узнать точки минимальной переплаты. Ну и дальше нужно будет просто набрать товаров на нужную сумму, заранее зная результат и имея уверенность в том, что переплата выйдет минимальной.

[Iska]

Iska, Почему тогда такая избирательность? Чем ему не угодили именно те цифры? »
Проблема в том, что в число дробная часть числа в машинном представлении всегда хранится с ограниченной точностью. И, в каких-то случаях, точности достаточно для правильного и точного представления числа, в большинстве же случаев — нет. Например, 20.33 не может быть точно представлено в машинном представлении (в двоичном представлении там будет бесконечная периодическая дробь). Для ликвидации последствий этого в Microsoft Excel, как и в большинстве других подобных приложений, используются специальные алгоритмы, выполняющие коррекцию при необходимости. Но они не всегда срабатывают.

Если Вы встанете на ячейку C38, нажмёте F2, выделите в формуле функцию Рабочего листа «ОСТАТ($B38;1)» и нажмёте F9 — то увидите, где алгоритмы не сработали и начинались проблемы:
https://i.imgur.com/gwH1UoD.png
Давайте подучаем, что можно сделать.

Вы ещё писали про округление — поясните подробнее, пожалуйста.

[Avatar-Lion]

Iska, Хм-м... Как-то в голову даже не могло прийти, что компьютер (!) может иметь какие-то проблемы с числами... Ладно. Собственно, говоря про округление, я говорил именно об этом: меня удивило сокращение на единичку, когда 330 превратилось 329. Это же первое правило округления: числа до 5 округляются в меньшую сторону, а выше 5 - в большую. Я мог бы понять, если бы 330 превратилось ровно в 300, но когда увидел 329... Для меня это выглядело как этакое неправильное округление или "округление со знаком минус". Но раз вы говорите, что это просто недоработка самой системы, то и хрен с ней. Я и так получил даже больше, чем рассчитывал. Большое вам спасибо! И вам, мистер megaloman, тоже спасибо.

Думаю, тему я отмечу решенной, поскольку таблицу с расчётами я получил, а некоторую погрешность в расчетах, которую внёс Excel, во внимание принимать пока не будем. Сама игра (This War of Mine) наверняка имеет ещё более хреновые механизмы компенсации ошибок в подобных расчётах, поэтому мучить непосредственно сам Excel я смысла не вижу. Еще раз всем спасибо.

[megaloman]

Iska, А зачем нужна махинация с превращением дробной части в целое? Что мешает сортировать по дробной части числа после ОСТАТ от деления на 1?
Прикладываю еще вариант, более общий: исходные числа могут следовать случайным образом.