• число 58,9 в ячейку В1;

• текст Итого в три любых ячейки (одной командой);

• текст Москва во все ячейки интервала (A8:C12;D14:E16);

• названия дней недели в ячейки А3:Н3 Автозаполнением,

• очистить таблицу.

  5. Создать Таблицу 1 по расчету заработной платы (см. с.5):

• набрать заголовок таблицы, таблицу сохранить (как файл рабочей книги);

• продолжить ввод данных (фамилии и оклады) в таблицу,

• ввести и скопировать формулы, вычисляющие подоходный налог (0,12 от оклада), пенсионные отчисления (1% от оклада) и результирующие выплаты;

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

  6. Создать Таблицу 2 (как вариант Таблицы 1) на Листе 2 рабочей книги, выполнив следующие действия:

  Расчет заработной платы Таблица 2

  ФАМ Должн. Оклад $ Под.нал. Пенс.отч. К выдаче

  Иванов Лаборант 125

  Кротов Юрист 530

  Сизова Референт 340

  Попов Директор 580

  И ИТОГО

  Под.нал. 12%

  Пенс.отч. 1%

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

• задать в новой таблице значения процента подоходного налога и пенсионных отчислений в отдельных ячейках,

• в формулы ввести абсолютные ссылки,

• вычислить итоговые значения (как и в первой таблице),

• изменить значение одного из окладов, проанализировать результат.

  7. Создать на третьем листе рабочей книги Таблицу 3:

  Динамика выплат в первом полугодии Таблица 3

  ФАМ янв фев мар апр май июн

  Иванов 125 139 155 220 250 289

  Кротов 530 490 475 450 465 480

  Попов 580 490 375 340 330 325

  8. Ввести имена для Листов 1,2,3 рабочей книги: Таблица 1 и Таблица 2, Динамика выплат.

  9. Опробовать различные варианты просмотра данных рабочей книги:

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

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

  10. На 3-м листе рабочей книги под Таблицей 3 создать аналогичную таблицу с удвоенными значениями всех выплат несколькими способами.

  11. На 4-м листе сформировать таблицу значений функции при изменении x от 0,5 до 4 с шагом 0,1.

  12. Рабочую книгу сохранить и выйти из системы Excel.

  3. Оформление таблицы

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

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

  3.1. Форматы данных

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

  Чаще всего применяются числовые форматы. Для их установки имеется несколько пиктограмм на панели инструментов.

  По умолчанию установлен Общий формат - число отображается так, как вводится.

  Числовой формат - самый применяемый: тысячи разделены пробелом, после запятой два знака. Денежный - как Числовой, но имеет размерность - р. Этот формат не дает выравнивания чисел по десятичной запятой. Финансовый - имеет выравнивание, в остальном похож на Денежный.

  

  На панели инструментов имеются также значки для изменения количества десятичных знаков и для разделения тысяч.

  3.2. Редактирование таблицы и данных

  Линии сетки устанавливаются и убираются командой Сервис-Параметры-Линии сетки (Tools-Options-View-GridLines).

  Ширину столбца можно менять разными способами:

  - с помощью команд главного или контекстного меню,

  - переносом мышью правой границы столбца над первой строкой,

  - двойным щелчком сверху возле правой границы столбца (ширина станет равной самому длинному элементу столбца).

  Вертикальное выравнивание данных - (Формат-Ячейки-Выравнивание-Вертикальное) позволяет установить помимо 4 типов выравнивания автоперенос слов в клетках (опция Переносить по словам - Wrap Text).

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

  Выделенный фрагмент таблицы можно обрамить, выделить цветом, покрыть узором, использовав команду Формат-Ячейки-Рамки или пиктограмму Линии рамки.

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

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

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

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

  Excel может строить диаграммы различных типов - круговые, столбиковые (гистограммы), объемные, графики - при помощи Мастера диаграмм (МД) за 5 шагов.

  

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

  На выбранном для диаграммы месте курсором-крестиком при нажатой клавише мыши рисуется пунктирная рамка, отпускается кнопка мыши и в окне МД нажимается кнопка Далее>.

  Затем выбирается тип и вариант диаграммы, поясняется, сколько строк отводится под заголовки осей, как расположены числовые данные - по строкам или по столбцам; вводятся или нет заголовок диаграммы, легенда-пояснение. Пятый шаг завершается кнопкой Закончить (Готово) и диаграмма появляется на рабочем листе.

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

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

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

  Если диаграмму необходимо построить на отдельном рабочем листе, выполняют команду Вставка-Диаграмма-На новом листе. Excel вставит новый лист (перед текущим) с именем Диаграмма 1 и запустит Мастер диаграмм.

  П р а к т и ч е с к о е з а д а н и е 2

  1. Войти в Excel и открыть созданную рабочую книгу.

  2. Применить к числовым данным первой таблицы различные числовые форматы и операции:

