Часть III Табличный процессор Excel

Лабораторная работа № 1. Освоение среды ППП EXCEL

Цель работы: освоение основных приемов работы с электронными таблицами. Создание простой таблицы.

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

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

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

3. Через меню Вид отобразить панели инструментов Стандартная, Форматирование. По умолчанию в указанных панелях представлены наиболее часто используемые кнопки. Если в процессе работы возникнет необходимость в выборе какой-либо дополнительной кнопки, то нужно выбрать указатель

, а затем воспользоваться командой Добавить или удалить кнопки.

4. С помощью меню Вид вывести на экран Строку формул (если она не отображена). В указанной строке отображается содержимое ячеек электронной таблицы (либо текст, либо непосредственно формула, которая «скрывается» за тем или иным числовым результатом). С помощью данной строки целесообразно осуществлять контроль за организацией вычислений в ячейках таблицы.

5. Книги и листы. Книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Каждая книга может состоять из нескольких листов. Листы служат для организации и анализа данных. Имена листов находятся на ярлычках, расположенных в нижней части окна книги. Для перехода с одного листа на другой необходимо активизировать с помощью мыши соответствующий ярлычок. Название текущего листа всегда выделено жирным шрифтом. Листы можно переименовывать, вставлять, удалять, перемещать или копировать в пределах одной книги или из одной книги в другую. Для этого необходимо выполнить щелчок правой кнопкой мыши на соответствующем ярлычке. В соответствии с вышесказанным выполнить последовательно следующие действия:

· Создать свою папку (по номеру группы) на диске, сохранить рабочую книгу в своей папке с помощью команды Сохранить как меню Файл.

6. Ввод данных в ячейки таблицы. Рабочая область листа состоит из ячеек, при этом одна из них выделена (текущая ячейка). Каждая ячейка имеет адрес, состоящий из буквы столбца и номера строки, на пересечении которых она находится. Адрес текущей ячейки отображается в строке формул. Каждая ячейка таблицы может содержать текст, число или формулу. Значением ячейки может быть текст или число. Ввести следующие данные: в ячейку А1 -текст «Освоение среды EXCEL», в ячейку А2 -число 325,5, в В2 - 65. При вводе числовой информации следует помнить, что десятичные числа в зависимости от установленного по умолчанию формата могут вводиться либо с запятой, либо с точкой (например, 325,5 или 325.5). При правильном вводе десятичных чисел число автоматически прижимается к правому краю ячейки таблицы. Если число остается прижатым к левому краю ячейки, то следует ввести его снова, сменив знак точки на запятую, или наоборот. (Примечание! При вводе чисел удобно использовать дополнительную цифровую клавиатуру). В ячейку С2 ввести формулу =А2-В2. (Внимание! Любая формула в Excel должна начинаться со знака =, в противном случае введенная информация будет интерпретироваться как текстовая). Если в формуле встречаются ссылки на адреса ячеек, рекомендуется при вводе формулы не вводить адреса ячеек с клавиатуры, а щелкать мышкой по ячейке, адрес которой необходимо ввести. Для ввода указанной формулы нужно выполнить следующие действия:

· набрать с клавиатуры «=», затем щелкнуть мышкой по ячейке А2, ввести с клавиатуры знак «-», щелкнуть мышкой по ячейке В2;

· нажать клавишу «Enter», чтобы зафиксировать ввод.

Значением ячейки С2, содержащей формулу, будет число, полученное в результате вычисления по формуле.Если выделить эту ячейку, то в строке формул будет представлена формула, по которой производились вычисления.

7. Выделение данных. Выполнить следующие операции:

· Выделить столбец В целиком. Для этого нужно щелкнуть на имени столбца (В). Аналогично выделить строку 3.

· Выделить столбцы С:Е. Для этого нужно щелкнуть на имени столбца (С) и, удерживая кнопку мыши, протащить ее до столбца Е.

· Выделить область ячеек В2:D6, затем отменить выделение щелчком мыши в любой другой области таблицы.

· Выделить несмежные области А2:С3 и В6:Е8. Для этого сначала нужно выделить область А2:С3, затем нажать клавишу «Ctrl», и, удерживая ее, выделить область В6:Е8.

· Выделить всю область таблицы, щелкнув по месту пересечения номеров строк и столбцов в левом верхнем углу таблицы.

Следует помнить, что при выделении данных (а также и при вводе информации) указатель мыши должен иметь вид «большого плюса» -

, в противном случае может произойти, например, перенос данных вместо их выделения.

