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

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


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

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

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

Не вопрос, держите: http://178.173.19.2:1987/Additional/1.xlsx

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

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

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

В Вашем случае я бы предложил в ячейку B1 ввести 107%, а в ячейку B2 внести формулу «=$A2*$B$1»:

Тогда при размножении вниз она будет корректироваться в «=$A3*$B$1», «=$A4*$B$1», «=$A5*$B$1» и т.д.

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

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

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

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

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

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

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

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

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 - служебный.
Не нравится - скройте его.
Не уверен, что смогу решить задачку без служебного столбца. Смысла как-то в ней не сильно вижу, хотелось бы понять, зачем это нужно.
Макрос - и флаг в руки, если это не разовая акция.

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 слотов едва ли получится), в рамках которого и будет осуществляется торговля. Соответственно, появится возможность узнать точки минимальной переплаты. Ну и дальше нужно будет просто набрать товаров на нужную сумму, заранее зная результат и имея уверенность в том, что переплата выйдет минимальной.

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

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

Давайте подучаем, что можно сделать.

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

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

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

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

А мне вообще непонятно как компьютер может хранить что-то с "ограниченной точностью". :) Там же просто нули и единицы. Не может же быть какого-то промежуточного состояния между этими двумя значениями.

Avatar-Lion, Когда-то компьютер занимали зал и работал всего с 7 десятичными знаками. Иногда приходилось учитывать, что в системе от перемены мест слагаемых сумма изменяется.

megaloman, В прошлом веке - да, возможно. Когда на лампочках всё было. Где-то напряжение просело - и опаньки, единица уже не совсем единица, а ближе к нолику. Ну, я так это вижу. Но сейчас-то подобное немыслимо. Все переводится в двоичный код. Если бы где-то нули и единицы в чехарду играть стали, то компы по всему миру каждый день валились бы в синий экран смерти и прочие чудачества исполняли.

Avatar-Lion, вот Вам простой вариант — как точно представить в десятичном виде результат деления 10 на 3? Ответ — никак нельзя, потому что получается периодическая десятичная дробь 3,3(3). А в троичной арифметике — можно: 10₁₀=101₃, 3₁₀=10₃, 101₃:10₃=10.1₃, десять целых и одна третья :).

Что нужно знать про арифметику с плавающей запятой / Хабр

А хрен его знает. Думал, что сие автору нужно в таком виде. В нашем случае проблема возникает именно при использовании функции Рабочего листа ОСТАТ() :), так что без разницы, превращать, або нет. Надо переделать без неё.

Avatar-Lion, используйте следующую формулу:

Avatar-Lion, вариант через формулу массива без дополнительного столбца. Формула массива утяжеляет пересчет, но в вашем случае объемы небольшие, так что это не должно создать проблем. Обратите внимание, что данные отформатированы, как таблица (по ctrl+L), поэтом не имеет значения, с каким стилем ссылок вы работаете - А1 или R1C1.

Цитата:

Цитата Iska Можно и с таким стилем работать, но он не так нагляден. »

Ну, тут не совсем с Вами согласен:

Скрытый текст

Уже много лет работаю в пространстве имен R1C1 именно из-за наглядности. Строго говоря R1C1 используется в реальной жизни почти всегда - "я сидел в кино на следующем ряду, на два кресла левее" - это стиль R1C1. "Картину нужно повесить правее на два сантиметра" - также R1C1.

В стиле А1 невозможно описать относительную ссылку на ячейку без того, чтобы обязательно не указать обе ссылки в абсолютных координатах (речь не про символ $ в названии, а про то, что G14 и $G$14 ссылаются на одну и ту же ячейку, вся относительность выражается исключительно в том, как эту ссылку обрабатывает Excel). Не зная, какая ячейка текущая, нельзя сказать, где относительно нее G14 - справа, слева, или совпадает (Сравните: "передвинь картину на С4, а сейчас она на С3", или там "в метро мы ехали в одной электричке, только я в 7-ом вагоне, а он в 9-ом" - номер вагона в метро обычно никому не интересен).

Если я хочу сослаться на одну ячейку левее текущей, для того чтобы написать формулу в А1, я должен знать текущий столбец, в R1C1 это всегда будет =RС[-1], при этом абсолютно неважно, какой столбец текущий - работать будет даже в ячейке с адресом А1. Ссылка на текущую ячейку - всегда RC (используется в том же условном форматировании). Если нужна ячейка третья влево, именно это нужно написать - RC[-3] - три ячейки влево, а не абсолютный адрес этой ячейки, как в А1.

Второй момент - я всегда могу скопировать текст формулы (например - часть формулы) и вставить в другие координаты - и все будет работать. В стиле А1 можно скопировать всю ячейку, а вот с частью формулы такое не получится.

Не могу сказать, что это великий камень преткновения, но поработав в обоих системах R1C1 мне все же понятнее - это же обычная матричная система из высшей математики - матрица (mxn), ну или Декартова система координат, повернутая на 90 градусов :)