ПРИМЕНЕНИЕ ПАКЕТА MICROSOFT EXCEL 2009-2010

Поможем в ✍️ написании учебной работы

Имя

Поможем с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой

Нажимая кнопку "Продолжить", я принимаю политику конфиденциальности

ПРИМЕНЕНИЕ ПАКЕТА MICROSOFT EXCEL 2009-2010

Учебно-методическое пособие

Рецензент:

канд. техн. наук., зав. кафедрой Бизнес-информатики и информационных технологий ФУ при правительстве РФ С.В. Полпудников

Утверждено методической комиссией КФ МГТУ им. Н.Э. Баумана

(протокол )

Гагарин Ю.Е., Ткаченко А.Л.

Применение пакета microsoft excel 2009-2010: учебно-методическое пособие. М.: Издательство МГТУ им. Н.Э. Баумана,

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

Учебно-методическое пособие рекомендуется студентам специальности «Программное обеспечение вычислительной техники и автоматизированных систем», изучающих курс «Информатика», и студентам других специальностей по курсу «Информатика и программирование».

ВВЕДЕНИЕ

Электронные таблицы (ЭТ) предназначены прежде всего для выполнение вычислений. Издавна многие расчеты выполняются в табличной форме, особенно в области делопроизводства: многочисленные расчетные ведомости, табуляграммы, сметы расходов и т. п. Кроме того, решение численными методами целого ряда математических задач удобно выполнять в табличной форме. Электронные таблицы представляют собой удобный инструмент для автоматизации таких вычислений. Использование математических формул в ЭТ позволяет представить взаимосвязь между различными параметрами некоторой реальной системы. Основное свойство ЭТ - мгновенный пересчет формул при изменении значений входящих в них операндов. Благодаря этому свойству, таблица представляет собой удобный инструмент для организации численного эксперимента: подбор параметров, прогноз поведения моделируемой системы, анализ зависимостей, планирование.

Дополнительные удобства для моделирования дает возможность графического представления данных (диаграммы); Использование электронной таблицы в качестве базы данных. Конечно, по сравнению с системой управления базами данных (СУБД) электронные таблицы имеют меньшие возможности в этой области. Однако некоторые операции манипулирования данными, свойственные реляционным СУБД в них реализованы. Это поиск информации по заданным условиям и сортировка информации.

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

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

Электронные таблицы просты в обращении, быстро осваиваются непрофессиональными пользователями компьютера и во много раз упрощают и ускоряют работу экономистов, ученых, бухгалтеров [1].

ИЗ ИСТОРИИ

Идею электронных таблиц впервые сформулировал Richard Mattesich, опубликовав в 1961 г. исследование под названием «Budgeting Models and System Simulation». Концепцию дополнили в 1970 г. Пардо и Ландау, подавшие заявку на соответствующий патент (U.S. Patent 4,398,249 (англ.)).

Общепризнанным родоначальником электронных таблиц как отдельного класса программного обеспечения является Дэн Бриклин, совместно с Бобом Фрэнкстоном разработавший легендарную программу VisiCalc в 1979 г. Этот табличный редактор для компьютера Apple II стал приложением, превратившим персональный компьютер из экзотической игрушки в массовый инструмент для бизнеса.

Впоследствии на рынке появились многочисленные продукты этого класса – SuperCalc, Microsoft MultiPlan, Quattro Pro, Lotus 1-2-3, Microsoft Excel, OpenOffice.org Calc, таблицы AppleWorks и Gnumeric, минималистический Spread32 [1].

ВОПРОС 2. ИНТЕРФЕЙС И ОСНОВНЫЕ ПОНЯТИЯ ТАБЛИЧНОГО РЕДАКТОРА EXCEL

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

К основным возможностям табличного редактора Excel относятся:

- быстрое построение, корректировка, сохранение таблиц;

- использование стандартных функций для проведения расчетов в таблицах;

- защита табличных данных;

- построение по табличным данным двух и трехмерных графиков и диаграмм, содержит разнообразные инструменты для редактирования графиков и диаграмм, включая средства для создания смешанных двухмерных графиков;

- поддержка OLE – технологии и технологии drag-and-drop;

- работа со связанными таблицами;

- работа с таблицей как с базой данных;

- одновременная работа с несколькими книгами и др.

Кроме того, Excel обеспечивает:

- быстрое построение таблиц любой формы одноразового и многоразового пользования и сохранение на магнитном носителе в виде отдельного файла с последующим чтением;

- возможность обработки таких типов данных, как числа, даты, формулы;

- возможность корректировки уже созданной таблицы (перемещение строк и столбцов, их копирование, удаление и т.д.);

- возможность выбора цветового оформления таблицы, а также выбора различных шрифтов и стилей, включая автоформатирование;

- наличие механизма мастеров, которые позволяют автоматизировать выполнение операций (например, мастер диаграмм или мастер функций);

- автоматизированную обработку таблиц с помощью макрокоманд, а также модулей на встроенном языке программирования Visual Basic for Application;

- поддержку работы в сети.

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

Основными понятиями табличного редактора Excel являются:

- книга – это файл, используемый для обработки и хранения данных, состоящий из отдельных листов;

- лист – разделен на строки и столбцы и служит основой для выполнения вычислений. Рабочий лист состоит из 256 столбцов и 65536 строк Столбцы обозначаются буквами латинского алфавита (в обычном режиме) от "A" до "Z", затем идет "AA-AZ", "BA-BZ" и т.п. Строки обозначаются обычными арабскими числами;

- ячейка – область, определяемая пересечением столбца и строки электронной таблицы;

- текущая (активная) ячейка – ячейка ЭТ, в которой в данный момент находится курсор;

- адрес ячейки определяется названием (номером) столбца и номером строки;

- блок ячеек (диапазон) – группа смежных ячеек, определяемая адресом верхней левой и нижней правой ячеек в прямоугольнике, образуемом блоком. Например, D4:F13;

- ссылка – ссылка указывает на ячейку или диапазон ячеек листа, которые требуется использовать в формуле;

- абсолютная ссылка – это ссылка, не изменяющаяся при копировании формулы в другую ячейку;

- относительная ссылка – это ссылка, автоматически изменяющаяся при копировании в соответствии с положением формулы.