· Скопировать данные из ячеек А2:С2 в ячейки А5:С5. Для этого нужно выделить диапазон ячеек А2:С2, скопировать содержимое в буфер обмена (меню Правка, команда Копировать), установить курсор в нужную позицию (для данного случая в А5), вставить данные (Правка \ Вставить).

· В ячейку А5 вместо числа 325,5 ввести число 555 и посмотреть, как изменится результат в ячейке С5.

· Отредактировать формулу в ячейке С5. Для этого нужно поставить курсорную рамку на указанную ячейку, щелкнуть мышкой на строке формул (или нажать клавишу «F2»), а затем заменить формулу на =(А5-В5)*2.

9. Вставка и удаление ячеек, строк, столбцов.

Вставка дополнительных строк и столбцов осуществляется с помощью меню Вставка. Предварительно необходимо выделить строку, выше которой добавляется строка, или столбец, левее которого вставляется новый столбец.

· Через меню Вставка вставить дополнительную строку между 9 и 10, а также дополнительный столбец между столбцами А и В.

· Выделить и затем удалить строки 9 и 10 через меню Правка. Используя клавишу «Delete» удалить содержимое ячеек С8:D9, а также строки с 7 по 13.

10. Перемещение ячеек. Для перемещения ячеек необходимо сначала их выделить, а затем навести указатель мыши на границу выделенного блока (ячейки). Когда указатель мыши примет вид стрелки, можно перетащить блок ячеек (ячейку) на новое место. Аналогично можно перемещать строки и столбцы с предварительным их выделением.

· Переместить ячейки В5:D5 на две строки ниже.

11. Ввод данных с помощью автозаполнения. В Microsoft Excel предусмотрена возможность автоматического заполнения календарных дат, временных интервалов и числовых рядов, включая комбинации чисел и текста, например, «Область1», «Область2» и так далее. Команда Параметры меню Сервис используется для создания настраиваемых списков, позволяющих заполнять текстовые записи (например, можно вынести названия филиалов фирмы в заголовки строк или столбцов).

Выполнить следующие варианты автозаполнения ячеек таблицы различными данными:

· В ячейку А9 ввести «январь». Затем установить указатель мыши в нижнем правом углу ячейки, и, когда указатель примет вид тонкого черного крестика

, протащить мышь вправо на 6 столбцов. В ячейку А10 ввести «понедельник». Аналогично выполнить автозаполнение в строке до воскресенья.

· Автозаполнение числовых рядов. В ячейку А12 ввести число 1. Чтобы задать приращение, нужно в следующую ячейку ряда ввести ее значение (для данного примера в ячейку А13 ввести число 3). Величина приращения будет определяться разностью значений, находящихся в этих ячейках. Выделить ячейки, содержащие начальные элементы (А12:А13), и выполнить автозаполнение до числа 13 (т.е. перетащить маркер заполнения через заполняемые ячейки).

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

· Для ряда «фев 95», «мар 95»,..., выбрать команду Заполнить по месяцам.

· Для ряда «янв 96», «янв 97»,..., выбрать команду Заполнить по годам.

12. Форматирование ячеек. Excel содержит практически тот же набор инструментов форматирования, что и текстовый редактор Word (выравнивание текста, изменение стиля, размера, шрифта, цвета и т.д.). Общее правило форматирования, как и в других приложениях, заключается в обязательном предварительном выделении нужного диапазона таблицы (ячейки, блока ячеек, столбца, строки и т.д.) и в выборе нужной операции форматирования. Специфичными операциями форматирования для Excel являются изменение высоты строк и ширины столбцов, а также установление различных видов представления числовой информации.

· Рассмотреть возможности изменения высоты строк (ширины столбцов) через меню Формат. Скрыть, а затем вновь показать строки 15 и 16, столбцы В и D.

· Изменить ширину столбца В (или высоту любой строки) с помощью мыши. Для этого нужно поднести указатель мышки к левому (правому) краю заголовка столбца (В), и, когда указатель примет вид

, протащить мышку, изменяя ширину столбца.

Выравнивание текста. Текст в ячейке можно выровнять по правому, левому краю и по центру. Выделить столбец А и задать различные типы выравнивания, используя кнопки панели управления или меню Формат \ Ячейки \ Выравнивание. Ввести в ячейку С7 текст несколько слов. Задать для ячейки С7 отображение – переносить по словам (Формат \ Ячейки \ Выравнивание, отметить галочкой пункт переносить по словам в блоке Отображение)

