» Excel FAQ (часть 2)

Pazan
Выкладывай на любой файловый обменник: например, на mytempdir.com

Цитата:

цена падает постепенно, с каждым увеличением количества на 1 до 1000, т.е. 1000 шт. уже на 50% дешевле 1 штуки. Потом с 1001 до 3000 цена опускается ещё на 1% с каждой 1,а с 3001 по 5000 - ещё на 1%. В результате 5000 на 52% дешевле 1 шт.

А вот тут я ничего не понял Причем здесь постепенное падение цены? Изначально была задача, что от определенного (фиксированного) количества рассчитывается цена. если количество в одном диапазоне - цена одна, в другом - другая и так далее.

Mushroomer
http://webfile.ru/994923

Pazan
Пример вижу, но не понимаю, что же ты хочешь получить на выходе? цену уменьшающуюся вижу. вижу также что у тебя столбец Е равен столбцу A.

Цитата:

E7 - я не знаю

. Что в моей формуле у тебя Е7
Ты хочешь этой искомой формулой получить автоматическое снижение цены или расчет итого по позиции?

Mushroomer

Цитата:

Что в моей формуле у тебя Е7

количество
Цитата:

расчет итого по позиции?

оно самое

Pazan
_http://www.mytempdir.com/749053 я тебя верно понял?
Delete link_http://www.mytempdir.com/index.php?id=749053&action=delete&c=12e530bb029d00c1435e83ea7886c217

Чем дальше в лес - тем больше вопросов.
1) Поясни вот это "Теперь нужно всё это поместить в одну ячейку в виде формулы" Вообще в одну ячейку?
2) У тебя до 1000 идет уменьшение цены на 0,00381, а после 1000 и до конца на 0,0000381. Все правильно?

Финиш:
У тебя в ячейке D3 ошибка: у тебя =D2-0,003805, а должно быть =D2-0,00381
Вот твоя формула. Логика понятна?
=ЕСЛИ(A2<=1000;(7,62-(A2-1)*0,00381)*A2;(3,81381-(A2-1000)*0,0000381)*A2)
погрешность вычислений только в 9 (!) знаке

Pazan

Цитата:

уже сам запутался

Вот-вот. Сначала было бы не плохо все таки сформулировать задачу правильно.

Не старайся уменьшить число диапазонов. Попробуй найти линейную зависимость уменьшения цены, чтобы любое n-е число можно было бы вычислить без использования функции ЕСЛИ. Так было бы проще всего. Если нельзя, постарайся выделить несколько диапазонов с линейной зависимостью.

Yuk

Цитата:

http://webfile.ru/994923

Добавлено:
Mushroomer

Цитата:

Поясни вот это "Теперь нужно всё это поместить в одну ячейку в виде формулы" Вообще в одну ячейку?

в тестовом файле я разбил количество по-строчно для большего понимания, в оригинале, количество товара вводиття в ячейку "Е" руками. Количество строк с наименованием товара не больше 100. Вот это и значит ,что формулу запихнуть в ячейку "F".

Цитата:

У тебя до 1000 идет уменьшение цены на 0,00381, а после 1000 и до конца на 0,0000381. Все правильно?

правильно, но... это только для этой цены в "7,62". Тут нужно, как-бы вывести соотношение, чтобы оно правильно срабатывало при разных ценах. у меня почему-то до тысячи получается =7,62/2000, а вот после 1000 =7,62/200000. почему так - не знаю. мал ешо я такое знать.


Добавлено:
Mushroomer
Yuk
http://www.mytempdir.com/749701
DELETE LINK:
http://www.mytempdir.com/index.php?id=749701&action=delete&c=640649ead6a3ed24e980386b403f1506

Pazan
Согласись, что для выше приведенного примера (test2.xls) я все сделал правильно. Касательно test4.xls - Твоя задача объяснить откуда или как падает цена. Неясным остаются вопросы:
1) получается, что диапазонов всего 2. До 1000 и больше 1000. Да?
2) от чего зависят эти значения 0,00381 0,0000381.
3) Если ты скажешь какие эти два значения будут для других цен, можно будет будет поискать закономерность.
Просто приведи данные
цена1 значение1 (до 1000?) значение1 (после 1000?)
цена2 значение2 (до 1000?) значение2 (после 1000?)
цена3 значение3 (до 1000?) значение3 (после 1000?)
Блин, задача при четкой постановке - дело 10 минут, а бьемся уже не первый день

Mushroomer

Цитата:

получается, что диапазонов всего 2. До 1000 и больше 1000. Да?

нет, 3:
смотри файл тест2: до 1 000, от 1 001 до 3 000, от 3 001 до 5 000

Цитата:

от чего зависят эти значения 0,00381 0,0000381.

х. его знает. я просто определил для цены 7,62, что до 1 000 шт, цена уменьшается на 0,00381 цента, а с 1001 до 3000 и с 3001 до 5000 -- 0,0000381 соотвественно. но цены на разные товары разные. это понятно.

Цитата:

Если ты скажешь какие эти два значения будут для других цен, можно будет будет поискать закономерность.

такие, чтобы с плавным понижением цены и с увеличением количества на 1, 1000 шт. уже стоила на 50% дешевле одного экземпляра, т.е. 2 шт. уже в сумме уже дешевле на некий % от оригинальной цены, 3 шт. ещё дешевле и т.д. и так до 1000 шт. набегает 50% скидки. в прайсе стоит цена одна и та же:
Фрагмент листа Excel:
* С D E F

Pazan
Цитата:

нет, 3: смотри файл тест2: до 1 000, от 1 001 до 3 000, от 3 001 до 5 000

Но у тебя же число (0,0000381) не поменялось при переходе через 3000.

Цитата:

1000 шт. уже стоила на 50% дешевле одного экземпляра

Это понятно.
2000 шт. уже стоила на AA% дешевле одного экземпляра
3000 шт. уже стоила на BB% дешевле одного экземпляра
4000 шт. уже стоила на CC% дешевле одного экземпляра
5000 шт. уже стоила на DD% дешевле одного экземпляра

Заполни AA, BB, CC, DD

Mushroomer

Цитата:

Но у тебя же число (0,0000381) не поменялось при переходе через 3000.

практически нет, но теоретически опорные цифры 1, 1001, 3001

Цитата:

Заполни AA, BB, CC, DD

чё?

Pazan
Цитата:

чё?

На сколько процентов? Ты говоришь, что на 1000 уже идет снижение на 50%, сколько процентов будет на 3000 и на 5000? На 5000 имхо будет 52 процента, а на 3000? 51?

Mushroomer

Цитата:

На 5000 имхо будет 52 процента, а на 3000? 51?

ja wohl, meine Herr я об этом уже писАл на предыдущей странице

Pazan
Вечером доделаю. Есть у меня некоторая уверенность, что я смогу это сделать. Мне интересно как ты проверять будешь? эту непростую формулу.

Читай моё сообщение от 21:54 18-06-2006

Добавлено:
там о

Цитата:

Mushroomer
Цитата:
На 5000 имхо будет 52 процента, а на 3000? 51?

как я и говорил

Pazan
Я все понял. Точно все. И даже как константы рассчитываются и даже почему у тебя при переходе через 3000 никогда не будет изменение константы, т.е. будет, как я и говорил, два диапазона: до 1000 и больше 1000. Или через полчаса или утром (в зависимости от свободного времени и сил) будет итоговая фомула. Если захочешь, то я могу и логику всю расписать.

Pazan
Давай объясню логику:
1) у тебя есть ошибка в примере test2.xls: у тебя половина цены достигута на 1001 элементе, а согласно описанию должна быть на 1000-ом элементе.
Пусть А - цена
2) это 2 арифметические прогрессии от 1 до 1000 и от 1001 до 5000:
а) до 1000-го элемента будет 999 вычетов первой константы. т.е разница (первая константа составит A/2/999. Т.к. в твоем примере половина цены была на 1001 элементе, то у тебя разница составляла А/2/1000 = 7,62/2/1000=0,00381 Узнаешь?
б) от 1001 до 3000 будет 1999 членов прогрессии и что интересно от 3001 до 5000 тоже будет 1999 членов прогрессии. А так как у тебя изменения с 50% до 49% (второй интервал) и 49% до 48% (третий интервал) составляет 1%, то по этим двум причинам вторая константа диапазоне от 1001 до 5000 будет одинаковой. она равняется A/200000. Все рассчитывается по формуле арифметической прогрессии (расчет хитрый приводить не буду). 2 важных условия:
1) разница между 1001 и 1000 элементом = разнице между 1002 и 1001 элементом и так далее до разницы между 3000 и 2999 элементами
2) разница между 3001 и 3000 элементом = разнице между 3002 и 3001 элементом и так далее до до разницы между 5000 и 4999 элементами

Вот тебе игрушка _http://www.mytempdir.com/751656 В ячейку B1 вводишь цену.
В 1000 элементе =50% от цены,
В 3000 элементе =49% от цены,
В 5000 элементе =48% от цены.