- формула – это конструкция, начинающаяся со знака “=”, состоящая из математических операторов, значений, ссылок на ячейки и имен функций, при этом результатом выполнения формулы является некоторое новое значение.

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

Рисунок 1 – Структура интерфейса

Лента состоит из объектов трех типов: вкладок, групп и команд [2].

Лента содержит три основных типа объектов (рис. 1):

1) Вкладки. Вкладок всего семь, и они находятся в верхней части окна. Каждая из них предоставляет возможность выполнять основные задачи, предусмотренные в Excel.

2) Группы. Каждая вкладка состоит из групп, объединяющих связанные друг с другом элементы.

3) Команды. Команда - это кнопка, поле для ввода данных или меню.

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

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

Если в правом нижнем углу группы находится кнопка со стрелкой (которая называется кнопка вызова диалогового окна), значит, для этой группы доступны дополнительные параметры. Щелкните эту стрелку, чтобы вывести диалоговое окно или область задач (рис. 2).

Рисунок 2 – Отображение дополнительных параметров.

Например, на вкладке [Главная] в группе [Шрифт] расположены все часто используемые команды для изменения шрифтов: команды для изменения шрифта и его размера, а также для применения полужирного шрифта, курсива или подчеркивания.

Если нужны другие возможности, например верхний индекс, нажмите кнопку со стрелкой справа от пункта [Шрифт], и появится диалоговое окно [Формат ячеек], содержащее верхний индекс и другие параметры, относящиеся к шрифтам [2].

Ввод константы

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

Зафиксировать ввод данных в ячейку можно следующими способами:

- нажатием клавиши [Enter], после чего курсор переместится в следующую ячейку. Направление перехода к другой ячейке после ввода можно задать нажав кнопку Microsoft Office в левом верхнем углу окна. Чтобы получить доступ к параметрам, нажмите кнопку Параметры Excel в нижней части меню. Выберите вкладку Дополнительно и в Параметрах правки в ниспадающем меню Направление выберите направление перехода к другой ячейке.

- с помощью клавиш управления курсором [], [], [¯], [®] клавиатуры;

- щелком левой клавишей мыши на другой ячейке рабочего листа.

Управление отображением текстовых значений в ячейках

Если длина вводимого текста превышает ширину ячейки, то он будет выступать за правый край ячейки, как показано на рис. 3, и располагаться поверх соседней ячейки, пока последняя свободна. Но текст при этом хранится в одной ячейке (в данном примере – в ячейке А1).

Рисунок 3 – Отображение данных поверх соседних ячеек

Чтобы отобразить длинный текст в одной ячейке можно увеличить ширину столбца, в котором находится эта ячейка. Для этого можно воспользоваться одним из способов: на ленте выбрать вкладку [Главная], в ней найти группу []Ячейки, выполнить команду [Формат] и в раскрывшемся списке выбрать [Автоподбор ширины столбца] (рисунок 4); то же сделает двойной щелчок мыши на правой границе заголовка столбца; или вручную, переместив мышью правую границу заголовка столбца, когда курсор приобрел вид двунаправленной стрелки.

Примечание. Для изменения высоты строк можно воспользоваться следующими методами: на ленте выбрать вкладку [Главная], в ней найти группу [Ячейки], выполнить команду [Формат] и в раскрывшемся списке выбрать [Автоподбор высоты строки]; или увеличить высоту строки вручную, аналогично изменению ширины столбца.

Рисунок 4 – Увеличение ячейки по размеру данных

Чтобы разместить текст в несколько строк в одной ячейке нужно выделить ячейку, на ленте выбрать вкладку [Главная], в ней найти группу [Ячейки], выполнить команду [Формат] и в раскрывшемся списке выбрать [Формат ячеек], в раскрывшемся диалоговом окне выбрать вкладку [Выравнивание], найти раздел [Отображение] и поставить флажок [Переносить по словам], затем нажать ОК. На рисунке 5 показано, как будет выглядеть ячейка после такого преобразования. Также на вкладке [Выравнивание] окна [Формат ячеек] можно из открывающихся списков выбрать способ выравнивания содержимого ячеек по горизонтали и по вертикали и ориентацию текста в ячейке.

Рисунок 5 – Размещение данных ячейки в несколько строк

Управление отображением текстовых значений в ячейках

Значения, которые хранятся в ячейках и появляются в строке формул, называются хранимыми значениями. Если в ячейку вводится длинное числовое значение, то Excel использует экспоненциальное представление числа. При этом точность значения выбирается такой, чтобы число можно было отобразить в ячейке. Например, если ввести в ячейку стандартной ширины число 1238567890123, то оно отобразится в виде 1,24Е+12.

Значения, которые появляются в ячейке в соответствии с формулами, называются выводимыми или отображаемыми значениями. Количество выводимых цифр зависит от ширины столбца. Если ширина столбца недостаточна, то Excel отображает строку символов #. В этом случае надо просто увеличить ширину столбца.

Редактирование данных

Для удаления содержимого одной или нескольких ячеек необходимо выделить ячейку (или блок ячеек) и на ленте выбрать вкладку [Главная], зайти в группу [Редактирование] и выбрать команду [Очистить] и из раскрывшегося списка выбрать команду [Очистить все]. Возможно также нажать [Delete] на клавиатуре.

Для исправления ошибок, можно выделить ячейку, и в окне ввода корректировать отдельные символы, перемещая точку вставки.

Рассмотрим порядок создания и сохранения табл. 1 (исходные данные пока вводить не будем).

1. Загрузите Excel командой Пуск►Все Программы►Microsoft Office►Microsoft Office Excel.

2. Переименуйте «Лист1». Для этого правой клавишей мыши (КЛ_П) щелкните по вкладке «Лист1», из раскрывшегося списка необходимо выбрать Переименовать и измените имя листа «Лист1» на «Товары».

3. Введите заголовок таблицы «Учет движения материалов на складах» в ячейку с адресом А1. Для расположения заголовка по центру таблицы (рисунке 6) выделите диапазон ячеек А1:F1 (т.к. таблица будет содержать 6 столбцов от А до F) и на ленте необходимо выбрать вкладку [Главная], в ней найти группу [Ячейки], выполнить команду [Формат] и в раскрывшемся списке выбрать [Формат ячеек], в раскрывшемся диалоговом окне выбрать вкладку [Выравнивание], в поле по горизонтали выбрать из списка – по центру выделения, в поле по вертикали – по центру и нажать [ОК].

