Введение

Работа с ОС Windows

Работа в Worde

Работа в Excel

Работа с СУБД Access

Литература

3. Работа с табличным процессором Microsoft Excel

Основные термины

Автозаполнение - Автоматическое заполнение ячеек формулами или числами.

Адрес ячейки - Состоит из номера столбца и строки.

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

Диаграмма - Графическое представление данных. С помощью форматирования имеется возможность представления диаграммы в самых различных видах.

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

Копирование - Копирование в EXCEL предполагает самые различные операции - копирование содержимого ячейки, её формата, формулы, результата, примечания.

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

Надстройки - Надстройки - это специальные средства, расширяющие возможности программы Excel.

Рабочая книга - Документ EXCEL называется Рабочей книгой и состоит из набора рабочих листов, каждый из которых имеет табличную структуру и может содержать одну или несколько таблиц.

Рабочий лист - Рабочий лист состоит из строк и столбцов. Пересечение строк и столбцов образуются ячейки таблицы.

Ссылки - Адреса ячеек, использумые в формулах. Ссылки бывают относительные и абсолютные.

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

3.1. Создание электронных таблиц Microsoft Excel

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

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

Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист может содержать до 256 столбцов, пронумерованных от A до IV. Строки последовательно нумеруются цифрами, от 1 до 65 536 (максимально допустимый номер строки).

Ячейки и их адресация. На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Обозначение отдельной ячейки сочетает в себе номера столбца и строки, на пересечении которых она расположена, например: A1 или DE234. Обозначение ячейки выполняет функции ее адреса. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках. Одна из ячеек всегда является активной и выделяется рамкой активной ячейки. Эта рамка в программе Excel играет роль курсора. Операции ввода и редактирования всегда производятся в активной ячейке. Переместить рамку активной ячейки можно с помощью курсорных клавиш или указателя мыши.

Диапазон ячеек. На данные, расположенные в ячейках, можно ссылаться в формулах как на единое целое. Такую группу ячеек называют диапазоном. Наиболее часто используют прямоугольные диапазоны, образующиеся на пересечении группы последовательно идущих строк и группы последовательно идущих столбцов. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в противоположных углах прямоугольника, например A1:C15. Если требуется выделить прямоугольный диапазон ячеек, это можно сделать протягиванием указателя от одной угловой ячейки до противоположной по диагонали. Рамка текущей ячейки при этом расширяется, охватывая весь выбранный диапазон. Чтобы выбрать столбец или строку целиком, следует щелкнуть на заголовке столбца (строки). Протягиванием указателя по заголовкам можно выбрать несколько идущих подряд столбцов или строк.

Ввод, редактирование и форматирование данных
Отдельная ячейка может содержать данные, относящиеся к одному из трех типов (текст, число или формула), а также оставаться пустой. Программа Excel при сохранении рабочей книги записывает в файл только прямоугольную область рабочих листов, примыкающую к левому верхнему углу (ячейка A1) и содержащую все заполненные ячейки. Тип данных, размещаемых в ячейке, определяется автоматически при вводе. Если эти данные можно интерпретировать как число, программа Excel так и делает. В противном случае данные рассматриваются как текст. Ввод формулы всегда начинается с символа "=" (знак равенства).

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

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

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

Вычисления в электронных таблицах

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

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

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

Пусть, например, в ячейке B2 имеется ссылка на ячейку A3. В случае относительного представления можно сказать, что ссылка указывает на ячейку, которая располагается на один столбец левее и на одну строку ниже данной. Если формула будет скопирована в другую ячейку, то такое относительное указание ссылки сохранится. Например, при копировании формулы в ячейку EA27 ссылка будет продолжать указывать на ячейку, располагающуюся левее и ниже, в данном случае на ячейку DZ28.

При абсолютной адресации адреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4. Элементы номера ячейки, использующие абсолютную адресацию, предваряются символом $. Например, при последовательных нажатиях клавиши F4 номер ячейки A1 будет записываться как A1, $A$1, A$1 и $A1. В двух последних случаях один из компонентов номера ячейки рассматривается как абсолютный, а другой - как относительный.

Копирование содержимого ячеек

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

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

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

Автоматизация ввода

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

Автозавершение. Этот метод используют для автоматизации ввода текстовых данных при вводе текстовых строк, среди которых есть повторяющиеся, в ячейки одного столбца рабочего листа. В ходе ввода текстовых данных в очередную ячейку программа Excel проверяет соответствие введенных символов строкам, имеющемся в этом столбце выше. Если обнаружено однозначное совпадение, введенный текст автоматически дополняется. Нажатие клавиши ENTER подтверждает операцию автозавершения, в противном случае ввод можно продолжать, не обращая внимания на предлагаемый вариант. Можно прервать работу средства автозавершения, оставив в столбце пустую ячейку. И наоборот, чтобы использовать возможности средства автозавершения, заполненные ячейки должны идти подряд, без промежутков между ними.

Автозаполнение числами. Этот метод используется при работе с числами. В правом нижнем углу рамки текущей ячейки имеется черный квадратик - маркер заполнения. При наведении на него указатель мыши (он обычно имеет вид толстого белого креста) приобретает форму тонкого черного крестика. Перетаскивание маркера заполнения рассматривается как операция "размножения" содержимого ячейки в горизонтальном или вертикальном направлении.

Если ячейка содержит число (в том числе дату, денежную сумму), то при перетаскивании маркера происходит копирование ячеек или их заполнение арифметической прогрессией. Для выбора способа автозаполнения следует производить специальное перетаскивание с использованием правой кнопки мыши.
Пусть, например, ячейка A1 содержит число 1. Наведите указатель мыши на маркер заполнения, нажмите правую кнопку мыши, перетащите маркер заполнения так, чтобы рамка охватила ячейки A1, B1 и C1, и отпустите кнопку мыши. Если теперь выбрать в открывшемся меню пункт Копировать ячейки, все ячейки будут содержать число 1. Если же выбрать пункт Заполнить, то в ячейках окажутся числа 1, 2 и 3.

Чтобы точно сформулировать условия заполнения ячеек, следует дать команду Правка —› Заполнить —› Прогрессия. В открывшемся диалоговом окне Прогрессия выбирается тип прогрессии, величина шага и предельное значение. После щелчка на кнопке ОК автоматически заполняются ячейки в соответствии с заданными правилами.

Автозаполнение формулами. Эта операция выполняется так же, как автозаполнение числами. Ее особенность заключается в необходимости копирования ссылок на другие ячейки. В ходе автозаполнения во внимание принимается характер ссылок в формуле: относительные ссылки изменяются в соответствии с относительным расположением копии и оригинала, абсолютные остаются без изменений. Для примера предположим, что значения в третьем столбце рабочего листа (столбце C) вычисляются как суммы значений в соответствующих ячейках столбцов A и B. Введем в ячейку C1 формулу = A1 + B1. Теперь скопируем эту формулу методом автозаполнения во все ячейки третьего столбца таблицы. Благодаря относительной адресации формула будет правильной для всех ячеек данного столбца.

Использование стандартных функций

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

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

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

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

3.2. Применение электронных таблиц для расчетов

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

Итоговые вычисления

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

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

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

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

Использование надстроек

Надстройки - это специальные средства, расширяющие возможности программы Excel. Доступ к ним осуществляется при помощи команды Сервис —› Надстройки. Рассмотрим основные надстройки, поставляемые вместе с программой Excel.
Пакет анализа. Обеспечивает дополнительные возможности анализа данных. Выбор конкретного метода анализа осуществляется в диалоговом окне Анализ данных, которое открывается командой Сервис —› Анализ данных.

Автосохранение. Обеспечивает режим автоматического сохранения рабочих книг через заданный интервал времени. Настройка режима автосохранения осуществляется с помощью команды Сервис —› Автосохранение.

Мастер суммирования. Позволяет автоматизировать создание формул для суммирования данных в столбце таблицы. При этом ячейки могут включаться в сумму только при выполнении определенных условий. Запуск мастера осуществляется с помощью команды Сервис —› Мастер —› Частичная сумма.

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

Построение диаграмм и графиков

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

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

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

Оформление диаграммы. Третий этап работы мастера (после щелчка на кнопке Далее) состоит в выборе оформления диаграммы. На вкладках окна мастера задаются:

• название диаграммы, подписи осей (вкладка Заготовки);
  
• отображение и маркировка осей координат (вкладка Оси);
  
• отображение сетки линий, параллельных осям координат (вкладка Линии сетки);
  
• описание построенных графиков (вкладка Легенда);
  
• отображение надписей, соответствующих отдельным элементам данных на графике (вкладка Подписи данных);
  
• представление данных, использованных при построении графика, в виде таблицы (вкладка Таблица данных).

В зависимости от типа диаграммы некоторые из перечисленных вкладок могут отсутствовать.

Размещение диаграммы. На последнем этапе работы мастера (после щелчка на кнопке Далее) указывается, следует ли использовать для размещения диаграммы новый рабочий лист или один из имеющихся. Обычно этот выбор важен только для последующей печати документа, содержащего диаграмму. После щелчка на кнопке Готово диаграмма строится автоматически и вставляется на указанный рабочий лист.

Редактирование диаграммы. Готовую диаграмму можно изменить. Она состоит из набора отдельных элементов, таких как сами графики (ряды данных), оси координат, заголовок диаграммы, область построения и прочее. При щелчке на элементе диаграммы он выделяется маркерами, а при наведении на него указателя мыши описывается всплывающей подсказкой. Открыть диалоговое окно для форматирования элемента диаграммы можно через меню Формат (для выделенного элемента) или через контекстное меню (команда Формат). Различные вкладки открывшегося диалогового окна позволяют изменять параметры отображения выбранного элемента данных. Если требуется внести в диаграмму существенные изменения, следует вновь воспользоваться мастером диаграмм. Для этого следует открыть рабочий лист с диаграммой или выбрать диаграмму, внедренную в рабочий лист с данными. Запустив Мастер диаграмм, можно изменить текущие параметры, которые рассматриваются в окнах мастера как заданные по умолчанию. Чтобы удалить диаграмму, можно удалить рабочий лист, на котором она расположена (Правка —› Удалить лист), или выбрать диаграмму, внедренную в рабочий лист с данными, и нажать клавишу Delete.

ПРАКТИЧЕСКАЯ РАБОТА

Задание 1. Формирование структуры таблицы и заполнение ее постоянными данными

Подготовьте электронную экзаменационную ведомость, форма которой представлена на рис. 9.

Рис. 9. Форма экзаменационной ведомости

Технология работы:

1. Запустите программу Excel (Пуск —› Программы —› Microsoft Excel) и создайте новую рабочую книгу (команда Файл —› Создать или кнопка Создать на стандартной панели инструментов).

2. Введите в указанные ячейки (табл. 2) тексты заголовка и шапки таблицы, после чего отформатируйте ячейки и данные.

Таблица 2

3. Заполните ячейки столбца B данными о студентах учебной группы (приблизительно 10-15 строк) и отформатируйте данные.

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

5. После списка студентов в нижней части таблицы введите в ячейки столбца A текст итоговых строк согласно рис. 9.

6. Объедините две соседние ячейки для более удобного представления текста итоговых строк. Для этого выделите две ячейки; вызовите контекстное меню и выберите команду Формат ячеек; на вкладке Выравнивание установите флажок Объединение ячеек и нажмите кнопку ОК.

7. Сохраните рабочую книгу в своей рабочей папке (имя файла - Session).

Задание 2. Технология работы с формулами

В рабочей папке с именем Session рассчитайте:

• количество неявок и оценок (отлично, хорошо, удовлетворительно, неудовлетворительно), полученных в данной группе;
  
• общее количество полученных оценок.
  

Предлагается следующий алгоритм для расчета.

1. Ввести дополнительно 5 столбцов.

2. В каждую ячейку ввести формулу по следующему правилу: для каждого студента в соответствующем столбце оценок ставится 1, а в остальных столбцах - 0.

3. В нижней части таблицы ввести формулы подсчета суммарного количества полученных оценок определенного вида и общее количество оценок.

4. Скопировать несколько раз (по числу экзаменов в сессию) этот шаблон на другие листы и провести коррекцию оценок по каждому предмету.

Технология работы

1. Загрузите рабочую книгу с именем Session.

2. Введите названия (5, 4, 3, 2, неявки) соответственно в ячейки F5, G5, H5, I5, J5 вспомогательных столбцов.

3. Используя Мастер функций, введите в столбцы F5-J5 вспомогательные формулы. Рассмотрим эту технологию на примере ввода формулы в ячейку F6:

• установите курсор в ячейку F6 и выберите мышью на панели инструментов кнопку Мастера функций;
  
• в 1-м диалоговом окне выберите вид функции (Категория - логические; Функция - ЕСЛИ) и щелкните по кнопке ОК;
  
• во втором диалоговом окне введите соответствующие операнды логической функции (Логическое выражение - D6 = 5; значение_если_ истина - 1; Значение_если_ ложно - 0) и щелкните по кнопке ОК.
  

4. С помощью Мастера функций введите формулы аналогичным способом в остальные ячейки данной строки.

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

• выделите блок ячеек F6:J6;
  
• установите курсор в правый нижний угол выделенного блока и после появления черного крестика, нажав правую кнопку мыши, перетащите ее до конца таблицы;
  
• выполните в контекстном меню команду Заполнить значения.
  

6. Определите имена блоков ячеек по каждому дополнительному столбцу.
Рассмотрим эту технологию на примере столбца F:

• выделите все значения дополнительного столбца и введите команду Вставка —› Имя —› Присвоить;
  
• в диалоговом окне в строке Имя введите слово ОТЛИЧНО и щелкните по кнопке Добавить;
  
• проводя аналогичные действия с остальными столбцами, создайте еще имена блоков ячеек: ХОРОШО, УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.
  

7. Выделите столбцы F - J целиком и сделайте их скрытыми (команда Формат —› Столбец —› Скрыть).

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

• установите указатель мыши в ячейку С17 подсчета количества отличных оценок и щелкните по кнопке Мастер функций;
  
• в диалоговом окне выберите: Категория - Математические, функция - СУММ; щелкните по кнопке ОК;
  
• в следующем диалоговом окне в строке Число 1 установите курсор и введите команду Вставка —› Имя —› Вставить;
  
• в появившемся диалоговом окне выделите имя блока Отлично и щелкните по кнопке ОК;
  
• повторите аналогичные действия для подсчета количества других оценок в ячейках С18 - С21.
  

9. Подсчитайте общее количество (ИТОГО) всех полученных оценок другим способом:

• установите курсор в пустой ячейке С21 и щелкните по кнопке <S>;
  