Изменение типа шрифта, размера и начертания задается через меню Формат \ Ячейки \ Шрифт. Оформление границ, цвета и узора ячейки задается через меню Формат \ Ячейки \ Граница (Вид).

Числа в Excel могут представляться в различных форматах, которые можно задать через меню Формат \ Ячейки \ Число.

13. Использование ссылок. Как было показано ранее, основным средством анализа данных в Excel являются формулы, которые могут включать в себя следующие элементы: операторы, ссылки на ячейки, значения, функции и имена. Как правило, основными составляющими элементами любой формулы являются ссылки на адреса ячеек. В Excel используются ссылки, в которых столбцы обозначаются буквами от A до IV (256 столбцов максимально), а строки числами - от 1 до 16384. Различают два основных типа ссылок: о тносительные и абсолютные. По умолчанию при наборе формул в Microsoft Excel используются относительные ссылки (например, А1). Признаком абсолютной ссылки является знак «$» ($A$1). Кроме этого, можно использовать смешанные ссылки, например, A$1 или $A1. При вводе и редактировании адреса (ссылки) в формуле последовательное нажатие клавиши F4 позволяет получить все возможные виды адреса.

Различия между относительными ссылками и абсолютными ссылками проявляются при копировании формул из одной ячейки в другую. Относительная ссылка в формуле используется для указания адреса ячейки, вычисляемого относительно адреса ячейки, в которой содержится формула. Абсолютная ссылка в формуле используется для указания фиксированного адреса ячейки. При перемещении или копировании формулы из одной ячейки в другую абсолютные ссылки в формуле не изменяются, а о тносительные ссылки автоматически обновляются в зависимости от нового положения. При смешанной адресации относительная часть ссылки, не содержащая знак «$», может обновляться при копировании, а абсолютная часть, со знаком «$», останется без изменения.

На Листе1 в ячейку D12 ввести число 25. В ячейку С15 ввести формулу: =D12*2. Т.к. в формуле, содержащейся в ячейке С15, имеется относительная ссылка на ячейку D12, то Excel воспринимает эту формулу следующим образом: значение ячейки, находящейся одним столбцом правее меня и тремя строками выше меня, умножить на два. Скопировать формулу из ячейки С15 в ячейки С16, С20, D15, F15, E18. Проанализировать, как изменилась формула при копировании!!!

На Листе2 в ячейку D12 ввести число 25. В ячейку С15 ввести формулу: =$D$12*2. Т.к. в формуле, содержащейся в ячейке С15, имеется абсолютная ссылка на ячейку D12, то Excel воспринимает эту формулу следующим образом: значение ячейки D12 умножить на два. Скопировать формулу из ячейки С15 в ячейки С16, С20, D15, F15, E18. Проанализировать, как изменилась формула при копировании!!!

На Листе3 в ячейку D12 ввести число 25. В ячейку С15 ввести формулу: =D$12*2. Т.к. в формуле, содержащейся в ячейке С15, имеется cмешанная ссылка на ячейку D12 (относительная ссылка на столбец и абсолютная ссылкана строку), то Excel воспринимает эту формулу следующим образом: значение ячейки, находящейся одним столбцом правее меня в 12 строке, умножить на два. Скопировать формулу из ячейки С15 в ячейки С16, С20, D15, F15, E18. Проанализировать, как изменилась формула при копировании!!!

На Листе4 в ячейку D12 ввести число 25. В ячейку С15 введите формулу: =$D12*2. Т.к. в формуле, содержащейся в ячейке С15, имеется смешанная ссылка на ячейку D12 (абсолютная ссылка на столбец и относительная ссылка на строку), то Excel воспринимает эту формулу следующим образом: значение ячейки, находящейся в столбце D тремя строками выше меня, умножить на два. Скопировать формулу из ячейки С15 в ячейки С16, С20, D15, F15, E18. Проанализировать, как изменилась формула при копировании!!!

14. Создание простой таблицы. Разработать ведомость заработной платы.

· На Листе5 в ячейку А1 занести текст «Минимальный размер оплаты труда, руб.»

· В ячейку А4 занести текст «Ведомость заработной платы кафедры экономики.

· В ячейки A6:K6 занести шапку таблицы в следующем виде:

КТУ – Коэффициент трудового участия (от 1 до 5)

МРОТ – минимальный размер оплаты труда (720 руб.)

В ячейки А7:А13 занести порядковые номера, используя автозаполнение.

· Заполнить произвольно столбцы ФИО и Разряд (целые числа в диапазоне от 4 до 17).

· В ячейку А16 занести текст «Премиальный фонд, руб.».

