Лабораторная работа № 15-А. Создание и редактирование таблиц
Модуль MS Excel состоит из 3 лабораторных работ:
- Создание и редактирование таблиц
- Ввод формул, вычисление значений функций и выражений.
- Создание и оформление таблиц-отчётов, построение диаграмм.
Эти работы составлены так, что алгоритмизация показательных примеров позволяет всем ученикам достичь правильного результата. Ввиду того, что скорость выполнения работ у учеников различна, количество выполненных работ также различно, и поэтому применяется разноуровневый подход.
Построение рабочего листа
Вы научитесь:
- Создавать простые таблицы.
- Изменять ширину столбцов и высоту строк.
- Выделять группы ячеек.
- Вводить информацию одновременно в несколько ячеек и пользоваться автозаполнением.
- Производить операцию суммирования содержимого ячеек.
Краткое содержание
Одной из частей интегрированного пакета Microsoft Office 2003 является программа обработки электронных таблиц Excel 2003. Её область применения широка: бухгалтерские и инженерные расчеты, составление сводок, диаграмм, прайс-листов и т.д.
Задание: Запустите электронную таблицу Excel. Любой документ, создаваемый и редактируемый в Excel, является в общем случае рабочей книгой, которая состоит из одного или нескольких листов. Окно Excel развернуто на весь экран.
1. Верхняя строка — заголовок.
2. Под заголовком расположена строка меню.
3. Еще ниже — панели инструментов.
Первая панель — Стандартная. Вторая панель — Форматирование. Третья панель — Рисование (расположена внизу).
4. Следующая строка — поле имени и строка формул. Поле имени показывает имя текущей ячейки, а строка формул отображает содержимое этой ячейки.
5. Ниже строки формул располагается рабочее поле, состоящее из ячеек, в которые можно вводить текст, числа, формулы. Столбцы обозначаются латинскими буквами (A, B, C, D), а строки — порядковыми номерами. Таким образом, каждая ячейка имеет свой адрес (A1, B8, C4).
6. Слева от горизонтальной полосы прокрутки расположен ряд кнопок переключения между листами, а также ярлычки рабочих листов.
7. Самая нижняя строка — строка состояния.
Основы работы с книгами Excel
1. Создание новой рабочей книги.
При запуске Excel автоматически открывает пустую рабочую книгу, с которой вы можете начать свою работу. Но иногда нужно открыть (создать) новую рабочую книгу при редактировании уже существующей. Для этого надо нажать кнопку 
на стандартной панели инструментов (или выбрать пункт меню Файл, опция Создать).
2. Создание и редактирование таблиц.
Ячейка, на которой стоит указатель, является активной. Перемещаться между ячейками можно с помощью клавиш управления курсором (или щелкнув мышью по нужной ячейке). В ячейки можно вводить текст, числа, формулы.
Ввод и удаление символов производится обычным образом. Во время ввода текста в строке формул появляется вводимый текст, а слева от нее – три кнопки: 
— отменить ввод, 
— принять ввод, 
— изменение формулы.
Если текст не помещается в одной ячейке, то для перевода курсора на следующую строку нажимается ALT + ENTER.
Удаление содержимого ячейки осуществляется после установки курсора на удаляемую ячейку нажатием клавиши DELETE на клавиатуре. Редактирование введенного в ячейку текста может осуществляться двумя способами:
1) двойным щелчком левой клавиши мыши по редактируемой ячейке до появления текстового курсора;
2) нажатием клавиши F2 на клавиатуре, когда указатель Excel стоит на редактируемой ячейке.
Задание: Наберите в ячейке A1 текст в две строчки: Мы изучаем математику. Нажмите ENTER. Поставьте курсор на ячейку A1. Давайте исправим слово математику на слово информатику, пользуясь любым из двух вышеприведенных способов.
Очень часто текст в ячейке служит заголовком для нескольких столбцов. Для этого случая существует специальная возможность отцентрировать заголовок относительно всех столбцов. Выделите те ячейки, для которых создается заголовок (сам заголовок должен быть в числе этих выделенных ячеек). После этого нажмите кнопку Объединить и поместить в центре (
).
Задание: Наберите в ячейке A2 текст: устройство ЭВМ. Наберите в ячейке B2 текст: теория алгоритмов. Содержимое ячейки A1 является как бы заголовком к темам, написанным в ячейках A2, B2, C2. Сделайте вышеуказанным способом заголовок для всех трех столбцов.
Ввод чисел аналогичен вводу текста. Если число не помещается в ячейку, то вместо него вводятся символы #. Части дроби разделяются запятой (например: 2,3; -43,56).
Ввод формул в Excel начинается с символа = (знак равенства), а потом вводится формула и нажимается ENTER или кнопка , а отменить ввод можно нажатием кнопки .
Замечание: если ячейка содержит формулу, то в ней самой выводится посчитанное значение, а в Строке формул можно увидеть непосредственно формулу.
1.1. Изменение ширины столбцов и высоты строк.
В процессе составления таблицы вероятна ситуация, когда данные или текст не помещаются по ширине в столбцах. Для выхода из такой ситуации надо:
1) установить указатель мыши на границу между буквами в заголовках столбцов;
2) когда указатель мыши примет форму двунаправленной стрелки, переместить указатель влево, чтобы уменьшить размеры столбца, или вправо для увеличения.
Аналогично меняется высота строк.
Точное задание параметров: меню Формат, опция Столбец, пункт Ширина.
Задание: Попробовать изменить ширину столбцов и высоту строк. Вернуть все в прежнее положение.
1.2. Выделение группы ячеек.
Для выделения всего столбца щелкнуть мышью на заголовке столбца (для нескольких столбцов — протянуть мышью). Аналогично для строк. Выделить весь лист — щелкнуть по пересечению имен строк и столбцов. Снять выделение можно, щелкнув в любом месте листа.
Задание: Попробовать выделения столбцов, строк и всего листа.
Предположим, что в ячейках A2, D5 и F1 должно быть написано одно и то же слово, например, Ячейка. Щелкните по ячейке A2 и, нажав на клавишу CTRL, щелкните по ячейкам D5 и F1. В результате на рабочем листе будут активными сразу три ячейки. Обычно в таких случаях говорят, что выделяют диапазон несвязанных ячеек. Теперь напишем нужное слово. Когда оно появится в ячейке (какой?) и в строке формул, нажмите на клавиши CTRL и ENTER одновременно.
Задание: Написать в ячейках A10, B10, C10, D10, E10, F10, G10, H10 слово Итого вышеприведенным способом.
Обратите внимание на то, что все ячейки примыкают друг к другу, то есть связаны. Для их выделения воспользуемся таким способом.
Щелкнем по ячейке A10 и, удерживая нажатой клавишу SHIFT, — по ячейке H10. В результате выделена полоска из семи ячеек. Такой диапазон называется связанным и обозначается A10:H10. Теперь можно вводить данные. Сделайте это самостоятельно.
Выделение диапазона связанных ячеек
1. Щелкнуть мышью по первой ячейке диапазона.
2. Нажать и удерживать нажатой клавишу SHIFT.
3. Щелкнуть мышью по последней ячейке диапазона.
4. Отпустить клавишу SHIFT.
Выделение диапазона несвязанных ячеек
1. Щелкнуть мышью по первой ячейке диапазона.
2. Нажать и удерживать нажатой клавишу CTRL.
3. Последовательно щелкнуть мышью по остальным ячейкам диапазона.
4. Отпустить клавишу CTRL.
Ввод информации одновременно в несколько ячеек
1. Выделить диапазон ячеек, в которые вводится одна и та же информация.
2. Написать текст, число или формулу.
3. Нажать одновременно CTRL и ENTER.
Задание: Выделить ячейки C12, D12, E12, F12, C13, D13, E13, F13, C14, D14, E14, F14 (диапазон обозначается C12:F14).
1.3. Автозаполнение.
В ячейке A1 напишем слово Январь и щелкнем мышью по кнопке Ввод (зеленая галочка на панели инструментов). Теперь поместим указатель мыши на правый нижний угол ячейки A1, туда, где расположен маленький квадратик.
Попав на маркер заполнения, указатель мыши изменит свою форму и превратится из большого белого креста в маленький черный крестик. Как только это произойдет, нажмите на левую клавишу мыши и, не отпуская ее, переместите указатель на ячейку A12. Если здесь Вы отпустите левую клавишу мыши, весь выделенный диапазон ячеек заполнится названиями месяцев.
Задание: В ячейке B1 напишите дату 29.12.99. Измените месяц и год в последовательности дат в диапазоне B1:B12, повторив те действия, которые выполняли при составлении списков месяцев.
Автозаполнение
1. Ввести слово или число в ячейку.
2. На панели инструментов щелкнуть по кнопке Ввод.
3. Установить указатель мыши на маркер автозаполнения.
4. Нажать на левую клавишу мыши и, не отпуская ее, переместить указатель мыши на последнюю ячейку заполняемого диапазона.
Если нужно пронумеровать строки или столбцы в созданной таблице, то функция автозаполнения освободит вас от нудной работы. Напишите в ячейке C3 число 1. Установите указатель мыши на маркер заполнения, нажмите на левую клавишу мыши и на клавишу CTRL.
Над указателем (немного правее) появится знак плюс. Не отпуская клавиши CTRL, переместите указатель на ячейку C12, отпустите клавишу мыши и затем клавишу CTRL. Посмотрите, что получилось.
Итак, при нажатой клавише CTRL происходит заполнение списка числами, увеличивающимися на единицу. Если нужно задать другую последовательность, то придется вводить два или три числа.
Заполним диапазон ячеек D3:D12 нечетными числами. Для этого в ячейку D3 запишем первое нечетное число — 1; в ячейку D4 — 3. Выделим диапазон D3:D4 и установим указатель мыши на маркер заполнения. Удерживая нажатой клавишу CTRL, распространим выделение до ячейки D12. В результате диапазон D3:D12 заполнен нечетными числами от 1 до 19.
Задание: Заполните диапазон ячеек E3:E12 четными числами.
Задание: Сделаем отчет о выручке по продаже мороженого за лето. В ячейку A1 поместим заголовок Продажа мороженого по округам города N (в млн. руб).
В A2: Лето 2002 г. В D5: Июль
В A6: Центральный В D6: 160
В A7: Западный В D7: 80
В A8: Северный В D8: 135
В A9: Южный В D9: 115
В C5: Июнь В E5: Август
В C6: 140 В E6: 120
В C7: 85 В E7: 100
В C8: 120 В E8: 140
В C9: 110 В E9: 105
Нам необходимо найти:
— сумму выручки по городу за каждый месяц;
— сумму выручки по каждому округу за все лето;
— общую сумму выручки.
Теперь приступим к расчету. В ячейку C10 надо поместить сумму за июнь по всем округам, то есть C6+C7+C8+C9. Для этого надо выделить ячейку C10 и внести в нее формулу =C6+C7+C8+C9.
Что делать, если в таблице не 4, а 444 строки? В Excel имеется функция СУММ (SUM), тогда формула будет записываться так =СУММ(C6,C7,C8,C9) или =СУММ(C6:C9).
Задание: Посчитайте сумму выручки по городу за июль и август.
Для суммирования значений нескольких ячеек на панели инструментов Стандартная вынесена специальная кнопка 
— автосуммирование. Но перед тем, как посчитать сумму нужных ячеек, надо установить указатель на ячейку, куда поместить сумму. Поставим указатель в ячейку F6, нажмем кнопку Автосуммирование.
Задание: Посчитайте сумму выручки по Западному, Северному и Южному округам, как изложено выше, а затем общую сумму выручки за все лето, которую поместите в ячейку F10.
Задание: Сохраните ваш документ с именем Задания по Excel к себе в каталог 11-б-? (D:\11-б\11-б-?) и закройте вашу рабочую книгу.
Лабораторная работа № 16-А. Ввод формул, вычисление значений функций и выражений
Построение рабочего листа
Вы научитесь:
- Вычислять значения функций вручную (с клавиатуры) и через Мастер функций.
- Находить значения выражений.
- Строить графики функций, заданных аналитически.
Краткое содержание
1. Для того, чтобы Excel выполнял свою основную функцию — считал, нужно указать, что вводимая информация должна обрабатываться как числа. Для этого заполнение ячейки начинают со знака равенства.
Задание: Найдите значение выражения 25–17+2?8.
Задание: Заполните ячейки листа так: в ячейке A1 напишите число 5, в B1 — 3, в C1 напишите =A1+B1 и нажмите на клавишу TAB. Что произойдёт, если в ячейке D1 написать =А1-В1? А как заставить Excel поместить в ячейку E1 результат деления содержимого ячейки C1 на содержимое ячейки D1?
Задание: Запишите в ячейки таблицы числа: в ячейку A2 число 101; в В2 — 25; в C2 — 6; в D2 — 15; в E2 — 20; в F2 — 4; в G2 — 7. Поместите в ячейку H2 значение выражения:
.
Записывать в ячейку адреса других ячеек не очень-то удобно. Можно облегчить запись формул, призвав на помощь мышь. Напишите в ячейке H2 знак равенства. Щёлкните мышью по ячейке A2, без пробела напишите знак <-> (минус) и щёлкните по ячейке B2. Вот так, щелчками мышью по нужным ячейкам, можно легко и быстро записать нужную формулу. Посчитайте значение нашего выражения.
2. Адресация ячеек.
Для произведения вычислений в электронных таблицах надо уметь обращаться к произвольной ячейке таблицы. В Excel используется несколько способов адресации, т. е. обращения к конкретным ячейкам:
абсолютные ссылки;
относительные ссылки;
ссылки с использованием имён.
Относительные ссылки используются по умолчанию. Программа может изменять их самостоятельно, сообразуясь с вашими операциями в электронной таблице.
Задание: Заполнить столбец суммы в рублях.
В ячейку E3 вставим формулу: =C3*D3
Аналогично можно заполнить ячейки E4, E5, E6. Но хорошо, что у нас мало строк, а если бы их было, например, 100?
Выделим ячейку с формулой, подведём указатель мыши в правый нижний угол рамки на чёрный квадратик, растянем выделение на ячейки E4, E5, E6, отменим выделение.
Обратите внимание, что в каждой ячейке помещены различные формулы (отличаются ссылки), т. е. результаты правильные.
Абсолютные ссылки. Значение ссылки не будет меняться при копировании формул. По написанию абсолютная ссылка отличается от относительной тем, что перед названием столбца и номером ряда ставится знак $.
Задание: Заполним сумму в долларах.
В F3 запишем формулу: =E3/D8. Появится значение суммы в долларах.
Скопируем эту формулу в ячейки F4, F5, F6. Что получилось? Появилась надпись #ДЕЛ/0!, означающая деление на ноль. Почему? Мы использовали относительную ссылку, и поэтому компьютер, заполняя ячейку F4, внёс туда формулу =E3/D9, а ячейка D9 пустая, поэтому произошла ошибка деления на ноль. Значит, надо использовать абсолютную ссылку на ячейку D9, т. е. $D$9.
Выделим ячейки F4, F5, F6 и удалим их содержимое.
В F3 запишем формулу: =E3/$D$8. Появится значение суммы в долларах. Скопируем эту формулу в ячейки F4, F5, F6.
3. Вычисление значений функций.
Excel содержит в своём составе весьма большое количество разнообразнейших функций. Вызов этих функций осуществляется с помощью Мастера функций Стандартной панели инструментов или с помощью пункта меню Вставка, опция Функция.
Задание: Построить таблицу значений функции 
на отрезке 
с шагом 0,5.
На новом листе в ячейку A1 ввести аргумент: -10. В меню выбрать: Правка — Заполнить — Прогрессия. В диалоговом окне «Прогрессия» задать: «Расположение» — По столбцам, Шаг — 0,5, «Тип» — Арифметическая, Предельное значение — 10. Нажать OK. Столбец A от 1?й до 41?й строки будет заполнен нужными значениями аргумента.
Передвинувшись в ячейку B1, вызвать Мастер функций. В окне «Мастер функций» выбрать категорию Математические. В списке Выберите функцию выбрать SIN. Нажать OK. На втором шаге работы мастера, в окне «Аргументы функции», в поле Число надо ввести адрес ячейки, в которой содержится аргумент. Её можно просто указать на самом листе, щёлкнув мышью (по A1). Нажать OK. Однако формула введена не вся. Теперь её надо отредактировать вручную. Нажать F2. После ручной правки формула в ячейке должна принять вид: =A1^3-SIN(A1)-0,5 (^ — знак возведения в степень).
Мышью за правый нижний угол скопировать ячейку B1 на диапазон B2:B41. Указанный диапазон теперь заполнен значениями функции для значений аргумента из соответствующих ячеек столбца A.
4. Построение графика функции.
Выделите заполненный значениями функции диапазон ячеек B1:B41. В меню Вставка выберите Диаграмма. В окне «Мастер диаграмм…» на первом шаге выберите тип График и первый из видов. На втором шаге на вкладке «Диапазон данных» оставьте предлагаемый диапазон без изменения (он был выделен), а на вкладке «Ряд» активизируйте поле ввода Подписи оси X и на рабочем листе выделите мышью диапазон A1:A41 (значений аргумента). На третьем шаге предлагается оформить заголовки осей и диаграммы в целом, подписи значений, шкалировку осей, легенду. «Поиграйте» с этими настройками самостоятельно (изменения появляются в миниатюре немедленно). На четвёртом шаге мастера поместите график в тот же лист, в котором находится таблица данных. Нажмите Готово.
Библиография
- Масина О. Н., Ушакова Л. А. WINDOWS, ОФИС-2003. Лабораторный практикум: Учебное пособие. — Елец: Изд-во Елецкого гос. ун-та им. И. А. Бунина, 2007.
- Зеньковский В. А. Применение Excel в экономических и инженерных расчётах. — М.: СОЛОН-Пресс, 2005. — [Серия «Про ПК»].
- Косопова. Расширенный Excel: Метод. пособие для курсов «Специалист» при МГТУ им. Н. Э. Баумана.