Цветовое оформление таблицы.

Лабораторная работа № 3

Тема: Мастер функций. Абсолютная ссылка. Имя ячейки. Подготовка документа к печати

Цель работы: познакомиться с возможностями Мастера функци и понятиями «абсолютный адрес ячейки» и «имя ячейки»; научиться оформлять таблицы различными цветами; научиться способу автоматической установки текущей даты, подготавливать документ к печати.

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

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

Способ 1: В меню Вставка выбрать пункт Функция.

Способ 2: Нажать на кнопку Вставка функции на Панели инструментов.

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

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

 

Задание 1.

1. Вычислите значение sin суммы чисел находящихся в нескольких ячейках. Наберите в ячейках А1 по А5 числа: 12, 34, 56, -43, 9.

2. В ячейке А6 наберите формулы: =SIN(СУММ(А1:А5)) и нажмите Enter. В результате в ячейке А6 появится число: -0,89793.

3. Для выполнения этого задания требовалось знание имени нужной вам функции. Если вы не знаете, каким именем задается та или иная функция, то следует воспользоваться Мастером функций. Вычислите значение cos суммы чисел находящихся в ячейках А1 по А5 с помощью Мастера функций.

4. Выделите ячейку В6, выполните команду Вставка®Функция или нажмите на кнопку Вставка функции на Панели инструментов.

5. В появившемся диалоговом окне Мастера функций в группе Категория выберите Математические. Затем в группе Функция выберите COS.

6. Для того чтобы посчитать сумму чисел в нескольких ячейках необязательно набирать команду СУММ с клавиатуры, а можно нажать на значок в виде направленного вниз треугольника в Строке формул слева от красного крестика. В результате появится список наиболее часто употребляемых функций, среди которых находится и СУММ. Выберите ее.

7. Прижимая правую кнопку мыши, перетащите диалоговое окно Мастера функций в сторону так, чтобы были видны ячейки с вашими числами. Выделите их. В результате в Строке формул появится адрес выделенного интервала ячеек. Нажмите OK. В ячекй В6 должно появиться число: 0,440143.

Абсолютный адрес ячейки.

Относительным адресом или просто адресом называется обозначение ячейки, составленное из номера столбца и номера строки (А5, В7 и т.д.). При некоторых операциях копирования, удаления, вставки Excel автоматически изменяет этот адрес в формулах. Иногда это служит источником ошибок. Чтобы отменить автоматическое изменение адреса данной ячейки, вы можете назначить ей абсолютный адрес. Ссылки на ячейки с абсолютным адресом называются абсолютными ссылками.

Если необходимо чтобы не менялся адрес ячеек какого-либо столбца (или строки), то для этого следует проставить перед номером столбца (или строки) знак доллара $. Например, в адресе $А5 не будет меняться номер столбца, в адресе В$7 - номер строки.

Если необходимо, чтобы не менялся адрес ячейки, то следует проставить знаки $ перед именем столбца и перед номером строки в адресе ячейки. Например, абсолютный адрес ячейки F3 будет иметь вид: $F$3.

При вводе абсолютного адреса с клавиатуры нет необходимости набирать знак $ вручную. Нажмите клавишу F4, и Excel сделает это сам. Например, при первом нажатии F4 адрес А2 превратится в $А$2, при втором – в А$2, при третьем – в $А2, и при четвертом – снова в А2.

 

Задание 2.

1. Составьте таблицу квадратов двузначных чисел. Верхняя строка этой таблицы представляет собой ряд цифр от 0 до 9 – это цифры единичного разряда числа. Первый столбец таблицы квадратов содержит цифры от 1 до 9 – это десятки. Ячейка таблицы квадратов содержит результат возведения в квадрат двузначного числа, первая цифра (десятки) которого содержится в первом столбце, а вторая (единицы) – в верхней строке.

2. В ячейку А3 введите число 1, в ячейку А4 – число 2, выделите обе ячейки и протащите Маркер заполнения вниз, чтобы заполнить столбец числами от 1 до 9. Аналогично заполните ячейки В2 – К2 числами от 0 до 9.

3. После того как вы заполнили 2 строчку числами от 0 до 9, то все необходимые вам для работы ячейки одновременно не видны на экране. Сделайте их более узкими, но так, чтобы все столбцы имели одинаковую ширину (чего нельзя добиться, изменяя ширину столбцов мышкой). Для этого выделите столбцы от А до К и выполните команду Формат®Столбец®Ширина, в поле ввода Ширина столбца введите какое-либо значение, меньшее установленного, например, 5.

