Новости:

К первому сообщению темы должен быть прикреплен файл примера в формате xls*.
Приложив пример, Вы избавите себя и других от вопросов типа "А какой критерий?", "А куда выводить результат?", "А сколько строк?" и все тех же просьб выложить файл. Рисовать за Вас Ваши же таблички с заданиями, а затем и решение к ним, никто желанием не горит. Да и, как показывает практика, в большинстве случаев без файла решения не найти.

Главное меню

Текстовый аргумент в формуле макроса

Автор Dr_Lex, 07.06.2015, 17:01

« назад - далее »

Dr_Lex

Здравствуйте, столкнулся с такой закавыкой. Есть формула, которая в зависимости от имени клиента выбирает скидку и считает выручку. Формула выглядит так: "=ЕСЛИ(B13="Фирма1";B12*(1-'Вспомогательные расчёты'!B18);ЕСЛИ(B13="Фирма2";B12*(1-'Вспомогательные расчёты'!B19);ЕСЛИ(B13="Фирма3";B12*(1-'Вспомогательные расчёты'!B20);B12))). В ячейке экселя эта формула спокойно работает, там всё нормально. Но, есть редкие ситуации, когда эта формула не подходит и для ускорения процесса, сотрудник считает выручку вручную и просто вписывает значение в эту ячейку вместо формулы. Но для приёма следующего заказа, естественно, нужно, чтобы формула опять была в этой ячейке. Когда попытался заставить макрос вписывать в ячейку эту формулу, ему не понравился аргумент "Фирма1" (вторая и третья соответственно тоже). Код выглядит так:
    Range("B14").Select
    ActiveCell.Formula = "=ЕСЛИ(B13="Фирма1";B12*(1-'Вспомогательные расчёты'!B18); _
        ЕСЛИ(B13="Фирма2";B12*(1-'Вспомогательные расчёты'!B19); _
        ЕСЛИ(B13="Фирма3";B12*(1-'Вспомогательные расчёты'!B20);B12)))"

Пытался вместо .Formula использовать .FormulaLocal результат тот же.
Подскажите в чём может быть проблема?

Serge 007

Здравствуйте. Замените это:
Цитата: Dr_Lex от 07.06.2015, 17:01    Range("B14").Select
    ActiveCell.Formula = "=ЕСЛИ(B13="Фирма1";B12*(1-'Вспомогательные расчёты'!B18); _
        ЕСЛИ(B13="Фирма2";B12*(1-'Вспомогательные расчёты'!B19); _
        ЕСЛИ(B13="Фирма3";B12*(1-'Вспомогательные расчёты'!B20);B12)))"

на это:
    [B14].FormulaR1C1 = _
        "=R[-2]C*(1-VLOOKUP(R[-1]C,'Вспомогательные расчёты'!R[4]C[-1]:R[6]C,2,))"
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Dr_Lex

Спасибо за ответ. Но, к сожалению проблему он не решает. Пока клиентом является один из постоянных клиентов всё хорошо, но стоит ввести нового клиента результатом становится #Н/Д. Кроме того список я ввёл для удобства примера, по сути его в файле не будет. Грубо говоря есть три категории постоянных клиентов с различными условиями и их нельзя однозначно описать ссылками на ячейку со скидкой. Нужно, чтобы вставлялась формула Если(...) с текстовыми аргументами. Возможно ли это сделать?

Serge 007

Цитата: Dr_Lex от 07.06.2015, 18:33...есть три категории постоянных клиентов с различными условиями и их нельзя однозначно описать ссылками на ячейку со скидкой. Нужно, чтобы вставлялась формула Если(...) с текстовыми аргументами.
Вы сами себе противоречите. С одной стороны Вы утверждаете что нельзя однозначно описать ссылками на ячейку со скидкой, а с другой просите что бы вставлялась формула. Но ведь любая формула - это и есть заранее заданный алгоритм. Если нельзя однозначно описать скидку для клиентов, то и формулой условия скидки тоже задать будет нельзя

PS Предложенная мной формула НИЧЕМ принципиально не отличается от Вашей, за исключением того, что мой вариант позволяет задать любое количество условий, а Ваша ограниченное (Excel 2003 - 7, Excel 2007 - 64, Excel 2010 и последующие - 128)
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Dr_Lex

Не противоречу. Для простоты понимания задачи я задал трём разным фирмам три значения скидки 10, 15, 20 процентов. Но на деле это не три фирмы, это три категории и значение скидки не 10, 15, 20, а набор вычислений из многих параметров, которые, дабы не загромождать пример, я не включил в файл. В некоторых случаях это даже не скидка, а надбавка. Мне достаточно трёх условий и четырёх вариантов вычислений. Можно ли создать макрос, который будет записывать формулу в том виде, в котором она у меня сейчас в ячейке Excel? Если нельзя, тогда нужно будет подумать, как это сделать с помощью списка.

