» Excel FAQ (часть 2)

Как я могу решить следующую задачу?

Есть книга из нескольких листов.

На одном листе есть список. Удобно. Его можно пополнять, редактировать, при этом он сам меняет размер, а также меняется соответствено и звязанный с ним именованный диапазон, так, что я всегда знаю, сколько строк в списке, и могу обратиться к каждой.

На других листах должны присутствовать таблички (или списки), содержащие некоторые столбцы из первого списка. Причём они должны, по возможности, автоматически обновляться при измеении первого списка. Автоматически должны менять свой размер, добавляя или удаляя у себя строки (под таблицами продолжается документ). Как это сделать?

DOCT
Тогда скорее всего придется склеивать. Если, конечно, завязан на экселе. В Матлабе, к примеру, такой график вполне можно сделать. Для склеивания можно использовать, например, Адобе Иллюстратор. Предварительно убрать метки оси Х. Перенести через copy-paste. Обязательно разгруппировать и почистить. В принципе, вариантов много, в зависимости от того, каким софтом лучше владеешь.

Yuk

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

Добавлено:
Dr Eam

Цитата:

На других листах должны присутствовать таблички...Причём они должны, по возможности, автоматически обновляться при измеении первого списка. Автоматически должны менять свой размер, добавляя или удаляя у себя строки

У меня кстати такая же ситуация с формами возникла, тоже не знаю как это сделать, чтобы список автоматически удалял строки... наверное через макрос. Но я нашёл лёгкое решение, например у меня на февраль формируется табличка взависимости от листа на январь,причём некоторые строки могут стать пустыми - лишними , следовательно должны быть удалены, но есть решение: поставить на эту таблицу автофильтр, который будет автоматически прятать лишние строки по выбору бухгалтера сортировать "непустые" - очень удобно, но конечно не полностью автоматизировано
По поводу того, чтобы ячейки автоматом меняли размер надо спросить у Юка

Vladimir54
Файл - Параметры страницы - Лист - Черно-белая

Dr Eam
Я вижу решение только через макрос, то есть генерировать таблицу на 2-м листе заново каждый раз при изменении таблицы на 1-м листе. Это если нужен полный автомат. При частых изменениях и если таблица большая может серьезно подзагрузить систему - возможна задержка после каждого изменения. Можно сделать полуавтомат - обновление 2-й таблицы например при нажатии пользователем на кнопку.
Если пойдешь по этому пути, придется осваивать VBA. Вряд ли здесь можно получить готовое решение.

Yuk

Цитата:

Попробовал написать функцию, а получилось, что воспроизвел РАНГ.

А реально сделать такую функцию которая:
1) вначале работает как РАНГ - просматривает массив чисел и самому большому присваивает 1 ранг, второму по величине - 2 и т.д;
2) если встречаются два одинаковых числа то они получают одинаковый ранг - допустим оба получают ранг 3;
3) все числа, которым уже присвоен ранг (т.е. те что получили 1, 2 и 3 "места") исключаются из обработки, обрабатывается только оставшаяся часть массива и самое большое число получает ранг 4, дальше цикл повторяется пока не будут присвоены ранги всем числам массива?

lucky_Luk
Если делать пользовательскую функцию, то проблема в том, что формула вычисляется для каждой ячейки в отдельности. То есть функция, не знает, какой ранг присвоен другим числам. А если присваивать ранг всем числам в таблице для каждой ячейки в отдельности, как я уже писал, это будет тормозить эксель со страшной силой. В общем, мое мнение - надо макрос делать.

как задать условие без VBA, типа, если B1>100 и <150 тогда условие1 иначе условие2?

Pazan
симбиоз функций Если и И: =ЕСЛИ(И(B1>100;B1<150);1;0)
либо хитрее B1>100 и <150 это тоже самое Abs (125-B1) < 25

Mushroomer
сенки вери мач

Yuk

Цитата:

В общем, мое мнение - надо макрос делать.

Я в VBA не тяну, не поможешь?

Mushroomer

Цитата:

B1>100 и <150 это тоже самое Abs (125-B1) < 25

типа, модуль? йоханный бабай, неужели всё так просто?

Pazan
Ага. Чистая математика.
100 < B1 < 150
- 25 < B1-125 < 25 т.е. ABS (B1-125) < 25

Mushroomer
Всем
Короче, задача такова:
ячейка Dx - цена;
ячейка Ex - количество
если количество от 1 до 100, тогда Dx*Ex,если от 101 до 200, тогда от Dx отнимается часть и умнож. на Ех, потом от 201-300 и т.д.
В результате если Ех в диапазоне 2001-5000 от Dx отнимается 52%.
создал в ячейке формулу:

Код:
=ЕСЛИ(И(B1>=1;B1<=100);D7*E7;ЕСЛИ(И(B1>=101;B1<=200);(D7-(D7*0,074285714))*E7;ЕСЛИ(И(B1>=201;B1<=300);(D7-(D7*0,148571429))*E7;ЕСЛИ(И(B1>=301;B1<=500);(D7-(D7*0,222857143))*E7;ЕСЛИ(И(B1>=501;B1<=999);(D7-(D7*0,371428571))*E7;ЕСЛИ(И(B1>=1000;B1<=5000);(D7-(D7*0,445714286))*E7;(D7-(D7*0,52))*E7)))))))

