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

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

Порядок выполнения.

1. Прочитать текст на страницах 3-15.

2. В папке Мои документы создать свою папку с именем Группа_N, где N – номер вашей группы. В ней создать папку Е1.

3. Запустить табличный процессор Excel, вставить в рабочую книгу три новых листа с названиями Таблица, Копия1 и Копия2, расположив их в указанном порядке после имеющихся в книге листов. Сохранить книгу в папке Е1 под именем Ех_1.

4. На листе Таблица создать таблицу по следующему образцу:

5. Увеличить высоту 1-й строки до 40 пунктов, 2-й, 3-й и 8-й строк до 20 пунктов.

6. Установить для 1 столбца ширину, равную 14, 3 пункта, для 2-7 столбцов ширину, равную 8, 5 пунктов.

7. Установить границы таблицы как показано в образце.

8. Установить для названия таблицы шрифт Arial Black коричневого цвета, полужирного начертания, размером 11 пунктов. Отцентрировать название таблицы по ширине, установить перенос по словам.

9. Установить для диапазонов ячеек A2: G3 и A8: G8 красный цвет шрифта, вертикальное и горизонтальное выравнивание по центру.

10. Залить:

• диапазоны ячеек В3: В8, D3: D8 и F3: F8 бледно-зеленым цветом;

• диапазоны ячеек С3: С8, Е3: Е8 и G3: G8 голубым цветом;

• диапазоны ячеек B5: G5 и B7: G7 светло-желтым цветом.

8. Установить для диапазона ячеек А4: А7 цвет шрифта синий, размер шрифта 12 пунктов и выравнивание по левому краю, а для диапазона ячеек B4: G7 – выравнивание по центру.

9. Защитить лист Таблица от изменений, перед защитой разблокировать диапазоны ячеек F4: G8, В8: Е8.

10. Сохранить изменения в файле.

11. Диапазон ячеек А2: С8 скопировать через буфер обмена на лист Копия1 (перед вставкой копируемого диапазона на лист Копия1 установить курсорную рамку в ячейку A2).

12. Выделить несмежные диапазоны ячеек А2: А8 и D2: G8 и скопировать через буфер обмена на лист Копия2 (перед вставкой копируемых диапазонов на лист Копия2 установить курсорную рамку в ячейку A2).

13. Сохранить измененную рабочую книгу в своей папке под прежним именем. Присвоить листу Лист1 имя График дежурств. Создать на этом листе таблицу по следующему образцу:

14. Для того, чтобы вся таблица поместилась на экране, для всех столбцов, кроме первого, установить ширину, равную 3 пунктам.

15. В ячейку A3 ввести слово «январь», затем произвести автозаполнение расположенных ниже ячеек с помощью маркера заполнения. В ячейку В2 ввести число 1, ячейки расположенные справа от нее заполнить с помощью маркера заполнения. Остальные ячейки таблицы заполнить условными обозначениями дежурных (д1, д2, дЗ и д4), либо штриховкой, применяя копирование и автозаполнение.

16. При помощи команды Правка-Заменить заменить условное обозначение каждого дежурного первой буквой его фамилии: д1 – Гуськов, д2 – Кукушкин, дЗ – Сорокин и д4 – Уточкин.

17. Используя условное форматирование, залить ячейки содержащие буквы Г, К и С соответственно светло-желтым, бледно-зеленым и светло-синим цветом.

18. Сохранить рабочую книгу Ех_1 под именем Задание_1.

Использование формул и функций

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

Формулы

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

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

В арифметических формулах используются следующие операторы: + (сложение), - (вычитание), * (умножение), / (деление), ^ (возведение в степень). Результатом вычисления арифметической формулы является число.

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

Логические формулы могут содержать операторы сравнения: = (равно), < > (не равно), > (больше), < (меньше), < = (не более), > = (не менее), а также специальные логические функции: ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ и НЕ.

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