vikttur

Зачем записывать формулу? Можно в коде все посчитать и на лист выгрузить результат. Пересчет можно производить при изменении во влияющих ячейках.

Dr_Lex

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

vikttur

Цитировать...каждого нового (а меняются они довольно часто) обучать макросам...
Т.е. обучить макросу, который вставляет формулу, можно, а другому - никак?
В данном случае специальных знаний не требуется. Нужно только знать, куда код вставить.
Смотрите пример. В столбце D сумма ячеек А и В текущей строки. Не нужно вставлять формулу, которую потом могут затереть.

Serge 007

Цитата: Dr_Lex от 07.06.2015, 22:45Не противоречу
Противоречите. И я объяснил Вам, в чем именно. Печально что Вы этого не понимаете

Цитата: Dr_Lex от 07.06.2015, 22:45...набор вычислений из многих параметров, которые, дабы не загромождать пример, я не включил в файл...
Вот их-то и надо было в файле выложить. Тогда можно было бы автоматизировать расчет, что бы никому не приходилось ничего вручную или формулами делать

Цитата: Dr_Lex от 07.06.2015, 22:45...Можно ли создать макрос, который будет записывать формулу в том виде, в котором она у меня сейчас в ячейке Excel?
Можно. Решение вставлять Вашу формулу макросом самое нелепое из всех существующих, однако раз Вы нормальные варианты не приемлете, то...
Range("B5").FormulaR1C1 = _
        "=IF(R[-1]C=""Фирма1"",R[-2]C*(1-'Вспомогательные расчёты'!R[4]C),IF(R[-1]C=""Фирма2"",R[-2]C*(1-'Вспомогательные расчёты'!R[5]C),IF(R[-1]C=""Фирма3"",R[-2]C*(1-'Вспомогательные расчёты'!R[6]C),R[-2]C)))"
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Dr_Lex

ЦитироватьТ.е. обучить макросу, который вставляет формулу, можно, а другому - никак?
Я и не обучаю других макросам, у оператора только кнопка и всё, макросом занимаюсь я. Но формулу на листе он видеть будет, а макрос нет. И как раз для того чтобы формула не затёрлась нужен макрос, который её каждый раз возвращает на место.
В вашем примере всё работает до тех пор, пока не нужно затереть значение суммы ручным значением. После этого даже изменение значений в столбцах A и B не приводит к правильному расчёту. Остаётся ручное значение. Оно поменяется лишь в том случае, если я в эти столбцы задам число вручную, а у меня везде формулы.

Dr_Lex

ЦитироватьВот их-то и надо было в файле выложить.
Это огромное количество параметров, в которых, чтобы понять, что мне нужно, вам потребовалось бы ещё разбираться и тратить своё время. Я максимально упростил пример, чтобы не тратить ваше время. Вы же не будете за меня каждый раз выполнять мою работу. Значит мне нужно разобраться самому, как и что делать, если похожая ситуация возникнет вновь, чтобы не дёргать людей по одному и тому же поводу сотню раз. Макросы для меня тёмный лес (пока), соответственно я использую их лишь в меру моих скромных возможностей, стараясь решать все поставленные задачи на рабочем листе Excel через формулы. Я не смог бы сам автоматизировать расчёт макросами, а просить это сделать других людей... ну это совсем обнаглеть. Расчёт в достаточной степени автоматизирован с помощью формул, они тоже дают неплохое пространство для манёвров.

ЦитироватьРешение вставлять Вашу формулу макросом самое нелепое из всех существующих, однако раз Вы нормальные варианты не приемлете, то...
Простите, что я такой упёртый баран :) но для меня это лучшее решение, которое я смогу понять и воплощать в дальнейшем. Как говорится, самая короткая тропинка та, которую ты знаешь. Огромное спасибо за помощь. Всё работает, оказывается нужно было лишь удвоить кавычки.

IKor

На всякий случай добавлю свои 5 копеек:
в тех случаях когда в моих таблицах появляется риск "порчи" пользователями формул, я стараюсь модифицировать формулы таким образом, чтобы рядом (в том же столбце или строке) находилась формула, которую можно скопировать вместо испорченной. Обычно такие ячейки я выделяю одинаковым цветом фона: и в большинстве случаев пользователям даже не приходится давать дополнительных пояснений...

ShAM

Ну, и мои 5 коп. Если хотите, чтобы было как у Вас, т.е. по-русски, то добавьте Local:
ActiveCell.FormulaLocal = _
"=ЕСЛИ(B13=""Фирма1"";B12*(1-'Вспомогательные расчёты'!B18);ЕСЛИ(B13=""Фирма2"";B12*(1-'Вспомогательные расчёты'!B19);ЕСЛИ(B13=""Фирма3"";B12*(1-'Вспомогательные расчёты'!B20);B12)))"

ЗЫ: Удвоение кавычек, конечно, тоже нужно.