Практическое занятие 6. Работа с электронными таблицами ms Excel

Цель работы:овладеть основными технологическими приемами работы с электронными таблицамиMicrosoftExcel.

Краткие сведения из теории.

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

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

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

Макросы в MSExcelсоздаются по тому же алгоритму, что и вMSWord.

Задание6.1.Запустите MS Excel: Пуск – Программы – (Microsoft Office) – Microsoft Excel. Для быстрого запуска можно создать на Рабочем столе ярлык.

Справку можно получить, нажав клавишу F1, через менюСправкаили кнопкойСправкана Стандартной панели.

Задание6.1.1.Каждый документ представляет собой рабочую книгу – набор листов-таблиц, разграфленных на строки и столбцы. Файлы имеют расширениеxls. Сохранение, открытие, создание рабочей книги выполняется командами менюФайл.

Сохраните книгу в своей папке под именем ОТЧЕТ* (вместо * введите свою фамилию). Закройте книгу. Найдите и откройте свою книгу.

Задание6.1.2.Окно программы состоит из рабочего поля. Курсор ввода представляет собой рамку, выделяющую активную ячейку, где будут появляться символы, вводимые с клавиатуры. Вверху – строка заголовка; в ней имя программы и имя файла рабочей книги. Ниже – строка меню, открывающая доступ к командам, предназначенным для работы с документами. Справа и внизу – полосы прокрутки.

В самом низу окна расположена строка состояний.

Отображением полос прокрутки, строки состояния и т.д. управляют в окне Сервис – Параметры – вкладка Вид. Ознакомьтесь с вкладками этого окна.

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

Выключите панель Форматирование. Включите ее.

Задание6.1.4.Под Панелями слева – поле Имени, справа – Строка формул, в которой отображается содержимое активной ячейки. При вводе данных в ней появляются две кнопки; крестик отменяет ввод (эквивалентно нажатиюEsc), зеленая галочка подтверждает ввод (эквивалентно нажатиюEnter). Перевод Строки формул в активное состояние выполняется в менюВид.

Выключите Строку формул. Включите ее.

Задание6.1.5.Одна рабочая книга может состоять из 256 листов; по умолчанию они называются Лист 1, Лист 2 и т. д. В нижней части экрана левее горизонтальной полосы прокрутки находятся ярлычки рабочих листов. Чтобы изменить количество отображаемых ярлычков, следует передвинуть двунаправленной стрелкой маркер, отделяющий их от горизонтальной полосы прокрутки. Если в книге много листов, ее удобно перелистывать, вызвав правой кнопкой мыши их список на кнопках прокрутки слева от ярлычков.

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

Перейдите на Лист 2. Назовите его «График функции».

Поставьте лист «График функции» первым в книге.

Командой Формат – Листможно скрыть (отобразить) лист. Скройте лист «График функции». Отобразите лист.

Задание6.1.6.Лист содержит 256 столбцов и 65 536 строк. Над таблицей – буквенные обозначения столбцов, слева – номера строк. Пересечение строк и столбцов образует ячейки, которые служат для ввода данных. Адрес ячейки однозначно задается номером строки и столбца и отображается в поле имени. При указании адреса ячейки используются латинские буквы. Например, А1 – ячейка в левом верхнем углу листа. Можно перейти к другому стилю, когда и столбцы и строки указываются числами. В этом стиле R1С1 обозначает ячейку А1 (R (Row) – номер строки, С (Column) – номер столбца). Стиль меняют на вкладкеОбщиедиалогового окнаСервис – Параметры.

Сравните, как задается адрес ячейки в разных стилях.

Задание 6.2. Выделение ячеек.

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

Установите курсор мыши на заголовок столбца С и щелкните по нему. Вы выделили столбец С. Если щелкнуть в другом месте рабочего листа, выделение снимется.

Установите курсор мыши на заголовок строки 2, нажмите левую кнопку мыши и перетащите указатель мыши на заголовок строки 4. Вы выделили строки 2, 3, 4. Снимите выделение.

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

Установите курсор мыши на ячейку С3. Нажмите левую кнопку мыши, перетащите ее указатель на ячейку Е8 и отпустите кнопку. Вы выделили область С3:Е8. Снимите выделение.

Установите курсор мыши на ячейку Е10. Удерживая клавишу Shift, нажимайте по очереди клавиши управления курсором. Происходит выделение диапазона ячеек в выбранном направлении.

Установите курсор мыши на ячейку А1. С помощью полос прокрутки выведите на экран ячейку Х40 и, удерживая клавишу Shift, щелкните по ней. Вы выделили область, охватывающую большой диапазон ячеек А1:Х40.

Выделите область В2:Е6. Удерживая клавишу Ctrl, выделите область С7:Н10. Вы выделили две несмежные области.

Задание 6.3. Ввод данных.

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

Защиту книги или ее отдельных листов устанавливают в меню Сервис.

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

Отказ от действия выполняется командой Правка–Отменить.

Задание 6.3.1. Перейдите на Лист 1. Введите в ячейку А1 свое имя и фамилию.

Введите в ячейку В1 значение 12345,6789 (десятичная дробь отделяется запятой). Поменяйте формат ячейки В1: дата; денежный; процентный. Обратите внимание, что в формате даты дни отсчитываются от 1 января 1900 года. Сравните значения в числовом формате с разным количеством десятичных знаков. Задайте Общий формат.

Введите в ячейку С1 формулу: =5*2+3/2. При этом в текущей ячейке отображается результат вычислений, а в Строке формул – введенная формула.

Введите в ячейку D1: =В1-С1*1000.

Выделите ячейки С1:D1. В строке состояния справа отобразилась сумма содержимого выделенных ячеек. Это поле для автовычислений. Вызовите на нем контекстное меню. Какие другие функции можно выбрать?

Отредактируйте формулу в ячейке С1 (вместо числа 3 введите 25). Что произошло с числами в С1 и в D1? Если они не изменились, проверьте в окне Сервис - Параметры – вкладка ВычисленияопциюАвтоматически.

Задание 6.3.2. В ячейку В2 введите формулу: =В1–В1. Если в таблице нежелательно отображение нулей, следует в окне Сервис – Параметрына вкладкеВидотключить в разделеПараметры окнанулевые значения.

Задание 6.3.3. Удобно делать текстовые примечания к ячейкам. Для этого укажите ячейку, к которой необходимо добавить комментарий. ВыберитеВставка – Примечание(или контекстная командаДобавить примечание). Введите текст. Редактирование (удаление) примечаний удобно делать через контекстное меню.

Сделайте примечание к ячейке А1. Как отмечается ячейка с примечанием?

Задание 6.4. Автоматизация ввода.

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

Автозаполнение допускается как в вертикальном, так и в горизонтальном направлениях.

Задание 6.4.1. Введите в ячейку Е1 число 5. Перетащите маркер заполнения левой кнопкой мыши черным крестиком до ячейки Е15.

Задание 6.4.2. Введите в ячейку F1 число 5. Перетащите маркер заполнения правой кнопкой мыши до F15. В появившемся контекстном меню выберитеЗаполнить.

Задание 6.4.3. Введите в ячейку G1: Январь. Перетащите маркер заполнения левой кнопкой до ячейки G15. Вы использовали встроенный вExcelсписок.

Задание 6.4.4. Можно встроить свой список. Для этого введите любой список в ячейки G20:G25 и выделите их. Вызовите менюСервис – Параметры–Списки. Нажмите кнопкиИмпорт иДобавить. Закройте окно. Введите в Н1 произвольный элемент встроенного вами списка и перетащите маркер заполнения левой кнопкой до ячейки Н15. Удалите свой список из встроенных.

Задание 6.4.5. Введите в ячейку I1 число 5. Перетащите маркер заполнения правой кнопкой мыши до I15. В появившемся контекстном меню выберитеПрогрессия. В диалоговом окне установитеГеометрическаяс шагом 2.

Задание 6.4.6. Введите в ячейку J1 число 5, а в ячейку J2 число 7. Выделите эти две ячейки. Перетащите левой кнопкой маркер заполнения до ячейки J15.

Задание 6.4.7. Введите в ячейку К1 число 5. Выделите область К1:К15. Вызовите командуПравка – Заполнить – Прогрессия. В диалоговом окне установитеАрифметическаяс шагом 2.

Задание 6.4.8. Введите в ячейку L1 число –5, а в ячейку L15 – число 27. Выделите область L1:L15. Вызовите командуПравка – Заполнить – Прогрессия. В диалоговом окне установитеАвтоматическое определение шага.

Задание 6.4.9. Введите в ячейку М1 текстДом 1. Перетащите маркер заполнения левой кнопкой до ячейки М15.

Задание 6.4.10. Введите в ячейку N1 текущую дату. Перетащите маркер заполнения правой кнопкой мыши до N15. ВыберитеЗаполнить по дням. Аналогично перетаскивая маркер дальше вниз, сравните разные варианты выполнения команд появляющегося меню.

Задание 6.5. Изменение размеров ячеек.

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

Установите курсор на А1. Щелкните в Строке формул в конце текста, введите номер группы и завершите ввод. Часть информации осталась за пределами видимости. Измените ширину столбца А.

Увеличьте высоту строки 1. Уменьшите ширину столбца С.

Выполните автоподбор ширины столбца С.

Задание 6.6. Операции с ячейками.

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

Задание 6.6.1. Различают понятияОчистка содержимогоиУдаление ячеек(диапазона ячеек). Используются команды менюПравкаили контекстного меню. При удалении происходит сдвиг всех остальных ячеек.

Очистите содержимое ячейки С1. Отмените это действие.

Задание 6.6.2. Удалите ячейку В1 со сдвигом влево. Удалите строку 2. Удалите столбец С. Отмените три последних действия.

Задание 6.6.3. Добавление строк или столбцов выполняется через менюВставка–Строки(Столбца).

Вставьте перед строкой 3 одну строку. Вставьте между столбцами D и E один столбец. Нажмите клавишу F4, повторяющую любое последнее действие. Отмените вставку строк и столбцов.

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

При копировании данных становится доступной кнопка Параметры вставки.

Скопируйте данные из области А1:С1 вниз разными способами.

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

Скопируйте данные из Е1:Е5 между столбцами К и L.

Задание 6.6.6. При копировании результатов вычислений вMSWordможно обеспечить целостность данных. Для этого выполнить вMSExcelкомандуКопировать, затем перейти вMSWordи в диалоговом окнеПравка – Специальная вставка включить параметрСвязать. В этом случае при изменении результатов вычисления в таблице, изменится и скопированный результат (для этого или открыть файл текста заново, или, установив курсор на результат, нажатьF9).

Скопируйте с установлением связи данные из А1:D1 в документMSWord. Отредактируйте вMSExcelформулу вD1 (вместо разности вычислите сумму). Отразились ли изменения в документе Word?

Задание 6.6.7. Выделите строки 2:7. Вызовите на выделенном контекстную командуСкрыть. Если выделить строки 3:8 и выбрать контекстную командуОтобразить, строки снова появятся на листе. Аналогично скрывают столбцы.

Задание 6.7. Копирование формул.

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

Задание 6.7.1. Скопируйте формулу из ячейки D1 в D2, Н20, К30, С10. Обратите внимание на изменение относительных ссылок в формуле.

Задание 6.7.2. Абсолютные ссылки и имена при копировании не меняются.

Абсолютная адресация устанавливается символом $ перед номером строки или столбца. Чтобы быстро изменить относительную ссылку на абсолютную, следует сделать текущей ячейку с формулой, установить курсор на адрес нужной ячейки в строке формул и нажать клавишу F4, выполняющую циклическое изменение типа ссылки.

Установите в формуле ячейки D1 абсолютную ссылку на ячейку $В$1. Скопируйте формулу из ячейки D1 в D3, Н21, К31, С11. Как изменяется абсолютная ссылка при копировании?

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

Присвойте ячейке В1 имя КВ, а ячейке С1 имя КС. Теперь в поле имени Строки формул отображается не адрес ячейки, а присвоенное имя. Введите в ячейку D4 формулу вычисления разности ячеек КВ и КС. Скопируйте формулу из ячейки D4 в D5, С12, Н22. Обратите внимание, что при копировании ссылки на имена не меняются.

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

Чтобы воспользоваться встроенными функциями, следует командой Вставка – Функция(или кнопкойf_xв строке формул) открыть окно Мастера функций. В появившемся диалоговом окне выбрать категорию и функцию. НажатьОК. С помощью появившейся палитры ввести аргументы функции в числовом виде или как ссылки на ячейки. Аргументами могут быть и другие функции.

В ячейке Е20 вычислите косинус числа 3,5 (–0,93646). Здесь и далее в скобках приведен ответ.

В ячейке F20 вычислите квадратный корень из значения ячейки F1 (2,236068).

В ячейках F21:F23 выполните округление значения из F20 до второго знака после запятой с помощью функций ОКРУГЛ, ОКРВВЕРХ и ОКРУГЛВНИЗ (категория Математические). В чем отличие этих трех способов округления?

Поменяйте значение в F1. Изменились ли результаты в F20:F23?

Задание 6.9. Работа с большими рабочими листами.

Если нужно одновременно разместить на экране далеко отстоящие части одного листа, то это можно сделать с помощью разделения окон, позволяющего просматривать четыре разные части документа одновременно. Для этого вызовите меню Окно–Разделить. Появятся линии разбивки. Их можно перемещать, «ухватив» левой кнопкой мыши курсором в виде двунаправленной стрелки. Чтобы убрать разбиение, вызовитеОкно–Снятьразделение.

Разбейте текущий лист на четыре части. Убедитесь, что с каждой из них можно работать автономно. Уберите разделение.

Задание 6.10. Вывод рабочей книги на печать.

Задание 6.10.1. КомандаФайл – Параметры страницыпозволяет задать поля, ориентацию, масштабирование печати.

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

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

Задание 6.10.2. Чтобы напечатать часть листа, ее следует выделить и вызвать командуФайл – Область печати – Задать. Отмена области печати делается через тот же пункт меню.

Задайте область печати А1:В10. Выполните просмотр. Снимите ограничение на область печати.

Задание 6.10.3. Если есть необходимость разбиения листа на нестандартные страницы, то это можно сделать следующим образом. Выбрать ячейку, с которой должна начинаться новая страница, и вызвать менюВставка–Разрывстраницы. Чтобы убрать разбиение, нужно сделать текущей ячейку левее и ниже границы разрыва и вызвать менюВставка–Убратьразрывстраницы.

Также можно перейти в режим Вид – Разметка страниц и расставить границы страниц вручную.

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

Разбейте таблицу на два листа. Задайте первую сроку как сквозную. Выполните просмотр.

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

Задание 6.11. Диаграммы и графики.

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

Шаг 1.Тип диаграммы. Чтобы увидеть, как будет выглядеть диаграмма выбранного типа, нажмите кнопкуПросмотр результата. Можно выбрать тип со второй вкладки –Нестандартные. Для построения графика функции используют типТочечная.

Шаг 2.Источник данных. На вкладкеДиапазонданных отображается информация о том, по каким ячейкам будет строиться диаграмма. На вкладкеРядможно дать имена рядам данных.

Шаг 3.Параметры диаграммы. На вкладках этого окна выполняется оформление внешнего вида диаграммы.

Шаг 4.Размещение диаграммы. Следует выбрать вариант размещения диаграммы – внедренная в лист с таблицей данных или расположенная на отдельном листе.

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

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

Задание 6.11.1. На листе «Диаграмма» введите таблицу 5.3.

Выполните вычисления, используя функцию СУММ (категория Математические).

Постройте гистограмму, иллюстрирующую изменение спроса на мебель.

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

Задание 6.11.2. Введите таблицу, аналогичную таблице 5.5.

Выполните вычисления, используя функцию СРЗНАЧ (категория Статистические).

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

Задание 6.12. Пусть задана функцияy= 2^x+ 5 cos²x– 2.

Построить график функции на отрезке [–3; 4].

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

Выполнить эти процедуры можно различными способами. Рассмотрим два из них.

Задание 6.12.1. 1 способ.Перейдите на лист График функции.

1. Введите в ячейку А1: X; в ячейку А2 значение –3, в ячейку А26 – значение 4. Заполните область А2:А26 прогрессией с автоматическим определением шага.

2. Для оформления графика в ячейку В1 введите формулу функции (можно в общем виде: y=f(x)). Введите в ячейку В2 формулу для вычисления функции, указывая вместо аргумента адрес первой ячейки из списка аргументов: = 2^A2+5*COS(A2)^2–2. Скопируйте эту формулу на область В2:В26 маркером заполнения.

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

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

У вас должен получиться примерно такой график.

Задание 6.12.2. 2 способ. Рассмотрим способ с использованием именованных диапазонов. Перейдите на другой лист. Назовите его График2.

1. Повторите первый шаг 1-го способа. Выделите область А2:А26. Вызовите команду Вставка – Имя – Присвоить. В диалоговом окне вам будет предложено имя Х для диапазона А2:А26. НажмитеДобавитьи закройте окно.

2. В ячейку В1 введите формулу функции. Введите в ячейку В2 формулу для вычисления функции: = 2^х+5*соs(x)^2–2. Заполните этой формулой область В2:В26.

3. Постройте график функции по алгоритму третьего шага 1-го способа.

В дальнейшем при построении графиков функций можете использовать любой из способов.

Задание 6.13. Решить уравнение 2^x+ 5 cos²x– 2 = 0.

Напомним, что корнем уравнения f(x) = 0 называется такое значение аргумента, при котором уравнение обращается в тождество. Геометрически корень уравнения – абсцисса точки пересечения, касания или другой общей точки графика функцииy=f(x) с осьюОх.

График функции позволяет отделить корни уравнения. Можно утверждать, что уравнение имеет корни, по крайней мере, на отрезках [–2,5; –1,5] и [–1,5; –0,5].

Вернитесь на лист График функции. Введите в D1: Корни. Выполните объединение ячеек D1:Е1. Для этого выделите D1:Е1 и на вкладкеВыравниваниедиалогового окнаФормат – ЯчейкивключитеОбъединение ячеек.

Найдите корень на [–2,5; –1,5]. Для этого скопируйте формулу из любой ячейки столбца В листа График функции в ячейку Е2. При этом в качестве аргумента в формуле фигурирует ячейка D2. Нужно подобрать в ячейке D2 такое значение, чтобы в ячейке Е2 получился ноль. Воспользуйтесь процедурой Поиск решения, встроенной в Excel(следует иметь в виду, что численные методы сходятся не всегда, поэтому для некоторых функций Поиск не сможет найти решение).

Вызовите команду Сервис–Поиск решения. Если в менюСервистакой команды нет, процедуру следует включить в окнеСервис–Надстройки. В том случае, когда и в этом окне его нет, следует переустановитьExcel.

Заполните диалоговое окно Поиска.

Установить целевую: указать адрес ячейки Е2

Равной значению: 0

Изменяя ячейки: указать D2 (можно щелчком мыши по ячейке)

Щелкните кнопку Добавить. В появившемся диалоговом окне «Добавление ограничения» введите условие принадлежности искомой точки рассматриваемому отрезку:

Ссылка на ячейку: D2

Во втором окошке нажмите стрелку ниспадающего меню и выберите знак 

В окошке Ограничениевведите значение –2,5

Нажмите Добавить. Аналогичным образом добавьте условие D2–1,5

Нажмите ОКи вернитесь в окно Поиска.

Кнопкой Выполнитьзапустите процедуру.