· В ячейку D7 занести формулу для вычисления оклада сотрудника (Оклад = МРОТ * Разряд). (Внимание! МРОТ для всех сотрудников один и содержится в конкретной ячейке А2, а разряд у каждого сотрудника свой. При составлении формулы используйте верный тип адресации). Скопируйте формулу из ячейки D7 в ячейки, содержащие оклады остальных сотрудников.

· В ячейке D14 вычислить общую сумму окладов, для чего выделить блок D7:D14 и выбрать на панели инструментов пиктограмму Автосуммирование –

. Скопировать полученную формулу в E14:K14.

· Занести в ячейку E7 формулу для вычисления надбавки сотрудника в размере 30% от его оклада, затем скопировать ее в ячейки, содержащие надбавки остальных сотрудников.

· В ячейки F7:F13 ввести произвольные значения КТУ (целые числа от 1 до 5).

· В ячейку G7 ввести формулу для вычисления премии (Премия = Премиальный фонд *КТУ / Общая сумма КТУ). При составлении формулы обратить внимание на вид адресации. Адреса каких ячеек не должны меняться при копировании формулы? Скопировать ее в другие ячейки столбца. Обратите внимание, что итоговая сумма премий всех сотрудников должна быть равна премиальному фонду. Измените значение премиального фонда в ячейке А17 со 100000 на 250000. Что изменилось в таблице и почему?

· Заполнить остальные графы таблицы. Подоходный налог берется в размере 13%, а в пенсионный фонд отчисляется 1% от общего заработка.

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

· Разместить заголовок таблицы по центру. Для этого выделить блок А4:K4 и выбрать пиктограмму Центрировать по выделению -

(или меню Формат \ Ячейки, закладка Выравнивание, отображение: объединить ячейки, выравнивание во горизонтали: по центру).

· Поставить защиту на ячейки, содержащие оклады.

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

15. Сохранить рабочую книгу на диске.Следует помнить, что при необходимости можно сохранить файл в нужном формате (например, в формате более ранних версий (Excel 5.0) и др.). В этом случае нужно указать в диалоговом окне при сохранении нужный тип файла.

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

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

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

Вычислить во сколько раз отличаются характеристики фигур с радиусом 1 от соответствующих характеристик фигур с радиусами 3, 5 и 10.

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

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

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

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

панели инструментов Стандартная. Функции сгруппированы по категориям, таким как «Финансовые», «Математические и тригонометрические», «Статистические», «Логические» и др. В нижней части окна мастера функций отображается синтаксис, а также определение выделенной функции.

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

После выбора функции из списка появляется диалоговое окно выбранной функции, которое содержит поля для ввода аргументов. В нижней части окна функции отображается описание того аргумента, в поле ввода которого стоит курсор. Аргументы могут быть числами, текстами, логическими величинами или ссылками. Задаваемые аргументы должны иметь допустимые для данного аргумента значения. Аргументы могут быть как константами, так и формулами. Эти формулы, в свою очередь, могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Microsoft Excel можно использовать до семи уровней вложения функций.

1. Функции даты и времени. Следуя описанным выше правилам использования встроенных функций Excel, выполнить следующие действия:

· В ячейку А1 вставить функцию СЕГОДНЯ, которая возвращает текущую дату.

· В ячейку В1 вставить функцию ДЕНЬНЕД. В открывшемся окне в качестве аргумента указать адрес А1. Выбрать подходящее значение аргумента Тип. Что возвращает эта функция?

· Определить в какой день недели вы родились?

2. Текстовые функции. Выполнить следующие операции с текстовой информацией:

· В ячейку А3 ввести текст «годовой отчет». В ячейку А4 вставить функцию ПОДСТАВИТЬ. В качестве первого аргумента указать адрес ячейки А3, в качестве второго - «годовой», третьего - «недельный».

· В ячейке А6, используя функцию ПОВТОР, получить текст,состоящий из 50 символов «#».

Что вызывает тренды на фондовых и товарных рынках Объяснение теории грузового поезда Первые 17 лет моих рыночных исследований сводились к попыткам вычислить, когда этот...

Что способствует осуществлению желаний? Стопроцентная, непоколебимая уверенность в своем...

Что будет с Землей, если ось ее сместится на 6666 км? Что будет с Землей? - задался я вопросом...

ЧТО ПРОИСХОДИТ, КОГДА МЫ ССОРИМСЯ Не понимая различий, существующих между мужчинами и женщинами, очень легко довести дело до ссоры...

Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте: