Использование функций в Excel. Мастер функций.

 

Продолжим работу с оформлением прайс-листа, при этом будем использовать встроенные в Excel функции.

В ячейку А47 введите текст: Цены в рублях рассчитываются по курсу ММВБ на день продажи + 3%

В ячейку А48 введите текст: Текущий курс рубля = 6, 02 рублей за 1$ на день 29/03/1998

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

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

Т.е. при изменении переменных на втором листе весь прайс-лист будет пересчитан, а строки текст останется прежним! Изменим этот недостаток.

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

Рассмотрим строку текста: Цены в рублях рассчитываются по курсу ММВБ на день продажи + 3%.

В ней текстовая (неизменяемая) часть – " Цены в рублях рассчитываются по курсу ММВБ на день продажи + ", а вторая (переменная) часть ссылается на ячейку второго листа Дополнительный_процент, потом опять текстовая часть которая содержит знак %.

 

Начнем редактирование (можно нажать клавишу F2 для редактирования). Преобразуйте строку текста в формулу, т.е. поставьте перед текстом знак равенства (=) и текстовую часть возьмите в кавычки, затем с клавиатуры наберите знак & перейдите на лист Переменные щелкните на ячейку Дополнительный_процент поставьте знак умножения * и наберите 100, опять наберите знак & поставьте в кавычках знак %.

 

В окончательном виде формула будет иметь вид:

=" Цены в рублях рассчитываются по курсу ММВБ на день продажи + " & Дополнительный_процент*100& " %"

где & - оператор текстовой функции СЦЕПИТЬ (см. приложение к работе), дополнительный_процент – аргумент со второго листа.

 

Внешне фраза не изменилась, но она стала формулой, которая " следит" за переменной.

Проверьте, работает ли ваша формула. Перейдите на второй лист и введите дополнительный процент, например 10%. Верните исходный процент 3%.

Аналогично измените в формулу следующую фразу.

Она должна принять следующий вид:

=" Текущий курс рубля = " & Текущий_курс_рубля & " рублей за 1 $ на день 29/03/1998"

 

Измените курс доллара, например 31, 27. Проверьте работу формулы.

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

 

Теперь введем в эту формулу функцию даты: СЕГОДНЯ():

=" Текущий курс рубля = " & Текущий_курс_рубля& " рублей за 1$ на день " & СЕГОДНЯ()

Вместо даты у вас, наверное, получилось число. Изменим формулу с помощью Мастера функций. Удалите из формулы функцию СЕГОДНЯ() и после знака & вызовите Мастера функций, нажав на панели инструментов кнопку f_x , затем в окне Мастер функций выберите Категорию: – Текстовые, Функция: – Текст (рис. 2).

Функция ТЕКСТ форматирует число и преобразует его в текст. Синтаксис функции: ТЕКСТ(значение; строка_формат). (Более подробно: см. приложение). Окно диалога на следующем шаге Мастера функций имеет вид, показанный на рис. 2.

 

 

Рис. 2 Окно Мастера функций (2-й шаг)

 

В поле Значение введите первый аргумент – функцию СЕГОДНЯ().

В поле Строка_формат введите второй аргумент – ДД/ММ/ГГГГ и нажмите ОК.

 

Формула теперь имеет вид:

=" Текущий курс рубля = " & Текущий_курс_рубля & " рублей за 1 $" & " на день " & ТЕКСТ(СЕГОДНЯ(); " ДД/ММ/ГГГГ" )

 

В прайс-листе будет выведен текст:

Текущий курс рубля = 6, 02 рублей за 1$ на день 29/03/1998

 

Если все формулы работают, то Вы получили грамотно работающий прайс-лист.

На этом наверное, можно было бы и закончить. НО!

ª Мы можем при работе с реальным прайс-листом забыть изменить текущий курс рубля.

 

Добавим перед нашими переменными на втором листе три строки. Выделите три строки и в меню Вставка выберите Строки.

В ячейку А2 введите текст – Текущая дата;

в ячейку А3 – Дата изменения курса;

в ячейку В2 введите формулу: =СЕГОДНЯ().

В ячейке В3 должна быть дата изменения курса рубля – введите вчерашнюю дату, разделяя день, месяц и год дробной чертой.

В этом случае формат ячейкам присвоится автоматически.

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

В ячейку С5 введем с помощью Мастера функций логическую функцию ЕСЛИ (информацию о функции вы найдете в приложении к работе). Окно диалога Мастера функций (2-й шаг) показано на рис.3.

 

 

Рис. 3 Окно диалога Мастера функций (2-й шаг)

 

В поле Логическое_выражение введите В2< > В3 (без пробелов);

в поле Значение_если_истина – Дата изменилась. Возможно изменился курс рубля;

в поле Значение_если_ложь ничего вводить не будем, поэтому поставьте просто две кавычки. Нажмите ОК.

 

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

 

Проверим работу условия. Введите совпадающую дату изменения курса: надпись пропадет. Снова введите вчерашнюю дату. Итак, Excel напоминает автоматически о возможном изменении курса рубля!

Дальнейшая автоматизация прайс-листа возможна при использовании макроса.

 

⇐ Предыдущая12345678Следующая ⇒

Поделиться: