Лабораторные работы по MS Excel

42 Pages·1.04 MB·Russian

by  Басков Н.Н.

Басков Н.Н.
Лабораторная работа № 1 Форматирование ячеек. Выделение диапазонов. Рабочие
листы Excel. ___________________________________________________________________________ 2
Лабораторная работа № 2. Работа с таблицами Excel. Ввод формул. __________________ 7
Лабораторная работа № 3 Относительные и абсолютные адреса ячеек. Графическое
представление числовых данных. _______________________________________________________ 9
Лабораторная работа №4. Связанные таблицы. ___________________________________ 13
Лабораторная работа №5. Абсолютные адреса ячеек _______________________________ 18
Лабораторная работа №6. Создание пользовательских функций ____________________ 19
Лабораторная работа №7. Линейная оптимизация _________________________________ 21
Лабораторная работа №8. Решение задач линейного программирования симплекс
методом _____________________________________________________________________________ 25
Лабораторная работа №9. Транспортная задача ___________________________________ 27
Лабораторная работа №10. Работа со списками. Сортировка данных. Промежуточные
итоги. Автофильтр. ___________________________________________________________________ 32
Лабораторная работа №11. Сводные таблицы _____________________________________ 34
Лабораторная работа №12. Автоматизация документа _____________________________ 37
Лабораторная работа №13. Консолидация. ________________________________________ 39

1
Лабораторные работы
Лабораторная работа № 1
Форматирование ячеек. Выделение диапазонов. Рабочие листы Excel.
Электронная таблица – компьютерный эквивалент обычной таблицы, в клетках
(ячейках) которой записаны данные различных типов: тексты, числа, даты, формулы.
Главное достоинство электронной таблицы – это возможность мгновенного
пересчета всех данных, связанных формульными зависимостями при изменении
значения любой исходной ячейки.
Отличительной чертой Excel является то, что рабочее поле имеет вид таблицы,
где имена столбцов – латинские буквы, а строки имеют нумерацию. Также курсор мыши
имеет совершенно иной вид – .
1. Форматы ячеек.
В Excel каждая ячейка обладает многими свойствами, в том числе форматами
отображения информации на экране и выводе ее на печать, которые можно изменить
или оставить их по умолчанию, т.е. в том формате, который использует и предлагает
сама программа (рис. 1).

Рис. 1. Диалоговое окно Формат ячеек
Основные свойства диалогового окна Формат ячеек:
 Число – закладка для выбора числового формата, определения количества
десятичных знаков, выбора обозначения введенного числа;
 Выравнивание – закладка для определения местоположения и ориентации
2
Басков Н.Н.
информации (числа или текста) внутри ячейки, а также задание требования по переносу
слов в ячейке ниже и др.
 Шрифт – закладка для выбора шрифта, его начертания, размера, а также
определение цвета, способа подчеркивания и специальных эффектов (зачеркнутый,
верхний индекс, нижний индекс);
 Граница – закладка для выбора типа линии ее цвета и места расположения
на сторонах ячейки (возможно проведение диагоналей в ячейке);
 Заливка – закладка для выбора фонового цвета и типа узора при заливке
ячейки;
 Защита – закладка для установки защиты на одну или несколько ячеек от
несанкционированного изменения или удаления данных.
По умолчанию все числа представляются в общем формате. В этом формате
число может состоять максимум из 11 цифр, если позволяет ширина ячейки. Числа
отображаются как целые (например, 21947 или 12387), или с десятичной точкой
(запятой) (например, 12,57 или 3,14159), или в научной (экспоненциальной) форме
(например, 9,43Е+7 или 21, 212Е-5).
2. Выделение диапазонов
Часто при работе в Excel требуется, чтобы та или иная операция (копирование,
перемещение, форматирование и др.), относилась не к одной, а сразу к нескольким
ячейкам. В этом случае перед выполнением операции необходимо выделить нужные
ячейки. Блок выделенных ячеек может состоять из одной (одиночный блок) или из
нескольких частей (кусочный блок). На экране блок выделяется инверсией цвета (рис 2)

Рис. 2. Выделение диапазона
С помощью мыши можно производить следующие выделения:
 одиночного блока — при нажатой левой клавише протащить курсор мыши от