Запись формулы в Excel должна начинаться знаком =, который можно ввести с клавиатуры или щелчком мыши по соответствующей кнопке в строке формул. Допускается ввод формулы начинать знаками + или -, в этом случае знак равенства вставляется перед формулой автоматически.

Формула может содержать ссылки на ячейки, расположенные на листах текущей рабочей книги или на листах других рабочих книг. В ссылке адрес листа (имя листа) отделяется от адреса ячейки или диапазона восклицательным знаком, например, Лист5! АЗ, а адрес рабочей книги (имя книги) записывают перед именем листа в квадратных скобках, например, [Книга2]Лист5! АЗ.

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

Способы адресации ячеек

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

Формулы можно копировать и перемещать в другие ячейки. При перемещении формулы ссылки, расположенные внутри формулы, не изменяются. При копировании формулы абсолютные ссылки не изменяются, а относительные или смешанные ссылки автоматически модифицируются таким образом, чтобы сохранялось относительное расположение ячейки содержащей формулу и ячеек с входящими в нее операндами. По умолчанию, при вводе адреса ячейки в формулу вводится относительная ссылка на эту ячейку. Например копируется формула =В1+С6 с относительными ссылками из ячейки СЗ в ячейку F5. После копирования формула принимает вид =E3+F8, поскольку ячейки ЕЗ и F8 расположены относительно ячейки F5, как ячейки В1 и С6 относительно ячейки СЗ.

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

Например, копируется формула =B$1+C6 со смешанной и относительной ссылками в ячейку F5. В отличие от предыдущего примера, ссылка на ячейку B1 содержит знак $ перед единицей. Поэтому при копировании в ячейку F5 имя столбца в этой ссылке изменится с В на Е, а номер строки останется прежним и формула примет следующий вид: =E$1+F8.

Для создания абсолютных и смешанных ссылок можно использовать клавишу F4. Если поставить курсор ввода у адреса ячейки и нажать один раз F4, то будет зафиксирована вся ячейка ($D$2 - абсолютная ссылка), два раза -только номер строки (D$2 - абсолютная ссылка по строке), три раза - только название столбца ($D2 - абсолютная ссылка по столбцу). При четвертом нажатии на F4 ссылка вновь становится относительной (D2).

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

Функции

Excel имеет более восьмисот встроенных функций – специальных, заранее созданных формул, имеющих один или несколько аргументов. Запись каждой из функций начинается знаком = и состоит из имени и списка аргументов, заключенного в скобки (например =СРЗНАЧ(А1: А4)). Аргументы в списке отделяются друг от друга точкой с запятой. Тип аргумента зависит от функции. Аргументами могут быть ссылки, числа, текст, логические величины, массивы, формулы (по формуле, например, может проверяться условие в логической функции ЕСЛИ), функции и т.д.

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

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

После запуска Мастера функций на экран выводится диалоговое окно с двумя полями (рис. 11). Для быстрого поиска нужной функции все встроенные функции разделены по типам или категориям, список которых содержится в поле Категория диалогового окна. В поле Выберите функцию выводится список функций выбранной категории.

Рисунок 11. Выбор вида функции с помощью мастера функций.

При вводе функции сначала выбирают ее тип в поле Категория (например, математические, статистические, логические, текстовые и т.д.), а затем в поле Выберите ф ункцию выбирают имя конкретной функции данного типа (например, ПРОИЗВЕД, СУММ, СРЗНАЧ, ЕСЛИ, и т.д.). После подтверждения выбора (кнопка ОК) на экран выводится окно для указания аргументов функции (рис. 12), содержащее для этого специальные строки с кнопками сворачивания окна. Названия строк для ввода обязательных параметров выделены в этом окне жирным шрифтом. В нижней части окна приводится краткое описание выбранной функции и формат ее записи, в котором список аргументов может заканчиваться многоточием, если функция использует несколько однотипных аргументов.

Рисунок 12. Расчет среднего значения данных с помощью мастера функций.