Рисунок 6 – Экранная копия таблицы 1 без исходных данных

4. Введите названия столбцов таблицы. Для этого:

Объедините ячейки А2 и А3, для чего выделите их, на ленте необходимо выбрать вкладку [Главная], в ней найти группу [Ячейки], выполнить команду [Формат] и в раскрывшемся списке выбрать вкладку [Формат ячеек], в раскрывшемся диалоговом окне выбрать вкладку [Выравнивание] и в разделе [Отображение] поставить флажки [Объединить ячейки] и [Переносить по словам]. В разделе [Выравнивание] выбрать в списках [По вертикали] и [По горизонтали] – [По центру]. Нажмите [ОК].

Затем в объединенные ячейки введите название «№ склада».

Аналогичные действия выполните для ввода названия второго столбца таблицы – «Код материала», объединив ячейки В2 и В3.

Введите в ячейку С2 заголовок «Количество», шт. и расположите его по центру четырех столбцов. Для этого выделите диапазон С2:F2 и на ленте выберите вкладку [Главная], в ней найдите группу [Ячейки], выполните команду [Формат] и в раскрывшемся списке выберите вкладку [Формат ячеек], в раскрывшемся диалоговом окне выберите вкладку [Выравнивание] и в разделе [Выравнивание] выберите в списках [По вертикали] и [По горизонтали] – [По центру].

Примечание. Предыдущее действие может быть заменено использованием кнопки [Объединить и поместить в центре] Рисунок 7, которая находится на ленте во вкладке [Главная] в группе [Выравнивание].

Рисунок 7 – Объединение ячеек

В ячейки С3, D3, Е3, F3 введите соответствующие названия столбцов. Для ячеек C3 и F3 выполните щелчок [КЛ_П] на соответствующих ячейках, выберите из списка [Формат ячеек], вкладку [Выравнивание] и в разделе [Выравнивание] выбрать в списках [По вертикали] и [По горизонтали] – [По центру], а также в разделе [Отображение] поставить флажок [Переносить по словам], чтобы текст распределился внутри выделенного диапазона в несколько строк.

В ячейку А9 введите – «ИТОГО».

Выполните шрифтовое оформление ячеек для заголовка и «шапки» таблицы. Выделите ячейки А1:F3 и на ленте на вкладке [Главная] выберите в группе [Шрифт] – Times New Roman, [Начертание] – курсив, [Размер шрифта] – 12 пт.

Для ячеек А4:F9 выберите [Шрифт] Times New Roman, [Начертание] – обычный, [Размер шрифта] – 12 пт.

Для задания обрамления таблицы выделите диапазон ячеек А2:F9 и нажав на поле ячеек [КЛ_П] выберите в диалогом окне [Формат ячеек], перейдите во вкладку [Граница], выберите из списков тип и цвет линии, для указания расположения границ выполните щелчок на кнопках [Внешние] и [Внутренние] и нажмите [ОК].

Аналогично можно воспользоваться кнопкой на ленте во вкладке [Главная], группе [Шрифт] .

Активизируйте «Лист2», переименуйте его на «Цены» и создайте таблицу 2. Вид спроектированной таблицы представлен на рисунке 8. Обратите внимание, что шапка таблицы размещена в одной строке.

Рисунок 8 – Экранная копия таблицы 2 без исходных данных.

Автозаполнение

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

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

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

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

Активизируйте лист Товары.

Введите в ячейку В4 число 101 и нажмите [Enter].

Активизируйте ячейку В4 и на ленте выберите вкладку [Главная], группу [Редактирование], выполните команду [Заполнить] и из раскрывающегося списка выберите [Прогрессия].

В поле [Расположение] установите флажок – [По столбцам], в поле [Тип] – [Арифметическая]. В поле [Шаг] введите – «1», в поле [Предельное значение] – «105». Нажмите [ОК].

Заполните таблицу остальными исходными данными (таблица 1). Таблица примет вид, представленный на рисунке 10.

Рисунок 10 – Таблица 1 с исходными данными и результатами вычислений

Построение диаграммы

Построение диаграмм осуществляется с помощью Мастера диаграмм.

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

1. Выделите на листе Товары диапазон ячеек С4:С8 с исходными данными (область данных диаграммы).

2. На ленте выберите вкладку Вставка, группу Диаграммы, выполните команду Круговая, выберите тип круговая диаграмма.

3. Левой клавишей мышки (КЛ_Л) щелкните по полю диаграммы и на ленте появится дополнительная вкладка Работа с диаграммами - Конструктор, проверьте правильность ориентации выбрав в группе [Данные] команду [Строка/столбец], выбрав [Столбец]. В групп [Данные] выберите команду [Выбрать данные], и в раскрывшемся диалоговом окне [Выбрать источник данных] в окне [Подписи горизонтальной оси (категорий)] нажмите кнопку [Изменить], выберите диапазон В4:В8 и нажмите ОК.

4. На ленте во вкладке выберите вкладку [Работа с диаграммами] – [Макет], в группе [Подписи] выберите команду [Название диаграммы], из раскрывающегося списка выберите [Над диаграммой] и введите «Остаток товаров на начло месяца». Выберите в группе [Подписи] команду [Легенда] и из раскрывающегося списка выберите [Добавить легенду снизу]. Там же выберите команду [Подписи данных] и из раскрывающегося списка выберите [У вершины снаружи].

5. На ленте выберите группу [Конструктор] и в ней группу [Расположение], команду [Переместить диаграмму]. В открывшемся диалоговом окне поставьте флажок [На отдельном листе] и напишите имя листа «Остаток», нажмите ОК.

Полученная диаграмма представлена на рисунке 11.

Рисунок 11 – круговая диаграмма

Редактирование диаграмм

Созданные диаграммы можно корректировать, вплоть до изменения исходных данных и способа их представления. Редактирование диаграмм предполагает изменение типа диаграммы или ориентации данных, изменение визуальных эффектов. Можно также добавлять или удалять отдельные элементы, либо изменять определенные параметры. В Excel предусмотрена также возможность дополнения диаграмм другими рядами данных.

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