• изменить (затем восстановить) число десятичных знаков;

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

  3. Убрать (и затем восстановить) линии сетки таблицы.

  4. Скрыть (и затем восстановить) столбец Оклад.

  5. Создать для первой таблицы обрамление и ввести фон для строки заголовка. Скопировать оформление на Листы 2 и 3.

  6. Вставить во вторую таблицу две пустых строки после строки с итоговыми данными.

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

  8. Для всех четырех таблиц построить диаграммы:

• для первой -гистограмму, представляющую значения окладов для каждого сотрудника,

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

• по данным третьей и четвертой - графики изменения уровня выплат и значений функции.

  Графические построения поместить под таблицами.

  9. Выполнить просмотр данных рабочей книги:

• первого листа с закреплением строки заголовка,

• всех трех листов одновременно в разных окнах,

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

  10. Изменить размеры диаграмм, отредактировать, переместить (если необходимо) и сохранить файл рабочей книги.

  11. Перенести часть созданной информации (одну из таблиц и соответствующую диаграмму) в документ Word через буфер обмена. Применить Специальную вставку со связью. Проверить эффективность связи.

  12. Вывести на экран справку по команде Правка-Специальная вставка для Excel и для Word при помощи пиктограммы Справка.

  13. Вызвать один из разделов Справки Excel (?-Вызов справки-Предметный указатель; набрать или выбрать из списка название темы (затем, возможно, подтемы) и Вывести); скопировать весь текст или его часть путем выделения в буфер обмена (Параметры-Копировать) и вставить в конец Word документа.

  4. Функции и вычисления

  

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

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

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

  Так, функция, СУММ (А1;В5;С10) дает сумму чисел, записанных в аргументах-ячейках; МИН(С2:Е4) находит минимальное среди чисел указанного в аргументе диапазона ячеек.

  В качестве аргументов можно использовать другие функции - например, СУММ(МИН(C2:E4);A1;B5;C10).

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

  К вычислительным функциям относятся математические, тригонометрические, финансовые, статистические и др.

  Функции текстовой группы выполняют преобразования чисел в ASCII-коды и обратно, определяют длину данных, выделяют из строк подстроки, объединяют подстроки, удаляют пробелы...

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

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

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

  4.1. Автосуммирование

  

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

  Если выделить интервал C3:F5 в представленном ниже фрагменте таблицы и нажать кнопку Автосуммирования, то все пустые клетки заполнятся суммарными значениями.

  C D E F

  3 56 78 35

  4 67 35 49

  5

  C D E F

  3 56 78 35 169

  4 67 35 49 151

  5 123 113 84 320

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

  4.2. Логическая функция если

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

  ЕСЛИ (лог_выраж; значение_если_истина; значение_если_ложь)

  Семантика (реализация) данной функции может быть пояснена следующим образом:

  если лог_выраж (1-й аргумент) - истина, то результатом будет значение_если_истина (2-й аргумент), иначе - значение_если_ложь (3-й аргумент).

  В качестве логических выражений используются:

  - логические отношения - два арифметических выражения, соединенные символом операции отношения, например, x > k-1;

  - логические одночлены - два или более логических отношения, соединенные логической операцией "И"; x > 10 И x < 15 ; в Еxcel такие выражения записываются по-другому (в префиксной форме - т.е. символ операции находится перед аргументами): И (x > 10; x < 15) ; это выражение соответствует высказыванию "х лежит между 10 и 15";

  - логические многочлены - два или более логических одночлена, соединенные операцией "ИЛИ": ИЛИ (x=2; y=2; z=2) - т.е. "хотя бы одна из трех переменных - x, y, z - равна 2"

  Значением любого логического выражения (и высказывания) может быть либо Истина, либо Ложь.

  Используя функцию ЕСЛИ, можно уточнить формулу расчета подоходного налога: =ЕСЛИ (C4>1000000; 20%*C4; 12%*С4).

  Если в качестве третьего аргумента функции ЕСЛИ использовать ее же, то можно получить три пути вычисления:

  ЕСЛИ (Х>10; формула1; ЕСЛИ (X<5; формула2; формула3) )

  На место формулы3 можно вставить еще одну функцию Если и реализовать вычисления по четырем формулам и т.д.

  Эта функция неявно применяется в других вычислениях, например, в функции СУММАЕСЛИ, вычисляющей сумму лишь тех значений, которые удовлетворяют заданному условию, в функции СЧЕТЕСЛИ, вычисляющей количество значений, удовлетворяющих условию, и др. Подробное описание этих и других функций можно получить (и скопировать) из Справки окна Мастера Функций для любой функции Excel.

  4.3. Функции прогнозирования

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

  Известны различные способы решения этой задачи - простые (на основе арифметической и геометрической прогрессии) и специально разработанные методы. Среди статистических функций Excel есть две функции - Тенденция (Trend) и Рост (Growth), вычисляющие линейную и экспоненциальную экстраполяцию.

  Можно проиллюстрировать решение такой задачи на следующем примере :

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

  Решение 1) Открыть новый лист программы Еxcel и ввести в него представленную в Таблице 4 информацию (для столбца А применить автозаполнение).

  A B

  1 Объем продаж

  2 Фактич.

  3 1-й кв. 94 234300

  4 2-й кв. 94 269800

  5 3-й кв. 94 255900

  6 4-й кв. 94 275500

  7 1-й кв. 95 269800

  8 2-й кв. 95 276600

  9 3-й кв. 95 269100

  10 4-й кв. 95 288200

  11 1-й кв. 96 264000

  12 2-й кв. 96 278600

  13 3-й кв. 96 276100

  14 4-й кв. 96 305700

  2) Выполнить линейную экстраполяцию. Проще всего это сделать с помощью выделения экстраполируемых данных и буксировки мышью маркера заполнения из В14 в В15:В18.

  Для быстрого выделения заполненных данными ячеек можно установить курсор на начальную ячейку - В3 и - <Ctrl-Shift->.

  Тот же результат можно получить по-другому - выделить данные блока В3:В18, затем - Правка-Заполнить-Прогрессия-Автозаполнение-ОК.

  3) Применить экспоненциальную экстраполяцию:

  скопировать В3:В14 в С3:С14 (чтобы сравнить результаты обоих методов, использующих арифметическую и геометрическую прогрессию),

  не снимая выделения, протащить маркер заполнения с помощью правой клавиши мыши из С14 в С18 и отпустить клавишу; откроется контекстное меню, в котором выбрать команду Экспоненциальное приближение.

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

  5) Cоздать столбец данных при помощи функции Тенденция:

  в D3:D18 ввести порядковые номера (начиная с 1) как значения независимой переменной, для чего выделить этот интервал и выполнить команду Правка-Заполнить-Прогрессия-По столбцам-Арифметическая-Шаг-1- ОК;

  выделить E3:E14; вызвать Мастер функций, установить Тенденцию, Далее>, диапазон исходных данных - В3:В14, Закончить (ввод формулы); нажать <F2> и <Ctrl-Shift-Enter> чтобы заполнить выделенный интервал значениями функции; эти значения интерполированы и образуют линию тренда;

  выделить E15:E18 - ячейки с новыми значениями на 1997 г.,

  при помощи мастера функций задать три аргумента для Тенденции - В3:В14 (известные значения y), D3:D14 (известные значения х), E15:E18 (новые значения х), Закончить, <F2> и <Ctrl-Shift-Enter>.

  6) Cоздать и вставить на отдельный лист диаграмму с линией тренда при помощи команды

  Вставка-Диаграмма-На другом листе-Интервал В3:В18-Далее >-График- Далее >-3- Далее >- Далее >-Добавить легенду?-Нет-Название диаграммы-Линейный тренд-Закончить.

  7) Отредактировать полученную зависимость:

  выделить двойным щелчком диаграмму,

  выделить одним щелчком вертикальную ось и отформатировать ее, задав команду Формат - Выделенная ось - Шкала-Минимум - 200000 - Максимум - 320000 - ОК,

  выделить одним щелчком горизонтальную ось и отформатировать ее: Формат-Выделенная ось-Шкала-Число категорий между засечками-2-Вид-Метки засечек-Нет-Засечки-Основные-Пересекают ось-Вспомогательные-Наружу - ОК;

  добавить в диаграмму линию тренда: Вставка-Линия тренда-Тип-Линейный-Параметры-Показывать уравнение на диаграмме - ОК.

  Далее представлен результат решения данной задачи.

  Динамика роста объемов продаж

  A B C D E

  1 Объем продаж

  2 Фактич Прогрессия Функция Тенденция

  3 1 кв 94 234300 234300 1 252905,1282

  4 2 кв 94 269800 269800 2 256370,8625

  5 3 кв 94 255900 255900 3 259836,5967

  6 4 кв 94 275500 275500 4 263302,331

  7 1 кв 95 269800 269800 5 266768,0653

  8 2 кв 95 276600 276600 6 270233,7995

  . . . . . . . . . . . . . . . . . . . . . . . .

  14 4 кв 96 305700 305700 12 291028,2051

  15 1 кв 97 294493,9 295179,3 13 294493,9394

  16 2 кв 97 297959,7 299008,2 14 297959,6737

  17 3 кв 97 301425,4 302886,7 15 301425,4079

  18 4 кв 97 304891,1 306815,5 16 304891,1422

  

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

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

  Метод итераций состоит в следующем.

  Проверяется начальное значение в "целевой" ячейке, содержащей параметр. Если это значение не дает нужную величину функции, то значение параметра изменяется и - следующий цикл. Для завершения вычислений задается точность и/или количество циклов-итераций (по умолчанию -100). Если точность за 100 циклов не достигнута, вычисления прекращаются, выдается результат и соответствующее сообщение.

  Ниже рассматриваются два примера, иллюстрирующие метод подбора параметра.

  Пример 1

  Некоторая фирма желает открыть валютный счет в коммерческом банке под 8% годовых и накопить на этом счете $25000, необходимых для обновления компьютерного парка предприятия. Требуется определить сумму ежегодных отчислений на этот счет.

  Для решения этой задачи можно использовать финансовую функцию Б3 и метод подбора параметра.

  Синтаксис функции Б3: Б3(ставка; кпер; плата; нз; тип).

  Функция Б3 выдает будущее значение вклада на основе ежегодных платежей и постоянной процентной ставки.

  Имеет три обязательных аргумента:

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

  и два необязательных:

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

  Тип - число - 0 или 1, обозначающее, когда должна производиться выплата (0 - в конце периода, 1 - в начале). Если аргумент опущен, то он равен 0.

  Алгоритм решения

  A B

  1 П о д б о р п а р а м е т р о в

  2 Процентная ставка 8%

  3 Колич-во периодов 5

  4 Выплаты 0

  5 Размер вклада =Б3(В2;В3;В4)

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

  - задать команду: Сервис-Подбор параметра-Установить в ячейке -$B$5 -Значение - 25000 - Изменяя ячейку - $B$4 - OK.

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

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

  Пример 2 Вычислить корень алгебраического уравнения

  (2x2 + 3)(1 - Sin x ) = ln x

  Решается эта задача методом подбора параметров по следующему алгоритму:

  - преобразовать уравнение так, чтобы в его правой части не содержалось неизвестных, т.е. перенести все x в левую часть;

  - выбрать ячейку, например, С7, присвоить этой ячейке имя х и поместить в эту ячейку приближенное (грубое) значение корня - для данного уравнения, равное 1;

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

  =(2 * x ^ 2 + 3) * ( 1 - Sin (x)) - LN (x) ,

  - выполнить команду: Сервис-Подбор параметров-Установить в ячейке - $C$8 - Значение -0- Изменяя ячейку - $C$7 - OK.

  Если есть необходимость в более точном вычислении корня, то перед вычислением выбрать команду Сервис-Параметры-Вычисления-Максимальное изменение - 1Е-9 - OK. Корни будут вычислены с точностью до 9 знака.

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

  Например, при определении корней уравнения

  2x2 +7x - 18,43113 = 0

  (если известно, что корни лежат между -6 и +5) была построена таблица функции и ее график, а затем применен метод подбора параметра для двух начальных значений корней: +2 и -5 и получены точные значения корня 1,75401 и -5,25401

  X Y

  -6 11,56887

  -5 -3,43113

  -4 -14,4311

  -3 -21,4311

  -2 -24,4311

  -1 -23,4311

  0 -18,4311

  1 -9,43113

  2 3,56887

  3 20,56887

  4 41,56887

  5 66,56887

  

  П р а к т и ч е с к о е з а д а н и е 3

  1. Создать таблицу (по представленному ниже образцу) и выполнить следующие действия:

  Экзаменационная ведомость студенческой группы

  ФИО Матем Физика Филос. Ср.балл Признак усп-ти.

  Сизова С.С. 4 5 2

  Петров П.П. 3 4 3

  Иванов И.И. 5 5 5

  Воронин П.Р. 4 5 4