Однотипные аргументы можно вводить вручную с клавиатуры в любую строку ввода в виде последовательности значений, ссылок на ячейки и диапазоны ячеек (через точку с запятой), такие аргументы можно также распределить по нескольким строкам ввода. Удобно вводить аргументы выделением области рабочего листа, содержащей эти аргументы. Чтобы окно мастера не закрывало область выделения его можно свернуть в строку (щелчком по кнопке в правой части строки ввода) или переместить в сторону (нужно нажать левую кнопку мыши в любом свободном месте окна и, не отпуская ее, передвинуть мышь в нужном направлении). Адрес выделенного диапазона автоматически вставляется в строку ввода окна. Свернутое окно затем можно восстановить повторным щелчком по кнопке в этой строке.

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

Чтобы вставить функцию в поле аргумента другой функции (вызвать Мастер функций из Мастера функций) нужно, установить курсор в это поле, шелкнуть мышью по кнопке в строке формул, а затем выбрать требуемую функцию в списке этой строки.

Автосуммирование

Суммирование является самой распространенной математической операцией. Поэтому, для удобства пользователей, кнопка для вставки этой функции вынесена на панель инструментов Стандартная. Чтобы вставить функцию суммирования в ячейку достаточно выделить ее и щелкнуть мышью по кнопке Автосумма . В ячейке появится знак равенства, имя функции (СУММ) и адрес диапазона суммирования (в скобках), который программа предлагает после анализа близлежащих ячеек. Если диапазон определен правильно, далее нужно нажать клавишу Enter, в противном случае – выделить нужный диапазон (диапазоны) ячеек (несмежные диапазоны выделяются при нажатой клавише Ctrl), а затем нажать клавишу Enter.

Редактирование формул и функций

Формулу можно редактировать в содержащей ее ячейке и в строке формул. По умолчанию, в ячейке с формулой отображается результат вычисления. После выделения ячейки записанная в нее формула выводится в строке формул, а чтобы отобразить формулу в ячейке нужно дважды щелкнуть по этой ячейке мышью или нажать клавишу F2. Установив курсор ввода в нужное место формулы, можно править ее обычными средствами (используя клавиатуру, команды меню и кнопки на панелях инструментов). Изменения фиксируются нажатием кнопки в строке формул или клавиши Enter.

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

Выполнение логических функций

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

С помощью формул и функций в Excel можно задать (запрограммировать) и автоматически выполнить практически любую последовательность операций над данными. Для программной обработки информации большое значение имеют условные команды, с их помощью организуют циклы и ветвления в программах. Среди логических функций Excel имеется функция ЕСЛИ, которую можно использовать в указанных целях. Эта функция имеет следующий формат:

ЕСЛИ (< логическое выражение>; < выражение1>; < выражение2>).

Первый аргумент функции – логическое выражение (в частном случае, условное выражение), которое принимает одно из двух значений: Истина или Ложь. В первом случае функция ЕСЛИ принимает значение выражения1, а во втором – значение выражения2. В качестве выражения1 и выражения2 можно использовать формулы, функции, ссылки и константы. Например, функция, сравнивающая две ячейки (В1 и В2) и выводящая в результате сравнения на экран сообщение «больше» или «меньше», имеет вид: =ЕСЛИ(В1> В2; " больше"; " меньше").

Создание примечаний

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

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

Рисунок 13. Вставка примечания.

Чтобы скрыть в книге примечания и (или) их индикаторы, необходимо в меню Сервис / Параметры открыть вкладку Вид и в группе Примечание выбрать вариант не отображать; только индикатор; примечание и индикатор.

Работа со справочной системой Microsoft Excel

Ответы на все вопросы, касающиеся работы табличного процессора можно найти в справочной системе Microsoft Excel. Запуск справки осуществляется клавишей F1 или функцией справка Microsoft Excel меню справка. При этом появляется помощник, выводящий окно для ввода вопроса или открывается дополнительное окно справки.

Рисунок 14. Пиктографическое меню справки Microsoft Excel.

В окне справки находится пиктографическое меню, содержащее шесть клавиш: автомозаика, показать/скрыть, назад, вперед, печать, параметры (рис. 14).

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