Если все сделано правильно, появится сообщение: Решение найдено. КнопкойОКсохранить найденное решение. В ячейке D2 отобразится значение корня уравнения –2,21096. Обратите внимание, что значение функции в Е2 представлено в экспоненциальной форме (–9,73E-07 = –9,73·10^–7) и его можно принять равным нулю с достаточной точностью.

Самостоятельно найдите корень на отрезке [–1,5; –0,5]. Ответ: –0,99.

Задание 6.14. Для поиска корня также можно пользоваться процедурой Подбор параметра. Скопируйте формулу из любой ячейки столбца В листа График функции в ячейку Е4. При этом в качестве аргумента в формуле фигурирует ячейка D4. Нужно подобрать в ячейке D4 такое значение, чтобы в ячейке Е4 получился ноль.

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

Установить в ячейке: Е4

Значение: 0

Изменяя значение в ячейке: D4

Кнопкой ОКзапустите процедуру.

Подбор параметра найдет ближайшее к стоящему в D4 значение корня. Так как D4 – пустая, то значение в ней равно нулю. Поэтому в D4 появится –0,99.

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

Скопируйте диапазон Е4:D4 в Е5:D5. В качестве начального приближения введите в Е5 значение –2. Выполните Подбор параметра.

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

Задание 6.15. Найти экстремумы функции 2^x+ 5 cos²x– 2 = 0.

Из графика функции видно, что она многоэкстремальная. Найдем локальный экстремум на отрезке [–3; 0]. Воспользуемся процедурой Поиска решения, указывая в диалоговом окне задания условий поиск минимального значения. Должны получить y_min–1.666 приx–1,59377.

Найдите локальный максимум на [–1; 1]. Ответ:  (0,07318; 4,0253).

Найдите локальный минимум на [0; 4]. Ответ:  (1,385491; 0,782343). При этом в силу особенностей поиска локального экстремума в качестве начального приближения этого значения корня следует выбрать ненулевое значение, например, 3.

Сделайте соответствующие пояснения на листе.

Задание 6.16. Построить поверхностьz = 2 x² – 2y³приx,y[–1, 1]

Назовите лист Поверхность. В ячейку А1 введите х, в ячейку В1:у, в ячейкуC1:z. Заполните области А2:А20 и В2:В20 значениямихиуна заданном диапазоне, используя прогрессию с автоматическим определением шага. В С2 введите формулу функции: = 2 * A2 ^ 2 – 2 * B2 ^ 3. Заполните диапазон С2:С20 значениями функции и постройте диаграммуПроволочная поверхность.

Форматирование поверхности выполняется стандартными методами.

Поэкспериментируйте с контекстной командой Объемный види добейтесь наиболее выгодного ракурса.

Задание 6.17. Найти скалярное произведение векторов (1; 4; 2) и (–1; 3; 0).

При работе с векторами и матрицами электронные таблицы должны идентифицировать их элементы как элементы единого целого – массива. В качестве элементов массива могут выступать числа, адреса и имена ячеек или диапазонов, формулы, возвращающие значения. При работе с массивом для подтверждения ввода используется комбинация клавиш Ctrl + Shift + Enter.

Скалярное произведение двух векторов равно сумме произведений соответствующих координат этих векторов .

Задание 6.17.1. Чтобы вычислить скалярное произведение двух векторов, нужно ввести координаты каждого вектора в смежные ячейки (по одной в ячейку). Вектора лучше вводить в столбец. Щелкните по пустой ячейке, вызовите функцию СУММ или нажмите кнопку Автосуммирование на Стандартной панели. В качестве аргумента функции должно фигурировать произведение областей, в которые введены вектора (выделить координаты первого вектора, нажать знак умножения, выделить координаты второго вектора и нажатьCtrl + Shift +Enter). Ответ: 11.

Задание 6.17.2. Вычислите скалярное произведение векторов другим способом, используя встроенную функцию СУММПРОИЗВ (категория Математические). Ответ: 11.

Задание 6.18. Вычислить длину первого вектора из задания 6.17.

Длина вектора равна корню квадратному из суммы квадратов его координат. Использовать функцию СУММКВ, а затем извлечь из результата квадратный корень. Ответ: 4,58.

Задание 6.19. Вычислить расстояние между векторами из задания 6.17.

Расстояние между векторами вычисляется как корень квадратный из суммы квадратов разностей их координат. Использовать функцию СУММКВРАЗН и КОРЕНЬ. Ответ: 3.

Задание 6.20. Вычислить определитель матрицы

Ввести матрицу (по одному элементу в каждую ячейку). В пустой ячейке ввести функцию МОПРЕД (категория Математические). В качестве аргумента функции ввести область, в которую введена матрица. Нажать ОК.Ответ: 1128.

Задание 6.21. Транспонировать матрицу из задания 6.20.

Выделить область для результата. Если исходная матрица имеет размерность mn, то транспонированная матрица должна иметь размерностьnm(в данном случае 44). Вызвать функцию ТРАНСП (категория Ссылки и массивы). В качестве аргумента ввести диапазон, в котором размещена транспонируемая матрица, и нажатьCtrl +Shift +Enter.

В результате столбцы станут строками, а строки – столбцами.

Задание 6.22. Найти матрицу, обратную матрице из задания 6.20.

Выделить область для результата. Если исходная матрица имеет размерность mn, то обратная матрица должна иметь размерностьnm. Вызвать функцию МОБР (категория Математические). В качестве аргумента ввести диапазон, в котором размещена обращаемая матрица, и нажатьCtrl + Shift + Enter.

Ответ:

Задание 6.23. Умножить матрицу из задания 6.20 на число 3.

Чтобы умножить матрицу на число, следует умножить на это число каждый ее элемент.

В пустой ячейке введите формулу, в которой элемент первой строки первого столбца матрицы умножается на 3, указывая не значение элемента, а его адрес. Например, если матрица введена в А11:А14, то формула будет такая: =3*А11. Так как матрица имеет порядок 4 4, скопируйте эту формулу маркером заполнения на три строки вниз и на три столбца вправо.

Задание 6.24. Сложить матрицу из задания 6.20 и результат из задания 6.21.

Чтобы сложить две матрицы, следует сложить их соответствующие элементы.

В пустой ячейке введите формулу сложения левых верхних элементов матриц. Например, если матрица из задания 6.20 введена в А11:А14, а результат выполнения задания 6.21 выведен в А16:А19, то формула будет такая: = А11+А16. Вычислите остальные элементы, скопировав формулу маркером заполнения.

Задание 6.25. Умножить матрицу из задания 6.20 на вектор-столбец (0; 4; –4; 2).

Ввести вектор в столбец. Выделить область для результата. Если первый сомножитель имеет размерность mn, а второй –nk, то произведение должно иметь размерностьmk. Поэтому предварительно выделяют областьmk. Для рассматриваемого примера нужно выделить 41. Вызвать функцию МУМНОЖ (категория Математические). В диалоговом окне Мастера функций ввести:Массив 1: диапазон, в котором размещена первая матрица;Массив 2: диапазон, в котором размещен вектор. НажатьCtrl +Shift+Enter.Ответ: (4; –36; 88; –4).

Задание 6.26. Умножить матрицу из задания 6.20 на результат из задания 6.22.

Для рассматриваемого примера нужно выделить область 4 4. Должна получиться единичная матрица, то есть такая, что на главной диагонали ее – единицы, а во всех остальных позициях – нули. Обратите внимание на погрешность вычислений. Измените формат чисел в ячейках результата как Числовой с одним десятичным знаком.

Задание 6.27. Решение системыnлинейных уравнений.

Дана система n линейных уравнений сnнеизвестными:

(6.1)

Решить систему (6.1) методом Крамера.

Метод Крамера состоит в том, что вычисляют – определитель матрицы, составленной из коэффициентов системы. Затем вычисляют определители матриц_i(i= 1, 2, …,n), составленных из первой матрицы заменойi-го столбца на столбец свободных членов. Тогдаx_i=_i/ ,i= 1, 2, …,n.

Возьмите чистый лист и назовите его «Правило Крамера». Введите в области А1:D4 матрицу коэффициентов системы. В ячейке Е1 вычислите определитель матрицы (349,18). Введите в области А6:А9 столбец свободных членов. Скопируйте значения из области В1:D4 в область B6:D9. Вы получили матрицу, в которой первый столбец заменен столбцом свободных членов. Аналогично введите в область А11:D14 матрицу, полученную из исходной заменой второго столбца свободными членами; в области А16:D19 – матрицу, полученную из исходной заменой третьего столбца; в области А21:D24 – матрицу, полученную из исходной заменой четвертого столбца. Скопируйте формулу из Е1 в Е6 (331,16), Е11 (328,38), Е16 (–89,2), Е21 (49,8).

Введите в ячейку F1 формулу: = Е6/$E$1. Вы получите значение первого неизвестного x₁= 0,948393. Скопируйте эту формулу в F6, F11, F16. Вы получите соответственно x₂= 0,940432,x₃= –0,25546,x₄= 0,14262.

Задание 6.28. Решить систему уравнений (6.1) матричным методом.

Систему уравнений (6.1) можно записать в матричном виде: АХ=В. ЗдесьА– матрица, составленная из коэффициентов системы,В– вектор-столбец свободных членов,Х– вектор-столбец неизвестных. Умножив слева обе части матричного уравнения наА^–1(матрицу, обратную матрицеА), получаютЕХ=А^–1ВилиХ=А^–1В. Тогда первый элемент вектора-произведения равен x₁, второй равен x₂, …,n-ый –x_n.

Возьмите чистый лист. Назовите его «Матричный метод». Введите в области А1:D4 матрицу коэффициентов системы (это можно сделать копированием данных с листа «Правило Крамера»). В области Е1:Е4 введите столбец свободных членов. В области А6:D9 вычислите обратную матрицу. В области А11:А14 найдите произведение массивов из области А6:D9 и Е1:Е4. Если вы все сделали правильно, то должны получиться такие же значения неизвестных, что и методом Крамера.