Рассмотрим порядок изменения типа построенной диаграммы «Остаток товаров на начало месяца» на тип [Гистограмма].

1. Перейдите на лист «Остаток».

2. Выполните команду [Изменить тип диаграммы] в группе [Тип], вкладки [Конструктор].

3. В раскрывшемся диалоговом окне выберите [Тип] – [Гистограмма], [Вид] – [Гистограмма с группировкой] и нажмите ОК.

4. На ленте выберите вкладку [Макет], группу [Подписи], команду [Легенда] и из раскрывающегося списка выберите [Нет].

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

Рисунок 12 – гистограмма.

Рассмотрим порядок добавления на диаграмму новых рядов данных. Добавим к диаграмме «Остаток товаров на начало месяца» данные «Приход», «Расход» и «Остаток на конец месяца».

1. На листе «Остаток» выделите диаграмму и выполните команду [Добавить данные] в группе [Данные] вкладки [Конструктор]. В раскрывшемся диалоговом окне [Выбор источника данных] нажмите на кнопку [Добавить], в открывшемся окне в поле значения необходимо указать диапазон ячеек D4:D8 на листе «Товары», данными из которых будет дополнена диаграмма, нажмите [ОК]. Проделайте аналогичные действия для столбцов «Расход» и «Остаток на конец месяца».

2. Перейдите на вкладку [Макет] и в группе [Подписи] выберите команду [Подписи данных] и из раскрывающегося списка выберите [У вершины снаружи].

3. Выберите вкладку [Макет], группу [Подписи], команду [Легенды] и из раскрывающегося списка [Добавить легенду снизу].

На данной диаграмме (рисунке 11) основу рядов будут составлять показатели остатков на начало и конец месяца, прихода и расхода по каждому товару. Категориями выступают коды товаров.

Измените названия элементов легенды «Ряд1», «Ряд2» и «Ряд3». Для этого перейдите на вкладку [Конструктор], выберите группу [Выбрать данные], в открывшемся диалоговом окне в поле [Элементы легенды (ряды)] выберите «Ряд1» и нажмите на кнопку [Изменить], в поле [Имя ряда] щелкните [КЛ_Л] на кнопке справа и выберите ячейку С3 на листе «Товары», которая содержит заголовок «Остаток товаров на начало месяца». Проделайте то же самое с элементами «Ряд2» и «Ряд3» задав для них в качестве названия содержимое ячеек Е3 и F3 листа «Товары».

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

Выберите вкладку [Работа с диаграммами] – [Макет], в группе [Подписи] команду [Название диаграммы] – [Над диаграммой]. В поле []Название диаграммы введите «Учет товаров на складах предприятия».

В этой же группе [Подписи] выберите команду [Название осей] – [Название основной горизонтальной оси] – [Название под осью] и введите «Коды товаров», нажмите [Enter]. Выберите [Название основной вертикальной оси] – [Повернутое название] и введите «Количество», нажмите [Enter].

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

Рисунок 13 – Гистограмма с добавленными данными

Рассмотрим алгоритм удаления на диаграмме ряда данных «Остаток товаров на начало месяца».

1. На диаграмме выделите ряд «Остаток товаров на начало месяца», щелкнув на одном из его маркеров.

2. Нажмите клавишу [Delete] на клавиатуре.

Форматирование диаграмм

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

Рассмотрим порядок изменения цвета для рядов данных, снятия линий сетки, установки параметров для элементов диаграммы: шрифта – 12 пт, выравнивание подписей значений для ряда Приход – под углом 90 градусов.

1. Активизируйте лист «Остаток».

2. Выделите на диаграмме ряд данных «Приход» щелчком мыши по любому маркеру ряда на диаграмме.

3. Выберите вкладку [Работа с диаграммами] – [Формат], в группе [Стили фигур] команду [Эффекты для фигур] – [Тень] – [Снаружи] – [Слева снаружи].

4. Выберите вкладку [Работа с диаграммами] – [Формат], в группе [Стили фигур] команду [Заливка фигуры] – выберите любой понравившейся цвет воспользовавшись кнопкой [Заливка] или [Текстура].

5. Самостоятельно измените цвет для остальных рядов.

6. Выделите область диаграммы, одним щелком мыши на поле цифровых подписей рядов, установите размер шрифта – 12 пт выбрав на ленте вкладку [Главная], группу [Шрифт] и поставьте значение 12.

7. Выделите область диаграммы, [КЛ_П] щелкните на цифровой подписи данных ряда и в открывшемся меню выберите [Формат подписи данных], вкладку [Выравнивание], в окне направление текста выберите [Повернуть весь текст на 270⁰].

8. Выберите вкладку [Работа с диаграммами], вкладку [Макет], выберите группу [Оси], команду [Сетка], во вкладках [Горизонтальные линии сетки по основной оси] и [Вертикальные линии сетки по основной оси] и выберите [Нет] (не показывать горизонтальные/вертикальные лини сетки).

Вид полученной диаграммы представлен на рис. 5.12.

Рисунок 14 – Гистограмма псле форматирования

ВОПРОС 5. ЗАЩИТА ДАННЫХ

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

Однако защита не вступает в силу пока не выбрана команда [Защитить лист]. В результате выполнения этой команды будет открыто диалоговое окно [Защитить лист] (рисунок 15). Установленные в нем варианты защиты применяются только к текущему листу.

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

В большинстве случаев нет необходимости в блокировке всех ячеек рабочего листа. Перед защитой листа необходимо выделить те ячейки, которые необходимо оставить незаблокированными, и на ленте во вкладке [Главная], группы [Ячейки] выберите команду [Формат] и в раскрывшемся списке сделайте неактивной команду [Блокировать ячейку]. Тогда при вводе в таблицу данных для перемещения курсора по пустым незащищенным ячейкам можно использовать клавишу [Тab].

Снимается защита командой [Снять защиту листа] на вкладке [Главная], группы [Ячейки], в ниспадающем списке команды [Формат].

Рассмотрим порядок установки защиты на лист «Товары» для блокировки в таблице 1 заголовка, названий столбцов и формул.

1. Активизируйте лист «Товары».

2. Для того, чтобы оставить ячейки А4:Е8 доступными для редактирования после защиты листа, выделите их и выполните команду на ленте во вкладке [Главная], группы [Ячейки] выберите команду [Формат] и в раскрывшемся списке сделайте неактивной команду [Блокировать ячейку].

3. Установите защиту на лист, выбрав на ленте вкладку [Главная], группу [Ячейки], команду [Формат] и в раскрывшемся списке выберите [Защитить лист]. После проверки работы защиты снимите ее командой [Снять защиту листа].

Рисунок 15 – окно Защита листа

Работа с формой

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

Для того чтобы открыть окно формы необходимо добавить кнопку [Форма] на панель быстрого доступа . Для этого:

1. Щелкните стрелку на панели быстрого доступа и выберите элемент [Другие команды].

2. В поле [Выбрать команды] из щелкните элемент [Все команды].

3. В списке выберите кнопку [Форма] и щелкните элемент [Добавить].

Рисунок 17 – Окно формы

Работа с формой предполагает:

1. Перемещение по полям записи мышью или клавишей [Tab];

2. Перемещение по записям с помощью полосы прокрутки и кнопок [Назад] и [Далее];

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

4. Удаление и добавление записей с помощью кнопок [Добавить] и [Удалить]. При добавлении новой записи, она всегда добавляется в конец таблицы.

5. Редактирование значений полей в записях.

6. Поиск данных по критерию с помощью кнопки [Критерии], после нажатия которой в появившемся окне в соответствующих полях задаются критерии. Задание критериев позволяет просматривать через окно [Формы] только те записи, которые удовлетворяют некоторым условиям поиска (критериям).

Перед тем как начать поиск записей по критерию, рекомендуется сделать текущей первую запись базы данных. Просмотр отобранных записей по критерию осуществляется с помощью кнопок [Назад] и [Далее]. При задании критериев поиска можно использовать символы подстановки:

* – для обозначения произвольного количества символов;

? – для обозначения одного символа.

Например, при необходимости выбора всех записей с фамилиями студентов, начинающихся с буквы «К», в качестве критерия поиска следует ввести К*. А при задании критерия К?рсанов, будут выбираться фамилии Кирсанов, Керсанов, Корсанов и т.п.

В критериях при поиске числовых значений можно использовать операторы сравнения: =, <, >, <>, <=, >=.

Чтобы задать несколько критериев поиска, следует указать их в различных полях, тогда они объединяются логическим И. Задание нескольких критериев позволяет сузить область поиска.

Сортировка данных

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

Рисунок 18 – Окно сортировки

Если сортировка ведется по нескольким ключам, то строки с одинаковыми значениями в столбце, указанном в поле Сортировать по, сортируются в порядке, определяемом столбцом, указанным в поле Затем по. Строки с одинаковыми значениями в первых двух столбцах сортируются по столбцу, указанному в поле [В последнюю очередь, по].

Рассмотрим порядок сортировки таблицы «Учет движения материалов на складах» по двум ключам: 1 – номер склада, 2 – код материала.

1. Активизируйте лист «Сортировка».

2. Установите курсор в любую ячейку диапазона A2:F7, который необходимо отсортировать, т.е. диапазона таблицы вместе с шапкой.

3. Выберите команду [Сортировка], которая находится на ленте во вкладке [Данные], в группе [Сортировка и фильтр]. В окне [Сортировка] (Рисунок 18) в раскрывающемся списке [Сортировать по] выберите поле, по которому следует выполнить сортировку в первую очередь – это «№ склада». Далее справа выберите в поле [Сортировка] – [Значение] и в поле [Порядок] – [По возрастанию].

4. В окне [Сортировка] активируйте команду [Добавить уровень]. В поле [Затем по] выберите второй ключ сортировки – поле [Код материала], сортировка – [значение], порядок сортировки – [по возрастанию]. Нажмите кнопку [ОК]. Вид таблицы после выполнения сортировки представлен на рисунке 19. Обратите внимание, что записи, имеющие одинаковые значения номера склада, выстроились в порядке возрастания кода материала.

Рисунок 19 – Таблица 1 после сортировки

Фильтрация данных

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

В Excel существует возможность фильтрации данных с помощью Фильтра и Расширенного фильтра.

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

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

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

Фильтр предоставляет несколько видов фильтрации данных:

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

2. Выбор записей по условию производится командой Условие, в результате чего открывается диалоговое окно Пользовательский автофильтр, где задают критерии с участием одного или двух условий с использованием шаблонов(”?” и “*”) , операций сравнения и логических операций «И», «ИЛИ».

3. Выбор первых наибольших или наименьших n значений позволяет выполнить команда [Первые 10].

4. Команда [Все] восстанавливает на экране все скрытые фильтром строки таблицы.

Рассмотрим использование фильтра на примере таблицы «Учет движения материалов на складах». Пусть из таблицы необходимо выбрать информацию о материалах со склада №2, у которых остаток на конец месяца больше 50.

Создайте копию листа «Сортировка» и назовите его «Фильтр».

Установите курсор в любую ячейку диапазона A2:F7.

Выберите команду команды [Фильтр], которая находится на ленте, на вкладке [Данные] в группе [Сортировка и фильтр].

Ячейки с названиями полей превращаются в раскрывающиеся списки.

Раскройте список в столбце «№ склада» для включения в критерий.

Выберите строку, где номер склада равен «2».

Раскройте список столбца «Остаток на конец месяца», в раскрывшемся диалоговом окне выберите команду [Числовые фильтры] – [Больше], в появившемся диалоговом окне в правом поле введите значение «50» и нажмите [ОК]. На экране останется одна строка, удовлетворяющая условиям отбора. Остальные строки таблицы окажутся скрытыми.

Рисунок 20 – Окно фильтрации по условию пользователя

Для отмены всех условий отбора надо снять выделение с команды Фильтр в группе Сортировка и фильтр.

Подведение итогов

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