Pazan
Выложи пример. И сколько точно будет таких диапазонов: 1-100, 100-200, .... B1 всегда > 0?
Ты слишком усложнил (или не понимаешь принцип работы) формулы Если.
Если у тебя B1 всегда > 0, то будет такая формула
=ЕСЛИ(B1<=100;1;ЕСЛИ(B1<=200;2;ЕСЛИ(B1<=300;3;ЕСЛИ(B1<=500;4;ЕСЛИ(B1<=1000;5;ЕСЛИ(B1<=5000;6;7))))))
Т.е. можно вкладывать Если друг в друга. Кажется до 8 раз включительно.

Mushroomer

Цитата:

Выложи пример.

Прайс на 100 наименований.
Значит, есть опорная цена,которая понижается в среднем на 7,4% от предыдущей с каждым диапазоном количества:
1-100
101-200
201-300
301-500
501-999
если можно 1000-2000 и 2001-5000, если нет, то:
1000-5000

Pazan
Как я понял будет еще > 5000. Верно? Я же написал формулу, тебе осталось только значения поставить вместо цифр 1,2,3,4,5,7,8
=ЕСЛИ(B1<=100;D7*E7;ЕСЛИ(B1<=200;(D7-(D7*0,074285714))*E7;ЕСЛИ(B1<=300;(D7-(D7*0,074285714*2))*E7;ЕСЛИ(B1<=500;(D7-(D7*0,074285714*3))*E7;ЕСЛИ(B1<=1000;(D7-(D7*0,074285714*4))*E7;ЕСЛИ(B1<=2000;(D7-(D7*0,074285714*5))*E7;ЕСЛИ(B1<=5000;(D7-(D7*0,074285714*6))*E7;(D7-(D7*0,52))*E7)))))))
проверь коэффиценты
1-100 D7*0,074285714*0
101-200 D7*0,074285714*1
201-300 D7*0,074285714*2
301-500 D7*0,074285714*3
501-999 D7*0,074285714*4
1000-2000 D7*0,074285714*5
2001-5000 D7*0,074285714*6
> 5000 D7*0,52

Если я написал коэффициенты правильно, то можно укоротить часть формулы, где прослеживается четкая закономерность:
=ЕСЛИ(B1<=400;(D7-(D7*0,074285714*ЦЕЛОЕ((B1-1)/100))*E7);ЕСЛИ(B1<=500;(D7-(D7*0,074285714*3))*E7;ЕСЛИ(B1<=1000;(D7-(D7*0,074285714*4))*E7;ЕСЛИ(B1<=2000;(D7-(D7*0,074285714*5))*E7;ЕСЛИ(B1<=5000;(D7-(D7*0,074285714*6))*E7;(D7-(D7*0,52))*E7)))))

Pazan
Как насчет вот такой формулы:
Код: =(D2-(D2*0.074*INT((B2-1)/100)))*E2

Yuk
Да, я был очень близок. всего-то до (B1-1) не додумался.

Yuk

Цитата:

=(D2-(D2*0.074*INT((B2-1)/100)))*E2

а чего это B2-1?
больша 100 позиций в прайсе не будет, может быть меньше

Pazan
Ты не понял (или Yuk написал не так) B2 - это число в диапазоне 1-5000 и далее

Pazan
100 соответствует 0, а 101 - 1, так как это уже следующий диапазон. Так?
Поэтому и B2-1.

Mushroomer
Yuk
если я правильно понимаю, то у меня не B2, а Е2, как на рисунке на предыдущей странице?

Pazan
А здесь что?
http://forum.ru-board.com/topic.cgi?forum=5&topic=19106&start=300#15

Yuk
а здесь тестовая формула, оригинал на рисунке

Pazan
Кончай тормозить. Замени B2 на Е2 и все.

всем пасиба, скидываю ручник...

lucky_Luk
Пренес задачу сюда.

Yuk
вчера уже времени не было протестировать формулу полностью.
сегодня это сделал, и вот что получается. цена знижается бесконечно и при заказе в 5000 штук сумма равна минус 99 252,48 при начальной цене в 7,56. вот. Больше 5000 количества не будет, это предел. при заказе от 1 до 5 000 картина такова:
1 штука * 7,62 = 7,62
1 000 * (~50% от 7,62) 3,81 = 3 813,82
5 000 штук * (~48% от 7,62) = 18 307,08
могу скинуть тестовій файл. скажи куда. там каждое изменение видно. Теперь нужно всё єто поместить в одну ячейку в виде формулы.

Pazan
Ты что издеваешься? Нельзя использовать формулу Yuk, т.к. у тебя используется нелинейная зависимость коэффициентов от диапазонов. Yuk, так же как и я, указали, что можно часть формулы сократить.
=ЕСЛИ(B1<=400;(D7-(D7*0,074285714*ЦЕЛОЕ((B1-1)/100))*E7);ЕСЛИ(B1<=500;(D7-(D7*0,074285714*3))*E7;ЕСЛИ(B1<=1000;(D7-(D7*0,074285714*4))*E7;ЕСЛИ(B1<=2000;(D7-(D7*0,074285714*6))*E7;(D7-(D7*0,52))*E7))))

Тебе надо было всего лишь разобраться в формуле и проверить коэффициенты. Вот новая редация коэффициентов в этой формуле, с учетом что больше 5000 не будет
1-100 D7*0,074285714*0
101-200 D7*0,074285714*1
201-300 D7*0,074285714*2
301-500 D7*0,074285714*3
501-1000 D7*0,074285714*4
1001-2000 D7*0,074285714*6
2001-5000 D7*0,52

B1 - анализируемое количество
D7 - цена
E7 - я не знаю.

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

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