Задание 6.29. Решить систему линейных уравнений (6.1) итерационным методом.

Возьмите чистый лист. Назовите его «Итерационный метод».

1. Мысленно зарезервируйте столько ячеек, сколько имеется неизвестных (уравнений). Для (6.1) – это четыре ячейки А1:А4.

2. Введите в отдельные ячейки (В1:В4) левую часть каждого уравнения минус свободный член. При этом вместо неизвестных следует вводить в формулах ссылки на зарезервированные на предыдущем шаге ячейки (вместо x₁– на первую, вместоx₂– на вторую и т.д.). Для первого уравнения В1: =4*А1-2*А2-0,9*А3-А4-2.

3. Вызовите Сервис – Поиск решения.

4. В диалоговом окне Поиск решения введите:

• Установить целевую: указать адрес ячейки, соответствующей первому уравнению системы (В1)

• Значению: 0

• Изменяя ячейки: указать адреса, зарезервированных под решение ячеек А1:А4 (обвести их мышкой).

5. Щелкнуть область ограничений и нажать кнопку Добавить. В появившемся диалоговом окне ввести:

• Ссылка на ячейку: адреса ячеек, соответствующих всем остальным уравнениям системы (В2:В4)

• Во втором окошке нажать стрелку ниспадающего меню и выбрать знак «равно»

• В окошке Ограничениеввести значение 0. НажатьОКи вернуться в окно Поиска.

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

Задание 6.30. Решить систему нелинейных уравнений (6.2) итерационным методом.

(6.2)

Ответ: {1,45604; –1,12427; –0,30099}. Назвать лист «Нелинейное уравнение».

Задание 6.31. Функции, заданные составным аналитическим выражением.

Задание 6.31.1. Табулировать функцию и построить график:

y(x) =(6.3)

Для ввода таких выражений в Excelимеется функция условная функция ЕСЛИ:

ЕСЛИ (логическая функция, формула 1, формула 2)

Когда логическая функциявозвращает значение ИСТИНА, используетсяформула 1, когда она возвращает значение ЛОЖЬ, –формула 2.

Для ввода функции (6.3) в случае, когда аргумент имеет адрес А1, следует использовать выражение: =ЕСЛИ(А1<>0; SIN(A1)/A1; 1).

Задание 6.31.1. Табулировать функцию и построить график:

y(x) =(6.4)

