Задания и порядок выполнения. Упражнение 1.Создать на первом рабочем листе таблицу по приведенному образцу (рис

Упражнение 1.Создать на первом рабочем листе таблицу по приведенному образцу (рис. 1.18).

1. В ячейку А1 ввести текст «Примеры форматирования данных».

2. В ячейку А3 ввести текст «По левому краю».

3. В ячейку А5 ввести текст «По правому краю».

4. В ячейку А7 ввести текст «По центру».

5. В ячейку А9 ввести текст «12121212».

6. В ячейку С4 ввести текст «По центру выделения (ячейки С4:J4)».

7. Переименовать рабочий лист, задав ему новое имя Таблица1. Для этого на ярлыке рабочего листа нажать правую кнопку мыши, выбрать команду Переименовать и ввести новое имя.

Рис. 1.18. Пример таблицы

8. Скопировать на второй рабочий лист содержимое первого рабочего листа.

9. Переименовать рабочий лист, дав ему новое имя Таблица2.

10. Включить режим автоматической установки ширины столбцов с помощью команды Формат – Столбец – Автоподбор ширины.

11. Объединить диапазон ячеек С4:J4. Для этого необходимо выполнить следующее:

11.1. Выделить диапазон С4:J4.

11.2. Выполнить команду Формат – Ячейки.

11.3. На вкладке Выравнивание установить флажок Объединение ячеек.

11.4. Нажать кнопку ОК.

12. Оформить таблицу так, чтобы внешний ее вид соответствовал рис. 1.19. Для выравнивания в ячейке использовать команду Формат – Ячейки – Выравнивание.

Рис. 1.19. Таблица оформлена с помощью диалогового
окна Формат ячейки, вкладка Выравнивание

13. Оформить таблицу в соответствии с рис. 1.20.

Рис. 1.20. Таблица оформлена с помощью диалогового
окна Формат ячейки, вкладки Шрифт, Границы и Вид

14. Для оформления таблицы использовать кнопку Границы на панели инструментов Форматирование.

15. Для выделения данных в таблице использовать различные варианты оформления из меню Формат – Ячейка – Вид – Заливка ячеек / Узор.

Сохранить созданную книгу под именем ЛР_ФИОс помощью команды Файл – Сохранить как…[3].

Упражнение 2. В созданном файле ЛР_ФИОна Листе3 создать таблицу «Затраты на производство на предприятии», изображенную на рис. 1.21 и заполнить ее.

Рис. 1.21. Таблица «Затраты на производство на предприятии»

1. В ячейку А1 ввести текст «Затраты на производство на предприятии».

2. В ячейку А2 ввести текст «№ п/п».

3. В ячейку В2 ввести текст «Подразделение».

4. В ячейку С2 ввести текст «Статья затрат».

5. В ячейку D2 ввести текст «Сумма затрат за 1-й квартал».

6. В ячейку Е2 ввести текст «Сумма затрат за 2-й квартал».

7. В ячейку Е3 ввести текст «Апрель».

8. В ячейку F3 ввести текст «Май».

9. В ячейку G3 ввести текст «Июнь».

10. Отформатировать ячейки А1:G1. Для этого:

10.1. Выделить данный блок ячеек;

10.2. Выполнить команду Объединить и поместить в центре, используя кнопку на панели инструментов Форматирование.

10.3. Выделить текст жирным шрифтом.

11. Отформатировать ячейки А2:G2. Для этого:

11.1. Выделить ячейки А2:G2;

11.2. Выполнить команду Формат – Ячейки – Выравнивание и задать следующие параметры: горизонтальное – по центру; вертикальное – по центру; переносить по словам – поставить флажок.

11.3. При необходимости увеличить ширину столбцов.

12. Объединить ячейки Е2:G2.Для этого:

12.1. Выделить ячейки Е2:G2.

12.2. Выполнить команду Формат – Ячейки – Выравниваниеи поставить флажок – Объединение ячеек.

13. Аналогично пунктам 12.1 и 12.2 объединить следующие ячейки: А2 и А3, В2 и В3, С2 и С3, D2 и D3.

14. Заполнить столбец А:

способ 1. Введите в ячейку А4 число 1, в ячейку А5 число 2, выделите ячейки А4:А5. Установите курсор мыши в правый нижний угол ячейки А5 так, чтобы указатель мыши приобрел изображение креста и, нажав левую кнопку мыши, протяните курсор на требуемую величину.

способ 2. Выполните команду Правка – Заполнить – Прогрессия и задайте следующие параметры: расположение – по столбцам; тип – арифметическая; шаг – 1; предельное значение – 10. Нажмите кнопку ОК.

15. Заполните ячейки столбцов Ви С данными о 10 подразделениях.

16. Аналогично пункту 14 заполните столбцы 4, 5, 6, 7, выбрав значения произвольно.

17. В рабочую книгу добавить Лист4. Для этого на ярлычке Лист3 нажать правую кнопку мыши и выполнить команду Добавить. В окне Вставка выбрать Лист и нажать кнопку ОК.

18. Скопируйте таблицу на Лист4, используя команды Копировать и Вставить.

19. Присвойте Листу3 имя Затраты на производство, а Листу4 – имя Копия1.

20. На листе Копия1 поменяйте местами столбцы 5 и 7.

21. Сохраните рабочую книгу под тем же именем с помощью команды Файл – Сохранить.

Вопросы для самопроверки

1. Для чего предназначен электронный процессор MS Excel?

2. Каково назначение элементов окна MS Excel?

3. Что такое ячейка и как определяется ее положение в таблице?

4. Дать определение рабочего листа и рабочей книги.

5. Что является объектами MS Excel?

6. Что такое редактирование ячеек?

7. Назовите способы редактирования.

8. Опишите процесс копирования и перемещения содержимого ячеек.

9. Каковы способы удаления содержимого ячеек?

10. Чем операция Очистить отличается от операции Удаление?

11. Опишите процесс Автозаполнение ячеек.

12. Опишите процесс Заполнение прогрессией.

13. Какие операции можно производить со строками и столбцами?

14. Какие существуют способы изменения ширины столбца и высоты строки?

15. Как можно скрыть или отобразить столбцы или строки?

16. Что такое форматирование ячеек?

17. Как изменить формат данных в ячейке?

18. Какие вкладки содержит диалоговое окно Формат ячеек? Объясните назначение каждой из них.

19. Опишите назначение числовых форматов вкладки Число диалогового окна Формат ячеек.

Раздел 2. Формулы и функции в MS Excel

2.1. Ссылки на ячейки. Относительные и абсолютные ссылки

Ссылкой однозначно определяется ячейка или группа ячеек листа, а также упрощается поиск значений или данных, используемых в формуле. С помощью ссылок можно использовать в формуле данные, находящиеся в различных местах листа, а также использовать значение одной и той же ячейки в нескольких формулах. Кроме того, можно ссылаться на ячейки, находящиеся на других листах книг или в другой книге, или на данные другого приложения. Ссылки на ячейки других книг называются внешними ссылками. Ссылки на данные других приложений называются удаленными ссылками [1].

При создании формулы ссылки обычно изменяются относительно расположения ячейки, содержащей формулу. Например, ячейка В6 содержит формулу =А5; искомое значение находится на одну ячейку выше и левее ячейки В6(рис. 2.1). Такая ячейка называется относительной.

При копировании формулы, содержащей относительные ссылки, и вставке ее в другое место, ссылки будут указывать на другие ячейки. Так, если формула из ячейки В6 копируется в ячейку В7, то в ячейке В7 будет содержаться формула =А6, которая указывает на одну ячейку выше и левее ячейки В7 (рис.2.2).

Рис. 2.1. Пример относительной ссылки	Рис. 2.2. Пример изменения относительной ссылки при копировании

При копировании относительной ссылки она меняется на разность между ячейкой оригиналом и ячейкой, содержащей копию (как по столбцам, так и по строкам). Скопировали на один столбец вправо ссылку Е5, ссылка также изменится на один столбец вправо – F5.

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

Абсолютную ссылку можно установить с помощью:

1) клавиатуры, набрав знак доллара $ перед названием столбца и/или номером строки;