4. В ячейку В3 следует поместить формулу, которая возводит в квадрат число, составленное из десятков, указанных в столбце А и единиц, соответствующих значению, размещенному в строке 2. Таким образом, само число, которое должно возводиться в квадрат в ячейке В3 можно задать формулой =А3*10+В2 (число десятков, умноженное на десять плюс число единиц). Остается возвести это число в квадрат.

5. Воспользуйтесь Мастером функций. Для этого выделите ячейку, в которой должен разместиться результат вычислений (В3), и выполните команду Вставка®Функция или нажмите на кнопку Вставка функции на Панели инструментов.

6. В появившемся диалоговом окне Мастера функций среди предложенных категорий функций выберите «Математические», затем выберите нужную вам функцию: «СТЕПЕНЬ» и нажмите кнопку Шаг. В следующем диалоговом окне введите в окне Число ваше основание степени: А3*10+В2, и в окне Показатель – нужный показатель степени: 2. Так же, как и при непосредственном наборе формулы в ячейке электронной таблицы, нет необходимости вводить адрес каждой ячейки, на которую ссылается формула, с клавиатуры. Нажмите кнопку OK. Тогда в ячейке В3 появится результат вычислений. Однако полученную формулу нельзя скопировать на остальные ячейки таблицы, так как при этом будут изменены адреса ячеек А3 и В2.

7. В этом случае следует применить абсолютные ссылки на ячейки первого столбца и второй строки. Поэтому исправьте имеющуюся формулу =СТЕПЕНЬ(А3*10+В2;2) на правильную =СТЕПЕНЬ($А3*10+В$2;2). Для этого выделите ячейку В3 и, установив текстовый курсор в Строку формул, вставьте знак $ в указанные места.

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

Цветовое оформление таблицы.

Часто для оформления электронного варианта документа или цветной печати требуется произвести цветовое оформление текста. Оформления электронных таблиц Excel различными цветами осуществляется почти также как и в Word.

 

Для того чтобы изменить фон выделенного интервала ячеек в меню Формат®Ячеек следует выбрать вкладку Вид. Затем выберите в группе Цвет заливки любой из имеющихся цветов.

 

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

 

Задание 3.

Оформите сделанную вами таблицу квадратов различными цветами и дополните ее заголовком.

1. Оформите заголовок таблицы. В ячейке А1 установите размер шрифта 14пт и наберите «Таблица квадратов двузначных чисел».

2. Теперь следует установить заголовок по центру всей таблицы. Для этого выделите интервал ячеек с А1 по К1, в меню Формат®Ячеек выберите вкладку Выравнивание. Затем в группе Выравнивание по горизонтали выберите по центру выделения и нажмите OK.

3. Для украшения электронного варианта документа выделите участки вашей таблицы разными цветами. Для изменения фона выделенного интервала ячеек в меню Формат®Ячеек выберите вкладку Вид. Затем выберите в группе Цвет заливки любой цвет на ваш вкус. В образце таблице на рис.1. выбрано: бирюзовый для фона квадратов чисел; лиловый для фона цифр, составляющих единицы и десятки двузначного числа; коричнево-зеленый для фона заголовка таблицы. Цвет шрифта можно изменить, выбрав в меню Формат®Ячейки вкладку Шрифт, затем в группе Цвет установить любой из имеющихся там цветов. В образце таблице на рис.1. выбран белый цвет шрифта заголовка.

Если вы все сделали правильно, то у вас должна быть такая таблица (образец):

 

Таблица квадратов двузначных чисел

Рис.1.

 

Имя ячейки.

В Excel предусмотрен очень удобный способ ссылки на ячейку с помощью присвоения этой ячейке произвольного имени. Чтобы присвоить ячейке имя, выделите ее и выберите команду Вставка®Имя®Присвоить. На экране появится диалоговое окно с полем ввода: Имя. В этом окне можно набрать любое слово, которое и будет именем ячейки.

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

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

 

Задание 4.

Оформите прайс-лист на товары в зависимости от курса доллара на сегодняшний день.

1. Сначала подготовьте таблицу, состоящую из столбцов: «Наименование товара», «Цена в $ US», «Цена в руб.». Заполните все столбцы, кроме последнего С. Столбец «Наименование товара» заполните текстовыми данными (перечень товаров по вашему усмотрению или см. образец таблицы на рис. 1.), а столбец «Цена в $ US» любыми числами или см. рис. 2.

Образец таблицы:

Прайс-лист	Дата:
Курс доллара:
Наименование товара	Цена в $ US	Цена в руб.
Компьютер Pentium 233MHz	309,70
Компьютер Pentium II 400MHz	480,20
Компьютер Pentium III 500MHz	608,50
Принтер матричный Epson LQ 670	376,66
Принтер струйный Epson Stylus 900C	420,08
Принтер лазерный HP LJ 4050	11310,98
Сканер HP ScanJet 5100С	222,24
Картридж HP DJ 720 цв	30,60
Тонер HP LJ 5L	5,76
Мышь NET Mous Pro 3x	6,80

Рис. 2.

2. В ячейку А2 введите текст: «Курс доллара».

3. В ячейку В2 введите курс доллара на сегодняшний день.

4. В столбце «Цена в р.» должна разместиться формула: «Цена в $ US»*«Курс доллара». Отведите под значение курса доллара отдельную ячейку, на которую будете ссылаться в формуле. Эта ссылка должна быть абсолютной, т.е. значение курса доллара можно брать только из этой конкретной ячейки с зафиксированным адресом. Используйте ссылку на имя, которое можно присвоить ячейке. Для этого:

1) выделите ячейку, в которой находится курс доллара,

2) выполните команду Вставка®Имя®Присвоить. В появившемся диалоговом окне уже будет находиться имя ячейки: «Курс_доллара» (ее точный адрес уже приведен в поле ввода «Формула»). Нажмите кнопку ОК.

5. Теперь введите формулу для подсчета цены в рублях. Для этого выделите ячейку С4 столбца «Цена в руб.» и наберите формулу следующим образом: введите знак «=», затем щелкните мышью по ячейке В2 (это ячейка с именем «Курс_доллара»), затем введите знак «*». После этого щелкните мышью по ячейке В4, в которой расположена цена в долларах. Формула должна выглядеть приблизительно так: =Курс_доллара *В4.

6. Заполните остальные ячейки этого столбца вашей формулой с помощью Маркера заполнения.

7. Выделите соответствующие ячейки и примените к ним денежный формат числа, установив количество знаков после запятой: 2. Осталось произвести оформление таблицы.

Установка текущей даты.

Часто бывает необходимо устанавливать на каком-либо документе дату его выпуска. Электронные таблицы Excel предусматривают очень удобный способ автоматической установки текущей даты. Для этого существует специальная категория функций, которая называется Дата и время.

Среди этой категории функция СЕГОДНЯ() автоматически устанавливает текущую дату. Аргументов эта функция не имеет. Если к ячейке применить команду СЕГОДНЯ(), то в ней появится дата в формате число.месяц.год, например, 10.02.00. Дата обновляется при каждом последующем открытии файла.

Если вам необходимо установить и дату и время создания таблицы, то следует воспользоваться функцией ТДАТА(). Аргументы этой функции также остаются пустыми. Время в этой команде не изменяется непрерывно, а лишь при выполнении каких-либо операций с электронными таблицами. Если к пустой ячейке применить команду ТДАТА(), то в ней появится дата в формате число.месяц.год и время в формате часы:минуты, например, 09.03.00 17:25.

 

Задание 5.

1. Оформите заголовок таблицы. Текст заголовков столбцов наберите полужирным шрифтом и выровняйте по центру ячеек.

2. Измените ширину столбцов так, чтобы надписи в каждой из ячеек помещались в одну строку.

3. Расширьте строку и примените вертикальное выравнивание по центру. Для этого в меню Формат®Ячейки выберите вкладку Выравнивание и в группе по вертикали выберите п о центру.

4. В ячейке С1 наберите текст: «Дата:».

5. Выделите таблицу и задайте для нее внешнее обрамление. Выделите интервал ячеек А3:С14 и установите внутренние границы ячеек.

6. Теперь установите дату, которая будет изменяться автоматически. Для этого выделите ту ячейку, в которой будет размещаться дата (в нашем случае - ячейку С2). Затем в меню Вставка выберите пункт Функция. Среди категорий выберите Дата и время. Затем в группе Функция выберите СЕГОДНЯ и нажмите OK. В появившемся диалоговом окне нажмите OK еще раз.

Если вы все сделали правильно, то в результате должна быть таблица примерно такого вида:

 

Прайс-лист	Дата:
Курс доллара:	29,50	09.03.00
Наименование товара	Цена в $ US	Цена в руб.
Компьютер Pentium 233MHz	309,70	9 136,15
Компьютер Pentium II 400MHz	480,20	14 165,90
Компьютер Pentium III 500MHz	608,50	17 950,75
Принтер матричный Epson LQ 670	376,66	11 111,47
Принтер струйный Epson Stylus 900C	420,08	12 392,36
Принтер лазерный HP LJ 4050	11 310,98	333 673,91
Сканер HP ScanJet 5100С	222,24	6 556,08
Картридж HP DJ 720 цв	30,60	902,70
Тонер HP LJ 5L	5,76	169,92
Мышь NET Mous Pro 3x	6,80	200,60

Рис. 3.