Используйте абсолютные адреса ячеек и присвоение имен ячейкам при задании формул

1.В ячейку А12 введите текст "План на 1-й квартал". В ячейку D12 введите величину плана 40 000 000.

2.В ячейке G5 отредактируйте формулу, указав вместо значения

40 000 000 ссылку на ячейку с абсолютным адресом ($D$12).

3.Скопируйте откорректированную формулу в ячейку G6.

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

− выделите ячейку;

− на вкладке Формулы нажмите кнопку – Присвоить имя;

− после этого на экране появится диалоговое окно, введите имя

"план" в поле Имя, закройте окно с помощью кнопки Ok.

5.В ячейке G7 отредактируйте формулу, указав вместо значения

40 000 000 ссылку на имя ячейки ("план") и скопируйте формулу в ячейку G8.

.

Взаимосвязь ячеек, трассировка

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

 

Задание 4

Постройте модель сбыта, изображенную на рис.8.

 

Ход выполнения работы

1.Откройте лист 2 и с оздайте таблицу, изображенную на рис.8.

2.Неизвестные параметры таблицы вычислите по формулам:

− Объем сбыта =35 * Сезонный фактор * (3атраты на рекламу + 3000) ^ 0,5.

− Доход от оборота = Объем сбыта * Цена продукции.

− Себестоимость реализованной продукции = Объем сбыта * Себестоимость продукции.

− Валовая прибыль = Доход от оборота - Себестоимость реализованной продукции.

− Накладные расходы = 0,15 * Доход от оборота.

− Валовые издержки = Затраты на зарплату + Затраты на рекламу + Накладные расходы.

− Прибыль от реализованной продукции = Валовая прибыль - Валовые издержки.

− Коэффициент прибыльности = Прибыль от продукции / Доходот оборота.

 

 

Рис.8

 

3.Нарисуйте зависимые ячейки от Цены продукции и нарисуйте влияющие ячейки (до второго колена) на Коэффициент прибыльности "Всего" (коэффициент прибыльности предприятия за год).

4.Оформите таблицу рамками, используя кнопку Границы на ленте вкладки Главная в группе Шрифт.

5.Созданные таблицы сохраните в файле Лр2.

 

Практическое занятие №3

Форматирование чисел. Мастер функций

Форматирование чисел

Формат числа определяется видомчислового шаблона, который может быть:

«#» − замещаемый

«0» −обязательный

Пример формата числа приводится в таблице 3. В первой колонке находятся коды форматов, во второй − числа после данного форматирования.

 

Таблица 3

Формат	Результат
#.###,##
0.000,00	0.013.00
#.##0,00	13,00

 

Если в качестве цифрового шаблона используется «0», то он сохранится везде, где его не заменит значащая цифра. Значок «решетки» отсутствует на местах, где нет значащих цифр. Рекомендуется использовать цифровой шаблон в виде «0» для цифр, стоящих после десятичной запятой, а в других случаях использовать значок «решетки». Excel округляет дробные числа, которые форматировались как целые, т.е. без разрядов после запятой. Однако округляются только числа, выводимые на экран, в расчетах же используются точные значения.

 

Форматирование даты

Excel отображает даты с помощью натуральных чисел от 1 до 65380. При этом летоисчисление начинается с 1 января 1900 года, которое соответствует единице, и кончается 31 декабря 2078 года. Эта дата соответствует числу 65380. Формат даты и времени задается буквенным шаблоном.

Результаты для различных вариантов форматов приведены в табл. 4.

Таблица 4

	Формат	Результат
Д	день	1 – 31
ДДД	день недели	Пн – Вс
ДДДД	день недели	понедельник – воскресенье
ММ	месяц	01 – 12
МММ	месяц	янв – дек
МММММ	месяц	Январь – Декабрь
ГГ	год	00 – 99
ГГГ	год	1900 – 2078

 

Форматирование времени

Для счета времени программа использует дробные числа. Например, час выражается числом 1/24, двенадцать часов − числом 12/24, или 0,5. Для того, чтобы знать минуту, программа использует число 1 / (12 * 60), для секунды− дробь 1 / (12 * 3600).

Форматы записи времени приведены в таблице 5.

Таблица 5

Формат	Результат
ч часы	0-24
мм минуты	00-59
сс секунды	00-59

 

Задание 1

Создайте таблицу, изображенную на рис.9.

 

Ход выполнения работы

1.Используя операцию, Заполнение смежных ячеек, создайте таблицу, изображенную на рис.9.

2.Произведите форматирование чисел в столбце "Форматирование чисел", используя для примера рис.9. Форматирование чисел произведите по следующей схеме:

− выделите ячейку В6;

− из контекстного меню выберите пункт Формат ячеек, а в появившемся диалоговом окне − вкладку Число;

− выберите в левой группе требуемый формат числа;

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

Примечание. В окне Пример появится пример формата выбранной ячейки.

− завершите задачу, нажав клавишу Ok.

Примечание. В поле Отрицательные числа можно выбрать вариант задания сумм, которые идут "в минус". Их можно выводить красным цветом, что часто используется при оформлении бухгалтерской документации.

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

Числовой, Финансовый, Процентный, Дробный, Экспоненциальный, Денежный.

3.Произведите форматирование даты в столбце "Форматирование даты", используя для примера рис.9. Для данного форматирования выберите в группе Числовые форматы категорию Дата.

4.Произведите форматирование времени в столбце "Форматирование времени", используя для примера рис.9. Для данного форматирования выберите в группе Числовые форматы категорию Время.

5.Отформатируйте оставшиеся не отформатированными ячейки, самостоятельно создав форматы. Создать собственный формат можно, выбрав в окне Числовые форматы группу (все форматы). В поле Тип выберите наиболее подходящий формат и отредактируйте его. Затем нажмите кнопку Ok, и данный формат попадет в поле Тип для данного формата ячейки.

6.Сохраните таблицу в виде файла Лр3.

 

Рис.9

 

 

Рис.10

 

Мастер функций

Мастер функций − это диалоговое окно, призванное облегчить ввод функций в формулы пользователя. Для вызова данного диалога нужно на вкладке Формулы нажать кнопку Вставить функцию либо нажать кнопку в строке формул с изображенным знаком функций

 

 

 

Задание 2