В диалоговом окне [Промежуточные итоги] поле при каждом изменении в используется для указания столбца, по которому следует сгруппировать данные для подведения итогов; поле [Операция] позволяет использовать различные функции; поле [Добавить итоги по]: позволяет отметить все поля, по которым будут подводиться итоги. Установка флажка [Заменить текущие итоги] позволяет заменить в таблице уже существующие итоги. Установленный флажок [Конец страницы] между группами автоматически вставляет конец страницы перед каждой группой данных, для которой вычисляются итоги. Флажок [Итоги] под данными дает возможность поместить строки промежуточных и общих итогов под соответствующими данными. Кнопка [Убрать все] позволяет восстановить исходный вид экрана до подведения итогов.

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

Отсортируйте таблицу по столбцу «№ склада».

Выделите диапазон таблицы вместе с шапкой, т.е. A2:F7.

Откройте окно формирования итогов командой «Данные►Итоги».

Заполните диалоговое окно как показано на рисунке 24 и нажмите [ОК].

Рисунок 24 – Окно Промежуточные итоги

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

Рисунок 25 – Таблица 1 с итогами

Кроме итоговых строк программа сформировала структуру (см. слева от таблицы), которая согласована с группировкой данных для вычисления промежуточных и общих итогов. Знак «-» означает, что можно спрятать строки, относящиеся к группе, оставив только промежуточный или общий результат, (щелкнув по кнопке [-]). Кнопки также служат для управления отображением структуры. Например, кнопка оставляет в таблице только промежуточные и общие итоги, скрывая содержимое таблицы.

ВОПРОС 9. ПОДГОТОВКА К ПЕЧАТИ. ПЕЧАТЬ ЭЛЕКТРОННОЙ ТАБЛИЦЫ

Создание колонтитулов

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

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

Рисунок 29 – Окно форматирования колонтитула

После изменения имени в сводке будет соответствующим образом изменен и текст колонтитула.

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

Задание заголовков таблиц для печати

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

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

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

Предварительный просмотр

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

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

Для выхода из режима просмотра следует нажать кнопку [Закрыть] на панели инструментов.

Печать электронной таблицы

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

- для всех листов задать формат страниц – А4, ориентация - книжная и следующие поля: верхнее – 2 см, нижнее – 2см, левое – 3 см, правое – 1,5 см;

- таблицы разместить по центру страниц;

- для каждого листа установить верхние и нижние колонтитулы: в верхних указать названия таблиц и текущую дату, в нижних – названия листов.

Активизируйте лист «Товары».

На ленте выберите вкладку Разметка страницы, группу [Параметры страницы].

На вкладке «Страница» выберите размер бумаги – А4 и установите ориентацию печати – книжная.

На вкладке [Поля] установите заданные поля и поставьте флажки горизонтально и вертикально.

На вкладке [Колонтитулы] нажмите кнопку [Создать верхний колонтитул..]. Установите курсор в поле [В центре] и введите текст Учет движения материалов на складах. Установите курсор в поле [Справа] и щелкните на кнопке для вставки текущей даты. В окне [Верхний колонтитул] нажмите [ОК].

Для задания нижнего колонтитула откройте поле списка [Нижний колонтитул] и выберите название текущего листа «Товары». В окне [Параметры] страницы нажмите [ОК].

Выполните аналогичные действия для листов «Цены» и «Реализация».

При наличии принтера, вставьте бумагу в принтер, активизируйте лист «Товары», нажмите кнопку Microsoft Office в левом верхнем углу окна и выберите команду [Печать], нажмите [ОК].

ЛАБОРАТОРНАЯ РАБОТА №1

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

Пример 1. Создать в Excel на основании документов, формы которых приведены в таблицах 3 и 4, две таблицы, разместив их на разных листах. Листы переименовать на «Список» и «Успеваемость» соответственно. Рабочую книгу сохранить под именем ЭИУ.xls.

Таблица 3

Список студентов ЭИУ 1-го курса

Группа	№ зачетки	Ф.И.О.	Вид оплаты
ЭИУ1	101490	Авраменко Н.В.	1
ЭИУ1	101492	Быковский Р.Б.	1
ЭИУ1	101495	Васильева Т.К.	2
ЭИУ1	101501	Вершинин Н.Л.	1
ЭИУ1	101504	Володина А.Р.	2
ЭИУ2	101470	Гаврилова Н.Н.	2
ЭИУ2	101476	Горелова Ю.С.	1
ЭИУ2	101483	Гусакова М.С.	1
ЭИУ2	101487	Емелин А.А.	2
ЭИУ3	101403	Засорина Е.А.	2
ЭИУ3	101407	Зимина С.В.	1
ЭИУ3	101414	Капкова И.Г.	1

Примечание: Вид оплаты

1- обучение за счет бюджета

2- платное обучение

Таблица 4

Успеваемость студентов ЭИУ 1-го курса

Группа	№ зачетки	Экзаменационные оценки			Средний бала студента
Группа	№ зачетки	Математика	Информатика	Философия	Средний бала студента
ЭИУ1	1490	4	5	4
ЭИУ1	1492	3	4	4
ЭИУ1	1495	5	5	5
ЭИУ1	1501	4	3	4
ЭИУ1	1504	3	4	3
ЭИУ2	1470	3	3	4
ЭИУ2	1476	2	3	3
ЭИУ2	1483	5	5	5
ЭИУ2	1487	4	4	4
ЭИУ3	1403	4	4	4
ЭИУ3	1407	3	4	3
ЭИУ3	1414	3	3	2
Средний балл по дисциплине

ЛАБОРАТОРНАЯ РАБОТА №2.

Содержание работы: построение и редактирование диаграмм.

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

ЛАБОРАТОРНАЯ РАБОТА №3

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

Внимание. Команды вкладки [Данные] работают корректно, когда шапка таблицы расположена в одной строке (если бы колонки таблицы были пронумерованы, то для работы команды [Данные] можно было бы использовать нумерацию в качестве названия колонок). При работе с командой [Данные] Excel рассматривает таблицу как базу данных (список), в которой строки являются записями, а столбцы - полями.

Для дальнейшей работы преобразуем на новом листе таблицу 4 к виду:

Рисунок 36 – Измененая форма таблицы 4

Команды меню [Данные] многофункциональны. Рассмотрим эти возможности, разбив их по темам.

Сортировка табличных данных