2) клавиши F4. Если в формуле подвести курсор к тому адресу, который нужно сделать абсолютно и нажимать клавишу F4, то последовательно
(с каждым нажатием) будет меняться адресация ячейки; при первом нажатии на клавишу F4 будет абсолютная адресация и по строке, и по столбцу, при втором – абсолютная по строке и относительная по столбцу, затем абсолютная по столбцу и относительная по строке, затем опять относительная и по строке, и по столбцу.

Существует три типа абсолютных ссылок.

1. Абсолютная ссылка – фиксирует полностью ячейку (не изменяется ни столбец, ни строка при копировании). Например: $Е$10.

2. Абсолютная ссылка по столбцу – фиксирует только столбец (при копировании изменяется только строка, столбец не изменяется). Например: $Е10.

3. Абсолютная ссылка по строке – фиксирует только строку (при копировании изменяется только столбец, строка не изменяется). Например: Е$10.

2.2. Операторы в формулах

Операторами обозначаются операции, которые следует выполнить над операндами формулы. Операндами могут быть: константы (числа, текст, даты, время), ссылки (адреса ячеек) или диапазоны ссылок, заголовки, имена (ячейкам можно давать вместо адресов осмысленные имена) или функции. В MS Excel включено четыре вида операторов: арифметические, текстовые, операторы сравнения и адресные операторы[2].

Арифметическиеоператоры используются для выполнения основных математических вычислений над числами (например, сложение, вычитание, умножение, деление и получение численных результатов). Результатом выполнения арифметической операции всегда является число.

Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ.

Текстовый оператор «&» используется для обозначения операции объединения последовательностей символов в единую последовательность.

Адресные операторы объединяют диапазоны ячеек для существующих вычислений.

2.3. Использование функций для вычисления значений

Функции – заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке, называемjм синтаксисом. Эти функции позволяют выполнять как простые, так и сложные вычисления [2].

Список аргументов может состоять из чисел, ссылок на ячейки, диапазона, текста, логических величин (например, ИСТИНА или ЛОЖЬ). Кроме того, аргументы могут быть как константами, так и формулами, которые называются вложенными. Эти формулы, в свою очередь, могут содержать другие формулы.

Ввод функции начинается со знака равно «=», затем указывается функция, затем вводится открывающаяся скобка, в которой указываются аргумент(ы), а затем – закрывающаяся скобка (рис. 2.3)

=СУММ(А10:Е20)

Рис. 2.3. Синтаксис функции

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

=ЕСЛИ(СРЗНАЧ(Е3:Е7)>50;СУММ(D3:D7);0)

Рис. 2.4. Вложенные функции

Функции можно вводить вручную, но в Excelпредусмотрен Мастер функций, позволяющий вводить их в полуавтоматическом режиме и практически без ошибок. Для вызова Мастера функции необходимо выполнить команду Вставка – Функция или нажать на кнопку , расположенную левее строки формул. В результате появится диалоговое окно Мастер функций (рис. 2.5), которое содержит поле Поиск функции и два списка: Категория и Функция. В поле Поиск функции можно ввести описание действия, которое нужно выполнить, затем нажать на кнопку Найти. В списке Функция появятся названия рекомендуемых функций. В списке Категория можно выбрать группу функций, а в списке Функция одну из функций выбранной группы. В этом диалоговом окне под списками находится область подсказок. В ней отображается синтаксис функции, а так же краткое описание выбранной функции.

Рассмотрим это на примерах двух функций: математической функции СУММ и логической функция ЕСЛИ.

Синтаксис функции СУММ:

СУММ(число1; число2;…)

где число1, число 2, … – это от 1 до 30 аргументов, для которых требуется определить сумму. Аргументами могут быть числа, ссылки на ячейки, текстовые представления чисел, которые непосредственно введены в список аргументов. Если аргумент является массивом или ссылкой, то в массиве или ссылке учитываются только числа. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются. Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают ошибки.

Например, =СУММ(А1:А9) – суммирует значения выделенного диапазона ячеек.

Синтаксис функции ЕСЛИ:

ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь),

где лог_выражение – это любое выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ, т.е. условие, которое требуется проверить;

значение_если_истина – это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА;

значение_если_ложь – это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ.

Например, дана таблица с данными (столбец А, рис. 2.6).

Рис. 2.6. Таблица с данными

В столбец В вводим следующие формулы:

Ошибки, возникающие при вычислениях:

2.4. Группы функций

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

Статистические. Позволяют выполнять статистический анализ диапазонов данных.

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

Аргументами финансовых функций часто являются следующие величины:

· будущее значение – стоимость вложения или ссуды по завершении всех отложенных платежей;

· количество выплат – общее количество платежей или периодов выплат;

· выплата – объем периодической выплаты по вложению или ссуде;

· текущее значение – начальная стоимость вложения или ссуды;

· ставка – процентная ставка или скидка по вложению или ссуде;

· режим выплат – режим выплат, с которым осуществляются выплаты (в конце или в начале месяца).

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

Логические функции. Предназначены для проверки выполнения условия или нескольких условий. Так, функция ЕСЛИ позволяет определить, выполняется ли указанное условие и возвратить одно значение, если условие ИСТИНО, и другое, если оно ЛОЖНО.

Функции обработки текста. С их помощью можно производить действия над строками, например: изменить регистр или определить длину строки. Можно также объединить несколько строк в одну. Например, формула =”Отчет от “&ТЕКСТ(СЕГОДНЯ(); “дд-мм-гг”) показывает, как с помощью функций СЕГОДНЯ() и ТЕКСТ() создать сообщение, содержащее текущую дату и привести его к виду “дд-мм-гг”.

Функции работы с базами данных. Если необходимо убедиться в том, что значения из списка удовлетворяют условию, можно использовать функции работы с базами данных. С их помощью, например, можно определить количество записей в таблице о продажах или извлечь те записи, в которых значение поля «Сумма» больше 1000, но меньше 2500. Эти функции, как правило, имеют три аргумента: база данных, поле и условие [6].

Лабораторная работа № 2.1
Знакомство со способами адресации MS Excel

Цель: ознакомиться со способами адресации в MS Excel и научиться использовать их при вычислениях в таблицах.