• вставить формулы расчета среднего балла для каждого студента, среднего балла по предмету и общего по всем предметам;

• отсортировать список по фамилиям (по алфавиту);

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

• ОТЛ для студентов, имеющих только отличные оценки,

• ХОР - "4" и "5", УД - "3", "4", "5" и НЕУД - "2" , "3", "4", "5";

• подсчитать в отдельных ячейках (с пояснениями) количество отличников и неуспевающих;

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

  2. Для данных (из файла) по народонаселению стран и континентов на 1950 -1990 гг. выполнить следующие операции:

• оформить таблицу по образцу (см. след.стр.),

• определить прогноз численности населения земного шара в 2000 г. по двум формулам (при помощи функций Тенденция и Рост) и представить окончательные результаты как среднее арифметическое двух вычислений;

• построить 2 отдельных диаграммы: графики роста численности населения - для Европы, Азии, Америки и

  - для Германии и Франции.

  

  3. Найти корни в данных уравнениях:

  2x3 + 4x - 1 = 0

  ln(1+x) - 0,95 sinx+ 6/7 = 0

  Для 1-го уравнения хнач= 0, для 2-го и 3-го хнач = 1.

  4. Найти все корни уравнения

  х6- 85х4+96х3+1027х2- 96х - 945 = 0

  при условии, что они расположены в интервале [-10, +8].

  

  

  5. Работа со списками

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

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

  5.1. Создание списка

  Выполняется в любом месте рабочего листа в два этапа.

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

  Эта команда переводит Excel в режим работы со списком данных. Появляется окно формы - встроенной базы данных (БД). В этом окне удобно вводить, удалять, корректировать записи и выполнять типовые для БД операции.

  После перехода в режим БД можно продолжить ввод данных.

  Для перехода из одного поля в другое применяется клавиша <Tab>, обратный переход - <Shift -Tab> ; переход к следующей записи - <> или <Enter>.

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

  Можно копировать одинаковые значения полей из предыдущей записи в текущую - <Ctrl - " >.

  После заполнения всех записей - <Esc> или кнопка Закрыть (Close).

  5.2. Операции со списками

  Просмотр БД - листание записей списка - выполняют так же, как и при наборе в окне формы; для этой цели предусмотрена линейка прокрутки.

  Очистка поля - <Del> на клавиатуре, удаление целой записи - Delete (Удалить) в окне формы.

  Для выполнения операций над БД ее выделяют или устанавливают курсор внутри БД (списка) и командой Данные-Форма переходят в режим форм.

  Поиск записей - кнопка Критерий. Появляются чистые текстовые окна для каждого поля - в них вводятся критерии поиска.

  Например, если в поле ФИО задать Н*, будут выданы все фамилии, начинающиеся с буквы Н; для соотношения <=300, выбираются строки-записи со значениями переменной этого поля не более 300.

  Затем - Найти следующее (Find Next) и появляется первая найденная запись, удовлетворяющая критерию. Так же выдаются и последующие записи.

  Чтобы задать другой критерий - новый щелчок на кнопке Критерий; отменить режим поиска - кнопка Форма.

  Выборку данных начинают с установки курсора на ключевое поле. Затем - Данные-Фильтр-Автофильтр - и на экране появится таблица, к каждому имени поля которой добавлен раскрывающийся список. В списке можно выбрать значение поля для использования его в простом критерии выборки или - пункт Настройка (Custom), чтобы задать сложное условие с использованием логических операций И и ИЛИ. Выбранные записи можно скопировать, распечатать, перенести в другую таблицу.

  Восстановление всех записей на экране - команда Данные-Фильтр-Показать Все. Отмена режима фильтрации - Данные-Фильтр-Автофильтр.

  Упорядочивание записей начинают с установки курсора на любую ячейку списка данных (если сортируются все строки) или выделяют область сортировки и - команда Данные-Сортировка. Появляется диалоговое окно с полями Сортировать по.. и Затем по.. (Sort by и Then by), позволяющими задать до 3 ключей (уровней) сортировки при наличии групп данных, т.е. данных, имеющих одинаковые значения в ключевом поле. Так, если упорядочить данные некоторого списка с информацией о сотрудниках по полю "Пол", список будет разбит на две группы - женщин и мужчин. В пределах каждой группы можно еще раз отсортировать данные - по фамилии, по году рождения и т.д.

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

  Наимен Колич Цена Стоим

  молоко 3 3,5 10,5

  молоко 4 12 48

  молоко Всего 7 58,5

  овощи 5 3,5 17,5

  овощи 4 8 32

  овощи Всего 9 49,5

  хлеб 2 2 4

  хлеб 1 2,5 2,5

  хлеб Всего 3 6,5

  Общий итог 19 114,5

  Исходная таблица

  наимен колич цена стоим

  молоко 3 3,5 10,5

  молоко 4 12 48

  овощи 5 3,5 17,5

  овощи 4 8 32

  хлеб 2 2 4

  хлеб 1 2,5 2,5

  Чтобы получить промежуточные итоги для данной (Исходной) таблицы, была выполнена следующая цепочка действий:

  Данные - Итоги - При каждом изменении в - Наимен - Использовать функцию: - Сумма - Добавить итоги по: - Колич - Стоим - Заменить текущие итоги - Итоги под данными - OK.

  Наимен Колич Цена Стоим

  молоко Всего 7 58,5

  овощи Всего 9 49,5

  хлеб Всего 3 6,5

  Общий итог 19 114,5

  Можно изменить уровень детализации списка. При выделении ячейки с промежуточным итогом левее вертикальной линейки появится кнопка со знаком "-" для удаления с экрана значений-составляющих данного итога. Кнопка "+" повышает уровень детализации списка.

  Удаление промежуточных итогов выполняется при помощи команды Данные-Промежуточные итоги-Удалить все.

  5.3. Консолидация данных

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

  Реализуется при помощи команды Данные-Консолидация и выбора в диалоговом окне этой операции необходимых опций.

  Сначала вводятся ссылки на объединяемые данные - прямо с клавиатуры или косвенно - путем выделения мышью интервалов ячеек. После задания каждой ссылки нажимают кнопку Добавить.

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

  Активизация поля Создавать связи с исходными данными установит режим автоматического обновления объединенных данных при изменениях в исходных таблицах .

  Пример Консолидация данных двух таблиц

  С к л а д 1

  ТОВАР ОТКУДА ДАТА СТОИМ

  Книга Киев мар 55,0

  Видео С.-П. янв 425,0

  Книга Киев фев 16,5

  Аудио Москва мар 148,0

  Видео С.-П. апр 520,0

  Аудио С.-П. май 623,0

  Книга Москва июн 58,0

  Аудио Москва янв 132,7

  Видео Москва фев 455,0

  Видео Москва апр 400,0

  (Склад 1 и Склад 2) - 3 варианта

  С к л а д 2

  ТОВАР ОТКУДА ДАТА СТОИМ

  Видео Киев мар 781,0

  Книга С.-П. апр 59,0

  Книга Москва май 122,0

  Видео Киев июн 477,0

  Аудио Москва мар 356,0

  Аудио Москва апр 533,0

  Видео С.-П. мар 699,0

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

  Далее представлены три варианта консолидации двух таблиц, полученные при выделении разных интервалов обеих таблиц.

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

  Консолидация по всем полям

  двух таблиц

  Консолидация по

  3, 4 Полю

  СТОИМ

  янв 557,7

  фев 471,5

  мар 2039,0

  апр 1512,0

  май 745,0

  июн 535,0

  ОТКУДА ДАТА СТОИМ

  Видео 3757,0

  Книга 310,5

  Аудио 1792,7

  Консолидация по 2,3,4 полю

  ОТКУДА ДАТА СТОИМ

  Киев 1329,5

  Москва 2204,7

  С.-П. 2326,0

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

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

  Являются наиболее мощным средством по сравнению с методом консолидации и "промежуточными итогами".

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

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

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

  Лучше всего поясняется этот эффективный механизм на конкретных примерах.

  Пример 1 ФОРМИРОВАНИЕ ПОТРЕБИТЕЛЬСКОЙ КОРЗИНЫ

  Наим Колич Цена Стоим Дата

  сыр 1 12 12 01.03.97

  хлеб 3 2 6 04.03.97

  мясо 2,5 13 32,5 05.03.97

  молоко 2 3,5 7 06.03.97

  мясо 0,4 23 9,2 12.03.97

  хлеб 3 2 6 14.03.97

  молоко 3 3,5 10,5 16.03.97

  сыр 1 12 12 19.03.97

  хлеб 2 2 4 29.03.97

  сыр 3 12 36 05.04.97

  СТ-1 С в о д н ы е т а б л и ц ы

  СТ-2

  Сумма по полю Колич Сумма по полю Стоим

  Наим Всего Наим Всего

  молоко 5 молоко 17,5

  мясо 2,9 мясо 41,7

  сыр 5 сыр 60

  хлеб 8 хлеб 16

  Общий итог 135,2

  Создание СТ-1 в данном примере свелось к группировке данных по полю Наим и суммированию данных по полям Колич и Стоим, для чего курсор был установлен в начало создаваемой таблицы, а затем были выполнены следующие действия:

  Данные-Сводные таблицы - Далее> - выделение исходной таблицы - Далее>; появляется третье окно Мастера сводных таблиц, в котором справа представлены все поля исходной таблицы, а в центре - области на макете сводной таблицы.

  

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

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

  Завершается процесс создания сводной таблицы кнопкой Закончить (Готово).

  Вторая сводная таблица СТ-2 была построена аналогично.

  Пример 2 П Л А Н С О З Д А Н И Я К Н И Г И

  Глава Раздел Автор Страниц Знаков Дата

  1 1.1. Иванов 3 5634 05.07.96

  1 1.2. Иванов 5 8916 17.09.96

  2 2.1. Иванов 10 15490 30.11.96

  2 2.2. Петров 3 4900 21.06.96

  2 2.3. Котов 6 10675 11.09.96

  2 2.4. Котов 5 8234 01.02.97

  3 3.1. Петров 2 987 13.09.96

  3 3.2. Котов 9 14980 13.03.97

  3 3.3. Петров 3 5634 27.11.96

  4 4.1. Иванов 7 8465 15.04.97

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

  Сумма по полю Стр. А в т о р С

  Глава Иванов Котов Петров Общий итог

  1 8 0 0 8

  2 10 11 3 24

  3 0 9 5 14

  4 7 0 0 7

  Общий итог 25 20 8 53

  Сумма по полю Знаков А в т о р т

  Глава Иванов Котов Петров Общий итог а

  1 14550 0 0 14550 б

  2 15490 18909 4900 39299 л

  3 0 14980 6621 21601 и

  4 8465 0 0 8465 ц

  Общий итог 38505 33889 11521 83915 ы

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

  Пример 3 ПОСТУПЛЕНИЕ ТОВАРОВ НА СКЛАД

  ТОВАР ОТКУДА ДАТА СТОИМ

  Книга Киев мар 55,0

  Видео С.-П. янв 425,0

  Книга Киев фев 16,5

  Аудио Москва мар 148,0

  Видео С.-П. апр 520,0

  Аудио С.-П. май 623,0

  Книга Москва июн 58,0

  Аудио Москва янв 132,7

  Видео Москва фев 455,0

  Книга Киев янв 39,9

  (продолжение списка)

  Видео Киев мар 781,0

  Книга С.-П. апр 59,0

  Книга Москва май 122,0

  Видео Киев июн 477,0

  Аудио Москва мар 356,0

  Аудио Москва апр 533,0

  Книга Киев фев 128,0

  Видео С.-П. мар 699,0

  Аудио С.-П. июн 134,0

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

  ОТКУДА Киев 

  Сумма по полю СТОИМ ДАТА

  ТОВАР фев мар май июн Общий итог

  Видео 0 781 425 477 1683

  Книга 144,5 55 0 0 199,5

  Общий итог 144,5 836 425 477 1882,5

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

  ОТКУДА С.-П. 

  Сумма по полю СТОИМ ДАТА

  ТОВАР янв фев мар май июн Общий итог

  Аудио 0 0 0 623 134 757

  Видео 425 699 520 0 0 1644

  Книга 0 0 59 0 0 59

  Общий итог 425 699 579 623 134 2460

  П р а к т и ч е с к о е з а д а н и е 4

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

  ФАМ - фамилия, ГР - год рождения (> 16 лет), ПОЛ,

  СПЕЦ - специ альность*, СТАЖ, №_М - № микрорайона*.

  2. Ввести 15-20 записей (с использованием режима форм),

  3. Скопировать полученный список на второй рабочий лист.

  4. Дать (и реализовать) пример двух- и трехуровневой сортировки созданного списка.

  5. Получить выборочные данные для лиц

• одной специальности,

• женского пола, проживающих в одном микрорайоне.

6. Из исходного списка создать 3 варианта списков с подитогами.

7. Реализовать Примеры 2 и 3 последнего раздела, загрузив данные для исходных таблиц из файла. Внести изменения в исходную информацию. Проанализировать результаты.

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

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

______________