Указание: используйте вложение функций: ЕСЛИ (А1>0; А1^2; ЕСЛИ(A1=0; 1;SQRT(1 –x))

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

Таблица 6.1.

Отформатированная таблица
№ зачетной книжки	ФИО		Личные данные
	Фамилия	Имя	Возраст	Пол
123456	Иванов	Павел	17	м
654321	Петров	Петр	18	м
987654	Кузнецова	Анна	17	ж

Таблицу можно рассматривать как совокупность именованных столбцов. Именами столбцов в Таблице 6.1 являются «№ зачетной книжки», «Фамилия», «Имя», «Возраст», «Пол». Столбцы часто называют полями.

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

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

Создайте лист «Данные» и напечатайте на нем таблицу, аналогичную 6.1.

Задание 6.33. Динамический подсчет результатов. Создайте лист «Подсчет».

В диапазоне А1:Е11 введите таблицу результатов тестирования.

Таблица 6.2.

№ п/п	Фамилия	Балл 1	Балл 2	Балл 3	Балл 4
1	Иванов	2	4	5	9

Заполните ячейки: первый столбец – порядковый номер, второй – фамилия тестируемого, остальные ячейки таблицы – произвольно, числами от 0 до 9. В таблице должно быть десять записей.

Выделите диапазон В2:Е2 с балами для первой фамилии. Присвойте выделенному диапазону имя «Иванов». Для этого активизируйте пункты меню: Вставка –Имя –Присвоить. Аналогичным образом поступите с остальными записями таблицы.

Если выделить все записи от фамилии первого тестируемого до четвертого балла последнего тестируемого, то команда Вставка – Имя – Создать – По тексту в столбце слеваприсвоит имена всем записям.

В ячейку А14 поместите список имен. Для этого выделите ячейку А14 и выберите пункты меню: Данные –Проверка – вкладка Параметры.В качестве типа данных укажитеСписок. Адресом источника укажите диапазон А2:А11, содержащий фамилии.

В ячейку С14 введите формулу = СРЗНАЧ(ДВССЫЛ(А14)).

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

Задание 6.34. Логические функции. Логическими называют функции, возвращающие значения ИСТИНА и ЛОЖЬ. Примером такой функции является функция отношения, имеющая вид:

<формула><символотношения><формула>,

где символ отношения может принимать значения: =  (равно); >  (больше); <  (меньше); ≥  (больше или равно); ≤  (меньше или равно); <>  (не равно).

Если после вычисления функций получается верное числовое неравенство, функция отношения возвращает значение ИСТИНА, в противном случае – ЛОЖЬ.

В ячейку А1 введите значение 2, в А2 – значение 5. В ячейку В1 введите формулу неравенства =А1>A2 (возвращает ЛОЖЬ), а в В2: =А1<A2 (вернет значение ИСТИНА).

Можно создавать логические формулы, используя одноместную логическую операцию НЕ и двуместные операции И и ИЛИ.

Поскольку в ExcelИСТИНА записывается как 1, а ЛОЖЬ – как 0, то логические операции можно заменить арифметическими.

Пусть А и В логические формулы, т.е. возвращают логические значения. Тогда

Логические операции	Эквивалентные арифметические операции
А И В	А · В
А ИЛИ В	А + В – А · В
НЕ А	1 – А

Например, выражение =(А1>С1)*(C2<10) вернет 1 только в том случае, если А1 содержит значение большее, чем С1, а содержимое С2 меньше 10. Если хотя бы одно из этих условий не выполняется, то результатом выполнения выражения будет 0.

Задание 6.35. Контроль правильности вводимых значений.

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

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

Кроме того, можно контролировать данные с использованием логических функций, возвращающих значения ИСТИНА или ЛОЖЬ. Вводимое значение подставляется в формулу. Если функция возвращает значение ИСТИНА, то значение пропускается, иначе появляется сообщение об ошибке.

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

1. Выделить блок ячеек.

2. Выбрать команду Данные–Проверка.

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

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

5. Щелкнуть по вкладке Сообщение для вводаи установить текст, который будет появляться при выборе ячейки. Этот текст должен указывать на то, какие данные можно вводить в ячейку.

6. Щелкнуть по вкладке Сообщение об ошибкеи задать, какое сообщение будет появляться при вводе недопустимых значений.

7. Чтобы не допустить ввод неправильного значения, выбрать опцию Останов. Обратите внимание, что возможны и другие, более мягкие виды контроля (какие?).

8. Нажать кнопку ОК.

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

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

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

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

Задание 6.35.1. Чтобы ячейки диапазона А1:А20 воспринимали только текст, в диалоговом окнеПроверка вводимых значенийвыберитеУсловие проверки:Тип данных – Другой, введите полеФормула: =етекст(А1). Эта функция возвращает ЛОЖЬ, если в ячейку вводится последовательность символов, которую можно рассматривать как число или другое значение. В случае, если диапазон ячеек для проверки заранее не выделен, следует ввести формулу =етекст(А1:А20). Проверьте работу формулы.

Задание 6.35.2. Чтобы в ячейки диапазона В1:В20 нельзя было вводить текст, используйте формулу: =ЕНетекст(В1). Эта функция возвращает ЛОЖЬ, если в ячейку вводится последовательность символов, которую можно рассматривать как текст. Проверьте работу формулы.

Задание 6.35.3. Чтобы в ячейки диапазона С1:С20 можно было вводить только такие значения, которые больше значения в ячейке строкой выше, следует ввести в С1 начальное значение, выделить область С2:С20, задать условие проверки по формуле =С2>С1. Проверьте работу формулы.

Задание 6.35.4. Чтобы в диапазонеD1:D20 были допустимы только уникальные значения, следует ввести вD1 начальное значение, выделить областьD2:D20, задать условие проверки по формуле: =D2<>D1. Проверьте работу формулы.

Задание 6.35.5. Чтобы в диапазон Е1:Е20 вводить тексты, которые начинаются с фиксированной буквы (например, «а»), используют формулу =ЛЕВСИМВ(Е1)=”а”. Проверьте работу формулы.

Задание 6.35.6. Чтобы в диапазонF1:F20 вводить только текст, начинающийся с буквы «а» и содержащий ровно 5 символов, используйте формулу =(ЛЕВСИМВ(F1)=”a”)*(ДЛСТР(F1)=5). Проверьте работу формулы.

Задание 6.36. Создайте с использованием методов контроля на основе функций на листе «Поставки» таблицу ПОСТАВКИ (код поставщика, код продукта, количество, цена, дата поставки). Предполагается, что

• значения в поле «код поставщика» не повторяются;

• поле «код продукта» имеет длину 6;

• «количество» – четное число;

• «цена» не больше заданной;

• «дата поставки» может быть только прошедшей датой (для текущей даты целесообразно использовать функцию СЕГОДНЯ из категории Дата и время).

Задание 6.37. Консолидация таблиц.

Однотипными таблицами называют таблицы, имеющие одинаковые спецификации, т.е. таблицы, столбцы (поля) которых имеют одинаковые имена и тип.

Операция консолидации позволяет объединить несколько однотипных таблиц в одну. Чаще всего эти таблицы содержат входную информацию.

При консолидации можно использовать следующие функции: сумма; количество значений; среднее; максимум; минимум; произведение и т.д.

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

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

Таблица 6.3. (данные, собранные первым сотрудником)

Профессия	Возраст	Доход
Рабочий	29	1500
Бухгалтер	35	2500

Таблица 6.4. (данные, собранные вторым сотрудником)

Профессия	Возраст	Доход
Рабочий	19	1300
Инженер	45	1000

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

Создайте книгу КОНСОЛИДАЦИЯ1. На отдельных листах введите данные из таблиц 6.3 и 6.4. На листе «Итоги» командой Данные – Консолидацияоткройте диалоговое окно. Выберите функциюСреднее. Установив курсор в полеСсылка, выделите данные таблицы 6.3, нажмитеДобавить. Аналогично добавьте ссылку на диапазон с данными таблицы 6.4. Включите опцииПодписи верхней строки,Значения левого столбца. НажмитеОК.

В результате консолидации при использовании для подсчетов среднего значения должно получиться:

Таблица 6.5. Результаты консолидации

Профессия	Возраст	Доход
Рабочий	24	1400
Бухгалтер	35	2300
Инженер	45	1000

На листе «Итоги_Связь» выполните те же действия, но при этом включите в диалоговом окне КонсолидацияопциюСоздавать связи с исходными данными. Изменяя данные в таблицах 6.3 и 6.4, сравните результаты в консолидированных таблицах.

Задание 6.37.2. Создайте книгу КОНСОЛИДАЦИЯ2. На трех листах введите информацию из таблиц 6.6-6.8, назвав их соответственно «Отделение 1», «Отделение 2», «Отделение 3».

Таблица 6.6. Отделение 1

Показатели	Iквартал	IIквартал	IIквартал	IVквартал
Оборотные средства	10	12	14	15
Основные средства	20	21	22	21
Доход	5	6	7	8
Прибыль	2	3	4	5
Число сотрудников	200	220	230	230
Фонд зарплаты	2	3	3	4
Инвестиции	1	2	1	3

Таблица 6.7. Отделение2

Показатели	Iквартал	IIквартал	IIквартал	IVквартал
Оборотные средства	12	14	14	16
Основные средства	22	24	26	28
Доход	6	6	7	8
Прибыль	3	3	4	5
Число сотрудников	240	220	230	230
Фонд зарплаты	3	3	3	4
Инвестиции	1	2	2	3

Таблица 6.8. Отделение3

Показатели	Iквартал	IIквартал	IIквартал	IVквартал
Оборотные средства	12	14	14	16
Основные средства	22	24	26	28
Доход	6	6	7	8
Прибыль	3	3	4	5
Число сотрудников	140	120	130	130
Фонд зарплаты	2	3	3	3
Инвестиции	1	2	2	3

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

Консолидировать данные, используя функцию «Сумма» и включив Создавать связи с исходными данными.

Изменяя исходные данные, проследить пересчет итоговых данных.

Задание 6.38. Группировка, сортировка и подведение итогов.

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

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

Группировка записей выполняется командой Данные – Сортировка. Возможно одновременное задание трех ключей сортировки. Если произвести сортировку последовательно по двум системам ключей, то список окажется отсортированным по всем используемым ключам. При этом ключи, используемые во второй раз, будут старше.

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

Задание 6.38.1. Создайте книгу СОРТИРОВКА.На листе «Поставки» создайте таблицу со структурой:

Таблица 6.9.

Поставщик

Потребитель

Товар

Количество

Цена

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

Пусть записи таблицы «Поставки» необходимо разбить на группы, используя три уровня деления:

Поставщик;

Потребитель;

Товар.

Соответственно следует задать ключи сортировки.

Скопируйте таблицу на лист Поставки2 и сгруппируйте записи по принципу:

Товар;

Поставщик;

Потребитель.

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

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

Подведите итоги в отсортированном списке на листе «Поставки». Щелкните по кнопке 2 и отсортируйте по полю «Цена» в порядке убывания. Раскройте список полностью.

Задание 6.38.2. Дана таблица результатов экзаменационной сессии. Необходимо группировать ее по значениям полей «Факультет» и «Курс», подвести итоги по полученным группам и подгруппам записей, скопировать итоговые таблицы на новые листы. Здесь имеются в виду не студенческие группы, а группы записей, содержащих одинаковое значение заданного поля, в данном случае поля «Факультет». Далее группы разбиваются на подгруппы записей, относящихся к одному и тому же курсу.

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

Таблица 6.10. Итоги сессии

№ зачетной книжки	ФИО	Курс	Факультет	Информатика	Математика	История	Сумма балов
22	Афанасьева	1	экономический	4	2	4
23	Абрамова	1	экономический	2	3	3
24	Бодрова	1	экономический	3	5	5
25	Гусева	1	экономический	5	5	2
26	Гусев	1	экономический	5	4	5
31	Кочешов	1	менеджмент	3	5	4
32	Кузьмин	1	менеджмент	5	5	5
33	Кузьмина	1	менеджмент	2	5	2
34	Купцов	1	менеджмент	5	5	3
40	Соловьев	2	экономический	3	4	3
41	Хандусь	2	экономический	4	4	2
45	Титова	2	экономический	3	2	4
46	Давыдов	2	менеджмент	3	4	5
47	Коптенко	2	менеджмент	5	4	4
65	Алексеева	2	менеджмент	4	4	3
67	Алиева	3	менеджмент	3	3	4
69	Бобров	3	менеджмент	2	3	3
73	Буссель	3	менеджмент	3	3	3
75	Вдовин	3	менеджмент	5	4	3
77	Веселова	3	менеджмент	5	2	5
93	Лебедев	3	экономический	3	2	3
99	Попова	3	экономический	5	5	5
101	Попов	3	экономический	5	5	4
103	Теслин	3	экономический	5	3	4

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

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

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

Скопируйте данные таблицы 6.2 на лист «Фильтр». Вызовите команду Данные – Фильтр – Автофильтр. С помощью появившихся в заголовках полей кнопок задайте условия фильтрации.Отфильтруйте записи о студентах, имеющих определенное количество баллов по одному из тестов. Можно ли фильтровать по двум полям одновременно? По трем полям? Выполните фильтрацию по критериям: средний балл больше 7; средний балл меньше 3 (строкаУсловиеиз раскрывающегося списка). Отфильтруйте студентов, фамилия которых начинается на определенную букву.

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

Расширенный фильтр позволяет:

• копировать полученные записи на новое место;

• сохранять критерий отбора;

• использовать для фильтрации большее число условий;

• создавать вычисляемые критерии;

• выводить только уникальные значения.

При расширенной фильтрации для отбора строк таблицы используется вспомогательная таблица (фильтр), где указываются некоторые из столбцов и строк исходной таблицы. Например, для фильтрации таблицы 6.11 можно создать таблицу 6.12 (диапазон условий). В результате фильтрации будут отобраны те записи таблицы КОНТРАКТ, которые содержат записи о поставках фирмой ООО «Павлин» мороженого в количестве большем, чем 100 единиц, и о поставках фирмой ЗАО «Энергия» моторов в количествах от 120 до 300 штук.

Таблица 6.11. Контракт

Поставщик	Потребитель	Товар	Количество

Таблица 6.12. Условия

Поставщик	Количество	Количество	Товар
ООО «Павлин»		> 100	Мороженое
ЗАО «Энергия»	< 300	> 120	Моторы

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

Количество	Количество
> 150	< 250

С помощью диапазона условий

Количество	Количество	Товар
> 150	< 250	Мороженое

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

Допускается создать диапазон условий фильтра, содержащий несколько строк. Каждой строке соответствуют элементарные условия, объединяемые союзом «И», а условия, соответствующие различным строкам, объединяются союзом «ИЛИ». Например, диапазон условий

Количество	Количество	Товар
> 150	< 250	Мороженое
> 200		Вафли

определяет все записи, удовлетворяющие логическому условию ((150 < количество < 250) И (товар = «мороженое»)) ИЛИ ((количество > 200) И (товар = «вафли»)), то есть те записи, которые относятся к сделкам по поставке мороженого в количестве от 150 до 250 единиц или поставкам вафель в количестве, превосходящем 200 единиц.

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

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

Форма – прямоугольный участок экрана, на котором помещаются различные элементы управления: кнопки действий, поля и списки ввода, поля выбора и т.п. В Excelвозможности создания и использования форм существенно ỳже, чем в системах управления базами данных, напримерMSAccess, но они достаточно удобны. Форма вExcelсоздается на основе заголовка таблицы, т.е. на основе списка имен полей. С каждым вводимым столбцом связывается поле ввода, а значения вычисляемых полей выводятся на форму. При этом видна только одна запись таблицы. Для перемещения по таблице имеются специальные кнопки. Текущую запись можно удалить или изменить (отредактировать). Для ввода новых данных используется кнопкаДобавить. КнопкаКритерийпозволяет задать простой критерий фильтрации записей. После ввода критерия следует нажатьНазадилиДалее; становятся доступны только записи удовлетворяющие критерию. Это полезно, когда записей много.

Скопируйте данные таблицы 6.2 на лист «Форма». Добавьте еще одно поле «Средний балл» и вычислите в нем средний балл для каждого студента. Командой Данные – Форма создайте форму для заполнения таблицы. Используя форму, добавьте 5 записей. Попробуйте удалить запись. Используйте критерий, выделив все записи о студентах, имеющих определенное количество баллов по одному из тестов. Можно ли фильтровать по двум полям одновременно? По трем полям? Выполните фильтрацию по критериям: средний балл больше 7; средний балл меньше 3. Выделите всех студентов, фамилия которых начинается на заданную букву.

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

Задание 6.41. Поиск в таблице.

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

Простейшая задача поиска в таблице может быть описана следующим образом:

1. Дана таблица, состоящая из двух или большего числа полей (столбцов);

2. Дано значение ключевого поля;

3. Требуется найти запись с соответствующим значением ключевого поля;

4. Требуется вернуть значение заданного поля в найденной записи.

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

Упорядочим поле поиска по возрастанию. Для установления соответствия необходимо найти два таких значения поля, что значение 1 ≤ ключ < значение 2.

В этом случае соответствующим будет значение «значение 1».

Если ключ больше всех значений поля, то выбирается последнее из значений поля, т.е. самое большое. Если, наоборот, ключ меньше всех значений поля, то возвращается ошибка.

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

Например, имеетсятелефонный справочник – таблица с четырьмя полями:

№ телефона;

Фамилия;

Имя;

Отчество.

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

Для решения задачи поиска в таблице EXCELпредлагает несколько функций.

Задание 6.41.1. Функция ВПР() (категория Ссылки и массивы).

Синтаксис функции:

ВПР(искомое значение; таблица; номер столбца, из которого возвращается результат; тип сравнения).

Функция имеет следующие особенности применения:

• Поиск происходит в первом столбце таблицы.

• Если «тип сравнения» = 0 (ЛОЖЬ), то сравнение подразумевает полное совпадение.

• Если «тип сравнения» = 1 (ИСТИНА), то первый столбец должен быть отсортирован по возрастанию.

• Если «тип сравнения» не указан, подразумевается, что он равен 1.

Дана таблица с именем «Результат»

№ зачетной книжки	ФИО	Курс	Код группы
1060	Иванов А.А.	1	И61д
1061	Кузнецов В.В.	1	И63д

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

Функция ВПР(1061; «Результат»; 4; 0) вернет значение И63д.

В данном случае требуется точное совпадение при сравнении.

Задание 6.41.2. Имеются данные по результатам экзаменационной сессии.

№ зачетной книжки	ФИО	Математика	Информатика
1061	Иванов А.А.	отлично	хорошо
1063	Амбалов Г.Г.	неудовлетворительно	хорошо
1059	Симонов С.С.	хорошо	удовлетворительно

Добавить в таблицу еще десять записей.

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

Задание 6.41.3. Результаты тестирования студентов представлены таблицей из 4 столбцов, три из которых заполнены:

№ зачетной книжки	ФИО	Курс	Число баллов	Оценка
1111	Ли С.Ю.	1	90

Необходимо дополнить таблицу еще десятью строками со сведениями о баллах студентов разных курсов и автоматизировать заполнение поля «Оценка», используя справочник.

Минимальное число баллов	Оценка
0	неудовлетворительно
30	удовлетворительно
70	хорошо
90	отлично

То есть, если получено меньше 30 баллов, то оценка «неудовлетворительно»; если набрано от 30 до 70, то оценка «удовлетворительно»; если число баллов больше 70 и меньше 90, то оценка «хорошо». Наконец, если студент набрал не меньше 90 баллов, то он получает оценку «отлично».

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

Указание. Присвоить имена столбцу «Курс» и справочникам, например, спр1, спр2. Второй аргумент функции ВПР запишите в виде функции ЕСЛИ(Курс=1; спр1;спр2).

Задание 6.41.5. Функция ПРОСМОТР() (категория Ссылки и массивы).

Синтаксис функции:

ПРОСМОТР (данное значение элемента массива; массив поиска; массив результата)

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

Дана таблица

Номер	ФИО	Доход
3	Вано И.П.	10000
5	Сергеев А.А.	20000

Впишите в таблицуеще 7 строк. Используйте функцию ПРОСМОТР() в векторной синтаксической форме для поиска размера доходов у людей с определенными номерами; заданной фамилией.

Задание 6.41.6. Функция ПОИСКПОЗ() (категория Ссылки и массивы).

Синтаксис функции:

ПОИСКПОЗ (данное значение элемента массива; массив поиска; тип сопоставления)

Здесь массивы поиска и сопоставления совпадают, но можно указать тип сопоставления. Функция возвращает позицию найденного элемента в массиве.

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

Задание 6.41.7. Функция ИНДЕКС() (категория Ссылки и массивы).

Синтаксис функции:

ИНДЕКС(массив; номер строки; номер столбца)

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

Заполните столбец Оценка таблицы из задания 6.41.6, используя функцию ИНДЕКС с аргументами из столбцов Оценка таблицы Справочник (6.41.3) и Позиция (6.41.6). Скройте столбец Позиция.

Задание 6.42. Функции анализа таблиц.

Функции СЧЕТЕСЛИ() и СУММЕСЛИ() позволяют на основе данной таблицы (таблицы сырых данных) создать производную таблицу (обработанных данных).

Функция СЧЕТЕСЛИ() (категория Статистические) имеет следующий синтаксис:

СЧЕТЕСЛИ (массив поиска; условие)

У функции СУММЕСЛИ() (категория Математические) похожий синтаксис:

СУММЕСЛИ (массив поиска; условие; массив суммирования)

Функция СЧЕТЕСЛИ() возвращает число элементов, удовлетворяющих условию; функция СУММЕСЛИ() возвращает сумму элементов, удовлетворяющих условию.

Задание 6.42.1. На листе «БД» введите таблицу:

Таблица 6.13. Пропуск занятий

ФИО	Факультет	Пропущено_часов	Математика	Информатика
Иванов	Экономики	10	5	4
Петров	Права	12	4	5
Федоров	Права	2	4	4
Пухов	Экономики	14	3	3
Троева	Экономики	6	5	5
Котова	Права	6	4	4

Командой Вставка – Имя – Создать присвойте второму столбцу имя Факультет, а третьему – имя Пропущено_часов. При использовании формулы СЧЕТЕСЛИ(Факультет; «Права») получится результат 3. Формула СУММЕСЛИ (Факультет; «Экономики»; Пропущено_часов) вернет сумму часов, пропущенных студентами экономического факультета – 30.

Задание 6.42.2. Дополните таблицу 6.13 десятью новыми записями и получите производную таблицу 6.14. Анализ: математика:

Математика	Факультет	Факультет
	Экономики	Права
Сумма пропущенных часов
Среднее
Отклонение

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

Введите заголовки строк и столбцов таблицы «Анализ_Математика». Установите курсор на ту ячейку, где должна появиться сумма пропущенных часов по математике для студентов факультета экономики. Вызовите функцию БДСУММ. В качестве ее аргументов укажите: База данных – вся таблица 6.13; Поле – ячейка с текстом «Математика» таблицы 6.14; Критерий – ячейки с текстом «Факультет» и «Экономики» (результат 13).

Для вычисления среднего значения и отклонения используйте функции ДСРЗНАЧи ДСТАНДОТКЛ (результаты соответственно4,33 и1,15).

Аналогично заполните поле со сведениями по факультету права (результаты 12, 4 и 0).

Составьте производную таблицу анализа результатов по информатике.

Задание 6.43. Сводные таблицы.

Если данные, необходимые для решения задачи, представлены в виде большой и сложной таблицы, то

1. Непосредственное восприятие данных затруднено;

2. Для решения конкретной задачи всех данных не требуется.

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

Все сказанное можно описать схемой:

Задание 6.43.1. Создание простой сводной таблицы.

Имеются данные о ежемесячных поставках товара:

Таблица 6.14.

№ контракта	Поставщик	Товар	Потребитель	Месяц	Стоимость

Введите в таблицу 6.14 не менее двадцати записей.

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

Для каждого поставщика требуется создать сводную таблицу:

	Потребители
Товары	Суммарная стоимость

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

Шаг 1. Создать таблицу: на основе данных находящихся в списке или базе данныхMicrosoftExcel. Вид создаваемого отчета: сводная таблица.

Шаг 2. В качестве диапазона исходных данных укажите таблицу 6.14.

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

Для рассматриваемого примера следует буксировать поля:

• Поставщик – в область страниц;

• Товары – в область столбцов;

• Потребитель – в область строк;

• Стоимость – в область данных.

Буксировку полей для сводной таблицы можно выполнять непосредственно на листе. Для этого следует на Шаге 3 Мастера сразу нажать кнопку Готово.

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

Обратите внимание на кнопки фильтрации сводной таблицы.

Измените исходные данные и обновите сводную таблицу соответствующей контекстной командой.

Задание 6.43.2. Данные о поставках печатной продукции со складов в киоски представлены в виде таблицы:

Склад	Киоск	Тип продукции	Количество

Необходимо предоставить каждому складу информацию об отгрузке продукции каждого типа каждому киоску. Для этого создайте по каждому киоску сводную таблицу с макетом

Склад (область страниц)
	Тип продукции (область столбцов)
Киоск (область строк)	Количество (область данных)

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

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

Задание 6.44.1. Рассчитать, какая сумма окажется на счете, если 100 рублей положить под 12 % годовых на 10 лет с ежеквартальным начислением сложных процентов.

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

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

Назовите лист «Накопление». Введите исходные данные.

А1: Сумма накопления без изменения ставки

А2: 2

В2: Количество периодов в год

А3: 12 (установите в этой ячейке процентный формат с двумя знаками после запятой)

В3: Годовая процентная ставка

А4: 100

В4: Взнос

А5: 10

В5: Срок

В ячейке А6 вставьте функцию БС с аргументами:

• Ставка: А4 / А2

• Кпер: А5 * А2

• Пс: А3

Нажмите ОК. Должен появиться результат: 326,20 р.

Здесь ставка делится на количество периодов в год, а общее число периодов выплат вычисляется как произведение количества лет на количество периодов.

Решите ту же задачу, при условии, что проценты начисляются ежемесячно (330,04 р.); один раз в год (310,58 р.).

Задание 6.44.2. Решите обратную задачу. За сколько лет наращенная сумма составит 1000 рублей, если положить 100 рублей под 12 % годовых с ежеквартальным начислением сложных процентов.

Для этого введите в А11: За сколько лет определенная сумма.

Скопируйте содержимое ячеек А2:В6 в А12:В16.

Установите курсор на А16. Выберите команду Сервис – Подбор параметра. В диалоговом окне укажите:

• Установитьв ячейке: А16

• Значение: 1000

• Изменяя значение ячейки: А15

Должно получиться 19,47 лет.

Задание 6.44.3. Самостоятельно решите следующую задачу с использованием Подбора параметра. Какую сумму следует положить на счет под 12 % годовых с ежеквартальным начислением сложных процентов, чтобы через 10 лет накопить 1000 рублей? (306,56 р.).

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

Назовите лист «Инвестиции». Введите исходные данные.

А1: Сумма накопления при выплатах в начале года

А2: 1

В2: Количество периодов в год

А3: 15 (процентный формат с двумя знаками после запятой)

В3: Годовая процентная ставка

А4: 100

В4: Выплата (тысяч руб.)

А5: 5

В5: Срок

А6: 1

В6: Тип выплат

В ячейке А7 вставьте функцию БС с аргументами:

• Ставка: А4 / А2

• Кпер: А5 * А2

• Плт: А3

• Тип: А6

Нажмите ОК. Должен появиться результат: 775 373, 84 р.

Самостоятельно решите задачу для случая выплат в конце года (674 238,12 р.).

Задание 6.44.5. Самостоятельно ознакомьтесь с другими финансовыми функциями. Изучите примеры из справочной системы.

Индивидуальные задания

Задание И-6.1. Построить график функции, найти ее корни и экстремумы.

1. y= 2^x + 5x– 3 на [–3; 3 ]

2. y= arctg x– 1 / (3x³) на [ 0,1; 1,3 ]

3. y= 3x⁴+ 4x³– 12x²–5 на [–3; 3 ]

4. y= 0,5^x+ 1 – (x– 2)²на [–7; 4 ]

5. y=sin (x+π/ 3) – 0,5xна [–7; 7 ]

6. y= (x– 3) cos xна [–3; 4 ]

7. y=xln (x+ 1) – 0,5 на [–0,9; 2 ]

8. y = (x – 1)²ln (x + 1) – 3 на [–0,9; 5 ]

9. y = 2 arctg x–x³ /3 на [–3; 3 ]

10. y =x²2^x – 1 на [–5; 1]

11. y =x^1,3 +cos (x²– 3) на–5; 5

12. y =x e^x– 2x³– 1 на–4; 4

13. y= (x– 2)²ln (x+ 3) на–2,3; 3

14. y = cos x ln (x + 1) на  –0,9; 9 

15. y= 2 ln (x+ 2) –xна–1,7; 4

16. y= (x– 1) ln (x+ 1+ 1 / 2) на–5; 5

17. y= 2e^x+ 3x+ 1 на–3; 3

18. y=x²– 5 sinx– 1 на–5; 5

19. y= 3^x–1– 2 –xна–3; 3

20. y=x– 2 + 5^xна–3; 3

Задание И-6.2. Табулировать функцию и построить график.

1. 2.

3. 4.

5. 6.

7. 8.

9. 10.

11. 12.

13. 14.

15. 16.

17. 18.

19. 20.

Задание И-6.3. Построить поверхность приx,y [–1, 1].

1. z=x²– 2y²

2. z=3x²–2y²sin²y

3. z=5x²cos²y–2y²e^y

4. z=2x²cos²y–2y²

5. z=2x²e^0,2–2y⁴

6. z=x³–y²

7. z=xcos²y–y

8. z=x²+2siny

9. z=2x²– 3ye^y

10. 

Задание И-6.4. Решить систему уравнений тремя способами.

1. 2.

3. 4.

5. 6.

7. 8.

9. 10.

11. 12.

13. 14.

15. 16.

17. 18.

19. 20.

Задание И-6.5. На нескольких листах имеются метеорологические данные за январь, февраль и март.

Январь

Город	Температура	Давление	Осадки
Москва	–10	765	68
Петербург	–12	764	45
Томск	–25	785	90
Псков	–9	760	46
Омск	–20	765	99

Февраль

Город	Температура	Давление	Осадки
Москва	–12	768	168
Петербург	–14	750	145
Томск	–35	785	90
Псков	–8	760	146
Омск	–24	765	99

Март

Город	Температура	Давление	Осадки
Москва	–1	740	68
Петербург	–2	744	70
Томск	–17	748	72
Псков	0	752	74
Омск	–10	756	76

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

Место	Средняя температура	Среднее давление	Средние осадки	Несмещенное отклонение температуры	Несмещенное отклонение давления	Несмещенное отклонение осадков

Задание И-6.6. Имеются следующие данные о сотрудниках.

Табельный №	Фамилия	Пол	Год рождения	Количество детей	Отдел	Дата поступления на работу	Оклад
56	Андреев	м	1980	1	23	2000	15 000
57	Петров	м	1975	3	23	2002	8 000
58	Голубев	м	1976	2	23	2003	9 000
59	Пушкин	м	1964	1	33	1995	10 000
60	Толстой	м	1968	7	33	1995	23 000
61	Котов	м	1977		33	2001	13 000
62	Сергеев	м	1975	2	11	2002	14 000
63	Михась	ж	1967	1	11	1990	25 000
64	Ли	ж	1980	1	11	2005	8 000
65	Иванова	ж	1976	2	33	2004	4 000
66	Зайцева	ж	1975	2	33	2003	5 000
67	Зайцев	м	1982	2	33	2004	11 000
68	Семенов	м	1980	3	11	2006	4 000
69	Янов	м	1982		11	1995	5 000

Создать форму для добавления записей в таблицу.

Используя фильтрацию, найти и скопировать на отдельные листы строки таблицы, удовлетворяющие следующим запросам:

1. Сотрудники отдела 33;

2. Табельный номер принадлежит интервалу [58; 63];

3. Количество детей 1 или 2;

4. Сотрудники, имеющие трех или более детей;

5. Сотрудники женского пола из отдела 11 с окладом 8 000;

6. Женщины, имеющие более одного ребенка;

7. Сотрудники, фамилии которых начинаются на букву «П»;

8. Сотрудники, поступившие на работу после 200 года и имеющие оклад 8 000;

9. Сотрудники, работающие в отделе 33 и имеющие оклад от 8 000 до 10 000;

10. Сотрудники мужского пола, работающие в 23 или 11 отделе и родившиеся до 1980 года;

11. Выведите список сотрудников, фамилия которых заканчивается на «ов»;

12. Составить список сотрудников второго отдела, чей оклад на 20 % превышает средний оклад всех сотрудников.

Указание: создать диапазон условий

Отдел	Оклад
2	=оклад 1> СРЗНАЧ(оклад)*1,2

где оклад 1 – ссылка на первую ячейку столбца «Оклад»;

13. Выполнить сортировку по отделам, а внутри отделов по возрастанию табельных номеров;

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

15. Вычислить возраст (число полных лет) каждого сотрудника. Выполнить сортировку по отделам, а внутри отделов – по убыванию возрастов;

16. Построить круговую диаграмму с суммарными окладами по отделам. Скопировать суммарные оклады по отделам на новый рабочий лист;

17. То же для данных по средним окладам.

Задание И-6.7. Имеются две таблицы: в первой указаны ФИО, Специальность, Должность, Число отработанных часов; во второй – справочник тарифов почасовой оплаты. Начислить заработную плату. При формировании таблицы плата за час должна быть получена из справочника с использованием функции ВПР.

Кадры

ФИО	Специальность	Должность	Отработано часов	Заработано
Адамов А.А	Слесарь	Рабочий
Джемов А.Л.	Физик	СНС
Иванов И.И.	Слесарь	Рабочий
Икин С.Ф.	Физик	СНС
Имамов А.С.	Токарь	Рабочий
Ирина О.К.	Токарь	Рабочий
Иркин Д.Л.	Физик	СНС
Исаков М.Л.	Физик	МНС
Кан П.Р.	Физик	СНС
Кисин М.И.	Физик	МНС
Львов М.Р.	Физик	МНС
Соков А.Б.	Токарь	Рабочий
Сомов М.Л.	Слесарь	Рабочий

Оплата

Должность	Плата за час
Рабочий	100
МНС	120
СНС	140

Задание И-6.8. Дана таблица контрактов на поставку товаров. Необходимо представить ее в разрезе поставщика, то есть преобразовать общую для всех поставщиков таблицу в последовательность таблиц, где для каждого поставщика имеется особая таблица с указанием всех его потребителей и товаров, которые он им поставляет, средний размер поставок, максимального и минимального размера цен на поставляемые каждому поставщику товары.

№ контракта	Поставщик	Потребитель	Товар	Количество	Цена	Дата поставки
4	Максим	ЦентПрод	Кофе	14,7	91	01.04.2002
6	Максим	СельПрод	Хлеб	56,9	89	14.03.2002
7	Максим	Партия	Чай	71,6	65	10.04.2002
8	Валент	Партия	Кофе	55,8	7	16.05.2002
12	Валент	СельПрод	Соль	79,0	70	19.04.2002
19	Валент	ЦентПрод	Хлеб	89,5	56	25.05.2002
22	Валент	ЦентПрод	Хлеб	52,2	5	07.05.2002
23	Архип	Партия	Кофе	26,2	8	28.09.2002
28	СоюзМ	Сеть2	Соль	41,5	20	30.06.2002
29	Максим	СельПрод	Хлеб	89,3	29	14.03.2002
32	Архип	Сеть2	Кофе	84,0	78	15.02.2002
32	Архип	ЦентПрод	Сахар	39,7	17	10.09.2002
33	СоюзМ	Сеть2	Соль	28,3	70	10.01.2002
34	Валент	Партия	Кофе	17,4	97	19.04.2002
35	Архип	СельПрод	Чай	57,3	78	06.02.2002
38	Архип	Сеть2	Кофе	2,1	29	12.06.2002
39	Максим	ЦентПрод	Кофе	19,0	31	24.02.2002
39	Валент	СельПрод	Соль	55,9	51	07.05.2002
44	Максим	Сеть2	Чай	19,7	31	23.03.2002
44	СоюзМ	Партия	Чай	59,3	39	28.01.2002
54	Валент	Сеть2	Сахар	57,8	66	28.04.2002
55	Архип	ЦентПрод	Сахар	52,1	90	24.02.2002
55	Архип	Партия	Кофе	76,6	4	05.03.2002
58	Максим	Партия	Чай	60,3	93	01.04.2002
59	СоюзМ	Партия	Чай	52,8	53	05.08.2002
65	Валент	Сеть2	Сахар	95,9	58	23.08.2002
67	СоюзМ	СельПрод	Хлеб	60,6	99	01.01.2002
75	Архип	СельПрод	Чай	35,6	82	21.11.2002
76	СоюзМ	ЦентПрод	Чай	36,3	26	19.01.2002
78	Максим	Сеть2	Чай	2,1	93	18.07.2002
96	СоюзМ	ЦентПрод	Чай	26,9	18	03.11.2002
98	СоюзМ	СельПрод	Хлеб	56,9	24	16.10.2002

Для получения результата достаточно выбрать Данные – Сводная таблица. Создать подходящий макет.

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

Задание И-6.9. Выполнить из следующих заданий три по указанию преподавателя.

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

Январь

Фамилия	Количество сделок	Сумма в руб.
Иванов	6	250
Антонов	12	430
Медведев	7	180

Февраль

Фамилия	Количество сделок	Сумма в руб.
Сидоров	5	200
Иванов	6	220

Март

Фамилия	Количество сделок	Сумма в руб.
Иванов	12	200
Сидоров	10	300
Антонов	8	150
Медведев	6	220

Сводка за 1 квартал

Фамилия	Сумма в руб.	Сумма сделок	Средняя цена сделки

И-6.9.2.Результаты трех экзаменов представлены на трех листах в виде списков. Создать общую ведомость

Математика

Фамилии	Оценки
Антонов	5
Васильев	4
Горшков	3
Дубов	4
Егоров	5
Лукин	5
Панчин	4
Рыков	3

Информатика

Фамилии	Оценки
Антонов	4
Васильев	5
Горшков	3
Дубов	4
Егоров	5
Лукин	4
Панчин	3
Рыков	4

История

Фамилии	Оценки
Антонов	4
Васильев	5
Горшков	3
Дубов	2
Егоров	2

И-6.9.3.На трех листах даны помесячные сводки часовой нагрузки преподавателей.

Создать сводную таблицу нагрузки за семестр по видам работ.

Сентябрь

Фамилии	Аудиторные	Методические	Консультации
Антонов	22	22	11
Васильев	43	24	23
Гарин	43	34	22
Дубов	12	23	11

Октябрь

Фамилии	Аудиторные	Методические	Консультации
Антонов	45	23	34
Васильев	43	4	56
Гарин	21	21	32
Дубов	23	12	12

Ноябрь

Фамилии	Аудиторные	Методические	Консультации
Антонов	23	67	23
Васильев	34	43	4
Гарин	34	35	56
Дубов	34	65	43

И-6.9.4.Результаты финансовой работы фирмы по месяцам представлены в виде таблиц на 3 листах. Составить сводный финансовый отчет за квартал.

Март

Статьи	Сумма, млн. руб.
Доходы	12
Затраты	23
Оборотные средства	22
Прибыль	6
Остаток на счетах	4

Апрель

Статьи	Сумма, млн. руб.
Доходы	2
Затраты	25
Оборотные средства	42
Прибыль	8
Остаток на счетах	6

Май

Статьи	Сумма, млн. руб.
Доходы	2
Затраты	13
Оборотные средства	12
Прибыль	1
Остаток на счетах	1

Задание И-6.10. Создайте лист «Результат И-6.10» и на нем таблицу РЕЗУЛЬТАТ с полями «№», «Фамилия», «Имя», «Отчество», «Факультет», «Курс», «Группа», «Предмет», «Дата», «Оценка».

Для поля «№» установите условие уникальности. Для поля «Имя» установите контроль по количеству символов; для поля «Факультет» – по списку имеющихся в институте факультетов; для полей «Курс» и «Оценка» – по допустимым пределам (от 1 до 5 и от 1 до 10 соответственно); для поля «Группа» – по первым символам. Установите подходящий контроль для полей «Предмет» и «Дата».

Вопросы для самопроверки

1. Назначение и общая характеристика MicrosoftExcel.

2. Как использовать справку в MSExcel?

3. Что такое электронная таблица?

4. Из каких структурных элементов состоит файл электронной таблицы?

5. Как сохранить новую рабочую книгу? Какое расширение имеют файлы MSExcel?

6. Как защитить информацию в книге от несанкционированного доступа? От изменения?

7. Как сохранить текущую электронную таблицу в другом формате (как тестовый, как шаблон или в формате другой версии программы)?

8. Как сохранить электронную таблицу в формате, подходящем для размещения на Web-сайте?

9. Как найти и открыть ранее созданную электронную таблицу?

10. Как создать новую электронную таблицу в MS Excel?

11. Какое максимальное число листов допускается в рабочей книге? Как переименовать рабочий лист? Перелистать рабочую книгу? Добавить новые листы в рабочую книгу? Поменять порядок следования листов?

12. Как вставлять дополнительные столбцы и строки в электронную таблицу? Как удалять строки и столбцы?

13. Как скрыть строки или столбцы? Скрыть листы?

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

15. Какие виды данных может содержать ячейка?

16. Как задать форматирование ячейки? Что под этим понимается?

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

18. Как установить гарнитуру шрифта, его размеры, начертание, цвет, ориентацию текста в ячейке?

19. Как установить и настроить фон и обрамление ячеек?

20. Как центрировать и выравнивать текст в заданном диапазоне ячеек по левому, по правому краю, по высоте?

21. Как объединить несколько ячеек в одну?

22. Как изменить высоту строк и ширину столбцов?

23. Как внести исправления в ячейку?

24. Какие категории встроенных функций вам известны? Опишите процесс ввода встроенной функции.

25. Как можно округлить число, используя встроенные функции?

26. Привести примеры записи формул при помощи арифметических и логических функций.

27. Как скопировать информацию из одной ячейки в другую? Как копировать и перемещать содержимое ячеек между разными листами и разными книгами MS Excel? Как скопировать (переместить) весь лист?

28. Как скопировать (переместить) содержимое ячеек, раздвину уже имеющиеся на листе данные?

29. Как скопировать информацию из таблицы в другое приложение MSOffice?

30. Как скопировать результаты вычислений в документ MSWord? Как при этом обеспечить связь с исходным файлом?

31. Чем отличается специальная вставка от обычной?

32. Что такое относительный адрес ячейки? Абсолютный адрес ячейки? Замена адреса ячейки с относительного на абсолютный.

33. Как дать имя ячейке? Блоку ячеек? Как изменить имя блока? Как увеличить или сократить количество ячеек в блоке?

34. Как меняются ссылки на ячейки при копировании формулы?

35. Как вставить в таблицу MSExcelрисунки, графики, текст и т.п. из других файлов? Как изменить расположение и размеры вставленных объектов?

36. Какие приемы автоматизации ввода вам известны?

37. Как заполнить диапазон ячеек арифметической прогрессией с автоматическим выбором шага?

38. Как воспользоваться функцией автовычисления в строке состояния?

39. Как напечатать часть таблицы?

40. Как обеспечить повторение заголовков на всех страницах?

41. Как выполнить печать в масштабе?

42. Какие типы диаграмм вам известны? Охарактеризовать различные типы диаграмм.

43. Опишите процесс построения диаграммы.

44. Что такое легенда диаграммы?

45. Как сделать подписи данных?

46. Как дать имена рядам?

47. Как дать название оси? Изменить шкалу оси?

48. Как изменить область построения диаграммы? Толщину и цвет линий? Тип маркера?

49. Как форматировать диаграммы (заголовки, подписи, метки, масштаб, цвета, заливка, изменение типа, изменение данных и т.д.)?

50. Как скопировать таблицу или диаграмму в другое приложение Windows?

51. Что такое табулирование функции? Как оно выполняется?

52. Как табулировать функции, заданные составным аналитическим выражением?

53. Как использовать средство Подбор параметра для решения уравнений в MS Excel?

54. Как использовать средство Поиск решения для решения уравнений?

55. Как найти экстремумы функции?

56. Какой тип диаграммы рекомендуется для построения графика функции?

57. Как построить график функции одной и двух переменных в MS Excel?

58. Какие операции с векторами можно выполнить в MS Excel?

59. Как умножить матрицу на число?

60. Как найти сумму нескольких матриц?

61. Какая комбинация клавиш используется при обработке массивов?

62. К какой категории относится функция вычисления обратной матрицы? Произведения матриц? Транспонирования матриц?

63. Какие функции используются при решения систем линейных уравнений матричным методом? Методом Крамера?

64. Опишите решение систем уравнений с использованием средства Поиск решения.

65. Что такое динамический подсчет результатов? В каких случаях он применяется?

66. Какие логические функции вам известны? Какие отношения допускается в них использовать?

67. Как установить и настроить проверку вводимых данных?

68. Что такое консолидация данных? Как ее выполнить?

69. Как консолидировать данные, чтобы при изменении исходных данных результирующая таблица также изменялась?

70. Как выполнить сортировку данных?

71. Что такое группа? Как выполнить группировку данных?

72. Как подсчитать промежуточные итоги?

73. Что такое фильтрация данных? Как выполнить фильтрацию?

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

75. Как скопировать результаты фильтрации на другой лист, исключив остальные данные?

76. Какие функции используются для поиска данных в таблицах?

77. Как создать однотабличную базу данных в MS Excel?

78. Как построить и использовать различные типы сводной таблицы?

79. Как построить диаграммы по данным сводной таблицы?

80. Какие финансовые функции вам известны?

81. Что такое макрос?

82. Как записать макрос в MS Excel? Как создать кнопку макроса?