• выделите блок ячеек, где подсчитываются суммы по всем видам оценок, и нажмите клавишу Enter.
  

10. Переименуйте текущий лист:

• установите курсор на имени текущего листа и вызовите контекстное меню;
  
• выберите параметр Переименовать и введите новое имя, например Экзамен 1.
  

11. Скопируйте несколько раз текущий лист Экзамен 1:

• установите курсор на имени текущего листа и вызовите контекстное меню;
  
• выберите параметр Переместить/Скопировать, поставьте флажок Создавать копию и параметр Переместить в конец; щелкните по кнопке ОК.
  

12. Сохраните рабочую книгу.

Задание 3. Подготовка ведомостей назначения студентов на стипендию по результатам экзаменационной сессии (рис. 10)

Рис. 10. Форма стипендиальной ведомости

Алгоритм действий по технологии выполнения задания:

1. Загрузите экзаменационную ведомость.

2. На новом листе создайте ведомость стипендии (рис. 10) и скопируйте в нее список группы из экзаменационной ведомости.

3. Вычислите средний балл по результатам сдачи экзаменов по каждому студенту.

4. Используя минимальное значение стипендии и учитывая, что сданы все экзамены, введите формулу начисления стипендии по условию:

• если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;
  
• если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;
  
• если средний балл меньше 3, стипендия не выплачивается.
  

5. Подсчитайте сумму стипендиального фонда всей группы.

Технология работы

1. Загрузите рабочую книгу с именем Session.

2. Создайте в этой книге новый лист - Стипендия, на который из столбцов A и B листа Экзамен 1 скопируйте фамилии и порядковые номера студентов.

3. Оформите название и шапку ведомости назначения на стипендию согласно рис. 10.

4. Укажите размер минимальной стипендии в ячейке D3.

5. Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия - Средний балл и Кол-во сданных экзаменов. Сверьте полученное изображение электронной таблицы с рис. 11.

Рис. 11. Электронная таблица Ведомость назначения на стипендию

6. Введите формулу начисления среднего балла для первого студента (ячейка С6):

• установите курсор в ячейке С6, вызовите Мастер функций и выберите в диалоговом окне параметры: Категория - Статистические, Имя - СРЗНАЧ; щелкните по кнопке ОК;
  
• установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
  