Завтра напишу (если сам не напишешь) твою формулу. Спать хочу.

Pazan
От тебя нужно подтверждение, что игрушка (последний выложенный мною вариант) работает правильно, т.е. правильно, что
В 1000 элементе =50% от цены,
В 3000 элементе =49% от цены,
В 5000 элементе =48% от цены.

Mushroomer
щас еду в командировку. буду вечером. гляну

Mushroomer

Цитата:

От тебя нужно подтверждение, что игрушка (последний выложенный мною вариант) работает правильно, т.е. правильно, что
В 1000 элементе =50% от цены,
В 3000 элементе =49% от цены,
В 5000 элементе =48% от цены.

да, арбайт гут.
но это построчное увеличение количества. а как в одной ячейке изменять? не буду ж я в прайс на каждое наименование товара забивать по 5 000 строк - только 1. и в ней покупатель будет вручную вносить количество в диапазоне 1-5000.

Pazan
Цитата:

да, арбайт гут

Мне нужно было такое подтверждение, чтобы зря не тратить силы на основную формулу. Теперь в твоем последнем выложенном файле мне всего лишь надо подработать мою формулу, которая работала для частного случая, распространив ее на общий случай.

Добавлено:
Вот твоя итоговая формула (мог бы и сам сообразить, ибо все данные у тебя были):
=ЕСЛИ(F6<=1000;(E6-E6*(F6-1)/1998)*F6;(E6/2-E6/200000-E6*(F6-1001)/200000)*F6)
А вот твой пример _http://www.mytempdir.com/753296 Тестируй

Mushroomer


Цитата:

мог бы и сам сообразить, ибо все данные у тебя были

я и сообразил, читай мой пост от 08:20 19-06-2006, но не знал, как это задать с помощью Excel.
Да завтра я себя покажу перед шефом, о да!
усем спасибо за этеншин

Pazan
Цитата:

я и сообразил

Имхо ты просто подогнал (Ты же не знал как константы получаются да и диапазонов у тебя три было + небольшая ошибка в 1001 элементе вместо 1000). А если вспомнить вообще с какого твоего вопроса началась эта задач, то ..... А я тебе почти всю математику расписал. А если честно, это школьная задача на арифметическую прогрессию Если бы ты сразу пример выложил (особенно test2.xls) и на нем бы все расписал, то все бы гораздо быстрее прошло. А так 3 дня ушло

Mushroomer
хе, у тебя 3 дня, а у меня ваще неделя. Ты бы услышал формулировку шефа, какой ему прайс нужен, так совсем бы загрустил, а это при чём "срочно и без ошибок, и к конце дня". так что премии за июнь я уже лишился. Это я потом начал анализировать, создавать тест1,2,3,4. вот. а в целом спасибо. так сначало и было - начиная с 1001 - уже 50%, но потом я убедил меньшего шефа, а он большего, что х-я получается. а на счет прогрессии ты прав. давно это было в школе.

Pazan
Цитата:

начиная с 1001

Если бы с 1001, там бы было чуть сложнее. Вот тогда бы было три диапазона (с условием что заканчивается все на 5000 элементе). А чтобы осталось 2 диапазона с условияем что 50% на 1001, надо чтобы последним был 5001 элемент и на нем 0,48

Цитата:

у тебя 3 дня

ну чистого времени работы в экселе имхо около 1-2 часов. Было еще обсуждение самой математической модели (около 2 часов).

Всем привет!
Возник насущный вопросик!
Есть некая таблица значений(не суть) для которой, по формулам, в отдельном столбце проверяются свойства этих значений. Есть ли возможность стандартными свойствами Ехсеl окрашивать ячейку с формулой (при выполнении или нет условия) в определенный цвет?
Есть стойкое ощущение, что можно. Один бывший сотрудник вроде делал (пару лет прошло) но связь с ним потеряна...

В меню Формат - Условное форматирование.
А какое конкретно условие?

Ну там - если определенная ячейка равна нулю - истина, если нет - ложь

Добавлено:
может так понятней будет:
пример
=ЕСЛИ(И(P87>0; Q87=0;S87=0); "Готов"; "Не Готов")
хочу чтобы не просто слово готов было, а еще и цветом выделялось т.к. список большей и визуальное восприятие будет лучше.

Thing
Все как сказал Yuk. Делаешь свою формулу, потом меню Формат - Условное форматирование. Значение равно "Готов". Дальше либо цвет текста либо цвет фона ячейки.