Пример 1. Отсортировать таблицу рисунок 36 по столбцам [Группа] и [Номер зачетки].

Технология сортировки таблицы по двум столбцам:

1) Выделить диапазон таблицы, включая шапку (А3:F15).

2) Выберите команду [Сортировка], которая находится на ленте во вкладке [Данные], в [Группе] [Сортировка и фильтр]. В окне [Сортировка] в раскрывающемся списке [Сортировать по] выберите поле, по которому следует выполнить сортировку в первую очередь – это [Группа]. Далее справа выберите в поле [Сортировка] – [Значение] и в поле [Порядок] – [От А до Я].

В окне Сортировка активируйте команду Добавить уровень. В поле [Затем по] выберите второй ключ сортировки – поле [№ зачетки], Сортировка – [значение], Порядок сортировки – [По возрастанию]. Нажмите кнопку [ОК]. Обратите внимание, что записи, в первую очередь выстроились в порядке увеличения номера группы, а затем в пределах каждой группы по возрастанию номера зачетки.

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

Пример 2. Выбрать из таблицы, представленной на рисунке 36, тех студентов, средний балл которых >=4.

Технология фильтрации (выбора) данных из таблицы с помощью Фильтра.

1) Выделить диапазон таблицы, включая шапку (A3:F15).

2) Выберите команду [Фильтр], которая находится на ленте, на вкладке [Данные] в группе [Сортировка и фильтр].

Ячейки с названиями полей превращаются в раскрывающиеся списки.

3) Раскройте список в столбце [Средний балл студента] для включения в критерий, в раскрывшемся диалоговом окне выберите команду [Числовые фильтры] – [больше или равно…], в появившемся диалоговом окне в правом поле введите значение 4 и нажмите [ОК]. На экране останется пять строк, удовлетворяющих условиям отбора. Остальные строки таблицы окажутся скрытыми.

4)Для отмены всех условий отбора надо снять выделение с команды [Фильтр] в группе [Сортировка и фильтр].

Использование формы данных

Пример 4. Найти в таблице, представленной на рисунке 36, сформированной на листе Список, тех студентов, у которых фамилия начинается с буквы "В".

Технология поиска данных с помощью формы

1. Выделить таблицу вместе с шапкой (A3:D15).

2. Выполнить команду Форма.

Для того чтобы открыть окно формы необходимо добавить кнопку Форма на панель быстрого доступа . Для этого:

Щелкните стрелку на панели быстрого доступа и выберите элемент Другие команды.

В поле Выбрать команды из щелкните элемент Все команды.

В списке выберите кнопку Форма и щелкните элемент Добавить.

Откроется форма данных.

3. Нажать кнопку [Критерии].

4. В окне Форма произойдет очистка полей и замена названий некоторых кнопок в форме.

5. В поле Фамилия, которое будет участвовать в определении критерия, ввести критерий: В*.

6. Нажать кнопку [Далее] или [Назад], чтобы перейти к записи, удовлетворяющей введенному критерию.

7. Нажать кнопку [Закрыть].

Пример 5. Изменить в таблице “Список студентов ЭИУ 1-го курса”, представленной на листе “Список”, фамилию студентки Володина на новую - Пугачева, полученную после замужества.

Технология редактирования данных с помощью формы

1. Выделить таблицу вместе с шапкой (A3:D15).

2. Выполнить команду [Форма]. Откроется форма данных.

3. Нажать кнопку [Критерии].

4. Произойдет очистка полей и замена некоторых кнопок в форме.

5. Перейти к полю Фамилия, которое будет участвовать в определении критерия и ввести критерий: Володина А.Р.

6. Нажать кнопку [Далее] или [Назад], чтобы перейти к записи, удовлетворяющей введенному критерию.

7. Ввести новую фамилию и инициалы: Пугачева А.Р.

8. Нажать кнопку [Закрыть].

Подведение итогов

Пример 6. Рассчитать средние баллы по всем дисциплинам, каждой из учебных групп (на примере таблицы, представленной на рисунке 32).

Технология подведения частных и общих итогов

1. Выделить диапазон таблицы, включая шапку (A3:F15).

2. Выполнить команду Промежуточные итоги на вкладке [Данные] в группе [Структура].

В поле [При каждом изменении в] из раскрывающегося списка выбрать [Группа];

в поле [Операция] из раскрывающегося списка выбрать [Среднее];

в поле [Добавить итоги по] установить флажки: Математика, Информатика, Философия;

установить флажок [Итоги под данными].

3. Нажать кнопку [ОК].

4. Округлить полученные итоги до двух десятичных знаков с помощью команды [Формат ячеек].

Результат работы команды [Итоги] представлен на рисунке 39.

Рисунок 39 – Результаты выполнения команды Промежуточные итоги

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

Для получения развернутой информации по группе следует нажать кнопку с изображением знака "+" для соответствующей группы; для свертывания - кнопку с изображением знака "-".

Рисунок 40 – Результат показа второго уровня итогов.

ЛАБОРАТОРНАЯ РАБОТА №4

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

Пример 1. Сформировать в Excel таблицу для расчета сумм стипендий студентам факультета ЭИУ в соответствии с формой, представленной в таблице 5. При этом учесть следующие условия:

· стипендия не начисляется студентам, обучающимся на платной основе;

· студенты получают минимальную стипендию, если средний экзаменационный балл больше 3;

· студенты, имеющие средний балл более 4.5, получают 50 % надбавки к стипендии.

Таблица 5

Ведомость начисления стипендии студентам ЭИУ 1-го курса

	Сумма минимальной стипендии		12000
№ зачетки	Ф.И.О.	Стипендия	Надбавка к стипендии	Итого начислено
101490	Авраменко Н.В.
101492	Быковский Р.Б.
101495	Васильева Т.К.
101501	Вершинин Н.Л.
101504	Володина А.Р.
101470	Гаврилова Н.Н.
101476	Горелова Ю.С.
101483	Гусакова М.С.
101487	Емелин А.А.
101403	Засорина Е.А.
101407	Зимина С.В.
101414	Капкова И.Г.

ЛАБОРАТОРНАЯ РАБОТА №5