левой верхней ячейки до правой нижней ячейки выделяемого блока (здесь и далее
выделение можно начинать с любой угловой ячейки);
 строки — щелкнуть по заголовку строки (здесь и в других описанных ниже
3
Лабораторные работы
случаях речь идет о заголовках, как элементах окна документа;
 столбца — щелкнуть по заголовку столбца;
 нескольких строк — при нажатой левой клавише протащить курсор мыши по
заголовкам строк;
 нескольких столбцов — при нажатой левой клавише протащить курсор мыши
по заголовкам столбцов;
 всей таблицы – щелкнуть по кнопке, находящейся на пересечении заголовков
строк и столбцов таблицы
Совместное выделение мышью и клавиатурой
 одиночного блока — щелкнуть по левой верхней, а затем, удерживая Shift, по
правой нижней ячейке выделяемого блока (выделение можно начинать с любой угловой
ячейки);
 кусочного блока — выделить мышью одиночный блок, а затем, удерживая
Ctrl, также, мышью, выделить остальные части блока;
 нескольких строк, следующих подряд — щелкнуть по заголовку верхней
строки, а затем, удерживая Shift, по заголовку нижней строки выделяемого блока
(выделение можно начинать с нижней строки);
 нескольких столбцов, следующих подряд — щелкнуть по заголовку левого
столбца, а затем, удерживая Shift, по заголовку правого столбца выделяемого блока
(выделение можно начинать с правого столбца);
 нескольких строк, не следующих подряд — щелкать по заголовкам строк,
удерживая Ctrl;
 нескольких столбцов, не следующих подряд — щелкать по заголовкам
столбцов, удерживая Ctrl;
 блока заполненных ячеек — установить табличный курсор на одну из ячеек
выделяемого блока, а затем, удерживая Shift, двойными щелчками по границам
выделения (курсор мыши превращается в наклоненную влево стрелку) расширить его.
3. Рабочие листы в Excel.
В Excel каждый файл это рабочая книга, которая может состоять из нескольких
рабочих листов, поэтому в одном файле можно поместить разнообразные сведения и
установить между ними необходимые связи. На рабочих листах могут быть размещены
такие таблицы, для которых при выводе на печать потребуется несколько листов
4
Басков Н.Н.
бумаги.
Поэтому не путайте Рабочий лист с обычным листом бумаги.
В Excel рабочие листы по умолчанию имеют названия: Лист1, Лист2, Лист3,...и
т.д. Имена листов отображаются на ярлычках в нижней части окна книги. Для перехода
с одного листа на другой следует указать соответствующий ярлычок. Название
активного (текущего) листа выделено жирным шрифтом.
Слева от них имеются кнопки прокрутки листов.

Переименование рабочего листа.
Для систематизации и облегчения поиска информации в рабочей книге удобнее
присваивать названиям листов собственные имена, отражающие их смысловое
содержание, например: "Реализация", "Цена", "Выручка" и т.п.
Существует два способа выполнения данной операции.
1-й способ:
 Щелкните правой кнопкой мыши на ярлычке Лист1 из контекстного меню
выберите команду Переименовать прежнее название листа закрасится черным цветом.
 Введите новое имя поверх старого и нажмите Enter.
2-й способ:
 Выберите двойным нажатием левой кнопки мыши нужный ярлычок листа.
 Введите новое имя поверх старого и нажмите Enter.
Добавление рабочих листов.
При работе в Excel часто требуется добавить один или несколько новых рабочих
листов. Существует два способа добавления рабочих листов в рабочую книгу Excel.
1-й способ:
В меню Вставка выберите команду Лист;
Чтобы вставить несколько листов, выберите необходимое количество листов,
удерживая нажатой клавишу SHIFT, затем из меню Вставка выполните команду Лист.
(В этом случае добавится столько листов, сколько было выбрано первоначально).
2-й способ:
5
Лабораторные работы
Щелкните правой кнопкой мыши на ярлычке листа из контекстного меню и
выберите команду Добавить...–Лист–OK;
Чтобы вставить несколько листов, выберите необходимое количество листов,
удерживая нажатой клавишу SHIFT, затем правой кнопкой мыши вызовите
контекстное меню и выберите команду Добавить–Лист–OK. (В этом случае добавится
столько листов, сколько было выбрано первоначально).
Примечание: Обратите внимание, куда вставились новые листы. Какие у них
имена?
Удаление рабочих листов.
При удалении одного или нескольких листов в Excel необходимо знать, что
после их удаления отменить данную операцию невозможно ! При удалении рабочих
листов программа Excel выдает предупреждение о невозможности отмены данной
операции и два варианта выхода из этой ситуации:
кнопка OK – процедура удаления будет продолжена и листы удалятся;
кнопка Отмена – процедура удаления будет остановлена (отменена),
Существует два способа удаления рабочих листов.
1-й способ:
 Активизируйте лист;
 В меню Правка выберите команду Удалить лист;
 Чтобы удалить несколько листов, выберите необходимое количество
листов, удерживая нажатой клавишу SHIFT, затем из меню Правка
выберите команду Удалить лист. (В этом случае удалится столько листов,
сколько было выбрано первоначально).
2-й способ:
 Активизируйте лист;
 Щелкните правой кнопкой мыши на ярлычке листа из контекстного меню
и выберите команду Удалить;
 Для удаления нескольких листов, выберите необходимое количество
листов, удерживая нажатой клавишу SHIFT, затем правой кнопкой мыши
на ярлычке листа вызовите контекстное меню и выберите команду
Удалить (В этом случае удалится столько листов, сколько было выбрано
первоначально).
6
Басков Н.Н.
Перемещение рабочих листов.
В Excel существует три способа перемещения рабочих листов.
1-й способ:
 Активизируйте лист;
 В меню Правка выберите команду Переместить/Скопировать лист;
 В диалоговом окне левой кнопкой мыши выберите, перед каким листом
должен расположиться выбранный рабочий лист, затем нажмите OK;
2-й способ:
 Активизируйте лист;
 Правой кнопкой мыши вызовите контекстное меню и выберите команду
Переместить/Скопировать...;
 В диалоговом окне левой кнопкой мыши выберите, перед каким листом
должен расположиться выбранный рабочий лист, затем нажмите OK;
3-й способ:
 Левой кнопкой мыши "захватите" ярлычок листа и перенесите его в другое
место.
Примечание: Если при перемещении листа держать нажатой клавишу Ctrl,
произойдет перемещение копии листа и к его имени добавится цифра (2), в этом случае
исходный лист останется на прежнем месте.
Лабораторная работа № 2. Работа с таблицами Excel. Ввод формул.
1. Запустите программу Excel (Пуск–Программы–Microsoft Excel).
2. Выделите диапазон ячеек А1:G1. На панели инструментов нажмите кнопку
(Объединить и поместить в центре). В объединённых ячейках напечатайте
«Выручка от реализации книжной продукции».
3. Выделите диапазон ячеек С2:D2. Напечатайте «лето–осень 2008».
4. В ячейке В4 введите «Июнь». Выделите эту ячейку, подведите курсор мыши
на маркер заполнения (чёрный квадратик в правом нижем углу прямоугольника).
Курсор измениться на чёрный крестик, зажмите левую кнопку мыши и потяните за
маркер заполнения (см. рис. 1) вправо до автоматического ввода слова «ноябрь».
5. В ячейки А5 введите «Книжный мир», в А6 – «Книга», в А7 – «Магазин №1»
и в А8 – «Всего». Если текст выходит за границы столбца А, то раздвиньте этот столбец,
7
Лабораторные работы
так чтобы текст полностью помещался в ячейке.

Рис 1. Маркер заполнения
6. Числовые данные введите, как показано на рис. 2.
7. В ячейку Н4 введите «Итого по магазину». Для переноса по словам
выделите ячейку Н8 и выполните команду ФорматЯчейки. Перейдите на закладку
«Выравнивание» и поставьте галочку напротив надписи «переносить по словам»

Рис 2. Числовые данные
8. Для подсчёта итоговых значений для магазина в ячейки Н5 введите формулу
«=СУММ(В5:G5)».
9. Для подсчёта итоговых значение можно воспользоваться и другим метод:
выделите диапазон ячеек В6:Н7, на панели инструментов «Стандартная» нажмите
кнопку (Автосумма). Программа автоматически пропишет формулу для нахождения
суммы по строке (выделите поочерёдно ячейки Н6 и Н7 и посмотрите формулы,
которые стоят в этих ячейках).
10. Для вычисления итоговых данных за каждый месяц (например за июнь)
можно также воспользоваться кнопкой . Выделите ячейку В8 и «протащите» за
маркер заполнения. Программа скопирует формулу, которая стоит в ячейки В8.
11. Для оформления таблицы выделите диапазон ячеек в которых находиться
таблица, включая шапку таблицы, и выполните команду ФорматАвтоформат. Из
предложенных программой автоформатов для таблиц выберите какой-нибудь из
финансовых, например «Финансовый 2» (рис 3).
8
Басков Н.Н.

Рис. 3. Таблица "Выручка от реализации книжной продукции"
12. Для предварительного просмотра таблицы перед печатью (а это делать
необходимо, так как Excel не показывает как располагается таблица на листе бумаги)
нажмите кнопку (Предварительный просмотр).
13. Может так получиться, что ваша таблица разобьётся на несколько листов.
Это очень «некрасиво». Чтобы этого, избежать в режиме «Предварительного
просмотра» нажмите кнопку «Страница…». В открывшимся диалоговом окне
«Параметры страницы» включите опцию «разместить не более чем» и проверьте,
стоят ли единицы в окошках для ширины и высоты.
14. Создайте в своей папке на сервере папку с именем «Excel-Table». В эту
папку вы будите сохранять файлы всех лабораторных работ. Сохраните полученный
файл под именем «Лабораторная работа №2»
Лабораторная работа № 3
Относительные и абсолютные адреса ячеек.
Графическое представление числовых данных.
1. Относительные и абсолютные ссылки
Ссылкой однозначно определяется ячейка или группа ячеек листа, а также
упрощается поиск значений или данных, используемых в формуле. С помощью ссылок
можно использовать в формуле данные, находящиеся в различных местах листа, а также
использовать значение одной и той же ячейки в нескольких формулах. Можно
ссылаться на ячейки, находящиеся на других листах книги или в другой книге, или на
данные другого приложения.
Ссылки на ячейки других книг называются внешними ссылками.
Ссылки на данные других приложений называются удаленными ссылками.
По умолчанию в Excel используются ссылки типа A1, в которых столбцы
9
Лабораторные работы
обозначаются буквами от A до IV (256 столбцов максимально), а строки числами — от 1
до 65 536).
В зависимости от поставленной задачи можно использовать:
относительные ссылки, которые при копировании или перемещении ячейки с
формулой ссылаются на другие ячейки относительно позиции ячейки с формулой;
абсолютные ссылки, которые при копировании или перемещении ячейки с
формулой всегда ссылаются на одну и ту же ячейку (ячейки).
Различия между относительными и абсолютными ссылками.
При создании формулы ссылки обычно изменяются относительно расположения
ячейки, содержащей формулу. Например, ячейка B6 содержит формулу =A5; искомое
значение находится на одну ячейку выше и левее ячейки B6.
При копировании формулы, содержащей относительные ссылки, и вставке ее в
другое место, ссылки будут указывать на другие ячейки. Например, если формула из
ячейки B6 копируется в ячейку B7, то в ячейке B7 будет содержаться формула =A6,
которая ссылается на одну ячейку выше и левее ячейки B7.
Другой пример, если имеется формула, умножающая содержимое ячейки A5 на
ячейку C1 (=A5*C1), то при копировании формулы в любую другую ячейку изменятся
обе ссылки и названия столбцов, и номера строк, т.к. изначально формула содержит
относительные ссылки на ячейки А5 и С1.
Такой тип ссылок называется относительным.
Если необходимо, чтобы ссылки не изменялись при копировании формулы в
другую ячейку, нужно использовать абсолютные ссылки.
Абсолютная ссылка при копировании или перемещении ячейки не изменяет весь
адрес ячейки, т.к. знак $ стоит и перед именем столбца, и перед номером строки.
Чтобы создать полную абсолютную ссылку на ячейку C1, впечатайте знак
доллара перед именем столбца и перед номером строки в адресе ячейки, например,
=A5*$C$1.
Смешенная ссылка при копировании или перемещении ячейки не изменяет тот
параметр адреса ячейки, перед которым стоит знак $ – это либо столбец, либо строка.
1. Запустите программу Excel (Пуск–Программы–Microsoft Excel).
2. Создайте таблицу по образцу (рис. 1). Ячейки в столбце F имеют формат
Процентный, а количество десятичных знаков = 0.
10