• установите курсор во 2-й строке (имя Число 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
  
• установите курсор в 3-й строке (имя Число 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по третьему экзамену;
  
• щелкните по кнопке ОК; в ячейке С6 появится значение, рассчитанное по формуле: СРЗНАЧ('Экзамен 1'!D6;'Экзамен 1(2)!D6;' Экзамен 1(3)'!D6).
  

7. Скопируйте формулу по всем ячейкам столбца С:

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

8. Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок. Технология ввода аналогична п. 6 (только в диалоговом окне выберите параметр Имя - СЧЕТ).

9. Скопируйте формулу по всем ячейкам столбца D (аналогично п. 7).

10. Введите формулу для вычисления размера стипендии студента в ячейку E6. Эта формула должна иметь следующий вид:

=ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(С6>=3;D6=3);$D$3;0)).

Технология ввода формулы аналогична описанной в п. 6.

11. Скопируйте эту формулу в другие ячейки столбца E (аналогично п. 7).

12. Проверьте работоспособность таблицы путем ввода других оценок в экзаменационную ведомость и изменения минимального размера стипендии.

13. Сохраните рабочую книгу.

Задание 4. Для таблицы на рис. 12 постройте два вида диаграмм - внедренную на лист с исходными данными и на отдельном листе

Рис. 12. Таблица успеваемости к заданию 4

Технология работы

1. Создайте новую рабочую книгу и сохраните ее под именем Diag.

2. Переименуйте Лист 1 на Успеваемость, используя контекстное меню.

3. Создайте таблицу согласно рис. 12, начиная с ячейки A1. В пустые ячейки с названием Факультет введите формулу вычисления среднего балла по факультету.

Для этого:

• вызовите Мастер функций, выберите категорию функций Статистическая, имя функции - СРЗНАЧ; щелкните по кнопке ОК;
  
• введите в первую строку диалогового окна адреса первой и последней ячеек столбца с оценками (B2:B5), используя для этого мышь;
  
• скопируйте формулу в ячейку С6 столбца Высшая математика.
  

4. Для построения внедренной диаграммы нажмите кнопку Мастер диаграмм или выполните команду Вставка —› Диаграмма.

Этап 1. Выбор типа и формата диаграммы: на вкладке Стандартные выберите тип диаграммы Гистограмма и вид диаграммы - номер 1; щелкните по кнопке Далее.

Этап 2. Выбор и указание диапазона данных для построения диаграммы:

• на вкладке Диапазон данных установите переключатель Ряды в столбцах и выделите диапазон данных A2:C6;
  
• в том же диалоговом окне щелкните по вкладке Ряд, в строке с названием Ряд1, установите курсор в строке Имя и щелкните в ячейке В1 с названием Информатика;
  
• в окне Ряд щелкните по названию Ряд2, установите курсор в строке Имя и щелкните в ячейке С1 с названием Высшая математика;
  
• для задания подписей по оси X щелкните в строке Подписи оси X, выделите данные первого столбца таблицы (диапазон A2:A6) и щелкните по кнопке <Далее>.
  

Этап 3. Задание параметров диаграммы:

на вкладке Заголовки введите названия в соответствующих строках:

• на вкладке Легенда поставьте флажок Добавить легенду и переключатель Справа;
  
• щелкните по кнопке Далее.
  

Этап 4. Размещение диаграммы: установите переключатель Поместить диаграмму на имеющемся листе, выберите из списка лист Успеваемость и щелкните по кнопке Готово. В результате на рабочем листе будет создана внедренная диаграмма, представленная на рис. 13.

Сведения об успеваемости

Рис. 13. Диаграмма типа Гистограмма для задания 4

5. Постройте диаграмму другого типа на отдельном листе. Для этого выполните действия, аналогичные описанным в п. 4, но на четвертом шаге установите переключатель На отдельном листе.

Задание 5. Редактирование диаграммы

1. Скопируйте диаграмму (рис. 13) в другое место листа.

2. Добавьте в исходную таблицу столбец с оценками по философии.

3. Измените формат диаграммы на объемный.

4. Вставьте в диаграмму столбец с оценками по философии и измените диаграмму так, чтобы она отражала успеваемость (ось Y) каждой группы (ось Z) в зависимости от дисциплины (ось X).

5. Измените параметры диаграммы - названия осей, уберите легенду.

6. Разместите диаграмму на отдельном листе.

Технология работы

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

2. Добавьте в исходную таблицу новый столбец Философия с различными оценками.

3. Измените формат диаграммы, сделав ее объемной:

• установите курсор мыши во внутренней незаполненной области диаграммы и вызовите контекстное меню;
  
• выполните команду Тип диаграммы, выберите на вкладке Стандартные тип Гистограмма, затем последний из представленных форматов (3-мерная гистограмма);
  
• щелкните по кнопке ОК.
  

4. Вставьте в диаграмму столбцы, отражающие успеваемость по философии:

• установите курсор мыши во внутренней незаполненной области диаграммы и вызовите контекстное меню;
  
• выполните команду Исходные данные и измените параметры: во вкладке Диапазон данных укажите весь диапазон данных A2:D6, включив информацию столбца Философия; установите переключатель Ряды в положение столбцах; на вкладке Ряд введите имена (ряд1 - Информатика, ряд2 - Высшая математика, ряд3 - Философия) и щелкните по кнопке ОК.
  

5. Измените параметры диаграммы:

• установите курсор мыши во внутренней незаполненной области диаграммы и вызовите контекстное меню;
  
• выполните команду Параметры диаграммы и укажите на вкладке Заголовки:

• на вкладке Легенда уберите флажок Добавить легенду и щелкните по кнопке ОК.
  

6. Активизируйте контекстное меню диаграммы и выполните команду Размещение. Установите переключатель Поместить диаграмму на листе в положение отдельном и щелкните по кнопке <ОК>. Результат представлен на рис. 14.

Сведения об успеваемости

Рис. 14. Итоговый результат задания по редактированию диаграммы

Задание 6. Форматирование диаграммы

1. Измените настройку объемного вида трехмерной диаграммы (рис. 14).

2. Измените настройку области диаграммы и области построения диаграммы.

3. Измените форму представления данных на диаграмме: рядов данных и их элементов.

4. Измените отображение осей диаграммы.

5. Проведите форматирование сетки в области построения диаграммы.

6. На созданной диаграмме поместите новую легенду и проведите ее форматирование.

Технология работы

1. Измените настройки параметров диаграммы:

• активизируйте внедренную диаграмму, щелкнув правой кнопкой мыши в пустой области диаграммы;
  
• в появившемся меню выберите команду Объемный вид и в диалоговом окне установите следующие параметры:

• нажмите кнопку ОК.
  

2. Проведите форматирование области диаграммы и области построения диаграммы:

• активизируйте внедренную диаграмму и в появившемся меню выберите команду Формат области диаграммы: задайте на вкладках диалогового окна установки:

• нажмите кнопку ОК;
  
• в области построения диаграммы вызовите контекстное меню, а в нем команду Формат области построения;
  
• задайте в диалоговом окне Вид установки:

   

  Рамка: автоматическая Заливка: белый цвет

• нажмите кнопку ОК.
  

3. Проведите форматирование рядов данных и их элементов:

• установите указатель мыши на ряде 1, вызовите контекстное меню и выполните команду Формат рядов данных;
  
• на вкладке Параметры произведите настройку:

• на вкладке Порядок данных в окне установите курсор на название Информатика и щелкните по кнопке Вверх. Закройте окно;
  
• на вкладке Подписи данных установите переключатель Значения;
  
• на вкладке Вид установите параметры:

• нажмите клавишу ОК;
  
• повторите установку параметров на вкладке Вид для остальных рядов диаграммы: для ряда 2 - желтый, для ряда 3 - зеленый.
  

4. Проведите форматирование осей диаграммы:

• на оси X вызовите контекстное меню, выполните команду Формат оси и установите параметры на вкладках:

• выполните форматирование оси Y:

• выполните форматирование оси Z:

Рис. 15. Итоговый результат задания по редактированию диаграммы.

5. Проведите форматирование сетки, стен и основания:

• в одном из четырех углов диаграммы вызовите контекстное меню и выберите команду Параметры диаграммы. Во вкладке Линии сетки установите параметры:

• в области стен диаграммы вызовите контекстное меню и выберите команду Формат стенок. Во вкладке Вид выберите светло-желтый цвет заливки;
  
• в области основания диаграммы вызовите контекстное меню и выберите команду Формат основания. Во вкладке Вид выберите светло-желтый цвет заливки.

6. Проведите форматирование легенды:

• в одном из четырех углов диаграммы вызовите контекстное меню и выберите команду Параметры диаграммы. Во вкладке Легенда поставьте флажок Добавить легенду и переключатель Справа;
  
• в окне легенды вызовите контекстное меню, выполните команду Формат легенды и установите следующие параметры:

7. Сравните созданную вами диаграмму с образцом на рис. 15.

Задание 7. Построение тренда:

1. Создайте таблицу, аналогичную табл. 3.
   
2. Постройте гистограмму распределения оценок по информатике по группам.
   
3. Постройте линейный тренд для гистограммы.
   
4. Постройте полиномиальный тренд для гистограммы.
   
5. Оформите диаграмму и линии тренда, как представлено на рис. 16.

Таблица 3

Технология работы

1. Создайте таблицу, представленную в табл. 3.

2. Постройте диаграмму распределения по группам оценок по информатике:

• вызовите Мастер диаграмм, выберите на вкладке Стандартные обычный тип диаграммы и нажмите кнопку Далее;
  
• установите курсор в строку Диапазон и выделите в таблице блок ячеек A2:B7;
  
• оформите заголовки и названия осей так, как показано на рис. 16.
  

3. Постройте линейный тренд для диаграммы:

• установите указатель мыши на один из столбиков гистограммы и щелкните левой кнопкой мыши так, чтобы появились на всех столбиках черные метки;
  
• для выделенной гистограммы вызовите контекстное меню и выполните команду Добавить линию тренда;
  
• в диалоговом окне <<Линия тренда>> на вкладке Тип выберите окно Линейная;
  
• на вкладке Параметры установите параметры:

• нажмите кнопку ОК.
  

4. Постройте полиномиальный тренд для гистограммы по технологии п. 3.

5. Оформите диаграмму и линии тренда так, как представлено на рис. 16.

Рис. 16. Гистограмма и тренды

Задание 8. Сортировка данных:

1. Создайте книгу и сохраните ее под именем Spisok, переименуйте Лист1 на Список, а Лист2 - на Сортировка.
   
2. На листе Список создайте таблицу, приведенную в табл. 4.
   
3. Произведите копирование списка (базы данных) с листа Список на лист Сортировка.
   
4. Сделайте сортировку на трех уровнях по возрастанию: по преподавателям, по номеру группы, по коду предмета.

Технология работы

1. Проведите подготовительную работу в соответствии с п. 1 задания.

2. Сформируйте на листе Список шапку таблицы 4. Для этого:

• выделите первую строку, вызовите контекстное меню и выберите команду Формат ячеек;
  
• произведите форматирование ячеек первой строки, установив параметры на вкладке Выравнивание:

• введите названия столбцов (имен полей) в соответствии с таблицей 4;
  
• заполните таблицу данными.
  

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

4. Выполните сортировку по столбцу Таб. № препод. Для этого:

• установите курсор в поле списка и введите команду Данные —› Сортировка. При этом должна выделиться вся область списка. Если этого не произошло, то предварительно выделите весь список, а затем введите указанную команду;
  
• в диалоговом окне <<Сортировка диапазона>> установите:

• установите флажок Идентифицировать поля по записям.

Таблица 4

Задание 9. Выборка данных из списка по критерию отбора, используя Автофильтр:

1. Переименуйте новый лист на Автофильтр и скопируйте на него исходную базу данных (табл. 4).
2. Выберите из списка данные, используя критерий:

• для преподавателя а1 выбрать сведения о сдаче экзамена на положительную оценку;
  
• вид занятий - лк.
  

3. Отмените результат автофильтрации.
4. Выберите из списка данные, используя критерий: для группы 1А получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4.
5. Отмените результат автофильтрации.

Технология работы

1. Переименуйте Лист3 на Автофильтр и скопируйте на него исходную базу данных.
2. Для выполнения п. 2 задания:

• установите курсор в область списка и выполните команду Данные —› Фильтр —› Автофильтр (в каждом столбце появятся кнопки списка);
  
• сформируйте условия отбора записей: в столбце Таб. № препод. из списка условий отбора выберите а1; в столбце Оценка из списка условий выберите Условие и в диалоговом окне сформируйте условие отбора >2;
  в столбце Вид занятия из списка условий выберите лк.
  

3. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные —› Фильтр —› Автофильтр.
4. Выполните п. 4 задания, воспользуясь аналогичной п. 3 технологией фильтрации.
5. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные —› Фильтр —› Автофильтр.

Задание 10. Выборка данных из списка, используя Расширенный фильтр, по Критерию сравнения и по Вычисляемому критерию:

1. Переименуйте новый лист на Расширенный лист и скопируйте на него исходную базу данных (табл. 4).
2. Скопируйте имена полей списка в другую область на том же листе.
3. Сформируйте в области условий отбора Критерий сравнения - о сдаче экзаменов студентами группы 1А по предмету п1 на оценки 4 и 5.
4. Произведите фильтрацию записей на том же листе.
5. Сформируйте в области условий отбора Вычисляемый критерий: для каждого преподавателя выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятия - лк.
6. Произведите фильтрацию записей на том же листе.

Технология работы

1. Переименуйте Лист4 на Расширенный лист и скопируйте на него исходную базу данных.
2. Скопируйте все имена полей списка в другую область (область формирования условий отбора записей) на том же листе, например установив курсор в ячейку J1.
3. Сформируйте в области условий отбора Критерий сравнения в соответствии с п. 3 задания. Для этого после имен полей введите в столбец Номер группы - 1А, в столбец Код предмета - п1, в столбец Оценка - условие - >3.
4. Произведите фильтрацию записей расширенным фильтром на том же листе:

• установите курсор в область списка (базы данных);
  
• выполните команду Данные —› Фильтр —› Расширенный фильтр;
  
• в диалоговом окне Расширенный фильтр задайте параметры:
• Скопировать результат в другое место: установить флажокИсходный диапазон: A1:G17Диапазон условия: J1:P2Поместить результат в диапазон: J4
• нажмите кнопку ОК.
  

5. Сформируйте в области условий отбора Вычисляемый критерий в соответствии с п. 3 задания. Для этого:

• в столбец Вид занятия введите лк;
  
• переименуйте в области критерия столбец Оценка, например, на имя Оценка 1;
  
• в столбец Оценка 1 введите вычисляемый критерий вида
  =G2>СРЗНАЧ($G$2:$G$17).
  

6. Произведите фильтрацию записей расширенным фильтром на том же листе аналогично п. 4.

Задание 11. Выборка данных из списка с использованием Формы:

1. Переименуйте новый лист на Форма и скопируйте на него исходную базу данных (табл. 4).
2. Просмотрите записи списка с помощью формы и добавьте две новые.
3. Сформируйте условие отбора с помощью формы данных: для преподавателя а1 выбрать сведения о сдаче студентами экзамена на положительную оценку, вид занятий - лк.
4. Просмотрите отобранные записи.

Технология работы

1. Переименуйте Лист5 на Расширенный лист и скопируйте на него исходную базу данных. Установите курсор в область списка и выполните команду Данные —› Форма.
2. Просмотрите записи списка и внесите необходимые изменения с помощью кнопок Назад и Далее. С помощью кнопки Добавить добавьте две новые записи.
3. Сформируйте условие отбора в соответствии с заданием:

• нажмите кнопку Критерии, название которой поменяется на Правка;
  
• в пустых строках имен списка введите критерии: в строку Таб. № препод. введите а1, в строку Вид занятия - лк, в строку Оценка - условие >2.
  

4. Просмотрите отобранные записи, нажимая на кнопку Назад или Далее.

Задание 12. Структурирование таблицы ручным способом:

1. Откройте книгу с таблицей, отображенной в табл. 4, переименуйте новый лист на Структура и скопируйте на него исходную базу данных.
2. Отсортируйте строки списка по номеру учебной группы.
3. Вставьте пустые разделяющие строки между учебными группами.
4. Создайте структурные части таблицы для учебных групп.
5. Создайте структурную часть таблицы для столбцов: Код предмета, Таб. № препод., Вид занятия.
6. Закройте и откройте структурные части таблицы.
7. Отмените структурирование.

Технология работы

1. Откройте книгу с именем Spisok, переименуйте Лист6 на Структура и скопируйте на него исходную базу данных.
2. Отсортируйте строки списка по номеру учебной группы (команда Данные —› Сортировка).
3. Вставьте пустые разделяющие строки между учебными группами:

• выделите первую строку с другим, отличным от предыдущей строки, номером группы;
  
• вызовите контекстное меню и выполните команду Добавить ячейки.
  

4. Создайте структурные части таблицы для учебных групп:

• выделите блок строк, относящихся к первой группе;
  
• выполните команду Данные —› Группа и структура —› Группировать. В появившемся окне установите флажок строки;
  

аналогичные действия повторите для других групп.

5. Создайте структурную часть таблицы для столбцов Код предмета, Таб. № препод., Вид занятия аналогично п. 4 (в появившемся окне установите флажок столбцы).
6. Закройте и откройте созданные структурные части таблицы, нажимая на кнопки Минус или Плюс.
7. Отмените структурирование командой Данные —› Группа и структура —› Разгруппировать.

Задание 13. Автоструктурирование таблицы и введение дополнительного иерархического уровня структуры ручным способом:

1. Откройте книгу с именем Spisok, вставьте и назовите новый рабочий лист.
2. Создайте таблицу расчета заработной платы (табл. 5), в которой:
- в столбцы Фамилия, Зарплата, Надбавка, Премия надо ввести константы;
- в строке Итого подсчитываются суммы по каждому столбцу;
- в остальные столбцы надо ввести формулы:

• Подоходный налог = 0,12*Зар.плата
  
• Пенсионный фонд = 0,01*Зар.плата
  
• Общий налог = Подоходный налог + Пенсионный фонд
  
• Итого доплат = Надбавка + Премия
  
• Сумма к выдаче = Зар.плата - Общий налог + Итого доплат

Таблица 5
Пример исходной таблицы для автоструктурирования

3. Создайте автоструктуру таблицы расчета заработной платы.
4. Ознакомьтесь с разными видами таблиц, нажимая на кнопки иерархических уровней и кнопки со знаками плюс и минус.
5. Введите в структурированную таблицу дополнительный иерархический уровень по строкам.

Технология работы

1. Откройте книгу с именем Spisok, вставьте новый рабочий лист и назовите его - Зар.плата.
2. Создайте таблицу согласно табл. 5. Введите формулы в ячейки в соответствии с п. 2 задания, используя метод автозаполнения. Проведите сортировку в списке по фамилиям.
3. Создайте автоструктуру таблицы расчета заработной платы: установите курсор в любую ячейку области данных и выполните команду Данные —› Группа и структура —› Создать структуру.
4. Ознакомьтесь с разными видами таблиц, нажимая на кнопки иерархических уровней и на кнопки со знаками плюс и минус.
5. Введите в структурированную таблицу дополнительный иерархический уровень по строкам, разделив весь список фамилий на группы по две фамилии. Для этого:

• вставьте пустую строку после первых двух фамилий: выделите третью строку и в контекстном меню выберите команду Добавить ячейки;
  
• аналогично вставьте пустую строку перед строкой Итого;
  
• выделите строки с первыми двумя фамилиями, вызовите контекстное меню и выполните команду Данные —› Группа и структура —› Группировать;
  
• выделите строки с остальными фамилиями, вызовите контекстное меню и выполните команду Данные —› Группа и структура —› Группировать.

Задание 14. Структурирование таблицы с автоматическим подведением итогов по группам таблицы, представленной в табл. 4:

1. Откройте книгу с таблицей, отображенной в табл. 4, назовите новый лист Итоги и скопируйте на него исходную базу данных.
2. Отсортируйте записи списка по номеру группы, коду предмета, виду занятий.
3. Создайте 1-й уровень итогов - средний балл по каждой учебной группе.
4. Создайте 2-й уровень итогов - средний балл по каждому предмету для каждой учебной группы.
5. Создайте 3-й уровень итогов - средний балл по каждому виду занятий для каждого предмета по всем учебным группам.
6. Просмотрите элементы структуры, закройте и откройте иерархические уровни.

Технология работы

1. Откройте книгу Spisok, назовите новый лист Итоги и скопируйте на него исходную базу данных.
2. Отсортируйте список записей с помощью команды Данные —› Сортировка, выбрав в старшем ключе номер группы, в промежуточном - код предмета, в младшем - вид занятий. Установите флажок Идентифицировать поля по подписям.
3. Создайте 1-й уровень итогов - средний балл по каждой учебной группе:

• установите курсор в произвольную ячейку списка и выполните команду Данные —› Итоги;
  
• в диалоговом окне Промежуточные итоги укажите:

4. Аналогично п. 3 создайте 2-й уровень итогов - средний балл по каждому предмету для каждой учебной группы.
5. Аналогично п. 3 создайте 3-й уровень итогов - средний балл по каждому виду занятий для каждого предмета по всем учебным группам.
6. Просмотрите элементы структуры, закройте и откройте иерархические уровни, используя кнопки с минусом и плюсом.

Задание 15. Для таблицы 4 постройте следующие виды сводных таблиц:

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

Технология работы

1. Откройте книгу Spisok, переименуйте новый лист на Итоги и скопируйте на него исходную базу данных.
2. Создайте сводную таблицу с помощью Мастера сводных таблиц по шагам (команда Данные —› Сводная таблица):

этап 1 (выбор источника данных ) - щелкните по кнопке <в списке или базе данных Excel> и по кнопке <Далее>;
этап 2 - отображение в строке Диапазон блока ячеек списка (базы данных). Если диапазон указан неверно, то его надо стереть и указать нужный блок ячеек;
этап 3 - построение макета сводной таблицы для п. 1 задания. Технология его построения состоит в следующем:

• перетащите элемент с именем поля, находящегося в правой стороне макета, в одну из областей: № группы - в Страницу; № зач. кн. -
  в Строку, Таб. № преп. и Вид занятия - в Столбец, Оценку - в Данные,
  
• в области Данные два раза щелкните левой кнопкой мыши и в диалоговом окне Вычисление поля сводной таблицы выберите соответствующую операцию над значением поля;
  

этап 4 - выбор места расположения: существующий лист.

3. Выполните автоформатирование полученной сводной таблицы (команда Формат —› Автоформат).
4. Внесите изменения в исходные данные и выполните команду Данные —› Обновить данные.
5. Аналогично постройте сводную таблицу для п. 2 задания.

Задание 16. Консолидация данных по расположению и по категориям:

1. Откройте книгу Spisok, вставьте два листа и переименуйте их, присвоив им имена Консол.распол. и Консол.катег.
2. Создайте на листе Консол.распол. таблицу расчета заработной платы за январь (табл. 5).
3. Скопируйте созданную таблицу на тот же лист и измените в ней данные. Эта таблица будет отражать уровень заработной платы за февраль (табл. 5).
4. Выполните консолидацию данных по расположению.
5. Скопируйте обе таблицы с листа Консол.распол. на лист Консол.катег. и измените вторую таблицу в соответствии с табл. 6.
6. Выполните консолидацию данных по категориям.

Таблица 5

Таблица 6
Исходные таблицы для консолидации данных по категориям
(первая таблица не изменяется)

Технология работы

1. Выполните п. 1 задания.
2. Выполните п. 2 задания.
3. Выполните п. 3 задания.
4. Выполните консолидацию данных по расположению:

• установите курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку A11;
  
• выполните команду Данные —› Консолидация;
  
• в диалоговом окне Консолидация выберите из списка функцию Сумма и установите флажки подписи верхней строки, значения левого столбца;
  
• установите курсор в окне Ссылка, выделите блок ячеек A2:D7 и нажмите кнопку Добавить;
• установите курсор в окне Ссылка, выделите блок ячеек F2:I7 и нажмите кнопку Добавить;
  
• нажмите кнопку ОК;
  

5. Выполните п. 5 задания.
6. Аналогично п. 4 выполните консолидацию данных по категориям.

ЛАБОРАТОРНЫЕ РАБОТЫ

Лабораторная работа 1. Создание и заполнение таблицы постоянными данными и формулами (2 часа)

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

1. Ознакомьтесь с теоретическими сведениями (п. 3.1).
2. Выполните практическое задание 1.
3. Выполните практическое задание 2.
4. Выполните практическое задание 3.
5. Оформите отчет, в котором должны быть отражены следующие технологии по созданию и заполнение таблицы постоянными данными и формулами: ввод текстовых и числовых данных в электронные таблицы; ввод и вычисление формул; копирование формул методом автозаполнения; использование относительных и абсолютных ссылок.

Лабораторная работа 2. Построение, редактирование и форматирование диаграмм (4 часа)

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

1. Ознакомьтесь с теоретическими сведениями (п. 3.2).
2. Выполните практическое задание 4.
3. Выполните практическое задание 5.
4. Выполните практическое задание 6.
5. Выполните практическое задание 7.
6. Оформите отчет, в котором должны быть отражены технологии по построению, редактированию и форматированию диаграмм, а также построению трендов.

Лабораторная работа 3. Сортировка данных (2 часа)

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

1. Ознакомьтесь с теоретическими сведениями (п. 3.3).
2. Выполните практическое задание 8.
3. Оформите отчет, в котором должны быть отражены основные технологии по сортировке данных.

Лабораторная работа 4. Фильтрация (выборка) данных (2 часа)

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

1. Ознакомьтесь с теоретическими сведениями (п. 3.4).
2. Выполните практическое задание 9.
3. Выполните практическое задание 10.
4. Выполните практическое задание 11.
5. Оформите отчет, в котором должны быть отражены технологии выборки данных с использованием Автофильтра, Расширенного фильтра, Критерия сравнения, Вычисляемого критерия, Формы.

Лабораторная работа 5. Структурирование таблиц (2 часа)

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

1. Ознакомьтесь с теоретическими сведениями (п. 3.5).
2. Выполните практическое задание 12.
3. Выполните практическое задание 13.
4. Выполните практическое задание 14.
5. Оформите отчет, в котором должны быть отражены следующие технологии по структурированию таблиц: структурирование таблицы ручным способом; автоструктурирование таблицы и введение дополнительного иерархического уровня структуры ручным способом; структурирование таблицы с автоматическим подведением итогов по группам таблицы.

Лабораторная работа 6. Создание сводных таблиц (2 часа)

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

1. Ознакомьтесь с теоретическими сведениями (п. 3.6).
2. Выполните практическое задание 15.
3. Оформите отчет, в котором должны быть отражены основные технологии по созданию сводных таблиц.

Лабораторная работа 7. Консолидация данных (2 часа)

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

1. Ознакомьтесь с теоретическими сведениями (п. 3.7).
2. Выполните практическое задание 16.
3. Оформите отчет, в котором должны быть отражены технологии консолидации данных по расположению и по категориям.

САМОСТОЯТЕЛЬНАЯ РАБОТА

Задание 1. Обработка данных

1. Запустите программу Excel (Пуск —› Программы —› Microsoft Excel).
2. Создайте новую рабочую книгу (кнопка Создать на стандартной панели инструментов).
3. Дважды щелкните на ярлычке текущего рабочего листа и дайте этому рабочему листу имя Данные.
4. Дайте команду Файл —› Сохранить как и сохраните рабочую книгу под именем book.xls.
5. Сделайте текущей ячейку A1 и введите в нее заголовок Результаты измерений.
6. Введите произвольные числа в последовательные ячейки столбца A, начиная с ячейки A2.
7. Введите в ячейку B1 строку Удвоенное значение.
8. Введите в ячейку C1 строку Квадрат значения.
9. Введите в ячейку D1 строку Квадрат следующего числа.
10. Введите в ячейку B2 формулу = 2*A2.
11. Введите в ячейку С2 формулу = A2*A2.
12. Введите в ячейку D2 формулу = B2 + C2 + 1.
13. Выделите протягиванием ячейки B2, C2 и D2.
14. Наведите указатель мыши на маркер заполнения в правом нижнем углу рамки, охватывающей выделенный диапазон. Нажмите левую кнопку мыши и перетащите этот маркер, чтобы рамка охватила столько строк в столбцах B, C и D, сколько имеется чисел в столбце A.
15. Убедитесь, что формулы автоматически модифицируются так, чтобы работать со значением ячейки в столбце A текущей строки.
16. Измените одно из значений в столбце A и убедитесь, что соответствующие значения в столбцах B, C и D в этой же строке были автоматически пересчитаны.
17. Введите в ячейку E1 строку Масштабный множитель.
18. Введите в ячейку E2 число 5.
19. Введите в ячейку F1 строку Масштабирование.
20. Введите в ячейку F2 формулу = A2*E2.
21. Используйте метод автозаполнения, чтобы скопировать эту формулу в ячейки столбца F, соответствующие заполненным ячейкам столбца A.
22. Убедитесь, что результат масштабирования оказался неверным. Это связано с тем, что адрес E2 в формуле задан относительной ссылкой.
23. Щелкните на ячейке F2, затем в строке формул. Установите текстовый курсор на ссылку E2 и нажмите клавишу F4. Убедитесь, что формула теперь выглядит как =A2*$E$2, и нажмите клавишу Enter.
24. Повторите заполнение столбца F формулой из ячейки F2.
25. Убедитесь, что благодаря использованию абсолютной адресации значения ячеек столбца F теперь вычисляются правильно. Сохраните рабочую книгу books.xls.

Задание 2. Применение итоговых функций

1. Запустите программу Excel и откройте рабочую книгу book.xls, созданную ранее.
2. Выберите рабочий лист Данные.
3. Сделайте текущей первую свободную ячейку в столбце A.
4. Щелкните на кнопке Автосумма на стандартной панели инструментов.
5. Убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек для суммирования. Нажмите клавишу Enter.
6. Сделайте текущей следующую свободную ячейку в столбце A.
7. Щелкните на кнопке Вставка функции на стандартной панели инструментов.
8. В списке Категория выберите пункт Статистические.
9. В списке Функция выберите функцию СРЗНАЧ и щелкните на кнопке ОК.
10. Переместите методом перетаскивания палитру формул, если она заслоняет нужные ячейки. Обратите внимание, что автоматически выбранный диапазон включает в себя все ячейки с числовым содержанием, в том числе и ту, которая содержит сумму. Выделите правильный диапазон методом протягивания и нажмите клавишу Enter.
11. Используя порядок действий, описанный в пп.6-10, вычислите минимальное число в выбранном наборе (функция МИН), максимальное число (МАКС), количество элементов в наборе (СЧЕТ).
12. Сохраните рабочую книгу book.xls.

Задание 3. Подготовка и форматирование прайс-листа

1. Запустите программу Excel и откройте рабочую книгу book.xls.
2. Выберите щелчком на ярлыке неиспользованный рабочий лист или создайте новый, переименовав его на Прейскурант.
3. В ячейку A1 введите текст Прейскурант и нажмите клавишу Enter.
4. В ячейку A2 введите текст Курс пересчета: и нажмите клавишу Enter. В ячейку B2 введите текст 1 у. е. = и нажмите клавишу Enter.
В ячейку C2 введите текущий курс пересчета и нажмите клавишу Enter.
5. В ячейку A3 введите текст Наименование товара и нажмите клавишу Enter. В ячейку B3 введите текст Цена (у. е.) и нажмите клавишу Enter. В ячейку С3 введите текст Цена (руб.) и нажмите клавишу Enter.
6. В последующие ячейки столбца A введите названия товаров, включенных в прейскурант.
7. В соответствующие ячейки столбца B введите цены товаров в условных единицах.
8. В ячейку C4 введите формулу: =B4*$C$2, которая используется для пересчета цены из условных единиц в рубли.
9. Методом автозаполнения скопируйте формулы во все ячейки столбца С, которым соответствуют заполненные ячейки столбцов A и B.
10. Измените курс пересчета в ячейке С2. Обратите внимание, что цены в рублях изменяются автоматически.
11. Выделите методом протягивания диапазон A1:C1 и дайте команду Формат —› Ячейки. На вкладке Выравнивание задайте выравнивание по горизонтали По центру и установите флажок Объединение ячеек.
12. На вкладке Шрифт задайте размер шрифта в 14 пунктов и в списке Начертание выберите вариант Полужирный. Щелкните на кнопке ОК.
13. Щелкните правой кнопкой мыши на ячейке B2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали По правому краю и щелкните на кнопке ОК.
14. Щелкните правой кнопкой мыши на ячейке C2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали По левому краю и щелкните на кнопке ОК.
15. Выделите методом протягивания диапазон B2:C2. С помощью кнопки Границы задайте для этих ячеек внешнюю рамку.
16. Дважды щелкните на границе между заголовками столбцов A и B, B и C, C и D. Обратите внимание, как при этом изменяется ширина столбцов A, B и C.
17. Щелкните на кнопке Предварительный просмотр на стандартной панели инструментов, чтобы увидеть, как документ будет выглядеть при печати.
18. Сохраните рабочую книгу book.xls.

Задание 4. Построение экспериментального графика

1. Запустите программу Excel и откройте рабочую книгу book.xls, созданную ранее.
2. Выберите щелчком на ярлычке неиспользуемый рабочий лист (или создайте новый) и переименуйте его на Обработка эксперимента.
3. В столбец A, начиная с ячейки A1, введите произвольный набор значений независимой переменной.
4. В столбец B, начиная с ячейки B1, введите произвольный набор функции.
5. Методом протягивания выделите все заполненные ячейки столбцов A и B.
6. Щелкните на значке Мастер диаграмм на стандартной панели инструментов.
7. В списке Тип выберите пункт Точечная (для отображения графика, заданного парами значений). В палитре Вид выберите средний пункт в первом столбце (маркеры, соединенные гладкими кривыми). Щелкните на кнопке Далее.
8. Так как диапазон ячеек был выбран заранее, Мастер диаграмм автоматически определяет расположение рядов данных. На вкладке Ряд в поле Имя укажите: Результаты измерений. Щелкните на кнопке Далее.
9. Выберите вкладку Заголовки. Убедитесь, что заданное название ряда данных автоматически использовано как заголовок диаграммы. Замените его, введя в поле Название диаграммы заголовок Экспериментальные точки. Щелкните на кнопке Далее.
10. Установите переключатель Отдельном. По желанию, задайте произвольное имя добавляемого рабочего листа. Щелкните на кнопке Готово.
11. Убедитесь, что диаграмма построена и внедрена в новый рабочий лист. Рассмотрите ее и щелкните на построенной кривой, чтобы выделить ряд данных.
12. Дайте команду Формат ® Выделенный ряд. Откройте вкладку Вид.
13. На панели Линия откройте палитру Цвет и выберите красный цвет. В списке Тип линии выберите пунктир.
14. На панели Маркер выберите в списке Тип маркера треугольный маркер. В палитрах Цвет и Фон выберите зеленый цвет.
15. Щелкните на кнопке ОК, снимите выделение с ряда данных и посмотрите, как изменился вид графика.
16. Сохраните рабочую книгу.

Задание 5. Анализ данных с использованием метода наименьших квадратов
Для заданного набора пар значений независимой переменной и функции определить наилучшее линейное приближение в виде прямой с уравнением y = aЧx + b и показательное приближение в виде линии с уравнением y = bЧax.

1. Запустите программу Excel и откройте рабочую книгу book.xls, созданную ранее.
2. Выберите рабочий лист Обработка эксперимента.
3. Сделайте ячейку C1 текущей и щелкните на кнопке Изменить формулу в строке формул. Раскройте список на левом краю строки формул и выберите пункт Другие функции.
4. В окне мастера функций выберите категорию Ссылки и массивы и функцию ИНДЕКС. В новом диалоговом окне выберите первый вариант набора параметров.
5. Установите текстовый курсор в первое поле для ввода параметров в палитре формул и снова выберите пункт Другие функции в раскрывающемся списке в строке формул.
6. С помощью мастера функций выберите функцию ЛИНЕЙН категории Статистические.
7. В качестве первого параметра функции ЛИНЕЙН выберите диапазон, содержащий значения функции (столбец B).
8. В качестве второго параметра функции ЛИНЕЙН выберите диапазон, содержащий значения независимой переменной.
9. Переместите текстовый курсор в строке формул, чтобы он стоял на имени функции ИНДЕКС. В качестве второго параметра функции ИНДЕКС задайте число 1. Щелкните на кнопке ОК на палитре формул.
Пояснение: функция ЛИНЕЙН возвращает коэффициенты уравнения прямой в виде массива из двух элементов, а функция ИНДЕКС выбирает нужный элемент.
10. Сделайте текущей ячейку D1. Повторите операции, описанные в пп. 3-9, чтобы в итоге в этой ячейке появилась формула: ИНДЕКС (ЛИНЕЙН(B1:B5;A1:A5);2). Ее можно ввести и вручную (посимвольно). Теперь в ячейках C1 и D1 вычислены соответственно коэффициенты a и b уравнения наилучшей прямой.
11. Сделайте текущей ячейку C2. Повторите операции, описанные в пп.
3-9, или введите вручную формулу: =ИНДЕКС(ЛГРФПРИБЛ(B1:B5;A1:A5);1).
12. Сделайте текущей ячейку D2. Повторите операции, описанные в пп.
3-9, или введите вручную формулу: =ИНДЕКС(ЛГРФПРИБЛ(B1:B5;A1:A5);2).
Теперь ячейки C2 и D2 содержат соответственно коэффициенты
a и b уравнения наилучшего показательного приближения.
13. Для построения наилучшей прямой другим способом дайте команду Сервис ® Анализ данных.
14. В списке Инструменты анализа выберите пункт Регрессия, после чего щелкните на кнопке ОК.
15. В поле Входной интервал Y укажите методом протягивания диапазон, содержащий значения функции (столбец B).
16. В поле Входной интервал X укажите методом протягивания диапазон, содержащий значения аргумента (столбец A).
17. Установите переключатель Новый рабочий лист, для которого задайте имя Результат расчета.
18. Щелкните на кнопке ОК и по окончании расчета откройте рабочий лист Результат расчета. Убедитесь, что вычисленные коэффициенты (см. ячейки B17 и B18) совпали с полученными первым способом.
19. Сохраните рабочую книгу book.xls.

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

1. Запустите программу Excel и откройте рабочую книгу book.xls.
2. Создайте рабочий лист Обработка эксперимента.
3. Для табулирования формулы сделайте текущей ячейку С3 и занесите в нее значение 0. Эта ячейка будет использоваться как ячейка ввода, на которую будут ссылаться формулы.
4. Скопируйте значения столбца А в столбец F, начиная со второй строки.
5. В ячейку G1 введите формулу =C3*$C$1+$D$1. Здесь C3 - ячейка ввода, а в качестве других ссылок используются вычисленные методом наименьших квадратов коэффициенты уравнения прямой.
6. В ячейку H1 введите формулу =$D$2*$C$2^C3 для вычисления значения показательной функции.
7. Выделите прямоугольный диапазон, включающий столбцы F, G
и H и строки от строки 1, содержащей формулы, до последней строки
с данными в столбце F.
8. Дайте команду Данные ® Таблица подстановки. Выберите поле Подставить значения по строкам и щелкните на ячейке ввода С3.
9. Щелкните на кнопке ОК, чтобы заполнить пустые ячейки в столбцах G и H выделенного диапазона значениями формул в ячейках первой строки для значений независимой переменной, выбранных из столбца F.
10. Щелкните на кнопке Мастер диаграмм на стандартной панели инструментов, выберите Тип График и щелкните на кнопке Далее.
11. В пункте Диапазон данных в строках укажите диапазон ячеек с данными в столбце F, а в пункте столбцы - диапазон ячеек в столбце G. Щелкните на кнопке Далее.
12. В поле Название диаграммы выберите Наилучшая прямая. В двух следующих полях можно дать названия осям координат. Щелкните на кнопке Далее.
13. Поместите диаграмму на отдельном листе, дав ему имя График 1. Щелкните на кнопке Готово.
14. Повторите пп. 10-13 для построения Показательной функции. Поместите диаграмму на отдельном листе, дав ему имя График 2.
15. Сохраните рабочую книгу book.xls.

Задание 7. Решение уравнений средствами программы Excel
Найти решение уравнения x3 - 3x2 + x = -1.

1. Запустите программу Excel и откройте рабочую книгу book.xls.
2. Создайте новый рабочий лист, дав ему имя Уравнение.
3. Занесите в ячейку А1 значение 0 (начальное значение).
4. Занесите в ячейку B1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку A1. Для заданного уравнения формула имеет вид: =A1^3-3*A1^2+A1.
5. Дайте команду Сервис —› Подбор параметра.
6. В поле Установить в ячейке укажите B1, в поле Значение задайте -1, в поле Изменяя значение ячейки укажите A1.
7. Щелкните на кнопке ОК и посмотрите на результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Щелкните на кнопке ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции.
8. Повторите расчет, задавая в ячейке A1 другие начальные значения, например 0,5 или 2. Совпали ли результаты вычислений? Чем можно объяснить различия?
9. Сохраните рабочую книгу book.xls.