Содержание работы: Создание собственных имен ячеек. Работа со связанными таблицами. Создание формул с использованием абсолютных ссылок. Функции ЕСЛИ и СУММ. Построение диаграмм. Форматирование таблицы.

Цель работы. Научиться работать с абсолютными ссылками. Закрепить знания по работе со связанными таблицами и по использованию Мастера функций.

Задача. Рассчитать расходы мелкооптовой базы, обслуживающей 5 магазинов. Расходы включают 3 статьи:

1) постоянные расходы в размере 12 000 руб. за каждый привоз товара в магазин;

2) транспортные расходы в размере 6000 руб. за 1 км пути от базы до магазина;

3) разгрузочные расходы в размере:

1000 рублей за 1 минуту простоя при простое до 10 минут;

1200 рублей за 1 минуту простоя при простое от 11 до 20 минут;

1500 рублей за 1 минуту простоя при простое от 21 до 30 минут;

1700 рублей за 1 минуту простоя при простое более 30 минут.

I. Создание таблицы исходных данных

Задание 1. На новом листе создайте таблицу исходных данных, в соответствии с рисунком 42. Назовите лист «Исходные».

Отформатируйте таблицу, выделив «шапку» таблицы и наименование магазинов. Шрифт, заливку и обрамление выберите произвольно.

Рисунок 42 – Изображение таблицы с исходными данными

II. Создание собственных имен ячеек

Задание 2. Присвойте ячейкам, содержащим тарифы по всем статьям расхода соответствующие собственные имена.

1. Выделите курсором ячейку D3, содержащую тариф постоянных расходов.

2. Щелкните в поле имен:

3. Введите вместо выделенного имени D3 собственное имя ячейки, например Постоянные.

4. Аналогично присвойте соответствующие имена остальным ячейкам (D4, D5, D6, D7, D8) с тарифами.

Замечание. Имя ячейки не должно содержать пробелов и дефисов, их можно заменить символом подчеркивания.

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

III. Расчет расходов с использованием технологии связанных таблиц.

Задание 3. На новом листе создайте таблицу для расчета расходов базы в соответствии с рисунком 43. Назовите лист «Расчетные».

Рисунок 43 – Изображение таблицы для расчета

Задание 4. Рассчитайте все виды расходов, в том числе их сумму в итоговой строке (ИТОГО) и столбце (Всего расходов).

1. При расчетах используйте ссылку на лист «Исходные».

2. При расчете разгрузочных расходов используйте функцию ЕСЛИ.

3. Отформатируйте таблицу на рисунке 43, выделив «шапку» таблицы, наименование магазинов и итоговые строку и столбец.

Задание 5. По таблице на рисунке 43 постройте гистограмму, отражающую все виды расходов магазинов.

IV. Представление результатов работы.

ЛИТЕРАТУРА

1.Табличный процессор. Электронный ресурс http://ru.wikipedia.org/wiki/Электронная_таблица

2. «В ногу со временем вместе с Excel 2007». Электронный ресурс.http://office.microsoft.com/ru-ru/training/RZ010062103.aspx?section=3

ОГЛАВЛЕНИЕ

ВВЕДЕНИЕ.. 3

ИЗ ИСТОРИИ.. 4

ТАБЛИЧНЫЙ РЕДАКТОР MICROSOFT EXCEL.. 5

ВОПРОС 1. ФУНКЦИОНАЛЬНЫЕ ВОЗМОЖНОСТИ ТАБЛИЧНЫХ РЕДАКТОРОВ 5

ВОПРОС 2. ИНТЕРФЕЙС И ОСНОВНЫЕ ПОНЯТИЯ ТАБЛИЧНОГО РЕДАКТОРА EXCEL 8

ВОПРОС 3. ТЕХНОЛОГИЯ РАЗРАБОТКИ ЭЛЕКТРОННОЙ ТАБЛИЦЫ 12

Ввод данных и их редактирование. 13

Ввод константы.. 13

Редактирование данных. 16

Ввод формул. Использование мастера функций. 19

Автозаполнение. 22

Стандартные и пользовательские числовые форматы.. 24

ВОПРОС 4. ДЕЛОВАЯ ГРАФИКА В СРЕДЕ EXCEL.. 26

Построение диаграммы.. 27

Редактирование диаграмм.. 28

Форматирование диаграмм.. 31

ВОПРОС 5. ЗАЩИТА ДАННЫХ.. 34

ВОПРОС 6. РАБОТА С ТАБЛИЦЕЙ КАК С БАЗОЙ ДАННЫХ.. 36

Работа с формой. 38

Сортировка данных. 40

Фильтрация данных. 41

Подведение итогов. 48

ВОПРОС 7. ТЕХНОЛОГИЯ СВЯЗЫВАНИЯ ТАБЛИЦ.. 51

ВОПРОС 8. РАБОТА С МАКРОСАМИ В ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL 55

ВОПРОС 9. ПОДГОТОВКА К ПЕЧАТИ. ПЕЧАТЬ ЭЛЕКТРОННОЙ ТАБЛИЦЫ 57

Установка параметров страницы.. 57

Создание колонтитулов. 57

Задание заголовков таблиц для печати. 58

Предварительный просмотр. 59

Разделение рабочих листов на страницы.. 59

Печать электронной таблицы.. 60

ВОПРОС 10. ПРАКТИЧЕСКАЯ ЧАСТЬ.. 62

ЛАБОРАТОРНАЯ РАБОТА №1. 62

Содержание работы.. 62

Технология построения таблицы.. 64

ЛАБОРАТОРНАЯ РАБОТА №2. 71

Содержание работы.. 71

Технология построения диаграммы.. 71

Технология редактирования диаграммы.. 73

ЛАБОРАТОРНАЯ РАБОТА №3. 76

Содержание работы.. 76

Сортировка табличных данных. 76

Использование фильтра. 77

Работа с расширенным фильтром.. 77

Использование формы данных. 80

Подведение итогов. 81

ЛАБОРАТОРНАЯ РАБОТА №4. 83

Содержание работы.. 83

Технология создания связанных таблиц. 84

ЛАБОРАТОРНАЯ РАБОТА №5. 87

Содержание работы: 87

Цель работы.. 87

Задача. 87

ЛИТЕРАТУРА.. 90

ОГЛАВЛЕНИЕ.. 91